PYTHON / PANDAS
Time series indexing and resampling
Turn a date column into a DatetimeIndex, slice it with partial date strings, and aggregate to any frequency with resample.
What you will learn
- Parse dates and set them as a sorted DatetimeIndex before any time slicing
- Select periods with partial strings like .loc['2024-02'] instead of comparisons
- Downsample with resample(freq).agg and read the label as a bin edge, not a data point
- Recognise that empty bins become NaN and upsampling needs ffill or asfreq
Understanding Time series indexing and resampling
A DatetimeIndex is not a column of strings that happen to look like dates: internally it is int64 nanoseconds since 1970, and each label is a Timestamp. Because pandas understands the calendar structure of those labels, the string '2024-02' is not treated as a key to match but as a range to expand, from 2024-02-01 00:00:00 to the last nanosecond of February. That is why partial string indexing only works on a monotonic index, and why the same string can select five rows, five thousand rows, or none at all.
resample is a groupby whose groups are calendar intervals rather than column values. You give it a target frequency, pandas builds a complete grid of bins spanning the first and last timestamp, assigns each row to exactly one bin, and then waits for an aggregation such as sum, mean, or last. Because the grid is complete rather than derived from the rows present, a day with no observations still produces a bin, and aggregating an empty bin yields NaN. Going the other way, to a finer frequency, invents labels that had no data at all, so upsampling is followed by asfreq, ffill, or interpolate.
The timestamp on a resampled row is a bin label, and which edge it names depends on the frequency. Tick-like frequencies such as D, h, and 12h default to closed='left', label='left', so the label is the first instant of the interval, while calendar frequencies such as W, ME, and QE default to closed='right', label='right', so a weekly total is stamped with the Sunday that closes the week. Confusing this with a rolling window is the usual source of surprise: rolling keeps one output row per input row, whereas resample collapses the index onto a new regular grid.
import pandas as pd
idx = pd.date_range("2024-01-29", periods=8, freq="D")
visits = pd.Series([10, 12, 9, 15, 11, 14, 13, 16], index=idx, name="visits")
print(visits.loc["2024-02"])
print()
print(visits.resample("W").sum())A DatetimeIndex lets pandas interpret dates as ranges, so slicing takes partial date strings and resample groups rows into a complete grid of calendar bins.
Worked examples
Irregular timestamps and empty bins
Shows that partial string indexing ignores the time part and that resample fills in a day with no readings.
import pandas as pd
s = pd.Series(
[4.0, 9.0, 6.0],
index=pd.to_datetime(["2024-03-01 09:00", "2024-03-01 15:00", "2024-03-03 11:00"]),
name="temp",
)
print(s.loc["2024-03-01"])
print()
print(s.resample("D").mean())Example explained
Line 1pd.to_datetime turns the strings into Timestamps, so the index carries no freq and no Freq line is printed.
Line 2.loc['2024-03-01'] expands to the whole day and keeps both 09:00 and 15:00 rows with their original times.
Line 3resample('D').mean() averages 4.0 and 9.0 into 6.5 for the first day.
Line 42024-03-02 appears with NaN because the bin grid is continuous even though no reading exists for that day.
Downsampling and upsampling the same series
Contrasts collapsing four daily values into two-day totals with expanding them onto a 12-hour grid.
import pandas as pd
s = pd.Series([1, 2, 3, 4], index=pd.date_range("2024-05-01", periods=4, freq="D"))
print(s.resample("2D").sum())
print()
print(s.resample("12h").asfreq())Example explained
Line 1resample('2D') pairs 05-01 with 05-02 and 05-03 with 05-04, labelling each total with the left edge.
Line 2The labels 05-01 and 05-03 are interval starts, so the value 3 belongs to a two-day span, not to that single date.
Line 3resample('12h').asfreq() creates the noon labels but has nothing to put in them, hence NaN.
Line 4Introducing NaN forces the int64 series to float64; ffill() instead of asfreq() would carry the previous value forward and keep the values whole.
Moving the bin boundary
Shows how closed and label change which rows land in a bin and which edge names it.
import pandas as pd
s = pd.Series(range(6), index=pd.date_range("2024-07-01", periods=6, freq="D"))
print(s.resample("3D", closed="right", label="right").sum())Example explained
Line 1closed='right' makes each bin exclude its left edge and include its right one, so the intervals are (06-28, 07-01], (07-01, 07-04] and (07-04, 07-07].
Line 2That leaves 07-01 alone in the first bin with a sum of 0, instead of grouping 07-01 through 07-03 as the default would.
Line 3label='right' stamps each total with the closing edge, which is why 2024-07-07 appears even though the data stops on 07-06.
Line 4The default for a tick frequency like 3D is closed='left', label='left'; only calendar frequencies such as W and ME default to right.
Important notes
pandas 2.2 renamed several aliases: use ME, YE, QE, h, min and s instead of M, Y, Q, H, T and S, which now emit FutureWarning.
If the timestamps are timezone aware, daily bins follow local calendar days, so a DST transition produces a 23- or 25-hour bin; resampling in UTC avoids that.
Common mistakes
Leaving dates as object dtype and calling resample, which raises TypeError: Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, because pandas has no calendar meaning for plain strings.
Slicing an unsorted DatetimeIndex, which raises KeyError about a non-monotonic index instead of silently returning a partial result.
Reading a resample('W') label as the start of the week; the default label is the closing Sunday, so a weekly total shows up under a date one or six days after the rows it summarises.
Calling resample('D') without an aggregation and then indexing the Resampler object, which yields attribute errors because no values have been computed yet.
Try it yourself
Change, predict, then run
Build a Series of 48 hourly values with pd.date_range('2024-06-01', periods=48, freq='h'), then print the maximum per 6-hour block and the daily mean, and confirm the 6-hour labels name the start of each block.
Open the Python workspaceCheck your understanding
A Series of temperature readings has rows on 2024-03-01 and 2024-03-03 but none on 2024-03-02. After resample('D').mean(), why does 2024-03-02 appear with NaN?
- resample builds a continuous grid of daily bins between the first and last timestamp, and aggregating a bin with no rows gives NaN
- The original data contained a NaN reading on that date which mean() propagated
- mean() cannot be computed for a single day and needs at least two rows per bin
- The DatetimeIndex was not sorted, so pandas inserted a placeholder row
Show answer
The bin grid comes from the frequency and the index range, not from the dates that happen to be present, so gaps in the data become empty bins that aggregate to NaN. The second option is tempting because NaN usually signals missing values in a column, but here nothing was missing in the input: the label 2024-03-02 did not exist at all until resample created it.