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. 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.
Click me to see the sample solution

3. Write a Pandas program to read specific columns from a given excel file.
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.
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.
Click me to see the sample solution

6. Write a Pandas program to import some excel data (coalpublic2013.xlsx ) skipping first twenty rows into a Pandas dataframe.
Click me to see the sample solution

7. Write a Pandas program to add summation to a row of the given excel file.
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.
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 ).
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.
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.
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".
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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