pandas Cheatsheet
Merging and Joining
Use this pandas reference while you build software engineering projects, review code for technical interview prep, or polish examples for a software engineer resume.
pd.merge() — SQL-style Joins
pd.merge() is the primary join function. It always returns a new DataFrame.
pd.merge(left, right, on="key") # inner join on shared column pd.merge(left, right, on=["key1", "key2"]) # composite key pd.merge(left, right, left_on="lid", right_on="rid") # different column names pd.merge(left, right, on="key", how="inner") # inner (default) pd.merge(left, right, on="key", how="left") # left join pd.merge(left, right, on="key", how="right") # right join pd.merge(left, right, on="key", how="outer") # full outer join pd.merge(left, right, on="key", how="cross") # cartesian product
how parameter
how | Keeps |
|---|---|
"inner" | Only rows with matching keys in both |
"left" | All rows from left; right fills NaN on mismatch |
"right" | All rows from right; left fills NaN on mismatch |
"outer" | All rows from both; NaN where no match |
"cross" | Every combination (no key needed) |
pd.merge() Options
pd.merge( left, right, on="key", how="left", suffixes=("_x", "_y"), # suffix for overlapping non-key columns indicator=True, # adds "_merge" col: "left_only"|"right_only"|"both" validate="one_to_many", # check: "one_to_one"|"one_to_many"|"many_to_one"|"many_to_many" sort=False, # don't sort the result keys ) # Use indicator to inspect unmatched rows merged = pd.merge(left, right, on="id", how="outer", indicator=True) unmatched_left = merged[merged["_merge"] == "left_only"] unmatched_right = merged[merged["_merge"] == "right_only"]
DataFrame.merge() — Instance Method
# Same semantics, called on the left DataFrame left.merge(right, on="key") left.merge(right, on="key", how="left") left.merge(right, left_on="lid", right_on="rid")
Joining on Index — left_index / right_index
# Join where one or both sides use index as key pd.merge(left, right, left_on="key", right_index=True) pd.merge(left, right, left_index=True, right_index=True) # DataFrame.join() — index-to-index shortcut left.join(right) # index join, how="left" default left.join(right, how="inner") left.join(right, lsuffix="_l", rsuffix="_r") # for overlapping columns # Join multiple DataFrames at once left.join([df2, df3], how="outer")
pd.concat() — Stack / Union
pd.concat() stacks DataFrames along an axis. No key matching — purely positional.
# Stack rows (default axis=0) pd.concat([df1, df2]) pd.concat([df1, df2, df3], ignore_index=True) # reset 0-based index pd.concat([df1, df2], axis=0) # Stack columns (side-by-side) pd.concat([df1, df2], axis=1) # Create a hierarchical index to identify origin pd.concat([df1, df2], keys=["train", "test"]) # Control alignment behavior pd.concat([df1, df2], join="outer") # "outer" (default) | "inner" pd.concat([df1, df2], join="inner") # only shared columns
concat vs merge
| Task | Use |
|---|---|
| Align on a key column | pd.merge() |
| Stack rows / union vertically | pd.concat(axis=0) |
| Stack columns side-by-side | pd.concat(axis=1) or .join() |
| Append one row/DataFrame | pd.concat([df, new_row]) |
pd.DataFrame.update() — In-place Overwrite
# Fills NaN in-place from another DataFrame (aligns on index+columns) df1.update(df2) # overwrites non-NaN values from df2 df1.update(df2, overwrite=False) # only fill NaN in df1, don't overwrite existing
pd.merge_asof() — Fuzzy / Nearest-Key Join
Useful for time series: joins on nearest key that does not exceed the right key.
# Both DataFrames must be sorted by the join key pd.merge_asof(left, right, on="timestamp") pd.merge_asof(left, right, on="date", by="ticker") # exact match on "by" first pd.merge_asof(left, right, on="ts", direction="backward") # "backward"|"forward"|"nearest" pd.merge_asof(left, right, on="price", tolerance=0.5) # max gap allowed
pd.merge_ordered() — Ordered Merge with Fill
# Like merge but respects order; supports fill_method for gaps pd.merge_ordered(left, right, on="date", fill_method="ffill")
pd.concat with Series
# Series concat → Series pd.concat([s1, s2]) pd.concat([s1, s2], ignore_index=True) # Series concat → DataFrame pd.concat([s1.rename("a"), s2.rename("b")], axis=1)
Common Gotchas
Duplicate keys cause row multiplication. A many-to-many inner join on a key with N duplicates in left and M in right produces N×M rows. Use
validate=to catch this early.
Column naming collisions. When both DataFrames have a non-key column with the same name, pandas suffixes them (
_x,_y). Usesuffixes=to choose meaningful names.
Index is ignored by
pd.merge()unless you setleft_index=Trueorright_index=True. Use.join()for index-to-index joins.
pd.concat()aligns on axis labels. Mismatched column names → NaN in the outer join (default). Usejoin="inner"to keep only shared columns.