2017_loaded_CSO_generate.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from itertools import product\n",
    "import random\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Metadata dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>index</th>\n",
       "      <th>TableNumber</th>\n",
       "      <th>TableSet</th>\n",
       "      <th>Underwriting</th>\n",
       "      <th>Sex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>3299</td>\n",
       "      <td>2017 Loaded CSO Preferred Structure</td>\n",
       "      <td>NS_SP</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>3300</td>\n",
       "      <td>2017 Loaded CSO Preferred Structure</td>\n",
       "      <td>NS_P</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>3301</td>\n",
       "      <td>2017 Loaded CSO Preferred Structure</td>\n",
       "      <td>NS_R</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>3302</td>\n",
       "      <td>2017 Loaded CSO Preferred Structure</td>\n",
       "      <td>NS_SP</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>3303</td>\n",
       "      <td>2017 Loaded CSO Preferred Structure</td>\n",
       "      <td>NS_P</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>3304</td>\n",
       "      <td>2017 Loaded CSO Preferred Structure</td>\n",
       "      <td>NS_R</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>3305</td>\n",
       "      <td>2017 Loaded CSO Preferred Structure</td>\n",
       "      <td>S_P</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>3306</td>\n",
       "      <td>2017 Loaded CSO Preferred Structure</td>\n",
       "      <td>S_R</td>\n",
       "      <td>Male</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>3307</td>\n",
       "      <td>2017 Loaded CSO Preferred Structure</td>\n",
       "      <td>S_P</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>3308</td>\n",
       "      <td>2017 Loaded CSO Preferred Structure</td>\n",
       "      <td>S_R</td>\n",
       "      <td>Female</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index  TableNumber                             TableSet Underwriting  \\\n",
       "0      0         3299  2017 Loaded CSO Preferred Structure        NS_SP   \n",
       "1      1         3300  2017 Loaded CSO Preferred Structure         NS_P   \n",
       "2      2         3301  2017 Loaded CSO Preferred Structure         NS_R   \n",
       "3      3         3302  2017 Loaded CSO Preferred Structure        NS_SP   \n",
       "4      4         3303  2017 Loaded CSO Preferred Structure         NS_P   \n",
       "5      5         3304  2017 Loaded CSO Preferred Structure         NS_R   \n",
       "6      6         3305  2017 Loaded CSO Preferred Structure          S_P   \n",
       "7      7         3306  2017 Loaded CSO Preferred Structure          S_R   \n",
       "8      8         3307  2017 Loaded CSO Preferred Structure          S_P   \n",
       "9      9         3308  2017 Loaded CSO Preferred Structure          S_R   \n",
       "\n",
       "      Sex  \n",
       "0    Male  \n",
       "1    Male  \n",
       "2    Male  \n",
       "3  Female  \n",
       "4  Female  \n",
       "5  Female  \n",
       "6    Male  \n",
       "7    Male  \n",
       "8  Female  \n",
       "9  Female  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table_set_loaded = '2017 Loaded CSO Preferred Structure'\n",
    "table_set_unloaded = '2017 Unloaded CSO Preferred Structure'\n",
    "\n",
    "tuples = [\n",
    "    (3299, table_set_loaded, \"NS_SP\", \"Male\"),\n",
    "    (3300, table_set_loaded, \"NS_P\", \"Male\"),\n",
    "    (3301, table_set_loaded, \"NS_R\", \"Male\"),\n",
    "    (3302, table_set_loaded, \"NS_SP\", \"Female\"),\n",
    "    (3303, table_set_loaded, \"NS_P\", \"Female\"),\n",
    "    (3304, table_set_loaded, \"NS_R\", \"Female\"),\n",
    "    (3305, table_set_loaded, \"S_P\", \"Male\"),\n",
    "    (3306, table_set_loaded, \"S_R\", \"Male\"),\n",
    "    (3307, table_set_loaded, \"S_P\", \"Female\"),\n",
    "    (3308, table_set_loaded, \"S_R\", \"Female\"),\n",
    "]\n",
    "\n",
    "meta_df = pd.DataFrame(tuples, columns=['TableNumber', 'TableSet', 'Underwriting', 'Sex'])\n",
    "meta_df.reset_index(inplace=True)\n",
    "meta_df.to_csv('assumption_tables/2017_loaded_CSO_index.csv', index=False)\n",
    "meta_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Generate a tidy dataframe\n",
    "\n",
    "Attempt to make a format that is similar to the format from the HeavyTable library"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['index', 'IssueAge', 'Duration', 'select_vals', 'TableNumber'], dtype='object')\n"
     ]
    }
   ],
   "source": [
    "from pymort import MortXML\n",
    "\n",
    "issue_attained_tuples = [(x, y, y-x+1) for x, y in product(range(18, 121), repeat=2) if y >= x]\n",
    "template_df = pd.DataFrame(issue_attained_tuples, columns=['IssueAge', 'Age', 'Duration'])\n",
    "tidy_format = pd.merge(template_df.assign(key=0), meta_df.assign(key=0), on='key').sort_values(['TableNumber', 'Age', 'Duration'])\n",
    "select_rate_dfs = []\n",
    "ultimate_rate_dfs = []\n",
    "for table_number in meta_df['TableNumber']:\n",
    "    select_rate_df = MortXML.from_id(table_number).Tables[0].Values\n",
    "    select_rate_df.reset_index(inplace=True)\n",
    "    ultimate_rate_df = MortXML.from_id(table_number).Tables[1].Values\n",
    "    ultimate_rate_df.reset_index(inplace=True)\n",
    "    select_rate_df.rename(columns={\"vals\": \"select_vals\", \"Age\": \"IssueAge\"}, inplace=True)\n",
    "    ultimate_rate_df.rename(columns={\"vals\": \"ultimate_vals\"}, inplace=True)\n",
    "    select_rate_df.reset_index(inplace=True)\n",
    "    ultimate_rate_df.reset_index(inplace=True)\n",
    "    select_rate_df[\"TableNumber\"] = table_number\n",
    "    ultimate_rate_df[\"TableNumber\"] = table_number\n",
    "    select_rate_dfs.append(select_rate_df)\n",
    "    ultimate_rate_dfs.append(ultimate_rate_df)\n",
    "\n",
    "select_rate_df = pd.concat(select_rate_dfs)\n",
    "print(select_rate_df.columns)\n",
    "ultimate_rate_df = pd.concat(ultimate_rate_dfs)\n",
    "\n",
    "final_df = tidy_format \\\n",
    "    .merge(select_rate_df, on=['TableNumber', 'IssueAge', 'Duration'], how='left') \\\n",
    "    .merge(ultimate_rate_df, on=['TableNumber', 'Age'], how='left')\n",
    "\n",
    "# use ultimate_vals if vals is null\n",
    "final_df['vals'] = final_df['select_vals'].fillna(final_df['ultimate_vals'])\n",
    "final_df = final_df[['IssueAge','Age', 'Duration', 'Underwriting', 'Sex', 'vals']].sort_values(['Underwriting', 'Sex', 'IssueAge', 'Age', 'Duration'])\n",
    "final_df.to_csv('assumption_tables/2017_loaded_CSO_mortality_rates.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Generate an array\n",
    "\n",
    "Store directly as NumPy, assume users will linear index into the mortality table with no string lookups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "select = np.array([MortXML.from_id(id).Tables[0].Values.unstack().values for id in meta_df['TableNumber']])\n",
    "ultimate = np.array([MortXML.from_id(id).Tables[1].Values.unstack().values for id in meta_df['TableNumber']])\n",
    "with open('assumption_tables/2017_loaded_CSO_mortality_rates_select.npy', 'wb') as f:\n",
    "    np.save(f, select)\n",
    "with open('assumption_tables/2017_loaded_CSO_mortality_rates_ultimate.npy', 'wb') as f:\n",
    "    np.save(f, ultimate)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>...</th>\n",
       "      <th>15</th>\n",
       "      <th>16</th>\n",
       "      <th>17</th>\n",
       "      <th>18</th>\n",
       "      <th>19</th>\n",
       "      <th>20</th>\n",
       "      <th>21</th>\n",
       "      <th>22</th>\n",
       "      <th>23</th>\n",
       "      <th>24</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.00070</td>\n",
       "      <td>0.00072</td>\n",
       "      <td>0.00075</td>\n",
       "      <td>0.00078</td>\n",
       "      <td>0.00075</td>\n",
       "      <td>0.00072</td>\n",
       "      <td>0.00072</td>\n",
       "      <td>0.00072</td>\n",
       "      <td>0.00061</td>\n",
       "      <td>0.00058</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00057</td>\n",
       "      <td>0.00065</td>\n",
       "      <td>0.00075</td>\n",
       "      <td>0.00088</td>\n",
       "      <td>0.00099</td>\n",
       "      <td>0.00109</td>\n",
       "      <td>0.00115</td>\n",
       "      <td>0.00120</td>\n",
       "      <td>0.00127</td>\n",
       "      <td>0.00135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.00065</td>\n",
       "      <td>0.00068</td>\n",
       "      <td>0.00069</td>\n",
       "      <td>0.00065</td>\n",
       "      <td>0.00065</td>\n",
       "      <td>0.00066</td>\n",
       "      <td>0.00066</td>\n",
       "      <td>0.00061</td>\n",
       "      <td>0.00053</td>\n",
       "      <td>0.00046</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00058</td>\n",
       "      <td>0.00067</td>\n",
       "      <td>0.00078</td>\n",
       "      <td>0.00090</td>\n",
       "      <td>0.00102</td>\n",
       "      <td>0.00112</td>\n",
       "      <td>0.00118</td>\n",
       "      <td>0.00125</td>\n",
       "      <td>0.00133</td>\n",
       "      <td>0.00141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.00056</td>\n",
       "      <td>0.00057</td>\n",
       "      <td>0.00063</td>\n",
       "      <td>0.00061</td>\n",
       "      <td>0.00059</td>\n",
       "      <td>0.00058</td>\n",
       "      <td>0.00056</td>\n",
       "      <td>0.00051</td>\n",
       "      <td>0.00046</td>\n",
       "      <td>0.00046</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00060</td>\n",
       "      <td>0.00070</td>\n",
       "      <td>0.00080</td>\n",
       "      <td>0.00092</td>\n",
       "      <td>0.00105</td>\n",
       "      <td>0.00115</td>\n",
       "      <td>0.00121</td>\n",
       "      <td>0.00127</td>\n",
       "      <td>0.00135</td>\n",
       "      <td>0.00145</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.00047</td>\n",
       "      <td>0.00057</td>\n",
       "      <td>0.00051</td>\n",
       "      <td>0.00049</td>\n",
       "      <td>0.00048</td>\n",
       "      <td>0.00047</td>\n",
       "      <td>0.00046</td>\n",
       "      <td>0.00043</td>\n",
       "      <td>0.00040</td>\n",
       "      <td>0.00043</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00063</td>\n",
       "      <td>0.00072</td>\n",
       "      <td>0.00082</td>\n",
       "      <td>0.00095</td>\n",
       "      <td>0.00107</td>\n",
       "      <td>0.00115</td>\n",
       "      <td>0.00121</td>\n",
       "      <td>0.00128</td>\n",
       "      <td>0.00138</td>\n",
       "      <td>0.00149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.00045</td>\n",
       "      <td>0.00047</td>\n",
       "      <td>0.00043</td>\n",
       "      <td>0.00040</td>\n",
       "      <td>0.00039</td>\n",
       "      <td>0.00039</td>\n",
       "      <td>0.00038</td>\n",
       "      <td>0.00037</td>\n",
       "      <td>0.00036</td>\n",
       "      <td>0.00041</td>\n",
       "      <td>...</td>\n",
       "      <td>0.00065</td>\n",
       "      <td>0.00074</td>\n",
       "      <td>0.00085</td>\n",
       "      <td>0.00098</td>\n",
       "      <td>0.00110</td>\n",
       "      <td>0.00116</td>\n",
       "      <td>0.00122</td>\n",
       "      <td>0.00128</td>\n",
       "      <td>0.00140</td>\n",
       "      <td>0.00154</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>73</th>\n",
       "      <td>0.02368</td>\n",
       "      <td>0.06223</td>\n",
       "      <td>0.14189</td>\n",
       "      <td>0.22673</td>\n",
       "      <td>0.24340</td>\n",
       "      <td>0.26243</td>\n",
       "      <td>0.28269</td>\n",
       "      <td>0.30471</td>\n",
       "      <td>0.32800</td>\n",
       "      <td>0.35209</td>\n",
       "      <td>...</td>\n",
       "      <td>0.47743</td>\n",
       "      <td>0.50332</td>\n",
       "      <td>0.53061</td>\n",
       "      <td>0.55939</td>\n",
       "      <td>0.58972</td>\n",
       "      <td>0.62170</td>\n",
       "      <td>0.65542</td>\n",
       "      <td>0.69096</td>\n",
       "      <td>0.72843</td>\n",
       "      <td>0.76794</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>0.03362</td>\n",
       "      <td>0.09526</td>\n",
       "      <td>0.22673</td>\n",
       "      <td>0.24340</td>\n",
       "      <td>0.26243</td>\n",
       "      <td>0.28269</td>\n",
       "      <td>0.30471</td>\n",
       "      <td>0.32800</td>\n",
       "      <td>0.35209</td>\n",
       "      <td>0.37447</td>\n",
       "      <td>...</td>\n",
       "      <td>0.50332</td>\n",
       "      <td>0.53061</td>\n",
       "      <td>0.55939</td>\n",
       "      <td>0.58972</td>\n",
       "      <td>0.62170</td>\n",
       "      <td>0.65542</td>\n",
       "      <td>0.69096</td>\n",
       "      <td>0.72843</td>\n",
       "      <td>0.76794</td>\n",
       "      <td>0.80958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>0.04979</td>\n",
       "      <td>0.14355</td>\n",
       "      <td>0.24340</td>\n",
       "      <td>0.26243</td>\n",
       "      <td>0.28269</td>\n",
       "      <td>0.30471</td>\n",
       "      <td>0.32800</td>\n",
       "      <td>0.35209</td>\n",
       "      <td>0.37447</td>\n",
       "      <td>0.39680</td>\n",
       "      <td>...</td>\n",
       "      <td>0.53061</td>\n",
       "      <td>0.55939</td>\n",
       "      <td>0.58972</td>\n",
       "      <td>0.62170</td>\n",
       "      <td>0.65542</td>\n",
       "      <td>0.69096</td>\n",
       "      <td>0.72843</td>\n",
       "      <td>0.76794</td>\n",
       "      <td>0.80958</td>\n",
       "      <td>0.85348</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>0.07304</td>\n",
       "      <td>0.16782</td>\n",
       "      <td>0.26243</td>\n",
       "      <td>0.28269</td>\n",
       "      <td>0.30471</td>\n",
       "      <td>0.32800</td>\n",
       "      <td>0.35209</td>\n",
       "      <td>0.37447</td>\n",
       "      <td>0.39680</td>\n",
       "      <td>0.41866</td>\n",
       "      <td>...</td>\n",
       "      <td>0.55939</td>\n",
       "      <td>0.58972</td>\n",
       "      <td>0.62170</td>\n",
       "      <td>0.65542</td>\n",
       "      <td>0.69096</td>\n",
       "      <td>0.72843</td>\n",
       "      <td>0.76794</td>\n",
       "      <td>0.80958</td>\n",
       "      <td>0.85348</td>\n",
       "      <td>0.89977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>0.10381</td>\n",
       "      <td>0.26243</td>\n",
       "      <td>0.28269</td>\n",
       "      <td>0.30471</td>\n",
       "      <td>0.32800</td>\n",
       "      <td>0.35209</td>\n",
       "      <td>0.37447</td>\n",
       "      <td>0.39680</td>\n",
       "      <td>0.41866</td>\n",
       "      <td>0.43965</td>\n",
       "      <td>...</td>\n",
       "      <td>0.58972</td>\n",
       "      <td>0.62170</td>\n",
       "      <td>0.65542</td>\n",
       "      <td>0.69096</td>\n",
       "      <td>0.72843</td>\n",
       "      <td>0.76794</td>\n",
       "      <td>0.80958</td>\n",
       "      <td>0.85348</td>\n",
       "      <td>0.89977</td>\n",
       "      <td>0.94856</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>78 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         0        1        2        3        4        5        6        7   \\\n",
       "0   0.00070  0.00072  0.00075  0.00078  0.00075  0.00072  0.00072  0.00072   \n",
       "1   0.00065  0.00068  0.00069  0.00065  0.00065  0.00066  0.00066  0.00061   \n",
       "2   0.00056  0.00057  0.00063  0.00061  0.00059  0.00058  0.00056  0.00051   \n",
       "3   0.00047  0.00057  0.00051  0.00049  0.00048  0.00047  0.00046  0.00043   \n",
       "4   0.00045  0.00047  0.00043  0.00040  0.00039  0.00039  0.00038  0.00037   \n",
       "..      ...      ...      ...      ...      ...      ...      ...      ...   \n",
       "73  0.02368  0.06223  0.14189  0.22673  0.24340  0.26243  0.28269  0.30471   \n",
       "74  0.03362  0.09526  0.22673  0.24340  0.26243  0.28269  0.30471  0.32800   \n",
       "75  0.04979  0.14355  0.24340  0.26243  0.28269  0.30471  0.32800  0.35209   \n",
       "76  0.07304  0.16782  0.26243  0.28269  0.30471  0.32800  0.35209  0.37447   \n",
       "77  0.10381  0.26243  0.28269  0.30471  0.32800  0.35209  0.37447  0.39680   \n",
       "\n",
       "         8        9   ...       15       16       17       18       19  \\\n",
       "0   0.00061  0.00058  ...  0.00057  0.00065  0.00075  0.00088  0.00099   \n",
       "1   0.00053  0.00046  ...  0.00058  0.00067  0.00078  0.00090  0.00102   \n",
       "2   0.00046  0.00046  ...  0.00060  0.00070  0.00080  0.00092  0.00105   \n",
       "3   0.00040  0.00043  ...  0.00063  0.00072  0.00082  0.00095  0.00107   \n",
       "4   0.00036  0.00041  ...  0.00065  0.00074  0.00085  0.00098  0.00110   \n",
       "..      ...      ...  ...      ...      ...      ...      ...      ...   \n",
       "73  0.32800  0.35209  ...  0.47743  0.50332  0.53061  0.55939  0.58972   \n",
       "74  0.35209  0.37447  ...  0.50332  0.53061  0.55939  0.58972  0.62170   \n",
       "75  0.37447  0.39680  ...  0.53061  0.55939  0.58972  0.62170  0.65542   \n",
       "76  0.39680  0.41866  ...  0.55939  0.58972  0.62170  0.65542  0.69096   \n",
       "77  0.41866  0.43965  ...  0.58972  0.62170  0.65542  0.69096  0.72843   \n",
       "\n",
       "         20       21       22       23       24  \n",
       "0   0.00109  0.00115  0.00120  0.00127  0.00135  \n",
       "1   0.00112  0.00118  0.00125  0.00133  0.00141  \n",
       "2   0.00115  0.00121  0.00127  0.00135  0.00145  \n",
       "3   0.00115  0.00121  0.00128  0.00138  0.00149  \n",
       "4   0.00116  0.00122  0.00128  0.00140  0.00154  \n",
       "..      ...      ...      ...      ...      ...  \n",
       "73  0.62170  0.65542  0.69096  0.72843  0.76794  \n",
       "74  0.65542  0.69096  0.72843  0.76794  0.80958  \n",
       "75  0.69096  0.72843  0.76794  0.80958  0.85348  \n",
       "76  0.72843  0.76794  0.80958  0.85348  0.89977  \n",
       "77  0.76794  0.80958  0.85348  0.89977  0.94856  \n",
       "\n",
       "[78 rows x 25 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(select[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Generate modelpoints"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "random.seed(0)\n",
    "selection = random.choices(list(product(set(meta_df[\"Underwriting\"]), set(meta_df[\"Sex\"]), range(18, 50), range(1, 20))), k=100_000)\n",
    "selection\n",
    "mp_df = pd.DataFrame(selection, columns=['Underwriting', 'Sex', 'IssueAge', 'Duration'])\n",
    "\n",
    "mp_df.to_csv('modelpoints_tables/2017_loaded_CSO_modelpoints.csv', index=False)"
   ]
  }
 ],
 "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.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}