DEV Community: AJ Kerrigan

Curses, space heists and found family - My freefall into Jessie Kwak fandom

AJ Kerrigan — Mon, 24 Jun 2024 17:59:27 +0000

My Jessie Kwak Jumpstart
But Wait, There's More!
...And More to Come (Kickstarter)!

TL;DR: I've fallen in love with Jessie Kwak's writing - character-rich speculative fiction stories, many of which take place in the same expanding world. I gush in the hope that there are other folks who will love her work but haven't come across it yet.

My Jessie Kwak Jumpstart

A while back I read the collection Dispatches from Anarres - stories from a bunch of Portland authors in tribute to Ursula K. Le Guin. As with most collections, a few stories stuck with me long after I finished. Atop that list was Black as Thread, from an author I had never read before. I love it when that happens! Jessie Kwak's teaser for the story on her site is:

Sewing curses into the boots of foreign occupiers may be helping win the war, but what does it do to the soul?

Coming as part of a Le Guin tribute, the story immediately reminded me of the Carrier Bag Theory of Fiction essay. Because sure there's that word "war", but the story isn't about battles and commanders and heroes. As the Carrier Bag essay notes:

That is why I like novels: instead of heroes they have people in them.

Something about the world, the characters, the writing style... it hooked me hard, and I had to find more from the author who created it.

But Wait, There's More!

It's a beautiful thing - finding an author that you can connect with immediately, and learning that there are several books already out in the world waiting for you. Jessie provides a couple recommended entry points for her fiction, which I found helpful. I latched onto the Nanshe Chronicles because... how could I not:

Are you looking for fun spaceship-based adventures that read like an episode of your favorite TV show — complete with heists, trick flying, witty banter, and a crew of wary newcomers slowly transforming into found family?

(Thank Firefly, Cowboy Bebop, and Leverage with a splash of Indiana Jones.)

Leverage doesn't mean anything to me, but the rest combine nicely and feel spot-on in retrospect. I found a lot of Expanse vibes in the series too, including one particular trope that surfaced in both series (for what it's worth, I vastly prefer how it was handled in the Nanshe Chronicles).

There's a certain optimism/pessimism slider that's tough to nail, particularly in speculative fiction. Some feels willfully pessimistic and depressing enough that it's a slog to read. Go too far the other way, and things are so happy huggy that I can't suspend disbelief long enough to enjoy a book. The Nanshe Chronicles hits a sweet spot - the world isn't perfect, but I have people to love and root for.

...And More to Come (Kickstarter)!

I've read the Bulari Saga and all that's available from the Nanshe Chronicles so far (the two series take place in the same world). The thriller From Earth and Bone had a totally different vibe, but worked for me also.

And today there's a Kickstarter launching for a collection that will dig deeper into the world of the Bulari Saga. It's intended as a love letter to fans, particularly those who have supported the series since it launched in 2019. It wasn't on my radar then, but I'm so glad it is now. And I hope this post reaches a few eyes that will love these stories as much as I do.

Cheers folks. One more thought from that Carrier Bag essay to send you off on your travels:

Science fiction properly conceived, like all serious fiction, however funny, is a way of trying to describe what is in fact going on, what people actually do and feel, how people relate to everything else in this vast stack, this belly of the universe, this womb of things to be and tomb of things that were, this unending story.

Git: Pull and Fetch, When and Why?

AJ Kerrigan — Sun, 31 Mar 2024 04:10:38 +0000

The Question
Simple Case
More Complex Case
Is This Working?

The Question

I saw a poll pop up in my Mastodon feed:

And my brain snagged on it. Because it reminded me that I developed some preferences, built them into aliases, and then shoved them comfortably below the level of conscious thought. And hey, that hasn't caused a problem for me so far! It's still good to re-evaluate these things from time to time though. So when I need to grab some upstream changes with git, what do I do?

Note: I don't intend this as expert guidance - I am neither expert nor guide.

Simple Case

Situation: My active local branch is behind its upstream counterpart.

I...

Run git f (an alias which in my head means "freshen up").
Under the covers, this runs a git pull and I'm up to date. Yay!

More Complex Case

Situation: I'm working on a branch and want to pull updates in from another branch (typically main).

I...

Run git f main.
Under the covers, this runs a git fetch command that updates my local main branch from its upstream remote, but leaves my current branch alone.
Then depending on the situation, I might:
- git merge main (perhaps I'm making minor maintainer edits to a community pull request)
- git rebase main (it's my own junk and I just want to be updated)
- git rebase -i main (I've got some fine-tuning to do)

Is This Working?

I haven't really interrogated my instincts in a while, so does this flow still work for me?

Yeah, I think so. It tucks most of my "get me up to date" behavior behind a single alias, and deliberately leaves me to make a judgement call in cases where the next step is less certain.

For the record, here's the full picture of what git f does:



#!/usr/bin/env bash
set -euxo pipefail

if [[ -z "${1:-}" ]]; then
    git pull -v
else
    git rev-parse --abbrev-ref "${1}"@{upstream} | \
        awk '{split ($0,ref,"/"); system("git fetch -v --prune " ref[1] " " ref[2] ":" ref[2]); }'
fi

That else clause feels a bit weird. But it means when I run git f main:

That git rev-parse bit finds whichever remote is upstream of main. Let's say that's called origin.
Something like git fetch origin main:main updates my local main branch, but leaves my active branch alone.
- --prune does a bit of tidying while we're at it, removing local branches that were tracking remote branches that no longer exist

There's undoubtedly a better/smarter way to handle this workflow, express it in bash, or both. Suggestions welcome in the comments!

Keep your AWS CLI config fresh with Cog

AJ Kerrigan — Thu, 28 Mar 2024 18:34:04 +0000

The Problem
An Opinionated Solution
How I Use It
- Install Cog and aws-sso-config-builder
- Update the AWS CLI config file
- Refresh my config file
Should you use this?

The Problem

I have...

Access to an ever-shifting set of AWS accounts and permissions sets, courtesy of IAM Identity Center (formerly AWS SSO).
Undying fondness for aws-vault to securely cache my session credentials.

I want...

A set of AWS CLI profiles automatically defined in ~/.aws/config, based on all of the permission sets available to me through IAM Identity Center.
Those profiles to "just work", in that:
- They transparently refresh short-lived credentials or launch a browser-based SSO login window as needed.
- They don't trip up external utilities that read an AWS CLI configuration.
The ability to add profiles manually for special cases, without interfering with the autogenerated profiles.

An Opinionated Solution

For a long time, I solved this problem with custom scripts. And that was fine! My workflow felt so specific to my tastes that it wasn't worth packaging.

But then a couple things happened:

I saw some of Cog's use cases and realized my custom scripts were just a low budget reimplementation of what Cog already did well.
I was exploring different Python packaging tools, but none of my previous work or personal experience gave me a reason to use Hatch.

So I decided to convert my custom nonsense into a Hatch-based package that would play nicely with Cog:

https://pypi.org/project/aws-sso-config-builder/

Bird.
Bird.
Stone.

How I Use It

The aws-sso-config-builder tool doesn't require this workflow - the usage section of the README includes alternatives. But this is how I work:

Install Cog and aws-sso-config-builder

Use pipx to install Cog and my aws-sso-config-builder tool in the same environment:

pipx install --verbose cogapp
pipx inject --verbose cogapp aws-sso-config-builder

Update the AWS CLI config file

Add a block like this to the ~/.aws/config file:

# [[[cog
# import cog
# from aws_sso_config_builder.gen_config import generate_config_blocks
#
# cog.outl(generate_config_blocks(
#     sso_directories=["home", "work"],
#     profile_template="""
#         [profile {profile_name}-sso]
#         sso_session = {sso_session}
#         sso_account_id = {account_id}
#         sso_role_name = {role_name}
#         output = json
#         region = us-east-2
#
#         [profile {profile_name}]
#         credential_process = {aws_vault_path} exec --json {profile_name}-sso
#         output = json
#         region = us-east-2
#     """,
#     regex_replacements={
#         "Production": "prod",
#         "Sandbox": "sbx"
#     },
#     aws_vault_path="/home/aj/go/bin/aws-vault",
# ))
# ]]]
# [[[end]]]

Which defines:

IAM Identity Center directory names / organizations that I have access to
The skeleton of a config block that should be generated for each account/permission set
A few regular expressions to customize / normalize profile names
The path to my aws-vault binary

Refresh my config file

Now I have:

Cog available
aws-sso-config-builder providing a mechanism for looking up permission sets
A template block in my config file

So when I run:

cog -r ~/.aws/config

It replaces everything between ]]] and [[[end]]] with one rendered profile_template block per permission set:

...template block from above...
]]]
...many other profiles...

[profile sbx-AJ-AWSReadOnlyAccess-sso]
sso_session = stacklet
sso_account_id = 111111111111
sso_role_name = AWSReadOnlyAccess
output = json
region = us-east-2

[profile sbx-AJ-AWSReadOnlyAccess]
credential_process = /home/aj/go/bin/aws-vault exec --json sbx-AJ-AWSReadOnlyAccess-sso
output = json
region = us-east-2

[profile sbx-AJ-AWSAdministratorAccess-sso]
sso_session = stacklet
sso_account_id = 111111111111
sso_role_name = AWSAdministratorAccess
output = json
region = us-east-2

[profile sbx-AJ-AWSAdministratorAccess]
credential_process = /home/aj/go/bin/aws-vault exec --json sbx-AJ-AWSAdministratorAccess-sso
output = json
region = us-east-2
[[[end]]]

And from there, it's easy for me to switch among any CLI profiles I have. The profile names are verbose enough that it's very clear which account/permissions I have, and through some shell helpers it's still friendly to bounce around:

❯ asp sbx-AJ-AWSReadOnlyAccess

~ via 🐍v3.12.2 (cogapp) on ☁️  sbx-AJ-AWSReadOnlyAccess (us-east-2)
❯

Should you use this?

Probably not. But if you got this far in the post... maybe? Thanks for reading in any case, you deserve some kanelbullar. Boy those things are delicious.

Stuff I Learned during Hanukkah of Data 2023

AJ Kerrigan — Mon, 18 Dec 2023 20:57:27 +0000

Background on Hanukkah of Data, motivation behind this post

Hanukkah of Data is a series of data-themed puzzles, where you solve puzzles to move your way through a holiday-themed story using a fictional dataset. I think of it as "Advent of Code meets SQL Murder Mystery".

Last year I worked through the challenges using VisiData, Datasette, and Pandas. I walked through my thought process and solutions in a series of posts.

This year I decided to commit to a set of tools on day 1 (Polars and Jupyter) and use them for the whole challenge. It seemed silly to do a whole new meandering walkthrough, so instead I'll highlight a few things that stuck out after finishing the challenge and sitting on it for a few days. Here we go!

Here are a few things that stuck with me after using Polars and Jupyter for Hanukkah of Data this year:

Parsing Dates at Read Time Can Be Fiddly
- Helping SQLite Help Me
- Falling Back to Explicit Post-Read Parsing
nbdime Rocks
Polars LazyFrames are Neat

Parsing Dates at Read Time Can Be Fiddly

While I had no trouble parsing dates from an existing Polars DataFrame, I hit two bumps trying to parse dates from strings while reading a SQLite file with read_database():

Helping SQLite Help Me

First, I got an error that looked like this:

ComputeError: could not append value: "2017-01-31 00:11:08"
of type: str to the builder; make sure that all rows have
the same schema or consider increasing `infer_schema_length`

it might also be that a value overflows the data-type's
capacity

That turned out to be related to pola-rs/polars#11912, and this linked comment provided a deceptively simple solution - use PARSE_DECLTYPES when creating the connection:

con = sqlite3.connect(
-    "5784/noahs.sqlite"
+    "5784/noahs.sqlite", detect_types=sqlite3.PARSE_DECLTYPES
)

Falling Back to Explicit Post-Read Parsing

With that fixed, I still hit overflow errors on the birthdate column. As far as I've been able to tell from here and elsewhere, this is because birthdate was a simple YYYY-MM-DD format string rather than a full ISO format date? In any case, I couldn't find a way to parse that at read time.

Note: I expect this is a failure in my brain and fingers, rather than Polars.

So I was able to parse a couple datetimes at read time, and leaned on .str.strptime() to parse birthdates after reading:

df = pl.read_database(
    connection=con,
    query="""
        ...
    """,
    schema_overrides={
        "ordered": pl.Datetime,
        "shipped": pl.Datetime,
    },
).with_columns(birthdate=pl.col("birthdate").str.strptime(pl.Date))

nbdime Rocks

I remember hearing about nbdime and thinking it sounded useful, but I've never really needed it since I rarely use Jupyter in the first place. But then I made some changes to my Hanukkah of Data 2023 notebook to work with the follow-up "speed run" challenge (a new dataset and slightly tweaked clues), and the native Git diff was too noisy to be useful. nbdime came to the rescue! Here are the changes I had to make for days 2 and 3 during the speed run:

(Full-size image)

Text representation of this diff

nbdiff 2023/Hanukkah of Data 2023.ipynb (656616004639c75824de43284ff34bbdc9d89f37) 2023/Hanukkah of Data 2023.ipynb
--- 2023/Hanukkah of Data 2023.ipynb (656616004639c75824de43284ff34bbdc9d89f37)  (no timestamp)
+++ 2023/Hanukkah of Data 2023.ipynb  2023-12-18 11:43:03.005108
## modified /cells/12/source:
@@ -1,7 +1,12 @@
-day2 = df.filter(
-    (pl.col("name").str.contains(r"J.*P"))
-    & (pl.col("desc").str.contains(r"(?i)(coffee|bagel)"))
-    & (pl.col("ordered").str.strptime(pl.Datetime).dt.year() == 2017)
+day2 = (
+    df.filter(
+        (pl.col("name").str.contains(r"D.*?S"))
+        & (pl.col("desc").str.contains(r"(?i)(coffee|bagel)"))
+        & (pl.col("ordered").dt.year() == 2017)
+    )
+    .group_by("phone", "citystatezip")
+    .agg(pl.col("orderid").count().alias("ordercount"))
+    .top_k(1, by="ordercount")
 )

-day2.select("phone").unique().item()
+day2.select("phone")

## modified /cells/14/source:
@@ -1,13 +1,8 @@
 neighborhood = day2.select("citystatezip").unique().item()

 day3 = df.filter(
-    (
-        pl.col("birthdate")
-        .str.strptime(pl.Datetime)
-        .dt.ordinal_day()
-        .is_between(173, 203)
-    )
-    & (pl.col("birthdate").str.strptime(pl.Datetime).dt.year() % 12 == 7)
+    (pl.col("birthdate").dt.ordinal_day().is_between(266, 296))
+    & (pl.col("birthdate").dt.year() % 12 == 11)
     & (pl.col("citystatezip") == neighborhood)
 )

Polars LazyFrames are Neat

For this eight-day challenge, I knew I would be reusing the same core dataset and just slicing it different ways. Reading the full data set into a starter DataFrame seemed fine for that use case, but I still wanted to fiddle with the Polars Lazy API a bit.

On Day 4, for example, I needed to rank customers by the number of bakery items they bought before 5am. That involves a few steps:

Filter: I only care about bakery orders (an order sku that starts with BKY) in the 0400-0459 time range.
Aggregate: Count the filtered orders by customer
Sort: Show the top customers by order count

Which I can do with an eager/non-lazy call like this:

day4 = (
    df.filter(
        (pl.col("sku").str.starts_with("BKY")) & (pl.col("ordered").dt.hour() == 4)
    )
    .group_by(["name", "phone"])
    .agg(pl.col("orderid").count().alias("ordercount"))
    .top_k(5, by="ordercount")
)

day4.select(["name", "phone", "ordercount"])

or this very similar looking lazified version, which only requires two changes (calling df.lazy() up front to queue operations, and collect() later to combine/perform them):

day4_lazy = (
    df.lazy()
    .filter((pl.col("sku").str.starts_with("BKY")) & (pl.col("ordered").dt.hour() == 4))
    .group_by(["name", "phone"])
    .agg(pl.col("orderid").count().alias("ordercount"))
    .top_k(5, by="ordercount")
)

day4_lazy.collect().select(["name", "phone", "ordercount"])

The neat part is that before executing those queued operations, the explain() and show_graph() methods can offer peeks at the query plan. And it lets you choose whether or not that plan display includes LazyFrame optimizations. Check it out, here's the unoptimized version of my day 4 operations:

Text version

SLICE[offset: 0, len: 5]
  SORT BY [col("ordercount")]
    AGGREGATE
        [col("orderid").count().alias("ordercount")] BY [col("name"), col("phone")] FROM
      FILTER [(col("sku").str.starts_with([Utf8(BKY)])) & ([(col("ordered").dt.hour()) == (4)])] FROM

      DF ["customerid", "name", "address", "citystatezip"]
 PROJECT */18 COLUMNS
 SELECTION: "None"

And here's the optimized version:

Text version

SORT BY [col("ordercount")]
  AGGREGATE
    [col("orderid").count().alias("ordercount")] BY [col("name"), col("phone")] FROM
    DF ["customerid", "name", "address", "citystatezip"]
 PROJECT 5/18 COLUMNS
 SELECTION: "[(col(\"sku\").str.starts_with([Utf8(BKY)])) & ([(col(\"ordered\").dt.hour()) == (4)])]"

It's pretty cool to see what Polars does under the hood to combine operations and make them more efficient.

I didn't get the full benefit of all available lazy optimizations because of the data I had and how I was using it. It's still very neat stuff to be aware of though!

Kitty Image Quality in the Terminal... Without Giving Up Tmux

AJ Kerrigan — Thu, 23 Mar 2023 03:04:55 +0000

Motivation
Is this actually a problem?
Time for a workaround
- Kitty remote control
- Kitty tabs
- Timg
- Duct taping it together
- Video demo
Better options

Motivation

This is a bit cheeky and simplistic, but also reasonably accurate:

Kitty has a graphics protocol that allows a terminal to render high-resolution images
A number of tools support kitty's graphics protocol
Tmux does not
...but I like tmux

Is this actually a problem?

One obvious solution here would be to... not use tmux. Kitty has its own support for windows/tabs/panes, and by all accounts it's quite full-featured. There are other terminals out there too. And hey, I'm all for trying out new tools. But some tools are ingrained enough in my fingers that I'm hesitant to discard them.

Time for a workaround

Since at least for the moment I'm not ready to give up tmux, what's the lowest friction way to still get kitty-quality images? The flow doesn't have to be pretty, just the image 😎. So far the way that feels smoothest to me combines:

Kitty remote control

I start a kitty session at login and keep it always a keyboard shortcut away. I've updated my startup command to listen on a unix socket:

kitty -o allow_remote_control=yes --listen-on unix:/tmp/kitty_remote_control

Kitty tabs

With my kitty session listening on a socket, I can run commands inside tmux that run in new kitty tabs outside of tmux:

kitty @ --to unix:/tmp/kitty_remote_control launch --type=tab --title='my new tab'

Timg

Kitty's graphics protocol page lists a number of tools that use it. While I haven't tried them all, I've become particularly fond of timg. It has some friendly options for things like upscaling and handling transparency/alpha channels. It also has the option to gracefully fall back from kitty-quality to half or quarter pixels in cases where a blocky image is OK.

Duct taping it together

With a kimg script like this:

#!/bin/bash
set -euxo pipefail

FILE="$1"
shift

kitty @ --to unix:/tmp/kitty_remote_control launch --type=tab --title="$FILE" --no-response \
    /bin/bash -c "timg -pk -B gray --title='%f (%wx%h)' $* '$(realpath $FILE)' && read -sn1"

I can use kimg <image file> to:

Open a new kitty tab outside tmux
Run timg to display the image
Wait for a keypress to close the kitty tab and return to my tmux home

Video demo

This short video shows the difference between opening an image in tmux directly vs. in a dedicated kitty tab:

Better options

If you've read this post and thought "wow that's pretty gross, why doesn't he just..." then great! First of all, thanks for reading. And second, please share your own suggestions.

Just keep in mind that if the suggestion is "abandon tmux" I'll be listening with my hands over my ears for now.

Opening CSVs with VisiData in Tmux, from the Firefox Flatpak App

AJ Kerrigan — Sat, 18 Feb 2023 15:20:04 +0000

The Flatpak Wrinkle
- Create a Flatpak-Specific Desktop Entry
- Let Firefox Talk to the System Bus
- Tell Firefox to Use VisiData as a Default App
The Setup Outside of Flatpak
- Desktop Entry
- VisiData as a Default Application

I use VisiData as my default app for CSVs. There are a couple moving parts, and I had ignored the Firefox/Flatpak piece until recently. While this all feels very specific to my usage patterns, some of these bits were not at all obvious to me at first. So maybe something in here will be a useful reference to someone else or future me.

The Flatpak Wrinkle

I had a mostly-working setup (details) that opened CSVs in VisiData just fine, until I started using the Firefox Flatpak app. I kept thinking "I'll fix this someday..." and then a comment on a GitHub issue reminded me about it.

So the keys to fix my Flatpak setup were...

Create a Flatpak-Specific Desktop Entry

I needed to copy my existing VisiData.desktop file to ~/.var/app/org.mozilla.firefox/data/applications/VisiData.desktop and tweak the Exec line to use flatpak-spawn:

[Desktop Entry]
Encoding=UTF-8
Version=1.0
Type=Application
Terminal=false
Exec=/usr/bin/bash -c "flatpak-spawn --host i3-focus-or-launch.sh "kitty" "kitty" && flatpak-spawn --host /usr/bin/tmux new-window -n vd ~/.local/bin/vd %U"
MimeType=application/vnd.oasis.opendocument.spreadsheet;application/vnd.oasis.opendocument.spreadsheet-template;application/vnd.sun.xml.calc;application/vnd.sun.xml.calc.template;application/msexcel;application/vnd.ms-excel;application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;application/vnd.ms-excel.sheet.macroEnabled.12;application/vnd.openxmlformats-officedocument.spreadsheetml.template;application/vnd.ms-excel.template.macroEnabled.12;application/vnd.ms-excel.sheet.binary.macroEnabled.12;text/csv;application/x-dbf;text/spreadsheet;application/csv;application/excel;application/tab-separated-values;application/vnd.lotus-1-2-3;application/vnd.oasis.opendocument.chart;application/vnd.oasis.opendocument.chart-template;application/x-dbase;application/x-dos_ms_excel;application/x-excel;application/x-msexcel;application/x-ms-excel;application/x-quattropro;application/x-123;text/comma-separated-values;text/tab-separated-values;text/x-comma-separated-values;text/x-csv;application/vnd.oasis.opendocument.spreadsheet-flat-xml;application/vnd.ms-works;application/clarisworks;application/x-iwork-numbers-sffnumbers;application/x-starcalc;
Name=VisiData
Icon=~/code/visidata.org/static/vd-rtd.png

Let Firefox Talk to the System Bus

flatpak override --user --talk-name org.freedesktop.Flatpak org.mozilla.firefox

Tell Firefox to Use VisiData as a Default App

In ~/.var/app/org.mozilla.firefox/config/mimeapps.list:

[Default Applications]
...snip...
text/csv=VisiData.desktop
application/csv=VisiData.desktop
application/x-csv=VisiData.desktop
application/vnd.oasis.opendocument.spreadsheet=VisiData.desktop
application/vnd.oasis.opendocument.spreadsheet-flat-xml=VisiData.desktop
application/vnd.oasis.opendocument.spreadsheet-template=VisiData.desktop
application/vnd.sun.xml.calc=VisiData.desktop
application/vnd.sun.xml.calc.template=VisiData.desktop
application/msexcel=VisiData.desktop
application/vnd.ms-excel=VisiData.desktop
application/vnd.openxmlformats-officedocument.spreadsheetml.sheet=VisiData.desktop
application/vnd.ms-excel.sheet.macroenabled.12=VisiData.desktop
application/vnd.openxmlformats-officedocument.spreadsheetml.template=VisiData.desktop
application/vnd.ms-excel.template.macroenabled.12=VisiData.desktop
application/vnd.ms-excel.sheet.binary.macroenabled.12=VisiData.desktop
application/x-dbf=VisiData.desktop
text/spreadsheet=VisiData.desktop

[Added Associations]

The Setup Outside of Flatpak

I'm running Regolith Desktop 2.1 on Ubuntu 22.04, but this should generalize to Ubuntu+Gnome pairings in the same version neighborhood.

Desktop Entry

I have a file like this at ~/.local/share/applications/VisiData.desktop:

[Desktop Entry]
Encoding=UTF-8
Version=1.0
Type=Application
Terminal=false
Exec=/usr/bin/sh -c "i3-focus-or-launch.sh kitty kitty && /usr/bin/tmux new-window -n vd ~/.local/bin/vd %U"
MimeType=application/vnd.oasis.opendocument.spreadsheet;application/vnd.oasis.opendocument.spreadsheet-template;application/vnd.sun.xml.calc;application/vnd.sun.xml.calc.template;application/msexcel;application/vnd.ms-excel;application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;application/vnd.ms-excel.sheet.macroEnabled.12;application/vnd.openxmlformats-officedocument.spreadsheetml.template;application/vnd.ms-excel.template.macroEnabled.12;application/vnd.ms-excel.sheet.binary.macroEnabled.12;text/csv;application/x-dbf;text/spreadsheet;application/csv;application/excel;application/tab-separated-values;application/vnd.lotus-1-2-3;application/vnd.oasis.opendocument.chart;application/vnd.oasis.opendocument.chart-template;application/x-dbase;application/x-dos_ms_excel;application/x-excel;application/x-msexcel;application/x-ms-excel;application/x-quattropro;application/x-123;text/comma-separated-values;text/tab-separated-values;text/x-comma-separated-values;text/x-csv;application/vnd.oasis.opendocument.spreadsheet-flat-xml;application/vnd.ms-works;application/clarisworks;application/x-iwork-numbers-sffnumbers;application/x-starcalc;
Name=VisiData
Icon=~/code/visidata.org/static/vd-rtd.png

That Exec string is pretty specific to my workflow, but it:

Focuses my terminal
Creates a new tmux window named vd
Opens the file/URL with VisiData in that window

The list of MIME types was copy/pasted from Libre Office Calc, since this started with "anywhere Calc is trying to open, use VisiData instead".

VisiData as a Default Application

I update my ~/.config/mimeapps.list file so my desktop environment knows to use VisiData for a bunch of stuff:

[Added Associations]
...snip...
text/csv=VisiData.desktop;
application/csv=VisiData.desktop;
text/x-csv=VisiData.desktop;
text/spreadsheet=VisiData.desktop;

[Default Applications]
...snip...
text/csv=VisiData.desktop
application/csv=VisiData.desktop
text/x-csv=VisiData.desktop
text/spreadsheet=VisiData.desktop
application/vnd.oasis.opendocument.spreadsheet=VisiData.desktop
application/vnd.oasis.opendocument.spreadsheet-flat-xml=VisiData.desktop
application/vnd.oasis.opendocument.spreadsheet-template=VisiData.desktop
application/vnd.sun.xml.calc=VisiData.desktop
application/vnd.sun.xml.calc.template=VisiData.desktop
application/msexcel=VisiData.desktop
application/vnd.ms-excel=VisiData.desktop
application/vnd.openxmlformats-officedocument.spreadsheetml.sheet=VisiData.desktop
application/vnd.ms-excel.sheet.macroenabled.12=VisiData.desktop
application/vnd.openxmlformats-officedocument.spreadsheetml.template=VisiData.desktop
application/vnd.ms-excel.template.macroenabled.12=VisiData.desktop
application/vnd.ms-excel.sheet.binary.macroenabled.12=VisiData.desktop
application/x-dbf=VisiData.desktop
text/spreadsheet=VisiData.desktop

Again, this is mostly yoinking Libre Office Calc-related entries out of /usr/share/applications/gnome-mimeapps.list and overriding them to target VisiData for my user.

Hanukkah of Data 2022 - Puzzle 8 and Wrap-Up

AJ Kerrigan — Fri, 30 Dec 2022 21:58:21 +0000

Hanukkah of Data 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 for a bit more detail, but the pitch in my head is "Advent of Code meets SQL Murder Mystery". This post walks through my approach to the eighth puzzle.

Thinking
Doing (Datasette)
Doing (Pandas)
Wrap-Up

Warning: 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

For the last puzzle, we're looking for the person who still probably has the rug! Turns out he's a bit of a pack rat:

She wound up getting a newer and more expensive rug, so she gave it to an acquaintance of hers who collects all sorts of junk. Apparently he owns an entire set of Noah’s collectibles! He probably still has the rug, even.

The plan here will be find what counts as a Noah's collectible and see who has the most of them.

Doing (Datasette)

Based on a peek at the products table, it looks like collectibles all have a 'COL' sku prefix. So we should be able to find customers who have ordered the most distinct items with that prefix:

select
  name,
  phone,
  count(distinct sku) as colcount
from
  noahs_order_detail
where
  sku like 'COL%'
group by
  name,
  phone
order by
  colcount desc

And yes, Travis has more than 2x the collectible count of any other customer!

Doing (Pandas)

A pretty straight SQL-->pandas translation seems to work fine here:

df[
  df.sku.str.contains('^COL')
].groupby(
  ['name','phone']
).agg(
  {'sku':'nunique'}
).sort_values(by='sku').tail()

Wrap-Up

This was a lot of fun! Some specific takeaways:

Huge thanks to The Devottys for putting this challenge together
- Saul & Anja pour a lot of heart and smarts into VisiData and the data/terminal-loving community
- I'm consistently impressed by Dwimmer's artwork, he's magic at the terminal
There's something dangerous or funky about using category dtypes in pandas with groupby()
- I got various errors or saw operations take 40x longer, though this is at least partly my inexperience / ignorance
- Issues like https://github.com/pandas-dev/pandas/issues/36698 suggest I'm not alone!
Working through problems with multiple tools can sometimes help you rethink your approach in all of them
You don't have to be Simon Willison to get a lot out of SQLite

I should also point out the official wrap-up post here, which links to other community posts.

Hanukkah of Data 2022 - Puzzle 7

AJ Kerrigan — Fri, 30 Dec 2022 21:27:07 +0000

Thinking
Doing (Datasette)
- Emily's Orders
- Orders on Emily Days
- Matching by SKU Prefix
- Fine, I'll Filter on Description
- The Whole is Uglier Than the Sum of Its Parts
Doing (Pandas)
- Tricky Colors
- Getting Chainy

Warning: 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

It turns out that one day while Emily was robbing Noah's blind, another customer bought the same item in a different color:

“Well I turned around and sure enough this cute guy was holding something I had bought. He said ‘I got almost exactly the same thing!’ We laughed about it and wound up swapping items because he had wanted the color I got. We had a moment when our eyes met and my heart stopped for a second. I asked him to get some food with me and we spent the rest of the day together.

So we'll need to find orders on the same day as Emily, where the item is almost the same as what Emily bought...

Tackling this in pandas felt a bit daunting to me, so I figured I'd start with SQL. Looking through the data I saw that color usually showed up inside parentheses in a description. That seemed something worth pursuing.

Doing (Datasette)

Emily's Orders

Breaking the problem down into smaller pieces, I figured we'd want to have Emily's orders first. So I set up a common table expression for those:

with emily_orders as (
  select
    o.ordered,
    p.sku,
    p.desc
  from
    customers c
    join orders o on c.customerid = o.customerid
    join orders_items i on o.orderid = i.orderid
    join products p on i.sku = p.sku
  where
    c.name = 'Emily Randolph'
)

Orders on Emily Days

Then I pulled in non-Emily orders that were placed on the same day as one of Emily's:

select
  c.name,
  c.address,
  c.citystatezip,
  c.phone,
  o.ordered,
  em.ordered as em_ordered,
  p.sku,
  em.sku as em_sku,
  p.desc
from
  customers c
  join orders o on c.customerid = o.customerid
    and date(o.ordered) in (
        select
        distinct date(ordered)
        from
        emily_orders
    )

That was too many results, so I fine-tuned the order time to look for non-Emily orders placed within 5 minutes of an Emily order:

  join emily_orders em on c.name != 'Emily Randolph'
    and abs(
        strftime('%s', o.ordered) - strftime('%s', em.ordered)
    ) <= 300

Matching by SKU Prefix

That was still too many results, so I tried filtering on orders that matched the sku prefix:

  join emily_orders em on c.name != 'Emily Randolph'
  join orders_items i on o.orderid = i.orderid
  join products p on i.sku = p.sku
where
  substr(p.sku, 1, 3) = substr(em.sku, 1, 3)

Fine, I'll Filter on Description

I was trying to avoid comparing descriptions, but ran out of other ideas before finding a definitive match. So I tried matching just the portion of a description in front of any parentheses by adding this to the select:

  case
    when instr(p.desc, '(') = 0 then p.desc
    else substr(p.desc, 1, instr(p.desc, '(') - 1)
  end as stripped_desc,
  case
    when instr(em.desc, '(') = 0 then em.desc
    else substr(em.desc, 1, instr(em.desc, '(') - 1)
  end as em_stripped_desc

And matching it in the WHERE:

where
  substr(p.sku, 1, 3) = substr(em.sku, 1, 3)
  and stripped_desc = em_stripped_desc

This felt pretty awkward to me as any latent SQL Server or Postgres instincts were useless. But it did work!

I think there are better ways to do this with extensions and/or full-text search support. This felt like a really ugly way to work around a lack of fuzzy matching or string splitting operations.

The Whole is Uglier Than the Sum of Its Parts

That was a bumpy ride, and I ended up with this monstrosity:

with emily_orders as (
  select
    o.ordered,
    p.sku,
    p.desc
  from
    customers c
    join orders o on c.customerid = o.customerid
    join orders_items i on o.orderid = i.orderid
    join products p on i.sku = p.sku
  where
    c.name = 'Emily Randolph'
)
select
  c.name,
  c.phone,
  o.ordered,
  em.ordered as em_ordered,
  p.sku,
  em.sku as em_sku,
  p.desc,
  case
    when instr(p.desc, '(') = 0 then p.desc
    else substr(p.desc, 1, instr(p.desc, '(') - 1)
  end as stripped_desc,
  em.desc,
  case
    when instr(em.desc, '(') = 0 then em.desc
    else substr(em.desc, 1, instr(em.desc, '(') - 1)
  end as em_stripped_desc
from
  customers c
  join orders o on c.customerid = o.customerid
  and date(o.ordered) in (
    select
      distinct date(ordered)
    from
      emily_orders
  ) -- rough cut
  join emily_orders em on c.name != 'Emily Randolph'
  and abs(
    strftime('%s', o.ordered) - strftime('%s', em.ordered)
  ) <= 300 -- fine tune
  join orders_items i on o.orderid = i.orderid
  join products p on i.sku = p.sku
where
  substr(p.sku, 1, 3) = substr(em.sku, 1, 3)
  and stripped_desc = em_stripped_desc

But also an answer, so I'll take it.

Doing (Pandas)

Because the SQL approach felt so gross to me, I tried to rethink it rather than just "porting" it to pandas. The start was the same - finding a group of not-Emily orders on the same day as Emily orders:

df['ordered_date'] = df.ordered.dt.date
emily = df[df.name == "Emily Randolph"]
not_emily = df[df.name != "Emily Randolph"]
merged = not_emily.merge(emily, on="ordered_date")

But then I tried to come up with a better way to indicate "descriptions that differ just by color". This was tricky! I experimented a bit with difflib which is fun for looking at close string matches. But in the end I leaned on a little helper function to split a description into a set of lowercased words with the parentheses and other punctuation discarded:

import re

stemset = lambda x: {val.lower() for val in re.findall(r"\w+", x)}

Which I could use in a transform(), and then use apply() to find the symmetric difference of those sets between each Emily and not-Emily order:

merged["desc_diff"] = merged.transform({"desc_x": stemset, "desc_y": stemset}).apply(
    lambda x: x.desc_x ^ x.desc_y, axis=1
)

Because the color wasn't always present in an item description, I figured I could look for cases where the symmetric difference between description wordsets had either 1 or 2 words. Combining that with the "orders within 5 minutes of each other" condition, that left this:

merged[
    merged.apply(
        lambda x: abs((x.ordered_x - x.ordered_y).total_seconds()) <= 300
        and len(x.desc_diff) in (1, 2),
        axis=1,
    )
]

Tricky Colors

I already had the answer by this point, but there was one thing nagging me: how would I check for specifically a color difference rather than just a 1-2 word difference? This is where I had to shake an angry yet appreciative fist at the puzzle design. Because while I could find lists of color names in packages like webcolors, matplotlib, Crayola APIs, etc... the relevant colors in this puzzle were outside the core set of Crayola/HTML/CSS colors. The best alternative I found was to pull out a distinct set of colors like this:

colors = set(
  df.desc.transform(
    lambda x: (match := re.search(r'\(([a-z]+)\)', x) ) and match.groups() or []
  ).explode().dropna().unique()
)

...and then check to be sure my desc_diff column contained only words from that set. In this case that ended up not being necessary or useful, but hey I was curious.

Getting Chainy

Some pandas users like to chain their methods together rather than leaving a trail of intermediate variables. I don't have a consistent preference, but it feels like good practice to work both ways. So if I tried to chainify this pandas code, it would look something like this:

df[df.name != "Emily Randolph"].merge(
    df[df.name == "Emily Randolph"], on="ordered_date"
).assign(
    desc_diff=lambda x: x.transform({"desc_x": stemset, "desc_y": stemset}).apply(
        lambda x: x.desc_x ^ x.desc_y, axis=1
    )
).pipe(
    lambda x: x[
        x.apply(
            lambda x: abs((x.ordered_x - x.ordered_y).total_seconds()) <= 300
            and len(x.desc_diff) in (1, 2),
            axis=1,
        )
    ]
)

I hope that when I browse other people's solutions I find a cleaner chained version, because this looks gross to me 😅.

Hanukkah of Data 2022 - Puzzle 6

AJ Kerrigan — Fri, 30 Dec 2022 21:07:43 +0000

Thinking
Doing (Pandas)
Doing (Datasette)

Warning: 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

Day 6 brings us a cheapskate!

“She refused to buy a new rug for herself–she said they were way too expensive. She’s always been very frugal, and she clips every coupon and shops every sale at Noah’s Market. In fact I like to tease her that Noah actually loses money whenever she comes in the store.

So we're looking for someone who's buying things on sale...

Doing (Pandas)

We have the wholesale cost of items, along with the unit price and quantity of each order. That means we can define the profit for each as:

df['profit'] = df.qty * (df.unit_price - df.wholesale_cost)

Which means we can see which customer represented the lowest profit (or greatest loss) in aggregate:

df.groupby(
  ['name','phone']
).agg(
  {'profit':'sum'}
).sort_values(
  by='profit'
).head()

Emily, you're a thief!

Doing (Datasette)

Having a noahs_order_detail view with the same structure as my pandas DataFrame makes this conversion pretty smooth:

select
  name,
  phone,
  sum(qty * (unit_price - wholesale_cost)) as profit
from
  noahs_order_detail
group by
  name,
  phone
order by
  profit

Hanukkah of Data 2022 - Puzzle 5

AJ Kerrigan — Fri, 30 Dec 2022 20:51:38 +0000

Thinking
Doing (Pandas)
Doing (Datasette)
Refining / Reusing

Warning: 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

You know what Hanukkah of Data was missing? A cat lady! And that's where the rug went next:

“I listed it on Freecycle, and a woman in Queens Village came to pick it up. She was wearing a ‘Noah’s Market’ sweatshirt, and it was just covered in cat hair. When I suggested that a clowder of cats might ruin such a fine tapestry, she looked at me funny and said she only had ten or eleven cats and they were getting quite old and had cataracts now so they probably wouldn’t notice some old rug anyway.

Extracting some facts

The next rug owner had a bunch of cats
The cats are old
She had a Noah's Market sweatshirt (seemed relevant initially)
Lives in Queens Village

Doing (Pandas)

So who's buying cat stuff?

df[(df.desc.str.contains(r'cat',case=False))]

Lots of people, cool. But who from Queens Village?

df[
  (df.desc.str.contains(r'cat',case=False))
  & (df.citystatezip.str.contains('Queens Village'))
]

Yikes Anita, that's a ton of cat stuff. Eyeballing the results it also looks like she's specifically buying senior cat food, which jives with the clues. We can bake that into the query and roll up by quantity though, to make things even clearer:

df[
  (df.desc.str.contains(r'senior.*cat', case=False, regex=True))
  & (df.citystatezip.str.contains('Queens Village'))
].groupby(['name', 'citystatezip', 'phone']).agg({'qty': sum})

Doing (Datasette)

Again the second tool feels more straightforward. The pandas work turns into this SQL query:

select
  c.name,
  c.citystatezip,
  c.phone,
  sum(i.qty) as total_items
from
  customers c
  join orders o on c.customerid = o.customerid
  join orders_items i on o.orderid = i.orderid
  join products p on i.sku = p.sku
where
  c.citystatezip like 'Queens Village%'
  and p.desc like '%senior%cat%'
group by
  c.name,
  c.citystatezip,
  c.phone
order by
  total_items desc

Though I did bump my head on this at first, as I was using count() rather than sum(). Whoops!

Refining / Reusing

By this point I was reusing the same core query with the same joins a lot, and realized I probably should have created a view for it already. So:

create view noahs_order_detail as
select
  c.*,
  o.orderid,
  o.ordered,
  o.shipped,
  o.total,
  i.qty,
  i.unit_price,
  p.sku,
  p.desc,
  p.wholesale_cost
from
  customers c
  join orders o on c.customerid = o.customerid
  join orders_items i on o.orderid = i.orderid
  join products p on i.sku = p.sku

Hanukkah of Data 2022 - Puzzle 4

AJ Kerrigan — Fri, 30 Dec 2022 20:34:48 +0000

Thinking
Doing (Pandas)
Doing (Datasette)

Warning: 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

On the fourth day, we find a woman who likes to eat pastries early in the morning:

“A few weeks later my bike chain broke on the way home, and I needed to get it fixed before work the next day. Thankfully, this woman I met on Tinder came over at 5am with her bike chain repair kit and some pastries from Noah’s. Apparently she liked to get up before dawn and claim the first pastries that came out of the oven.

Doing (Pandas)

This time I turned things around and started with Pandas. Because I had explored the data a bit prior to starting puzzles, I noticed that the sku column started with a category/department prefix. Baked goods all seemed to have a bakery prefix of BKY, so finding orders with that sku prefix seemed like a good start:

df[(df.sku.str.contains('BKY'))]

That was far too many results (as expected), so the next step was to find bakery orders early in the morning. Let's say between 4am and 9am, which should cover cases where someone got up before dawn and got the first pastries out of the oven:

df[(df.sku.str.contains('BKY')) & (df.ordered.dt.hour.between(4,9))]

That was still a bunch of rows! But the clues make it sound like this is a habit. So how about looking at which customers get those early morning pastries most often?

df[
    (df.sku.str.contains('BKY'))
    & (df.ordered.dt.hour.between(4,9))
].groupby(['name','phone']).size().sort_values().tail()

That suggests Cristina Booker as the clear winner, and narrowing that time range from 4-9am to 4-6am makes it even clearer.

Doing (Datasette)

Adapting the pandas logic to SQL left me with this query:

select
  c.name,
  c.phone,
  count(*) as ordercount
from
  customers c
  join orders o on c.customerid = o.customerid
  join orders_items i on o.orderid = i.orderid
  join products p on i.sku = p.sku
where
  p.sku like 'BKY%'
  and cast(strftime('%H', o.ordered) as int) <= 5
group by
  c.name,
  c.phone
order by ordercount desc

Hanukkah of Data 2022 - Puzzle 3

AJ Kerrigan — Fri, 30 Dec 2022 20:02:14 +0000

Thinking
- Tool Choices
Doing (Datasette)
- Joining and Selecting
- Filtering
- All Together Now
Doing (Pandas)

Warning: 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

In the third puzzle, we learn that the rug passed from a contractor to someone in his neighborhood:

“At last I couldn’t deal with the rug taking up my whole bathtub, so I gave it to this guy who lived in my neighborhood. He said that he was naturally assertive because he was a Aries born in the year of the Dog, so maybe he was able to clean it.

Extracting some facts about the guy in the contractor's neighborhood:

Aries
- Birth date between March 21 and April 19
Born in the year of the dog
- 1958, 1970, etc
Lives in the contractor's neighborhood
- To start, look at the same zip code

Tool Choices

VisiData would work great for this puzzle, but I was having fun practicing with the combo of Datasette and Pandas so I kept rolling with it for the rest of the challenge.

Doing (Datasette)

Joining and Selecting

Here we're only looking at customer data, so there are no joins required. A select/from block like this works just fine:

select
  c.name,
  c.birthdate,
  c.address,
  c.citystatezip,
  c.phone
from
  customers c

Filtering

We're looking for someone in the same neighborhood as the contractor from the previous puzzle. So let's look in that zip code:

where
  c.citystatezip like '%11420'

...who was born in the year of the dog. A search took me to this page which says that's every 12 years, most recently 2018. In a Python REPL, 2018 % 12 == 2 so:

  and strftime('%Y', c.birthdate) % 12 = 2

The last bit looking for an Aries is a nice touch - a fun subtle bit of puzzle design from the Devottys. Because horoscope borders cross months, it makes things just a bit trickier than looking at birth month alone. My first attempt was to convert the birthdate to "date of the year" (with a bit of padding to cover leap years), but that felt a bit quirky afterward. Tweaking it to use this felt a little clearer and more explicit:

  -- March 21 - April 19
  and cast(strftime('%m%d', c.birthdate) as int) between 321 and 419

All Together Now

That combines into a tidier/simpler than Puzzle 2:

select
  distinct c.name,
  c.birthdate,
  c.address,
  c.citystatezip,
  c.phone
from
  customers c
  join orders o on c.customerid = o.customerid
  join orders_items i on o.orderid = i.orderid
  join products p on i.sku = p.sku
where
  c.citystatezip like '%11420'
  and strftime('%Y', c.birthdate) % 12 = 2
  and cast(strftime('%m%d', c.birthdate) as int) between 321 and 419

Doing (Pandas)

After doing all the thinking and building a SQL query, the Pandas piece feels a little anti-climactic:

df[
    (df.birthdate.dt.day_of_year.between(79,111))
    & (df.birthdate.dt.year % 12 == 2)
    & (df.citystatezip.str.contains('11420'))
]

Using the arguably clearer "month + day" formulation felt a bit more awkward in Pandas, but maybe that's because there's a better way to do this than faithfully porting my SQL 🤔:

df[
    (df.birthdate.dt.strftime('%m%d').transform(int).between(321,419))
    & (df.birthdate.dt.year % 12 == 2)
    & (df.citystatezip.str.contains('11420'))
]