R: Conditionally replace values in data.table column over loop

Question

I want to replace all values in one data.table column (col1). I thought to try to do it over loop but only few values are replaced. Example:

#Generate data.table
library(data.table)
dt2 <- data.table(col1=rep(c("a","b","c","d"),each=2), col2=rep(c(1,2,3,4),2),col3=rep(c(1,2,3,4),2))
> dt2
   col1 col2 col3
1:    a    1    1
2:    a    2    2
3:    b    3    3
4:    b    4    4
5:    c    1    1
6:    c    2    2
7:    d    3    3
8:    d    4    4

Change values:

#new col1 elements
new_col1 = c("WT","m1","m9","m10")
#change col1
col1_names = unique(dt$col1)
for (i in 1:length(col1_names)){
    dt2[col1==col1_names[i],col1:=new_col1[i]]
    }
> dt2
   col1 col2 col3
1:   WT    1    1
2:   WT    2    2
3:   m1    3    3
4:   m1    4    4
5:    c    1    1
6:    c    2    2
7:    d    3    3
8:    d    4    4

Values in col1 are only partially replaced. Can anybody explain it or suggest better way how to replace?

I tried to replace them at once but this does not work either properly:

#generate data.table
dt2 <- data.table(col1=rep(c("a","b","c","d"),each=2), col2=rep(c(1,2,3,4),2),col3=rep(c(1,2,3,4),2))

   col1 col2 col3
1:    a    1    1
2:    a    2    2
3:    b    3    3
4:    b    4    4
5:    c    1    1
6:    c    2    2
7:    d    3    3
8:    d    4    4
#replace values
col1_names = unique(dt$col1)
dt2[col1==col1_names,col1:=new_col1]
> dt2
   col1 col2 col3
1:   WT    1    1
2:   m1    2    2
3:   m9    3    3
4:  m10    4    4
5:   WT    1    1
6:   m1    2    2
7:   m9    3    3
8:  m10    4    4

Values are just replaced without any condition.

You can use `match` to find the position and based on that replace it `dt2[, col1 := new_col1[match(col1, group_names)]]` — akrun, Apr 26 '17 at 13:06
I found that when I use: col1_names = list(unique(dt$col1)) then it works. But do not understand (yet) why list works. — luida, Apr 26 '17 at 13:52
changing to list is also problematic as it gives empty data.table when looking subtable accoring to some col1 values. FOr example this dt2[col1==new_col1[4]] — luida, Apr 26 '17 at 14:27

score 1 · Answer 1 · edited Apr 26 '17 at 14:41

1

I agree with the lookup-table answer. You might want to try:

library(data.table)

dt2 <- data.table(col1=rep(c("a","b","c","d"),each=2),
                  col2=rep(c(1,2,3,4),2),
                  col3=rep(c(1,2,3,4),2))

new_col1 <- c(a="WT",b="m1",c="m9",d="m10") # .. have to change slightly

dt2$col1<-unname(new_col1[dt2$col1])

Or with data.table "update join" syntax:

lookup = data.table(old = c("a","b","c","d"), new = c("WT","m1","m9","m10"))
dt2[lookup, on=.(col1 = old), col1 := i.new ]

edited Apr 26 '17 at 14:41

Frank

66,179
8
96
180

answered Apr 26 '17 at 13:58

r.user.05apr

5,356
3
22
39

I ended up using akrun suggestion as it is more universal. The same vector "new_col1" can be used then with different data.tables were col1 values could be different. But thanks anyway – luida Apr 26 '17 at 14:30

R: Conditionally replace values in data.table column over loop

1 Answers1