Auditing and Reconciling Data Across Sources in pandas: Matching Totals, Flagging Discrepancies, and Building a Reconciliation Report

Introduction

You've just received an urgent message from finance: the revenue numbers in the data warehouse don't match the figures in the sales team's Excel tracker, and nobody can figure out which one is right. The CRM shows 4,312 deals closed last quarter. The ERP shows $8.2M in revenue. But the hand-maintained spreadsheet the VP of Sales has been presenting to the board shows $7.9M from 4,287 deals. Something is off — possibly a lot of things — and your job is to figure out what, document it clearly, and produce a report that non-technical stakeholders can actually act on.

This is data reconciliation, and it's one of the most important — and underappreciated — analytical skills in the modern data stack. It's not glamorous. It doesn't involve neural networks. But the ability to systematically audit two or more data sources, identify exactly where they diverge, and communicate discrepancies in a structured way separates analysts who get trusted from analysts who don't. Reconciliation work is what happens before a board presentation, before a financial close, before a data migration goes live. Get it wrong and the consequences range from embarrassing to catastrophic.

By the end of this lesson, you'll be able to build a complete reconciliation workflow in pandas — from loading and normalizing heterogeneous sources, through granular row-level matching, to producing a formatted report that clearly communicates the size, location, and likely cause of every discrepancy.

What you'll learn:

• How to normalize and align data from multiple sources before comparison
• How to match aggregated totals across sources and measure discrepancies as both absolute and percentage values
• How to perform row-level reconciliation using merge-based diffing techniques
• How to classify and categorize discrepancy types (missing rows, value mismatches, duplicates)
• How to build a structured reconciliation report as both a DataFrame and an exportable Excel workbook

---

Prerequisites

This is an advanced lesson. You should already be comfortable with pandas fundamentals — loading data, filtering with boolean masks, groupby aggregation, and merge operations. If you need a refresher on any of those, check out Joining DataFrames with pandas merge: SQL Joins and VLOOKUP in Python and Grouping and Aggregating in pandas: groupby as the PivotTable Replacement before continuing.

You should also understand Cleaning Messy Data with pandas: Missing Values, Duplicates, and Data Types, because reconciliation always starts with data that isn't clean.

---

The Dataset: What We're Working With

We'll build our workflow around a scenario that mirrors real-world reconciliation work: a company's monthly sales data exists in three places.

1. Source A (ERP system): The authoritative financial system, exported as CSV. Contains order_id, customer_id, region, product_sku, order_date, revenue, units.
2. Source B (CRM system): The sales team's deal tracker, also CSV. Contains opportunity_id, account_id, territory, sku, close_date, deal_value, quantity.
3. Source C (Finance spreadsheet): A hand-maintained Excel file with monthly summaries by region.

Let's build these datasets programmatically so you can follow along without needing external files:

import pandas as pd
import numpy as np
from datetime import datetime, timedelta
import random

random.seed(42)
np.random.seed(42)

# Source A: ERP data (authoritative)
n = 500
order_ids = [f"ORD-{i:05d}" for i in range(1, n + 1)]
regions = np.random.choice(["North", "South", "East", "West"], n)
skus = np.random.choice(["SKU-001", "SKU-002", "SKU-003", "SKU-004"], n)
dates = pd.date_range("2024-01-01", "2024-03-31", periods=n)
revenues = np.round(np.random.uniform(500, 15000, n), 2)
units = np.random.randint(1, 50, n)

erp = pd.DataFrame({
    "order_id": order_ids,
    "customer_id": [f"CUST-{random.randint(1000, 9999)}" for _ in range(n)],
    "region": regions,
    "product_sku": skus,
    "order_date": dates,
    "revenue": revenues,
    "units": units
})

# Source B: CRM data — same orders, but with noise
# Introduce: 20 missing orders, 15 extra orders, and 30 value mismatches
crm_orders = erp.copy()

# Drop 20 rows (orders missing from CRM)
drop_idx = crm_orders.sample(20, random_state=1).index
crm_orders = crm_orders.drop(drop_idx)

# Introduce 30 revenue mismatches
mismatch_idx = crm_orders.sample(30, random_state=2).index
crm_orders.loc[mismatch_idx, "revenue"] = crm_orders.loc[mismatch_idx, "revenue"] * np.random.uniform(0.85, 1.15, 30)
crm_orders.loc[mismatch_idx, "revenue"] = crm_orders.loc[mismatch_idx, "revenue"].round(2)

# Add 15 phantom orders not in ERP
phantom_ids = [f"ORD-{i:05d}" for i in range(n + 1, n + 16)]
phantom_rows = pd.DataFrame({
    "order_id": phantom_ids,
    "customer_id": [f"CUST-{random.randint(1000, 9999)}" for _ in range(15)],
    "region": np.random.choice(["North", "South", "East", "West"], 15),
    "product_sku": np.random.choice(["SKU-001", "SKU-002", "SKU-003", "SKU-004"], 15),
    "order_date": pd.date_range("2024-01-15", periods=15, freq="5D"),
    "revenue": np.round(np.random.uniform(500, 15000, 15), 2),
    "units": np.random.randint(1, 50, 15)
})

crm_raw = pd.concat([crm_orders, phantom_rows], ignore_index=True)

# Rename CRM columns to simulate real-world naming differences
crm = crm_raw.rename(columns={
    "order_id": "opportunity_id",
    "customer_id": "account_id",
    "region": "territory",
    "product_sku": "sku",
    "order_date": "close_date",
    "revenue": "deal_value",
    "units": "quantity"
})

# Source C: Finance summary (monthly aggregates by region)
finance_monthly = erp.groupby([
    erp["order_date"].dt.to_period("M"),
    "region"
]).agg(revenue=("revenue", "sum"), orders=("order_id", "count")).reset_index()
finance_monthly.columns = ["month", "region", "finance_revenue", "finance_orders"]

# Introduce a few errors in the finance sheet
finance_monthly.loc[0, "finance_revenue"] = finance_monthly.loc[0, "finance_revenue"] * 1.03
finance_monthly.loc[3, "finance_revenue"] = finance_monthly.loc[3, "finance_revenue"] - 12000
finance_monthly.loc[7, "finance_orders"] = finance_monthly.loc[7, "finance_orders"] + 5

Now we have three realistic, messy sources. Let's reconcile them.

---

Step 1: Normalizing Column Names and Data Types

Before you can compare anything, you need both sources to speak the same language. Column name differences are the single most common source of confusion in reconciliation work. Analysts waste enormous amounts of time second-guessing whether deal_value means the same thing as revenue, or whether close_date and order_date are actually equivalent.

Build a canonical column mapping explicitly and document it:

# Define the canonical schema
ERP_TO_CANONICAL = {
    "order_id": "order_id",
    "customer_id": "customer_id",
    "region": "region",
    "product_sku": "sku",
    "order_date": "transaction_date",
    "revenue": "revenue",
    "units": "units"
}

CRM_TO_CANONICAL = {
    "opportunity_id": "order_id",
    "account_id": "customer_id",
    "territory": "region",
    "sku": "sku",
    "close_date": "transaction_date",
    "deal_value": "revenue",
    "quantity": "units"
}

erp_clean = erp.rename(columns=ERP_TO_CANONICAL)
crm_clean = crm.rename(columns=CRM_TO_CANONICAL)

# Normalize string fields: strip whitespace, uppercase
for col in ["region", "sku"]:
    erp_clean[col] = erp_clean[col].str.strip().str.upper()
    crm_clean[col] = crm_clean[col].str.strip().str.upper()

# Normalize date types
erp_clean["transaction_date"] = pd.to_datetime(erp_clean["transaction_date"])
crm_clean["transaction_date"] = pd.to_datetime(crm_clean["transaction_date"])

# Normalize numeric precision — work in cents to avoid float comparison pitfalls
erp_clean["revenue_cents"] = (erp_clean["revenue"] * 100).round().astype(int)
crm_clean["revenue_cents"] = (crm_clean["revenue"] * 100).round().astype(int)

print(f"ERP rows: {len(erp_clean)}")
print(f"CRM rows: {len(crm_clean)}")

The string normalization step deserves emphasis. If the ERP stores "North" and the CRM stores " north " (with a trailing space and lowercase), a naive equality check will say they don't match. These are the kinds of problems that make reconciliation reports look worse than the data actually is. Strip and uppercase everything before you start comparing. See Text Cleanup at Scale with pandas String Methods and Regular Expressions for deeper coverage of this pattern.

---

Step 2: Top-Level Total Reconciliation

Before diving into row-level detail, always start at the aggregate level. This gives you a quick health check and tells you how big the overall problem is. If totals match within rounding error, the discrepancy might be a presentation issue. If they're off by 7%, you've got a real data integrity problem.

def summarize_source(df, label, key_col="order_id", revenue_col="revenue", units_col="units"):
    return {
        "source": label,
        "row_count": len(df),
        "unique_orders": df[key_col].nunique(),
        "total_revenue": df[revenue_col].sum().round(2),
        "total_units": df[units_col].sum(),
        "date_min": df["transaction_date"].min().date(),
        "date_max": df["transaction_date"].max().date()
    }

summary = pd.DataFrame([
    summarize_source(erp_clean, "ERP"),
    summarize_source(crm_clean, "CRM")
])

print(summary.to_string(index=False))

Output will look something like:

source  row_count  unique_orders  total_revenue  total_units   date_min   date_max
   ERP        500            500    3782451.23        12487 2024-01-01 2024-03-31
   CRM        495            495    3734218.76        12302 2024-01-01 2024-03-31

Now calculate discrepancies explicitly:

erp_total = erp_clean["revenue"].sum()
crm_total = crm_clean["revenue"].sum()

delta_abs = erp_total - crm_total
delta_pct = (delta_abs / erp_total) * 100

print(f"Revenue delta: ${delta_abs:,.2f} ({delta_pct:.2f}%)")
print(f"Order count delta: {len(erp_clean) - len(crm_clean)} rows")

---

Step 3: Regional and Time-Based Aggregate Reconciliation

A single top-line total that matches doesn't mean your data is clean — it might mean the errors cancel each other out. You need to reconcile at multiple levels of granularity. Start with region and month.

def agg_by_dims(df, label, date_col="transaction_date", region_col="region", 
                revenue_col="revenue", order_col="order_id"):
    return (
        df.groupby([df[date_col].dt.to_period("M"), region_col])
        .agg(revenue=(revenue_col, "sum"), orders=(order_col, "count"))
        .reset_index()
        .rename(columns={"transaction_date": "month"})
        .assign(source=label)
    )

erp_agg = agg_by_dims(erp_clean, "ERP")
crm_agg = agg_by_dims(crm_clean, "CRM")

# Merge on month + region
compare_agg = erp_agg.merge(
    crm_agg,
    on=["month", "region"],
    suffixes=("_erp", "_crm"),
    how="outer"
)

# Fill NaN where one source has no data for a dimension
compare_agg[["revenue_erp", "revenue_crm", "orders_erp", "orders_crm"]] = \
    compare_agg[["revenue_erp", "revenue_crm", "orders_erp", "orders_crm"]].fillna(0)

# Calculate deltas
compare_agg["revenue_delta"] = (compare_agg["revenue_erp"] - compare_agg["revenue_crm"]).round(2)
compare_agg["revenue_delta_pct"] = (
    (compare_agg["revenue_delta"] / compare_agg["revenue_erp"].replace(0, np.nan)) * 100
).round(2)
compare_agg["orders_delta"] = compare_agg["orders_erp"] - compare_agg["orders_crm"]

# Flag rows where discrepancy exceeds threshold
REVENUE_TOLERANCE_PCT = 1.0   # flag if >1% off
ORDER_TOLERANCE = 2           # flag if order count differs by more than 2

compare_agg["revenue_flag"] = compare_agg["revenue_delta_pct"].abs() > REVENUE_TOLERANCE_PCT
compare_agg["orders_flag"] = compare_agg["orders_delta"].abs() > ORDER_TOLERANCE

flagged = compare_agg[compare_agg["revenue_flag"] | compare_agg["orders_flag"]]
print(f"Flagged dimension combinations: {len(flagged)} of {len(compare_agg)}")
print(flagged[["month", "region", "revenue_erp", "revenue_crm", "revenue_delta_pct", "orders_delta"]].to_string())

The tolerance thresholds (REVENUE_TOLERANCE_PCT, ORDER_TOLERANCE) deserve discussion. Setting them as named constants at the top of your script — rather than hardcoding numbers inside conditions — means business stakeholders can negotiate what "acceptable" means and you just update one place. In financial reconciliation, a 1% tolerance is common for operational reporting. For regulatory or audit contexts, you might need to flag anything over 0.1%. Make this configurable.

---

Step 4: Row-Level Reconciliation — The Hard Part

Aggregate comparison tells you that there's a problem. Row-level reconciliation tells you where it is. This is where the real work happens.

The technique is a full outer merge on your key field (order_id), followed by systematic analysis of what the merge produced.

ROW_COMPARE_COLS = ["revenue_cents", "units"]

erp_keyed = erp_clean[["order_id", "transaction_date", "region", "sku", "revenue_cents", "units"]].copy()
crm_keyed = crm_clean[["order_id", "transaction_date", "region", "sku", "revenue_cents", "units"]].copy()

merged = erp_keyed.merge(
    crm_keyed,
    on="order_id",
    how="outer",
    suffixes=("_erp", "_crm"),
    indicator=True
)

print(merged["_merge"].value_counts())

both          465
left_only      35
right_only     15

The indicator=True parameter is your best friend in reconciliation work. It stamps every row with whether it came from both sources, only the left (ERP), or only the right (CRM). This immediately tells you:

• left_only: Orders in ERP but not in CRM — potential missed deals in CRM
• right_only: Orders in CRM but not in ERP — phantom deals, or deals not yet recognized in finance system
• both: Orders in both systems — these need value-level comparison

Let's classify each case:

# Rows only in ERP
erp_only = merged[merged["_merge"] == "left_only"].copy()
erp_only["discrepancy_type"] = "MISSING_FROM_CRM"

# Rows only in CRM
crm_only = merged[merged["_merge"] == "right_only"].copy()
crm_only["discrepancy_type"] = "MISSING_FROM_ERP"

# Rows in both — now check for value mismatches
both = merged[merged["_merge"] == "both"].copy()

both["revenue_mismatch"] = both["revenue_cents_erp"] != both["revenue_cents_crm"]
both["units_mismatch"] = both["units_erp"] != both["units_crm"]

both_mismatched = both[both["revenue_mismatch"] | both["units_mismatch"]].copy()
both_mismatched["discrepancy_type"] = "VALUE_MISMATCH"

both_clean = both[~(both["revenue_mismatch"] | both["units_mismatch"])].copy()
both_clean["discrepancy_type"] = "MATCHED"

print(f"\nReconciliation Summary:")
print(f"  Matched cleanly:      {len(both_clean)}")
print(f"  Value mismatches:     {len(both_mismatched)}")
print(f"  Missing from CRM:     {len(erp_only)}")
print(f"  Missing from ERP:     {len(crm_only)}")

For the value mismatch rows, calculate the exact difference:

both_mismatched["revenue_delta_cents"] = (
    both_mismatched["revenue_cents_erp"] - both_mismatched["revenue_cents_crm"]
)
both_mismatched["revenue_delta"] = both_mismatched["revenue_delta_cents"] / 100
both_mismatched["revenue_delta_pct"] = (
    both_mismatched["revenue_delta"] / 
    (both_mismatched["revenue_cents_erp"] / 100)
).round(4) * 100

# Show the worst offenders
print("\nLargest revenue mismatches:")
print(
    both_mismatched
    .nlargest(10, "revenue_delta_cents")[
        ["order_id", "revenue_cents_erp", "revenue_cents_crm", "revenue_delta", "revenue_delta_pct"]
    ]
    .assign(
        revenue_erp=lambda x: x["revenue_cents_erp"] / 100,
        revenue_crm=lambda x: x["revenue_cents_crm"] / 100
    )
    .drop(columns=["revenue_cents_erp", "revenue_cents_crm"])
    .to_string(index=False)
)

---

Step 5: Diagnosing Duplicate Keys

One subtlety that bites analysts in reconciliation is when the key field isn't actually unique. If order_id appears twice in either source, a merge will create a cartesian explosion — and your totals will be inflated by the number of duplicates. Always check for this before merging.

def check_key_integrity(df, key_col, source_label):
    total = len(df)
    unique = df[key_col].nunique()
    dupes = total - unique
    dupe_rows = df[df.duplicated(subset=[key_col], keep=False)]
    
    report = {
        "source": source_label,
        "total_rows": total,
        "unique_keys": unique,
        "duplicate_keys": dupes,
        "duplicate_pct": round((dupes / total) * 100, 2)
    }
    
    if dupes > 0:
        print(f"\n⚠ {source_label}: {dupes} duplicate key(s) found!")
        print(dupe_rows[[key_col]].value_counts().head(10))
    else:
        print(f"✓ {source_label}: All keys unique")
    
    return report

integrity_erp = check_key_integrity(erp_clean, "order_id", "ERP")
integrity_crm = check_key_integrity(crm_clean, "order_id", "CRM")

If you find duplicates in a source, you have a decision to make before you can reconcile: aggregate them (if they're legitimately split transactions), deduplicate (if they're data entry errors), or investigate them separately. This is a business logic question, not a technical one — but you can surface it clearly in your report. See Detecting and Resolving Data Quality Issues Across Merged DataFrames: Diagnosing Join Mismatches, Duplicate Keys, and Row Count Surprises in pandas for an exhaustive treatment of this problem.

---

Step 6: Reconciling Against the Finance Summary (Source C)

Now let's bring in the third source: the monthly finance summary. This is a different type of reconciliation because we're comparing transactional data (ERP) against pre-aggregated data (finance spreadsheet). We can't do row-level matching; we compare at the aggregate level only.

# Re-aggregate ERP data to match finance summary granularity
erp_monthly = (
    erp_clean
    .groupby([erp_clean["transaction_date"].dt.to_period("M"), "region"])
    .agg(
        erp_revenue=("revenue", "sum"),
        erp_orders=("order_id", "count")
    )
    .reset_index()
    .rename(columns={"transaction_date": "month"})
)

# Merge with finance data
finance_compare = erp_monthly.merge(
    finance_monthly,
    on=["month", "region"],
    how="outer"
)

finance_compare["revenue_delta"] = (
    finance_compare["erp_revenue"] - finance_compare["finance_revenue"]
).round(2)
finance_compare["revenue_delta_pct"] = (
    (finance_compare["revenue_delta"] / finance_compare["erp_revenue"]) * 100
).round(2)
finance_compare["orders_delta"] = (
    finance_compare["erp_orders"] - finance_compare["finance_orders"]
)

finance_compare["flag"] = (
    finance_compare["revenue_delta_pct"].abs() > REVENUE_TOLERANCE_PCT
) | (
    finance_compare["orders_delta"].abs() > ORDER_TOLERANCE
)

print("Finance vs ERP comparison:")
print(
    finance_compare[["month", "region", "erp_revenue", "finance_revenue", 
                      "revenue_delta", "revenue_delta_pct", "flag"]]
    .to_string(index=False)
)

---

Step 7: Building the Reconciliation Report

Now we assemble everything into a single, structured, exportable reconciliation report. A good reconciliation report has multiple sections, each answering a different question.

from openpyxl import Workbook
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
from openpyxl.utils.dataframe import dataframe_to_rows
import io

def build_executive_summary(erp_df, crm_df, row_level_results):
    """Build a one-row-per-metric executive summary."""
    
    erp_total = erp_df["revenue"].sum()
    crm_total = crm_df["revenue"].sum()
    delta = erp_total - crm_total
    
    matched = row_level_results["matched"]
    value_mismatch = row_level_results["value_mismatch"]
    missing_crm = row_level_results["missing_from_crm"]
    missing_erp = row_level_results["missing_from_erp"]
    
    mismatch_revenue_impact = value_mismatch["revenue_delta"].sum()
    missing_crm_revenue = (missing_crm["revenue_cents_erp"].fillna(0) / 100).sum()
    
    rows = [
        {"metric": "ERP Total Revenue", "erp": erp_total, "crm": crm_total, "delta": delta,
         "delta_pct": round(delta / erp_total * 100, 3), "status": "INFO"},
        {"metric": "Order Count", "erp": len(erp_df), "crm": len(crm_df),
         "delta": len(erp_df) - len(crm_df), "delta_pct": None, "status": "INFO"},
        {"metric": "Matched Orders (Clean)", "erp": matched, "crm": matched,
         "delta": 0, "delta_pct": 0.0, "status": "OK"},
        {"metric": "Value Mismatches", "erp": len(value_mismatch), "crm": len(value_mismatch),
         "delta": len(value_mismatch), "delta_pct": None, "status": "WARN"},
        {"metric": "Revenue Impact of Mismatches", "erp": mismatch_revenue_impact, "crm": None,
         "delta": mismatch_revenue_impact, "delta_pct": None, "status": "WARN"},
        {"metric": "Orders in ERP, Missing from CRM", "erp": len(missing_crm), "crm": 0,
         "delta": len(missing_crm), "delta_pct": None, "status": "ERROR"},
        {"metric": "Orders in CRM, Missing from ERP", "erp": 0, "crm": len(missing_erp),
         "delta": len(missing_erp), "delta_pct": None, "status": "ERROR"},
        {"metric": "ERP Revenue Not in CRM", "erp": missing_crm_revenue, "crm": 0,
         "delta": missing_crm_revenue, "delta_pct": None, "status": "ERROR"},
    ]
    return pd.DataFrame(rows)


row_level_results = {
    "matched": len(both_clean),
    "value_mismatch": both_mismatched,
    "missing_from_crm": erp_only,
    "missing_from_erp": crm_only
}

exec_summary = build_executive_summary(erp_clean, crm_clean, row_level_results)

Now write the whole thing to Excel with formatting:

def write_recon_report(
    exec_summary, 
    value_mismatches, 
    missing_from_crm, 
    missing_from_erp,
    finance_compare,
    filepath="reconciliation_report.xlsx"
):
    """Write a multi-sheet formatted reconciliation report."""
    
    # Color palette
    RED_FILL = PatternFill(start_color="FFCCCC", end_color="FFCCCC", fill_type="solid")
    YELLOW_FILL = PatternFill(start_color="FFF2CC", end_color="FFF2CC", fill_type="solid")
    GREEN_FILL = PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid")
    HEADER_FILL = PatternFill(start_color="1F4E79", end_color="1F4E79", fill_type="solid")
    HEADER_FONT = Font(color="FFFFFF", bold=True)
    
    STATUS_FILLS = {"ERROR": RED_FILL, "WARN": YELLOW_FILL, "OK": GREEN_FILL, "INFO": None}
    
    def write_df_to_sheet(ws, df, title=None, status_col=None):
        """Write a DataFrame to a worksheet with header styling."""
        start_row = 1
        if title:
            ws.cell(row=1, column=1, value=title).font = Font(bold=True, size=14)
            start_row = 3
        
        for r_idx, row in enumerate(dataframe_to_rows(df, index=False, header=True)):
            for c_idx, value in enumerate(row):
                cell = ws.cell(row=r_idx + start_row, column=c_idx + 1, value=value)
                if r_idx == 0:  # Header row
                    cell.fill = HEADER_FILL
                    cell.font = HEADER_FONT
                    cell.alignment = Alignment(horizontal="center")
                elif status_col and r_idx > 0:
                    status_val = row[df.columns.get_loc(status_col)] if status_col in df.columns else None
                    fill = STATUS_FILLS.get(str(status_val))
                    if fill:
                        cell.fill = fill
        
        # Auto-size columns (approximate)
        for col in ws.columns:
            max_len = max((len(str(cell.value or "")) for cell in col), default=0)
            ws.column_dimensions[col[0].column_letter].width = min(max_len + 4, 40)
    
    wb = Workbook()
    
    # Sheet 1: Executive Summary
    ws1 = wb.active
    ws1.title = "Executive Summary"
    write_df_to_sheet(ws1, exec_summary, title="Reconciliation Report — Q1 2024", status_col="status")
    
    # Sheet 2: Value Mismatches
    ws2 = wb.create_sheet("Value Mismatches")
    vm_display = value_mismatches[[
        "order_id", "revenue_cents_erp", "revenue_cents_crm", 
        "revenue_delta", "revenue_delta_pct", "units_erp", "units_crm"
    ]].copy()
    vm_display.insert(0, "status", "WARN")
    write_df_to_sheet(ws2, vm_display, title="Orders with Value Discrepancies", status_col="status")
    
    # Sheet 3: Missing from CRM
    ws3 = wb.create_sheet("Missing from CRM")
    missing_crm_display = erp_only[
        [c for c in erp_only.columns if c not in ["_merge", "discrepancy_type"]]
    ].copy()
    missing_crm_display.insert(0, "status", "ERROR")
    write_df_to_sheet(ws3, missing_crm_display, title="ERP Orders Not Found in CRM", status_col="status")
    
    # Sheet 4: Missing from ERP
    ws4 = wb.create_sheet("Missing from ERP")
    missing_erp_display = crm_only[
        [c for c in crm_only.columns if c not in ["_merge", "discrepancy_type"]]
    ].copy()
    missing_erp_display.insert(0, "status", "ERROR")
    write_df_to_sheet(ws4, missing_erp_display, title="CRM Orders Not Found in ERP", status_col="status")
    
    # Sheet 5: Finance comparison
    ws5 = wb.create_sheet("Finance vs ERP")
    fin_display = finance_compare.copy()
    fin_display["month"] = fin_display["month"].astype(str)
    fin_display.insert(0, "status", fin_display["flag"].map({True: "WARN", False: "OK"}))
    fin_display = fin_display.drop(columns=["flag"])
    write_df_to_sheet(ws5, fin_display, title="Finance Summary vs ERP Actuals", status_col="status")
    
    wb.save(filepath)
    print(f"Report saved: {filepath}")

write_recon_report(
    exec_summary=exec_summary,
    value_mismatches=both_mismatched,
    missing_from_crm=erp_only,
    missing_from_erp=crm_only,
    finance_compare=finance_compare
)

The result is a multi-sheet Excel workbook where every discrepancy is color-coded by severity (red for errors, yellow for warnings, green for clean), formatted with proper headers, and organized so a finance director can navigate directly to the section that concerns them. For more on building formatted workbooks like this, see Automating Excel Reports with pandas and openpyxl: Formatted Workbooks Without Manual Work.

---

Step 8: Adding a Discrepancy Classification Engine

In production reconciliation workflows, it's useful to automatically classify why a discrepancy likely occurred. This doesn't replace investigation, but it helps triage.

def classify_mismatch(row, tolerance_pct=5.0):
    """
    Classify the likely cause of a value mismatch based on patterns.
    Returns a string label.
    """
    delta_pct = abs(row["revenue_delta_pct"])
    revenue_erp = row["revenue_cents_erp"] / 100
    revenue_crm = row["revenue_cents_crm"] / 100
    
    # Rounding issue: very small delta
    if delta_pct < 0.1:
        return "ROUNDING"
    
    # FX conversion pattern: delta near common FX rates (e.g., ~8-9% for USD/GBP)
    # This is a placeholder for domain-specific rules
    if 7.0 < delta_pct < 10.0:
        return "POSSIBLE_FX_CONVERSION"
    
    # CRM entered deal without discount applied
    if revenue_crm > revenue_erp and delta_pct > 5:
        return "CRM_OVER_REPORTED"
    
    # ERP adjustment post-close (credit memo, etc.)
    if revenue_erp < revenue_crm and delta_pct > 5:
        return "POSSIBLE_CREDIT_MEMO"
    
    # Large unexplained discrepancy
    if delta_pct > 20:
        return "LARGE_UNEXPLAINED"
    
    return "MINOR_VARIANCE"

both_mismatched["classification"] = both_mismatched.apply(classify_mismatch, axis=1)

print("\nMismatch classification summary:")
print(both_mismatched["classification"].value_counts())

This kind of domain logic is best encapsulated in a module you can test and version separately. See Structuring a Reusable Data Analysis Project: Functions, Modules, Notebooks, and Scripts for the right way to organize this code.

---

Step 9: Automating the Reconciliation Run

A reconciliation report you have to rebuild from scratch every month isn't a workflow — it's a ritual. Turn this into a function that accepts source DataFrames and returns structured output:

from dataclasses import dataclass, field
from typing import Optional

@dataclass
class ReconciliationResult:
    """Structured container for all reconciliation outputs."""
    run_timestamp: str
    exec_summary: pd.DataFrame
    matched_rows: pd.DataFrame
    value_mismatches: pd.DataFrame
    missing_from_source_a: pd.DataFrame
    missing_from_source_b: pd.DataFrame
    aggregate_comparison: pd.DataFrame
    
    # Summary metrics for quick access
    total_rows_a: int = 0
    total_rows_b: int = 0
    matched_count: int = 0
    mismatch_count: int = 0
    missing_a_count: int = 0
    missing_b_count: int = 0
    
    def is_clean(self, revenue_tolerance_pct: float = 1.0) -> bool:
        """Returns True if reconciliation passes all thresholds."""
        if self.mismatch_count > 0:
            return False
        if self.missing_a_count > 0 or self.missing_b_count > 0:
            return False
        return True
    
    def print_summary(self):
        print(f"\n{'='*50}")
        print(f"RECONCILIATION REPORT — {self.run_timestamp}")
        print(f"{'='*50}")
        print(f"Source A rows:     {self.total_rows_a}")
        print(f"Source B rows:     {self.total_rows_b}")
        print(f"Matched (clean):   {self.matched_count}")
        print(f"Value mismatches:  {self.mismatch_count}")
        print(f"Missing from B:    {self.missing_a_count}")
        print(f"Missing from A:    {self.missing_b_count}")
        print(f"Status:            {'✓ CLEAN' if self.is_clean() else '✗ DISCREPANCIES FOUND'}")
        print(f"{'='*50}\n")


def run_reconciliation(
    source_a: pd.DataFrame,
    source_b: pd.DataFrame,
    key_col: str,
    value_cols: list,
    label_a: str = "Source A",
    label_b: str = "Source B",
    revenue_tolerance_pct: float = 1.0
) -> ReconciliationResult:
    """
    Run a complete reconciliation between two DataFrames.
    
    Parameters
    ----------
    source_a, source_b : DataFrames with identical schemas
    key_col : unique key to join on
    value_cols : list of columns to compare for mismatches
    label_a, label_b : names for the sources (for reporting)
    revenue_tolerance_pct : flag threshold for aggregate comparison
    
    Returns
    -------
    ReconciliationResult dataclass
    """
    timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    
    # Key integrity check
    for df, label in [(source_a, label_a), (source_b, label_b)]:
        dupes = df[key_col].duplicated().sum()
        if dupes > 0:
            print(f"⚠ Warning: {dupes} duplicate keys in {label}. De-duplicating on first occurrence.")
            df = df.drop_duplicates(subset=[key_col], keep="first")
    
    # Full outer merge
    merged = source_a.merge(
        source_b,
        on=key_col,
        how="outer",
        suffixes=(f"_{label_a.lower()}", f"_{label_b.lower()}"),
        indicator=True
    )
    
    # Split by merge status
    matched_both = merged[merged["_merge"] == "both"].copy()
    only_a = merged[merged["_merge"] == "left_only"].copy()
    only_b = merged[merged["_merge"] == "right_only"].copy()
    
    # Value comparison on matched rows
    mismatch_mask = pd.Series(False, index=matched_both.index)
    for col in value_cols:
        col_a = f"{col}_{label_a.lower()}"
        col_b = f"{col}_{label_b.lower()}"
        if col_a in matched_both.columns and col_b in matched_both.columns:
            mismatch_mask = mismatch_mask | (matched_both[col_a] != matched_both[col_b])
    
    value_mismatches = matched_both[mismatch_mask].copy()
    matched_clean = matched_both[~mismatch_mask].copy()
    
    # Build executive summary (simplified for the generic version)
    exec_rows = [
        {"metric": f"{label_a} Row Count", "value": len(source_a)},
        {"metric": f"{label_b} Row Count", "value": len(source_b)},
        {"metric": "Row Count Delta", "value": len(source_a) - len(source_b)},
        {"metric": "Matched (Clean)", "value": len(matched_clean)},
        {"metric": "Value Mismatches", "value": len(value_mismatches)},
        {"metric": f"In {label_a}, Not {label_b}", "value": len(only_a)},
        {"metric": f"In {label_b}, Not {label_a}", "value": len(only_b)},
    ]
    
    return ReconciliationResult(
        run_timestamp=timestamp,
        exec_summary=pd.DataFrame(exec_rows),
        matched_rows=matched_clean,
        value_mismatches=value_mismatches,
        missing_from_source_a=only_a,
        missing_from_source_b=only_b,
        aggregate_comparison=pd.DataFrame(),  # populated separately
        total_rows_a=len(source_a),
        total_rows_b=len(source_b),
        matched_count=len(matched_clean),
        mismatch_count=len(value_mismatches),
        missing_a_count=len(only_a),
        missing_b_count=len(only_b)
    )

# Run it
result = run_reconciliation(
    source_a=erp_clean,
    source_b=crm_clean,
    key_col="order_id",
    value_cols=["revenue_cents", "units"],
    label_a="ERP",
    label_b="CRM"
)
result.print_summary()

Once you have a clean run_reconciliation() function, you can connect it to a scheduler and run it daily without manual intervention. The is_clean() method on the result can be used as a gate in a pipeline — if it returns False, alert a Slack channel or send an email. See Building and Automating Recurring Reports with pandas: Scheduling Scripts to Run Without You for how to wire that up.

---

Hands-On Exercise

Work through this scenario with the data you built earlier. The goal is to produce a complete reconciliation report without referencing the code above more than necessary.

Scenario: Your finance team says Q1 total revenue should be between $3.7M and $3.9M based on bank deposits. Your ERP shows $3.78M. Your CRM shows $3.73M. The finance spreadsheet shows $3.76M. Your job is to produce a reconciliation report that:

1. Confirms the top-line delta between ERP and CRM (absolute and percentage).
2. Identifies exactly which orders are missing from each system.
3. Finds all orders where the revenue value differs between ERP and CRM.
4. Identifies which month-region combinations are farthest out of tolerance.
5. Produces a final Excel report with color-coded sheets for each category.

Extend the challenge:

• Add a fourth column to the value comparison: units. How many orders have unit count mismatches but no revenue mismatch? What might that indicate?
• Write a function that takes a list of order_id values and returns whether each one is "clean" (matched in both sources with matching values), so a finance analyst can look up individual orders.
• Add a date_range filter to run_reconciliation() so you can reconcile only January orders without having to pre-filter outside the function.

---

Common Mistakes & Troubleshooting

Mistake 1: Comparing Floats Directly

# Wrong
df["revenue_erp"] == df["revenue_crm"]

# Correct: integer cents, or np.isclose for small tolerances
np.isclose(df["revenue_erp"], df["revenue_crm"], rtol=1e-5, atol=0.01)

The two-cent discrepancy that makes your reconciliation fail might literally be a float representation artifact, not a real discrepancy. Define "equal" explicitly.

Mistake 2: Forgetting That a Full Outer Merge Creates NaN in Value Columns

# Wrong — will throw errors or silently compare NaN != NaN as True
both = merged[merged["_merge"] == "both"]
mismatches = both[both["revenue_erp"] != both["revenue_crm"]]

# Correct — filter first, then compare
both = merged[merged["_merge"] == "both"].copy()
# At this point, revenue_erp and revenue_crm should both be non-null for "both" rows
# But if your key has nulls or there are type coercion issues, verify:
assert both[["revenue_cents_erp", "revenue_cents_crm"]].isna().sum().sum() == 0, \
    "Unexpected nulls in matched rows — check for null keys or type mismatches"

Mistake 3: Using the Wrong Key

If order_id isn't truly unique — if the same order can be split across lines, or if order IDs are reused across years — your reconciliation will produce garbage. Before committing to a key, verify it's actually a business key, not just something that looks like one. Composite keys (order + line number, for example) are common in ERP systems.

# Validate composite key uniqueness
assert erp_clean.duplicated(subset=["order_id"]).sum() == 0, \
    "order_id is not unique in ERP — check for split lines or reused IDs"

Mistake 4: Treating Date Mismatches as Non-Issues

It's tempting to ignore rows where the date differs but the revenue matches. Don't. A deal that closed in December showing up in January in the CRM is a revenue recognition timing difference — which is a compliance issue, not a data quality issue. Add date comparison to your mismatch detection, but categorize it separately.

both["date_mismatch"] = (
    both["transaction_date_erp"].dt.to_period("M") != 
    both["transaction_date_crm"].dt.to_period("M")
)
both_date_mismatch = both[both["date_mismatch"] & ~both["revenue_mismatch"]]
print(f"Period-only mismatches (same value, different month): {len(both_date_mismatch)}")

Mistake 5: Building the Report Before Validating the Inputs

Always validate that your input DataFrames have the expected structure before running reconciliation logic. A column rename in an upstream process will silently produce an empty merge. Add explicit assertions:

REQUIRED_ERP_COLS = ["order_id", "transaction_date", "region", "sku", "revenue_cents", "units"]
missing = set(REQUIRED_ERP_COLS) - set(erp_clean.columns)
assert not missing, f"ERP DataFrame missing expected columns: {missing}"

---

Performance Considerations for Large Datasets

For datasets with millions of rows, the full outer merge approach becomes expensive. Here are targeted optimizations:

Pre-filter by time window. If you're running a monthly reconciliation, filter both sources to the current period before merging. A merge of 50K rows against 50K rows is orders of magnitude faster than 5M vs 5M, even when most rows will match.

Use integer keys. Merging on string keys ("ORD-00001") is slower than merging on integers. If you can create a numeric version of your order key, do it.

Sort before merging. pandas' merge algorithm is faster when the key column is sorted.

erp_clean = erp_clean.sort_values("order_id")
crm_clean = crm_clean.sort_values("order_id")

Chunked reconciliation for very large datasets. If you're working with datasets too large to fit in memory, consider reconciling by time window in a loop and concatenating results. See Handling Large Datasets in Python: Chunked Reading, Efficient dtypes, and When to Use Polars for the mechanics of chunked processing.

---

Summary & Next Steps

Let's take stock of what you've built. Starting from three heterogeneous data sources with real-world messiness — naming differences, missing rows, value discrepancies, summary vs. transactional granularity — you built a complete reconciliation pipeline that:

• Normalizes data to a canonical schema before any comparison
• Validates key uniqueness and data types before merging
• Compares at multiple granularities: top-line totals, regional/monthly aggregates, and row-level detail
• Classifies discrepancies into actionable categories: MISSING_FROM_CRM, MISSING_FROM_ERP, VALUE_MISMATCH, ROUNDING, etc.
• Encapsulates the workflow in a reusable run_reconciliation() function with a structured result type
• Produces a formatted Excel report that finance and operations teams can navigate without pandas knowledge

The reconciliation patterns here — normalize first, merge with indicator=True, split by merge result, compare value columns on matched rows — are directly portable to any reconciliation problem you'll encounter. The specific columns and thresholds change; the logic doesn't.

Where to go from here:

• Connect your ERP and CRM data directly from their databases instead of CSV exports. See Reading from SQL Databases into pandas with SQLAlchemy for how to do that cleanly.
• Profile your incoming data before reconciliation to catch format changes early. Validating and Profiling a New Dataset with pandas: Row Counts, Distributions, and Outlier Checks Before You Analyze covers this systematically.
• Turn this into a scheduled, automated process that runs at month-end without you in the loop. Building a Reusable ETL Pipeline in pandas: Extract, Transform, and Load Data from Multiple Sources into a Clean, Analysis-Ready Output is the next natural step.

Data that's been reconciled is data that can be trusted. And data that can be trusted is the only data worth analyzing.