代码之家 › 专栏 › 技术社区 › Cenoc

根据列从不同的数据帧进行查找

-2

Cenoc · 技术社区 · 9 年前

假设我有以下数据帧:

d1 <- data.frame(index = c(1,2,3,4), location = c('barn', 'house', 'restaurant', 'tomb'), random = c(5,3,2,1), different_col1 = c(66,33,22,11))
d2 <- data.frame(index = c(1,2,3,4), location = c('server', 'computer', 'home', 'dictionary'), random = c(1,7,2,9), differen_col2 = c('hi', 'there', 'different', 'column'))

我想做的是根据索引和它是什么数据帧来获取位置。所以我有以下几点:

data <- data.frame(src = c('one', 'one', 'two', 'one', 'two'), index = c(1,4,2,3,2))

哪里 src 指示数据应该来自哪个数据帧,以及 index ,中的值 指数 来自 指数 柱

src  |  index
-------------
one  |    1
one  |    4
two  |    2
one  |    3
two  |    2

我希望它成为:

src  | index | location
-----------------------
one  |   1   | barn
one  |   4   | tomb
two  |   2   | computer
one  |   3   | restaurant
two  |   2   | computer

由于我的数据量很大,我希望避免 merge 或类似联接( sqldf 等)。

4 回复 | 直到 9 年前

Arun 9 年前

下面是添加新列的一种方法 通过引用 使用 data.table :

require(data.table)
setDT(d1); setDT(d2); setDT(data) # convert all data.frames to data.tables

data[src == "one", location := d1[.SD, location, on="index"]]
data[src == "two", location := d2[.SD, location, on="index"]]

.SD 代表 数据子集 ,并包含中的所有列 data 符合中提供的条件 i -参数。

请参阅 vignettes 了解更多信息。

你可以使用 match 在右边的表达式中 := 而不是提取 location 使用 join 。但如果您想在多个列上进行匹配,它将不可扩展。

user5249203 9 年前

library(dplyr)
mutate(data,
       location = ifelse(src == "one", 
                         as.character(d1[index, "location"]),
                         as.character(d2[index, "location"])))

输出

  src index   location
1 one     1       barn
2 one     4       tomb
3 two     2   computer
4 one     3 restaurant
5 two     2   computer

Hack-R 9 年前

data.table 将帮助您更有效地处理大数据。

你可以使用 match 或特殊数据。表实现 merge 正如我们在评论中讨论的那样,这比合并我的原始解决方案要快得多。

下面是一个示例:

require(data.table)
d1 <- data.frame(index = c(1,2,3,4), location = c('barn', 'house', 'restaurant', 'tomb'), random = c(5,3,2,1), different_col1 = c(66,33,22,11))
d2 <- data.frame(index = c(1,2,3,4), location = c('server', 'computer', 'home', 'dictionary'), random = c(1,7,2,9), differen_col2 = c('hi', 'there', 'different', 'column'))

mydata <- data.table(src = c('one', 'one', 'two', 'one', 'two'), index = c(1,4,2,3,2))

mydata.d1       <- mydata[mydata$src == "one",]
mydata.d2       <- mydata[mydata$src == "two",]

mydata.d1 <- merge(mydata.d1, d1, all.x = T, by = "index")
mydata.d2 <- merge(mydata.d2, d2, all.x = T, by = "index")

# If you want to keep the 'different column' values from d1 and d2:
mydata <- rbind(mydata.d1, mydata.d2, fill = T)
mydata

   index src   location random different_col1 differen_col2
1:     1 one       barn      5             66            NA
2:     3 one restaurant      2             22            NA
3:     4 one       tomb      1             11            NA
4:     2 two   computer      7             NA         there
5:     2 two   computer      7             NA         there

# If you don't want to keep those 'different column' values:
mydata <- rbind(mydata.d1[,.(index, src, location)], mydata.d2[,.(index, src, location)])

mydata

   index src   location
1:     1 one       barn
2:     3 one restaurant
3:     4 one       tomb
4:     2 two   computer
5:     2 two   computer

-1

IRTFM 9 年前

基本解决方案:使用字符索引选择正确的数据帧,然后使用 mapply 处理多个“并行参数”的提交。

 dput(dat)
structure(list(src = c("one", "one", "two", "one", "two"), X. = c("|", 
"|", "|", "|", "|"), index = c(1L, 4L, 2L, 3L, 2L), location = structure(c(1L, 
4L, 5L, 3L, 5L), .Label = c("barn", "house", "restaurant", "tomb", 
"computer", "dictionary", "home", "server"), class = "factor")), .Names = c("src", 
"X.", "index", "location"), row.names = c(NA, -5L), class = "data.frame")

可能需要使用stringsAsFactor来确保字符参数。

dat$location <- mapply(function(whichd,i) dlist[[whichd]][i,'location'], whichd=dat$src, i=dat$index)

> dat
  src X. index   location
1 one  |     1       barn
2 one  |     4       tomb
3 two  |     2   computer
4 one  |     3 restaurant
5 two  |     2   computer
>