Creating a DataFrame from an Excel file using Pandas

Many datasets are provided in an Excel file format (file extension .xlsx). The pd.read_excel function provides two primary ways to read an Excel file.

Reading an Excel file with a single sheet

By default, pd.read_excel file will always read the first sheet in an Excel file into a DataFrame. This means an Excel file with only a single sheet is easily read. The next section, "Reading in an Excel file with multiple sheets", guides you through reading all of the sheets in one Excel file.

Reading in an Excel file from a URL

Sometimes, as with many publicly available datasets, we can use the URL of the dataset. One example of this is a dataset used to document digital equity in the U.S. This dataset includes information on high-speed internet availability in the United States, provided by the U.S. Census. You can find the dataset here.

To load this dataset, input the URL as a string to the read_excel function.

import pandas as pd
df = pd.read_excel("https://www2.census.gov/programs-surveys/demo/datasets/community-resilience/state_total_covered_populations.xlsx")

Reading in an Excel file from your local computer

Now, let's learn how to read in an Excel file from your computer. In this example, we will use a dataset provided by the World Bank, that shows the annual GDP in every country each year (in USD), starting in 1970. The dataset can be found here.

To read in an Excel file from your computer:

• Make sure the Excel file is in the same folder as the python file you are working with.
• Specify the file name as a string into the read_excel function.

import pandas as pd
df = pd.read_excel("global-gdp-1970-2020.xls")

Reading in an Excel file with multiple sheets

If the excel file you want to read in has multiple tabs or multiple sheets, you can load each sheet in as a separate DataFrame by including the sheet_name as an additional input. This can either be done with the names of the sheets or with the index of the sheet (sheet 0, 1, 2, etc.)

An Excel dataset with multiple sheets

For example, we will use the file state_total_covered_populations.xslx, which is a dataset provided by the U.S. Census Bureau that gives nationwide statistics about the availability of high-speed internet in each state.

This dataset comes with multiple sheets, state_total_covered_populations, and Variable Descriptions. We want to load in the first and second sheets as their own DataFrames.

Loading multiple sheets by the index

First we will load them using the order (index) of the sheets.

import pandas as pd
df1 = pd.read_excel("state_total_covered_populations.xslx", sheet_name=0)
df2 =  pd.read_excel("state_total_covered_populations.xslx", sheet_name=1)
df1

df2

Loading multiple sheets by sheet name

We can also use the names of the sheets. Both will yield the same result.

import pandas as pd
df1 = pd.read_excel("state_total_covered_populations.xslx", sheet_name='state_total_covered_populations')
df2 =  pd.read_excel("state_total_covered_populations.xslx", sheet_name='codebook')
df1

df2

Pandas Documentation

The full documentation for read_excel is available in the pandas documentation.