How to Read Excel Multiple Sheets in Pandas

1. Read Multiple Excel Sheets From a Workbook Using Pandas in Python
2. Use read_Excel() to Open a Spreadsheet From a Workbook in Pandas
3. Use the parse() Method to Open a Spreadsheet From a Workbook in Pandas

Pandas is a famous Python package used in data science and machine learning. This library provides many functionalities regarding data analytics, prediction, and manipulation.

We can perform many operations on the dataset provided. Most of the time, we perform analysis on an Excel file (also known as xls file) or a CSV file (single spreadsheet) containing the dataset.

We can load a single worksheet or multiple Excel sheets from that file.

Read Multiple Excel Sheets From a Workbook Using Pandas in Python

To use Pandas, we should install it first using the following command.

#Python 3.x
pip install pandas

Further, we will read an Excel file here (having an extension of xls). For that purpose, we also have to install the xlrd module using the following command.

#Python 3.x
pip install xlrd

Sometimes we have a large dataset consisting of multiple spreadsheets in the same workbook. But we are interested only in some specific spreadsheet on that file.

For that purpose, we have to open specific spreadsheets from the workbook. We can do this task easily using Pandas in Python.

Use read_Excel() to Open a Spreadsheet From a Workbook in Pandas

First, we will read the whole Excel file using pd.ExcelFile('path_to_file.xls'). Here, pd refers to Pandas, and we will pass the path to the Excel file as an argument in a method call to ExcelFile().

In the following code, we have imported the Pandas package. Then we read an Excel file dataset.xls containing two spreadsheets, iris and customer_churn.

We are using a Jupyter notebook, so we have uploaded this dataset to our home directory. The spreadsheet is now accessible to the xls object.

If we want to read only the iris spreadsheet, we will again call the read_excel() method. In the first argument, we will pass xls, and in the second argument, we will write the name of that specific spreadsheet to read from that xls file.

Then we have displayed the first five rows of that spreadsheet using df.head(). We can load multiple spreadsheets from a workbook in the same way by specifying the name of that spreadsheet in read_excel().

# Python 3.x
import pandas as pd

xls = pd.ExcelFile("dataset.xls")
df = pd.read_excel(xls, "iris")
df.head()
print(df)

Output:

Use the parse() Method to Open a Spreadsheet From a Workbook in Pandas

Another way to read a single spreadsheet from a workbook is to load the xls file and then call the parse() method with the xls object. As an argument of the parse() method, we will specify the index number of the spreadsheet.

An integer refers to a spreadsheet from that xls file. 0 represents the first spreadsheet, 1 represents the second, and so on, just like array indexing.

Here, we have loaded the iris spreadsheet, which is the second spreadsheet of our workbook, and so we will pass 1 as an index. The output of this code is the same as the above one.

# Python 3.x
import pandas as pd

xls = pd.ExcelFile("dataset.xls")
df = xls.parse(1)
df.head()
print(df)

Output: