Home Â» How to Aggregate Daily Data to Monthly and Yearly in R

# How to Aggregate Daily Data to Monthly and Yearly in R

Occasionally you may want to aggregate daily data to weekly, monthly, or yearly data in R.

This tutorial explains how to easily do so using theÂ lubridate andÂ dplyr packages.

## Example: Aggregate Daily Data in R

Suppose we have the following data frame in R that shows the daily sales of some item over the course of 100 consecutive days:

```#make this example reproducible
set.seed(1)

#create data frame
df as.Date("2020-12-01") + 0:99,
sales = runif(100, 20, 50))

#view first six rows

date    sales
1 2020-12-01 27.96526
2 2020-12-02 31.16372
3 2020-12-03 37.18560
4 2020-12-04 47.24623
5 2020-12-05 26.05046
6 2020-12-06 46.95169```

To aggregate this data, we can use the floor_date() function from theÂ lubridateÂ package which uses the following syntax:

`floor_date(x, unit)`

where:

• x:Â A vector of date objects.
• unit:Â A time unit to round to. Options include second, minute, hour, day, week, month, bimonth, quarter, halfyear, and year.

The following code snippets show how to use this function along with theÂ group_by() and summarize() functions from theÂ dplyrÂ package to find the mean sales by week, month, and year:

Mean Sales by Week

```library(lubridate)
library(dplyr)

#round dates down to week
df\$week floor_date(df\$date, "week")

#find mean sales by week
df %>%
group_by(week) %>%
summarize(mean = mean(sales))

# A tibble: 15 x 2
week        mean

1 2020-11-29  33.9
2 2020-12-06  35.3
3 2020-12-13  39.0
4 2020-12-20  34.4
5 2020-12-27  33.6
6 2021-01-03  35.9
7 2021-01-10  37.8
8 2021-01-17  36.8
9 2021-01-24  32.8
10 2021-01-31  33.9
11 2021-02-07  34.1
12 2021-02-14  41.6
13 2021-02-21  31.8
14 2021-02-28  35.2
15 2021-03-07  37.1```

Mean Sales by Month

```library(lubridate)
library(dplyr)

#round dates down to week
df\$month floor_date(df\$date, "month")

#find mean sales by month
df %>%
group_by(month) %>%
summarize(mean = mean(sales))

# A tibble: 4 x 2
month       mean

1 2020-12-01  35.3
2 2021-01-01  35.6
3 2021-02-01  35.2
4 2021-03-01  37.0
```

Mean Sales by Year

```library(lubridate)
library(dplyr)

#round dates down to week
df\$year floor_date(df\$date, "year")

#find mean sales by month
df %>%
group_by(year) %>%
summarize(mean = mean(sales))

# A tibble: 2 x 2
year        mean

1 2020-01-01  35.3
2 2021-01-01  35.7
```

Note that we chose to aggregate by the mean, but we could use any summary statistic weâ€™d like such as the median, mode, max, min, etc.