{"cells":[{"metadata":{"_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","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('/kaggle/input'):\n    for filename in filenames:\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":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nimport matplotlib.pyplot as plt\n%matplotlib inline\nimport seaborn as sns\nfrom time import time\nimport datetime\nimport gc\npd.set_option('display.max_columns',100)\npd.set_option('display.max_rows',1500)\npd.set_option('display.float_format', lambda x: '%.5f' % x)\nfrom sklearn.model_selection import train_test_split,KFold,GroupKFold\nfrom sklearn.preprocessing import LabelEncoder\nimport lightgbm as lgb\n\nfrom plotly.offline import init_notebook_mode,iplot,plot\nimport plotly.graph_objects as go\ninit_notebook_mode(connected=True)\nimport plotly.figure_factory as ff","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"metadata_dtype = {'site_id':\"uint8\",'building_id':'uint16','square_feet':'float32','year_built':'float32','floor_count':\"float16\"}\nmetadata = pd.read_csv(\"../input/ashrae-energy-prediction/building_metadata.csv\",dtype=metadata_dtype)\nmetadata.info(memory_usage='deep')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_dtype = {\"site_id\":\"uint8\"}\nweather_train = pd.read_csv(\"../input/ashrae-energy-prediction/weather_train.csv\",parse_dates=['timestamp'],dtype=weather_dtype)\nweather_test = pd.read_csv(\"../input/ashrae-energy-prediction/weather_test.csv\",parse_dates=['timestamp'],dtype=weather_dtype)\nprint (weather_train.info(memory_usage='deep'))\nprint (\"-------------------------------------\")\nprint (weather_test.info(memory_usage='deep'))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_dtype = {'meter':\"uint8\",'building_id':'uint16','meter_reading':\"float32\"}\ntrain = pd.read_csv(\"../input/ashrae-energy-prediction/train.csv\",parse_dates=['timestamp'],dtype=train_dtype)\ntest_dtype = {'meter':\"uint8\",'building_id':'uint16'}\ntest_cols_to_read = ['building_id','meter','timestamp']\ntest = pd.read_csv(\"../input/ashrae-energy-prediction/test.csv\",parse_dates=['timestamp'],usecols=test_cols_to_read,dtype=test_dtype)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"Submission = pd.DataFrame(test.index,columns=['row_id'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train.tail(20)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"metadata.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_test.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Missing Values"},{"metadata":{"trusted":true},"cell_type":"code","source":"missing_weather = pd.DataFrame(weather_train.isna().sum()/len(weather_train),columns=[\"Weather_Train_Missing_Pct\"])\nmissing_weather[\"Weather_Test_Missing_Pct\"] = weather_test.isna().sum()/len(weather_test)\nmissing_weather","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"1. precip_depth_1_hr variable has similar number of missing values in both train and test weather data.\n2. cloud_coverage variable has similar number of missing values in both train and test weather data.\n3. site_id and timestamp do not have missing values. Other variables have some missing values."},{"metadata":{"trusted":true},"cell_type":"code","source":"metadata.isna().sum()/len(metadata)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"metadata['floor_count_isNa'] = metadata['floor_count'].isna().astype('uint8')\nmetadata['year_built_isNa'] = metadata['year_built'].isna().astype('uint8')\n# Dropping floor_count variable as it has 75% missing values\nmetadata.drop('floor_count',axis=1,inplace=True)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"1. year_built also has large number of missing columns."},{"metadata":{"trusted":true},"cell_type":"code","source":"missing_train_test = pd.DataFrame(train.isna().sum()/len(train),columns=[\"Missing_Pct_Train\"])\nmissing_train_test[\"Missing_Pct_Test\"] = test.isna().sum()/len(test)\nmissing_train_test","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"1. No Missing values in train/test datasets."},{"metadata":{},"cell_type":"markdown","source":"### EDA- Train Data"},{"metadata":{"trusted":true},"cell_type":"code","source":"train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train.describe(include='all')\n# Data contains records from 1st Jan to 31st Dec of 2016.\n# Data has information about 1448 buildings.\n# Data has 4 meter types.\n# Some extremely high values in meter reading which can be explored further.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train['meter'].replace({0:\"Electricity\",1:\"ChilledWater\",2:\"Steam\",3:\"HotWater\"},inplace=True)\ntest['meter'].replace({0:\"Electricity\",1:\"ChilledWater\",2:\"Steam\",3:\"HotWater\"},inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"trace1 = go.Bar(x=train['meter'].unique(),y=train['meter'].value_counts().values,marker=dict(color=\"rgb(55, 83, 109)\"),text='train')\ntrace2 = go.Bar(x=test['meter'].unique(),y=test['meter'].value_counts().values,marker=dict(color=\"blue\"),text='test')\ndata=[trace1,trace2]\nlayout = go.Layout(title='Countplot of meter',xaxis=dict(title='Meter'),yaxis=dict(title='Count'),hovermode='closest')\nfigure = go.Figure(data=data,layout=layout)\niplot(figure)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print (\"There are {} unique Buildings in the training data\".format(train['building_id'].nunique()))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train['building_id'].value_counts(dropna=False).head(20)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train[train['building_id'] == 1094]['meter'].unique()\n# Like it is mentioned in the competition description, each building may or may not have all 4 meter Id codes.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train.groupby('meter')['meter_reading'].agg(['min','max','mean','median','count','std'])\n# We can see that Steam meter has some values that are very high maximum values, we have to explore further. \n# Minimum value for all 4 types of meter is 0.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"for df in [train, test]:\n    df['Month'] = df['timestamp'].dt.month.astype(\"uint8\")\n    df['DayOfMonth'] = df['timestamp'].dt.day.astype(\"uint8\")\n    df['DayOfWeek'] = df['timestamp'].dt.dayofweek.astype(\"uint8\")\n    df['Hour'] = df['timestamp'].dt.hour.astype(\"uint8\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train[['timestamp','meter_reading']].set_index('timestamp').resample(\"H\")['meter_reading'].mean().plot(kind='line',figsize=(10,6),label='Avg_Meter_by_Hour')\ntrain[['timestamp','meter_reading']].set_index('timestamp').resample(\"D\")['meter_reading'].mean().plot(kind='line',figsize=(10,6),label='Avg_Meter_by_Day')\nplt.legend()\nplt.xlabel(\"Timestamp\")\nplt.ylabel(\"Average Meter Reading\")\nplt.title(\"Graph of Average Meter Reading\")\n# We can see some surprising trends here, the meter reading is low from Jan to March, however from March it shoots up until mid June, then it almost reaches 0\n# till Mid november and then briefly shoots up again and then drops to zero. ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"meter_Electricity = train[train['meter'] == \"Electricity\"]\nmeter_Electricity[['timestamp','meter_reading']].set_index('timestamp').resample(\"H\")['meter_reading'].mean().plot(kind='line',figsize=(10,6),label='Avg_Meter_by_Hour')\nmeter_Electricity[['timestamp','meter_reading']].set_index('timestamp').resample(\"D\")['meter_reading'].mean().plot(kind='line',figsize=(10,6),label='Avg_Meter_by_Day')\nplt.legend()\nplt.xlabel(\"Timestamp\")\nplt.ylabel(\"Average Meter Reading\")\nplt.title(\"Graph of Average Meter Readingfor Electricity Meter\")\n# The increase and decreasing trend can be attributed to the usage during the weekdays and during the weekends when it drops. ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"meter_ChilledWater = train[train['meter'] == \"ChilledWater\"]\nmeter_ChilledWater[['timestamp','meter_reading']].set_index('timestamp').resample(\"H\")['meter_reading'].mean().plot(kind='line',figsize=(10,6),label='Avg_Meter_by_Hour')\nmeter_ChilledWater[['timestamp','meter_reading']].set_index('timestamp').resample(\"D\")['meter_reading'].mean().plot(kind='line',figsize=(10,6),label='Avg_Meter_by_Day')\nplt.legend()\nplt.xlabel(\"Timestamp\")\nplt.ylabel(\"Average Meter Reading\")\nplt.title(\"Graph of Average Meter Readingfor ChilledWater Meter\")\n# Consumption gradually increases and reaches its peak during septembet to November months. ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"meter_Steam = train[train['meter'] == \"Steam\"]\nmeter_Steam[['timestamp','meter_reading']].set_index('timestamp').resample(\"H\")['meter_reading'].mean().plot(kind='line',figsize=(10,6),label='Avg_Meter_by_Hour')\nmeter_Steam[['timestamp','meter_reading']].set_index('timestamp').resample(\"D\")['meter_reading'].mean().plot(kind='line',figsize=(10,6),label='Avg_Meter_by_Day')\nplt.legend()\nplt.xlabel(\"Timestamp\")\nplt.ylabel(\"Average Meter Reading\")\nplt.title(\"Graph of Average Meter Readingfor Steam Meter\")\n# This is almost similar to that of the overall trend. ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"meter_HotWater = train[train['meter'] == \"HotWater\"]\nmeter_HotWater[['timestamp','meter_reading']].set_index('timestamp').resample(\"H\")['meter_reading'].mean().plot(kind='line',figsize=(10,6),label='Avg_Meter_by_Hour')\nmeter_HotWater[['timestamp','meter_reading']].set_index('timestamp').resample(\"D\")['meter_reading'].mean().plot(kind='line',figsize=(10,6),label='Avg_Meter_by_Day')\nplt.legend()\nplt.xlabel(\"Timestamp\")\nplt.ylabel(\"Average Meter Reading\")\nplt.title(\"Graph of Average Meter Readingfor HotWater Meter\")\n# Hot water meter reading is high during the winter months and reduces during the summer months. ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train.groupby(['meter','Month'])['meter_reading'].agg(['max','mean','median','count','std'])\n# We can see that only Steam meter has very high meter_reading values as compared to other types of meters.\n# We can see that the average electricity meter_reading does not vary much across the months.\n# Average Hot Water meter_reading is relatively less from April to October Months.\n# Average Steam meter_reading is way higher from March to June as compared to the other months.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train.groupby(['meter','DayOfWeek'])['meter_reading'].agg(['max','mean','median','count','std'])\n# Average meter_reading of Steam type of meter is higher as compared to the other meter types.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train.groupby('building_id')['meter_reading'].agg(['count','min','max','mean','median','std'])\n# We can see that the values for building number 1099 are exceptionally high. These can be safely considered as outliers and can be dropped.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train[train['building_id'] == 1099]['meter_reading'].describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.hist(train[train['building_id'] == 1099]['meter_reading'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train[(train['building_id'] == 1099) & (train['meter'] == \"Steam\")]['meter_reading'].describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"iqr = train[train['building_id'] == 1099]['meter_reading'].quantile(0.75)-train[train['building_id'] == 1099]['meter_reading'].quantile(0.25)\nq3 = train[train['building_id'] == 1099]['meter_reading'].quantile(0.75)\nq1 = train[train['building_id'] == 1099]['meter_reading'].quantile(0.25)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"outlier_range_building_1099 = (q1-1.5*iqr,q3+1.5*iqr)\noutlier_range_building_1099\n# We can see that any value above 16739 can be considered as outlier","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Dependent Variable"},{"metadata":{"trusted":true},"cell_type":"code","source":"train['meter_reading'].describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.distplot(np.log1p(train['meter_reading']),kde=False)\nplt.title(\"Distribution of Log of Meter Reading Variable\")\n# Lot of 0 values as can be seen from the distribution","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.boxplot(np.log1p(train[train['meter'] == \"Electricity\"]['meter_reading']))\nplt.title(\"Boxplot of Meter Reading Variable for the Meter Type: Electricity\")\n# We can see a few outliers here.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.boxplot(np.log1p(train[train['meter'] == \"ChilledWater\"]['meter_reading']))\nplt.title(\"Boxplot of Meter Reading Variable for the Meter Type: Electricity\")\n# Not many outliers here. ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.boxplot(np.log1p(train[train['meter'] == \"HotWater\"]['meter_reading']))\nplt.title(\"Boxplot of Meter Reading Variable for the Meter Type: Electricity\")\n# We can see a single value that is way off from the rest. ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.boxplot(np.log1p(train[train['meter'] == \"Steam\"]['meter_reading']))\nplt.title(\"Boxplot of Meter Reading Variable for the Meter Type: Electricity\") ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.distplot(np.log1p(train[train['meter'] == \"Electricity\"]['meter_reading']),kde=False)\nplt.title(\"Distribution of Meter Reading per MeterID code: Electricity\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.distplot(np.log1p(train[train['meter'] == \"ChilledWater\"]['meter_reading']),kde=False)\nplt.title(\"Distribution of Meter Reading per MeterID code: Chilledwater\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.distplot(np.log1p(train[train['meter'] == \"Steam\"]['meter_reading']),kde=False)\nplt.title(\"Distribution of Meter Reading per MeterID code: Steam\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.distplot(np.log1p(train[train['meter'] == \"HotWater\"]['meter_reading']),kde=False)\nplt.title(\"Distribution of Meter Reading per MeterID code: Hotwater\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Metadata EDA"},{"metadata":{"trusted":true},"cell_type":"code","source":"metadata.info()\n# Missing values in year_built and floor_count variables.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"metadata.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cols = ['site_id','primary_use','building_id','year_built']\nfor col in cols:\n    print (\"Number of Unique Values in the {} column are:\".format(col),metadata[col].nunique())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cols = ['site_id','primary_use','year_built']\nfor col in cols:\n    print (\"Unique Values in the {} column are:\".format(col),metadata[col].unique())\n    print (\"\\n\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"trace1 = go.Bar(x=metadata['site_id'].unique(),y=metadata['site_id'].value_counts().values,marker=dict(color=\"rgb(55, 83, 109)\"))\ndata=[trace1]\nlayout = go.Layout(title='Countplot of site_id variable',xaxis=dict(title='site_id'),yaxis=dict(title='Count'),hovermode='closest')\nfigure = go.Figure(data=data,layout=layout)\niplot(figure)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"trace1 = go.Bar(x=metadata['primary_use'].unique(),y=metadata['primary_use'].value_counts().values,marker=dict(color=\"rgb(55, 83, 109)\"))\ndata=[trace1]\nlayout = go.Layout(title='Countplot of primary_use variable',xaxis=dict(title='primary_use'),yaxis=dict(title='Count'),hovermode='closest')\nfigure = go.Figure(data=data,layout=layout)\niplot(figure)\n# Education, Office, Entertainment/Public Assembly, Public Services, Lodging/Residential form the bulk of Primary Use","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"metadata['primary_use'].value_counts(normalize=True)\n# Since there are a lot of categories which form a meager percentage of the whole , it makes sense to combine them. ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"metadata['square_feet'].describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.boxplot(metadata['square_feet'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"metadata['square_feet'] = np.log1p(metadata['square_feet'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.distplot(metadata['square_feet'])\nplt.title(\"Distribution of Square Feet variable of Metadata Table\")\nplt.xlabel(\"Area in Square Feet\")\nplt.ylabel(\"Frequency\")\n# Looks like a normal distribution distribution","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.boxplot(metadata['square_feet'])\nplt.title(\"Box Plot of Square Feet Variable\")\n# There are a few outliers visible","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"metadata.groupby('primary_use')['square_feet'].agg(['mean','median','count']).sort_values(by='count')\n# Parking has the highest average are although the count is less.\n# Education has the highest count as can be seen in the countplot above.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"trace1 = go.Bar(x=metadata['year_built'].unique(),y=metadata['year_built'].value_counts().values,marker=dict(color=\"rgb(55, 83, 109)\"))\ndata=[trace1]\nlayout = go.Layout(title='Countplot of year_built variable',xaxis=dict(title='year_built'),yaxis=dict(title='Count'),hovermode='closest')\nfigure = go.Figure(data=data,layout=layout)\niplot(figure)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"metadata.groupby('primary_use')['square_feet'].agg(['count','mean','median']).sort_values(by='count')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"metadata.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"metadata['year_built'].fillna(1976, inplace=True)\nmetadata['year_built'] = metadata['year_built'].astype('int16')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Weather Data (Train)"},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train.info(memory_usage='deep')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train.isna().sum()/len(weather_train)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train[['air_temperature','cloud_coverage','dew_temperature','precip_depth_1_hr','sea_level_pressure','wind_speed']].describe()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"1. Missing values in air_temperature, cloud_coverage, dew_temperature, precip_depth_1_hr, sea_level_pressure, wind_speed variables\n2. There are negative values in air_temperature, dew_temperature and precip_depth_1_hr variables.\n3. Looks like there are outliers in precip_depth_1_hr variable (can be guessed from Max value).\n4. min value of wind_speed as 0 does not make any sense."},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train['timestamp'].describe()\n# This data is from 1st Jan to 31st Dec 2016, similar to the timestamp of the training data","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cols = ['air_temperature','cloud_coverage','dew_temperature','precip_depth_1_hr','sea_level_pressure','wind_speed']\nfor ind,col in enumerate(weather_train[cols]):\n    plt.figure(ind)\n    sns.distplot(weather_train[col].dropna())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"1. Distribution of sea_level_pressure looks like a normal distribution.\n2. Lot of 0 values in precip_depth_1_hr variable.\n3. Wind_Speed distribution looks like positively skewed.\n4. Dew Temperature looks like a Negatively skewed distribution.\n5. Cloud_Coverage takes distinct values unlike these other variables."},{"metadata":{"trusted":true},"cell_type":"code","source":"cols = ['air_temperature','cloud_coverage','dew_temperature','precip_depth_1_hr','sea_level_pressure','wind_speed']\nfor ind,col in enumerate(weather_train[cols]):\n    plt.figure(ind)\n    sns.boxplot(weather_train[col].dropna())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Weather_Test data"},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_test.info(memory_usage='deep')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_test['timestamp'].describe()\n# The time duration is similar to the test dataset.","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def fill_weather_dataset(weather_df):\n    \n    # Find Missing Dates\n    time_format = \"%Y-%m-%d %H:%M:%S\"\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    return weather_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train = fill_weather_dataset(weather_train)\nweather_test = fill_weather_dataset(weather_test)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"for df in [weather_train,weather_test]:\n    df['air_temperature'] = df['air_temperature'].astype('float32')\n    df['cloud_coverage'] = df['cloud_coverage'].astype('float16')\n    df['dew_temperature'] = df['dew_temperature'].astype('float16')\n    df['precip_depth_1_hr'] = df['precip_depth_1_hr'].astype('float32')\n    df['sea_level_pressure'] = df['sea_level_pressure'].astype('float32')\n    df['wind_direction'] = df['wind_direction'].astype('float32')\n    df['wind_speed'] = df['wind_speed'].astype('float16')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Merging Data"},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\ntrain = pd.merge(train,metadata,on='building_id',how='left')\ntest  = pd.merge(test,metadata,on='building_id',how='left')\nprint (\"Training Data Shape {}\".format(train.shape))\nprint (\"Testing Data Shape {}\".format(test.shape))\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\ntrain = pd.merge(train,weather_train,on=['site_id','timestamp'],how='left')\ntest  = pd.merge(test,weather_test,on=['site_id','timestamp'],how='left')\nprint (\"Training Data Shape {}\".format(train.shape))\nprint (\"Testing Data Shape {}\".format(test.shape))\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"for df in [train,test]:\n    df['square_feet'] = df['square_feet'].astype('float16')\n    df['Age'] = df['timestamp'].dt.year - df['year_built']\n    df['Age_isNa'] = df['year_built_isNa']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train.drop('year_built_isNa',axis=1,inplace=True)\ntest.drop('year_built_isNa',axis=1,inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# As per the discussion in the following thread, https://www.kaggle.com/c/ashrae-energy-prediction/discussion/117083, there is some discrepancy in the meter_readings for different ste_id's and buildings. It makes sense to delete them\nidx_to_drop = list((train[(train['site_id'] == 0) & (train['timestamp'] < \"2016-05-21 00:00:00\")]).index)\nprint (len(idx_to_drop))\ntrain.drop(idx_to_drop,axis='rows',inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# dropping all the electricity meter readings that are 0, after considering them as anomalies.\nidx_to_drop = list(train[(train['meter'] == \"Electricity\") & (train['meter_reading'] == 0)].index)\nprint(len(idx_to_drop))\ntrain.drop(idx_to_drop,axis='rows',inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"idx_to_drop = list((train[(train['building_id']==1099)&(train['meter_reading'] > 30000)&(train['meter'] == \"Steam\")]).index)\nprint (len(idx_to_drop))\ntrain.drop(idx_to_drop,axis='rows',inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Converting the dependent variable to logarithmic scale\ntrain['meter_reading'] = np.log1p(train['meter_reading'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\nmean_meter_reading_per_building = train.groupby('building_id')['meter_reading'].mean()\ntrain['mean_meter_reading_per_building'] = train['building_id'].map(mean_meter_reading_per_building)\nmedian_meter_reading_per_building = train.groupby('building_id')['meter_reading'].median()\ntrain['median_meter_reading_per_building'] = train['building_id'].map(median_meter_reading_per_building)\nstd_meter_reading_per_building = train.groupby('building_id')['meter_reading'].std()\ntrain['std_meter_reading_per_building'] = train['building_id'].map(std_meter_reading_per_building)\n\nmean_meter_reading_per_dayofweek = train.groupby('DayOfWeek')['meter_reading'].mean()\ntrain['mean_meter_reading_per_dayofweek'] = train['DayOfWeek'].map(mean_meter_reading_per_dayofweek)\nmedian_meter_reading_per_dayofweek = train.groupby('DayOfWeek')['meter_reading'].median()\ntrain['median_meter_reading_per_dayofweek'] = train['DayOfWeek'].map(median_meter_reading_per_dayofweek)\nstd_meter_reading_per_dayofweek = train.groupby('DayOfWeek')['meter_reading'].std()\ntrain['std_meter_reading_per_dayofweek'] = train['DayOfWeek'].map(std_meter_reading_per_dayofweek)\n\n\nmean_meter_reading_per_meter = train.groupby('meter')['meter_reading'].mean()\ntrain['mean_meter_reading_per_meter'] = train['meter'].map(mean_meter_reading_per_meter)\nmedian_meter_reading_per_meter = train.groupby('meter')['meter_reading'].median()\ntrain['median_meter_reading_per_meter'] = train['meter'].map(median_meter_reading_per_meter)\nstd_meter_reading_per_meter = train.groupby('meter')['meter_reading'].std()\ntrain['std_meter_reading_per_meter'] = train['meter'].map(std_meter_reading_per_meter)\n\n\nmean_meter_reading_per_month = train.groupby('Month')['meter_reading'].mean()\ntrain['mean_meter_reading_per_month'] = train['Month'].map(mean_meter_reading_per_month)\nmedian_meter_reading_per_month = train.groupby('Month')['meter_reading'].median()\ntrain['median_meter_reading_per_month'] = train['Month'].map(median_meter_reading_per_month)\nstd_meter_reading_per_month = train.groupby('Month')['meter_reading'].std()\ntrain['std_meter_reading_per_month'] = train['Month'].map(std_meter_reading_per_month)\n\n\ntest['mean_meter_reading_per_building'] = test['building_id'].map(mean_meter_reading_per_building)\ntest['median_meter_reading_per_building'] = test['building_id'].map(median_meter_reading_per_building)\ntest['std_meter_reading_per_building'] = test['building_id'].map(std_meter_reading_per_building)\n\ntest['mean_meter_reading_per_dayofweek'] = test['DayOfWeek'].map(mean_meter_reading_per_dayofweek)\ntest['median_meter_reading_per_dayofweek'] = test['DayOfWeek'].map(median_meter_reading_per_dayofweek)\ntest['std_meter_reading_per_dayofweek'] = test['DayOfWeek'].map(std_meter_reading_per_dayofweek)\n\ntest['mean_meter_reading_per_meter'] = test['meter'].map(mean_meter_reading_per_meter)\ntest['median_meter_reading_per_meter'] = test['meter'].map(median_meter_reading_per_meter)\ntest['std_meter_reading_per_meter'] = test['meter'].map(std_meter_reading_per_meter)\n\ntest['mean_meter_reading_per_month'] = test['Month'].map(mean_meter_reading_per_month)\ntest['median_meter_reading_per_month'] = test['Month'].map(median_meter_reading_per_month)\ntest['std_meter_reading_per_month'] = test['Month'].map(std_meter_reading_per_month)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\nfor df in [train, test]:\n    df['mean_meter_reading_per_building'] = df['mean_meter_reading_per_building'].astype(\"float16\")\n    df['median_meter_reading_per_building'] = df['mean_meter_reading_per_building'].astype(\"float16\")\n    df['std_meter_reading_per_building'] = df['std_meter_reading_per_building'].astype(\"float16\")\n    \n    df['mean_meter_reading_per_meter'] = df['mean_meter_reading_per_meter'].astype(\"float16\")\n    df['median_meter_reading_per_meter'] = df['median_meter_reading_per_meter'].astype(\"float16\")\n    df['std_meter_reading_per_meter'] = df['std_meter_reading_per_meter'].astype(\"float16\")\n    \n    df['mean_meter_reading_per_dayofweek'] = df['mean_meter_reading_per_dayofweek'].astype(\"float16\")\n    df['median_meter_reading_per_dayofweek'] = df['median_meter_reading_per_dayofweek'].astype(\"float16\")\n    df['std_meter_reading_per_dayofweek'] = df['std_meter_reading_per_dayofweek'].astype(\"float16\")\n    \n    df['mean_meter_reading_per_month'] = df['mean_meter_reading_per_month'].astype(\"float16\")\n    df['median_meter_reading_per_month'] = df['median_meter_reading_per_month'].astype(\"float16\")\n    df['std_meter_reading_per_month'] = df['std_meter_reading_per_month'].astype(\"float16\")\n    df['Age'] = df['Age'].astype('uint8')\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train.drop(['timestamp','year_built'],axis=1,inplace=True)\ntest.drop(['timestamp','year_built'],axis=1,inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print (train.shape, test.shape)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\nle = LabelEncoder()\n\ntrain['meter']= le.fit_transform(train['meter']).astype(\"uint8\")\ntest['meter']= le.fit_transform(test['meter']).astype(\"uint8\")\ntrain['primary_use']= le.fit_transform(train['primary_use']).astype(\"uint8\")\ntest['primary_use']= le.fit_transform(test['primary_use']).astype(\"uint8\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\n# Let's check the correlation between the variables and eliminate the one's that have high correlation\n# Threshold for removing correlated variables\nthreshold = 0.9\n\n# Absolute value correlation matrix\ncorr_matrix = train.corr().abs()\ncorr_matrix.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Upper triangle of correlations\nupper = corr_matrix.where(np.triu(np.ones(corr_matrix.shape), k=1).astype(np.bool))\nupper","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Select columns with correlations above threshold\nto_drop = [column for column in upper.columns if any(upper[column] > threshold)]\n\nprint('There are %d columns to remove.' % (len(to_drop)))\nprint (\"Following columns can be dropped {}\".format(to_drop))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train.drop(to_drop,axis=1,inplace=True)\ntest.drop(to_drop,axis=1,inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"y = train['meter_reading']\ntrain.drop('meter_reading',axis=1,inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"categorical_cols = ['building_id','Month','meter','Hour','primary_use','DayOfWeek','DayOfMonth','floor_count_isNa']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"params = {'feature_fraction': 0.8,\n          'bagging_fraction': 0.5,\n          \"bagging_freq\": 5,\n          'objective': 'regression',\n          'max_depth': 11,\n          'learning_rate': 0.05,\n          \"boosting_type\": \"gbdt\",\n          \"bagging_seed\": 11,\n          \"metric\": 'rmse',\n          \"verbosity\": -1,\n          'reg_alpha': 0.5,\n          'reg_lambda': 0.5,\n          'random_state': 47,\n          \"num_leaves\": 31}\n\nkf = KFold(n_splits=3)\nmodels = []\nfor train_index,test_index in kf.split(train):\n    train_features = train.iloc[train_index]\n    train_target = y.iloc[train_index]\n    \n    test_features = train.iloc[test_index]\n    test_target = y.iloc[test_index]\n    \n    d_training = lgb.Dataset(train_features, label=train_target,categorical_feature=categorical_cols, free_raw_data=False)\n    d_test = lgb.Dataset(test_features, label=test_target,categorical_feature=categorical_cols, free_raw_data=False)\n    \n    model = lgb.train(params, train_set=d_training, num_boost_round=2000, valid_sets=[d_training,d_test], verbose_eval=100, early_stopping_rounds=50)\n    models.append(model)\n    gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ser1 = pd.DataFrame(models[0].feature_importance(),train.columns,columns=['Importance']).sort_values(by='Importance')\nser1['Importance'].plot(kind='bar',figsize=(10,6))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ser2 = pd.DataFrame(models[1].feature_importance(),train.columns,columns=['Importance']).sort_values(by='Importance')\nser2['Importance'].plot(kind='bar',figsize=(10,6))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"ser3 = pd.DataFrame(models[2].feature_importance(),train.columns,columns=['Importance']).sort_values(by='Importance')\nser3['Importance'].plot(kind='bar',figsize=(10,6))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del train_features, train_target, test_features, test_target, d_training, d_test, weather_train, weather_test, metadata","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"stepsize = 500000\nresults = np.zeros(test.shape[0])\nfor model in models:\n  predictions = []\n  for i in range(0, test.shape[0], stepsize):\n    predictions.append(np.expm1(model.predict(test.loc[i:i+stepsize-1,:], num_iteration=model.best_iteration)))\n  results += (1 / len(models)) * np.concatenate(predictions, axis=0)\n  del(model)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\nSubmission['meter_reading'] = results\nSubmission['meter_reading'].clip(lower=0,upper=None,inplace=True)\nSubmission.to_csv(\"Normal.csv\",index=None)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"Submission.head(20)","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.6.6"}},"nbformat":4,"nbformat_minor":1}