- Read Multiple Excel Sheets From a Workbook Using Pandas in Python
read_Excel()to Open a Spreadsheet From a Workbook in Pandas
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.
read_Excel() to Open a Spreadsheet From a Workbook in Pandas
First, we will read the whole Excel file using
pd refers to Pandas, and we will pass the path to the Excel file as an argument in a method call to
In the following code, we have imported the Pandas package. Then we read an Excel file
dataset.xls containing two spreadsheets,
We are using a
Jupyter notebook, so we have uploaded this dataset to our home directory. The spreadsheet is now accessible to the
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
#Python 3.x import pandas as pd xls = pd.ExcelFile('dataset.xls') df = pd.read_excel(xls, 'iris') df.head() print(df)
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)