EURO DEMO road 20 VS all roads - motorcycle accidents type

[yuvalbar23]

JSON:
{
"name": "highway_motor_accident_type_road20_vs_all_roads",
"data": {
"items":
[
{
"accident_type_hebrew": "התנגשות צד בצד",
"Road 20": 0.3889334403,
"Other Roads": 0.2008490427
},
{
"accident_type_hebrew": "התנגשות חזית באחור",
"Road 20": 0.2518043304,
"Other Roads": 0.1324212429
},
{
"accident_type_hebrew": "החלקה",
"Road 20": 0.2197273456,
"Other Roads": 0.3053821743
},
{
"accident_type_hebrew": "התנגשות חזית בצד",
"Road 20": 0.0769847634,
"Other Roads": 0.1970661791
},
{
"accident_type_hebrew": "התנגשות עם עצם דומם",
"Road 20": 0.0160384924,
"Other Roads": 0.0190193977
}
]
}
}

[galraij]

Amazing! It will be easy I guess to duplicate fir other vehicles types in the future.

[atalyaalon]

@yuvalbar23 @MichalOren great work! This really looks awesome!

I suggest creating an issue using the new template (see example here)

Also I believe you'll need to elaborate in the new issue regarding the logic of choosing the accident types to be presented (perhaps top 5 in the current road - in our case road 20)

Also - what do you think regarding vehicle type? Should we create this widget for motorcycle accidents only, or for all accidents without filtering motorcycle accidents?
I suggest perhaps creating 2 Widgets, one for all accidents, and one for a specific vehicle, depending on what's written in the news flash.

@galraij @yuvalbar23 @MichalOren any thoughts?

[MichalOren]

HI

• I am adding a new infographic template for this issue.
• I will elaborate regarding the logic of choosing the accident types to be presented.
• Regarding other vehicle types, i think that we or I should test it and run the query with other vehicle types just to see if that's make sense
• Same sugestion for 'all accidents' (no filter for motorbike)
  @atalyaalon @yuvalbar23

[atalyaalon]

Great @MichalOren !
After opening the issue, please ask one of our team members for an internal review

[yuvalbar23]

import pandas as pd

#Loading data
inv_mar = pd.read_csv('involved_markers_hebrew.csv',low_memory=False)

#Initialize parameters
road = 20
year_lower_bound = 2014
year_upper_bound = 2019
vehicle_types = [8,9,10,19]

#Query
df_road = inv_mar[(inv_mar['vehicle_vehicle_type'].isin(vehicle_types))&
(inv_mar['accident_year']>=year_lower_bound)&
(inv_mar['accident_year']<=year_upper_bound)&
(inv_mar['road1']==road)]

vc_road = df_road.drop_duplicates('provider_and_id')['accident_type_hebrew'].value_counts(normalize=True)[:5]
vc_road = vc_road.to_frame().reset_index().rename(columns={'index':'accident_type_hebrew','accident_type_hebrew':'Road {}'.format(road)})

df_all = inv_mar[(inv_mar['vehicle_vehicle_type'].isin(vehicle_types))&
(inv_mar['accident_year']>=year_lower_bound)&
(inv_mar['accident_year']<=year_upper_bound)]

vc_all = df_all['accident_type_hebrew'].value_counts(normalize=True)
vc_all = vc_all.to_frame().reset_index().rename(columns={'index':'accident_type_hebrew','accident_type_hebrew':'Other Roads'})

final = vc_road.join(vc_all.set_index('accident_type_hebrew'),on='accident_type_hebrew',how='inner')
final

[yuvalbar23]

** SQL Query **

WITH road_table AS
(SELECT accident_type,
cast(count(distinct(markers.provider_and_id))/sum(count(distinct(markers.provider_and_id))) OVER () AS decimal (10, 2)) AS road_20
FROM markers
JOIN vehicles USING(provider_and_id)
WHERE markers.road1=20
AND markers.accident_year BETWEEN 2014 AND 2019
AND vehicle_type IN (8, 9,10,19)
GROUP BY accident_type
ORDER BY road_20 DESC
LIMIT 5),

all_roads_table AS
(SELECT accident_type,
cast(count(distinct(markers.provider_and_id))/sum(count(distinct(markers.provider_and_id))) OVER () AS decimal (10, 2)) AS all_roads
FROM markers
JOIN vehicles USING(provider_and_id)
WHERE markers.accident_year BETWEEN 2014 AND 2019
AND vehicle_type IN(8, 9, 10, 19)
GROUP BY accident_type
ORDER BY all_roads DESC)

SELECT road_table.accident_type,
road_20,
all_roads
FROM road_table
JOIN all_roads_table USING(accident_type)
ORDER BY road_20 DESC

---

---

The output supposes to be with "accident_type_hebrew" instead of "accident_type", but I couldn't overcome the duplicates in the accident_type Table without using Hebrew conditions.

[atalyaalon]

@MichalOren @yuvalbar23 can we close this one? Since we have #1606 ?

[MichalOren]

yes we can close it