CODEWITHSUNDEEP
pythonpandas
run-out
run-out

Reputation: 3184

Multi-index calculation to new columns

I have a dataframe like this. 

status               new                    allocation          
asset                csh       fi        eq        csh   fi   eq
person act_type                                                 
p1     inv           0.0      0.0  100000.0        0.0  0.0  1.0
       rsp           0.0  30000.0   20000.0        0.0  0.6  0.4
       tfsa      10000.0  40000.0       0.0        0.2  0.8  0.0

The right three columns are percent of total for each act_type. The following does calculate the columns correctly: 

# set the percent allocations
df.loc[idx[:,:],idx["allocation",'csh']] = df.loc[idx[:,:],idx["new",'csh']] / df.loc[idx[:,:],idx["new",:]].sum(axis=1)
df.loc[idx[:,:],idx["allocation",'fi']] = df.loc[idx[:,:],idx["new",'fi']] / df.loc[idx[:,:],idx["new",:]].sum(axis=1)
df.loc[idx[:,:],idx["allocation",'eq']] = df.loc[idx[:,:],idx["new",'eq']] / df.loc[idx[:,:],idx["new",:]].sum(axis=1)

I have tried to do these calculations on one line combining 'csh', 'fi', 'eq' as follows: 

df.loc[idx[:,:],idx["new", ('csh', 'fi', 'eq')]] / df.loc[idx[:,:],idx["new",:]].sum(axis=1)

But this results in ValueError: cannot join with no level specified and no overlapping names

Any suggestions how I can reduce these three lines to one line of code so that i'm dividing ('csh','fi','eq') by the account total and getting percents in the next columns?

Upvotes: 1

Views: 84

Answers (1)

jezrael
jezrael

Reputation: 863166

First idx[:,:] should be simplify by :, then use DataFrame.div by axis=0 and for new columns use rename with DataFrame.join:

df1=df.loc[:, idx["new",('csh', 'fi', 'eq')]].div(df.loc[:, idx["new",:]].sum(axis=1),axis=0)
df = df.join(df1.rename(columns={'new':'allocation'}, level=0))
print (df)
status               new                    allocation          
asset                csh       fi        eq        csh   fi   eq
person act_type                                                 
p1     inv           0.0      0.0  100000.0        0.0  0.0  1.0
       rsp           0.0  30000.0   20000.0        0.0  0.6  0.4
       tfsa      10000.0  40000.0       0.0        0.2  0.8  0.0

Upvotes: 2

Related Questions