Divide all values by reference row

user_3635471 0
Viewed 105

Although this seems similar to this, I'm looking for a "tidy" solution...

Let's look at the following data (it's rocks compositions for some chemical elements, if you are curious):

# A tibble: 4 x 15
  Rock        La     Ce     Pr     Nd    Sm    Eu    Gd     Tb    Dy     Ho    Er     Tm    Yb     Lu
  <chr>      <dbl>  <dbl>  <dbl>  <dbl> <dbl> <dbl> <dbl>  <dbl> <dbl>  <dbl> <dbl>  <dbl> <dbl>  <dbl>
1 Upper CC  31     63     7.1    27     4.7   1     4     0.7    3.9   0.83   2.3   0.3    1.96  0.31  
2 Middle CC 24     53     5.8    25     4.6   1.4   4     0.7    3.8   0.82   2.3   0.32   2.2   0.4   
3 Lower CC   8     20     2.4    11     2.8   1.1   3.1   0.48   3.1   0.68   1.9   0.24   1.5   0.25  
4 chondrite  0.235  0.603 0.0891  0.452 0.147 0.056 0.197 0.0363 0.243 0.0556 0.159 0.0242 0.162 0.0243

(see at the end for the dput)

This is made of three samples and a reference value (chondrite). I want to normalize the value of each element by the chondrite, for each sample, i.e. get something like that:

# A tibble: 4 x 15
  Rock         La    Ce    Pr    Nd    Sm    Eu    Gd    Tb    Dy    Ho    Er    Tm    Yb    Lu
  <chr>     <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 Upper CC  132.  104.   79.7  59.7  32.0  17.9  20.3  19.3  16.0  14.9  14.5 12.4  12.1   12.8
2 Middle CC 102.   87.9  65.1  55.3  31.3  25    20.3  19.3  15.6  14.8  14.5 13.2  13.6   16.5
3 Lower CC   34.0  33.2  26.9  24.3  19.0  19.6  15.7  13.2  12.8  12.2  12.0  9.92  9.26  10.3
4 chondrite   1     1     1     1     1     1     1     1     1     1     1    1     1      1

In which, of course, the first 132 for df["Upper CC","La"] comes from 31 / 0.235, i.e. df["Upper CC","La"] / df["chondrite","La"]

This is trivial in excel, and can be done in plain R with something along the lines of

apply(df[,-1],1,FUN=function(z){return(z/df[4,-1])})

Give or take some unlist() and other niceties.

But how do I do this in tidyverse idiom ? I started constructing

df %>% mutate(across( where(is.numeric), ... ? .... ) )

... but could not go further.

Generalize/related question: instead of normalizing by df[4,], normalize by an arbitrary named vector.

dput(df)

structure(list(Rock = c("Upper CC", "Middle CC", "Lower CC", 
"chondrite"), La = c(31, 24, 8, 0.2347), Ce = c(63, 53, 20, 0.6032
), Pr = c(7.1, 5.8, 2.4, 0.0891), Nd = c(27, 25, 11, 0.4524), 
    Sm = c(4.7, 4.6, 2.8, 0.1471), Eu = c(1, 1.4, 1.1, 0.056), 
    Gd = c(4, 4, 3.1, 0.1966), Tb = c(0.7, 0.7, 0.48, 0.0363), 
    Dy = c(3.9, 3.8, 3.1, 0.2427), Ho = c(0.83, 0.82, 0.68, 0.0556
    ), Er = c(2.3, 2.3, 1.9, 0.1589), Tm = c(0.3, 0.32, 0.24, 
    0.0242), Yb = c(1.96, 2.2, 1.5, 0.1625), Lu = c(0.31, 0.4, 
    0.25, 0.0243)), row.names = c(NA, -4L), class = c("tbl_df", 
"tbl", "data.frame"))
dplyr r matrix
3 Answers

You can use :

library(dplyr)

df %>% mutate(across(where(is.numeric), ~./.[Rock == "chondrite"]))

#   Rock     La    Ce    Pr    Nd    Sm    Eu    Gd    Tb    Dy
#  <chr>   <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#1 Upper … 132.  104.   79.7  59.7  32.0  17.9  20.3  19.3  16.1
#2 Middle… 102.   87.9  65.1  55.3  31.3  25.0  20.3  19.3  15.7
#3 Lower …  34.1  33.2  26.9  24.3  19.0  19.6  15.8  13.2  12.8
#4 chondr…   1     1     1     1     1     1     1     1     1  
# … with 5 more variables: Ho <dbl>, Er <dbl>, Tm <dbl>,
#   Yb <dbl>, Lu <dbl>
user_39629140

Using matrix calculations.

m <- t(dat[-1])
dat[-1] <- t(m / m[,4])
# Rock        La        Ce       Pr       Nd       Sm       Eu       Gd       Tb       Dy       Ho       Er        Tm        Yb       Lu
# 1  Upper CC 131.91489 104.47761 79.68575 59.73451 31.97279 17.85714 20.30457 19.28375 16.04938 14.92806 14.46541 12.396694 12.098765 12.75720
# 2 Middle CC 102.12766  87.89386 65.09540 55.30973 31.29252 25.00000 20.30457 19.28375 15.63786 14.74820 14.46541 13.223140 13.580247 16.46091
# 3  Lower CC  34.04255  33.16750 26.93603 24.33628 19.04762 19.64286 15.73604 13.22314 12.75720 12.23022 11.94969  9.917355  9.259259 10.28807
# 4 chondrite   1.00000   1.00000  1.00000  1.00000  1.00000  1.00000  1.00000  1.00000  1.00000  1.00000  1.00000  1.000000  1.000000  1.00000

Data

dat <- structure(list(Rock = c("Upper CC", "Middle CC", "Lower CC", 
"chondrite"), La = c(31, 24, 8, 0.235), Ce = c(63, 53, 20, 0.603
), Pr = c(7.1, 5.8, 2.4, 0.0891), Nd = c(27, 25, 11, 0.452), 
    Sm = c(4.7, 4.6, 2.8, 0.147), Eu = c(1, 1.4, 1.1, 0.056), 
    Gd = c(4, 4, 3.1, 0.197), Tb = c(0.7, 0.7, 0.48, 0.0363), 
    Dy = c(3.9, 3.8, 3.1, 0.243), Ho = c(0.83, 0.82, 0.68, 0.0556
    ), Er = c(2.3, 2.3, 1.9, 0.159), Tm = c(0.3, 0.32, 0.24, 
    0.0242), Yb = c(1.96, 2.2, 1.5, 0.162), Lu = c(0.31, 0.4, 
    0.25, 0.0243)), class = "data.frame", row.names = c("1", 
"2", "3", "4"))
user_65740380

Using data.table

library(data.table)
setDT(df1)[, (names(df1)[-1]) := lapply(.SD, function(x) 
       x/x[match( "chondrite", Rock)]), .SDcols = -1]
user_37322710
Related