how to add new row into each group of groupby in PANDAS , one of the value of that row is sum of values of each groups

Question

let's say I have a data frame like this I wanted to add a new row into each group of groupby(by=['eff_date','mdl_cd','ast_cd']) in which column value for eff_date,mdl_cd and ast_cd will remain same but for prop_cd value become Hlds and value value column become sum of value of that group e.g. for first group value of value column will be (-0.1234+0.5123+-0.7612)

Accepted Answer

You can create a dataframe with the sum of each group by .groupby() and .sum(), set the prop_cd as Hlds by .assign().Then, concat with the original dataframe by pd.concat() and sort the columns to put the sum rows back together with their respective groups by .sort_values(), as follows:df_sum = df.groupby(['eff_date','mdl_cd','ast_cd'], as_index=False)['value'].sum().assign(prop_cd='Hlds')df_out = pd.concat([df, df_sum]).sort_values(['eff_date','mdl_cd','ast_cd'], kind='stable', ignore_index=True)Result:print(df_out)      eff_date  mdl_cd    ast_cd prop_cd   value0   2021-09-22    Comm      Agri     Car -0.12341   2021-09-22    Comm      Agri    Fund  0.51232   2021-09-22    Comm      Agri     Mmt -0.76123   2021-09-22    Comm      Agri    Hlds -0.37234   2021-09-22    Comm      Engy     Car  0.12125   2021-09-22    Comm      Engy    Fund -0.12346   2021-09-22    Comm      Engy     Mmt  0.51237   2021-09-22    Comm      Engy    Hlds  0.51018   2021-09-22    Comm  Industry     Car -0.76129   2021-09-22    Comm  Industry    Fund  0.121210  2021-09-22    Comm  Industry     Mmt -0.123411  2021-09-22    Comm  Industry    Hlds -0.763412  2021-09-22    Comm     Metal     Car  0.512313  2021-09-22    Comm     Metal    Fund -0.761214  2021-09-22    Comm     Metal     Mmt  0.121215  2021-09-22    Comm     Metal    Hlds -0.127716  2021-09-23  Equity      Agri     Car  0.654117  2021-09-23  Equity      Agri    Fund  0.512318  2021-09-23  Equity      Agri     Mmt -0.187419  2021-09-23  Equity      Agri    Hlds  0.979020  2021-09-23  Equity      Engy     Car  0.121221  2021-09-23  Equity      Engy    Fund -0.623422  2021-09-23  Equity      Engy     Mmt  0.512323  2021-09-23  Equity      Engy    Hlds  0.010124  2021-09-23  Equity  Industry     Car -0.161225  2021-09-23  Equity  Industry    Fund  0.121226  2021-09-23  Equity  Industry     Mmt -0.193427  2021-09-23  Equity  Industry    Hlds -0.233428  2021-09-23  Equity     Metal     Car  0.512329  2021-09-23  Equity     Metal    Fund  0.541230  2021-09-23  Equity     Metal     Mmt  0.121231  2021-09-23  Equity     Metal    Hlds  1.1747Setupdf = pd.read_clipboard(',')      eff_date  mdl_cd    ast_cd prop_cd   value0   2021-09-22    Comm      Agri     Car -0.12341   2021-09-22    Comm      Agri    Fund  0.51232   2021-09-22    Comm      Agri     Mmt -0.76123   2021-09-22    Comm      Engy     Car  0.12124   2021-09-22    Comm      Engy    Fund -0.12345   2021-09-22    Comm      Engy     Mmt  0.51236   2021-09-22    Comm  Industry     Car -0.76127   2021-09-22    Comm  Industry    Fund  0.12128   2021-09-22    Comm  Industry     Mmt -0.12349   2021-09-22    Comm     Metal     Car  0.512310  2021-09-22    Comm     Metal    Fund -0.761211  2021-09-22    Comm     Metal     Mmt  0.121212  2021-09-23  Equity      Agri     Car  0.654113  2021-09-23  Equity      Agri    Fund  0.512314  2021-09-23  Equity      Agri     Mmt -0.187415  2021-09-23  Equity      Engy     Car  0.121216  2021-09-23  Equity      Engy    Fund -0.623417  2021-09-23  Equity      Engy     Mmt  0.512318  2021-09-23  Equity  Industry     Car -0.161219  2021-09-23  Equity  Industry    Fund  0.121220  2021-09-23  Equity  Industry     Mmt -0.193421  2021-09-23  Equity     Metal     Car  0.512322  2021-09-23  Equity     Metal    Fund  0.541223  2021-09-23  Equity     Metal     Mmt  0.1212Interim result:print(df_sum)     eff_date  mdl_cd    ast_cd   value prop_cd0  2021-09-22    Comm      Agri -0.3723    Hlds1  2021-09-22    Comm      Engy  0.5101    Hlds2  2021-09-22    Comm  Industry -0.7634    Hlds3  2021-09-22    Comm     Metal -0.1277    Hlds4  2021-09-23  Equity      Agri  0.9790    Hlds5  2021-09-23  Equity      Engy  0.0101    Hlds6  2021-09-23  Equity  Industry -0.2334    Hlds7  2021-09-23  Equity     Metal  1.1747    Hlds

Advertisement

Answer