MortalityTables.jl stores the rates in a very efficient manner as a collection of vectors indexed by attained age.
First, we include the package, and then we'll pick a table, where all of the mort.soa.org
tables are mirrored into your MortalityTables.jl installation.
begin
using MortalityTables
vbt = MortalityTables.table("2001 VBT Residual Standard Select and Ultimate - Male Nonsmoker, ANB") #or any other table
end
MortalityTable (Insured Lives Mortality): Name: 2001 VBT Residual Standard Select and Ultimate - Male Nonsmoker, ANB Fields: (:select, :ultimate, :metadata) Provider: Society of Actuaries mort.SOA.org ID: 1118 mort.SOA.org link: https://mort.soa.org/ViewTable.aspx?&TableIdentity=1118 Description: 2001 Valuation Basic Table (VBT) Residual Standard Select and Ultimate Table - Male Nonsmoker. Basis: Age Nearest Birthday. Minimum Select Age: 0. Maximum Select Age: 99. Minimum Ultimate Age: 25. Maximum Ultimate Age: 120
To see how the data is represented, we can look at the the select data for a 55 year old and see the attained age and mortality rates:
vbt.select[55]
66-element OffsetArray(::Vector{Float64}, 55:120) with eltype Float64 with indices 55:120: 0.00139 0.00218 0.00288 0.00344 0.00403 0.00485 0.00599 ⋮ 0.75603 0.79988 0.84627 0.89536 0.94729 1.0
This is very efficient and convienent for modeling, but a lot of times you want the data matched up with policy data in a DataFrame.
using DataFrames
sample_size = 10_000
10000
sample_data = let
# generate fake data
df = DataFrame(
"sex" => rand(["Male","Female"],sample_size),
"smoke" => rand(["Smoker","Nonsmoker"],sample_size),
"issue_age" => rand(25:65,sample_size),
)
# a random offset of issue age is the current attained age
df.attained_age = df.issue_age .+ rand(1:10,sample_size)
df
end
sex | smoke | issue_age | attained_age | |
---|---|---|---|---|
1 | "Female" | "Smoker" | 31 | 39 |
2 | "Male" | "Smoker" | 36 | 46 |
3 | "Female" | "Smoker" | 36 | 46 |
4 | "Male" | "Smoker" | 38 | 46 |
5 | "Male" | "Smoker" | 61 | 65 |
6 | "Male" | "Nonsmoker" | 53 | 61 |
7 | "Male" | "Nonsmoker" | 50 | 60 |
8 | "Female" | "Smoker" | 46 | 51 |
9 | "Female" | "Smoker" | 51 | 52 |
10 | "Female" | "Nonsmoker" | 58 | 62 |
... | ||||
10000 | "Male" | "Nonsmoker" | 41 | 49 |
There are a lot of different possible combinations of parameters that you might want to use, such as rates that vary by sex, risk class, table set (VBT/CSO/etc), smoking status, relative risk, ALB/ANB, etc.
It's easy to define the parameters applicable to your assumption set. Here, we'll use a dictionary to define the relationship:
rate_map = Dict(
"Male" => Dict(
"Smoker" => MortalityTables.table("2001 VBT Residual Standard Select and Ultimate - Male Smoker, ANB"),
"Nonsmoker" => MortalityTables.table("2001 VBT Residual Standard Select and Ultimate - Male Nonsmoker, ANB"),
),
"Female" => Dict(
"Smoker" => MortalityTables.table("2001 VBT Residual Standard Select and Ultimate - Female Smoker, ANB"),
"Nonsmoker" => MortalityTables.table("2001 VBT Residual Standard Select and Ultimate - Female Nonsmoker, ANB"),
)
);
and then we'll define a function to look up the relevant rate. Note how the function matches the levels we defined for the assumption set dictionary above.
function rate_lookup(assumption_map,sex,smoke,issue_age,attained_age)
# pick the relevant table
table = assumption_map[sex][smoke]
# check if the select rate exists, otherwise look to the ulitmate table
if issue_age in eachindex(table.select)
table.select[issue_age][attained_age]
else
table.ultimate[attained_age]
end
end
rate_lookup (generic function with 1 method)
By mapping each row's data to the lookup function, we get a vector of rates for our data:
rates = map(eachrow(sample_data)) do row
rate_lookup(rate_map,row.sex,row.smoke,row.issue_age,row.attained_age)
end
10000-element Vector{Float64}: 0.00134 0.0041 0.00298 0.00398 0.01606 0.00686 0.00706 ⋮ 0.00104 0.00617 0.0132 0.00213 0.00233 0.00241
And finally, we can just add this to the dataframe:
sample_data.expectation = rates;
sample_data
sex | smoke | issue_age | attained_age | expectation | |
---|---|---|---|---|---|
1 | "Female" | "Smoker" | 31 | 39 | 0.00134 |
2 | "Male" | "Smoker" | 36 | 46 | 0.0041 |
3 | "Female" | "Smoker" | 36 | 46 | 0.00298 |
4 | "Male" | "Smoker" | 38 | 46 | 0.00398 |
5 | "Male" | "Smoker" | 61 | 65 | 0.01606 |
6 | "Male" | "Nonsmoker" | 53 | 61 | 0.00686 |
7 | "Male" | "Nonsmoker" | 50 | 60 | 0.00706 |
8 | "Female" | "Smoker" | 46 | 51 | 0.00424 |
9 | "Female" | "Smoker" | 51 | 52 | 0.00321 |
10 | "Female" | "Nonsmoker" | 58 | 62 | 0.00397 |
... | |||||
10000 | "Male" | "Nonsmoker" | 41 | 49 | 0.00241 |
begin
# add a table of contents to the page
using PlutoUI
TableOfContents()
end
Built with Julia 1.8.5 and
DataFrames 1.2.2To run this page locally, download this file and open it with Pluto.jl.