{"cells":[{"metadata":{"trusted":true},"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load in \n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the \"../input/\" directory.\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('../input/ashrae-energy-prediction'):\n    for filename in filenames:\n        #df_name = os.path.splitext(filename)[0]\n        print(os.path.join(dirname, filename))\n\n# Any results you write to the current directory are saved as output.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import matplotlib.pyplot as plt\n%matplotlib inline\nimport seaborn as sns\nfrom time import time\nimport datetime\npd.set_option('display.max_columns',100)\npd.set_option('display.max_rows',1500)\npd.set_option('display.float_format', lambda x: '%.2f' % x)\nfrom collections import Counter \nfrom pandas.plotting import register_matplotlib_converters\nregister_matplotlib_converters()\nimport gc\nfrom sklearn.preprocessing import StandardScaler\nfrom sklearn.model_selection import train_test_split\n\nfrom sklearn.ensemble import RandomForestRegressor\nfrom sklearn.metrics import roc_auc_score, accuracy_score, precision_score, recall_score, \\\n                            f1_score, roc_curve, confusion_matrix","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# importing datas of weather and simultaneously determine the datatypes:\n\nweather_dtype = {\"site_id\":\"uint8\",'air_temperature':\"float16\",'cloud_coverage':\"float16\",'dew_temperature':\"float16\",'precip_depth_1_hr':\"float16\",\n                 'sea_level_pressure':\"float32\",'wind_direction':\"float16\",'wind_speed':\"float16\"}\n\ndf_weather_train=pd.read_csv('../input/ashrae-energy-prediction/weather_train.csv', parse_dates=['timestamp'],dtype=weather_dtype)\ndf_weather_test=pd.read_csv('../input/ashrae-energy-prediction/weather_test.csv', parse_dates=['timestamp'],dtype=weather_dtype)\n\n\n# importing datas of building characteristics. \n\nmetadata_dtype = {'site_id':\"uint8\",'building_id':'uint16','square_feet':'int','year_built':'float32','floor_count':\"float16\"}\ndf_buildings=pd.read_csv('../input/ashrae-energy-prediction/building_metadata.csv', dtype=metadata_dtype)\n\n\n\n# importing train-data\n\ntrain_dtype = {'meter':\"uint8\",'building_id':'uint16','meter_reading':\"float32\"}\ndf_train=pd.read_csv('../input/ashrae-energy-prediction/train.csv',parse_dates=['timestamp'],dtype=train_dtype)\n\n\n# importing test-data\n\ndf_test=pd.read_csv('../input/ashrae-energy-prediction/test.csv',parse_dates=['timestamp'],dtype=train_dtype)\n\n\nprint(\"data loaded\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# find missing values in dataframes:\ndef missing_values(df):\n    return pd.DataFrame(df.isna().sum()/len(df),columns=[\"% NANs\"])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## weather"},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"df_weather_train.isnull().sum(axis = 0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_weather_train.describe()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Weather data contains many NaNs. Some should be imputed and some omitted. Cloud_coverage, precip_depth_1_hr datasets are with biggest gaps. From the distribution of cloud_coverage (data not shown) and for its importance we decided to throw our only the precip_depth_1_hr value.  Importantly, we imputed the missing gaps in weather dataset *site-specificaly*, because sites are connected to the actual location where the weather data comes from. \n\nWe will try to impute the other values."},{"metadata":{},"cell_type":"markdown","source":"## buildings"},{"metadata":{"scrolled":false,"trusted":true},"cell_type":"code","source":"display(df_buildings.describe())\ndisplay(missing_values(df_buildings))\ndf_buildings.info(memory_usage='deep')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Looking at the building data, we decided to remove \"floor_count\" and \"year_built\" features from the dataset, because they copntain 53% and 76% NaNs, respectively, which can't be computed."},{"metadata":{},"cell_type":"markdown","source":"## train"},{"metadata":{"trusted":true},"cell_type":"code","source":"display(df_train.head())\ndisplay(missing_values(df_train))\ndisplay(df_train.describe())\ndf_buildings[df_buildings['building_id']==1099]\ndf_buildings[df_buildings['building_id']==1099]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"display(df_train[(df_train['building_id'] == 1099) & (df_train['meter'] == 2)]['meter_reading'].describe())\ndf_buildings[df_buildings['building_id']==1099]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# dataset without rows with building1099\ntt = df_train[df_train.building_id != 1099]\nq75 = tt[tt['meter'] == 2]['meter_reading'].quantile(0.75)\nq25 = tt[tt['meter'] == 2]['meter_reading'].quantile(0.25)\n\nIQR = q75-q25\nlowerIQR = q25 - 1.5*(IQR)\nupperIQR = q75 + 1.5*(IQR)\nprint(lowerIQR, upperIQR)\nprint(int(21904700/2483.25))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ajut=df_train[(df_train['building_id'] == 1099) & (df_train['meter'] == 2)]\najut_e=df_train[(df_train['building_id'] == 1099) & (df_train['meter'] == 0)]\n\nplt.plot(ajut_e['timestamp'].dt.date, ajut_e['meter_reading'])\nplt.plot(ajut['timestamp'].dt.date, ajut['meter_reading']/8820)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Building nr 1099 'steam' has exceptionally high energy values. We calculated that any value above 2483.25 in the dataset's 'steam' can be considered as outlier. So, we divided the steam meter values for building 1099 by 8820 so that it would fit to the dataset, instead of deleting that building data."},{"metadata":{"trusted":true},"cell_type":"code","source":"# look closer at site 0 energuy consumtion\n\ndf_train['meter_reading'] = np.log1p(df_train['meter_reading'])\n\n\nsite0_bds = list(df_buildings[df_buildings['site_id']==0]['building_id'])\nplt.figure(figsize=(10,6))\nfor i in site0_bds:\n    temp_df = df_train[df_train['building_id'] == i]\n    plt.scatter(temp_df['timestamp'].dt.date, temp_df['meter_reading'], marker='.')\n\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We notioced that adter convervting the energy values to log1p scale, we get a more desent data distribution.\n\nWe also noticed that site 0 has weird energy consumption pattern until 2016.06. We decided to omit this data until that date. "},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.distplot(df_train[df_train['meter'] == 0]['meter_reading'],kde=False, label=\"Electricity\")\nsns.distplot(df_train[df_train['meter'] == 1]['meter_reading'],kde=False, label=\"ChilledWater\")\nsns.distplot(df_train[df_train['meter'] == 2]['meter_reading'],kde=False, label=\"Steam\")\nsns.distplot(df_train[df_train['meter'] == 3]['meter_reading'],kde=False, label=\"HotWater\")\nplt.title(\"Distribution of Log of Meter Reading Variable\")\nplt.legend()\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"\nMost used energy source is Electricity, the others are far less used."},{"metadata":{"trusted":true},"cell_type":"code","source":"btypes = Counter(df_buildings['primary_use'])\nbuilding_meters={}\nfor b in btypes.keys():\n    building_meters[b] = df_buildings[df_buildings['primary_use']==b]['building_id'].unique().tolist()\n\ndf_train['meter'].replace({0:\"Electricity\",1:\"ChilledWater\",2:\"Steam\",3:\"HotWater\"},inplace=True)\n\nfor btype, b_list in building_meters.items():\n    #print(Counter([df_train[df_train['building_id']==b]['meter'] for b in b_list])\n    temp = Counter([df_train[df_train['building_id']==int(b)]['meter'].unique()[0] for b in b_list])\n    print(btype, dict(temp))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"As shown, all buiilding types used 'Electricity'. Many building types contained so few data, that we decided to merge them under buildingtype \"Other\"."},{"metadata":{},"cell_type":"markdown","source":"## Fix data"},{"metadata":{"trusted":true},"cell_type":"code","source":"#import train data again:\n\ndf_train=pd.read_csv('../input/ashrae-energy-prediction/train.csv',parse_dates=['timestamp'],dtype=train_dtype)","execution_count":null,"outputs":[]},{"metadata":{"scrolled":false,"trusted":true},"cell_type":"code","source":"%%time\n\n# remove weird date data from site 0:\nto_del = df_train[(df_train['building_id'] <= 104) & (df_train['timestamp'] <= \"2016-05-20\")].index\ndf_train=df_train.drop(to_del, axis=0)\n\n\n# Fix format error for \"Energy\" in site 0:\n#   Site 0: Multiply by 0.2931 to get to model inputs into kWh like the other sites, and 3.4118 to get back to kBTU for scoring.\ndf_train.loc[(\n    df_train['building_id'] <=104) & (df_train['meter'] == 0), 'meter_reading'] *= 0.2931\n\n# reduce abnormaly high \"steam\" values for building 1099 so that their max_val is out of outlier border \n#  (calculated from all \"steam\" values excluding this one)\ndf_train.loc[(\n    df_train['building_id'] == 1099) & (df_train['meter'] == 2), 'meter_reading'] /= 8744\n\n\n# now convert meter values to log1p: # need to convert back later?\ndf_train['meter_reading'] = np.log1p(df_train['meter_reading'])\n\n\n# Weather: remove 'precip_depth_1_hr'\ndf_weather_train.drop('precip_depth_1_hr',axis=1,inplace=True)\ndf_weather_test.drop('precip_depth_1_hr',axis=1,inplace=True)\n\n\n# BUILDINGS: remove 'floor_count' and 'year_built'\ndf_buildings.drop('floor_count',axis=1,inplace=True)\ndf_buildings.drop('year_built',axis=1,inplace=True)\n\n# group least common building types under \"Other\"\ndf_buildings['primary_use'].replace({'Healthcare':\"Other\",\n                                     'Parking':\"Other\",\n                                     'Warehouse/storage':\"Other\",\n                                     'Manufacturing/industrial':\"Other\", \n                                     'Retail':\"Other\",\n                                     'Services':\"Other\",\n                                     'Technology/science':\"Other\", \n                                     'Food sales and service':\"Other\",\n                                     'Utility':\"Other\", \n                                     'Religious worship':\"Other\"},inplace=True)\n\n\n#impute missing variables for weather (within the site-ids!):\ndef impute_cols(df):\n    \n    cols = df.columns\n    sites=list(Counter(df.site_id).values())\n    sites[0]=sites[0]-1\n    counter = 0\n    for i in sites:\n        df.loc[counter:counter+i, cols] = df.loc[counter:counter+i, cols].interpolate(axis=0)\n        counter+=i\n        \nimpute_cols(df_weather_train)\nimpute_cols(df_weather_test)\n\n\n\n#weather_test_df = weather_test_df.groupby('site_id').apply(lambda group: group.interpolate(limit_direction='both'))\n#df_weather_test.groupby('site_id').apply(lambda group: group.isna().sum())\n#Counter(df_weather_train.isnull().any(axis=1))\n\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"%%time\n\n#Merge all datasets\n\n# for train:\ndf_train = pd.merge(df_train, df_buildings, on='building_id', how='left', copy=False)\ndf_train = pd.merge(df_train, df_weather_train, on=['site_id', 'timestamp'], how='left', copy=False)\n#del(df_train[\"timestamp\"])\nprint(\"trainig data shape:\", df_train.shape)\n\n# for test:\ndf_test = pd.merge(df_test, df_buildings, on='building_id', how='left', copy=False)\ndf_test = pd.merge(df_test, df_weather_test, on=['site_id', 'timestamp'], how='left', copy=False)\n#del(df_test[\"timestamp\"])\n\n\n\n# site 8 has more data on more dates, so we need to trim off data from other sites at these dates:\ndf_train=df_train.dropna()   \ndf_test=df_test.dropna()  \nprint(\"NA values in train dataset:\", dict(Counter(df_train.isnull().any(axis=1))))\nprint(\"NA values in test dataset:\", dict(Counter(df_test.isnull().any(axis=1))))\n#df_train[df_train.isnull().any(axis=1)]\n\n\n# Generate time data from timestamp and delete the latter:\ndef preprocess(df):\n    df[\"hour\"] = df[\"timestamp\"].dt.hour  # test deleting\n    df[\"day\"] = df[\"timestamp\"].dt.day\n    df[\"month\"] = df[\"timestamp\"].dt.month\n    df[\"dayofweek\"] = df[\"timestamp\"].dt.dayofweek\n    df[\"weekend\"] = df[\"dayofweek\"] >= 5\n    del(df[\"timestamp\"])\n    \n\npreprocess(df_train) \npreprocess(df_test)\n\ngc.collect()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(df_test.shape)\nprint(df_train.shape)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## train for each meter separately:\n\n### {0:\"Electricity\",1:\"ChilledWater\",2:\"Steam\",3:\"HotWater\"}"},{"metadata":{"trusted":true},"cell_type":"code","source":"# extra preprocess:\n\ndef prepare_meter_data(metertype):\n\n    # get indexes of rows with selected metertype \n    tr_rowids = df_train[df_train['meter'] == metertype].index\n    ts_rowids = df_test[df_test['meter'] == metertype].index\n\n    # slice out selected rows for train and test dataset separately\n    df_train_mod = df_train.loc[tr_rowids].drop(['meter_reading'], axis=1)\n    df_val_mod = df_train.loc[tr_rowids]['meter_reading']\n\n    df_test_mod = df_test.loc[ts_rowids]\n\n    # delete unnecesarry cols\n    todrop = ([\"hour\", \"day\", \"weekend\", \"meter\"])\n    df_train_mod = df_train_mod.drop(todrop, axis = 1) \n    df_test_mod = df_test_mod.drop(todrop, axis = 1) \n\n    #one-hot encoding for cateorical variables\n    df_train_mod = pd.get_dummies(df_train_mod, columns = [\"month\", \"dayofweek\", \"primary_use\"])\n    df_test_mod = pd.get_dummies(df_test_mod, columns = [\"month\", \"dayofweek\", \"primary_use\"])\n    \n    return (df_train_mod, df_val_mod, df_test_mod)\n","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"# As example, create train, val, test datsets for \"steam\"\ntrain, val, test = prepare_meter_data(2)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(train.shape, val.shape, test.shape)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Create predictions for all models and put them bac to the df_test dataset under 'meter_reading' column for final conversions:"},{"metadata":{},"cell_type":"markdown","source":"## After training the model and predicting the results, you need to transform back some data (in the created 'meter_reading' column in the train_dataset) before submitting it."},{"metadata":{"trusted":false},"cell_type":"code","source":"'''\n\n# convert all meter readings back freom log1p scale: \ndf_test['meter_reading'] = np.expm1(df_test['meter_reading'])\n\n# de-fix format error for \"Energy\" in site 0 ((Multiply model inputs 3.4118 to get back to kBTU for scoring)):\ndf_test.loc[(\n    df_test['building_id'] <=104) & (df_test['meter'] == 0), 'meter_reading'] *= 3.4118\n\n# de-fix abnormaly high \"steam\" values for building 1099 ((multiply values with 8744):\ndf_test.loc[(\n    df_test['building_id'] == 1099) & (df_test['meter'] == 2), 'meter_reading'] *= 8744\n\n'''","execution_count":null,"outputs":[]}],"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.7.4"}},"nbformat":4,"nbformat_minor":1}