Top, Bottom and Range of Excel Rows by Pandas [head & tail, iloc methods]

Getting top and bottom rows of Excel using Pandas

Once you have a data frame by using the read_excel method of Pandas, you may access/manipulate Excel data in Python programs.

In certain scenarios, you may want only to get a number of top rows from the Excel sheet.

Similarly, accessing a specified number of rows from the bottom of the sheet.

Let us show you examples of getting top and bottom rows by Pandas head and tail methods.

The last example also shows how to get a range in between e.g. 5 to 8 rows.

Our sample sheet:

An example of displaying the top five rows by head method

First, create a data frame by using read_excel method of Pandas:

(We assumed you have Pandas package installed, otherwise above code gives an error).

To display the top five rows from the data frame, write this line of code:

That’s it!

Combine the above code and output

import pandas as pd_top

#Loading a sheet "Product Information"

dt_top = pd_top.read_excel('test_Excel.xlsx', sheet_name="Product Information")

#Display top five rows by head method

print(dt_top.head(5))

Output:

Display the last three rows from the Excel sheet

For getting only the last specified number of rows from the last, you may use the tail method of the data frame.

The example below displays the last three rows from the bottom of our sample sheet “Product Information”.

The code and output:

import pandas as pd_bottom

#Loading a sheet "Product Information"

dt_bottom = pd_bottom.read_excel('test_Excel.xlsx', sheet_name="Product Information")

#Display last three rows by tail method

print(dt_bottom.tail(3))

Output:

Learn more about the tail method here.

How about accessing rows in between

So, we want to access neither the top nor bottom but rows in between certain numbers. For example, return data only from row number 5 to 8 rows from Excel sheet.

The answer is using the iloc method of the data frame.

This is how you may specify the rows range in iloc method:

The example below displays data from row number 5 to 8:

import pandas as pd_mid

#Loading a sheet "Product Information"

dt_mid = pd_mid.read_excel('test_Excel.xlsx', sheet_name="Product Information")

#Display rows 3 to 8 by iloc

print(dt_mid.iloc[3:8])

 

Output: