<jupyter_start><jupyter_text>Data cleaningScripts to build and clean CFD data<jupyter_code># modules
import pandas as pd
import os
import geopandas as gpd
import json
import requests
import shapely
import datetime
# directories
data_dir = os.path.join('..', 'data')
raw_dir = os.path.join(data_dir, 'raw')
clean_dir = os.path.join(data_dir, 'clean')
# links to Charlottesville Open Data portal
baseurl = 'https://opendata.arcgis.com/datasets/'
parcel_area_url = '320d465ddf0d498796da6491e21f6dde_43.geojson'
parcel_area_detail_url = '0e9946c2a77d4fc6ad16d9968509c588_72.geojson'<jupyter_output><empty_output><jupyter_text>Get parcel dataThe first step is to get parcel data, base and details, and merge them together<jupyter_code># get parcel data
df = requests.get(baseurl + parcel_area_url).json()

# convert to spatial dataframe
df = gpd.GeoDataFrame.from_features(df)

# add CRS so we can map
df.crs = {'init' :'epsg:4326'}

# lower column names
df.columns = map(str.lower, df.columns)
df.head()
# get parcel data
df_detail = requests.get(baseurl + parcel_area_detail_url).json()

# convert to spatial dataframe
df_detail = gpd.GeoDataFrame.from_features(df_detail)

# lower column names
df_detail.columns = map(str.lower, df_detail.columns)

# drop geometry
df_detail.drop(['geometry','objectid'], axis=1,inplace=True)
df = df.merge(df_detail, on='gpin', how='left')
df.head()<jupyter_output><empty_output><jupyter_text>Get fire incident dataGet fire incident data and convert to GeoDataFrame.<jupyter_code># read in fire incidents
fire = pd.read_csv(os.path.join(raw_dir, '12_16to5_18FireResponses-cleaner.csv'))
fire.size
# remove empty lon/lat
fire.dropna(subset=['basic_incident_longitude_fd1_69','basic_incident_latitude_fd1_69'], inplace=True)
fire.size
# make coordinates
fire['coordinates'] = list(zip(fire.basic_incident_longitude_fd1_69, fire.basic_incident_latitude_fd1_69))
fire['coordinates'] = fire['coordinates'].apply(shapely.geometry.Point)

# make geodataframe
fire = gpd.GeoDataFrame(fire, geometry='coordinates')
fire.crs = {'init' :'epsg:4326'}
# convert to datetime
def to_date(dt_str):
    dt_str = str(dt_str)
    if (dt_str == 'nan'):
        return pd.NaT
    else:
        return datetime.datetime.strptime(str(dt_str), '%Y-%m-%dT%H:%M:%SZ')

fire['dt'] = pd.to_datetime(fire['apparatus_resource_dispatch_date_time_fd18_3'], format='%Y-%m-%dT%H:%M:%SZ')
fire
# split datetime to year month day hour minute
cols = ['year','month','day','hour','minute','second']
fire[cols] = pd.DataFrame([(x.year, x.month, x.day, x.hour, x.minute, x.second) for x in fire['dt']])
fire<jupyter_output><empty_output><jupyter_text>Join fire incidents to parcels using lon/lat in polygonsRather than trying to fuzzy match on address, see how well we can find the fire incident points in the parcel polygons.<jupyter_code>geo_p = df[['geometry', 'objectid']]
geo_f = fire[['coordinates','basic_incident_number_fd1']]
geo_p.plot()
geo_f.plot(marker='*', color='green', markersize=5)
# limit fires to inside Charlottesville proper
# NB: doesn't include UVa or some fire incidents in Albemarle County
cville_fires = gpd.sjoin(geo_f, geo_p, how='inner')
cville_fires
# drop geometry and convert to pandas data frame
cville_fires = pd.DataFrame(cville_fires.drop(columns=['coordinates','index_right']))

# add column of ones
cville_fires['fire'] = 1

# join back to parcel data frame
df = df.merge(cville_fires, how='left', on='objectid')

# fill fire NaNs with 0, b/c that's what they are
df['fire'].fillna(0, inplace=True)

# merge in other fire information
df = df.merge(fire, how='left', on='basic_incident_number_fd1')

df<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1291643910431458, 'fraction_numerical': 0.022665210267613325, 'mean_word_length': 6.615384615384615, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 17, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '322436', 'license': 'MIT', 'n_tokens_mistral': 1372, 'n_tokens_neox': 1305, 'n_words': 308, 'path': 'jupyter/data_cleaning.ipynb', 'repo_name': 'Smart-Cville/CID-2018-CFD-Challenge', 'size': '3662'}