Create subgroups in data.table

Question

Say i have the following simplified dataset:

dt <- data.table(id = 1:5, val = c(1, 2, 3, 2, 4))
dt2 <- data.table(id = c(2, 4), val = c(2, 3))

I want to replace all values in dt which have the value 2. The replacing value is given in dt2. The two tables can be joined via the id. The final value should remain the same if the value is unequal to 2. And if it is equal to 2 it should become paste0(dt$val, ".", dt2$val).

Desired Output:

row id val
1:  1   1
2:  2   2.2
3:  3   3
4:  4   2.3
5:  5   4

What i tried (it works but it does not seem to be elegant):

merged <- merge(x = dt, y = dt2, by= "id", all.x = TRUE)
merged[!is.na(merged$val.y), ]$val.x <- paste0(
  merged[!is.na(merged$val.y), ]$val.x, ".",
  merged[!is.na(merged$val.y), ]$val.y)
merged[, val.y := NULL]
setnames(x = merged, old = "val.x", new = "val")
merged

Question: How can I do the transformation more elegantly?

score 3 · Accepted Answer · answered Jun 28 '19 at 10:20

R u looking for an update join

dt[dt2, on=.(id), val := paste0(x.val, ".", i.val)]

output:

   id val
1:  1   1
2:  2 2.2
3:  3   3
4:  4 2.3
5:  5   4

data:

#val column needs to be of character type to suppress the warning
dt <- data.table(id = 1:5, val = as.character(c(1, 2, 3, 2, 4)))
dt2 <- data.table(id = c(2, 4), val = c(2, 3))

score 2 · Answer 2 · answered Jun 28 '19 at 10:09

library(data.table)

# example data
dt <- data.table(id = 1:5, val = c(1, 2, 3, 2, 4))
dt2 <- data.table(id = c(2, 4), val = c(2, 3))

If your datasets are both properly ordered you can use base R like this:

dt$val[dt$id %in% dt2$id] = paste0(dt$val[dt$id %in% dt2$id], ".", dt2$val)

dt

#    id val
# 1:  1   1
# 2:  2 2.2
# 3:  3   3
# 4:  4 2.3
# 5:  5   4

Otherwise you can use this:

dt_merged = merge(dt, dt2, by="id", all.x=T)[, val:=ifelse(is.na(val.y), 
                                                           val.x, 
                                                           paste0(val.x, ".", val.y))]
dt_merged = dt_merged[, c("id","val")]
dt_merged

#    id val
# 1:  1   1
# 2:  2 2.2
# 3:  3   3
# 4:  4 2.3
# 5:  5   4

Create subgroups in data.table

2 Answers2