The concept is called "coalesce" in SQL. One idea is: land both the .x and .y columns and then pick between them. Here is some (admittedly non-tidyverse) code to do this.
library("wrapr")
x <- data.frame(
ID = c(1,2,3),
S1 = c(1, NA, NA),
S2 = c(2, 2, 2)
)
y <- data.frame(
ID = c(1, 2, 3, 4),
S1 = c(1, 1, 1, 1),
S3 = c(3, 3, 3, 3)
)
final <- merge(x, y, by = "ID", all = TRUE)
final$S1 <- final$S1.x %?% final$S1.y
knitr::kable(final)
| ID |
S1.x |
S2 |
S1.y |
S3 |
S1 |
| 1 |
1 |
2 |
1 |
3 |
1 |
| 2 |
NA |
2 |
1 |
3 |
1 |
| 3 |
NA |
2 |
1 |
3 |
1 |
| 4 |
NA |
NA |
1 |
3 |
1 |
The coalescing behavior is the designed behavior of rqdatatable's "natural join".
library("rqdatatable")
#> Loading required package: rquery
x <- data.frame(
ID = c(1,2,3),
S1 = c(1, NA, NA),
S2 = c(2, 2, 2)
)
y <- data.frame(
ID = c(1, 2, 3, 4),
S1 = c(1, 1, 1, 1),
S3 = c(3, 3, 3, 3)
)
final <- natural_join(x, y,
by = "ID",
jointype = "FULL")
knitr::kable(final)
| ID |
S1 |
S2 |
S3 |
| 1 |
1 |
2 |
3 |
| 2 |
1 |
2 |
3 |
| 3 |
1 |
2 |
3 |
| 4 |
1 |
NA |
3 |