DEV Community

Catch a pandas join that silently multiplies attendance rows

An attendance table has five check-ins. After a join, it has six rows. Nothing crashed, but the report is already wrong. A duplicated learner ID in the roster produced two matches for one check-in. This fictional example makes that failure visible before building any totals. Run the example All illustrative records are fictional. Save the following as attendance_join.py. The complete script was run in PythonIDE on iPhone; the screenshots show the actual source and output. import pandas as pd

Fictional learner roster and workshop check-ins.

roster = pd.DataFrame({
'id': ['S1', 'S2', 'S2', 'S3', 'S4'],
'name': ['Ari', 'Bo', 'Bea', 'Cy', 'Dee'],
})
visits = pd.DataFrame({
'id': ['S1', 'S2', 'S1', 'S9', 'S3'],
'session': ['AM', 'AM', 'PM', 'PM', 'PM'],
})
print('FICTIONAL ATTENDANCE CHECK')
print('Visit rows before join:', len(visits))
unsafe = visits.merge(roster, on='id', how='left')
print('Rows from unchecked join:', len(unsafe))

Many visits may match one learner, never two identities.

try:
visits.merge(roster, on='id', how='left',
validate='many_to_one')
except pd.errors.MergeError:
print('Guard caught a non-unique roster key.')

Do not choose arbitrarily between conflicting identities.

ambiguous = roster['id'].duplicated(keep=False)
blocked_ids = set(roster.loc[ambiguous, 'id'])
unique_roster = roster.loc[~ambiguous]
report = visits.merge(
unique_roster, on='id', how='left',
validate='many_to_one', indicator=True,
)
report['review'] = report['_merge'].eq('left_only')
print('\nJOINED REPORT')
print(report[['id', 'session', 'name', 'review']].to_string(index=False))
print('\nREVIEW REASONS')
for learner in report.loc[report['review'], 'id'].unique():
reason = 'ambiguous identity' if learner in blocked_ids else 'unknown ID'
print(f'{learner}: {reason}')

Count people, not check-ins. S1 attended twice.

known = report.loc[~report['review']]
print('\nMatched check-ins:', len(known))
print('Distinct matched learners:', known['id'].nunique())
assert len(report) == len(visits)
assert len(known) == 3
assert known['id'].nunique() == 2
print('Row conservation and distinct-count checks passed.')
Understand the checks and their limits validate="many_to_one" states the intended relationship: many check-ins can refer to one learner. A repeated roster key violates that assumption. Rather than keep the first conflicting name, the example quarantines every roster row belonging to an ambiguous ID. A left join then preserves every check-in and indicator=True shows unmatched entries. The actual iPhone run started with five check-ins and produced six rows in the unchecked join. The many-to-one guard caught the duplicated roster key. The guarded report retained five rows, flagged S2 as ambiguous and S9 as unknown, and counted three matched check-ins belonging to two distinct learners. All row-conservation and distinct-count assertions passed. Those are different metrics. This fixture uses nonmissing string IDs; real keys need normalization and missing-key checks. pandas can match null join keys, unlike the SQL behavior many people expect. Try one change Resolve S2 by replacing the two conflicting roster rows with one verified row. Predict matched check-ins and distinct learners. Then add a repeated check-in and decide whether the session plus learner ID should be unique. I build PythonIDE, a Python coding app for iPhone and iPad. The example is intended to teach a workflow you can inspect and adapt. AI assisted with drafting and translation. The example was checked in PythonIDE. PythonIDE on the App Store

Powered by Readybot.io