Shape · Combine tables

As-of Join

Matches each row to the closest row in another table by a sorted key such as a time. For example, attach the price in effect at the time of each trade.

Also known as: Nearest match, merge_asof

How it works

Wire the tables into left and right and pick the Key column, which must exist on both sides. Direction chooses the right row for each left row: Backward takes the latest right key at or before the left key, Forward the earliest at or after it, Nearest the closer of the two, preferring the backward one on a tie. Match within groups adds columns that must be equal, such as a ticker. Tolerance drops a match whose keys are further apart than this, in the key's own units and in seconds for timestamps; blank means no limit. Inputs need not be sorted, and the output keeps the left table's order. A non-key column present on both sides gets a _left or _right suffix.

Inputs and outputs

Settings

{
  keyColumn: string
  byColumns?: string[]
  direction: "backward"|"forward"|"nearest"
  tolerance?: number
}

Example

Each trade should carry the price in effect when it happened. Join trades to prices on ts, match within ticker, direction Backward, tolerance 300 so a trade more than five minutes after the last quote is not matched.

More in Combine tables

• Join — Matches rows from two tables on one or more key columns: inner, left, right, full, anti or semi. Keys can also compare with <, > or between, for range joins.
• Lookup — Adds columns from a reference table to every row by matching one key, like Excel's VLOOKUP. Rows without a match stay, with blanks.
• Union — Stacks the rows of two or more tables one under another. Columns line up by name or by position.
• Zip — Puts two tables side by side, row 1 with row 1, row 2 with row 2, with no key needed. Both tables must have the same number of rows.
• Cross Join — Pairs every row of one table with every row of the other, so 10 × 5 rows gives 50. For example, every store with every month.
• Fuzzy Match — Joins two tables on keys that are similar but not identical, such as customer names with typos. A score column shows how close each match is.