Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Calculate Moving Averages with pandas rolling

Smooth daily sales with a 7-day moving average, per store, the way an Excel AVERAGE over a sliding range does. Learn why rolling(7) and rolling("7D") differ when days are missing, what min_periods does to the first rows, trailing versus centred windows, and how to roll within groups without scrambling rows.
Pivots & summariesDates & timespandas

Daily figures are noisy: a Saturday dip, a promotion spike, a delivery that landed on Monday instead of Friday. A moving average smooths them into a trend a reader can see. In Excel it is an AVERAGE over a range that slides down with the row, or a chart trendline; in pandas it is rolling(...).mean(). The call is short, but it hides three choices that change the numbers — whether the window counts rows or days, what happens before the window is full, and how the window is anchored — plus one trap when rolling within groups. This page, part of Creating Pivot Tables from Excel Data with Pandas, covers each.

North daily sales and their 7-day moving average A line chart of North's daily sales from 1 to 14 March, with dips on weekends such as 90 and 85 on the 6th and 7th, alongside a 7-day trailing moving average. The first six points average the days available so far; from the 7th the full window gives 122.6, rising steadily to 130.4 by the 14th. daily sales 7-day average 0 50 100 150 200 1 2 3 4 5 6 7 8 9 10 11 12 13 14

Prerequisites

Bash
pip install pandas openpyxl

daily_sales.xlsx has two weeks of sales for two stores. North has a row for every day; South only has rows on six of the fourteen days, the typical shape of an export that records only days with activity.

Step 1 — A trailing 7-day average on complete data

For a series with one row per day, rolling(7).mean() averages each row with the six before it:

Python
import pandas as pd

sales = pd.read_excel("daily_sales.xlsx").sort_values(["store", "date"])
north = sales[sales["store"] == "North"].set_index("date")["sales"]
ma = north.rolling(7).mean()
print(pd.DataFrame({"sales": north, "ma7": ma.round(1)}))

The first six values are NaN, because a seven-row window is not full until the seventh day; from 7 March onwards each value is the average of that day and the six before, the same as =AVERAGE(B2:B8) in Excel dragged down the column. Sort by date first — rolling uses the rows in the order they are, and an unsorted export produces averages of unrelated days.

Step 2 — Decide what happens before the window is full

min_periods sets how many rows the window needs before it returns a value. With min_periods=1, the first rows average whatever is available:

Python
early = north.rolling(7, min_periods=1).mean()
print(early.head(7).round(1).tolist())

That removes the gap at the start of a chart, at the cost of early values based on fewer days — the first "7-day average" is a single day. If you show them, mark them: a lighter colour in the chart, or a window_days column that says how many days each value used. For a dashboard that must show the trend from day one, a sensible compromise is min_periods=4, which starts once more than half the window is filled.

Step 3 — Count days, not rows, when days are missing

South has no rows on the quiet days. rolling(7) still takes seven rows, which for South spans far more than a week; rolling("7D") on a date index takes the rows whose dates fall within the last seven days:

Python
south = sales[sales["store"] == "South"].set_index("date")["sales"]
compare = pd.DataFrame({
    "sales": south,
    "rows_7": south.rolling(7, min_periods=1).mean().round(1),
    "days_7D": south.rolling("7D").mean().round(1),
})
print(compare)

On 12 March, rows_7 averages every row back to 1 March — twelve days of data — while days_7D averages only the rows from 6 to 12 March. Neither is a true seven-day average of daily sales, though, because missing days are really zero-sales days. When a missing row means "nothing happened", fill the missing dates with zero first and then roll; when it means "no data", the time-based window over the rows that exist is the honest option. Fill Missing Dates in a Time Series from Excel with pandas shows the filling step.

What each window averages for South on 12 March A comparison of three ways to compute South's 7-day average on 12 March. rolling(7) averages the last seven rows, which reach back to 1 March. rolling("7D") averages the three rows dated 6 to 12 March. Filling missing dates with zero and then using rolling(7) averages seven days including four zero days. Rows used Period covered Treats missing days as rolling(7) all 6 rows 1–12 Mar ignored rolling("7D") 3 rows 6–12 Mar ignored fill 0, then rolling(7) 7 days 6–12 Mar zero sales

Step 4 — Trailing or centred

A trailing window, the default, averages each day with the days before it, so the average lags the data: a rise shows up in the average a few days late. center=True averages days on both sides, which lines up with the data and is better for analysing past trends:

Python
both = pd.DataFrame({"sales": north,
                     "trailing": north.rolling(7).mean().round(1),
                     "centred": north.rolling(7, center=True).mean().round(1)})
print(both.tail(5))

The centred average has no value for the last three days — it needs days that have not happened yet. That is why dashboards showing today use trailing averages, and why a centred chart line stops short of the right-hand edge. Excel's chart trendline "Moving Average" is a trailing average, which matters if you are matching a chart someone built by hand.

Step 5 — Roll within each group

With several stores in one table, each store needs its own window: a moving average that runs across the boundary from North's last day into South's first mixes the two. Roll within groups, and keep the result aligned with the original rows by using transform:

Python
sales = sales.sort_values(["store", "date"])
sales["ma7"] = (sales.groupby("store")["sales"]
                     .transform(lambda s: s.rolling(7, min_periods=4).mean())
                     .round(1))
print(sales.groupby("store").tail(3).to_string(index=False))

transform returns a Series indexed like sales, so it can be assigned straight back as a column. The more obvious sales.groupby("store")["sales"].rolling(7).mean() returns a result indexed by store and original row number, which cannot be assigned back without resetting the index — a common source of "cannot reindex" errors and of values landing on the wrong rows. For time-based windows per group, set the date as the index inside the lambda: lambda s: s.set_axis(dates.loc[s.index]).rolling("7D").mean().values, or roll each store separately.

Moving averages computed separately for each store The sales table sorted by store and date with a ma7 column. Each store's moving average uses only its own rows: North's values continue its own series, and South's first values are empty until four South rows exist, instead of borrowing North's last days. groupby('store').transform(rolling mean) A B C D 1 date store sales ma7 2 Mar 12 North 170 129.3 3 Mar 13 North 95 130 4 Mar 14 North 88 130.4 5 Mar 01 South 60 6 Mar 02 South 64 7 Mar 05 South 70 8 Mar 06 South 75 67.2

Exponential averages react faster

A simple moving average gives every day in the window the same weight, so a spike stays in the average for exactly seven days and then drops out abruptly. An exponentially weighted average gives recent days more weight and older days progressively less, which reacts faster to real changes and has no cliff edge:

Python
smooth = pd.DataFrame({"sales": north,
                       "sma7": north.rolling(7).mean().round(1),
                       "ewm7": north.ewm(span=7, adjust=False).mean().round(1)})
print(smooth.tail(4))

span=7 gives a weighting comparable to a seven-day window. The exponential version follows the weekend dip on 13 and 14 March more closely than the simple average, which is still dominated by the strong midweek days. Use it for alerting and short-term monitoring; keep the simple average where readers need to reproduce the figure with AVERAGE in Excel.

Step 6 — Write the average next to the data

Write the moving average as a column beside the daily figures, so a chart in the workbook can plot both:

Python
out = sales[["date", "store", "sales", "ma7"]]
with pd.ExcelWriter("sales_ma.xlsx", engine="xlsxwriter", datetime_format="yyyy-mm-dd") as writer:
    out.to_excel(writer, index=False, sheet_name="Daily")
print(pd.read_excel("sales_ma.xlsx")["ma7"].notna().sum(), "rows with a moving average")

If readers will add days themselves, a formula column keeps working where a static number would not: =IF(COUNT(C2:C8)=7, AVERAGE(C2:C8), "") in row 8, filled down within each store's block. Add a Line Chart to an Excel Report with Python builds the chart.

Common pitfalls

  • Rolling unsorted data. Sort by date (and group) first.
  • Row windows over gappy data. rolling(7) on six sparse rows spans weeks; use "7D" or fill the dates.
  • Rolling across groups. Use groupby().transform so each store has its own window.
  • Assigning groupby().rolling() directly. Its MultiIndex does not line up with the frame.
  • Early values from min_periods=1. They are one-day "averages"; mark them or start later.

Performance notes

rolling().mean() is implemented in compiled code and handles millions of rows quickly. The transform with a lambda calls Python once per group, which is fine for hundreds of groups; for tens of thousands, groupby().rolling() followed by reset_index(level=0, drop=True) avoids the per-group Python call and aligns the result by the original row index.

Conclusion

Sort by date, then rolling(7).mean() gives a trailing seven-row average — correct when there is one row per day. Use rolling("7D") or fill missing dates first when days are absent, set min_periods deliberately and mark partial windows, choose trailing for dashboards and centred for analysis, and roll within groups with groupby().transform so every store gets its own window and the result lines up with its rows.

Frequently asked questions

What is the difference between rolling(7) and rolling("7D")? rolling(7) averages the last seven rows, whatever dates they cover. rolling("7D") averages the rows whose dates fall in the last seven days. With one row per day they agree; when days are missing, rolling(7) stretches over a longer period and rolling("7D") averages fewer rows.

Why are the first six values NaN? A seven-row window is not full until the seventh row, and by default rolling returns NaN until it is. Pass min_periods=1 (or another minimum) to average whatever is available, and label those early values as partial.

How do I calculate a moving average per store? Sort by store and date, group by store, and apply the rolling mean inside transform with a lambda, as shown in Step 5. transform keeps the result aligned with the original rows, so it can be assigned straight back as a column.

Is a centred moving average better? It lines up better with the data for analysis of past trends, because each point averages days on both sides. It cannot be computed for the most recent days, so dashboards that must show today usually use a trailing average.

More on pivots & summaries

More on pivots & summaries →