Programming for Data Processing

Lecture 8
Time Series
Data Processing

Today

• From parsing → resampling → rolling → TZ/DST → outliers
• Live notebooks: Real-world data for Hanoi/Vietnam (link) + Household Power Consumption (link)
• Focus: clean indices, correct frequency, reliable aggregates

Why Processing Time Series?

• Many signals are time-ordered: apps, sensors, finance, operations
• Order reveals trends, seasonality, autocorrelation
• Clean time axes → reliable KPIs, monitoring, forecasts
• Real-world wrinkles: TZ/DST, gaps, duplicates, irregular sampling

What Goes Wrong Without It?

• Duplicate or unsorted timestamps break asfreq() & rolling
• Hidden gaps bias aggregates after resample()
• Bad TZ/DST handling shifts data or drops hours
• Random (non-time) splits cause leakage
• Imputation over long spans collapses variance
• Mixing asfreq and resample intents leads to silent errors

Date & Time Basics

• Parse with pd.to_datetime(..., errors="coerce")
• Set DateTimeIndex; always sort_index()
• Keep raw strings until parsing is verified

df = pd.read_csv("data.csv")
df["ts"] = pd.to_datetime(df["timestamp"], 
                          utc=True, errors="coerce")
df = df.set_index("ts").sort_index()
      

# DateTimeIndex[UTC] ready for time-aware ops
      

Indexing & Duplicates

• Time slicing: s["2022-10":"2022-12"]
• Detect duplicates: .index.duplicated()
• Resolve: groupby(level=0).mean() or drop_duplicates(keep="last")

dup = df.index.duplicated(keep=False)
df_clean = df.groupby(level=0).mean(numeric_only=True)  # collapse exact dup timestamps
      

# Unique DateTimeIndex achieved
      

Date Ranges & Frequencies

• Generate with pd.date_range
• Common codes: D, H, T, MS, QS
• Shift values with s.shift(k) for lags

rng = pd.date_range("2024-01-01", periods=5, freq="D")
s = pd.Series(range(5), index=rng)
s_lag1 = s.shift(1)
      

# Lag features ready
      

Time Zones & DST

• Localize tz-naive: Raw data did not specify timezone, then use tz_localize("Europe/Paris")
• Convert tz-aware: Raw data used a different timezone, then use tz_convert("Asia/Ho_Chi_Minh")
• Daylight Saving Time (DST): handle nonexistent & ambiguous times

df = df.tz_localize("Europe/Paris", ambiguous="NaT", nonexistent="NaT")
df_hcm = df.tz_convert("Asia/Ho_Chi_Minh")
      

# DST-safe index for resampling and rolling windows
      

Upsampling vs Downsampling

• Upsample: increase frequency (e.g., daily → hourly)
• Downsample: decrease frequency (e.g., minute → hourly)
• Two main methods: .asfreq() vs .resample()

.asfreq()

Reveal / Align to a Fixed Grid

• Reindex to a target frequency without aggregation
• Preserves values at exact timestamps; others become NaN
• Use to expose gaps, prep interpolate("time") / ffill(), align multiple series
• Typical: hourly → 15min grid, then impute

.resample()

Aggregate / Change Granularity

• Bins by time; apply aggregations like mean(), sum()
• Downsample: e.g., 1min → 15min with mean()
• Upsample: prefer .asfreq() + impute (aggregation alone won’t create values)
• Compare: asfreq = align grid (no math); resample(...).mean() = within-bin summary

Grouped Resampling & Calendar Features

• Weekly/monthly views: resample("W").sum()
• Per-key groups: df.groupby("city").resample("D").mean()
• Calendar: index.dayofweek, index.month

weekly = s.resample("W").sum()
by_month = s.groupby(s.index.month).mean()
      

# Seasonal patterns by calendar buckets
      

Takeaways

• Rule of thumb:
  • asfreq to align/expose gaps, then interpolate/ffill
  • resample to summarize/change granularity
• Be explicit about intent: asfreq() ≠ mean()

Moving Windows

Capture trends & local patterns

• Row-based: rolling(30, min_periods=15)
• Time-based: rolling("30D")
• Use center=True when visual trend matters

roll = s.rolling("30D", min_periods=15)
ma = roll.mean(); sd = roll.std()
      

# Smooth trend & local variability
      

Missing Data & Imputation

• Reveal gaps: asfreq("D")
• Fill: ffill, bfill, interpolate("time")
• Beware variance collapse on long spans

s_reg = s.asfreq("D")
s_fill = s_reg.interpolate("time").ffill()
      

# Consistent daily series for downstream ops
      

Outliers

• Local rule: |z| > 3 with rolling mean/std
• Another option: rolling MAD
• Guard: ignore tiny sd windows

roll = s.rolling(30, min_periods=15)
mu, sd = roll.mean(), roll.std()
z = (s - mu) / sd
outliers = s[z.abs() > 3]
      

# Flags are relative to the local 30-sample context