{"cells":[{"metadata":{},"cell_type":"markdown","source":"# Filling Missing Values with Machine Learning \n\n* If you inspect the missing rows int the weather data, there are many instances where there is a missing value on one element of the row but not in the other elements of the same row. Example: value at column A in Row 5 is missing, but the other columns in row 5 are not missing. \n* Using this pattern, we can predict on the missing column using the non missing columns.\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"import pandas as pd\nimport numpy as np \nimport os \nimport seaborn as sns\nimport matplotlib.pyplot as plt\nfrom sklearn.preprocessing import LabelEncoder\nfrom sklearn.model_selection import train_test_split\nimport sklearn.metrics, sklearn.ensemble\nfrom sklearn.svm import SVR\nfrom lightgbm import LGBMRegressor\nfrom pathlib import Path\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Loading data\nIn this Kernal, we'll be filling the missing values in weather_train, also by combining values from the weather.test.csv. To predict for test weather data, just swap the two weather paths below."},{"metadata":{"trusted":true},"cell_type":"code","source":"data_dir = Path('../input/ashrae-energy-prediction')\n# this is for train. Just switch below to paths to fill missing data in test weather\nweather = pd.read_csv(data_dir/'weather_train.csv', parse_dates=['timestamp'], \n                      dtype={'site_id': np.uint16})\nweather_test = pd.read_csv(data_dir/'weather_test.csv', parse_dates=['timestamp'], \n                      dtype={'site_id': np.uint16})\n\nweather.head()\n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Adding in missing the timestamps\nAfter merging weather and train, there are many additional missing values created, because the weather file skips an hour's reading here and there which the train set contains (weather merges on train timestamp). Will be better if we can fill in these values too, whilst filling up the others with ML"},{"metadata":{"trusted":true},"cell_type":"code","source":"# running df that will be appended to \nrunning_batch = weather[weather['site_id'] == 1].set_index('timestamp').resample('h').mean().copy()\nrunning_batch['site_id'] = 1\n# for each site, resampling weather every one hour\nfor site in weather['site_id'].unique():\n    if site == 1:\n        continue\n\n    site_batch = weather[weather['site_id'] == site].set_index('timestamp').resample('1h').mean()   \n    site_batch['site_id'] = site\n    running_batch = running_batch.append(site_batch)\nprint(running_batch.isna().sum())\nprint('Weather has increased by {} samples'.format(len(running_batch)-len(weather)))\n    ","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Creating time features"},{"metadata":{"trusted":true},"cell_type":"code","source":"weather = running_batch.reset_index(level=0).copy()\nweather = weather.sort_values(['timestamp'])\n\nweather['hour']=weather['timestamp'].apply(lambda x: x.hour).astype(np.uint8)\nweather['month'] = weather['timestamp'].apply(lambda x: x.month).astype(np.uint8)\nweather['day']=weather['timestamp'].apply(lambda x: x.day).astype(np.uint8)\nweather['year']=(weather['timestamp'].apply(lambda x: x.year) - 2015).astype(np.uint8)\n\n\nweather_test['hour']=weather_test['timestamp'].apply(lambda x: x.hour).astype(np.uint8)\nweather_test['month'] = weather_test['timestamp'].apply(lambda x: x.month).astype(np.uint8)\nweather_test['day']=weather_test['timestamp'].apply(lambda x: x.day).astype(np.uint8)\nweather_test['year']=(weather_test['timestamp'].apply(lambda x: x.year) - 2015).astype(np.uint8)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"corr = weather.corr()\nsns.heatmap(corr, \n        xticklabels=corr.columns,\n        yticklabels=corr.columns)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Functions to train model on nonmissing data and predict on missing data\n\nProcedure of fill_col with LightGBM:\n* A target and feature list is provided. After which, train and test sets are created.\n* Train sets are created using Non missing values of the features and target. \n* The rows in which feature columns have missing values, those rows are not considered in train or test\n* Rows in which features are present, but not the target, that becomes test set\n\n\nIf you have more compute available, you can experiment with adding larger and smaller n_estimators and min_child_samples to the grid search. Because some trainsets, in the process of filling, can contain few features and less data. Grid search would most likely choose lower n_est and min_child_samples for smaller trainsets.\n"},{"metadata":{"trusted":true},"cell_type":"code","source":"# label encode columns [list] of df \ndef encode_df(df, columns):\n        onehot_features = []\n        for i, column in enumerate(columns):\n            lenc = LabelEncoder()\n            df[column] = lenc.fit_transform(df[column].values)\n            \n        return df\n    \n# get data from X columns test data \ndef extract_x_from_test(df, cols):\n    df = df[cols]\n    df.dropna(inplace=True)\n    return df \n\n# inputs: Dataframe, list, list, string. Fills rows in target column that are missing, by\n# training on non missing values of target and features \n# features only include continous features\ndef fill_col(df, X_from_testset, features, target,  cat_features_names):    \n    \n    # updating feature set names \n    features += cat_features_names\n    # onehot encoding cat columns \n    train_df  = df.copy()\n    train_df = encode_df(train_df, cat_features_names)\n    # extracting non-missing features and missing target rows for test \n    x_test =  train_df[(~train_df[features].isna()).all(axis=1)][train_df[target].isna()][features]\n    # extracting non-missing features and target rows for train\n    x_train =  train_df[(~train_df[features].isna()).all(axis=1)][~train_df[target].isna()]\n    if len(X_from_testset) !=0:\n        a = X_from_testset[features+[target]].dropna()\n        x_train = x_train.append(a)\n    \n    # data from test set     \n    # dataset specs \n    print('Training on {0:.5f} fraction, {1:} samples'.format(len(x_train)/len(train_df), len(x_train)))\n    print('Filling up {0:.5f} fraction, {1:} samples'.format(len(x_test)/len(train_df),len(x_test)))\n    \n    if len(x_train) == 0 or len(x_test) == 0:\n        print('Cannot fill any missing values.')\n        return df\n\n    y_train = x_train[target]\n    x_train=x_train[features]\n\n    # grid search cv\n    param_grid = {'num_leaves': [15], 'learning_rate':[0.25],\n                 'min_child_samples':[70], 'n_estimators':[45],\n                  'lambda_l2':[20], 'max_bin':[50], 'objective':['regression']}\n    \n    gbm = LGBMRegressor(categorical_features=cat_features_names)\n    gc = sklearn.model_selection.GridSearchCV(gbm, param_grid=param_grid, cv=4, verbose=1,\n        n_jobs=6, refit='r2', scoring=['neg_mean_absolute_error', 'r2'], \n                                              return_train_score=True)\n    # fits best scoring model \n    gc.fit(x_train, y_train)\n    \n    train_preds2 = gc.predict(X=x_train)\n    test_preds = gc.predict(X=x_test)\n    df.at[x_test.index, target] = test_preds\n    \n                  \n    results = pd.DataFrame.from_dict(gc.cv_results_)\n    results = results.sort_values(['rank_test_r2'])\n    metrics=['mean_test_r2', 'mean_test_neg_mean_absolute_error', 'mean_train_r2', 'mean_train_neg_mean_absolute_error' ]\n    eval_results = results.iloc[0][metrics]\n    \n    print(gc.best_params_)\n    print(eval_results)\n\n    return df\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print('Missing values at start')\nprint(weather.isna().sum())","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Fitting, predicting on missing values \n\n* Target -> Col we want to fillna\n* Features -> Features we want to use to predict on Target"},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"target = 'dew_temperature'\nfeatures = ['air_temperature', 'hour', 'month', 'year', 'day']\ncat_features = ['site_id']\n\nweather_f1 = fill_col(weather.copy(), weather_test.copy(), features, target, cat_features)  \n","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"target = 'cloud_coverage'\nfeatures=['dew_temperature', 'hour', 'month', 'wind_speed', 'year', 'day']\ncat_features = ['site_id']\nweather_f1 = fill_col(weather_f1.copy(), weather_test.copy(), features, target, cat_features)  \nweather_f1.isna().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"target = 'precip_depth_1_hr'\nfeatures=['dew_temperature', 'hour', 'month', 'wind_speed', 'cloud_coverage',\n          'year', 'day']\n\ncat_features = ['site_id']\nweather_f1 = fill_col(weather_f1.copy(), weather_test.copy(), features, target, cat_features)  \nweather_f1.isna().sum()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"target = 'sea_level_pressure'\nfeatures=['air_temperature', 'hour', 'month', 'wind_speed', 'cloud_coverage', 'precip_depth_1_hr', \n         'wind_direction', 'year', 'day']\ncat_features = ['site_id']\nweather_f1 = fill_col(weather_f1.copy(), weather_test.copy(), features, target, cat_features)  \nweather_f1.isna().sum()\n","execution_count":null,"outputs":[]},{"metadata":{"scrolled":false,"trusted":true},"cell_type":"code","source":"# predicting on due temperature again with missing values filled in\ntarget = 'dew_temperature'\nfeatures=['hour', 'month', 'wind_speed', 'cloud_coverage', 'precip_depth_1_hr', 'year', 'day']\ncat_features = ['site_id']\nweather_f1 = fill_col(weather_f1.copy(), weather_test.copy(), features, target, cat_features)  \nweather_f1.isna().sum()\n","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"target = 'wind_direction'\nfeatures=['hour', 'month', 'wind_speed', 'cloud_coverage', \n          'precip_depth_1_hr', 'dew_temperature', 'year', 'day']\ncat_features = ['site_id']\nweather_f1 = fill_col(weather_f1.copy(), weather_test.copy(), features, target, cat_features)  ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_f1.isna().sum()","execution_count":null,"outputs":[]},{"metadata":{"scrolled":true,"trusted":true},"cell_type":"code","source":"target = 'wind_direction'\nfeatures=['hour', 'month', 'year', 'day']\ncat_features = ['site_id']\nweather_f1 = fill_col(weather_f1.copy(), weather_test.copy(), features, target, cat_features)  \n","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Filling Nans with a mean filter\n* Value of missing value at position i, is `(val[i-1] + val[i+1])/2`. Window size used is three. "},{"metadata":{"trusted":true},"cell_type":"code","source":"# getting df series with col names and if they cocntain missing values \ncols_ismissing = weather_f1.isna().any(axis=0).reset_index(level=0)\n# getting missing column names \nmissing_cols = cols_ismissing[cols_ismissing[0] == True]['index'].values\nweather_f1 = weather_f1.sort_values(['timestamp'])\nweather_f2 = weather_f1.copy()\n\nfor site_id in weather_f1['site_id'].unique():\n    df = weather_f1[weather['site_id']==site_id].copy()\n    if df.isna().any(axis=0).sum() == 0:\n        continue\n        \n    weather_f2.at[df.index, missing_cols] = (df[missing_cols].fillna(method='bfill',limit =1) + \n                                             df[missing_cols].fillna(method='ffill', limit =1))/2\n    \nweather_f2.isna().sum()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"\nprevious_targets = []\nfor target in ['air_temperature','cloud_coverage','dew_temperature','precip_depth_1_hr',\n                'sea_level_pressure', 'wind_direction','wind_speed']:\n    print('Filling ', target)\n    features=['hour', 'month', 'year'] + previous_targets\n    cat_features = ['site_id', 'day'] \n\n    weather_f2 = fill_col(weather_f2.copy(), weather_test.copy(), features, target, cat_features)\n    if target not in ['sea_level_pressure', 'wind_direction']:\n        previous_targets.append(target)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_f2.isna().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_f2.drop(['day', 'month', 'hour', 'year'], axis=1, inplace=True)\nprint(weather_f2.columns)\nassert len(weather) == len(weather_f2)\nweather_f2.to_csv('weather_train_filled.csv', index=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Conclusion:\n* The performance of filling certain weather items are good and some are not great (eg. wind direction has mae of ~80 degrees). Prediction of precip_depth_1_hr had the worst performance with an r2 of 0.0399225 (Predictions explain variance of 3.9% of variance) and mae of 1.15764. Meanwhile, dew temperature showed r2 of ~0.840953 and mae of ~ 2.9 and cloud_coverage r2 of ~0.52 and mae of ~1.28957. \n* This approach sort of gives you more claritiy on how accurate your fills for NaNs are \n* Filling of missing values based on seasonality and temporal promixity  \n* I did recieve a slight boost in my performance using this filled dataset as compared to mean filling, but much less of an improvement than I expected to see after this. If weather had more of an effect of target readings then this approach might have held more value.\n* This method might be a little overkill to fill missing values compared to other approaches seen, but let me know what you think!\n\n"}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"codemirror_mode":{"name":"ipython","version":3},"file_extension":".py","mimetype":"text/x-python","name":"python","nbconvert_exporter":"python","pygments_lexer":"ipython3","version":"3.7.3"}},"nbformat":4,"nbformat_minor":1}