library(tidyverse)
potholes <- read_csv("Street_Pothole_Work_Orders_-_Closed_(Dataset)_20260918.csv")
NOAA_precip_data <- read_csv("NOAA_precip_data.csv") %>%
mutate(month = ym(DATE)) %>%
select(month,snow = SNOW)
p <- potholes %>%
transmute(closed = floor_date(ymd_hms(RptClosed), unit = "months"),
reported = floor_date(ymd_hms(RptDate), unit = "months"),
delay = difftime(ymd_hms(RptClosed),ymd_hms(RptDate),units = "days")) %>%
pivot_longer(-delay,
names_to = "type",
values_to = "month") %>%
group_by(type,month) %>%
summarise(n = n(),
avg_delay = mean(delay)) %>%
left_join(NOAA_precip_data, by = "month") %>%
ungroup() %>%
mutate(mamdani = year(month) == 2026,
month_fct = month(month,label = T),
# Set January 2026 as reference for intercept estimate
month_n = interval(ymd("20260101"), month) %/% months(1)) %>%
group_by(type) %>%
mutate(,
snow_lag1 = lag(snow, 1),
snow_lag2 = lag(snow, 2),
snow_lag3 = lag(snow, 3),
snow_lag4 = lag(snow, 4),
snow_lag5 = lag(snow, 5),
snow_lag6 = lag(snow, 6)) %>%
filter(month >= ymd("20160101"))
ggplot(p,
aes(x=month,y=n,color=type)) +
#facet_wrap(~Boro,ncol = 1) +
geom_line(linewidth = 1) +
labs(x="Month",y="Number of potholes reported/fixed")
ggplot(filter(p,type=="reported"),
aes(x=month,y=avg_delay)) +
#facet_wrap(~Boro,ncol = 1) +
geom_line(linewidth = 1) +
labs(x="Month",y="Average days to fix")
Snowfall
ggplot(filter(p,type=="reported"),
aes(x=month,y=snow)) +
#facet_wrap(~Boro,ncol = 1) +
geom_line(linewidth = 1) +
labs(x="Month",y="Total snowfall during month")
#Get effect size at average month
contrasts(p$month_fct) <- contr.sum(nlevels(p$month_fct))
pothole_mod <- lm(n ~ mamdani + month_n + month_fct,
data = p[p$type == "closed",])
summary(pothole_mod)
##
## Call:
## lm(formula = n ~ mamdani + month_n + month_fct, data = p[p$type ==
## "closed", ])
##
## Residuals:
## Min 1Q Median 3Q Max
## -1369.87 -218.85 37.88 216.19 1649.95
##
## Coefficients:
## Estimate Std. Error t value Pr(>|t|)
## (Intercept) 1140.30 86.37 13.202 < 2e-16 ***
## mamdaniTRUE 956.95 192.00 4.984 2.24e-06 ***
## month_n -11.95 1.24 -9.634 < 2e-16 ***
## month_fct1 162.94 135.68 1.201 0.232269
## month_fct2 335.89 135.62 2.477 0.014731 *
## month_fct3 1384.39 135.58 10.211 < 2e-16 ***
## month_fct4 492.98 135.55 3.637 0.000416 ***
## month_fct5 119.02 135.52 0.878 0.381671
## month_fct6 452.88 135.51 3.342 0.001125 **
## month_fct7 -380.90 135.51 -2.811 0.005818 **
## month_fct8 -572.58 135.52 -4.225 4.83e-05 ***
## month_fct9 -652.53 141.84 -4.601 1.10e-05 ***
## month_fct10 -674.68 141.87 -4.756 5.83e-06 ***
## month_fct11 -718.73 141.91 -5.065 1.59e-06 ***
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## Residual standard error: 468.4 on 114 degrees of freedom
## Multiple R-squared: 0.7438, Adjusted R-squared: 0.7146
## F-statistic: 25.46 on 13 and 114 DF, p-value: < 2.2e-16
pothole_mod_glm <- glm(n ~ mamdani + month_n + month_fct,
family = "poisson",
data = p[p$type == "closed",])
summary(pothole_mod_glm)
##
## Call:
## glm(formula = n ~ mamdani + month_n + month_fct, family = "poisson",
## data = p[p$type == "closed", ])
##
## Coefficients:
## Estimate Std. Error z value Pr(>|z|)
## (Intercept) 7.057e+00 4.795e-03 1471.62 <2e-16 ***
## mamdaniTRUE 5.282e-01 9.006e-03 58.65 <2e-16 ***
## month_n -6.479e-03 6.229e-05 -104.00 <2e-16 ***
## month_fct1 1.371e-01 6.393e-03 21.44 <2e-16 ***
## month_fct2 2.172e-01 6.187e-03 35.10 <2e-16 ***
## month_fct3 6.000e-01 5.270e-03 113.86 <2e-16 ***
## month_fct4 2.872e-01 6.031e-03 47.62 <2e-16 ***
## month_fct5 1.185e-01 6.511e-03 18.20 <2e-16 ***
## month_fct6 2.731e-01 6.100e-03 44.78 <2e-16 ***
## month_fct7 -1.695e-01 7.455e-03 -22.74 <2e-16 ***
## month_fct8 -3.098e-01 7.976e-03 -38.84 <2e-16 ***
## month_fct9 -3.831e-01 8.694e-03 -44.06 <2e-16 ***
## month_fct10 -4.059e-01 8.815e-03 -46.05 <2e-16 ***
## month_fct11 -4.495e-01 9.026e-03 -49.80 <2e-16 ***
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## (Dispersion parameter for poisson family taken to be 1)
##
## Null deviance: 47664 on 127 degrees of freedom
## Residual deviance: 9612 on 114 degrees of freedom
## AIC: 10828
##
## Number of Fisher Scoring iterations: 4
(exp(coef(pothole_mod_glm)["mamdaniTRUE"]) - 1) * 100
## mamdaniTRUE
## 69.58471
pothole_mod_glm_snow <- glm(n ~ mamdani + month_n + month_fct + snow + snow_lag1 + snow_lag2 + snow_lag3 + snow_lag4 + snow_lag5 + snow_lag6,
family = "poisson",
data = p[p$type == "closed",])
summary(pothole_mod_glm_snow)
##
## Call:
## glm(formula = n ~ mamdani + month_n + month_fct + snow + snow_lag1 +
## snow_lag2 + snow_lag3 + snow_lag4 + snow_lag5 + snow_lag6,
## family = "poisson", data = p[p$type == "closed", ])
##
## Coefficients:
## Estimate Std. Error z value Pr(>|z|)
## (Intercept) 7.041e+00 4.857e-03 1449.549 < 2e-16 ***
## mamdaniTRUE 2.714e-01 1.174e-02 23.120 < 2e-16 ***
## month_n -5.186e-03 7.462e-05 -69.500 < 2e-16 ***
## month_fct1 3.320e-01 7.537e-03 44.041 < 2e-16 ***
## month_fct2 3.411e-01 7.794e-03 43.772 < 2e-16 ***
## month_fct3 5.477e-01 7.208e-03 75.979 < 2e-16 ***
## month_fct4 9.803e-02 7.978e-03 12.287 < 2e-16 ***
## month_fct5 -1.149e-01 8.571e-03 -13.401 < 2e-16 ***
## month_fct6 8.387e-02 8.331e-03 10.067 < 2e-16 ***
## month_fct7 -2.531e-01 9.455e-03 -26.770 < 2e-16 ***
## month_fct8 -2.838e-01 9.050e-03 -31.358 < 2e-16 ***
## month_fct9 -3.102e-01 9.132e-03 -33.969 < 2e-16 ***
## month_fct10 -3.108e-01 9.314e-03 -33.373 < 2e-16 ***
## month_fct11 -3.448e-01 9.514e-03 -36.244 < 2e-16 ***
## snow -1.088e-02 5.124e-04 -21.233 < 2e-16 ***
## snow_lag1 3.019e-03 4.595e-04 6.571 5.00e-11 ***
## snow_lag2 1.453e-02 4.433e-04 32.772 < 2e-16 ***
## snow_lag3 1.384e-02 4.893e-04 28.278 < 2e-16 ***
## snow_lag4 1.575e-02 5.067e-04 31.080 < 2e-16 ***
## snow_lag5 1.145e-02 5.449e-04 21.021 < 2e-16 ***
## snow_lag6 4.886e-03 6.177e-04 7.910 2.57e-15 ***
## ---
## Signif. codes: 0 '***' 0.001 '**' 0.01 '*' 0.05 '.' 0.1 ' ' 1
##
## (Dispersion parameter for poisson family taken to be 1)
##
## Null deviance: 47664.5 on 127 degrees of freedom
## Residual deviance: 6486.6 on 107 degrees of freedom
## AIC: 7717
##
## Number of Fisher Scoring iterations: 4
(exp(coef(pothole_mod_glm_snow)["mamdaniTRUE"]) - 1) * 100
## mamdaniTRUE
## 31.18487