one column as denominator and many as nominator pandas

user_14441059 0
Viewed 27

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

pandas
1 Answers

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
user_58208140
Related