{"cells":[{"metadata":{},"cell_type":"markdown","source":"# Introduction\n\nThe aim of this notebook is to analyse what happen with zeros.\n\nA zero can occur if there is:\n\n- No consumption. (Yes, in fact it is a true zero value)\n- Meter error.\n- Comunication error with meter.\n- Error from the software that collects data. That is how it interprets a bad value or the absence of data.\n\nWe shall differenciate electricity from the others energy aspects: chilled water, steam, hot water.\n\nIn a building is very rare that the electricity consumption is zero at any moment.\nA building always has electricity consumption.\nAs it has a lot of permanently connected devices.\n\nThe other energy aspects can be zero if nobody is using them.\nIt can occur at certain hours of days (like weekends, bank holidays or periods when the building is closed)."},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true,"_kg_hide-input":true,"_kg_hide-output":false},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport plotly.express as px\nfrom IPython.core.display import display, HTML\nfrom ashrae_utils import reduce_mem_usage","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Prepare data"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"data_path = '../input/ashrae-energy-prediction/'","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## train"},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"X_train = pd.read_csv(data_path + 'train.csv', engine='python')\nX_train['building_id'] = pd.Categorical(X_train['building_id'])\nX_train['timestamp'] = pd.to_datetime(X_train['timestamp'], format='%Y-%m-%d %H:%M:%S')\nX_train['meter'] = pd.Categorical(X_train['meter']).rename_categories({0: 'electricity', 1: 'chilledwater', 2: 'steam', 3: 'hotwater'})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":false},"cell_type":"code","source":"X_train.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Insights"},{"metadata":{},"cell_type":"markdown","source":"## # of zeros"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"n_samples = X_train.shape[0]\nn_samples_zero = X_train[X_train['meter_reading']==0.0].shape[0]\ndisplay(HTML(f'''There are {n_samples_zero:,} zeros for a total of {n_samples:,} samples.<br>\nThe ratio of zeros is: {n_samples_zero/n_samples:.2%}.'''))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## # of zeros per building"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"X_train_building =  X_train[['building_id', 'meter_reading']]\nzeros_building = X_train_building.groupby(['building_id']).agg(['count', np.count_nonzero])\nzeros_building['meter_reading', 'count_nonzero'] = zeros_building['meter_reading', 'count_nonzero'].astype(int)\nzeros_building['meter_reading', 'count_zero'] = zeros_building['meter_reading']['count'] - zeros_building['meter_reading']['count_nonzero']\nzeros_building.columns = zeros_building.columns.droplevel(0)\nzeros_building.rename_axis(None, axis=1)\nzeros_building.sort_values(by=['count_zero'], ascending=False, inplace=True)\nzeros_building.reset_index(inplace=True)\nzeros_building.to_csv('zeros_building.csv', index = False)\nzeros_building.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"n_buildings = zeros_building.shape[0]\nn_buildings_nonzero = zeros_building[zeros_building['count_zero'] == 0].shape[0]\ndisplay(HTML(f'''There are {n_buildings_nonzero} buildings without any zero value\nfor a total of {n_buildings} buildings.<br>\nThe ratio of buildings without any zero value is: {n_buildings_nonzero/n_buildings:.2%}.'''))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"fig = px.bar(zeros_building[zeros_building['count_zero'] > 0],\n             x='building_id',\n             y='count_zero')\nfig.update_layout(xaxis={'type': 'category'})\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Something happens that quite a few buildings has the same number of zeros."},{"metadata":{},"cell_type":"markdown","source":"## # of zeros per building and energy aspect"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"X_train_building_aspect =  X_train[['building_id', 'meter', 'meter_reading']]\nzeros_building_aspect = X_train_building_aspect.groupby(['building_id', 'meter']).agg(['count', np.count_nonzero])\nzeros_building_aspect['meter_reading', 'count_zero'] = zeros_building_aspect['meter_reading']['count'] - zeros_building_aspect['meter_reading']['count_nonzero']\nzeros_building_aspect.columns = zeros_building_aspect.columns.droplevel(0)\nzeros_building_aspect.rename_axis(None, axis=1)\nzeros_building_aspect.sort_values(by=['count_zero'], ascending=False, inplace=True)\nzeros_building_aspect.reset_index(inplace=True)\nzeros_building_aspect.to_csv('zeros_building_aspect.csv', index = False)\nzeros_building_aspect.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# The groupby created all combinations of building and meter, even if them doesn't exist.\n# Then we get only the existing combinations.\nzeros_building_aspect_notna = zeros_building_aspect[zeros_building_aspect['count_zero'].notna()]\nzeros_building_aspect_notzero = zeros_building_aspect_notna[zeros_building_aspect_notna['count_zero']==0]\nn_series = zeros_building_aspect_notna.shape[0]\nn_series_notzero = zeros_building_aspect_notzero.shape[0]\ndisplay(HTML(f'''There are {n_series_notzero} series without any zero value\nfor a total of {n_series} series.<br>\nThe ratio of series without any zero value is: {n_series_notzero/n_series:.2%}.'''))","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"zbann = zeros_building_aspect_notna[['meter', 'building_id']].groupby('meter').count()\nzbann.columns = ['total']\nzbanz = zeros_building_aspect_notzero[['meter', 'building_id']].groupby('meter').count()\nzbanz.columns = ['without zero values']\ntimeseries_aspect = zbanz.join(zbann)\ntimeseries_aspect['with zero values'] = timeseries_aspect['total'] - timeseries_aspect['without zero values']\ntimeseries_aspect['wozv ratio'] = round(timeseries_aspect['without zero values'] / timeseries_aspect['total'] * 100, 2).astype('str') + ' %'\ntimeseries_aspect['wzv ratio'] = round(timeseries_aspect['with zero values'] / timeseries_aspect['total'] * 100, 2).astype('str') + ' %'\ntimeseries_aspect[['without zero values', 'wozv ratio', 'with zero values', 'wzv ratio', 'total']]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The trend is that the electricity timeseries has any zero value.\nAnd the other energy aspects has some zero values.\n\nIdeally, all electricity timeseries should not have any zero.\nAnd the other energy aspects should have."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"zeros_building_aspect_zero = zeros_building_aspect_notna[zeros_building_aspect_notna['count_zero']>0].copy()\nzeros_building_aspect_zero['building_id-meter'] = zeros_building_aspect_zero['building_id'].astype('str') + '-' + zeros_building_aspect_zero['meter']\nfig = px.bar(zeros_building_aspect_zero,\n             x='building_id-meter',\n             y='count_zero')\nfig.update_layout(xaxis={'type': 'category'})\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"markdown","source":"The phenomenon of the same number of zeros in quite a few buildings is more appreciated in timelines."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"fig = px.bar(zeros_building_aspect_zero,\n             x='building_id-meter',\n             y='count_zero',\n             color='meter')\nfig.update_layout(xaxis={'type': 'category'})\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"This phenomenon seems exclusive of electricity timeseries."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"fig = px.histogram(zeros_building_aspect_zero,\n                   x='count_zero',\n                   facet_col='meter',\n                   histnorm='percent',\n                   nbins=10)\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The histogram shows the phenomenon of the same number of zeros in quite a few buildings.\nBut also that in some energy aspects there is a lack of the number buildings in some bins.\nCould this be something relevant?"},{"metadata":{},"cell_type":"markdown","source":"## Same number of zeros in timeseries"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"z = zeros_building_aspect_zero['count_zero'].value_counts().iloc[0:10]\n\ndisplay(HTML(f'''Repeated number of zeros:<br><br>\n<pre><code>{z}</code></pre>'''))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"It's normal to have some buildings that lack 1, 2, 3, 4 or a small number of samples.\n\nIt's curious that some buildings have the very same specific number of zeros:\n\n- 3377\n- 46\n- 206\n- 7\n- 218\n- 3378"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"cases = [3377, 46, 206, 7, 218, 3378]\n\ndef zeros_aspect(df, zeros):\n    za = df[df['count_zero'] == zeros][['meter', 'building_id']].groupby('meter').count()\n    za.columns = [zeros]\n    display(za)\n\nfor case in cases:\n    zeros_aspect(zeros_building_aspect_zero, case)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"And most of them, only in electricity.\n\nIn chilled water, 24 buildings match themselves with exactly 206 zero values."},{"metadata":{},"cell_type":"markdown","source":"## # of zeros energy aspect"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"X_train_aspect =  X_train[['meter', 'meter_reading']]\nzeros_aspect = X_train_aspect.groupby(['meter']).agg(['count', np.count_nonzero])\nzeros_aspect['meter_reading', 'count_nonzero'] = zeros_aspect['meter_reading', 'count_nonzero'].astype(int)\nzeros_aspect['meter_reading', 'count_zero'] = zeros_aspect['meter_reading']['count'] - zeros_aspect['meter_reading']['count_nonzero']\nzeros_aspect.columns = zeros_aspect.columns.droplevel(0)\nzeros_aspect.rename_axis(None, axis=1)\nzeros_aspect = zeros_aspect.reset_index()\nzeros_aspect['percentage'] = round(zeros_aspect['count_zero'] / zeros_aspect['count'] * 100, 2).astype('str') + ' %'\nzeros_aspect","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Electricity is the energy aspect with less ratio of zeros.\nThe other aspects lack more than 10 % of zeros."},{"metadata":{},"cell_type":"markdown","source":"## Zeros by hour and energy aspect"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"X_train_hour =  X_train[['timestamp', 'meter', 'meter_reading']]\nX_train_hour['hour'] = X_train_hour['timestamp'].dt.hour\nzeros_hour = X_train_hour.groupby(['hour', 'meter']).agg(['count', np.count_nonzero])\nzeros_hour['meter_reading', 'count_nonzero'] = zeros_hour['meter_reading', 'count_nonzero'].astype(int)\nzeros_hour['meter_reading', 'count_zero'] = zeros_hour['meter_reading']['count'] - zeros_hour['meter_reading']['count_nonzero']\nzeros_hour.columns = zeros_hour.columns.droplevel(0)\nzeros_hour.rename_axis(None, axis=1)\nzeros_hour = zeros_hour.reset_index()\nzeros_hour.head()","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"fig = px.bar(zeros_hour,\n             x='hour',\n             y='count_zero',\n             facet_row='meter')\nfig.update_layout(xaxis={'type': 'category'})\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Chilledwater number of zeros and the hour are very correlated."},{"metadata":{},"cell_type":"markdown","source":"## Zeros by dayofweek and energy aspect"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"X_train_dayofweek =  X_train[['timestamp', 'meter', 'meter_reading']]\nX_train_dayofweek['dayofweek'] = X_train_dayofweek['timestamp'].dt.dayofweek\nzeros_dayofweek = X_train_dayofweek.groupby(['dayofweek', 'meter']).agg(['count', np.count_nonzero])\nzeros_dayofweek['meter_reading', 'count_nonzero'] = zeros_dayofweek['meter_reading', 'count_nonzero'].astype(int)\nzeros_dayofweek['meter_reading', 'count_zero'] = zeros_dayofweek['meter_reading']['count'] - zeros_dayofweek['meter_reading']['count_nonzero']\nzeros_dayofweek.columns = zeros_dayofweek.columns.droplevel(0)\nzeros_dayofweek.rename_axis(None, axis=1)\nzeros_dayofweek = zeros_dayofweek.reset_index()\nzeros_dayofweek.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"fig = px.bar(zeros_dayofweek,\n             x='dayofweek',\n             y='count_zero',\n             facet_row='meter')\nfig.update_layout(xaxis={'type': 'category'})\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Chilledwater has more zeros on the weekend."},{"metadata":{},"cell_type":"markdown","source":"## Zeros by month and energy aspect"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"X_train_month = X_train[['timestamp', 'meter', 'meter_reading']]\nX_train_month['month'] = X_train_month['timestamp'].dt.month\nzeros_month = X_train_month.groupby(['month', 'meter']).agg(['count', np.count_nonzero])\nzeros_month['meter_reading', 'count_nonzero'] = zeros_month['meter_reading', 'count_nonzero'].astype(int)\nzeros_month['meter_reading', 'count_zero'] = zeros_month['meter_reading']['count'] - zeros_month['meter_reading']['count_nonzero']\nzeros_month.columns = zeros_month.columns.droplevel(0)\nzeros_month.rename_axis(None, axis=1)\nzeros_month = zeros_month.reset_index()\nzeros_month.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"fig = px.bar(zeros_month,\n             x='month',\n             y='count_zero',\n             facet_row='meter')\nfig.update_layout(xaxis={'type': 'category'})\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"There are clearly a relation of the months with the number of zeros of the energy aspects:\nchilled water, steam, hot water.\n\nAs the months and the temperature are hightly correlated,\nthen the number of zeros shall be correlated with the temperature.\n\nSomethings happens with the electricity in the five first months.\nCould this be related to the phenomenon of the same number of zeros in quite a few buildings?"},{"metadata":{},"cell_type":"markdown","source":"# Correlative zeros"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"def correlativeness(series):\n    \n    # Initialize variables\n    count = 0\n    total_zeros = 0\n    local_zeros = 0\n    clusters = []\n    \n    # Iterate series\n    for i in series:\n        # Count\n        count += 1\n        # If the element is zero, increment both total and local zeros counter\n        if i == 0:\n            total_zeros += 1\n            local_zeros += 1\n        else:\n            # If the last iteration was a zero, then append local zeros counter to clusters\n            # and reset local zeros counter\n            if local_zeros > 0:\n                clusters.append(local_zeros)\n                local_zeros = 0\n                \n    # Append local zeros counter to clusters in case it is not saved before\n    if local_zeros > 0:\n        clusters.append(local_zeros)\n        \n    # Calculate mean and standard deviation\n    if len(clusters) > 0:\n        mean = np.mean(clusters)\n        std = np.std(clusters) / mean\n    else:\n        mean = np.NaN\n        std = np.NaN\n\n    return count, total_zeros, len(clusters), mean, std, clusters","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"X_train_correlative = X_train[['building_id', 'meter', 'meter_reading']]\ncorrelative = X_train_correlative.groupby(['building_id', 'meter']).agg([correlativeness]).dropna()\ncorrelative_ext = pd.DataFrame(correlative[('meter_reading', 'correlativeness')].values.tolist(), index=correlative.index)\ncorrelative[['count', 'zeros', 'clusters', 'mean', 'std', 'detail']] = correlative_ext\ncorrelative.drop(['meter_reading'], axis='columns', inplace=True)\ncorrelative.columns = correlative.columns.droplevel(1)\ncorrelative.reset_index(inplace=True)\ncorrelative.head()","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"# Plotly has no logarithmic x axis in histogram\nimport matplotlib.pyplot as plt\nimport seaborn as sns","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"g = sns.FacetGrid(correlative, col='meter')\ng = g.set(xscale='log')\ng = g.map(plt.hist, 'zeros', bins=np.logspace(0, np.log10(24*366), num=25).astype('int'))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"In electricity some peaks detected before appears here: 46, 218 and 3377-3378.\n\nIn chilled water the 206 peak is detected here.\n\nThere is no visible distribution.\n\nIn electricity and chilledwater, some buildings has few zeros.\n\nIn chilled water, steam and hotwater there are more buildings with a lot of zeros than with few zeros."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"g = sns.FacetGrid(correlative, col='meter')\ng = g.set(xscale='log')\ng = g.map(plt.hist, 'clusters',bins=np.logspace(0, 3, num=25).astype('int'))","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"g = sns.FacetGrid(correlative, col='meter')\ng = g.set(xscale='log')\ng = g.map(plt.hist, 'mean', bins=np.logspace(0, np.log10(24*366), num=25))","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"g = sns.FacetGrid(correlative, col='meter')\ng = g.set(xscale='log')\ng = g.map(plt.hist, 'std', bins=np.logspace(0, 1, num=25))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Footprints"},{"metadata":{"trusted":true},"cell_type":"code","source":"def count_zeros(series):\n    zeros = 0\n    for i in series:\n        if i == 0:\n            zeros += 1\n    return zeros","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"X_train_dayofyear = X_train[['timestamp', 'building_id', 'meter', 'meter_reading']].copy()\nX_train_dayofyear['dayofyear'] = X_train_dayofyear['timestamp'].dt.dayofyear\nX_train_dayofyear.drop('timestamp', axis='columns', inplace=True)\nzeros_dayofyear = X_train_dayofyear.groupby(['dayofyear', 'building_id', 'meter']).agg([count_zeros]).dropna()\nzeros_dayofyear = zeros_dayofyear.reset_index()\nzeros_dayofyear.columns = zeros_dayofyear.columns.droplevel(1)\nzeros_dayofyear.rename(columns={'meter_reading':'count_zeros'}, inplace=True)\nzeros_dayofyear.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"fig = px.density_heatmap(zeros_dayofyear[zeros_dayofyear['meter'] == 'electricity'],\n                         x='dayofyear',\n                         y='building_id',\n                         z='count_zeros',\n                         histfunc='sum',\n                         nbinsx=366,\n                         nbinsy=1449,\n                         height=1600)\nfig.update_layout(xaxis={'type': 'category'})\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The phenomenon of the repeated number of zeros is clearly visible in this heatmap.\nA lot of buildings has the same pattern of zeros.\nAnd seem to be very close together.\n\nPatterns:\n\n- sporadic zeros of few hours one over one or a few days (building 476).\n- sporadic zeros of few whole days (building 545).\n- long periods of zeros.\n- most days have few zeros.\n- most days have a lot of zeros.\n- some days are full of data and others full of zeros."},{"metadata":{"trusted":true},"cell_type":"code","source":"fig = px.density_heatmap(zeros_dayofyear[zeros_dayofyear['meter'] == 'chilledwater'],\n                         x='dayofyear',\n                         y='building_id',\n                         z='count_zeros',\n                         histfunc='sum',\n                         nbinsx=366,\n                         nbinsy=1449,\n                         height=1600)\nfig.update_layout(xaxis={'type': 'category'})\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"fig = px.density_heatmap(zeros_dayofyear[zeros_dayofyear['meter'] == 'steam'],\n                         x='dayofyear',\n                         y='building_id',\n                         z='count_zeros',\n                         histfunc='sum',\n                         nbinsx=366,\n                         nbinsy=1449,\n                         height=1600)\nfig.update_layout(xaxis={'type': 'category'})\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"fig = px.density_heatmap(zeros_dayofyear[zeros_dayofyear['meter'] == 'hotwater'],\n                         x='dayofyear',\n                         y='building_id',\n                         z='count_zeros',\n                         histfunc='sum',\n                         nbinsx=366,\n                         nbinsy=1449,\n                         height=1600)\nfig.update_layout(xaxis={'type': 'category'})\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}