Thomas Eapen · Selected work

Search for matches, then count once

Separate finding relevant records from deciding what constitutes one count.

Synthetic example · independently invented dataOne-page print edition · A3 landscape
01 Inspect the inputs02 Apply the method03 Check the result

INVENTED INPUTS

An event log with a repeat

Eight invented event rows
CaseStage
Case AIntake
Case AReview
Case AReview
Case BIntake
Case BSpecialist
Case CReview
Case CSpecialist
Case DIntake

Case A has two Review rows. Case C reaches both selected stages. The target stages are Review and Specialist.

SELECTED PYTHON

The working calculation

Pandas is pd. rows is the log at left; targets contains the two selected stage labels.

events = pd.DataFrame(rows)
matches = events.loc[events['stage'].isin(targets)]
distinct = matches.drop_duplicates(['case', 'stage'])
counts = distinct.groupby('stage').size().reindex(
    targets, fill_value=0
)
any_match = distinct['case'].nunique()

The code shown here is executed to calculate this example’s results.

COMPUTED RESULT

Distinct matches

Distinct cases reaching each selected stage
StageCases
Review2
Specialist2
3cases with any target match5 matching rows → 4 distinct case/stage pairs
Distinct cases per selected stageReview: 2; Specialist: 2.Review2Specialist2

Case A counts once for Review. Case C counts once in each stage, but only once in the “any match” total.

What this demonstratesThe counting unit is part of the method. Matching rows, distinct case/stage pairs and distinct cases answer different questions.

Check the boundaryDo not add the two stage counts to obtain the number of cases: a case can appear in both stages. This is a new demonstration of the search/counting idea.