Pandas多分组下多段日期范围的缺失日期提取问题求解

完整实现代码 #

import pandas as pd

# 构造示例数据
df = pd.DataFrame({"CURRENCY":{"0":"EUR","1":"EUR","2":"EUR","3":"GBP","4":"GBP","5":"GBP","6":"USD","7":"USD","8":"SAR","9":"SAR"},
                   "DATE FROM":{"0":"2021-10-01","1":"2021-10-11","2":"2021-10-19","3":"2021-10-01","4":"2021-10-05",
                                "5":"2021-10-11","6":"2021-10-01","7":"2021-10-05","8":"2021-10-01","9":"2021-10-05"},
                   "DATE TO":{"0":"2021-10-05","1":"2021-10-18","2":"2021-10-23","3":"2021-10-04","4":"2021-10-07",
                              "5":"2021-10-18","6":"2021-10-02","7":"2021-10-10","8":"2021-10-01","9":"2021-10-10"}})

# 1. 日期列转datetime格式
df[['DATE FROM', 'DATE TO']] = df[['DATE FROM', 'DATE TO']].apply(pd.to_datetime)

# 2. 定义分组缺失日期计算逻辑
def calc_missing(group):
    # 取当前币种的整体时间区间
    total_start = group['DATE FROM'].min()
    total_end = group['DATE TO'].max()
    # 生成整体区间的所有日期集合
    total_dates = set(pd.date_range(total_start, total_end))
    # 汇总当前币种所有已覆盖的日期
    covered = set()
    for _, row in group.iterrows():
        covered.update(pd.date_range(row['DATE FROM'], row['DATE TO']))
    # 排序返回缺失日期
    missing_dates = sorted(total_dates - covered)
    return pd.Series(missing_dates, name='MISSING')

# 3. 按币种分组计算，格式化输出
result = df.groupby('CURRENCY', group_keys=False).apply(calc_missing).reset_index()
result['MISSING'] = result['MISSING'].dt.strftime('%Y-%m-%d')

# 输出结果和示例完全一致
print(result)

逻辑说明 #

• 先统一转换日期列的类型，避免字符串比较出错
• 按CURRENCY分组后单独处理每个币种的数据，不需要自行遍历全局列表
• 先求每个币种的整体覆盖区间，再和该币种下所有已记录的日期区间求差集，直接得到缺失日期

大数据量优化方案 #

如果单币种的区间量特别大、时间跨度很长，可以用区间合并法替代日期集合对比，进一步提升性能：

1. 对当前分组的所有区间按DATE FROM升序排序
2. 合并重叠/相邻的区间，得到不重叠的连续区间列表
3. 遍历合并后的区间，计算前一个区间的结束日期和后一个区间的起始日期之间的间隔，就是缺失日期，无需生成全量日期集合

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