Moving Averages: Smoothing a Series Without Smoothing Away the Truth

1. What a moving average is doing to your numbers

Before the explanation: look at weeks three, four and five in that table: 660, 240, 660. Decide whether the 240 tells you the business dropped, before reading on.

It does not, and that is the problem a moving average solves. Any single period contains two things mixed together: whatever is genuinely going on, and the accident of which orders happened to land in that week. Week four's 240 is one small lamp order. It is not a collapse in demand, it is a Tuesday.

A moving average separates them by averaging over a window of consecutive periods. Random ups and downs partly cancel, because the high accidents and the low accidents sit in the same window. Anything sustained does not cancel, because it is present in every period of the window. What comes out is the sustained part, with the accidents damped.

The damping is measurable. Our raw weekly numbers have a standard deviation of 236.81. The three-week average has 100.56, and the five-week average has 61.95. The wider the window, the more cancelling happens, and section three is about what that costs.

2. Building a three-week average by hand

Before the arithmetic: you want a three-week average for the week beginning 19 January. Say which three weeks go into it, before reading on. There are two defensible answers and the difference matters.

The common answer is a trailing window: this week and the two before it. That is 660, 850 and 880, which average to (660 + 850 + 880) ÷ 3 = 796.67. Then you slide the window forward one week at a time, dropping the oldest and picking up the newest.

Two things to read off that table. The first two weeks have no value at all, because a three-week window needs three weeks, and every moving average starts late by window length minus one. And the 1,100 spike in the week of 9 March never appears in the smooth series; the closest it gets is lifting three consecutive averages to 620, 606.67 and 768.33.

# pandas
s.rolling(3).mean()              # trailing, needs 3 values
s.rolling(3, min_periods=1).mean()   # starts immediately, first values on fewer rows

-- SQL: a window frame is exactly a moving average
SELECT OrderDate,
       Revenue,
       AVG(Revenue) OVER (ORDER BY OrderDate
                          ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma3
FROM Orders
ORDER BY OrderDate;

' Excel, with revenue in B2:B17 and the formula from B4's row
=IF(ROW()-ROW($B$2)<2, "", AVERAGE(B2:B4))

The SQL version is worth a second look, because ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is the definition written out in words. If window frames are new, window functions covers the whole family, and running totals is the same machinery with a different frame.

3. Choosing the window, and what each length costs

Before the trade: a longer window smooths more. Name what you give up for that, before reading on.

You give up responsiveness and you give up data at the start. Here are the three numbers that make the trade concrete on our series.

The five-week version is beautifully smooth and it has thrown away four of your sixteen weeks and compressed a real 860-wide range into 186. If something genuinely changed in the last fortnight, a five-week average will show you about two fifths of it.

The rule that beats taste: match the window to the cycle you want removed.

• Daily data with a weekday pattern: use 7. A seven-day average always contains exactly one of each weekday, so the weekday effect cancels completely rather than partly.
• Weekly data: 4 for a month-ish view, 13 for a quarter.
• Monthly data with a seasonal pattern: use 12, for the same reason as 7 on daily data.
• No cycle, just noise: pick the shortest window that makes the chart readable, and say what it is.

Say out loud why a 6-day average on daily data is worse than a 7-day one, even though it is nearly as smooth. A six-day window contains two of one weekday and none of another, and which ones changes as the window slides. The averaged line then carries a wobble the raw data does not have, which is the worst possible outcome from a smoothing step.

Now picture your own busiest chart. What cycle is in it, and does the window you use, if you use one, actually match that cycle?

4. Trailing against centred, and the lag

Before the comparison: our trailing average puts the value 796.67 on the week of 19 January, but it was computed from 5, 12 and 19 January. Say where that number really belongs on the timeline.

In the middle, on 12 January. A trailing average is systematically late, by half the window, because it labels a window with its right-hand edge. That is the lag, and it is not an error, it is the direct cost of using only information you would actually have had at the time.

The centred column is the same numbers shifted one row up. Nothing was recomputed, only relabelled, and that relabelling is what removes the lag.

Which one to use comes down to one question: does the value need to be honest about what you knew at the time?

Use trailing for anything operational or forward-looking. A dashboard, an alert, a forecast input. A centred average uses future data, so a centred line on a live dashboard is claiming knowledge you do not have, and it will change under you as new data arrives.

Use centred for historical description, where all the data already exists and you want the trend line to sit correctly on the timeline. Seasonal decomposition uses centred averages for exactly this reason.

Centred windows also want an odd length, so there is a genuine middle period. With an even window you end up averaging two adjacent centred averages, which is a real technique and a fiddly one, and is the main practical reason to prefer 3, 5, 7 and 13 over 4, 6 and 12.

5. The missing weeks that corrupt it silently

Before the trap: our series jumps from 23 March straight to 4 May. Five weeks with no orders. Say what rolling(3) will do with that, before reading on.

It will average across the hole and tell you nothing happened. Look at what the trailing three-week average produces for the week of 4 May if the gap is not represented in the data.

Week of 2026-05-04, 3-week trailing average = 601.67
  built from: 2026-03-16   440
              2026-03-23   765
              2026-05-04   600

Those three "consecutive" weeks span nine weeks of calendar. The number 601.67 looks entirely ordinary sitting in a column, and it is an average of March and May with five weeks of nothing quietly deleted. Nothing errors, nothing warns, and the smooth line glides straight through a two-month hole.

The reason is worth stating plainly: rolling in pandas and ROWS BETWEEN in SQL both count rows, not time. If a period has no rows, it has no row, so it is not in your table, so it cannot be counted. Grouping never invents the periods it did not see.

The fix is the same one every time-based calculation needs. Build the full set of periods, join your data onto it, and decide what the empty ones mean.

# reindex onto every Monday in the range
full = s.asfreq('W-MON')          # missing weeks appear as NaN
full.rolling(3).mean()            # now NaN through the gap: correct and visible
full.fillna(0).rolling(3).mean()  # gap treated as zero sales

Three defensible answers and one indefensible one. Treating the gap as zero says the business made no sales, which is right if the shop was shut and wrong if the data simply did not arrive. Leaving it as no data says you cannot compute an average there, which is right if you do not know. Ignoring the calendar entirely, the first column, says the five weeks never existed, and that is the one nobody chose on purpose.

Deciding which of the first two applies is a data question, not a statistics question, and it is the same decision as in handling missing values. What matters is that you make it rather than let rolling make it for you.

6. Weighted and exponential versions

Before the idea: a plain three-week average treats last week and the week before last as equally informative. Say whether you believe that, before reading on.

Usually not. Recent periods are normally more relevant, and there are two standard ways to say so.

A weighted moving average assigns explicit weights that add to one. Weights of 0.5, 0.3 and 0.2 on the most recent three weeks give, for the week of 23 March: 0.5 × 765 + 0.3 × 440 + 0.2 × 1,100 = 382.5 + 132 + 220 = 734.50, against the plain average's 768.33. It is transparent, and it needs you to invent the weights.

An exponentially weighted average avoids inventing them. It uses one number, usually called alpha, and blends each new value into the running average by that fraction.

new average = alpha × this week + (1 − alpha) × last week's average

With alpha = 0.3 on our series, the first value is 880, then 0.3 × 850 + 0.7 × 880 = 871.00, then 0.3 × 660 + 0.7 × 871 = 807.70, and so on. Every past week still contributes, and its influence shrinks the further back it is. It never fully forgets and it never gives an old week much say.

s.ewm(alpha=0.3, adjust=False).mean()
# 880.00, 871.00, 807.70, 637.39, 644.17, 654.92, ...

Two practical advantages over a plain window. There is no start-up gap, because the first value is simply the first observation. And there is no window length to defend, only alpha, where higher means more responsive and lower means smoother. Alpha around 0.2 to 0.3 is a reasonable starting point for weekly business data.

One caution that applies to all three. Exponential smoothing counts every past value, so a spike never leaves the series entirely, it only fades. On a plain three-week window the 1,100 spike is gone after three weeks, cleanly. Which behaviour you want is a real choice, not a detail.

7. What a moving average must not be used for

Before the warning: a smooth line is very persuasive. Name something a moving average cannot tell you, before reading on.

It is not a forecast. A trailing average tells you where the recent past sat, and extending it flat into the future is a forecast method, but a weak one that assumes no trend and no seasonality. It is worth computing precisely because it is the baseline anything fancier has to beat, and forecast accuracy is how you check whether it did.

It does not make a trend real. A moving average will produce a smooth, convincing rising line from data that is pure noise, because averaging adjacent random numbers creates apparent runs. Three rising points on a smoothed line is not evidence of anything.

It cannot detect a change point in the last few periods. By construction the most recent value is diluted by older ones, so a genuine shift last week shows up as a small nudge. If detecting recent change is the job, look at the raw series with a control limit, not at the smooth line.

It should not replace the raw data on a chart. Plot both. A reader shown only the smooth line has no way to judge how much was smoothed away, and the difference between a wobble of 237 and one of 62 is information they need.

Say in one sentence why the smooth line alone is the version most likely to mislead. It is because smoothness reads as certainty, and the smoothing step is exactly the step that removed the evidence of uncertainty. That is the same argument as in how charts mislead, applied to a technique rather than an axis.

Edge cases that catch people out

Six that each cost somebody an afternoon.

min_periods quietly changes the meaning. rolling(3, min_periods=1) gives you a value from the very first row, but that value is a one-row average sitting in a column labelled three-week. Convenient for charts, misleading in a table.

Duplicate rows in the same period. If two orders share a date and you have not aggregated first, a row-based window is averaging orders rather than days, and the window length no longer means what its name says. Aggregate to one row per period before smoothing, always.

Sorting. A window function with no ORDER BY, or a DataFrame not sorted by date, produces a moving average of whatever order the rows happened to be in. It will not error and it will look plausible.

Averaging a rate. A moving average of a conversion rate weights every period equally regardless of traffic. If you want a smoothed rate, smooth the numerator and denominator separately and divide at the end.

The last point moves. Yesterday's three-day average changes when today's data arrives if the window is centred, and does not if it is trailing. A dashboard whose historical numbers change overnight is usually a centred average that should have been trailing.

Comparing two series smoothed differently. A 7-day average and a 30-day average of the same data will cross each other constantly, and those crossings mean nothing about the business. If you are showing two windows, say what each one is for.