Hanukkah of Data 2022 - Puzzle 1
Tags: hanukkahofdata, visidata
Published: 2022-12-29 21:57:02

---
title: Hanukkah of Data 2022 - Puzzle 1
published: true
description: Talk through (and spoil!) the first puzzle from Hanukkah of Data 2022.
tags: hanukkahofdata, visidata
series: Hanukkah of Data 2022
---

_[Hanukkah of Data](https://hanukkah.bluebird.sh/) is a series of data-themed puzzles, where you solve puzzles to move your way through a holiday-themed story using a fictional dataset. See the [introductory post](https://www.visidata.org/blog/2022/hanukkah-of-data/) for a bit more detail, but the pitch in my head is "[Advent of Code](https://adventofcode.com/) meets [SQL Murder Mystery](https://mystery.knightlab.com/)". This post walks through my approach to the first puzzle._

- [Pre-work](#pre-work)
- [Thinking](#thinking)
- [Doing (VisiData)](#doing-visidata)
- [Refining / Reusing](#refining--reusing)
- [Solutions from Elsewhere](#solutions-from-elsewhere)

## Pre-work

Before actually solving any puzzles, there's a mini warm-up puzzle that lets you access the fictional data set. With that done, it's helpful to explore the data first just to get a feel for the shape/size/relationships. I'm not going to document that here, but in my case the gist was "open with [VisiData](https://www.visidata.org/) and poke around a bit."

**Warning:** From here on, this post unavoidably contains spoilers. If you'd rather do the puzzles on your own first, please close this browser tab and run away :).

## Thinking

The first daily puzzle involves finding a customer whose phone number matches their last name as typed on a phone's keypad:

> “They came in awhile ago and showed me their business card, and that’s what it said. Skilled Private Investigator. And their phone number was their last name spelled out. I didn’t know what that meant, but apparently before there were smartphones, people had to remember phone numbers or write them down. If you wanted a phone number that was easy-to-remember, you could get a number that spelled something using the letters printed on the phone buttons: like 2 has “ABC”, and 3 “DEF”, etc. And I guess this person had done that, so if you dialed the numbers corresponding to the letters in their name, it would call their phone number!

So...

- Define a phone letter --> number translation
- Find cases where the phone number shows up in the customer name

## Doing (VisiData)

Since this was the first puzzle and I already had the data set open in VisiData, I stayed there. I wrote a [little plugin](https://github.com/ajkerrigan/visidata-plugins/blob/main/plugins/vdrepl.py) a while back that lets me drop into a [ptipython](https://github.com/prompt-toolkit/ptpython#ipython-support) REPL that preserves VisiData context and keeps a VisiData-specific history. I find it super helpful for quick code explorations that I intend to throw away - if something is useful, I can adapt bits from the saved history into `.visidatarc` snippets or plugins later. So my actual work started there...

- From a REPL, define a translation table and save it to the global `vd` object so it's available in future vd expressions:

```python
phone_pad = {
    "abc": 2,
    "def": 3,
    "ghi": 4,
    "jkl": 5,
    "mno": 6,
    "pqrs": 7,
    "tuv": 8,
    "wxyz": 9,
}

vd.phone_lookup = str.maketrans(
    {letter: str(number) for letters, number in phone_pad.items() for letter in letters}
)
```

- Add a new column (`=` or `addcol-expr`) based on this expression:

```python
name.lower().replace(' ', '').translate(vd.phone_lookup)
```

- Rename the new column (`^` or `rename-col`) to a friendly name like `name_translated`

- Find rows where the phone number appears in the translated name, using `z|` or `select-expr` with this expression:

```python
phone.replace('-', '') in name_translated
```

- Open a new sheet with only the selected row, using `"` or `dup-selected`

## Refining / Reusing

While I find this "one step at a time" iterative approach very friendly for ad-hoc exploration, sometimes "just this once..." operations keep coming back. So how would I clean this up _just enough_ so that it was more easily reusable?

- First, turn those custom REPL bits into a custom VisiData command, and include it in my `.visidatarc` - either directly or as part of an isolated plugin file.
- Then, save my manual steps using VisiData's command log and adapt/replay it in the future.

There's sample code for that in [this gist](https://gist.github.com/ajkerrigan/bd9cb288d94ab3c080465691f9b0fd52). With the Python bits included or imported into `.visidatarc` and a `noahs-customers.csv` file in the current directory, `vd -p puzzle1.vdj` finds the solution.

## Solutions from Elsewhere

I love [Dian Fey's approach](https://di.nmfay.com/hanukkah) to this puzzle using Postgres!

Dolmen's solution [here](https://github.com/dolmen/Hanukkah-of-Data/blob/main/5783/1/1.sql) leaned into the SQLite approach I snuck up on and rejected in favor of VisiData. It's good to see it up in GitHub for the sake of comparison - pure SQLite is a feature!