# Pandas Excel: Exercises, Practice, Solution

[The purpose of the following exercises to show various Excel tasks. We have executed Python code in Jupyter QtConsole and used coalpublic2013.xlsl (Historical Coal Production Data: 2013) as reference data (modified version). To get Jupyter QtConsole download Anaconda from here. Download original coalpublic2013.xls file from here. ]

## Pandas Excel [25 exercises with solution]

**1.** Write a Pandas program to import excel data (coalpublic2013.xlsx ) into a Pandas dataframe. Go to Excel data

Click me to see the sample solution

**2.** Write a Pandas program to get the data types of the given excel data (coalpublic2013.xlsx ) fields. Go to Excel data

Click me to see the sample solution

**3.** Write a Pandas program to read specific columns from a given excel file. Go to Excel data

Click me to see the sample solution

**4.** Write a Pandas program to find the sum, mean, max, min value of 'Production (short tons)' column of coalpublic2013.xlsx file. Go to Excel data

Click me to see the sample solution

**5.** Write a Pandas program to insert a column in the sixth position of the said excel sheet and fill it with NaN values. Go to Excel data

Click me to see the sample solution

**6.** Write a Pandas program to import excel data (coalpublic2013.xlsx ) skipping first twenty rows into a Pandas dataframe. Go to Excel data

Click me to see the sample solution

**7.** Write a Pandas program to add summation to a row of the given excel file. Go to Excel data

Click me to see the sample solution

**8.** Write a Pandas program to import excel data (coalpublic2013.xlsx ) into a Pandas dataframe and display the last ten rows. Go to Excel data

Click me to see the sample solution

**9.** Write a Pandas program to create a subtotal of "Labor Hours" against MSHA ID from the given excel data (coalpublic2013.xlsx ). Go to Excel data

Click me to see the sample solution

**10.** Write a Pandas program to import excel data (coalpublic2013.xlsx ) into a dataframe and find a specific MSHA ID. Go to Excel data

Click me to see the sample solution

**11.** Write a Pandas program to import excel data (coalpublic2013.xlsx ) into a dataframe and find details where "Labor Hours" > 20000. Go to Excel data

Click me to see the sample solution

**12.** Write a Pandas program to import excel data (coalpublic2013.xlsx ) into a dataframe and find details where "Mine Name" starts with "P". Go to Excel data

Click me to see the sample solution

**13.** Write a Pandas program to import excel data (coalpublic2013.xlsx ) into a dataframe and find all records that include two specific MSHA ID. Go to Excel data

Click me to see the sample solution

**14.** Write a Pandas program to import excel data (coalpublic2013.xlsx ) into a Pandas dataframe and find a list of specified customers by name. Go to Excel data

Click me to see the sample solution

**15.** Write a Pandas program to import excel data (employee.xlsx ) into a Pandas dataframe and find a list of employees where hire_date> 01-01-07. Go to Excel data

Click me to see the sample solution

**16.** Write a Pandas program to import excel data (employee.xlsx ) into a Pandas dataframe and to sort the records by the hire_date column. Go to Excel data

Click me to see the sample solution

**17.** Write a Pandas program to import excel data (employee.xlsx ) into a Pandas dataframe and find a list of employees where hire_date between two specific month and year. Go to Excel data

Click me to see the sample solution

**18.** Write a Pandas program to import excel data (employee.xlsx ) into a Pandas dataframe and find a list of employees of a specified year. Go to Excel data

Click me to see the sample solution

**19.** Write a Pandas program to import excel data (employee.xlsx ) into a Pandas dataframe and convert the data to use the hire_date as the index. Go to Excel data

Click me to see the sample solution

**20.** Write a Pandas program to import given excel data (employee.xlsx ) into a Pandas dataframe and sort based on multiple given columns. Go to Excel data

Click me to see the sample solution

**21.** Write a Pandas program to import sheet2 data from a given excel data (employee.xlsx ) into a Pandas dataframe. Go to Excel data

Click me to see the sample solution

**22.** Write a Pandas program to import three datasheets from a given excel data (coalpublic2013.xlsx ) in to a single dataframe. Go to Excel data

Note: Structure of the three datasheets are same.

Click me to see the sample solution

**23.** Write a Pandas program to import excel data (coalpublic2013.xlsx ) into a dataframe and draw a bar plot where each bar will represent one of the top 10 production. Go to Excel data

Click me to see the sample solution

**24.** Write a Pandas program to import excel data (coalpublic2013.xlsx ) into a dataframe and draw a bar plot comparing year, MSHA ID, Production and Labor_hours of first ten records. Go to Excel data

Click me to see the sample solution

**25.** Write a Pandas program to import three datasheets from a given excel data (employee.xlsx ) into a single dataframe and export the result into new Excel file. Go to Excel data

Note: Structure of the three datasheets are same.

Click me to see the sample solution

**Excel Data:**

*coalpublic2013.xlsx:*

*employee.xlsx:*

**Sheet-1**

**Sheet-2**

**Sheet-3**

**Download (Coalpublic2013.xlsx) from here**

**Download (employee.xlsx) from here**

**More to Come !**

**Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.**

## Python: Tips of the Day

**Python: How do I copy a file in Python?**

shutil has many methods you can use. One of which is:

from shutil import copyfile copyfile(src, dst)

- Copy the contents of the file named src to a file named dst.
- The destination location must be writable; otherwise, an IOError exception will be raised.
- If dst already exists, it will be replaced.
- Special files such as character or block devices and pipes cannot be copied with this function.
- With copy, src and dst are path names given as strings.

If you use os.path operations, use copy rather than copyfile. copyfile will only accept strings.

Ref: https://bit.ly/3e2Y1G0

**New Content published on w3resource :**- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- React - JavaScript Library
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework