The Join That Added 3,664 Rows

Why the row count grew. A left join keeps every row of orders and attaches matching rows from customers. If a customer_id appears k times on the right, every order for that customer is duplicated k times. The surplus is 3,664 rows, so a handful of customer IDs are duplicated - three IDs appearing twice explains only part of it; she should check for IDs appearing three or more times with customers['customer_id'].value_counts().loc[lambda s: s > 1].

Why drop_duplicates() is the wrong fix.

• With no subset, pandas compares all columns. Two genuinely distinct orders with the same customer, amount, and date would collapse into one, silently deleting revenue.
• With subset=['customer_id'], it keeps whichever duplicate row happens to come first. The kept region value is then arbitrary - for the EMEA/Europe customer, the report would assign that customer's revenue to a region by file order, not by fact.
• It treats a symptom. The real defect is in customers.csv, and it will recur on every refresh.

The honest sequence is: quantify the duplicates, resolve them at the source or via an explicit, documented rule, deduplicate customers so the key is unique, and then join. A useful guard is assert len(merged) == len(orders), which fails loudly the moment cardinality changes.

Dtype first, because it changes the answer. In current pandas, merging int64 against object raises a ValueError about merging on int64 and object columns; in older versions it may proceed and match nothing. Either way, the tempting fix - casting both to int - is destructive: '0012345' becomes 12345, which may be a different real customer's ID, or the cast fails on the non-numeric strings. The safe move is to read both files with an explicit schema:

orders = pd.read_csv('orders.csv', dtype={'customer_id': 'string'})
customers = pd.read_csv('customers.csv', dtype={'customer_id': 'string'})

Then verify overlap with a set intersection before joining. If, say, only 8,900 of 12,040 IDs match, that is a finding to report, not a detail to paper over - it means the leading-zero IDs need canonicalising at the source.

Which duplicate to keep. There is no data-side answer; there is a business rule. Ask the CRM owner which record is authoritative - typically the one with the latest updated_at, or one flagged is_primary. The EMEA/Europe conflict cannot be resolved by picking the first row, because region is the report's grouping variable: an arbitrary choice reassigns real revenue between regions in a deck a VP will present. Until the owner confirms, the defensible output is a small table of the affected IDs, their conflicting values, and the revenue they represent, plus a total flagged as provisional.

Checks that pay for themselves in three minutes:

1. Key uniqueness after cleaning - assert customers['customer_id'].is_unique. Protects against the duplicate explosion recurring silently.
2. Cardinality preservation - assert len(merged) == len(orders). Any increase means the right side is still non-unique.
3. Measure invariance - assert merged['order_amount'].sum() == orders['order_amount'].sum(). Revenue lives in orders; a join must not change its total. This is the single check that would have caught the original problem.
4. No nulls in the grouping key - merged['region'].isna().sum() == 0. Unmatched customers produce NaN regions that pandas drops from groupby by default, so the regional totals quietly fail to sum to the grand total.
5. Reconciliation - compare the grand total against the finance system's quarter figure; a join error is usually visible as a percentage-point gap.

Finally, save the dedupe mapping as a file next to the report and add a one-line footnote: N duplicate customer records, rule applied, M rows affected. That converts an invisible judgement call into something a reviewer can audit, which matters more than shaving the last twenty minutes off the deadline.