Data cleaning starts with defining what each row represents, which fields identify it and how exceptions may be handled. A program’s success message establishes that an operation ran, not that duplicate keys, unmatched records or missing values were treated appropriately. AI can help inspect and execute a specified workflow while retaining rules and outputs that let a researcher trace each decision back to its inputs.
Inspect the cleaning and merge case. It uses two originally generated simulated tables and independently executes Python and Stata. Inputs, code, row audits, all merge statuses and a numerical cross-check are available. The example illustrates processing, not real firms or research findings.
Define the two keys
The master represents firm-year observations keyed by firm_id + year. The region lookup should contain one valid region per firm and is keyed by firm_id. Firms may appear in several years in the master, so the relationship is many-to-one. Stata implements this with merge m:1 firm_id; verify master firm-year uniqueness and lookup firm uniqueness first. Official merge documentation
The fixture defines firm_id as five-digit text, preserving leading zeros in 00101. Year must have four digits. Revenue is a nonnegative number with optional correctly grouped thousands commas; employees is a nonnegative integer. Blank values remain missing. These rules belong to the fixture dictionary and must be reconsidered for actual sources. An identifier should not become numeric merely because it looks like a number.
Separate exact duplicates from conflicting keys
Trim surrounding whitespace, then validate keys and values. The master contains an invalid year, a missing identifier and an unparseable revenue. These are recorded and quarantined rather than silently converted into valid missing values. The Stata program labels invalid inputs before numeric conversion; its subsequent force option prepares calculations without admitting those rows into the valid data.
When normalized valid content is identical, the stated rule retains the earliest source row and labels extra copies duplicate_removed. Provenance row numbers are excluded from the content comparison, and raw CSV remains unchanged. Stata’s duplicates documentation describes duplicate detection; deduplicating only by a key differs from comparing complete content.
A shared key with different content requires a separate decision. One master firm-year has two revenue values and one lookup firm has two regions. This case marks every member of those groups key_conflict and preserves source rows for investigation. It does not arbitrarily choose the first value. Quarantining these records changes merge coverage and must be reported.
Retain all merge statuses
The master starts with 16 rows: 3 invalid, 1 extra exact duplicate and 2 conflicting rows are held out, leaving 10 observations. The lookup starts with 10 rows and retains 6 after the corresponding rules. Both independently executed implementations agree on counts and row statuses.
| Merge status | Count | Meaning |
|---|---|---|
| matched | 8 | Master observation has a valid region match |
| master_only | 2 | Firm-year exists without a valid lookup match |
| lookup_only | 1 | Lookup record has no master firm-year observation |
The union therefore contains 11 rows. A lookup-only record has no master year and is not an additional observed firm-year. merged-all.csv retains every status; analysis-ready.csv retains all 10 cleaned master observations, including unmatched ones. One revenue and one employees value remain missing without imputation. Subsequent estimation still needs a complete-case check for its actual variables.
Dropping every unmatched row produces another sample. This may suit a research objective only when the reason and coverage are explained. Here, master-only records relate to a conflicting lookup key; inventing a region is not a repair.
Give Marx Max a specific processing request
Opening the case in Marx Max creates a task and material instructions. Its package includes raw simulated tables and their dictionary. A bounded request is:
Read README and the dictionary. Check firm-year and region keys first. Preserve raw files and report invalid fields, exact duplicates and conflicting keys. Apply the specified rules before a many-to-one merge. Export row audits and every merge status without imputing missing data or arbitrarily choosing conflicting values. Compare current outputs against references.
Read only selected inputs and write new files to the practice directory. For research data, first establish provenance, identifier meaning, year coverage and the merge relationship. An unresolved conflict can remain a documented open item; completing a tidy table is not a reason to invent its value. See the separate research-data and AI guide for transmission boundaries.
Rerun independently and check rows
Extract into a new directory and retain reference outputs. Python uses only its standard library: run python clean_merge.py --output-dir rerun/python from the package root. In a licensed independent Stata session, create rerun first, then run do clean_merge.do rerun/stata. Stata clears that session and overwrites named files in the specified output directory: save existing work first.
Compare audits by source row and inspect cleaned tables, merge outputs and counts.csv. Matching counts alone are insufficient; identifiers, years, values and statuses must also agree. cross-check.json records the published independent comparison. A newly produced log provides evidence of the current run rather than reusing the reference log.
The calculations ran on macOS, without Windows hardware coverage or a model executing the entire task. The programs demonstrate the fixture’s rules and cannot replace decisions about a real database or research sample. Continue with Stata model comparison and regression results to Word, retaining the provenance of preprocessing and sample changes.