Report 311 Requests for Sidewalk Repairs using Parameterized Baltimore City 311 Data Report Template (Draft)

library(tidyverse) # for data wrangling
library(RSocrata) # for importing data from Open Baltimore
library(sf) # for working with spatial data
library(lubridate) # for working with time/date 
library(scales)
library(gt) # for creating tables
library(gtsummary) # for creating summary tables
# library(tidylog)
library(labelled) # for labelling variables and values
library(glue)

plot_theme <- theme_minimal() +
  hrbrthemes::theme_ipsum_rc(base_size = 20,
                             plot_title_size = 30,
                             axis_title_size = 15)

map_theme <- theme_void() +
  hrbrthemes::theme_ipsum_rc(
    base_size = 20,
    plot_title_size = 30,
    axis_title_size = 15
  ) + theme(
    panel.grid.major = element_line(color = "transparent"),
    axis.title = element_text(color = "transparent"),
    axis.text = element_text(color = "transparent")
  )

label_wrap_report <- label_wrap(55)

plot_color <- "#453781FF" # Based on viridis 


# Parse start/end date parameters
start_date <- paste0(params$start_date,"T00:00:00")
end_date <- paste0(params$end_date, "T23:59:59")

sr_type <- params$sr_type
sr_type_label_singular <- params$sr_type_label_singular
sr_type_label_plural <- params$sr_type_label_plural

# Create an account on Open Baltimore and get an API key to use RSocrata.
# More information on the Socrata API: https://dev.socrata.com/docs/endpoints.html
# The following is adapted from this post: https://mattherman.info/blog/point-in-poly/

# 311 Customer Service Requests on Open Baltimore
base <- "https://data.baltimorecity.gov/resource/"
resource <- "9agw-sxsr"

# Define list of variables
vars <- c("ServiceRequestNum", "CreatedDate", "StatusDate", "Agency", "SRType", "SRStatus",  "LastActivity", "Outcome", "Address", "Neighborhood", "Longitude", "Latitude")  # selected variables
vars_list <- paste(vars,collapse = ", ")

# Create API call
call <- glue("{base}{resource}.json?$where=CreatedDate between '{start_date}' and '{end_date}' AND SRType like '{sr_type}'&$select={vars_list}")

# Download data from Open Baltimore. API access token is saved via keyring package
requests <- read.socrata(call, app_token = keyring::key_get("Open Baltimore")) %>%
  as_tibble() %>%
  janitor::clean_names()

requests_analysis <- requests %>% # Clean variable names
  mutate(
    created_datetime = ymd_hms(created_date),
    created_date = date(created_date),
    status_datetime = ymd_hms(status_date),
    longitude = as.numeric(longitude),
    latitude = as.numeric(latitude),
    year_created = year(created_date), # Add year
    year_qrt_created = quarter(created_date, with_year = TRUE), # Add a year/quarter
    month_created = month(created_date), # Add month
    week_created = week(created_date), # Add week
    wday_created = wday(created_date),
    hours_to_close = case_when(
      sr_status == "Closed" ~ (int_length(interval(created_datetime,
                                                  status_datetime)))/3600), # Add days to close column to closed service requests
    days_to_close = hours_to_close / 24
    )

# Import data dictionary
request_dict <- tibble::tribble(
  ~variable, ~label, ~value_labels, ~definition,
  "service_request_num", "Request number", NA, "A unique identification number assigned to each service request.",
  "created_date", "Date created", NA, NA,
  "sr_status", "Request status", NA, NA,
  "status_date", "Date closed/updated", NA, "Date of the most recent status change (e.g. closed or updated)",
  "agency", "Assigned Baltimore City agency", NA, NA,
  "last_activity", "Last activity", NA, NA,
  "outcome", "Request outcome", NA, NA,
  "address", "Street address", NA, "Address number and street name",
  "neighborhood", "Neighborhood", NA, "Equivalent to neighborhood statistical area",
  "longitude", "Longitude", NA, NA,
  "latitude", "Latitude", NA, NA,
  "year_created", "Year created", NA, NA,
  "year_qrt_created", "Quarter created", NA, NA,
  "month_created", "Month created", "1 January 2 February 3 March 4 April 5 May 6 June 7 July 8 August 9 September 10 October 11 November 12 December", NA,
  "week_created", "Week created", NA, NA,
  "wday_created", "Weekday created", "1 Sunday 2 Monday 3 Tuesday 4 Wednesday 5 Thursday 6 Friday 7 Saturday", NA,
  "hours_to_close", "Hours to close request", NA, NA,
  "days_to_close", "Days to close request", NA, NA
  )

requests_analysis$neighborhood <- factor(requests_analysis$neighborhood)

# Label cleaned request data
var_label(requests_analysis) <- request_dict %>%
  select(variable, label) %>%
  codebook::dict_to_list()

val_labels(requests_analysis$wday_created) <- c(
  "Sunday" = 1,
  "Monday" = 2,
  "Tuesday" = 3,
  "Wednesday" = 4,
  "Thursday" = 5,
  "Friday" = 6,
  "Saturday" = 7
)

min_created_date <- as.character(min(requests_analysis$created_date),
                              format = "%B %e, %Y")

max_created_date <- as.character(max(requests_analysis$created_date),
                              format = "%B %e, %Y")

What is the status of the requests?

label_wrap_plot <- label_wrap(50)

requests_analysis %>%
  count(sr_status, name = "sr_status_count") %>%
  arrange(desc(sr_status)) %>% 
  ggplot(aes(sr_status, sr_status_count)) +
  geom_col(fill = plot_color, color = plot_color) +
  labs(
    caption = "Source: Open Baltimore",
    x = var_label(requests_analysis$sr_status),
    y = "Number of requests",
    title = label_wrap_plot(glue::glue(
      "Number of {sr_type_label_singular} 311 service requests from {min_created_date} to {max_created_date} by {str_to_lower(var_label(requests_analysis$sr_status))}"))
  ) +
  plot_theme +
  coord_flip() +
  theme(axis.text.y = element_text(size = 14))

When were the requests created?

Date
Week
Weekday

requests_analysis %>%
  ggplot(aes(created_date)) +
  geom_bar(fill = plot_color, color = plot_color) +
  labs(
    caption = "Source: Open Baltimore",
    x = var_label(requests_analysis$created_date),
    y = "Number of requests",
    title = label_wrap_report(glue::glue(
      "311 service requests for {sr_type_label_plural} from {min_created_date} to {max_created_date} by {str_to_lower(var_label(requests_analysis$created_date))}",
      min_date = as.character(min(requests_analysis$created_date),
                              format = "%B %e, %Y"),
      max_date = as.character(max(requests_analysis$created_date),
                              format = "%B %e, %Y")))
  ) +
  plot_theme

requests_analysis %>%
  ggplot(aes(week_created)) +
  geom_bar(fill = plot_color, color = plot_color) +
  labs(
    caption = "Source: Open Baltimore",
    x = var_label(requests_analysis$week_created),
    y = "Number of requests",
    title = label_wrap_report(glue::glue(
      "311 service requests for {sr_type_label_plural} from {min_created_date} to {max_created_date} by {str_to_lower(var_label(requests_analysis$week_created))}"))
  ) +
  plot_theme

Where are the requests located?

Neighborhoods
Community statistical areas

label_wrap_plot <- label_wrap(50)

requests_analysis %>%
  count(neighborhood, name = "neighborhood_count") %>%
  top_n(10, neighborhood_count) %>%
  arrange(desc(neighborhood)) %>% 
  ggplot(aes(neighborhood, neighborhood_count)) +
  geom_col(fill = plot_color, color = plot_color) +
  labs(
    caption = "Source: Open Baltimore",
    x = var_label(requests_analysis$neighborhood),
    y = "Number of requests",
    title = label_wrap_plot(glue::glue(
      "Ten neighborhoods with the greatest number of {sr_type_label_singular} 311 service requests from {min_created_date} to {max_created_date}"))
  ) +
  plot_theme +
  coord_flip() +
  theme(axis.text.y = element_text(size = 13))

# Convert request data to an SF object
requests_analysis_sf <- requests_analysis %>%
  filter(!is.na(longitude)) %>% 
  st_as_sf(
    coords = c("longitude", "latitude"),
    agr = "constant",
    crs = 4269,
    stringsAsFactors = FALSE,
    remove = TRUE
  ) %>%
  st_transform(4269) # Matching the CRS of the CSA data

csas <- read_sf('~/References/baltimore_gis/csa_2010_boundaries/CSA_NSA_Tracts.shp') %>% 
  janitor::clean_names('snake') %>%
  st_transform(4269)

requests_analysis_sf <- st_join(requests_analysis_sf,
                                csas, join = st_within)

var_label(requests_analysis_sf$community) <- c("Community Statistical Area")

label_wrap_plot <- label_wrap(35)

ggplot() +
  geom_sf(data = csas, color = "lightgray", fill = NA) +
  geom_sf(data = requests_analysis_sf, aes(color = community), size = 0.9, show.legend = FALSE) +
  scale_color_viridis_d(option="magma", direction = -1) +
  labs(caption = "Source: Open Baltimore",
    title = label_wrap_plot(
      glue("311 service requests for {sr_type_label_plural} from {min_created_date} to {max_created_date}"))) +
  map_theme

requests_analysis_sf %>%
  count(community, name = "community_count") %>%
  st_drop_geometry() %>% 
  right_join(csas, by = "community") %>% 
  st_as_sf() %>% 
  ggplot() +
  geom_sf(aes(fill = community_count), color = "white", size = 0.6) +
  scale_fill_viridis_c(option="magma", direction = -1) +
  scale_color_viridis_c(option="magma", direction = -1) +
  guides(color = FALSE) +
  labs(caption = "Source: Open Baltimore",
    title = label_wrap_plot(glue("311 service requests from {min_created_date} to {max_created_date} for {sr_type_label_plural} by Community Statistical Area")),
    fill = "Number of requests") +
  map_theme

Report 311 Requests for Sidewalk Repairs using Parameterized Baltimore City 311 Data Report Template (Draft)

Eli Pousson

What is the status of the requests?

When were the requests created?

Where are the requests located?