Is there a good way to write a loop for recoding a variable and then computing means in R?

I have a dataset which looks like the simulated below. The dataset represents three items of a questionnaire. One of the three items has to be repeatedly recoded and then I would like to compute the mean of the recoded and two original items. I could to this all by hand, but that is quite tedious, hence I'm searching for a nice for loop solution.

# Simulate dataset #
id <- 1:100 

EC01_01 <- sample(x = 1:7, size = 100, replace = T)
EC02_01 <- sample(x = 1:7, size = 100, replace = T)
EC03_01 <- sample(x = 1:7, size = 100, replace = T)
EC04_01 <- sample(x = 1:7, size = 100, replace = T)
EC05_01 <- sample(x = 1:7, size = 100, replace = T)
EC06_01 <- sample(x = 1:7, size = 100, replace = T)
EC07_01 <- sample(x = 1:7, size = 100, replace = T)
EC08_01 <- sample(x = 1:7, size = 100, replace = T)
EC09_01 <- sample(x = 1:7, size = 100, replace = T)
EC10_01 <- sample(x = 1:7, size = 100, replace = T)

EC01_02 <- sample(x = 1:7, size = 100, replace = T)
EC02_02 <- sample(x = 1:7, size = 100, replace = T)
EC03_02 <- sample(x = 1:7, size = 100, replace = T)
EC04_02 <- sample(x = 1:7, size = 100, replace = T)
EC05_02 <- sample(x = 1:7, size = 100, replace = T)
EC06_02 <- sample(x = 1:7, size = 100, replace = T)
EC07_02 <- sample(x = 1:7, size = 100, replace = T)
EC08_02 <- sample(x = 1:7, size = 100, replace = T)
EC09_02 <- sample(x = 1:7, size = 100, replace = T)
EC10_02 <- sample(x = 1:7, size = 100, replace = T)

EC01_03 <- sample(x = 1:7, size = 100, replace = T)
EC02_03 <- sample(x = 1:7, size = 100, replace = T)
EC03_03 <- sample(x = 1:7, size = 100, replace = T)
EC04_03 <- sample(x = 1:7, size = 100, replace = T)
EC05_03 <- sample(x = 1:7, size = 100, replace = T)
EC06_03 <- sample(x = 1:7, size = 100, replace = T)
EC07_03 <- sample(x = 1:7, size = 100, replace = T)
EC08_03 <- sample(x = 1:7, size = 100, replace = T)
EC09_03 <- sample(x = 1:7, size = 100, replace = T)
EC10_03 <- sample(x = 1:7, size = 100, replace = T)

ds <- data.frame(id, 
                 EC01_01, EC02_01, EC03_01, EC04_01, EC05_01, 
                 EC06_01, EC07_01, EC08_01, EC09_01, EC10_01,
                 EC01_02, EC02_02, EC03_02, EC04_02, EC05_02, 
                 EC06_02, EC07_02, EC08_02, EC09_02, EC10_02,
                 EC01_03, EC02_03, EC03_03, EC04_03, EC05_03, 
                 EC06_03, EC07_03, EC08_03, EC09_03, EC10_03)

My questions is whether there is a nice way to write a for loop for first recoding variables based on parts of their column names. For example, I want to recode all columns that are called EC01_01 to EC10_01, but only those that are named ECxx_01. I could do this by hand, as outlined below, but I would prefer a loop solution.

# Recode ECxx_01
ds$EC01_01r <- 8 - ds$EC01_01
ds$EC02_01r <- 8 - ds$EC02_01
ds$EC03_01r <- 8 - ds$EC03_01
ds$EC04_01r <- 8 - ds$EC04_01
ds$EC05_01r <- 8 - ds$EC05_01
ds$EC06_01r <- 8 - ds$EC06_01
ds$EC07_01r <- 8 - ds$EC07_01
ds$EC08_01r <- 8 - ds$EC08_01
ds$EC09_01r <- 8 - ds$EC09_01
ds$EC10_01r <- 8 - ds$EC10_01

After the recoding, I would like compute row means (e.g., EC01r_01, EC01_02, EC01_03). Again, I could do this by hand, as outlined below, but I would prefer a loop solution.

# Compute means
ds$EC01.mean <- rowMeans(ds[c("EC01_01r", "EC01_02", "EC01_03")], na.rm = T)
ds$EC02.mean <- rowMeans(ds[c("EC02_01r", "EC02_02", "EC02_03")], na.rm = T)
ds$EC03.mean <- rowMeans(ds[c("EC03_01r", "EC03_02", "EC03_03")], na.rm = T)
ds$EC04.mean <- rowMeans(ds[c("EC04_01r", "EC04_02", "EC04_03")], na.rm = T)
ds$EC05.mean <- rowMeans(ds[c("EC05_01r", "EC05_02", "EC05_03")], na.rm = T)
ds$EC06.mean <- rowMeans(ds[c("EC06_01r", "EC06_02", "EC06_03")], na.rm = T)
ds$EC07.mean <- rowMeans(ds[c("EC07_01r", "EC07_02", "EC07_03")], na.rm = T)
ds$EC08.mean <- rowMeans(ds[c("EC08_01r", "EC08_02", "EC08_03")], na.rm = T)
ds$EC09.mean <- rowMeans(ds[c("EC09_01r", "EC09_02", "EC09_03")], na.rm = T)
ds$EC10.mean <- rowMeans(ds[c("EC10_01r", "EC10_02", "EC10_03")], na.rm = T)

Any help/hint is much appreciated.

David

Hi @david84. I suggest the code as follow.

library(tidyverse)

# Simulate dataset #
id <- 1:100 

EC01_01 <- sample(x = 1:7, size = 100, replace = T)
EC02_01 <- sample(x = 1:7, size = 100, replace = T)
EC03_01 <- sample(x = 1:7, size = 100, replace = T)
EC04_01 <- sample(x = 1:7, size = 100, replace = T)
EC05_01 <- sample(x = 1:7, size = 100, replace = T)
EC06_01 <- sample(x = 1:7, size = 100, replace = T)
EC07_01 <- sample(x = 1:7, size = 100, replace = T)
EC08_01 <- sample(x = 1:7, size = 100, replace = T)
EC09_01 <- sample(x = 1:7, size = 100, replace = T)
EC10_01 <- sample(x = 1:7, size = 100, replace = T)

EC01_02 <- sample(x = 1:7, size = 100, replace = T)
EC02_02 <- sample(x = 1:7, size = 100, replace = T)
EC03_02 <- sample(x = 1:7, size = 100, replace = T)
EC04_02 <- sample(x = 1:7, size = 100, replace = T)
EC05_02 <- sample(x = 1:7, size = 100, replace = T)
EC06_02 <- sample(x = 1:7, size = 100, replace = T)
EC07_02 <- sample(x = 1:7, size = 100, replace = T)
EC08_02 <- sample(x = 1:7, size = 100, replace = T)
EC09_02 <- sample(x = 1:7, size = 100, replace = T)
EC10_02 <- sample(x = 1:7, size = 100, replace = T)

EC01_03 <- sample(x = 1:7, size = 100, replace = T)
EC02_03 <- sample(x = 1:7, size = 100, replace = T)
EC03_03 <- sample(x = 1:7, size = 100, replace = T)
EC04_03 <- sample(x = 1:7, size = 100, replace = T)
EC05_03 <- sample(x = 1:7, size = 100, replace = T)
EC06_03 <- sample(x = 1:7, size = 100, replace = T)
EC07_03 <- sample(x = 1:7, size = 100, replace = T)
EC08_03 <- sample(x = 1:7, size = 100, replace = T)
EC09_03 <- sample(x = 1:7, size = 100, replace = T)
EC10_03 <- sample(x = 1:7, size = 100, replace = T)

ds <- data.frame(id, 
                 EC01_01, EC02_01, EC03_01, EC04_01, EC05_01, 
                 EC06_01, EC07_01, EC08_01, EC09_01, EC10_01,
                 EC01_02, EC02_02, EC03_02, EC04_02, EC05_02, 
                 EC06_02, EC07_02, EC08_02, EC09_02, EC10_02,
                 EC01_03, EC02_03, EC03_03, EC04_03, EC05_03, 
                 EC06_03, EC07_03, EC08_03, EC09_03, EC10_03)

ds %>%
  gather(EC, value, -id) %>%
  bind_rows(., filter(., grepl("_01", .$EC)) %>%
              mutate(EC = paste0(EC, "r"), value = 8 - value)) %>%
  bind_rows(., filter(., grepl("_01r|_02|03", .$EC)) %>%
              mutate(EC = substring(EC, 1, 4)) %>%
              group_by(id, EC) %>%
              summarise(value = mean(value)) %>%
              ungroup() %>%
              mutate(EC = paste0(EC, ".mean"))) %>%
  spread(EC, value)
#>      id EC01_01 EC01_01r EC01_02 EC01_03 EC01.mean EC02_01 EC02_01r EC02_02
#> 1     1       7        1       1       5  2.333333       5        3       5
#> 2     2       1        7       4       4  5.000000       1        7       6
#> 3     3       5        3       2       4  3.000000       2        6       3
#> 4     4       5        3       5       5  4.333333       7        1       3
#> 5     5       5        3       3       7  4.333333       1        7       4
#> 6     6       6        2       7       7  5.333333       5        3       7
#> 7     7       6        2       5       7  4.666667       4        4       1
#> 8     8       4        4       3       7  4.666667       1        7       4
#> 9     9       5        3       5       2  3.333333       6        2       1
#> 10   10       7        1       5       2  2.666667       2        6       4
#> 11   11       6        2       6       7  5.000000       7        1       6
#> 12   12       1        7       2       3  4.000000       2        6       6
#> 13   13       5        3       6       4  4.333333       3        5       2
#> 14   14       1        7       2       3  4.000000       6        2       2
#> 15   15       7        1       3       1  1.666667       4        4       1
#> 16   16       3        5       4       1  3.333333       2        6       4
#> 17   17       4        4       6       1  3.666667       7        1       6
#> 18   18       4        4       1       1  2.000000       5        3       4
#> 19   19       3        5       2       1  2.666667       5        3       7
#> 20   20       7        1       1       4  2.000000       7        1       5
#> 21   21       4        4       2       3  3.000000       3        5       1
#> 22   22       6        2       6       5  4.333333       3        5       3
#> 23   23       4        4       3       7  4.666667       1        7       7
#> 24   24       6        2       5       3  3.333333       5        3       7
#> 25   25       1        7       1       3  3.666667       5        3       3
#> 26   26       7        1       2       2  1.666667       5        3       5
#> 27   27       1        7       6       1  4.666667       2        6       5
#> 28   28       5        3       6       6  5.000000       7        1       1
#> 29   29       4        4       3       2  3.000000       3        5       7
#> 30   30       2        6       2       1  3.000000       2        6       4
#> 31   31       6        2       7       1  3.333333       4        4       5
#> 32   32       4        4       6       4  4.666667       2        6       3
#> 33   33       6        2       7       5  4.666667       1        7       5
#> 34   34       2        6       5       1  4.000000       5        3       6
#> 35   35       1        7       4       5  5.333333       7        1       3
#> 36   36       3        5       4       3  4.000000       3        5       6
#> 37   37       5        3       5       6  4.666667       4        4       3
#> 38   38       6        2       2       1  1.666667       6        2       3
#> 39   39       4        4       2       1  2.333333       6        2       7
#> 40   40       1        7       3       5  5.000000       4        4       4
#> 41   41       7        1       2       1  1.333333       1        7       2
#> 42   42       3        5       4       3  4.000000       7        1       7
#> 43   43       3        5       2       4  3.666667       7        1       6
#> 44   44       5        3       4       4  3.666667       6        2       4
#> 45   45       4        4       2       6  4.000000       2        6       6
#> 46   46       4        4       2       1  2.333333       4        4       3
#> 47   47       1        7       6       4  5.666667       3        5       1
#> 48   48       1        7       6       4  5.666667       3        5       3
#> 49   49       3        5       4       3  4.000000       1        7       2
#> 50   50       6        2       2       5  3.000000       6        2       2
#> 51   51       4        4       7       1  4.000000       3        5       5
#> 52   52       2        6       1       7  4.666667       2        6       2
#> 53   53       6        2       4       5  3.666667       7        1       3
#> 54   54       7        1       7       1  3.000000       7        1       6
#> 55   55       6        2       6       1  3.000000       3        5       2
#> 56   56       5        3       3       4  3.333333       5        3       3
#> 57   57       1        7       5       6  6.000000       7        1       1
#> 58   58       3        5       1       6  4.000000       4        4       7
#> 59   59       4        4       1       6  3.666667       1        7       3
#> 60   60       5        3       4       5  4.000000       6        2       5
#> 61   61       3        5       2       4  3.666667       5        3       2
#> 62   62       2        6       4       5  5.000000       6        2       5
#> 63   63       4        4       4       3  3.666667       2        6       4
#> 64   64       6        2       6       4  4.000000       2        6       2
#> 65   65       7        1       5       6  4.000000       1        7       3
#> 66   66       1        7       7       6  6.666667       2        6       6
#> 67   67       7        1       3       2  2.000000       7        1       1
#> 68   68       6        2       1       5  2.666667       7        1       1
#> 69   69       4        4       5       3  4.000000       1        7       1
#> 70   70       2        6       6       3  5.000000       4        4       1
#> 71   71       6        2       3       2  2.333333       1        7       7
#> 72   72       5        3       5       4  4.000000       3        5       6
#> 73   73       6        2       3       4  3.000000       7        1       5
#> 74   74       1        7       4       5  5.333333       5        3       3
#> 75   75       5        3       3       7  4.333333       1        7       4
#> 76   76       4        4       1       6  3.666667       1        7       7
#> 77   77       4        4       4       7  5.000000       1        7       2
#> 78   78       5        3       2       3  2.666667       4        4       3
#> 79   79       4        4       5       1  3.333333       6        2       5
#> 80   80       5        3       6       5  4.666667       7        1       5
#> 81   81       5        3       1       6  3.333333       7        1       2
#> 82   82       1        7       5       2  4.666667       4        4       7
#> 83   83       3        5       4       7  5.333333       2        6       2
#> 84   84       1        7       6       4  5.666667       3        5       6
#> 85   85       4        4       1       6  3.666667       6        2       1
#> 86   86       7        1       7       4  4.000000       3        5       3
#> 87   87       3        5       7       6  6.000000       3        5       5
#> 88   88       3        5       2       3  3.333333       1        7       4
#> 89   89       6        2       7       1  3.333333       7        1       4
#> 90   90       2        6       4       6  5.333333       5        3       6
#> 91   91       6        2       6       3  3.666667       7        1       6
#> 92   92       6        2       7       5  4.666667       3        5       2
#> 93   93       2        6       1       5  4.000000       7        1       1
#> 94   94       5        3       6       5  4.666667       4        4       6
#> 95   95       4        4       6       7  5.666667       7        1       5
#> 96   96       3        5       7       4  5.333333       6        2       7
#> 97   97       2        6       1       5  4.000000       6        2       4
#> 98   98       2        6       4       1  3.666667       7        1       7
#> 99   99       4        4       4       3  3.666667       4        4       5
#> 100 100       1        7       2       6  5.000000       7        1       1
#>     EC02_03 EC02.mean EC03_01 EC03_01r EC03_02 EC03_03 EC03.mean EC04_01
#> 1         6  4.666667       7        1       3       3      3.50       3
#> 2         4  5.666667       7        1       4       4      4.00       6
#> 3         7  5.333333       1        7       1       2      2.75       2
#> 4         2  2.000000       6        2       7       1      4.00       3
#> 5         7  6.000000       4        4       4       4      4.00       1
#> 6         1  3.666667       1        7       5       4      4.25       2
#> 7         2  2.333333       6        2       3       3      3.50       3
#> 8         7  6.000000       2        6       1       3      3.00       1
#> 9         6  3.000000       1        7       1       3      3.00       6
#> 10        3  4.333333       3        5       6       7      5.25       1
#> 11        5  4.000000       7        1       3       7      4.50       4
#> 12        7  6.333333       2        6       4       2      3.50       5
#> 13        5  4.000000       4        4       3       4      3.75       5
#> 14        4  2.666667       6        2       6       6      5.00       2
#> 15        5  3.333333       1        7       2       1      2.75       1
#> 16        1  3.666667       2        6       4       4      4.00       4
#> 17        2  3.000000       6        2       2       2      3.00       6
#> 18        6  4.333333       3        5       1       3      3.00       5
#> 19        1  3.666667       2        6       7       6      5.25       4
#> 20        4  3.333333       1        7       2       1      2.75       5
#> 21        3  3.000000       1        7       3       4      3.75       3
#> 22        5  4.333333       2        6       4       7      4.75       1
#> 23        6  6.666667       2        6       5       6      4.75       7
#> 24        5  5.000000       2        6       5       3      4.00       7
#> 25        5  3.666667       1        7       5       2      3.75       6
#> 26        5  4.333333       3        5       1       2      2.75       4
#> 27        4  5.000000       2        6       4       4      4.00       1
#> 28        7  3.000000       4        4       1       6      3.75       3
#> 29        1  4.333333       1        7       1       6      3.75       6
#> 30        7  5.666667       2        6       7       2      4.25       3
#> 31        7  5.333333       3        5       1       2      2.75       4
#> 32        3  4.000000       6        2       3       2      3.25       3
#> 33        1  4.333333       2        6       7       5      5.00       1
#> 34        3  4.000000       2        6       2       3      3.25       1
#> 35        4  2.666667       2        6       5       5      4.50       2
#> 36        4  5.000000       6        2       6       2      4.00       4
#> 37        2  3.000000       4        4       4       1      3.25       4
#> 38        7  4.000000       3        5       4       5      4.25       1
#> 39        7  5.333333       6        2       5       4      4.25       1
#> 40        1  3.000000       6        2       7       1      4.00       1
#> 41        7  5.333333       3        5       4       6      4.50       1
#> 42        7  5.000000       1        7       5       6      4.75       5
#> 43        5  4.000000       6        2       2       6      4.00       5
#> 44        7  4.333333       3        5       2       1      2.75       3
#> 45        2  4.666667       7        1       2       1      2.75       7
#> 46        4  3.666667       3        5       1       3      3.00       6
#> 47        1  2.333333       3        5       3       7      4.50       1
#> 48        1  3.000000       1        7       7       2      4.25       5
#> 49        6  5.000000       6        2       2       3      3.25       4
#> 50        4  2.666667       3        5       2       3      3.25       5
#> 51        5  5.000000       4        4       1       3      3.00       7
#> 52        2  3.333333       7        1       4       7      4.75       7
#> 53        1  1.666667       4        4       1       2      2.75       2
#> 54        5  4.000000       1        7       3       3      3.50       3
#> 55        2  3.000000       4        4       2       1      2.75       5
#> 56        4  3.333333       2        6       5       7      5.00       7
#> 57        1  1.000000       4        4       2       3      3.25       5
#> 58        1  4.000000       1        7       5       6      4.75       1
#> 59        2  4.000000       7        1       7       2      4.25       4
#> 60        2  3.000000       4        4       5       7      5.00       7
#> 61        6  3.666667       3        5       6       4      4.50       7
#> 62        4  3.666667       4        4       5       6      4.75       5
#> 63        1  3.666667       4        4       5       6      4.75       5
#> 64        6  4.666667       3        5       2       3      3.25       3
#> 65        4  4.666667       3        5       1       7      4.00       2
#> 66        1  4.333333       3        5       1       3      3.00       7
#> 67        6  2.666667       1        7       1       7      4.00       4
#> 68        3  1.666667       5        3       7       2      4.25       2
#> 69        2  3.333333       6        2       6       4      4.50       1
#> 70        6  3.666667       2        6       2       2      3.00       7
#> 71        1  5.000000       6        2       5       2      3.75       3
#> 72        5  5.333333       6        2       4       1      3.25       2
#> 73        6  4.000000       5        3       7       1      4.00       7
#> 74        6  4.000000       6        2       6       1      3.75       2
#> 75        7  6.000000       2        6       6       6      5.00       2
#> 76        6  6.666667       5        3       4       7      4.75       3
#> 77        2  3.666667       4        4       4       1      3.25       6
#> 78        2  3.000000       5        3       2       2      3.00       6
#> 79        7  4.666667       5        3       3       1      3.00       4
#> 80        7  4.333333       6        2       2       6      4.00       6
#> 81        5  2.666667       4        4       5       1      3.50       2
#> 82        5  5.333333       3        5       6       4      4.50       3
#> 83        2  3.333333       6        2       7       7      5.50       4
#> 84        7  6.000000       3        5       4       7      4.75       5
#> 85        4  2.333333       6        2       7       5      5.00       4
#> 86        7  5.000000       6        2       2       6      4.00       1
#> 87        4  4.666667       4        4       1       5      3.50       6
#> 88        2  4.333333       2        6       2       4      3.50       4
#> 89        6  3.666667       7        1       2       5      3.75       7
#> 90        7  5.333333       6        2       2       7      4.25       3
#> 91        5  4.000000       6        2       4       7      4.75       5
#> 92        4  3.666667       5        3       6       1      3.75       7
#> 93        5  2.333333       1        7       3       5      4.00       5
#> 94        4  4.666667       1        7       3       3      3.50       2
#> 95        7  4.333333       3        5       3       5      4.00       4
#> 96        1  3.333333       3        5       4       7      4.75       3
#> 97        3  3.000000       6        2       7       7      5.50       3
#> 98        6  4.666667       5        3       3       5      4.00       6
#> 99        7  5.333333       4        4       7       2      4.25       3
#> 100       6  2.666667       6        2       5       2      3.75       7

Created on 2020-02-08 by the reprex package (v0.3.0)

Hi @raytong,

Thanks for the super helpful reply!
This is gonna save me a lot of time.

David

my attempt assumes the starting input is in 'ds' dataframe

n <- names(ds) # starting list of columns for convenience
# recode all columns  named ECxx_01 naming the result like ECxx_01_r having subtracted them from 8
sol2a <- ds %>% mutate_at(.vars = n[startsWith(n, "EC") & endsWith(n,"_01")],
                          .funs = list(r = ~( 8 - . )))

# now rowmeans of combinations of ECxx_01_r,ECxx_02,ECxx_03
dorowmeans <- function(df,ecnum){
  ecn <- paste0("EC",formatC(ecnum,width = 2,flag = "0"))
  colselect <- c(paste0(ecn,"_01_r"),
                 paste0(ecn,"_02"),
                 paste0(ecn,"_03"))
  df[[paste0(ecn,".mean")]] <- rowMeans(df[colselect])
  return(df)
}

sol2b <- sol2a
for (i in 1:10)
{
  sol2b <- dorowmeans(sol2b,i)
}
2 Likes

@nirgrahamuk Thanks for your help! Much appreciated.