Overview

I like working with Chicago 311 service request data, so this document makes some tables using those data, which all available from the Chicago Data Portal. As it turns out, summarizing these data can result in tables that are quite long, extending well beyond the vertical length of most screens. The negative effects of this can mitigated by incorporating interactivity into the tables, and {reactable} makes that easy to accomplish.

The data in this table includes service requests created on or between 31 Oct 2019 and 31 Oct 2020, and excludes the two most common requests, 311 information requests and aircraft noise complaints, because they are not associated with specific geographic locations in the available 311 request data: all information requests are associated with the main 311 office location, and all aircraft noise complaints are associated with airport locations.

library(tidyverse)
library(xml2)
library(rvest)
library(reactable)

# read 311 data
raw_311 <- try(readRDS('./data/lastyear_311_requests.rds'), silent = TRUE)

if (class(raw_311) == 'try-error') {
  message('no local version of 311 data, grabbing from github...')
  raw_311 <- readRDS(
  url("https://github.com/jimtheflash/rstudio.tablecontest.2020/raw/main/data/lastyear_311_requests.rds","rb")
  )
}

# get CA lookup for merging
ca_lu <- read_html('https://en.wikipedia.org/wiki/Community_areas_in_Chicago') %>%
  html_table() %>%
  `[[`(1) %>%
  transmute(community_area = as.numeric(`Number[8]`),
            ca_name = `Name[8]`,
            ca_sq_mi = as.numeric(`Area (sq mi.)[10]`))

# read latest CA population data
raw_ca <- read_csv("https://github.com/jimtheflash/rstudio.tablecontest.2020/raw/main/data/ReferenceCCAProfiles20142018.csv") %>%
  transmute(ca_name = GEOG,
            ca_est_pop = floor(as.numeric(TOT_POP)))

# tidy and merge for tables
tidy_311 <- raw_311 %>%
  # get rid of the top 2 types of service requests
  filter(sr_type != '311 INFORMATION ONLY CALL',
         sr_type != 'Aircraft Noise Complaint') %>%
  mutate(grouping_sr_number = if_else(parent_sr_number == "", sr_number, parent_sr_number)) %>%
  select(grouping_sr_number,
         sr_number,
         sr_type,
         created_date,
         community_area) %>%
  group_by(grouping_sr_number) %>%
  mutate(distinct_requests_by_grouping_sr_number = n_distinct(sr_number)) %>%
  ungroup() %>%
  inner_join(ca_lu, by = 'community_area') %>%
  inner_join(raw_ca, by = "ca_name")

Service Requests By Community Area

The table initially displays the total number of service requests and requests per 100,000 residents, by Community Area. To see counts of specific request types within each Community Area, click on the row. The table is sortable by any of the columns.

table_data <- tidy_311 %>%
  group_by(`Community Area` = ca_name, 
           `Estimated Population` = ca_est_pop,
           `Service Request Type` = sr_type) %>%
  summarise(`Total Requests` = n_distinct(sr_number)) %>%
  mutate(`Requests Per 100K` = (`Total Requests` / `Estimated Population`) * 100000) %>%
  arrange(desc(`Total Requests`))

the_table <- table_data %>%
  reactable(
    groupBy = "Community Area",
    columns = list(
      `Estimated Population` = colDef(
        name = 'Est. Population',
        aggregate = 'max',
        format = colFormat(separators = TRUE)
      ),
      `Total Requests` = colDef(
        name = 'Total Requests',
        aggregate = 'sum',
        format = colFormat(separators = TRUE)
        ),
      `Requests Per 100K` = colDef(
        name = 'Requests Per 100K',
        aggregate = JS("function(values, rows) {
        var totalReqs = 0
        var pop = 0
        rows.forEach(function(row) {
          totalReqs += row['Total Requests']
          pop = Math.max(row['Estimated Population'])
        })
        return (totalReqs / pop) * 100000
      }"),
        format = colFormat(digits = 1, separators = TRUE)
      )
    ),
    highlight = TRUE,
    defaultSorted = 'Total Requests',
    defaultSortOrder = 'desc',
    showPageSizeOptions = FALSE, 
    defaultPageSize = 100)

the_table