Pandas Time Series

Real-World Scenario

An IoT platform ingests sensor readings every minute from 10,000 devices. The analytics pipeline needs to: detect missing timestamps, resample to hourly averages, compute 24-hour rolling baselines, flag anomalies, and produce daily summaries. All of this is time series manipulation — one of Pandas’ strongest areas.

Parsing and Indexing Dates

import pandas as pd
import numpy as np

# Read CSV with automatic date parsing
df = pd.read_csv(
    "sensor_data.csv",
    parse_dates=["timestamp"],
    index_col="timestamp",
)

# Or convert an existing string column
df = pd.DataFrame({
    "timestamp": ["2024-01-15 08:00", "2024-01-15 09:00", "2024-01-15 10:00"],
    "temp_c":    [22.1, 22.4, 23.0],
})
df["timestamp"] = pd.to_datetime(df["timestamp"])
df = df.set_index("timestamp")

# DatetimeIndex gives access to date components
df.index.year
df.index.month
df.index.day
df.index.hour
df.index.dayofweek    # 0=Monday, 6=Sunday
df.index.is_month_end

# Create a complete time series with no gaps
dates = pd.date_range(start="2024-01-01", end="2024-12-31", freq="D")
print(len(dates))  # 366 (2024 is a leap year)

# Hourly for a single day
hours = pd.date_range("2024-01-01", periods=24, freq="h")

Slicing Time Series

import pandas as pd
import numpy as np

rng = np.random.default_rng(42)
idx = pd.date_range("2024-01-01", periods=365, freq="D", name="date")
df = pd.DataFrame({"revenue": rng.uniform(5000, 20000, 365)}, index=idx)

# Slice by string date — Pandas parses it automatically
print(df["2024-01"])           # all of January
print(df["2024-Q1"])           # Q1 (Jan–Mar)
print(df["2024-03-01":"2024-03-31"])  # March

# Truncate
print(df.truncate(before="2024-06-01", after="2024-06-30"))

Resampling — Changing Frequency

import pandas as pd
import numpy as np

rng = np.random.default_rng(42)
idx = pd.date_range("2024-01-01", periods=8760, freq="h")   # hourly for full year
df = pd.DataFrame({
    "power_kw": rng.uniform(50, 500, 8760),
    "temp_c":   rng.normal(15, 8, 8760),
}, index=idx)

# Downsample: hourly → daily (one row per day)
daily = df["power_kw"].resample("D").agg({
    "total_kwh": "sum",
    "peak_kw":   "max",
    "avg_kw":    "mean",
})
print(daily.head(5))

# Downsample to monthly
monthly = df.resample("ME").agg(   # ME = Month End
    total_kwh = ("power_kw", "sum"),
    avg_temp  = ("temp_c",   "mean"),
)
print(monthly)

# Upsample: daily → hourly with forward-fill (e.g., for sparse sensor data)
sparse = pd.DataFrame(
    {"price": [100.0, 105.0, 98.0]},
    index=pd.date_range("2024-01-01", periods=3, freq="D")
)
hourly = sparse.resample("h").ffill()  # carry forward until next reading
print(hourly.head(25))

Rolling Windows

import pandas as pd
import numpy as np

rng = np.random.default_rng(42)
idx = pd.date_range("2024-01-01", periods=180, freq="D")
df = pd.DataFrame({"sales": rng.uniform(1000, 5000, 180)}, index=idx)

# Simple rolling mean — 7-day smoothing
df["sales_7d"] = df["sales"].rolling(window=7).mean()

# 30-day rolling with minimum periods (avoid NaN at start)
df["sales_30d"] = df["sales"].rolling(window=30, min_periods=15).mean()

# Rolling std — measure volatility
df["volatility_7d"] = df["sales"].rolling(window=7).std()

# Exponentially weighted moving average — more weight on recent values
df["ewm_7d"] = df["sales"].ewm(span=7).mean()

# Multiple statistics at once
rolling_stats = df["sales"].rolling(30).agg(["mean", "std", "min", "max"])
print(rolling_stats.head(35).tail(5))  # first rows with 30 data points

Detecting Gaps and Reindexing

import pandas as pd
import numpy as np

rng = np.random.default_rng(42)

# Simulate sensor data with missing timestamps
all_hours = pd.date_range("2024-01-01", periods=720, freq="h")
# Randomly drop 10% of readings
present_idx = rng.choice(all_hours, size=648, replace=False)
present_idx = pd.DatetimeIndex(sorted(present_idx))

df = pd.DataFrame({"reading": rng.standard_normal(648)}, index=present_idx)

# Reindex to a complete time grid — missing hours become NaN
complete = df.reindex(all_hours)
missing_count = complete["reading"].isnull().sum()
print(f"Missing hours: {missing_count}")  # ~72

# Strategies to fill gaps
filled_ffill = complete.ffill(limit=2)       # carry forward up to 2 hours
filled_interp = complete.interpolate("time") # linear interpolation
filled_zero   = complete.fillna(0)           # assume zero reading

Time Zone Handling

import pandas as pd

# Create a time series in UTC
idx_utc = pd.date_range("2024-01-01", periods=24, freq="h", tz="UTC")
df = pd.DataFrame({"temp": range(24)}, index=idx_utc)

# Convert to US Eastern (handles DST automatically)
df_eastern = df.tz_convert("America/New_York")
print(df_eastern.head(5))

# Localize a naive (timezone-unaware) series
naive_idx = pd.date_range("2024-01-01", periods=24, freq="h")
df_naive = pd.DataFrame({"temp": range(24)}, index=naive_idx)
df_utc = df_naive.tz_localize("UTC")

# Remove timezone info when saving to systems that don't support it
df_no_tz = df_utc.tz_localize(None)

Real-World: Daily Sales Anomaly Detection

import pandas as pd
import numpy as np

rng = np.random.default_rng(42)
idx = pd.date_range("2023-01-01", periods=730, freq="D")

# Simulate sales with weekly seasonality + trend + anomalies
base = np.linspace(10000, 15000, 730)
seasonality = 2000 * np.sin(np.arange(730) * 2 * np.pi / 7)
noise = rng.normal(0, 500, 730)
sales = base + seasonality + noise

df = pd.DataFrame({"sales": sales}, index=idx)

# Inject anomalies
df.loc["2023-06-15", "sales"] = 50000    # spike
df.loc["2023-11-28", "sales"] = 100000   # Black Friday
df.loc["2023-12-25", "sales"] = 2000     # Christmas (low)

# Rolling Z-score: flag values more than 2.5 std from 30-day rolling mean
window = 30
df["rolling_mean"] = df["sales"].rolling(window, center=True).mean()
df["rolling_std"]  = df["sales"].rolling(window, center=True).std()
df["z_score"] = (df["sales"] - df["rolling_mean"]) / df["rolling_std"]
df["anomaly"] = df["z_score"].abs() > 2.5

anomalies = df[df["anomaly"]]
print(f"Anomalies detected: {len(anomalies)}")
print(anomalies[["sales", "rolling_mean", "z_score"]].round(0))