pws-rates-match-1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Matching PWS rates information from 2017 CA-NV AWWA report with PWSid\n",
    "\n",
    "The 2017 CA-NV AWWA reports contains information about rates charged by about 300 WSPs, but it did not include the PWSID. The `name` field of the tables with PWS information, in most cases, does not match exactly with the `provider_name` in the AWWA rates table.\n",
    "\n",
    "This is a common situation when dealing with real world data. One possible solution is to perform a \"fuzzy match join\": the two tables are compared, a (fuzzy) string matching algorithm applied to each `(left_name, right_name)` combination, and the highest score is taken for the tentative match. The algorithm returns a single continuous score, but below a certain threshold (empirically, 0.5), a high number of false negatives (missed matches, usually because of abbreviations) occurs, as well as false positives (when the string matching algorithm scores high because of long, non-identifying common parts of the name such as `XXX Municipal Water Company` and `YYY Municipal Water Company`).\n",
    "\n",
    "The choice of algorithm, parameter optimization, ect. is interesting in its own regard, but for time constraints I decided to opt for a preliminary pass with an automated algorithm, and then a manual/visual check on a spreadsheet, choosing among a set of match quality markers to indentify the match and then selecting only matches above a certain threshold to include in further processing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# %load nb-imports.py\n",
    "import geopandas as gpd\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "import cawc\n",
    "from cawc import datasets as ds\n",
    "\n",
    "%matplotlib inline\n",
    "%load_ext autoreload\n",
    "%autoreload 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[dataset `pws-rates-awwa`]: getting from loader...\n",
      "[dataset `pws-rates-awwa`]: processing...\n"
     ]
    }
   ],
   "source": [
    "RATES = ds.PWS_RATES()[['provider_name', 'total_charge']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[dataset `pws-boundaries`]: getting from loader...\n",
      "[dataset `pws-boundaries`]: processing...\n"
     ]
    }
   ],
   "source": [
    "ID = ds.PWS_BOUNDARIES()[['pwsid', 'name', 'county_fips']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I tried to study both `join` possibilities separately; in the end, without a more thorough study, I opted to continue with `JOIN_2` since it's a better match for our intent (of all possible PWSIDs, we want to have as many matched rate charges as possible)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import fuzzymatcher as fm\n",
    "\n",
    "JOIN_1 = fm.fuzzy_left_join(RATES, ID, left_on='provider_name', right_on='name')\n",
    "JOIN_2 = fm.fuzzy_left_join(ID, RATES, left_on='name', right_on='provider_name')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>best_match_score</th>\n",
       "      <th>__id_left</th>\n",
       "      <th>__id_right</th>\n",
       "      <th>provider_name</th>\n",
       "      <th>total_charge</th>\n",
       "      <th>pwsid</th>\n",
       "      <th>name</th>\n",
       "      <th>county_fips</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.660616</td>\n",
       "      <td>0_left</td>\n",
       "      <td>1997_right</td>\n",
       "      <td>Alameda County Water District</td>\n",
       "      <td>62.66</td>\n",
       "      <td>CA0110001</td>\n",
       "      <td>ALAMEDA COUNTY WATER DISTRICT</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.243397</td>\n",
       "      <td>1_left</td>\n",
       "      <td>2373_right</td>\n",
       "      <td>Amador Water Agency</td>\n",
       "      <td>45.72</td>\n",
       "      <td>CA3310005</td>\n",
       "      <td>DESERT WATER AGENCY</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0.637465</td>\n",
       "      <td>2_left</td>\n",
       "      <td>2238_right</td>\n",
       "      <td>American Canyon  City Of</td>\n",
       "      <td>55.63</td>\n",
       "      <td>CA2810005</td>\n",
       "      <td>AMERICAN CANYON, CITY OF</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.526898</td>\n",
       "      <td>3_left</td>\n",
       "      <td>1218_right</td>\n",
       "      <td>Anaheim City Of</td>\n",
       "      <td>18.16</td>\n",
       "      <td>CA3010001</td>\n",
       "      <td>CITY OF ANAHEIM</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0.670879</td>\n",
       "      <td>4_left</td>\n",
       "      <td>4291_right</td>\n",
       "      <td>Antioch  City Of</td>\n",
       "      <td>53.72</td>\n",
       "      <td>CA0710001</td>\n",
       "      <td>CITY OF ANTIOCH</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>0.493564</td>\n",
       "      <td>6_left</td>\n",
       "      <td>3244_right</td>\n",
       "      <td>Arcadia  City Of</td>\n",
       "      <td>42.69</td>\n",
       "      <td>CA1910003</td>\n",
       "      <td>CITY OF ARCADIA</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>0.503599</td>\n",
       "      <td>7_left</td>\n",
       "      <td>4517_right</td>\n",
       "      <td>Arcata  City Of</td>\n",
       "      <td>26.25</td>\n",
       "      <td>CA1210001</td>\n",
       "      <td>ARCATA, CITY OF</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>0.161438</td>\n",
       "      <td>9_left</td>\n",
       "      <td>2120_right</td>\n",
       "      <td>Arroyo Grande  City Of</td>\n",
       "      <td>70.94</td>\n",
       "      <td>CA4010001</td>\n",
       "      <td>ARROYO GRANDE, WATER DEPARTMENT</td>\n",
       "      <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>0.506829</td>\n",
       "      <td>10_left</td>\n",
       "      <td>696_right</td>\n",
       "      <td>Bakersfield  City Of</td>\n",
       "      <td>32.83</td>\n",
       "      <td>CA1510031</td>\n",
       "      <td>BAKERSFIELD, CITY OF</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>0.771279</td>\n",
       "      <td>11_left</td>\n",
       "      <td>2214_right</td>\n",
       "      <td>Bear State Water Works</td>\n",
       "      <td>18.77</td>\n",
       "      <td>CA0901217</td>\n",
       "      <td>BEAR STATE WATER WORKS</td>\n",
       "      <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>0.153110</td>\n",
       "      <td>12_left</td>\n",
       "      <td>752_right</td>\n",
       "      <td>Bellflowersomerset Mutual Water Company</td>\n",
       "      <td>30.47</td>\n",
       "      <td>CA4400618</td>\n",
       "      <td>RIDGE MUTUAL WATER COMPANY</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>0.592281</td>\n",
       "      <td>13_left</td>\n",
       "      <td>3341_right</td>\n",
       "      <td>Benicia  City Of</td>\n",
       "      <td>44.56</td>\n",
       "      <td>CA4810001</td>\n",
       "      <td>CITY OF BENICIA</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>0.156664</td>\n",
       "      <td>14_left</td>\n",
       "      <td>544_right</td>\n",
       "      <td>Beverly Hills City Of</td>\n",
       "      <td>74.51</td>\n",
       "      <td>CA3610036</td>\n",
       "      <td>CHINO HILLS, CITY OF</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>203</th>\n",
       "      <td>0.594769</td>\n",
       "      <td>17_left</td>\n",
       "      <td>921_right</td>\n",
       "      <td>Blythe  City Of</td>\n",
       "      <td>40.21</td>\n",
       "      <td>CA3310003</td>\n",
       "      <td>BLYTHE - CITY OF</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td>0.506829</td>\n",
       "      <td>18_left</td>\n",
       "      <td>1534_right</td>\n",
       "      <td>Brawley  City Of</td>\n",
       "      <td>57.19</td>\n",
       "      <td>CA1310001</td>\n",
       "      <td>BRAWLEY, CITY OF</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td>0.468886</td>\n",
       "      <td>19_left</td>\n",
       "      <td>3905_right</td>\n",
       "      <td>Brea  City Of</td>\n",
       "      <td>52.92</td>\n",
       "      <td>CA3010002</td>\n",
       "      <td>CITY OF BREA</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>256</th>\n",
       "      <td>0.560159</td>\n",
       "      <td>21_left</td>\n",
       "      <td>350_right</td>\n",
       "      <td>Buena Park  City Of</td>\n",
       "      <td>56.10</td>\n",
       "      <td>CA3010003</td>\n",
       "      <td>CITY OF BUENA PARK</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>257</th>\n",
       "      <td>0.216125</td>\n",
       "      <td>22_left</td>\n",
       "      <td>4653_right</td>\n",
       "      <td>Burbank City Of</td>\n",
       "      <td>39.37</td>\n",
       "      <td>CA1910179</td>\n",
       "      <td>BURBANK-CITY, WATER DEPT.</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>317</th>\n",
       "      <td>0.526898</td>\n",
       "      <td>23_left</td>\n",
       "      <td>1955_right</td>\n",
       "      <td>Burlingame City Of</td>\n",
       "      <td>79.94</td>\n",
       "      <td>CA4110003</td>\n",
       "      <td>CITY OF BURLINGAME</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>318</th>\n",
       "      <td>0.180321</td>\n",
       "      <td>24_left</td>\n",
       "      <td>4608_right</td>\n",
       "      <td>Calaveras County Water District</td>\n",
       "      <td>96.69</td>\n",
       "      <td>CA5810002</td>\n",
       "      <td>LINDA COUNTY WATER DISTRICT</td>\n",
       "      <td>115</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>425</th>\n",
       "      <td>0.526898</td>\n",
       "      <td>26_left</td>\n",
       "      <td>4538_right</td>\n",
       "      <td>Calexico  City Of</td>\n",
       "      <td>43.89</td>\n",
       "      <td>CA1310002</td>\n",
       "      <td>CALEXICO, CITY OF</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>0.130339</td>\n",
       "      <td>28_left</td>\n",
       "      <td>4149_right</td>\n",
       "      <td>California American Water Ventura District</td>\n",
       "      <td>73.47</td>\n",
       "      <td>CA3310038</td>\n",
       "      <td>RANCHO CALIFORNIA WATER DISTRICT</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>477</th>\n",
       "      <td>0.119037</td>\n",
       "      <td>29_left</td>\n",
       "      <td>118_right</td>\n",
       "      <td>California Water Service Company Antelope Valley</td>\n",
       "      <td>96.37</td>\n",
       "      <td>CA1900794</td>\n",
       "      <td>ANTELOPE PARK MUTUAL WATER COMPANY</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>887</th>\n",
       "      <td>0.269151</td>\n",
       "      <td>36_left</td>\n",
       "      <td>917_right</td>\n",
       "      <td>California Water Service Company Kern River Va...</td>\n",
       "      <td>257.40</td>\n",
       "      <td>CA1500252</td>\n",
       "      <td>KERN VALLEY MUTUAL WATER COMPANY</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1003</th>\n",
       "      <td>0.295526</td>\n",
       "      <td>39_left</td>\n",
       "      <td>1675_right</td>\n",
       "      <td>California Water Service Company Los Altos Sub...</td>\n",
       "      <td>80.30</td>\n",
       "      <td>CA4310001</td>\n",
       "      <td>CWSC LOS ALTOS SUBURBAN</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1189</th>\n",
       "      <td>0.330975</td>\n",
       "      <td>42_left</td>\n",
       "      <td>2075_right</td>\n",
       "      <td>California Water Service Company Palos Verdes</td>\n",
       "      <td>116.86</td>\n",
       "      <td>CA1910104</td>\n",
       "      <td>CALIFORNIA WATER SERVICE CO. - PALOS VER</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1391</th>\n",
       "      <td>0.101486</td>\n",
       "      <td>46_left</td>\n",
       "      <td>417_right</td>\n",
       "      <td>California Water Service Company Stockton</td>\n",
       "      <td>50.75</td>\n",
       "      <td>CA1910199</td>\n",
       "      <td>CALIFORNIA DOMESTIC WATER COMPANY</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1591</th>\n",
       "      <td>0.112469</td>\n",
       "      <td>50_left</td>\n",
       "      <td>4041_right</td>\n",
       "      <td>Californiaamerican Water Company San Diego Dis...</td>\n",
       "      <td>45.51</td>\n",
       "      <td>CA3710042</td>\n",
       "      <td>SAN DIEGO COUNTY WATER AUTHORITY</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1678</th>\n",
       "      <td>0.171111</td>\n",
       "      <td>51_left</td>\n",
       "      <td>3998_right</td>\n",
       "      <td>Camarillo  City Of</td>\n",
       "      <td>38.73</td>\n",
       "      <td>CA5610019</td>\n",
       "      <td>CAMARILLO WATER DEPT</td>\n",
       "      <td>111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1738</th>\n",
       "      <td>0.204732</td>\n",
       "      <td>52_left</td>\n",
       "      <td>3766_right</td>\n",
       "      <td>Cambria Community Services District</td>\n",
       "      <td>40.85</td>\n",
       "      <td>CA3600022</td>\n",
       "      <td>BAKER COMMUNITY SERVICES DISTRICT</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6315</th>\n",
       "      <td>0.615812</td>\n",
       "      <td>289_left</td>\n",
       "      <td>4145_right</td>\n",
       "      <td>Tuolumne Utilities District</td>\n",
       "      <td>92.50</td>\n",
       "      <td>CA5590001</td>\n",
       "      <td>TUOLUMNE UTILITIES DISTRICT</td>\n",
       "      <td>109</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6316</th>\n",
       "      <td>0.463282</td>\n",
       "      <td>290_left</td>\n",
       "      <td>2437_right</td>\n",
       "      <td>Turlock  City Of</td>\n",
       "      <td>42.09</td>\n",
       "      <td>CA5010019</td>\n",
       "      <td>TURLOCK, CITY OF</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6320</th>\n",
       "      <td>0.760968</td>\n",
       "      <td>291_left</td>\n",
       "      <td>4367_right</td>\n",
       "      <td>Twentynine Palms Water District</td>\n",
       "      <td>41.49</td>\n",
       "      <td>CA3610049</td>\n",
       "      <td>TWENTYNINE PALMS WATER DISTRICT</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6321</th>\n",
       "      <td>0.465814</td>\n",
       "      <td>292_left</td>\n",
       "      <td>1638_right</td>\n",
       "      <td>Ukiah City Of</td>\n",
       "      <td>60.41</td>\n",
       "      <td>CA2310003</td>\n",
       "      <td>UKIAH, CITY OF</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6322</th>\n",
       "      <td>0.526898</td>\n",
       "      <td>293_left</td>\n",
       "      <td>3439_right</td>\n",
       "      <td>Upland  City Of</td>\n",
       "      <td>61.64</td>\n",
       "      <td>CA3610050</td>\n",
       "      <td>UPLAND, CITY OF</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6323</th>\n",
       "      <td>0.506829</td>\n",
       "      <td>294_left</td>\n",
       "      <td>3359_right</td>\n",
       "      <td>Vacaville  City Of</td>\n",
       "      <td>41.97</td>\n",
       "      <td>CA4810008</td>\n",
       "      <td>CITY OF VACAVILLE</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6324</th>\n",
       "      <td>0.323267</td>\n",
       "      <td>295_left</td>\n",
       "      <td>4382_right</td>\n",
       "      <td>Valencia Water Company</td>\n",
       "      <td>37.13</td>\n",
       "      <td>CA1910163</td>\n",
       "      <td>VALENCIA HEIGHTS WATER CO.</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6411</th>\n",
       "      <td>0.402975</td>\n",
       "      <td>296_left</td>\n",
       "      <td>1939_right</td>\n",
       "      <td>Vallejo  City Of  Vallejo Service Area</td>\n",
       "      <td>85.34</td>\n",
       "      <td>CA4810007</td>\n",
       "      <td>CITY OF VALLEJO</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6472</th>\n",
       "      <td>0.534851</td>\n",
       "      <td>297_left</td>\n",
       "      <td>4431_right</td>\n",
       "      <td>Vallejo City Of  Lakes Service Area</td>\n",
       "      <td>238.44</td>\n",
       "      <td>CA4810021</td>\n",
       "      <td>CITY OF VALLEJO-LAKES SYSTEM</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6532</th>\n",
       "      <td>0.450866</td>\n",
       "      <td>298_left</td>\n",
       "      <td>337_right</td>\n",
       "      <td>Valley County Water District</td>\n",
       "      <td>28.59</td>\n",
       "      <td>CA1909006</td>\n",
       "      <td>WEST VALLEY COUNTY WATER DISTRICT</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6592</th>\n",
       "      <td>0.965085</td>\n",
       "      <td>300_left</td>\n",
       "      <td>2124_right</td>\n",
       "      <td>Valley Of The Moon Water District</td>\n",
       "      <td>49.54</td>\n",
       "      <td>CA4910013</td>\n",
       "      <td>VALLEY OF THE MOON WATER DISTRICT</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6679</th>\n",
       "      <td>0.140507</td>\n",
       "      <td>302_left</td>\n",
       "      <td>2534_right</td>\n",
       "      <td>Ventura City Of</td>\n",
       "      <td>91.44</td>\n",
       "      <td>CA5602140</td>\n",
       "      <td>SATICOY COUNTRY CLUB-CITY OF VENTURA</td>\n",
       "      <td>111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6680</th>\n",
       "      <td>0.312528</td>\n",
       "      <td>303_left</td>\n",
       "      <td>686_right</td>\n",
       "      <td>Ventura County Waterworks District No 01  Moor...</td>\n",
       "      <td>67.49</td>\n",
       "      <td>CA5610018</td>\n",
       "      <td>VENTURA CWWD NO. 1 - MOORPARK</td>\n",
       "      <td>111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6754</th>\n",
       "      <td>0.216125</td>\n",
       "      <td>304_left</td>\n",
       "      <td>3857_right</td>\n",
       "      <td>Vernon  City Of</td>\n",
       "      <td>21.19</td>\n",
       "      <td>CA1910167</td>\n",
       "      <td>VERNON-CITY, WATER DEPT.</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6814</th>\n",
       "      <td>0.543882</td>\n",
       "      <td>305_left</td>\n",
       "      <td>3775_right</td>\n",
       "      <td>Victorville Water District</td>\n",
       "      <td>37.61</td>\n",
       "      <td>CA3610052</td>\n",
       "      <td>VICTORVILLE WATER DISTRICT</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6815</th>\n",
       "      <td>0.198152</td>\n",
       "      <td>306_left</td>\n",
       "      <td>1385_right</td>\n",
       "      <td>Virgin Valley Water District</td>\n",
       "      <td>60.05</td>\n",
       "      <td>CA3610004</td>\n",
       "      <td>WEST VALLEY WATER DISTRICT</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6828</th>\n",
       "      <td>0.506024</td>\n",
       "      <td>307_left</td>\n",
       "      <td>711_right</td>\n",
       "      <td>Vista Irrigation District</td>\n",
       "      <td>76.85</td>\n",
       "      <td>CA3710027</td>\n",
       "      <td>VISTA IRRIGATION DISTRICT</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6829</th>\n",
       "      <td>0.703955</td>\n",
       "      <td>308_left</td>\n",
       "      <td>3564_right</td>\n",
       "      <td>Walnut Valley Water District</td>\n",
       "      <td>63.09</td>\n",
       "      <td>CA1910234</td>\n",
       "      <td>WALNUT VALLEY WATER DISTRICT</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6830</th>\n",
       "      <td>0.640776</td>\n",
       "      <td>309_left</td>\n",
       "      <td>2878_right</td>\n",
       "      <td>Watsonville  City Of</td>\n",
       "      <td>56.60</td>\n",
       "      <td>CA4410011</td>\n",
       "      <td>WATSONVILLE, CITY OF</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6831</th>\n",
       "      <td>0.718074</td>\n",
       "      <td>310_left</td>\n",
       "      <td>2374_right</td>\n",
       "      <td>West Sacramento  City Of</td>\n",
       "      <td>52.22</td>\n",
       "      <td>CA5710003</td>\n",
       "      <td>CITY OF WEST SACRAMENTO</td>\n",
       "      <td>113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6832</th>\n",
       "      <td>0.981551</td>\n",
       "      <td>311_left</td>\n",
       "      <td>2280_right</td>\n",
       "      <td>West San Martin Water Works</td>\n",
       "      <td>62.21</td>\n",
       "      <td>CA4300543</td>\n",
       "      <td>WEST SAN MARTIN WATER WORKS INC</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6833</th>\n",
       "      <td>0.631380</td>\n",
       "      <td>312_left</td>\n",
       "      <td>1385_right</td>\n",
       "      <td>West Valley Water District</td>\n",
       "      <td>67.46</td>\n",
       "      <td>CA3610004</td>\n",
       "      <td>WEST VALLEY WATER DISTRICT</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6835</th>\n",
       "      <td>0.340827</td>\n",
       "      <td>313_left</td>\n",
       "      <td>845_right</td>\n",
       "      <td>Westborough Water District</td>\n",
       "      <td>52.05</td>\n",
       "      <td>CA4110027</td>\n",
       "      <td>WESTBOROUGH COUNTY WATER DIST</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6933</th>\n",
       "      <td>0.236168</td>\n",
       "      <td>314_left</td>\n",
       "      <td>179_right</td>\n",
       "      <td>Western Municipal Water District</td>\n",
       "      <td>70.44</td>\n",
       "      <td>CA2110002</td>\n",
       "      <td>MARIN MUNICIPAL WATER DISTRICT</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6938</th>\n",
       "      <td>0.204732</td>\n",
       "      <td>315_left</td>\n",
       "      <td>3766_right</td>\n",
       "      <td>Westhaven Community Services District</td>\n",
       "      <td>207.27</td>\n",
       "      <td>CA3600022</td>\n",
       "      <td>BAKER COMMUNITY SERVICES DISTRICT</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7055</th>\n",
       "      <td>0.749944</td>\n",
       "      <td>318_left</td>\n",
       "      <td>603_right</td>\n",
       "      <td>Windsor  Town Of</td>\n",
       "      <td>28.84</td>\n",
       "      <td>CA4910017</td>\n",
       "      <td>WINDSOR, TOWN OF</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7056</th>\n",
       "      <td>0.596111</td>\n",
       "      <td>319_left</td>\n",
       "      <td>3862_right</td>\n",
       "      <td>Woodland  City Of</td>\n",
       "      <td>92.77</td>\n",
       "      <td>CA5710006</td>\n",
       "      <td>CITY OF WOODLAND</td>\n",
       "      <td>113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7057</th>\n",
       "      <td>0.720344</td>\n",
       "      <td>320_left</td>\n",
       "      <td>1910_right</td>\n",
       "      <td>Yorba Linda Water District</td>\n",
       "      <td>66.60</td>\n",
       "      <td>CA3010037</td>\n",
       "      <td>YORBA LINDA WATER DISTRICT</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7058</th>\n",
       "      <td>0.487695</td>\n",
       "      <td>321_left</td>\n",
       "      <td>2104_right</td>\n",
       "      <td>Yreka  City Of</td>\n",
       "      <td>53.94</td>\n",
       "      <td>CA4710011</td>\n",
       "      <td>YREKA, CITY OF</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7059</th>\n",
       "      <td>0.658449</td>\n",
       "      <td>322_left</td>\n",
       "      <td>3045_right</td>\n",
       "      <td>Yucaipa Valley Water District</td>\n",
       "      <td>34.58</td>\n",
       "      <td>CA3610055</td>\n",
       "      <td>YUCAIPA VALLEY WATER DISTRICT</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>260 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      best_match_score __id_left  __id_right  \\\n",
       "0             0.660616    0_left  1997_right   \n",
       "1             0.243397    1_left  2373_right   \n",
       "7             0.637465    2_left  2238_right   \n",
       "8             0.526898    3_left  1218_right   \n",
       "9             0.670879    4_left  4291_right   \n",
       "26            0.493564    6_left  3244_right   \n",
       "27            0.503599    7_left  4517_right   \n",
       "30            0.161438    9_left  2120_right   \n",
       "90            0.506829   10_left   696_right   \n",
       "91            0.771279   11_left  2214_right   \n",
       "92            0.153110   12_left   752_right   \n",
       "142           0.592281   13_left  3341_right   \n",
       "143           0.156664   14_left   544_right   \n",
       "203           0.594769   17_left   921_right   \n",
       "204           0.506829   18_left  1534_right   \n",
       "205           0.468886   19_left  3905_right   \n",
       "256           0.560159   21_left   350_right   \n",
       "257           0.216125   22_left  4653_right   \n",
       "317           0.526898   23_left  1955_right   \n",
       "318           0.180321   24_left  4608_right   \n",
       "425           0.526898   26_left  4538_right   \n",
       "476           0.130339   28_left  4149_right   \n",
       "477           0.119037   29_left   118_right   \n",
       "887           0.269151   36_left   917_right   \n",
       "1003          0.295526   39_left  1675_right   \n",
       "1189          0.330975   42_left  2075_right   \n",
       "1391          0.101486   46_left   417_right   \n",
       "1591          0.112469   50_left  4041_right   \n",
       "1678          0.171111   51_left  3998_right   \n",
       "1738          0.204732   52_left  3766_right   \n",
       "...                ...       ...         ...   \n",
       "6315          0.615812  289_left  4145_right   \n",
       "6316          0.463282  290_left  2437_right   \n",
       "6320          0.760968  291_left  4367_right   \n",
       "6321          0.465814  292_left  1638_right   \n",
       "6322          0.526898  293_left  3439_right   \n",
       "6323          0.506829  294_left  3359_right   \n",
       "6324          0.323267  295_left  4382_right   \n",
       "6411          0.402975  296_left  1939_right   \n",
       "6472          0.534851  297_left  4431_right   \n",
       "6532          0.450866  298_left   337_right   \n",
       "6592          0.965085  300_left  2124_right   \n",
       "6679          0.140507  302_left  2534_right   \n",
       "6680          0.312528  303_left   686_right   \n",
       "6754          0.216125  304_left  3857_right   \n",
       "6814          0.543882  305_left  3775_right   \n",
       "6815          0.198152  306_left  1385_right   \n",
       "6828          0.506024  307_left   711_right   \n",
       "6829          0.703955  308_left  3564_right   \n",
       "6830          0.640776  309_left  2878_right   \n",
       "6831          0.718074  310_left  2374_right   \n",
       "6832          0.981551  311_left  2280_right   \n",
       "6833          0.631380  312_left  1385_right   \n",
       "6835          0.340827  313_left   845_right   \n",
       "6933          0.236168  314_left   179_right   \n",
       "6938          0.204732  315_left  3766_right   \n",
       "7055          0.749944  318_left   603_right   \n",
       "7056          0.596111  319_left  3862_right   \n",
       "7057          0.720344  320_left  1910_right   \n",
       "7058          0.487695  321_left  2104_right   \n",
       "7059          0.658449  322_left  3045_right   \n",
       "\n",
       "                                          provider_name  total_charge  \\\n",
       "0                         Alameda County Water District         62.66   \n",
       "1                                   Amador Water Agency         45.72   \n",
       "7                              American Canyon  City Of         55.63   \n",
       "8                                       Anaheim City Of         18.16   \n",
       "9                                      Antioch  City Of         53.72   \n",
       "26                                     Arcadia  City Of         42.69   \n",
       "27                                      Arcata  City Of         26.25   \n",
       "30                               Arroyo Grande  City Of         70.94   \n",
       "90                                 Bakersfield  City Of         32.83   \n",
       "91                               Bear State Water Works         18.77   \n",
       "92              Bellflowersomerset Mutual Water Company         30.47   \n",
       "142                                    Benicia  City Of         44.56   \n",
       "143                               Beverly Hills City Of         74.51   \n",
       "203                                     Blythe  City Of         40.21   \n",
       "204                                    Brawley  City Of         57.19   \n",
       "205                                       Brea  City Of         52.92   \n",
       "256                                 Buena Park  City Of         56.10   \n",
       "257                                     Burbank City Of         39.37   \n",
       "317                                  Burlingame City Of         79.94   \n",
       "318                     Calaveras County Water District         96.69   \n",
       "425                                   Calexico  City Of         43.89   \n",
       "476          California American Water Ventura District         73.47   \n",
       "477    California Water Service Company Antelope Valley         96.37   \n",
       "887   California Water Service Company Kern River Va...        257.40   \n",
       "1003  California Water Service Company Los Altos Sub...         80.30   \n",
       "1189      California Water Service Company Palos Verdes        116.86   \n",
       "1391          California Water Service Company Stockton         50.75   \n",
       "1591  Californiaamerican Water Company San Diego Dis...         45.51   \n",
       "1678                                 Camarillo  City Of         38.73   \n",
       "1738                Cambria Community Services District         40.85   \n",
       "...                                                 ...           ...   \n",
       "6315                        Tuolumne Utilities District         92.50   \n",
       "6316                                   Turlock  City Of         42.09   \n",
       "6320                    Twentynine Palms Water District         41.49   \n",
       "6321                                      Ukiah City Of         60.41   \n",
       "6322                                    Upland  City Of         61.64   \n",
       "6323                                 Vacaville  City Of         41.97   \n",
       "6324                             Valencia Water Company         37.13   \n",
       "6411             Vallejo  City Of  Vallejo Service Area         85.34   \n",
       "6472                Vallejo City Of  Lakes Service Area        238.44   \n",
       "6532                       Valley County Water District         28.59   \n",
       "6592                  Valley Of The Moon Water District         49.54   \n",
       "6679                                    Ventura City Of         91.44   \n",
       "6680  Ventura County Waterworks District No 01  Moor...         67.49   \n",
       "6754                                    Vernon  City Of         21.19   \n",
       "6814                         Victorville Water District         37.61   \n",
       "6815                       Virgin Valley Water District         60.05   \n",
       "6828                          Vista Irrigation District         76.85   \n",
       "6829                       Walnut Valley Water District         63.09   \n",
       "6830                               Watsonville  City Of         56.60   \n",
       "6831                           West Sacramento  City Of         52.22   \n",
       "6832                        West San Martin Water Works         62.21   \n",
       "6833                         West Valley Water District         67.46   \n",
       "6835                         Westborough Water District         52.05   \n",
       "6933                   Western Municipal Water District         70.44   \n",
       "6938              Westhaven Community Services District        207.27   \n",
       "7055                                   Windsor  Town Of         28.84   \n",
       "7056                                  Woodland  City Of         92.77   \n",
       "7057                         Yorba Linda Water District         66.60   \n",
       "7058                                     Yreka  City Of         53.94   \n",
       "7059                      Yucaipa Valley Water District         34.58   \n",
       "\n",
       "          pwsid                                      name  county_fips  \n",
       "0     CA0110001             ALAMEDA COUNTY WATER DISTRICT            1  \n",
       "1     CA3310005                       DESERT WATER AGENCY           65  \n",
       "7     CA2810005                  AMERICAN CANYON, CITY OF           55  \n",
       "8     CA3010001                           CITY OF ANAHEIM           59  \n",
       "9     CA0710001                           CITY OF ANTIOCH           13  \n",
       "26    CA1910003                           CITY OF ARCADIA           37  \n",
       "27    CA1210001                           ARCATA, CITY OF           23  \n",
       "30    CA4010001           ARROYO GRANDE, WATER DEPARTMENT           79  \n",
       "90    CA1510031                      BAKERSFIELD, CITY OF           29  \n",
       "91    CA0901217                    BEAR STATE WATER WORKS           17  \n",
       "92    CA4400618                RIDGE MUTUAL WATER COMPANY           87  \n",
       "142   CA4810001                           CITY OF BENICIA           95  \n",
       "143   CA3610036                      CHINO HILLS, CITY OF           71  \n",
       "203   CA3310003                          BLYTHE - CITY OF           65  \n",
       "204   CA1310001                          BRAWLEY, CITY OF           25  \n",
       "205   CA3010002                              CITY OF BREA           59  \n",
       "256   CA3010003                        CITY OF BUENA PARK           59  \n",
       "257   CA1910179                 BURBANK-CITY, WATER DEPT.           37  \n",
       "317   CA4110003                        CITY OF BURLINGAME           81  \n",
       "318   CA5810002               LINDA COUNTY WATER DISTRICT          115  \n",
       "425   CA1310002                         CALEXICO, CITY OF           25  \n",
       "476   CA3310038          RANCHO CALIFORNIA WATER DISTRICT           65  \n",
       "477   CA1900794        ANTELOPE PARK MUTUAL WATER COMPANY           37  \n",
       "887   CA1500252          KERN VALLEY MUTUAL WATER COMPANY           29  \n",
       "1003  CA4310001                   CWSC LOS ALTOS SUBURBAN           85  \n",
       "1189  CA1910104  CALIFORNIA WATER SERVICE CO. - PALOS VER           37  \n",
       "1391  CA1910199         CALIFORNIA DOMESTIC WATER COMPANY           37  \n",
       "1591  CA3710042          SAN DIEGO COUNTY WATER AUTHORITY           73  \n",
       "1678  CA5610019                      CAMARILLO WATER DEPT          111  \n",
       "1738  CA3600022         BAKER COMMUNITY SERVICES DISTRICT           71  \n",
       "...         ...                                       ...          ...  \n",
       "6315  CA5590001               TUOLUMNE UTILITIES DISTRICT          109  \n",
       "6316  CA5010019                          TURLOCK, CITY OF           99  \n",
       "6320  CA3610049           TWENTYNINE PALMS WATER DISTRICT           71  \n",
       "6321  CA2310003                            UKIAH, CITY OF           45  \n",
       "6322  CA3610050                           UPLAND, CITY OF           71  \n",
       "6323  CA4810008                         CITY OF VACAVILLE           95  \n",
       "6324  CA1910163                VALENCIA HEIGHTS WATER CO.           37  \n",
       "6411  CA4810007                           CITY OF VALLEJO           95  \n",
       "6472  CA4810021              CITY OF VALLEJO-LAKES SYSTEM           95  \n",
       "6532  CA1909006         WEST VALLEY COUNTY WATER DISTRICT           37  \n",
       "6592  CA4910013         VALLEY OF THE MOON WATER DISTRICT           97  \n",
       "6679  CA5602140      SATICOY COUNTRY CLUB-CITY OF VENTURA          111  \n",
       "6680  CA5610018             VENTURA CWWD NO. 1 - MOORPARK          111  \n",
       "6754  CA1910167                  VERNON-CITY, WATER DEPT.           37  \n",
       "6814  CA3610052                VICTORVILLE WATER DISTRICT           71  \n",
       "6815  CA3610004                WEST VALLEY WATER DISTRICT           71  \n",
       "6828  CA3710027                 VISTA IRRIGATION DISTRICT           73  \n",
       "6829  CA1910234              WALNUT VALLEY WATER DISTRICT           37  \n",
       "6830  CA4410011                      WATSONVILLE, CITY OF           87  \n",
       "6831  CA5710003                   CITY OF WEST SACRAMENTO          113  \n",
       "6832  CA4300543           WEST SAN MARTIN WATER WORKS INC           85  \n",
       "6833  CA3610004                WEST VALLEY WATER DISTRICT           71  \n",
       "6835  CA4110027             WESTBOROUGH COUNTY WATER DIST           81  \n",
       "6933  CA2110002            MARIN MUNICIPAL WATER DISTRICT           41  \n",
       "6938  CA3600022         BAKER COMMUNITY SERVICES DISTRICT           71  \n",
       "7055  CA4910017                          WINDSOR, TOWN OF           97  \n",
       "7056  CA5710006                          CITY OF WOODLAND          113  \n",
       "7057  CA3010037                YORBA LINDA WATER DISTRICT           59  \n",
       "7058  CA4710011                            YREKA, CITY OF           93  \n",
       "7059  CA3610055             YUCAIPA VALLEY WATER DISTRICT           71  \n",
       "\n",
       "[260 rows x 8 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "JOIN_1.loc[lambda d: d.best_match_score > 0.1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>best_match_score</th>\n",
       "      <th>__id_left</th>\n",
       "      <th>__id_right</th>\n",
       "      <th>pwsid</th>\n",
       "      <th>name</th>\n",
       "      <th>county_fips</th>\n",
       "      <th>provider_name</th>\n",
       "      <th>total_charge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>729</th>\n",
       "      <td>0.388590</td>\n",
       "      <td>51_left</td>\n",
       "      <td>134_right</td>\n",
       "      <td>CA3710010</td>\n",
       "      <td>HELIX WATER DISTRICT</td>\n",
       "      <td>73</td>\n",
       "      <td>Helix Water District</td>\n",
       "      <td>64.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>0.462648</td>\n",
       "      <td>69_left</td>\n",
       "      <td>244_right</td>\n",
       "      <td>CA3010036</td>\n",
       "      <td>CITY OF SAN CLEMENTE</td>\n",
       "      <td>59</td>\n",
       "      <td>San Clemente  City Of</td>\n",
       "      <td>54.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1054</th>\n",
       "      <td>0.439750</td>\n",
       "      <td>71_left</td>\n",
       "      <td>104_right</td>\n",
       "      <td>CA3010069</td>\n",
       "      <td>CITY OF FOUNTAIN VALLEY</td>\n",
       "      <td>59</td>\n",
       "      <td>Fountain Valley  City Of</td>\n",
       "      <td>33.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1234</th>\n",
       "      <td>0.227250</td>\n",
       "      <td>80_left</td>\n",
       "      <td>250_right</td>\n",
       "      <td>CA1910039</td>\n",
       "      <td>SAN GABRIEL VALLEY WATER CO.-EL MONTE</td>\n",
       "      <td>37</td>\n",
       "      <td>San Gabriel Valley Water Company</td>\n",
       "      <td>62.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1237</th>\n",
       "      <td>0.295424</td>\n",
       "      <td>81_left</td>\n",
       "      <td>222_right</td>\n",
       "      <td>CA3710016</td>\n",
       "      <td>RAINBOW MUNICIPAL WD</td>\n",
       "      <td>73</td>\n",
       "      <td>Rainbow Municipal Water District</td>\n",
       "      <td>133.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1366</th>\n",
       "      <td>0.450837</td>\n",
       "      <td>94_left</td>\n",
       "      <td>138_right</td>\n",
       "      <td>CA4110016</td>\n",
       "      <td>TOWN OF HILLSBOROUGH</td>\n",
       "      <td>81</td>\n",
       "      <td>Hillsborough  Town Of</td>\n",
       "      <td>241.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1641</th>\n",
       "      <td>0.231470</td>\n",
       "      <td>106_left</td>\n",
       "      <td>267_right</td>\n",
       "      <td>CA5400536</td>\n",
       "      <td>KINGS RIVER ESTATES MUTUAL WATER COMPANY</td>\n",
       "      <td>107</td>\n",
       "      <td>Sierra Estates Mutual Water Company</td>\n",
       "      <td>69.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1834</th>\n",
       "      <td>0.341550</td>\n",
       "      <td>114_left</td>\n",
       "      <td>132_right</td>\n",
       "      <td>CA0110006</td>\n",
       "      <td>CITY OF HAYWARD</td>\n",
       "      <td>1</td>\n",
       "      <td>Hayward  City Of</td>\n",
       "      <td>49.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006</th>\n",
       "      <td>0.305135</td>\n",
       "      <td>123_left</td>\n",
       "      <td>122_right</td>\n",
       "      <td>CA3010035</td>\n",
       "      <td>GOLDEN STATE WC - PLACENTIA</td>\n",
       "      <td>59</td>\n",
       "      <td>Golden State Water Company  Placentia</td>\n",
       "      <td>64.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2040</th>\n",
       "      <td>0.331516</td>\n",
       "      <td>127_left</td>\n",
       "      <td>72_right</td>\n",
       "      <td>CA3310037</td>\n",
       "      <td>CORONA, CITY OF</td>\n",
       "      <td>65</td>\n",
       "      <td>Corona City Of</td>\n",
       "      <td>52.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2917</th>\n",
       "      <td>0.493754</td>\n",
       "      <td>179_left</td>\n",
       "      <td>174_right</td>\n",
       "      <td>CA2110002</td>\n",
       "      <td>MARIN MUNICIPAL WATER DISTRICT</td>\n",
       "      <td>41</td>\n",
       "      <td>Marin Municipal Water District</td>\n",
       "      <td>55.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3101</th>\n",
       "      <td>0.341550</td>\n",
       "      <td>201_left</td>\n",
       "      <td>105_right</td>\n",
       "      <td>CA1010007</td>\n",
       "      <td>CITY OF FRESNO</td>\n",
       "      <td>19</td>\n",
       "      <td>Fresno City Of</td>\n",
       "      <td>28.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3159</th>\n",
       "      <td>0.790753</td>\n",
       "      <td>204_left</td>\n",
       "      <td>277_right</td>\n",
       "      <td>CA1910205</td>\n",
       "      <td>SUBURBAN WATER SYSTEMS-SAN JOSE</td>\n",
       "      <td>37</td>\n",
       "      <td>Suburban Water Systems  San Jose Hills</td>\n",
       "      <td>61.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3435</th>\n",
       "      <td>0.505515</td>\n",
       "      <td>215_left</td>\n",
       "      <td>266_right</td>\n",
       "      <td>CA4510006</td>\n",
       "      <td>CITY OF SHASTA LAKE</td>\n",
       "      <td>89</td>\n",
       "      <td>Shasta Lake  City Of</td>\n",
       "      <td>46.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3535</th>\n",
       "      <td>0.447941</td>\n",
       "      <td>227_left</td>\n",
       "      <td>157_right</td>\n",
       "      <td>CA3910015</td>\n",
       "      <td>CITY OF LATHROP</td>\n",
       "      <td>77</td>\n",
       "      <td>Lathrop  City Of</td>\n",
       "      <td>67.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5281</th>\n",
       "      <td>0.209540</td>\n",
       "      <td>324_left</td>\n",
       "      <td>185_right</td>\n",
       "      <td>CA5010005</td>\n",
       "      <td>CITY OF MODESTO, SALIDA</td>\n",
       "      <td>99</td>\n",
       "      <td>Modesto  City Of</td>\n",
       "      <td>49.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5490</th>\n",
       "      <td>0.326279</td>\n",
       "      <td>337_left</td>\n",
       "      <td>298_right</td>\n",
       "      <td>CA1909006</td>\n",
       "      <td>WEST VALLEY COUNTY WATER DISTRICT</td>\n",
       "      <td>37</td>\n",
       "      <td>Valley County Water District</td>\n",
       "      <td>28.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5658</th>\n",
       "      <td>0.351545</td>\n",
       "      <td>342_left</td>\n",
       "      <td>267_right</td>\n",
       "      <td>CA1500447</td>\n",
       "      <td>SIERRA BREEZE MUTUAL WATER COMPANY</td>\n",
       "      <td>29</td>\n",
       "      <td>Sierra Estates Mutual Water Company</td>\n",
       "      <td>69.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5783</th>\n",
       "      <td>0.481873</td>\n",
       "      <td>350_left</td>\n",
       "      <td>21_right</td>\n",
       "      <td>CA3010003</td>\n",
       "      <td>CITY OF BUENA PARK</td>\n",
       "      <td>59</td>\n",
       "      <td>Buena Park  City Of</td>\n",
       "      <td>56.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5911</th>\n",
       "      <td>0.305538</td>\n",
       "      <td>362_left</td>\n",
       "      <td>267_right</td>\n",
       "      <td>CA1200588</td>\n",
       "      <td>SEAWOOD ESTATES MUTUAL WATER COMPANY</td>\n",
       "      <td>23</td>\n",
       "      <td>Sierra Estates Mutual Water Company</td>\n",
       "      <td>69.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6231</th>\n",
       "      <td>0.867887</td>\n",
       "      <td>384_left</td>\n",
       "      <td>236_right</td>\n",
       "      <td>CA1910140</td>\n",
       "      <td>RUBIO CANON LAND &amp; WATER ASSOCIATION</td>\n",
       "      <td>37</td>\n",
       "      <td>Rubio Canyon Land And Water Association</td>\n",
       "      <td>75.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6234</th>\n",
       "      <td>0.341550</td>\n",
       "      <td>387_left</td>\n",
       "      <td>177_right</td>\n",
       "      <td>CA0710006</td>\n",
       "      <td>CITY OF MARTINEZ</td>\n",
       "      <td>13</td>\n",
       "      <td>Martinez  City Of</td>\n",
       "      <td>51.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6280</th>\n",
       "      <td>0.287661</td>\n",
       "      <td>393_left</td>\n",
       "      <td>14_right</td>\n",
       "      <td>CA1910156</td>\n",
       "      <td>BEVERLY HILLS-CITY, WATER DEPT.</td>\n",
       "      <td>37</td>\n",
       "      <td>Beverly Hills City Of</td>\n",
       "      <td>74.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7945</th>\n",
       "      <td>0.379744</td>\n",
       "      <td>478_left</td>\n",
       "      <td>145_right</td>\n",
       "      <td>CA1510017</td>\n",
       "      <td>INDIAN WELLS VALLEY W.D.</td>\n",
       "      <td>29</td>\n",
       "      <td>Indian Wells Valley Water District</td>\n",
       "      <td>52.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9094</th>\n",
       "      <td>0.505515</td>\n",
       "      <td>544_left</td>\n",
       "      <td>57_right</td>\n",
       "      <td>CA3610036</td>\n",
       "      <td>CHINO HILLS, CITY OF</td>\n",
       "      <td>71</td>\n",
       "      <td>Chino Hills  City Of</td>\n",
       "      <td>59.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9913</th>\n",
       "      <td>0.557228</td>\n",
       "      <td>603_left</td>\n",
       "      <td>318_right</td>\n",
       "      <td>CA4910017</td>\n",
       "      <td>WINDSOR, TOWN OF</td>\n",
       "      <td>97</td>\n",
       "      <td>Windsor  Town Of</td>\n",
       "      <td>28.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10357</th>\n",
       "      <td>0.251272</td>\n",
       "      <td>625_left</td>\n",
       "      <td>248_right</td>\n",
       "      <td>CA1910144</td>\n",
       "      <td>SAN GABRIEL COUNTY WD</td>\n",
       "      <td>37</td>\n",
       "      <td>San Gabriel County Water District</td>\n",
       "      <td>38.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10737</th>\n",
       "      <td>0.283222</td>\n",
       "      <td>655_left</td>\n",
       "      <td>300_right</td>\n",
       "      <td>CA4900871</td>\n",
       "      <td>DE ANZA MOON VALLEY WATER COMPANY</td>\n",
       "      <td>97</td>\n",
       "      <td>Valley Of The Moon Water District</td>\n",
       "      <td>49.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11231</th>\n",
       "      <td>0.341550</td>\n",
       "      <td>685_left</td>\n",
       "      <td>133_right</td>\n",
       "      <td>CA4910005</td>\n",
       "      <td>HEALDSBURG, CITY OF</td>\n",
       "      <td>97</td>\n",
       "      <td>Healdsburg  City Of</td>\n",
       "      <td>65.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11232</th>\n",
       "      <td>0.295245</td>\n",
       "      <td>686_left</td>\n",
       "      <td>303_right</td>\n",
       "      <td>CA5610018</td>\n",
       "      <td>VENTURA CWWD NO. 1 - MOORPARK</td>\n",
       "      <td>111</td>\n",
       "      <td>Ventura County Waterworks District No 01  Moor...</td>\n",
       "      <td>67.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71796</th>\n",
       "      <td>0.368522</td>\n",
       "      <td>4446_left</td>\n",
       "      <td>81_right</td>\n",
       "      <td>CA0710007</td>\n",
       "      <td>DIABLO WATER DISTRICT</td>\n",
       "      <td>13</td>\n",
       "      <td>Diablo Water District</td>\n",
       "      <td>49.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72085</th>\n",
       "      <td>0.550287</td>\n",
       "      <td>4461_left</td>\n",
       "      <td>142_right</td>\n",
       "      <td>CA3010053</td>\n",
       "      <td>CITY OF HUNTINGTON BEACH</td>\n",
       "      <td>59</td>\n",
       "      <td>Huntington Beach City Of</td>\n",
       "      <td>28.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72086</th>\n",
       "      <td>0.341550</td>\n",
       "      <td>4462_left</td>\n",
       "      <td>265_right</td>\n",
       "      <td>CA1510019</td>\n",
       "      <td>SHAFTER, CITY OF</td>\n",
       "      <td>29</td>\n",
       "      <td>Shafter  City Of</td>\n",
       "      <td>44.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72087</th>\n",
       "      <td>0.290919</td>\n",
       "      <td>4463_left</td>\n",
       "      <td>199_right</td>\n",
       "      <td>CA1907014</td>\n",
       "      <td>NORTH TRAILS MUTUAL WATER COMPANY</td>\n",
       "      <td>37</td>\n",
       "      <td>North Lone Pine Mutual Water Company</td>\n",
       "      <td>12.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72259</th>\n",
       "      <td>0.321481</td>\n",
       "      <td>4485_left</td>\n",
       "      <td>62_right</td>\n",
       "      <td>CA3710014</td>\n",
       "      <td>OCEANSIDE, CITY OF</td>\n",
       "      <td>73</td>\n",
       "      <td>City Of Oceanside</td>\n",
       "      <td>34.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72448</th>\n",
       "      <td>0.341550</td>\n",
       "      <td>4507_left</td>\n",
       "      <td>106_right</td>\n",
       "      <td>CA3010010</td>\n",
       "      <td>CITY OF FULLERTON</td>\n",
       "      <td>59</td>\n",
       "      <td>Fullerton City Of</td>\n",
       "      <td>36.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72603</th>\n",
       "      <td>0.260677</td>\n",
       "      <td>4514_left</td>\n",
       "      <td>57_right</td>\n",
       "      <td>CA3610012</td>\n",
       "      <td>CHINO, CITY OF</td>\n",
       "      <td>71</td>\n",
       "      <td>Chino Hills  City Of</td>\n",
       "      <td>59.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72613</th>\n",
       "      <td>0.321481</td>\n",
       "      <td>4517_left</td>\n",
       "      <td>7_right</td>\n",
       "      <td>CA1210001</td>\n",
       "      <td>ARCATA, CITY OF</td>\n",
       "      <td>23</td>\n",
       "      <td>Arcata  City Of</td>\n",
       "      <td>26.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72614</th>\n",
       "      <td>0.506243</td>\n",
       "      <td>4518_left</td>\n",
       "      <td>5_right</td>\n",
       "      <td>CA3610118</td>\n",
       "      <td>APPLE VALLEY RANCHOS WATER CO - YERMO</td>\n",
       "      <td>71</td>\n",
       "      <td>Apple Valley Ranchos Water Company</td>\n",
       "      <td>97.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72665</th>\n",
       "      <td>0.535537</td>\n",
       "      <td>4520_left</td>\n",
       "      <td>260_right</td>\n",
       "      <td>CA3010101</td>\n",
       "      <td>SANTA MARGARITA WATER DISTRICT</td>\n",
       "      <td>59</td>\n",
       "      <td>Santa Margarita Water District</td>\n",
       "      <td>78.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72731</th>\n",
       "      <td>0.462648</td>\n",
       "      <td>4525_left</td>\n",
       "      <td>243_right</td>\n",
       "      <td>CA4110023</td>\n",
       "      <td>CITY OF SAN BRUNO</td>\n",
       "      <td>81</td>\n",
       "      <td>San Bruno  City Of</td>\n",
       "      <td>60.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72752</th>\n",
       "      <td>0.267592</td>\n",
       "      <td>4529_left</td>\n",
       "      <td>92_right</td>\n",
       "      <td>CA1910038</td>\n",
       "      <td>EL MONTE-CITY, WATER DEPT.</td>\n",
       "      <td>37</td>\n",
       "      <td>El Monte  City Of</td>\n",
       "      <td>24.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72934</th>\n",
       "      <td>0.341550</td>\n",
       "      <td>4538_left</td>\n",
       "      <td>26_right</td>\n",
       "      <td>CA1310002</td>\n",
       "      <td>CALEXICO, CITY OF</td>\n",
       "      <td>25</td>\n",
       "      <td>Calexico  City Of</td>\n",
       "      <td>43.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73314</th>\n",
       "      <td>0.332512</td>\n",
       "      <td>4557_left</td>\n",
       "      <td>80_right</td>\n",
       "      <td>CA3610009</td>\n",
       "      <td>BIGHORN - DESERT VIEW WATER AGENCY</td>\n",
       "      <td>71</td>\n",
       "      <td>Desert Water Agency</td>\n",
       "      <td>36.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73443</th>\n",
       "      <td>0.449293</td>\n",
       "      <td>4564_left</td>\n",
       "      <td>89_right</td>\n",
       "      <td>CA3610064</td>\n",
       "      <td>EAST VALLEY WATER DISTRICT</td>\n",
       "      <td>71</td>\n",
       "      <td>East Valley Water District</td>\n",
       "      <td>67.26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73846</th>\n",
       "      <td>0.552113</td>\n",
       "      <td>4603_left</td>\n",
       "      <td>151_right</td>\n",
       "      <td>CA3010018</td>\n",
       "      <td>CITY OF LA HABRA</td>\n",
       "      <td>59</td>\n",
       "      <td>La Habra  City Of</td>\n",
       "      <td>61.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73851</th>\n",
       "      <td>0.471802</td>\n",
       "      <td>4608_left</td>\n",
       "      <td>158_right</td>\n",
       "      <td>CA5810002</td>\n",
       "      <td>LINDA COUNTY WATER DISTRICT</td>\n",
       "      <td>115</td>\n",
       "      <td>Linda County Water District</td>\n",
       "      <td>65.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73906</th>\n",
       "      <td>0.465896</td>\n",
       "      <td>4611_left</td>\n",
       "      <td>278_right</td>\n",
       "      <td>CA1910059</td>\n",
       "      <td>SUBURBAN WATER SYSTEMS-LA MIRADA</td>\n",
       "      <td>37</td>\n",
       "      <td>Suburban Water Systems  Whittierla Mirada</td>\n",
       "      <td>50.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74143</th>\n",
       "      <td>0.253984</td>\n",
       "      <td>4620_left</td>\n",
       "      <td>143_right</td>\n",
       "      <td>CA1910049</td>\n",
       "      <td>HUNTINGTON PARK-CITY, WATER DEPT.</td>\n",
       "      <td>37</td>\n",
       "      <td>Huntington Park  City Of</td>\n",
       "      <td>21.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74393</th>\n",
       "      <td>0.633908</td>\n",
       "      <td>4627_left</td>\n",
       "      <td>283_right</td>\n",
       "      <td>CA3710025</td>\n",
       "      <td>SWEETWATER AUTHORITY</td>\n",
       "      <td>73</td>\n",
       "      <td>Sweetwater Authority</td>\n",
       "      <td>54.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74395</th>\n",
       "      <td>0.438621</td>\n",
       "      <td>4629_left</td>\n",
       "      <td>280_right</td>\n",
       "      <td>CA1910157</td>\n",
       "      <td>SUNNY SLOPE WATER CO.</td>\n",
       "      <td>37</td>\n",
       "      <td>Sunny Slope Water Company</td>\n",
       "      <td>46.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74513</th>\n",
       "      <td>0.616079</td>\n",
       "      <td>4643_left</td>\n",
       "      <td>252_right</td>\n",
       "      <td>CA3410021</td>\n",
       "      <td>SAN JUAN WATER DISTRICT</td>\n",
       "      <td>67</td>\n",
       "      <td>San Juan Water District</td>\n",
       "      <td>75.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74515</th>\n",
       "      <td>0.645963</td>\n",
       "      <td>4645_left</td>\n",
       "      <td>208_right</td>\n",
       "      <td>CA3410016</td>\n",
       "      <td>ORANGE VALE WATER COMPANY</td>\n",
       "      <td>67</td>\n",
       "      <td>Orange Vale Water Company</td>\n",
       "      <td>35.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74652</th>\n",
       "      <td>0.359505</td>\n",
       "      <td>4649_left</td>\n",
       "      <td>278_right</td>\n",
       "      <td>CA1910174</td>\n",
       "      <td>SUBURBAN WATER SYSTEMS-WHITTIER</td>\n",
       "      <td>37</td>\n",
       "      <td>Suburban Water Systems  Whittierla Mirada</td>\n",
       "      <td>50.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74902</th>\n",
       "      <td>0.230584</td>\n",
       "      <td>4662_left</td>\n",
       "      <td>212_right</td>\n",
       "      <td>CA4010007</td>\n",
       "      <td>PASO ROBLES WATER DEPARTMENT</td>\n",
       "      <td>79</td>\n",
       "      <td>Paso Robles  City Of</td>\n",
       "      <td>56.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75027</th>\n",
       "      <td>0.331516</td>\n",
       "      <td>4669_left</td>\n",
       "      <td>107_right</td>\n",
       "      <td>CA3410011</td>\n",
       "      <td>GALT, CITY OF</td>\n",
       "      <td>67</td>\n",
       "      <td>Galt  City Of</td>\n",
       "      <td>33.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75093</th>\n",
       "      <td>0.468706</td>\n",
       "      <td>4674_left</td>\n",
       "      <td>117_right</td>\n",
       "      <td>CA3410015</td>\n",
       "      <td>GOLDEN STATE WATER CO. - CORDOVA</td>\n",
       "      <td>67</td>\n",
       "      <td>Golden State Water Company  Cordova</td>\n",
       "      <td>38.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75719</th>\n",
       "      <td>0.301413</td>\n",
       "      <td>4704_left</td>\n",
       "      <td>69_right</td>\n",
       "      <td>CA3610014</td>\n",
       "      <td>COLTON, CITY OF</td>\n",
       "      <td>71</td>\n",
       "      <td>Colton  City Of</td>\n",
       "      <td>44.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75937</th>\n",
       "      <td>0.341550</td>\n",
       "      <td>4715_left</td>\n",
       "      <td>232_right</td>\n",
       "      <td>CA3310031</td>\n",
       "      <td>RIVERSIDE, CITY OF</td>\n",
       "      <td>65</td>\n",
       "      <td>Riverside  City Of</td>\n",
       "      <td>28.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76169</th>\n",
       "      <td>0.541362</td>\n",
       "      <td>4731_left</td>\n",
       "      <td>164_right</td>\n",
       "      <td>CA1910075</td>\n",
       "      <td>LOS ANGELES CO WW DIST 21-KAGEL CANYON</td>\n",
       "      <td>37</td>\n",
       "      <td>Los Angeles County Waterworks District 21  Kag...</td>\n",
       "      <td>127.91</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>267 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       best_match_score  __id_left __id_right      pwsid  \\\n",
       "729            0.388590    51_left  134_right  CA3710010   \n",
       "999            0.462648    69_left  244_right  CA3010036   \n",
       "1054           0.439750    71_left  104_right  CA3010069   \n",
       "1234           0.227250    80_left  250_right  CA1910039   \n",
       "1237           0.295424    81_left  222_right  CA3710016   \n",
       "1366           0.450837    94_left  138_right  CA4110016   \n",
       "1641           0.231470   106_left  267_right  CA5400536   \n",
       "1834           0.341550   114_left  132_right  CA0110006   \n",
       "2006           0.305135   123_left  122_right  CA3010035   \n",
       "2040           0.331516   127_left   72_right  CA3310037   \n",
       "2917           0.493754   179_left  174_right  CA2110002   \n",
       "3101           0.341550   201_left  105_right  CA1010007   \n",
       "3159           0.790753   204_left  277_right  CA1910205   \n",
       "3435           0.505515   215_left  266_right  CA4510006   \n",
       "3535           0.447941   227_left  157_right  CA3910015   \n",
       "5281           0.209540   324_left  185_right  CA5010005   \n",
       "5490           0.326279   337_left  298_right  CA1909006   \n",
       "5658           0.351545   342_left  267_right  CA1500447   \n",
       "5783           0.481873   350_left   21_right  CA3010003   \n",
       "5911           0.305538   362_left  267_right  CA1200588   \n",
       "6231           0.867887   384_left  236_right  CA1910140   \n",
       "6234           0.341550   387_left  177_right  CA0710006   \n",
       "6280           0.287661   393_left   14_right  CA1910156   \n",
       "7945           0.379744   478_left  145_right  CA1510017   \n",
       "9094           0.505515   544_left   57_right  CA3610036   \n",
       "9913           0.557228   603_left  318_right  CA4910017   \n",
       "10357          0.251272   625_left  248_right  CA1910144   \n",
       "10737          0.283222   655_left  300_right  CA4900871   \n",
       "11231          0.341550   685_left  133_right  CA4910005   \n",
       "11232          0.295245   686_left  303_right  CA5610018   \n",
       "...                 ...        ...        ...        ...   \n",
       "71796          0.368522  4446_left   81_right  CA0710007   \n",
       "72085          0.550287  4461_left  142_right  CA3010053   \n",
       "72086          0.341550  4462_left  265_right  CA1510019   \n",
       "72087          0.290919  4463_left  199_right  CA1907014   \n",
       "72259          0.321481  4485_left   62_right  CA3710014   \n",
       "72448          0.341550  4507_left  106_right  CA3010010   \n",
       "72603          0.260677  4514_left   57_right  CA3610012   \n",
       "72613          0.321481  4517_left    7_right  CA1210001   \n",
       "72614          0.506243  4518_left    5_right  CA3610118   \n",
       "72665          0.535537  4520_left  260_right  CA3010101   \n",
       "72731          0.462648  4525_left  243_right  CA4110023   \n",
       "72752          0.267592  4529_left   92_right  CA1910038   \n",
       "72934          0.341550  4538_left   26_right  CA1310002   \n",
       "73314          0.332512  4557_left   80_right  CA3610009   \n",
       "73443          0.449293  4564_left   89_right  CA3610064   \n",
       "73846          0.552113  4603_left  151_right  CA3010018   \n",
       "73851          0.471802  4608_left  158_right  CA5810002   \n",
       "73906          0.465896  4611_left  278_right  CA1910059   \n",
       "74143          0.253984  4620_left  143_right  CA1910049   \n",
       "74393          0.633908  4627_left  283_right  CA3710025   \n",
       "74395          0.438621  4629_left  280_right  CA1910157   \n",
       "74513          0.616079  4643_left  252_right  CA3410021   \n",
       "74515          0.645963  4645_left  208_right  CA3410016   \n",
       "74652          0.359505  4649_left  278_right  CA1910174   \n",
       "74902          0.230584  4662_left  212_right  CA4010007   \n",
       "75027          0.331516  4669_left  107_right  CA3410011   \n",
       "75093          0.468706  4674_left  117_right  CA3410015   \n",
       "75719          0.301413  4704_left   69_right  CA3610014   \n",
       "75937          0.341550  4715_left  232_right  CA3310031   \n",
       "76169          0.541362  4731_left  164_right  CA1910075   \n",
       "\n",
       "                                           name  county_fips  \\\n",
       "729                        HELIX WATER DISTRICT           73   \n",
       "999                        CITY OF SAN CLEMENTE           59   \n",
       "1054                    CITY OF FOUNTAIN VALLEY           59   \n",
       "1234      SAN GABRIEL VALLEY WATER CO.-EL MONTE           37   \n",
       "1237                       RAINBOW MUNICIPAL WD           73   \n",
       "1366                       TOWN OF HILLSBOROUGH           81   \n",
       "1641   KINGS RIVER ESTATES MUTUAL WATER COMPANY          107   \n",
       "1834                            CITY OF HAYWARD            1   \n",
       "2006                GOLDEN STATE WC - PLACENTIA           59   \n",
       "2040                            CORONA, CITY OF           65   \n",
       "2917             MARIN MUNICIPAL WATER DISTRICT           41   \n",
       "3101                             CITY OF FRESNO           19   \n",
       "3159            SUBURBAN WATER SYSTEMS-SAN JOSE           37   \n",
       "3435                        CITY OF SHASTA LAKE           89   \n",
       "3535                            CITY OF LATHROP           77   \n",
       "5281                    CITY OF MODESTO, SALIDA           99   \n",
       "5490          WEST VALLEY COUNTY WATER DISTRICT           37   \n",
       "5658         SIERRA BREEZE MUTUAL WATER COMPANY           29   \n",
       "5783                         CITY OF BUENA PARK           59   \n",
       "5911       SEAWOOD ESTATES MUTUAL WATER COMPANY           23   \n",
       "6231       RUBIO CANON LAND & WATER ASSOCIATION           37   \n",
       "6234                           CITY OF MARTINEZ           13   \n",
       "6280            BEVERLY HILLS-CITY, WATER DEPT.           37   \n",
       "7945                   INDIAN WELLS VALLEY W.D.           29   \n",
       "9094                       CHINO HILLS, CITY OF           71   \n",
       "9913                           WINDSOR, TOWN OF           97   \n",
       "10357                     SAN GABRIEL COUNTY WD           37   \n",
       "10737         DE ANZA MOON VALLEY WATER COMPANY           97   \n",
       "11231                       HEALDSBURG, CITY OF           97   \n",
       "11232             VENTURA CWWD NO. 1 - MOORPARK          111   \n",
       "...                                         ...          ...   \n",
       "71796                     DIABLO WATER DISTRICT           13   \n",
       "72085                  CITY OF HUNTINGTON BEACH           59   \n",
       "72086                          SHAFTER, CITY OF           29   \n",
       "72087         NORTH TRAILS MUTUAL WATER COMPANY           37   \n",
       "72259                        OCEANSIDE, CITY OF           73   \n",
       "72448                         CITY OF FULLERTON           59   \n",
       "72603                            CHINO, CITY OF           71   \n",
       "72613                           ARCATA, CITY OF           23   \n",
       "72614     APPLE VALLEY RANCHOS WATER CO - YERMO           71   \n",
       "72665            SANTA MARGARITA WATER DISTRICT           59   \n",
       "72731                         CITY OF SAN BRUNO           81   \n",
       "72752                EL MONTE-CITY, WATER DEPT.           37   \n",
       "72934                         CALEXICO, CITY OF           25   \n",
       "73314        BIGHORN - DESERT VIEW WATER AGENCY           71   \n",
       "73443                EAST VALLEY WATER DISTRICT           71   \n",
       "73846                          CITY OF LA HABRA           59   \n",
       "73851               LINDA COUNTY WATER DISTRICT          115   \n",
       "73906          SUBURBAN WATER SYSTEMS-LA MIRADA           37   \n",
       "74143         HUNTINGTON PARK-CITY, WATER DEPT.           37   \n",
       "74393                      SWEETWATER AUTHORITY           73   \n",
       "74395                     SUNNY SLOPE WATER CO.           37   \n",
       "74513                   SAN JUAN WATER DISTRICT           67   \n",
       "74515                 ORANGE VALE WATER COMPANY           67   \n",
       "74652           SUBURBAN WATER SYSTEMS-WHITTIER           37   \n",
       "74902              PASO ROBLES WATER DEPARTMENT           79   \n",
       "75027                             GALT, CITY OF           67   \n",
       "75093          GOLDEN STATE WATER CO. - CORDOVA           67   \n",
       "75719                           COLTON, CITY OF           71   \n",
       "75937                        RIVERSIDE, CITY OF           65   \n",
       "76169    LOS ANGELES CO WW DIST 21-KAGEL CANYON           37   \n",
       "\n",
       "                                           provider_name  total_charge  \n",
       "729                                 Helix Water District         64.25  \n",
       "999                                San Clemente  City Of         54.47  \n",
       "1054                            Fountain Valley  City Of         33.24  \n",
       "1234                    San Gabriel Valley Water Company         62.20  \n",
       "1237                    Rainbow Municipal Water District        133.90  \n",
       "1366                               Hillsborough  Town Of        241.20  \n",
       "1641                 Sierra Estates Mutual Water Company         69.59  \n",
       "1834                                    Hayward  City Of         49.22  \n",
       "2006               Golden State Water Company  Placentia         64.15  \n",
       "2040                                      Corona City Of         52.72  \n",
       "2917                      Marin Municipal Water District         55.46  \n",
       "3101                                      Fresno City Of         28.08  \n",
       "3159              Suburban Water Systems  San Jose Hills         61.22  \n",
       "3435                                Shasta Lake  City Of         46.10  \n",
       "3535                                    Lathrop  City Of         67.67  \n",
       "5281                                    Modesto  City Of         49.59  \n",
       "5490                        Valley County Water District         28.59  \n",
       "5658                 Sierra Estates Mutual Water Company         69.59  \n",
       "5783                                 Buena Park  City Of         56.10  \n",
       "5911                 Sierra Estates Mutual Water Company         69.59  \n",
       "6231             Rubio Canyon Land And Water Association         75.47  \n",
       "6234                                   Martinez  City Of         51.73  \n",
       "6280                               Beverly Hills City Of         74.51  \n",
       "7945                  Indian Wells Valley Water District         52.28  \n",
       "9094                                Chino Hills  City Of         59.98  \n",
       "9913                                    Windsor  Town Of         28.84  \n",
       "10357                  San Gabriel County Water District         38.31  \n",
       "10737                  Valley Of The Moon Water District         49.54  \n",
       "11231                                Healdsburg  City Of         65.11  \n",
       "11232  Ventura County Waterworks District No 01  Moor...         67.49  \n",
       "...                                                  ...           ...  \n",
       "71796                              Diablo Water District         49.55  \n",
       "72085                           Huntington Beach City Of         28.93  \n",
       "72086                                   Shafter  City Of         44.70  \n",
       "72087               North Lone Pine Mutual Water Company         12.50  \n",
       "72259                                  City Of Oceanside         34.98  \n",
       "72448                                  Fullerton City Of         36.56  \n",
       "72603                               Chino Hills  City Of         59.98  \n",
       "72613                                    Arcata  City Of         26.25  \n",
       "72614                 Apple Valley Ranchos Water Company         97.87  \n",
       "72665                     Santa Margarita Water District         78.39  \n",
       "72731                                 San Bruno  City Of         60.89  \n",
       "72752                                  El Monte  City Of         24.08  \n",
       "72934                                  Calexico  City Of         43.89  \n",
       "73314                                Desert Water Agency         36.36  \n",
       "73443                         East Valley Water District         67.26  \n",
       "73846                                  La Habra  City Of         61.58  \n",
       "73851                        Linda County Water District         65.40  \n",
       "73906          Suburban Water Systems  Whittierla Mirada         50.22  \n",
       "74143                           Huntington Park  City Of         21.46  \n",
       "74393                               Sweetwater Authority         54.77  \n",
       "74395                          Sunny Slope Water Company         46.30  \n",
       "74513                            San Juan Water District         75.43  \n",
       "74515                          Orange Vale Water Company         35.60  \n",
       "74652          Suburban Water Systems  Whittierla Mirada         50.22  \n",
       "74902                               Paso Robles  City Of         56.57  \n",
       "75027                                      Galt  City Of         33.73  \n",
       "75093                Golden State Water Company  Cordova         38.74  \n",
       "75719                                    Colton  City Of         44.72  \n",
       "75937                                 Riverside  City Of         28.28  \n",
       "76169  Los Angeles County Waterworks District 21  Kag...        127.91  \n",
       "\n",
       "[267 rows x 8 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "JOIN_2.loc[lambda d: d.best_match_score > 0.2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 4746 entries, 0 to 76438\n",
      "Data columns (total 8 columns):\n",
      "best_match_score    3361 non-null float64\n",
      "__id_left           4746 non-null object\n",
      "__id_right          3361 non-null object\n",
      "pwsid               4746 non-null object\n",
      "name                4746 non-null object\n",
      "county_fips         4746 non-null int64\n",
      "provider_name       3361 non-null category\n",
      "total_charge        3361 non-null float64\n",
      "dtypes: category(1), float64(2), int64(1), object(4)\n",
      "memory usage: 318.4+ KB\n"
     ]
    }
   ],
   "source": [
    "JOIN_2.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x7f296da54518>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYIAAAD8CAYAAAB6paOMAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMi4zLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvIxREBQAAE11JREFUeJzt3XuQXnddx/H3pw21FItt6bbGlpiiscIwQuvaqeINQh1stakzLZbxEplIHG/cvBDQEbzNBEVRR0YIFg0o0ItAIkWxxCLqQGFLEXqBSSmhhMZmxZYWipTWr388JxrLXs5m95wnz573a2bnOec859nz/WW3/ezv9zuXVBWSpOE6ZtwFSJLGyyCQpIEzCCRp4AwCSRo4g0CSBs4gkKSBMwgkaeAMAkkaOINAkgZuzbgLaOPUU0+t9evXj7sMSZooN954439U1dRi+01EEKxfv56ZmZlxlyFJEyXJp9vs1+nQUJIXJbklyc1J3pLk+CRnJbkhyd4kVyY5rssaJEkL6ywIkpwBPB+YrqonA8cClwOvBF5dVRuAe4AtXdUgSVpc15PFa4BHJ1kDnAAcAJ4BXNO8vxO4pOMaJEkL6CwIquqzwKuAOxkFwOeBG4F7q+qhZrf9wBlzfT7J1iQzSWZmZ2e7KlOSBq/LoaGTgU3AWcA3AI8BfnCOXed8IEJV7aiq6aqanppadNJbknSEuhwaeibwqaqaraqvAG8Dvgs4qRkqAjgTuKvDGiRJi+gyCO4Ezk9yQpIAG4FbgeuBS5t9NgO7OqxBkrSILucIbmA0Kfxh4GPNsXYALwFenOR24HHAFV3VIElaXKcXlFXVy4GXP2LzHcB5XR5XktTeRFxZPInWb7t23vf2bb+ox0okaWHedE6SBs4gkKSBMwgkaeAMAkkaOINAkgbOIJCkgTMIJGngDAJJGjiDQJIGziCQpIEzCCRp4AwCSRo4g0CSBs4gkKSBMwgkaeAMAkkauM6CIMnZST5y2Nd9SV6Y5JQk1yXZ27ye3FUNkqTFdfaEsqr6BPBUgCTHAp8F3g5sA/ZU1fYk25r1l3RVx3xPCvMpYZI00tfQ0Ebgk1X1aWATsLPZvhO4pKcaJElz6CsILgfe0iyfXlUHAJrX03qqQZI0h84fXp/kOOBi4KVL/NxWYCvAunXrVrwuh4wkaaSPHsEPAh+uqrub9buTrAVoXg/O9aGq2lFV01U1PTU11UOZkjRMfQTBc/i/YSGA3cDmZnkzsKuHGiRJ8+g0CJKcAFwAvO2wzduBC5Lsbd7b3mUNkqSFdTpHUFUPAI97xLbPMTqLSJJ0FPDKYkkaOINAkgbOIJCkgev8OoJJ4/UFkobGHoEkDZxBIEkDZxBI0sAZBJI0cAaBJA2cQSBJA2cQSNLAGQSSNHAGgSQNnEEgSQNnEEjSwBkEkjRw3nRuDLyxnaSjiT0CSRq4rp9ZfFKSa5J8PMltSb4zySlJrkuyt3k9ucsaJEkL67pH8MfA31fVtwJPAW4DtgF7qmoDsKdZlySNSWdBkOSxwPcCVwBU1YNVdS+wCdjZ7LYTuKSrGiRJi+uyR/AEYBb4iyQ3JfnzJI8BTq+qAwDN62kd1iBJWkSXQbAGOBf4s6o6B/giSxgGSrI1yUySmdnZ2a5qlKTB6zII9gP7q+qGZv0aRsFwd5K1AM3rwbk+XFU7qmq6qqanpqY6LFOShq2zIKiqfwc+k+TsZtNG4FZgN7C52bYZ2NVVDZKkxXV9QdkvAn+d5DjgDuC5jMLnqiRbgDuByzquQZK0gE6DoKo+AkzP8dbGLo8rSWrPK4slaeAMAkkaOINAkgbOIJCkgTMIJGngDAJJGjiDQJIGziCQpIEzCCRp4AwCSRo4g0CSBs4gkKSBMwgkaeAMAkkaOINAkgbOIJCkgev6CWXSgtZvu3bO7fu2X9RzJdJwdRoESfYB9wMPAw9V1XSSU4ArgfXAPuDZVXVPl3VIkubXx9DQ06vqqVV16JGV24A9VbUB2NOsS5LGZBxzBJuAnc3yTuCSMdQgSWp0HQQF/EOSG5NsbbadXlUHAJrX0zquQZK0gFZzBEmeXFU3H8H3f1pV3ZXkNOC6JB9v+8EmOLYCrFu37ggOLUlqo22P4LVJPpjk55Kc1PabV9VdzetB4O3AecDdSdYCNK8H5/nsjqqarqrpqamptoeUJC1RqyCoqu8Gfgx4PDCT5M1JLljoM0kek+TEQ8vADwA3A7uBzc1um4FdR1i7JGkFtD59tKr2Jvl1YAb4E+CcJAFeVlVvm+MjpwNvH+3CGuDNVfX3ST4EXJVkC3AncNlyGyFJOnJt5wi+DXgucBFwHfDDVfXhJN8AvB/4qiCoqjuAp8yx/XPAxuUULUlaOW17BH8KvJ7RX/9fOrSxmQj+9U4qkyT1om0QXAh8qaoeBkhyDHB8VT1QVW/qrDpJUufanjX0HuDRh62f0GyTJE24tkFwfFV94dBKs3xCNyVJkvrUNgi+mOTcQytJvh340gL7S5ImRNs5ghcCVye5q1lfC/xoNyVJkvrUKgiq6kNJvhU4Gwjw8ar6SqeVSZJ6sZTnEXwHo2cIrGF0MRlV9cZOqpIk9abtBWVvAr4J+Aijh8zA6M6iBoEkTbi2PYJp4ElVVV0WI0nqX9uzhm4Gvr7LQiRJ49G2R3AqcGuSDwJfPrSxqi7upCpJUm/aBsEruixCkjQ+bU8f/ack3whsqKr3JDkBOLbb0iRJfWg1R5DkecA1wOuaTWcA7+iqKElSf9pOFv888DTgPhg9pAYfOi9Jq0LbIPhyVT14aCXJGkbXEUiSJlzbIPinJC8DHt08q/hq4G+7K0uS1Je2QbANmAU+BvwM8C6g1ZPJkhyb5KYk72zWz0pyQ5K9Sa5MctyRFC5JWhmtgqCq/ruqXl9Vl1XVpc1y26GhFwC3Hbb+SuDVVbUBuAfYsrSSJUkrqe1ZQ59Kcscjv1p87kxGD7z/82Y9wDMYnYEEsBO45MhKlySthKXca+iQ44HLgFNafO6PgF8FTmzWHwfcW1UPNev7GZ2KKkkak7ZDQ5877OuzVfVHjP6yn1eSHwIOVtWNh2+e69vP8/mtSWaSzMzOzrYpU5J0BNrehvrcw1aPYdRDOHGe3Q95GnBxkgsZ9SIey6iHcFKSNU2v4Ezgrrk+XFU7gB0A09PTnqoqSR1pOzT0B4ctPwTsA5690Aeq6qXASwGSfD/wy1X1Y0muBi4F3gpsBnYtrWRJ0kpqe6+hp6/gMV8CvDXJ7wA3AVes4PeWJC1R26GhFy/0flX94SLvvxd4b7N8B3Beu/IkSV1byllD3wHsbtZ/GHgf8Jkuipok67ddO+4SJGlZlvJgmnOr6n6AJK8Arq6qn+6qMElSP9reYmId8OBh6w8C61e8GklS79r2CN4EfDDJ2xmd9/8jwBs7q2qg5htm2rf9op4rkTQkbc8a+t0kfwd8T7PpuVV1U3dlSZL60nZoCOAE4L6q+mNgf5KzOqpJktSjtjedezmj8/9f2mx6FPBXXRUlSepP2x7BjwAXA18EqKq7WPwWE5KkCdA2CB5snj9QAEke011JkqQ+tQ2Cq5K8jtEN454HvAd4fXdlSZL60vasoVc1zyq+Dzgb+I2quq7TyiRJvVg0CJIcC7y7qp4J+D9/SVplFh0aqqqHgQeSfF0P9UiSetb2yuL/Aj6W5DqaM4cAqur5nVR1FPLmcpJWq7ZBcG3zJUlaZRYMgiTrqurOqtrZV0GSpH4tNkfwjkMLSf6m41okSWOwWBDksOUnLOUbJzk+yQeT/FuSW5L8ZrP9rCQ3JNmb5Mokxy21aEnSylksCGqe5Ta+DDyjqp4CPBV4VpLzgVcCr66qDcA9wJYlfl9J0gpaLAiekuS+JPcD39Ys35fk/iT3LfTBGvlCs/qo5quAZwDXNNt3Apcso35J0jItOFlcVccu55s3F6PdCHwz8Brgk8C9VfVQs8t+4IzlHEOStDxLeR7BklXVw1X1VOBM4DzgiXPtNtdnk2xNMpNkZnZ2tssyJWnQOg2CQ6rqXuC9wPmMblx3qCdyJnDXPJ/ZUVXTVTU9NTXVR5mSNEidBUGSqSQnNcuPBp4J3AZcD1za7LYZ2NVVDZKkxbW9svhIrAV2NvMExwBXVdU7k9wKvDXJ7wA3AVd0WIMkaRGdBUFVfRQ4Z47tdzCaL5AkHQV6mSOQJB29DAJJGjiDQJIGziCQpIEzCCRp4AwCSRo4g0CSBs4gkKSBMwgkaeAMAkkaOINAkgbOIJCkgTMIJGngDAJJGrgun0egFbJ+27Vzbt+3/aKeK5G0GtkjkKSBMwgkaeAMAkkauC4fXv/4JNcnuS3JLUle0Gw/Jcl1SfY2ryd3VYMkaXFd9ggeAn6pqp4InA/8fJInAduAPVW1AdjTrEuSxqSzIKiqA1X14Wb5fuA24AxgE7Cz2W0ncElXNUiSFtfLHEGS9cA5wA3A6VV1AEZhAZzWRw2SpLl1HgRJvhb4G+CFVXXfEj63NclMkpnZ2dnuCpSkges0CJI8ilEI/HVVva3ZfHeStc37a4GDc322qnZU1XRVTU9NTXVZpiQNWmdXFicJcAVwW1X94WFv7QY2A9ub111d1aCjw3xXRks6OnR5i4mnAT8BfCzJR5ptL2MUAFcl2QLcCVzWYQ2SpEV0FgRV9S9A5nl7Y1fHlSQtjVcWS9LAGQSSNHAGgSQNnEEgSQNnEEjSwPmEsgHxSWeS5mKPQJIGziCQpIEzCCRp4AwCSRo4g0CSBs6zhrRk3k1UWl3sEUjSwBkEkjRwBoEkDZxBIEkD52Sx5rWaJ4WX2rb5bsPhbTu0GtgjkKSB6ywIkrwhycEkNx+27ZQk1yXZ27ye3NXxJUntdDk09JfAnwJvPGzbNmBPVW1Psq1Zf0mHNaiF1TwEdLRxKElHo856BFX1PuA/H7F5E7CzWd4JXNLV8SVJ7fQ9WXx6VR0AqKoDSU6bb8ckW4GtAOvWreupPK029nakxR21k8VVtaOqpqtqempqatzlSNKq1XcQ3J1kLUDzerDn40uSHqHvoaHdwGZge/O6q+fjryoOe6weC/0snUhW17o8ffQtwPuBs5PsT7KFUQBckGQvcEGzLkkao856BFX1nHne2tjVMSVJS3fUThZLkvphEEjSwHnTOakFJ+a1mtkjkKSBMwgkaeAcGtJEcYhGWnn2CCRp4AwCSRo4h4akCeWzDbRS7BFI0sDZI9BRadInhSe9fg2LPQJJGjiDQJIGzqEh6Si3UsNMTi7/H/8t/j97BJI0cPYIpFVmUnoQR1LnUP9i75o9AkkauLEEQZJnJflEktuTbBtHDZKkkd6HhpIcC7yG0TOL9wMfSrK7qm7tuxZJSx+iWeqQUR/XVDj5uzzj6BGcB9xeVXdU1YPAW4FNY6hDksR4guAM4DOHre9vtkmSxmAcZw1ljm31VTslW4GtzeoXknziCI93KvAfR/jZSWWbh+GoanNe2csxltTmpdbURxuWaLk/429ss9M4gmA/8PjD1s8E7nrkTlW1A9ix3IMlmamq6eV+n0lim4fBNq9+fbV3HENDHwI2JDkryXHA5cDuMdQhSWIMPYKqeijJLwDvBo4F3lBVt/RdhyRpZCxXFlfVu4B39XS4ZQ8vTSDbPAy2efXrpb2p+qp5WknSgHiLCUkauFUTBIvdtiLJ1yS5snn/hiTr+69yZbVo84uT3Jrko0n2JGl1KtnRrO3tSZJcmqSSTPQZJm3am+TZzc/5liRv7rvGldbi93pdkuuT3NT8bl84jjpXUpI3JDmY5OZ53k+SP2n+TT6a5NwVLaCqJv6L0aTzJ4EnAMcB/wY86RH7/Bzw2mb5cuDKcdfdQ5ufDpzQLP/sENrc7Hci8D7gA8D0uOvu+Ge8AbgJOLlZP23cdffQ5h3AzzbLTwL2jbvuFWj39wLnAjfP8/6FwN8xug7rfOCGlTz+aukRtLltxSZgZ7N8DbAxyVwXt02KRdtcVddX1QPN6gcYXbMxydrenuS3gd8D/qvP4jrQpr3PA15TVfcAVNXBnmtcaW3aXMBjm+WvY47rkCZNVb0P+M8FdtkEvLFGPgCclGTtSh1/tQRBm9tW/O8+VfUQ8Hngcb1U142l3qpjC6O/KCbZom1Ocg7w+Kp6Z5+FdaTNz/hbgG9J8q9JPpDkWb1V1402bX4F8ONJ9jM6+/AX+yltrDq9Nc9qeTBNm9tWtLq1xQRp3Z4kPw5MA9/XaUXdW7DNSY4BXg38VF8FdazNz3gNo+Gh72fU4/vnJE+uqns7rq0rbdr8HOAvq+oPknwn8Kamzf/dfXlj0+n/v1ZLj6DNbSv+d58kaxh1KRfqih3tWt2qI8kzgV8DLq6qL/dUW1cWa/OJwJOB9ybZx2gsdfcETxi3/b3eVVVfqapPAZ9gFAyTqk2btwBXAVTV+4HjGd2TZzVr9d/7kVotQdDmthW7gc3N8qXAP1YzCzOhFm1zM0zyOkYhMOljx7BIm6vq81V1alWtr6r1jOZFLq6qmfGUu2xtfq/fweikAJKcymio6I5eq1xZbdp8J7ARIMkTGQXBbK9V9m838JPN2UPnA5+vqgMr9c1XxdBQzXPbiiS/BcxU1W7gCkZdyNsZ9QQuH1/Fy9eyzb8PfC1wdTMvfmdVXTy2opepZZtXjZbtfTfwA0luBR4GfqWqPje+qpenZZt/CXh9khcxGh75qQn/o44kb2E0vHdqM/fxcuBRAFX1WkZzIRcCtwMPAM9d0eNP+L+fJGmZVsvQkCTpCBkEkjRwBoEkDZxBIEkDZxBI0sAZBJI0cAaBJA2cQSBJA/c/HdiASbR3llgAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "JOIN_2.best_match_score.plot.hist(range=(0, 1), bins=50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x7f29a9410390>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAYIAAAD8CAYAAAB6paOMAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADl0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uIDIuMi4zLCBodHRwOi8vbWF0cGxvdGxpYi5vcmcvIxREBQAAEshJREFUeJzt3X+QZXV95vH344ABEwywNDpBZkdTaKSodWBbii2TqIgpQhLBLZNIrVl2l3U0ibtrJbUVllgJiZsq3VLZpGIlTiJxwkajaFSiuFkkGtaUgI2O/BAT/MEawhQzblQkGhD45I97JukM3dPnjn3O6eb7flXd6nPPPfeeZ+5099Pn1/emqpAktetxUweQJE3LIpCkxlkEktQ4i0CSGmcRSFLjLAJJapxFIEmNswgkqXEWgSQ17oipA/Rxwgkn1Pbt26eOIUmbys033/zlqlpYa7lNUQTbt29naWlp6hiStKkk+X99lnPXkCQ1ziKQpMZZBJLUOItAkhpnEUhS4ywCSWqcRSBJjbMIJKlxFoEkNW5TXFks6dG2X/LBFeff9bofGTmJNju3CCSpcRaBJDXOIpCkxlkEktQ4i0CSGmcRSFLjBiuCJEcluSnJp5PcnuRXuvlvS/LFJHu6246hMkiS1jbkdQQPAGdX1f1JjgQ+luRD3WP/tarePeC6JUk9DVYEVVXA/d3dI7tbDbU+SdLhGfQYQZItSfYA+4Brq+rG7qFfS3JLksuTfMeQGSRJhzZoEVTVw1W1A3gKcGaS04D/Bnwf8GzgeOAXVnpukp1JlpIs7d+/f8iYktS0Uc4aqqqvAh8Fzq2qvTXzAPB7wJmrPGdXVS1W1eLCwsIYMSWpSUOeNbSQ5Nhu+mjgHOCzSbZ28wJcANw2VAZJ0tqGPGtoK7A7yRZmhfOuqvpAkj9NsgAE2AO8csAMkqQ1DHnW0C3A6SvMP3uodUqS5ueVxZLUOItAkhpnEUhS4ywCSWqcRSBJjbMIJKlxFoEkNc4ikKTGWQSS1DiLQJIaZxFIUuMsAklqnEUgSY2zCCSpcRaBJDXOIpCkxlkEktQ4i0CSGmcRSFLjBiuCJEcluSnJp5PcnuRXuvlPTXJjkjuTvDPJ44fKIEla25BbBA8AZ1fVs4AdwLlJzgJeD1xeVacAXwEuHjCDJGkNgxVBzdzf3T2yuxVwNvDubv5u4IKhMkiS1jboMYIkW5LsAfYB1wKfB75aVQ91i9wNnDRkBknSoQ1aBFX1cFXtAJ4CnAk8c6XFVnpukp1JlpIs7d+/f8iYktS0Uc4aqqqvAh8FzgKOTXJE99BTgHtWec6uqlqsqsWFhYUxYkpSk4Y8a2ghybHd9NHAOcAdwEeAl3SLXQS8f6gMkqS1HbH2IodtK7A7yRZmhfOuqvpAks8Af5jkvwOfAt46YAZJ0hoGK4KqugU4fYX5X2B2vECStAF4ZbEkNc4ikKTGWQSS1DiLQJIaZxFIUuMsAklqnEUgSY2zCCSpcRaBJDXOIpCkxlkEktQ4i0CSGmcRSFLjLAJJapxFIEmNswgkqXEWgSQ1ziKQpMZZBJLUuMGKIMnJST6S5I4ktyf5L938y5L8dZI93e28oTJIktY22IfXAw8BP19Vn0xyDHBzkmu7xy6vqjcMuG5JUk+DFUFV7QX2dtNfT3IHcNJQ65MkHZ5RjhEk2Q6cDtzYzXpVkluSXJHkuDEySJJWNngRJPku4D3Aq6vqPuC3gO8FdjDbYnjjKs/bmWQpydL+/fuHjilJzRq0CJIcyawE/qCq/gigqu6tqoer6hHgd4AzV3puVe2qqsWqWlxYWBgypiQ1bcizhgK8Fbijqt60bP7WZYu9GLhtqAySpLUNedbQc4CfAm5NsqebdylwYZIdQAF3Aa8YMIMkaQ1DnjX0MSArPHTNUOuUJM3PK4slqXEWgSQ1rlcRJDlt6CCSpGn03SL47SQ3JfmZJMcOmkiSNKpeRVBV3w/8G+BkYCnJ25O8cNBkkqRR9D5GUFV3Aq8BfgF4LvAbST6b5F8PFU6SNLy+xwj+RZLLgTuAs4Efq6pndtOXD5hPkjSwvtcR/Caz4SAurapvHphZVfckec0gySRJo+hbBOcB36yqhwGSPA44qqq+UVVXDpZOkjS4vscIPgwcvez+E7p5kqRNrm8RHFVV9x+4000/YZhIkqQx9S2Cv01yxoE7Sf4l8M1DLC9J2iT6HiN4NXBVknu6+1uBnxwmkiRpTL2KoKo+keT7gGcwG1H0s1X1rUGTSZJGMc8w1M8GtnfPOT0JVfX7g6SSJI2mVxEkuZLZ5wzvAR7uZhdgEUjSJtd3i2AROLWqasgwkqTx9T1r6DbgyUMGkSRNo+8WwQnAZ5LcBDxwYGZVvWiQVJKk0fQtgsvmfeEkJzM7hvBk4BFgV1X9epLjgXcyO/B8F/ATVfWVeV9fkrQ++n4ewZ8x+6V9ZDf9CeCTazztIeDnu1FKzwJ+NsmpwCXAdVV1CnBdd1+SNJG+w1C/HHg38JZu1knA+w71nKraW1Wf7Ka/zmwI65OA84Hd3WK7gQvmjy1JWi99Dxb/LPAc4D74hw+pObHvSpJsB04HbgSeVFV7u9fZO8/rSJLWX98ieKCqHjxwJ8kRzK4jWFOS7wLeA7y6qu7rGyzJziRLSZb279/f92mSpDn1LYI/S3IpcHT3WcVXAX+81pOSHMmsBP6gqv6om31vkq3d41uBfSs9t6p2VdViVS0uLCz0jClJmlffIrgE2A/cCrwCuIbZ5xevKkmAtwJ3VNWblj10NXBRN30R8P55AkuS1lffQeceYfZRlb8zx2s/B/gp4NYke7p5lwKvA96V5GLgS8CPz/GakqR11nesoS+ywjGBqnraas+pqo8xG6l0JS/olU6SNLh5xho64Chmf8Ufv/5xtJFsv+SDK86/63U/MnISSUPqe0HZ/192++uq+p/A2QNnkySNoO+uoTOW3X0csy2EYwZJJEkaVd9dQ29cNv0Q3RhB655GkjS6vmcNPX/oIJKkafTdNfRzh3r8oOsEJEmbyDxnDT2b2cVgAD8GXA/81RCh9O3xbB9J85jng2nO6EYRJcllwFVV9R+HCiZJGkffISa2AQ8uu/8gsw+WkSRtcn23CK4EbkryXmZXGL+Y2aePSZI2ub5nDf1akg8BP9DN+vdV9anhYkmSxtJ31xDAE4D7qurXgbuTPHWgTJKkEfU9ffSXmZ059Azg94Ajgf/FbIRRSQNa7Swwab303SJ4MfAi4G8BquoeHGJCkh4T+hbBg1VVdENRJ/nO4SJJksbUtwjeleQtwLFJXg58mPk+pEaStEH1PWvoDd1nFd/H7DjBL1XVtYMmkySNYs0iSLIF+JOqOgfwl78kPcasuWuoqh4GvpHku0fII0kaWd8ri/+O2YfQX0t35hBAVf3n1Z6Q5ArgR4F9VXVaN+8y4OXA/m6xS6vqmsPILUlaJ32L4IPdbR5vA36TRw9FcXlVvWHO15IkDeSQRZBkW1V9qap2z/vCVXV9ku2HG0ySNI61jhG878BEkves0zpfleSWJFckOW6dXlOSdJjW2jWUZdNPW4f1/RbwWmYXpr2W2Wch/4cVV5zsBHYCbNu2bR1Wrc3ED9eRxrPWFkGtMn1Yqureqnq4qh5hdkHamYdYdldVLVbV4sLCwre7aknSKtbaInhWkvuYbRkc3U3T3a+qeuI8K0uytar2dndfDNw2V1pJ0ro7ZBFU1ZbDfeEk7wCeB5yQ5G7gl4HnJdnBbOviLuAVh/v6kqT10ff00blV1YUrzH7rUOuTJB2ewYpA2sg8GC39o3k+oUyS9BhkEUhS4ywCSWqcRSBJjbMIJKlxnjXUEM+U2dhW+//ZLPz+2rzcIpCkxlkEktQ4i0CSGmcRSFLjLAJJapxnDWkUnlEibVxuEUhS4ywCSWqcRSBJjbMIJKlxHizWpDb7sAqPZf7ftMMtAklq3GBFkOSKJPuS3LZs3vFJrk1yZ/f1uKHWL0nqZ8gtgrcB5x407xLguqo6Bbiuuy9JmtBgRVBV1wN/c9Ds84Hd3fRu4IKh1i9J6mfsYwRPqqq9AN3XE0devyTpIBv2rKEkO4GdANu2bZs4jZZzuAjNw++XjW/sLYJ7k2wF6L7uW23BqtpVVYtVtbiwsDBaQElqzdhFcDVwUTd9EfD+kdcvSTrIkKePvgP4OPCMJHcnuRh4HfDCJHcCL+zuS5ImNNgxgqq6cJWHXjDUOiVJ89uwB4ullXjgUVp/DjEhSY2zCCSpcRaBJDXOIpCkxlkEktQ4i0CSGmcRSFLjLAJJapxFIEmNswgkqXEWgSQ1zrGGpEY4TpNW4xaBJDXOIpCkxlkEktQ4i0CSGufBYq2b1Q5GTrnujXggdMr3aSUbLY/G5xaBJDVuki2CJHcBXwceBh6qqsUpckiSpt019Pyq+vKE65ck4a4hSWreVEVQwP9JcnOSnRNlkCQx3a6h51TVPUlOBK5N8tmqun75Al1B7ATYtm3bFBmlTWmznAV0qJwb8Wyvx7JJtgiq6p7u6z7gvcCZKyyzq6oWq2pxYWFh7IiS1IzRiyDJdyY55sA08EPAbWPnkCTNTLFr6EnAe5McWP/bq+p/T5BDksQERVBVXwCeNfZ6JUkrS1VNnWFNi4uLtbS0NHWMTWOzHCzcTFY7eHk4Q1v4/7P+NtrB5Y0y5EmSm/tcsOt1BJLUOItAkhpnEUhS4ywCSWqcRSBJjfODaaQePNNnc9ooZ+9sdG4RSFLjLAJJapxFIEmNswgkqXEWgSQ1zrOGNgHPWNl8/D8b12Z5vw8n5xhnOLlFIEmNswgkqXEWgSQ1ziKQpMY95g8Wb8RLzDdiJqkl/gz+U24RSFLjJimCJOcm+Yskn0tyyRQZJEkzoxdBki3Am4EfBk4FLkxy6tg5JEkzU2wRnAl8rqq+UFUPAn8InD9BDkkS0xTBScBfLbt/dzdPkjSBKc4aygrz6lELJTuBnd3d+5P8xbqGeH3vRU8Avrye617NHJn6Gi37ADZzdtjc+ZvNPsDP4Lwelf/bzPTP+yw0RRHcDZy87P5TgHsOXqiqdgG7xgq1miRLVbU4dY7DYfbpbOb8Zp/OVPmn2DX0CeCUJE9N8njgpcDVE+SQJDHBFkFVPZTkVcCfAFuAK6rq9rFzSJJmJrmyuKquAa6ZYt2HYfLdU98Gs09nM+c3+3QmyZ+qRx2nlSQ1xCEmJKlxFsFBkhyf5Nokd3Zfj1thmR1JPp7k9iS3JPnJKbIuy3PIITuSfEeSd3aP35hk+/gpV9Yj+88l+Uz3Pl+XpNfpcGPoO1RKkpckqSQb6myWPvmT/ET3/t+e5O1jZ1xNj++bbUk+kuRT3ffOeVPkXEmSK5LsS3LbKo8nyW90/7ZbkpwxeKiq8rbsBvwP4JJu+hLg9Sss83TglG76e4C9wLET5d0CfB54GvB44NPAqQct8zPAb3fTLwXeOfX7PEf25wNP6KZ/ejNl75Y7BrgeuAFYnDr3nO/9KcCngOO6+ydOnXuO7LuAn+6mTwXumjr3smw/CJwB3LbK4+cBH2J2zdVZwI1DZ3KL4NHOB3Z307uBCw5eoKr+sqru7KbvAfYBC6Ml/Kf6DNmx/N/0buAFSVa6sG9sa2avqo9U1Te6uzcwu+5kI+g7VMprmf1x8XdjhuuhT/6XA2+uqq8AVNW+kTOupk/2Ap7YTX83K1yrNJWquh74m0Mscj7w+zVzA3Bskq1DZrIIHu1JVbUXoPt64qEWTnIms79KPj9CtpX0GbLjH5apqoeArwH/bJR0hzbvcCMXM/tLaSNYM3uS04GTq+oDYwbrqc97/3Tg6Un+PMkNSc4dLd2h9cl+GfCyJHczO0PxP40TbV2MPgzPY/6DaVaS5MPAk1d46BfnfJ2twJXARVX1yHpkOwx9huzoNazHBHrnSvIyYBF47qCJ+jtk9iSPAy4H/t1YgebU570/gtnuoecx2xL7v0lOq6qvDpxtLX2yXwi8raremORfAVd22af6OZ3H6D+vTRZBVZ2z2mNJ7k2ytar2dr/oV9wcTvJE4IPAa7rNt6n0GbLjwDJ3JzmC2abyoTZNx9JruJEk5zAr6edW1QMjZVvLWtmPAU4DPtrthXsycHWSF1XV0mgpV9f3++aGqvoW8MVuvK9TmI0OMKU+2S8GzgWoqo8nOYrZOD4bZffWofT6uVhP7hp6tKuBi7rpi4D3H7xANzTGe5ntx7tqxGwr6TNkx/J/00uAP63uqNTE1sze7V55C/CiDbSPGtbIXlVfq6oTqmp7VW1ndnxjo5QA9Pu+eR+zg/UkOYHZrqIvjJpyZX2yfwl4AUCSZwJHAftHTXn4rgb+bXf20FnA1w7srh7M1EfQN9qN2b7z64A7u6/Hd/MXgd/tpl8GfAvYs+y2Y8LM5wF/yew4xS92836V2S8emP0QXAV8DrgJeNrU7/Mc2T8M3Lvsfb566sx9sx+07EfZQGcN9XzvA7wJ+AxwK/DSqTPPkf1U4M+ZnVG0B/ihqTMvy/4OZmcafovZX/8XA68EXrnsfX9z92+7dYzvG68slqTGuWtIkhpnEUhS4ywCSWqcRSBJjbMIJKlxFoEkNc4ikKTGWQSS1Li/By8Up7iqbMueAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "JOIN_1.best_match_score.plot.hist(bins=50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "JOIN_2.to_csv('pws-matching-1.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[dataset `pws-rates-awwa`]: getting from loader...\n",
      "[dataset `pws-rates-awwa`]: processing...\n"
     ]
    }
   ],
   "source": [
    "ds.PWS_RATES().to_csv('pws-awwa-1.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>total_charge</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>provider_name</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Alameda County Water District</th>\n",
       "      <td>62.66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Amador Water Agency</th>\n",
       "      <td>45.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>American Canyon  City Of</th>\n",
       "      <td>55.63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Anaheim City Of</th>\n",
       "      <td>18.16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Antioch  City Of</th>\n",
       "      <td>53.72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Apple Valley Ranchos Water Company</th>\n",
       "      <td>97.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arcadia  City Of</th>\n",
       "      <td>42.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arcata  City Of</th>\n",
       "      <td>26.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arrowbear Park County Water District</th>\n",
       "      <td>86.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arroyo Grande  City Of</th>\n",
       "      <td>70.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bakersfield  City Of</th>\n",
       "      <td>32.83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bear State Water Works</th>\n",
       "      <td>18.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bellflowersomerset Mutual Water Company</th>\n",
       "      <td>30.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Benicia  City Of</th>\n",
       "      <td>44.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Beverly Hills City Of</th>\n",
       "      <td>74.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Big Bear City Community Service District</th>\n",
       "      <td>49.15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Big Bear Lake  City Of</th>\n",
       "      <td>79.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Blythe  City Of</th>\n",
       "      <td>40.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Brawley  City Of</th>\n",
       "      <td>57.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Brea  City Of</th>\n",
       "      <td>52.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Brentwood City Of</th>\n",
       "      <td>73.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Buena Park  City Of</th>\n",
       "      <td>56.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Burbank City Of</th>\n",
       "      <td>39.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Burlingame City Of</th>\n",
       "      <td>79.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Calaveras County Water District</th>\n",
       "      <td>96.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Calaveras Public Utilities District</th>\n",
       "      <td>51.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Calexico  City Of</th>\n",
       "      <td>43.89</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>California American Water Company  Sacramento District</th>\n",
       "      <td>66.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>California American Water Ventura District</th>\n",
       "      <td>73.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>California Water Service Company Antelope Valley</th>\n",
       "      <td>96.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Upland  City Of</th>\n",
       "      <td>61.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vacaville  City Of</th>\n",
       "      <td>41.97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Valencia Water Company</th>\n",
       "      <td>37.13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vallejo  City Of  Vallejo Service Area</th>\n",
       "      <td>85.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vallejo City Of  Lakes Service Area</th>\n",
       "      <td>238.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Valley County Water District</th>\n",
       "      <td>28.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Valley Estates Properties Owners Association</th>\n",
       "      <td>336.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Valley Of The Moon Water District</th>\n",
       "      <td>49.54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vaughn Water Company</th>\n",
       "      <td>50.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ventura City Of</th>\n",
       "      <td>91.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ventura County Waterworks District No 01  Moorpark</th>\n",
       "      <td>67.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vernon  City Of</th>\n",
       "      <td>21.19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Victorville Water District</th>\n",
       "      <td>37.61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Virgin Valley Water District</th>\n",
       "      <td>60.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Vista Irrigation District</th>\n",
       "      <td>76.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Walnut Valley Water District</th>\n",
       "      <td>63.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Watsonville  City Of</th>\n",
       "      <td>56.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West Sacramento  City Of</th>\n",
       "      <td>52.22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West San Martin Water Works</th>\n",
       "      <td>62.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>West Valley Water District</th>\n",
       "      <td>67.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Westborough Water District</th>\n",
       "      <td>52.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Western Municipal Water District</th>\n",
       "      <td>70.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Westhaven Community Services District</th>\n",
       "      <td>207.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Westminister  City Of</th>\n",
       "      <td>35.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Whittier  City Of</th>\n",
       "      <td>39.53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Windsor  Town Of</th>\n",
       "      <td>28.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Woodland  City Of</th>\n",
       "      <td>92.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yorba Linda Water District</th>\n",
       "      <td>66.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yreka  City Of</th>\n",
       "      <td>53.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Yucaipa Valley Water District</th>\n",
       "      <td>34.58</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>323 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    total_charge\n",
       "provider_name                                                   \n",
       "Alameda County Water District                              62.66\n",
       "Amador Water Agency                                        45.72\n",
       "American Canyon  City Of                                   55.63\n",
       "Anaheim City Of                                            18.16\n",
       "Antioch  City Of                                           53.72\n",
       "Apple Valley Ranchos Water Company                         97.87\n",
       "Arcadia  City Of                                           42.69\n",
       "Arcata  City Of                                            26.25\n",
       "Arrowbear Park County Water District                       86.00\n",
       "Arroyo Grande  City Of                                     70.94\n",
       "Bakersfield  City Of                                       32.83\n",
       "Bear State Water Works                                     18.77\n",
       "Bellflowersomerset Mutual Water Company                    30.47\n",
       "Benicia  City Of                                           44.56\n",
       "Beverly Hills City Of                                      74.51\n",
       "Big Bear City Community Service District                   49.15\n",
       "Big Bear Lake  City Of                                     79.06\n",
       "Blythe  City Of                                            40.21\n",
       "Brawley  City Of                                           57.19\n",
       "Brea  City Of                                              52.92\n",
       "Brentwood City Of                                          73.65\n",
       "Buena Park  City Of                                        56.10\n",
       "Burbank City Of                                            39.37\n",
       "Burlingame City Of                                         79.94\n",
       "Calaveras County Water District                            96.69\n",
       "Calaveras Public Utilities District                        51.28\n",
       "Calexico  City Of                                          43.89\n",
       "California American Water Company  Sacramento D...         66.54\n",
       "California American Water Ventura District                 73.47\n",
       "California Water Service Company Antelope Valley           96.37\n",
       "...                                                          ...\n",
       "Upland  City Of                                            61.64\n",
       "Vacaville  City Of                                         41.97\n",
       "Valencia Water Company                                     37.13\n",
       "Vallejo  City Of  Vallejo Service Area                     85.34\n",
       "Vallejo City Of  Lakes Service Area                       238.44\n",
       "Valley County Water District                               28.59\n",
       "Valley Estates Properties Owners Association              336.05\n",
       "Valley Of The Moon Water District                          49.54\n",
       "Vaughn Water Company                                       50.74\n",
       "Ventura City Of                                            91.44\n",
       "Ventura County Waterworks District No 01  Moorpark         67.49\n",
       "Vernon  City Of                                            21.19\n",
       "Victorville Water District                                 37.61\n",
       "Virgin Valley Water District                               60.05\n",
       "Vista Irrigation District                                  76.85\n",
       "Walnut Valley Water District                               63.09\n",
       "Watsonville  City Of                                       56.60\n",
       "West Sacramento  City Of                                   52.22\n",
       "West San Martin Water Works                                62.21\n",
       "West Valley Water District                                 67.46\n",
       "Westborough Water District                                 52.05\n",
       "Western Municipal Water District                           70.44\n",
       "Westhaven Community Services District                     207.27\n",
       "Westminister  City Of                                      35.30\n",
       "Whittier  City Of                                          39.53\n",
       "Windsor  Town Of                                           28.84\n",
       "Woodland  City Of                                          92.77\n",
       "Yorba Linda Water District                                 66.60\n",
       "Yreka  City Of                                             53.94\n",
       "Yucaipa Valley Water District                              34.58\n",
       "\n",
       "[323 rows x 1 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "RATES.set_index('provider_name')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_index_diff(df1, df2):\n",
    "    return df2.index.difference(df1.index)\n",
    "\n",
    "def get_with_fewest_nulls(df, source_columns=None):\n",
    "    return df[source_columns].bfill(axis=1).iloc[:, 0]\n",
    "\n",
    "def apply_corrections(df):\n",
    "    from functools import partial\n",
    "\n",
    "    return df.assign(provider_name=partial(get_with_fewest_nulls, source_columns=['match_corrected', 'provider_name']))\n",
    "\n",
    "def process_rates_match(df):\n",
    "    return (df\n",
    "            .pipe(apply_corrections)\n",
    "#             .assign()\n",
    "           )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we perfom a three-way match between the AWWA rates table and the PWS table, using the match table as an intermediate. The `PWS.RATES_MATCH` dataset is loaded from the corrected table, exported as a CSV file from an Airtable base after manual inspection and processing.\n",
    "\n",
    "A slighly modified version of this operation is included in the TODO:fill dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[dataset `pws-rates-match-2`]: getting from loader...\n",
      "[dataset `pws-rates-match-2`]: processing...\n",
      "[dataset `pws-rates-awwa`]: getting from loader...\n",
      "[dataset `pws-rates-awwa`]: processing...\n",
      "[dataset `pws-boundaries`]: getting from loader...\n",
      "[dataset `pws-boundaries`]: processing...\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>name_awwa2017</th>\n",
       "      <th>name_match_score</th>\n",
       "      <th>name_match_quality</th>\n",
       "      <th>area_km2</th>\n",
       "      <th>total_charge</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pwsid</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>CA1910087</th>\n",
       "      <td>METROPOLITAN WATER DIST. OF SO. CAL.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13625.885494</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA1310014</th>\n",
       "      <td>IMPERIAL IRRIGATION DISTRICT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2466.149873</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA3310001</th>\n",
       "      <td>COACHELLA VWD: COVE COMMUNITY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1910.107177</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA3310009</th>\n",
       "      <td>EASTERN MUNICIPAL WD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1436.033699</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA3310049</th>\n",
       "      <td>WESTERN MWD</td>\n",
       "      <td>Western Municipal Water District</td>\n",
       "      <td>0.07945</td>\n",
       "      <td>match</td>\n",
       "      <td>1342.762986</td>\n",
       "      <td>70.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA1910067</th>\n",
       "      <td>LOS ANGELES-CITY, DEPT. OF WATER &amp; POWER</td>\n",
       "      <td>Los Angeles Department Of Water And Power</td>\n",
       "      <td>0.41962</td>\n",
       "      <td>match</td>\n",
       "      <td>1284.632781</td>\n",
       "      <td>45.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA4310027</th>\n",
       "      <td>SANTA CLARA VALLEY WATER DISTRICT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1119.086752</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA0110010</th>\n",
       "      <td>ZONE 7 WATER AGENCY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1116.671117</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA0110005</th>\n",
       "      <td>EAST BAY MUD</td>\n",
       "      <td>East Bay Municipal Utility District</td>\n",
       "      <td>0.00226</td>\n",
       "      <td>match</td>\n",
       "      <td>1052.861384</td>\n",
       "      <td>51.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA4910020</th>\n",
       "      <td>SONOMA COUNTY WATER AGENCY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>932.600400</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA3610019</th>\n",
       "      <td>SAN BERNARDINO VALLEY WD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>912.896175</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA1210013</th>\n",
       "      <td>HUMBOLDT BAY MWD</td>\n",
       "      <td>Humboldt Bay Municipal Water District</td>\n",
       "      <td>0.11432</td>\n",
       "      <td>match</td>\n",
       "      <td>876.046864</td>\n",
       "      <td>29.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA3710020</th>\n",
       "      <td>SAN DIEGO, CITY OF</td>\n",
       "      <td>San Diego  City Of</td>\n",
       "      <td>0.44258</td>\n",
       "      <td>match</td>\n",
       "      <td>868.918811</td>\n",
       "      <td>53.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA5610046</th>\n",
       "      <td>UNITED WTR CONS DIST</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>860.023521</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA5610050</th>\n",
       "      <td>CALLEGUAS MUNICIPAL WATER DIST</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>852.959015</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA3310005</th>\n",
       "      <td>DESERT WATER AGENCY</td>\n",
       "      <td>Desert Water Agency</td>\n",
       "      <td>0.56249</td>\n",
       "      <td>match</td>\n",
       "      <td>833.936829</td>\n",
       "      <td>36.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA1510016</th>\n",
       "      <td>RAND COMMUNITIES WATER DISTRICT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>832.771007</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA1510022</th>\n",
       "      <td>WEST KERN WATER DISTRICT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>822.964925</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA5010043</th>\n",
       "      <td>STANISLAUS REGIONAL WATER AUTHORITY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>755.709284</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA0710003</th>\n",
       "      <td>CONTRA COSTA WATER DISTRICT</td>\n",
       "      <td>Contra Costa Water District</td>\n",
       "      <td>0.57585</td>\n",
       "      <td>match</td>\n",
       "      <td>562.842308</td>\n",
       "      <td>46.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA0910001</th>\n",
       "      <td>EL DORADO ID - MAIN</td>\n",
       "      <td>El Dorado Irrigation District  Main</td>\n",
       "      <td>0.35159</td>\n",
       "      <td>match</td>\n",
       "      <td>560.440948</td>\n",
       "      <td>52.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA5610500</th>\n",
       "      <td>CHANNEL ISLANDS NATIONAL PARK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>544.473790</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA1510032</th>\n",
       "      <td>CALIFORNIA CITY, CITY OF</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>526.648923</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA1910048</th>\n",
       "      <td>SANTA CLARITA VALLEY W.A.-IMPORTED DIVIS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>506.223366</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA5810006</th>\n",
       "      <td>NORTH YUBA WATER DISTRICT</td>\n",
       "      <td>North Yuba Water District</td>\n",
       "      <td>0.64534</td>\n",
       "      <td>match</td>\n",
       "      <td>501.383453</td>\n",
       "      <td>31.76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA1910253</th>\n",
       "      <td>CENTRAL BASIN MWD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>471.264055</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA3010092</th>\n",
       "      <td>IRVINE RANCH WATER DISTRICT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>466.794258</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA5010038</th>\n",
       "      <td>MODESTO IRRIGATION DISTRICT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>409.390184</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA3310038</th>\n",
       "      <td>RANCHO CALIFORNIA WATER DISTRICT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>401.312022</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA4210700</th>\n",
       "      <td>AMERICAN WATER O&amp;M, LLC - VAFB</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>400.854994</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA0900305</th>\n",
       "      <td>CAMP VIRNER RESTAURANT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.001117</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA0707521</th>\n",
       "      <td>BYRON CORNERS INC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.001106</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA5403061</th>\n",
       "      <td>RIVER VIEW RESTAURANT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.001092</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA0900548</th>\n",
       "      <td>MARK TWAIN MOTEL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.001072</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA4901453</th>\n",
       "      <td>MAURITSON LABOR HOUSE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.001039</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA4901175</th>\n",
       "      <td>OAK GROVE SCHOOL DISTRICT OFFICE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.001006</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA0706031</th>\n",
       "      <td>COUNTRY JUNCTION DELI</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000998</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA0706059</th>\n",
       "      <td>GAS N SAVE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000861</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA0500100</th>\n",
       "      <td>BURSON DONUT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000818</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA4901451</th>\n",
       "      <td>SPIDER WEB RANCH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000778</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA0900579</th>\n",
       "      <td>FAIRBRIDGE INN EXPRESS-MIDWAY</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000755</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA5000382</th>\n",
       "      <td>FARMERS' DEN /MARKET</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000731</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA0105002</th>\n",
       "      <td>RIVERS END MARINA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000690</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA0800850</th>\n",
       "      <td>SEA WEST RESTAURANT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000668</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA4901193</th>\n",
       "      <td>SCHELLVILLE GRILL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000649</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA0706054</th>\n",
       "      <td>BETHEL MARKET</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000550</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA1700733</th>\n",
       "      <td>JITTERBEAN COFFEE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000482</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA0400169</th>\n",
       "      <td>GRIDLEY GRILL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000404</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA4901412</th>\n",
       "      <td>CAST ESTATE VINEYARDS &amp; CELLARS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000398</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA1400102</th>\n",
       "      <td>LAWS TOWN LADWP</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000383</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA0707568</th>\n",
       "      <td>MACS OLD HOUSE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000383</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA1502004</th>\n",
       "      <td>ANDREWS AG., INC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000380</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA0500090</th>\n",
       "      <td>AMERICAN LEGION POST #376</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000367</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA0500003</th>\n",
       "      <td>U.S. ARMY CORP NEW HOGAN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000332</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA0707570</th>\n",
       "      <td>BRIDGEHEAD CAFE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000302</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA0900103</th>\n",
       "      <td>SILVERFORK STORE WATER SYSTEM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000292</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA0900653</th>\n",
       "      <td>SKI RUN MANAGEMENT COMPANY-INACTIVE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000228</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA1400502</th>\n",
       "      <td>BAKER CREEK CAMPGROUND</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000187</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA0300524</th>\n",
       "      <td>MP ASSOCIATES, INC.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000097</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CA5601725</th>\n",
       "      <td>SMITH FORKS WATER SYSTEM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000086</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4746 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               name  \\\n",
       "pwsid                                                 \n",
       "CA1910087      METROPOLITAN WATER DIST. OF SO. CAL.   \n",
       "CA1310014              IMPERIAL IRRIGATION DISTRICT   \n",
       "CA3310001             COACHELLA VWD: COVE COMMUNITY   \n",
       "CA3310009                      EASTERN MUNICIPAL WD   \n",
       "CA3310049                               WESTERN MWD   \n",
       "CA1910067  LOS ANGELES-CITY, DEPT. OF WATER & POWER   \n",
       "CA4310027         SANTA CLARA VALLEY WATER DISTRICT   \n",
       "CA0110010                       ZONE 7 WATER AGENCY   \n",
       "CA0110005                              EAST BAY MUD   \n",
       "CA4910020                SONOMA COUNTY WATER AGENCY   \n",
       "CA3610019                  SAN BERNARDINO VALLEY WD   \n",
       "CA1210013                          HUMBOLDT BAY MWD   \n",
       "CA3710020                        SAN DIEGO, CITY OF   \n",
       "CA5610046                      UNITED WTR CONS DIST   \n",
       "CA5610050            CALLEGUAS MUNICIPAL WATER DIST   \n",
       "CA3310005                       DESERT WATER AGENCY   \n",
       "CA1510016           RAND COMMUNITIES WATER DISTRICT   \n",
       "CA1510022                  WEST KERN WATER DISTRICT   \n",
       "CA5010043       STANISLAUS REGIONAL WATER AUTHORITY   \n",
       "CA0710003               CONTRA COSTA WATER DISTRICT   \n",
       "CA0910001                       EL DORADO ID - MAIN   \n",
       "CA5610500             CHANNEL ISLANDS NATIONAL PARK   \n",
       "CA1510032                  CALIFORNIA CITY, CITY OF   \n",
       "CA1910048  SANTA CLARITA VALLEY W.A.-IMPORTED DIVIS   \n",
       "CA5810006                 NORTH YUBA WATER DISTRICT   \n",
       "CA1910253                         CENTRAL BASIN MWD   \n",
       "CA3010092               IRVINE RANCH WATER DISTRICT   \n",
       "CA5010038               MODESTO IRRIGATION DISTRICT   \n",
       "CA3310038          RANCHO CALIFORNIA WATER DISTRICT   \n",
       "CA4210700            AMERICAN WATER O&M, LLC - VAFB   \n",
       "...                                             ...   \n",
       "CA0900305                    CAMP VIRNER RESTAURANT   \n",
       "CA0707521                         BYRON CORNERS INC   \n",
       "CA5403061                     RIVER VIEW RESTAURANT   \n",
       "CA0900548                          MARK TWAIN MOTEL   \n",
       "CA4901453                     MAURITSON LABOR HOUSE   \n",
       "CA4901175          OAK GROVE SCHOOL DISTRICT OFFICE   \n",
       "CA0706031                     COUNTRY JUNCTION DELI   \n",
       "CA0706059                                GAS N SAVE   \n",
       "CA0500100                              BURSON DONUT   \n",
       "CA4901451                          SPIDER WEB RANCH   \n",
       "CA0900579             FAIRBRIDGE INN EXPRESS-MIDWAY   \n",
       "CA5000382                      FARMERS' DEN /MARKET   \n",
       "CA0105002                         RIVERS END MARINA   \n",
       "CA0800850                       SEA WEST RESTAURANT   \n",
       "CA4901193                         SCHELLVILLE GRILL   \n",
       "CA0706054                             BETHEL MARKET   \n",
       "CA1700733                         JITTERBEAN COFFEE   \n",
       "CA0400169                             GRIDLEY GRILL   \n",
       "CA4901412           CAST ESTATE VINEYARDS & CELLARS   \n",
       "CA1400102                           LAWS TOWN LADWP   \n",
       "CA0707568                            MACS OLD HOUSE   \n",
       "CA1502004                          ANDREWS AG., INC   \n",
       "CA0500090                 AMERICAN LEGION POST #376   \n",
       "CA0500003                  U.S. ARMY CORP NEW HOGAN   \n",
       "CA0707570                           BRIDGEHEAD CAFE   \n",
       "CA0900103             SILVERFORK STORE WATER SYSTEM   \n",
       "CA0900653       SKI RUN MANAGEMENT COMPANY-INACTIVE   \n",
       "CA1400502                    BAKER CREEK CAMPGROUND   \n",
       "CA0300524                       MP ASSOCIATES, INC.   \n",
       "CA5601725                  SMITH FORKS WATER SYSTEM   \n",
       "\n",
       "                                       name_awwa2017  name_match_score  \\\n",
       "pwsid                                                                    \n",
       "CA1910087                                        NaN               NaN   \n",
       "CA1310014                                        NaN               NaN   \n",
       "CA3310001                                        NaN               NaN   \n",
       "CA3310009                                        NaN               NaN   \n",
       "CA3310049           Western Municipal Water District           0.07945   \n",
       "CA1910067  Los Angeles Department Of Water And Power           0.41962   \n",
       "CA4310027                                        NaN               NaN   \n",
       "CA0110010                                        NaN               NaN   \n",
       "CA0110005        East Bay Municipal Utility District           0.00226   \n",
       "CA4910020                                        NaN               NaN   \n",
       "CA3610019                                        NaN               NaN   \n",
       "CA1210013      Humboldt Bay Municipal Water District           0.11432   \n",
       "CA3710020                         San Diego  City Of           0.44258   \n",
       "CA5610046                                        NaN               NaN   \n",
       "CA5610050                                        NaN               NaN   \n",
       "CA3310005                        Desert Water Agency           0.56249   \n",
       "CA1510016                                        NaN               NaN   \n",
       "CA1510022                                        NaN               NaN   \n",
       "CA5010043                                        NaN               NaN   \n",
       "CA0710003                Contra Costa Water District           0.57585   \n",
       "CA0910001        El Dorado Irrigation District  Main           0.35159   \n",
       "CA5610500                                        NaN               NaN   \n",
       "CA1510032                                        NaN               NaN   \n",
       "CA1910048                                        NaN               NaN   \n",
       "CA5810006                  North Yuba Water District           0.64534   \n",
       "CA1910253                                        NaN               NaN   \n",
       "CA3010092                                        NaN               NaN   \n",
       "CA5010038                                        NaN               NaN   \n",
       "CA3310038                                        NaN               NaN   \n",
       "CA4210700                                        NaN               NaN   \n",
       "...                                              ...               ...   \n",
       "CA0900305                                        NaN               NaN   \n",
       "CA0707521                                        NaN               NaN   \n",
       "CA5403061                                        NaN               NaN   \n",
       "CA0900548                                        NaN               NaN   \n",
       "CA4901453                                        NaN               NaN   \n",
       "CA4901175                                        NaN               NaN   \n",
       "CA0706031                                        NaN               NaN   \n",
       "CA0706059                                        NaN               NaN   \n",
       "CA0500100                                        NaN               NaN   \n",
       "CA4901451                                        NaN               NaN   \n",
       "CA0900579                                        NaN               NaN   \n",
       "CA5000382                                        NaN               NaN   \n",
       "CA0105002                                        NaN               NaN   \n",
       "CA0800850                                        NaN               NaN   \n",
       "CA4901193                                        NaN               NaN   \n",
       "CA0706054                                        NaN               NaN   \n",
       "CA1700733                                        NaN               NaN   \n",
       "CA0400169                                        NaN               NaN   \n",
       "CA4901412                                        NaN               NaN   \n",
       "CA1400102                                        NaN               NaN   \n",
       "CA0707568                                        NaN               NaN   \n",
       "CA1502004                                        NaN               NaN   \n",
       "CA0500090                                        NaN               NaN   \n",
       "CA0500003                                        NaN               NaN   \n",
       "CA0707570                                        NaN               NaN   \n",
       "CA0900103                                        NaN               NaN   \n",
       "CA0900653                                        NaN               NaN   \n",
       "CA1400502                                        NaN               NaN   \n",
       "CA0300524                                        NaN               NaN   \n",
       "CA5601725                                        NaN               NaN   \n",
       "\n",
       "          name_match_quality      area_km2  total_charge  \n",
       "pwsid                                                     \n",
       "CA1910087                NaN  13625.885494           NaN  \n",
       "CA1310014                NaN   2466.149873           NaN  \n",
       "CA3310001                NaN   1910.107177           NaN  \n",
       "CA3310009                NaN   1436.033699           NaN  \n",
       "CA3310049              match   1342.762986         70.44  \n",
       "CA1910067              match   1284.632781         45.68  \n",
       "CA4310027                NaN   1119.086752           NaN  \n",
       "CA0110010                NaN   1116.671117           NaN  \n",
       "CA0110005              match   1052.861384         51.31  \n",
       "CA4910020                NaN    932.600400           NaN  \n",
       "CA3610019                NaN    912.896175           NaN  \n",
       "CA1210013              match    876.046864         29.35  \n",
       "CA3710020              match    868.918811         53.23  \n",
       "CA5610046                NaN    860.023521           NaN  \n",
       "CA5610050                NaN    852.959015           NaN  \n",
       "CA3310005              match    833.936829         36.36  \n",
       "CA1510016                NaN    832.771007           NaN  \n",
       "CA1510022                NaN    822.964925           NaN  \n",
       "CA5010043                NaN    755.709284           NaN  \n",
       "CA0710003              match    562.842308         46.36  \n",
       "CA0910001              match    560.440948         52.10  \n",
       "CA5610500                NaN    544.473790           NaN  \n",
       "CA1510032                NaN    526.648923           NaN  \n",
       "CA1910048                NaN    506.223366           NaN  \n",
       "CA5810006              match    501.383453         31.76  \n",
       "CA1910253                NaN    471.264055           NaN  \n",
       "CA3010092                NaN    466.794258           NaN  \n",
       "CA5010038                NaN    409.390184           NaN  \n",
       "CA3310038                NaN    401.312022           NaN  \n",
       "CA4210700                NaN    400.854994           NaN  \n",
       "...                      ...           ...           ...  \n",
       "CA0900305                NaN      0.001117           NaN  \n",
       "CA0707521                NaN      0.001106           NaN  \n",
       "CA5403061                NaN      0.001092           NaN  \n",
       "CA0900548                NaN      0.001072           NaN  \n",
       "CA4901453                NaN      0.001039           NaN  \n",
       "CA4901175                NaN      0.001006           NaN  \n",
       "CA0706031                NaN      0.000998           NaN  \n",
       "CA0706059                NaN      0.000861           NaN  \n",
       "CA0500100                NaN      0.000818           NaN  \n",
       "CA4901451                NaN      0.000778           NaN  \n",
       "CA0900579                NaN      0.000755           NaN  \n",
       "CA5000382                NaN      0.000731           NaN  \n",
       "CA0105002                NaN      0.000690           NaN  \n",
       "CA0800850                NaN      0.000668           NaN  \n",
       "CA4901193                NaN      0.000649           NaN  \n",
       "CA0706054                NaN      0.000550           NaN  \n",
       "CA1700733                NaN      0.000482           NaN  \n",
       "CA0400169                NaN      0.000404           NaN  \n",
       "CA4901412                NaN      0.000398           NaN  \n",
       "CA1400102                NaN      0.000383           NaN  \n",
       "CA0707568                NaN      0.000383           NaN  \n",
       "CA1502004                NaN      0.000380           NaN  \n",
       "CA0500090                NaN      0.000367           NaN  \n",
       "CA0500003                NaN      0.000332           NaN  \n",
       "CA0707570                NaN      0.000302           NaN  \n",
       "CA0900103                NaN      0.000292           NaN  \n",
       "CA0900653                NaN      0.000228           NaN  \n",
       "CA1400502                NaN      0.000187           NaN  \n",
       "CA0300524                NaN      0.000097           NaN  \n",
       "CA5601725                NaN      0.000086           NaN  \n",
       "\n",
       "[4746 rows x 6 columns]"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(ds.PWS.RATES_MATCH()\n",
    " .pipe(apply_corrections)\n",
    " .set_index('provider_name')\n",
    " .join(ds.PWS_RATES().set_index('provider_name')[['total_charge']], how='left')\n",
    " .reset_index(drop=False).set_index('pwsid')\n",
    " [['provider_name', 'best_match_score', 'quality', 'total_charge']]\n",
    " .rename(columns={'provider_name': 'name_awwa2017',\n",
    "                  'best_match_score': 'name_match_score',\n",
    "                  'quality': 'name_match_quality',\n",
    "                 })\n",
    " .join(ds.PWS_BOUNDARIES().set_index('pwsid')[['name', 'area_km2']], how='right')\n",
    " [['name', 'name_awwa2017', 'name_match_score', 'name_match_quality', 'area_km2', 'total_charge']]\n",
    "#  .dropna(subset=['name'])\n",
    " .sort_index()\n",
    " .sort_values('area_km2', ascending=False)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "cawc",
   "language": "python",
   "name": "cawc"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}