Using MortaltiyTables.jl with DataFrames

MortalityTables.jl standard representation

MortalityTables.jl stores the rates in a very efficient manner as a collection of vectors indexed by attained age.

using MortalityTables

name = "2001 VBT Residual Standard Select and Ultimate - Male Nonsmoker, ANB"
vbt = MortalityTables.table(name)

1: Or any other mort.soa.org table

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

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.

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.00736
 0.00879
 0.01059
 ⋮
 0.63838
 0.67541
 0.71458
 0.75603
 0.79988
 0.84627
 0.89536
 0.94729
 1.0

This is very efficient and convenient for modeling, but a lot of times you want the data matched up with policy data in a DataFrame.

Getting data into a dataframe

Generate sample data

using DataFrames

sample_size = 10_000

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

10000×4 DataFrame

9975 rows omitted

Row	sex	smoke	issue_age	attained_age
	String	String	Int64	Int64
1	Female	Nonsmoker	59	65
2	Female	Nonsmoker	54	62
3	Male	Smoker	56	62
4	Male	Smoker	46	49
5	Female	Nonsmoker	55	62
6	Male	Nonsmoker	34	41
7	Male	Smoker	55	62
8	Male	Smoker	25	35
9	Male	Smoker	32	39
10	Male	Nonsmoker	29	36
11	Male	Nonsmoker	51	60
12	Female	Smoker	36	41
13	Female	Smoker	59	60
⋮	⋮	⋮	⋮	⋮
9989	Female	Nonsmoker	36	43
9990	Male	Smoker	37	46
9991	Male	Nonsmoker	46	51
9992	Male	Smoker	52	57
9993	Female	Nonsmoker	36	45
9994	Female	Nonsmoker	53	54
9995	Female	Smoker	36	42
9996	Female	Nonsmoker	51	52
9997	Male	Nonsmoker	34	39
9998	Female	Nonsmoker	29	36
9999	Female	Nonsmoker	36	46
10000	Male	Smoker	28	36

Define the table set you want to use

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 ultimate 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)

Lining up with dataframe

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.00595
 0.00555
 0.01404
 0.00328
 0.00506
 0.00114
 0.01508
 0.00159
 0.00205
 0.00086
 ⋮
 0.00785
 0.0013
 0.00163
 0.00159
 0.00136
 0.00094
 0.00061
 0.00149
 0.00166

And finally, we can just add this to the dataframe:

sample_data.expectation = rates

sample_data

10000×5 DataFrame

9975 rows omitted

Row	sex	smoke	issue_age	attained_age	expectation
	String	String	Int64	Int64	Float64
1	Female	Nonsmoker	59	65	0.00595
2	Female	Nonsmoker	54	62	0.00555
3	Male	Smoker	56	62	0.01404
4	Male	Smoker	46	49	0.00328
5	Female	Nonsmoker	55	62	0.00506
6	Male	Nonsmoker	34	41	0.00114
7	Male	Smoker	55	62	0.01508
8	Male	Smoker	25	35	0.00159
9	Male	Smoker	32	39	0.00205
10	Male	Nonsmoker	29	36	0.00086
11	Male	Nonsmoker	51	60	0.0066
12	Female	Smoker	36	41	0.00132
13	Female	Smoker	59	60	0.00561
⋮	⋮	⋮	⋮	⋮	⋮
9989	Female	Nonsmoker	36	43	0.00096
9990	Male	Smoker	37	46	0.00403
9991	Male	Nonsmoker	46	51	0.00231
9992	Male	Smoker	52	57	0.00785
9993	Female	Nonsmoker	36	45	0.0013
9994	Female	Nonsmoker	53	54	0.00163
9995	Female	Smoker	36	42	0.00159
9996	Female	Nonsmoker	51	52	0.00136
9997	Male	Nonsmoker	34	39	0.00094
9998	Female	Nonsmoker	29	36	0.00061
9999	Female	Nonsmoker	36	46	0.00149
10000	Male	Smoker	28	36	0.00166