john
Reputation: 1036
Fetch data exist in another data frame
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
')
Upvotes: 0
Views: 39
Answers (1)
JdeMello
Reputation: 1718
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
Upvotes: 1
Related Questions
- Check if column data exists in column and return value of the another column in R
- Compare two dataframes and retrieve values
- R finding the rows that exist within another dataframe
- Extracting column values based on condition from another data table in R
- return values existing in other rows based on column match
- Filter data.frame with another data.frame using select(contains)
- How to compare two data.frames and return rows based on conditions?
- Finding values in data frame based on another data frame in R
- In R, select values from another dataframe based on rows and columns, which are saved in dataframe
- Fetch all the rows in a data frame matching with rows of other data frame