使用特定条件和添加新列将宽数据帧转换为长数据帧 [英] Convert wide dataframe to long dataframe with specific conditions and addition of new columns

问题描述

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

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

这篇关于使用特定条件和添加新列将宽数据帧转换为长数据帧的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！