How to retain column with non-numerical objects after dividing all rows by the last row in pandas dataframe?

user_19373823 0
Viewed 54

Here is a pandas dataframe (df) as show below:

print(df)

   Sample_name  C14-Cer_mean  C16-Cer_mean  C18-Cer_mean  C18:1-Cer_mean 
0          1 1      0.124749      0.285659     35.302029        8.173144   
1         1 10      0.332976      0.656197     39.220933        6.446620   
2         1 13      0.227423      0.272440     26.866943        5.763723   
3         1 14      0.128359      0.604903     29.848169        6.801633   
4          1 5      0.204000      0.242652     21.354158        4.637632   
5          1 6      0.122697      0.412868     25.168258        5.866785   
6          1 9      0.161679      0.569781     28.707720        6.525267   
7        Blank      0.408713      0.526573      0.531430        0.111025

I wanted to get a dataframe where values in all rows have been divided by the values in the row with Sample_name "Blank" (normalizing to sample "Blank") without losing the "Sample_name" column. However, using the following script, I get the result but the Sample_name column is lost.

print(df.loc[:,'C14-Cer_mean':].div(df.iloc[-1]['C14-Cer_mean':]))

 C14-Cer_mean C16-Cer_mean C18-Cer_mean C18:1-Cer_mean
0     0.305224     0.542487      66.4284        73.6153
1     0.814694      1.24617      73.8026        58.0646
2     0.556437     0.517383      50.5559        51.9137
3     0.314057      1.14875      56.1658        61.2622
4     0.499128     0.460814      40.1824        41.7711
5     0.300203     0.784066      47.3595         52.842
6     0.395581      1.08206      54.0198         58.773
7            1            1            1              1

How to retain the "Sample_name" column??

Any suggestions on how to retain the Sample Name, i.e., the first column containing non-numerical objects, so that I get the desired df as shown below:-

Sample_name C14-Cer_mean    C16-Cer_mean    C18-Cer_mean    C18:1-Cer_mean
1 1     0.305224    0.542487    66.42840    73.6153
1 10    0.814694    1.246170    73.80260    58.0646
1 13    0.556437    0.517383    50.55590    51.9137
1 14    0.314057    1.148750    56.16580    61.2622
1 5     0.499128    0.460814    40.18240    41.7711
1 6     0.300203    0.784066    47.35950    52.8420
1 9     0.395581    1.082060    54.01980    8.77300
7 Blank 1.000000    1.000000    1.000000    1.00000
pandas dataframe python
1 Answers

You can use loc for select all columns after C14-Cer_mean and assign back, here are some alternatives without loc with iloc together:

 #select by position, position of C14-Cer_mean is count by get_loc
 df2 = df.iloc[-1, df.columns.get_loc('C14-Cer_mean'):]

 #select by position, from second column
 df2 = df.iloc[-1, 1:]

 #seelct by labels, last row by last index
 df2 = df.loc[df.index[-1],'C14-Cer_mean':]

 df.loc[:,'C14-Cer_mean':] = df.loc[:,'C14-Cer_mean':].div(df2)

print(df)
  Sample_name C14-Cer_mean C16-Cer_mean C18-Cer_mean C18:1-Cer_mean
0         1 1     0.305224     0.542487    66.428371      73.615348
1        1 10     0.814694     1.246165    73.802633       58.06458
2        1 13     0.556437     0.517383     50.55594       51.91374
3        1 14     0.314057     1.148754    56.165758      61.262175
4         1 5     0.499128     0.460814    40.182447      41.771061
5         1 6     0.300203     0.784066    47.359498      52.842018
6         1 9     0.395581     1.082055    54.019758      58.772952
7       Blank          1.0          1.0          1.0            1.0
user_29010020
Related