Saturday Night Data: Hate Crime Reporting

This is Saturday night data. We are continuing to dig in on the data from the FBI and a variety of other sources. This week, one of the things we didn't have last year when we put together this paper was uh any year-over-year data because it was just a snapshot in time. So this time we have the ability to do some year-of-year processing of the data to see what's changed because an important part of this uh project is understanding if we're making progress on the reporting of hate crimes. So we're gonna go ahead and bring in our data here.

And one of the things that we want to do is we're gonna want to try and unify these two uh data frames. So let's do change and we're gonna do a uh let's see. Let's do a left join. Yep. All right.

Do a left join on 2017 by state. Ooh, we need to clean the names on thesetherwise this got really bad. There we go. Alright. By state and lowercase.

And I want to name they're gonna have identical column names. So we're going to need to output disabiguate them. Let's take a look at an example here. You know, just going it. We have X's and Y's, which is fine.

We know what those two are. Let's figure out what the overall looks like. Change equals. And our change formula is new minus new minus old divide by old. So we have total y minus total x divided by total x.

Have a few oddities and errors here. We have forty-four. Now we have some zeros from previous year. Okay, that's fine. Let's go ahead and burn this down.

And now if we fire up our visualization software, I could do this in Ggplot, but it takes so long. It's so much easier just to pop it open in a simple visualization tool. And our change. We don't really want this that this way. And this is going to be our percentage change year over year.

So let's make sure we format that appropriately. Okay. And so we have in some cases substantially fewer hate crimes being reported year over year, and some cases substantially more. Now one of the challenges with this is these numbers don't tell the whole story. Change reported crimes twenty seventeen to twenty eighteen.

We'll save this for now. Let's see what this data frame looks like. We should call it change. There we go. So total number of incidences reported.

Okay, so we have two things. We have total number of incidences reported, and we have agencies submitting incident reports. What we want to look at is the agencies submitting reports because that's going to tell us if we look at this. Number of participating agencies, number agency submitting reports. Total number of incidents reported.

Number of participating agencies. Okay, so we want what do we want to do here? We I think we want to focus on agencies submitting reports. So X. Actually, we want it to be dot Yes is going to be new minus old divided by old there we go let's clean all this up a little bit Okay so we should have hate reporting change that's what I thought all right let's take this and write this down to a file and now we bring it into our visualization software add a new text file add a new feature here agency change remember this is a percentage okay slap our labels on what's interesting here is that you have in some cases like a number of states where there's substantially fewer police departments uh reporting hate crimes.

Now, if we put these two things together, let's do a horizontal grid. Let's make our grid automatic. And let's go back and sort by state name. So that is alphabetical. And so this is astonishing.

Some of these things, like here we see California, 886% increase in reported hate crimes, and actually 5% fewer agencies reporting. Mississippi, 400% more crimes, 300% more reporting. That makes logical sense. Well, it's easy to make crime decrease if you don't report it. It doesn't mean it's things are any better.

It just means that there's less reporting of it. Um, for our work in trying to make an actually safer society for everyone, you kinda gotta go with um the truth. Even if the truth is something you don't want to hear, right? If you have an 800% increase in reported hate crimes, there's a problem. There's there's something that you need to investigate and fix.

But when we incentivize our leaders to uh make things look good on paper, even though it doesn't reflect the reality of the situation, we end up in a pretty bad place. So the takeaway from this is twofold. One, in your own work, when you're doing you know, marketing or sales or whatever kind of data analysis, tell the truth, please. Um don't do what we see happening here where we're seeing less and less reporting. It's a bad thing.

In fact, I'm curious here. If I select all uh, we can't do that in this let's let's see what this says. So our agency change, we have those. We need to drop those NA's. Okay.

Well, yeah, tell the truth. And second, when you're doing any kind of year over year comparison of a metric or KPI or whatever, make sure that you have insight into the reporting accuracy as well. So if we had just gone with the hate crime data alone, right, it would look like things were actually, you know, pretty good, right? We're seeing decreases in the number of hate crimes. Like, yay, this is a good thing.

And we want that to be a good thing, right? Uh we see a lot of places here with a decrease in hate crimes. But when we look on the reporting side, then we also see that uh we've lost a lot of ground on reporting. We've lost accuracy. And so in your own work, make sure that you're doing both the reporting of the metric, but also the reporting on the reporting on how you're getting the data, the condition it's in, things like that.

Because without the two, it's very easy to mislead others. It's even easier to mislead yourself when you are going to compare year over year data, for example, and you're like, huh, I don't remember what I did last year, and you don't have this reporting accuracy, it gets really challenging to uh make sure that you've you've captured the instrument sensitivity as well. And that applies to all forms of reporting. So those would be the the key takeaways for this. As you can see from the date itself, we have a lot of work to do.

We have substantial increases in hate crimes, and we want more reporting of them. And we have decreases in the accuracy, and that is for the citizens of those states. Like here's my state. Um 27% fewer agencies reporting. I need to be contacting my state legislators and saying we want mandatory reporting of all uh hate crimes by uh police departments, regardless of what the politician or the mayor or whatever uh is doing to make themselves look better free election.

We want to know if it's actually safer to go outside with our kids if we're uh not in a majority ethnicity or religion or sexual orientation or whatever the case may be. So I hope you take some lessons from this for your own work. And I hope that if you do see if you're in the US and you do see your state in the reporting inaccuracy uh uh section here, that you also take action and contact your legislators to help improve the accuracy of the data. Thanks for watching. I'll talk to you soon.

Take care.