如何在聚合函数中获取所有的总和？

Question

这里是一些示例数据：

dat="x1 x2 x3 x4 x5
1   C  1 16 NA 16
2   A  1 16 16 NA
3   A  1 16 16 NA
4   A  4 64 64 NA
5   C  4 64 NA 64
6   A  1 16 16 NA
7   A  1 16 16 NA
8   A  1 16 16 NA
9   B  4 64 32 32
10  A  3 48 48 NA
11  B  4 64 32 32
12  B  3 48 32 16"

data<-read.table(text=dat,header=TRUE)   
aggregate(cbind(x2,x3,x4,x5)~x1, FUN=sum, data=data)   
 x1 x2  x3 x4 x5   
1  B 11 176 96 8   

我该如何在x1中同时得到A和C的总和？

 aggregate(.~x1, FUN=sum, data=data, na.action = na.omit)  
   x1 x2  x3 x4 x5
 1  B 11 176 96 80 

当我使用 sqldf 时：

library("sqldf")
sqldf("select sum(x2),sum(x3),sum(x4),sum(x5) from data group by x1")
  sum(x2) sum(x3) sum(x4) sum(x5)
1      12     192     192    <NA>
2      11     176      96      80
3       5      80      NA      80

为什么第一行出现了 <NA>，而第三行却是 NA？它们有什么区别？为什么我会得到 <NA>？数据中没有 <NA>！

str(data)
'data.frame':   12 obs. of  5 variables:
 $ x1: Factor w/ 3 levels "A","B","C": 3 1 1 1 3 1 1 1 2 1 ...
 $ x2: int  1 1 1 4 4 1 1 1 4 3 ...
 $ x3: int  16 16 16 64 64 16 16 16 64 48 ...
 $ x4: int  NA 16 16 64 NA 16 16 16 32 48 ...
 $ x5: int  16 NA NA NA 64 NA NA NA 32 NA ...

问题出在sqldf上，为什么sum(x4)得到了NA，而sum(x5)却得到了<NA>？

我可以证明x4和x5中所有的NA都是相同的：

data[is.na(data)] <- 0     

> data
   x1 x2 x3 x4 x5
1   C  1 16  0 16
2   A  1 16 16  0
3   A  1 16 16  0
4   A  4 64 64  0
5   C  4 64  0 64
6   A  1 16 16  0
7   A  1 16 16  0
8   A  1 16 16  0
9   B  4 64 32 32
10  A  3 48 48  0
11  B  4 64 32 32
12  B  3 48 32 16

因此，sqldf对sum(x4)和sum(x5)的处理方式不同，这是如此奇怪，以至于我认为sqldf存在逻辑混乱。它可以在其他计算机上重现。请先进行测试，然后进行讨论。

- showkey

---

也许这可以帮助你：https://dev59.com/EF_Va4cB1Zd3GeqPRkXD - lukeA

你可以使用 <NA> 来区分真正的 NA 值和字符表示的 NA，例如 "NA"。如果你查看运行该命令后的返回值，你会得到一个 data.frame，其中前三列是 integer 类型，第四列是 character 类型。我猜 sqldf 在某个地方将第四列转换为了因子。尝试使用 str(sqldf("select sum(x2),sum(x3),sum(x4),sum(x5) from data group by x1")) 查看我的意思。 - Simon O'Hanlon

SQLite根据列的第一行分配列亲和性，并在其为NULL时使用文本。一些解决方法是：（1）输出列与输入列使用相同的名称，这种情况下sqldf将推断您想要强制转换回该类型，（2）使用total代替sum，这种情况下零行将总计为0而不是NULL，因此问题不会发生，（3）使用sqldf的method参数指定类别，（4）使用sqldf支持的其他数据库之一（H2、MySQL、PostgreSQL）而不是SQLite。有关更多信息，请参见?sqldf和http://sqldf.googlecode.com。 - G. Grothendieck

Answer 1

如果您有兴趣，以下是使用data.table的方式：

require(data.table)
dt <- data.table(data)
dt[, lapply(.SD, sum, na.rm=TRUE), by=x1]
#    x1 x2  x3  x4 x5
# 1:  C  5  80   0 80
# 2:  A 12 192 192  0
# 3:  B 11 176  96 80

如果您想让sum返回NA而不是除去NA后的总和，请删除na.rm=TRUE参数。

这里的.SD是一个内部的data.table变量，默认情况下构造除x1以外的所有列。您可以通过以下方式检查.SD的内容：

dt[, print(.SD), by=x1]

想要了解什么是.SD，可以查看?data.table，里面还有其他一些内部（非常有用的）特殊变量，例如.I、.N、.GRP等等。

- Arun

Answer 2

由于aggregate函数的公式方法默认处理NA值，因此在使用sum函数的na.rm参数之前，您需要覆盖该默认设置。您可以通过将na.action设置为NULL或na.pass来实现：

aggregate(cbind(x2,x3,x4,x5) ~ x1, FUN = sum, data = data, 
          na.rm = TRUE, na.action = NULL)
#   x1 x2  x3  x4 x5
# 1  A 12 192 192  0
# 2  B 11 176  96 80
# 3  C  5  80   0 80

aggregate(cbind(x2,x3,x4,x5) ~ x1, FUN = sum, data = data, 
          na.rm = TRUE, na.action = na.pass)
#   x1 x2  x3  x4 x5
# 1  A 12 192 192  0
# 2  B 11 176  96 80
# 3  C  5  80   0 80

关于 sqldf，似乎根据第一个分组变量的第一行中的项目是否为 NA，列会被转换为不同的类型。如果它是 NA，那么该列将被转换为 character。

比较：

df1 <- data.frame(id = c(1, 1, 2, 2, 2),
                 A = c(1, 1, NA, NA, NA),
                 B = c(NA, NA, 1, 1, 1))
sqldf("select sum(A), sum(B) from df1 group by id")
#   sum(A) sum(B)
# 1      2   <NA>
# 2     NA    3.0

df2 <- data.frame(id = c(2, 2, 1, 1, 1),
                  A = c(1, 1, NA, NA, NA),
                  B = c(NA, NA, 1, 1, 1))
sqldf("select sum(A), sum(B) from df2 group by id")
#   sum(A) sum(B)
# 1   <NA>      3
# 2    2.0     NA

然而，有一个简单的解决方法：将原始名称重新分配给正在创建的新列。也许这可以让SQLite继承以前数据库中的一些信息？（我不太使用SQL。）

示例（与先前创建的“df2”相同）：

sqldf("select sum(A) `A`, sum(B) `B` from df2 group by id")
#    A  B
# 1 NA  3
# 2  2 NA

您可以轻松使用paste方法创建您的select语句：

Aggs <- paste("sum(", names(data)[-1], ") `", 
              names(data)[-1], "`", sep = "", collapse = ", ")
sqldf(paste("select", Aggs, "from data group by x1"))
#   x2  x3  x4 x5
# 1 12 192 192 NA
# 2 11 176  96 80
# 3  5  80  NA 80
str(.Last.value)
# 'data.frame':  3 obs. of  4 variables:
#  $ x2: int  12 11 5
#  $ x3: int  192 176 80
#  $ x4: int  192 96 NA
#  $ x5: int  NA 80 80

如果你想要用 0 代替 NA，可以采用类似的方法：

Aggs <- paste("sum(ifnull(", names(data)[-1], ", 0)) `", 
              names(data)[-1], "`", sep = "", collapse = ", ")
sqldf(paste("select", Aggs, "from data group by x1"))
#   x2  x3  x4 x5
# 1 12 192 192  0
# 2 11 176  96 80
# 3  5  80   0 80

- A5C1D2H2I1M1N2O1R2T1

---

亲爱的Ananda Mahto，您还没有回答为什么sqldf会以不同的方式处理NA。 - showkey

@it_is_a_literature，我没有确切的答案为什么，但我有一个解决方案。我猜这不是“sqldf”方面的问题，而更多是与SQLite有关的问题。请参见此页面上的FAQ 14 ，了解读取数据时类似的问题。我假设这里发生了类似的情况。 - A5C1D2H2I1M1N2O1R2T1

Answer 3

使用reshape包，您可以这样做：

> # x1 = identifier variable, everything else = measured variables
> data_melted <- melt(data, id="x1", measured=c("x2", "x3", "x4", "x5"))
>
> # Thus we now have (measured variable and it's value) per x1 (id variable)
> head(data_melted)
  x1 variable value
1  C       x2     1
2  A       x2     1
3  A       x2     1
4  A       x2     4
5  C       x2     4
6  A       x2     1

> tail(data_melted)
   x1 variable value
43  A       x5    NA
44  A       x5    NA
45  B       x5    32
46  A       x5    NA
47  B       x5    32
48  B       x5    16

> # Now aggregate using sum, passing na.rm to it
> cast(data_melted, x1 ~ ..., sum, na.rm=TRUE)
  x1 x2  x3  x4 x5
1  A 12 192 192  0
2  B 11 176  96 80
3  C  5  80   0 80

或者，在 melt() 过程中使用 na.rm。

学习 library(reshape) 的好处在于，引用作者的话 ("Reshaping Data with the reshape Package")，

"在 R 中，有许多通用函数可以聚合数据，例如 tapply、by 和 aggregate，以及一个专门用于重塑数据的函数 reshape。这些函数中的每一个都倾向于处理一到两个特定的场景，并且每一个都需要稍微不同的输入参数。实际上，您需要仔细思考，拼凑正确的操作序列，以使数据呈现您想要的形式。reshape 包源于我为咨询客户重塑数据时的挫败感，并通过一个只使用两个函数（melt 和 cast）的通用概念框架来解决这些问题。"

- vijucat

Answer 4

aggregate(data[, -1], by=list(data$x1), FUN=sum)

我删除了第一列，因为您在求和时没有使用它，它只是一个用于拆分数据的组变量（事实上，我随后在“by”中使用了它）。

该代码是使用R语言中的aggregate函数进行数据聚合计算，其中data表示要聚合的数据集，-1表示不包括第一列，list(data$x1)表示按照data中x1列的值进行分组，FUN=sum表示对每组数据求和。

- Davide Passaretti

---

data[,-1] 是什么意思？ - janos

我删除了第一列，因为您在求和时不需要考虑它，它只是一个分组变量，您必须使用它来拆分数据（事实上，我在“by”中使用了它）。 - Davide Passaretti

也许您可以在您的回答中添加这个解释，使其更加完整和有用；-) - janos

完成。感谢您的建议；-) - Davide Passaretti

您可能还想使用 na.rm=TRUE 吗？（这取决于问题提出者的真实数据 - 在此示例中，一组中的所有值都为 NA 或者 都不是 NA。） - Simon O'Hanlon