1┌─────────────────────────────────────────────────────────┐
2│            Time Series Decomposition                     │
3├─────────────────────────────────────────────────────────┤
4│                                                          │
5│  Original = Trend + Seasonality + Residual              │
6│                                                          │
7│  ┌────────────────────────────────────────────┐         │
8│  │ Trend: Long-term direction                 │         │
9│  │   ↗ Upward / ↘ Downward / → Flat           │         │
10│  └────────────────────────────────────────────┘         │
11│                                                          │
12│  ┌────────────────────────────────────────────┐         │
13│  │ Seasonality: Regular patterns               │         │
14│  │   📅 Daily / Weekly / Monthly / Yearly     │         │
15│  └────────────────────────────────────────────┘         │
16│                                                          │
17│  ┌────────────────────────────────────────────┐         │
18│  │ Residual: Random noise                     │         │
19│  │   ~ Unpredictable fluctuations             │         │
20│  └────────────────────────────────────────────┘         │
21│                                                          │
22└─────────────────────────────────────────────────────────┘

1import pandas as pd
2import numpy as np
3import matplotlib.pyplot as plt
4
5# Create datetime index
6dates = pd.date_range(start='2022-01-01', end='2024-12-31', freq='D')
7
8# Generate sample data with trend + seasonality + noise
9np.random.seed(42)
10n = len(dates)
11trend = np.linspace(100, 200, n)  # Upward trend
12seasonality = 30 * np.sin(2 * np.pi * np.arange(n) / 365)  # Yearly cycle
13noise = np.random.normal(0, 10, n)
14
15sales = trend + seasonality + noise
16
17df = pd.DataFrame({'date': dates, 'sales': sales})
18df.set_index('date', inplace=True)
19
20print(df.head())
21print(f"\nShape: {df.shape}")
22print(f"Date range: {df.index.min()} to {df.index.max()}")

1# Date components
2df['year'] = df.index.year
3df['month'] = df.index.month
4df['quarter'] = df.index.quarter
5df['day_of_week'] = df.index.dayofweek
6df['week_of_year'] = df.index.isocalendar().week
7df['is_weekend'] = df.index.dayofweek >= 5
8df['is_month_start'] = df.index.is_month_start
9df['is_month_end'] = df.index.is_month_end
10
11# Parse dates from strings
12df['date'] = pd.to_datetime(df['date_string'], format='%Y-%m-%d')
13
14# Handle different formats
15df['date'] = pd.to_datetime(df['date_col'], infer_datetime_format=True)
16
17# Set datetime index
18df = df.set_index('date')
19df = df.sort_index()

1# Create complete date range
2full_range = pd.date_range(df.index.min(), df.index.max(), freq='D')
3
4# Reindex to fill missing dates
5df = df.reindex(full_range)
6
7# Fill missing values
8df['sales'] = df['sales'].fillna(method='ffill')  # Forward fill
9df['sales'] = df['sales'].fillna(method='bfill')  # Backward fill
10df['sales'] = df['sales'].interpolate(method='linear')  # Interpolate
11df['sales'] = df['sales'].fillna(df['sales'].mean())  # Mean
12
13# Check for gaps
14missing_dates = full_range.difference(df.index)
15print(f"Missing dates: {len(missing_dates)}")

1fig, axes = plt.subplots(3, 1, figsize=(14, 10))
2
3# Line plot
4axes[0].plot(df.index, df['sales'], linewidth=0.8)
5axes[0].set_title('Daily Sales')
6axes[0].set_ylabel('Sales')
7
8# Monthly resampling
9monthly = df['sales'].resample('M').mean()
10axes[1].plot(monthly.index, monthly.values, marker='o')
11axes[1].set_title('Monthly Average Sales')
12axes[1].set_ylabel('Sales')
13
14# Year-over-year comparison
15for year in df.index.year.unique():
16    yearly_data = df[df.index.year == year]['sales']
17    yearly_data.index = yearly_data.index.dayofyear
18    axes[2].plot(yearly_data.index, yearly_data.values, label=str(year), alpha=0.7)
19axes[2].set_title('Year-over-Year Comparison')
20axes[2].set_xlabel('Day of Year')
21axes[2].legend()
22
23plt.tight_layout()
24plt.show()

1def seasonal_plot(df, column, freq='month'):
2    """Create seasonal subseries plot"""
3    fig, axes = plt.subplots(4, 3, figsize=(14, 10))
4    axes = axes.flatten()
5    
6    for i, month in enumerate(range(1, 13)):
7        monthly_data = df[df.index.month == month][column]
8        axes[i].plot(monthly_data.index.year, monthly_data.values, 'o-')
9        axes[i].set_title(f'Month {month}')
10        axes[i].axhline(monthly_data.mean(), color='r', linestyle='--', alpha=0.5)
11    
12    plt.suptitle('Seasonal Subseries Plot')
13    plt.tight_layout()
14    plt.show()
15
16seasonal_plot(df, 'sales')

1# Pivot for heatmap
2heatmap_data = df.pivot_table(
3    values='sales',
4    index=df.index.year,
5    columns=df.index.month,
6    aggfunc='mean'
7)
8
9plt.figure(figsize=(12, 6))
10plt.imshow(heatmap_data, cmap='YlOrRd', aspect='auto')
11plt.colorbar(label='Average Sales')
12plt.yticks(range(len(heatmap_data.index)), heatmap_data.index)
13plt.xticks(range(12), ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 
14                       'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'])
15plt.xlabel('Month')
16plt.ylabel('Year')
17plt.title('Sales Heatmap by Year and Month')
18plt.show()

1# Daily to Weekly
2weekly = df['sales'].resample('W').agg({
3    'mean': 'mean',
4    'sum': 'sum',
5    'min': 'min',
6    'max': 'max'
7})
8
9# Daily to Monthly
10monthly = df['sales'].resample('M').agg(['mean', 'std', 'min', 'max'])
11
12# Daily to Quarterly
13quarterly = df['sales'].resample('Q').sum()
14
15# Custom aggregation
16monthly_stats = df.resample('M').agg({
17    'sales': ['sum', 'mean', 'std'],
18    'quantity': 'sum'
19})
20
21# Rename columns
22monthly_stats.columns = ['total_sales', 'avg_sales', 'std_sales', 'total_qty']

1# Monthly to Daily
2monthly_data = pd.DataFrame({
3    'date': pd.date_range('2024-01-01', periods=12, freq='M'),
4    'value': [100, 120, 115, 130, 145, 160, 155, 170, 165, 180, 190, 200]
5})
6monthly_data.set_index('date', inplace=True)
7
8# Upsample with forward fill
9daily = monthly_data.resample('D').ffill()
10
11# Upsample with interpolation
12daily_interp = monthly_data.resample('D').interpolate(method='linear')
13
14# Spline interpolation (smoother)
15daily_spline = monthly_data.resample('D').interpolate(method='spline', order=3)

1# Business days only
2business_days = df.asfreq('B')
3
4# Week starting Monday
5weekly_mon = df.resample('W-MON').sum()
6
7# Month end
8monthly_end = df.resample('M').last()
9
10# Quarter end
11quarterly = df.resample('Q-DEC').sum()  # Fiscal year ending December

1# Simple moving average
2df['SMA_7'] = df['sales'].rolling(window=7).mean()
3df['SMA_30'] = df['sales'].rolling(window=30).mean()
4
5# Exponential moving average
6df['EMA_7'] = df['sales'].ewm(span=7, adjust=False).mean()
7df['EMA_30'] = df['sales'].ewm(span=30, adjust=False).mean()
8
9# Rolling statistics
10df['rolling_std'] = df['sales'].rolling(window=30).std()
11df['rolling_min'] = df['sales'].rolling(window=30).min()
12df['rolling_max'] = df['sales'].rolling(window=30).max()
13
14# Plot
15fig, ax = plt.subplots(figsize=(14, 6))
16ax.plot(df.index, df['sales'], alpha=0.3, label='Actual')
17ax.plot(df.index, df['SMA_30'], label='30-day SMA')
18ax.plot(df.index, df['EMA_30'], label='30-day EMA')
19ax.legend()
20ax.set_title('Sales with Moving Averages')
21plt.show()

1# Custom rolling function
2def rolling_range(x):
3    return x.max() - x.min()
4
5df['rolling_range'] = df['sales'].rolling(window=30).apply(rolling_range)
6
7# Rolling correlation
8df['rolling_corr'] = df['sales'].rolling(window=30).corr(df['marketing_spend'])
9
10# Rolling regression slope
11from scipy import stats
12
13def rolling_slope(y):
14    x = np.arange(len(y))
15    slope, _, _, _, _ = stats.linregress(x, y)
16    return slope
17
18df['trend_slope'] = df['sales'].rolling(window=30).apply(rolling_slope)

1# Cumulative statistics
2df['cumsum'] = df['sales'].cumsum()
3df['cummean'] = df['sales'].expanding().mean()
4df['cummax'] = df['sales'].expanding().max()
5df['cummin'] = df['sales'].expanding().min()
6
7# Year-to-date
8df['ytd_sum'] = df.groupby(df.index.year)['sales'].cumsum()

1from scipy import stats
2
3# Extract values
4x = np.arange(len(df))
5y = df['sales'].values
6
7# Linear regression
8slope, intercept, r_value, p_value, std_err = stats.linregress(x, y)
9
10# Calculate trend line
11df['trend'] = intercept + slope * x
12
13print(f"Slope: {slope:.4f} per day")
14print(f"R-squared: {r_value**2:.4f}")
15print(f"P-value: {p_value:.4e}")
16
17# Plot
18plt.figure(figsize=(12, 6))
19plt.plot(df.index, df['sales'], alpha=0.5, label='Actual')
20plt.plot(df.index, df['trend'], 'r-', linewidth=2, label='Trend')
21plt.legend()
22plt.title(f'Sales Trend (slope: {slope:.4f}/day)')
23plt.show()

1# Polynomial fit
2x = np.arange(len(df))
3y = df['sales'].values
4
5# Degree 2 (quadratic)
6coeffs = np.polyfit(x, y, 2)
7poly = np.poly1d(coeffs)
8df['poly_trend'] = poly(x)
9
10# Compare trends
11fig, ax = plt.subplots(figsize=(12, 6))
12ax.plot(df.index, df['sales'], alpha=0.5, label='Actual')
13ax.plot(df.index, df['trend'], label='Linear')
14ax.plot(df.index, df['poly_trend'], label='Polynomial')
15ax.legend()
16plt.show()

1# Remove linear trend
2df['detrended'] = df['sales'] - df['trend']
3
4# Using differencing
5df['diff_1'] = df['sales'].diff(1)  # First difference
6df['diff_7'] = df['sales'].diff(7)  # Weekly difference (remove weekly seasonality)
7
8# Percentage change
9df['pct_change'] = df['sales'].pct_change()
10df['pct_change_7'] = df['sales'].pct_change(periods=7)

1from statsmodels.tsa.seasonal import seasonal_decompose
2
3# Additive decomposition (Original = Trend + Seasonal + Residual)
4result_add = seasonal_decompose(df['sales'], model='additive', period=365)
5
6# Multiplicative decomposition (Original = Trend × Seasonal × Residual)
7result_mult = seasonal_decompose(df['sales'], model='multiplicative', period=365)
8
9# Plot components
10fig = result_add.plot()
11fig.set_size_inches(14, 10)
12plt.tight_layout()
13plt.show()
14
15# Extract components
16trend = result_add.trend
17seasonal = result_add.seasonal
18residual = result_add.resid

1from statsmodels.tsa.seasonal import STL
2
3# STL decomposition
4stl = STL(df['sales'], period=365, robust=True)
5result = stl.fit()
6
7# Plot
8fig = result.plot()
9fig.set_size_inches(14, 10)
10plt.show()
11
12# Get seasonally adjusted data
13df['seasonally_adjusted'] = df['sales'] - result.seasonal

1def calculate_seasonal_indices(df, column, freq='month'):
2    """Calculate seasonal indices"""
3    if freq == 'month':
4        grouped = df.groupby(df.index.month)[column].mean()
5    elif freq == 'dayofweek':
6        grouped = df.groupby(df.index.dayofweek)[column].mean()
7    elif freq == 'quarter':
8        grouped = df.groupby(df.index.quarter)[column].mean()
9    
10    # Calculate index (ratio to overall mean)
11    overall_mean = df[column].mean()
12    seasonal_index = grouped / overall_mean
13    
14    return seasonal_index
15
16monthly_index = calculate_seasonal_indices(df, 'sales', 'month')
17print("Monthly Seasonal Indices:")
18print(monthly_index)
19
20# Visualize
21plt.figure(figsize=(10, 5))
22monthly_index.plot(kind='bar')
23plt.axhline(y=1, color='r', linestyle='--')
24plt.title('Monthly Seasonal Indices')
25plt.xlabel('Month')
26plt.ylabel('Index')
27plt.show()

1# Naive forecast (last value)
2df['forecast_naive'] = df['sales'].shift(1)
3
4# Seasonal naive (same day last year)
5df['forecast_seasonal_naive'] = df['sales'].shift(365)
6
7# Moving average forecast
8df['forecast_ma'] = df['sales'].rolling(window=30).mean().shift(1)
9
10# Exponential smoothing
11df['forecast_ewm'] = df['sales'].ewm(span=30).mean().shift(1)

1from statsmodels.tsa.holtwinters import SimpleExpSmoothing
2
3# Fit model
4model = SimpleExpSmoothing(df['sales']).fit(smoothing_level=0.2)
5
6# In-sample fitted values
7df['ses_fitted'] = model.fittedvalues
8
9# Forecast
10forecast = model.forecast(30)  # Next 30 days
11
12print(f"Smoothing level (alpha): {model.params['smoothing_level']:.4f}")

1from statsmodels.tsa.holtwinters import ExponentialSmoothing
2
3# Fit model with trend and seasonality
4model = ExponentialSmoothing(
5    df['sales'],
6    trend='add',           # Additive trend
7    seasonal='add',        # Additive seasonality
8    seasonal_periods=365   # Yearly seasonality
9).fit()
10
11# In-sample fitted
12df['hw_fitted'] = model.fittedvalues
13
14# Forecast
15forecast = model.forecast(90)  # Next 90 days
16
17# Plot
18fig, ax = plt.subplots(figsize=(14, 6))
19ax.plot(df.index[-365:], df['sales'][-365:], label='Actual')
20ax.plot(forecast.index, forecast.values, 'r--', label='Forecast')
21ax.legend()
22ax.set_title('Holt-Winters Forecast')
23plt.show()

1from sklearn.metrics import mean_absolute_error, mean_squared_error
2
3def evaluate_forecast(actual, predicted, model_name='Model'):
4    """Calculate forecast metrics"""
5    # Remove NaN
6    mask = ~(np.isnan(actual) | np.isnan(predicted))
7    actual = actual[mask]
8    predicted = predicted[mask]
9    
10    mae = mean_absolute_error(actual, predicted)
11    rmse = np.sqrt(mean_squared_error(actual, predicted))
12    mape = np.mean(np.abs((actual - predicted) / actual)) * 100
13    
14    print(f"\n{model_name} Performance:")
15    print(f"  MAE:  {mae:.2f}")
16    print(f"  RMSE: {rmse:.2f}")
17    print(f"  MAPE: {mape:.2f}%")
18    
19    return {'mae': mae, 'rmse': rmse, 'mape': mape}
20
21# Compare methods
22evaluate_forecast(df['sales'], df['forecast_naive'], 'Naive')
23evaluate_forecast(df['sales'], df['forecast_ma'], 'Moving Average')
24evaluate_forecast(df['sales'], df['hw_fitted'], 'Holt-Winters')

1-- PostgreSQL date operations
2SELECT
3    order_date,
4    EXTRACT(YEAR FROM order_date) as year,
5    EXTRACT(MONTH FROM order_date) as month,
6    EXTRACT(DOW FROM order_date) as day_of_week,
7    DATE_TRUNC('month', order_date) as month_start,
8    order_date - INTERVAL '7 days' as week_ago,
9    order_date + INTERVAL '30 days' as month_ahead
10FROM orders;
11
12-- Generate date series
13SELECT generate_series(
14    '2024-01-01'::date,
15    '2024-12-31'::date,
16    '1 day'::interval
17)::date as date;

1-- Daily to monthly
2SELECT
3    DATE_TRUNC('month', order_date) as month,
4    COUNT(*) as orders,
5    SUM(amount) as revenue,
6    AVG(amount) as avg_order
7FROM orders
8GROUP BY DATE_TRUNC('month', order_date)
9ORDER BY month;
10
11-- Moving average with window functions
12SELECT
13    order_date,
14    amount,
15    AVG(amount) OVER (
16        ORDER BY order_date
17        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
18    ) as moving_avg_7d
19FROM orders;
20
21-- Year-over-year comparison
22WITH monthly_sales AS (
23    SELECT
24        DATE_TRUNC('month', order_date) as month,
25        SUM(amount) as revenue
26    FROM orders
27    GROUP BY DATE_TRUNC('month', order_date)
28)
29SELECT
30    month,
31    revenue,
32    LAG(revenue, 12) OVER (ORDER BY month) as revenue_last_year,
33    ROUND((revenue - LAG(revenue, 12) OVER (ORDER BY month)) / 
34          LAG(revenue, 12) OVER (ORDER BY month) * 100, 2) as yoy_growth
35FROM monthly_sales
36ORDER BY month;

1-- Fill missing dates with zero
2WITH date_series AS (
3    SELECT generate_series(
4        (SELECT MIN(order_date) FROM orders),
5        (SELECT MAX(order_date) FROM orders),
6        '1 day'::interval
7    )::date as date
8),
9daily_sales AS (
10    SELECT
11        order_date::date as date,
12        SUM(amount) as revenue
13    FROM orders
14    GROUP BY order_date::date
15)
16SELECT
17    ds.date,
18    COALESCE(d.revenue, 0) as revenue
19FROM date_series ds
20LEFT JOIN daily_sales d ON ds.date = d.date
21ORDER BY ds.date;

1# Analyze retail sales data:
2# 1. Load and prepare data
3# 2. Visualize trends and seasonality
4# 3. Decompose time series
5# 4. Calculate seasonal indices
6# 5. Build simple forecast
7# 6. Evaluate forecast accuracy
8
9# YOUR CODE HERE