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 that 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, the little spark that set data-grid apart was extinguished. 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 work 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.1 The missing piece turned out to be scope: the worker has to control the iframe response and all of its imports, so the notebook now lives under the same origin as this post.

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 demo below runs entirely in your browser. PyScript fetches a public dataset on traffic violations, massages it with pandas, and hands it straight to the web component, which uses @sqlite.org/sqlite-wasm to summarize it. Drag the attribute chips between the row and column axes.

Update: this used to be a JupyterLite notebook served from /lite, and most of the fight described above was about getting cross-origin isolation to reach inside that iframe.1 It no longer is. <data-grid> grew a data-vfs attribute, so the demo can ask sqlite for an in-memory database instead of OPFS — and without OPFS there is no SharedArrayBuffer, no COOP/COEP, and no service worker to coax into scope. What replaced a whole JupyterLite build is one static page driven by PyScript. The struggle was real; it was also, in the end, avoidable.

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.

  1. Strictly speaking the coi-serviceworker shim stopped being necessary even before that. Once the notebook was served from /lite under the same origin, JupyterLite’s own service worker took care of the isolation headers for the notebook app and its imports. The page-level shim was only needed while I was still trying to fake that setup from outside the iframe. ↩ ↩2