# 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 ) and combine 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.**

Test your Python skills with w3resource's quiz

## Python: Tips of the Day

**Python: Annotated Assignment Statement**

This might not seem as impressive as some other tricks but it's a new syntax that was introduced to Python in recent years and just good to be aware of.

Annotated assignments allow the coder to leave type hints in the code. These don't have any enforcing power at least not yet. It's still nice to be able to imply some type hints and definitely offers more options than only being able to comment regarding expected types of variables.

day: str = 'Monday' print(day) lst: list = [1,2,3,4] print(lst)

Output:

Monday [1, 2, 3, 4]

Or the same thing in a shorter way:

day= 'Monday' #str print(day) lst= [1,2,3,4] # list print(lst)

Output:

Monday [1, 2, 3, 4]

**New Content published on w3resource:**- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- 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