Пропустить навигацию

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.

Доступ

Доступно вСценарии использования
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.

Предварительная версия

dataType dataSubtype dateTime category status address latitude longitude
Safety 311_All 10/18/2019 11:54:38 PM Building Violation Open 12349 S WALLACE ST 41.669127492 -87.636546513
Safety 311_All 10/18/2019 11:53:54 PM Missed Garbage Pick-Up Complaint Open 5804 S NARRAGANSETT AVE 41.786619425 -87.781869695
Safety 311_All 10/18/2019 11:53:13 PM Street Light Out Complaint Open 527 W 60TH PL 41.784184295 -87.638714246
Safety 311_All 10/18/2019 11:52:56 PM Restaurant Complaint Open 2008 W 63RD ST 41.779686183 -87.674468508
Safety 311_All 10/18/2019 11:50:32 PM Street Light Out Complaint Open 8059 S CALIFORNIA AVE 41.74649656 -87.69218337
Safety 311_All 10/18/2019 11:48:23 PM Aircraft Noise Complaint Completed 10510 W ZEMKE RD
Safety 311_All 10/18/2019 11:47:37 PM 311 INFORMATION ONLY CALL Completed 2111 W Lexington ST
Safety 311_All 10/18/2019 11:46:58 PM Aircraft Noise Complaint Completed 10510 W ZEMKE RD
Safety 311_All 10/18/2019 11:44:55 PM 311 INFORMATION ONLY CALL Completed 2111 W Lexington ST
Safety 311_All 10/18/2019 11:39:56 PM Sanitation Code Violation Open 4600 W BELMONT AVE 41.939096879 -87.742461297
Имя Тип данных Уникальные Значения (пример) Описание
address string 683,937 2111 W Lexington ST
10510 W ZEMKE RD

Street Address.

category string 97 311 INFORMATION ONLY CALL
Pothole in Street

Type of Service Request.

dataSubtype string 1 311_All

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

dataType string 1 Safety

“Safety”.

dateTime timestamp 1,785,723 2014-01-13 00:00:00
2014-03-10 00:00:00

Creation date of the Service Request.

extendedProperties string 568,486 "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 951,704 41.871831278
41.994896549

Latitude.

longitude double 1,022,892 -87.679846219
-87.67984622

Longitude.

status string 5 Completed
Completed - Dup

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

Выберите предпочитаемую службу:

Azure Notebooks

Azure Databricks

Azure Notebooks

Пакет: Язык: 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

Пакет: Язык: 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.