assuming have following dataframe:
a b c sce1 sce2 sce3 sce4 sce5 sc6 animal ground dog 0.0 0.9 0.5 0.0 0.3 0.4 animal ground cat 0.6 0.5 0.3 0.5 1.0 0.2 animal air eagle 1.0 0.1 0.1 0.6 0.9 0.1 animal air owl 0.3 0.1 0.5 0.3 0.5 0.9 object metal car 0.3 0.3 0.8 0.6 0.5 0.6 object metal bike 0.5 0.1 0.4 0.7 0.4 0.2 object wood chair 0.9 0.6 0.1 0.9 0.2 0.8 object wood table 0.9 0.6 0.6 0.1 0.9 0.7 i want create multiindex, contain sum of each lvl. output this:
a b c sce1 sce2 sce3 sce4 sce5 sce6 animal 1.9 1.6 1.4 1.3 2.7 1.6 ground 0.6 1.4 0.8 0.5 1.3 0.6 dog 0.0 0.9 0.5 0.0 0.3 0.4 cat 0.6 0.5 0.3 0.5 1.0 0.2 air 1.3 0.2 0.7 0.8 1.4 1.0 eagle 1.0 0.1 0.1 0.6 0.9 0.1 owl 0.3 0.1 0.5 0.3 0.5 0.9 object 2.6 1.6 1.8 2.3 2.0 2.3 metal 0.8 0.3 1.1 1.3 0.9 0.8 car 0.3 0.3 0.8 0.6 0.5 0.6 bike 0.5 0.1 0.4 0.7 0.4 0.2 wood 1.8 1.3 0.6 1.0 1.1 1.5 chair 0.9 0.6 0.1 0.9 0.2 0.8 table 0.9 0.6 0.6 0.1 0.9 0.7 at moment using loop create 3 different dataframes on each level , manipulate them on excel, below. wanted take calculation in python if possible.
for in range range(0,3): df = df.groupby(list(df.columns)[0:lvl], as_index=false).sum() return df many in advance.
with liberal use of magic
pd.concat([ df.assign( **{x: 'total' x in 'abc'[i:]} ).groupby(list('abc')).sum() in range(4) ]).sort_index() sce1 sce2 sce3 sce4 sce5 sc6 b c animal air eagle 1.0 0.1 0.1 0.6 0.9 0.1 owl 0.3 0.1 0.5 0.3 0.5 0.9 total 1.3 0.2 0.6 0.9 1.4 1.0 ground cat 0.6 0.5 0.3 0.5 1.0 0.2 dog 0.0 0.9 0.5 0.0 0.3 0.4 total 0.6 1.4 0.8 0.5 1.3 0.6 total total 1.9 1.6 1.4 1.4 2.7 1.6 object metal bike 0.5 0.1 0.4 0.7 0.4 0.2 car 0.3 0.3 0.8 0.6 0.5 0.6 total 0.8 0.4 1.2 1.3 0.9 0.8 total total 2.6 1.6 1.9 2.3 2.0 2.3 wood chair 0.9 0.6 0.1 0.9 0.2 0.8 table 0.9 0.6 0.6 0.1 0.9 0.7 total 1.8 1.2 0.7 1.0 1.1 1.5 total total total 4.5 3.2 3.3 3.7 4.7 3.9 i can asked with
pd.concat([ df.assign( **{x: '' x in 'abc'[i:]} ).groupby(list('abc')).sum() in range(1, 4) ]).sort_index() sce1 sce2 sce3 sce4 sce5 sc6 b c animal 1.9 1.6 1.4 1.4 2.7 1.6 air 1.3 0.2 0.6 0.9 1.4 1.0 eagle 1.0 0.1 0.1 0.6 0.9 0.1 owl 0.3 0.1 0.5 0.3 0.5 0.9 ground 0.6 1.4 0.8 0.5 1.3 0.6 cat 0.6 0.5 0.3 0.5 1.0 0.2 dog 0.0 0.9 0.5 0.0 0.3 0.4 object 2.6 1.6 1.9 2.3 2.0 2.3 metal 0.8 0.4 1.2 1.3 0.9 0.8 bike 0.5 0.1 0.4 0.7 0.4 0.2 car 0.3 0.3 0.8 0.6 0.5 0.6 wood 1.8 1.2 0.7 1.0 1.1 1.5 chair 0.9 0.6 0.1 0.9 0.2 0.8 table 0.9 0.6 0.6 0.1 0.9 0.7 as how! i'll leave exercise reader.
Comments
Post a Comment