{"cells":[{"metadata":{},"cell_type":"markdown","source":"# Introduction"},{"metadata":{},"cell_type":"markdown","source":"### Data Findings\n\n1) The energy consumption level of each building is very different. \n2) The data clearly have some 'errors', like loss of data or unusual spikes. \n3) And from the visuals below, it's obvious that each meter type behave very differently. "},{"metadata":{},"cell_type":"markdown","source":"# Load Data"},{"metadata":{"_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","trusted":true},"cell_type":"code","source":"# import packages\nimport gc\nimport os\nimport subprocess\nimport numpy as np\nimport pandas as pd\nimport matplotlib.pyplot as plt\nimport featuretools as ft\nimport lightgbm as lgb\nfrom lightgbm import plot_tree\nfrom graphviz import Digraph\nimport seaborn as sns\nfrom sklearn.model_selection import GridSearchCV\nfrom sklearn.model_selection import KFold,GroupKFold\nfrom sklearn.metrics import roc_auc_score,mean_squared_error\nimport time\nimport pickle\n\n%matplotlib inline","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"def check_fline(fpath):\n    \"\"\"check total number of lines of file for large files\n    \n    Args:\n    fpath: string. file path\n    \n    Returns:\n    None\n    \n    \"\"\"\n    lines = subprocess.run(['wc', '-l', fpath], stdout=subprocess.PIPE).stdout.decode('utf-8')\n    print(lines, end='', flush=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"fs=['../input/ashrae-energy-prediction/train.csv', \n    '../input/ashrae-energy-prediction/test.csv', \n    '../input/ashrae-energy-prediction/weather_test.csv',\n    '../input/ashrae-energy-prediction/weather_train.csv',\n    '../input/ashrae-energy-prediction/building_metadata.csv']\n[check_fline(s) for s in fs]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Load sample training data\ndf_train = pd.read_csv('../input/ashrae-energy-prediction/train.csv')\ndf_train_weather = pd.read_csv('../input/ashrae-energy-prediction/weather_train.csv')\ndf_test = pd.read_csv('../input/ashrae-energy-prediction/test.csv')\ndf_test_weather = pd.read_csv('../input/ashrae-energy-prediction/weather_test.csv')\ndf_building = pd.read_csv('../input/ashrae-energy-prediction/building_metadata.csv')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Basic Data Structure"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Show data shape\n[print(item.shape) for item in [df_train,df_train_weather,df_test,df_test_weather,df_building]]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We need to predict meter reading given building id, meter, and timestamp.\n\nbuilding_meta.csv --> train.csv by 'building_id'.\n\nweather.csv --> building_meta.csv by 'site_id' --> train.csv by 'timestamp'"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_test.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_train_weather.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_building.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_train_total = pd.merge(df_train,df_building,how='left',on='building_id')\ndf_train_total = pd.merge(df_train_total,df_train_weather,how='left',on=[\"site_id\", \"timestamp\"])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_train_total.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_test_total = pd.merge(df_test,df_building,how='left',on='building_id')\ndf_test_total = pd.merge(df_test_total,df_test_weather,how='left',on=[\"site_id\", \"timestamp\"])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_test_total.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Explore Unique Values"},{"metadata":{"trusted":true},"cell_type":"code","source":"def feat_value_count(df,colname):\n    \"\"\"value count of each feature\n    \n    Args\n    df: data frame.\n    colname: string. Name of to be valued column\n    \n    Returns\n    df_count: data frame.\n    \"\"\"\n    df_count = df[colname].value_counts().to_frame().reset_index()\n    df_count = df_count.rename(columns={'index':colname+'_values',colname:'counts'})\n    return df_count","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"feat_value_count(df_train,'building_id')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"A total of 1449 buildings are in train data. Building 1298 has the most records and building 403 has the least records."},{"metadata":{"trusted":true},"cell_type":"code","source":"feat_value_count(df_test,'building_id')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"len(set(df_train.building_id) & set(df_test.building_id))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"It looks that we need to predict all 1449 building meter readings. All buildings that need to be predicted appear in train data."},{"metadata":{"trusted":true},"cell_type":"code","source":"feat_value_count(df_train,'meter')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Looks like meter 0: electricity has the most record."},{"metadata":{"trusted":true},"cell_type":"code","source":"feat_value_count(df_train_weather,'site_id')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"feat_value_count(df_building,'primary_use')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"feat_value_count(df_building,'site_id')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Check Data Types"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_train_total.dtypes","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_train_total[\"timestamp\"] = pd.to_datetime(df_train_total[\"timestamp\"], format='%Y-%m-%d %H:%M:%S')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_test_total[\"timestamp\"] = pd.to_datetime(df_test_total[\"timestamp\"], format='%Y-%m-%d %H:%M:%S')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Check Missing Values"},{"metadata":{"trusted":true},"cell_type":"code","source":"def check_missing(df,cols=None,axis=0):\n    \"\"\"check data frame column missing situation\n    Args\n    df: data frame.\n    cols: list. List of column names\n    axis: int. 0 means column and 1 means row\n    \n    Returns\n    missing_info: data frame. \n    \"\"\"\n    if cols != None:\n        df = df[cols]\n    missing_num = df.isnull().sum(axis).to_frame().rename(columns={0:'missing_num'})\n    missing_num['missing_percent'] = df.isnull().mean(axis)*100\n    return missing_num.sort_values(by='missing_percent',ascending = False) ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_colmissing = check_missing(df_train_total,cols=None,axis=0)\ndf_colmissing","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del df_colmissing\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Target Explore"},{"metadata":{"trusted":true},"cell_type":"code","source":"print(max(df_train_total.timestamp),min(df_train_total.timestamp))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(max(df_test_total.timestamp),min(df_test_total.timestamp))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"So we have train data span the whole 2016 and we need to predict from day one of 2017 to the end of 2018."},{"metadata":{},"cell_type":"markdown","source":"## single building"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_one_building = df_train_total[df_train_total.building_id == 1258]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_one_building.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# electricity\nsns.lineplot(x='timestamp',y='meter_reading',data=df_one_building[df_train_total.meter == 0])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Looks like there are some outliers."},{"metadata":{"trusted":true},"cell_type":"code","source":"del df_one_building\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# chilledwater\nsns.lineplot(x='timestamp',y='meter_reading',data=df_one_building[df_train_total.meter == 1])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# steam\nsns.lineplot(x='timestamp',y='meter_reading',data=df_one_building[df_train_total.meter == 2])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# hotwater\nsns.lineplot(x='timestamp',y='meter_reading',data=df_one_building[df_train_total.meter == 3])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## multiple building"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_lots_building = df_train_total[df_train_total['building_id'].isin([1258,1298,1249])]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"for i in range(0,4):\n    f, ax = plt.subplots(figsize=(15, 6))\n    sns.lineplot(x='timestamp',y='meter_reading', hue = 'building_id',legend=False,\n             data=df_lots_building[df_lots_building.meter == i])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del df_lots_building\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## feature correlation with target"},{"metadata":{"trusted":true},"cell_type":"code","source":"for i in range(0,4):\n    corr = df_train_total[df_train_total.meter == i][['timestamp','meter_reading','square_feet','year_built','floor_count',\n             'air_temperature','cloud_coverage','dew_temperature','precip_depth_1_hr',\n             'sea_level_pressure','wind_direction','wind_speed']].corr()\n    f, ax = plt.subplots(figsize=(15, 6))\n    sns.heatmap(corr, vmin=-1, vmax=1, annot=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del corr\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Baseline Model"},{"metadata":{"trusted":true},"cell_type":"code","source":"del df_train\ndel df_train_weather\ndel df_test\ndel df_test_weather\ndel df_building\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## helper function"},{"metadata":{"trusted":true},"cell_type":"code","source":"def label_encoder(df, categorical_columns=None):\n    \"\"\"Encode categorical values as integers (0,1,2,3...) with pandas.factorize. \"\"\"\n    # if categorical_colunms are not given than treat object as categorical features\n    if not categorical_columns:\n        categorical_columns = [col for col in df.columns if df[col].dtype == 'object']\n    for col in categorical_columns:\n        df[col], uniques = pd.factorize(df[col])\n    return df, categorical_columns","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_train_total,colname = label_encoder(df_train_total, categorical_columns=['primary_use'])\ndf_test_total,colname = label_encoder(df_test_total, categorical_columns=['primary_use'])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## set parameter"},{"metadata":{"trusted":true},"cell_type":"code","source":"params = {'objective':'regression',\n          'boosting_type':'gbdt',\n          'metric':'rmse',\n          'learning_rate':0.1,\n          'num_leaves': 2**8,\n          'max_depth':-1,\n          'colsample_bytree':0.5,# feature_fraction 0.7\n          'subsample_freq':1,\n          'subsample':0.7,\n          'verbose':-1,\n          #'num_threads':8,\n          'seed': 47,#42\n                } ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"category_cols = ['building_id', 'site_id', 'primary_use']","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Train Model"},{"metadata":{"trusted":true},"cell_type":"code","source":"def fold_train_model(splits_num,features_train,labels_train,features_test,categorical):\n    splits = splits_num\n    folds = KFold(n_splits = splits,random_state=50)\n    predictions = np.zeros(len(features_test))\n    ave_score = 0\n    \n    for fold_num, (trn_idx, val_idx) in enumerate(folds.split(features_train.values, labels_train.values)):\n        print(\"Fold {}\".format(fold_num))\n        train_df, y_train_df = features_train.iloc[trn_idx], labels_train.iloc[trn_idx]\n        valid_df, y_valid_df = features_train.iloc[val_idx], labels_train.iloc[val_idx]\n\n        trn_data = lgb.Dataset(train_df, label=y_train_df,categorical_feature=categorical)\n        val_data = lgb.Dataset(valid_df, label=y_valid_df,categorical_feature=categorical)\n\n        valid_results = {}\n        clf = lgb.train(params,\n                        trn_data,\n                        10000,\n                        valid_sets = [trn_data, val_data],\n                        verbose_eval=500,\n                        early_stopping_rounds=500,\n                        evals_result=valid_results)\n\n        pred = clf.predict(valid_df)\n        score = np.sqrt(mean_squared_error(y_valid_df, pred))\n        ave_score += score / splits\n        predictions += clf.predict(features_test) / splits\n    return ave_score,predictions","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def train_meter_type(meter_type,df_train_total,df_test_total,category_cols):\n    # prepare data\n    df_type_train = df_train_total[df_train_total.meter == meter_type]\n    # transfer label with log\n    df_type_label = np.log1p(df_type_train['meter_reading'])\n    df_type_train.drop(columns = ['meter','meter_reading'],inplace=True)\n    df_type_train['timestamp'] = df_type_train['timestamp'].astype('int64') // 10**9\n\n    df_type_test = df_test_total[df_test_total.meter == meter_type]\n    df_type_row_id = df_type_test['row_id']\n    df_type_test.drop(columns = ['row_id','meter'],inplace=True)\n    df_type_test['timestamp'] = df_type_test['timestamp'].astype('int64') // 10**9\n    \n    # train model\n    print('train model')\n    ave_score,predictions_type = fold_train_model(3,df_type_train,df_type_label,df_type_test,category_cols)\n    print('ave socre is %s'%(ave_score))\n    \n    # get prediction\n    print('get prediction')\n    sub_type = pd.DataFrame({'row_id': df_type_row_id, 'meter_reading': np.expm1(predictions_type)})\n    return sub_type,ave_score","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#sub_ele_f,ave_score = train_meter_type(0,df_train_total,df_test_total,category_cols)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#sub_cw_f,ave_score_cw = train_meter_type(1,df_train_total,df_test_total,category_cols)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#sub_stm_f,ave_score_stm = train_meter_type(2,df_train_total,df_test_total,category_cols)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#sub_hw_f,ave_score_hw = train_meter_type(3,df_train_total,df_test_total,category_cols)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#sub_all = pd.concat([sub_ele_f,sub_cw_f,sub_stm_f,sub_hw_f])\n#sub_all.sort_values(by='row_id')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sub_all.to_csv(['../output/baseline_log.csv', index = False)","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.6.6"}},"nbformat":4,"nbformat_minor":1}