Skip to content
Tutorials Center
My learning

Topics

Lesson 36 of 64 ยท Combining Data

Advanced Merges

Go beyond basic joins in pandas. Use merge_asof for nearest-time matches, merge_ordered, cross joins, anti-joins, self-joins, combine_first, update and compare.

An ordinary merge needs keys that match exactly. Real data is often messier. A trade happens at 09:30:00.048 but quotes are posted at 09:30:00.045 and 09:30:00.040: which quote was valid? A sensor reading is joined to the last configuration change before it. Two product lists need every combination. Or you simply want the rows of one table that have no partner in another.

This lesson covers the merge tools for those cases: merge_asof and merge_ordered for ordered data, cross joins, anti-joins built on the indicator column, self-joins, and three ways of patching and comparing frames (combine_first, update and compare). You should be comfortable with Merge and Join first.

merge_asof: match the nearest earlier key

pd.merge_asof(left, right, on="time") is a left join on the nearest key instead of an equal key. For each left row, it picks the last right row whose key is less than or equal to the left key (a "backward" search). Both frames must be sorted by the key.

flowchart LR
    subgraph timeline["time (ms)"]
      q1["quote 0"] --> q2["quote 40"] --> t["trade 48"] --> q3["quote 60"]
    end
    t -. "backward: picks quote 40" .-> q2

merge_asof(direction="backward") gives the trade at 48 ms the most recent quote at or before it. direction="forward" would pick 60, and "nearest" the closer of the two.

Useful arguments:

  • by="ticker" matches exactly on this column first, then looks for the nearest time inside each group.
  • tolerance=pd.Timedelta("30ms") refuses matches farther away than this. The unmatched rows get NaN.
  • direction="backward" (default), "forward" or "nearest".
  • allow_exact_matches=False ignores right rows with exactly the same key.
Python Attach the latest quote to each trade

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In the first result, the AAA trade at ...023 gets the quote from ...000 (100.0), because the next AAA quote at ...040 is still in the future. The last trade, one second later, gets the quote at ...040 even though it is almost a second old. That is usually wrong for real prices, which is why tolerance exists: with 30 ms, the last trade has no recent enough quote and gets NaN. With direction="nearest", the first trade picks ...040 (17 ms away) rather than ...000 (23 ms away). Be careful with "forward" and "nearest" on real analyses: they look into the future relative to the event, a form of data leakage.

You can also use plain numbers as the key, for example "the price list that applies from this order amount upwards". The key only has to be sortable.

merge_ordered: ordered outer joins

pd.merge_ordered() is an outer join (by default) that returns the result sorted by the key, with an optional fill_method="ffill" that forward-fills gaps. It suits two ordered series with different observation dates that you want to line up.

An ordinary merge(how="outer") also keeps every key, but does not promise an order and has no fill option, so merge_ordered saves a sort and a ffill step. Note that fill_method="ffill" fills the gaps created by the join, in the non-key columns.

Cross joins: every combination

A cross join pairs every row of the left table with every row of the right table (the Cartesian product). Pass how="cross" and no key. It is the way to generate all combinations, such as every size in every color, or a grid of dates and stores to fill with data later. The result has len(left) * len(right) rows, so watch the size.

Python merge_ordered and cross join

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In the forward-filled version, a on 2024-01-02 repeats the value 1 from January 1, and b on January 5 repeats 20 from January 3. The first b stays NaN because there is nothing earlier to fill from.

Anti-joins and self-joins

An anti-join returns the rows of one table that have no match in another: customers who never ordered, products never sold, orders with an unknown customer. SQL has NOT EXISTS for this. pandas has no how="anti", but a merge with indicator=True followed by a filter does the job. The isin method is a shorter alternative when one key is enough: customers[~customers["customer_id"].isin(orders["customer_id"])].

A self-join merges a table with itself, usually to link rows through a parent reference. A classic example is an employee table where each row stores the id of the employee's boss. Merge the table onto itself, matching boss against id, and use suffixes to tell the two sides apart.

Python Anti-join with indicator and a self-join

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice orders[["customer_id"]].drop_duplicates() in the anti-join. Without it, customer 1 (with two orders) would be repeated, which does not matter for the filter but is wasteful for large tables.

Patching and comparing frames

Three smaller tools help when you reconcile two versions of a table.

combine_first(other) fills the gaps in the caller with values from other, aligned by index and column labels. It also adds rows and columns that exist only in other. Use it to "take mine, fall back to theirs".

update(other) modifies the caller in place, overwriting its values with the non-missing values of other, aligned by labels. It never adds new rows or columns, and NaN in other means "no change". Because it works in place, use it on a copy if you want to keep the original.

compare(other) shows the differences between two frames with identical labels and shape. By default it keeps only the rows and columns that differ, with a self and an other column for each. keep_shape=True keeps the full shape, keep_equal=True shows equal values too, and align_axis=0 stacks the self and other rows.

Python combine_first, update and compare

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

compare is a quick way to see exactly what changed between a table before and after a cleaning step. It is also handy in tests: if two frames should be identical, old.compare(new).empty is True. For strict assertions with tolerances, use pandas.testing.assert_frame_equal, covered in Testing and Validating DataFrames.

Picking the right tool

Problem Tool
Nearest earlier time or threshold merge_asof
Two ordered series, outer join, optional fill merge_ordered
All combinations merge(how="cross")
Rows without a partner merge(indicator=True) and filter, or isin
Hierarchy inside one table Self-join with suffixes
Fill my gaps from another frame combine_first
Overwrite values with corrections update
See what changed compare

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does merge_asof pick by default for each left row?

    The default direction is "backward": the most recent right row at or before the left key. Both frames must be sorted by the key.
  2. 2. Why use tolerance in merge_asof?

    Without tolerance, a very old right row can match a new left row. tolerance puts a limit on the distance, so stale matches become NaN.
  3. 3. How many rows does sizes.merge(colors, how="cross") return when sizes has 3 rows and colors has 4?

    A cross join pairs every left row with every right row: 3 x 4 = 12.
  4. 4. Which approach returns customers that have no orders?

    A left merge keeps all customers, and the indicator column marks those without a match as left_only. That is the anti-join pattern.
  5. 5. What does DataFrame.update(other) do with NaN values in other?

    update only copies non-missing values from other. It works in place and does not add new rows or columns.

Summary

  • merge_asof joins on the nearest key (backward by default); sort both frames, use by for groups, and tolerance to avoid stale matches.
  • merge_ordered is a sorted outer join with an optional forward fill; how="cross" produces every combination.
  • An anti-join is a left merge with indicator=True filtered on left_only (or ~isin).
  • A self-join links rows to other rows of the same table; suffixes keep the two sides apart.
  • combine_first fills gaps from another frame, update overwrites in place with non-missing values, and compare lists differences.

Next: Time Series Basics