{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.10.13","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":50160,"databundleVersionId":7921029,"sourceType":"competition"}],"dockerImageVersionId":30698,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"The key column is \"refreshdate_3813885D\" from the credit_bureau_a_1_* files.\n\nIts minimum value per case id is almost always equal to 3/1/2019. One possible reason for this could be that it was used to fill in NaN values.\n\nThe difference between \"refreshdate_3813885D\" and \"date_decision\" has an almost perfect correlation with \"date_decision\". As the date differences are preserved in the test set, we can restore the original date by subtracting this difference from 3/1/2019.\n\nThe percentage of correctly restored dates is 87%, with 3% errors and 10% missing values.","metadata":{}},{"cell_type":"code","source":"import datetime as dt\nimport glob\n\nimport polars as pl\nimport pandas as pd\n\nTRAIN_PATH = \"/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train\"\n\n# Read credit bureau data (we are only interested in refreshdate_3813885D)\ndfs = []\nfor path in glob.glob(f\"{TRAIN_PATH}/train_credit_bureau_a_1_*.parquet\"):\n    df_i = pl.read_parquet(path)[[\"refreshdate_3813885D\", \"case_id\"]]\n    dfs.append(df_i)\ndf = pl.concat(dfs, how=\"vertical_relaxed\")\n\n# Merge with base\ndf_base = pl.read_parquet(f\"{TRAIN_PATH}/train_base.parquet\")\ndf = df.join(df_base, how=\"left\", on=\"case_id\")\n\n# Convert to dates\ndf = df.with_columns(\n    pl.col(\"date_decision\").cast(pl.Date),\n    pl.col(\"refreshdate_3813885D\").cast(pl.Date)\n)\n\n# Convert to pandas, sort by dates\ndf = df.to_pandas()\ndf_base = df_base.to_pandas()\ndf.sort_values(\"date_decision\", inplace=True)\n\n# Difference between refreshdate_3813885D and date_decision (which is preserved on the test set)\ndf[\"refreshdate_3813885D_diff\"] = (df[\"refreshdate_3813885D\"]-df[\"date_decision\"]).dt.days\n\n# Aggregate by case_ids and merge with base\ndf_agg = df.groupby(\"case_id\")[\"refreshdate_3813885D_diff\"].min().reset_index()\ndf = df_base.merge(df_agg, how=\"left\", on=\"case_id\")\ndf[\"date_decision\"] = pd.to_datetime(df[\"date_decision\"])\n\n# Recover dates and week_num\ndf[\"date_restored\"] =  dt.datetime(2019,1,3) - pd.to_timedelta(df[\"refreshdate_3813885D_diff\"], unit='d')\ndf[\"week_restored\"] = (df[\"date_restored\"] - dt.datetime(2019,1,1)).dt.days // 7","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2024-05-22T19:52:19.478195Z","iopub.execute_input":"2024-05-22T19:52:19.479160Z","iopub.status.idle":"2024-05-22T19:52:43.088006Z","shell.execute_reply.started":"2024-05-22T19:52:19.479125Z","shell.execute_reply":"2024-05-22T19:52:43.086894Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Accuracy assessment","metadata":{}},{"cell_type":"code","source":"# Calculate difference between recovered date and real date_decision\ndf[\"dates_error\"] = (df[\"date_restored\"] - df[\"date_decision\"]).dt.days\ndf[\"weeks_error\"] = df[\"week_restored\"] - df[\"WEEK_NUM\"]\n\nprint(f\"Correctly restored dates: {(df['dates_error']==0).sum() / len(df):.2%}\")\nprint(f\"Correctly restored weeks: {(df['weeks_error']==0).sum() / len(df):.2%}\")\n\nprint(f\"Percentage of non-missing values: {df['date_restored'].count() / len(df):.2%}\")","metadata":{"execution":{"iopub.status.busy":"2024-05-22T19:57:01.143326Z","iopub.execute_input":"2024-05-22T19:57:01.143693Z","iopub.status.idle":"2024-05-22T19:57:01.221755Z","shell.execute_reply.started":"2024-05-22T19:57:01.143663Z","shell.execute_reply":"2024-05-22T19:57:01.220264Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}