Messing with data¶
In [1]:
import pandas as pd
import numpy as np
import seaborn as sns
In [2]:
from matplotlib import pyplot as plt
from scipy import stats
In [3]:
data = pd.read_csv('clean_murder_data.csv')
stateAbbr = pd.read_csv("StatesAbbr.csv")
data.head()
Out[3]:
In [4]:
print(data.columns)
print(stateAbbr.columns)
relationship_df = data.groupby(['Relationship']).CaseID.count().reset_index()
relationship_df
Out[4]:
Creating State database¶
In [5]:
# Total Murders by state since 1976
totalMurders = data.groupby('State')['CaseID'].count()
print(totalMurders.head())
totalMurders_df = totalMurders.reset_index().rename(columns={'CaseID':'TotalMurders'})
print(totalMurders_df.head())
In [6]:
# Number of UNSOLVED homicides by state since 1976
unsolved = data.loc[data['Solved'] == 'No']
unsolved = unsolved.groupby('State')['CaseID'].count()
print(unsolved.head())
unsolved_df = unsolved.reset_index().rename(columns={'CaseID':'TotalUnsolved'})
print(unsolved_df.head())
In [7]:
# Percent of UNSOLVED homicides by state since 1976
percentUnsolved = (unsolved/totalMurders)*100
percentUnsolved = percentUnsolved.round(1)
print(percentUnsolved.round(1).head())
percentUnsolved_df = pd.DataFrame(percentUnsolved).reset_index().rename(columns={'CaseID':'PercentUnsolved'})
print(percentUnsolved_df.head())
In [8]:
# Number of SOLVED homicides by state since 1976
solved = data.loc[data['Solved'] == 'Yes']
solved = solved.groupby('State')['CaseID'].count()
print(solved.head())
solved_df = solved.reset_index().rename(columns={'CaseID':'TotalSolved'})
print(solved_df.head())
In [9]:
# Percent of SOLVED homicides by state since 1976
percentSolved = (solved/totalMurders)*100
percentSolved = percentSolved.round(1)
print(percentSolved.head())
percentSolved_df = pd.DataFrame(percentSolved).reset_index().rename(columns={'CaseID':'PercentSolved'})
print(percentSolved_df.head())
In [10]:
# stateWeapon= data.groupby(['State','Weapon'])["CaseID"].count().reset_index()
# print(stateWeapon.head())
# stateWeapon = stateWeapon.sort_values(["CaseID"], ascending=False)
# stateWeapon_df = stateWeapon.drop_duplicates(["State"])
# print(stateWeapon_df.head())
In [11]:
# Merge all State data to create final csv
state_db = stateAbbr.merge(totalMurders_df,on="State")
state_db = state_db.merge(unsolved_df, on="State")
state_db = state_db.merge(percentUnsolved_df, on="State")
state_db = state_db.merge(solved_df, on="State")
state_db = state_db.merge(percentSolved_df, on="State")
print(state_db.head())
state_db.to_csv("state_db.csv", index=False)
Testing for Murder data by month¶
In [12]:
def month_string_to_number(string):
m = {
'jan': 1,
'feb': 2,
'mar': 3,
'apr':4,
'may':5,
'jun':6,
'jul':7,
'aug':8,
'sep':9,
'oct':10,
'nov':11,
'dec':12
}
s = string.strip()[:3].lower()
try:
out = m[s]
return out
except:
raise ValueError('Not a month')
In [13]:
stateMurdersByMonth = data.groupby(['State','Month'])['CaseID'].count().reset_index()
#print(stateMurdersByMonth.head())
stateMurdersByMonth['MonthNums']=stateMurdersByMonth['Month'].map(month_string_to_number)
stateMurdersByMonth = stateMurdersByMonth.rename(columns={'CaseID':'TotalMurders'})
stateMurdersByMonth = stateMurdersByMonth.sort_values(['State',"MonthNums"], ascending=True)
plotala=stateMurdersByMonth.loc[stateMurdersByMonth['State']=='North Carolina']
print(plotala)
plt.errorbar(plotala.MonthNums, plotala['TotalMurders'],
linestyle='--', marker='.', markersize=8)
Out[13]:
In [14]:
pivot = stateMurdersByMonth.pivot(index='MonthNums', columns='State',values='TotalMurders')
print(pivot.head())
pivot = pivot.reset_index()
for state in stateAbbr['State']:
plt.errorbar(pivot.MonthNums, pivot[state], fmt='2',
linestyle='--', marker='.', markersize=8,capsize=4)
plt.show()
In [15]:
relationship_df = data.groupby(['Relationship']).CaseID.count().reset_index()
relationship_df
Out[15]:
In [16]:
weapon_df = data.groupby(['Weapon']).CaseID.count().reset_index()
weapon_df
Out[16]:
In [17]:
circumstance_df = data.groupby(['Circumstance']).CaseID.count().reset_index()
circumstance_df
Out[17]:
In [18]:
year_df = data.groupby(['Year']).CaseID.count().reset_index()
year_df
Out[18]:
In [19]:
NC_df = data.loc[data["StateAbbr"]=="NC"]
agency_df = NC_df.groupby(['Agency']).CaseID.count().reset_index()
agency_df=agency_df.rename(columns={'CaseID':'Murders'})
agency_sort=agency_df.sort_values(['Murders'], ascending=False)
agency=agency_sort.head(10)
agency
Out[19]:
In [32]:
agency.reset_index(drop=True,inplace=True)
labels=agency['Agency']
colors=['red','tan','darkred','grey']
agency.plot.pie('Murders',labels=labels,legend=False,autopct="%0.0f%%",colors=colors,fontsize=12)
plt.axis('equal')
plt.title("North Carolinas Most Murderous Cities",fontsize=15)
plt.ylabel('')
fig = plt.gcf()
fig.set_size_inches(10,10)
plt.tight_layout()
plt.savefig('ncmurder.png', bbox_inches='tight', transparent=True)
plt.show()
In [21]:
MSA_df = NC_df.groupby(['MSA']).CaseID.count().reset_index()
MSA_df.sort_values(['CaseID'], ascending=False)
Out[21]:
In [22]:
rel_state = data.groupby(['State','Relationship'])["CaseID"].count().reset_index()
relationship2=rel_state.sort_values(["CaseID","Relationship"], ascending=False)
relbystate=relationship2.drop_duplicates(["State"])
relbystate.head(5)
Out[22]:
In [23]:
off_state = data.groupby(['State','OffRace'])["CaseID"].count().reset_index()
off2=off_state.sort_values(["CaseID","OffRace"], ascending=False)
offracebystate=off2.drop_duplicates(["State"])
offracebystate.head(51)
Out[23]:
In [24]:
vic_state = data.groupby(['State','VicRace'])["CaseID"].count().reset_index()
vic2=vic_state.sort_values(["CaseID","VicRace"], ascending=False)
vicracebystate=vic2.drop_duplicates(["State"])
vicracebystate.head(51)
Out[24]:
In [25]:
unsolved=data.loc[data["Solved"]=='No']
age_state = data.groupby(['State','VicAge'])["CaseID"].count().reset_index()
age2=age_state.sort_values(["CaseID","VicAge"], ascending=False)
vicagebystate=age2.drop_duplicates(["State"])
vicagebystate=vicagebystate.rename(columns={'CaseID':'Murders','VicAge': 'VictimAge'})
vicagebystate.head(51)
Out[25]:
In [26]:
weapon_counts = data["Weapon"].value_counts().reset_index()
weaponcounts2 = weapon_counts.rename(columns={'Weapon':'Murders'})
weapon=weaponcounts2.rename(columns={"index":"Weapon"})
weapon
Out[26]:
In [27]:
weapons=weapon.set_index("Weapon")
weapons2=weapons.head(20)
weapons2.plot(kind='bar',y='Murders',figsize=(15,15),fontsize=15,legend=False,color="red",)
plt.xlabel("Weapon",fontsize=30)
plt.ylabel('Murders',fontsize=30)
plt.ylim(ymax=125000)
plt.title('Murder Weapons',fontsize=40)
plt.tight_layout()
plt.savefig('mweapons.png',transparent=True)
plt.show()
