Python Pandas Group By And Aggregate Columns

I am using panda version 0.23.0. I want to use data frame group by function to generate new aggregated columns using [lambda] functions.. My data frame looks like ID Flag Amount U

Solution 1:

Use DataFrameGroupBy.agg by dictionary by column names with aggregate function, then reshape by unstack, flatten MultiIndex of columns, rename columns and last reset_index:

df = (df.groupby(["ID", "Flag"])
      .agg({'Flag':'size', 'Amount':'sum', 'User':'nunique'})
      .unstack(fill_value=0))

#python 3.6+
df.columns = [f'{i}{j}' for i, j in df.columns]
#python bellow
#df.columns = [f'{}{}'.format(i, j) for i, j in df.columns]
d = {'Flag0':'Flag0_Count',
     'Flag1':'Flag1_Count',
     'Amount0':'Flag0_Amount_SUM',
     'Amount1':'Flag1_Amount_SUM',
     'User0':'Flag0_User_Count',
     'User1':'Flag1_User_Count',
     }
df = df.rename(columns=d).reset_index()
print (df)

   ID  Flag0_Count  Flag1_Count  Flag0_Amount_SUM  Flag1_Amount_SUM  \
0   1            0            2                 0               155   
1   2            2            0                50                 0   
2   3            1            0                50                 0   

   Flag0_User_Count  Flag1_User_Count  
0                 0                 2  
1                 2                 0  
2                 1                 0