CODEWITHSUNDEEP
r
pang2016
pang2016

Reputation: 539

R two table merge

I have two data.frame df1 and df2 .

df1=data.frame(id=c(1,2,2),var1=c(3,5,5),var3=c(2,3,4))
df2=data.frame(id=c(1,1,2,2),var1=c('NONE','NONE','NONE','NONE'),var3=c(2,4,6,5))

now I want to merge to one data.frame. First, I should change the df2$var1. re encoding the df2$var1 with df1$var1 when df2$id match with df1$id. For example, df1$id=1 df1$var1=3 then df2$id=1 and df2$var1=3, so the result should like this:

df1=data.frame(id=c(1,2,2),var1=c(3,5,5),var3=c(2,3,4)).
df2=data.frame(id=c(1,1,2,2),var1=c(3,3,5,5),var3=c(2,4,6,5))

secondly, I want to merge two data.frame and delete the same one.the result should like this:

df=data.frame(id=c(1,1,2,2,2,2),var1=c(3,3,5,5,5,5),var2=c(2,4,3,4,6,5))

Sorry, it's my first to use stackoverflow. And most importantly,English isn't my native language.

Upvotes: 0

Views: 93

Answers (3)

Gauss.Y
Gauss.Y

Reputation: 100

library(dplyr)
union_all(df1, df2) %>%
  distinct() %>%
  arrange(id, var1)

  id var1 var3
1  1    3    2
2  1    3    4
3  2    5    3
4  2    5    4
5  2    5    6
6  2    5    5

First,I use dplyr::union,then I found that the order is disrupted. So,finally I use union_all, then rank it

Upvotes: 2

HubertL
HubertL

Reputation: 19544

with merge:

df2$var1 <- df1[df2$id,'var1']; 
df2
  id var1 var3
1  1    3    2
2  1    3    4
3  2    5    6
4  2    5    5

df <- merge(df1, df2, by='id')[-2:-3]
df
  id var1.y var3.y
1  1      3      2
2  1      3      4
3  2      5      6
4  2      5      5
5  2      5      6
6  2      5      5

Upvotes: 0

Hack-R
Hack-R

Reputation: 23231

I think this is what you want. 

library(sqldf)
sqldf("select b.id, a.var1, b.var3 from df1 a left join df2 b on a.id = b.id")

  id var1 var3
1  1    3    2
2  1    3    4
3  2    5    5
4  2    5    6
5  2    5    5
6  2    5    6

This is the same as the example you gave of your desired result, except for the 3rd column of the 3rd and 4th row. I believe that is due to a typo in your example, however if I am mistaken about this please let me know (and just explain why those values would be different and I will update my answer accordingly).

By the way, there are multiple ways to do this, but I find this one to be quick and easy.

Upvotes: 1

Related Questions