26 Oct Read Excel in Python Pandas

To read an Excel file in Python, we use the Pandas read_excel() method. First, install the openpyxl package using pip. Type the following command to install openpyxl:

pip install openpyxl

Before moving further, we’ve prepared a video tutorial to read an Excel file in Pandas:

Let’s say we have the following Excel file Cricket.xlsx:

Read an Excel

Let us now see an example to read our Excel file Cricket.xlsx using the pandas.read_excel() method in Pandas. First, load the Excel file, read the data, and store it in a Pandas DataFrame:

import pandas as pd

# Input Excel File
# Load the Excel in the DataFrame
dataFrame = pd.read_excel('Cricket.xlsx')

# Display the Excel file records
print("Our DataFrame =\n", dataFrame)

Output

Our DataFrame =
   Player  Rank  Points
0  Virat     1     100
1  David     2      95
2  Steve     3      85
3    Ben     4      70
4  Rohit     5      65
5   Kane     6      60

Display the top n rows of the DataFrame

The head() method will display the first n rows of a DataFrame. The default rows returned are 5.

Let us see an example to display only the top 2 rows. For that, set the parameter of the head() as 2. First, load the Excel file, read the data, and store it in a Pandas DataFrame:

import pandas as pd

# Input Excel File
# Load the Excel in the DataFrame
dataFrame = pd.read_excel('Cricket.xlsx')

# Display the Excel file records
print("Our DataFrame =\n", dataFrame)

print("\nDisplay top 2 rows =\n",dataFrame.head(2))

Output

Our DataFrame =
   Player  Rank  Points
0  Virat     1     100
1  David     2      95
2  Steve     3      85
3    Ben     4      70
4  Rohit     5      65
5   Kane     6      60

Display top 2 rows =
   Player  Rank  Points
0  Virat     1     100
1  David     2      95

Display the last n rows of a DataFrame

The tail() method will display the last n rows of a DataFrame. The default rows returned are 5.

Let us see an example to display only the bottom 2 rows. For that, set the parameter of the tail() as 2. First, load the Excel file, read the data, and store it in a Pandas DataFrame:

import pandas as pd

# Input Excel File
# Load the Excel in the DataFrame
dataFrame = pd.read_excel('Cricket.xlsx')

# Display the Excel file records
print("Our DataFrame =\n", dataFrame)

print("\nDisplay last 2 rows =\n",dataFrame.tail(2))

Output

Our DataFrame =
   Player  Rank  Points
0  Virat     1     100
1  David     2      95
2  Steve     3      85
3    Ben     4      70
4  Rohit     5      65
5   Kane     6      60

Display last 2 rows =
   Player  Rank  Points
4  Rohit     5      65
5   Kane     6      60

If you liked the tutorial, spread the word and share the link and our website Studyopedia with others.

For Videos, Join Our YouTube Channel: Join Now