Python pandas实现理赔单新建-审核-处理状态逐日统计

数据集说明 #

现有两个pandas DataFrame数据集：

• df1：日期分组基准表，包含统计周期内的所有日期与分组字段

import pandas as pd

df1 = [('2021-04-27', 'A') ,
           ('2021-04-28', 'A'),
           ('2021-04-29', 'A'),
           ('2021-04-30', 'A'),
           ('2021-05-01', 'A'),
           ('2021-05-02', 'A'),
           ('2021-05-03', 'A'),
           ('2021-05-04', 'A'),
           ('2021-05-05', 'A'),
           ('2021-05-06', 'A'),
           ('2021-05-07', 'A'),
           ('2021-05-08', 'A'),
           ('2021-05-09', 'A')
            ]
df1 = pd.DataFrame(df1, columns=['Date', 'Group'])

• df2：理赔单明细表，包含ClaimID（理赔单ID）、New（新建日期）、Approved（审核通过日期）、Processed（处理完成日期）、Group（分组）字段

df2 =  [(80, '2021-04-27', 'NA', '2021-04-27', 'A'),
           (90, '2021-04-28', 'NA', '2021-04-28', 'A'),
           (100, '2021-04-29', 'NA', '2021-04-29', 'A'),
           (206, '2021-04-30', '2021-04-30', '2021-04-30' , 'A') ,
           (629, '2021-04-30', '2021-04-30', '2021-04-30', 'A'),
           (4000, '2021-04-30', '2021-04-30', '2021-04-30', 'A'),
           (4001, '2021-05-01', '2021-05-01', 'NA' , 'A'),
           (4002, '2021-05-01', '2021-05-01', '2021-05-01', 'A'),
           (4003, '2021-05-03', '2021-05-03', '2021-05-03', 'A'),
           (4004, '2021-05-03', '2021-05-03', '2021-05-03', 'A'),
           (4005, '2021-05-03', '2021-05-03', '2021-05-06', 'A'),
           (4006, '2021-05-03', '2021-05-03', '2021-05-06', 'A'),
           (4007, '2021-05-04', '2021-05-04', '2021-05-04', 'A'),
           (4008, '2021-05-04', '2021-05-04', '2021-05-04', 'A')
            ]
df2 = pd.DataFrame(df2, columns=['ClaimID', 'New', 'Approved', 'Processed', 'Group'])

目标是生成符合业务规则的日度统计表df3，结构如下：

df3 = [('2021-04-27', 1, 0, 1, 'A'),
           ('2021-04-28', 1, 0, 1, 'A'),
           ('2021-04-29', 1, 0, 1, 'A'),
           ('2021-04-30', 3, 3, 3, 'A'),
           ('2021-05-01', 2, 2, 1, 'A'),
           ('2021-05-02', 0, 1, 0, 'A'),
           ('2021-05-03', 4, 5, 2, 'A'),
           ('2021-05-04', 2, 5, 2, 'A'),
           ('2021-05-05', 0, 3, 0, 'A'),
           ('2021-05-06', 0, 3, 2, 'A'),
           ('2021-05-07', 0, 1, 0, 'A'),
           ('2021-05-08', 0, 1, 0, 'A'),
           ('2021-05-09', 0, 1, 0, 'A')
            ]
df3 = pd.DataFrame(df3, columns=['Date', 'New', 'Approved', 'Processed', 'Group'])

统计规则 #

• 当日同时完成审核与处理的理赔单，当日Approved、Processed字段统计对应数量，次日数值重置为当日新增量
• 当日审核通过量大于当日处理完成量时，未处理的剩余量结转至后续所有日期统计，包含永久未处理（Processed=NA）的单据
• 晚于审核日期处理的理赔单，对应的待处理量在Approved字段中持续结转至处理当日
• Approved字段从第一笔理赔单审核通过日期开始计数，此前日期该字段值为0

实现代码 #

核心逻辑是先统计各维度每日新增量，再按日期顺序遍历计算Approved字段的结转值：

# 1. 数据预处理：替换NA字符串为空值，转换日期格式
df2 = df2.replace('NA', pd.NA)
date_cols = ['New', 'Approved', 'Processed']
for col in date_cols:
    df2[col] = pd.to_datetime(df2[col])
df1['Date'] = pd.to_datetime(df1['Date'])

# 2. 统计各维度每日数量
# 每日新建量
daily_new = df2.groupby(['New', 'Group'])['ClaimID'].count().reset_index()
daily_new.columns = ['Date', 'Group', 'New']

# 每日处理完成量（所有单据）
daily_processed = df2[df2['Processed'].notna()].groupby(['Processed', 'Group'])['ClaimID'].count().reset_index()
daily_processed.columns = ['Date', 'Group', 'Processed']

# 每日新增审核通过量
daily_approved_add = df2[df2['Approved'].notna()].groupby(['Approved', 'Group'])['ClaimID'].count().reset_index()
daily_approved_add.columns = ['Date', 'Group', 'approved_add']

# 每日已审核单据的处理完成量（用于扣减结转量）
daily_processed_approved = df2[(df2['Approved'].notna()) & (df2['Processed'].notna())].groupby(['Processed', 'Group'])['ClaimID'].count().reset_index()
daily_processed_approved.columns = ['Date', 'Group', 'processed_approved']

# 3. 关联所有统计量到基准日期表
df3 = df1.merge(daily_new, on=['Date', 'Group'], how='left')\
         .merge(daily_processed, on=['Date', 'Group'], how='left')\
         .merge(daily_approved_add, on=['Date', 'Group'], how='left')\
         .merge(daily_processed_approved, on=['Date', 'Group'], how='left')
# 空值填充为0
df3 = df3.fillna(0)

# 4. 按日期顺序计算Approved字段的结转值
pending = 0 # 初始化已审核未处理的结转量
approved_list = []
for _, row in df3.iterrows():
    # 当日Approved值 = 上期结转 + 当日新增审核
    cur_approved = pending + row['approved_add']
    approved_list.append(cur_approved)
    # 更新结转量 = 当日Approved值 - 当日已审核单据的处理量
    pending = cur_approved - row['processed_approved']

# 赋值并整理字段
df3['Approved'] = approved_list
df3 = df3[['Date', 'New', 'Approved', 'Processed', 'Group']]
# 日期转回字符串格式，和目标格式一致
df3['Date'] = df3['Date'].dt.strftime('%Y-%m-%d')
# 数值列转整数
num_cols = ['New', 'Approved', 'Processed']
df3[num_cols] = df3[num_cols].astype(int)

结果验证 #

执行上述代码后输出的df3与给定的目标结果完全一致，所有统计规则均满足。

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