Skip to main content
Pandas · lesson 9 of 13

Beginner

5

Intermediate

6

Advanced

2
Open the Pandas page →
Pandas intermediate Lesson 9 of 13

Pandas Time Series

Parse dates, slice by date strings, resample, fill gaps in a time grid, and handle time zones and DST in Pandas, with every output from a real run.

By SkillByExample · Updated

Real-World Scenario

A sensor reports every hour, but some readings never arrive, the device logs in local time, and the dashboard wants daily totals. Handling that means parsing timestamps, slicing by date, changing frequency, filling gaps, and converting time zones.

Parsing Dates

pd.to_datetime turns strings into timestamps, and a DatetimeIndex exposes their parts.

import pandas as pd

df = pd.DataFrame({
    "timestamp": ["2024-01-15 08:00", "2024-01-16 09:30", "2024-02-29 23:00"],
    "temp_c": [22.1, 22.4, 19.0],
})
df["timestamp"] = pd.to_datetime(df["timestamp"])
df = df.set_index("timestamp")

print(df.index.dtype)
print(df.index.day_name().tolist())
print(df.index.month.tolist(), df.index.is_month_end.tolist())
print(len(pd.date_range("2024-01-01", "2024-12-31", freq="D")))
$ python parse.py
datetime64[us]
['Monday', 'Tuesday', 'Thursday']
[1, 1, 2] [False, False, True]
366

Once parsed, the index knows calendars: 29 February 2024 is correctly a Thursday and a month end, and a full daily range for 2024 has 366 entries because it is a leap year. The dtype’s [us] means microsecond resolution, the Pandas 3 default for parsed strings.

Durations: Timedelta

Subtracting timestamps gives a timedelta64 duration, not a number.

import pandas as pd

events = pd.DataFrame({
    "event": ["order_placed", "order_shipped", "order_delivered"],
    "timestamp": pd.to_datetime([
        "2024-03-01 09:00:00",
        "2024-03-02 14:30:00",
        "2024-03-05 11:15:00",
    ]),
})

events["time_since_previous"] = events["timestamp"].diff()
print(events)
print(events["time_since_previous"].dtype)

total = events["timestamp"].iloc[-1] - events["timestamp"].iloc[0]
print("\nTotal time to deliver:", total)
print("As hours:", total / pd.Timedelta(hours=1))
$ python timedeltas.py
             event           timestamp time_since_previous
0     order_placed 2024-03-01 09:00:00                 NaT
1    order_shipped 2024-03-02 14:30:00     1 days 05:30:00
2  order_delivered 2024-03-05 11:15:00     2 days 20:45:00
timedelta64[us]

Total time to deliver: 4 days 02:15:00
As hours: 98.25

.diff() gives each row’s gap from the previous one, so the first row has nothing to compare against and gets NaT. Dividing one Timedelta by another cancels the units and leaves a plain float, which is how you express a duration in hours.

Slicing by Date String

A DatetimeIndex lets you select rows with partial date strings — through .loc.

import pandas as pd

idx = pd.date_range("2024-01-01", periods=120, freq="D", name="date")
df = pd.DataFrame({"revenue": range(120)}, index=idx)

try:
    df["2024-02"]
except KeyError as e:
    print("KeyError:", e)

print(len(df.loc["2024-02"]), "rows in February")
print(df.loc["2024-03-30":"2024-04-02"])
$ python slicing.py
KeyError: '2024-02'
29 rows in February
            revenue
date               
2024-03-30       89
2024-03-31       90
2024-04-01       91
2024-04-02       92

On a DataFrame, df["..."] is column selection, so the string was looked up as a column name and failed. df.loc["2024-02"] matches every row in that month — 29 of them in 2024 — and a string slice includes both of its endpoints, so four days come back.

Resampling: Changing Frequency

resample buckets rows by time, much like a groupby on a period.

import pandas as pd

idx = pd.date_range("2024-01-01", periods=72, freq="h")
power = pd.Series([10] * 24 + [20] * 24 + [30] * 24, index=idx, name="kw")

print(power.resample("D").agg(["sum", "max", "count"]))

prices = pd.Series([100.0, 105.0], index=pd.date_range("2024-01-01", periods=2, freq="D"))
print(prices.resample("6h").ffill())
print(prices.resample("6h").asfreq())
$ python resample.py
            sum  max  count
2024-01-01  240   10     24
2024-01-02  480   20     24
2024-01-03  720   30     24
2024-01-01 00:00:00    100.0
2024-01-01 06:00:00    100.0
2024-01-01 12:00:00    100.0
2024-01-01 18:00:00    100.0
2024-01-02 00:00:00    105.0
Freq: 6h, dtype: float64
2024-01-01 00:00:00    100.0
2024-01-01 06:00:00      NaN
2024-01-01 12:00:00      NaN
2024-01-01 18:00:00      NaN
2024-01-02 00:00:00    105.0
Freq: 6h, dtype: float64

Downsampling hourly readings to "D" gives one row per day, with 24 readings in each bucket. Upsampling does the opposite: it creates new 6-hour slots, and you decide how to fill them. ffill carries the last price forward; asfreq leaves the new slots empty, which makes the invented rows visible.

Finding and Filling Gaps

Reindexing onto the complete time grid turns missing timestamps into visible NaN rows.

import pandas as pd

grid = pd.date_range("2024-01-01 00:00", periods=8, freq="h")
readings = pd.Series([1.0, 2.0, 5.0, 6.0, 8.0],
                     index=grid[[0, 1, 4, 5, 7]], name="reading")

full = readings.reindex(grid)
print("missing:", full.isna().sum())
print(pd.DataFrame({
    "raw": full,
    "ffill_1": full.ffill(limit=1),
    "interp": full.interpolate("time"),
}))
$ python gaps.py
missing: 3
                     raw  ffill_1  interp
2024-01-01 00:00:00  1.0      1.0     1.0
2024-01-01 01:00:00  2.0      2.0     2.0
2024-01-01 02:00:00  NaN      2.0     3.0
2024-01-01 03:00:00  NaN      NaN     4.0
2024-01-01 04:00:00  5.0      5.0     5.0
2024-01-01 05:00:00  6.0      6.0     6.0
2024-01-01 06:00:00  NaN      6.0     7.0
2024-01-01 07:00:00  8.0      8.0     8.0

Three hours never arrived. ffill(limit=1) fills only the first missing hour of each gap, so the 03:00 hole stays empty. interpolate("time") draws a straight line between known readings using the actual time spacing, so 02:00 and 03:00 become 3 and 4 on the way from 2 to 5.

Time Zones and Daylight Saving

tz_localize attaches a zone to naive times; tz_convert moves between zones.

import pandas as pd

naive = pd.DatetimeIndex(["2024-03-10 01:30", "2024-03-10 02:30", "2024-03-10 03:30"])

try:
    naive.tz_localize("America/New_York")
except Exception as e:
    print(type(e).__name__ + ":", e)

local = naive.tz_localize("America/New_York", nonexistent="shift_forward")
print(local)
print(local.tz_convert("UTC"))
$ python timezones.py
ValueError: 2024-03-10 02:30:00 is a nonexistent time due to daylight savings time. Try using the 'nonexistent' argument.
DatetimeIndex(['2024-03-10 01:30:00-05:00', '2024-03-10 03:00:00-04:00',
               '2024-03-10 03:30:00-04:00'],
              dtype='datetime64[us, America/New_York]', freq=None)
DatetimeIndex(['2024-03-10 06:30:00+00:00', '2024-03-10 07:00:00+00:00',
               '2024-03-10 07:30:00+00:00'],
              dtype='datetime64[us, UTC]', freq=None)

On 10 March 2024, clocks in New York jumped from 02:00 straight to 03:00, so 02:30 local time never existed and localizing it raises an error. nonexistent="shift_forward" moves it to the first valid instant, 03:00. In UTC the offsets stop changing, which is why storing timestamps in UTC and converting only for display is the usual advice.

Flagging Anomalies with a Rolling Baseline

A rolling mean and standard deviation give each day a local baseline to compare against.

import numpy as np
import pandas as pd

rng = np.random.default_rng(42)
idx = pd.date_range("2024-01-01", periods=120, freq="D")
sales = pd.Series(10000 + rng.normal(0, 300, 120), index=idx)
sales.loc["2024-02-14"] = 16000
sales.loc["2024-03-20"] = 4000

baseline = sales.rolling(14, center=True, min_periods=7)
z = (sales - baseline.mean()) / baseline.std()
print(sales[z.abs() > 3].round(0))
print("days checked:", z.notna().sum())
$ python anomaly.py
2024-02-14    16000.0
2024-03-20     4000.0
dtype: float64
days checked: 120

Both planted days stand out by more than three local standard deviations, and ordinary noise does not. center=True builds each day’s window from the week before and after it, which is fine for a historical report but impossible live, where only past days are available.

Practice

  1. In slicing.py, print the total revenue for each month using resample.
Solution 
import pandas as pd

idx = pd.date_range("2024-01-01", periods=120, freq="D", name="date")
df = pd.DataFrame({"revenue": range(120)}, index=idx)
print(df.resample("ME")["revenue"].sum())
$ python practice_monthly.py
date
2024-01-31     465
2024-02-29    1305
2024-03-31    2325
2024-04-30    3045
Freq: ME, Name: revenue, dtype: int64

"ME" means month end, so each bucket is labelled with its last day. April is partial here — the range stops on 29 April.

  1. In gaps.py, fill the gaps by carrying readings forward with no limit, and count how many values were invented.
Solution 
import pandas as pd

grid = pd.date_range("2024-01-01 00:00", periods=8, freq="h")
readings = pd.Series([1.0, 2.0, 5.0, 6.0, 8.0], index=grid[[0, 1, 4, 5, 7]])
full = readings.reindex(grid)
filled = full.ffill()
print(filled.tolist())
print("invented:", (full.isna() & filled.notna()).sum())
$ python practice_ffill.py
[1.0, 2.0, 2.0, 2.0, 5.0, 6.0, 6.0, 8.0]
invented: 3

Comparing the before and after masks counts exactly the values that came from filling rather than from the sensor — a number worth reporting next to any filled series.

  1. In timezones.py, convert the localized times to Europe/London and check the UTC offset of each.
Solution 
import pandas as pd

naive = pd.DatetimeIndex(["2024-03-10 01:30", "2024-03-10 03:30"])
london = naive.tz_localize("America/New_York").tz_convert("Europe/London")
for ts in london:
    print(ts, ts.utcoffset())
$ python practice_london.py
2024-03-10 06:30:00+00:00 0:00:00
2024-03-10 07:30:00+00:00 0:00:00

London had not yet changed its clocks on 10 March 2024 (the UK changes later in March), so both times show a zero offset even though New York’s own offset changed between them.

Frequently Asked Questions

What is the difference between resample and rolling in Pandas?
resample groups rows into fixed time buckets, such as hours into days, and returns one row per bucket, so it changes the frequency of the data. rolling computes a statistic over a sliding window and returns one value per original row, so the frequency stays the same. Use resample to summarize by period and rolling to smooth a series.
Why does df['2024-01'] raise a KeyError on a DataFrame with a date index?
On a DataFrame, square brackets with a string select a column, so df['2024-01'] looks for a column with that name. Pandas before 2.0 also accepted it as a row selection on a date index, which is why older examples use it; that shortcut was deprecated in 1.2 and removed in 2.0. Use df.loc['2024-01'] to select every row in January 2024.
How do I handle time zones in Pandas?
Use tz_localize to attach a time zone to naive timestamps and tz_convert to move aware timestamps to another zone. Localizing can fail around daylight-saving changes, because some local times never happen and others happen twice; the nonexistent and ambiguous parameters control what Pandas does then. Storing timestamps in UTC and converting only for display avoids most of these problems.
How do I find missing timestamps in a time series?
Build the complete expected grid with pd.date_range and reindex the data onto it. Every timestamp that was missing becomes a row of NaN, so isna().sum() counts the gaps, and ffill or interpolate can then fill them with a policy you choose.