Simon Willison's Weblog: data

Highlights from my appearance on the Data Renegades podcast with CL Kao and Dori Wilson

2025-11-26T00:29:11+00:00

I talked with CL Kao and Dori Wilson for an episode of their new Data Renegades podcast titled Data Journalism Unleashed with Simon Willison.

I fed the transcript into Claude Opus 4.5 to extract this list of topics with timestamps and illustrative quotes. It did such a good job I'm using what it produced almost verbatim here - I tidied it up a tiny bit and added a bunch of supporting links.

What is data journalism and why it's the most interesting application of data analytics [02:03]

"There's this whole field of data journalism, which is using data and databases to try and figure out stories about the world. It's effectively data analytics, but applied to the world of news gathering. And I think it's fascinating. I think it is the single most interesting way to apply this stuff because everything is in scope for a journalist."
The origin story of Django at a small Kansas newspaper [02:31]

"We had a year's paid internship from university where we went to work for this local newspaper in Kansas with this chap Adrian Holovaty. And at the time we thought we were building a content management system."
Building the "Downloads Page" - a dynamic radio player of local bands [03:24]

"Adrian built a feature of the site called the Downloads Page. And what it did is it said, okay, who are the bands playing at venues this week? And then we'll construct a little radio player of MP3s of music of bands who are playing in Lawrence in this week."
Working at The Guardian on data-driven reporting projects [04:44]

"I just love that challenge of building tools that journalists can use to investigate stories and then that you can use to help tell those stories. Like if you give your audience a searchable database to back up the story that you're presenting, I just feel that's a great way of building more credibility in the reporting process."
Washington Post's opioid crisis data project and sharing with local newspapers [05:22]

"Something the Washington Post did that I thought was extremely forward thinking is that they shared [the opioid files] with other newspapers. They said, 'Okay, we're a big national newspaper, but these stories are at a local level. So what can we do so that the local newspaper and different towns can dive into that data for us?'"
NICAR conference and the collaborative, non-competitive nature of data journalism [07:00]

"It's all about trying to figure out what is the most value we can get out of this technology as an industry as a whole."

NICAR 2026
ProPublica and the Baltimore Banner as examples of nonprofit newsrooms [09:02]

"The Baltimore Banner are a nonprofit newsroom. They have a hundred employees now for the city of Baltimore. This is an enormously, it's a very healthy newsroom. They do amazing data reporting... And I believe they're almost breaking even on subscription revenue [correction, not yet], which is astonishing."
The "shower revelation" that led to Datasette - SQLite on serverless hosting [10:31]

"It was literally a shower revelation. I was in the shower thinking about serverless and I thought, 'hang on a second. So you can't use Postgres on serverless hosting, but if it's a read-only database, could you use SQLite? Could you just take that data, bake it into a blob of a SQLite file, ship that as part of the application just as another asset, and then serve things on top of that?'"
Datasette's plugin ecosystem and the vision of solving data publishing [12:36]

"In the past I've thought about it like how Pinterest solved scrapbooking and WordPress solved blogging, who's going to solve data like publishing tables full of data on the internet? So that was my original goal."
Unexpected Datasette use cases: Copenhagen electricity grid, Brooklyn Cemetery [13:59]

"Somebody was doing research on the Brooklyn Cemetery and they got hold of the original paper files of who was buried in the Brooklyn Cemetery. They digitized those, loaded the results into Datasette and now it tells the story of immigration to New York."
Bellingcat using Datasette to investigate leaked Russian food delivery data [14:40]

"It turns out the Russian FSB, their secret police, have an office that's not near any restaurants and they order food all the time. And so this database could tell you what nights were the FSB working late and what were the names and phone numbers of the FSB agents who ordered food... And I'm like, 'Wow, that's going to get me thrown out of a window.'"

Bellingcat: Food Delivery Leak Unmasks Russian Security Agents
The frustration of open source: no feedback on how people use your software [16:14]

"An endless frustration in open source is that you really don't get the feedback on what people are actually doing with it."
Open office hours on Fridays to learn how people use Datasette [16:49]

"I have an open office hours Calendly, where the invitation is, if you use my software or want to use my software, grab 25 minutes to talk to me about it. And that's been a revelation. I've had hundreds of conversations in the past few years with people."
Data cleaning as the universal complaint - 95% of time spent cleaning [17:34]

"I know every single person I talk to in data complains about the cleaning that everyone says, 'I spend 95% of my time cleaning the data and I hate it.'"
Version control problems in data teams - Python scripts on laptops without Git [17:43]

"I used to work for a large company that had a whole separate data division and I learned at one point that they weren't using Git for their scripts. They had Python scripts, littering laptops left, right and center and lots of notebooks and very little version control, which upset me greatly."
The Carpentries organization teaching scientists Git and software fundamentals [18:12]

"There's an organization called The Carpentries. Basically they teach scientists to use Git. Their entire thing is scientists are all writing code these days. Nobody ever sat them down and showed them how to use the UNIX terminal or Git or version control or write tests. We should do that."
Data documentation as an API contract problem [21:11]

"A coworker of mine said, you do realize that this should be a documented API interface, right? Your data warehouse view of your project is something that you should be responsible for communicating to the rest of the organization and we weren't doing it."
The importance of "view source" on business reports [23:21]

"If you show somebody a report, you need to have view source on those reports... somebody would say 25% of our users did this thing. And I'm thinking I need to see the query because I knew where all of the skeletons were buried and often that 25% was actually a 50%."
Fact-checking process for data reporting [24:16]

"Their stories are fact checked, no story goes out the door without someone else fact checking it and without an editor approving it. And it's the same for data. If they do a piece of data reporting, a separate data reporter has to audit those numbers and maybe even produce those numbers themselves in a separate way before they're confident enough to publish them."
Queries as first-class citizens with version history and comments [27:16]

"I think the queries themselves need to be first class citizens where like I want to see a library of queries that my team are using and each one I want to know who built it and when it was built. And I want to see how that's changed over time and be able to post comments on it."
Two types of documentation: official docs vs. temporal/timestamped notes [29:46]

"There's another type of documentation which I call temporal documentation where effectively it's stuff where you say, 'Okay, it's Friday, the 31st of October and this worked.' But the timestamp is very prominent and if somebody looks that in six months time, there's no promise that it's still going to be valid to them."
Starting an internal blog without permission - instant credibility [30:24]

"The key thing is you need to start one of these without having to ask permission first. You just one day start, you can do it in a Google Doc, right?... It gives you so much credibility really quickly because nobody else is doing it."
Building a search engine across seven documentation systems [31:35]

"It turns out, once you get a search engine over the top, it's good documentation. You just have to know where to look for it. And if you are the person who builds the search engine, you secretly control the company."
The TIL (Today I Learned) blog approach - celebrating learning basics [33:05]

"I've done TILs about 'for loops' in Bash, right? Because okay, everyone else knows how to do that. I didn't... It's a value statement where I'm saying that if you've been a professional software engineer for 25 years, you still don't know everything. You should still celebrate figuring out how to learn 'for loops' in Bash."
Coding agents like Claude Code and their unexpected general-purpose power [34:53]

"They pretend to be programming tools but actually they're basically a sort of general agent because they can do anything that you can do by typing commands into a Unix shell, which is everything."
Skills for Claude - markdown files for census data, visualization, newsroom standards [36:16]

"Imagine a markdown file for census data. Here's where to get census data from. Here's what all of the columns mean. Here's how to derive useful things from that. And then you have another skill for here's how to visualize things on a map using D3... At the Washington Post, our data standards are this and this and this."

Claude Skills are awesome, maybe a bigger deal than MCP
The absurd 2025 reality: cutting-edge AI tools use 1980s terminal interfaces [38:22]

"The terminal is now accessible to people who never learned the terminal before 'cause you don't have to remember all the commands because the LLM knows the commands for you. But isn't that fascinating that the cutting edge software right now is it's like 1980s style— I love that. It's not going to last. That's a current absurdity for 2025."
Cursor for data? Generic agent loops vs. data-specific IDEs [38:18]

"More of a notebook interface makes a lot more sense than a Claude Code style terminal 'cause a Jupyter Notebook is effectively a terminal, it's just in your browser and it can show you charts."
Future of BI tools: prompt-driven, instant dashboard creation [39:54]

"You can copy and paste a big chunk of JSON data from somewhere into [an LLM] and say build me a dashboard. And they do such a good job. Like they will just decide, oh this is a time element so we'll do a bar chart over time and these numbers feel big so we'll put those in a big green box."
Three exciting LLM applications: text-to-SQL, data extraction, data enrichment [43:06]

"LLMs are stunningly good at outputting SQL queries. Especially if you give them extra metadata about the columns. Maybe a couple of example queries and stuff."
LLMs extracting structured data from scanned PDFs at 95-98% accuracy [43:36]

"You file a freedom of information request and you get back horrifying scanned PDFs with slightly wonky angles and you have to get the data out of those. LLMs for a couple of years now have been so good at, 'here's a page of a police report, give me back JSON with the name of the arresting officer and the date of the incident and the description,' and they just do it."
Data enrichment: running cheap models in loops against thousands of records [44:36]

"There's something really exciting about the cheaper models, Gemini Flash 2.5 Lite, things like that. Being able to run those in a loop against thousands of records feels very valuable to me as well."

datasette-enrichments
Multimodal LLMs for images, audio transcription, and video processing [45:42]

"At one point I calculated that using Google's least expensive model, if I wanted to generate captions for like 70,000 photographs in my personal photo library, it would cost me like $13 or something. Wildly inexpensive."

Correction: with Gemini 1.5 Flash 8B it would cost 173.25 cents
First programming language: hated C++, loved PHP and Commodore 64 BASIC [46:54]

"I hated C++ 'cause I got my parents to buy me a book on it when I was like 15 and I did not make any progress with Borland C++ compiler... Actually, my first program language was Commodore 64 BASIC. And I did love that. Like I tried to build a database in Commodore 64 BASIC back when I was like six years old or something."
Biggest production bug: crashing The Guardian's MPs expenses site with a progress bar [47:46]

"I tweeted a screenshot of that progress bar and said, 'Hey, look, we have a progress bar.' And 30 seconds later the site crashed because I was using SQL queries to count all 17,000 documents just for this one progress bar."

Crowdsourced document analysis and MP expenses
Favorite test dataset: San Francisco's tree list, updated several times a week [48:44]

"There's 195,000 trees in this CSV file and it's got latitude and longitude and species and age when it was planted... and get this, it's updated several times a week... most working days, somebody at San Francisco City Hall updates their database of trees, and I can't figure out who."
Showrunning TV shows as a management model - transferring vision to lieutenants [50:07]

"Your job is to transfer your vision into their heads so they can go and have the meetings with the props department and the set design and all of those kinds of things... I used to sniff at the idea of a vision when I was young and stupid. And now I'm like, no, the vision really is everything because if everyone understands the vision, they can make decisions you delegate to them."

The Eleven Laws of Showrunning by Javier Grillo-Marxuach
Hot take: all executable code with business value must be in version control [52:21]

"I think it's inexcusable to have executable code that has business value that is not in version control somewhere."
Hacker News automation: GitHub Actions scraping for notifications [52:45]

"I've got a GitHub actions thing that runs a piece of software I wrote called shot-scraper that runs Playwright, that loads up a browser in GitHub actions to scrape that webpage and turn the results into JSON, which then get turned into an atom feed, which I subscribe to in NetNewsWire."
Dream project: whale detection camera with Gemini AI [53:47]

"I want to point a camera at the ocean and take a snapshot every minute and feed it into Google Gemini or something and just say, is there a whale yes or no? That would be incredible. I want push notifications when there's a whale."
Favorite podcast: Mark Steel's in Town (hyperlocal British comedy) [54:23]

"Every episode he goes to a small town in England and he does a comedy set in a local venue about the history of the town. And so he does very deep research... I love that sort of like hyperlocal, like comedy, that sort of British culture thing."

Mark Steel's in Town available episodes
Favorite fiction genre: British wizards caught up in bureaucracy [55:06]

"My favorite genre of fiction is British wizards who get caught up in bureaucracy... I just really like that contrast of like magical realism and very clearly researched government paperwork and filings."

The Laundry Files, Rivers of London, The Rook

Colophon

I used a Claude Project for the initial analysis, pasting in the HTML of the transcript since that included <span data-timestamp="425"> elements. The project uses the following custom instructions

You will be given a transcript of a podcast episode. Find the most interesting quotes in that transcript - quotes that best illustrate the overall themes, and quotes that introduce surprising ideas or express things in a particularly clear or engaging or spicy way. Answer just with those quotes - long quotes are fine.

I then added a follow-up prompt saying:

Now construct a bullet point list of key topics where each item includes the mm:ss in square braces at the end

Then suggest a very comprehensive list of supporting links I could find

Then one more follow-up:

Add an illustrative quote to every one of those key topics you identified

Here's the full Claude transcript of the analysis.

Tags: data, data-journalism, django, ai, datasette, podcast-appearances

GROUNDHOG-DAY.com

2023-02-02T22:05:28+00:00

GROUNDHOG-DAY.com

“The leading Groundhog Day data source”. I love this so much: it’s a collection of predictions from all 59 groundhogs active in towns scattered across North America (I had no idea there were that many). The data is available via a JSON API too.

Via Show HN: Groundhog-day.com – structured groundhog data

Tags: data

Quoting Andrej Karpathy

2022-08-24T21:28:00+00:00

To make the analogy explicit, in Software 1.0, human-engineered source code (e.g. some .cpp files) is compiled into a binary that does useful work. In Software 2.0 most often the source code comprises 1) the dataset that defines the desirable behavior and 2) the neural net architecture that gives the rough skeleton of the code, but with many details (the weights) to be filled in. The process of training the neural network compiles the dataset into the binary — the final neural network. In most practical applications today, the neural net architectures and the training systems are increasingly standardized into a commodity, so most of the active “software development” takes the form of curating, growing, massaging and cleaning labeled datasets.

— Andrej Karpathy

Tags: data, machine-learning, ai, andrej-karpathy

The data team: a short story

2021-07-08T23:12:59+00:00

The data team: a short story

Erik Bernhardsson’s fictional account (“I guess I should really call this a parable”) of a new data team leader successfully growing their team and building a data-first culture in a medium-sized technology company. His depiction of the initial state of the company (data in many different places, frustrated ML researchers who can’t get their research into production, confusion over what the data team is actually for) definitely rings true to me.

Via Hacker News

Tags: data, data-science, leadership

What I've learned about data recently

2021-06-22T17:09:07+00:00

What I've learned about data recently

Laurie Voss talks about the structure of data teams, based on his experience at npm and more recently Netlify. He suggests that Airflow and dbt are the data world’s equivalent of frameworks like Rails: opinionated tools that solve core problems and which mean that you can now hire people who understand how your data pipelines work on their first day on the job.

Via @seldo

Tags: data, big-data, data-science, laurie-voss

The Seven Secrets of Successful Data Scientists

2010-09-03T00:36:00+00:00

The Seven Secrets of Successful Data Scientists

Some sensible advice, including pick the right sized tool, compress everything, split up your data, use open source and run the analysis where the data is.

Tags: data, big-data, recovered

Using Freebase Gridworks to Create Linked Data

2010-08-23T20:11:00+00:00

Using Freebase Gridworks to Create Linked Data

A very handy tutorial from data.gov.uk’s Jeni Tennison.

Tags: data, datagovuk, freebase, gridworks, recovered, jenitennison

Quoting Kellan Elliott-McCrea

2010-05-18T18:21:00+00:00

With Flickr you can get out, via the API, every single piece of information you put into the system. [...] Asking people to accept anything else is sharecropping. It’s a bad deal. Flickr helped pioneer “Web 2.0″, and personal data ownership is a key piece of that vision. Just because the wider public hasn’t caught on yet to all the nuances around data access, data privacy, data ownership, and data fidelity, doesn’t mean you shouldn’t be embarrassed to be failing to deliver a quality product.

— Kellan Elliott-McCrea

Tags: data, flickr, kellan-elliott-mccrea, sharecropping, web20, recovered

Preview: Freebase Gridworks

2010-03-27T18:43:42+00:00

Preview: Freebase Gridworks

If my experience with government datasets has taught me anything, it’s that most datasets are collected by human beings (probably using Excel) and human beings are inconsistent. The first step in any data related project inevitably involves cleaning up the data. The Freebase team must run up against this all the time, and it looks like they’re tackling the problem head-on. Freebase Gridworks is just a screencast preview at the moment but an open source release is promised “within a month”—and the tool looks absolutely fantastic. DabbleDB-style data refactoring of spreadsheet data, running on your desktop but with the UI served in a browser. Full undo, a JavaScript-based expression language, powerful faceting and the ability to “reconcile” data against Freebase types (matching up country names, for example). I can’t wait to get my hands on this.

Via Jon Udell

Tags: cleanup, dabbledb, data, freebase, gridworks, javascript, open-data

The Case For An Older Woman

2010-02-17T22:20:03+00:00

The Case For An Older Woman

OK Cupid’s fascinating statistics blog uses cleverly plotted aggregate data from the dating site to illustrate the difference in age tastes between the genders (men try to date younger women) and show why that might not be the best strategy. An infographics tour-de-force.

Tags: data, dating, graphs, infographics, okcupid

World Government Data

2010-01-27T12:27:03+00:00

World Government Data

Launched last week, this is the Guardian’s meta-search engine for searching and browsing through data from four different government data sites (with more sites planned). Under the hood it’s Django, Solr, Haystack and the Scrapy crawling library. The application was built by Ben Firshman during an internship over Christmas.

Tags: ben-firshman, data, datagovuk, django, guardian, haystack, projects, python, scrapy, solr

Toiling in the data-mines: what data exploration feels like

2009-10-26T09:34:34+00:00

Toiling in the data-mines: what data exploration feels like

Useful advice from Tom Armitage on the exploratory development approach required when starting to build a project against a large, complex dataset. Tips include making sure you have a REPL to hand and using tools like gRaphael to generate graphs against pretty much everything, since until you’ve seen their shape you won’t know if they are interesting or not.

Tags: berg, data, exploratoryprogramming, graphael, graphing, programming, repl, tom-armitage

Yahoo! Geo: Announcing GeoPlanet Data

2009-05-20T21:12:24+00:00

Yahoo! Geo: Announcing GeoPlanet Data

The Yahoo! WhereOnEarth geographic data set is fantastic, but I’ve always felt slightly uncomfortable about building applications against it in case the API went away. That’s not an issue any more—the entire dataset is now available to download and use under a Creative Commons Attribution license. It’s not entirely clear what the attribution requirements are—do you have to put “data from GeoPlanet” on every page or can you get away with just tucking the attribution away in an “about this site” page? UPDATE: The data doesn’t include latitude/longitude or bounding boxes, which severely reduces its utility.

Tags: attribution, creativecommons, data, geoplanet, geospatial, gis, whereonearth, yahoo

Drug seizures: how pure is street cocaine?

2009-05-13T12:34:03+00:00

Drug seizures: how pure is street cocaine?

Neat story on the Guardian Datablog using graphs from Timetric to show that while the purity of cocaine seized by customs over the past five years has stayed constant, the purity of drugs seized by the police has been trending downwards.

Tags: cocaine, data, drugs, guardian, stats, timetric

Drop ACID and think about data

2009-04-17T17:13:57+00:00

Drop ACID and think about data

I’ve been very impressed with the quality and speed with which the PyCon 2009 videos have been published. Here’s Bob Ippolito on distributed databases and key/value stores.

Tags: acid, bob-ippolito, data, databases, pycon, pycon2009, python

A few notes on the Guardian Open Platform

2009-03-10T14:28:39+00:00

This morning we launched the Guardian Open Platform at a well attended event in our new offices in Kings Place. This is one of the main projects I've been helping out with since joining the Guardian last year, and it's fantastic to finally have it out in the open.

There are two components to the launch today: the Content API and the Data Store. I'll describe the Data Store first as it deserves not to get buried in the discussion about its larger cousin.

The Data Store

Simon Rogers is the Guardian news editor who is principally responsible for gathering data about the world. If you ever see an infographic in the paper, the chances are Simon had a hand in researching the data for it. His delicious feed is a positive gold mine.

As of today, a sizeable portion the data he collects for the newspaper will also be published online. As a starting point, we're publishing over 80 data sets, all using Google Spreadsheets which means it's all accessible through the Spreadsheets Data API.

Here's Simon's take on it, from Welcome to the Datablog:

Everyday we work with datasets from around the world. We have had to check this data and make sure it's the best we can get, from the most credible sources. But then it lives for the moment of the paper's publication and afterward disappears into a hard drive, rarely to emerge again before updating a year later.

So, together with its companion site, the Data Store – a directory of all the stats we post – we are opening up that data for everyone. Whenever we come across something interesting or relevant or useful, we'll post it up here and let you know what we're planning to do with it.

It's worth spending quite a while digging around the data. Most sets come with a full description, including where the data was sourced from. New data sets will be announced on the Datablog, which is cleverly subtitled "Facts are sacred".

The Content API

The Content API provides REST-ish access to over a million items of content, mostly from the last decade but with a few gems that are a little bit older. Various types of content are available - article is the most common, but you can grab information (though not necessarily content) about audio, video, galleries and more. You can retrieve 50 items at a time, and pagination is unlimited (provided you stay below the API's rate limit).

Articles are provided with their full body content, though this does not currently include any HTML tags (a known issue). It's a good idea to review our terms and conditions, but you should know that if you opt to republish our article bodies on your site we may ask you to include our ads alongside our content in the future.

We serve 15 minute HTTP cache headers, but you are allowed to store our content for up to 24 hours. You really, really don't want to store content for longer than that, as in addition to violating our T&Cs you might find yourself inadvertently publishing an article that has been retracted for legal reasons. UK libel laws can be pretty scary.

In addition to regular search, you can also filter our content using tags. Tags are a core aspect of the Guardian's R2 platform, being used for keywords, contributors, "series" (used to implement blogs), content types and more. Every item returned by the API includes tags, and the tags can be used to further filter the results.

We also return a list of filters at the bottom of each page of search results showing the tags that could be used to filter that result set, ordered by the number of results (you may have seen this feature referred to as faceted search or guided navigation). Handy tip: you can use ?count=0 in your search API key to turn off results entirely and just get back the filters section. The race is on to be first to release a tag relationship browser based on this feature.

API responses can be had in custom XML, JSON or Atom. The Atom format is the least mature at the moment, and we'd welcome suggestions for improving it from the community.

I released a Python client library for the API this morning, and we also have libraries for Ruby, Java and PHP.

We also have an API Explorer (written in JavaScript and jQuery, hosted on the same domain as the API so that it can make Ajax requests) but you'll need an API key to try it out.

The bad news

The response to the API release has been terrific (check out what Tom Watson had to say), but as a result it's likely that API key provisions will be significantly lower than the overall demand for them. Please bear with us while we work towards a more widely accessible release.

Tags: apis, atom, contentapi, data, data-journalism, datastore, guardian, javascript, journalism, jquery, json, openplatform, python, simon-rogers, tom-watson, xml

US economic data spreadsheets from the Guardian

2009-01-16T18:17:34+00:00

US economic data spreadsheets from the Guardian

At the Guardian we’ve just released a bunch of economic data about the US painstakingly collected by Simon Rogers, our top data journalist, as Google Docs spreadsheets. Get your data here.

Tags: data, economics, google-docs, simon-rogers, spreadsheets, the-guardian, usa

ficlets memorial

2009-01-14T22:02:42+00:00

ficlets memorial

Here’s a great argument for Creative Commons—AOL shut down Ficlets without providing an archive or export tool, but the license meant Ficlets co-creator Kevin Lawver could scrape and preserve all of the content anyway.

Tags: aol, archive, creativecommons, data, ficlets, kevin-lawver, preservation

Magic/Replace

2008-12-01T00:23:10+00:00

Magic/Replace

More inspirational magic from the team at Dabble DB. Be sure to watch the (short) demo video.

Tags: avi-bryant, cleanupdata, dabbledb, data, magicreplace

Code your own election mashup with Google's JSON data

2008-11-06T20:24:59+00:00

Code your own election mashup with Google's JSON data

The data that powered Google’s US election results map is available to download as a bunch of JSON files.

Tags: data, google, json, uselection

CKAN - Comprehensive Knowledge Archive Network

2008-07-05T15:24:37+00:00

CKAN - Comprehensive Knowledge Archive Network

Aims to be the “Debian of data”, with apt-get style tools for installing datasets. Presented at Open Tech 2008 by Rufus Pollock.

Tags: ckan, data, opentech, opentech2008, rufus-pollock

The Data Bill of Rights

2007-05-27T19:28:21+00:00

The Data Bill of Rights

John Battelle’s inherently sensible “draft of what rights we, as consumers, might demand from companies making hay off the data we create as we trip across the web”.

Via Mike Migurski

Tags: data, john-battelle