{"cells":[{"metadata":{},"cell_type":"markdown","source":"1.  [Introduction](#section-one)\n2.  [Libraries](#section-two)\n3.  [Understanding The Data](#section-tree)\n    - [Train Set](#subsection-trainset)\n        - [Train](#subsection-t)\n        - [Building Metadata](#subsection-b)\n        - [Weather train](#subsection-w)\n4.  [Exploratory Data Analysis](#section-four)\n    - [Merging Tables](#subsection-m)\n    - [Analysis](#subsection-a)\n        - [Removing Outliers](#subsection-o)\n5.  [Modeling](#section-five)"},{"metadata":{},"cell_type":"markdown","source":"<a id=\"section-one\"></a>\n# 1. Introduction"},{"metadata":{},"cell_type":"markdown","source":"<a id=\"section-two\"></a>\n# 2. Libraries"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Set your own project id here\nPROJECT_ID = 'ASHRAE - Energy Prediction1'\nfrom google.cloud import storage\nstorage_client = storage.Client(project=PROJECT_ID)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"!pip install seaborn==0.11.0","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true,"_kg_hide-input":false},"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nimport os\nimport matplotlib as mpl\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport gc","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"print(f'''Pandas version: {pd.__version__}\nNumPy version: {np.__version__}\nMatplotlib version: {mpl.__version__}\nSeaborn version: {sns.__version__}''')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"gc.enable()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<a id=\"section-tree\"></a>\n# 3. Understanding The Data"},{"metadata":{},"cell_type":"markdown","source":"Six tables are given to predict building energy usage in future. One of these tables is sample submission file. This table will not be used for neigher training nor testing. \n\nThere are three tables for model training,\n* train\n* weather_train\n* building_metadata\n\nTwo tables for testing,\n* test\n* weather_test\n\nAll tables are given in csv format. In this notebook pandas is used to import, read and manipulate the data. \n"},{"metadata":{},"cell_type":"markdown","source":"<a id=\"subsection-trainset\"></a>\n# Train Set"},{"metadata":{"trusted":true},"cell_type":"code","source":"train = pd.read_csv('../input/ashrae-energy-prediction/train.csv')\nweather_train = pd.read_csv('../input/ashrae-energy-prediction/weather_train.csv')\nbuilding = pd.read_csv('../input/ashrae-energy-prediction/building_metadata.csv')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"During the data analysis I face the problem with memory usage. Solve this problem by reducing memory size of the data frames without changing the data.\nIn the cell below there is a memory reduction function."},{"metadata":{"trusted":true,"_kg_hide-input":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,"_kg_hide-input":true},"cell_type":"code","source":"train = reduce_mem_usage(train)\nweather_train = reduce_mem_usage(weather_train)\nbuilding = reduce_mem_usage(building)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<a id=\"subsection-t\"></a>\n## Train\n\n* building_id - Foreign key for the building metadata.\n* meter - The meter id code. Read as {0: electricity, 1: chilledwater, 2: steam, 3: hotwater}. Not every building has all meter types.\n* timestamp - When the measurement was taken\n* meter_reading - The target variable. Energy consumption in kWh (or equivalent). Note that this is real data with measurement error, which we expect will impose a baseline level of modeling error. UPDATE: The site 0 electric meter readings are in kBTU\n\nLet's take a look at the first few rows and data types of train set."},{"metadata":{"trusted":true},"cell_type":"code","source":"train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-output":false},"cell_type":"code","source":"train.info()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"timestamp column contains date information, so the data type of the column is change to datetime.\nAfter changing the data type find time period for recordings (dates that entries start and end)."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"train['timestamp'] = pd.to_datetime(train['timestamp'])\nprint(f'''{train['timestamp'].dtype}\n{train['timestamp'].min()}\n{train['timestamp'].max()}''')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Check the missing values and the distribution of the values."},{"metadata":{"trusted":true},"cell_type":"code","source":"train.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train['meter_reading'].describe()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"There is no missing values in the train data frame. However,meter_reading colum has '0' value. Since the meter_reading colum is representing energy consumption it is not logical a building have zero consumption. Let's take a closer look at the buildings that have zero electricity consumption."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"print('Percent of zero read values:', \"%.2f\"%(train[train['meter_reading']== 0].shape[0] /train.shape[0]))\ntrain[train['meter_reading'] == 0]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Only 9% of the meter readings are zero. 9% could be imputed with the 91% with a good accuracy. Try to find if there is a visible pattern between zero values.\n"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"print(f'''Total number of buildings: {train['building_id'].nunique()}\nNumber of buildings with zero readings: {train[train['meter_reading']== 0][\"building_id\"].value_counts().shape[0]}\nPercentage: {round(train[train['meter_reading']== 0][\"building_id\"].value_counts().shape[0] / train['building_id'].nunique(),2)}\n\nNumber of zero readings per building: \n{train[train['meter_reading']== 0][\"building_id\"].value_counts().sort_values(ascending = False)}\n\nNumber of buildings with only one zero readings: {sum(train[train['meter_reading']== 0]['building_id'].value_counts() == 1)}\nPercentage of buildings with only one zero reading: {round( sum(train[train['meter_reading']== 0]['building_id'].value_counts() == 1) / train[train['meter_reading']== 0][\"building_id\"].value_counts().shape[0] , 2)}\n''')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Although only 9% of the reading value is zero, 66% of the buildings cotain zero reading values. Number of zero readings varies from 1 to 16031. Small number of zero readings might occur due to an equipment error, however for larger amounts ity is most likely that there is another reason. \n\nAlso, it is possible that a building have more than one meter type. So having zero reading doesn't have to mean that there is no energy consumption at all. It is possible to use different types of energy sources time to time. \n\nHaving that in mind, let's see if one kind of meter contains more zero values than other."},{"metadata":{"trusted":true},"cell_type":"code","source":"(train[train['meter_reading']== 0]).groupby('building_id')['meter'].value_counts().sort_values(ascending = False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"print('Number of zero readings for each meter:\\n',train[train['meter_reading']== 0][\"meter\"].value_counts(),'\\n')\n\nfor i in range(train[\"meter\"].nunique()):\n    percent = round(train[train['meter_reading']== 0][\"meter\"].value_counts()[i] /train[\"meter\"].value_counts()[i],2)\n    print(f'% of zero reads for Meter {i}: {percent}')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Zero readings are higher for meter type 1 and 0. Type 2 and 3 has zero readings as much as half of type 1.\n\nOn the other hand percentage of zero read values is highest for meter 3, followed by 1 and 2. Type 0 has the lowest percentage with 0.4%.\n\nIt can be said that zero readings related with the meter type. Also, for different meter types seasons might have an effect on energy usage. By analyzing the distribution of the zero readings in given period of time and the application areas of different meter types it can be possible to find the reason and -if neccessary- the best imputation method  for these readings.\n\nMeter type information will be usefull at this point;\n* 0: electricity\n* 1: chilledwater\n* 2: steam\n* 3: hotwater\n\nPS. Not every building has all meter types.\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"train['meter'] = pd.Categorical(train['meter']).rename_categories({0: 'electricity', 1: 'chilledwater', 2: 'steam', 3: 'hotwater'})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"g = sns.FacetGrid(train[train['meter_reading']== 0], col=\"meter\",hue = 'meter',palette='coolwarm',col_wrap=2,height=3, aspect=2)\ng.map(sns.histplot, 'timestamp', bins=12)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Occurance of zero readings changes with respect to time and meter type. For electricity meter zeros were read mostly in first 5 months of the year. After that zero reading counts decrease significantly. Like electricity meters chilledwater energy meter also have most of the zeros in the first months of the year. Yet, for chilled water meter decrease on zero readings occurs only between 5 - 9th months and increase start after 9th month, reading counts reach first months again. When comes to steam and hotwater meters zero reading occurance increase in the middle of the year and has a distribution close to normal distribution. \n\nThese values probably highly related with the usage areas of different electiricity sources. To gain a more deeper inside let's review the meter types and usage area;\n\n* 0 - Electricity : An electric meter is a device used to measure the electrical energy usage of a home, building, or other electrically powered device. Digital meters simply state the number of kWh of electricity have been used. It's important to note that neither the digital nor the analog meters reset at the beginning of the month, the power company subtracts off the start from the end to figure out how much to bill the household [[1]](https://commons.wikimedia.org/wiki/File:Hydro_quebec_meter.JPG#/media/File:Hydro_quebec_meter.JPG) [[2]](https://www.hydro.mb.ca/customer_services/how_to_read/meter.shtml).\n* 1 - Chilledwater: Chilled Water Energy Meters, commonly referred to as BTU meters, can be defined by the measurement of heat/chilled water energy      consumption. The quantity of thermal energy transferred from the cooling water to the consumer over a defined period of time is proportional to the temperature difference between the flow and return and the volume of cooling water that has flowed through [[3]](https://www.districtenergy.com/customer-resources/how-it-works/cooling-with-chilled-water/)[[4]](https://www.badgermeter.com/flow-measurement-solutions-for-chilled-water-applications/).\n    * Application Areas: Cooling / heating systems with water as a cooling/heat carrier, transfer stations, larger cooling/heating systems in apartment    buildings(specially in commercial buildings)[[5]](https://www.ista.com/ae/solutions/technology/).\n* 2 - Steam: The use of a steam flowmeter may be used to directly measure the steam usage of an operational item of plant. Steam is one of the most widely used commodities for conveying heat energy. Its use is popular throughout industry for a broad range of tasks from mechanical power production to space heating and process applications[[6]](https://www.spiraxsarco.com/learn-about-steam/introduction/steam---the-energy-fluid).\n* 3: Hotwater: Your hot water meter allows us to measure hot water consumption within your building or apartment so we can charge for the water heated [[7]](https://www.originenergy.com.au/content/dam/origin/residential/docs/hot-water/your-centralised-hot-water.pdf).\n\nIn conclusion I've decide to keep zero readings as it is unless I realize \nany anomalies further in analysis.\n\n**For electiricity:** It is still unlogical to have zero electricity consumption.Possible reasons for zero readings; \n -Using different kind of energy source.\n -Having no informaiton about the consuption and record electiricity consumption as zero to table.\n\n**For Chilled water:** It is possible to have zero reading in cooler months -chilled water system is usually used for cooling purposes- or certain time periods when the building is not used actively.\n\n**For Steam:** Fluctuations in consumption can be seasonal as a result of change in weather. During hot months it is logical to have less or zero consumption. \n\n**For Hot water:** Fluctuations in consumption can be seasonal as a result of change in weather. During hot months it is logical to have less or zero consumption. \n\nBesides from the zeros there are also very high readings in the data. Lets try to see if there are any outliers and the distribution of the readings with a box plot."},{"metadata":{"trusted":true},"cell_type":"code","source":"fig, axes = plt.subplots(1, 1, figsize=(14, 6))\nsns.boxplot(y='meter', x='meter_reading', data=train, showfliers=True)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Looks like we have a lot of outliers! See what will the plot look like without outliers and zero values."},{"metadata":{"trusted":true},"cell_type":"code","source":"fig, axes = plt.subplots(1, 1, figsize=(14, 6))\nsns.boxplot(y='meter', x='meter_reading', data=train[train['meter_reading'] !=  0 ], showfliers=False);","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.lineplot(data=train.groupby(['timestamp']).sum(), x=\"timestamp\", y=\"meter_reading\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.relplot(data=train.groupby(['timestamp','meter']).sum(), x=\"timestamp\", y=\"meter_reading\",col=\"meter\", hue=\"meter\",kind=\"line\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.relplot(data=(train[train['meter'].isin(['electricity','chilledwater','hotwater'])].groupby(['timestamp','meter']).sum()), x=\"timestamp\", y=\"meter_reading\",col=\"meter\", hue=\"meter\",kind=\"line\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"It is clear that we have some very high values in the readings. It is better to indentity outliers considering the building and site information (eg. for a big building with hight energy consumption kWh/area value might not be that high)"},{"metadata":{"trusted":true},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<a id=\"subsection-b\"></a>\n## Building Metadata"},{"metadata":{},"cell_type":"markdown","source":"* site_id - Foreign key for the weather files.\n* building_id - Foreign key for training.csv\n* primary_use - Indicator of the primary category of activities for the building based on EnergyStar property type definitions\n* square_feet - Gross floor area of the building\n* year_built - Year building was opened\n* floor_count - Number of floors of the building\n\nBuilding meta data table includes information about the buildings."},{"metadata":{"trusted":true},"cell_type":"code","source":"building.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"building.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"building.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"building.describe(include = 'O')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"It is interesting that we have inf as mean value of year_built column."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"print(f'Null value counts:\\n{building.isnull().sum()}\\n')\nfor col in list(building.columns):\n    if building[col].isnull().sum() > 0:\n        print(f'% of null in column {col}: {round(building[col].isnull().sum() / building.shape[0], 2 )}' )","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"To analyze distribution of null values for different usage areas Nan values filled with 'Missing'."},{"metadata":{"trusted":true},"cell_type":"code","source":"building.fillna('Missing', inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"print('For Primary Usage Areas\\n')\nprint('% of Null year_built Values Less Than 50%')\nfor usage in list(building['primary_use'].unique()):\n    percent  = round(sum(building[building['primary_use']== usage]['year_built']== 'Missing') / building[building['primary_use']== usage].shape[0], 2)\n    if percent < 0.5:\n        print(f'{usage}: {percent}')\n        \nprint('\\n% of Null year_built Values Higher Than 50%')\nfor usage in list(building['primary_use'].unique()):\n    percent  = round(sum(building[building['primary_use']== usage]['year_built']== 'Missing') / building[building['primary_use']== usage].shape[0], 2)\n    if percent > 0.5:\n        print(f'{usage}: {percent}')\n    ","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"print('For Primary Usage Areas\\n')\nprint('\\n% of Null floor_count Values Less Than 50%\\n')\nfor usage in list(building['primary_use'].unique()):\n    percent  = round(sum(building[building['primary_use']== usage]['floor_count']== 'Missing') / building[building['primary_use']== usage].shape[0], 2)\n    if percent < 0.5:\n        print(f'{usage}: {percent}')\n        \nprint('\\n% of Null floor_count Values Higher Than 50%\\n')\nfor usage in list(building['primary_use'].unique()):\n    percent  = round(sum(building[building['primary_use']== usage]['floor_count']== 'Missing') / building[building['primary_use']== usage].shape[0], 2)\n    if percent > 0.5:\n        print(f'{usage}: {percent}')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"I decide to drop floor_count and year_built because there is not enough information for imputation."},{"metadata":{"trusted":true},"cell_type":"code","source":"building.drop(['floor_count','year_built'],axis=1,inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<a id=\"subsection-w\"></a>\n## Weather train\n\nWeather data from a meteorological station as close as possible to the site.\n\n* site_id\n* air_temperature - Degrees Celsius\n* cloud_coverage - Portion of the sky covered in clouds, in oktas\n* dew_temperature - Degrees Celsius\n* precip_depth_1_hr - Millimeters\n* sea_level_pressure - Millibar/hectopascals\n* wind_direction - Compass direction (0-360)\n* wind_speed - Meters per second\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train.info()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"timestamp column contains date information, so the data type of the column is change to datetime.\nAfter changing the data type find time period for recordings (dates that entries start and end)."},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train['timestamp'] = pd.to_datetime(weather_train['timestamp'])\nprint(f'''{weather_train['timestamp'].dtype}\n{weather_train['timestamp'].min()}\n{weather_train['timestamp'].max()}''')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Null value check"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"print(f'Null value counts:\\n{weather_train.isnull().sum()}\\n')\nfor col in list(weather_train.columns):\n    if weather_train[col].isnull().sum() > 0:\n        print(f'% of null in column {col}: {round(weather_train[col].isnull().sum() / weather_train.shape[0], 4 )}' )","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"There are missing values in seven colums. Luckly, percentage of missing values is not high. So, it is possible to fill missing values. I will drop colud coverage and precipt depth only.\n\nWeather characteristics change according to the season and location, and fluctuations occur even during the day. Hence, to impute missing values with good accuracy site_id and date information must be used. "},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train.drop(['cloud_coverage','precip_depth_1_hr'], axis = 1,inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train['hour'] = weather_train.timestamp.dt.hour\nweather_train['month'] = weather_train.timestamp.dt.month","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Best way to impute meteorological data is using mean values. [reference study](http://www.scienceasia.org/2008.34.n3/scias34_341.pdf)"},{"metadata":{"trusted":true},"cell_type":"code","source":"def site_mean_weather(table):\n    for col in list(table.columns[table.isnull().any()]):\n        imputaion = table.groupby(['site_id','hour','month'])[col].transform('mean')\n        table[col].fillna(imputaion,inplace = True)\n    print('Imputation with mean values is completed.')\n     \nsite_mean_weather(weather_train)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"After imputation I realize there are still missing values in sea_level_pressure column, so I droped that column to."},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train.drop(['sea_level_pressure'], axis = 1, inplace = True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<a id=\"section-four\"></a>\n# 4. Exploratory Data Analysis"},{"metadata":{},"cell_type":"markdown","source":"<a id=\"subsection-m\"></a>\n## Merging Tables "},{"metadata":{},"cell_type":"markdown","source":"To evaluate all train data together train, building and weather tables are merged together and unnecessary tables are deleted."},{"metadata":{"trusted":true},"cell_type":"code","source":"df = pd.merge(train,building, on=\"building_id\", how=\"left\")\ndf = df.merge(weather_train, on=[\"site_id\", \"timestamp\"], how=\"left\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df.nunique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df['month'] = df.timestamp.dt.month\ndf['hour'] = df.timestamp.dt.hour","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Fill the missing weather data with mean values."},{"metadata":{"trusted":true},"cell_type":"code","source":"site_mean_weather(df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"for col in list(df.columns[df.isnull().any()]):    \n    imputaion = df.groupby(['hour','month'])[col].transform('mean')\n    df[col].fillna(imputaion,inplace = True)\nprint('Imputation is completed.')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We don't need train, building and weather_train tables anymore."},{"metadata":{"trusted":true},"cell_type":"code","source":"del train\ndel building\ndel weather_train\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Check the dtypes for further memory reduction."},{"metadata":{"trusted":true},"cell_type":"code","source":"df.dtypes","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df[['primary_use','hour','month','site_id','building_id','wind_direction']] = df[['primary_use','hour','month','site_id','building_id','wind_direction']].astype('category')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"As disgussed before, it will be more convenient to analyse meter readings after dividing it to area. cons/sqft column is formed to contain this information. Before creating the that column, the units of the electric meter readings for site 0 is changed to kWh ([it was given in BTU](https://www.kaggle.com/c/ashrae-energy-prediction/discussion/119261))."},{"metadata":{"trusted":true,"_kg_hide-input":false},"cell_type":"code","source":"df['cons/sqft'] = df['meter_reading'] / df['square_feet']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"df.loc[(df['site_id'] == 0) & (df['meter'] == 'electricity'), 'meter_reading'] = df[(df['site_id'] == 0) & (df['meter'] == 'electricity')]['meter_reading'].apply(lambda x: x* 0.2931 )","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df['day'] = df.timestamp.dt.year","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df = reduce_mem_usage(df)\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<a id=\"subsection-a\"></a>\n## Analysis"},{"metadata":{"trusted":true},"cell_type":"code","source":"fig, axes = plt.subplots(1, 2, figsize=(12, 4),constrained_layout=True)\nfig.suptitle('Building Counts')\nsns.barplot(ax=axes[0],y=\"site_id\", x='building_id', data=df.groupby(['site_id'])['building_id'].nunique().reset_index())\naxes[0].set(xlabel = 'Building Count', ylabel = 'Site id')  \nsns.barplot(ax=axes[1],y=\"primary_use\", x='building_id', data=df.groupby(['primary_use'])['building_id'].nunique().reset_index())\naxes[1].set(xlabel = 'Building Count', ylabel = 'Primary Use')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"What is the distribution of energy consumption among the sites and primary usages ?"},{"metadata":{"trusted":true},"cell_type":"code","source":"fig, axes = plt.subplots(2, 2, figsize=(20, 16),constrained_layout=True)\nfig.suptitle('Energy Consumption for Primary Use')\nsns.boxplot(ax=axes[0, 0], y='primary_use', x='meter_reading', data=df, showfliers=True)\nsns.boxplot(ax=axes[0, 1], y='primary_use', x='meter_reading', data=df, showfliers=False)\nsns.boxplot(ax=axes[1, 0], y='primary_use', x='cons/sqft', data=df, showfliers=True)\nsns.boxplot(ax=axes[1, 1], y='primary_use', x='cons/sqft', data=df, showfliers=False);","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"fig, axes = plt.subplots(2, 2, figsize=(14, 16),constrained_layout=True)\nfig.suptitle('Energy Consumption for Sites')\nsns.boxplot(ax=axes[0, 0], y='site_id', x='meter_reading', data=df, showfliers=True)\nsns.boxplot(ax=axes[0, 1], y='site_id', x='meter_reading', data=df, showfliers=False)\nsns.boxplot(ax=axes[1, 0], y='site_id', x='cons/sqft', data=df, showfliers=True)\nsns.boxplot(ax=axes[1, 1], y='site_id', x='cons/sqft', data=df, showfliers=False);","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Highest enery consuming sites"},{"metadata":{"trusted":true},"cell_type":"code","source":"df['day'] = df.timestamp.dt.day","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df[df['meter']=='electricity'].groupby(['site_id','month','day','building_id'])['meter_reading'].sum().sort_values(ascending = False).reset_index().head(100)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df[df['meter']=='electricity'].groupby(['site_id','month','day','building_id'])['cons/sqft'].sum().sort_values(ascending = False).reset_index().head(100)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df.groupby(['site_id'])['meter_reading'].sum().sort_values(ascending = False).reset_index()\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<a id=\"subsection-o\"></a>\n### Removing Outliers"},{"metadata":{},"cell_type":"markdown","source":"As it can be seen from box plots site 13 and 6, building type education and entertainment have lot's of outliers."},{"metadata":{"trusted":true},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"total = 0\nprint('Outlier distribution in meter types')\nfor col in list(df['meter'].unique()):\n    r = np.percentile(df[df['meter'] == col]['cons/sqft'],75) + 1.5 * (np.percentile(df[df['meter'] == col]['cons/sqft'],75) - np.percentile(df[df['meter'] == col]['cons/sqft'],25))\n    print(f'''Percentage of outliers in {col} to all readings: { round(df[(df['meter'] == col) & (df['cons/sqft'] > r)].shape[0]/ df.shape[0],5)}''')\n    total +=  df[(df['meter'] == col) & (df['cons/sqft'] > r)].shape[0]\nprint(f'Total fraction of outliers {round(total / df.shape[0],5)}')\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"total = 0\nprint('Outlier distribution in building types')\nfor col in list(df['primary_use'].unique()):\n    r = np.percentile(df[df['primary_use'] == col]['cons/sqft'],75) + 1.5 * (np.percentile(df[df['primary_use'] == col]['cons/sqft'],75) - np.percentile(df[df['primary_use'] == col]['cons/sqft'],25))\n    print(f'''Percentage of outliers in {col} to all readings: { round(df[(df['primary_use'] == col) & (df['cons/sqft'] > r)].shape[0]/ df.shape[0],5)}''')\n    total +=  df[(df['primary_use'] == col) & (df['cons/sqft'] > r)].shape[0]\nprint(f'Total fraction of outliers {round(total / df.shape[0],5)}')\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"total = 0\nprint('Outlier distribution in building types')\nfor col in list(df['site_id'].unique()):\n    r = np.percentile(df[df['site_id'] == col]['cons/sqft'],75) + 1.5 * (np.percentile(df[df['site_id'] == col]['cons/sqft'],75) - np.percentile(df[df['site_id'] == col]['cons/sqft'],25))\n    print(f'''Percentage of outliers in {col} to all readings: { round(df[(df['site_id'] == col) & (df['cons/sqft'] > r)].shape[0]/ df.shape[0],5)}''')\n    total +=  df[(df['site_id'] == col) & (df['cons/sqft'] > r)].shape[0]\nprint(f'Total fraction of outliers {round(total / df.shape[0],5)}')\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df.groupby(['month','day','building_id','meter'])['meter_reading'].sum().sort_values(ascending = False).reset_index().head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df.groupby(['month','day','building_id','meter'])['meter_reading'].sum().sort_values(ascending = False).reset_index().head(100)['building_id'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df.groupby(['building_id','site_id','meter'])['meter_reading'].sum().sort_values(ascending = False).reset_index().head(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df.groupby(['building_id','site_id','meter'])['cons/sqft'].sum().sort_values(ascending = False).reset_index().head(10)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<a id=\"subsection-m\"></a>\n## Feature Selection & Preparation"},{"metadata":{},"cell_type":"markdown","source":"For different usage areas energy consumption may fluctuate significantly between weekdays and weekends. Weekend column is added to dataframe."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"df[\"weekday\"] = df.timestamp.dt.weekday \ndf.loc[df['weekday'].isin([5, 6]), 'Weekend'] = 1\ndf['Weekend'].fillna(0,inplace = True)\ndf['Weekend'] = df['Weekend'].astype('bool')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"<a id=\"section-five\"></a>\n# 5. Modeling"},{"metadata":{},"cell_type":"markdown","source":"Due to memory constrains modeling is carried in another notebook. Please see [ASHRAE - Energy Prediction2](https://www.kaggle.com/fatmanuranl/ashrae-energy-prediction2)"}],"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":4}