I have a data frame including many columns. I want the col1 as the denominator and all other columns as the nominator. I have done this for just col2 (see below code). I want to do this for all other columns in shortcode.
df
Town col1 col2 col3 col4
A 8 7 5 2
B 8 4 2 3
C 8 5 8 5
here is my code for col2:
df['col2'] = df['col2'] / df['col1'
here is my result:
df
A 8 0.875000 1.0 5 2
B 8 0.500000 0.0 2 3
C 8 0.625000 1.0 8 5
I want to do the same with all cols (i.e. col3, col4....)
If this could be done in pivot_table then it will be awsome
Thanks for your help
CodePudding user response:
Use df.iloc
with df.div
:
In [2084]: df.iloc[:, 2:] = df.iloc[:, 2:].div(df.col1, axis=0)
In [2085]: df
Out[2085]:
Town col1 col2 col3 col4
0 A 8 0.875 0.625 0.250
1 B 8 0.500 0.250 0.375
2 C 8 0.625 1.000 0.625
OR use df.filter
, pd.concat
with df.div
In [2073]: x = df.filter(like='col').set_index('col1')
In [2078]: out = pd.concat([df.Town, x.div(x.index).reset_index()], 1)
In [2079]: out
Out[2079]:
Town col1 col2 col3 col4
0 A 8 0.875 0.625 0.250
1 B 8 0.500 0.250 0.375
2 C 8 0.625 1.000 0.625