{"id":35194,"date":"2025-08-28T12:27:26","date_gmt":"2025-08-28T10:27:26","guid":{"rendered":"https:\/\/www.graviton.at\/letterswaplibrary\/queens-python-etl-api-for-making-energy-datasets-machine-readable\/"},"modified":"2025-08-28T12:27:26","modified_gmt":"2025-08-28T10:27:26","slug":"queens-python-etl-api-for-making-energy-datasets-machine-readable","status":"publish","type":"post","link":"https:\/\/www.graviton.at\/letterswaplibrary\/queens-python-etl-api-for-making-energy-datasets-machine-readable\/","title":{"rendered":"QUEENS: Python ETL + API For Making Energy Datasets Machine Readable"},"content":{"rendered":"<p><!-- SC_OFF --><\/p>\n<div class=\"md\">\n<p>Hi all.<\/p>\n<p>I\u2019ve open-sourced <strong>QUEENS<\/strong> (QUEryable ENergy National Statistics), a Python toolchain for converting official statistics released as multi-sheet Excel files into a tidy, queryable dataset with a small REST API.<\/p>\n<ul>\n<li><strong>What it is<\/strong>: an ETL + API in one package. It ingests spreadsheets, normalizes headers\/notes, reshapes to long format, writes to SQLite (<strong>RAW \u2192 PROD<\/strong> with versioning), and exposes a <strong>FastAPI<\/strong> for filtered queries. Exports to CSV\/Parquet\/XLSX are included.<\/li>\n<li><strong>Who it\u2019s for<\/strong>: anyone who works with national\/sectoral statistics that come as \u201chuman-first\u201d Excel (multiple sheets, awkward headers, footnotes, year-on-columns, etc.).<\/li>\n<li><strong>Batteries included<\/strong>: it ships with an adapter for the UK\u2019s <strong>DUKES<\/strong> (the official annual energy statistics compendium), but the design is <strong>collection-agnostic<\/strong>. You can point it at other national statistics by editing a few JSON configs and simple Excel \u201cmapping templates\u201d (no code changes required for many cases).<\/li>\n<\/ul>\n<p><strong>Key features<\/strong><\/p>\n<ul>\n<li>Robust Excel parsing (multi-sheet, inferred headers, optional transpose, note-tag removal).<\/li>\n<li>Schema validation &amp; type coercion; duplicate checks.<\/li>\n<li>SQLite with versioning (RAW \u2192 staged PROD).<\/li>\n<li><strong>API<\/strong>: <code>\/data\/{collection}<\/code> and <code>\/metadata\/{collection}<\/code> with typed filters (<code>eq, neq, lt, lte, gt, gte, like<\/code>) and cursor pagination.<\/li>\n<li><strong>CLI &amp; library<\/strong>: <code>queens ingest<\/code>, <code>queens stage<\/code>, <code>queens export<\/code>, or use <code>import queens as q<\/code>.<\/li>\n<\/ul>\n<p><strong>Install and CLI usage<\/strong><\/p>\n<pre><code>pip install queens # ingest selected tables queens ingest dukes --table 1.1 --table 6.1 # ingest all tables in dukes queens ingest dukes # stage a snapshot of the data queens stage dukes --as-of-date 2025-08-24 # launch the API service on localhost queens serve <\/code><\/pre>\n<p>Why this might help <a href=\"https:\/\/www.reddit.com\/r\/datasets\">r\/datasets<\/a><\/p>\n<ul>\n<li>Many official stats are published as Excel meant for people, not machines. QUEENS gives you a repeatable path to <strong>clean, typed, long-format data<\/strong> and a tiny API you can point tools at.<\/li>\n<li>The approach generalizes beyond UK energy: the parsing\/mapping layer is configurable, so you can adapt it to other national statistics that share the \u201cExcel + multi-sheet + odd headers\u201d pattern.<\/li>\n<\/ul>\n<p><strong>Links<\/strong><\/p>\n<ul>\n<li>PyPI: <a href=\"https:\/\/pypi.org\/project\/queens\/\"><code>https:\/\/pypi.org\/project\/queens\/<\/code><\/a><\/li>\n<li>GitHub (README, docs, examples): <a href=\"https:\/\/github.com\/alebgz-91\/queens\"><code>https:\/\/github.com\/alebgz-91\/queens<\/code><\/a><\/li>\n<\/ul>\n<p><strong>License<\/strong>: MIT<br \/> Happy to answer questions or help sketch an adapter for another dataset\/collection.<\/p>\n<\/div>\n<p><!-- SC_ON -->   submitted by   <a href=\"https:\/\/www.reddit.com\/user\/KaleidoscopeNo6551\"> \/u\/KaleidoscopeNo6551 <\/a> <br \/> <span><a href=\"https:\/\/www.reddit.com\/r\/datasets\/comments\/1n1bc0q\/queens_python_etl_api_for_making_energy_datasets\/\">[link]<\/a><\/span>   <span><a href=\"https:\/\/www.reddit.com\/r\/datasets\/comments\/1n1bc0q\/queens_python_etl_api_for_making_energy_datasets\/\">[comments]<\/a><\/span><\/p><div class='watch-action'><div class='watch-position align-right'><div class='action-like'><a class='lbg-style1 like-35194 jlk' href='javascript:void(0)' data-task='like' data-post_id='35194' data-nonce='65e0e39b87' rel='nofollow'><img class='wti-pixel' src='https:\/\/www.graviton.at\/letterswaplibrary\/wp-content\/plugins\/wti-like-post\/images\/pixel.gif' title='Like' \/><span class='lc-35194 lc'>0<\/span><\/a><\/div><\/div> <div class='status-35194 status align-right'><\/div><\/div><div class='wti-clear'><\/div>","protected":false},"excerpt":{"rendered":"<p>Hi all. I\u2019ve open-sourced QUEENS (QUEryable ENergy National Statistics), a Python toolchain for converting official statistics released&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[85],"tags":[],"class_list":["post-35194","post","type-post","status-publish","format-standard","hentry","category-datatards","wpcat-85-id"],"_links":{"self":[{"href":"https:\/\/www.graviton.at\/letterswaplibrary\/wp-json\/wp\/v2\/posts\/35194","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.graviton.at\/letterswaplibrary\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.graviton.at\/letterswaplibrary\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.graviton.at\/letterswaplibrary\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.graviton.at\/letterswaplibrary\/wp-json\/wp\/v2\/comments?post=35194"}],"version-history":[{"count":0,"href":"https:\/\/www.graviton.at\/letterswaplibrary\/wp-json\/wp\/v2\/posts\/35194\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.graviton.at\/letterswaplibrary\/wp-json\/wp\/v2\/media?parent=35194"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.graviton.at\/letterswaplibrary\/wp-json\/wp\/v2\/categories?post=35194"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.graviton.at\/letterswaplibrary\/wp-json\/wp\/v2\/tags?post=35194"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}