Week 3B Window Functions

Author

Sarah Abdelrahman

DATA 607 — Data Acquisition & Management

M.S. in Data Science | CUNY School of Professional Studies

Student: Sarah Abdelrahman
Instructor: Professor Darwin Gomez

Introduction

For this assignment, I created a sample time-series dataset containing daily closing pricesfortwo companies: Apple (AAPL) and Microsoft (MSFT).

The dataset includes the date, stock symbol, and daily closing price.

I will use R and dplyr window functions to calculate the year-to-date average closing price and the six-day moving average for each stock.

Setup

library(dplyr)

Create the Dataset

I created a time-series dataset containing daily closing prices for Apple and Microsoft.

stock_data <- data.frame(
  date = rep(as.Date("2026-01-01") + 0:11, 2),

  symbol = c(
    rep("AAPL", 12),
    rep("MSFT", 12)
  ),

  close = c(
    243.85, 245.10, 242.75, 246.20,
    248.60, 247.90, 250.15, 251.40,
    249.80, 252.10, 254.35, 253.60,

    421.30, 423.15, 420.85, 425.40,
    427.20, 426.75, 429.10, 431.50,
    430.20, 433.60, 435.15, 434.50
  )
)

stock_data

         date symbol  close
1  2026-01-01   AAPL 243.85
2  2026-01-02   AAPL 245.10
3  2026-01-03   AAPL 242.75
4  2026-01-04   AAPL 246.20
5  2026-01-05   AAPL 248.60
6  2026-01-06   AAPL 247.90
7  2026-01-07   AAPL 250.15
8  2026-01-08   AAPL 251.40
9  2026-01-09   AAPL 249.80
10 2026-01-10   AAPL 252.10
11 2026-01-11   AAPL 254.35
12 2026-01-12   AAPL 253.60
13 2026-01-01   MSFT 421.30
14 2026-01-02   MSFT 423.15
15 2026-01-03   MSFT 420.85
16 2026-01-04   MSFT 425.40
17 2026-01-05   MSFT 427.20
18 2026-01-06   MSFT 426.75
19 2026-01-07   MSFT 429.10
20 2026-01-08   MSFT 431.50
21 2026-01-09   MSFT 430.20
22 2026-01-10   MSFT 433.60
23 2026-01-11   MSFT 435.15
24 2026-01-12   MSFT 434.50

Organize the Data

I organized the data by stock symbol and date so that each company can be analyzed separately.

stock_data <- stock_data |>
  arrange(symbol, date)

stock_data

         date symbol  close
1  2026-01-01   AAPL 243.85
2  2026-01-02   AAPL 245.10
3  2026-01-03   AAPL 242.75
4  2026-01-04   AAPL 246.20
5  2026-01-05   AAPL 248.60
6  2026-01-06   AAPL 247.90
7  2026-01-07   AAPL 250.15
8  2026-01-08   AAPL 251.40
9  2026-01-09   AAPL 249.80
10 2026-01-10   AAPL 252.10
11 2026-01-11   AAPL 254.35
12 2026-01-12   AAPL 253.60
13 2026-01-01   MSFT 421.30
14 2026-01-02   MSFT 423.15
15 2026-01-03   MSFT 420.85
16 2026-01-04   MSFT 425.40
17 2026-01-05   MSFT 427.20
18 2026-01-06   MSFT 426.75
19 2026-01-07   MSFT 429.10
20 2026-01-08   MSFT 431.50
21 2026-01-09   MSFT 430.20
22 2026-01-10   MSFT 433.60
23 2026-01-11   MSFT 435.15
24 2026-01-12   MSFT 434.50

Year-to-Date Average

Next, I grouped the data by stock symbol and calculated the year-to-date average closing price.

The cummean() function calculates the cumulative average from the beginning of the data through each date.

stock_results <- stock_data |>
  group_by(symbol) |>
  arrange(date, .by_group = TRUE) |>
  mutate(
    ytd_average = cummean(close)
  )

stock_results

# A tibble: 24 × 4
# Groups:   symbol [2]
   date       symbol close ytd_average
   <date>     <chr>  <dbl>       <dbl>
 1 2026-01-01 AAPL    244.        244.
 2 2026-01-02 AAPL    245.        244.
 3 2026-01-03 AAPL    243.        244.
 4 2026-01-04 AAPL    246.        244.
 5 2026-01-05 AAPL    249.        245.
 6 2026-01-06 AAPL    248.        246.
 7 2026-01-07 AAPL    250.        246.
 8 2026-01-08 AAPL    251.        247.
 9 2026-01-09 AAPL    250.        247.
10 2026-01-10 AAPL    252.        248.
# ℹ 14 more rows

Six-Day Moving Average

Next, I calculated the six-day moving average for each stock.

The calculation uses the current day’s closing price together with the previous five days.

stock_results <- stock_results |>
  mutate(
    six_day_moving_average = (
      close +
      lag(close, 1) +
      lag(close, 2) +
      lag(close, 3) +
      lag(close, 4) +
      lag(close, 5)
    ) / 6
  ) |>
  ungroup()

stock_results

# A tibble: 24 × 5
   date       symbol close ytd_average six_day_moving_average
   <date>     <chr>  <dbl>       <dbl>                  <dbl>
 1 2026-01-01 AAPL    244.        244.                    NA 
 2 2026-01-02 AAPL    245.        244.                    NA 
 3 2026-01-03 AAPL    243.        244.                    NA 
 4 2026-01-04 AAPL    246.        244.                    NA 
 5 2026-01-05 AAPL    249.        245.                    NA 
 6 2026-01-06 AAPL    248.        246.                   246.
 7 2026-01-07 AAPL    250.        246.                   247.
 8 2026-01-08 AAPL    251.        247.                   248.
 9 2026-01-09 AAPL    250.        247.                   249.
10 2026-01-10 AAPL    252.        248.                   250.
# ℹ 14 more rows

Final Results

The final results show the date, stock symbol, daily closing price, year-to-date average, and six-day moving average.

stock_results |>
  select(
    date,
    symbol,
    close,
    ytd_average,
    six_day_moving_average
  )

# A tibble: 24 × 5
   date       symbol close ytd_average six_day_moving_average
   <date>     <chr>  <dbl>       <dbl>                  <dbl>
 1 2026-01-01 AAPL    244.        244.                    NA 
 2 2026-01-02 AAPL    245.        244.                    NA 
 3 2026-01-03 AAPL    243.        244.                    NA 
 4 2026-01-04 AAPL    246.        244.                    NA 
 5 2026-01-05 AAPL    249.        245.                    NA 
 6 2026-01-06 AAPL    248.        246.                   246.
 7 2026-01-07 AAPL    250.        246.                   247.
 8 2026-01-08 AAPL    251.        247.                   248.
 9 2026-01-09 AAPL    250.        247.                   249.
10 2026-01-10 AAPL    252.        248.                   250.
# ℹ 14 more rows

Conclusion

In this assignment, I created a sample time-series dataset containing daily closing prices for Apple and Microsoft.

I organized the data by stock symbol and date and used dplyr window functions to analyze each stock separately.

I used cummean() to calculate the year-to-date average closing price.

I also calculated the six-day moving average using the current day’s closing price and the previous five days.