creating new column based on values from 2 other data frames

Question

I want to create a df that combines elements from 2 columns in other dfs.

Below is df1:

id visit v1
1   4    25
1   5    23
2   1    8

Below is df2:

id visit v2
1   3    11
1   4    5
2   1    9

I want:

id visit v1  v2
1   3    NA  11
1   4    25   5
1   5    23  NA
2   1    8    9

I tried this

df3 <- bind_rows(df1, df2) %>%
 group_by(id, visit) %>%
 distinct() %>%
 arrange(id, visit)

But it's not doing an exact merge on id and visit. See snipbit below where participant 1 and visit 4 is not merging.

id visit v1  v2
1   3    NA  11
1   4    25   NA
1   4    NA   5

Can someone please help?

WIth your new dataset `full_join(df1, df2)` is working for me — akrun, Sep 28 '20 at 23:43
Thank you, @akrun. I just updated my question. I'm having the issue that I pointed out in the last part of my comment where it isn't merging by id and visit correctly. — D. Fowler, Sep 28 '20 at 23:44
Please check the `full_join` which is working with the new example — akrun, Sep 28 '20 at 23:44

score 0 · Accepted Answer · answered Sep 28 '20 at 23:45

0

We can use full_join

library(dplyr)
full_join(df1, df2)

answered Sep 28 '20 at 23:45

akrun

874,273
37
540
662

score 0 · Answer 2 · answered Sep 29 '20 at 00:08

Using base syntax:

df1 <- data.frame(cbind(c(1,1,2,3,3), c(4,5,1,1,2)))
names(df1) <- c("id", "visit")

df2 <- data.frame(cbind(c(1,1,2,3,3), c(3,4,1,3,4)))
names(df2) <- c("id", "visit")

merge(x = df1, y = df2, by = c("id", "visit"), all = TRUE)

Great post on joins here

creating new column based on values from 2 other data frames

2 Answers2