In [1]:
from IPython.display import HTML

HTML('''<script>
code_show=true; 
function code_toggle() {
 if (code_show){
 $('div.input').hide();
 } else {
 $('div.input').show();
 }
 code_show = !code_show
} 
$( document ).ready(code_toggle);
</script>
<form action="javascript:code_toggle()"><input type="submit" value="Click here to toggle on/off the raw code."></form>''')
Out[1]:

311 Data Aggregation

Importing libraries

In [58]:
import pandas as pd
import matplotlib
from IPython.display import Image
from IPython.core.display import HTML 

%matplotlib inline

Reading standardized data

*** census tract infromation added - retrived from census for each record based on lat-lon

In [73]:
df=pd.read_csv('311_geo_report_tracts.csv',parse_dates=['requested_datetime','updated_datetime','closed_date'])

Number of Requests Containing Location Information:

In [59]:
df.shape[0]
Out[59]:
25739

Sample 311 Data - Containting Location Information

In [60]:
df.head()
Out[60]:
service_request_id service_name requested_datetime source description status_description updated_datetime service_subtype neighborhood_district closed_date ... long STATEFP COUNTYFP TRACTCE AFFGEOID GEOID NAME LSAD ALAND AWATER
0 400578 Trash questions 2015-07-02 12:17:00 33891 DPW Sanitation Closed 2017-06-26 20:17:00 information calls Ward 7 2017-06-26 20:17:00 ... -71.12520 25 17 350600 1400000US25017350600 25017350600 3506.0 CT 516247 0
1 400606 Request tree on public property 2015-07-02 12:59:00 43103 OSPCD Closed 2017-06-08 12:35:00 Service Requests Ward 5 2017-06-08 12:35:00 ... -71.10195 25 17 350200 1400000US25017350200 25017350200 3502.0 CT 683707 0
2 401540 Temporary no parking sign posting 2015-07-07 10:08:00 43542 Traffic and Parking Closed 2017-06-29 15:58:00 internally generated Ward 6 2017-06-29 15:58:00 ... -71.11537 25 17 350900 1400000US25017350900 25017350900 3509.0 CT 421685 0
3 402710 Request tree on public property 2015-07-10 11:12:00 27228 OSPCD Closed 2016-02-23 10:08:00 Service Requests Ward 3 2016-02-23 10:08:00 ... -71.11064 25 17 351000 1400000US25017351000 25017351000 3510.0 CT 668313 0
4 403238 Arborist and tree maintenance 2015-07-13 15:20:00 31420 DPW-Highway Closed 2017-05-09 14:03:00 Service Requests Ward 7 2017-05-09 14:03:00 ... -71.12431 25 17 350600 1400000US25017350600 25017350600 3506.0 CT 516247 0

5 rows × 23 columns

Total number of requests from Feb to June (2017) containing location information

In [61]:
df['count']=1
df['year']=df.requested_datetime.dt.year
df=df[df['year']==2017]
df['month']=df.requested_datetime.dt.month
df=df[df.month>2]
df.shape[0]
Out[61]:
24390

100 most frequent request types containing location information

In [62]:
df.service_name.value_counts()[0:50]
Out[62]:
Pothole                                               1754
Obtain a parking permit inquiry                       1329
Temporary no parking sign posting                     1274
Illegal parking                                        872
TV/monitor pick up                                     856
School building maintenance & repairs                  827
Repeat Call                                            534
Trash questions                                        449
Sidewalk repair                                        418
City building maintenance & repairs                    407
Down tree limb branch                                  400
Residential & other non-business permit assistance     397
CS-Lost call (wrong #, hang up, dead air)              397
Miscellaneous                                          383
Tree trimming/pruning                                  373
Consideration request                                  365
Online permit help/questions                           354
Status of permit renewal/purchase inquiry              344
Water inquiries & feedback                             323
Disposal of yard waste/xmas tree inquiry               322
ISD-Buildings                                          313
Rats                                                   310
Missed trash pick up                                   274
Dead animal                                            269
Registry hold release request                          242
Graffiti                                               240
Report extra trash                                     235
Ticket info. (balance, how many, citation #, etc.)     225
City office hours or directions                        205
Report missed recycling pick up                        195
Snow/sidewalk not shoveled                             194
Traffic light/crossing signal issue                    189
Appeal issue request                                   187
DPW information/feedback                               185
Police-Call transferred to dispatch                    178
Clarification of traffic/parking regulations           177
Constituent Services                                   175
Arborist and tree maintenance                          173
Moving vans/pods/No Parking signs inquiry              163
CS-Welcome kit paper/mail out requests                 159
Clerk-Public services/non-criminal citations           157
Snow removal request                                   157
Park/grounds issue                                     153
How to appeal a parking ticket inquiry                 151
Treasury                                               150
Report missed yard waste pick up                       146
T&P information/feedback                               145
Assessor                                               128
Airplane, airport or FAA questions and complaints      126
Trash and recycling pickup/holiday information         123
Name: service_name, dtype: int64
In [63]:
df.service_name.value_counts()[50:100]
Out[63]:
Trash issues                                          123
OSPCD                                                 123
Deliveries for events                                 122
Street/road defect                                    120
Treasury-Municipal billing/tax information            113
Sustainability & environment                          112
Welcome desk online assistance                        110
ISD-Health                                            109
City event information-sponsored                      107
City school department call                           107
T&P Miscellaneous                                     102
CS-Street sweeper schedule & alert add/remove          99
City Clerk                                             99
Reschedule hearing request                             97
Sinkhole                                               97
Sewer                                                  94
Construction and road work inquiries                   90
Abandoned property                                     90
Council on Aging                                       89
Recycling questions                                    88
Unknown/Miscellaneous                                  86
Fire-Downed wire                                       86
Furniture and equipment moving                         86
White goods sticker questions                          79
Missing/stolen recycling cart                          79
Disposal of hazardous waste inquiry                    78
Assistance paying a parking ticket                     77
Tree/stump removal                                     77
Bagging request                                        74
Street light issue                                     72
Street sign issue                                      71
Damaged or missing permanent sign                      71
Police-Questions/information                           70
Trash/debris on sidewalk or street                     69
Status on ticket appeal inquiry                        69
Snow related feedback (if follow up is not needed)     67
Missing/stolen trash cart report                       67
CTY follow up questions                                67
Damaged/broken trash cart report                       66
Animal Control                                         65
Mayor                                                  65
Removing a registry hold Inquiry                       62
Illegal dumping                                        61
Fire-Other inquiries                                   61
Late fee removal request                               59
CTY add/remove request                                 59
Disposal extra trash inquiry                           59
Street cleaning/sweeping                               59
Catch basin                                            56
Fire                                                   55
Name: service_name, dtype: int64

Limiting Data to requests of the following types:

1- 'Abandoned property', 2- 'Power outage', 3- 'Street light issue', 4- 'ISD-Buildings', 5- 'Illegal parking', 6- 'TV/monitor pick up'

In [64]:
dg=df.groupby('service_name').count()
services=['TV/monitor pick up','Illegal parking','ISD-Buildings','Street light issue','Abandoned property','Power outage']
df=df[df.service_name.isin(services)]
df=df.reset_index(drop=True)
df.shape[0]
Out[64]:
2251

Aggregating data by census tract

In [65]:
dg=df.groupby('TRACTCE').count()
tracts=dg[dg['count']>10].index
df=df[df.TRACTCE.isin(tracts)]
df=df.reset_index(drop=True)

Aggregating data by week and tract

In [66]:
df['week']=df.requested_datetime.dt.week
dg=df[['week','TRACTCE','service_name','count']].groupby(['week','TRACTCE','service_name']).count()
dg=dg.unstack().reset_index()
dg.columns=['Week Number','Tract Number']+list(dg.columns.get_level_values(1)[2:])

Number of week - tract groups

In [69]:
dg=dg.fillna(0)
dg.shape[0]
Out[69]:
392

Sample aggregated data

In [72]:
dg.to_csv('311_tract_groups.csv',index=False)
dg.head()
Out[72]:
Week Number Tract Number Abandoned property ISD-Buildings Illegal parking Power outage Street light issue TV/monitor pick up
0 9 350104 0.0 0.0 5.0 0.0 0.0 1.0
1 9 350200 0.0 0.0 2.0 0.0 0.0 1.0
2 9 350300 0.0 0.0 1.0 0.0 0.0 1.0
3 9 350400 0.0 0.0 1.0 0.0 1.0 1.0
4 9 350500 0.0 0.0 1.0 0.0 0.0 0.0