311 Data Standardization

specification: http://wiki.open311.org/GeoReport/bulk

Importing libraries

In [15]:
import pandas as pd

Importing 311 raw data

In [16]:
df=pd.read_csv('311_Constituent_Services_Daily_Calls.csv')

Sample 311 raw data

In [17]:
df.head()
Out[17]:
ticket_id issue_type ticket_created_date_time submitter issue_description ticket_status ticket_last_updated_date_time secondary_issue_type neighborhood_district ticket_closed_date_time location street_address
0 400578 Trash questions 07/02/2015 12:17:00 PM 33891 DPW Sanitation Closed 06/26/2017 08:17:00 PM information calls Ward 7 06/26/2017 08:17:00 PM 80 Electric Ave\nSomerville, MA\n(42.403581, -... 80 Electric Ave
1 400606 Request tree on public property 07/02/2015 12:59:00 PM 43103 OSPCD Closed 06/08/2017 12:35:00 PM Service Requests Ward 5 06/08/2017 12:35:00 PM 32 Robinson St\nSomerville, MA\n(42.3928953, -... 32 Robinson St
2 401540 Temporary no parking sign posting 07/07/2015 10:08:00 AM 43542 Traffic and Parking Closed 06/29/2017 03:58:00 PM internally generated Ward 6 06/29/2017 03:58:00 PM 5 Windsor Rd\nSomerville, MA\n(42.3930438, -71... 5 Windsor Rd
3 402710 Request tree on public property 07/10/2015 11:12:00 AM 27228 OSPCD Closed 02/23/2016 10:08:00 AM Service Requests Ward 3 02/23/2016 10:08:00 AM 56 Lowell St\nSomerville, MA\n(42.3863771, -71... 56 Lowell St
4 403238 Arborist and tree maintenance 07/13/2015 03:20:00 PM 31420 DPW-Highway Closed 05/09/2017 02:03:00 PM Service Requests Ward 7 05/09/2017 02:03:00 PM 17 Sunset Rd\nSomerville, MA\n(42.4087858, -71... 17 Sunset Rd

Renaming fileds to:

     'service_request_id',
     'service_name',
     'requested_datetime',
     'source',
     'description',
     'status_description',
     'updated_datetime',
     'service_subtype',
     'neighborhood_district',
     'closed_date',
     'location',
     'address',
In [18]:
df.columns=['service_request_id',
         'service_name',
         'requested_datetime',
         'source',
         'description',
         'status_description',
         'updated_datetime',
         'service_subtype',
         'neighborhood_district',
         'closed_date',
         'location',
         'address',         
        ]

Converting location data to latitude and longitude

In [19]:
df['lat']=df.location.apply(lambda x : x.split('\n')[2].split(',')[0][1:])
df['long']=df.location.apply(lambda x : x.split('\n')[2].split(',')[1][:-1])

Formtting date and time to ISO 8601 (e.g. 1994-11-05T08:15:30-05:00)

In [20]:
df['requested_datetime']=pd.to_datetime(df['requested_datetime'])
df['updated_datetime']=pd.to_datetime(df['updated_datetime'])
df['closed_date']=pd.to_datetime(df['closed_date'])
In [21]:
df['requested_datetime']=df['requested_datetime'].dt.strftime('%Y%m%dT%H:%MZ')
df['updated_datetime']=df['updated_datetime'].dt.strftime('%Y%m%dT%H:%MZ')
df['closed_date']=df['closed_date'].dt.strftime('%Y%m%dT%H:%MZ')

Sample standardized data

In [25]:
df.head()
Out[25]:
service_request_id service_name requested_datetime source description status_description updated_datetime service_subtype neighborhood_district closed_date location address lat long
0 400578 Trash questions 20150702T12:17Z 33891 DPW Sanitation Closed 20170626T20:17Z information calls Ward 7 20170626T20:17Z 80 Electric Ave\nSomerville, MA\n(42.403581, -... 80 Electric Ave 42.403581 -71.125199
1 400606 Request tree on public property 20150702T12:59Z 43103 OSPCD Closed 20170608T12:35Z Service Requests Ward 5 20170608T12:35Z 32 Robinson St\nSomerville, MA\n(42.3928953, -... 32 Robinson St 42.3928953 -71.1019513
2 401540 Temporary no parking sign posting 20150707T10:08Z 43542 Traffic and Parking Closed 20170629T15:58Z internally generated Ward 6 20170629T15:58Z 5 Windsor Rd\nSomerville, MA\n(42.3930438, -71... 5 Windsor Rd 42.3930438 -71.115367
3 402710 Request tree on public property 20150710T11:12Z 27228 OSPCD Closed 20160223T10:08Z Service Requests Ward 3 20160223T10:08Z 56 Lowell St\nSomerville, MA\n(42.3863771, -71... 56 Lowell St 42.3863771 -71.1106436
4 403238 Arborist and tree maintenance 20150713T15:20Z 31420 DPW-Highway Closed 20170509T14:03Z Service Requests Ward 7 20170509T14:03Z 17 Sunset Rd\nSomerville, MA\n(42.4087858, -71... 17 Sunset Rd 42.4087858 -71.1243068

Saving standardized data into '311_geo_report.csv'

In [23]:
df.to_csv('311_geo_report.csv',index=False)