如何匹配两个DataFrame日期，为信号表生成生效与截止日期列？

问题背景 #

我有如下Signal数据框：

Date      M   SH    SM
0 2023-06-16     0    1     0
1 2023-06-21    59    0     0
2 2023-07-07    74    0     0
3 2023-05-31     0    0     1
4 2023-06-13    39    0     0
5 2023-07-07     0    1     0

以及日历数据框（calendar df）：

0    2024-01-03
1    2023-12-28
2    2023-12-25
3    2023-12-20
4    2023-12-15
5    2023-12-12
6    2023-12-07
7    2023-12-04
8    2023-11-29
9    2023-11-24
10   2023-11-21
11   2023-11-16
12   2023-11-13
13   2023-11-08
14   2023-11-03
15   2023-10-31
16   2023-10-26
17   2023-10-23
18   2023-10-18
19   2023-10-13
20   2023-10-05
21   2023-10-02
22   2023-09-27
23   2023-09-22
24   2023-09-19
25   2023-09-14
26   2023-09-11
27   2023-09-06
28   2023-09-01
29   2023-08-28
30   2023-08-23
31   2023-08-18
32   2023-08-15
33   2023-08-10
34   2023-08-07
35   2023-08-02
36   2023-07-28
37   2023-07-25
38   2023-07-20
39   2023-07-17
40   2023-07-12
41   2023-07-07
42   2023-07-04
43   2023-06-26
44   2023-06-21
45   2023-06-16
46   2023-06-13
47   2023-06-08

任务要求 #

为Signal数据框新增Effective和Until列：

• Effective：对应日期在calendar df中匹配位置的下一个日期（比如2023-07-07在calendar df的索引是41，下一个日期是索引40的2023-07-12）
• Until：对应日期在calendar df中匹配位置的下下个日期（比如2023-07-07对应的Until是索引39的2023-07-17）

我已实现单个日期的处理逻辑：

effective = df3.loc[df3.isin(['2023-07-07'])].index[0]-1
until = df3.loc[df3.isin(['2023-07-07'])].index[0]-2

effective = df3.iloc[effective]
until = df3.iloc[until]

但不清楚如何批量处理Signal数据框中的所有日期。

批量处理解决方案 #

核心思路是先为calendar df建立日期到索引的映射字典，再通过批量操作计算每个Signal日期对应的生效和截止日期。

步骤1：预处理日历数据框 #

先将calendar df的列重命名并建立日期与索引的映射：

import pandas as pd

# 重命名calendar df的列（原列名为0）
calendar = calendar.rename(columns={0: 'date'})
# 创建日期到索引的映射字典
date_to_idx = dict(zip(calendar['date'], calendar.index))

步骤2：高效批量生成新列 #

推荐使用矢量化操作（比apply更快），步骤如下：

1. 为Signal数据框添加对应calendar索引的辅助列
2. 计算生效和截止日期的索引
3. 通过索引匹配日期，同时处理索引越界情况

# 假设Signal数据框名为signal_df
# 获取每个Signal日期对应的calendar索引
signal_df['calendar_idx'] = signal_df['Date'].map(date_to_idx)

# 计算Effective和Until对应的calendar索引
signal_df['effective_idx'] = signal_df['calendar_idx'] - 1
signal_df['until_idx'] = signal_df['calendar_idx'] - 2

# 根据索引匹配日期，索引越界时返回NA
signal_df['Effective'] = signal_df['effective_idx'].apply(
    lambda x: calendar.iloc[x]['date'] if x >= 0 else pd.NA
)
signal_df['Until'] = signal_df['until_idx'].apply(
    lambda x: calendar.iloc[x]['date'] if x >= 0 else pd.NA
)

# 可选：删除中间辅助列
signal_df = signal_df.drop(['calendar_idx', 'effective_idx', 'until_idx'], axis=1)

替代方案：使用apply遍历 #

如果数据量较小，也可以用apply函数逐行处理：

def get_dates(row):
    date = row['Date']
    if date not in date_to_idx:
        return pd.Series([pd.NA, pd.NA])
    idx = date_to_idx[date]
    if idx - 1 < 0 or idx - 2 < 0:
        return pd.Series([pd.NA, pd.NA])
    return pd.Series([
        calendar.iloc[idx-1]['date'],
        calendar.iloc[idx-2]['date']
    ])

signal_df[['Effective', 'Until']] = signal_df.apply(get_dates, axis=1)

最终结果示例 #

处理后Signal数据框的部分结果如下：

Date     M   SH    SM    Effective        Until
0 2023-06-16     0    1     0   2023-06-21   2023-06-26
1 2023-06-21    59    0     0   2023-06-26   2023-07-04
2 2023-07-07    74    0     0   2023-07-12   2023-07-17
5 2023-07-07     0    1     0   2023-07-12   2023-07-17

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