How to sum two rows in a multiindex dataframe?-CodePudding

Have multiindex data frame as result of compare of two dataframes ...

        DATE    CUSIP   TICKER  Q44_0
0   self    03/16/2022  00846U101   A   10327
    other   03/16/2022  00846U101   A   10327
1   self    03/16/2022  02376R102   AAL 66467
    other   03/16/2022  02376R102   AAL 66467

Want to add a diff row and subtract "other" from "self" in numeric columns ...

        DATE    CUSIP   TICKER  Q44_0
0   self    03/16/2022  00846U101   A   10327
    other   03/16/2022  00846U101   A   10327
    diff                                    0    
1   self    03/16/2022  02376R102   AAL 66467
    other   03/16/2022  02376R102   AAL 66467
    diff                                    0

The actual dataframe has many numeric columns that I would like to perform this operation on.

CodePudding user response：

Filter both DataFrames - only numeric columns and substract, last add back to original DataFrame:

df1=df.rename({'self':'diff'}).select_dtypes(np.number).xs('diff',drop_level=False,level=1)
df2=df.rename({'other':'diff'}).select_dtypes(np.number).xs('diff',drop_level=False,level=1)

df = pd.concat([df, df1.sub(df2)]).sort_index().reindex(['self','other','diff'], level=1)
print (df)

               DATE      CUSIP TICKER  Q44_0
0 self   03/16/2022  00846U101      A  10327
  other  03/16/2022  00846U101      A  10327
  diff          NaN        NaN    NaN      0
1 self   03/16/2022  02376R102    AAL  66467
  other  03/16/2022  02376R102    AAL  66467
  diff          NaN        NaN    NaN      0