<jupyter_start><jupyter_text><NAME> - 10th Place Solution - Meli Data Challenge 2021First of all I'd like to thank <NAME> for this great contest. I'd also like to thank <NAME> for sharing so much of his approach which gave me a huge head start: most of my solution got the ideas (and implementation) from him. With a litte feature engineering + ensemble with non-ML predictions + post processing I was able to get to the 10th place. Business UnderstandingGiven the sales of 600k+ products between february and march of 2021, we were asked to predict the inventory days of these products in april of 2021. The items were sold in three countries: Argentina, Brazil and Mexico. Moreover, the predictions should be presented in terms of the probability of selling out in the 30 days of April. It's given that all products are sold within 30 days.The task as stated in the Challenge:"Your task is to predict how long it will take for the inventory of a certain item to be sold completely. In inventory management theory this concept is known as inventory days.In the evaluation set you will be given the item target stock, and you will have to provide a prediction for the number of days it will take to run out. Possible values range from 1 to 30. Rather than giving a point estimate, you are expected to provide a score for each the possible outcomes.To put it simply, you need to answer the following question:'What are the odds that the target stock will be sold out in one day?', 'What about in two days?' and so on until day 30." Data UnderstandingWe were given three files:* The first file has the information about the sales in Februrary and March of 2021 of 600k+ products, with features regarding the products in each day: price, minutes active in the last 24 hours and number os itens sold that day* The second had the metadata of the products* Finally, the test file has the skus (unique id for each product) with the inventory to predict the inventory days ModelingI used a simple regressor to find how many items were sold in the last day of prediction. With this information, we can calculate the inventory days: items_sold_prediction / inventory_in_testThe validation were the mean RDS between the traning in february and validating in march and vice-versa.Besides the creation of features that helped improving the solution, the different approaches I did different from other solutions were:* I've created an ensemble model with XGBRegressor and a naive predictor (average sales)* I've done post processing setting 30 inventory days to the skus which stock were too big considering the sales on the previous monthsI tryed different distributions, but in the end, tweedie was the best one to calculate the submissions.<jupyter_code># Import the libraries 
# numpy and pandas, because... ah, you know what for :)
# gp_minimize to tune the XGB hyperparameteres
# utils has functions to calculate the tweedie distribution and to read the json file into a list (thanks <NAME> and Meli)

import numpy as np
import pandas as pd
from skopt import gp_minimize
from xgboost import XGBRegressor
import tweedie
import os

import utils
import wget

# Create data folder if it does not exist
if not os.path.isdir('./data'):
    os.mkdir('./data')

# Download the files from Meli Data Challenge website    
train_data_url = 'https://meli-data-challenge.s3.amazonaws.com/2021/train_data.parquet'
test_data_url = 'https://meli-data-challenge.s3.amazonaws.com/2021/test_data.csv'
details_url = 'https://meli-data-challenge.s3.amazonaws.com/2021/items_static_metadata_full.jl'

urls = [train_data_url, test_data_url, details_url]

for url in urls:
    file_name = os.path.split(url)[1]
    if not os.path.isfile(f'./data/{file_name}'):
        wget.download(url, f'./data/{file_name}')
    
# Load the train data
train = pd.read_parquet('./data/train_data.parquet')
# Take a look
train.head()
# Number of products
f'There are {len(train["sku"].unique())} skus'
# Load the metadata
list_details = utils.json_to_list('./data/items_static_metadata_full.jl')
df_details = pd.DataFrame(data=list_details)
# Merge the product details into the main train dataframe
train = train.merge(df_details, on='sku')
train.head()
# Check train shape
train.shape
# Check data types
train.dtypes
# Let's fix the date
train['date'] = pd.to_datetime(train['date'])
# Check for missing data
train.isna().sum()<jupyter_output><empty_output><jupyter_text>Feature engineeringI created new features to play with XGBRegressor:* The presence of product family (not that good since there are a lot of missing data)* I wish I had more time to try embeddings with the description (thank you boss!!), so I created a dumb feature regarding the word count* Two features regarding availability of the product: if it was all day available (available_24h) and if it showed up a least 1 minute in the last 24h (available)* Two date features: weekday and is_weekend<jupyter_code>train['product_family_present'] = (train['product_family_id'].notnull()).map({True: 1, False: 0})
train['desc_word_count'] = train['item_title'].str.lower().str.split().apply(lambda l: len(l))

train['available_24h'] = (train['minutes_active'] == 1440).map({True: 1, False: 0})
train['available'] = (train['minutes_active'] > 0).map({True: 1, False: 0})

train['weekday'] = train['date'].dt.weekday
train['is_weekend'] = (train['weekday'] >= 5).map({True: 1, False: 0})
# Let's take a look how it went
train.head()
train = train.drop(['item_title', 'product_family_id'], axis=1)
# For evaluation, I chose to train in feb and validate march and vice-versa. Let's create the folds
train['fold'] = train['date'].dt.month
test = pd.read_csv("./data/test_data.csv", index_col=0).squeeze()
cats = ['item_domain_id', 'currency',
        'listing_type', 'shipping_logistic_type',
        'shipping_payment', 'site_id',
        'available_24h', 'available', 'weekday', 'is_weekend']

from category_encoders import OrdinalEncoder

enc = OrdinalEncoder(cats)
train = enc.fit_transform(train)
def gen_tr_ts():
    for fold in [2, 3]:
        ts = train[train['fold'] != fold]['date'].max()
        ts = train[(train['fold'] != fold) & (train['date'] == ts)].index
        yield train.index[train['fold'] == fold], ts, fold
def tune(params):
    print(params)
    features = ["current_price", "minutes_active", 'product_family_present', 'desc_word_count'] + cats

    mean_rps = 0.
    for tr, ts, fold in gen_tr_ts():
        X = train[features]
        y = train['sold_quantity']

        Xtr = X.iloc[tr]
        ytr = y.iloc[tr]
        Xval = X.iloc[ts]
        yval = y.iloc[ts]

        mdl = XGBRegressor(n_estimators=100, learning_rate=params[0],
                           max_depth=params[1],
                           subsample=params[2],
                           colsample_bytree=params[3],
                           tweedie_variance_power=params[4],
                           min_child_weight=params[5],
                           random_state=0, objective="reg:tweedie",
                           base_score=1e-3,
                           tree_method='gpu_hist')
        mdl.fit(Xtr, ytr)
        p = mdl.predict(Xval)

        ## EVAL
        pp = train[train['fold'] != fold][['sku', 'date', 'sold_quantity']]
        pp['stock'] = pp['sku'].map(test)
        pp = pp.sort_values(["sku", "date"])
        pp['cumulative_y'] = pp.groupby("sku")['sold_quantity'].cumsum()

        pp = pp.dropna(subset=['stock'])
        pp['stockout_y'] = pp['cumulative_y'] >= pp['stock']

        first_so_y = pp[pp['stockout_y']].groupby("sku").first()
        days_to_so_y = (first_so_y["date"] - pp["date"].min()) / np.timedelta64(1, 'D')
        days_to_so_y = days_to_so_y.reindex(pp['sku'].unique()).fillna(30.).clip(1, 30)

        ppp = train.iloc[ts][['sku']]
        # p[~np.isfinite(p)] = 17.
        ppp['p'] = p
        ppp['stock'] = ppp['sku'].map(test)
        ppp = ppp.dropna(subset=['stock'])
        ppp['days_to_so'] = (ppp['stock'] / ppp['p']).astype(int).fillna(30.).clip(1, 30)
        days_to_so_p = ppp[['sku', 'days_to_so']].set_index("sku").squeeze().reindex(days_to_so_y.index)

        days_to_so_p2 = utils.pred_list_to_tweedie(days_to_so_p, phi=2, p=1.5)

        rps = utils.rps(days_to_so_y, days_to_so_p2, probs=True)
        mean_rps += rps
        print(rps)
    return mean_rps / 2


space = [(1e-3, 1e-1, 'log-uniform'),
         (1, 10),
         (0.05, 0.95),
         (0.05, 0.95),
         (1.0, 1.99),
         (1, 300)]
res = gp_minimize(tune, space, random_state=1, verbose=1)
features = ["current_price", "minutes_active", 'product_family_present', 'desc_word_count'] + cats

# Some tests with parameters
# params = [0.036887776337184194, 3, 0.2612410543047176, 0.72389895876477, 1.696027970970498, 93]
#params = [0.036866248434672594, 5, 0.08075418006725515, 0.6116269878296181, 1.654029372935623, 90]
params = [0.014749532034944692, 6, 0.6025444860164502, 0.9108820910034702, 1.2583691898194014, 70]

mdl = XGBRegressor(n_estimators=100, learning_rate=params[0],
                   max_depth=params[1],
                   subsample=params[2],
                   colsample_bytree=params[3],
                   tweedie_variance_power=params[4],
                   min_child_weight=params[5],
                   random_state=0, objective="reg:tweedie",
                   base_score=1e-3,
                   tree_method='gpu_hist')

mdl.fit(train[features], train['sold_quantity'])
# Predict the sales on the last day
test_df = train[train['date'] == "2021-03-31"]
test_df = test_df[test_df['sku'].isin(test.index)]

p = mdl.predict(test_df[features])
# With the prediction for one day, we must calculate how many days the sock will end in 30 days (even the same variable names, thanks a lot <NAME> <3)
spp = test_df[['sku']].copy()
spp['p'] = p
spp['stock'] = spp['sku'].map(test)
spp['days_to_so'] = (spp['stock'] / spp['p']).fillna(30.).clip(1, 30).astype(int)
# A little "smart dumbness". If the model is too far from the mean sales, let's bring the predictions back
# This should be our baseline, but it showed a good ensemble with the XGBRegressor
df_dumb_prediction_by_sku = train.groupby('sku')['sold_quantity'].mean()
# Calculate how many days the stock would end with the mean sales
spp['mean_p'] = spp['sku'].map(df_dumb_prediction_by_sku)
spp['mean_days_to_so'] = (spp['stock'] / spp['mean_p']).fillna(30.).clip(1, 30).astype(int)
# Creating a simple mean ensemble
spp['final_days_to_so'] = ((spp['days_to_so'] + spp['mean_days_to_so']) / 2).fillna(30.).clip(1, 30).astype(int)
# Storing the predictions of best submission without post processing
spp[['final_days_to_so']].to_parquet('./models/predictions_best_submission_11.parquet')
# Now, for the first post processing, we compare the predictions with plain logic (sometimes machine are just dumb)
# If the stock is too big based on the items sold in the previous months, it wont sell until the last day (it'll take 30 days)
df_sales_by_sku = train.groupby('sku').sum('sold_quantity')

df_sales_by_sku.drop(['current_price', 'minutes_active'], axis=1, inplace=True)

df_sales_by_sku = df_sales_by_sku.merge(test, on='sku')

df_sales_by_sku['mean_sales_by_day'] = df_sales_by_sku['sold_quantity'] / 60
df_sales_by_sku['mean_sales_by_month'] = df_sales_by_sku['sold_quantity'] / 2
df_sales_by_sku['diff_sold_stock'] = df_sales_by_sku['sold_quantity'] - df_sales_by_sku['target_stock']

df_predictions = pd.read_parquet('./models/predictions_final_day_xgb.parquet')
df_predictions.reset_index(level=0, inplace=True)

df_predictions.columns = ['sku', 'predictions']

# Now we can set the inventory days to 30 to all skus with stock bigger than sales in the previous months
skus_in_predictions_with_30 = set(df_predictions.loc[df_predictions['predictions'] == 30, 'sku'].values)
skus_with_inventory_bigger_than_sales = set(df_sales_by_sku.loc[df_sales_by_sku['diff_sold_stock'] <= 0, :].index)

df_predictions.loc[df_predictions['sku'].isin(skus_with_inventory_bigger_than_sales), 'predictions'] = 30
# create a intermediate prediction (i've tested it to see if the post processing were effective and they were)
prob_array = utils.pred_list_to_tweedie(df_predictions['predictions'].values, phi=2., p=1.5)
pd.set_option("display.max_columns", 31)
pd.DataFrame(prob_array).round(4).to_csv("./submissions/2021_08_22_really_naive.csv.gz", header=False, index=False,
                                         compression="gzip")
# Now the last post processing, based on Mario Filho's approach once again: predict if the sku is available
# and start the distribution only when the product is active (by the prediction)
# The main idea is to use a regressor to predict the day the product will start being active (visible),
# a product that is not active will not sell until the day it is active
import xgboost as xgb
from sklearn.metrics import mean_squared_error


test = pd.read_csv("./data/test_data.csv").set_index("sku").squeeze()
train = pd.read_parquet("./data/0.parquet")
train['date'] = pd.to_datetime(train['date'])
cats = ['item_domain_id', 'currency', 'listing_type', 'shipping_logistic_type', 'shipping_payment', 'site_id']
for cat in cats:
    train[cat] = train[cat].astype("category").cat.codes

train.loc[train["minutes_active"] == 0, "active"] = 0
train["active"] = train["active"].fillna(1)

act = train[train['active'] == 1][['sku', 'date']].sort_values("date")
act['active_date'] = act['date']
inact = train[train['active'] == 0][['sku', 'date', 'shipping_logistic_type', 'shipping_payment',
                                     'listing_type', 'currency', 'current_price', 'item_domain_id',
                                     'site_id']].sort_values("date")
all_ = pd.merge_asof(inact, act, on=['date'], direction='forward', by=['sku']).dropna(subset=['active_date'])
all_['days_to_active'] = (all_['active_date'] - all_['date']) / np.timedelta64(1, 'D')
all_['days_since_inactive'] = (all_['date'] - all_.groupby("sku")["date"].transform("min")) / np.timedelta64(1, 'D')
y = all_['days_to_active'].copy()
all_.corr(method='spearman')

Xtr = all_.loc[all_['date'] < "2021-03-01", ['days_since_inactive', 'current_price'] + cats]
Xval = all_.loc[all_['date'] >= "2021-03-01", ['days_since_inactive', 'current_price'] + cats]

ytr = y[all_['date'] < "2021-03-01"]
yval = y[all_['date'] >= "2021-03-01"]

mdl = xgb.XGBRegressor(n_estimators=100, max_depth=3, learning_rate=0.1, random_state=0, n_jobs=6, tree_method='hist')
mdl.fit(Xtr, ytr)
p = mdl.predict(Xval)
np.sqrt(mean_squared_error(yval, p))

all_t = train[train['active'] == 0].copy()

all_t['days_since_inactive'] = (all_t['date'] - all_t.groupby("sku")["date"].transform("min")) / np.timedelta64(1, 'D')
all_t = all_t.groupby("sku").last()
all_t = all_t[all_t['date'] == "2021-03-31"].copy()

X = all_.loc[:, ['days_since_inactive', 'current_price'] + cats]
Xt = all_t.loc[:, ['days_since_inactive', 'current_price'] + cats]

mdl = xgb.XGBRegressor(n_estimators=100, max_depth=3, learning_rate=0.1, random_state=0, n_jobs=6, tree_method='hist')
mdl.fit(X, y)
p = mdl.predict(Xt)

p2 = pd.Series(p.round(), index=Xt.index).reindex(test.index).dropna().astype(int)

# reading the post processed submission
sub = pd.read_csv("./submissions/2021_08_22_really_naive.csv.gz", header=None)  # 4.23
sub_ = sub.copy()
sub_.index = test.index

for sku in p2.index:
    s = sub_.loc[sku].copy()
    days = p2.loc[sku]
    s.iloc[:days] = s.iloc[:days] * 0.5
    s = s / s.sum()
    sub_.loc[sku, :] = s

# Best score LB 4.20
sub_.round(4).to_csv("./submissions/2021_08_22_really_naive_v2.csv.gz", header=False, index=False, compression="gzip")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13333333333333333, 'fraction_numerical': 0.034066985645933016, 'mean_word_length': 4.285232636547539, 'pattern_counts': {'":': 0, '<': 18, '<?xml version=': 0, '>': 19, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '340078', 'license': 'MIT', 'n_tokens_mistral': 5636, 'n_tokens_neox': 5182, 'n_words': 1463, 'path': '10th Place Meli Data Challenge Solution.ipynb', 'repo_name': 'leonsolon/meli-data-challente-2021', 'size': '15701'}