Merging two datasets based on column in R

Question

I have two datasets originally from the same source, but due to categorization I found it necessary to divide these. I was wondering how to merge these datasets back based on missing values from two columns? In other words, I need all the rows (columns are identical as they are from the same source) from dataset 1 and then based on a columns indicating years and country code when rows are missing from dataset 1 extract the rows from dataset 2?

df1 <- read.table(
text =
"Year, Data,Country
1,2,US
3,2,US
5,1,US
1,3,UK
2,5,UK
4,3,UK
", sep = ",", header = TRUE)
df1

df2 <- read.table(
text =
"Year, Data,Country
1,3,US
4,5,US
5,8,US
2,9,UK
3,4,UK
", sep = ",", header = TRUE)
df2

df3 <- read.table(
text =
"Year, Data,Country
1,2,US
3,2,US
4,5,US
5,1,US
1,3,UK
2,5,UK
3,4,UK
4,3,UK
", sep = ",", header = TRUE)
df3

The df3 extracts the missing year values from df1 and df2. How would this extraction be coded?

Answer 1

You could do a full_join and select the non-NA value between Data.x and Data.y using coalesce.

library(dplyr)

full_join(df1, df2,  by = c('Country', 'Year')) %>%
   mutate(Date = coalesce(Data.x, Data.y)) %>%
   select(-Data.x, -Data.y) %>%
   arrange(Country)

#  Year Country Date
#1    1      UK    3
#2    2      UK    5
#3    4      UK    3
#4    3      UK    4
#5    1      US    2
#6    3      US    2
#7    5      US    1
#8    4      US    5

The same logic in base R :

transform(merge(df1, df2, by = c('Country', 'Year'), all = TRUE), 
          Data = ifelse(is.na(Data.x), Data.y, Data.x))[names(df1)]

Answer 2

Welcome to Stackoverflow!!! Next time please provide a sample of your data and not a link or an image or a link to an image. For example, I create sample data to test solution using read.table(). Data can be small since if a solution works on four or five rows, it will work on all rows. This is especially true if the solution is vectorised, as the one below is.

df1 <- read.table(
text =
"Year, Data
1,2
2,4
3,2
5,1
", sep = ",", header = TRUE)

df2 <- read.table(
text =
"Year, Data
1,3
2,4
4,5
5,8
", sep = ",", header = TRUE)

Next we simply extract the row that isn't in the first data frame and rbind to the first data frame. We need to specify drop = T so that the rows are binded by row number, which places the row from df2 in the fourth row of the new data frame, otherwise it will be placed at the end of the new dataframe.

new_yr <- which(!(df2$Year %in% df1$Year))

df <- rbind(df1[, , drop = T], df2[new_yr, , drop = T])

df
Year Data
1    1    2
2    2    4
3    3    2
4    5    1
5    4    5