2

I have two data frames, link and body:

link is like this:

wpt        ID
1          1235
mediate    4562
mediate    0928
2          6351
3          3826
mediate    0835

body is like this:

wpt   fuel    distance
1     2221    53927
2     4821    48261
3     8362    47151

The output i expected is like this:

wpt      fuel   distance   ID
1        2221   53927      1235
mediate  NA     NA         4562
mediate  NA     NA         0928
2        4821   48261      6351
3        8362   47151      3826
mediate  NA     NA         0835

I tried using "merge" function, did not work out. Suppose using row number of "mediate" as index to split "body" and rbind them piece by piece might work. Is there a better nice way? See someone could help here?

Thanks in advance!

Demo
  • 291
  • 1
  • 5
  • 16
  • 1
    If `merge` doesn't work, I would try it again because it seems a typical `merge` problem to me. – Psidom Jun 28 '16 at 02:27
  • 1
    `merge(link, body, all = TRUE)` – alistaire Jun 28 '16 at 02:33
  • I don't think so, i want the rows with "mediate" in the exact same position. While merge would automatically put them together. – Demo Jun 28 '16 at 02:36
  • If you want to keep order, `merge(link, body, all = TRUE)[order(link$wpt),]`. `merge` has a `sort` argument you can set to `FALSE`, but it doesn't seem to obey it here. – alistaire Jun 28 '16 at 03:04

4 Answers4

3
df1 <- data.frame(wpt = c(1, "meditate", "meditate", 2,3,"meditate"), 
              ID = c(1235, 4562, 0928,6351,3826,0835))
df1$wpt <- as.character(df1$wpt)


df2 <- data.frame(wpt = c(1,2,3), 
              fuel = c(1235, 4562, 0928), 
              distance = c(2,3,4))
df2$wpt <- as.character(df2$wpt)


library(dplyr)
full_join(df1, df2, by = "wpt")

Don't mind the values! You can always rearrange the columns. 

       wpt   ID fuel distance
1        1 1235 1235        2
2 meditate 4562   NA       NA
3 meditate  928   NA       NA
4        2 6351 4562        3
5        3 3826  928        4
6 meditate  835   NA       NA
Sumedh
  • 4,835
  • 2
  • 17
  • 32
  • Kind reminder that I want the rows with "mediate" in the exact position as i indicated in the question. Or this could be done with just merge function. – Demo Jun 28 '16 at 02:39
  • Please check my edit. The meditate values are in the position as you want them. – Sumedh Jun 28 '16 at 02:43
3

I think the following should work :

library(data.table)
setkey(link,wpt)
setkey(body,wpt)
merge(link,body,by="wpt",all.x=T)
thelatemail
  • 91,185
  • 12
  • 128
  • 188
Ram K
  • 1,746
  • 2
  • 14
  • 23
3

Here's a non-merge base R solution built around match():

link[names(body)[-1L]] <- body[match(link[,1L],body[,1L]),-1L];
link;
##       wpt   ID fuel distance
## 1       1 1235 2221    53927
## 2 mediate 4562   NA       NA
## 3 mediate 0928   NA       NA
## 4       2 6351 4821    48261
## 5       3 3826 8362    47151
## 6 mediate 0835   NA       NA

Data

link <- data.frame(wpt=c('1','mediate','mediate','2','3','mediate'),ID=c('1235','4562','0928'
,'6351','3826','0835'),stringsAsFactors=F);
body <- data.frame(wpt=c(1L,2L,3L),fuel=c(2221L,4821L,8362L),distance=c(53927L,48261L,47151L)
);
bgoldst
  • 34,190
  • 6
  • 38
  • 64
1

We can use left_join

library(dplyr)
mutate(df2, wpt = as.character(wpt)) %>% 
                  left_join(df1, ., by = 'wpt')
#      wpt   ID fuel distance
#1       1 1235 2221    53927
#2 mediate 4562   NA       NA
#3 mediate  928   NA       NA
#4       2 6351 4821    48261
#5       3 3826 8362    47151
#6 mediate  835   NA       NA

Or using data.table

library(data.table)
setDT(df2)[, wpt := as.character(wpt)][df1, on = "wpt"]
#       wpt fuel distance   ID
#1:       1 2221    53927 1235
#2: mediate   NA       NA 4562
#3: mediate   NA       NA  928
#4:       2 4821    48261 6351
#5:       3 8362    47151 3826
#6: mediate   NA       NA  835
akrun
  • 874,273
  • 37
  • 540
  • 662