Join Rows to Effective Date Ranges with pandas

Prices change, tax rates change, commission plans change — and a report that applies today's value to last quarter's transactions is wrong. Reference tables handle this with effective dates: each row says the value and the period it applies to, ValidFrom to ValidTo, often with a blank end meaning "still valid". Joining transactions to such a table is a range join: match on the product (or employee, or country) and on the date falling inside the period. A plain merge cannot express that, and Excel's VLOOKUP cannot either without helper columns. pandas can do it two ways, and both need a check that every row matched exactly once. This page is part of Merging and Joining Excel DataFrames.

Prerequisites

pip install pandas openpyxl

pricing.xlsx has a Prices sheet — each SKU's price with the period it applies to, inclusive at both ends, a blank ValidTo for the current price — and an Orders sheet with nine orders. The price list has the problems real ones have: a gap for B-200 between 16 and 31 May, two C-300 periods that overlap in August, and an order for a SKU that has no price at all.

import pandas as pd

prices = pd.read_excel("pricing.xlsx", sheet_name="Prices", parse_dates=["ValidFrom", "ValidTo"])
orders = pd.read_excel("pricing.xlsx", sheet_name="Orders", parse_dates=["OrderDate"])
prices["ValidTo"] = prices["ValidTo"].fillna(pd.Timestamp.max.normalize())
print(prices.to_string(index=False))

A blank end date means "until further notice", so fill it with the largest date pandas supports. That keeps every comparison below simple — no special case for open-ended periods.

Step 1 — Merge on the key, then filter by date

The most direct method: join every order to every price row for its SKU, then keep the rows whose date falls inside the period:

candidates = orders.merge(prices, on="SKU", how="left")
in_range = candidates["OrderDate"].between(candidates["ValidFrom"], candidates["ValidTo"])
priced = candidates[in_range]
print(priced[["OrderID", "OrderDate", "SKU", "ValidFrom", "Price"]].to_string(index=False))
print(len(orders), "orders ->", len(priced), "priced rows")

between includes both ends, which matches inclusive ValidTo dates: the order on 1 April gets the new price and the one on 30 June the old one. The method is easy to read and handles any shape of reference table. Its cost is the intermediate candidates frame, which has one row per order per price period of its SKU — fine for price lists with a handful of periods per key, heavy for a daily rate table joined to millions of rows. The row count already signals trouble: nine orders produced eight priced rows, and order 9008 appears twice — so two orders are missing and one is duplicated. Step 3 finds out which.

Step 2 — merge_asof with a validity check

merge_asof finds, for each order, the last price row whose ValidFrom is on or before the order date — per SKU with by= — without building all the combinations. It knows nothing about ValidTo, so check that separately:

asof = pd.merge_asof(orders.sort_values("OrderDate"), prices.sort_values("ValidFrom"),
                     left_on="OrderDate", right_on="ValidFrom", by="SKU", direction="backward")
asof["Valid"] = asof["OrderDate"] <= asof["ValidTo"]
print(asof[["OrderID", "OrderDate", "SKU", "ValidFrom", "ValidTo", "Price", "Valid"]].to_string(index=False))

Both inputs must be sorted by their date column. Without the ValidTo check, order 9006 on 20 May would silently get B-200's old price, because the period that started on 1 January is the last one to start before that date — even though it ended on 15 May. With the check, the row is marked invalid. merge_asof always returns at most one match per order, which hides overlaps instead of revealing them: order 9008 quietly gets 7.60, the later of its two overlapping C-300 prices, and nothing in the output says there was a choice. The open-ended periods show ValidTo as 11 April 2262 — the largest date pandas can store, filled in above.

Step 3 — Every order must match exactly once

Count the matches per order from the merge-and-filter result and classify them:

matches = priced.groupby("OrderID").size().reindex(orders["OrderID"], fill_value=0)
report = orders.assign(Matches=matches.values)
report["Problem"] = report["Matches"].map({0: "no price", 1: ""}).fillna("several prices")
print(report[report["Problem"] != ""].to_string(index=False))

Three orders need attention, and each is a different problem. Order 9006 falls in B-200's gap between 16 and 31 May — the price list is missing a period, or the old price should have run to the end of the month. Order 9008 on 15 August matches two C-300 prices, because the old period runs to 31 August while the new one starts on 1 August — an overlap in the reference data, which would double the order's revenue in any total. Order 9009 is for D-400, which has no price at all. None of these is a coding problem; all of them are questions for whoever owns the price list, and a report should raise them, not guess.

Step 4 — Check the reference table itself

Gaps and overlaps come from the price list, so check it directly as well. Sort each SKU's periods and compare each start with the previous period's end:

p = prices.sort_values(["SKU", "ValidFrom"]).copy()
p["PrevTo"] = p.groupby("SKU")["ValidTo"].shift()
p["Issue"] = ""
p.loc[p["ValidFrom"] <= p["PrevTo"], "Issue"] = "overlaps previous period"
p.loc[p["ValidFrom"] > p["PrevTo"] + pd.Timedelta(days=1), "Issue"] = "gap after previous period"
print(p.loc[p["Issue"] != "", ["SKU", "ValidFrom", "PrevTo", "Issue"]].to_string(index=False))

A clean effective-dated table has each period starting the day after the previous one ends. Running this check whenever the price list is updated catches the problem before any order is priced against it. Find Overlapping Date Ranges with pandas covers overlap detection in more depth.

Step 5 — Price the clean orders and report the rest

Use the single matches, compute line values, and write the problems to their own sheet so they get fixed:

ok_ids = report.loc[report["Problem"] == "", "OrderID"]
lines = priced[priced["OrderID"].isin(ok_ids)].copy()
lines["Value"] = lines["Qty"] * lines["Price"]
print(lines[["OrderID", "SKU", "Qty", "Price", "Value"]].to_string(index=False))
print(f"priced {len(lines)} of {len(orders)} orders, value {lines['Value'].sum():,.2f}")

with pd.ExcelWriter("orders_priced.xlsx", engine="xlsxwriter", datetime_format="dd mmm yyyy") as xw:
    lines.drop(columns=["ValidTo"]).to_excel(xw, sheet_name="Priced", index=False)
    report[report["Problem"] != ""].to_excel(xw, sheet_name="Needs attention", index=False)
    p[p["Issue"] != ""].drop(columns="PrevTo").to_excel(xw, sheet_name="Price list issues", index=False)

Keeping ValidFrom in the output shows which price period each line used, which is the first thing anyone checking a disputed invoice will ask. The same pattern prices anything effective-dated: tax rates by country, commission plans by salesperson, cost rates by employee grade, discount agreements by customer.

Performance notes

The merge-then-filter method multiplies each order by the number of periods for its key. A price list with a dozen periods per SKU and a million orders makes twelve million intermediate rows — several hundred megabytes, workable but slow. merge_asof stays at one row per order and runs in roughly the time of a sort, so it is the method for large volumes, as long as the ValidTo check follows it and the reference table has been checked for overlaps (Step 4) beforehand. For very large jobs where the data already sits in a database, the same range join in SQL — ON o.sku = p.sku AND o.order_date BETWEEN p.valid_from AND p.valid_to — lets the database do the work; Query Excel Files with SQL Using DuckDB shows how to run it directly against workbooks.

Common pitfalls

• Open-ended periods as NaN. A blank ValidTo fails every comparison; fill it with a far-future date first.
• Inclusive versus exclusive ends. If ValidTo means "valid until, not including", use < for the upper bound.
• Trusting merge_asof alone. It ignores end dates and hides overlaps; always check ValidTo and the reference table.
• Unsorted inputs to merge_asof. Both sides must be sorted by the date column, or it raises.
• Summing without counting matches. An overlap doubles the row; validate one match per order before totalling.

Conclusion

Fill open-ended ValidTo dates, then either merge on the key and keep rows where the date is between the period's ends, or use merge_asof by key and check the end date separately. Count matches per transaction — zero means a gap or a missing key, more than one an overlap — and check the reference table for gaps and overlaps directly. Price the rows with exactly one match and send the rest back to whoever owns the reference data.

Frequently asked questions

How do I merge on a date range in pandas? Merge on the key column, which gives every combination of transaction and period for that key, then keep the rows where the transaction date is between ValidFrom and ValidTo. For large tables, merge_asof on ValidFrom with by= the key, followed by a check against ValidTo, avoids the intermediate combinations.

How do I handle a blank "valid to" date? Fill it with a far-future date, such as pd.Timestamp.max.normalize(), before comparing. Then open-ended periods behave like any other range.

Why do some rows appear twice after the join? Two periods for the same key overlap, so the transaction date falls inside both. Fix the reference table so each period starts the day after the previous one ends, and count matches per transaction to catch it.

Can merge_asof handle valid_to dates? No. It matches on the start date only and returns the last period that started before the transaction, even if that period has ended. Compare the transaction date with the matched ValidTo afterwards and treat failures as unmatched.

Related

• Up one level: Merging and Joining Excel DataFrames — every way to combine tables.
• Match Rows by Nearest Date with merge_asof — merge_asof in full.
• Validate a Merge to Avoid Duplicate Rows — catching unexpected many-to-one joins.
• Convert Multi-Currency Transactions with an FX Rate Table — a daily rate table joined by date.