<jupyter_start><jupyter_text>Montgomery County Arrests Analysis Trevor Andrus Import Packages<jupyter_code>import pandas as pd
import matplotlib.pyplot as plt
import getpass
import mysql.connector
from sqlalchemy import create_engine
%matplotlib inline
pd.options.display.max_rows = 100
import warnings
warnings.filterwarnings('ignore')
plt.rcParams.update({'font.size': 18})<jupyter_output><empty_output><jupyter_text>Creating SQL connection<jupyter_code>user = input("Enter Username")
pwd = input("Enter Password")
host = input("Enter Host")
database = input("Enter Database")

cnx_string = 'mysql+pymysql://'+user + ':'+pwd + '@' + host + '/' + database

cnxn = create_engine(cnx_string)
query = "SELECT * from Arrests"
Arrests = pd.read_sql(query, cnxn)
Arrests<jupyter_output><empty_output><jupyter_text>Querying from the full table<jupyter_code>query2 = "SELECT * from ArrestsFull"
ArrestsFull = pd.read_sql(query2, cnxn)
ArrestsFull.head(10)<jupyter_output><empty_output><jupyter_text>Arrest Date After querying all the available data, we were left with 329 entries. The dates covered ranged from February 23 - March 25 of 2021. Considering we had mainly categorical variables, I first wanted to go through and analyze the value counts of each to identify the most common values and look for anything interesting on a single variable level.  10 Most common Arrest dates<jupyter_code>ArrestsFull["arrest_date"].value_counts()<jupyter_output><empty_output><jupyter_text>It seems that if you want to get arrested, March 17 was the best day for you to try and do so. Although this date doesn't seem significantly higher than all the rest. I next wanted to create a weekday column in the dataframe to see if more arrests occurred on weekdays or weekends.<jupyter_code>ArrestsFull["weekday"] = 0
for i in range(len(ArrestsFull["arrest_date"])):
    ArrestsFull["weekday"][i] = ArrestsFull["arrest_date"][i].weekday()
ArrestsFull["weekday"].value_counts()<jupyter_output><empty_output><jupyter_text>The pandas weekday function returns an integer corresponding to the day of the week - 1 being monday, and 6 being sunday. Interestingly from what we see above, Wednesday was the most common day to be arrested on, and the 3 weekend days were the days on which you were least likely to be arrested. This initially went against what I thought would happen - I assumed people on the weekends would be more rambunctious and be arrested more often, but this was not the case. It may be that police are more leanient on weekends, or that less police work weekends than weekdays. At this point all possible explanations are just speculation.   Age I next took a look at the age variable<jupyter_code>ArrestsFull["age"].describe()<jupyter_output><empty_output><jupyter_text>From the above we can see that the mean age of someone arrested in Montgomery county over the past month is 33. The oldest person arrested was 74, and the youngest was 17. Just out of curiosity, I printed the whole row for the 74 year old criminal below:<jupyter_code>ArrestsFull[ArrestsFull["age"] == 74]<jupyter_output><empty_output><jupyter_text>Being arrested for failure to comply, it sounds like he may have been causing trouble and then refusing to stop when approached by law enforcement.   Offense Continuing the theme of looking at specific offenses, I then took a look at the offense column as a whole.<jupyter_code>ArrestsFull["offense"].value_counts().head(10)<jupyter_output><empty_output><jupyter_text>From the above we can see that the most common offenses were assault (by a considerable margin might I add), and that thankfully the most common crimes are not too severe. Although I was curious as to how many severe crimes occured over the past month, and after looking through all the individual offenses found the following:9 counts of sex abuse against a minor3 counts of child pornography (1 with intent to distribute)2 counts of kidnapping1 count of first degree murder  Name After seeing the extent of the offenses, I became curious if anyone was arrested multiple times over the past month. I created a column in the dataframe combining the first an last names of people arrested, and looked at the individual values of each.<jupyter_code>ArrestsFull["FullName"] = ArrestsFull["first_name"] + ArrestsFull["last_name"]
ArrestsFull["FullName"].value_counts().head(10)<jupyter_output><empty_output><jupyter_text>It appears that <NAME> was the most frequent culprit, being arrested 4 times in the past month. The second in the list being "Juvenile Juvenile", I assume Montgomery country doesn't publicly disclose the identity of minors. Again for curiosity's sake, I've printed every time that Lafe has been arrested below.<jupyter_code>ArrestsFull[ArrestsFull["FullName"] == "LAFEBRACKENRIDGE"]<jupyter_output><empty_output><jupyter_text>After looking at <NAME>'s data, it looks like he wasn't arrested 4 times, but just charged with 4 offenses and arrested once on the 8th of march. I was then curious if any of the other people had actually been arrested more than once in the past month.<jupyter_code>ArrestsFull[ArrestsFull["FullName"] == "JORDANDOUVILLE"]<jupyter_output><empty_output><jupyter_text>After looking at each of the other's data, 4/5 of the people who have two offenses on record were actually arrested twice. Of note, <NAME> was arrested twice in two consecutive days, and <NAME> was one of the counts of kidnapping that we identified earlier.   City Continuing through the variables, I then looked at which cities were most common to be arrested in.<jupyter_code>ArrestsFull["city"].value_counts().head(10)<jupyter_output><empty_output><jupyter_text>Picture from : https://www.pinterest.com/pin/68117013090795472/ I also included a map above to get a better idea of where these arrests were occuring. It seems like most occur in and around the Washington DC area - a good portion of them occuring in DC proper and in silver spring (right outside). Fun fact - I didn't realize how close this was to my home town, but my home county in Virginia literally borders montgomery county to the south - I live probably 20 minutes from the city of bethesda shown above.   Street I also looked at the values for the street on which people were arrested, but didn't see anything significant there. Here is the top 10 most common places that people were arrested:<jupyter_code>ArrestsFull["street"].value_counts().head(10)<jupyter_output><empty_output><jupyter_text>State The last individual variable I had left to analyze was state, and I printed all of the values below.<jupyter_code>ArrestsFull["state"].value_counts()<jupyter_output><empty_output><jupyter_text>Many of the states featured are not surprising: Maryland makes up a bulk of the arrests, followed by DC and then Virginia which borders directly to the south. However, I was surprised to see states as far as Washington reporting arrests - literally thousands of miles away. I don't really understand how that happens (I highly doubt montgomery county police are hunting down fugitives across the country) but it may be that these people were living in Maryland, but then arrested by local police in these other states.   Two Variable Analyses After the individual variables, I decided to take a look at some relationships between variables. I looked to see if there was a difference in age based on the city in which the suspect was arrested.<jupyter_code>CityAge = ArrestsFull.groupby(["city"]).mean()
CityAge = CityAge.drop(columns = "weekday")
CityAge.describe()<jupyter_output><empty_output><jupyter_text>I'm fairly certain the minimum value was the result of only the one minor being arrested in the city, but I found the max of 67 interesting - because the value isn't 74, we know that more than one value must be contributing to the average. (or at least that our previously mentioned 74 year old pal <NAME> wasn't the only one arrested in his city).<jupyter_code>CityAge[CityAge["age"] == CityAge["age"].max()]<jupyter_output><empty_output><jupyter_text>Let it be known that the city of martinsburg holds the title of oldest average criminals over the past month in Montgomery county Maryland.  I decided to finish my analysis by looking at some vizualizations and comparisons between variables.   Vizualizations We really only have one quantitative variable (age) so I performed a histogram to see its distribution below.<jupyter_code>plt.hist(ArrestsFull["age"])
plt.title("Histogram of Age")
plt.ylabel("Frequency")
plt.xlabel("Age")<jupyter_output><empty_output><jupyter_text>We can see that younger people (below age 40) are more likely to be arrested, and that nobody below the age of 17 is being arrested. We can see the histogram is consistent with our previous numerical summary - it looks like the average age of suspect is around 33. I then wanted to look at arrests over time, and see if there was an increasing or decreasing trend. I pushed the value counts of the dates into a data frame below, and then created a plot to show how the number of arrests changed over the month.<jupyter_code>dates = ArrestsFull["arrest_date"].value_counts().rename_axis('Date').reset_index(name='counts')
dates = dates.sort_values(by ='Date' )
dates.head(5)
plt.figure(figsize=(20,10))
plt.plot(dates["Date"], dates["counts"])
plt.title("Arrests over time")
plt.ylabel("Number of Arrests")
plt.xlabel("Date")<jupyter_output><empty_output><jupyter_text>From what I can tell, there doesn't seem to be much of a trend - everything seems pretty random. If I had more time this may be good data to run time series analysis on (because everything looks fairly stationary already.) As an additional note, with this new data frame we can look at average arrests per day:<jupyter_code>print("On average " + str(dates["counts"].mean()) + " people per day were arrested in Montgomery county Marlyand from February 23 to March 25 2021.")<jupyter_output>On average 10.612903225806452 people per day were arrested in Montgomery county Marlyand from February 23 to March 25 2021.
{'fraction_non_alphanumeric': 0.06733167082294264, 'fraction_numerical': 0.012768079800498754, 'mean_word_length': 5.774324324324325, 'pattern_counts': {'":': 0, '<': 81, '<?xml version=': 0, '>': 81, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '19', 'dir': 'jupyter-structured-clean-dedup', 'id': '607221', 'license': 'MIT', 'n_tokens_mistral': 2862, 'n_tokens_neox': 2660, 'n_words': 1294, 'path': 'Python/Arrests Analysis/Arrests Analysis.ipynb', 'repo_name': 'Andrustn/Andrustn.github.io', 'size': '10064'}