{"cells":[{"metadata":{},"cell_type":"markdown","source":"# All Leak Dataset\n\nAs you already know there are huge data leak in this competition. Until now site-0, site-1, site-2, site-4 and site-15 building meter reading data\nwas discovered by great kagglers.\n\nThis Kernel collects all leak data revealed by following great kernels:\n\n* [ASHRAE - UCF Spider and EDA (Full Test Labels)](https://www.kaggle.com/gunesevitan/ashrae-ucf-spider-and-eda-full-test-labels) v3\n* [UCL: Data Leakage (Episode 2)](https://www.kaggle.com/mpware/ucl-data-leakage-episode-2) v1\n* [ASU train and scraped test data](https://www.kaggle.com/pdnartreb/scrap-asu-data) v7\n* [UCB: Data Leakage (Site 4)](https://www.kaggle.com/serengil/ucb-data-leakage-site-4/output) v15\n* [ASHRAE-site15-cornell](https://www.kaggle.com/pp2file/ashrae-site15-cornell) v3\n\nThank you @gunesevitan, @mpware, @poedator, @pdnartreb, @serengil and @pp2file . You are Great Kagglers!!"},{"metadata":{"trusted":true},"cell_type":"code","source":"import pandas as pd\nfrom pathlib import Path\nfrom matplotlib import pyplot as plt","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import os\nos.listdir('../input/')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df = pd.read_csv('../input/ashrae-energy-prediction/train.csv')\ntrain_df['timestamp'] = pd.to_datetime(train_df['timestamp'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df.meter_reading.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def plot_meter(train, leak, start=0, n=100, bn=10):\n    for bid in leak.building_id.unique()[:bn]:    \n        tr = train[train.building_id == bid]\n        lk = leak[leak.building_id == bid]\n        \n        for m in lk.meter.unique():\n            plt.figure(figsize=[10,2])\n            trm = tr[tr.meter == m]\n            lkm = lk[lk.meter == m]\n            \n            plt.plot(trm.timestamp[start:start+n], trm.meter_reading.values[start:start+n], label='train')    \n            plt.plot(lkm.timestamp[start:start+n], lkm.meter_reading.values[start:start+n], '--', label='leak')\n            plt.title('bid:{}, meter:{}'.format(bid, m))\n            plt.legend()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# site 0"},{"metadata":{"trusted":true},"cell_type":"code","source":"# load site 0 data\nucf_root = Path('../input/ashrae-ucf-spider-and-eda-full-test-labels')\nleak0_df = pd.read_pickle(ucf_root/'site0.pkl') \nleak0_df['meter_reading'] = leak0_df.meter_reading_scraped\nleak0_df.drop(['meter_reading_original','meter_reading_scraped'], axis=1, inplace=True)\nprint( 'null=', leak0_df.meter_reading.isnull().sum())\nleak0_df.dropna(inplace=True)\nprint( 'null=', leak0_df.meter_reading.isnull().sum())\n#leak0_df.fillna(0, inplace=True)\nleak0_df.loc[leak0_df.meter_reading < 0, 'meter_reading'] = 0\nprint(len(leak0_df))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"leak0_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"leak0_df.tail()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_meter(train_df, leak0_df, start=5000)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# site 1"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"# load site 1 data\nucl_root = Path('../usr/lib/ucl_data_leakage_episode_2')\nleak1_df = pd.read_pickle(ucl_root/'site1.pkl') \nleak1_df['meter_reading'] = leak1_df.meter_reading_scraped\nleak1_df.drop(['meter_reading_scraped'], axis=1, inplace=True)\n\nprint( 'null=', leak1_df.meter_reading.isnull().sum())\nleak1_df.dropna(inplace=True)\nprint( 'null=', leak1_df.meter_reading.isnull().sum())\n\nleak1_df.loc[leak1_df.meter_reading < 0, 'meter_reading'] = 0\nprint(len(leak1_df))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"leak1_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"leak1_df.tail()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_meter(train_df, leak1_df, start=0)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":" # site 2"},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"# load site 2 data\nleak2_df = pd.read_csv('/kaggle/input/asu-buildings-energy-consumption/asu_2016-2018.csv')\nleak2_df['timestamp'] = pd.to_datetime(leak2_df['timestamp'])\n\n\nprint( 'null=', leak2_df.meter_reading.isnull().sum())\nleak2_df.dropna(inplace=True)\nprint( 'null=', leak2_df.meter_reading.isnull().sum())\n\n#leak2_df.fillna(0, inplace=True)\nleak2_df.loc[leak2_df.meter_reading < 0, 'meter_reading'] = 0\n\nleak2_df = leak2_df[leak2_df.building_id!=245] # building 245 is missing now.\n\n#leak2_df = leak2_df[leak2_df.timestamp.dt.year > 2016]\nprint(len(leak2_df))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"leak2_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"leak2_df.tail()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_meter(train_df, leak2_df, start=0)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# site 4"},{"metadata":{"trusted":true},"cell_type":"code","source":"# load site 4 data\n# its looks better to use threshold ...\nleak4_df = pd.read_csv('../input/ucb-data-leakage-site-4/site4.csv')\n\nleak4_df['timestamp'] = pd.to_datetime(leak4_df['timestamp'])\nleak4_df.rename(columns={'meter_reading_scraped': 'meter_reading'}, inplace=True)\n#leak4_df.fillna(0, inplace=True)\n\nprint( 'null=', leak4_df.meter_reading.isnull().sum())\nleak4_df.dropna(inplace=True)\nprint( 'null=', leak4_df.meter_reading.isnull().sum())\n\nleak4_df.loc[leak4_df.meter_reading < 0, 'meter_reading'] = 0\nleak4_df['meter'] = 0\n\nprint('before remove dupilicate', leak4_df.duplicated(subset=['building_id','timestamp']).sum())\nleak4_df.drop_duplicates(subset=['building_id','timestamp'],inplace=True)\nprint('after remove dupilicate', leak4_df.duplicated(subset=['building_id','timestamp']).sum())\nprint(len(leak4_df))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"leak4_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"leak4_df.tail() # its include 2019. i will delete them later","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"len(leak4_df.building_id.unique())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plot_meter(train_df, leak4_df, start=0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df[train_df.building_id == 621].timestamp.min() # some train data is missing","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# site 15"},{"metadata":{"trusted":true},"cell_type":"code","source":"# this data does not include 2016.\nleak15_df = pd.read_csv('../input/ashrae-site15-cornell/site15_leakage.csv')\n\nleak15_df['timestamp'] = pd.to_datetime(leak15_df['timestamp'])\n#leak15_df.fillna(0, inplace=True)\n\nprint( 'null=', leak15_df.meter_reading.isnull().sum())\nleak15_df.dropna(inplace=True)\nprint( 'null=', leak15_df.meter_reading.isnull().sum())\n\nleak15_df.loc[leak15_df.meter_reading < 0, 'meter_reading'] = 0\n\nprint(leak15_df.duplicated().sum())\nprint(len(leak15_df))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"leak15_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"leak15_df.tail()","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"","_uuid":"","trusted":true},"cell_type":"code","source":"df = pd.concat([leak0_df, leak1_df, leak2_df, leak4_df, leak15_df])\ndf.drop('score', axis=1, inplace=True)\ndf = df[(df.timestamp.dt.year >= 2016) & (df.timestamp.dt.year < 2019)]\ndf.reset_index(inplace=True, drop=True)\nprint(len(df))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df.timestamp.min(), df.timestamp.max() ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df.to_feather('leak_null.feather')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"leak_df = pd.read_feather('leak_null.feather')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"leak_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"leak_df.meter.value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"len(leak_df.building_id.unique())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Wow!! What do you think ? it's really huge now !! \nlen(leak_df) / len(train_df)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"data len of building meter"},{"metadata":{"trusted":true},"cell_type":"code","source":"building_meta_df = pd.read_csv('../input/ashrae-energy-prediction/building_metadata.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"leak_df = leak_df.merge(building_meta_df[['building_id', 'site_id']], on='building_id', how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"leak_df2 = leak_df[(leak_df.timestamp.dt.year > 2016) & (leak_df.timestamp.dt.year < 2019)]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"bmg = leak_df2.groupby(['building_id', 'meter', 'site_id']).meter.count()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"bmg.hist(bins=100)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"bmg.sort_values()[:20]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"len(leak_df[(leak_df.building_id == 1443) & (leak_df.meter == 0)])","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}