{"cells":[{"metadata":{},"cell_type":"markdown","source":"# Introduction\n\nThe aim of this notebook is to analyse what happen with gaps.\n\nA gap can occur if:\n\n- The meter is off.\n- Communication error with meter.\n- The software that collects data (or part of it) is hung.\n- The server is off."},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport plotly.express as px\nfrom IPython.core.display import display, HTML\nimport pickle\nfrom datetime import timedelta, date\nfrom tqdm.notebook import tqdm","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Import data"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"data_path = '../input/ashrae-data-wrangling-csv-to-pickle/'\nwith open(data_path + 'X_train.pickle', 'rb') as f:\n    X_train = pickle.load(f)\nwith open(data_path + 'X_test.pickle', 'rb') as f:\n    X_test = pickle.load(f)\nwith open(data_path + 'nan.pickle', 'rb') as f:\n    nan = pickle.load(f)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Insights"},{"metadata":{},"cell_type":"markdown","source":"## # of gaps"},{"metadata":{"_kg_hide-input":true,"trusted":true,"_kg_hide-output":true},"cell_type":"code","source":"X_train_gap = X_train[['building_id', 'meter', 'meter_reading']]\ngap = X_train_gap.groupby(['building_id', 'meter']).agg(['count'])\ngap['meter_reading', 'gap'] = 24*366 - gap['meter_reading']['count']\ngap.columns = gap.columns.droplevel(0)\ngap.rename_axis(None, axis=1)\ngap.sort_values(by=['gap'], ascending=False, inplace=True)\ngap.reset_index(inplace=True)\ngap.to_csv('train_gap.csv', index = False)\ngap.head()","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"n_samples = X_train.shape[0]\nn_samples_gap = gap['gap'].sum()\ndisplay(HTML(f'''In train set, there are {n_samples_gap:,} gaps for a total of {(n_samples + n_samples_gap):,} samples.<br>\nThe ratio of gaps is: {n_samples_gap / (n_samples + n_samples_gap):.2%}.'''))","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"X_test_gap = X_test[['building_id', 'meter', 'row_id']]\ntest_gap = X_test_gap.groupby(['building_id', 'meter']).agg(['count'])\ntest_gap['row_id', 'gap'] = 24*365*2 - test_gap['row_id']['count']\ntest_gap.columns = test_gap.columns.droplevel(0)\ntest_gap.rename_axis(None, axis=1)\ntest_gap.sort_values(by=['gap'], ascending=False, inplace=True)\ntest_gap.reset_index(inplace=True)\ntest_gap.to_csv('test_gap.csv', index = False)\ntest_gap.sample(5)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"n_samples = X_test.shape[0]\nn_samples_gap = test_gap['gap'].sum()\ndisplay(HTML(f'''In test set, there are {n_samples_gap:,} gaps for a total of {n_samples:,} samples.<br>\nThe ratio of gaps is: {n_samples_gap/n_samples:.2%}.'''))","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"theoretical_maximum_samples_in_test_set = 2380*24*365*2\ndisplay(HTML(f'''Theoretical maximum of samples in test set: {theoretical_maximum_samples_in_test_set:,} samples<br>\n2380 timesets × 24 hours × 365 days × 2 years'''))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The test set has no gaps,\nas stated in data description:\n> Gaps in the test set are not revealed or scored."},{"metadata":{},"cell_type":"markdown","source":"## # of gaps per building and energy aspect"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"n_timeseries = gap.shape[0]\nn_timeseries_gap = gap[gap['gap']==0].shape[0]\ndisplay(HTML(f'''There are {n_timeseries_gap} timeseries without any gap\nfor a total of {n_timeseries} timeseries.<br>\nThe ratio of timeseries without any gap is: {n_timeseries_gap/n_timeseries:.2%}.'''))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"nan_building_aspect = nan.groupby(['building_id', 'meter']).agg(['sum'])\nnan_building_aspect.columns = nan_building_aspect.columns.droplevel(1)\nnan_building_aspect.rename(columns={\"isNaN\": \"count_NaN\"}, inplace=True)\nnan_building_aspect.sort_values(by=['count_NaN'], ascending=False, inplace=True)\nnan_building_aspect.reset_index(inplace=True)\ndisplay(HTML(f'''Top 5 timeseries with more NaNs:'''))\nnan_building_aspect.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"nan_building_aspect_graph = nan_building_aspect[nan_building_aspect['count_NaN']>0].copy()\nnan_building_aspect_graph['building_id-meter'] = nan_building_aspect_graph['building_id'].astype('str') + '-' + nan_building_aspect_graph['meter'].astype('str')\nfig = px.bar(nan_building_aspect_graph,\n             x='building_id-meter',\n             y='count_NaN')\nfig.update_layout(xaxis={'type': 'category'})\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"n_samples = nan_building_aspect_graph['count_NaN'].sum()\ndisplay(HTML(f'''There are {n_samples} samples with NaN values.\nThis value matches with the missing values count.'''))","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"fig = px.bar(nan_building_aspect_graph,\n             x='building_id-meter',\n             y='count_NaN',\n             color='meter')\nfig.update_layout(xaxis={'type': 'category'})\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"fig = px.histogram(nan_building_aspect_graph,\n                   x='count_NaN',\n                   facet_row='meter',\n                   histnorm='percent',\n                   range_x=[0,2000],\n                   nbins=800)\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## # of gaps per building"},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"gap_building = gap.groupby(['building_id']).agg(['sum'])\ngap_building.sample(5)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"n_buildings = gap_building.shape[0]\nn_buildings_gap = gap_building[gap_building['gap']['sum']==0].shape[0]\ndisplay(HTML(f'''There are {n_buildings_gap} buildings without any gap\nfor a total of {n_buildings} buildings.<br>\nThe ratio of buildings without any gap is: {n_buildings_gap/n_buildings:.2%}.'''))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"nan_building = nan_building_aspect.groupby(['building_id']).agg(['sum'])\nnan_building.columns = nan_building.columns.droplevel(1)\nnan_building.sort_values(by=['count_NaN'], ascending=False, inplace=True)\nnan_building.reset_index(inplace=True)\nnan_building.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"nan_building_graph = nan_building[nan_building['count_NaN']>0].copy()\nfig = px.bar(nan_building_graph,\n             x='building_id',\n             y='count_NaN')\nfig.update_layout(xaxis={'type': 'category'})\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"n_buildings = nan_building[nan_building['count_NaN']==0]['building_id'].count()\ndisplay(HTML(f'''There are {n_buildings} buildings without any gap.\nThis value matches with the previous approximation.'''))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Same number of NaNs in timeseries\n"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"z = nan_building_aspect['count_NaN'].value_counts().iloc[0:10]\n\ndisplay(HTML(f'''Repeated number of gaps:<br><br>\n<pre><code>{z}</code></pre>'''))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"There are a lot of timeseries with 1, 2 or 3 gaps.\nLet's check what happens looking at what timestamps they occur."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"def get_timestamps(df):\n    ts = pd.DataFrame(columns=['building_id', 'meter', 'timestamp'])\n    for index, row in df.iterrows():\n        timestamps = nan[(nan['building_id']==row['building_id']) &\\\n                         (nan['meter']==row['meter']) &\\\n                         (nan['isNaN']==True)]['timestamp']\n        for timestamp in timestamps:\n            ts = ts.append({'building_id': row['building_id'],\n                             'meter': row['meter'],\n                             'timestamp': timestamp}, ignore_index=True)\n    return ts","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"one_nan = get_timestamps(nan_building_aspect[nan_building_aspect['count_NaN']==1])\none_nan_value_counts = one_nan['timestamp'].value_counts()\none_nan_value_counts.name = 'n_timestamps'\ndisplay(HTML(f'''Most repeated timestamps for timeseries which contains only one NaN:<br>\n{pd.DataFrame(one_nan_value_counts[one_nan_value_counts > 10]).to_html()}'''))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"There are 47 timeseries that lacks one sample at `2016-03-13 02:00:00`.\nThis matches with the Daylight Saving Time starting date of 2016 in USA.\nIn Europe, the Daylight Saving Time starting date was Sunday, 27 March."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"two_nans = get_timestamps(nan_building_aspect[nan_building_aspect['count_NaN']==2])\ntwo_nans_value_counts = two_nans['timestamp'].value_counts()\ntwo_nans_value_counts.name = 'n_timestamps'\ndisplay(HTML(f'''Most repeated timestamps for timeseries which contains exactly two NaNs:<br>\n{pd.DataFrame(two_nans_value_counts[two_nans_value_counts > 10]).to_html()}'''))","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"three_nans = get_timestamps(nan_building_aspect[nan_building_aspect['count_NaN']==3])\nthree_nans_value_counts = three_nans['timestamp'].value_counts()\nthree_nans_value_counts.name = 'n_timestamps'\ndisplay(HTML(f'''Most repeated timestamps for timeseries which contains exactly three NaNs:<br>\n{pd.DataFrame(three_nans_value_counts[three_nans_value_counts > 10]).to_html()}'''))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"It's curious that some buildings have the very same specific number of NaNs:\n\n- 1312\n- 1452"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"cases = [1312, 1452]\n\ndef nans_aspect(df, nans):\n    za = df[df['count_NaN'] == nans][['meter', 'building_id']].groupby('meter').count()\n    za.columns = [nans]\n    display(za)\n\nfor case in cases:\n    nans_aspect(nan_building_aspect, case)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"There are 52 buildings that have exactly 1312 NaNs in electricity,\nand 44 buildings the same number of NaNs in steam.\n\nThere are 40 buildings that have exactly 1452 NaNs in electricity,\nand 1 building the same number of NaNs in hotwater."},{"metadata":{},"cell_type":"markdown","source":"## # of NaNs energy aspect"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"X_train_aspect = X_train[['meter', 'meter_reading']].groupby(['meter']).agg(['count'])\nX_train_aspect.columns = X_train_aspect.columns.droplevel(0)\nX_train_aspect\nnan_aspect = nan_building_aspect.groupby(['meter']).agg(['sum'])\nnan_aspect.columns = nan_aspect.columns.droplevel(1)\nnan_aspect['count_NaN'] = nan_aspect['count_NaN'].astype(int)\nnan_aspect = pd.merge(X_train_aspect, nan_aspect, on='meter')\nnan_aspect.reset_index(inplace=True)\nnan_aspect['percentage'] = round(nan_aspect['count_NaN'] / nan_aspect['count'] * 100, 2).astype('str') + ' %'\nnan_aspect","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Hotwater is the energy aspect with less ratio of NaNs."},{"metadata":{},"cell_type":"markdown","source":"## NaNs by hour and energy aspect"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"nan_hour =  nan[['timestamp', 'meter', 'isNaN']]\nnan_hour['hour'] = nan_hour['timestamp'].dt.hour\nnan_hour = nan_hour.groupby(['hour', 'meter']).agg([np.count_nonzero])\nnan_hour.drop('timestamp', axis='columns', inplace=True)\nnan_hour.columns = nan_hour.columns.droplevel(0)\nnan_hour.rename(columns={'count_nonzero': 'count_NaN'}, inplace=True)\nnan_hour.reset_index(inplace=True)\nnan_hour.pivot(index='meter', columns='hour', values='count_NaN')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"fig = px.bar(nan_hour,\n             x='hour',\n             y='count_NaN',\n             facet_row='meter')\nfig.update_layout(xaxis={'type': 'category'})\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"There is no remarkable difference between the hours."},{"metadata":{},"cell_type":"markdown","source":"## NaNs by dayofweek and energy aspect"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"nan_dayofweek =  nan[['timestamp', 'meter', 'isNaN']]\nnan_dayofweek['dayofweek'] = nan_dayofweek['timestamp'].dt.dayofweek\nnan_dayofweek = nan_dayofweek.groupby(['dayofweek', 'meter']).agg(['count', np.count_nonzero])\nnan_dayofweek.drop('timestamp', axis='columns', inplace=True)\nnan_dayofweek.columns = nan_dayofweek.columns.droplevel(0)\nnan_dayofweek.rename(columns={'count_nonzero': 'count_NaN'}, inplace=True)\nnan_dayofweek.reset_index(inplace=True)\nnan_dayofweek.pivot(index='meter', columns='dayofweek', values='count_NaN')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"fig = px.bar(nan_dayofweek,\n             x='dayofweek',\n             y='count_NaN',\n             facet_row='meter')\nfig.update_layout(xaxis={'type': 'category'})\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## NaNs by month and energy aspect\n"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"nan_month =  nan[['timestamp', 'meter', 'isNaN']]\nnan_month['month'] = nan_month['timestamp'].dt.month\nnan_month = nan_month.groupby(['month', 'meter']).agg(['count', np.count_nonzero])\nnan_month.drop('timestamp', axis='columns', inplace=True)\nnan_month.columns = nan_month.columns.droplevel(0)\nnan_month.rename(columns={'count_nonzero': 'count_NaN'}, inplace=True)\nnan_month.reset_index(inplace=True)\nnan_month.pivot(index='meter', columns='month', values='count_NaN')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"fig = px.bar(nan_month,\n             x='month',\n             y='count_NaN',\n             facet_row='meter')\nfig.update_layout(xaxis={'type': 'category'})\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Footprints"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"nan_dayofyear =  nan[['timestamp', 'building_id', 'meter', 'isNaN']]\nnan_dayofyear['dayofyear'] = nan_dayofyear['timestamp'].dt.dayofyear\nnan_dayofyear = nan_dayofyear.groupby(['dayofyear', 'building_id', 'meter']).agg(['count', np.count_nonzero])\nnan_dayofyear.drop('timestamp', axis='columns', inplace=True)\nnan_dayofyear.columns = nan_dayofyear.columns.droplevel(0)\nnan_dayofyear.rename(columns={'count_nonzero': 'count_NaN'}, inplace=True)\nnan_dayofyear.reset_index(inplace=True)\nnan_dayofyear.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Electricity"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"fig = px.density_heatmap(nan_dayofyear[nan_dayofyear['meter'] == 'electricity'],\n                         x='dayofyear',\n                         y='building_id',\n                         z='count_NaN',\n                         histfunc='sum',\n                         nbinsx=366,\n                         nbinsy=1449,\n                         height=1600)\nfig.update_layout(xaxis={'type': 'category',\n                         'tickformat': '%d %B %Y'})\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Chilled water"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"fig = px.density_heatmap(nan_dayofyear[nan_dayofyear['meter'] == 'chilledwater'],\n                         x='dayofyear',\n                         y='building_id',\n                         z='count_NaN',\n                         histfunc='sum',\n                         nbinsx=366,\n                         nbinsy=1449,\n                         height=1600)\nfig.update_layout(xaxis={'type': 'category',\n                         'tickformat': '%d %B %Y'})\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Steam"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"fig = px.density_heatmap(nan_dayofyear[nan_dayofyear['meter'] == 'steam'],\n                         x='dayofyear',\n                         y='building_id',\n                         z='count_NaN',\n                         histfunc='sum',\n                         nbinsx=366,\n                         nbinsy=1449,\n                         height=1600)\nfig.update_layout(xaxis={'type': 'category',\n                         'tickformat': '%d %B %Y'})\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Hot water"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"fig = px.density_heatmap(nan_dayofyear[nan_dayofyear['meter'] == 'hotwater'],\n                         x='dayofyear',\n                         y='building_id',\n                         z='count_NaN',\n                         histfunc='sum',\n                         nbinsx=366,\n                         nbinsy=1449,\n                         height=1600)\nfig.update_layout(xaxis={'type': 'category',\n                         'tickformat': '%d %B %Y'})\nfig.show()","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}