Different results of `summarize` and `group_by` with different months in time-series datasets

user_16044195 0
Viewed 40

I have daily time-series data for more than 20 years. I want to extract the quantiles (0.1, 0.5, 0.9) by three months window for each year, which divided into JFM (Jan-Mar), FMA (Feb-Apr), ... and so on until OND (Oct-Dec). As a newbie in R, after so many days of research in the past two weeks, I finally found the method to do this. However, in the final step, I am stuck on this problem.

Actually, I am working using lists. But, for example, let's say we have this dataframe:

library(lubridate)
Date<-seq.Date(ymd(19700101),ymd(19721231),"day")
Q<-runif(ymd(19730101)-ymd(19700101),1,20)
df<-data.frame(Date,Q)

Now, we subset the df to obtain only specific three months (in this case JFM and FMA):

df.JFM<-df[months(df$Date) %in% month.name[1:3],] #cutting Jan-Mar
df.FMA<-df[months(df$Date) %in% month.name[2:4],] #cutting Feb-Apr

Then, to find the quantile of 50% for three-month series, I use this method:

library(dplyr)
df.JFM %>% group_by(Year=floor_date(Date, "3 months")) %>%
  summarize(Q=quantile(Q, 0.5, na.rm=T))
# A tibble: 3 x 2
  Year           Q
  <date>     <dbl>
1 1970-01-01  8.83
2 1971-01-01  9.88
3 1972-01-01 11.3

No issue in the JFM set. Let's see for FMA set:

df.FMA %>% group_by(Year=floor_date(Date, "3 months")) %>%
  summarize(Q=quantile(Q, 0.5, na.rm=T))
# A tibble: 6 x 2
  Year           Q
  <date>     <dbl>
1 1970-01-01  8.75
2 1970-04-01 13.5 
3 1971-01-01  8.58
4 1971-04-01 13.2 
5 1972-01-01 10.2 
6 1972-04-01  8.29

Here, we found that the floor_date function round down the February dates to January dates of the same year. I expected that after I cut the data with February as the first element in the Date column, the floor_date would start from February. Apparently no. I also have tried with other three-month series and found that they give the same result as the FMA set. I also tried to change the index of the dataframe to become the same as the original index before the subset/cut, but no luck.

How to solve this problem? Other methods for obtaining quantiles from a given period in a year (in the sense of my aim described at the beginning of the post) are also very welcomed.

Thank you.

dplyr lubridate time-series r date
1 Answers

Here, floor_date/ceiling_date performs rounding every 3 months always from the start of the year and not based on the dates in the data.

Here you may use cut instead which works as per your requirement.

library(dplyr)

df.JFM %>% 
  group_by(Year=cut(Date, "3 months")) %>%
  summarize(Q=quantile(Q, 0.5, na.rm=TRUE))

#  Year           Q
#  <fct>      <dbl>
#1 1970-01-01 11.0 
#2 1971-01-01 11.5 
#3 1972-01-01  9.57

df.FMA %>% 
  group_by(Year= cut(Date, '3 months')) %>%
  summarize(Q = quantile(Q, 0.5, na.rm=T))

#   Year        Q
#  <fct>      <dbl>
#1 1970-02-01 11.3 
#2 1971-02-01 10.5 
#3 1972-02-01  9.67
user_39629140
Related