Nightly Pandas Merge That Drops Stores and Margins
A retail chain's nightly Python job merges 40 store CSV files with a store master table and produces a margin report. Store IDs look like '00123' in the CSVs; pandas reads them as integers, while the master stores them as strings. The merge silently drops about 8% of rows. The job then runs df[df['amount'] > 0]['margin'] = df['amount'] - df['cost'] and later df.groupby('store_id')['margin'].sum(). Analysts report that margin is often NaN and that stores with missing IDs disappear. A date column contains mixed formats ('2024-01-05', '05/01/2024', 'not available'); pd.to_datetime(..., errors='coerce') turns some valid dates into NaT. The job currently has no row-count assertions and overwrites the previous output. The lead asks for a diagnosis and a safer pipeline before month-end close.
1. Diagnose the row loss in the merge and the NaN margins. What pandas behaviors cause each?
Read the worked answer
The merge loses rows because the join keys have different dtypes and representations. '00123' read by pandas becomes integer 123; the master has string '00123'. Depending on the pandas version, the merge either raises a dtype error or coerces to object and fails to match, so a left merge produces NaN master columns and an inner merge drops rows. The 8% loss is consistent with stores whose IDs have leading zeros. The assignment df[df['amount'] > 0]['margin'] = ... is chained indexing: the boolean filter returns a copy, so the assignment writes to a temporary object, not df; margins remain NaN. groupby('store_id') drops NaN keys by default, so rows with missing IDs vanish from the report. Date parsing with errors='coerce' without an explicit format turns invalid values into NaT and may also misinterpret ambiguous day/month values, so valid dates can be lost or shifted.
2. Rewrite the pipeline steps safely and add validation checks before overwriting the output.
Read the worked answer
Read IDs as strings and normalize before merging: df = pd.read_csv(path, dtype={'store_id': str}) df['store_id'] = df['store_id'].str.strip().str.zfill(5) master['store_id'] = master['store_id'].astype(str).str.zfill(5) df = df.merge(master, on='store_id', how='left', validate='many_to_one', indicator=True) Check the _merge counts and fail if unmatched exceeds a threshold. Compute margin with .loc: mask = df['amount'].notna() & df['cost'].notna() df.loc[mask, 'margin'] = df.loc[mask, 'amount'] - df.loc[mask, 'cost'] Group with dropna=False or assign an 'UNKNOWN' store before grouping. Parse dates with explicit formats: df['date'] = pd.to_datetime(df['date_raw'], format='%Y-%m-%d', errors='coerce') fallback = pd.to_datetime(df['date_raw'], format='%d/%m/%Y', errors='coerce') df['date'] = df['date'].fillna(fallback) Add assertions: input row count, post-merge row count equals input, unmatched rate < 1%, margin non-null rate, NaT rate, and ID regex ^\d{5}$. Write to a temp file and atomically replace the previous output; log counts.