<jupyter_start><jupyter_text>sales_train.csv - the training set. Daily historical data from January 2013 to October 2015.   test.csv - the test set. You need to forecast the sales for these shops and products for November 2015.   sample_submission.csv - a sample submission file in the correct format.   items.csv - supplemental information about the items/products.   item_categories.csv  - supplemental information about the items categories.   shops.csv- supplemental information about the shops.教训，将数据类型改为int8会导致负数的出现code, kbd, pre, samp {    font-family:'consolas', Lucida Console, SimSun, Fira Code, Monaco !important;    font-size: 11pt !important;}div.output_area pre {    font-family: 'consolas', Lucida Console, SimSun, Fira Code, Monaco !important;    font-size:  10pt !important;}div.output_area img, div.output_area svg {    background-color: FFFFFF !important;}<jupyter_code>import numpy as np
import pandas as pd
import time
#pd.set_option('display.max_rows', 500)
pd.set_option('display.max_columns', 100)
from sklearn.preprocessing import LabelEncoder
import gc
from time_series_pipeline import *
from tqdm import tqdm
from scipy import stats
import time_series_pipeline
from itertools import product
items, shops, cats, train, test_indicate = load_data()
# item_id字段无负值
#train['id'] = train['shop_id'].astype(str) + '_' + train['item_id'].astype(str)
test_indicate['id'] = test_indicate['shop_id'].astype(str) + '_' + test_indicate['item_id'].astype(str)
#train = train[train['id'].isin(test_indicate['id'])]

shops.loc[shops['shop_name'] == 'Сергиев Посад ТЦ "7Я"', 'shop_name'] = 'СергиевПосад ТЦ "7Я"'
shops['city'] = shops['shop_name'].str.split(' ').transform(lambda x: x[0])
shops.loc[shops['city'] == '!Якутск', 'city'] = 'Якутск'
shops['city_code'] = LabelEncoder().fit_transform(shops['city'])
shops = shops[['shop_id','city_code']]

cats['split'] = cats['item_category_name'].str.split('-')
cats['type'] = cats['split'].transform(lambda x: x[0].strip())
cats['type_code'] = LabelEncoder().fit_transform(cats['type']) # 类型
cats['subtype'] = cats['split'].map(lambda x: x[1].strip() if len(x) > 1 else x[0].strip())
cats['subtype_code'] = LabelEncoder().fit_transform(cats['subtype']) # 子类型
cats = cats[['item_category_id','type_code', 'subtype_code']]

items.drop(['item_name'], axis = 1, inplace = True)
##################### 数据增强
matrix = [] 
cols = ['date_block_num','shop_id','item_id']
for i in range(34):
    sales = train[train.date_block_num==i]
    matrix.append(np.array(list(product([i], 
                                        sales.shop_id.unique(), 
                                        sales.item_id.unique())), 
                                        dtype='int16'))
    
matrix = pd.DataFrame(np.vstack(matrix), columns=cols)
matrix.sort_values(cols,inplace=True)
matrix['id'] = matrix['shop_id'].astype(str) + '_' + matrix['item_id'].astype(str)
###########加入测试集
test_indicate['date_block_num'] = 34
test_indicate['date_block_num'] = test_indicate['date_block_num'].astype(np.int8)
test_indicate['shop_id'] = test_indicate['shop_id'].astype(np.int8)
test_indicate['item_id'] = test_indicate['item_id'].astype(np.int16)
test_indicate

matrix = pd.concat([matrix, test_indicate], ignore_index = True, sort = False)
#matrix.fillna(0, inplace = True)
# 将日数据汇总为月数据
start = time.time()
df = pd.DataFrame() 
grouped = train.groupby(['date_block_num','shop_id','item_id'])
#df['item_price'] = grouped['item_price'].mean()
df['item_cnt_month'] = grouped['item_cnt_day'].sum()
df.reset_index(inplace = True)
#matrix['item_category_id'] = grouped['item_category_id'].agg(lambda x:stats.mode(x)[0][0])
#matrix['type_code'] = grouped['type_code'].agg(lambda x:stats.mode(x)[0][0])
#matrix['subtype_code'] = grouped['subtype_code'].agg(lambda x:stats.mode(x)[0][0]) #计算众数
print('data has {} rows and {} columns'.format(df.shape[0], df.shape[1]))
print('The program costs %.2f seconds'%(time.time() - start))

matrix = pd.merge(matrix, df, on = cols, how = 'left')
matrix['item_cnt_month'] = (matrix['item_cnt_month']
                                .fillna(0)
                                .clip(0,20) # NB clip target here
                                .astype(np.float16))
#matrix['item_category_id'] = grouped['item_category_id'].agg(lambda x:stats.mode(x)[0][0])
#matrix['type_code'] = grouped['type_code'].agg(lambda x:stats.mode(x)[0][0])
#matrix['subtype_code'] = grouped['subtype_code'].agg(lambda x:stats.mode(x)[0][0]) #计算众数
print('data has {} rows and {} columns'.format(df.shape[0], df.shape[1]))
print('The program costs %.2f seconds'%(time.time() - start))
matrix
train_shops = pd.merge(matrix, shops, on = 'shop_id', how = 'left')
print('data has {} rows and {} columns'.format(train_shops.shape[0], train_shops.shape[1]))
items_cats = pd.merge(items, cats, on = 'item_category_id')
print('data has {} rows and {} columns'.format(items_cats.shape[0], items_cats.shape[1]))
df = pd.merge(train_shops, items_cats, on = 'item_id', how = 'left')
print('data has {} rows and {} columns'.format(df.shape[0], df.shape[1]))
#%whos
del cats, grouped, items, items_cats, matrix, sales, shops, test_indicate, train, train_shops
gc.collect()
df.sort_values(by = ['date_block_num','id'], inplace = True)
df
#df.reset_index(inplace=True)
def groupby_shift(df, col, groupcol, shift_n, fill_na = np.nan):
    '''
    apply fast groupby shift
    df: data 
    col: column need to be shift 
    shift: n
    fill_na: na filled value
    '''
    rowno = list(df.groupby(groupcol).size().cumsum()) # 获取每分组第一个元素的index
    lagged_col = df[col].shift(shift_n) # 不分组滚动
    na_rows = [i for i in range(shift_n)] # 初始化为缺失值的index
    for i in rowno:
        if i == rowno[len(rowno)-1]: # 最后一个index直接跳过不然会超出最大index
            continue 
        else:
            new = [i + j for j in range(shift_n)] # 将每组最开始的shift_n个值变成nan
            na_rows.extend(new) # 加入列表
    na_rows = list(set(na_rows)) # 去除重复值
    na_rows = [i for i in na_rows if i <= len(lagged_col) - 1] # 防止超出最大index
    lagged_col.iloc[na_rows] = fill_na # 变成nan
    return lagged_col

start = time.time()

df['shift_1'] = groupby_shift(df, 'item_cnt_month', 'id', 1)
df['shift_2'] = groupby_shift(df, 'item_cnt_month', 'id', 2)
df['shift_3'] = groupby_shift(df, 'item_cnt_month', 'id', 3)
df['shift_6'] = groupby_shift(df, 'item_cnt_month', 'id', 6)
df['shift_12'] = groupby_shift(df, 'item_cnt_month', 'id', 12)


df['shift_3_roll_avg_3'] = df['shift_3'].rolling(3).mean().astype(np.float32)
df['shift_3_roll_avg_6'] = df['shift_3'].rolling(6).mean().astype(np.float32)
df['shift_12_roll_avg_6'] = df['shift_12'].rolling(6).mean().astype(np.float32)

'''
df['mon_avg_item_cnt'] = groupby_shift(df, 'item_cnt_month', 'date_block_num', 1)
df['mon_avg_item_cnt'] = df.groupby(['date_block_num'])['mon_avg_item_cnt'].transform(lambda x: x.mean())

group = ['date_block_num', 'item_id']
df['mon_item_avg_1'] = groupby_shift(df, 'item_cnt_month', group, 1)
df['mon_item_avg_1'] = df.groupby(group)['mon_item_avg_1'].transform(lambda x: x.mean())
df['mon_item_avg_2'] = groupby_shift(df, 'item_cnt_month', group, 2)
df['mon_item_avg_2'] = df.groupby(group)['mon_item_avg_2'].transform(lambda x: x.mean())
df['mon_item_avg_6'] = groupby_shift(df, 'item_cnt_month', group, 6)
df['mon_item_avg_6'] = df.groupby(group)['mon_item_avg_6'].transform(lambda x: x.mean())

group = ['date_block_num', 'shop_id']
df['mon_shop_1'] = groupby_shift(df, 'item_cnt_month', group, 1)
df['mon_shop_1'] = df.groupby(group)['mon_shop_1'].transform(lambda x: x.mean())

group = ['date_block_num', 'shop_id', 'item_category_id']
df['mon_shop_item_1'] = groupby_shift(df, 'item_cnt_month', group, 1)
df['mon_shop_item_1'] = df.groupby(group)['mon_shop_item_1'].transform(lambda x: x.mean())

group = ['date_block_num', 'shop_id', 'subtype_code']
df['mon_shop_sub_1'] = groupby_shift(df, 'item_cnt_month', group, 1)
df['mon_shop_sub_1'] = df.groupby(group)['mon_shop_sub_1'].transform(lambda x: x.mean())

group = ['date_block_num', 'city_code']
df['mon_city_avg_1'] = groupby_shift(df, 'item_cnt_month', group, 1)
df['mon_city_avg_1'] = df.groupby(group)['mon_city_avg_1'].transform(lambda x: x.mean())

group = ['date_block_num', 'item_id', 'city_code']
df['mon_item_city_avg_1'] = groupby_shift(df, 'item_cnt_month', group, 1)
df['mon_item_city_avg_1'] = df.groupby(group)['mon_item_city_avg_1'].transform(lambda x: x.mean())

group = ['date_block_num', 'type_code']
df['mon_type_avg_1'] = groupby_shift(df, 'item_cnt_month', group, 1)
df['mon_type_avg_1'] = df.groupby(group)['mon_type_avg_1'].transform(lambda x: x.mean())

group = ['date_block_num', 'subtype_code']
df['mon_subtype_avg_1'] = groupby_shift(df, 'item_cnt_month', group, 1)
df['mon_subtype_avg_1'] = df.groupby(group)['mon_subtype_avg_1'].transform(lambda x: x.mean())
'''
#df['shift6_rolling6_mean'] = df.groupby(['id'])['item_cnt_month'].transform(lambda x: x.shift(6).rolling(6).mean())
#df['shift2_rolling2_mean'] = df.groupby(['id'])['item_cnt_month'].transform(lambda x: x.shift(2).rolling(2).mean())
#df['shift3_rolling1_mean'] = df.groupby(['id'])['item_cnt_month'].transform(lambda x: x.shift(3).rolling(1).mean())   
#df['shift3_rolling2_mean'] = df.groupby(['id'])['item_cnt_month'].transform(lambda x: x.shift(3).rolling(2).mean())
#df['shift6_rolling6_mean'] = df.groupby(['id'])['item_cnt_month'].transform(lambda x: x.shift(6).rolling(6).mean())
print('The program costs %.2f seconds'%(time.time() - start))
df
'''
df['price_shift_1'] = groupby_shift(df, 'item_price', 'id', 1)
df['price_shift_2'] = groupby_shift(df, 'item_price', 'id', 2)
df['price_shift_3'] = groupby_shift(df, 'item_price', 'id', 3)
df['price_shift_6'] = groupby_shift(df, 'item_price', 'id', 6)
df['price_shift_12'] = groupby_shift(df, 'item_price', 'id', 12)
'''
def recode_na(df, cols):
    '''recode na value by grouped average
    '''
    for i in tqdm(cols):
        df[i] = df[i].transform(lambda x: x.fillna(x.median()))
    return df

colz = ['shift_1', 'shift_3', 'shift_12', 'price_shift_1', 'price_shift_3',
       'price_shift_6', 'price_shift_12', 'shift_3_roll_avg_3',
       'shift_3_roll_avg_6', 'shift_12_roll_avg_6', 'mon_avg_item_cnt',
       'mon_item_avg_1', 'mon_item_avg_2', 'mon_item_avg_6', 'mon_shop_1',
       'mon_shop_item_1', 'mon_shop_sub_1', 'mon_city_avg_1',
       'mon_item_city_avg_1', 'mon_type_avg_1', 'mon_subtype_avg_1']

df = recode_na(df, colz)

df['month'] = df['date_block_num'] % 12
df = df[]
df['id'].isna().value_counts()
df[(== '59_22088') & (df['date_block_num'] > 11)]

df.columns
features = ['date_block_num',
            'month',
            'shop_id',
            'item_id',
            'city_code', 
            'item_category_id', 
            'type_code', 
            'subtype_code',
            'shift_1',
            'shift_3',
            'shift_12', 
            'price_shift_1', 
            'price_shift_3',
            'price_shift_6', 
            'price_shift_12', 
            'shift_3_roll_avg_3',
            'shift_3_roll_avg_6', 
            'shift_12_roll_avg_6',
            'mon_avg_item_cnt',
            'mon_item_avg_1',
            'mon_item_avg_2', 
            'mon_item_avg_6', 
            'mon_shop_1',
            'mon_shop_item_1', 
            'mon_shop_sub_1', 
            'mon_city_avg_1',
            'mon_item_city_avg_1', 
            'mon_type_avg_1', 
            'mon_subtype_avg_1']
cat_features = ['month', 'shop_id','item_id','city_code', 'item_category_id', 'type_code', 'subtype_code']
from catboost import CatBoostRegressor
from sklearn import preprocessing, metrics
from sklearn.model_selection import TimeSeriesSplit
df.sort_values(['date_block_num','id'],inplace = True)
#df
#data = data.sort_values('date_block_num')

x_train = df[df['date_block_num'] < 34]
y_train = x_train['item_cnt_month']
test = df[df['date_block_num'] == 34]

#need_to_remove = ['item_cnt_day','city_code','item_category_id',
                 # 'type_code','subtype_code', 'shop_id', 'item_id', 'id']
#features = [i for i in list(df.columns) if i not in need_to_remove]
#n_fold = 3 #3 for timely purpose of the kernel
folds = TimeSeriesSplit(n_splits = 3) # use TimeSeriesSplit cv
splits = folds.split(x_train, y_train)
val_pred = np.zeros(len(x_train))
test_pred = np.zeros(len(test))
for fold, (trn_idx, val_idx) in enumerate(splits):
    print(f'Training fold {fold + 1}')
          
    train_set = x_train.iloc[trn_idx][features]
    y_tra = y_train.iloc[trn_idx]
    val_set = x_train.iloc[val_idx][features]
    y_val = y_train.iloc[val_idx]

    model = CatBoostRegressor(iterations = 500,
                              learning_rate = 0.05,
                              depth = 6,
                              eval_metric = 'RMSE',
                              random_seed = 42,
                              bagging_temperature = 0.2,
                              od_type = 'Iter',
                              metric_period = 50,
                              od_wait = 20)
    model.fit(train_set, y_tra, 
              eval_set = (val_set, y_val),
              use_best_model = True, 
              cat_features = cat_features,
              verbose = 50)
        
    val_pred[val_idx] = model.predict(x_train.iloc[val_idx][features]) # prediction
    test_pred += model.predict(test[features]) / 3 # calculate mean prediction value of 3 models
    print('-' * 50)
    print('\n')
          
val_rmse = np.sqrt(metrics.mean_squared_error(y_train, val_pred))
print('Our out of folds rmse is {:.4f}'.format(val_rmse))
len(val_pred)
len(test_pred)
pd.Series(test_pred.clip(0,20)).describe()
test_indicate

test_indicate['item_cnt_month'] = test_pred.clip(0,20)
#test_indicate.to_csv('xgb_submission.csv', index=False)
test_indicate

submission = pd.DataFrame({'ID': test_indicate.index,'item_cnt_month': test_pred.clip(0,20)})
submission
submission.to_csv('../output/cat_submission.csv', index=False)
submission
def run_lgb(data):
    '''cross validation
    '''
    start = time.time()
    
    data = data.sort_values('date_block_num')
    
    x_train = data[data['date_block_num'] <= 27
    y_train = x_train['item_cnt_day']
    
    test = data[(data['date_block_num'] > 27)
    

    del data
    gc.collect()

    params = {
        'boosting_type': 'gbdt',
        'metric': 'rmse',
        'objective': 'poisson', # loss function
        'seed': 225,
        'learning_rate': 0.02,
        'lambda': 0.4, # l2 regularization
        'reg_alpha': 0.4, # l1 regularization
        'max_depth': 5, # max depth of decision trees
        'num_leaves': 64, #  number of leaves
        'bagging_fraction': 0.7, # bootstrap sampling
        'bagging_freq' : 1,
        'colsample_bytree': 0.7 # feature sampling
    }
    
      
    oof = np.zeros(len(x_train))
    preds = np.zeros(len(test))
    
    n_fold = 3 #3 for timely purpose of the kernel
    folds = TimeSeriesSplit(n_splits=n_fold) # use TimeSeriesSplit cv
    splits = folds.split(x_train, y_train)


    #feature_importances = pd.DataFrame()
    #feature_importances['feature'] = features
    
    for fold, (trn_idx, val_idx) in enumerate(splits):
        print(f'Training fold {fold + 1}')
        
        train_set = lgb.Dataset(x_train.iloc[trn_idx][features], y_train.iloc[trn_idx], categorical_feature = cat)
        
        val_set = lgb.Dataset(x_train.iloc[val_idx][features], y_train.iloc[val_idx], categorical_feature = cat)

        model = lgb.train(params, train_set, num_boost_round = 2400, early_stopping_rounds = 50, 
                          valid_sets = [val_set], verbose_eval = 50)
        
        
        #lgb.plot_importance(model, importance_type = 'gain', precision = 0,
                               # height = 0.5, figsize = (6, 10), title = '') 
        
        #feature_importances[f'fold_{fold + 1}'] = model.feature_importance()
        
        oof[val_idx] = model.predict(x_train.iloc[val_idx][features]) # prediction
        preds += model.predict(test[features]) / 3 # calculate mean prediction value of 3 models
        print('-' * 50)
        print('\n')
    model.save_model('model.lgb') # save model
    del x_train
        
    print('3 folds cross-validation costs %7.2f seconds'%(time.time() - start))

    oof_rmse = np.sqrt(metrics.mean_squared_error(y_train, oof))
    print(f'Our out of folds rmse is {oof_rmse}')
    del y_train
        
    test = test[['id', 'date', 'demand']]
    test['demand'] = preds
    gc.collect()
    return test
df[(df['shop_id'] == 59) & (df['item_id'] == 126)]<jupyter_output><empty_output><jupyter_text>```Index               67512848date                67512848date_block_num       8439106shop_id              8439106item_id             16878212item_price          33756424item_cnt_day         8439106city_code            8439106item_category_id     8439106type_code            8439106subtype_code         8439106lag_1               67512848dtype: int64```<jupyter_code>的
df['lag_t1'] = df.groupby(['shop_id','item_id'])['item_cnt_day'].transform(lambda x: x.shift(30))


date = df.groupby(['shop_id','item_id'])['date']
date = pd.DataFrame(date)
date
df['lag_t1_rolling'] = df.groupby(['shop_id','item_id'])['item_cnt_day'].transform(lambda x: x.shift(30).rolling(30).mean())
df['lag_3'].isna().value_counts()
df['lag_t7'] = df.groupby(['shop_id', 'item_id'])['item_cnt_day'].transform(lambda x: x.shift(7))
df
df['lag_t7'].isna().value_counts()<jupyter_output><empty_output><jupyter_text>测试集是34个月内某些商店和某些物品的乘积。 有5100个商品 * 42个商店 = 214200对。 与训练集相比，有363件新商品。 因此，对于测试集中的大多数项目，目标值应为零。 另一方面，训练集仅包含过去出售或退回的货币对。 主要思想是计算月销售额，并在一个月内将每个唯一对的零销售额扩展为零。 这样，训练数据将类似于测试数据。<jupyter_code>df

data = pd.read_pickle('../data/data.pkl')
data
data.columns
features = [
    'date_block_num',
    'shop_id',
    'item_id',
    #'item_cnt_month',
    'city_code',
    'item_category_id',
    'type_code',
    'subtype_code',
    'item_cnt_month_lag_1',
    'item_cnt_month_lag_2',
    'item_cnt_month_lag_3',
    'item_cnt_month_lag_6',
    'item_cnt_month_lag_12',
    'date_avg_item_cnt_lag_1',
    'date_item_avg_item_cnt_lag_1',
    'date_item_avg_item_cnt_lag_2',
    'date_item_avg_item_cnt_lag_3',
    'date_item_avg_item_cnt_lag_6',
    'date_item_avg_item_cnt_lag_12',
    #'date_shop_avg_item_cnt_lag_1',
    #'date_shop_avg_item_cnt_lag_2',
    #'date_shop_avg_item_cnt_lag_3',
    #'date_shop_avg_item_cnt_lag_6',
    #'date_shop_avg_item_cnt_lag_12',
    #'date_cat_avg_item_cnt_lag_1',
    #'date_shop_cat_avg_item_cnt_lag_1',
    #'date_shop_type_avg_item_cnt_lag_1',
    #'date_shop_subtype_avg_item_cnt_lag_1',
    #'date_city_avg_item_cnt_lag_1',
    #'date_item_city_avg_item_cnt_lag_1',
    #'date_type_avg_item_cnt_lag_1',
    #'date_subtype_avg_item_cnt_lag_1',
    #'delta_price_lag',
    'month',
    #'days',
    #'item_shop_last_sale',
    #'item_last_sale',
    #'item_shop_first_sale',
    #'item_first_sale',
]
cat_features = ['date_block_num',
                'month', 
                'shop_id',
                'item_id',
                'city_code',
                'item_category_id',
                'type_code', 
                'subtype_code']
#data
data['id'] = data['shop_id'].astype(str) + '_' + test_indicate['item_id'].astype(str)
data.sort_values(['date_block_num','id'],inplace = True)
x_train = data[data['date_block_num'] < 34]
y_train = x_train['item_cnt_month'].astype(np.float32)
test = data[data['date_block_num'] == 34]

#need_to_remove = ['item_cnt_day','city_code','item_category_id',
                 # 'type_code','subtype_code', 'shop_id', 'item_id', 'id']
#features = [i for i in list(df.columns) if i not in need_to_remove]
#n_fold = 3 #3 for timely purpose of the kernel
folds = TimeSeriesSplit(n_splits = 3) # use TimeSeriesSplit cv
splits = folds.split(x_train, y_train)
val_pred = np.zeros(len(x_train))
test_pred = np.zeros(len(test))
for fold, (trn_idx, val_idx) in enumerate(splits):
    print(f'Training fold {fold + 1}')
          
    train_set = x_train.iloc[trn_idx][features]
    y_tra = y_train.iloc[trn_idx]
    val_set = x_train.iloc[val_idx][features]
    y_val = y_train.iloc[val_idx]

    model = CatBoostRegressor(iterations = 500,
                              learning_rate = 0.05,
                              depth = 6,
                              eval_metric = 'RMSE',
                              random_seed = 42,
                              bagging_temperature = 0.2,
                              od_type = 'Iter',
                              metric_period = 50,
                              od_wait = 20)
    model.fit(train_set, y_tra, 
              eval_set = (val_set, y_val),
              use_best_model = True, 
              cat_features = cat_features,
              verbose = 50)
        
    val_pred[val_idx] = model.predict(x_train.iloc[val_idx][features]) # prediction
    test_pred += model.predict(test[features]) / 3 # calculate mean prediction value of 3 models
    print('-' * 50)
    print('\n')
          
val_rmse = np.sqrt(metrics.mean_squared_error(y_train, val_pred))
print('Our out of folds rmse is {:.4f}'.format(val_rmse))
from itertools import product
product[1]<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup