{"cells":[{"metadata":{},"cell_type":"markdown","source":"# **1.Imports**"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np \nimport pandas as pd\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport datetime\nimport warnings\nwarnings.filterwarnings('ignore')\nimport lightgbm as lgb\nimport gc\nimport holidays","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# **2.Reading in Data**"},{"metadata":{"trusted":true},"cell_type":"code","source":"building = pd.read_csv('../input/ashrae-energy-prediction/building_metadata.csv')\nweather_train = pd.read_csv('../input/ashrae-energy-prediction/weather_train.csv')\ntrain = pd.read_csv('../input/ashrae-energy-prediction/train.csv')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# **3.Weather Data handling**"},{"metadata":{},"cell_type":"markdown","source":"## **3.1Adding Day & Month Columns**"},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train[\"datetime\"] = pd.to_datetime(weather_train[\"timestamp\"])\nweather_train[\"month\"] = weather_train[\"datetime\"].dt.month\nweather_train = weather_train.set_index(['site_id','month'])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## **3.2eatures with Low Percentage of Missing Data**"},{"metadata":{},"cell_type":"markdown","source":"Air temperature, dew temperature, wind direction and wind speed are features with low percentage of missing data. These feature are random and changes with season. Fill missing values with mean values of day of the month is a feasible and easy way to deal with missing values."},{"metadata":{"trusted":true},"cell_type":"code","source":"air_temperature_filler = pd.DataFrame(weather_train.groupby(['site_id','month'])['air_temperature'].mean(),columns = [\"air_temperature\"])\ndew_temperature_filler = pd.DataFrame(weather_train.groupby(['site_id','month'])['dew_temperature'].mean(),columns = [\"dew_temperature\"])\nwind_direction_filler =  pd.DataFrame(weather_train.groupby(['site_id','month'])['wind_direction'].mean(),columns = ['wind_direction'])\nwind_speed_filler =  pd.DataFrame(weather_train.groupby(['site_id','month'])['wind_speed'].mean(),columns = ['wind_speed'])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## **3.3Features with High Percentage of Missing Data**"},{"metadata":{},"cell_type":"markdown","source":"Cloud coverage, precip_depth_1_hr and sea_level_pressure are features with high percentage of missing data. Data is missing for most of days and even many consecutive days. First, use mean value of day of the month to fill in the 0 and then fill rest missing values with last valid observation."},{"metadata":{"trusted":true},"cell_type":"code","source":"cloud_coverage_filler = weather_train.groupby(['site_id','month'])['cloud_coverage'].mean()\ncloud_coverage_filler = pd.DataFrame(cloud_coverage_filler.fillna(method = 'ffill'),columns = [\"cloud_coverage\"])\nprecip_depth_filler = weather_train.groupby(['site_id','month'])['precip_depth_1_hr'].mean()\nprecip_depth_filler = pd.DataFrame(precip_depth_filler.fillna(method = 'ffill'),columns = ['precip_depth_1_hr'])\nsea_level_filler = weather_train.groupby(['site_id','month'])['sea_level_pressure'].mean()\nsea_level_filler = pd.DataFrame(sea_level_filler.fillna(method = 'ffill'),columns = ['sea_level_pressure'])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## **3.4Filling in Data**"},{"metadata":{"trusted":true},"cell_type":"code","source":"order = ['air_temperature', 'cloud_coverage', 'dew_temperature', 'precip_depth_1_hr', 'sea_level_pressure', 'wind_direction', 'wind_speed', 'timestamp','datetime']\nweather_train = weather_train[order]\nfiller = pd.concat([air_temperature_filler,cloud_coverage_filler,dew_temperature_filler,precip_depth_filler,sea_level_filler,wind_direction_filler,wind_speed_filler],axis = 1)\nweather_train.update(filler,overwrite = False)\nweather_train = weather_train.reset_index()\nweather_train = weather_train.drop(['datetime','month'],axis = 1)\nweather_train.to_csv(\"weather_train_filled.csv\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# **4.Data Merging**"},{"metadata":{"trusted":true},"cell_type":"code","source":"merge1 = building.merge(train, left_on = 'building_id', right_on = 'building_id')\ndata_table = merge1.merge(weather_train, left_on = ['site_id','timestamp'], right_on = ['site_id','timestamp'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"data_table","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# **5.Drop Holidays**"},{"metadata":{"trusted":true},"cell_type":"code","source":"# find holiday\nen_holidays = holidays.England()\nir_holidays = holidays.Ireland()\nca_holidays = holidays.Canada()\nus_holidays = holidays.UnitedStates()\n\nen_idx = data_table.query('site_id == 1 or site_id == 5').index\nir_idx = data_table.query('site_id == 12').index\nca_idx = data_table.query('site_id == 7 or site_id == 11').index\nus_idx = data_table.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\ndata_table['IsHoliday'] = 0\ndata_table.loc[en_idx, 'IsHoliday'] = data_table.loc[en_idx, 'timestamp'].apply(lambda x: en_holidays.get(x, default = 0))\ndata_table.loc[ir_idx, 'IsHoliday'] = data_table.loc[ir_idx, 'timestamp'].apply(lambda x: ir_holidays.get(x, default = 0))\ndata_table.loc[ca_idx, 'IsHoliday'] = data_table.loc[ca_idx, 'timestamp'].apply(lambda x: ca_holidays.get(x, default = 0))\ndata_table.loc[us_idx, 'IsHoliday'] = data_table.loc[us_idx, 'timestamp'].apply(lambda x: us_holidays.get(x, default = 0))\n\nholiday_idx = data_table['IsHoliday'] != 0\ndata_table.loc[holiday_idx, 'IsHoliday'] = 1\ndata_table['IsHoliday'] = data_table['IsHoliday'].astype(np.uint8)\n\n# drop holiday row\ndata_table.drop(data_table.query('IsHoliday == 1').index, inplace = True)\ndata_table.drop(columns = ['IsHoliday'], inplace = True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"data_table","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# **6.Unimportant Feature Deletion**"},{"metadata":{"trusted":true},"cell_type":"code","source":"drop_features = [\"primary_use\", \"cloud_coverage\", \"floor_count\", \"precip_depth_1_hr\", \"wind_speed\",\"year_built\"]\ndata_table.drop(drop_features, axis = 1, inplace = True) ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"data_table.to_csv(\"data_table_1.csv\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# **7.Meter Reading Outlier Deletion**"},{"metadata":{},"cell_type":"markdown","source":"## **7.1Site 0 Outlier Visualization**"},{"metadata":{"trusted":true},"cell_type":"code","source":"df = pd.read_csv(\"../input/cee69005-double-quarter-pounder-preprocessing/data_table_1.csv\", parse_dates = [\"timestamp\"], index_col = [0])\nplt.figure(figsize = [12,6])\ndf[df['site_id'] == 0][['timestamp', 'meter_reading']].set_index('timestamp').resample('H').mean()['meter_reading'].plot(alpha=0.8, color = 'tab:blue').set_ylabel('Mean meter reading', fontsize = 12)\nplt.savefig('site0.png')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize = [12,6])\ndf[(df['meter'] == 2) & (df['building_id'] == 1099)][['timestamp', 'meter_reading']].set_index('timestamp').resample('H').mean()['meter_reading'].plot(alpha = 0.8, label = 'By hour', color = 'tab:blue').set_ylabel('Mean meter reading', fontsize = 12)\nplt.savefig('building1099.png')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df.timestamp = (df.timestamp - pd.to_datetime(\"2016-01-01\")).dt.total_seconds() // 3600","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def find_bad_sitezero(X):\n    \"\"\"Returns indices of bad rows from the early days of Site 0 (UCF).\"\"\"\n    return X[(X.timestamp < 3378) & (X.site_id == 0) & (X.meter == 0)].index\ndef find_bad_building1099(X, y):\n    \"\"\"Returns indices of bad rows (with absurdly high readings) from building 1099.\"\"\"\n    return X[(X.building_id == 1099) & (X.meter == 2) & (y > 3e4)].index\ndef find_bad_rows(X, y):\n    return find_bad_sitezero(X).union(find_bad_building1099(X, y))\nbad_rows = find_bad_rows(df, df.meter_reading)\ndf = df.drop(index = bad_rows)\ndf","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df.to_csv(\"final_data_table.csv\")","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat":4,"nbformat_minor":4}