Introduction

This assignment asks to work with a time-series dataset that includes at least two separate items and use window functions to calculate a year-to-date average and a six-day moving average for each one. My plan is to create the dataset in Mockaroo with daily values over the same date range, then organize the data by item and date before doing the calculations. I will also check for missing values, gaps in the dates, or any formatting issues that could affect the results.

Load Packages

The dplyr package is used for data manipulation and window functions, while readr is used to import the CSV file.

library(dplyr)
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
library(readr)

load data

The dataset is imported from a raw CSV file stored on GitHub.

tips <- read_csv(
  "https://raw.githubusercontent.com/jmald1987/DATA607_Window_Functions/main/MOCK_DATA.csv"
)
## Rows: 28 Columns: 3
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (2): date, employee
## dbl (1): tips_earned
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
tips
## # A tibble: 28 × 3
##    date     employee  tips_earned
##    <chr>    <chr>           <dbl>
##  1 9/1/2026 Employee1        112.
##  2 9/1/2026 Employee2        148 
##  3 9/2/2026 Employee1        129.
##  4 9/2/2026 Employee2        162.
##  5 9/3/2026 Employee1         95 
##  6 9/3/2026 Employee2        137.
##  7 9/4/2026 Employee1        143.
##  8 9/4/2026 Employee2        175 
##  9 9/5/2026 Employee1        156.
## 10 9/5/2026 Employee2        154.
## # ℹ 18 more rows

Inspect the Data

The structure of the dataset is checked to identify the variables and their data types.

str(tips)
## spc_tbl_ [28 × 3] (S3: spec_tbl_df/tbl_df/tbl/data.frame)
##  $ date       : chr [1:28] "9/1/2026" "9/1/2026" "9/2/2026" "9/2/2026" ...
##  $ employee   : chr [1:28] "Employee1" "Employee2" "Employee1" "Employee2" ...
##  $ tips_earned: num [1:28] 112 148 129 162 95 ...
##  - attr(*, "spec")=
##   .. cols(
##   ..   date = col_character(),
##   ..   employee = col_character(),
##   ..   tips_earned = col_double()
##   .. )
##  - attr(*, "problems")=<externalptr>

Format the Date

The date variable is converted into a date vector using as.Date(). The format matches the month, day, and year format used in the dataset.

tips$date <- as.Date(tips$date, format = "%m/%d/%Y")

class(tips$date)
## [1] "Date"

Order the Time Series

The observations are arranged by employee and date. This places each employee’s observations in chronological order before calculating the window functions.

tips <- tips %>%
  arrange(employee, date)

tips
## # A tibble: 28 × 3
##    date       employee  tips_earned
##    <date>     <chr>           <dbl>
##  1 2026-09-01 Employee1        112.
##  2 2026-09-02 Employee1        129.
##  3 2026-09-03 Employee1         95 
##  4 2026-09-04 Employee1        143.
##  5 2026-09-05 Employee1        156.
##  6 2026-09-06 Employee1        121 
##  7 2026-09-07 Employee1        168.
##  8 2026-09-08 Employee1        134.
##  9 2026-09-09 Employee1        149.
## 10 2026-09-10 Employee1        119.
## # ℹ 18 more rows

Calculate the Year-to-Date Average

The data is grouped by employee so that the calculations are performed separately for each employee. cummean() is a window function that calculates the cumulative average through each observation.

tips <- tips %>%
  group_by(employee) %>%
  mutate(
    ytd_average = cummean(tips_earned)
  )

tips
## # A tibble: 28 × 4
## # Groups:   employee [2]
##    date       employee  tips_earned ytd_average
##    <date>     <chr>           <dbl>       <dbl>
##  1 2026-09-01 Employee1        112.        112.
##  2 2026-09-02 Employee1        129.        121.
##  3 2026-09-03 Employee1         95         112.
##  4 2026-09-04 Employee1        143.        120.
##  5 2026-09-05 Employee1        156.        127.
##  6 2026-09-06 Employee1        121         126.
##  7 2026-09-07 Employee1        168.        132.
##  8 2026-09-08 Employee1        134.        132.
##  9 2026-09-09 Employee1        149.        134.
## 10 2026-09-10 Employee1        119.        133.
## # ℹ 18 more rows

Calculate the Six-Day Moving Average

The six-day moving average is calculated separately for each employee. The current day’s tips and the previous five observations are used to create a six-observation window. The lag() window function accesses each of the previous observations.

tips <- tips %>%
  mutate(
    six_day_average = (
      tips_earned +
      lag(tips_earned, 1) +
      lag(tips_earned, 2) +
      lag(tips_earned, 3) +
      lag(tips_earned, 4) +
      lag(tips_earned, 5)
    ) / 6
  ) %>%
  ungroup()

tips
## # A tibble: 28 × 5
##    date       employee  tips_earned ytd_average six_day_average
##    <date>     <chr>           <dbl>       <dbl>           <dbl>
##  1 2026-09-01 Employee1        112.        112.             NA 
##  2 2026-09-02 Employee1        129.        121.             NA 
##  3 2026-09-03 Employee1         95         112.             NA 
##  4 2026-09-04 Employee1        143.        120.             NA 
##  5 2026-09-05 Employee1        156.        127.             NA 
##  6 2026-09-06 Employee1        121         126.            126.
##  7 2026-09-07 Employee1        168.        132.            135.
##  8 2026-09-08 Employee1        134.        132.            136.
##  9 2026-09-09 Employee1        149.        134.            145.
## 10 2026-09-10 Employee1        119.        133.            141.
## # ℹ 18 more rows

Results

The final results display the date, employee, daily tips, year-to-date average, and six-day moving average.

tips %>%
  select(
    date,
    employee,
    tips_earned,
    ytd_average,
    six_day_average
  )
## # A tibble: 28 × 5
##    date       employee  tips_earned ytd_average six_day_average
##    <date>     <chr>           <dbl>       <dbl>           <dbl>
##  1 2026-09-01 Employee1        112.        112.             NA 
##  2 2026-09-02 Employee1        129.        121.             NA 
##  3 2026-09-03 Employee1         95         112.             NA 
##  4 2026-09-04 Employee1        143.        120.             NA 
##  5 2026-09-05 Employee1        156.        127.             NA 
##  6 2026-09-06 Employee1        121         126.            126.
##  7 2026-09-07 Employee1        168.        132.            135.
##  8 2026-09-08 Employee1        134.        132.            136.
##  9 2026-09-09 Employee1        149.        134.            145.
## 10 2026-09-10 Employee1        119.        133.            141.
## # ℹ 18 more rows

The first five observations for each employee have NA for the six-day moving average because six observations are required before the first complete six-day window can be calculated.

Conclusion

The window functions calculate the averages separately for each employee while maintaining the chronological order of the time series. The year-to-date average shows the cumulative average of each employee’s tips through each available date. The six-day moving average uses the current observation and the previous five observations to show changes in the employee’s more recent tip earnings. Together, these calculations provide a comparison between the cumulative average and the shorter six-day trend for each employee.

Sources