Filling Gaps in a CSV Time Series With SQL and DuckDB

A time series with missing periods lies in quiet ways. A weekly average over seven recorded days is not a weekly average if the eighth day was lost. A chart connects the points on either side of a gap and shows a trend that was never measured. A join between two series drops the days only one of them has. The remedy is to make the gaps visible first and then decide, explicitly, what each one means. This article finds the gaps in a daily CSV, builds a calendar to expose them and shows four ways of filling.

The data and the gap

day,visits
2025-03-01,120
2025-03-02,135
2025-03-03,150
2025-03-05,90
2025-03-06,140
2025-03-07,160
2025-03-08,180

Seven rows for a range that should hold eight days. The total is 975.

Step 1: detect gaps

You do not need a calendar to find them. Compare each date with the next:

SELECT day, lead(day) OVER (ORDER BY day) AS next_day,
       date_diff('day', day, lead(day) OVER (ORDER BY day)) AS step
FROM read_csv('daily.csv')
QUALIFY step > 1;

One row: 2025-03-03, next day 2025-03-05, step 2. A step of 2 means one missing day; larger means more. Run this before anything else, because the number of gaps decides the approach (one missing day may be a failed export; thirty may be a feed that only reports on weekdays). Also look at the start and end of the series: the first and last dates should be the period you intended.

Step 2: build a calendar

To get rows for the missing days, generate every date in the range and join:

WITH cal AS (
  SELECT d::DATE AS day FROM range(DATE '2025-03-01', DATE '2025-03-09', INTERVAL 1 DAY) t(d)
)
SELECT cal.day, v.visits
FROM cal LEFT JOIN read_csv('daily.csv') v USING (day)
ORDER BY cal.day;

The range end is exclusive, so 2025-03-09 produces days up to the 8th: eight rows. The left join keeps every calendar day, and 4 March has visits NULL. Derive the bounds from the data instead of typing them: SELECT min(day), max(day) over the file, then range(min, max + INTERVAL 1 DAY, INTERVAL 1 DAY). A calendar built this way never has gaps by construction, so it is also the right spine for any join of several series.

Step 3: choose a fill

What a missing day means decides how to fill it. Four options, with the eight-day series.

Leave it NULL. The honest default. Aggregates skip it: the sum is still 975 and avg is 975 / 7 = 139.29. Use this when the day is truly unknown and the consumer can cope with NULL.

Zero. Right when no row means no events: nobody visited, no orders were placed. coalesce(v.visits, 0) gives 4 March = 0, the sum stays 975, and the average over eight days is 121.88. Wrong when the data was not collected: a failed export is not a day with no visitors.

Carry the last value forward. Right for levels that persist: a stock level, a balance, a price, a configuration. The last known value is 150 (3 March), so 4 March = 150, and the total becomes 1,125 (but you should not sum a level across days at all). In DuckDB:

SELECT day,
       coalesce(visits, last_value(visits IGNORE NULLS) OVER (ORDER BY day)) AS visits_filled
FROM (SELECT cal.day, v.visits FROM cal LEFT JOIN read_csv('daily.csv') v USING (day));

(IGNORE NULLS support in last_value depends on your engine and version; PostgreSQL lacks it, where you can use a grouping trick: count the non-NULL values cumulatively and take the max within each group.)

Interpolate. Right for smooth physical measurements (temperature, a sensor). The value on 4 March between 150 on the 3rd and 90 on the 5th is the midpoint, 120, and the total becomes 1,095. The formula for one missing step is the mean of the neighbors; for longer runs, linear steps between the known endpoints. It invents information, so use it for display or for models that need a complete grid, not for reported totals.

Mark what you filled

Keep the original value and add a flag:

SELECT cal.day, v.visits, v.visits IS NULL AS filled,
       coalesce(v.visits, 0) AS visits_zero_filled
FROM cal LEFT JOIN read_csv('daily.csv') v USING (day);

A reader of the filled table can then see which rows are measurements and which are assumptions, and a total can be computed both ways. In the report, state the rule ("one missing day, 4 March, filled with 0"). This is the same principle as in cleaning: never replace data silently.

Several series at once

When there are multiple groups (visits per region), fill each group separately: cross-join the calendar with the list of groups, then left join the data. Otherwise a region with no data on a day will not get a row:

SELECT g.region, cal.day, coalesce(v.visits, 0) AS visits
FROM cal CROSS JOIN (SELECT DISTINCT region FROM read_csv('visits_by_region.csv')) g
LEFT JOIN read_csv('visits_by_region.csv') v ON v.day = cal.day AND v.region = g.region;

That is a dense grid: regions × days rows, which can be large; for sparse data it may be better to leave the gaps and handle them in the consumer.

In pandas

s = pd.read_csv("daily.csv", parse_dates=["day"], index_col="day")["visits"]
full = s.reindex(pd.date_range("2025-03-01", "2025-03-08"))
full.fillna(0)            # zero
full.ffill()              # carry forward
full.interpolate()        # linear

reindex against a full date_range is the calendar join. The three fill methods match the three SQL options. Note interpolate() on integer data returns floats, so 120.0.

Gaps at the edges and duplicates in the middle

Two neighbors of the gap problem deserve a check. Gaps at the edges do not show up as a step between rows: a series that should start on 1 March but whose first row is 3 March has no internal gap and is still short. Compare min(day) and max(day) with the period you expect, and build the calendar from the expected bounds and not from the data. Duplicates in the middle (two rows for the same day) make a left join produce two rows for that day, so the calendar no longer maps one to one; check count(*) = count(DISTINCT day) first, and decide whether the duplicates are repeats to drop or parts to sum (duplicate keys).

A related trap is the series with regular but intended gaps: a market that closes at weekends, a report that runs on business days. A calendar of all days would invent two missing days every week. Use a business-day calendar (filter dayofweek(day) BETWEEN 1 AND 5, or a table of trading days) so that only unexpected absences count as gaps. The count of gaps you find should be zero or explainable. If it is neither, stop and ask the source.

Reporting a filled series

When a filled series goes into a report, say how many points were filled and with what: "eight daily values, one filled with zero (4 March, export failure)". Show filled points in a different style on charts. If you fill with an interpolation, label the series as estimated. And keep the unfilled data available, so that someone who wants to apply a different rule can.

How to tell it worked

Count the rows: eight for eight days. Count the NULLs before filling and the number of filled rows after: they must match. Compare the total before and after, and be able to explain the difference (zero fill: none; carry forward: +150; interpolation: +120). Plot the result with the filled points marked.

When a gap means something

A missing day can be the finding: an outage, a holiday, a feed that failed. Filling it hides the event. Before you fill, ask whether the gap is a data problem or a fact, and if the cause is known (a bank holiday), add the reason as a column. If the gaps are many and irregular, the data is not a regular time series, and resampling to a grid may be the wrong frame: model it as events instead. For the next steps with a complete series, see moving averages and detecting drops.

Further reading: DuckDB range functions; pandas missing data guide.