{"cells":[{"metadata":{},"cell_type":"markdown","source":"The purpose of this notebook is to fill time series gaps in train data set for further analysis, visualization and modelling, and also for convenient visualization of meter values grouped by site/building/meters, with marking of filled values (assuming that zero and missing values contain different information which can help in better predictions)."},{"metadata":{"trusted":true},"cell_type":"code","source":"from pathlib import Path\n\nimport numpy as np\nimport pandas as pd\n\n%matplotlib inline\nimport matplotlib.pyplot as plt\nimport seaborn as sns","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.rcParams['figure.figsize'] = (8, 4) # default - (6, 4)\nplt.rcParams['figure.max_open_warning'] = 1250 # default - 20","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"DATA_PATH = Path('../input/ashrae-energy-prediction')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df = pd.read_csv(DATA_PATH/'train.csv', parse_dates=['timestamp'])\ntest_df = pd.read_csv(DATA_PATH/'test.csv', parse_dates=['timestamp'])\nbuilding_df = pd.read_csv(DATA_PATH/'building_metadata.csv')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"One of the differencies between train and test data - there are missing records in train dataset for specific timestamp - building_id - meter values:"},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df.groupby('timestamp')['meter'].count().plot()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We don't have this problem in test dataset:"},{"metadata":{"trusted":true},"cell_type":"code","source":"test_df.groupby('timestamp')['meter'].count().plot()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We can add missing records using the following steps:\n- Set index for timestamp - building_id - meter values\n- Unstack by timestamp, temporarily fill missing values with -1, and stack by timestamp\n- Sort values by timestamp - building_id - meter values like in original train dataset\n- Reset index to sequential values\n- Create boolean indicator (is_metered) whether data is read or not (True/False)\n- Replace filled values with zeros "},{"metadata":{"trusted":true},"cell_type":"code","source":"train_filled_df = (train_df.set_index(['timestamp', 'building_id', 'meter'])\n                           .unstack('timestamp')\n                           .fillna(-1)\n                           .stack('timestamp')\n                           .reset_index()\n                           .sort_values(['timestamp', 'building_id', 'meter'])\n                           .reset_index(drop=True))\ntrain_filled_df['is_metered'] = (train_filled_df['meter_reading'] != -1)\ntrain_filled_df.loc[train_filled_df['meter_reading'] == -1, 'meter_reading'] = 0\ntrain_filled_df","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"After this transformation, we have the same distribution of meter counts as in test dataset"},{"metadata":{"trusted":true},"cell_type":"code","source":"train_filled_df.groupby('timestamp')['meter'].count().plot()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Let's add building information for further meter visualization grouped by sites"},{"metadata":{"trusted":true},"cell_type":"code","source":"train_filled_full_df = train_filled_df.reset_index().merge(building_df, how='inner', on='building_id')\ntrain_filled_full_df = train_filled_full_df.set_index('index').sort_index()\ntrain_filled_full_df.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We have the following distribution of buildings by sites:"},{"metadata":{"trusted":true},"cell_type":"code","source":"train_filled_full_df.groupby(['site_id'])['building_id'].nunique().plot.bar()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"I suggested that it will be convenient to plot meter values grouped by site/building/meter with marking values that we filled before, to separate zero and missing values.  \nAlso may be it will be useful to see the patterns of meter reading.\nThe code for plotting of multicolored lines is used from this blog - http://abhay.harpale.net/blog/python/how-to-plot-multicolored-lines-in-matplotlib/, with a few modifications, to plot meter values as subplots."},{"metadata":{"trusted":true},"cell_type":"code","source":"def find_contiguous_colors(colors):\n    segs = []\n    curr_seg = []\n    prev_color = ''\n    for c in colors:\n        if c == prev_color or prev_color == '':\n            curr_seg.append(c)\n        else:\n            segs.append(curr_seg)\n            curr_seg = []\n            curr_seg.append(c)\n        prev_color = c\n    segs.append(curr_seg)\n    return segs\n \ndef plot_multicolored_lines(x,y,colors,ax=None,**kwargs):\n    segments = find_contiguous_colors(colors);\n    start= 0\n    \n    for seg in segments:\n        end = start + len(seg)\n        if ax is None:\n            l, = plt.gca().plot(x[start:end],y[start:end],lw=2,c=seg[0], **kwargs)\n        else:\n            l, = ax.plot(x[start:end],y[start:end],lw=2,c=seg[0], **kwargs)\n        start = end","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Now you can specify site_id and visualize all meter values grouped by building/meter:"},{"metadata":{"trusted":true},"cell_type":"code","source":"site_id = 10\n\nsite_df = train_filled_full_df[train_filled_full_df['site_id']==site_id]\nbuildings = site_df['building_id'].unique().tolist()\n\nfor building_id in buildings:\n    building_meter_df = site_df[site_df['building_id']==building_id].set_index('timestamp')\n    \n    fig, axes = plt.subplots(1, 4, figsize=(24, 4))\n    fig.suptitle(f'building_id={building_id}')\n    \n    for key, group in building_meter_df.groupby('meter'): \n        ax = axes[int(key)]\n        ax.set_title(f'meter={key}')\n        \n        if len(group) > 0:\n            colors = group['is_metered'].map({True: 'green', False: 'red'})\n            plot_multicolored_lines(group.index, group['meter_reading'], colors, ax=ax)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"I hope that it will be useful :)"}],"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.9"}},"nbformat":4,"nbformat_minor":1}