instead of NA, is it possible to get left_join / right_join to fill rows that were not joined with something else?

Question

> data.frame(A=1:10, B=1:10) |> dplyr::left_join(data.frame(A=1:5, C=1:5, D=1:5))
Joining, by = "A"
    A  B  C  D
1   1  1  1  1
2   2  2  2  2
3   3  3  3  3
4   4  4  4  4
5   5  5  5  5
6   6  6 NA NA
7   7  7 NA NA
8   8  8 NA NA
9   9  9 NA NA
10 10 10 NA NA

Instead of the NA, can I tell left_join to fill the cells that were not joinable with something else?

that does not work when there are NA that were present NOT due to the join. And this is precisely why this functionality is needed. If NA were already present, then the 2 types of NA become conflated. — Bear Bile Farming is Torture, Oct 05 '21 at 19:05

score 1 · Answer 1 · answered Oct 05 '21 at 18:55

We could write a function: Learned here: R Left Outer Join with 0 Fill Instead of NA While Preserving Valid NA's in Left Table

library(dplyr)
library(tidyr) 

left_join_999 <- function(x, y, fill = 999, ...){
  z <- left_join(x, y, ...)
  new_cols <- setdiff(names(z), names(x))
  z <- replace_na(z, setNames(as.list(rep(fill, length(new_cols))), new_cols))
  z
}

left_join_999(df1, df2, by="A")

output:

  A  B   C   D
1   1  1   1   1
2   2  2   2   2
3   3  3   3   3
4   4  4   4   4
5   5  5   5   5
6   6  6 999 999
7   7  7 999 999
8   8  8 999 999
9   9  9 999 999
10 10 10 999 999

instead of NA, is it possible to get left_join / right_join to fill rows that were not joined with something else?

1 Answers1