Table of Contents
Pandas is a powerful Python library that provides easy-to-use data structures and data analysis tools. It includes a powerful data reader that can read Excel files directly into a Pandas DataFrame. To read an Excel file into a DataFrame, use the read_excel() method of the Pandas library. Pass the path of the file as an argument to the read_excel() method and use the sheet name or index to read data from a specific sheet. Finally, use the head() method to display the first few rows of the imported DataFrame.
Excel files are one of the most common ways to store data. Fortunately the pandas function read_excel() allows you to easily read in Excel files.
This tutorial explains several ways to read Excel files into Python using pandas.
Example 1: Read Excel File into a pandas DataFrame
Suppose we have the following Excel file:
The following code shows how to use the read_excel() function to import this Excel file into a pandas DataFrame:
import pandas as pd #import Excel file df = pd.read_excel('data.xlsx') #view DataFrame df playerID team points 0 1 Lakers 26 1 2 Mavs 19 2 3 Bucks 24 3 4 Spurs 22
Example 2: Read Excel File with Index Column
Sometimes you may also have an Excel file in which one of the columns is an index column:
In this case you can use index_col to tell pandas which column to use as the index column when importing:
import pandas as pd #import Excel file, specifying the index column df = pd.read_excel('data.xlsx', index_col='index') #view DataFrame df playerID team points index 1 1 Lakers 26 2 2 Mavs 19 3 3 Bucks 24 4 4 Spurs 22
Example 3: Read Excel File Using Sheet Name
You can also read specific sheet names from an Excel file into a pandas DataFrame. For example, consider the following Excel file:
To read a specific sheet in as a pandas DataFrame, you can use the sheet_name() argument:
import pandas as pd #import only second sheet df = pd.read_excel('data.xlsx', sheet_name='second sheet') #view DataFrame df playerID team points 0 1 Lakers 26 1 2 Mavs 19 2 3 Bucks 24 3 4 Spurs 22
Common Error: Install xlrd
When you attempt to use the read_excel() function, you may encounter the following error:
ImportError: Install xlrd >= 1.0.0 for Excel support
In this case, you need to first install xlrd:
pip install xlrd
Once this is installed, you may proceed to use the read_excel() function.
How to Read CSV Files with Pandas
How to Export a Pandas DataFrame to Excel