How to Read Excel Files in Python

Do you need to read Excel files in Python? Excel files are common data sources and reading them with Python can provide you with more flexibility in processing and analyzing data.

In this guide, we will explore how to read Excel files in Python.

Prerequisite

Before we get started, let’s make sure that we have the necessary tools installed. We will need:

• Python 3.x
• Pandas package (for reading Excel files)

You can install the Pandas package using pip:

pip install pandas

Understanding Excel files

Before we start reading Excel files in Python, let’s briefly review the structure of an Excel file. An Excel file is organized into worksheets, which are collections of cells arranged in rows and columns. Each cell can contain data of different types, such as numbers, text, or formulas.

The most common file format for Excel files is XLSX. This format is based on the Office Open XML (OOXML) standard and uses XML files to store data and metadata.

Read Excel files in Python

Now that we have installed the necessary tools and reviewed the structure of Excel files, we can start reading them with Python. We will use the Pandas package, which provides a powerful set of tools for data manipulation and analysis.

Step 1: Importing the Pandas package

The first step is to import the Pandas package in your Python script:

import pandas as pd

Step 2: Loading the Excel file

The next step is to load the Excel file into a Pandas DataFrame. A DataFrame is a two-dimensional table of data with rows and columns, similar to a worksheet in Excel.

You can load an Excel file using the read_excel() function in Pandas:

df = pd.read_excel('file.xlsx')

By default, read_excel() reads the first sheet in the Excel file. You can also specify the sheet name or index using the sheet_name parameter:

df = pd.read_excel('file.xlsx', sheet_name='Sheet2')

Step 3: Accessing the data

Once you have loaded the Excel file into a DataFrame, you can access the data using standard Pandas functions. For example, you can display the first five rows of data using the head() function:

print(df.head())

You can also access specific cells or ranges of cells using the iloc[] function, which uses zero-based indexing:

# Access cell at row 2, column 3
print(df.iloc[1, 2])

# Access range of cells from row 2 to 4, columns 3 to 5
print(df.iloc[1:4, 2:5])

Step 4: Handling missing values

Excel files may contain missing or empty cells, which can cause issues when processing the data. Pandas provides several functions for handling missing values, such as dropna() to remove rows with missing values and fillna() to fill missing values with a specified value or method.

# Remove rows with missing values
df.dropna(inplace=True)

# Fill missing values with the mean of the column
df.fillna(df.mean(), inplace=True)

Step 5: Saving the data

Finally, you may want to save the data to a different format or file. Pandas provides functions for saving data in various formats, such as CSV, Excel, or SQL.

# Save data to CSV file
df.to_csv('file.csv', index=False)

# Save data to Excel file
df.to_excel('file.xlsx', sheet_name='Sheet2', index=False)

Final Codes

Let’s put everything we have learned into a complete Python script that reads an Excel file, processes the data, and saves it to a CSV file:

import pandas as pd

# Load Excel file into DataFrame
df = pd.read_excel('file.xlsx')

# Display first five rows of data
print(df.head())

# Remove rows with missing values
df.dropna(inplace=True)

# Fill missing values with the mean of the column
df.fillna(df.mean(), inplace=True)

# Save data to CSV file
df.to_csv('file.csv', index=False)

Conclusion

In this guide, we have explored how to read Excel files in Python using the Pandas package. We have covered the basic steps of loading an Excel file into a DataFrame, accessing the data, handling missing values, and saving the data to a different format or file.

But this is just the tip of the iceberg. Pandas provides a rich set of tools for data manipulation and analysis, and there are many more advanced techniques for reading and processing Excel files in Python. If you want to learn more, check out the Pandas documentation and explore the many examples and tutorials available online.