从另一个数据框中添加ID列匹配值 [英] Adding ID column matching values from another data frame

问题描述

我的数据帧安排如下

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"))

我有另一个数据框（df1），想添加一个ID列。 ID值应与df中的相同。所需的数据帧应如下所示。

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.

推荐答案

您应重新格式化密钥，此时，应该很容易合并以获取新的ID。

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

示例：

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

---

基本R方法可能类似于：

---

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

这篇关于从另一个数据框中添加ID列匹配值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！