Saturday Night Data: Analyzing Hate Crime Data

Alright, this is Saturday night data. Um normally we call it the Saturday Night Data Party, but since we're analyzing hate crime data, doesn't feel right. So just some Saturday night data stuff. Uh this is something that is a part of a project that uh we've done at Trust Insights now. Uh we did it last year.

It's something we typically do for Pride Month, but given everything going on in the world uh and the data that we have available, it's probably not the most uh inappropriate thing to be looking at more than just hate crime reporting against a specific group, in this case the LGBTQIA community. There's a lot of bad stuff happening all around. So uh to give you a bit of background on the project, what we did last year was take a whole bunch of data that's publicly available uh and try to understand how bad is the problem of underreporting and what data is available. And there is data um available about the reporting of hate crimes, particularly from the Federal Bureau of Investigation. They have this report they come out every year called the Uniform Crime Report.

And what it tries to do is take data from all over uh the country and roll it up into a master database that is then available to the public for analysis and and disclosure. There's a lot of problems with this report. Um it is the best they can do. Um, so I'm not not hating on their efforts, but there's a lot of problems with this report because especially when it comes to hate crimes, every state has different definitions of what constitutes a hate crime. Every state has different definitions about how hate crimes get reported or don't get reported.

And so what you end up with is a lot of number salad for lack of a better term. So what we want to try and do, and what we did last year was try and bring a bunch of this data together and run some analysis, try to see what's in there. So let's get started with the process of getting the data from the FBI. So the FBI uh uniform uh crime report, UCR.fbi.gov. I strongly recommend if you are interested in these sorts of statistics.

The US government does publish these things, uh, and there is a lot of uh useful data in here. Um a lot of it's available in spreadsheets. In fact, I kind of have a love hate relationship with the FBI, specifically around their data formats. Their data formats are not fantastic, but it is it is what's available. So let's go to UCR.fbi.gov slash hate crime.

And this is where we're gonna start. And you can see there's data going back to 1995. Let's choose uh calendar 2018. And what we have here are uh by jurisdictions, incidences, offenses, the victims, offenders, location types, and then uh federal crime data, crime in the United States, things like this. Uh the first data set we're gonna want is the by jurisdiction.

Uh and the by jurisdiction is table uh 13 provides 12 and 13 provide the data we're looking for. So let's go to table 12, teta 13, and table 14. So let's open these up. Again, one of the things you're gonna notice about FBI data, it's super messy. Super super messy.

So this is what you get. Um you get states, then cities, and then rolled up data. And this is so one of the things the first things you have to do is essentially get this data into a single table, which is what I did here, manually copying and pasting. Um there is I I'm sure there's a way to programmatically do this. I have not been frankly willing to spend the time figuring out how to programmatically do it when I can you know once a year uh copy and paste.

Uh let's also look at table 12. Did we get table 12? Yes, table 12. Okay, so this is um a list of reports of which agencies are participating in the reporting of hate crimes. This is where you start to find anomalies.

So let's take a look at this. And gonna try to be sensitive in my commentary here, but there are some places that you will notice, for example, like Alabama and Wyoming that have reported no hate crimes whatsoever in 2018. So the question is were there no hate crimes there, or were there no hate crimes reported? And if there were no none reported there, why not? Given the state of everything happening right now around the country, I find it difficult to believe that there is any state in the union, period, where there have not been hate crimes.

Whether they were reported and whether those agencies in turn reported to the federal government is probably the bigger question. We can confirm this by looking at the this hate crime data reporting by state and federal agencies. So we see, for example, Alabama had 98 agencies that participated in reporting, but only four that actually submitted reports. I'm sorry, only zero that submitted reports. So of course they reported zero.

So it's not that there weren't hate crimes in this jurisdiction. It's just that no agencies submitted reports, no police departments uh and other law enforcement groups submitted anything. And then when you start to go down the numbers here, one of the things you'll notice, again, in the spreadsheet, um, you'll notice like some places have a ton of reported hate crimes. California, 596 against race and ancestry, uh, 20 against gender identity, a thousand total uh hate crimes. People you're sitting there going, wow, California must be like crazy unsafe.

Well, no. A couple of reasons for us. One, it's a really big state with a huge population, and two, when we look in the data itself, uh California has 736 agencies, of which 220 provide data to the federal government. That means that there are vastly more, uh infinitely more technically, if you want to get math all mathy, uh more people report, more agencies reporting in California than there are in Alabama. Uh if we go down here to Wyoming, again, Wyoming, same situation.

57 police departments within Wyoming, zero reported any data. And as we s look through this list, you start to get a sense of just how bad the problem is. The problem is that police departments are not submitting incident reports. Whether there are incidents, whether they don't have any incidents to report, whether the laws in those jurisdictions don't exist, we don't know. All we can see is that on a percentage basis, there are a number of uh places where, for example, in Delaware, 10 out of the 63 police departments reported something within calendar year 2018.

So let's go ahead and download this file because we're going to need to take this file and convert it into uh into data that's readable into our software. I'm going to do that at another time. I'm not gonna spend some a whole bunch of time on that here, but I I do want to spend some time just doing a quick analysis here. We'll just call it percentage reporting. We're gonna do this out of the total number of agencies, 16,000 agencies around the country.

And let's slap ourselves just a nice little data bar here. And already we can see like District of Columbia, Hawaii, 100% of those reporting. And then let's sort like so. Get rid of those first two rows. Connecticut, 35% reporting.

Right, and then look, we just kind of fall down into you know Pennsylvania, 1,400 uh police agencies, 15 of them reporting. Pennsylvania is a big state. There's a lot of colleges, there's a lot of towns and cities. But I find it difficult to believe that only 15 out of 1400, essentially 1% of police agencies had any kind of hate crime that they would not need to report it in 2018. So this to me is a bit of a problem.

So let's go ahead and take for now these data. We're going to save this in a different spreadsheet here and just get this cleaned up. Woof, that came out looking like salad. What happened here? Oh, it's Excel.

And it's dealing with old data formats. Let's see if we can just snag it from here cleanly. Alright, we'll have to fix up those headers later. We also want to zap those gremlins because that's just not pretty to look at. Alright, we'll call this porting 2018.csv.

We'll come back to that later. So that's our second table. Our third table that we're going to want to take a look at is this uh zero data submitted by quarter. Who is not submitting data at all? Alright, so we have pretty much the entirety of Alabama.

This goes on for quite some time. But you can get a sense of who is expected to be relaying data. It is universities and colleges, state police agencies, cities, tribal agencies. And this has implications not just for the LGBT, LGBTQIA community. It has implications for the religious communities out there, has implications for uh races, uh racial groups, uh disabil people with disabilities, people with um uh gender identity that are that are non-standard.

If we're not reporting on this data for any of these groups, there's a whole bunch of things that are going on that we don't really understand, we don't know. And because we don't know, we can't make decisions about it. And so this is really problematic. All right, so we've got our population data. We're gonna we'll want to update our population data as well to go to bring in new data.

Uh we will want to uh get as best as possible, if it's available, our LGBTQ populations, uh population density, number of people, right? And so this data will have to, again, you'll you'll see a lot of copying and pasting and scraping of data this way. Because for a lot of these nonprofit organizations, they have to publish their data, and they're gonna make it as easy as possible put it to put it on the web so you get lovely things that look like this. First things first, this is just a disaster waiting to happen. Uh, we're gonna get rid of all the commas.

Because that's just not a good idea to have commas in a CSV file. And then at some point we're gonna a little bit later on, we're gonna need to clean up those names. Let's see. Let's this is an important one. Who has policies for hate crimes?

This is from the Department of Justice. Which states have hate crime laws that require data collection on hate crimes? So this is kind of interesting. The blue represents has hate crime laws and requires data collection on it. The gray has has hate crime laws in place, no data collection requirement, and the red are places that do not have hate crime laws and therefore do not require data collection.

We see Wyoming there, right? Alabama doesn't have a data require collection requirement. And so now we're starting to get really into the heart of the matter, which is there are a lot of states on this map where our data is gonna be suspect, right? If you are looking at data from Vermont or North Carolina or Montana in the big spreads bring this back here. Table 18.

Vermont. 27 agencies voluntarily submitted data because they don't have to report it. If you look at Montana, 108 agencies, five reported. And the reason for that is because of those gray boxes. Wyoming, we hit we knew how to zero.

Arkansas, let's see, 286 police departments, nine voluntarily reported. And that's you know gonna be local jurisdiction stuff because the state doesn't have laws on the books for hate crimes. So you really get a sense of just how how bad this problem is. And then if we look at which states have, in this case, for this this particular uh, this is all hate crimes against any group. So if you are in any of these protected groups, you are not a member of the majority racial group.

You're not a member of the majority religious group, you're not a member of the majority sexual orientation, and you live in either gray or red states, you may or may not have a whole lot of protection and ability to uh to have incidences against you reported. If we look in the the equality maps on the Movement Advancement Project, any state that's orange is a state that uh does not have laws for hate crimes that include sexual orientation or gender identity. And there's a lot of orange on this map. One of the big takeaways from the report that we put together is if you live in one of these locales, you should definitely petition your uh your elected representatives to put some laws in the books, right? It doesn't hurt anyone to expand rights to cover more people.

It doesn't hurt anyone to have an existing hate crime law expanded to cover sexual orientation and gender identity. Right? Crime's a crime. And if this law can expand to that definition to cover protected groups, it's better for everybody. Because it catches bad guys and makes bad guys serve longer penalties for doing bad things.

People who don't commit hate crimes of any kind don't have to worry about these the expansion of a law. So we will pull in that data, and if you could get the tabula data here again. News coverage and social media conversations. We're not going to dig into this right now because we're already about half a little bit more than 15 minutes into this, but we can extract data from Google's GDELT project, which is sort of a back-end Google News style database. We can extract data from the TalkWalker platform.

I'll show you an example of that. When you look at you know reporting a hate crime, how what is the language that people use around these particular terms? So let's look at some noun phrases here. And what we want to use here is try and identify are there specific terms, words, phrases, frequencies of uh conversations about these things that we can use to help benchmark these problems. So the way to think about this is if you see in a given location, in fact, let's pull up our location map here.

Illinois has 40 400 results. Have Minnesota, 167 results. So one of these things that we can do with this data is to use this to understand are there conversations that are happening that we can benchmark at a locale level with social media software as a starting point. Now, social media conversation data is really tricky to work with because A, particularly on any politically charged topic, there's a tremendous amount of bots and automation stuff that's gonna screw up the results. And B there are some things people don't want to talk about.

Like if you live in Alabama, for example, um, or actually, if you live in any of those states that we identified that have those, you know, those uh red, you know, no hate crime laws in the books, chances are the people who live there are not gonna be super receptive to having conversations about reporting hate crimes. So the social media day we have to take with a large grain of salt because it's really good for qualitative stuff. So trying to understand the words and phrases. What we would want to do is to start digging into search data, right? Report hate crime, how to report a hate crime.

Let's go into uh Google Trends here, and we would extract a lot of this data from Google Trends. Right. We're starting to see we're even here, there's not a ton of volume. Let's look at five years. Looking back five years, there's not a ton of search data happening here either.

So one of our typical go-tos for trying to understand a problem is are people searching for it? In this case, there's not a ton of search data to work with. So we this tells us that for our research project, we have to start looking for alternate sources somehow. And that may mean going into uh specific focus groups and things like that, where we can talk to people in one of the targeted populations and say how how, if at all, have you reported this? Um did you get some kind of result from it to better understand what are the ways that people are dealing with these problems, and that can then inform our statistical analysis.

When you look at this and you go, wow, there's just not a lot of data by sub-region here for something that you would seem to be think is important. That means that you probably have a massive underreporting problem. And that in turn means you may need to even do things like commission anonymous surveys and stuff just to get some data from people about like, hey, have you know, have you ever been the victim of a hate crime? Right. And you have to calibrate against that as well.

Um, as well as collect uh demographic data to understand uh was it a hate crime for for religious reasons or sexual orientation reasons, things like that. This is a very difficult problem to unpack. It is a worthwhile problem to unpack. It is an important problem to unpack, but from a data perspective, it is very, very difficult. That said, from a common sense perspective, if you look at the data from 2018, there were 6,794 hate crimes reported in the United States out of a population of 330 million people.

Intuitively, that seems a little low. And so you can do things like, for example, if you were to take the number of hate crimes reported in a jurisdiction and multiply that by the inverse of the number of police departments reporting, essentially trying to compensate for all those that aren't reporting, could you get closer to an actual estimate? Probably. And say, hey, I'm interested in helping out. Would you like some help trying to process this data to bring awareness to the problem?

To motivate people to action. For example, getting people to petition legislators. You need to make a law that says we are required to report hate crime data to the federal government. That alone would be a massive change. It'd be a great change.

It'll be enlightening eye-opening would be let's take the 2017 data. Let's see how much has changed from 2017. Let's take equals new minus old divided by old. Thank you, Alabama. Percentage change.

Isn't that interesting? There's a lot less being reported by jurisdiction in 2018 than there was in 2017. Does that reflect reality? I don't know. It but it reflects the data that we have.

So again, that's another case where there's something here to investigate. So first takeaway, use this data, pick it up, analyze it, and use it to help motivate some change. Two exercises like this really stretch your capabilities as a data scientist, as a analytics person practitioner, as someone who is a citizen analyst. And I would really encourage you to maybe not this data, but take up something that is belongs to a cause that you believe in and run with it. Run with it, see what you can do with it, see if you can shed some light on something, and help make change for the better.

Because right now, the world in general, the country I live in, the United States of America in specific, is desperately in need of people who are trying to help make the world a better place. And if we have the tools and we have the data and we have the motivation, we should be using it. So I would hope that you take away some of the techniques that we've done from this about sourcing data. Start to pick at the problems in the data to understand that what the data says may not be what real reflects reality in reality, and to help you to take up the mantle of a citizen analyst. Thank you for watching.

I hope that you've learned something. I hope that you take action with this. Take care. Want help solving your company's data analytics and digital marketing problems? Visit TrustInsights.ai today and let us know how we can help you.