{"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_minor":4,"nbformat":4,"cells":[{"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\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 read-only \"../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('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"metre = pd.read_csv(\"/kaggle/input/ashrae-energy-prediction/train.csv\")\nbuilding = pd.read_csv(\"/kaggle/input/ashrae-energy-prediction/building_metadata.csv\")","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"metre.head()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"metre.head()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# import matplotlib.pyplot as plt\n# n, bins, batches  = plt.hist(x= train[\"meter_reading\"], bins=\"auto\", color = '#0504aa')\n# plt.grid(axis =\"y\")\n# plt.xlabel(\"meter_reading\")\n# plt.ylabel(\"count\")\n# plt.title(\"Sale Price distribution\")","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"weather= pd.read_csv(\"/kaggle/input/ashrae-energy-prediction/weather_train.csv\")","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"weather","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"building","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"building[\"primary_use\"].value_counts()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Explore and Fill NA's","metadata":{}},{"cell_type":"code","source":"metre.isnull().sum() /metre.shape[0]","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"building.isnull().sum()/1449 ","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"building.drop([\"floor_count\", \"year_built\"], axis=1, inplace=True)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"weather.isnull().sum()/ weather.shape[0] ","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"weather[weather[\"air_temperature\"].isnull()]","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Mostly, missing air_temp data isn't contigous, so this method could work.","metadata":{}},{"cell_type":"code","source":"weather[weather[\"cloud_coverage\"].isnull()].head(30)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"markdown","source":"# cloud coverage has lots of contigous missing values\nfor example, index9, 10, 11 are missing. If we use backward or forward for filling values, this could lead to inaccurate results, since clouds coverage dynamically changes every hour or less. ?? I'll fill it untill I validate this hypothesis","metadata":{}},{"cell_type":"code","source":"# weather.drop(\"cloud_coverage\", axis=1, inplace=True)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"weather[weather[\"dew_temperature\"].isnull()].head(30) ","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"When filling the missing weather data, using previous or future measurements can be good provided they are not too far in the future or past. We will start with forward/back filling weather data up to 24 hours for each site.","metadata":{}},{"cell_type":"code","source":"#Forward filling missing data in the weather dataset +-24 hours\n#Train weather\nweather_train_f = weather.copy()\nweather_train_f[\"timestamp\"] = pd.to_datetime(weather_train_f[\"timestamp\"])\nweather_train_f = weather_train_f.sort_values(by = [\"site_id\", \"timestamp\"])\n\nweather_train_f.fillna(method= \"ffill\", inplace=True, limit=5)\nweather_train_f.fillna(method = \"bfill\", inplace=True, limit=5)\n\n#same for test","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"weather_train_f.isnull().sum()/weather_train_f.shape[0]","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"precip_depth is a dynamically changing column; forward filling wouldn't make any sense.???","metadata":{}},{"cell_type":"markdown","source":"to save memory","metadata":{}},{"cell_type":"code","source":"del weather","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"\n\nAs seen above, there are still blocks of data missing over long periods of time. For these we will fill with the mean values for the given site.\n","metadata":{}},{"cell_type":"code","source":"#Train data\n\nmissing_cols = [col for col in weather_train_f.columns if weather_train_f[col].isna().any()]\nfill_lib = weather_train_f.groupby(\"site_id\")[missing_cols].transform(\"mean\")\nweather_train_f.fillna(fill_lib, inplace=True)\n\n#Test data\n","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"weather_train_f.isnull().sum()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# THIS IS NOT THE BEST METHOD TO FILL NULLS. COME BACK TO IT LATER","metadata":{}},{"cell_type":"code","source":"building.isnull().sum()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"markdown","source":"# Corr matrix; variable selection for linear regression","metadata":{}},{"cell_type":"markdown","source":"# Merge","metadata":{}},{"cell_type":"code","source":"metre[\"timestamp\"] = pd.to_datetime(metre[\"timestamp\"])","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"building_metres = pd.merge(metre, building, how = \"left\", on=\"building_id\")","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train = pd.merge(building_metres, weather_train_f, how= \"left\", on=[\"timestamp\",\"site_id\" ], validate=\"many_to_one\")","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del building\ndel metre","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.shape","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Check NA's after merge","metadata":{}},{"cell_type":"code","source":"train.isnull().sum()/train.shape[0]*100","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"code","source":"\n# #we need to temporarily remove the train_m datset from memory here so we do not run out of memory\n# pickle.dump( train_m, open( \"train_m.p\", \"wb\" ) )\n# del train_m\n\n\n# train_m = pickle.load( open('train_m.p', 'rb')) #load back in the train data\n##########\ntrain_m = train.sort_values(by=[\"building_id\", \"timestamp\"])\ntrain_m.fillna(method = \"ffill\", inplace=True)\n","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# train_m.isnull().sum()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Optimize memory usage","metadata":{}},{"cell_type":"code","source":"","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# # train.columns(include = \"float64\")\n# train_float = train.select_dtypes(include = \"float64\")\n# #train_float.columns.tolist()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train.info()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"float_cols = ['square_feet', 'air_temperature',\n              'dew_temperature', 'precip_depth_1_hr',\n              'sea_level_pressure', 'wind_direction', 'wind_speed',\n              'meter_reading']\n\nint_cols = ['building_id', 'meter', 'site_id', 'square_feet']\n\nfor column in float_cols:\n    train_m[column] = pd.to_numeric(train_m[column], downcast = 'float')\n\nfor column in int_cols:\n    train_m[column] = pd.to_numeric(train_m[column], downcast = 'integer')","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# train","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Feature engineering; time of day; peak times?","metadata":{}},{"cell_type":"code","source":"# ","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# train_m[\"year\"].value_counts()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Extract hour, day, month, year from timestamp","metadata":{}},{"cell_type":"code","source":"\n\n#Add hour, time of year, and weekend columns\n#Train\n\n\ntrain_m[\"hour\"] = train_m[\"timestamp\"].dt.hour\ntrain_m[\"day\"] = train_m[\"timestamp\"].dt.day\ntrain_m[\"month\"] = train_m[\"timestamp\"].dt.month\ntrain_m[\"day_of_year\"] = (train_m[\"timestamp\"] - pd.Timestamp(\"2016-01-01\")).dt.days%365\ntrain_m[\"is_weekend\"] = train_m[\"timestamp\"].dt.weekday.isin([5, 6]).astype(int)\n#Test\n####\ntrain_m","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\n#bucketing hours into 6 buckets\ntrain_m[\"part_of_day\"] = (train_m[\"hour\"] %24 +4 ) //4\n","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_m[\"part_of_day\"].replace({1: \"Late Night\",\n                               2: \"Early Morning\", \n                               3: \"Morning\", \n                               4: \"Noon\", \n                               5: \"Evening\", \n                               6: \"Night\"}, inplace = True)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# train_m.drop(\"year\", axis=1, inplace=True)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_m.reset_index(drop=True)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"code","source":"","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Visualize target variable","metadata":{}},{"cell_type":"code","source":"# import matplotlib.pyplot as plt\n# n, bins, batches  = plt.hist(x= train_m[\"meter_reading\"], bins=\"auto\", color = '#0504aa')\n# plt.grid(axis =\"y\")\n# plt.xlabel(\"meter_reading\")\n# plt.ylabel(\"count\")\n# plt.title(\"Sale Price distribution\")","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"__3 years later__","metadata":{}},{"cell_type":"markdown","source":"the target variable is skewed>> __take log__","metadata":{}},{"cell_type":"markdown","source":"# Convert units","metadata":{}},{"cell_type":"code","source":"train_m['meter_reading']=train_m[['site_id','meter','meter_reading']].apply(\n    lambda x: x['meter_reading']*0.2931 \n    if x['meter']==0 and x['site_id']==0 \n    else x['meter_reading'], axis=1)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_m[(train_m ==0 ) &(train_m['site_id']==0)]","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# outliers?","metadata":{}},{"cell_type":"code","source":"alltrain[alltrain.meter_reading==0].groupby(\"site_id\").size().sort_values()[-5:]\n","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"","metadata":{}},{"cell_type":"code","source":"# train_m[['site_id','meter','meter_reading']]","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(\"hi\")","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}