{"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_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# ASHRAE with fast.ai, Part 1: Preprocessing","metadata":{}},{"cell_type":"markdown","source":"This kernel performs the preprocessing of the training and test data for future use. \n\nEven with careful memory management, some operations on the large ASHRAE datasets are too demanding. Let's spread the work across multiple notebooks. This kernel is thus part of the series which further includes:\n\n- https://www.kaggle.com/michelezoccali/ashrae-with-fast-ai-part-2 (training)\n- https://www.kaggle.com/michelezoccali/ashrae-with-fast-ai-part-3 (inference)\n\nNB: Kaggle allows you to reference the static output of a kernel from other instances, as long as you remember to commit the notebook and save the output.","metadata":{}},{"cell_type":"markdown","source":"# Imports","metadata":{}},{"cell_type":"code","source":"import os\nimport gc\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O\nimport datetime","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2021-06-26T12:43:53.793784Z","iopub.execute_input":"2021-06-26T12:43:53.794199Z","iopub.status.idle":"2021-06-26T12:43:53.798775Z","shell.execute_reply.started":"2021-06-26T12:43:53.794165Z","shell.execute_reply":"2021-06-26T12:43:53.797917Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"path = '../input/ashrae-energy-prediction'\n\nfor dirname, _, filenames in os.walk(path):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))","metadata":{"execution":{"iopub.status.busy":"2021-06-26T12:43:53.800229Z","iopub.execute_input":"2021-06-26T12:43:53.800718Z","iopub.status.idle":"2021-06-26T12:43:53.819833Z","shell.execute_reply.started":"2021-06-26T12:43:53.800674Z","shell.execute_reply":"2021-06-26T12:43:53.817849Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Utilities","metadata":{}},{"cell_type":"code","source":"def reduce_mem(df):\n    result = df.copy()\n    for col in result.columns:\n        col_data = result[col]\n        dn = col_data.dtype.name\n        if not dn.startswith(\"datetime\"):\n            if dn == \"object\":  # only object feature has low cardinality\n                result[col] = pd.to_numeric(col_data.astype(\"category\").cat.codes, downcast=\"unsigned\")\n            elif dn.startswith(\"int\") | dn.startswith(\"uint\"):\n                if col_data.min() >= 0:\n                    result[col] = pd.to_numeric(col_data, downcast=\"unsigned\")\n                else:\n                    result[col] = pd.to_numeric(col_data, downcast='integer')\n            else:\n                result[col] = pd.to_numeric(col_data, downcast='float')\n    return result\n\ndef add_lag_features(weather_df, window=3):\n    group_df = weather_df.groupby('site_id')\n    cols = ['air_temperature', 'cloud_coverage', 'dew_temperature', 'precip_depth_1_hr']\n    rolled = group_df[cols].rolling(window=window, min_periods=0)\n    lag_mean = rolled.mean().reset_index().astype(np.float16)\n    lag_max = rolled.max().reset_index().astype(np.float16)\n    lag_min = rolled.min().reset_index().astype(np.float16)\n    for col in cols:\n        weather_df[f'{col}_min_lag{window}'] = lag_min[col]\n        weather_df[f'{col}_mean_lag{window}'] = lag_mean[col]\n        weather_df[f'{col}_max_lag{window}'] = lag_max[col]\n    return weather_df\n\ndef _delete_bad_sitezero(X, y):\n    cond = (X.timestamp > '2016-05-20') | (X.site_id != 0) | (X.meter != 0)\n    X = X[cond]\n    y = y.reindex_like(X)\n    return X.reset_index(drop=True), y.reset_index(drop=True)\n\ndef _extract_temporal(X, train=True):\n    X['hour'] = X.timestamp.dt.hour\n    X['weekday'] = X.timestamp.dt.weekday\n    if train:\n        # include month to create validation set, to be deleted before training\n        X['month'] = X.timestamp.dt.month \n    # month and year cause overfit, could try other (holiday, business, etc.)\n    return reduce_mem(X)","metadata":{"execution":{"iopub.status.busy":"2021-06-26T12:43:53.822228Z","iopub.execute_input":"2021-06-26T12:43:53.822583Z","iopub.status.idle":"2021-06-26T12:43:53.83752Z","shell.execute_reply.started":"2021-06-26T12:43:53.822549Z","shell.execute_reply":"2021-06-26T12:43:53.836508Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def load_data(source='train'):\n    assert source in ['train','test']\n    df = pd.read_csv(f'{path}/{source}.csv', parse_dates=['timestamp'])\n    return reduce_mem(df)\n\ndef load_building():\n    df = pd.read_csv(f'{path}/building_metadata.csv').fillna(-1)\n    return reduce_mem(df)\n\ndef load_weather(source='train', fix_timezone=True, impute=True, add_lag=True):\n    assert source in ['train','test']\n    df = pd.read_csv(f'{path}/weather_{source}.csv', parse_dates=['timestamp'])\n    if fix_timezone:\n        offsets = [5,0,9,6,8,0,6,6,5,7,8,6,0,7,6,6]\n        offset_map = {site: offset for site, offset in enumerate(offsets)}\n        df.timestamp = df.timestamp - pd.to_timedelta(df.site_id.map(offset_map), unit='h')\n    if impute:\n        site_dfs = []\n        for site in df.site_id.unique():\n            if source == 'train':\n                new_idx = pd.date_range(start='2016-1-1', end='2016-12-31-23', freq='H')\n            else:\n                new_idx = pd.date_range(start='2017-1-1', end='2018-12-31-23', freq='H')\n            site_df = df[df.site_id == site].set_index('timestamp').reindex(new_idx)\n            site_df.site_id = site\n            for col in [c for c in site_df.columns if c != 'site_id']:\n                site_df[col] = site_df[col].interpolate(limit_direction='both', method='linear')\n                site_df[col] = site_df[col].fillna(df[col].median())\n            site_dfs.append(site_df)\n        df = pd.concat(site_dfs)\n        df['timestamp'] = df.index\n        df = df.reset_index(drop=True)\n        \n    if add_lag:\n        df = add_lag_features(df, window=3)\n    \n    return reduce_mem(df)\n\ndef merged_dfs(source='train', fix_timezone=True, impute=True, add_lag=False):\n    df = load_data(source=source).merge(load_building(), on='building_id', how='left')\n    df = df.merge(load_weather(source=source, fix_timezone=fix_timezone, impute=impute, add_lag=add_lag),\n                 on=['site_id','timestamp'], how='left')\n    if source == 'train':\n        X = df.drop('meter_reading', axis=1)  \n        y = np.log1p(df.meter_reading)  # log-transform of target\n        return X, y\n    elif source == 'test':\n        return df","metadata":{"execution":{"iopub.status.busy":"2021-06-26T12:43:53.839052Z","iopub.execute_input":"2021-06-26T12:43:53.839525Z","iopub.status.idle":"2021-06-26T12:43:53.858282Z","shell.execute_reply.started":"2021-06-26T12:43:53.83948Z","shell.execute_reply":"2021-06-26T12:43:53.85695Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Training data","metadata":{}},{"cell_type":"markdown","source":"We try without lag features first, as unsure if the TabularPandas instance can deal with the added features without adding enough overhead to fill the RAM.","metadata":{}},{"cell_type":"code","source":"X_train, y_train = merged_dfs(add_lag=False)\nX_train.head()","metadata":{"execution":{"iopub.status.busy":"2021-06-26T12:43:53.859639Z","iopub.execute_input":"2021-06-26T12:43:53.859972Z","iopub.status.idle":"2021-06-26T12:44:19.104938Z","shell.execute_reply.started":"2021-06-26T12:43:53.859939Z","shell.execute_reply":"2021-06-26T12:44:19.10394Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# preprocessing\nX_train, y_train = _delete_bad_sitezero(X_train, y_train)\nX_train = _extract_temporal(X_train)\n\n# remove timestamp and other unimportant features\nto_drop = ['timestamp','sea_level_pressure','wind_direction','wind_speed']\nX_train.drop(to_drop, axis=1, inplace=True)\n\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2021-06-26T12:44:19.106539Z","iopub.execute_input":"2021-06-26T12:44:19.107293Z","iopub.status.idle":"2021-06-26T12:44:33.683341Z","shell.execute_reply.started":"2021-06-26T12:44:19.107243Z","shell.execute_reply":"2021-06-26T12:44:33.682094Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"df_train = pd.concat([X_train, y_train], axis=1)\n\ndel X_train, y_train\ngc.collect()\n\ndf_train.info()","metadata":{"execution":{"iopub.status.busy":"2021-06-26T12:44:33.685492Z","iopub.execute_input":"2021-06-26T12:44:33.685781Z","iopub.status.idle":"2021-06-26T12:44:34.196274Z","shell.execute_reply.started":"2021-06-26T12:44:33.685754Z","shell.execute_reply":"2021-06-26T12:44:34.195241Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"An important part of the preprocessing involves careful type downcasting for memory management. Saving to a csv file does not preserve dtypes, hence we save in HDF5 format. We can add multiple frames to the save HDF5 file as long as we specify a key for retrieval.","metadata":{}},{"cell_type":"code","source":"df_train.to_hdf('preprocessing_no_lag.h5', index=False, key='train')\ndel df_train","metadata":{"execution":{"iopub.status.busy":"2021-06-26T12:44:34.19764Z","iopub.execute_input":"2021-06-26T12:44:34.197933Z","iopub.status.idle":"2021-06-26T12:44:36.47129Z","shell.execute_reply.started":"2021-06-26T12:44:34.197905Z","shell.execute_reply":"2021-06-26T12:44:36.470131Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Test data","metadata":{}},{"cell_type":"code","source":"X_test = merged_dfs(source='test', add_lag=False)\nX_test = _extract_temporal(X_test, train=False)\nX_test.drop(columns=['timestamp']+to_drop, inplace=True)\ngc.collect()\n\nX_test.head()","metadata":{"execution":{"iopub.status.busy":"2021-06-26T12:44:36.472641Z","iopub.execute_input":"2021-06-26T12:44:36.472949Z","iopub.status.idle":"2021-06-26T12:46:06.155669Z","shell.execute_reply.started":"2021-06-26T12:44:36.472919Z","shell.execute_reply":"2021-06-26T12:46:06.154491Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_test.info()","metadata":{"execution":{"iopub.status.busy":"2021-06-26T12:46:06.157222Z","iopub.execute_input":"2021-06-26T12:46:06.157564Z","iopub.status.idle":"2021-06-26T12:46:06.173454Z","shell.execute_reply.started":"2021-06-26T12:46:06.157533Z","shell.execute_reply":"2021-06-26T12:46:06.172027Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Now we add the test DataFrame to the same HDF5 file.","metadata":{}},{"cell_type":"code","source":"X_test.to_hdf('preprocessing_no_lag.h5', index=False, key='test')","metadata":{"execution":{"iopub.status.busy":"2021-06-26T12:46:06.175134Z","iopub.execute_input":"2021-06-26T12:46:06.17582Z","iopub.status.idle":"2021-06-26T12:46:12.602906Z","shell.execute_reply.started":"2021-06-26T12:46:06.175762Z","shell.execute_reply":"2021-06-26T12:46:12.601959Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"Let us verify that HDF5 files did indeed preserve dtypes.","metadata":{}},{"cell_type":"code","source":"X_test2 = pd.read_hdf('preprocessing_no_lag.h5','test')\nX_test2.info()","metadata":{"execution":{"iopub.status.busy":"2021-06-26T12:49:38.698308Z","iopub.execute_input":"2021-06-26T12:49:38.698704Z","iopub.status.idle":"2021-06-26T12:49:44.412568Z","shell.execute_reply.started":"2021-06-26T12:49:38.698671Z","shell.execute_reply":"2021-06-26T12:49:44.411505Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We are now ready to load `df_train` and `X_test` in any other kernel to start modelling! If you are interested, check out the kernel at:\n\n- https://www.kaggle.com/michelezoccali/ashrae-with-fast-ai-part-2","metadata":{}}]}