同时汇总一列和平均值一列 [英] aggregate a column by sum and another column by mean at the same time

问题描述

我想在日期框架上使用聚合函数，但将一列求和并取另一列的平均值。

I want to use aggregate function on a date frame but sum one column and take average of another column.

这里是示例数据框架

Manager   Category  Amount  SqFt
Joe           Rent     150   500
Alice         Rent     250   700
Joe      Utilities      50   500
Alice    Utilities      75   700

我无法执行以下操作。有简单的方法吗？

I cannot do something like below. Is there an easy way to do it ?

Avg_CPSF=aggregate(cbind(Amount,SqFt)~Manager,data=aaa,FUN=c(sum,mean)

最终我需要

Manager  Amount   SqFT
Joe       200      500
Alice     325      700

，这样我就可以通过计算数量/平方英尺来计算每平方英尺的成本

so that I can calculate Cost per Square Foot by doing Amount/SqFT

推荐答案

有这样做的几种方法。以下是一些我喜欢的方法（所有这些假设我们都从名为 mydf的 data.frame 开始）：

There are several ways to do this. Here are some that I like (all assuming we're starting with a data.frame named "mydf"):

使用 ave 和 unique

unique(within(mydf, {
  Amount <- ave(Amount, Manager, FUN = sum)
  SqFt <- ave(SqFt, Manager, FUN = mean)
  rm(Category)
}))
#   Manager Amount SqFt
# 1     Joe    200  500
# 2   Alice    325  700

---

使用 data.table ：

library(data.table)
DT <- data.table(mydf)
DT[, list(Amount = sum(Amount), SqFt = mean(SqFt)), by = "Manager"]
#    Manager Amount SqFt
# 1:     Joe    200  500
# 2:   Alice    325  700

---

使用 sqldf：

---

Using "sqldf":

library(sqldf)
sqldf("select Manager, sum(Amount) `Amount`, 
      avg(SqFt) `SqFt` from mydf group by Manager")

---

使用汇总和合并：

merge(aggregate(Amount ~ Manager, mydf, sum), 
      aggregate(SqFt ~ Manager, mydf, mean))

这篇关于同时汇总一列和平均值一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！