LAG & LEAD (shift, diff)
Python · GroupBy & SQL-Style Analytics
Theory
s.shift(1) moves values down one row, so each row sees the previous row's value - SQL's LAG(). shift(-1) moves them up, giving the next row's value - LEAD(). The first row (for lag) or last row (for lead) has no neighbour and gets NaN; fill_value=0 changes that. s.diff() is s - s.shift(1), the change from the previous row, and s.pct_change() is the relative change, (current - previous) / previous.
As with all window-style work, sort first, usually by date. To stop values leaking between groups, call the method on a groupby: df.groupby('city')['amount'].shift(1) gives the previous amount within the same city only - LAG(amount) OVER (PARTITION BY city ORDER BY month). groupby(...).diff() and groupby(...).pct_change() work the same way. Typical uses: month-over-month growth, days since previous order (date.diff().dt.days), and spotting when a value changed (s != s.shift()).
Why we use it
Comparisons with the previous period are at the heart of reporting: growth versus last month, gap between orders, change in price. shift/diff/pct_change do this without self-joins or loops.
When to use it
Use shift for raw previous/next values, diff for absolute change and pct_change for growth rates. Always sort by time first and use groupby for per-entity series. For averages over several previous rows, use rolling instead.
How it works
- Sort by key and time
- Group by the partition key
- shift(1) brings the previous row's value alongside
- First row of the group? Yes: No previous value: NaN. No: Compute diff / pct_change.
- Previous, change and growth columns
Syntax
s.shift(1) # LAG
s.shift(-1) # LEAD
s.diff(); s.pct_change(fill_method=None)
df.groupby('key')['col'].shift(1) / .diff() / .pct_change(fill_method=None)Example
df = df.sort_values(['city', 'month'])
df['prev'] = df.groupby('city')['amount'].shift(1)
df['growth_%'] = df.groupby('city')['amount'].pct_change(fill_method=None) * 100Key points
- shift(1) = LAG, shift(-1) = LEAD; edges become NaN unless fill_value= is given
- diff() is current minus previous; pct_change() is the relative change
- Sort by time first, and use groupby so values never leak across groups
- Pass fill_method=None to pct_change to avoid a deprecation warning when NaN exist
- SQL equivalent: LAG(amount) OVER (PARTITION BY city ORDER BY month)