merge and replace values in two data.tables

Question

I am quite new to the data.table package and have a simple problem. I have two data.tables that are compared with the use of keys. In data.table 1 the value of column C is changed from "NO" to "OK" if the key columns A and B are equally found in data.table B. This step is inevitably and has to be done.

library(data.table)
df_1 <- data.frame(A=c(1,1,3,5,6,7), B = c("x","y","z","q","w","e"), C = rep("NO",6))
df_2 <- data.frame(A=c(3,5,1), B = c("z","q","x"), D=c(3,5,99))
keys <- c("A","B")
dt_1 <- data.table(df_1, key = keys)
dt_2 <- data.table(df_2, key = keys)
dt_1[dt_2, C := "OK"]

Now I get the data.table:

   A     B     C
1: 1     x     OK
2: 1     y     NO
3: 3     z     OK
4: 5     q     OK
5: 6     w     NO
6: 7     e     NO

I would like to include a second operation. If in data.table 2 the value of column A is not equal to column D the value of column D should be used after the first operation. Meaning column D is superior to A. This should work no matter how many values in D are different. The desired data.table looks the following:

   A     B     C
1: 99    x     OK
2: 1     y     NO
3: 3     z     OK
4: 5     q     OK
5: 6     w     NO
6: 7     e     NO

I tired something without success.

dt_1[dt_2, A != D, A := D]

Thank you for your help!

David Arenburg · Accepted Answer · 2015-09-03 10:44:21Z

5

Try:

dt_1[C == "OK", A:= dt_2[,D]]

#   A B  C
# 1: 99 x OK
# 2:  1 y NO
# 3:  3 z OK
# 4:  5 q OK
# 5:  6 w NO
# 6:  7 e NO

And here's how you should have done the whole process in the first place.

Create both data sets as data.tables in the first place (or convert in place using setDT)

dt_1 <- data.table(A=c(1,1,3,5,6,7), B = c("x","y","z","q","w","e"), C = rep("NO",6))
dt_2 <- data.table(A=c(3,5,1), B = c("z","q","x"), D=c(3,5,99))

Then key them using setkeyv instead of using the <- operator

keys <- c("A","B")
setkeyv(dt_1, keys)
setkeyv(dt_2, keys)

Then just update both column within a single join

dt_1[dt_2, `:=`(C = "OK", A = i.D)]
#     A B  C
# 1: 99 x OK
# 2:  1 y NO
# 3:  3 z OK
# 4:  5 q OK
# 5:  6 w NO
# 6:  7 e NO

In this case the condition df_1$A != df_2$D is redundant

edited Sep 3, 2015 at 10:44

David Arenburg

92.4k18 gold badges145 silver badges202 bronze badges

answered Sep 3, 2015 at 9:18

Andriy T.

2,03013 silver badges23 bronze badges

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

8 Comments

Andriy T. Over a year ago

As I understand if you change some velue in dt_2$D so the corresponding value in dt_1$A should be changed... anyway you are right, it's just a quick solution to his question. I am looking for some more general avoiding a step of the column C in dt_1

VDK Over a year ago

Thank you for the quick reply. The step to set column C to OK is inevitable. I edited the question.

Andriy T. Over a year ago

@DavidArenburg, after dt_2[2L, D := 1e3] value A is different D in row 2, so in dt_1[3,A] is changed to 1000

VDK Over a year ago

@Andriy T. so far it seems to work for me the way it is supposed to work. I will take a closer look in my actual code.

David Arenburg Over a year ago

I guess this should be just a one liner for both operation at once dt_1[dt_2, `:=`(C = "OK", A = D)]

|

Collectives™ on Stack Overflow

merge and replace values in two data.tables

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related