{"cells":[{"metadata":{},"cell_type":"markdown","source":"**Warning !**\nThis is just a test of leaked data. \n\nSince @sohier said:\n> We will ensure that any meter readings that can be publicly downloaded are not included in the private set.\n\nhttps://www.kaggle.com/c/ashrae-energy-prediction/discussion/117357\n\nThere is nothing about the private LB. You can replce the 'submission_base' with your own one. \nIf everyone use this snippet, **maybe it can make public LB some useful.**"},{"metadata":{"_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","trusted":true},"cell_type":"code","source":"import warnings\nwarnings.filterwarnings('ignore')\nimport os\nimport gc\nimport pickle\nimport numpy as np\nimport pandas as pd\nimport random as rn\nimport matplotlib.pyplot as plt\n\nfrom tqdm import tqdm\nfrom datetime import datetime, timedelta","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test = pd.read_csv('../input/ashrae-energy-prediction/test.csv', index_col=0, parse_dates = ['timestamp'])\nbuilding = pd.read_csv('../input/ashrae-energy-prediction/building_metadata.csv', usecols=['site_id', 'building_id'])","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"test = test.merge(building, left_on = \"building_id\", right_on = \"building_id\", how = \"left\")\nt = test[['building_id', 'meter', 'timestamp']]\nt['row_id'] = t.index","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"I used a public kernel from [@rohanrao](https://www.kaggle.com/rohanrao) as example.\n\nhttps://www.kaggle.com/rohanrao/ashrae-half-and-half\n\n**Replace this part with yours.**"},{"metadata":{"trusted":true},"cell_type":"code","source":"submission_base = pd.read_csv('../input/ashrae-half-and-half/submission.csv', index_col=0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"submission = submission_base.copy()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"* Site 0\n\nThanks to [@yamsam](https://www.kaggle.com/yamsam/) and his great kernel.\n\nhttps://www.kaggle.com/yamsam/new-ucf-starter-kernel"},{"metadata":{"trusted":true},"cell_type":"code","source":"site_0 = pd.read_csv('../input/new-ucf-starter-kernel/submission_ucf_replaced.csv', index_col=0)\nsubmission.loc[test[test['site_id']==0].index, 'meter_reading'] = site_0['meter_reading']\ndel site_0\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"* Site 1\n\nThanks to [@mpware](https://www.kaggle.com/mpware) and his great kernel.\n\nhttps://www.kaggle.com/mpware/ucl-data-leakage-episode-2\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"with open('../usr/lib/ucl_data_leakage_episode_2/site1.pkl', 'rb') as f:\n    site_1 = pickle.load(f)\nsite_1 = site_1[site_1['timestamp'].dt.year > 2016]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"site_1 = site_1.merge(t, left_on = ['building_id', 'meter', 'timestamp'], right_on = ['building_id', 'meter', 'timestamp'], how = \"left\")\nsite_1 = site_1[['meter_reading_scraped', 'row_id']].set_index('row_id').dropna()\nsubmission.loc[site_1.index, 'meter_reading'] = site_1['meter_reading_scraped']\ndel site_1\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"* Site 2\n\nThanks to [@pdnartreb](https://www.kaggle.com/pdnartreb) and his great dataset.\n\nhttps://www.kaggle.com/pdnartreb/asu-buildings-energy-consumption"},{"metadata":{"trusted":true},"cell_type":"code","source":"site_2 = pd.read_csv('../input/asu-buildings-energy-consumption/asu_2016-2018.csv', parse_dates = ['timestamp'])\nsite_2 = site_2[site_2['timestamp'].dt.year > 2016]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"site_2 = site_2.merge(t, left_on = ['building_id', 'meter', 'timestamp'], right_on = ['building_id', 'meter', 'timestamp'], how = \"left\")\nsite_2 = site_2[['meter_reading', 'row_id']].set_index('row_id').dropna()\nsubmission.loc[site_2.index, 'meter_reading'] = site_2['meter_reading']\ndel site_2\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"* Site 4\n\nThanks to [@serengil](https://www.kaggle.com/serengil) and his great kernel.\n\nhttps://www.kaggle.com/serengil/ucb-data-leakage-site-4"},{"metadata":{"trusted":true},"cell_type":"code","source":"site_4 = pd.read_csv('../input/ucb-data-leakage-site-4/site4.csv', parse_dates = ['timestamp'])\nsite_4.columns = ['building_id', 'timestamp', 'meter_reading']\nsite_4['meter'] = 0\nsite_4['timestamp'] = pd.DatetimeIndex(site_4['timestamp']) + timedelta(hours=-8)\nsite_4 = site_4[site_4['timestamp'].dt.year > 2016]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"site_4 = site_4.merge(t, left_on = ['building_id', 'meter', 'timestamp'], right_on = ['building_id', 'meter', 'timestamp'], how = \"left\")\nsite_4 = site_4[['meter_reading', 'row_id']].dropna().set_index('row_id')\nsubmission.loc[site_4.index, 'meter_reading'] = site_4['meter_reading']\ndel site_4\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"submission.to_csv('submission.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"submission.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.6.6"}},"nbformat":4,"nbformat_minor":1}