wenmin-wu/tabular-cross-dataset-user-aggregation

Overview

Many marketplace/classifieds competitions ship auxiliary tables (train_active, test_active, periods_*) containing millions of extra listings that do not overlap with train/test on item_id but do share user_id. Ignoring them wastes a huge signal; joining them on item_id yields nothing. The right pattern is a two-step aggregation: first compute per-item statistics from the periods table (days-live, times-put-up), then mean-reduce across all of a user's items to produce user-level behavioral features, finally left-join on user_id. On Avito Demand Prediction this pattern was worth ~0.003 RMSE — about what a whole new model block usually buys.

Quick Start

import pandas as pd

all_samples = pd.concat([train, train_active, test, test_active]).drop_duplicates(['item_id'])
all_periods = pd.concat([train_periods, test_periods])
all_periods['days_up'] = (all_periods['date_to'] - all_periods['date_from']).dt.days

gp_df = all_periods.groupby('item_id').agg(
    days_up_sum=('days_up', 'sum'),
    times_put_up=('days_up', 'count'),
).reset_index()

item_user = all_periods.drop_duplicates('item_id')[['item_id']].merge(
    gp_df, on='item_id').merge(all_samples[['item_id', 'user_id']], on='item_id', how='left')

user_feats = item_user.groupby('user_id')[['days_up_sum', 'times_put_up']].mean().reset_index()
user_feats = user_feats.merge(
    all_samples.groupby('user_id').size().reset_index(name='n_user_items'),
    on='user_id', how='outer')

train = train.merge(user_feats, on='user_id', how='left').fillna(-1)
test  = test.merge(user_feats,  on='user_id', how='left').fillna(-1)

Workflow

1. Concat train + train_active + test + test_active and dedupe on item_id to build the universe of known listings
2. Compute per-item stats from the periods table (days_up_sum, times_put_up)
3. Merge item stats onto the universe to attach user_id
4. Group by user_id and mean-reduce — one row per user, columns are behavioral averages
5. Left-join the user table onto train/test on user_id, fill unseen users with sentinel -1

Key Decisions

• Mean vs median at user-level: mean captures typical behavior; median is more robust for heavy-tailed users but loses signal when most users have 1-2 listings.
• Include n_user_items: a user with 500 listings is a pro seller, which is itself predictive — don't let the mean wash that away.
• Fill missing with -1 not 0: GBDTs handle -1 as its own decision path; 0 conflates "unseen user" with "user who listed for 0 days".
• Dedupe on item_id at every concat: auxiliary tables overlap and double-counting silently inflates n_user_items.
• This is not leakage: user-level stats are computed over all listings including the user's test items, but they aggregate behavior rather than look up labels.

References

• Aggregated features & LightGBM