Model / timeseries · All operations

Resample a time series

Resample, decompose, difference, smooth, fill time gaps.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result

a new grain: each row stands for a group of input rows

Level

several clauses of SQL, or a window

The DuckDB step

Daily buckets with the gaps filled

It reads the orders table the proofs share. Create it first:

SQL

CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL

CREATE TEMP TABLE result AS
WITH spine AS (SELECT d::DATE AS day FROM generate_series(DATE '2026-01-03', DATE '2026-01-12', INTERVAL 1 DAY) t(d)),
     daily AS (SELECT order_date AS day, sum(amount) AS total FROM orders GROUP BY 1)
SELECT day, coalesce(total, 0) AS total,
       round(avg(coalesce(total, 0)) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS ma3
FROM spine LEFT JOIN daily USING (day) ORDER BY day;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT count(*) = 10 AND max(total) FILTER (WHERE day = DATE '2026-01-05') = 380.5 AND max(total) FILTER (WHERE day = DATE '2026-01-04') = 0 AND max(ma3) FILTER (WHERE day = DATE '2026-01-05') = 166.83 FROM result)) THEN 'as expected' ELSE error('a day with no orders was not filled with 0, or a total is wrong') END AS verdict;

It needs nothing installed beyond DuckDB itself.
The statement checks its own result and raises an error when it is wrong: “a day with no orders was not filled with 0, or a total is wrong”.
It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Decompose a series into trend and season
Split a series into trend, seasonal and remainder parts.
Fill gaps in a time series
Insert rows for missing periods, or interpolate onto evenly spaced time stamps, so the series runs at a regular frequency.
Measure autocorrelation or a spectrum
Compute how a series relates to itself across lags, its frequency spectrum, or its load-cycle counts.

Aggregate into regular time windows

Group rows into fixed, hopping or rolling time windows and aggregate each, changing the series' grain.

Difference, accumulate or smooth a series

Replace each value with its change from an earlier one, a running total, or a smoothed value, keeping one row per period.

Find peaks and summarise a series

Flag or list the peaks of a series, or fit it with a curve and keep the coefficients as features.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (2)

AB Trendread 2026-09-27
Compute trend and seasonality measures per unit, for matching controls to treatments
TS Fillerread 2026-09-27
Fill gaps in a time series by inserting rows for the missing periods

Ibis (1)

WindowAggregateread 2026-09-27
Aggregate by tumbling or hopping time windows, adding window start and end columns

Polars (2)

DataFrame.group_by_dynamicread 2026-09-27
Group rows into time or index windows set by every, period and offset; a row may fall in several
DataFrame.upsampleread 2026-09-27
Insert rows so that a time column runs at a regular frequency

RapidMiner Studio (14)

Autocorrelation / Autocovarianceread 2026-09-27
Compute autocorrelation, partial autocorrelation or autocovariance of series columns by lag
Classic Decompositionread 2026-09-27
Decompose a series into trend, seasonal and remainder by classical decomposition
Differentiateread 2026-09-27
Difference series columns (subtract the previous value) by a chosen lag
Equalize Numerical Indicesread 2026-09-27
Resample a series with numeric index onto equidistant index values, interpolating
Equalize Time Stampsread 2026-09-27
Resample a series with date-time index onto equidistant time stamps, interpolating
Exponential Smoothingread 2026-09-27
Smooth series columns by exponential smoothing
Extract Coefficients (Polynomial Fit)read 2026-09-27
Fit a polynomial to each series and output the coefficients and fit error as features
Extract Peaksread 2026-09-27
Detect peaks in a series and output each peak's value, position, width and amplitude
Fast Fourier Transformationread 2026-09-27
Compute the fast Fourier transform (amplitude and phase spectrum) of series columns
Highest Peak Transformationread 2026-09-27
Flag the n highest or lowest peaks of a series as an indicator series
Integrateread 2026-09-27
Cumulatively sum (integrate) series columns
Rainflow Matrixread 2026-09-27
Compute the rainflow-counting matrix of load cycles for a discretised series column
STL Decompositionread 2026-09-27
Decompose a series into trend, seasonal and remainder by STL (Loess)
Z-Score Peak Transformationread 2026-09-27
Flag peaks of a series by z-score against a moving window as an indicator series

Tableau Prep (1)

New Rowsread 2026-09-27
Generate the missing rows of a numeric or date sequence, filled with null, zero or previous value

Background

Resampling changes the sampling rate of a discrete signal to obtain a new discrete representation of the underlying continuous signal.^[1] Upsampling approximates the sequence that sampling at a higher rate would have given, and downsampling the sequence a lower rate would have given.^[2]^[3] In pandas, resampling is a time-based groupby followed by a reduction on each group, as when secondly data is converted into 5-minutely data.^[4]

When to use it

In pandas, resampling serves frequency conversion, a task that is extremely common in, but not limited to, financial applications.^[4] Reach for it when two series must meet at one grain, such as daily orders against monthly targets, or when a reader needs the shape of a series rather than every reading. (Meridian's view)

Decimating a signal by an integer factor M keeps only every Mth sample.^[3] Decimating without first reducing high-frequency components with a lowpass filter creates aliasing, in which high-frequency components are mistaken for lower frequencies.^[3] When a fine series is thinned to a coarser grain, summarise all the readings in each window rather than keeping one reading per window, so that fast swings are less likely to be read as slow ones. (Meridian's view)

In pandas, naively upsampling a sparse series can generate lots of intermediate values, which are left missing when no fill method is given.^[4] Before moving a series to a finer grain, work out how many rows the new grain will create, and fill only the rows a reader needs. (Meridian's view)

The method

In pandas, resample needs a datetime-like index, or the name of a datetime-like column passed with the on keyword.^[5] In pandas, asfreq changes a series' frequency by generating a date range and reindexing the series to it, and can be given an interpolation method for the gaps that appear.^[4]

Upsampling by an integer factor L first inserts L−1 zeros between the original samples, then smooths out the discontinuities with a lowpass filter called an interpolation filter.^[2]

A moving average of order m estimates the trend-cycle, the smooth underlying movement of a series, at time t by averaging the values within k periods of t, where m = 2k + 1.^[6] In a moving average, a larger order generally means a smoother curve.^[6] A moving average of order 5 over yearly data has no values for the first two or the last two years, because those years lack two observations on either side.^[6] Leave the missing ends of a moving average missing rather than padding them, so that nobody reads an edge value as a smoothed one. (Meridian's view) In RapidMiner's Exponential Smoothing operator, larger values of the parameter alpha smooth less, and alpha = 1 leaves the series not smoothed at all.^[7]

Sources

[1]Sample-rate conversion - Wikipediasecondary source
[2]Upsampling - Wikipediasecondary source
[3]Downsampling (signal processing) - Wikipediasecondary source
[4]Time series / date functionality — pandas 3.1.1 documentationdocumentation
[5]pandas.DataFrame.resample — pandas 3.1.1 documentationdocumentation
[6]3 Time series decomposition – Forecasting: Principles and Practicesecondary source
[7]Exponential Smoothing - Rapidminer Documentationdocumentation