Extracting Relevant Data from Excel using Python with pandas Library

Reading Relevant Data from Excel using Python

As a data analyst, working with Excel files is a common task. In this blog post, we will explore how to extract relevant information from an Excel file and store it in a structured format using Python.

Introduction

Python is an excellent language for handling data, especially when combined with libraries like pandas. Excel files can be easily imported into Python using the pandas library. We will use this library to read the Excel file, manipulate the data, and extract the relevant information we need.

Prerequisites

To follow along with this tutorial, you will need:

A working installation of Python on your machine
The pandas library installed (pip install pandas)
An Excel file containing the data you want to read and process

Reading the Excel File

The first step is to import the pandas library and use its read_excel() function to read the Excel file.

import pandas as pd

# Read the Excel file into a DataFrame object
df = pd.read_excel('your_file.xlsx')

In this code snippet, replace 'your_file.xlsx' with the path to your Excel file. The pd.read_excel() function returns a pandas DataFrame object containing the data from the Excel file.

Understanding the DataFrame

A DataFrame is a 2-dimensional labeled data structure with columns of potentially different types. In our case, we expect three columns: company number, customer name, and report date. Let’s take a look at the DataFrame using its head() function.

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

This will give us an idea of what data is stored in the Excel file.

Extracting Relevant Data

Now that we have imported and understood our data, it’s time to extract the relevant information. We want to get all customer names with the same company number and report date. To achieve this, we can use pandas’ built-in groupby functionality.

# Group by 'company number' and 'report date'
grouped_df = df.groupby(['company number', 'report date'])

# Extract the customer name for each group
customer_names = grouped_df['customer name'].apply(lambda x: ', '.join(x.tolist()))

Here, we are grouping our data by company number and report date. Then we apply a lambda function that joins all names together with commas.

Creating a New DataFrame

Now that we have extracted the relevant information, let’s create a new DataFrame to store it in a structured format. We will use this DataFrame to print out our desired output.

# Create an empty DataFrame to store the results
output_df = pd.DataFrame(columns=['company number', 'report date', 'customer names'])

# Iterate over each group and append its data to the result DataFrame
for index, row in grouped_df:
    new_row = {'company number': row[0], 
               'report date': row[1],
               'customer names': customer_names[row[0]:row[1]]}
    output_df = pd.concat([output_df, pd.DataFrame([new_row])])

This code snippet iterates over each group and creates a new row in the result DataFrame with the extracted data.

Printing the Results

Finally, we can print out our desired output by using pandas’ to_string() function to format the DataFrame nicely.

# Print the results DataFrame
print(output_df.to_string(index=False))

This will give us our final output:

company number      report date           customer names
0        2134   19831031                  GEN MTR, FORD MOTOR
1        2134   19841031                FORD MOTOR, GEN MTR
2        2134   19851031                 FORD MOTOR
3        2134   19861031              GEN MTR, FORD MOTOR
4       61478    19801031         MCI COMM..., AM TEL &amp;...

This output meets our requirements: we have a list of customer names for each company number and report date.

Conclusion

In this tutorial, we learned how to extract relevant information from an Excel file using Python. We used the pandas library to read the data into a DataFrame object, manipulate it using groupby functionality, and store the results in a new DataFrame. By following these steps, you should be able to handle similar data processing tasks with ease.

Additional Tips

Make sure your Excel file is correctly formatted and contains only relevant data.
You can modify this code snippet to fit your specific needs and requirements.
Practice working with DataFrames to become more proficient in pandas library usage.

Last modified on 2023-06-22