Replace multiple values across columns in a loop

Question

I want to recode multiple values across different columns.

For example:

df <- data.frame(wave = c(1,1,1,1,1,1,2,2,2,2,2,2),
                 party = rep(c("A", "A", "A", "B", "B", "B"), 2),
                 s_item = rep(c(3,4,5,1,2,6), 2), 
                 s_item2 = rep(c(1,2,3,4,5,6), 2),
                 s_item3 = rep(c(6,2,3,1,5,4), 2))

The data:

   wave party s_item s_item2 s_item3
1     1     A      3       1       6
2     1     A      4       2       2
3     1     A      5       3       3
4     1     B      1       4       1
5     1     B      2       5       5
6     1     B      6       6       4
7     2     A      3       1       6
8     2     A      4       2       2
9     2     A      5       3       3
10    2     B      1       4       1
11    2     B      2       5       5
12    2     B      6       6       4

For all the "s_" columns I want to recode values "1, 2, 3, 4, 5, 6" to "-1, -0.5, 0, 0.5, 1, NA".

So, what I need is the following:

   wave party s_item s_item2 s_item3
1     1     A    0.0    -1.0      NA
2     1     A    0.5    -0.5    -0.5
3     1     A    1.0     0.0     0.0
4     1     B   -1.0     0.5    -1.0
5     1     B   -0.5     1.0     1.0
6     1     B     NA      NA     0.5
7     2     A    0.0    -1.0      NA
8     2     A    0.5    -0.5    -0.5
9     2     A    1.0     0.0     0.0
10    2     B   -1.0     0.5    -1.0
11    2     B   -0.5     1.0     1.0
12    2     B     NA      NA     0.5

This works for an individual value:

for(i in df %>% select(starts_with("s_")) %>% colnames()){
  df[[i]] <-  replace(df[[i]], df[[i]] %in% 1, -1)
}

But this does not work:

for(i in df %>% select(starts_with("s_")) %>% colnames()){
  df[[i]] <-  replace(df[[i]], df[[i]] %in% 1, -1,
                      df[[i]], df[[i]] %in% 2, -0.5,
                      df[[i]], df[[i]] %in% 3, 0,
                      df[[i]], df[[i]] %in% 4, 0.5,
                      df[[i]], df[[i]] %in% 5, 1,
                      df[[i]], df[[i]] %in% 6, NA,)
}

Is there a way to do this in a single loop, like I imagine it in the code above? I am also open to different strategies of doing this, as long as the code will be as compact as possible.

score 5 · Answer 1 · answered Jul 15 '21 at 10:15

You can try the code below

v <- c(-1, -0.5, 0, 0.5, 1, NA)
idx <- startsWith(names(df), "s_")
df[idx] <- v[as.matrix(df[idx])]

which gives

> df
   wave party s_item s_item2 s_item3
1     1     A    0.0    -1.0      NA
2     1     A    0.5    -0.5    -0.5
3     1     A    1.0     0.0     0.0
4     1     B   -1.0     0.5    -1.0
5     1     B   -0.5     1.0     1.0
6     1     B     NA      NA     0.5
7     2     A    0.0    -1.0      NA
8     2     A    0.5    -0.5    -0.5
9     2     A    1.0     0.0     0.0
10    2     B   -1.0     0.5    -1.0
11    2     B   -0.5     1.0     1.0
12    2     B     NA      NA     0.5

score 1 · Answer 2 · answered Jul 15 '21 at 11:35

Using across and recode -

library(dplyr)

df <- df %>%
  mutate(across(starts_with('s_'), 
        ~recode(., `1` = -1, `2` = -0.5, `3` = 0, 
                    `4` = 0.5, `5` = 1, `6` = NA_real_)))
df


#   wave party s_item s_item2 s_item3
#1     1     A    0.0    -1.0      NA
#2     1     A    0.5    -0.5    -0.5
#3     1     A    1.0     0.0     0.0
#4     1     B   -1.0     0.5    -1.0
#5     1     B   -0.5     1.0     1.0
#6     1     B     NA      NA     0.5
#7     2     A    0.0    -1.0      NA
#8     2     A    0.5    -0.5    -0.5
#9     2     A    1.0     0.0     0.0
#10    2     B   -1.0     0.5    -1.0
#11    2     B   -0.5     1.0     1.0
#12    2     B     NA      NA     0.5

score 1 · Accepted Answer · answered Jul 15 '21 at 17:01

Using a named vector

library(dplyr)
df %>%
    mutate(across(starts_with("s_"), 
   ~ setNames(c(-1, -0.5, 0, 0.5, 1, NA), 1:6)[as.character(.)]))
   wave party s_item s_item2 s_item3
1     1     A    0.0    -1.0      NA
2     1     A    0.5    -0.5    -0.5
3     1     A    1.0     0.0     0.0
4     1     B   -1.0     0.5    -1.0
5     1     B   -0.5     1.0     1.0
6     1     B     NA      NA     0.5
7     2     A    0.0    -1.0      NA
8     2     A    0.5    -0.5    -0.5
9     2     A    1.0     0.0     0.0
10    2     B   -1.0     0.5    -1.0
11    2     B   -0.5     1.0     1.0
12    2     B     NA      NA     0.5

Replace multiple values across columns in a loop

3 Answers3