Shift and lag operations in Data Analysis Python

Introduction

Shift and lag operations help you compare data points in a sequence by moving values up or down. This is useful to see changes over time or between steps.

To compare today's sales with yesterday's sales in a store.

To find the difference in temperature between consecutive days.

To analyze stock prices by comparing current price with previous day's price.

To detect trends or changes in time series data like website visits over days.

Syntax

Data Analysis Python

df['new_column'] = df['existing_column'].shift(n)

n is the number of positions to move. Positive n shifts down (lag), negative n shifts up (lead).

Missing values appear where data is shifted beyond the start or end.

Examples

This creates a new column with values shifted down by 1, showing the previous row's value.

Data Analysis Python

df['lag_1'] = df['value'].shift(1)

This shifts values up by 1, showing the next row's value.

Data Analysis Python

df['lead_1'] = df['value'].shift(-1)

This shifts values down by 2 rows, showing the value two steps before.

Data Analysis Python

df['lag_2'] = df['value'].shift(2)

Sample Program

This code creates a table of sales over 5 days. It adds two new columns: one with the previous day's sales and one with the next day's sales, using shift operations.

Data Analysis Python

import pandas as pd

data = {'day': [1, 2, 3, 4, 5], 'sales': [100, 150, 130, 170, 160]}
df = pd.DataFrame(data)

df['previous_day_sales'] = df['sales'].shift(1)
df['next_day_sales'] = df['sales'].shift(-1)

print(df)

Important Notes

Shift does not change the order of rows, it only moves values within a column.

Missing values (NaN) appear where there is no data to fill after shifting.

You can use shift with any numeric or categorical column to compare rows.

Summary

Shift moves data up or down to compare rows in sequence.

Positive shift values create lag (previous rows), negative create lead (next rows).

Useful for time series and sequential data analysis.

Practice

1. What does the shift(1) function do to a pandas DataFrame column?

easy

A. Moves the data down by one row, creating a lag of previous values

B. Moves the data up by one row, creating a lead of next values

C. Sorts the column in ascending order

D. Removes the first row of the column

Solution

1. Step 1: Understand the shift function

   The shift(1) moves data down by one row, so each value is replaced by the previous row's value.

2. Step 2: Identify lag vs lead

   Positive shift values create lag (previous rows), so shift(1) creates lag by one row.

3. Final Answer:

   Moves the data down by one row, creating a lag of previous values -> Option A

4. Quick Check:

   shift(1) = lag by 1 row [OK]

Hint: Positive shift means lag (previous rows), negative means lead [OK]

Common Mistakes:

• Confusing shift(1) with shift(-1)
• Thinking shift sorts data
• Assuming shift removes rows

2. Which of the following is the correct syntax to create a new column 'prev_value' that contains the previous row's value of column 'value' in pandas?

easy

A. df['prev_value'] = df['value'].shift(-1)

B. df['prev_value'] = shift(df['value'], 1)

C. df['prev_value'] = df.shift('value', 1)

D. df['prev_value'] = df['value'].shift(1)

Solution

1. Step 1: Recall pandas shift syntax

   The correct syntax is to call shift() on the Series, like df['value'].shift(1).

2. Step 2: Check each option

   df['prev_value'] = df['value'].shift(1) uses correct syntax. df['prev_value'] = df['value'].shift(-1) shifts in the wrong direction. df['prev_value'] = df.shift('value', 1) uses wrong method call. df['prev_value'] = shift(df['value'], 1) uses undefined function.

3. Final Answer:

   df['prev_value'] = df['value'].shift(1) -> Option D

4. Quick Check:

   Correct pandas shift syntax = df['prev_value'] = df['value'].shift(1) [OK]

Hint: Use Series.shift(n) to lag by n rows [OK]

Common Mistakes:

• Using negative shift for previous row
• Calling shift on DataFrame with wrong arguments
• Using undefined shift function

3. Given the DataFrame:

import pandas as pd
df = pd.DataFrame({'value': [10, 20, 30, 40]})
df['lag1'] = df['value'].shift(1)
df['lead1'] = df['value'].shift(-1)
print(df)

What is the output?

medium

A. value lag1 lead1
0 10 10.0 20.0
1 20 20.0 30.0
2 30 30.0 40.0
3 40 40.0 NaN

B. value lag1 lead1
0 10 20.0 NaN
1 20 30.0 10.0
2 30 40.0 20.0
3 40 NaN 30.0

C. value lag1 lead1
0 10 NaN 20.0
1 20 10.0 30.0
2 30 20.0 40.0
3 40 30.0 NaN

D. value lag1 lead1
0 10 NaN NaN
1 20 10.0 20.0
2 30 20.0 30.0
3 40 30.0 40.0

Solution

1. Step 1: Calculate lag1 column

   shift(1) moves values down by one, so first row is NaN, second row gets 10, third 20, fourth 30.

2. Step 2: Calculate lead1 column

   shift(-1) moves values up by one, so last row is NaN, third row gets 40, second 30, first 20.

3. Final Answer:

   value lag1 lead1 0 10 NaN 20.0 1 20 10.0 30.0 2 30 20.0 40.0 3 40 30.0 NaN -> Option C

4. Quick Check:

   Positive shift = lag, negative shift = lead [OK]

Hint: Positive shift adds NaN on top, negative shift adds NaN at bottom [OK]

Common Mistakes:

• Mixing lag and lead directions
• Forgetting NaN appears at shifted edges
• Confusing shift with sorting

4. What is wrong with this code snippet?

df['prev'] = df.shift(1)['value']

Assuming df is a pandas DataFrame with a column 'value'.

medium

A. It works correctly and creates a lagged column

B. It shifts the entire DataFrame, not just the 'value' column, which is inefficient

C. It causes a syntax error due to wrong indexing

D. It will cause a KeyError because shift returns a Series, not DataFrame

Solution

1. Step 1: Understand what df.shift(1) does

   df.shift(1) shifts the entire DataFrame down by one row, including all columns.

2. Step 2: Check indexing and efficiency

   Accessing ['value'] after shift works, but shifting entire DataFrame is inefficient if only one column is needed.

3. Final Answer:

   It shifts the entire DataFrame, not just the 'value' column, which is inefficient -> Option B

4. Quick Check:

   Shift on DataFrame shifts all columns [OK]

Hint: Shift only the needed column, not whole DataFrame [OK]

Common Mistakes:

• Assuming shift returns Series when called on DataFrame
• Expecting syntax error from indexing
• Ignoring performance impact

5. You have a time series DataFrame df with a column 'sales'. You want to create a new column 'pct_change' that shows the percentage change from the previous day. Which code correctly uses shift to calculate this?

hard

A. df['pct_change'] = (df['sales'] - df['sales'].shift(1)) / df['sales'].shift(1) * 100

B. df['pct_change'] = (df['sales'].shift(1) - df['sales']) / df['sales'] * 100

C. df['pct_change'] = df['sales'].pct_change(1)

D. df['pct_change'] = df['sales'].shift(-1) / df['sales'] * 100

Solution

1. Step 1: Understand percentage change formula

   Percentage change = (current - previous) / previous * 100.

2. Step 2: Apply shift to get previous value

   df['sales'].shift(1) gives previous day's sales, so formula in df['pct_change'] = (df['sales'] - df['sales'].shift(1)) / df['sales'].shift(1) * 100 matches the formula.

3. Step 3: Check other options

   df['pct_change'] = (df['sales'].shift(1) - df['sales']) / df['sales'] * 100 reverses numerator and denominator incorrectly. df['pct_change'] = df['sales'].pct_change(1) uses built-in method but question asks for shift usage. df['pct_change'] = df['sales'].shift(-1) / df['sales'] * 100 uses lead instead of lag.

4. Final Answer:

   df['pct_change'] = (df['sales'] - df['sales'].shift(1)) / df['sales'].shift(1) * 100 -> Option A

5. Quick Check:

   Percentage change uses lag with shift(1) [OK]

Hint: Use (current - shift(1)) / shift(1) for percent change [OK]

Common Mistakes:

• Using shift(-1) instead of shift(1)
• Reversing numerator and denominator
• Confusing built-in pct_change with manual shift