[Python] Weird result in pd.merge_asof with left_index=True and right column?

When I execute the following, the behavior of merge_asof is as expected:

import pandas as pd

date_range = pd.date_range(start="2025-01-01", end="2025-03-31", freq="D")
df = pd.DataFrame(index=date_range)
df["dummy_value"] = range(len(df))

quarter_dates = ["2024-03-31", "2024-06-30", "2024-09-30", "2024-12-31"]
Table = pd.DataFrame({
"QuarterEnd_Gaap": pd.to_datetime(quarter_dates),
"some_metric": [100, 200, 300, 400]
})

df.index = pd.to_datetime(df.index)
Table["QuarterEnd_Gaap"] = pd.to_datetime(Table["QuarterEnd_Gaap"])
Table = Table.sort_values("QuarterEnd_Gaap")

merged_df = pd.merge_asof(
df,
Table,
left_index=True,
right_on="QuarterEnd_Gaap",
direction="backward",
)

print(merged_df.tail(10))


dummy_value QuarterEnd_Gaap some_metric
2025-03-22 80 2024-12-31 400
2025-03-23 81 2024-12-31 400
2025-03-24 82 2024-12-31 400
2025-03-25 83 2024-12-31 400
2025-03-26 84 2024-12-31 400
2025-03-27 85 2024-12-31 400
2025-03-28 86 2024-12-31 400
2025-03-29 87 2024-12-31 400
2025-03-30 88 2024-12-31 400
2025-03-31 89 2024-12-31 400


However, if I change the start date of df to a date that is before the first QuarterEnd, then the QuarterEnd_Gaap gets the df.index for some weird reason, and I also think the whole merge_asof stops working:

date_range = pd.date_range(start="2023-01-01", end="2025-03-31", freq="D")
df = pd.DataFrame(index=date_range)
df["dummy_value"] = range(len(df))

quarter_dates = ["2024-03-31", "2024-06-30", "2024-09-30", "2024-12-31"]
Table = pd.DataFrame({
"QuarterEnd_Gaap": pd.to_datetime(quarter_dates),
"some_metric": [100, 200, 300, 400]
})

df.index = pd.to_datetime(df.index)
Table["QuarterEnd_Gaap"] = pd.to_datetime(Table["QuarterEnd_Gaap"])
Table = Table.sort_values("QuarterEnd_Gaap")

merged_df = pd.merge_asof(
df,
Table,
left_index=True,
right_on="QuarterEnd_Gaap",
direction="backward",
allow_exact_matches=False,
)

print(merged_df.tail(10))
print(merged_df.head(10))


dummy_value QuarterEnd_Gaap some_metric
2025-03-22 811 2025-03-22 400.0
2025-03-23 812 2025-03-23 400.0
2025-03-24 813 2025-03-24 400.0
2025-03-25 814 2025-03-25 400.0
2025-03-26 815 2025-03-26 400.0
2025-03-27 816 2025-03-27 400.0
2025-03-28 817 2025-03-28 400.0
2025-03-29 818 2025-03-29 400.0
2025-03-30 819 2025-03-30 400.0
2025-03-31 820 2025-03-31 400.0

dummy_value QuarterEnd_Gaap some_metric
2023-01-01 0 2023-01-01 NaN
2023-01-02 1 2023-01-02 NaN
2023-01-03 2 2023-01-03 NaN
2023-01-04 3 2023-01-04 NaN
2023-01-05 4 2023-01-05 NaN
2023-01-06 5 2023-01-06 NaN
2023-01-07 6 2023-01-07 NaN
2023-01-08 7 2023-01-08 NaN
2023-01-09 8 2023-01-09 NaN
2023-01-10 9 2023-01-10 NaN


I would expect that the column QuarterEnd_Gaap contains the last available QuarterEnd within Table, exactly as in the first example. For df indices that are before the first Quarter I'd either expect the last quarter or Nan/None. However, the QuarterEnd_Gaap now contains the df.index and in particular values that are not included in Table at all. The behaviour exists in the second example throughout the dataframe. That means the column QuarterEnd_Gaap is equal to df.index for all rows. Switching allow_exact_matches to True doesn't change the behaviour.

I changed the date range of df and found that the problem starts to exist once the first date in df is smaller than the first quarter.

Continue reading...