A merge indicator for R data.table?

user_10484383 0
Viewed 492

My question is related to this question but it was asking dplyr solution.

What I'd like to do is to perform outer join and create a indicator variable that explains the merge result, like pandas or STATA would do.

To be specific, I would like to have _merge column after full outer join operation that indicates the merge result with left_only or right_only or both as below example.

UPDATE : I've updated example

key1 = c('a','b','c','d','e')
v1 = c(1,2,3, NA, 5)
key2 = c('a','b','d','f')
v2 = c(4,5,6,7)
df1 = data.frame(key=key1,v1)
df2 = data.frame(key=key2,v2)

> df1
   key v1
1:   a  1
2:   b  2
3:   c  3
4:   d NA
5:   e  5

> df2
   key v2
1:   a  4
2:   b  5
3:   d  6
4:   f  7

# merge result I'd like to have

   key v1 v2     _merge
1:   a  1  4       both
2:   b  2  5       both
3:   c  3 NA  left_only
4:   d NA  6       both # <- not right_only, both
5:   e  5 NA  left_only
6:   f NA  7 right_only

I'm wondering if I'm missing an existing data.table feature, or is there a simple way to do this task?

data.table r
1 Answers

You can use merge.data.table with all=TRUE for a full outer join:

library(data.table)
setDT(df1)
setDT(df2)
DT <- merge(df1[, r1 := .I], df2[, r2 := .I], by="key", all=TRUE)
DT[, merge_ := "both"][
    is.na(r1), merge_ := "right_only"][
        is.na(r2), merge_ := "left_only"]

output:

   key v1 r1 v2 r2     merge_
1:   a  1  1  4  1       both
2:   b  2  2  5  2       both
3:   c  3  3 NA NA  left_only
4:   d NA NA  6  3 right_only

data:

key1 = c('a','b','c')
v1 = c(1,2,3)
key2 = c('a','b','d')
v2 = c(4,5,6)
df1 = data.frame(key=key1,v1)
df2 = data.frame(key=key2,v2)

As mentioned by Michael Chirico, with data.table_1.13.0 released on Jul 24, 2020, one can also use fcase as follows:

DT[, merge_ := fcase(
    is.na(r1), "right_only",
    is.na(r2), "left_only",
    default = "both"
)]
user_19894800
Related