Analyzing and predicting Service Request Types in DC

import arcgis
from arcgis.features import *

requests_url = 'https://maps2.dcgis.dc.gov/dcgis/rest/services/DCGIS_DATA/ServiceRequests/MapServer/9'

requests_layer = FeatureLayer(requests_url)
requests_layer

<FeatureLayer url:"https://maps2.dcgis.dc.gov/dcgis/rest/services/DCGIS_DATA/ServiceRequests/MapServer/9">

# Extract all the data and display number of rows
requests_features = requests_layer.query()
print('Total number of rows in the dataset: ')
print(len(requests_features.features))

# store as dataframe
requests = requests_features.sdf

# View first 5 rows
requests.head()

neighborhood_url = 'https://maps2.dcgis.dc.gov/dcgis/rest/services/DCGIS_DATA/Administrative_Other_Boundaries_WebMercator/MapServer/17'

neighborhood_layer = FeatureLayer(neighborhood_url)
neighborhood_layer

# Extract all the data and display number of rows
neighborhood_features = neighborhood_layer.query()
print('Total number of rows in the dataset: ')
print(len(neighborhood_features.features))

# store as dataframe
neighborhood = neighborhood_features.sdf

# View first 5 rows
neighborhood.head()

# Connect to the GIS
from arcgis.gis import GIS
gis = GIS('http://dcdev.maps.arcgis.com/', 'username', 'password')

# Perform spatial join between CBG layer and the service areas created for all time durations
requests_with_neighborhood = arcgis.features.analysis.join_features(requests_url, neighborhood_url, spatial_relationship='Intersects', output_name='serviceRequests_Neighborhood_DC_1')

requests_with_neighborhood.share(everyone=True)

{'itemId': '21ecb944fdc3495ea23e8cee411d0e29', 'notSharedWith': []}

requests_with_neighborhood_url = str(requests_with_neighborhood.url)+'/0/'
layer = FeatureLayer(requests_with_neighborhood_url)
features = layer.query()
print('Total number of rows in the dataset: ')
print(len(features.features))

Total number of rows in the dataset: 
80957

merged = features.sdf
merged.head()

quads = ['NE', 'NW', 'SE', 'SW']
def generate_quadrant(x):
    '''Function that extracts quadrant from street address'''
    try:
        temp = x[-2:]
        if temp in quads:
            return temp
        else:
            return 'NaN'
    except:
        return 'NaN'

merged['QUADRANT'] = merged['STREETADDRESS'].apply(generate_quadrant)
merged['QUADRANT'].head()

0    NE
1    NW
2    NW
3    NW
4    SE
Name: QUADRANT, dtype: object

merged['QUADRANT'].unique()

array(['NE', 'NW', 'SE', 'NaN', 'SW'], dtype=object)

merged['CLUSTER'] = merged['NAME'].apply(lambda x: x[8:])
merged['CLUSTER'].head()

0    30
1    10
2     6
3    13
4    28
Name: CLUSTER, dtype: object

merged['CLUSTER'] = merged['CLUSTER'].astype(int)

merged['ORGANIZATIONACRONYM'].unique()

array(['DPW', 'DDOT', 'FEMS', 'DOH', 'OUC', 'DOEE', 'DMV', 'ORM',
       'DC-ICH', 'DDS'], dtype=object)

merged['STATUS_CODE'].unique()

array(['CLOSED', 'OPEN'], dtype=object)

len(merged['SERVICETYPECODEDESCRIPTION'].unique())

23

requests['SERVICETYPECODEDESCRIPTION'].unique()

array(['SNOW', 'PEMA- Parking Enforcement Management Administration',
       'Toa-Street & Bridge Maintenance', 'Urban Forrestry',
       'SWMA- Solid Waste Management Admistration',
       'Transportation Operations Administration', 'SIOD',
       'Department of Transportation', 'Driver Vehicle Services',
       'Toa-Trans Sys Mnt-Signs', 'Toa- Trans Sys Mnt',
       'DOH- Department Of Health', 'Tru-311',
       'Transportation Policy & Planning Administration',
       'FEMS-Smoke Alarms', 'FEMS-Special Events',
       'Department of Energy and Environment', 'Adjudication Services',
       '311- Call Center', 'HOMYDRPR- How Is My Driving Program',
       'DC Interagency Council on Homelessness', '311- Emergencies',
       'Department of Disability Services'], dtype=object)

# Import necessary packages
from sklearn.preprocessing import *
from sklearn.model_selection import train_test_split
from sklearn.ensemble import RandomForestClassifier
from sklearn.metrics import accuracy_score

# Convert categorical (text) fields to numbers
number = LabelEncoder()
merged['SERVICETYPE_NUMBER'] = number.fit_transform(merged['SERVICETYPECODEDESCRIPTION'].astype('str'))
merged['STATUS_CODE_NUMBER'] = number.fit_transform(merged['STATUS_CODE'].astype('str'))

# Extract desired fields
data = merged[['SERVICETYPECODEDESCRIPTION', 'SERVICETYPE_NUMBER', 'QUADRANT', 'CLUSTER', 'WARD', 'ORGANIZATIONACRONYM', 'STATUS_CODE', 'STATUS_CODE_NUMBER']]
data.reset_index(inplace=True)
data.head()

import pandas as pd
data = pd.get_dummies(data=data, columns=['QUADRANT', 'ORGANIZATIONACRONYM'])
data.head()

# Extract input dataframe
model_data = data.drop(['SERVICETYPECODEDESCRIPTION', 'SERVICETYPE_NUMBER', 'STATUS_CODE'], axis=1)
model_data.head()

def handle_ward(x):
    accept = [range(0,8)]
    if x not in accept:
        return 0
    else:
        return x

model_data['WARD'] = model_data['WARD'].apply(handle_ward)

# Define independent and dependent variables
y = data['SERVICETYPE_NUMBER'].values
X = model_data.values

# Split data into training and test samples of 70%-30%
X_train, X_test, y_train, y_test = train_test_split(X,y, test_size = .3, random_state=522, stratify=y)

# n_estimators = number of trees in the forest
# min_samples_leaf = minimum number of samples required to be at a leaf node for the tree
rf = RandomForestClassifier(n_estimators=2500, min_samples_leaf=5, random_state=522)
rf.fit(X_train, y_train)
y_pred = rf.predict(X_test)
print(y_pred)

[15 15 12 ... 15 21 15]

print('Accuracy: ', accuracy_score(y_test, y_pred))

Accuracy:  0.6824769433465085

data = merged[['SERVICETYPECODEDESCRIPTION', 'SERVICETYPE_NUMBER', 'QUADRANT', 'CLUSTER', 'WARD', 'ORGANIZATIONACRONYM', 'STATUS_CODE', 'STATUS_CODE_NUMBER']]
data.reset_index(inplace=True)
data.head()

data1 = pd.get_dummies(data=data,columns=['QUADRANT'])
data1.head()

model_data1 = data1.drop(['SERVICETYPECODEDESCRIPTION', 'SERVICETYPE_NUMBER', 'STATUS_CODE', 'ORGANIZATIONACRONYM'], axis=1)
model_data1.head()

model_data1['WARD'] = model_data1['WARD'].apply(handle_ward)

y = data['SERVICETYPE_NUMBER'].values
X = model_data1.values

# Split data into training and test samples of 70%-30%
X_train, X_test, y_train, y_test = train_test_split(X,y, test_size = .3, random_state=522, stratify=y)

# n_estimators = number of trees in the forest
# min_samples_leaf = minimum number of samples required to be at a leaf node for the tree
rf = RandomForestClassifier(n_estimators=2500, min_samples_leaf=5, random_state=522)
rf.fit(X_train, y_train)
y_pred = rf.predict(X_test)
print(y_pred)

[15 15 12 ... 15 15 15]

print('Accuracy: ', accuracy_score(y_test, y_pred))

Accuracy:  0.4862895256916996

# Count of service requests per cluster
cluster_count = merged.groupby('NAME').size().reset_index(name='counts')
cluster_count.head()

# merge with original file
neighborhood = pd.merge(neighborhood, cluster_count, on='NAME')
neighborhood.head()

temp = neighborhood.sort_values(['counts'], ascending=[False])
temp[['NAME', 'NBH_NAMES', 'counts']]

# Viewing the map
search_result = gis.content.search("Neighborhood_Service_Requests")
search_result[0]

import scipy.stats
merged.columns

Index(['ADDDATE', 'CITY', 'DETAILS', 'INSPECTIONDATE', 'INSPECTIONFLAG',
       'INSPECTORNAME', 'Join_Count', 'LATITUDE', 'LONGITUDE',
       'MARADDRESSREPOSITORYID', 'NAME', 'NBH_NAMES', 'OBJECTID',
       'ORGANIZATIONACRONYM', 'PRIORITY', 'RESOLUTIONDATE', 'SERVICECALLCOUNT',
       'SERVICECODE', 'SERVICECODEDESCRIPTION', 'SERVICEDUEDATE',
       'SERVICEORDERDATE', 'SERVICEORDERSTATUS', 'SERVICEREQUESTID',
       'SERVICETYPECODEDESCRIPTION', 'STATE', 'STATUS_CODE', 'STREETADDRESS',
       'TYPE', 'WARD', 'WEB_URL', 'XCOORD', 'YCOORD', 'ZIPCODE', 'SHAPE',
       'QUADRANT', 'CLUSTER', 'SERVICETYPE_NUMBER', 'STATUS_CODE_NUMBER'],
      dtype='object')

df = merged[['NAME', 'SERVICECODEDESCRIPTION']]

# Extract the most frequently occuring service request type, and its count
df1 = df.groupby('NAME').agg(lambda x: scipy.stats.mode(x)[0][0])
df2 = df.groupby('NAME').agg(lambda x: scipy.stats.mode(x)[1][0])

df1.reset_index(inplace=True)
df2.reset_index(inplace=True)
df2 = df2.rename(columns={'SERVICECODEDESCRIPTION':'SERVICECODEDESCRIPTION_COUNT'})

# merge the two datasets
final_df = pd.merge(df1, df2, on='NAME')
final_df.head()

# merge it with neighborhood clusters
neighborhood_data = pd.merge(neighborhood, final_df, on='NAME')

# view the map
search_result = gis.content.search("Neighborhood_Service_DC")
search_result[0]