<jupyter_start><jupyter_text>Collisions Overview and Trend Viz  Import packages<jupyter_code>import pandas as pd
import altair as alt
from vega_datasets import data
import math
# set the data_folder to the folder containing switrs collisions data files
data_folder = "/Users/pobajaj/Berkeley/W209/collisions_data"
%cd $data_folder
%pwd
#alt.data_transformers.disable_max_rows()
#alt.data_transformers.enable('data_server')
alt.data_transformers.enable('json')<jupyter_output><empty_output><jupyter_text>Read collisions data for the past 10 years<jupyter_code>df_collisions_11_20 = {}
for year in range(2011, 2021):
  print(data_folder+"/"+str(year)+"/CollisionRecords.txt")
  df_collisions_11_20[year] = pd.read_csv(data_folder+"/"+str(year)+"/CollisionRecords.txt", error_bad_lines=False)
df_collisions_11_20[2011].head()
# combine all the dataframes into one
df_collisions_all = pd.concat(df_collisions_11_20, ignore_index=True)
df_collisions_all.columns
df_collisions_all.head()<jupyter_output><empty_output><jupyter_text>From the above, the fields COLLISION_DATE, COLLISION_TIME, CNTY_CITY_LOC, PCF_VIOL_CATEGORY, COLLISION_SEVERITY, and ROAD_COND_1 look interesting.<jupyter_code># convert the date and time strings to pandas date-time format
df_collisions_all["COLLISION_DATE_T"]= pd.to_datetime(
    df_collisions_all["COLLISION_DATE"].astype(str), format="%Y%m%d", errors='coerce')
df_collisions_all["COLLISION_TIME_T"]= pd.to_datetime(
    df_collisions_all["COLLISION_DATE"].astype(str)+ \
    df_collisions_all["COLLISION_TIME"].astype(str), format="%Y%m%d%H%M", errors='coerce') 
df_collisions_all = df_collisions_all[['CASE_ID', 'ACCIDENT_YEAR', 'COLLISION_DATE_T', 'COLLISION_TIME_T', 'CNTY_CITY_LOC', \
                                       'PCF_VIOL_CATEGORY', 'COLLISION_SEVERITY', 'ROAD_COND_1', 'COUNT_PED_KILLED', \
                                       'ALCOHOL_INVOLVED', 'PEDESTRIAN_ACCIDENT', 'BICYCLE_ACCIDENT', 'MOTORCYCLE_ACCIDENT', 'TRUCK_ACCIDENT', \
                                       'COUNT_PED_INJURED', 'COUNT_BICYCLIST_KILLED', 'COUNT_BICYCLIST_INJURED', \
                                      'COUNT_MC_KILLED', 'COUNT_MC_INJURED', 'LATITUDE', 'LONGITUDE' ]]
df_collisions_all.head()
# save the combined dataframe to a csv file
df_collisions_all.to_csv(data_folder+"/df_collisions_all.csv")
df_collisions_all = pd.read_csv(data_folder+"/df_collisions_all.csv", infer_datetime_format=True)
# set datatypes
df_collisions_all[['COLLISION_DATE_T', 'COLLISION_TIME_T']] = df_collisions_all[['COLLISION_DATE_T', 'COLLISION_TIME_T']].apply(pd.to_datetime)<jupyter_output>/Applications/anaconda3/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3063: DtypeWarning: Columns (6,7,14) have mixed types.Specify dtype option on import or set low_memory=False.
  interactivity=interactivity, compiler=compiler, result=result)<jupyter_text>Group collisions by date and build overview trend chart<jupyter_code>collisions_by_date = df_collisions_all.groupby(['COLLISION_DATE_T'])['CASE_ID'].aggregate(['count'])
collisions_by_date = collisions_by_date.reset_index()
line_chart = alt.Chart(collisions_by_date).mark_line(tooltip=True, size=1).encode(
    x=alt.X(
        'COLLISION_DATE_T:T',
        title="Date"
    ),
    y=alt.Y(
        'sum(count):Q',
         title="Number of Collisions"
        
    )
).properties(
    width=800,
    height=300,
    title = "Trend of California Traffic Collisions Over the Past 10 Years"
).interactive()

point_chart = alt.Chart(collisions_by_date).mark_point(tooltip=True, size=50, opacity=0).encode(
    x=alt.X(
        'COLLISION_DATE_T:T',
        title="Date"
    ),
    y=alt.Y(
        'sum(count):Q',
         title="Number of Collisions"
        
    )
).properties(
    width=800,
    height=300,
).interactive()

line_chart = line_chart+point_chart.add_selection(alt.selection_single())
line_chart
line_chart.save(data_folder+"/overview_line_chart.html")<jupyter_output><empty_output><jupyter_text>Trend and % of Collisions chart  Method 1. Use Altair binding_checkbox for displaying choices<jupyter_code># create a checkbox selection for alcohol involved cases
check_alcohol = alt.binding_checkbox(name="% of Collisions Involving Alcohol")
alcohol_selection = alt.selection_single(bind=check_alcohol, name="Alcohol")
alcohol_checkbox_condition = alt.condition(alcohol_selection,
                                        alt.value(0.0),
                                        alt.value(1.0)
                                       )

# prepare data for alcohol involved collisions
df_alcohol = df_collisions_all.loc[df_collisions_all['ALCOHOL_INVOLVED'] == 'Y']
alcohol_by_date = df_alcohol.groupby(['COLLISION_DATE_T'])['CASE_ID'].aggregate(['count'])
alcohol_by_date = alcohol_by_date.reset_index()

df_collisions_alcohol = collisions_by_date
df_collisions_alcohol = df_collisions_alcohol.rename(columns={"COLLISION_DATE_T": "Date", "count": "Total Collisions"})
df_collisions_alcohol['Collisions Involving Alcohol'] = alcohol_by_date['count']
df_collisions_alcohol['Alcohol'] = df_collisions_alcohol['Collisions Involving Alcohol'] / df_collisions_alcohol["Total Collisions"]

alcohol_line = alt.Chart(df_collisions_alcohol).mark_line(tooltip=True, color="red").encode(
    x=alt.X(
        'year(Date):O',
    ),
    y=alt.Y(
        'mean(Alcohol):Q',
        axis=alt.Axis(format='%'),
        title="% of Collisions"
    ),
    opacity=alcohol_checkbox_condition
).properties(
    width=800,
    height=300,
    title = ""
)
alcohol_points = alt.Chart(df_collisions_alcohol).mark_point(tooltip=True, color="red").encode(
    x=alt.X(
        'year(Date):O',
    ),
    y=alt.Y(
        'mean(Alcohol):Q',
        axis=alt.Axis(format='%'),
        title="% of Collisions"
    ),
    opacity=alcohol_checkbox_condition
)

alcohol_chart = alcohol_line+alcohol_points
alcohol_chart = alcohol_chart.add_selection(alcohol_selection)
df_collisions_alcohol
# create a checkbox selection for pedestrian involved cases
check_pedestrian = alt.binding_checkbox(name="% of Collisions Involving Pedestrians")
pedestrian_selection = alt.selection_single(bind=check_pedestrian, name="Pedestrians")
pedestrian_checkbox_condition = alt.condition(pedestrian_selection,
                                        alt.value(0.0),
                                        alt.value(1.0)
                                       )

# prepare data for pedestrians involved collisions
df_pedestrian = df_collisions_all.loc[df_collisions_all['PEDESTRIAN_ACCIDENT'] == 'Y']
pedestrian_by_date = df_pedestrian.groupby(['COLLISION_DATE_T'])['CASE_ID'].aggregate(['count'])
pedestrian_by_date = pedestrian_by_date.reset_index()

df_collisions_pedestrian = collisions_by_date
df_collisions_pedestrian = df_collisions_pedestrian.rename(columns={"COLLISION_DATE_T": "Date", "count": "Total Collisions"})
df_collisions_pedestrian['Collisions Involving Pedestrians'] = pedestrian_by_date['count']
df_collisions_pedestrian['Pedestrians'] = df_collisions_pedestrian['Collisions Involving Pedestrians'] / df_collisions_pedestrian["Total Collisions"]

pedestrian_line = alt.Chart(df_collisions_pedestrian).mark_line(tooltip=True, color="blue").encode(
    x=alt.X(
        'year(Date):O',
    ),
    y=alt.Y(
        'mean(Pedestrians):Q',
        axis=alt.Axis(format='%'),
        title="% of Collisions"
    ),
    opacity=pedestrian_checkbox_condition
)
pedestrian_points = alt.Chart(df_collisions_pedestrian).mark_point(tooltip=True, color="blue").encode(
    x=alt.X(
        'year(Date):O',
    ),
    y=alt.Y(
        'mean(Pedestrians):Q',
        axis=alt.Axis(format='%'),
        title="% of Collisions"
    ),
    opacity=pedestrian_checkbox_condition
)

pedestrian_chart = pedestrian_line+pedestrian_points
pedestrian_chart = pedestrian_chart.add_selection(pedestrian_selection)
#pedestrian_chart
# create a checkbox selection for pedestrian involved cases
check_bicycle = alt.binding_checkbox(name="% of Collisions Involving Bicycles")
bicycle_selection = alt.selection_single(bind=check_bicycle, name="Bicycle")
bicycle_checkbox_condition = alt.condition(bicycle_selection,
                                        alt.value(0.0),
                                        alt.value(1.0)
                                       )

# prepare data for pedestrians involved collisions
df_bicycle = df_collisions_all.loc[df_collisions_all['BICYCLE_ACCIDENT'] == 'Y']
bicycle_by_date = df_bicycle.groupby(['COLLISION_DATE_T'])['CASE_ID'].aggregate(['count'])
bicycle_by_date = bicycle_by_date.reset_index()

df_collisions_bicycle = collisions_by_date
df_collisions_bicycle = df_collisions_bicycle.rename(columns={"COLLISION_DATE_T": "Date", "count": "Total Collisions"})
df_collisions_bicycle['Collisions Involving Bicycles'] = bicycle_by_date['count']
df_collisions_bicycle['Bicycles'] = df_collisions_bicycle['Collisions Involving Bicycles'] / df_collisions_pedestrian["Total Collisions"]

bicycle_line = alt.Chart(df_collisions_bicycle).mark_line(tooltip=True, color="green").encode(
    x=alt.X(
        'year(Date):O',
    ),
    y=alt.Y(
        'mean(Bicycles):Q',
        axis=alt.Axis(format='%'),
        title="% of Collisions"
    ),
    opacity=bicycle_checkbox_condition
)
bicycle_points = alt.Chart(df_collisions_bicycle).mark_point(tooltip=True, color="green").encode(
    x=alt.X(
        'year(Date):O',
    ),
    y=alt.Y(
        'mean(Bicycles):Q',
        axis=alt.Axis(format='%'),
        title="% of Collisions"
    ),
    opacity=bicycle_checkbox_condition
)

bicycle_chart = bicycle_line+bicycle_points
bicycle_chart = bicycle_chart.add_selection(bicycle_selection)
#bicycle_chart
extra_charts = alcohol_chart + pedestrian_chart + bicycle_chart

layered_chart = alt.layer(
    year_chart,
    extra_charts,
).properties(
    width=800,
    height=300
).resolve_scale(
    y = 'independent'
)

layered_chart<jupyter_output><empty_output><jupyter_text>Method 2. Use mark_rect for displaying choices<jupyter_code># prepare data for Motor Cycle involved collisions
df_motorcycle = df_collisions_all.loc[df_collisions_all['MOTORCYCLE_ACCIDENT'] == 'Y']
motorcycle_by_date = df_motorcycle.groupby(['COLLISION_DATE_T'])['CASE_ID'].aggregate(['count'])
motorcycle_by_date = motorcycle_by_date.reset_index()

df_collisions_motorcycle = collisions_by_date
df_collisions_motorcycle = df_collisions_motorcycle.rename(columns={"COLLISION_DATE_T": "Date", "count": "Total Collisions"})
df_collisions_motorcycle['Collisions Involving MotorCycle'] = motorcycle_by_date['count']
df_collisions_motorcycle['Motor Cycles'] = df_collisions_motorcycle['Collisions Involving MotorCycle'] / df_collisions_motorcycle["Total Collisions"]

# prepare data for Truck involved collisions
df_truck = df_collisions_all.loc[df_collisions_all['TRUCK_ACCIDENT'] == 'Y']
truck_by_date = df_truck.groupby(['COLLISION_DATE_T'])['CASE_ID'].aggregate(['count'])
truck_by_date = truck_by_date.reset_index()

df_collisions_truck = collisions_by_date
df_collisions_truck = df_collisions_truck.rename(columns={"COLLISION_DATE_T": "Date", "count": "Total Collisions"})
df_collisions_truck['Collisions Involving Trucks'] = truck_by_date['count']
df_collisions_truck['Trucks'] = df_collisions_truck['Collisions Involving Trucks'] / df_collisions_truck["Total Collisions"]

df_collisions_by_type = df_collisions_alcohol
df_collisions_by_type['Pedestrians'] = df_collisions_pedestrian['Pedestrians']
df_collisions_by_type['Bicycles'] = df_collisions_bicycle['Bicycles']
df_collisions_by_type['Motor Cycles'] = df_collisions_motorcycle['Motor Cycles']
df_collisions_by_type['Trucks'] = df_collisions_truck['Trucks']

df_collisions_by_type
df_collisions_by_type1 = df_collisions_by_type.reset_index().melt(id_vars=['Date'], 
                                                                  value_vars=['Alcohol', 'Pedestrians', 'Bicycles', 'Motor Cycles', 'Trucks'], 
                                                                  var_name='% of Collisions Involving',
                                                                  value_name='pct')

df_collisions_by_type1['pct'] = pd.Series([round(val, 4) for val in df_collisions_by_type1['pct']])
df_collisions_by_type1
# define selection
df_collision_type = pd.DataFrame({'% of Collisions Involving': ['Alcohol', 'Pedestrians', 'Bicycles', 'Motor Cycles', 'Trucks']})

selection = alt.selection_multi(fields=['% of Collisions Involving'], empty='all')
color = alt.condition(selection, alt.Color('% of Collisions Involving:N', 
                                           legend=None, 
                                           scale=alt.Scale(scheme="category10")
                                          ), 
                      alt.value('lightgray'))
type_selector = alt.Chart(df_collision_type).mark_circle(tooltip=True, size=400).encode(
    y=alt.Y('% of Collisions Involving', title=""), 
    color=color,
    tooltip=[alt.Tooltip('% of Collisions Involving', title="Collisions Involving")
            ]
).add_selection(selection).properties(title="Select to View % of Collisions")

# percentage collisions charts
pct_line_chart = alt.Chart(df_collisions_by_type1).mark_line(point=True).encode(
    x='year(Date):O',
    y=alt.Y('mean(pct):Q', title="% of Collisions", axis=alt.Axis(format='%')),
    color=color
).transform_filter(selection).add_selection(selection)

pct_point_chart = alt.Chart(df_collisions_by_type1).mark_point(tooltip=True, size=50, opacity=0).encode(
    x='year(Date):O',
    y=alt.Y('mean(pct):Q', title="% of Collisions", axis=alt.Axis(format='%')),
    color=color,
    tooltip = [
        alt.Tooltip('year(Date):O', title='Year'),
        alt.Tooltip('mean(pct):Q', title="% of Collisions", format='%')
        ]
).transform_filter(selection).add_selection(alt.selection_single())
    
# total collisions charts
collisions_line_chart = alt.Chart(collisions_by_date).mark_line(point=True, color='black').encode(
    x=alt.X(
        'year(COLLISION_DATE_T):O',
        title="Year",
    ),
    y=alt.Y(
        'sum(count):Q',
         title="Number of Collisions"
    )
).add_selection(alt.selection_single())

collisions_point_chart = alt.Chart(collisions_by_date).mark_point(size=50, opacity=0).encode(
    x=alt.X(
        'year(COLLISION_DATE_T):O',
        title="Year"
    ),
    y=alt.Y(
        'sum(count):Q',
         title="Number of Collisions"
    ),
    tooltip = [
        alt.Tooltip('year(COLLISION_DATE_T):O', title='Year'),
        alt.Tooltip('sum(count):Q', title="Number of Collisions")
    ],
).add_selection(alt.selection_single())

total_collisions_chart = collisions_line_chart + collisions_point_chart
pct_charts = pct_line_chart + pct_point_chart

type_selector | pct_charts
layered_chart1 = alt.layer(
    total_collisions_chart,
    pct_charts
).properties(
    width=600,
    height=300,
    title="Trend and % of Collisions for Various Types"
).resolve_scale(
    y = 'independent'
).add_selection(alt.selection_single())

percent_collisions = alt.hconcat(layered_chart1, type_selector)
percent_collisions = percent_collisions.configure_axis(grid=True)

display(percent_collisions)

display(HTML("""
<p style="text-align:right">Use <b>Shift+Click</b> for Multi Selection</p>
"""))
percent_collisions.save(data_folder+"/percent_collisions.html")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12357426839601121, 'fraction_numerical': 0.007560451020009125, 'mean_word_length': 4.517439769866955, 'pattern_counts': {'":': 10, '<': 28, '<?xml version=': 0, '>': 28, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 25, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '600795', 'license': 'MIT', 'n_tokens_mistral': 5314, 'n_tokens_neox': 5104, 'n_words': 748, 'path': 'Collisions_Overview_Trend.ipynb', 'repo_name': 'poonamparhar/w209_final_project', 'size': '15343'}