{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"Credit to: \n\nkxx for https://www.kaggle.com/kailex/ac-dc <br>\nVopani for https://www.kaggle.com/rohanrao/ashrae-half-and-half <br>\nNZ for https://www.kaggle.com/nz0722/aligned-timestamp-lgbm-by-meter-type <br>","metadata":{}},{"cell_type":"code","source":"import gc\nimport os\nimport random\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport lightgbm as lgb\nimport numpy as np\nimport pandas as pd\nimport pickle\nfrom sklearn.metrics import mean_squared_error\nfrom sklearn.preprocessing import LabelEncoder\nimport datetime\n\nmyfavouritenumber = 0\nseed = myfavouritenumber\nrandom.seed(seed)\n\nimport warnings\nwarnings.filterwarnings('ignore')","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2021-08-09T11:53:03.016765Z","iopub.execute_input":"2021-08-09T11:53:03.017427Z","iopub.status.idle":"2021-08-09T11:53:04.716992Z","shell.execute_reply.started":"2021-08-09T11:53:03.017368Z","shell.execute_reply":"2021-08-09T11:53:04.715596Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\ndf_train = pd.read_feather('../input/ashrae-great-energy-predictor-iii-featherdataset/train.feather')\ndf_test = pd.read_feather('../input/ashrae-great-energy-predictor-iii-featherdataset/test.feather')\n\nbuilding = pd.read_feather('../input/ashrae-great-energy-predictor-iii-featherdataset/building_metadata.feather')\nle = LabelEncoder()\nbuilding.primary_use = le.fit_transform(building.primary_use)\n\n# weather_train = pd.read_feather('../input/ashrae-feather/weather_train.ft')\n# weather_test = pd.read_feather('../input/ashrae-feather/weather_test.ft')\n\nDATA_PATH = \"../input/ashrae-energy-prediction/\"\nweather_train = pd.read_csv(DATA_PATH + 'weather_train.csv')\nweather_test = weather_df = pd.read_csv(DATA_PATH + 'weather_test.csv')","metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","execution":{"iopub.status.busy":"2021-08-09T11:53:04.719596Z","iopub.execute_input":"2021-08-09T11:53:04.719924Z","iopub.status.idle":"2021-08-09T11:53:25.518393Z","shell.execute_reply.started":"2021-08-09T11:53:04.71987Z","shell.execute_reply":"2021-08-09T11:53:25.517419Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\ndf_train = df_train.query('not (building_id <= 104 & meter == 0 & timestamp <= \"2016-05-20 18\")')\ndf_train = df_train.query('not (building_id == 681 & meter == 0 & timestamp <= \"2016-04-27\")')\ndf_train = df_train.query('not (building_id == 761 & meter == 0 & timestamp <= \"2016-09-02\")')\ndf_train = df_train.query('not (building_id == 799 & meter == 0 & timestamp <= \"2016-09-02\")')\ndf_train = df_train.query('not (building_id == 802 & meter == 0 & timestamp <= \"2016-08-24\")')\ndf_train = df_train.query('not (building_id == 1073 & meter == 0 & timestamp <= \"2016-10-26\")')\ndf_train = df_train.query('not (building_id == 1094 & meter == 0 & timestamp <= \"2016-09-08\")')\ndf_train = df_train.query('not (building_id == 29 & meter == 0 & timestamp <= \"2016-08-10\")')\ndf_train = df_train.query('not (building_id == 40 & meter == 0 & timestamp <= \"2016-06-04\")')\ndf_train = df_train.query('not (building_id == 45 & meter == 0 & timestamp <= \"2016-07\")')\ndf_train = df_train.query('not (building_id == 106 & meter == 0 & timestamp <= \"2016-11\")')\ndf_train = df_train.query('not (building_id == 107 & meter == 0 & timestamp >= \"2016-11-10\")')\ndf_train = df_train.query('not (building_id == 112 & meter == 0 & timestamp < \"2016-10-31 15\")')\ndf_train = df_train.query('not (building_id == 144 & meter == 0 & timestamp > \"2016-05-14\" & timestamp < \"2016-10-31\")')\ndf_train = df_train.query('not (building_id == 147 & meter == 0 & timestamp > \"2016-06-05 19\" & timestamp < \"2016-07-18 15\")')\ndf_train = df_train.query('not (building_id == 171 & meter == 0 & timestamp <= \"2016-07-05\")')\ndf_train = df_train.query('not (building_id == 177 & meter == 0 & timestamp > \"2016-06-04\" & timestamp < \"2016-06-25\")')\ndf_train = df_train.query('not (building_id == 258 & meter == 0 & timestamp > \"2016-09-26\" & timestamp < \"2016-12-12\")')\ndf_train = df_train.query('not (building_id == 258 & meter == 0 & timestamp > \"2016-08-30\" & timestamp < \"2016-09-08\")')\ndf_train = df_train.query('not (building_id == 258 & meter == 0 & timestamp > \"2016-09-18\" & timestamp < \"2016-09-25\")')\ndf_train = df_train.query('not (building_id == 260 & meter == 0 & timestamp <= \"2016-05-11\")')\ndf_train = df_train.query('not (building_id == 269 & meter == 0 & timestamp > \"2016-06-04\" & timestamp < \"2016-06-25\")')\ndf_train = df_train.query('not (building_id == 304 & meter == 0 & timestamp >= \"2016-11-20\")')\ndf_train = df_train.query('not (building_id == 545 & meter == 0 & timestamp > \"2016-01-17\" & timestamp < \"2016-02-10\")')\ndf_train = df_train.query('not (building_id == 604 & meter == 0 & timestamp < \"2016-11-21\")')\ndf_train = df_train.query('not (building_id == 693 & meter == 0 & timestamp > \"2016-09-07\" & timestamp < \"2016-11-23\")')\ndf_train = df_train.query('not (building_id == 693 & meter == 0 & timestamp > \"2016-07-12\" & timestamp < \"2016-05-29\")')\ndf_train = df_train.query('not (building_id == 723 & meter == 0 & timestamp > \"2016-10-06\" & timestamp < \"2016-11-22\")')\ndf_train = df_train.query('not (building_id == 733 & meter == 0 & timestamp > \"2016-05-29\" & timestamp < \"2016-06-22\")')\ndf_train = df_train.query('not (building_id == 733 & meter == 0 & timestamp > \"2016-05-19\" & timestamp < \"2016-05-20\")')\ndf_train = df_train.query('not (building_id == 803 & meter == 0 & timestamp > \"2016-9-25\")')\ndf_train = df_train.query('not (building_id == 815 & meter == 0 & timestamp > \"2016-05-17\" & timestamp < \"2016-11-17\")')\ndf_train = df_train.query('not (building_id == 848 & meter == 0 & timestamp > \"2016-01-15\" & timestamp < \"2016-03-20\")')\ndf_train = df_train.query('not (building_id == 857 & meter == 0 & timestamp > \"2016-04-13\")')\ndf_train = df_train.query('not (building_id == 909 & meter == 0 & timestamp < \"2016-02-02\")')\ndf_train = df_train.query('not (building_id == 909 & meter == 0 & timestamp < \"2016-06-23\")')\ndf_train = df_train.query('not (building_id == 1008 & meter == 0 & timestamp > \"2016-10-30\" & timestamp < \"2016-11-21\")')\ndf_train = df_train.query('not (building_id == 1113 & meter == 0 & timestamp < \"2016-07-27\")')\ndf_train = df_train.query('not (building_id == 1153 & meter == 0 & timestamp < \"2016-01-20\")')\ndf_train = df_train.query('not (building_id == 1169 & meter == 0 & timestamp < \"2016-08-03\")')\ndf_train = df_train.query('not (building_id == 1170 & meter == 0 & timestamp > \"2016-06-30\" & timestamp < \"2016-07-05\")')\ndf_train = df_train.query('not (building_id == 1221 & meter == 0 & timestamp < \"2016-11-04\")')\ndf_train = df_train.query('not (building_id == 1225 & meter == 0 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id == 1234 & meter == 0 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id >= 1233 & building_id <= 1234 & meter == 0 & timestamp > \"2016-01-13 22\" & timestamp < \"2016-03-08 12\")')\ndf_train = df_train.query('not (building_id == 1241 & meter == 0 & timestamp > \"2016-07-14\" & timestamp < \"2016-11-19\")')\ndf_train = df_train.query('not (building_id == 1250 & meter == 0 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id == 1255 & meter == 0 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id == 1264 & meter == 0 & timestamp > \"2016-08-23\")')\ndf_train = df_train.query('not (building_id == 1265 & meter == 0 & timestamp > \"2016-05-06\" & timestamp < \"2016-05-26\")')\ndf_train = df_train.query('not (building_id == 1272 & meter == 0 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id >= 1275 & building_id <= 1280 & meter == 0 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id == 1283 & meter == 0 & timestamp > \"2016-07-08\" & timestamp < \"2016-08-03\")')\ndf_train = df_train.query('not (building_id >= 1291 & building_id <= 1302 & meter == 0 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id == 1303 & meter == 0 & timestamp > \"2016-07-25 22\" & timestamp < \"2016-07-27 16\")')\ndf_train = df_train.query('not (building_id == 1303 & meter == 0 & timestamp > \"2016-01-26\" & timestamp < \"2016-06-02 12\")')\ndf_train = df_train.query('not (building_id == 1319 & meter == 0 & timestamp > \"2016-05-17 16\" & timestamp < \"2016-06-07 12\")')\ndf_train = df_train.query('not (building_id == 1319 & meter == 0 & timestamp > \"2016-08-18 14\" & timestamp < \"2016-09-02 14\")')\ndf_train = df_train.query('not (building_id == 1322 & meter == 0 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\n\n# 2nd cleaning\ndf_train = df_train.query('not (building_id >= 874 & building_id <= 997 & meter == 0 & timestamp > \"2016-10-14 22\" & timestamp < \"2016-10-17 08\")')\ndf_train = df_train.query('not (building_id >= 874 & building_id <= 997 & meter == 0 & timestamp > \"2016-07-01 14\" & timestamp < \"2016-07-05 06\")')\ndf_train = df_train.query('not (building_id >= 874 & building_id <= 997 & meter == 1 & timestamp > \"2016-10-14 22\" & timestamp < \"2016-10-17 08\")')\ndf_train = df_train.query('not (building_id >= 874 & building_id <= 997 & meter == 1 & timestamp > \"2016-07-01 14\" & timestamp < \"2016-07-05 06\")')\ndf_train = df_train.query('not (building_id >= 874 & building_id <= 997 & meter == 2 & timestamp > \"2016-10-14 22\" & timestamp < \"2016-10-17 08\")')\ndf_train = df_train.query('not (building_id >= 874 & building_id <= 997 & meter == 2 & timestamp > \"2016-07-01 14\" & timestamp < \"2016-07-05 06\")')\ndf_train = df_train.query('not (building_id == 1272 & meter == 1 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id >= 1291 & building_id <= 1297 & meter == 1 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id == 1300 & meter == 1 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id == 1302 & meter == 1 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id >= 1291 & building_id <= 1299 & meter == 2 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id == 1221 & meter == 0 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id >= 1225 & building_id <= 1226 & meter == 0 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id >= 1233 & building_id <= 1234 & meter == 0 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id == 1241 & meter == 0 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id == 1223 & meter == 1 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id == 1226 & meter == 1 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id >= 1233 & building_id <= 1234 & meter == 1 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id >= 1225 & building_id <= 1226 & meter == 2 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id == 1305 & meter == 2 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id == 1307 & meter == 2 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id == 1223 & meter == 3 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id == 1231 & meter == 3 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id >= 1233 & building_id <= 1234 & meter == 3 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id == 1272 & meter == 3 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id >= 1275 & building_id <= 1297 & meter == 3 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id == 1300 & meter == 3 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id == 1302 & meter == 3 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id == 1293 & meter == 3 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-25 12\")')\ndf_train = df_train.query('not (building_id == 1302 & meter == 3 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-25 12\")')\ndf_train = df_train.query('not (building_id == 1223 & meter == 0 & timestamp > \"2016-9-28 07\" & timestamp < \"2016-10-11 18\")')\ndf_train = df_train.query('not (building_id == 1225 & meter == 1 & timestamp > \"2016-8-22 23\" & timestamp < \"2016-10-11 14\")')\ndf_train = df_train.query('not (building_id == 1230 & meter == 1 & timestamp > \"2016-8-22 08\" & timestamp < \"2016-10-05 18\")')\ndf_train = df_train.query('not (building_id == 904 & meter == 0 & timestamp < \"2016-02-17 08\")')\ndf_train = df_train.query('not (building_id == 986 & meter == 0 & timestamp < \"2016-02-17 08\")')\ndf_train = df_train.query('not (building_id == 954 & meter == 0 & timestamp < \"2016-08-08 11\")')\ndf_train = df_train.query('not (building_id == 954 & meter == 0 & timestamp < \"2016-06-23 08\")')\ndf_train = df_train.query('not (building_id >= 745 & building_id <= 770 & meter == 1 & timestamp > \"2016-10-05 01\" & timestamp < \"2016-10-10 09\")')\ndf_train = df_train.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\ndf_train = df_train.query('not (building_id >= 874 & building_id <= 997 & meter == 0 & timestamp > \"2016-05-11 09\" & timestamp < \"2016-05-12 01\")')\ndf_train = df_train.query('not (building_id >= 874 & building_id <= 997 & meter == 1 & timestamp > \"2016-05-11 09\" & timestamp < \"2016-05-12 01\")')\ndf_train = df_train.query('not (building_id >= 874 & building_id <= 997 & meter == 2 & timestamp > \"2016-05-11 09\" & timestamp < \"2016-05-12 01\")')\n\ndf_train = df_train.query('not (building_id >= 874 & building_id <= 997 & meter == 0 & timestamp == \"2016-02-26 01\")')\ndf_train = df_train.query('not (building_id >= 874 & building_id <= 997 & meter == 1 & timestamp == \"2016-02-26 01\")')\ndf_train = df_train.query('not (building_id >= 874 & building_id <= 997 & meter == 2 & timestamp == \"2016-02-26 01\")')\n\ndf_train = df_train.query('not (building_id >= 874 & building_id <= 997 & meter == 0 & timestamp > \"2016-03-29 10\" & timestamp < \"2016-03-30 12\")')\ndf_train = df_train.query('not (building_id >= 874 & building_id <= 997 & meter == 1 & timestamp > \"2016-03-29 10\" & timestamp < \"2016-03-30 12\")')\ndf_train = df_train.query('not (building_id >= 874 & building_id <= 997 & meter == 2 & timestamp > \"2016-03-29 10\" & timestamp < \"2016-03-30 12\")')\n\ndf_train = df_train.query('not (building_id >= 874 & building_id <= 997 & meter == 0 & timestamp > \"2016-01-19 23\" & timestamp < \"2016-01-28 15\")')\ndf_train = df_train.query('not (building_id >= 874 & building_id <= 997 & meter == 1 & timestamp > \"2016-01-19 23\" & timestamp < \"2016-01-28 15\")')\ndf_train = df_train.query('not (building_id >= 874 & building_id <= 997 & meter == 2 & timestamp > \"2016-01-19 23\" & timestamp < \"2016-01-28 15\")')\n\ndf_train = df_train.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)\ndf_train = df_train.query('not (building_id >= 1223 & building_id <= 1324 & meter==1 & timestamp > \"2016-07-16 04\" & timestamp < \"2016-07-19 11\")')\ndf_train = df_train.query('not (building_id == 107 & meter == 0 & timestamp <= \"2016-07-06\")')\ndf_train = df_train.query('not (building_id == 180 & timestamp >= \"2016-02-17 12\")')\ndf_train = df_train.query('not (building_id == 182 & meter == 0)')\ndf_train = df_train.query('not (building_id == 191 & meter == 0 & timestamp >= \"2016-12-22 09\")')\ndf_train = df_train.query('not (building_id == 192 & meter == 1 & timestamp >= \"2016-05-09 18\")')\ndf_train = df_train.query('not (building_id == 192 & meter == 3 & timestamp >= \"2016-03-29 05\" & timestamp <= \"2016-04-04 08\")')\ndf_train = df_train.query('not (building_id == 207 & meter == 1 & timestamp > \"2016-07-02 20\" & timestamp < \"2016-08-25 12\")')\ndf_train = df_train.query('not (building_id == 258 & timestamp > \"2016-09-18\" & timestamp < \"2016-12-12 13\")')\ndf_train = df_train.query('not (building_id == 258 & timestamp > \"2016-08-29 08\" & timestamp < \"2016-09-08 14\")')\ndf_train = df_train.query('not (building_id == 257 & meter == 1 & timestamp < \"2016-03-25 16\")')\ndf_train = df_train.query('not (building_id == 260 & meter == 1 & timestamp > \"2016-05-10 17\" & timestamp < \"2016-08-17 11\")')\ndf_train = df_train.query('not (building_id == 260 & meter == 1 & timestamp > \"2016-08-28 01\" & timestamp < \"2016-10-31 13\")')\ndf_train = df_train.query('not (building_id == 220 & meter == 1 & timestamp > \"2016-09-23 01\" & timestamp < \"2016-09-23 12\")')\ndf_train = df_train.query('not (building_id == 281 & meter == 1 & timestamp > \"2016-10-25 08\" & timestamp < \"2016-11-04 15\")')\ndf_train = df_train.query('not (building_id == 273 & meter == 1 & timestamp > \"2016-04-03 04\" & timestamp < \"2016-04-29 15\")')\ndf_train = df_train.query('not (building_id == 28 & meter == 0 & timestamp < \"2016-10-14 20\")')\ndf_train = df_train.query('not (building_id == 71 & meter == 0 & timestamp < \"2016-08-18 20\")')\ndf_train = df_train.query('not (building_id == 76 & meter == 0 & timestamp > \"2016-06-04 09\" & timestamp < \"2016-06-04 14\")')\ndf_train = df_train.query('not (building_id == 101 & meter == 0 & timestamp > \"2016-10-12 13\" & timestamp < \"2016-10-12 18\")')\ndf_train = df_train.query('not (building_id == 7 & meter == 1 & timestamp > \"2016-11-03 09\" & timestamp < \"2016-11-28 14\")')\ndf_train = df_train.query('not (building_id == 9 & meter == 1 & timestamp > \"2016-12-06 08\")')\ndf_train = df_train.query('not (building_id == 43 & meter == 1 & timestamp > \"2016-04-03 08\" & timestamp < \"2016-06-06 13\")')\ndf_train = df_train.query('not (building_id == 60 & meter == 1 & timestamp > \"2016-05-01 17\" & timestamp < \"2016-05-01 21\")')\ndf_train = df_train.query('not (building_id == 75 & meter == 1 & timestamp > \"2016-08-05 13\" & timestamp < \"2016-08-26 12\")')\ndf_train = df_train.query('not (building_id == 95 & meter == 1 & timestamp > \"2016-08-08 10\" & timestamp < \"2016-08-26 13\")')\ndf_train = df_train.query('not (building_id == 97 & meter == 1 & timestamp > \"2016-08-08 14\" & timestamp < \"2016-08-25 14\")')\ndf_train = df_train.query('not (building_id == 1232 & meter == 1 & timestamp > \"2016-06-23 16\" & timestamp < \"2016-08-31 20\")')\ndf_train = df_train.query('not (building_id == 1236 & meter == 1 & meter_reading >= 3000)')\ndf_train = df_train.query('not (building_id == 1239 & meter == 1 & timestamp > \"2016-03-11 16\" & timestamp < \"2016-03-27 17\")')\ndf_train = df_train.query('not (building_id == 1264 & meter == 1 & timestamp > \"2016-08-22 17\" & timestamp < \"2016-09-22 20\")')\ndf_train = df_train.query('not (building_id == 1264 & meter == 1 & timestamp > \"2016-09-28 07\" & timestamp < \"2016-10-20 13\")')\ndf_train = df_train.query('not (building_id == 1269 & meter == 1 & meter_reading >= 2000)')\ndf_train = df_train.query('not (building_id == 1272 & meter == 1 & timestamp > \"2016-08-11 12\" & timestamp < \"2016-08-30 19\")')\ndf_train = df_train.query('not (building_id == 1273 & meter == 1 & timestamp > \"2016-05-31 14\" & timestamp < \"2016-06-17\")')\ndf_train = df_train.query('not (building_id == 1276 & meter == 1 & timestamp < \"2016-02-03 23\")')\ndf_train = df_train.query('not (building_id == 1280 & meter == 1 & timestamp > \"2016-05-18\" & timestamp < \"2016-05-26 09\")')\ndf_train = df_train.query('not (building_id == 1280 & meter == 1 & timestamp > \"2016-02-28 23\" & timestamp < \"2016-05-02 05\")')\ndf_train = df_train.query('not (building_id == 1280 & meter == 1 & timestamp > \"2016-06-12 01\" & timestamp < \"2016-7-07 06\")')\ndf_train = df_train.query('not (building_id == 1288 & meter == 1 & timestamp > \"2016-07-07 15\" & timestamp < \"2016-08-12 17\")')\ndf_train = df_train.query('not (building_id == 1311 & meter == 1 & timestamp > \"2016-04-25 18\" & timestamp < \"2016-05-13 14\")')\ndf_train = df_train.query('not (building_id == 1099 & meter == 2)')\n\ndf_train = df_train.query('not (building_id == 1329 & meter == 0 & timestamp > \"2016-04-28 00\" & timestamp < \"2016-04-28 07\")')\ndf_train = df_train.query('not (building_id == 1331 & meter == 0 & timestamp > \"2016-04-28 00\" & timestamp < \"2016-04-28 07\")')\ndf_train = df_train.query('not (building_id == 1427 & meter == 0 & timestamp > \"2016-04-11 10\" & timestamp < \"2016-04-11 14\")')\ndf_train = df_train.query('not (building_id == 1426 & meter == 2 & timestamp > \"2016-05-03 09\" & timestamp < \"2016-05-03 14\")')\ndf_train = df_train.query('not (building_id == 1345 & meter == 0 & timestamp < \"2016-03-01\")')\ndf_train = df_train.query('not (building_id == 1346 & timestamp < \"2016-03-01\")')\ndf_train = df_train.query('not (building_id == 1359 & meter == 0 & timestamp > \"2016-04-25 17\" & timestamp < \"2016-07-22 14\")')\ndf_train = df_train.query('not (building_id == 1365 & meter == 0 & timestamp > \"2016-08-19 00\" & timestamp < \"2016-08-19 07\")')\ndf_train = df_train.query('not (building_id == 1365 & meter == 0 & timestamp > \"2016-06-18 22\" & timestamp < \"2016-06-19 06\")')\n\ndf_train = df_train.query('not (building_id == 18 & meter == 0 & timestamp > \"2016-06-04 09\" & timestamp < \"2016-06-04 16\")')\ndf_train = df_train.query('not (building_id == 18 & meter == 0 & timestamp > \"2016-11-05 05\" & timestamp < \"2016-11-05 15\")')\ndf_train = df_train.query('not (building_id == 101 & meter == 0 & meter_reading > 800)')\n\ndf_train = df_train.query('not (building_id == 1384 & meter == 0 & meter_reading == 0 )')\ndf_train = df_train.query('not (building_id >= 1289 & building_id <= 1301 & meter == 2 & meter_reading == 0)')\ndf_train = df_train.query('not (building_id == 1243 & meter == 2 & meter_reading == 0)')\ndf_train = df_train.query('not (building_id == 1263 & meter == 2 & meter_reading == 0)')\ndf_train = df_train.query('not (building_id == 1284 & meter == 2 & meter_reading == 0)')\ndf_train = df_train.query('not (building_id == 1286 & meter == 2 & meter_reading == 0)')\ndf_train = df_train.query('not (building_id == 1263 & meter == 0 & timestamp > \"2016-11-10 11\" & timestamp < \"2016-11-10 15\")')\n\ndf_train = df_train.query('not (building_id == 1238 & meter == 2 & meter_reading == 0)')\ndf_train = df_train.query('not (building_id == 1329 & meter == 2 & timestamp > \"2016-11-21 12\" & timestamp < \"2016-11-29 12\")')\ndf_train = df_train.query('not (building_id == 1249 & meter == 2 & meter_reading == 0)')\n\ndf_train = df_train.query('not (building_id == 1250 & meter == 2 & meter_reading == 0)')\ndf_train = df_train.query('not (building_id == 1256 & meter == 2 & timestamp > \"2016-03-05 18\" & timestamp < \"2016-03-05 22\")')\ndf_train = df_train.query('not (building_id == 1256 & meter == 2 & timestamp > \"2016-03-27 00\" & timestamp < \"2016-03-27 23\")')\ndf_train = df_train.query('not (building_id == 1256 & meter == 2 & timestamp > \"2016-04-11 09\" & timestamp < \"2016-04-13 03\")')\ndf_train = df_train.query('not (building_id == 1256 & meter == 2 & timestamp > \"2016-04-29 00\" & timestamp < \"2016-04-30 15\")')\ndf_train = df_train.query('not (building_id == 1303 & meter == 2 & timestamp < \"2016-06-06 19\")')\ndf_train = df_train.query('not (building_id >= 1223 & building_id <= 1324 & meter == 1 & timestamp > \"2016-08-11 17\" & timestamp < \"2016-08-12 17\")')\ndf_train = df_train.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\")')\ndf_train = df_train.query('not (building_id >= 1223 & building_id <= 1324 & meter == 3 & timestamp > \"2016-08-11 17\" & timestamp < \"2016-08-12 17\")')","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# building_meter map\n\nbm_cols = ['bm', 'weekday', 'hour',]\ndf_train['hour'] = df_train['timestamp'].dt.hour\ndf_train['weekday'] = df_train['timestamp'].dt.weekday\ndf_train['bm'] = df_train['building_id'].apply(lambda x: str(x)) + '_' + df_train['meter'].apply(lambda x: str(x))\nbm = df_train.groupby(bm_cols)['meter_reading'].mean().rename('bm_week_hour').to_frame()","metadata":{"execution":{"iopub.status.busy":"2021-08-09T11:53:25.520186Z","iopub.execute_input":"2021-08-09T11:53:25.520835Z","iopub.status.idle":"2021-08-09T11:54:03.299469Z","shell.execute_reply.started":"2021-08-09T11:53:25.520773Z","shell.execute_reply":"2021-08-09T11:54:03.298354Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train = df_train.merge(bm, right_index=True, left_on=bm_cols, how='left')","metadata":{"execution":{"iopub.status.busy":"2021-08-09T11:54:03.30097Z","iopub.execute_input":"2021-08-09T11:54:03.301232Z","iopub.status.idle":"2021-08-09T11:54:12.145068Z","shell.execute_reply.started":"2021-08-09T11:54:03.301187Z","shell.execute_reply":"2021-08-09T11:54:12.143996Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train.drop(['bm'], axis=1, inplace=True)\ndf_train.head()","metadata":{"execution":{"iopub.status.busy":"2021-08-09T11:54:12.148066Z","iopub.execute_input":"2021-08-09T11:54:12.148362Z","iopub.status.idle":"2021-08-09T11:54:13.619439Z","shell.execute_reply.started":"2021-08-09T11:54:12.148312Z","shell.execute_reply":"2021-08-09T11:54:13.618676Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"weather = pd.concat([weather_train, weather_test],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()","metadata":{"execution":{"iopub.status.busy":"2021-08-09T11:54:13.622545Z","iopub.execute_input":"2021-08-09T11:54:13.622985Z","iopub.status.idle":"2021-08-09T11:54:13.918323Z","shell.execute_reply.started":"2021-08-09T11:54:13.622791Z","shell.execute_reply":"2021-08-09T11:54:13.917039Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"full_weather['timestamp'] = pd.to_datetime(full_weather['timestamp'])","metadata":{"execution":{"iopub.status.busy":"2021-08-09T11:54:13.920097Z","iopub.execute_input":"2021-08-09T11:54:13.920576Z","iopub.status.idle":"2021-08-09T11:54:14.058677Z","shell.execute_reply.started":"2021-08-09T11:54:13.920319Z","shell.execute_reply":"2021-08-09T11:54:14.057385Z"},"trusted":true},"execution_count":null,"outputs":[]},{"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","metadata":{"execution":{"iopub.status.busy":"2021-08-09T11:54:14.060131Z","iopub.execute_input":"2021-08-09T11:54:14.060572Z","iopub.status.idle":"2021-08-09T11:54:14.568743Z","shell.execute_reply.started":"2021-08-09T11:54:14.06038Z","shell.execute_reply":"2021-08-09T11:54:14.568087Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"## Memory optimization\n\n# 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\n\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    \"\"\"\n    Iterate through all the columns of a dataframe and modify the data type to reduce memory usage.        \n    \"\"\"\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            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","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2021-08-09T11:54:14.569813Z","iopub.execute_input":"2021-08-09T11:54:14.570215Z","iopub.status.idle":"2021-08-09T11:54:14.589295Z","shell.execute_reply.started":"2021-08-09T11:54:14.570174Z","shell.execute_reply":"2021-08-09T11:54:14.588546Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def fill_weather_dataset(weather_df):\n    \n    # Find Missing Dates\n    time_format = \"%Y-%m-%d %H:%M:%S\"\n    start_date = datetime.datetime.strptime(weather_df['timestamp'].min(),time_format)\n    end_date = datetime.datetime.strptime(weather_df['timestamp'].max(),time_format)\n    \n#     start_date = weather_df['timestamp'].min()\n#     end_date = weather_df['timestamp'].max()\n    \n    total_hours = int(((end_date - start_date).total_seconds() + 3600) / 3600)\n    hours_list = [(end_date - datetime.timedelta(hours=x)).strftime(time_format) for x in range(total_hours)]\n    \n    missing_hours = []\n    for site_id in range(16):\n        site_hours = np.array(weather_df[weather_df['site_id'] == site_id]['timestamp'])\n        new_rows = pd.DataFrame(np.setdiff1d(hours_list,site_hours),columns=['timestamp'])\n        new_rows['site_id'] = site_id\n        weather_df = pd.concat([weather_df,new_rows])\n\n        weather_df = weather_df.reset_index(drop=True)           \n\n    # Add new Features\n    weather_df[\"timestamp\"] = pd.to_datetime(weather_df[\"timestamp\"])\n    weather_df[\"day\"] = weather_df[\"timestamp\"].dt.day\n    weather_df[\"week\"] = weather_df[\"timestamp\"].dt.week\n    weather_df[\"month\"] = weather_df[\"timestamp\"].dt.month\n    \n    # Reset Index for Fast Update\n    weather_df = weather_df.set_index(['site_id','day','month'])\n\n    air_temperature_filler = pd.DataFrame(weather_df.groupby(['site_id','day','month'])['air_temperature'].mean(),columns=[\"air_temperature\"])\n    weather_df.update(air_temperature_filler,overwrite=False)\n\n    # Step 1\n    cloud_coverage_filler = weather_df.groupby(['site_id','day','month'])['cloud_coverage'].mean()\n    # Step 2\n    cloud_coverage_filler = pd.DataFrame(cloud_coverage_filler.fillna(method='ffill'),columns=[\"cloud_coverage\"])\n\n    weather_df.update(cloud_coverage_filler,overwrite=False)\n\n    due_temperature_filler = pd.DataFrame(weather_df.groupby(['site_id','day','month'])['dew_temperature'].mean(),columns=[\"dew_temperature\"])\n    weather_df.update(due_temperature_filler,overwrite=False)\n\n    # Step 1\n    sea_level_filler = weather_df.groupby(['site_id','day','month'])['sea_level_pressure'].mean()\n    # Step 2\n    sea_level_filler = pd.DataFrame(sea_level_filler.fillna(method='ffill'),columns=['sea_level_pressure'])\n\n    weather_df.update(sea_level_filler,overwrite=False)\n\n    wind_direction_filler =  pd.DataFrame(weather_df.groupby(['site_id','day','month'])['wind_direction'].mean(),columns=['wind_direction'])\n    weather_df.update(wind_direction_filler,overwrite=False)\n\n    wind_speed_filler =  pd.DataFrame(weather_df.groupby(['site_id','day','month'])['wind_speed'].mean(),columns=['wind_speed'])\n    weather_df.update(wind_speed_filler,overwrite=False)\n\n    # Step 1\n    precip_depth_filler = weather_df.groupby(['site_id','day','month'])['precip_depth_1_hr'].mean()\n    # Step 2\n    precip_depth_filler = pd.DataFrame(precip_depth_filler.fillna(method='ffill'),columns=['precip_depth_1_hr'])\n\n    weather_df.update(precip_depth_filler,overwrite=False)\n\n    weather_df = weather_df.reset_index()\n    weather_df = weather_df.drop(['day','week','month'],axis=1)\n        \n    return weather_df","metadata":{"execution":{"iopub.status.busy":"2021-08-09T11:54:14.590421Z","iopub.execute_input":"2021-08-09T11:54:14.59083Z","iopub.status.idle":"2021-08-09T11:54:14.615349Z","shell.execute_reply.started":"2021-08-09T11:54:14.590771Z","shell.execute_reply":"2021-08-09T11:54:14.614227Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"weather_train = fill_weather_dataset(weather_train)","metadata":{"execution":{"iopub.status.busy":"2021-08-09T11:54:14.616824Z","iopub.execute_input":"2021-08-09T11:54:14.61709Z","iopub.status.idle":"2021-08-09T11:54:33.964604Z","shell.execute_reply.started":"2021-08-09T11:54:14.617041Z","shell.execute_reply":"2021-08-09T11:54:33.963767Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"weather_train","metadata":{"execution":{"iopub.status.busy":"2021-08-09T11:54:33.966864Z","iopub.execute_input":"2021-08-09T11:54:33.967184Z","iopub.status.idle":"2021-08-09T11:54:33.991643Z","shell.execute_reply.started":"2021-08-09T11:54:33.967134Z","shell.execute_reply":"2021-08-09T11:54:33.990333Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train = reduce_mem_usage(df_train, use_float16=True)\nbuilding = reduce_mem_usage(building, use_float16=True)\nweather_train = reduce_mem_usage(weather_train, use_float16=True)","metadata":{"_kg_hide-output":true,"execution":{"iopub.status.busy":"2021-08-09T11:54:33.993328Z","iopub.execute_input":"2021-08-09T11:54:33.993659Z","iopub.status.idle":"2021-08-09T11:54:34.747122Z","shell.execute_reply.started":"2021-08-09T11:54:33.99355Z","shell.execute_reply":"2021-08-09T11:54:34.746083Z"},"trusted":true},"execution_count":null,"outputs":[]},{"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))","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2021-08-09T11:54:34.748478Z","iopub.execute_input":"2021-08-09T11:54:34.748748Z","iopub.status.idle":"2021-08-09T11:54:34.75552Z","shell.execute_reply.started":"2021-08-09T11:54:34.748691Z","shell.execute_reply":"2021-08-09T11:54:34.754161Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"le = LabelEncoder()\n\ndef prepare_data(X, building_data, weather_data, test=False):\n    \"\"\"\n    Preparing final dataset with all features.\n    \"\"\"\n    # align timestamp\n    weather_data = timestamp_align(weather_data)\n    \n    X = X.merge(building_data, on=\"building_id\", how=\"left\")\n    X = X.merge(weather_data, on=[\"site_id\", \"timestamp\"], how=\"left\")\n    \n    X.sort_values(\"timestamp\")\n    X.reset_index(drop=True)\n    \n    gc.collect()\n    \n    X.timestamp = pd.to_datetime(X.timestamp, format=\"%Y-%m-%d %H:%M:%S\")\n    X.square_feet = np.log1p(X.square_feet)\n    \n    X[\"hour\"] = X.timestamp.dt.hour\n    X[\"weekday\"] = X.timestamp.dt.weekday\n    X['hour'] = X['hour'].astype('int8')\n    X['weekday'] = X['weekday'].astype('int8')\n    \n    drop_features = [\"timestamp\", \"site_id\",\"sea_level_pressure\", \"precip_depth_1_hr\",]# \"wind_direction\", \"wind_speed\"]\n\n    X.drop(drop_features, axis=1, inplace=True)\n\n    X = reduce_mem_usage(X, use_float16=True)\n    \n    if test:\n        row_ids = X.row_id\n        X.drop(\"row_id\", axis=1, inplace=True)\n        return X, row_ids\n    else:\n        y = np.log1p(X.meter_reading)\n        X.drop(\"meter_reading\", axis=1, inplace=True)\n        return X, y","metadata":{"execution":{"iopub.status.busy":"2021-08-09T11:54:34.757479Z","iopub.execute_input":"2021-08-09T11:54:34.757873Z","iopub.status.idle":"2021-08-09T11:54:34.77347Z","shell.execute_reply.started":"2021-08-09T11:54:34.757802Z","shell.execute_reply":"2021-08-09T11:54:34.771829Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_train, y_train = prepare_data(df_train, building, weather_train)\n\ndel df_train, weather_train\ngc.collect()","metadata":{"_kg_hide-output":true,"execution":{"iopub.status.busy":"2021-08-09T11:54:34.7753Z","iopub.execute_input":"2021-08-09T11:54:34.77572Z","iopub.status.idle":"2021-08-09T11:55:04.023383Z","shell.execute_reply.started":"2021-08-09T11:54:34.77565Z","shell.execute_reply":"2021-08-09T11:55:04.022145Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\nX_half_1 = X_train[:int(X_train.shape[0] / 2)]\nX_half_2 = X_train[int(X_train.shape[0] / 2):]\n\ny_half_1 = y_train[:int(X_train.shape[0] / 2)]\ny_half_2 = y_train[int(X_train.shape[0] / 2):]\n\ncategorical_features = [\"building_id\", \"meter\", \"primary_use\", \"hour\", \"weekday\"]\n\nd_half_1 = lgb.Dataset(X_half_1, label=y_half_1, categorical_feature=categorical_features, free_raw_data=False)\nd_half_2 = lgb.Dataset(X_half_2, label=y_half_2, categorical_feature=categorical_features, free_raw_data=False)\n\nwatchlist_1 = [d_half_1, d_half_2]\nwatchlist_2 = [d_half_2, d_half_1]\n\nparams = {\n    \"objective\": \"regression\",\n    \"boosting\": \"gbdt\",\n    \"num_leaves\": 40,\n    \"learning_rate\": 0.05,\n    \"feature_fraction\": 0.85,\n    \"reg_lambda\": 2,\n    \"metric\": \"rmse\",\n    \"num_threads\": 2\n}\n\nprint(\"Building model with first half and validating on second half:\")\nmodel_half_1 = lgb.train(params, train_set=d_half_1, num_boost_round=1000, valid_sets=watchlist_1, verbose_eval=200, early_stopping_rounds=200)\n\nprint(\"Building model with second half and validating on first half:\")\nmodel_half_2 = lgb.train(params, train_set=d_half_2, num_boost_round=1000, valid_sets=watchlist_2, verbose_eval=200, early_stopping_rounds=200)","metadata":{"execution":{"iopub.status.busy":"2021-08-09T11:55:04.024751Z","iopub.execute_input":"2021-08-09T11:55:04.024979Z","iopub.status.idle":"2021-08-09T12:09:59.371584Z","shell.execute_reply.started":"2021-08-09T11:55:04.024944Z","shell.execute_reply":"2021-08-09T12:09:59.370579Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\npred = np.zeros(len(X_train))\npred1 = model_half_1.predict(X_half_2)\npred2 = model_half_2.predict(X_half_1)","metadata":{"execution":{"iopub.status.busy":"2021-08-09T12:09:59.375522Z","iopub.execute_input":"2021-08-09T12:09:59.376027Z","iopub.status.idle":"2021-08-09T12:13:16.59573Z","shell.execute_reply.started":"2021-08-09T12:09:59.375956Z","shell.execute_reply":"2021-08-09T12:13:16.594484Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"pred = np.zeros(len(X_train))\npred[:len(pred2)] += pred2\npred[len(pred2):] += pred1\nprint(f\"Half/Half RMSE: {rmse(pred, y_train):.4f}\")","metadata":{"execution":{"iopub.status.busy":"2021-08-09T12:13:16.597514Z","iopub.execute_input":"2021-08-09T12:13:16.597946Z","iopub.status.idle":"2021-08-09T12:13:17.20524Z","shell.execute_reply.started":"2021-08-09T12:13:16.597903Z","shell.execute_reply":"2021-08-09T12:13:17.203976Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\nplt.figure(figsize=(8,8))\nplt.scatter(y_train, pred)\nplt.xlabel('y_true')\nplt.ylabel('pred')\nplt.show()","metadata":{"execution":{"iopub.status.busy":"2021-08-09T12:13:17.206831Z","iopub.execute_input":"2021-08-09T12:13:17.207146Z","iopub.status.idle":"2021-08-09T12:17:45.740452Z","shell.execute_reply.started":"2021-08-09T12:13:17.207092Z","shell.execute_reply":"2021-08-09T12:17:45.739628Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import matplotlib.pyplot as plt\n\ndef plot_feature_importance(model):\n    importance_df = pd.DataFrame(model.feature_importance(),\n                                 index=X_train.columns,\n                                 columns=['importance']).sort_values('importance')\n    fig, ax = plt.subplots(figsize=(6, 6))\n    importance_df.plot.barh(ax=ax)\n    plt.show()","metadata":{"execution":{"iopub.status.busy":"2021-08-09T12:17:45.742133Z","iopub.execute_input":"2021-08-09T12:17:45.742421Z","iopub.status.idle":"2021-08-09T12:17:45.749381Z","shell.execute_reply.started":"2021-08-09T12:17:45.742366Z","shell.execute_reply":"2021-08-09T12:17:45.748504Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_feature_importance(model_half_1)","metadata":{"execution":{"iopub.status.busy":"2021-08-09T12:17:45.750905Z","iopub.execute_input":"2021-08-09T12:17:45.751228Z","iopub.status.idle":"2021-08-09T12:17:46.015096Z","shell.execute_reply.started":"2021-08-09T12:17:45.751174Z","shell.execute_reply":"2021-08-09T12:17:46.01388Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"plot_feature_importance(model_half_2)","metadata":{"execution":{"iopub.status.busy":"2021-08-09T12:17:46.017084Z","iopub.execute_input":"2021-08-09T12:17:46.017387Z","iopub.status.idle":"2021-08-09T12:17:46.263784Z","shell.execute_reply.started":"2021-08-09T12:17:46.017335Z","shell.execute_reply":"2021-08-09T12:17:46.262799Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Save Model","metadata":{}},{"cell_type":"code","source":"# save model to file\npickle.dump(model_half_1, open(\"model_half_1.pkl\", \"wb\"))\npickle.dump(model_half_2, open(\"model_half_2.pkl\", \"wb\"))\npickle.dump(pred, open(\"pred_L1.pkl\", \"wb\"))","metadata":{"execution":{"iopub.status.busy":"2021-08-09T12:17:46.265265Z","iopub.execute_input":"2021-08-09T12:17:46.26555Z","iopub.status.idle":"2021-08-09T12:17:46.885539Z","shell.execute_reply.started":"2021-08-09T12:17:46.265498Z","shell.execute_reply":"2021-08-09T12:17:46.884303Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del X_train, X_half_1, X_half_2, y_half_1, y_half_2, d_half_1, d_half_2, \ndel watchlist_1, watchlist_2, pred, pred1, pred2\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2021-08-09T12:17:46.889003Z","iopub.execute_input":"2021-08-09T12:17:46.889443Z","iopub.status.idle":"2021-08-09T12:17:47.180816Z","shell.execute_reply.started":"2021-08-09T12:17:46.889364Z","shell.execute_reply":"2021-08-09T12:17:47.17953Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Preparing test data","metadata":{}},{"cell_type":"code","source":"weather_test = weather_df = pd.read_csv(DATA_PATH + 'weather_test.csv')","metadata":{"execution":{"iopub.status.busy":"2021-08-09T12:17:47.182706Z","iopub.execute_input":"2021-08-09T12:17:47.183049Z","iopub.status.idle":"2021-08-09T12:17:47.550017Z","shell.execute_reply.started":"2021-08-09T12:17:47.182988Z","shell.execute_reply":"2021-08-09T12:17:47.549133Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"weather_test.info()","metadata":{"execution":{"iopub.status.busy":"2021-08-09T12:17:47.551574Z","iopub.execute_input":"2021-08-09T12:17:47.552148Z","iopub.status.idle":"2021-08-09T12:17:47.602621Z","shell.execute_reply.started":"2021-08-09T12:17:47.552095Z","shell.execute_reply":"2021-08-09T12:17:47.601922Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time\n# weather_test = pd.read_feather('../input/ashrae-feather/weather_test.ft')\ndf_test = pd.read_feather('../input/ashrae-great-energy-predictor-iii-featherdataset/test.feather')\nweather_test = fill_weather_dataset(weather_test)\n\ndf_test['hour'] = df_test['timestamp'].dt.hour\ndf_test['weekday'] = df_test['timestamp'].dt.weekday\ndf_test['bm'] = df_test['building_id'].apply(lambda x: str(x)) + '_' + df_test['meter'].apply(lambda x: str(x))\ndf_test = df_test.merge(bm, right_index=True, left_on=bm_cols, how='left')\ndf_test.drop('bm', axis=1, inplace=True)\n\ndf_test = reduce_mem_usage(df_test, use_float16=True)\nweather_test = reduce_mem_usage(weather_test, use_float16=True)\n\nX_test, row_ids = prepare_data(df_test, building, weather_test, test=True)","metadata":{"execution":{"iopub.status.busy":"2021-08-09T12:17:47.604105Z","iopub.execute_input":"2021-08-09T12:17:47.604399Z","iopub.status.idle":"2021-08-09T12:17:48.209918Z","shell.execute_reply.started":"2021-08-09T12:17:47.604344Z","shell.execute_reply":"2021-08-09T12:17:48.208676Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_test.head()","metadata":{"execution":{"iopub.status.busy":"2021-08-09T12:17:48.211311Z","iopub.execute_input":"2021-08-09T12:17:48.211581Z","iopub.status.idle":"2021-08-09T12:17:48.24109Z","shell.execute_reply.started":"2021-08-09T12:17:48.211532Z","shell.execute_reply":"2021-08-09T12:17:48.239279Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del df_test, building, weather_test\ngc.collect()","metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.status.busy":"2021-08-09T12:17:48.242226Z","iopub.status.idle":"2021-08-09T12:17:48.242696Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Scoring test data","metadata":{}},{"cell_type":"code","source":"%%time\npred = np.expm1(model_half_1.predict(X_test, num_iteration=model_half_1.best_iteration)) / 2\n\ndel model_half_1\ngc.collect()\n\npred += np.expm1(model_half_2.predict(X_test, num_iteration=model_half_2.best_iteration)) / 2\n    \ndel model_half_2\ngc.collect()","metadata":{"_kg_hide-output":true,"execution":{"iopub.status.busy":"2021-08-09T12:17:48.244229Z","iopub.status.idle":"2021-08-09T12:17:48.244603Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Submission","metadata":{}},{"cell_type":"code","source":"submission = pd.DataFrame({\"row_id\": row_ids, \"meter_reading\": np.clip(pred, 0, a_max=None)})\nsubmission['meter_reading'] = submission['meter_reading'].astype('float32')\nsubmission['row_id'] = submission['row_id'].astype('int32')\nsubmission.to_csv(\"submission.csv\", index=False, chunksize=25000)","metadata":{"execution":{"iopub.status.busy":"2021-08-09T12:17:48.245578Z","iopub.status.idle":"2021-08-09T12:17:48.245988Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"submission.head()","metadata":{"execution":{"iopub.status.busy":"2021-08-09T12:17:48.246865Z","iopub.status.idle":"2021-08-09T12:17:48.247372Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(f\"submission mean: {submission['meter_reading'].mean():.4f}\")\nprint(f\"submission std: {submission['meter_reading'].std():.4f}\")\nprint(f\"submission min: {submission['meter_reading'].min():.4f}\")\nprint(f\"submission max: {submission['meter_reading'].max():.4f}\")","metadata":{"execution":{"iopub.status.busy":"2021-08-09T12:17:48.248635Z","iopub.status.idle":"2021-08-09T12:17:48.249249Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"submission[X_test['building_id'] > 105]","metadata":{"execution":{"iopub.status.busy":"2021-08-09T12:17:48.250203Z","iopub.status.idle":"2021-08-09T12:17:48.25066Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sns.distplot(np.log1p(submission['meter_reading'].values), kde=False);\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2021-08-09T12:17:48.251619Z","iopub.status.idle":"2021-08-09T12:17:48.251991Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}