Time Series Analysis Example

Demonstrates how to analyze temporal data with moving averages, trend calculations, and aggregation by time periods.

Scenario

You have daily stock price data and need to:

1. Calculate moving averages for trend detection
2. Calculate daily returns
3. Filter for trading days only
4. Aggregate by week to see weekly trends
5. Sort chronologically

Raw Data

| date       | close | volume    |
|------------|-------|-----------|
| 2024-01-01 | 150   | 1000000   |
| 2024-01-02 | 152   | 1200000   |
| 2024-01-03 | 151   | 950000    |
| 2024-01-04 | 153   | 1100000   |
| 2024-01-05 | 155   | 1300000   |
| 2024-01-08 | 154   | 1050000   |
| 2024-01-09 | 156   | 1150000   |
| 2024-01-10 | 158   | 1400000   |

Chart Configuration

type: line
engine: plot
x: Week Start
y: Avg Price

data:
  source: |
    date,close,volume
    2024-01-01,150,1000000
    2024-01-02,152,1200000
    2024-01-03,151,950000
    2024-01-04,153,1100000
    2024-01-05,155,1300000
    2024-01-08,154,1050000
    2024-01-09,156,1150000
    2024-01-10,158,1400000

transformations:
  # Step 1: Calculate daily return percentage
  - type: derive
    configuration:
      columns:
        return_pct: "(close - lag_close) / lag_close * 100"
        # Note: lag_close should come from previous row (typically handled by sorting)

  # Step 2: Filter out weekends (assuming dates skip weekends)
  - type: filter
    configuration:
      where:
        volume: { gt: 0 }

  # Step 3: Calculate weekly aggregates
  - type: derive
    configuration:
      columns:
        week: "Math.floor((new Date(date) - new Date('2024-01-01')) / (7 * 24 * 60 * 60 * 1000))"

  - type: aggregate
    configuration:
      groupBy: ["week"]
      first: ["date"]
      avg: ["close"]
      max: ["close"]
      min: ["close"]
      sum: ["volume"]

  # Step 4: Sort by week ascending
  - type: sort
    configuration:
      by:
        - column: "week"
          direction: "asc"

  # Step 5: Rename for display
  - type: rename
    configuration:
      mapping:
        date_first: "Week Start"
        close_avg: "Avg Price"
        close_max: "High"
        close_min: "Low"
        volume_sum: "Total Volume"

Transformation Pipeline Walkthrough

Step 1: Derive (Calculate Returns)

Input: 8 rows of daily prices

Transformation:

return_pct = (close - lag_close) / lag_close * 100

Output: Added return_pct column showing daily percentage change

Step 2: Filter (Volume Check)

Input: 8 rows

Condition: volume > 0

Output: 8 rows (all volume values are positive)

Step 3: Derive (Calculate Week Number)

Input: 8 rows

Transformation: Calculate week number from date

Output: Added week column (0 or 1 for first/second week)

Step 4: Aggregate (Weekly Summary)

Input: 8 daily records

Grouping: By week number

Output: 2 weekly records

| week | date_first | close_avg | close_max | close_min | volume_sum |
|------|------------|-----------|-----------|-----------|------------|
| 0    | 2024-01-01 | 152.00    | 155       | 150       | 5250000    |
| 1    | 2024-01-08 | 156.00    | 158       | 154       | 3600000    |

Step 5: Sort (Chronological)

Input: 2 weekly records

Sorting: By week ascending

Output: Already sorted, maintains chronological order

Step 6: Rename (For Display)

Input: 2 weekly records

Mapping:

• date_first → Week Start
• close_avg → Avg Price
• close_max → High
• close_min → Low
• volume_sum → Total Volume

Final Output:

| week | Week Start | Avg Price | High | Low | Total Volume |
|------|------------|-----------|------|-----|--------------|
| 0    | 2024-01-01 | 152.00    | 155  | 150 | 5250000      |
| 1    | 2024-01-08 | 156.00    | 158  | 154 | 3600000      |

Chart Result

A line chart showing average weekly closing prices with trend visible:

┌─────────────────────────────────┐
│   Weekly Stock Price Trend      │
├─────────────────────────────────┤
│
│  158 ╱
│  156 ╱
│  154 ╱
│  152 ╱
│  150 ┱
│       Week 1  Week 2
│
└─────────────────────────────────┘

Use Cases

Scenario 1: Daily Moving Average

Track 5-day moving average:

transformations:
  # Add moving average column
  - type: derive
    configuration:
      columns:
        ma5: "close"  # In real scenario, would reference previous 4 closes

  # Filter to last 100 days
  - type: limit
    configuration:
      offset: 0
      count: 100

Scenario 2: Volatility Analysis

Calculate standard deviation:

transformations:
  # Group by month and calculate volatility
  - type: aggregate
    configuration:
      groupBy: ["month"]
      first: ["date"]
      avg: ["close"]
      max: ["close"]
      min: ["close"]

  # Derive volatility metric
  - type: derive
    configuration:
      columns:
        volatility: "(close_max - close_min) / close_avg * 100"

Scenario 3: Comparative Analysis

Compare multiple time series:

transformations:
  # Derive normalized prices (base = 100)
  - type: derive
    configuration:
      columns:
        normalized: "(close / 150) * 100"

  # Group by week
  - type: aggregate
    configuration:
      groupBy: ["week"]
      avg: ["normalized"]

  # Sort ascending
  - type: sort
    configuration:
      by:
        - column: "week"
          direction: "asc"

Performance Tips

1. Aggregate Early: Reduces from days to weeks/months, huge performance gain
2. Filter Before Aggregation: Remove outliers before calculations
3. Derive Complex Metrics: Do expensive calculations on aggregated data
4. Limit Results: Paginate large time series for better rendering

Common Issues & Solutions

Issue	Solution
Too much data to visualize	Aggregate to higher time period (daily → weekly → monthly)
Chart too spiky	Use derived moving average column
Missing dates in plot	Filter out nulls, check date format consistency
Aggregation has gaps	Verify groupBy columns exist in all rows
Performance is slow	Profile aggregation step, it's usually the bottleneck

Real-World Examples

Stock Trading Signals

transformations:
  # Add day of week for pattern analysis
  - type: derive
    configuration:
      columns:
        day_of_week: "new Date(date).getDay()"
        price_change: "close - open"

  # Filter trading days only
  - type: filter
    configuration:
      where:
        volume: { gt: 100000 }

  # Group by day of week for average behavior
  - type: aggregate
    configuration:
      groupBy: ["day_of_week"]
      avg: ["price_change", "volume"]
      count: ["close"]

Cryptocurrency 24/7 Monitoring

transformations:
  # Add hour from timestamp
  - type: derive
    configuration:
      columns:
        hour: "new Date(timestamp).getHours()"

  # Aggregate by hour
  - type: aggregate
    configuration:
      groupBy: ["hour"]
      avg: ["price"]
      max: ["price"]
      min: ["price"]

  # Sort by hour
  - type: sort
    configuration:
      by:
        - column: "hour"
          direction: "asc"

Application Performance Monitoring

transformations:
  # Add minute bucket from timestamp
  - type: derive
    configuration:
      columns:
        minute_bucket: "Math.floor(timestamp / 60000)"

  # Aggregate metrics by minute
  - type: aggregate
    configuration:
      groupBy: ["minute_bucket"]
      avg: ["response_time_ms"]
      max: ["response_time_ms"]
      count: ["request_id"]

  # Limit to last 60 minutes
  - type: limit
    configuration:
      count: 60

Best Practices

1. Sort Early: Get data in chronological order before other operations
2. Validate Dates: Ensure consistent date format before time calculations
3. Aggregate First: Then derive metrics on smaller dataset
4. Handle Gaps: Consider backfilling missing dates in time series
5. Document Time Zones: Be explicit about UTC vs local times