Merge and match data frames of unequal length

Question

I have DF1 and DF2.

DF1
A    B
rs1  1
rs2  2
rs3  3

DF2
a    b
rs1  4
rs2  5
rs3  6
rs1  7
rs2  8
rs3  9

I would like to get such output

DF3
a_m    b   B_m
rs1    4   1
rs2    5   2
rs3    6   3
rs1    7   1
rs2    8   2
rs3    9   3

I was trying merge with all=T (as was suggested in previous posts) but it does not make a job.

`merge` isn't working for you with its defaults because you have different column names, `A` in DF1, `a` in DF2. Either do `merge(DF1, DF2, all = T, by.x = "A", by.y = "a")` or change the names so that the columns you want to merge on have the same name and then `merge(DF1, DF2, all = T)` will work. — Gregor Thomas, May 27 '16 at 19:14
Thank you, i have tried to merge two data frames, it returns not what im expecting — HoHoHo, May 27 '16 at 19:17
Also, in this case, `all = T` isn't necessary. The default will work just fine. — Gregor Thomas, May 27 '16 at 19:18
See [How to make a reproducible example](http://stackoverflow.com/q/5963269/903061) to learn how to accurately share your data (use `dput(DF1)`!) — Gregor Thomas, May 27 '16 at 19:19
you could just add it as new column to `DF2` : `DF2$c <- DF1$B[match(DF1$A,DF2$a)]` — mtoto, May 27 '16 at 19:22

Psidom · Answer 1 · 2016-05-27T20:04:31.897

0

You can try this:

DF1
    A B
1 rs1 1
2 rs2 2
3 rs3 3
DF2
    a b
1 rs1 4
2 rs2 5
3 rs3 6
4 rs1 7
5 rs2 8
6 rs3 9
DF3 <- merge(DF1, DF2, by.x = "A", by.y = "a", all = T)
DF3
    A B b
1 rs1 1 4
2 rs1 1 7
3 rs2 2 5
4 rs2 2 8
5 rs3 3 6
6 rs3 3 9

edited May 27 '16 at 20:04

answered May 27 '16 at 19:10

Psidom

209,562
33
339
356

It returns unexpected columns filled by only first row – HoHoHo May 27 '16 at 19:14
@HoHoHo not with the data you provided it doesn't. Share your data with `dput()` if you've got weird classes or other things you're not telling us about. – Gregor Thomas May 27 '16 at 19:18
Check the updated answer. Is that something you want? – Psidom May 27 '16 at 19:18
I would not like to change the order of column A in output data frame. Is it possible? – HoHoHo May 27 '16 at 19:19
So you want to keep the order of column A as in DF2? – Psidom May 27 '16 at 19:22
Yes, i would like to keep order of this column while creating merged data frame, if possible – HoHoHo May 27 '16 at 19:23
Check the updated answer, it is not a general solution. In general, if you want to keep the order of DF2, create an `id` variable and sort by the `id` after the merge. – Psidom May 27 '16 at 19:29
Something like: `DF2$id <- seq_len(dim(DF2)[1]); DF3 <- merge(DF1, DF2, by.x = "A", by.y = "a"); DF3 <- DF3[order(DF3$id), ]` – Psidom May 27 '16 at 19:31
Thank you for help, i found that sort=F works for me, instead of all=T – HoHoHo May 27 '16 at 19:59

akrun · Answer 2 · 2016-05-27T20:28:16.720

0

We can use a full_join from dplyr

library(dplyr)
full_join(DF2,DF1, by = c("a" = "A")) %>%
               setNames(., c("a_m", "b", "B_m"))
#  a_m b B_m
#1 rs1 4   1
#2 rs2 5   2
#3 rs3 6   3
#4 rs1 7   1
#5 rs2 8   2
#6 rs3 9   3

edited May 27 '16 at 20:28

answered May 27 '16 at 20:23

akrun

874,273
37
540
662

Thank you, but then we loose order of first column – HoHoHo May 27 '16 at 20:25

Merge and match data frames of unequal length

2 Answers2