Compare two dataframe snapshots with stable keys

Align snapshots by a stable business key, separate added and removed keys, then compare values on shared keys. Comparing row positions can report false changes when an export is merely reordered.

Before comparing, verify that each snapshot has one row per key and that the fields use compatible units and types.

Identify the three kinds of change

The pandas quality lab supplies two tiny snapshots. P01 is unchanged, P02 increases from 5,000 to 5,500 paise, P03 is removed and P04 is added with a zero amount.

import pandas as pd
from build_and_verify import ROOT

types = {'order_id': 'string', 'amount_paise': 'Int64'}
before = pd.read_csv(ROOT / 'snapshot_before.csv', dtype=types).set_index('order_id')
after = pd.read_csv(ROOT / 'snapshot_after.csv', dtype=types).set_index('order_id')
assert before.index.is_unique and after.index.is_unique
added = after.index.difference(before.index)
removed = before.index.difference(after.index)
shared = before.index.intersection(after.index).sort_values()
changes = before.loc[shared].compare(after.loc[shared], result_names=('before', 'after'))
assert added.tolist() == ['P04']
assert removed.tolist() == ['P03']
assert changes.index.tolist() == ['P02']
assert changes.loc['P02', ('amount_paise', 'before')] == 5000
assert changes.loc['P02', ('amount_paise', 'after')] == 5500
print(changes)

The DataFrame.compare reference explains comparison of identically labeled frames. Added and removed keys are handled separately because they are not part of that aligned shared-key comparison.

Reconcile the total change

import pandas as pd
from build_and_verify import ROOT

types = {'order_id': 'string', 'amount_paise': 'Int64'}
before = pd.read_csv(ROOT / 'snapshot_before.csv', dtype=types).set_index('order_id')
after = pd.read_csv(ROOT / 'snapshot_after.csv', dtype=types).set_index('order_id')
shared = before.index.intersection(after.index)
added_value = int(after.loc[after.index.difference(before.index), 'amount_paise'].sum())
removed_value = int(before.loc[before.index.difference(after.index), 'amount_paise'].sum())
changed_value = int((after.loc[shared, 'amount_paise'] - before.loc[shared, 'amount_paise']).sum())
delta = int(after['amount_paise'].sum() - before['amount_paise'].sum())
assert (added_value, removed_value, changed_value) == (0, 20000, 500)
assert delta == added_value - removed_value + changed_value == -19500
print({'before_paise': 35000, 'after_paise': 15500, 'delta_paise': delta})

The total falls by 19,500 paise even though the one changed shared record increases. A headline delta alone would hide that the removed record drives the result.

This arithmetic assumes the compared amounts are all observed and share the same unit. If one snapshot has a missing amount, report that coverage change rather than treating subtraction through missing values as a complete explanation.

Distinguish removal from business deletion

P03's absence in the later file means it is absent from that snapshot. It does not automatically prove a cancelled order. The later extract might use a different filter, be incomplete or represent a different reporting date.

Compare extraction parameters and source-completeness evidence before assigning a business interpretation. The same key can also refer to different grains after a schema change, which invalidates a direct comparison.

Make equality fit the field

Integer minor-unit amounts can be compared exactly under this contract. Floating-point model outputs may require a documented tolerance. Text labels may require a controlled normalization, but raw values should remain available when the normalization affects interpretation.

For nullable fields, explicitly distinguish both missing, newly missing and newly supplied values. A generic inequality mask can propagate missing Boolean results; inspect its behavior before using it to select changed records.

Preserve provenance for both sides

Store each snapshot's source identifier or hash, schema version, extraction period and comparison code revision. A file named latest.csv is not enough to reproduce what was compared after the next export overwrites it.

Exercise: shuffle the later snapshot and verify that the keyed result is unchanged. Then duplicate P02 and confirm that uniqueness validation stops the comparison instead of producing a many-to-many change table.

NeuraPath's Data Analytics with Generative AI course connects pandas comparison with report reconciliation. A useful change report explains both the aggregate movement and the exact records behind it.

Continue learning

This article is part of the Pandas wrangling and data checks sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Build a reusable data-quality report in Python.
• Continue with Pandas Copy-on-Write: avoid ambiguous chained assignment.