CODEWITHSUNDEEP
r
bna3p45vnh
bna3p45vnh

Reputation: 141

how to normalize the values in one column for each type defined in another column

BEFORE
-------------------------------
ID            measure     value
-------------------------------
original      weight      120.0
variant1      weight      110.0
variant2      weight       78.0
variant3      weight      140.0
original      speed        56.0
variant1      speed        54.0
variant2      speed        56.0
variant3      speed        61.0
original      height        6.7
variant1      height        6.3
variant2      height        4.5
variant3      height        5.3
-------------------------------

say I had a table like that. For each type of "measure", I want to normalize the "values" with the values find in "original". So that the result looks like. Append the new column named "norm_value"

AFTER
------------------------------------
ID            measure     norm_value
------------------------------------
original      weight       1.0
variant1      weight       0.91
variant2      weight       0.65
variant3      weight       1.16
original      speed        1.0
variant1      speed        0.96
variant2      speed        1.0
variant3      speed        1.08
original      height       1.0
variant1      height       0.94
variant2      height       0.67
variant3      height       0.79
-------------------------------

any help is greatly appreciated.

Upvotes: 3

Views: 43

Answers (1)

tmfmnk
tmfmnk

Reputation: 39858

With dplyr, you can try:

df %>%
 group_by(measure) %>%
 mutate(norm_value = value/value[ID == "original"])

   ID       measure value norm_value
   <chr>    <chr>   <dbl>      <dbl>
 1 original weight  120        1    
 2 variant1 weight  110        0.917
 3 variant2 weight   78        0.65 
 4 variant3 weight  140        1.17 
 5 original speed    56        1    
 6 variant1 speed    54        0.964
 7 variant2 speed    56        1    
 8 variant3 speed    61        1.09 
 9 original height    6.7      1    
10 variant1 height    6.3      0.940
11 variant2 height    4.5      0.672
12 variant3 height    5.3      0.791

And the same idea with base R:

with(df, value/ave((ID == "original") * value, measure, FUN = max))

Upvotes: 5

Related Questions