skip to content

Merge, Join, and Concat

merge joins on columns, join on the index, concat stacks frames, and merge_asof matches the nearest key. Interviewers ask about validate, indicator and joins that multiply rows.

on this pageshow

questions

6

In pandas, when do you combine two DataFrames with merge, with DataFrame.join, or with pd.concat?

level: juniorimportance: must knowfreq 78%

answer

  1. columns, index, or an axis
  2. three different defaults for how
  3. join matches the other frame's index
  4. concat stacks and aligns labels

basics

~20 s

Use 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 lines
python
import 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

for a junior

Recall which tool matches on columns, which on the index and which just stacks, and the default how of merge (inner) and join (left).

for a middle

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.

for a senior

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.

for a principal

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.
open as a page

In pandas, how do you find customers who never placed an order with merge, and what did pandas 3.0 add for it?

level: middleimportance: must knowfreq 62%

basics

~10 s

Left-merge customers to orders with indicator=True and keep rows whose _merge is 'left_only'. In pandas 3.0, merge(how='left_anti') returns those customers directly.

open as a page

In pandas, how does merge's validate argument stop an orders-to-customers merge from silently multiplying rows?

level: middleimportance: must knowfreq 55%

basics

~10 s

validate declares the expected key relationship; with validate='many_to_one', merge checks that customer_id is unique in the customers frame and raises MergeError instead of returning extra rows when it is not.

open as a page

In pandas, after pd.concat of twelve monthly DataFrames, why does df.loc[0] return twelve rows, and what do ignore_index and keys change?

level: seniorimportance: should knowfreq 45%

basics

~20 s

pd.concat keeps each piece's index, so twelve frames that each start at 0 stack into twelve rows labelled 0. ignore_index=True renumbers 0..n-1; keys=[...] adds an outer index level naming each month. In pandas 3.0, passing both raises ValueError.

open as a page

Should a data team make merge's validate argument and a row-count check a standing review rule on every pandas merge in its pipelines?

level: principalimportance: should knowfreq 30%

basics

~20 s

Usually yes for pipeline code: require explicit how= and validate= on every merge, plus a row-count or match-rate assertion where the business has an expectation. Keep notebooks exempt, and accept the cost of failing runs and an extra uniqueness pass.

open as a page

In pandas, how does merge_asof match each trade to the latest quote for the same ticker, and what must be true of the inputs?

level: seniorimportance: nice to knowfreq 28%

basics

~20 s

pd.merge_asof(trades, quotes, on='time', by='ticker') keeps every trade and attaches the last quote at or before its time for the same ticker. Both frames must be sorted by the on key, which must be numeric or datetime with no nulls.

open as a page