Or How do you Slice and Dice Data?

A former employer of mine rolled out a graphical tool years ago which let us interrogate tabular data sets with minimal coding. Think Excel PivotTable except better. You could drag column headers and drop them into the left margin to summarize (group) data by the corresponding values (or dimension). There was a filter dialog who let you compose complex Boolean expressions with a few mouse clicks. That was so helpful and I miss it to this day.

After I left, I started looking around for a substitute and came across PivotTable.js. I liked it but was immediately nerd sniped by this comment. I looked at the code and thought that implementing OLAP in JavaScript probably contributed to data size limitations. Around the same time, I noticed that Chrome, like many industrial applications, embedded SQLite. Not only that, it even exposed it to JavaScript. Yes, the W3C page already sported the deprecation warning but I was young and naive so I worked my way through the quirky Web SQL API and did a thing.

React and Angular were the leading Web UI frameworks at the time but frameworks just irked me and I wanted something more lightweight so I reached for <gasp>Web Components</gasp>! PivotTable.js also relied on jQuery and I wouldn’t stand for that so I learned the HTML Drag and Drop API. And that crazy gizmo kinda worked back in 2019!

jburgy/data-grid sat there, gathering dust (and bit rot) until I created an issue to Replace Web SQL on the Spring Equinox. You see, Chrome finally did deprecate and remove Web SQL as threatened promised. And just like that, lthe little spark that set data-grid apart was estinguished. However, a great many things happened to the Web during that time. Most importantly, WebAssembly democratized embedding non-web software in web browsers. Much to their credit, the SQLite maintainers decided to offer an official WASM port of their awesome product.

@sqlite.org/sqlite-wasm brought data-grid back from the brink. I should also say that its Promise-based Wrapper is much less quirky that the old Web SQL API. That first step was the easy part. The heavy lift came from the fact that I insisted on embedding a demo in this blog post. For better or worse, I really liked how the most recent iteration of the What, python, slow? post turned out: using JupyterLite. Had I known how much additional worked it would have created, I might have chosen a simpler approach. (Nah, who am I kidding, since when do I do these because they’re easy)

2019 data-grid also came as an incredibly clunky custom Jupyter Widget. You need to remember that es6 modules were not widely supported back then so you still needed to contend with nonsense like AMD and UMD. anywidget appeared in 2024 precisely to make custom Jupyter Widgets easy (or at least easier if you don’t need to worry about bundling dependencies).

Fortunately, many of the pieces were already in place. Unfortunately, previous demos on this blog pale in comparison with the complexity of this one, particularly when it comes to import depth. gzuidhof/coi-serviceworker, which let me circumvent GitHub’s persistent lack of custom headers many times before, just wouldn’t work this time. I asked Claude and Copilot for help but they came up empty so I threw in the towel and used a Cloudflare Worker. Its name is a nod to my ancestors since my grandmother grew up on a farm that is now at the bottom of the lake which surrounds l’Île d’Ogoz.

Lastly, I needed to figure out how to mount the Origin Private File System in a pyodide kernel. I had never heard of pyodide_js until today. The amount of back-and-forth between python and JavaScript reminds me of Robert Downey Jr’s character in “Tropic Thunder” (“I’m a dude playing a dude disguised as another dude”).

The notebook below runs entirely in your browser. After installing anywidget, it fetches a public dataset on traffic violations, massages it with pandas before saving it to a SQLite file in OPFS. The widget uses @sqlite.org/sqlite-wasm to summarize that data.

And if you read that far, first of all congratulations! I realize you might still like an answer to the question posed in this article’s title. Besides sounding like a Wallace & Gromit invention, The Veg-O-Matic is the appliance that coined the “It slices! It dices!” catchphrase. I thought it was relevant since we’re discussing a tool to slice and dice data. Womp womp.