{"cells":[{"metadata":{},"cell_type":"markdown","source":"# Fast prepare data with forecast null features"},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"from tqdm import tqdm_notebook\n\nimport pandas as pd\npd.set_option('display.max_columns',100)\npd.set_option('display.max_rows',150)\npd.set_option('display.float_format', lambda x: '%.5f' % x)\nimport numpy as np\nimport matplotlib.pyplot as plt\n%matplotlib inline\nimport seaborn as sns\nfrom time import time\nimport datetime\nimport gc\n\nPATH = '/kaggle/input/ashrae-energy-prediction/'","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# metadata df"},{"metadata":{"trusted":true},"cell_type":"code","source":"metadata_dtype = {'site_id':\"uint8\",'building_id':'uint16','square_feet':'float32','year_built':'float32','floor_count':\"float16\"}\nmetadata = pd.read_csv(PATH+\"building_metadata.csv\",dtype=metadata_dtype)\nmetadata.info(memory_usage='deep')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# weather train/test df"},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_dtype = {\"site_id\":\"uint8\"}\nweather_train = pd.read_csv(PATH+\"weather_train.csv\",parse_dates=['timestamp'],dtype=weather_dtype)\nweather_test = pd.read_csv(PATH+\"weather_test.csv\",parse_dates=['timestamp'],dtype=weather_dtype)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# train/test df"},{"metadata":{"trusted":true},"cell_type":"code","source":"train_dtype = {'meter':\"uint8\",'building_id':'uint16','meter_reading':\"float32\"}\ntrain = pd.read_csv(PATH+\"train.csv\",parse_dates=['timestamp'],dtype=train_dtype)\ntest_dtype = {'meter':\"uint8\",'building_id':'uint16'}\ntest_cols_to_read = ['building_id','meter','timestamp']\ntest = pd.read_csv(PATH+\"test.csv\",parse_dates=['timestamp'],usecols=test_cols_to_read,dtype=test_dtype)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"Submission = pd.DataFrame(test.index,columns=['row_id'])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Null rows per columns in df's"},{"metadata":{"trusted":true},"cell_type":"code","source":"train.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"metadata.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_test.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# dim target\ntarget = 'meter_reading'","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# helper functions"},{"metadata":{"trusted":true},"cell_type":"code","source":"SEED = 17\n\n# remove unnecessary columns in the list\ndef set_to_list(cols, excepted):\n    return list(set(cols) - set(excepted))\n\n# get string variables\ndef get_update_string_variables(df, exclude_features):\n        string_variables = set_to_list(list(df.select_dtypes('object').columns), exclude_features)\n        return string_variables\n\n# get null variables\ndef get_update_null_variables(df, exclude_features, target):\n    with_null_fields = set_to_list(\n        set_to_list(\n            df.columns[df.isnull().any()]\n            , [target]\n        )\n        , exclude_features\n    )\n    return with_null_fields\n\n# dummies and update var's\ndef set_dummies(df, variables, cat_features, features):\n    if len(variables)>0:\n        print('Dummies preprocess for:', ', '.join(variables))\n        # save change\n        dummies_columns = pd.get_dummies(df[variables]).columns\n\n        features = list(\n            features + list(\n                dummies_columns\n            )\n        )\n\n        cat_features = list(\n            cat_features + list(\n                dummies_columns\n            )\n        )\n\n        df = pd.get_dummies(df, columns = variables)\n        features = set_to_list(features, variables)\n        cat_features = set_to_list(cat_features, variables)\n    return df, features, cat_features\n\n\n# Model-driven Imputation w RF\n# exclude object and datetime\ndef set_predict_null_values(\n    df,\n    features,\n    exclude_features,\n    features_for_predict,\n    # ensemble = []\n    type_predict, # Regressor, Classifier\n    n_estimators = 30,\n    n_jobs = 20,\n):\n\n    from sklearn.ensemble import RandomForestClassifier, RandomForestRegressor\n\n    if len(features) == 0:\n        features = list(df.columns)\n    if len(features_for_predict) == 0:\n        exclude_features = []\n        with_null_fields = get_update_null_variables(df, exclude_features)\n    else:\n        with_null_fields = features_for_predict\n\n    from tqdm import tqdm_notebook\n    if type_predict == 'Regressor':\n        model = RandomForestRegressor(\n            n_estimators=n_estimators, n_jobs = n_jobs, random_state = SEED\n        )\n    elif type_predict == 'Classifier':\n        model = RandomForestClassifier(\n            n_estimators=n_estimators, n_jobs = n_jobs, random_state = SEED\n        )\n\n    df_null_stat = pd.DataFrame(df[features_for_predict].isnull().sum(), columns = ['CountNull'])\n    df_null_stat.sort_values('CountNull', inplace = True)\n    predict_null_fields = list(df_null_stat[df_null_stat['CountNull'] > 0].index)\n\n    print('Apply encoder for this columns, they have object or datetime variables',', '.join(df[features_for_predict].select_dtypes(include=['object', 'datetime']).columns))\n    print(list(df[features_for_predict].select_dtypes(include=['object', 'datetime']).columns))\n\n    for c in df[features_for_predict].select_dtypes(include=['object', 'datetime']).columns:\n        if c in predict_null_fields:\n            predict_null_fields.remove(c)\n            print('Remove obj/datetime var:', c)\n\n    print('\\nFeatures changes:',', '.join(predict_null_fields))\n    features_wo_null = set_to_list(\n        df[set_to_list(features, with_null_fields)].columns\n        , df.select_dtypes(include=['object', 'datetime']).columns\n    )\n\n    for c in tqdm_notebook(predict_null_fields):\n        x_train = df[df[c].isnull()==False][features_wo_null]\n        x_test = df[df[c].isnull()==True][features_wo_null]\n        y_train = df[df[c].isnull()==False][c]\n\n        print(\n            'Predict:',c,'\\nCount Null string:', x_test.shape[0],\n            '\\nFeatures wo null in train:', ', '.join(features_wo_null),\n        )\n\n        model.fit(x_train, y_train)\n        y_pred = model.predict(x_test)\n        x_test[c] = y_pred\n\n        df_without_null_c = pd.DataFrame(\n            pd.concat([df[df[c].isnull()==False][c], x_test[c]])\n            , columns = [c]\n        )\n\n        df[c] = df_without_null_c.sort_index()[c]\n\n        features_wo_null.append(c)\n        \n    return df","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Predict null features per df\n\n* sort the columns by the number of missing values (ascending)\n* we forecast one by one, adding a forecast column when predicting the next value\n* and so we do until we run out of null features/values"},{"metadata":{"trusted":true},"cell_type":"code","source":"df = metadata.copy()\nexclude_features = []\ncat_features = []\nfeatures = list(df.columns)\nnull_variables = get_update_null_variables(df, exclude_features, target)\ndf, features, cat_features = set_dummies(\n    df = df,\n    variables = ['primary_use'],\n    cat_features = cat_features,\n    features = features\n)\n\ndf = set_predict_null_values(\n    df = df,\n    features = features,\n    exclude_features = exclude_features,\n    features_for_predict = null_variables,\n    type_predict = 'Regressor',\n    n_estimators=60,\n    n_jobs=40,\n)\n\nmetadata = df.copy()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# For better weather forecast we add temporary time variables"},{"metadata":{"trusted":true},"cell_type":"code","source":"df = weather_train.copy()\n\ndf = pd.merge(\n    df,\n    pd.merge(\n        train, metadata, on = ['building_id'], how = 'left'\n    )[['site_id', 'timestamp']].drop_duplicates(),\n    on = ['site_id', 'timestamp'],\n    how = 'outer'\n)\n\ndf.sort_values(['timestamp','site_id'], inplace=True)\n\nexclude_features = []\ndf['Month'] = df['timestamp'].dt.month.astype(\"uint8\")\ndf['DayOfMonth'] = df['timestamp'].dt.day.astype(\"uint8\")\ndf['DayOfWeek'] = df['timestamp'].dt.dayofweek.astype(\"uint8\")\ndf['Hour'] = df['timestamp'].dt.hour.astype(\"uint8\")\n\nfeatures = list(df.columns)\nnull_variables = get_update_null_variables(df, exclude_features, target)\n\ndf = set_predict_null_values(\n    df = df,\n    features = features,\n    exclude_features = exclude_features,\n    features_for_predict = null_variables,\n    type_predict = 'Regressor',\n    n_estimators=60,\n    n_jobs=40,\n)\n\nweather_train = df.copy()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# save test copy\nweather_test_check = weather_test.copy()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df = weather_test.copy()\ndf = pd.merge(\n    df,\n    pd.merge(\n        test,\n        metadata,\n        on = ['building_id'],\n        how = 'left'\n    )[['site_id', 'timestamp']].drop_duplicates(),\n    on = ['site_id', 'timestamp'],\n    how = 'outer'\n)\n\ndf.sort_values(['timestamp','site_id'], inplace=True)\n\nexclude_features = []\n\ndf['Month'] = df['timestamp'].dt.month.astype(\"uint8\")\ndf['DayOfMonth'] = df['timestamp'].dt.day.astype(\"uint8\")\ndf['DayOfWeek'] = df['timestamp'].dt.dayofweek.astype(\"uint8\")\ndf['Hour'] = df['timestamp'].dt.hour.astype(\"uint8\")\nfeatures = list(df.columns)\nnull_variables = get_update_null_variables(df, exclude_features, target)\n\ndf = set_predict_null_values(\n    df = df,\n    features = features,\n    exclude_features = exclude_features,\n    features_for_predict = null_variables,\n    type_predict = 'Regressor',\n    n_estimators=60,\n    n_jobs=40,\n)\n\nweather_test = df.copy()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# an example of a forecast"},{"metadata":{"trusted":true},"cell_type":"code","source":"# weather_train\nupdate_feat = ['air_temperature', 'dew_temperature', 'wind_speed', 'wind_direction', 'sea_level_pressure', 'precip_depth_1_hr', 'cloud_coverage']\ndf.rename(columns = {f:'pred_'+f for f in update_feat}, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"df = pd.merge(df, weather_test_check, on = ['site_id', 'timestamp'], how='outer')","execution_count":null,"outputs":[]},{"metadata":{"scrolled":false,"trusted":true},"cell_type":"code","source":"# measures = 'dew_temperature'\nfor meas in update_feat:\n    \n    df[\n        (df['site_id']==7) &\\\n        (df['Month']==4)\n    ][['pred_'+meas, meas]].reset_index(drop=True).plot()\n    plt.show();","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Update time variables"},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train.drop(['Month', 'DayOfMonth', 'DayOfWeek','Hour'], axis=1, inplace=True)\nweather_test.drop(['Month', 'DayOfMonth', 'DayOfWeek','Hour'], axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Not enough memory for commit, so commented out a small number of time features.\n\nfrom tqdm import tqdm_notebook\nfor df in tqdm_notebook([train, test]):\n    df['Month'] = df['timestamp'].dt.month.astype(\"uint8\")\n    df['DayOfMonth'] = df['timestamp'].dt.day.astype(\"uint8\")\n#     df['DayOfWeek'] = df['timestamp'].dt.dayofweek.astype(\"uint8\")\n#     df['Hour'] = df['timestamp'].dt.hour.astype(\"uint8\")\n#     df['is_year_start'] = df['timestamp'].dt.is_year_start.astype(\"uint8\")\n#     df['is_year_end'] = df['timestamp'].dt.is_year_end.astype(\"uint8\")\n#     df['weekofyear'] = df['timestamp'].dt.weekofyear.astype(\"uint8\")\n#     df['is_month_end'] = df['timestamp'].dt.is_month_end.astype(\"uint8\")\n#     df['is_month_start'] = df['timestamp'].dt.is_month_start.astype(\"uint8\")\n#     df['dayofyear'] = df['timestamp'].dt.dayofyear.astype(\"uint16\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# meter dummies"},{"metadata":{"trusted":true},"cell_type":"code","source":"train = pd.get_dummies(train, columns = ['meter'])\ntest = pd.get_dummies(test, columns = ['meter'])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# concat weather + metadata + train/test"},{"metadata":{"trusted":true},"cell_type":"code","source":"train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"weather_train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"metadata.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"cols_float32 = ['square_feet', 'year_built', 'floor_count'] \nweather_cols = ['air_temperature', 'cloud_coverage', 'dew_temperature', 'precip_depth_1_hr', 'sea_level_pressure', 'wind_direction', 'wind_speed']\nmetadata[cols_float32] = metadata[cols_float32].astype('float32')\nfor df in tqdm_notebook([weather_train, weather_test]):\n    df[weather_cols] = df[weather_cols].astype('float32')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"gc.collect();","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(train.shape)\ntrain = pd.merge(train, metadata, on = ['building_id'], how = 'left')\ntrain = pd.merge(train, weather_train, on = ['site_id', 'timestamp'], how = 'left')\nprint(train.shape)\n\ndel weather_train;\ngc.collect();","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(test.shape)\ntest = pd.merge(test, metadata, on = ['building_id'], how = 'left')\ntest = pd.merge(test, weather_test, on = ['site_id', 'timestamp'], how = 'left')\nprint(test.shape)\n\ndel weather_test;\ngc.collect();","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# count null rows per columns"},{"metadata":{"trusted":true},"cell_type":"code","source":"train.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# get cyclical values for time features"},{"metadata":{"trusted":true},"cell_type":"code","source":"# pd.concat([train, test], axis=0, sort=False)[\n#     [\n#         'Month'\n#         , 'DayOfMonth'\n# #         , 'DayOfWeek'\n# #         , 'Hour'\n# #         , 'weekofyear'\n# #         , 'dayofyear'\n#         , 'year_built'\n#     ]\n# ].nunique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def get_cyclical_encode(\n    df,\n    cols_maxval = {},\n    is_drop = False\n):\n    df = df.copy()\n    for col in tqdm_notebook(cols_maxval.keys()):\n        print('Start ', col)\n        df[col + '_sin'] = (np.sin(2 * np.pi * df[col]/cols_maxval[col])).astype('float16')\n        df[col + '_cos'] = (np.cos(2 * np.pi * df[col]/cols_maxval[col])).astype('float16')\n        print('Add', col + '_sin',col + '_cos')\n\n        if is_drop:\n            # drop non-cycle features\n            df.drop(col, axis=1, inplace=True)\n            print('Drop in features')\n    return df\n\n# Not enough memory for commit, so commented out a small number of time features.\ncols_maxval = {\n    'Month':12\n    , 'DayOfMonth':31\n#     , 'DayOfWeek':7\n#     , 'Hour':24\n#     , 'weekofyear':53\n#     , 'dayofyear':366\n    , 'year_built':751\n}","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train = get_cyclical_encode(train, cols_maxval, is_drop = True)\ntest = get_cyclical_encode(test, cols_maxval, is_drop = True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"print(train.shape, test.shape)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train.columns","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test.columns","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test.info()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# log target feature"},{"metadata":{"trusted":true},"cell_type":"code","source":"train['meter_reading'] = np.log1p(train['meter_reading'])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# save to pickle"},{"metadata":{"trusted":true},"cell_type":"code","source":"# train.to_pickle(PATH+'train_v2.pkl')\n# test.to_pickle(PATH+'test_v2.pkl')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"gc.collect();","execution_count":null,"outputs":[]}],"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}