Finding value in one data.frame and transfering value from other column

Question

I don't know if I will be able to explain it correctly but what I want to achieve really simple.

That's first data.frame. The important value for me is in first column "V1"

    > dput(Data1)
structure(list(V1 = c(10L, 5L, 3L, 9L, 1L, 2L, 6L, 4L, 8L, 7L
), V2 = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = "NA", class = "factor"), 
    V3 = c(18L, 17L, 13L, 20L, 15L, 12L, 16L, 11L, 14L, 19L)), .Names = c("V1", 
"V2", "V3"), row.names = c(NA, -10L), class = "data.frame")

Second data.frame:

   > dput(Data2)
structure(list(Names = c(9L, 10L, 6L, 4L, 2L, 7L, 5L, 3L, 1L, 
8L), Herat = c(30L, 29L, 21L, 25L, 24L, 22L, 28L, 27L, 23L, 26L
), Grobpel = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L), .Label = "NA", class = "factor"), Hassynch = c(19L, 12L, 
15L, 20L, 11L, 13L, 14L, 16L, 18L, 17L)), .Names = c("Names", 
"Herat", "Grobpel", "Hassynch"), row.names = c(NA, -10L), class = "data.frame"

)

The value from first data.frame can be find in 1st column and I would like to copy the value from 4 column (Hassynch) and put it in the second column in first data.frame.

How to do it in the fastest way ?

You can make-up a reproducible example: http://stackoverflow.com/a/5963610/1412059. However, I suspect you want `merge`. — Roland, Dec 05 '14 at 15:30
Okey, I will look deeper in thread which you sent me the link to find how to deal with big data sets. — Shaxi Liver, Dec 05 '14 at 15:39
It looks like I am too stupid to find the way how to make my data reproducible. It was a hard day... before weekend. Any fast suggestion ? — Shaxi Liver, Dec 05 '14 at 15:47
Do not *make* your data reproducible. Simply invent some data that is representative of your problem and present it in a way that makes import easy. — Roland, Dec 05 '14 at 15:49

score 1 · Accepted Answer · answered Dec 06 '14 at 00:49

library(dplyr)
left_join(Data1, Data2, by=c("V1"="Names"))
#    V1 V2 V3 Herat Grobpel Hassynch
# 1  10 NA 18    29      NA       12
# 2   5 NA 17    28      NA       14
# 3   3 NA 13    27      NA       16
# 4   9 NA 20    30      NA       19
# 5   1 NA 15    23      NA       18
# 6   2 NA 12    24      NA       11
# 7   6 NA 16    21      NA       15
# 8   4 NA 11    25      NA       20
# 9   8 NA 14    26      NA       17
# 10  7 NA 19    22      NA       13

# if you don't want V2 and V3, you could
left_join(Data1, Data2, by=c("V1"="Names")) %>%
  select(-V2, -V3)
#    V1 Herat Grobpel Hassynch
# 1  10    29      NA       12
# 2   5    28      NA       14
# 3   3    27      NA       16
# 4   9    30      NA       19
# 5   1    23      NA       18
# 6   2    24      NA       11
# 7   6    21      NA       15
# 8   4    25      NA       20
# 9   8    26      NA       17
# 10  7    22      NA       13

score 0 · Answer 2 · answered Dec 05 '14 at 16:26

Here's a toy example that I made some time ago to illustrate merge. left_join from dplyr is also good, and data.table almost certainly has another option.

You can subset your reference dataframe so that it contains only the key variable and value variable so that you don't end up with an unmanageable dataframe.

id<-as.numeric((1:5))
m<-c("a","a","a","","")
n<-c("","","b","b","b")
dfm<-data.frame(cbind(id,m))
head(dfm)
  id m
1  1 a
2  2 a
3  3 a
4  4  
5  5  
dfn<-data.frame(cbind(id,n))
head(dfn)
  id n
1  1  
2  2  
3  3 b
4  4 b
5  5 b

dfm$id<-as.numeric(dfm$id)
dfn$id<-as.numeric(dfn$id)

dfm<-subset(dfm,id<4)
head(dfm)
  id m
1  1 a
2  2 a
3  3 a

dfn<-subset(dfn,id!=1 & id!=2)
head(dfn)
  id n
3  3 b
4  4 b
5  5 b

df.all<-merge(dfm,dfn,by="id",all=TRUE)
head(df.all)
  id    m    n
1  1    a <NA>
2  2    a <NA>
3  3    a    b
4  4 <NA>    b
5  5 <NA>    b

df.all.m<-merge(dfm,dfn,by="id",all.x=TRUE)
head(df.al.lm)
  id m    n
1  1 a <NA>
2  2 a <NA>
3  3 a    b

df.all.n<-merge(dfm,dfn,by="id",all.y=TRUE)
head(df.all.n)
  id    m n
1  3    a b
2  4 <NA> b
3  5 <NA> b

Finding value in one data.frame and transfering value from other column

2 Answers2