{"cells":[{"metadata":{"_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","trusted":true},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport gc\nimport os\n\nfrom os import path\n\npd.set_option(\"max_columns\", 500)\n%matplotlib inline","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Preprocessing utils"},{"metadata":{},"cell_type":"markdown","source":"# Additional utils"},{"metadata":{"trusted":true},"cell_type":"code","source":"def create_d_n_feature(df):\n    data_dict = {0: [ 10, 11, 12, 13, 14, 15, 16], 1: [0,1,2,3,4,5, 22, 23],\n                 2:[6,  7,  8,  9,17,18, 19, 20, 21]}\n    day_night_hrs_dict = {value:key for key in data_dict for value in data_dict[key]}\n    df[\"d_n\"]=df['timestamp'].dt.hour.map(day_night_hrs_dict)\n    df.d_n = df.d_n.astype(np.int8)\n    return df\n\ndef create_date_feature(df):\n    df['date'] = df.timestamp.dt.date\n    return df\n\ndef create_max_min_by_day_night(df):\n    df_temp = df.groupby([\"building_id\",\"date\",\"d_n\"]).agg(max_at =(\"air_temperature\", \"max\"),\n                                                                 min_at =(\"air_temperature\", \"min\"),\n                                                                 mean_at=(\"air_temperature\", \"mean\"),\n                                                                 max_dt =(\"dew_temperature\", \"max\"),\n                                                                 min_dt =(\"dew_temperature\", \"min\"),\n                                                                 mean_dt =(\"dew_temperature\", \"mean\") ).reset_index()\n\n    df = df.merge(df_temp, on=[\"building_id\",\"date\",\"d_n\"], how='left')\n\n    del df_temp\n    df = df.drop(columns='date')\n    \n    gc.collect()\n    \n    return df\n\n\ndef create_lag_features(df, window):\n    \"\"\"\n    Creating lag-based features looking back in time.\n    \"\"\"\n    df = df.sort_values(['site_id','timestamp'])\n    \n    feature_cols = [\"air_temperature\", \"dew_temperature\"]\n    df_site = df.groupby(\"site_id\")\n\n    df_rolled = df_site[feature_cols].rolling(window=window, min_periods=0)\n\n    df_mean = df_rolled.mean().reset_index()\n    df_median = df_rolled.median().reset_index()\n    df_min = df_rolled.min().reset_index()\n    df_max = df_rolled.max().reset_index()\n\n    for feature in feature_cols:\n        df[f\"{feature}_mean_lag{window}\"] = df_mean[feature]\n        df[f\"{feature}_median_lag{window}\"] = df_median[feature]\n        df[f\"{feature}_min_lag{window}\"] = df_min[feature]\n        df[f\"{feature}_max_lag{window}\"] = df_max[feature]\n        \n    del df_mean\n    del df_median\n    del df_min\n    del df_max\n    del df_rolled\n    \n    gc.collect()\n        \n    return df\n\n\n\ndef fill_medians_with_filling_value(df, fill_value=-1):\n    for col in df.columns:\n        if col.startswith('had'):\n            df.loc[df[col[len('had_'):]] == 0, col] = fill_value\n            \n    return df","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Compresing utils"},{"metadata":{"trusted":true},"cell_type":"code","source":"def compress_dataframe(df):\n    result = df.copy()\n    for col in result.columns:\n        col_data = result[col]\n        dn = col_data.dtype.name\n        if dn == \"object\":\n            result[col] = pd.to_numeric(col_data.astype(\"category\").cat.codes, downcast=\"integer\")\n        elif dn == \"bool\":\n            result[col] = col_data.astype(\"int8\")\n        elif dn.startswith(\"int\") or (col_data.round() == col_data).all():\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","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Collecting bad raws"},{"metadata":{"trusted":true},"cell_type":"code","source":"def make_is_bad_zero(Xy_subset, min_interval=48, summer_start=3000, summer_end=7500):\n    \"\"\"Helper routine for 'find_bad_zeros'.\n    \n    This operates upon a single dataframe produced by 'groupby'. We expect an \n    additional column 'meter_id' which is a duplicate of 'meter' because groupby \n    eliminates the original one.\"\"\"\n    meter = Xy_subset.meter_id.iloc[0]\n    is_zero = Xy_subset.meter_reading == 0\n    if meter == 0:\n        # Electrical meters should never be zero. Keep all zero-readings in this table so that\n        # they will all be dropped in the train set.\n        return is_zero\n\n    transitions = (is_zero != is_zero.shift(1))\n    all_sequence_ids = transitions.cumsum()\n    ids = all_sequence_ids[is_zero].rename(\"ids\")\n    if meter in [2, 3]:\n        # It's normal for steam and hotwater to be turned off during the summer\n        keep = set(ids[(Xy_subset.timestamp < summer_start) |\n                       (Xy_subset.timestamp > summer_end)].unique())\n        is_bad = ids.isin(keep) & (ids.map(ids.value_counts()) >= min_interval)\n    elif meter == 1:\n        time_ids = ids.to_frame().join(Xy_subset.timestamp).set_index(\"timestamp\").ids\n        is_bad = ids.map(ids.value_counts()) >= min_interval\n\n        # Cold water may be turned off during the winter\n        jan_id = time_ids.get(0, False)\n        dec_id = time_ids.get(8283, False)\n        if (jan_id and dec_id and jan_id == time_ids.get(500, False) and\n                dec_id == time_ids.get(8783, False)):\n            is_bad = is_bad & (~(ids.isin(set([jan_id, dec_id]))))\n    else:\n        raise Exception(f\"Unexpected meter type: {meter}\")\n\n    result = is_zero.copy()\n    result.update(is_bad)\n    return result\n\ndef find_bad_zeros(X, y):\n    \"\"\"Returns an Index object containing only the rows which should be deleted.\"\"\"\n    Xy = X.assign(meter_reading=y, meter_id=X.meter)\n    is_bad_zero = Xy.groupby([\"building_id\", \"meter\"]).apply(make_is_bad_zero)\n    return is_bad_zero[is_bad_zero].index.droplevel([0, 1])\n\ndef find_bad_sitezero(X):\n    \"\"\"Returns indices of bad rows from the early days of Site 0 (UCF).\"\"\"\n    return X[(X.timestamp < 3378) & (X.site_id == 0) & (X.meter == 0)].index\n\ndef find_bad_building1099(X, y):\n    \"\"\"Returns indices of bad rows (with absurdly high readings) from building 1099.\"\"\"\n    return X[(X.building_id == 1099) & (X.meter == 2) & (y > 3e4)].index\n\ndef find_bad_rows(X, y):\n    return find_bad_zeros(X, y).union(find_bad_sitezero(X)).union(find_bad_building1099(X, y))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Process building metadata"},{"metadata":{"trusted":true},"cell_type":"code","source":"def read_building_metadata(nan_filling=-1):\n    return compress_dataframe(pd.read_csv(\n        input_file(\"building_metadata.csv\")).fillna(nan_filling)).set_index(\"building_id\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Process weather metadata"},{"metadata":{"trusted":true},"cell_type":"code","source":"site_GMT_offsets = [-5, 0, -7, -5, -8, 0, -5, -5, -5, -6, -7, -5, 0, -6, -5, -5]\n\ndef read_weather_train(fix_timestamps=True, interpolate_na=True, add_na_indicators=True, create_rolling_features=True):\n    df = pd.read_csv(input_file(\"weather_train.csv\"), parse_dates=[\"timestamp\"])\n    \n    \n    df.timestamp = (df.timestamp - pd.to_datetime(\"2016-01-01\")).dt.total_seconds() // 3600\n    if fix_timestamps:\n        GMT_offset_map = {site: offset for site, offset in enumerate(site_GMT_offsets)}\n        df.timestamp = df.timestamp + df.site_id.map(GMT_offset_map)\n    if interpolate_na:\n        site_dfs = []\n        for site_id in df.site_id.unique():\n            # Make sure that we include all possible hours so that we can interpolate evenly\n            site_df = df[df.site_id == site_id].set_index(\"timestamp\").reindex(range(8784))\n            site_df.site_id = site_id\n            for col in [c for c in site_df.columns if c != \"site_id\"]:\n                if add_na_indicators: site_df[f\"had_{col}\"] = ~site_df[col].isna()\n                site_df[col] = site_df[col].interpolate(limit_direction='both', method='linear')\n                # Some sites are completely missing some columns, so use this fallback\n                site_df[col] = site_df[col].fillna(df[col].median())\n            site_dfs.append(site_df)\n        df = pd.concat(site_dfs).reset_index()  # make timestamp back into a regular column\n    elif add_na_indicators:\n        for col in df.columns:\n            if df[col].isna().any(): df[f\"had_{col}\"] = ~df[col].isna()\n    \n    if create_rolling_features:\n        df = create_lag_features(df, window=24)\n        \n    df = compress_dataframe(df)\n    \n    return df.set_index([\"site_id\", \"timestamp\"])\n\n\ndef read_weather_test(fix_timestamps=True, interpolate_na=True, add_na_indicators=True, create_rolling_features=True):\n    df = pd.read_csv(input_file(\"weather_test.csv\"), parse_dates=[\"timestamp\"])\n    \n    \n    df.timestamp = (df.timestamp - pd.to_datetime(\"2016-01-01\")).dt.total_seconds() // 3600\n    if fix_timestamps:\n        GMT_offset_map = {site: offset for site, offset in enumerate(site_GMT_offsets)}\n        df.timestamp = df.timestamp + df.site_id.map(GMT_offset_map)\n    if interpolate_na:\n        site_dfs = []\n        for site_id in df.site_id.unique():\n            # Make sure that we include all possible hours so that we can interpolate evenly\n            site_df = df[df.site_id == site_id].set_index(\"timestamp\").reindex(range(8784, 26304))\n            site_df.site_id = site_id\n            for col in [c for c in site_df.columns if c != \"site_id\"]:\n                if add_na_indicators: site_df[f\"had_{col}\"] = ~site_df[col].isna()\n                site_df[col] = site_df[col].interpolate(limit_direction='both', method='linear')\n                # Some sites are completely missing some columns, so use this fallback\n                site_df[col] = site_df[col].fillna(df[col].median())\n            site_dfs.append(site_df)\n        df = pd.concat(site_dfs).reset_index()  # make timestamp back into a regular column\n    elif add_na_indicators:\n        for col in df.columns:\n            if df[col].isna().any(): df[f\"had_{col}\"] = ~df[col].isna()\n                \n    if create_rolling_features:\n        df = create_lag_features(df, window=24)\n    df = compress_dataframe(df)\n    \n    return df.set_index([\"site_id\", \"timestamp\"])","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Additional features"},{"metadata":{"trusted":true},"cell_type":"code","source":"def _add_time_features(X):\n    return X.assign(tm_day_of_week=((X.timestamp // 24) % 7), tm_hour_of_day=(X.timestamp % 24))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Main data reading Pipeline"},{"metadata":{"trusted":true},"cell_type":"code","source":"def input_file(file):\n    path = f\"../input/ashrae-energy-prediction/{file}\"\n    if not os.path.exists(path): return path + \".gz\"\n    return path","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Train"},{"metadata":{"trusted":true},"cell_type":"code","source":"def read_train(df):    \n    df = create_d_n_feature(df)\n    df = create_date_feature(df)\n    \n    df.timestamp = (df.timestamp - pd.to_datetime(\"2016-01-01\")).dt.total_seconds() // 3600\n    return compress_dataframe(df)\n\ndef combined_train_data(df, fix_timestamps=True, interpolate_na=True, add_na_indicators=True, create_rolling_features=True):\n    Xy = compress_dataframe(read_train(df).join(read_building_metadata(), on=\"building_id\").join(\n        read_weather_train(fix_timestamps, interpolate_na, add_na_indicators, create_rolling_features=create_rolling_features),\n        on=[\"site_id\", \"timestamp\"]).fillna(-1))\n    return Xy.drop(columns=[\"meter_reading\"]), Xy.meter_reading","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Test"},{"metadata":{"trusted":true},"cell_type":"code","source":"def read_test(df): \n    df = create_d_n_feature(df)\n    df = create_date_feature(df)\n    \n    df.timestamp = (df.timestamp - pd.to_datetime(\"2016-01-01\")).dt.total_seconds() // 3600\n    return compress_dataframe(df).set_index(\"row_id\")\n\ndef combined_test_data(df, fix_timestamps=True, interpolate_na=True, add_na_indicators=True, create_rolling_features=True):\n    X = compress_dataframe(read_test(df).join(read_building_metadata(), on=\"building_id\").join(\n        read_weather_test(fix_timestamps, interpolate_na, add_na_indicators, create_rolling_features=create_rolling_features),\n        on=[\"site_id\", \"timestamp\"]).fillna(-1))\n    return X","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Create Stratify KFold"},{"metadata":{"trusted":true},"cell_type":"code","source":"NUM_FOLDS = 5\n\nfrom sklearn.model_selection import StratifiedKFold\n\n\ndef create_stratify_kfold(df):\n    kf = StratifiedKFold(n_splits=NUM_FOLDS, shuffle=True, random_state=42)\n    df['k_folds'] = 0\n    \n    for idx, (train_idx, test_idx) in enumerate(kf.split(df, df['building_id'])):\n        df['k_folds'].iloc[test_idx] = idx\n        \n    return df","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Main Pipeline"},{"metadata":{"trusted":true},"cell_type":"code","source":"cat_columns = [\n    \"building_id\", \"meter\", \"site_id\", \"primary_use\", \"had_air_temperature\", \"had_cloud_coverage\",\n    \"had_dew_temperature\", \"had_precip_depth_1_hr\", \"had_sea_level_pressure\", \"had_wind_direction\",\n    \"had_wind_speed\", \"tm_day_of_week\", \"tm_hour_of_day\", \"d_n\"\n]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Main code"},{"metadata":{"trusted":true},"cell_type":"code","source":"X_test = pd.read_csv('/kaggle/input/ashrae-energy-prediction/test.csv', parse_dates=['timestamp'])\n    \nX_test = combined_test_data(X_test)\nX_test = create_max_min_by_day_night(X_test)\nX_test = compress_dataframe(_add_time_features(X_test))\n\nX_test = X_test.drop(columns=\"timestamp\") \nX_test['row_id'] = X_test.index\n\ngc.collect()\n\nX_test.to_parquet('X_test.parquet.gzip', compression='gzip')\n\ndel X_test\ngc.collect()\n\nprint('Test ready!')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"leak_df = pd.read_csv('/kaggle/input/leakaggregator/leak_df.csv', parse_dates=['timestamp'])\nX_train = pd.read_csv('/kaggle/input/ashrae-energy-prediction/train.csv', parse_dates=['timestamp'])\n\n\nX_train = pd.concat([X_train, leak_df[leak_df['timestamp'].dt.year == 2016]], axis=0).drop_duplicates(['timestamp','building_id', 'meter']).reset_index()\n\ndel leak_df\ngc.collect()\n\nX_train, y_train = combined_train_data(X_train)\nX_train = create_max_min_by_day_night(X_train)\n\nbad_rows = find_bad_rows(X_train, y_train)\n\nX_train = X_train.drop(index=bad_rows)\ny_train = y_train.reindex_like(X_train)\n\n# Additional preprocessing\nX_train = compress_dataframe(_add_time_features(X_train))\n\nX_train = X_train.drop(columns=\"timestamp\")  # Raw timestamp doesn't help when prediction\ny_train = np.log1p(y_train)\n\nX_train['meter_reading'] = y_train\n\ndel y_train\ngc.collect()\n\nX_train = create_stratify_kfold(X_train)\n\ngc.collect()\n\nX_train.to_parquet('X_train.parquet.gzip', compression='gzip')\n\ndel X_train\ngc.collect()\n\nprint('Train ready!')","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.6.6"}},"nbformat":4,"nbformat_minor":1}