How to shift values in a dataframe column with pandas ?

September 27, 2021    /    Viewed: 237    /    Comments: 0    /    Edit


Examples of how to shift values in a dataframe column with pandas:

Create a dataframe with pandas

Let's first create a dataframe with pandas:

import pandas as pd 
import numpy as np

data = np.arange(1,31)
data = data.reshape(10,3)

df = pd.DataFrame(data=data,columns=['a','b','c'])

print(df)

gives then

    a   b   c
0   1   2   3
1   4   5   6
2   7   8   9
3  10  11  12
4  13  14  15
5  16  17  18
6  19  20  21
7  22  23  24
8  25  26  27
9  28  29  30

Shift dataframe values for a given column

Shifting rows down or up

To shift, for example, 3 rows down for the column c, a solution is to use pandas.DataFrame.shift. Parameter "periods" defined
the number of rows to shift:

df = df['c'].shift(periods=3)

print(df)

gives

0     NaN
1     NaN
2     NaN
3     3.0
4     6.0
5     9.0
6    12.0
7    15.0
8    18.0
9    21.0
Name: c, dtype: float64

To shift 3 rows up for the column c

df = pd.DataFrame(data=data,columns=['a','b','c'])

df = df['c'].shift(periods=-3)

print(df)

gives

0    12.0
1    15.0
2    18.0
3    21.0
4    24.0
5    27.0
6    30.0
7     NaN
8     NaN
9     NaN
Name: c, dtype: float64

Another example shifting only 1 row:

df = pd.DataFrame(data=data,columns=['a','b','c'])

df = df['c'].shift(periods=1)

print(df)

gives

0     NaN
1     3.0
2     6.0
3     9.0
4    12.0
5    15.0
6    18.0
7    21.0
8    24.0
9    27.0
Name: c, dtype: float64

Define fill_value

By default fill_value is NaN but it is possible to define another value using the parameter fill_value:

df = pd.DataFrame(data=data,columns=['a','b','c'])

df = df['c'].shift(periods=3, fill_value=-99999)

print(df)

gives

0   -99999
1   -99999
2   -99999
3        3
4        6
5        9
6       12
7       15
8       18
9       21
Name: c, dtype: int64

Create new columns with nearest neighbors values

Another example let's create new columns with nearest neighbors values

df = pd.DataFrame(data=data,columns=['a','b','c'])

df["n+1"] = df['c'].shift(periods=1, fill_value=-99999)
df["n+2"] = df['c'].shift(periods=2, fill_value=-99999)
df["n-1"] = df['c'].shift(periods=-1, fill_value=-99999)
df["n-2"] = df['c'].shift(periods=-2, fill_value=-99999)

print(df)

gives

    a   b   c    n+1    n+2    n-1    n-2
0   1   2   3 -99999 -99999      6      9
1   4   5   6      3 -99999      9     12
2   7   8   9      6      3     12     15
3  10  11  12      9      6     15     18
4  13  14  15     12      9     18     21
5  16  17  18     15     12     21     24
6  19  20  21     18     15     24     27
7  22  23  24     21     18     27     30
8  25  26  27     24     21     30 -99999
9  28  29  30     27     24 -99999 -99999

References


Card image cap
profile-image
Daidalos

Hi, I am Ben.

I have developed this web site from scratch with Django to share with everyone my notes. If you have any ideas or suggestions to improve the site, let me know ! (you can contact me using the form in the welcome page). Thanks!