california-data-collaborative / owrs-analysis Goto Github PK

Here is the 2015 survey report for reference: http://ca-nv-awwa.org/canv/downloads/2016/CANVRateSurvey2015.pdf

Additional analyses

• - John had good points about ET used in typical bill. Ideally that would be localized

• - Type of rate structure (similar to bill frequency)

• - Dashboard with hydrologic region as a selector

• - Group statistics by region (hydrologic and other)

• Rate structure by region

• Average volumetric / variable charge

• Average fixed charge

• - Use 15 ccf for default average usage

• - Use agency's R-GPCD to dynamically set water usage for rates. Could do a winter and summer bill plot. X axis price at typical water usage dynamically set by actual R-GPCD; Y axis is the amount over / under the efficiency target (also use water budget / goal)

• - Scatter plot of mandated percentage reduction against actually achieved reduction. Color based on yes / no response to drought rate changes.

• - Comparing to 2013 and 2015 results. Ideas on how to implement? Are people changing rate structures? Are people changing bimonthly to monthly? Are people adding drought rates?

• - Just for fun... these joy plots are super cool and would be great to integrate that style somehow: https://lsteely.shinyapps.io/streamflow_joyplots/

Will be good to keep discussing about which analyses are more amenable to being put in the report and which are more amenable to interactive dashboards on a website.

This one is lower priority, but it would be great to be able to accurately simulate changes in demand, e.g. from water conservation measures and look at the impact on revenue. We should be able to do this using the supplier report.

One approach would be to generate a synthetic population of households with representative water use based on the population and GPCD from the supplier report. Could use some sort of bayesian or monte-carlo method with prior assumptions for the distributions. Then imagine that all customers reduce waster use by 25%... etc

In addition to the OWRS files that give hard numbers to the utility rate structures, the 2017 Cal Nevada survey also asked more qualitative questions. Would be good to join these in, and it should be a pretty straightforward join on utility name.

One of the most interesting responses here is the "Percent of fixed costs". We can compare this to our OWRS-based analysis of the percent of fixed revenue to find those agencies who are the most susceptible to fluctuations in demand. Will share the data in Slack

Right now we are reading all OWRS file across years for a utility. This should change to only the most recent file.

There's a lot of great things going on this repo though it's a bit hard to follow. Would be good to clarify the ReadMe the:

• Linkage between OWRS data with supplier report. Is this correct_fuzzy_match.csv in the open_water_rate_data folder?
• Linkage between OWRS data with census socioeconomics.
• Where the analysis is occurring and protocol for contributing

CC @christophertull @victorsette @fernandomelchor

We would like to match the OWRS files (more specifically the dataframe of the OWRS utilities df_OWRS) with the utilities in the supplier_status_table. The names will probably not match exactly because of issues like spacing, plurals ("utility" vs. "utilities") and word order ("City of X" vs "X City of").

Because of this, we will need to do some sort of fuzzy matching to join the files. e.g. first pass can be regexes and stuff, but might need something fancier like string similarity. Can be in R to match the main body of the analysis but could also be in python since this step is sort of stand-alone. If in python could utilize the fuzzywuzzy package, not sure about comparable packages in R.

Right now units are being converted for kgal, but only AFTER calculating bills. Need to do this before calcing bills.

@victorsette what formula are you using here? https://github.com/California-Data-Collaborative/OWRS-Analysis/blob/rate_vs_efficiency/owrs_analysis_files/figure-html/unnamed-chunk-19-1.png

Also minor nit though would be helpful to have more descriptive file names than "unnamed-chunk-x.png" :)

After issue #6 we can then spatial join the utilities with census data. Could do something like average over blockgroups within the utility service area to get an idea of income and then could plot price of water vs. income. Do lower-income folks pay more or less per-unit for water?

We should join the OWRS utilities with their shapes so we can map them. Issue #5 will take care of some of this, but not all of the OWRS utilities are in the supplier report so the remaining utilities will need to be joined with table agency_polygons_for_survey_2017 using the utility_id key.

Utilize local population divided by number of customers rather than 4 per household in that analysis.

After #5 we can derive efficiency like this and then see if any attributes of the rates (total price, number of tiers, water budgets) correlate with higher efficiency.

From 4/19 CaDC meeting

e.g. https://github.com/California-Data-Collaborative/Open-Water-Rate-Specification/blob/master/full_utility_rates/California/Glenbrook%20Water%20Cooperative%20-%203299/1-1-2016.owrs