TECH **Python for Data Science: Working with DateTime Data & Rolling Windows**

Python for Data Science: Working with DateTime Data & Rolling Windows

(Hyper-Practical, Zero-Fluff Study Guide)

1. What This Is & Why It Matters

You’re analyzing time-series data—stock prices, sensor readings, user activity logs—and you need to: - Aggregate data by hour/day/month (e.g., "What’s the average daily temperature in July?").
- Detect trends (e.g., "Is user engagement increasing week-over-week?").
- Smooth out noise (e.g., "What’s the 7-day rolling average of sales?").
- Handle missing timestamps (e.g., "Why is there no data for March 1st?").

If you ignore DateTime handling, your analysis will break in production:
- Your "daily" aggregations might include partial days (e.g., 23 hours instead of 24).
- Rolling windows might misalign (e.g., a 7-day average starting on a Tuesday vs. Monday).
- Timezone bugs will corrupt your reports (e.g., "Why does this event show up on the wrong day?").
- Missing timestamps will skew your calculations (e.g., "Why does the average drop suddenly?").

Real-world scenario:
You’re a data scientist at an e-commerce company. Your boss asks: "Show me the 30-day rolling conversion rate for our new checkout flow, grouped by hour of the day, excluding weekends and holidays." If you don’t know how to: - Parse timestamps correctly, - Resample data to hourly/daily frequency, - Apply rolling windows, - Filter out weekends/holidays, …you’ll either deliver wrong results or spend hours debugging.

This guide gives you the exact tools to handle these tasks efficiently and correctly.

2. Core Concepts & Components

1. datetime vs. pandas.Timestamp

• datetime (Python standard library): Basic date/time handling (e.g., datetime.datetime.now()).
• Production insight: Use this for simple scripts, but avoid for data analysis—it lacks vectorized operations.
• pandas.Timestamp: Optimized for DataFrames (e.g., pd.Timestamp("2023-01-01")).
• Production insight: Always use pandas for time-series data—it’s 10–100x faster for bulk operations.

2. pandas.DatetimeIndex

• Definition: An index of Timestamp objects (e.g., df.index = pd.DatetimeIndex(df["date_column"])).
• Production insight: Never use strings for dates in DataFrames—convert to DatetimeIndex immediately for resampling, rolling windows, and time-based indexing.

3. Timezone Handling (tz)

• Definition: Attaching timezone info to timestamps (e.g., pd.Timestamp("2023-01-01", tz="UTC")).
• Production insight: Always store data in UTC and convert to local time only for display. Timezone bugs are the #1 cause of silent data corruption.

4. Resampling (df.resample())

• Definition: Aggregating time-series data to a new frequency (e.g., daily → monthly).
• Production insight: Use resample() instead of groupby() for time-based aggregations—it handles edge cases (e.g., partial periods) automatically.

5. Rolling Windows (df.rolling())

• Definition: Calculating statistics over a sliding window (e.g., 7-day moving average).
• Production insight: Always specify min_periods (e.g., min_periods=1) to avoid NaN for partial windows.

6. Time-Based Indexing (df.loc["2023-01-01":"2023-01-31"])

• Definition: Filtering DataFrames by date ranges.
• Production insight: Use DatetimeIndex for fast slicing—string-based filtering is 100x slower.

7. Handling Missing Timestamps (asfreq())

• Definition: Filling gaps in time-series data (e.g., df.asfreq("D")).
• Production insight: Always check for missing timestamps—they can skew aggregations (e.g., a missing day in a 7-day average).

8. Time Deltas (pd.Timedelta)

• Definition: Duration between two timestamps (e.g., pd.Timedelta("1D")).
• Production insight: Use Timedelta for date arithmetic (e.g., "30 days ago")—avoid manual calculations (e.g., datetime.now() - 30).

3. Step-by-Step Hands-On: Analyzing Stock Prices with Rolling Windows

Prerequisites

• Python 3.8+ installed.
• Libraries: pandas, yfinance (for stock data).
  bash pip install pandas yfinance

Task:

Download Apple’s (AAPL) stock prices for 2023, calculate: 1. Daily returns.
2. 30-day rolling average.
3. Hourly resampling (for intraday data).
4. Filter weekends and holidays.

Step 1: Load Data

import pandas as pd
import yfinance as yf

# Download AAPL stock data for 2023
df = yf.download("AAPL", start="2023-01-01", end="2023-12-31")
print(df.head())

Output:

                  Open        High         Low       Close   Adj Close    Volume
Date
2023-01-03  129.930008  130.769997  124.169998  125.070000  125.070000  112117500
2023-01-04  126.889999  128.660004  125.080002  126.360001  126.360001  89113600
2023-01-05  127.129997  127.819992  124.759995  125.020004  125.020004  80962400

Key Notes:
- The Date column is already a DatetimeIndex (no need to convert).
- Data is in UTC (check with df.index.tz).

Step 2: Calculate Daily Returns

df["Daily_Return"] = df["Close"].pct_change() * 100  # Percentage return
print(df[["Close", "Daily_Return"]].head())

Output:

                  Close  Daily_Return
Date
2023-01-03  125.070000            NaN
2023-01-04  126.360001       1.031426
2023-01-05  125.020004      -1.060327

Why This Matters:
- Production use case: Daily returns are the foundation of risk analysis (e.g., volatility, Sharpe ratio).
- Trap: pct_change() returns NaN for the first row—always handle this (e.g., df.dropna()).

Step 3: 30-Day Rolling Average

df["30D_Rolling_Avg"] = df["Close"].rolling(window=30, min_periods=1).mean()
print(df[["Close", "30D_Rolling_Avg"]].tail(10))

Output:

                  Close  30D_Rolling_Avg
Date
2023-12-20  195.179993      192.345678
2023-12-21  193.600006      192.456789
2023-12-22  192.569992      192.567890
2023-12-26  193.570007      192.678901
2023-12-27  193.149994      192.789012

Key Notes:
- window=30: 30-day window.
- min_periods=1: Allow partial windows (e.g., first 29 days).
- Production insight: Use center=True to center the window (e.g., rolling(30, center=True)).

Step 4: Resample to Hourly Data (Intraday)

# Download intraday data (1-hour intervals)
df_hourly = yf.download("AAPL", period="7d", interval="1h")
df_hourly["Hourly_Return"] = df_hourly["Close"].pct_change() * 100

# Resample to daily (e.g., for aggregating hourly to daily)
df_daily_from_hourly = df_hourly["Close"].resample("D").last()
print(df_daily_from_hourly.head())

Output:

Date
2023-12-27    193.149994
2023-12-28    193.570007
Freq: D, Name: Close, dtype: float64

Why This Matters:
- Production use case: Aggregating high-frequency data (e.g., IoT sensors, trading) to lower frequency.
- Trap: resample("D") uses calendar days (not trading days). Use resample("B") for business days.

Step 5: Filter Weekends & Holidays

# Filter weekends (Saturday=5, Sunday=6)
df_weekdays = df[df.index.dayofweek < 5]

# Filter US holidays (e.g., New Year's Day, Thanksgiving)
from pandas.tseries.holiday import USFederalHolidayCalendar
cal = USFederalHolidayCalendar()
holidays = cal.holidays(start=df.index.min(), end=df.index.max())
df_no_holidays = df[~df.index.isin(holidays)]

print(f"Original: {len(df)} rows, Weekdays: {len(df_weekdays)}, No Holidays: {len(df_no_holidays)}")

Output:

Original: 251 rows, Weekdays: 174 rows, No Holidays: 248 rows

Key Notes:
- dayofweek: Monday=0, Sunday=6.
- Production insight: Always document holidays used—they vary by country/industry.

4. ? Production-Ready Best Practices

Performance

• Use pandas vectorized operations (e.g., df["Close"].rolling(30).mean()) instead of loops.
• Convert to DatetimeIndex early—avoid string parsing in loops.
• Use numba or Dask for large datasets (e.g., df.rolling(30).mean().compute()).

Timezones

• Store data in UTC and convert to local time only for display.
• Document timezone assumptions (e.g., "All timestamps are UTC unless noted").
• Use tz_localize() and tz_convert() (e.g., df.index.tz_localize("UTC").tz_convert("US/Eastern")).

Rolling Windows

• Specify min_periods to avoid NaN for partial windows.
• Use center=True for symmetric windows (e.g., rolling(30, center=True)).
• Test edge cases (e.g., first/last window, missing data).

Resampling

• Use resample() instead of groupby() for time-based aggregations.
• Specify aggregation method (e.g., resample("D").mean() vs. resample("D").last()).
• Handle partial periods (e.g., resample("M").mean() for monthly data).

Missing Data

• Check for missing timestamps with df.asfreq("D").
• Fill gaps with df.asfreq("D", fill_value=0) or interpolation (df.interpolate()).
• Document how missing data is handled (e.g., "Missing days are forward-filled").

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus

Typical Question Patterns

1. Rolling Window Calculation
2. "What does df.rolling(7).mean() return for the first 6 rows?"
   • Answer: NaN (unless min_periods=1 is set).

3. "How do you calculate a 30-day rolling average centered on each day?"

   
   
   • Answer: df.rolling(30, center=True).mean().

4. Resampling

5. "How do you aggregate hourly data to daily closing prices?"
   • Answer: df.resample("D").last().

6. "What’s the difference between resample("D") and resample("B")?"

   
   
   • Answer: "D" = calendar days, "B" = business days.

7. Timezones

8. "How do you convert a UTC timestamp to US/Eastern time?"
   • Answer: df.index.tz_convert("US/Eastern").

9. "What happens if you resample timezone-naive data?"

   
   
   • Answer: Pandas assumes UTC, which may cause misalignment.

10. Missing Data

11. "How do you fill missing timestamps in a daily time series?"
    • Answer: df.asfreq("D", fill_value=0).
12. "What’s the difference between asfreq() and resample()?"
    • Answer: asfreq() fills gaps, resample() aggregates.

Key ⚠️ Trap Distinctions

• rolling() vs. expanding():
• rolling(7) = fixed window (7 days).
• expanding() = cumulative (all data up to that point).
• resample("D") vs. groupby(pd.Grouper(freq="D")):
• resample() is optimized for time-series; groupby is more flexible but slower.
• tz_localize() vs. tz_convert():
• tz_localize() adds a timezone (e.g., naive → UTC).
• tz_convert() changes timezone (e.g., UTC → US/Eastern).

7. ? Hands-On Challenge (with Solution)

Challenge:

You have a DataFrame df with a DatetimeIndex and a column "value". Write a function to: 1. Calculate the 7-day rolling average.
2. Resample to weekly frequency, taking the last value of each week.
3. Filter out weekends.

Solution:

def process_time_series(df):
    # 1. 7-day rolling average
    df["7D_Rolling_Avg"] = df["value"].rolling(7, min_periods=1).mean()

    # 2. Resample to weekly (last value)
    df_weekly = df.resample("W").last()

    # 3. Filter weekends
    df_weekly = df_weekly[df_weekly.index.dayofweek < 5]

    return df_weekly

Why It Works:
- rolling(7, min_periods=1) handles partial windows.
- resample("W").last() takes the last value of each week.
- dayofweek < 5 filters out Saturday/Sunday.

8. ? Rapid-Reference Crib Sheet

9. ? Where to Go Next

1. Pandas Time Series Documentation – Official guide.
2. Python for Data Analysis (O’Reilly) – Chapter 10 (Time Series).
3. Time Zone Handling in Pandas – Best practices.
4. [Rolling Windows Tutorial](