pandas Merge Validation: Join Cardinality Checks and Row Count Reconciliation
Join tables safely and prove nothing was lost or duplicated.
The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.
[VERIFY] is an editorial flag, not a blank to fill. Wherever the prompt shows [VERIFY], check that item against a reliable source before you use it.
Help me merge two tables in pandas safely. Table A columns and a few invented rows: [TABLE_A]. Table B columns and a few invented rows: [TABLE_B]. The key columns and what each row represents in each table (one row per what?): [KEYS_AND_GRAIN]. The join I intend (left, inner, outer): [INTENDED_JOIN]. Explain in plain words how one-to-one, one-to-many and many-to-many joins change the row count: 3 orders joined to 7 order lines gives 7 rows, so summing an order-level amount afterwards would overcount. Use only the columns I listed. Give code blocks with comments and the expected printed output for my sample rows, and explain each line so I can adapt it. Describe a checking function: take the two tables and the key, print rows, unique keys and duplicated keys in each, merge with an indicator, print counts of matched, left only and right only, and raise an error if the post-merge row count differs from expected (3 orders and 7 lines should give 7 rows, not 3 or 21). Use the validate option [VERIFY for your pandas version] and test with deliberately duplicated keys. Causes of non-matching keys: leading zeros, trailing spaces, mixed int and string types, different date formats, case; give code to normalise them, and debug by sampling twenty unmatched keys and comparing their representations. Reconciliation table template: rows in A, rows in B, matched, unmatched in each, rows after merge, explanation. Treat version-dependent syntax as unconfirmed [VERIFY].
Fill in before use
Optional: type your details and the prompt above updates. Nothing is saved or sent.
Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.