How to Read Excel Files Using Pandas in Python

In this tutorial, we will explore how to read Excel files using the popular Python library, Pandas. Pandas is widely used for data manipulation and analysis, and it provides excellent support for handling Excel files. Whether you are working on a data analysis project, extracting data for machine learning, or simply need to read data from an Excel spreadsheet, Pandas has got you covered.

Prerequisites to Read Excel Files in Pandas

Before we dive into the tutorial, make sure you have Python and Pandas installed on your system. You can install Pandas using the following command in your terminal or command prompt:

pip install pandas

Reading Excel Files with Pandas

To begin, let’s create a simple Excel file named sample_excel.xlsx with some sample data. You can use any spreadsheet software to create this file. For demonstration purposes, our Excel file will look like this:

| Name   | Age | City       |
|--------|-----|------------|
| Somya  | 25  | New York   |
| Sena   | 30  | Los Angeles|
| Kavya  | 22  | Chicago    |

Now, let’s write a Python script to read this Excel file using Pandas.

# Importing the Pandas library
import pandas as pds

# Specify the path to your Excel file
xl_file_path = 'path/to/sample_excel.xlsx'

# Read the Excel file into a Pandas data frame
dfr = pds.read_excel(xl_file_path)

# Display the data frame
print("DataFrame from Excel File:")
print(dfr)

In this example:

• We import the Pandas library using the import pandas as pds statement.
• We specify the path to our Excel file using the xl_file_path variable.
• We use the pds.read_excel() function to read the Excel file into a Pandas DataFrame. The data frame is a two-dimensional, tabular data structure.
• Finally, we print the DataFrame to see the data.

Save the script and run it. You should see the following output:

DataFrame from Excel File:
      Name  Age          City
0   Soumya   25      New York
1     Sena   30   Los Angeles
2    Kavya   22       Chicago

Congratulations! You have successfully read an Excel file using Pandas.

Reading Specific Sheets

If your Excel file contains multiple sheets, you can read a specific sheet by specifying its name with the sheet_name parameter. Let’s modify our script to read a sheet named “Sheet2” from our Excel file.

# Specify the sheet name
sheet_name = 'Sheet2'

# Read the specific sheet into a Pandas data frame
df_sheet2 = pds.read_excel(excel_file_path, sheet_name=sheet_name)

# Display the data frame for the specific sheet
print(f"DataFrame from {sheet_name}:")
print(df_sheet2)

Replace the existing script with this modified version, and run it. If you don’t have a sheet named “Sheet2,” you can create one in your Excel file. The output will display the data from the specified sheet.

Reading Specific Columns

Sometimes, you may only be interested in specific columns from your Excel file. Pandas library allows you to select and read only the columns you need. Let’s modify our script to read only the “Name” and “City” columns.

# Specify the columns to read
sel_cols = ['Name', 'City']

# Read specific columns into a Pandas DataFrame
df_sel_cols = pds.read_excel(xl_file_path, usecols=sel_cols)

# Display the data frame with selected columns
print("DataFrame with Selected Columns:")
print(df_sel_cols)

Replace the existing script with this version, and run it. The output will show a data frame containing only the specified columns.

Handling Missing Values

Real-world data often contains missing values. Pandas provides methods to handle missing values effectively. Let’s modify our script to include handling missing values.

# Read the Excel file with handling missing values
df_with_missing = pds.read_excel(xl_file_path, na_values=['NA', 'N/A', 'Missing'])

# Display the data frame with missing values handled
print("DataFrame with Missing Values Handled:")
print(df_with_missing)

In this example, we use the na_values parameter to specify the values that should be treated as missing. Replace the existing script, and run it to see how Pandas handles missing values in your data.

Frequently Asked Questions (FAQ)

Check out this FAQ section if you want some more clarity or have any of the following queries.

Q1: Can Pandas read Excel files with different endings, like .xls or .xlsx?

A: Yes, Pandas works with both file types. Whether your Excel file has .xls or .xlsx extension, you can use the same pds.read_excel() function.

Q2: What if my Excel file has multiple sheets? Can Pandas read all of them?

A: Yes, Pandas can read all sheets by default. You can simply use pds.read_excel() without specifying the sheet name, or you can use sheet_name=None to read all sheets into a dictionary of DataFrames.

Q3: How do I handle large Excel files with Pandas?

A: To handle large files efficiently, use the chunksize parameter in pds.read_excel(). This allows Pandas to read the file in smaller parts, making it more memory-friendly. You can also explore other libraries like openpyxl or xlrd that Pandas uses for Excel operations.

Q4: Is Pandas the only library for reading Excel files in Python?

A: While Pandas is popular, there are other libraries like openpyxl and xlrd. These libraries offer Excel-related features, and the choice depends on your needs and preferences.

Q5: How do I install additional libraries like openpyxl?

A: Installing extra libraries is easy. Use the command pip install openpyxl in your terminal or command prompt. Ensure you have an internet connection during the installation process.

Feel free to ask more queries via the comment box until you are fully familiar with the topic.

Conclusion

In this tutorial, we covered the basics of reading Excel files using Pandas in Python. We learned how to read the entire Excel file, read specific sheets, select specific columns, and handle missing values. These are fundamental skills that will empower you to work with Excel data efficiently in your Python projects.

Pandas provides a versatile and powerful set of tools for data manipulation and analysis. As you continue to explore and use Pandas, you’ll discover its vast capabilities and its ability to streamline your data-related tasks.

If you want us to continue writing such tutorials, support us by sharing this post on your social media accounts like Facebook / Twitter. This will encourage us and help us reach more people.

Happy coding,
Team TechBeamers