Replace values in a data.table based on row values in another table

Question

I have two data.tables:

left_table <- data.table(a = c(1,2,3,4), b = c(4,5,6,7), c = c(8,9,10,11))

right_table <- data.table(record = sample(LETTERS, 9))

I would like to replace the numeric entries in left_table by the values associated with the corresponding row numbers in right_table. e.g. All instances of 4 in left_table are replaced by whatever letter (or set of characters in my real data) is on row 4 of right_table and so on.

I have this solution but I feel it's a bit cumbersome and a simpler solution must be possible?

right_table <- data.table(row_n = as.character(seq_along(1:9)), right_table)

for (i in seq_along(left_table)){
  cols <- colnames(left_table)
  current_col <- cols[i]

  # convert numbers to character to allow := to work for matching records
  left_table[,(current_col) := lapply(.SD, as.character), .SDcols = current_col]

  #right_table[,(current_col) := lapply(.SD, as.character), .SDcols = current_col]

  #set key for quick joins

  setkeyv(left_table, current_col)
  setkeyv(right_table, "row_n")

  # replace matching records
  left_table[right_table, (current_col) := record]
}

Fino · Accepted Answer · 2020-01-15 14:35:05Z

1

You can create the new columns fetching the letters from right_table using the original variables.

left_table[, c("newa","newb","newc") := 
             .(right_table[a,record],right_table[b,record],right_table[c,record])]

#    a b  c newa newb newc
# 1: 1 4  8    Y    A    R
# 2: 2 5  9    D    B    W
# 3: 3 6 10    G    K <NA>
# 4: 4 7 11    A    N <NA>

Edit:

To make it more generic:

columnNames <- names(left_table)
left_table[, (columnNames) := 
             lapply(columnNames, function(x) right_table[left_table[,get(x)],record])]

Although there is probably a better way to do this without needing to call left_table inside lapply()

edited Jan 15, 2020 at 14:35

answered Jan 15, 2020 at 14:15

Fino

1,78413 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris Over a year ago

Is there a way of making this scalable? For instance, if the data have have n columns rather than 3?

denis · Accepted Answer · 2020-01-15 14:41:23Z

0

Using mapvalue from plyr:

library(plyr)

corresp <- function(x) mapvalues(x,seq(right_table$record),right_table$record)
left_table[,c(names(left_table)) := lapply(.SD,corresp),.SDcols = names(left_table)]

   a b  c
1: N K  X
2: U Q  V
3: Z I 10
4: K G 11

edited Jan 15, 2020 at 14:41

answered Jan 15, 2020 at 14:35

denis

5,7311 gold badge16 silver badges45 bronze badges

Comments

jazzurro · Accepted Answer · 2020-01-16 03:43:59Z

Here is my attempt. When we replace the numeric values to character values, we get NAs as we see from some other answers. So I decided to take another way. First, I created a vector using unlist(). Then, I used fifelse() from the data.table package. I used foo as indices and replaces numbers in foo with characters. I also converted numeric to character (i.e., 10 and 11 in the sample data). Then, I created a matrix and converted it to a data.table object. Finally, I assigned column names to the object.

library(data.table)

foo <- unlist(left_table)

temp <- fifelse(test = foo <= nrow(right_table),
                yes = right_table$record[foo],
                no = as.character(foo))

res <- as.data.table(matrix(data = temp, nrow = nrow(left_table)))

setnames(res, names(left_table))

#   a b  c
#1: B G  J
#2: Y D  I
#3: P T 10
#4: G S 11

chinsoon12 · Accepted Answer · 2020-01-16 05:56:57Z

0

I think it might be easier to just keep record as a vector and access it via indexing:

left_table <- data.table(a = c(1,2,3,4), b = c(4,5,6,7), c = c(8,9,10,11))
#   a b  c
#1: 1 4  8
#2: 2 5  9
#3: 3 6 10
#4: 4 7 11

set.seed(0L)
right_table <- data.table(record = sample(LETTERS, 9))
record <- right_table$record
#[1] "N" "Y" "D" "G" "A" "B" "K" "Z" "R"

left_table[, names(left_table) := lapply(.SD, function(k) fcoalesce(record[k], as.character(k)))]
left_table
#    a b  c
# 1: N G  Z
# 2: Y A  R
# 3: D B 10
# 4: G K 11

edited Jan 16, 2020 at 5:56

answered Jan 16, 2020 at 0:38

chinsoon12

25.2k4 gold badges27 silver badges35 bronze badges

2 Comments

jazzurro Over a year ago

I like the idea. Just one thing. right_table does not have 11 elements. It has only 9 in this question.

chinsoon12 Over a year ago

@jazzurro I added a fcoalesce but not in front of computer to test now

Collectives™ on Stack Overflow

Replace values in a data.table based on row values in another table

4 Answers 4

1 Comment

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related