<jupyter_start><jupyter_text>Predicting number of victims in collisions in CaliforniaGame plan:- Consider data from June 3, 2020 - June 3, 2021<jupyter_code># basics
import pandas as pd
import numpy as np
import shap
import sqlite3

# initial stab at the problem
from sklearn.model_selection import train_test_split

# displaying
np.set_printoptions(threshold=80)
pd.set_option('display.max_columns', 80)
pd.set_option('display.max_rows', 80)
con = sqlite3.connect('../../../iCloud/Employment/Plentina/switrs.sqlite')

query_injured = """
SELECT c.injured_victims, 
       c.case_id, 
       c.collision_date, 
       c.collision_time,        
       c.alcohol_involved, 
       SUBSTR(c.county_city_location, -4, 2) AS county_code, 
       c.lighting,
       c.road_surface, 
       c.road_condition_1, 
       c.weather_1,
       c.intersection, 
       c.state_highway_indicator,
       p.party_age,
       p.cellphone_use_type,
       p.party_drug_physical
FROM `collisions` AS c
LEFT JOIN `parties` AS p
ON c.case_id = p.case_id
WHERE c.collision_date IS NOT NULL 
AND c.collision_time IS NOT NULL
AND c.chp_beat_type IS NOT 'interstate'
AND p.party_type IS 'driver'
AND p.at_fault = 1
AND date(c.collision_date) BETWEEN date('2020-06-03') and date('2021-06-03')
"""

df_injuredMain = pd.read_sql_query(query_injured, con, parse_dates=['collision_date'])

df_injured = df_injuredMain.copy()

df_injured.isna().sum()
# df_injured['weather_1'] = df_injured['weather_1'].fillna('-1')
# df_injured['lighting'] = df_injured['lighting'].fillna(-1)
# df_injured['road_surface'] = df_injured['road_surface'].fillna(-1)
# df_injured['road_condition_1'] = df_injured['road_condition_1'].fillna(-1)

df_injured['alcohol_involved'] = df_injured['alcohol_involved'].fillna(0).astype(int)
df_injured['intersection'] = df_injured['intersection'].fillna(0).astype(int)
df_injured['state_highway_indicator'] = df_injured['state_highway_indicator'].fillna(
    0).astype(int)

missingCategorical = ['lighting', 'road_surface', 'road_condition_1', 'weather_1',
'cellphone_use_type', 'party_drug_physical']
for var in missingCategorical:
    df_injured[var] = df_injured[var].fillna('-1')

df_injured['party_age'] = df_injured['party_age'].fillna(-1)

df_injured.isna().sum()

display(df_injured['weather_1'].value_counts())
display(df_injured['lighting'].value_counts())
display(df_injured['road_surface'].value_counts())
display(df_injured['road_condition_1'].value_counts())
df_injuredCleaned = df_injured.loc[(df_injured['lighting'] != 'G') &
    (df_injured['road_surface'] != 'H') &
    (df_injured['road_surface'] != 'I') &
    (df_injured['road_surface'] != 'J') &
    (df_injured['cellphone_use_type'] != '4')].reset_index(drop=True)

df_injuredCleaned['collision_hour'] = pd.to_datetime(
    df_injuredCleaned['collision_time'], format='%H:%M:%S').dt.hour.astype(str)
df_injuredCleaned['collision_day_of_week'] = pd.to_datetime(
    df_injuredCleaned['collision_date']).dt.dayofweek.astype(str)
df_injuredCleaned['collision_month'] = pd.to_datetime(
    df_injuredCleaned['collision_date']).dt.month.astype(str)
df_injuredCleaned
# SELECT case_id, latitude, longitude, collision_date, SUBSTR(county_city_location, -4, 2) AS county_code,
# collision_time, collision_severity,
# severe_injury_count, other_visible_injury_count,
# complaint_of_pain_injury_count, pedestrian_killed_count,
# pedestrian_injured_count, bicyclist_killed_count,
# bicyclist_injured_count, motorcyclist_killed_count,
# motorcyclist_injured_count, killed_victims, injured_victims,
# party_count, primary_collision_factor, pcf_violation_code,
# pcf_violation_category, pcf_violation, pcf_violation_subsection,
# pedestrian_collision, bicycle_collision, motorcycle_collision, truck_collision,
# hit_and_run

df_dummies = pd.get_dummies(df_injuredCleaned[['county_code', 'lighting', 'road_surface',
                            'road_condition_1', 'weather_1', 'collision_hour', 'collision_day_of_week', 
                            'cellphone_use_type','party_drug_physical']])

dataset = pd.concat([df_dummies, df_injuredCleaned[[
                    'alcohol_involved', 'intersection', 'state_highway_indicator', 'party_age', 'injured_victims']]], axis=1)

X = dataset.iloc[:, :133]
y = dataset['injured_victims']

X_train, X_test, y_train, y_test = train_test_split(
    X, y, test_size=.3, random_state=875)


display(y.value_counts())

print("Number of cases with injured victims:")
display(y.value_counts()[1:].sum())

from sklearn.tree import DecisionTreeRegressor
from sklearn.linear_model import LinearRegression
from sklearn.ensemble import RandomForestRegressor, AdaBoostRegressor, GradientBoostingRegressor
from sklearn.metrics import mean_absolute_error

lr_rmodel = LinearRegression().fit(X_train, y_train)
mae_lr = mean_absolute_error(y_test, lr_rmodel.predict(X_test))
print('Linear Regression Mean Absolute Error is:', mae_lr)

X_trees = df_injuredCleaned.iloc[:, 4:]
y_trees = df_injuredCleaned['injured_victims']

X_trees[['county_code', 'lighting', 'road_surface',
         'road_condition_1', 'weather_1', 'collision_hour', 'collision_day_of_week',
         'cellphone_use_type', 'party_drug_physical']] = X_trees[['county_code', 'lighting', 'road_surface',
         'road_condition_1', 'weather_1', 'collision_hour', 'collision_day_of_week',
         'cellphone_use_type', 'party_drug_physical']].astype("category")

X_train2, X_test2, y_train2, y_test2 = train_test_split(
    X_trees, y_trees, test_size=.3, random_state=875)

dt_model = DecisionTreeRegressor(random_state=875).fit(X_train, y_train)
mae_dt = mean_absolute_error(y_test2, dt_model.predict(X_test))
print('Decision Tree Regressor Mean Absolute Error is:', mae_dt)
rf_rmodel = RandomForestRegressor(random_state=875).fit(X_train, y_train)
rf_mae = mean_absolute_error(y_test, rf_rmodel.predict(X_test))
print('Random Forest Regressor Mean Absolute Error is:', rf_mae)
ab_rmodel = AdaBoostRegressor(random_state=875).fit(X_train, y_train)
ab_mae = mean_absolute_error(y_test, ab_rmodel.predict(X_test))
print('AdaBoost Regressor Mean Squared Error is:', ab_mae)
gbm_rmodel1 = GradientBoostingRegressor(random_state=875).fit(X_train, y_train)
gbm_mae = mean_absolute_error(y_test, gbm_rmodel1.predict(X_test))
print('Gradient Boosting Regressor Mean Squared Error is:', gbm_mae)

pd.DataFrame({'Model':['Linear Regression', 'Decision Tree', 'Random Forest Regressor', 'AdaBoost Regressor', 'Gradient Boosting Regressor'],
              'MAE': [mae_lr, mae_dt, rf_mae, ab_mae, gbm_mae]})

features = list(X.columns)

explainer = shap.TreeExplainer(gbm_rmodel1)

shap_values = explainer.shap_values(X_train)
shap.summary_plot(shap_values, X_train,
                  feature_names=features, plot_type="bar")
shap.summary_plot(shap_values, X_train, feature_names=features)

#i = 'intersection'
shap.initjs()
shap.force_plot(explainer.expected_value[0],
                shap_values[25], X_test.iloc[25], feature_names=features)

shap.decision_plot(
    explainer.expected_value[0], shap_values[0], feature_names=list(features))

shap.dependence_plot("lighting_dark with street lights", shap_values, X_train, feature_names=features)

shap.dependence_plot("intersection", shap_values,
                     X_train, feature_names=features)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12677789934354486, 'fraction_numerical': 0.01449671772428884, 'mean_word_length': 6.32032032032032, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 0, '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': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '301906', 'license': 'MIT', 'n_tokens_mistral': 2842, 'n_tokens_neox': 2631, 'n_words': 418, 'path': 'california-prediction.ipynb', 'repo_name': 'nkespiritu/california-collisions-dashboard', 'size': '7312'}