2025-05-31 - Dev Log

Reflections on day one

So after the fever dream that was working on this project non-stop for half a day yesterday and some much needed sleep. It occurred to me that including columns like u_diff, d_diff, t_diff, u_rate, and d_rate is pretty stupid and ruins the predictive capabilities of the model. I reached this conclusion intuitively when thinking about how I’d generate predictions for the next 30 days as a test.

Failure

If we had this information to feed the model for some future date… we wouldn’t need the model to predict t_rate in the first place… duh

I found out there is an actual name for this Leaking Future Information:

Info

At its core, leaking future information (often a type of data leakage or specifically target leakage) happens when your training data contains information that would not actually be available at the time you would use the model to make a prediction in a real-world scenario.

Think of it like giving a student the answers to a test before they take it. They’ll likely ace the test, but it doesn’t mean they’ve actually learned the material. Similarly, a model trained with leaked future information learns to “cheat” by using this privileged information, leading to an overly optimistic evaluation of its performance. When deployed, this future information isn’t available, and the model’s predictions become inaccurate.

So, put another way, including features like "u_diff", "d_diff", "t_diff", "u_rate", "d_rate", "t_rate" as inputs to predict t_rate doesn’t make sense for forecasting future values, since those are essentially the target or closely related to the target at the current time step. Using them as features leaks future information and leads to overly optimistic performance and unrealistic predictions. This is great learning experience at least haha

Revisiting Feature Engineering

Removing those features from the model at least makes it possible to use to generate predictions; but they’re not very good so lets see what we might add in the feature engineering step to improve things a bit.

Weird ideas off the top of my head:

  • Use some historical weather API + period_start to retrieve things like: high/low temp, was it raining that day.
    • On the flip side when making predictions, at least the raining feature, this would force me to rely on weather forecasts. For just temp you could just grab the average high and low from NOAA.gov
  • Streaming release dates for movies sounds interesting, but I don’t know that it’s useful at all in this case
  • I have a table with historical data on what the monthly data caps were. The cap amount definitely influenced my habits so there could be some predictive value. now that there isnt a cap anymore I’m not sure the best way to address that. Perhaps yet another feature “bandwidth_cap_enabled” ?
  • Same with historical link speeds, I don’t have this information readily available but I think I could compile it

Other typical types of features

  • Lagged versions of t_rate For example, t_rate_lag_1 (previous hour), t_rate_lag_24 (previous day same hour), etc. This gives the model temporal context without leaking future info. This will be somewhat tricky to apply to the entire dataset due to varying durations per row as you go further back in time. In the beginning I only polled the cable company API every 12 hours
  • Rolling statistics (mean, std, min, max) over windows like 1 day, 7 days, 30 days for t_rate and other relevant columns. These capture trends and seasonality. Similar issue as lags when applying to the entire dataset but since these are measures in days it should be easier
  • Time-Based Cyclical Features - Instead of just hour, day_of_week, month as integers, encode cyclical features using sine and cosine transforms to better capture periodicity:
    • hour_sin=sin(2π(hour/24))hour\_sin = sin(2π(hour/24))
    • hour_cos=cos(2π(hour/24))hour\_cos = cos(2π(hour/24))
    • dow_sin=sin(2π(dow/7))dow\_sin = sin(2π(dow/7))
    • dow_cos=cos(2π(dow/7))dow\_cos = cos(2π(dow/7))
    • month_sin=sin(2π(month/12))month\_sin = sin(2π(month/12))
    • month_cos=cos(2π(month/12))month\_cos = cos(2π(month/12))

Handling row duration variation, the lost episode of Schoolhouse Rock

Tldr

We can use pandas to tame the vagaries of the durations column in the dataset via resampling and interpolation

Todays episode is brought to you by the concept of Resampling, and how trivial pandas makes the task. Implementing the lagged columns would be a whole lot easier if each row was exactly 1 hour right? The formula for t_rate_lag_12H would just be df_resampled['t_rate'].shift(12) i.e. we just need to shift by a particular number of rows

But we live in the real world, and even my new implementation of the bandwidth scraper that runs hourly sometimes fails to run for various reasons. Meaning there are still sometimes rows that represent 2 hours, or even more. The beginning of the dataset (from 2015) is predominately comprised of 12 hour rows as well.

Specifically I’ve chosen to upsample my data to hourly intervals. So a 12 hour row would be “broken up” into 12 hourly rows and the data for these new rows is determined via interpolation and a statistical aggregation that is appropriate for a given column, In the case of t_rate I’m using mean()

It honestly seems too easy, how this is accomplished in pandas

    # Ensure period_start is datetime and set as index
    df['period_start'] = pd.to_datetime(df['period_start'])
    df = df.set_index('period_start').sort_index()

    # Resample to hourly frequency, aggregating numeric columns by mean
    df_resampled = df.resample('H').mean()

    # Interpolate missing values linearly
    df_resampled.interpolate(method='linear', inplace=True)

    # Reset index to bring period_start back as a column
    df_resampled = df_resampled.reset_index()

This code runs first during the featuring engineering step, since it’s going to be adding rows.

And here’s the code for adding some rolling aggregates and lagged t_rate features:

# Rolling statistics for t_rate (1 day, 7 days, 30 days windows in hours)
windows = [24, 24*7, 24*30]
for window in windows:
    df_resampled[f't_rate_roll_mean_{window}h'] = (
        df_resampled['t_rate'].rolling(window=window, min_periods=1).mean().shift(1)
    )
    df_resampled[f't_rate_roll_std_{window}h'] = (
        df_resampled['t_rate'].rolling(window=window, min_periods=1).std().shift(1)
    )

# Lagged features for t_rate (lags in hours)
lags = [1, 2, 3, 6, 12, 24]
for lag in lags:
    df_resampled[f't_rate_lag_{lag}h'] = df_resampled['t_rate'].shift(lag)

# Fill any remaining NaNs in rolling and lag features
lag_rolling_cols = [col for col in df_resampled.columns if ('roll' in col) or ('lag' in col)]
df_resampled[lag_rolling_cols] = df_resampled[lag_rolling_cols].fillna(method='bfill').fillna(method='ffill')

# Set duration to 3600 seconds (1 hour) since resampled
df_resampled['duration'] = 3600

Note

It makes sense to explicitly overwrite whatever is in the duration column now with 3600 since we know all rows are 1 hour. This has exactly the effect you’d expect on it’s importance in the models. (it drops to zero)

Revisiting the models with these new features

First of all, all of these extra features really jacked up how long the pipeline is taking to run. I’m going to have to get a GPU for the server or something… More likely, I’ll just start dual-booting Linux again on my desktop so I can run it there.

Anyway, I modified my pipeline to train both a Random Forest and an XGBoost model in separate steps both with time-series cross validation and random step hyperparameter tuning. Initial results with the new features were positive:

Random Forest

Best Random Forest MAE (CV): 10989.983241688746

Random Forest Mean Absolute Error (MAE):      244,558
Random Forest Root Mean Squared Error (RMSE): 453,500

Random Forest Feature Importance:
t_rate_lag_1h:         0.9908886403164731
t_rate_lag_3h:         0.005243315655416841
t_rate_lag_6h:         0.0011556614804315028
t_rate_lag_2h:         0.0011514639495538196
hour:                  0.0003142659936691411
hour_sin:              0.0002601824982685622
t_rate_lag_12h:        0.00019767107380326085
hour_cos:              0.00011224173256844106
t_rate_roll_mean_720h: 8.423271692137731e-05
t_rate_roll_mean_168h: 8.132533490943174e-05
t_rate_roll_std_24h:   8.064973293693442e-05
t_rate_roll_mean_24h:  7.576569709651099e-05
t_rate_roll_std_720h:  6.469362172411886e-05
t_rate_lag_24h:        5.969506960834044e-05
t_rate_roll_std_168h:  5.52113135219876e-05
day_of_year:           4.1101278529080366e-05
year:                  2.5934779827157025e-05
month_sin:             1.9123816692246613e-05
day_of_week_sin:       1.8943606931247723e-05
month_cos:             1.7844942158311875e-05
day_of_week:           1.5271180378822417e-05
day_of_week_cos:       1.1138510054441294e-05
month:                 1.0486940196981263e-05
cap_bytes:             9.384008308014037e-06
quarter:               3.984376073974322e-06
is_weekend:            1.770373946249246e-06
duration:              0.0

XGBoost

Best XGBoost MAE (CV): 14,061

XGBoost Mean Absolute Error (MAE):      251,350
XGBoost Root Mean Squared Error (RMSE): 447,516

XGBoost Feature importance (gain):
t_rate_lag_1h:    10,906,495,877,120
t_rate_lag_3h:        61,014,061,056
t_rate_lag_6h:        18,963,990,528
t_rate_lag_2h:        10,087,224,320
hour_cos:              9,805,532,160
hour:                  8,731,028,480
hour_sin:              6,838,880,768
day_of_week_sin:       4,165,131,776
month:                 3,869,282,560
t_rate_roll_std_24h:   3,792,819,712
year:                  3,247,406,336
t_rate_roll_mean_720h: 3,210,926,080
t_rate_lag_12h:        3,168,783,104
t_rate_roll_mean_168h: 2,884,528,128
cap_bytes:             2,769,338,112
day_of_year:           2,563,918,592
month_sin:             2,504,319,232
t_rate_roll_mean_24h:  2,385,282,048
t_rate_roll_std_720h:  2,146,098,432
month_cos:             2,037,157,504
day_of_week_cos:       1,950,766,080
t_rate_lag_24h:        1,841,494,272
t_rate_roll_std_168h:  1,792,660,352
day_of_week:           1,447,310,208

I think it’s interesting the subtle differences in important the models each assigned to some of the less important features like day_of_week_sin but it’s very clear that the new lagged features are dominating. The rolling aggregates didn’t have as profound of an effect, though the rolling 24hour standard deviation looks promising. I think it’s also time to prune the feature list a bit:

You are the weakest link, good bye

drop_features = [
    't_rate_lag_12h', 't_rate_lag_24h',
    't_rate_roll_mean_168h', 't_rate_roll_mean_720h',
    't_rate_roll_std_168h', 't_rate_roll_std_720h',
    'duration', 'is_weekend', 'quarter', 'cap_bytes', 'day_of_year', 'year',
    'month_sin', 'month_cos', 'day_of_week', 'day_of_week_cos'
]
features = [col for col in df.columns if col not in drop_features and col != target_col and col not in ['period_start', 'period_end', 'u_diff', 'd_diff', 't_diff', 'u_rate', 'd_rate', 't_rate']]

hour, hour_sin, hour_cos, day_of_week_sin, and month seem to have small but consistent importance. Keeping these for capturing seasonality.

Somewhat disappointingly the data cap in bytes that I added in became almost completely irrelevant and the though just occurred to me as I write this that it maybe got corrupted in my resampling step. The cap table is small, perhaps it could be stored in a dict and the cap values added in via pandas after the resampling step.

Failure is unique among sin, in that we can turn it into a virtue when it drives us to succeed.

The living diminish, but the machine endures...

Wesley Ray · blog · git · resume