calculate logarithm and mean, add the result to dataset in R

user_11050180 0
Viewed 132

In the following dataset

  code test1 ref1 test2 ref2
1    A  19.0 19.5  31.8   33
2    B  17.6 19.5  29.1   33
3    C  19.6 19.5  31.4   33
4    D  20.0 19.5  33.5   33
5    E  17.8 19.5  30.5   33
6    F  18.6 19.5  31.7   33
7    G  19.0 19.5  31.2   33
8    H  18.2 19.5  29.6   33
9    I  18.3 19.5  30.3   33

I want to calculate the difference between logs of the "test" and "ref" columns.

log(test1)- log(ref1) = a
log(test2)- log(ref2) = b

Then I want to calculate the mean of a and b for each row and save the result on a new column in the dataset.

I tried this function:

f <- function(x) {
  x[paste0(x, sep = "_log")] <<- log(x[x]) 
}

sapply(names(x)[-1], f)

but I can only calculate the logs of each column and add the result to new columns.

If I continue like this, my dataset will be too long. Could anyone help me to create a code that performs all calculations and add a single column as the final result?

Many thanks in advance

mean dataframe r logging
3 Answers

We can use

library(dplyr)
df1 %>%
    mutate(a = log(test1)- log(ref1), b = log(test2)- log(ref2),
       Mean = rowMeans(cbind(a, b), na.rm = TRUE))

-output

 code test1 ref1 test2 ref2            a           b        Mean
1    A  19.0 19.5  31.8   33 -0.025975486 -0.03704127 -0.03150838
2    B  17.6 19.5  29.1   33 -0.102515564 -0.12576939 -0.11414248
3    C  19.6 19.5  31.4   33  0.005115101 -0.04969967 -0.02229228
4    D  20.0 19.5  33.5   33  0.025317808  0.01503788  0.02017784
5    E  17.8 19.5  30.5   33 -0.091216008 -0.07878088 -0.08499844
6    F  18.6 19.5  31.7   33 -0.047252885 -0.04019088 -0.04372188
7    G  19.0 19.5  31.2   33 -0.025975486 -0.05608947 -0.04103248
8    H  18.2 19.5  29.6   33 -0.068992871 -0.10873320 -0.08886304
9    I  18.3 19.5  30.3   33 -0.063513406 -0.08535985 -0.07443663

Or if there are many pairs, do this automatically

library(stringr)
df1 %>%
     mutate(Mean = rowMeans(across(starts_with('test'), 
    ~ log(.) - log(get(str_replace(cur_column(), "test", "ref")))), na.rm = TRUE))

-output

code test1 ref1 test2 ref2        Mean
1    A  19.0 19.5  31.8   33 -0.03150838
2    B  17.6 19.5  29.1   33 -0.11414248
3    C  19.6 19.5  31.4   33 -0.02229228
4    D  20.0 19.5  33.5   33  0.02017784
5    E  17.8 19.5  30.5   33 -0.08499844
6    F  18.6 19.5  31.7   33 -0.04372188
7    G  19.0 19.5  31.2   33 -0.04103248
8    H  18.2 19.5  29.6   33 -0.08886304
9    I  18.3 19.5  30.3   33 -0.07443663

data

df1 <- structure(list(code = c("A", "B", "C", "D", "E", "F", "G", "H", 
"I"), test1 = c(19, 17.6, 19.6, 20, 17.8, 18.6, 19, 18.2, 18.3
), ref1 = c(19.5, 19.5, 19.5, 19.5, 19.5, 19.5, 19.5, 19.5, 19.5
), test2 = c(31.8, 29.1, 31.4, 33.5, 30.5, 31.7, 31.2, 29.6, 
30.3), ref2 = c(33L, 33L, 33L, 33L, 33L, 33L, 33L, 33L, 33L)),
 class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6", "7", "8", "9"))
user_37322710

Actually it's cool to paste the numbers to the names. Here a solution that fits to your example. If you have four pairs just use 1:4 in the sapply.

rowMeans(sapply(1:2, \(i) 
                log(dat[[paste0('test', i)]]) - log(dat[[paste0('ref', i)]])), 
         na.rm=TRUE)
# [1] -0.03150838 -0.11414248 -0.02229228  0.02017784 -0.08499844 -0.04372188
# [7] -0.04103248 -0.08886304 -0.07443663

Note: \(i) means function(i) in R versions prior to R4.1.0.

user_65740380

If you're doing things like this often with your data, you might find it more convenient to store the data in "long" format, because then operations like this can be done a little more naturally

Make into long format

df1_long <- 
  df1 %>% 
    pivot_longer(names_pattern = '(test|ref)(\\d+)',
                 names_to = c('name', 'num'),
                 cols = -1) %>% 
    group_by(code, num) %>% 
    arrange(name, .by_group = TRUE)
# # A tibble: 36 x 4
# # Groups:   code, num [18]
# code  name  num   value
# <chr> <chr> <chr> <dbl>
#   1 A     ref   1      19.5
#   2 A     test  1      19  
#   3 A     ref   2      33  
#   4 A     test  2      31.8
#   5 B     ref   1      19.5
#   6 B     test  1      17.6
#   7 B     ref   2      33  
#   8 B     test  2      29.1
#   9 C     ref   1      19.5
#   10 C     test  1      19.6
#   # … with 26 more rows

With data in this shape it's more obvious how to get the summary by group

df1_long %>% 
  summarise(dlog = diff(log(value))) %>% 
  summarise(mean_dlog = mean(dlog))
# # A tibble: 9 x 2
# code  mean_dlog
# <chr>     <dbl>
#   1 A       -0.0315
#   2 B       -0.114 
#   3 C       -0.0223
#   4 D        0.0202
#   5 E       -0.0850
#   6 F       -0.0437
#   7 G       -0.0410
#   8 H       -0.0889
#   9 I       -0.0744

You could also use this method while still keeping your data in the same format, but in that case you may as well use the method in the other answers

df1 %>% 
  pivot_longer(names_pattern = '(test|ref)(\\d+)',
               names_to = c('name', 'num'),
               cols = -1) %>% 
  group_by(code, num) %>% 
  arrange(name) %>% 
  summarise(dlog = diff(log(value))) %>% 
  summarise(mean_dlog = mean(dlog)) %>% 
  mutate(.data = df1, mean_dlog = .$mean_dlog)
# code test1 ref1 test2 ref2   mean_dlog
# 1    A  19.0 19.5  31.8   33 -0.03150838
# 2    B  17.6 19.5  29.1   33 -0.11414248
# 3    C  19.6 19.5  31.4   33 -0.02229228
# 4    D  20.0 19.5  33.5   33  0.02017784
# 5    E  17.8 19.5  30.5   33 -0.08499844
# 6    F  18.6 19.5  31.7   33 -0.04372188
# 7    G  19.0 19.5  31.2   33 -0.04103248
# 8    H  18.2 19.5  29.6   33 -0.08886304
# 9    I  18.3 19.5  30.3   33 -0.07443663
user_67627550
Related