{"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":"## 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):\n    df = pd.read_csv(input_file(\"weather_train.csv\"), parse_dates=[\"timestamp\"])\n    \n    #df = create_lag_features(df, 24)\n    #gc.collect()\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    return compress_dataframe(df).set_index([\"site_id\", \"timestamp\"])\n\n\ndef read_weather_test(fix_timestamps=True, interpolate_na=True, add_na_indicators=True):\n    df = pd.read_csv(input_file(\"weather_test.csv\"), parse_dates=[\"timestamp\"])\n    \n    #df = create_lag_features(df, 24)\n    #gc.collect()\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    return compress_dataframe(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():\n    df = pd.read_csv(input_file(\"train.csv\"), parse_dates=[\"timestamp\"])\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(fix_timestamps=True, interpolate_na=True, add_na_indicators=True):\n    Xy = compress_dataframe(read_train().join(read_building_metadata(), on=\"building_id\").join(\n        read_weather_train(fix_timestamps, interpolate_na, add_na_indicators),\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():\n    df = pd.read_csv(input_file(\"test.csv\"), parse_dates=[\"timestamp\"])\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(fix_timestamps=True, interpolate_na=True, add_na_indicators=True):\n    X = compress_dataframe(read_test().join(read_building_metadata(), on=\"building_id\").join(\n        read_weather_test(fix_timestamps, interpolate_na, add_na_indicators),\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\"\n]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"def get_all_data():\n    X_train, y_train = combined_train_data()\n\n    bad_rows = find_bad_rows(X_train, y_train)\n\n    X_train = X_train.drop(index=bad_rows)\n    y_train = y_train.reindex_like(X_train)\n\n    # Additional preprocessing\n    X_train = compress_dataframe(_add_time_features(X_train))\n\n    X_train = X_train.drop(columns=\"timestamp\")  # Raw timestamp doesn't help when prediction\n    y_train = np.log1p(y_train)\n\n    X_train['meter_reading'] = y_train\n    \n    X_train = create_stratify_kfold(X_train)\n\n    del y_train\n    gc.collect()\n    \n    X_test = combined_test_data()\n    X_test = compress_dataframe(_add_time_features(X_test))\n\n    X_test = X_test.drop(columns=\"timestamp\") \n    X_test['row_id'] = X_test.index\n\n    gc.collect()\n    \n    return X_train, X_test","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Main code"},{"metadata":{"trusted":true},"cell_type":"code","source":"X_train, X_test = get_all_data()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"X_train['k_folds'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"X_train.to_parquet('X_train.parquet.gzip', compression='gzip')\nX_test.to_parquet('X_test.parquet.gzip', compression='gzip')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"","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}