5.9. Merge Path Clustering.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <font color='CC3D3D'> 0.5_9 Merge_Path_Clustering\n",
    "    \n",
    "- 비슷한 경로의 6개의 군집으로 나눈 path_group.csv와 loan_result.csv를 취합한 코드\n",
    "- 중간 단계의 데이터 <span style=\"color:blue\"> **loan_.csv** </span> 생성\n",
    "- 최종 취합 데이터 <span style=\"color:blue\"> **loan_path_segment.csv** </span> 생성"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "SB741M-hYfYZ"
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from tqdm import tqdm\n",
    "import pickle\n",
    "from IPython.display import display\n",
    "import gc\n",
    "from typing import Union\n",
    "import time\n",
    "\n",
    "pd.set_option('display.max_columns', None)\n",
    "\n",
    "with open(\"../Data/dtypes.pkl\", 'rb') as f:\n",
    "    dtypes = pickle.load(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "tN04VOv6xtIU"
   },
   "outputs": [],
   "source": [
    "def view_info(df:pd.DataFrame) -> None:\n",
    "    print(df.shape)\n",
    "    print()\n",
    "    df.info()\n",
    "    print()\n",
    "    display(df.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "XMfpInxT3abM"
   },
   "source": [
    "# Task"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "kAHV40db3K-1"
   },
   "source": [
    "## Data Import"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "Y5SzIr3KYz_B",
    "outputId": "2c32c571-a9e0-474b-c58a-36bd23fce2aa"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(13527363, 3)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 13527363 entries, 0 to 13527362\n",
      "Data columns (total 3 columns):\n",
      " #   Column                 Dtype         \n",
      "---  ------                 -----         \n",
      " 0   application_id         uint32        \n",
      " 1   loanapply_insert_time  datetime64[ns]\n",
      " 2   is_applied             float64       \n",
      "dtypes: datetime64[ns](1), float64(1), uint32(1)\n",
      "memory usage: 258.0 MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-769b0a20-b06c-4551-8411-d4b5828a2dd8\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>application_id</th>\n",
       "      <th>loanapply_insert_time</th>\n",
       "      <th>is_applied</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-769b0a20-b06c-4551-8411-d4b5828a2dd8')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-769b0a20-b06c-4551-8411-d4b5828a2dd8 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-769b0a20-b06c-4551-8411-d4b5828a2dd8');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "   application_id loanapply_insert_time  is_applied\n",
       "0         1748340   2022-06-07 13:05:41         NaN\n",
       "1         1748340   2022-06-07 13:05:41         NaN\n",
       "2         1748340   2022-06-07 13:05:41         NaN\n",
       "3         1748340   2022-06-07 13:05:41         NaN\n",
       "4         1748340   2022-06-07 13:05:41         NaN"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "(17843993, 4)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 17843993 entries, 0 to 17843992\n",
      "Data columns (total 4 columns):\n",
      " #   Column     Dtype         \n",
      "---  ------     -----         \n",
      " 0   user_id    uint32        \n",
      " 1   event      object        \n",
      " 2   timestamp  datetime64[ns]\n",
      " 3   date_cd    datetime64[ns]\n",
      "dtypes: datetime64[ns](2), object(1), uint32(1)\n",
      "memory usage: 476.5+ MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-e504594c-b7e0-43d2-afc5-2358a39baec0\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>event</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>date_cd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>576409</td>\n",
       "      <td>StartLoanApply</td>\n",
       "      <td>2022-03-25 11:12:09</td>\n",
       "      <td>2022-03-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>576409</td>\n",
       "      <td>ViewLoanApplyIntro</td>\n",
       "      <td>2022-03-25 11:12:09</td>\n",
       "      <td>2022-03-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>72878</td>\n",
       "      <td>EndLoanApply</td>\n",
       "      <td>2022-03-25 11:14:44</td>\n",
       "      <td>2022-03-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>645317</td>\n",
       "      <td>OpenApp</td>\n",
       "      <td>2022-03-25 11:15:09</td>\n",
       "      <td>2022-03-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>645317</td>\n",
       "      <td>UseLoanManage</td>\n",
       "      <td>2022-03-25 11:15:11</td>\n",
       "      <td>2022-03-25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-e504594c-b7e0-43d2-afc5-2358a39baec0')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-e504594c-b7e0-43d2-afc5-2358a39baec0 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-e504594c-b7e0-43d2-afc5-2358a39baec0');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "   user_id               event           timestamp    date_cd\n",
       "0   576409      StartLoanApply 2022-03-25 11:12:09 2022-03-25\n",
       "1   576409  ViewLoanApplyIntro 2022-03-25 11:12:09 2022-03-25\n",
       "2    72878        EndLoanApply 2022-03-25 11:14:44 2022-03-25\n",
       "3   645317             OpenApp 2022-03-25 11:15:09 2022-03-25\n",
       "4   645317       UseLoanManage 2022-03-25 11:15:11 2022-03-25"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "(1394216, 3)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1394216 entries, 0 to 1394215\n",
      "Data columns (total 3 columns):\n",
      " #   Column          Non-Null Count    Dtype         \n",
      "---  ------          --------------    -----         \n",
      " 0   application_id  1394216 non-null  uint32        \n",
      " 1   user_id         1394216 non-null  uint32        \n",
      " 2   insert_time     1394216 non-null  datetime64[ns]\n",
      "dtypes: datetime64[ns](1), uint32(2)\n",
      "memory usage: 21.3 MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-9186659f-8368-49a4-90d1-7ff97187da4e\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>application_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>insert_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1249046</td>\n",
       "      <td>118218</td>\n",
       "      <td>2022-06-07 06:28:18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>954900</td>\n",
       "      <td>553686</td>\n",
       "      <td>2022-06-07 14:29:03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>137274</td>\n",
       "      <td>59516</td>\n",
       "      <td>2022-06-07 21:40:22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1570936</td>\n",
       "      <td>167320</td>\n",
       "      <td>2022-06-07 09:40:27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>967833</td>\n",
       "      <td>33400</td>\n",
       "      <td>2022-06-07 08:55:07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-9186659f-8368-49a4-90d1-7ff97187da4e')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-9186659f-8368-49a4-90d1-7ff97187da4e button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-9186659f-8368-49a4-90d1-7ff97187da4e');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "   application_id  user_id         insert_time\n",
       "0         1249046   118218 2022-06-07 06:28:18\n",
       "1          954900   553686 2022-06-07 14:29:03\n",
       "2          137274    59516 2022-06-07 21:40:22\n",
       "3         1570936   167320 2022-06-07 09:40:27\n",
       "4          967833    33400 2022-06-07 08:55:07"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "loan = pd.read_csv(\"../Data/2022빅콘테스트_데이터분석리그_데이터분석분야_퓨처스부문_데이터셋_220908/loan_result.csv\", engine='c',\n",
    "                   low_memory=True,\n",
    "                   parse_dates=['loanapply_insert_time'],\n",
    "                   dtype={'application_id': 'uint32', 'bank_id': 'uint8', 'product_id': 'uint8'},\n",
    "                   usecols=['application_id', 'loanapply_insert_time', 'is_applied'])\n",
    "log = pd.read_csv(\"../Data/2022빅콘테스트_데이터분석리그_데이터분석분야_퓨처스부문_데이터셋_220908/log_data.csv\", engine='c', \n",
    "                  dtype={'user_id': 'uint32', 'event': 'object'},\n",
    "                  usecols=['user_id', 'event', 'timestamp', 'date_cd'], \n",
    "                  low_memory=True,\n",
    "                  parse_dates=['timestamp', 'date_cd'])\n",
    "user = pd.read_csv(\"../Data/2022빅콘테스트_데이터분석리그_데이터분석분야_퓨처스부문_데이터셋_220908/user_spec.csv\", engine='c',\n",
    "                   dtype={'application_id': 'uint32', 'user_id': 'uint32'},\n",
    "                   low_memory=True,\n",
    "                   parse_dates=['insert_time'],\n",
    "                   usecols=['application_id', 'user_id', 'insert_time'])\n",
    "\n",
    "view_info(loan)\n",
    "print()\n",
    "view_info(log)\n",
    "print()\n",
    "view_info(user)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "IaatXQBfeJKR",
    "outputId": "2cd2b323-ee6e-4110-d858-5b494dad22b3"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "application_id                 0\n",
       "loanapply_insert_time          0\n",
       "is_applied               3257239\n",
       "dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "r9C-hNXb3UDZ"
   },
   "source": [
    "## Merge application - user"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "mIV0lCVvlcDW",
    "outputId": "1df73039-a164-4279-d416-6f06a40baba2"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "application_id                 0\n",
       "loanapply_insert_time          0\n",
       "is_applied               3257239\n",
       "user_id                      113\n",
       "dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# app_user = {user['application_id'].iloc[i]: user['user_id'].iloc[i] for i in range(user.shape[0])}\n",
    "# loan['user_id'] = loan['application_id'].map(app_user)\n",
    "# loan.isnull().sum()\n",
    "# train = loan[loan['loanapply_insert_time'].dt.month < 6]\n",
    "# train['user_id'] = train['application_id'].map(app_user)\n",
    "# train.dropna(subset=['user_id'], inplace=True)\n",
    "# train['user_id'] = train['user_id'].astype(np.uint32)\n",
    "\n",
    "# view_info(train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "fM4QpCiw2r8A"
   },
   "outputs": [],
   "source": [
    "loan.dropna(subset=['user_id'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "my7FoFpFmyo5",
    "outputId": "b96e5de1-bc00-4ac7-f4f4-d783a2c289ab"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(728997, 2)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 728997 entries, 0 to 728996\n",
      "Data columns (total 2 columns):\n",
      " #   Column          Non-Null Count   Dtype  \n",
      "---  ------          --------------   -----  \n",
      " 0   application_id  728997 non-null  uint64 \n",
      " 1   total_applied   728997 non-null  float64\n",
      "dtypes: float64(1), uint64(1)\n",
      "memory usage: 11.1 MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-350e0306-f9c2-4e92-abaa-8af194a07f99\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>application_id</th>\n",
       "      <th>total_applied</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-350e0306-f9c2-4e92-abaa-8af194a07f99')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-350e0306-f9c2-4e92-abaa-8af194a07f99 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-350e0306-f9c2-4e92-abaa-8af194a07f99');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "   application_id  total_applied\n",
       "0               1            0.0\n",
       "1               2            0.0\n",
       "2               6            0.0\n",
       "3              14            3.0\n",
       "4              17            1.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# train = loan[loan['loanapply_insert_time'].dt.month < 6]\n",
    "# apply_group = train[['application_id', 'is_applied']].groupby(by='application_id').sum()\n",
    "# apply_group.reset_index(drop=False, inplace=True)\n",
    "# apply_group.columns = ['application_id', 'total_applied']\n",
    "\n",
    "# view_info(apply_group)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 456
    },
    "id": "ZfELGGl5wOXC",
    "outputId": "a8746bd1-da5e-4a3d-e9f0-bb9aea06c5e9"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(10270124, 4)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 10270124 entries, 0 to 10270123\n",
      "Data columns (total 4 columns):\n",
      " #   Column                 Dtype         \n",
      "---  ------                 -----         \n",
      " 0   application_id         uint32        \n",
      " 1   loanapply_insert_time  datetime64[ns]\n",
      " 2   is_applied             float64       \n",
      " 3   total_applied          float64       \n",
      "dtypes: datetime64[ns](1), float64(2), uint32(1)\n",
      "memory usage: 352.6 MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-8a84d16c-c97b-416b-8340-e596b14f0135\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>application_id</th>\n",
       "      <th>loanapply_insert_time</th>\n",
       "      <th>is_applied</th>\n",
       "      <th>total_applied</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2157865</td>\n",
       "      <td>2022-05-09 08:44:59</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>576643</td>\n",
       "      <td>2022-05-09 10:54:53</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>576643</td>\n",
       "      <td>2022-05-09 10:54:53</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2136706</td>\n",
       "      <td>2022-05-09 10:41:06</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2136706</td>\n",
       "      <td>2022-05-09 10:41:07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-8a84d16c-c97b-416b-8340-e596b14f0135')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-8a84d16c-c97b-416b-8340-e596b14f0135 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-8a84d16c-c97b-416b-8340-e596b14f0135');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "   application_id loanapply_insert_time  is_applied  total_applied\n",
       "0         2157865   2022-05-09 08:44:59         1.0            1.0\n",
       "1          576643   2022-05-09 10:54:53         0.0            0.0\n",
       "2          576643   2022-05-09 10:54:53         0.0            0.0\n",
       "3         2136706   2022-05-09 10:41:06         0.0            3.0\n",
       "4         2136706   2022-05-09 10:41:07         0.0            3.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "total_loan = pd.merge(left=train, right=apply_group, on='application_id', how='inner')\n",
    "view_info(total_loan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 492
    },
    "id": "ebCABhblxp8m",
    "outputId": "bb78eb5a-976e-4033-8461-d6c61d7a637a"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(10270011, 6)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 10270011 entries, 0 to 10270123\n",
      "Data columns (total 6 columns):\n",
      " #   Column                 Dtype         \n",
      "---  ------                 -----         \n",
      " 0   application_id         uint32        \n",
      " 1   loanapply_insert_time  datetime64[ns]\n",
      " 2   is_applied             float64       \n",
      " 3   total_applied          float64       \n",
      " 4   user_id                uint32        \n",
      " 5   date_cd                datetime64[ns]\n",
      "dtypes: datetime64[ns](2), float64(2), uint32(2)\n",
      "memory usage: 470.1 MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-b26c7bb5-ecb4-4efe-8934-5352b577dec8\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>application_id</th>\n",
       "      <th>loanapply_insert_time</th>\n",
       "      <th>is_applied</th>\n",
       "      <th>total_applied</th>\n",
       "      <th>user_id</th>\n",
       "      <th>date_cd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2157865</td>\n",
       "      <td>2022-05-09 08:44:59</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>346970</td>\n",
       "      <td>2022-05-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>576643</td>\n",
       "      <td>2022-05-09 10:54:53</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>545882</td>\n",
       "      <td>2022-05-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>576643</td>\n",
       "      <td>2022-05-09 10:54:53</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>545882</td>\n",
       "      <td>2022-05-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2136706</td>\n",
       "      <td>2022-05-09 10:41:06</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>558819</td>\n",
       "      <td>2022-05-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2136706</td>\n",
       "      <td>2022-05-09 10:41:07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>558819</td>\n",
       "      <td>2022-05-09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-b26c7bb5-ecb4-4efe-8934-5352b577dec8')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-b26c7bb5-ecb4-4efe-8934-5352b577dec8 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-b26c7bb5-ecb4-4efe-8934-5352b577dec8');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "   application_id loanapply_insert_time  is_applied  total_applied  user_id  \\\n",
       "0         2157865   2022-05-09 08:44:59         1.0            1.0   346970   \n",
       "1          576643   2022-05-09 10:54:53         0.0            0.0   545882   \n",
       "2          576643   2022-05-09 10:54:53         0.0            0.0   545882   \n",
       "3         2136706   2022-05-09 10:41:06         0.0            3.0   558819   \n",
       "4         2136706   2022-05-09 10:41:07         0.0            3.0   558819   \n",
       "\n",
       "     date_cd  \n",
       "0 2022-05-09  \n",
       "1 2022-05-09  \n",
       "2 2022-05-09  \n",
       "3 2022-05-09  \n",
       "4 2022-05-09  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "total_loan['user_id'] = total_loan['application_id'].map(app_user)\n",
    "total_loan.dropna(subset=['user_id'], inplace=True)\n",
    "total_loan['user_id'] = total_loan['user_id'].astype(np.uint32)\n",
    "total_loan['date_cd'] = pd.to_datetime(total_loan['loanapply_insert_time'].dt.date)\n",
    "view_info(total_loan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 456
    },
    "id": "JKp7PwJWuSLm",
    "outputId": "53087c70-22f7-4e97-ac80-7dcf7944a48b"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(12907328, 4)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 12907328 entries, 0 to 17843992\n",
      "Data columns (total 4 columns):\n",
      " #   Column     Dtype         \n",
      "---  ------     -----         \n",
      " 0   user_id    uint32        \n",
      " 1   event      object        \n",
      " 2   timestamp  datetime64[ns]\n",
      " 3   date_cd    datetime64[ns]\n",
      "dtypes: datetime64[ns](2), object(1), uint32(1)\n",
      "memory usage: 443.1+ MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-9e4b67ef-7971-4a0a-884d-c9e7cf6fdb35\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>event</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>date_cd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>576409</td>\n",
       "      <td>StartLoanApply</td>\n",
       "      <td>2022-03-25 11:12:09</td>\n",
       "      <td>2022-03-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>576409</td>\n",
       "      <td>ViewLoanApplyIntro</td>\n",
       "      <td>2022-03-25 11:12:09</td>\n",
       "      <td>2022-03-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>72878</td>\n",
       "      <td>EndLoanApply</td>\n",
       "      <td>2022-03-25 11:14:44</td>\n",
       "      <td>2022-03-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>645317</td>\n",
       "      <td>OpenApp</td>\n",
       "      <td>2022-03-25 11:15:09</td>\n",
       "      <td>2022-03-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>645317</td>\n",
       "      <td>UseLoanManage</td>\n",
       "      <td>2022-03-25 11:15:11</td>\n",
       "      <td>2022-03-25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-9e4b67ef-7971-4a0a-884d-c9e7cf6fdb35')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-9e4b67ef-7971-4a0a-884d-c9e7cf6fdb35 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-9e4b67ef-7971-4a0a-884d-c9e7cf6fdb35');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "   user_id               event           timestamp    date_cd\n",
       "0   576409      StartLoanApply 2022-03-25 11:12:09 2022-03-25\n",
       "1   576409  ViewLoanApplyIntro 2022-03-25 11:12:09 2022-03-25\n",
       "2    72878        EndLoanApply 2022-03-25 11:14:44 2022-03-25\n",
       "3   645317             OpenApp 2022-03-25 11:15:09 2022-03-25\n",
       "4   645317       UseLoanManage 2022-03-25 11:15:11 2022-03-25"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "log_train = log[log['date_cd'].dt.month < 6]\n",
    "view_info(log_train)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 492
    },
    "id": "np_AcxO75DR5",
    "outputId": "3caaefda-d085-4962-ffe6-d456858e4aff"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(105414947, 6)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 105414947 entries, 0 to 105414946\n",
      "Data columns (total 6 columns):\n",
      " #   Column          Dtype         \n",
      "---  ------          -----         \n",
      " 0   user_id         uint32        \n",
      " 1   event           object        \n",
      " 2   timestamp       datetime64[ns]\n",
      " 3   date_cd         datetime64[ns]\n",
      " 4   application_id  uint32        \n",
      " 5   total_applied   uint8         \n",
      "dtypes: datetime64[ns](2), object(1), uint32(2), uint8(1)\n",
      "memory usage: 4.0+ GB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-cfdfcf4a-94e9-4c15-9858-8734cd37d4a7\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>event</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>date_cd</th>\n",
       "      <th>application_id</th>\n",
       "      <th>total_applied</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>576409</td>\n",
       "      <td>StartLoanApply</td>\n",
       "      <td>2022-03-25 11:12:09</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>87171</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>576409</td>\n",
       "      <td>StartLoanApply</td>\n",
       "      <td>2022-03-25 11:12:09</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>87171</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>576409</td>\n",
       "      <td>StartLoanApply</td>\n",
       "      <td>2022-03-25 11:12:09</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>87171</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>576409</td>\n",
       "      <td>StartLoanApply</td>\n",
       "      <td>2022-03-25 11:12:09</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>87171</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>576409</td>\n",
       "      <td>StartLoanApply</td>\n",
       "      <td>2022-03-25 11:12:09</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>87171</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-cfdfcf4a-94e9-4c15-9858-8734cd37d4a7')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-cfdfcf4a-94e9-4c15-9858-8734cd37d4a7 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-cfdfcf4a-94e9-4c15-9858-8734cd37d4a7');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "   user_id           event           timestamp    date_cd  application_id  \\\n",
       "0   576409  StartLoanApply 2022-03-25 11:12:09 2022-03-25           87171   \n",
       "1   576409  StartLoanApply 2022-03-25 11:12:09 2022-03-25           87171   \n",
       "2   576409  StartLoanApply 2022-03-25 11:12:09 2022-03-25           87171   \n",
       "3   576409  StartLoanApply 2022-03-25 11:12:09 2022-03-25           87171   \n",
       "4   576409  StartLoanApply 2022-03-25 11:12:09 2022-03-25           87171   \n",
       "\n",
       "   total_applied  \n",
       "0              0  \n",
       "1              0  \n",
       "2              0  \n",
       "3              0  \n",
       "4              0  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "app_log_merge = pd.merge(left=log_train, right=total_loan[['user_id', 'application_id', 'date_cd', 'total_applied']], on=['user_id', 'date_cd'], how='inner')\n",
    "app_log_merge['total_applied'] = app_log_merge['total_applied'].astype(np.uint8)\n",
    "view_info(app_log_merge)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "FEWSZjGA3kTF"
   },
   "source": [
    "## Split into actual applied vs non-applied"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "m-5xiMtVqXxZ",
    "outputId": "341c7712-e205-43e8-f035-a831ad802e90"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "# of application ids that applied: 377669\n",
      "# of application ids that not applied: 351328\n",
      "2167826\n",
      "2167819\n"
     ]
    }
   ],
   "source": [
    "app_id = apply_group[apply_group['total_applied'] != 0]['application_id'].unique().astype(np.uint32)\n",
    "noapp_id = apply_group[apply_group['total_applied'] == 0]['application_id'].unique().astype(np.uint32)\n",
    "\n",
    "print(f\"# of application ids that applied: {app_id.shape[0]}\")\n",
    "print(f\"# of application ids that not applied: {noapp_id.shape[0]}\")\n",
    "\n",
    "print(app_id.max())\n",
    "print(noapp_id.max())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Aq8ge99w03Ba"
   },
   "outputs": [],
   "source": [
    "actual_log = app_log_merge[app_log_merge['application_id'].isin(app_id)]\n",
    "no_log = app_log_merge[app_log_merge['application_id'].isin(noapp_id)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 492
    },
    "id": "FQYjxO1i1MuB",
    "outputId": "78a60208-515e-46b2-fc96-f62ac10097eb"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(59026370, 6)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 59026370 entries, 42 to 105414946\n",
      "Data columns (total 6 columns):\n",
      " #   Column          Dtype         \n",
      "---  ------          -----         \n",
      " 0   user_id         uint32        \n",
      " 1   event           object        \n",
      " 2   timestamp       datetime64[ns]\n",
      " 3   date_cd         datetime64[ns]\n",
      " 4   application_id  uint32        \n",
      " 5   total_applied   uint8         \n",
      "dtypes: datetime64[ns](2), object(1), uint32(2), uint8(1)\n",
      "memory usage: 2.3+ GB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-a74fdc0f-c7a3-4570-b687-a313d76bb4ae\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>event</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>date_cd</th>\n",
       "      <th>application_id</th>\n",
       "      <th>total_applied</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>72878</td>\n",
       "      <td>EndLoanApply</td>\n",
       "      <td>2022-03-25 11:14:44</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>871260</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>72878</td>\n",
       "      <td>EndLoanApply</td>\n",
       "      <td>2022-03-25 11:14:44</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>871260</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>72878</td>\n",
       "      <td>EndLoanApply</td>\n",
       "      <td>2022-03-25 11:14:44</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>871260</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>72878</td>\n",
       "      <td>EndLoanApply</td>\n",
       "      <td>2022-03-25 11:14:44</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>871260</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>72878</td>\n",
       "      <td>EndLoanApply</td>\n",
       "      <td>2022-03-25 11:14:44</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>871260</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-a74fdc0f-c7a3-4570-b687-a313d76bb4ae')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-a74fdc0f-c7a3-4570-b687-a313d76bb4ae button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-a74fdc0f-c7a3-4570-b687-a313d76bb4ae');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "    user_id         event           timestamp    date_cd  application_id  \\\n",
       "42    72878  EndLoanApply 2022-03-25 11:14:44 2022-03-25          871260   \n",
       "43    72878  EndLoanApply 2022-03-25 11:14:44 2022-03-25          871260   \n",
       "44    72878  EndLoanApply 2022-03-25 11:14:44 2022-03-25          871260   \n",
       "45    72878  EndLoanApply 2022-03-25 11:14:44 2022-03-25          871260   \n",
       "46    72878  EndLoanApply 2022-03-25 11:14:44 2022-03-25          871260   \n",
       "\n",
       "    total_applied  \n",
       "42              2  \n",
       "43              2  \n",
       "44              2  \n",
       "45              2  \n",
       "46              2  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "view_info(actual_log)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 492
    },
    "id": "WeK6QyjbqyEg",
    "outputId": "5d2d9bf7-6181-4bd6-db31-6923c38de6d6"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(46388577, 6)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 46388577 entries, 0 to 105414922\n",
      "Data columns (total 6 columns):\n",
      " #   Column          Dtype         \n",
      "---  ------          -----         \n",
      " 0   user_id         uint32        \n",
      " 1   event           object        \n",
      " 2   timestamp       datetime64[ns]\n",
      " 3   date_cd         datetime64[ns]\n",
      " 4   application_id  uint32        \n",
      " 5   total_applied   uint8         \n",
      "dtypes: datetime64[ns](2), object(1), uint32(2), uint8(1)\n",
      "memory usage: 1.8+ GB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-6c95972f-5323-412d-8495-f4481630cc63\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>event</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>date_cd</th>\n",
       "      <th>application_id</th>\n",
       "      <th>total_applied</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>576409</td>\n",
       "      <td>StartLoanApply</td>\n",
       "      <td>2022-03-25 11:12:09</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>87171</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>576409</td>\n",
       "      <td>StartLoanApply</td>\n",
       "      <td>2022-03-25 11:12:09</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>87171</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>576409</td>\n",
       "      <td>StartLoanApply</td>\n",
       "      <td>2022-03-25 11:12:09</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>87171</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>576409</td>\n",
       "      <td>StartLoanApply</td>\n",
       "      <td>2022-03-25 11:12:09</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>87171</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>576409</td>\n",
       "      <td>StartLoanApply</td>\n",
       "      <td>2022-03-25 11:12:09</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>87171</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-6c95972f-5323-412d-8495-f4481630cc63')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-6c95972f-5323-412d-8495-f4481630cc63 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-6c95972f-5323-412d-8495-f4481630cc63');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "   user_id           event           timestamp    date_cd  application_id  \\\n",
       "0   576409  StartLoanApply 2022-03-25 11:12:09 2022-03-25           87171   \n",
       "1   576409  StartLoanApply 2022-03-25 11:12:09 2022-03-25           87171   \n",
       "2   576409  StartLoanApply 2022-03-25 11:12:09 2022-03-25           87171   \n",
       "3   576409  StartLoanApply 2022-03-25 11:12:09 2022-03-25           87171   \n",
       "4   576409  StartLoanApply 2022-03-25 11:12:09 2022-03-25           87171   \n",
       "\n",
       "   total_applied  \n",
       "0              0  \n",
       "1              0  \n",
       "2              0  \n",
       "3              0  \n",
       "4              0  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "view_info(no_log)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "c_ZBaQHJ--S8",
    "outputId": "72c0f942-9d60-401f-b485-484ecbe1052b"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "752"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del app_log_merge\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "HCivDEAe36i5"
   },
   "source": [
    "## Get path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "ClLZmYmLrHju"
   },
   "outputs": [],
   "source": [
    "def get_path(df:pd.DataFrame) -> pd.DataFrame:\n",
    "    df.sort_values(by=['application_id', 'timestamp'], inplace=True)\n",
    "    event_tuples = dict()\n",
    "    for appid in tqdm(df['application_id'].unique()):\n",
    "        subdf = df[df['application_id'] == appid]\n",
    "        for dt in subdf['date_cd'].unique():\n",
    "            subsubdf = subdf[subdf['date_cd'] == dt]\n",
    "            events = tuple(subsubdf['event'])\n",
    "            if events not in event_tuples:\n",
    "                event_tuples[events] = 1\n",
    "            else:\n",
    "                event_tuples[events] += 1\n",
    "\n",
    "    result = pd.DataFrame({'path': event_tuples.keys(), 'count': event_tuples.values()})\n",
    "\n",
    "    return result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "5WQekpddrmZ0"
   },
   "outputs": [],
   "source": [
    "app_path = get_path(actual_log)\n",
    "app_path.to_csv(\"../Data/path_applied_user.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "414GxAqd9kCt"
   },
   "outputs": [],
   "source": [
    "noapp_path = get_path(no_log)\n",
    "noapp_path.to_csv(\"../Data/path_no_applied_user.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "6fxRhXPvmEpD"
   },
   "outputs": [],
   "source": [
    "app_path.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "rwT3zrWuvQW6"
   },
   "outputs": [],
   "source": [
    "app_path.sort_values(by='count', ascending=False, inplace=True)\n",
    "app_path.reset_index(drop=True, inplace=True)\n",
    "for i in range(20):\n",
    "    path, count = app_path.head(20).iloc[i]\n",
    "    print(f\"{path}\\t{count}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "FdY_XSzx5lZr"
   },
   "outputs": [],
   "source": [
    "noapp_path.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "9p2GeHf55lZr"
   },
   "outputs": [],
   "source": [
    "noapp_path.sort_values(by='count', ascending=False, inplace=True)\n",
    "noapp_path.reset_index(drop=True, inplace=True)\n",
    "for i in range(20):\n",
    "    path, count = noapp_path.head(20).iloc[i]\n",
    "    print(f\"{path}\\t{count}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "BbG5oeJZ4Dm-"
   },
   "source": [
    "## Merge loan path group and application id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "7JUibSSa4G88"
   },
   "outputs": [],
   "source": [
    "app_user = {user['application_id'].iloc[i]: user['user_id'].iloc[i] for i in range(user.shape[0])}\n",
    "loan['user_id'] = loan['application_id'].map(app_user)\n",
    "loan.dropna(subset=['user_id'], inplace=True)\n",
    "loan['user_id'] = loan['user_id'].astype(np.uint32)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 424
    },
    "id": "cwGmUWWh3UUp",
    "outputId": "eac16629-edfe-45a4-9973-1f4a504048c3"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-6a56da12-1eb8-49d9-a8f9-87d64d226f10\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>application_id</th>\n",
       "      <th>loanapply_insert_time</th>\n",
       "      <th>is_applied</th>\n",
       "      <th>user_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13527358</th>\n",
       "      <td>1428218</td>\n",
       "      <td>2022-06-03 12:01:58</td>\n",
       "      <td>NaN</td>\n",
       "      <td>478802</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13527359</th>\n",
       "      <td>1428218</td>\n",
       "      <td>2022-06-03 12:01:49</td>\n",
       "      <td>NaN</td>\n",
       "      <td>478802</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13527360</th>\n",
       "      <td>1428218</td>\n",
       "      <td>2022-06-03 12:01:48</td>\n",
       "      <td>NaN</td>\n",
       "      <td>478802</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13527361</th>\n",
       "      <td>1428218</td>\n",
       "      <td>2022-06-03 12:01:48</td>\n",
       "      <td>NaN</td>\n",
       "      <td>478802</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13527362</th>\n",
       "      <td>1428218</td>\n",
       "      <td>2022-06-03 12:01:48</td>\n",
       "      <td>NaN</td>\n",
       "      <td>478802</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>13527250 rows × 4 columns</p>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-6a56da12-1eb8-49d9-a8f9-87d64d226f10')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-6a56da12-1eb8-49d9-a8f9-87d64d226f10 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-6a56da12-1eb8-49d9-a8f9-87d64d226f10');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "          application_id loanapply_insert_time  is_applied  user_id\n",
       "0                1748340   2022-06-07 13:05:41         NaN   430982\n",
       "1                1748340   2022-06-07 13:05:41         NaN   430982\n",
       "2                1748340   2022-06-07 13:05:41         NaN   430982\n",
       "3                1748340   2022-06-07 13:05:41         NaN   430982\n",
       "4                1748340   2022-06-07 13:05:41         NaN   430982\n",
       "...                  ...                   ...         ...      ...\n",
       "13527358         1428218   2022-06-03 12:01:58         NaN   478802\n",
       "13527359         1428218   2022-06-03 12:01:49         NaN   478802\n",
       "13527360         1428218   2022-06-03 12:01:48         NaN   478802\n",
       "13527361         1428218   2022-06-03 12:01:48         NaN   478802\n",
       "13527362         1428218   2022-06-03 12:01:48         NaN   478802\n",
       "\n",
       "[13527250 rows x 4 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 484
    },
    "id": "B5E80Xmb5BZa",
    "outputId": "e369595d-141a-4242-ea09-a13f0ef2673c"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1740729, 6)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1740729 entries, 0 to 1740728\n",
      "Data columns (total 6 columns):\n",
      " #   Column          Dtype  \n",
      "---  ------          -----  \n",
      " 0   application_id  float64\n",
      " 1   path            object \n",
      " 2   date_cd         object \n",
      " 3   embeddings      object \n",
      " 4   user_id         int64  \n",
      " 5   labels          int64  \n",
      "dtypes: float64(1), int64(2), object(3)\n",
      "memory usage: 79.7+ MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-c23e0cb0-779c-49b0-aa44-1cba40d0fbee\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>application_id</th>\n",
       "      <th>path</th>\n",
       "      <th>date_cd</th>\n",
       "      <th>embeddings</th>\n",
       "      <th>user_id</th>\n",
       "      <th>labels</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>871260.0</td>\n",
       "      <td>('Login', 'StartLoanApply', 'ViewLoanApplyIntr...</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>[ 0.00453116  0.00014933 -0.00333886  0.007471...</td>\n",
       "      <td>72878</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>94017.0</td>\n",
       "      <td>('OpenApp', 'Login', 'StartLoanApply', 'ViewLo...</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>[ 0.0479816   0.11158732 -0.05752625 -0.063861...</td>\n",
       "      <td>618857</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1482636.0</td>\n",
       "      <td>('OpenApp', 'Login', 'StartLoanApply', 'ViewLo...</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>[ 0.0479816   0.11158732 -0.05752625 -0.063861...</td>\n",
       "      <td>618857</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1345169.0</td>\n",
       "      <td>('OpenApp', 'Login', 'StartLoanApply', 'ViewLo...</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>[ 0.00314156  0.09357454 -0.04824954 -0.061293...</td>\n",
       "      <td>496477</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>75852.0</td>\n",
       "      <td>('OpenApp', 'Login', 'UseLoanManage', 'GetCred...</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>[-0.00634183  0.07155684 -0.05163227 -0.045615...</td>\n",
       "      <td>217308</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-c23e0cb0-779c-49b0-aa44-1cba40d0fbee')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-c23e0cb0-779c-49b0-aa44-1cba40d0fbee button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-c23e0cb0-779c-49b0-aa44-1cba40d0fbee');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "   application_id                                               path  \\\n",
       "0        871260.0  ('Login', 'StartLoanApply', 'ViewLoanApplyIntr...   \n",
       "1         94017.0  ('OpenApp', 'Login', 'StartLoanApply', 'ViewLo...   \n",
       "2       1482636.0  ('OpenApp', 'Login', 'StartLoanApply', 'ViewLo...   \n",
       "3       1345169.0  ('OpenApp', 'Login', 'StartLoanApply', 'ViewLo...   \n",
       "4         75852.0  ('OpenApp', 'Login', 'UseLoanManage', 'GetCred...   \n",
       "\n",
       "      date_cd                                         embeddings  user_id  \\\n",
       "0  2022-03-25  [ 0.00453116  0.00014933 -0.00333886  0.007471...    72878   \n",
       "1  2022-03-25  [ 0.0479816   0.11158732 -0.05752625 -0.063861...   618857   \n",
       "2  2022-03-25  [ 0.0479816   0.11158732 -0.05752625 -0.063861...   618857   \n",
       "3  2022-03-25  [ 0.00314156  0.09357454 -0.04824954 -0.061293...   496477   \n",
       "4  2022-03-25  [-0.00634183  0.07155684 -0.05163227 -0.045615...   217308   \n",
       "\n",
       "   labels  \n",
       "0       3  \n",
       "1       0  \n",
       "2       0  \n",
       "3       4  \n",
       "4       1  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "path_group = pd.read_csv(\"../Data/path_group.csv\", engine='c', low_memory=True)\n",
    "view_info(path_group)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 951
    },
    "id": "XgTG3eKwED_H",
    "outputId": "91a9bfcb-7f69-4049-ba6a-94f303deb106"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1060733, 6)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1060733 entries, 679996 to 1740728\n",
      "Data columns (total 6 columns):\n",
      " #   Column          Non-Null Count    Dtype  \n",
      "---  ------          --------------    -----  \n",
      " 0   application_id  0 non-null        float64\n",
      " 1   path            1060733 non-null  object \n",
      " 2   date_cd         1060733 non-null  object \n",
      " 3   embeddings      1060733 non-null  object \n",
      " 4   user_id         1060733 non-null  int64  \n",
      " 5   labels          1060733 non-null  int64  \n",
      "dtypes: float64(1), int64(2), object(3)\n",
      "memory usage: 56.6+ MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-5ad832c7-6e27-4aa3-89bb-ab329ff077ba\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>application_id</th>\n",
       "      <th>path</th>\n",
       "      <th>date_cd</th>\n",
       "      <th>embeddings</th>\n",
       "      <th>user_id</th>\n",
       "      <th>labels</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>679996</th>\n",
       "      <td>NaN</td>\n",
       "      <td>('StartLoanApply', 'ViewLoanApplyIntro', 'Comp...</td>\n",
       "      <td>2022-03-24</td>\n",
       "      <td>[ 0.03146735  0.1536676  -0.08643018 -0.074777...</td>\n",
       "      <td>808079</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>679997</th>\n",
       "      <td>NaN</td>\n",
       "      <td>('OpenApp', 'OpenApp', 'StartLoanApply', 'View...</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>[ 0.01104167  0.04542439 -0.02805091 -0.040920...</td>\n",
       "      <td>808079</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>679998</th>\n",
       "      <td>NaN</td>\n",
       "      <td>('OpenApp', 'StartLoanApply', 'ViewLoanApplyIn...</td>\n",
       "      <td>2022-03-26</td>\n",
       "      <td>[ 0.01934403  0.10600652 -0.05335192 -0.065669...</td>\n",
       "      <td>808079</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>679999</th>\n",
       "      <td>NaN</td>\n",
       "      <td>('OpenApp', 'StartLoanApply', 'ViewLoanApplyIn...</td>\n",
       "      <td>2022-03-27</td>\n",
       "      <td>[ 0.01934403  0.10600652 -0.05335192 -0.065669...</td>\n",
       "      <td>808079</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>680000</th>\n",
       "      <td>NaN</td>\n",
       "      <td>('OpenApp', 'GetCreditInfo', 'UseLoanManage')</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>[ 0.02640858  0.05666207 -0.03640145 -0.016879...</td>\n",
       "      <td>808079</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-5ad832c7-6e27-4aa3-89bb-ab329ff077ba')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-5ad832c7-6e27-4aa3-89bb-ab329ff077ba button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-5ad832c7-6e27-4aa3-89bb-ab329ff077ba');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "        application_id                                               path  \\\n",
       "679996             NaN  ('StartLoanApply', 'ViewLoanApplyIntro', 'Comp...   \n",
       "679997             NaN  ('OpenApp', 'OpenApp', 'StartLoanApply', 'View...   \n",
       "679998             NaN  ('OpenApp', 'StartLoanApply', 'ViewLoanApplyIn...   \n",
       "679999             NaN  ('OpenApp', 'StartLoanApply', 'ViewLoanApplyIn...   \n",
       "680000             NaN      ('OpenApp', 'GetCreditInfo', 'UseLoanManage')   \n",
       "\n",
       "           date_cd                                         embeddings  \\\n",
       "679996  2022-03-24  [ 0.03146735  0.1536676  -0.08643018 -0.074777...   \n",
       "679997  2022-03-25  [ 0.01104167  0.04542439 -0.02805091 -0.040920...   \n",
       "679998  2022-03-26  [ 0.01934403  0.10600652 -0.05335192 -0.065669...   \n",
       "679999  2022-03-27  [ 0.01934403  0.10600652 -0.05335192 -0.065669...   \n",
       "680000  2022-03-29  [ 0.02640858  0.05666207 -0.03640145 -0.016879...   \n",
       "\n",
       "        user_id  labels  \n",
       "679996   808079       0  \n",
       "679997   808079       1  \n",
       "679998   808079       4  \n",
       "679999   808079       4  \n",
       "680000   808079       4  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(679996, 6)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 679996 entries, 0 to 679995\n",
      "Data columns (total 6 columns):\n",
      " #   Column          Non-Null Count   Dtype  \n",
      "---  ------          --------------   -----  \n",
      " 0   application_id  679996 non-null  float64\n",
      " 1   path            679996 non-null  object \n",
      " 2   date_cd         679996 non-null  object \n",
      " 3   embeddings      679996 non-null  object \n",
      " 4   user_id         679996 non-null  int64  \n",
      " 5   labels          679996 non-null  int64  \n",
      "dtypes: float64(1), int64(2), object(3)\n",
      "memory usage: 36.3+ MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-29aa4972-fb47-4638-b9e4-b5c950ae49f3\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>application_id</th>\n",
       "      <th>path</th>\n",
       "      <th>date_cd</th>\n",
       "      <th>embeddings</th>\n",
       "      <th>user_id</th>\n",
       "      <th>labels</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>871260.0</td>\n",
       "      <td>('Login', 'StartLoanApply', 'ViewLoanApplyIntr...</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>[ 0.00453116  0.00014933 -0.00333886  0.007471...</td>\n",
       "      <td>72878</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>94017.0</td>\n",
       "      <td>('OpenApp', 'Login', 'StartLoanApply', 'ViewLo...</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>[ 0.0479816   0.11158732 -0.05752625 -0.063861...</td>\n",
       "      <td>618857</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1482636.0</td>\n",
       "      <td>('OpenApp', 'Login', 'StartLoanApply', 'ViewLo...</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>[ 0.0479816   0.11158732 -0.05752625 -0.063861...</td>\n",
       "      <td>618857</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1345169.0</td>\n",
       "      <td>('OpenApp', 'Login', 'StartLoanApply', 'ViewLo...</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>[ 0.00314156  0.09357454 -0.04824954 -0.061293...</td>\n",
       "      <td>496477</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>75852.0</td>\n",
       "      <td>('OpenApp', 'Login', 'UseLoanManage', 'GetCred...</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>[-0.00634183  0.07155684 -0.05163227 -0.045615...</td>\n",
       "      <td>217308</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-29aa4972-fb47-4638-b9e4-b5c950ae49f3')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-29aa4972-fb47-4638-b9e4-b5c950ae49f3 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-29aa4972-fb47-4638-b9e4-b5c950ae49f3');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "   application_id                                               path  \\\n",
       "0        871260.0  ('Login', 'StartLoanApply', 'ViewLoanApplyIntr...   \n",
       "1         94017.0  ('OpenApp', 'Login', 'StartLoanApply', 'ViewLo...   \n",
       "2       1482636.0  ('OpenApp', 'Login', 'StartLoanApply', 'ViewLo...   \n",
       "3       1345169.0  ('OpenApp', 'Login', 'StartLoanApply', 'ViewLo...   \n",
       "4         75852.0  ('OpenApp', 'Login', 'UseLoanManage', 'GetCred...   \n",
       "\n",
       "      date_cd                                         embeddings  user_id  \\\n",
       "0  2022-03-25  [ 0.00453116  0.00014933 -0.00333886  0.007471...    72878   \n",
       "1  2022-03-25  [ 0.0479816   0.11158732 -0.05752625 -0.063861...   618857   \n",
       "2  2022-03-25  [ 0.0479816   0.11158732 -0.05752625 -0.063861...   618857   \n",
       "3  2022-03-25  [ 0.00314156  0.09357454 -0.04824954 -0.061293...   496477   \n",
       "4  2022-03-25  [-0.00634183  0.07155684 -0.05163227 -0.045615...   217308   \n",
       "\n",
       "   labels  \n",
       "0       3  \n",
       "1       0  \n",
       "2       0  \n",
       "3       4  \n",
       "4       1  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "null = path_group[path_group['application_id'].isna()]\n",
    "no_null = path_group[~path_group['application_id'].isna()]\n",
    "\n",
    "view_info(null)\n",
    "view_info(no_null)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "EHzlcYIHEjMU",
    "outputId": "57c31218-77ea-40fd-9379-33864fbb19f7"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python3.7/dist-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "no_null['application_id'] = no_null['application_id'].astype('uint32')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 467
    },
    "id": "giIsqrPq5o7g",
    "outputId": "1a98a51f-4226-4c1a-8634-2df3133e6625"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(13545171, 5)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 13545171 entries, 0 to 13545170\n",
      "Data columns (total 5 columns):\n",
      " #   Column                 Dtype         \n",
      "---  ------                 -----         \n",
      " 0   application_id         uint32        \n",
      " 1   loanapply_insert_time  datetime64[ns]\n",
      " 2   is_applied             float64       \n",
      " 3   user_id                uint32        \n",
      " 4   labels                 float64       \n",
      "dtypes: datetime64[ns](1), float64(2), uint32(2)\n",
      "memory usage: 516.7 MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-2f383dc1-545e-448e-a2f5-7413828e6d37\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>application_id</th>\n",
       "      <th>loanapply_insert_time</th>\n",
       "      <th>is_applied</th>\n",
       "      <th>user_id</th>\n",
       "      <th>labels</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-2f383dc1-545e-448e-a2f5-7413828e6d37')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-2f383dc1-545e-448e-a2f5-7413828e6d37 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-2f383dc1-545e-448e-a2f5-7413828e6d37');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "   application_id loanapply_insert_time  is_applied  user_id  labels\n",
       "0         1748340   2022-06-07 13:05:41         NaN   430982     NaN\n",
       "1         1748340   2022-06-07 13:05:41         NaN   430982     NaN\n",
       "2         1748340   2022-06-07 13:05:41         NaN   430982     NaN\n",
       "3         1748340   2022-06-07 13:05:41         NaN   430982     NaN\n",
       "4         1748340   2022-06-07 13:05:41         NaN   430982     NaN"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "loan_ = pd.merge(left=loan, right=no_null[['application_id', 'labels']], how='left', on='application_id')\n",
    "view_info(loan_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 675
    },
    "id": "5mYngQUCv_E5",
    "outputId": "66953297-5575-4b08-aa2d-0dd1971e004d"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1060733, 6)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1060733 entries, 679996 to 1740728\n",
      "Data columns (total 6 columns):\n",
      " #   Column          Non-Null Count    Dtype  \n",
      "---  ------          --------------    -----  \n",
      " 0   application_id  0 non-null        float64\n",
      " 1   path            1060733 non-null  object \n",
      " 2   date_cd         1060733 non-null  object \n",
      " 3   embeddings      1060733 non-null  object \n",
      " 4   user_id         1060733 non-null  int64  \n",
      " 5   labels_x        1060733 non-null  int64  \n",
      "dtypes: float64(1), int64(2), object(3)\n",
      "memory usage: 56.6+ MB\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python3.7/dist-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n",
      "/usr/local/lib/python3.7/dist-packages/pandas/core/frame.py:4913: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  errors=errors,\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-e0cc6818-9b09-45c6-a594-61f6ca3e8b8b\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>application_id</th>\n",
       "      <th>path</th>\n",
       "      <th>date_cd</th>\n",
       "      <th>embeddings</th>\n",
       "      <th>user_id</th>\n",
       "      <th>labels_x</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>679996</th>\n",
       "      <td>NaN</td>\n",
       "      <td>('StartLoanApply', 'ViewLoanApplyIntro', 'Comp...</td>\n",
       "      <td>2022-03-24</td>\n",
       "      <td>[ 0.03146735  0.1536676  -0.08643018 -0.074777...</td>\n",
       "      <td>808079</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>679997</th>\n",
       "      <td>NaN</td>\n",
       "      <td>('OpenApp', 'OpenApp', 'StartLoanApply', 'View...</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>[ 0.01104167  0.04542439 -0.02805091 -0.040920...</td>\n",
       "      <td>808079</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>679998</th>\n",
       "      <td>NaN</td>\n",
       "      <td>('OpenApp', 'StartLoanApply', 'ViewLoanApplyIn...</td>\n",
       "      <td>2022-03-26</td>\n",
       "      <td>[ 0.01934403  0.10600652 -0.05335192 -0.065669...</td>\n",
       "      <td>808079</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>679999</th>\n",
       "      <td>NaN</td>\n",
       "      <td>('OpenApp', 'StartLoanApply', 'ViewLoanApplyIn...</td>\n",
       "      <td>2022-03-27</td>\n",
       "      <td>[ 0.01934403  0.10600652 -0.05335192 -0.065669...</td>\n",
       "      <td>808079</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>680000</th>\n",
       "      <td>NaN</td>\n",
       "      <td>('OpenApp', 'GetCreditInfo', 'UseLoanManage')</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>[ 0.02640858  0.05666207 -0.03640145 -0.016879...</td>\n",
       "      <td>808079</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-e0cc6818-9b09-45c6-a594-61f6ca3e8b8b')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-e0cc6818-9b09-45c6-a594-61f6ca3e8b8b button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-e0cc6818-9b09-45c6-a594-61f6ca3e8b8b');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "        application_id                                               path  \\\n",
       "679996             NaN  ('StartLoanApply', 'ViewLoanApplyIntro', 'Comp...   \n",
       "679997             NaN  ('OpenApp', 'OpenApp', 'StartLoanApply', 'View...   \n",
       "679998             NaN  ('OpenApp', 'StartLoanApply', 'ViewLoanApplyIn...   \n",
       "679999             NaN  ('OpenApp', 'StartLoanApply', 'ViewLoanApplyIn...   \n",
       "680000             NaN      ('OpenApp', 'GetCreditInfo', 'UseLoanManage')   \n",
       "\n",
       "           date_cd                                         embeddings  \\\n",
       "679996  2022-03-24  [ 0.03146735  0.1536676  -0.08643018 -0.074777...   \n",
       "679997  2022-03-25  [ 0.01104167  0.04542439 -0.02805091 -0.040920...   \n",
       "679998  2022-03-26  [ 0.01934403  0.10600652 -0.05335192 -0.065669...   \n",
       "679999  2022-03-27  [ 0.01934403  0.10600652 -0.05335192 -0.065669...   \n",
       "680000  2022-03-29  [ 0.02640858  0.05666207 -0.03640145 -0.016879...   \n",
       "\n",
       "        user_id  labels_x  \n",
       "679996   808079         0  \n",
       "679997   808079         1  \n",
       "679998   808079         4  \n",
       "679999   808079         4  \n",
       "680000   808079         4  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "null['labels_x'] = null['labels'].copy()\n",
    "null.drop('labels', axis=1, inplace=True)\n",
    "view_info(null)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 484
    },
    "id": "TyG9N6bvw1No",
    "outputId": "83da2d85-5172-41af-a9b3-f87274b25ce5"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(13545171, 6)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 13545171 entries, 0 to 13545170\n",
      "Data columns (total 6 columns):\n",
      " #   Column                 Dtype         \n",
      "---  ------                 -----         \n",
      " 0   application_id         uint32        \n",
      " 1   loanapply_insert_time  datetime64[ns]\n",
      " 2   is_applied             float64       \n",
      " 3   user_id                uint32        \n",
      " 4   labels                 float64       \n",
      " 5   date_cd                datetime64[ns]\n",
      "dtypes: datetime64[ns](2), float64(2), uint32(2)\n",
      "memory usage: 620.0 MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-edfa870c-647c-41bb-abcd-f0f1dbeeee38\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>application_id</th>\n",
       "      <th>loanapply_insert_time</th>\n",
       "      <th>is_applied</th>\n",
       "      <th>user_id</th>\n",
       "      <th>labels</th>\n",
       "      <th>date_cd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-edfa870c-647c-41bb-abcd-f0f1dbeeee38')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-edfa870c-647c-41bb-abcd-f0f1dbeeee38 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-edfa870c-647c-41bb-abcd-f0f1dbeeee38');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "   application_id loanapply_insert_time  is_applied  user_id  labels  \\\n",
       "0         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "1         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "2         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "3         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "4         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "\n",
       "     date_cd  \n",
       "0 2022-06-07  \n",
       "1 2022-06-07  \n",
       "2 2022-06-07  \n",
       "3 2022-06-07  \n",
       "4 2022-06-07  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "loan_['date_cd'] = pd.to_datetime(loan_['loanapply_insert_time'].dt.date)\n",
    "view_info(loan_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 588
    },
    "id": "QFUXcFnBxI9C",
    "outputId": "4f377d47-4462-47b8-bcbb-ca498dcc696e"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1060733, 6)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1060733 entries, 679996 to 1740728\n",
      "Data columns (total 6 columns):\n",
      " #   Column          Non-Null Count    Dtype         \n",
      "---  ------          --------------    -----         \n",
      " 0   application_id  0 non-null        float64       \n",
      " 1   path            1060733 non-null  object        \n",
      " 2   date_cd         1060733 non-null  datetime64[ns]\n",
      " 3   embeddings      1060733 non-null  object        \n",
      " 4   user_id         1060733 non-null  int64         \n",
      " 5   labels_x        1060733 non-null  int64         \n",
      "dtypes: datetime64[ns](1), float64(1), int64(2), object(2)\n",
      "memory usage: 56.6+ MB\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/lib/python3.7/dist-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-87f2d78c-53fd-4c39-9d9b-51bbc6eb36bd\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>application_id</th>\n",
       "      <th>path</th>\n",
       "      <th>date_cd</th>\n",
       "      <th>embeddings</th>\n",
       "      <th>user_id</th>\n",
       "      <th>labels_x</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>679996</th>\n",
       "      <td>NaN</td>\n",
       "      <td>('StartLoanApply', 'ViewLoanApplyIntro', 'Comp...</td>\n",
       "      <td>2022-03-24</td>\n",
       "      <td>[ 0.03146735  0.1536676  -0.08643018 -0.074777...</td>\n",
       "      <td>808079</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>679997</th>\n",
       "      <td>NaN</td>\n",
       "      <td>('OpenApp', 'OpenApp', 'StartLoanApply', 'View...</td>\n",
       "      <td>2022-03-25</td>\n",
       "      <td>[ 0.01104167  0.04542439 -0.02805091 -0.040920...</td>\n",
       "      <td>808079</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>679998</th>\n",
       "      <td>NaN</td>\n",
       "      <td>('OpenApp', 'StartLoanApply', 'ViewLoanApplyIn...</td>\n",
       "      <td>2022-03-26</td>\n",
       "      <td>[ 0.01934403  0.10600652 -0.05335192 -0.065669...</td>\n",
       "      <td>808079</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>679999</th>\n",
       "      <td>NaN</td>\n",
       "      <td>('OpenApp', 'StartLoanApply', 'ViewLoanApplyIn...</td>\n",
       "      <td>2022-03-27</td>\n",
       "      <td>[ 0.01934403  0.10600652 -0.05335192 -0.065669...</td>\n",
       "      <td>808079</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>680000</th>\n",
       "      <td>NaN</td>\n",
       "      <td>('OpenApp', 'GetCreditInfo', 'UseLoanManage')</td>\n",
       "      <td>2022-03-29</td>\n",
       "      <td>[ 0.02640858  0.05666207 -0.03640145 -0.016879...</td>\n",
       "      <td>808079</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-87f2d78c-53fd-4c39-9d9b-51bbc6eb36bd')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-87f2d78c-53fd-4c39-9d9b-51bbc6eb36bd button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-87f2d78c-53fd-4c39-9d9b-51bbc6eb36bd');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "        application_id                                               path  \\\n",
       "679996             NaN  ('StartLoanApply', 'ViewLoanApplyIntro', 'Comp...   \n",
       "679997             NaN  ('OpenApp', 'OpenApp', 'StartLoanApply', 'View...   \n",
       "679998             NaN  ('OpenApp', 'StartLoanApply', 'ViewLoanApplyIn...   \n",
       "679999             NaN  ('OpenApp', 'StartLoanApply', 'ViewLoanApplyIn...   \n",
       "680000             NaN      ('OpenApp', 'GetCreditInfo', 'UseLoanManage')   \n",
       "\n",
       "          date_cd                                         embeddings  user_id  \\\n",
       "679996 2022-03-24  [ 0.03146735  0.1536676  -0.08643018 -0.074777...   808079   \n",
       "679997 2022-03-25  [ 0.01104167  0.04542439 -0.02805091 -0.040920...   808079   \n",
       "679998 2022-03-26  [ 0.01934403  0.10600652 -0.05335192 -0.065669...   808079   \n",
       "679999 2022-03-27  [ 0.01934403  0.10600652 -0.05335192 -0.065669...   808079   \n",
       "680000 2022-03-29  [ 0.02640858  0.05666207 -0.03640145 -0.016879...   808079   \n",
       "\n",
       "        labels_x  \n",
       "679996         0  \n",
       "679997         1  \n",
       "679998         4  \n",
       "679999         4  \n",
       "680000         4  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "null['date_cd'] = pd.to_datetime(null['date_cd'])\n",
    "view_info(null)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 501
    },
    "id": "BsfCNcB-wcxL",
    "outputId": "8af50105-edb1-4ca3-f44e-7d276cdf33e5"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(13545171, 7)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 13545171 entries, 0 to 13545170\n",
      "Data columns (total 7 columns):\n",
      " #   Column                 Dtype         \n",
      "---  ------                 -----         \n",
      " 0   application_id         uint32        \n",
      " 1   loanapply_insert_time  datetime64[ns]\n",
      " 2   is_applied             float64       \n",
      " 3   user_id                uint32        \n",
      " 4   labels                 float64       \n",
      " 5   date_cd                datetime64[ns]\n",
      " 6   labels_x               float64       \n",
      "dtypes: datetime64[ns](2), float64(3), uint32(2)\n",
      "memory usage: 723.4 MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-315d399d-6e1c-48db-9786-ef62c367c359\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>application_id</th>\n",
       "      <th>loanapply_insert_time</th>\n",
       "      <th>is_applied</th>\n",
       "      <th>user_id</th>\n",
       "      <th>labels</th>\n",
       "      <th>date_cd</th>\n",
       "      <th>labels_x</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-07</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-07</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-07</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-07</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-07</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-315d399d-6e1c-48db-9786-ef62c367c359')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-315d399d-6e1c-48db-9786-ef62c367c359 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-315d399d-6e1c-48db-9786-ef62c367c359');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "   application_id loanapply_insert_time  is_applied  user_id  labels  \\\n",
       "0         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "1         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "2         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "3         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "4         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "\n",
       "     date_cd  labels_x  \n",
       "0 2022-06-07       NaN  \n",
       "1 2022-06-07       NaN  \n",
       "2 2022-06-07       NaN  \n",
       "3 2022-06-07       NaN  \n",
       "4 2022-06-07       NaN  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "loan_ = pd.merge(left=loan_, right=null[['user_id', 'date_cd', 'labels_x']], how='left', on=['user_id', 'date_cd'])\n",
    "view_info(loan_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "92uK4qt0ckLp"
   },
   "outputs": [],
   "source": [
    "loan_.drop('date_cd', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "aIRlQg1Uxoij",
    "outputId": "b99da79e-5b4a-4422-bc0e-40ae539f5e04"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "13545171"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(loan_['labels'] != loan_['labels_x']).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "q822_twmx3-C",
    "outputId": "5d9f793f-3762-400c-8d02-d35daaeb9573"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "application_id                  0\n",
       "loanapply_insert_time           0\n",
       "is_applied                3257239\n",
       "user_id                         0\n",
       "labels                    4076082\n",
       "labels_x                 12178195\n",
       "dtype: int64"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 363
    },
    "id": "59JyokjBdSBF",
    "outputId": "b93c36f4-5f32-49c3-cc59-29e57e96c9f2"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-d203b199-20cf-4785-a3a4-e1fb343aa505\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>application_id</th>\n",
       "      <th>loanapply_insert_time</th>\n",
       "      <th>is_applied</th>\n",
       "      <th>user_id</th>\n",
       "      <th>labels</th>\n",
       "      <th>labels_x</th>\n",
       "      <th>labels_isnull</th>\n",
       "      <th>labels_x_isnull</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:40</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:40</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:46</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:51</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-d203b199-20cf-4785-a3a4-e1fb343aa505')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-d203b199-20cf-4785-a3a4-e1fb343aa505 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-d203b199-20cf-4785-a3a4-e1fb343aa505');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "   application_id loanapply_insert_time  is_applied  user_id  labels  \\\n",
       "0         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "1         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "2         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "3         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "4         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "5         1748340   2022-06-07 13:05:40         NaN   430982     NaN   \n",
       "6         1748340   2022-06-07 13:05:40         NaN   430982     NaN   \n",
       "7         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "8         1748340   2022-06-07 13:05:46         NaN   430982     NaN   \n",
       "9         1748340   2022-06-07 13:05:51         NaN   430982     NaN   \n",
       "\n",
       "   labels_x  labels_isnull  labels_x_isnull  \n",
       "0       NaN           True             True  \n",
       "1       NaN           True             True  \n",
       "2       NaN           True             True  \n",
       "3       NaN           True             True  \n",
       "4       NaN           True             True  \n",
       "5       NaN           True             True  \n",
       "6       NaN           True             True  \n",
       "7       NaN           True             True  \n",
       "8       NaN           True             True  \n",
       "9       NaN           True             True  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_['labels_isnull'] = loan_['labels'].isnull()\n",
    "loan_['labels_x_isnull'] = loan_['labels_x'].isnull()\n",
    "\n",
    "loan_.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "id": "mRLx5i1xy_lT",
    "outputId": "99b5274b-8cc6-44d6-b681-4723339729de"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-ccbc5068-c96d-408c-b10a-9519108c49da\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>application_id</th>\n",
       "      <th>loanapply_insert_time</th>\n",
       "      <th>is_applied</th>\n",
       "      <th>user_id</th>\n",
       "      <th>labels</th>\n",
       "      <th>labels_x</th>\n",
       "      <th>labels_isnull</th>\n",
       "      <th>labels_x_isnull</th>\n",
       "      <th>label_null_agg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-ccbc5068-c96d-408c-b10a-9519108c49da')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-ccbc5068-c96d-408c-b10a-9519108c49da button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-ccbc5068-c96d-408c-b10a-9519108c49da');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "   application_id loanapply_insert_time  is_applied  user_id  labels  \\\n",
       "0         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "1         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "2         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "3         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "4         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "\n",
       "   labels_x  labels_isnull  labels_x_isnull  label_null_agg  \n",
       "0       NaN           True             True               2  \n",
       "1       NaN           True             True               2  \n",
       "2       NaN           True             True               2  \n",
       "3       NaN           True             True               2  \n",
       "4       NaN           True             True               2  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_['label_null_agg'] = loan_['labels_isnull'].astype(np.uint8) + loan_['labels_x_isnull'].astype(np.uint8)\n",
    "loan_.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 380
    },
    "id": "3hASQAwoz_Re",
    "outputId": "614b2c2f-8736-4e85-c22d-a4abf98981ee"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 13545171/13545171 [38:28<00:00, 5868.71it/s]\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-0071ac29-5bde-4564-bb6f-30d764564fc6\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>application_id</th>\n",
       "      <th>loanapply_insert_time</th>\n",
       "      <th>is_applied</th>\n",
       "      <th>user_id</th>\n",
       "      <th>labels</th>\n",
       "      <th>labels_x</th>\n",
       "      <th>labels_isnull</th>\n",
       "      <th>labels_x_isnull</th>\n",
       "      <th>label_null_agg</th>\n",
       "      <th>labels_agg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:40</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:40</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:46</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:51</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-0071ac29-5bde-4564-bb6f-30d764564fc6')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-0071ac29-5bde-4564-bb6f-30d764564fc6 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-0071ac29-5bde-4564-bb6f-30d764564fc6');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "   application_id loanapply_insert_time  is_applied  user_id  labels  \\\n",
       "0         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "1         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "2         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "3         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "4         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "5         1748340   2022-06-07 13:05:40         NaN   430982     NaN   \n",
       "6         1748340   2022-06-07 13:05:40         NaN   430982     NaN   \n",
       "7         1748340   2022-06-07 13:05:41         NaN   430982     NaN   \n",
       "8         1748340   2022-06-07 13:05:46         NaN   430982     NaN   \n",
       "9         1748340   2022-06-07 13:05:51         NaN   430982     NaN   \n",
       "\n",
       "   labels_x  labels_isnull  labels_x_isnull  label_null_agg  labels_agg  \n",
       "0       NaN           True             True               2         NaN  \n",
       "1       NaN           True             True               2         NaN  \n",
       "2       NaN           True             True               2         NaN  \n",
       "3       NaN           True             True               2         NaN  \n",
       "4       NaN           True             True               2         NaN  \n",
       "5       NaN           True             True               2         NaN  \n",
       "6       NaN           True             True               2         NaN  \n",
       "7       NaN           True             True               2         NaN  \n",
       "8       NaN           True             True               2         NaN  \n",
       "9       NaN           True             True               2         NaN  "
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "label_agg = np.zeros(shape=loan_.shape[0])\n",
    "for i in tqdm(range(loan_.shape[0])):\n",
    "    if loan_['label_null_agg'].iloc[i] == 2:\n",
    "        label_agg[i] = loan_['labels'].iloc[i]\n",
    "    elif loan_['label_null_agg'].iloc[i] == 1:\n",
    "        if loan_['labels_isnull'].iloc[i]:\n",
    "            label_agg[i] = loan_['labels_x'].iloc[i]\n",
    "        else:\n",
    "            label_agg[i] = loan_['labels'].iloc[i]\n",
    "    else:\n",
    "        label_agg[i] = loan_['labels'].iloc[i]\n",
    "\n",
    "loan_['labels_agg'] = label_agg\n",
    "loan_.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "kfHlyBF50-zp",
    "outputId": "b917ead8-b063-4ee5-cb1a-3b03509c7256"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "application_id                  0\n",
       "loanapply_insert_time           0\n",
       "is_applied                3257239\n",
       "user_id                         0\n",
       "labels                    4076082\n",
       "labels_x                 12178195\n",
       "labels_isnull                   0\n",
       "labels_x_isnull                 0\n",
       "label_null_agg                  0\n",
       "labels_agg                2709106\n",
       "dtype: int64"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 363
    },
    "id": "nxapHtwI_Qzd",
    "outputId": "43efe386-efe0-4965-b9f8-1c2342199487"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-ce7d8ddf-020b-490e-844e-1c0d94f2d111\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>application_id</th>\n",
       "      <th>loanapply_insert_time</th>\n",
       "      <th>is_applied</th>\n",
       "      <th>user_id</th>\n",
       "      <th>labels</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:40</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:40</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:46</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:51</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-ce7d8ddf-020b-490e-844e-1c0d94f2d111')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-ce7d8ddf-020b-490e-844e-1c0d94f2d111 button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-ce7d8ddf-020b-490e-844e-1c0d94f2d111');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "   application_id loanapply_insert_time  is_applied  user_id  labels\n",
       "0         1748340   2022-06-07 13:05:41         NaN   430982     NaN\n",
       "1         1748340   2022-06-07 13:05:41         NaN   430982     NaN\n",
       "2         1748340   2022-06-07 13:05:41         NaN   430982     NaN\n",
       "3         1748340   2022-06-07 13:05:41         NaN   430982     NaN\n",
       "4         1748340   2022-06-07 13:05:41         NaN   430982     NaN\n",
       "5         1748340   2022-06-07 13:05:40         NaN   430982     NaN\n",
       "6         1748340   2022-06-07 13:05:40         NaN   430982     NaN\n",
       "7         1748340   2022-06-07 13:05:41         NaN   430982     NaN\n",
       "8         1748340   2022-06-07 13:05:46         NaN   430982     NaN\n",
       "9         1748340   2022-06-07 13:05:51         NaN   430982     NaN"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_.drop(['labels', 'labels_x', 'labels_isnull', 'labels_x_isnull', 'label_null_agg'], axis=1, inplace=True)\n",
    "loan_['labels'] = loan_['labels_agg'].copy()\n",
    "loan_.drop('labels_agg', axis=1, inplace=True)\n",
    "\n",
    "loan_.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Deployment CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "pk39nDLqId4e"
   },
   "outputs": [],
   "source": [
    "loan_.to_csv('../Data/loan_.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "vLDWx1sbJD6z"
   },
   "source": [
    "# Restart"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "id": "ZdtqGrjZJXM4",
    "outputId": "1cc8ed40-8bd3-447a-c1ef-9f75ce3095a3"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-4a51bffa-e2e1-4ff9-b06f-e8badaf3592d\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>application_id</th>\n",
       "      <th>loanapply_insert_time</th>\n",
       "      <th>is_applied</th>\n",
       "      <th>user_id</th>\n",
       "      <th>labels</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>NaN</td>\n",
       "      <td>430982</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-4a51bffa-e2e1-4ff9-b06f-e8badaf3592d')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-4a51bffa-e2e1-4ff9-b06f-e8badaf3592d button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-4a51bffa-e2e1-4ff9-b06f-e8badaf3592d');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "   application_id loanapply_insert_time  is_applied  user_id  labels\n",
       "0         1748340   2022-06-07 13:05:41         NaN   430982     NaN\n",
       "1         1748340   2022-06-07 13:05:41         NaN   430982     NaN\n",
       "2         1748340   2022-06-07 13:05:41         NaN   430982     NaN\n",
       "3         1748340   2022-06-07 13:05:41         NaN   430982     NaN\n",
       "4         1748340   2022-06-07 13:05:41         NaN   430982     NaN"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_ = pd.read_csv(\"../Data/loan_.csv\", engine='c', low_memory=True)\n",
    "loan_.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "X912NWA3oEi7"
   },
   "outputs": [],
   "source": [
    "def user_separate(df:pd.DataFrame, key:str) -> list:\n",
    "    app_subdfs = []\n",
    "    for app in tqdm(df[key].unique()):\n",
    "        subdf = df[df[key] == app]\n",
    "        app_subdfs.append(subdf)\n",
    "        \n",
    "    return app_subdfs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "XgKyfwp79j0L"
   },
   "outputs": [],
   "source": [
    "def fill_group(array:list) -> Union[int, pd.DataFrame]:\n",
    "    null_users = 0\n",
    "    concat_loan = pd.DataFrame()\n",
    "    for item in tqdm(array):\n",
    "        try:\n",
    "            to_fill = item['labels'].value_counts().index[0].astype(np.uint8)\n",
    "            item.fillna(to_fill, inplace=True)\n",
    "            concat_loan = pd.concat([concat_loan, item], axis=0)\n",
    "        except:\n",
    "            null_users += 1\n",
    "            concat_loan = pd.concat([concat_loan, item], axis=0)\n",
    "    return concat_loan, null_users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "HNKbhTjPomO3",
    "outputId": "ebc7115d-713f-4b51-e065-0f218b2965f9"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 317467/317467 [1:14:39<00:00, 70.87it/s]\n"
     ]
    }
   ],
   "source": [
    "actual_users = user_separate(loan_, key='user_id')\n",
    "\n",
    "threshold = len(actual_users) // 8\n",
    "listA = actual_users[:threshold]\n",
    "listB = actual_users[threshold:2*threshold]\n",
    "listC = actual_users[2*threshold:3*threshold]\n",
    "listD = actual_users[3*threshold:4*threshold]\n",
    "listE = actual_users[4*threshold:5*threshold]\n",
    "listF = actual_users[5*threshold:6*threshold]\n",
    "listG = actual_users[6*threshold:7*threshold]\n",
    "listH = actual_users[7*threshold:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "DNimSVkjE6vK",
    "outputId": "720ba2ea-871d-46cb-a638-529e0b1ef5c8"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 39683/39683 [50:31<00:00, 13.09it/s]\n",
      "100%|██████████| 39683/39683 [35:30<00:00, 18.63it/s]\n",
      "100%|██████████| 39683/39683 [27:11<00:00, 24.32it/s]\n",
      "100%|██████████| 39683/39683 [22:04<00:00, 29.96it/s]\n",
      "100%|██████████| 39683/39683 [17:17<00:00, 38.26it/s]\n",
      "100%|██████████| 39683/39683 [15:07<00:00, 43.74it/s]\n",
      "100%|██████████| 39683/39683 [12:37<00:00, 52.36it/s]\n",
      "100%|██████████| 39686/39686 [10:01<00:00, 66.01it/s]\n"
     ]
    }
   ],
   "source": [
    "resultA, nullA = fill_group(listA)\n",
    "resultB, nullB = fill_group(listB)\n",
    "resultC, nullC = fill_group(listC)\n",
    "resultD, nullD = fill_group(listD)\n",
    "resultE, nullE = fill_group(listE)\n",
    "resultF, nullF = fill_group(listF)\n",
    "resultG, nullG = fill_group(listG)\n",
    "resultH, nullH = fill_group(listH)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 467
    },
    "id": "vI2lRUP9YFdj",
    "outputId": "db4d9b25-4b7b-4de7-d924-e47dad0af39b"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(13545171, 5)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 13545171 entries, 0 to 13545170\n",
      "Data columns (total 5 columns):\n",
      " #   Column                 Dtype  \n",
      "---  ------                 -----  \n",
      " 0   application_id         int64  \n",
      " 1   loanapply_insert_time  object \n",
      " 2   is_applied             float64\n",
      " 3   user_id                int64  \n",
      " 4   labels                 float64\n",
      "dtypes: float64(2), int64(2), object(1)\n",
      "memory usage: 516.7+ MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-039f28ea-89e7-4e79-a2e3-97058f7db7fb\">\n",
       "    <div class=\"colab-df-container\">\n",
       "      <div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>application_id</th>\n",
       "      <th>loanapply_insert_time</th>\n",
       "      <th>is_applied</th>\n",
       "      <th>user_id</th>\n",
       "      <th>labels</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>0.0</td>\n",
       "      <td>430982</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>0.0</td>\n",
       "      <td>430982</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>0.0</td>\n",
       "      <td>430982</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>0.0</td>\n",
       "      <td>430982</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1748340</td>\n",
       "      <td>2022-06-07 13:05:41</td>\n",
       "      <td>0.0</td>\n",
       "      <td>430982</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-039f28ea-89e7-4e79-a2e3-97058f7db7fb')\"\n",
       "              title=\"Convert this dataframe to an interactive table.\"\n",
       "              style=\"display:none;\">\n",
       "        \n",
       "  <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
       "       width=\"24px\">\n",
       "    <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
       "    <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
       "  </svg>\n",
       "      </button>\n",
       "      \n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\n",
       "      flex-wrap:wrap;\n",
       "      gap: 12px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert {\n",
       "      background-color: #E8F0FE;\n",
       "      border: none;\n",
       "      border-radius: 50%;\n",
       "      cursor: pointer;\n",
       "      display: none;\n",
       "      fill: #1967D2;\n",
       "      height: 32px;\n",
       "      padding: 0 0 0 0;\n",
       "      width: 32px;\n",
       "    }\n",
       "\n",
       "    .colab-df-convert:hover {\n",
       "      background-color: #E2EBFA;\n",
       "      box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "      fill: #174EA6;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert {\n",
       "      background-color: #3B4455;\n",
       "      fill: #D2E3FC;\n",
       "    }\n",
       "\n",
       "    [theme=dark] .colab-df-convert:hover {\n",
       "      background-color: #434B5C;\n",
       "      box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
       "      filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
       "      fill: #FFFFFF;\n",
       "    }\n",
       "  </style>\n",
       "\n",
       "      <script>\n",
       "        const buttonEl =\n",
       "          document.querySelector('#df-039f28ea-89e7-4e79-a2e3-97058f7db7fb button.colab-df-convert');\n",
       "        buttonEl.style.display =\n",
       "          google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "\n",
       "        async function convertToInteractive(key) {\n",
       "          const element = document.querySelector('#df-039f28ea-89e7-4e79-a2e3-97058f7db7fb');\n",
       "          const dataTable =\n",
       "            await google.colab.kernel.invokeFunction('convertToInteractive',\n",
       "                                                     [key], {});\n",
       "          if (!dataTable) return;\n",
       "\n",
       "          const docLinkHtml = 'Like what you see? Visit the ' +\n",
       "            '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
       "            + ' to learn more about interactive tables.';\n",
       "          element.innerHTML = '';\n",
       "          dataTable['output_type'] = 'display_data';\n",
       "          await google.colab.output.renderOutput(dataTable, element);\n",
       "          const docLink = document.createElement('div');\n",
       "          docLink.innerHTML = docLinkHtml;\n",
       "          element.appendChild(docLink);\n",
       "        }\n",
       "      </script>\n",
       "    </div>\n",
       "  </div>\n",
       "  "
      ],
      "text/plain": [
       "   application_id loanapply_insert_time  is_applied  user_id  labels\n",
       "0         1748340   2022-06-07 13:05:41         0.0   430982     0.0\n",
       "1         1748340   2022-06-07 13:05:41         0.0   430982     0.0\n",
       "2         1748340   2022-06-07 13:05:41         0.0   430982     0.0\n",
       "3         1748340   2022-06-07 13:05:41         0.0   430982     0.0\n",
       "4         1748340   2022-06-07 13:05:41         0.0   430982     0.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "concat_loan = pd.concat([resultA, resultB, resultC, resultD, resultE, resultF, resultG, resultH], axis=0)\n",
    "concat_loan.reset_index(drop=True, inplace=True)\n",
    "view_info(concat_loan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "tKkIFBQza9yr",
    "outputId": "4627c0ca-52c5-4d10-8eb9-5d9586c0ae3f"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "31602"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "null_users = nullA + nullB + nullC + nullD + nullE + nullF + nullH + nullG\n",
    "null_users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "PGdwB6394JQJ",
    "outputId": "f1c70060-784a-49af-c864-b9591730cc74"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0    4122604\n",
       "4.0    3818921\n",
       "1.0    2637573\n",
       "3.0    1136156\n",
       "5.0    1079598\n",
       "2.0     153689\n",
       "Name: labels, dtype: int64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "concat_loan['labels'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "wURitibn5F8O",
    "outputId": "565e99cb-7da9-47df-cfaf-05fbfd473396"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(596630, 5)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "concat_loan[concat_loan['labels'].isna()].shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Deployment CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "nmA6_8aieN2x"
   },
   "outputs": [],
   "source": [
    "concat_loan.to_csv(\"../Data/loan_path_segment.csv\", index=False)"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [
    "qMxoYg8W3FRP",
    "kAHV40db3K-1",
    "r9C-hNXb3UDZ",
    "FEWSZjGA3kTF",
    "HCivDEAe36i5",
    "BbG5oeJZ4Dm-"
   ],
   "machine_shape": "hm",
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}