Match the specimens, then count what is missing
A plate-reader export had measurements for A01 and A03. The measurement plan listed A01 and A02. A join that only keeps shared IDs would show one clean row and hide two different problems. A02 was expected but has no result. A03 has a result but no planned specimen to attach it to.
Use a synthetic example to test the join before running it on the full file.
planned_rows = [("A01", "control"), ("A02", "treatment")]
observed_rows = [("A01", 0.24), ("A03", 0.19)]
def by_specimen(rows):
indexed = {}
for specimen_id, value in rows:
if specimen_id in indexed:
raise ValueError(f"Duplicate specimen ID: {specimen_id}")
indexed[specimen_id] = value
return indexed
planned = by_specimen(planned_rows)
observed = by_specimen(observed_rows)
matched = sorted(planned.keys() & observed.keys())
missing_result = sorted(planned.keys() - observed.keys())
unplanned_result = sorted(observed.keys() - planned.keys())
print("Matched:", matched)
print("Expected, no result:", missing_result)
print("Result, no planned ID:", unplanned_result)
Matched: ['A01']
Expected, no result: ['A02']
Result, no planned ID: ['A03']
Before analysis, check the run log for A02 and compare the raw export with the plate map for A03. Preserve missing values and log any correction to an ID. If the protocol intentionally measures each specimen several times, use the specimen ID and reading number together before applying the duplicate check.
For the handoff, retain both unjoined tables and the mismatch report. A reviewer should be able to account for every planned specimen and every returned result. The example is synthetic and was checked locally. It was prepared with AI assistance for the RigorMed research workspace.
Top comments (0)