Pandas dataframe modification

Question

My input dataframe looks like:-

St     | sgmt   | family | type | Val1 | Val2 | Val3
Closed   common   pension  indv   32    33 34
Closed   common   pension  fam    30 29 28
Closed   common   pension  oth    10 20 30

My output dataframe should be like:-

Col | Val1 | Val2 | Val3
Closed total 72 82 92
-Common total 72 82 92
--Pension total 72 82 92
---indv 32 33 34
---fam 30 29 28
---oth 10 20 30

72,82,92 are sum of columns respectively.

I am stuck and don't know how to proceed. Please help 🥺

Answer 1

You can use pandas.melt to reformat your dataframe and then grouping like that:

df_new = pd.melt(df, id_vars =['Val1','Val2','Val3'], value_vars =['st', 'sgmt','family','type'])
df_new.groupby('value', sort=False).sum()

This is the data:

import pandas as pd

data = {'st':['Closed']*3,
        'sgmt':['common']*3,
        'family':['pension']*3,
        'type':['indv','fam','oth'],
         'Val1':[32,30,10],
         'Val2':[33,29,20],
         'Val3':[34,28,30]}


df = pd.DataFrame(data)