Skip to main content

Combining DataFrames

Use merge to relate rows by keys and concat to stack or align whole objects. The important question is not syntax but the relationship you expect.

Join deliberately​

result = orders.merge(
    customers,
    on="customer_id",
    how="left",
    validate="many_to_one",
    indicator=True,
    suffixes=("_order", "_customer"),
)

Cardinality​

Choose the validate contract before looking at the output:

  • one_to_one: both keys unique.
  • one_to_many: left key unique.
  • many_to_one: right key unique.
  • many_to_many: duplication is expected; check output size explicitly.

Unexpected duplicate keys can multiply rows and corrupt aggregates. Check key uniqueness before the merge when the relationship is part of the data contract.

Diagnose unmatched keys​

With indicator=True, inspect _merge values (left_only, right_only, both). Do not simply drop the indicator without explaining unmatched records.

Pandas matches null keys to other null keys during a merge, unlike typical SQL join semantics. Remove, fill, or isolate null keys first when that match is not meaningful.

Concatenate​

rows = pd.concat([january, february], ignore_index=True)
columns = pd.concat([features, labels.rename("target")], axis="columns")

Row concatenation aligns columns; column concatenation aligns indexes. Use ignore_index=True only when old row labels carry no meaning. Add keys= when the source partition should remain traceable.

Time-aware joins​

Use merge_asof for nearest-key time joins after sorting by the join key. Specify direction and tolerance so “nearest” does not quietly become an arbitrary match.

Source​