Window Operations: shift, rank & Running Totals

Many business questions are about a row and its neighbours: "How much did sales grow since last month?", "What is the running total so far this year?", "Who is the top scorer in each city?", "What was the longest dry spell?". A normal groupby() squeezes each group into one row, so it cannot answer them. Window operations keep every row and add a value calculated from a window of related rows.

In this lesson you will use shift(), diff(), cumulative methods, rank(), group-aware lags, rolling and exponential windows, and a trick for finding streaks. If you know SQL, these are the pandas versions of LAG, RANK and SUM() OVER (...) from Window Functions.

Looking back: shift, diff and running totals

shift(1) moves every value one row down, so each row can see the previous row's value. diff() is the difference from the previous row. The cumulative methods cumsum(), cummax(), cummin() and cumprod() give a running result, and expanding() works like a window that starts at the first row and grows.

import pandas as pd

runs = pd.DataFrame({
    "match": [1, 2, 3, 4, 5, 6],
    "runs": [34, 0, 71, 12, 105, 48],
})
runs["previous"] = runs["runs"].shift(1)
runs["change"] = runs["runs"].diff()
runs["total"] = runs["runs"].cumsum()
runs["best_so_far"] = runs["runs"].cummax()
runs["average_so_far"] = runs["runs"].expanding().mean().round(1)
print(runs)

   match  runs  previous  change  total  best_so_far  average_so_far
0      1    34       NaN     NaN     34           34            34.0
1      2     0      34.0   -34.0     34           34            17.0
2      3    71       0.0    71.0    105           71            35.0
3      4    12      71.0   -59.0    117           71            29.2
4      5   105      12.0    93.0    222          105            44.4
5      6    48     105.0   -57.0    270          105            45.0

The first row has no previous match, so previous and change are NaN there. best_so_far only rises when a new record is set (71, then 105), and average_so_far is the batting average after each match. All of these depend on the row order, so always sort by date or match number first.

Ranking values and ties

rank() gives each value its position. The interesting part is how ties are handled, which you choose with method:

import pandas as pd

marks = pd.Series([92, 85, 92, 78, 85, 99],
                  index=["Aarav", "Priya", "Kabir", "Meera", "Rohan", "Ananya"])
table = pd.DataFrame({
    "marks": marks,
    "average": marks.rank(ascending=False),
    "min": marks.rank(ascending=False, method="min"),
    "dense": marks.rank(ascending=False, method="dense"),
    "first": marks.rank(ascending=False, method="first"),
    "pct": marks.rank(pct=True).round(2),
})
print(table.sort_values("marks", ascending=False))

        marks  average  min  dense  first   pct
Ananya     99      1.0  1.0    1.0    1.0  1.00
Aarav      92      2.5  2.0    2.0    2.0  0.75
Kabir      92      2.5  2.0    2.0    3.0  0.75
Priya      85      4.5  4.0    3.0    4.0  0.42
Rohan      85      4.5  4.0    3.0    5.0  0.42
Meera      78      6.0  6.0    4.0    6.0  0.17

ascending=False gives rank 1 to the highest mark. pct=True turns ranks into a percentile rank between 0 and 1: Meera's 0.17 means she is at the bottom sixth of this small group. Pick the method that matches the rule you are reporting; a merit list usually wants "min", while "the 3rd highest distinct mark" wants "dense".

Top N within each group

Add groupby() in front of rank() and the ranking restarts inside each group. This is the standard way to find "top 3 per city" or "best-selling product per category".

import pandas as pd

students = pd.read_csv("students.csv", na_values=["AB"]).drop_duplicates()
students["city_rank"] = students.groupby("city")["maths"].rank(ascending=False, method="min")

top3 = (
    students[students["city_rank"] <= 3]
    .sort_values(["city", "city_rank"])
    [["city", "name", "maths", "city_rank"]]
)
print(top3[top3["city"].isin(["Jaipur", "Kochi"])].to_string(index=False))

best = students.sort_values("maths", ascending=False).groupby("city").head(1)
print(best[["city", "name", "maths"]].sort_values("city").to_string(index=False))

  city             name  maths  city_rank
Jaipur Ananya Deshpande  100.0        1.0
Jaipur      Divya Joshi   88.0        2.0
Jaipur    Ishita Ansari   88.0        2.0
 Kochi     Payal Thomas   98.0        1.0
 Kochi    Fatima George   94.0        2.0
 Kochi    Ananya Thomas   93.0        3.0
   city             name  maths
 Jaipur Ananya Deshpande  100.0
  Kochi     Payal Thomas   98.0
Lucknow  Gauri Deshpande  100.0
   Pune        Kunal Rao  100.0
 Rohtak       Dev Ansari   97.0

Jaipur has a tie for 2nd place, so with method="min" the next rank would be 4 and only three students qualify. The second approach, sort then groupby().head(1), is shorter but hides ties: Lucknow has more than one student with 100, and head(1) keeps just one of them. Decide whether ties matter before choosing.

Group-aware lags: change per city

The classic bug in time comparisons is shifting across group boundaries. If your table holds monthly revenue for ten cities one after another, a plain shift(1) gives Chennai's January the previous city's December. groupby("city")["revenue"].shift(1) restarts the shift for each city.

import pandas as pd

sales = pd.read_csv("sales.csv", parse_dates=["order_date"]).drop_duplicates()
sales = sales[sales["quantity"].between(1, 20)]
sales["revenue"] = sales["quantity"] * sales["unit_price"] * (1 - sales["discount"] / 100)
sales["city"] = sales["city"].str.strip().str.title().replace({"Bangalore": "Bengaluru", "Cochin": "Kochi"})

monthly = (
    sales.assign(month=sales["order_date"].dt.to_period("M"))
    .groupby(["city", "month"], as_index=False)["revenue"].sum()
    .sort_values(["city", "month"])
)
monthly["prev_month"] = monthly.groupby("city")["revenue"].shift(1)
monthly["growth_pct"] = ((monthly["revenue"] / monthly["prev_month"] - 1) * 100).round(1)
monthly["ytd"] = monthly.groupby("city")["revenue"].cumsum()
pune = monthly[monthly["city"] == "Pune"].head(4)
print(pune.round({"revenue": 0, "prev_month": 0, "ytd": 0}).to_string(index=False))

wrong = monthly["revenue"].shift(1)
print(monthly.loc[[12, 24], ["city", "month"]].assign(wrong_prev=wrong.loc[[12, 24]].round(0)))

city   month  revenue  prev_month  growth_pct     ytd
Pune 2025-01   7655.0         NaN         NaN  7655.0
Pune 2025-02  14411.0      7655.0        88.3 22066.0
Pune 2025-03  17496.0     14411.0        21.4 39562.0
Pune 2025-04  13400.0     17496.0       -23.4 52962.0
       city    month  wrong_prev
12  Chennai  2025-01     22421.0
24    Delhi  2025-01     11807.0

Pune's January correctly has no previous month, and ytd (year to date) restarts for every city. The second table shows the bug: with a plain shift(1), Chennai's January "previous month" is really Bengaluru's December, a number that has nothing to do with Chennai. No error appears, which is why this mistake survives into real reports.

Rolling and exponential windows per group

You met rolling() in Dates & Time Series. Grouped rolling works the same way per group, but the result has the group name as an extra index level, which you drop before assigning it back. An exponentially weighted mean (ewm) is a smoother alternative: recent days count most, older days count less and less, and no day suddenly falls out of the window.

import pandas as pd

weather = pd.read_csv("weather.csv", parse_dates=["date"]).sort_values(["city", "date"])
weather["rain_7d"] = (
    weather.groupby("city")["rainfall_mm"]
    .rolling(7, min_periods=7).sum()
    .reset_index(level=0, drop=True)
)
weather["temp_ewm"] = weather.groupby("city")["temp_max"].transform(lambda s: s.ewm(span=7).mean())

kochi = weather.loc[weather["city"] == "Kochi", ["date", "rainfall_mm", "rain_7d", "temp_max", "temp_ewm"]]
print(kochi.iloc[150:155].round({"rain_7d": 1, "temp_ewm": 1}).to_string(index=False))

      date  rainfall_mm  rain_7d  temp_max  temp_ewm
2025-05-31          4.6     47.6      29.6      29.5
2025-06-01         19.7     65.3      28.4      29.2
2025-06-02         66.5    124.0      27.1      28.7
2025-06-03        108.9    205.2      25.9      28.0
2025-06-04         29.7    229.4      28.0      28.0

rain_7d is the total rain in the last seven days, a common feature for flood or demand forecasting: you can watch the monsoon arrive in Kochi as it jumps from 47.6 mm to 229.4 mm in five days. temp_ewm follows the temperature but reacts gently. span=7 sets how fast the weights fade; a bigger span means a smoother line.

Advanced: streaks and spells

"Longest dry spell", "longest winning streak" and "consecutive days without a sale" all use one trick. Mark where the condition changes compared with the previous row, then cumsum() those change points: every run of identical values gets its own ID, which you can group by.

import pandas as pd

weather = pd.read_csv("weather.csv", parse_dates=["date"]).sort_values(["city", "date"])
weather["dry"] = weather["rainfall_mm"] == 0

new_spell = weather["dry"] != weather.groupby("city")["dry"].shift()
weather["spell_id"] = new_spell.cumsum()

spells = (
    weather[weather["dry"]]
    .groupby(["city", "spell_id"])
    .agg(start=("date", "min"), end=("date", "max"), days=("date", "size"))
)
longest = spells.sort_values("days", ascending=False).groupby("city").head(1)
print(longest.reset_index(level=1, drop=True).sort_values("days", ascending=False))

             start        end  days
city                               
Chennai 2025-02-09 2025-05-08    89
Mumbai  2025-02-25 2025-05-07    72
Jaipur  2025-01-20 2025-03-28    68
Delhi   2025-10-01 2025-11-16    47
Kochi   2025-01-01 2025-02-06    37

The shifted value for each city's first row is missing, so the comparison is True there and every city starts a fresh spell. Chennai went 89 days without rain before the summer showers, while even Kochi, the wettest city, had a 37-day dry spell in January.

Common mistakes

• Forgetting to sort. shift, cumsum, rolling and ewm follow row order. Sort by group and date first.
• Shifting across groups. Use groupby(...).shift() whenever the table holds more than one series.
• Ignoring ties. Choose a rank() method deliberately, and remember that head(1) drops tied rows.
• Assigning grouped rolling results directly. Drop the extra group level (or use transform) so the index lines up with the original rows.

Key points

• shift() and diff() compare each row with earlier rows; pct_change() gives the percentage change.
• cumsum(), cummax() and expanding() give running totals, records and averages.
• rank() ranks values; method controls ties and pct=True gives percentile ranks.
• groupby(...).rank(), .shift() and .cumsum() restart inside each group.
• Grouped rolling() and ewm() smooth each series separately.
• Change points plus cumsum() label streaks and spells.

import pandas as pd

emp = pd.read_csv("employees.csv").dropna(subset=["salary"])
emp["dept_rank"] = emp.groupby("department")["salary"].rank(ascending=False, method="min")
emp["gap_to_top"] = emp.groupby("department")["salary"].transform("max") - emp["salary"]
emp["salary_pct"] = emp["salary"].rank(pct=True).round(2)

finance = emp[emp["department"] == "Finance"].sort_values("dept_rank")
print(finance[["name", "salary", "dept_rank", "gap_to_top", "salary_pct"]].head(2).to_string(index=False))

       name    salary  dept_rank  gap_to_top  salary_pct
  Aarav Rao 1364000.0        1.0         0.0        0.90
Farhan Nair 1042000.0        2.0    322000.0        0.75