Fetch data exist in another data frame

0
votes

I have the following tables : DF1

var1    var2    var3    Ars_0   Ars_1   Ars_2   Ars_3   Ars_4   Ars_5   Ars_6   Ars_7
x   y1  z1  1.000   1.000   1.000   1.000   1.000   1.000   1.000   0.000
x   y2  z1  1.000   1.000   1.000   1.000   1.000   1.000   1.000   0.000
x   y1  z2  1.000   1.000   1.000   1.000   1.000   1.000   1.000   0.000
x   y2  z2  1.000   1.000   1.000   1.000   1.000   1.000   1.000   0.000
y   y1  z1  1.000   1.000   1.000   1.000   1.000   1.000   1.000   1.000
y   y2  z1  1.000   1.000   1.000   1.000   1.000   1.000   1.000   1.000
y   y1  z2  1.000   1.000   1.000   1.000   1.000   1.000   1.000   1.000
y   y2  z2  1.000   1.000   1.000   1.000   1.000   1.000   1.000   1.000
z   y1  z1  1.000   1.000   1.000   1.000   1.000   1.000   1.000   1.000
z   y2  z1  1.000   1.000   1.000   1.000   1.000   1.000   1.000   1.000
z   y1  z2  1.000   1.000   1.000   1.000   1.000   1.000   1.000   1.000
z   y2  z2  1.000   1.000   1.000   1.000   1.000   1.000   1.000   1.000

DF2

var1    var2    var3    Ars_0   Ars_1   Ars_2   Ars_3   Ars_4   Ars_5   Ars_6
x   y1  z1  0.935   0.065   0.000   0.000   0.000   0.000   0.000
x   y2  z1  1.000   0.000   0.000   0.000   0.000   0.000   0.000
x   y1  z2  0.146   0.000   0.854   0.000   0.000   0.000   0.000
x   y2  z2  0.520   0.377   0.103   0.000   0.000   0.000   0.000
y   y1  z1  0.939   0.060   0.000   0.001   0.000   0.000   0.000
y   y2  z1  0.987   0.013   0.000   0.000   0.000   0.000   0.000
y   y1  z2  0.175   0.052   0.773   0.000   0.000   0.000   0.000
y   y2  z2  0.000   0.000   1.000   0.000   0.000   0.000   0.000
z   y1  z1  0.948   0.052   0.000   0.000   0.000   0.000   0.000
z   y2  z1  0.981   0.019   0.000   0.000   0.000   0.000   0.000

I want all rows from DF2 and rows which does not exist in DF2 but exist in DF1 (based on var1, var2 and var3). If a column does not exist, the whole column should be fetched. For e.g. Ars_7 exist in DF1 but not exist in DF2 so whole could be added in the final output.

Desired Output

var1    var2    var3    Ars_0   Ars_1   Ars_2   Ars_3   Ars_4   Ars_5   Ars_6   Ars_7
x   y1  z1  0.935   0.065   0.000   0.000   0.000   0.000   0.000   0.000
x   y2  z1  1.000   0.000   0.000   0.000   0.000   0.000   0.000   0.000
x   y1  z2  0.146   0.000   0.854   0.000   0.000   0.000   0.000   0.000
x   y2  z2  0.520   0.377   0.103   0.000   0.000   0.000   0.000   0.000
y   y1  z1  0.939   0.060   0.000   0.001   0.000   0.000   0.000   1.000
y   y2  z1  0.987   0.013   0.000   0.000   0.000   0.000   0.000   1.000
y   y1  z2  0.175   0.052   0.773   0.000   0.000   0.000   0.000   1.000
y   y2  z2  0.000   0.000   1.000   0.000   0.000   0.000   0.000   1.000
z   y1  z1  0.948   0.052   0.000   0.000   0.000   0.000   0.000   1.000
z   y2  z1  0.981   0.019   0.000   0.000   0.000   0.000   0.000   1.000
z   y1  z2  1.000   1.000   1.000   1.000   1.000   1.000   1.000   1.000
z   y2  z2  1.000   1.000   1.000   1.000   1.000   1.000   1.000   1.000

R Code 

x1 <- read.table(header = TRUE, 
text='var1  var2    var3    Ars_0   Ars_1   Ars_2   Ars_3   Ars_4   Ars_5   Ars_6
x   y1  z1  0.935   0.065   0.000   0.000   0.000   0.000   0.000
x   y2  z1  1.000   0.000   0.000   0.000   0.000   0.000   0.000
x   y1  z2  0.146   0.000   0.854   0.000   0.000   0.000   0.000
x   y2  z2  0.520   0.377   0.103   0.000   0.000   0.000   0.000
y   y1  z1  0.939   0.060   0.000   0.001   0.000   0.000   0.000
y   y2  z1  0.987   0.013   0.000   0.000   0.000   0.000   0.000
y   y1  z2  0.175   0.052   0.773   0.000   0.000   0.000   0.000
y   y2  z2  0.000   0.000   1.000   0.000   0.000   0.000   0.000
z   y1  z1  0.948   0.052   0.000   0.000   0.000   0.000   0.000
z   y2  z1  0.981   0.019   0.000   0.000   0.000   0.000   0.000
')

x2 <- read.table(header = TRUE, 
                 text='var1 var2    var3    Ars_0   Ars_1   Ars_2   Ars_3   Ars_4   Ars_5   Ars_6   Ars_7
x   y1  z1  1.000   1.000   1.000   1.000   1.000   1.000   1.000   0.000
x   y2  z1  1.000   1.000   1.000   1.000   1.000   1.000   1.000   0.000
x   y1  z2  1.000   1.000   1.000   1.000   1.000   1.000   1.000   0.000
x   y2  z2  1.000   1.000   1.000   1.000   1.000   1.000   1.000   0.000
y   y1  z1  1.000   1.000   1.000   1.000   1.000   1.000   1.000   1.000
y   y2  z1  1.000   1.000   1.000   1.000   1.000   1.000   1.000   1.000
y   y1  z2  1.000   1.000   1.000   1.000   1.000   1.000   1.000   1.000
y   y2  z2  1.000   1.000   1.000   1.000   1.000   1.000   1.000   1.000
z   y1  z1  1.000   1.000   1.000   1.000   1.000   1.000   1.000   1.000
z   y2  z1  1.000   1.000   1.000   1.000   1.000   1.000   1.000   1.000
z   y1  z2  1.000   1.000   1.000   1.000   1.000   1.000   1.000   1.000
z   y2  z2  1.000   1.000   1.000   1.000   1.000   1.000   1.000   1.000
')
1
rdplyr
It'd be nice if you post the desired output. I think you want to match all rows in x2 with x1 plus whatever in x1 that is not matching in x2. That would be a full-join: merge(x1, x2, all=TRUE) – JdeMello
Thanks. I have added desired output. Full join does not return the desired output. It returns 22 rows. – john

1 Answers

1
votes

Using base R, you can left-join using merge to match x1 and x2 by the keys c("var1","var2","var3"). Then you can anti-join whatever in x2 that does not match in x1 by the same keys in the merge. You need to make sure you only select the key columns on the right-hand side of the merge plus whatever column is missing in x1 from x2:

cols <- c("var1","var2","var3") # keys

missingCol <- setdiff(names(x2), names(x1)) # column(s) missing in x1 but that exist in x2

# left-join for the first component
a <- merge(x1, x2[c(cols, missingCol)], all.x=TRUE, by = c("var1","var2","var3"))

# anti join x2 on x1, see the use of `interaction`
b <- x2[!interaction(x2[cols]) %in% interaction(x1[cols]), ]

# "stack" tables with rbind
out <- do.call(rbind, list(a, b))

Result:

> out
   var1 var2 var3 Ars_0 Ars_1 Ars_2 Ars_3 Ars_4 Ars_5 Ars_6 Ars_7
1     x   y1   z1 0.935 0.065 0.000 0.000     0     0     0     0
2     x   y1   z2 0.146 0.000 0.854 0.000     0     0     0     0
3     x   y2   z1 1.000 0.000 0.000 0.000     0     0     0     0
4     x   y2   z2 0.520 0.377 0.103 0.000     0     0     0     0
5     y   y1   z1 0.939 0.060 0.000 0.001     0     0     0     1
6     y   y1   z2 0.175 0.052 0.773 0.000     0     0     0     1
7     y   y2   z1 0.987 0.013 0.000 0.000     0     0     0     1
8     y   y2   z2 0.000 0.000 1.000 0.000     0     0     0     1
9     z   y1   z1 0.948 0.052 0.000 0.000     0     0     0     1
10    z   y2   z1 0.981 0.019 0.000 0.000     0     0     0     1
11    z   y1   z2 1.000 1.000 1.000 1.000     1     1     1     1
12    z   y2   z2 1.000 1.000 1.000 1.000     1     1     1     1

with data.table

We repeat the same process as above —that we need to piece together the left-join and the anti-join tables in a stack but with friendly syntax of data.table and its usual computational efficiency:

# data.table solution
library(data.table)

cols <- c("var1","var2","var3") # keys
missingCol <- setdiff(names(x2), names(x1)) # column(s) missing in x1 but that exist in x2


setDT(x1) # need to use `setDT` to make data.frames into data.table objects
setDT(x2)

a <- x2[, c(cols, missingCol), with=F][x1, on=cols] # left join
b <- x2[!x1, on=cols] # anti join

res3 <- rbindlist(list(a, b), use.names = TRUE) # bind rows

setcolorder(res3, names(x2)) # order columns as x2

Result:

> res3
    var1 var2 var3 Ars_0 Ars_1 Ars_2 Ars_3 Ars_4 Ars_5 Ars_6 Ars_7
 1:    x   y1   z1 0.935 0.065 0.000 0.000     0     0     0     0
 2:    x   y2   z1 1.000 0.000 0.000 0.000     0     0     0     0
 3:    x   y1   z2 0.146 0.000 0.854 0.000     0     0     0     0
 4:    x   y2   z2 0.520 0.377 0.103 0.000     0     0     0     0
 5:    y   y1   z1 0.939 0.060 0.000 0.001     0     0     0     1
 6:    y   y2   z1 0.987 0.013 0.000 0.000     0     0     0     1
 7:    y   y1   z2 0.175 0.052 0.773 0.000     0     0     0     1
 8:    y   y2   z2 0.000 0.000 1.000 0.000     0     0     0     1
 9:    z   y1   z1 0.948 0.052 0.000 0.000     0     0     0     1
10:    z   y2   z1 0.981 0.019 0.000 0.000     0     0     0     1
11:    z   y1   z2 1.000 1.000 1.000 1.000     1     1     1     1
12:    z   y2   z2 1.000 1.000 1.000 1.000     1     1     1     1