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