{"cells":[{"metadata":{},"cell_type":"markdown","source":"# ASHRAE exploratory data analysis\n\nIn this notebook, we will study the data for the ASHRAE competition. Let's first look at the `train.csv` data"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import pandas as pd\npd.plotting.register_matplotlib_converters()\npd.set_option('display.float_format', lambda x: '%.5f' % x)\nimport matplotlib.pyplot as plt\n%matplotlib inline\nimport seaborn as sns\nimport matplotlib\nsns.set()\nprint(\"Setup Complete\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import pathlib\n\ndata_dir = pathlib.Path('/kaggle/input/ashrae-energy-prediction')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"train_data = pd.read_csv(data_dir / 'train.csv', parse_dates=['timestamp']) #, date_parser=lambda x: pd.datetime.strptime(x, \"%Y-%m-%d %H:%M:%S\"))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We create a short dictionnary to translate the meter column values. We do not directly rename the values to avoid unnecessarily increasing memory use (object type takes more space than int, the initial type of the meter column)."},{"metadata":{"trusted":true},"cell_type":"code","source":"meter_meaning = {0: 'electricity', 1: 'chilledwater', 2: 'steam', 3: 'hotwater'}","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_data.dtypes","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We could work on types to be more memory efficient. For example by casting the meter and building_id columns to int16, but here we want to focus on EDA."},{"metadata":{"trusted":true},"cell_type":"code","source":"train_data.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(f'Nb of buildings: {len(train_data.building_id.unique())}')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"for meter_val in train_data.meter.unique():\n    print(meter_meaning[meter_val])\n    print(train_data[train_data.meter == meter_val].meter_reading.describe())\n    print()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Very quickly, we can see that we have extreme values on the readings for the four metrics."},{"metadata":{"trusted":true},"cell_type":"code","source":"nb_building_per_meter = train_data[['building_id', 'meter']].drop_duplicates() \\\n                                                            .groupby(by='meter') \\\n                                                            .count() \\\n                                                            .reset_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"fig, axes = plt.subplots(1, 1, figsize=(10, 5), dpi=100)\nsns.barplot([meter_meaning[x] for x in nb_building_per_meter['meter']], \n            nb_building_per_meter['building_id'],\n            axes=axes)\naxes.set_ylabel('Nb of buildings', fontsize=13)\naxes.set_xlabel('')\naxes.set_title('Nb of buildings recorded for each type of energy', fontsize=14)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del nb_building_per_meter","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We see that the number of recorded buildings vary a lot depending upon the type of metric."},{"metadata":{"trusted":true},"cell_type":"code","source":"nb_building_per_building_id = train_data[['building_id', 'meter']].drop_duplicates() \\\n                                                                  .groupby(by='building_id') \\\n                                                                  .count() \\\n                                                                  .reset_index() \\\n                                                                  .groupby(by='meter') \\\n                                                                  .count() \\\n                                                                  .reset_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"nb_building_per_building_id","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"fig, axes = plt.subplots(1, 1, figsize=(10, 5), dpi=100)\nsns.barplot(nb_building_per_building_id['meter'], \n            nb_building_per_building_id['building_id'],\n            ax=axes)\naxes.set_ylabel('Nb of buildings', fontsize=13)\naxes.set_xlabel('Nb of recorded meters', fontsize=13)\naxes.set_title('Nb of buildings vs nb of recorded meters', fontsize=14)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del nb_building_per_building_id","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"nb_meters = train_data['meter'].nunique()\nfig, axes = plt.subplots(nb_meters, 1, figsize=(10, 20), dpi=100)\nfor i, meter in enumerate(train_data['meter'].unique()):\n    sns.kdeplot(data=train_data[train_data['meter'] == meter]['meter_reading'].dropna(),\n                shade=True,\n                label=meter_meaning[meter],\n                ax=axes[i%nb_meters])\n    axes[i%nb_meters].set_xlabel('')\n    axes[i%nb_meters].set_title(f'Distribution of values for {meter}')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We see that electricity, hot water and chilled water we have extreme values taht should be further investigated."},{"metadata":{},"cell_type":"markdown","source":"Let's now look at the building metadata dataset"},{"metadata":{"trusted":true},"cell_type":"code","source":"b_metadata = pd.read_csv(data_dir / 'building_metadata.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"b_metadata.dtypes","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"b_metadata.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"b_metadata.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(f'Nb of sites: {b_metadata.site_id.nunique()}')\nprint(f'Nb of buildings: {b_metadata.building_id.nunique()}')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"buildings_per_site_df = b_metadata.site_id.value_counts()\nfig, axes = plt.subplots(1, 1, figsize=(10, 5), dpi=100)\nsns.barplot(x=buildings_per_site_df.index,\n            y=buildings_per_site_df.values)\naxes.set_title('Nb of buildings per site')\ndel buildings_per_site_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"buildings_per_floor_df = b_metadata.floor_count.value_counts()\nfig, axes = plt.subplots(1, 1, figsize=(10, 5), dpi=100)\nsns.barplot(x=buildings_per_floor_df.index,\n            y=buildings_per_floor_df.values)\naxes.set_title('Nb of buildings per floor count')\ndel buildings_per_floor_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"buildings_per_year_built_df = b_metadata.year_built.value_counts()\nfig, axes = plt.subplots(1, 1, figsize=(10, 5), dpi=100)\nsns.lineplot(x=buildings_per_year_built_df.index,\n            y=buildings_per_year_built_df.values)\naxes.set_title('Nb of buildings per year built')\ndel buildings_per_year_built_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"buildings_per_primary_use_df = b_metadata.primary_use.value_counts()\nfig, axes = plt.subplots(1, 1, figsize=(10, 5), dpi=100)\nsns.barplot(x=buildings_per_primary_use_df.index,\n            y=buildings_per_primary_use_df.values)\naxes.set_xticklabels(axes.get_xticklabels(), rotation=90)\naxes.set_title('Nb of buildings per primary use built')\ndel buildings_per_primary_use_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"fig, axes = plt.subplots(1, 1, figsize=(10, 5), dpi=100)\nsns.distplot(b_metadata.square_feet,\n            hist=False,\n            rug=True,\n            ax=axes)\naxes.set_title('Distribution of building surfaces')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We have now a good overview of both dataset taken separately, let's merge them and analyse metric readings while having the metadata information available"},{"metadata":{"trusted":true},"cell_type":"code","source":"full_train_df = train_data.merge(b_metadata, \n                                 on='building_id',                                 \n                                 how='inner')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"full_train_df.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del train_data, b_metadata","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"full_train_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"nb_sites = full_train_df.site_id.nunique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"nb_meters = full_train_df.meter.nunique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"mean_per_site_id = full_train_df.groupby(['site_id', 'meter'])['meter_reading'].mean().reset_index()\n\nfig, axes = plt.subplots(1, 1, figsize=(10, 5), dpi=100)\nsns.barplot(data=mean_per_site_id,\n            x='site_id', \n            y='meter_reading',\n            hue='meter')\naxes.set_title('Mean meter reading per site id')\nplt.show()\ndel mean_per_site_id","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Clearly there's something going on on site 13. Let's dive into it."},{"metadata":{"trusted":true},"cell_type":"code","source":"mean_per_building_id = full_train_df[full_train_df.site_id == 13][['building_id', 'meter', 'meter_reading']] \\\n                                    .groupby(['building_id', 'meter']) \\\n                                    .mean() \\\n                                    .reset_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"mean_per_building_id.groupby(['building_id', 'meter'])['meter_reading'].max().sort_values(ascending=False).head(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"del mean_per_building_id","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Let's remove building 1099 from the dataset for a moment and plot again our `Mean meter reading per site id` graph"},{"metadata":{"trusted":true},"cell_type":"code","source":"mean_per_site_id_without_1099 = full_train_df[full_train_df.building_id != 1099].groupby(['site_id', 'meter'])['meter_reading'].mean().reset_index()\nmean_per_site_id_without_1099['meter'] = mean_per_site_id_without_1099['meter'].map(lambda x: meter_meaning[x])\n\nfig, axes = plt.subplots(1, 1, figsize=(10, 5), dpi=100)\nsns.barplot(data=mean_per_site_id_without_1099,\n            x='site_id', \n            y='meter_reading',\n            hue='meter')\naxes.set_title('Mean meter reading per site id after removing building 1099')\nplt.show()\ndel mean_per_site_id_without_1099","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"This looks much better. We should see later how to deal with 1099 in our ML workflow, but we know that removing this only building make site 13 behave in a similar way as the other sites (althouth steam consumption is still very high)"},{"metadata":{"trusted":true},"cell_type":"code","source":"mean_per_primary_use = full_train_df.groupby(['primary_use', 'meter'])['meter_reading'].mean().reset_index()\nmean_per_primary_use['meter'] = mean_per_primary_use['meter'].map(lambda x: meter_meaning[x])\n\nfig, axes = plt.subplots(1, 1, figsize=(10, 5), dpi=100)\nsns.barplot(data=mean_per_primary_use,\n            x='primary_use', \n            y='meter_reading',\n            hue='meter')\naxes.set_title('Mean meter reading per primary_use')\naxes.set_xticklabels(axes.get_xticklabels(), rotation=90)\nplt.show()\ndel mean_per_primary_use","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Note that here we plot these graph to have an overview, but it would be interesting to normalize by the mean and std for each meter type to have a scaled view over the meter readings. For example here steam have the highest values but the scale is not the same"},{"metadata":{},"cell_type":"markdown","source":"In the following graph, we go deeper into the data and plot the meter reading distributions per meter type and per site"},{"metadata":{"trusted":true},"cell_type":"code","source":"fig, axes = plt.subplots(nb_sites, nb_meters, figsize=(15, 60), dpi=100)\nfor i,site in enumerate(full_train_df.site_id.unique()):\n    for j in range(0, nb_meters):\n        sns.kdeplot(full_train_df[(full_train_df.site_id==site) & (full_train_df.meter==j)].meter_reading,\n                    shade=True,\n                    ax=axes[i][j])\n        axes[i][j].set_title(f'{meter_meaning[j]} record for site {site}')\nfig.tight_layout()\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"markdown","source":"Hope you learnt something here!\n\nThis is still an ongoing work, come back to see the notebook updated! \n\nUpvote if you liked it, comment if you think something is wrong or I missed something ;-)"},{"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":1}