Combine only different rows in r

Question

I have two data.frames similar to these:

#Dt1
Id  Date Weight  
1    2     10
2    3     20
3    4     30
4    4     30
5    6     40

and

#DT2
Id  Date Weight late 
1    2     10     3
2    3     20     4
3    4     30     5
8    5     10     6

I would like to merge these files considering only the different ID between them like this:

#Dt.final
Id  Date Weight late
4    4     30    NA
5    6     40    NA
8    5     10     6

My originals files are bigger than these, thanks.

You're going to run into problems with the differing number of columns. — DarrenRhodes, May 03 '19 at 15:31
Possible duplicate of [How to join (merge) data frames (inner, outer, left, right)](https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right) — camille, May 03 '19 at 16:32

score 4 · Answer 1 · answered May 03 '19 at 16:12

besides @yarnabrina answer, The anti_join in dplyr is also what you need, but we have to apply twice. anti_join(x, y) drops all obs in x that have a match in y:

> full_join(anti_join(df1, df2, by = 'Id'), anti_join(df2, df1, by = 'Id'))
Joining, by = c("Id", "Date", "Weight")
  Id Date Weight late
1  4    4     30   NA
2  5    6     40   NA
3  8    5     10    6

score 3 · Accepted Answer · answered May 03 '19 at 15:43

Are you looking for something like this?

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

df1 <- data.frame(Id = c(1, 2, 3, 4, 5),
                  Date = c(2, 3, 4, 4, 6),
                  Weight = c(10, 20, 30, 30, 40))

df2 <- data.frame(Id = c(1, 2, 3, 8),
                  Date = c(2, 3, 4, 5),
                  Weight = c(10, 20, 30, 10),
                  late = c(3, 4, 5, 6))

full_join(x = filter(.data = df1, Id %in% setdiff(x = df1$Id, y = df2$Id)),
          y = filter(.data = df2, Id %in% setdiff(x = df2$Id, y = df1$Id)))
#> Joining, by = c("Id", "Date", "Weight")
#>   Id Date Weight late
#> 1  4    4     30   NA
#> 2  5    6     40   NA
#> 3  8    5     10    6

^{Created on 2019-05-03 by the reprex package (v0.2.1)}

Rémi Coulaud · Answer 3 · 2019-05-03T16:00:35.920

2

maybe this can solve your problem, it is an hand made answer but I hope not too bad :

df_1 <- data.frame(ID = factor(1:5, levels=1:8),
                   Date = c(2, 3, 4, 4, 6),
                   Weight = c(10, 20, 20, 30, 40))

df_2 <- data.frame(ID = factor(4:8, levels=1:8),
                   Date = c(2, 3, 4, 4, 6),
                   Weight = c(10, 20, 20, 30, 40),
                   late = c(1, 2, 3, 4, 5))

# Temporary dataframe
df_temp <- data.frame(
  df_1[!df_1$ID %in% df_2$ID, ],
  late = NA)

df.final <- rbind(
  df_temp,
  df_2[!df_2$ID %in% df_1$ID, ])

edited May 03 '19 at 16:00

answered May 03 '19 at 15:39

Rémi Coulaud

1,684
1
8
19

1

It doesn't generate the output OP wants. – yarnabrina May 03 '19 at 15:48
1

Ok, it is not exactly the same input. Thanks for your answer using dplyr. – Rémi Coulaud May 03 '19 at 16:03

Combine only different rows in r

3 Answers3