How to Read and Write Excel Files Using Pandas

Introduction 🔗

We recently covered the basics of Pandas. Today, we’ll learn how to work with Excel spreadsheets using Pandas.

Here’s what this article will cover:

• Read Excel file into a DataFrame.
• Load selected columns and skip blank rows or columns.
• Read multiple worksheets from an Excel file.
• Write a DataFrame to an Excel file.
• Write multiple worksheets to an Excel file.

Datasets 🔗

We’ll use below Excel files in today’s tutorial:

• largest_cities.xlsx: contains data on the 200 largest US cities.
• ranked_cities.xlsx: has three worksheets with rankings of US cities.

Please download them from here and copy them to the same directory as your Jupyter Notebook (or Python script).

Install OpenPyXL 🔗

Pandas uses the OpenPyXL library to interact with Excel files. You can install it using the below pip command:

# Run this from the command line 
pip install openpyxl

Now you can launch your notebook and import Pandas as usual:

# import Pandas library
# It'll import openpyxl automatically when required
import pandas as pd

Read Excel File 🔗

We’ll load the Excel file largest_cities.xlsx in this section. Here’s a screenshot of this file:

A few important things to notice:

• The first row contains the column headers (City, State, 2023 Population, etc.).
• The first column, City, has the names of the largest US cities. We can use it as an index to identify rows uniquely.
• Excel also assigns alphabetical labels (A, B, C, etc.) to each column (red rectangle). These labels will come in handy, as we’ll see later.

Let’s use Pandas' method read_excel() to load this file. The method will return a Pandas DataFrame:

# Read the Excel file and print the DataFrame
# Pass the full file path as the input
# we have the file in the same directory as the code
# So, below will work   
pd.read_excel('largest_cities.xlsx')

Pandas used the first row for column labels (in blue). However, it automatically created an index (in red). Let’s print the index to confirm:

# print DataFrame index
largest_cities.index

RangeIndex(start=0, stop=200, step=1)

It is a numerical index ranging from 0 to 199. We don’t want that. Instead, we should use the City column (in green) as the index.
.

Set Column as Index 🔗

We can set a column (or a list of columns) as the index using the DataFrame method set_index():

# set City as the index 
largest_cities = largest_cities.set_index('City')
# print the top 5 rows 
largest_cities.head()

The DataFrame looks better. The list of cities is set as the index. Let’s print the index again to confirm:

largest_cities.index

Index(['New York City', 'Los Angeles', 'Chicago', 'Houston', 'Phoenix',
   'Philadelphia', 'San Antonio', 'San Diego', 'Dallas', 'San Jose',
   ...
   'Kent', 'Syracuse', 'Thornton', 'Denton', 'Jackson', 'Torrance',
   'Rockford', 'Visalia', 'Fullerton', 'Gainesville'],
  dtype='object', name='City', length=200)

You can now use Pandas loc[] to access specific cities using the index labels:

# Get data for a specific city 
# using loc and single square brackets
largest_cities.loc['Anchorage']

State                Alaska
2023 Population     291,073
2020 Census         291,247
Annual Change        -0.02%
Area mi2           1,706.80
Density mi2             171
Name: Anchorage, dtype: object

# Access data for multiple cities
# Notice the use of double square brackets 
largest_cities.loc[['Austin', 'Denver', 'Nashville']]

Load Selected Columns 🔗

Recall that Excel assigns letter labels (A, B, C, etc.) to each column. The read_excel() method allows you to use these labels to load a specific set of columns.

Let’s see some examples.

List of Columns 🔗

The parameter usecols takes in a list of comma-separated letter labels to load specific columns.

Suppose you only want to load four specific columns - City, State, 2020 Census, and Density mi2. You can do that by passing their corresponding letter labels to the parameter usecols:

largest_cities = pd.read_excel(
    'largest_cities.xlsx', 
    # load specific columns using Excel letter labels
    usecols='A,B,D,G',
    # Use the first loaded column as the index
    index_col=0
)
largest_cities.head()

Consecutive Columns 🔗

Suppose you want to load a range of columns (e.g., from A to D). We can use usecols to specify the start and end columns using a colon (:) like below.

largest_cities = pd.read_excel(
    'largest_cities.xlsx', 
    # Load columns A to D (both inclusive)
    usecols='A:D',
    # Set the first loaded column as the index
    index_col=0
)
# Print the last five rows from the DataFrame
largest_cities.tail()

Mixed Columns 🔗

We can also read specific columns and a range of columns together. The below code loads a range (C:E) and two individual columns using usecols:

largest_cities = pd.read_excel(
    'largest_cities.xlsx', 
    # load a range and the individual columns  
    usecols='A,C:E,G',
    # set the first loaded column as the index
    index_col=0
)
largest_cities.head()

Read Excel With Blank Rows & Columns 🔗

So far, we’ve worked with a perfect Excel file. However, it’s common to get messy excel files with empty rows and columns.

Let’s see an example - largest_cities_blank_rc.xlsx has the same data we saw in the last section, but the first column (A) and the top four rows are blank:

If you load this file using read_excel(), you’ll see a DataFrame with lots of missing values (NaN / Unnamed):

# Load a file with blank rows and columns
pd.read_excel('largest_cities_blank_rc.xlsx')

You can skip these empty rows and columns using the below parameters:

• usecols: We already covered this parameter in the last section. Since columns B to H contain the data, we’ll set it to ‘B:H’.
• skiprows: The number of lines to skip at the start of the file. We know the file begins with four blank rows, so let’s set it to 4.

pd.read_excel(
    'largest_cities_blank_rc.xlsx', 
    # Skip the first four rows
    skiprows=4, 
    # Load columns B to H (thus skip A)
    usecols='B:H',
    # Use the first loaded column as the index
    index_col=0
)

The DataFrame looks good now. It doesn’t have any empty rows or columns, and it uses City as the index.

Read Excel With Multiple Sheets 🔗

Excel files can have multiple worksheets, each containing a different data set.

Pandas works great with such files. It helps you to:

• Get the names of all the sheets in a file.
• Load a specific sheet or a selected set of sheets.
• Load all sheets.

Let’s see these operations in action. We’ll use the excel file ranked_cities.xlsx, which contains three sheets:

Get Sheet Names 🔗

How can you get the names of all the sheets in an Excel file?

Easy! Load the file using the ExcelFile class. Then get the value of the sheet_names attribute:

# Get the names of all the sheets in an Excel spreadsheet
pd.ExcelFile('ranked_cities.xlsx').sheet_names

['Best Tech Jobs', 'Fastest Growing', 'Worst Traffic']

Load Specific Sheet 🔗

By default, the read_excel() method loads the first sheet from the Excel file:

# by default, read_excel() loads the first sheet
ranked_cities = pd.read_excel(
    # load the first sheet - 'Best Tech Jobs'
    'ranked_cities.xlsx', 
    # Use the loaded sheet's first column 
    # as the index 
    index_col=0
)
ranked_cities.head() 

You can load a specific sheet by using the parameter sheet_name:

ranked_cities = pd.read_excel(
    'ranked_cities.xlsx',
    # load a specific sheet
    sheet_name='Fastest Growing', 
    # Set the first column as the index 
    index_col=0
)
ranked_cities.head() # Fastest Growing

Load selected sheets 🔗

The parameter sheet_name is quite versatile. It also accepts a list of sheet names (or their integer positions).

For example, if you want to load the first and third sheets, you can set sheet_name to ['Best Tech Jobs', 'Worst Traffic'], or [0, 2]. You’ll get back a dictionary containing data from both sheets.

ranked_cities =  pd.read_excel(
    'ranked_cities.xlsx',
    # sheet_name - Sheets to load 
    # Accepts integer index as well
    # so sheet_name=[0, 2] will load the same sheets
    sheet_name=['Best Tech Jobs', 'Worst Traffic'],
    # Set the first column as the index 
    # for all loaded sheets
    index_col=0
)

# Check return type - it must be a dictionary
type(ranked_cities)

dict

Let’s print the dictionary keys. It’ll be the same sheet names (or integer positions) that you passed in the above read_excel() call.

ranked_cities.keys()

dict_keys(['Best Tech Jobs', 'Worst Traffic'])

The dictionary values will be the DataFrame objects for each sheet. Let’s retrieve them:

# Get DataFrame objects from the dictionary
tech_df = ranked_cities['Best Tech Jobs']
traffic_df = ranked_cities['Worst Traffic']

And print the top 5 entries from both DataFrames:

# Top 5 cities for tech jobs
tech_df.head()

# Top 5 cities with the worst traffic
traffic_df.head()

Los Angeles in the fifth position? That’s shocking 😲. It’s impossible to imagine a city with worse traffic than LA!

Load All Sheets 🔗

You can read all the sheets at once by setting sheet_name to None:

ranked_cities =  pd.read_excel(
    'ranked_cities.xlsx', 
    # Load all the sheets
    sheet_name=None,
    # Set the first column as 
    # the index for all sheets
    index_col=0 
)

The returned dictionary will now have all three sheets:

ranked_cities.keys()

dict_keys(['Best Tech Jobs', 'Fastest Growing', 'Worst Traffic'])

Let’s print the DataFrame containing the fastest growing cities:

ranked_cities['Fastest Growing'].head()

Write DataFrame to Excel File 🔗

Pandas makes it easy to save DataFrames as Excel files. Let’s see it in action.

Suppose you’ve compiled a list of your favorite restaurants in Seattle. And you prepare a DataFrame with their cuisine type and customer ratings:

seattle_restaurants = [
    ['Bakery Nouveau', 'French', 4.6],
    ['Pizzeria Credo', 'Italian', 4.6],
    ['Chan Seattle', 'Korean', 4.4],
    ['Tilikum Place Cafe', 'European', 4.6],
    ['Ba Bar Capitol Hill', 'Vietnamese', 4.5]
]

seattle_restaurants_df = pd.DataFrame(
    data=seattle_restaurants, 
    columns=['Restaurant', 'Cuisine', 'Rating']
)

# Print DataFrame contents
seattle_restaurants_df

You can write this DataFrame to an Excel file using the method to_excel().

It’ll persist the index as well. We don’t want to save the numeric index. So we’ll set the parameter index to False:

seattle_restaurants_df.to_excel(
    'seattle_restaurants.xlsx', 
    # Don't save the auto-generated numeric index
    index=False
)

You should now see the file seattle_restaurants.xlsx in the same directory where you’re running this notebook.

Write Multiple sheets 🔗

Suppose you also compile a list of restaurants in New York City:

nyc_restaurants = [
    ['Piccola Cucina Estiatorio', 'Italian', 4.6],
    ['Boucherie West Village', 'French', 4.6],
    ['Mei Jin Ramen', 'Japanese', 4.5],
    ['Spice Symphony', 'Indian', 4.4],
    ['Bua Thai Ramen & Robata Grill', 'Thai', 4.5]
]

nyc_restaurants_df = pd.DataFrame(
    data=nyc_restaurants, 
    columns=['Restaurant', 'Cuisine', 'Rating']
)

nyc_restaurants_df

Now you have two DataFrames with lists of your favorite restaurants in two cities - seattle_restaurants_df and nyc_restaurants_df.

You would like to create an Excel file with two sheets - it should have one sheet for each DataFrame. You can do that by using Pandas' class ExcelWriter and DataFrame method to_excel():

# Create an ExcelWriter instance 
# and use it as a context manager
with pd.ExcelWriter('cities_restaurants.xlsx') as writer: 
    # Write Seattle restaurants to a sheet named 'Seattle'
    seattle_restaurants_df.to_excel(
        writer, 
        sheet_name='Seattle', 
        index=False
    )    
    # Write NYC restaurants to a sheet named 'New York City'
    nyc_restaurants_df.to_excel(
        writer, 
        sheet_name='New York City', 
        index=False
    )

You’ll now see a new Excel file, cities_restaurants.xlsx, in your current directory. And it’ll have two sheets - Seattle and New York City.

Add Sheet to Existing File 🔗

Let’s say you gather a list of restaurants in New Orleans and create a DataFrame for them:

# New Orleans - Favorite restaurants
nola_restaurants = [
    ['Olde Nola Cookery', 'Cajun & Creole', 4.4],
    ['Atchafalaya', 'Contemporary', 4.7],
    ['Southern Candymakers', 'Dessert', 4.7],
    ['Maïs Arepas', 'Colombian', 4.5],
    ['Katie\'s Restaurant & Bar', 'Cajun & Creole', 4.6]
]

nola_restaurants_df = pd.DataFrame(
    data=nola_restaurants, 
    columns=['Restaurant', 'Cuisine', 'Rating'])

nola_restaurants_df

And you want to save this DataFrame as a new sheet in the file we created in the last section - cities_restaurants.xlsx.

To do that, you’ll need to use the class ExcelWriter in append mode (parameter mode='a'):

# Add a new sheet to the existing file
# Create an instance of ExcelWriter in append mode  
with pd.ExcelWriter('cities_restaurants.xlsx', mode='a') as writer:
    # Append new sheet
    nola_restaurants_df.to_excel(
        writer, 
        sheet_name='New Orleans', 
        index=False
    )

Let’s confirm that the Excel file has the new sheet by printing the sheet names:

# Print the names of all the sheets in the Excel file  
pd.ExcelFile('cities_restaurants.xlsx').sheet_names

['Seattle', 'New York City', 'New Orleans']

As expected, the file now contains the new sheet - New Orleans.

Summary 🔗

This article showed you how to interact with Excel files using Python and Pandas. Here’s a quick recap of what we learned today:

• Read Excel files using read_excel().
  • Load selected columns using the parameter usecols.
  • Skip blank columns and rows with the parameters usecols and skiprows.
  • Read selected sheets using the parameter sheet_name.
• Write DataFrame to Excel file using to_excel().
• Create an Excel file with multiple sheets with the class ExcelWriter.