{"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":false,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        pass\n        # print(os.path.join(dirname, filename))\n\n# You can write up to 20GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2024-05-28T00:43:53.381409Z","iopub.execute_input":"2024-05-28T00:43:53.382112Z","iopub.status.idle":"2024-05-28T00:43:54.477322Z","shell.execute_reply.started":"2024-05-28T00:43:53.382077Z","shell.execute_reply":"2024-05-28T00:43:54.475654Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Cell to define imports\nimport numpy as np\nimport polars as pl\nimport pandas as pd\nimport matplotlib.pyplot as plt\nimport os\nimport gc\nfrom sklearn.preprocessing import LabelEncoder\nfrom sklearn.preprocessing import OneHotEncoder\n%matplotlib inline","metadata":{"execution":{"iopub.status.busy":"2024-05-28T00:43:58.828571Z","iopub.execute_input":"2024-05-28T00:43:58.829827Z","iopub.status.idle":"2024-05-28T00:43:59.692647Z","shell.execute_reply.started":"2024-05-28T00:43:58.829787Z","shell.execute_reply":"2024-05-28T00:43:59.691474Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Read all required Parquets here\nroot :str = \"/kaggle/input/home-credit-credit-risk-model-stability/\"\ndatapath :str = os.path.join(root, \"parquet_files/train/\")","metadata":{"execution":{"iopub.status.busy":"2024-05-28T00:43:59.694298Z","iopub.execute_input":"2024-05-28T00:43:59.694632Z","iopub.status.idle":"2024-05-28T00:43:59.701699Z","shell.execute_reply.started":"2024-05-28T00:43:59.694605Z","shell.execute_reply":"2024-05-28T00:43:59.700743Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"feature_definitions :pl.DataFrame = pl.read_csv(os.path.join(root, \"feature_definitions.csv\"))","metadata":{"execution":{"iopub.status.busy":"2024-05-28T00:44:00.813230Z","iopub.execute_input":"2024-05-28T00:44:00.813645Z","iopub.status.idle":"2024-05-28T00:44:00.973432Z","shell.execute_reply.started":"2024-05-28T00:44:00.813617Z","shell.execute_reply":"2024-05-28T00:44:00.972218Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"### Class for looking up the definition of a single attribute / or all the attributes present in a file (If defn exists for that attribute). \nclass defnLookup:\n    def __init__(self):\n        self.feature_definitions :pl.DataFrame = pl.read_csv(os.path.join(root, \"feature_definitions.csv\"))\n    \n    # Attribute Meaning lookup in feature definition csv\n    def lookupSingle(self, feature_name, definitions = feature_definitions) -> str:\n        defn = definitions.filter(pl.col(\"Variable\") == feature_name)\n        defn = defn.select(pl.col(\"Description\").alias(\"Descr\"))\n        return defn.item()\n    \n    def lookupFile(self, file: pl.DataFrame):\n        attributes_in_train_1 = file.columns\n        availableDefn :list = self.feature_definitions[\"Variable\"].to_list()\n        #attributes_in_train_1[0]\n        for cols in attributes_in_train_1:\n            if cols in availableDefn:\n                print(\"{0} --- {1}\".format(cols, self.lookupSingle(cols, self.feature_definitions)))\n\n          \nlookup = defnLookup()","metadata":{"execution":{"iopub.status.busy":"2024-05-28T00:44:01.256850Z","iopub.execute_input":"2024-05-28T00:44:01.257236Z","iopub.status.idle":"2024-05-28T00:44:01.267728Z","shell.execute_reply.started":"2024-05-28T00:44:01.257207Z","shell.execute_reply":"2024-05-28T00:44:01.266735Z"},"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.Float32).alias(col))\n        if col[-1] in (\"M\"):\n            if df[[col]].dtypes == pl.Int64 or df[[col]].dtypes == pl.Int32:\n                df = df.with_columns(pl.col(col).cast(pl.Int16).alias(col))\n\n    return df\n\ndef filterAMP(df: pl.DataFrame) -> pl.DataFrame:\n    selected_static_cols = [\"case_id\"]\n    for col in df.columns:\n        if col[-1] in (\"A\", \"M\", \"P\"):\n            selected_static_cols.append(col)\n    df = df[selected_static_cols]\n    \n    return df\n\ndef selectCategoricalColumns(df) -> pl.DataFrame:\n    selected_static_cols = [\"case_id\"]\n    for col in df.columns:\n        if col[-1] in (\"M\"):\n            selected_static_cols.append(col)\n    df = df[selected_static_cols]\n    return df\n\ndef categoricalEncoding(df) -> pl.DataFrame:\n    df_temp = df.pipe(selectCategoricalColumns)\n    for x in df_temp.columns:\n        if not x == \"case_id\":\n            enc = LabelEncoder()\n            tp = enc.fit_transform(df_temp[x])\n            df_temp = df_temp.with_columns(pl.Series(name = x, values=tp))\n            le_name_mapping = dict(zip(enc.classes_, enc.transform(enc.classes_)))\n            # print(le_name_mapping.keys)\n            if \"a55475b1\" in le_name_mapping.keys():\n                df_temp = df_temp.with_columns(\n                    pl.when(\n                        pl.col(x) != le_name_mapping[\"a55475b1\"]\n                    ).then( pl.col(x) )\n                ) \n\n    for x in df_temp.columns:\n        if not x == \"case_id\":\n            df = df.with_columns(pl.Series(name=x, values=df_temp[x]))\n            \n    return df\n\n","metadata":{"execution":{"iopub.status.busy":"2024-05-28T01:13:36.503070Z","iopub.execute_input":"2024-05-28T01:13:36.503516Z","iopub.status.idle":"2024-05-28T01:13:36.519274Z","shell.execute_reply.started":"2024-05-28T01:13:36.503485Z","shell.execute_reply":"2024-05-28T01:13:36.517899Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Flow -> Select 3 or 4 files, of all depths and extract features from it for preprocessing. This can be generalized later.\n# Work with 1 file at a time\n# Currently working with train_person_1\ntrain_base = pl.read_parquet(os.path.join(root, datapath) + \"train_base.parquet\").pipe(set_table_dtypes)\n\n# Depth 1\ntrain_person_1 = pl.read_parquet(os.path.join(root, datapath) + \"train_person_1.parquet\").pipe(set_table_dtypes).pipe(filterAMP).pipe(categoricalEncoding).pipe(set_table_dtypes) \n# Depth 2\ntrain_person_2 = pl.read_parquet(os.path.join(root, datapath) + \"train_person_2.parquet\").pipe(set_table_dtypes).pipe(filterAMP).pipe(categoricalEncoding).pipe(set_table_dtypes)\n\n# Depth 1\ntrain_applprev_1 = pl.concat(\n        [\n           pl.read_parquet(os.path.join(root, datapath) + \"train_applprev_1_0.parquet\").pipe(set_table_dtypes).pipe(filterAMP),\n           pl.read_parquet(os.path.join(root, datapath) + \"train_applprev_1_1.parquet\").pipe(set_table_dtypes).pipe(filterAMP),\n        ],\n        how = \"vertical_relaxed\"\n).pipe(categoricalEncoding).pipe(set_table_dtypes)\n#Depth 2\n# train_applprev_2 = pl.read_parquet(os.path.join(root, datapath) + \"train_applprev_2.parquet\").pipe(set_table_dtypes).pipe(filterAMP).pipe(categoricalEncoding)\n\n# Depth 0\ntrain_static = pl.concat( \n        [\n            pl.read_parquet(os.path.join(root, datapath) + \"train_static_0_0.parquet\").pipe(set_table_dtypes),\n            pl.read_parquet(os.path.join(root, datapath) + \"train_static_0_1.parquet\").pipe(set_table_dtypes),\n        ],\n        how=\"vertical_relaxed\",\n).pipe(filterAMP).pipe(categoricalEncoding).pipe(set_table_dtypes)\n","metadata":{"execution":{"iopub.status.busy":"2024-05-28T00:44:03.895903Z","iopub.execute_input":"2024-05-28T00:44:03.896347Z","iopub.status.idle":"2024-05-28T00:45:37.724265Z","shell.execute_reply.started":"2024-05-28T00:44:03.896284Z","shell.execute_reply":"2024-05-28T00:45:37.723281Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Cell for train_tax_registries, Depth 1, sum on A \ntrain_tax_registry_a = pl.read_parquet(os.path.join(root, datapath) + \"train_tax_registry_a_1.parquet\").pipe(set_table_dtypes)\ntrain_tax_registry_b = pl.read_parquet(os.path.join(root, datapath) + \"train_tax_registry_b_1.parquet\").pipe(set_table_dtypes)\ntrain_tax_registry_c = pl.read_parquet(os.path.join(root, datapath) + \"train_tax_registry_c_1.parquet\").pipe(set_table_dtypes)\ntrain_tax = train_base[[\"case_id\"]].join(\n    train_tax_registry_a.group_by(\"case_id\").agg(pl.col(\"amount_4527230A\").sum()), on=\"case_id\", how=\"left\"\n).join(\n    train_tax_registry_b.group_by(\"case_id\").agg(pl.col(\"amount_4917619A\").sum()), on=\"case_id\", how=\"left\"\n).join(\n    train_tax_registry_c.group_by(\"case_id\").agg(pl.col(\"pmtamount_36A\").sum()), on=\"case_id\", how=\"left\",\n)\n","metadata":{"execution":{"iopub.status.busy":"2024-05-28T00:45:37.725928Z","iopub.execute_input":"2024-05-28T00:45:37.726438Z","iopub.status.idle":"2024-05-28T00:45:39.159081Z","shell.execute_reply.started":"2024-05-28T00:45:37.726411Z","shell.execute_reply":"2024-05-28T00:45:39.158083Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_c_1 = pl.read_parquet(os.path.join(root, datapath) + \"train_credit_bureau_a_1_0.parquet\").pipe(set_table_dtypes).pipe(filterAMP).pipe(categoricalEncoding).pipe(set_table_dtypes)\ntrain_c_2 = pl.read_parquet(os.path.join(root, datapath) + \"train_credit_bureau_a_1_1.parquet\").pipe(set_table_dtypes).pipe(filterAMP).pipe(categoricalEncoding).pipe(set_table_dtypes)\ntrain_c_3 = pl.read_parquet(os.path.join(root, datapath) + \"train_credit_bureau_a_1_2.parquet\").pipe(set_table_dtypes).pipe(filterAMP).pipe(categoricalEncoding).pipe(set_table_dtypes)\ntrain_c_4 = pl.read_parquet(os.path.join(root, datapath) + \"train_credit_bureau_a_1_3.parquet\").pipe(set_table_dtypes).pipe(filterAMP).pipe(categoricalEncoding).pipe(set_table_dtypes)\n\n\ntrain_credit_bureau = pl.concat(\n    [\n        train_c_1, train_c_2, train_c_3, train_c_4\n    ],\n    how=\"vertical_relaxed\"\n).pipe(set_table_dtypes)\n\n","metadata":{"execution":{"iopub.status.busy":"2024-05-28T00:45:39.160256Z","iopub.execute_input":"2024-05-28T00:45:39.160630Z","iopub.status.idle":"2024-05-28T00:48:33.989322Z","shell.execute_reply.started":"2024-05-28T00:45:39.160602Z","shell.execute_reply":"2024-05-28T00:48:33.988355Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Filter on num_group==0 and sum by case_id, drop null category columns\n# This Function processes depth 2 credit bureau a.\n\ndef process_tcb_a_2(df : pl.DataFrame, base: pl.DataFrame) -> pl.DataFrame:\n\n    # tcb2_final = df.filter(\n    #     pl.col(\"num_group2\") == 0\n    # ).pipe(selectCategoricalColumns).pipe(categoricalEncoding)\n    \n    '''\n    catProcess = pl.concat(\n        [ catProcess, df[[\"num_group1\", \"num_group2\"]] ], how=\"horizontal\"\n    )\n    catProcess = catProcess.filter(pl.col(\"num_group2\") == 0).drop(\"num_group2\")\n    categoricalColumns = catProcess.columns\n    tcb2_final = base[[\"case_id\"]] # Final to Return\n    \n    for col in categoricalColumns:\n        \n        if not col == \"case_id\" and not col == \"num_group1\":\n            ohe = OneHotEncoder(sparse_output=False)\n            tp = ohe.fit_transform(catProcess[[col]])\n            b = pl.DataFrame(tp).drop(\"column_3\").select(\n                pl.col(\"column_0\").alias(col + \"_\" + \"0\"),\n                pl.col(\"column_1\").alias(col + \"_\" + \"1\"),\n                pl.col(\"column_2\").alias(col + \"_\" + \"2\")\n            )\n            print(ohe.categories_)\n            temp = pl.concat(\n                [b, catProcess], how=\"horizontal\"\n            ).drop(\"num_group1\").select(\n                \"case_id\", col + \"_0\", col + \"_1\", col + \"_2\"\n            ).group_by(\n                \"case_id\"\n            ).agg(\n                pl.col(\n                    col + \"_0\"\n                ).sum(),\n                pl.col(\n                    col + \"_1\"\n                ).sum(),\n                pl.col(\n                    col + \"_2\"\n                ).sum()\n\n            )\n            \n            tcb2_final = tcb2_final.join(\n                temp, on = \"case_id\", how=\"left\"\n            )\n            \n    '''\n    \n    CVG_1 = df.select(['case_id', 'num_group1', 'num_group2', 'collater_valueofguarantee_1124L']).filter(\n        (pl.col('num_group1') == 0) & (pl.col('num_group2') == 0)\n    ).select(['case_id', 'collater_valueofguarantee_1124L'])\n\n    CVG_2 = df.select(['case_id', 'num_group1', 'num_group2', 'collater_valueofguarantee_876L']).filter(\n        (pl.col('num_group1') == 0) & (pl.col('num_group2') == 0)\n    ).select(['case_id', 'collater_valueofguarantee_876L'])\n\n    feats = df.group_by(\"case_id\").agg(\n        pl.col(\"pmts_overdue_1140A\").sum(),\n        pl.col(\"pmts_overdue_1152A\").sum(),\n        pl.col(\"pmts_dpd_1073P\").sum(),\n        pl.col(\"pmts_dpd_303P\").sum(),\n    )\n\n    feats_prev = CVG_1.join(\n        CVG_2, on=\"case_id\", how=\"left\",\n    ).join(\n        feats, on=\"case_id\", how=\"left\"\n    )\n    \n    '''\n    tcb2_final = tcb2_final.join(\n        feats_prev, on=\"case_id\", how=\"left\"\n    )\n    \n    tcb2_final = tcb2_final.group_by(\n        \"case_id\"\n    ).agg(\n        pl.col(\"collater_typofvalofguarant_298M\").max().cast(pl.UInt16),\n        pl.col(\"collater_typofvalofguarant_407M\").max().cast(pl.UInt16),\n        pl.col(\"collaterals_typeofguarante_359M\").max().cast(pl.UInt16),\n        pl.col(\"collaterals_typeofguarante_669M\").max().cast(pl.UInt16),\n        pl.col(\"subjectroles_name_541M\").max().cast(pl.UInt16),\n        pl.col(\"subjectroles_name_838M\").max().cast(pl.UInt16),\n        pl.col(\"pmts_overdue_1152A\").sum(),\n        pl.col(\"pmts_overdue_1140A\").sum(),\n        pl.col(\"pmts_dpd_1073P\").sum(),\n        pl.col(\"pmts_dpd_303P\").sum(),\n        pl.col(\"collater_valueofguarantee_1124L\").sum(),\n        pl.col(\"collater_valueofguarantee_876L\").sum(),\n    )\n    '''\n    \n    tcb2_final = base[[\"case_id\"]].join(\n        feats_prev, on=\"case_id\", how=\"left\"\n    )\n    \n    return tcb2_final","metadata":{"execution":{"iopub.status.busy":"2024-05-28T00:48:33.994252Z","iopub.execute_input":"2024-05-28T00:48:33.995653Z","iopub.status.idle":"2024-05-28T00:48:34.009801Z","shell.execute_reply.started":"2024-05-28T00:48:33.995611Z","shell.execute_reply":"2024-05-28T00:48:34.008586Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def aggregateOnColumnWithMemory(df1: pl.DataFrame, df2: pl.DataFrame) -> pl.DataFrame:\n    df = pl.concat(\n        [df1, df2], how=\"vertical_relaxed\"\n    )\n    cols = df.columns\n    \n    return df.group_by(\"case_id\").agg(\n        pl.exclude(\"case_id\").sum()\n    )\n            \n            ","metadata":{"execution":{"iopub.status.busy":"2024-05-28T00:48:34.012043Z","iopub.execute_input":"2024-05-28T00:48:34.012415Z","iopub.status.idle":"2024-05-28T00:48:34.023408Z","shell.execute_reply.started":"2024-05-28T00:48:34.012386Z","shell.execute_reply":"2024-05-28T00:48:34.022602Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc.collect()","metadata":{"execution":{"iopub.status.busy":"2024-05-28T00:48:34.024748Z","iopub.execute_input":"2024-05-28T00:48:34.025615Z","iopub.status.idle":"2024-05-28T00:48:34.121901Z","shell.execute_reply.started":"2024-05-28T00:48:34.025577Z","shell.execute_reply":"2024-05-28T00:48:34.120708Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_c_1 = pl.read_parquet(os.path.join(root, datapath) + \"train_credit_bureau_a_2_0.parquet\").pipe(process_tcb_a_2, train_base)\ntrain_c_2 = pl.read_parquet(os.path.join(root, datapath) + \"train_credit_bureau_a_2_1.parquet\").pipe(process_tcb_a_2, train_base)\n\ntrain_final = aggregateOnColumnWithMemory(train_c_1, train_c_2)\n\ntrain_c_3 = pl.read_parquet(os.path.join(root, datapath) + \"train_credit_bureau_a_2_2.parquet\").pipe(process_tcb_a_2, train_base)\ntrain_c_4 = pl.read_parquet(os.path.join(root, datapath) + \"train_credit_bureau_a_2_3.parquet\").pipe(process_tcb_a_2, train_base)\n\ntrain_final = aggregateOnColumnWithMemory(train_c_3, train_c_4).pipe(aggregateOnColumnWithMemory, train_final)\n\ndel train_c_1\ndel train_c_2\ndel train_c_3\ndel train_c_4\n\ngc.collect()\n\ntrain_c_5 = pl.read_parquet(os.path.join(root, datapath) + \"train_credit_bureau_a_2_4.parquet\").pipe(process_tcb_a_2, train_base)\ntrain_c_6 = pl.read_parquet(os.path.join(root, datapath) + \"train_credit_bureau_a_2_5.parquet\").pipe(process_tcb_a_2, train_base)\n\ntrain_final = aggregateOnColumnWithMemory(train_c_5, train_c_6).pipe(aggregateOnColumnWithMemory, train_final)\n\ntrain_c_7 = pl.read_parquet(os.path.join(root, datapath) + \"train_credit_bureau_a_2_6.parquet\").pipe(process_tcb_a_2, train_base)\ntrain_c_8 = pl.read_parquet(os.path.join(root, datapath) + \"train_credit_bureau_a_2_7.parquet\").pipe(process_tcb_a_2, train_base)\n\ntrain_final = aggregateOnColumnWithMemory(train_c_7, train_c_8).pipe(aggregateOnColumnWithMemory, train_final)\n\ndel train_c_5\ndel train_c_6\ndel train_c_7\ndel train_c_8\n\ngc.collect()\n\ntrain_c_9 = pl.read_parquet(os.path.join(root, datapath) + \"train_credit_bureau_a_2_8.parquet\").pipe(process_tcb_a_2, train_base)\ntrain_c_10 = pl.read_parquet(os.path.join(root, datapath) + \"train_credit_bureau_a_2_9.parquet\").pipe(process_tcb_a_2, train_base)\n\ntrain_final = aggregateOnColumnWithMemory(train_c_9, train_c_10).pipe(aggregateOnColumnWithMemory, train_final)\n\ntrain_c_11 = pl.read_parquet(os.path.join(root, datapath) + \"train_credit_bureau_a_2_10.parquet\").pipe(process_tcb_a_2, train_base)\n\ntrain_final = aggregateOnColumnWithMemory(train_c_11, train_final)\n\n\ndel train_c_9\ndel train_c_10\ndel train_c_11\n\ngc.collect()\n\ntrain_tcb2 = train_final\ndel train_final\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2024-05-28T00:48:34.125470Z","iopub.execute_input":"2024-05-28T00:48:34.126137Z","iopub.status.idle":"2024-05-28T00:49:35.722771Z","shell.execute_reply.started":"2024-05-28T00:48:34.126108Z","shell.execute_reply":"2024-05-28T00:49:35.721597Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"rejected = train_base[[\"target\"]].filter(pl.col(\"target\") == 0).count().item()\napproved = train_base[[\"target\"]].filter(pl.col(\"target\") == 1).count().item()\nassert((approved+rejected) >= 0)\napproval_rate = approved / (approved+rejected)\nprint(\"Approval Rate: {0}\".format(approval_rate*100))","metadata":{"execution":{"iopub.status.busy":"2024-05-28T00:49:35.724406Z","iopub.execute_input":"2024-05-28T00:49:35.724764Z","iopub.status.idle":"2024-05-28T00:49:35.747885Z","shell.execute_reply.started":"2024-05-28T00:49:35.724734Z","shell.execute_reply":"2024-05-28T00:49:35.747070Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def grouping(df: pl.DataFrame, train_base : pl.DataFrame) -> pl.DataFrame:\n    to_merge = train_base[[\"case_id\"]]\n    for col in df.columns:\n        if col[-1] in (\"A\"):\n            to_merge = to_merge.join(\n                df.group_by(\"case_id\").agg(pl.col(col).sum()), on=\"case_id\", how=\"left\"\n            )\n        elif col[-1] in (\"M\"):\n            to_merge = to_merge.join(\n                df.group_by(\"case_id\").agg(pl.col(col).mean()), on=\"case_id\", how=\"left\"\n            )\n        elif col[-1] in (\"P\"): \n            to_merge = to_merge.join(\n                df.group_by(\"case_id\").agg(pl.col(col).max()), on=\"case_id\", how=\"left\"\n            )\n            \n    return to_merge","metadata":{"execution":{"iopub.status.busy":"2024-05-28T00:49:35.749219Z","iopub.execute_input":"2024-05-28T00:49:35.749725Z","iopub.status.idle":"2024-05-28T00:49:35.758952Z","shell.execute_reply.started":"2024-05-28T00:49:35.749693Z","shell.execute_reply":"2024-05-28T00:49:35.757864Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from sklearn.model_selection import RandomizedSearchCV\nfrom sklearn.metrics import roc_auc_score \nimport lightgbm as GBM\nfrom sklearn.model_selection import train_test_split\nfrom typing import Tuple\n\n\ndef 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\n\ndef WeakTrain(df: pl.DataFrame, base: pl.DataFrame):\n    treeOne = base.join(\n        df, on=\"case_id\", how=\"left\"\n    )\n    \n    treeOne = treeOne.to_pandas()\n    dropList = treeOne.filter([\"case_id\", \"target\", \"date_decision\", \"MONTH\", \"WEEK_NUM\"]) # Filters out non-existent columns\n    X = treeOne.drop(columns = dropList) # only Existing Columns in the DataFrame are dropped\n    Y = treeOne[\"target\"]\n    x_train, x_test, y_train, y_test = train_test_split(X, Y, train_size=0.6, random_state=2)\n    \n    lgb_train = GBM.Dataset(x_train, label=y_train)\n    lgb_valid = GBM.Dataset(x_test, label=y_test, reference=lgb_train)\n\n    params = {\n        \"boosting_type\": \"gbdt\",\n        \"objective\": \"binary\",\n        \"metric\": \"auc\",\n        \"max_depth\": 3,\n        \"num_leaves\": 31,\n        \"learning_rate\": 0.05,\n        \"feature_fraction\": 0.9,\n        \"bagging_fraction\": 0.8,\n        \"bagging_freq\": 5,\n        \"n_estimators\": 1000,\n        \"verbose\": -1,\n    }\n\n    # gbm = GBM.LGBMClassifier()\n\n    # grid = RandomizedSearchCV(gbm, params,verbose=1,cv=10, n_jobs = -1, n_iter=10)\n    # grid.fit(x_train,y_train, callbacks=[GBM.log_evaluation(50)])\n\n\n    gbm = GBM.train(\n        params,\n        lgb_train,\n        valid_sets=lgb_valid,\n        callbacks=[GBM.log_evaluation(50), GBM.early_stopping(10)]\n    )\n    \n    giniStability: list = []\n    for i in range(0, 10):\n        stabilityDF = treeOne.sample(frac=0.5)\n        X = stabilityDF.drop(columns = dropList)\n        y_pred = gbm.predict(X, num_iteration=gbm.best_iteration)\n        stabilityDF[\"score\"] = y_pred\n        giniStability.append(gini_stability(stabilityDF))\n        \n    print(\"STABILITIES: {0} \\n AVERAGE: {1}\".format(giniStability, np.mean(giniStability)))\n    \n    GBM.plot_importance(gbm, max_num_features=10)\n    return gbm, gbm.feature_name(), gbm.feature_importance()\n\n\n\ndef selectAndReturnDFWithTopK(top_k_train_static: np.array, features: list, k: int, finalDF: pl.DataFrame, df: pl.DataFrame) -> pl.DataFrame:\n    topKFeatures = np.argsort(top_k_train_static)[::-1][:k]\n    colNames  = [features[x] for x in topKFeatures]\n    colNames.append(\"case_id\")\n    print(colNames)\n    return finalDF.join(\n        df[colNames], on=\"case_id\", how=\"left\"\n    )\n\n\ndef PrepareFinalDF(base: pl.DataFrame) -> pl.DataFrame:\n    return base[[\"case_id\", \"target\"]]","metadata":{"execution":{"iopub.status.busy":"2024-05-28T00:49:35.762073Z","iopub.execute_input":"2024-05-28T00:49:35.762398Z","iopub.status.idle":"2024-05-28T00:49:36.854780Z","shell.execute_reply.started":"2024-05-28T00:49:35.762371Z","shell.execute_reply":"2024-05-28T00:49:36.853921Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"topK: int = 10 # Top-K \nfinalDF = PrepareFinalDF(train_base)\n\n#1. Train for train_static\nmodel, features, top_k_train = WeakTrain(train_static, train_base)\nfinalDF = selectAndReturnDFWithTopK(top_k_train, features, topK, finalDF, train_static) # Select Top-K and join with finalDF\ndel train_static\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2024-05-28T00:49:36.856085Z","iopub.execute_input":"2024-05-28T00:49:36.857537Z","iopub.status.idle":"2024-05-28T00:53:07.247715Z","shell.execute_reply.started":"2024-05-28T00:49:36.857496Z","shell.execute_reply":"2024-05-28T00:53:07.246728Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#2. Train for train_tcb2\nmodel, features, top_k_train = WeakTrain(train_tcb2, train_base)\nfinalDF = selectAndReturnDFWithTopK(top_k_train, features, topK, finalDF, train_tcb2) # Select Top-K and join with finalDF\ndel train_tcb2\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2024-05-28T00:53:26.098831Z","iopub.execute_input":"2024-05-28T00:53:26.099274Z","iopub.status.idle":"2024-05-28T00:53:58.456009Z","shell.execute_reply.started":"2024-05-28T00:53:26.099239Z","shell.execute_reply":"2024-05-28T00:53:58.454952Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#3. Train for train_applprev_1 (depth 1, pipe into grouping())\ntrain_applprev_1_train = train_applprev_1.pipe(grouping, train_base)\nmodel, features, top_k_train = WeakTrain(train_applprev_1_train, train_base)\nfinalDF = selectAndReturnDFWithTopK(top_k_train, features, topK, finalDF, train_applprev_1_train) # Select Top-K and join with finalDF\ndel train_applprev_1\ndel train_applprev_1_train\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2024-05-28T00:53:58.458074Z","iopub.execute_input":"2024-05-28T00:53:58.458408Z","iopub.status.idle":"2024-05-28T00:55:43.797010Z","shell.execute_reply.started":"2024-05-28T00:53:58.458381Z","shell.execute_reply":"2024-05-28T00:55:43.795812Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#4. Train for train_credit_bureau_1 (depth 1, pipe into grouping())\ntrain_credit_bureau = train_credit_bureau.pipe(grouping, train_base)\nmodel, features, top_k_train = WeakTrain(train_credit_bureau, train_base)\nfinalDF = selectAndReturnDFWithTopK(top_k_train, features, topK, finalDF, train_credit_bureau)\ndel train_credit_bureau\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2024-05-28T00:55:43.798477Z","iopub.execute_input":"2024-05-28T00:55:43.798859Z","iopub.status.idle":"2024-05-28T00:59:18.807344Z","shell.execute_reply.started":"2024-05-28T00:55:43.798829Z","shell.execute_reply":"2024-05-28T00:59:18.805915Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#5. Train train_person_1 (depth 1, pipe into grouping())\ntrain_person_1_train = train_person_1.pipe(grouping, train_base)\nmodel, features, top_k_train = WeakTrain(train_person_1_train, train_base)\nfinalDF = selectAndReturnDFWithTopK(top_k_train, features, topK, finalDF, train_person_1_train) # Select Top-K and join with finalDF\ndel train_person_1_train\ndel train_person_1\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2024-05-28T00:59:18.811871Z","iopub.execute_input":"2024-05-28T00:59:18.812261Z","iopub.status.idle":"2024-05-28T01:00:41.019126Z","shell.execute_reply.started":"2024-05-28T00:59:18.812229Z","shell.execute_reply":"2024-05-28T01:00:41.017911Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#6. Train train_tax (depth 1, no need for grouping, pass to weakTrain)\nmodel, features, top_k_train = WeakTrain(train_tax, train_base)\nfinalDF = selectAndReturnDFWithTopK(top_k_train, features, topK, finalDF, train_tax) # Select Top-K and join with finalDF\ndel train_tax\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2024-05-28T01:00:41.020617Z","iopub.execute_input":"2024-05-28T01:00:41.021050Z","iopub.status.idle":"2024-05-28T01:00:54.961547Z","shell.execute_reply.started":"2024-05-28T01:00:41.021013Z","shell.execute_reply":"2024-05-28T01:00:54.960208Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"finalDF = finalDF.drop([\"target\"])\nfinal_model, features, top_k_train =  WeakTrain(finalDF, train_base[[\"case_id\", \"WEEK_NUM\", \"target\"]]) # Strong Learner.","metadata":{"execution":{"iopub.status.busy":"2024-05-28T01:01:16.335972Z","iopub.execute_input":"2024-05-28T01:01:16.336466Z","iopub.status.idle":"2024-05-28T01:06:13.452639Z","shell.execute_reply.started":"2024-05-28T01:01:16.336429Z","shell.execute_reply":"2024-05-28T01:06:13.451415Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"selected_static_cols = features","metadata":{"execution":{"iopub.status.busy":"2024-05-28T01:06:38.777027Z","iopub.execute_input":"2024-05-28T01:06:38.777488Z","iopub.status.idle":"2024-05-28T01:06:38.785641Z","shell.execute_reply.started":"2024-05-28T01:06:38.777452Z","shell.execute_reply":"2024-05-28T01:06:38.784559Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# FINAL_MODEL_NAME\n\n## final_model","metadata":{"execution":{"iopub.status.busy":"2024-05-28T01:06:40.698769Z","iopub.execute_input":"2024-05-28T01:06:40.699199Z","iopub.status.idle":"2024-05-28T01:06:40.703818Z","shell.execute_reply.started":"2024-05-28T01:06:40.699167Z","shell.execute_reply":"2024-05-28T01:06:40.702553Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# ***LOAD TESTING DATA*** # ","metadata":{}},{"cell_type":"code","source":"TestDatapath :str = os.path.join(root, \"parquet_files/test/\")\ntest_base = pl.read_parquet(os.path.join(root, TestDatapath) + \"test_base.parquet\").pipe(set_table_dtypes)\n\n# Depth 1\ntest_person_1 = pl.read_parquet(os.path.join(root, TestDatapath) + \"test_person_1.parquet\").pipe(set_table_dtypes).pipe(filterAMP).pipe(categoricalEncoding) \n# Depth 2\ntest_person_2 = pl.read_parquet(os.path.join(root, TestDatapath) + \"test_person_2.parquet\").pipe(set_table_dtypes).pipe(filterAMP).pipe(categoricalEncoding)\n\n# Depth 1\ntest_applprev_1 = pl.concat(\n        [\n           pl.read_parquet(os.path.join(root, TestDatapath) + \"test_applprev_1_0.parquet\").pipe(set_table_dtypes).pipe(filterAMP),\n           pl.read_parquet(os.path.join(root, TestDatapath) + \"test_applprev_1_1.parquet\").pipe(set_table_dtypes).pipe(filterAMP),\n           pl.read_parquet(os.path.join(root, TestDatapath) + \"test_applprev_1_2.parquet\").pipe(set_table_dtypes).pipe(filterAMP) \n        ],\n        how = \"vertical_relaxed\"\n).pipe(categoricalEncoding)\n\n# Depth 0\ntest_static = pl.concat( \n        [\n            pl.read_parquet(os.path.join(root, TestDatapath) + \"test_static_0_0.parquet\").pipe(set_table_dtypes),\n            pl.read_parquet(os.path.join(root, TestDatapath) + \"test_static_0_1.parquet\").pipe(set_table_dtypes),\n            pl.read_parquet(os.path.join(root, TestDatapath) + \"test_static_0_2.parquet\").pipe(set_table_dtypes)\n        ],\n        how=\"vertical_relaxed\",\n).pipe(filterAMP).pipe(categoricalEncoding)","metadata":{"execution":{"iopub.status.busy":"2024-05-28T02:38:28.642029Z","iopub.execute_input":"2024-05-28T02:38:28.642554Z","iopub.status.idle":"2024-05-28T02:38:28.727121Z","shell.execute_reply.started":"2024-05-28T02:38:28.642518Z","shell.execute_reply":"2024-05-28T02:38:28.726217Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"# Preprocessing\n\n#1. test_static\n#2. Test for test_applprev_1 (depth 1, pipe into grouping())\ntest_applprev_1_test = test_applprev_1.pipe(grouping, test_base)\n#3. Train train_person_1 (depth 1, pipe into grouping())\ntest_person_1_test = test_person_1.pipe(grouping, test_base)\n\n#4. train_tax_registries, Depth 1, sum on A \n\ntest_tax_registry_a = pl.read_parquet(os.path.join(root, TestDatapath) + \"test_tax_registry_a_1.parquet\").pipe(set_table_dtypes)\ntest_tax_registry_b = pl.read_parquet(os.path.join(root, TestDatapath) + \"test_tax_registry_b_1.parquet\").pipe(set_table_dtypes)\ntest_tax_registry_c = pl.read_parquet(os.path.join(root, TestDatapath) + \"test_tax_registry_c_1.parquet\").pipe(set_table_dtypes)\ntest_base = test_base.to_pandas()\n\ntest_tax = test_base[[\"case_id\"]].merge(\n    test_tax_registry_a.group_by(\"case_id\").agg(pl.col(\"amount_4527230A\").sum()).to_pandas(), on=\"case_id\", how=\"left\"\n).merge(\n    test_tax_registry_b.group_by(\"case_id\").agg(pl.col(\"amount_4917619A\").sum()).to_pandas(), on=\"case_id\", how=\"left\"\n).merge(\n    test_tax_registry_c.group_by(\"case_id\").agg(pl.col(\"pmtamount_36A\").sum()).to_pandas(), on=\"case_id\", how=\"left\",\n)\n\ntest_tax = pl.from_pandas(test_tax)\ntest_base = pl.from_pandas(test_base)","metadata":{"execution":{"iopub.status.busy":"2024-05-28T02:38:29.091694Z","iopub.execute_input":"2024-05-28T02:38:29.092916Z","iopub.status.idle":"2024-05-28T02:38:29.162358Z","shell.execute_reply.started":"2024-05-28T02:38:29.092877Z","shell.execute_reply":"2024-05-28T02:38:29.160902Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#5.  train_credit_bureau_a_1\ntest_c_1 = pl.read_parquet(os.path.join(root, TestDatapath) + \"test_credit_bureau_a_1_0.parquet\").pipe(set_table_dtypes).pipe(filterAMP).pipe(categoricalEncoding).pipe(set_table_dtypes)\ntest_c_2 = pl.read_parquet(os.path.join(root, TestDatapath) + \"test_credit_bureau_a_1_1.parquet\").pipe(set_table_dtypes).pipe(filterAMP).pipe(categoricalEncoding).pipe(set_table_dtypes)\ntest_c_3 = pl.read_parquet(os.path.join(root, TestDatapath) + \"test_credit_bureau_a_1_2.parquet\").pipe(set_table_dtypes).pipe(filterAMP).pipe(categoricalEncoding).pipe(set_table_dtypes)\ntest_c_4 = pl.read_parquet(os.path.join(root, TestDatapath) + \"test_credit_bureau_a_1_3.parquet\").pipe(set_table_dtypes).pipe(filterAMP).pipe(categoricalEncoding).pipe(set_table_dtypes)\n\n\ntest_credit_bureau = pl.concat(\n    [\n        test_c_1, test_c_2, test_c_3, test_c_4\n    ],\n    how=\"vertical_relaxed\"\n).pipe(set_table_dtypes)\n\ndel test_c_1\ndel test_c_2\ndel test_c_3\ndel test_c_4\n\ngc.collect()\n\ntest_credit_bureau = test_credit_bureau.pipe(grouping, test_base)","metadata":{"execution":{"iopub.status.busy":"2024-05-28T02:38:38.659330Z","iopub.execute_input":"2024-05-28T02:38:38.659763Z","iopub.status.idle":"2024-05-28T02:38:39.608827Z","shell.execute_reply.started":"2024-05-28T02:38:38.659730Z","shell.execute_reply":"2024-05-28T02:38:39.607550Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_c_1 = pl.read_parquet(os.path.join(root, TestDatapath) + \"test_credit_bureau_a_2_0.parquet\").pipe(process_tcb_a_2, test_base)\ntest_c_2 = pl.read_parquet(os.path.join(root, TestDatapath) + \"test_credit_bureau_a_2_1.parquet\").pipe(process_tcb_a_2, test_base)\n\ntest_final = aggregateOnColumnWithMemory(test_c_1, test_c_2)\n\ntest_c_3 = pl.read_parquet(os.path.join(root, TestDatapath) + \"test_credit_bureau_a_2_2.parquet\").pipe(process_tcb_a_2, test_base)\ntest_c_4 = pl.read_parquet(os.path.join(root, TestDatapath) + \"test_credit_bureau_a_2_3.parquet\").pipe(process_tcb_a_2, test_base)\n\ntest_final = aggregateOnColumnWithMemory(test_c_3, test_c_4).pipe(aggregateOnColumnWithMemory, test_final)\n\ndel test_c_1\ndel test_c_2\ndel test_c_3\ndel test_c_4\n\ngc.collect()\n\ntest_c_5 = pl.read_parquet(os.path.join(root, TestDatapath) + \"test_credit_bureau_a_2_4.parquet\").pipe(process_tcb_a_2, test_base)\ntest_c_6 = pl.read_parquet(os.path.join(root, TestDatapath) + \"test_credit_bureau_a_2_5.parquet\").pipe(process_tcb_a_2, test_base)\n\ntest_final = aggregateOnColumnWithMemory(test_c_5, test_c_6).pipe(aggregateOnColumnWithMemory, test_final)\n\ntest_c_7 = pl.read_parquet(os.path.join(root, TestDatapath) + \"test_credit_bureau_a_2_6.parquet\").pipe(process_tcb_a_2, test_base)\ntest_c_8 = pl.read_parquet(os.path.join(root, TestDatapath) + \"test_credit_bureau_a_2_7.parquet\").pipe(process_tcb_a_2, test_base)\n\ntest_final = aggregateOnColumnWithMemory(test_c_7, test_c_8).pipe(aggregateOnColumnWithMemory, test_final)\n\ndel test_c_5\ndel test_c_6\ndel test_c_7\ndel test_c_8\n\ngc.collect()\n\ntest_c_9 = pl.read_parquet(os.path.join(root, TestDatapath) + \"test_credit_bureau_a_2_8.parquet\").pipe(process_tcb_a_2, test_base)\ntest_c_10 = pl.read_parquet(os.path.join(root, TestDatapath) + \"test_credit_bureau_a_2_9.parquet\").pipe(process_tcb_a_2, test_base)\n\ntest_final = aggregateOnColumnWithMemory(test_c_9, test_c_10).pipe(aggregateOnColumnWithMemory, test_final)\n\ntest_c_11 = pl.read_parquet(os.path.join(root, TestDatapath) + \"test_credit_bureau_a_2_10.parquet\").pipe(process_tcb_a_2, test_base)\ntest_c_12 = pl.read_parquet(os.path.join(root, TestDatapath) + \"test_credit_bureau_a_2_11.parquet\").pipe(process_tcb_a_2, test_base)\n\ntest_final = aggregateOnColumnWithMemory(test_c_11, test_c_12).pipe(aggregateOnColumnWithMemory, test_final)\n\n\ndel test_c_9\ndel test_c_10\ndel test_c_11\ndel test_c_12\n\ngc.collect()\n\ntest_tcb2 = test_final\n\ndel test_final\ngc.collect()","metadata":{"execution":{"iopub.status.busy":"2024-05-28T02:52:03.825969Z","iopub.execute_input":"2024-05-28T02:52:03.827003Z","iopub.status.idle":"2024-05-28T02:52:06.687766Z","shell.execute_reply.started":"2024-05-28T02:52:03.826966Z","shell.execute_reply":"2024-05-28T02:52:06.686590Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_tcb2","metadata":{"execution":{"iopub.status.busy":"2024-05-28T02:55:47.396420Z","iopub.execute_input":"2024-05-28T02:55:47.396905Z","iopub.status.idle":"2024-05-28T02:55:47.407579Z","shell.execute_reply.started":"2024-05-28T02:55:47.396867Z","shell.execute_reply":"2024-05-28T02:55:47.406377Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_static_cols = list(set(selected_static_cols).intersection(set(test_static.columns)))\ntest_applprev_1_cols = list(set(selected_static_cols).intersection(set(test_applprev_1_test.columns)))\ntest_person_1_cols = list(set(selected_static_cols).intersection(set(test_person_1_test.columns)))\ntest_tax_cols = list(set(selected_static_cols).intersection(set(test_tax.columns)))\ntest_credit_bureau_cols = list(set(selected_static_cols).intersection(set(test_credit_bureau.columns)))\ntest_tcb2_cols = list(set(selected_static_cols).intersection(set(test_tcb2.columns)))","metadata":{"execution":{"iopub.status.busy":"2024-05-28T02:56:30.492491Z","iopub.execute_input":"2024-05-28T02:56:30.492878Z","iopub.status.idle":"2024-05-28T02:56:30.503088Z","shell.execute_reply.started":"2024-05-28T02:56:30.492849Z","shell.execute_reply":"2024-05-28T02:56:30.502209Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data_submission = test_base.join(\n    test_static.select([\"case_id\"]+test_static_cols), how=\"left\", on=\"case_id\"\n).join(\n    test_applprev_1_test.select([\"case_id\"]+test_applprev_1_cols), how=\"left\", on=\"case_id\"\n).join(\n    test_person_1_test.select([\"case_id\"]+test_person_1_cols), how=\"left\", on=\"case_id\"\n).join(\n    test_tax.select([\"case_id\"]+test_tax_cols), on=\"case_id\", how=\"left\",\n).join(\n    test_credit_bureau.select([\"case_id\"]+test_credit_bureau_cols), on=\"case_id\", how=\"left\",\n).join(\n    test_tcb2.select([\"case_id\"]+test_tcb2_cols), on=\"case_id\", how=\"left\",\n)","metadata":{"execution":{"iopub.status.busy":"2024-05-28T02:57:14.132054Z","iopub.execute_input":"2024-05-28T02:57:14.132477Z","iopub.status.idle":"2024-05-28T02:57:14.148562Z","shell.execute_reply.started":"2024-05-28T02:57:14.132445Z","shell.execute_reply":"2024-05-28T02:57:14.147499Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"data_submission[selected_static_cols].to_pandas()","metadata":{"execution":{"iopub.status.busy":"2024-05-28T02:57:17.541370Z","iopub.execute_input":"2024-05-28T02:57:17.541751Z","iopub.status.idle":"2024-05-28T02:57:17.582151Z","shell.execute_reply.started":"2024-05-28T02:57:17.541723Z","shell.execute_reply":"2024-05-28T02:57:17.580999Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"X_submission = data_submission[selected_static_cols].to_pandas()\ny_submission_pred = final_model.predict(X_submission, num_iteration=final_model.best_iteration)\n\nsubmission = 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-05-28T02:57:51.741716Z","iopub.execute_input":"2024-05-28T02:57:51.742104Z","iopub.status.idle":"2024-05-28T02:57:51.761976Z","shell.execute_reply.started":"2024-05-28T02:57:51.742074Z","shell.execute_reply":"2024-05-28T02:57:51.761035Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"submission","metadata":{"execution":{"iopub.status.busy":"2024-05-28T02:57:53.964613Z","iopub.execute_input":"2024-05-28T02:57:53.965008Z","iopub.status.idle":"2024-05-28T02:57:53.975245Z","shell.execute_reply.started":"2024-05-28T02:57:53.964980Z","shell.execute_reply":"2024-05-28T02:57:53.974072Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"from sklearn.preprocessing import LabelBinarizer\n\n### Interested in the features below in train_person_1\n'''\nchildnum_185L --- Number of children of the applicant - Ordinal, unspecified\neducation_927M --- Education level of the person. - Categorical\nempl_employedtotal_800L --- Employment length of a person. - Categorical, Unspecified\nfamilystate_447L --- Family state of the person. (Married, Divorced….), Categorical, Unspecified\nincometype_1044T --- Type of income of the person (Government, Private…), categorical\nmainoccupationinc_384A --- Amount of the main income of the client. (Most Important), numerical -> Deal with this at the end\n# maritalst_703L --- Marital status of the client, categorical (same as familystate, so don't need this?? )\nsafeguarantyflag_411L --- Flag indicating if client is using a flexible product with additional safeguard guaranty.\n'''\n### Plan : Transformations using polars -> convert to pandas, or convert polars DF to_numpy() -> Preprocessing, \n\n### 1. Numerical/categorical/object?? \n### 2. Handle NA \n### 3. Transform into something else???? \n\nclass transformations:\n    def __init__(self, df: pl.DataFrame, base_table: pl.DataFrame):\n        self.df = df\n        self.baseTable = base_table\n        \n    def joinFeatures(self, *args):\n        for index, data in enumerate(args[1:]):\n            data[index] = data[index-1].join(\n                data[index], how=\"left\", on=\"case_id\"\n            )\n        return data\n    \n    def train_person_1_transform(self):\n        cn185L = self.df.group_by('case_id').agg(pl.col('childnum_185L').max().alias('cn185L'))\n        ed927M = self.df.group_by('case_id').agg(pl.col(\"education_927M\").count().alias(\"ed927M\"))\n        # em800L = self.df.group_by('case_id').agg(pl.col(\"empl_employedtotal_800L\").max().alias(\"em800L\"))\n        # fs447L = self.df.filter(pl.col(\"num_group1\") == 0).select(pl.col(\"case_id\"), \n        #                                                 pl.col(\"familystate_447L\"))\n        sgf_411L = self.df.filter(pl.col(\"num_group1\") == 0).select(pl.col(\"case_id\"), pl.col(\"safeguarantyflag_411L\").cast(pl.Int32))\n        mo_384A  = self.df.group_by(\"case_id\").agg(pl.col(\"mainoccupationinc_384A\").sum())\n        # it_1044T = self.df.filter(pl.col(\"num_group1\") == 0).select(pl.col(\"case_id\"), \n        #                                                 pl.col(\"incometype_1044T\"))\n        em800L = self.df.select(pl.col(\"case_id\"), pl.col(\"empl_employedtotal_800L\")).fill_null(\"null\") \n        # Step 2: Map null to 0 (No employment history), LESS_ONE to 1 (Less than One Year), MORE_ONE = 2.0 (More than one Year), Else 5.0\n        em800L = em800L.select(pl.col(\"case_id\"), pl.col(\"empl_employedtotal_800L\").map_elements(\n                                    lambda x: 0.0 if x==\"null\" else 1.0 if x==\"LESS_ONE\" else 2.0 if x == \"MORE_ONE\" else 5.0, \n                                    return_dtype = pl.Float32)\n                          )\n        # Step 3: Group by the case_id and aggregate on empl_800L, count the number of entries for each case ID\n        #         Since there are 4 distinct values for empl_800L, find the sum and div / 4.0 . (we don't find the mean because count is diff)\n        #         To penalize case_id's with more entries we mutliply the value calculated above by 1/(count of entries).\n        #         Logic: The more entries for case_id, the more our div/4.0 will be penalized. If all people involved in a case_id\n        #         have some form of experience this penalty is minimal. If Just one person has experience and there's 5 people involved\n        #         in the case_id, penalty is high. This could mean that the primary borrower is the only person employed.\n        em800L = em800L.group_by(\"case_id\").agg(pl.col(\"empl_employedtotal_800L\").count().alias(\"count\"), \n                             pl.col(\"empl_employedtotal_800L\").sum() / 4.0, \n                            ).sort(by=\"case_id\").with_columns(((1.0/pl.col(\"count\")) * pl.col(\"empl_employedtotal_800L\")).\n                                                                       alias(\"Significance\")).select(pl.col(\"case_id\", \"Significance\"))\n        return self.baseTable.join(\n                cn185L, on=\"case_id\", how=\"left\"\n            ).join(\n                ed927M, on=\"case_id\", how=\"left\"\n            ).join(\n                sgf_411L, on=\"case_id\", how=\"left\"\n            ).join(\n                mo_384A, on=\"case_id\", how=\"left\"\n            ).join(\n                em800L, on=\"case_id\", how=\"left\"\n            )\n\n\n     \n               \n# transformation = transformations(train_person_second, train_base)\n# train_features = transformation.train_person_1_transform()\n\n","metadata":{"execution":{"iopub.status.busy":"2024-05-24T09:24:44.012843Z","iopub.execute_input":"2024-05-24T09:24:44.013458Z","iopub.status.idle":"2024-05-24T09:24:44.040712Z","shell.execute_reply.started":"2024-05-24T09:24:44.013420Z","shell.execute_reply":"2024-05-24T09:24:44.039261Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}