如何按Items分组，基于最近日期时间戳映射两个DataFrame数据点？

原始数据 #

import pandas as pd
import numpy as np
from pandas.tseries.offsets import MonthEnd

df1 = pd.DataFrame({"items":["i1", "i1", "i1", "i2","i2", "i2"], "dates":["09-Nov-2022", "10-Aug-2022", "27-May-2022", "20-Oct-2022", "01-Nov-2022","27-Jul-2022"]})
df2 = pd.DataFrame({"items": ["i1", "i1", "i1", "i1", "i2", "i2"], "prod_mmmyyyy": ["Sep 2022", "Jun 2022", "Mar 2022", "Dec 2021", "Sep 2022", "Jun 2022"]})

需求说明 #

按items分组，为df2中每个prod_mmmyyyy匹配df1.dates里最近的下一个日期（即大于prod_mmmyyyy对应月末日期的最小日期）。

实现步骤 #

1. 转换日期格式并预处理

   • 将df1.dates转为datetime类型，按items分组后排序日期，方便后续查找匹配
   • 将df2.prod_mmmyyyy转为datetime类型，再转为当月最后一天，对齐需求中的月末逻辑

2. 分组匹配目标日期
   对每个items组，用numpy.searchsorted定位prod月末日期在df1排序后日期中的插入位置，该位置对应的日期即为符合要求的最近下一个日期。

完整代码 #

# 转换df1日期格式并分组排序
df1['dates'] = pd.to_datetime(df1['dates'])
df1_sorted = df1.groupby('items')['dates'].apply(lambda x: x.sort_values().reset_index(drop=True))

# 转换df2的prod日期为月末日期
df2['prod_date'] = pd.to_datetime(df2['prod_mmmyyyy']) + MonthEnd(0)

# 定义分组匹配函数
def match_next_date(group):
    item = group['items'].iloc[0]
    df1_dates = df1_sorted[item]
    # 找到每个prod_date对应的插入位置，取右侧第一个大于它的日期
    indices = np.searchsorted(df1_dates, group['prod_date'], side='right')
    # 转换为要求的日期格式
    group['mapped_date'] = df1_dates.iloc[indices].dt.strftime('%d-%b-%Y')
    return group

# 应用函数并整理结果
result = df2.groupby('items', group_keys=False).apply(match_next_date)
result = result[['items', 'prod_mmmyyyy', 'mapped_date']]
print(result)

输出结果 #

items prod_mmmyyyy mapped_date
0    i1     Sep 2022  09-Nov-2022
1    i1     Jun 2022  10-Aug-2022
2    i1     Mar 2022  27-May-2022
3    i1     Dec 2021  27-May-2022
4    i2     Sep 2022  20-Oct-2022
5    i2     Jun 2022  27-Jul-2022

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