{"cells":[{"metadata":{},"cell_type":"markdown","source":"![Copycat Ken](https://i.ytimg.com/vi/ByH13tdosnM/maxresdefault.jpg)"},{"metadata":{},"cell_type":"markdown","source":"Why is the inclusion of an introductory header image a 'soft requirement' for kernels these days, lol? Guess the anime."},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport gc\n\ntr = pd.read_csv('/kaggle/input/ashrae-energy-prediction/train.csv')\nte = pd.read_csv('/kaggle/input/ashrae-energy-prediction/test.csv')\n\na = set(tr.building_id)\nb = set(te.building_id)\nlen(a-b), len(b-a)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"> Cool, each test building exists in train."},{"metadata":{"trusted":true},"cell_type":"code","source":"del a,b; gc.collect()\ntr.timestamp.min(), tr.timestamp.max()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"If we drop year, we don't have to worry about collisions... since we only have a max of 1 unique reading per hour per meter per building."},{"metadata":{"trusted":true},"cell_type":"code","source":"tr.timestamp = tr.timestamp.map(lambda x: x[5:])\nte.timestamp = te.timestamp.map(lambda x: x[5:])\nte = te.merge(\n    tr[['building_id','meter','timestamp','meter_reading']],\n    how='left',\n    on=['building_id','meter','timestamp']\n)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del tr; gc.collect()\n(te.meter_reading.isna().sum() / te.shape[0] * 100).astype(np.int)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Small amount of nans we need to handle..."},{"metadata":{"trusted":true},"cell_type":"code","source":"fillna = te.groupby(['building_id', 'meter']).meter_reading.mean().reset_index()\nfillna.rename(columns={'meter_reading':'missing'}, inplace=True)\nte = te.merge(fillna, how='left', on=['building_id', 'meter'])\n\nmask = te.meter_reading.isna()\nte.loc[mask, 'meter_reading'] = te[mask].missing\n(te.meter_reading.isna().sum() / te.shape[0] * 100).astype(np.int)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"te[['row_id', 'meter_reading']].to_csv('./submission.csv', index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"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":4,"nbformat_minor":1}