CODEWITHSUNDEEP
r
sharp
sharp

Reputation: 2158

Merge monthly dates in R

Does R have any function to merge the dates into months? 

dates <- c(20130401, 20130403,  20130504,   20130508,   20130511,
       20130716,    20130719,   20130723,   20130729,   20130907)
cost <- c(12,  41,  89, 45.5,   32.89,  74, 76, 12, 15.78,  10)

data <- data.frame(dates,cost)

data$dates <- as.Date(as.character(data$dates), "%Y%m%d")

Basically, I am looking for output similar to this. When I have to merge/stack do I have to get rid of days from the date? So, format yyyy-mm.

dates      total_cost_per_month
2013-04    53
2013-05    167.39
2013-07    177.78
2013-09    10

Upvotes: 3

Views: 88

Answers (1)

akrun
akrun

Reputation: 887851

Try format

aggregate(cbind(total_cost_per_month=cost)~cbind(dates=format(dates, 
         '%Y-%m')), data, sum)
#    dates total_cost_per_month
#1 2013-04                53.00
#2 2013-05               167.39
#3 2013-07               177.78
#4 2013-09                10.00

Or you could use sub

library(data.table)
setDT(data)[, list(total_cost_per_month= sum(cost)),
         list(dates=sub('-..$', '', dates))]

Upvotes: 4

Related Questions