{"cells":[{"metadata":{},"cell_type":"markdown","source":"# ASHRAE - Great Energy Prediction III\n# This kernel is not finished yet."},{"metadata":{},"cell_type":"markdown","source":"### Data\nTarget Variable\n- meter_reading\n\nCategorical Feature - Train & Test Data\n- building_id : Foreign key for the building metadata.\n- meter : The meter id code. Not every building has all meter types.\n    - 0: electrocity\n    - 1: chilledwater\n    - 2: steam\n    - 3: hotwater\n\nCatergorical Feature - Building Meta Data\n- site_id: Foreign key for the weather files.\n- building_id: Foreign key for 'training.csv'\n- primary_use: Indicator of the primary  \n\nCategorical Feature - Weather Train & Test Data\n- cloud_corverage\n- wind_direction"},{"metadata":{},"cell_type":"markdown","source":"# Import Module"},{"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 stat\n\nimport matplotlib.pyplot as plt\nimport seaborn as sns\n\nimport warnings\nwarnings.filterwarnings('ignore')\nimport datetime\n\nimport gc","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Load Dataset"},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"building_data = pd.read_csv('../input/ashrae-energy-prediction/building_metadata.csv')\ntrain = pd.read_csv('../input/ashrae-energy-prediction/train.csv')\ntest = pd.read_csv('../input/ashrae-energy-prediction/test.csv')\nweather_train = pd.read_csv('../input/ashrae-energy-prediction/weather_train.csv')\nweather_test = pd.read_csv('../input/ashrae-energy-prediction/weather_test.csv')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Reducing Memory Size"},{"metadata":{},"cell_type":"markdown","source":"Defined Function"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"## Function to reduce the DF size\ndef reduce_mem_usage(df, verbose=True):\n    numerics = ['int16', 'int32', 'int64', 'float16', 'float32', 'float64']\n    start_mem = df.memory_usage().sum() / 1024**2    \n    for col in df.columns:\n        col_type = df[col].dtypes\n        if col_type in numerics:\n            c_min = df[col].min()\n            c_max = df[col].max()\n            if str(col_type)[:3] == 'int':\n                if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max:\n                    df[col] = df[col].astype(np.int8)\n                elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:\n                    df[col] = df[col].astype(np.int16)\n                elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:\n                    df[col] = df[col].astype(np.int32)\n                elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:\n                    df[col] = df[col].astype(np.int64)  \n            else:\n                if c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max:\n                    df[col] = df[col].astype(np.float16)\n                elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:\n                    df[col] = df[col].astype(np.float32)\n                else:\n                    df[col] = df[col].astype(np.float64)    \n    end_mem = df.memory_usage().sum() / 1024**2\n    if verbose: print('Mem. usage decreased to {:5.2f} Mb ({:.1f}% reduction)'.format(end_mem, 100 * (start_mem - end_mem) / start_mem))\n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"building_data = reduce_mem_usage(building_data)\ntrain = reduce_mem_usage(train)\ntest = reduce_mem_usage(test)\nweather_train = reduce_mem_usage(weather_train)\nweather_test = reduce_mem_usage(weather_test)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Check Dataset"},{"metadata":{},"cell_type":"markdown","source":"Defined Function"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"def resumetable(df):\n    print(f\"Dataset Shape: {df.shape}\")\n    summary = pd.DataFrame(df.dtypes,columns=['dtypes'])\n    summary = summary.reset_index()\n    summary['Name'] = summary['index']\n    summary = summary[['Name','dtypes']]\n    summary['Min'] = df.min().values\n    summary['Max'] = df.max().values\n    summary['Missing'] = df.isnull().sum().values    \n    summary['Uniques'] = df.nunique().values\n\n    return summary","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"resumetable(building_data)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print('Primary Using Case:',building_data['primary_use'].unique())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"- When i checked building meta data, there were a total of 1,449 buildings. The building was mainly used for 16 purposes.\n- The area ranged from at least 283 square meters to 875,000 square meters.\n- The building's model years varied from 1900 to 2017, but 774 missing values existed.\n- The building existed from the first to the 26th floors and 75 percent of the total was missing value."},{"metadata":{"trusted":true},"cell_type":"code","source":"#resumetable(train)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"- The observation period for the training data was from Jan. 1, 2016 to Dec. 31, 2016."},{"metadata":{"trusted":true},"cell_type":"code","source":"resumetable(weather_train)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"- Cloud Coverage are divided into nine classes , but about 50 percent of the total is missing value.\n- An observation with precipitation of -1 was present. This is expected to be a sensor error."},{"metadata":{},"cell_type":"markdown","source":"# Sectional EDA"},{"metadata":{},"cell_type":"markdown","source":"## Timestamp Processing"},{"metadata":{"trusted":true},"cell_type":"code","source":"train['date'] = pd.to_datetime(train['timestamp'].str.split(\" \", expand = True)[0])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Target Distribution\n### Meter Reading Quantiles\nBefore show a plot of the target variable, let's look at the Quantile first."},{"metadata":{"trusted":true},"cell_type":"code","source":"print(\"Meter Reading (Target Variable) Quantiles:\")\nprint(train['meter_reading'].quantile([0.01, 0.025, 0.1, 0.25, 0.5, 0.75, 0.9, 0.975, 0.99]))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Ploting Meter Reading Values Distribution"},{"metadata":{"trusted":true},"cell_type":"code","source":"plt.figure(figsize = (18,12))\nplt.suptitle('Meter Reading Values Distribution', fontsize = 22)\nplt.subplot(221)\nsns.distplot(train['meter_reading'], hist = False,kde_kws={\"shade\": True})\nplt.title('Meter Reading Distribution', fontsize = 18)\nplt.xlabel(\"\")\nplt.ylabel('Probability', fontsize = 15)\n\nplt.subplot(222)\nsns.distplot(np.log1p(train['meter_reading']), hist = False,kde_kws={\"shade\": True})\nplt.title('Meter Reading (Log1p) Distribution', fontsize = 18)\nplt.xlabel(\"\")\nplt.ylabel(\"Probability\",fontsize = 15)\n\nplt.subplot(223)\ng = sns.countplot(train['meter'])\nplt.xticks(np.arange(4),labels =['Electricity','Chilledwater','Steam','Hotwater'])\ng.set_title('Meter Type Countplot', fontsize = 18)\ng.set_xlabel('Type', fontsize = 15)\ng.set_ylabel('Count', fontsize = 15)\n\ntmp0 = train.loc[train['meter'] == 0]['meter_reading'].max()\ntmp1 = train.loc[train['meter'] == 1]['meter_reading'].max()\ntmp2 = train.loc[train['meter'] == 2]['meter_reading'].max()\ntmp3 = train.loc[train['meter'] == 3]['meter_reading'].max()\n\ntmp_meter = ['Electricity','Chilledwater','Steam','Hotwater']\ntmp_meter_reading = [tmp0, tmp1, tmp2, tmp3]\n\ntmp = pd.DataFrame(tmp_meter,tmp_meter_reading)\ntmp = tmp.reset_index()\ntmp.columns = ['meter','max_meter_reading']\n\ngt = g.twinx()\ngt = sns.pointplot(x = 'max_meter_reading', y = 'meter', data = tmp, color = 'black',\n                  order = ['Electricity','Chilledwater','Steam','Hotwater'], legend = False)\ngt.set_ylabel('Max Meter Reading by Meter Type',fontsize = 15)\n\nplt.subplot(224)\nsns.boxplot(x= 'meter', y = 'meter_reading', data = train)\nplt.xticks(np.arange(4),labels =['Electricity','Chilledwater','Steam','Hotwater'])\nplt.title('Meter Reading Distribution by Meter Type', fontsize = 18)\nplt.xlabel('Meter Type', fontsize = 15)\nplt.ylabel('Meter Reading Values', fontsize = 15)\n\nplt.subplots_adjust(hspace = 0.85, top = .9)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"- The distribution of the target variable shows that about 25% of the total is 0, and the frequency of the value increases after over 0.5 and 1.5.\n- If Meter Type is steam type, it can be seen that the target variable is higher than other types."},{"metadata":{"trusted":true},"cell_type":"code","source":"tmp_reading = train.groupby([train['date'].dt.date,'meter'])['meter_reading'].sum().reset_index()\n\nplt.figure(figsize = (18,12))\nplt.suptitle('Time series Meter Reading Values Distribution', fontsize = 22)\nplt.subplot(221)\nsns.lineplot(x = 'date', y = 'meter_reading', data = tmp_reading.loc[tmp_reading['meter'] == 0])\nplt.title('Distribution of Meter Reading for Time Series \\n Electricity', fontsize = 18)\nplt.xlabel('Date', fontsize = 15)\nplt.ylabel('Meter Reading', fontsize = 15)\n\nplt.subplot(222)\nsns.lineplot(x = 'date', y = 'meter_reading', data = tmp_reading.loc[tmp_reading['meter'] == 1], color = 'orange')\nplt.title('Distribution of Meter Reading for Time Series \\n Chilledwater', fontsize = 18)\nplt.xlabel('Date', fontsize = 15)\nplt.ylabel('Meter Reading', fontsize = 15)\n\nplt.subplot(223)\nsns.lineplot(x = 'date', y = 'meter_reading', data = tmp_reading.loc[tmp_reading['meter'] == 2], color = 'green')\nplt.title('Distribution of Meter Reading for Time Series \\n Steam', fontsize = 18)\nplt.xlabel('Date', fontsize = 15)\nplt.ylabel('Meter Reading', fontsize = 15)\n\nplt.subplot(224)\nsns.lineplot(x = 'date', y = 'meter_reading', data = tmp_reading.loc[tmp_reading['meter'] == 3], color = 'red')\nplt.title('Distribution of Meter Reading for Time Series \\n Hotwater', fontsize = 18)\nplt.xlabel('Date', fontsize = 15)\nplt.ylabel('Meter Reading', fontsize = 15)\n\nplt.subplots_adjust(hspace = 0.85, top = .8)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"- Electricity Meter Reading can be seen to increase significantly after May.\n- Childwater meter reading can be seen to increase rapidly since September and become normal after October.\n- Steam meter reading has increased by a large margin since March and has shown a normal distribution since June. One thing that stands out is that you can see a sharp increase in November for a short period of time.\n- Hotwater meter reading tended to be low in summer and high in winter."},{"metadata":{},"cell_type":"markdown","source":"## Building id\nThe total number of buildings appeared to be 1,449 and we will first check which type of meter there are many."},{"metadata":{"trusted":true},"cell_type":"code","source":"tmp = train.drop_duplicates(['building_id'])[['building_id','meter']]\n\nplt.figure(figsize = (6,4))\ng = sns.countplot(tmp['meter'])\nplt.xticks(np.arange(4),labels =['Electricity','Chilledwater','Steam','Hotwater'])\ng.set_title('Meter Type by Building', fontsize = 18)\ng.set_xlabel('Type', fontsize = 15)\ng.set_ylabel('Count', fontsize = 15)\n\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"- Most meters can be seen using electricity."},{"metadata":{},"cell_type":"markdown","source":"# Merging Dataset\n- Building_metadata.csv is the data that contains the characteristics of a building. Therefore, the train data and building_data will be combined based on building_id.\n\n## Questions\nThe merged data will answer the following hypotheses.\n- The meter reading will vary depending on the main purpose of use.\n- The meter reading will vary depending on the floor area.\n- The building year will affect meter reading.\n- The number of floors in the building will affect meter reading.\n- There will be a difference in meter reading by site."},{"metadata":{"trusted":true},"cell_type":"code","source":"merged_train = pd.merge(train, building_data, on = 'building_id', how = 'left')\nprint('Merged Train Data Shape:',merged_train.shape)\nmerged_train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"tmp = merged_train.drop_duplicates(['building_id'])[['building_id','primary_use']]\n\nplt.figure(figsize = (20,20))\nplt.subplot(221)\ng = sns.countplot(x = tmp['primary_use'])\ng.set_title('Primary Used Type by Building', fontsize = 18)\ng.set_xlabel('Primary Use', fontsize = 15)\ng.set_ylabel('Count', fontsize = 15)\ng.set_xticklabels(g.get_xticklabels(), rotation = 90)\n\nmax_meter_reading = []\n\nfor i in merged_train['primary_use'].unique().tolist():\n    max_meter_reading.append(merged_train.loc[merged_train['primary_use'] == i]['meter_reading'].max())\n    \nmax_meter_reading\n\ntmp2 = pd.DataFrame(merged_train['primary_use'].unique().tolist(), max_meter_reading)\ntmp2 = tmp2.reset_index()\ntmp2.columns = ['meter_reading','primary_use']\ngt = g.twinx()\ngt = sns.pointplot(y = 'meter_reading', x = 'primary_use', data = tmp2, color = 'black',\n                  order = merged_train['primary_use'].unique().tolist(), legend = False)\ngt.set_ylabel('Max Meter Reading by Primary Use Type',fontsize = 15)\ngt.set_xticklabels(gt.get_xticklabels(), rotation = 90)\n\nplt.subplot(222)\n\ntmp = merged_train.drop_duplicates(['building_id'])[['building_id','year_built']]\ntmp = tmp.groupby(['year_built'])['building_id'].count()\ntmp = pd.DataFrame(tmp).reset_index()\ntmp.columns = ['year_built','count']\ntmp['year_built'] = tmp['year_built'].astype(int)\ntmp = tmp.sort_values(by = 'count', ascending= False)\ntmp_top20 = tmp.head(20)\n\ng = sns.barplot(x = tmp_top20['year_built'], y = tmp_top20['count'])\ng.set_title('Built Year Countplot', fontsize = 18)\ng.set_xlabel('Year', fontsize = 15)\ng.set_ylabel('Count', fontsize = 15)\ng.set_xticklabels(g.get_xticklabels(), rotation = 90)\n\nmax_meter_reading = []\n\nfor i in tmp_top20['year_built']:\n    max_meter_reading.append(merged_train.loc[merged_train['year_built'] == i]['meter_reading'].max())\n\ntmp_top20['max_meter_reading'] = max_meter_reading\n\ngt = g.twinx()\ngt = sns.pointplot(y= 'max_meter_reading', x = 'year_built', data = tmp_top20, color = 'black', legend = False)\ngt.set_xlabel('Year',fontsize = 15)\ngt.set_ylabel('Max Meter Reading by Built Year', fontsize = 15)\ngt.set_xticklabels(gt.get_xticklabels(), rotation = 90)\n\nplt.subplot(223)\ntmp = merged_train.drop_duplicates(['building_id'])[['building_id','site_id']]\ng = sns.countplot(x = tmp['site_id'])\ng.set_title('Site id by Building', fontsize = 18)\ng.set_ylabel('Count', fontsize = 15)\ng.set_xlabel('Site id', fontsize = 15)\n\nmax_meter_reading = []\n\nfor i in merged_train['site_id'].unique().tolist():\n    max_meter_reading.append(merged_train.loc[merged_train['site_id'] == i]['meter_reading'].max())\n\ntmp3 = pd.DataFrame(merged_train['site_id'].unique().tolist(), max_meter_reading)\ntmp3 = tmp3.reset_index()\ntmp3.columns = ['max_meter_reading','site_id']\ngt = g.twinx()\ngt = sns.pointplot(y = 'max_meter_reading', x = 'site_id', data = tmp3, color = 'black',\n                  order = merged_train['site_id'].unique().tolist(), legend = False)\ngt.set_ylabel('Max Meter Reading by Site id',fontsize = 15)\n\nplt.subplot(224)\ntmp = np.sort(merged_train['floor_count'].unique())\ntmp2 = []\nfor i in tmp:\n    tmp2.append(merged_train.loc[merged_train['floor_count'] == i]['meter_reading'].max())\n\ng = sns.countplot(merged_train['floor_count'])\ng.set_title('Floor Count by Building Countplot without NaN', fontsize = 18)\ng.set_xlabel('Floor Count', fontsize = 15)\ng.set_ylabel('Count', fontsize = 15)\n\ngt = g.twinx()\ngt = sns.pointplot(x = tmp, y = tmp2, color = 'black')\ngt.set_ylabel('Max Meter Reading by Floor Count', fontsize = 15)\n\nplt.subplots_adjust(hspace = .6, top = .9)\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"- The main use of buildings can be seen as the largest number of educational buildings. It can also be seen that the maximum value of the target variable is included here.\n- It can be seen that the maximum meter_reading of buildings used outside of educational purposes is almost similar.\n- This is a visualization of the top 20 years when the most buildings were built except NaN. Except for NaN, the largest number of buildings were built in 1976.\n- It can be seen that in 1930 there is a relatively high maximum meter_reading. It can be seen that 2017 and 1976 are the next highest.\n- Observations with maximum meter reading occurred in site 13.\n- In particular, the maximum meter reading of the two floor building was the highest."},{"metadata":{},"cell_type":"markdown","source":"# Weather Data\nWe will check the weather data by referring to the [ASHRAE - Missing Weather Data Handling](https://www.kaggle.com/aitude/ashrae-missing-weather-data-handling) Notebook."},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train = pd.read_csv('../input/ashrae-energy-prediction/weather_train.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(\"Weather Train Dataset Shape:\", weather_train.shape)\nweather_train.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Check Missing Values\nFirst, let's define a function to check missing values."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"def missing_statistics(df):\n    statistics = pd.DataFrame(df.isnull().sum()).reset_index()\n    statistics.columns = ['Column Name','Missing Values']\n    statistics['Total Rows'] = df.shape[0]\n    statistics['% Missing'] = round((statistics['Missing Values']/statistics['Total Rows'])*100,2)\n    return statistics","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Missing Hours\nThe csv file is the data for the hourly weather in 16 sites in 2016.Therefore, there must be 140,544 rows. However, there are only 139,773 rows in a given file. 771 hours is missing."},{"metadata":{"trusted":true},"cell_type":"code","source":"time_format = \"%Y-%m-%d %H:%M:%S\"\n\nstart_date = datetime.datetime.strptime(weather_train['timestamp'].min(), time_format)\nend_date = datetime.datetime.strptime(weather_train['timestamp'].max(), time_format)\ntotal_hours = int(((end_date - start_date).total_seconds() + 3600)/ 3600)\nhour_list = [(end_date - datetime.timedelta(hours = x)).strftime(time_format) for x in range(total_hours)]\nprint(hour_list[:3])\n\nmissing_hours = []\nfor site_id in np.sort(merged_train['site_id'].unique()):\n    site_hours = np.array(weather_train.loc[weather_train['site_id'] == site_id]['timestamp'])\n    new_rows = pd.DataFrame(np.setdiff1d(hour_list,site_hours), columns = ['timestamp'])\n    new_rows['site_id'] = site_id\n    weather_train = pd.concat([weather_train,new_rows])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Count Missing Values"},{"metadata":{"trusted":true},"cell_type":"code","source":"missing_statistics(weather_train)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Day,Week & Month Columns"},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train['datetime'] = pd.to_datetime(weather_train['timestamp'])\nweather_train['day'] = weather_train['datetime'].dt.day\nweather_train['week'] = weather_train['datetime'].dt.week\nweather_train['month'] = weather_train['datetime'].dt.month","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Reset Index for Fast Update"},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train = weather_train.set_index(['site_id','day','month'])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Fill Air Temperature\nMissing values for temperature will be filled with <b>daily average temperatures of the month</b>. Because the temperature varies greatly from season to season, it is not good to use the average annual temperature."},{"metadata":{},"cell_type":"markdown","source":"### Fill Cloud Coverage\nAlmost 50% data is missing. In addition, values are continuously missing. So, calculate daily average cloud coverage of the month and fill rest missing values with last valid observation."},{"metadata":{"trusted":true},"cell_type":"code","source":"# Calculate daily average cloud coverage of the month\ncloud_coverage_filler = weather_train.groupby(['site_id','day','month'])['cloud_coverage'].mean()\n# Fill rest missing values with last valid observation\ncloud_coverage_filler = pd.DataFrame(cloud_coverage_filler.fillna(method='ffill'),columns=[\"cloud_coverage\"])\n\nweather_train.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":"dew_temperature_filler = pd.DataFrame(weather_train.groupby(['site_id','day','month'])['dew_temperature'].mean(), columns = ['dew_temperature'])\nweather_train.update(dew_temperature_filler, overwrite = False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Fill Sea level Pressure"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Calculate daily average Sea level Pressure of the month\nsea_level_filler = weather_train.groupby(['site_id','day','month'])['sea_level_pressure'].mean()\n# Fill rest missing values with last valid observation\nsea_level_filler = pd.DataFrame(sea_level_filler.fillna(method = 'ffill'), columns = ['sea_level_pressure'])\n\nweather_train.update(sea_level_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_train.groupby(['site_id','day','month'])['wind_speed'].mean(), columns = ['wind_speed'])\nweather_train.update(wind_speed_filler, overwrite = False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Fill precip_depth_1_hr\nThis value indicates very little rain and how much snow it has fallen. This value is greater than zero, but it will not be able to be measured with a rain gauge."},{"metadata":{"trusted":true},"cell_type":"code","source":"# Calculate daily average Precipitation Depth 1 hour of the month\nprecip_depth_filler = weather_train.groupby(['site_id','day','month'])['precip_depth_1_hr'].mean()\n# Fill rest missing values with last valid observation\nprecip_depth_filler = pd.DataFrame(precip_depth_filler.fillna(method = 'ffill'), columns = ['precip_depth_1_hr'])\n\nweather_train.update(precip_depth_filler, overwrite = False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Remove Useless Feature"},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train = weather_train.reset_index()\nweather_train.drop(columns = ['datetime','day','week','month'], inplace = True)","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}