step1_理賠再購屬性合併.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **套件匯入**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import calendar\n",
    "import seaborn as sns\n",
    "import datetime as dt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **理賠資料**\n",
    "## 將兩張理賠表匯入"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.read_excel(\"CLAIM_ACCT_FIN_1.xlsx\")\n",
    "df2 = pd.read_excel(\"CLAIM_ACCT_FIN_2.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 將兩張理賠表合併"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.index = [120000+i for i in range(len(df2))]\n",
    "frames = [df1,df2]\n",
    "df = pd.concat(frames)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>INJURED_RK</th>\n",
       "      <th>Claim_RK</th>\n",
       "      <th>Policy_RK</th>\n",
       "      <th>BundleSubtype2</th>\n",
       "      <th>illness_code</th>\n",
       "      <th>illness_desc</th>\n",
       "      <th>DiagnosisCode_DESC</th>\n",
       "      <th>claim_settle_dt</th>\n",
       "      <th>REIMBURSED_YR_TW</th>\n",
       "      <th>INSURED_RK</th>\n",
       "      <th>POLICY_HOLDER_RK</th>\n",
       "      <th>MATURITY_BENEFICIARY_RK</th>\n",
       "      <th>DEATH_BENEFICIARY_RK</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>14</td>\n",
       "      <td>4034</td>\n",
       "      <td>72224</td>\n",
       "      <td>5.N疾病醫療</td>\n",
       "      <td>A09</td>\n",
       "      <td>感染性胃腸炎及大腸炎</td>\n",
       "      <td>01.傳染病和寄生蟲病</td>\n",
       "      <td>2017-03-10</td>\n",
       "      <td>8505.00</td>\n",
       "      <td>14</td>\n",
       "      <td>212635</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14</td>\n",
       "      <td>62576</td>\n",
       "      <td>72224</td>\n",
       "      <td>4.C重大疾病</td>\n",
       "      <td>N19</td>\n",
       "      <td>腎衰竭</td>\n",
       "      <td>14.泌尿生殖系統疾病</td>\n",
       "      <td>2016-08-19</td>\n",
       "      <td>4200.00</td>\n",
       "      <td>14</td>\n",
       "      <td>212635</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>14</td>\n",
       "      <td>90970</td>\n",
       "      <td>72224</td>\n",
       "      <td>5.N疾病醫療</td>\n",
       "      <td>H26.9</td>\n",
       "      <td>白內障</td>\n",
       "      <td>07.眼和附器疾病</td>\n",
       "      <td>2016-12-04</td>\n",
       "      <td>29102.85</td>\n",
       "      <td>14</td>\n",
       "      <td>212635</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>14</td>\n",
       "      <td>230243</td>\n",
       "      <td>72224</td>\n",
       "      <td>1.D身故給付</td>\n",
       "      <td>V99</td>\n",
       "      <td>運輸意外事故</td>\n",
       "      <td>20.疾病和死亡的外因</td>\n",
       "      <td>2017-03-10</td>\n",
       "      <td>2131618.65</td>\n",
       "      <td>14</td>\n",
       "      <td>212635</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>24</td>\n",
       "      <td>157920</td>\n",
       "      <td>91033</td>\n",
       "      <td>5.N疾病醫療</td>\n",
       "      <td>R19.7</td>\n",
       "      <td>腹瀉,未特定</td>\n",
       "      <td>18.症狀異常所見，不可歸類</td>\n",
       "      <td>2017-06-29</td>\n",
       "      <td>32452.35</td>\n",
       "      <td>30372</td>\n",
       "      <td>70614</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>234423</th>\n",
       "      <td>249733</td>\n",
       "      <td>30121</td>\n",
       "      <td>166605</td>\n",
       "      <td>6.N意外醫療</td>\n",
       "      <td>Y99.8</td>\n",
       "      <td>其他外因狀態</td>\n",
       "      <td>99.不知道不想猜</td>\n",
       "      <td>2015-07-09</td>\n",
       "      <td>33600.00</td>\n",
       "      <td>249733</td>\n",
       "      <td>250207</td>\n",
       "      <td>NaN</td>\n",
       "      <td>250207.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>234424</th>\n",
       "      <td>249733</td>\n",
       "      <td>86463</td>\n",
       "      <td>166605</td>\n",
       "      <td>5.N疾病醫療</td>\n",
       "      <td>D33</td>\n",
       "      <td>大腦及中樞神經系統其他部位之良性腫瘤</td>\n",
       "      <td>02.腫瘤</td>\n",
       "      <td>2016-11-02</td>\n",
       "      <td>231.00</td>\n",
       "      <td>249733</td>\n",
       "      <td>250207</td>\n",
       "      <td>NaN</td>\n",
       "      <td>250207.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>234425</th>\n",
       "      <td>249733</td>\n",
       "      <td>109718</td>\n",
       "      <td>166605</td>\n",
       "      <td>6.N意外醫療</td>\n",
       "      <td>Z04.2</td>\n",
       "      <td>來院接受對工作意外後之檢查及觀察</td>\n",
       "      <td>21.影響健康狀態與保健機構接觸的</td>\n",
       "      <td>2017-02-11</td>\n",
       "      <td>1163.40</td>\n",
       "      <td>249733</td>\n",
       "      <td>250207</td>\n",
       "      <td>NaN</td>\n",
       "      <td>250207.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>234426</th>\n",
       "      <td>249733</td>\n",
       "      <td>204841</td>\n",
       "      <td>166605</td>\n",
       "      <td>6.N意外醫療</td>\n",
       "      <td>V23</td>\n",
       "      <td>摩拖車騎士與汽車，小貨車或箱型車碰撞受傷</td>\n",
       "      <td>20.疾病和死亡的外因</td>\n",
       "      <td>2017-10-29</td>\n",
       "      <td>12478.20</td>\n",
       "      <td>249733</td>\n",
       "      <td>250207</td>\n",
       "      <td>NaN</td>\n",
       "      <td>250207.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>234427</th>\n",
       "      <td>249795</td>\n",
       "      <td>189792</td>\n",
       "      <td>97319</td>\n",
       "      <td>5.N疾病醫療</td>\n",
       "      <td>K05</td>\n",
       "      <td>齒齦炎及牙周疾病</td>\n",
       "      <td>11.消化系統疾病</td>\n",
       "      <td>2017-09-20</td>\n",
       "      <td>3307.50</td>\n",
       "      <td>249795</td>\n",
       "      <td>249795</td>\n",
       "      <td>249795.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>234428 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        INJURED_RK  Claim_RK  Policy_RK BundleSubtype2 illness_code  \\\n",
       "0               14      4034      72224        5.N疾病醫療          A09   \n",
       "1               14     62576      72224        4.C重大疾病          N19   \n",
       "2               14     90970      72224        5.N疾病醫療        H26.9   \n",
       "3               14    230243      72224        1.D身故給付          V99   \n",
       "4               24    157920      91033        5.N疾病醫療        R19.7   \n",
       "...            ...       ...        ...            ...          ...   \n",
       "234423      249733     30121     166605        6.N意外醫療        Y99.8   \n",
       "234424      249733     86463     166605        5.N疾病醫療          D33   \n",
       "234425      249733    109718     166605        6.N意外醫療        Z04.2   \n",
       "234426      249733    204841     166605        6.N意外醫療          V23   \n",
       "234427      249795    189792      97319        5.N疾病醫療          K05   \n",
       "\n",
       "                illness_desc DiagnosisCode_DESC claim_settle_dt  \\\n",
       "0                 感染性胃腸炎及大腸炎        01.傳染病和寄生蟲病      2017-03-10   \n",
       "1                        腎衰竭        14.泌尿生殖系統疾病      2016-08-19   \n",
       "2                        白內障          07.眼和附器疾病      2016-12-04   \n",
       "3                     運輸意外事故        20.疾病和死亡的外因      2017-03-10   \n",
       "4                     腹瀉,未特定     18.症狀異常所見，不可歸類      2017-06-29   \n",
       "...                      ...                ...             ...   \n",
       "234423                其他外因狀態          99.不知道不想猜      2015-07-09   \n",
       "234424    大腦及中樞神經系統其他部位之良性腫瘤              02.腫瘤      2016-11-02   \n",
       "234425      來院接受對工作意外後之檢查及觀察  21.影響健康狀態與保健機構接觸的      2017-02-11   \n",
       "234426  摩拖車騎士與汽車，小貨車或箱型車碰撞受傷        20.疾病和死亡的外因      2017-10-29   \n",
       "234427              齒齦炎及牙周疾病          11.消化系統疾病      2017-09-20   \n",
       "\n",
       "        REIMBURSED_YR_TW  INSURED_RK  POLICY_HOLDER_RK  \\\n",
       "0                8505.00          14            212635   \n",
       "1                4200.00          14            212635   \n",
       "2               29102.85          14            212635   \n",
       "3             2131618.65          14            212635   \n",
       "4               32452.35       30372             70614   \n",
       "...                  ...         ...               ...   \n",
       "234423          33600.00      249733            250207   \n",
       "234424            231.00      249733            250207   \n",
       "234425           1163.40      249733            250207   \n",
       "234426          12478.20      249733            250207   \n",
       "234427           3307.50      249795            249795   \n",
       "\n",
       "        MATURITY_BENEFICIARY_RK  DEATH_BENEFICIARY_RK  \n",
       "0                           NaN                   NaN  \n",
       "1                           NaN                   NaN  \n",
       "2                           NaN                   NaN  \n",
       "3                           NaN                   NaN  \n",
       "4                           NaN                   NaN  \n",
       "...                         ...                   ...  \n",
       "234423                      NaN              250207.0  \n",
       "234424                      NaN              250207.0  \n",
       "234425                      NaN              250207.0  \n",
       "234426                      NaN              250207.0  \n",
       "234427                 249795.0                   NaN  \n",
       "\n",
       "[234428 rows x 13 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 在新的理賠表中做運算，產生新的資料欄"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3 = df.set_index(['POLICY_HOLDER_RK','Policy_RK']).sort_values(['POLICY_HOLDER_RK','Policy_RK','claim_settle_dt'])\n",
    "df3['計數'] = 1\n",
    "df3['累積理賠金額'] = df3.groupby('Policy_RK')['REIMBURSED_YR_TW'].transform('cumsum')\n",
    "df3['初次理賠時間'] = df3.groupby('Policy_RK')['claim_settle_dt'].transform(min)\n",
    "df3['累積理賠次數'] = df3.groupby('Policy_RK')['計數'].transform('cumsum')\n",
    "df3 = df3.drop(columns=['計數'])\n",
    "df3['結案後120天'] = df3['claim_settle_dt'] + dt.timedelta(days=120)\n",
    "df3['結案後180天'] = df3['claim_settle_dt'] + dt.timedelta(days=180)\n",
    "df3['結案後360天'] = df3['claim_settle_dt'] + dt.timedelta(days=360)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 將處理完的理賠檔存檔"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3.to_excel('ready_CLAIM_ACCT_FIN.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 213,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df3 = df3.loc[~df3.index.duplicated(keep='last')]\n",
    "# df3 = df3.groupby(level=df3.index.names)  \n",
    "# df3 = df3.last()\n",
    "# df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 查看某位要保人的理賠資訊"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>INJURED_RK</th>\n",
       "      <th>Claim_RK</th>\n",
       "      <th>BundleSubtype2</th>\n",
       "      <th>illness_code</th>\n",
       "      <th>illness_desc</th>\n",
       "      <th>DiagnosisCode_DESC</th>\n",
       "      <th>claim_settle_dt</th>\n",
       "      <th>REIMBURSED_YR_TW</th>\n",
       "      <th>INSURED_RK</th>\n",
       "      <th>MATURITY_BENEFICIARY_RK</th>\n",
       "      <th>DEATH_BENEFICIARY_RK</th>\n",
       "      <th>累積理賠金額</th>\n",
       "      <th>初次理賠時間</th>\n",
       "      <th>累積理賠次數</th>\n",
       "      <th>結案後120天</th>\n",
       "      <th>結案後180天</th>\n",
       "      <th>結案後360天</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Policy_RK</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>105429</th>\n",
       "      <td>130950</td>\n",
       "      <td>160458</td>\n",
       "      <td>5.N疾病醫療</td>\n",
       "      <td>C57</td>\n",
       "      <td>其他及未明示女性生殖器官之惡性腫瘤</td>\n",
       "      <td>02.腫瘤</td>\n",
       "      <td>2017-07-05</td>\n",
       "      <td>16800.0</td>\n",
       "      <td>130950</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>202650.0</td>\n",
       "      <td>2015-09-04</td>\n",
       "      <td>21</td>\n",
       "      <td>2017-11-02</td>\n",
       "      <td>2018-01-01</td>\n",
       "      <td>2018-06-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105429</th>\n",
       "      <td>130950</td>\n",
       "      <td>169555</td>\n",
       "      <td>5.N疾病醫療</td>\n",
       "      <td>C57</td>\n",
       "      <td>其他及未明示女性生殖器官之惡性腫瘤</td>\n",
       "      <td>02.腫瘤</td>\n",
       "      <td>2017-07-28</td>\n",
       "      <td>16800.0</td>\n",
       "      <td>130950</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>219450.0</td>\n",
       "      <td>2015-09-04</td>\n",
       "      <td>22</td>\n",
       "      <td>2017-11-25</td>\n",
       "      <td>2018-01-24</td>\n",
       "      <td>2018-07-23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105429</th>\n",
       "      <td>130950</td>\n",
       "      <td>178279</td>\n",
       "      <td>5.N疾病醫療</td>\n",
       "      <td>C57</td>\n",
       "      <td>其他及未明示女性生殖器官之惡性腫瘤</td>\n",
       "      <td>02.腫瘤</td>\n",
       "      <td>2017-08-19</td>\n",
       "      <td>23100.0</td>\n",
       "      <td>130950</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>242550.0</td>\n",
       "      <td>2015-09-04</td>\n",
       "      <td>23</td>\n",
       "      <td>2017-12-17</td>\n",
       "      <td>2018-02-15</td>\n",
       "      <td>2018-08-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105429</th>\n",
       "      <td>130950</td>\n",
       "      <td>187467</td>\n",
       "      <td>5.N疾病醫療</td>\n",
       "      <td>C57</td>\n",
       "      <td>其他及未明示女性生殖器官之惡性腫瘤</td>\n",
       "      <td>02.腫瘤</td>\n",
       "      <td>2017-09-13</td>\n",
       "      <td>30450.0</td>\n",
       "      <td>130950</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>273000.0</td>\n",
       "      <td>2015-09-04</td>\n",
       "      <td>24</td>\n",
       "      <td>2018-01-11</td>\n",
       "      <td>2018-03-12</td>\n",
       "      <td>2018-09-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105429</th>\n",
       "      <td>130950</td>\n",
       "      <td>222326</td>\n",
       "      <td>5.N疾病醫療</td>\n",
       "      <td>C57</td>\n",
       "      <td>其他及未明示女性生殖器官之惡性腫瘤</td>\n",
       "      <td>02.腫瘤</td>\n",
       "      <td>2017-12-14</td>\n",
       "      <td>18900.0</td>\n",
       "      <td>130950</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>291900.0</td>\n",
       "      <td>2015-09-04</td>\n",
       "      <td>25</td>\n",
       "      <td>2018-04-13</td>\n",
       "      <td>2018-06-12</td>\n",
       "      <td>2018-12-09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           INJURED_RK  Claim_RK BundleSubtype2 illness_code  \\\n",
       "Policy_RK                                                     \n",
       "105429         130950    160458        5.N疾病醫療          C57   \n",
       "105429         130950    169555        5.N疾病醫療          C57   \n",
       "105429         130950    178279        5.N疾病醫療          C57   \n",
       "105429         130950    187467        5.N疾病醫療          C57   \n",
       "105429         130950    222326        5.N疾病醫療          C57   \n",
       "\n",
       "                illness_desc DiagnosisCode_DESC claim_settle_dt  \\\n",
       "Policy_RK                                                         \n",
       "105429     其他及未明示女性生殖器官之惡性腫瘤              02.腫瘤      2017-07-05   \n",
       "105429     其他及未明示女性生殖器官之惡性腫瘤              02.腫瘤      2017-07-28   \n",
       "105429     其他及未明示女性生殖器官之惡性腫瘤              02.腫瘤      2017-08-19   \n",
       "105429     其他及未明示女性生殖器官之惡性腫瘤              02.腫瘤      2017-09-13   \n",
       "105429     其他及未明示女性生殖器官之惡性腫瘤              02.腫瘤      2017-12-14   \n",
       "\n",
       "           REIMBURSED_YR_TW  INSURED_RK  MATURITY_BENEFICIARY_RK  \\\n",
       "Policy_RK                                                          \n",
       "105429              16800.0      130950                      NaN   \n",
       "105429              16800.0      130950                      NaN   \n",
       "105429              23100.0      130950                      NaN   \n",
       "105429              30450.0      130950                      NaN   \n",
       "105429              18900.0      130950                      NaN   \n",
       "\n",
       "           DEATH_BENEFICIARY_RK    累積理賠金額     初次理賠時間  累積理賠次數    結案後120天  \\\n",
       "Policy_RK                                                                 \n",
       "105429                      NaN  202650.0 2015-09-04      21 2017-11-02   \n",
       "105429                      NaN  219450.0 2015-09-04      22 2017-11-25   \n",
       "105429                      NaN  242550.0 2015-09-04      23 2017-12-17   \n",
       "105429                      NaN  273000.0 2015-09-04      24 2018-01-11   \n",
       "105429                      NaN  291900.0 2015-09-04      25 2018-04-13   \n",
       "\n",
       "             結案後180天    結案後360天  \n",
       "Policy_RK                        \n",
       "105429    2018-01-01 2018-06-30  \n",
       "105429    2018-01-24 2018-07-23  \n",
       "105429    2018-02-15 2018-08-14  \n",
       "105429    2018-03-12 2018-09-08  \n",
       "105429    2018-06-12 2018-12-09  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3.loc[130950].tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **再購資料**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df4 = pd.read_excel(\"COV_ACCT_FIN.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>INSURED_RK</th>\n",
       "      <th>Policy_RK</th>\n",
       "      <th>RIDER_CD</th>\n",
       "      <th>payment_period</th>\n",
       "      <th>EFFECTIVE_DT</th>\n",
       "      <th>SHORT_NAME</th>\n",
       "      <th>prod_detail2</th>\n",
       "      <th>POLICY_HOLDER_RK</th>\n",
       "      <th>AFYP_NT</th>\n",
       "      <th>MATURITY_BENEFICIARY_RK</th>\n",
       "      <th>DEATH_BENEFICIARY_RK</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13965</th>\n",
       "      <td>26347</td>\n",
       "      <td>56714</td>\n",
       "      <td>0</td>\n",
       "      <td>月繳</td>\n",
       "      <td>2017-10-29</td>\n",
       "      <td>PAR</td>\n",
       "      <td>AHc</td>\n",
       "      <td>4</td>\n",
       "      <td>3969.00</td>\n",
       "      <td>26347.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12107</th>\n",
       "      <td>23125</td>\n",
       "      <td>179172</td>\n",
       "      <td>1</td>\n",
       "      <td>月繳</td>\n",
       "      <td>2018-11-24</td>\n",
       "      <td>CAB</td>\n",
       "      <td>AHb</td>\n",
       "      <td>4</td>\n",
       "      <td>7207.20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12108</th>\n",
       "      <td>23125</td>\n",
       "      <td>179173</td>\n",
       "      <td>0</td>\n",
       "      <td>月繳</td>\n",
       "      <td>2018-11-24</td>\n",
       "      <td>NPBBR</td>\n",
       "      <td>AHc</td>\n",
       "      <td>4</td>\n",
       "      <td>1197.00</td>\n",
       "      <td>23125.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12109</th>\n",
       "      <td>23125</td>\n",
       "      <td>179173</td>\n",
       "      <td>0</td>\n",
       "      <td>月繳</td>\n",
       "      <td>2018-11-24</td>\n",
       "      <td>NAI</td>\n",
       "      <td>AHc</td>\n",
       "      <td>4</td>\n",
       "      <td>705.60</td>\n",
       "      <td>23125.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12110</th>\n",
       "      <td>23125</td>\n",
       "      <td>179173</td>\n",
       "      <td>0</td>\n",
       "      <td>月繳</td>\n",
       "      <td>2018-11-24</td>\n",
       "      <td>HIR</td>\n",
       "      <td>AHa</td>\n",
       "      <td>4</td>\n",
       "      <td>1965.60</td>\n",
       "      <td>23125.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134471</th>\n",
       "      <td>251945</td>\n",
       "      <td>177625</td>\n",
       "      <td>1</td>\n",
       "      <td>躉繳保費</td>\n",
       "      <td>2018-08-31</td>\n",
       "      <td>BVA</td>\n",
       "      <td>ILP</td>\n",
       "      <td>251945</td>\n",
       "      <td>315000.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>120128.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79401</th>\n",
       "      <td>134583</td>\n",
       "      <td>90488</td>\n",
       "      <td>0</td>\n",
       "      <td>年繳</td>\n",
       "      <td>2017-08-31</td>\n",
       "      <td>N1TR</td>\n",
       "      <td>REG</td>\n",
       "      <td>251951</td>\n",
       "      <td>1037.40</td>\n",
       "      <td>251951.0</td>\n",
       "      <td>251951.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79402</th>\n",
       "      <td>134583</td>\n",
       "      <td>90488</td>\n",
       "      <td>1</td>\n",
       "      <td>年繳</td>\n",
       "      <td>2017-08-31</td>\n",
       "      <td>NPHI</td>\n",
       "      <td>AHa</td>\n",
       "      <td>251951</td>\n",
       "      <td>12852.00</td>\n",
       "      <td>251951.0</td>\n",
       "      <td>251951.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79555</th>\n",
       "      <td>134869</td>\n",
       "      <td>90491</td>\n",
       "      <td>0</td>\n",
       "      <td>年繳</td>\n",
       "      <td>2017-09-08</td>\n",
       "      <td>N1TR</td>\n",
       "      <td>REG</td>\n",
       "      <td>251951</td>\n",
       "      <td>937.65</td>\n",
       "      <td>251951.0</td>\n",
       "      <td>251951.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79556</th>\n",
       "      <td>134869</td>\n",
       "      <td>90491</td>\n",
       "      <td>1</td>\n",
       "      <td>年繳</td>\n",
       "      <td>2017-09-08</td>\n",
       "      <td>NPHI</td>\n",
       "      <td>AHa</td>\n",
       "      <td>251951</td>\n",
       "      <td>12295.50</td>\n",
       "      <td>251951.0</td>\n",
       "      <td>251951.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>134472 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        INSURED_RK  Policy_RK  RIDER_CD payment_period EFFECTIVE_DT  \\\n",
       "13965        26347      56714         0             月繳   2017-10-29   \n",
       "12107        23125     179172         1             月繳   2018-11-24   \n",
       "12108        23125     179173         0             月繳   2018-11-24   \n",
       "12109        23125     179173         0             月繳   2018-11-24   \n",
       "12110        23125     179173         0             月繳   2018-11-24   \n",
       "...            ...        ...       ...            ...          ...   \n",
       "134471      251945     177625         1           躉繳保費   2018-08-31   \n",
       "79401       134583      90488         0             年繳   2017-08-31   \n",
       "79402       134583      90488         1             年繳   2017-08-31   \n",
       "79555       134869      90491         0             年繳   2017-09-08   \n",
       "79556       134869      90491         1             年繳   2017-09-08   \n",
       "\n",
       "       SHORT_NAME prod_detail2  POLICY_HOLDER_RK    AFYP_NT  \\\n",
       "13965         PAR          AHc                 4    3969.00   \n",
       "12107         CAB          AHb                 4    7207.20   \n",
       "12108       NPBBR          AHc                 4    1197.00   \n",
       "12109         NAI          AHc                 4     705.60   \n",
       "12110         HIR          AHa                 4    1965.60   \n",
       "...           ...          ...               ...        ...   \n",
       "134471        BVA          ILP            251945  315000.00   \n",
       "79401        N1TR          REG            251951    1037.40   \n",
       "79402        NPHI          AHa            251951   12852.00   \n",
       "79555        N1TR          REG            251951     937.65   \n",
       "79556        NPHI          AHa            251951   12295.50   \n",
       "\n",
       "        MATURITY_BENEFICIARY_RK  DEATH_BENEFICIARY_RK  \n",
       "13965                   26347.0                   NaN  \n",
       "12107                       NaN                   NaN  \n",
       "12108                   23125.0                   NaN  \n",
       "12109                   23125.0                   NaN  \n",
       "12110                   23125.0                   NaN  \n",
       "...                         ...                   ...  \n",
       "134471                      NaN              120128.0  \n",
       "79401                  251951.0              251951.0  \n",
       "79402                  251951.0              251951.0  \n",
       "79555                  251951.0              251951.0  \n",
       "79556                  251951.0              251951.0  \n",
       "\n",
       "[134472 rows x 11 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4.sort_values(['POLICY_HOLDER_RK','Policy_RK','EFFECTIVE_DT'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **理賠再購合併**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 要vs要 合併"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "第0筆資料已標記!!\n",
      "第10000筆資料已標記!!\n",
      "第20000筆資料已標記!!\n",
      "第30000筆資料已標記!!\n",
      "第40000筆資料已標記!!\n",
      "第50000筆資料已標記!!\n",
      "第60000筆資料已標記!!\n",
      "第70000筆資料已標記!!\n",
      "第80000筆資料已標記!!\n",
      "第90000筆資料已標記!!\n",
      "第100000筆資料已標記!!\n",
      "第110000筆資料已標記!!\n",
      "第120000筆資料已標記!!\n",
      "第130000筆資料已標記!!\n",
      "第140000筆資料已標記!!\n",
      "第150000筆資料已標記!!\n",
      "第160000筆資料已標記!!\n",
      "第170000筆資料已標記!!\n",
      "第180000筆資料已標記!!\n",
      "第190000筆資料已標記!!\n",
      "第200000筆資料已標記!!\n",
      "第210000筆資料已標記!!\n",
      "第220000筆資料已標記!!\n",
      "第230000筆資料已標記!!\n"
     ]
    }
   ],
   "source": [
    "df3['再購(120天)'] = 0 \n",
    "for i in range(len(df3.index)):\n",
    "    time_list = df4[df4['POLICY_HOLDER_RK']==df3.index[i][0]]['EFFECTIVE_DT'].unique()\n",
    "    for time in time_list:\n",
    "        if (df3.iloc[i,6] < time) and (time < df3.iloc[i,14]):\n",
    "            df3.iloc[i,17] = 1\n",
    "            break\n",
    "    if i % 10000 == 0:\n",
    "        print(\"第{}筆資料已標記!!\".format(i))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "第0筆資料已標記!!\n",
      "第10000筆資料已標記!!\n",
      "第20000筆資料已標記!!\n",
      "第30000筆資料已標記!!\n",
      "第40000筆資料已標記!!\n",
      "第50000筆資料已標記!!\n",
      "第60000筆資料已標記!!\n",
      "第70000筆資料已標記!!\n",
      "第80000筆資料已標記!!\n",
      "第90000筆資料已標記!!\n",
      "第100000筆資料已標記!!\n",
      "第110000筆資料已標記!!\n",
      "第120000筆資料已標記!!\n",
      "第130000筆資料已標記!!\n",
      "第140000筆資料已標記!!\n",
      "第150000筆資料已標記!!\n",
      "第160000筆資料已標記!!\n",
      "第170000筆資料已標記!!\n",
      "第180000筆資料已標記!!\n",
      "第190000筆資料已標記!!\n",
      "第200000筆資料已標記!!\n",
      "第210000筆資料已標記!!\n",
      "第220000筆資料已標記!!\n",
      "第230000筆資料已標記!!\n"
     ]
    }
   ],
   "source": [
    "df3['再購(180天)'] = 0 \n",
    "for i in range(len(df3.index)):\n",
    "    time_list = df4[df4['POLICY_HOLDER_RK']==df3.index[i][0]]['EFFECTIVE_DT'].unique()\n",
    "    for time in time_list:\n",
    "        if (df3.iloc[i,6] < time) and (time < df3.iloc[i,15]):\n",
    "            df3.iloc[i,18] = 1\n",
    "            break\n",
    "    if i % 10000 == 0:\n",
    "        print(\"第{}筆資料已標記!!\".format(i))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "第0筆資料已標記!!\n",
      "第10000筆資料已標記!!\n",
      "第20000筆資料已標記!!\n",
      "第30000筆資料已標記!!\n",
      "第40000筆資料已標記!!\n",
      "第50000筆資料已標記!!\n",
      "第60000筆資料已標記!!\n",
      "第70000筆資料已標記!!\n",
      "第80000筆資料已標記!!\n",
      "第90000筆資料已標記!!\n",
      "第100000筆資料已標記!!\n",
      "第110000筆資料已標記!!\n",
      "第120000筆資料已標記!!\n",
      "第130000筆資料已標記!!\n",
      "第140000筆資料已標記!!\n",
      "第150000筆資料已標記!!\n",
      "第160000筆資料已標記!!\n",
      "第170000筆資料已標記!!\n",
      "第180000筆資料已標記!!\n",
      "第190000筆資料已標記!!\n",
      "第200000筆資料已標記!!\n",
      "第210000筆資料已標記!!\n",
      "第220000筆資料已標記!!\n",
      "第230000筆資料已標記!!\n"
     ]
    }
   ],
   "source": [
    "df3['再購(360天)'] = 0 \n",
    "for i in range(len(df3.index)):\n",
    "    time_list = df4[df4['POLICY_HOLDER_RK']==df3.index[i][0]]['EFFECTIVE_DT'].unique()\n",
    "    for time in time_list:\n",
    "        if (df3.iloc[i,6] < time) and (time < df3.iloc[i,16]):\n",
    "            df3.iloc[i,19] = 1\n",
    "            break\n",
    "    if i % 10000 == 0:\n",
    "        print(\"第{}筆資料已標記!!\".format(i))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 理賠再購合併檔案輸出"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3.to_excel('理賠再購合併.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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></th>\n",
       "      <th>INJURED_RK</th>\n",
       "      <th>Claim_RK</th>\n",
       "      <th>BundleSubtype2</th>\n",
       "      <th>illness_code</th>\n",
       "      <th>illness_desc</th>\n",
       "      <th>DiagnosisCode_DESC</th>\n",
       "      <th>claim_settle_dt</th>\n",
       "      <th>REIMBURSED_YR_TW</th>\n",
       "      <th>INSURED_RK</th>\n",
       "      <th>MATURITY_BENEFICIARY_RK</th>\n",
       "      <th>DEATH_BENEFICIARY_RK</th>\n",
       "      <th>累積理賠金額</th>\n",
       "      <th>初次理賠時間</th>\n",
       "      <th>累積理賠次數</th>\n",
       "      <th>結案後120天</th>\n",
       "      <th>結案後180天</th>\n",
       "      <th>結案後360天</th>\n",
       "      <th>再購(120天)</th>\n",
       "      <th>再購(180天)</th>\n",
       "      <th>再購(360天)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>POLICY_HOLDER_RK</th>\n",
       "      <th>Policy_RK</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <th>49082</th>\n",
       "      <td>4324</td>\n",
       "      <td>104018</td>\n",
       "      <td>5.N疾病醫療</td>\n",
       "      <td>D36</td>\n",
       "      <td>其他及未明示部位良性腫瘤</td>\n",
       "      <td>02.腫瘤</td>\n",
       "      <td>2017-01-20</td>\n",
       "      <td>9189.60</td>\n",
       "      <td>4324</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9189.60</td>\n",
       "      <td>2017-01-20</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-05-20</td>\n",
       "      <td>2017-07-19</td>\n",
       "      <td>2018-01-15</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">4</th>\n",
       "      <th>56714</th>\n",
       "      <td>26347</td>\n",
       "      <td>90482</td>\n",
       "      <td>6.N意外醫療</td>\n",
       "      <td>W18</td>\n",
       "      <td>其他滑倒、摔倒或絆倒和跌落</td>\n",
       "      <td>20.疾病和死亡的外因</td>\n",
       "      <td>2016-12-01</td>\n",
       "      <td>1837.50</td>\n",
       "      <td>26347</td>\n",
       "      <td>26347.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1837.50</td>\n",
       "      <td>2016-12-01</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-03-31</td>\n",
       "      <td>2017-05-30</td>\n",
       "      <td>2017-11-26</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56714</th>\n",
       "      <td>26347</td>\n",
       "      <td>184848</td>\n",
       "      <td>6.N意外醫療</td>\n",
       "      <td>W18</td>\n",
       "      <td>其他滑倒、摔倒或絆倒和跌落</td>\n",
       "      <td>20.疾病和死亡的外因</td>\n",
       "      <td>2017-09-06</td>\n",
       "      <td>2857.05</td>\n",
       "      <td>26347</td>\n",
       "      <td>26347.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4694.55</td>\n",
       "      <td>2016-12-01</td>\n",
       "      <td>2</td>\n",
       "      <td>2018-01-04</td>\n",
       "      <td>2018-03-05</td>\n",
       "      <td>2018-09-01</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <th>84973</th>\n",
       "      <td>93160</td>\n",
       "      <td>183589</td>\n",
       "      <td>5.N疾病醫療</td>\n",
       "      <td>D24</td>\n",
       "      <td>乳房良性腫廇</td>\n",
       "      <td>02.腫瘤</td>\n",
       "      <td>2017-09-02</td>\n",
       "      <td>2835.00</td>\n",
       "      <td>93160</td>\n",
       "      <td>93160.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2835.00</td>\n",
       "      <td>2017-09-02</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-12-31</td>\n",
       "      <td>2018-03-01</td>\n",
       "      <td>2018-08-28</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <th>65407</th>\n",
       "      <td>94922</td>\n",
       "      <td>175992</td>\n",
       "      <td>5.N疾病醫療</td>\n",
       "      <td>L03.90</td>\n",
       "      <td>蜂窩組織炎</td>\n",
       "      <td>12.皮膚和皮下組織疾病</td>\n",
       "      <td>2017-08-13</td>\n",
       "      <td>4200.00</td>\n",
       "      <td>94922</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4200.00</td>\n",
       "      <td>2017-08-13</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-12-11</td>\n",
       "      <td>2018-02-09</td>\n",
       "      <td>2018-08-08</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251945</th>\n",
       "      <th>102196</th>\n",
       "      <td>123213</td>\n",
       "      <td>112437</td>\n",
       "      <td>6.N意外醫療</td>\n",
       "      <td>V23</td>\n",
       "      <td>摩拖車騎士與汽車，小貨車或箱型車碰撞受傷</td>\n",
       "      <td>20.疾病和死亡的外因</td>\n",
       "      <td>2017-02-19</td>\n",
       "      <td>813.75</td>\n",
       "      <td>123213</td>\n",
       "      <td>251945.0</td>\n",
       "      <td>251945.0</td>\n",
       "      <td>813.75</td>\n",
       "      <td>2017-02-19</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-06-19</td>\n",
       "      <td>2017-08-18</td>\n",
       "      <td>2018-02-14</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251947</th>\n",
       "      <th>16872</th>\n",
       "      <td>86041</td>\n",
       "      <td>179452</td>\n",
       "      <td>6.N意外醫療</td>\n",
       "      <td>Y99.8</td>\n",
       "      <td>其他外因狀態</td>\n",
       "      <td>99.不知道不想猜</td>\n",
       "      <td>2017-08-23</td>\n",
       "      <td>1627.50</td>\n",
       "      <td>86041</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1627.50</td>\n",
       "      <td>2017-08-23</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-12-21</td>\n",
       "      <td>2018-02-19</td>\n",
       "      <td>2018-08-18</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251949</th>\n",
       "      <th>100554</th>\n",
       "      <td>161848</td>\n",
       "      <td>113170</td>\n",
       "      <td>6.N意外醫療</td>\n",
       "      <td>V99</td>\n",
       "      <td>運輸意外事故</td>\n",
       "      <td>20.疾病和死亡的外因</td>\n",
       "      <td>2017-02-24</td>\n",
       "      <td>262237.50</td>\n",
       "      <td>161848</td>\n",
       "      <td>251949.0</td>\n",
       "      <td>31420.0</td>\n",
       "      <td>262237.50</td>\n",
       "      <td>2017-02-24</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-06-24</td>\n",
       "      <td>2017-08-23</td>\n",
       "      <td>2018-02-19</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251951</th>\n",
       "      <th>96970</th>\n",
       "      <td>134869</td>\n",
       "      <td>174868</td>\n",
       "      <td>5.N疾病醫療</td>\n",
       "      <td>D36</td>\n",
       "      <td>其他及未明示部位良性腫瘤</td>\n",
       "      <td>02.腫瘤</td>\n",
       "      <td>2017-08-11</td>\n",
       "      <td>546.00</td>\n",
       "      <td>134869</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>546.00</td>\n",
       "      <td>2017-08-11</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-12-09</td>\n",
       "      <td>2018-02-07</td>\n",
       "      <td>2018-08-06</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251954</th>\n",
       "      <th>74765</th>\n",
       "      <td>156320</td>\n",
       "      <td>136040</td>\n",
       "      <td>4.C重大疾病</td>\n",
       "      <td>I25.1</td>\n",
       "      <td>自體的冠狀動脈粥樣硬化心臟病</td>\n",
       "      <td>09.循環系統疾病</td>\n",
       "      <td>2017-04-29</td>\n",
       "      <td>16825.20</td>\n",
       "      <td>251954</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>16825.20</td>\n",
       "      <td>2017-04-29</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-08-27</td>\n",
       "      <td>2017-10-26</td>\n",
       "      <td>2018-04-24</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>234428 rows × 20 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            INJURED_RK  Claim_RK BundleSubtype2 illness_code  \\\n",
       "POLICY_HOLDER_RK Policy_RK                                                     \n",
       "3                49082            4324    104018        5.N疾病醫療          D36   \n",
       "4                56714           26347     90482        6.N意外醫療          W18   \n",
       "                 56714           26347    184848        6.N意外醫療          W18   \n",
       "5                84973           93160    183589        5.N疾病醫療          D24   \n",
       "6                65407           94922    175992        5.N疾病醫療       L03.90   \n",
       "...                                ...       ...            ...          ...   \n",
       "251945           102196         123213    112437        6.N意外醫療          V23   \n",
       "251947           16872           86041    179452        6.N意外醫療        Y99.8   \n",
       "251949           100554         161848    113170        6.N意外醫療          V99   \n",
       "251951           96970          134869    174868        5.N疾病醫療          D36   \n",
       "251954           74765          156320    136040        4.C重大疾病        I25.1   \n",
       "\n",
       "                                    illness_desc DiagnosisCode_DESC  \\\n",
       "POLICY_HOLDER_RK Policy_RK                                            \n",
       "3                49082              其他及未明示部位良性腫瘤              02.腫瘤   \n",
       "4                56714             其他滑倒、摔倒或絆倒和跌落        20.疾病和死亡的外因   \n",
       "                 56714             其他滑倒、摔倒或絆倒和跌落        20.疾病和死亡的外因   \n",
       "5                84973                    乳房良性腫廇              02.腫瘤   \n",
       "6                65407                     蜂窩組織炎       12.皮膚和皮下組織疾病   \n",
       "...                                          ...                ...   \n",
       "251945           102196     摩拖車騎士與汽車，小貨車或箱型車碰撞受傷        20.疾病和死亡的外因   \n",
       "251947           16872                    其他外因狀態          99.不知道不想猜   \n",
       "251949           100554                   運輸意外事故        20.疾病和死亡的外因   \n",
       "251951           96970              其他及未明示部位良性腫瘤              02.腫瘤   \n",
       "251954           74765            自體的冠狀動脈粥樣硬化心臟病          09.循環系統疾病   \n",
       "\n",
       "                           claim_settle_dt  REIMBURSED_YR_TW  INSURED_RK  \\\n",
       "POLICY_HOLDER_RK Policy_RK                                                 \n",
       "3                49082          2017-01-20           9189.60        4324   \n",
       "4                56714          2016-12-01           1837.50       26347   \n",
       "                 56714          2017-09-06           2857.05       26347   \n",
       "5                84973          2017-09-02           2835.00       93160   \n",
       "6                65407          2017-08-13           4200.00       94922   \n",
       "...                                    ...               ...         ...   \n",
       "251945           102196         2017-02-19            813.75      123213   \n",
       "251947           16872          2017-08-23           1627.50       86041   \n",
       "251949           100554         2017-02-24         262237.50      161848   \n",
       "251951           96970          2017-08-11            546.00      134869   \n",
       "251954           74765          2017-04-29          16825.20      251954   \n",
       "\n",
       "                            MATURITY_BENEFICIARY_RK  DEATH_BENEFICIARY_RK  \\\n",
       "POLICY_HOLDER_RK Policy_RK                                                  \n",
       "3                49082                          3.0                   NaN   \n",
       "4                56714                      26347.0                   NaN   \n",
       "                 56714                      26347.0                   NaN   \n",
       "5                84973                      93160.0                   NaN   \n",
       "6                65407                          NaN                   NaN   \n",
       "...                                             ...                   ...   \n",
       "251945           102196                    251945.0              251945.0   \n",
       "251947           16872                          NaN                   NaN   \n",
       "251949           100554                    251949.0               31420.0   \n",
       "251951           96970                          NaN                   NaN   \n",
       "251954           74765                          NaN                   NaN   \n",
       "\n",
       "                               累積理賠金額     初次理賠時間  累積理賠次數    結案後120天  \\\n",
       "POLICY_HOLDER_RK Policy_RK                                            \n",
       "3                49082        9189.60 2017-01-20       1 2017-05-20   \n",
       "4                56714        1837.50 2016-12-01       1 2017-03-31   \n",
       "                 56714        4694.55 2016-12-01       2 2018-01-04   \n",
       "5                84973        2835.00 2017-09-02       1 2017-12-31   \n",
       "6                65407        4200.00 2017-08-13       1 2017-12-11   \n",
       "...                               ...        ...     ...        ...   \n",
       "251945           102196        813.75 2017-02-19       1 2017-06-19   \n",
       "251947           16872        1627.50 2017-08-23       1 2017-12-21   \n",
       "251949           100554     262237.50 2017-02-24       1 2017-06-24   \n",
       "251951           96970         546.00 2017-08-11       1 2017-12-09   \n",
       "251954           74765       16825.20 2017-04-29       1 2017-08-27   \n",
       "\n",
       "                              結案後180天    結案後360天  再購(120天)  再購(180天)  再購(360天)  \n",
       "POLICY_HOLDER_RK Policy_RK                                                      \n",
       "3                49082     2017-07-19 2018-01-15         0         0         0  \n",
       "4                56714     2017-05-30 2017-11-26         0         0         1  \n",
       "                 56714     2018-03-05 2018-09-01         1         1         1  \n",
       "5                84973     2018-03-01 2018-08-28         1         1         1  \n",
       "6                65407     2018-02-09 2018-08-08         0         0         0  \n",
       "...                               ...        ...       ...       ...       ...  \n",
       "251945           102196    2017-08-18 2018-02-14         0         0         0  \n",
       "251947           16872     2018-02-19 2018-08-18         0         0         0  \n",
       "251949           100554    2017-08-23 2018-02-19         0         0         0  \n",
       "251951           96970     2018-02-07 2018-08-06         1         1         1  \n",
       "251954           74765     2017-10-26 2018-04-24         0         0         0  \n",
       "\n",
       "[234428 rows x 20 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **理賠再購檔.客戶屬性檔合併**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df4 = pd.read_excel('CUST_PROPERTY_FIN_1.xlsx')\n",
    "df5 = pd.read_excel('CUST_PROPERTY_FIN_2.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df5.index = [65000+i for i in range(len(df5))]\n",
    "frames = [df4,df5]\n",
    "df6 = pd.concat(frames)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>CUST_RK</th>\n",
       "      <th>ternure_m</th>\n",
       "      <th>recency_m</th>\n",
       "      <th>SIN</th>\n",
       "      <th>SIN_his</th>\n",
       "      <th>REG</th>\n",
       "      <th>REG_his</th>\n",
       "      <th>ILP</th>\n",
       "      <th>ILP_his</th>\n",
       "      <th>AHa</th>\n",
       "      <th>...</th>\n",
       "      <th>WEALTH_LEVEL</th>\n",
       "      <th>CLIENT_MARITAL</th>\n",
       "      <th>CLIENT_INCOME</th>\n",
       "      <th>DIGI_FLG</th>\n",
       "      <th>TOPCARD</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>stick_level2</th>\n",
       "      <th>cust_group2</th>\n",
       "      <th>TOTAL_AUM</th>\n",
       "      <th>INSURED_DOB</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>24</td>\n",
       "      <td>201</td>\n",
       "      <td>201</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>W7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>S10</td>\n",
       "      <td>G4</td>\n",
       "      <td>396720.0</td>\n",
       "      <td>1984-04-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>152</td>\n",
       "      <td>175</td>\n",
       "      <td>175</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>W6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>S10</td>\n",
       "      <td>G4</td>\n",
       "      <td>165870.0</td>\n",
       "      <td>1951-02-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>155</td>\n",
       "      <td>227</td>\n",
       "      <td>227</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>W7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>S10</td>\n",
       "      <td>G4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1976-07-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>158</td>\n",
       "      <td>165</td>\n",
       "      <td>165</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>W7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>S10</td>\n",
       "      <td>G4</td>\n",
       "      <td>68362.0</td>\n",
       "      <td>1978-12-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>204</td>\n",
       "      <td>181</td>\n",
       "      <td>181</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>W7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>S10</td>\n",
       "      <td>G4</td>\n",
       "      <td>68400.0</td>\n",
       "      <td>1976-09-20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130482</th>\n",
       "      <td>251944</td>\n",
       "      <td>271</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>W2</td>\n",
       "      <td>M</td>\n",
       "      <td>1900000</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>S04</td>\n",
       "      <td>G0</td>\n",
       "      <td>2885720.0</td>\n",
       "      <td>1966-10-06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130483</th>\n",
       "      <td>251945</td>\n",
       "      <td>255</td>\n",
       "      <td>54</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>W5</td>\n",
       "      <td>M</td>\n",
       "      <td>247000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>S07</td>\n",
       "      <td>G4</td>\n",
       "      <td>300840.3</td>\n",
       "      <td>1966-01-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130484</th>\n",
       "      <td>251947</td>\n",
       "      <td>135</td>\n",
       "      <td>102</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>W7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>S09</td>\n",
       "      <td>G4</td>\n",
       "      <td>123291.0</td>\n",
       "      <td>1979-05-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130485</th>\n",
       "      <td>251951</td>\n",
       "      <td>125</td>\n",
       "      <td>34</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>W5</td>\n",
       "      <td>M</td>\n",
       "      <td>665000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>S07</td>\n",
       "      <td>G4</td>\n",
       "      <td>224832.7</td>\n",
       "      <td>1972-06-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130486</th>\n",
       "      <td>251954</td>\n",
       "      <td>297</td>\n",
       "      <td>68</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>W6</td>\n",
       "      <td>M</td>\n",
       "      <td>760000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>S07</td>\n",
       "      <td>G4</td>\n",
       "      <td>126247.4</td>\n",
       "      <td>1960-09-28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>130487 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        CUST_RK  ternure_m  recency_m  SIN  SIN_his  REG  REG_his  ILP  \\\n",
       "0            24        201        201    0        0    1        1    0   \n",
       "1           152        175        175    0        0    1        1    0   \n",
       "2           155        227        227    0        0    1        1    0   \n",
       "3           158        165        165    0        0    0        0    0   \n",
       "4           204        181        181    0        0    1        1    0   \n",
       "...         ...        ...        ...  ...      ...  ...      ...  ...   \n",
       "130482   251944        271          4    1        1    1        1    0   \n",
       "130483   251945        255         54    0        0    1        1    0   \n",
       "130484   251947        135        102    0        0    1        1    0   \n",
       "130485   251951        125         34    0        0    1        1    0   \n",
       "130486   251954        297         68    0        0    1        1    0   \n",
       "\n",
       "        ILP_his  AHa  ...  WEALTH_LEVEL  CLIENT_MARITAL  CLIENT_INCOME  \\\n",
       "0             0    0  ...            W7             NaN              0   \n",
       "1             0    1  ...            W6             NaN              0   \n",
       "2             0    1  ...            W7             NaN              0   \n",
       "3             0    1  ...            W7             NaN              0   \n",
       "4             0    1  ...            W7             NaN              0   \n",
       "...         ...  ...  ...           ...             ...            ...   \n",
       "130482        0    1  ...            W2               M        1900000   \n",
       "130483        0    1  ...            W5               M         247000   \n",
       "130484        0    1  ...            W7             NaN              0   \n",
       "130485        0    1  ...            W5               M         665000   \n",
       "130486        0    1  ...            W6               M         760000   \n",
       "\n",
       "        DIGI_FLG  TOPCARD  GENDER  stick_level2 cust_group2  TOTAL_AUM  \\\n",
       "0              0        0       1           S10          G4   396720.0   \n",
       "1              0        0       1           S10          G4   165870.0   \n",
       "2              0        0       1           S10          G4        NaN   \n",
       "3              0        0       1           S10          G4    68362.0   \n",
       "4              0        0       1           S10          G4    68400.0   \n",
       "...          ...      ...     ...           ...         ...        ...   \n",
       "130482         0        1       1           S04          G0  2885720.0   \n",
       "130483         0        0       1           S07          G4   300840.3   \n",
       "130484         0        0       1           S09          G4   123291.0   \n",
       "130485         0        0       1           S07          G4   224832.7   \n",
       "130486         0        0       1           S07          G4   126247.4   \n",
       "\n",
       "       INSURED_DOB  \n",
       "0       1984-04-04  \n",
       "1       1951-02-26  \n",
       "2       1976-07-19  \n",
       "3       1978-12-09  \n",
       "4       1976-09-20  \n",
       "...            ...  \n",
       "130482  1966-10-06  \n",
       "130483  1966-01-09  \n",
       "130484  1979-05-16  \n",
       "130485  1972-06-22  \n",
       "130486  1960-09-28  \n",
       "\n",
       "[130487 rows x 29 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df7 = df3.merge(right=df6, how='left', left_on='POLICY_HOLDER_RK',right_on='CUST_RK')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "df7.index= df3.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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></th>\n",
       "      <th>INJURED_RK</th>\n",
       "      <th>Claim_RK</th>\n",
       "      <th>BundleSubtype2</th>\n",
       "      <th>illness_code</th>\n",
       "      <th>illness_desc</th>\n",
       "      <th>DiagnosisCode_DESC</th>\n",
       "      <th>claim_settle_dt</th>\n",
       "      <th>REIMBURSED_YR_TW</th>\n",
       "      <th>INSURED_RK</th>\n",
       "      <th>MATURITY_BENEFICIARY_RK</th>\n",
       "      <th>...</th>\n",
       "      <th>WEALTH_LEVEL</th>\n",
       "      <th>CLIENT_MARITAL</th>\n",
       "      <th>CLIENT_INCOME</th>\n",
       "      <th>DIGI_FLG</th>\n",
       "      <th>TOPCARD</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>stick_level2</th>\n",
       "      <th>cust_group2</th>\n",
       "      <th>TOTAL_AUM</th>\n",
       "      <th>INSURED_DOB</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>POLICY_HOLDER_RK</th>\n",
       "      <th>Policy_RK</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <th>49082</th>\n",
       "      <td>4324</td>\n",
       "      <td>104018</td>\n",
       "      <td>5.N疾病醫療</td>\n",
       "      <td>D36</td>\n",
       "      <td>其他及未明示部位良性腫瘤</td>\n",
       "      <td>02.腫瘤</td>\n",
       "      <td>2017-01-20</td>\n",
       "      <td>9189.60</td>\n",
       "      <td>4324</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">4</th>\n",
       "      <th>56714</th>\n",
       "      <td>26347</td>\n",
       "      <td>90482</td>\n",
       "      <td>6.N意外醫療</td>\n",
       "      <td>W18</td>\n",
       "      <td>其他滑倒、摔倒或絆倒和跌落</td>\n",
       "      <td>20.疾病和死亡的外因</td>\n",
       "      <td>2016-12-01</td>\n",
       "      <td>1837.50</td>\n",
       "      <td>26347</td>\n",
       "      <td>26347.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56714</th>\n",
       "      <td>26347</td>\n",
       "      <td>184848</td>\n",
       "      <td>6.N意外醫療</td>\n",
       "      <td>W18</td>\n",
       "      <td>其他滑倒、摔倒或絆倒和跌落</td>\n",
       "      <td>20.疾病和死亡的外因</td>\n",
       "      <td>2017-09-06</td>\n",
       "      <td>2857.05</td>\n",
       "      <td>26347</td>\n",
       "      <td>26347.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <th>84973</th>\n",
       "      <td>93160</td>\n",
       "      <td>183589</td>\n",
       "      <td>5.N疾病醫療</td>\n",
       "      <td>D24</td>\n",
       "      <td>乳房良性腫廇</td>\n",
       "      <td>02.腫瘤</td>\n",
       "      <td>2017-09-02</td>\n",
       "      <td>2835.00</td>\n",
       "      <td>93160</td>\n",
       "      <td>93160.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <th>65407</th>\n",
       "      <td>94922</td>\n",
       "      <td>175992</td>\n",
       "      <td>5.N疾病醫療</td>\n",
       "      <td>L03.90</td>\n",
       "      <td>蜂窩組織炎</td>\n",
       "      <td>12.皮膚和皮下組織疾病</td>\n",
       "      <td>2017-08-13</td>\n",
       "      <td>4200.00</td>\n",
       "      <td>94922</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251945</th>\n",
       "      <th>102196</th>\n",
       "      <td>123213</td>\n",
       "      <td>112437</td>\n",
       "      <td>6.N意外醫療</td>\n",
       "      <td>V23</td>\n",
       "      <td>摩拖車騎士與汽車，小貨車或箱型車碰撞受傷</td>\n",
       "      <td>20.疾病和死亡的外因</td>\n",
       "      <td>2017-02-19</td>\n",
       "      <td>813.75</td>\n",
       "      <td>123213</td>\n",
       "      <td>251945.0</td>\n",
       "      <td>...</td>\n",
       "      <td>W5</td>\n",
       "      <td>M</td>\n",
       "      <td>247000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>S07</td>\n",
       "      <td>G4</td>\n",
       "      <td>300840.3</td>\n",
       "      <td>1966-01-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251947</th>\n",
       "      <th>16872</th>\n",
       "      <td>86041</td>\n",
       "      <td>179452</td>\n",
       "      <td>6.N意外醫療</td>\n",
       "      <td>Y99.8</td>\n",
       "      <td>其他外因狀態</td>\n",
       "      <td>99.不知道不想猜</td>\n",
       "      <td>2017-08-23</td>\n",
       "      <td>1627.50</td>\n",
       "      <td>86041</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>W7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>S09</td>\n",
       "      <td>G4</td>\n",
       "      <td>123291.0</td>\n",
       "      <td>1979-05-16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251949</th>\n",
       "      <th>100554</th>\n",
       "      <td>161848</td>\n",
       "      <td>113170</td>\n",
       "      <td>6.N意外醫療</td>\n",
       "      <td>V99</td>\n",
       "      <td>運輸意外事故</td>\n",
       "      <td>20.疾病和死亡的外因</td>\n",
       "      <td>2017-02-24</td>\n",
       "      <td>262237.50</td>\n",
       "      <td>161848</td>\n",
       "      <td>251949.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251951</th>\n",
       "      <th>96970</th>\n",
       "      <td>134869</td>\n",
       "      <td>174868</td>\n",
       "      <td>5.N疾病醫療</td>\n",
       "      <td>D36</td>\n",
       "      <td>其他及未明示部位良性腫瘤</td>\n",
       "      <td>02.腫瘤</td>\n",
       "      <td>2017-08-11</td>\n",
       "      <td>546.00</td>\n",
       "      <td>134869</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>W5</td>\n",
       "      <td>M</td>\n",
       "      <td>665000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>S07</td>\n",
       "      <td>G4</td>\n",
       "      <td>224832.7</td>\n",
       "      <td>1972-06-22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251954</th>\n",
       "      <th>74765</th>\n",
       "      <td>156320</td>\n",
       "      <td>136040</td>\n",
       "      <td>4.C重大疾病</td>\n",
       "      <td>I25.1</td>\n",
       "      <td>自體的冠狀動脈粥樣硬化心臟病</td>\n",
       "      <td>09.循環系統疾病</td>\n",
       "      <td>2017-04-29</td>\n",
       "      <td>16825.20</td>\n",
       "      <td>251954</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>W6</td>\n",
       "      <td>M</td>\n",
       "      <td>760000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>S07</td>\n",
       "      <td>G4</td>\n",
       "      <td>126247.4</td>\n",
       "      <td>1960-09-28</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>234428 rows × 49 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            INJURED_RK  Claim_RK BundleSubtype2 illness_code  \\\n",
       "POLICY_HOLDER_RK Policy_RK                                                     \n",
       "3                49082            4324    104018        5.N疾病醫療          D36   \n",
       "4                56714           26347     90482        6.N意外醫療          W18   \n",
       "                 56714           26347    184848        6.N意外醫療          W18   \n",
       "5                84973           93160    183589        5.N疾病醫療          D24   \n",
       "6                65407           94922    175992        5.N疾病醫療       L03.90   \n",
       "...                                ...       ...            ...          ...   \n",
       "251945           102196         123213    112437        6.N意外醫療          V23   \n",
       "251947           16872           86041    179452        6.N意外醫療        Y99.8   \n",
       "251949           100554         161848    113170        6.N意外醫療          V99   \n",
       "251951           96970          134869    174868        5.N疾病醫療          D36   \n",
       "251954           74765          156320    136040        4.C重大疾病        I25.1   \n",
       "\n",
       "                                    illness_desc DiagnosisCode_DESC  \\\n",
       "POLICY_HOLDER_RK Policy_RK                                            \n",
       "3                49082              其他及未明示部位良性腫瘤              02.腫瘤   \n",
       "4                56714             其他滑倒、摔倒或絆倒和跌落        20.疾病和死亡的外因   \n",
       "                 56714             其他滑倒、摔倒或絆倒和跌落        20.疾病和死亡的外因   \n",
       "5                84973                    乳房良性腫廇              02.腫瘤   \n",
       "6                65407                     蜂窩組織炎       12.皮膚和皮下組織疾病   \n",
       "...                                          ...                ...   \n",
       "251945           102196     摩拖車騎士與汽車，小貨車或箱型車碰撞受傷        20.疾病和死亡的外因   \n",
       "251947           16872                    其他外因狀態          99.不知道不想猜   \n",
       "251949           100554                   運輸意外事故        20.疾病和死亡的外因   \n",
       "251951           96970              其他及未明示部位良性腫瘤              02.腫瘤   \n",
       "251954           74765            自體的冠狀動脈粥樣硬化心臟病          09.循環系統疾病   \n",
       "\n",
       "                           claim_settle_dt  REIMBURSED_YR_TW  INSURED_RK  \\\n",
       "POLICY_HOLDER_RK Policy_RK                                                 \n",
       "3                49082          2017-01-20           9189.60        4324   \n",
       "4                56714          2016-12-01           1837.50       26347   \n",
       "                 56714          2017-09-06           2857.05       26347   \n",
       "5                84973          2017-09-02           2835.00       93160   \n",
       "6                65407          2017-08-13           4200.00       94922   \n",
       "...                                    ...               ...         ...   \n",
       "251945           102196         2017-02-19            813.75      123213   \n",
       "251947           16872          2017-08-23           1627.50       86041   \n",
       "251949           100554         2017-02-24         262237.50      161848   \n",
       "251951           96970          2017-08-11            546.00      134869   \n",
       "251954           74765          2017-04-29          16825.20      251954   \n",
       "\n",
       "                            MATURITY_BENEFICIARY_RK  ...  WEALTH_LEVEL  \\\n",
       "POLICY_HOLDER_RK Policy_RK                           ...                 \n",
       "3                49082                          3.0  ...           NaN   \n",
       "4                56714                      26347.0  ...           NaN   \n",
       "                 56714                      26347.0  ...           NaN   \n",
       "5                84973                      93160.0  ...           NaN   \n",
       "6                65407                          NaN  ...           NaN   \n",
       "...                                             ...  ...           ...   \n",
       "251945           102196                    251945.0  ...            W5   \n",
       "251947           16872                          NaN  ...            W7   \n",
       "251949           100554                    251949.0  ...           NaN   \n",
       "251951           96970                          NaN  ...            W5   \n",
       "251954           74765                          NaN  ...            W6   \n",
       "\n",
       "                            CLIENT_MARITAL CLIENT_INCOME  DIGI_FLG TOPCARD  \\\n",
       "POLICY_HOLDER_RK Policy_RK                                                   \n",
       "3                49082                 NaN           NaN       NaN     NaN   \n",
       "4                56714                 NaN           NaN       NaN     NaN   \n",
       "                 56714                 NaN           NaN       NaN     NaN   \n",
       "5                84973                 NaN           NaN       NaN     NaN   \n",
       "6                65407                 NaN           NaN       NaN     NaN   \n",
       "...                                    ...           ...       ...     ...   \n",
       "251945           102196                  M      247000.0       0.0     0.0   \n",
       "251947           16872                 NaN           0.0       0.0     0.0   \n",
       "251949           100554                NaN           NaN       NaN     NaN   \n",
       "251951           96970                   M      665000.0       0.0     0.0   \n",
       "251954           74765                   M      760000.0       0.0     0.0   \n",
       "\n",
       "                           GENDER stick_level2  cust_group2  TOTAL_AUM  \\\n",
       "POLICY_HOLDER_RK Policy_RK                                               \n",
       "3                49082        NaN          NaN          NaN        NaN   \n",
       "4                56714        NaN          NaN          NaN        NaN   \n",
       "                 56714        NaN          NaN          NaN        NaN   \n",
       "5                84973        NaN          NaN          NaN        NaN   \n",
       "6                65407        NaN          NaN          NaN        NaN   \n",
       "...                           ...          ...          ...        ...   \n",
       "251945           102196       1.0          S07           G4   300840.3   \n",
       "251947           16872        1.0          S09           G4   123291.0   \n",
       "251949           100554       NaN          NaN          NaN        NaN   \n",
       "251951           96970        1.0          S07           G4   224832.7   \n",
       "251954           74765        1.0          S07           G4   126247.4   \n",
       "\n",
       "                            INSURED_DOB  \n",
       "POLICY_HOLDER_RK Policy_RK               \n",
       "3                49082              NaT  \n",
       "4                56714              NaT  \n",
       "                 56714              NaT  \n",
       "5                84973              NaT  \n",
       "6                65407              NaT  \n",
       "...                                 ...  \n",
       "251945           102196      1966-01-09  \n",
       "251947           16872       1979-05-16  \n",
       "251949           100554             NaT  \n",
       "251951           96970       1972-06-22  \n",
       "251954           74765       1960-09-28  \n",
       "\n",
       "[234428 rows x 49 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "df8 = df7[['INJURED_RK', 'Claim_RK', 'BundleSubtype2', 'illness_code',\n",
    "       'illness_desc', 'DiagnosisCode_DESC', 'claim_settle_dt',\n",
    "       'REIMBURSED_YR_TW', 'INSURED_RK', 'MATURITY_BENEFICIARY_RK',\n",
    "       'DEATH_BENEFICIARY_RK', '累積理賠金額', '初次理賠時間', '累積理賠次數', '結案後120天',\n",
    "       '結案後180天', '結案後360天', 'CUST_RK',\n",
    "       'ternure_m', 'recency_m', 'SIN', 'SIN_his', 'REG', 'REG_his', 'ILP',\n",
    "       'ILP_his', 'AHa', 'AHa_his', 'AHb', 'AHb_his', 'AHc', 'AHc_his', 'AHd',\n",
    "       'AHd_his', 'VIP_CLASS', 'VIP', 'WEALTH_LEVEL', 'CLIENT_MARITAL',\n",
    "       'CLIENT_INCOME', 'DIGI_FLG', 'TOPCARD', 'GENDER', 'stick_level2',\n",
    "       'cust_group2', 'TOTAL_AUM', 'INSURED_DOB','再購(120天)', '再購(180天)', '再購(360天)']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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></th>\n",
       "      <th>INJURED_RK</th>\n",
       "      <th>Claim_RK</th>\n",
       "      <th>BundleSubtype2</th>\n",
       "      <th>illness_code</th>\n",
       "      <th>illness_desc</th>\n",
       "      <th>DiagnosisCode_DESC</th>\n",
       "      <th>claim_settle_dt</th>\n",
       "      <th>REIMBURSED_YR_TW</th>\n",
       "      <th>INSURED_RK</th>\n",
       "      <th>MATURITY_BENEFICIARY_RK</th>\n",
       "      <th>...</th>\n",
       "      <th>DIGI_FLG</th>\n",
       "      <th>TOPCARD</th>\n",
       "      <th>GENDER</th>\n",
       "      <th>stick_level2</th>\n",
       "      <th>cust_group2</th>\n",
       "      <th>TOTAL_AUM</th>\n",
       "      <th>INSURED_DOB</th>\n",
       "      <th>再購(120天)</th>\n",
       "      <th>再購(180天)</th>\n",
       "      <th>再購(360天)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>POLICY_HOLDER_RK</th>\n",
       "      <th>Policy_RK</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <th>49082</th>\n",
       "      <td>4324</td>\n",
       "      <td>104018</td>\n",
       "      <td>5.N疾病醫療</td>\n",
       "      <td>D36</td>\n",
       "      <td>其他及未明示部位良性腫瘤</td>\n",
       "      <td>02.腫瘤</td>\n",
       "      <td>2017-01-20</td>\n",
       "      <td>9189.60</td>\n",
       "      <td>4324</td>\n",
       "      <td>3.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">4</th>\n",
       "      <th>56714</th>\n",
       "      <td>26347</td>\n",
       "      <td>90482</td>\n",
       "      <td>6.N意外醫療</td>\n",
       "      <td>W18</td>\n",
       "      <td>其他滑倒、摔倒或絆倒和跌落</td>\n",
       "      <td>20.疾病和死亡的外因</td>\n",
       "      <td>2016-12-01</td>\n",
       "      <td>1837.50</td>\n",
       "      <td>26347</td>\n",
       "      <td>26347.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56714</th>\n",
       "      <td>26347</td>\n",
       "      <td>184848</td>\n",
       "      <td>6.N意外醫療</td>\n",
       "      <td>W18</td>\n",
       "      <td>其他滑倒、摔倒或絆倒和跌落</td>\n",
       "      <td>20.疾病和死亡的外因</td>\n",
       "      <td>2017-09-06</td>\n",
       "      <td>2857.05</td>\n",
       "      <td>26347</td>\n",
       "      <td>26347.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <th>84973</th>\n",
       "      <td>93160</td>\n",
       "      <td>183589</td>\n",
       "      <td>5.N疾病醫療</td>\n",
       "      <td>D24</td>\n",
       "      <td>乳房良性腫廇</td>\n",
       "      <td>02.腫瘤</td>\n",
       "      <td>2017-09-02</td>\n",
       "      <td>2835.00</td>\n",
       "      <td>93160</td>\n",
       "      <td>93160.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <th>65407</th>\n",
       "      <td>94922</td>\n",
       "      <td>175992</td>\n",
       "      <td>5.N疾病醫療</td>\n",
       "      <td>L03.90</td>\n",
       "      <td>蜂窩組織炎</td>\n",
       "      <td>12.皮膚和皮下組織疾病</td>\n",
       "      <td>2017-08-13</td>\n",
       "      <td>4200.00</td>\n",
       "      <td>94922</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251945</th>\n",
       "      <th>102196</th>\n",
       "      <td>123213</td>\n",
       "      <td>112437</td>\n",
       "      <td>6.N意外醫療</td>\n",
       "      <td>V23</td>\n",
       "      <td>摩拖車騎士與汽車，小貨車或箱型車碰撞受傷</td>\n",
       "      <td>20.疾病和死亡的外因</td>\n",
       "      <td>2017-02-19</td>\n",
       "      <td>813.75</td>\n",
       "      <td>123213</td>\n",
       "      <td>251945.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>S07</td>\n",
       "      <td>G4</td>\n",
       "      <td>300840.3</td>\n",
       "      <td>1966-01-09</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251947</th>\n",
       "      <th>16872</th>\n",
       "      <td>86041</td>\n",
       "      <td>179452</td>\n",
       "      <td>6.N意外醫療</td>\n",
       "      <td>Y99.8</td>\n",
       "      <td>其他外因狀態</td>\n",
       "      <td>99.不知道不想猜</td>\n",
       "      <td>2017-08-23</td>\n",
       "      <td>1627.50</td>\n",
       "      <td>86041</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>S09</td>\n",
       "      <td>G4</td>\n",
       "      <td>123291.0</td>\n",
       "      <td>1979-05-16</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251949</th>\n",
       "      <th>100554</th>\n",
       "      <td>161848</td>\n",
       "      <td>113170</td>\n",
       "      <td>6.N意外醫療</td>\n",
       "      <td>V99</td>\n",
       "      <td>運輸意外事故</td>\n",
       "      <td>20.疾病和死亡的外因</td>\n",
       "      <td>2017-02-24</td>\n",
       "      <td>262237.50</td>\n",
       "      <td>161848</td>\n",
       "      <td>251949.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251951</th>\n",
       "      <th>96970</th>\n",
       "      <td>134869</td>\n",
       "      <td>174868</td>\n",
       "      <td>5.N疾病醫療</td>\n",
       "      <td>D36</td>\n",
       "      <td>其他及未明示部位良性腫瘤</td>\n",
       "      <td>02.腫瘤</td>\n",
       "      <td>2017-08-11</td>\n",
       "      <td>546.00</td>\n",
       "      <td>134869</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>S07</td>\n",
       "      <td>G4</td>\n",
       "      <td>224832.7</td>\n",
       "      <td>1972-06-22</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>251954</th>\n",
       "      <th>74765</th>\n",
       "      <td>156320</td>\n",
       "      <td>136040</td>\n",
       "      <td>4.C重大疾病</td>\n",
       "      <td>I25.1</td>\n",
       "      <td>自體的冠狀動脈粥樣硬化心臟病</td>\n",
       "      <td>09.循環系統疾病</td>\n",
       "      <td>2017-04-29</td>\n",
       "      <td>16825.20</td>\n",
       "      <td>251954</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>S07</td>\n",
       "      <td>G4</td>\n",
       "      <td>126247.4</td>\n",
       "      <td>1960-09-28</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>234428 rows × 49 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            INJURED_RK  Claim_RK BundleSubtype2 illness_code  \\\n",
       "POLICY_HOLDER_RK Policy_RK                                                     \n",
       "3                49082            4324    104018        5.N疾病醫療          D36   \n",
       "4                56714           26347     90482        6.N意外醫療          W18   \n",
       "                 56714           26347    184848        6.N意外醫療          W18   \n",
       "5                84973           93160    183589        5.N疾病醫療          D24   \n",
       "6                65407           94922    175992        5.N疾病醫療       L03.90   \n",
       "...                                ...       ...            ...          ...   \n",
       "251945           102196         123213    112437        6.N意外醫療          V23   \n",
       "251947           16872           86041    179452        6.N意外醫療        Y99.8   \n",
       "251949           100554         161848    113170        6.N意外醫療          V99   \n",
       "251951           96970          134869    174868        5.N疾病醫療          D36   \n",
       "251954           74765          156320    136040        4.C重大疾病        I25.1   \n",
       "\n",
       "                                    illness_desc DiagnosisCode_DESC  \\\n",
       "POLICY_HOLDER_RK Policy_RK                                            \n",
       "3                49082              其他及未明示部位良性腫瘤              02.腫瘤   \n",
       "4                56714             其他滑倒、摔倒或絆倒和跌落        20.疾病和死亡的外因   \n",
       "                 56714             其他滑倒、摔倒或絆倒和跌落        20.疾病和死亡的外因   \n",
       "5                84973                    乳房良性腫廇              02.腫瘤   \n",
       "6                65407                     蜂窩組織炎       12.皮膚和皮下組織疾病   \n",
       "...                                          ...                ...   \n",
       "251945           102196     摩拖車騎士與汽車，小貨車或箱型車碰撞受傷        20.疾病和死亡的外因   \n",
       "251947           16872                    其他外因狀態          99.不知道不想猜   \n",
       "251949           100554                   運輸意外事故        20.疾病和死亡的外因   \n",
       "251951           96970              其他及未明示部位良性腫瘤              02.腫瘤   \n",
       "251954           74765            自體的冠狀動脈粥樣硬化心臟病          09.循環系統疾病   \n",
       "\n",
       "                           claim_settle_dt  REIMBURSED_YR_TW  INSURED_RK  \\\n",
       "POLICY_HOLDER_RK Policy_RK                                                 \n",
       "3                49082          2017-01-20           9189.60        4324   \n",
       "4                56714          2016-12-01           1837.50       26347   \n",
       "                 56714          2017-09-06           2857.05       26347   \n",
       "5                84973          2017-09-02           2835.00       93160   \n",
       "6                65407          2017-08-13           4200.00       94922   \n",
       "...                                    ...               ...         ...   \n",
       "251945           102196         2017-02-19            813.75      123213   \n",
       "251947           16872          2017-08-23           1627.50       86041   \n",
       "251949           100554         2017-02-24         262237.50      161848   \n",
       "251951           96970          2017-08-11            546.00      134869   \n",
       "251954           74765          2017-04-29          16825.20      251954   \n",
       "\n",
       "                            MATURITY_BENEFICIARY_RK  ...  DIGI_FLG  TOPCARD  \\\n",
       "POLICY_HOLDER_RK Policy_RK                           ...                      \n",
       "3                49082                          3.0  ...       NaN      NaN   \n",
       "4                56714                      26347.0  ...       NaN      NaN   \n",
       "                 56714                      26347.0  ...       NaN      NaN   \n",
       "5                84973                      93160.0  ...       NaN      NaN   \n",
       "6                65407                          NaN  ...       NaN      NaN   \n",
       "...                                             ...  ...       ...      ...   \n",
       "251945           102196                    251945.0  ...       0.0      0.0   \n",
       "251947           16872                          NaN  ...       0.0      0.0   \n",
       "251949           100554                    251949.0  ...       NaN      NaN   \n",
       "251951           96970                          NaN  ...       0.0      0.0   \n",
       "251954           74765                          NaN  ...       0.0      0.0   \n",
       "\n",
       "                           GENDER  stick_level2 cust_group2 TOTAL_AUM  \\\n",
       "POLICY_HOLDER_RK Policy_RK                                              \n",
       "3                49082        NaN           NaN         NaN       NaN   \n",
       "4                56714        NaN           NaN         NaN       NaN   \n",
       "                 56714        NaN           NaN         NaN       NaN   \n",
       "5                84973        NaN           NaN         NaN       NaN   \n",
       "6                65407        NaN           NaN         NaN       NaN   \n",
       "...                           ...           ...         ...       ...   \n",
       "251945           102196       1.0           S07          G4  300840.3   \n",
       "251947           16872        1.0           S09          G4  123291.0   \n",
       "251949           100554       NaN           NaN         NaN       NaN   \n",
       "251951           96970        1.0           S07          G4  224832.7   \n",
       "251954           74765        1.0           S07          G4  126247.4   \n",
       "\n",
       "                           INSURED_DOB  再購(120天)  再購(180天)  再購(360天)  \n",
       "POLICY_HOLDER_RK Policy_RK                                            \n",
       "3                49082             NaT         0         0         0  \n",
       "4                56714             NaT         0         0         1  \n",
       "                 56714             NaT         1         1         1  \n",
       "5                84973             NaT         1         1         1  \n",
       "6                65407             NaT         0         0         0  \n",
       "...                                ...       ...       ...       ...  \n",
       "251945           102196     1966-01-09         0         0         0  \n",
       "251947           16872      1979-05-16         0         0         0  \n",
       "251949           100554            NaT         0         0         0  \n",
       "251951           96970      1972-06-22         1         1         1  \n",
       "251954           74765      1960-09-28         0         0         0  \n",
       "\n",
       "[234428 rows x 49 columns]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df8"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "df8.to_excel('理賠再購屬性合併.xlsx')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}