MoOdEV MoOdEV - 13 days ago 5
R Question

Merge vs Sort: ordering dataframe by month/year

I need to sort a data frame chronologically according to months/years format and get monthly sum and mean.
I've tried several attempts: it's a problem of merging or of sorting, and I didn't get a clean data frame.

Here is a reproducible example.
Date is supposed to be class() format.

Date <- c('2008-05-12','2006-01-30', '2006-02-29', '2006-01-15', '2008-05-20','2008-09-26')
Income <- c(10, 20, 30, 20, 10, 20)
df<-data.frame(Date,Income)


I'd like to achieve a data.frame of this form

ShortDate Sum Mean
01-2006 40 20
02-2006 30 30
05-2008 20 10
09-2008 20 20


Thanks for any advice.

Answer

Using dplyr, we group by the formatted 'Date' (%m-%Y), and summarise with the sum and mean of 'Income'.

library(dplyr)
df %>% 
   group_by(ShortDate = format(as.Date(Date), "%m-%Y")) %>% 
   summarise(Sum = sum(Income), Mean = mean(Income)) 
#   ShortDate   Sum  Mean
#      <chr> <dbl> <dbl>
#1   01-2006    40    20
#2   02-2006    30    30
#3   05-2008    20    10
#4   09-2008    20    20
Comments