Loading Necessary Packages

library(tidyverse)

Reading in the Data

MC311 <- read_csv("MC311_SR.csv")

##Gsub

# Eliminating spaces and hyphens and renaming Attached Solution Topic
colnames(MC311) <- gsub(" ", "_", colnames(MC311))
colnames(MC311) <- gsub("-", "", colnames(MC311))

MC311 <- MC311 %>%
  rename(AST = `Attached_Solution_(Topic)`)

Adjusting Date Column & Adding other Time Info

MC311$Opened <- as.POSIXct(MC311$Opened, format = "%m/%d/%Y %I:%M:%S %p")

MC311$Opened <- as.Date(MC311$Opened, format = "%m/%d/%Y %I:%M:%S %p")

MC311 <- MC311 %>% mutate(Month = as.numeric(format(Opened, "%m"))) # Adding "Month" column

MC311 <- MC311 %>% mutate(Year = as.numeric(format(Opened, "%Y"))) # Adding "Year" column

Supplementing NAs

# Moving information from one column to another to supplement NAs instead of dropping them
MC311 <- MC311 %>% 
  mutate(Area = ifelse(is.na(Area), Department, Area),
         SubArea = ifelse(is.na(SubArea), Department, SubArea),
         AST = ifelse(is.na(AST), Department, AST)) 

Macroanalysis of Call Volume by Year and Department

MC311 %>% filter(Department %in%c("DOT", "DPS", "Non-MCG") & Source == "Phone") %>%
  group_by(Department, Year) %>%
  summarize(count = n())

## `summarise()` has grouped output by 'Department'. You can override using the
## `.groups` argument.

## # A tibble: 15 × 3
## # Groups:   Department [3]
##    Department  Year count
##    <chr>      <dbl> <int>
##  1 DOT         2019 69553
##  2 DOT         2020 46990
##  3 DOT         2021 42645
##  4 DOT         2022 49258
##  5 DOT         2023 45870
##  6 DPS         2019 73697
##  7 DPS         2020 57202
##  8 DPS         2021 55335
##  9 DPS         2022 53976
## 10 DPS         2023 50043
## 11 Non-MCG     2019 44585
## 12 Non-MCG     2020 49066
## 13 Non-MCG     2021 48197
## 14 Non-MCG     2022 42153
## 15 Non-MCG     2023 42950

Aggregating Data for Phone Calls

ASTPhoneCounts <- MC311 %>%
  filter(Source == "Phone") %>% # Grouping AST (Attached Solution Topic)
  group_by(AST) %>%
  summarize(count = n()) 

ASTPhoneCounts <- ASTPhoneCounts %>%
  filter(count >= 3650)

TOPASTs <- unique(ASTPhoneCounts$AST) # selecting top ASTs (Attached Solution Topic)

MC311TopCalls <- MC311 %>%
  filter(Source == "Phone") %>% # Filtering for only those ASTs ^
  filter(AST %in% TOPASTs)

AST_MY <- MC311TopCalls %>% # Grouping ASTs by month and year, hence 'MY'
  group_by(Department, AST, Year) %>% 
  summarise(count = n()) 

Change in Call Volume for Top Phone Calls

Calls_2019 <- AST_MY %>% 
  filter(Year == 2019) %>%
  group_by(AST) %>% 
  summarize(Count2019 = sum(count))

AST_MY <- left_join(AST_MY, Calls_2019, by = "AST")

AST_MY <- AST_MY %>% 
  mutate(Count2019 = replace(Count2019, is.na(Count2019), 0),
         Change = ((count - Count2019) / Count2019) * 100)

AST_MY <- AST_MY %>% 
  mutate(Change = round(Change, 2))

Solution Topics of Interest (Phone)

ASTPhoneRows <- c(24:28, 86:90, 99:103, 123:132, 140:144, 166:170, 182:186, 206:210, 231:233, 246:250,
                  266:269, 274:278, 325:329, 369:373, 420:424, 425:429)

ASTPhoneY_Final <- AST_MY[ASTPhoneRows, ]

Visualization (Phone Requests) HEATMAP

library(plotly)

PhoneHeatmap <- plot_ly(data = ASTPhoneY_Final, x = ~Year, y = ~AST, z = ~Change, type = "heatmap", colors = colorRamp(c("red", "white", "#228B22")), 
        text = ~paste("Attached Solution (Topic): ", AST, "<br>Year: ", Year, "<br>Percent Change: ", Change, "<br>Corresponding Department: ", Department), hoverinfo = "text") %>%
  layout(title = "Percent Change of Call Volume for Attached Solution (Topics) of MC311 Phone Requests from 2019 to 2023",
         xaxis = list(title = "Year"),
         yaxis = list(title = "Attached Solution (Topic)"),
         showlegend = TRUE)

PhoneHeatmap

htmlwidgets::saveWidget(PhoneHeatmap, "PhoneRequestsHeatmap.html")

New Insights

Department of Transportation (DOT)

ASTDOT <- AST_MY %>%
  filter(AST %in% c("Ride On Complaint - Driver Behavior", "Ride On Complaint - Service", "Ride On Trip Planning"))

DOTplot <- ggplot(ASTDOT, aes(x = Year, y = count, color = AST)) +
  geom_line() + 
  labs(x = "Year",
       y = "Number of Phone Service Requests",
       color = "Solution Topic",
       title = "Total Number of 311 Phone Service Requests for MCDOT by Year and Solution Topic") +
  scale_y_continuous(breaks = seq(0, 6500, by = 500)) +
  theme(panel.grid.major.x = element_line(color = "black", linetype = "dotted"))

DOTplot

ggsave("DOT.png", plot = DOTplot, width = 10)

## Saving 10 x 5 in image

DOTCalls <- MC311 %>% filter(Source == "Phone" & Department == "DOT") %>%
  group_by(Year) %>% 
  summarize(count = n())

ggplot(DOTCalls, aes(x = Year, y = count, fill = Year)) +
  geom_bar(stat = "Identity", fill = "#4682B4") +
  labs(y = "Number of Phone Service Requests",
       title = "Total Number of 311 Phone Service Requests for MCDOT by Year") +
  scale_y_continuous(breaks = seq(0, 70000, by = 5000)) +
  geom_text(aes(label = count), vjust = -0.5, color = "black", size = 3) +
  theme_minimal()

Non-MCG

ASTNonMCG <- AST_MY %>%
  filter(AST %in% c("Land Records", "Washington DC 311", "Washington Suburban Sanitary Commission (WSSC)"))

NONMCGPlot <- ggplot(ASTNonMCG, aes(x = Year, y = count, color = AST)) +
  geom_line() + 
  labs(x = "Year",
       y = "Number of Phone Service Requests",
       color = "Solution Topic",
       title = "Total Number of Non-MCG 311 Phone Service Requests by Year and Solution Topic") +
  scale_y_continuous(breaks = seq(0, 6500, by = 500)) +
  theme(panel.grid.major.x = element_line(color = "black", linetype = "dotted"))

NONMCGPlot

NONMCGCALLS <- MC311 %>% filter(Source == "Phone" & Department == "Non-MCG") %>%
  group_by(Year) %>% 
  summarize(count = n())

ggplot(NONMCGCALLS, aes(x = Year, y = count, fill = Year)) +
  geom_bar(stat = "Identity", fill = "#4682B4") +
  labs(y = "Number of Phone Service Requests",
       title = "Total Number of Non-MCG 311 Phone Service Requests by Year") +
  scale_y_continuous(breaks = seq(0, 70000, by = 5000)) +
  geom_text(aes(label = count), vjust = -0.5, color = "black", size = 3) +
  theme_minimal()

ggsave("ASTNon-MCG.png", plot = NONMCGPlot, width = 12)

## Saving 12 x 5 in image

DPS

ASTDPS <- AST_MY %>%
  filter(AST %in% c("Schedule DPS Commercial Building Construction Related Permitting Inspections", 
                    "Renew or Apply for Fire Code Compliance, Operational, or General Fire Permits",
                    "Fire Code Compliance Inspection Request"))

ASTDPS <- ggplot(ASTDPS, aes(x = Year, y = count, color = AST)) +
  geom_line() + 
  labs(x = "Year",
       y = "Number of Phone Service Requests",
       color = "Solution Topic",
       title = "Total Number of Montgomery County DPS 311 Phone Service Requests by Year and Solution Topic") +
  scale_y_continuous(breaks = seq(0, 6500, by = 500)) +
  theme(panel.grid.major.x = element_line(color = "black", linetype = "dotted"))

ASTDPS

ggsave("ASTDPS.png", plot = ASTDPS, width = 12)

## Saving 12 x 5 in image

DPSCalls <- MC311 %>% filter(Source == "Phone" & Department == "DPS") %>%
  group_by(Year) %>% 
  summarize(count = n())

ggplot(DPSCalls, aes(x = Year, y = count, fill = Year)) +
  geom_bar(stat = "Identity", fill = "#4682B4") +
  labs(y = "Number of Phone Service Requests",
       title = "Total Number of Montgomery County DPS 311 Phone Service Requests by Year") +
  scale_y_continuous(breaks = seq(0, 75000, by = 5000)) +
  geom_text(aes(label = count), vjust = -0.5, color = "black", size = 3) +
  theme_minimal()

Forecasting Call Volume Using Facebook’s Prophet

library(prophet)
library(dygraphs)

DOT

DOT311 <- MC311 %>% filter(Source == "Phone" & Department == "DOT") %>%
  group_by(Opened) %>%
  summarize(count = n())

colnames(DOT311) <- c("ds", "y")

DOTPModel <- prophet(DOT311)

## Disabling daily seasonality. Run prophet with daily.seasonality=TRUE to override this.

DOTfuture <- make_future_dataframe(DOTPModel, periods = 365)
DOTforecast <- predict(DOTPModel, DOTfuture)

plot(DOTPModel, DOTforecast,
     xlab = "Year",
     ylab = "Number of 311 Phone Service Requests",
     main = "Prophet Forecast for MCDOT 311 Phone Service Requests")

DOT Forecast

DOT2024 <- DOTforecast %>% filter(ds >= as.Date("2024-01-01"))

## Warning: There was 1 warning in `filter()`.
## ℹ In argument: `ds >= as.Date("2024-01-01")`.
## Caused by warning in `check_tzones()`:
## ! 'tzone' attributes are inconsistent

ggplot(DOT2024, aes(x = ds)) +
  geom_line(aes(y = yhat_lower), color = "red") +
  geom_line(aes(y = yhat_upper), color = "blue")+
  geom_ribbon(aes(ymin = yhat_lower, ymax = yhat_upper)) +
  scale_x_datetime(date_breaks = "1 month", date_labels = "%b %Y") +
  theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
  theme(panel.grid.major.x = element_line(color = "black", linetype = "dotted")) +
  labs(x = "Date",
       y = "Number of Predicted MCDOT 311 Phone Service Requests",
       title = "2024 Prophet Forecast for DOT 311 Service Requests") +
  scale_color_manual(name = "Bounds", values = c("Lower Bound" = "red", "Upper Bound" = "blue")) +
  guides(fill = guide_legend(title = "95% Confidence Interval")) +
   scale_y_continuous(breaks = seq(-150, 350, by = 25))

DOT Bootstrapping

library(infer)

DOTCI <- DOTforecast %>% 
  specify(response = yhat_lower) %>%
  generate(reps = 10000, type = "bootstrap") %>%
  calculate(stat = "mean") %>%
  summarize(l = quantile(stat, 0.025),
            u = quantile(stat, 0.975))

DOTCI

## # A tibble: 1 × 2
##       l     u
##   <dbl> <dbl>
## 1  122.  129.

Non-MCG

NonMCG311 <- MC311 %>% filter(Source == "Phone" & Department == "Non-MCG") %>%
  group_by(Opened) %>%
  summarize(count = n())

colnames(NonMCG311) <- c("ds", "y")

NMCGMod <- prophet(NonMCG311)

## Disabling daily seasonality. Run prophet with daily.seasonality=TRUE to override this.

NMCGfuture <- make_future_dataframe(NMCGMod, periods = 365)
NMCGforecast <- predict(NMCGMod, NMCGfuture)

plot(NMCGMod, NMCGforecast,
     xlab = "Year",
     ylab = "Number of 311 Phone Service Requests",
     main = "Prophet Forecast for Non-MCG 311 Phone Service Requests")

Non-MCG Forecast

NMCG2024 <- NMCGforecast %>% filter(ds >= as.Date("2024-01-01"))

## Warning: There was 1 warning in `filter()`.
## ℹ In argument: `ds >= as.Date("2024-01-01")`.
## Caused by warning in `check_tzones()`:
## ! 'tzone' attributes are inconsistent

NMCG2024Plot <- ggplot(NMCG2024, aes(x = ds)) +
  geom_line(aes(y = yhat_lower), color = "red") +
  geom_line(aes(y = yhat_upper), color = "blue")+
  geom_ribbon(aes(ymin = yhat_lower, ymax = yhat_upper)) +
  scale_x_datetime(date_breaks = "1 month", date_labels = "%b %Y") +
  theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
  theme(panel.grid.major.x = element_line(color = "black", linetype = "dotted")) +
  labs(x = "Date",
       y = "Number of Predicted Non-MCG 311 Phone Service Requests",
       title = "2024 Prophet Forecast for Non-MCG 311 Service Requests") +
  scale_color_manual(name = "Bounds", values = c("Lower Bound" = "red", "Upper Bound" = "blue")) +
  guides(fill = guide_legend(title = "95% Confidence Interval")) +
   scale_y_continuous(breaks = seq(-150, 350, by = 25))

NMCG2024Plot

ggsave("NMCG2024.png", plot = NMCG2024Plot, height = 5)

## Saving 7 x 5 in image

Non-MCG Bootstrapping

NMCGCI <- NMCGforecast %>% 
  specify(response = yhat_lower) %>%
  generate(reps = 10000, type = "bootstrap") %>%
  calculate(stat = "mean") %>%
  summarize(l = quantile(stat, 0.025),
            u = quantile(stat, 0.975))

NMCGCI

## # A tibble: 1 × 2
##       l     u
##   <dbl> <dbl>
## 1  116.  121.

DPS

DPS311 <- MC311 %>% filter(Source == "Phone" & Department == "DPS") %>%
  group_by(Opened) %>%
  summarize(count = n())

colnames(DPS311) <- c("ds", "y")

DPSMod <- prophet(DPS311)

## Disabling daily seasonality. Run prophet with daily.seasonality=TRUE to override this.

DPSfuture <- make_future_dataframe(DPSMod, periods = 365)
DPSforecast <- predict(DPSMod, NMCGfuture)

plot(DPSMod, DPSforecast,
     xlab = "Year",
     ylab = "Number of 311 Phone Service Requests",
     main = "Prophet Forecast for DPS 311 Phone Service Requests")

DPS Forecast

DPS2024 <- DPSforecast %>% filter(ds >= as.Date("2024-01-01"))

## Warning: There was 1 warning in `filter()`.
## ℹ In argument: `ds >= as.Date("2024-01-01")`.
## Caused by warning in `check_tzones()`:
## ! 'tzone' attributes are inconsistent

DPS2024Plot <- ggplot(DPS2024, aes(x = ds)) +
  geom_line(aes(y = yhat_lower), color = "red") +
  geom_line(aes(y = yhat_upper), color = "blue")+
  geom_ribbon(aes(ymin = yhat_lower, ymax = yhat_upper)) +
  scale_x_datetime(date_breaks = "1 month", date_labels = "%b %Y") +
  theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
  theme(panel.grid.major.x = element_line(color = "black", linetype = "dotted")) +
  labs(x = "Date",
       y = "Number of Predicted DPS 311 Phone Service Requests",
       title = "2024 Prophet Forecast for DPS 311 Service Requests") +
  scale_color_manual(name = "Bounds", values = c("Lower Bound" = "red", "Upper Bound" = "blue")) +
  guides(fill = guide_legend(title = "95% Confidence Interval")) +
   scale_y_continuous(breaks = seq(-150, 350, by = 25))

DPS2024Plot

### DPS Bootstrapping

DPSCI <- DPSforecast %>% 
  specify(response = yhat_lower) %>%
  generate(reps = 10000, type = "bootstrap") %>%
  calculate(stat = "mean") %>%
  summarize(l = quantile(stat, 0.025),
            u = quantile(stat, 0.975))

DPSCI

## # A tibble: 1 × 2
##       l     u
##   <dbl> <dbl>
## 1  159.  166.