Moving averages — Demonstrated using Covid Data

Sometimes I do not like data analytics. I feel all the focus is on the trends and the minute details are lost or ignored. The focus of such an analysis is mostly on the 80/20 principle. What happens to the problems which are unattended? It is always a worry.

But on the other hand, I feel the bigger picture does help, and analyzing data from that perspective is definitely helpful.

This particular post is about moving averages. Moving average is a simple concept that helps analyze the long term trend in data by smoothening the data so that there are no distractions based on the daily fluctuations.

Here, I have come up with two plots. The first one plots the daily hospitalized covid cases in a state in India. The second one is a 7 day moving average of the same data. We can see the daily fluctuations in the first one. The second one is more smoothened and focuses mainly on the trend & we can observe that there is a clear downward trend.

Daily Hospitalised Covid Cases — Visualisation

7 Day Moving Average — Hospitalised Cases

The calculation:

I am linking here the spreadsheet used for the calculation. Also, attaching screenshots to show how the formula is applied on the spreadsheet to calculate the values to make a moving average plot. We calculate the average of the first 7 values to find the first moving average. The next moving average is calculated by calculating the average of the next 7 values and the entries go on. Simple x,y plots using line graphs are then made with these values to understand the trend of the data. (Below are the screenshots of the calculations)

Moving Average — Calculations — Entry 1

Moving Average Calculations — Entry 2 (And the subsequent entries)

(All the data has been taken from the covid19india.org. The required data is extracted manually and there can be minor variations in the numbers here and there and they haven’t been corrected since it is used solely for this blog)