Troubleshoot building report card records

[danrademacher]

We’ve recently received some stakeholder feedback about bugs with the Building Reports in the Energy Benchmarking Map. Although we’re going to be overhauling the Building Reports with Stamen fairly soon, we need to resolve these bugs in the meantime. The recurring problem is that the fuel breakdown by GHG and KBtu in the report (the two horizontal bar charts near the top) is either not displaying or displaying incorrectly when complete benchmarking data is available. I’ve reviewed the data inputs from our data ecosystem (Carto_2022_Data_Extra) and they do not appear to be the source of the problem. I’ve attached some examples of the observed problems. Please take a look and let me know if you think these issues will be complicated to resolve.

ID	Problem	Reporting Year
21741	Building report is not displaying at all for any years	All
25414	Building report is saying "We don't have enough data to show fuel use" - but building is seemingly reporting complete data. Building is all electric. Building Report is not showing fuel breakdown for 2022, but did show for 2021.	2022
338	Building is reporting complete data… displaying in building report as incomplete data	2022
338	Building report is only displaying natural gas in fuel breakdown graphic - both electricity and gas present in upload file	2021
50455	Building is not displaying complete gas data in the fuel breakdown	2021
50455	Building report is saying "We don't have enough data to show fuel use" - but building is seemingly reporting complete data.	2022
20201	Showing only gas data in the fuel breakdown	2021
127	Building report is saying "We don't have enough data to show fuel use" - but building is seemingly reporting complete data.	2022
21364	Property has steam, electricity, and gas - is not displaying fuel breakdown correctly	2022

[tomay]

I looked at all of these and found the following

• Property 21741. There is no property_type assigned for 2021. All other year's have a property_type and the reports print without issue
• All of the others are correctly reporting, based on the values in electricity_pct, gas_pct and steam_pct. There are several related data issues:
  • Percents are not always correctly calculated from the raw inputs electric, gas and steam (there are a lot of blanks in the CSV that comes out of R where there should be values)
  • Percentages that should be decimals between 0-1 are represented as 0,1 integers in CARTO. This is true at least for the following fields: electricity_pct, gas_pct, steam_pct, electricity_ghg_percent, gas_ghg_percent, steam_ghg_percent, and all of these are used in the fuels chart (fueluse.js). This is not an issue between CSV and CARTO: I tested this yesterday with the output from R, and the decimals are correctly typed. So I'm not sure where the truncation occurred, but I suspect somewhere where Excel was used as an intermediary.

[tomay]

In the meantime, the simplest thing would probably be to just download all the Carto data, manually calculate the percentages in Excel, and then reupload to Carto.

If this is to be the fix, I would recommend calculating the percentages using CARTO SQL, since this is known to be the production data, and routing this through Excel again is problematic. (In addition, I see other issues in the raw CSV from R, it includes extra line returns for some property_types for example)

[tomay]

Link to latest raw CSV from R: https://drive.google.com/file/d/1tcvbihcYGs2JWTfpwMiGvlyQxXp8zwPv/view?usp=drive_link

Starting from this file, as there are many columns in production CARTO that are floats represented as integers

New percent calculations: electricity_pct = (electric 3.412) / ((gas 100) + (electric 3.412) + steam) gas_pct = (gas 100) / ((gas 100) + (electric 3.412) + steam) steam_pct = steam / ((gas 100) + (electric 3.412) + steam) electricity_ghg_percent = electricty_ghg / (gas_ghg + electric_ghg + steam_ghg) gas_ghg_percent = gas_ghg / (gas_ghg + electric_ghg + steam_ghg) steam_ghg_percent = steam_ghg / (gas_ghg + electric_ghg + steam_ghg)

Steps:

• [x] Open the above CSV in Sheets,(never mind see below, superseded with a new sheet)
• [x] Add the above formulas
• [x] Also fix any issues with extra line returns in property_type
• [x] Fix one missing property_type listed above
• [x] Check for NAs
• [ ] Upload as new table to CARTO
• [ ] Replace existing production table with this new one

Once that's done, confirm that the reports listed at the top of this issue are now correct

[tomay]

After new formulas, this includes additional 0's where previously there were blanks, but I don't think that's necessarily the issue here, as much as the truncation to 0/1

Unless buildings are reporting the raw electricity, gas, steam etc. values that go into the percentages, and they are somehow dropped/missing. But that would be upstream of this sheet, and I cannot fix that.

Update: Actually, for 2022 data, there were simply a lot of the percent calcs missing, for uknown reasons

[tomay]

Update, while I was working on this Mike sent a complete sheet with updated percentages. That's here: https://docs.google.com/spreadsheets/d/13Qz8187eB4S7PiSQj7yiAyyy-eSO3xtf_c8xIhsb5mk/edit#gid=708118130

We'll use this to update production data on CARTO

[tomay]

New table in CARTO is called "seattle_buildings_update_mr_20240425_final"

Planning to join in the new percents with SQL like this:

UPDATE seattle_buildings_2022_update 
SET electricity_pct = seattle_buildings_update_mr_20240425_final.electricity_pct
FROM seattle_buildings_update_mr_20240425_final
WHERE seattle_buildings_2022_update.unique_id = seattle_buildings_update_mr_20240425_final.unique_id

Unfortunately, this doesn't seem to work - probably because the existing field names are already case as integer

Looks like we have to replace the whole table.

[tomay]

Looking good:

[tomay]

Update:

Here’s the updated version of the file with the Electricity and Gas fields for 2015-2017 converted to kWh and Therms, respectively. The fuel breakdowns should be correct now for 2015-2017. Only the 2015-2017 files have changed.

Original XLS saved here: https://docs.google.com/spreadsheets/d/1Clm1Ust8qACUKcnQs_0ewKHWSKMx5z-o/edit?usp=drive_link&ouid=112218622749960089733&rtpof=true&sd=true

Exported to CSV (after fixing 21741 one more time), and uploaded to CARTO, file rename to replace the existing production table

[tomay]

Update: We traced the issue with decimal formatting back to Sheets work in 12/2023

Fixed in this file and uploaded 4/3/2024 to replace the production table on CARTO https://docs.google.com/spreadsheets/d/1k230hmEty7MtL_V1emy_5StOyl4xQUUShRTnSkbfPb0/edit#gid=1775896918

This has

• Fixed property_type for 21741
• Decimals formatted
• Energy percent formulas updated by MR

OSE is working on a full update of historic data that will supersede and replace this soon