<jupyter_start><jupyter_text>Data Extracting  Get crime data from Analyze Boston websitereference like: https://data.boston.gov/dataset/crime-incident-reports-august-2015-to-date-source-new-system/resource/12cb3883-56f5-47de-afa5-3b1cf61b257b- use a request to query the data from the api - store data in a dataframe using pandas framework We also drop column we will not use<jupyter_code>import pandas as pd

crime = pd.read_csv('data/crime.csv')
crime['OCCURRED_ON_DATE'] = pd.to_datetime(crime['OCCURRED_ON_DATE'])
crime['Lat'] = pd.to_numeric(crime['Lat'])
crime['Long'] = pd.to_numeric(crime['Long'])
print("strat date:", crime['OCCURRED_ON_DATE'].min())
print("end date:", crime['OCCURRED_ON_DATE'].max())
# drop redundance column
crime = crime.drop(['HOUR', 'Location', 'MONTH', 'OFFENSE_CODE_GROUP', 'REPORTING_AREA','SHOOTING', 'STREET', 'YEAR', 'UCR_PART', 'INCIDENT_NUMBER'], axis=1)
crime.describe()<jupyter_output>strat date: 2015-06-15 00:00:00
end date: 2019-11-22 23:14:00<jupyter_text>Get weather data from csv filesreference like: https://www.ncdc.noaa.gov/cdo-web/datasets/GHCND/stations/GHCND:USW00014739/detail- load data from csv file and keep only needed column<jupyter_code>weather = pd.read_csv('data/weather.csv')
weather['DATE'] =  pd.to_datetime(weather['DATE']).dt.date
weather = weather[['DATE', 'SNOW', 'TAVG']]
weather.describe()<jupyter_output><empty_output><jupyter_text>Data Visualization and Cleaning  Visualize weather dataWe start with weather data because there is only one thing we need which is average tempurature- Check null value in the dataset<jupyter_code>print(weather.isnull().sum(axis=0))<jupyter_output>DATE    0
SNOW    0
TAVG    1
dtype: int64<jupyter_text>- Find a null value Only one data is missing, we will remove this when we finish cleaning and export the cleaned data seet<jupyter_code>weather.loc[weather['TAVG'].isnull() == True]<jupyter_output><empty_output><jupyter_text>- Plot average tempurature<jupyter_code>import matplotlib.pyplot as plt
%matplotlib inline

plt.figure(figsize=(20,10))
plt.hist(weather['TAVG'], 50)
plt.show<jupyter_output>C:\Users\Chaya\Anaconda3\lib\site-packages\numpy\lib\histograms.py:829: RuntimeWarning: invalid value encountered in greater_equal
  keep = (tmp_a >= first_edge)
C:\Users\Chaya\Anaconda3\lib\site-packages\numpy\lib\histograms.py:830: RuntimeWarning: invalid value encountered in less_equal
  keep &= (tmp_a <= last_edge)<jupyter_text>Visualize crime data - Check null value in data<jupyter_code>crime.isnull().sum(axis=0)<jupyter_output><empty_output><jupyter_text>- Romove district that has value "External" There are created for test.<jupyter_code>crime = crime.drop(crime.loc[crime['DISTRICT'] == 'External'].index.tolist())
crime['DISTRICT'].unique()<jupyter_output><empty_output><jupyter_text>- Remove null value that cannot be determinate We can use machine learning to fill null value of district and location, but if both location and district is null we cannot use that. Since, there is only 48 rows from 32,000 that have null values on both location and district, we will just remove these rows.<jupyter_code>crime.loc[(crime['DISTRICT'].isnull() == True) & ((crime['Lat'].isnull() == True) | (crime['Lat'] == -1))].count()
crime['DISTRICT'] = crime['DISTRICT'].fillna("none")
crime = crime.drop(crime.loc[(crime['DISTRICT'] == 'none') & ((crime['Lat'].isnull() == True) | (crime['Lat'] == -1))].index.tolist())
# we also drop data that have lat and long at -1
crime = crime.drop(crime.loc[crime['Lat'] == -1].index.tolist())
# we also drop data that have external district, there are testing data
crime.loc[(crime['DISTRICT'] == 'none') & ((crime['Lat'].isnull() == True) | (crime['Lat'] == -1))].count()<jupyter_output><empty_output><jupyter_text>- Find the mean "Lat" and "Long" of each district for fill null value<jupyter_code>crime_without_null = crime[['DISTRICT', 'Lat', 'Long']]
crime_without_null = crime_without_null.dropna(subset=['Lat', 'Long'])
crime_without_null['Lat'] = pd.to_numeric(crime_without_null['Lat'])
crime_without_null['Long'] = pd.to_numeric(crime_without_null['Long'])
mean_district = crime_without_null.groupby('DISTRICT').mean()
mean_district<jupyter_output><empty_output><jupyter_text>- Fill Lat and Long null value of each district with its mean lat and long<jupyter_code>crime['Lat'] = crime.apply(lambda x : mean_district[mean_district.index == x['DISTRICT']]['Lat'][0] if not x['Lat'] == x['Lat'] else x['Lat'], axis = 1)
crime['Long'] = crime.apply(lambda x : mean_district[mean_district.index == x['DISTRICT']]['Long'][0] if not x['Long'] == x['Long'] else x['Long'], axis = 1)
crime.isnull().sum()<jupyter_output><empty_output><jupyter_text>- Plot location of each districtWe can see that none value is all over the place and some location have mislabelled district<jupyter_code># change all lat and long to number for plot
crime['Lat'] = pd.to_numeric(crime['Lat'])
crime['Long'] = pd.to_numeric(crime['Long'])
# plot each district location
districts = sorted(crime['DISTRICT'].unique())
plt.figure(figsize=(20,10))
for district in districts:
    if district == "none":
        plt.scatter(crime[crime['DISTRICT'] == district]['Long'],crime[crime['DISTRICT'] == district]['Lat'],label=district,alpha = 1, color='black')
    else :
        plt.scatter(crime[crime['DISTRICT'] == district]['Long'],crime[crime['DISTRICT'] == district]['Lat'],label=district,alpha = 0.5)
plt.legend()
plt.show()
from sklearn.neighbors import KNeighborsClassifier

# change none value back to null for train
crime['DISTRICT'] = crime.apply(lambda x : None if x['DISTRICT'] == 'none' else x['DISTRICT'], axis = 1)

# create dataframe for train
trainset = crime[crime['DISTRICT'].isnull() == False][0:21000] # data is unclean should use only some of them
features=list(zip(trainset['Long'],trainset['Lat']))
print(features[0:5])
model = KNeighborsClassifier(n_neighbors=30)

# Train the model using the training sets
model.fit(features,trainset['DISTRICT'])

# Predict Output
predicted = model.predict([[0,2]])
print(predicted)
features = list(zip(crime['Long'],crime['Lat']))
predicted = model.predict(features)
crime['DISTRICT'] = predicted
districts = crime['DISTRICT'].unique().tolist()
plt.figure(figsize=(20,10))
for district in districts:
    plt.scatter(crime[crime['DISTRICT'] == district]['Long'],crime[crime['DISTRICT'] == district]['Lat'],label=district,alpha = 0.5)
plt.legend()
plt.show()<jupyter_output><empty_output><jupyter_text>Export to csv file for analysis<jupyter_code>crime['DATE'] = pd.to_datetime(crime['OCCURRED_ON_DATE']).dt.date
crimeandweather = pd.merge(crime, weather, on='DATE',  how='left')
crimeandweather.head()
crimeandweather = crimeandweather.dropna(subset=['TAVG'])
crimeandweather.head()
crimeandweather.to_csv("data/crimeandweather.csv", index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1569199358319965, 'fraction_numerical': 0.0179378737057022, 'mean_word_length': 7.154577883472057, 'pattern_counts': {'":': 1, '<': 47, '<?xml version=': 0, '>': 47, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, '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': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '32057', 'license': 'MIT', 'n_tokens_mistral': 2476, 'n_tokens_neox': 2244, 'n_words': 534, 'path': '.ipynb_checkpoints/data_preprocess-checkpoint.ipynb', 'repo_name': 'chaipi-chaya/Boston-Crime-Analysis', 'size': '6857'}