<jupyter_start><jupyter_text>Items (SKU metadata)<jupyter_code>%%time

items = pd.read_json('items_static_metadata_full.jl', lines=True)
items['item_domain_id'] = np.where(items['sku'].isin([459892]), 'MLB-HEADSET_TELEPHONES', items['item_domain_id'])
items['item_domain_id'] = np.where(items['sku'].isin([553503]), 'MLB-HAIR_STRAIGHTENERS', items['item_domain_id'])
items['item_domain_id'] = np.where(items['sku'].isin([454273]), 'MLB-HEADPHONES', items['item_domain_id'])
items
items[items['item_domain_id'].isnull()]
items.isnull().sum()
items['site_id'].value_counts()<jupyter_output><empty_output><jupyter_text>Test<jupyter_code>%%time

test = pd.read_csv('test_data.csv').merge(items, how='left')
test
test['target_stock'].mean()
target_stock_by_site_id = test.groupby('site_id').agg(
    mean_target=pd.NamedAgg('target_stock', 'mean'),
    sd_target=pd.NamedAgg('target_stock', 'std')
)
target_stock_by_site_id<jupyter_output><empty_output><jupyter_text>Preprocessing<jupyter_code>%%time

train = pd.read_parquet('train_data.parquet').merge(items, how='left')
train['date'] = pd.to_datetime(train['date'])
train
train['item_domain_id'].value_counts()<jupyter_output><empty_output><jupyter_text>Temporal split<jupyter_code>%%time

data_train = train[train['date'] <= '2021-03-01'].copy()

data_train.loc[:, 'is_active'] = np.where(data_train['minutes_active'] > 0, 1, 0) 
data_train.loc[:, 'is_inactive'] = np.where(data_train['minutes_active'] == 0, 1, 0) 

# use money as pct change instead of absolute
data_train['current_price'] = data_train.groupby('sku')['current_price'].pct_change()

data_train['week'] = data_train['date'].dt.isocalendar().week.astype(int)
data_train['week'] = data_train['week'] - data_train['week'].min() + 1

data_train['has_zero_sold'] = np.where(data_train['sold_quantity'] == 0, 1, 0)

data_train
%%time

data_val = train[train['date'] > '2021-03-01'].copy()

data_val.loc[:, 'is_active'] = np.where(data_val['minutes_active'] > 0, 1, 0) 
data_val.loc[:, 'is_inactive'] = np.where(data_val['minutes_active'] == 0, 1, 0)

# use money as pct change instead of absolute
data_val['current_price'] = data_val.groupby('sku')['current_price'].pct_change()

data_val['week'] = data_val['date'].dt.isocalendar().week.astype(int)
data_val['week'] = data_val['week'] - data_val['week'].min() + 1

data_val['has_zero_sold'] = np.where(data_val['sold_quantity'] == 0, 1, 0)

data_val<jupyter_output>CPU times: user 12 s, sys: 6.38 s, total: 18.4 s
Wall time: 20.5 s<jupyter_text>Mappings<jupyter_code>sku2item_domain_id = {x[0]: x[1] for x in items[['sku', 'item_domain_id']].values}
sku2site_id = {x[0]: x[1] for x in items[['sku', 'site_id']].values}
sku2item_title = {x[0]: x[1] for x in items[['sku', 'item_title']].values}

site_id2int = {x: i for i, x in enumerate(items['site_id'].unique(), start=1)}
item_domain_id2int = {x: i for i, x in enumerate(items['item_domain_id'].unique(), start=1)}
def build_target_stock_and_inventory_days(dataset, days):
    '''
    Provided by the workshop (with some minor changes from me): https://www.youtube.com/watch?v=WqIXnWHyMVA
    '''
    
    np.random.seed(42)
    
    print('Sorting records...')
    temp_df = dataset[['sku', 'date', 'sold_quantity']].sort_values(['sku', 'date'])
    
    print('Grouping quantity...')
    temp_dict = temp_df.groupby('sku').agg({'sold_quantity': lambda x: [i for i in x]})['sold_quantity'].to_dict()
    # {0: [0, 0, 0, 1, 1, 0, 0, 3, ..., 0]}
    
    result = []
    for idx, list_quantity in temp_dict.items():
        cumsum = np.array(list_quantity).cumsum()
        # [0, 0, 0, 1, 2, 2, 2, 5, ..., k]
        
        stock_target = 0
        if cumsum[-1] > 0 and len(cumsum) == days:
            
            # choose a random target different from 0
            while stock_target == 0:
                stock_target = np.random.choice(cumsum)
                
            # get the first day with this amount of sales
            day_to_stockout = np.argwhere(cumsum == stock_target).min() + 1

            # add to list
            result.append({'sku': idx,
                           'item_domain_id': sku2item_domain_id[idx],
                           'site_id': site_id2int[sku2site_id[idx]],
                           'item_title': sku2item_title[idx],
                           'target_stock': stock_target,
                           'inventory_days': day_to_stockout})
            
    return result
%%time

val_dataset = pd.DataFrame(build_target_stock_and_inventory_days(data_val, days=30))<jupyter_output>Sorting records...
Grouping quantity...
CPU times: user 37.9 s, sys: 4.23 s, total: 42.1 s
Wall time: 43.6 s<jupyter_text>Feature Engineering<jupyter_code>def long_to_wide(df: pd.DataFrame, is_val: bool, features: list, fill_na: bool):
    '''
    Convert dataframe from long to wide, using one or more features.
    '''
    
    df_long = df.copy().set_index(['sku', 'date'])
    df_wide = df_long[features].unstack().droplevel(0, axis=1)
         
    days = 30 if is_val else 29
    idxs = list(range(1, days + 1)) * len(features)
    cols = np.repeat(features, days)  
    
    # val goes from [1, 30] and train goes from [1, 29], so train needs +1
    if is_val:
        df_wide.columns = [f'{x[0]}_{x[1]}' for x in zip(cols, idxs)]
        
        # delete first day for each feature if is validation data
        df_wide = df_wide[[x for x in df_wide.columns if x.split('_')[-1] != '1']]
        
    else:
        df_wide.columns = [f'{x[0]}_{x[1] + 1}' for x in zip(cols, idxs)]
        
    if fill_na:
        # impute NA based on each feature
        for col in df_wide.columns:
            if 'sold_quantity' in col or 'minutes_active' in col or 'current_price' in col:
                na_replace = 0
            elif 'listing_type' in col or 'shipping_logistic_type' in col or 'shipping_payment' in col:
                na_replace = 0

            df_wide[col] = df_wide[col].fillna(na_replace)
        
    return df_wide
# check categories and try to incorporate rolling windows for categorical features
for col in ['listing_type', 'shipping_logistic_type', 'shipping_payment']:
    print(col)
    print(train[col].unique())
    print()
%%time

roll_feats = ['sold_quantity', 'minutes_active', 'current_price', 'has_zero_sold',
              'listing_type', 'shipping_logistic_type', 'shipping_payment']

data_train_wide = long_to_wide(
    data_train,
    is_val=False,
    features=roll_feats,
    fill_na=False
)

data_train_wide_filled_na = long_to_wide(
    data_train,
    is_val=False,
    features=roll_feats,
    fill_na=True
)

data_train_wide.shape, data_train_wide_filled_na.shape
# check the data format
data_train_wide.head(10)
%%time

data_val_wide = long_to_wide(
    data_val,
    is_val=True,
    features=roll_feats,
    fill_na=False
)

data_val_wide_filled_na = long_to_wide(
    data_val,
    is_val=True,
    features=roll_feats,
    fill_na=True
)

data_val_wide.shape, data_val_wide_filled_na.shape
# testing some windows [(end, start), (end, start), ...]
w = 1
n_windows = 29 - w
[(30 - i, 30 - i - w) for i in list(range(n_windows))]
# testing some windows [(end, start), (end, start), ...]
w = 5
n_windows = 29 - w
[(30 - i, 30 - i - w) for i in list(range(n_windows))]
# testing some windows [(end, start), (end, start), ...]
w = 10
n_windows = 29 - w
[(30 - i, 30 - i - w) for i in list(range(n_windows))]<jupyter_output><empty_output><jupyter_text>LOFO (Leave One Feature Out)After applying LOFO when building the LGBM model, I reduced from 106 to 40 features and still improved the CV.<jupyter_code>selected_features_lofo = [
    # lag for sold quantity
    'sold_quantity_17', 'sold_quantity_18', 'sold_quantity_19',
    'sold_quantity_20', 'sold_quantity_21', 'sold_quantity_25',
    'sold_quantity_26', 'sold_quantity_27', 'sold_quantity_28',
    'sold_quantity_29', 'sold_quantity_30',
    
    # lag for minutes active
    'minutes_active_29', 'minutes_active_30',
    
    # rolling mean sold quantity
    'roll_mean_sold_30_11', 'roll_mean_sold_29_10', 'roll_mean_sold_28_9',
    'roll_mean_sold_27_8', 'roll_mean_sold_26_7', 'roll_mean_sold_25_6',
    'roll_mean_sold_24_5',
    
    # rolling mean minutes active
    'roll_mean_minutes_30_11', 'roll_mean_minutes_29_10', 'roll_mean_minutes_26_7',
    'roll_mean_minutes_25_6', 'roll_mean_minutes_24_5',
    
    # rolling mean pct change of price
    'roll_mean_price_30_11', 'roll_mean_price_29_10', 'roll_mean_price_27_8',
    'roll_mean_price_25_6', 'roll_mean_price_24_5',
    
    # rolling sum (count) of days with zero sold
    'roll_sum_zero_sold_30_11', 'roll_sum_zero_sold_24_5',
    
    # rolling sum (count) across different categorical features
    'roll_sum_shipping_logistic_type_fulfillment_30_11',
    'roll_sum_shipping_logistic_type_cross_docking_30_11',
    'roll_sum_shipping_logistic_type_fulfillment_28_9',
    'roll_sum_shipping_logistic_type_fulfillment_26_7',
    'roll_sum_shipping_logistic_type_fulfillment_25_6',
    'roll_sum_shipping_logistic_type_fulfillment_24_5',
    'roll_sum_shipping_payment_free_shipping_28_9',
    
    # target stock (this feature was provided in the test set as well)
    'target_stock'
]

print(len(selected_features_lofo))
def create_train_val_test(
        w,
        n_windows,
        use_undersampling: bool,
        use_standardize: bool,
        use_minmax: bool
    ):
    '''
    Create datasets using rolling windows features, based on window size "w".    
    If "n_windows" is passed, keep only the last "n_windows".
    '''
    
    # w must be in [1, 27]
    assert w in range(1, 28)
        
    if n_windows == None:
        n_windows = 29 - w
    
    print()
    print(f'------ w = {w} ------')
    print(f'------ n_windows = {n_windows} ------')
    
    
    if use_standardize or use_minmax:
        df_roll_train = data_train_wide_filled_na.copy()
        df_roll_val = data_val_wide_filled_na.copy()
    else:
        df_roll_train = data_train_wide.copy()
        df_roll_val = data_val_wide.copy()
      
    # rolling window for w days
    for end, start in [(30 - i, 30 - i - w) for i in list(range(n_windows))]:
        for feat in roll_feats:
            print(end, start, feat)
            
            cols = [f'{feat}_{i}' for i in list(range(start, end + 1))]
            
            if feat == 'sold_quantity':
                df_roll_train[f'roll_mean_sold_{end}_{start}'] = df_roll_train[cols].mean(axis=1)
                df_roll_val[f'roll_mean_sold_{end}_{start}'] = df_roll_val[cols].mean(axis=1)
                
            if feat == 'minutes_active':
                df_roll_train[f'roll_mean_minutes_{end}_{start}'] = df_roll_train[cols].mean(axis=1)
                df_roll_val[f'roll_mean_minutes_{end}_{start}'] = df_roll_val[cols].mean(axis=1)

            elif feat == 'current_price':
                df_roll_train[f'roll_mean_price_{end}_{start}'] = df_roll_train[cols].mean(axis=1)
                df_roll_val[f'roll_mean_price_{end}_{start}'] = df_roll_val[cols].mean(axis=1)

            elif feat == 'has_zero_sold':
                df_roll_train[f'roll_sum_zero_sold_{end}_{start}'] = df_roll_train[cols].sum(axis=1)
                df_roll_val[f'roll_sum_zero_sold_{end}_{start}'] = df_roll_val[cols].sum(axis=1)
                
            # categorical features
            elif feat == 'listing_type':
                for lvl in ['classic', 'premium']:
                    df_roll_train[f'roll_sum_{feat}_{lvl}_{end}_{start}'] = (df_roll_train[cols] == lvl).sum(axis=1)
                    df_roll_val[f'roll_sum_{feat}_{lvl}_{end}_{start}'] = (df_roll_val[cols] == lvl).sum(axis=1)
                
            elif feat == 'shipping_logistic_type':
                for lvl in ['fulfillment', 'cross_docking', 'drop_off']:
                    df_roll_train[f'roll_sum_{feat}_{lvl}_{end}_{start}'] = (df_roll_train[cols] == lvl).sum(axis=1)
                    df_roll_val[f'roll_sum_{feat}_{lvl}_{end}_{start}'] = (df_roll_val[cols] == lvl).sum(axis=1)
            
            elif feat == 'shipping_payment':
                for lvl in ['free_shipping', 'paid_shipping']:
                    df_roll_train[f'roll_sum_{feat}_{lvl}_{end}_{start}'] = (df_roll_train[cols] == lvl).sum(axis=1)
                    df_roll_val[f'roll_sum_{feat}_{lvl}_{end}_{start}'] = (df_roll_val[cols] == lvl).sum(axis=1)
                
                
    # bind new features
    val_dataset_ok = val_dataset.set_index('sku').join(df_roll_train)
    sub_dataset_ok = test[['sku', 'target_stock']].set_index('sku').join(df_roll_val)
    print('Binded new features')
    print()  
    
    # choosing features
    feats = selected_features_lofo
    
    
    # data for submission
    X_test = sub_dataset_ok.copy()
    X_test = X_test[feats]


    # split train/val
    X_train, X_val, y_train, y_val = train_test_split(
        val_dataset_ok[feats],
        val_dataset_ok['inventory_days'] - 1, # use target in [0, 29] instead of [1, 30]
        random_state=42
    )   
    
    if use_standardize:
        print('Stardardize...')
        for col in feats:
            mean_ = X_train[col].mean()
            std_ = X_train[col].std()
            X_train[col] = (X_train[col] - mean_) / std_
            X_val[col] = (X_val[col] - mean_) / std_
            X_test[col] = (X_test[col] - mean_) / std_

            # impute NA (very few ones)    
            X_train[col] = X_train[col].fillna(mean_) 
            X_val[col] = X_val[col].fillna(mean_)
            X_test[col] = X_test[col].fillna(mean_) 
            
    if use_minmax:
        print('MinMax...')
        for col in feats:            
            min_ = X_train[col].min()
            max_ = X_train[col].max()
            X_train = (X_train - min_) / (max_ - min_)
            X_val = (X_val - min_) / (max_ - min_)
            X_test = (X_test - min_) / (max_ - min_)
            
            # impute NA (very few ones)  
            mean_ = X_train[col].mean()
            X_train[col] = X_train[col].fillna(mean_) 
            X_val[col] = X_val[col].fillna(mean_)        
            X_test[col] = X_test[col].fillna(mean_) 
            

    if use_undersampling:
        print('Undersampling...')
        print('Before Undersampling:', X_train.shape[0])
        smp = RandomUnderSampler(random_state=2021)
        X_train, y_train = smp.fit_resample(X_train, y_train)
        print('After Undersampling:', X_train.shape[0])
    
    
    if use_standardize or use_minmax:
        assert X_train.isnull().sum().sum() == 0   
        assert X_val.isnull().sum().sum() == 0  
        assert X_test.isnull().sum().sum() == 0 
    
    
    print('Using', X_train.shape[1], 'features')
    
    return X_train, X_val, y_train, y_val, X_test<jupyter_output><empty_output><jupyter_text>Save datasets<jupyter_code>%%time

w = 19
n_windows = 7

X_train, X_val, y_train, y_val, X_test = create_train_val_test(
    w=w,
    n_windows=n_windows,
    use_undersampling=False,
    use_standardize=False,
    use_minmax=False
)

X_train.reset_index().to_feather('X_train.f')
X_val.reset_index().to_feather('X_val.f')
y_train.reset_index().to_feather('y_train.f')
y_val.reset_index().to_feather('y_val.f')
X_test.reset_index().to_feather('X_test.f')
%%time

w = 19
n_windows = 7

X_train_standard, X_val_standard, _, _, X_test_standard = create_train_val_test(
    w=w,
    n_windows=n_windows,
    use_undersampling=False,
    use_standardize=True,
    use_minmax=False
)

X_train_standard.reset_index().to_feather('X_train_standard.f')
X_val_standard.reset_index().to_feather('X_val_standard.f')
X_test_standard.reset_index().to_feather('X_test_standard.f')<jupyter_output>------ w = 19 ------
------ n_windows = 7 ------
30 11 sold_quantity
30 11 minutes_active
30 11 current_price
30 11 has_zero_sold
30 11 listing_type
30 11 shipping_logistic_type
30 11 shipping_payment
29 10 sold_quantity
29 10 minutes_active
29 10 current_price
29 10 has_zero_sold
29 10 listing_type
29 10 shipping_logistic_type
29 10 shipping_payment
28 9 sold_quantity
28 9 minutes_active
28 9 current_price
28 9 has_zero_sold
28 9 listing_type
28 9 shipping_logistic_type
28 9 shipping_payment
27 8 sold_quantity
27 8 minutes_active
27 8 current_price
27 8 has_zero_sold
27 8 listing_type
27 8 shipping_logistic_type
27 8 shipping_payment
26 7 sold_quantity
26 7 minutes_active
26 7 current_price
26 7 has_zero_sold
26 7 listing_type
26 7 shipping_logistic_type
26 7 shipping_payment
25 6 sold_quantity
25 6 minutes_active
25 6 current_price
25 6 has_zero_sold
25 6 listing_type
25 6 shipping_logistic_type
25 6 shipping_payment
24 5 sold_quantity
24 5 minutes_active
24 5 current_price
24 5 has[...]
{'fraction_non_alphanumeric': 0.1323432540870711, 'fraction_numerical': 0.029642493562488773, 'mean_word_length': 3.2299898682877406, 'pattern_counts': {'":': 0, '<': 31, '<?xml version=': 0, '>': 34, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '613627', 'license': 'MIT', 'n_tokens_mistral': 6413, 'n_tokens_neox': 5961, 'n_words': 1185, 'path': '1 - Preprocessing.ipynb', 'repo_name': 'igorkf/meli-data-challenge-2021', 'size': '16699'}