<jupyter_start><jupyter_text>**Adding date and time features **<jupyter_code>data['air_visit_data']['DayInMonth'] = data['air_visit_data']['visit_date'].dt.day
data['air_visit_data']['Month']  = data['air_visit_data']['visit_date'].dt.month
data['air_visit_data']['DayOfWeek'] = data['air_visit_data']['visit_date'].dt.weekday
data['air_visit_data']['Year'] = data['air_visit_data']['visit_date'].dt.year
data['air_visit_data']['Quarter'] = data['air_visit_data']['visit_date'].dt.quarter
data['air_reserve']['Latency'] = (data['air_reserve']['visit_datetime'] - data['air_reserve']['reserve_datetime']).dt.days
tmp1 = data['air_reserve'].groupby(['air_store_id','visit_datetime'], as_index=False)[['Latency', 'reserve_visitors']].mean()
tmp2 = data['air_reserve'].groupby(['air_store_id','visit_datetime'], as_index=False)[['Latency', 'reserve_visitors']].sum()
data['air_reserve'] = pd.merge(tmp1, tmp2, how='inner', on=['air_store_id','visit_datetime'])
data['hpg_reserve']['Latency'] = (data['hpg_reserve']['visit_datetime'] - data['hpg_reserve']['reserve_datetime']).dt.days
tmp1 = data['hpg_reserve'].groupby(['air_store_id','visit_datetime'], as_index=False)[['Latency', 'reserve_visitors']].mean()
tmp2 = data['hpg_reserve'].groupby(['air_store_id','visit_datetime'], as_index=False)[['Latency', 'reserve_visitors']].sum()
data['hpg_reserve'] = pd.merge(tmp1, tmp2, how='inner', on=['air_store_id','visit_datetime'])
data['hpg_reserve']['Visit_DayInMonth'] = data['hpg_reserve']['visit_datetime'].dt.day
data['hpg_reserve']['Visit_Month']  = data['hpg_reserve']['visit_datetime'].dt.month
data['hpg_reserve']['Visit_DayOfWeek'] = data['hpg_reserve']['visit_datetime'].dt.weekday
data['hpg_reserve']['Visit_Year'] = data['hpg_reserve']['visit_datetime'].dt.year
data['hpg_reserve']['Visit_Quarter'] = data['hpg_reserve']['visit_datetime'].dt.quarter
data['hpg_reserve']['Visit_Hour'] = data['hpg_reserve']['visit_datetime'].dt.hour

# data['hpg_reserve']['Reserve_DayInMonth'] = data['hpg_reserve']['reserve_datetime'].dt.day
# data['hpg_reserve']['Reserve_Month']  = data['hpg_reserve']['reserve_datetime'].dt.month
# data['hpg_reserve']['Reserve_DayOfWeek'] = data['hpg_reserve']['reserve_datetime'].dt.weekday
# data['hpg_reserve']['Reserve_Year'] = data['hpg_reserve']['reserve_datetime'].dt.year
# data['hpg_reserve']['Reserve_Quarter'] = data['hpg_reserve']['reserve_datetime'].dt.quarter
# data['hpg_reserve']['Reserve_Hour'] = data['hpg_reserve']['reserve_datetime'].dt.hour

# data['hpg_reserve']['Latency'] = (data['hpg_reserve']['visit_datetime'] - data['hpg_reserve']['reserve_datetime']).dt.days
data['air_reserve']['Visit_DayInMonth'] = data['air_reserve']['visit_datetime'].dt.day
data['air_reserve']['Visit_Month']  = data['air_reserve']['visit_datetime'].dt.month
data['air_reserve']['Visit_DayOfWeek'] = data['air_reserve']['visit_datetime'].dt.weekday
data['air_reserve']['Visit_Year'] = data['air_reserve']['visit_datetime'].dt.year
data['air_reserve']['Visit_Quarter'] = data['air_reserve']['visit_datetime'].dt.quarter
data['air_reserve']['Visit_Hour'] = data['air_reserve']['visit_datetime'].dt.hour

# data['air_reserve']['Reserve_DayInMonth'] = data['air_reserve']['reserve_datetime'].dt.day
# data['air_reserve']['Reserve_Month']  = data['air_reserve']['reserve_datetime'].dt.month
# data['air_reserve']['Reserve_DayOfWeek'] = data['air_reserve']['reserve_datetime'].dt.weekday
# data['air_reserve']['Reserve_Year'] = data['air_reserve']['reserve_datetime'].dt.year
# data['air_reserve']['Reserve_Quarter'] = data['air_reserve']['reserve_datetime'].dt.quarter
# data['air_reserve']['Reserve_Hour'] = data['air_reserve']['reserve_datetime'].dt.hour

# data['air_reserve']['Latency'] = (data['air_reserve']['visit_datetime'] - data['air_reserve']['reserve_datetime']).dt.days
data['air_reserve'].head()<jupyter_output><empty_output><jupyter_text>**Adding names of cities**<jupyter_code>def Major_Cit(name):
    if name.startswith("Tōkyō-to"):
        return "Tokyo"
    elif name.startswith("Fukuoka-ken"):
        return "Fukuoka-ken"
    elif name.startswith("Ōsaka-fu"):
        return "Ōsaka-fu"
    elif name.startswith("Hokkaidō"):
        return "Hokkaidō"
    elif name.startswith("Hyōgo-ken"):
        return "Hyōgo-ken"
    elif name.startswith("Niigata-ken"):
        return "Niigata-ken"
    elif name.startswith("Hiroshima-ken"):
        return "Hiroshima-ken"
    elif name.startswith("Niigata-ken"):
        return "Niigata-ken"
    elif name.startswith("Shizuoka-ken"):
        return "Shizuoka-ken"
    elif name.startswith("Miyagi-ken"):
        return "Miyagi-ken"
    elif name.startswith("Osaka"):
        return "Ōsaka-fu"  
    elif name.startswith("Saitama-ken"):
        return "Saitama-ken"
    elif name.startswith("Kanagawa-ken"):
        return "Kanagawa-ken"
    else:
        return "Other"
# Find_them = data['hpg_store_info'] [ data['hpg_store_info']['city_name'] == 'Other' ]
# Find_them.to_csv('GIND.csv', index=None)
data['air_store_info']['city_name'] = data['air_store_info']['air_area_name'].apply(Major_Cit)
data['hpg_store_info']['city_name'] = data['hpg_store_info']['hpg_area_name'].apply(Major_Cit)
from sklearn import *
label = preprocessing.LabelEncoder()
data['air_store_info']['city_name']  = label.fit_transform(data['air_store_info']['city_name'])
data['hpg_store_info']['city_name']  = label.fit_transform(data['hpg_store_info']['city_name'])<jupyter_output><empty_output><jupyter_text>**Adding sub genere**<jupyter_code>data['air_store_info']['air_genre_name'].value_counts()
def Sub_Genre(name):
    if name in ['Izakaya','Dining bar','Bar/Cocktail']:
        return "Bar"
    elif name in ['Italian/French','Japanese food','Yakiniku/Korean food','Western food']:
        return "Meals"
    elif name in ['Cafe/Sweets','Okonomiyaki/Monja/Teppanyaki','Other']:
        return "Pastry"
    elif name in ['Creative cuisine','International cuisine','Asian']:
        return "Cuisine"
    elif name in ['Karaoke/Party']:
        return "Karaoke"
    else:
        raise Exception
data['air_store_info']['air_sub_genre_name'] = data['air_store_info']['air_genre_name'].apply(Sub_Genre)
data['air_store_info']['air_sub_genre_name']  = label.fit_transform(data['air_store_info']['air_sub_genre_name'])
data['air_store_info'].tail()<jupyter_output><empty_output><jupyter_text>**Calculate distance in kilometers from latitude and longitude**<jupyter_code>air_latslong_citi = data['air_store_info'][['latitude','longitude','city_name']]
air_latslong = air_latslong_citi.apply(lambda r: ','.join([str(r['latitude']), str(r['longitude'])]), axis=1)
set_latslong = list(set(air_latslong))
from tqdm import tqdm
import geopy.distance
pointer_distances = []     
for cords in tqdm(set_latslong):
    lat1 = float(cords.split(',')[0])
    long1 = float(cords.split(',')[1])
    coords_1 = (lat1, long1)
    for cord2 in set_latslong:
        lat2 = float(cord2.split(',')[0])
        long2 = float(cord2.split(',')[1])
        coords_2 = (lat2, long2)
        pointer_distances.append(geopy.distance.vincenty(coords_1, coords_2).km)
print ("Mean of all distances is" , np.mean(pointer_distances) ) 
print ("Max of all distances is" , np.max(pointer_distances) )
print ("Std of all distances is" , np.std(pointer_distances) ) 
air_latslong_citi = data['air_store_info'][['latitude','longitude','city_name']]
air_latslong = air_latslong_citi.apply(lambda r: ','.join([str(r['latitude']), str(r['longitude'])]), axis=1)
set_latslong = list(set(air_latslong))

def Radar_air_store(cols):
    latitudex1 = cols[0]
    longitudex1 = cols[1]
    coords_1 = (latitudex1, longitudex1)
    distances = []     
    for cords in set_latslong:
        latitudex2 = float(cords.split(',')[0])
        longitudex2 = float(cords.split(',')[1])
        coords_2 = (latitudex2, longitudex2)
        distances.append((geopy.distance.vincenty(coords_1, coords_2).km))
    limit =  1000
  
    return len([c for c in distances if c > limit])

def Nearest(cols):
    latitudex1 = cols[0]
    longitudex1 = cols[1]
    coords_1 = (latitudex1, longitudex1)
    distances = []
    for cords in set_latslong:
        latitudex2 = float(cords.split(',')[0])
        longitudex2 = float(cords.split(',')[1])
        coords_2 = (latitudex2, longitudex2)
        distances.append((geopy.distance.vincenty(coords_1, coords_2).km))
    distances = [c for c in distances if c > 0]
    return np.min(distances)

def Farthest(cols):
    latitudex1 = cols[0]
    longitudex1 = cols[1]
    coords_1 = (latitudex1, longitudex1)
    distances = []
    for cords in set_latslong:
        latitudex2 = float(cords.split(',')[0])
        longitudex2 = float(cords.split(',')[1])
        coords_2 = (latitudex2, longitudex2)
        distances.append((geopy.distance.vincenty(coords_1, coords_2).km))
    distances = [c for c in distances if c > 0]
    return np.max(distances)
data['air_store_info']['Vicinity'] = data['air_store_info'][['latitude','longitude']].apply(Radar_air_store, axis=1)
data['air_store_info']['Nearest Distance'] = data['air_store_info'][['latitude','longitude']].apply(Nearest ,  axis=1)
data['air_store_info']['Farthest Distance'] = data['air_store_info'][['latitude','longitude']].apply(Farthest, axis =1)
data['air_store_info'].head()
hpg_latslong_citi = data['hpg_store_info'][['latitude','longitude','city_name']]
hpg_latslong = hpg_latslong_citi.apply(lambda r: ','.join([str(r['latitude']), str(r['longitude'])]), axis=1)
set_latslong = list(set(hpg_latslong))

def Radar_hpg_store(cols):
    latitudex1 = cols[0]
    longitudex1 = cols[1]
    coords_1 = (latitudex1, longitudex1)
    distances = []     
    for cords in set_latslong:
        latitudex2 = float(cords.split(',')[0])
        longitudex2 = float(cords.split(',')[1])
        coords_2 = (latitudex2, longitudex2)
        distances.append((geopy.distance.vincenty(coords_1, coords_2).km))
    limit =  1000
    return len([c for c in distances if c > limit])

def Nearest(cols):
    latitudex1 = cols[0]
    longitudex1 = cols[1]
    coords_1 = (latitudex1, longitudex1)
    distances = []
    for cords in set_latslong:
        latitudex2 = float(cords.split(',')[0])
        longitudex2 = float(cords.split(',')[1])
        coords_2 = (latitudex2, longitudex2)
        distances.append((geopy.distance.vincenty(coords_1, coords_2).km))
    distances = [c for c in distances if c > 0]
    return np.min(distances)

def Farthest(cols):
    latitudex1 = cols[0]
    longitudex1 = cols[1]
    coords_1 = (latitudex1, longitudex1)
    distances = []
    for cords in set_latslong:
        latitudex2 = float(cords.split(',')[0])
        longitudex2 = float(cords.split(',')[1])
        coords_2 = (latitudex2, longitudex2)
        distances.append((geopy.distance.vincenty(coords_1, coords_2).km))
    distances = [c for c in distances if c > 0]
    return np.max(distances)
data['hpg_store_info']['Vicinity'] = data['hpg_store_info'][['latitude','longitude']].apply(Radar_hpg_store, axis=1)
data['hpg_store_info']['Nearest Distance'] = data['hpg_store_info'][['latitude','longitude']].apply(Nearest ,  axis=1)
data['hpg_store_info']['Farthest Distance'] = data['hpg_store_info'][['latitude','longitude']].apply(Farthest, axis =1)
data['hpg_store_info'].head()
data['air_reserve'].head()
data['sample_submission'] = pd.read_csv('../input/sample_submission.csv')
data['sample_submission']['visit_date'] = data['sample_submission']['id'].map(lambda x: str(x).split('_')[2])
data['sample_submission']['air_store_id'] = data['sample_submission']['id'].map(lambda x: '_'.join(x.split('_')[:2]))
data['sample_submission']['visit_date'] = pd.to_datetime(data['sample_submission']['visit_date'])
data['sample_submission']['DayOfWeek'] = data['sample_submission']['visit_date'].dt.dayofweek
data['sample_submission']['Year'] = data['sample_submission']['visit_date'].dt.year
data['sample_submission']['Month'] = data['sample_submission']['visit_date'].dt.month
data['sample_submission']['Quarter'] = data['sample_submission']['visit_date'].dt.quarter

data['sample_submission']['DayInMonth'] = data['sample_submission']['visit_date'].dt.day
data['sample_submission']['visit_date'] = data['sample_submission']['visit_date'].dt.date

unique_stores = data['sample_submission']['air_store_id'].unique()
stores = pd.concat([pd.DataFrame({'air_store_id': unique_stores, 'DayOfWeek': [i]*len(unique_stores)}) for i in range(7)], axis=0, ignore_index=True).reset_index(drop=True)
#sure it can be compressed...
tmp = data['air_visit_data'].groupby(['air_store_id','DayOfWeek'], as_index=False)['visitors'].min().rename(columns={'visitors':'min_visitors'})
stores = pd.merge(stores, tmp, how='left', on=['air_store_id','DayOfWeek']) 
tmp = data['air_visit_data'].groupby(['air_store_id','DayOfWeek'], as_index=False)['visitors'].mean().rename(columns={'visitors':'mean_visitors'})
stores = pd.merge(stores, tmp, how='left', on=['air_store_id','DayOfWeek'])
tmp = data['air_visit_data'].groupby(['air_store_id','DayOfWeek'], as_index=False)['visitors'].median().rename(columns={'visitors':'median_visitors'})
stores = pd.merge(stores, tmp, how='left', on=['air_store_id','DayOfWeek'])
tmp = data['air_visit_data'].groupby(['air_store_id','DayOfWeek'], as_index=False)['visitors'].max().rename(columns={'visitors':'max_visitors'})
stores = pd.merge(stores, tmp, how='left', on=['air_store_id','DayOfWeek'])
tmp = data['air_visit_data'].groupby(['air_store_id','DayOfWeek'], as_index=False)['visitors'].count().rename(columns={'visitors':'count_observations'})
stores = pd.merge(stores, tmp, how='left', on=['air_store_id','DayOfWeek']) 
stores = pd.merge(stores, data['air_store_info'], how='left', on=['air_store_id']) 
from sklearn import *
# NEW FEATURES FROM <NAME>
stores['air_genre_name'] = stores['air_genre_name'].map(lambda x: str(str(x).replace('/',' ')))
stores['air_area_name'] = stores['air_area_name'].map(lambda x: str(str(x).replace('-',' ')))
lbl = preprocessing.LabelEncoder()
for i in range(10):
    stores['air_genre_name'+str(i)] = lbl.fit_transform(stores['air_genre_name'].map(lambda x: str(str(x).split(' ')[i]) if len(str(x).split(' '))>i else ''))
    stores['air_area_name'+str(i)] = lbl.fit_transform(stores['air_area_name'].map(lambda x: str(str(x).split(' ')[i]) if len(str(x).split(' '))>i else ''))
stores['air_genre_name'] = lbl.fit_transform(stores['air_genre_name'])
stores['air_area_name'] = lbl.fit_transform(stores['air_area_name'])
data['date_info'] = pd.read_csv('../input/date_info.csv').rename(columns={'calendar_date':'visit_date'})
data['date_info'].head(20)
data['air_visit_data'].head()
data['date_info']['visit_date'] = pd.to_datetime(data['date_info']['visit_date']).dt.date
data['air_visit_data']['visit_date'] = pd.to_datetime(data['air_visit_data']['visit_date']).dt.date
data['date_info']['day_of_week'] = lbl.fit_transform(data['date_info']['day_of_week'])
traindf = pd.merge(data['air_visit_data'], data['date_info'], how='left', on=['visit_date'])
testdf = pd.merge(data['sample_submission'], data['date_info'], how='left', on=['visit_date']) 
train = pd.merge(traindf, stores, how='left', on=['air_store_id','DayOfWeek']) 
test = pd.merge(testdf, stores, how='left', on=['air_store_id','DayOfWeek'])
data['air_reserve']
data['air_reserve'] = data['air_reserve'].rename(columns={'visit_datetime':'visit_date'})
data['hpg_reserve'] = data['hpg_reserve'].rename(columns={'visit_datetime':'visit_date'})
data['air_reserve']['visit_date'] = pd.to_datetime(data['air_reserve']['visit_date']).dt.date
data['hpg_reserve']['visit_date'] = pd.to_datetime(data['hpg_reserve']['visit_date']).dt.date
for df in ['air_reserve','hpg_reserve']:
    data[df] = data[df].rename(columns={'visit_datetime':'visit_date'})

    train = pd.merge(train, data[df], how='left', on=['air_store_id','visit_date']) 
    test = pd.merge(test, data[df], how='left', on=['air_store_id','visit_date'])
train['id'] = train.apply(lambda r: '_'.join([str(r['air_store_id']), str(r['visit_date'])]), axis=1)
# NEW FEATURES FROM JMBULL
train['date_int'] = train['visit_date'].apply(lambda x: x.strftime('%Y%m%d')).astype(int)
test['date_int'] = test['visit_date'].apply(lambda x: x.strftime('%Y%m%d')).astype(int)
train['var_max_lat'] = train['latitude'].max() - train['latitude']
train['var_max_long'] = train['longitude'].max() - train['longitude']
test['var_max_lat'] = test['latitude'].max() - test['latitude']
test['var_max_long'] = test['longitude'].max() - test['longitude']

# NEW FEATURES FROM Georgii Vyshnia
train['lon_plus_lat'] = train['longitude'] + train['latitude'] 
test['lon_plus_lat'] = test['longitude'] + test['latitude']

lbl = preprocessing.LabelEncoder()
train['air_store_id2'] = lbl.fit_transform(train['air_store_id'])
test['air_store_id2'] = lbl.transform(test['air_store_id'])

col = [c for c in train if c not in ['id', 'air_store_id', 'visit_date','visitors']]
train = train.fillna(-1)
test = test.fillna(-1)
train[col].head()
train.describe()
def RMSLE(y, pred):
    return metrics.mean_squared_error(y, pred)**0.5<jupyter_output><empty_output><jupyter_text>**Cross Validation Strategy**<jupyter_code>X = np.array(train[col])
y = np.log1p(train['visitors'].values)
from lightgbm import LGBMRegressor
light = LGBMRegressor(learning_rate=0.5, num_leaves=3400, max_depth=30, max_bin=300, min_child_weight=5)
from sklearn.model_selection import TimeSeriesSplit
tscv = TimeSeriesSplit(n_splits=5)
X = np.array(train[col[:10]])
y = np.log1p(train['visitors'].values)
scores = []
for train_index, test_index in tscv.split(X):
    X_train, X_test = X[train_index], X[test_index]
    y_train, y_test = y[train_index], y[test_index]
    light.fit(X_train,y_train)
    lightpred = light.predict(X_test)
    score = RMSLE(y_test, lightpred)
    print('RMSE LightGBM: ', score)
    scores.append(score)
print ("Mean Score:", np.mean(scores))
knn = neighbors.KNeighborsRegressor(n_jobs=-1, n_neighbors=4)
knn.fit(train[col], np.log1p(train['visitors'].values))
knnpred = knn.predict(train[col])
print('RMSE KNeighborsRegressor: ', RMSLE(np.log1p(train['visitors'].values), knnpred))
from lightgbm import LGBMRegressor
light = LGBMRegressor(learning_rate=0.3, num_leaves=1400, max_depth=15, max_bin=300, min_child_weight=5)
light.fit(train[col], np.log1p(train['visitors'].values))
lightpred = light.predict(train[col])
print('RMSE LightGBM: ', RMSLE(np.log1p(train['visitors'].values), lightpred))
pd.DataFrame({'Features' : train[col].columns, 'Imp': light.feature_importances_}).sort_values(by='Imp', ascending=False)
xgb = XGBRegressor(learning_rate=0.2, seed=3, n_estimators=200, subsample=0.8, 
                      colsample_bytree=0.8, max_depth =10)
xgb.fit(train[col], np.log1p(train['visitors'].values))
xgbpred = xgb.predict(train[col])
print('RMSE XGBRegressor: ', RMSLE(np.log1p(train['visitors'].values), xgbpred))
model1 = ensemble.GradientBoostingRegressor(learning_rate=0.2, n_estimators=200, subsample=0.8, 
                      max_depth =10)
model2 = neighbors.KNeighborsRegressor(n_jobs=-1, n_neighbors=4)
model3 = XGBRegressor(learning_rate=0.2, n_estimators=200, subsample=0.8, 
                      colsample_bytree=0.8, max_depth =10)
model1.fit(train[col], np.log1p(train['visitors'].values))
model2.fit(train[col], np.log1p(train['visitors'].values))
model3.fit(train[col], np.log1p(train['visitors'].values))
preds1 = model1.predict(train[col])
preds2 = model2.predict(train[col])
preds3 = model3.predict(train[col])

print('RMSE GradientBoostingRegressor: ', RMSLE(np.log1p(train['visitors'].values), preds1))
print('RMSE KNeighborsRegressor: ', RMSLE(np.log1p(train['visitors'].values), preds2))
print('RMSE XGBRegressor: ', RMSLE(np.log1p(train['visitors'].values), preds3))
preds1 = model1.predict(test[col])
preds2 = model2.predict(test[col])
preds3 = model3.predict(test[col])

test['visitors'] = 0.3*preds1+0.3*preds2+0.4*preds3
test['visitors'] = np.expm1(test['visitors']).clip(lower=0.)
sub1 = test[['id','visitors']].copy()
del train; del data;
import glob,re
# from hklee
# https://www.kaggle.com/zeemeen/weighted-mean-comparisons-lb-0-497-1st/code
dfs = { re.search('/([^/\.]*)\.csv', fn).group(1):
    pd.read_csv(fn)for fn in glob.glob('../input/*.csv')}

for k, v in dfs.items(): locals()[k] = v

wkend_holidays = date_info.apply(
    (lambda x:(x.day_of_week=='Sunday' or x.day_of_week=='Saturday') and x.holiday_flg==1), axis=1)
date_info.loc[wkend_holidays, 'holiday_flg'] = 0
date_info['weight'] = ((date_info.index + 1) / len(date_info)) ** 5  

visit_data = air_visit_data.merge(date_info, left_on='visit_date', right_on='calendar_date', how='left')
visit_data.drop('calendar_date', axis=1, inplace=True)
visit_data['visitors'] = visit_data.visitors.map(pd.np.log1p)

wmean = lambda x:( (x.weight * x.visitors).sum() / x.weight.sum() )
visitors = visit_data.groupby(['air_store_id', 'day_of_week', 'holiday_flg']).apply(wmean).reset_index()
visitors.rename(columns={0:'visitors'}, inplace=True) # cumbersome, should be better ways.

sample_submission['air_store_id'] = sample_submission.id.map(lambda x: '_'.join(x.split('_')[:-1]))
sample_submission['calendar_date'] = sample_submission.id.map(lambda x: x.split('_')[2])
sample_submission.drop('visitors', axis=1, inplace=True)
sample_submission = sample_submission.merge(date_info, on='calendar_date', how='left')
sample_submission = sample_submission.merge(visitors, on=[
    'air_store_id', 'day_of_week', 'holiday_flg'], how='left')

missings = sample_submission.visitors.isnull()
sample_submission.loc[missings, 'visitors'] = sample_submission[missings].merge(
    visitors[visitors.holiday_flg==0], on=('air_store_id', 'day_of_week'), 
    how='left')['visitors_y'].values

missings = sample_submission.visitors.isnull()
sample_submission.loc[missings, 'visitors'] = sample_submission[missings].merge(
    visitors[['air_store_id', 'visitors']].groupby('air_store_id').mean().reset_index(), 
    on='air_store_id', how='left')['visitors_y'].values

sample_submission['visitors'] = sample_submission.visitors.map(pd.np.expm1)
sub2 = sample_submission[['id', 'visitors']].copy()
sub_merge = pd.merge(sub1, sub2, on='id', how='inner')

sub_merge['visitors'] = 0.7*sub_merge['visitors_x'] + 0.3*sub_merge['visitors_y']* 1.1
sub_merge = sub_merge.drop_duplicates(['id'], keep='first')
sub_merge[['id', 'visitors']].to_csv('submission1233.csv', index=False)
# preds1 = knn.predict(test[col])
# preds2 = light.predict(test[col])
# preds3 = xgb.predict(test[col])
# pred = (preds1+preds2+preds3) / 3.0
test['visitors'] = pred
test['visitors'] = np.expm1(test['visitors']).clip(lower=0.)
sub1 = test[['id','visitors']].copy()
test[col].shape
import glob,re
date_info = pd.read_csv('../input/date_info.csv')
air_visit_data = pd.read_csv('../input/air_visit_data.csv')
sample_submission = pd.read_csv('../input/sample_submission.csv')
from __future__ import division
# from hklee
# https://www.kaggle.com/zeemeen/weighted-mean-comparisons-lb-0-497-1st/code
dfs = { re.search('/([^/\.]*)\.csv', fn).group(1):
    pd.read_csv(fn)for fn in glob.glob('../input/*.csv')}

for k, v in dfs.items(): locals()[k] = v

wkend_holidays = date_info.apply(
    (lambda x:(x.day_of_week=='Sunday' or x.day_of_week=='Saturday') and x.holiday_flg==1), axis=1)
date_info.loc[wkend_holidays, 'holiday_flg'] = 0
date_info['weight'] = ((date_info.index + 1) / len(date_info)) ** 5  

visit_data = air_visit_data.merge(date_info, left_on='visit_date', right_on='calendar_date', how='left')
visit_data.drop('calendar_date', axis=1, inplace=True)
visit_data['visitors'] = visit_data.visitors.map(pd.np.log1p)

wmean = lambda x:( (x.weight * x.visitors).sum() / x.weight.sum() )
visitors = visit_data.groupby(['air_store_id', 'day_of_week', 'holiday_flg']).apply(wmean).reset_index()
visitors.rename(columns={0:'visitors'}, inplace=True) # cumbersome, should be better ways.

sample_submission['air_store_id'] = sample_submission.id.map(lambda x: '_'.join(x.split('_')[:-1]))
sample_submission['calendar_date'] = sample_submission.id.map(lambda x: x.split('_')[2])
sample_submission.drop('visitors', axis=1, inplace=True)
sample_submission = sample_submission.merge(date_info, on='calendar_date', how='left')
sample_submission = sample_submission.merge(visitors, on=[
    'air_store_id', 'day_of_week', 'holiday_flg'], how='left')

missings = sample_submission.visitors.isnull()
sample_submission.loc[missings, 'visitors'] = sample_submission[missings].merge(
    visitors[visitors.holiday_flg==0], on=('air_store_id', 'day_of_week'), 
    how='left')['visitors_y'].values

missings = sample_submission.visitors.isnull()
sample_submission.loc[missings, 'visitors'] = sample_submission[missings].merge(
    visitors[['air_store_id', 'visitors']].groupby('air_store_id').mean().reset_index(), 
    on='air_store_id', how='left')['visitors_y'].values

sample_submission['visitors'] = sample_submission.visitors.map(pd.np.expm1)
sub2 = sample_submission[['id', 'visitors']].copy()
sub_merge = pd.merge(sub1, sub2, on='id', how='inner')

sub_merge['visitors'] = (sub_merge['visitors_x'] + sub_merge['visitors_y']* 1.1)/2
sub_merge = sub_merge.drop_duplicates(['id'], keep='first')
sub_merge[['id', 'visitors']].to_csv('Bismillahv12223.csv', index=False)
sub_merge[sub_merge['id'] == "air_0164b9927d20bcc3_2017-04-27"]
# 0.487
# With changed LGB PARAMs 0.484<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.19313034230117776, 'fraction_numerical': 0.014534998227439241, 'mean_word_length': 7.118963863127599, 'pattern_counts': {'":': 0, '<': 22, '<?xml version=': 0, '>': 30, 'https://': 2, 'lorem ipsum': 0, 'www.': 2, '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': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '519830', 'license': '0BSD', 'n_tokens_mistral': 9614, 'n_tokens_neox': 9462, 'n_words': 1243, 'path': 'Recruit Restaurant Visitor Forecasting/Notebooks/DEC3011PM.ipynb', 'repo_name': 'shaz13/KaggleMaster', 'size': '25396'}