Retail price data for states?

[fredstaffordcs]

Hi folks,

EIA provides average retail price data by customer class for states, but as far as I can tell, this data doesn't live in PUDL. Should it?

The data is derived from Form 861's sales to ultimate customers data. That of course does live in PUDL, as core_eia861__yearly_sales. Unfortunately, because PUDL (understandably) strips out the state-level adjustments from that data, one cannot accurately reconstruct EIA's state-based average prices from it, which depends on those adjustments.

For example, this code should reconstruct the average retail price by state and class:

def calc_avg_price(x):
    # Include all data for revenue, as per Form 861 notes.
    revenue = x["sales_revenue"].sum()/1000
    
    # Exclude "Part C" for sales aggregation by state, as per Form 861 notes.
    sales = x[x["service_type"] != "delivery"]["sales_mwh"].sum()
    
    avg_price = (
        revenue / sales
        if sales != 0
        else np.nan
    )
    return pd.Series(
        [revenue, sales, avg_price],
        index=pd.Index(["revenue", "sales", "avg_price"]),
    )
    
(
    df
    .groupby(["state", "customer_class"])
    .apply(calc_avg_price)
)

But that gives me a 2023 average residential price for NY of 22.65 c/kWh, whereas the EIA data gives 22.24 c/kWh. Only by adding in those lost state adjustment rows to the revenue and sales can I get from the former to the latter.

In the end, I need to process the excel files or use the EIA Data API to analyze historical changes of retail prices. But should this data instead be derivable from PUDL? Is it already, and I'm just not seeing it?

Obligatory note of appreciation for all the work of PUDL contributors.

Best,
Fred

[zaneselvans]

I'm not sure if this will address your issue but we just finally integrated the records associated with "utility" IDs 88888 and 99999 last week. See #808 and #4291. 88888 is a catch-all for utilities whose identities have been redacted to protect proprietary business information, and 99999 is the state-level adjustments (for a variety of reasons). In the original data there are often many records with 88888 and we aggregate them together within each set of primary keys in the table. For 99999 I think there's 1 record for each distinct combination of state, year & customer class in the sales table.

So if you're not working with the most recent nightly build outputs you might want to try that and see if it gets closer to what you find from the other EIA data sources. And if it still doesn't (or you're already using the most recent nightly build) that would be interesting to know.

[fredstaffordcs]

Yes, perfect, that gets the numbers I was expecting. Thanks Zane.