Geek Answers Handbook

Sum a column with a sum and another column on average at the same time

I want to use the aggregate function in a date frame, but summarize one column and take the average of the other column.

Here is an example data frame

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

I can not do something like below. Is there an easy way to do this?

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

In the end i need

 Manager Amount SqFT Joe 200 500 Alice 325 700

so that I can calculate the cost per square foot by doing Amount / SqFT

+4
source share
2 answers

There are several ways to do this. Here are some of them that I like (all suggest that we start with data.frame named "mydf"):

Using ave and 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

Using 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

Using "sqldf":

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

Using aggregate and merge :

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

You can use the summarise function with ddply in the plyr package:

 library(plyr) ddply(mtcars,.(cyl),summarise,TotalWt=sum(wt),AveHP=mean(hp)) cyl TotalWt AveHP 1 4 25.143 82.63636 2 6 21.820 122.28571 3 8 55.989 209.21429
+4
source

More articles:


All Articles