{"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":170267939,"sourceType":"kernelVersion"}],"dockerImageVersionId":30673,"isInternetEnabled":false,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"# Purpose \n\nThe purpose of this notebook is planning imputation.\n\n","metadata":{}},{"cell_type":"markdown","source":"# Procedure for planning imputation\n## Check NULL \n\n1. Probability of target for NULL and NOT NULL\n2. Check data type of columns. factor, date, numeric\n\n## Preprocessing\n\nFactor: Remove Minorities --> \"OTHERS\"\n\nDate: Convert to Integers\n\nNumeric: Nothing to do\n\n## Inputation\n\nFactor\n\n1. NULL --> \"Z\", if case_id does not exist in the file, then \"NA\"\n\nDate and Numeric\n\n1. Logistic regression by each X. \n2. Take inverse of probability of target in NULL, replace NULL with this value\n3. if case_id does not exist in the file, fill with 0.5\n","metadata":{}},{"cell_type":"markdown","source":"# About the output of notebok\n\nOutput json files contains which values to be kept of factor variables, and imputation value for numeric variables.","metadata":{}},{"cell_type":"code","source":"import numpy as np \nimport pandas as pd \nimport os\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport time\nimport re\nimport json\n#from sklearn.ensemble import RandomForestClassifier\n#from sklearn import metrics\nfrom sklearn.linear_model import LogisticRegression","metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","execution":{"iopub.status.busy":"2024-04-04T10:38:58.980612Z","iopub.execute_input":"2024-04-04T10:38:58.980992Z","iopub.status.idle":"2024-04-04T10:39:02.002563Z","shell.execute_reply.started":"2024-04-04T10:38:58.980961Z","shell.execute_reply":"2024-04-04T10:39:02.001183Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"sample_df = pd.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/sample_submission.csv\")\nfeature_definition = pd.read_csv(\"/kaggle/input/home-credit-credit-risk-model-stability/feature_definitions.csv\")\ntrain_dir = \"/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/train/\"\ntrain_files = os.listdir(train_dir)\nlen(train_files)","metadata":{"execution":{"iopub.status.busy":"2024-04-04T10:39:02.006598Z","iopub.execute_input":"2024-04-04T10:39:02.007076Z","iopub.status.idle":"2024-04-04T10:39:02.047930Z","shell.execute_reply.started":"2024-04-04T10:39:02.007049Z","shell.execute_reply":"2024-04-04T10:39:02.046817Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"test_dir = \"/kaggle/input/home-credit-credit-risk-model-stability/parquet_files/test/\"\ntest_files = os.listdir(test_dir)\nlen(test_files)","metadata":{"execution":{"iopub.status.busy":"2024-04-04T10:39:02.049274Z","iopub.execute_input":"2024-04-04T10:39:02.049817Z","iopub.status.idle":"2024-04-04T10:39:02.075914Z","shell.execute_reply.started":"2024-04-04T10:39:02.049789Z","shell.execute_reply":"2024-04-04T10:39:02.074596Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_base = pd.read_parquet(train_dir + \"train_base.parquet\")","metadata":{"execution":{"iopub.status.busy":"2024-04-04T10:39:02.078516Z","iopub.execute_input":"2024-04-04T10:39:02.078863Z","iopub.status.idle":"2024-04-04T10:39:02.463880Z","shell.execute_reply.started":"2024-04-04T10:39:02.078835Z","shell.execute_reply":"2024-04-04T10:39:02.463035Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"n_all = train_base.shape[0]\nn_train = int(n_all*0.9)\nn_val = n_all - n_train","metadata":{"execution":{"iopub.status.busy":"2024-04-04T10:39:02.465056Z","iopub.execute_input":"2024-04-04T10:39:02.465488Z","iopub.status.idle":"2024-04-04T10:39:02.469944Z","shell.execute_reply.started":"2024-04-04T10:39:02.465450Z","shell.execute_reply":"2024-04-04T10:39:02.469174Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"print(train_base[\"target\"].iloc[n_train:].sum(), \" positive in val data\")","metadata":{"execution":{"iopub.status.busy":"2024-04-04T10:39:02.471238Z","iopub.execute_input":"2024-04-04T10:39:02.472121Z","iopub.status.idle":"2024-04-04T10:39:02.482967Z","shell.execute_reply.started":"2024-04-04T10:39:02.472070Z","shell.execute_reply":"2024-04-04T10:39:02.482068Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_base = train_base.iloc[:n_train,:]","metadata":{"execution":{"iopub.status.busy":"2024-04-04T10:39:02.484267Z","iopub.execute_input":"2024-04-04T10:39:02.485308Z","iopub.status.idle":"2024-04-04T10:39:02.492957Z","shell.execute_reply.started":"2024-04-04T10:39:02.485276Z","shell.execute_reply":"2024-04-04T10:39:02.491965Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"category = [\"applprev\",\n\"credit_bureau\",\n\"debitcard_1\",\n\"deposit_1\",\n\"person\",\n\"static\",\n\"tax_registry\",\n\"other\"]","metadata":{"execution":{"iopub.status.busy":"2024-04-04T10:39:02.494595Z","iopub.execute_input":"2024-04-04T10:39:02.495295Z","iopub.status.idle":"2024-04-04T10:39:02.502650Z","shell.execute_reply.started":"2024-04-04T10:39:02.495253Z","shell.execute_reply":"2024-04-04T10:39:02.501770Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def read_json(file1):\n    with open(file1, 'r',  encoding='utf-8') as f:\n        data = json.load(f)\n        \n    return data","metadata":{"execution":{"iopub.status.busy":"2024-04-04T10:39:02.504005Z","iopub.execute_input":"2024-04-04T10:39:02.504565Z","iopub.status.idle":"2024-04-04T10:39:02.511994Z","shell.execute_reply.started":"2024-04-04T10:39:02.504536Z","shell.execute_reply":"2024-04-04T10:39:02.511175Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fpath = \"/kaggle/input/homecredit-filesbycategory/train_files_subcategory.json\"\nsubcategory_files_train = read_json(fpath)","metadata":{"execution":{"iopub.status.busy":"2024-04-04T10:39:02.515526Z","iopub.execute_input":"2024-04-04T10:39:02.515879Z","iopub.status.idle":"2024-04-04T10:39:02.524493Z","shell.execute_reply.started":"2024-04-04T10:39:02.515850Z","shell.execute_reply":"2024-04-04T10:39:02.523438Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fpath = \"/kaggle/input/homecredit-filesbycategory/test_files_subcategory.json\"\nsubcategory_files_test = read_json(fpath)","metadata":{"execution":{"iopub.status.busy":"2024-04-04T10:39:02.525915Z","iopub.execute_input":"2024-04-04T10:39:02.526252Z","iopub.status.idle":"2024-04-04T10:39:02.534756Z","shell.execute_reply.started":"2024-04-04T10:39:02.526220Z","shell.execute_reply":"2024-04-04T10:39:02.533552Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"fpath = \"/kaggle/input/homecredit-filesbycategory/files_depth.json\"\nfile_depth = read_json(fpath)\n","metadata":{"execution":{"iopub.status.busy":"2024-04-04T10:39:02.536531Z","iopub.execute_input":"2024-04-04T10:39:02.536924Z","iopub.status.idle":"2024-04-04T10:39:02.545967Z","shell.execute_reply.started":"2024-04-04T10:39:02.536893Z","shell.execute_reply":"2024-04-04T10:39:02.545171Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Codes for reading files","metadata":{}},{"cell_type":"code","source":"def readFiles(files, merge_label = False):\n    count = 0\n    for file in files:\n        if count == 0:\n            data = pd.read_parquet(train_dir + file)\n            \n        else:\n            tmp = pd.read_parquet(train_dir + file)\n            data = pd.concat([data, tmp])\n        \n        count += 1\n    \n    if merge_label:\n        data = data.merge(train_base[[\"case_id\", \"target\"]], on = \"case_id\", how = \"inner\")\n    \n    return data\n\n\ndef readFilesAggL1(files, merge_label = False):\n    count = 0\n    \n    \n    for file in files:\n        if count == 0:\n            data = pd.read_parquet(train_dir + file)\n            filter1 = data[\"num_group1\"] == 0\n            data = data.loc[filter1]\n        \n            \n        else:\n            tmp = pd.read_parquet(train_dir + file)\n            filter1 = tmp[\"num_group1\"] == 0\n            tmp = tmp.loc[filter1]\n            \n            data = pd.concat([data, tmp])\n        \n        count += 1\n    \n    if merge_label:\n        data = data.merge(train_base[[\"case_id\", \"target\"]], on = \"case_id\", how = \"inner\")\n    \n    return data\n\n\ndef readFilesAggL2(files, merge_label = False):\n    count = 0\n    \n    for file in files:\n        if count == 0:\n            data = pd.read_parquet(train_dir + file)\n            filter1 = (data[\"num_group1\"] == 0) & (data[\"num_group2\"] == 0)\n            data = data.loc[filter1]\n        \n            \n        else:\n            tmp = pd.read_parquet(train_dir + file)\n            filter1 = (tmp[\"num_group1\"] == 0) & (tmp[\"num_group2\"] == 0)\n            tmp = tmp.loc[filter1]\n            \n            data = pd.concat([data, tmp])\n        \n        count += 1\n    \n    if merge_label:\n        data = data.merge(train_base[[\"case_id\", \"target\"]], on = \"case_id\", how = \"inner\")\n    \n    return data","metadata":{"execution":{"iopub.status.busy":"2024-04-04T10:39:02.547243Z","iopub.execute_input":"2024-04-04T10:39:02.547953Z","iopub.status.idle":"2024-04-04T10:39:02.561209Z","shell.execute_reply.started":"2024-04-04T10:39:02.547914Z","shell.execute_reply":"2024-04-04T10:39:02.560167Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def createDataForEDA(data):\n\n    n_pos = min(data[\"target\"].sum(), 10000)\n    filter1 = data[\"target\"] == 1\n    data_eda = pd.concat([data.loc[filter1].sample(n_pos), data.loc[~filter1].sample(n_pos)])\n    \n    return data_eda","metadata":{"execution":{"iopub.status.busy":"2024-04-04T10:39:02.562639Z","iopub.execute_input":"2024-04-04T10:39:02.563028Z","iopub.status.idle":"2024-04-04T10:39:02.574040Z","shell.execute_reply.started":"2024-04-04T10:39:02.562998Z","shell.execute_reply":"2024-04-04T10:39:02.573133Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Pipline Code","metadata":{}},{"cell_type":"markdown","source":"## For numeric and datetime","metadata":{}},{"cell_type":"code","source":"def isDATE(c):\n    \n    if c.endswith(\"D\"):\n        return True\n    return False\n    \n    words = [\"date\", \"from\", \"dt\", \"birth\", \"from_\"]\n    \n    for w in words:\n        if re.search(\"date\", c) is not None:\n            return True\n    \n    return False","metadata":{"execution":{"iopub.status.busy":"2024-04-04T10:39:02.575146Z","iopub.execute_input":"2024-04-04T10:39:02.575791Z","iopub.status.idle":"2024-04-04T10:39:02.583173Z","shell.execute_reply.started":"2024-04-04T10:39:02.575748Z","shell.execute_reply":"2024-04-04T10:39:02.582316Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def meanY_NumericNULL(data):\n    \n    drop_list = [\"case_id\", \"target\"]\n    cnames = data.drop(drop_list, axis = 1).columns\n    imputation_dict = {}\n    \n    na_df = data.isna()\n    na_sum = na_df.sum()\n    na_df[\"target\"] = data[\"target\"]\n    \n    for c in cnames:  \n        dtype1 = data[c].dtype\n\n        dtype2 = \"factor\"\n        if dtype1 == float:\n            dtype2 = \"numeric\"\n\n        if dtype1 == object:\n            if isDATE(c):\n                dtype2 = \"date\"\n\n        if na_sum[c] > 0:\n            try:\n                v_false, v_true = na_df[[c, \"target\"]].groupby(c).mean()[\"target\"].to_numpy()\n                imputation_dict[c] = (v_false, v_true, dtype2)\n            except ValueError:\n                pass\n\n        else:\n            imputation_dict[c] = (0.5, 0.5, dtype2)\n            \n            \n    return imputation_dict","metadata":{"execution":{"iopub.status.busy":"2024-04-04T10:39:02.584542Z","iopub.execute_input":"2024-04-04T10:39:02.585084Z","iopub.status.idle":"2024-04-04T10:39:02.598664Z","shell.execute_reply.started":"2024-04-04T10:39:02.585054Z","shell.execute_reply":"2024-04-04T10:39:02.597630Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"def createLogisticRegression(data, c, null_p, plot_reg = False):\n    # logistic regression --------------\n    clf = LogisticRegression(random_state=0)\n\n    # select column, remove null\n    data2 = data[[c, \"target\"]].dropna()\n    \n    if data2[c].dtype == object:\n        data2[c] = pd.to_datetime(data2[c]).astype(int)\n\n    # logistic regression\n    \n    X = data2[c].to_numpy().reshape((-1, 1))\n    y = data2[\"target\"].to_numpy()\n    \n    try:\n        clf.fit(X, y)\n    except ValueError:\n        return 0.0, 0, 0, 0, 0\n\n    # if train accuracy < Threshold, drop this variable\n    \n    phat = clf.predict_proba(X)[:,1]\n    yhat = clf.predict(X)\n    \n    accuracy = np.round(np.mean(y == yhat), 4)\n    \n    b0 = clf.intercept_[0]\n    b1 = clf.coef_[0][0]\n    \n    x_imputation = (np.log(null_p/(1-null_p)) - b0)/b1\n    noid_imputation = (np.log(0.5) - b0)/b1\n    \n    if plot_reg:\n        \n        print(\"accuracy\", accuracy)\n        print(\"intercept\", b0)\n        print(\"coeff\", b1)\n        print(\"imputation\", x_imputation)\n    \n        fig, ax = plt.subplots(figsize = (4.5,3.5))\n        #fig, ax = plt.subplots()\n        ax.scatter(X, y, alpha = 0.1)\n        ax.scatter(X, phat, label  = \"yhat\", s = 5)\n        ax.legend()\n        ax.axhline(y = 0.5, color = \"black\", alpha = 0.4, ls = \"-.\")\n        ax.set_title(c + \", accuracy \" + str(accuracy))\n        plt.show()\n    \n    \n    return accuracy, b0, b1, x_imputation, noid_imputation","metadata":{"execution":{"iopub.status.busy":"2024-04-04T10:39:02.600385Z","iopub.execute_input":"2024-04-04T10:39:02.601093Z","iopub.status.idle":"2024-04-04T10:39:02.611937Z","shell.execute_reply.started":"2024-04-04T10:39:02.601058Z","shell.execute_reply":"2024-04-04T10:39:02.610699Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## For factors","metadata":{}},{"cell_type":"code","source":"def filterFactor(data, c, min_count = 50, p_thresh = 0.075):\n    #min count was 100\n    #p thresh was 0.075\n    \n    data_agg = data[[c, \"target\"]].groupby(c).agg([\"mean\", \"count\"])[\"target\"]\n    \n    filter1 = data_agg[\"count\"] >= min_count\n    filter2 = (data_agg[\"mean\"] - 0.5).abs() > p_thresh\n    \n    filter3 = filter1 & filter2\n    \n    return list(data_agg.loc[filter3].index)","metadata":{"execution":{"iopub.status.busy":"2024-04-04T10:39:02.613399Z","iopub.execute_input":"2024-04-04T10:39:02.614069Z","iopub.status.idle":"2024-04-04T10:39:02.627266Z","shell.execute_reply.started":"2024-04-04T10:39:02.614037Z","shell.execute_reply":"2024-04-04T10:39:02.626258Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Combine All: Pipeline","metadata":{}},{"cell_type":"code","source":"def Pipeline(data, p_thresh = 0.55):\n    \n    imputation_dict = meanY_NumericNULL(data)\n    \n    drop_list = [\"case_id\", \"target\"]\n    cnames = list(data.drop(drop_list, axis = 1).columns)\n    N =  len(cnames)\n    \n    col_data_dict = {}\n    for i in range(N):\n        \n        try:\n            val_f, val_t, dtype1 = imputation_dict[cnames[i]]\n            c = cnames[i]\n\n            if dtype1 != \"factor\":\n                accuracy, b0, b1, x_imputation, noid_imputation = createLogisticRegression(data, c, val_t)\n\n                if (accuracy > p_thresh) and np.abs(b1) > 0:\n                    col_data_dict[c] = dtype1, x_imputation, noid_imputation\n\n            else:\n                factor_values = filterFactor(data, c)\n                if len(factor_values) > 0:\n                    col_data_dict[c] = dtype1, factor_values\n        except KeyError:\n            pass\n        \n    return col_data_dict","metadata":{"execution":{"iopub.status.busy":"2024-04-04T10:39:02.628985Z","iopub.execute_input":"2024-04-04T10:39:02.629479Z","iopub.status.idle":"2024-04-04T10:39:02.638433Z","shell.execute_reply.started":"2024-04-04T10:39:02.629448Z","shell.execute_reply":"2024-04-04T10:39:02.637239Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## write result as json","metadata":{}},{"cell_type":"code","source":"def write_json(data_json, file1):\n    fname_json = file1 + \".json\"\n    with open(fname_json, 'w',  encoding='utf-8') as f:\n        json.dump(data_json, f)","metadata":{"execution":{"iopub.status.busy":"2024-04-04T10:39:02.640085Z","iopub.execute_input":"2024-04-04T10:39:02.640663Z","iopub.status.idle":"2024-04-04T10:39:02.650753Z","shell.execute_reply.started":"2024-04-04T10:39:02.640628Z","shell.execute_reply":"2024-04-04T10:39:02.649693Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# Execute","metadata":{}},{"cell_type":"code","source":"pipeline_dict_all = {}","metadata":{"execution":{"iopub.status.busy":"2024-04-04T10:39:02.652471Z","iopub.execute_input":"2024-04-04T10:39:02.653186Z","iopub.status.idle":"2024-04-04T10:39:02.660478Z","shell.execute_reply.started":"2024-04-04T10:39:02.653146Z","shell.execute_reply":"2024-04-04T10:39:02.659402Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"for key in subcategory_files_train.keys():\n    files1 = subcategory_files_train[key]\n    depth1 = file_depth[key]\n    print(depth1, files1)\n    \n    if depth1 == 0:\n        data_df = readFiles(files1, True)\n    elif depth1 == 1:\n        data_df = readFilesAggL1(files1, True)\n    else:\n        data_df = readFilesAggL2(files1, True)\n        \n    eda_df = createDataForEDA(data_df)\n    pipeline_dict = Pipeline(eda_df)\n    pipeline_dict_all[key] = pipeline_dict\n\n","metadata":{"execution":{"iopub.status.busy":"2024-04-04T10:39:02.661649Z","iopub.execute_input":"2024-04-04T10:39:02.662173Z","iopub.status.idle":"2024-04-04T10:42:01.593898Z","shell.execute_reply.started":"2024-04-04T10:39:02.662147Z","shell.execute_reply":"2024-04-04T10:42:01.591547Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"#pipeline_dict_all","metadata":{"execution":{"iopub.status.busy":"2024-04-04T10:42:01.595896Z","iopub.execute_input":"2024-04-04T10:42:01.597201Z","iopub.status.idle":"2024-04-04T10:42:01.625060Z","shell.execute_reply.started":"2024-04-04T10:42:01.597148Z","shell.execute_reply":"2024-04-04T10:42:01.623859Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"write_json(pipeline_dict_all, \"imputation_plan\")","metadata":{"execution":{"iopub.status.busy":"2024-04-04T10:42:01.626578Z","iopub.execute_input":"2024-04-04T10:42:01.627721Z","iopub.status.idle":"2024-04-04T10:42:01.634595Z","shell.execute_reply.started":"2024-04-04T10:42:01.627684Z","shell.execute_reply":"2024-04-04T10:42:01.633612Z"},"trusted":true},"execution_count":null,"outputs":[]}]}