CODEWITHSUNDEEP
pythonpandasdataframehierarchical-datamulti-index
mrtaste
mrtaste

Reputation: 13

DataFrame of function call on a specific MultiIndex DataFrame level

How to calculate (e.g.) sum on multi index DataFrame level=1 for every column and store results in a new DataFrame like getting from this_to_that.

data

T = ['t1','t2']
S = ['S1','S2']
K = ['earnings','costs']

multi_index = pd.MultiIndex.from_product([T,S])
input_df = pd.DataFrame(index = multi_index, columns = K)
input_df['earnings'] = (150.0,25.0,80.0,40.0)
input_df['costs'] = (150.0,12.5,36.36,22.72)

my overlaborate way

dc = dict()
for t in T:
    dc[t] = input_df.xs(t, level = 0, axis = 0).apply(sum, axis = 0)

dc_to_df = pd.concat(dc)
dc_to_df = pd.DataFrame(dc_to_df)
dc_to_df = dc_to_df.unstack(level=1)
dc_to_df.columns = dc_to_df.columns.droplevel(0)
desired_df = dc_to_df

Upvotes: 0

Views: 129

Answers (1)

Andrew L
Andrew L

Reputation: 7038

Is this what you're looking for?

input_df
       earnings   costs
t1 S1     150.0  150.00
   S2      25.0   12.50
t2 S1      80.0   36.36
   S2      40.0   22.72

input_df.groupby(level=0).sum()
    earnings   costs
t1     175.0  162.50
t2     120.0   59.08

You can assign the above output to a new dataframe.

EDIT: After looking at your output you're actually grouping on level=0.

Upvotes: 1

Related Questions