how to perform sum and count on dataframe in R

Question

I have a dataframe with ID, type, and the area I would like to perform two operations at once

ID         Type         Area     
1           Aa          0.02    
2           Ag          0.12    
2           Ag          0.14    
2           Ag          0.80    
2           Bm          0.20    
2           Xm          0.13    

The expected outcome is

ID          Type       count      area     
1           Aa           1        0.02    
2           Ag           3        1.06 (sum)    
2           Bm           1        0.20    
2           Xm           1        0.13 

I have up to 100-150 ID and type, count and basal area varies for each type with the same ID, what would be the best approach to perform sum and count same time and keep type and ID in dataframe?

Thanks

Answer 1

1) Base R -- aggregate Counts are just the sum of a constant column of ones so using DF shown reproducibly in the Note at the end we add such a column and aggregate using sum. No packages are used.

aggregate(cbind(Count, Area) ~ ID + Type, transform(DF, Count = 1), sum)

giving:

  ID Type Count Area
1  1   Aa     1 0.02
2  2   Ag     3 1.06
3  2   Bm     1 0.20
4  2   Xm     1 0.13

2) Base R -- by An approach using only base R that does not rely on the trick of adding a column of ones is to use by. The by call produces a list of class by and the do.call("rbind", ...) converts that to a data frame.

do.call("rbind", by(DF, DF[1:2], with, 
  data.frame(ID = ID[1], Type = Type[1], Count = length(ID), Area = sum(Area))))

giving:

  ID Type Count Area
1  1   Aa     1 0.02
2  2   Ag     3 1.06
3  2   Bm     1 0.20
4  2   Xm     1 0.13

3) sqldf SQL allows the separate and simultaneous application of count and sum.

library(sqldf)
sqldf("select ID, Type, count(*) as Count, sum(Area) as Area
  from DF
  group by 1, 2")

giving:

  ID Type Count Area
1  1   Aa     1 0.02
2  2   Ag     3 1.06
3  2   Bm     1 0.20
4  2   Xm     1 0.13

4) data.table The data.table package can also be used.

library(data.table)

DT <- as.data.table(DF)
DT[, .(Count = .N, Area = sum(Area)), by = "ID,Type"]

giving:

   ID Type Count Area
1:  1   Aa     1 0.02
2:  2   Ag     3 1.06
3:  2   Bm     1 0.20
4:  2   Xm     1 0.13

Note

Lines <- "ID         Type         Area     
1           Aa          0.02    
2           Ag          0.12    
2           Ag          0.14    
2           Ag          0.80    
2           Bm          0.20    
2           Xm          0.13 "

DF <- read.table(text = Lines, header = TRUE)