{"cells":[{"metadata":{},"cell_type":"markdown","source":"## About\n\nEnergy consumption is heavily dependent on weather conditions. This weather dataset has lots of missing weather information. This kernel is dedicated to fix this issue.\n"},{"metadata":{},"cell_type":"markdown","source":"## Changelog\n\n* **V5** - Unused variable removed.\n* **V4** - Super Fast now. I was using previous version in my model and it was very slow so I decided to make it fast.\n* **V3** - Bug Fix - Filler method is correctly specified.\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nimport warnings\nimport datetime\n\nwarnings.filterwarnings('ignore')\n                        \nDATA_PATH = '../input/ashrae-energy-prediction/'","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Weather Train Data Set"},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_df = pd.read_csv(DATA_PATH + 'weather_train.csv')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Utility Functions"},{"metadata":{"trusted":true},"cell_type":"code","source":"def missing_statistics(df):    \n    statitics = pd.DataFrame(df.isnull().sum()).reset_index()\n    statitics.columns=['COLUMN NAME',\"MISSING VALUES\"]\n    statitics['TOTAL ROWS'] = df.shape[0]\n    statitics['% MISSING'] = round((statitics['MISSING VALUES']/statitics['TOTAL ROWS'])*100,2)\n    return statitics","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Missing Hours\n\nThis csv has hourly weather information for 16 sites in 2016. So this should have 140,544 records (16 x 24 x 366, 2016 is a leap year). But this csv has 139,773 records so 771 hours of data is missing. "},{"metadata":{"trusted":true},"cell_type":"code","source":"time_format = \"%Y-%m-%d %H:%M:%S\"\nstart_date = datetime.datetime.strptime(weather_df['timestamp'].min(),time_format)\nend_date = datetime.datetime.strptime(weather_df['timestamp'].max(),time_format)\ntotal_hours = int(((end_date - start_date).total_seconds() + 3600) / 3600)\nhours_list = [(end_date - datetime.timedelta(hours=x)).strftime(time_format) for x in range(total_hours)]\n\nmissing_hours = []\nfor site_id in range(16):\n    site_hours = np.array(weather_df[weather_df['site_id'] == site_id]['timestamp'])\n    new_rows = pd.DataFrame(np.setdiff1d(hours_list,site_hours),columns=['timestamp'])\n    new_rows['site_id'] = site_id\n    weather_df = pd.concat([weather_df,new_rows])\n\nweather_df = weather_df.reset_index(drop=True)    ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Count Missing Values"},{"metadata":{"trusted":true},"cell_type":"code","source":"missing_statistics(weather_df)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Add Day,Week & Month Columns\n\nThis dataset consits of hourly weather information. So we are going to fill missing values based on below new date features."},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"weather_df[\"datetime\"] = pd.to_datetime(weather_df[\"timestamp\"])\nweather_df[\"day\"] = weather_df[\"datetime\"].dt.day\nweather_df[\"week\"] = weather_df[\"datetime\"].dt.week\nweather_df[\"month\"] = weather_df[\"datetime\"].dt.month","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Reset Index for Fast Update"},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_df = weather_df.set_index(['site_id','day','month'])\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Fill Air Temperature\n\nIdes is to fill missing air temperature with mean temperature of day of the month. Each month comes in a season and temperature varies lots in a season. So filling with yearly mean value is not a good idea."},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"air_temperature_filler = pd.DataFrame(weather_df.groupby(['site_id','day','month'])['air_temperature'].mean(),columns=[\"air_temperature\"])\nweather_df.update(air_temperature_filler,overwrite=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Fill Cloud Coverage\n\nAlmost 50% data is missing. And data is missing for most of days and even many consecutive days. So, first, calculate mean cloud coverage of day of the month and then fill rest missing values with  last valid observation."},{"metadata":{"trusted":true},"cell_type":"code","source":"# Step 1\ncloud_coverage_filler = weather_df.groupby(['site_id','day','month'])['cloud_coverage'].mean()\n# Step 2\ncloud_coverage_filler = pd.DataFrame(cloud_coverage_filler.fillna(method='ffill'),columns=[\"cloud_coverage\"])\n\nweather_df.update(cloud_coverage_filler,overwrite=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Fill Dew Temperature"},{"metadata":{"trusted":true},"cell_type":"code","source":"due_temperature_filler = pd.DataFrame(weather_df.groupby(['site_id','day','month'])['dew_temperature'].mean(),columns=[\"dew_temperature\"])\nweather_df.update(due_temperature_filler,overwrite=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Fill Sea level Pressure"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Step 1\nsea_level_filler = weather_df.groupby(['site_id','day','month'])['sea_level_pressure'].mean()\n# Step 2\nsea_level_filler = pd.DataFrame(sea_level_filler.fillna(method='ffill'),columns=['sea_level_pressure'])\n\nweather_df.update(sea_level_filler,overwrite=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Fill Wind Direction"},{"metadata":{"trusted":true},"cell_type":"code","source":"wind_direction_filler =  pd.DataFrame(weather_df.groupby(['site_id','day','month'])['wind_direction'].mean(),columns=['wind_direction'])\nweather_df.update(wind_direction_filler,overwrite=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Fill Wind Speed"},{"metadata":{"trusted":true},"cell_type":"code","source":"wind_speed_filler =  pd.DataFrame(weather_df.groupby(['site_id','day','month'])['wind_speed'].mean(),columns=['wind_speed'])\nweather_df.update(wind_speed_filler,overwrite=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Fill precip_depth_1_hr\n\nI noted this value is ignored by most of the kagglers and it has 35% missing data. This value indicates a very light and brief rain or snow, you often won't know it unless you go outside. At a moment, this value will be just a greater than zero but not measureable by rain gauge."},{"metadata":{"trusted":true},"cell_type":"code","source":"# Step 1\nprecip_depth_filler = weather_df.groupby(['site_id','day','month'])['precip_depth_1_hr'].mean()\n# Step 2\nprecip_depth_filler = pd.DataFrame(precip_depth_filler.fillna(method='ffill'),columns=['precip_depth_1_hr'])\n\nweather_df.update(precip_depth_filler,overwrite=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Remove Extra Features"},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_df = weather_df.reset_index()\nweather_df = weather_df.drop(['datetime','day','week','month'],axis=1)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Count Missing Values "},{"metadata":{"trusted":true},"cell_type":"code","source":"missing_statistics(weather_df)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## How to Use\n\nI have put all code in one function so you can just copy and use it.     "},{"metadata":{"trusted":true},"cell_type":"code","source":"def fill_weather_dataset(weather_df):\n    \n    # Find Missing Dates\n    time_format = \"%Y-%m-%d %H:%M:%S\"\n    start_date = datetime.datetime.strptime(weather_df['timestamp'].min(),time_format)\n    end_date = datetime.datetime.strptime(weather_df['timestamp'].max(),time_format)\n    total_hours = int(((end_date - start_date).total_seconds() + 3600) / 3600)\n    hours_list = [(end_date - datetime.timedelta(hours=x)).strftime(time_format) for x in range(total_hours)]\n\n    missing_hours = []\n    for site_id in range(16):\n        site_hours = np.array(weather_df[weather_df['site_id'] == site_id]['timestamp'])\n        new_rows = pd.DataFrame(np.setdiff1d(hours_list,site_hours),columns=['timestamp'])\n        new_rows['site_id'] = site_id\n        weather_df = pd.concat([weather_df,new_rows])\n\n        weather_df = weather_df.reset_index(drop=True)           \n\n    # Add new Features\n    weather_df[\"datetime\"] = pd.to_datetime(weather_df[\"timestamp\"])\n    weather_df[\"day\"] = weather_df[\"datetime\"].dt.day\n    weather_df[\"week\"] = weather_df[\"datetime\"].dt.week\n    weather_df[\"month\"] = weather_df[\"datetime\"].dt.month\n    \n    # Reset Index for Fast Update\n    weather_df = weather_df.set_index(['site_id','day','month'])\n\n    air_temperature_filler = pd.DataFrame(weather_df.groupby(['site_id','day','month'])['air_temperature'].mean(),columns=[\"air_temperature\"])\n    weather_df.update(air_temperature_filler,overwrite=False)\n\n    # Step 1\n    cloud_coverage_filler = weather_df.groupby(['site_id','day','month'])['cloud_coverage'].mean()\n    # Step 2\n    cloud_coverage_filler = pd.DataFrame(cloud_coverage_filler.fillna(method='ffill'),columns=[\"cloud_coverage\"])\n\n    weather_df.update(cloud_coverage_filler,overwrite=False)\n\n    due_temperature_filler = pd.DataFrame(weather_df.groupby(['site_id','day','month'])['dew_temperature'].mean(),columns=[\"dew_temperature\"])\n    weather_df.update(due_temperature_filler,overwrite=False)\n\n    # Step 1\n    sea_level_filler = weather_df.groupby(['site_id','day','month'])['sea_level_pressure'].mean()\n    # Step 2\n    sea_level_filler = pd.DataFrame(sea_level_filler.fillna(method='ffill'),columns=['sea_level_pressure'])\n\n    weather_df.update(sea_level_filler,overwrite=False)\n\n    wind_direction_filler =  pd.DataFrame(weather_df.groupby(['site_id','day','month'])['wind_direction'].mean(),columns=['wind_direction'])\n    weather_df.update(wind_direction_filler,overwrite=False)\n\n    wind_speed_filler =  pd.DataFrame(weather_df.groupby(['site_id','day','month'])['wind_speed'].mean(),columns=['wind_speed'])\n    weather_df.update(wind_speed_filler,overwrite=False)\n\n    # Step 1\n    precip_depth_filler = weather_df.groupby(['site_id','day','month'])['precip_depth_1_hr'].mean()\n    # Step 2\n    precip_depth_filler = pd.DataFrame(precip_depth_filler.fillna(method='ffill'),columns=['precip_depth_1_hr'])\n\n    weather_df.update(precip_depth_filler,overwrite=False)\n\n    weather_df = weather_df.reset_index()\n    weather_df = weather_df.drop(['datetime','day','week','month'],axis=1)\n        \n    return weather_df","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Usages"},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train_df = pd.read_csv(DATA_PATH + 'weather_train.csv')\nweather_test_df = pd.read_csv(DATA_PATH + 'weather_test.csv')\n\nweather_train_df = fill_weather_dataset(weather_train_df)\nweather_test_df = fill_weather_dataset(weather_test_df)\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Discussion\n\nIf you have more ideas to fill these missing values accurately, please share in comments."}],"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}