基于YYYY-MM匹配合并两个日期索引的DataFrame

需求说明 #

忽略日期列的具体日信息，仅以YYYY-MM（年月）为匹配依据合并两个DataFrame：

• 第一个DataFrame（df1）每个月份只有一行数据
• 第二个DataFrame（df2）每个月份可能有多行数据
• 要把df1中对应年月的行数据，匹配到df2该年月的所有行里

示例数据 #

df1数据 #

2020-01-01   1.480
2020-02-01   1.620
2020-03-01   1.000
2020-04-01   1.000
2020-05-01   1.950
2020-06-01   1.080
2020-07-01   1.480
2020-08-01   1.620
2020-09-01   1.000
2020-10-01   1.000
2020-11-01   1.950
2020-12-01   1.080
2021-01-01   11.480
2021-02-01   12.620
2021-03-01   13.000
2021-04-01   14.000
2021-05-01   15.950
2021-06-01   16.080
2021-07-01   17.480
2021-08-01   18.620
2021-09-01   19.000
2021-10-01   10.000
2021-11-01   11.950
2021-12-01   12.080
2022-01-01   21.480
2022-02-01   22.620
2022-03-01   23.000
2022-04-01   24.000
2022-05-01   25.950
2022-06-01   26.080
2022-07-01   27.480
2022-08-01   28.620
2022-09-01   29.000
2022-10-01   30.000
2022-11-01   31.950
2022-12-01   32.080
2023-01-01   31.480
2023-02-01   31.620
2023-03-01   32.000
2023-04-01   32.200
2023-05-01   31.950
2023-06-01   32.080
2023-07-01   33.080

df2数据 #

2020-01-15            NaN             NaN             111               111 
2020-02-25            NaN             333             NaN               204 
2021-02-22            123             NaN             NaN               111 
2023-07-18            NaN             324             111               NaN 
2023-03-15            NaN             NaN             NaN               205  

期望合并结果 #

2020-01-15     1.480       NaN             NaN             111               111
2020-02-25     1.620       NaN             333             NaN               204 
2021-02-22     1.620       123             NaN             NaN               111 
2023-07-18     33.080      NaN             324             111               NaN 
2023-03-15     32.000      NaN             NaN             NaN               205  

注：期望结果中2021-02-22对应的数值应为df1中2021-02的12.620，推测是示例笔误，代码将按正确匹配逻辑执行

实现代码（Pandas） #

直接用Pandas处理，核心是提取年月作为合并键：

import pandas as pd

# 构造df1
df1_data = [
    ("2020-01-01", 1.480), ("2020-02-01", 1.620), ("2020-03-01", 1.000),
    ("2020-04-01", 1.000), ("2020-05-01", 1.950), ("2020-06-01", 1.080),
    ("2020-07-01", 1.480), ("2020-08-01", 1.620), ("2020-09-01", 1.000),
    ("2020-10-01", 1.000), ("2020-11-01", 1.950), ("2020-12-01", 1.080),
    ("2021-01-01", 11.480), ("2021-02-01", 12.620), ("2021-03-01", 13.000),
    ("2021-04-01", 14.000), ("2021-05-01", 15.950), ("2021-06-01", 16.080),
    ("2021-07-01", 17.480), ("2021-08-01", 18.620), ("2021-09-01", 19.000),
    ("2021-10-01", 10.000), ("2021-11-01", 11.950), ("2021-12-01", 12.080),
    ("2022-01-01", 21.480), ("2022-02-01", 22.620), ("2022-03-01", 23.000),
    ("2022-04-01", 24.000), ("2022-05-01", 25.950), ("2022-06-01", 26.080),
    ("2022-07-01", 27.480), ("2022-08-01", 28.620), ("2022-09-01", 29.000),
    ("2022-10-01", 30.000), ("2022-11-01", 31.950), ("2022-12-01", 32.080),
    ("2023-01-01", 31.480), ("2023-02-01", 31.620), ("2023-03-01", 32.000),
    ("2023-04-01", 32.200), ("2023-05-01", 31.950), ("2023-06-01", 32.080),
    ("2023-07-01", 33.080)
]
df1 = pd.DataFrame(df1_data, columns=["date", "value1"])

# 构造df2
df2_data = [
    ("2020-01-15", None, None, 111, 111),
    ("2020-02-25", None, 333, None, 204),
    ("2021-02-22", 123, None, None, 111),
    ("2023-07-18", None, 324, 111, None),
    ("2023-03-15", None, None, None, 205)
]
df2 = pd.DataFrame(df2_data, columns=["date", "value2", "value3", "value4", "value5"])

# 提取年月作为合并键
df1["year_month"] = pd.to_datetime(df1["date"]).dt.to_period("M")
df2["year_month"] = pd.to_datetime(df2["date"]).dt.to_period("M")

# 合并数据，保留df2的所有行，匹配df1对应年月的value1
merged_df = pd.merge(df2, df1[["year_month", "value1"]], on="year_month", how="left")

# 调整列顺序并移除临时的年月列
merged_df = merged_df[["date", "value1", "value2", "value3", "value4", "value5"]]

# 打印结果
print(merged_df.to_string(index=False))

运行结果 #

date  value1  value2  value3  value4  value5
 2020-01-15    1.48     NaN     NaN   111.0   111.0
 2020-02-25    1.62     NaN   333.0     NaN   204.0
 2021-02-22   12.62   123.0     NaN     NaN   111.0
 2023-07-18   33.08     NaN   324.0   111.0     NaN
 2023-03-15   32.00     NaN     NaN     NaN   205.0

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