<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 make the train_pivot, duplicate exist when index = ['Cliente','Producto']
for each cliente & producto, first find its most common Agencia_ID, Canal_ID, Ruta_SAK
Step1:  make pivot table of test
Step2:  groupby use Agencia_ID, Ruta_SAK, Cliente_ID, Producto_ID
Step3:  if predict week 8, use data from 3,4,5,6,7
if predict week 9, use data from 3,4,5,6,7
Step4:  data for predict week [34567----9], time plus 2 week
Step5:  test_for private data, week 11
Step6:  for two week ahead 45678 to 10
Step7:  data for predict week 8&9, time plus 1 week
train_45678 for 8+1 =9
Step8:  train_34567 7+1 = 8
Step9:  concat train_pivot_45678_to_9 & train_pivot_34567_to_8 to perform t_plus_1, train_data is over
Step10:  prepare for test data, for week 10, we use 5,6,7,8,9
Step11:  begin predict for week 11
train_3456 for 6+2 = 8
Step12:  train_4567 for 7 + 2 = 9
Step13:  concat
Step14:  for test data week 11, we use 6,7,8,9
Step15:  over
Step16:  create time feature
Step17:  fit mean feature on target
Step18:  add dummy feature
Step19:  add product feature
Step20:  add town feature
Step21:  begin xgboost training
Step22:  for 1 week later
cv rmse 0.451181 with dummy canal, time regr,
cv rmse 0.450972 without dummy canal, time regr,
cv rmse 0.4485676 without dummy canal, time regr, producto info
cv rmse 0.4487434 without dummy canal, time regr, producto info, cliente_per_town
for 2 week later
cv rmse 0.4513236 without dummy canal, time regr, producto info
<ASSISTANT_TASK:>
Python Code:
agencia_for_cliente_producto = train_dataset[['Cliente_ID','Producto_ID'
                                              ,'Agencia_ID']].groupby(['Cliente_ID',
                                                                        'Producto_ID']).agg(lambda x:x.value_counts().index[0]).reset_index()
canal_for_cliente_producto = train_dataset[['Cliente_ID',
                                            'Producto_ID','Canal_ID']].groupby(['Cliente_ID',
                                                                        'Producto_ID']).agg(lambda x:x.value_counts().index[0]).reset_index()
ruta_for_cliente_producto = train_dataset[['Cliente_ID',
                                           'Producto_ID','Ruta_SAK']].groupby(['Cliente_ID',
                                                                        'Producto_ID']).agg(lambda x:x.value_counts().index[0]).reset_index()
gc.collect()
agencia_for_cliente_producto.to_pickle('agencia_for_cliente_producto.csv')
canal_for_cliente_producto.to_pickle('canal_for_cliente_producto.csv')
ruta_for_cliente_producto.to_pickle('ruta_for_cliente_producto.csv')
agencia_for_cliente_producto = pd.read_pickle('agencia_for_cliente_producto.csv')
canal_for_cliente_producto = pd.read_pickle('canal_for_cliente_producto.csv')
ruta_for_cliente_producto = pd.read_pickle('ruta_for_cliente_producto.csv')
# train_dataset['log_demand'] = train_dataset['Demanda_uni_equil'].apply(np.log1p)
pivot_train = pd.pivot_table(data= train_dataset[['Cliente_ID','Producto_ID','log_demand','Semana']],
                                        values='log_demand', index=['Cliente_ID','Producto_ID'],
                                        columns=['Semana'], aggfunc=np.mean,fill_value = 0).reset_index()
pivot_train.head()
pivot_train = pd.merge(left = pivot_train, right = agencia_for_cliente_producto, how = 'inner', on = ['Cliente_ID','Producto_ID'])
pivot_train = pd.merge(left = pivot_train, right = canal_for_cliente_producto, how = 'inner', on = ['Cliente_ID','Producto_ID'])
pivot_train = pd.merge(left = pivot_train, right = ruta_for_cliente_producto, how = 'inner', on = ['Cliente_ID','Producto_ID'])
pivot_train.to_pickle('pivot_train_with_zero.pickle')
pivot_train = pd.read_pickle('pivot_train_with_zero.pickle')
pivot_train.to_pickle('pivot_train_with_nan.pickle')
pivot_train = pd.read_pickle('pivot_train_with_nan.pickle')
pivot_train = pivot_train.rename(columns={3: 'Sem3', 4: 'Sem4',5: 'Sem5', 6: 'Sem6',7: 'Sem7', 8: 'Sem8',9: 'Sem9'})
pivot_train.head()
pivot_train.columns.values
Explanation: make the train_pivot, duplicate exist when index = ['Cliente','Producto']
for each cliente & producto, first find its most common Agencia_ID, Canal_ID, Ruta_SAK
End of explanation
test_dataset = pd.read_csv('origin/test.csv')
test_dataset.head()
test_dataset[test_dataset['Semana'] == 10].shape
test_dataset[test_dataset['Semana'] == 11].shape
pivot_test = pd.merge(left=pivot_train, right = test_dataset[['id','Cliente_ID','Producto_ID','Semana']],
                      on =['Cliente_ID','Producto_ID'],how = 'inner' )
pivot_test.head()
pivot_test_new = pd.merge(pivot_train[['Cliente_ID', 'Producto_ID', 'Sem3', 'Sem4', 'Sem5', 'Sem6', 'Sem7',
       'Sem8', 'Sem9']],right = test_dataset, on = ['Cliente_ID','Producto_ID'],how = 'right')
pivot_test_new.head()
pivot_test_new.to_pickle('pivot_test.pickle')
pivot_test.to_pickle('pivot_test.pickle')
pivot_test = pd.read_pickle('pivot_test.pickle')
pivot_test.head()
Explanation: make pivot table of test
End of explanation
train_dataset.head()
import itertools
col_list = ['Agencia_ID', 'Ruta_SAK', 'Cliente_ID', 'Producto_ID']
all_combine = itertools.combinations(col_list,2)
list_2element_combine = [list(tuple) for tuple in all_combine]
col_1elm_2elm = col_list + list_2element_combine
col_1elm_2elm
train_dataset_test = train_dataset[train_dataset['Semana'] < 8].copy()
Explanation: groupby use Agencia_ID, Ruta_SAK, Cliente_ID, Producto_ID
End of explanation
def categorical_useful(train_dataset,pivot_train):
#     if is_train:
#         train_dataset_test = train_dataset[train_dataset['Semana'] < 8].copy()
#     elif is_train == False:
    train_dataset_test = train_dataset.copy()
        
    log_demand_by_agen = train_dataset_test[['Agencia_ID','log_demand']].groupby('Agencia_ID').mean().reset_index()
    log_demand_by_ruta = train_dataset_test[['Ruta_SAK','log_demand']].groupby('Ruta_SAK').mean().reset_index()
    log_demand_by_cliente = train_dataset_test[['Cliente_ID','log_demand']].groupby('Cliente_ID').mean().reset_index()
    log_demand_by_producto = train_dataset_test[['Producto_ID','log_demand']].groupby('Producto_ID').mean().reset_index()
    
    log_demand_by_agen_ruta = train_dataset_test[['Agencia_ID', 'Ruta_SAK',
                                                  'log_demand']].groupby(['Agencia_ID', 'Ruta_SAK']).mean().reset_index()
    log_demand_by_agen_cliente = train_dataset_test[['Agencia_ID', 'Cliente_ID',
                                                     'log_demand']].groupby(['Agencia_ID', 'Cliente_ID']).mean().reset_index()
    log_demand_by_agen_producto = train_dataset_test[['Agencia_ID', 'Producto_ID',
                                                      'log_demand']].groupby(['Agencia_ID', 'Producto_ID']).mean().reset_index()
    log_demand_by_ruta_cliente = train_dataset_test[['Ruta_SAK', 'Cliente_ID',
                                                     'log_demand']].groupby(['Ruta_SAK', 'Cliente_ID']).mean().reset_index()
    log_demand_by_ruta_producto = train_dataset_test[['Ruta_SAK', 'Producto_ID',
                                                      'log_demand']].groupby(['Ruta_SAK', 'Producto_ID']).mean().reset_index()
    log_demand_by_cliente_producto = train_dataset_test[['Cliente_ID', 'Producto_ID',
                                                     'log_demand']].groupby(['Cliente_ID', 'Producto_ID']).mean().reset_index()
    
    log_demand_by_cliente_producto_agen = train_dataset_test[[
            'Cliente_ID','Producto_ID','Agencia_ID','log_demand']].groupby(['Cliente_ID',
                                                                            'Agencia_ID','Producto_ID']).mean().reset_index()
    
    log_sum_by_cliente = train_dataset_test[['Cliente_ID','log_demand']].groupby('Cliente_ID').sum().reset_index()
    
    ruta_freq_semana = train_dataset[['Semana','Ruta_SAK']].groupby(['Ruta_SAK']).count().reset_index()
    clien_freq_semana = train_dataset[['Semana','Cliente_ID']].groupby(['Cliente_ID']).count().reset_index()
    agen_freq_semana = train_dataset[['Semana','Agencia_ID']].groupby(['Agencia_ID']).count().reset_index()
    prod_freq_semana = train_dataset[['Semana','Producto_ID']].groupby(['Producto_ID']).count().reset_index()
    
    
    pivot_train = pd.merge(left = pivot_train,right = ruta_freq_semana,
                           how = 'left', on = ['Ruta_SAK']).rename(columns={'Semana': 'ruta_freq'})
    pivot_train = pd.merge(left = pivot_train,right = clien_freq_semana,
                           how = 'left', on = ['Cliente_ID']).rename(columns={'Semana': 'clien_freq'})
    pivot_train = pd.merge(left = pivot_train,right = agen_freq_semana,
                           how = 'left', on = ['Agencia_ID']).rename(columns={'Semana': 'agen_freq'})
    pivot_train = pd.merge(left = pivot_train,right = prod_freq_semana,
                           how = 'left', on = ['Producto_ID']).rename(columns={'Semana': 'prod_freq'})
    
    pivot_train = pd.merge(left = pivot_train,
                           right = log_demand_by_agen,
                           how = 'left', on = ['Agencia_ID']).rename(columns={'log_demand': 'agen_for_log_de'})
    pivot_train = pd.merge(left = pivot_train,
                           right = log_demand_by_ruta,
                           how = 'left', on = ['Ruta_SAK']).rename(columns={'log_demand': 'ruta_for_log_de'})
    pivot_train = pd.merge(left = pivot_train,
                           right = log_demand_by_cliente,
                           how = 'left', on = ['Cliente_ID']).rename(columns={'log_demand': 'cliente_for_log_de'})
    pivot_train = pd.merge(left = pivot_train,
                           right = log_demand_by_producto,
                           how = 'left', on = ['Producto_ID']).rename(columns={'log_demand': 'producto_for_log_de'})
    pivot_train = pd.merge(left = pivot_train,
                           right = log_demand_by_agen_ruta,
                           how = 'left', on = ['Agencia_ID', 'Ruta_SAK']).rename(columns={'log_demand': 'agen_ruta_for_log_de'})
    pivot_train = pd.merge(left = pivot_train,
                           right = log_demand_by_agen_cliente,
                           how = 'left', on = ['Agencia_ID', 'Cliente_ID']).rename(columns={'log_demand': 'agen_cliente_for_log_de'})
    pivot_train = pd.merge(left = pivot_train,
                           right = log_demand_by_agen_producto,
                           how = 'left', on = ['Agencia_ID', 'Producto_ID']).rename(columns={'log_demand': 'agen_producto_for_log_de'})
    pivot_train = pd.merge(left = pivot_train,
                           right = log_demand_by_ruta_cliente,
                           how = 'left', on = ['Ruta_SAK', 'Cliente_ID']).rename(columns={'log_demand': 'ruta_cliente_for_log_de'})
    pivot_train = pd.merge(left = pivot_train,
                           right = log_demand_by_ruta_producto,
                           how = 'left', on = ['Ruta_SAK', 'Producto_ID']).rename(columns={'log_demand': 'ruta_producto_for_log_de'})
    pivot_train = pd.merge(left = pivot_train,
                           right = log_demand_by_cliente_producto,
                           how = 'left', on = ['Cliente_ID', 'Producto_ID']).rename(columns={'log_demand': 'cliente_producto_for_log_de'})
    
    pivot_train = pd.merge(left = pivot_train,
                           right = log_sum_by_cliente,
                           how = 'left', on = ['Cliente_ID']).rename(columns={'log_demand': 'cliente_for_log_sum'})
    
    pivot_train = pd.merge(left = pivot_train,
                       right = log_demand_by_cliente_producto_agen,
                       how = 'left', on = ['Cliente_ID', 'Producto_ID',
                                           'Agencia_ID']).rename(columns={'log_demand': 'cliente_producto_agen_for_log_sum'})
    
    
    pivot_train['corr'] = pivot_train['producto_for_log_de'] * pivot_train['cliente_for_log_de'] / train_dataset_test['log_demand'].median()
    return pivot_train
def define_time_features(df, to_predict = 't_plus_1' , t_0 = 8):
    if(to_predict == 't_plus_1' ):
        df['t_min_1'] = df['Sem'+str(t_0-1)]
    if(to_predict == 't_plus_2' ):
        df['t_min_6'] = df['Sem'+str(t_0-6)]
      
    df['t_min_2'] = df['Sem'+str(t_0-2)]
    df['t_min_3'] = df['Sem'+str(t_0-3)]
    df['t_min_4'] = df['Sem'+str(t_0-4)]
    df['t_min_5'] = df['Sem'+str(t_0-5)]
    
    
    if(to_predict == 't_plus_1' ):
        df['t1_min_t2'] =  df['t_min_1'] - df['t_min_2']
        df['t1_min_t3'] =  df['t_min_1'] - df['t_min_3']
        df['t1_min_t4'] =  df['t_min_1'] - df['t_min_4']
        df['t1_min_t5'] =  df['t_min_1'] - df['t_min_5']
    
    if(to_predict == 't_plus_2' ):
        df['t2_min_t6'] =  df['t_min_2'] - df['t_min_6']
        df['t3_min_t6'] =  df['t_min_3'] - df['t_min_6']
        df['t4_min_t6'] =  df['t_min_4'] - df['t_min_6']
        df['t5_min_t6'] =  df['t_min_5'] - df['t_min_6']
    df['t2_min_t3'] =  df['t_min_2'] - df['t_min_3']
    df['t2_min_t4'] =  df['t_min_2'] - df['t_min_4']
    df['t2_min_t5'] =  df['t_min_2'] - df['t_min_5']
    df['t3_min_t4'] =  df['t_min_3'] - df['t_min_4']
    df['t3_min_t5'] =  df['t_min_3'] - df['t_min_5']
    df['t4_min_t5'] =  df['t_min_4'] - df['t_min_5']
        
    return df 
def lin_regr(row, to_predict, t_0, semanas_numbers):
    row = row.copy()
    row.index = semanas_numbers
    row = row.dropna()
    if(len(row>2)):
        X = np.ones(shape=(len(row), 2))
        X[:,1] = row.index
        y = row.values
        regr = linear_model.LinearRegression()
        regr.fit(X, y)
        if(to_predict == 't_plus_1'):
            return regr.predict([[1,t_0+1]])[0]
        elif(to_predict == 't_plus_2'):
            return regr.predict([[1,t_0+2]])[0]
    else:
        return None
def lin_regr_features(pivot_df,to_predict, semanas_numbers,t_0):
    pivot_df = pivot_df.copy()
    semanas_names = ['Sem%i' %i for i in semanas_numbers]
    columns = ['Sem%i' %i for i in semanas_numbers]
    columns.append('Producto_ID')
    pivot_grouped = pivot_df[columns].groupby('Producto_ID').aggregate('mean')
    pivot_grouped['LR_prod'] = np.zeros(len(pivot_grouped))
    pivot_grouped['LR_prod'] = pivot_grouped[semanas_names].apply(lin_regr, axis = 1,
                                                                  to_predict = to_predict,
                                                                  t_0 = t_0, semanas_numbers = semanas_numbers )
    pivot_df = pd.merge(pivot_df, pivot_grouped[['LR_prod']], how='left', left_on = 'Producto_ID', right_index=True)
    pivot_df['LR_prod_corr'] = pivot_df['LR_prod'] * pivot_df['cliente_for_log_sum'] / 100
    return pivot_df
cliente_tabla = pd.read_csv('origin/cliente_tabla.csv')
town_state = pd.read_csv('origin/town_state.csv')
town_state['town_id'] = town_state['Town'].str.split()
town_state['town_id'] = town_state['Town'].str.split(expand = True)
def add_pro_info(dataset):
    train_basic_feature = dataset[['Cliente_ID','Producto_ID','Agencia_ID']].copy()
    train_basic_feature.drop_duplicates(inplace = True)
    cliente_per_town = pd.merge(train_basic_feature,cliente_tabla,on = 'Cliente_ID',how= 'inner' )
#     print cliente_per_town.shape
    cliente_per_town = pd.merge(cliente_per_town,town_state[['Agencia_ID','town_id']],on = 'Agencia_ID',how= 'inner' )
#     print cliente_per_town.shape
    cliente_per_town_count = cliente_per_town[['NombreCliente','town_id']].groupby('town_id').count().reset_index()
#     print cliente_per_town_count.head()
    cliente_per_town_count_final = pd.merge(cliente_per_town[['Cliente_ID','Producto_ID','town_id','Agencia_ID']],
                                            cliente_per_town_count,on = 'town_id',how = 'inner')
#     print cliente_per_town_count_final.head()
    cliente_per_town_count_final.drop_duplicates(inplace = True)
    
    dataset_final = pd.merge(dataset,cliente_per_town_count_final[['Cliente_ID','Producto_ID','NombreCliente','Agencia_ID']],
                             on = ['Cliente_ID','Producto_ID','Agencia_ID'],how = 'left')
    return dataset_final
pre_product = pd.read_csv('preprocessed_products.csv',index_col = 0)
pre_product['weight_per_piece'] = pd.to_numeric(pre_product['weight_per_piece'], errors='coerce')
pre_product['weight'] = pd.to_numeric(pre_product['weight'], errors='coerce')
pre_product['pieces'] = pd.to_numeric(pre_product['pieces'], errors='coerce')
def add_product(dataset):
    dataset = pd.merge(dataset,pre_product[['ID','weight','weight_per_piece','pieces']],
                                                             left_on = 'Producto_ID',right_on = 'ID',how = 'left')
    return dataset
Explanation: if predict week 8, use data from 3,4,5,6,7
if predict week 9, use data from 3,4,5,6,7
End of explanation
train_34567 = train_dataset.loc[train_dataset['Semana'].isin([3,4,5,6,7]), :].copy()
train_pivot_34567_to_9 = pivot_train_zero.loc[(pivot_train['Sem9'].notnull()),:].copy()
train_pivot_34567_to_9 = categorical_useful(train_34567,train_pivot_34567_to_9)
del train_34567
gc.collect()
train_pivot_34567_to_9 = define_time_features(train_pivot_34567_to_9, to_predict = 't_plus_2' , t_0 = 9)
train_pivot_34567_to_9 = lin_regr_features(train_pivot_34567_to_9,to_predict ='t_plus_2',
                                           semanas_numbers = [3,4,5,6,7],t_0 = 9)
train_pivot_34567_to_9['target'] = train_pivot_34567_to_9['Sem9']
train_pivot_34567_to_9.drop(['Sem8','Sem9'],axis =1,inplace = True)
#add cum_sum
train_pivot_cum_sum = train_pivot_34567_to_9[['Sem3','Sem4','Sem5','Sem6','Sem7']].cumsum(axis = 1)
train_pivot_34567_to_9.drop(['Sem3','Sem4','Sem5','Sem6','Sem7'],axis =1,inplace = True)
train_pivot_34567_to_9 = pd.concat([train_pivot_34567_to_9,train_pivot_cum_sum],axis =1)
train_pivot_34567_to_9 = train_pivot_34567_to_9.rename(columns={'Sem3': 't_m_6_cum',
                                          'Sem4': 't_m_5_cum','Sem5': 't_m_4_cum',
                                                                'Sem6': 't_m_3_cum','Sem7': 't_m_2_cum'})
# add geo_info
train_pivot_34567_to_9 = add_pro_info(train_pivot_34567_to_9)
#add product info
train_pivot_34567_to_9 = add_product(train_pivot_34567_to_9)
train_pivot_34567_to_9.drop(['ID'],axis = 1,inplace = True)
gc.collect()
train_pivot_34567_to_9.head()
train_pivot_34567_to_9.columns.values
len(train_pivot_34567_to_9.columns.values)
train_pivot_34567_to_9.to_csv('train_pivot_34567_to_9.csv')
train_pivot_34567_to_9 = pd.read_csv('train_pivot_34567_to_9.csv',index_col = 0)
Explanation: data for predict week [34567----9], time plus 2 week
End of explanation
pivot_test.head()
pivot_test_week11 = pivot_test.loc[pivot_test['sem10_sem11'] == 11]
pivot_test_week11.reset_index(drop=True,inplace = True)
pivot_test_week11 = pivot_test_week11.fillna(0)
pivot_test_week11.head()
pivot_test_week11.shape
train_56789 = train_dataset.loc[train_dataset['Semana'].isin([5,6,7,8,9]), :].copy()
train_pivot_56789_to_11 = pivot_test_week11.copy()
train_pivot_56789_to_11 = categorical_useful(train_56789,train_pivot_56789_to_11)
del train_56789
gc.collect()
train_pivot_56789_to_11 = define_time_features(train_pivot_56789_to_11, to_predict = 't_plus_2' , t_0 = 11)
train_pivot_56789_to_11 = lin_regr_features(train_pivot_56789_to_11,to_predict ='t_plus_2' , 
                                            semanas_numbers = [5,6,7,8,9],t_0 = 9)
train_pivot_56789_to_11.drop(['Sem3','Sem4'],axis =1,inplace = True)
#add cum_sum
train_pivot_cum_sum = train_pivot_56789_to_11[['Sem5','Sem6','Sem7','Sem8','Sem9']].cumsum(axis = 1)
train_pivot_56789_to_11.drop(['Sem5','Sem6','Sem7','Sem8','Sem9'],axis =1,inplace = True)
train_pivot_56789_to_11 = pd.concat([train_pivot_56789_to_11,train_pivot_cum_sum],axis =1)
train_pivot_56789_to_11 = train_pivot_56789_to_11.rename(columns={'Sem5': 't_m_6_cum',
                                          'Sem6': 't_m_5_cum','Sem7': 't_m_4_cum',
                                                                  'Sem8': 't_m_3_cum','Sem9': 't_m_2_cum'})
# add product_info
train_pivot_56789_to_11 = add_pro_info(train_pivot_56789_to_11)
#
train_pivot_56789_to_11 = add_product(train_pivot_56789_to_11)
train_pivot_56789_to_11.drop(['ID'],axis =1,inplace = True)
for col in train_pivot_56789_to_11.columns.values:
    train_pivot_56789_to_11[col] = train_pivot_56789_to_11[col].astype(np.float32)
train_pivot_56789_to_11.head()
train_pivot_56789_to_11.columns.values
train_pivot_56789_to_11.shape
new_feature = ['id', 'ruta_freq', 'clien_freq', 'agen_freq',
       'prod_freq', 'agen_for_log_de', 'ruta_for_log_de',
       'cliente_for_log_de', 'producto_for_log_de', 'agen_ruta_for_log_de',
       'agen_cliente_for_log_de', 'agen_producto_for_log_de',
       'ruta_cliente_for_log_de', 'ruta_producto_for_log_de',
       'cliente_producto_for_log_de', 'cliente_for_log_sum',
       'cliente_producto_agen_for_log_sum', 'corr', 't_min_6', 't_min_2',
       't_min_3', 't_min_4', 't_min_5', 't2_min_t6', 't3_min_t6',
       't4_min_t6', 't5_min_t6', 't2_min_t3', 't2_min_t4', 't2_min_t5',
       't3_min_t4', 't3_min_t5', 't4_min_t5', 'LR_prod', 'LR_prod_corr',
       't_m_6_cum', 't_m_5_cum', 't_m_4_cum', 't_m_3_cum', 't_m_2_cum',
       'NombreCliente', 'weight', 'weight_per_piece', 'pieces']
len(new_feature)
train_pivot_56789_to_11 = train_pivot_56789_to_11[new_feature]
train_pivot_56789_to_11.head()
train_pivot_56789_to_11['id'] = train_pivot_56789_to_11['id'].astype(int) 
train_pivot_56789_to_11.head()
train_pivot_56789_to_11.to_csv('train_pivot_56789_to_11_private.csv',index = False)
Explanation: test_for private data, week 11
End of explanation
pivot_test_week10 = pivot_test.loc[pivot_test['sem10_sem11'] == 10]
pivot_test_week10.reset_index(drop=True,inplace = True)
pivot_test_week10 = pivot_test_week10.fillna(0)
pivot_test_week10.head()
train_45678 = train_dataset.loc[train_dataset['Semana'].isin([4,5,6,7,8]), :].copy()
train_pivot_45678_to_10 = pivot_test_week10.copy()
train_pivot_45678_to_10 = categorical_useful(train_45678,train_pivot_45678_to_10)
del train_45678
gc.collect()
train_pivot_45678_to_10 = define_time_features(train_pivot_45678_to_10, to_predict = 't_plus_2' , t_0 = 10)
train_pivot_45678_to_10 = lin_regr_features(train_pivot_45678_to_10,to_predict ='t_plus_2' , 
                                            semanas_numbers = [4,5,6,7,8],t_0 = 8)
train_pivot_45678_to_10.drop(['Sem3','Sem9'],axis =1,inplace = True)
#add cum_sum
train_pivot_cum_sum = train_pivot_45678_to_10[['Sem4','Sem5','Sem6','Sem7','Sem8']].cumsum(axis = 1)
train_pivot_45678_to_10.drop(['Sem4','Sem5','Sem6','Sem7','Sem8'],axis =1,inplace = True)
train_pivot_45678_to_10 = pd.concat([train_pivot_45678_to_10,train_pivot_cum_sum],axis =1)
train_pivot_45678_to_10 = train_pivot_45678_to_10.rename(columns={'Sem4': 't_m_6_cum',
                                          'Sem5': 't_m_5_cum','Sem6': 't_m_4_cum',
                                                                  'Sem7': 't_m_3_cum','Sem8': 't_m_2_cum'})
# add product_info
train_pivot_45678_to_10 = add_pro_info(train_pivot_45678_to_10)
#
train_pivot_45678_to_10 = add_product(train_pivot_45678_to_10)
train_pivot_45678_to_10.drop(['ID'],axis =1,inplace = True)
for col in train_pivot_45678_to_10.columns.values:
    train_pivot_45678_to_10[col] = train_pivot_45678_to_10[col].astype(np.float32)
train_pivot_45678_to_10.head()
train_pivot_45678_to_10.columns.values
train_pivot_45678_to_10 = train_pivot_45678_to_10[new_feature]
train_pivot_45678_to_10['id'] = train_pivot_45678_to_10['id'].astype(int)
train_pivot_45678_to_10.head()
train_pivot_45678_to_10.to_pickle('validation_45678_10.pickle')
Explanation: for two week ahead 45678 to 10
End of explanation
train_45678 = train_dataset.loc[train_dataset['Semana'].isin([4,5,6,7,8]), :].copy()
train_pivot_45678_to_9 = pivot_train_zero.loc[(pivot_train['Sem9'].notnull()),:].copy()
train_pivot_45678_to_9 = categorical_useful(train_45678,train_pivot_45678_to_9)
train_pivot_45678_to_9 = define_time_features(train_pivot_45678_to_9, to_predict = 't_plus_1' , t_0 = 9)
del train_45678
gc.collect()
train_pivot_45678_to_9 = lin_regr_features(train_pivot_45678_to_9,to_predict ='t_plus_1',
                                           semanas_numbers = [4,5,6,7,8],t_0 = 8)
train_pivot_45678_to_9['target'] = train_pivot_45678_to_9['Sem9']
train_pivot_45678_to_9.drop(['Sem3','Sem9'],axis =1,inplace = True)
#add cum_sum
train_pivot_cum_sum = train_pivot_45678_to_9[['Sem4','Sem5','Sem6','Sem7','Sem8']].cumsum(axis = 1)
train_pivot_45678_to_9.drop(['Sem4','Sem5','Sem6','Sem7','Sem8'],axis =1,inplace = True)
train_pivot_45678_to_9 = pd.concat([train_pivot_45678_to_9,train_pivot_cum_sum],axis =1,copy = False)
train_pivot_45678_to_9 = train_pivot_45678_to_9.rename(columns={'Sem4': 't_m_5_cum',
                                          'Sem5': 't_m_4_cum','Sem6': 't_m_3_cum', 'Sem7': 't_m_2_cum','Sem8': 't_m_1_cum'})
# add geo_info
train_pivot_45678_to_9 = add_pro_info(train_pivot_45678_to_9)
#add product info
train_pivot_45678_to_9 = add_product(train_pivot_45678_to_9)
train_pivot_45678_to_9.drop(['ID'],axis = 1,inplace = True)
for col in train_pivot_45678_to_9.columns.values:
    train_pivot_45678_to_9[col] = train_pivot_45678_to_9[col].astype(np.float32)
gc.collect()
train_pivot_45678_to_9.head()
train_pivot_45678_to_9.columns.values
train_pivot_45678_to_9 = train_pivot_45678_to_9[['ruta_freq', 'clien_freq', 'agen_freq', 'prod_freq',
       'agen_for_log_de', 'ruta_for_log_de', 'cliente_for_log_de',
       'producto_for_log_de', 'agen_ruta_for_log_de',
       'agen_cliente_for_log_de', 'agen_producto_for_log_de',
       'ruta_cliente_for_log_de', 'ruta_producto_for_log_de',
       'cliente_producto_for_log_de', 'cliente_for_log_sum',
       'cliente_producto_agen_for_log_sum', 'corr', 't_min_1', 't_min_2',
       't_min_3', 't_min_4', 't_min_5', 't1_min_t2', 't1_min_t3',
       't1_min_t4', 't1_min_t5', 't2_min_t3', 't2_min_t4', 't2_min_t5',
       't3_min_t4', 't3_min_t5', 't4_min_t5', 'LR_prod', 'LR_prod_corr',
       'target', 't_m_5_cum', 't_m_4_cum', 't_m_3_cum', 't_m_2_cum',
       't_m_1_cum', 'NombreCliente', 'weight', 'weight_per_piece', 'pieces']] 
train_pivot_45678_to_9.shape
train_pivot_45678_to_9.to_csv('train_pivot_45678_to_9_whole_zero.csv')
# train_pivot_45678_to_9_old = pd.read_csv('train_pivot_45678_to_9.csv',index_col = 0)
sum(train_pivot_45678_to_9['target'].isnull())
Explanation: data for predict week 8&9, time plus 1 week
train_45678 for 8+1 =9
End of explanation
train_34567 = train_dataset.loc[train_dataset['Semana'].isin([3,4,5,6,7]), :].copy()
train_pivot_34567_to_8 = pivot_train_zero.loc[(pivot_train['Sem8'].notnull()),:].copy()
train_pivot_34567_to_8 = categorical_useful(train_34567,train_pivot_34567_to_8)
train_pivot_34567_to_8 = define_time_features(train_pivot_34567_to_8, to_predict = 't_plus_1' , t_0 = 8)
del train_34567
gc.collect()
train_pivot_34567_to_8 = lin_regr_features(train_pivot_34567_to_8,to_predict = 't_plus_1',
                                           semanas_numbers = [3,4,5,6,7],t_0 = 7)
train_pivot_34567_to_8['target'] = train_pivot_34567_to_8['Sem8']
train_pivot_34567_to_8.drop(['Sem8','Sem9'],axis =1,inplace = True)
#add cum_sum
train_pivot_cum_sum = train_pivot_34567_to_8[['Sem3','Sem4','Sem5','Sem6','Sem7']].cumsum(axis = 1)
train_pivot_34567_to_8.drop(['Sem3','Sem4','Sem5','Sem6','Sem7'],axis =1,inplace = True)
train_pivot_34567_to_8 = pd.concat([train_pivot_34567_to_8,train_pivot_cum_sum],axis =1)
train_pivot_34567_to_8 = train_pivot_34567_to_8.rename(columns={'Sem3': 't_m_5_cum','Sem4': 't_m_4_cum',
                                          'Sem5': 't_m_3_cum','Sem6': 't_m_2_cum',
                                                                'Sem7': 't_m_1_cum'})
# add product_info
train_pivot_34567_to_8 = add_pro_info(train_pivot_34567_to_8)
#add product
train_pivot_34567_to_8 = add_product(train_pivot_34567_to_8)
train_pivot_34567_to_8.drop(['ID'],axis = 1,inplace = True)
for col in train_pivot_34567_to_8.columns.values:
    train_pivot_34567_to_8[col] = train_pivot_34567_to_8[col].astype(np.float32)
gc.collect()
train_pivot_34567_to_8.head()
train_pivot_34567_to_8.shape
train_pivot_34567_to_8.columns.values
train_pivot_34567_to_8.to_csv('train_pivot_34567_to_8.csv')
train_pivot_34567_to_8 = pd.read_csv('train_pivot_34567_to_8.csv',index_col = 0)
gc.collect()
Explanation: train_34567 7+1 = 8
End of explanation
train_pivot_xgb_time1 = pd.concat([train_pivot_45678_to_9, train_pivot_34567_to_8],axis = 0,copy = False)
train_pivot_xgb_time1 = train_pivot_xgb_time1[['ruta_freq', 'clien_freq', 'agen_freq', 'prod_freq',
       'agen_for_log_de', 'ruta_for_log_de', 'cliente_for_log_de',
       'producto_for_log_de', 'agen_ruta_for_log_de',
       'agen_cliente_for_log_de', 'agen_producto_for_log_de',
       'ruta_cliente_for_log_de', 'ruta_producto_for_log_de',
       'cliente_producto_for_log_de', 'cliente_for_log_sum',
       'cliente_producto_agen_for_log_sum', 'corr', 't_min_1', 't_min_2',
       't_min_3', 't_min_4', 't_min_5', 't1_min_t2', 't1_min_t3',
       't1_min_t4', 't1_min_t5', 't2_min_t3', 't2_min_t4', 't2_min_t5',
       't3_min_t4', 't3_min_t5', 't4_min_t5', 'LR_prod', 'LR_prod_corr',
       'target', 't_m_5_cum', 't_m_4_cum', 't_m_3_cum', 't_m_2_cum',
       't_m_1_cum', 'NombreCliente', 'weight', 'weight_per_piece', 'pieces']]
train_pivot_xgb_time1.columns.values
train_pivot_xgb_time1.shape
np.sum(train_pivot_xgb_time1.memory_usage())/(1024**3)
train_pivot_xgb_time1.to_csv('train_pivot_xgb_time1_44fea_zero.csv',index = False)
train_pivot_xgb_time1.to_csv('train_pivot_xgb_time1.csv')
del train_pivot_xgb_time1
del train_pivot_45678_to_9
del train_pivot_34567_to_8
gc.collect()
Explanation: concat train_pivot_45678_to_9 & train_pivot_34567_to_8 to perform t_plus_1, train_data is over
End of explanation
pivot_test.head()
pivot_test_week10 = pivot_test.loc[pivot_test['sem10_sem11'] == 10]
pivot_test_week10.reset_index(drop=True,inplace = True)
pivot_test_week10 = pivot_test_week10.fillna(0)
pivot_test_week10.head()
pivot_test_week10.shape
train_56789 = train_dataset.loc[train_dataset['Semana'].isin([5,6,7,8,9]), :].copy()
train_pivot_56789_to_10 = pivot_test_week10.copy()
train_pivot_56789_to_10 = categorical_useful(train_56789,train_pivot_56789_to_10)
del train_56789
gc.collect()
train_pivot_56789_to_10 = define_time_features(train_pivot_56789_to_10, to_predict = 't_plus_1' , t_0 = 10)
train_pivot_56789_to_10 = lin_regr_features(train_pivot_56789_to_10,to_predict ='t_plus_1' , 
                                            semanas_numbers = [5,6,7,8,9],t_0 = 9)
train_pivot_56789_to_10.drop(['Sem3','Sem4'],axis =1,inplace = True)
#add cum_sum
train_pivot_cum_sum = train_pivot_56789_to_10[['Sem5','Sem6','Sem7','Sem8','Sem9']].cumsum(axis = 1)
train_pivot_56789_to_10.drop(['Sem5','Sem6','Sem7','Sem8','Sem9'],axis =1,inplace = True)
train_pivot_56789_to_10 = pd.concat([train_pivot_56789_to_10,train_pivot_cum_sum],axis =1)
train_pivot_56789_to_10 = train_pivot_56789_to_10.rename(columns={'Sem5': 't_m_5_cum',
                                          'Sem6': 't_m_4_cum','Sem7': 't_m_3_cum',
                                                                  'Sem8': 't_m_2_cum','Sem9': 't_m_1_cum'})
# add product_info
train_pivot_56789_to_10 = add_pro_info(train_pivot_56789_to_10)
#
train_pivot_56789_to_10 = add_product(train_pivot_56789_to_10)
train_pivot_56789_to_10.drop(['ID'],axis =1,inplace = True)
for col in train_pivot_56789_to_10.columns.values:
    train_pivot_56789_to_10[col] = train_pivot_56789_to_10[col].astype(np.float32)
train_pivot_56789_to_10.head()
train_pivot_56789_to_10  = train_pivot_56789_to_10[['id','ruta_freq', 'clien_freq', 'agen_freq',
       'prod_freq', 'agen_for_log_de', 'ruta_for_log_de',
       'cliente_for_log_de', 'producto_for_log_de', 'agen_ruta_for_log_de',
       'agen_cliente_for_log_de', 'agen_producto_for_log_de',
       'ruta_cliente_for_log_de', 'ruta_producto_for_log_de',
       'cliente_producto_for_log_de', 'cliente_for_log_sum',
       'cliente_producto_agen_for_log_sum', 'corr', 't_min_1', 't_min_2',
       't_min_3', 't_min_4', 't_min_5', 't1_min_t2', 't1_min_t3',
       't1_min_t4', 't1_min_t5', 't2_min_t3', 't2_min_t4', 't2_min_t5',
       't3_min_t4', 't3_min_t5', 't4_min_t5', 'LR_prod', 'LR_prod_corr',
       't_m_5_cum', 't_m_4_cum', 't_m_3_cum', 't_m_2_cum', 't_m_1_cum',
       'NombreCliente', 'weight', 'weight_per_piece', 'pieces']] 
train_pivot_56789_to_10.head()
train_pivot_56789_to_10.shape
len(train_pivot_56789_to_10.columns.values)
train_pivot_56789_to_10.to_pickle('train_pivot_56789_to_10_44fea_zero.pickle')
Explanation: prepare for test data, for week 10, we use 5,6,7,8,9
End of explanation
train_3456 = train_dataset.loc[train_dataset['Semana'].isin([3,4,5,6]), :].copy()
train_pivot_3456_to_8 = pivot_train.loc[(pivot_train['Sem8'].notnull()),:].copy()
train_pivot_3456_to_8 = categorical_useful(train_3456,train_pivot_3456_to_8)
del train_3456
gc.collect()
train_pivot_3456_to_8 = define_time_features(train_pivot_3456_to_8, to_predict = 't_plus_2' , t_0 = 8)
#notice that the t_0 means different
train_pivot_3456_to_8 = lin_regr_features(train_pivot_3456_to_8,to_predict = 't_plus_2', semanas_numbers = [3,4,5,6],t_0 = 6)
train_pivot_3456_to_8['target'] = train_pivot_3456_to_8['Sem8']
train_pivot_3456_to_8.drop(['Sem7','Sem8','Sem9'],axis =1,inplace = True)
#add cum_sum
train_pivot_cum_sum = train_pivot_3456_to_8[['Sem3','Sem4','Sem5','Sem6']].cumsum(axis = 1)
train_pivot_3456_to_8.drop(['Sem3','Sem4','Sem5','Sem6'],axis =1,inplace = True)
train_pivot_3456_to_8 = pd.concat([train_pivot_3456_to_8,train_pivot_cum_sum],axis =1)
train_pivot_3456_to_8 = train_pivot_3456_to_8.rename(columns={'Sem4': 't_m_4_cum',
                                          'Sem5': 't_m_3_cum','Sem6': 't_m_2_cum', 'Sem3': 't_m_5_cum'})
# add product_info
train_pivot_3456_to_8 = add_pro_info(train_pivot_3456_to_8)
train_pivot_3456_to_8 = add_product(train_pivot_3456_to_8)
train_pivot_3456_to_8.drop(['ID'],axis =1,inplace = True)
train_pivot_3456_to_8.head()
train_pivot_3456_to_8.columns.values
train_pivot_3456_to_8.to_csv('train_pivot_3456_to_8.csv')
Explanation: begin predict for week 11
train_3456 for 6+2 = 8
End of explanation
train_4567 = train_dataset.loc[train_dataset['Semana'].isin([4,5,6,7]), :].copy()
train_pivot_4567_to_9 = pivot_train.loc[(pivot_train['Sem9'].notnull()),:].copy()
train_pivot_4567_to_9 = categorical_useful(train_4567,train_pivot_4567_to_9)
del train_4567
gc.collect()
train_pivot_4567_to_9 = define_time_features(train_pivot_4567_to_9, to_predict = 't_plus_2' , t_0 = 9)
#notice that the t_0 means different
train_pivot_4567_to_9 = lin_regr_features(train_pivot_4567_to_9,to_predict = 't_plus_2', 
                                          semanas_numbers = [4,5,6,7],t_0 = 7)
train_pivot_4567_to_9['target'] = train_pivot_4567_to_9['Sem9']
train_pivot_4567_to_9.drop(['Sem3','Sem8','Sem9'],axis =1,inplace = True)
#add cum_sum
train_pivot_cum_sum = train_pivot_4567_to_9[['Sem7','Sem4','Sem5','Sem6']].cumsum(axis = 1)
train_pivot_4567_to_9.drop(['Sem7','Sem4','Sem5','Sem6'],axis =1,inplace = True)
train_pivot_4567_to_9 = pd.concat([train_pivot_4567_to_9,train_pivot_cum_sum],axis =1)
train_pivot_4567_to_9 = train_pivot_4567_to_9.rename(columns={'Sem4': 't_m_5_cum',
                                          'Sem5': 't_m_4_cum','Sem6': 't_m_3_cum', 'Sem7': 't_m_2_cum'})
# add product_info
train_pivot_4567_to_9 = add_pro_info(train_pivot_4567_to_9)
train_pivot_4567_to_9 = add_product(train_pivot_4567_to_9)
train_pivot_4567_to_9.drop(['ID'],axis =1,inplace = True)
train_pivot_4567_to_9.head()
train_pivot_4567_to_9.columns.values
train_pivot_4567_to_9.to_csv('train_pivot_4567_to_9.csv')
Explanation: train_4567 for 7 + 2 = 9
End of explanation
train_pivot_xgb_time2 = pd.concat([train_pivot_3456_to_8, train_pivot_4567_to_9],axis = 0,copy = False)
train_pivot_xgb_time2.columns.values
train_pivot_xgb_time2.shape
train_pivot_xgb_time2.to_csv('train_pivot_xgb_time2_38fea.csv')
train_pivot_xgb_time2 = pd.read_csv('train_pivot_xgb_time2.csv',index_col = 0)
train_pivot_xgb_time2.head()
del train_pivot_3456_to_8
del train_pivot_4567_to_9
del train_pivot_xgb_time2
del train_pivot_34567_to_8
del train_pivot_45678_to_9
del train_pivot_xgb_time1
gc.collect()
Explanation: concat
End of explanation
pivot_test_week11 = pivot_test_new.loc[pivot_test_new['Semana'] == 11]
pivot_test_week11.reset_index(drop=True,inplace = True)
pivot_test_week11.head()
pivot_test_week11.shape
train_6789 = train_dataset.loc[train_dataset['Semana'].isin([6,7,8,9]), :].copy()
train_pivot_6789_to_11 = pivot_test_week11.copy()
train_pivot_6789_to_11 = categorical_useful(train_6789,train_pivot_6789_to_11)
del train_6789
gc.collect()
train_pivot_6789_to_11 = define_time_features(train_pivot_6789_to_11, to_predict = 't_plus_2' , t_0 = 11)
train_pivot_6789_to_11 = lin_regr_features(train_pivot_6789_to_11,to_predict ='t_plus_2' ,
                                           semanas_numbers = [6,7,8,9],t_0 = 9)
train_pivot_6789_to_11.drop(['Sem3','Sem4','Sem5'],axis =1,inplace = True)
#add cum_sum
train_pivot_cum_sum = train_pivot_6789_to_11[['Sem6','Sem7','Sem8','Sem9']].cumsum(axis = 1)
train_pivot_6789_to_11.drop(['Sem6','Sem7','Sem8','Sem9'],axis =1,inplace = True)
train_pivot_6789_to_11 = pd.concat([train_pivot_6789_to_11,train_pivot_cum_sum],axis =1)
train_pivot_6789_to_11 = train_pivot_6789_to_11.rename(columns={'Sem6': 't_m_5_cum',
                                          'Sem7': 't_m_4_cum', 'Sem8': 't_m_3_cum','Sem9': 't_m_2_cum'})
# add product_info
train_pivot_6789_to_11 = add_pro_info(train_pivot_6789_to_11)
train_pivot_6789_to_11 = add_product(train_pivot_6789_to_11)
train_pivot_6789_to_11.drop(['ID'],axis = 1,inplace = True)
train_pivot_6789_to_11.head()
train_pivot_6789_to_11.shape
train_pivot_6789_to_11.to_pickle('train_pivot_6789_to_11_new.pickle')
Explanation: for test data week 11, we use 6,7,8,9
End of explanation
% time pivot_train_categorical_useful = categorical_useful(train_dataset,pivot_train,is_train = True)
% time pivot_train_categorical_useful = categorical_useful(train_dataset,pivot_train,is_train = True)
pivot_train_categorical_useful_train.to_csv('pivot_train_categorical_useful_with_nan.csv')
pivot_train_categorical_useful_train = pd.read_csv('pivot_train_categorical_useful_with_nan.csv',index_col = 0)
pivot_train_categorical_useful_train.head()
Explanation: over
End of explanation
pivot_train_categorical_useful.head()
pivot_train_categorical_useful_time = define_time_features(pivot_train_categorical_useful,
                                                                 to_predict = 't_plus_1' , t_0 = 8)
pivot_train_categorical_useful_time.head()
pivot_train_categorical_useful_time.columns
Explanation: create time feature
End of explanation
# Linear regression features 
pivot_train_categorical_useful_time_LR = lin_regr_features(pivot_train_categorical_useful_time, semanas_numbers = [3,4,5,6,7])
pivot_train_categorical_useful_time_LR.head()
pivot_train_categorical_useful_time_LR.columns
pivot_train_categorical_useful_time_LR.to_csv('pivot_train_categorical_useful_time_LR.csv')
pivot_train_categorical_useful_time_LR = pd.read_csv('pivot_train_categorical_useful_time_LR.csv',index_col = 0)
pivot_train_categorical_useful_time_LR.head()
Explanation: fit mean feature on target
End of explanation
# pivot_train_canal = pd.get_dummies(pivot_train_categorical_useful_train['Canal_ID'])
# pivot_train_categorical_useful_train = pivot_train_categorical_useful_train.join(pivot_train_canal)
# pivot_train_categorical_useful_train.head()
Explanation: add dummy feature
End of explanation
%ls
pre_product = pd.read_csv('preprocessed_products.csv',index_col = 0)
pre_product.head()
pre_product['weight_per_piece'] = pd.to_numeric(pre_product['weight_per_piece'], errors='coerce')
pre_product['weight'] = pd.to_numeric(pre_product['weight'], errors='coerce')
pre_product['pieces'] = pd.to_numeric(pre_product['pieces'], errors='coerce')
pivot_train_categorical_useful_time_LR_weight = pd.merge(pivot_train_categorical_useful_time_LR,
                                                         pre_product[['ID','weight','weight_per_piece']],
                                                         left_on = 'Producto_ID',right_on = 'ID',how = 'left')
pivot_train_categorical_useful_time_LR_weight.head()
pivot_train_categorical_useful_time_LR_weight = pd.merge(pivot_train_categorical_useful_time_LR,
                                                         pre_product[['ID','weight','weight_per_piece']],
                                                         left_on = 'Producto_ID',right_on = 'ID',how = 'left')
pivot_train_categorical_useful_time_LR_weight.head()
pivot_train_categorical_useful_time_LR_weight.to_csv('pivot_train_categorical_useful_time_LR_weight.csv')
pivot_train_categorical_useful_time_LR_weight = pd.read_csv('pivot_train_categorical_useful_time_LR_weight.csv',index_col = 0)
pivot_train_categorical_useful_time_LR_weight.head()
Explanation: add product feature
End of explanation
%cd '/media/siyuan/0009E198000CD19B/bimbo/origin'
%ls
cliente_tabla = pd.read_csv('cliente_tabla.csv')
town_state = pd.read_csv('town_state.csv')
town_state['town_id'] = town_state['Town'].str.split()
town_state['town_id'] = town_state['Town'].str.split(expand = True)
train_basic_feature = pivot_train_categorical_useful_time_LR_weight[['Cliente_ID','Producto_ID','Agencia_ID']]
cliente_per_town = pd.merge(train_basic_feature,cliente_tabla,on = 'Cliente_ID',how= 'inner' )
cliente_per_town = pd.merge(cliente_per_town,town_state[['Agencia_ID','town_id']],on = 'Agencia_ID',how= 'inner' )
cliente_per_town_count = cliente_per_town[['NombreCliente','town_id']].groupby('town_id').count().reset_index()
cliente_per_town_count['NombreCliente'] = cliente_per_town_count['NombreCliente']/float(100000)
cliente_per_town_count_final = pd.merge(cliente_per_town[['Cliente_ID','Producto_ID','Agencia_ID','town_id']],
                                        cliente_per_town_count,on = 'town_id',how = 'left')
pivot_train_categorical_useful_time_LR_weight_town = pd.merge(pivot_train_categorical_useful_time_LR_weight,
                                                              cliente_per_town_count_final[['Cliente_ID','Producto_ID','NombreCliente']],
                                                              on = ['Cliente_ID','Producto_ID'],how = 'left')
cliente_tabla.head()
town_state.head()
town_state['town_id'] = town_state['Town'].str.split()
town_state['town_id'] = town_state['Town'].str.split(expand = True)
town_state.head()
pivot_train_categorical_useful_time_LR_weight.columns.values
train_basic_feature = pivot_train_categorical_useful_time_LR_weight[['Cliente_ID','Producto_ID','Agencia_ID']]
cliente_per_town = pd.merge(train_basic_feature,cliente_tabla,on = 'Cliente_ID',how= 'inner' )
cliente_per_town = pd.merge(cliente_per_town,town_state[['Agencia_ID','town_id']],on = 'Agencia_ID',how= 'inner' )
cliente_per_town.head()
cliente_per_town_count = cliente_per_town[['NombreCliente','town_id']].groupby('town_id').count().reset_index()
cliente_per_town_count['NombreCliente'] = cliente_per_town_count['NombreCliente']/float(100000)
cliente_per_town_count.head()
cliente_per_town_count_final = pd.merge(cliente_per_town[['Cliente_ID','Producto_ID','Agencia_ID','town_id']],
                                        cliente_per_town_count,on = 'town_id',how = 'left')
cliente_per_town_count_final.head()
pivot_train_categorical_useful_time_LR_weight_town = pd.merge(pivot_train_categorical_useful_time_LR_weight,
                                                              cliente_per_town_count_final[['Cliente_ID','Producto_ID','NombreCliente']],
                                                              on = ['Cliente_ID','Producto_ID'],how = 'left')
pivot_train_categorical_useful_time_LR_weight_town.head()
pivot_train_categorical_useful_time_LR_weight_town.columns.values
Explanation: add town feature
End of explanation
train_pivot_xgb_time1.columns.values
train_pivot_xgb_time1 = train_pivot_xgb_time1.drop(['Cliente_ID','Producto_ID','Agencia_ID',
                                                                            'Ruta_SAK','Canal_ID'],axis = 1)
pivot_train_categorical_useful_train_time_no_nan = pivot_train_categorical_useful_train[pivot_train_categorical_useful_train['Sem8'].notnull()]
# pivot_train_categorical_useful_train_time_no_nan = pivot_train_categorical_useful_train[pivot_train_categorical_useful_train['Sem9'].notnull()]
pivot_train_categorical_useful_train_time_no_nan_sample = pivot_train_categorical_useful_train_time_no_nan.sample(1000000)
train_feature = pivot_train_categorical_useful_train_time_no_nan_sample.drop(['Sem8','Sem9'],axis = 1)
train_label = pivot_train_categorical_useful_train_time_no_nan_sample[['Sem8','Sem9']]
#seperate train and test data
# datasource: sparse_week_Agencia_Canal_Ruta_normalized_csr label:train_label
%time train_set, valid_set, train_labels, valid_labels = train_test_split(train_feature,\
                                                                                  train_label, test_size=0.10)
# dtrain = xgb.DMatrix(train_feature,label = train_label['Sem8'],missing=NaN)
dtrain = xgb.DMatrix(train_feature,label = train_label['Sem8'],missing=NaN)
param = {'booster':'gbtree',
         'nthread': 7,
         'max_depth':6, 
         'eta':0.2,
         'silent':0,
         'subsample':0.7, 
         'objective':'reg:linear',
         'eval_metric':'rmse',
         'colsample_bytree':0.7}
# param = {'eta':0.1, 'eval_metric':'rmse','nthread': 8}
# evallist  = [(dvalid,'eval'), (dtrain,'train')]
num_round = 1000
# plst = param.items()
# bst = xgb.train( plst, dtrain, num_round, evallist )
cvresult = xgb.cv(param, dtrain, num_round, nfold=5,show_progress=True,show_stdv=False,
                        seed = 0, early_stopping_rounds=10)
print(cvresult.tail())
Explanation: begin xgboost training
End of explanation
# xgb.plot_importance(cvresult)
Explanation: for 1 week later
cv rmse 0.451181 with dummy canal, time regr,
cv rmse 0.450972 without dummy canal, time regr,
cv rmse 0.4485676 without dummy canal, time regr, producto info
cv rmse 0.4487434 without dummy canal, time regr, producto info, cliente_per_town
for 2 week later
cv rmse 0.4513236 without dummy canal, time regr, producto info
End of explanation
<END_TASK>