Pandas如何对比相同contract和RB的行 按指定日期条件删除不符合行

原始数据构建 #

import pandas as pd
from io import StringIO

dfs = """
    contract  RB  BeginDate  ValIssueDate   EndDate   Valindex0
1  A00118  46   19000100      19880901  19841231          50
2  A00118  46   19850100      19880901  99999999          50
3  A00118  47   19000100      19880901  19831231          47
4  A00118  47   19840100      19880901  19841299          47
5  A00118  47   19850100      19880901  99999999          50
6  A00253  48   19000100      19820101  19811231          47
7  A00253  48   19820100      19820101  19841299          47
8  A00253  48   19850100      19820101  99999999          50
9  A00253  50   19000100      19820101  19781231          47
10 A00253  50   19790100      19820101  19841299          47
11 A00253  50   19850100      19820101  99999999          50
12 A00253  4L   20170101      19880901  99999999          39


"""
df = pd.read_csv(StringIO(dfs.strip()), sep='\s+', 
                  dtype={"RB": str, "BeginDate": int, "EndDate": int,'ValIssueDate':int,'Valindex0':int})

需求说明 #

• 分组依据：contract和RB两个字段的组合值
• 过滤规则：
  • 若分组内仅有1行数据，直接保留
  • 若分组内有多行数据，仅保留满足BeginDate ≤ ValIssueDate ≤ EndDate条件的行

实现代码 #

# 计算每个(contract, RB)分组的行数
df['group_size'] = df.groupby(['contract', 'RB'])['contract'].transform('count')
# 构建保留行的过滤条件
filter_cond = (df['group_size'] == 1) | ((df['ValIssueDate'] >= df['BeginDate']) & (df['ValIssueDate'] <= df['EndDate']))
# 过滤得到结果并删除辅助列
result = df[filter_cond].drop('group_size', axis=1)
# 如果需要直接修改原df，替换为下面的代码即可
# df = df[filter_cond].drop('group_size', axis=1)

输出结果 #

contract  RB  BeginDate  ValIssueDate   EndDate  Valindex0
2    A00118  46   19850100      19880901  99999999         50
5    A00118  47   19850100      19880901  99999999         50
7    A00253  48   19820100      19820101  19841299         47
10   A00253  50   19790100      19820101  19841299         47
12   A00253  4L   20170101      19880901  99999999         39

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