Using Previous Observations when Computation Values in a DataFrame
When you're analyzing data reported on a regular basis (ex: daily cases, monthly reports, etc), it is common to need to use the values from the previous one or more observations in your calculation. The df.column.shift(1) observation reports the value for a column from one observation earlier.
Below is a small subset of Johns Hopkins University's COVID-19 dataset. The JHU COVID-19 dataset contains the total cumulative number of COVID-19 cases in a given location:
The most direct use of the DataFrame.shift function is to add a new column based on shifting the data by a set amount. In our example data, using df.Confirmed.shift(1) creates a DataFrame that contains the confirmed cases from the pervious day:
Notice that the new column, ConfirmedYesterday, contains the new daily cases each day. On 2020-05-01, the column records a missing value (NaN) since there was no previous row to use in the calculation.
Calculating the Change from the Previous Observation
When a cumulative total is provided in sorted order, the difference between the observations indicates the change in the data. In the case of our example data, the difference between any two observations is the daily change in COVID-19 cases in Illinois.
The shift(1) function will shift the contents of the DataFrame, allowing us to subtract the current day's confirmed case count from the previous day's confirmed case count. The code below create a new column DailyConfirmed:
Notice that the new column, DailyConfirmed, contains the new daily cases each day. On 2020-05-01, the column records a missing value (NaN) since there was no previous row to use in the calculation.
Calculating a Running Average
A "running average" is a term used to calculate an average based off a fixed previous number of observations, which is constantly updated each day. In the case of our example data, a "7-day running average" of cases provides us with the average number of new cases over the past week by calculating the average new of daily new cases over the most recent seven observations.
The shift function can be used to shift the DataFrame by any number of rows. To calculate the weekly running average, we need the current daily cases AND data from the previous six days to get the full 7-day running average: