Using Inefficiency Data

[amandaaprahamian]

Hi all!

Thank you to @monobina for putting together the water inefficiency data. Now the fun part: finding meaning for the public within the pile of numbers. Let's break it down and find how we can give a sentence of data instead of csv's of data to the public:

In the TotalInefficiency.csv:

• Each line shows total inefficiency in CCF (hundred cubic feet) for one month for a certain census block geoid.
• The number in that "TotalIEUse" column is all the usage minus the water budget for that month. So the number in the column is total cubic feet over budget. Total over budget consumption is important; it shows how much overwatering is happening (either indoor or outdoor).
• An example use of this number could be "Last water year (July 2015-2016), your neighborhood used an average of 7,345 gallons of water above what it needed each month. The biggest usages were in April and June."
• If we use the data like in that sentence, we only need data from July 2015 - June 2016 (The csv goes back to 2011). We can use it in it's current format to find the biggest usage months and we can gather it by water year (@jgrewal has a code for determining water year if we end up calculating this way) to find average monthly overuse.
• For example, for geoid 60590320022003, we would 1) gather by the 15-16 water year (Aug 2015 - Jun 2016 because there's no July 2015 data*) and 2) average the "TotalIEUse" (last column, rows 2 - 12) then 3) convert to a public friendly unit of gallons.

• The sentence would be "Last water year (July 2015-2016), your neighborhood used an average of 9,657 gallons of water above what it needed each month. The biggest usages were in August and December and the lowest usages were in September and February".

Too High Numbers:

Picturing 9,657 gallons of water per month is a little difficult though. So maybe we only use the above data for highest and lowest usage months and we use the WeightedAverageEfficiency to get efficiency in a proportion instead of in gallons:

In the WeightedAverageEfficiency.csv:

• Each line shows a weighted average of the ratios of individual water use:budget for one month for a certain census block geoid.
• For example, if one individual in census block #125 used 3 ccfs and had a budget of 2 ccfs (150% usage:budget) and another individual in census block #125 used 7 ccfs and had a budget of 16 ccfs (<50% usage:budget), the weighted average proportion would be: [(3^2)/2 + (7^2)/2] / (3 +7) = 75%.
• An example use of this number could be "Your neighborhood typically uses 75% of its water budget"

End Product:

With both these csvs, I see us getting an end product like this: "Your neighborhood typically uses 97% of its water budget. The biggest usages in 2015-2016 water year were in August and January. The lowest usages were in September and February."

@datwater @monobina Please correct me if I'm pulling incorrect meaning from the data. What are some other sentences we could pull from this data too/instead?

Thank you!

[amandaaprahamian]

Using only Tier 3, 4, and 5.

(if we’re just using the TotalInefficiency.csv for highest and lowest usages, this comment can be ignored)

@monobina explained that there are 5 “Tiers”. Each individual is assigned to a Tier based on their water inefficiency (water use/budget). For example, if you’re < 70% (not inefficient) you’re in Teir 1 for that month. If you’re < 100% (not inefficient), you’re in Teir 2. If you’re between 100 and 115% (inefficient because you used more than in your budget) you’re in Tier 3, and so on.

In order to calculate the data for inefficient use (use that was above the budget), @monobina only used Teir 3-5 individuals in her calculations. Sometimes there was no Tier 3-5 individuals in a month because everyone used < 100% of their budget (in the original issue above, geoid 60590320022003 doesn’t have a 7th month in 2015 for this reason).

If we use this data to average and find “Last water year (July 2015-2016), your neighborhood used an average of 7,345 gallons of water above what it needed each month” do we need to add back in the Tiers 1 & 2 to get a correct net inefficiency?

[patwater]

No (unless I am misunderstanding). Tier 1 reflects a customer's efficient indoor needs and tier 2 reflects a customer's efficient outdoor needs so usage above that (Tiers 3-5) is inefficient. See here for a nice graphic: https://www.mnwd.com/understandingwaterbudget/

[amandaaprahamian]

So it looks like Tier 1 and Tier 2 are customers that remained within their water budgets for the month. Tier 3, 4, and 5 are customers that exceeded their water budgets for the month.

How, if at all, do we want to calculate "Last water year (July 2015-2016), your neighborhood used an average of X,XXX gallons of water above what it needed each month"?

For example, in geoid 60590320022003, all customers remained within their water budgets in 07/2015. I made up a number that said the whole census block was "-10" ccf under their budget for that month (grayed row). Averaging the inefficient use column with and without this Tier 1&2 data gives different outcomes:

• Without giving credit for the months where all customers were within their water budget, the average is 13 ccf above water budget.
• If we do include the 7/2015 month as a negative number, the average is 11 ccf.

Which would we rather do?

Including Tier 1&2 customer data would 1) give us data for missing months and 2) lower overall inefficient use numbers because those numbers would incorporate below-budget individuals too

Again, we may not want to include this metric and only include the weighted proportion of water use:budget. @monobina does the WeightedAverageEfficiency.csv incorporate data from all 5 Tiers? Or just 3-5?

[monobina]

@amandaaprahamian thanks for opening this issue and great points! My thinking is for the total value for usage over budget, it might be misleading to include Tier1 and Tier2 in there as those are still efficient and within budget usage. I can see that the total absolute value might be too large if the customers see it and why @amandaaprahamian you recommend using an average. If I take an average of inefficient usage in a month I would still take average of Tier3, 4 and 5 usage to say on average so much ccf or gallons usage happens over budget. It might be still useful to keep the total value (sum of tier3,4 and 5 by census block) as it might more reflective of the potential of water run-off. Open to more thoughts on this from the rest of the team @datwater @christophertull @patwater

[monobina]

@amandaaprahamian i like the end product description for the users. Maybe also add the total/average usage exceeding budget was xgallons in the past year or so?

[patwater]

@monobina I agree and think that the average amount over budget could be more relatable as 'neighbors in your community use 10,000 or whatever gallons per month more than is efficient' is more relatable than acre feet or a bazillion gallons. Also might be worth to translate that amount over budget into how many hours of outdoor watering or something tangible

[datwater]

I agree- we can take the total water used in Tiers 3, 4, and 5 and divide by the number of households (water meters) and provide the average by month of inefficient usage. I like the average efficiency too but I'm a bit of a data geek so it might not be the best data to show to the public. Might be a good tool for OC Stormwater though for the utility portal.

[monobina]

Thanks @datwater for your thoughts on this. I think this might be a good approach for calculating monthly average inefficient usage metric. Does everybody else agree with this?

[patwater]

Yup I agree! Would just also add that it'd be nice to have a statement like "X gallons of average amount of inefficiency or y minutes of taking a shower each month"

[amandaaprahamian]

@patwater I like that idea. A google search says that showers use around 2.5-3.0 gpm. With the example numbers from above that means 9,657 gallons of water is "between 3219.0 and 3862.8 minutes of taking a shower each month". We could round it to "about 3500 minutes", but that's still a pretty high number. Changing it to hours ("about 60 hours") or days ("about 2.5 days") might be easier to digest, numbers wise. What do you think?

[patwater]

Looks good to me! Would be curious to the MNWD folks thoughts to make sure that aligns with their on the ground intuition. @monobina ?

[amandaaprahamian]

Here's what I gather from above. The sentences regarding water overuse will be:

"Your neighborhood typically uses 97% of its water budget. Last water year (July 2015-2016), your neighborhood used an average of 9,657 gallons of water per month above what it needed. That’s equivalent to about 3.2 days of taking a shower! The biggest usages were in August and December and the lowest usages were in September and February."

I disagree with the approach discussed above for calculating the bolded calculations in those sentences. I believe we should include all Tiers, which would be usage data from all customers regardless of whether they were within a month's budget or not. Here’s my reasoning:

• Overuse varies by month/season. We can’t just report on one month, because that wouldn’t represent the rest of the months.
• To get a clear view of overuse, we therefore change to a yearly scale. I would like to know over the course of a year, does the census block have a net overuse or a net underuse of water on the yearly scale.
• This yearly scale should add up all usage and all budgets for the whole water year.
• This gives us one large usage number and one large budget number to see how a year’s usage compares to a year’s budget.
• The difference between a year’s use and a year’s budget (the net overuse or underuse) would be too large of a number (for example, 115,884 gallons), so we therefore translate it into a monthly number to be easier to digest.
• Then we report it as 115,884/12 months = 9,657 gallons of water per month of overuse.

Because I want an average on a yearly scale, we should use a yearly budget and a yearly usage that doesn’t discriminate based on whether the usage was within a monthly budget.

Walking through my reasoning, I think the sentence should be tweaked to explain exactly what we calculated. I'm thinking that switching it from “gallons of water above what is needed each month” to “gallons of water per month above what is needed” would work. I included this change in the paragraph in this comment.

In a forum format it's a bit difficult to discuss which calculations to do and why, so if we set up a meeting I might better understand nuances in the data and our analysis. Please let me know your thoughts. I am available next week for a meeting.

[monobina]

@amandaaprahamian yeah lets talk offline on this. I have sent you an email to set up a time for a call next week. Anybody else who wants to join the call are most welcome! @johnathancruz and I will be on the call from MNWD

[patwater]

@monobina @amandaaprahamian @leighphan any updates or anything you need on our end?

[monobina]

@patwater we have shared the data with @amandaaprahamian recently. It should have all the requested variables. @amandaaprahamian let us know if you need anything else.

[amandaaprahamian]

@patwater @monobina @leighphan The data has been compiled! Next step is working on how we want to present it. Please check out this box.com document and add edits/comments.

[patwater]

Looking good! Is there an eta on launching a demo on the web?

[monobina]

The final data for the public facing app is uploaded in the Data folder "IEUsage_Rebate2". "Data dictionary_Stormwater" doc file is added to help interpret the data :-)