{"cells":[{"metadata":{},"cell_type":"markdown","source":"# **ASHRAE - EDA & Preprocessing**"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"from collections import defaultdict\nfrom datetime import datetime, timedelta\nfrom tqdm import tqdm\nimport holidays\nimport gc\nimport os\nimport warnings\nwarnings.filterwarnings(\"ignore\")\n\nimport numpy as np\nimport pandas as pd\npd.set_option('display.max_rows', 500)\npd.set_option('display.max_columns', 500)\npd.set_option('display.width', 1000)\n\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport cufflinks as cf\ncf.go_offline(connected=False)\n\nimport lightgbm as lgb\nprint(lgb.__version__)\n\nSEED = 42","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## **1. Weather Set**"},{"metadata":{},"cell_type":"markdown","source":"### **1.1 Overview**\n\n* Training set period is **1 year**: `2016-01-01 00:00:00` - `2016-12-31 23:00:00`\n* Test set period is **2 years**: `2017-01-01 00:00:00` - `2018-12-31 23:00:00`"},{"metadata":{"trusted":true},"cell_type":"code","source":"WEATHER_DTYPES = {'site_id': np.uint8, 'air_temperature': np.float32, 'cloud_coverage': np.float32, 'dew_temperature': np.float32, \n                     'precip_depth_1_hr': np.float32, 'sea_level_pressure': np.float32, 'wind_direction': np.float32, 'wind_speed': np.float32}\ndf_weather_train = pd.read_csv('../input/ashrae-energy-prediction/weather_train.csv', dtype=WEATHER_DTYPES)\ndf_weather_test = pd.read_csv('../input/ashrae-energy-prediction/weather_test.csv', dtype=WEATHER_DTYPES)\n\ndf_weather = pd.concat([df_weather_train, df_weather_test], ignore_index=True)\ndf_weather['timestamp'] = pd.to_datetime(df_weather['timestamp'], infer_datetime_format=True)\n\nprint('Weather Set Shape = {}'.format(df_weather.shape))\nprint('Weather Set Memory Usage = {:.2f} MB'.format(df_weather.memory_usage().sum() / 1024**2))\nprint('Training Weather Set Time Period = {} - {}'.format(df_weather[:len(df_weather_train)]['timestamp'].min(), df_weather[:len(df_weather_train)]['timestamp'].max()))\nprint('Test Weather Set Time Period = {} - {}'.format(df_weather[len(df_weather_train):]['timestamp'].min(), df_weather[len(df_weather_train):]['timestamp'].max()))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Weather data is collected every hour, but there are some missing hours in some sites in training and test weather sets. Those hour gaps may be holidays and they could give information about the location of the site.\n\n* **site 0** and **site 8** has **0** gaps\n* **site 2**, **site 3**, **site 4**, **site 6**, **site 9**, **site 10**, **site 13**, **site 14** has few and small gaps between **2-10** hours\n* **site 1**, **site 5**, **site 7**, **site 11**, **site 12** has more frequent large gaps between **2-200** hours\n* **site 15** is unique in terms of hour gaps, it has the most frequent small gaps between **2-14** hours\n\n**UPDATE**: Leaks have shown that the hour gaps are dependent to meter readings of the site. Since the weather set is left joined to the meter readings, some timestamps are excluded because there were no meter readings at those times. There is no extra information hidden in this anomaly."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"df_weather['HourGap'] = df_weather.groupby('site_id')['timestamp'].diff() / np.timedelta64(1, 'h')\n\nplt.figure(figsize=(25, 15))\nfor i in df_weather['site_id'].unique():\n    ax = plt.subplot(4, 4, i + 1)    \n    df_weather[df_weather['site_id'] == i].set_index('timestamp')['HourGap'].plot()\n    ax.set_title(f'site {i} Hour Gaps')\n    \nplt.tight_layout()\nplt.show()\n\ndf_weather.drop(columns=['HourGap'], inplace=True)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The weather data appears inconsistent with the timestamp. Daily peak and bottom temperatures varying from site to site. This inconsistency shows the timezone differences between sites.\n\n* **site 0**, **site 2**, **site 3**, **site 4**, **site 6**, **site 7**, **site 8**, **site 9**, **site 10**, **site 11**, **site 13**, **site 14** and **site 15** have similar daily temperature peaks and bottoms, but there are small timezone shifts.\n* **site 1**, **site 5** and **site 12** have identical daily temperature peaks and bottoms. There are no timezone shifts between those sites, so they are very close to each other, and they are far away from other sites.\n\n**UPDATE**: Leaks have shown that **site 1**, **site 5** and **site 12** are in Europe and other sites are in North America."},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"weather_key = ['site_id', 'timestamp']\ndf_air_temperature = df_weather[weather_key + ['air_temperature']].drop_duplicates(subset=weather_key).sort_values(by=weather_key).copy()\ndf_air_temperature['HourOfDay'] = df_air_temperature['timestamp'].dt.hour\n\nplt.figure(figsize=(25, 15))\nfor site_id, data_by_site in df_air_temperature.groupby('site_id'):\n    mean = data_by_site.groupby('HourOfDay').mean()\n    \n    ax = plt.subplot(4, 4, site_id + 1)\n    plt.plot(mean.index, mean['air_temperature'], 'xb-')\n    ax.set_title(f'site {site_id} Hour Mean Temperature')\n    \nplt.tight_layout()\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### **1.2 Timestamp Alignment**\n\n**Reference:** https://www.kaggle.com/frednavruzov/aligning-temperature-timestamp\n\nRanks of hourly temperatures within **site_id/date** groups are computed, then averaged across all timestamps and re-scaled between **0** and **1**. The sites are grouped by their peak `air_temperature` hour:\n* **site 1**, **site 5** and **site 12** have the most correct `air_temperature` peaks which is at **14:00**\n* **site 0** and **site 8** have their `air_temperature` peaks at **19:00**, and they were similar in terms of hour gaps\n* **site 3**, **site 6**, **site 7**, **site 11**, **site 14**, **site 15** have their `air_temperature` peaks at **20:00**\n* **site 9** and **site 13** have their `air_temperature` peaks at **21:00**\n* **site 4** and **site 10** have their `air_temperature` peaks at **22:00**\n* **site 2** has its `air_temperature` peaks at **23:00**\n\nIn the western locations, the `air_temperature` peaks are higher. If we assume the peaks of **site 1, 5, 12** are correct, the other sites are located to the west of those sites. The shift of `air_temperature` is clearly visible in the heatmap below."},{"metadata":{"trusted":true},"cell_type":"code","source":"df_air_temperature['air_temperature_rank'] = df_air_temperature.groupby(['site_id', df_air_temperature['timestamp'].dt.date])['air_temperature'].rank('average')\ndf_air_temperature_rank = df_air_temperature.groupby(['site_id', 'HourOfDay'])['air_temperature_rank'].mean().unstack(level=1)\ndf_air_temperature_rank = df_air_temperature_rank / df_air_temperature_rank.max(axis=1).values.reshape((-1,1))\n\nsite_ids_argmax_maxtemp = pd.Series(np.argmax(df_air_temperature_rank.values, axis=1)).sort_values().index\nsite_ids_offsets = pd.Series(df_air_temperature_rank.values.argmax(axis=1) - 14)\n\ndf_air_temperature_rank = df_air_temperature_rank.iloc[site_ids_argmax_maxtemp]\ndf_air_temperature_rank.index = [f'idx={i:02d}_site_id={s:02d}' for (i, s) in zip(range(16), df_air_temperature_rank.index)]\n\ndf_air_temperature_rank.T.iplot(kind='heatmap', colorscale='ylorrd', xTitle='HourOfDay [0-23]', title='Mean air_temperature Rank by Hour from Least to Most Correct Site')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The site offset calculations are based on the **14:00** peak time. When the offsets of each site are subtracted from their timestamp, their `air_temperature` values are aligned. This operation standardizes the `timestamp` relative to the **site (1, 5, 12)**."},{"metadata":{"trusted":true},"cell_type":"code","source":"df_air_temperature['offset'] = df_air_temperature['site_id'].map(site_ids_offsets)\ndf_air_temperature['timestamp_aligned'] = (df_air_temperature['timestamp'] - pd.to_timedelta(df_air_temperature['offset'], unit='H'))\ndf_air_temperature['air_temperature_rank_aligned'] = df_air_temperature.groupby(['site_id', df_air_temperature['timestamp_aligned'].dt.date])['air_temperature'].rank('max')\n\n# Adding the timestamp_aligned to the df_weather\ndf_weather['timestamp'] = df_air_temperature['timestamp_aligned']\n\ndf_air_temperature_rank = df_air_temperature.groupby(['site_id', df_air_temperature['timestamp_aligned'].dt.hour])['air_temperature_rank_aligned'].mean().unstack(level=1)\ndf_air_temperature_rank.T.iplot(kind='heatmap', colorscale='ylorrd', xTitle='HourOfDay [0-23]', yTitle='site_id [0-16]', title='Mean air_temperature Rank by Hour with Aligned Timestamps')\n\ndel df_air_temperature, df_air_temperature_rank, site_ids_argmax_maxtemp, site_ids_offsets","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### **1.3 Imputation**\n\nThere were some missing hours for some sites except **site 0** and **site_8**. When the offsets are subtracted from `timestamp`, beginning date shifted to `2015-12-31 15:00:00`, so the missing hours are increased. Those hours have to be created in order to make rolling features consistent even though they don't exist in training or test set.\n\n* A date range from `2015-12-31 15:00:00` to `2018-12-31 23:00:00` with hour frequency is used as index in order to create missing hours \n* Categorical date and time features are cyclic continuous features for different periods of time\n* Continuous date and time features are cumulatively increasing features as time progresses"},{"metadata":{"trusted":true},"cell_type":"code","source":"print('Initial Weather Set Shape = {}'.format(df_weather.shape))\n\n# Setting site_id and timestamp as multi index and creating missing hours\nsite_ids = sorted(np.unique(df_weather['site_id']))\ndf_weather = df_weather.set_index(['site_id', 'timestamp'], drop=False).sort_index()\nfull_index = pd.MultiIndex.from_product([site_ids, pd.date_range(start='2015-12-31 15:00:00', end='2018-12-31 23:00:00', freq='H')])\ndf_weather = df_weather.reindex(full_index)\nprint('Weather Set Shape after reindexing = {}'.format(df_weather.shape))\n\n# timestamp and site_id as features again\ndf_weather['site_id'] = df_weather.index.get_level_values(0)\ndf_weather['site_id'] = df_weather['site_id'].astype(np.uint8)\ndf_weather['timestamp'] = df_weather.index.get_level_values(1)\n\n# Categorical date and time features\ndf_weather['HourOfDay'] = df_weather['timestamp'].dt.hour.values.astype(np.uint8)\ndf_weather['DayOfWeek'] = df_weather['timestamp'].dt.dayofweek.values.astype(np.uint8)\ndf_weather['DayOfMonth'] = df_weather['timestamp'].dt.day.values.astype(np.uint8)\ndf_weather['DayOfYear'] = df_weather['timestamp'].dt.dayofyear.values.astype(np.uint16)\ndf_weather['WeekOfYear'] = (np.floor(df_weather['DayOfYear'] / 7) + 1).astype(np.uint8) # Series.dt.weekofyear is not correct: https://github.com/pandas-dev/pandas/issues/6936\ndf_weather['MonthOfYear'] = df_weather['timestamp'].dt.month.values.astype(np.uint8)\ndf_weather['Year'] = df_weather['timestamp'].dt.year.astype(np.uint16)\n\n# Continuous date and time features\ndf_weather['Hour'] = ((pd.to_timedelta(df_weather['timestamp'] - df_weather['timestamp'].min()).dt.total_seconds().astype('int64')) / 3600).astype(np.uint16)\ndf_weather['Day'] = (df_weather['Hour'] / 24).astype(np.uint16)\ndf_weather['Week'] = (df_weather['Day'] / 7).astype(np.uint8)\n\nprint('Weather Set Shape after date and time features = {}'.format(df_weather.shape))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"WEATHER_COLS = ['air_temperature', 'cloud_coverage', 'dew_temperature', 'precip_depth_1_hr', 'sea_level_pressure', 'wind_direction', 'wind_speed']\n\nfig = plt.figure(figsize=(15, 7))\nsns.barplot(x=df_weather[WEATHER_COLS].isnull().sum().index, y=df_weather[WEATHER_COLS].isnull().sum().values)\n\nplt.xlabel('Weather Features', size=15, labelpad=20)\nplt.ylabel('Missing Value Count', size=15, labelpad=20)\nplt.tick_params(axis='x', labelsize=12)\nplt.tick_params(axis='y', labelsize=15)\n\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def nan_imputer(col, window=24):\n    \n    WEATHER_PREDICTORS = ['HourOfDay', 'DayOfYear', 'WeekOfYear', 'MonthOfYear',  'Year', 'site_id', \n                          f'Site_{col}_RollingBackMean', f'Site_{col}_RollingForwMean', f'SiteHourOfDay_{col}_RollingBackMean', f'SiteHourOfDay_{col}_RollingForwMean']\n    df = df_weather.copy()\n    \n    imputer = lgb.LGBMRegressor(\n        learning_rate=0.05,\n        objective='mae',\n        n_estimators=350,\n        num_threads=os.cpu_count(),\n        num_leaves=31,\n        max_depth=8,\n        subsample=0.8,\n        min_child_samples=50,\n        random_state=SEED,\n    )    \n    \n    df[f'Site_{col}_RollingBackMean'] = df.groupby('site_id')[col].rolling(window=window, min_periods=1).mean().interpolate().values\n    df[f'Site_{col}_RollingForwMean'] = df.iloc[::-1].groupby('site_id')[col].rolling(window=window, min_periods=1).mean().interpolate().values\n    df[f'SiteHourOfDay_{col}_RollingBackMean'] = df.groupby(by=['site_id', 'HourOfDay'])[col].rolling(window=3, min_periods=1).mean().interpolate().values\n    df[f'SiteHourOfDay_{col}_RollingForwMean'] = df.iloc[::-1].groupby(by=['site_id', 'HourOfDay'])[col].rolling(window=3, min_periods=1).mean().interpolate().values\n    \n    trn_idx, missing_idx = ~df[col].isnull(), df[col].isnull()    \n    imputer.fit(X=df.loc[trn_idx, WEATHER_PREDICTORS], y=df.loc[trn_idx, col], categorical_feature=['site_id', 'Year'])\n    \n    df[f'{col}_Restored'] = df[col].copy()\n    df.loc[missing_idx, f'{col}_Restored'] = imputer.predict(df.loc[missing_idx, WEATHER_PREDICTORS])\n    \n    lgb.plot_importance(imputer)\n    plt.title(f'{col} Imputation Feature Importance')\n    \n    return df[f'{col}_Restored'].values.astype(np.float32)\n\n# Makes imputation on a copy and visualizes it\ndef plot_imputation(col, start='2016-01-01 00:00:00', end='2018-12-31 23:00:00'):    \n        \n    df = df_weather.copy()\n    df[f'{col}_Restored'] = nan_imputer(col, window=24)\n    df[f'{col}_RollingMean'] = df.groupby(by='site_id')[col].rolling(window=(24 * 3), min_periods=1).mean().values\n\n    for site in range(16):\n        start =  (site, '2016-01-01 00:00:00')\n        end = (site, '2018-07-01 00:00:00')\n        df.loc[start:end].set_index('timestamp')[[f'{col}_Restored', f'{col}_RollingMean', f'{col}']].iplot()    \n    ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"`nan_imputer` is used for filling the missing values in `air_temperature`, `dew_temperature` and `wind_speed` features. Those features are selected because;\n* They have the least number of missing values so the imputation is safer compared to other features\n* They are top performing features among weather features according to LightGBM split feature importance\n* They are needed for further feature engineering"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_weather['air_temperature'] = nan_imputer('air_temperature')\ndf_weather['dew_temperature'] = nan_imputer('dew_temperature')\ndf_weather['wind_speed'] = nan_imputer('wind_speed')\n\ndf_weather.drop(columns=['DayOfMonth', 'DayOfYear', 'WeekOfYear', 'MonthOfYear', 'Year', 'Hour', 'Day', 'Week'], inplace=True)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### **1.4 New Weather Features**\n\n* `humidity` is implemented according to formulas [here](https://www.weather.gov/media/epz/wxcalc/vaporPressure.pdf)\n* Rolling mean and std of `air_temperature`, `dew_temperature` and `humidity` are not used"},{"metadata":{"trusted":true,"_kg_hide-output":true},"cell_type":"code","source":"# Humidity\nsaturated_vapor_pressure = 6.11 * (10.0 ** (7.5 * df_weather['air_temperature'] / (237.3 + df_weather['air_temperature'])))                                    \nactual_vapor_pressure = 6.11 * (10.0 ** (7.5 * df_weather['dew_temperature'] / (237.3 + df_weather['dew_temperature'])))    \ndf_weather['humidity'] = (actual_vapor_pressure / saturated_vapor_pressure) * 100\ndf_weather['humidity'] = df_weather['humidity'].astype(np.float32)\n\ndel saturated_vapor_pressure, actual_vapor_pressure\ngc.collect()\n\n# Rolling Weather Features\n#for col in ['air_temperature', 'dew_temperature', 'humidity']:\n    #df_weather[col + '_mean_1'] = df_weather.groupby('site_id')[col].rolling(24).mean().values\n    #df_weather[col + '_mean_3'] = df_weather.groupby('site_id')[col].rolling(72).mean().values\n    #df_weather[col + '_std_1'] = df_weather.groupby('site_id')[col].rolling(24).std().values\n    #df_weather[col + '_std_3'] = df_weather.groupby('site_id')[col].rolling(72).std().values","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### **1.5 Holidays**\n* `IsHoliday` becomes **1** when the date is a holiday in that site, otherwise becomes **0** \n\nHolidays could have a major impact on energy consumption since most of the workplaces are not active on those days. Sites comprise **4** different countries which are **England**, **Ireland**, **Canada** and **United States**, so their holidays' have to be marked separately. [holidays](https://github.com/dr-prodigy/python-holidays) package is used for implementing the `IsHoliday` feature.\n\n* **England** (site 1, site 5)\n* **Ireland** (site 12)\n* **Canada** (site 7, site 11)\n* **United States** (site 0, site 2, site 3, site 4, site 6, site 8, site 9, site 10, site 13, site 14, site 15)"},{"metadata":{"trusted":true},"cell_type":"code","source":"en_holidays = holidays.England()\nir_holidays = holidays.Ireland()\nca_holidays = holidays.Canada()\nus_holidays = holidays.UnitedStates()\n\nen_idx = df_weather.query('site_id == 1 or site_id == 5').index\nir_idx = df_weather.query('site_id == 12').index\nca_idx = df_weather.query('site_id == 7 or site_id == 11').index\nus_idx = df_weather.query('site_id == 0 or site_id == 2 or site_id == 3 or site_id == 4 or site_id == 6 or site_id == 8 or site_id == 9 or site_id == 10 or site_id == 13 or site_id == 14 or site_id == 15').index\n\ndf_weather['IsHoliday'] = 0\ndf_weather.loc[en_idx, 'IsHoliday'] = df_weather.loc[en_idx, 'timestamp'].apply(lambda x: en_holidays.get(x, default=0))\ndf_weather.loc[ir_idx, 'IsHoliday'] = df_weather.loc[ir_idx, 'timestamp'].apply(lambda x: ir_holidays.get(x, default=0))\ndf_weather.loc[ca_idx, 'IsHoliday'] = df_weather.loc[ca_idx, 'timestamp'].apply(lambda x: ca_holidays.get(x, default=0))\ndf_weather.loc[us_idx, 'IsHoliday'] = df_weather.loc[us_idx, 'timestamp'].apply(lambda x: us_holidays.get(x, default=0))\n\nholiday_idx = df_weather['IsHoliday'] != 0\ndf_weather.loc[holiday_idx, 'IsHoliday'] = 1\ndf_weather['IsHoliday'] = df_weather['IsHoliday'].astype(np.uint8)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## **2. Building Metadata Set**"},{"metadata":{},"cell_type":"markdown","source":"### **2.1 Overview**\n\n* There are **16** regions (`site_id`) and **1449** unique buildings (`building_id`) in Building Metadata set\n* There are also **16** unique building types (`primary_use`)\n* Every building type doesn't exist in every single region, and every region have different number of buildings\n* There are only missing values in `year_built` and `floor_count` features"},{"metadata":{"trusted":true},"cell_type":"code","source":"BUILDING_METADATA_DTYPES = {'site_id': np.uint8, 'building_id': np.uint16, 'square_feet': np.float32, 'year_built': np.float32, 'floor_count': np.float32, 'building_eui': np.float32}\ndf_building_metadata = pd.read_csv('../input/ashrae-leaks/building_metadata_processed.csv', dtype=BUILDING_METADATA_DTYPES) # Using scraped building_metadata for site 0, 1, 2\n\nprint('Building Metadata Set Shape = {}'.format(df_building_metadata.shape))\nprint('Building Metadata Set Building Count = {}'.format(df_building_metadata['building_id'].nunique()))\nprint('Building Metadata Set Site Count = {}'.format(df_building_metadata['site_id'].nunique()))\nprint('Building Metadata Set Memory Usage = {:.2f} MB'.format(df_building_metadata.memory_usage().sum() / 1024**2))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"* Site 3 has the most number of buildings (**274**) by far\n* Sites located in **US** have more buildings compared to **UK** and **CA**\n* Sites located in **CA** have the least number of buildings (**15**, **5**)\n* Sites located in **UK** have decent number of buildings (**89**, **51** and **36**)"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"df_building_count = df_building_metadata.groupby(['site_id'])['building_id'].count().sort_values(ascending=False)\ndf_building_count.index = [f'site_{site} ({count})' for site, count in df_building_count.to_dict().items()]\n\nfig = plt.figure(figsize=(25, 8))\nsns.barplot(x=df_building_count.index, y=df_building_count.values)\n\nplt.xlabel('Sites', size=15, labelpad=20)\nplt.ylabel('Building Counts', size=15, labelpad=20)\nplt.tick_params(axis='x', labelsize=12)\nplt.tick_params(axis='y', labelsize=15)\n\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"* Most of the sites have either **Education** or **Lodging/residential** or **Office** of their most common building type except **site_8**\n* Most of the sites have heterogeneous `primary_use` distributions except **site_7** and **site_11**, which have only **Education** type buildings"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"df_building_counts = df_building_metadata.groupby(['site_id', 'primary_use'])['building_id'].count()\nprimary_use_idx = df_building_metadata['primary_use'].unique().tolist()\nbuilding_counts = {i: {primary_use: 0 for primary_use in primary_use_idx} for i in range(16)}\nsite_idx = [i for i in range(16)]  \n\nfor site in site_idx:\n    for primary_use in primary_use_idx:\n        try:\n            count = df_building_counts[site][primary_use]\n            building_counts[site][primary_use] = count \n        except KeyError:\n            building_counts[site][primary_use] = 0\n\ndf_building_counts = pd.DataFrame(building_counts)\n\nfor col in df_building_counts.columns:\n    df_building_counts[col] = df_building_counts[col].apply(lambda x: x / df_building_counts[col].sum() * 100)\n    \nfor site in site_idx:\n    df_building_counts[f'{site}_CumSum'] = df_building_counts[site].cumsum()\n    \nBAR_COUNT = np.arange(len(site_idx))  \nBAR_WIDTH = 0.85\nBAR_BOTTOMS = [f'{i}_CumSum' for i in range(16)]\n\nplt.figure(figsize=(25, 12))\n\nplt.bar(BAR_COUNT, df_building_counts.loc['Education', site_idx], color='tab:blue', edgecolor='white', width=BAR_WIDTH, label='Education')\nplt.bar(BAR_COUNT, df_building_counts.loc['Lodging/residential', site_idx], bottom=df_building_counts.loc['Education', BAR_BOTTOMS], color='tab:orange', edgecolor='white', width=BAR_WIDTH, label='Lodging/residential')\nplt.bar(BAR_COUNT, df_building_counts.loc['Office', site_idx], bottom=df_building_counts.loc['Lodging/residential', BAR_BOTTOMS], color='tab:green', edgecolor='white', width=BAR_WIDTH, label='Office')\nplt.bar(BAR_COUNT, df_building_counts.loc['Entertainment/public assembly', site_idx], bottom=df_building_counts.loc['Office', BAR_BOTTOMS], color='tab:red', edgecolor='white', width=BAR_WIDTH, label='Entertainment/public assembly')\nplt.bar(BAR_COUNT, df_building_counts.loc['Other', site_idx], color='tab:purple', bottom=df_building_counts.loc['Entertainment/public assembly', BAR_BOTTOMS], edgecolor='white', width=BAR_WIDTH, label='Other')\nplt.bar(BAR_COUNT, df_building_counts.loc['Retail', site_idx], color='tab:brown', bottom=df_building_counts.loc['Other', BAR_BOTTOMS], edgecolor='white', width=BAR_WIDTH, label='Retail')\nplt.bar(BAR_COUNT, df_building_counts.loc['Parking', site_idx], color='tab:pink', bottom=df_building_counts.loc['Retail', BAR_BOTTOMS], edgecolor='white', width=BAR_WIDTH, label='Parking')\nplt.bar(BAR_COUNT, df_building_counts.loc['Public services', site_idx], color='tab:gray', bottom=df_building_counts.loc['Parking', BAR_BOTTOMS], edgecolor='white', width=BAR_WIDTH, label='Public services')\nplt.bar(BAR_COUNT, df_building_counts.loc['Warehouse/storage', site_idx], color='tab:olive', bottom=df_building_counts.loc['Public services', BAR_BOTTOMS], edgecolor='white', width=BAR_WIDTH, label='Warehouse/storage')\nplt.bar(BAR_COUNT, df_building_counts.loc['Food sales and service', site_idx], color='tab:cyan', bottom=df_building_counts.loc['Warehouse/storage', BAR_BOTTOMS], edgecolor='white', width=BAR_WIDTH, label='Food sales and service')\nplt.bar(BAR_COUNT, df_building_counts.loc['Religious worship', site_idx], color='black', bottom=df_building_counts.loc['Food sales and service', BAR_BOTTOMS], edgecolor='white', width=BAR_WIDTH, label='Religious worship')\nplt.bar(BAR_COUNT, df_building_counts.loc['Healthcare', site_idx], color='yellow', bottom=df_building_counts.loc['Religious worship', BAR_BOTTOMS], edgecolor='white', width=BAR_WIDTH, label='Healthcare')\nplt.bar(BAR_COUNT, df_building_counts.loc['Utility', site_idx], color='aqua', bottom=df_building_counts.loc['Healthcare', BAR_BOTTOMS], edgecolor='white', width=BAR_WIDTH, label='Utility')\nplt.bar(BAR_COUNT, df_building_counts.loc['Technology/science', site_idx], color='deeppink', bottom=df_building_counts.loc['Utility', BAR_BOTTOMS], edgecolor='white', width=BAR_WIDTH, label='Technology/science')\nplt.bar(BAR_COUNT, df_building_counts.loc['Manufacturing/industrial', site_idx], color='blue', bottom=df_building_counts.loc['Technology/science', BAR_BOTTOMS], edgecolor='white', width=BAR_WIDTH, label='Manufacturing/industrial')\nplt.bar(BAR_COUNT, df_building_counts.loc['Services', site_idx], color='lime', bottom=df_building_counts.loc['Manufacturing/industrial', BAR_BOTTOMS], edgecolor='white', width=BAR_WIDTH, label='Services')\n\nplt.xlabel('Sites', size=15, labelpad=20)\nplt.ylabel('Building Type Percentage', size=15, labelpad=20)\nplt.xticks(BAR_COUNT, [f'site_{i}' for i in site_idx])    \nplt.tick_params(axis='x', labelsize=15)\nplt.tick_params(axis='y', labelsize=15)\n\nplt.legend(loc='upper left', bbox_to_anchor=(1, 1), prop={'size': 15})\nplt.title('Building Type Distribution in Sites', size=18, y=1.05)   \n\nplt.show() ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### **2.2 New Building Metadata Features**\n* `primary_use`: Label encoded\n* `building_eui`: Energy per square foot per year (kBTU/sqft) scraped from universities' websites is not used"},{"metadata":{"trusted":true},"cell_type":"code","source":"primary_use_map = {'Education': 1, 'Office': 2, 'Entertainment/public assembly': 3, 'Lodging/residential': 4,\n                   'Public services': 5, 'Healthcare': 6, 'Other': 7, 'Parking': 8, 'Manufacturing/industrial': 9,\n                   'Food sales and service': 10, 'Retail': 11, 'Warehouse/storage': 12, 'Services': 13, \n                   'Technology/science': 14, 'Utility': 15, 'Religious worship': 16}\n\ndf_building_metadata['primary_use'] = df_building_metadata['primary_use'].map(primary_use_map).astype(np.uint8)\ndf_building_metadata.drop(columns=['building_eui'], inplace=True)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## **3. Training and Test Set**"},{"metadata":{},"cell_type":"markdown","source":"### **3.1 Overview**\n* Meter reading counts in training set are real and their timestamps are correct\n* Meter reading counts in test are constant, there are timestamps for every buildings' every meter type even though they don't exist in real life\n* Those fake meter readings aren't supposed to be predicted, they are excluded from the final rmsle calculation according to organizers"},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\n\nTRAIN_DTYPES = {'building_id': np.uint16, 'meter': np.uint8, 'meter_reading': np.float32}\ndf_train = pd.read_csv('../input/ashrae-energy-prediction/train.csv', dtype=TRAIN_DTYPES)\ndf_train['timestamp'] = pd.to_datetime(df_train['timestamp'], infer_datetime_format=True)\n\nprint('Training Set Shape after merge = {}'.format(df_train.shape))\nprint('Training Set Memory Usage after merge = {:.2f} MB'.format(df_train.memory_usage().sum() / 1024**2))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### **3.2 Outlier and Constant Target Detection**\nConstant value series occur frequently in almost every site and every meter type.`filter2` is used for detecting those series. It takes `building_id`, `meter` and `min_length` parameters and returns the indices of constant series of the given building and meter type.\n* If `plot` parameter is set to `True`, function displays the target signal with and without dropped indices for comparison.\n* If `verbose` is set to `True`, function outputs the length of the series and their dates."},{"metadata":{"trusted":true},"cell_type":"code","source":"def filter2(building_id, meter, min_length, plot=False, verbose=False):\n    if verbose:\n        print(\"building_id: {}, meter: {}\".format(building_id, meter))\n    temp_df = df_train[(df_train['building_id'] == building_id) & (df_train['meter'] == meter)]        \n    target = temp_df['meter_reading'].values\n    \n    splitted_target = np.split(target, np.where(target[1:] != target[:-1])[0] + 1)\n    splitted_date = np.split(temp_df['timestamp'].values, np.where(target[1:] != target[:-1])[0] + 1)\n\n    building_idx = []\n    for i, x in enumerate(splitted_date):\n        if len(x) > min_length:\n            start = x[0]\n            end = x[-1]\n            value = splitted_target[i][0]\n            idx = df_train.query('(@start <= timestamp <= @end) and meter_reading == @value and meter == @meter and building_id == @building_id', engine='python').index.tolist()\n            building_idx.extend(idx)\n            if verbose:\n                print('Length: {},\\t{}  -  {},\\tvalue: {}'.format(len(x), start, end, value))\n                \n    building_idx = pd.Int64Index(building_idx)\n    if plot:\n        fig, axes = plt.subplots(nrows=2, figsize=(16, 18), dpi=100)\n        \n        temp_df.set_index('timestamp')['meter_reading'].plot(ax=axes[0])     \n        temp_df.drop(building_idx, axis=0).set_index('timestamp')['meter_reading'].plot(ax=axes[1])\n        \n        axes[0].set_title(f'Building {building_id} raw meter readings')\n        axes[1].set_title(f'Building {building_id} filtered meter readings')\n        \n        plt.show()\n        \n    return building_idx\n        ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df_train['IsFiltered'] = 0\n\n#################### SITE 0 ####################\n\n##### METER 0 #####\nprint('[Site 0 - Electricity] Filtering leading constant values')\n\nleading_zeros = defaultdict(list)\nfor building_id in range(105):\n    leading_zeros_last_date = df_train.query('building_id == @building_id and meter_reading == 0 and timestamp < \"2016-09-01 01:00:00\"', engine='python')['timestamp'].max()\n    leading_zeros[leading_zeros_last_date].append(building_id)\n\nfor timestamp in leading_zeros.keys():\n    building_ids = leading_zeros[pd.Timestamp(timestamp)]\n    filtered_idx = df_train[df_train['building_id'].isin(building_ids)].query('meter == 0 and timestamp <= @timestamp').index\n    df_train.loc[filtered_idx, 'IsFiltered'] = 1\n\nprint('[Site 0 - Electricity] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 0 and meter == 0 and (meter_reading > 400 or meter_reading < -400)').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 18 and meter == 0 and meter_reading < 1300').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 22 and meter == 0 and meter_reading == 0').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 25 and meter == 0 and meter_reading <= 0').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 38 and meter == 0 and (meter_reading > 2000 or meter_reading < 0)').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 41 and meter == 0 and (meter_reading > 2000 or meter_reading < 0)').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 53 and meter == 0 and meter_reading == 0').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 77 and meter == 0 and (meter_reading > 1000 or meter_reading < 0)').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 78 and meter == 0 and (meter_reading > 20000 or meter_reading < 0)').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 86 and meter == 0 and (meter_reading > 1000 or meter_reading < 0)').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 101 and meter == 0 and meter_reading > 400').index, 'IsFiltered'] = 1\n\n##### METER 1 #####\nprint('[Site 0 - Chilled Water] Filtering leading constant values')\nsite0_meter1_thresholds = {\n    50: [7, 9, 43, 60, 75, 95, 97, 98]\n}\n\nfor threshold in site0_meter1_thresholds:\n    for building_id in site0_meter1_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 1, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n\nprint('[Site 0 - Chilled Water] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 60 and meter == 1 and meter_reading > 25000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 103 and meter == 1 and meter_reading > 5000').index, 'IsFiltered'] = 1\n\n#################### SITE 1 #####################\n\n##### METER 0 #####\nprint('[Site 1 - Electricity] Filtering leading constant values')\nsite1_meter0_thresholds = {\n    20: [106],\n    50: [105, 107, 108, 109, 110, 111, 112, 113, 114, 115, 117, 119, 120, 127, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 147, 149, 152, 155]\n}\n\nfor threshold in site1_meter0_thresholds:\n    for building_id in site1_meter0_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 0, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n\n##### METER 3 #####\nprint('[Site 1 - Hot Water] Filtering leading constant values')\nsite1_meter3_thresholds = {\n    40: [106, 109, 112, 113, 114, 117, 119, 121, 138, 139, 144, 145]    \n}\n\nfor threshold in site1_meter3_thresholds:\n    for building_id in site1_meter3_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 3, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n\nprint('[Site 1 - Hot Water] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 119 and meter == 3 and meter_reading > 4000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 121 and meter == 3 and meter_reading > 20000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 144 and meter == 3 and meter_reading > 100').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 145 and meter == 3 and meter_reading > 2000').index, 'IsFiltered'] = 1\n\n#################### SITE 2 #####################\n\n##### METER 0 #####\nprint('[Site 2 - Electricity] Filtering leading constant values')\nsite2_meter0_thresholds = {\n    40: [278],\n    100: [177, 258, 269],\n    1000: [180]\n}\n\nfor threshold in site2_meter0_thresholds:\n    for building_id in site2_meter0_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 0, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n        \n##### METER 1 #####\nprint('[Site 2 - Chilled Water] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 187 and meter == 1 and meter_reading > 1000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 254 and meter == 1 and meter_reading > 1600').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 257 and meter == 1 and meter_reading > 800').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 273 and meter == 1 and meter_reading > 3000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 281 and meter == 1 and meter_reading > 2000').index, 'IsFiltered'] = 1\n\nprint('[Site 2 - Chilled Water] Filtering leading constant values')\nsite2_meter1_thresholds = {\n    100: [207],\n    1000: [260]\n}\n\nfor threshold in site2_meter1_thresholds:\n    for building_id in site2_meter1_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 1, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n\n#################### SITE 3 #####################\n\n##### METER 0 #####\nprint('[Site 3 - Electricity] Filtering leading constant values')\nsite3_meter0_thresholds = {\n    100: [545]\n}\n\nfor threshold in site3_meter0_thresholds:\n    for building_id in site3_meter0_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 0, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n\n#################### SITE 4 #####################\n\n##### METER 0 #####\nprint('[Site 4 - Electricity] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 592 and meter == 0 and meter_reading < 100').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 609 and meter == 0 and meter_reading < 300').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 620 and meter == 0 and meter_reading < 750').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 626 and meter == 0 and meter_reading < 10').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 627 and meter == 0 and meter_reading < 85').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 632 and meter == 0 and meter_reading < 30').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 645 and meter == 0 and meter_reading < 1000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 654 and meter == 0 and meter_reading < 40').index, 'IsFiltered'] = 1\n\nprint('[Site 4 - Electricity] Filtering leading constant values')\nsite4_meter0_thresholds = {\n    100: [577, 604]\n}\n\nfor threshold in site4_meter0_thresholds:\n    for building_id in site4_meter0_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 0, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n\n#################### SITE 5 #####################\n\n##### METER 0 #####\nprint('[Site 5 - Electricity] Filtering leading constant values')\nsite5_meter0_thresholds = {\n    100: [681, 723, 733, 739],\n    1000: [693]\n}\n\nfor threshold in site5_meter0_thresholds:\n    for building_id in site5_meter0_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 0, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n\n#################### SITE 6 #####################\n\n##### METER 0 #####\nprint('[Site 6 - Electricity] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 749 and meter == 0 and meter_reading < 10').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 758 and meter == 0 and meter_reading < 10').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 769 and meter == 0 and meter_reading < 10').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 770 and meter == 0 and meter_reading < 10').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 773 and meter == 0 and meter_reading < 10').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 778 and meter == 0 and meter_reading < 50').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 781 and meter == 0 and meter_reading < 10').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 785 and meter == 0 and meter_reading < 200').index, 'IsFiltered'] = 1\n\n##### METER 1 #####\nprint('[Site 6 - Chilled Water] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 745 and meter == 1 and meter_reading > 500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 750 and meter == 1 and meter_reading > 1500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 753 and meter == 1 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 755 and meter == 1 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 765 and meter == 1 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 769 and meter == 1 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 770 and meter == 1 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 771 and meter == 1 and meter_reading > 20').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 776 and meter == 1 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 777 and meter == 1 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 780 and meter == 1 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 786 and meter == 1 and meter_reading > 7000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 787 and meter == 1 and meter_reading > 2000').index, 'IsFiltered'] = 1\n\nprint('[Site 6 - Chilled Water] Filtering leading constant values')\nsite6_meter1_thresholds = {\n    100: [748, 750, 752, 763, 767, 776, 786]\n}\n\nfor threshold in site6_meter1_thresholds:\n    for building_id in site6_meter1_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 1, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n        \n##### METER 2 #####\nprint('[Site 6 - Steam] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 762 and meter == 2 and meter_reading > 500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 764 and meter == 2 and meter_reading < 500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 769 and meter == 2 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 776 and meter == 2 and meter_reading > 3000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 784 and meter == 2 and meter_reading < 2000').index, 'IsFiltered'] = 1\n\nprint('[Site 6 - Steam] Filtering leading constant values')\nsite6_meter2_thresholds = {\n    150: [750, 751, 753, 770],\n    500: [774]\n}\n\nfor threshold in site6_meter2_thresholds:\n    for building_id in site6_meter2_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 2, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n\n#################### SITE 7 #####################\n\n##### METER 0 #####\nprint('[Site 7 - Electricity] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 800 and meter == 0 and meter_reading < 75').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 801 and meter == 0 and meter_reading < 3000').index, 'IsFiltered'] = 1\n\nprint('[Site 7 - Electricity] Filtering leading constant values')\nsite7_meter0_thresholds = {\n    100: [799, 800, 802]\n}\n\nfor threshold in site7_meter0_thresholds:\n    for building_id in site7_meter0_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 0, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n        \n##### METER 1 #####\nprint('[Site 7 - Chilled Water] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 799 and meter == 1 and meter_reading > 4000 and timestamp > \"2016-11-01\"').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 800 and meter == 1 and meter_reading > 400 and timestamp > \"2016-11-01\"').index, 'IsFiltered'] = 1\n\nprint('[Site 7 - Chilled Water] Filtering leading constant values')\nsite7_meter1_thresholds = {\n    50: [789, 790, 792]\n}\n\nfor threshold in site7_meter1_thresholds:\n    for building_id in site7_meter1_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 1, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n        \n##### METER 2 #####\nprint('[Site 7 - Steam] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 797 and meter == 2 and meter_reading > 8000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 800 and meter == 2 and meter_reading == 0').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 803 and meter == 2 and meter_reading == 0').index, 'IsFiltered'] = 1\n\nprint('[Site 7 - Steam] Filtering leading constant values')\nsite7_meter2_thresholds = {\n    100: [800]\n}\n\nfor threshold in site7_meter2_thresholds:\n    for building_id in site7_meter2_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 2, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n\n#################### SITE 8 #####################\n\n##### METER 0 #####\nprint('[Site 8 - Electricity] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 857 and meter == 0 and meter_reading > 10').index, 'IsFiltered'] = 1\n\nprint('[Site 8 - Electricity] Filtering leading constant values')\nsite8_meter0_thresholds = {\n    1000: [815, 848]\n}\n\nfor threshold in site8_meter0_thresholds:\n    for building_id in site8_meter0_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 0, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n\n#################### SITE 9 #####################\n\n##### METER 0 #####\nprint('[Site 9 - Electricity] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 886 and meter == 0 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 904 and meter == 0 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 921 and meter == 0 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 927 and meter == 0 and meter_reading > 3000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 927 and meter == 0 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 949 and meter == 0 and meter_reading > 600').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 954 and meter == 0 and meter_reading > 10000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 955 and meter == 0 and meter_reading > 4000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 956 and meter == 0 and meter_reading > 500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 961 and meter == 0 and meter_reading > 1500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 962 and meter == 0 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 966 and meter == 0 and meter_reading > 3000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 977 and meter == 0 and meter_reading > 1000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 983 and meter == 0 and meter_reading > 3500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 986 and meter == 0 and meter_reading > 500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 990 and meter == 0 and meter_reading > 1000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 993 and meter == 0 and meter_reading > 6000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 997 and meter == 0 and meter_reading > 500').index, 'IsFiltered'] = 1\ndf_train.drop(df_train.query('IsFiltered == 1').index, inplace=True)\n\nprint('[Site 9 - Electricity] Filtering leading constant values')\nsite9_meter0_thresholds = {\n    40: [897],\n    50: [874, 875, 876, 877, 878, 879, 880, 881, 882, 883, 884, 885, 886, 887, 888, 889, 890, 891, 892, 893, 894, 895, 896, 898, 899,\n         900, 901, 902, 903, 904, 905, 906, 907, 908, 910, 911, 912, 913, 914, 915, 916, 917, 918, 919, 920, 921, 922, 923, 924, 925,\n         926, 927, 928, 929, 930, 931, 932, 935, 936, 937, 938, 939, 940, 941, 942, 943, 944, 945, 946, 947, 948, 949, 950, 951, 952,\n         953, 954, 955, 956, 957, 958, 959, 960, 961, 962, 963, 964, 965, 966, 967, 968, 969, 970, 971, 972, 973, 974, 975, 976, 977,\n         978, 979, 980, 981, 982, 983, 984, 985, 986, 987, 988, 989, 990, 991, 992, 993, 994, 995, 996, 997],\n    100: [909],\n}\n\nfor threshold in site9_meter0_thresholds:\n    for building_id in site9_meter0_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 0, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n\n##### METER 1 #####\nprint('[Site 9 - Chilled Water] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 879 and meter == 1 and meter_reading > 8000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 880 and meter == 1 and meter_reading > 8000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 885 and meter == 1 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 891 and meter == 1 and meter_reading > 1000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 900 and meter == 1 and meter_reading > 5000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 903 and meter == 1 and meter_reading > 20000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 906 and meter == 1 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 907 and meter == 1 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 908 and meter == 1 and meter_reading > 2500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 910 and meter == 1 and meter_reading > 4000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 920 and meter == 1 and meter_reading > 4000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 923 and meter == 1 and meter_reading > 1500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 925 and meter == 1 and meter_reading > 1000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 926 and meter == 1 and meter_reading > 500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 927 and meter == 1 and meter_reading > 20000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 929 and meter == 1 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 931 and meter == 1 and meter_reading > 6000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 934 and meter == 1 and meter_reading > 2500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 946 and meter == 1 and meter_reading > 10000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 948 and meter == 1 and meter_reading > 1000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 949 and meter == 1 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 954 and meter == 1 and meter_reading > 50000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 955 and meter == 1 and meter_reading > 15000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 956 and meter == 1 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 957 and meter == 1 and meter_reading > 3000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 961 and meter == 1 and meter_reading > 8000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 963 and meter == 1 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 964 and meter == 1 and meter_reading > 5000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 965 and meter == 1 and meter_reading > 1000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 967 and meter == 1 and meter_reading > 1750').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 969 and meter == 1 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 973 and meter == 1 and meter_reading > 2500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 976 and meter == 1 and meter_reading > 1000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 978 and meter == 1 and meter_reading > 4000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 983 and meter == 1 and meter_reading > 3800').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 989 and meter == 1 and meter_reading > 3000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 990 and meter == 1 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 993 and meter == 1 and meter_reading > 10000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 994 and meter == 1 and meter_reading > 2500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 996 and meter == 1 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.drop(df_train.query('IsFiltered == 1').index, inplace=True)\n\nprint('[Site 9 - Chilled Water] Filtering leading constant values')\nsite9_meter1_thresholds = {\n    50: [874, 875, 879, 880, 883, 885, 886, 887, 888, 889, 890, 891, 893, 894, 895, 896, 898, 899, 900, 901, 903, 905, 906, 907, 908,\n         910, 911, 912, 913, 914, 915, 916, 917, 918, 920, 921, 922, 923, 924, 925, 926, 927, 928, 929, 931, 932, 933, 934, 935, 942,\n         945, 946, 948, 949, 951, 952, 953, 954, 955, 956, 957, 958, 959, 960, 961, 962, 963, 964, 965, 966, 967, 968, 969, 971, 972,\n         973, 974, 975, 976, 978, 979, 980, 981, 983, 987, 989, 990, 991, 992, 993, 994, 995, 996, 997]\n}\n\nfor threshold in site9_meter1_thresholds:\n    for building_id in site9_meter1_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 1, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n\n##### METER 2 #####\nprint('[Site 9 - Steam] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 875 and meter == 2 and meter_reading > 1000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 876 and meter == 2 and meter_reading > 800').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 878 and meter == 2 and meter_reading > 200').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 879 and meter == 2 and meter_reading > 3000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 880 and meter == 2 and meter_reading > 1500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 880 and meter == 2 and meter_reading > 600 and (\"2016-06-01\" < timestamp < \"2016-11-01\")').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 885 and meter == 2 and meter_reading > 250').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 886 and meter == 2 and meter_reading > 1000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 886 and meter == 2 and meter_reading > 300 and (\"2016-07-01\" < timestamp < \"2016-11-01\")').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 887 and meter == 2 and meter_reading > 325').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 888 and meter == 2 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 889 and meter == 2 and meter_reading > 500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 890 and meter == 2 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 894 and meter == 2 and meter_reading > 500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 895 and meter == 2 and meter_reading > 400').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 896 and meter == 2 and meter_reading > 1000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 896 and meter == 2 and meter_reading > 400 and (\"2016-07-01\" < timestamp < \"2016-11-01\")').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 898 and meter == 2 and meter_reading > 150').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 898 and meter == 2 and meter_reading > 1500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 899 and meter == 2 and meter_reading > 800 and (\"2016-07-01\" < timestamp < \"2016-11-01\")').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 900 and meter == 2 and meter_reading > 800').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 901 and meter == 2 and meter_reading > 1000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 903 and meter == 2 and meter_reading > 2400').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 905 and meter == 2 and meter_reading > 140').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 906 and meter == 2 and meter_reading > 400').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 907 and meter == 2 and meter_reading > 300').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 907 and meter == 2 and meter_reading > 200 and (\"2016-07-01\" < timestamp < \"2016-08-01\")').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 908 and meter == 2 and meter_reading > 1000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 908 and meter == 2 and meter_reading > 300 and (\"2016-07-01\" < timestamp < \"2016-11-01\")').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 910 and meter == 2 and meter_reading > 300 and (\"2016-07-01\" < timestamp < \"2016-11-01\")').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 911 and meter == 2 and meter_reading > 1250').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 912 and meter == 2 and meter_reading > 120').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 912 and meter == 2 and meter_reading > 90 and (\"2016-07-01\" < timestamp < \"2016-11-01\")').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 913 and meter == 2 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 914 and meter == 2 and meter_reading > 800').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 915 and meter == 2 and meter_reading > 750').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 915 and meter == 2 and meter_reading > 185 and (\"2016-07-01\" < timestamp < \"2016-11-01\")').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 916 and meter == 2 and meter_reading > 200').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 917 and meter == 2 and meter_reading > 1500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 918 and meter == 2 and meter_reading > 300').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 920 and meter == 2 and meter_reading > 490').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 921 and meter == 2 and meter_reading > 1500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 922 and meter == 2 and meter_reading > 600').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 922 and meter == 2 and meter_reading > 200 and (\"2016-07-01\" < timestamp < \"2016-11-01\")').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 924 and meter == 2 and meter_reading > 3000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 926 and meter == 2 and meter_reading > 200').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 926 and meter == 2 and meter_reading > 70 and (\"2016-07-01\" < timestamp < \"2016-11-01\")').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 927 and meter == 2 and meter_reading > 4000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 928 and meter == 2 and meter_reading > 1250').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 929 and meter == 2 and meter_reading > 600').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 931 and meter == 2 and meter_reading > 1000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 931 and meter == 2 and meter_reading > 400 and (\"2016-07-01\" < timestamp < \"2016-11-01\")').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 932 and meter == 2 and meter_reading > 1750').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 933 and meter == 2 and meter_reading > 75').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 934 and meter == 2 and meter_reading > 500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 942 and meter == 2 and meter_reading > 600').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 945 and meter == 2 and meter_reading > 1200').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 946 and meter == 2 and meter_reading > 1200').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 948 and meter == 2 and meter_reading > 120').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 949 and meter == 2 and meter_reading > 500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 951 and meter == 2 and meter_reading > 800').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 952 and meter == 2 and meter_reading > 1600').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 953 and meter == 2 and meter_reading > 1250').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 954 and meter == 2 and meter_reading > 10000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 955 and meter == 2 and meter_reading > 1750').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 956 and meter == 2 and meter_reading > 350').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 957 and meter == 2 and meter_reading > 1200').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 957 and meter == 2 and meter_reading > 350 and (\"2016-07-01\" < timestamp < \"2016-11-01\")').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 958 and meter == 2 and meter_reading > 1200').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 959 and meter == 2 and meter_reading > 2500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 960 and meter == 2 and meter_reading > 350').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 960 and meter == 2 and meter_reading > 100 and (\"2016-07-01\" < timestamp < \"2016-11-01\")').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 961 and meter == 2 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 963 and meter == 2 and meter_reading > 200').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 964 and meter == 2 and meter_reading > 1000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 965 and meter == 2 and meter_reading > 200').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 966 and meter == 2 and meter_reading > 1750').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 966 and meter == 2 and meter_reading > 500 and (\"2016-07-01\" < timestamp < \"2016-08-01\")').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 967 and meter == 2 and meter_reading > 575').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 968 and meter == 2 and meter_reading > 800').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 969 and meter == 2 and meter_reading > 700').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 969 and meter == 2 and meter_reading > 400 and (\"2016-07-01\" < timestamp < \"2016-08-01\")').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 971 and meter == 2 and meter_reading > 800').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 972 and meter == 2 and meter_reading > 1500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 973 and meter == 2 and meter_reading > 450').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 974 and meter == 2 and meter_reading > 800').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 976 and meter == 2 and meter_reading > 60').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 978 and meter == 2 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 978 and meter == 2 and meter_reading > 1250 and (\"2016-07-01\" < timestamp < \"2016-08-01\")').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 980 and meter == 2 and meter_reading > 150').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 981 and meter == 2 and meter_reading > 400').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 983 and meter == 2 and meter_reading > 3000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 983 and meter == 2 and meter_reading > 1750 and (\"2016-05-01\" < timestamp < \"2016-08-01\")').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 987 and meter == 2 and meter_reading > 1000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 989 and meter == 2 and meter_reading > 400').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 991 and meter == 2 and meter_reading > 400').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 991 and meter == 2 and meter_reading > 200 and (\"2016-06-01\" < timestamp < \"2016-08-01\")').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 992 and meter == 2 and meter_reading > 600').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 993 and meter == 2 and meter_reading > 6000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 994 and meter == 2 and meter_reading > 600').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 995 and meter == 2 and meter_reading > 200').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 996 and meter == 2 and meter_reading > 260').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 997 and meter == 2 and meter_reading > 300').index, 'IsFiltered'] = 1\ndf_train.drop(df_train.query('IsFiltered == 1').index, inplace=True)\n\nprint('[Site 9 - Steam] Filtering leading constant values')\nsite9_meter2_thresholds = {\n    40: [889, 910, 934, 955, 962, 974, 976],\n    50: [874, 875, 876, 878, 879, 880, 885, 886, 887, 888, 890, 894, 895, 896, 898, 901, 903, 905, 906, 907, 908, 911, 912, 913, 914, 915, 916, 917, 918, 920, 921,\n         922, 924, 925, 926, 927, 928, 929, 931, 932, 933, 942, 945, 946, 948, 949, 951, 952, 953, 954, 956, 957, 958, 959, 960, 961, 963, 964, 965, 966, 967, 968, \n         969, 971, 972, 973, 978, 979, 980, 981, 983, 987, 989, 991, 992, 993, 994, 995, 996, 997]\n}\n\nfor threshold in site9_meter2_thresholds:\n    for building_id in site9_meter2_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 2, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n        \n#################### SITE 10 #####################\n\n##### METER 0 #####           \nprint('[Site 10 - Electricity] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 998 and meter == 0 and meter_reading > 300').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1000 and meter == 0 and meter_reading > 300').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1006 and meter == 0 and meter_reading == 0').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1008 and meter == 0 and (meter_reading > 250 or meter_reading < 5)').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1019 and meter == 0 and meter_reading == 0').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1025 and meter == 0 and meter_reading < 20').index, 'IsFiltered'] = 1\n\n##### METER 1 #####\nprint('[Site 10 - Chilled Water] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 1003 and meter == 1 and meter_reading > 1000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1017 and meter == 1 and meter_reading > 2000').index, 'IsFiltered'] = 1\n\n##### METER 3 #####\nprint('[Site 10 - Hot Water] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 1000 and meter == 3 and meter_reading > 5000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1003 and meter == 3 and meter_reading > 40 and (\"2016-06-01\" < timestamp < \"2016-08-01\")').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1012 and meter == 3 and meter_reading > 500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1017 and meter == 3 and meter_reading > 5000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1018 and meter == 3 and meter_reading > 10000').index, 'IsFiltered'] = 1\n        \n#################### SITE 12 #####################\n\n##### METER 0 #####        \nprint('[Site 12 - Electricity] Filtering leading constant values')\nsite12_meter0_thresholds = {\n    50: [1066]\n}\n\nfor threshold in site12_meter0_thresholds:\n    for building_id in site12_meter0_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 0, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n        \n#################### SITE 13 #####################\n\n##### METER 0 #####\nprint('[Site 13 - Electricity] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 1070 and meter == 0 and (meter_reading < 0 or meter_reading > 200)').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1073 and meter == 0 and (meter_reading < 60 or meter_reading > 800)').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1073 and meter == 0 and timestamp < \"2016-08-01\" and meter_reading > 200').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1082 and meter == 0 and (meter_reading < 5 or meter_reading > 200)').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1088 and meter == 0 and meter_reading == 0').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1098 and meter == 0 and meter_reading == 0').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1100 and meter == 0 and meter_reading == 0').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1119 and meter == 0 and meter_reading < 30').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1128 and meter == 0 and (meter_reading < 25 or meter_reading > 175)').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1153 and meter == 0 and (meter_reading < 90 or meter_reading > 250)').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1157 and meter == 0 and (meter_reading < 110 or meter_reading > 200)').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1162 and meter == 0 and meter_reading == 0').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1163 and meter == 0 and meter_reading < 30').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1165 and meter == 0 and meter_reading == 0').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1167 and meter == 0 and (meter_reading == 0 or meter_reading > 250)').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1168 and meter == 0 and meter_reading > 6000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1172 and meter == 0 and meter_reading < 100').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1177 and meter == 0 and meter_reading < 15').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1185 and meter == 0 and (meter_reading > 300 or meter_reading < 10)').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1202 and meter == 0 and (meter_reading < 50 or meter_reading > 300)').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1203 and meter == 0 and meter_reading == 0').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1206 and meter == 0 and meter_reading < 40').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1207 and meter == 0 and meter_reading < 100').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1208 and meter == 0 and meter_reading < 100').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1209 and meter == 0 and (meter_reading > 350 or meter_reading < 175)').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1210 and meter == 0 and (meter_reading > 225 or meter_reading < 75)').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1212 and meter == 0 and meter_reading < 600').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1219 and meter == 0 and (meter_reading < 35 or meter_reading > 300)').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1222 and meter == 0 and meter_reading < 100').index, 'IsFiltered'] = 1\n\nprint('[Site 13 - Electricity] Filtering leading constant values')\nsite13_meter0_thresholds = {\n    40: [1079, 1096, 1113, 1154, 1160, 1169, 1170, 1189, 1221]\n}\n\nfor threshold in site13_meter0_thresholds:\n    for building_id in site13_meter0_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 0, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n\n##### METER 1 #####\nprint('[Site 13 - Chilled Water] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 1088 and meter == 1 and meter_reading > 10000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1220 and meter == 1 and meter_reading > 4000').index, 'IsFiltered'] = 1\n\nprint('[Site 13 - Chilled Water] Filtering leading constant values')\nsite13_meter1_thresholds = {\n    40: [1130, 1160]\n}\n\nfor threshold in site13_meter1_thresholds:\n    for building_id in site13_meter1_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 1, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n        \n##### METER 2 #####\nprint('[Site 13 - Steam] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 1075 and meter == 2 and meter_reading > 3500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1099 and meter == 2 and meter_reading > 30000').index, 'IsFiltered'] = 1\n\nprint('[Site 13 - Steam] Filtering leading constant values')\nsite13_meter2_thresholds = {\n    40: [1072, 1098, 1158],\n    100: [1111],\n    500: [1129, 1176, 1189]\n}\n\nfor threshold in site13_meter2_thresholds:\n    for building_id in site13_meter2_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 2, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n        \n#################### SITE 14 #####################\n\n##### METER 0 #####\nprint('[Site 14 - Electricity] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 1252 and meter == 0 and meter_reading == 0').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1258 and meter == 0 and meter_reading == 0').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1263 and meter == 0 and meter_reading == 0').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1284 and meter == 0 and meter_reading == 0').index, 'IsFiltered'] = 1\n\nprint('[Site 14 - Electricity] Filtering leading constant values')\nsite14_meter0_thresholds = {\n    100: [1223, 1225, 1226, 1240, 1241, 1250, 1255, 1264, 1265, 1272, 1275, 1276, 1277, 1278, 1279, 1280, 1283,\n          1291, 1292, 1293, 1294, 1295, 1296, 1297, 1298, 1299, 1300, 1302, 1303, 1317, 1322],\n    300: [1319],\n    500: [1233, 1234]\n}\n\nfor threshold in site14_meter0_thresholds:\n    for building_id in site14_meter0_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 0, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n\n##### METER 1 #####\nprint('[Site 14 - Chilled Water] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 1236 and meter == 1 and meter_reading > 2000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1242 and meter == 1 and meter_reading > 800').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1276 and meter == 1 and meter_reading > 600').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1280 and meter == 1 and meter_reading > 120').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1311 and meter == 1 and meter_reading > 1100').index, 'IsFiltered'] = 1\n\nprint('[Site 14 - Chilled Water] Filtering leading constant values')\nsite14_meter1_thresholds = {\n    50: [1239, 1245, 1247, 1248, 1254, 1287, 1295, 1307, 1308],\n    100: [1225, 1226, 1227, 1230, 1232, 1233, 1234, 1237, 1240, 1246, 1260, 1263, 1264, 1272, 1276, \n          1280, 1288, 1290, 1291, 1292, 1293, 1294, 1296, 1297, 1300, 1302, 1303, 1310, 1311, 1312, \n          1317],\n    500: [1223]\n}\n\nfor threshold in site14_meter1_thresholds:\n    for building_id in site14_meter1_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 1, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n        \n##### METER 2 #####\nprint('[Site 14 - Steam] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 1249 and meter == 2 and meter_reading > 4000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1254 and meter == 2 and meter_reading > 1000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1256 and meter == 2 and meter_reading > 1500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1263 and meter == 2 and meter_reading > 9000').index, 'IsFiltered'] = 1\n\nprint('[Site 14 - Steam] Filtering leading constant values')\nsite14_meter2_thresholds = {\n    50: [1225, 1226, 1239, 1254, 1284, 1285, 1286, 1287, 1289, 1290,\n         1291, 1292, 1293, 1294, 1295, 1296, 1297, 1298, 1299, 1301,\n         1303, 1305, 1308, 1309, 1310],\n    100: [1238, 1243, 1245, 1247, 1248, 1249, 1250, 1263, 1307]\n}\n\nfor threshold in site14_meter2_thresholds:\n    for building_id in site14_meter2_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 2, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n        \n##### METER 3 #####\nprint('[Site 14 - Hot Water] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 1231 and meter == 3 and meter_reading > 600').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1232 and meter == 3 and meter_reading > 4500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1236 and meter == 3 and meter_reading > 600').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1251 and meter == 3 and meter_reading > 3000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1268 and meter == 3 and meter_reading > 1500').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1274 and meter == 3 and meter_reading > 1000').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1298 and meter == 3 and meter_reading > 5000').index, 'IsFiltered'] = 1\n\nprint('[Site 14 - Hot Water] Filtering leading constant values')\nsite14_meter3_thresholds = {\n    40: [1270, 1322, 1323],\n    50: [1223, 1224, 1227, 1228, 1229, 1231, 1233, 1234, 1235, 1236, 1240, 1242, 1244, 1246, 1251, 1252, 1260, 1262, 1265,\n         1266, 1267, 1269, 1271, 1272, 1273, 1274, 1275, 1276, 1312, 1317, 1318, 1319, 1321],\n    100: [1231, 1232, 1237]\n}\n\nfor threshold in site14_meter3_thresholds:\n    for building_id in site14_meter3_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 3, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n        \n#################### SITE 15 #####################\n\n##### METER 0 #####        \nprint('[Site 15 - Electricity] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 1383 and meter == 0 and meter_reading < 60').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1401 and meter == 0 and meter_reading < 10').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1414 and meter == 0 and meter_reading < 30').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1423 and meter == 0 and meter_reading < 5').index, 'IsFiltered'] = 1\n\nprint('[Site 15 - Electricity] Filtering leading constant values')\nsite15_meter0_thresholds = {\n    50: [1345, 1359, 1446]\n}\n\nfor threshold in site15_meter0_thresholds:\n    for building_id in site15_meter0_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 0, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n\n##### METER 1 #####\nprint('[Site 15 - Chilled Water] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 1349 and meter == 1 and meter_reading > 1000 and timestamp < \"2016-04-15\"').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1382 and meter == 1 and meter_reading > 300 and timestamp < \"2016-02-01\"').index, 'IsFiltered'] = 1\n\nprint('[Site 15 - Chilled Water] Filtering leading constant values')\nsite15_meter1_thresholds = {\n    50: [1363, 1410]\n}\n\nfor threshold in site15_meter1_thresholds:\n    for building_id in site15_meter1_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 1, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n        \n##### METER 2 #####        \nprint('[Site 15 - Steam] Filtering outliers')\ndf_train.loc[df_train.query('building_id == 1355 and meter == 2 and meter_reading < 200').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1361 and meter == 2 and meter_reading < 203').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1392 and meter == 2 and meter_reading < 150').index, 'IsFiltered'] = 1\ndf_train.loc[df_train.query('building_id == 1426 and meter == 2 and meter_reading > 1000').index, 'IsFiltered'] = 1\n\nprint('[Site 15 - Steam] Filtering leading constant values')\nsite15_meter2_thresholds = {\n    20: [1425, 1427],\n    40: [1329, 1337, 1338, 1341, 1342, 1344, 1347, 1350, 1351, 1354, 1360, 1364,\n         1367, 1377, 1379, 1381, 1382, 1383, 1391, 1396, 1402, 1405, 1406, 1409,\n         1414, 1418, 1424, 1430, 1433, 1437]\n}\n\nfor threshold in site15_meter2_thresholds:\n    for building_id in site15_meter2_thresholds[threshold]:\n        filtered_idx = filter2(building_id, 2, threshold)\n        df_train.loc[filtered_idx, 'IsFiltered'] = 1\n\ndf_train.drop(df_train.query('IsFiltered == 1').index, inplace=True)\ndf_train.drop(columns=['IsFiltered'], inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\n\nTEST_DTYPES = {'building_id': np.uint16, 'meter': np.uint8}\ndf_test = pd.read_csv('../input/ashrae-energy-prediction/test.csv', dtype=TEST_DTYPES)\ndf_test['timestamp'] = pd.to_datetime(df_test['timestamp'], infer_datetime_format=True)\ndf_test.drop(columns=['row_id'], inplace=True)\n\ndf_all = pd.concat([df_train, df_test], ignore_index=True, sort=False)\ndf_all = df_all.merge(df_building_metadata, on=['building_id'], how='left')\ndf_all = df_all.merge(df_weather, on=['site_id', 'timestamp'], how='left')\n\nprint('Training Set Shape after merge = {}'.format(df_train.shape))\nprint('Test Set Shape after merge = {}'.format(df_test.shape))\nprint('Training Set Memory Usage after merge = {:.2f} MB'.format(df_train.memory_usage().sum() / 1024**2))\nprint('Test Set Memory Usage after merge = {:.2f} MB'.format(df_test.memory_usage().sum() / 1024**2))\n\ndel df_train, df_test\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### **3.3 Target Encoding (Not Used)**\n* `LogTargetMeanOnAirTemperature`: Target mean on `building_id, meter, air_temperature_int` (`air_temperature` casted to int for reducing cardinality)\n* `LogTargetMeanOnDewTemperature`: Target mean on `building_id, meter, dew_temperature_int` (`dew_temperature` casted to int for reducing cardinality)"},{"metadata":{"trusted":true},"cell_type":"code","source":"# df_all['air_temperature_int'] = df_all['air_temperature'].astype(np.int8)\n# df_all['dew_temperature_int'] = df_all['dew_temperature'].astype(np.int8)\n\n# df_all['LogTargetMeanOnAirTemperature'] = np.log1p(df_all.groupby(['building_id', 'meter', 'air_temperature_int'])['meter_reading'].transform('mean').values)\n# df_all['LogTargetMeanOnDewTemperature'] = np.log1p(df_all.groupby(['building_id', 'meter', 'dew_temperature_int'])['meter_reading'].transform('mean').values)\n\n# df_all.drop(columns=['air_temperature_int', 'dew_temperature_int'], inplace=True)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### **3.4 Aggregations**"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_all['Building_Reading_Count'] = df_all['building_id'].map(df_all['building_id'].value_counts(dropna=False))\ndf_all['Building_Reading_Count'] = df_all['Building_Reading_Count'].astype(np.uint32)\n\ndf_all['Site_Reading_Count'] = df_all['site_id'].map(df_all['site_id'].value_counts(dropna=False))\ndf_all['Site_Reading_Count'] = df_all['Site_Reading_Count'].astype(np.uint32)\n\ndf_all['Building_MeterType_Reading_Count'] = df_all.groupby(['building_id', 'meter'])['building_id'].transform('count')\ndf_all['Building_MeterType_Reading_Count'] = df_all['Building_MeterType_Reading_Count'].astype(np.uint16)\n\ndf_all['Site_BuildingType_Reading_Count'] = df_all.groupby(['site_id', 'primary_use'])['site_id'].transform('count')\ndf_all['Site_BuildingType_Reading_Count'] = df_all['Site_BuildingType_Reading_Count'].astype(np.uint16)\n\ndf_all['Building_MeterType_Reading_Percentage'] = df_all['Building_MeterType_Reading_Count'] / df_all['Building_Reading_Count']\ndf_all['Building_MeterType_Reading_Percentage'] = df_all['Building_MeterType_Reading_Percentage'].astype(np.float32)\n\ndf_all['Site_BuildingType_Reading_Percentage'] = df_all['Site_BuildingType_Reading_Count'] / df_all['Site_Reading_Count']\ndf_all['Site_BuildingType_Reading_Percentage'] = df_all['Site_BuildingType_Reading_Percentage'].astype(np.float32)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## **4. Conclusion**\n* Combined `DayOfWeek` and `HourOfDay` (`DayOfWeek` + (`HourOfDay` / 24)) for identifying working days and hours in a single feature, and it makes the cv score worse\n* Combined `building_id` and `meter` (`building_id` + (`meter` / 10)) for identifying building and meter types in a single feature, and it makes the cv score worse as well\n* `IsWorkingHour` is a flag which becomes True when `DayOfWeek` is between 0-4 and `HourOfDay` is between 7-17, it doesn't have any effect on cv score\n* Filtering constant and outlier meter readings improves cv score a lot because it reduces the std of dataset, but it has to be done carefully because there is chance of losing seasonality trends\n* Target encoding (log mean meter reading of building meter type on `air_temperature` and `dew_temperature`) increases cv score significantly, but lb score decreases slighly\n* **Aggregations**\n  * `Site_DayOfYear_air_temperature_Mean` and `Site_DayOfYear_air_temperature_Std` instantly become the most important features, and cv score decreases\n  * `Site_BuildingType_square_feet_Mean, Site_BuildingType_square_feet_Std, Site_BuildingType_year_built_Mean, Site_BuildingType_year_built_Std, Site_BuildingType_floor_count_Mean, Site_BuildingType_floor_count_Std`, `Site_Building_Count`, `Site_TypeBuilding_Count`, `Site_TypeBuilding_Percentage` decrease cv score and they stay at the bottom of the feature importance, because every building are independent from each other and, they should be treated independently"},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\n\nSPLIT_DATE = '2017-01-01 00:00:00'\n\n# Dropping merged columns for size optimization\nWEATHER_COLS = ['site_id', 'air_temperature', 'cloud_coverage', 'dew_temperature', 'precip_depth_1_hr', 'sea_level_pressure', 'wind_direction', 'wind_speed', \n                'HourOfDay', 'DayOfWeek', 'humidity', 'IsHoliday']\nBUILDING_COLS = ['square_feet', 'year_built', 'floor_count', 'primary_use']\nALL_DROP_COLS = WEATHER_COLS + BUILDING_COLS\ndf_all.drop(columns=ALL_DROP_COLS, inplace=True)\n\ndf_all[df_all['timestamp'] < SPLIT_DATE].to_pickle('train.pkl')\ndf_all[df_all['timestamp'] >= SPLIT_DATE].to_pickle('test.pkl')\ndf_weather.to_pickle('weather.pkl')\ndf_building_metadata.to_pickle('building_metadata.pkl')\n\nprint('Processed Training Set Shape = {}'.format(df_all[df_all['timestamp'] < SPLIT_DATE].shape))\nprint('Processed Test Set Shape = {}'.format(df_all[df_all['timestamp'] >= SPLIT_DATE].shape))\nprint('Processed Training Set Memory Usage = {:.2f} MB'.format(df_all[df_all['timestamp'] < SPLIT_DATE].memory_usage().sum() / 1024**2))\nprint('Processed Test Set Memory Usage = {:.2f} MB'.format(df_all[df_all['timestamp'] >= SPLIT_DATE].memory_usage().sum() / 1024**2))\nprint('Processed Weather Set Shape = {}'.format(df_weather.shape))\nprint('Processed Weather Set Memory Usage = {:.2f} MB'.format(df_weather.memory_usage().sum() / 1024**2))\nprint('Processed Building Metadata Set Shape = {}'.format(df_building_metadata.shape))\nprint('Processed Building Metadata Set Memory Usage = {:.2f} MB'.format(df_building_metadata.memory_usage().sum() / 1024**2))","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}