# Conquering Daily Data: How to Aggregate to Months and Years Like a Pro in R

code
rtip
timeseries
Author

Steven P. Sanderson II, MPH

Published

January 8, 2024

# Introduction

Taming the beast of daily data can be daunting. While it captures every detail, sometimes you need a bird’s-eye view. Enter aggregation, your secret weapon for transforming daily data into monthly and yearly insights. In this post, we’ll dive into the world of R, where you’ll wield powerful tools like `dplyr` and `lubridate` to master this data wrangling art.

# Packages: Gear Up with the Right Packages

Think of R packages like your trusty toolbox. Today, we’ll need two essentials:

• `dplyr`: This swiss army knife lets you manipulate and summarize data like a boss.
• `lubridate`: Time is our domain, and `lubridate` helps us navigate it with precision, especially for dates.

# Sample Data, Our Training Ground

Imagine you have daily sales data for a year. Each row represents a day, with columns for date, product, and sales amount. Let’s create a mini version:

``````library(dplyr)
library(lubridate)

# Generate random dates and sales
set.seed(123)
dates <- seq(as.Date('2023-01-01'), as.Date('2023-12-31'), by = 'day')
sales <- runif(365, min=5000, max=10000)

# Create our data frame
daily_data <- data.frame(date = dates, sales = sales)

# Peek at our data
``````        date    sales
1 2023-01-01 6437.888
2 2023-01-02 8941.526
3 2023-01-03 7044.885
4 2023-01-04 9415.087
5 2023-01-05 9702.336
6 2023-01-06 5227.782``````

This code generates 10 random dates and sales figures, and stores them in a data frame called `daily_data`.

# Monthly Magic – From Days to Months

Now, let’s transform this daily data into monthly insights. Here’s the incantation:

``````# Group data by month
monthly_data <- daily_data %>%
# Group by month extracted from date
group_by(month = month(date)) %>%
# Calculate total sales for each month
summarize(total_sales = sum(sales))

``````# A tibble: 6 × 2
month total_sales
<dbl>       <dbl>
1     1     245675.
2     2     199109.
3     3     233764.
4     4     227888.
5     5     230928.
6     6     222015.``````

Let’s break it down:

• `group_by(month = month(date))`: We tell R to group our data by the month extracted from the `date` column.
• `summarize(total_sales = sum(sales))`: Within each month group, we calculate the total sales by summing the `sales` values.

# Yearly Triumph – Conquering the Calendar

Yearning for yearly insights? Fear not! Modify the spell slightly:

``````# Group data by year
yearly_data <- daily_data %>%
# Group by year extracted from date
group_by(year = year(date)) %>%
# Calculate average sales for each year
summarize(average_sales = mean(sales))

``````# A tibble: 1 × 2
year average_sales
<dbl>         <dbl>
1  2023         7494.``````

Here, we group by the year extracted from `date` and then calculate the average sales for each year.

# But what about base R?

So far, we’ve used `dplyr` to group and summarize our data. But what if you don’t have `dplyr`? No problem! You can use base R functions like `aggregate()` to achieve the same results:

``````monthly_data <- aggregate(
daily_data\$sales,
by = list(month = format(daily_data\$date, '%m')),
FUN = sum
)
``````  month        x
1    01 245675.1
2    02 199108.7
3    03 233764.1
4    04 227888.3
5    05 230928.0
6    06 222015.3``````
``````yearly_data <- aggregate(
daily_data\$sales,
by = list(year = format(daily_data\$date, '%Y')),
FUN = mean
)
``````  year      x
1 2023 7493.8``````

# Experiment!

The magic doesn’t stop there! You can customize your aggregations to your heart’s content. Try these variations:

• Calculate maximum sales per month.
• Find the product with the highest average sales per year.
• Group data by month and product to see which products perform best each month.

# Remember

• Play around with different `summarize()` functions like `min()`, `max()`, or `median()`.
• Use `filter()` before `group_by()` to focus on specific subsets of data.
• Explore other time units like weeks or quarters with lubridate’s powerful tools.

# The Takeaway

Mastering daily data aggregation is a valuable skill for any data warrior. With the help of R and your newfound knowledge, you can transform mountains of daily data into insightful monthly and yearly summaries. So, go forth, conquer your data, and share your insights with the world!

Bonus Challenge: Share your own R code and insights in the comments below! Let’s learn from each other and become daily data aggregation masters together!