{"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":30673,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# Relevance of different tax registry tables over time  \n\nIt looks like three tax registry providers provided data at different points in time. It might be interesting because at test time, probably only provider B is available. If they continue to change providers every 30-40 weeks, it might even be a fourth provider during the test period :D\n\nI am not sure yet how I will handle this. If the data across providers is similar, a sensible approach could be to merge the features from all providers into a single table to create a more consistent dataset.\n\nEDIT: I added a section to compare the amounts from the different sources where there is overlap. Turns out the values can be mapped from on source to another, but it is not a 1 to 1 mapping in all cases. ","metadata":{}},{"cell_type":"code","source":"import pandas as pd\nimport polars as pl\nimport matplotlib.pyplot as plt \n\ndef create_plots(df):\n\n    fig, ax = plt.subplots(1, 2, figsize=(24, 8))\n\n    # First subplot: Tax Registry Data Relevance Over Weeks\n    for col in df.columns.drop([\"WEEK_NUM\", \"tax_available\"]):\n        ax[0].plot(df[\"WEEK_NUM\"], df[col], label=col)\n\n    ax[0].set_title('Tax Registry Data Relevance Over Weeks')\n    ax[0].set_xlabel('Week Number')\n    ax[0].set_ylabel('Count')\n    ax[0].legend()\n    ax[0].grid(True)\n\n    # Second subplot: Percentage of cases with tax registry entry\n    ax[1].plot(\n        df[\"WEEK_NUM\"], \n        df[\"tax_available\"], \n        label=\"% of cases with tax registry entry\", color='green'\n    )\n\n    ax[1].set_title('Percentage of Cases with Tax Registry Entry')\n    ax[1].set_xlabel('Week Number')\n    ax[1].set_ylabel('Percentage (%)')\n    ax[1].legend()\n    ax[1].grid(True)\n\n    plt.tight_layout()\n    plt.show()","metadata":{"execution":{"iopub.status.busy":"2024-03-22T20:16:18.928731Z","iopub.execute_input":"2024-03-22T20:16:18.929770Z","iopub.status.idle":"2024-03-22T20:16:20.344772Z","shell.execute_reply.started":"2024-03-22T20:16:18.929734Z","shell.execute_reply":"2024-03-22T20:16:20.343531Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data_path = \"/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/\"\n\n# Load base table and tax registries\nbase_table = pl.read_parquet(data_path + f\"train/train_base.parquet\")\ntax_registry_a = pl.read_parquet(\n    data_path + f\"train/train_tax_registry_a_1.parquet\"\n).filter(pl.col(\"num_group1\") == 0).drop(\"num_group1\")\ntax_registry_b = pl.read_parquet(\n    data_path + f\"train/train_tax_registry_b_1.parquet\"\n).filter(pl.col(\"num_group1\") == 0).drop(\"num_group1\")\ntax_registry_c = pl.read_parquet(\n    data_path + f\"train/train_tax_registry_c_1.parquet\"\n).filter(pl.col(\"num_group1\") == 0).drop(\"num_group1\")","metadata":{"execution":{"iopub.status.busy":"2024-03-22T20:16:50.746636Z","iopub.execute_input":"2024-03-22T20:16:50.747236Z","iopub.status.idle":"2024-03-22T20:16:52.111237Z","shell.execute_reply.started":"2024-03-22T20:16:50.747202Z","shell.execute_reply":"2024-03-22T20:16:52.110211Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tax_df_a = base_table.select([\"case_id\", \"WEEK_NUM\"]).join(\n    tax_registry_a, how=\"left\", on=\"case_id\"\n).to_pandas()\n\n# Get number of entries by week\ntax_df_grouped_a = tax_df_a.groupby(\n    \"WEEK_NUM\", as_index=False\n).count().rename(columns={\"case_id\": \"n_cases\"})\n\n# Calculating the percentage of tax_available as of n_cases\ntax_df_grouped_a[\"tax_available\"] = (\n    tax_df_grouped_a[\"amount_4527230A\"] / tax_df_grouped_a[\"n_cases\"]\n) * 100\n\ncreate_plots(tax_df_grouped_a)","metadata":{"execution":{"iopub.status.busy":"2024-03-22T20:16:52.112926Z","iopub.execute_input":"2024-03-22T20:16:52.113263Z","iopub.status.idle":"2024-03-22T20:16:53.554092Z","shell.execute_reply.started":"2024-03-22T20:16:52.113236Z","shell.execute_reply":"2024-03-22T20:16:53.553141Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tax_df_b = base_table.select([\"case_id\", \"WEEK_NUM\"]).join(\n    tax_registry_b, how=\"left\", on=\"case_id\"\n).to_pandas()\n\n# Get number of entries by week\ntax_df_grouped_b = tax_df_b.groupby(\n    \"WEEK_NUM\", as_index=False\n).count().rename(columns={\"case_id\": \"n_cases\"})\n\n# Calculating the percentage of tax_available as of n_cases\ntax_df_grouped_b[\"tax_available\"] = (\n    tax_df_grouped_b[\"amount_4917619A\"] / tax_df_grouped_b[\"n_cases\"]\n) * 100\n\ncreate_plots(tax_df_grouped_b)","metadata":{"execution":{"iopub.status.busy":"2024-03-22T20:16:53.555312Z","iopub.execute_input":"2024-03-22T20:16:53.556426Z","iopub.status.idle":"2024-03-22T20:16:54.675387Z","shell.execute_reply.started":"2024-03-22T20:16:53.556383Z","shell.execute_reply":"2024-03-22T20:16:54.674204Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"tax_df_c = base_table.select([\"case_id\", \"WEEK_NUM\"]).join(\n    tax_registry_c, how=\"left\", on=\"case_id\"\n).to_pandas()\n\n# Get number of entries by week\ntax_df_grouped_c = tax_df_c.groupby(\n    \"WEEK_NUM\", as_index=False\n).count().rename(columns={\"case_id\": \"n_cases\"})\n\n# Calculating the percentage of tax_available as of n_cases\ntax_df_grouped_c[\"tax_available\"] = (\n    tax_df_grouped_c[\"pmtamount_36A\"] / tax_df_grouped_c[\"n_cases\"]\n) * 100\n\ncreate_plots(tax_df_grouped_c)","metadata":{"execution":{"iopub.status.busy":"2024-03-22T20:16:54.677723Z","iopub.execute_input":"2024-03-22T20:16:54.678130Z","iopub.status.idle":"2024-03-22T20:16:56.024735Z","shell.execute_reply.started":"2024-03-22T20:16:54.678087Z","shell.execute_reply":"2024-03-22T20:16:56.023565Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Matching different sources","metadata":{}},{"cell_type":"code","source":"tax_reg_a = tax_registry_a.rename(\n    lambda col: \"tax_a__\" + col if col != \"case_id\" else col\n)\ntax_reg_b = tax_registry_b.rename(\n    lambda col: \"tax_b__\" + col if col != \"case_id\" else col\n)\ntax_reg_c = tax_registry_c.rename(\n    lambda col: \"tax_c__\" + col if col != \"case_id\" else col\n)\n\ntax_df = base_table.select([\"WEEK_NUM\", \"case_id\"]).join(\n    tax_reg_a, how=\"left\", on=\"case_id\"\n).join(\n    tax_reg_b, how=\"left\", on=\"case_id\"\n).join(\n    tax_reg_c, how=\"left\", on=\"case_id\"\n).to_pandas()\n\ntax_df.head()","metadata":{"execution":{"iopub.status.busy":"2024-03-22T20:16:56.060662Z","iopub.execute_input":"2024-03-22T20:16:56.061551Z","iopub.status.idle":"2024-03-22T20:16:56.868443Z","shell.execute_reply.started":"2024-03-22T20:16:56.061505Z","shell.execute_reply":"2024-03-22T20:16:56.867364Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"We can see that the amounts from provider A and B have the same description,while C had a different description.","metadata":{}},{"cell_type":"code","source":"# Load feature descriptions\nfd = pd.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/feature_definitions.csv\")\n\n# Most interesting are the amounts\nfor feature in [\"tax_a__amount_4527230A\", \"tax_b__amount_4917619A\", \"tax_c__pmtamount_36A\"]:\n    table, name = feature.split(\"__\")\n    desc = fd[fd[\"Variable\"] == name][\"Description\"].iloc[0]\n    print(f\"Table: {table}, Feature: {name}, Description: {desc}\\n\")\n","metadata":{"execution":{"iopub.status.busy":"2024-03-22T20:17:02.148523Z","iopub.execute_input":"2024-03-22T20:17:02.148924Z","iopub.status.idle":"2024-03-22T20:17:02.179190Z","shell.execute_reply.started":"2024-03-22T20:17:02.148896Z","shell.execute_reply":"2024-03-22T20:17:02.178167Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Comparing amounts in tax_registry_a and tax_registry_b\nFor the cases where we have information from both A and B, the values don't match, but there is a common ratio:","metadata":{}},{"cell_type":"code","source":"(tax_df[\"tax_a__amount_4527230A\"] / tax_df[\"tax_b__amount_4917619A\"]).hist(bins=100)","metadata":{"execution":{"iopub.status.busy":"2024-03-22T20:17:33.775560Z","iopub.execute_input":"2024-03-22T20:17:33.776347Z","iopub.status.idle":"2024-03-22T20:17:34.362630Z","shell.execute_reply.started":"2024-03-22T20:17:33.776312Z","shell.execute_reply":"2024-03-22T20:17:34.360869Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"(tax_df[\"tax_a__amount_4527230A\"] / tax_df[\"tax_b__amount_4917619A\"]).value_counts().nlargest(20)","metadata":{"execution":{"iopub.status.busy":"2024-03-22T20:17:36.710696Z","iopub.execute_input":"2024-03-22T20:17:36.711141Z","iopub.status.idle":"2024-03-22T20:17:36.740509Z","shell.execute_reply.started":"2024-03-22T20:17:36.711103Z","shell.execute_reply":"2024-03-22T20:17:36.739393Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"a_to_b_ratio = (tax_df[\"tax_a__amount_4527230A\"] / tax_df[\"tax_b__amount_4917619A\"])\nmode = a_to_b_ratio.mode().iloc[0]\n\ndiffs = tax_df[(a_to_b_ratio > mode + 0.001) | (a_to_b_ratio < mode - 0.001)][[\"WEEK_NUM\", \"case_id\", \"tax_a__amount_4527230A\", \"tax_b__amount_4917619A\"]]\ndiffs[\"ratio\"] = diffs[\"tax_a__amount_4527230A\"] / diffs[\"tax_b__amount_4917619A\"]\nprint(f\"There are {len(diffs)} cases with a (significantly) different ratio ({round(len(diffs) / a_to_b_ratio.count(), 4) * 100} %)\")\n\ndiffs","metadata":{"execution":{"iopub.status.busy":"2024-03-22T20:17:42.077380Z","iopub.execute_input":"2024-03-22T20:17:42.077784Z","iopub.status.idle":"2024-03-22T20:17:42.131416Z","shell.execute_reply.started":"2024-03-22T20:17:42.077755Z","shell.execute_reply":"2024-03-22T20:17:42.130126Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Comparing amounts in tax_registry_a and tax_registry_c\nDespite the different feature description, for the cases where we have information from both A and C, the amounts match in ~93 % of cases. But there are also serious deviations.","metadata":{"execution":{"iopub.status.busy":"2024-03-22T20:07:02.090371Z","iopub.execute_input":"2024-03-22T20:07:02.090806Z","iopub.status.idle":"2024-03-22T20:07:02.098229Z","shell.execute_reply.started":"2024-03-22T20:07:02.090774Z","shell.execute_reply":"2024-03-22T20:07:02.096459Z"}}},{"cell_type":"code","source":"(tax_df[\"tax_a__amount_4527230A\"] / tax_df[\"tax_c__pmtamount_36A\"]).hist(bins=100)","metadata":{"execution":{"iopub.status.busy":"2024-03-22T20:18:14.190691Z","iopub.execute_input":"2024-03-22T20:18:14.191153Z","iopub.status.idle":"2024-03-22T20:18:14.648986Z","shell.execute_reply.started":"2024-03-22T20:18:14.191102Z","shell.execute_reply":"2024-03-22T20:18:14.647808Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"(tax_df[\"tax_a__amount_4527230A\"] / tax_df[\"tax_c__pmtamount_36A\"]).value_counts().nlargest(20)","metadata":{"execution":{"iopub.status.busy":"2024-03-22T20:18:15.083535Z","iopub.execute_input":"2024-03-22T20:18:15.084820Z","iopub.status.idle":"2024-03-22T20:18:15.121876Z","shell.execute_reply.started":"2024-03-22T20:18:15.084774Z","shell.execute_reply":"2024-03-22T20:18:15.121001Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"a_to_c_ratio = (tax_df[\"tax_a__amount_4527230A\"] / tax_df[\"tax_c__pmtamount_36A\"])\nmode = a_to_c_ratio.mode().iloc[0]\n\ndiffs_ac = tax_df[(a_to_c_ratio > mode + 0.001) | (a_to_c_ratio < mode - 0.001)][[\"WEEK_NUM\", \"case_id\", \"tax_a__amount_4527230A\", \"tax_c__pmtamount_36A\"]]\ndiffs_ac[\"ratio\"] = diffs_ac[\"tax_a__amount_4527230A\"] / diffs_ac[\"tax_c__pmtamount_36A\"]\nprint(f\"There are {len(diffs_ac)} cases with a (significantly) different ratio ({round(len(diffs_ac) / a_to_c_ratio.count(), 4) * 100} %)\")\n\ndiffs_ac.sort_values(\"ratio\")","metadata":{"execution":{"iopub.status.busy":"2024-03-22T20:18:51.524240Z","iopub.execute_input":"2024-03-22T20:18:51.524664Z","iopub.status.idle":"2024-03-22T20:18:51.572733Z","shell.execute_reply.started":"2024-03-22T20:18:51.524632Z","shell.execute_reply":"2024-03-22T20:18:51.571631Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## Comparing amounts in tax_registry_b and tax_registry_c\nThere are no common entries in from A and B\n","metadata":{"execution":{"iopub.status.busy":"2024-03-22T20:11:49.281727Z","iopub.execute_input":"2024-03-22T20:11:49.282116Z","iopub.status.idle":"2024-03-22T20:11:49.287461Z","shell.execute_reply.started":"2024-03-22T20:11:49.282088Z","shell.execute_reply":"2024-03-22T20:11:49.286196Z"}}},{"cell_type":"code","source":"b_to_c_ratio = (tax_df[\"tax_b__amount_4917619A\"] / tax_df[\"tax_c__pmtamount_36A\"])\nb_to_c_ratio.isna().mean()","metadata":{"execution":{"iopub.status.busy":"2024-03-22T20:19:07.295702Z","iopub.execute_input":"2024-03-22T20:19:07.296187Z","iopub.status.idle":"2024-03-22T20:19:07.312255Z","shell.execute_reply.started":"2024-03-22T20:19:07.296147Z","shell.execute_reply":"2024-03-22T20:19:07.310977Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}