{"cells":[{"metadata":{},"cell_type":"markdown","source":"Started on `16 October 2019`"},{"metadata":{},"cell_type":"markdown","source":"# Introduction"},{"metadata":{},"cell_type":"markdown","source":"#### This notebook explores the data in the ASHRAE Great Energy Predictor III competition, and make some observations along the way"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport matplotlib.pyplot as plt\n%matplotlib inline\nimport seaborn as sns\nimport datetime as dt\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\nsns.set()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":false},"cell_type":"code","source":"# load data from csv files\ntrain = pd.read_csv('../input/ashrae-energy-prediction/train.csv')\ntest = pd.read_csv('../input/ashrae-energy-prediction/test.csv')\nbuilding = pd.read_csv('../input/ashrae-energy-prediction/building_metadata.csv')\nweather_train = pd.read_csv('../input/ashrae-energy-prediction/weather_train.csv')\nweather_test = pd.read_csv('../input/ashrae-energy-prediction/weather_test.csv')\nsubmission = pd.read_csv('../input/ashrae-energy-prediction/sample_submission.csv')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true,"_kg_hide-input":false},"cell_type":"code","source":"print('Shape of the data:','\\n','  train_csv: ',train.shape,'\\n','  test_csv: ',test.shape,'\\n',\n      '  building_metadata.csv: ',building.shape,'\\n','  weather_train.csv: ',weather_train.shape,'\\n',\n      '  weather_test.csv: ',weather_test.shape)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"There are two main files - `train.csv` and `test.csv`.  In addition to these files, there are also other files, namely:\n* Meta data on the buildings - `building_metadata.csv`\n* Weather information - `weather_train.csv` and `weather_test.csv`"},{"metadata":{},"cell_type":"markdown","source":"# Examine the train data"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# look at the train_data\ntrain.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":false},"cell_type":"code","source":"# convert 'timestamp' which is a string into datetime object\ntrain['timestamp'] = pd.to_datetime(train['timestamp'])\n\n# let's also extract the pertinent date features like 'hour', 'day', 'day of week' and 'month'\ntrain[\"hour\"] = train[\"timestamp\"].dt.hour.astype(np.int8)\ntrain['day'] = train['timestamp'].dt.day.astype(np.int8)\ntrain[\"weekday\"] = train[\"timestamp\"].dt.weekday.astype(np.int8)\ntrain[\"month\"] = train[\"timestamp\"].dt.month.astype(np.int8)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":false},"cell_type":"code","source":"print('Total rows in train: ', len(train))\nprint('Total buildings: ', train['building_id'].nunique(), '\\t', np.sort(train['building_id'].unique()))\nprint('Number of meter types: ', train['meter'].nunique(), '\\t', np.sort(train['meter'].unique()))\nprint('Total hourly intervals: ', train['timestamp'].nunique(), '\\t', np.sort(train['timestamp'].unique()))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Observation:\n* There are 1449 unique building IDs from 0 to 1448.\n* There are 4 types of meters, i.e 0:electricity; 1:chilledwater; 2:steam; 3:hotwater.\n* Timestamp looks to be from 2016-01-01 to 2016-12-31 at hourly intervals, and there is a total of **8784** _(366*24)_ hourly intervals."},{"metadata":{"trusted":true,"_kg_hide-input":false},"cell_type":"code","source":"print('Count of ', train.groupby('meter')['building_id'].nunique())\nprint('\\n', 'Total meters: ', train.groupby('meter')['building_id'].nunique().sum())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Observation:\n* It is stated that not every buildings have all meter types.\n* While the majority of meters are of type '0:electricity', it looks like some buildings don't have electricity meters.\n* There are a total of **2380** meters.\n* Consider over 2016, all these 2380 meters should register a total of 20,905,920 hourly readings _(2380*8784)_. \n* However, the train data set has **20,216,100** rows. This indicates that there are some missed meter readings, probably."},{"metadata":{},"cell_type":"markdown","source":"### Visualize how the meter readings varies with timestamp\n* Meter readings were converted to log values for better visualization"},{"metadata":{"_kg_hide-input":false,"trusted":true},"cell_type":"code","source":"train['target'] = np.log1p(train['meter_reading'])","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":false,"trusted":true},"cell_type":"code","source":"meter_timestamp = {'h':[], 'd':[], 'w':[], 'm':[]}\n\nfor i in range(4):\n    x = train.query('meter == @i')\n    meter_timestamp['h'].append(x.groupby('hour')['target'].mean())\n    meter_timestamp['d'].append(x.groupby('day')['target'].mean())\n    meter_timestamp['w'].append(x.groupby('weekday')['target'].mean())\n    meter_timestamp['m'].append(x.groupby('month')['target'].mean())","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"plt.figure(figsize=(12,12))\nplt.suptitle('Variation of readings for meter types with various time intervals', fontsize=16)\npos = 0\nfor key,value in meter_timestamp.items():\n    plt.subplot(2, 2, pos+1)\n    for meter,readings in enumerate(value):\n        readings.plot(label=meter)\n    pos += 1\n    plt.legend()\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Observation:\n* There are some significant variations in readings for different meter types with respect to hours, days, days of week and months, in particular months and days of week.\n* Electricity readings are higher in the day and from April to October generally, and lower towards the weekend.\n* Chilled water readings, which is air-conditioning, are also higher in the day, and significantly higher from April to October. The location and sites should be in the northern hemisphere. \n* Steam and hotwater readings are significant lower in the summer months.\n* We can conclude that the features 'hour', 'day', 'weekday' and 'month' would be very useful for the energy predictions."},{"metadata":{},"cell_type":"markdown","source":"### Meter Reading (Target) Distribution\n* Let's look at the distribution of the meter readings for each type of the meters. Since the metric is RMSLE, we should plot the log of the meter readings."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"plt.figure(figsize=(12, 12))\nplt.suptitle('Distribution of meter readings by meter type', fontsize=16)\nlabels = {0:'Electricity', 1:'Chilled Water', 2:'Steam', 3:'Hot Water'}\ncolors = {0:'navy', 1:'darkorange', 2:'green', 3:'maroon'}\nfor i in range(4):\n    plt.subplot(2,2,i+1)\n    meter = train[train['meter']==i]['target']\n    g = sns.distplot(meter, color=colors[i])\n    plt.xlabel(labels[i])\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"* Let's also look at some samples of meter readings for individual buildings."},{"metadata":{},"cell_type":"markdown","source":"### Electricity meter readings"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"from pandas.plotting import register_matplotlib_converters\nregister_matplotlib_converters()\n\nelectricity = train[train['meter']==0]\nplt.figure(figsize=(20, 40))\nfor i in range(200):\n    plt.subplot(20, 10, i+1)\n    bldg = electricity[electricity['building_id']==i]\n    g = sns.lineplot(y='meter_reading', x='timestamp', data=bldg, color=colors[0])\n    plt.title(\"building %d\" %i,fontsize=10)\n    plt.axis('off')\n    plt.ylim(0,2000)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Observation:\n* For building_id up to 110+, it looks like there aren't any electricity readings for the first three to four months. These buildings weren't commissioned or occupied during that time period, perhaps. This could be something to think about when data cleansing or when creating ML models."},{"metadata":{},"cell_type":"markdown","source":"### Chilled water meter readings"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"chilledwater = train[train['meter']==1]\nbldg_cw = chilledwater['building_id'].unique()\n\nplt.figure(figsize=(20, 20))\nfor i in range(100):\n    index = bldg_cw[i]\n    bldg = chilledwater[chilledwater['building_id']==index]\n    plt.subplot(10, 10, i+1)\n    g = sns.lineplot(y='meter_reading', x='timestamp', data=bldg, color=colors[1])\n    plt.title(\"building %d\" %index,fontsize=10)\n    plt.axis('off')\n    plt.ylim(0, 6000)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Observation:\n* Chilled water readings look ok for the buildings shown. Generally, there is a trend of higher readings in the summer months."},{"metadata":{},"cell_type":"markdown","source":"### Steam"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"steam = train[train['meter']==2]\nbldg_st = steam['building_id'].unique()\n\nplt.figure(figsize=(20, 20))\nfor i in range(100):\n    index = bldg_st[i]\n    bldg = steam[steam['building_id']==index]\n    plt.subplot(10, 10, i+1)\n    g = sns.lineplot(y='meter_reading', x='timestamp', data=bldg, color=colors[2])\n    plt.title(\"building %d\" %index,fontsize=10)\n    plt.axis('off')\n    plt.ylim(0, 10000)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Hot Water"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"hotwater = train[train['meter']==3]\nbldg_hw = hotwater['building_id'].unique()\n\nplt.figure(figsize=(20, 20))\nfor i in range(100):\n    index = bldg_hw[i]\n    bldg = hotwater[hotwater['building_id']==index]\n    plt.subplot(10, 10, i+1)\n    g = sns.lineplot(y='meter_reading', x='timestamp', data=bldg, color=colors[3])\n    plt.title(\"building %d\" %index,fontsize=10)\n    plt.axis('off')\n    plt.ylim(0, 4000)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Observation:\n* Steam and hot water generally has a trough in the summer months."},{"metadata":{},"cell_type":"markdown","source":"# Examine the building meta data"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"building.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"building.describe()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Observation:\n* As stated, 'building_id' is the foreign key to the train and test data. Number of unique IDs matches.\n* Total of **16** site IDs, which is the foreign key to the weather data."},{"metadata":{"trusted":true,"_kg_hide-input":false},"cell_type":"code","source":"print('Total rows in building metadata: ', len(building))\nprint('Total sites: ', building['site_id'].nunique(), '\\t', np.sort(building['site_id'].unique()))\nprint('Total buildings: ', building['building_id'].nunique(), '\\t', np.sort(building['building_id'].unique()))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"building_site = building.groupby('site_id')['building_id'].count()\nplt.figure(figsize=(8,6))\nplt.bar(np.arange(16), building_site)\nplt.title('Number of buildings by Site', fontsize=16)\nplt.xlabel('site_id')\nplt.ylabel('Number')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Observation:\n* No discernable pattern here. Furthermore, we do not know the location of the sites."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"plt.figure(figsize=(12,6))\ng = sns.countplot(y='primary_use',data=building)\nplt.title('Count of primary uses of the buildings', fontsize=16)\nplt.yticks(fontsize=10)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Observation:\n* The majority of the buildings are from education, offices, entertainment/public assembly, and public service and residential."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"plt.figure(figsize=(12, 6))\ng = sns.distplot(building['year_built'].dropna(),bins=24,kde=False)\nplt.title('Distribution of buildings by the year they are built', fontsize=16)\nplt.yticks(fontsize=10)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"plt.figure(figsize=(12, 7))\ng = sns.scatterplot(y='square_feet',x='floor_count',hue='primary_use',size='square_feet',data=building)\nplt.title('XY plot of square feet vs floor count', fontsize=16)\nplt.yticks(fontsize=10)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Observation:\n* Looking at the plot, 'square_feet' correspond well with 'floor_count' generally.\n* It is noted that there are many missing values in 'floor_count' (75% missing) and 'year_built' (53% missing). It might not worthwhile to try and impute these missing values."},{"metadata":{},"cell_type":"markdown","source":"# Examine the weather data"},{"metadata":{},"cell_type":"markdown","source":"### Examine weather_train"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"weather_train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":false},"cell_type":"code","source":"# convert 'timestamp' which is a string into datetime object\nweather_train['timestamp'] = pd.to_datetime(weather_train['timestamp'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"weather_train.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":false},"cell_type":"code","source":"print('Total rows in weather_train: ', len(weather_train))\nprint('Total sites: ', weather_train['site_id'].nunique(), '\\t', np.sort(weather_train['site_id'].unique()))\nprint('Total hourly intervals: ', weather_train['timestamp'].nunique(), np.sort(weather_train['timestamp'].unique()))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Observation:\n* Over 2016 and over the 16 sites, there should register a total of 140,544 hourly readings _(8784*16)_. The weather_train data set has **139,773** rows. This indicates that there are some missed weather observation readings at some sites in the weather_train data."},{"metadata":{},"cell_type":"markdown","source":"### Examine weather_test"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"weather_test.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":false},"cell_type":"code","source":"# convert 'timestamp' which is a string into datetime object\nweather_test['timestamp'] = pd.to_datetime(weather_test['timestamp'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"weather_test.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":false},"cell_type":"code","source":"print('Total rows in weather_test: ', len(weather_test))\nprint('Total sites: ', weather_test['site_id'].nunique(), np.sort(weather_test['site_id'].unique()))\nprint('Total hourly intervals: ', weather_test['timestamp'].nunique(), np.sort(weather_test['timestamp'].unique()))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Observation:\n* Over 2017 to 2018, and over the 16 sites, there should register a total of 280,320 hourly readings.\n* But the weather_test data set has less rows, i.e. **277,243** rows."},{"metadata":{},"cell_type":"markdown","source":"# Finally, let's look at the test data"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# let's see if the test data correspond to the train data since this is a time series prediction challenge\ntest.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":false},"cell_type":"code","source":"# convert 'timestamp' which is a string into datetime object\ntest['timestamp'] = pd.to_datetime(test['timestamp'])\n\n# let's also extract the pertinent date features like 'hour', 'day', 'day of week' and 'month'\ntest[\"hour\"] = test[\"timestamp\"].dt.hour.astype(np.int8)\ntest['day'] = test['timestamp'].dt.day.astype(np.int8)\ntest[\"weekday\"] = test[\"timestamp\"].dt.weekday.astype(np.int8)\ntest[\"month\"] = test[\"timestamp\"].dt.month.astype(np.int8)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":false},"cell_type":"code","source":"print('Total rows in test: ', len(test))\nprint('Total buildings: ', test['building_id'].nunique(), '\\t', np.sort(test['building_id'].unique()))\nprint('Number of meter types: ', test['meter'].nunique(), '\\t', np.sort(test['meter'].unique()))\nprint('Total hourly intervals: ', test['timestamp'].nunique(), '\\t', np.sort(test['timestamp'].unique()))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Observation:\n* Timestamp looks to be over a two-year period from 2017-01-01 to 2018-12-31 at hourly intervals, a total of **17,500** hourly intervals."},{"metadata":{"trusted":true,"_kg_hide-input":false},"cell_type":"code","source":"print('Count of ', test.groupby('meter')['building_id'].nunique())\nprint('\\n', 'Total meters: ', test.groupby('meter')['building_id'].nunique().sum())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Observation:\n* Similarly, there is a total of **2380** meters.\n* Over the two years from 2017 and 2018, all these 2380 meters should register a total of 41,697,600 hourly readings. The test data set has the same number of **41,697,600** rows. The test data are in order."},{"metadata":{},"cell_type":"markdown","source":"#### That's all for now....."}],"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat":4,"nbformat_minor":1}