我想通过将具有匹配变量的列求和(而不是附加列(来将一组数据框合并到单个数据框中。
例如，给定
df1 <- data.frame(A = c(0,0,1,1,1,2,2), B = c(1,2,1,2,3,1,5), x = c(2,3,1,5,3,7,0))
df2 <- data.frame(A = c(0,1,1,2,2,2), B = c(1,1,3,2,4,5), x = c(4,8,4,1,0,3))
df3 <- data.frame(A = c(0,1,2), B = c(5,4,2), x = c(5,3,1))
我想按"A"匹配，"B"并求和"x"的值。对于此示例，我可以得到所需的结果，如下所示：
library(plyr)
library(dplyr)
# rename columns so that join_all preserves them all:
colnames(df1)[3] <- "x1"
colnames(df2)[3] <- "x2"
colnames(df3)[3] <- "x3"
# join the data frames by matching "A" and "B" values:
res <- join_all(list(df1, df2, df3), by = c("A", "B"), type = "full")
# get the sums and drop superfluous columns:
arrange(res, A, B) %>%
rowwise() %>%
mutate(x = sum(x1, x2, x3, na.rm = TRUE)) %>%
select(A, B, x)
结果：
A B x
<dbl> <dbl> <dbl>
1 0 1 6
2 0 2 3
3 0 5 5
4 1 1 9
5 1 2 5
6 1 3 7
7 1 4 3
8 2 1 7
9 2 2 2
10 2 4 0
11 2 5 3
更通用的解决方案是
library(dplyr)
# function to get the desired result for two data frames:
my_merge <- function(df1, df2)
{
m1 <- merge(df1, df2, by = c("A", "B"), all = TRUE)
m1 <- rowwise(res) %>%
mutate(x = sum(x.x, x.y, na.rm = TRUE)) %>%
select(A, B, x)
return(m1)
}
l1 <- list(df2, df3) # omit the first data frame
res <- df1 # initial value of the result
for(df in l1) res <- my_merge(res, df) # call the function repeatedly
是否有更有效的选项来组合大量数据框？理想情况下，它应该是递归的(即在计算总和之前最好不要将所有数据帧连接到一个庞大的数据帧中(。
更简单的选择是绑定数据集的行，然后按感兴趣的列分组，并通过获取"x"的sum来获得汇总的输出
library(tidyverse)
bind_rows(df1, df2, df3) %>%
group_by(A, B) %>%
summarise(x = sum(x))
# A tibble: 11 x 3
# Groups: A [?]
# A B x
# <dbl> <dbl> <dbl>
# 1 0 1 6
# 2 0 2 3
# 3 0 5 5
# 4 1 1 9
# 5 1 2 5
# 6 1 3 7
# 7 1 4 3
# 8 2 1 7
# 9 2 2 2
#10 2 4 0
#11 2 5 3
如果全局环境中有许多对象，其模式"df"后跟一些数字
mget(ls(pattern= "^df\d+")) %>%
bind_rows %>%
group_by(A, B) %>%
summarise(x = sum(x))
正如OP提到的memory约束，如果我们先做join，然后使用rowSums或+reduce，它会更有效
mget(ls(pattern= "^df\d+")) %>%
reduce(full_join, by = c("A", "B")) %>%
transmute(A, B, x = rowSums(.[3:5], na.rm = TRUE)) %>%
arrange(A, B)
# A B x
#1 0 1 6
#2 0 2 3
#3 0 5 5
#4 1 1 9
#5 1 2 5
#6 1 3 7
#7 1 4 3
#8 2 1 7
#9 2 2 2
#10 2 4 0
#11 2 5 3
这也可以通过data.table来完成
library(data.table)
rbindlist(mget(ls(pattern= "^df\d+")))[, .(x = sum(x)), by = .(A, B)]
理想情况下，它应该是递归的(即在计算总和之前最好不要将所有数据帧合并到一个庞大的数据帧中(。
如果您内存受限并且愿意牺牲速度(与@akrun的 data.table 方法相比(，请在循环中一次使用一个表：
library(data.table)
tabs = c("df1", "df2", "df3")
# enumerate all combos for the results table
# initializing sum to 0
res = CJ(A = 0:2, B = 1:5, x = 0)
# loop over tabs, adding on
for (i in seq_along(tabs)){
tab = get(tabs[[i]])
res[tab, on=.(A, B), x := x + i.x][]
rm(tab)
}
如果需要从磁盘读取表，请将tabs更改为文件名，get更改为fread或任何函数。
我怀疑您是否可以将所有表放入内存中，但也不能将它们的rbind副本放在一起。
同样(感谢@akrun的评论(，成对使用他的方法：
res = data.table(get(tabs[[1]]))[0L]
for (i in seq_along(tabs)){
tab = get(tabs[[i]])
res = rbind(res, tab)[, .(x = sum(x)), by=.(A,B)]
rm(tab)
}