Pandas – Shift column values up or down

Shifting column values can be quite handy particularly when working with time series related data. In this tutorial, we’ll look at how to shift values of a pandas dataframe column up and down through some examples.

How to shift a Pandas dataframe column?

You can use the pandas series shift() function to shift the column values up or down on the index. The following is the syntax:

df['Col'].shift(1)

Here, ‘Col’ is the column you want to shift. In the above syntax we shift the column values by 1 step. Pass the number of steps you want to shift to the function.

Examples

Let’s look at some examples of shifting a column’s value in pandas. First, we’ll create a sample dataframe that we’ll be using throughout this tutorial.

import pandas as pd

# dataframe of stock values
df = pd.DataFrame({
    'Open': [551.27, 543.22, 529.2, 519.85, 523],
    'Close': [546.13, 537.41, 516.71, 522.04, 519.59]
}, index=['25-01-2021', '26-01-2021', '27-01-2021', '28-01-2021', '29-01-2021'])

# show the dataframe
print(df)

Output:

              Open   Close
25-01-2021  551.27  546.13
26-01-2021  543.22  537.41
27-01-2021  529.20  516.71
28-01-2021  519.85  522.04
29-01-2021  523.00  519.59

Now we have a dataframe containing the open and close price of a dummy stock across five days.

1. Shift column values down

To shift the column values down, that is, to see values from prior indices, pass a positive step size to the shift() function. For example, let’s shift the values of the ‘Close’ column so that we get the close price of the stock from the previous date.

print(df['Close'].shift(1))

Output:

25-01-2021       NaN
26-01-2021    546.13
27-01-2021    537.41
28-01-2021    516.71
29-01-2021    522.04
Name: Close, dtype: float64

You can see that for each date we shifted the close price to its previous date. Note that since there was no prior data for the first date, we get a NaN when shifting with a positive step.

2. Shift column values up

To shift the column values up, that is, to see values from higher(or following) indices, pass a negative step size to the shift() function. For example, let’s shift the values of the ‘Close’ column so that we get the close price of the stock from the next date.

print(df['Close'].shift(-1))

Output:

25-01-2021    537.41
26-01-2021    516.71
27-01-2021    522.04
28-01-2021    519.59
29-01-2021       NaN
Name: Close, dtype: float64

In the output you can see that the close prices are shifted up, that is, we see the close price from the next date for each date. Now since the last close price does not have any following data we get NaN when shifting with a negative step.

Shifting column values can be important for data manipulation and feature creation particularly when working with time-series-based data. Building on the above example, let’s go ahead and add two more columns to our original dataframe. One for the closing price from the previous date and the other for the daily change in closing price.

# add column for close price on previous day
df['Close_day_before'] = df['Close'].shift(1)
# add column for daily change
df['Day_change'] = df['Close'] - df['Close_day_before']
# show the dataframe
print(df)

Output:

              Open   Close  Close_day_before  Day_change
25-01-2021  551.27  546.13               NaN         NaN
26-01-2021  543.22  537.41            546.13       -8.72
27-01-2021  529.20  516.71            537.41      -20.70
28-01-2021  519.85  522.04            516.71        5.33
29-01-2021  523.00  519.59            522.04       -2.45

The dataframe now has additional columns that give clear insights on whether the stock gained or lost value on a particular date.

This was just one example, you can use a different step size with shift depending on your use-case. For example, you may want to compare values on a weekly basis or monthly basis, etc.

For more on the pandas shift() function, refer to its documentation.

With this, we come to the end of this tutorial. The code examples and results presented in this tutorial have been implemented in a Jupyter Notebook with a python (version 3.8.3) kernel having pandas version 1.0.5