5.7. Log Feature Merge.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <font color='CC3D3D'> 0.5_7 Log_Feature_Merge\n",
    "    \n",
    "- 위에서 만든 duration.csv, visit.csv, event_cnt.csv, loan_apply_cnt.csv로부터 개별 유저에 관한 로그 데이터 집계 정보를 모두 취합한 데이터 생성\n",
    "- 취합 데이터 <span style=\"color:blue\"> **loan_with_new_feats.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",
    "import warnings\n",
    "\n",
    "warnings.filterwarnings('ignore')\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 viewinfo(df:pd.DataFrame) -> None:\n",
    "    print(df.shape)\n",
    "    print()\n",
    "    df.info()\n",
    "    print()\n",
    "    display(df.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Load"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 878
    },
    "id": "Y5SzIr3KYz_B",
    "outputId": "a73d16ab-9e81-4b61-e4f4-c4ee7bf32854"
   },
   "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-19b8d12e-00be-4381-96f8-a7f010c9d54a\">\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-19b8d12e-00be-4381-96f8-a7f010c9d54a')\"\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-19b8d12e-00be-4381-96f8-a7f010c9d54a 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-19b8d12e-00be-4381-96f8-a7f010c9d54a');\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",
      "(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-f3105998-d910-4074-85f6-b4b4c77c8bcc\">\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-f3105998-d910-4074-85f6-b4b4c77c8bcc')\"\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-f3105998-d910-4074-85f6-b4b4c77c8bcc 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-f3105998-d910-4074-85f6-b4b4c77c8bcc');\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",
    "\n",
    "user = pd.read_csv(f\"../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",
    "viewinfo(loan)\n",
    "print()\n",
    "viewinfo(user)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "-lzd0hIUyxzD"
   },
   "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)\n",
    "loan['date_cd'] = pd.to_datetime(loan['loanapply_insert_time'].dt.date)\n",
    "loan.sort_values(by=['user_id', 'loanapply_insert_time'], inplace=True)\n",
    "loan.reset_index(drop=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 474
    },
    "id": "7oyazMZq11o6",
    "outputId": "d7ff4da3-3783-4084-92c8-84aa4b6a5c51"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(13527250, 5)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 13527250 entries, 0 to 13527249\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   date_cd                datetime64[ns]\n",
      "dtypes: datetime64[ns](2), float64(1), uint32(2)\n",
      "memory usage: 412.8 MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-7bee872b-72c0-45db-9dd8-47863a5ca904\">\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>date_cd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-7bee872b-72c0-45db-9dd8-47863a5ca904')\"\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-7bee872b-72c0-45db-9dd8-47863a5ca904 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-7bee872b-72c0-45db-9dd8-47863a5ca904');\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    date_cd\n",
       "0          248576   2022-05-21 23:41:09         0.0        9 2022-05-21\n",
       "1          248576   2022-05-21 23:41:09         0.0        9 2022-05-21\n",
       "2          248576   2022-05-21 23:41:09         0.0        9 2022-05-21\n",
       "3          248576   2022-05-21 23:41:09         0.0        9 2022-05-21\n",
       "4          248576   2022-05-21 23:41:10         0.0        9 2022-05-21"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "viewinfo(loan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "X4mZIy-X0zQE",
    "outputId": "0c05828d-7ea4-48ba-d59b-798738c2c3ef"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "22"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del user\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "USGNiE8uzyky"
   },
   "source": [
    "# Duration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 438
    },
    "id": "C1LU_3-0zuHv",
    "outputId": "19790335-b2d6-4cb3-c502-0c570fb647bb"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1863305, 3)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1863305 entries, 0 to 1863304\n",
      "Data columns (total 3 columns):\n",
      " #   Column    Dtype         \n",
      "---  ------    -----         \n",
      " 0   user_id   int64         \n",
      " 1   date_cd   datetime64[ns]\n",
      " 2   duration  int64         \n",
      "dtypes: datetime64[ns](1), int64(2)\n",
      "memory usage: 42.6 MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-028ffaf9-f7f0-42a6-b8d1-82c71dcf8ac5\">\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>date_cd</th>\n",
       "      <th>duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>354</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td>2022-03-24</td>\n",
       "      <td>347</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11</td>\n",
       "      <td>2022-04-20</td>\n",
       "      <td>685</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11</td>\n",
       "      <td>2022-06-09</td>\n",
       "      <td>169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11</td>\n",
       "      <td>2022-06-13</td>\n",
       "      <td>141</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-028ffaf9-f7f0-42a6-b8d1-82c71dcf8ac5')\"\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-028ffaf9-f7f0-42a6-b8d1-82c71dcf8ac5 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-028ffaf9-f7f0-42a6-b8d1-82c71dcf8ac5');\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    date_cd  duration\n",
       "0        9 2022-05-21       354\n",
       "1       11 2022-03-24       347\n",
       "2       11 2022-04-20       685\n",
       "3       11 2022-06-09       169\n",
       "4       11 2022-06-13       141"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "duration = pd.read_csv(\"../Data/duration.csv\", engine='c',\n",
    "                       low_memory=True, parse_dates=['date_cd'])\n",
    "viewinfo(duration)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 388
    },
    "id": "fZBiVikW19VF",
    "outputId": "154ee383-28d1-42f1-f4ff-1ebd8cbdd335"
   },
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAk0AAAFzCAYAAAAjYj0YAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4yLjIsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+WH4yJAAAX90lEQVR4nO3dfYxdZ50f8O8Tv0DWDklwUMQ6Syast11RdpsQtyKIkmyrsnZQBSvlD1ClzBakoN2tkypaqYE45IW0apeWEtwXFNRdnHbpQtNdNYjYgQLWgkNgncQxWQjJwA7UId2YsRMn3sT22Kd/3DOTO+M74+fa83In/nykK597Xp77O7+cM/nOOffeKU3TBACA2Z212AUAACwFQhMAQAWhCQCggtAEAFBBaAIAqCA0AQBUWN7PyhdccEEzNDQ0T6V0HDp0KKtWrZrX13g10a/+6Fc9veqPfvVHv/qjX/W6e/Xwww//vGmaN8zV2H2FpqGhoezatWuuXrunHTt25KqrrprX13g10a/+6Fc9veqPfvVHv/qjX/W6e1VK+clcju32HABABaEJAKCC0AQAUEFoAgCoIDQBAFQQmgAAKghNAAAVhCYAgApCEwBABaEJAKCC0AQAUEFoAgCoIDQBAFQQmgAAKghNAAAVhCYAgApCEwBABaEJAKCC0AQAUEFoAgCoIDQBAFQQmgAAKghNAAAVhCYAgApCEwBABaEJAKCC0AQAUEFoAgCoIDQBAFRYvtgFdNuyZUvOO++8XHPNNUmStWvXZt26ddm0adMiVwYAnOkGKjSNjIzkLX/nrdl34Pkkyb4DBxe5IgCAjoEKTUmSs5bl2C+sWewqAACm8J4mAIAKQhMAQAWhCQCggtAEAFBBaAIAqCA0AQBUEJoAACoITQAAFYQmAIAKQhMAQAWhCQCggtAEAFBBaAIAqCA0AQBUEJoAACoITQAAFYQmAIAKQhMAQAWhCQCggtAEAFBBaAIAqCA0AQBUEJoAACoITQAAFYQmAIAKQhMAQAWhCQCggtAEAFBBaAIAqCA0AQBUEJoAACoITQAAFYQmAIAKQhMAQAWhCQCggtAEAFBBaAIAqCA0AQBUEJoAACoITQAAFYQmAIAKQhMAQAWhCQCggtAEAFBBaAIAqCA0AQBUEJoAACoITQAAFYQmAIAKQhMAQAWhCQCggtAEAFBBaAIAqCA0AQBUEJoAACoITQAAFYQmAIAKQhMAQAWhCQCggtAEAFBBaAIAqCA0AQBUWL7YBXR7+umn89LhIyddb8uWLUmSTZs2zXdJAABJBiw0vfTSS3lu//4kZdb1RkZGFqYgAICW23MAABWEJgCACkITAEAFoQkAoILQBABQQWgCAKggNAEAVBCaAAAqCE0AABWEJgCACkITAEAFoQkAoILQBABQQWgCAKggNAEAVBCaAAAqCE0AABWEJgCACkITAEAFoQkAoILQBABQQWgCAKggNAEAVBCaAAAqCE0AABWEJgCACkITAEAFoQkAoILQBABQQWgCAKggNAEAVBCaAAAqCE0AABWEJgCACkITAEAFoQkAoILQBABQQWgCAKggNAEAVBCaAAAqCE0AABWEJgCACkITAEAFoQkAoILQBABQQWgCAKggNAEAVBCaAAAqCE0AABWEJgCACkITAEAFoQkAoILQBABQQWgCAKggNAEAVBCaAAAqCE0AABWEJgCACkITAEAFoQkAoILQBABQYfliF3AqHnvssSTJVVddtbiFkNe97nU5ePDgjMuXLVuWY8eOJUnOPffcPP/88ycd86KLLsqHPvSh3HHHHVm+fHlKKTly5EiSZOXKlVm7dm3OPvvsfPCDH8ytt96aD3/4w/nUpz6VW265JV/84hczPj6eY8eOZe/evTly5EguvvjirFy5MitWrMjGjRszNjaWj3zkI/npT3+aN73pTfnABz6QO+64I5dcckk++tGP5pOf/GSapsmdd96ZNWvWZGRkJDfccEPuuuuurFu37oR6x8bGcvvtt+fWW2/NmjVrpjxPkltuuSVHjx5NkpRSJvsyMf7EGJs3b04pJTfeeOMJNUys0z3WihUr8vGPf3zK8ptuuil79+7Nli1bTqh1el3dNffywgsv5Prrr8/111+fT3/601O2Gx4ezsc+9rHJnkzvQY2ZtjmVsV4tlsK+D3KN/dQ2yPuxVJxqD5dy75fddttt1Svffffdt1133XXzVsznP//5rFy5Mi+Ml8l5v3j+6mzcuHHKep/73OfmrQb6c/jw4VmXN01Tve6EgwcPZufOnTl+/HiOHz8+GbqS5NixY3nuueeyb9++PPjggzl06FAeeuihNE2TnTt35tlnn83+/ftz4MCBye2ef/757N+/P/v27cvRo0fz4x//OA8++GDGx8czNjY2+VoHDhzInj178tRTT+XnP/95Dh8+nCuuuCI33nhj9u3blz179uR973vfCfV+5jOfyTe/+c28/PLLueKKK6Y83717d771rW9l//792b9/f8bGxjI2NjZl/Ikxdu7cOfk602uYWKd7rH379p2w/Nvf/nbGx8d71jq9ru6ae/nsZz+b3bt3Z8+ePXnyySenbLdz584cOnRo8nWm96DGTNucyliDYHR0NENDQ6c1xlLY97mqcS76NV0/tS2FXnebj36drlPt4Xz3vrtXt99++zO33Xbb3XM19pK7Pefq0plhfHz8pOu8+OKLSV4JZjXbPProo7n//vtnfK3R0dHJ6W3btmXXrl2T80ZHRzMyMjJl27GxsWzfvj1N02T79u0ZGRmZfL5t27Zs27Ztxlq2bds2GaK615tew8Q627dv72uM7lq769y2bduUmsfGxk4Yd2xsLLt3707TNBkdHZ2yP03TTPZ+dHQ0Dz/88EnH6zV+r21mmn8mWAr7Psg19lPbIO/HUnGqPVzqvR/o0HTWywcnb41MPOB0HDt2rCpcJcnRo0cz/UrsnXfeOeX51q1bc/z48cmx77zzzsnnR48enbyVNtP499xzT7Zu3TpjTd3r9BprtuXdtXbX2V3XsWPHcs8995ww7tatW6dcJZzYrledt95665Qe9Bqv1/i9tplp/plgKez7INfYT22DvB9Lxan2cKn3/qTvaSqlXJfkuiS58MILs2PHjnkr5pxzzslZZy3LG1d1np+/9uIcfO5AnnvuuXl7TZhJ9xWVCaOjo1POgQceeGAySIyPj0+5SjQ9dPQaf+Lq0Uzrnmyd7uXTddfaXWf3OOPj49m+fXsuu+yyKds+8MADU26LzlZjd49mGm+66X2b2Gam+UvBiy++eFo/H5fCvs9ljafbr9OpbSn0erq57tfpOtUeLkTv57NXJw1NTdPcneTuJFm/fn0zn7fHPvGJT2TVqtV55qXOe5qeOXQgl7/5wtx1112T67g9x0IppWTVqlVTQsHQ0NCUY/CRRx7J/fffn/Hx8SxfvjwXXXRR9u7dm/Hx8ZRSZg1OpZRs2LAhTdPkS1/6Us91T7ZO9/L77rtvyrLuWrvrnHgzetM0Wb58eTZs2HDCefXII4/ky1/+8pTg1L1dt9WrV+fll1+e7EGv8aab3reJbWaavxTs2LHjtGpdCvs+lzWebr9Op7al0Ovp5rpfp+tUe7gQvZ/PXg307TmYa8uWLcvy5XUfGl2xYsUJt+c2b9485fnw8HDOOuusybE3b948+XzFihVZsWLFrONfe+21GR4enrGm7nV6jTXb8u5au+vsrmvZsmW59tprTxh3eHh4MiR1v1avOm+//fYpPeg1Xq/xe20z0/wzwVLY90GusZ/aBnk/lopT7eFS7/2SC02DdHmS+VMTbFavXp3klSsgNdtcdtllufrqq2d8re5Pp2zcuDHr16+fnDc0NHTCx/jXrFmTDRs2TF7xWbdu3eTzjRs3nvDJz24bN27MmjVrsmbNminrTa9hYp0NGzb0NUZ3rd11bty4cUrNvT7yu2bNmlx66aUppWRoaGjK/pRSJns/NDSUyy+//KTj9Rq/1zYzzT8TLIV9H+Qa+6ltkPdjqTjVHi713i/J72licAzS9zTdfPPNJ/2epiuvvDLveMc78sQTT5z0e5omfgPavHlzbrjhhhOuMk0YHh7O6OjolKsl3c9HRkZ6fk9T929Yw8PDeeqpp074nqbp63SPNXGVqXv5D37wg+zdu7dnrdPr6p7u5corr8yRI0cmv6epe7uJ72maeJ3pY9eYaZtTGevVYins+yDX2E9tg7wfS8Wp9nAp976c7M2q3davX9/s2rVr3op5z3vek1WrVudnL71yW2D6e5qSTH6Kbvr8M9Gg3ecedPpVT6/6o1/90a/+6Fe97l6VUh5ummb9XI295G7PAQAsBqEJAKCC0AQAUEFoAgCoIDQBAFQQmgAAKghNAAAVhCYAgApCEwBABaEJAKCC0AQAUEFoAgCoIDQBAFQQmgAAKghNAAAVhCYAgApCEwBABaEJAKCC0AQAUEFoAgCoIDQBAFQQmgAAKghNAAAVhCYAgApCEwBABaEJAKCC0AQAUEFoAgCoIDQBAFQQmgAAKghNAAAVhCYAgApCEwBABaEJAKCC0AQAUEFoAgCoIDQBAFQQmgAAKghNAAAVhCYAgApCEwBABaEJAKCC0AQAUEFoAgCoIDQBAFQQmgAAKghNAAAVhCYAgApCEwBABaEJAKCC0AQAUEFoAgCoIDQBAFQQmgAAKghNAAAVhCYAgApCEwBABaEJAKCC0AQAUGH5YhfQ7eyzz855r399fvb0gVnXW7du3QJVBADQMVChae3atXnLr/3dfP/pr8y63qZNmxaoIgCADrfnAAAqCE0AABWEJgCACkITAEAFoQkAoILQBABQQWgCAKggNAEAVBCaAAAqCE0AABWEJgCACkITAEAFoQkAoILQBABQQWgCAKggNAEAVBCaAAAqCE0AABWEJgCACkITAEAFoQkAoILQBABQQWgCAKggNAEAVBCaAAAqCE0AABWEJgCACkITAEAFoQkAoILQBABQQWgCAKggNAEAVBCaAAAqCE0AABWEJgCACkITAEAFoQkAoILQBABQQWgCAKggNAEAVBCaAAAqCE0AABWEJgCACkITAEAFoQkAoILQBABQQWgCAKggNAEAVBCaAAAqCE0AABWEJgCACkITAEAFoQkAoILQBABQQWgCAKggNAEAVBCaAAAqCE0AABWEJgCACkITAEAFoQkAoILQBABQYfliF3CC48ey7G/G2iclyYWLWQ0AQJIBC03r1q3LL5z92rzh/HOTJGvXrs26desWuSoAgAELTZs2bcqOHTty7733LnYpAABTeE8TAEAFoQkAoILQBABQQWgCAKggNAEAVBCaAAAqCE0AABWEJgCACkITAEAFoQkAoILQBABQQWgCAKggNAEAVBCaAAAqCE0AABWEJgCACkITAEAFoQkAoILQBABQQWgCAKggNAEAVBCaAAAqCE0AABWEJgCACkITAEAFoQkAoILQBABQQWgCAKggNAEAVBCaAAAqCE0AABVK0zT1K5eyL8lP5q+cJMkFSX4+z6/xaqJf/dGvenrVH/3qj371R7/qdffq4qZp3jBXA/cVmhZCKWVX0zTrF7uOpUK/+qNf9fSqP/rVH/3qj37Vm89euT0HAFBBaAIAqDCIoenuxS5gidGv/uhXPb3qj371R7/6o1/15q1XA/eeJgCAQTSIV5oAAAbOQIWmUsqGUsoPSykjpZSbFruexVJKGS2lfK+UsruUsqud9/pSyldLKU+1/57fzi+llE+3PdtTSnlb1zjD7fpPlVKGF2t/5lop5Q9LKc+WUh7vmjdn/SmlXN72f6TdtizsHs6tGfp1Wynl6fYY211Kubpr2Ufaff9hKeU3u+b3PD9LKZeUUr7Tzv9CKWXlwu3d3Cql/FIp5RullO+XUv6ylHJDO9/x1cMs/XJ89VBKeW0p5bullMfaft3ezu+5j6WU17TPR9rlQ11j9dXHpWaWXn2ulPJXXcfWpe38hTkXm6YZiEeSZUl+lOTNSVYmeSzJWxa7rkXqxWiSC6bN+4MkN7XTNyX5t+301Um2JSlJ3p7kO+381yf5cfvv+e30+Yu9b3PUn3cleVuSx+ejP0m+265b2m03LvY+z0O/bkvy+z3WfUt77r0mySXtOblstvMzyReTvL+d/kyS31nsfT6NXr0xydva6XOSPNn2xPHVX78cX737VZKsbqdXJPlOeyz03Mckv5vkM+30+5N84VT7uNQes/Tqc0mu6bH+gpyLg3Sl6e8nGWma5sdN0xxJ8idJ3rvINQ2S9ybZ2k5vTfK+rvn3NB0PJTmvlPLGJL+Z5KtN0+xvmuZAkq8m2bDQRc+Hpmn+PMn+abPnpD/tstc1TfNQ0zmr7ukaa0maoV8zeW+SP2ma5nDTNH+VZCSdc7Pn+dn+ZvYPk9zbbt/d+yWnaZpnmqZ5pJ1+IckPkqyN46unWfo1kzP9+GqapnmxfbqifTSZeR+7j7t7k/yjtid99XGed2tezNKrmSzIuThIoWltkv/b9XxvZj/5Xs2aJF8ppTxcSrmunXdh0zTPtNP/L8mF7fRMfTvT+jlX/VnbTk+f/2r0z9vL2H84cbsp/fdrTZLnmqYZnzZ/yWtvhVyWzm+4jq+TmNavxPHVUyllWSlld5Jn0/kf+I8y8z5O9qVd/nw6PTkjfu5P71XTNBPH1r9qj63/UEp5TTtvQc7FQQpNvOKdTdO8LcnGJL9XSnlX98I2FfvY4wz0p8p/SfLLSS5N8kySf7+45QyWUsrqJP8ryb9omuZg9zLH14l69MvxNYOmaY41TXNpkovSuTL0q4tc0sCa3qtSyluTfCSdnv29dG65/cuFrGmQQtPTSX6p6/lF7bwzTtM0T7f/Ppvkz9I5sf66vZyY9t9n29Vn6tuZ1s+56s/T7fT0+a8qTdP8dfsD6XiSz6ZzjCX992ssncvgy6fNX7JKKSvSCQB/3DTNn7azHV8z6NUvx9fJNU3zXJJvJLkiM+/jZF/a5eem05Mz6ud+V682tLeEm6ZpDif5o5z6sXVK5+Ighaa/SPIr7acIVqbzprf7FrmmBVdKWVVKOWdiOsm7kzyeTi8m3vU/nOR/t9P3Jbm2/eTA25M8395GeCDJu0sp57eXxt/dznu1mpP+tMsOllLe3r534NqusV41JgJA67fSOcaSTr/e335q55Ikv5LOmyV7np/tVZdvJLmm3b6790tO+9/8vyb5QdM0n+xa5PjqYaZ+Ob56K6W8oZRyXjt9dpJ/nM77wGbax+7j7pokX2970lcf53/P5t4MvXqi65eXks57kLqPrfk/F3u9O3yxHum8+/3JdO7x3rzY9SxSD96cziceHkvylxN9SOc+9teSPJXk/yR5ffPKJwz+U9uz7yVZ3zXWB9N5g+BIkn+22Ps2hz36H+lc8j+azn3oD81lf5Ksb0/EHyX5j2m/BHapPmbo139r+7Gn/WHzxq71b273/Yfp+jTJTOdne8x+t+3j/0zymsXe59Po1TvTufW2J8nu9nG146vvfjm+evfr15M82vbl8SQfm20fk7y2fT7SLn/zqfZxqT1m6dXX22Pr8ST/Pa98wm5BzkXfCA4AUGGQbs8BAAwsoQkAoILQBABQQWgCAKggNAEAVBCagNNSOn/R/vfnYJzzSim/2/X8F0sp9862DcBCEpqABdP1rce9nJfOX3VPkjRN87Omaa6ZZX2ABSU0AX0rpdxcSnmylPKtJH+7nbejlLK+nb6glDLaTv92KeW+UsrXk3ytlLK6lPK1UsojpZTvlVIm/gr7v0nyy6WU3aWUT5RShkopj7djvLaU8kft+o+WUn6ja+w/LaVsL6U8VUr5gwVuBXAGme23PoATlFIuT+fPM1yazs+QR5I8fJLN3pbk15um2d9ebfqtpmkOllIuSPJQKeW+JDcleWvT+QOdKaUMdW3/e+n8rdxfK6X8apKvlFL+Vrvs0iSXJTmc5IellC1N03T/VXOAOSE0Af36B0n+rGmav0mSNvCczFebptnfTpck/7qU8q4kx5OsTXLhSbZ/Z5ItSdI0zROllJ8kmQhNX2ua5vm2lu8nuTiJ0ATMOaEJmCvjeeWW/2unLTvUNf1Pk7whyeVN0xxtb+NNX78fh7umj8XPNWCeeE8T0K8/T/K+UsrZpZRzkvyTdv5oksvb6dnewH1ukmfbwPQb6VwZSpIXkpwzwzbfTCdspb0t96Z0/lApwIIRmoC+NE3zSJIvJHksybYkf9Eu+ndJfqeU8miSC2YZ4o+TrC+lfC/JtUmeaMcdS7KzlPJ4KeUT07b5z0nOarf5QpLfbprmcAAWUGmaZrFrAAAYeK40AQBUEJoAACoITQAAFYQmAIAKQhMAQAWhCQCggtAEAFBBaAIAqPD/AWgwzgTHCuJYAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 720x432 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "plt.figure(figsize=(10, 6))\n",
    "sns.boxplot(data=duration, x='duration')\n",
    "plt.grid(True)\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 81
    },
    "id": "ihydV4t21Gsn",
    "outputId": "0ba68636-a70d-4642-b64f-cc6149ec5c97"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-d5bf6d8d-cc41-45fb-9240-5826d0397ba2\">\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>date_cd</th>\n",
       "      <th>duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>792305</th>\n",
       "      <td>374546</td>\n",
       "      <td>2022-04-06</td>\n",
       "      <td>33479</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-d5bf6d8d-cc41-45fb-9240-5826d0397ba2')\"\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-d5bf6d8d-cc41-45fb-9240-5826d0397ba2 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-d5bf6d8d-cc41-45fb-9240-5826d0397ba2');\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    date_cd  duration\n",
       "792305   374546 2022-04-06     33479"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "duration[duration['duration'] == duration['duration'].max()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "CkpPSRTAGI0M",
    "outputId": "8de8a45f-fd1d-4169-824d-602907984406"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['2022-03-31T00:00:00.000000000', '2022-04-12T00:00:00.000000000',\n",
       "       '2022-04-13T00:00:00.000000000', '2022-04-18T00:00:00.000000000',\n",
       "       '2022-04-21T00:00:00.000000000', '2022-04-22T00:00:00.000000000',\n",
       "       '2022-04-25T00:00:00.000000000', '2022-04-27T00:00:00.000000000',\n",
       "       '2022-04-28T00:00:00.000000000'], dtype='datetime64[ns]')"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan[(loan['user_id'] == 374546)]['date_cd'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 528
    },
    "id": "Jx61_exUG3Yt",
    "outputId": "8da71ba5-8477-4dfc-88ed-3ce09ed8b381"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(13527250, 8)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 13527250 entries, 0 to 13527249\n",
      "Data columns (total 8 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   date_cd                datetime64[ns]\n",
      " 5   event                  float64       \n",
      " 6   loan_cnt               float64       \n",
      " 7   duration               float64       \n",
      "dtypes: datetime64[ns](2), float64(4), uint32(2)\n",
      "memory usage: 825.6 MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-096fde6b-2d7e-46f2-887f-6e6b527c336b\">\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>date_cd</th>\n",
       "      <th>event</th>\n",
       "      <th>loan_cnt</th>\n",
       "      <th>duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>354.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>354.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>354.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>354.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>354.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-096fde6b-2d7e-46f2-887f-6e6b527c336b')\"\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-096fde6b-2d7e-46f2-887f-6e6b527c336b 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-096fde6b-2d7e-46f2-887f-6e6b527c336b');\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    date_cd  \\\n",
       "0          248576   2022-05-21 23:41:09         0.0        9 2022-05-21   \n",
       "1          248576   2022-05-21 23:41:09         0.0        9 2022-05-21   \n",
       "2          248576   2022-05-21 23:41:09         0.0        9 2022-05-21   \n",
       "3          248576   2022-05-21 23:41:09         0.0        9 2022-05-21   \n",
       "4          248576   2022-05-21 23:41:10         0.0        9 2022-05-21   \n",
       "\n",
       "   event  loan_cnt  duration  \n",
       "0    3.0       0.0     354.0  \n",
       "1    3.0       0.0     354.0  \n",
       "2    3.0       0.0     354.0  \n",
       "3    3.0       0.0     354.0  \n",
       "4    3.0       0.0     354.0  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "loan_duration = pd.merge(left=loan, right=duration, how='left', on=['user_id', 'date_cd'])\n",
    "viewinfo(loan_duration)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "VW3FxioQHD0e",
    "outputId": "82b7fd8c-84fc-4925-9b3b-1bbc99209b85"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['2022-03-31T00:00:00.000000000', '2022-04-12T00:00:00.000000000',\n",
       "       '2022-04-13T00:00:00.000000000', '2022-04-18T00:00:00.000000000',\n",
       "       '2022-04-21T00:00:00.000000000', '2022-04-22T00:00:00.000000000',\n",
       "       '2022-04-25T00:00:00.000000000', '2022-04-27T00:00:00.000000000',\n",
       "       '2022-04-28T00:00:00.000000000'], dtype='datetime64[ns]')"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_duration[(loan_duration['user_id'] == 374546)]['date_cd'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "1fqX4WkeHOhK",
    "outputId": "a714d6b2-14a4-445f-8117-f4fc83c3c6bd"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "application_id                 0\n",
       "loanapply_insert_time          0\n",
       "is_applied               3257239\n",
       "user_id                        0\n",
       "date_cd                        0\n",
       "event                     703483\n",
       "loan_cnt                  703483\n",
       "duration                  991813\n",
       "dtype: int64"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_duration.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "uaosIhiDHXue",
    "outputId": "c2695ef3-15a7-4e23-9c2f-255882348a4d"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "195"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan = loan_duration.copy()\n",
    "del loan_duration, duration\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 438
    },
    "id": "N9Whv8zmI3LC",
    "outputId": "599b52fc-4f93-4aac-d93b-7db5d13667cc"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(417988, 3)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 417988 entries, 0 to 417987\n",
      "Data columns (total 3 columns):\n",
      " #   Column      Non-Null Count   Dtype  \n",
      "---  ------      --------------   -----  \n",
      " 0   user_id     417988 non-null  uint64 \n",
      " 1   is_applied  417988 non-null  float64\n",
      " 2   duration    378037 non-null  float64\n",
      "dtypes: float64(2), uint64(1)\n",
      "memory usage: 9.6 MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-9d18b7e4-932b-41f3-b682-3867fa00796d\">\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>is_applied</th>\n",
       "      <th>duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>354.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td>0.0</td>\n",
       "      <td>519.595745</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11</td>\n",
       "      <td>1.0</td>\n",
       "      <td>459.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1037.666667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-9d18b7e4-932b-41f3-b682-3867fa00796d')\"\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-9d18b7e4-932b-41f3-b682-3867fa00796d 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-9d18b7e4-932b-41f3-b682-3867fa00796d');\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  is_applied     duration\n",
       "0        9         0.0   354.000000\n",
       "1       11         0.0   519.595745\n",
       "2       11         1.0   459.666667\n",
       "3       14         0.0          NaN\n",
       "4       17         0.0  1037.666667"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "## 유저별 평균\n",
    "avg_duration = loan[['user_id', 'is_applied', 'duration']].groupby(by=['user_id', 'is_applied']).mean()\n",
    "avg_duration.reset_index(drop=False, inplace=True)\n",
    "viewinfo(avg_duration)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 528
    },
    "id": "fWDVR4EbJCwz",
    "outputId": "cfc88c6b-497f-4371-8481-55f89d6bd8fd"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(991813, 8)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 991813 entries, 155 to 13527249\n",
      "Data columns (total 8 columns):\n",
      " #   Column                 Non-Null Count   Dtype         \n",
      "---  ------                 --------------   -----         \n",
      " 0   application_id         991813 non-null  uint32        \n",
      " 1   loanapply_insert_time  991813 non-null  datetime64[ns]\n",
      " 2   is_applied             820693 non-null  float64       \n",
      " 3   user_id                991813 non-null  uint32        \n",
      " 4   date_cd                991813 non-null  datetime64[ns]\n",
      " 5   event                  288330 non-null  float64       \n",
      " 6   loan_cnt               288330 non-null  float64       \n",
      " 7   duration               0 non-null       float64       \n",
      "dtypes: datetime64[ns](2), float64(4), uint32(2)\n",
      "memory usage: 60.5 MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-15ae782b-4b1a-4a80-ae52-da5f69e5e188\">\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>date_cd</th>\n",
       "      <th>event</th>\n",
       "      <th>loan_cnt</th>\n",
       "      <th>duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>1508745</td>\n",
       "      <td>2022-04-18 14:37:10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14</td>\n",
       "      <td>2022-04-18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>1508745</td>\n",
       "      <td>2022-04-18 14:37:11</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14</td>\n",
       "      <td>2022-04-18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>1854180</td>\n",
       "      <td>2022-05-06 17:10:55</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19</td>\n",
       "      <td>2022-05-06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163</th>\n",
       "      <td>513514</td>\n",
       "      <td>2022-03-13 20:17:19</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20</td>\n",
       "      <td>2022-03-13</td>\n",
       "      <td>10.50</td>\n",
       "      <td>5.5</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164</th>\n",
       "      <td>513514</td>\n",
       "      <td>2022-03-13 20:17:19</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20</td>\n",
       "      <td>2022-03-13</td>\n",
       "      <td>9.68</td>\n",
       "      <td>5.1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-15ae782b-4b1a-4a80-ae52-da5f69e5e188')\"\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-15ae782b-4b1a-4a80-ae52-da5f69e5e188 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-15ae782b-4b1a-4a80-ae52-da5f69e5e188');\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    date_cd  \\\n",
       "155         1508745   2022-04-18 14:37:10         0.0       14 2022-04-18   \n",
       "156         1508745   2022-04-18 14:37:11         0.0       14 2022-04-18   \n",
       "162         1854180   2022-05-06 17:10:55         0.0       19 2022-05-06   \n",
       "163          513514   2022-03-13 20:17:19         1.0       20 2022-03-13   \n",
       "164          513514   2022-03-13 20:17:19         0.0       20 2022-03-13   \n",
       "\n",
       "     event  loan_cnt  duration  \n",
       "155    NaN       NaN       NaN  \n",
       "156    NaN       NaN       NaN  \n",
       "162    NaN       NaN       NaN  \n",
       "163  10.50       5.5       NaN  \n",
       "164   9.68       5.1       NaN  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "nulls = loan[(loan['duration'].isna())]\n",
    "viewinfo(nulls)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 528
    },
    "id": "e50Q9JprJ54i",
    "outputId": "b86033ee-fc01-4f4e-e770-b15df26f82dd"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(13527250, 8)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 13527250 entries, 0 to 13527249\n",
      "Data columns (total 8 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   date_cd                datetime64[ns]\n",
      " 5   event                  float64       \n",
      " 6   loan_cnt               float64       \n",
      " 7   duration               float64       \n",
      "dtypes: datetime64[ns](2), float64(4), uint32(2)\n",
      "memory usage: 825.6 MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-de7610d1-7d94-411a-91ac-ef01800b3ed9\">\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>date_cd</th>\n",
       "      <th>event</th>\n",
       "      <th>loan_cnt</th>\n",
       "      <th>duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>354.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>354.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>354.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>354.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>354.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-de7610d1-7d94-411a-91ac-ef01800b3ed9')\"\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-de7610d1-7d94-411a-91ac-ef01800b3ed9 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-de7610d1-7d94-411a-91ac-ef01800b3ed9');\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    date_cd  \\\n",
       "0          248576   2022-05-21 23:41:09         0.0        9 2022-05-21   \n",
       "1          248576   2022-05-21 23:41:09         0.0        9 2022-05-21   \n",
       "2          248576   2022-05-21 23:41:09         0.0        9 2022-05-21   \n",
       "3          248576   2022-05-21 23:41:09         0.0        9 2022-05-21   \n",
       "4          248576   2022-05-21 23:41:10         0.0        9 2022-05-21   \n",
       "\n",
       "   event  loan_cnt  duration  \n",
       "0    3.0       0.0     354.0  \n",
       "1    3.0       0.0     354.0  \n",
       "2    3.0       0.0     354.0  \n",
       "3    3.0       0.0     354.0  \n",
       "4    3.0       0.0     354.0  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "viewinfo(loan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "IojNmQm6JSKs",
    "outputId": "312a0caf-457b-4efa-fe79-91f54422af05"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 991813/991813 [43:36<00:00, 379.10it/s]\n"
     ]
    }
   ],
   "source": [
    "for idx in tqdm(nulls.index):\n",
    "    _, _, is_applied, user_id, _, _, _, _ = nulls.loc[idx]\n",
    "    sub = avg_duration[(avg_duration['user_id'] == user_id) & (avg_duration['is_applied'] == is_applied)]\n",
    "    if sub.shape[0] == 0:\n",
    "        continue\n",
    "    loan['duration'].loc[idx] = sub['duration'].values[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 528
    },
    "id": "vnoU18iSUkf3",
    "outputId": "f2e2f236-f4eb-4098-c110-6454a406648d"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(13527250, 8)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 13527250 entries, 0 to 13527249\n",
      "Data columns (total 8 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   date_cd                datetime64[ns]\n",
      " 5   event                  float64       \n",
      " 6   loan_cnt               float64       \n",
      " 7   duration               float64       \n",
      "dtypes: datetime64[ns](2), float64(4), uint32(2)\n",
      "memory usage: 825.6 MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-79174c47-376a-44d6-8531-18156346b585\">\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>date_cd</th>\n",
       "      <th>event</th>\n",
       "      <th>loan_cnt</th>\n",
       "      <th>duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>354.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>354.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>354.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>354.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>354.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-79174c47-376a-44d6-8531-18156346b585')\"\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-79174c47-376a-44d6-8531-18156346b585 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-79174c47-376a-44d6-8531-18156346b585');\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    date_cd  \\\n",
       "0          248576   2022-05-21 23:41:09         0.0        9 2022-05-21   \n",
       "1          248576   2022-05-21 23:41:09         0.0        9 2022-05-21   \n",
       "2          248576   2022-05-21 23:41:09         0.0        9 2022-05-21   \n",
       "3          248576   2022-05-21 23:41:09         0.0        9 2022-05-21   \n",
       "4          248576   2022-05-21 23:41:10         0.0        9 2022-05-21   \n",
       "\n",
       "   event  loan_cnt  duration  \n",
       "0    3.0       0.0     354.0  \n",
       "1    3.0       0.0     354.0  \n",
       "2    3.0       0.0     354.0  \n",
       "3    3.0       0.0     354.0  \n",
       "4    3.0       0.0     354.0  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "viewinfo(loan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "TmfeTGyBUmur",
    "outputId": "e978a2d3-02ea-4b5f-8adb-a700825ca273"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "application_id                 0\n",
       "loanapply_insert_time          0\n",
       "is_applied               3257239\n",
       "user_id                        0\n",
       "date_cd                        0\n",
       "event                     703483\n",
       "loan_cnt                  703483\n",
       "duration                  703483\n",
       "dtype: int64"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "8GLnJIES0d_X"
   },
   "source": [
    "# Visits"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 438
    },
    "id": "l5Bn7YN20fVz",
    "outputId": "6325c304-e972-4912-f9ea-47e4f5a47d40"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1863305, 3)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1863305 entries, 0 to 1863304\n",
      "Data columns (total 3 columns):\n",
      " #   Column   Dtype         \n",
      "---  ------   -----         \n",
      " 0   user_id  uint32        \n",
      " 1   date_cd  datetime64[ns]\n",
      " 2   visit    uint8         \n",
      "dtypes: datetime64[ns](1), uint32(1), uint8(1)\n",
      "memory usage: 23.1 MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-45f50e16-341b-4221-9e19-5adecf35d2e8\">\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>date_cd</th>\n",
       "      <th>visit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td>2022-03-24</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11</td>\n",
       "      <td>2022-04-20</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11</td>\n",
       "      <td>2022-06-09</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11</td>\n",
       "      <td>2022-06-13</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-45f50e16-341b-4221-9e19-5adecf35d2e8')\"\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-45f50e16-341b-4221-9e19-5adecf35d2e8 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-45f50e16-341b-4221-9e19-5adecf35d2e8');\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    date_cd  visit\n",
       "0        9 2022-05-21      1\n",
       "1       11 2022-03-24      1\n",
       "2       11 2022-04-20      1\n",
       "3       11 2022-06-09      1\n",
       "4       11 2022-06-13      1"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "visit = pd.read_csv(\"../Data/visit.csv\", engine='c', low_memory=True, parse_dates=['date_cd'],\n",
    "                    dtype={'user_id':'uint32', 'visit':'uint8'})\n",
    "viewinfo(visit)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "XJT9y5_YIsfg",
    "outputId": "0b02142d-03b5-4740-8ebe-c125aeedf717"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "17"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "visit['visit'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 563
    },
    "id": "aJpDYXwbU0O_",
    "outputId": "de6cfa04-288a-4c9a-b865-037d29484da9"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(13527250, 10)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 13527250 entries, 0 to 13527249\n",
      "Data columns (total 10 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   date_cd                datetime64[ns]\n",
      " 5   event                  float64       \n",
      " 6   loan_cnt               float64       \n",
      " 7   duration               float64       \n",
      " 8   visit_x                float64       \n",
      " 9   visit_y                float64       \n",
      "dtypes: datetime64[ns](2), float64(6), uint32(2)\n",
      "memory usage: 1.0 GB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-409d4d47-8c4e-453e-8da8-c932e1e6612a\">\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>date_cd</th>\n",
       "      <th>event</th>\n",
       "      <th>loan_cnt</th>\n",
       "      <th>duration</th>\n",
       "      <th>visit_x</th>\n",
       "      <th>visit_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>354.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>354.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>354.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>354.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>354.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-409d4d47-8c4e-453e-8da8-c932e1e6612a')\"\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-409d4d47-8c4e-453e-8da8-c932e1e6612a 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-409d4d47-8c4e-453e-8da8-c932e1e6612a');\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    date_cd  \\\n",
       "0          248576   2022-05-21 23:41:09         0.0        9 2022-05-21   \n",
       "1          248576   2022-05-21 23:41:09         0.0        9 2022-05-21   \n",
       "2          248576   2022-05-21 23:41:09         0.0        9 2022-05-21   \n",
       "3          248576   2022-05-21 23:41:09         0.0        9 2022-05-21   \n",
       "4          248576   2022-05-21 23:41:10         0.0        9 2022-05-21   \n",
       "\n",
       "   event  loan_cnt  duration  visit_x  visit_y  \n",
       "0    3.0       0.0     354.0      1.0      1.0  \n",
       "1    3.0       0.0     354.0      1.0      1.0  \n",
       "2    3.0       0.0     354.0      1.0      1.0  \n",
       "3    3.0       0.0     354.0      1.0      1.0  \n",
       "4    3.0       0.0     354.0      1.0      1.0  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "loan_visit = pd.merge(left=loan, right=visit, how='left', on=['user_id', 'date_cd'])\n",
    "viewinfo(loan_visit)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "hszs41YiVHn-",
    "outputId": "69289766-eb0d-4629-d1c4-5ab1c880b498"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "22"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan = loan_visit.copy()\n",
    "del loan_visit\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "iBhfV0XxVuTr",
    "outputId": "a452ef90-7682-4656-c60b-3179e950cb48"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['application_id',\n",
       " 'loanapply_insert_time',\n",
       " 'is_applied',\n",
       " 'user_id',\n",
       " 'date_cd',\n",
       " 'event',\n",
       " 'loan_cnt',\n",
       " 'duration',\n",
       " 'visit']"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan.drop(['visit_y'], axis=1, inplace=True)\n",
    "columns = list(loan.columns.values)\n",
    "columns[-1] = 'visit'\n",
    "columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "-tA-gIyLV6BJ"
   },
   "outputs": [],
   "source": [
    "loan.columns = columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 438
    },
    "id": "_6N8MWfRVOrY",
    "outputId": "4146f37a-d161-4226-fb8b-95d0a3522b37"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(417988, 3)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 417988 entries, 0 to 417987\n",
      "Data columns (total 3 columns):\n",
      " #   Column      Non-Null Count   Dtype  \n",
      "---  ------      --------------   -----  \n",
      " 0   user_id     417988 non-null  uint64 \n",
      " 1   is_applied  417988 non-null  float64\n",
      " 2   visit       378037 non-null  float64\n",
      "dtypes: float64(2), uint64(1)\n",
      "memory usage: 9.6 MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-c9435be4-b349-47dc-9974-304d97b50999\">\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>is_applied</th>\n",
       "      <th>visit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.666667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-c9435be4-b349-47dc-9974-304d97b50999')\"\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-c9435be4-b349-47dc-9974-304d97b50999 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-c9435be4-b349-47dc-9974-304d97b50999');\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  is_applied     visit\n",
       "0        9         0.0  1.000000\n",
       "1       11         0.0  1.000000\n",
       "2       11         1.0  1.000000\n",
       "3       14         0.0       NaN\n",
       "4       17         0.0  1.666667"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "## 유저별 평균\n",
    "avg_visit = loan[['user_id', 'is_applied', 'visit']].groupby(by=['user_id', 'is_applied']).mean()\n",
    "avg_visit.reset_index(drop=False, inplace=True)\n",
    "viewinfo(avg_visit)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "_RiW7_H1VprK",
    "outputId": "05f706fe-3d98-4d55-de86-f6da8a1f0375"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 991813/991813 [43:51<00:00, 376.94it/s]\n"
     ]
    }
   ],
   "source": [
    "for idx in tqdm(nulls.index):\n",
    "    tup = nulls.loc[idx]\n",
    "    is_applied, user_id = tup[2], tup[3]\n",
    "    sub = avg_visit[(avg_visit['user_id'] == user_id) & (avg_visit['is_applied'] == is_applied)]\n",
    "    if sub.shape[0] == 0:\n",
    "        continue\n",
    "    loan['visit'].loc[idx] = sub['visit'].values[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 545
    },
    "id": "uISauXeIgge2",
    "outputId": "e7bb3dbb-c583-4f6b-9682-5afd975fba1a"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(13527250, 9)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 13527250 entries, 0 to 13527249\n",
      "Data columns (total 9 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   date_cd                datetime64[ns]\n",
      " 5   event                  float64       \n",
      " 6   loan_cnt               float64       \n",
      " 7   duration               float64       \n",
      " 8   visit                  float64       \n",
      "dtypes: datetime64[ns](2), float64(5), uint32(2)\n",
      "memory usage: 928.8 MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-b38a7cca-d168-4087-b8ec-a685813cfe1b\">\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>date_cd</th>\n",
       "      <th>event</th>\n",
       "      <th>loan_cnt</th>\n",
       "      <th>duration</th>\n",
       "      <th>visit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>354.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>354.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>354.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>354.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>354.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-b38a7cca-d168-4087-b8ec-a685813cfe1b')\"\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-b38a7cca-d168-4087-b8ec-a685813cfe1b 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-b38a7cca-d168-4087-b8ec-a685813cfe1b');\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    date_cd  \\\n",
       "0          248576   2022-05-21 23:41:09         0.0        9 2022-05-21   \n",
       "1          248576   2022-05-21 23:41:09         0.0        9 2022-05-21   \n",
       "2          248576   2022-05-21 23:41:09         0.0        9 2022-05-21   \n",
       "3          248576   2022-05-21 23:41:09         0.0        9 2022-05-21   \n",
       "4          248576   2022-05-21 23:41:10         0.0        9 2022-05-21   \n",
       "\n",
       "   event  loan_cnt  duration  visit  \n",
       "0    3.0       0.0     354.0    1.0  \n",
       "1    3.0       0.0     354.0    1.0  \n",
       "2    3.0       0.0     354.0    1.0  \n",
       "3    3.0       0.0     354.0    1.0  \n",
       "4    3.0       0.0     354.0    1.0  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "viewinfo(loan)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "qXls1APJgjT2",
    "outputId": "2cf38f7e-fa5e-43e4-8933-842e9dc99587"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "application_id                 0\n",
       "loanapply_insert_time          0\n",
       "is_applied               3257239\n",
       "user_id                        0\n",
       "date_cd                        0\n",
       "event                     703483\n",
       "loan_cnt                  703483\n",
       "duration                  703483\n",
       "visit                     703483\n",
       "dtype: int64"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "rjK4Y-ly0WIr"
   },
   "source": [
    "# Event Count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 878
    },
    "id": "LwYR9ICb0mHQ",
    "outputId": "58952e5d-ec85-413d-b6ab-8c03f86c06bb"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1863305, 3)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1863305 entries, 0 to 1863304\n",
      "Data columns (total 3 columns):\n",
      " #   Column   Dtype         \n",
      "---  ------   -----         \n",
      " 0   user_id  int64         \n",
      " 1   date_cd  datetime64[ns]\n",
      " 2   event    int64         \n",
      "dtypes: datetime64[ns](1), int64(2)\n",
      "memory usage: 42.6 MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-52f57ee6-282a-48ed-9a0c-3a90bac2023c\">\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>date_cd</th>\n",
       "      <th>event</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td>2022-03-24</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11</td>\n",
       "      <td>2022-04-20</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11</td>\n",
       "      <td>2022-06-09</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11</td>\n",
       "      <td>2022-06-13</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-52f57ee6-282a-48ed-9a0c-3a90bac2023c')\"\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-52f57ee6-282a-48ed-9a0c-3a90bac2023c 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-52f57ee6-282a-48ed-9a0c-3a90bac2023c');\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    date_cd  event\n",
       "0        9 2022-05-21      3\n",
       "1       11 2022-03-24     12\n",
       "2       11 2022-04-20     21\n",
       "3       11 2022-06-09      5\n",
       "4       11 2022-06-13      5"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "(1863305, 3)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1863305 entries, 0 to 1863304\n",
      "Data columns (total 3 columns):\n",
      " #   Column    Dtype         \n",
      "---  ------    -----         \n",
      " 0   user_id   int64         \n",
      " 1   date_cd   datetime64[ns]\n",
      " 2   loan_cnt  int64         \n",
      "dtypes: datetime64[ns](1), int64(2)\n",
      "memory usage: 42.6 MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-d2b093c2-88b5-4c89-9248-15dfadd4b227\">\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>date_cd</th>\n",
       "      <th>loan_cnt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td>2022-03-24</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11</td>\n",
       "      <td>2022-04-20</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11</td>\n",
       "      <td>2022-06-09</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11</td>\n",
       "      <td>2022-06-13</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-d2b093c2-88b5-4c89-9248-15dfadd4b227')\"\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-d2b093c2-88b5-4c89-9248-15dfadd4b227 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-d2b093c2-88b5-4c89-9248-15dfadd4b227');\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    date_cd  loan_cnt\n",
       "0        9 2022-05-21         0\n",
       "1       11 2022-03-24         3\n",
       "2       11 2022-04-20         7\n",
       "3       11 2022-06-09         3\n",
       "4       11 2022-06-13         3"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "app_event = pd.read_csv(\"../Data/event_cnt.csv\", engine='c', \n",
    "                        low_memory=True, parse_dates=['date_cd'])\n",
    "loan_apply_count = pd.read_csv(\"../Data/loan_apply_cnt.csv\", \n",
    "                               engine='c', low_memory=True, parse_dates=['date_cd'])\n",
    "\n",
    "viewinfo(app_event)\n",
    "print()\n",
    "viewinfo(loan_apply_count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "EOzvHiMFFhOH",
    "outputId": "6b3c5575-729a-41ca-a554-14c38c185dda"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "305\n",
      "152\n"
     ]
    }
   ],
   "source": [
    "print(app_event['event'].max())\n",
    "print(loan_apply_count['loan_cnt'].max())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "eCYxarQqGMpB",
    "outputId": "4e2d66f7-66f2-4a86-dc0e-44132c438d43"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "None\n"
     ]
    }
   ],
   "source": [
    "for i, (event, loan_cnt) in enumerate(zip(app_event['event'], loan_apply_count['loan_cnt'])):\n",
    "    if event < loan_cnt:\n",
    "        print(app_event.iloc[i])\n",
    "print(\"None\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 456
    },
    "id": "ugj4NP1jEp1E",
    "outputId": "bce64934-d284-4745-e4b4-b79eab483450"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1863305, 4)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 1863305 entries, 0 to 1863304\n",
      "Data columns (total 4 columns):\n",
      " #   Column    Dtype         \n",
      "---  ------    -----         \n",
      " 0   user_id   int64         \n",
      " 1   date_cd   datetime64[ns]\n",
      " 2   event     int64         \n",
      " 3   loan_cnt  int64         \n",
      "dtypes: datetime64[ns](1), int64(3)\n",
      "memory usage: 71.1 MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-c4e6c308-0efc-4846-994d-a547f4aac88a\">\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>date_cd</th>\n",
       "      <th>event</th>\n",
       "      <th>loan_cnt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td>2022-03-24</td>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11</td>\n",
       "      <td>2022-04-20</td>\n",
       "      <td>21</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11</td>\n",
       "      <td>2022-06-09</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11</td>\n",
       "      <td>2022-06-13</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-c4e6c308-0efc-4846-994d-a547f4aac88a')\"\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-c4e6c308-0efc-4846-994d-a547f4aac88a 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-c4e6c308-0efc-4846-994d-a547f4aac88a');\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    date_cd  event  loan_cnt\n",
       "0        9 2022-05-21      3         0\n",
       "1       11 2022-03-24     12         3\n",
       "2       11 2022-04-20     21         7\n",
       "3       11 2022-06-09      5         3\n",
       "4       11 2022-06-13      5         3"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "cnt_merge = pd.merge(left=app_event, right=loan_apply_count, on=['user_id', 'date_cd'], how='left')\n",
    "# cnt_merge.fillna(0)\n",
    "viewinfo(cnt_merge)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "g7gIX-1IGz5Q",
    "outputId": "9286b07c-83fe-468f-eb54-481e8882b3bd"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "88"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del app_event, loan_apply_count\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 510
    },
    "id": "k66Bo68bG8jS",
    "outputId": "b4dad03b-a10d-429a-bdd5-fdf3ff7c4c51"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(13527250, 7)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 13527250 entries, 0 to 13527249\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   date_cd                datetime64[ns]\n",
      " 5   event                  float64       \n",
      " 6   loan_cnt               float64       \n",
      "dtypes: datetime64[ns](2), float64(3), uint32(2)\n",
      "memory usage: 722.4 MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-020ff4e7-31a1-4371-be10-2e7d885472b3\">\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>date_cd</th>\n",
       "      <th>event</th>\n",
       "      <th>loan_cnt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>248576</td>\n",
       "      <td>2022-05-21 23:41:10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-05-21</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-020ff4e7-31a1-4371-be10-2e7d885472b3')\"\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-020ff4e7-31a1-4371-be10-2e7d885472b3 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-020ff4e7-31a1-4371-be10-2e7d885472b3');\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    date_cd  \\\n",
       "0          248576   2022-05-21 23:41:09         0.0        9 2022-05-21   \n",
       "1          248576   2022-05-21 23:41:09         0.0        9 2022-05-21   \n",
       "2          248576   2022-05-21 23:41:09         0.0        9 2022-05-21   \n",
       "3          248576   2022-05-21 23:41:09         0.0        9 2022-05-21   \n",
       "4          248576   2022-05-21 23:41:10         0.0        9 2022-05-21   \n",
       "\n",
       "   event  loan_cnt  \n",
       "0    3.0       0.0  \n",
       "1    3.0       0.0  \n",
       "2    3.0       0.0  \n",
       "3    3.0       0.0  \n",
       "4    3.0       0.0  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "loan_cnt_merge = pd.merge(left=loan, right=cnt_merge, on=['user_id', 'date_cd'], how='left')\n",
    "viewinfo(loan_cnt_merge)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "VNAye-GuIPQx",
    "outputId": "e77123ae-9c11-41a9-9652-aec6a314edbc"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "44"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "del loan\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "MXNI4yImIhJo",
    "outputId": "1e2c94ec-3ab6-4865-efe9-72c97734032a"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "application_id                 0\n",
       "loanapply_insert_time          0\n",
       "is_applied               3257239\n",
       "user_id                        0\n",
       "date_cd                        0\n",
       "event                     991813\n",
       "loan_cnt                  991813\n",
       "dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan_cnt_merge.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "f1R-uRnrJCbY",
    "outputId": "c268010b-4893-4f83-fc7c-e94f3ca45d11"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "44"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan = loan_cnt_merge.copy()\n",
    "del loan_cnt_merge\n",
    "gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 456
    },
    "id": "ZYybwN4wI1G7",
    "outputId": "33abd66d-7729-4d78-9717-fe904e3294dc"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(417988, 4)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 417988 entries, 0 to 417987\n",
      "Data columns (total 4 columns):\n",
      " #   Column      Non-Null Count   Dtype  \n",
      "---  ------      --------------   -----  \n",
      " 0   user_id     417988 non-null  uint64 \n",
      " 1   is_applied  417988 non-null  float64\n",
      " 2   event       378037 non-null  float64\n",
      " 3   loan_cnt    378037 non-null  float64\n",
      "dtypes: float64(3), uint64(1)\n",
      "memory usage: 12.8 MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-7fafa5b4-c110-42e1-b8da-41009249dc0e\">\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>is_applied</th>\n",
       "      <th>event</th>\n",
       "      <th>loan_cnt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td>0.0</td>\n",
       "      <td>16.595745</td>\n",
       "      <td>5.042553</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11</td>\n",
       "      <td>1.0</td>\n",
       "      <td>15.000000</td>\n",
       "      <td>4.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>7.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-7fafa5b4-c110-42e1-b8da-41009249dc0e')\"\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-7fafa5b4-c110-42e1-b8da-41009249dc0e 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-7fafa5b4-c110-42e1-b8da-41009249dc0e');\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  is_applied      event  loan_cnt\n",
       "0        9         0.0   3.000000  0.000000\n",
       "1       11         0.0  16.595745  5.042553\n",
       "2       11         1.0  15.000000  4.333333\n",
       "3       14         0.0        NaN       NaN\n",
       "4       17         0.0  12.000000  7.000000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "## 유저별 평균\n",
    "avg_events = loan[['user_id', 'is_applied', 'event', 'loan_cnt']].groupby(by=['user_id', 'is_applied']).mean()\n",
    "avg_events.reset_index(drop=False, inplace=True)\n",
    "viewinfo(avg_events)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 510
    },
    "id": "ZaZMd8bFKReR",
    "outputId": "f20bd082-a9d0-4749-afe0-49cd36d46298"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(991813, 7)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 991813 entries, 155 to 13527249\n",
      "Data columns (total 7 columns):\n",
      " #   Column                 Non-Null Count   Dtype         \n",
      "---  ------                 --------------   -----         \n",
      " 0   application_id         991813 non-null  uint32        \n",
      " 1   loanapply_insert_time  991813 non-null  datetime64[ns]\n",
      " 2   is_applied             820693 non-null  float64       \n",
      " 3   user_id                991813 non-null  uint32        \n",
      " 4   date_cd                991813 non-null  datetime64[ns]\n",
      " 5   event                  0 non-null       float64       \n",
      " 6   loan_cnt               0 non-null       float64       \n",
      "dtypes: datetime64[ns](2), float64(3), uint32(2)\n",
      "memory usage: 53.0 MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-51b7b655-5e1a-44c2-9d22-b4b449759348\">\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>date_cd</th>\n",
       "      <th>event</th>\n",
       "      <th>loan_cnt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>1508745</td>\n",
       "      <td>2022-04-18 14:37:10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14</td>\n",
       "      <td>2022-04-18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>1508745</td>\n",
       "      <td>2022-04-18 14:37:11</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14</td>\n",
       "      <td>2022-04-18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>1854180</td>\n",
       "      <td>2022-05-06 17:10:55</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19</td>\n",
       "      <td>2022-05-06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163</th>\n",
       "      <td>513514</td>\n",
       "      <td>2022-03-13 20:17:19</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20</td>\n",
       "      <td>2022-03-13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164</th>\n",
       "      <td>513514</td>\n",
       "      <td>2022-03-13 20:17:19</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20</td>\n",
       "      <td>2022-03-13</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-51b7b655-5e1a-44c2-9d22-b4b449759348')\"\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-51b7b655-5e1a-44c2-9d22-b4b449759348 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-51b7b655-5e1a-44c2-9d22-b4b449759348');\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    date_cd  \\\n",
       "155         1508745   2022-04-18 14:37:10         0.0       14 2022-04-18   \n",
       "156         1508745   2022-04-18 14:37:11         0.0       14 2022-04-18   \n",
       "162         1854180   2022-05-06 17:10:55         0.0       19 2022-05-06   \n",
       "163          513514   2022-03-13 20:17:19         1.0       20 2022-03-13   \n",
       "164          513514   2022-03-13 20:17:19         0.0       20 2022-03-13   \n",
       "\n",
       "     event  loan_cnt  \n",
       "155    NaN       NaN  \n",
       "156    NaN       NaN  \n",
       "162    NaN       NaN  \n",
       "163    NaN       NaN  \n",
       "164    NaN       NaN  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "nulls = loan[(loan['event'].isna()) | (loan['loan_cnt'].isna())]\n",
    "viewinfo(nulls)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "kO0W8vK8MkYH",
    "outputId": "ff4b2d93-a49d-436e-be96-f8dbc0d3d017"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 991813/991813 [46:17<00:00, 357.06it/s]\n"
     ]
    }
   ],
   "source": [
    "for idx in tqdm(nulls.index):\n",
    "    _, _, is_applied, user_id, _, _, _ = nulls.loc[idx]\n",
    "    sub = avg_events[(avg_events['user_id'] == user_id) & (avg_events['is_applied'] == is_applied)]\n",
    "    # print(user_id, is_applied)\n",
    "    # display(sub)\n",
    "    if sub.shape[0] == 0:\n",
    "        continue\n",
    "    loan['event'].loc[idx] = sub['event'].values[0]\n",
    "    loan['loan_cnt'].loc[idx] = sub['loan_cnt'].values[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "z6K8Wra-LqeR",
    "outputId": "838d090a-baf5-47db-da69-2286632f827f"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "application_id                        513514\n",
       "loanapply_insert_time    2022-03-13 20:17:19\n",
       "is_applied                               1.0\n",
       "user_id                                   20\n",
       "date_cd                  2022-03-13 00:00:00\n",
       "event                                   10.5\n",
       "loan_cnt                                 5.5\n",
       "Name: 163, dtype: object"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "loan.iloc[163]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Deployment CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "vcYkjTAUwx57"
   },
   "outputs": [],
   "source": [
    "loan.to_csv(\"../Data/loan_with_new_feats.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "4gaJA1pD0YaN"
   },
   "source": [
    "# Application path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 842
    },
    "id": "sk2IC91JF7Yp",
    "outputId": "b6b5c471-5c39-45ec-b1c4-b44ff9093e50"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(354998, 2)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 354998 entries, 0 to 354997\n",
      "Data columns (total 2 columns):\n",
      " #   Column          Non-Null Count   Dtype \n",
      "---  ------          --------------   ----- \n",
      " 0   application_id  354998 non-null  int64 \n",
      " 1   path            354998 non-null  object\n",
      "dtypes: int64(1), object(1)\n",
      "memory usage: 5.4+ MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-402071e7-a174-4d01-b4d6-783904f04aec\">\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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14</td>\n",
       "      <td>('GetCreditInfo', 'GetCreditInfo')</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17</td>\n",
       "      <td>('OpenApp', 'ViewLoanApplyIntro', 'CompleteIDC...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>28</td>\n",
       "      <td>('OpenApp', 'StartLoanApply', 'ViewLoanApplyIn...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>34</td>\n",
       "      <td>('OpenApp', 'StartLoanApply', 'ViewLoanApplyIn...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>36</td>\n",
       "      <td>('OpenApp', 'Login', 'GetCreditInfo', 'StartLo...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-402071e7-a174-4d01-b4d6-783904f04aec')\"\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-402071e7-a174-4d01-b4d6-783904f04aec 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-402071e7-a174-4d01-b4d6-783904f04aec');\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              14                 ('GetCreditInfo', 'GetCreditInfo')\n",
       "1              17  ('OpenApp', 'ViewLoanApplyIntro', 'CompleteIDC...\n",
       "2              28  ('OpenApp', 'StartLoanApply', 'ViewLoanApplyIn...\n",
       "3              34  ('OpenApp', 'StartLoanApply', 'ViewLoanApplyIn...\n",
       "4              36  ('OpenApp', 'Login', 'GetCreditInfo', 'StartLo..."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "(323291, 2)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 323291 entries, 0 to 323290\n",
      "Data columns (total 2 columns):\n",
      " #   Column          Non-Null Count   Dtype \n",
      "---  ------          --------------   ----- \n",
      " 0   application_id  323291 non-null  int64 \n",
      " 1   path            323291 non-null  object\n",
      "dtypes: int64(1), object(1)\n",
      "memory usage: 4.9+ MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-bcbf4cb2-fa9b-4f43-977b-d6462154b026\">\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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>('OpenApp', 'StartLoanApply', 'ViewLoanApplyIn...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>('OpenApp', 'Login', 'StartLoanApply', 'ViewLo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>('OpenApp', 'Login', 'UseLoanManage', 'GetCred...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20</td>\n",
       "      <td>('OpenApp', 'Login', 'GetCreditInfo', 'UseLoan...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>26</td>\n",
       "      <td>('OpenApp', 'Login', 'GetCreditInfo', 'StartLo...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-bcbf4cb2-fa9b-4f43-977b-d6462154b026')\"\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-bcbf4cb2-fa9b-4f43-977b-d6462154b026 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-bcbf4cb2-fa9b-4f43-977b-d6462154b026');\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               1  ('OpenApp', 'StartLoanApply', 'ViewLoanApplyIn...\n",
       "1               2  ('OpenApp', 'Login', 'StartLoanApply', 'ViewLo...\n",
       "2               6  ('OpenApp', 'Login', 'UseLoanManage', 'GetCred...\n",
       "3              20  ('OpenApp', 'Login', 'GetCreditInfo', 'UseLoan...\n",
       "4              26  ('OpenApp', 'Login', 'GetCreditInfo', 'StartLo..."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "app_path =  pd.read_csv(\"../Data/path_applied_user.csv\", engine='c', low_memory=True)\n",
    "noapp_path =  pd.read_csv(\"../Data/path_no_applied_user.csv\", engine='c', low_memory=True)\n",
    "\n",
    "viewinfo(app_path)\n",
    "print()\n",
    "viewinfo(noapp_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 420
    },
    "id": "1zNxJS5ZW69V",
    "outputId": "6057b114-5618-4521-9d3a-0fe6b5779e79"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(678289, 2)\n",
      "\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 678289 entries, 0 to 323290\n",
      "Data columns (total 2 columns):\n",
      " #   Column          Non-Null Count   Dtype \n",
      "---  ------          --------------   ----- \n",
      " 0   application_id  678289 non-null  int64 \n",
      " 1   path            678289 non-null  object\n",
      "dtypes: int64(1), object(1)\n",
      "memory usage: 15.5+ MB\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-1d015c47-862b-4b42-87ea-ed82e906810e\">\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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14</td>\n",
       "      <td>('GetCreditInfo', 'GetCreditInfo')</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17</td>\n",
       "      <td>('OpenApp', 'ViewLoanApplyIntro', 'CompleteIDC...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>28</td>\n",
       "      <td>('OpenApp', 'StartLoanApply', 'ViewLoanApplyIn...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>34</td>\n",
       "      <td>('OpenApp', 'StartLoanApply', 'ViewLoanApplyIn...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>36</td>\n",
       "      <td>('OpenApp', 'Login', 'GetCreditInfo', 'StartLo...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-1d015c47-862b-4b42-87ea-ed82e906810e')\"\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-1d015c47-862b-4b42-87ea-ed82e906810e 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-1d015c47-862b-4b42-87ea-ed82e906810e');\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              14                 ('GetCreditInfo', 'GetCreditInfo')\n",
       "1              17  ('OpenApp', 'ViewLoanApplyIntro', 'CompleteIDC...\n",
       "2              28  ('OpenApp', 'StartLoanApply', 'ViewLoanApplyIn...\n",
       "3              34  ('OpenApp', 'StartLoanApply', 'ViewLoanApplyIn...\n",
       "4              36  ('OpenApp', 'Login', 'GetCreditInfo', 'StartLo..."
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "paths = pd.concat([app_path, noapp_path], axis=0)\n",
    "paths.reset_index(drop=True)\n",
    "viewinfo(paths)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 206
    },
    "id": "ZZVYkTgwXRjV",
    "outputId": "e6bc4793-b34f-4a83-a86e-30b471f5d7ce"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-7fc915f1-08b3-48d0-9b5f-a2fd18bf5029\">\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>path_group</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14</td>\n",
       "      <td>('GetCreditInfo', 'GetCreditInfo')</td>\n",
       "      <td>4541</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>17</td>\n",
       "      <td>('OpenApp', 'ViewLoanApplyIntro', 'CompleteIDC...</td>\n",
       "      <td>157300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>28</td>\n",
       "      <td>('OpenApp', 'StartLoanApply', 'ViewLoanApplyIn...</td>\n",
       "      <td>143686</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>34</td>\n",
       "      <td>('OpenApp', 'StartLoanApply', 'ViewLoanApplyIn...</td>\n",
       "      <td>141689</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>36</td>\n",
       "      <td>('OpenApp', 'Login', 'GetCreditInfo', 'StartLo...</td>\n",
       "      <td>53296</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-7fc915f1-08b3-48d0-9b5f-a2fd18bf5029')\"\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-7fc915f1-08b3-48d0-9b5f-a2fd18bf5029 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-7fc915f1-08b3-48d0-9b5f-a2fd18bf5029');\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              14                 ('GetCreditInfo', 'GetCreditInfo')   \n",
       "1              17  ('OpenApp', 'ViewLoanApplyIntro', 'CompleteIDC...   \n",
       "2              28  ('OpenApp', 'StartLoanApply', 'ViewLoanApplyIn...   \n",
       "3              34  ('OpenApp', 'StartLoanApply', 'ViewLoanApplyIn...   \n",
       "4              36  ('OpenApp', 'Login', 'GetCreditInfo', 'StartLo...   \n",
       "\n",
       "   path_group  \n",
       "0        4541  \n",
       "1      157300  \n",
       "2      143686  \n",
       "3      141689  \n",
       "4       53296  "
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sklearn.preprocessing import LabelEncoder\n",
    "\n",
    "le = LabelEncoder()\n",
    "le.fit(paths['path'])\n",
    "paths['path_group'] = le.transform(paths['path'])\n",
    "\n",
    "paths.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "v-eB1tI3X5c5",
    "outputId": "2d8fcf77-265c-4715-b16f-93bfc16faf9c"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "171812"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "paths['path_group'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "stn2qnBeF9me"
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "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
}