Labels
Posted by

Analyzing School Shootings From 1970-2022

 



Kaggle Dataset: HERE 

GitHub Dataset: HERE

Handled with: Microsoft Excel, Microsoft SQL Server Management Studio 18, Tableau 

Programming Language: SQL


What Inspires this Project?

     To put it simply the topic of school shootings came into my life when I was in Middle School when I heard about the tragedy of Sandy Hook which occurred in 2012. Here a 20-year old male killed 26 people including himself. This premeditated occurrence began by the shooter first killing his mother at their shared home. After which he took four of her guns and drove to the school, where he killed 20 first-grade children aged six and seven along with six adults, including four teachers, the principal, and the school psychologist. Two other persons were injured. Lanza then killed himself as police arrived at the school. 

    Me being a kid I obviously found this information very shocking. Contemplating why someone would have the audacity to commit such a heinous decision. To this day nobody know definitively why this happened. People argue that it was bad parenting, that he had prior psychological issues, he was bullied, to be infamous, or just completely random. Either way the killer got the publicity of America and killed a plethora of innocent people. 

    Unbeknownst to me (at this age) is that this school shooting was just a sprinkle of other atrocities that have already occurred. The most notorious one that's talked about was the Columbine shooting which happened in 1999. I won't go into too much detail of this historic event since I personally feel that giving specific instances too much attention just perpetuates/motivates future shooters to attack. All I will say is that two men with pre-emptive motives went to a High School and killed 12 students and 1 teacher.

    To this day school shootings are a prevalent ongoing issue. A student should not have to remind themselves that they may one day have to succumb to an instance of a school shooting. Parents should not have to feel that one day their own child might die to a random school shooter. Even faculty should not feel like their lives are threatened on the daily. Let's be honest. Nobody wants to be at school let alone have to worry about being killed or shot at. 

    I understand that this topic is heavy and has multiple layers to handle. However, I personally feel that not doing anything is also a big issue. Therefore, I'd like to recommend some preventative actions that schools can take in helps to prevent a school shooter. Do I think any resolution will 100% guarantee the safety of faculty and students at a school? No, but perhaps at least a suggestion is better than getting into cyclical battle of banning guns which inevitability has no resolution nor does it lead to any progress to school safety. 

    What I want to do here is show data regarding well-documented school shooting incidents that have occurred since 1970 till midway through 2022. Even if the suggested information does not get taken seriously. What I really hope someone takes away from this is how school shootings are still happening, maybe clear up some confusion, and also display empirical facts to be aware of.


Microsoft Excel for Data Cleaning

    I first downloaded the four (.CSV) files provided by Shi Long Zhuang on Kaggle. The files included incident data (general data regarding the specific instances), any involved shooters data, victim data, and also the data regarding the weapons used during any particular incident. I then cleaned all the data inside of Microsoft Excel. 

  1. Replaced all empty values with the value of NULL.
  2. Deleted "Narrative", "Summary"  and "Source" data (all still very important and kept a backup copy of the raw data just in case people want to read more about a particular instance) 
  3. Fixed columns that displayed random integer values and entered accurate values to corresponded to the incident.
  4. Removed "Teen", "Child", "Adult" entries within "age" column to keep the data reliable and consistent.
  5. Added a "Age_Classification" column where a "Child" is younger less than or equal to 12,a teen is localized between 13-19, and a "Adult" is 20 years or older. 
  6. Fixed awkward entries within "School Level" and manually entered the schools real school level.

Microsoft SQL Server Management Studio 18 for Formatting

       Here, I wanted to format the data to be more easily readable and also really get a hold of what the data was telling. Here I really found out that the shooter data not only encapsulated the criminal(s) but also other people involved (faculty, police officer, et cetera). I also combined "weapon type" data to the involved shooters. I also chose MSSMS to shift columns around more conveniently as opposed to Microsoft Excel's user interface. Both can do the job except MSSMS can perform the task much faster.

Tableau for Analysis

School Shooting Location Data


    As we can see there is a significant increasing trend in school shootings as we proceed by the year. What makes that chart even scarier is that we recently had a high number of school shootings in the year 2021 with a high 251 documented school shootings. A deeper dive can be even further expanded on as that was the same year COVID had significantly impacted our country psychologically as well as financially. So it would be interesting to see if there is any correlation. The top three states with active school shootings comprised of California (216), Texas (177), and Florida (120). 
    Being a California native it makes me sad that of all 50 states that we lead in school shootings. I do not know anything about politics but I find it really concerning how Californians are limited to the amount of news they are seeing. It appears that school shootings don't become a topic of conversation until someone with a rile goes on a killing spree in another state. I love California but when these instances become a "trend" repeatedly I can't help but question the mindset of my state to be contempt with this occurrence being a norm. 
    Another interesting statistic I found was that a shooters favorite time to strike is during morning classes. Which leads me to believe that most school shootings are premeditated and do not normally occur by random. Also another favorite time for an attacker is during a sporting event which ranks 3rd on the list. While I haven't done any correlations between sport events and school shootings. It is a fact to be aware of for future reference. 
    I lastly made a list of cities with school shootings ranked from most to least. The picture itself only lists the top 16. However if you click the link (posted at the top of this), and hover over that list, you should be able to scroll down an see way more cities. You might be surprised of what you find there. 


School Shootings Involved Shooter(s) Data

Shootings By School Level

    From the chart we can see that High School's rank highest in school shootings. What is even more fascinating is how elementary schools rank second in shootings. 


Why School Shootings Happen


  • Accidental: No intent to fire the weapon (e.g., showing off gun and it went off; gun in backpack went off).
  • Anger Over Grade/Suspension/Discipline: Primarily targeted teacher or school administrator due to poor grades, suspension, expulsion, or discipline. Also includes a school employee who targeted a school administrator or coworker following poor performance evaluation, suspension, or loss of employment.
  • Bullying: Bullied by the victim(s) and did not indiscriminately target random students.
  • Domestic w/ Targeted Victim: Had a romantic or familial relationship with the victim or victim was in a romantic relationship with a former lover of the shooter
  • Drive-by: Shots fired by a person in a vehicle at people, or another vehicle, on school property.
  • Escalation of Dispute: Physical or verbal altercation between the shooter and victim prior to the shooting or retaliation for a prior altercation or attack (e.g., during a fight between multiple students, one of the students involved pulled out a handgun and fired; following an argument earlier in the day, a student shot the other student that he/she argued with in the school parking lot).
  • Hostage/Standoff: A standoff at the school between an armed shooter and law enforcement with or without hostages taken. Hostages may have been released without injury and/or no shots were fired during the standoff.
  • Illegal Activity: Shots were fired during a robbery, sale or exchange of illegal drugs, trespassing, theft of property, or exchange of stolen property.
  • Indiscriminate: Targeted random victims with the intent to kill or injure as many as possible (e.g., fired into a crowd; shot students in the hallway and random classrooms).
  • Intentional Property Damage: Shots were fired to cause damage to the school building or vehicles on school property without intent to cause injury.
  • Psychosis: Described as disconnected from reality and exhibiting symptoms of a severe psychotic episode, or having paranoid schizophrenia stated in open-source reports (e.g., shooter believed the school was sending mind control signals or part of a government conspiracy).
  • Murder/Suicide: Shot targeted victim and then immediately killed self (including other bystanders who were struck by gunfire but were not intended targets).
  • Officer Involved Shooting: Police officer, SRO, or armed security guard was the only person to fire a weapon.
  • Racial: Shooter targeted victim based on race.
  • Self Defense: Shooter fired in self-defense or defense of someone else.
  • Suicide/Attempted: Suicide or attempted suicide by a shooter who was the only victim (not an indiscriminate shooting or revenge/bullying where shooter kills self during the shooting).

    While the reason doesn't justify the shooters actions. It is however important to make note that most shootings do not occur due to bullying which is a common theme most people seem to believe. Instead people tend to shoot during a physical or verbal altercation between the shooter and victim.
    

Involved Shooters by Affiliation


Popular Guns Used During a School Shooting


    What I find the most interesting about this pie chart is the amount of hand guns used in school shootings. I personally feel the media highlights incidents involving mass shootings in which includes any firearm except a handgun (typically a semi automatic rifle). All I'm trying to understand is why can't a school shooting involving a single handgun targeting one person get the same kind of treatment as a mass shooting. Why does it take atrocities involving multiple people being murdered for people to understand the seriousness of the problem regarding school safety.  
 

Victim Data in School Shootings
Infamous School's

        The list above shows schools with the highest number of victims sorted by their injury (Fatal, wounded, minor injuries). The pictured list is limited. If you want to see more please feel free to click the link above to see more schools. 



Where NOT To Be During a School Shooting

Conclusion

    School shootings are increasing by the year. If we keep getting distracted by controversial topics, finding the reason as to why school shootings happen, or argue which solution is considered is best then progress will never be made. Truth is at this current moment in time anything to reduce the count of school shooters is considered progress. Until actions are taken either by schools/government/or social movements then nothing will be done and schools will remain in the same unsafe place they are already in. 


Suggested Solutions (Pros & Cons)
  • Adding More Security on Campus
    • Pros: A shooter might be less likely to strike if they know that there are active duty members patrolling the campus at all times.
    • Cons: Potential unfair policing of students based on biases. 
      • Also can affect School Budget expenses.

  • Implementing Metal Detectors
    • Pros: Checks quickly and efficiently for potential dangerous weapons
    • Cons: An expensive option
      • Possibility of privacy invasion

  • A Fully Closed Campus w/ Gates
    • Pros: Makes it much more difficult for strangers to be on campus. 
    • Cons: Cuts into school budget expenses

  • Assessing a Student's Mental Health 
    • Pros: Offering confidential counseling sessions to those who might need it to boost ones mental health.
      • Students can potentially open up more and communicate what's going on in their lives. 
      • A free strategy
    • Cons: Possible controversy over questions being asked  
      • Debate on which questions are to be considered standard
      • Students can lie on the assessment and potentially risk tarnishing danger level data

  • Enforcing Students to Carry Transparent Backpacks
    • Pros: Reduce chances of students entering school premises with firearms or other weapons
    • Cons: Invasion of Privacy 
      • Searching's can case a delay which can be time consuming 
      • Students with a "bad" reputation might be subject to be searched based on preconceived biases

  • Allowing Faculty to Carry
    • Pros: An attempt to scare students to not bring weapons to school
    • Cons: Adults threatening the lives of students
      • A potential increase in school shootings 
      • Putting faculty/staff in harms way where the shooter knows who is armed and therefore a primary target in an escape plan
      • Which member is allowed to carry and who is not?

THANK YOU!
    A big thank you to Shi Long Zhuang for sharing her well-documented amount of data for the public to access and analyze. I hope some of these charts and graphs can give you an idea of how serious the topic is and I thank anyone who took time out of their day to read all of this. Regardless of my opinions or suggestions, spreading awareness is important to me. These visuals and information can definitely be expanded on further to understanding the root cause of these occurrences happening. However, the goal of this project was to illustrate the horrifying nature of these events. It happens more often than one would assume. I also wanted to provide some suggestions as to what we can do to prevent this from happening as much as we possibly can. 
Labels: