sample_notebooks/preprocess_2021_dataset.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "233ee8c8-171a-4db4-b140-27fe97e93e42",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2022-04-24 18:07:52,193 ilectools.data_prep INFO:  Downloading ilec-2009-18-20210528.zip\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{2021: 'https://cdn-files.soa.org/research/ilec/ilec-2009-18-20210528.zip', 2018: 'https://cdn-files.soa.org/web/ilec-2016/ilec-data-set.zip'}\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2022-04-24 18:09:48,769 ilectools.data_prep INFO:  Download complete\n",
      "2022-04-24 18:09:48,769 ilectools.data_prep INFO:  Decompressing ilec-2009-18-20210528.zip\n",
      "2022-04-24 18:11:01,522 ilectools.data_prep INFO:  Decompressed ILEC 2009-18 20210528.csv\n",
      "2022-04-24 18:11:01,523 ilectools.data_prep INFO:  Reading original csv /home/brian/data/ilec/Data/Processed/2021_published/ILEC 2009-18 20210528.csv\n",
      "2022-04-24 18:11:03,260 ilectools.data_prep INFO:  0 records\n",
      "2022-04-24 18:11:04,968 ilectools.data_prep INFO:  500,000 records\n",
      "2022-04-24 18:11:06,648 ilectools.data_prep INFO:  1,000,000 records\n",
      "2022-04-24 18:11:08,286 ilectools.data_prep INFO:  1,500,000 records\n",
      "2022-04-24 18:11:09,948 ilectools.data_prep INFO:  2,000,000 records\n",
      "2022-04-24 18:11:11,606 ilectools.data_prep INFO:  2,500,000 records\n",
      "2022-04-24 18:11:13,183 ilectools.data_prep INFO:  3,000,000 records\n",
      "2022-04-24 18:11:14,804 ilectools.data_prep INFO:  3,500,000 records\n",
      "2022-04-24 18:11:16,427 ilectools.data_prep INFO:  4,000,000 records\n",
      "2022-04-24 18:11:18,044 ilectools.data_prep INFO:  4,500,000 records\n",
      "2022-04-24 18:11:19,656 ilectools.data_prep INFO:  5,000,000 records\n",
      "2022-04-24 18:11:21,256 ilectools.data_prep INFO:  5,500,000 records\n",
      "2022-04-24 18:11:22,893 ilectools.data_prep INFO:  6,000,000 records\n",
      "2022-04-24 18:11:24,475 ilectools.data_prep INFO:  6,500,000 records\n",
      "2022-04-24 18:11:26,087 ilectools.data_prep INFO:  7,000,000 records\n",
      "2022-04-24 18:11:27,690 ilectools.data_prep INFO:  7,500,000 records\n",
      "2022-04-24 18:11:29,332 ilectools.data_prep INFO:  8,000,000 records\n",
      "2022-04-24 18:11:30,956 ilectools.data_prep INFO:  8,500,000 records\n",
      "2022-04-24 18:11:32,509 ilectools.data_prep INFO:  9,000,000 records\n",
      "2022-04-24 18:11:34,091 ilectools.data_prep INFO:  9,500,000 records\n",
      "2022-04-24 18:11:35,632 ilectools.data_prep INFO:  10,000,000 records\n",
      "2022-04-24 18:11:37,260 ilectools.data_prep INFO:  10,500,000 records\n",
      "2022-04-24 18:11:38,862 ilectools.data_prep INFO:  11,000,000 records\n",
      "2022-04-24 18:11:40,476 ilectools.data_prep INFO:  11,500,000 records\n",
      "2022-04-24 18:11:42,069 ilectools.data_prep INFO:  12,000,000 records\n",
      "2022-04-24 18:11:43,681 ilectools.data_prep INFO:  12,500,000 records\n",
      "2022-04-24 18:11:45,278 ilectools.data_prep INFO:  13,000,000 records\n",
      "2022-04-24 18:11:46,901 ilectools.data_prep INFO:  13,500,000 records\n",
      "2022-04-24 18:11:48,534 ilectools.data_prep INFO:  14,000,000 records\n",
      "2022-04-24 18:11:50,161 ilectools.data_prep INFO:  14,500,000 records\n",
      "2022-04-24 18:11:51,794 ilectools.data_prep INFO:  15,000,000 records\n",
      "2022-04-24 18:11:53,401 ilectools.data_prep INFO:  15,500,000 records\n",
      "2022-04-24 18:11:55,025 ilectools.data_prep INFO:  16,000,000 records\n",
      "2022-04-24 18:11:56,631 ilectools.data_prep INFO:  16,500,000 records\n",
      "2022-04-24 18:11:58,247 ilectools.data_prep INFO:  17,000,000 records\n",
      "2022-04-24 18:11:59,847 ilectools.data_prep INFO:  17,500,000 records\n",
      "2022-04-24 18:12:01,440 ilectools.data_prep INFO:  18,000,000 records\n",
      "2022-04-24 18:12:03,049 ilectools.data_prep INFO:  18,500,000 records\n",
      "2022-04-24 18:12:04,703 ilectools.data_prep INFO:  19,000,000 records\n",
      "2022-04-24 18:12:06,236 ilectools.data_prep INFO:  19,500,000 records\n",
      "2022-04-24 18:12:07,804 ilectools.data_prep INFO:  20,000,000 records\n",
      "2022-04-24 18:12:09,332 ilectools.data_prep INFO:  20,500,000 records\n",
      "2022-04-24 18:12:10,937 ilectools.data_prep INFO:  21,000,000 records\n",
      "2022-04-24 18:12:12,498 ilectools.data_prep INFO:  21,500,000 records\n",
      "2022-04-24 18:12:14,077 ilectools.data_prep INFO:  22,000,000 records\n",
      "2022-04-24 18:12:15,623 ilectools.data_prep INFO:  22,500,000 records\n",
      "2022-04-24 18:12:17,201 ilectools.data_prep INFO:  23,000,000 records\n",
      "2022-04-24 18:12:18,778 ilectools.data_prep INFO:  23,500,000 records\n",
      "2022-04-24 18:12:20,348 ilectools.data_prep INFO:  24,000,000 records\n",
      "2022-04-24 18:12:21,957 ilectools.data_prep INFO:  24,500,000 records\n",
      "2022-04-24 18:12:23,545 ilectools.data_prep INFO:  25,000,000 records\n",
      "2022-04-24 18:12:25,119 ilectools.data_prep INFO:  25,500,000 records\n",
      "2022-04-24 18:12:26,745 ilectools.data_prep INFO:  26,000,000 records\n",
      "2022-04-24 18:12:28,321 ilectools.data_prep INFO:  26,500,000 records\n",
      "2022-04-24 18:12:29,896 ilectools.data_prep INFO:  27,000,000 records\n",
      "2022-04-24 18:12:31,470 ilectools.data_prep INFO:  27,500,000 records\n",
      "2022-04-24 18:12:32,994 ilectools.data_prep INFO:  28,000,000 records\n",
      "2022-04-24 18:12:34,617 ilectools.data_prep INFO:  28,500,000 records\n",
      "2022-04-24 18:12:36,167 ilectools.data_prep INFO:  29,000,000 records\n",
      "2022-04-24 18:12:37,736 ilectools.data_prep INFO:  29,500,000 records\n",
      "2022-04-24 18:12:39,332 ilectools.data_prep INFO:  30,000,000 records\n",
      "2022-04-24 18:12:40,905 ilectools.data_prep INFO:  30,500,000 records\n",
      "2022-04-24 18:12:42,458 ilectools.data_prep INFO:  31,000,000 records\n",
      "2022-04-24 18:12:44,063 ilectools.data_prep INFO:  31,500,000 records\n",
      "2022-04-24 18:12:45,639 ilectools.data_prep INFO:  32,000,000 records\n",
      "2022-04-24 18:12:47,199 ilectools.data_prep INFO:  32,500,000 records\n",
      "2022-04-24 18:12:48,779 ilectools.data_prep INFO:  33,000,000 records\n",
      "2022-04-24 18:12:50,356 ilectools.data_prep INFO:  33,500,000 records\n",
      "2022-04-24 18:12:51,950 ilectools.data_prep INFO:  34,000,000 records\n",
      "2022-04-24 18:12:53,554 ilectools.data_prep INFO:  34,500,000 records\n",
      "2022-04-24 18:12:55,161 ilectools.data_prep INFO:  35,000,000 records\n",
      "2022-04-24 18:12:56,759 ilectools.data_prep INFO:  35,500,000 records\n",
      "2022-04-24 18:12:58,353 ilectools.data_prep INFO:  36,000,000 records\n",
      "2022-04-24 18:12:59,985 ilectools.data_prep INFO:  36,500,000 records\n",
      "2022-04-24 18:13:01,594 ilectools.data_prep INFO:  37,000,000 records\n",
      "2022-04-24 18:13:03,216 ilectools.data_prep INFO:  37,500,000 records\n",
      "2022-04-24 18:13:04,838 ilectools.data_prep INFO:  38,000,000 records\n",
      "2022-04-24 18:13:06,471 ilectools.data_prep INFO:  38,500,000 records\n",
      "2022-04-24 18:13:08,063 ilectools.data_prep INFO:  39,000,000 records\n",
      "2022-04-24 18:13:09,719 ilectools.data_prep INFO:  39,500,000 records\n",
      "2022-04-24 18:13:11,328 ilectools.data_prep INFO:  40,000,000 records\n",
      "2022-04-24 18:13:12,953 ilectools.data_prep INFO:  40,500,000 records\n",
      "2022-04-24 18:13:14,580 ilectools.data_prep INFO:  41,000,000 records\n",
      "2022-04-24 18:13:16,162 ilectools.data_prep INFO:  41,500,000 records\n",
      "2022-04-24 18:13:17,323 ilectools.data_prep INFO:  42,000,000 records\n",
      "2022-04-24 18:13:17,342 ilectools.data_prep INFO:  ... read.  Concatenating:\n",
      "2022-04-24 18:13:25,366 ilectools.data_prep INFO:  ...concatenated, 42,369,416 records in result.\n",
      "2022-04-24 18:13:25,367 ilectools.data_prep INFO:  Saving pkl...\n",
      "2022-04-24 18:14:08,568 ilectools.data_prep INFO:  ...saved.\n",
      "2022-04-24 18:14:08,577 ilectools.data_prep INFO:  Saving parquet...\n",
      "2022-04-24 18:14:36,799 ilectools.data_prep INFO:  ... saved.\n",
      "2022-04-24 18:15:04,460 ilectools.data_prep INFO:  set uw column\n",
      "2022-04-24 18:15:36,713 ilectools.data_prep INFO:  set band min, band max\n",
      "2022-04-24 18:15:41,433 ilectools.data_prep INFO:  Saving parquet...\n",
      "2022-04-24 18:16:14,145 ilectools.data_prep INFO:  Saved v1: /home/brian/data/ilec/Data/Processed/2021_published/ILEC 2009-18 20210528_v1.parquet\n",
      "2022-04-24 18:16:14,152 ilectools.data_prep INFO:  Dropping rows for minors\n",
      "2022-04-24 18:19:01,213 ilectools.data_prep INFO:  Dropping columns for moments, industry tables besides 2015vbt\n",
      "2022-04-24 18:19:16,022 ilectools.data_prep INFO:  Saving as parquet\n",
      "2022-04-24 18:19:38,201 ilectools.data_prep INFO:  Saved v2: /home/brian/data/ilec/Data/Processed/2021_published/ILEC 2009-18 20210528_v2.parquet\n"
     ]
    },
    {
     "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>observation_year</th>\n",
       "      <th>preferred_indicator</th>\n",
       "      <th>gender</th>\n",
       "      <th>smoker_status</th>\n",
       "      <th>insurance_plan</th>\n",
       "      <th>issue_age</th>\n",
       "      <th>duration</th>\n",
       "      <th>attained_age</th>\n",
       "      <th>age_basis</th>\n",
       "      <th>face_amount_band</th>\n",
       "      <th>...</th>\n",
       "      <th>amount_actual</th>\n",
       "      <th>policy_exposure</th>\n",
       "      <th>amount_exposure</th>\n",
       "      <th>amount_2015vbt</th>\n",
       "      <th>policy_2015vbt</th>\n",
       "      <th>expdeathqx2015vbtwmi_bypol</th>\n",
       "      <th>expdeathqx2015vbtwmi_byamt</th>\n",
       "      <th>uw</th>\n",
       "      <th>band_min</th>\n",
       "      <th>band_max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>row</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>7101</th>\n",
       "      <td>2009</td>\n",
       "      <td>0</td>\n",
       "      <td>Female</td>\n",
       "      <td>Nonsmoker</td>\n",
       "      <td>Perm</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>ALB</td>\n",
       "      <td>1-9999</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.643836</td>\n",
       "      <td>13219.180000</td>\n",
       "      <td>3.569179</td>\n",
       "      <td>0.000714</td>\n",
       "      <td>0.000740</td>\n",
       "      <td>3.699968</td>\n",
       "      <td>N/1/1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>9999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7102</th>\n",
       "      <td>2009</td>\n",
       "      <td>0</td>\n",
       "      <td>Female</td>\n",
       "      <td>Nonsmoker</td>\n",
       "      <td>Perm</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>ALB</td>\n",
       "      <td>25000-49999</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>16.164384</td>\n",
       "      <td>441492.129666</td>\n",
       "      <td>119.202875</td>\n",
       "      <td>0.004364</td>\n",
       "      <td>0.004524</td>\n",
       "      <td>123.570952</td>\n",
       "      <td>N/1/1</td>\n",
       "      <td>25000.0</td>\n",
       "      <td>49999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7103</th>\n",
       "      <td>2009</td>\n",
       "      <td>0</td>\n",
       "      <td>Female</td>\n",
       "      <td>Nonsmoker</td>\n",
       "      <td>Perm</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>ALB</td>\n",
       "      <td>50000-99999</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.575343</td>\n",
       "      <td>139383.600000</td>\n",
       "      <td>37.633572</td>\n",
       "      <td>0.000695</td>\n",
       "      <td>0.000721</td>\n",
       "      <td>39.012619</td>\n",
       "      <td>N/1/1</td>\n",
       "      <td>50000.0</td>\n",
       "      <td>99999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7104</th>\n",
       "      <td>2009</td>\n",
       "      <td>0</td>\n",
       "      <td>Female</td>\n",
       "      <td>Nonsmoker</td>\n",
       "      <td>Perm</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>ANB</td>\n",
       "      <td>10000-24999</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.898630</td>\n",
       "      <td>8986.300000</td>\n",
       "      <td>2.426301</td>\n",
       "      <td>0.000243</td>\n",
       "      <td>0.000252</td>\n",
       "      <td>2.515211</td>\n",
       "      <td>N/1/1</td>\n",
       "      <td>10000.0</td>\n",
       "      <td>24999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7105</th>\n",
       "      <td>2009</td>\n",
       "      <td>0</td>\n",
       "      <td>Female</td>\n",
       "      <td>Nonsmoker</td>\n",
       "      <td>Perm</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>18</td>\n",
       "      <td>ANB</td>\n",
       "      <td>25000-49999</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8.750684</td>\n",
       "      <td>225829.950350</td>\n",
       "      <td>60.974087</td>\n",
       "      <td>0.002363</td>\n",
       "      <td>0.002449</td>\n",
       "      <td>63.208424</td>\n",
       "      <td>N/1/1</td>\n",
       "      <td>25000.0</td>\n",
       "      <td>49999.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      observation_year  preferred_indicator  gender smoker_status  \\\n",
       "row                                                                 \n",
       "7101              2009                    0  Female     Nonsmoker   \n",
       "7102              2009                    0  Female     Nonsmoker   \n",
       "7103              2009                    0  Female     Nonsmoker   \n",
       "7104              2009                    0  Female     Nonsmoker   \n",
       "7105              2009                    0  Female     Nonsmoker   \n",
       "\n",
       "     insurance_plan  issue_age  duration  attained_age age_basis  \\\n",
       "row                                                                \n",
       "7101           Perm         18         1            18       ALB   \n",
       "7102           Perm         18         1            18       ALB   \n",
       "7103           Perm         18         1            18       ALB   \n",
       "7104           Perm         18         1            18       ANB   \n",
       "7105           Perm         18         1            18       ANB   \n",
       "\n",
       "     face_amount_band  ...  amount_actual  policy_exposure  amount_exposure  \\\n",
       "row                    ...                                                    \n",
       "7101           1-9999  ...            0.0         2.643836     13219.180000   \n",
       "7102      25000-49999  ...            0.0        16.164384    441492.129666   \n",
       "7103      50000-99999  ...            0.0         2.575343    139383.600000   \n",
       "7104      10000-24999  ...            0.0         0.898630      8986.300000   \n",
       "7105      25000-49999  ...            0.0         8.750684    225829.950350   \n",
       "\n",
       "     amount_2015vbt policy_2015vbt expdeathqx2015vbtwmi_bypol  \\\n",
       "row                                                             \n",
       "7101       3.569179       0.000714                   0.000740   \n",
       "7102     119.202875       0.004364                   0.004524   \n",
       "7103      37.633572       0.000695                   0.000721   \n",
       "7104       2.426301       0.000243                   0.000252   \n",
       "7105      60.974087       0.002363                   0.002449   \n",
       "\n",
       "     expdeathqx2015vbtwmi_byamt     uw  band_min  band_max  \n",
       "row                                                         \n",
       "7101                   3.699968  N/1/1       1.0    9999.0  \n",
       "7102                 123.570952  N/1/1   25000.0   49999.0  \n",
       "7103                  39.012619  N/1/1   50000.0   99999.0  \n",
       "7104                   2.515211  N/1/1   10000.0   24999.0  \n",
       "7105                  63.208424  N/1/1   25000.0   49999.0  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd, numpy as np, os, datetime as dt\n",
    "from pathlib import Path\n",
    "import os\n",
    "\n",
    "import sys\n",
    "if not '/home/brian/dev/ILECTools' in sys.path:\n",
    "    sys.path.append('/home/brian/dev/ILECTools')\n",
    "    \n",
    "from ilectools.data_prep import prepare_all, URLS\n",
    "%config Completer.use_jedi = False\n",
    "data_dir = '/home/brian/data/ilec/Data/Processed/2021_published'\n",
    "\n",
    "print(URLS)\n",
    "\n",
    "data = prepare_all(URLS[2021], data_dir)\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5df6cad1-24e5-407e-89c7-bca5ff2af070",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:ilec0]",
   "language": "python",
   "name": "conda-env-ilec0-py"
  },
  "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.10.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}