CODEWITHSUNDEEP
rdataframe
korone
korone

Reputation: 125

Adding ID column matching values from another data frame

I have a data frame arranged as follows 

df <- structure(list(NAME1    =  c("AAA","CCC", "BBB", "BBB"), 
             NAME2            =  c("BBB", "AAA","DDD", "AAA"),
             ID1              =  c(1,3,2,2),
             ID2              =  c(2,1,4,1)),
        .Names                =  c("NAME1","NAME2","ID1","ID2"), 
        row.names             =  c("1", "2", "3", "4"), class =("data.frame"))

I have another data frame (df1) and would like to add an ID column. The ID values should be the same as in df. The desired data frame should look like this. 

df1 <- structure(list(NAME         =  c("AAA","BBB", "CCC", "DDD"), 
                      SIZE         =  c(0.9, 1.7, 1.4, 1.1),                     
                       ID          =  c(1,2,3,4)),
                .Names             =  c("NAME","SIZE", "ID"), 
                row.names          =  c("1", "2", "3", "4"), class =("data.frame"))

Any suggestions would be appreciated. Cheers.

Upvotes: 1

Views: 85

Answers (1)

A5C1D2H2I1M1N2O1R2T1
A5C1D2H2I1M1N2O1R2T1

Reputation: 193517

You should reformat your "key", at which point, it should be easy to merge to get the new IDs.

Example:

library(data.table)
setDT(df)
setDT(df1)
df1[, ID := NULL][] ## I assume you're starting without an ID
df1
#    NAME SIZE
# 1:  AAA  0.9
# 2:  BBB  1.7
# 3:  CCC  1.4
# 4:  DDD  1.1

idkey <- unique(melt(df, measure.vars = patterns("NAME", "ID"),
                     value.name = c("NAME", "ID")), 
                by = c("NAME", "ID"))[, c("NAME", "ID"), with = FALSE]

idkey
#    NAME ID
# 1:  AAA  1
# 2:  CCC  3
# 3:  BBB  2
# 4:  DDD  4

df1[idkey, on = "NAME"]
#    NAME SIZE ID
# 1:  AAA  0.9  1
# 2:  CCC  1.4  3
# 3:  BBB  1.7  2
# 4:  DDD  1.1  4

The base R approach might be something like:

idkey <- unique(
  data.frame(NAME = unlist(df[grep("NAME", names(df))], use.names = FALSE), 
             ID = unlist(df[grep("ID", names(df))], use.names = FALSE)))
merge(df1, idkey, by = "NAME")
#   NAME SIZE ID
# 1  AAA  0.9  1
# 2  BBB  1.7  2
# 3  CCC  1.4  3
# 4  DDD  1.1  4

Upvotes: 1

Related Questions