{"cells":[{"metadata":{},"cell_type":"markdown","source":"The task is to build a model which will predict the consumtion of the energy for the building based on the weather condition. These will allow to assess how the improvment of building energy efficiency will influence the energy consumption. I will start with looking at the data about building. \n![.](https://images-wixmp-ed30a86b8c4ca887773594c2.wixmp.com/f/46e8636d-7443-4725-b69f-1a7cd3eee693/d620ip0-c6be5721-ed05-42cf-88db-1e8ea25f1d6e.jpg/v1/fill/w_1024,h_768,q_75,strp/window_in_window_by_yenneferx_d620ip0-fullview.jpg?token=eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJzdWIiOiJ1cm46YXBwOjdlMGQxODg5ODIyNjQzNzNhNWYwZDQxNWVhMGQyNmUwIiwiaXNzIjoidXJuOmFwcDo3ZTBkMTg4OTgyMjY0MzczYTVmMGQ0MTVlYTBkMjZlMCIsIm9iaiI6W1t7ImhlaWdodCI6Ijw9NzY4IiwicGF0aCI6IlwvZlwvNDZlODYzNmQtNzQ0My00NzI1LWI2OWYtMWE3Y2QzZWVlNjkzXC9kNjIwaXAwLWM2YmU1NzIxLWVkMDUtNDJjZi04OGRiLTFlOGVhMjVmMWQ2ZS5qcGciLCJ3aWR0aCI6Ijw9MTAyNCJ9XV0sImF1ZCI6WyJ1cm46c2VydmljZTppbWFnZS5vcGVyYXRpb25zIl19.W5iJUg6YB0_uKaoT0ytLbHAFV8JDoAc1rbJkopQm1kA)"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import pandas as pd\nimport matplotlib.pyplot as plt\nimport numpy as np\nfrom bokeh import palettes as bh\nfrom datetime import datetime","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"folder = '../input/ashrae-energy-prediction/'\nweather_train_df = pd.read_csv(folder + 'weather_train.csv')\nbuilding_meta_df = pd.read_csv(folder + 'building_metadata.csv')\ntrain_df = pd.read_csv(folder + 'train.csv')\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"REducing memory function is taken from [ASHRAE -Start Here: A Gentle Introduction](https://www.kaggle.com/caesarlupum/ashrae-start-here-a-gentle-introduction )"},{"metadata":{"trusted":true},"cell_type":"code","source":"## Function to reduce the DF size\ndef 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\n## REducing memory\n\nweather_train_df = reduce_mem_usage(weather_train_df)\nbuilding_meta_df = reduce_mem_usage(building_meta_df)\ntrain_df = reduce_mem_usage(train_df)\nmerged_train = pd.merge(train_df, building_meta_df, how=\"left\", on=[\"building_id\"])\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Buildings\nLet's start with `building_metadata.csv` dataset. \nWe have 5 columns:\n* `site_id`- we will use it to join with weather files\n* `building_id` - id of the building, we have 1449 buildings (one row per building)\t\n* `primary_use` - we have 16 different primary use of the buildings, for Education and Office the number of buildings is quite reasonable, but for example for Religious worship we have only 3 buildings. \t\n* `square_feet` - Size of building, we have building as small as 283 ft^2 \t(26 m^2) and as big as 875000 ft^2 81290 m^2\n* `year_built` - \tNewest building come from 2017, the oldest one from 1900. It may be important information as the technology changed significantly in between. We miss this information for all of the building. \n* `floor_count` - Higest building is 26 floors, lowest 1. Again we don't have this information for some of the buildings. "},{"metadata":{"trusted":true},"cell_type":"code","source":"print('The shape of our data is:', building_meta_df.shape)\nprint(f\"There are {len(building_meta_df['building_id'].unique())} distinct buildings.\" )","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"building_meta_df.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"building_meta_pu = building_meta_df.groupby('primary_use').agg({'building_id':'count',\n                                             'year_built':['min','max','mean'],\n                                            'square_feet':['min','max','mean']})\nbuilding_meta_pu_mi = building_meta_pu.columns\nbuilding_meta_pu_mi = pd.Index([e[0] + ' ' + e[1] for e in building_meta_pu_mi.tolist()])\nbuilding_meta_pu.columns = building_meta_pu_mi\nbuilding_meta_pu = building_meta_pu.sort_values('building_id count', ascending = False)\nbuilding_meta_pu","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"fig, ax = plt.subplots()\n\nax.spines['right'].set_visible(False)\nax.spines['top'].set_visible(False)\n\nplt.hist(building_meta_df['square_feet'], 20, facecolor=bh.magma(6)[1], alpha=0.75)\nplt.xlabel('Size in square feet')\nplt.ylabel('Number of buildings')\nplt.title('Histogram of size in square feet')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Let's see if there is any difference in the size dependent of the primary use. "},{"metadata":{"trusted":true},"cell_type":"code","source":"fig, (ax1,ax2,ax3,ax4)  = plt.subplots(4, sharex=True, figsize=(12,10))\n\nax1.hist(building_meta_df[building_meta_df['primary_use']==building_meta_pu.index[0]]['square_feet'],\n                     20, facecolor=bh.viridis(6)[1], alpha=0.75, label = building_meta_pu.index[0])\nax1.legend(prop={'size': 10})\na2 = ax2.hist(building_meta_df[building_meta_df['primary_use']==building_meta_pu.index[1]]['square_feet'],\n                     20, facecolor=bh.viridis(6)[2], alpha=0.75, label = building_meta_pu.index[1])\nax2.legend(prop={'size': 10})\na3 = ax3.hist(building_meta_df[building_meta_df['primary_use']==building_meta_pu.index[2]]['square_feet'],\n                     20, facecolor=bh.viridis(6)[3], alpha=0.75, label = building_meta_pu.index[2])\nax3.legend(prop={'size': 10})\na4 = ax4.hist(building_meta_df[building_meta_df['primary_use']==building_meta_pu.index[3]]['square_feet'],\n                     20, facecolor=bh.viridis(6)[4], alpha=0.75, label = building_meta_pu.index[3])\nax4.legend(prop={'size': 10})\nax1.set_xlim([0, 500000])\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Now we will look on the histogram of Year of built. "},{"metadata":{"trusted":true},"cell_type":"code","source":"fig, ax = plt.subplots()\n\nax.spines['right'].set_visible(False)\nax.spines['top'].set_visible(False)\n\nplt.hist(building_meta_df['year_built'], 20, facecolor=bh.magma(6)[2], alpha=0.75)\nplt.xlabel('Year of building')\nplt.ylabel('Number of buildings')\nplt.title('Histogram of year of building')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"fig, (ax1,ax2,ax3,ax4)  = plt.subplots(4, sharex=True, figsize=(12,10))\n\nax1.hist(building_meta_df[building_meta_df['primary_use']==building_meta_pu.index[0]]['year_built'],\n                     20, facecolor=bh.viridis(6)[1], alpha=0.75, label = building_meta_pu.index[0])\nax1.legend(prop={'size': 10})\na2 = ax2.hist(building_meta_df[building_meta_df['primary_use']==building_meta_pu.index[1]]['year_built'],\n                     20, facecolor=bh.viridis(6)[2], alpha=0.75, label = building_meta_pu.index[1])\nax2.legend(prop={'size': 10})\na3 = ax3.hist(building_meta_df[building_meta_df['primary_use']==building_meta_pu.index[2]]['year_built'],\n                     20, facecolor=bh.viridis(6)[3], alpha=0.75, label = building_meta_pu.index[2])\nax3.legend(prop={'size': 10})\na4 = ax4.hist(building_meta_df[building_meta_df['primary_use']==building_meta_pu.index[3]]['year_built'],\n                     20, facecolor=bh.viridis(6)[4], alpha=0.75, label = building_meta_pu.index[3])\nax4.legend(prop={'size': 10})\nax1.set_xlim([1900, 2020])\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"And the last will be the number of floor. "},{"metadata":{"trusted":true},"cell_type":"code","source":"fig, ax = plt.subplots()\n\nax.spines['right'].set_visible(False)\nax.spines['top'].set_visible(False)\n\nplt.hist(building_meta_df['floor_count'], 20, facecolor=bh.magma(6)[4], alpha=0.75)\nplt.xlabel('Number of floor')\nplt.ylabel('Number of buildings')\nplt.title('Histogram of floor count')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"fig, (ax1,ax2,ax3,ax4)  = plt.subplots(4, sharex=True, figsize=(12,10))\n\nax1.hist(building_meta_df[building_meta_df['primary_use']==building_meta_pu.index[0]]['floor_count'],\n                     10, facecolor=bh.viridis(6)[1], alpha=0.75, label = building_meta_pu.index[0])\nax1.legend(prop={'size': 10})\na2 = ax2.hist(building_meta_df[building_meta_df['primary_use']==building_meta_pu.index[1]]['floor_count'],\n                     10, facecolor=bh.viridis(6)[2], alpha=0.75, label = building_meta_pu.index[1])\nax2.legend(prop={'size': 10})\na3 = ax3.hist(building_meta_df[building_meta_df['primary_use']==building_meta_pu.index[2]]['floor_count'],\n                     10, facecolor=bh.viridis(6)[3], alpha=0.75, label = building_meta_pu.index[2])\nax3.legend(prop={'size': 10})\na4 = ax4.hist(building_meta_df[building_meta_df['primary_use']==building_meta_pu.index[3]]['floor_count'],\n                     10, facecolor=bh.viridis(6)[4], alpha=0.75, label = building_meta_pu.index[3])\nax4.legend(prop={'size': 10})\nax1.set_xlim([0, 25])\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Weather \nLet's now look at `weather_train_df`. \n* site_id - We have 16 unique site_id. \n* air_temperature - First of all we have some rows with null values for air_temputere. It's not null for 139 718 rows from 139 773. The lowest air_temputere is -28.9, the higest is 47.2. We will soon see if the air temputere is significantly different dependent from the site_id. \n* cloud_coverage - We have data only for 70 600 rows. \n* dew_temperature[1] - We have data for 139 660 rows. \n* precip_depth_1_hr - We have data for 89 484 rows. \n* sea_level_pressure - We have data for 129155 rows. \n* wind_direction - We have data for 133 505 rows. Interesting is that it is numeric data from range 0-360, where 0&deg;/360&deg; can be interpreted as directly North. \n* wind_speed - We have data for 139 469 rows. The minimum value is 0, maximum value is 19m/s (68.4 km/h).\n\n[1] Dew temputere is the temperature to which air must be cooled to become saturated with water vapor. When further cooled, the airborne water vapor will condense to form liquid water (dew)."},{"metadata":{"trusted":true},"cell_type":"code","source":"print('The shape of our data is:', weather_train_df.shape)\nprint(f\"There are {len(weather_train_df.site_id.unique())} unique site_id.\" )\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train_df.describe()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Let's look at the histogram of temputere from different sites.\nWe can see that in some site_id we observe extreme cold weather (for example 11 or 13), in some other extreme hot weather (2), but we also have some with moderate weather (12, 13). "},{"metadata":{"trusted":true},"cell_type":"code","source":"site_ids = weather_train_df['site_id'].unique()\nfig, axs   = plt.subplots(4,4, sharex=True,sharey=True, figsize=(12,12))\n\nfor i in range(0,16):\n    axs[i//4,i%4 ].hist(weather_train_df[weather_train_df['site_id']==site_ids[i]]['air_temperature'],\n                     20, facecolor=bh.magma(19)[i], alpha=0.75, label = site_ids[i])\n    axs[i//4,i%4].set_title(''.join(['Site id:',str(site_ids[i])]))\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":""},{"metadata":{},"cell_type":"markdown","source":"And the wind."},{"metadata":{"trusted":true},"cell_type":"code","source":"site_ids = weather_train_df['site_id'].unique()\nfig, axs   = plt.subplots(4,4, sharex=True,sharey=True, figsize=(12,10))\n\nfor i in range(0,16):\n    axs[i//4,i%4 ].hist(weather_train_df[weather_train_df['site_id']==site_ids[i]]['wind_speed'],\n                     20, facecolor=bh.magma(19)[i], alpha=0.75, label = site_ids[i])\n    axs[i//4,i%4].set_title(''.join(['Site id:',str(site_ids[i])]))\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"And the pressure. We have data only from the first 5 site_id. "},{"metadata":{"trusted":true},"cell_type":"code","source":"site_ids = weather_train_df['site_id'].unique()\nfig, axs   = plt.subplots(5, sharex=True,sharey=True, figsize=(12,10))\n\nfor i in range(0,5):\n    axs[i ].hist(weather_train_df[weather_train_df['site_id']==site_ids[i]]['sea_level_pressure'],\n                     20, facecolor=bh.magma(19)[i], alpha=0.75, label = site_ids[i])\n    axs[i].set_title(''.join(['Site id:',str(site_ids[i])]))\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Train\nTrain dataset has only 4 columns:\n* building_id - described in part about buildings, we will use it to join these datasets.\n* meter - we have 4 posible option: 0 is electricity, 1 - chilledwater, 2 - steam, hotwater -3\n* timestamp - all data come from one year - 2016. \n* meter_reading - target value"},{"metadata":{"trusted":true},"cell_type":"code","source":"print('The shape of our data is:', train_df.shape)\nprint(f\"There are {len(train_df.building_id.unique())} unique building_id.\" )\n","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":"train_df['meter'].unique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df.groupby('meter').agg({'building_id':'nunique', 'meter_reading':sum, 'timestamp': ['min','max']})","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Let's look more detailed on the meter reading. "},{"metadata":{"trusted":true},"cell_type":"code","source":"train_df['log_meter_reading']=np.log1p(train_df['meter_reading'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"meter_ids = train_df['meter'].unique()\nmeter_ids_map = {0: 'electricity', 1: 'chilledwater', 2: 'steam', 3:'hotwater'}\n\nfig, axs   = plt.subplots(2,2, sharex=True,sharey=True, figsize=(12,12))\n\nfor i in range(0,4):\n    axs[i//2,i%2 ].hist(train_df[train_df['meter']==meter_ids[i]]['log_meter_reading'],\n                     20, facecolor=bh.cividis(4)[i], alpha=0.75, label = meter_ids_map[meter_ids[i]])\n    axs[i//2,i%2].set_title(''.join(['Logaritm of meter id:',meter_ids_map[meter_ids[i]]]))\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"I put 0 in place of -Inf. But we generally should look more carefully at those 0.0 values. It seems that we have quite a lot of them. "},{"metadata":{"trusted":true},"cell_type":"code","source":"len(train_df[train_df['meter_reading']==0]['meter_reading'])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Important concepts\nI've decided to check if some of the concepts from [discussion topic](https://www.kaggle.com/c/ashrae-energy-prediction/discussion/112871) are true. \n1. Weekly cycle of using energy. We expect Education buildings to use energy mostly during week days, while Religious only during the special weekday. \n2. For some building we can see trend independent from the weather, connected with increasing/decreasing of amount of people using the building. \n3. There is singnificant difference between old and new building because of technology. These difference is not linear, but caused by the change of technology or practise (for example some old building have high ceilings, what is not so popular nowadays). \n4. Wind is more significant for building where you are able to open the window, so it's not so significant for very high, new builidings, where windows often are fix. \n5. Buildings built in the places where weather often is extreme are better prepared for different temperature. "},{"metadata":{"trusted":true},"cell_type":"code","source":"\nmerged_train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"merged_train['Weekday'] = merged_train['timestamp'].apply(lambda row: datetime.strptime(row, '%Y-%m-%d %H:%M:%S'))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"merged_train['Weekday_num'] = merged_train['Weekday'].apply(lambda row: row.strftime('%w'))\nmerged_train['Weekday'] = merged_train['Weekday'].apply(lambda row: row.strftime('%A'))\nmerged_train['log_meter_reading']=np.log1p(merged_train['meter_reading'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"merged_train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"groupby_day = merged_train.groupby(['primary_use','Weekday','Weekday_num','site_id']).agg({'log_meter_reading':['sum','mean']})\nmerged_train","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"groupby_day_mi = groupby_day.columns\ngroupby_day_mi = pd.Index([e[0] + ' ' + e[1] for e in groupby_day_mi.tolist()])\ngroupby_day.columns = groupby_day_mi\ngroupby_day = groupby_day.reset_index()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"\nsite_ids = list(groupby_day['site_id'].unique())\nprimary_uses = list(building_meta_pu[building_meta_pu['building_id count']>20].index)\nfig, axs   = plt.subplots(4,4, sharex=True,sharey=True, figsize=(12,12))\nwidth = 1/9\nfor i in range(0,16):\n    df_site = groupby_day[groupby_day['site_id']==site_ids[i]]\n    for j in range(0,8):\n        axs[i//4,i%4 ].bar(df_site[df_site['primary_use']==primary_uses[j]]['Weekday_num'].astype(float)+width*j,df_site[df_site['primary_use']==primary_uses[j]]['log_meter_reading mean']\n                     , facecolor=bh.viridis(8)[j], alpha=0.75, label = primary_uses[j],width=width )\n        axs[i//4,i%4].set_title(''.join(['Site id:',str(site_ids[i])]))\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Work in progress...."}],"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}