I have the following dataframe:
df=pd.DataFrame(np.random.randint(1,3,27).reshape((9,3)),\
index= [['KH','KH','KH','KH','KH','KH','KH','KH','KH'],\
['AOK','AOK','AOK','DOK','DOK','DOK','ROK','ROK','ROK'],\
['A','B','C','A','B','C','A','B','C']],\
columns=['JE','TE','DE']\
)
df.index.names = ['Deck','Status','Urs']
df
Out[116]:
JE TE DE
Deck Status Urs
KH AOK A 1 1 2
B 1 2 2
C 2 1 1
DOK A 2 2 1
B 1 2 1
C 1 2 2
ROK A 2 2 2
B 1 1 2
C 1 2 1
Now i want to simply append a column 'JErel' to it. This column should contain the values from 'JE', but as relative fraction. The fraction should relate to the total sum of the 'Status' index groups.
I can Access the sum via:
df.loc[('KH','AOK')]['JE'].sum()
Out[117]: 4
The column should result in something like:
1/df.loc[('KH','AOK')]['JE'].sum(),
1/df.loc[('KH','AOK')]['JE'].sum(),
2/df.loc[('KH','AOK')]['JE'].sum() and then,
2/df.loc[('KH','DOK')]['JE'].sum(), ...
,,,, Thats how far i got.
How do i add the column dynamically like with apply(Lambda...) or so?
You can use groupby.transform to calculate the column JE sum which has the same length and index as the original data frame, and then divide JE column by it:
df['JErel'] = df.JE.div(df.groupby(level=['Deck','Status']).JE.transform('sum'))
df
# JE TE DE JErel
# Deck Status Urs
# KH AOK A 2 2 1 0.400000
# B 2 2 1 0.400000
# C 1 1 2 0.200000
# DOK A 1 1 2 0.250000
# B 2 1 2 0.500000
# C 1 1 1 0.250000
# ROK A 2 1 2 0.333333
# B 2 1 2 0.333333
# C 2 1 1 0.333333