如何用Pandas实现查询日期与多周期有效日期的关联匹配？

给定如下日期映射表（各列日期对应各自独立的数据集）：

D         3D          W         2W          M
0   2023-03-20        NaT 2023-03-20        NaT        NaT
1   2023-03-17        NaT        NaT        NaT        NaT
2   2023-03-16 2023-03-16        NaT        NaT        NaT
3   2023-03-15        NaT        NaT        NaT        NaT
4   2023-03-14        NaT        NaT        NaT        NaT
5   2023-03-13 2023-03-13 2023-03-13 2023-03-13        NaT
6   2023-03-10        NaT        NaT        NaT        NaT
7   2023-03-09        NaT        NaT        NaT        NaT
8   2023-03-08 2023-03-08        NaT        NaT        NaT
9   2023-03-07        NaT        NaT        NaT        NaT
10  2023-03-06        NaT 2023-03-06        NaT        NaT
11  2023-03-03 2023-03-03        NaT        NaT        NaT
12  2023-03-02        NaT        NaT        NaT        NaT
13  2023-03-01        NaT        NaT        NaT 2023-03-01
14  2023-02-28 2023-02-28        NaT        NaT        NaT
15  2023-02-27        NaT 2023-02-27 2023-02-27        NaT

需求说明：

• 查询D列某一日期时，需获取该日期对应的D类数据集；同时要为3D、W、2W、M这几类分别匹配对应数据集
• 若某类（如3D）在查询日期无对应数据，需匹配该类中小于等于查询日期的最近有效日期对应的数据集。例如查询D=2023-03-10时：
  • 3D取2023-03-08
  • W取2023-03-06
  • 2W取2023-02-27
  • M取2023-03-01
• 不可修改或生成数据库中的日期，需用Pandas实现该逻辑

可以通过预处理各列有效日期+二分查找匹配最近日期的方式实现，具体步骤如下：

1. 预处理：提取各列的有效日期集合 #

先从原表格中提取每一列的非空有效日期，并按升序排序（确保二分查找可用）：

import pandas as pd

# 读取原映射表（假设已加载为df，需根据实际数据源调整）
df = pd.read_csv("your_date_map.csv", parse_dates=["D", "3D", "W", "2W", "M"])

# 提取各列有效日期并去重、排序
valid_dates = {}
for col in ["D", "3D", "W", "2W", "M"]:
    valid_dates[col] = df[col].dropna().unique()
    valid_dates[col].sort()

2. 实现查询函数 #

编写函数接收查询日期，返回各列对应的目标日期：

import numpy as np

def get_target_dates(query_date):
    target_dates = {}
    # D列直接匹配查询日期
    target_dates["D"] = query_date
    
    # 处理其他列：查找<=查询日期的最近有效日期
    for col in ["3D", "W", "2W", "M"]:
        dates = valid_dates[col]
        # 用np.searchsorted快速定位插入位置，减1得到最近符合条件的日期索引
        idx = np.searchsorted(dates, query_date, side="right") - 1
        if idx >= 0:
            target_dates[col] = dates[idx]
        else:
            # 无符合条件的日期（所有日期都大于查询日期），可根据需求返回None或抛出异常
            target_dates[col] = None
    return target_dates

# 示例：查询D=2023-03-10
query_date = pd.to_datetime("2023-03-10")
result = get_target_dates(query_date)
print(result)
# 输出：{'D': Timestamp('2023-03-10 00:00:00'), '3D': Timestamp('2023-03-08 00:00:00'), 'W': Timestamp('2023-03-06 00:00:00'), '2W': Timestamp('2023-02-27 00:00:00'), 'M': Timestamp('2023-03-01 00:00:00')}

3. 关联获取数据集 #

拿到目标日期后，即可根据各列的目标日期去对应的数据集表中提取数据，例如：

# 假设各数据集已加载为DataFrame，比如d_dataset、three_d_dataset等
d_data = d_dataset[d_dataset["date"] == result["D"]]
three_d_data = three_d_dataset[three_d_dataset["date"] == result["3D"]]
# 其他列数据集提取逻辑同理

关键逻辑说明 #

• np.searchsorted是核心：它能在有序数组中快速找到插入位置，side="right"确保找到第一个大于查询日期的位置，减1后就是最后一个小于等于查询日期的元素索引
• 预处理时对日期去重排序，保证二分查找的准确性和效率，避免重复计算

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