Meter data can't be shown by month

RDmitchell commented 3 years ago

Describe the bug ESPM meter data was imported, but when trying to view it aggregated by month the program shows "No Data". I believe this is because the start and stop times for any given interval span a month

I thought this was a bug, but in retrospect maybe this is a feature, ie, the data is already monthly but it would be nice to see it in a "different" monthly form.

Expected Behavior would like to see the data aggregated (correctly) by month, which means probably making the aggregation algorithm more sophisticated, ie, to aggregate only the days of the month for each month even when the interval spans a month.

Actual Behavior No data is displayed by month

Steps to Reproduce

Org: ID = 323
Cycle: 2018
In the inventory view for the cycle, filter by the label "Properties with Meter Data"
Then go to the detail view of PM Property ID: 3207429 and go to the Meter View; you will see electricity and natural gas meters, both of which have intervals that span more than one month.
Set the Interval to Month, and the program will display "No Data" -- even though the data is already monthly data, because it spans months, it would be nice to be able to see the data actually aggregated by month.
Setting the Interval to Year shows results

Instance Information

Instance: LBNL production SHA: 357ab7a3c

RDmitchell commented 3 years ago

In our discussion today, it came up that we were going to look into how ESPM aggregates this kind of data.

RDmitchell commented 3 years ago

@adrian-lara / @nllong / @axelstudios -- I think this is going to become a high priority issue when we connect to the BETTER analysis, because I think it is going to need to data in calendar months to map to the weather data that is also in calendar months.

yroussev commented 3 years ago

@RDmitchell this ESPM help-page shows how to access the the "calendarized" meter data in ESPM via UI or custom report. They currently have limitation on custom report "calendarized monthly export" functionality only available for Electricity and Natural Gas. ESPM programming team should be able to share with SEED team their calculation method.

https://energystar-mesa.force.com/PortfolioManager/s/article/Can-I-download-my-calendarized-meter-data-1600088547662

github-actions[bot] commented 3 years ago

This issue has been automatically marked as stale because it has not had recent activity within 60 days. It will be closed if no further activity occurs. Thank you for your contributions.

RDmitchell commented 3 years ago

@macintoshpie / @nllong -- I think we should really tackle this issue, maybe put it in the Dec 2021 project?

macintoshpie commented 3 years ago

@RDmitchell feel free to throw a priority label on this, I'm not sure what it should be. Then we can add it to the queue for next quarter. Have any users mention this issue? I do agree this is behavior is probably undesirable/unexpected

macintoshpie commented 3 years ago

So... I would like to the backend functionality for aggregating meters for BETTER analyses (see #2917 ). But due to this issue mentioned I can't use it -- if a property has valid monthly data that "straddles" months, then we get no monthly readings. This has to do with the algorithm we're using here (and I suspect we will have have the same issue with yearly aggregation though I haven;'t looked into it) https://github.com/SEED-platform/seed/blob/c0655c43cea0ee0abd0398a9485600a18e29c093/seed/utils/meters.py#L115

RDmitchell commented 3 years ago

In the past we have talked about finding out the methodology that Portfolio Manager uses to aggregate Monthly Data and use that method for SEED when there is data that straddles months.

@nllong -- can we put this on our list of things to do for 2022?

nllong commented 3 years ago

tagged with P-1 and we can prioritize FY22 tasks in the next week or so.

macintoshpie commented 3 years ago

I wanted to mention that I addressed my issue (specifically for running the BETTER analysis with some types of meter data) with a new approach for aggregating/binning meters in this PR: #2919

It's probably not the best approach or long term solution but wanted to point it out

nllong commented 3 years ago

I wanted to mention that I addressed my issue (specifically for running the BETTER analysis with some types of meter data) with a new approach for aggregating/binning meters in this PR: #2919

It's probably not the best approach or long term solution but wanted to point it out

I think the task is to now compare to the methodology that EPA uses in ESPM and probably with how BETTER is doing it. We should pin to the method that ESPM uses if there is a discrepancy.

macintoshpie commented 3 years ago

It sounds like we're going to set up a meeting to discuss their implementation, but in the meantime, here are some overview notes I found online. TLDR; ESPM uses a time-weighted approach:

Time-weighted allocation: apportion a given period’s consumption to a month based on the percentage of days in billing period that fall in that month

https://docs.google.com/presentation/d/1EXNndMweV6W4D7RLo20VTkcCWo83VqFT/edit#slide=id.p21

macintoshpie commented 3 years ago

We've received more information from ESPM, see page 6 figure 6 here: https://portfoliomanager.energystar.gov/pdf/reference/Climate%20and%20Weather.pdf

In short, data is apportioned to calendar months based on the average energy use per day. For example, if a bill runs from January 10 through February 9, it covers 31 days: 22 in January and 9 in February. Of the total bill, 22/31 (71%) is assigned to January and 9/31 (29%) is assigned to February.

This matches the algorithm we've implemented for BETTER meter preprocessing (see here) - though that implementation/usage is in flux at the moment.

perryr16 commented 2 years ago

Running off of Robin's example, and Ted's notes. Does the following seem like the correct approach? The first entry:

    {
        'start time': '2018-01-06 00:00:00',
        'end time': '2018-02-06 00:00:00',
        'electric reading': 44160
    },

There are 25 days in January and 6 days in February for a combined 31 days. 81% is in January and 19% is in February. Thus splitting the electric reading into parts:

35612.9 (81%) should be assigned to January
8547.1 (19%) should be assigned to February.

Then looking at the next electric entry,

    {
        'start time': '2018-02-06 00:00:00',
        'end time': '2018-03-08 00:00:00',
        'electric': 44320
    },

the same process should occur and accumulate for each month. 22 days in February (73%), 8 days in March (27%).

32501.33 (73%) to February + 8547.1 from first entry calculation
11818.67 (27%) to March + the next partial March electric reading

... and so on

Thoughts?

nllong commented 2 years ago

Yes that looks right! I wonder if there is something in TimeScaleDB that will handle this level of interpolation without us having to manually write it. I'd make sure to double check the documentation to see if there is a helper function...

Ryoken commented 2 years ago

Check out "analysis_pipelines/utils" and "calendarize_and_extrapolate_meter_readings", that may help out here!

RDmitchell commented 2 years ago

Instance: dev1 SHA: 9a3a23722 Org: JCC, ID 323

Now showing monthly data

SEED-platform / seed

Meter data can't be shown by month #2685