{"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"},{"sourceId":8023315,"sourceType":"datasetVersion","datasetId":4728096}],"dockerImageVersionId":30673,"isInternetEnabled":false,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"#!pip install /kaggle/input/polars-0-20-18/polars-0.20.18-cp38-abi3-manylinux_2_17_x86_64.manylinux2014_x86_64.whl","metadata":{"execution":{"iopub.status.busy":"2024-04-15T08:43:56.408305Z","iopub.execute_input":"2024-04-15T08:43:56.409192Z","iopub.status.idle":"2024-04-15T08:43:56.443229Z","shell.execute_reply.started":"2024-04-15T08:43:56.409153Z","shell.execute_reply":"2024-04-15T08:43:56.442106Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv\nimport re\nimport polars as pl\nimport seaborn as sns\nimport sys\nimport gc\nimport psutil\nimport warnings\nwarnings.filterwarnings(\"ignore\")","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2024-04-19T14:30:19.209605Z","iopub.execute_input":"2024-04-19T14:30:19.210391Z","iopub.status.idle":"2024-04-19T14:30:21.850084Z","shell.execute_reply.started":"2024-04-19T14:30:19.210349Z","shell.execute_reply":"2024-04-19T14:30:21.848854Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(pl.__version__)\nprint(psutil.virtual_memory().available * 100 / psutil.virtual_memory().total)","metadata":{"execution":{"iopub.status.busy":"2024-04-19T09:58:35.837545Z","iopub.execute_input":"2024-04-19T09:58:35.837949Z","iopub.status.idle":"2024-04-19T09:58:35.843744Z","shell.execute_reply.started":"2024-04-19T09:58:35.837920Z","shell.execute_reply":"2024-04-19T09:58:35.842885Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def size_of_var(var):\n    return f\"size of object is {sys.getsizeof(var)} bytes\"","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:30:29.251459Z","iopub.execute_input":"2024-04-19T14:30:29.251974Z","iopub.status.idle":"2024-04-19T14:30:29.256966Z","shell.execute_reply.started":"2024-04-19T14:30:29.251942Z","shell.execute_reply":"2024-04-19T14:30:29.255674Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def set_table_dtypes(df: pl.DataFrame) -> pl.DataFrame:\n    # implement here all desired dtypes for tables\n    # the following is just an example\n    for col in df.columns:\n        # last letter of column name will help you determine the type\n        if col[-1] in (\"P\", \"A\"):\n            df = df.with_columns(pl.col(col).cast(pl.Float64).alias(col))\n\n    return df\n\ndef set_table_dtypes(df):\n    for col in df.columns:\n        if col in [\"case_id\", \"WEEK_NUM\", \"num_group1\", \"num_group2\"]:\n            df = df.with_columns(pl.col(col).cast(pl.Int64))\n        elif col in [\"date_decision\"]:\n            df = df.with_columns(pl.col(col).cast(pl.Date))\n        elif col[-1] in (\"P\", \"A\"):\n            df = df.with_columns(pl.col(col).cast(pl.Float64))\n        elif col[-1] in (\"M\",):\n            df = df.with_columns(pl.col(col).cast(pl.String))\n        elif col[-1] in (\"D\",):\n            df = df.with_columns(pl.col(col).cast(pl.Date))\n        #df = df.drop(\"date_decision\", \"MONTH\")\n    return df\n\ndef handle_dates(df):\n    for col in df.columns:\n        if col[-1] in (\"D\",):\n            df = df.with_columns(pl.col(col) - pl.col(\"date_decision\"))  #!!?\n            df = df.with_columns(pl.col(col).dt.total_days()) # t - t-1\n    df = df.drop(\"date_decision\", \"MONTH\")\n    return df\n\ndef convert_strings(df: pd.DataFrame) -> pd.DataFrame:\n    for col in df.columns:  \n        if df[col].dtype.name in ['object', 'string']:\n            df[col] = df[col].astype(\"string\").astype('category')\n            current_categories = df[col].cat.categories\n            new_categories = current_categories.to_list() + [\"Unknown\"]\n            new_dtype = pd.CategoricalDtype(categories=new_categories, ordered=True)\n            df[col] = df[col].astype(new_dtype)\n    return df","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:30:29.866097Z","iopub.execute_input":"2024-04-19T14:30:29.866537Z","iopub.status.idle":"2024-04-19T14:30:29.879154Z","shell.execute_reply.started":"2024-04-19T14:30:29.866506Z","shell.execute_reply":"2024-04-19T14:30:29.878105Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def reduce_mem_usage(df):\n    \"\"\" iterate through all the columns of a dataframe and modify the data type\n        to reduce memory usage.        \n    \"\"\"\n    start_mem = df.estimated_size() / 1024**2\n    print('Memory usage of dataframe is {:.2f} MB'.format(start_mem))\n    \n    for col in df.columns:\n        col_type = df[col].dtype\n        #print(col_type)\n        if str(col_type)==\"category\" or str(col_type)==\"Date\" or str(col_type)==\"String\" or col==\"case_id\":\n            continue\n        \n        if col_type != object:\n            c_min = df[col].min()\n            c_max = df[col].max()\n            if str(col_type)[:3] == 'int':\n                if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max:\n                    df = df.with_columns(pl.col(col).cast(pl.Int8))\n                elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:\n                    df = df.with_columns(pl.col(col).cast(pl.Int16))\n                elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:\n                    df = df.with_columns(pl.col(col).cast(pl.Int32))\n                elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:\n                    df = df.with_columns(pl.col(col).cast(pl.Int64))\n            else:\n#                 if c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max:\n#                     df = df.with_columns(pl.col(col).cast(pl.Float16))\n                if c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:\n                    df = df.with_columns(pl.col(col).cast(pl.Float32))\n                else:\n                    df = df.with_columns(pl.col(col).cast(pl.Float64))\n        else:\n            continue\n    end_mem = df.estimated_size() / 1024**2\n    print('Memory usage after optimization is: {:.2f} MB'.format(end_mem))\n    print('Decreased by {:.1f}%'.format(100 * (start_mem - end_mem) / start_mem))\n    \n    return df","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:30:31.739177Z","iopub.execute_input":"2024-04-19T14:30:31.740022Z","iopub.status.idle":"2024-04-19T14:30:31.753491Z","shell.execute_reply.started":"2024-04-19T14:30:31.739984Z","shell.execute_reply":"2024-04-19T14:30:31.752399Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# ***************************\n# <a href=\"https://www.kaggle.com/code/kunduruanil/credit-risk-data-understanding\">Link to undestanding Data </a>\n# ***************************\n\n# Pick features to join","metadata":{}},{"cell_type":"code","source":"train_base = pl.read_csv('/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_base.csv').pipe(set_table_dtypes)\nprint(\"start date in training data : \",train_base[\"date_decision\"].min(),\"end date in training data : \",train_base[\"date_decision\"].max(),end='\\n\\n')\nprint(train_base.shape)\nprint(\"Sample of Training Base Table below : \")\ntrain_base.head()","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:30:39.586779Z","iopub.execute_input":"2024-04-19T14:30:39.587382Z","iopub.status.idle":"2024-04-19T14:30:40.237781Z","shell.execute_reply.started":"2024-04-19T14:30:39.587352Z","shell.execute_reply":"2024-04-19T14:30:40.236711Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# feature definations file\n# fd = pd.read_csv('/kaggle/input/home-credit-credit-risk-model-stability/feature_definitions.csv')\n# print(fd.shape)\n# fd = dict(zip(fd.Variable,fd.Description))","metadata":{"execution":{"iopub.status.busy":"2024-04-19T10:36:45.614637Z","iopub.execute_input":"2024-04-19T10:36:45.615087Z","iopub.status.idle":"2024-04-19T10:36:45.636729Z","shell.execute_reply.started":"2024-04-19T10:36:45.615049Z","shell.execute_reply":"2024-04-19T10:36:45.635649Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# read_data","metadata":{}},{"cell_type":"code","source":"#train_basetable = pl.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_base.csv\")\ntrain_static = pl.concat(\n    [\n        pl.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_static_0_0.csv\").pipe(set_table_dtypes),\n        pl.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_static_0_1.csv\").pipe(set_table_dtypes),\n    ],\n    how=\"vertical_relaxed\", \n)\ntrain_static_cb = pl.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_static_cb_0.csv\").pipe(set_table_dtypes)\ntrain_person_1 = pl.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_person_1.csv\").pipe(set_table_dtypes)\ntrain_credit_bureau_b_2 = pl.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/train/train_credit_bureau_b_2.csv\").pipe(set_table_dtypes)","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:30:51.254771Z","iopub.execute_input":"2024-04-19T14:30:51.255108Z","iopub.status.idle":"2024-04-19T14:31:09.323067Z","shell.execute_reply.started":"2024-04-19T14:30:51.255083Z","shell.execute_reply":"2024-04-19T14:31:09.321690Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# We need to use aggregation functions in tables with depth > 1, so tables that contain num_group1 column or \n# also num_group2 column.\ntrain_person_1_feats_1 = train_person_1.group_by(\"case_id\").agg(\n    pl.col(\"mainoccupationinc_384A\").max().alias(\"mainoccupationinc_384A_max\"),\n    (pl.col(\"incometype_1044T\") == \"SELFEMPLOYED\").max().alias(\"mainoccupationinc_384A_any_selfemployed\")\n)\n\n# Here num_group1=0 has special meaning, it is the person who applied for the loan.\ntrain_person_1_feats_2 = train_person_1.select([\"case_id\", \"num_group1\", \"housetype_905L\"]).filter(\n    pl.col(\"num_group1\") == 0\n).drop(\"num_group1\").rename({\"housetype_905L\": \"person_housetype\"})\n\n# Here we have num_goup1 and num_group2, so we need to aggregate again.\ntrain_credit_bureau_b_2_feats = train_credit_bureau_b_2.group_by(\"case_id\").agg(\n    pl.col(\"pmts_pmtsoverdue_635A\").max().alias(\"pmts_pmtsoverdue_635A_max\"),\n    (pl.col(\"pmts_dpdvalue_108P\") > 31).max().alias(\"pmts_dpdvalue_108P_over31\")\n)\n\n# Join all tables together.\ndata = train_base.join(\n    train_static, how=\"left\", on=\"case_id\"\n).join(\n    train_static_cb, how=\"left\", on=\"case_id\"\n).join(\n    train_person_1_feats_1, how=\"left\", on=\"case_id\"\n).join(\n    train_person_1_feats_2, how=\"left\", on=\"case_id\"\n).join(\n    train_credit_bureau_b_2_feats, how=\"left\", on=\"case_id\"\n)","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:31:09.324729Z","iopub.execute_input":"2024-04-19T14:31:09.325045Z","iopub.status.idle":"2024-04-19T14:31:13.183828Z","shell.execute_reply.started":"2024-04-19T14:31:09.325018Z","shell.execute_reply":"2024-04-19T14:31:13.182980Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# memory managment\ndata = reduce_mem_usage(data)\ntrain_base,train_basetable,train_static,train_static_cb,train_person_1,train_credit_bureau_b_2,train_person_1_feats_1,train_person_1_feats_2,train_credit_bureau_b_2_feats = [None]*9\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:31:19.829159Z","iopub.execute_input":"2024-04-19T14:31:19.829524Z","iopub.status.idle":"2024-04-19T14:31:23.541323Z","shell.execute_reply.started":"2024-04-19T14:31:19.829496Z","shell.execute_reply":"2024-04-19T14:31:23.540048Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_basetable = pl.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/test/test_base.csv\").pipe(set_table_dtypes)\ntest_static = pl.concat(\n    [\n        pl.read_csv( \"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/test/test_static_0_0.csv\").pipe(set_table_dtypes),\n        pl.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/test/test_static_0_1.csv\").pipe(set_table_dtypes),\n        pl.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/test/test_static_0_2.csv\").pipe(set_table_dtypes),\n    ],\n    how=\"vertical_relaxed\",\n)\ntest_static_cb = pl.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/test/test_static_cb_0.csv\").pipe(set_table_dtypes)\ntest_person_1 = pl.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/test/test_person_1.csv\").pipe(set_table_dtypes)\ntest_credit_bureau_b_2 = pl.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/test/test_credit_bureau_b_2.csv\").pipe(set_table_dtypes)","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:31:29.613994Z","iopub.execute_input":"2024-04-19T14:31:29.614617Z","iopub.status.idle":"2024-04-19T14:31:29.684821Z","shell.execute_reply.started":"2024-04-19T14:31:29.614585Z","shell.execute_reply":"2024-04-19T14:31:29.683849Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_person_1_feats_1 = test_person_1.group_by(\"case_id\").agg(\n    pl.col(\"mainoccupationinc_384A\").max().alias(\"mainoccupationinc_384A_max\"),\n    (pl.col(\"incometype_1044T\") == \"SELFEMPLOYED\").max().alias(\"mainoccupationinc_384A_any_selfemployed\")\n)\n\ntest_person_1_feats_2 = test_person_1.select([\"case_id\", \"num_group1\", \"housetype_905L\"]).filter(\n    pl.col(\"num_group1\") == 0\n).drop(\"num_group1\").rename({\"housetype_905L\": \"person_housetype\"})\n\ntest_credit_bureau_b_2_feats = test_credit_bureau_b_2.group_by(\"case_id\").agg(\n    pl.col(\"pmts_pmtsoverdue_635A\").max().alias(\"pmts_pmtsoverdue_635A_max\"),\n    (pl.col(\"pmts_dpdvalue_108P\") > 31).max().alias(\"pmts_dpdvalue_108P_over31\")\n)\n\ndata_submission = test_basetable.join(\n    test_static, how=\"left\", on=\"case_id\"\n).join(\n    test_static_cb, how=\"left\", on=\"case_id\"\n).join(\n    test_person_1_feats_1, how=\"left\", on=\"case_id\"\n).join(\n    test_person_1_feats_2, how=\"left\", on=\"case_id\"\n).join(\n    test_credit_bureau_b_2_feats, how=\"left\", on=\"case_id\"\n)","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:31:33.215996Z","iopub.execute_input":"2024-04-19T14:31:33.216394Z","iopub.status.idle":"2024-04-19T14:31:33.236733Z","shell.execute_reply.started":"2024-04-19T14:31:33.216359Z","shell.execute_reply":"2024-04-19T14:31:33.235688Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# memory managment\ntest_basetable,test_static,test_static_cb,test_person_1,test_credit_bureau_b_2,test_person_1_feats_1,test_person_1_feats_2,test_credit_bureau_b_2_feats = [None]*8\nprint(gc.collect())\nprint(psutil.virtual_memory().available * 100 / psutil.virtual_memory().total)","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:31:42.430404Z","iopub.execute_input":"2024-04-19T14:31:42.431289Z","iopub.status.idle":"2024-04-19T14:31:42.531053Z","shell.execute_reply.started":"2024-04-19T14:31:42.431238Z","shell.execute_reply":"2024-04-19T14:31:42.529890Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# applprev 1","metadata":{}},{"cell_type":"code","source":"ts = pl.concat(\n    [\n        pl.read_parquet( \"/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_applprev_1_0.parquet\").pipe(set_table_dtypes),\n        pl.read_parquet( \"/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_applprev_1_1.parquet\").pipe(set_table_dtypes)\n    ],\n    how=\"vertical_relaxed\",\n)\nprint(ts.shape)\nts.sample(3)","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:31:46.696691Z","iopub.execute_input":"2024-04-19T14:31:46.697066Z","iopub.status.idle":"2024-04-19T14:31:55.420832Z","shell.execute_reply.started":"2024-04-19T14:31:46.697036Z","shell.execute_reply":"2024-04-19T14:31:55.419625Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"applprev = ts.groupby(\"case_id\").agg(pl.sum(\"actualdpd_943P\").alias(\"sum_dpd\"),\n                          pl.max(\"annuity_853A\").alias(\"max_annuity_amount\"),\n                         pl.count(\"approvaldate_319D\").alias(\"count_approval\"),\n                         pl.max(\"byoccupationinc_3656910L\").alias(\"max_salary\"),\n                         pl.n_unique(\"cancelreason_3545846M\").alias(\"count_cancel_reasons\"),\n                         pl.max(\"childnum_21L\").alias(\"max_children\"),\n                         pl.count(\"creationdate_885D\").alias(\"count_created\"),\n                         pl.max(\"credacc_actualbalance_314A\").alias(\"max_balance_credit\"),\n                         pl.max(\"credacc_credlmt_575A\").alias(\"max_credit_approval\"),\n                         pl.max(\"credacc_maxhisbal_375A\").alias(\"max_historical_balance\"),\n                         pl.min(\"credacc_minhisbal_90A\").alias(\"min_historical_balance\"),\n                         pl.sum(\"credacc_transactions_402L\").alias(\"sum_transactions\"),\n                         pl.max(\"credamount_590A\").alias(\"max_credit_limit\"),\n                         pl.sum(\"currdebt_94A\").alias(\"sum_credit_debt\"),\n                         pl.sum(\"downpmt_134A\").alias(\"sum_down_payment\"),\n                         pl.max(\"mainoccupationinc_437A\").alias(\"max_mainincome\"),\n                         pl.sum(\"outstandingdebt_522A\").alias(\"sum_debt\"),\n                         pl.sum(\"pmtnum_8L\").alias(\"sum_payments\"),\n                         pl.sum(\"tenor_203L\").alias(\"sum_installments\"))\napplprev = applprev.with_columns(address_more_4=ts.groupby(\"case_id\").agg(pl.n_unique(\"district_544M\"))[\"district_544M\"]>=4)\napplprev.shape","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:32:01.704619Z","iopub.execute_input":"2024-04-19T14:32:01.705272Z","iopub.status.idle":"2024-04-19T14:32:04.355876Z","shell.execute_reply.started":"2024-04-19T14:32:01.705242Z","shell.execute_reply":"2024-04-19T14:32:04.354852Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# not_using = [\"credacc_status_367L\",\"credtype_587L\",\"dateactivated_425D\",\"district_544M\",\"dtlastpmt_581D\",\"dtlastpmtallstes_3545839D\",\"education_1138M\",\n#              \"employedfrom_700D\",\"familystate_726L\",\"firstnonzeroinstldate_307D\",\"inittransactioncode_279L\",\"isbidproduct_390L\",\"isdebitcard_527L\",\n#             \"maxdpdtolerance_577P\",\"postype_4733339M\",\"profession_152M\",\"rejectreason_755M\",\"rejectreasonclient_4145042M\",\"revolvingaccount_394A\",\n#             \"status_219L\"]","metadata":{"execution":{"iopub.status.busy":"2024-04-19T11:21:45.440276Z","iopub.execute_input":"2024-04-19T11:21:45.440693Z","iopub.status.idle":"2024-04-19T11:21:45.445748Z","shell.execute_reply.started":"2024-04-19T11:21:45.440657Z","shell.execute_reply":"2024-04-19T11:21:45.444663Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# applprev 2","metadata":{}},{"cell_type":"code","source":"# ts = pd.read_parquet(f'/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_applprev_2.parquet')\n# print(ts.shape)\n# ts.sample(3)","metadata":{"execution":{"iopub.status.busy":"2024-04-19T11:21:45.449924Z","iopub.execute_input":"2024-04-19T11:21:45.450718Z","iopub.status.idle":"2024-04-19T11:21:45.460210Z","shell.execute_reply.started":"2024-04-19T11:21:45.450687Z","shell.execute_reply":"2024-04-19T11:21:45.459192Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# for col in ts.columns:\n#     if col in fd:\n#         print(col,\"  \",fd[col])","metadata":{"execution":{"iopub.status.busy":"2024-04-19T11:21:45.461842Z","iopub.execute_input":"2024-04-19T11:21:45.462582Z","iopub.status.idle":"2024-04-19T11:21:45.469383Z","shell.execute_reply.started":"2024-04-19T11:21:45.462546Z","shell.execute_reply":"2024-04-19T11:21:45.468346Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data = data.join(applprev,how='left',on=\"case_id\")\ndata.shape","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:32:10.575073Z","iopub.execute_input":"2024-04-19T14:32:10.575604Z","iopub.status.idle":"2024-04-19T14:32:11.194143Z","shell.execute_reply.started":"2024-04-19T14:32:10.575552Z","shell.execute_reply":"2024-04-19T14:32:11.193358Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test = pl.concat(\n    [\n        pl.read_csv( \"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/test/test_applprev_1_0.csv\").pipe(set_table_dtypes),\n        pl.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/test/test_applprev_1_1.csv\").pipe(set_table_dtypes),\n        pl.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/test/test_applprev_1_2.csv\").pipe(set_table_dtypes),\n    ],\n    how=\"vertical_relaxed\",\n)\ntest.shape","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:32:14.177685Z","iopub.execute_input":"2024-04-19T14:32:14.178127Z","iopub.status.idle":"2024-04-19T14:32:14.213155Z","shell.execute_reply.started":"2024-04-19T14:32:14.178086Z","shell.execute_reply":"2024-04-19T14:32:14.212217Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"applprev_test = test.groupby(\"case_id\").agg(pl.sum(\"actualdpd_943P\").alias(\"sum_dpd\"),\n                          pl.max(\"annuity_853A\").alias(\"max_annuity_amount\"),\n                         pl.count(\"approvaldate_319D\").alias(\"count_approval\"),\n                         pl.max(\"byoccupationinc_3656910L\").alias(\"max_salary\"),\n                         pl.n_unique(\"cancelreason_3545846M\").alias(\"count_cancel_reasons\"),\n                         pl.max(\"childnum_21L\").alias(\"max_children\"),\n                         pl.count(\"creationdate_885D\").alias(\"count_created\"),\n                         pl.max(\"credacc_actualbalance_314A\").alias(\"max_balance_credit\"),\n                         pl.max(\"credacc_credlmt_575A\").alias(\"max_credit_approval\"),\n                         pl.max(\"credacc_maxhisbal_375A\").alias(\"max_historical_balance\"),\n                         pl.min(\"credacc_minhisbal_90A\").alias(\"min_historical_balance\"),\n                         pl.sum(\"credacc_transactions_402L\").alias(\"sum_transactions\"),\n                         pl.max(\"credamount_590A\").alias(\"max_credit_limit\"),\n                         pl.sum(\"currdebt_94A\").alias(\"sum_credit_debt\"),\n                         pl.sum(\"downpmt_134A\").alias(\"sum_down_payment\"),\n                         pl.max(\"mainoccupationinc_437A\").alias(\"max_mainincome\"),\n                         pl.sum(\"outstandingdebt_522A\").alias(\"sum_debt\"),\n                         pl.sum(\"pmtnum_8L\").alias(\"sum_payments\"),\n                         pl.sum(\"tenor_203L\").alias(\"sum_installments\"))\napplprev_test = applprev_test.with_columns(address_more_4=test.groupby(\"case_id\").agg(pl.n_unique(\"district_544M\"))[\"district_544M\"]>=4)\napplprev_test.shape","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:32:17.269468Z","iopub.execute_input":"2024-04-19T14:32:17.269816Z","iopub.status.idle":"2024-04-19T14:32:17.283818Z","shell.execute_reply.started":"2024-04-19T14:32:17.269789Z","shell.execute_reply":"2024-04-19T14:32:17.282731Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data_submission = data_submission.join(applprev_test,how='left',on='case_id')\napplprev_test = ''\ngc.collect()\ndata_submission.shape","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:32:29.527468Z","iopub.execute_input":"2024-04-19T14:32:29.527812Z","iopub.status.idle":"2024-04-19T14:32:29.617647Z","shell.execute_reply.started":"2024-04-19T14:32:29.527786Z","shell.execute_reply":"2024-04-19T14:32:29.616910Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# memory managment\ndata = reduce_mem_usage(data)\napplprev_test,test,applprev,ts = [None]*4\nprint(gc.collect())\nprint(psutil.virtual_memory().available * 100 / psutil.virtual_memory().total)","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:32:34.291069Z","iopub.execute_input":"2024-04-19T14:32:34.292184Z","iopub.status.idle":"2024-04-19T14:32:35.205164Z","shell.execute_reply.started":"2024-04-19T14:32:34.292142Z","shell.execute_reply":"2024-04-19T14:32:35.204251Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# need to check how to add categarical values when we have duplicate case id's","metadata":{}},{"cell_type":"markdown","source":"# tax_registry Analysis","metadata":{}},{"cell_type":"code","source":"ts  = pl.read_parquet(f'/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_tax_registry_a_1.parquet').pipe(set_table_dtypes)\nprint(gc.collect())\nprint(ts.shape)\nts.sample(3)","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:33:12.789361Z","iopub.execute_input":"2024-04-19T14:33:12.789716Z","iopub.status.idle":"2024-04-19T14:33:13.631515Z","shell.execute_reply.started":"2024-04-19T14:33:12.789688Z","shell.execute_reply":"2024-04-19T14:33:13.630490Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# for col in ts.columns:\n#     if col in fd:\n#         print(col,\"  \",fd[col])","metadata":{"execution":{"iopub.status.busy":"2024-04-15T08:44:34.067754Z","iopub.execute_input":"2024-04-15T08:44:34.068264Z","iopub.status.idle":"2024-04-15T08:44:34.074532Z","shell.execute_reply.started":"2024-04-15T08:44:34.068223Z","shell.execute_reply":"2024-04-15T08:44:34.072892Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"taxa = ts.group_by(\"case_id\").agg(pl.sum(\"amount_4527230A\").alias(\"sum_tax_paid\"),\n                          pl.n_unique(\"recorddate_4527225D\").alias(\"nunique_taxpaid\"))\ntaxa.shape","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:33:17.836856Z","iopub.execute_input":"2024-04-19T14:33:17.839863Z","iopub.status.idle":"2024-04-19T14:33:18.468415Z","shell.execute_reply.started":"2024-04-19T14:33:17.839819Z","shell.execute_reply":"2024-04-19T14:33:18.467422Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ts  = pl.read_parquet(f'/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_tax_registry_b_1.parquet').pipe(set_table_dtypes)\nprint(gc.collect())\nprint(ts.shape)\nts.sample(3)","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:33:18.473314Z","iopub.execute_input":"2024-04-19T14:33:18.473715Z","iopub.status.idle":"2024-04-19T14:33:18.927539Z","shell.execute_reply.started":"2024-04-19T14:33:18.473681Z","shell.execute_reply":"2024-04-19T14:33:18.926490Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# for col in ts.columns:\n#     if col in fd:\n#         print(col,\"  \",fd[col])","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:33:19.176203Z","iopub.execute_input":"2024-04-19T14:33:19.176587Z","iopub.status.idle":"2024-04-19T14:33:19.181852Z","shell.execute_reply.started":"2024-04-19T14:33:19.176559Z","shell.execute_reply":"2024-04-19T14:33:19.180616Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"taxb = ts.group_by(\"case_id\").agg(pl.sum(\"amount_4917619A\").alias(\"sum_tax_paid_b\"),\n                          pl.n_unique(\"deductiondate_4917603D\").alias(\"nunique_taxpaid_b\"))\ntaxb.shape","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:33:19.865480Z","iopub.execute_input":"2024-04-19T14:33:19.866022Z","iopub.status.idle":"2024-04-19T14:33:20.030582Z","shell.execute_reply.started":"2024-04-19T14:33:19.865995Z","shell.execute_reply":"2024-04-19T14:33:20.029411Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"ts  = pl.read_parquet(f'/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_tax_registry_c_1.parquet').pipe(set_table_dtypes)\nprint(gc.collect())\nprint(ts.shape)\nts.sample(3)","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:33:20.519989Z","iopub.execute_input":"2024-04-19T14:33:20.521083Z","iopub.status.idle":"2024-04-19T14:33:21.429472Z","shell.execute_reply.started":"2024-04-19T14:33:20.521044Z","shell.execute_reply":"2024-04-19T14:33:21.428739Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"taxc = ts.group_by(\"case_id\").agg(pl.sum(\"pmtamount_36A\").alias(\"sum_tax_credit_bureau_c\"),\n                          pl.n_unique(\"processingdate_168D\").alias(\"nunique_taxpaid_c\"))\ntaxc.shape","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:33:21.431084Z","iopub.execute_input":"2024-04-19T14:33:21.431394Z","iopub.status.idle":"2024-04-19T14:33:21.941451Z","shell.execute_reply.started":"2024-04-19T14:33:21.431368Z","shell.execute_reply":"2024-04-19T14:33:21.940652Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data = data.join(taxa,how=\"left\",on='case_id').join(taxb,how='left',on='case_id').join(taxc,how='left',on=\"case_id\")\ndata.shape","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:33:21.944280Z","iopub.execute_input":"2024-04-19T14:33:21.945760Z","iopub.status.idle":"2024-04-19T14:33:22.202424Z","shell.execute_reply.started":"2024-04-19T14:33:21.945729Z","shell.execute_reply":"2024-04-19T14:33:22.201507Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test  = pl.read_csv( \"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/test/test_tax_registry_a_1.csv\").pipe(set_table_dtypes)\ntesta = test.group_by(\"case_id\").agg(pl.sum(\"amount_4527230A\").alias(\"sum_tax_paid\"),\n                          pl.n_unique(\"recorddate_4527225D\").alias(\"nunique_taxpaid\"))\ntesta.shape","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:33:23.721776Z","iopub.execute_input":"2024-04-19T14:33:23.722483Z","iopub.status.idle":"2024-04-19T14:33:23.734099Z","shell.execute_reply.started":"2024-04-19T14:33:23.722447Z","shell.execute_reply":"2024-04-19T14:33:23.733094Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test  = pl.read_csv( \"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/test/test_tax_registry_b_1.csv\").pipe(set_table_dtypes)\ntestb = test.group_by(\"case_id\").agg(pl.sum(\"amount_4917619A\").alias(\"sum_tax_paid_b\"),\n                          pl.n_unique(\"deductiondate_4917603D\").alias(\"nunique_taxpaid_b\"))\ntestb.shape","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:33:24.757969Z","iopub.execute_input":"2024-04-19T14:33:24.758604Z","iopub.status.idle":"2024-04-19T14:33:24.769284Z","shell.execute_reply.started":"2024-04-19T14:33:24.758572Z","shell.execute_reply":"2024-04-19T14:33:24.768557Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test  = pl.read_csv( \"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/test/test_tax_registry_c_1.csv\").pipe(set_table_dtypes)\nprint(test.shape)\ntestc = test.group_by(\"case_id\").agg(pl.sum(\"pmtamount_36A\").alias(\"sum_tax_credit_bureau_c\"),\n                          pl.n_unique(\"processingdate_168D\").alias(\"nunique_taxpaid_c\"))\ntestc.shape","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:33:25.941901Z","iopub.execute_input":"2024-04-19T14:33:25.942659Z","iopub.status.idle":"2024-04-19T14:33:25.957398Z","shell.execute_reply.started":"2024-04-19T14:33:25.942618Z","shell.execute_reply":"2024-04-19T14:33:25.956360Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"testc = testc.to_pandas()\ntestc[\"case_id\"] = testc[\"case_id\"].astype(\"int\")\ntestc = pl.from_pandas(testc)\ntestc","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:33:26.980579Z","iopub.execute_input":"2024-04-19T14:33:26.980940Z","iopub.status.idle":"2024-04-19T14:33:27.027445Z","shell.execute_reply.started":"2024-04-19T14:33:26.980912Z","shell.execute_reply":"2024-04-19T14:33:27.026392Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data_submission = data_submission.join(testa,how=\"left\",on='case_id').join(testb,how='left',on='case_id').join(testc,how='left',on=\"case_id\")\ndata_submission.shape","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:33:28.844813Z","iopub.execute_input":"2024-04-19T14:33:28.845574Z","iopub.status.idle":"2024-04-19T14:33:28.857023Z","shell.execute_reply.started":"2024-04-19T14:33:28.845538Z","shell.execute_reply":"2024-04-19T14:33:28.856105Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# memory managment\ndata = reduce_mem_usage(data)\ntestc,test,testb,testa,taxc,taxb,taxa,ts = [None]*8\nprint(gc.collect())\nprint(psutil.virtual_memory().available * 100 / psutil.virtual_memory().total)","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:33:30.096581Z","iopub.execute_input":"2024-04-19T14:33:30.096922Z","iopub.status.idle":"2024-04-19T14:33:30.552120Z","shell.execute_reply.started":"2024-04-19T14:33:30.096897Z","shell.execute_reply":"2024-04-19T14:33:30.551155Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# credit_bureau","metadata":{}},{"cell_type":"code","source":"# Training data\nts = pl.concat(\n    [\n        pl.read_parquet( \"/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_credit_bureau_a_1_0.parquet\").pipe(set_table_dtypes),\n        pl.read_parquet( \"/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/train_credit_bureau_a_1_1.parquet\").pipe(set_table_dtypes)\n    ],\n    how=\"vertical_relaxed\",\n)\nprint(ts.shape)\nts.sample(3)","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:33:54.102001Z","iopub.execute_input":"2024-04-19T14:33:54.102838Z","iopub.status.idle":"2024-04-19T14:34:12.130492Z","shell.execute_reply.started":"2024-04-19T14:33:54.102803Z","shell.execute_reply":"2024-04-19T14:34:12.129545Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# not_using = [\"classificationofcontr_13M\",\"classificationofcontr_400M\",\"contractst_545M\",\"contractst_964M\",\"financialinstitution_382M\",\n#             \"financialinstitution_591M\",\"instlamount_852A\",\"periodicityofpmts_1102L\",\"periodicityofpmts_837L\",\"purposeofcred_426M\",\n#             \"purposeofcred_874M\",\"subjectrole_182M\",\"subjectrole_93M\"]\n# date = [\"dateofcredend_289D\",\"dateofcredend_353D\",\"dateofcredstart_181D\",\"dateofcredstart_739D\",\"dateofrealrepmt_138D\",\"lastupdate_1112D\",\n#        \"lastupdate_388D\",\"numberofoverdueinstlmaxdat_148D\",\"numberofoverdueinstlmaxdat_641D\",\"overdueamountmax2date_1002D\",\n#         \"overdueamountmaxdatemonth_365T\",\"refreshdate_3813885D\",\"\"]","metadata":{"execution":{"iopub.status.busy":"2024-04-15T08:44:52.382410Z","iopub.execute_input":"2024-04-15T08:44:52.382976Z","iopub.status.idle":"2024-04-15T08:44:52.388405Z","shell.execute_reply.started":"2024-04-15T08:44:52.382945Z","shell.execute_reply":"2024-04-15T08:44:52.386883Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"cb = ts.group_by(\"case_id\").agg(pl.max(\"annualeffectiverate_199L\").alias(\"max_interest_rate_closed\"),\n                           pl.max(\"annualeffectiverate_63L\").alias(\"max_intrest_rate_current\"),\n                          pl.max(\"credlmt_230A\").alias(\"max_credit_limit_closed\"),\n                           pl.max(\"credlmt_935A\").alias(\"max_credit_limit_current\"),\n                           pl.sum(\"debtoutstand_525A\").alias(\"sum_outstand_closed\"),\n                          pl.sum(\"debtoverdue_47A\").alias(\"sum_overdue_current\"),\n                          pl.mean(\"dpdmax_139P\").alias(\"mean_due_current\"),\n                          pl.mean(\"dpdmax_757P\").alias(\"mean_due_closed\"),\n                          pl.max(\"dpdmaxdatemonth_442T\").alias(\"max_dpd\"),\n                          pl.count(\"dpdmaxdatemonth_89T\").alias(\"count_dpd_month\"),\n                          pl.count(\"dpdmaxdateyear_596T\").alias(\"count_dpd_year\"),\n                          pl.count(\"dpdmaxdateyear_896T\").alias(\"count_dpd_dateyear\"),\n                          pl.max(\"instlamount_768A\").alias(\"max_installment_amount_closed\"),\n                          pl.sum(\"interestrate_508L\").alias(\"sum_intrestrate\"),\n                          pl.mean(\"monthlyinstlamount_332A\").alias(\"mean_monthlyinstlamount_active\"),\n                          pl.mean(\"monthlyinstlamount_674A\").alias(\"mean_monthlyinstlamount_closed\"),\n                          pl.mean(\"nominalrate_281L\").alias(\"mean_nominalrate_active\"),\n                          pl.mean(\"nominalrate_498L\").alias(\"mean_nominalrate_closed\"),\n                          pl.sum(\"numberofcontrsvalue_258L\").alias(\"sum_numberofcontrsvalue_active\"),\n                          pl.sum(\"numberofcontrsvalue_358L\").alias(\"sum_numberofcontrsvalue_closed\"),\n                          pl.sum(\"numberofoutstandinstls_520L\").alias(\"sum_numberofoutstandinstls_closed\"),\n                          pl.sum(\"numberofoutstandinstls_59L\").alias(\"sum_numberofoutstandinstls_active\"),\n                          pl.sum(\"numberofoverdueinstlmax_1039L\").alias(\"sum_numberofoverdueinstlmax_active\"),\n                          pl.sum(\"numberofoverdueinstlmax_1151L\").alias(\"sum_numberofoverdueinstlmax_closed\"),\n                          pl.max(\"numberofoverdueinstls_725L\").alias(\"max_numberofoverdueinstls_active\"),\n                          pl.max(\"numberofoverdueinstls_834L\").alias(\"max_numberofoverdueinstls_closed\"),\n                          pl.max(\"outstandingamount_354A\").alias(\"max_outstandingamount_closed\"),\n                          pl.max(\"outstandingamount_362A\").alias(\"max_outstandingamount_active\"),\n                          pl.sum(\"overdueamount_31A\").alias(\"sum_overdueamount_closed\"),\n                          pl.sum(\"overdueamount_659A\").alias(\"sum_overdueamount_active\"),\n                          pl.max(\"overdueamountmax2_14A\").alias(\"max_overdueamountmax2_active\"),\n                          pl.max(\"overdueamountmax2_398A\").alias(\"max_overdueamountmax2_closed\"),\n                          pl.mean('prolongationcount_1120L').alias(\"mean_prolongationcount_closed\"),\n                          pl.mean(\"prolongationcount_599L\").alias(\"mean_prolongationcount_active\"),\n                          pl.sum(\"residualamount_488A\").alias(\"sum_residualamount_closed\"),\n                          pl.sum(\"residualamount_856A\").alias(\"sum_residualamount_active\"),\n                          pl.mean(\"totalamount_6A\").alias(\"mean_totalamount_closed\"),\n                          pl.mean(\"totalamount_996A\").alias(\"mean_totalamount_active\"),\n                          pl.sum(\"totaldebtoverduevalue_178A\").alias(\"sum_totaldebtoverduevalue_active\"),\n                          pl.sum(\"totaldebtoverduevalue_718A\").alias(\"sum_totaldebtoverduevalue_closed\"),\n                          pl.sum(\"totaloutstanddebtvalue_39A\").alias(\"sum_totaloutstanddebtvalue_active\"),\n                          pl.sum(\"totaloutstanddebtvalue_668A\").alias(\"sum_totaloutstanddebtvalue_closed\"))\ncb.shape","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:34:12.132887Z","iopub.execute_input":"2024-04-19T14:34:12.133223Z","iopub.status.idle":"2024-04-19T14:34:14.974241Z","shell.execute_reply.started":"2024-04-19T14:34:12.133192Z","shell.execute_reply":"2024-04-19T14:34:14.973134Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data = data.join(cb,how=\"left\",on=\"case_id\")\ndata.shape","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:34:14.975934Z","iopub.execute_input":"2024-04-19T14:34:14.976715Z","iopub.status.idle":"2024-04-19T14:34:15.647261Z","shell.execute_reply.started":"2024-04-19T14:34:14.976677Z","shell.execute_reply":"2024-04-19T14:34:15.646479Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#data.write_parquet(\"credit_risk_dataset.parquet\")","metadata":{"execution":{"iopub.status.busy":"2024-04-15T08:44:56.843000Z","iopub.execute_input":"2024-04-15T08:44:56.843328Z","iopub.status.idle":"2024-04-15T08:44:56.849055Z","shell.execute_reply.started":"2024-04-15T08:44:56.843302Z","shell.execute_reply":"2024-04-15T08:44:56.847489Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test = pl.concat(\n    [\n        pl.read_csv( \"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/test/test_credit_bureau_a_1_0.csv\").pipe(set_table_dtypes),\n        pl.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/test/test_credit_bureau_a_1_1.csv\").pipe(set_table_dtypes),\n        pl.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/test/test_credit_bureau_a_1_2.csv\").pipe(set_table_dtypes),\n        pl.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/test/test_credit_bureau_a_1_3.csv\").pipe(set_table_dtypes),\n        pl.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/csv_files/test/test_credit_bureau_a_1_4.csv\").pipe(set_table_dtypes),\n    ],\n    how=\"vertical_relaxed\",\n)\ntest.shape","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:55:54.597110Z","iopub.execute_input":"2024-04-19T14:55:54.597489Z","iopub.status.idle":"2024-04-19T14:55:54.700362Z","shell.execute_reply.started":"2024-04-19T14:55:54.597460Z","shell.execute_reply":"2024-04-19T14:55:54.699146Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_cb = test.group_by(\"case_id\").agg(pl.max(\"annualeffectiverate_199L\").alias(\"max_interest_rate_closed\"),\n                           pl.max(\"annualeffectiverate_63L\").alias(\"max_intrest_rate_current\"),\n                          pl.max(\"credlmt_230A\").alias(\"max_credit_limit_closed\"),\n                           pl.max(\"credlmt_935A\").alias(\"max_credit_limit_current\"),\n                           pl.sum(\"debtoutstand_525A\").alias(\"sum_outstand_closed\"),\n                          pl.sum(\"debtoverdue_47A\").alias(\"sum_overdue_current\"),\n                          pl.mean(\"dpdmax_139P\").alias(\"mean_due_current\"),\n                          pl.mean(\"dpdmax_757P\").alias(\"mean_due_closed\"),\n                          pl.max(\"dpdmaxdatemonth_442T\").alias(\"max_dpd\"),\n                          pl.count(\"dpdmaxdatemonth_89T\").alias(\"count_dpd_month\"),\n                          pl.count(\"dpdmaxdateyear_596T\").alias(\"count_dpd_year\"),\n                          pl.count(\"dpdmaxdateyear_896T\").alias(\"count_dpd_dateyear\"),\n                          pl.max(\"instlamount_768A\").alias(\"max_installment_amount_closed\"),\n                          pl.sum(\"interestrate_508L\").alias(\"sum_intrestrate\"),\n                          pl.mean(\"monthlyinstlamount_332A\").alias(\"mean_monthlyinstlamount_active\"),\n                          pl.mean(\"monthlyinstlamount_674A\").alias(\"mean_monthlyinstlamount_closed\"),\n                          pl.mean(\"nominalrate_281L\").alias(\"mean_nominalrate_active\"),\n                          pl.mean(\"nominalrate_498L\").alias(\"mean_nominalrate_closed\"),\n                          pl.sum(\"numberofcontrsvalue_258L\").alias(\"sum_numberofcontrsvalue_active\"),\n                          pl.sum(\"numberofcontrsvalue_358L\").alias(\"sum_numberofcontrsvalue_closed\"),\n                          pl.sum(\"numberofoutstandinstls_520L\").alias(\"sum_numberofoutstandinstls_closed\"),\n                          pl.sum(\"numberofoutstandinstls_59L\").alias(\"sum_numberofoutstandinstls_active\"),\n                          pl.sum(\"numberofoverdueinstlmax_1039L\").alias(\"sum_numberofoverdueinstlmax_active\"),\n                          pl.sum(\"numberofoverdueinstlmax_1151L\").alias(\"sum_numberofoverdueinstlmax_closed\"),\n                          pl.max(\"numberofoverdueinstls_725L\").alias(\"max_numberofoverdueinstls_active\"),\n                          pl.max(\"numberofoverdueinstls_834L\").alias(\"max_numberofoverdueinstls_closed\"),\n                          pl.max(\"outstandingamount_354A\").alias(\"max_outstandingamount_closed\"),\n                          pl.max(\"outstandingamount_362A\").alias(\"max_outstandingamount_active\"),\n                          pl.sum(\"overdueamount_31A\").alias(\"sum_overdueamount_closed\"),\n                          pl.sum(\"overdueamount_659A\").alias(\"sum_overdueamount_active\"),\n                          pl.max(\"overdueamountmax2_14A\").alias(\"max_overdueamountmax2_active\"),\n                          pl.max(\"overdueamountmax2_398A\").alias(\"max_overdueamountmax2_closed\"),\n                          pl.mean('prolongationcount_1120L').alias(\"mean_prolongationcount_closed\"),\n                          pl.mean(\"prolongationcount_599L\").alias(\"mean_prolongationcount_active\"),\n                          pl.sum(\"residualamount_488A\").alias(\"sum_residualamount_closed\"),\n                          pl.sum(\"residualamount_856A\").alias(\"sum_residualamount_active\"),\n                          pl.mean(\"totalamount_6A\").alias(\"mean_totalamount_closed\"),\n                          pl.mean(\"totalamount_996A\").alias(\"mean_totalamount_active\"),\n                          pl.sum(\"totaldebtoverduevalue_178A\").alias(\"sum_totaldebtoverduevalue_active\"),\n                          pl.sum(\"totaldebtoverduevalue_718A\").alias(\"sum_totaldebtoverduevalue_closed\"),\n                          pl.sum(\"totaloutstanddebtvalue_39A\").alias(\"sum_totaloutstanddebtvalue_active\"),\n                          pl.sum(\"totaloutstanddebtvalue_668A\").alias(\"sum_totaloutstanddebtvalue_closed\"))\ntest_cb.shape","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:55:58.057787Z","iopub.execute_input":"2024-04-19T14:55:58.058520Z","iopub.status.idle":"2024-04-19T14:55:58.088036Z","shell.execute_reply.started":"2024-04-19T14:55:58.058478Z","shell.execute_reply":"2024-04-19T14:55:58.086872Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data_submission = data_submission.join(test_cb,how=\"left\",on='case_id')\ndata_submission.shape","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:55:59.111827Z","iopub.execute_input":"2024-04-19T14:55:59.112199Z","iopub.status.idle":"2024-04-19T14:55:59.121054Z","shell.execute_reply.started":"2024-04-19T14:55:59.112170Z","shell.execute_reply":"2024-04-19T14:55:59.119935Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#data_submission.write_parquet(\"credit_risk_testdataset.parquet\")","metadata":{"execution":{"iopub.status.busy":"2024-04-15T08:44:56.968174Z","iopub.execute_input":"2024-04-15T08:44:56.968941Z","iopub.status.idle":"2024-04-15T08:44:56.978612Z","shell.execute_reply.started":"2024-04-15T08:44:56.968898Z","shell.execute_reply":"2024-04-15T08:44:56.976822Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# memory managment\ndata = reduce_mem_usage(data)\ntest_cb,test,cb,ts = [None]*4\nprint(gc.collect())\nprint(psutil.virtual_memory().available * 100 / psutil.virtual_memory().total)","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:56:03.791684Z","iopub.execute_input":"2024-04-19T14:56:03.792075Z","iopub.status.idle":"2024-04-19T14:56:06.390844Z","shell.execute_reply.started":"2024-04-19T14:56:03.792044Z","shell.execute_reply":"2024-04-19T14:56:06.389168Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data = handle_dates(data)\ndata = reduce_mem_usage(data)","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:56:11.021229Z","iopub.execute_input":"2024-04-19T14:56:11.021893Z","iopub.status.idle":"2024-04-19T14:56:15.619544Z","shell.execute_reply.started":"2024-04-19T14:56:11.021850Z","shell.execute_reply":"2024-04-19T14:56:15.615403Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"t = set()\nfor col in data.columns:\n    t.add(data[col].dtype)\nt","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:56:25.606970Z","iopub.execute_input":"2024-04-19T14:56:25.607358Z","iopub.status.idle":"2024-04-19T14:56:25.616198Z","shell.execute_reply.started":"2024-04-19T14:56:25.607316Z","shell.execute_reply":"2024-04-19T14:56:25.615058Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# model","metadata":{}},{"cell_type":"code","source":"def convert_strings(df: pl.DataFrame) -> pl.DataFrame:\n    #df = df.to_pandas()\n    for col in df.columns:  \n        if df[col].dtype.name in ['object', 'string']:\n            df[col] = df[col].astype(\"string\").astype('category')\n            current_categories = df[col].cat.categories\n            new_categories = current_categories.to_list() + [\"Unknown\"]\n            new_dtype = pd.CategoricalDtype(categories=new_categories, ordered=True)\n            df[col] = df[col].astype(new_dtype)\n    return df","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:56:32.000307Z","iopub.execute_input":"2024-04-19T14:56:32.000713Z","iopub.status.idle":"2024-04-19T14:56:32.007730Z","shell.execute_reply.started":"2024-04-19T14:56:32.000683Z","shell.execute_reply":"2024-04-19T14:56:32.006554Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"case_ids = data[\"case_id\"].unique().shuffle(seed=1)\nfrom sklearn.model_selection import train_test_split\ncase_ids_train, case_ids_test = train_test_split(case_ids, train_size=0.6, random_state=1)\ncase_ids_valid, case_ids_test = train_test_split(case_ids_test, train_size=0.5, random_state=1)\n\n\n\ndef from_polars_to_pandas(case_ids: pl.DataFrame) -> pl.DataFrame:\n    return (\n        data.filter(pl.col(\"case_id\").is_in(case_ids))[[\"case_id\", \"WEEK_NUM\", \"target\"]].to_pandas(),\n        data.filter(pl.col(\"case_id\").is_in(case_ids))[:,5:].to_pandas(),\n        data.filter(pl.col(\"case_id\").is_in(case_ids))[\"target\"].to_pandas()\n    )\n\nbase_train, X_train, y_train = from_polars_to_pandas(case_ids_train)\nbase_valid, X_valid, y_valid = from_polars_to_pandas(case_ids_valid)\nbase_test, X_test, y_test = from_polars_to_pandas(case_ids_test)\n\nfor df in [X_train, X_valid,X_test]:\n    df = convert_strings(df)","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:56:40.979317Z","iopub.execute_input":"2024-04-19T14:56:40.979998Z","iopub.status.idle":"2024-04-19T14:57:01.066479Z","shell.execute_reply.started":"2024-04-19T14:56:40.979965Z","shell.execute_reply":"2024-04-19T14:57:01.065390Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(gc.collect())\nprint(psutil.virtual_memory().available * 100 / psutil.virtual_memory().total)","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:57:01.067975Z","iopub.execute_input":"2024-04-19T14:57:01.068299Z","iopub.status.idle":"2024-04-19T14:57:01.173835Z","shell.execute_reply.started":"2024-04-19T14:57:01.068274Z","shell.execute_reply":"2024-04-19T14:57:01.172650Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import xgboost as xgb\n\nxgb_model = xgb.XGBClassifier(\n    objective='binary:logistic',\n    tree_method=\"hist\",\n    enable_categorical=True,\n    eval_metric='auc',\n    #gamma=0.7,\n    #reg_alpha=0.7,\n    max_depth=3,\n    n_estimators=1200,\n    random_state=42,\n)\n\n# Training the model on the training data\nxgb_model.fit(\n    X_train, y_train,\n    eval_set=[(X_valid, y_valid)],\n    early_stopping_rounds=100,\n    verbose=True,\n)","metadata":{"execution":{"iopub.status.busy":"2024-04-19T14:57:15.083432Z","iopub.execute_input":"2024-04-19T14:57:15.084190Z","iopub.status.idle":"2024-04-19T15:04:18.668703Z","shell.execute_reply.started":"2024-04-19T14:57:15.084155Z","shell.execute_reply":"2024-04-19T15:04:18.667422Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from sklearn.metrics import roc_auc_score \nfor base, X in [(base_train, X_train), (base_valid, X_valid), (base_test, X_test)]:\n    y_pred = xgb_model.predict(X)\n    base[\"score\"] = y_pred\n\nprint(f'The AUC score on the train set is: {roc_auc_score(base_train[\"target\"], base_train[\"score\"])}') \nprint(f'The AUC score on the valid set is: {roc_auc_score(base_valid[\"target\"], base_valid[\"score\"])}') \nprint(f'The AUC score on the test set is: {roc_auc_score(base_test[\"target\"], base_test[\"score\"])}') ","metadata":{"execution":{"iopub.status.busy":"2024-04-19T15:04:18.670909Z","iopub.execute_input":"2024-04-19T15:04:18.671401Z","iopub.status.idle":"2024-04-19T15:04:22.132432Z","shell.execute_reply.started":"2024-04-19T15:04:18.671361Z","shell.execute_reply":"2024-04-19T15:04:22.131108Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def gini_stability(base, w_fallingrate=88.0, w_resstd=-0.5):\n    gini_in_time = base.loc[:, [\"WEEK_NUM\", \"target\", \"score\"]]\\\n        .sort_values(\"WEEK_NUM\")\\\n        .groupby(\"WEEK_NUM\")[[\"target\", \"score\"]]\\\n        .apply(lambda x: 2*roc_auc_score(x[\"target\"], x[\"score\"])-1).tolist()\n    \n    x = np.arange(len(gini_in_time))\n    y = gini_in_time\n    a, b = np.polyfit(x, y, 1)\n    y_hat = a*x + b\n    residuals = y - y_hat\n    res_std = np.std(residuals)\n    avg_gini = np.mean(gini_in_time)\n    return avg_gini + w_fallingrate * min(0, a) + w_resstd * res_std\n\nstability_score_train = gini_stability(base_train)\nstability_score_valid = gini_stability(base_valid)\nstability_score_test = gini_stability(base_test)\n\nprint(f'The stability score on the train set is: {stability_score_train}') \nprint(f'The stability score on the valid set is: {stability_score_valid}') \nprint(f'The stability score on the test set is: {stability_score_test}') ","metadata":{"execution":{"iopub.status.busy":"2024-04-19T15:04:22.134118Z","iopub.execute_input":"2024-04-19T15:04:22.134881Z","iopub.status.idle":"2024-04-19T15:04:23.019551Z","shell.execute_reply.started":"2024-04-19T15:04:22.134840Z","shell.execute_reply":"2024-04-19T15:04:23.018530Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Submission","metadata":{}},{"cell_type":"code","source":"data_submission = data_submission.drop(\"target\")","metadata":{"execution":{"iopub.status.busy":"2024-04-19T15:09:52.300473Z","iopub.execute_input":"2024-04-19T15:09:52.300861Z","iopub.status.idle":"2024-04-19T15:09:52.306381Z","shell.execute_reply.started":"2024-04-19T15:09:52.300826Z","shell.execute_reply":"2024-04-19T15:09:52.305242Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data_submission = handle_dates(data_submission)","metadata":{"execution":{"iopub.status.busy":"2024-04-19T15:11:20.819497Z","iopub.execute_input":"2024-04-19T15:11:20.819878Z","iopub.status.idle":"2024-04-19T15:11:20.834115Z","shell.execute_reply.started":"2024-04-19T15:11:20.819850Z","shell.execute_reply":"2024-04-19T15:11:20.833046Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"t = set()\nfor col in data_submission.columns:\n    t.add(data_submission[col].dtype)\nt","metadata":{"execution":{"iopub.status.busy":"2024-04-19T15:11:23.240802Z","iopub.execute_input":"2024-04-19T15:11:23.241487Z","iopub.status.idle":"2024-04-19T15:11:23.250588Z","shell.execute_reply.started":"2024-04-19T15:11:23.241453Z","shell.execute_reply":"2024-04-19T15:11:23.249253Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_train.shape,data_submission.shape","metadata":{"execution":{"iopub.status.busy":"2024-04-19T15:12:16.366022Z","iopub.execute_input":"2024-04-19T15:12:16.366476Z","iopub.status.idle":"2024-04-19T15:12:16.374435Z","shell.execute_reply.started":"2024-04-19T15:12:16.366445Z","shell.execute_reply":"2024-04-19T15:12:16.373253Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data_submission[:,4:].head()","metadata":{"execution":{"iopub.status.busy":"2024-04-19T15:14:16.983488Z","iopub.execute_input":"2024-04-19T15:14:16.983853Z","iopub.status.idle":"2024-04-19T15:14:16.999054Z","shell.execute_reply.started":"2024-04-19T15:14:16.983827Z","shell.execute_reply":"2024-04-19T15:14:16.997875Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"y_submission_pred  = xgb_model.predict(convert_strings(data_submission[:,4:].to_pandas()))","metadata":{"execution":{"iopub.status.busy":"2024-04-19T15:14:22.434942Z","iopub.execute_input":"2024-04-19T15:14:22.435318Z","iopub.status.idle":"2024-04-19T15:14:22.605585Z","shell.execute_reply.started":"2024-04-19T15:14:22.435290Z","shell.execute_reply":"2024-04-19T15:14:22.602118Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"submission = pd.DataFrame({\n    \"case_id\": data_submission[\"case_id\"].to_numpy(),\n    \"score\": y_submission_pred\n}).set_index('case_id')\nsubmission.to_csv(\"./submission.csv\")","metadata":{"execution":{"iopub.status.busy":"2024-04-19T15:14:24.822364Z","iopub.execute_input":"2024-04-19T15:14:24.822754Z","iopub.status.idle":"2024-04-19T15:14:24.835779Z","shell.execute_reply.started":"2024-04-19T15:14:24.822725Z","shell.execute_reply":"2024-04-19T15:14:24.834556Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}