Problems merging data frames in R

Question

I have two data frames:

x <- data.frame("coi" = c(0,1,NA,1),"v2" = c(7,8,9,NA))
y <- data.frame("c1" = c(0,-1,1), "c2" = c(0,-1,-1), "coi" = c(0,1,NA))

> x
  coi v2
1   0  7
2   1  8
3  NA  9
4   1 NA
> y
  c1 c2 coi
1  0  0   0
2 -1 -1   1
3  1 -1  NA

And I want to merge them into something like this:

> obj
  coi v2 c1 c2
1   0  7  0  0
2   1  8 -1 -1
3  NA  9  1 -1
4   1 NA -1 -1

but when i try

merge(x,y)

I'm getting this:

  coi v2 c1 c2
1   0  7  0  0
2   1  8 -1 -1
3   1 NA -1 -1
4  NA  9  1 -1

which is not bad, but is there anyway to preserve the original order in column "coi"?

Not using `merge`. According to the help page: "The rows are by default lexicographically sorted on the common columns, but for sort = FALSE are in an unspecified order. " — Edward, May 13 '20 at 14:04

Harshal Gajare · Answer 1 · 2020-05-14T01:22:08.460

5

you can use something like:

library(dplyr)
left_join(x, y, by = "coi")

you will get:

  coi v2 c1 c2
1   0  7  0  0
2   1  8 -1 -1
3  NA  9  1 -1
4   1 NA -1 -1

edited May 14 '20 at 01:22

answered May 13 '20 at 14:05

Harshal Gajare

score 4 · Answer 2 · answered May 13 '20 at 14:04

4

In this case (match only one) you can use cbind in combination with match like:

cbind(x, y[match(x$coi, y$coi),-3])
#    coi v2 c1 c2
#1     0  7  0  0
#2     1  8 -1 -1
#3    NA  9  1 -1
#2.1   1 NA -1 -1

answered May 13 '20 at 14:04

GKi

2 Answers2