Data cleaning and merge

Open in Marx Max

Inspect invalid fields, exact duplicates and conflicting keys in original simulated tables. Download inputs, row audits, independent processing code and actual logs.

Read README and the dictionary, download the original simulated package into reference/ and retain raw inputs and references. Check the master firm_id + year key and lookup firm_id key while preserving leading zeros. Validate fields before separating normalized exact duplicates from conflicting keys. Quarantine invalid inputs, retain the earliest source row of exact duplicates and hold out every conflicting-key row without arbitrarily selecting a value. Independently execute Python and Stata many-to-one merges into rerun/, preserving audits, clean tables, all statuses, master analysis-ready observations and actual logs. Do not impute missing data, silently drop unmatched rows or count lookup-only records as observed firm-years. Compare source rows, keys, values and counts. The reference has 10 master observations, 6 lookup rows, 8 matches, 2 master-only and 1 lookup-only; investigate discrepancies rather than changing inputs to match counts.

Actual simulated cleaning and merge counts: 16 master rows become 10, with 8 matched, 2 master-only and 1 lookup-only. — Counts come from actual execution; the package contains row-level disposition and provenance.

Download the complete rerun package (ZIP)

Read this case guide

Which records entered the merge?

Input	Raw	Invalid	Extra duplicates	Conflict rows held	Retained
Firm-year master	16	3	1	2	10
Region lookup	10	1	1	2	6

All inputs are originally generated simulations. Check firm-year and firm keys, preserve text-ID leading zeros, quarantine invalid values before removing extra exact duplicates, and hold out all conflicting-key rows. Independent Python/Stata executions agree on counts, row statuses, clean values and merged data. Raw inputs remain and missing values are not imputed.

Why 11 merged rows but 10 master observations?

There are 8 matched, 2 master-only and 1 lookup-only records. merged-all.csv retains every status. A lookup-only row has no master year and is not a new observed firm-year. analysis-ready.csv retains all 10 cleaned master observations, including unmatched ones, with one missing revenue and one missing employees value.

Python needs only the standard library: run python clean_merge.py --output-dir rerun/python from the root. In an independent Stata session, create rerun then run do clean_merge.do rerun/stata. Retain reference results and compare source rows and actual values. Fixture rules do not replace real-data definitions or sample decisions. Windows hardware and a model executing the workflow were not covered.

Continue to Stata model comparison · Return to the guide