R aggregate data.frame with date column
Solution 1
Indicate the variables you are trying to get the aggregate of in your aggregate statement, and this problem should be resolved:
dta.sum <- aggregate(x = dta[c("Expenditure","Indicator")],
FUN = sum,
by = list(Group.date = dta$Date))
EDITED TO ADD EXPLANATION: When you give the aggregate
argument as just dta
, aggregate attempts to apply the argument to every column. sum
is not defined for date values in R, and therefore you are getting errors. You want to exclude the grouping column by using the code described above.
Solution 2
Or use dplyr
:
library(dplyr)
dta %>%
group_by(Date) %>%
summarise(Tot.Expenditure = sum(Expenditure))
Solution 3
Upgrade from base and use data.table
instead to simplify (and speed up) your code/life:
library(data.table)
dt = as.data.table(dta)
dt[, lapply(.SD, sum), by = Date]
Solution 4
df <- data.frame(c('29-01-2011', '25-01-2012', '11-02-2011'), c(5455, 5452, 365), c(212, 211, 5))
colnames(df) <- c('Date', 'Expenditure', 'Indicator')
colSums(df[2])
#>Expenditure
#11272
Konrad
I've an interest in R, R/Shiny and mixing R with other languages.
Updated on July 17, 2020Comments
-
Konrad almost 4 years
I have the data frame resambling the one in the below
Date Expenditure Indicator 29-01-2011 5455 212 25-01-2012 5452 111 11-02-2011 365 5
I'm currently interested in summing up the Expenditure values, I'm trying to use the function below
dta.sum <- aggregate(x = dta, FUN = sum, by = list(Group.date = dta$date))
but R returns the following error, Error in Summary.Date(c(15614L, 15614L, 15614L, 15614L, 15614L, 15614L, : sum not defined for "Date" objects. The Date column was previously defined as date with use of the as.Date function. Analogous function but with the mean works fine.
dta.sum <- aggregate(x = dta, FUN = mean by = list(Group.date = dta$date))
I would like to keep date formatted as date.
-
Konrad almost 10 yearsThank you very much it seems that it's working. Just to add to the discussion, can I also make the old date column disappear in the same function without doing list(date = dta$date)? I like to keep functions neat and concise and it produces a table with Group.date and date column, one being redundant. I would like the aggregate function to drop the redundant date column automatically, if possible.
-
TARehman almost 10 yearsI think you need to do the
list()
but that you might be able to get away without naming the list elements (i.e without Group.date). -
Konrad almost 10 yearsThanks for all the answers.