This project forecasts daily withdrawals for four ATMs in May 2010, monthly residential electricity consumption for 2014, and hourly waterflow for one week. Each analysis includes data preparation, visualization, model evaluation, and forecasts.
The ATM data are downloaded from my public GitHub repository. Cash amounts are recorded in hundreds of dollars.
atm_url <- paste0(
"https://raw.githubusercontent.com/UR-71/",
"DATA624_Project1/main/ATM624Data.xlsx"
)
atm_file <- tempfile(fileext = ".xlsx")
download.file(
url = atm_url,
destfile = atm_file,
mode = "wb",
quiet = TRUE
)
atm_raw <- readxl::read_excel(atm_file)
head(atm_raw)
## # A tibble: 6 × 3
## DATE ATM Cash
## <dbl> <chr> <dbl>
## 1 39934 ATM1 96
## 2 39934 ATM2 107
## 3 39935 ATM1 82
## 4 39935 ATM2 89
## 5 39936 ATM1 85
## 6 39936 ATM2 90
dim(atm_raw)
## [1] 1474 3
Excel stores dates as numbers. These are converted to calendar dates before analysis.
atm_data <- atm_raw
atm_data$DATE <- as.Date(atm_data$DATE, origin = "1899-12-30")
head(atm_data)
## # A tibble: 6 × 3
## DATE ATM Cash
## <date> <chr> <dbl>
## 1 2009-05-01 ATM1 96
## 2 2009-05-01 ATM2 107
## 3 2009-05-02 ATM1 82
## 4 2009-05-02 ATM2 89
## 5 2009-05-03 ATM1 85
## 6 2009-05-03 ATM2 90
range(atm_data$DATE)
## [1] "2009-05-01" "2010-05-14"
Before fitting models, I check for missing values and count the observations for each ATM.
colSums(is.na(atm_data))
## DATE ATM Cash
## 0 14 19
table(atm_data$ATM, useNA = "ifany")
##
## ATM1 ATM2 ATM3 ATM4 <NA>
## 365 365 365 365 14
atm_data[is.na(atm_data$ATM) | is.na(atm_data$Cash), ]
## # A tibble: 19 × 3
## DATE ATM Cash
## <date> <chr> <dbl>
## 1 2009-06-13 ATM1 NA
## 2 2009-06-16 ATM1 NA
## 3 2009-06-18 ATM2 NA
## 4 2009-06-22 ATM1 NA
## 5 2009-06-24 ATM2 NA
## 6 2010-05-01 <NA> NA
## 7 2010-05-02 <NA> NA
## 8 2010-05-03 <NA> NA
## 9 2010-05-04 <NA> NA
## 10 2010-05-05 <NA> NA
## 11 2010-05-06 <NA> NA
## 12 2010-05-07 <NA> NA
## 13 2010-05-08 <NA> NA
## 14 2010-05-09 <NA> NA
## 15 2010-05-10 <NA> NA
## 16 2010-05-11 <NA> NA
## 17 2010-05-12 <NA> NA
## 18 2010-05-13 <NA> NA
## 19 2010-05-14 <NA> NA
Fourteen rows contain dates but no ATM identifier or withdrawal amount. I exclude these rows from the historical data. The five missing withdrawal amounts for identified ATMs remain for further examination.
atm_history <- atm_data[!is.na(atm_data$ATM), ]
atm_history <- atm_history[
order(atm_history$ATM, atm_history$DATE),
]
dim(atm_history)
## [1] 1460 3
colSums(is.na(atm_history))
## DATE ATM Cash
## 0 0 5
range(atm_history$DATE)
## [1] "2009-05-01" "2010-04-30"
sum(duplicated(atm_history[c("ATM", "DATE")]))
## [1] 0
I plot each ATM separately because withdrawal levels and patterns may differ across machines. Cash remains in hundreds of dollars. Missing observations appear as gaps.
old_par <- par(mfrow = c(2, 2), mar = c(4, 4, 3, 1))
for (atm_name in sort(unique(atm_history$ATM))) {
atm_subset <- atm_history[atm_history$ATM == atm_name, ]
plot(
atm_subset$DATE,
atm_subset$Cash,
type = "l",
main = atm_name,
xlab = "Date",
ylab = "Cash (hundreds of dollars)",
col = "steelblue",
lwd = 1
)
}
par(old_par)
ATM3 shows a long period of zero withdrawals followed by recent activity. ATM4 contains a large isolated spike. I inspect these observations before deciding whether special treatment is appropriate. Neither pattern alone establishes a recording error.
# Display every positive withdrawal for ATM3.
atm3_positive <- atm_history[
atm_history$ATM == "ATM3" &
!is.na(atm_history$Cash) &
atm_history$Cash > 0,
]
atm3_positive
## # A tibble: 3 × 3
## DATE ATM Cash
## <date> <chr> <dbl>
## 1 2010-04-28 ATM3 96
## 2 2010-04-29 ATM3 82
## 3 2010-04-30 ATM3 85
# Display the largest recorded withdrawal for ATM4.
atm4_data <- atm_history[atm_history$ATM == "ATM4", ]
atm4_data[which.max(atm4_data$Cash), ]
## # A tibble: 1 × 3
## DATE ATM Cash
## <date> <chr> <dbl>
## 1 2010-02-09 ATM4 10920.
I inspect observations around each missing withdrawal before choosing how to fill the gaps.
missing_rows <- atm_history[is.na(atm_history$Cash), ]
for (i in seq_len(nrow(missing_rows))) {
this_atm <- missing_rows$ATM[i]
this_date <- missing_rows$DATE[i]
cat(
"\n", this_atm, "— missing on",
format(this_date), "\n"
)
nearby <- atm_history[
atm_history$ATM == this_atm &
atm_history$DATE >= this_date - 3 &
atm_history$DATE <= this_date + 3,
]
print(nearby)
}
##
## ATM1 — missing on 2009-06-13
## # A tibble: 7 × 3
## DATE ATM Cash
## <date> <chr> <dbl>
## 1 2009-06-10 ATM1 81
## 2 2009-06-11 ATM1 16
## 3 2009-06-12 ATM1 142
## 4 2009-06-13 ATM1 NA
## 5 2009-06-14 ATM1 120
## 6 2009-06-15 ATM1 106
## 7 2009-06-16 ATM1 NA
##
## ATM1 — missing on 2009-06-16
## # A tibble: 7 × 3
## DATE ATM Cash
## <date> <chr> <dbl>
## 1 2009-06-13 ATM1 NA
## 2 2009-06-14 ATM1 120
## 3 2009-06-15 ATM1 106
## 4 2009-06-16 ATM1 NA
## 5 2009-06-17 ATM1 108
## 6 2009-06-18 ATM1 21
## 7 2009-06-19 ATM1 140
##
## ATM1 — missing on 2009-06-22
## # A tibble: 7 × 3
## DATE ATM Cash
## <date> <chr> <dbl>
## 1 2009-06-19 ATM1 140
## 2 2009-06-20 ATM1 110
## 3 2009-06-21 ATM1 115
## 4 2009-06-22 ATM1 NA
## 5 2009-06-23 ATM1 108
## 6 2009-06-24 ATM1 66
## 7 2009-06-25 ATM1 13
##
## ATM2 — missing on 2009-06-18
## # A tibble: 7 × 3
## DATE ATM Cash
## <date> <chr> <dbl>
## 1 2009-06-15 ATM2 66
## 2 2009-06-16 ATM2 82
## 3 2009-06-17 ATM2 24
## 4 2009-06-18 ATM2 NA
## 5 2009-06-19 ATM2 134
## 6 2009-06-20 ATM2 95
## 7 2009-06-21 ATM2 82
##
## ATM2 — missing on 2009-06-24
## # A tibble: 7 × 3
## DATE ATM Cash
## <date> <chr> <dbl>
## 1 2009-06-21 ATM2 82
## 2 2009-06-22 ATM2 90
## 3 2009-06-23 ATM2 99
## 4 2009-06-24 ATM2 NA
## 5 2009-06-25 ATM2 3
## 6 2009-06-26 ATM2 117
## 7 2009-06-27 ATM2 53
The five missing withdrawal amounts are estimated using the median of
observed withdrawals for the same ATM and weekday within 28 days on
either side. Original observations are preserved in
atm_history. ATM3’s zeros and ATM4’s large spike remain
unchanged.
atm_clean <- atm_history
imputation_log <- atm_history[
is.na(atm_history$Cash),
c("DATE", "ATM", "Cash")
]
names(imputation_log)[3] <- "Imputed_Cash"
for (i in seq_len(nrow(imputation_log))) {
this_atm <- imputation_log$ATM[i]
this_date <- imputation_log$DATE[i]
days_apart <- as.integer(atm_history$DATE - this_date)
donors <- atm_history$Cash[
atm_history$ATM == this_atm &
!is.na(atm_history$Cash) &
abs(days_apart) <= 28 &
days_apart %% 7 == 0
]
stopifnot(length(donors) > 0)
replacement <- median(donors)
target <- which(
atm_clean$ATM == this_atm &
atm_clean$DATE == this_date
)
atm_clean$Cash[target] <- replacement
imputation_log$Imputed_Cash[i] <- replacement
}
imputation_log
## # A tibble: 5 × 3
## DATE ATM Imputed_Cash
## <date> <chr> <dbl>
## 1 2009-06-13 ATM1 99
## 2 2009-06-16 ATM1 109
## 3 2009-06-22 ATM1 96.5
## 4 2009-06-18 ATM2 7
## 5 2009-06-24 ATM2 25.5
colSums(is.na(atm_clean))
## DATE ATM Cash
## 0 0 0
I compare average withdrawals by weekday for ATM1 and ATM2. This helps assess whether a weekly seasonal model could be useful. ATM3 and ATM4 will receive separate consideration because of their unusual patterns.
weekday_labels <- c(
"Monday", "Tuesday", "Wednesday", "Thursday",
"Friday", "Saturday", "Sunday"
)
weekday_data <- atm_clean[
atm_clean$ATM %in% c("ATM1", "ATM2"),
]
weekday_data$Weekday <- factor(
as.integer(format(weekday_data$DATE, "%u")),
levels = 1:7,
labels = weekday_labels
)
weekday_summary <- aggregate(
Cash ~ ATM + Weekday,
data = weekday_data,
FUN = mean
)
weekday_summary <- weekday_summary[
order(weekday_summary$ATM, weekday_summary$Weekday),
]
weekday_summary$Cash <- round(weekday_summary$Cash, 2)
weekday_summary
## ATM Weekday Cash
## 1 ATM1 Monday 86.20
## 3 ATM1 Tuesday 89.94
## 5 ATM1 Wednesday 82.15
## 7 ATM1 Thursday 31.69
## 9 ATM1 Friday 98.64
## 11 ATM1 Saturday 96.65
## 13 ATM1 Sunday 102.65
## 2 ATM2 Monday 58.73
## 4 ATM2 Tuesday 73.25
## 6 ATM2 Wednesday 43.39
## 8 ATM2 Thursday 25.17
## 10 ATM2 Friday 92.02
## 12 ATM2 Saturday 75.98
## 14 ATM2 Sunday 67.15
The final 31 observed days are held out to evaluate forecast accuracy. Models will be trained using earlier observations and compared against these held-out values before forecasting May.
ATM3 is considered separately because it has only three positive observations.
atm_train <- atm_clean[
atm_clean$DATE < as.Date("2010-03-31"),
]
atm_test <- atm_clean[
atm_clean$DATE >= as.Date("2010-03-31"),
]
# Verify the dates and number of observations.
range(atm_train$DATE)
## [1] "2009-05-01" "2010-03-30"
range(atm_test$DATE)
## [1] "2010-03-31" "2010-04-30"
table(atm_train$ATM)
##
## ATM1 ATM2 ATM3 ATM4
## 334 334 334 334
table(atm_test$ATM)
##
## ATM1 ATM2 ATM3 ATM4
## 31 31 31 31
stopifnot(
all(table(atm_train$ATM) == 334),
all(table(atm_test$ATM) == 31),
max(atm_train$DATE) < min(atm_test$DATE)
)
Three baseline methods are compared using the 31-day test period. Lower MAE and RMSE indicate better forecast accuracy. Both measures are expressed in hundreds of dollars.
baseline_results <- data.frame()
for (atm_name in c("ATM1", "ATM2")) {
train_values <- atm_train$Cash[atm_train$ATM == atm_name]
test_values <- atm_test$Cash[atm_test$ATM == atm_name]
h <- length(test_values)
predictions <- list(
Mean = rep(mean(train_values), h),
Naive = rep(tail(train_values, 1), h),
Seasonal_Naive = rep(tail(train_values, 7), length.out = h)
)
for (method_name in names(predictions)) {
errors <- test_values - predictions[[method_name]]
baseline_results <- rbind(
baseline_results,
data.frame(
ATM = atm_name,
Method = method_name,
MAE = mean(abs(errors)),
RMSE = sqrt(mean(errors^2))
)
)
}
}
baseline_results$MAE <- round(baseline_results$MAE, 2)
baseline_results$RMSE <- round(baseline_results$RMSE, 2)
baseline_results
## ATM Method MAE RMSE
## 1 ATM1 Mean 20.17 32.24
## 2 ATM1 Naive 63.29 67.20
## 3 ATM1 Seasonal_Naive 13.16 15.86
## 4 ATM2 Mean 32.39 37.19
## 5 ATM2 Naive 62.71 72.91
## 6 ATM2 Seasonal_Naive 19.84 26.82
Weekly time series are created using a seasonal period of seven days. ETS and seasonal ARIMA models are fitted using only the training observations.
atm_fits <- list()
for (atm_name in c("ATM1", "ATM2")) {
train_values <- atm_train$Cash[atm_train$ATM == atm_name]
train_ts <- ts(train_values, frequency = 7)
atm_fits[[atm_name]] <- list(
ETS = forecast::ets(train_ts),
ARIMA = forecast::auto.arima(
train_ts,
seasonal = TRUE,
stepwise = FALSE,
approximation = FALSE
)
)
}
for (atm_name in names(atm_fits)) {
cat("\n", atm_name, "\n")
print(atm_fits[[atm_name]]$ETS)
print(atm_fits[[atm_name]]$ARIMA)
}
##
## ATM1
## ETS(A,N,A)
##
## Call:
## forecast::ets(y = train_ts)
##
## Smoothing parameters:
## alpha = 0.0171
## gamma = 0.3273
##
## Initial states:
## l = 78.6767
## s = -65.8788 4.1496 15.7681 4.4473 17.5111 9.4304
## 14.5724
##
## sigma: 24.8638
##
## AIC AICc BIC
## 4098.357 4099.039 4136.469
## Series: train_ts
## ARIMA(0,0,1)(1,1,1)[7]
##
## Coefficients:
## ma1 sar1 sma1
## 0.1884 0.2134 -0.7842
## s.e. 0.0583 0.0881 0.0644
##
## sigma^2 = 592.4: log likelihood = -1508.53
## AIC=3025.06 AICc=3025.18 BIC=3040.22
##
## ATM2
## ETS(A,N,A)
##
## Call:
## forecast::ets(y = train_ts)
##
## Smoothing parameters:
## alpha = 1e-04
## gamma = 0.3692
##
## Initial states:
## l = 72.892
## s = -44.9811 -25.2817 19.0331 0.1025 4.601 13.6449
## 32.8813
##
## sigma: 25.7001
##
## AIC AICc BIC
## 4120.457 4121.138 4158.568
## Series: train_ts
## ARIMA(2,0,2)(0,1,1)[7] with drift
##
## Coefficients:
## ar1 ar2 ma1 ma2 sma1 drift
## -0.4255 -0.9307 0.4622 0.8006 -0.7980 -0.074
## s.e. 0.0451 0.0387 0.0774 0.0546 0.0447 0.041
##
## sigma^2 = 605.3: log likelihood = -1511.12
## AIC=3036.24 AICc=3036.59 BIC=3062.77
ETS and ARIMA forecasts are evaluated against the same held-out observations as the baseline methods. Model selection is based on test-set accuracy rather than comparing AIC values across ETS and ARIMA.
model_results <- data.frame()
for (atm_name in c("ATM1", "ATM2")) {
actual <- atm_test$Cash[atm_test$ATM == atm_name]
for (method_name in c("ETS", "ARIMA")) {
fc <- forecast::forecast(
atm_fits[[atm_name]][[method_name]],
h = length(actual)
)
errors <- actual - as.numeric(fc$mean)
model_results <- rbind(
model_results,
data.frame(
ATM = atm_name,
Method = method_name,
MAE = mean(abs(errors)),
RMSE = sqrt(mean(errors^2))
)
)
}
}
atm_accuracy <- rbind(baseline_results, model_results)
atm_accuracy <- atm_accuracy[
order(atm_accuracy$ATM, atm_accuracy$MAE),
]
atm_accuracy$MAE <- round(atm_accuracy$MAE, 2)
atm_accuracy$RMSE <- round(atm_accuracy$RMSE, 2)
rownames(atm_accuracy) <- NULL
atm_accuracy
## ATM Method MAE RMSE
## 1 ATM1 ETS 9.86 12.74
## 2 ATM1 ARIMA 11.43 14.96
## 3 ATM1 Seasonal_Naive 13.16 15.86
## 4 ATM1 Mean 20.17 32.24
## 5 ATM1 Naive 63.29 67.20
## 6 ATM2 ETS 14.70 19.41
## 7 ATM2 ARIMA 18.67 23.21
## 8 ATM2 Seasonal_Naive 19.84 26.82
## 9 ATM2 Mean 32.39 37.19
## 10 ATM2 Naive 62.71 72.91
ETS produced the lowest errors on the 31-day test period for both ATMs. Before finalizing the models, I inspect their training residuals for remaining autocorrelation, unusual observations, and changes in variability.
forecast::checkresiduals(
atm_fits[["ATM1"]][["ETS"]],
lag = 14
)
##
## Ljung-Box test
##
## data: Residuals from ETS(A,N,A)
## Q* = 28.806, df = 14, p-value = 0.0111
##
## Model df: 0. Total lags used: 14
forecast::checkresiduals(
atm_fits[["ATM2"]][["ETS"]],
lag = 14
)
##
## Ljung-Box test
##
## data: Residuals from ETS(A,N,A)
## Q* = 35.396, df = 14, p-value = 0.001284
##
## Model df: 0. Total lags used: 14
The ETS residuals show statistically significant autocorrelation for both ATMs. I also examine the ARIMA residuals to assess whether those models capture the remaining dependence more effectively, despite their higher test errors.
forecast::checkresiduals(
atm_fits[["ATM1"]][["ARIMA"]],
lag = 14
)
##
## Ljung-Box test
##
## data: Residuals from ARIMA(0,0,1)(1,1,1)[7]
## Q* = 16.944, df = 11, p-value = 0.1095
##
## Model df: 3. Total lags used: 14
forecast::checkresiduals(
atm_fits[["ATM2"]][["ARIMA"]],
lag = 14
)
##
## Ljung-Box test
##
## data: Residuals from ARIMA(2,0,2)(0,1,1)[7] with drift
## Q* = 9.1683, df = 9, p-value = 0.4219
##
## Model df: 5. Total lags used: 14
ETS had the lowest MAE and RMSE for both ATMs on the held-out month. ARIMA had better short-lag residual diagnostics but higher forecast errors. I select ETS based on test accuracy, while acknowledging that remaining autocorrelation and extreme residuals may affect prediction interval reliability.
The selected ETS(A,N,A) structure is refitted using all 365 historical days before forecasting May 1–31, 2010.
may_dates <- seq.Date(
as.Date("2010-05-01"),
as.Date("2010-05-31"),
by = "day"
)
atm_final_fits <- list()
atm_final_forecasts <- list()
for (atm_name in c("ATM1", "ATM2")) {
full_values <- atm_clean$Cash[atm_clean$ATM == atm_name]
atm_final_fits[[atm_name]] <- forecast::ets(
ts(full_values, frequency = 7),
model = "ANA"
)
atm_final_forecasts[[atm_name]] <- forecast::forecast(
atm_final_fits[[atm_name]],
h = length(may_dates),
level = c(80, 95)
)
}
# Preview point forecasts in the original units.
atm12_preview <- data.frame(
DATE = may_dates,
ATM1 = as.numeric(atm_final_forecasts[["ATM1"]]$mean),
ATM2 = as.numeric(atm_final_forecasts[["ATM2"]]$mean)
)
head(atm12_preview, 7)
## DATE ATM1 ATM2
## 1 2010-05-01 86.980573 68.388716
## 2 2010-05-02 100.682676 74.228857
## 3 2010-05-03 73.070856 11.075955
## 4 2010-05-04 5.635037 2.105405
## 5 2010-05-05 100.078226 101.661085
## 6 2010-05-06 79.373916 92.444079
## 7 2010-05-07 85.539134 69.117172
The plots show the final eight weeks of observed withdrawals followed by May forecasts. Shaded areas represent model-based 80% and 95% prediction intervals. These intervals should be interpreted cautiously because the ETS residuals retained some autocorrelation.
for (atm_name in c("ATM1", "ATM2")) {
history <- atm_clean[atm_clean$ATM == atm_name, ]
history <- tail(history, 56)
fc <- atm_final_forecasts[[atm_name]]
lower95 <- as.numeric(fc$lower[, "95%"])
upper95 <- as.numeric(fc$upper[, "95%"])
lower80 <- as.numeric(fc$lower[, "80%"])
upper80 <- as.numeric(fc$upper[, "80%"])
plot(
history$DATE,
history$Cash,
type = "l",
xlim = range(c(history$DATE, may_dates)),
ylim = range(c(history$Cash, lower95, upper95)),
main = paste(atm_name, "— May 2010 Forecast"),
xlab = "Date",
ylab = "Cash (hundreds of dollars)",
lwd = 1.5
)
polygon(
c(may_dates, rev(may_dates)),
c(lower95, rev(upper95)),
col = "#DBEAFE",
border = NA
)
polygon(
c(may_dates, rev(may_dates)),
c(lower80, rev(upper80)),
col = "#93C5FD",
border = NA
)
lines(may_dates, as.numeric(fc$mean), col = "#1D4ED8", lwd = 2)
abline(v = as.Date("2010-05-01"), lty = 2, col = "gray50")
legend(
"topleft",
legend = c("Observed", "Forecast", "80% interval", "95% interval"),
col = c("black", "#1D4ED8", "#93C5FD", "#DBEAFE"),
lty = c(1, 1, NA, NA),
lwd = c(1.5, 2, NA, NA),
pch = c(NA, NA, 15, 15),
bty = "n",
cex = 0.8
)
}
## Forecast ATM3
ATM3 recorded zero withdrawals until its final three observed days. The reason for this change is unknown. Assuming the recent activity continues throughout May, I use the average of those three positive withdrawals as the daily forecast.
This is a judgment-based forecast supported by very limited data. A seasonal pattern and reliable prediction intervals cannot be estimated from only three active days. The original zero observations remain unchanged.
atm3_active <- atm_clean$Cash[
atm_clean$ATM == "ATM3" & atm_clean$Cash > 0
]
stopifnot(length(atm3_active) == 3)
atm3_daily_mean <- mean(atm3_active)
atm3_forecast <- data.frame(
DATE = may_dates,
ATM = "ATM3",
Forecast_Hundreds = rep(atm3_daily_mean, length(may_dates))
)
data.frame(
Active_Days = length(atm3_active),
Daily_Forecast_Dollars = round(atm3_daily_mean * 100, 2),
May_Total_Dollars = round(
sum(atm3_forecast$Forecast_Hundreds) * 100, 2
)
)
## Active_Days Daily_Forecast_Dollars May_Total_Dollars
## 1 3 8766.67 271766.7
ATM4 contains an unusually large withdrawal on February 9, 2010. Since there is no evidence that this observation is incorrect, it is retained in this initial comparison.
Mean, naïve, seasonal naïve, ETS, and ARIMA forecasts are evaluated against the same 31-day test period used for ATM1 and ATM2.
atm4_train_values <- atm_train$Cash[atm_train$ATM == "ATM4"]
atm4_actual <- atm_test$Cash[atm_test$ATM == "ATM4"]
atm4_train_ts <- ts(atm4_train_values, frequency = 7)
atm4_h <- length(atm4_actual)
atm4_fits <- list(
ETS = forecast::ets(atm4_train_ts),
ARIMA = forecast::auto.arima(
atm4_train_ts,
seasonal = TRUE,
stepwise = FALSE,
approximation = FALSE
)
)
atm4_predictions <- list(
Mean = rep(mean(atm4_train_values), atm4_h),
Naive = rep(tail(atm4_train_values, 1), atm4_h),
Seasonal_Naive = rep(
tail(atm4_train_values, 7),
length.out = atm4_h
),
ETS = as.numeric(
forecast::forecast(atm4_fits$ETS, h = atm4_h)$mean
),
ARIMA = as.numeric(
forecast::forecast(atm4_fits$ARIMA, h = atm4_h)$mean
)
)
atm4_accuracy <- do.call(
rbind,
lapply(names(atm4_predictions), function(method_name) {
errors <- atm4_actual - atm4_predictions[[method_name]]
data.frame(
Method = method_name,
MAE = mean(abs(errors)),
RMSE = sqrt(mean(errors^2))
)
})
)
atm4_accuracy <- atm4_accuracy[order(atm4_accuracy$MAE), ]
rownames(atm4_accuracy) <- NULL
atm4_accuracy$MAE <- round(atm4_accuracy$MAE, 2)
atm4_accuracy$RMSE <- round(atm4_accuracy$RMSE, 2)
atm4_accuracy
## Method MAE RMSE
## 1 ARIMA 241.42 288.72
## 2 Mean 241.42 288.72
## 3 Seasonal_Naive 260.18 329.58
## 4 ETS 319.62 404.41
## 5 Naive 378.19 466.06
ARIMA and the historical mean produced the lowest test errors at the displayed precision. I inspect the selected ARIMA specification and residuals before choosing the final forecasting method.
print(atm4_fits$ARIMA)
## Series: atm4_train_ts
## ARIMA(0,0,0) with non-zero mean
##
## Coefficients:
## mean
## 481.0634
## s.e. 36.8722
##
## sigma^2 = 455456: log likelihood = -2649.28
## AIC=5302.55 AICc=5302.59 BIC=5310.18
# Check whether ARIMA produces the same forecasts as the mean.
max(abs(
atm4_predictions$ARIMA - atm4_predictions$Mean
))
## [1] 1.705303e-13
forecast::checkresiduals(
atm4_fits$ARIMA,
lag = 14
)
##
## Ljung-Box test
##
## data: Residuals from ARIMA(0,0,0) with non-zero mean
## Q* = 4.2935, df = 14, p-value = 0.9934
##
## Model df: 0. Total lags used: 14
The selected ARIMA model is equivalent to forecasting the historical mean. I compare this mean with and without the largest observation to assess its influence. Excluding that observation here is only a sensitivity check, not a claim that it is incorrect.
atm4_values <- atm_clean$Cash[atm_clean$ATM == "ATM4"]
atm4_largest <- which.max(atm4_values)
atm4_sensitivity <- data.frame(
Scenario = c(
"All observations retained",
"Largest observation excluded"
),
Daily_Mean_Dollars = c(
mean(atm4_values),
mean(atm4_values[-atm4_largest])
) * 100
)
atm4_sensitivity$May_Total_Dollars <-
atm4_sensitivity$Daily_Mean_Dollars * length(may_dates)
atm4_sensitivity$Daily_Mean_Dollars <- round(
atm4_sensitivity$Daily_Mean_Dollars, 2
)
atm4_sensitivity$May_Total_Dollars <- round(
atm4_sensitivity$May_Total_Dollars, 2
)
atm4_sensitivity
## Scenario Daily_Mean_Dollars May_Total_Dollars
## 1 All observations retained 47404.33 1469534
## 2 Largest observation excluded 44534.63 1380574
The mean forecast tied with automatic ARIMA for the lowest test errors. Automatic ARIMA selected an ARIMA(0,0,0) model with a mean, confirming that the two approaches are equivalent here.
I retain all recorded withdrawals, including the unusually large observation, and estimate the final daily forecast using the full historical mean. Excluding the largest observation would reduce the forecast by approximately 6.4% relative to the excluded-observation estimate.
The large spike and skewed residuals make normal-theory prediction intervals potentially unreliable.
atm4_daily_mean <- mean(atm4_values)
atm4_forecast <- data.frame(
DATE = may_dates,
ATM = "ATM4",
Forecast_Hundreds = rep(atm4_daily_mean, length(may_dates))
)
data.frame(
Daily_Forecast_Dollars = round(atm4_daily_mean * 100, 2),
May_Total_Dollars = round(
sum(atm4_forecast$Forecast_Hundreds) * 100, 2
)
)
## Daily_Forecast_Dollars May_Total_Dollars
## 1 47404.33 1469534
The export contains one forecast per ATM per day for May 2010: 124 rows in total. Forecasts are provided in both the original hundreds-of-dollars units and dollars.
atm_may_forecasts <- rbind(
data.frame(
DATE = may_dates,
ATM = "ATM1",
Forecast_Hundreds = as.numeric(
atm_final_forecasts[["ATM1"]]$mean
)
),
data.frame(
DATE = may_dates,
ATM = "ATM2",
Forecast_Hundreds = as.numeric(
atm_final_forecasts[["ATM2"]]$mean
)
),
atm3_forecast,
atm4_forecast
)
atm_may_forecasts <- atm_may_forecasts[
order(atm_may_forecasts$ATM, atm_may_forecasts$DATE),
]
rownames(atm_may_forecasts) <- NULL
atm_may_forecasts$Forecast_Dollars <- round(
atm_may_forecasts$Forecast_Hundreds * 100, 2
)
stopifnot(
nrow(atm_may_forecasts) == 124,
all(table(atm_may_forecasts$ATM) == 31),
!anyDuplicated(atm_may_forecasts[c("ATM", "DATE")]),
all(is.finite(atm_may_forecasts$Forecast_Hundreds)),
all(atm_may_forecasts$Forecast_Hundreds >= 0)
)
write.csv(
atm_may_forecasts,
file = "ATM_May_2010_forecasts.csv",
row.names = FALSE
)
# Monthly totals calculated before rounding daily forecasts.
atm_monthly_totals <- aggregate(
Forecast_Hundreds ~ ATM,
data = atm_may_forecasts,
FUN = sum
)
atm_monthly_totals$May_Total_Dollars <- round(
atm_monthly_totals$Forecast_Hundreds * 100, 2
)
atm_monthly_totals[c("ATM", "May_Total_Dollars")]
## ATM May_Total_Dollars
## 1 ATM1 238617.6
## 2 ATM2 182977.9
## 3 ATM3 271766.7
## 4 ATM4 1469534.4
# Show where the CSV was saved.
normalizePath("ATM_May_2010_forecasts.csv")
## [1] "/Users/ummayrukiya/Desktop/ATM_May_2010_forecasts.csv"
ETS produced the lowest test MAE and RMSE for ATM1 and ATM2, outperforming mean, naïve, seasonal naïve, and ARIMA forecasts. The selected ETS models were refitted using all historical observations to forecast May. Their residuals retained some autocorrelation, so prediction intervals should be interpreted cautiously.
ATM3 had only three positive observations. Its forecast uses their average, conditional on continued activity throughout May. This assumption cannot be reliably validated with the available data.
For ATM4, automatic ARIMA selected a model equivalent to the historical mean. This approach tied for the lowest test errors. The unusually large February withdrawal was retained because it could not be confirmed as an error. A sensitivity analysis showed that excluding it would lower the daily forecast from approximately $47,404 to $44,535.
These forecasts estimate withdrawals, not required replenishment amounts. Operational cash planning would also require information about existing balances, refill schedules, machine capacity, and acceptable cash-shortage risk.
The dataset contains monthly residential electricity consumption from January 1998 through December 2013. Consumption is measured in kilowatt-hours (KWH). The goal is to forecast each month of 2014.
power_url <- paste0(
"https://raw.githubusercontent.com/UR-71/",
"DATA624_Project1/main/",
"ResidentialCustomerForecastLoad-624.xlsx"
)
power_file <- tempfile(fileext = ".xlsx")
download.file(
url = power_url,
destfile = power_file,
mode = "wb",
quiet = TRUE
)
power_raw <- readxl::read_excel(power_file)
head(power_raw)
## # A tibble: 6 × 3
## CaseSequence `YYYY-MMM` KWH
## <dbl> <chr> <dbl>
## 1 733 1998-Jan 6862583
## 2 734 1998-Feb 5838198
## 3 735 1998-Mar 5420658
## 4 736 1998-Apr 5010364
## 5 737 1998-May 4665377
## 6 738 1998-Jun 6467147
dim(power_raw)
## [1] 192 3
str(power_raw)
## tibble [192 × 3] (S3: tbl_df/tbl/data.frame)
## $ CaseSequence: num [1:192] 733 734 735 736 737 738 739 740 741 742 ...
## $ YYYY-MMM : chr [1:192] "1998-Jan" "1998-Feb" "1998-Mar" "1998-Apr" ...
## $ KWH : num [1:192] 6862583 5838198 5420658 5010364 4665377 ...
Month labels are converted to the first day of each month. I then check that the observations form a complete monthly sequence and inspect any missing consumption values.
power_data <- as.data.frame(power_raw)
# Match English month abbreviations without relying on system locale.
month_labels <- power_data[["YYYY-MMM"]]
month_numbers <- match(
substr(month_labels, 6, 8),
month.abb
)
stopifnot(!anyNA(month_numbers))
power_data$DATE <- as.Date(
sprintf(
"%s-%02d-01",
substr(month_labels, 1, 4),
month_numbers
)
)
power_data <- power_data[order(power_data$DATE), ]
expected_months <- seq.Date(
as.Date("1998-01-01"),
as.Date("2013-12-01"),
by = "month"
)
range(power_data$DATE)
## [1] "1998-01-01" "2013-12-01"
colSums(is.na(power_data))
## CaseSequence YYYY-MMM KWH DATE
## 0 0 1 0
sum(duplicated(power_data$DATE))
## [1] 0
# TRUE means every expected month appears in the correct order.
identical(power_data$DATE, expected_months)
## [1] TRUE
# Display any missing consumption observations.
power_data[
is.na(power_data$KWH),
c("DATE", "KWH")
]
## DATE KWH
## 129 2008-09-01 NA
The time plot helps identify trend, seasonal variation, and unusual observations. The missing consumption value is left unchanged at this stage.
plot(
power_data$DATE,
power_data$KWH,
type = "l",
col = "steelblue",
lwd = 1.5,
main = "Monthly Residential Electricity Consumption",
xlab = "Year",
ylab = "Electricity consumption (KWH)"
)
# Identify the missing month.
power_data[
is.na(power_data$KWH),
c("DATE", "KWH"),
drop = FALSE
]
## DATE KWH
## 129 2008-09-01 NA
# Inspect the five lowest observed values.
observed_power <- power_data[
!is.na(power_data$KWH),
c("DATE", "KWH")
]
head(
observed_power[order(observed_power$KWH), ],
5
)
## DATE KWH
## 151 2010-07-01 770523
## 95 2005-11-01 4313019
## 24 1999-12-01 4419229
## 17 1999-05-01 4426794
## 23 1999-11-01 4436269
September 2008 is missing, and July 2010 is unusually low. I compare these observations with the same calendar months in nearby years before deciding how to handle them.
power_year <- as.integer(format(power_data$DATE, "%Y"))
power_month <- as.integer(format(power_data$DATE, "%m"))
# Compare September observations around the missing month.
power_data[
power_month == 9 & power_year >= 2006 & power_year <= 2010,
c("DATE", "KWH"),
drop = FALSE
]
## DATE KWH
## 105 2006-09-01 7296355
## 117 2007-09-01 7666970
## 129 2008-09-01 NA
## 141 2009-09-01 7583146
## 153 2010-09-01 7819472
# Compare July observations around the unusually low value.
power_data[
power_month == 7 & power_year >= 2008 & power_year <= 2012,
c("DATE", "KWH"),
drop = FALSE
]
## DATE KWH
## 127 2008-07-01 7643987
## 139 2009-07-01 7713260
## 151 2010-07-01 770523
## 163 2011-07-01 10093343
## 175 2012-07-01 8886851
September 2008 is estimated using the average consumption for September in the preceding and following years. This preserves the annual seasonal comparison. The original data remain unchanged.
The unusually low July 2010 observation is retained for the initial analysis because its cause is unknown.
power_clean <- power_data
missing_index <- which(
power_clean$DATE == as.Date("2008-09-01")
)
donor_values <- power_data$KWH[
power_data$DATE %in% as.Date(
c("2007-09-01", "2009-09-01")
)
]
stopifnot(
length(missing_index) == 1,
is.na(power_clean$KWH[missing_index]),
length(donor_values) == 2,
all(is.finite(donor_values))
)
power_clean$KWH[missing_index] <- mean(donor_values)
power_clean[
missing_index,
c("DATE", "KWH"),
drop = FALSE
]
## DATE KWH
## 129 2008-09-01 7625058
sum(is.na(power_clean$KWH))
## [1] 0
Observations from January 1998 through December 2012 are used for training. The 12 months of 2013 are held out to compare forecast accuracy before forecasting 2014.
The September 2008 replacement uses only observations within the training period.
power_train <- power_clean[
power_clean$DATE < as.Date("2013-01-01"),
]
power_test <- power_clean[
power_clean$DATE >= as.Date("2013-01-01"),
]
power_train_ts <- ts(
power_train$KWH,
start = c(1998, 1),
frequency = 12
)
range(power_train$DATE)
## [1] "1998-01-01" "2012-12-01"
range(power_test$DATE)
## [1] "2013-01-01" "2013-12-01"
c(
Training_Months = nrow(power_train),
Test_Months = nrow(power_test)
)
## Training_Months Test_Months
## 180 12
stopifnot(
nrow(power_train) == 180,
nrow(power_test) == 12,
max(power_train$DATE) < min(power_test$DATE),
!anyNA(power_train$KWH),
!anyNA(power_test$KWH)
)
Mean, naïve, and seasonal naïve forecasts are evaluated against the 12 months of 2013. Seasonal naïve uses consumption from the corresponding month of 2012.
Lower MAE and RMSE indicate better accuracy. Both measures are expressed in KWH.
power_actual <- power_test$KWH
power_h <- length(power_actual)
power_baseline_predictions <- list(
Mean = rep(mean(power_train$KWH), power_h),
Naive = rep(tail(power_train$KWH, 1), power_h),
Seasonal_Naive = rep(
tail(power_train$KWH, 12),
length.out = power_h
)
)
power_baseline_results <- do.call(
rbind,
lapply(names(power_baseline_predictions), function(method_name) {
errors <- power_actual -
power_baseline_predictions[[method_name]]
data.frame(
Method = method_name,
MAE = mean(abs(errors)),
RMSE = sqrt(mean(errors^2))
)
})
)
power_baseline_results <- power_baseline_results[
order(power_baseline_results$RMSE),
]
rownames(power_baseline_results) <- NULL
print(
power_baseline_results,
digits = 8
)
## Method MAE RMSE
## 1 Seasonal_Naive 618605.58 1035537.6
## 2 Naive 1501420.00 1861178.6
## 3 Mean 1544514.52 1961764.4
ETS and seasonal ARIMA are fitted using only the training period. Their 12-month forecasts are compared with the observed consumption for 2013 and the baseline forecasts.
power_fits <- list(
ETS = forecast::ets(power_train_ts),
ARIMA = forecast::auto.arima(
power_train_ts,
seasonal = TRUE,
stepwise = FALSE,
approximation = FALSE
)
)
power_model_results <- do.call(
rbind,
lapply(names(power_fits), function(method_name) {
fc <- forecast::forecast(
power_fits[[method_name]],
h = power_h
)
errors <- power_actual - as.numeric(fc$mean)
data.frame(
Method = method_name,
MAE = mean(abs(errors)),
RMSE = sqrt(mean(errors^2))
)
})
)
power_accuracy <- rbind(
power_baseline_results,
power_model_results
)
power_accuracy <- power_accuracy[
order(power_accuracy$RMSE),
]
rownames(power_accuracy) <- NULL
print(power_accuracy, digits = 8)
## Method MAE RMSE
## 1 Seasonal_Naive 618605.58 1035537.6
## 2 ETS 691602.39 1068359.1
## 3 ARIMA 1006845.76 1541077.2
## 4 Naive 1501420.00 1861178.6
## 5 Mean 1544514.52 1961764.4
print(power_fits$ETS)
## ETS(M,N,M)
##
## Call:
## forecast::ets(y = power_train_ts)
##
## Smoothing parameters:
## alpha = 0.1482
## gamma = 1e-04
##
## Initial states:
## l = 6139225.6768
## s = 0.9347 0.7516 0.8776 1.1947 1.2714 1.2015
## 0.9939 0.7683 0.8063 0.9156 1.0747 1.2097
##
## sigma: 0.1171
##
## AIC AICc BIC
## 5813.678 5816.605 5861.573
print(power_fits$ARIMA)
## Series: power_train_ts
## ARIMA(1,0,0)(2,1,0)[12] with drift
##
## Coefficients:
## ar1 sar1 sar2 drift
## 0.1942 -0.8463 -0.6236 7120.818
## s.e. 0.0762 0.0679 0.0769 2803.504
##
## sigma^2 = 6.784e+11: log likelihood = -2532.6
## AIC=5075.19 AICc=5075.56 BIC=5090.81
July 2010 is unusually low compared with surrounding years. For a sensitivity analysis only, I replace it with the average of July 2009 and July 2011, then refit ETS and ARIMA.
All replacement values come from the training period. The original training data and the 2013 test observations remain unchanged.
power_train_adjusted <- power_train
july_index <- which(
power_train_adjusted$DATE == as.Date("2010-07-01")
)
july_donors <- power_train$KWH[
power_train$DATE %in% as.Date(
c("2009-07-01", "2011-07-01")
)
]
stopifnot(
length(july_index) == 1,
length(july_donors) == 2,
all(is.finite(july_donors))
)
power_train_adjusted$KWH[july_index] <- mean(july_donors)
power_adjusted_ts <- ts(
power_train_adjusted$KWH,
start = c(1998, 1),
frequency = 12
)
power_adjusted_fits <- list(
ETS_Adjusted = forecast::ets(power_adjusted_ts),
ARIMA_Adjusted = forecast::auto.arima(
power_adjusted_ts,
seasonal = TRUE,
stepwise = FALSE,
approximation = FALSE
)
)
power_adjusted_results <- do.call(
rbind,
lapply(names(power_adjusted_fits), function(method_name) {
fc <- forecast::forecast(
power_adjusted_fits[[method_name]],
h = power_h
)
errors <- power_actual - as.numeric(fc$mean)
data.frame(
Method = method_name,
MAE = mean(abs(errors)),
RMSE = sqrt(mean(errors^2))
)
})
)
power_sensitivity_accuracy <- rbind(
power_accuracy,
power_adjusted_results
)
power_sensitivity_accuracy <- power_sensitivity_accuracy[
order(power_sensitivity_accuracy$RMSE),
]
rownames(power_sensitivity_accuracy) <- NULL
print(power_sensitivity_accuracy, digits = 8)
## Method MAE RMSE
## 1 ARIMA_Adjusted 663570.99 973296.73
## 2 Seasonal_Naive 618605.58 1035537.61
## 3 ETS_Adjusted 675490.90 1047930.44
## 4 ETS 691602.39 1068359.10
## 5 ARIMA 1006845.76 1541077.16
## 6 Naive 1501420.00 1861178.59
## 7 Mean 1544514.52 1961764.43
The adjusted ARIMA model has the lowest test RMSE, while seasonal naïve has the lowest MAE. The adjusted model depends on treating July 2010 as an exceptional observation rather than a typical seasonal value.
I inspect its residuals before deciding whether this modeling assumption is appropriate for the final forecast.
print(power_adjusted_fits$ARIMA_Adjusted)
## Series: power_adjusted_ts
## ARIMA(0,0,3)(2,1,0)[12] with drift
##
## Coefficients:
## ma1 ma2 ma3 sar1 sar2 drift
## 0.3628 0.0334 0.2336 -0.6786 -0.4243 8061.612
## s.e. 0.0762 0.0826 0.0704 0.0764 0.0812 3005.122
##
## sigma^2 = 3.402e+11: log likelihood = -2469.88
## AIC=4953.75 AICc=4954.45 BIC=4975.62
forecast::checkresiduals(
power_adjusted_fits$ARIMA_Adjusted,
lag = 24
)
##
## Ljung-Box test
##
## data: Residuals from ARIMA(0,0,3)(2,1,0)[12] with drift
## Q* = 11.534, df = 19, p-value = 0.9046
##
## Model df: 5. Total lags used: 24
The adjusted ARIMA model achieved the lowest test RMSE and satisfactory residual autocorrelation diagnostics through lag 24. Seasonal naïve achieved the lowest MAE and remains a useful benchmark.
For the primary forecast, July 2010 is treated as an exceptional
observation and replaced with the average of July 2009 and July 2011.
This is a modeling assumption, not a verified data correction. The
original observations remain preserved in power_data.
The selected ARIMA structure is refitted using the full historical period.
power_final_data <- power_clean
power_final_data$KWH[
power_final_data$DATE == as.Date("2010-07-01")
] <- power_train_adjusted$KWH[july_index]
power_final_ts <- ts(
power_final_data$KWH,
start = c(1998, 1),
frequency = 12
)
power_final_fit <- forecast::Arima(
power_final_ts,
order = c(0, 0, 3),
seasonal = list(
order = c(2, 1, 0),
period = 12
),
include.drift = TRUE,
method = "ML"
)
power_final_fc <- forecast::forecast(
power_final_fit,
h = 12,
level = c(80, 95)
)
power_2014_forecasts <- data.frame(
DATE = seq.Date(
as.Date("2014-01-01"),
by = "month",
length.out = 12
),
Forecast_KWH = as.numeric(power_final_fc$mean),
Lower80_KWH = as.numeric(power_final_fc$lower[, "80%"]),
Upper80_KWH = as.numeric(power_final_fc$upper[, "80%"]),
Lower95_KWH = as.numeric(power_final_fc$lower[, "95%"]),
Upper95_KWH = as.numeric(power_final_fc$upper[, "95%"])
)
print(power_2014_forecasts, digits = 9)
## DATE Forecast_KWH Lower80_KWH Upper80_KWH Lower95_KWH Upper95_KWH
## 1 2014-01-01 10410443.06 9608282.41 11212603.71 9183644.21 11637241.91
## 2 2014-02-01 8498503.38 7655574.64 9341432.12 7209355.12 9787651.64
## 3 2014-03-01 7250735.11 6407692.48 8093777.74 5961412.67 8540057.55
## 4 2014-04-01 6018370.77 5152810.51 6883931.02 4694610.59 7342130.94
## 5 2014-05-01 5950849.60 5085289.35 6816409.85 4627089.43 7274609.77
## 6 2014-06-01 8186803.42 7321243.17 9052363.67 6863043.25 9510563.59
## 7 2014-07-01 9406564.12 8541003.87 10272124.37 8082803.95 10730324.29
## 8 2014-08-01 9915220.53 9049660.28 10780780.79 8591460.36 11238980.70
## 9 2014-09-01 8450940.39 7585380.14 9316500.64 7127180.22 9774700.56
## 10 2014-10-01 5869716.94 5004156.68 6735277.19 4545956.77 7193477.11
## 11 2014-11-01 6126234.40 5260674.15 6991794.66 4802474.23 7449994.57
## 12 2014-12-01 8501216.10 7635655.85 9366776.35 7177455.93 9824976.27
The plot shows the final three years of recorded consumption, the ARIMA forecasts, and model-based 80% and 95% prediction intervals. The seasonal naïve benchmark repeats the corresponding monthly values from 2013.
Intervals are conditional on the model and data adjustments; they do not include uncertainty about whether July 2010 should have been adjusted.
power_recent <- tail(power_data, 36)
power_2014_benchmark <- tail(power_clean$KWH, 12)
plot(
power_recent$DATE,
power_recent$KWH,
type = "l",
xlim = range(c(power_recent$DATE, power_2014_forecasts$DATE)),
ylim = range(c(
power_recent$KWH,
power_2014_forecasts$Lower95_KWH,
power_2014_forecasts$Upper95_KWH,
power_2014_benchmark
)),
main = "Residential Electricity Consumption: 2014 Forecast",
xlab = "Year",
ylab = "Electricity consumption (KWH)",
lwd = 1.5
)
forecast_dates <- power_2014_forecasts$DATE
polygon(
c(forecast_dates, rev(forecast_dates)),
c(
power_2014_forecasts$Lower95_KWH,
rev(power_2014_forecasts$Upper95_KWH)
),
col = "#DBEAFE",
border = NA
)
polygon(
c(forecast_dates, rev(forecast_dates)),
c(
power_2014_forecasts$Lower80_KWH,
rev(power_2014_forecasts$Upper80_KWH)
),
col = "#93C5FD",
border = NA
)
lines(
forecast_dates,
power_2014_forecasts$Forecast_KWH,
col = "#1D4ED8",
lwd = 2
)
lines(
forecast_dates,
power_2014_benchmark,
col = "#B45309",
lty = 2,
lwd = 1.5
)
abline(v = as.Date("2014-01-01"), lty = 3, col = "gray50")
legend(
"topleft",
legend = c(
"Observed", "ARIMA forecast", "Seasonal naive",
"80% interval", "95% interval"
),
col = c("black", "#1D4ED8", "#B45309", "#93C5FD", "#DBEAFE"),
lty = c(1, 1, 2, NA, NA),
lwd = c(1.5, 2, 1.5, NA, NA),
pch = c(NA, NA, NA, 15, 15),
bty = "n",
cex = 0.8
)
The export contains forecasts for all 12 months of 2014, along with model-based 80% and 95% prediction intervals. A seasonal naïve benchmark is included for comparison. All values are in KWH.
power_2014_forecasts$Seasonal_Naive_KWH <-
as.numeric(power_2014_benchmark)
numeric_columns <- setdiff(
names(power_2014_forecasts),
"DATE"
)
stopifnot(
nrow(power_2014_forecasts) == 12,
!anyDuplicated(power_2014_forecasts$DATE),
all(is.finite(
as.matrix(power_2014_forecasts[numeric_columns])
)),
all(power_2014_forecasts$Forecast_KWH > 0),
all(
power_2014_forecasts$Lower95_KWH <=
power_2014_forecasts$Lower80_KWH &
power_2014_forecasts$Lower80_KWH <=
power_2014_forecasts$Forecast_KWH &
power_2014_forecasts$Forecast_KWH <=
power_2014_forecasts$Upper80_KWH &
power_2014_forecasts$Upper80_KWH <=
power_2014_forecasts$Upper95_KWH
)
)
write.csv(
power_2014_forecasts,
file = "Power_2014_forecasts.csv",
row.names = FALSE
)
data.frame(
ARIMA_Annual_Total_KWH = round(
sum(power_2014_forecasts$Forecast_KWH), 2
),
Seasonal_Naive_Annual_Total_KWH = round(
sum(power_2014_forecasts$Seasonal_Naive_KWH), 2
)
)
## ARIMA_Annual_Total_KWH Seasonal_Naive_Annual_Total_KWH
## 1 94585598 91420024
normalizePath("Power_2014_forecasts.csv")
## [1] "/Users/ummayrukiya/Desktop/Power_2014_forecasts.csv"
Residential electricity consumption shows a recurring annual seasonal pattern. September 2008 was missing and was estimated using the average of September 2007 and September 2009.
Seasonal naïve produced the lowest test MAE of 618,606 KWH. After adjusting the unusually low July 2010 observation, ARIMA produced the lowest test RMSE of 973,297 KWH. The adjusted ARIMA model was selected because RMSE places greater weight on large forecast errors.
The selected ARIMA(0,0,3)(2,1,0)[12] model with drift showed no significant residual autocorrelation through lag 24 in the training-period Ljung–Box test (p = 0.9046). It was refitted using the full history and forecasts approximately 94.59 million KWH for 2014, compared with 91.42 million KWH from seasonal naïve.
The July 2010 adjustment is a modeling assumption, not a confirmed correction. Prediction intervals are conditional on this assumption and the fitted model. Model comparisons use only one held-out year, so performance may differ in other years. The forecasts also do not explicitly incorporate weather, customer counts, or changes in electricity prices.
The two pipe datasets are imported separately because their timestamps may follow different patterns. Each will be organized into hourly observations, averaging multiple readings within the same hour, before evaluating stationarity and forecasting the following week.
water_base_url <- paste0(
"https://raw.githubusercontent.com/UR-71/",
"DATA624_Project1/main/"
)
pipe1_file <- tempfile(fileext = ".xlsx")
pipe2_file <- tempfile(fileext = ".xlsx")
download.file(
url = paste0(water_base_url, "Waterflow_Pipe1.xlsx"),
destfile = pipe1_file,
mode = "wb",
quiet = TRUE
)
download.file(
url = paste0(water_base_url, "Waterflow_Pipe2.xlsx"),
destfile = pipe2_file,
mode = "wb",
quiet = TRUE
)
pipe1_raw <- readxl::read_excel(pipe1_file)
pipe2_raw <- readxl::read_excel(pipe2_file)
cat("\nPIPE 1\n")
##
## PIPE 1
print(head(pipe1_raw))
## # A tibble: 6 × 2
## `Date Time` WaterFlow
## <dbl> <dbl>
## 1 42300. 23.4
## 2 42300. 28.0
## 3 42300. 23.1
## 4 42300. 30.0
## 5 42300. 6.00
## 6 42300. 15.9
str(pipe1_raw)
## tibble [1,000 × 2] (S3: tbl_df/tbl/data.frame)
## $ Date Time: num [1:1000] 42300 42300 42300 42300 42300 ...
## $ WaterFlow: num [1:1000] 23.4 28 23.1 30 6 ...
cat("\nPIPE 2\n")
##
## PIPE 2
print(head(pipe2_raw))
## # A tibble: 6 × 2
## `Date Time` WaterFlow
## <dbl> <dbl>
## 1 42300. 18.8
## 2 42300. 43.1
## 3 42300. 38.0
## 4 42300. 36.1
## 5 42300. 31.9
## 6 42300. 28.2
str(pipe2_raw)
## tibble [1,000 × 2] (S3: tbl_df/tbl/data.frame)
## $ Date Time: num [1:1000] 42300 42300 42300 42300 42300 ...
## $ WaterFlow: num [1:1000] 18.8 43.1 38 36.1 31.9 ...
Excel timestamps are converted from days since December 30, 1899, into date-time values. UTC is used only as a clock convention; the actual recording timezone is unknown.
pipe1_data <- data.frame(
DateTime = as.POSIXct(
pipe1_raw[["Date Time"]] * 86400,
origin = "1899-12-30",
tz = "UTC"
),
WaterFlow = pipe1_raw$WaterFlow
)
pipe2_data <- data.frame(
DateTime = as.POSIXct(
pipe2_raw[["Date Time"]] * 86400,
origin = "1899-12-30",
tz = "UTC"
),
WaterFlow = pipe2_raw$WaterFlow
)
pipe1_data <- pipe1_data[order(pipe1_data$DateTime), ]
pipe2_data <- pipe2_data[order(pipe2_data$DateTime), ]
cat("\nPIPE 1\n")
##
## PIPE 1
print(head(pipe1_data))
## DateTime WaterFlow
## 1 2015-10-23 00:24:06 23.369599
## 2 2015-10-23 00:40:02 28.002881
## 3 2015-10-23 00:53:51 23.065895
## 4 2015-10-23 00:55:40 29.972809
## 5 2015-10-23 01:19:17 5.997953
## 6 2015-10-23 01:23:58 15.935223
print(range(pipe1_data$DateTime))
## [1] "2015-10-23 00:24:06 UTC" "2015-11-01 23:35:43 UTC"
print(colSums(is.na(pipe1_data)))
## DateTime WaterFlow
## 0 0
cat("\nPIPE 2\n")
##
## PIPE 2
print(head(pipe2_data))
## DateTime WaterFlow
## 1 2015-10-23 01:00:00 18.81079
## 2 2015-10-23 01:59:59 43.08703
## 3 2015-10-23 03:00:00 37.98770
## 4 2015-10-23 04:00:00 36.12038
## 5 2015-10-23 04:59:59 31.85126
## 6 2015-10-23 06:00:00 28.23809
print(range(pipe2_data$DateTime))
## [1] "2015-10-23 01:00:00 UTC" "2015-12-03 16:00:00 UTC"
print(colSums(is.na(pipe2_data)))
## DateTime WaterFlow
## 0 0
Timestamps are rounded to the nearest second to remove Excel conversion precision artifacts, then assigned to the beginning of their hour. Multiple readings within an hour are averaged.
A complete hourly sequence is created for each pipe. Hours without readings remain missing, not zero.
make_hourly <- function(pipe_data) {
rounded_seconds <- round(as.numeric(pipe_data$DateTime))
hour_start <- as.POSIXct(
floor(rounded_seconds / 3600) * 3600,
origin = "1970-01-01",
tz = "UTC"
)
hourly_mean <- aggregate(
list(WaterFlow = pipe_data$WaterFlow),
by = list(DateTime = hour_start),
FUN = mean
)
hourly_count <- aggregate(
list(Readings = rep(1L, nrow(pipe_data))),
by = list(DateTime = hour_start),
FUN = sum
)
hourly_observed <- merge(
hourly_mean, hourly_count,
by = "DateTime"
)
complete_hours <- data.frame(
DateTime = seq(
min(hour_start),
max(hour_start),
by = "hour"
)
)
result <- merge(
complete_hours, hourly_observed,
by = "DateTime",
all.x = TRUE,
sort = TRUE
)
result$Readings[is.na(result$Readings)] <- 0L
result
}
pipe1_hourly <- make_hourly(pipe1_data)
pipe2_hourly <- make_hourly(pipe2_data)
for (pipe_name in c("Pipe1", "Pipe2")) {
hourly <- if (pipe_name == "Pipe1") pipe1_hourly else pipe2_hourly
cat("\n", pipe_name, "\n")
print(head(hourly))
print(c(
Hours = nrow(hourly),
Missing_Hours = sum(is.na(hourly$WaterFlow)),
Total_Readings = sum(hourly$Readings)
))
}
##
## Pipe1
## DateTime WaterFlow Readings
## 1 2015-10-23 00:00:00 26.10280 4
## 2 2015-10-23 01:00:00 18.85202 5
## 3 2015-10-23 02:00:00 15.15857 2
## 4 2015-10-23 03:00:00 23.07886 5
## 5 2015-10-23 04:00:00 15.48219 2
## 6 2015-10-23 05:00:00 22.72539 9
## Hours Missing_Hours Total_Readings
## 240 4 1000
##
## Pipe2
## DateTime WaterFlow Readings
## 1 2015-10-23 01:00:00 18.81079 1
## 2 2015-10-23 02:00:00 43.08703 1
## 3 2015-10-23 03:00:00 37.98770 1
## 4 2015-10-23 04:00:00 36.12038 1
## 5 2015-10-23 05:00:00 31.85126 1
## 6 2015-10-23 06:00:00 28.23809 1
## Hours Missing_Hours Total_Readings
## 1000 0 1000
Each pipe is plotted over its own recording period. Missing hours appear as gaps. Waterflow units were not specified in the assignment.
plot(
pipe1_hourly$DateTime,
pipe1_hourly$WaterFlow,
type = "l",
col = "steelblue",
main = "Pipe 1 — Hourly Mean Waterflow",
xlab = "Date",
ylab = "Waterflow (source units)"
)
plot(
pipe2_hourly$DateTime,
pipe2_hourly$WaterFlow,
type = "l",
col = "steelblue",
main = "Pipe 2 — Hourly Waterflow",
xlab = "Date",
ylab = "Waterflow (source units)"
)
# Display the missing hours for Pipe 1.
pipe1_hourly[
is.na(pipe1_hourly$WaterFlow),
,
drop = FALSE
]
## DateTime WaterFlow Readings
## 114 2015-10-27 17:00:00 NA 0
## 121 2015-10-28 00:00:00 NA 0
## 222 2015-11-01 05:00:00 NA 0
## 226 2015-11-01 09:00:00 NA 0
Four hourly slots contain no readings. For modeling, these gaps are filled by linear interpolation between surrounding observed hourly means. These estimates are not actual measurements.
Pipe 2 requires no imputation. Missing Pipe 1 hours will be excluded from forecast-accuracy calculations if they fall in the test period.
pipe1_clean <- pipe1_hourly
pipe2_clean <- pipe2_hourly
missing_hours <- is.na(pipe1_hourly$WaterFlow)
observed_hours <- !missing_hours
pipe1_clean$WaterFlow <- approx(
x = as.numeric(pipe1_hourly$DateTime[observed_hours]),
y = pipe1_hourly$WaterFlow[observed_hours],
xout = as.numeric(pipe1_hourly$DateTime),
method = "linear",
rule = 1
)$y
pipe1_imputation_log <- data.frame(
DateTime = pipe1_hourly$DateTime[missing_hours],
Estimated_WaterFlow = pipe1_clean$WaterFlow[missing_hours]
)
print(pipe1_imputation_log)
## DateTime Estimated_WaterFlow
## 1 2015-10-27 17:00:00 22.49977
## 2 2015-10-28 00:00:00 18.79852
## 3 2015-11-01 05:00:00 16.83043
## 4 2015-11-01 09:00:00 15.38992
c(
Pipe1_Missing = sum(is.na(pipe1_clean$WaterFlow)),
Pipe2_Missing = sum(is.na(pipe2_clean$WaterFlow))
)
## Pipe1_Missing Pipe2_Missing
## 0 0
stopifnot(
all(is.finite(pipe1_clean$WaterFlow)),
all(is.finite(pipe2_clean$WaterFlow))
)
The KPSS-based differencing check assesses whether differencing is needed to stabilize the series’ level. Results are interpreted alongside the time plots and autocorrelation plots.
Pipe 1 includes four interpolated values, which may slightly affect these diagnostics.
waterflow_ndiffs <- data.frame(
Pipe = c("Pipe1", "Pipe2"),
Recommended_Differences = c(
forecast::ndiffs(
pipe1_clean$WaterFlow,
alpha = 0.05,
test = "kpss"
),
forecast::ndiffs(
pipe2_clean$WaterFlow,
alpha = 0.05,
test = "kpss"
)
)
)
print(waterflow_ndiffs)
## Pipe Recommended_Differences
## 1 Pipe1 0
## 2 Pipe2 0
acf(
pipe1_clean$WaterFlow,
lag.max = 48,
main = "Pipe 1 — Hourly Waterflow ACF"
)
acf(
pipe2_clean$WaterFlow,
lag.max = 48,
main = "Pipe 2 — Hourly Waterflow ACF"
)
## Create Waterflow Training and Test Sets
The final 168 hourly slots of each pipe are reserved for evaluation, matching the required one-week forecast horizon.
The split uses the original hourly data. Any training gaps will be filled using training observations only, and missing test values will not be scored. This prevents future observations from influencing training-data preparation.
water_h <- 168L
pipe1_cutoff <- nrow(pipe1_hourly) - water_h
pipe2_cutoff <- nrow(pipe2_hourly) - water_h
pipe1_train <- pipe1_hourly[seq_len(pipe1_cutoff), ]
pipe1_test <- pipe1_hourly[
seq.int(pipe1_cutoff + 1L, nrow(pipe1_hourly)),
]
pipe2_train <- pipe2_hourly[seq_len(pipe2_cutoff), ]
pipe2_test <- pipe2_hourly[
seq.int(pipe2_cutoff + 1L, nrow(pipe2_hourly)),
]
water_split_summary <- data.frame(
Pipe = c("Pipe1", "Pipe2"),
Training_Hours = c(nrow(pipe1_train), nrow(pipe2_train)),
Test_Hours = c(nrow(pipe1_test), nrow(pipe2_test)),
Missing_Training = c(
sum(is.na(pipe1_train$WaterFlow)),
sum(is.na(pipe2_train$WaterFlow))
),
Observed_Test_Hours = c(
sum(!is.na(pipe1_test$WaterFlow)),
sum(!is.na(pipe2_test$WaterFlow))
)
)
print(water_split_summary)
## Pipe Training_Hours Test_Hours Missing_Training Observed_Test_Hours
## 1 Pipe1 72 168 0 164
## 2 Pipe2 832 168 0 168
stopifnot(
nrow(pipe1_test) == water_h,
nrow(pipe2_test) == water_h,
max(pipe1_train$DateTime) < min(pipe1_test$DateTime),
max(pipe2_train$DateTime) < min(pipe2_test$DateTime)
)
Baseline forecasts are evaluated over the held-out week. Only observed test values are scored; interpolated test values are not used.
Daily seasonal naïve repeats the final 24 training hours. It is included to test whether repeating a daily pattern improves accuracy.
water_training_sets <- list(
Pipe1 = pipe1_train,
Pipe2 = pipe2_train
)
water_test_sets <- list(
Pipe1 = pipe1_test,
Pipe2 = pipe2_test
)
water_baseline_results <- data.frame()
for (pipe_name in names(water_training_sets)) {
train_values <- water_training_sets[[pipe_name]]$WaterFlow
actual <- water_test_sets[[pipe_name]]$WaterFlow
observed <- !is.na(actual)
stopifnot(
all(is.finite(train_values)),
sum(observed) > 0
)
predictions <- list(
Mean = rep(mean(train_values), length(actual)),
Naive = rep(tail(train_values, 1), length(actual)),
Daily_Seasonal_Naive = rep(
tail(train_values, 24),
length.out = length(actual)
)
)
for (method_name in names(predictions)) {
errors <- actual[observed] -
predictions[[method_name]][observed]
water_baseline_results <- rbind(
water_baseline_results,
data.frame(
Pipe = pipe_name,
Method = method_name,
Scored_Hours = sum(observed),
MAE = mean(abs(errors)),
RMSE = sqrt(mean(errors^2))
)
)
}
}
water_baseline_results <- water_baseline_results[
order(water_baseline_results$Pipe, water_baseline_results$RMSE),
]
rownames(water_baseline_results) <- NULL
print(water_baseline_results, digits = 6)
## Pipe Method Scored_Hours MAE RMSE
## 1 Pipe1 Naive 164 3.25800 4.18726
## 2 Pipe1 Mean 164 3.27470 4.21544
## 3 Pipe1 Daily_Seasonal_Naive 164 4.75913 6.04837
## 4 Pipe2 Mean 168 13.74957 16.73292
## 5 Pipe2 Naive 168 13.76086 16.73593
## 6 Pipe2 Daily_Seasonal_Naive 168 17.60502 21.57002
Simple exponential smoothing and nonseasonal ARIMA are compared with the baselines. No seasonal pattern is imposed because the plots did not show clear repeating seasonality.
All models use training observations only, and missing test hours are excluded from scoring.
water_model_fits <- list()
water_model_results <- data.frame()
for (pipe_name in names(water_training_sets)) {
train_values <- water_training_sets[[pipe_name]]$WaterFlow
actual <- water_test_sets[[pipe_name]]$WaterFlow
observed <- !is.na(actual)
train_ts <- ts(train_values, frequency = 1)
fits <- list(
SES = forecast::ets(train_ts, model = "ANN"),
ARIMA = forecast::auto.arima(
train_ts,
seasonal = FALSE,
stepwise = FALSE,
approximation = FALSE
)
)
water_model_fits[[pipe_name]] <- fits
for (method_name in names(fits)) {
fc <- forecast::forecast(
fits[[method_name]],
h = length(actual)
)
errors <- actual[observed] -
as.numeric(fc$mean)[observed]
water_model_results <- rbind(
water_model_results,
data.frame(
Pipe = pipe_name,
Method = method_name,
Scored_Hours = sum(observed),
MAE = mean(abs(errors)),
RMSE = sqrt(mean(errors^2))
)
)
}
}
water_accuracy <- rbind(
water_baseline_results,
water_model_results
)
water_accuracy <- water_accuracy[
order(water_accuracy$Pipe, water_accuracy$RMSE),
]
rownames(water_accuracy) <- NULL
print(water_accuracy, digits = 6)
## Pipe Method Scored_Hours MAE RMSE
## 1 Pipe1 Naive 164 3.25800 4.18726
## 2 Pipe1 Mean 164 3.27470 4.21544
## 3 Pipe1 ARIMA 164 3.27470 4.21544
## 4 Pipe1 SES 164 3.27477 4.21553
## 5 Pipe1 Daily_Seasonal_Naive 164 4.75913 6.04837
## 6 Pipe2 Mean 168 13.74957 16.73292
## 7 Pipe2 SES 168 13.74979 16.73297
## 8 Pipe2 Naive 168 13.76086 16.73593
## 9 Pipe2 ARIMA 168 13.84148 16.82356
## 10 Pipe2 Daily_Seasonal_Naive 168 17.60502 21.57002
Naïve achieved the lowest test MAE and RMSE for Pipe 1. Mean achieved the lowest values for Pipe 2. The differences between several methods were small, and Pipe 1’s evaluation used only 72 training hours, so the rankings should be interpreted cautiously.
Residual diagnostics assess whether the selected methods leave detectable autocorrelation.
water_selected_training <- list(
Pipe1 = forecast::naive(
ts(pipe1_train$WaterFlow),
h = water_h
),
Pipe2 = forecast::meanf(
ts(pipe2_train$WaterFlow),
h = water_h
)
)
cat("\nPIPE 1 — NAIVE\n")
##
## PIPE 1 — NAIVE
forecast::checkresiduals(
water_selected_training$Pipe1,
lag = 24
)
##
## Ljung-Box test
##
## data: Residuals from Naive method
## Q* = 61.1, df = 24, p-value = 4.46e-05
##
## Model df: 0. Total lags used: 24
cat("\nPIPE 2 — MEAN\n")
##
## PIPE 2 — MEAN
forecast::checkresiduals(
water_selected_training$Pipe2,
lag = 24
)
##
## Ljung-Box test
##
## data: Residuals from Mean
## Q* = 34.342, df = 24, p-value = 0.07877
##
## Model df: 0. Total lags used: 24
The naïve method narrowly outperformed the mean on the held-out week, but its residuals showed strong negative lag-1 autocorrelation. This suggests that treating each latest fluctuation as a persistent level may be inappropriate.
The mean forecast had only slightly higher test error. I examine its training residuals as an alternative.
pipe1_mean_training <- forecast::meanf(
ts(pipe1_train$WaterFlow),
h = water_h
)
forecast::checkresiduals(
pipe1_mean_training,
lag = 24
)
##
## Ljung-Box test
##
## data: Residuals from Mean
## Q* = 22.161, df = 24, p-value = 0.5697
##
## Model df: 0. Total lags used: 24
Mean forecasts are selected for both pipes. Pipe 2’s mean had the lowest test RMSE. For Pipe 1, the mean’s RMSE was only about 0.7% above naïve, while its residual diagnostics were substantially better.
These forecasts estimate the expected hourly level, not individual random fluctuations. Prediction intervals assume independent, approximately normally distributed errors and do not account for imputation uncertainty or varying numbers of readings per hour.
water_final_fc <- list(
Pipe1 = forecast::meanf(
ts(pipe1_clean$WaterFlow),
h = 168,
level = c(80, 95)
),
Pipe2 = forecast::meanf(
ts(pipe2_clean$WaterFlow),
h = 168,
level = c(80, 95)
)
)
water_full_data <- list(
Pipe1 = pipe1_clean,
Pipe2 = pipe2_clean
)
water_forecast_tables <- list()
for (pipe_name in names(water_final_fc)) {
fc <- water_final_fc[[pipe_name]]
history <- water_full_data[[pipe_name]]
future_hours <- seq(
from = max(history$DateTime) + 3600,
by = "hour",
length.out = 168
)
water_forecast_tables[[pipe_name]] <- data.frame(
DateTime = future_hours,
Pipe = pipe_name,
Forecast = as.numeric(fc$mean),
Lower80 = as.numeric(fc$lower[, "80%"]),
Upper80 = as.numeric(fc$upper[, "80%"]),
Lower95 = as.numeric(fc$lower[, "95%"]),
Upper95 = as.numeric(fc$upper[, "95%"])
)
}
for (pipe_name in names(water_forecast_tables)) {
cat("\n", pipe_name, "\n")
print(head(water_forecast_tables[[pipe_name]], 3))
print(range(water_forecast_tables[[pipe_name]]$DateTime))
}
##
## Pipe1
## DateTime Pipe Forecast Lower80 Upper80 Lower95 Upper95
## 1 2015-11-02 00:00:00 Pipe1 19.86744 14.42901 25.30587 11.53085 28.20402
## 2 2015-11-02 01:00:00 Pipe1 19.86744 14.42901 25.30587 11.53085 28.20402
## 3 2015-11-02 02:00:00 Pipe1 19.86744 14.42901 25.30587 11.53085 28.20402
## [1] "2015-11-02 00:00:00 UTC" "2015-11-08 23:00:00 UTC"
##
## Pipe2
## DateTime Pipe Forecast Lower80 Upper80 Lower95 Upper95
## 1 2015-12-03 17:00:00 Pipe2 39.55551 18.96364 60.14738 8.045606 71.06541
## 2 2015-12-03 18:00:00 Pipe2 39.55551 18.96364 60.14738 8.045606 71.06541
## 3 2015-12-03 19:00:00 Pipe2 39.55551 18.96364 60.14738 8.045606 71.06541
## [1] "2015-12-03 17:00:00 UTC" "2015-12-10 16:00:00 UTC"
Each plot shows the final 72 historical hours followed by 168 forecast hours. Shaded regions represent model-based prediction intervals for individual future hourly values.
for (pipe_name in names(water_forecast_tables)) {
history <- tail(water_full_data[[pipe_name]], 72)
fc <- water_forecast_tables[[pipe_name]]
plot(
history$DateTime,
history$WaterFlow,
type = "l",
xlim = range(c(history$DateTime, fc$DateTime)),
ylim = range(c(
history$WaterFlow, fc$Lower95, fc$Upper95
)),
main = paste(pipe_name, "— One-Week Waterflow Forecast"),
xlab = "Date",
ylab = "Waterflow (source units)",
lwd = 1.2
)
polygon(
c(fc$DateTime, rev(fc$DateTime)),
c(fc$Lower95, rev(fc$Upper95)),
col = "#DBEAFE",
border = NA
)
polygon(
c(fc$DateTime, rev(fc$DateTime)),
c(fc$Lower80, rev(fc$Upper80)),
col = "#93C5FD",
border = NA
)
lines(
fc$DateTime, fc$Forecast,
col = "#1D4ED8", lwd = 2
)
abline(
v = as.numeric(fc$DateTime[1]),
lty = 2,
col = "gray50"
)
legend(
"topleft",
legend = c(
"History (gaps interpolated)",
"Mean forecast", "80% interval", "95% interval"
),
col = c("black", "#1D4ED8", "#93C5FD", "#DBEAFE"),
lty = c(1, 1, NA, NA),
lwd = c(1.2, 2, NA, NA),
pch = c(NA, NA, 15, 15),
bty = "n",
cex = 0.8
)
}
## Export Waterflow Results
Each forecast file contains 168 hourly predictions with 80% and 95% prediction intervals. The hourly-data export preserves observed hourly means, reading counts, and flags identifying interpolated hours.
Timestamps follow the UTC clock convention used throughout this analysis; the original recording timezone is unknown.
for (pipe_name in names(water_forecast_tables)) {
fc <- water_forecast_tables[[pipe_name]]
stopifnot(
nrow(fc) == 168,
!anyDuplicated(fc$DateTime),
all(diff(as.numeric(fc$DateTime)) == 3600),
all(is.finite(as.matrix(fc[, 3:7]))),
all(
fc$Lower95 <= fc$Lower80 &
fc$Lower80 <= fc$Forecast &
fc$Forecast <= fc$Upper80 &
fc$Upper80 <= fc$Upper95
)
)
export_fc <- fc
export_fc$DateTime <- format(
fc$DateTime,
format = "%Y-%m-%d %H:%M:%S",
tz = "UTC"
)
write.csv(
export_fc,
file = paste0(pipe_name, "_168_hour_forecasts.csv"),
row.names = FALSE
)
}
hourly_export <- rbind(
data.frame(
Pipe = "Pipe1",
DateTime = pipe1_hourly$DateTime,
Observed_Hourly_Mean = pipe1_hourly$WaterFlow,
Modeling_Value = pipe1_clean$WaterFlow,
Readings = pipe1_hourly$Readings,
Imputed = is.na(pipe1_hourly$WaterFlow)
),
data.frame(
Pipe = "Pipe2",
DateTime = pipe2_hourly$DateTime,
Observed_Hourly_Mean = pipe2_hourly$WaterFlow,
Modeling_Value = pipe2_clean$WaterFlow,
Readings = pipe2_hourly$Readings,
Imputed = is.na(pipe2_hourly$WaterFlow)
)
)
hourly_export$DateTime <- format(
hourly_export$DateTime,
format = "%Y-%m-%d %H:%M:%S",
tz = "UTC"
)
write.csv(
hourly_export,
file = "Waterflow_Hourly_Data.csv",
row.names = FALSE,
na = ""
)
output_files <- c(
"Pipe1_168_hour_forecasts.csv",
"Pipe2_168_hour_forecasts.csv",
"Waterflow_Hourly_Data.csv"
)
print(data.frame(
File = output_files,
Saved = file.exists(output_files)
))
## File Saved
## 1 Pipe1_168_hour_forecasts.csv TRUE
## 2 Pipe2_168_hour_forecasts.csv TRUE
## 3 Waterflow_Hourly_Data.csv TRUE
Pipe 1’s irregular readings were averaged into 240 hourly slots. Four hours had no readings and were interpolated for the final modeling dataset. Pipe 2 contained 1,000 consecutive hourly observations and required no imputation.
The KPSS-based check recommended no differencing for either series. Together with the time plots and mostly small autocorrelations, this supports treating the series as approximately stationary over their observed periods, rather than proving stationarity.
Mean forecasts were selected for both pipes. For Pipe 1, naïve had slightly lower test RMSE, but its residuals showed strong negative lag-1 autocorrelation. The mean’s RMSE was only about 0.7% higher, and its training residuals showed no significant autocorrelation through lag 24 (p = 0.5697). For Pipe 2, the mean had the lowest test RMSE; its corresponding diagnostic p-value was 0.07877.
The expected hourly waterflow is approximately 19.87 for Pipe 1 and 39.56 for Pipe 2, in the source units. Each forecast covers 168 hours beyond that pipe’s own endpoint. These forecasts estimate stable expected levels, not individual random fluctuations.
Pipe 1’s short history limits confidence: only 72 hours were available for training when holding out one week. Missing test hours were excluded from scoring. Prediction intervals do not account for imputation uncertainty or varying reading counts within each hour. Recording units and the actual timezone were unspecified.