Skip Navigation

Chicago Safety Data

311 Chicago Complaints Garbage Lights Pot Holes Public Safety Sanitation Service Requests Streets

311 service requests from the city of Chicago, including historical sanitation code complaints, pot holes reported, and street light issues

All open sanitation code complaints made to 311 and all requests completed since January 1, 2011. The Department of Streets and Sanitation investigates and remedies reported violations of Chicago’s sanitation code. Residents may request service for violations such as overflowing dumpsters and garbage in the alley. 311 sometimes receives duplicate sanitation code complaints. Requests that have been labeled as duplicates are in the same geographic area as a previous request and have been entered into 311’s Customer Service Request (CSR) system at around the same time. Duplicate complaints are labeled as such in the status field, as either “Open - Dup” or “Completed - Dup.”

The Chicago Department of Transportation (CDOT) oversees the patching of potholes on over 4,000 miles of arterial and residential streets in Chicago. CDOT receives reports of potholes through the 311 call center and uses a computerized mapping and tracking system to identify pothole locations and efficiently schedule crews. One call to 311 can generate multiple pothole repairs. When a crew arrives to repair a 311 pothole, it fills all the other potholes within the block. Pothole repairs are generally completed within 7 days from the first report of a pothole to 311. Weather conditions, particularly frigid temps and precipitation, influence how long a repair takes. On days when weather is cooperative and there is no precipitation, crews can fill several thousand potholes. If a previous request is already open for a buffer of 4 addresses the request is given the status of “Duplicate (Open)”. For example, if there is an existing CSR for 6535 N Western and a new request is received for 6531 N Western (which is within four addresses of the original CSR) then the new request is given a status of “Duplicate (Open)”. Once the street is repaired, the status in CSR will read “Completed” for the original request and “Duplicate (Closed)” for any duplicate requests. A service request also receives the status of “Completed” when the reported address is inspected but no potholes are found or have already been filled. If another issue is found with the street, such as a “cave-in” or “failed utility cut”, then it is directed to the appropriate department or contractor.

All open reports of “Street Lights - All Out” (an outage of 3 or more lights) made to 311 and all requests completed since January 1, 2011.The Chicago Department of Transportation (CDOT) oversees approximately 250,000 street lights that illuminate arterial and residential streets in Chicago. CDOT performs repairs and bulb replacements in response to residents’ reports of street light outages. Whenever CDOT receives a report of an “All Out” the electrician assigned to make the repair looks at all the lights in that circuit (each circuit has 8-16 lights) to make sure that they are all working properly. If a second request of lights out in the same circuit is made within four calendar days of the original request, the newest request is automatically given the status of “Duplicate (Open).” Since CDOT’s electrician will be looking at all the lights in a circuit to verify that they are all working, any “Duplicate (Open)” address will automatically be observed and repaired. Once the street lights are repaired, the status in CSR will read “Completed” for the original request and “Duplicate (Closed)” for any duplicate requests. A service request also receives the status of “Completed” when the reported lights are inspected but found to be in good repair and functioning; when the service request is for a non-existent address; or when the lights are maintained by a contractor. Data is updated daily.

Volume and Retention

This dataset is stored in Parquet format. It is updated daily, and contains about 1M rows (80MB) in total as of 2018.

This dataset contains historical records accumulated from 2011 to 2018. You can use parameter settings in our SDK to fetch data within a specific time range.

Storage Location

This dataset is stored in the East US Azure region. Allocating compute resources in East US is recommended for affinity.

Additional Information

This dataset is sourced from city of Chicago government. More details can be found from the following links: Sanitation, Pot Holes, Street Lights.

Reference here for the terms of using this dataset. Email if you have any questions about the data source.

Notices

MICROSOFT PROVIDES AZURE OPEN DATASETS ON AN “AS IS” BASIS. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, GUARANTEES OR CONDITIONS WITH RESPECT TO YOUR USE OF THE DATASETS. TO THE EXTENT PERMITTED UNDER YOUR LOCAL LAW, MICROSOFT DISCLAIMS ALL LIABILITY FOR ANY DAMAGES OR LOSSES, INCLUDING DIRECT, CONSEQUENTIAL, SPECIAL, INDIRECT, INCIDENTAL OR PUNITIVE, RESULTING FROM YOUR USE OF THE DATASETS.

This dataset is provided under the original terms that Microsoft received source data. The dataset may include data sourced from Microsoft.

Access

Available inWhen to use
Azure Notebooks

Quickly explore the dataset with Jupyter notebooks hosted on Azure or your local machine.

Azure Databricks

Use this when you need the scale of an Azure managed Spark cluster to process the dataset.

Preview

dataType dataSubtype dateTime category status address latitude longitude
Safety 311_All 12/10/2019 11:53:25 PM 311 INFORMATION ONLY CALL Completed 2111 W Lexington ST
Safety 311_All 12/10/2019 11:52:16 PM 311 INFORMATION ONLY CALL Completed 2111 W Lexington ST
Safety 311_All 12/10/2019 11:52:00 PM Street Light Out Complaint Open 5300 S CORNELL AVE 41.799510363 -87.58555395
Safety 311_All 12/10/2019 11:51:33 PM 311 INFORMATION ONLY CALL Completed 2111 W Lexington ST
Safety 311_All 12/10/2019 11:50:53 PM 311 INFORMATION ONLY CALL Completed 2111 W Lexington ST
Safety 311_All 12/10/2019 11:50:49 PM 311 INFORMATION ONLY CALL Completed 2111 W Lexington ST
Safety 311_All 12/10/2019 11:50:32 PM 311 INFORMATION ONLY CALL Completed 2111 W Lexington ST
Safety 311_All 12/10/2019 11:49:41 PM 311 INFORMATION ONLY CALL Completed 2111 W Lexington ST
Safety 311_All 12/10/2019 11:49:13 PM 311 INFORMATION ONLY CALL Completed 2111 W Lexington ST
Safety 311_All 12/10/2019 11:47:10 PM Street Light Out Complaint Open 8407 S PAULINA ST 41.740960913 -87.665343747
Name Data type Unique Values (sample) Description
address string 702,753 2111 W Lexington ST
10510 W ZEMKE RD

Street Address.

category string 99 311 INFORMATION ONLY CALL
Aircraft Noise Complaint

Type of Service Request.

dataSubtype string 1 311_All

“311_Sanitation”, “311_Potholes” or “311_Street_Lights”.

dataType string 1 Safety

“Safety”.

dateTime timestamp 2,027,726 2014-01-13 00:00:00
2014-03-10 00:00:00

Creation date of the Service Request.

extendedProperties string 568,544 "community_area:61,completion_date:2014-03-05T00:00:00.000,current_activity:Final Outcome,most_recent_action:No Jurisdiction,number_of_potholes_filled_on_block:0,police_district:9,ssa:13,zip:60609,y_coordinate:1878869.77549354,x_coordinate:1166917.50391208,ward:11"
"community_area:61,completion_date:2014-04-21T00:00:00.000,current_activity:Final Outcome,most_recent_action:No Potholes Found,number_of_potholes_filled_on_block:0,police_district:9,ssa:13,zip:60609,y_coordinate:1878869.77549354,x_coordinate:1166917.50391208,ward:11"

Additional fields availalbe with “key:value” pair format for each service request.

latitude double 980,441 41.871831278
41.994896549

Latitude.

longitude double 1,064,395 -87.679846219
-87.887751654

Longitude.

status string 5 Completed
Completed - Dup

Status of the Service Request, indicating whether it’s completed or not.

Select your preferred service:

Azure Notebooks

Azure Databricks

Azure Notebooks

Package: Language: Python Python
In [1]:
# This is a package in preview.
from azureml.opendatasets import ChicagoSafety

from datetime import datetime
from dateutil import parser


end_date = parser.parse('2016-01-01')
start_date = parser.parse('2015-05-01')
safety = ChicagoSafety(start_date=start_date, end_date=end_date)
safety = safety.to_pandas_dataframe()
ActivityStarted, to_pandas_dataframe ActivityStarted, to_pandas_dataframe_in_worker Looking for parquet files... Reading them into Pandas dataframe... Reading Safety/Release/city=Chicago/part-00080-tid-845600952581210110-a4f62588-4996-42d1-bc79-23a9b4635c63-446923.c000.snappy.parquet under container citydatacontainer Done. ActivityCompleted: Activity=to_pandas_dataframe_in_worker, HowEnded=Success, Duration=11294.27 [ms] ActivityCompleted: Activity=to_pandas_dataframe, HowEnded=Success, Duration=11295.76 [ms]
In [2]:
safety.info()
<class 'pandas.core.frame.DataFrame'> Int64Index: 56431 entries, 3751 to 2291417 Data columns (total 11 columns): dataType 56431 non-null object dataSubtype 56431 non-null object dateTime 56431 non-null datetime64[ns] category 56431 non-null object subcategory 0 non-null object status 56431 non-null object address 56431 non-null object latitude 56358 non-null float64 longitude 56358 non-null float64 source 0 non-null object extendedProperties 29124 non-null object dtypes: datetime64[ns](1), float64(2), object(8) memory usage: 5.2+ MB
# Pip install packages
import os, sys

!{sys.executable} -m pip install azure-storage
!{sys.executable} -m pip install pyarrow
!{sys.executable} -m pip install pandas

# COMMAND ----------

# Azure storage access info
azure_storage_account_name = "azureopendatastorage"
azure_storage_sas_token = r""
container_name = "citydatacontainer"
folder_name = "Safety/Release/city=Chicago"

# COMMAND ----------

from azure.storage.blob import BlockBlobService

if azure_storage_account_name is None or azure_storage_sas_token is None:
    raise Exception("Provide your specific name and key for your Azure Storage account--see the Prerequisites section earlier.")

print('Looking for the first parquet under the folder ' + folder_name + ' in container "' + container_name + '"...')
blob_service = BlockBlobService(account_name = azure_storage_account_name, sas_token = azure_storage_sas_token,)
blobs = blob_service.list_blobs(container_name)
sorted_blobs = sorted(list(blobs), key=lambda e: e.name, reverse=True)
targetBlobName=''
for blob in sorted_blobs:
    if blob.name.startswith(folder_name) and blob.name.endswith('.parquet'):
        targetBlobName = blob.name
        break

print('Target blob to download: ' + targetBlobName)
_, filename = os.path.split(targetBlobName)
parquet_file=blob_service.get_blob_to_path(container_name, targetBlobName, filename)

# COMMAND ----------

# Read the local parquet file into Pandas data frame
import pyarrow.parquet as pq
import pandas as pd

appended_df = []
print('Reading the local parquet file into Pandas data frame')
df = pq.read_table(filename).to_pandas()

# COMMAND ----------

# you can add your filter at below
print('Loaded as a Pandas data frame: ')
df

# COMMAND ----------


Azure Databricks

Package: Language: Python Python
In [1]:
# This is a package in preview.
# You need to pip install azureml-opendatasets in Databricks cluster. https://docs.microsoft.com/en-us/azure/data-explorer/connect-from-databricks#install-the-python-library-on-your-azure-databricks-cluster
from azureml.opendatasets import ChicagoSafety

from datetime import datetime
from dateutil import parser


end_date = parser.parse('2016-01-01')
start_date = parser.parse('2015-05-01')
safety = ChicagoSafety(start_date=start_date, end_date=end_date)
safety = safety.to_spark_dataframe()
ActivityStarted, to_spark_dataframe ActivityStarted, to_spark_dataframe_in_worker ActivityCompleted: Activity=to_spark_dataframe_in_worker, HowEnded=Success, Duration=3396.42 [ms] ActivityCompleted: Activity=to_spark_dataframe, HowEnded=Success, Duration=3398.23 [ms]
In [2]:
display(safety.limit(5))
dataTypedataSubtypedateTimecategorysubcategorystatusaddresslatitudelongitudesourceextendedProperties
Safety311_All2015-08-14T00:00:00.000+0000Street Lights - All/OutnullCompleted3742 W 59TH ST41.78603578433899-87.71664452773216nullcommunity_area:62,completion_date:2015-08-20T00:00:00.000,police_district:8,service_request_number:15-04081104,type_of_service_request:Street Lights - All/Out,zip_code:60629,y_coordinate:1865225.18787483,x_coordinate:1152434.60798184,ward:23
Safety311_All2015-06-22T00:00:00.000+0000Street Lights - All/OutnullCompleted3719 W 53RD ST41.79697833262681-87.71666270664977nullcommunity_area:62,completion_date:2015-06-23T00:00:00.000,police_district:8,service_request_number:15-02890103,type_of_service_request:Street Lights - All/Out,zip_code:60632,y_coordinate:1869212.53972903,x_coordinate:1152401.04890436,ward:23
Safety311_All2015-12-11T00:00:00.000+0000Street Lights - All/OutnullCompleted2010 N SAWYER AVE41.9177056604285-87.70893291956332nullcommunity_area:22,completion_date:2015-12-14T00:00:00.000,police_district:14,service_request_number:15-06669629,type_of_service_request:Street Lights - All/Out,zip_code:60647,y_coordinate:1913220.44877936,x_coordinate:1154188.85999445,ward:26
Safety311_All2015-09-14T00:00:00.000+0000Street Lights - All/OutnullCompleted4800 N CENTRAL PARK AVE41.96834840769826-87.7182549592663nullcommunity_area:14,completion_date:2015-09-15T00:00:00.000,police_district:17,service_request_number:15-04793248,type_of_service_request:Street Lights - All/Out,zip_code:60625,y_coordinate:1931656.45677884,x_coordinate:1151519.31229956,ward:35
Safety311_All2015-12-17T00:00:00.000+0000Street Lights - All/OutnullCompleted7100 S YATES BLVDnullnullnullnull
# Databricks notebook source
# Azure storage access info
blob_account_name = "azureopendatastorage"
blob_container_name = "citydatacontainer"
blob_relative_path = "Safety/Release/city=Chicago"
blob_sas_token = r""

# COMMAND ----------

# Allow SPARK to read from Blob remotely
wasbs_path = 'wasbs://%s@%s.blob.core.windows.net/%s' % (blob_container_name, blob_account_name, blob_relative_path)
spark.conf.set(
  'fs.azure.sas.%s.%s.blob.core.windows.net' % (blob_container_name, blob_account_name),
  blob_sas_token)
print('Remote blob path: ' + wasbs_path)

# COMMAND ----------

# SPARK read parquet, note that it won't load any data yet by now
df = spark.read.parquet(wasbs_path)
print('Register the DataFrame as a SQL temporary view: source')
df.createOrReplaceTempView('source')

# COMMAND ----------

# Display top 10 rows
print('Displaying top 10 rows: ')
display(spark.sql('SELECT * FROM source LIMIT 10'))

City Safety

From the Urban Innovation Initiative at Microsoft Research, databricks notebook for analytics with safety data (311 and 911 call data) from major U.S. cities. Analyses show frequency distributions and geographic clustering of safety issues within cities.