Merge dataframes in R without creating new columns

[Mr.Mensch]

I am pretty new to R, maybe you can help I believe its quite simple, but somehow i cant make it work. I have two datasets A and B,which I want to merge together. The columns are called country, rate and year.

Dataset A would look like this

 structure(list(Country = c("A", "A", "B", "B", "C", "C"), Year = c(2010,2015, 2010, 2015, 2010, 2015), Rate = c(0.1, 0.1, NA, NA, NA,NA)), row.names = c(NA, -6L), class = c("tbl_df", "tbl", "data.frame"))

and Dataset B like this

     structure(list(Country = c("B", "B"), Year = c(2010, 2015), Rate = c(0.8,0.9)), row.names = c(NA, -2L), class = c("tbl_df", "tbl", "data.frame"))

I want to merge A and B by country and year, and I only want one extra column showing the rate. I tried all types of joins, but I didnt find a solution yet. I dont want the columns in the output to be rate.x and rate.y.

Thanks for your help

[FJCC]

Here are three versions of merging the two data frame. Is one of them what you want?

DF1 <- structure(list(Country = c("A", "A", "B", "B", "C", "C"), 
                      Year = c(2010,2015, 2010, 2015, 2010, 2015), 
                      Rate = c(0.1, 0.1, NA, NA, NA,NA)), 
                 row.names = c(NA, -6L), class = c("tbl_df", "tbl", "data.frame"))
DF2 <- structure(list(Country = c("B", "B"), 
                      Year = c(2010, 2015), 
                      Rate = c(0.8,0.9)), 
                 row.names = c(NA, -2L), class = c("tbl_df", "tbl", "data.frame"))
library(dplyr)
Ver1 <- left_join(DF1, DF2, by = c("Country", "Year"), suffix = c("", ".B"))
Ver1
#> # A tibble: 6 x 4
#>   Country  Year  Rate Rate.B
#>   <chr>   <dbl> <dbl>  <dbl>
#> 1 A        2010   0.1   NA  
#> 2 A        2015   0.1   NA  
#> 3 B        2010  NA      0.8
#> 4 B        2015  NA      0.9
#> 5 C        2010  NA     NA  
#> 6 C        2015  NA     NA

Ver2 <- Ver1 %>% mutate(Rate = ifelse(is.na(Rate), Rate.B, Rate))
Ver2
#> # A tibble: 6 x 4
#>   Country  Year  Rate Rate.B
#>   <chr>   <dbl> <dbl>  <dbl>
#> 1 A        2010   0.1   NA  
#> 2 A        2015   0.1   NA  
#> 3 B        2010   0.8    0.8
#> 4 B        2015   0.9    0.9
#> 5 C        2010  NA     NA  
#> 6 C        2015  NA     NA

Ver3 <- Ver2 %>% select(-Rate.B)
Ver3
#> # A tibble: 6 x 3
#>   Country  Year  Rate
#>   <chr>   <dbl> <dbl>
#> 1 A        2010   0.1
#> 2 A        2015   0.1
#> 3 B        2010   0.8
#> 4 B        2015   0.9
#> 5 C        2010  NA  
#> 6 C        2015  NA

^{Created on 2020-08-08 by the reprex package (v0.3.0)}

[jrkrideau]

@Mr.Mensch

structure(list(Country = c("B", "B"), Year = c(2010, 2015), Rate = c(0.8,0.9)), row.names = c(NA, -2L), class = c("tbl_df", "tbl", "data.frame"))

Possible not a merge but just append the data?
dat1 <- structure(list(Country = c("A", "A", "B", "B", "C", "C"), Year = c(2010,2015, 2010, 2015, 2010, 2015), Rate = c(0.1, 0.1, NA, NA, NA,NA)), row.names = c(NA, -6L), class = c("tbl_df", "tbl", "data.frame"))
dat2 <- structure(list(Country = c("B", "B"), Year = c(2010, 2015), Rate = c(0.8,0.9)), row.names = c(NA, -2L), class = c("tbl_df", "tbl", "data.frame"))

rbind(dat1, dat2)

[Mr.Mensch]

@FJCC Yes version 3 ist what i wanted. Thank you