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]
Go to Excel data
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics