Prices, exchange rates and contracts change over time. For each order you want the value that was in effect on the order date: the latest change on or before it. An exact join can't do that; merge_asof can:
pd.merge_asof(df, ph, left_on="order_date", right_on="effective_date", by="product")
by= matches within the same product (or store, or currency).- Both tables must be sorted by the date columns, or you get left keys must be sorted.
direction="backward" (the default) takes the latest earlier row; "forward" the next one; "nearest" whichever is closer.tolerance=pd.Timedelta("60D") leaves a row unmatched when the nearest change is too far away.
Orders before a product's first price row get NaN, which is worth counting.