使用pd.merge_asof时left_index=True搭配右列出现异常结果

正常工作场景 #

当左表所有行的索引都晚于右表最早匹配日期时，pd.merge_asof行为符合预期：

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

异常场景 #

当左表起始日期早于右表最早匹配日期（如2023-01-01早于2024-03-31）时，pd.merge_asof出现异常：

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

问题表现 #

• QuarterEnd_Gaap列被填充为左表的索引值，而非右表中存在的季度末日期
• 早于右表首个季度的行，some_metric为NaN，但QuarterEnd_Gaap错误填充了左表日期
• 修改allow_exact_matches参数无法修复该问题，只要左表包含早于右表最早匹配值的行就会触发

原因分析 #

这是因为在direction="backward"模式下，当左表行的连接键（索引）早于右表所有连接键时，merge_asof无法找到符合条件的匹配项，此时会错误地将左表的连接键值填充到右表的连接列中，属于pandas的非预期行为。

解决方案 #

方案1：将左表索引转为显式连接列 #

避免直接使用索引作为连接键，转为单独列后再执行合并：

import pandas as pd

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))
# 将索引转为显式列
df = df.reset_index().rename(columns={"index": "date"})

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]
})

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

merged_df = pd.merge_asof(
    df,
    Table,
    left_on="date",
    right_on="QuarterEnd_Gaap",
    direction="backward",
)
# 将date列转回索引
merged_df = merged_df.set_index("date")

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

方案2：在右表添加早于左表起始日期的占位行 #

通过添加占位行，确保所有左表行都能找到反向匹配：

import pandas as pd

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]
})

# 添加早于左表起始日期的占位行
placeholder = pd.DataFrame({
    "QuarterEnd_Gaap": [pd.to_datetime("2022-12-31")],
    "some_metric": [pd.NA]
})
Table = pd.concat([placeholder, Table]).sort_values("QuarterEnd_Gaap")

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

merged_df = pd.merge_asof(
    df,
    Table,
    left_index=True,
    right_on="QuarterEnd_Gaap",
    direction="backward",
)
# 可选：将早于首个有效季度的行的QuarterEnd_Gaap设为NaN
merged_df.loc[merged_df["QuarterEnd_Gaap"] == "2022-12-31", "QuarterEnd_Gaap"] = pd.NA

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

两种方案均能得到符合预期的结果：

• 2024-03-31之后的行，QuarterEnd_Gaap显示最近的季度末日期
• 2024-03-31之前的行，QuarterEnd_Gaap为NaN（可根据需求调整为右表最后一个季度值）

内容的提问来源于stack exchange，提问作者Michał