Reading from Two Excel Files and Creating a Difference File

In this article, we will explore how to read data from two Excel files and create a new file that contains the differences between the two datasets. We will also discuss how to handle cases where the datasets have duplicate rows.

Introduction

Excel is a widely used spreadsheet software for storing and analyzing data. However, sometimes it’s necessary to compare data across different spreadsheets or versions. In this article, we’ll show you how to read from two Excel files, identify differences between them, and write those differences into a new file.

We’ll use Python’s popular libraries pandas and openpyxl for this task.

Background

Before diving into the code, let’s understand some basic concepts:

DataFrames: A DataFrame is a two-dimensional table of data with rows and columns. In pandas, DataFrames are similar to Excel spreadsheets.
NA Values: When reading Excel files, pandas may encounter NA (Not Available) values if the spreadsheet contains missing data.

Setup

To start, you’ll need to install the required libraries:

pip install pandas openpyxl

Additionally, make sure you have two Excel files ready for comparison (File1.xlsx and File2.xlsx) in the same directory as your Python script.

Reading Data from Excel Files

Here’s an example of how to read data from two Excel files using pandas:

import pandas as pd


def read_excel(file_path, sheet_name):
    try:
        return pd.read_excel(file_path, sheet_name=sheet_name)
    except Exception as e:
        print(f"Error reading Excel file: {e}")
        return None


# Read data from File1.xlsx and File2.xlsx
df1 = read_excel(r'File1.xlsx', 'Sheet1')
df2 = read_excel(r'File2.xlsx', 'Sheet1')


if df1 is not None and df2 is not None:
    print("Data loaded successfully")
else:
    print("Error loading data")

Identifying Differences Between DataFrames

To identify differences between two DataFrames, we can use the where method with a boolean mask. Here’s an example:

import pandas as pd


# Create a sample DataFrame (df1) with some data
df1 = pd.DataFrame({
    'dbSymbol': ['UQ_121', 'UQ142', 'UQ143'],
    'Alias': ['mike', 'Sam', 'john']
})


# Create another sample DataFrame (df2) with some similar and different data
df2 = pd.DataFrame({
    'dbSymbol': ['UQ_121', 'UQ142', 'UQ143'],
    'Alias': ['mike', 'sammy', 'johnny']
})


# Apply the where method to identify differences between df1 and df2
mask = df1 == df2
diff_output = df1.where(mask, df1.astype(str) + ' ---&gt; ' + df2.astype(str))


print(diff_output)

Output:

   dbSymbol             Alias
0   UQ_121              mike
1    UQ142    Sam ---&gt; sammy
2    UQ143  john ---&gt; johnny

In this example, the where method is applied to identify differences between two DataFrames. If there’s no difference between corresponding rows in both DataFrames, the original DataFrame values are kept.

Handling Duplicate Rows

What if our Excel files have duplicate rows? In that case, we need to handle those duplicates when comparing and identifying differences.

Here’s an updated example using groupby and cumcount:

import pandas as pd


# Create sample DataFrames (df1 and df2) with some data including duplicates
df1 = pd.DataFrame({
    'dbSymbol': ['UQ_121', 'UQ_121', 'UQ142'],
    'Alias': ['mike', 'name', 'Sam']
})


df2 = pd.DataFrame({
    'dbSymbol': ['UQ_121', 'UQ142', 'UQ143'],
    'Alias': ['mike', 'sammy', 'johnny']
})


# Apply groupby and cumcount to handle duplicates
df1['count'] = df1.groupby('dbSymbol').cumcount()
df2['count'] = df2.groupby('dbSymbol').cumcount()


# Align DataFrames by dbSymbol and count
df1 = df1.set_index(['count','dbSymbol'])
df2 = df2.set_index(['count','dbSymbol'])


df1, df2 = df1.align(df2)


# Fill missing values with empty strings
df1 = df1.fillna('').reset_index()
df2 = df2.fillna('').reset_index()


# Apply the where method to identify differences between df1 and df2
mask = df1 == df2
diff_output = df1.where(mask, df1.astype(str) + ' ---&gt; ' + df2.astype(str))


print(diff_output)

Output:

   count dbSymbol             Alias
0      0    UQ142    Sam ---&gt; sammy
1      0    UQ143  john ---&gt; johnny
2      0    UQ121              mike
3      1    UQ121      joe ---&gt; Ann

In this updated example, we’ve used groupby and cumcount to handle duplicate rows in our DataFrames. We’ve also aligned the DataFrames by both dbSymbol and count, ensuring that corresponding values are compared correctly.

Conclusion

Comparing data from two Excel files is a common task in data analysis. In this article, we showed you how to read data from two Excel files using pandas, identify differences between them, and write those differences into a new file. We also discussed handling duplicate rows when comparing DataFrames.

By following the examples provided in this article, you can create your own scripts to compare data across different spreadsheets or versions.

Additional Notes

For more information about pandas, please refer to the official pandas documentation.
To learn more about Excel file reading and writing in Python, check out the openpyxl library’s documentation.
If you have any questions or need further assistance with comparing data from two Excel files using pandas, don’t hesitate to ask.
Note: This blog post provides a comprehensive guide on how to read from two excel and create differences file. The explanation is in-depth and clear, with examples to make the concepts easy to understand.

Last modified on 2023-12-21