Saturday Night Data Party: First and Second Order Derivatives

Alright, let's get started. Tonight we are going to be going through the COVID-19 data set that Johns Hopkins University publishes. You're familiar with this dashboard, everyone has seen it uh many many times. Uh what we want to do though is try and get the underlying data to do some stuff because you can see here this is like really cool charts and graphs and things, but as with every tool, there's more that we could do with this. So you'll notice in the credits down here, it tells you uh you get there are accessible data sources.

There's one on GitHub, so let's go click through on that. If you're here, uh stop by and say hello. We have case data for this, which is what we want to go after, and we want time series data because time series data is gonna be probably the most useful thing. So let's see what we got here. Deprecated tables.

We want confirmed cases. I guess we could do confirmed cases and maybe deaths too. Let's stick with confirmed cases for now. We have uh let's see. Oh, okay.

So we have province, we have country, latitude, longitude, and then the date series.csv. Let's switch over from COVID data to our R environment. Make sure we got the data. Yep. Looks good.

Time for a new file. Alright. Put our data folder there. And let's go ahead and see what happens. Hi, Ashley.

How you doing? We are looking at uh COVID data tonight. We have a data frame that has 249 observations of 70 variables. This is what we were looking at earlier. That's the province, country, latitude, longitude, and then what they've done is they've put the data in a whole series of columns.

Ugh, that's really messy. Alright, let's see. Let's save this. Let's do a well, so look the date the dates are by an X in front of each column. That's useful.

Alright, let's do a pivot. Let's do pivot longer. And we want to the columns equals starts with all the date columns start with an X. Will you not be able to sleep after watching this live stream? You will be.

Well, I don't know. Um what I think what I wanted to do with this was look at um look at the data to see what else we can do with it to see some useful information that isn't in the original data, because in the original data set you have you know lots of cool charts and graphs and visualizations, which I wholly applaud, but you can't really do much with this, so we're gonna try and do something with the data tonight. So let's first do a pivot here. And now this should have taken all of our let's do names to the date. There we go.

That changes that field. Now the date field has got the dates in it sort of, but they're still wrong. So mutate and we're gonna go change the date field equals first. Let's get rid of that X. Replace it with nothing for date.

Good. And now let's do a second mutate. We should just do it right in line here, and we're gonna do equals. We're gonna change the underscore to a slash in the date. And then a third mutation, the date equals.

What do we got here? This is this is uh in American, so it's month day year. Now if we did this right, this is going to do tree three transformations to get rid of the X in front, turn the underscores into slashes, and then there we go. So now we have date lines. Let's take a look at that.

So now we have country, latitude, longitude, the date, and then the value. I want to rename value too while we're here. So with this pre-processing, we've now been able to identify or clean up this table. We have 16,000 lines of data by country, latitude, longitude, the date, and the cases. So now let's look at just do America.

And we're gonna do a filter equals. Let's see what we get. Let's do the United Kingdom. Unless for some reason Johns Hopkins is storing the US data in a different location. So this is the daily reports.

Hmm. Let's see what's in the daily reports folder. Oh, that's an awful lot of stuff. Hi Kate. Let's see what's in the upcoming changes series.

ISOCUD. County level for the US. Three new tables will be added for the United States. Ah. Okay.

Where are they? Yeah, I think it has county and city for the New York Times has County level. Alright, let's see state level data in the United States. The New York version of the file. Well that's a much better ordered table anyway.

Let's just use that one. Nice that they included FIPS codes too. So let's take that. We won't need any of the mutation data. Okay, let's see what we got.

It did not. Let's do a type convert. All right, we have dates now, FIPS code, which we don't need, cases and deaths. All right, so let's do arrange at first by state and then arrange by date. Okay.

And now let's do one for that's national. And we'll do summarize. Let's do a group by date. Summarize by total cases equals sum cases. That looks better.

So we have. Alright, good. And just to double check that nothing went. Heywire arrange by date. And now what we want to do is we want to do percent changes.

So anytime you have a data series like this, you have essentially what happened, sort of a positional data. What we would next want to take a look at is is there oops, what did I just do there? There we go. Alright, so we have our national data. And what we want to do is is look at how fast is something increasing.

So let's add a percent change equals uh let's see cases divided by lead of cases minus one times one hundred. Oh, total cases. So now we have lead by cases, zero change, lead. Actually, it should be lag. Go in the wrong direction.

Could we arrange the dataset by ascending date? Yep, there we go. So a hundred percent more cases from that day, and so on and so forth. Let's do this ggplot loaded. Let's do a ggplot data equals National DF aesthetic X axis will be date.

Y axis will be percent change. And our geometry will be a line. And our geometry will be point. Okay, so there's our percent change day over day. And percent change.

Now we're looking at consistent change once you get into into March. For reference sake, let's do this. Let's do our just our cases number here. Look at that first. Oops.

So there's our total cases. That's what you see on the Johns Hopkins chart, which by the way is terrifying to look at. And now if we go, let's label that. And now if we label this percent change. So this is the equivalent for if for those of you who uh don't remember, uh, this is essentially a first order derivative function.

So instead of looking at the total number of cases, looking how fast is something changing. And a first order derivative is equivalent of velocity. So if you have on a map point A and point B the distance between two points, this the subtraction of two over time turns that into velocity. How fast did you go? Now, if we want to take this and do another derivative, um, you could actually do what's called a second order derivative, which would be your acceleration, which is what we want to do next.

So let's do I'm gonna do a mutate on acceleration equals percent change. By the way, lag percent change minus one. Duh, can't type. Alright, so now we should have an acceleration number. Let's take a look at that.

Yep. Alright, now let's do acceleration. So the derivative of the derivative means how is the is this going faster or slower? Is the speed itself changing? And now this time instead of percent change, we want to graph acceleration.

And so you can see there's consistent ups and downs here. So the first half really is kind of not useful because uh there was so little data to work with. But once you start getting into uh into March, you now see acceleration, deceleration, acceleration, deceleration. What this means is that we still are having trouble containing this thing, right? Because your acceleration in a fair number of cases is over zero.

What we would want to see is you want to see a a deceleration. You want this number to be below zero because if it's below zero, it means that the speed of change is slowing down. And once you get to that point, then you start being able to say, okay, I you know this thing might be uh starting to get in control. But it for any period of time where you're having you know things pick up the pace, that's bad. Right?

Um and clearly there were when you look at the further the first half of March, there were definitely more periods of time where the acceleration was above than there was below, and then even the first week after the 15th, and now we're starting to hopefully see things slow down. So this analysis is the kind of thing you would want to run in order to see is this thing under control? Now if we there's two things to be careful of. Number one, if we're increasing testing, these numbers are gonna go wildly wrong because you're just detecting more cases of this. Um and that it's better to know than not know.

Uh, but it will throw off this analysis because you know you that there's gonna be more cases. So that's one aspect. The second aspect is that um even if it's decelerating, it's like, you know, even if a bullet's slowing down, it's still gonna hurt you, unless you're at the very, very, very end of it. So it's it's by no means once you start to see the deceleration happen, by no means is is it you know the the fun uh uh is the ride over. It is it is still continuing to be dangerous.

So we but this when you see acceleration just stay above zero, you know that we don't have control of it. Now this analysis is really useful for stuff other than life or death pandemics. Um yeah, cases versus testing cases with population density is probably more interesting. The other thing that's interesting would be um uh modeling other numbers like deaths, because a case may be detectable or may not be detectable. Uh uh a deceased person is not something that goes away as easily or not thrown off.

Now you can have obviously misdiagnosis, but if you have somebody who is presumed positive needed to be intubated and and they were uh they didn't make it, that number is a little easier to work with. In fact, let's go slightly morbid. Let's see if we can get that out of the New York Times. Oh yeah, this isn't the New York Times data set. There's a deaths column.

Uh let's see what we got here. Yep, deaths. All right. So instead of doing cases, let's do equals some deaths. And let's add two new mutations.

Alright, so now we should have columns for deaths. So let's look at those numbers. So here's our death rate. Woof, that's something. Hmm.

Um and then let's look at our percent change deaths. So after that initial percent change again, there. And then let's look at our death acceleration. Zoom that opened. Sounds good, Kate.

Take care. Stay safe, please. Stay safe. Um our death rate is staying above zero a little more, right? And then there's some pretty sharp numbers here.

And the overall trend is not going in the right direction. If I remember correctly, how do I plot a linear regression? See if I can remember how to do this. Alright, let's do I just slap a smooth geometry on this. Let's find out.

Huh. Yep. That worked. That's bad. Um so what I did was added a simple linear regression onto the the chart that shows the general trend things going in.

Our zero line is right here. So we are trending upwards in the acceleration of deaths. So even though the case detection stuff is uh maybe it have issues because of testing. Now looking at the rate of deaths accelerating is showing that this is this is not great. In fact, let's slap the that geometry onto each of these.

So let's do rate of deaths first. Yep, there's our line. That's uh this is case, by the way, where linear regression is clearly the wrong measure because that's an exponential curve. Our rate of change has a relatively as a poor fit on the first half of those lines. Um and then let's look at our death curve here.

That is not pretty. And you could use a uh simple linear aggression models to fit and figure out okay, is that blue line going up or down? Uh and that helps you understand, yep, we've got a uh either I got a great situation happening here, we got a problem, right? Would be the the two different ways to look at that. So take a look at this.

Now you don't have to use um R or a stats programming, which you could actually do all this inside of Excel. It's just boring to look at for me, and I need to practice. Um but you can absolutely do this inside of Excel. You'd have one column with your dates, you have the next column, which is your percentage change, your next column, which is your percentage change, your percentage change, and then make charts out of that. So it's something you can do in Excel.

Doing it this way allows you to plug into other data sources. If I wanted to, I could connect Google Analytics to this and do that. Or in this case, we're using it for uh, you know, the current situation. The current situation, as we see, is bad. And if this was something much more mundane, like web analytics, and the numbers were going up, we'd say, great, whatever it is we're doing, let's keep doing that.

Or if the you know it's something bad, you know, balance rate or whatever, um, you would say, okay, let's do less of this. But either way, take this set of techniques and use it on a data series of your choice, debt, financial spending, you name it, and do your percentage change and then your percentage change and your percentage change to see how you can use these numbers for uh for better analysis of any of any time series data, any time series trend. All right, folks, that's gonna do it for this week's uh Saturday night data party. Thank you for joining me. Thank you for watching it on replay.

If you watched it on replay, I'm gonna load this up to our YouTube channel as well so that you can catch that. Uh I hope you learned something. I hope you take this technique and use it for something that's uh useful in your marketing. Um so it's not just life or death data. I just wanted to see what we could do with the Hopkins data.

Thanks for watching. We'll talk to you soon. Take care, please, and stay well. 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.