<jupyter_start><jupyter_text>To convert the date, we will split it into two columns. One will be a float64 in units of months (do we ignore the year?). The other will be a float64 in units of hours.<jupyter_code>def null_values(df, return_table=True):
    mis_val = df.isnull().sum()
    mis_val_percent = 100 * mis_val / len(df)
    mis_val_table = pd.concat([mis_val, mis_val_percent], axis=1)
    mis_val_table_ren_columns = mis_val_table.rename(
    columns = {0 : 'Missing Values', 1 : '% of Total Values'})
    mis_val_table_ren_columns = mis_val_table_ren_columns[
        mis_val_table_ren_columns.iloc[:, 1] != 0].sort_values(
        '% of Total Values', ascending=False).round(4)
    print("There are", len(df.columns), "columns in total.")
    print("There are " + str(mis_val_table_ren_columns.shape[0]) +
              " columns that have missing values.")
    if return_table:
        return mis_val_table_ren_columns<jupyter_output><empty_output><jupyter_text>The only nonmissing features are the ID, the date and time, and the last name.<jupyter_code>miss_values = null_values(arrests)
pd.set_option('display.max_rows', None)
miss_values<jupyter_output>There are 12 columns in total.
There are 9 columns that have missing values.<jupyter_text>To impute missing data in 'Sex', all nan values should be replaced with 'Unknown'.<jupyter_code>arrests['Sex'].unique()<jupyter_output><empty_output><jupyter_text>The two rows without first names are interesting. In the row with ID 2262, the last name is a placeholder '<NAME>'.<jupyter_code>arrests.loc[arrests['FirstName'] != arrests['FirstName']]<jupyter_output><empty_output><jupyter_text>What are 'nwt', 'doc', and 'jpg' doing in the 'Race' feature?<jupyter_code>arrests.loc[arrests['Race'] == 'nwt']
arrests.loc[arrests['Race'] == 'doc']<jupyter_output><empty_output><jupyter_text>We see that there are multiple descriptions that correspond to the same statute. Therefore, we drop the StatuteDescription feature because it does not add anything to the data.If we keep the names, it is reasonable to perform feature engineering that assigns a unique number to a full name, including the suffix if applicable, since we see that one person can and often commits more than one offense. We will have to convert every name to uppercase, since the capitalization is inconsistent.There are 472 unique statutes. A OneHotEncoder will result in the number of features increasing to about 480.<jupyter_code>arrests['NameSuffix'].unique()
arrests['StatuteDescription'].value_counts()
arrests['Statute'].value_counts()
arrests['Statute'].nunique()<jupyter_output><empty_output><jupyter_text>Data cleaning We drop the ArrestID and StatuteDescription because the former is useless and the latter is redundant with Statute. In fact, there are many descriptions that can potentially refer to the same statute. We also drop the names since there are too many unique names for an analysis to be useful with them kept.<jupyter_code>from sklearn.preprocessing import OneHotEncoder
arrests = pd.read_csv('Arrests.csv')
useless_and_redundant = ['ArrestID', 'StatuteDescription']
names = ['FirstName', 'LastName', 'MiddleName', 'NameSuffix']
arrests.drop(useless_and_redundant, axis=1, inplace=True)
arrests.drop(names, axis=1, inplace=True)
arrests['Year'] = pd.to_datetime(arrests['ArrestDatetime']).apply(
    lambda x: float(x.strftime('%Y')) if x == x else np.nan)
arrests['Month'] = pd.to_datetime(arrests['ArrestDatetime']).apply(
    lambda x: float(x.strftime('%m')) if x == x else np.nan) 
arrests['Day'] = pd.to_datetime(arrests['ArrestDatetime']).apply(
    lambda x: float(x.strftime('%d')) if x == x else np.nan) 
arrests['Time'] = pd.to_datetime(arrests['ArrestDatetime']).apply(
    lambda x: float(x.strftime('%H')) + 1/60 * float(x.strftime('%M')) + 1/3600 * float(x.strftime('%S'))
    if x == x else np.nan)
def convert_housenumber(x):
    if x == x:
        return str(int(x))
    else:
        return ''

arrests['HouseNumber'] = arrests['HouseNumber'].apply(convert_housenumber)
arrests['Address'] = arrests['HouseNumber'] + ' ' + arrests['Street'] + ', CHARLOTTESVILLE, VA'
arrests.drop(['ArrestDatetime', 'HouseNumber', 'Street'], axis=1, inplace=True)<jupyter_output><empty_output><jupyter_text>We will use OneHotEncoding for race and sex.<jupyter_code>onehot = OneHotEncoder()

def convert_races(x):
    if x == 'Unknown' or x == 'nwt' or x == 'doc' or x == 'jpg' or x != x:
        return 'Unknown Race'
    else:
        return x

def convert_sex(x):
    if x == 'Unknown' or x != x:
        return 'Unknown Sex'
    else:
        return x
    
arrests['Race'] = arrests['Race'].apply(convert_races)    
arrests['Sex'] = arrests['Sex'].apply(convert_sex)

cat_onehot = arrests[['Race', 'Sex']]
mtx_onehot = onehot.fit_transform(cat_onehot).toarray()
df_onehot = pd.DataFrame(mtx_onehot, columns=np.concatenate(onehot.categories_))
arrests.drop(['Race', 'Sex'], axis=1, inplace=True)
arrests = pd.concat([arrests, df_onehot], axis=1)
arrests.head()
miss_values = null_values(arrests)
pd.set_option('display.max_rows', None)
miss_values<jupyter_output>There are 14 columns in total.
There are 2 columns that have missing values.<jupyter_text>We shall remove those entries with missing addresses and statutes.<jupyter_code>arrests.dropna(subset=['Address', 'Statute'], how='any', inplace=True)
miss_values = null_values(arrests)
pd.set_option('display.max_rows', None)
miss_values
arrests.head(10)
from geopy.extra.rate_limiter import RateLimiter

locator = geopy.Nominatim(user_agent="myGeocoder")
geocode = RateLimiter(locator.geocode, min_delay_seconds=1)
df = arrests.head(10)
# location = locator.geocode("<NAME>, Charlottesville, VA")
df['Location'] = df['Address'].apply(geocode)
df['Point'] = df['Location'].apply(lambda loc: tuple(loc.point) if loc else None)
df[['Latitude', 'Longitude', 'Altitude']] = pd.DataFrame(df['Point'].tolist(), index=df.index)

arrests
arrests['Address']<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup