How To Read An Excel File In Python?

In this tutorial, I will explain how to read an Excel file in Python using the pandas library. As a programmer, we often come across reading files, and reading Excel files is necessary when dealing with Python files. I will explain reading an Excel file with examples and screenshots.

Table of Contents

Read an Excel File in Python

Let us learn more about reading an Excel file in Python.

Read How to Import a Python File from the Same Directory?

Example:

Let’s start with a basic example. Suppose you have an Excel file named sales_data.xlsx with a sheet named Q1_Sales. Here’s how you can read this file:

import pandas as pd

# Load the Excel file
file_path = 'path/to/your/sales_data.xlsx'
sheet_name = 'Q1_Sales'

# Read the Excel file
df = pd.read_excel(file_path, sheet_name=sheet_name)

# Display the first few rows of the DataFrame
print(df.head())

Output:

         Date  Sales     Location
0  2024-01-01    500     New York
1  2024-01-02    750      Chicago
2  2024-01-03    600  Los Angeles

You can see the output in the screenshot below.

In this example, pandas.read_excel() is used to read the Excel file into a DataFrame. The sheet_name parameter specifies which sheet to read.

Check out How to Get File Size in Python?

Read Multiple Sheets

If your Excel file contains multiple sheets and you need to read them all, you can do so using a dictionary of DataFrames:

# Read all sheets
dfs = pd.read_excel(file_path, sheet_name=None)

# Display the names of the sheets
print(dfs.keys())

# Access a specific sheet
print(dfs['Q1_Sales'].head())

Output:

dict_keys(['Q1_Sales'])
         Date  Sales     Location
0  2024-01-01    500     New York
1  2024-01-02    750      Chicago
2  2024-01-03    600  Los Angeles

You can see the output in the screenshot below.

Here, setting sheet_name=None reads all sheets into a dictionary where the keys are the sheet names and the values are the DataFrames.

Read How to Overwrite a File in Python?

Handle Missing Data

Real-world data often contains missing values. You can handle these using pandas:

df = pd.read_excel(file_path, sheet_name=sheet_name)

# Fill missing values with a specific value
df_filled = df.fillna(0)

# Drop rows with missing values
df_dropped = df.dropna()

print(df_filled.head())
print(df_dropped.head())

Output:

dict_keys(['Q1_Sales'])
         Date  Sales     Location
0  2024-01-01    500     New York
1  2024-01-02    750      Chicago
2  2024-01-03    600  Los Angeles
         Date  Sales     Location
0  2024-01-01    500     New York
1  2024-01-02    750      Chicago
2  2024-01-03    600  Los Angeles
         Date  Sales     Location
0  2024-01-01    500     New York
1  2024-01-02    750      Chicago
2  2024-01-03    600  Los Angeles

You can see the output in the screenshot below.

Read an Excel File in Python Handle Missing Data

Check out How to Rename Files in Python?

Read Specific Columns

If you only need a subset of the columns, you can specify them using the usecols parameter:

columns_to_read = ['Date', 'Revenue']

df = pd.read_excel(file_path, sheet_name=sheet_name, usecols=columns_to_read)

print(df.head())

Parse Dates

When your data includes date columns, you can ensure they are parsed correctly:

df = pd.read_excel(file_path, sheet_name=sheet_name, parse_dates=['Date'])

print(df.head())
print(df.info())

Read How to Check if a File is Empty in Python?

Example: Analyze Sales Data

Let’s put it all together with a more comprehensive example. Suppose you want to analyze the sales data for the first quarter. Your Excel file contains columns for date, region, manager, product, units sold, and revenue.

# Load the Excel file
file_path = 'path/to/your/sales_data.xlsx'
sheet_name = 'Q1_Sales'

# Read the Excel file
df = pd.read_excel(file_path, sheet_name=sheet_name, parse_dates=['Date'])

# Fill missing values
df = df.fillna({'Units Sold': 0, 'Revenue': 0})

# Calculate total revenue per region
total_revenue_per_region = df.groupby('Region')['Revenue'].sum()

print(total_revenue_per_region)

In this example, we read the Excel file, parsed the date column, filled missing values, and calculated the total revenue per region.

Check out How to Get File Name Without Extension in Python?

Conclusion

In this tutorial, I explained how to read an Excel file in Python. I discussed an example of reading an Excel file, how to read multiple sheets, handling missing data, reading specific columns, parse dates, and example to analyze sales data.

You may read:

Bijay Kumar

I am Bijay Kumar, a Microsoft MVP in SharePoint. Apart from SharePoint, I started working on Python, Machine learning, and artificial intelligence for the last 5 years. During this time I got expertise in various Python libraries also like Tkinter, Pandas, NumPy, Turtle, Django, Matplotlib, Tensorflow, Scipy, Scikit-Learn, etc… for various clients in the United States, Canada, the United Kingdom, Australia, New Zealand, etc. Check out my profile.

enjoysharepoint.com/