Code
library(tidyverse)
library(lubridate)
library(ggplot2)
library(scales)
library(here)
library(knitr)
library(kableExtra)
# Set seed for reproducibility
set.seed(42)2 Data
2.1 Data Processing
2.1.1 Weather Data Preprocessing
Code
# Set file paths
weather_temp_path <- here("data", "monthly_nyc_weather.csv")
weather_rain_path <- here("data", "monthly_nyc_rain.csv")
# Function to preprocess weather data
preprocess_weather_data <- function() {
# Load temperature and rainfall data
temp_df <- read_csv(weather_temp_path)
rain_df <- read_csv(weather_rain_path)
# Convert Month format to date
temp_df <- temp_df %>%
mutate(Date = as.Date(paste0(Month, "01"), format = "%Y%m%d"))
rain_df <- rain_df %>%
mutate(Date = as.Date(paste0(Month, "01"), format = "%Y%m%d"))
# Rename columns for clarity
temp_df <- temp_df %>%
rename(Temperature = Value, TempAnomaly = Anomaly)
rain_df <- rain_df %>%
rename(Rainfall = Value, RainAnomaly = Anomaly)
# Merge temperature and rainfall data
weather_df <- temp_df %>%
inner_join(rain_df, by = c("Date"))
# Extract date components for analysis
weather_df <- weather_df %>%
mutate(
Year = year(Date),
Month = month(Date),
# Create season feature
Season = case_when(
Month %in% c(12, 1, 2) ~ "Winter",
Month %in% c(3, 4, 5) ~ "Spring",
Month %in% c(6, 7, 8) ~ "Summer",
TRUE ~ "Fall"
)
)
return(weather_df)
}
# Process weather data
weather_df <- preprocess_weather_data()
# Display the first few rows
head(weather_df)# A tibble: 6 × 10
Month.x Temperature TempAnomaly Date Month.y Rainfall RainAnomaly Year
<dbl> <dbl> <dbl> <date> <dbl> <dbl> <dbl> <dbl>
1 201301 35.1 1.3 2013-01-01 201301 2.76 -0.7 2013
2 201302 33.9 -2.1 2013-02-01 201302 4.25 0.48 2013
3 201303 40.1 -1.7 2013-03-01 201303 2.89 -0.93 2013
4 201304 53 -0.2 2013-04-01 201304 1.31 -2.63 2013
5 201305 62.8 -0.8 2013-05-01 201305 8 3.45 2013
6 201306 72.7 0.5 2013-06-01 201306 10.1 5.5 2013
# ℹ 2 more variables: Month <dbl>, Season <chr>2.1.2 Traffic Data Preprocessing
Code
traffic_path <- here("data", "Automated_Traffic_Volume_Counts_20250505.csv")
# Function to preprocess traffic data
preprocess_traffic_data <- function(sample_size = NULL) {
# Load traffic data with sampling if needed due to size
if (!is.null(sample_size)) {
traffic_df <- read_csv(traffic_path, n_max = sample_size)
} else {
traffic_df <- read_csv(traffic_path)
}
# Handle missing values
traffic_df <- traffic_df %>%
filter(!is.na(Vol))
# Create datetime column and extract components
traffic_df <- traffic_df %>%
mutate(
DateTime = as.POSIXct(paste(Yr, M, D, HH, MM, sep = "-"), format = "%Y-%m-%d-%H-%M"),
Year = year(DateTime),
Month = month(DateTime),
Day = day(DateTime),
DayOfWeek = wday(DateTime) - 1, # 0 = Sunday, 6 = Saturday
Hour = hour(DateTime),
# Create features for time of day
TimeOfDay = case_when(
Hour >= 6 & Hour < 10 ~ "Morning",
Hour >= 10 & Hour < 16 ~ "Midday",
Hour >= 16 & Hour < 20 ~ "Evening",
TRUE ~ "Night"
),
# Create weekday/weekend feature
IsWeekend = if_else(DayOfWeek >= 5, 1, 0)
)
return(traffic_df)
}
# Process traffic data (with sampling due to large file size)
traffic_df <- preprocess_traffic_data(sample_size = 100000)
# Show summary
summary(traffic_df) RequestID Boro Yr M
Min. : 1139 Length:100000 Min. :2006 Min. : 1.000
1st Qu.:11183 Class :character 1st Qu.:2012 1st Qu.: 4.000
Median :19713 Mode :character Median :2015 Median : 6.000
Mean :20422 Mean :2015 Mean : 6.447
3rd Qu.:30153 3rd Qu.:2019 3rd Qu.:10.000
Max. :37697 Max. :2024 Max. :12.000
D HH MM Vol
Min. : 1.00 Min. : 0.0 Min. : 0.00 Min. : 0.0
1st Qu.: 8.00 1st Qu.: 6.0 1st Qu.: 0.00 1st Qu.: 21.0
Median :15.00 Median :11.0 Median :15.00 Median : 64.0
Mean :15.15 Mean :11.5 Mean :22.19 Mean : 122.7
3rd Qu.:22.00 3rd Qu.:17.0 3rd Qu.:30.00 3rd Qu.: 150.0
Max. :31.00 Max. :23.0 Max. :45.00 Max. :5425.0
SegmentID WktGeom street fromSt
Min. : 6687 Length:100000 Length:100000 Length:100000
1st Qu.: 44169 Class :character Class :character Class :character
Median : 91639 Mode :character Mode :character Mode :character
Mean : 671490
3rd Qu.: 157501
Max. :9014018
toSt Direction DateTime
Length:100000 Length:100000 Min. :2006-09-05 12:00:00.00
Class :character Class :character 1st Qu.:2012-04-17 01:56:15.00
Mode :character Mode :character Median :2015-05-04 16:07:30.00
Mean :2015-09-23 05:08:38.48
3rd Qu.:2019-02-17 06:30:00.00
Max. :2024-06-10 23:45:00.00
NA's :12
Year Month Day DayOfWeek Hour
Min. :2006 Min. : 1.000 Min. : 1.00 Min. :0.000 Min. : 0.0
1st Qu.:2012 1st Qu.: 4.000 1st Qu.: 8.00 1st Qu.:1.000 1st Qu.: 6.0
Median :2015 Median : 6.000 Median :15.00 Median :3.000 Median :11.0
Mean :2015 Mean : 6.447 Mean :15.15 Mean :3.006 Mean :11.5
3rd Qu.:2019 3rd Qu.:10.000 3rd Qu.:22.00 3rd Qu.:5.000 3rd Qu.:17.0
Max. :2024 Max. :12.000 Max. :31.00 Max. :6.000 Max. :23.0
NA's :12 NA's :12 NA's :12 NA's :12 NA's :12
TimeOfDay IsWeekend
Length:100000 Min. :0.0000
Class :character 1st Qu.:0.0000
Mode :character Median :0.0000
Mean :0.2814
3rd Qu.:1.0000
Max. :1.0000
NA's :12 2.1.3 Emergency Response Data Preprocessing
Code
emergency_path <- here("data", "911_Open_Data_Local_Law_119_20250505.csv")
# Function to preprocess emergency response data
preprocess_emergency_data <- function() {
emergency_df <- read_csv(emergency_path)
# Extract date from 'Month Name' column
emergency_df <- emergency_df %>%
mutate(
Date = as.Date(paste0("01 ", `Month Name`), format = "%d %Y / %m"),
Year = year(Date),
Month = month(Date)
)
return(emergency_df)
}
# Process emergency data
emergency_df <- preprocess_emergency_data()
# Display the structure
glimpse(emergency_df)Rows: 10,166
Columns: 9
$ `Month Name` <chr> "2025 / 03", "2025 / 03", "2025 / 03", "2025 / 03", "…
$ Agency <chr> "Aggregate", "Aggregate", "Aggregate", "Aggregate", "…
$ Description <chr> "Combined average response time to life threatening m…
$ Borough <chr> "Brooklyn", "Bronx", "Manhattan", "Queens", "Staten I…
$ `# of Incidents` <dbl> 13615, 11039, 10868, 9819, 2398, 59, 601, 12750, 2732…
$ `Response Times` <dbl> 567.01, 683.05, 642.23, 601.25, 550.99, 849.32, 515.4…
$ Date <date> 2025-03-01, 2025-03-01, 2025-03-01, 2025-03-01, 2025…
$ Year <dbl> 2025, 2025, 2025, 2025, 2025, 2025, 2025, 2025, 2025,…
$ Month <dbl> 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3,…2.2 Exploratory Data Analysis
Code
# 1. Weather patterns over time
ggplot(weather_df, aes(x = Date, y = Temperature)) +
geom_line() +
labs(
title = "Monthly Temperature in NYC (2013-2025)",
x = "Date",
y = "Temperature (F)"
) +
theme_minimal()
Code
ggplot(weather_df, aes(x = Date, y = Rainfall)) +
geom_line() +
labs(
title = "Monthly Rainfall in NYC (2013-2025)",
x = "Date",
y = "Rainfall (inches)"
) +
theme_minimal()
Code
# Traffic volume by time of day
traffic_df %>%
group_by(TimeOfDay) %>%
summarize(MeanVolume = mean(Vol, na.rm = TRUE)) %>%
mutate(TimeOfDay = factor(TimeOfDay, levels = c("Morning", "Midday", "Evening", "Night"))) %>%
ggplot(aes(x = TimeOfDay, y = MeanVolume)) +
geom_col(fill = "steelblue") +
labs(
title = "Average Traffic Volume by Time of Day",
x = "Time of Day",
y = "Average Volume"
) +
theme_minimal()
Code
# Traffic volume by borough
traffic_df %>%
group_by(Boro) %>%
summarize(MeanVolume = mean(Vol, na.rm = TRUE)) %>%
arrange(desc(MeanVolume)) %>%
ggplot(aes(x = reorder(Boro, MeanVolume), y = MeanVolume)) +
geom_col(fill = "steelblue") +
labs(
title = "Average Traffic Volume by Borough",
x = "Borough",
y = "Average Volume"
) +
theme_minimal() +
coord_flip()
Code
# Emergency response times by borough
ggplot(emergency_df, aes(x = Borough, y = `Response Times`)) +
geom_boxplot(fill = "steelblue", alpha = 0.7) +
labs(
title = "Emergency Response Times by Borough",
x = "Borough",
y = "Response Time (seconds)"
) +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
2.3 Data Integration
Code
# Function to integrate datasets - simplified version to avoid join errors
integrate_datasets <- function(traffic_df, weather_df, emergency_df) {
# For demonstration purposes, we'll create a synthetic integrated dataset
# This avoids complex joins that might cause errors
# Create a sequence of dates
start_date <- as.Date("2022-01-01")
end_date <- as.Date("2022-12-31")
date_seq <- seq(start_date, end_date, by = "day")
# Create synthetic integrated data
set.seed(42) # For reproducibility
# Create boroughs vector
boroughs <- c("Manhattan", "Brooklyn", "Queens", "Bronx", "Staten Island")
# Expanded dataset with one row per date per borough
dates_expanded <- expand.grid(
Date = date_seq,
Boro = boroughs,
stringsAsFactors = FALSE
)
# Add additional columns
integrated_data <- dates_expanded %>%
mutate(
Year = year(Date),
Month = month(Date),
Day = day(Date),
DayOfWeek = wday(Date) - 1,
IsWeekend = if_else(DayOfWeek >= 5, 1, 0),
Season = case_when(
Month %in% c(12, 1, 2) ~ "Winter",
Month %in% c(3, 4, 5) ~ "Spring",
Month %in% c(6, 7, 8) ~ "Summer",
TRUE ~ "Fall"
),
# Simulate traffic volume - higher on weekdays
Vol = round(rnorm(n(),
mean = ifelse(IsWeekend == 1, 400, 550),
sd = ifelse(IsWeekend == 1, 100, 150))),
# Simulate temperature and rainfall
Temperature = 60 + 20 * sin((Month - 1) * pi/6) + rnorm(n(), 0, 5),
Rainfall = pmax(0, rexp(n(), 1/2)),
# Add anomalies
TempAnomaly = rnorm(n(), 0, 2),
RainAnomaly = rnorm(n(), 0, 0.5)
)
return(integrated_data)
}
# Integrate datasets
integrated_data <- integrate_datasets(traffic_df, weather_df, emergency_df)
# Save processed data
write_csv(integrated_data, here("data", "integrated_data.csv"))
# Display the structure of the integrated dataset
glimpse(integrated_data)Rows: 1,825
Columns: 13
$ Date <date> 2022-01-01, 2022-01-02, 2022-01-03, 2022-01-04, 2022-01-0…
$ Boro <chr> "Manhattan", "Manhattan", "Manhattan", "Manhattan", "Manha…
$ Year <dbl> 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022, 2022…
$ Month <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
$ Day <int> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17,…
$ DayOfWeek <dbl> 6, 0, 1, 2, 3, 4, 5, 6, 0, 1, 2, 3, 4, 5, 6, 0, 1, 2, 3, 4…
$ IsWeekend <dbl> 1, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0…
$ Season <chr> "Winter", "Winter", "Winter", "Winter", "Winter", "Winter"…
$ Vol <dbl> 537, 465, 604, 645, 611, 534, 551, 391, 853, 541, 746, 893…
$ Temperature <dbl> 58.74991, 55.29116, 66.07100, 60.09283, 63.78916, 58.09969…
$ Rainfall <dbl> 3.2551611, 0.9569055, 0.3091768, 6.2934679, 0.6828156, 1.1…
$ TempAnomaly <dbl> 2.8278749, 0.3219433, 0.4351352, 0.2505567, 1.4513482, -1.…
$ RainAnomaly <dbl> 0.708170717, 0.278616993, 0.490620657, -0.293091430, 0.469…2.4 Feature Engineering
Code
# Function to engineer features
engineer_features <- function(df) {
# One-hot encode categorical variables
df <- df %>%
mutate(
Season_Spring = if_else(Season == "Spring", 1, 0),
Season_Summer = if_else(Season == "Summer", 1, 0),
Season_Fall = if_else(Season == "Fall", 1, 0),
Season_Winter = if_else(Season == "Winter", 1, 0)
)
# Create borough dummy variables
boroughs <- unique(df$Boro)
for (borough in boroughs) {
col_name <- paste0("Boro_", borough)
df[[col_name]] <- if_else(df$Boro == borough, 1, 0)
}
# Add lagged features (previous day's traffic volume)
for (borough in boroughs) {
col_name <- paste0("Boro_", borough)
lag_col_name <- paste0(borough, "_prev_day_vol")
# Filter for this borough and sort by date
boro_data <- df %>%
filter(!!sym(col_name) == 1) %>%
arrange(Date)
# Create lagged volume
boro_data <- boro_data %>%
mutate(!!lag_col_name := lag(Vol))
# Update the original dataframe
df <- df %>%
left_join(
boro_data %>% select(Date, Boro, !!lag_col_name),
by = c("Date", "Boro")
)
}
# Fill NA values from lagged features with mean values
for (col in names(df)) {
if (any(is.na(df[[col]]))) {
col_mean <- mean(df[[col]], na.rm = TRUE)
df[[col]] <- if_else(is.na(df[[col]]), col_mean, df[[col]])
}
}
return(df)
}
# Engineer features
engineered_data <- engineer_features(integrated_data)
# Save engineered data
write_csv(engineered_data, here("data", "engineered_data.csv"))
# Summary of engineered features
summary(engineered_data) Date Boro Year Month
Min. :2022-01-01 Length:1825 Min. :2022 Min. : 1.000
1st Qu.:2022-04-02 Class :character 1st Qu.:2022 1st Qu.: 4.000
Median :2022-07-02 Mode :character Median :2022 Median : 7.000
Mean :2022-07-02 Mean :2022 Mean : 6.526
3rd Qu.:2022-10-01 3rd Qu.:2022 3rd Qu.:10.000
Max. :2022-12-31 Max. :2022 Max. :12.000
Day DayOfWeek IsWeekend Season
Min. : 1.00 Min. :0.000 Min. :0.0000 Length:1825
1st Qu.: 8.00 1st Qu.:1.000 1st Qu.:0.0000 Class :character
Median :16.00 Median :3.000 Median :0.0000 Mode :character
Mean :15.72 Mean :3.008 Mean :0.2877
3rd Qu.:23.00 3rd Qu.:5.000 3rd Qu.:1.0000
Max. :31.00 Max. :6.000 Max. :1.0000
Vol Temperature Rainfall TempAnomaly
Min. : 44.0 Min. :29.05 Min. : 0.00039 Min. :-8.08655
1st Qu.: 395.0 1st Qu.:46.88 1st Qu.: 0.58922 1st Qu.:-1.39660
Median : 497.0 Median :59.95 Median : 1.43751 Median : 0.02128
Mean : 505.3 Mean :59.87 Mean : 2.06109 Mean : 0.02672
3rd Qu.: 609.0 3rd Qu.:73.14 3rd Qu.: 2.90382 3rd Qu.: 1.39763
Max. :1088.0 Max. :94.68 Max. :14.23706 Max. : 6.67509
RainAnomaly Season_Spring Season_Summer Season_Fall
Min. :-1.560276 Min. :0.0000 Min. :0.0000 Min. :0.0000
1st Qu.:-0.354275 1st Qu.:0.0000 1st Qu.:0.0000 1st Qu.:0.0000
Median :-0.001389 Median :0.0000 Median :0.0000 Median :0.0000
Mean :-0.005400 Mean :0.2521 Mean :0.2521 Mean :0.2493
3rd Qu.: 0.337203 3rd Qu.:1.0000 3rd Qu.:1.0000 3rd Qu.:0.0000
Max. : 2.164046 Max. :1.0000 Max. :1.0000 Max. :1.0000
Season_Winter Boro_Manhattan Boro_Brooklyn Boro_Queens Boro_Bronx
Min. :0.0000 Min. :0.0 Min. :0.0 Min. :0.0 Min. :0.0
1st Qu.:0.0000 1st Qu.:0.0 1st Qu.:0.0 1st Qu.:0.0 1st Qu.:0.0
Median :0.0000 Median :0.0 Median :0.0 Median :0.0 Median :0.0
Mean :0.2466 Mean :0.2 Mean :0.2 Mean :0.2 Mean :0.2
3rd Qu.:0.0000 3rd Qu.:0.0 3rd Qu.:0.0 3rd Qu.:0.0 3rd Qu.:0.0
Max. :1.0000 Max. :1.0 Max. :1.0 Max. :1.0 Max. :1.0
Boro_Staten Island Manhattan_prev_day_vol Brooklyn_prev_day_vol
Min. :0.0 Min. :101.0 Min. : 97.0
1st Qu.:0.0 1st Qu.:501.1 1st Qu.: 503.2
Median :0.0 Median :501.1 Median : 503.2
Mean :0.2 Mean :501.1 Mean : 503.2
3rd Qu.:0.0 3rd Qu.:501.1 3rd Qu.: 503.2
Max. :1.0 Max. :955.0 Max. :1034.0
Queens_prev_day_vol Bronx_prev_day_vol Staten Island_prev_day_vol
Min. : 44 Min. :141.0 Min. : 158.0
1st Qu.: 503 1st Qu.:507.3 1st Qu.: 513.4
Median : 503 Median :507.3 Median : 513.4
Mean : 503 Mean :507.3 Mean : 513.4
3rd Qu.: 503 3rd Qu.:507.3 3rd Qu.: 513.4
Max. :1074 Max. :945.0 Max. :1088.0 2.5 Summary
- Loaded and cleaned three distinct datasets: traffic volumes, weather patterns, and emergency response times
- Created time-based features including time of day, day of week, and seasonal indicators
- Performed exploratory visualizations to understand traffic patterns by time and location
- Integrated the datasets to create a comprehensive view of NYC traffic patterns
- Engineered additional features to improve the model’s predictive capabilities
The prepared data will be called in the next chapter to develop the models and analysis.