{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","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\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport matplotlib.pyplot as plt \nimport seaborn as sns\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 5GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"In this EDA, the 'train', 'weather_train', and 'building_metadata' will be used and the 3 data files are imported:"},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"train_df=pd.read_csv('/kaggle/input/ashrae-energy-prediction/train.csv')\nbuilding_meta_df=pd.read_csv('/kaggle/input/ashrae-energy-prediction/building_metadata.csv')\nweather_train_df=pd.read_csv('/kaggle/input/ashrae-energy-prediction/weather_train.csv')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Check the size of each dataframe to make sure the data is properly imported and the NA values of each dataset is checked."},{"metadata":{"trusted":true},"cell_type":"code","source":"print(train_df.shape)\nprint(building_meta_df.shape)\nprint(weather_train_df.shape)\nprint(train_df.isna().sum())\nprint(weather_train_df.isna().sum())\nprint(building_meta_df.isna().sum())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"building_meta_df.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"In 'train_df', each building is identified with a building_id. And for each building_id, there is one or more types of meters attached to it. To explore the time series data, meter_reading, the time series plots of meter_readings from the four meters in building_id=0 is plotted with hourly,daily and monthly sampling frequency. (Note: the building_id is rondomly chosen and can be replaced by other building_id.)"},{"metadata":{},"cell_type":"markdown","source":"Electricity meter reading for building_id=0"},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df['timestamp']=pd.to_datetime(train_df['timestamp'],format='%Y-%m-%d %H:%M:%S')\ntrain_electricity=train_df[train_df['meter']==0]\n\nfig, axes = plt.subplots(1, 1, figsize=(14, 6))\ntrain_electricity[train_electricity['building_id']==0][['timestamp','meter_reading']].set_index('timestamp').plot(ax=axes).set_ylabel('building 0 electricity/kWh')\ntrain_electricity[train_electricity['building_id']==0][['timestamp','meter_reading']].set_index('timestamp').resample('D').mean().plot(ax=axes).set_ylabel('building 0 electricity/kWh')\ntrain_electricity[train_electricity['building_id']==0][['timestamp','meter_reading']].set_index('timestamp').resample('M').mean().plot(ax=axes).set_ylabel('building 0 electricity/kWh')\naxes.set_title('building 0 electricity hourly, daily, and monthly meter reading')\naxes.legend(['hourly','daily','monthly']) ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"building_id=0 doesn't have chilledwater and steam meter. \nHotwater meter reading for building_id=0:"},{"metadata":{"trusted":true},"cell_type":"code","source":"train_hotwater=train_df[train_df['meter']==3]\nfig, axes = plt.subplots(1, 1, figsize=(14, 6))\ntrain_hotwater[train_hotwater['building_id']==106][['timestamp','meter_reading']].set_index('timestamp').plot(ax=axes).set_ylabel('building 0 hotwater/kWh')\ntrain_hotwater[train_hotwater['building_id']==106][['timestamp','meter_reading']].set_index('timestamp').resample('D').mean().plot(ax=axes).set_ylabel('building 0 hotwater/kWh')\ntrain_hotwater[train_hotwater['building_id']==106][['timestamp','meter_reading']].set_index('timestamp').resample('M').mean().plot(ax=axes).set_ylabel('building 0 hotwater/kWh')\naxes.set_title('building 0 hotwater hourly, daily, and monthly meter reading')\naxes.legend(['hourly','daily','monthly']) ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Check the values of meter_reading by plotting the histograms for each meter:"},{"metadata":{"trusted":true},"cell_type":"code","source":"fig, axes = plt.subplots(2,2)\ntrain_df[train_df['meter']==0].meter_reading.plot.hist(ax=axes[0,0])\ntrain_df[train_df['meter']==1].meter_reading.plot.hist(ax=axes[0,1])\ntrain_df[train_df['meter']==2].meter_reading.plot.hist(ax=axes[1,0])\ntrain_df[train_df['meter']==3].meter_reading.plot.hist(ax=axes[1,1])\naxes[0,0].set_title('electricity')\naxes[0,1].set_title('chilledwater')\naxes[1,0].set_title('steam')\naxes[1,1].set_title('hotwater')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"As we can see from the histograms of meter_readings, the meter_reading of all the meter type is significantly right_skewed. This might be due to very large outliers which pushes all the relatively small values to the left in the histogram. Next, let's check if there are outliers in the meter_reading variable.\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"fig, axes = plt.subplots(1,4,figsize=(10,6))\ntrain_df[train_df['meter']==0].meter_reading.plot.box(ax=axes[0])\ntrain_df[train_df['meter']==1].meter_reading.plot.box(ax=axes[1])\ntrain_df[train_df['meter']==2].meter_reading.plot.box(ax=axes[2])\ntrain_df[train_df['meter']==3].meter_reading.plot.box(ax=axes[3])\naxes[0].set_title('electricity')\naxes[1].set_title('chilledwater')\naxes[2].set_title('steam')\naxes[3].set_title('hotwater')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"As shown in the boxplots, there are outliers in the meter_reading of each of the meter type. Next, the outliers of meter_reading are removed and a histogram of the meter_reading without the outliers is shown:"},{"metadata":{"trusted":true},"cell_type":"code","source":"lower_quantile_meter_reading=train_df['meter_reading'].describe()[4]\nhigher_quantile_meter_reading=train_df['meter_reading'].describe()[6]\nplt.figure()\ntrain_df[(train_df['meter_reading']>lower_quantile_meter_reading)&(train_df['meter_reading']<higher_quantile_meter_reading)].meter_reading.hist()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":" From the histogram plot of the meter_reading data without outliers, we can see the data is still significantly right_skewed. Therefore, when doing the data analysis, transformation such as log transformation on the meter_reading data might be needed.\n \n Log transformed meter_reading:"},{"metadata":{"trusted":true},"cell_type":"code","source":"log_meter_reading=np.log1p(train_df['meter_reading']).plot.hist()\nlog_meter_reading.set_title('log transformed meter reading')\ntrain_df['meter_reading']=np.log1p(train_df['meter_reading'])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"From now on, the log-transformed meter_reading is used."},{"metadata":{},"cell_type":"markdown","source":"Since the meter_reading is time series data, It's helpful to figure out the time variables hlepful in predicting meter_reading. The meter_reading versus month and the meter_reading versus hour are explored in the following plots and the trend shown in the two plots are analyzed:"},{"metadata":{"trusted":true},"cell_type":"code","source":"fig, axes = plt.subplots(5, 1, figsize=(10, 10))\ntrain_df[['timestamp','meter_reading']].set_index('timestamp').resample('M').mean().plot(ax=axes[0])\ntrain_df[train_df['meter']==0][['timestamp','meter_reading']].set_index('timestamp').resample('M').mean().plot(ax=axes[1])\ntrain_df[train_df['meter']==1][['timestamp','meter_reading']].set_index('timestamp').resample('M').mean().plot(ax=axes[2])\ntrain_df[train_df['meter']==2][['timestamp','meter_reading']].set_index('timestamp').resample('M').mean().plot(ax=axes[3])\ntrain_df[train_df['meter']==3][['timestamp','meter_reading']].set_index('timestamp').resample('M').mean().plot(ax=axes[4])\naxes[0].legend(['all meter data'],loc='upper left')\naxes[1].legend(['electricity meter data'],loc='upper left')\naxes[2].legend(['chilledwater meter data'],loc='upper left')\naxes[3].legend(['steam meter data'],loc='upper left')\naxes[4].legend(['hotwater meter data'],loc='upper left')\naxes[0].set_title('monthly meter reading trend')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"From the plots of the monthly meter reading, we can see that difference types of energy consumption hit the peak in different months within a year. For example, the chilledwater meter reading reaches the peak around September. This might be because the weahter in Septermber is hot and people tend to use more chilledwater. Thus, timestamp such as 'month' can be a predictor for predicting the meter_reading. Most of the meter reading goes up in winter, while the chilledwater meter reading goes down in winter."},{"metadata":{"trusted":true},"cell_type":"code","source":"hour=train_df.timestamp.dt.hour # extract 'hour' from datetime.\ntrain_df['hour']=hour\nfig, axes = plt.subplots(5,1,figsize=(10, 10))\ntrain_df.groupby('hour').mean().meter_reading.plot(ax=axes[0])\ntrain_df[train_df['meter']==0].groupby('hour').mean().meter_reading.plot(ax=axes[1])\ntrain_df[train_df['meter']==1].groupby('hour').mean().meter_reading.plot(ax=axes[2])\ntrain_df[train_df['meter']==2].groupby('hour').mean().meter_reading.plot(ax=axes[3])\ntrain_df[train_df['meter']==3].groupby('hour').mean().meter_reading.plot(ax=axes[4])\naxes[0].legend(['all meter data'],loc='upper left')\naxes[1].legend(['electricity meter data'],loc='upper left')\naxes[2].legend(['chilledwater meter data'],loc='upper left')\naxes[3].legend(['steam meter data'],loc='upper left')\naxes[4].legend(['hotwater meter data'],loc='upper left')\naxes[0].set_title('hourly meter reading trend')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"As we can see from the hourly meter_reading plot, different types of meter reach the maximum value at different time of a day. Thus, 'time of a day' can be a predictor for predicting the meter reading."},{"metadata":{},"cell_type":"markdown","source":"Above, the response variable 'meter_reading' is explored. Now,  let's look at some potential feature variables. The variable 'primary use' is investigated first."},{"metadata":{"trusted":true},"cell_type":"code","source":"print(building_meta_df.primary_use.value_counts())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Merge train_df and building_meta_df into one dataframe:"},{"metadata":{"trusted":true},"cell_type":"code","source":"train_whole=train_df.merge(building_meta_df, on='building_id', how='left')\ntrain_whole.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"To explore whether meter_reading is related to primary_use, I  plot the time series meter_reading for each primary use as follows:"},{"metadata":{"trusted":true},"cell_type":"code","source":"train_whole['timestamp']=pd.to_datetime(train_whole['timestamp'])\ntrain_whole['month']=train_whole['timestamp'].dt.month\ntrain_whole['meter_reading']=np.log1p(train_whole['meter_reading'])\nfig, axes = plt.subplots(8, 2, figsize=(14, 16))\nplt.subplots_adjust(hspace=0.8)\ntrain_whole[['meter_reading','primary_use','month']].groupby(['month','primary_use']).sum().meter_reading.unstack().plot(subplots=True,ax=axes)\nplt.legend(loc='upper right') ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"From the meter_reading plots for each primary use, we can see the primary use impact the trend of energy consumption significantly. Thus, primary use should be a predictor. And as a categorical variable, 'primary_use' should be encoded before use. For most of the builidng uses, the meter reading goes up in the winter."},{"metadata":{},"cell_type":"markdown","source":"Another way to show the relationship between a numerical variable and a categorical variable is the boxplot:"},{"metadata":{"trusted":true},"cell_type":"code","source":"sns.boxplot(x='meter_reading',y='primary_use',data=train_whole, orient='h')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Explore the variables: 'square_feet' and 'floor_count data'."},{"metadata":{"trusted":true},"cell_type":"code","source":"(np.log1p(building_meta_df.square_feet)).hist()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"building_meta_df.floor_count.value_counts()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"variable 'year_built':"},{"metadata":{"trusted":true},"cell_type":"code","source":"building_meta_df.year_built.value_counts()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The next sections are simple investigations of the variables in weather_train_df."},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train_df.air_temperature.dropna().hist() ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"From the above plot, air_temperatuere is roughly normal."},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train_df.dew_temperature.hist() ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"dew_temperature is roughly normal"},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat":4,"nbformat_minor":4}