Implement SQL LAG and LEAD with .shift() in Pandas

Question

Given user page visits with timestamps, calculate the time elapsed since the previous visit for each user.

Accepted Answer

Optimal Solution:
import pandas as pd

df['visit_time'] = pd.to_datetime(df['visit_time'])
df = df.sort_values(['user_id', 'visit_time'])

# Shift 1 row down within user group (equivalent to SQL LAG)
df['prev_visit'] = df.groupby('user_id')['visit_time'].shift(1)

df['elapsed_min'] = (df['visit_time'] - df['prev_visit']).dt.total_seconds() / 60.0

result_df = df[['user_id', 'visit_time', 'elapsed_min']]
result_df['visit_time'] = result_df['visit_time'].dt.strftime('%Y-%m-%d %H:%M:%S')

Technical Explanation:
`shift(1)` moves values down by 1 row within each user partition (LAG). `shift(-1)` moves values up by 1 row (LEAD).