R: add rows based on matching condition from another dataframe

user_15460436 0
Viewed 982

I have two dataframes which look like the following:

a<-c(1,1,-1,1,-1,1)
b<-c(0,200,0,0,0,45)
c<-c(4400,4403,4407,4408,4412,4423)
df1<-cbind(a,b,c)
df1<-as.data.frame(df1)
df1
   a   b    c
1  1   0 4400
2  1 200 4403
3 -1   0 4407
4  1   0 4408
5 -1   0 4412
6  1  45 4423

a<-c(1,1,-1,1,-1,1,-1,1,1,1,1)
b<-c(1,200,1,1,1,45,1,1,30,1,0)
c<-c(3400,3403,3407,3408,3412,3423,3436,3245,3234,3456,2345)
df2<-cbind(a,b,c)
df2<-as.data.frame(df2)
df2
    a   b    c
1   1   1 3400
2   1 200 3403
3  -1   1 3407
4   1   1 3408
5  -1   1 3412
6   1  45 3423
7  -1   1 3436
8   1   1 3245
9   1  30 3234
10  1   1 3456
11  1   1 2345

How can I add rows to df1 based on matching column values in column b? So if the value in column b is the same in both dataframes, the corresponding row from df 2 should be added to df1.

For this sample the output should look like

   a   b    c
1  1   0 4400
2  1 200 4403
3  1 200 3403
4 -1   0 4407
5  1   0 4408
6 -1   0 4412
7  1  45 4423
8  1  45 3423

All join functions from dplyr couldn't help me with that. Thanks!

dplyr r
3 Answers

You could index into df2 for the values that are in column b:

rbind(df1, df2[(df2$b %in% df1$b),])

Output:

 a   b    c
 1   0 4400
 1 200 4403
-1   0 4407
 1   0 4408
-1   0 4412
 1  45 4423
 1 200 3403
 1  45 3423
 1   0 2345
user_143489960

The sound as if you want to do a semi_join and bind it to your df1:


library(dplyr)

df1 %>% 
  bind_rows(semi_join(df2, df1, by = "b"))
#>    a   b    c
#> 1  1   0 4400
#> 2  1 200 4403
#> 3 -1   0 4407
#> 4  1   0 4408
#> 5 -1   0 4412
#> 6  1  45 4423
#> 7  1 200 3403
#> 8  1  45 3423
#> 9  1   0 2345
user_129938610

I think you can use the following solution:

library(dplyr)

df2 %>%
  filter(b %in% (df1$b %>%
           intersect(df2$b))) %>%
  bind_rows(df1) %>%
  arrange(b)

   a   b    c
1  1   0 4400
2 -1   0 4407
3  1   0 4408
4 -1   0 4412
5  1  45 3423
6  1  45 4423
7  1 200 3403
8  1 200 4403
user_143145200
Related