How to Merge Two Excel Files in Python

Today i was working on a very intresting analysis in which i needed to analyse whether the direct supply done to our branches as urgent orders were actually sold. this would determine the risk of allowing our branches to make urgent orders of some products or not i found necessary to document this incase anyone wanted to merge two excel sheets while matching 2 to 3 columns.

Merge Company holdings.xlsx with Direct Supply Audit.xlsx and bring the Stock Holding column into the Supply dataset.

Step 1: Import Pandas
import pandas as pd
Step 2: Load the Excel Files
Load Company Holdings

Holding = pd.read_excel(
    r"C:\Users\User\OneDrive\Documents\Company holdings.xlsx"
)

Load Direct Supply Audit

Supply = pd.read_excel(
    r"C:\Users\User\OneDrive\Documents\Direct Supply Audit.xlsx"
)

Step 3: Inspect the Data
View First Few Rows
print(Holding.head())
print(Supply.head())
View Column Names
print(Holding.columns.tolist())
print(Supply.columns.tolist())

Step 4: Merge the Files

Match records using:

Supply Holdings
Whse Warehouse Code
Code Item No.

merged = pd.merge(
    Supply,
    Holding,
    left_on=["Whse", "Code"],
    right_on=["Warehouse Code", "Item No."],
    how="left"
)

Explanation
left_on=["Whse", "Code"] uses warehouse and item code from the Supply file.
right_on=["Warehouse Code", "Item No."] uses warehouse and item code from the Holdings file.
how="left" keeps all records from the Supply file.
Step 5: Verify the Merge
View Columns

print(merged.columns.tolist())

View Sample Records

print(merged.head())

Step 6 Save the Merged File

merged.to_excel(
    r"C:\Users\User\OneDrive\Documents\Supply_With_Stock.xlsx",
    index=False
)

print("File saved successfully!")

This allows direct supply records to be analyzed alongside stock holdings.