Picking a baby name with PostgreSQL® and FuzzyStrMatch
Choosing the name of your new child is really serious, so as a data professional you will naturally want to reach for PostgreSQL®. Find out more below.
Loading...
Choosing the name of your new child is really serious, so as a data professional you will naturally want to reach for PostgreSQL®. Find out more below.
Naming variables is one of the hardest problems in Computer Science, but when the variable is a new life, the challenge can rise quite significantly. Family names, personal preferences, celebrities, vetoes, word sound, it all goes into the decision mix! Even more, the possible pool of names is huge - and we all know that the traditional "let's browse for names in a book" approach doesn't scale anymore with the ever-increasing set of options.
Help is needed, and this blog post shows how PostgreSQL® can help us refine our search and get us closer to a final selection.
To start with, we need a dataset of valid names. This post uses this collection of baby names from the US Social Security administration (stored in a github repo)
Once you have created the account, you will be able to download the dataset in a names.csv file on your local machine.
You can run PostgreSQL locally or, if you prefer, create an instance using Aiven for PostgreSQL® with the Aiven CLI command, as shown below:
Loading code...
The command starts creation of a PostgreSQL service (--service-type pg) on the Google Europe West 3 cloud region with the minimal hobbyist plan, which is enough for our needs. The service takes a couple of minutes to start, and we can wait for it to be up with:
Loading code...
Once the service is in RUNNING state, we can connect to it with the following command that calls psql under the hood:
Loading code...
After connecting we need to create a table, since babynames-clean.csv doesn't contain any header:
Loading code...
Now it's time to copy the data:
Loading code...
Now the data is loaded in the names table and we can start the research of our imaginary baby's name.
Okay, time to choose a name! Is it going to be a boy or a girl? If you already have that information, it's going to cut out half of the options, since the query below shows that we have a more or less equal amount of male and female names.
Loading code...
The result shows slightly fewer boys' names than girls'.
Loading code...
What if we don't know the gender yet, and we have almost seven thousand names to pick from? Scanning all of them one by one is not an option. We need to apply some filters.
Do you have a long surname? It might be nice to save the poor kid from the stress of writing out a long first name as well. Let's filter for names with fewer than 6 characters.
Loading code...
We still have lots of results (2866), so some more pruning is needed ... I personally like names ending with vowels - can we add this constraint? Yes we can, with the help of regular expressions:
Loading code...
With the help of regular expressions we can take it even further, like specifying 1 l or 2 consecutive ls:
Loading code...
Regular expressions can only help so much, and we still have too many names in our list. We need some more ways to exclude options.
We all have names we never want to hear again. Maybe they remind us of someone we don't want to think about, or they're just taken by somebody else we hear about way too often. It's easy to avoid these names, but we can use PostgreSQL's FuzzyStrMatch to go a step further and eliminate anything even vaguely similar. We can install the extension with:
Loading code...
The extension brings us several functions. The first one, named Levenstein difference, helps us understand how many characters make two strings differ. Let's say, for example, we don't want anything that is written too similar to Allie.
Loading code...
Returns
Loading code...
We can see that the Levenshtein difference between:
Allie and Allie is 0 since they are the same stringAllie and Altie is 1 (the t)Allie and Alfie is 1 (the f)Allie and Elle is 2 (the v and a)We can remove all names with a Levenshtein difference of less than 3, since this helps us avoid names written too similarly to Allie.
Loading code...
We still have 251 rows, but the difference in the number of characters is just the beginning. We don't even want to hear a sound similar to Allie! Luckily the FuzzyStrMatch extension brings us another function, named soundex that provides the Soundex code of a string. Let's try with Allie itself:
Loading code...
The result is A400 which, by itself, is not very useful. But FuzzyStrMatch also offers difference which, despite the name, provides a Soundex similarity score between 0 (two strings sound different) to 4 (two strings sound the same). If we try with our dataset:
Loading code...
This shows that Clay, Milo, Riley, Wiley and Rolla sound a bit too close to our Allie.
Loading code...
We want to go for an entirely different sound, so let's remove anything with a Soundex score greater than 2.
Loading code...
We still have 115 rows, a few too many to choose from. The final step in the selection could be based on a name of which we like the sound, and therefore we'd like to explore similar-sounding ones. Let's take Marlo as an example. To retrieve names that sound similar we can either use the Soundex function again, or we could evolve our approach and use the Metaphone or double metaphone for higher accuracy.
Loading code...
The double metaphone function returns the MRL string. We can use that, together with the Levenshtein difference, to find all strings which vary from it by only one character.
Loading code...
Now we have to parse only 26 rows, and frankly, this is where the hard choices begin. Still, with the help of PostgreSQL, some regular expressions and the FuzzyStrMatch extension, we were able to prune a lot of our possible dataset.
Which name is best? The final selection is, of course, up to you.
String similarity is not an easy topic, but luckily the FuzzyStrMatch extension provides a few functions like Levenshtein, Soundex and Metaphone that can help us solve some common problems when comparing texts.
Some further resources:
avn service create demo-pg \
--service-type pg \
--cloud google-europe-west3 \
--plan hobbyistavn service wait demo-pgavn service cli demo-pg-- Create the gender enum
CREATE TYPE gender AS ENUM ('male', 'female');
-- Create the names table
CREATE TABLE names (
name TEXT PRIMARY KEY,
gender gender NOT NULL
);\copy names from 'names.csv' csvselect
gender,
count(*) number_of_names
from names
group by gender;gender | number_of_names
--------+-----------------
male | 3336
female | 4049select count(*) from names where length(name) < 6;select count(*) from names
where length(name) < 6
and name similar to '%(a|e|i|o|u|y)';select *
from names
where
length(name) < 6
and name similar to '%(l){1,2}%(a|e|i|o|u|y)'
limit 10;create extension fuzzystrmatch;select *,
levenshtein(name, 'Allie') diff
from names
where
length(name) < 6
and name similar to '%(l){1,2}%(a|e|i|o|u|y)'
order by diff
limit 10; name | gender | diff
-------+--------+------
Allie | female | 0
Altie | female | 1
Ollie | female | 1
Ellie | female | 1
Arlie | female | 1
Alfie | male | 1
Alvie | male | 1
Alcie | female | 1
Algie | male | 1
Elle | female | 2select count(*)
from names
where
length(name) < 6
and name similar to '%(l){1,2}%(a|e|i|o|u|y)'
and levenshtein(name, 'Allie') > 3;select soundex('Allie');select *,
soundex(name) sdx,
difference(name, 'Allie') sdx_score
from names
where
length(name) < 6
and name similar to '%(l){1,2}%(a|e|i|o|u|y)'
and levenshtein(name, 'Allie') > 3
order by sdx_score desc
limit 5; name | gender | sdx | sdx_score
-------+--------+------+-----------
Clay | boy | C400 | 3
Milo | boy | M400 | 3
Riley | boy | R400 | 3
Wiley | boy | W400 | 3
Rolla | boy | R400 | 3
(5 rows)select count(*)
from names
where
length(name) < 6
and name similar to '%(l){1,2}%(a|e|i|o|u|y)'
and levenshtein(name, 'Allie') > 3
and difference(name, 'Allie') <= 2;select dmetaphone('Marlo') dmet;select count(*)
from names
where
length(name) < 6
and name similar to '%(l){1,2}%(a|e|i|o|u|y)'
and levenshtein(name, 'Allie') > 3
and difference(name, 'Allie') <= 2
and levenshtein(dmetaphone(name), dmetaphone('Marlo')) <=1;