{"cells":[{"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 seaborn as sns\nimport os\nimport matplotlib.pyplot as plt\nfrom datetime import datetime\nfrom tqdm import tqdm","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"rootdir='/kaggle/input/ashrae-energy-prediction/'\ntrain = pd.read_csv(rootdir + 'train.csv')\n# convert timestamps\ntrain['timestamp'] = pd.to_datetime(train['timestamp'],format='%Y-%m-%d %H:%M:%S')\n# add log_meter_reading\ntrain['log_meter_reading'] = np.log1p(train['meter_reading'])\ntrain.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Averages**"},{"metadata":{"trusted":true},"cell_type":"code","source":"def ave_before_date(df, date_value):\n    dfsub = df.loc[df.timestamp<date_value,['log_meter_reading']]\n    return np.nanmean(dfsub['log_meter_reading'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def ave_after_date(df, date_value):\n    dfsub = df.loc[df.timestamp>date_value,['log_meter_reading']]\n    return np.nanmean(dfsub['log_meter_reading'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"date_value = datetime(2016,5,21)\nprint('Average BEFORE cut-off date...')\nave_bef = train.groupby(['building_id','meter']).apply(lambda x: ave_before_date(x, date_value)).to_frame()\nave_bef.columns=['before']\nprint('Average AFTER cut-off date...')\nave_aft = train.groupby(['building_id','meter']).apply(lambda x: ave_after_date(x, date_value)).to_frame()\nave_aft.columns=['after']\nprint('Merge...')\nave_total = pd.merge(ave_bef,ave_aft,on=['building_id','meter'])\nave_total.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# some time series do not have points for the entire year, so the average results in NaN's\n\n# set nans to zero\nfor col in ave_total.columns:\n    ave_total.loc[np.isnan(ave_total[col]), col] = 0","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(8,6))\nsns.scatterplot(ave_total.after, ave_total.before)\nplt.grid();","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(10,6))\nsns.scatterplot(ave_total.after, ave_total.before)\nplt.grid();\nplt.xscale('log')\nplt.yscale('log')\nplt.xlim(0.001,15);\nplt.ylim(0.001,15);","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"thr = 0.1 # arbitrary threshold\nprint(f'Series with average below {thr} before cut-off date: ' + str(sum(ave_total['before']<thr)))\nprint(f'Series with average below {thr} after cut-off date: ' + str(sum(ave_total['after']<thr)))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# rows where average is below threshold\nix_before = ave_total.loc[ave_total.before<thr].index.to_frame()\nix_before.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ix_after = ave_total.loc[ave_total.after<thr].index.to_frame()\n\n#ix_after.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Count the number of time series with average below threshold by grouping them by meter**"},{"metadata":{},"cell_type":"markdown","source":"Most time series with average below threshold before cut-off date have meter=0, although some exist with other meter types"},{"metadata":{"trusted":true},"cell_type":"code","source":"ix_before.meter.value_counts()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"There exist a few time series with average below threshold AFTER cut-off date"},{"metadata":{"trusted":true},"cell_type":"code","source":"ix_after.meter.value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# time series with meter = 1,2, or 3\nix_before_not0meter=ix_before.loc[ix_before.meter>0]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"k=-1","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Use the next cell to browse through the time series with average below threshold"},{"metadata":{"trusted":true},"cell_type":"code","source":"k +=1\nplt.figure(figsize=(8,6))\nbid = ix_after.iloc[k,0]\nmid = ix_after.iloc[k,1]\nbuilding_time_series = train.loc[(train.building_id==bid) & (train.meter==mid)]\nbef_value = ave_total.loc[bid].loc[mid]['before']\naft_value = ave_total.loc[bid].loc[mid]['after']\nplt.plot(building_time_series.timestamp, building_time_series.meter_reading);\nplt.title('Building {:d} - Meter {:d} - Before {:.2f} After {:.2f}'.format(bid,mid,bef_value,aft_value));\nplt.grid();","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(8,5))\nfor col in ['before','after']:\n    sns.distplot(ave_total[col],label=col,kde=False)\nplt.grid();\nplt.legend();\nplt.xlabel('Average log meter');\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"**Cleanup**"},{"metadata":{"trusted":true},"cell_type":"code","source":"# copy dataframe\ntrain_clean = train.copy()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# remove sections where before_average < threshold\nfor k in tqdm(range(len(ix_before))):\n    bid = ix_before.iloc[k,0]\n    mid = ix_before.iloc[k,1]\n    ix_to_drop = train_clean[(train_clean.building_id == bid) & (train_clean.meter == mid) & (train_clean.timestamp < date_value)].index\n    train_clean.drop(ix_to_drop, inplace = True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# remove sections where after_average < threshold\nfor k in tqdm(range(len(ix_after))):\n    bid = ix_after.iloc[k,0]\n    mid = ix_after.iloc[k,1]\n    ix_to_drop = train_clean[(train_clean.building_id == bid) & (train_clean.meter == mid) & (train_clean.timestamp < date_value)].index\n    train_clean.drop(ix_to_drop, inplace = True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# new length\nprint('New dataset length: ' + str(len(train_clean)))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# cleanup ratio\nprint('{:.1f}% of datapoints removed from original training dataset'.format(100*(1-len(train_clean)/len(train))))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Export new dataset"},{"metadata":{"trusted":true},"cell_type":"code","source":"train_clean.to_csv('train_cleanup01.csv',index=False)","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}