In pandas, when do you combine two DataFrames with merge, with DataFrame.join, or with pd.concat?
answer
- columns, index, or an axis
- three different defaults for how
- join matches the other frame's index
- concat stacks and aligns labels
basics
~20 sUse merge to match rows on key columns (default how='inner'), DataFrame.join to match against the other frame's index (default how='left'), and pd.concat to stack frames along an axis while aligning the labels of the other axis (default join='outer').
solid answer
~40 s`merge` is the general key-based join: `orders.merge(customers, on="customer_id", how="left")` matches rows on column values, defaults to `how="inner"`, adds `_x`/`_y` suffixes to clashing column names, and throws away both indexes when it joins columns on columns. `DataFrame.join` is a shortcut built on `merge` that always matches against the **other frame's index**, either index-to-index or `on=` a column of the caller; it defaults to `how="left"` and raises if columns overlap and you gave no `lsuffix`/`rsuffix`. `pd.concat` does no key matching at all: it stacks a list of frames along `axis=0` (rows) or `axis=1` (columns), aligns the other axis by label with `join="outer"` by default, and keeps the original index labels unless you pass `ignore_index=True`.
code
python · 19 linesimport pandas as pd
orders = pd.DataFrame(
{"order_id": [1, 2, 3], "customer_id": [10, 20, 30], "amount": [50.0, 20.0, 75.0]}
)
customers = pd.DataFrame({"customer_id": [10, 20], "name": ["Ana", "Ben"]})
# key columns on both sides; default how is "inner", so say "left"
by_column = orders.merge(customers, on="customer_id", how="left")
print(by_column) # 3 rows, name is NaN for customer 30
# the lookup frame indexed by the key; join defaults to how="left"
by_index = orders.join(customers.set_index("customer_id"), on="customer_id")
print(by_index)
# a later batch of the same table: stack, do not match
march = pd.DataFrame({"order_id": [4], "customer_id": [20], "amount": [12.0]})
stacked = pd.concat([orders, march], ignore_index=True)
print(stacked.shape) # (4, 3)go deeper
Recall which tool matches on columns, which on the index and which just stacks, and the default how of merge (inner) and join (left).
Explain on vs left_on/right_on, the intersection-of-columns default when on is omitted, suffixes, and how concat aligns the non-concatenation axis.
Spot silent mis-joins in review: concat(axis=1) on positional indexes, merge on accidental shared columns, a default inner merge that dropped rows nobody counted.
Decide which combining idioms a team standardises on, for example explicit how= and on= on every merge, so reviewers can read intent without re-deriving defaults.
## Three tools, three questions pandas offers three everyday ways to combine **DataFrames** (its two-dimensional labelled tables). They answer different questions, and an interviewer listens for whether you know which one matches rows by **value**, which by **label**, and which does not match at all. | Tool | Matches on | Default | Typical job | |---|---|---|---| | `pd.merge` / `DataFrame.merge` | values in key columns (or index levels) | `how="inner"` | orders enriched with customer attributes | | `DataFrame.join` | the other frame's **index** | `how="left"` | add lookup columns keyed by an index | | `pd.concat` | nothing: it stacks, then aligns labels on the other axis | `axis=0`, `join="outer"` | twelve monthly extracts into one table | ## `merge`: the key-based join `merge` is pandas' database-style join. The important arguments are: - `how`: `"inner"` (the default, keys present on both sides), `"left"`, `"right"`, `"outer"` and `"cross"` (every left row paired with every right row, with no key at all). - `on`: the key column name(s) present in both frames. If you pass nothing and do not merge on indexes, pandas uses **every column name the two frames share**, which silently turns a one-column join into a multi-column one when both frames carry, say, an `updated_at` column. - `left_on` / `right_on`: different key names on each side (both key columns stay in the result). - `left_index` / `right_index`: use an index as the key instead of a column. - `suffixes`: default `("_x", "_y")`, appended to non-key columns whose names clash. When `merge` joins **columns on columns**, both input indexes are discarded and the result gets a fresh default index. Row order follows the left keys for `inner` and `left`; `outer` sorts the keys lexicographically. ## `DataFrame.join`: the index-based shortcut `left.join(right)` is implemented on top of `merge`: it always uses `right`'s index as the right-hand key. With no `on=`, it matches index to index; with `on="customer_id"`, it matches the caller's `customer_id` column against `right`'s index, the way a spreadsheet lookup reads a value from a keyed table. Points that differ from `merge`: - the default is `how="left"`, so every row of the caller survives; - overlapping column names raise `ValueError: columns overlap but no suffix specified` unless you pass `lsuffix` or `rsuffix` (both default to the empty string); - it accepts a **list** of frames and joins them all on the index in one call, though `on`, `lsuffix` and `rsuffix` are not supported in that form. ## `pd.concat`: stacking, not matching `pd.concat([jan, feb, mar])` glues objects end to end. With the default `axis=0` it appends rows and **aligns columns by name**: a column present in only some pieces is filled with missing values elsewhere (an integer column then becomes `float64` to hold `NaN`). With `axis=1` it places frames side by side and aligns rows by **index label**, which is a join on the index, not on a column. Other arguments: 1. `join="outer"` (default) keeps the union of the other axis' labels; `join="inner"` keeps only the labels every piece shares. 2. `ignore_index=True` replaces the concatenation axis with `0 .. n-1`; without it, repeated labels such as several `0`s survive. 3. `keys=[...]` adds an outer index level naming the source of each block. ## Choosing in practice - Two tables share a business key in columns: `merge`, and state `how` explicitly. - The lookup table is already indexed by the key: `join` reads shorter and defaults to keeping every caller row. - Same-shaped pieces of one dataset (months, shards, files): `concat`, ideally once on a list rather than in a loop. - Wide pieces that share a row index: `concat(axis=1)` or `join`; both align on the index. ## Function or method, frame or Series `pd.merge(left, right, ...)` and `left.merge(right, ...)` are the same operation; the method form reads better in a chain. Both accept a **named Series** as either side, treated as a one-column frame. `DataFrame.join` also accepts a named Series, whose name becomes the new column. `pd.concat` returns a **Series** when every piece is a Series stacked along `axis=0`, and a DataFrame as soon as one piece is a DataFrame or when `axis=1`. Knowing which shape comes back saves a surprised `.columns` lookup on a Series later in the pipeline. ## The silent mis-join A common review comment is that `concat(axis=1)` was used where a key-based `merge` was needed: if the two frames' indexes are just default `RangeIndex` positions, `concat(axis=1)` pairs row 0 with row 0 whatever the customer is, and nothing warns you.
- What happens if you call orders.merge(customers) with no on= and both frames have customer_id and updated_at columns?With no `on`, `left_on`/`right_on` or index flags, `merge` joins on the intersection of column names, so it matches on `customer_id` **and** `updated_at`. Rows only match when both values agree, so most orders lose their customer data in a left merge or vanish in the default inner merge. Always pass `on=` explicitly.
- Why can pd.concat([a, b], axis=1) pair the wrong rows together?`concat(axis=1)` aligns rows by index label, not by any column. If both frames carry a default `RangeIndex`, row 0 of `a` meets row 0 of `b` regardless of which customer each describes; if their indexes differ, you get missing values where labels do not overlap. Use `merge` on the business key, or set that key as the index on both sides first.
- When does DataFrame.join raise where merge would not?When the two frames share a non-key column name. `join` passes `lsuffix` and `rsuffix`, both empty by default, to `merge`, and with no suffix on either side pandas raises `ValueError: columns overlap but no suffix specified`. `merge` defaults to `("_x", "_y")` and renames silently.
merge is matching two guest lists by name; join is looking each name up in an indexed address book; concat is stapling this month's sign-in sheet under last month's.
- Says merge defaults to a left join, so unmatched orders are always kept.
- Thinks DataFrame.join matches on same-named columns like merge does.
- Uses concat with axis=1 to attach customer data by customer_id.
- Believes concat drops columns that only some pieces have.
- Assumes merge keeps the left frame's original index after a column join.