如何按指定条件将宽DataFrame转为长DataFrame并新增统计列

云原生阿岚

2026-10-1

解决方案

完整实现代码

import pandas as pd
import numpy as np

# 加载示例数据
NaN = np.nan
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':[NaN,'Pos',NaN,'Neg',NaN,NaN,NaN,NaN,'Pos', NaN, 
              NaN,NaN,'Negative',NaN,NaN,NaN,'Positive'],
    'Week1_adher':['Yes',NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,'No',NaN,NaN,NaN,NaN,NaN,NaN,NaN],
    'Week2_adher':['No',NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,'No',NaN,NaN,NaN,NaN,NaN,NaN,NaN],
    'Week3_adher':['No',NaN,NaN,NaN,NaN,NaN,NaN,NaN,NaN,'No',NaN,NaN,NaN,NaN,NaN,NaN,NaN]}
    
df1 = pd.DataFrame(data)

# 1. 提取每个受试者的固定属性（ID、Risk，每个ID唯一）
id_static = df1.groupby('ID', as_index=False)['Risk'].max()

# 2. 统计每个受试者每周的检测非空数量：#of test
test_count = df1.groupby(['ID','Week'], as_index=False)['Testing'].agg(**{'#of test': lambda x: x.notna().sum()})

# 3. 处理依从性列，宽转长匹配对应周的依从性
adher_df = df1.melt(id_vars=['ID'], value_vars=['Week1_adher','Week2_adher','Week3_adher'],
                    var_name='Week', value_name='Adherence')
adher_df['Week'] = adher_df['Week'].str.replace('_adher','')
adher_df = adher_df.dropna().drop_duplicates()

# 4. 合并所有数据得到最终结果
final_df = id_static.merge(test_count, on='ID', how='left').merge(adher_df, on=['ID','Week'], how='left')

# 查看输出
print(final_df)