<jupyter_start><jupyter_text>Load in 2009 crime data, test it out, see what we need to do<jupyter_code># Read Data source
dfs_09 = pd.read_csv('data/HPD_Crime/UCR09_crime.csv')
dfs_09.head()<jupyter_output><empty_output><jupyter_text>Look at how many rows and what the column names are, we needto get rid of<jupyter_code>print(dfs_09.columns)
dfs_09.shape<jupyter_output>Index(['Unnamed: 0', 'ObjectID', 'Status', 'Score', 'Match_type', 'Side', 'X',
       'Y', 'Match_addr', 'ARC_Street', 'ARC_City', 'ARC_State', 'ARC_ZIP',
       'Field1', 'Address', 'City', 'State', 'coords.x1', 'coords.x2', 'X1',
       'Date', 'Hour', 'Offense Type', 'Beat', 'Premise', 'Block Range',
       'Street Name', 'Type', 'Suffix', '# Of Offenses', 'X__1', 'Address.1',
       'City.1', 'State.1'],
      dtype='object')<jupyter_text>Drop These rows and make new column names that will go with all sheets<jupyter_code>dfs_09_clean = dfs_09.drop(columns=['Unnamed: 0', 'ObjectID', 'Status', 'Score', 'Match_type', 'Side', 'Match_addr', 'ARC_Street', 'ARC_City', 'ARC_State','ARC_ZIP', 
                    'Field1', 'coords.x1', 'coords.x2', 'X1', 'Premise','Suffix', '# Of Offenses', 'X__1', 'Address.1', 'City.1', 'State.1'])
print(dfs_09_clean.columns)
columnNames = ['X', 'Y', 'Address', 'City', 'State', 'Date', 'Hour', 'OffenseType',
       'Beats', 'BlockRange', 'StreetName', 'Type']
dfs_09_clean.columns = columnNames
print(dfs_09_clean.columns)
dfs_09_clean.to_csv('data/HPD_Crime/Crime_09.csv',index=False)<jupyter_output><empty_output><jupyter_text>Repeat with other years<jupyter_code>dfs_10 = pd.read_csv('data/HPD_Crime/UCR10_crime.csv')
dfs_11 = pd.read_csv('data/HPD_Crime/UCR11_crime.csv')

dfs_12 = pd.read_csv('data/HPD_Crime/UCR12_crime.csv')
dfs_13 = pd.read_csv('data/HPD_Crime/UCR13_crime.csv')
dfs_14 = pd.read_csv('data/HPD_Crime/UCR14_crime.csv')
dfs_15 = pd.read_csv('data/HPD_Crime/UCR15_crime.csv')
print(dfs_10.shape)
print(dfs_11.shape)
print(dfs_10.columns)
print(dfs_11.columns)
dfs_10_clean = dfs_10.drop(columns=['Unnamed: 0', 'ObjectID', 'Status', 'Score', 'Match_type', 'Side', 'Match_addr', 'ARC_Street', 'ARC_City', 'ARC_State','ARC_ZIP', 
                    'Field1', 'Field11', 'coords.x1', 'coords.x2', 'X1', 'Premise','Suffix', '# Of Offenses', 'X__1', 'Address.1', 'City.1', 'State.1'])
dfs_10_clean.columns = columnNames
print(dfs_10_clean.columns)
dfs_11_clean = dfs_11.drop(columns=['Unnamed: 0', 'ObjectID', 'Status', 'Score', 'Match_type', 'Side', 'Match_addr', 'ARC_Street', 'ARC_City', 'ARC_State','ARC_ZIP', 
                    'coords.x1', 'coords.x2', 'X1', 'Premise','Suffix', '# Of Offenses', 'Address.1', 'City.1', 'State.1', 'Field1'])
dfs_11_clean.columns = columnNames

print(dfs_11_clean.columns)
dfs_12.columns
dfs_12_clean = dfs_12.drop(columns=['Unnamed: 0', 'ObjectID', 'Status', 'Score', 'Match_type', 'Side', 'Match_addr', 'ARC_Street', 'ARC_City', 'ARC_State','ARC_ZIP', 
                    'coords.x1', 'coords.x2', 'X1', 'Premise','Suffix', '# Of Offenses', 'Address.1', 'City.1', 'State.1', 'Field1'])
dfs_12_clean.columns = columnNames
print(dfs_12_clean.columns)
dfs_13_clean = dfs_13.drop(columns=['Unnamed: 0', 'ObjectID', 'Status', 'Score', 'Match_type', 'Side', 'Match_addr', 'ARC_Street', 'ARC_City', 'ARC_State','ARC_ZIP', 
                    'coords.x1', 'coords.x2', 'X1', 'Premise','Suffix', '# Of Offenses', '# Of','Address.1', 'City.1', 'State.1', 'Field1'])
dfs_13_clean.columns = columnNames
print(dfs_13_clean.columns)
dfs_14_clean = dfs_14.drop(columns=['Unnamed: 0', 'ObjectID', 'Status', 'Score', 'Match_type', 'Side', 'Match_addr', 'ARC_Street', 'ARC_City', 'ARC_State','ARC_ZIP', 
                    'coords.x1', 'coords.x2', 'X1', 'Premise','Suffix', '# offenses', 'Address.1', 'City.1', 'State.1', 'Field1'])
dfs_14_clean.columns = columnNames
print(dfs_14_clean.columns)
dfs_15_clean = dfs_15.drop(columns=['Unnamed: 0', 'ObjectID', 'Status', 'Score', 'Match_type', 'Side', 'Match_addr', 'ARC_Street', 'ARC_City', 'ARC_State','ARC_ZIP', 
                    'coords.x1', 'coords.x2', 'X1', 'Premise','Suffix', '# offenses', 'Address.1', 'City.1', 'State.1', 'Field1'])
dfs_15_clean.columns = columnNames
print(dfs_15_clean.columns)
merged_df = pd.concat([dfs_09_clean, dfs_10_clean, dfs_11_clean, dfs_12_clean, dfs_13_clean, dfs_14_clean, dfs_15_clean])

merged_df.to_csv('data/HPD_Crime/Crime_09_15.csv',index=False)
merged_df.head()
HPDbeats = pd.read_csv('data/HPD_Crime/Houston_Police_Beats.csv')
merge_test = merged_df
HPDbeats.head()
HPDbeats = HPDbeats.drop(columns = ['OBJECTID_12', 'OBJECTID_1', 'OBJECTID', 'District', 'Beats_0'])
HPDbeats.head()
merge_test = merge_test.join(HPDbeats.set_index('Beats'), on='Beats')
merge_test.head()
merge_test.shape
merge_test.to_csv('data/HPD_Crime/Crime_09_15.csv',index=False)<jupyter_output><empty_output><jupyter_text>The rest of 2016<jupyter_code>aug_16 = pd.read_excel('data/HPD_Crime/dirty_data/aug16.xlsx')
sep_16 = pd.read_excel('data/HPD_Crime/dirty_data/sep16.xlsx')
oct_16 = pd.read_excel('data/HPD_Crime/dirty_data/oct16.xlsx')
nov_16 = pd.read_excel('data/HPD_Crime/dirty_data/nov16.xlsx')
dec_16 = pd.read_excel('data/HPD_Crime/dirty_data/dec16.xlsx')
aug_16.head()
df_16 = pd.concat([aug_16, sep_16, oct_16, nov_16, dec_16])
df_16 = df_16.drop(columns=['Suffix', '# offenses'])

test= df_16
test.head()

test["Type"] = test["Type"].replace({'-': 'ST'})
test.head()
address = test["BlockRange"].str.split("-", n = 1, expand = True) 
print(address.head())
address = address.drop(columns=[0])
newColumn = ["Address"]
address.columns = newColumn
address.head()
test["Block"] = address
test.head()

test.StreetName.astype(str)
test.Type.astype(str)
test.Block.astype(str)
test.head()
test['Address'] = test['Block'].str.cat(test['StreetName'],sep=" ")
test['Address'] = test['Address'].str.cat(test['Type'],sep = " ")
test.head()

test.rename(columns={'Beat':'Beats'}, inplace=True)
test.head()
test = test.join(HPDbeats.set_index('Beats'), on='Beats')

geocode_test = test.head(1000)
geocode_test

# lat = []
# lon = []

# for x in range(0,len(geocode_test)):
#     try:
#         print(geocode_test['Address'][x])
#         URL = 'https://nominatim.openstreetmap.org/search?q='+ geocode_test['Address'][x] + ',Houston,Tx&format=json'
#         row = requests.get(URL)
#         lat.append(row.json()[0]['lat'])
#         lon.append(row.json()[0]['lon'])
#         print('-----APPEND DONE------')
#     except:
#         lat.append('-')
#         lon.append('-')
#         pass
# 'https://nominatim.openstreetmap.org/search?q='+ df['Address'] + ',Houston,Tx
# geocode_test['lat'] = lat
# geocode_test['lon'] = lon

# geocode_test
# geocode_test = geocode_test[geocode_test.lat != '-']
# geocode_test.shape
# lat = []
# lon = []

# for x in range(0,len(test)):
#     try:
#         print(test['Address'][x])
#         URL = 'https://nominatim.openstreetmap.org/search?q='+ test['Address'][x] + ',Houston,Tx&format=json'
#         row = requests.get(URL)
#         lat.append(row.json()[0]['lat'])
#         lon.append(row.json()[0]['lon'])
#         print('-----APPEND DONE------')
#     except:
#         lat.append('-')
#         lon.append('-')
#         pass
# 'https://nominatim.openstreetmap.org/search?q='+ df['Address'] + ',Houston,Tx
# test['lat'] = lat
# test['lon'] = lon
# test.head(50)
# test1 = test[test.lat != '-']
# test1.shape
test["City"] = "Houston"
test["State"] = "TX"
test.head()
address_df = test[['Address', 'City', 'State']].copy()
address_df.head()
address_df.to_csv('data/HPD_Crime/address_16.csv',index=False)<jupyter_output><empty_output><jupyter_text>Rest of 2017 data<jupyter_code>jan_17 = pd.read_excel('data/HPD_Crime/dirty_data/jan17.xlsx')
feb_17 = pd.read_excel('data/HPD_Crime/dirty_data/feb17.xlsx')
mar_17 = pd.read_excel('data/HPD_Crime/dirty_data/mar17.xlsx')
apr_17 = pd.read_excel('data/HPD_Crime/dirty_data/apr17.xlsx')
may_17 = pd.read_excel('data/HPD_Crime/dirty_data/may17.xlsx')
jun_17 = pd.read_excel('data/HPD_Crime/dirty_data/jun17.xlsx')
jul_17 = pd.read_excel('data/HPD_Crime/dirty_data/jul17.xlsx')
aug_17 = pd.read_excel('data/HPD_Crime/dirty_data/aug17.xlsx')
sep_17 = pd.read_excel('data/HPD_Crime/dirty_data/sep17.xlsx')
oct_17 = pd.read_excel('data/HPD_Crime/dirty_data/oct17.xlsx')
nov_17 = pd.read_excel('data/HPD_Crime/dirty_data/nov17.xlsx')
dec_17 = pd.read_excel('data/HPD_Crime/dirty_data/dec17.xlsx')
df_17 = pd.concat([jan_17, feb_17, mar_17, apr_17, may_17, jun_17, jul_17, aug_17, sep_17, oct_17, nov_17, dec_17])
df_17 = df_17.drop(columns=['Suffix', 'Offenses', 'Block Range', 'Street Name'])
df_17.head()
df_17["Type"] = df_17["Type"].replace({'-': 'ST'})
address_17 = df_17["BlockRange"].str.split("-", n = 1, expand = True) 
print(address_17.head())
address_17 = address_17.drop(columns=[0])
newColumn = ["Address"]
address_17.columns = newColumn
address_17.head()
df_17["Block"] = address_17
df_17.head()

df_17.StreetName.astype(str)
df_17.Type.astype(str)
df_17.Block.astype(str)
df_17.head()
df_17['Address'] = df_17['Block'].str.cat(df_17['StreetName'],sep=" ")

df_17['Address'] = df_17['Address'].str.cat(df_17['Type'],sep = " ")
df_17.head()
df_17.rename(columns={'Beat':'Beats'}, inplace=True)
df_17 = df_17.join(HPDbeats.set_index('Beats'), on='Beats')
df_17["City"] = "Houston"
df_17["State"] = "TX"
df_17.head()
address_17_df = test[['Address', 'City', 'State']].copy()
address_17_df.head()
address_17_df.to_csv('data/HPD_Crime/address_17.csv',index=False)<jupyter_output><empty_output><jupyter_text>Merge 16 address with lat and long to original 16 data<jupyter_code>df_16 = test
df_16.head()
GeocodedAddress_16 = pd.read_csv('data/HPD_Crime/2016_GeocodedAddress.csv')
GeocodedAddress_16.head()<jupyter_output><empty_output><jupyter_text>Importing Geopandas for shapefile<jupyter_code>import matplotlib.pyplot as plt
import descartes
import geopandas as gpd
from shapely.geometry import Point, Polygon

%matplotlib inline
htown_map = gpd.read_file('data/Super_Neighborhoods/Super_Neighborhoods.shp')
htown_map.head()
fig,ax = plt.subplots(figsize = (15,15))
htown_map.plot(ax=ax)
# geocode_test["lat"] = geocode_test["lat"].astype(float)
# geocode_test["lon"] = geocode_test["lon"].astype(float)
# geocode_test.head()
crs = {'init':'epsg:4326'}
# geometry = [Point(xy) for xy in zip(geocode_test["lon"], geocode_test["lat"])]
# geometry[:3]
# geo_df = gpd.GeoDataFrame(geocode_test,
#                          crs = crs,
#                          geometry = geometry)

# geo_df.head()
# fig,ax = plt.subplots(figsize = (15,15))
# htown_map.plot(ax=ax, alpha = 0.4, color = "grey")
# geo_df.plot(ax=ax, markersize = 20, color = 'blue', marker = "^", label = "Crime")
# plt.legend(prop={"size":15})<jupyter_output><empty_output><jupyter_text>Spatial Join of geocode_test data and Houston Super Neighborhoods<jupyter_code># #Execute Spatial Join
# crime_with_hood = gpd.sjoin(geo_df,htown_map, how = "inner", op ="intersects")
# crime_with_hood.head()<jupyter_output><empty_output><jupyter_text>Now do it with the actual dataset, not the test dataset<jupyter_code>merge_test.head()
# merge_test.shape
merge_test["Y"] = merge_test["Y"].astype(float)
merge_test["X"] = merge_test["X"].astype(float)
geometry = [Point(xy) for xy in zip(merge_test["X"], merge_test["Y"])]
geometry[:3]
htownCrime_df = gpd.GeoDataFrame(merge_test,
                         crs = crs,
                         geometry = geometry)

htownCrime_df.head()
#Execute Spatial Join
crime_with_hoods = gpd.sjoin(htownCrime_df,htown_map, how = "inner", op ="intersects")
crime_with_hoods.head(50)

crime_with_hoods.columns
crime_with_hoods_df = pd.DataFrame(crime_with_hoods)
crime_with_hoods_df = pd.DataFrame(crime_with_hoods_df.drop(columns= ['geometry', 'Top10', 'WeCan', 'SnbrInfoUR', 'RECOGNITIO', 'COUNCIL_AC', 'cohgisCO_1', 'cohgisCOHG','index_right', 'OBJECTID']))
crime_with_hoods_df.head()
crime_with_hoods_df.to_csv('data/HPD_Crime/Crime_09_15.csv',index=False)
crime_with_hoods_df.replace('', np.nan, inplace=True)

crime_with_hoods_df.columns
crime_with_hoods_df['Date'] = pd.to_datetime(crime_with_hoods_df['Date'])

crime_with_hoods_df['Year'] = crime_with_hoods_df['Date'].dt.year
crime_with_hoods_df['Month'] = crime_with_hoods_df['Date'].dt.month
crime_with_hoods_df['Day'] = crime_with_hoods_df['Date'].dt.day
crime_with_hoods_df['Weekday'] = crime_with_hoods_df['Date'].dt.dayofweek
crime_with_hoods_df.dtypes
crime_with_hoods_df.isna().sum()
crime_with_hoods_df.to_csv('data/HPD_Crime/Crime_09_15.csv',index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.18952479503303352, 'fraction_numerical': 0.03725224866671973, 'mean_word_length': 5.846866485013624, 'pattern_counts': {'":': 1, '<': 40, '<?xml version=': 0, '>': 40, 'https://': 4, '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': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '311418', 'license': 'MIT', 'n_tokens_mistral': 5468, 'n_tokens_neox': 5092, 'n_words': 730, 'path': 'Notebooks/HPD_Crime .ipynb', 'repo_name': 'ppainuly/Houston-Five-o', 'size': '12563'}