combine_first & update
Python · Combining DataFrames (SQL Joins & Unions)
Theory
Both methods patch one table with values from another, aligning on index and column labels rather than position. a.combine_first(b) returns a new DataFrame that keeps a's values and fills a's missing values (NaN) from b; rows or columns that exist only in b are added. It answers 'use the primary source, fall back to the backup'. A Series version works the same way: s1.combine_first(s2), much like SQL's COALESCE(a.phone, b.phone).
a.update(b) works in place and the other way round: non-missing values in b overwrite a's values at matching labels, NaN in b never overwrites, and rows only in b are ignored. It answers 'apply these corrections'. Since alignment is by label, set the business key as the index first: crm.set_index('cust_id'). update() modifies a and returns None, so do not assign its result. For column-level choice, fillna() with another Series, or np.where, are alternatives.
Why we use it
Data often comes from several partial sources - a CRM missing phone numbers, a correction file with a few fixed emails. combine_first and update merge these by key in one call without writing a join plus many fillna or where steps.
When to use it
Use combine_first to fill gaps from a fallback source, update to overwrite with a list of corrections. Set the key as the index on both frames first. If you need to keep both versions side by side, use merge with suffixes instead.
How it works
- Two frames with the same key as index
- Fill gaps (primary wins)? Yes: primary.combine_first(backup). No: Apply corrections.
- main.update(fixes): non-NaN fixes overwrite matching cells
- Check the result; update has changed main in place
- Patched DataFrame
Syntax
primary.combine_first(backup) # fill NaN from backup, new frame
main.update(fixes) # overwrite in place with non-NaN fixes
both aligned on index + column labelsExample
crm = crm.set_index('cust_id')
filled = crm.combine_first(erp.set_index('cust_id'))Key points
- Both align by index and column labels - set the key as the index first
- combine_first: caller wins, gaps filled from the other; returns a new frame
- update: the other wins where it is not NaN; works in place, returns None
- update never adds new rows; combine_first does
- SQL equivalent: COALESCE(crm.city, erp.city) after a FULL OUTER JOIN on cust_id