[Puzzles] Solve, Learn, Repeat: Family Trees

[ITWhisperer]

This puzzle (first published here is based on finding grandparents and grandchildren (inspired by a question in Answers about identifying grandparent processes from Windows logs).

Using partially complete i.e. incomplete genealogical records, combine parent/child relationships to establish grandparent/grandchild relationships. The genealogical records are in the form of a comma separated values (csv) file. Dates and ages have been simplified so that only the year is present (consider everybody's birthday is January 1^st, just like thoroughbred horses in the northern hemisphere!). Unfortunately, the records have been jumbled up and are a mixture of births, marriages, and deaths.

This is an SPL challenge to find which grandparent has the most surviving grandchildren. Note that the grandparent may have died, but their grandchildren have not (at least as far as the available records are concerned)!

Disclaimer: This is a work of fiction. The names used are the products of the author’s imagination. Any resemblance to actual persons, living or dead is purely coincidental.

This article contains spoilers!

In fact, the whole article is a spoiler as it contains solutions to the puzzle. If you are trying to solve the puzzle yourself and just want some pointers to get you started, stop reading when you have enough, and return if you get stuck again, or just want to compare your solution to mine!

Separating the record types

We can start by taking the data (copied from the link given above) and splitting it into separate events, one for each record.

| makeresults
| fields - _time
| eval _raw="1944,Andrew Noah Oliver Mantel,49,M
1994,Paul Mantel,24,Maeve Lincoln,23
South,George,M,1995,George Theodore South,26,Olivia South,25
2019,Alfred Austin Mantel,70,M
...
2023,Hazel Sofia Farrow,47,F"
| multikv noheader=t
| fields _raw
| fields - _mkv_child _mkv_parent

As well as breaking a chunk of data into separate line-events, the multikv command will also attempt to extract key-value pairs from each line, but we do not need them in this case, so we just keep the _raw field.

For each record type, we want to tag them so that they can be easily separated and combined later. We start by tagging the whole set as "Trunk".

| eval _branch="Trunk"

Now, we process all the events, creating separate "branches" for each record type. Starting with the birth certificates, which we know (from the original puzzle description) contain the following fields: Surname, Given name(s), Sex, Year of Birth, Father, Father's Age, Mother, and Mother's Age

| appendpipe
    [
    | rex "^(?<Surname>[^,]+),(?<Given>[^,]+),(?<Sex>[^,]+),(?<Birth>[^,]+),(?<Father>[^,]+),(?<Father_Age>[^,]+),(?<Mother>[^,]+),(?<Mother_Age>[^,]+)$"
    | where isnotnull(Surname)
    | eval _branch="Birth Certificates"
    ]

Records which do mot match the regular expression will have null values for the fields extracted so we can filter for just those records which match.

We can do a similar thing for the marriage certificates which have the following fields: Year of Marriage, Husband's Name, Husband's Age, Wife's Name (prior to marriage, wives take their husband's surname after marriage), and Wife's Age

| appendpipe
    [
    | where _branch="Trunk"
    | rex "^(?<Marriage>[^,]+),(?<Husband>[^,]+),(?<Husband_Age>[^,]+),(?<Wife>[^,]+),(?<Wife_Age>[^,]+)$"
    | where isnotnull(Marriage)
    | eval _branch="Marriage Certificates"
    ]

Note that we filter the event pipe so that we are just (re-)processing the initial set of records.

Finally, the death certificates which have the following fields: Year of Death, Name, Age, and Sex

| appendpipe
    [
    | where _branch="Trunk"
    | rex "^(?<Death>[^,]+),(?<Name>[^,]+),(?<Age>[^,]+),(?<Sex>[^,]+)$"
    | where isnotnull(Death)
    | eval _branch="Death Certificates"
    ]

Now we can filter out the "Trunk" i.e. keep the other "branches".

| where _branch="Birth Certificates" or _branch="Marriage Certificates" or _branch="Death Certificates"
| table Birth Surname Given Sex Father Father_Age Mother Mother_Age Marriage Husband Husband_Age Wife Wife_Age Death Name Age _branch

Building correlations

In order to correctly establish relationships between people, we need to be sure who is who, for example, is the "Joe Bloggs" who married at age 20, the same "Joe Bloggs" whose son was born when "Joe" was 23? Start by using the ages in the marriage and birth records to infer the years of birth for the parents.

| eval Father_DoB=Birth-Father_Age
| eval Mother_DoB=Birth-Mother_Age
| eval Husband_DoB=Marriage-Husband_Age
| eval Wife_DoB=Marriage-Wife_Age

Every time a woman gets married (in this scenario), she takes on her husband's surname (as mentioned in the puzzle description). This means that if she subsequently marries, she will be recorded with her married name not her birth (nee/maiden) name. It turns out that only one woman has married more than once (but this still needs to be correlated), and that she has only remarried once.

In order to determine the maiden names, we need to establish the married names so that we can look for other marriage records using those names.

| eval Married_Name=mvjoin(mvappend(mvindex(split(Wife, " "), 0, -2), mvindex(split(Husband, " "), -1)), " ")

(Note that the names used in the puzzle assume that the wife takes on her (new) husband's surname in place of her surname, not in addition to her surname. While it might be possible to deal with this scenario, it is unnecessary for this version of the puzzle.)

Note the use of negative indexes on the mvindex() function to index/count from the end of the multi-value fields (created by the split() functions).

Now we can reprocess the marriage certificate records to establish the maiden names for the wives.

| appendpipe
    [
    | where _branch="Marriage Certificates"
    | eval Wife_Maiden_Name=Wife
    | eval Wife=Married_Name
    | table Wife_DoB Wife Wife_Maiden_Name
    | eval _branch="Maiden_Names"
    ]

Using the eventstats command, we can (self-)join the wife's maiden name using her (pre-marriage) name and date of birth (previously calculated from her age at the time of the marriage).

| eventstats values(Wife_Maiden_Name) as Wife_Maiden_Name by Wife Wife_DoB

(If there had been more marriages for any woman, a similar process could have been followed until all marriages had been resolved.)

Now, the wife's maiden name is either the newly joined maiden name (if there had been a previous marriage), or the name she was married under.

| eval Wife_Maiden_Name=coalesce(Wife_Maiden_Name, Wife)

We can use eventstats again to determine her final name.

| eventstats last(Married_Name) as Wife_Final_Name by Wife_Maiden_Name Wife_DoB

We can now drop the "Maiden_Names" branch as we no longer need it.

| where _branch!="Maiden_Names"

Now we can copy the mother's maiden name to the birth certificate records by using the mother's name and the wife's married name and their corresponding dates of birth.

| eval Married_Name=coalesce(Married_Name, Mother)
| eval Wife_DoB=coalesce(Wife_DoB, Mother_DoB)
| eventstats values(Wife_Maiden_Name) as Mother_Maiden_Name by Married_Name Wife_DoB

Tidy up the fields.

| table Birth Surname Given Sex Father Father_DoB Mother Mother_Maiden_Name Mother_DoB Marriage Husband Husband_DoB Wife Wife_DoB Wife_Maiden_Name Wife_Final_Name Death Name Age _branch

We now add the maiden names to the female death certificate records by (self-)joining through the wife's final name..

| eval Wife_DoB=coalesce(Wife_DoB, if(Sex="F", Death-Age, null()))
| eval Wife_Final_Name=if(Sex="F", Name, Wife_Final_Name)
| eventstats values(Wife_Maiden_Name) as Wife_Maiden_Name by Wife_Final_Name Wife_DoB

Individualising records with multiple people

Marriage certificate records represent two people and birth certificate records represent three people. It is time to give everyone their own records.

We will start by identifying both parties in the marriages.

| appendpipe
    [
    | where _branch="Marriage Certificates"
    | eval Sex=coalesce(Sex, split("MF", ""))
    | mvexpand Sex
    | eval Birth_Name=if(Sex="M", Husband, Wife_Maiden_Name)
    | eval Birth=if(Sex="M", Husband_DoB, Wife_DoB)
    | table Birth Birth_Name Sex
    | eval _branch="Spouses"
    ]

By creating yet another "branch" with appendpipe, we can filter on the branch we want to process ("Marriage Certificates") without having messy checks as to which "branch" the event if part of on every eval.

Having created the "Spouses" branch, we can now drop the "Marriage Certificates" branch.

| where _branch!="Marriage Certificates"

Now we can do a similar thing with the parents mentioned in the birth certificate records.

| appendpipe
    [
    | where _branch="Birth Certificates"
    | eval Sex=split("MF","")
    | mvexpand Sex
    | eval Birth_Name=if(Sex="M", Father, Mother_Maiden_Name)
    | eval Birth=if(Sex="M", Father_DoB, Mother_DoB)
    | stats count as _count by Birth Birth_Name Sex
    | eval _branch="Parents"
    ]

This time we do not drop the "Birth Certificates" branch as these records are still required.

Coalesce the names from the various branches and calculate the date of birth for the death certificate records using the age and date of death.

| eval Name=coalesce(Birth_Name, Name, Given." ".Surname)
| eval Birth=coalesce(Birth, Death-Age)

We can now gather all the information we have on each person (using the birth name and date of birth).

| stats values(Sex) as Sex values(Death) as Death values(Father) as Father values(Father_DoB) as Father_DoB values(Mother_Maiden_Name) as Mother values(Mother_DoB) as Mother_DoB by Name Birth

These records represent all the people we know about from all the records, and, where known, who their parents are.

| eval _branch="People"

Now we are ready to start solving the puzzle!

Find grandparents

In the same way that we found previous pre-marriage names, we can find parent's parents i.e. grandparents. Starting with paternal grandparents, find the parents of male individuals, and (self-)join them with other individual's fathers.

| appendpipe
    [
    | where Sex="M"
    | eval Paternal_Grandfather=Father
    | eval Paternal_Grandfather_DoB=Father_DoB
    | eval Paternal_Grandmother=Mother
    | eval Paternal_Grandmother_DoB=Mother_DoB
    | eval Father=Name
    | eval Father_DoB=Birth
    | eval Father_DoD=Death
    | table Father Father_DoB Father_DoD Paternal_Grandfather Paternal_Grandfather_DoB Paternal_Grandmother Paternal_Grandmother_DoB
    ]
| eventstats values(Father_DoD) as Father_DoD values(Paternal_Grandfather) as Paternal_Grandfather values(Paternal_Grandfather_DoB) as Paternal_Grandfather_DoB values(Paternal_Grandmother) as Paternal_Grandmother values(Paternal_Grandmother_DoB) as Paternal_Grandmother_DoB by Father Father_DoB
| where _branch="People"

Repeat for the maternal grandparents (I will leave this for you to do).

You can now list the grandchildren for each grandparent and find the one who has the most surviving grandchildren.

Solution hint

Rather than giving you the full search that I used, so that you can verify your solution, I will tell you that the highest number of grandchildren in the puzzle answer is a prime number, (as is the number of surviving grandchildren), and the number of vowels in their names is 81.

Summary

Using the appendpipe command to reprocess the event pipeline can set up self-joins which can be resolved with the eventstats command.

Have questions or thoughts? Comment on this article or in Slack #puzzles channel. Whichever you prefer.