import pandas as pd
import numpy as np
import os
import glob
from sklearn.model_selection import train_test_split, GridSearchCV, KFold, StratifiedKFold
from xgboost import XGBClassifier
import xgboost as xgb
import matplotlib.pyplot as plt
from sklearn.model_selection import cross_val_score
from sklearn.cluster import MiniBatchKMeans
import operator

path = '/Users/charlie/Desktop/TrendMicro/'

tr = pd.read_csv(path+'training-set.csv' , names=['FileID','bug'], header=None)
te = pd.read_csv(path+'testing-set.csv', names=['FileID','bug'],header=None)
exp_tr = pd.read_table(path+'exception/exception_train.txt', names=['FileID'] ,header=None)
exp_te = pd.read_table(path+'exception/exception_testing.txt' , names=['FileID'], header=None)

# delete null rows in training & testing sets
tmp = pd.merge(tr,exp_tr,on=['FileID'],how="outer",indicator=True)
tr = tmp[tmp['_merge']=='left_only']

tmp = pd.merge(te,exp_te,on=['FileID'],how="outer",indicator=True)
te = tmp[tmp['_merge']=='left_only']

tr.to_csv(path+'train.csv',index=False)
te.to_csv(path+'test.csv',index=False)


# import log files
file_list = glob.glob(os.path.join(path+'query_log/','*.csv'))
log_data = pd.concat(pd.read_csv(files, header=None, names=['FileID','CustomerID','QueryTS','ProductID'],
                                 dtype={'FileID':object,'CustomerID':object,'QueryTS':np.int64,'ProductID':object}
                                ) for files in file_list)
del file_list
log_data.to_csv(path+'log_data.csv',index=False)

# Time variables
def unixtime_to_datetime(df):
    df['QueryDT'] = pd.to_datetime(df['QueryTS'], unit='s')
    df = df.drop(['QueryTS'],axis=1)

def time_variable(df):
    df['week'] = df['QueryDT'].dt.week
    df['weekday'] = df['QueryDT'].dt.weekday
    df['hour'] = df['QueryDT'].dt.hour
    df['month'] = df['QueryDT'].dt.month

unixtime_to_datetime(log_data)
time_variable(log_data)

#tr = pd.read_csv(path +'train.csv')
#te = pd.read_csv(path +'test.csv')
#log_data = pd.read_csv(path +'log_data.csv',dtype={'FileID':object,'CustomerID':object,'QueryTS':np.int64,'ProductID':object})

# 檔案的時間分配特徵
log_data = log_data.sort_values(['FileID','QueryTS'],ascending=[1,1])
log_data['diff'] = log_data.groupby(['FileID'])['QueryTS'].diff()

tmp = log_data[log_data['diff'] >= 0].groupby(['FileID']).agg({'diff':['mean','max','min','median']})
tmp.columns = ['mean_File_Customer_Product_diff','max_File_Customer_Product_diff','min_File_Customer_Product_diff','median_File_Customer_Product_diff']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')
tmp = log_data[log_data['diff'] >= 0].groupby('FileID')['diff'].agg(np.std, ddof=0)
tmp = pd.DataFrame(tmp)
tmp.columns = ['std_File_Customer_Product_diff']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

# 檔案、使用者的時間分配特徵
log_data = log_data.sort_values(['FileID','CustomerID','QueryTS'],ascending=[1,1,1])
diff = log_data['QueryTS'].diff()
diff = pd.DataFrame({'Customer_diff':diff})
log_data = pd.concat([log_data,diff],axis=1)

tmp = log_data[log_data['Customer_diff'] >= 0].groupby(['FileID']).agg({'Customer_diff':['mean','max','min','median']})
tmp.columns = ['mean_Customer_diff','max_Customer_diff','min_Customer_diff','median_Customer_diff']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')
tmp = log_data[log_data['Customer_diff'] >= 0].groupby('FileID')['Customer_diff'].agg(np.std, ddof=0)
tmp = pd.DataFrame(tmp)
tmp.columns = ['std_Customer_diff']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')


# 檔案、裝置的時間分配特徵
log_data = log_data.sort_values(['FileID','ProductID','QueryTS'],ascending=[1,1,1])
diff = log_data['QueryTS'].diff()
diff = pd.DataFrame({'Product_diff':diff})
log_data = pd.concat([log_data,diff],axis=1)

tmp = log_data[log_data['Product_diff'] >= 0].groupby(['FileID']).agg({'Product_diff':['mean','max','min','median']})
tmp.columns = ['mean_Product_diff','max_Product_diff','min_Product_diff','median_Product_diff']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')
tmp = log_data[log_data['Product_diff'] >= 0].groupby('FileID')['Product_diff'].agg(np.std, ddof=0)
tmp = pd.DataFrame(tmp)
tmp.columns = ['std_Product_diff']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')


# 檔案、使用者、裝置的時間分配特徵
log_data = log_data.sort_values(['FileID','CustomerID','ProductID','QueryTS'],ascending=[1,1,1,1])
log_data['diff'] = log_data.groupby(['FileID','CustomerID','ProductID'])['QueryTS'].diff()
 
tmp = log_data.groupby(['FileID']).agg({'diff':['mean','max','min','median']})
tmp.columns = ['mean_File_Customer_Product_diff','max_File_Customer_Product_diff','min_File_Customer_Product_diff','median_File_Customer_Product_diff']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')
tmp = log_data.groupby('FileID')['diff'].agg(np.std, ddof=0)
tmp = pd.DataFrame(tmp)
tmp.columns = ['std_File_Customer_Product_diff']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

#補值
tr = tr.fillna(0)
te = te.fillna(0)

# 檔案的標準差小時使用次數
tmp = log_data.groupby(['FileID','hour']).agg({'CustomerID':'count'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','hour','hour_count']
tmp = tmp.groupby('FileID')['hour_count'].agg(np.std, ddof=0)
tmp = pd.DataFrame(tmp)
tmp.columns = ['std_hour_count']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

# 檔案的標準差星期使用次數
tmp = log_data.groupby(['FileID','weekday']).agg({'CustomerID':'count'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','weekday','weekday_count']
tmp = tmp.groupby('FileID')['weekday_count'].agg(np.std, ddof=0)
tmp = pd.DataFrame(tmp)
tmp.columns = ['std_weekday_count']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

# 檔案的標準差週使用次數
tmp = log_data.groupby(['FileID','week']).agg({'CustomerID':'count'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','week','week_count']
tmp = tmp.groupby('FileID')['week_count'].agg(np.std, ddof=0)
tmp = pd.DataFrame(tmp)
tmp.columns = ['std_week_count']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

# 檔案的標準差月使用次數
tmp = log_data.groupby(['FileID','month']).agg({'CustomerID':'count'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','month','month_count']
tmp = tmp.groupby('FileID')['month_count'].agg(np.std, ddof=0)
tmp = pd.DataFrame(tmp)
tmp.columns = ['std_month_count']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

# 檔案的最高、平均、中位數、最低的月使用次數
tmp = log_data.groupby(['FileID','month']).agg({'CustomerID':'count'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','month','month_count']
tmp = tmp.groupby(['FileID']).agg({'month_count':['mean','max','min','median']})
tmp.columns = ['mean_month_count','max_month_count','min_month_count','median_month_count']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

# 檔案的最高、平均、中位數、最低的週使用次數
tmp = log_data.groupby(['FileID','week']).agg({'CustomerID':'count'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','week','week_count']
tmp = tmp.groupby(['FileID']).agg({'week_count':['mean','max','min','median']})
tmp.columns = ['mean_week_count','max_week_count','min_week_count','median_week_count']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')


# 檔案的最高、平均、中位數、最低的星期使用次數
tmp = log_data.groupby(['FileID','weekday']).agg({'CustomerID':'count'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','weekday','weekday_count']
tmp = tmp.groupby(['FileID']).agg({'weekday_count':['mean','max','min','median']})
tmp.columns = ['mean_weekday_count','max_weekday_count','min_weekday_count','median_weekday_count']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

# 檔案的最高、平均、中位數、最低的小時使用次數
tmp = log_data.groupby(['FileID','hour']).agg({'CustomerID':'count'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','hour','hour_count']
tmp = tmp.groupby(['FileID']).agg({'hour_count':['mean','max','min','median']})
tmp.columns = ['mean_hour_count','max_hour_count','min_hour_count','median_hour_count']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

# 檔案出現幾個小時
tmp = log_data.groupby('FileID').agg({'hour':'nunique'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','hour_unique_count']
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

# 檔案在各小時的使用次數 （或是檔案在哪幾個小時出現過）
tmp = log_data.groupby(['FileID','hour']).agg({'CustomerID':'count'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','hour','hour_count']
tmp = tmp.pivot_table(index='FileID',columns='hour',values='hour_count').reset_index()
new_names = ['hour_V'+ str(var_name) for var_name in tmp.columns]
tmp.columns.values[1:] = new_names[1:]
tmp = tmp.fillna(0)
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

# 檔案出現幾個星期
tmp = log_data.groupby('FileID').agg({'weekday':'nunique'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','weekday_unique_count']
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

# 檔案在各星期的使用次數 （或是檔案在哪幾個星期出現過）
tmp = log_data.groupby(['FileID','weekday']).agg({'CustomerID':'count'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','weekday','weekday_count']
tmp = tmp.pivot_table(index='FileID',columns='weekday',values='weekday_count').reset_index()
new_names = ['weekday_V'+ str(var_name) for var_name in tmp.columns]
tmp.columns.values[1:] = new_names[1:]
tmp = tmp.fillna(0)
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

# 檔案出現幾個週
tmp = log_data.groupby('FileID').agg({'week':'nunique'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','week_unique_count']
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

# 檔案出現幾個月
tmp = log_data.groupby('FileID').agg({'month':'nunique'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','month_unique_count']
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')


# 檔案在各週的使用次數 （或是檔案在哪幾週出現過）
tmp = log_data.groupby(['FileID','week']).agg({'CustomerID':'count'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','week','week_count']
tmp = tmp.pivot_table(index='FileID',columns='week',values='week_count').reset_index()
new_names = ['week_V'+ str(var_name) for var_name in tmp.columns]
tmp.columns.values[1:] = new_names[1:]
tmp = tmp.fillna(0)
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

# 檔案在各月份的使用次數 （或是檔案在哪些月份出現過）
tmp = log_data.groupby(['FileID','month']).agg({'CustomerID':'count'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','month','month_count']
tmp = tmp.pivot_table(index='FileID',columns='month',values='month_count').reset_index()
new_names = ['month_V'+ str(var_name) for var_name in tmp.columns]
tmp.columns.values[1:] = new_names[1:]
tmp = tmp.fillna(0)
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

# 開啟該檔案的 使用者裝置ID種數 (多少獨立id的使用者使用)
tmp = log_data.groupby('FileID').agg({'CustomerID':'nunique'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','CustomerID_unique_count']
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

# 檔案的標準差顧客使用次數
tmp = log_data.groupby(['FileID','CustomerID']).agg({'ProductID':'count'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','CustomerID','CustomerID_count']
tmp = tmp.groupby('FileID')['CustomerID_count'].agg(np.std, ddof=0)
tmp = pd.DataFrame(tmp)
tmp.columns = ['std_CustomerID_count']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

# 檔案的最高、平均、中位數、最低的顧客使用次數
tmp = log_data.groupby(['FileID','CustomerID']).agg({'ProductID':'count'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','CustomerID','CustomerID_count']
tmp = tmp.groupby(['FileID']).agg({'CustomerID_count':['mean','max','min','median']})
tmp.columns = ['mean_CustomerID_count','max_CustomerID_count','min_CustomerID_count','median_CustomerID_count']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')


# ProductID_unique_count
# 開啟該檔案的 使用者裝置種數
tmp = log_data.groupby('FileID').agg({'ProductID':'nunique'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','ProductID_unique_count']
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

# 各種使用者裝置的使用次數 (可考慮改成是否使用過該裝置種類 0 or 1)
tmp = log_data.groupby(['FileID','ProductID']).agg({'CustomerID':'count'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','ProductID','ProductID_count']
tmp = tmp.pivot_table(index='FileID',columns='ProductID',values='ProductID_count').reset_index()
new_names = ['ProductID_V'+var_name for var_name in tmp.columns]
tmp.columns.values[1:] = new_names[1:]
tmp = tmp.fillna(0)
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

# 檔案的標準差產品使用次數
tmp = log_data.groupby(['FileID','ProductID']).agg({'CustomerID':'count'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','ProductID','ProductID_count']
tmp = tmp.groupby('FileID')['ProductID_count'].agg(np.std, ddof=0)
tmp = pd.DataFrame(tmp)
tmp.columns = ['std_ProductID_count']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

# 檔案的最高、平均、中位數、最低的產品使用次數
tmp = log_data.groupby(['FileID','ProductID']).agg({'CustomerID':'count'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','ProductID','ProductID_count']
tmp = tmp.groupby(['FileID']).agg({'ProductID_count':['mean','max','min','median']})
tmp.columns = ['mean_ProductID_count','max_ProductID_count','min_ProductID_count','median_ProductID_count']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

# f_count
# 該檔案總共被開啟的次數
tmp = log_data.groupby(['FileID']).count()
tmp = tmp.reset_index()[['FileID','CustomerID']]
tmp.columns = ['FileID','f_count']
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

# 檔案的時間分配特徵
tmp = log_data.groupby(['FileID']).agg({'QueryTS':['mean','max','min','median']})
tmp.columns = ['mean_FileID_QTS','max_FileID_QTS','min_FileID_QTS','median_FileID_QTS']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

# 檔案的時間分配特徵
tmp = log_data.groupby('FileID')['QueryTS'].agg(np.std, ddof=0)
tmp = pd.DataFrame(tmp)
tmp.columns = ['std_QueryTS_count']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

# FileID - CustomerID - ProductID
tmp = log_data.groupby(['FileID','CustomerID','ProductID']).agg({'QueryTS':'count'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','CustomerID','ProductID','FileID_CustomerID_ProductID_count']
tmp = tmp.groupby(['FileID']).agg({'FileID_CustomerID_ProductID_count':['mean','max','min','median']})
tmp.columns = ['mean_FileID_CustomerID_ProductID_count','max_FileID_CustomerID_ProductID_count',
               'min_FileID_CustomerID_ProductID_count','median_FileID_CustomerID_ProductID_count']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

tmp = log_data.groupby(['FileID','CustomerID','ProductID']).agg({'QueryTS':'count'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','CustomerID','ProductID','FileID_CustomerID_ProductID_count']
tmp = tmp.groupby(['FileID'])['FileID_CustomerID_ProductID_count'].agg(np.std, ddof=0)
tmp = pd.DataFrame(tmp)
tmp.columns = ['std_FileID_CustomerID_ProductID_count']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')


# FileID - CustomerID - ProductID - hour
tmp = log_data.groupby(['FileID','CustomerID','ProductID','hour']).agg({'QueryTS':'count'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','CustomerID','ProductID','hour','FileID_CustomerID_ProductID_hour_count']
tmp = tmp.groupby(['FileID'])['FileID_CustomerID_ProductID_hour_count'].agg(np.std, ddof=0)
tmp = pd.DataFrame(tmp)
tmp.columns = ['std_FileID_CustomerID_ProductID_hour_count']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

tmp = log_data.groupby(['FileID','CustomerID','ProductID','hour']).agg({'QueryTS':'count'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','CustomerID','ProductID','hour','FileID_CustomerID_ProductID_hour_count']
tmp = tmp.groupby(['FileID']).agg({'FileID_CustomerID_ProductID_hour_count':['mean','max','min','median']})
tmp.columns = ['mean_FileID_CustomerID_ProductID_hour_count','max_FileID_CustomerID_ProductID_hour_count',
               'min_FileID_CustomerID_ProductID_hour_count','median_FileID_CustomerID_ProductID_hour_count']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

# FileID - CustomerID - ProductID - weekday
tmp = log_data.groupby(['FileID','CustomerID','ProductID','weekday']).agg({'QueryTS':'count'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','CustomerID','ProductID','weekday','FileID_CustomerID_ProductID_weekday_count']
tmp = tmp.groupby(['FileID']).agg({'FileID_CustomerID_ProductID_weekday_count':['mean','max','min','median']})
tmp.columns = ['mean_FileID_CustomerID_ProductID_weekday_count','max_FileID_CustomerID_ProductID_weekday_count',
               'min_FileID_CustomerID_ProductID_weekday_count','median_FileID_CustomerID_ProductID_weekday_count']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

tmp = log_data.groupby(['FileID','CustomerID','ProductID','weekday']).agg({'QueryTS':'count'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','CustomerID','ProductID','weekday','FileID_CustomerID_ProductID_weekday_count']
tmp = tmp.groupby(['FileID'])['FileID_CustomerID_ProductID_weekday_count'].agg(np.std, ddof=0)
tmp = pd.DataFrame(tmp)
tmp.columns = ['std_FileID_CustomerID_ProductID_weekday_count']
tmp = tmp.reset_index()
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

## 使用者裝置ID分群
# 總共5539312個使用者裝置ID
# 使用者裝置ID的總次數（是否為高頻率的使用者
user_count = log_data.groupby('CustomerID').count()
user_count = user_count.reset_index()[['CustomerID','FileID']]
user_count.columns = ['CustomerID','user_count']

# 使用者裝置ID下有幾種裝置 （是否為多裝置使用者）
# 99%的人持有1(90%)～2台裝置
user_product = log_data.groupby('CustomerID').agg({'ProductID':'nunique'}).reset_index()
user_product.columns = ['CustomerID','User_ProductID_unique_count']
#user_product.User_ProductID_unique_count.value_counts()
user_data = pd.merge(user_count, user_product, how='inner', on='CustomerID')

# 使用者於各週的使用次數
tmp = log_data.groupby(['CustomerID','week']).agg({'FileID':'count'})
tmp = tmp.reset_index()
tmp.columns = ['CustomerID','week','week_count']
tmp = tmp.pivot_table(index='CustomerID',columns='week',values='week_count').reset_index()
new_names = ['user_week_V'+ str(var_name) for var_name in tmp.columns]
tmp.columns.values[1:] = new_names[1:]
tmp = tmp.fillna(0)
user_data = pd.merge(user_data, tmp, how='left', on='CustomerID')
#user_data.to_csv(path +'user_data.csv', index=False)

#使用者於各小時的使用次數
tmp = log_data.groupby(['CustomerID','hour']).agg({'FileID':'count'})
tmp = tmp.reset_index()
tmp.columns = ['CustomerID','hour','hour_count']
tmp = tmp.pivot_table(index='CustomerID',columns='hour',values='hour_count').reset_index()
new_names = ['user_hour_V'+ str(var_name) for var_name in tmp.columns]
tmp.columns.values[1:] = new_names[1:]
tmp = tmp.fillna(0)
user_data = pd.merge(user_data, tmp, how='left', on='CustomerID')

#使用者於星期的使用次數
tmp = log_data.groupby(['CustomerID','weekday']).agg({'FileID':'count'})
tmp = tmp.reset_index()
tmp.columns = ['CustomerID','weekday','weekday_count']
tmp = tmp.pivot_table(index='CustomerID',columns='weekday',values='weekday_count').reset_index()
new_names = ['user_weekday_V'+ str(var_name) for var_name in tmp.columns]
tmp.columns.values[1:] = new_names[1:]
tmp = tmp.fillna(0)
user_data = pd.merge(user_data, tmp, how='left', on='CustomerID')

#使用者於月的使用次數
#tmp = log_data.groupby(['CustomerID','month']).agg({'FileID':'count'})
#tmp = tmp.reset_index()
#tmp.columns = ['CustomerID','month','month_count']
#tmp = tmp.pivot_table(index='CustomerID',columns='month',values='month_count').reset_index()
#new_names = ['user_month_V'+ str(var_name) for var_name in tmp.columns]
#tmp.columns.values[1:] = new_names[1:]
#tmp = tmp.fillna(0)
#user_data = pd.merge(user_data, tmp, how='left', on='CustomerID')

# 使用者裝置ID下有幾種檔案
#tmp = log_data.groupby('CustomerID').agg({'FileID':'nunique'}).reset_index()
#tmp.columns = ['CustomerID','User_FileID_unique_count']
#user_product.User_ProductID_unique_count.value_counts()
#user_data = pd.merge(user_data, tmp, how='inner', on='CustomerID')

# 使用者行為分群
n_cluster = 4
kmeans = MiniBatchKMeans(n_clusters=n_cluster, batch_size=500).fit(user_data[['user_count', 'User_ProductID_unique_count',
       'user_week_V9', 'user_week_V10', 'user_week_V11', 'user_week_V12',
       'user_week_V13', 'user_week_V14', 'user_week_V15', 'user_week_V16',
       'user_week_V17', 'user_week_V18', 'user_week_V19', 'user_week_V20',
       'user_week_V21', 'user_week_V22',
       'user_hour_V0', 'user_hour_V1', 'user_hour_V2', 'user_hour_V3',
       'user_hour_V4', 'user_hour_V5', 'user_hour_V6', 'user_hour_V7',
       'user_hour_V8', 'user_hour_V9', 'user_hour_V10', 'user_hour_V11',
       'user_hour_V12', 'user_hour_V13', 'user_hour_V14', 'user_hour_V15',
       'user_hour_V16', 'user_hour_V17', 'user_hour_V18', 'user_hour_V19',
       'user_hour_V20', 'user_hour_V21', 'user_hour_V22', 'user_hour_V23']].values)
user_behavior_group = kmeans.predict(user_data[['user_count', 'User_ProductID_unique_count',
       'user_week_V9', 'user_week_V10', 'user_week_V11', 'user_week_V12',
       'user_week_V13', 'user_week_V14', 'user_week_V15', 'user_week_V16',
       'user_week_V17', 'user_week_V18', 'user_week_V19', 'user_week_V20',
       'user_week_V21', 'user_week_V22',
       'user_hour_V0', 'user_hour_V1', 'user_hour_V2', 'user_hour_V3',
       'user_hour_V4', 'user_hour_V5', 'user_hour_V6', 'user_hour_V7',
       'user_hour_V8', 'user_hour_V9', 'user_hour_V10', 'user_hour_V11',
       'user_hour_V12', 'user_hour_V13', 'user_hour_V14', 'user_hour_V15',
       'user_hour_V16', 'user_hour_V17', 'user_hour_V18', 'user_hour_V19',
       'user_hour_V20', 'user_hour_V21', 'user_hour_V22', 'user_hour_V23']].values)
tmp = pd.DataFrame({'user_behavior_group':user_behavior_group})
user_data = pd.concat([user_data, tmp], axis=1)

log_data = pd.merge(log_data, user_data[['CustomerID','user_behavior_group']], how='left', on='CustomerID')

# 每個檔案各群使用者的數目
tmp = log_data.groupby(['FileID','user_behavior_group']).agg({'CustomerID':'count'})
tmp = tmp.reset_index()
tmp.columns = ['FileID','user_behavior_group','user_behavior_group_count']
tmp = tmp.pivot_table(index='FileID',columns='user_behavior_group',values='user_behavior_group_count').reset_index()
new_names = ['user_behavior_group_count_V'+ str(var_name) for var_name in tmp.columns]
tmp.columns.values[1:] = new_names[1:]
tmp = tmp.fillna(0)
tr = pd.merge(tr, tmp, how='left', on='FileID')
te = pd.merge(te, tmp, how='left', on='FileID')

# 儲存
tr.to_csv(path+'train.csv',index=False)
te.to_csv(path+'test.csv',index=False)

# 平均中毒率
train = pd.read_csv('/Users/charlie/Desktop/TrendMicro/training-set.csv', names=['FileID','res'])
query_log = pd.read_csv('/Users/charlie/Desktop/TrendMicro/log_data.csv')
res_output = pd.DataFrame([])
skf = StratifiedKFold(n_splits=3, random_state=2018)
skf.get_n_splits(train['FileID'].values, train['res'].values)

def hour_res(df_file):
	df_file_hour = df_file[~df_file[['hour']].duplicated()]
	df_file_hour = df_file_hour.dropna(subset=['res'])
	hour_res_mean = df_file_hour.res.mean()
	hour_res_std = df_file_hour.res.std()
	hour_res_0, hour_res_10, hour_res_20, hour_res_30, hour_res_40, hour_res_50, hour_res_60, hour_res_70, hour_res_80, hour_res_90, hour_res_100 =  df_file_hour['res'].quantile(0),\
	    df_file_hour['res'].quantile(0.1),\
	    df_file_hour['res'].quantile(0.2), \
	    df_file_hour['res'].quantile(0.3),\
	    df_file_hour['res'].quantile(0.4), \
	    df_file_hour['res'].quantile(0.5), \
	    df_file_hour['res'].quantile(0.6), \
	    df_file_hour['res'].quantile(0.7),\
	    df_file_hour['res'].quantile(0.8),\
	    df_file_hour['res'].quantile(0.9),\
	    df_file_hour['res'].quantile(1)
	hour_res_range = hour_res_100 - hour_res_0
	return pd.Series((
		hour_res_mean, hour_res_std, hour_res_range, 
		hour_res_0, hour_res_10, hour_res_20, hour_res_30, hour_res_40, hour_res_50, hour_res_60, hour_res_70, hour_res_80, hour_res_90, hour_res_100),
		index=[
		'hour_res_mean', 'hour_res_std', 'hour_res_range',
		'hour_res_0', 'hour_res_10', 'hour_res_20', 'hour_res_30', 'hour_res_40', 'hour_res_50', 'hour_res_60', 'hour_res_70', 'hour_res_80', 'hour_res_90', 'hour_res_100'
		])
def week_res(df_file):
	df_file_week = df_file[~df_file[['week']].duplicated()]
	df_file_week = df_file_week.dropna(subset=['res'])
	week_res_mean = df_file_week.res.mean()
	week_res_std = df_file_week.res.std()
	week_res_0, week_res_10, week_res_20, week_res_30, week_res_40, week_res_50, week_res_60, week_res_70, week_res_80,week_res_90, week_res_100 =  df_file_week['res'].quantile(0),\
	    df_file_week['res'].quantile(0.1),\
	    df_file_week['res'].quantile(0.2), \
	    df_file_week['res'].quantile(0.3),\
	    df_file_week['res'].quantile(0.4), \
	    df_file_week['res'].quantile(0.5), \
	    df_file_week['res'].quantile(0.6), \
	    df_file_week['res'].quantile(0.7),\
	    df_file_week['res'].quantile(0.8),\
	    df_file_week['res'].quantile(0.9),\
	    df_file_week['res'].quantile(1)
	week_res_range = week_res_100 - week_res_0
	return pd.Series((
		week_res_mean, week_res_std, week_res_range, 
		week_res_0, week_res_10, week_res_20, week_res_30, week_res_40, week_res_50, week_res_60, week_res_70, week_res_80, week_res_90, week_res_100),
		index=[
		'week_res_mean', 'week_res_std', 'week_res_range',
		'week_res_0', 'week_res_10', 'week_res_20', 'week_res_30', 'week_res_40', 'week_res_50', 'week_res_60', 'week_res_70', 'week_res_80', 'week_res_90', 'week_res_100'
		])
def weekday_res(df_file):
	df_file_weekday = df_file[~df_file[['weekday']].duplicated()]
	df_file_weekday = df_file_weekday.dropna(subset=['res'])
	weekday_res_mean = df_file_weekday.res.mean()
	weekday_res_std = df_file_weekday.res.std()
	weekday_res_0, weekday_res_10, weekday_res_20, weekday_res_30, weekday_res_40, weekday_res_50, weekday_res_60, weekday_res_70, weekday_res_80, weekday_res_90, weekday_res_100 =  df_file_weekday['res'].quantile(0),\
	    df_file_weekday['res'].quantile(0.1),\
	    df_file_weekday['res'].quantile(0.2), \
	    df_file_weekday['res'].quantile(0.3),\
	    df_file_weekday['res'].quantile(0.4), \
	    df_file_weekday['res'].quantile(0.5), \
	    df_file_weekday['res'].quantile(0.6), \
	    df_file_weekday['res'].quantile(0.7),\
	    df_file_weekday['res'].quantile(0.8),\
	    df_file_weekday['res'].quantile(0.9),\
	    df_file_weekday['res'].quantile(1)
	weekday_res_range = weekday_res_100 - weekday_res_0
	return pd.Series((
		weekday_res_mean, weekday_res_std, weekday_res_range, 
		weekday_res_0, weekday_res_10, weekday_res_20, weekday_res_30, weekday_res_40, weekday_res_50, weekday_res_60, weekday_res_70, weekday_res_80, weekday_res_90, weekday_res_100),
		index=[
		'weekday_res_mean', 'weekday_res_std', 'weekday_res_range',
		'weekday_res_0', 'weekday_res_10', 'weekday_res_20', 'weekday_res_30', 'weekday_res_40', 'weekday_res_50', 'weekday_res_60', 'weekday_res_70', 'weekday_res_80', 'weekday_res_90', 'weekday_res_100'
		])
def customer_prod_res(df_file):
	df_file_cust_prod = df_file[~df_file[['CustomerID','ProductID']].duplicated()]
	df_file_cust_prod = df_file_cust_prod.dropna(subset=['res'])
	cust_prod_res_mean = df_file_cust_prod.res.mean()
	cust_prod_res_std = df_file_cust_prod.res.std()
	cust_prod_res_0, cust_prod_res_10, cust_prod_res_20, cust_prod_res_30, cust_prod_res_40, cust_prod_res_50, cust_prod_res_60, cust_prod_res_70, cust_prod_res_80, cust_prod_res_90, cust_prod_res_100 =  df_file_cust_prod['res'].quantile(0),\
	    df_file_cust_prod['res'].quantile(0.1),\
	    df_file_cust_prod['res'].quantile(0.2), \
	    df_file_cust_prod['res'].quantile(0.3),\
	    df_file_cust_prod['res'].quantile(0.4), \
	    df_file_cust_prod['res'].quantile(0.5), \
	    df_file_cust_prod['res'].quantile(0.6), \
	    df_file_cust_prod['res'].quantile(0.7),\
	    df_file_cust_prod['res'].quantile(0.8),\
	    df_file_cust_prod['res'].quantile(0.9),\
	    df_file_cust_prod['res'].quantile(1)
	cust_prod_res_range = cust_prod_res_100 - cust_prod_res_0
	return pd.Series((
		cust_prod_res_mean, cust_prod_res_std, cust_prod_res_range, 
		cust_prod_res_0, cust_prod_res_10, cust_prod_res_20, cust_prod_res_30, cust_prod_res_40, cust_prod_res_50, cust_prod_res_60, cust_prod_res_70, cust_prod_res_80, cust_prod_res_90, cust_prod_res_100),
		index=[
		'cust_prod_res_mean', 'cust_prod_res_std', 'cust_prod_res_range',
		'cust_prod_res_0', 'cust_prod_res_10', 'cust_prod_res_20', 'cust_prod_res_30', 'cust_prod_res_40', 'cust_prod_res_50', 'cust_prod_res_60', 'cust_prod_res_70', 'cust_prod_res_80', 'cust_prod_res_90', 'cust_prod_res_100'
		])
def customer_res(df_file):
	df_file_customer = df_file[~df_file['CustomerID'].duplicated()]
	df_file_customer = df_file_customer.dropna(subset=['res'])
	customer_res_mean = df_file_customer.res.mean()
	customer_res_std = df_file_customer.res.std()
	customer_res_0, customer_res_10, customer_res_20, customer_res_30, customer_res_40, customer_res_50, customer_res_60, customer_res_70, customer_res_80, customer_res_90, customer_res_100 =  df_file_customer['res'].quantile(0),\
	    df_file_customer['res'].quantile(0.1),\
	    df_file_customer['res'].quantile(0.2), \
	    df_file_customer['res'].quantile(0.3),\
	    df_file_customer['res'].quantile(0.4), \
	    df_file_customer['res'].quantile(0.5), \
	    df_file_customer['res'].quantile(0.6), \
	    df_file_customer['res'].quantile(0.7),\
	    df_file_customer['res'].quantile(0.8),\
	    df_file_customer['res'].quantile(0.9),\
	    df_file_customer['res'].quantile(1)
	customer_res_range = customer_res_100 - customer_res_0
	return pd.Series((
		customer_res_mean, customer_res_std, customer_res_range, 
		customer_res_0, customer_res_10, customer_res_20, customer_res_30, customer_res_40, customer_res_50, customer_res_60, customer_res_70, customer_res_80, customer_res_90, customer_res_100),
		index=[
		'customer_res_mean', 'customer_res_std', 'customer_res_range',
		'customer_res_0', 'customer_res_10', 'customer_res_20', 'customer_res_30', 'customer_res_40', 'customer_res_50', 'customer_res_60', 'customer_res_70', 'customer_res_80', 'customer_res_90', 'customer_res_100'
		])
def prod_res(df_file):
	df_file_prod = df_file[~df_file['ProductID'].duplicated()]
	df_file_prod = df_file_prod.dropna(subset=['res'])
	prod_res_mean = df_file_prod.res.mean()
	prod_res_std = df_file_prod.res.std()
	prod_res_0, prod_res_10, prod_res_20, prod_res_30, prod_res_40, prod_res_50, prod_res_60, prod_res_70, prod_res_80, prod_res_90, prod_res_100 =  df_file_prod['res'].quantile(0),\
	    df_file_prod['res'].quantile(0.1),\
	    df_file_prod['res'].quantile(0.2), \
	    df_file_prod['res'].quantile(0.3),\
	    df_file_prod['res'].quantile(0.4), \
	    df_file_prod['res'].quantile(0.5), \
	    df_file_prod['res'].quantile(0.6), \
	    df_file_prod['res'].quantile(0.7),\
	    df_file_prod['res'].quantile(0.8),\
	    df_file_prod['res'].quantile(0.9),\
	    df_file_prod['res'].quantile(1)
	prod_res_range = prod_res_100 - prod_res_0
	return pd.Series((
		prod_res_mean, prod_res_std, prod_res_range, 
		prod_res_0, prod_res_10, prod_res_20, prod_res_30, prod_res_40, prod_res_50, prod_res_60, prod_res_70, prod_res_80, prod_res_90, prod_res_100),
		index=[
		'prod_res_mean', 'prod_res_std', 'prod_res_range',
		'prod_res_0', 'prod_res_10', 'prod_res_20', 'prod_res_30', 'prod_res_40', 'prod_res_50', 'prod_res_60', 'prod_res_70', 'prod_res_80', 'prod_res_90', 'prod_res_100'
		])

# Train Features
for train_index, test_index in skf.split(train['FileID'].values, train['res'].values):
	print('loop')
	tr_tmp = train.iloc[train_index]
	tmp = pd.merge(tr_tmp, query_log, on='FileID', how='left')
	tmp_mean = tmp.groupby('weekday')['res'].mean()
	tmp_mean = pd.DataFrame(tmp_mean)
	tmp_mean['weekday'] = tmp_mean.index

	te_tmp = train.iloc[test_index]
	te_tmp = te_tmp.rename(columns={'res':'res_old'})
	te_tmp_log = pd.merge(te_tmp, query_log, on='FileID',how='left')
	te_tmp_log = pd.merge(te_tmp_log, tmp_mean, on='weekday',how='left')
	loop_output = te_tmp_log.groupby('FileID').apply(weekday_res)
	res_output = res_output.append(loop_output)

res_output.to_csv('weekday_res.csv')

res_output = pd.DataFrame([])
for train_index, test_index in skf.split(train['FileID'].values, train['res'].values):
	print('loop')
	tr_tmp = train.iloc[train_index]
	tmp = pd.merge(tr_tmp, query_log, on='FileID', how='left')
	tmp_mean = tmp.groupby('week')['res'].mean()
	tmp_mean = pd.DataFrame(tmp_mean)
	tmp_mean['week'] = tmp_mean.index

	te_tmp = train.iloc[test_index]
	te_tmp = te_tmp.rename(columns={'res':'res_old'})
	te_tmp_log = pd.merge(te_tmp, query_log, on='FileID',how='left')
	te_tmp_log = pd.merge(te_tmp_log, tmp_mean, on='week',how='left')
	loop_output = te_tmp_log.groupby('FileID').apply(week_res)
	res_output = res_output.append(loop_output)

res_output.to_csv('week_res.csv')

res_output = pd.DataFrame([])
for train_index, test_index in skf.split(train['FileID'].values, train['res'].values):
	print('loop')
	tr_tmp = train.iloc[train_index]
	tmp = pd.merge(tr_tmp, query_log, on='FileID', how='left')
	tmp_mean = tmp.groupby('ProductID')['res'].mean()
	tmp_mean = pd.DataFrame(tmp_mean)
	tmp_mean['ProductID'] = tmp_mean.index

	te_tmp = train.iloc[test_index]
	te_tmp = te_tmp.rename(columns={'res':'res_old'})
	te_tmp_log = pd.merge(te_tmp, query_log, on='FileID',how='left')
	te_tmp_log = pd.merge(te_tmp_log, tmp_mean, on='ProductID',how='left')
	loop_output = te_tmp_log.groupby('FileID').apply(prod_res)
	res_output = res_output.append(loop_output)

res_output.to_csv('ProductID_ratio.csv')

res_output = pd.DataFrame([])
for train_index, test_index in skf.split(train['FileID'].values, train['res'].values):
	print('loop')
	tr_tmp = train.iloc[train_index]
	tmp = pd.merge(tr_tmp, query_log, on='FileID', how='left')
	tmp_mean = tmp.groupby(['CustomerID','ProductID'],as_index=False)['res'].mean()
	tmp_mean = pd.DataFrame(tmp_mean)

	te_tmp = train.iloc[test_index]
	te_tmp = te_tmp.rename(columns={'res':'res_old'})
	te_tmp_log = pd.merge(te_tmp, query_log, on='FileID',how='left')
	te_tmp_log = pd.merge(te_tmp_log, tmp_mean, on=['CustomerID','ProductID'],how='left')
	loop_output = te_tmp_log.groupby('FileID').apply(customer_prod_res)
	res_output = res_output.append(loop_output)

res_output.to_csv('CustomerID_ProductID_ratio.csv')

res_output = pd.DataFrame([])
for train_index, test_index in skf.split(train['FileID'].values, train['res'].values):
	print('loop')
	tr_tmp = train.iloc[train_index]
	tmp = pd.merge(tr_tmp, query_log, on='FileID', how='left')
	tmp_mean = tmp.groupby('CustomerID')['res'].mean()
	tmp_mean = pd.DataFrame(tmp_mean)
	tmp_mean['CustomerID'] = tmp_mean.index

	te_tmp = train.iloc[test_index]
	te_tmp = te_tmp.rename(columns={'res':'res_old'})
	te_tmp_log = pd.merge(te_tmp, query_log, on='FileID',how='left')
	te_tmp_log = pd.merge(te_tmp_log, tmp_mean, on='CustomerID',how='left')
	loop_output = te_tmp_log.groupby('FileID').apply(customer_res)
	res_output = res_output.append(loop_output)

res_output.to_csv('customer_res.csv')

res_output = pd.DataFrame([])
for train_index, test_index in skf.split(train['FileID'].values, train['res'].values):
	print('loop')
	tr_tmp = train.iloc[train_index]
	tmp = pd.merge(tr_tmp, query_log, on='FileID', how='left')
	tmp_mean = tmp.groupby('hour')['res'].mean()
	tmp_mean = pd.DataFrame(tmp_mean)
	tmp_mean['hour'] = tmp_mean.index

	te_tmp = train.iloc[test_index]
	te_tmp = te_tmp.rename(columns={'res':'res_old'})
	te_tmp_log = pd.merge(te_tmp, query_log, on='FileID',how='left')
	te_tmp_log = pd.merge(te_tmp_log, tmp_mean, on='hour',how='left')
	loop_output = te_tmp_log.groupby('FileID').apply(hour_res)
	res_output = res_output.append(loop_output)

res_output.to_csv('hour_res.csv')

# Test Features
tr_tmp = pd.read_csv('/Users/charlie/Desktop/TrendMicro/training-set.csv', names=['FileID','res'])
te_tmp = pd.read_csv('/Users/charlie/Desktop/TrendMicro/testing-set.csv', names=['FileID','res'])
tmp = pd.merge(tr_tmp, query_log, on='FileID', how='left')
tmp_mean = tmp.groupby('ProductID')['res'].mean()
tmp_mean = pd.DataFrame(tmp_mean)
tmp_mean['ProductID'] = tmp_mean.index

te_tmp = te_tmp.rename(columns={'res':'res_old'})
te_tmp_log = pd.merge(te_tmp, query_log, on='FileID',how='left')
te_tmp_log = pd.merge(te_tmp_log, tmp_mean, on='ProductID',how='left')
loop_output = te_tmp_log.groupby('FileID').apply(prod_res)
loop_output.to_csv('ProductID_ratio_test.csv')

tr_tmp = pd.read_csv('/Users/charlie/Desktop/TrendMicro/training-set.csv', names=['FileID','res'])
te_tmp = pd.read_csv('/Users/charlie/Desktop/TrendMicro/testing-set.csv', names=['FileID','res'])
tmp = pd.merge(tr_tmp, query_log, on='FileID', how='left')
tmp_mean = tmp.groupby('week')['res'].mean()
tmp_mean = pd.DataFrame(tmp_mean)
tmp_mean['week'] = tmp_mean.index

te_tmp = te_tmp.rename(columns={'res':'res_old'})
te_tmp_log = pd.merge(te_tmp, query_log, on='FileID',how='left')
te_tmp_log = pd.merge(te_tmp_log, tmp_mean, on='week',how='left')
loop_output = te_tmp_log.groupby('FileID').apply(week_res)
loop_output.to_csv('week_res_test.csv')

tr_tmp = pd.read_csv('/Users/charlie/Desktop/TrendMicro/training-set.csv', names=['FileID','res'])
te_tmp = pd.read_csv('/Users/charlie/Desktop/TrendMicro/testing-set.csv', names=['FileID','res'])
tmp = pd.merge(tr_tmp, query_log, on='FileID', how='left')
tmp_mean = tmp.groupby('CustomerID')['res'].mean()
tmp_mean = pd.DataFrame(tmp_mean)
tmp_mean['CustomerID'] = tmp_mean.index

te_tmp = te_tmp.rename(columns={'res':'res_old'})
te_tmp_log = pd.merge(te_tmp, query_log, on='FileID',how='left')
te_tmp_log = pd.merge(te_tmp_log, tmp_mean, on='CustomerID',how='left')
loop_output = te_tmp_log.groupby('FileID').apply(customer_res)
loop_output.to_csv('customer_res_test.csv')

tr_tmp = pd.read_csv('/Users/charlie/Desktop/TrendMicro/training-set.csv', names=['FileID','res'])
te_tmp = pd.read_csv('/Users/charlie/Desktop/TrendMicro/testing-set.csv', names=['FileID','res'])
tmp = pd.merge(tr_tmp, query_log, on='FileID', how='left')
tmp_mean = tmp.groupby(['CustomerID','ProductID'],as_index=False)['res'].mean()
tmp_mean = pd.DataFrame(tmp_mean)

te_tmp = te_tmp.rename(columns={'res':'res_old'})
te_tmp_log = pd.merge(te_tmp, query_log, on='FileID',how='left')
te_tmp_log = pd.merge(te_tmp_log, tmp_mean, on=['CustomerID','ProductID'],how='left')
loop_output = te_tmp_log.groupby('FileID').apply(customer_prod_res)
loop_output.to_csv('customer_product_ratio_test.csv')

tr_tmp = pd.read_csv('/Users/charlie/Desktop/TrendMicro/training-set.csv', names=['FileID','res'])
te_tmp = pd.read_csv('/Users/charlie/Desktop/TrendMicro/testing-set.csv', names=['FileID','res'])
tmp = pd.merge(tr_tmp, query_log, on='FileID', how='left')
tmp_mean = tmp.groupby('hour')['res'].mean()
tmp_mean = pd.DataFrame(tmp_mean)
tmp_mean['hour'] = tmp_mean.index

te_tmp = te_tmp.rename(columns={'res':'res_old'})
te_tmp_log = pd.merge(te_tmp, query_log, on='FileID',how='left')
te_tmp_log = pd.merge(te_tmp_log, tmp_mean, on='hour',how='left')
loop_output = te_tmp_log.groupby('FileID').apply(hour_res)
loop_output.to_csv('hour_res_test.csv')

tr_tmp = pd.read_csv('/Users/charlie/Desktop/TrendMicro/training-set.csv', names=['FileID','res'])
te_tmp = pd.read_csv('/Users/charlie/Desktop/TrendMicro/testing-set.csv', names=['FileID','res'])
tmp = pd.merge(tr_tmp, query_log, on='FileID', how='left')
tmp_mean = tmp.groupby('weekday')['res'].mean()
tmp_mean = pd.DataFrame(tmp_mean)
tmp_mean['weekday'] = tmp_mean.index

te_tmp = te_tmp.rename(columns={'res':'res_old'})
te_tmp_log = pd.merge(te_tmp, query_log, on='FileID',how='left')
te_tmp_log = pd.merge(te_tmp_log, tmp_mean, on='weekday',how='left')
loop_output = te_tmp_log.groupby('FileID').apply(weekday_res)
loop_output.to_csv('weekday_res_test.csv')

# 不同平均中毒率的區間內，檔案被使用的次數
def customer_res_group(df_file):
	##
	df_file = df_file.dropna(subset=['res'])
	df_file = df_file.groupby(['FileID','CustomerID','res']).agg({'ProductID':'count'})
	df_file = df_file.reset_index()
	df_file.columns = ['FileID','CustomerID','res','cust_count']
	#df_file = df_file[~df_file['CustomerID'].duplicated()]
	#customer_res_mean = df_file_customer.res.mean()
	#customer_res_std = df_file_customer.res.std()
	customer_res_0, customer_res_10, customer_res_20, customer_res_30, customer_res_40, customer_res_50, customer_res_60, customer_res_70, customer_res_80, customer_res_90, customer_res_100 =  df_file['res'].quantile(0),\
	    df_file['res'].quantile(0.1),\
	    df_file['res'].quantile(0.2), \
	    df_file['res'].quantile(0.3),\
	    df_file['res'].quantile(0.4), \
	    df_file['res'].quantile(0.5), \
	    df_file['res'].quantile(0.6), \
	    df_file['res'].quantile(0.7),\
	    df_file['res'].quantile(0.8),\
	    df_file['res'].quantile(0.9),\
	    df_file['res'].quantile(1)
	#customer_res_range = customer_res_100 - customer_res_0
	##
	cust_res_0_10 = df_file[(df_file.res>=customer_res_0)&(df_file.res<customer_res_10)]['cust_count'].sum(axis=0)
	cust_res_10_20 = df_file[(df_file.res>=customer_res_10)&(df_file.res<customer_res_20)]['cust_count'].sum(axis=0)
	cust_res_20_30 = df_file[(df_file.res>=customer_res_20)&(df_file.res<customer_res_30)]['cust_count'].sum(axis=0)
	cust_res_30_40 = df_file[(df_file.res>=customer_res_30)&(df_file.res<customer_res_40)]['cust_count'].sum(axis=0)
	cust_res_40_50 = df_file[(df_file.res>=customer_res_40)&(df_file.res<customer_res_50)]['cust_count'].sum(axis=0)
	cust_res_50_60 = df_file[(df_file.res>=customer_res_50)&(df_file.res<customer_res_60)]['cust_count'].sum(axis=0)
	cust_res_60_70 = df_file[(df_file.res>=customer_res_60)&(df_file.res<customer_res_70)]['cust_count'].sum(axis=0)
	cust_res_70_80 = df_file[(df_file.res>=customer_res_70)&(df_file.res<customer_res_80)]['cust_count'].sum(axis=0)
	cust_res_80_90 = df_file[(df_file.res>=customer_res_80)&(df_file.res<customer_res_90)]['cust_count'].sum(axis=0)
	cust_res_90_100 = df_file[(df_file.res>=customer_res_90)&(df_file.res<=customer_res_100)]['cust_count'].sum(axis=0)
	return pd.Series((
		cust_res_0_10,cust_res_10_20,cust_res_20_30,cust_res_30_40,cust_res_40_50,cust_res_50_60,
    cust_res_60_70,cust_res_70_80,cust_res_80_90,cust_res_90_100),
		index=[
		'cust_res_0_10', 'cust_res_10_20', 'cust_res_20_30',
		'cust_res_30_40', 'cust_res_40_50', 'cust_res_50_60', 'cust_res_60_70', 'cust_res_70_80', 'cust_res_80_90', 'cust_res_90_100'
		])

def hour_res_group_ratio(df_file):
	##
	df_file = df_file.dropna(subset=['res'])
	df_file = df_file.groupby(['FileID','hour','res']).agg({'ProductID':'count'})
	df_file = df_file.reset_index()
	df_file.columns = ['FileID','hour','res','hour_count']
	#df_file = df_file[~df_file['CustomerID'].duplicated()]
	#customer_res_mean = df_file_customer.res.mean()
	#customer_res_std = df_file_customer.res.std()
	hour_res_0, hour_res_10, hour_res_20, hour_res_30, hour_res_40, hour_res_50, hour_res_60, hour_res_70, hour_res_80, hour_res_90, hour_res_100 =  df_file['res'].quantile(0),\
	    df_file['res'].quantile(0.1),\
	    df_file['res'].quantile(0.2), \
	    df_file['res'].quantile(0.3),\
	    df_file['res'].quantile(0.4), \
	    df_file['res'].quantile(0.5), \
	    df_file['res'].quantile(0.6), \
	    df_file['res'].quantile(0.7),\
	    df_file['res'].quantile(0.8),\
	    df_file['res'].quantile(0.9),\
	    df_file['res'].quantile(1)
	#customer_res_range = customer_res_100 - customer_res_0
	##
	hour_res_0_10 = df_file[(df_file.res>=hour_res_0)&(df_file.res<hour_res_10)]['hour_count'].sum(axis=0)
	hour_res_10_20 = df_file[(df_file.res>=hour_res_10)&(df_file.res<hour_res_20)]['hour_count'].sum(axis=0)
	hour_res_20_30 = df_file[(df_file.res>=hour_res_20)&(df_file.res<hour_res_30)]['hour_count'].sum(axis=0)
	hour_res_30_40 = df_file[(df_file.res>=hour_res_30)&(df_file.res<hour_res_40)]['hour_count'].sum(axis=0)
	hour_res_40_50 = df_file[(df_file.res>=hour_res_40)&(df_file.res<hour_res_50)]['hour_count'].sum(axis=0)
	hour_res_50_60 = df_file[(df_file.res>=hour_res_50)&(df_file.res<hour_res_60)]['hour_count'].sum(axis=0)
	hour_res_60_70 = df_file[(df_file.res>=hour_res_60)&(df_file.res<hour_res_70)]['hour_count'].sum(axis=0)
	hour_res_70_80 = df_file[(df_file.res>=hour_res_70)&(df_file.res<hour_res_80)]['hour_count'].sum(axis=0)
	hour_res_80_90 = df_file[(df_file.res>=hour_res_80)&(df_file.res<hour_res_90)]['hour_count'].sum(axis=0)
	hour_res_90_100 = df_file[(df_file.res>=hour_res_90)&(df_file.res<=hour_res_100)]['hour_count'].sum(axis=0)
	return pd.Series((
		hour_res_0_10,hour_res_10_20,hour_res_20_30,hour_res_30_40,hour_res_40_50,hour_res_50_60,
    hour_res_60_70,hour_res_70_80,hour_res_80_90,hour_res_90_100),
		index=[
		'hour_res_0_10', 'hour_res_10_20', 'hour_res_20_30',
		'hour_res_30_40', 'hour_res_40_50', 'hour_res_50_60', 'hour_res_60_70', 'hour_res_70_80', 'hour_res_80_90', 'hour_res_90_100'
		])

def prod_res_group_ratio(df_file):
	##
	df_file = df_file.dropna(subset=['res'])
	df_file = df_file.groupby(['FileID','ProductID','res']).agg({'CustomerID':'count'})
	df_file = df_file.reset_index()
	df_file.columns = ['FileID','ProductID','res','prod_count']
	#df_file = df_file[~df_file['CustomerID'].duplicated()]
	#customer_res_mean = df_file_customer.res.mean()
	#customer_res_std = df_file_customer.res.std()
	prod_res_0, prod_res_10, prod_res_20, prod_res_30, prod_res_40, prod_res_50, prod_res_60, prod_res_70, prod_res_80, prod_res_90, prod_res_100 =  df_file['res'].quantile(0),\
	    df_file['res'].quantile(0.1),\
	    df_file['res'].quantile(0.2), \
	    df_file['res'].quantile(0.3),\
	    df_file['res'].quantile(0.4), \
	    df_file['res'].quantile(0.5), \
	    df_file['res'].quantile(0.6), \
	    df_file['res'].quantile(0.7),\
	    df_file['res'].quantile(0.8),\
	    df_file['res'].quantile(0.9),\
	    df_file['res'].quantile(1)
	#customer_res_range = customer_res_100 - customer_res_0
	##
	prod_res_0_10 = df_file[(df_file.res>=prod_res_0)&(df_file.res<prod_res_10)]['prod_count'].sum(axis=0)
	prod_res_10_20 = df_file[(df_file.res>=prod_res_10)&(df_file.res<prod_res_20)]['prod_count'].sum(axis=0)
	prod_res_20_30 = df_file[(df_file.res>=prod_res_20)&(df_file.res<prod_res_30)]['prod_count'].sum(axis=0)
	prod_res_30_40 = df_file[(df_file.res>=prod_res_30)&(df_file.res<prod_res_40)]['prod_count'].sum(axis=0)
	prod_res_40_50 = df_file[(df_file.res>=prod_res_40)&(df_file.res<prod_res_50)]['prod_count'].sum(axis=0)
	prod_res_50_60 = df_file[(df_file.res>=prod_res_50)&(df_file.res<prod_res_60)]['prod_count'].sum(axis=0)
	prod_res_60_70 = df_file[(df_file.res>=prod_res_60)&(df_file.res<prod_res_70)]['prod_count'].sum(axis=0)
	prod_res_70_80 = df_file[(df_file.res>=prod_res_70)&(df_file.res<prod_res_80)]['prod_count'].sum(axis=0)
	prod_res_80_90 = df_file[(df_file.res>=prod_res_80)&(df_file.res<prod_res_90)]['prod_count'].sum(axis=0)
	prod_res_90_100 = df_file[(df_file.res>=prod_res_90)&(df_file.res<=prod_res_100)]['prod_count'].sum(axis=0)
	return pd.Series((
		prod_res_0_10,prod_res_10_20,prod_res_20_30,prod_res_30_40,prod_res_40_50,prod_res_50_60,
    prod_res_60_70,prod_res_70_80,prod_res_80_90,prod_res_90_100),
		index=[
		'prod_res_0_10', 'prod_res_10_20', 'prod_res_20_30',
		'prod_res_30_40', 'prod_res_40_50', 'prod_res_50_60', 'prod_res_60_70', 'prod_res_70_80', 'prod_res_80_90', 'prod_res_90_100'
		])

# Train
res_output = pd.DataFrame([])
for train_index, test_index in skf.split(train['FileID'].values, train['res'].values):
	print('loop')
	tr_tmp = train.iloc[train_index]
	tmp = pd.merge(tr_tmp, query_log, on='FileID', how='left')
	tmp_mean = tmp.groupby('CustomerID')['res'].mean()
	tmp_mean = pd.DataFrame(tmp_mean)
	tmp_mean['CustomerID'] = tmp_mean.index

	te_tmp = train.iloc[test_index]
	te_tmp = te_tmp.rename(columns={'res':'res_old'})
	te_tmp_log = pd.merge(te_tmp, query_log, on='FileID',how='left')
	te_tmp_log = pd.merge(te_tmp_log, tmp_mean, on='CustomerID',how='left')
	loop_output = te_tmp_log.groupby('FileID').apply(customer_res_group)
	res_output = res_output.append(loop_output)

res_output.to_csv('res_customer_group_ratio.csv')

res_output = pd.DataFrame([])
for train_index, test_index in skf.split(train['FileID'].values, train['res'].values):
	print('loop')
	tr_tmp = train.iloc[train_index]
	tmp = pd.merge(tr_tmp, query_log, on='FileID', how='left')
	tmp_mean = tmp.groupby('hour')['res'].mean()
	tmp_mean = pd.DataFrame(tmp_mean)
	tmp_mean['hour'] = tmp_mean.index

	te_tmp = train.iloc[test_index]
	te_tmp = te_tmp.rename(columns={'res':'res_old'})
	te_tmp_log = pd.merge(te_tmp, query_log, on='FileID',how='left')
	te_tmp_log = pd.merge(te_tmp_log, tmp_mean, on='hour',how='left')
	loop_output = te_tmp_log.groupby('FileID').apply(hour_res_group_ratio)
	res_output = res_output.append(loop_output)

res_output.to_csv('hour_group_ratio.csv')

res_output = pd.DataFrame([])
for train_index, test_index in skf.split(train['FileID'].values, train['res'].values):
	print('loop')
	tr_tmp = train.iloc[train_index]
	tmp = pd.merge(tr_tmp, query_log, on='FileID', how='left')
	tmp_mean = tmp.groupby('ProductID')['res'].mean()
	tmp_mean = pd.DataFrame(tmp_mean)
	tmp_mean['ProductID'] = tmp_mean.index

	te_tmp = train.iloc[test_index]
	te_tmp = te_tmp.rename(columns={'res':'res_old'})
	te_tmp_log = pd.merge(te_tmp, query_log, on='FileID',how='left')
	te_tmp_log = pd.merge(te_tmp_log, tmp_mean, on='ProductID',how='left')
	loop_output = te_tmp_log.groupby('FileID').apply(prod_res_group_ratio)
	res_output = res_output.append(loop_output)

res_output.to_csv('prod_group_ratio.csv')

# Test
tr_tmp = pd.read_csv('/Users/charlie/Desktop/TrendMicro/training-set.csv', names=['FileID','res'])
te_tmp = pd.read_csv('/Users/charlie/Desktop/TrendMicro/testing-set.csv', names=['FileID','res'])
tmp = pd.merge(tr_tmp, query_log, on='FileID', how='left')
tmp_mean = tmp.groupby('CustomerID')['res'].mean()
tmp_mean = pd.DataFrame(tmp_mean)
tmp_mean['CustomerID'] = tmp_mean.index

te_tmp = te_tmp.rename(columns={'res':'res_old'})
te_tmp_log = pd.merge(te_tmp, query_log, on='FileID',how='left')
te_tmp_log = pd.merge(te_tmp_log, tmp_mean, on='CustomerID',how='left')
loop_output = te_tmp_log.groupby('FileID').apply(customer_res_group)
loop_output.to_csv('customer_group_ratio_test.csv')

tr_tmp = pd.read_csv('/Users/charlie/Desktop/TrendMicro/training-set.csv', names=['FileID','res'])
te_tmp = pd.read_csv('/Users/charlie/Desktop/TrendMicro/testing-set.csv', names=['FileID','res'])
tmp = pd.merge(tr_tmp, query_log, on='FileID', how='left')
tmp_mean = tmp.groupby('hour')['res'].mean()
tmp_mean = pd.DataFrame(tmp_mean)
tmp_mean['hour'] = tmp_mean.index

te_tmp = te_tmp.rename(columns={'res':'res_old'})
te_tmp_log = pd.merge(te_tmp, query_log, on='FileID',how='left')
te_tmp_log = pd.merge(te_tmp_log, tmp_mean, on='hour',how='left')
loop_output = te_tmp_log.groupby('FileID').apply(hour_res_group_ratio)
loop_output.to_csv('hour_group_ratio_test.csv')

tr_tmp = pd.read_csv('/Users/charlie/Desktop/TrendMicro/training-set.csv', names=['FileID','res'])
te_tmp = pd.read_csv('/Users/charlie/Desktop/TrendMicro/testing-set.csv', names=['FileID','res'])
tmp = pd.merge(tr_tmp, query_log, on='FileID', how='left')
tmp_mean = tmp.groupby('ProductID')['res'].mean()
tmp_mean = pd.DataFrame(tmp_mean)
tmp_mean['ProductID'] = tmp_mean.index

te_tmp = te_tmp.rename(columns={'res':'res_old'})
te_tmp_log = pd.merge(te_tmp, query_log, on='FileID',how='left')
te_tmp_log = pd.merge(te_tmp_log, tmp_mean, on='ProductID',how='left')
loop_output = te_tmp_log.groupby('FileID').apply(prod_res_group_ratio)
loop_output.to_csv('prod_group_ratio_test.csv')


# Merge all data
# tr = pd.read_csv(path +'train.csv')
# te = pd.read_csv(path +'test.csv')
customer_res = pd.read_csv(path+'customer_res.csv')
customer_res_test = pd.read_csv('/Users/charlie/Desktop/TrendMicro/customer_res_test.csv')
ProductID_ratio = pd.read_csv(path+'res_ProductID_ratio.csv')
ProductID_ratio_test = pd.read_csv('/Users/charlie/Desktop/TrendMicro/ProductID_ratio_test.csv')
CustomerID_ProductID_ratio = pd.read_csv(path+'CustomerID_ProductID_ratio.csv')
CustomerID_ProductID_ratio_test = pd.read_csv('/Users/charlie/Desktop/TrendMicro/customer_product_ratio_test.csv')
hour_res = pd.read_csv(path+'hour_res.csv')
hour_res_test = pd.read_csv('/Users/charlie/Desktop/TrendMicro/hour_res_test.csv')
weekday_res = pd.read_csv(path+'res_weekday_res.csv')
weekday_res_test = pd.read_csv('/Users/charlie/Desktop/TrendMicro/weekday_res_test.csv')
week_res = pd.read_csv(path+'week_res.csv')
week_res_test = pd.read_csv('/Users/charlie/Desktop/TrendMicro/week_res_test.csv')
customer_group_ratio = pd.read_csv(path+'customer_group_ratio.csv')
customer_group_ratio_test = pd.read_csv('/Users/charlie/Desktop/TrendMicro/customer_group_ratio_test.csv')
prod_group_ratio = pd.read_csv(path+'prod_group_ratio.csv')
prod_group_ratio_test = pd.read_csv('/Users/charlie/Desktop/TrendMicro/prod_group_ratio_test.csv')
hour_group_ratio = pd.read_csv(path+'hour_group_ratio.csv')
hour_group_ratio_test = pd.read_csv('/Users/charlie/Desktop/TrendMicro/hour_group_ratio_test.csv')

tr = pd.merge(tr, customer_res, how='left', on='FileID')
te = pd.merge(te, customer_res_test, how='left', on='FileID')
tr = pd.merge(tr, ProductID_ratio, how='left', on='FileID')
te = pd.merge(te, ProductID_ratio_test, how='left', on='FileID')
tr = pd.merge(tr, CustomerID_ProductID_ratio, how='left', on='FileID')
te = pd.merge(te, CustomerID_ProductID_ratio_test, how='left', on='FileID')
tr = pd.merge(tr, hour_res, how='left', on='FileID')
te = pd.merge(te, hour_res_test, how='left', on='FileID')
tr = pd.merge(tr, weekday_res, how='left', on='FileID')
te = pd.merge(te, weekday_res_test, how='left', on='FileID')
tr = pd.merge(tr, week_res, how='left', on='FileID')
te = pd.merge(te, week_res_test, how='left', on='FileID')
tr = pd.merge(tr, customer_group_ratio, how='left', on='FileID')
te = pd.merge(te, customer_group_ratio_test, how='left', on='FileID')

tr = pd.merge(tr, prod_group_ratio, how='left', on='FileID')
te = pd.merge(te, prod_group_ratio_test, how='left', on='FileID')
tr = pd.merge(tr, hour_group_ratio, how='left', on='FileID')
te = pd.merge(te, hour_group_ratio_test, how='left', on='FileID')

# 中位數補值
imr = Imputer(missing_values='NaN', strategy='median', axis=0)
tr[['customer_res_mean',
 'customer_res_std',
 'customer_res_range',
 'customer_res_0',
 'customer_res_10',
 'customer_res_20',
 'customer_res_30',
 'customer_res_40',
 'customer_res_50',
 'customer_res_60',
 'customer_res_70',
 'customer_res_80',
 'customer_res_90',
 'customer_res_100',
 'prod_res_std',
   'cust_prod_res_mean', 'cust_prod_res_std',
       'cust_prod_res_range', 'cust_prod_res_0', 'cust_prod_res_10',
       'cust_prod_res_20', 'cust_prod_res_30', 'cust_prod_res_40',
       'cust_prod_res_50', 'cust_prod_res_60', 'cust_prod_res_70',
       'cust_prod_res_80', 'cust_prod_res_90', 'cust_prod_res_100',
   'hour_res_std','weekday_ratio_std','week_res_std']]= imr.fit_transform(tr[['customer_res_mean',
 'customer_res_std',
 'customer_res_range',
 'customer_res_0',
 'customer_res_10',
 'customer_res_20',
 'customer_res_30',
 'customer_res_40',
 'customer_res_50',
 'customer_res_60',
 'customer_res_70',
 'customer_res_80',
 'customer_res_90',
 'customer_res_100',
 'prod_res_std',
'cust_prod_res_mean', 'cust_prod_res_std',
       'cust_prod_res_range', 'cust_prod_res_0', 'cust_prod_res_10',
       'cust_prod_res_20', 'cust_prod_res_30', 'cust_prod_res_40',
       'cust_prod_res_50', 'cust_prod_res_60', 'cust_prod_res_70',
       'cust_prod_res_80', 'cust_prod_res_90', 'cust_prod_res_100','hour_res_std','weekday_ratio_std','week_res_std']])

imr = Imputer(missing_values='NaN', strategy='median', axis=0)
te[['customer_res_mean',
 'customer_res_std',
 'customer_res_range',
 'customer_res_0',
 'customer_res_10',
 'customer_res_20',
 'customer_res_30',
 'customer_res_40',
 'customer_res_50',
 'customer_res_60',
 'customer_res_70',
 'customer_res_80',
 'customer_res_90',
 'customer_res_100',
 'prod_res_std',
   'cust_prod_res_mean', 'cust_prod_res_std',
       'cust_prod_res_range', 'cust_prod_res_0', 'cust_prod_res_10',
       'cust_prod_res_20', 'cust_prod_res_30', 'cust_prod_res_40',
       'cust_prod_res_50', 'cust_prod_res_60', 'cust_prod_res_70',
       'cust_prod_res_80', 'cust_prod_res_90', 'cust_prod_res_100','hour_res_std','weekday_ratio_std','week_res_mean',
       'week_res_std', 'week_res_range', 'week_res_0',
       'week_res_10', 'week_res_20', 'week_res_30', 'week_res_40',
       'week_res_50', 'week_res_60', 'week_res_70', 'week_res_80',
       'week_res_90', 'week_res_100']]= imr.fit_transform(te[['customer_res_mean',
 'customer_res_std',
 'customer_res_range',
 'customer_res_0',
 'customer_res_10',
 'customer_res_20',
 'customer_res_30',
 'customer_res_40',
 'customer_res_50',
 'customer_res_60',
 'customer_res_70',
 'customer_res_80',
 'customer_res_90',
 'customer_res_100',
 'prod_res_std',
'cust_prod_res_mean', 'cust_prod_res_std',
       'cust_prod_res_range', 'cust_prod_res_0', 'cust_prod_res_10',
       'cust_prod_res_20', 'cust_prod_res_30', 'cust_prod_res_40',
       'cust_prod_res_50', 'cust_prod_res_60', 'cust_prod_res_70',
       'cust_prod_res_80', 'cust_prod_res_90', 'cust_prod_res_100','hour_res_std','weekday_ratio_std',
                                                                 'week_res_mean',
       'week_res_std', 'week_res_range', 'week_res_0',
       'week_res_10', 'week_res_20', 'week_res_30', 'week_res_40',
       'week_res_50', 'week_res_60', 'week_res_70', 'week_res_80',
       'week_res_90', 'week_res_100']])

# 以0補值
tr[['mean_FileID_hour_diff',
       'max_FileID_hour_diff', 'min_FileID_hour_diff',
       'median_FileID_hour_diff', 'std_FileID_hour_diff']] = tr[['mean_FileID_hour_diff',
       'max_FileID_hour_diff', 'min_FileID_hour_diff',
       'median_FileID_hour_diff', 'std_FileID_hour_diff']].fillna(0)
te[['mean_FileID_hour_diff',
       'max_FileID_hour_diff', 'min_FileID_hour_diff',
       'median_FileID_hour_diff', 'std_FileID_hour_diff']] = te[['mean_FileID_hour_diff',
       'max_FileID_hour_diff', 'min_FileID_hour_diff',
       'median_FileID_hour_diff', 'std_FileID_hour_diff']].fillna(0)

tr.to_csv(path+'tr.csv',index=False)
te.to_csv(path+'te.csv',index=False)

