Canonical format for dataframe-like mortality table format?

[alecloudenback]

Canonical dataframe-like mortality table format

@MatthewCaseres I know we have had some discussion on the topic but before implementing I wanted to finalize the design in MortalityTables.jl[1]:

If a user wanted mortality rates in a dataframe-like format, what would that look like?

I have read your article and I'm not sure that the example given is tidy, but maybe it's more practical for day to-day dataframe usage?

What I mean:

The articles gives this example (call it format A):

 Row │ issue_age  duration  att_age  q_sel          q_ult    q
     │ Int64      Int64     Int64    Float64?       Float64  Float64
─────┼───────────────────────────────────────────────────────────────
   1 │        60        60      119  missing        0.5      0.5
   2 │        60        61      120  missing        0.5      0.5
   3 │        61         1       61        0.00152  0.00772  0.00152
   4 │        61         2       62        0.0031   0.0086   0.0031
   5 │        61         3       63        0.00525  0.00957  0.00525

But isn't the following "tidy" (call it format B)?

 Row │ issue_age  duration  variable  value
     │ Int64      Int64     String    Float64?
─────┼──────────────────────────────────────────────
   1 │        60        60  q_sel     missing
   2 │        60        61  q_sel     missing
   3 │        61         1  q_sel           0.00152
   4 │        61         2  q_sel           0.0031
   5 │        61         3  q_sel           0.00525
   6 │        60        60  q_ult           0.5
   7 │        60        61  q_ult           0.5
   8 │        61         1  q_ult           0.00772
   9 │        61         2  q_ult           0.0086
  10 │        61         3  q_ult           0.00957

The latter has the advantage(?) that if a rate doesn't exist then you might not generate a row for the data (though it's shown as missing above).

In your experience, have you found A to be more ergonomic? Or do you think that in retrospect users of a package would prefer B as (I think) it follows the true 'tidy' rules:

• Every column is a variable.
• Every row is an observation.
• Every cell is a single value.

My default preference would be to return B if someone did DataFrame(vbt2001) but I do more modeling and less dataframe wrangling so am curious what you (@MatthewCaseres) plus others might prefer.

---

Other Questions

Some other questions irrespective of A vs B decision:

• what other variables to include in the returned table?
  • E.g. do you also include the table name, description, etc.?
  • For reference, the current table metadata in MortalityTables.jl:

julia> vbt2001 = MortalityTables.table("2001 VBT Residual Standard Select and Ultimate - Male Nonsmoker, ANB")
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

• Should missing value be returned where a rate doesn't exist or should the row be omitted?

• Other suggestions of things people would want in a canonical dataframe-like format for mortality rates?

• Is it worth defining a function like DataFrame(vbt2001) or is it going to be fruitless because everyone expects/wants different data in a table?

---

Footnotes

Existing issue in MortalityTables.jl repo: JuliaActuary/MortalityTables.jl#111

1: MortalityTables.jl design stores rates based on indexing by issue/attained age: It uses an approach where rates are indexed by select/ultimate => issue_age => att_age which works really well for modeling as its both very ergonomic and performant. More on the design here.

[MatthewCaseres]

• format A vs format B
  • Both format A and format B are "tidy" in that each row is an issue age/duration combination, although A includes redundant information. The format in A is containing some artifacts that make clear how to do a calculation from the original tables.
  • I think format B is more sensible.
• what other information to be included
  • I think a canonical representation should be able to be represented by a CSV without any additional metadata values.
  • Maybe you would include other information in your implementation, but this is maybe particular to your implementation. I think that the SOA should distribute simple CSV files that are easy to upload to a database or work with programmatically. These CSV files should be free of metadata so that they can parse as a dataframe or load to a database. Metadata can live in another file that references the tables.
• Is it worth defining a function like DataFrame(vbt2001) or is it going to be fruitless because everyone expects/wants different data in a table?
  • I think VBT2001 is not granular enough. I think the tables currently are more granular than necessary for certain purposes and that this is a source of friction.
    • 2015 VBT is too broad. 2015 VBT Smoker Distinct Female Smoker ALB is too narrow. 2015 VBT Smoker Distinct ALB represents an actual partitioning of the population for use in a study. So there would be a single 2015 VBT Smoker Distinct ALB table, that can be joined to existing data using a simple JOIN in SQL.

I am sure there is a person at every company that has their own ways of dealing with these tables, so maybe there are people in your network you can ask around outside of this forum too. My practical experience is quite limited to be honest.

[alecloudenback]

Thanks for the input! I think I agree with most of what you said.

vbt2001 wasn't meant to represent the whole set of related tables, it's just my go to example for showing a single table, a la the package README.

If others have thoughts I'd be interested to hear. Like @MatthewCaseres I don't have a ton of experience here because it was never an issue for me when I was doing experience studies to grab the the rates I needed on the fly, but I know since people prefer to work end-to-end in dataframes and want to make that as easy as possible.