-
Notifications
You must be signed in to change notification settings - Fork 0
/
annualReportMap.py
92 lines (61 loc) · 2.85 KB
/
annualReportMap.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
import pandas as pd
def overarchingType(typeList):
if "H" in typeList:
return "Homicide"
else:
return "Non-Fatal Shooting"
def countHomicideVictims(typeList):
count = 0
for event in typeList:
if event == "H":
count = count + 1
return count
def countNonFatalVictims(typeList):
count = 0
for event in typeList:
if event == "B":
count = count + 1
return count
def overarchingTitle(EventList, Referred):
for event in EventList:
for ref in Referred:
if event == "Homicide" and ref == "Yes":
return 'Homicide - Referred'
if event == 'Homicide' and ref == "No":
return 'Homicide - Not Referred'
if event == 'Non-Fatal Shooting' and ref == "Yes":
return 'Non-Fatal - Referred'
if event == 'Non-Fatal Shooting' and ref == 'No':
return 'Non-Fata - Not Referred'
shootingDataPath = "ShootingData\\2021 Shooting Data.xlsx"
shootingData = pd.read_excel(shootingDataPath)
shootingData = shootingData[~shootingData['IncidentAddress'].str.contains('Unknown, Kansas City, MO')]
shootingData = shootingData[shootingData['JaCo']=='Yes']
shootingData['DateTime'] = shootingData.DateTime.astype(str)
underReview = shootingData[['geometry', 'Ref']]
crns = shootingData[['CRN', 'geometry']]
byCrn = shootingData.groupby('geometry')['CRN'].apply(set).apply(str).to_frame().reset_index()
byAddress = shootingData.groupby('geometry')['IncidentAddress'].apply(set).to_frame().reset_index()
byDate = shootingData.groupby('geometry')['DateTime'].apply(set).to_frame().reset_index()
byPoint = shootingData.groupby('geometry')['Type'].apply(list).to_frame().reset_index()
byPoint = byPoint.merge(underReview, on = 'geometry', how = 'left')
byPoint = byPoint.merge(byCrn, on = 'geometry', how = 'left')
byPoint = byPoint.merge(byAddress, on = 'geometry', how = 'left')
byPoint = byPoint.merge(byDate, on = 'geometry', how = 'left')
byPoint['Number of Casualties'] = byPoint.Type.apply(lambda x: len(x))
# new data frame with split value columns
byPoint["geometry"] = byPoint["geometry"].str.replace("(", "")
byPoint["geometry"] = byPoint["geometry"].str.replace(")", "")
coordinates = byPoint["geometry"].str.split(" ", n = 2, expand = True)
byPoint['Longitude'] = coordinates[1]
byPoint['Latitude'] = coordinates[2]
byPoint['Event'] = byPoint.Type.apply(lambda x: overarchingType(x))
byPoint['Homicide Casualties'] = byPoint.Type.apply(lambda x: countHomicideVictims(x))
byPoint['Non-Fatal Casualties'] = byPoint.Type.apply(lambda x: countNonFatalVictims(x))
byPoint['Ref'] = byPoint['Ref'].str.replace("Yes", " - Referred")
byPoint['Ref'] = byPoint['Ref'].str.replace("No", " - Not Referred")
byPoint['Label'] = byPoint['Event'] + byPoint['Ref']
byPoint['Total Casualties'] = byPoint['Homicide Casualties'] + byPoint['Non-Fatal Casualties']
byPoint = byPoint.drop_duplicates(subset = 'CRN')
byPoint = byPoint.astype(str)
byPoint.to_csv("MapData.csv", index = False)