Saturday, September 09, 2017

Count number of items and sum of the items in a column in R


mydf <- data.frame(Key=c('a','b','c','d','a','a','a','a','b','c','d','c','c','d','b','b','a'), Values=c(4,6,3,4,5,6,2,1,3,4,5,6,4,2,4,5,6))
mydf_keys <- unique(mydf$Key)
mydf_vals <- c(nrow(mydf_keys))
for (i in mydf_keys)
    mydf_vals <- c(mydf_vals, nrow(mydf[mydf$Key %in% i, ]))

#mydf_count gives count of items i.e. how many times they appear
mydf_count <- data.frame(item=mydf_keys, count=mydf_vals)

#mydf_sum gives sum of values of items in the column you specify
mydf_sum <- setNames(aggregate(mydf$Values, by = list(item=mydf$Key), FUN = sum, na.rm = TRUE), c('item', 'sum'))


If you have a better approach for this then please share in comments below or email me. :)