EIA-861 charting - existing? or preferred data retrieval?

[Lalo-ATX]

Hi, I'm interested in a simple web app to select and chart EIA-861 sales & revenue data. I haven't found one, are y'all aware of any? If so, I may just use that and the rest of this question is moot.

If there isn't one, I'd like to code one. I'd use core_eia861__yearly_sales from PUDL. Then my second question is if y'all have a preferred method for me to pull the data. Normally I'd code the web app so that each user would retrieve the latest CSV or Parquet full data, locally cache it, and satisfy searching/filtering/reporting/charting out of that local copy. The cache would last as long as Cache-Control: max-age indicates, or whenever the Etag or If-Modified-Since triggers a fresh download.

Is that fine? Do you want me to cache for durations beyond what I wrote above? Do you prefer that I use the CSV or Parquet?

Or, do you want me to host my own copies of the PUDL data? This is less preferable to me, because then I need my own backend process to update my copy. I'd rather just code all this as a zero-maintenance web app.

[zaneselvans]

Hi @Lalo-ATX,

EIA has a dashboard interface for working with a bunch of their electricity data, if you're looking for something that already exists. You can select the EIA-861 sales data from one of the dropdowns.

If you want to build something more flexible, all of the PUDL data is available as a collection of Parquet files for programmatic use by the public through the AWS Open Data Registry, which provides us with a free S3 bucket at s3://pudl.catalyst.coop. For programmatic use, that's the best place to pull the data from. You can query it dynamically and efficiently pull only the subset of data you need with tools like pandas, polars, or DuckDB. Or read whole tables into memory all at once if you want, or download the files and cache them locally -- whatever works best for your application.

Note that there are multiple version of the data in that bucket. We have nightly builds that put fresh data under /nightly and versioned stable releases like /v2025.8.0 that come out at least once a quarter. /stable always points at the most recent versioned release. So for the sales table you mentioned, you'd find the most recent version at:

s3://pudl.catalyst.coop/nightly/core_eia861__yearly_sales.parquet

which you might e.g. access through DuckDB by doing:

SELECT * FROM
    's3://pudl.catalyst.coop/nightly/core_eia861__yearly_sales.parquet'
WHERE YEAR(report_date) >= 2023
LIMIT 10

utility_id_eia	state	report_date	balancing_authority_code_eia	customer_class	business_model	data_observed	entity_type	service_type	short_form	utility_name_eia	customers	sales_mwh	sales_revenue	data_maturity
55	MS	2023-01-01	TVA	commercial	retail	true	Municipal	bundled	NULL	City of Aberdeen - (MS)	730.0	28398.0	3487000.0	final
55	MS	2023-01-01	TVA	industrial	retail	true	Municipal	bundled	NULL	City of Aberdeen - (MS)	2.0	121028.0	6827000.0	final
55	MS	2023-01-01	TVA	other	retail	true	Municipal	bundled	NULL	City of Aberdeen - (MS)	NULL	NULL	NULL	final
55	MS	2023-01-01	TVA	residential	retail	true	Municipal	bundled	NULL	City of Aberdeen - (MS)	2571.0	32434.0	4003000.0	final
55	MS	2023-01-01	TVA	transportation	retail	true	Municipal	bundled	NULL	City of Aberdeen - (MS)	0.0	0.0	0.0	final
84	MD	2023-01-01	PJM	commercial	retail	true	Cooperative	bundled	NULL	A & N Electric Coop	42.0	531.0	74600.0	final
84	MD	2023-01-01	PJM	industrial	retail	true	Cooperative	bundled	NULL	A & N Electric Coop	0.0	0.0	0.0	final
84	MD	2023-01-01	PJM	other	retail	true	Cooperative	bundled	NULL	A & N Electric Coop	NULL	NULL	NULL	final
84	MD	2023-01-01	PJM	residential	retail	true	Cooperative	bundled	NULL	A & N Electric Coop	259.0	2058.0	285600.0	final
84	MD	2023-01-01	PJM	transportation	retail	true	Cooperative	bundled	NULL	A & N Electric Coop	0.0	0.0	0.0	final

The CSVs that are available through the PUDL Viewer aren't meant for programmatic use -- we primarily offer them so that spreadsheet users can still work with the data if they need to.

You can see other code snippets in the PUDL data dictionary. See also the more extensive data access documentation.

[zaneselvans]

If you need access through https:// rather than s3:// the same files are also available using URLs of the form:

https://s3.us-west-2.amazonaws.com/pudl.catalyst.coop/nightly/core_eia861__yearly_sales.parquet

[zaneselvans]

So long as we continue to have free access to the AWS Open Data Registry, it's actually better for us if you refer directly to the S3 bucket and don't cache data locally, since that gives us better usage metrics, so we can see which data is getting the most use, and from where. That helps us decide what data to prioritize for more frequent updates, and helps us demonstrate impact to grant funders.

[Lalo-ATX]

This is perfect, thank you so much. I'm glad I asked!

I don't love the EIA interface, but DuckDB as an interface may just scratch my itch, I'll have to try that out.

Thanks!