How to merge both dataframes by fixed columns

Question

I got a problem when trying to merge two dataframes in R and I need your help. Suppose that I have following dataframes:

> Data_A
Code    year    score   
A       1991    1   
A       1992    2   
A       1993    3   
B       1991    3   
B       1993    7   
> Data_B
Code    year    l.score 
A       1991    NA  
A       1992    1   
A       1993    2   
A       1994    3   
B       1991    NA  
B       1992    3
B       1993    NA  
B       1994    7

And the desire result after merging them should be like that:

> Data_merge
    Code    year    score   l.score
    A      1991     1       NA
    A      1992     2       1
    A      1993     3       2
    B      1991     3       NA
    B      1993     7       NA

It means that when merging these dataframes, share columns in one will be kept (in this case, "Code" and "year" of Data_A). I tried merge(Data_A, Data_B, all = FALSE) but not success. Someone have any idea? Thanks for reading!

`merge(Data_A, Data_B)` works for me...Note tha `all = FALSE` is the default so no need to specify it unless it is `TRUE` — Sotos, Dec 03 '19 at 09:27
In reality, Data_A has 8291 observations and Data_B has 8299 observations. I used `Data_C<-merge(Data_A, Data_B)` and Data_C has 8809 observations. But, as I mention above, it should have 8291 observations (= Data_A). — Vĩnh Vũ Quang, Dec 03 '19 at 09:33
@sindri_baldur I tried them, but it does not work. I do not know why. Thanks. — Vĩnh Vũ Quang, Dec 03 '19 at 09:44
I thought I just did :) Look for duplicate entries of `Code` and `Year`. Something like `duplicated(Data_A[,1:2])`...Same with `Data_B` — Sotos, Dec 03 '19 at 09:51
I still confuse it, but will try your suggest. Thanks @Sotos — Vĩnh Vũ Quang, Dec 03 '19 at 10:03

AlexB · Answer 1 · 2019-12-03T09:31:34.253

0

library(dplyr)
    Data_A %>%
    left_join(Data_B, by = c('Code', 'year'))

  Code year score l.score
1    A 1991     1      NA
2    A 1992     2       1
3    A 1993     3       2
4    B 1991     3      NA
5    B 1993     7      NA

edited Dec 03 '19 at 09:31

answered Dec 03 '19 at 09:25

AlexB

3,061
2
17
19

You mean `c("Code","year")`, right? I tried your code but the result is the same with `merge()`. – Vĩnh Vũ Quang Dec 03 '19 at 09:38
It should work with `c('Code', 'year')` as well. It returns the output you stated in the question. – AlexB Dec 03 '19 at 10:45
Thanks @AlexB. I found the problem. It comes from my dataset. :) – Vĩnh Vũ Quang Dec 09 '19 at 01:15

score 0 · Answer 2 · edited Dec 03 '19 at 10:02

0

It seems your own solution can work (I am using R 3.6.1), but no idea why you cannot

> merge(Data_A, Data_B, all = FALSE)
  Code year score X1.score
1    A 1991     1       NA
2    A 1992     2        1
3    A 1993     3        2
4    B 1991     3       NA
5    B 1993     7       NA

DATA

Data_A <- structure(list(Code = structure(c(1L, 1L, 1L, 2L, 2L), .Label = c("A", 
"B"), class = "factor"), year = c(1991L, 1992L, 1993L, 1991L, 
1993L), score = c(1L, 2L, 3L, 3L, 7L)), class = "data.frame", row.names = c(NA, 
-5L))

Data_B <- structure(list(Code = structure(c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 
2L), .Label = c("A", "B"), class = "factor"), year = c(1991L, 
1992L, 1993L, 1994L, 1991L, 1992L, 1993L, 1994L), X1.score = c(NA, 
1L, 2L, 3L, NA, 3L, NA, 7L)), class = "data.frame", row.names = c(NA, 
-8L))

edited Dec 03 '19 at 10:02

Sotos

51,121
6
32
66

answered Dec 03 '19 at 09:37

ThomasIsCoding

96,636
9
24
81

I am also using R 3.6.1 but it does not work with me :(. Anyway, thank you! – Vĩnh Vũ Quang Dec 03 '19 at 09:42
@VĩnhVũQuang I am not sure if the data frames `Data_A` and `Data_B` are the same as yours ( see the `DATA` part). Maybe you can have a look at it, e.g., trying my `dput()` DATA to your code to see if it works – ThomasIsCoding Dec 03 '19 at 09:46
I also think that my data have problem. I will try again. Thanks! – Vĩnh Vũ Quang Dec 03 '19 at 09:53

How to merge both dataframes by fixed columns

2 Answers2