How to Shift a Column in Pandas (With Examples)


You can use the shift() function to shift the values of a column up or down in a pandas DataFrame:

#shift values down by 1
df['column1'] = df['column1'].shift(1)

#shift values up by 1
df['column1'] = df['column1'].shift(-1)

The following examples show how to use this function in practice with the following pandas DataFrame:

import pandas as pd

#create DataFrame
df = pd.DataFrame({'product': ['A', 'B', 'C', 'D', 'E', 'F'],
                   'sales': [4, 7, 8, 12, 15, 19]})

#view DataFrame
df

        product	sales
0	A	4
1	B	7
2	C	8
3	D	12
4	E	15
5	F	19

Example 1: Shift One Column Up or Down

The following code shows how to shift all of the values of the ‘product’ column down by 1:

#shift all 'product' values down by 1
df['product'] = df['product'].shift(1)

#view updated DataFrame
df

	product	sales
0	NaN	4
1	A	7
2	B	8
3	C	12
4	D	15
5	E	19

Notice that each value in the ‘product’ column has been shifted down by 1 and the first value in the column has been replaced with NaN.

Also notice that the last value in the product column (‘F’) has been removed from the DataFrame entirely.

To keep the ‘F’ value in the DataFrame, we need to first add an empty row to the bottom of the DataFrame and then perform the shift:

import numpy as np

#add empty row to bottom of DataFrame
df.loc[len(df.index)] = [np.nan, np.nan]

#shift all 'product' values down by 1
df['product'] = df['product'].shift(1)

#view updated DataFrame
df

	product	sales
0	NaN	4.0
1	A	7.0
2	B	8.0
3	C	12.0
4	D	15.0
5	E	19.0
6	F	NaN

Notice that the ‘F’ value is retained as the last value in the ‘product’ column.

Example 2: Shift Multiple Columns Up or Down

The following code shows how to shift all of the values of the ‘product’ and ‘sales’ columns up by 2:

#shift all 'product' and 'sales' values up by 2
df[['product', 'sales']] = df[['product', 'sales']].shift(-2)

#view updated DataFrame
df

        product	sales
0	C	8.0
1	D	12.0
2	E	15.0
3	F	19.0
4	NaN	NaN
5	NaN	NaN

Notice that each value in the ‘product’ and ‘sales’ column has been shifted up by 2 and the bottom two values in each column have been replaced with NaN.

Note: You can find the complete documentation for the shift() function here.

Additional Resources

The following tutorials explain how to perform other common operations in pandas:

How to Add Rows to a Pandas DataFrame
How to Add a Column to a Pandas DataFrame
How to Add Header Row to a Pandas DataFrame

Leave a Reply

Your email address will not be published.