w3resource

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 some 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

Understanding slice notation:

It's pretty simple really:

a[start:stop]  # items start through stop-1
a[start:]      # items start through the rest of the array
a[:stop]       # items from the beginning through stop-1
a[:]           # a copy of the whole array

There is also the step value, which can be used with any of the above:

a[start:stop:step] # start through not past stop, by step

The key point to remember is that the :stop value represents the first value that is not in the selected slice. So, the difference between stop and start is the number of elements selected (if step is 1, the default).

The other feature is that start or stop may be a negative number, which means it counts from the end of the array instead of the beginning. So:

a[-1]    # last item in the array
a[-2:]   # last two items in the array
a[:-2]   # everything except the last two items

Similarly, step may be a negative number:

a[::-1]    # all items in the array, reversed
a[1::-1]   # the first two items, reversed
a[:-3:-1]  # the last two items, reversed
a[-3::-1]  # everything except the last two items, reversed

Python is kind to the programmer if there are fewer items than you ask for. For example, if you ask for a[:-2] and a only contains one element, you get an empty list instead of an error. Sometimes you would prefer the error, so you have to be aware that this may happen.

Relation to slice() object

The slicing operator [] is actually being used in the above code with a slice() object using the : notation (which is only valid within []), i.e.:

a[start:stop:step]

is equivalent to:

a[slice(start, stop, step)]

Slice objects also behave slightly differently depending on the number of arguments, similarly to range(), i.e. both slice(stop) and slice(start, stop[, step]) are supported. To skip specifying a given argument, one might use None, so that e.g. a[start:] is equivalent to a[slice(start, None)] or a[::-1] is equivalent to a[slice(None, None, -1)].

While the : -based notation is very helpful for simple slicing, the explicit use of slice() objects simplifies the programmatic generation of slicing.

Ref: https://bit.ly/2MHaTp7