使用特定条件和添加新列将宽数据帧转换为长数据帧

问题描述

我有如下所示的示例数据帧。

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)
df1 

最终数据帧必须是这样的，即每个参与者必须有与周数一样多的行。周列转换为行后，应该有相应的值。

此外，还应将每个参与者每周的"测试"列中的NOTNA值添加到"测试值的数量"中。

最终数据帧应如下图所示。

---

解决方案

通过创建两个新列预处理数据帧，然后按ID和Week分组，最后聚合新列：

df1['SurveyAdherence'] = df1.filter(regex=r'Weekd+_adher').eq('Yes').any(axis=1)
df1['#Tests'] = df1['Testing'].notna()

mi = pd.MultiIndex.from_product([df1['ID'].unique(), df1['Week'].unique()],
                                names=['ID', 'Week'])

out = df1.groupby(['ID', 'Week']) 
         .agg({'SurveyAdherence': 'max', '#Tests': 'sum'}) 

out = out.reindex(mi) 
         .fillna({'SurveyAdherence': False, '#Tests': 0}) 
         .astype({'SurveyAdherence': bool, '#Tests': int}) 
         .reset_index()

输出：

>>> df1
  ID   Week  SurveyAdherence  #Tests
0  A  Week1             True       2
1  A  Week2            False       0
2  A  Week3            False       1
3  C  Week1            False       1
4  C  Week2            False       1
5  C  Week3            False       0