Optimization by Radius


The goal of this analysis was to determine the least-cost way to supply county's water needs, at various radii. In particular:

  • When all other counties are held constant.
  • When all counties within state are allowed to change.
  • When all counties within country are allowed to change.

The county-level optimization is exactly the USGS (or other baseline water withdrawal) dataset, since this is what would be applied as demands that would need to be met.

The national-level optimization is the optimize-waterallocation.jl script, for the same reason.

The state-level optimization takes as its starting point the optimize-waterallocation.jl constraints, but adds on a constraint that rivers that flow across state boundaries maintain the values they have under optimize-surface.jl.

analyses/optradii/holdstate.jl performs this optimization and compares the results.


In [1]:
using DataFrames, RegressionTables


INFO: Recompiling stale cache file /Users/jrising/.julia/lib/v0.6/DataFrames.ji for module DataFrames.
INFO: Recompiling stale cache file /Users/jrising/.julia/lib/v0.6/RegressionTables.ji for module RegressionTables.

In [2]:
awwa = readtable("../../prepare/costs/awwadata.csv")


Out[2]:
xCountyWaterstatsIDUtilityTypeUtilityNameAWWAGroupCategoryRegionAWWA_CityState_NameStateZipCountryFIPSCityPopDensMedianIncomeGroundSurfacePurchasesPrimarySourceSplitPercentResidentialRateStrRateStructSeasonalResCharges1000cfServiceResCharges1000cfVolumeResCharges1000cfTotalResCharges1500cfServiceResCharges1500cfVolumeResCharges1500cfTotalFixedChargeRatioWWCharges1000cfServiceWWCharges1000cfVolumeWWCharges1000cfTotalWWCharges1500cfServiceWWCharges1500cfVolumeWWCharges1500cfTotalCombResCharges1000cfServiceCombResCharges1000cfVolumeCombResCharges1000cfTotalCombResCharges1500cfServiceCombResCharges1500cfVolumeCombResCharges1500cfTotalMGDailyWaterTreatmntProdCapacityMGDAverageDayWaterProductionMGDMaxDayWaterProductionAvgPlantUtilizationMaxPlantUtilizationAnnualMGallonsSoldResNonresAnnualMGallonsSoldWholesaleAnnualMGallonsSoldTotalSvcPopRetailSvcPopWholesaleSvcPopTotalX_xScvPopDailyPerCapitaConsumptionGallonsActiveAccountResidentialActiveAccountNonResAccountRatio_NonResActiveAccountWholesaleActiveAccountTotalBSFullTimeEmployeesMGYWWTotalGallonsTreatedWaterPerEmployeeBSTotalAssetsBSLongtermDebtDebtRatioBSTotalAssetsCombBSLongTermDebtCombDebtRatioCombISOperatingRevenuesISBeforeDepreciationIncomeStatement_Depreciation_ExpenseOpExpRevISInterestExpenseISNetIncomeDebtPerMGOpExpPerMGNonOperatingRevenueNonOperatingIncomeISOperatingRevenuesCombISBeforeDepreciationCombOpExpRevCombISInterestExpenseCombISNetIncomeCombAverageCapitalNeedsIncomeStatement_W_C_WWWaterIS_PeriodWastewaterIS_PeriodUtilityType_1WaterOnlyTempAveTempVarPrecipAnnualPrecipSeasonalMaxCumDefNDIX_ymeandepth
11Ada200224City of BoiseXUWestBoiseIdahoID83701-0500USA16001Boise325.8554930.00.00.0NANANANA0000.0000#DIV/0!4.5625.930.464.5638.8543.41NANA30.464.5638.8543.410.00.00.00.00%0.00%0.00.00.0000.000.0#DIV/0!00#DIV/0!000.09171.0#DIV/0!00#DIV/0!304,337,000.0020,371,000.000.070.000#DIV/0!00#DIV/0!#DIV/0!0025,174,000.0021,381,000.000.85474,000.001.952e6#DIV/0!NAN / RFY094010.2712.89320.794.130.32560589518.1290908331465.2669511164.528134
22Adams16092Aurora WaterBUWestAuroraColoradoCO80012USA8001Aurora346.6608220.0100.00.012IB2012.0639.4251.4812.0659.1371.190.22.9720.4223.392.9730.6333.615.0359.8474.8715.0389.76104.79160.040.4180.025.26%50.00%14749.650.014749.653140000314000.01314000.0128.69426757281139990.05492302076810268.00.055.036007461,700,272,392.00701,818,920.000.412,202,062,415.00765,875,228.000.358.4493197e733,252,590.00103908600.396,527,282.0062,555,533.0047,582.072,254.4701131,056,160.0067,078,926.000.516,634,481.008.3449478e770,687,288.80Water onlyCY09CY09219.8813.023745.250.946505845.97714294851866.2198051494.320894
33Alachua10192Gainesville Regional UtilitiesBUSouthGainesvilleFloridaFL32614USA12001Gainesville245.036833100.00.00.022IB207.312.3419.647.322.1829.480.336.037.9243.926.056.8962.8913.350.2663.5613.379.0792.3754.024.95731.80146.22%58.89%7708.00.07708.01244910124491.00124491.0169.63321226185176450.12360349906949671.05620.0108.5633803179,640,510.00108,415,016.000.6394,617,821.00230,178,472.000.582.5712256e712,590,285.0065432050.4905,179,080.0014,065.261,633.401157,688,361.0025,264,781.000.44561,085.001.0288549e75,900,862.80Water onlyCY09FY092120.786.631,318.752.770.0278175610.455845935133027.06252198.633167
44Alameda13372East Bay Municipal Utility DistrictAUWestOaklandCaliforniaCA94623USA6001Oakland2040.3523840.0100.00.012DB3012.0321.4433.4712.0333.8445.870.3611.155.917.0511.155.917.0523.1827.3450.5223.1839.7462.92181.0181.0262.0100.00%144.75%61962.40.061962.4130000001.3e601.3e6130.5846154356173255550.07174884103817281783.024090.034.751766693,709,906,000.002,210,846,000.000.64,374,434,000.002,571,183,000.000.592.91631e8227,493,000.00685180000.7807,262,000.0035,680.443,671.4701360,723,000.00277,633,000.000.7713,509,000.005.851e6177,800,000.00Water onlyFY09FY092114.476.12468.0556.7846.591370360.809378725151523.785408858.5948757
55Alameda12831Alameda County Water DistrictBUWestFremontCaliforniaCA94538USA6001Fremont2040.310015034.041.025.03NAU105.8127.5433.355.8141.3147.120.140.00.00.00.00.00.05.8127.5433.355.8141.3147.1275.050.075.066.67%100.00%15621.620.015621.623336480333648.01333648.0128.27578447301267750.092792966079787214.00.072.9982243467,620,400.0050,844,200.000.11467,620,400.0050,844,200.000.115.67777e746,199,600.00107958000.812,846,600.006,792,500.003,254.732,957.411156,777,700.0046,199,600.000.812,846,600.006.7925e622,618,200.00Water onlyFY09N / R1114.476.12468.0556.7846.591370360.809378725151523.785408858.5948757
66Albemarle21682Albemarle County Service AuthorityCUSouthCharlottesvilleVirginiaVA22911USA51003Waynesboro127.4405510.00.0100.04NAIB206.1544.6250.776.1589.2495.390.070.053.9353.930.080.980.96.1598.55104.76.15170.14176.290.00.00.00.00%0.00%1545.70.01545.762518062518.0162518.067.737204011566213120.08376963401697438.00.040.6763157961,594,162.0000108,336,426.00364,100.0001.0257885e78,786,314.0012968120.8603,428,498.0005,684.360118,964,507.0015,805,202.000.8321,010.006.772006e63,997,090.00Water onlyFY09FY092113.3311.081,123.401.350.00382425810.37407846181641.3751084.430981
77Allegheny20871Fox Chapel AuthorityCUNortheastPittsburgh (FCA)PennsylvaniaPA15238USA42003Pittsburgh1692.3373880.00.0100.04NADB304.5453.3357.874.548084.540.060.00.00.00.00.00.04.5453.3357.874.548084.540.01.820.00.00%0.00%467.19387.729554.92218000130019300.0119300.078.7737951651782430.0469293162542313.00.042.6863076927,444,428.0011,024,794.000.427,444,428.0011,024,794.000.44.301948e62,594,875.005454480.6537,679.00630,543.0019,867.294,676.11104,301,948.002,594,875.000.6537,679.00630543.0750,000.00Water onlyFY09N / R1110.6311.8987.811.5219.92541983241.9507224281287.677419459.8801384
88Allen14082Lima Department of UtilitiesCUMidwestLimaOhioOH45801USA39003Lima266.6353980.0100.00.012DB307.8613.8221.687.8620.7328.590.3817.8717.7135.5817.8730.3648.2325.7331.5357.2625.7351.0976.8230.013.518.045.00%60.00%4015.02357.96372.972000300075000.0175000.0232.82213019290.08716674212406048.799999244441.29130.592215234,744,643.003,341,854.000.177,969,109.0013,684,719.000.189.365747e68,116,866.0011333940.87287,673.00184,502.00524.391,273.651017,862,416.0013,597,543.000.761,048,863.00989326.010,230,266.00Water onlyFY08FY082110.3912.64927.771.630.53521008579.85310308291285.464217656.343153
99Anderson300212Oak Ridge, City ofCUSouthOak RidgeTennesseeTN37831-0001USA47001Oak Ridge211.4548620.0100.00.012DB3011.524.9336.4311.541.3452.840.2813.528.2241.7213.548.4461.942553.1578.152589.78114.7828.010.013.035.71%46.43%1074.02174.03248.027500027500.010.0323.58655041129316250.1438944481129190.01973.7#DIV/0!00#DIV/0!00#DIV/0!0.000#DIV/0!00000000#DIV/0!00.01,770,000.00NAN / RN / R2013.4510.631,399.021.490.00551149213.5547028936798.1153846494.0790067
1010Anne Arundel300062City of AnnapolisCUSouthAnnapolisMarylandMD21401USA24003Annapolis1246.864400100.00.00.022DB304.211.3415.544.218.6322.830.236.0717.4123.486.0726.9433.0110.2728.7539.0210.2745.5755.8410.03.916.339.10%63.00%1186.00.01186.038900038900.0038900.083.529950351047419390.18512507201241322.01790.053.9090909118,884,860.003,254,453.000.1744,424,758.007,477,401.000.173.250584e64,736,853.005540741.46212,320.00-1,816,385.002,744.063,993.97108,198,446.009,122,853.001.11377,027.00-293936.010,872,600.00Water onlyCY09CY09219.98.85967.61.310.7113229324.596156945391712.1428571078.942619
1111Atchison42311City of AtchisonCUMidwestAtchisonKansasKS66002USA20005Atchison37.8406840.0100.00.012DB3023.3821.9945.3723.3836.4959.870.640.00.00.00.00.00.023.3821.9945.3723.3836.4959.878.63.7545.67843.65%66.02%840.396188.5941028.9910402265013052.0113052.0215.993769937064960.133837021542075.50.0187.089090911,358,405.006,390,072.000.5611,358,405.006,390,072.000.562.733628e61,394,251.004791100.51352,394.001,383,936.006,210.041,354.97012,733,628.001,394,251.000.51352,394.001.383936e6210,411.00Water onlyCY09N / R1112.1913.42923.554.350.2409483681.242559969651099.280899685.1325214
1212Baltimore11682City of Baltimore Department of Public WorksAUSouthBaltimoreMarylandMD21202USA24005Baltimore1300.3393360.0100.00.012DB308.2316.4624.698.2328.8137.040.2911.0622.1333.1911.0638.7249.7819.2938.5957.8819.2967.5386.82360.0226.00.062.78%0.00%25769.037195.062964.0180000001.8e601.8e695.83561644193881004193885893.065000.070.508398661,007,440,000.00454,940,000.000.452,761,248,000.001,102,175,000.000.41.1984e894,547,000.00118880000.7910,434,000.003,364,000.007,225.401,501.6000119,840,000.00215,670,000.001.820,462,000.008.956e674,093,820.80Water onlyFY09N / R2111.4710.91,106.571.311.936986763194.8737825851343.935696712.3397972
1313Bartow25531Cartersville Water DepartmentCUSouthCartersvilleGeorgiaGA30120USA13015Cartersville196.2538320.0100.00.012IB2061218.0626.9432.940.220.00.00.00.00.00.061218.0626.9432.9427.011.0214.0640.81%52.07%2363.01701.04064.0193827681896200.0096200.0115.7406089903614010.15504648111043821.00.0193.523809500#DIV/0!00#DIV/0!1.142e710,129,400.0000.890329,600.0002,492.471011,420,000.0010,129,400.000.890329600.012,039,250.00Combined water and wastewaterFY09N / R1115.5910.031,305.981.410.0068297474.3451740211012453.0337.4033195
1414Bay23901City of Panama CityCUSouthPanama CityFloridaFL32405USA12005Panama City208.3412900.00.0100.04NAU106.418.5524.956.427.8334.230.230.00.00.00.00.00.06.418.5524.956.427.8334.230.00.00.00.00%0.00%1988.1750.01988.17538000038000.0038000.0143.34354721377832270.23421396401700589.50.022.2142458100#DIV/0!00#DIV/0!1.4736091e79,689,123.0020103570.66829,960.001,344,651.0004,873.381014,736,091.009,689,123.000.66829,960.001.344651e61,157,000.00Combined water and wastewaterFY09N / R1114.745.771,391.781.930.0152102781.2060900661053947.7190083598.653966
1515Bennington39051City of BenningtonCUNortheastBenningtonVermontVT5201USA50003Bennington54.6440815.095.00.011U1044.8720.6465.5144.8730.9775.841.450.00.00.00.00.00.044.8720.6465.5144.8730.9775.844.01.52.837.50%70.00%538.07.2545.213000113001.0113001.0114.8910569280010000.357142857138016.00.090.866666678,949,679.006,455,608.000.728,949,679.006,455,608.000.721.385168e61,457,974.004113491.050-484,155.0011,840.812,674.20001,385,168.001,457,974.001.050-484155.01,000,000.00Water onlyFY09N / R115.7412.91,329.051.160.0011658198.08999994125945.6666667338.098112
1616Benton15473Benton Washington Reg. Public Water Auth.CUSouthRogersArkansasAR72756-9088USA5007Rogers206.6529320.0100.00.012NANA0000.0000#DIV/0!0.00.00.00.00.00.0000.0000.016.07.0116.043.81%100.00%0.02260.7382260.7380100540100540.010.061.6053584600#DIV/0!171716.00.0141.29612568,576,213.0049,689,705.000.7268,576,213.0049,689,705.000.724.625323e61,794,047.0012178600.391,195,252.00642,431.0021,979.42793.57004,625,323.001,794,047.000.391,195,252.00642431.01,350,000.00Water onlyN / RN / R3014.4811.411,139.001.930.0130211760.448504031282073.1080521683.007876
1717Benton36602City of CorvallisCUWestCorvallisOregonOR97330USA41003Corvallis121.3460820.0100.00.012IDB4012.1512.4324.5812.1523.9836.130.519.8829.439.289.8844.153.9822.0341.8363.8622.0368.0890.1124.07.116.229.58%67.50%2529.40.02529.455125055125.0155125.0125.71180071341511650.08684308601458032.909999853399.7376.858098251,315,434.004,625,080.000.09131,933,109.0023,091,674.000.188.786133e68,051,351.0016358520.92292,358.00-487,556.001,828.533,183.110118,083,566.0016,253,144.000.91,241,476.00-1.710462e61,263,422.00Water onlyFY09FY092110.437.471,699.1313.140.012818761.1547513261282073.1080521683.007876
1818Benton35942Kennewick, City ofCUWestKennewickWashingtonWA99336USA53005Kennewick90.05389941.2858.720.03NAU109.3210.5719.899.3215.8625.180.5919.240.019.2419.240.019.2428.5610.5739.1328.5615.8644.4230.010.824.0636.00%80.20%3673.760.03673.7669454069454.0069454.0144.91744021904321200.11132699702116317.51993.78209.9291429138,888,272.0033,997,148.000.24138,888,272.0033,997,148.000.248.245542e65,288,636.0023812580.640753,095.009,254.051,439.571114,932,355.009,196,804.000.6202.178827e63,943,800.00Water onlyN / RFY092111.0911.7215.244.60.380828623888.21738311282073.1080521683.007876
1919Berkeley22202Berkeley Water & SanitationCUSouthBerkeley CountySouth CarolinaSC29461USA45015Berkeley122.1521920.00.0100.04NAU108.522.9631.468.543.4451.940.244.00.044.044.00.044.052.522.9675.4652.543.4495.940.00.00.00.00%0.00%1715.5812590.01715.58125950245050245.0150245.093.54607677175137580.04328213321827351.299999244357.033.4421303115,155,876.9556,292,833.790.49393,236,182.93201,870,434.910.518.3142264e65,280,130.6725822610.6490,975.001,261,355.5632,812.693,077.750033,591,364.8616,006,680.000.487,917,295.006.44667398e63,671,973.20Water onlyFY09FY092116.958.561,161.202.10.0113052418.700094251301517.381481787.8887881
2020Bernalillo13262Albuquerque Bernalillo County Water Utility AuthorityBUWestAlbuquerqueNew MexicoNM87120USA35001Albuquerque517.85005275.025.00.031U1111.4114.125.5111.4121.1532.560.547.497.8115.37.4911.7119.218.921.9140.8118.932.8651.76294.087.51150.029.77%51.02%2716.40.02716.45950000595000.000.012.50788535171983198300.1153020940191813210.021170.012.93523811,434,211,198.00617,193,912.000.431,434,211,198.00617,193,912.000.431.52232345e8140,185,811.003934100.92019,679,654.00227,210.2551,607.2010152,232,345.00280,371,622.001.8403.9359308e7#DIV/0!Combined water and wastewaterFY08FY082011.3311.65278.733.187.40695646913.249365981331426.9052631452.422101
2121Bexar15262San Antonio Water SystemAUSouthSan AntonioTexasTX78298USA48029San Antonio1216.64736194.830.05.1721IB216.9619.626.566.9630.937.860.237.8112.3120.127.8120.027.8114.7731.9146.6814.7750.965.67826.0181.4243.021.96%29.42%55176.0119.055295.0134054901.340549e601.340549e6113.0082904327610232420.070944117350859645.051987.085.728682172,131,876,349.001,064,883,922.000.53,777,231,489.001,846,000,356.000.492.21512601e8133,747,390.00470192680.639,001,302.00-2,350,804.0019,258.232,418.8011356,339,136.00209,894,831.000.5966,285,409.00-6.463547e6151,720,000.00Water onlyCY09CY092120.288.53769.172.0917.8359469111.510352361366421.4531.8959485
2222Black Hawk10751Waterloo Water WorksCUMidwestWaterlooIowaIA50704USA19013Waterloo224.544586100.00.00.022DB305.257.9113.165.2513.5618.810.390.00.00.00.00.00.05.257.9113.165.2513.5618.8150.012.817.5825.60%35.16%3720.18104.033824.2170384316973553.0173553.0142.44541582408617320.07190899322582036.00.0106.228055625,505,042.002,105,824.000.0825,505,042.002,105,824.000.085.719686e64,587,738.004934970.8108,696.001,190,531.00550.661,199.66115,719,686.004,587,738.000.8108,696.001.190531e62,564,106.60Water onlyCY09N / R118.2414.4863.794.31.29878708112.722014051431816.6758241119.17127
2323Blount22992Maryville Utilities, City ofCUSouthMaryvilleTennesseeTN37801USA47009Maryville199.2524990.099.940.0612DB305.6716.1121.785.6725.4531.120.225.0728.233.275.0742.347.3710.7444.3155.0510.7467.7578.496.03.9745.65366.23%94.22%1084.59750.01084.597530374030374.010.097.830381251290315570.12066961201446031.00.034.98701613103,294,420.0051,240,000.000.5103,294,420.0051,240,000.000.51.1372336e77,736,229.0016276250.682,378,155.00105,235.0047,243.337,132.810011,372,336.0015,472,458.001.364,756,310.00210470.01,651,657.40Combined water and wastewaterFY09FY092013.6510.321,379.051.470.007535285126.8458681501436.238095817.4877471
2424Boulder16102Boulder, City ofCUWestBoulderColoradoCO80302USA8013Boulder396.1585220.00.0100.04NAIB208.5518.4426.998.5539.6248.170.220.9628.4229.380.9642.6443.69.5146.8656.379.5182.2691.7753.015.4130.529.08%57.55%4987.00.04987.01158810115881.01115881.0117.90555572504234160.1364108302845875.129997254915.066.37828008236,489,000.0045,360,000.000.19342,655,000.0086,597,000.000.252.0173e711,197,000.0049890000.562,114,000.00862,000.009,095.652,245.240132,894,000.0018,943,000.000.583,782,000.001.302e64,820,091.60Water onlyCY09CY09215.0911.9556.732.110.0641403551.675958612164999.4566745852.4835878
2525Boulder16162Longmont, City ofCUWestLongmontColoradoCO80501USA8013Longmont396.1669260.0100.00.012IB203.664.037.693.666.9410.60.537.2917.2224.517.2927.0634.3510.9521.2532.210.953444.9547.013.6629.4929.06%62.74%4245.80.04245.886303086303.0186303.0134.78475572355329880.1268628202654152.34999847967.2581.10410934332,354,156.0015,398,953.000.05427,672,924.0016,017,826.000.041.202335e79,398,073.0038474420.780-2,818,585.003,626.872,213.501019,439,389.0015,467,067.000.8268,225.00-4.121233e66,945,578.80Water onlyCY09CY09215.0911.9556.732.110.0641403551.675958612164999.4566745852.4835878
2626Boulder43592Lafayette Water DepartmentCUWestLafayetteColoradoCO80026USA8013Lafayette396.1737290.0100.00.012IB209.1514.4323.589.1524.9534.10.378.316.4114.728.3111.4119.7217.4620.8438.317.4636.3653.8213.03.78.228.46%63.08%1159.8629.3631189.2233200080032800.0132800.099.3336952976715320.0693521052820521.10000038741.056.361278698,136,454.0017,402,741.000.18126,487,753.0023,273,971.000.184.828996e62,813,144.0016461090.58890,948.001,073,177.0014,633.712,365.53016,482,405.004,429,035.000.681,112,575.00251207.03,344,600.00Water onlyFY09FY09215.0911.9556.732.110.0641403551.675958612164999.4566745852.4835878
2727Branch73552Coldwater Board of Public UtilitiesCUMidwestColdwaterMichiganMI7355USA26023Coldwater91.744352100.00.00.022DB3012.3515.2627.6112.3523.9836.330.5214.5911.3425.9314.5917.8232.4126.9426.653.5426.9441.868.746.61.9193.5929.08%54.39%492.580.0492.5812700012700.0012700.0106.262539138757190.185548387045947.0917.170.3685714312,355,678.005,810,000.000.4727,896,206.0011,925,000.000.432.116761e61,200,967.003996710.57248,700.00341,098.0011,795.042,438.12004,571,795.002,635,390.000.58526,010.00533557.0371,000.00Water onlyFY09FY09219.0312.86910.121.770.2805953011.783249152175928.5458.912301
2828Brevard16402City of CocoaBUSouthCocoaFloridaFL32922USA12009Cocoa501.93539280.020.00.02NAIB2010.4714.725.1710.4726.0136.480.49.7732.4142.189.7750.9360.720.2447.1167.3520.2476.9497.1848.025.3534.4552.81%71.77%8360.5140.08360.5142450000245000.010.093.491909427641000076410143.5643.6658.2614216208,056,605.0078,185,401.000.38208,056,605.0078,185,401.000.384.1132721e732,022,078.0075509570.783,220,301.005,897,698.009,351.753,830.161141,132,721.0064,044,156.001.566,440,602.001.1795396e713,912,815.40Combined water and wastewaterFY09FY092015.343.661,211.343.180.080295675.4386149911821403.2222222505.831238
2929Brevard10272Melbourne Water Department, City ofCUSouthMelbourneFloridaFL32901USA12009Melbourne501.94521230.070.00.01NANA406.2528.534.756.2543.1249.370.148.1843.851.988.1866.374.4814.4372.386.7314.43109.42123.8526.514.918.956.23%71.32%0.00.00.015000015328165328.010.005171949330.095380808455669780.02151.7590213,153,688.00118,629,993.000.56213,153,688.00118,629,993.000.564.057917e733,164,016.0093791030.824,936,601.00-5,316,559.00#DIV/0!#DIV/0!0140,579,170.0066,329,032.001.639,873,202.00-1.0634118e7531,760.00Combined water and wastewaterFY09FY092015.343.661,211.343.180.080295675.4386149911821403.2222222505.831238
3030Broward10372Broward County Water and Wastewater ServicesBUSouthBroward CountyFloridaFL33069USA12011Pompano Beach1449.55452476.070.023.932NAIB2012.1420.5532.6912.1438.6650.80.3115.4322.2937.7215.4333.4448.8727.5742.8470.4127.5772.199.6746.020.2922.7544.11%49.46%7182.151819.329001.4723000061000291000.010.084.747634515202561770.118731379358205349.023793.025.79217765809,182,431.00425,139,830.000.53809,182,431.00425,139,830.000.531.08085468e864,788,684.00331202850.615,814,160.001,427,215.0047,230.047,197.5701108,085,468.00129,577,368.001.231,628,320.002.85443e640,299,934.00Combined water and wastewaterFY09FY092021.593.811,473.083.80.6033864144.01E-061922835.6984133404.365641
&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip&vellip

In [3]:
awwa[:costs] = convert(DataVector{Float64}, map(x -> isna(x) || x == "#DIV/0!" ? NA : parse(Float64, replace(x, ",", "")), awwa[:ISBeforeDepreciation]));
awwa[:SurfaceMG] = awwa[:Surface] .* awwa[:AnnualMGallonsSoldTotal] / 100
awwa[:GroundMG] = awwa[:Ground] .* awwa[:AnnualMGallonsSoldTotal] / 100
#awwa[:Purchases] = map(x -> isna(x) ? NA : (v = tryparse(Float64, x); isnull(v) ? NA : get(v)), awwa[:Purchases])
awwa[:PurchasesMG] = awwa[:Purchases] .* awwa[:AnnualMGallonsSoldTotal] / 100;

In [4]:
mean(awwa[:depth][.!isna.(awwa[:depth])])


Out[4]:
1125.5941329695002

In [5]:
using FixedEffectModels

In [6]:
rr = reg(awwa, @model(costs ~ SurfaceMG + GroundMG + PurchasesMG))


WARNING: ccdf(d::UnivariateDistribution, X::AbstractArray) is deprecated, use ccdf.(d, X) instead.
Stacktrace:
 [1] depwarn(::String, ::Symbol) at ./deprecated.jl:70
 [2] ccdf(::Distributions.FDist{Float64}, ::Array{Float64,1}) at ./deprecated.jl:57
 [3] coeftable(::FixedEffectModels.RegressionResult) at /Users/jrising/.julia/v0.6/FixedEffectModels/src/RegressionResult.jl:80
 [4] limitstringmime(::MIME{Symbol("text/plain")}, ::FixedEffectModels.RegressionResult) at /Users/jrising/.julia/v0.6/IJulia/src/inline.jl:24
 [5] display_dict(::FixedEffectModels.RegressionResult) at /Users/jrising/.julia/v0.6/IJulia/src/execute_request.jl:28
 [6] (::Compat.#inner#17{Array{Any,1},IJulia.#display_dict,Tuple{FixedEffectModels.RegressionResult}})() at /Users/jrising/.julia/v0.6/Compat/src/Compat.jl:488
 [7] execute_request(::ZMQ.Socket, ::IJulia.Msg) at /Users/jrising/.julia/v0.6/IJulia/src/execute_request.jl:186
 [8] (::Compat.#inner#17{Array{Any,1},IJulia.#execute_request,Tuple{ZMQ.Socket,IJulia.Msg}})() at /Users/jrising/.julia/v0.6/Compat/src/Compat.jl:488
 [9] eventloop(::ZMQ.Socket) at /Users/jrising/.julia/v0.6/IJulia/src/eventloop.jl:8
 [10] (::IJulia.##14#17)() at ./task.jl:335
while loading /Users/jrising/.julia/v0.6/IJulia/src/kernel.jl, in expression starting on line 31
Out[6]:
                             Linear Model                             
=======================================================================
Number of obs:                 323   Degrees of freedom:              4
R2:                          0.706   R2 Adjusted:                 0.703
F Statistic:               634.506   p-value:                     0.000
              Estimate Std.Error  t value Pr(>|t|)  Lower 95% Upper 95%
SurfaceMG      3188.93   127.086  25.0928    0.000     2938.9   3438.96
GroundMG       1365.21   399.996  3.41305    0.001    578.244   2152.17
PurchasesMG    3106.28   294.385  10.5518    0.000     2527.1   3685.46
(Intercept)  1.48265e6 3.56121e6 0.416332    0.677 -5.52378e6 8.48908e6

In [7]:
rr = reg(awwa, @model(costs ~ SurfaceMG + GroundMG + GroundMG & depth + PurchasesMG))


=======================================================================
-----------------------------------------------------------------------
=======================================================================
Out[7]:
                               Linear Model                               
===========================================================================
Number of obs:                   320   Degrees of freedom:                5
R2:                            0.579   R2 Adjusted:                   0.574
F Statistic:                  151.86   p-value:                       0.000
                   Estimate Std.Error  t value Pr(>|t|) Lower 95% Upper 95%
SurfaceMG           1606.73   136.596  11.7626    0.000   1337.98   1875.49
GroundMG            1657.99   492.498   3.3665    0.001   688.991   2626.99
PurchasesMG         3086.09   217.028  14.2198    0.000   2659.09    3513.1
GroundMG & depth   0.277748  0.453235 0.612812    0.540 -0.614003    1.1695
(Intercept)       9.99565e6 2.66335e6  3.75303    0.000 4.75544e6 1.52359e7

In [8]:
regtable(rr; renderSettings = latexOutput())


===========================================================================
---------------------------------------------------------------------------
===========================================================================
\begin{tabular}{lr}
\toprule
                 & \multicolumn{1}{c}{costs} \\ 
\cmidrule(lr){2-2} 
                 &                       (1) \\ 
\midrule
(Intercept)      &            9995648.366*** \\ 
                 &             (2663351.993) \\ 
SurfaceMG        &               1606.733*** \\ 
                 &                 (136.596) \\ 
GroundMG         &               1657.993*** \\ 
                 &                 (492.498) \\ 
PurchasesMG      &               3086.094*** \\ 
                 &                 (217.028) \\ 
GroundMG & depth &                     0.278 \\ 
                 &                   (0.453) \\ 
\midrule
Estimator        &                       OLS \\ 
\midrule
$N$              &                       320 \\ 
$R^2$            &                     0.579 \\ 
\bottomrule
\end{tabular}

In [15]:
using GLM, StatsModels, QuantileRegression
import StatsModels.@formula
import StatsModels.ModelFrame

In [16]:
qreg(@formula(costs ~ SurfaceMG + GroundMG + PurchasesMG), awwa, .5)


UndefVarError: ModelFrame not defined

Stacktrace:
 [1] qreg(::StatsModels.Formula, ::DataFrames.DataFrame, ::Float64, ::QuantileRegression.IP) at /Users/jrising/.julia/v0.6/QuantileRegression/src/QuantileRegression.jl:35 (repeats 2 times)

In [7]:
optnation = readtable("../../analyses/optradii/radius-nation.csv");
optstate = readtable("../../analyses/optradii/radius-state.csv");
#opt200km = readtable("../../analyses/optradii/radius-200km.csv");
opt100km = readtable("../../analyses/optradii/radius-100km.csv");
opt50km = readtable("../../analyses/optradii/radius-50km.csv");
#opt0km = readtable("../../analyses/optradii/radius-0km.csv");
optneighbors = readtable("../../analyses/optradii/radius-neighbors.csv");
optsingle = readtable("../../analyses/optradii/radius-single.csv");
optcounty = readtable("../../analyses/optradii/radius-nothing.csv");

In [10]:
DataFrame(radius=["Nation", "State", "Neighbors", "County"], # "200 km", "0 km", "100 km", "50 km", "Single", 
    surface=[sum(optnation[optnation[:variable] .== "withdrawals", :optimized]),
        sum(optstate[optstate[:variable] .== "withdrawals", :optimized]),
#        sum(opt200km[opt200km[:variable] .== "withdrawals", :optimized]),
#        sum(opt100km[opt100km[:variable] .== "withdrawals", :optimized]),
#        sum(opt50km[opt50km[:variable] .== "withdrawals", :optimized]),
#        sum(opt0km[opt0km[:variable] .== "withdrawals", :optimized]),
        sum(optneighbors[optneighbors[:variable] .== "withdrawals", :optimized]),
#        sum(optsingle[optsingle[:variable] .== "withdrawals", :optimized]),
        sum(optcounty[optcounty[:variable] .== "withdrawals", :optimized])],        
    groundwater=[sum(optnation[optnation[:variable] .== "waterfromgw", :optimized]),
        sum(optstate[optstate[:variable] .== "waterfromgw", :optimized]),
#        sum(opt200km[opt200km[:variable] .== "waterfromgw", :optimized]),
#        sum(opt100km[opt100km[:variable] .== "waterfromgw", :optimized]),
#        sum(opt50km[opt50km[:variable] .== "waterfromgw", :optimized]),
#        sum(opt0km[opt0km[:variable] .== "waterfromgw", :optimized]),
        sum(optneighbors[optneighbors[:variable] .== "waterfromgw", :optimized]),
#        sum(optsingle[optsingle[:variable] .== "waterfromgw", :optimized]),
        sum(optcounty[optcounty[:variable] .== "waterfromgw", :optimized])])


Out[10]:
radiussurfacegroundwater
1Nation4.7700663946448874e85.727795615511276e6
2State1.4261982262421383e101.0178368529331375e7
3Neighbors4.570581528183927e82.5676282261607297e7
4County3.726519630078e81.0797075467999999e8

In [17]:
DataFrame(transition=["County2Neighbors", "", "100km2State", "State2Nation"],
    surface=[sum(optneighbors[optneighbors[:variable] .== "withdrawals", :optimized] - optcounty[optcounty[:variable] .== "withdrawals", :optimized]) / sum(optcounty[optcounty[:variable] .== "withdrawals", :optimized]),
             0,
             sum(optstate[optstate[:variable] .== "withdrawals", :optimized] - opt100km[opt100km[:variable] .== "withdrawals", :optimized]) / sum(opt100km[opt100km[:variable] .== "withdrawals", :optimized]),
             sum(optnation[optnation[:variable] .== "withdrawals", :optimized] - optstate[optstate[:variable] .== "withdrawals", :optimized]) / sum(optstate[optstate[:variable] .== "withdrawals", :optimized])],
    groundwater=[sum(optneighbors[optneighbors[:variable] .== "waterfromgw", :optimized] - optcounty[optcounty[:variable] .== "waterfromgw", :optimized]) / sum(optcounty[optcounty[:variable] .== "waterfromgw", :optimized]),
             0,
             sum(optstate[optstate[:variable] .== "waterfromgw", :optimized] - opt100km[opt100km[:variable] .== "waterfromgw", :optimized]) / sum(opt100km[opt100km[:variable] .== "waterfromgw", :optimized]),
             sum(optnation[optnation[:variable] .== "waterfromgw", :optimized] - optstate[optstate[:variable] .== "waterfromgw", :optimized]) / sum(optstate[optstate[:variable] .== "waterfromgw", :optimized])])


Out[17]:
transitionsurfacegroundwater
1County2Neighbors-0.9519037243018486-0.3177891935097157
20.00.0
3100km2State46.84688525625985-0.9670333073424108
4State2Nation-0.7027009411896701-0.06344522575111837

In [11]:
stateextracts = sum(reshape(getparametersolution(house, sol_after.sol, :waterfromgw), nrow(recorded), 2), 2);
nationextracts = sum(reshape(getparametersolution(house, sol_before.sol, :waterfromgw), nrow(recorded), 2), 2);

In [20]:
diffs_state2county = recorded[:TO_GW] * 1383. - vec(stateextracts);
diffs_nation2state = vec(stateextracts - nationextracts);

In [22]:
df = DataFrame(fips=readtable("../../data/extraction/USGS-2010.csv")[:FIPS], state2county=diffs_state2county, nation2state=diffs_nation2state)


WARNING: imported binding for df overwritten in module Main
Out[22]:
fipsstate2countynation2state
1100113452.3982341872580.0
2100362275.908108707816-15874.060042775025
310055744.905031219915865.8835775124799
410073695.116453307127-828.1292149080609
510095587.320.0
61011-222.860400445849341834.0554004458495
710132033.01000000000021043.5431993970915
8101522579.001702385372-16000.179690772746
91017-2420.25000000000052610.363079411765
1010194384.110.0
1110216841.1057613712750.0
121023-2272.0314942316727-14374.068123244986
1310254231.98000000000050.0
141027-2315.115882886881-312.58411711311874
15102920.744999999999891013.8423573622051
16103111972.229461852588-3271.3801837660085
171033-12768.13021771091615630.940217710915
1810351503.7244616279077-120.72446162790698
1910371161.720.0
2010399708.66-4423.704084971649
2110413214.929698127182-1155.7338259257454
221043-43655.0093569546430293.5734627196
2310456320.4724948443084764.821043495628
24104717204.520.0
251049-10365.560212762.736880487475
2610516832.020.0
27105312008.8025604516332678.657439548366
2810557509.69-3951.9934619146516
291057-5265.5856358472092962.8906358472086
301059-1656.99458193865214727.254581938652
&vellip&vellip&vellip&vellip

In [91]:
mapinited = false
include("../../src/lib/graphing.jl")

In [92]:
df[:value] = df[:state2county]
usmap(df, true)


Out[92]:
RCall.RObject{RCall.VecSxp}

In [93]:
df[:value] = df[:nation2state]
usmap(df, true)


Out[93]:
RCall.RObject{RCall.VecSxp}