{"cells":[{"metadata":{},"cell_type":"markdown","source":"# Objective\n\n* Perform a exploratory data analysis and a baseline model for ASHRAE - Great Energy Predictor III competition"},{"metadata":{},"cell_type":"markdown","source":"# Description of the problem\n\nEnergy savings is one of the important area of focus our current world. Energy savings has two key elements:\n\n* Forecasting future energy usage without improvements\n* Forecasting energy use after a specific set of improvements have been implemented\n\nIn this competition, you’ll develop accurate models of metered building energy usage in the following areas: chilled water, electric, hot water, and steam meters. The data comes from over 1,000 buildings over a three-year timeframe. With better estimates of these energy-saving investments, large scale investors and financial institutions will be more inclined to invest in this area to enable progress in building efficiencies."},{"metadata":{},"cell_type":"markdown","source":"# Libraries"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Libraries\nimport numpy as np\nimport pandas as pd\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport lightgbm as lgb\nimport warnings\nwarnings.filterwarnings('ignore')\npd.set_option('display.max_columns', 1000)\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Exploring train and test sets"},{"metadata":{"trusted":false},"cell_type":"code","source":"print('Reading train set...')\ntrain = pd.read_csv('/kaggle/input/ashrae-energy-prediction/train.csv')\nprint('Reading test set...')\ntest = pd.read_csv('/kaggle/input/ashrae-energy-prediction/test.csv')\nprint('Train set has {} rows and {} columns'.format(train.shape[0], train.shape[1]))\nprint('Test set has {} rows and {} columns'.format(test.shape[0], test.shape[1]))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"This dataset have a lot of rows to process in memory. Let's use a memory reduction function to handle it better."},{"metadata":{"trusted":false},"cell_type":"code","source":"def 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":{"scrolled":false,"trusted":false},"cell_type":"code","source":"train = reduce_mem_usage(train)\ntest = reduce_mem_usage(test)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"train.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"test.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We have no missing values in our train and test set."},{"metadata":{},"cell_type":"markdown","source":"Here is a description of each feature:\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."},{"metadata":{},"cell_type":"markdown","source":"We have no missing values in test set. We have the same max for the building_id in the train and test.\n\nEach building_id combine with each meter category is a time series. In other words we have a multi time series problem. Let's calculate first how many time series we have in our training and test set."},{"metadata":{"trusted":false},"cell_type":"code","source":"train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"print('We have {} time series in our training set'.format((train['building_id'].astype(str) + train['meter'].astype(str)).nunique()))\nprint('We have {} time series in our test set'.format((test['building_id'].astype(str) + test['meter'].astype(str)).nunique()))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We also need to check the consistency of this time series. In other words let's check how many time series are in the training set and also are in test set. On the other hand let's calculate how many of them are in the test set but not in the training set."},{"metadata":{"trusted":false},"cell_type":"code","source":"train_series = list((train['building_id'].astype(str) + train['meter'].astype(str)).unique())\ntest_series = list((test['building_id'].astype(str) + test['meter'].astype(str)).unique())\nprint('Number of series that are in the training set and are also contained in the test set {}'.format(len([x for x in train_series if x in test_series])))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Great!, we have consistency. The processing is very slow. Let's check the distribution of each meter (there are 4) and then split the training set in 4 parts to explore faster (maybee it's a good idea to make 4 models)."},{"metadata":{"trusted":false},"cell_type":"code","source":"def plot_count(df, col):\n    total = len(df)\n    plt.figure(figsize = (12,8))\n    plot_me = sns.countplot(df[col])\n    plot_me.set_xlabel('{} type'.format(col), fontsize = 16)\n    plot_me.set_ylabel('frequency', fontsize = 16)\n    for p in plot_me.patches:\n        height = p.get_height()\n        plot_me.text(p.get_x()+p.get_width()/2.,\n                height + 3,\n                '{:1.2f}%'.format(height/total*100),\n                ha=\"center\", fontsize=15)\n        \nplot_count(train, 'meter')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"* 59.66% of the observations correspond to the electricity meter.\n\n* 20.69% of the observations correspond to the chilled water meter.\n\n* 20.69% of the observations correspond to the steam meter.\n\n* 20.69% of the observations correspond to the hotwater meter."},{"metadata":{},"cell_type":"markdown","source":"Let's check the test distribution. Should be almost the same."},{"metadata":{"trusted":false},"cell_type":"code","source":"plot_count(test, 'meter')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We can see some differences. Let's divide the dataset in 4 sets.\n\n* It's possible that not all of the time series have records for all the dates.\n* It's possible that not all of the time series have records for all the hours in a specific date.\n* It's possible that not all of the time series have the same time window (all start and end at the same date)."},{"metadata":{"trusted":false},"cell_type":"code","source":"train['timestamp'] = pd.to_datetime(train['timestamp'])\ntest['timestamp'] = pd.to_datetime(test['timestamp'])\ntrain_el = train[train['meter']==0]\ntrain_ch = train[train['meter']==1]\ntrain_st = train[train['meter']==2]\ntrain_ho = train[train['meter']==3]\ntest_el = test[test['meter']==0]\ntest_ch = test[test['meter']==1]\ntest_st = test[test['meter']==2]\ntest_ho = test[test['meter']==3]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Timestamp feature\n\n* Let's start exploring the timestamp feature for each meter type"},{"metadata":{},"cell_type":"markdown","source":"# Electricity Meter"},{"metadata":{"trusted":false},"cell_type":"code","source":"# count the number of building for each timestamp for electicity meter\ndef plot_time_freq(df, name = 'electricity', se = 'train'):\n    print('We have {} series'.format(df['building_id'].nunique()))\n    print('Min date: ', df.timestamp.min())\n    print('Max date: ', df.timestamp.max())\n    print('Time behaviour for {} meter for the {} set'.format(name, se))\n    df['date'] = df['timestamp'].dt.date \n    df['week'] = df['timestamp'].dt.week\n    df['dayofmonth'] = df['timestamp'].dt.day\n    df['month'] = df['timestamp'].dt.month\n    df['dayofweek'] = df['timestamp'].dt.dayofweek\n    df['hour'] = df['timestamp'].dt.hour\n    tmp1 = df.groupby(['date'])['building_id'].count().reset_index().rename(columns = {'building_id': 'frequency'})\n    tmp2 = df.groupby(['week'])['building_id'].count().reset_index().rename(columns = {'building_id': 'frequency'})\n    tmp3 = df.groupby(['dayofmonth'])['building_id'].count().reset_index().rename(columns = {'building_id': 'frequency'})\n    tmp4 = df.groupby(['hour'])['building_id'].count().reset_index().rename(columns = {'building_id': 'frequency'})\n    tmp5 = df.groupby(['month'])['building_id'].count().reset_index().rename(columns = {'building_id': 'frequency'})\n    tmp6 = df.groupby(['dayofweek'])['building_id'].count().reset_index().rename(columns = {'building_id': 'frequency'})\n    fig, (ax1, ax2, ax3, ax4, ax5, ax6) = plt.subplots(6, 1, figsize = (12, 12))\n    sns.lineplot(tmp1['date'], tmp1['frequency'], ax = ax1)\n    ax1.set_title('Date Frequency')\n    ax1.set_xlabel('Date', fontsize = 10)\n    ax1.set_ylabel('Frequency', fontsize = 10)\n    sns.lineplot(tmp2['week'], tmp2['frequency'], ax = ax2)\n    ax2.set_title('Week Frequency')\n    ax2.set_xlabel('Week', fontsize = 10)\n    ax2.set_ylabel('Frequency', fontsize = 10)\n    sns.lineplot(tmp3['dayofmonth'], tmp3['frequency'], ax = ax3)\n    ax3.set_title('Day of month frequency')\n    ax3.set_xlabel('Day of month', fontsize = 10)\n    ax3.set_ylabel('Frequency', fontsize = 10)\n    sns.lineplot(tmp4['hour'], tmp4['frequency'], ax = ax4)\n    ax4.set_title('Hour frequency')\n    ax4.set_xlabel('Hour', fontsize = 10)\n    ax4.set_ylabel('Frequency', fontsize = 10)\n    sns.lineplot(tmp5['month'], tmp5['frequency'], ax = ax5)\n    ax5.set_title('Month frequency')\n    ax5.set_xlabel('Month', fontsize = 10)\n    ax5.set_ylabel('Frequency', fontsize = 10)\n    sns.lineplot(tmp6['dayofweek'], tmp6['frequency'], ax = ax6)\n    ax6.set_title('Day of week frequency')\n    ax6.set_xlabel('Day of week', fontsize = 10)\n    ax6.set_ylabel('Frequency', fontsize = 10)\n    plt.tight_layout()\n    plt.show()\n\nplot_time_freq(train_el, 'electricity', 'train')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"* We have data for one entire year (2016)\n* We can see some strange decending spikes in Frebuary, March, September, November and Decemeber\n* We can see a stable day of month frequency\n* We can see two peaks in the hour plot, 3-5 and 22-1, 15 and 1 are the lowest (1 is very strange)\n* We can see a lower frequency in Frebuary and March. (Frebuary have 28 days)\n* We can see two spikes for day of week 4 and 5 (Thursday and Friday)\n\nThere should be an explanation for this :), no idea why haha\n\nLet's check the test set"},{"metadata":{"scrolled":false,"trusted":false},"cell_type":"code","source":"plot_time_freq(test_el, 'electricity', 'test')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"* Date and hour frequency are constant."},{"metadata":{"trusted":false},"cell_type":"code","source":"# let's check a month, in this case Frebuary and March\ndef build_sw(df, cols, p_cols, value):\n    sw = df.groupby(cols)['meter'].count().reset_index()\n    sw1 = sw[sw[p_cols]==value]\n    plt.figure(figsize = (10,8))\n    plt.scatter(sw1[cols[2]], sw1[cols[0]])\n    plt.title('Observation for each serie for {} {}'.format(p_cols, value))\n    plt.show()\nbuild_sw(train_el, ['building_id', 'month', 'dayofmonth'], 'month', 2)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Eureka!, top building ids dont have records between day 11 till 29."},{"metadata":{"scrolled":false,"trusted":false},"cell_type":"code","source":"build_sw(train_el, ['building_id', 'month', 'dayofmonth'], 'month', 3)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"That's why we have those strange decending spikes in Frebuary, and March\n\nWe confirm our first hypothesis:\n\nNot all of the time series have records for all the dates in the electricity meter in the training set."},{"metadata":{"scrolled":false,"trusted":false},"cell_type":"code","source":"def check_hour(df):\n    tmp = df.groupby(['building_id', 'date'])['meter'].count().reset_index()\n    return tmp[tmp['meter']!=24].iloc[::10].head(10)\ncheck_hour(train_el)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"check_hour(test_el)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We can also see that some series dont have observations for all the hours in a specific date. But in the test set i could not find any!!!. \n\nIt's a good idea to predict the meter_reading for the series that have missing records?? And then use those prediction in the final training. We need to explore further. Leave your comments :))\n\nLet's check the start date for each series (we have 1413 series for the electricity meter)."},{"metadata":{"trusted":false},"cell_type":"code","source":"def start_date(df):\n    b_id = []\n    min_date = []\n    for i in list(df['building_id'].unique()):\n        b_id.append(i)\n        min_date.append(df[df['building_id']==i]['date'].min())\n    tmp = pd.DataFrame({'building_id': b_id, 'min_date': min_date})\n    tmp['min_date'] = tmp['min_date'].astype(str)\n    print('There are {} series that start after 2016-01-01'.format(tmp[tmp['min_date']!='2016-01-01'].shape[0]))\nstart_date(train_el)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Let's check the max date. We already know that all the series that are in the training set are in the test set so we need need to check for the max date in the test set."},{"metadata":{"trusted":false},"cell_type":"code","source":"def end_date(df):\n    b_id = []\n    max_date = []\n    for i in list(df['building_id'].unique()):\n        b_id.append(i)\n        max_date.append(df[df['building_id']==i]['date'].max())\n    tmp = pd.DataFrame({'building_id': b_id, 'max_date': max_date})\n    tmp['max_date'] = tmp['max_date'].astype(str)\n    print('There are {} series that finish before 2018-12-31'.format(tmp[tmp['max_date']!='2018-12-31'].shape[0]))\nend_date(test_el)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"So all the series have observations in the last date of the test set\n\nLet's build a function that can check the time frequency consistency of the dataframes"},{"metadata":{"trusted":false},"cell_type":"code","source":"def check_series(df, n_years = 1):\n    n_day_month = {1 : 31, 2: 28, 3: 31, 4: 30, 5: 31, 6: 30, 7: 31, 8: 31, 9:30, 10:31, 11:30, 12:31}\n    df1 = df.groupby('month')['meter'].count().reset_index()\n    df1['n_days'] = df1['month'].map(n_day_month)\n    df1['meter_'] = df1['n_days'] * df.building_id.nunique() * n_years * 24\n    df1['missing_observations_%'] = 100 - (df1['meter'] / df1['meter_']) * 100\n    df1['missing_observations_%'] = df1['missing_observations_%'].astype(str) + '%'\n    return df1\n\ncheck_series(train_el, 1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"check_series(test_el, 2)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"* Test set have 1413 series where each series have observations for the 365 days of the year and for each day 24 hours.\n* Train set is not the case :(. "},{"metadata":{},"cell_type":"markdown","source":"# Chilled Water\n\nLet's check the building_ids (time series) for chilled water meter"},{"metadata":{"trusted":false},"cell_type":"code","source":"plot_time_freq(train_ch, 'chilled water', 'train')","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"plot_time_freq(test_ch, 'chilled water', 'test')","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"build_sw(train_ch, ['building_id', 'month', 'dayofmonth'], 'month', 2)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"build_sw(train_ch, ['building_id', 'month', 'dayofmonth'], 'month', 3)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"check_hour(train_ch)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"check_hour(test_ch)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"start_date(train_ch)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"check_series(train_ch, 1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"check_series(test_ch, 2)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"* Same escenario for chilled water"},{"metadata":{},"cell_type":"markdown","source":"# Steam\n\nLet's check the building_ids (time series) for steam meter"},{"metadata":{"trusted":false},"cell_type":"code","source":"plot_time_freq(train_st, 'steam', 'train')","execution_count":null,"outputs":[]},{"metadata":{"scrolled":false,"trusted":false},"cell_type":"code","source":"plot_time_freq(test_st, 'steam', 'test')","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"check_series(train_st, 1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"check_series(test_st, 2)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"* Same escenario for steam"},{"metadata":{},"cell_type":"markdown","source":"# Hot Water\n\nLet's check the building_ids (time series) for how water"},{"metadata":{"trusted":false},"cell_type":"code","source":"plot_time_freq(train_ho, 'hot water', 'train')","execution_count":null,"outputs":[]},{"metadata":{"scrolled":false,"trusted":false},"cell_type":"code","source":"plot_time_freq(test_ho, 'hot water', 'test')","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"check_series(train_ho, 1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"check_series(test_ho, 2)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Cross Series\n\nLet's check how many series have the 4 meter types, 3, 2 and 1"},{"metadata":{"trusted":false},"cell_type":"code","source":"cross_series = train.groupby(['building_id'])['meter'].nunique().reset_index()\ncross_series.columns = ['building_id', 'n_meter']\nprint('{} series are in the 4 types of meters'.format(cross_series[cross_series['n_meter']==4].shape[0]))\nprint('{} series are in the 3 types of meters'.format(cross_series[cross_series['n_meter']==3].shape[0]))\nprint('{} series are in the 2 types of meters'.format(cross_series[cross_series['n_meter']==2].shape[0]))\nprint('{} series are only in 1 meter'.format(cross_series[cross_series['n_meter']==1].shape[0]))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Target Variable vs Time Analysis\n\nLet's explore the target variable (meter_reading) vs time for each meter type"},{"metadata":{"trusted":false},"cell_type":"code","source":"fig, ax = plt.subplots(2, 2, figsize = (12, 12))\nsns.distplot(np.log1p(train_el['meter_reading']), ax = ax[0,0])\nax[0,0].set_title('Distribution for electricity meter')     \nsns.distplot(np.log1p(train_ch['meter_reading']), ax = ax[0,1])\nax[0,1].set_title('Distribution for chilled water meter') \nsns.distplot(np.log1p(train_st['meter_reading']), ax = ax[1,0])\nax[1,0].set_title('Distribution for steam meter') \nsns.distplot(np.log1p(train_ho['meter_reading']), ax = ax[1,1])\nax[1,1].set_title('Distribution for hot water meter')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"* We have very different distribution for each meter type. Also, we have a lot of 0.\n* What is the meaning of 0 readings (can this 0 readings be related with the previous founds?)\n\nLet's plot some random building_ids for each meter type"},{"metadata":{"trusted":false},"cell_type":"code","source":"def plot_series(df, building_id, meter_type):\n    plt.figure(figsize = (8, 8))\n    df1 = df[df['building_id']==building_id]\n    df1.groupby(['date'])['meter_reading'].sum().reset_index()\n    sns.lineplot(df1['date'], df1['meter_reading'])\n    plt.xlabel('Date', fontsize = 10)\n    plt.ylabel('Meter reading (sum of the day)')\n    plt.suptitle('Meter reading for building_id {} for {} meter type'.format(building_id, meter_type))\n    plt.show()\nplot_series(train_el, 0, 'electricity')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"This series start on 2016-01-01 but i believe it really starts on May. Maybee the first months were calibration of the meter or a malfunction, or maybee it's right and all the people that leave in that building were in vacations xD.... i really don't know...\n\nLet's plot more series"},{"metadata":{"trusted":false},"cell_type":"code","source":"plot_series(train_el, 1, 'electricity')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Similar to the previous building_id. In May reading increase a lot. Why is this happening?"},{"metadata":{"trusted":false},"cell_type":"code","source":"plot_series(train_el, 1400, 'electricity')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Very different haha. Also look in Fre and March, we are missing some observations"},{"metadata":{"trusted":false},"cell_type":"code","source":"plot_series(train_el, 800, 'electricity')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Also different"},{"metadata":{"scrolled":false,"trusted":false},"cell_type":"code","source":"plot_series(train_el, 300, 'electricity')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"To understand this series better we need more information!!."},{"metadata":{"scrolled":false,"trusted":false},"cell_type":"code","source":"plot_series(train_ch, 1000, 'chilled water')","execution_count":null,"outputs":[]},{"metadata":{"scrolled":false,"trusted":false},"cell_type":"code","source":"plot_series(train_ch, 1350, 'chilled water')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Also missing some observations"},{"metadata":{},"cell_type":"markdown","source":"# Check other time variables"},{"metadata":{"scrolled":false,"trusted":false},"cell_type":"code","source":"def plot_time_variables(df1, df2, df3, df4, col, meter_type):\n    df1 = df1.groupby([col])['meter_reading'].sum().reset_index()\n    df2 = df2.groupby([col])['meter_reading'].sum().reset_index()\n    df3 = df3.groupby([col])['meter_reading'].sum().reset_index()\n    df4 = df4.groupby([col])['meter_reading'].sum().reset_index()\n    fig, ax = plt.subplots(2, 2, figsize = (12, 12))\n    sns.lineplot(df1[col], df1['meter_reading'], ax = ax[0,0])\n    sns.lineplot(df2[col], df2['meter_reading'], ax = ax[0,1])\n    sns.lineplot(df3[col], df3['meter_reading'], ax = ax[1,0])\n    sns.lineplot(df4[col], df4['meter_reading'], ax = ax[1,1])\n     \nplot_time_variables(train_el, train_ch, train_st, train_ho, 'hour', 'electricity')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Hour feature is very predictive."},{"metadata":{"trusted":false},"cell_type":"code","source":"plot_time_variables(train_el, train_ch, train_st, train_ho, 'week', 'electricity')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We definitely have a seasonality for each meter type"},{"metadata":{"scrolled":false,"trusted":false},"cell_type":"code","source":"plot_time_variables(train_el, train_ch, train_st, train_ho, 'dayofweek', 'electricity')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Day of week is also predictive"},{"metadata":{},"cell_type":"markdown","source":"Let's move forward and check the other files."},{"metadata":{},"cell_type":"markdown","source":"# What do we know so far\n\n* We have the same series in the training and testing data (2380)\n* We have 1413, 498, 224 and 145 series for electricity, chilled water, steam and hot water meters\n* For some series in the training data we are missing some observations \n* For the test data we dont have missing observations, each series have one observation for each hour for the 365 days of year (2017 and 2018).\n* We have a lot of 0 meter readings in each meter type\n* Some series have 0 meter readings at the beggining and very low readings at the end.\n* 13 series are in the 4 types of meters\n* 331 series are in the 3 types of meters\n* 230 series are in the 2 types of meters\n* 875 series are only in 1 meter\n* Time features are very predictive"},{"metadata":{},"cell_type":"markdown","source":"# Dont know so far :(\n\n* Why do we have so many 0 meter readings?\n* Why do we have 0 meter readings at the beggining and very low readings the end of the series?\n* Why do we have missing observations in some series of the train set but not in the test set?\n\nGoing to try and find a answer to all this questions with the other features of the competition."},{"metadata":{},"cell_type":"markdown","source":"# Exploring Building Meta Data"},{"metadata":{"trusted":false},"cell_type":"code","source":"bm = pd.read_csv('/kaggle/input/ashrae-energy-prediction/building_metadata.csv')\nbm = reduce_mem_usage(bm)\nbm.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"# check if we have all the building metadata\nlen(list(set(train['building_id'].unique()).intersection(set(bm['building_id'].unique()))))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Its ok"},{"metadata":{"trusted":false},"cell_type":"code","source":"# check for missing values\ndef missing_values(df):\n    df1 = pd.DataFrame(bm.isnull().sum()).reset_index()\n    df1.columns = ['feature', 'n_missing_values']\n    df1['ratio'] = df1['n_missing_values'] / df.shape[0]\n    df1['unique'] = df.nunique().values\n    df1['max'] = df.max().values\n    df1['min'] = df.min().values\n    return df1\nmissing_values(bm)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"* So we have a lot of missing values for year built and floor count. On the other hand we have very small buildings (283 square feet) and very big (875000 square feet). \n* Talles building have 26 floor, smaller building have 1 floor\n* We have 16 site_id\n* We have 16 primary_use types"},{"metadata":{"trusted":false},"cell_type":"code","source":"fig, (ax1, ax2) = plt.subplots(1, 2, figsize = (15, 5))\nsns.distplot(bm['square_feet'], ax = ax1)\nax1.set_title('Square feet distribution')\nsns.distplot(bm['year_built'].dropna(), ax = ax2)\nax2.set_title('Year built distribution')","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"train_el = train_el.merge(bm, on = 'building_id')\ntrain_el.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":false},"cell_type":"code","source":"# is there a relation between square feet and floor count\nbm[['square_feet', 'floor_count']].corr()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Building metadata meter readings\n\nLet's check the meter reading for each building metadata information"},{"metadata":{"scrolled":false,"trusted":false},"cell_type":"code","source":"def plot_group_meta(cols, df, name):\n    df1 = df.groupby(cols)['meter_reading'].sum().reset_index()\n    for i in list(df1[cols[0]].unique()):\n        df2 = df1[df1[cols[0]]==i]\n        plt.figure(figsize = (9, 9))\n        sns.lineplot(df2[cols[1]], df2['meter_reading'])\n        plt.title('Meter readings for {} meter for {} {}'.format(name, cols[0], i))\n        \n        \nplot_group_meta(['site_id', 'date'], train_el, 'electricity')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"* Intresting, take a look site_id 15. Seems that this site_id belongs to buildings that have missing observations.\n* Site_id 0 also have a very unique behaviour"},{"metadata":{"scrolled":false,"trusted":false},"cell_type":"code","source":"build_sw(train_el[train_el['site_id']==15], ['building_id', 'month', 'dayofmonth'], 'month', 2)","execution_count":null,"outputs":[]},{"metadata":{"scrolled":false,"trusted":false},"cell_type":"code","source":"build_sw(train_el[train_el['site_id']==15], ['building_id', 'month', 'dayofmonth'], 'month', 3)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Wow!. Site id_15 have missing observations. Maybe site_id reffers to a country? or place?"},{"metadata":{"scrolled":false,"trusted":false},"cell_type":"code","source":"plot_group_meta(['primary_use', 'date'], train_el, 'electricity')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Let's check the correlation between square_feet, year_built, and floor count with meter reading"},{"metadata":{"trusted":true},"cell_type":"code","source":"def plot_corr(df, files):\n    plt.figure(figsize = (10,8))\n    sns.heatmap(df.corr(), annot = True, cmap=\"YlGnBu\")\n    plt.title('Correlation analysis between target variable and {}'.format(files))\n    plt.show\ncorr_frame = train_el[['meter_reading', 'year_built', 'square_feet', 'floor_count']]\nplot_corr(corr_frame, 'building metadata')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"markdown","source":"* Square feet is more the feature that have the talles positive correlation with meter_reading,  second is floor count."},{"metadata":{},"cell_type":"markdown","source":"More to come, stay tuned!."}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.7.3"}},"nbformat":4,"nbformat_minor":1}