0

I have a dataframe with 10 columns (in which the last column is the sum of all the values in each row), and I need to divide each value of the dataframe by the corresponding total column.

Cluster 1 2 3 4 5 6 7 8 9  Total
Sector
   1    3 2 1 3 1 2 1 1 2  16
   2    2 2 2 3 1 2 1 1 2  16
   3    1 1 1 3 1 2 1 1 2  13
   4    4 1 1 3 1 2 1 1 2  16
   5    3 2 3 3 1 2 1 1 2  18

And I need the new dataframe as:

Cluster  1     2    3    4    5    6    7    8    9  Total
Sector
   1    3/16 2/16 1/16 3/16 1/16 2/16 1/16 1/16 2/16  16
   2    2/16 2/16 2/16 3/16 1/16 2/16 1/16 1/16 2/16  16
   3    1/13 1/13 1/13 3/13 1/13 2/13 1/13 1/13 2/13  13
   4    .....
   5    ....
123mcgr
  • 5
  • 1
  • You can control the axis where the division takes place with the `.division` method. `df[['1', '2', '3', '4', '5', '6', '7' ,'8', '9']].divide(df['Total'], axis="index")` – tdelaney Oct 14 '22 at 15:03

1 Answers1

1

here is one way to do it

df.iloc[:,1:-1]=df.iloc[:,1:-1].div(df.iloc[:,-1], axis=0)
df

    Cluster     1   2   3   4   5   6   7   8   9   Total
0   1   0.187500    0.125000    0.062500    0.187500    0.062500    0.125000    0.062500    0.062500    0.125000    16
1   2   0.125000    0.125000    0.125000    0.187500    0.062500    0.125000    0.062500    0.062500    0.125000    16
2   3   0.076923    0.076923    0.076923    0.230769    0.076923    0.153846    0.076923    0.076923    0.153846    13
3   4   0.250000    0.062500    0.062500    0.187500    0.062500    0.125000    0.062500    0.062500    0.125000    16
4   5   0.166667    0.111111    0.166667    0.166667    0.055556    0.111111    0.055556    0.055556    0.111111    18
Naveed
  • 11,495
  • 2
  • 14
  • 21