read_excel in pandas — practice examples (Bamboo Weekly)

Read Excel workbooks into data frames — one sheet at a time, or all of them at once.

Spreadsheets are where a great deal of the world's public data actually lives, and they arrive shaped for human readers: title rows, multiple sheets, notes in the margin. read_excel handles all of that, provided you tell it what you are looking at.

Official documentation: pandas.read_excel

The arguments that earn their keep

pd.read_excel(source,
              sheet_name='Units',        # which sheet; default is the first
              usecols=['Country', 'Status'],  # only these columns
              skiprows=5,                # skip a title block above the header
              header=0,                  # which row holds the column names
              nrows=100,                 # peek at a big workbook
              na_values=['-', 'n/a'])    # what counts as missing

Two forms worth knowing before you start guessing:

# What sheets does this workbook even have?
pd.ExcelFile(url).sheet_names

# Read several sheets at once -> a dict of {sheet_name: DataFrame}
pd.read_excel(url, sheet_name=['Units', 'CO2 Parameters'])

# Read every sheet
pd.read_excel(url, sheet_name=None)

Like read_csv, source can be a path or a URL — Pandas fetches it for you, so the example below runs without downloading anything first.

A worked example, on real data

The Global Coal Plant Tracker is a real workbook with several sheets. Bamboo Weekly #64 used it.

Start by asking what is in the file, rather than assuming:

import pandas as pd

url = ('https://www.bambooweekly.com/content/files/wp-content/uploads/2024/02/'
       'global-coal-plant-tracker-january-2024.xlsx')

pd.ExcelFile(url).sheet_names

['About', 'Units', 'Summaries (excl China)', 'CO2 Parameters']

The default would have given us About — a page of documentation, not data. The real table is in Units, and it has far more columns than we need:

(
    pd.read_excel(url, sheet_name='Units',
                  usecols=['Country', 'Capacity (MW)', 'Status',
                           'Start year', 'Region',
                           'Annual CO2 (million tonnes / annum)'])
)

That is 13,906 rows of every coal-fired generating unit on earth, with six columns instead of dozens.

Three mistakes people make

Accepting the first sheet without looking. read_excel defaults to sheet zero, which in real workbooks is frequently a cover page or a table of contents. Run pd.ExcelFile(url).sheet_names first; it costs one line and saves a confusing debugging session.

Reading every column of a large workbook. Excel files are slow to parse compared to CSV, and column count drives that cost. usecols is a bigger win here than it is with read_csv.

Fighting a title block instead of skipping it. If the first rows are a merged title and your columns come back as Unnamed: 1, Unnamed: 2, the header is not where Pandas looked. Use skiprows to step past the decoration, or header= to point at the real header row.

Watch it

Excel parsing is genuinely slow, and there is a much faster route: What's 2,000x faster than read_excel in Pandas?. For workbooks that fight back, Cleaning messy Excel data with Pandas.

Practice it

Work through a read_excel exercise, with instant feedback and no signup required: practice.lernerpython.com/bamboo-weekly/read-excel/

Go deeper

Bamboo Weekly is the practice. If you want the structured version — full courses with downloadable Jupyter notebooks, plus live Pandas office hours when you get stuck — that is LernerPython+Data. A paid Bamboo Weekly subscription is included with it.

See it on real data

Below are the 60 Bamboo Weekly exercises that use read_excel on real-world data — try each one, then study the worked solution.

Part of the Pandas Methods Index. See also practice by skill.

pandas read_excel