[Python] Time Series Forecasting: How to Predict Future Values Using Features Calculated Up to...

Question:


I'm working on a time series forecasting project where I need to predict weekly sales (kg_ordered_sum) for various customer-product combinations. The data is aggregated weekly (W-MON) per customer_name and prod_family, spanning from January 1, 2021, to October 15, 2024.

Objective:


Predict kg_ordered_sum at time t for each customer_name and prod_family combination, using features calculated up to time t-1.

Approach:


Feature Calculation: All features are calculated based on data up to time t-1 to prevent data leakage.

• Features include:
  • Cumulative sums (e.g., cumulative kg_ordered_sum)
  • Lagged variables (e.g., kg_ordered_sum in previous weeks)
  • Rolling statistics
  • Purchase frequencies
  • Weeks since first purchase

Modeling: I'm using machine learning models like XGBoost for forecasting.

Data Preparation Steps:


Load and Preprocess Data:

df = load_and_preprocess_data(data_path)
weekly_sales = preprocess_raw_data(df)
weekly_sales['is_future'] = 0 # All data initially marked as historical


Define Training and Testing Periods: Training Data: Up to September 30, 2024 (2024-09-30). Test Data: From October 7, 2024 (2024-10-07), onwards. Test data means the future I want to predict, not the usual train and test datasets where I train and evaluate my model. I did that already, with good r2score results (0.86). Now I want to simulate real scenario where I actually need to make predictions for next week. Create Future DataFrame for Prediction:

# Define cutoff date
cutoff_date = pd.to_datetime('2024-09-30')

# Historical data up to cutoff date
history_upto_cutoff = weekly_sales[weekly_sales['date'] <= cutoff_date].copy()

# Future dates for prediction
future_dates = pd.date_range(start='2024-10-07', periods=1, freq='W-MON')

# Get unique customer-product combinations
customer_prod_combinations = history_upto_cutoff[['customer_name', 'prod_family']].drop_duplicates()

# Create future data
future_data = customer_prod_combinations.assign(key=1).merge(
pd.DataFrame({'date': future_dates, 'key': 1}),
on='key'
).drop('key', axis=1)
future_data['kg_ordered_sum'] = 0 # Future actual sales are unknown
future_data['nettosales_sum'] = 0
future_data['is_future'] = 1


Assign Last Known Historical Values to Future Data: To provide context for the model, I assign the last known historical values to the future data for lagged features.

# Get last known values
last_known_values = history_upto_cutoff.groupby(['customer_name', 'prod_family']).agg({
'kg_ordered_sum': 'last',
'nettosales_sum': 'last',
'first_purchase_date': 'first',
'cumulative_kg_ordered': 'last',
'order_count': 'last',
'avg_order_size_overall': 'last',
}).reset_index()
last_known_values.rename(columns={
'kg_ordered_sum': 'last_kg_ordered_sum',
'nettosales_sum': 'last_nettosales_sum',
}, inplace=True)

# Merge into future_data
future_data = future_data.merge(last_known_values, on=['customer_name', 'prod_family'], how='left')

# Assign historical values
future_data['kg_ordered_sum_shifted'] = future_data['last_kg_ordered_sum'].fillna(0)
future_data['nettosales_sum_shifted'] = future_data['last_nettosales_sum'].fillna(0)
future_data['first_purchase_date'] = future_data['first_purchase_date'].fillna(future_data['date'])
future_data['cumulative_kg_ordered'] = future_data['cumulative_kg_ordered'].fillna(0)
future_data['order_count'] = future_data['order_count'].fillna(0).astype(int)
future_data['avg_order_size_overall'] = future_data['avg_order_size_overall'].fillna(0)


Combine Historical and Future Data:

combined_data = pd.concat([history_upto_cutoff, future_data], ignore_index=True)
combined_data = compute_all_features(combined_data)


Issue:

After adjusting my feature calculations to prevent data leakage by excluding future data (i.e., only using data up to t-1), my model's performance has deteriorated significantly, resulting in a negative R² score.

Specific Concerns: Feature Calculation in Future Periods:

• 
  When computing cumulative features like cumulative_kg_ordered, should I exclude future periods (where is_future == 1), or should I include the historical values assigned to future data in these calculations?
  
  
• 
  For features like purchase_frequency and weeks_since_first_purchase that depend on first_purchase_date and order_count, values become zero or incorrect in future periods due to how they're calculated.
  

My Questions:

1. 
   How should I handle feature calculations for future periods to prevent data leakage while providing the model with sufficient historical context?
   
   
2. 
   Is it acceptable to use historical values assigned to future data in feature calculations, or should I continue excluding future periods in cumulative sums and counts?
   
   
3. 
   Are there best practices for handling cumulative and lagged features in time series forecasting to balance between data leakage prevention and model performance?
   

Any guidance, suggestions, or best practices from those experienced in time series forecasting and feature engineering would be greatly appreciated!

Continue reading...