Pandas按分组条件计算每周依从性并填充至对应列首行实现

实现代码 #

你可以按以下步骤实现需求：先统计每个ID对应周的有效检测数，计算依从性后匹配到每个ID的首行对应列即可，完整代码如下：

import pandas as pd

data = {'ID':['A','A','A','A','A','A','A','A','A','C','C','C','C','C','C','C','C'],
    'Week': ['Week1','Week1','Week1','Week1','Week2','Week2','Week2','Week2','Week3',
             'Week1','Week1','Week1','Week1','Week2','Week2','Week2','Week2'],
    'Risk':['High','','','','','','','','','High','','','','','','',''],
    'Testing':['','Pos','','Neg','','','','','Pos', '', '','','Neg','','','','Pos'],
    'Week1_adher':['','','','','','','','','', '','','','','','','',''],
    'Week2_adher':['','','','','','','','','','','','','','','','',''],
    'Week3_adher':['','','','','','','','','','','','','','','','','']}
    
df1 = pd.DataFrame(data)

# 步骤1：计算每个ID每周的依从性
# 标记有效检测记录（非空）
df1['valid_test'] = df1['Testing'].ne('')
# 分组统计每周有效检测数，判断依从性
weekly_adher = df1.groupby(['ID','Week'])['valid_test'].sum().reset_index()
weekly_adher['adher_res'] = weekly_adher['valid_test'].ge(2).map({True:'Yes', False:'No'})
# 转成宽表匹配WeekX_adher列名
adher_wide = weekly_adher.pivot(index='ID', columns='Week', values='adher_res').add_suffix('_adher')

# 步骤2：获取每个ID的首行索引，填充结果
first_idx = df1.groupby('ID').head(1).index
df1.loc[first_idx, adher_wide.columns] = adher_wide.loc[df1.loc[first_idx, 'ID']].values

# 删掉辅助列
df1.drop('valid_test', axis=1, inplace=True)

print(df1)

结果验证 #

运行后即可得到符合要求的DataFrame：

• ID为A的首行：Week1_adher为Yes、Week2_adher为No、Week3_adher为No
• ID为C的首行：Week1_adher为No、Week2_adher为No、Week3_adher为空

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