Convert Daily to Weekly/Monthly Data with R

Convert Daily Data into Weekly Data in R

These solutions all use base R and differ only in the definition and labelling of weeks.

1) cut the dates into weeks and then aggregate over those. Weeks start on Monday but you can add start.on.monday=FALSE to cut to start them on Sunday if you prefer.

Week <- as.Date(cut(DF$Date, "week"))
aggregate(Frequency ~ Week, DF, sum)
##         Week Frequency
## 1 2013-12-30       549
## 2 2014-01-06       418

2) If you prefer to define a week as 7 days starting with DF$Date[1] and label them according to the first date in that week then use this. (Add 6 to Week if you prefer the last date in the week.)

weekno <- as.numeric(DF$Date - DF$Date[1]) %/% 7
Week <- DF$Date[1] + 7 * weekno
aggregate(Frequency ~ Week, DF, sum)
##         Week Frequency
## 1 2014-01-01       690
## 2 2014-01-08       277

3) or if you prefer to label it with the first date existing in DF in that week then use this. This and the last Week definition give the same result if there are no missing dates as is the case here. (If you want the last existing date in the week rather than the first then replace match with findInterval.)

weekno <- as.numeric(DF$Date - DF$Date[1]) %/% 7
Week <- DF$Date[match(weekno, weekno)]
aggregate(Frequency ~ Week, DF, sum)
##         Week Frequency
## 1 2014-01-01       690
## 2 2014-01-08       277

Note

The input in reproducible form is assumed to be:

Lines <- "Date Frequency
1 2014-01-01 179
2 2014-01-02 82 
3 2014-01-03 89 
4 2014-01-04 109 
5 2014-01-05 90 
6 2014-01-06 66 
7 2014-01-07 75 
8 2014-01-08 106 
9 2014-01-09 89 
10 2014-01-10 82"
DF <- read.table(text = Lines)
DF$Date <- as.Date(DF$Date)

Convert daily data to weekly data and group them by companies

For each stock_code divide all the dates by 1st date and select the rows which are completely divisible by 7 so that you get the weekly dates.

library(dplyr)

result <- data %>% 
            group_by(stock_code) %>% 
            filter(as.integer(x - first(x)) %% 7 == 0)

Converting daily data to weekly data using R

At the time of writing: a bug, see github issue 148.

A possible workaround, using tidyr and timetk and purrr. Using timetk to get the data into xts format, transform data into weekly and turn back into a data.frame format. Including nest and unnest from tidyr and map from purrr. data.table is not needed but prints the data a lot better than tibbles.

library(tidyr)
library(timetk)
# library(purrr)

result <- dt %>% 
  group_by(symbol) %>% 
  nest() %>%   
  mutate(data = purrr::map(data, function(x) x %>% 
                             select(date, Open = open, High = high, Low = low, Close = close) %>% 
                             tk_xts(x, select = c(Open, High, Low, Close), date_var = date) %>% 
                             to.weekly %>% 
                             tk_tbl)) %>% 
  unnest(data) %>% 
  rename_with( ~ tolower(gsub("..", "", .x, fixed = T))) %>% 
  rename(date = index)

result %>% 
  data.table %>% 
  filter(date == "2017-02-03")

   symbol       date     open     high     low  close
1:   AAPL 2017-02-03  30.2325  32.6225  30.155  32.27
2:   GOOG 2017-02-03 814.6600 815.8400 790.520 801.49

R : Converting Daily time series to monthly

The possible solution for OP issue can be based on:

#Code
y.mon<-aggregate(property_damages_in_dollars~format(as.Date(date_decision_made),
format="%Y/%m"),data=final_data, FUN=sum)

More variants can be explored around the format() options.

R: aggregate daily to weekly data by group

You can group by multiple columns.

library(dplyr)

df %>%
  group_by(Country, week = lubridate::week(Date)) %>%
  summarise(StringencyIndex = mean(StringencyIndex))

Base R aggregate -

aggregate(StringencyIndex~Country + week, transform(df, week = format(Date, '%V')), mean)

---

Related Topics