Read xlsx File in Python using Pandas: Know with Examples

Pandas is the best python package for the manipulation of small or large datasets in the form of dataframe. There are many inbuilt functions provided by the pandas that make manipulation and computation very easy. Suppose you want to read an excel file then you have a function for it, want to save data then there is a function for that also. In this entire tutorial, you will know how to read xlsx files in python using Pandas.

Read xlsx File in Python using Pandas

There is a function in pandas that allow you to read xlsx file in python and it is pandas.read_excel(). The syntax of the function is below.

pandas.read_excel(io, sheet_name=0, header=0, names=None, index_col=None, usecols=None)

Explanation of the parameters

io: It is the path for your excel file.

sheet_name: Sheet name inside the excel worksheet. Default is the first sheet name.

header: By default, it is 0. It means the first row of the worksheet will be considered as the header.

index_col: It allows you to select the column you want to make its index.

usecols: If it is set to None then it will consider all the columns but if define some column name then it will show the rows for that column only.

This function returns dataframe object.

How to create xlsx file in Pandas

Before going to the implementation part let’s first create a Sample xlsx file using pandas. If you already have a worksheet file then you don’t have to create it. It is only for extra information. You can move to the examples section.

Execute the below lines of code to create a Sample Xlsx person.xlsx file.

import pandas as pd
data = {"name":["Sahil","Rob","Maya"],"age":[23,67,45]}
df = pd.DataFrame(data)
df.to_excel("person.xlsx",index=None)

Here I have passed the index= None for removing index rows from the sheet.

By default, the data will be saved with the sheet named “Sheet 1”.

Examples to Read xlsx File in Python

In this section, you will know how to read xlsx files in python using the pandas library.

Example 1: Reading xlsx file directly

You can read any worksheet file using the pandas.read_excel() method. Suppose I want to read the above created worksheet then I will execute the following lines of code.

import pandas as pd
df = pd.read_excel("person.xlsx")
print(df)

Output

Here you are seeing index is counted according to number. But you can choose any column as index using the index_col argument.

import pandas as pd
df = pd.read_excel("person.xlsx",index_col="name")
print(df)

Output

Read xlsx file directly and changing the index column

Example 2: Read different sheets from xlsx file

In the above example, you were reading the first sheet of the xlsx file. It can be possible that there can be many sheets inside the worksheet then how can we read that sheet? Suppose I have a sheet name country inside the same file. Then For this case, you will use the same function but in addition, you have to also pass the sheet name you want to read.

Execute the following lines of code.

import pandas as pd
df = pd.read_excel("person.xlsx",sheet_name="country")
print(df)

Output

Reading sheet name country from same excel sheet

Example 3: Reading multiple sheets

The above example was reading one sheet. But you can also read multiple sheets at once. You have to just pass the sheet’s name as a list. Execute the below lines of code.

import pandas as pd
df = pd.read_excel("person.xlsx",sheet_name=["Sheet1","country"])
print("Sheet1\n",df["Sheet1"],"\n")
print("Coutry Sheet\n",df["country"])

Output

Example 4: Loading all sheets

You can also load all sheets in the memory bypassing the sheet_name as None. It will read all the sheets at once. To display each sheet you have to just pass the sheet name in the square bracket [].

import pandas as pd
df = pd.read_excel("person.xlsx",sheet_name=None)
print("Sheet1\n",df["Sheet1"],"\n")
print("Country Sheet\n",df["country"])

Output

Reading entire sheets from the excel worksheet

END NOTES

If you want to convert xlsx file to dataframe then read it using the panda’s library. It increases the efficiency for manipulating the data. These are the examples for implementing pandas xlsx file. I will advise you to read xlsx file using the sheet name as it will reduce memory and makes you compute fast on your system. I hope you have liked this tutorial. If you have any queries then you can contact us for more help.