How to recode multiple columns in R

Question

I tried my best to recode multiple columns, but I still struggle to do it. Here what I have done:

df<-read.table(text="ZR1 Time1 ZR2 Time2 ZR3 Time3
  A 60  A   56  B   44
  C 61  B   44  D   78
  D 62  C   78  E   66
  E 58  D   46  B   45
  A 54  B   23  B   23
  A 57  E   24  B   100",h=T)

What I have done

for (i in 1) {
 ZRi<-paste0("ZR", i)
Zi<-paste0("Z",i)}
df[,Zi]=c(A=4,B=3,C=2,D=1,E=0)
 df[,Zi]=c(A=4,B=3,C=2,D=1,E=0)[df[,ZRi]]

I got this:

ZR1 Time1 ZR2 Time2 ZR3 Time3 Z1
1   A    60   A    56   B    44  4
2   C    61   B    44   D    78  3
3   D    62   C    78   E    66  2
4   E    58   D    46   B    45  1
5   A    54   B    23   B    23  4
6   A    57   E    24   B   100  4

As you can see, I could get Z1, which is wrong.

I want to get this:

ZR1 Time1   ZR2 Time2   ZR3 Time3   Z1  Z2  Z3
A   60  A   56  B   44  4   4   3
C   61  B   44  D   78  2   3   1
D   62  C   78  E   66  1   2   0
E   58  D   46  B   45  0   1   3
A   54  B   23  B   23  4   3   3
A   57  E   24  B   100 4   0   3

Just want to point out the reason behind your failure is the default behavior of `stringsAsFactors = TRUE` in `read.table` (which results in inconsistent factor levels). That's probably why we might need tidy data format in an explicitly consistent way nowadays. — Lerong, Apr 02 '18 at 21:53

IRTFM · Answer 1 · 2018-04-02T21:15:37.903

3

Here's the base approach (and probably fastest). You are just using the values of the ZR columns as an index into c(A=4,B=3,C=2,D=1,E=0) which becomes a translation table and then assigning those results to new columns in df:

df[ paste0("Z", 1:3) ] <- 
   lapply( df[ , grepl("^ZR", names(df))] , # passes "ZR" columns one-at-a-time 
               function(x) {c(A=4,B=3,C=2,D=1,E=0)[as.character(x)]})

Depending on what was intended as the purpose for these new columns, @User60 should be aware that this delivers numeric vectors

edited Apr 02 '18 at 21:15

answered Apr 02 '18 at 21:07

IRTFM

258,963
21
364
487

@ 42 I got error: Error in `[.data.table`(x, i, which = TRUE) : – Apr 02 '18 at 21:16
I'm not using `data.table` functions, I think there's a problem with a masking of a base function. You didn't indicate this was a `data.table` problem. Try it with a fresh session. – IRTFM Apr 02 '18 at 21:20
Again. Those are data.table errors that I'm not generating in a session that doesn't have data.table loaded. If we are supposed to assume that `data.table` is loaded, then it is _your_ responsibility to include code at the beginning or your example calling `library(data.table)`. (But I'm not getting that error even if I load `data.table`. So I think it's happening because you ran one of the other answers that coerced your `df`-object to be a data.table.) – IRTFM Apr 02 '18 at 21:24
p.s. @User60 you can convert a `data.table` back to just a `data.frame` with `setDF(df)` or `class(df) <- 'data.frame'`. This will allow the solution to work. – IceCreamToucan Apr 02 '18 at 21:36

moodymudskipper · Answer 2 · 2018-04-02T21:16:31.137

By playing with levels and labels you can get this:

for (i in 1:3) {
  df[[paste0("Z",i)]] <-
    factor(df[[paste0("ZR", i)]],levels=LETTERS[1:5],labels=4:0)
}
df
#   ZR1 Time1 ZR2 Time2 ZR3 Time3 Z1 Z2 Z3
# 1   A    60   A    56   B    44  4  4  3
# 2   C    61   B    44   D    78  2  3  1
# 3   D    62   C    78   E    66  1  2  0
# 4   E    58   D    46   B    45  0  1  3
# 5   A    54   B    23   B    23  4  3  3
# 6   A    57   E    24   B   100  4  0  3

The created columns with this method will be factors, to have numeric instead use the following:

for (i in 1:3) {
  df[[paste0("Z",i)]] <-
    as.numeric(as.character(factor(df[[paste0("ZR", i)]],levels=LETTERS[1:5],labels=4:0)))
}

I think that delivers a factor vector. Might or might not be what was expected. — IRTFM, Apr 02 '18 at 21:12

score 1 · Answer 3 · answered Apr 02 '18 at 22:01

Maybe this one one-liner with dplyr could help

df %>% 
  mutate_at(setNames(paste0("ZR", 1:3), paste0("Z", 1:3)), 
            ~5-as.numeric(factor(.x, levels = LETTERS[1:5])))

The trick here is to pass named vector to mutate_at to create new columns. You can coerce factor to numeric if you pre-specified the levels.

score 0 · Answer 4 · answered Apr 02 '18 at 20:32

An alternative solution using dplyr + magrittr packages

library(dplyr); library(magrittr)    
df2 <- select(df, starts_with("ZR")) %>% 
       lapply(as.character) %>% 
       mapply(`[`, list(c(A=4,B=3,C=2,D=1,E=0)), .) %>% 
       data.frame(df, .)
names(df2)[ncol(df2)-2:0] <- paste0("Z", 1:3)

IceCreamToucan · Answer 5 · 2018-04-02T21:32:43.040

Here's a more dplyr-esque method. Useful for recoding when the output isn't an integer.

library(dplyr)
# Make lookup table
lookup <- data.frame(let = LETTERS[1:5], num = 4:0, stringsAsFactors = F)
# Join with lookup table
df %>% 
  left_join(lookup, by = c('ZR1' = 'let')) %>% 
  left_join(lookup, by = c('ZR2' = 'let')) %>% 
  left_join(lookup, by = c('ZR3' = 'let')) %>% 
  rename_at(vars(matches('num')), ~paste0('Z', 1:3))

Or, with data.table

library(data.table)
lookup <- data.frame(let = LETTERS[1:5], num = 4:0, stringsAsFactors = F)
setDT(df)
df[, paste0('Z', 1:3) := lapply(df[,paste0('ZR', 1:3)], 
                                function(x) lookup$num[match(x, lookup$let)])]

How to recode multiple columns in R

5 Answers5

Linked