Convert Wide Dataframe To Long Dataframe With Specific Conditions And Addition Of New Columns

I have a sample dataframe as shown below. 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'],

Solution 1:

Preprocess your dataframe by creating the two new columns then group by ID and Week and finally aggregate new columns:

df1['SurveyAdherence'] = df1.filter(regex=r'Week\d+_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()

Output:

>>> df1
  ID   Week  SurveyAdherence  #Tests0  A  Week1             True21  A  Week2            False02  A  Week3            False13  C  Week1            False14  C  Week2            False15  C  Week3            False0