How To Group By Column In A Dataframe And Create Pivot Tables In A Loop

I have the following table df . ID CATEG LEVEL COLS VALUE COMMENT 1 A 3 Apple 388 comment1 1 A 3 Orange 204 comment1 1 A 2

Solution 1:

Use:

df = pd.DataFrame({'ID': [1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 2, 1, 2], 'CATEG': ['A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'B', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'C', 'B'], 'LEVEL': [3.0, 3.0, 2.0, 1.0, 1.0, 2.0,  np.nan,  np.nan,  np.nan, 3.0, 2.0, 3.0, 2.0, 1.0, 1.0,  np.nan, 2.0, 1.0, 1.0, 2.0, 3.0,  np.nan,  np.nan, 3.0,  np.nan], 'COLS': ['Apple', 'Orange', 'Orange', 'Orange', 'Apple', 'Apple', 'Berry', 'Car', 'Berry', 'Apple', 'Apple', 'Orange', 'Orange', 'Orange', 'Apple', 'Car', 'Orange', 'Orange', 'Apple', 'Apple', 'Apple', 'Berry', 'Car', 'Orange', 'Car'], 'VALUE': [388, 204, 322, 716, 282, 555, 289, 316, 297, 756, 460, 497, 831, 225, 395, 486, 320, 208, 464, 613, 369, 474, 888, 345, 664], 'COMMENT': ['comment1', 'comment1', 'comment1', 'comment1', 'comment1', 'comment1', 'comment1', 'comment1', 'comment1', 'comment1', 'comment1', 'comment1', 'comment1', 'comment1', 'comment1', 'comment1', 'comment1', 'comment1', 'comment1', 'comment1', 'comment1', 'comment1', 'comment1', 'comment1', 'comment2']})

---

#check misisng values
mask = df['LEVEL'].isna()

#split DataFrames for different processing
df1 = df[~mask]
df2 = df[mask]

#pivoting with differnet columns parameters
df1 = df1.pivot_table(index=['ID','COMMENT','CATEG'], 
                      columns=['COLS','LEVEL'],
                      values='VALUE')
# print (df1)

df2 = df2.pivot_table(index=['ID','COMMENT','CATEG'], columns='COLS',values='VALUE')
# print (df1)from pandas import ExcelWriter
with pd.ExcelWriter('Values.xlsx') as writer: 
    
    #groupby by first 2 levels ID, COMMENTfor (ids,comments), sample_df in df1.groupby(['ID','COMMENT']):
        #removed first 2 levels, also removed only NaNs columns
        df = sample_df.reset_index(level=[1], drop=True).dropna(how='all', axis=1)
        #new sheetnames by f-strings
        name = f'{ids}_{comments}'#write to file
        df.to_excel(writer,sheet_name=name)
        
    for (ids,comments), sample_df in df2.groupby(['ID','COMMENT']):
        df = sample_df.reset_index(level=[1], drop=True).dropna(how='all', axis=1)
        name = f'{ids}_NULL_{comments}'
        df.to_excel(writer,sheet_name=name)

Another solution without repeating code:

mask = df['LEVEL'].isna()

dfs = {'no_null': df[~mask], 'null': df[mask]}

from pandas import ExcelWriter
with pd.ExcelWriter('Values.xlsx') as writer: 
    
    for k, v in dfs.items():
        if k == 'no_null':
            add = ''
            cols = ['COLS','LEVEL']
        else:
             add = 'NULL_'
             cols = 'COLS'
        
        df = v.pivot_table(index=['ID','COMMENT','CATEG'], columns=cols, values='VALUE')
          
        for (ids,comments), sample_df in df.groupby(['ID','COMMENT']):
            df = sample_df.reset_index(level=[1], drop=True).dropna(how='all', axis=1)
            name = f'{ids}_{add}{comments}'
            df.to_excel(writer,sheet_name=name)