{"cells":[{"metadata":{},"cell_type":"markdown","source":"# Imports"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import gc\nimport pickle\nfrom sklearn.preprocessing import LabelEncoder\nimport sys\nimport datetime","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"pd.plotting.register_matplotlib_converters()\nimport matplotlib.pyplot as plt\n%matplotlib inline\nimport seaborn as sns","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Helper functions"},{"metadata":{"trusted":true},"cell_type":"code","source":"## Function to reduce the DF size\ndef reduce_mem_usage(df, verbose=True):\n    numerics = ['int16', 'int32', 'int64', 'float16', 'float32', 'float64']\n    start_mem = df.memory_usage().sum() / 1024**2    \n    for col in df.columns:\n        col_type = df[col].dtypes\n        if col_type in numerics:\n            c_min = df[col].min()\n            c_max = df[col].max()\n            if str(col_type)[:3] == 'int':\n                if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max:\n                    df[col] = df[col].astype(np.int8)\n                elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:\n                    df[col] = df[col].astype(np.int16)\n                elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:\n                    df[col] = df[col].astype(np.int32)\n                elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:\n                    df[col] = df[col].astype(np.int64)  \n            else:\n                if c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max:\n                    df[col] = df[col].astype(np.float16)\n                elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:\n                    df[col] = df[col].astype(np.float32)\n                else:\n                    df[col] = df[col].astype(np.float64)    \n    end_mem = df.memory_usage().sum() / 1024**2\n    if verbose: print('Mem. usage decreased to {:5.2f} Mb ({:.1f}% reduction)'.format(end_mem, 100 * (start_mem - end_mem) / start_mem))\n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Original imputing code for weather from https://www.kaggle.com/aitude/ashrae-missing-weather-data-handling by @aitude\ndef fill_weather_dataset(weather_df):\n    \n    # Find Missing Dates\n    time_format = \"%Y-%m-%d %H:%M:%S\"\n    start_date = datetime.datetime.strptime(weather_df['timestamp'].min(),time_format)\n    end_date = datetime.datetime.strptime(weather_df['timestamp'].max(),time_format)\n    total_hours = int(((end_date - start_date).total_seconds() + 3600) / 3600)\n    hours_list = [(end_date - datetime.timedelta(hours=x)).strftime(time_format) for x in range(total_hours)]\n\n    missing_hours = []\n    for site_id in range(16):\n        site_hours = np.array(weather_df[weather_df['site_id'] == site_id]['timestamp'])\n        new_rows = pd.DataFrame(np.setdiff1d(hours_list,site_hours),columns=['timestamp'])\n        new_rows['site_id'] = site_id\n        weather_df = pd.concat([weather_df,new_rows])\n\n        weather_df = weather_df.reset_index(drop=True)           \n\n    # Add new Features\n    weather_df[\"datetime\"] = pd.to_datetime(weather_df[\"timestamp\"])\n    weather_df[\"day\"] = weather_df[\"datetime\"].dt.day\n    weather_df[\"week\"] = weather_df[\"datetime\"].dt.week\n    weather_df[\"month\"] = weather_df[\"datetime\"].dt.month\n    \n    # Reset Index for Fast Update\n    weather_df = weather_df.set_index(['site_id','day','month'])\n\n    air_temperature_filler = pd.DataFrame(weather_df.groupby(['site_id','day','month'])['air_temperature'].mean(),columns=[\"air_temperature\"])\n    weather_df.update(air_temperature_filler,overwrite=False)\n\n    # Step 1\n    cloud_coverage_filler = weather_df.groupby(['site_id','day','month'])['cloud_coverage'].mean()\n    # Step 2\n    cloud_coverage_filler = pd.DataFrame(cloud_coverage_filler.fillna(method='ffill'),columns=[\"cloud_coverage\"])\n\n    weather_df.update(cloud_coverage_filler,overwrite=False)\n\n    due_temperature_filler = pd.DataFrame(weather_df.groupby(['site_id','day','month'])['dew_temperature'].mean(),columns=[\"dew_temperature\"])\n    weather_df.update(due_temperature_filler,overwrite=False)\n\n    # Step 1\n    sea_level_filler = weather_df.groupby(['site_id','day','month'])['sea_level_pressure'].mean()\n    # Step 2\n    sea_level_filler = pd.DataFrame(sea_level_filler.fillna(method='ffill'),columns=['sea_level_pressure'])\n\n    weather_df.update(sea_level_filler,overwrite=False)\n\n    wind_direction_filler =  pd.DataFrame(weather_df.groupby(['site_id','day','month'])['wind_direction'].mean(),columns=['wind_direction'])\n    weather_df.update(wind_direction_filler,overwrite=False)\n\n    wind_speed_filler =  pd.DataFrame(weather_df.groupby(['site_id','day','month'])['wind_speed'].mean(),columns=['wind_speed'])\n    weather_df.update(wind_speed_filler,overwrite=False)\n\n    # Step 1\n    precip_depth_filler = weather_df.groupby(['site_id','day','month'])['precip_depth_1_hr'].mean()\n    # Step 2\n    precip_depth_filler = pd.DataFrame(precip_depth_filler.fillna(method='ffill'),columns=['precip_depth_1_hr'])\n\n    weather_df.update(precip_depth_filler,overwrite=False)\n\n    weather_df = weather_df.reset_index()\n    weather_df = weather_df.drop(['datetime','day','week','month'],axis=1)\n        \n    return weather_df","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# ETL"},{"metadata":{"trusted":true},"cell_type":"code","source":"building_metadata = pd.read_csv('/kaggle/input/ashrae-energy-prediction/building_metadata.csv')\ndata = pd.read_csv('/kaggle/input/ashrae-energy-prediction/train.csv')\nweather = pd.read_csv('/kaggle/input/ashrae-energy-prediction/weather_train.csv')       ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Basic preprocessing"},{"metadata":{"trusted":true},"cell_type":"code","source":"def basic_preprocessing(building_metadata, weather, data):\n\n    data = reduce_mem_usage(data)\n    building_metadata = reduce_mem_usage(building_metadata)\n    weather = reduce_mem_usage(weather)\n\n    # joining by building_id\n    data = (building_metadata.set_index(\"building_id\").join(data.set_index(\"building_id\"))).reset_index()\n\n    # Correct units for site 0 to kwh    \n    data.loc[(data['site_id'] == 0) & (data['meter'] == 0), 'meter_reading'] = data[(data['site_id'] == 0) & (data['meter'] == 0)]['meter_reading'] * 0.2931    \n    \n    # joining by site_id and timestamp using multi indexes\n    data = data.set_index(['site_id','timestamp']).join(weather.set_index(['site_id','timestamp'])).reset_index()\n    del building_metadata, weather\n    gc.collect()\n    \n    # Convert timestamp string to datetime\n    data.loc[:, 'timestamp'] = pd.to_datetime(data.timestamp)\n\n    # Remove all rows where the meter reading is 0\n    data = data.drop(data.loc[data.meter_reading == 0].index, axis = 0)\n    \n    data = reduce_mem_usage(data)   \n    print(data.memory_usage().sum() / 1024**2, 'Mb')    \n        \n    return data","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def preprocessing():\n    building_metadata = pd.read_csv('/kaggle/input/ashrae-energy-prediction/building_metadata.csv')\n    data = pd.read_csv('/kaggle/input/ashrae-energy-prediction/train.csv')\n    weather = pd.read_csv('/kaggle/input/ashrae-energy-prediction/weather_train.csv')           \n   \n    weather = fill_weather_dataset(weather)\n\n    data = reduce_mem_usage(data)\n    building_metadata = reduce_mem_usage(building_metadata)\n    weather = reduce_mem_usage(weather)\n\n    # joining by building_id\n    data = (building_metadata.set_index(\"building_id\").join(data.set_index(\"building_id\"))).reset_index()\n\n    # Correct units for site 0 to kwh    \n    data.loc[(data['site_id'] == 0) & (data['meter'] == 0), 'meter_reading'] = data[(data['site_id'] == 0) & (data['meter'] == 0)]['meter_reading'] * 0.2931    \n    \n    # joining by site_id and timestamp using multi indexes\n    data = data.set_index(['site_id','timestamp']).join(weather.set_index(['site_id','timestamp'])).reset_index()\n    del building_metadata, weather\n    gc.collect()\n    \n    # Convert timestamp string to datetime\n    data.loc[:, 'timestamp'] = pd.to_datetime(data.timestamp)\n    data['month'] = pd.DatetimeIndex(data.timestamp).month\n    data['weekday'] = pd.DatetimeIndex(data.timestamp).dayofweek\n    data['hour'] = pd.DatetimeIndex(data.timestamp).hour\n    data['day'] = pd.DatetimeIndex(data.timestamp).day\n\n    # Remove outliers\n    Meter1_Outliers = data.loc[(data.meter == 1) & (data.meter_reading > 20000)].building_id.unique()\n    data = data[~data['building_id'].isin(Meter1_Outliers)] \n    Meter2_Outliers = data.loc[(data.meter == 2) & (data.meter_reading > 20000)].building_id.unique()\n    data = data[~data['building_id'].isin(Meter2_Outliers)] \n    Meter3_Outliers = data.loc[(data.meter == 3) & (data.meter_reading > 5000)].building_id.unique()\n    data = data[~data['building_id'].isin(Meter3_Outliers)] \n\n    # Remove all rows where the meter reading is 0\n    data = data.drop(data.loc[data.meter_reading == 0].index, axis = 0)\n    y = data.meter_reading\n    data = data.drop('meter_reading', axis = 1)           \n    \n    # Dropping useless\n    useless = ['timestamp', \"sea_level_pressure\", \"wind_direction\", \"wind_speed\",\"year_built\",\"floor_count\"]\n    data = data.drop(useless, axis = 1)\n    gc.collect()\n    \n    le = LabelEncoder()\n    data[\"primary_use\"] = le.fit_transform(data[\"primary_use\"])\n    output = open('LabelEncoder.pkl', 'wb')\n    pickle.dump(le, output)\n    output.close()\n    \n    output = open('data_train.pkl', 'wb')  \n    pickle.dump(data, output)\n    output.close()\n    \n    output = open('y.pkl', 'wb')  \n    pickle.dump(y, output)\n    output.close()\n    \n    data = reduce_mem_usage(data)   \n    print(data.memory_usage().sum() / 1024**2, 'Mb')    \n        \n    return data, y","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"data = basic_preprocessing(building_metadata, weather, data)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Prepares and saves preprocessed data for use by the model notebook\npreprocessing()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Data Exploration"},{"metadata":{},"cell_type":"markdown","source":"## 1. Basic description"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Global data. Pretty obvious outliers in the meter_readings. \n#But where is the threshold between large building consumption and a wrong measurement ?\ndata.describe()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### 1.1 Analysis by meter"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Data for power consumption (meter = 0)\ndata.loc[(data.meter == 0)].describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(14,7))\nsns.set(style=\"whitegrid\")\nax = sns.boxplot(x=data.loc[(data.meter == 0)].meter_reading)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Limiting ourselves to reasonable values\nplt.figure(figsize=(14,7))\nsns.set(style=\"whitegrid\")\nax = sns.boxplot(x=data.loc[(data.meter == 0) & (data.meter_reading < 1000)].meter_reading)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# How many buildings have large values? which ones?\ndata.loc[(data.meter == 0) & (data.meter_reading > 400)].nunique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"data.loc[(data.meter == 0) & (data.meter_reading > 400)].building_id.unique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"data.loc[(data.meter == 0) & (data.meter_reading > 400)].describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Data for chilled water consumption (meter = 1)\ndata.loc[(data.meter == 1)].describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(14,7))\nsns.set(style=\"whitegrid\")\nax = sns.boxplot(x=data.loc[(data.meter == 1)].meter_reading)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(14,7))\nsns.set(style=\"whitegrid\")\nax = sns.boxplot(x=data.loc[(data.meter == 1) & (data.meter_reading < 3000)].meter_reading)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"data.loc[(data.meter == 1) & (data.meter_reading > 20000)].nunique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"data.loc[(data.meter == 1) & (data.meter_reading > 20000)].building_id.unique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"PotentialOutliers = data.loc[(data.meter == 1) & (data.meter_reading > 20000)]\nPotentialOutliers.groupby(['building_id']).apply(lambda df: df.loc[df.meter_reading.idxmax()])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ax = sns.scatterplot(x=PotentialOutliers.building_id, y = PotentialOutliers.meter_reading )","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Data for steam consumption (meter = 2)\ndata.loc[(data.meter == 2)].describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(14,7))\nsns.set(style=\"whitegrid\")\nax = sns.boxplot(x=data.loc[(data.meter == 2)].meter_reading)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(14,7))\nsns.set(style=\"whitegrid\")\nax = sns.boxplot(x=data.loc[(data.meter == 2) & (data.meter_reading < 5000)].meter_reading)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"data.loc[(data.meter == 2) & (data.meter_reading > 20000)].nunique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"data.loc[(data.meter == 2) & (data.meter_reading > 20000)].building_id.unique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"PotentialOutliers = data.loc[(data.meter == 2) & (data.meter_reading > 20000)]\nPotentialOutliers.groupby(['building_id']).apply(lambda df: df.loc[df.meter_reading.idxmax()])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ax = sns.scatterplot(x=PotentialOutliers.building_id, y = PotentialOutliers.meter_reading )","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Data for hot water consumption (meter = 3)\ndata.loc[(data.meter == 3)].describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(14,7))\nsns.set(style=\"whitegrid\")\nax = sns.boxplot(x=data.loc[(data.meter == 3)].meter_reading)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(14,7))\nsns.set(style=\"whitegrid\")\nax = sns.boxplot(x=data.loc[(data.meter == 3) & (data.meter_reading < 5000)].meter_reading)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"data.loc[(data.meter == 3) & (data.meter_reading > 5000)].nunique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"data.loc[(data.meter == 3) & (data.meter_reading > 5000)].building_id.unique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"PotentialOutliers = data.loc[(data.meter == 3) & (data.meter_reading > 5000)]\nPotentialOutliers.groupby(['building_id']).apply(lambda df: df.loc[df.meter_reading.idxmax()])\nax = sns.scatterplot(x=PotentialOutliers.building_id, y = PotentialOutliers.meter_reading )","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## 2. Features"},{"metadata":{},"cell_type":"markdown","source":"### 2.1 Square feet\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(14,7))\nsns.set(style=\"whitegrid\")\nax = sns.boxplot(x=building_metadata.square_feet)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Concentrating on big buildings (for identifying the outliers)\nBig = building_metadata.loc[building_metadata.square_feet > 600000]\nBig.nunique()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### 2.2 Weather data\nThese are the most problematic as there are many missing or wrong data. Since time and weather station are essential, we'll use heatmaps.\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"weather.isna().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather.loc[:, 'timestamp'] = pd.to_datetime(weather.timestamp)\nweather.loc[:, 'timestamp'] = pd.to_datetime(weather.timestamp)\nweather['month'] = pd.DatetimeIndex(weather.timestamp).month\nweather['weekday'] = pd.DatetimeIndex(weather.timestamp).dayofweek\nweather['hour'] = pd.DatetimeIndex(weather.timestamp).hour\nweather['day'] = pd.DatetimeIndex(weather.timestamp).day\nweather['date'] = pd.to_timedelta(weather.timestamp).dt.total_seconds() / 3600\nweather['date'] = weather.date.astype(int)\nweather.date -= weather.date.min()\nweather.date.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Air temp\nplt.figure(figsize=(14,7))\nmissmap = np.empty((16, weather.date.max()+1))\nmissmap.fill(np.nan)\nfor l in weather.values:\n    missmap[int(l[0]), int(l[13])] = l[2]\nsns.heatmap(missmap)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# cloud : site 7 and 11 have no values\nplt.figure(figsize=(14,7))\nmissmap = np.empty((16, weather.date.max()+1))\nmissmap.fill(np.nan)\nfor l in weather.values:\n    missmap[int(l[0]), int(l[13])] = l[3]\nsns.heatmap(missmap)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# precipitation : sites 1, 5, 12 have none\nplt.figure(figsize=(14,7))\nmissmap = np.empty((16, weather.date.max()+1))\nmissmap.fill(np.nan)\nfor l in weather.values:\n    missmap[int(l[0]), int(l[13])] = l[5]\nsns.heatmap(missmap)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# pressure : site 5 missing\nplt.figure(figsize=(14,7))\nmissmap = np.empty((16, weather.date.max()+1))\nmissmap.fill(np.nan)\nfor l in weather.values:\n    missmap[int(l[0]), int(l[13])] = l[6]\nsns.heatmap(missmap)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# wind dir\nplt.figure(figsize=(14,7))\nmissmap = np.empty((16, weather.date.max()+1))\nmissmap.fill(np.nan)\nfor l in weather.values:\n    missmap[int(l[0]), int(l[13])] = l[7]\nsns.heatmap(missmap)","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}