CODEWITHSUNDEEP
rdataframetime-series
psysky
psysky

Reputation: 3195

time series aggregation by month in R

In mydataset, the dates in day format. i need aggregate it in to month format. to make it clear, here mydataset.

mydat
structure(list(date = structure(c(1L, 1L, 2L, 2L, 2L, 3L, 3L, 
3L, 3L, 3L, 3L, 3L, 4L, 4L, 4L), .Label = c("12.01.2015", "13.01.2015", 
"14.01.2015", "15.01.2015"), class = "factor"), Y = c(200L, 50L, 
100L, 50L, 200L, 200L, 50L, 200L, 100L, 1000L, 1000L, 50L, 50L, 
100L, 200L)), .Names = c("date", "Y"), class = "data.frame", row.names = c(NA, 
-15L))

Aggreation must by sum of Y. in output i expect this format 01.2015 3550(the sum of Y variable for jan,2015) 02.2015 4000(the sum of Y variable for feb,2015)

How to do it? i tried do it like here Aggregate time series object by month R , but it didn't help me. How do it correct?

Upvotes: 1

Views: 618

Answers (3)

G. Grothendieck
G. Grothendieck

Reputation: 269441

1) data.frame Use aggregate and a "yearmon" class grouping variable:

library(zoo)

fmt <- "%d.%m.%Y"
aggregate(mydat["Y"], list(Date = as.yearmon(mydat$date, fmt)), sum)

##       Date    Y
## 1 Jan 2015 3550

2) zoo You might consider using a time series representation rather than a data frame. This makes many time series operations easier. Here we use read.zoo to convert mydat to a zoo object. fmt is from above.

library(zoo)

Y <- read.zoo(mydat, FUN = as.yearmon, format = fmt, aggregate = sum)

giving this zoo object:

Y
## Jan 2015 
##     3550

Although unnecessary, if you want to convert it back to data frame see ?fortify.zoo .

3) xts/zoo

Convert to an xts time series representation x and then use aggregate.zoo creating a zoo object z. fmt is from above.

library(xts)  # also pulls in zoo

x <- xts(mydat["Y"], as.Date(mydat$date, fmt))
z <- aggregate(x, as.yearmon, sum)
z
## 
## Jan 2015 3550

Upvotes: 1

Maurits Evers
Maurits Evers

Reputation: 50668

Here is a base R solution using aggregate:

with(mydat, aggregate(
    Y, 
    list(month_year = format(as.POSIXct(date, format = "%d.%m.%Y"), "%m/%Y")), 
    sum))
#  month_year    x
#1    01/2015 3550

Explanation: Extract month_year component from date and sum Y by month_year using aggregate.

Sample data

mydat <- structure(list(date = structure(c(1L, 1L, 2L, 2L, 2L, 3L, 3L,
        3L, 3L, 3L, 3L, 3L, 4L, 4L, 4L), .Label = c("12.01.2015", "13.01.2015",
        "14.01.2015", "15.01.2015"), class = "factor"), Y = c(200L, 50L,
        100L, 50L, 200L, 200L, 50L, 200L, 100L, 1000L, 1000L, 50L, 50L,
        100L, 200L)), .Names = c("date", "Y"), class = "data.frame", row.names = c(NA,
        -15L))

Upvotes: 2

akrun
akrun

Reputation: 886938

We create a grouping variable with year + month and then do the sum

library(tidyverse)
library(zoo)
mydat %>%
   group_by(yearMon = as.yearmon(dmy(date))) %>% 
   summarise(Y = sum(Y))

Upvotes: 1

Related Questions