{"cells":[{"metadata":{},"cell_type":"markdown","source":"credit to:\n\n[nz0722](https://www.kaggle.com/nz0722) for https://www.kaggle.com/nz0722/aligned-timestamp-lgbm-by-meter-type"},{"metadata":{"_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_kg_hide-input":true,"_kg_hide-output":true,"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","trusted":true},"cell_type":"code","source":"import gc\nimport os\nfrom pathlib import Path\nimport random\nimport sys\nimport os\nfrom tqdm import tqdm\nimport numpy as np\nimport pandas as pd\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport feather\n#from IPython.core.display import display, HTML\n\nfrom plotly import tools, subplots\nimport plotly.offline as py\npy.init_notebook_mode(connected=True)\nimport plotly.graph_objs as go\nimport plotly.express as px\nimport plotly.figure_factory as ff\n\nfrom sklearn import preprocessing\nfrom sklearn.model_selection import *\nimport lightgbm as lgb\nimport xgboost as xgb\n\nimport warnings\nwarnings.filterwarnings('ignore')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\ntrain_df = feather.read_dataframe('../input/ashrae-feather/train.ft')\nbuilding_df = feather.read_dataframe('../input/ashrae-feather/building.ft')\nweather_train_df = feather.read_dataframe('../input/ashrae-feather/weather_train.ft')\nweather_test_df = feather.read_dataframe('../input/ashrae-feather/weather_test.ft')\ntest_df = feather.read_dataframe('../input/ashrae-feather/test.ft')","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_kg_hide-input":true,"_kg_hide-output":true,"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"weather = pd.concat([weather_train_df,weather_test_df],ignore_index=True)\n\nweather_key = ['site_id', 'timestamp']\nfull_weather = weather[weather_key + ['air_temperature']].drop_duplicates(subset=weather_key).sort_values(by=weather_key).copy()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"data_to_plot = full_weather.copy()\ndata_to_plot[\"hour\"] = data_to_plot[\"timestamp\"].dt.hour\ncount = 1\nplt.figure(figsize=(25, 15))\nfor site_id, data_by_site in data_to_plot.groupby('site_id'):\n    by_site_by_hour = data_by_site.groupby('hour').mean()\n    ax = plt.subplot(4, 4, count)\n    plt.plot(by_site_by_hour.index,by_site_by_hour['air_temperature'],'xb-')\n    ax.set_title('site: '+str(site_id))\n    count += 1\nplt.tight_layout()\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# calculate ranks of hourly temperatures within date/site_id chunks\nfull_weather['temp_rank'] = full_weather.groupby(['site_id', full_weather.timestamp.dt.date])['air_temperature'].rank('average')\n\n# create a dataframe of site_ids (0-16) x mean hour rank of temperature within day (0-23)\ndf_2d = full_weather.groupby(['site_id', full_weather.timestamp.dt.hour])['temp_rank'].mean().unstack(level=1)\n\n# Subtract the columnID of temperature peak by 14, getting the timestamp alignment gap.\nsite_ids_offsets = pd.Series(df_2d.values.argmax(axis=1) - 14)\nsite_ids_offsets.index.name = 'site_id'\n\ndef timestamp_align(df):\n    df['offset'] = df.site_id.map(site_ids_offsets)\n    df['timestamp_aligned'] = (df.timestamp - pd.to_timedelta(df.offset, unit='H'))\n    df['timestamp'] = df['timestamp_aligned']\n    del df['timestamp_aligned']\n    return df","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"# Original code from https://www.kaggle.com/gemartin/load-data-reduce-memory-usage by @gemartin\n# Modified to support timestamp type, categorical type\n# Modified to add option to use float16 or not. feather format does not support float16.\nfrom pandas.api.types import is_datetime64_any_dtype as is_datetime\nfrom pandas.api.types import is_categorical_dtype\n\ndef reduce_mem_usage(df, use_float16=False):\n    \"\"\" iterate through all the columns of a dataframe and modify the data type\n        to reduce memory usage.        \n    \"\"\"\n    start_mem = df.memory_usage().sum() / 1024**2\n    print('Memory usage of dataframe is {:.2f} MB'.format(start_mem))\n    \n    for col in df.columns:\n        if is_datetime(df[col]) or is_categorical_dtype(df[col]):\n            # skip datetime type or categorical type\n            continue\n        col_type = df[col].dtype\n        \n        if col_type != object:\n            c_min = df[col].min()\n            c_max = df[col].max()\n            if str(col_type)[:3] == 'int':\n                if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max:\n                    df[col] = df[col].astype(np.int8)\n                elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:\n                    df[col] = df[col].astype(np.int16)\n                elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:\n                    df[col] = df[col].astype(np.int32)\n                elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:\n                    df[col] = df[col].astype(np.int64)  \n            else:\n                if use_float16 and c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max:\n                    df[col] = df[col].astype(np.float16)\n                elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:\n                    df[col] = df[col].astype(np.float32)\n                else:\n                    df[col] = df[col].astype(np.float64)\n        else:\n            df[col] = df[col].astype('category')\n\n    end_mem = df.memory_usage().sum() / 1024**2\n    print('Memory usage after optimization is: {:.2f} MB'.format(end_mem))\n    print('Decreased by {:.1f}%'.format(100 * (start_mem - end_mem) / start_mem))\n    \n#     return df","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"building_site_dict = dict(zip(building_df['building_id'], building_df['site_id']))\nsite_meter_raw = train_df[['building_id', 'meter', 'timestamp', 'meter_reading']].copy()\nsite_meter_raw['site_id'] = site_meter_raw.building_id.map(building_site_dict)\ndel site_meter_raw['building_id']\nsite_meter_to_plot = site_meter_raw.copy()\nsite_meter_to_plot[\"hour\"] = site_meter_to_plot[\"timestamp\"].dt.hour\nelec_to_plot = site_meter_to_plot[site_meter_to_plot.meter == 0]","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":false,"trusted":true},"cell_type":"code","source":"count = 1\nplt.figure(figsize=(25, 40))\nfor site_id, data_by_site in elec_to_plot.groupby('site_id'):\n    by_site_by_hour = data_by_site.groupby('hour').mean()\n    ax = plt.subplot(15, 4, count)\n    plt.plot(by_site_by_hour.index,by_site_by_hour['meter_reading'],'xb-')\n    ax.set_title('site: '+str(site_id))\n    count += 1\nplt.tight_layout()\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def preprocess(df):\n    df[\"hour\"] = df[\"timestamp\"].dt.hour\n    df[\"weekday\"] = df[\"timestamp\"].dt.weekday\n    df[\"month\"] = df[\"timestamp\"].dt.month\n    \ndef add_lag_feature(weather_df, window=3):\n    group_df = weather_df.groupby('site_id')\n    cols = ['air_temperature', 'cloud_coverage', 'dew_temperature', 'precip_depth_1_hr', 'sea_level_pressure', 'wind_direction', 'wind_speed']\n    rolled = group_df[cols].rolling(window=window, min_periods=0)\n    lag_mean = rolled.mean().reset_index().astype(np.float16)\n    lag_max = rolled.max().reset_index().astype(np.float16)\n    lag_min = rolled.min().reset_index().astype(np.float16)\n    lag_std = rolled.std().reset_index().astype(np.float16)\n    for col in cols:\n        weather_df[f'{col}_mean_lag{window}'] = lag_mean[col]\n        weather_df[f'{col}_max_lag{window}'] = lag_max[col]\n        weather_df[f'{col}_min_lag{window}'] = lag_min[col]\n        weather_df[f'{col}_std_lag{window}'] = lag_std[col]","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"%%time\ntrain_df['date'] = train_df['timestamp'].dt.date\ntrain_df['meter_reading_log1p'] = np.log1p(train_df['meter_reading'])\n\ntrain_df = train_df.query('not (building_id <= 104 & meter == 0 & timestamp <= \"2016-05-20 18\")')\ntrain_df = train_df.query('not (building_id == 681 & meter == 0 & timestamp <= \"2016-04-27\")')\ntrain_df = train_df.query('not (building_id == 761 & meter == 0 & timestamp <= \"2016-09-02\")')\ntrain_df = train_df.query('not (building_id == 799 & meter == 0 & timestamp <= \"2016-09-02\")')\ntrain_df = train_df.query('not (building_id == 802 & meter == 0 & timestamp <= \"2016-08-24\")')\ntrain_df = train_df.query('not (building_id == 1073 & meter == 0 & timestamp <= \"2016-10-26\")')\ntrain_df = train_df.query('not (building_id == 1094 & meter == 0 & timestamp <= \"2016-09-08\")')\ntrain_df = train_df.query('not (building_id == 29 & meter == 0 & timestamp <= \"2016-08-10\")')\ntrain_df = train_df.query('not (building_id == 40 & meter == 0 & timestamp <= \"2016-06-04\")')\ntrain_df = train_df.query('not (building_id == 45 & meter == 0 & timestamp <= \"2016-07\")')\ntrain_df = train_df.query('not (building_id == 106 & meter == 0 & timestamp <= \"2016-11\")')\ntrain_df = train_df.query('not (building_id == 107 & meter == 0 & timestamp >= \"2016-11-10\")')\ntrain_df = train_df.query('not (building_id == 112 & meter == 0 & timestamp < \"2016-10-31 15\")')\ntrain_df = train_df.query('not (building_id == 144 & meter == 0 & timestamp > \"2016-05-14\" & timestamp < \"2016-10-31\")')\ntrain_df = train_df.query('not (building_id == 147 & meter == 0 & timestamp > \"2016-06-05 19\" & timestamp < \"2016-07-18 15\")')\ntrain_df = train_df.query('not (building_id == 171 & meter == 0 & timestamp <= \"2016-07-05\")')\ntrain_df = train_df.query('not (building_id == 177 & meter == 0 & timestamp > \"2016-06-04\" & timestamp < \"2016-06-25\")')\ntrain_df = train_df.query('not (building_id == 258 & meter == 0 & timestamp > \"2016-09-26\" & timestamp < \"2016-12-12\")')\ntrain_df = train_df.query('not (building_id == 258 & meter == 0 & timestamp > \"2016-08-30\" & timestamp < \"2016-09-08\")')\ntrain_df = train_df.query('not (building_id == 258 & meter == 0 & timestamp > \"2016-09-18\" & timestamp < \"2016-09-25\")')\ntrain_df = train_df.query('not (building_id == 260 & meter == 0 & timestamp <= \"2016-05-11\")')\ntrain_df = train_df.query('not (building_id == 269 & meter == 0 & timestamp > \"2016-06-04\" & timestamp < \"2016-06-25\")')\ntrain_df = train_df.query('not (building_id == 304 & meter == 0 & timestamp >= \"2016-11-20\")')\ntrain_df = train_df.query('not (building_id == 545 & meter == 0 & timestamp > \"2016-01-17\" & timestamp < \"2016-02-10\")')\ntrain_df = train_df.query('not (building_id == 604 & meter == 0 & timestamp < \"2016-11-21\")')\ntrain_df = train_df.query('not (building_id == 693 & meter == 0 & timestamp > \"2016-09-07\" & timestamp < \"2016-11-23\")')\ntrain_df = train_df.query('not (building_id == 693 & meter == 0 & timestamp > \"2016-07-12\" & timestamp < \"2016-05-29\")')\ntrain_df = train_df.query('not (building_id == 723 & meter == 0 & timestamp > \"2016-10-06\" & timestamp < \"2016-11-22\")')\ntrain_df = train_df.query('not (building_id == 733 & meter == 0 & timestamp > \"2016-05-29\" & timestamp < \"2016-06-22\")')\ntrain_df = train_df.query('not (building_id == 733 & meter == 0 & timestamp > \"2016-05-19\" & timestamp < \"2016-05-20\")')\ntrain_df = train_df.query('not (building_id == 803 & meter == 0 & timestamp > \"2016-9-25\")')\ntrain_df = train_df.query('not (building_id == 815 & meter == 0 & timestamp > \"2016-05-17\" & timestamp < \"2016-11-17\")')\ntrain_df = train_df.query('not (building_id == 848 & meter == 0 & timestamp > \"2016-01-15\" & timestamp < \"2016-03-20\")')\ntrain_df = train_df.query('not (building_id == 857 & meter == 0 & timestamp > \"2016-04-13\")')\ntrain_df = train_df.query('not (building_id == 909 & meter == 0 & timestamp < \"2016-02-02\")')\ntrain_df = train_df.query('not (building_id == 909 & meter == 0 & timestamp < \"2016-06-23\")')\ntrain_df = train_df.query('not (building_id == 1008 & meter == 0 & timestamp > \"2016-10-30\" & timestamp < \"2016-11-21\")')\ntrain_df = train_df.query('not (building_id == 1113 & meter == 0 & timestamp < \"2016-07-27\")')\ntrain_df = train_df.query('not (building_id == 1153 & meter == 0 & timestamp < \"2016-01-20\")')\ntrain_df = train_df.query('not (building_id == 1169 & meter == 0 & timestamp < \"2016-08-03\")')\ntrain_df = train_df.query('not (building_id == 1170 & meter == 0 & timestamp > \"2016-06-30\" & timestamp < \"2016-07-05\")')\ntrain_df = train_df.query('not (building_id == 1221 & meter == 0 & timestamp < \"2016-11-04\")')\ntrain_df = train_df.query('not (building_id == 1225 & meter == 0 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id == 1234 & meter == 0 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id >= 1233 & building_id <= 1234 & meter == 0 & timestamp > \"2016-01-13 22\" & timestamp < \"2016-03-08 12\")')\ntrain_df = train_df.query('not (building_id == 1241 & meter == 0 & timestamp > \"2016-07-14\" & timestamp < \"2016-11-19\")')\ntrain_df = train_df.query('not (building_id == 1250 & meter == 0 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id == 1255 & meter == 0 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id == 1264 & meter == 0 & timestamp > \"2016-08-23\")')\ntrain_df = train_df.query('not (building_id == 1265 & meter == 0 & timestamp > \"2016-05-06\" & timestamp < \"2016-05-26\")')\ntrain_df = train_df.query('not (building_id == 1272 & meter == 0 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id >= 1275 & building_id <= 1280 & meter == 0 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id == 1283 & meter == 0 & timestamp > \"2016-07-08\" & timestamp < \"2016-08-03\")')\ntrain_df = train_df.query('not (building_id >= 1291 & building_id <= 1302 & meter == 0 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id == 1303 & meter == 0 & timestamp > \"2016-07-25 22\" & timestamp < \"2016-07-27 16\")')\ntrain_df = train_df.query('not (building_id == 1303 & meter == 0 & timestamp > \"2016-01-26\" & timestamp < \"2016-06-02 12\")')\ntrain_df = train_df.query('not (building_id == 1319 & meter == 0 & timestamp > \"2016-05-17 16\" & timestamp < \"2016-06-07 12\")')\ntrain_df = train_df.query('not (building_id == 1319 & meter == 0 & timestamp > \"2016-08-18 14\" & timestamp < \"2016-09-02 14\")')\ntrain_df = train_df.query('not (building_id == 1322 & meter == 0 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\n\n# 2nd cleaning\ntrain_df = train_df.query('not (building_id >= 874 & building_id <= 997 & meter == 0 & timestamp > \"2016-10-14 22\" & timestamp < \"2016-10-17 08\")')\ntrain_df = train_df.query('not (building_id >= 874 & building_id <= 997 & meter == 0 & timestamp > \"2016-07-01 14\" & timestamp < \"2016-07-05 06\")')\ntrain_df = train_df.query('not (building_id >= 874 & building_id <= 997 & meter == 1 & timestamp > \"2016-10-14 22\" & timestamp < \"2016-10-17 08\")')\ntrain_df = train_df.query('not (building_id >= 874 & building_id <= 997 & meter == 1 & timestamp > \"2016-07-01 14\" & timestamp < \"2016-07-05 06\")')\ntrain_df = train_df.query('not (building_id >= 874 & building_id <= 997 & meter == 2 & timestamp > \"2016-10-14 22\" & timestamp < \"2016-10-17 08\")')\ntrain_df = train_df.query('not (building_id >= 874 & building_id <= 997 & meter == 2 & timestamp > \"2016-07-01 14\" & timestamp < \"2016-07-05 06\")')\ntrain_df = train_df.query('not (building_id == 1272 & meter == 1 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id >= 1291 & building_id <= 1297 & meter == 1 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id == 1300 & meter == 1 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id == 1302 & meter == 1 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id >= 1291 & building_id <= 1299 & meter == 2 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id == 1221 & meter == 0 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id >= 1225 & building_id <= 1226 & meter == 0 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id >= 1233 & building_id <= 1234 & meter == 0 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id == 1241 & meter == 0 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id == 1223 & meter == 1 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id == 1226 & meter == 1 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id >= 1233 & building_id <= 1234 & meter == 1 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id >= 1225 & building_id <= 1226 & meter == 2 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id == 1305 & meter == 2 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id == 1307 & meter == 2 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id == 1223 & meter == 3 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id == 1231 & meter == 3 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id >= 1233 & building_id <= 1234 & meter == 3 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id == 1272 & meter == 3 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id >= 1275 & building_id <= 1297 & meter == 3 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id == 1300 & meter == 3 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id == 1302 & meter == 3 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id == 1293 & meter == 3 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-25 12\")')\ntrain_df = train_df.query('not (building_id == 1302 & meter == 3 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-25 12\")')\ntrain_df = train_df.query('not (building_id == 1223 & meter == 0 & timestamp > \"2016-9-28 07\" & timestamp < \"2016-10-11 18\")')\ntrain_df = train_df.query('not (building_id == 1225 & meter == 1 & timestamp > \"2016-8-22 23\" & timestamp < \"2016-10-11 14\")')\ntrain_df = train_df.query('not (building_id == 1230 & meter == 1 & timestamp > \"2016-8-22 08\" & timestamp < \"2016-10-05 18\")')\ntrain_df = train_df.query('not (building_id == 904 & meter == 0 & timestamp < \"2016-02-17 08\")')\ntrain_df = train_df.query('not (building_id == 986 & meter == 0 & timestamp < \"2016-02-17 08\")')\ntrain_df = train_df.query('not (building_id == 954 & meter == 0 & timestamp < \"2016-08-08 11\")')\ntrain_df = train_df.query('not (building_id == 954 & meter == 0 & timestamp < \"2016-06-23 08\")')\ntrain_df = train_df.query('not (building_id >= 745 & building_id <= 770 & meter == 1 & timestamp > \"2016-10-05 01\" & timestamp < \"2016-10-10 09\")')\ntrain_df = train_df.query('not (building_id >= 774 & building_id <= 787 & meter == 1 & timestamp > \"2016-10-05 01\" & timestamp < \"2016-10-10 09\")')\n\n# 3rd cleaning hourly spikes\ntrain_df = train_df.query('not (building_id >= 874 & building_id <= 997 & meter == 0 & timestamp > \"2016-05-11 09\" & timestamp < \"2016-05-12 01\")')\ntrain_df = train_df.query('not (building_id >= 874 & building_id <= 997 & meter == 1 & timestamp > \"2016-05-11 09\" & timestamp < \"2016-05-12 01\")')\ntrain_df = train_df.query('not (building_id >= 874 & building_id <= 997 & meter == 2 & timestamp > \"2016-05-11 09\" & timestamp < \"2016-05-12 01\")')\n\ntrain_df = train_df.query('not (building_id >= 874 & building_id <= 997 & meter == 0 & timestamp == \"2016-02-26 01\")')\ntrain_df = train_df.query('not (building_id >= 874 & building_id <= 997 & meter == 1 & timestamp == \"2016-02-26 01\")')\ntrain_df = train_df.query('not (building_id >= 874 & building_id <= 997 & meter == 2 & timestamp == \"2016-02-26 01\")')\n\ntrain_df = train_df.query('not (building_id >= 874 & building_id <= 997 & meter == 0 & timestamp > \"2016-03-29 10\" & timestamp < \"2016-03-30 12\")')\ntrain_df = train_df.query('not (building_id >= 874 & building_id <= 997 & meter == 1 & timestamp > \"2016-03-29 10\" & timestamp < \"2016-03-30 12\")')\ntrain_df = train_df.query('not (building_id >= 874 & building_id <= 997 & meter == 2 & timestamp > \"2016-03-29 10\" & timestamp < \"2016-03-30 12\")')\n\ntrain_df = train_df.query('not (building_id >= 874 & building_id <= 997 & meter == 0 & timestamp > \"2016-01-19 23\" & timestamp < \"2016-01-28 15\")')\ntrain_df = train_df.query('not (building_id >= 874 & building_id <= 997 & meter == 1 & timestamp > \"2016-01-19 23\" & timestamp < \"2016-01-28 15\")')\ntrain_df = train_df.query('not (building_id >= 874 & building_id <= 997 & meter == 2 & timestamp > \"2016-01-19 23\" & timestamp < \"2016-01-28 15\")')\n\ntrain_df = train_df.query('not (building_id != 1227 & building_id != 1281 & building_id != 1314 & building_id >=1223 & building_id < 1335 & meter==0 & meter_reading==0)')\n\n# 4th cleaning (some using hindsight from leaks)\ntrain_df = train_df.query('not (building_id >= 1223 & building_id <= 1324 & meter==1 & timestamp > \"2016-07-16 04\" & timestamp < \"2016-07-19 11\")')\ntrain_df = train_df.query('not (building_id == 107 & meter == 0 & timestamp <= \"2016-07-06\")')\ntrain_df = train_df.query('not (building_id == 180 & timestamp >= \"2016-02-17 12\")')\ntrain_df = train_df.query('not (building_id == 182 & meter == 0)')\ntrain_df = train_df.query('not (building_id == 191 & meter == 0 & timestamp >= \"2016-12-22 09\")')\ntrain_df = train_df.query('not (building_id == 192 & meter == 1 & timestamp >= \"2016-05-09 18\")')\ntrain_df = train_df.query('not (building_id == 192 & meter == 3 & timestamp >= \"2016-03-29 05\" & timestamp <= \"2016-04-04 08\")')\ntrain_df = train_df.query('not (building_id == 207 & meter == 1 & timestamp > \"2016-07-02 20\" & timestamp < \"2016-08-25 12\")')\ntrain_df = train_df.query('not (building_id == 258 & timestamp > \"2016-09-18\" & timestamp < \"2016-12-12 13\")')\ntrain_df = train_df.query('not (building_id == 258 & timestamp > \"2016-08-29 08\" & timestamp < \"2016-09-08 14\")')\ntrain_df = train_df.query('not (building_id == 257 & meter == 1 & timestamp < \"2016-03-25 16\")')\ntrain_df = train_df.query('not (building_id == 260 & meter == 1 & timestamp > \"2016-05-10 17\" & timestamp < \"2016-08-17 11\")')\ntrain_df = train_df.query('not (building_id == 260 & meter == 1 & timestamp > \"2016-08-28 01\" & timestamp < \"2016-10-31 13\")')\ntrain_df = train_df.query('not (building_id == 220 & meter == 1 & timestamp > \"2016-09-23 01\" & timestamp < \"2016-09-23 12\")')\ntrain_df = train_df.query('not (building_id == 281 & meter == 1 & timestamp > \"2016-10-25 08\" & timestamp < \"2016-11-04 15\")')\ntrain_df = train_df.query('not (building_id == 273 & meter == 1 & timestamp > \"2016-04-03 04\" & timestamp < \"2016-04-29 15\")')\ntrain_df = train_df.query('not (building_id == 28 & meter == 0 & timestamp < \"2016-10-14 20\")')\ntrain_df = train_df.query('not (building_id == 71 & meter == 0 & timestamp < \"2016-08-18 20\")')\ntrain_df = train_df.query('not (building_id == 76 & meter == 0 & timestamp > \"2016-06-04 09\" & timestamp < \"2016-06-04 14\")')\ntrain_df = train_df.query('not (building_id == 101 & meter == 0 & timestamp > \"2016-10-12 13\" & timestamp < \"2016-10-12 18\")')\ntrain_df = train_df.query('not (building_id == 7 & meter == 1 & timestamp > \"2016-11-03 09\" & timestamp < \"2016-11-28 14\")')\ntrain_df = train_df.query('not (building_id == 9 & meter == 1 & timestamp > \"2016-12-06 08\")')\ntrain_df = train_df.query('not (building_id == 43 & meter == 1 & timestamp > \"2016-04-03 08\" & timestamp < \"2016-06-06 13\")')\ntrain_df = train_df.query('not (building_id == 60 & meter == 1 & timestamp > \"2016-05-01 17\" & timestamp < \"2016-05-01 21\")')\ntrain_df = train_df.query('not (building_id == 75 & meter == 1 & timestamp > \"2016-08-05 13\" & timestamp < \"2016-08-26 12\")')\ntrain_df = train_df.query('not (building_id == 95 & meter == 1 & timestamp > \"2016-08-08 10\" & timestamp < \"2016-08-26 13\")')\ntrain_df = train_df.query('not (building_id == 97 & meter == 1 & timestamp > \"2016-08-08 14\" & timestamp < \"2016-08-25 14\")')\ntrain_df = train_df.query('not (building_id == 1232 & meter == 1 & timestamp > \"2016-06-23 16\" & timestamp < \"2016-08-31 20\")')\ntrain_df = train_df.query('not (building_id == 1236 & meter == 1 & meter_reading >= 3000)')\ntrain_df = train_df.query('not (building_id == 1239 & meter == 1 & timestamp > \"2016-03-11 16\" & timestamp < \"2016-03-27 17\")')\ntrain_df = train_df.query('not (building_id == 1264 & meter == 1 & timestamp > \"2016-08-22 17\" & timestamp < \"2016-09-22 20\")')\ntrain_df = train_df.query('not (building_id == 1264 & meter == 1 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ntrain_df = train_df.query('not (building_id == 1269 & meter == 1 & meter_reading >= 2000)')\ntrain_df = train_df.query('not (building_id == 1272 & meter == 1 & timestamp > \"2016-08-11 12\" & timestamp < \"2016-08-30 19\")')\ntrain_df = train_df.query('not (building_id == 1273 & meter == 1 & timestamp > \"2016-05-31 14\" & timestamp < \"2016-06-17\")')\ntrain_df = train_df.query('not (building_id == 1276 & meter == 1 & timestamp < \"2016-02-03 23\")')\ntrain_df = train_df.query('not (building_id == 1280 & meter == 1 & timestamp > \"2016-05-18\" & timestamp < \"2016-05-26 09\")')\ntrain_df = train_df.query('not (building_id == 1280 & meter == 1 & timestamp > \"2016-02-28 23\" & timestamp < \"2016-05-02 05\")')\ntrain_df = train_df.query('not (building_id == 1280 & meter == 1 & timestamp > \"2016-06-12 01\" & timestamp < \"2016-7-07 06\")')\ntrain_df = train_df.query('not (building_id == 1288 & meter == 1 & timestamp > \"2016-07-07 15\" & timestamp < \"2016-08-12 17\")')\ntrain_df = train_df.query('not (building_id == 1311 & meter == 1 & timestamp > \"2016-04-25 18\" & timestamp < \"2016-05-13 14\")')\ntrain_df = train_df.query('not (building_id == 1099 & meter == 2)')\n\ntrain_df = train_df.query('not (building_id == 1329 & meter == 0 & timestamp > \"2016-04-28 00\" & timestamp < \"2016-04-28 07\")')\ntrain_df = train_df.query('not (building_id == 1331 & meter == 0 & timestamp > \"2016-04-28 00\" & timestamp < \"2016-04-28 07\")')\ntrain_df = train_df.query('not (building_id == 1427 & meter == 0 & timestamp > \"2016-04-11 10\" & timestamp < \"2016-04-11 14\")')\ntrain_df = train_df.query('not (building_id == 1426 & meter == 2 & timestamp > \"2016-05-03 09\" & timestamp < \"2016-05-03 14\")')\ntrain_df = train_df.query('not (building_id == 1345 & meter == 0 & timestamp < \"2016-03-01\")')\ntrain_df = train_df.query('not (building_id == 1346 & timestamp < \"2016-03-01\")')\ntrain_df = train_df.query('not (building_id == 1359 & meter == 0 & timestamp > \"2016-04-25 17\" & timestamp < \"2016-07-22 14\")')\ntrain_df = train_df.query('not (building_id == 1365 & meter == 0 & timestamp > \"2016-08-19 00\" & timestamp < \"2016-08-19 07\")')\ntrain_df = train_df.query('not (building_id == 1365 & meter == 0 & timestamp > \"2016-06-18 22\" & timestamp < \"2016-06-19 06\")')\n\ntrain_df = train_df.query('not (building_id == 18 & meter == 0 & timestamp > \"2016-06-04 09\" & timestamp < \"2016-06-04 16\")')\ntrain_df = train_df.query('not (building_id == 18 & meter == 0 & timestamp > \"2016-11-05 05\" & timestamp < \"2016-11-05 15\")')\ntrain_df = train_df.query('not (building_id == 101 & meter == 0 & meter_reading > 800)')\n\ntrain_df = train_df.query('not (building_id == 1384 & meter == 0 & meter_reading == 0 )')\ntrain_df = train_df.query('not (building_id >= 1289 & building_id <= 1301 & meter == 2 & meter_reading == 0)')\ntrain_df = train_df.query('not (building_id == 1243 & meter == 2 & meter_reading == 0)')\ntrain_df = train_df.query('not (building_id == 1263 & meter == 2 & meter_reading == 0)')\ntrain_df = train_df.query('not (building_id == 1284 & meter == 2 & meter_reading == 0)')\ntrain_df = train_df.query('not (building_id == 1286 & meter == 2 & meter_reading == 0)')\ntrain_df = train_df.query('not (building_id == 1263 & meter == 0 & timestamp > \"2016-11-10 11\" & timestamp < \"2016-11-10 15\")')\n\ntrain_df = train_df.query('not (building_id == 1238 & meter == 2 & meter_reading == 0)')\ntrain_df = train_df.query('not (building_id == 1329 & meter == 2 & timestamp > \"2016-11-21 12\" & timestamp < \"2016-11-29 12\")')\ntrain_df = train_df.query('not (building_id == 1249 & meter == 2 & meter_reading == 0)')\n\ntrain_df = train_df.query('not (building_id == 1250 & meter == 2 & meter_reading == 0)')\ntrain_df = train_df.query('not (building_id == 1256 & meter == 2 & timestamp > \"2016-03-05 18\" & timestamp < \"2016-03-05 22\")')\ntrain_df = train_df.query('not (building_id == 1256 & meter == 2 & timestamp > \"2016-03-27 00\" & timestamp < \"2016-03-27 23\")')\ntrain_df = train_df.query('not (building_id == 1256 & meter == 2 & timestamp > \"2016-04-11 09\" & timestamp < \"2016-04-13 03\")')\ntrain_df = train_df.query('not (building_id == 1256 & meter == 2 & timestamp > \"2016-04-29 00\" & timestamp < \"2016-04-30 15\")')\ntrain_df = train_df.query('not (building_id == 1303 & meter == 2 & timestamp < \"2016-06-06 19\")')\ntrain_df = train_df.query('not (building_id >= 1223 & building_id <= 1324 & meter == 1 & timestamp > \"2016-08-11 17\" & timestamp < \"2016-08-12 17\")')\ntrain_df = train_df.query('not (building_id >= 1223 & building_id <= 1324 & building_id != 1296 & building_id != 129 & building_id != 1298 & building_id != 1299 & meter == 2 & timestamp > \"2016-08-11 17\" & timestamp < \"2016-08-12 17\")')\ntrain_df = train_df.query('not (building_id >= 1223 & building_id <= 1324 & meter == 3 & timestamp > \"2016-08-11 17\" & timestamp < \"2016-08-12 17\")')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"debug = False    \npreprocess(train_df)\n\nbm_cols = ['building_id', 'weekday', 'hour',]\nbm = train_df.groupby(bm_cols)['meter_reading'].mean().rename('b_week_hour').to_frame()\ntrain_df = train_df.merge(bm, right_index=True, left_on=bm_cols, how='left')\n\n# https://www.kaggle.com/ryches/simple-lgbm-solution\ndf_group = train_df.groupby('building_id')['meter_reading_log1p']\nbuilding_mean = df_group.mean().astype(np.float16)\nbuilding_median = df_group.median().astype(np.float16)\nbuilding_min = df_group.min().astype(np.float16)\nbuilding_max = df_group.max().astype(np.float16)\nbuilding_std = df_group.std().astype(np.float16)\n\ntrain_df['building_mean'] = train_df['building_id'].map(building_mean)\ntrain_df['building_median'] = train_df['building_id'].map(building_median)\ntrain_df['building_min'] = train_df['building_id'].map(building_min)\ntrain_df['building_max'] = train_df['building_id'].map(building_max)\ntrain_df['building_std'] = train_df['building_id'].map(building_std)\n\nweather_train_df = timestamp_align(weather_train_df)\nweather_train_df = weather_train_df.groupby('site_id').apply(lambda group: group.interpolate(limit_direction='both'))\n\n# add_lag_feature(weather_train_df, window=3)\nadd_lag_feature(weather_train_df, window=72)\n\nprimary_use_list = building_df['primary_use'].unique()\nprimary_use_dict = {key: value for value, key in enumerate(primary_use_list)} \nbuilding_df['primary_use'] = building_df['primary_use'].map(primary_use_dict)\n\ngc.collect()\n\nreduce_mem_usage(train_df, use_float16=True)\nreduce_mem_usage(building_df, use_float16=True)\nreduce_mem_usage(weather_train_df, use_float16=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"category_cols = ['building_id', 'primary_use', ]  # , 'meter'\nfeature_cols = ['square_feet', 'year_built'] + [#'sin_hour', 'cos_hour', \n    'hour','weekday', # 'month'\n    'building_median'] + ['b_week_hour',\n    'air_temperature', 'cloud_coverage',\n    'dew_temperature', 'precip_depth_1_hr', 'sea_level_pressure',\n    'wind_direction', 'wind_speed', 'air_temperature_mean_lag72',\n    'air_temperature_max_lag72', 'air_temperature_min_lag72',\n    'air_temperature_std_lag72', 'cloud_coverage_mean_lag72',\n    'dew_temperature_mean_lag72', 'precip_depth_1_hr_mean_lag72',\n    'sea_level_pressure_mean_lag72', 'wind_direction_mean_lag72',\n    'wind_speed_mean_lag72',]# 'air_temperature_mean_lag3',\n#     'air_temperature_max_lag3',\n#     'air_temperature_min_lag3', 'cloud_coverage_mean_lag3',\n#     'dew_temperature_mean_lag3',\n#     'precip_depth_1_hr_mean_lag3', 'sea_level_pressure_mean_lag3',\n#     'wind_direction_mean_lag3', 'wind_speed_mean_lag3']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def create_X_y(train_df, target_meter, use_month=False):\n    target_train_df = train_df[train_df['meter'] == target_meter]\n    target_train_df = target_train_df.merge(building_df, on='building_id', how='left')\n    target_train_df = target_train_df.merge(weather_train_df, on=['site_id', 'timestamp'], how='left')\n    if not use_month:\n        X_train = target_train_df[feature_cols + category_cols]\n    else:\n        X_train = target_train_df[feature_cols + category_cols + ['month']]\n    y_train = target_train_df['meter_reading_log1p'].values\n\n    del target_train_df\n    return X_train, y_train\n\ndef fit_lgbm(train, val, devices=(-1,), seed=None, cat_features=None, num_rounds=1500, lr=0.1, bf=0.1):\n    \"\"\"Train Light GBM model\"\"\"\n    X_train, y_train = train\n    X_valid, y_valid = val\n    metric = 'l2'\n    params = {'num_leaves': 100,\n              'objective': 'regression',\n#               'max_depth': -1,\n              'learning_rate': lr,\n              \"boosting\": \"gbdt\",\n              \"bagging_freq\": 6,\n              \"bagging_fraction\": bf,\n              \"feature_fraction\": 0.9,\n              \"metric\": metric,\n              \"num_threads\": 5,\n#               \"verbosity\": -1,\n#               'reg_alpha': 0.1,\n#               'reg_lambda': 0.3\n              }\n    device = devices[0]\n    if device == -1:\n        # use cpu\n        pass\n    else:\n        # use gpu\n        print(f'using gpu device_id {device}...')\n        params.update({'device': 'gpu', 'gpu_device_id': device})\n\n    params['seed'] = seed\n\n    early_stop = 20\n    verbose_eval = 100\n\n    d_train = lgb.Dataset(X_train, label=y_train, categorical_feature=cat_features)\n    d_valid = lgb.Dataset(X_valid, label=y_valid, categorical_feature=cat_features)\n    watchlist = [d_train, d_valid]\n\n    model = lgb.train(params,\n                      train_set=d_train,\n                      num_boost_round=num_rounds,\n                      valid_sets=watchlist,\n                      verbose_eval=verbose_eval,\n                      early_stopping_rounds=early_stop)\n\n    # predictions\n    y_pred_valid = model.predict(X_valid, num_iteration=model.best_iteration)\n    \n    log = {'train/mae': model.best_score['training']['l2'],\n           'valid/mae': model.best_score['valid_1']['l2']}\n    return model, y_pred_valid, log\n\nfolds = 5\nseed = 666\nshuffle = False\nkf = StratifiedKFold(n_splits=folds, shuffle=shuffle, random_state=seed)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"%%time\ntarget_meter = 0\nX_train, y_train = create_X_y(train_df, target_meter=target_meter)\ny_valid_pred_total = np.zeros(X_train.shape[0])\ngc.collect()\nprint('target_meter', target_meter, X_train.shape)\n\ncat_features = [X_train.columns.get_loc(cat_col) for cat_col in category_cols]\nprint('cat_features', cat_features)\n\nmodels0 = []\nfor train_idx, valid_idx in kf.split(X_train, X_train['building_id']):\n    train_data = X_train.iloc[train_idx,:], y_train[train_idx]\n    valid_data = X_train.iloc[valid_idx,:], y_train[valid_idx]\n\n    model, y_pred_valid, log = fit_lgbm(train_data, valid_data, cat_features=category_cols,\n                                        num_rounds=1000, lr=0.05, bf=0.9)\n    y_valid_pred_total[valid_idx] = y_pred_valid\n    models0.append(model)\n    gc.collect()\n    if debug:\n        break\n\nsns.distplot(y_train)\ndel X_train, y_train\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\ntarget_meter = 1\nX_train, y_train = create_X_y(train_df, target_meter=target_meter)\ny_valid_pred_total = np.zeros(X_train.shape[0])\ngc.collect()\nprint('target_meter', target_meter, X_train.shape)\n\ncat_features = [X_train.columns.get_loc(cat_col) for cat_col in category_cols]\nprint('cat_features', cat_features)\n\nmodels1 = []\nfor train_idx, valid_idx in kf.split(X_train, X_train['building_id']):\n    train_data = X_train.iloc[train_idx,:], y_train[train_idx]\n    valid_data = X_train.iloc[valid_idx,:], y_train[valid_idx]\n\n    model, y_pred_valid, log = fit_lgbm(train_data, valid_data, cat_features=category_cols, num_rounds=1000,\n                                       lr=0.05, bf=0.9)\n    y_valid_pred_total[valid_idx] = y_pred_valid\n    models1.append(model)\n    gc.collect()\n    if debug:\n        break\n\nsns.distplot(y_train)\ndel X_train, y_train\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\ntarget_meter = 2\nX_train, y_train = create_X_y(train_df, target_meter=target_meter)\ny_valid_pred_total = np.zeros(X_train.shape[0])\n\ngc.collect()\nprint('target_meter', target_meter, X_train.shape)\n\ncat_features = [X_train.columns.get_loc(cat_col) for cat_col in category_cols]\nprint('cat_features', cat_features)\n\nmodels2 = []\nfor train_idx, valid_idx in kf.split(X_train, X_train['building_id']):\n    train_data = X_train.iloc[train_idx,:], y_train[train_idx]\n    valid_data = X_train.iloc[valid_idx,:], y_train[valid_idx]\n\n    model, y_pred_valid, log = fit_lgbm(train_data, valid_data, cat_features=category_cols,\n                                        num_rounds=1000, lr=0.05, bf=0.9)\n    y_valid_pred_total[valid_idx] = y_pred_valid\n    models2.append(model)\n    gc.collect()\n    if debug:\n        break\n\nsns.distplot(y_train)\ndel X_train, y_train\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\ntarget_meter = 3\nX_train, y_train = create_X_y(train_df, target_meter=target_meter)\ny_valid_pred_total = np.zeros(X_train.shape[0])\n\ngc.collect()\nprint('target_meter', target_meter, X_train.shape)\n\ncat_features = [X_train.columns.get_loc(cat_col) for cat_col in category_cols]\nprint('cat_features', cat_features)\n\nmodels3 = []\nfor train_idx, valid_idx in kf.split(X_train, X_train['building_id']):\n    train_data = X_train.iloc[train_idx,:], y_train[train_idx]\n    valid_data = X_train.iloc[valid_idx,:], y_train[valid_idx]\n\n    model, y_pred_valid, log = fit_lgbm(train_data, valid_data, cat_features=category_cols, num_rounds=1000,\n                                       lr=0.03, bf=0.9)\n    y_valid_pred_total[valid_idx] = y_pred_valid\n    models3.append(model)\n    gc.collect()\n    if debug:\n        break\n\nsns.distplot(y_train)\ndel X_train, y_train\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print('preprocessing building...')\ntest_df['date'] = test_df['timestamp'].dt.date\npreprocess(test_df)\ntest_df['building_mean'] = test_df['building_id'].map(building_mean)\ntest_df['building_median'] = test_df['building_id'].map(building_median)\ntest_df['building_min'] = test_df['building_id'].map(building_min)\ntest_df['building_max'] = test_df['building_id'].map(building_max)\ntest_df['building_std'] = test_df['building_id'].map(building_std)\n\nprint('preprocessing weather...')\nweather_test_df = timestamp_align(weather_test_df)\nweather_test_df = weather_test_df.groupby('site_id').apply(lambda group: group.interpolate(limit_direction='both'))\nweather_test_df.groupby('site_id').apply(lambda group: group.isna().sum())\n\n# add_lag_feature(weather_test_df, window=3)\nadd_lag_feature(weather_test_df, window=72)\n\nprint('reduce mem usage...')\nreduce_mem_usage(test_df, use_float16=True)\nreduce_mem_usage(weather_test_df, use_float16=True)\n\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sub = pd.read_feather('../input/ashrae-feather/sample_submission.ft')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def create_X(test_df, target_meter):\n    target_test_df = test_df[test_df['meter'] == target_meter]\n    target_test_df = target_test_df.merge(building_df, on='building_id', how='left')\n    target_test_df = target_test_df.merge(weather_test_df, on=['site_id', 'timestamp'], how='left')\n    preprocess(target_test_df)\n    target_test_df = target_test_df.merge(bm, right_index=True, left_on=bm_cols, how='left')\n    \n    X_test = target_test_df[feature_cols + category_cols]\n    return X_test","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def pred(X_test, models, batch_size=2000000):\n    iterations = (X_test.shape[0] + batch_size -1) // batch_size\n\n    y_test_pred_total = np.zeros(X_test.shape[0])\n    for i, model in enumerate(models):\n        for k in tqdm(range(iterations)):\n            y_pred_test = model.predict(X_test[k*batch_size:(k+1)*batch_size], num_iteration=model.best_iteration)\n            y_test_pred_total[k*batch_size:(k+1)*batch_size] += y_pred_test\n\n    y_test_pred_total /= len(models)\n    return y_test_pred_total","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\nX_test = create_X(test_df, target_meter=0)\n\ny_test0 = pred(X_test, models0)\n\nsns.distplot(y_test0)\n\ndel X_test\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\nX_test = create_X(test_df, target_meter=1)\n\ny_test1 = pred(X_test, models1)\nsns.distplot(y_test1)\n\ndel X_test\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\nX_test = create_X(test_df, target_meter=2)\n\ny_test2 = pred(X_test, models2)\nsns.distplot(y_test2)\n\ndel X_test\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\nX_test = create_X(test_df, target_meter=3)\n\ny_test3 = pred(X_test, models3)\nsns.distplot(y_test3)\n\ndel X_test\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sub.loc[test_df['meter'] == 0, 'meter_reading'] = np.expm1(y_test0)\nsub.loc[test_df['meter'] == 1, 'meter_reading'] = np.expm1(y_test1)\nsub.loc[test_df['meter'] == 2, 'meter_reading'] = np.expm1(y_test2)\nsub.loc[test_df['meter'] == 3, 'meter_reading'] = np.expm1(y_test3)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\nsub['meter_reading'] = np.clip(sub['meter_reading'].values, a_min=0, a_max=None)\nsub['meter_reading'] = sub['meter_reading'].astype('float32')\nsub.to_csv('submission.csv', index=False, chunksize=25000, float_format='%.4f')\nsub.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"np.log1p(sub['meter_reading']).hist()\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.yscale('log')\nsub['meter_reading'].hist()\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"def plot_feature_importance(model):\n    importance_df = pd.DataFrame(model.feature_importance(),\n                                 index=feature_cols + category_cols,\n                                 columns=['importance']).sort_values('importance')\n    fig, ax = plt.subplots(figsize=(8, 8))\n    importance_df.plot.barh(ax=ax)\n    plt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_feature_importance(models0[1])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_feature_importance(models1[1])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_feature_importance(models2[1])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_feature_importance(models3[1])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Submission"},{"metadata":{"trusted":true},"cell_type":"code","source":"def rmse(ytrue, ypred):\n    return np.sqrt(np.mean(np.square(ypred - ytrue), axis=0))\ndef rmsle(ytrue, ypred):\n    return np.sqrt(np.mean(np.square(np.log1p(ypred) - np.log1p(ytrue)), axis=0))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sub.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(f\"sub mean: {sub['meter_reading'].mean():.4f}\")\nprint(f\"sub std: {sub['meter_reading'].std():.4f}\")\nprint(f\"sub min: {sub['meter_reading'].min():.4f}\")\nprint(f\"sub max: {sub['meter_reading'].max():.4f}\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.distplot(np.log1p(sub['meter_reading'].values), kde=False);","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Submission Validation UCF Site0 (bld 0-104)"},{"metadata":{"trusted":true},"cell_type":"code","source":"site0 = pd.read_feather('../input/ucf-building-meter-reading/site0.ft')\ndf_test = pd.read_feather('../input/ashrae-feather/test.ft')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"merged = df_test.merge(site0, left_on=['building_id', 'meter', 'timestamp'], \n              right_on=['building_id', 'meter', 'timestamp'], how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ytrue = merged[~merged['meter_reading'].isna()]['meter_reading']\npred = sub[~merged['meter_reading'].isna()]['meter_reading']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del site0, merged\nprint(f'RMSLE of buildings 0-104: {rmsle(ytrue, pred):.4f}')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Submission Validation UCL Site1 (bld 105-155)"},{"metadata":{"trusted":true},"cell_type":"code","source":"site1 = pd.read_feather('../input/ucl-data-leakage-episode-2/site1.ft')\nsite1 = site1.query('timestamp >= 2017')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"merged = df_test.merge(site1, left_on=['building_id', 'meter', 'timestamp'], \n              right_on=['building_id', 'meter', 'timestamp'], how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ytrue = merged[~merged['meter_reading'].isna()]['meter_reading']\npred = sub[~merged['meter_reading'].isna()]['meter_reading']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del merged, site1\nprint(f'RMSLE of buildings 105-155: {rmsle(ytrue, pred):.4f}')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Submission Validation ASU Site2 (bld 156-290)"},{"metadata":{"trusted":true},"cell_type":"code","source":"site2 = pd.read_feather('../input/asu-feather/site2.ft')\nsite2 = site2.query('timestamp >= 2017')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"merged = df_test.merge(site2, left_on=['building_id', 'meter', 'timestamp'], \n              right_on=['building_id', 'meter', 'timestamp'], how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ytrue = merged[~merged['meter_reading'].isna()]['meter_reading']\npred = sub[~merged['meter_reading'].isna()]['meter_reading']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del site2, merged\nprint(f'RMSLE of buildings 156-290: {rmsle(ytrue, pred):.4f}')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Submission Validation UCB Site4 (74 blds)"},{"metadata":{"trusted":true},"cell_type":"code","source":"site4 = pd.read_feather('../input/ucb-feather/site4.ft')\nsite4 = site4.query('timestamp >= 2017')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"merged = df_test.merge(site4, left_on=['building_id', 'timestamp'], \n              right_on=['building_id', 'timestamp'], how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ytrue = merged[~merged['meter_reading'].isna()]['meter_reading']\npred = sub[~merged['meter_reading'].isna()]['meter_reading']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del site4, merged\nprint(f'RMSLE of 74/91 buildings : {rmsle(ytrue, pred):.4f}')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Submission Validation Cornell Site15 (bld 1325-1448)"},{"metadata":{"trusted":true},"cell_type":"code","source":"site15 = pd.read_feather('../input/cornell-feather/site15.ft')\nsite15 = site15.query('timestamp >= 2017')\nsite15 = site15.drop_duplicates()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"merged = df_test.merge(site15, left_on=['building_id', 'meter', 'timestamp'], \n              right_on=['building_id', 'meter', 'timestamp'], how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ytrue = merged[~merged['meter_reading'].isna()]['meter_reading']\npred = sub[~merged['meter_reading'].isna()]['meter_reading']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del site15, merged\nprint(f'RMSLE of buildings 1325-1448: {rmsle(ytrue, pred):.4f}')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Submission Validation Site0-2 (bld 0-290)"},{"metadata":{"trusted":true},"cell_type":"code","source":"site012 = pd.read_feather('../input/comb-leaked-dataset/site012.ft')\nsite012 = site012.query('timestamp >= 2017')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"merged = df_test.merge(site012, left_on=['building_id', 'meter', 'timestamp'], \n              right_on=['building_id', 'meter', 'timestamp'], how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ytrue = merged[~merged['meter_reading'].isna()]['meter_reading']\npred = sub[~merged['meter_reading'].isna()]['meter_reading']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del site012, merged\nprint(f'RMSLE of buildings 0-290: {rmsle(ytrue, pred):.4f}')","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.7.3"}},"nbformat":4,"nbformat_minor":1}