I have two dataframes that I want to join together. df1 is the original dataframe and I created df2 earlier in my workflow where I conducted additional analysis.
I want to join only var3 using ID as the common ID, which means var4 will be excluded from the join. For the merged df3, when an ID is not present in df1 from df2, the value for var3 should be 0.
What is the best way to accomplish this?
library(dplyr)
df1 <- tibble(ID = c(1001, 1002, 1003, 1004), var1 = c(1,0,1,0), var2 = c(0,1,1,0))
df2 <- tibble(ID = c(1001, 1002), var3 = c(1,1), var4 = c(0, 0))
Table 1 (df1):
| ID | var1 | var2 |
|---|---|---|
| 1001 | 1 | 0 |
| 1002 | 0 | 1 |
| 1003 | 1 | 1 |
| 1004 | 0 | 0 |
Table 2 (df2):
| ID | var3 | var4 |
|---|---|---|
| 1001 | 1 | 0 |
| 1002 | 1 | 0 |
Joined Table (df3):
| ID | var1 | var2 | var3 |
|---|---|---|---|
| 1001 | 1 | 0 | 1 |
| 1002 | 0 | 1 | 1 |
| 1003 | 1 | 1 | 0 |
| 1004 | 0 | 0 | 0 |
>Solution :
We can use dplyr::left_join to merge df1 with a version of df2 that contains only "ID" and "var3". Then mutate the "var" columns to replace NA (missing) values with 0.
df3 <- df1 %>%
left_join(select(df2, ID, var3), by = 'ID') %>%
mutate(across(-ID, ~replace_na(., 0)))
ID var1 var2 var3
<dbl> <dbl> <dbl> <dbl>
1 1001 1 0 1
2 1002 0 1 1
3 1003 1 1 0
4 1004 0 0 0
There are several valid ways to select the "var" columns within across. Here I’ve used -ID. One could also use starts_with('var') or even everything(), though the latter assumes no NA values in "ID".