Pandas Excel: Exercises, Practice, Solution
This resource offers a total of 125 Pandas Excel problems for practice. It includes 25 main exercises, each accompanied by solutions, detailed explanations, and four related problems.
[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. ]
1. Import Excel Data
Write a Pandas program to import excel data (coalpublic2013.xlsx ) into a Pandas dataframe.
Click me to see the sample solution2. Get Data Types of Excel Data
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. Read Specific Columns from Excel
Write a Pandas program to read specific columns from a given excel file.
Click me to see the sample solution
4. Aggregate Production Column Statistics
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. Insert Column with NaN Values
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. Import Excel Data Skipping Rows
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. Add Summation to a Row
Write a Pandas program to add summation to a row of the given excel file.
Click me to see the sample solution
8. Display Last Ten Rows of Excel Data
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. Subtotal of Labor Hours by MSHA ID
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. Find Specific MSHA ID
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. Filter Records with Labor Hours > 20000
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. Find Records Where Mine Name Starts with "P"
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. Find Records with Two Specific MSHA IDs
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. Find Specified Customers by Name
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. Filter Employees by Hire Date (> 01-01-07)
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. Sort Records by Hire Date
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. Filter Employees with Hire Date Between Two Dates
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. Filter Employees of a Specified Year
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. Set Hire Date as Index
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. Sort by Multiple Columns
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. Import Sheet2 Data
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. Combine Three Datasheets into a Single DataFrame
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. Bar Plot of Top 10 Production
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. Bar Plot Comparing Multiple Metrics
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. Export Combined Datasheets to a New Excel File
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