Compute relative frequency from count table

user_15775608 0
Viewed 248

I have a large dataframe with effective numbers of several items and want to change them into relative frequency. So I added a column that sums all values in the columns for each row :

product    apple  pear   banana    sum
Jean           3     5        1      9
Phil           2     7        1     10
Yanis          5     4        4     14

Next I have to apply the formula x/sum(X)*100 to get the relative frequency for each value in the dataframe in order to get a dataframe like this :

product      apple      pear       banana
Jean         33.33     55.55        11.11
Phil            20        70           10
Yanis        37.71     28.57        28.57

I don't really know how to do this without specifying each columns (this is just a small dataset for the example)

My code for now :

df = pd.read_csv(f, sep="\t", index_col='product')
dff = df.transpose()
dff["sum"] = dff.sum(axis=1)

Thank you in advance

pandas dataframe frequency python
3 Answers

If you have this dataframe:

  product  apple  pear  banana
0    Jean      3     5       1
1    Phil      2     7       1
2   Yanis      5     4       4

You can do:

df.iloc[:, 1:] = df.iloc[:, 1:].div(df.sum(axis=1), axis=0) * 100
print(df)

Prints:

  product      apple       pear     banana
0    Jean  33.333333  55.555556  11.111111
1    Phil  20.000000  70.000000  10.000000
2   Yanis  38.461538  30.769231  30.769231
user_100359850

With df oriented as:

         apple  pear  banana
product                     
Jean         3     5       1
Phil         2     7       1
Yanis        5     4       4

Just use:

df.div(df.sum(axis=1), axis=0)*100
>>
             apple       pear     banana
product                                 
Jean     33.333333  55.555556  11.111111
Phil     20.000000  70.000000  10.000000
Yanis    38.461538  30.769231  30.769231
user_98576310

print(df)

   product  apple  pear  banana  sum
0    Jean      3     5       1    9
1    Phil      2     7       1   10
2   Yanis      5     4       4   14

Solution

  df.drop(columns=['sum']).set_index('product').apply(lambda x: ((x/x.sum())*100).round(2), axis=1).reset_index()



product apple   pear    banana
0   Jean    33.33   55.56   11.11
1   Phil    20.00   70.00   10.00
2   Yanis   38.46   30.77   30.77
user_89869750
Related