Window Functions

Author

David Melchor

Introduction

We were asked to find a dataset that contained time series data for two or more separate items. I found the Individual Household Electric Power Consumption dataset for this assignment at the UCI Machine Learning Repository website from this link UCI Machine Learning Data Repository. Using the ucimlrepo R package I was able to import the data directly into this script, so there is no need to import data from any document. Technical setup and package usage guidelines follow the documentation outlined on the ucimlrepo R Package Site

Business & Data Science Question

  • Business Question: “Which sub-metered areas of the house (kitchen, laundry room, or climate control) consume the most energy over time, and how do their daily and year-to-date usage trends compare?”

Strategy & Technical Approach

  1. Data Import: I will use the ucimlrepo package to import the dataset direclty into R.
  2. Data Cleaning & Wrangling:
    • Check data types
    • Look for missing values
    • Clean column headers
    • Recode variables as needed
    • Select a subset of data needed for analysis
  3. Exploratory Visualizations: Use ggplot2 to visualize relationships and identify patient risks.

Anticipated Data Challenges

  • Missing Data Handling: The dataset may contain missing values records that may require filtering.
  • Recoding Values: I’m going to take a while guess and say that I’m going to have to recode some value to something that makes more sense.

Installing and Loading Packages

# Install ucimlrepo and load the packages
pacman::p_load(ucimlrepo, tidyverse, slider, scales)

Loading the Data

# Fetch the dataset from the UCI data repository
uci_data <- fetch_ucirepo(name = "Individual Household Electric Power Consumption")

# Extract the original dataset
power_data <- uci_data$data$features

Data Exploration

# Check out the data dictionary
data_dictionary <- uci_data$variables

# Check the data structure
glimpse(power_data)
Rows: 2,075,259
Columns: 9
$ Date                  <chr> "16/12/2006", "16/12/2006", "16/12/2006", "16/12…
$ Time                  <chr> "17:24:00", "17:25:00", "17:26:00", "17:27:00", …
$ Global_active_power   <chr> "4.216", "5.360", "5.374", "5.388", "3.666", "3.…
$ Global_reactive_power <chr> "0.418", "0.436", "0.498", "0.502", "0.528", "0.…
$ Voltage               <chr> "234.840", "233.630", "233.290", "233.740", "235…
$ Global_intensity      <chr> "18.400", "23.000", "23.000", "23.000", "15.800"…
$ Sub_metering_1        <chr> "0.000", "0.000", "0.000", "0.000", "0.000", "0.…
$ Sub_metering_2        <chr> "1.000", "1.000", "2.000", "1.000", "1.000", "2.…
$ Sub_metering_3        <dbl> 17, 16, 17, 17, 17, 17, 17, 17, 17, 16, 17, 17, …

Data Cleaning and Processing

The variables Date, Time, and Sub_metering_1-3 are <chr> type variables so R will not be able to recognize calendar dates, time, or numbers.

# Transform data types
power_data <- power_data |> 
  mutate(
    Date = dmy(Date),
    Kitchen = as.numeric(Sub_metering_1),
    Laundry = as.numeric(Sub_metering_2),
    Climate = as.numeric(Sub_metering_3)
  )
Warning: There were 2 warnings in `mutate()`.
The first warning was:
ℹ In argument: `Kitchen = as.numeric(Sub_metering_1)`.
Caused by warning:
! NAs introduced by coercion
ℹ Run `dplyr::last_dplyr_warnings()` to see the 1 remaining warning.
power_data <- power_data |> 
  pivot_longer(
    cols = c(Kitchen, Laundry, Climate),
    names_to = "Sub_Meter",
    values_to = "Energy_Wh"
  )
# Add up the Wh by day
power_summary <- power_data |> 
  group_by(Date, Sub_Meter) |> 
  summarise(
    Daily_Energy = sum(Energy_Wh, na.rm = TRUE),
    .groups = "drop"
  )

Applying Window Functions

# Create a new variable year
power_summary <- power_summary |> 
  mutate(
    Year = year(Date)) |> 
# Year to date metric
  group_by(Sub_Meter, Year) |> 
  mutate(
    YTD_Avg = cummean(Daily_Energy)) |> 
# 6 day moving average
  group_by(Sub_Meter) |> 
  mutate(
    Moving_Avg_6D = slide_dbl(Daily_Energy, mean, .before = 5, .complete = TRUE)) |> 
  ungroup()

Visualizing Time Series for 6-Day Moving Average

power_summary |> 
  ggplot(aes(
    x = Date,
    y = Moving_Avg_6D,
    colour = Sub_Meter)) +
  geom_line(
    linewidth = 0.8,
    alpha = 0.85) +
  scale_y_continuous(
    labels = comma) +
  scale_color_manual(values = c(
    "Climate" = "#009E73", 
    "Kitchen" = "#E69F00", 
    "Laundry" = "#0072B2")) +
 labs(
    title = "Household Energy Consumption Trends (2006–2010)",
    subtitle = "6-day moving average of daily energy consumption per sub-meter",
    x = "Date",
    y = "6-Day Moving Average (Watt-Hours)",
    color = "Sub-meter Category",
    caption = "Data Source: UCI Machine Learning Repository") +
  theme_minimal(base_size = 12) +
  theme(
    plot.title = element_text(face = "bold", size = 14),
    plot.subtitle = element_text(color = "grey30", margin = margin(b = 10)),
    legend.position = "bottom",
    panel.grid.minor = element_blank()
  )
Warning: Removed 15 rows containing missing values or values outside the scale range
(`geom_line()`).

Visualizing Time Series for Year-To-Date

power_summary |> 
  ggplot(aes(
    x = Date,
    y = YTD_Avg,
    colour = Sub_Meter)) +
  geom_line(
    linewidth = 0.8,
    alpha = 0.85) +
  scale_y_continuous(
    labels = comma) +
  scale_color_manual(values = c(
    "Climate" = "#009E73", 
    "Kitchen" = "#E69F00", 
    "Laundry" = "#0072B2")) +
 labs(
    title = "Household Energy Consumption Trends (2006–2010)",
    subtitle = "YTD cummulative average of energy consumption per sub-meter",
    x = "Date",
    y = "YTD Cummulative Average (Watt-Hours)",
    color = "Sub-meter Category",
    caption = "Data Source: UCI Machine Learning Repository") +
  theme_minimal(base_size = 12) +
  theme(
    plot.title = element_text(face = "bold", size = 14),
    plot.subtitle = element_text(color = "grey30", margin = margin(b = 10)),
    legend.position = "bottom",
    panel.grid.minor = element_blank()
  )

Conclusion

Looking at the two visualizations side-by-side highlights how different metrics frame household energy patterns over time. The 6-day moving average chart clearly captures seasonality, driven almost entirely by the dramatic ups and downs of the climate sub-meter as heating and cooling demands shift throughout the year.

In contrast, the YTD cumulative average chart offers a look at annual baseline performance. The hard reset every January 1st causes noticeable volatility early in the year, where a small number of days in the average leads to sharp initial spikes. As more days are added to the calculation, the metric stabilizes, offering a reliable picture of cumulative energy consumption across each sub-meter as the year progresses.