EAR_to_WaterProduced.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "from functools import reduce\n",
    "from collections import OrderedDict"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Import the raw data file into pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "xls = pd.ExcelFile('inputData/EAR_Raw_2016.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read in each sheet of the raw data file into a dataframe. All sheets contain the Public Water System ID (PWSID) for the corresponding system. This file contains the following raw data:\n",
    "\n",
    "Sheet 1 contains information about each individual water system and total water usage\n",
    "\n",
    "Sheet 2 contains month-by-month water production data\n",
    "\n",
    "Sheet 3 has CCC, Conservation, and Complaint data\n",
    "\n",
    "Sheet 4 has Source, Rate, Delivery (month-by-month), Quality, Recycled Water, Treatment, and Emergency data for Large Water Systems (LWS)\n",
    "\n",
    "Sheet 5 has Source, Rate, Delivery (month-by-month), Quality, and Violation data for Small Water Systems (SWS)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "data1 = pd.read_excel(xls,'Table1_Final')\n",
    "data2 = pd.read_excel(xls,'Table2_Final')\n",
    "data3 = pd.read_excel(xls,'Table3_Final')\n",
    "data4 = pd.read_excel(xls,'Table4_LWS')\n",
    "data5 = pd.read_excel(xls,'Table5_SWS')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Concatenate the last two sheets, since each water system only appears once across the two sheets and there are some columns that only apply to large (or to small) water systems."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "frames = [data4,data5]\n",
    "data45 = pd.concat(frames,sort=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge dataframes into a single dataframe using the PWSID."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = [data1,data2,data3,data45]\n",
    "dataAll = reduce(lambda left,right: pd.merge(left,right,on='PWSID'),dfs)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have the data frame containing all of the data, set the PWSID as the index for easier analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "dataAll.set_index('PWSID',inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Define the columns you want in the output file. Careful: the column names defined here must match the ones used in the analysis loop! Also define dictionaries for storing month information and unit conversions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "colList = ['PWSID','Water.System.Name','Water.System.Classification','Year','Month','Date','Days.In.Month',\n",
    "           'WATER PRODUCED Water.Units IN UNITS ORIGINALLY REPORTED',\n",
    "           'WATER PRODUCED Water.Units REVIEWED BY OFFICE OF INFORMATION MANAGEMENT AND ANALYSIS',\n",
    "           'UNITS ADJUSTED BY OIMA?','Finished.Water.Vol.Type.Revised','WATER PRODUCED FROM GROUNDWATER',\n",
    "           'WATER PRODUCED FROM SURFACE WATER','FINISHED WATER PURCHASED OR RECEIVED FROM ANOTHER PUBLIC WATER SYSTEM',\n",
    "           'WATER SOLD TO ANOTHER PUBLIC WATER SYSTEM','Non-Potable Produced Water (EXCLUDING RECYCLING)',\n",
    "           'RECYCLED WATER PRODUCED',\n",
    "           'TOTAL POTABLE WATER PRODUCED USING REVISED UNITS',\n",
    "           'TOTAL POTABLE WATER IN GALLONS',\n",
    "           'Population Of Service Area','CALCULATED GPCD (Total Potable Produced in gallons per capita day)']\n",
    "months = {'Jan':31,'Feb':28,'Mar':31,'Apr':30,'May':31,'Jun':30,'Jul':31,'Aug':31,\n",
    "          'Sep':30,'Oct':31,'Nov':30,'Dec':31}\n",
    "convToGal = {'G':1,'MG':1000000,'AF':325851,'CCF':748,'HG':100,'DG':10,'CF':7.48}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now, loop over each PWSID in the raw data and then over each month. This could in principle be expanded to loop over multiple years as well. For each month, make a dictionary using the column lists, and then fill the dictionary accordingly. Add the dictionary to a list of dictionaries, and then make a Pandas DataFrame using this list of dictionaries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>PWSID</th>\n",
       "      <th>Water.System.Name</th>\n",
       "      <th>Water.System.Classification</th>\n",
       "      <th>Year</th>\n",
       "      <th>Month</th>\n",
       "      <th>Date</th>\n",
       "      <th>Days.In.Month</th>\n",
       "      <th>WATER PRODUCED Water.Units IN UNITS ORIGINALLY REPORTED</th>\n",
       "      <th>WATER PRODUCED Water.Units REVIEWED BY OFFICE OF INFORMATION MANAGEMENT AND ANALYSIS</th>\n",
       "      <th>UNITS ADJUSTED BY OIMA?</th>\n",
       "      <th>...</th>\n",
       "      <th>FINISHED WATER PURCHASED OR RECEIVED FROM ANOTHER PUBLIC WATER SYSTEM</th>\n",
       "      <th>WATER SOLD TO ANOTHER PUBLIC WATER SYSTEM</th>\n",
       "      <th>Non-Potable Produced Water (EXCLUDING RECYCLING)</th>\n",
       "      <th>RECYCLED WATER PRODUCED</th>\n",
       "      <th>TOTAL POTABLE WATER PRODUCED USING REVISED UNITS</th>\n",
       "      <th>TOTAL POTABLE WATER IN GALLONS</th>\n",
       "      <th>Population Of Service Area</th>\n",
       "      <th>CALCULATED GPCD (Total Potable Produced in gallons per capita day)</th>\n",
       "      <th>WATER PRODUCED Water.Units in UNITS ORIGINALLY REPORTED</th>\n",
       "      <th>WATER PRODUCED FROM SURFACEWATER</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CA0103040</td>\n",
       "      <td>NORRIS CANYON PROPERTY OWNERS ASSN</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Jan</td>\n",
       "      <td>Jan 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>126403</td>\n",
       "      <td>1.264030e+05</td>\n",
       "      <td>50.0</td>\n",
       "      <td>8.155032e+01</td>\n",
       "      <td>G</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CA0103040</td>\n",
       "      <td>NORRIS CANYON PROPERTY OWNERS ASSN</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Feb</td>\n",
       "      <td>Feb 1, 2016</td>\n",
       "      <td>28</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>118248</td>\n",
       "      <td>1.182480e+05</td>\n",
       "      <td>50.0</td>\n",
       "      <td>8.446286e+01</td>\n",
       "      <td>G</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CA0103040</td>\n",
       "      <td>NORRIS CANYON PROPERTY OWNERS ASSN</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Mar</td>\n",
       "      <td>Mar 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>139399</td>\n",
       "      <td>1.393990e+05</td>\n",
       "      <td>50.0</td>\n",
       "      <td>8.993484e+01</td>\n",
       "      <td>G</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CA0103040</td>\n",
       "      <td>NORRIS CANYON PROPERTY OWNERS ASSN</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Apr</td>\n",
       "      <td>Apr 1, 2016</td>\n",
       "      <td>30</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>127710</td>\n",
       "      <td>1.277100e+05</td>\n",
       "      <td>50.0</td>\n",
       "      <td>8.514000e+01</td>\n",
       "      <td>G</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CA0103040</td>\n",
       "      <td>NORRIS CANYON PROPERTY OWNERS ASSN</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>May</td>\n",
       "      <td>May 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>171440</td>\n",
       "      <td>1.714400e+05</td>\n",
       "      <td>50.0</td>\n",
       "      <td>1.106065e+02</td>\n",
       "      <td>G</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CA0103040</td>\n",
       "      <td>NORRIS CANYON PROPERTY OWNERS ASSN</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Jun</td>\n",
       "      <td>Jun 1, 2016</td>\n",
       "      <td>30</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>237645</td>\n",
       "      <td>2.376450e+05</td>\n",
       "      <td>50.0</td>\n",
       "      <td>1.584300e+02</td>\n",
       "      <td>G</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>CA0103040</td>\n",
       "      <td>NORRIS CANYON PROPERTY OWNERS ASSN</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Jul</td>\n",
       "      <td>Jul 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>293738</td>\n",
       "      <td>2.937380e+05</td>\n",
       "      <td>50.0</td>\n",
       "      <td>1.895084e+02</td>\n",
       "      <td>G</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>CA0103040</td>\n",
       "      <td>NORRIS CANYON PROPERTY OWNERS ASSN</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Aug</td>\n",
       "      <td>Aug 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>276755</td>\n",
       "      <td>2.767550e+05</td>\n",
       "      <td>50.0</td>\n",
       "      <td>1.785516e+02</td>\n",
       "      <td>G</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>CA0103040</td>\n",
       "      <td>NORRIS CANYON PROPERTY OWNERS ASSN</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Sep</td>\n",
       "      <td>Sep 1, 2016</td>\n",
       "      <td>30</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>206123</td>\n",
       "      <td>2.061230e+05</td>\n",
       "      <td>50.0</td>\n",
       "      <td>1.374153e+02</td>\n",
       "      <td>G</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>CA0103040</td>\n",
       "      <td>NORRIS CANYON PROPERTY OWNERS ASSN</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Oct</td>\n",
       "      <td>Oct 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>145825</td>\n",
       "      <td>1.458250e+05</td>\n",
       "      <td>50.0</td>\n",
       "      <td>9.408065e+01</td>\n",
       "      <td>G</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>CA0103040</td>\n",
       "      <td>NORRIS CANYON PROPERTY OWNERS ASSN</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Nov</td>\n",
       "      <td>Nov 1, 2016</td>\n",
       "      <td>30</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>84083</td>\n",
       "      <td>8.408300e+04</td>\n",
       "      <td>50.0</td>\n",
       "      <td>5.605533e+01</td>\n",
       "      <td>G</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>CA0103040</td>\n",
       "      <td>NORRIS CANYON PROPERTY OWNERS ASSN</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Dec</td>\n",
       "      <td>Dec 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>210175</td>\n",
       "      <td>2.101750e+05</td>\n",
       "      <td>50.0</td>\n",
       "      <td>1.355968e+02</td>\n",
       "      <td>G</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>CA0103041</td>\n",
       "      <td>TRAILER HAVEN MOBILE HOME PARK</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Jan</td>\n",
       "      <td>Jan 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>810158</td>\n",
       "      <td>8.101580e+05</td>\n",
       "      <td>543.0</td>\n",
       "      <td>4.812915e+01</td>\n",
       "      <td>G</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>CA0103041</td>\n",
       "      <td>TRAILER HAVEN MOBILE HOME PARK</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Feb</td>\n",
       "      <td>Feb 1, 2016</td>\n",
       "      <td>28</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>751072</td>\n",
       "      <td>7.510720e+05</td>\n",
       "      <td>543.0</td>\n",
       "      <td>4.939963e+01</td>\n",
       "      <td>G</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>CA0103041</td>\n",
       "      <td>TRAILER HAVEN MOBILE HOME PARK</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Mar</td>\n",
       "      <td>Mar 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>837333</td>\n",
       "      <td>8.373330e+05</td>\n",
       "      <td>543.0</td>\n",
       "      <td>4.974354e+01</td>\n",
       "      <td>G</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>CA0103041</td>\n",
       "      <td>TRAILER HAVEN MOBILE HOME PARK</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Apr</td>\n",
       "      <td>Apr 1, 2016</td>\n",
       "      <td>30</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>457461</td>\n",
       "      <td>4.574610e+05</td>\n",
       "      <td>543.0</td>\n",
       "      <td>2.808232e+01</td>\n",
       "      <td>G</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>CA0103041</td>\n",
       "      <td>TRAILER HAVEN MOBILE HOME PARK</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>May</td>\n",
       "      <td>May 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>554163</td>\n",
       "      <td>5.541630e+05</td>\n",
       "      <td>543.0</td>\n",
       "      <td>3.292123e+01</td>\n",
       "      <td>G</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>CA0103041</td>\n",
       "      <td>TRAILER HAVEN MOBILE HOME PARK</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Jun</td>\n",
       "      <td>Jun 1, 2016</td>\n",
       "      <td>30</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>525724</td>\n",
       "      <td>5.257240e+05</td>\n",
       "      <td>543.0</td>\n",
       "      <td>3.227281e+01</td>\n",
       "      <td>G</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>CA0103041</td>\n",
       "      <td>TRAILER HAVEN MOBILE HOME PARK</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Jul</td>\n",
       "      <td>Jul 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>644280</td>\n",
       "      <td>6.442800e+05</td>\n",
       "      <td>543.0</td>\n",
       "      <td>3.827482e+01</td>\n",
       "      <td>G</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>CA0103041</td>\n",
       "      <td>TRAILER HAVEN MOBILE HOME PARK</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Aug</td>\n",
       "      <td>Aug 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>695504</td>\n",
       "      <td>6.955040e+05</td>\n",
       "      <td>543.0</td>\n",
       "      <td>4.131789e+01</td>\n",
       "      <td>G</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>CA0103041</td>\n",
       "      <td>TRAILER HAVEN MOBILE HOME PARK</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Sep</td>\n",
       "      <td>Sep 1, 2016</td>\n",
       "      <td>30</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>698935</td>\n",
       "      <td>6.989350e+05</td>\n",
       "      <td>543.0</td>\n",
       "      <td>4.290577e+01</td>\n",
       "      <td>G</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>CA0103041</td>\n",
       "      <td>TRAILER HAVEN MOBILE HOME PARK</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Oct</td>\n",
       "      <td>Oct 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>644056</td>\n",
       "      <td>6.440560e+05</td>\n",
       "      <td>543.0</td>\n",
       "      <td>3.826151e+01</td>\n",
       "      <td>G</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>CA0103041</td>\n",
       "      <td>TRAILER HAVEN MOBILE HOME PARK</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Nov</td>\n",
       "      <td>Nov 1, 2016</td>\n",
       "      <td>30</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>678351</td>\n",
       "      <td>6.783510e+05</td>\n",
       "      <td>543.0</td>\n",
       "      <td>4.164217e+01</td>\n",
       "      <td>G</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>CA0103041</td>\n",
       "      <td>TRAILER HAVEN MOBILE HOME PARK</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Dec</td>\n",
       "      <td>Dec 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>675353</td>\n",
       "      <td>6.753530e+05</td>\n",
       "      <td>543.0</td>\n",
       "      <td>4.012077e+01</td>\n",
       "      <td>G</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>CA0105002</td>\n",
       "      <td>RIVERS END MARINA</td>\n",
       "      <td></td>\n",
       "      <td>2016</td>\n",
       "      <td>Jan</td>\n",
       "      <td>Jan 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>CA0105002</td>\n",
       "      <td>RIVERS END MARINA</td>\n",
       "      <td></td>\n",
       "      <td>2016</td>\n",
       "      <td>Feb</td>\n",
       "      <td>Feb 1, 2016</td>\n",
       "      <td>28</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>CA0105002</td>\n",
       "      <td>RIVERS END MARINA</td>\n",
       "      <td></td>\n",
       "      <td>2016</td>\n",
       "      <td>Mar</td>\n",
       "      <td>Mar 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>CA0105002</td>\n",
       "      <td>RIVERS END MARINA</td>\n",
       "      <td></td>\n",
       "      <td>2016</td>\n",
       "      <td>Apr</td>\n",
       "      <td>Apr 1, 2016</td>\n",
       "      <td>30</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>CA0105002</td>\n",
       "      <td>RIVERS END MARINA</td>\n",
       "      <td></td>\n",
       "      <td>2016</td>\n",
       "      <td>May</td>\n",
       "      <td>May 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>CA0105002</td>\n",
       "      <td>RIVERS END MARINA</td>\n",
       "      <td></td>\n",
       "      <td>2016</td>\n",
       "      <td>Jun</td>\n",
       "      <td>Jun 1, 2016</td>\n",
       "      <td>30</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td></td>\n",
       "      <td></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",
       "      <td>...</td>\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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82362</th>\n",
       "      <td>CA5810005</td>\n",
       "      <td>NEVADA ID - SMARTVILLE</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Jul</td>\n",
       "      <td>Jul 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.841</td>\n",
       "      <td>8.410000e+05</td>\n",
       "      <td>122.0</td>\n",
       "      <td>2.223691e+02</td>\n",
       "      <td>MG</td>\n",
       "      <td>0.841</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82363</th>\n",
       "      <td>CA5810005</td>\n",
       "      <td>NEVADA ID - SMARTVILLE</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Aug</td>\n",
       "      <td>Aug 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.81</td>\n",
       "      <td>8.100000e+05</td>\n",
       "      <td>122.0</td>\n",
       "      <td>2.141724e+02</td>\n",
       "      <td>MG</td>\n",
       "      <td>0.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82364</th>\n",
       "      <td>CA5810005</td>\n",
       "      <td>NEVADA ID - SMARTVILLE</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Sep</td>\n",
       "      <td>Sep 1, 2016</td>\n",
       "      <td>30</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.58</td>\n",
       "      <td>5.800000e+05</td>\n",
       "      <td>122.0</td>\n",
       "      <td>1.584699e+02</td>\n",
       "      <td>MG</td>\n",
       "      <td>0.58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82365</th>\n",
       "      <td>CA5810005</td>\n",
       "      <td>NEVADA ID - SMARTVILLE</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Oct</td>\n",
       "      <td>Oct 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.41</td>\n",
       "      <td>4.100000e+05</td>\n",
       "      <td>122.0</td>\n",
       "      <td>1.084082e+02</td>\n",
       "      <td>MG</td>\n",
       "      <td>0.41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82366</th>\n",
       "      <td>CA5810005</td>\n",
       "      <td>NEVADA ID - SMARTVILLE</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Nov</td>\n",
       "      <td>Nov 1, 2016</td>\n",
       "      <td>30</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.37</td>\n",
       "      <td>3.700000e+05</td>\n",
       "      <td>122.0</td>\n",
       "      <td>1.010929e+02</td>\n",
       "      <td>MG</td>\n",
       "      <td>0.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82367</th>\n",
       "      <td>CA5810005</td>\n",
       "      <td>NEVADA ID - SMARTVILLE</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Dec</td>\n",
       "      <td>Dec 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.32</td>\n",
       "      <td>3.200000e+05</td>\n",
       "      <td>122.0</td>\n",
       "      <td>8.461132e+01</td>\n",
       "      <td>MG</td>\n",
       "      <td>0.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82368</th>\n",
       "      <td>CA5810006</td>\n",
       "      <td>NORTH YUBA WATER DISTRICT</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Jan</td>\n",
       "      <td>Jan 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>8232000</td>\n",
       "      <td>8.232000e+12</td>\n",
       "      <td>3105.0</td>\n",
       "      <td>8.552283e+07</td>\n",
       "      <td>MG</td>\n",
       "      <td>8232000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82369</th>\n",
       "      <td>CA5810006</td>\n",
       "      <td>NORTH YUBA WATER DISTRICT</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Feb</td>\n",
       "      <td>Feb 1, 2016</td>\n",
       "      <td>28</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7031000</td>\n",
       "      <td>7.031000e+12</td>\n",
       "      <td>3105.0</td>\n",
       "      <td>8.087187e+07</td>\n",
       "      <td>MG</td>\n",
       "      <td>7031000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82370</th>\n",
       "      <td>CA5810006</td>\n",
       "      <td>NORTH YUBA WATER DISTRICT</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Mar</td>\n",
       "      <td>Mar 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7642100</td>\n",
       "      <td>7.642100e+12</td>\n",
       "      <td>3105.0</td>\n",
       "      <td>7.939432e+07</td>\n",
       "      <td>MG</td>\n",
       "      <td>7642100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82371</th>\n",
       "      <td>CA5810006</td>\n",
       "      <td>NORTH YUBA WATER DISTRICT</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Apr</td>\n",
       "      <td>Apr 1, 2016</td>\n",
       "      <td>30</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>9150700</td>\n",
       "      <td>9.150700e+12</td>\n",
       "      <td>3105.0</td>\n",
       "      <td>9.823618e+07</td>\n",
       "      <td>MG</td>\n",
       "      <td>9150700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82372</th>\n",
       "      <td>CA5810006</td>\n",
       "      <td>NORTH YUBA WATER DISTRICT</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>May</td>\n",
       "      <td>May 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10594300</td>\n",
       "      <td>1.059430e+13</td>\n",
       "      <td>3105.0</td>\n",
       "      <td>1.100649e+08</td>\n",
       "      <td>MG</td>\n",
       "      <td>10594300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82373</th>\n",
       "      <td>CA5810006</td>\n",
       "      <td>NORTH YUBA WATER DISTRICT</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Jun</td>\n",
       "      <td>Jun 1, 2016</td>\n",
       "      <td>30</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>15677100</td>\n",
       "      <td>1.567710e+13</td>\n",
       "      <td>3105.0</td>\n",
       "      <td>1.682995e+08</td>\n",
       "      <td>MG</td>\n",
       "      <td>15677100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82374</th>\n",
       "      <td>CA5810006</td>\n",
       "      <td>NORTH YUBA WATER DISTRICT</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Jul</td>\n",
       "      <td>Jul 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>18598300</td>\n",
       "      <td>1.859830e+13</td>\n",
       "      <td>3105.0</td>\n",
       "      <td>1.932191e+08</td>\n",
       "      <td>MG</td>\n",
       "      <td>18598300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82375</th>\n",
       "      <td>CA5810006</td>\n",
       "      <td>NORTH YUBA WATER DISTRICT</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Aug</td>\n",
       "      <td>Aug 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>17872300</td>\n",
       "      <td>1.787230e+13</td>\n",
       "      <td>3105.0</td>\n",
       "      <td>1.856766e+08</td>\n",
       "      <td>MG</td>\n",
       "      <td>17872300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82376</th>\n",
       "      <td>CA5810006</td>\n",
       "      <td>NORTH YUBA WATER DISTRICT</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Sep</td>\n",
       "      <td>Sep 1, 2016</td>\n",
       "      <td>30</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>14439700</td>\n",
       "      <td>1.443970e+13</td>\n",
       "      <td>3105.0</td>\n",
       "      <td>1.550156e+08</td>\n",
       "      <td>MG</td>\n",
       "      <td>14439700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82377</th>\n",
       "      <td>CA5810006</td>\n",
       "      <td>NORTH YUBA WATER DISTRICT</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Oct</td>\n",
       "      <td>Oct 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>11214600</td>\n",
       "      <td>1.121460e+13</td>\n",
       "      <td>3105.0</td>\n",
       "      <td>1.165093e+08</td>\n",
       "      <td>MG</td>\n",
       "      <td>11214600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82378</th>\n",
       "      <td>CA5810006</td>\n",
       "      <td>NORTH YUBA WATER DISTRICT</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Nov</td>\n",
       "      <td>Nov 1, 2016</td>\n",
       "      <td>30</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>8779300</td>\n",
       "      <td>8.779300e+12</td>\n",
       "      <td>3105.0</td>\n",
       "      <td>9.424906e+07</td>\n",
       "      <td>MG</td>\n",
       "      <td>8779300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82379</th>\n",
       "      <td>CA5810006</td>\n",
       "      <td>NORTH YUBA WATER DISTRICT</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Dec</td>\n",
       "      <td>Dec 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>8307400</td>\n",
       "      <td>8.307400e+12</td>\n",
       "      <td>3105.0</td>\n",
       "      <td>8.630617e+07</td>\n",
       "      <td>MG</td>\n",
       "      <td>8307400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82380</th>\n",
       "      <td>CA5810700</td>\n",
       "      <td>BEALE AIR FORCE BASE</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Jan</td>\n",
       "      <td>Jan 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.19</td>\n",
       "      <td>13.4</td>\n",
       "      <td>1.340000e+07</td>\n",
       "      <td>2100.0</td>\n",
       "      <td>2.058372e+02</td>\n",
       "      <td>MG</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82381</th>\n",
       "      <td>CA5810700</td>\n",
       "      <td>BEALE AIR FORCE BASE</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Feb</td>\n",
       "      <td>Feb 1, 2016</td>\n",
       "      <td>28</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.88</td>\n",
       "      <td>13</td>\n",
       "      <td>1.300000e+07</td>\n",
       "      <td>2100.0</td>\n",
       "      <td>2.210884e+02</td>\n",
       "      <td>MG</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82382</th>\n",
       "      <td>CA5810700</td>\n",
       "      <td>BEALE AIR FORCE BASE</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Mar</td>\n",
       "      <td>Mar 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.48</td>\n",
       "      <td>13</td>\n",
       "      <td>1.300000e+07</td>\n",
       "      <td>2100.0</td>\n",
       "      <td>1.996928e+02</td>\n",
       "      <td>MG</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82383</th>\n",
       "      <td>CA5810700</td>\n",
       "      <td>BEALE AIR FORCE BASE</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Apr</td>\n",
       "      <td>Apr 1, 2016</td>\n",
       "      <td>30</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>10.85</td>\n",
       "      <td>26.9</td>\n",
       "      <td>2.690000e+07</td>\n",
       "      <td>2100.0</td>\n",
       "      <td>4.269841e+02</td>\n",
       "      <td>MG</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82384</th>\n",
       "      <td>CA5810700</td>\n",
       "      <td>BEALE AIR FORCE BASE</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>May</td>\n",
       "      <td>May 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>17.28</td>\n",
       "      <td>34.3</td>\n",
       "      <td>3.430000e+07</td>\n",
       "      <td>2100.0</td>\n",
       "      <td>5.268817e+02</td>\n",
       "      <td>MG</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82385</th>\n",
       "      <td>CA5810700</td>\n",
       "      <td>BEALE AIR FORCE BASE</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Jun</td>\n",
       "      <td>Jun 1, 2016</td>\n",
       "      <td>30</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>20.01</td>\n",
       "      <td>37.8</td>\n",
       "      <td>3.780000e+07</td>\n",
       "      <td>2100.0</td>\n",
       "      <td>6.000000e+02</td>\n",
       "      <td>MG</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82386</th>\n",
       "      <td>CA5810700</td>\n",
       "      <td>BEALE AIR FORCE BASE</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Jul</td>\n",
       "      <td>Jul 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>19.32</td>\n",
       "      <td>43.3</td>\n",
       "      <td>4.330000e+07</td>\n",
       "      <td>2100.0</td>\n",
       "      <td>6.651306e+02</td>\n",
       "      <td>MG</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82387</th>\n",
       "      <td>CA5810700</td>\n",
       "      <td>BEALE AIR FORCE BASE</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Aug</td>\n",
       "      <td>Aug 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>13.12</td>\n",
       "      <td>50.5</td>\n",
       "      <td>5.050000e+07</td>\n",
       "      <td>2100.0</td>\n",
       "      <td>7.757296e+02</td>\n",
       "      <td>MG</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82388</th>\n",
       "      <td>CA5810700</td>\n",
       "      <td>BEALE AIR FORCE BASE</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Sep</td>\n",
       "      <td>Sep 1, 2016</td>\n",
       "      <td>30</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>18.19</td>\n",
       "      <td>43.2</td>\n",
       "      <td>4.320000e+07</td>\n",
       "      <td>2100.0</td>\n",
       "      <td>6.857143e+02</td>\n",
       "      <td>MG</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82389</th>\n",
       "      <td>CA5810700</td>\n",
       "      <td>BEALE AIR FORCE BASE</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Oct</td>\n",
       "      <td>Oct 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5.22</td>\n",
       "      <td>28</td>\n",
       "      <td>2.800000e+07</td>\n",
       "      <td>2100.0</td>\n",
       "      <td>4.301075e+02</td>\n",
       "      <td>MG</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82390</th>\n",
       "      <td>CA5810700</td>\n",
       "      <td>BEALE AIR FORCE BASE</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Nov</td>\n",
       "      <td>Nov 1, 2016</td>\n",
       "      <td>30</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.57</td>\n",
       "      <td>16.8</td>\n",
       "      <td>1.680000e+07</td>\n",
       "      <td>2100.0</td>\n",
       "      <td>2.666667e+02</td>\n",
       "      <td>MG</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82391</th>\n",
       "      <td>CA5810700</td>\n",
       "      <td>BEALE AIR FORCE BASE</td>\n",
       "      <td>Community Water System</td>\n",
       "      <td>2016</td>\n",
       "      <td>Dec</td>\n",
       "      <td>Dec 1, 2016</td>\n",
       "      <td>31</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.14</td>\n",
       "      <td>16.3</td>\n",
       "      <td>1.630000e+07</td>\n",
       "      <td>2100.0</td>\n",
       "      <td>2.503840e+02</td>\n",
       "      <td>MG</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>82392 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           PWSID                   Water.System.Name  \\\n",
       "0      CA0103040  NORRIS CANYON PROPERTY OWNERS ASSN   \n",
       "1      CA0103040  NORRIS CANYON PROPERTY OWNERS ASSN   \n",
       "2      CA0103040  NORRIS CANYON PROPERTY OWNERS ASSN   \n",
       "3      CA0103040  NORRIS CANYON PROPERTY OWNERS ASSN   \n",
       "4      CA0103040  NORRIS CANYON PROPERTY OWNERS ASSN   \n",
       "5      CA0103040  NORRIS CANYON PROPERTY OWNERS ASSN   \n",
       "6      CA0103040  NORRIS CANYON PROPERTY OWNERS ASSN   \n",
       "7      CA0103040  NORRIS CANYON PROPERTY OWNERS ASSN   \n",
       "8      CA0103040  NORRIS CANYON PROPERTY OWNERS ASSN   \n",
       "9      CA0103040  NORRIS CANYON PROPERTY OWNERS ASSN   \n",
       "10     CA0103040  NORRIS CANYON PROPERTY OWNERS ASSN   \n",
       "11     CA0103040  NORRIS CANYON PROPERTY OWNERS ASSN   \n",
       "12     CA0103041      TRAILER HAVEN MOBILE HOME PARK   \n",
       "13     CA0103041      TRAILER HAVEN MOBILE HOME PARK   \n",
       "14     CA0103041      TRAILER HAVEN MOBILE HOME PARK   \n",
       "15     CA0103041      TRAILER HAVEN MOBILE HOME PARK   \n",
       "16     CA0103041      TRAILER HAVEN MOBILE HOME PARK   \n",
       "17     CA0103041      TRAILER HAVEN MOBILE HOME PARK   \n",
       "18     CA0103041      TRAILER HAVEN MOBILE HOME PARK   \n",
       "19     CA0103041      TRAILER HAVEN MOBILE HOME PARK   \n",
       "20     CA0103041      TRAILER HAVEN MOBILE HOME PARK   \n",
       "21     CA0103041      TRAILER HAVEN MOBILE HOME PARK   \n",
       "22     CA0103041      TRAILER HAVEN MOBILE HOME PARK   \n",
       "23     CA0103041      TRAILER HAVEN MOBILE HOME PARK   \n",
       "24     CA0105002                   RIVERS END MARINA   \n",
       "25     CA0105002                   RIVERS END MARINA   \n",
       "26     CA0105002                   RIVERS END MARINA   \n",
       "27     CA0105002                   RIVERS END MARINA   \n",
       "28     CA0105002                   RIVERS END MARINA   \n",
       "29     CA0105002                   RIVERS END MARINA   \n",
       "...          ...                                 ...   \n",
       "82362  CA5810005              NEVADA ID - SMARTVILLE   \n",
       "82363  CA5810005              NEVADA ID - SMARTVILLE   \n",
       "82364  CA5810005              NEVADA ID - SMARTVILLE   \n",
       "82365  CA5810005              NEVADA ID - SMARTVILLE   \n",
       "82366  CA5810005              NEVADA ID - SMARTVILLE   \n",
       "82367  CA5810005              NEVADA ID - SMARTVILLE   \n",
       "82368  CA5810006           NORTH YUBA WATER DISTRICT   \n",
       "82369  CA5810006           NORTH YUBA WATER DISTRICT   \n",
       "82370  CA5810006           NORTH YUBA WATER DISTRICT   \n",
       "82371  CA5810006           NORTH YUBA WATER DISTRICT   \n",
       "82372  CA5810006           NORTH YUBA WATER DISTRICT   \n",
       "82373  CA5810006           NORTH YUBA WATER DISTRICT   \n",
       "82374  CA5810006           NORTH YUBA WATER DISTRICT   \n",
       "82375  CA5810006           NORTH YUBA WATER DISTRICT   \n",
       "82376  CA5810006           NORTH YUBA WATER DISTRICT   \n",
       "82377  CA5810006           NORTH YUBA WATER DISTRICT   \n",
       "82378  CA5810006           NORTH YUBA WATER DISTRICT   \n",
       "82379  CA5810006           NORTH YUBA WATER DISTRICT   \n",
       "82380  CA5810700                BEALE AIR FORCE BASE   \n",
       "82381  CA5810700                BEALE AIR FORCE BASE   \n",
       "82382  CA5810700                BEALE AIR FORCE BASE   \n",
       "82383  CA5810700                BEALE AIR FORCE BASE   \n",
       "82384  CA5810700                BEALE AIR FORCE BASE   \n",
       "82385  CA5810700                BEALE AIR FORCE BASE   \n",
       "82386  CA5810700                BEALE AIR FORCE BASE   \n",
       "82387  CA5810700                BEALE AIR FORCE BASE   \n",
       "82388  CA5810700                BEALE AIR FORCE BASE   \n",
       "82389  CA5810700                BEALE AIR FORCE BASE   \n",
       "82390  CA5810700                BEALE AIR FORCE BASE   \n",
       "82391  CA5810700                BEALE AIR FORCE BASE   \n",
       "\n",
       "      Water.System.Classification  Year Month         Date  Days.In.Month  \\\n",
       "0          Community Water System  2016   Jan  Jan 1, 2016             31   \n",
       "1          Community Water System  2016   Feb  Feb 1, 2016             28   \n",
       "2          Community Water System  2016   Mar  Mar 1, 2016             31   \n",
       "3          Community Water System  2016   Apr  Apr 1, 2016             30   \n",
       "4          Community Water System  2016   May  May 1, 2016             31   \n",
       "5          Community Water System  2016   Jun  Jun 1, 2016             30   \n",
       "6          Community Water System  2016   Jul  Jul 1, 2016             31   \n",
       "7          Community Water System  2016   Aug  Aug 1, 2016             31   \n",
       "8          Community Water System  2016   Sep  Sep 1, 2016             30   \n",
       "9          Community Water System  2016   Oct  Oct 1, 2016             31   \n",
       "10         Community Water System  2016   Nov  Nov 1, 2016             30   \n",
       "11         Community Water System  2016   Dec  Dec 1, 2016             31   \n",
       "12         Community Water System  2016   Jan  Jan 1, 2016             31   \n",
       "13         Community Water System  2016   Feb  Feb 1, 2016             28   \n",
       "14         Community Water System  2016   Mar  Mar 1, 2016             31   \n",
       "15         Community Water System  2016   Apr  Apr 1, 2016             30   \n",
       "16         Community Water System  2016   May  May 1, 2016             31   \n",
       "17         Community Water System  2016   Jun  Jun 1, 2016             30   \n",
       "18         Community Water System  2016   Jul  Jul 1, 2016             31   \n",
       "19         Community Water System  2016   Aug  Aug 1, 2016             31   \n",
       "20         Community Water System  2016   Sep  Sep 1, 2016             30   \n",
       "21         Community Water System  2016   Oct  Oct 1, 2016             31   \n",
       "22         Community Water System  2016   Nov  Nov 1, 2016             30   \n",
       "23         Community Water System  2016   Dec  Dec 1, 2016             31   \n",
       "24                                 2016   Jan  Jan 1, 2016             31   \n",
       "25                                 2016   Feb  Feb 1, 2016             28   \n",
       "26                                 2016   Mar  Mar 1, 2016             31   \n",
       "27                                 2016   Apr  Apr 1, 2016             30   \n",
       "28                                 2016   May  May 1, 2016             31   \n",
       "29                                 2016   Jun  Jun 1, 2016             30   \n",
       "...                           ...   ...   ...          ...            ...   \n",
       "82362      Community Water System  2016   Jul  Jul 1, 2016             31   \n",
       "82363      Community Water System  2016   Aug  Aug 1, 2016             31   \n",
       "82364      Community Water System  2016   Sep  Sep 1, 2016             30   \n",
       "82365      Community Water System  2016   Oct  Oct 1, 2016             31   \n",
       "82366      Community Water System  2016   Nov  Nov 1, 2016             30   \n",
       "82367      Community Water System  2016   Dec  Dec 1, 2016             31   \n",
       "82368      Community Water System  2016   Jan  Jan 1, 2016             31   \n",
       "82369      Community Water System  2016   Feb  Feb 1, 2016             28   \n",
       "82370      Community Water System  2016   Mar  Mar 1, 2016             31   \n",
       "82371      Community Water System  2016   Apr  Apr 1, 2016             30   \n",
       "82372      Community Water System  2016   May  May 1, 2016             31   \n",
       "82373      Community Water System  2016   Jun  Jun 1, 2016             30   \n",
       "82374      Community Water System  2016   Jul  Jul 1, 2016             31   \n",
       "82375      Community Water System  2016   Aug  Aug 1, 2016             31   \n",
       "82376      Community Water System  2016   Sep  Sep 1, 2016             30   \n",
       "82377      Community Water System  2016   Oct  Oct 1, 2016             31   \n",
       "82378      Community Water System  2016   Nov  Nov 1, 2016             30   \n",
       "82379      Community Water System  2016   Dec  Dec 1, 2016             31   \n",
       "82380      Community Water System  2016   Jan  Jan 1, 2016             31   \n",
       "82381      Community Water System  2016   Feb  Feb 1, 2016             28   \n",
       "82382      Community Water System  2016   Mar  Mar 1, 2016             31   \n",
       "82383      Community Water System  2016   Apr  Apr 1, 2016             30   \n",
       "82384      Community Water System  2016   May  May 1, 2016             31   \n",
       "82385      Community Water System  2016   Jun  Jun 1, 2016             30   \n",
       "82386      Community Water System  2016   Jul  Jul 1, 2016             31   \n",
       "82387      Community Water System  2016   Aug  Aug 1, 2016             31   \n",
       "82388      Community Water System  2016   Sep  Sep 1, 2016             30   \n",
       "82389      Community Water System  2016   Oct  Oct 1, 2016             31   \n",
       "82390      Community Water System  2016   Nov  Nov 1, 2016             30   \n",
       "82391      Community Water System  2016   Dec  Dec 1, 2016             31   \n",
       "\n",
       "      WATER PRODUCED Water.Units IN UNITS ORIGINALLY REPORTED  \\\n",
       "0                                                   None        \n",
       "1                                                   None        \n",
       "2                                                   None        \n",
       "3                                                   None        \n",
       "4                                                   None        \n",
       "5                                                   None        \n",
       "6                                                   None        \n",
       "7                                                   None        \n",
       "8                                                   None        \n",
       "9                                                   None        \n",
       "10                                                  None        \n",
       "11                                                  None        \n",
       "12                                                  None        \n",
       "13                                                  None        \n",
       "14                                                  None        \n",
       "15                                                  None        \n",
       "16                                                  None        \n",
       "17                                                  None        \n",
       "18                                                  None        \n",
       "19                                                  None        \n",
       "20                                                  None        \n",
       "21                                                  None        \n",
       "22                                                  None        \n",
       "23                                                  None        \n",
       "24                                                  None        \n",
       "25                                                  None        \n",
       "26                                                  None        \n",
       "27                                                  None        \n",
       "28                                                  None        \n",
       "29                                                  None        \n",
       "...                                                  ...        \n",
       "82362                                               None        \n",
       "82363                                               None        \n",
       "82364                                               None        \n",
       "82365                                               None        \n",
       "82366                                               None        \n",
       "82367                                               None        \n",
       "82368                                               None        \n",
       "82369                                               None        \n",
       "82370                                               None        \n",
       "82371                                               None        \n",
       "82372                                               None        \n",
       "82373                                               None        \n",
       "82374                                               None        \n",
       "82375                                               None        \n",
       "82376                                               None        \n",
       "82377                                               None        \n",
       "82378                                               None        \n",
       "82379                                               None        \n",
       "82380                                               None        \n",
       "82381                                               None        \n",
       "82382                                               None        \n",
       "82383                                               None        \n",
       "82384                                               None        \n",
       "82385                                               None        \n",
       "82386                                               None        \n",
       "82387                                               None        \n",
       "82388                                               None        \n",
       "82389                                               None        \n",
       "82390                                               None        \n",
       "82391                                               None        \n",
       "\n",
       "      WATER PRODUCED Water.Units REVIEWED BY OFFICE OF INFORMATION MANAGEMENT AND ANALYSIS  \\\n",
       "0                                                   None                                     \n",
       "1                                                   None                                     \n",
       "2                                                   None                                     \n",
       "3                                                   None                                     \n",
       "4                                                   None                                     \n",
       "5                                                   None                                     \n",
       "6                                                   None                                     \n",
       "7                                                   None                                     \n",
       "8                                                   None                                     \n",
       "9                                                   None                                     \n",
       "10                                                  None                                     \n",
       "11                                                  None                                     \n",
       "12                                                  None                                     \n",
       "13                                                  None                                     \n",
       "14                                                  None                                     \n",
       "15                                                  None                                     \n",
       "16                                                  None                                     \n",
       "17                                                  None                                     \n",
       "18                                                  None                                     \n",
       "19                                                  None                                     \n",
       "20                                                  None                                     \n",
       "21                                                  None                                     \n",
       "22                                                  None                                     \n",
       "23                                                  None                                     \n",
       "24                                                  None                                     \n",
       "25                                                  None                                     \n",
       "26                                                  None                                     \n",
       "27                                                  None                                     \n",
       "28                                                  None                                     \n",
       "29                                                  None                                     \n",
       "...                                                  ...                                     \n",
       "82362                                               None                                     \n",
       "82363                                               None                                     \n",
       "82364                                               None                                     \n",
       "82365                                               None                                     \n",
       "82366                                               None                                     \n",
       "82367                                               None                                     \n",
       "82368                                               None                                     \n",
       "82369                                               None                                     \n",
       "82370                                               None                                     \n",
       "82371                                               None                                     \n",
       "82372                                               None                                     \n",
       "82373                                               None                                     \n",
       "82374                                               None                                     \n",
       "82375                                               None                                     \n",
       "82376                                               None                                     \n",
       "82377                                               None                                     \n",
       "82378                                               None                                     \n",
       "82379                                               None                                     \n",
       "82380                                               None                                     \n",
       "82381                                               None                                     \n",
       "82382                                               None                                     \n",
       "82383                                               None                                     \n",
       "82384                                               None                                     \n",
       "82385                                               None                                     \n",
       "82386                                               None                                     \n",
       "82387                                               None                                     \n",
       "82388                                               None                                     \n",
       "82389                                               None                                     \n",
       "82390                                               None                                     \n",
       "82391                                               None                                     \n",
       "\n",
       "      UNITS ADJUSTED BY OIMA?                ...                 \\\n",
       "0                        None                ...                  \n",
       "1                        None                ...                  \n",
       "2                        None                ...                  \n",
       "3                        None                ...                  \n",
       "4                        None                ...                  \n",
       "5                        None                ...                  \n",
       "6                        None                ...                  \n",
       "7                        None                ...                  \n",
       "8                        None                ...                  \n",
       "9                        None                ...                  \n",
       "10                       None                ...                  \n",
       "11                       None                ...                  \n",
       "12                       None                ...                  \n",
       "13                       None                ...                  \n",
       "14                       None                ...                  \n",
       "15                       None                ...                  \n",
       "16                       None                ...                  \n",
       "17                       None                ...                  \n",
       "18                       None                ...                  \n",
       "19                       None                ...                  \n",
       "20                       None                ...                  \n",
       "21                       None                ...                  \n",
       "22                       None                ...                  \n",
       "23                       None                ...                  \n",
       "24                       None                ...                  \n",
       "25                       None                ...                  \n",
       "26                       None                ...                  \n",
       "27                       None                ...                  \n",
       "28                       None                ...                  \n",
       "29                       None                ...                  \n",
       "...                       ...                ...                  \n",
       "82362                    None                ...                  \n",
       "82363                    None                ...                  \n",
       "82364                    None                ...                  \n",
       "82365                    None                ...                  \n",
       "82366                    None                ...                  \n",
       "82367                    None                ...                  \n",
       "82368                    None                ...                  \n",
       "82369                    None                ...                  \n",
       "82370                    None                ...                  \n",
       "82371                    None                ...                  \n",
       "82372                    None                ...                  \n",
       "82373                    None                ...                  \n",
       "82374                    None                ...                  \n",
       "82375                    None                ...                  \n",
       "82376                    None                ...                  \n",
       "82377                    None                ...                  \n",
       "82378                    None                ...                  \n",
       "82379                    None                ...                  \n",
       "82380                    None                ...                  \n",
       "82381                    None                ...                  \n",
       "82382                    None                ...                  \n",
       "82383                    None                ...                  \n",
       "82384                    None                ...                  \n",
       "82385                    None                ...                  \n",
       "82386                    None                ...                  \n",
       "82387                    None                ...                  \n",
       "82388                    None                ...                  \n",
       "82389                    None                ...                  \n",
       "82390                    None                ...                  \n",
       "82391                    None                ...                  \n",
       "\n",
       "      FINISHED WATER PURCHASED OR RECEIVED FROM ANOTHER PUBLIC WATER SYSTEM  \\\n",
       "0                                                      0                      \n",
       "1                                                      0                      \n",
       "2                                                      0                      \n",
       "3                                                      0                      \n",
       "4                                                      0                      \n",
       "5                                                      0                      \n",
       "6                                                      0                      \n",
       "7                                                      0                      \n",
       "8                                                      0                      \n",
       "9                                                      0                      \n",
       "10                                                     0                      \n",
       "11                                                     0                      \n",
       "12                                                     0                      \n",
       "13                                                     0                      \n",
       "14                                                     0                      \n",
       "15                                                     0                      \n",
       "16                                                     0                      \n",
       "17                                                     0                      \n",
       "18                                                     0                      \n",
       "19                                                     0                      \n",
       "20                                                     0                      \n",
       "21                                                     0                      \n",
       "22                                                     0                      \n",
       "23                                                     0                      \n",
       "24                                                                            \n",
       "25                                                                            \n",
       "26                                                                            \n",
       "27                                                                            \n",
       "28                                                                            \n",
       "29                                                                            \n",
       "...                                                  ...                      \n",
       "82362                                                  0                      \n",
       "82363                                                  0                      \n",
       "82364                                                  0                      \n",
       "82365                                                  0                      \n",
       "82366                                                  0                      \n",
       "82367                                                  0                      \n",
       "82368                                                  0                      \n",
       "82369                                                  0                      \n",
       "82370                                                  0                      \n",
       "82371                                                  0                      \n",
       "82372                                                  0                      \n",
       "82373                                                  0                      \n",
       "82374                                                  0                      \n",
       "82375                                                  0                      \n",
       "82376                                                  0                      \n",
       "82377                                                  0                      \n",
       "82378                                                  0                      \n",
       "82379                                                  0                      \n",
       "82380                                                  0                      \n",
       "82381                                                  0                      \n",
       "82382                                                  0                      \n",
       "82383                                                  0                      \n",
       "82384                                                  0                      \n",
       "82385                                                  0                      \n",
       "82386                                                  0                      \n",
       "82387                                                  0                      \n",
       "82388                                                  0                      \n",
       "82389                                                  0                      \n",
       "82390                                                  0                      \n",
       "82391                                                  0                      \n",
       "\n",
       "      WATER SOLD TO ANOTHER PUBLIC WATER SYSTEM  \\\n",
       "0                                             0   \n",
       "1                                             0   \n",
       "2                                             0   \n",
       "3                                             0   \n",
       "4                                             0   \n",
       "5                                             0   \n",
       "6                                             0   \n",
       "7                                             0   \n",
       "8                                             0   \n",
       "9                                             0   \n",
       "10                                            0   \n",
       "11                                            0   \n",
       "12                                            0   \n",
       "13                                            0   \n",
       "14                                            0   \n",
       "15                                            0   \n",
       "16                                            0   \n",
       "17                                            0   \n",
       "18                                            0   \n",
       "19                                            0   \n",
       "20                                            0   \n",
       "21                                            0   \n",
       "22                                            0   \n",
       "23                                            0   \n",
       "24                                                \n",
       "25                                                \n",
       "26                                                \n",
       "27                                                \n",
       "28                                                \n",
       "29                                                \n",
       "...                                         ...   \n",
       "82362                                         0   \n",
       "82363                                         0   \n",
       "82364                                         0   \n",
       "82365                                         0   \n",
       "82366                                         0   \n",
       "82367                                         0   \n",
       "82368                                         0   \n",
       "82369                                         0   \n",
       "82370                                         0   \n",
       "82371                                         0   \n",
       "82372                                         0   \n",
       "82373                                         0   \n",
       "82374                                         0   \n",
       "82375                                         0   \n",
       "82376                                         0   \n",
       "82377                                         0   \n",
       "82378                                         0   \n",
       "82379                                         0   \n",
       "82380                                         0   \n",
       "82381                                         0   \n",
       "82382                                         0   \n",
       "82383                                         0   \n",
       "82384                                         0   \n",
       "82385                                         0   \n",
       "82386                                         0   \n",
       "82387                                         0   \n",
       "82388                                         0   \n",
       "82389                                         0   \n",
       "82390                                         0   \n",
       "82391                                         0   \n",
       "\n",
       "      Non-Potable Produced Water (EXCLUDING RECYCLING)  \\\n",
       "0                                                    0   \n",
       "1                                                    0   \n",
       "2                                                    0   \n",
       "3                                                    0   \n",
       "4                                                    0   \n",
       "5                                                    0   \n",
       "6                                                    0   \n",
       "7                                                    0   \n",
       "8                                                    0   \n",
       "9                                                    0   \n",
       "10                                                   0   \n",
       "11                                                   0   \n",
       "12                                                   0   \n",
       "13                                                   0   \n",
       "14                                                   0   \n",
       "15                                                   0   \n",
       "16                                                   0   \n",
       "17                                                   0   \n",
       "18                                                   0   \n",
       "19                                                   0   \n",
       "20                                                   0   \n",
       "21                                                   0   \n",
       "22                                                   0   \n",
       "23                                                   0   \n",
       "24                                                       \n",
       "25                                                       \n",
       "26                                                       \n",
       "27                                                       \n",
       "28                                                       \n",
       "29                                                       \n",
       "...                                                ...   \n",
       "82362                                                0   \n",
       "82363                                                0   \n",
       "82364                                                0   \n",
       "82365                                                0   \n",
       "82366                                                0   \n",
       "82367                                                0   \n",
       "82368                                                0   \n",
       "82369                                                0   \n",
       "82370                                                0   \n",
       "82371                                                0   \n",
       "82372                                                0   \n",
       "82373                                                0   \n",
       "82374                                                0   \n",
       "82375                                                0   \n",
       "82376                                                0   \n",
       "82377                                                0   \n",
       "82378                                                0   \n",
       "82379                                                0   \n",
       "82380                                                0   \n",
       "82381                                                0   \n",
       "82382                                                0   \n",
       "82383                                                0   \n",
       "82384                                                0   \n",
       "82385                                                0   \n",
       "82386                                                0   \n",
       "82387                                                0   \n",
       "82388                                                0   \n",
       "82389                                                0   \n",
       "82390                                                0   \n",
       "82391                                                0   \n",
       "\n",
       "      RECYCLED WATER PRODUCED  \\\n",
       "0                           0   \n",
       "1                           0   \n",
       "2                           0   \n",
       "3                           0   \n",
       "4                           0   \n",
       "5                           0   \n",
       "6                           0   \n",
       "7                           0   \n",
       "8                           0   \n",
       "9                           0   \n",
       "10                          0   \n",
       "11                          0   \n",
       "12                          0   \n",
       "13                          0   \n",
       "14                          0   \n",
       "15                          0   \n",
       "16                          0   \n",
       "17                          0   \n",
       "18                          0   \n",
       "19                          0   \n",
       "20                          0   \n",
       "21                          0   \n",
       "22                          0   \n",
       "23                          0   \n",
       "24                              \n",
       "25                              \n",
       "26                              \n",
       "27                              \n",
       "28                              \n",
       "29                              \n",
       "...                       ...   \n",
       "82362                       0   \n",
       "82363                       0   \n",
       "82364                       0   \n",
       "82365                       0   \n",
       "82366                       0   \n",
       "82367                       0   \n",
       "82368                       0   \n",
       "82369                       0   \n",
       "82370                       0   \n",
       "82371                       0   \n",
       "82372                       0   \n",
       "82373                       0   \n",
       "82374                       0   \n",
       "82375                       0   \n",
       "82376                       0   \n",
       "82377                       0   \n",
       "82378                       0   \n",
       "82379                       0   \n",
       "82380                    0.19   \n",
       "82381                    0.88   \n",
       "82382                    0.48   \n",
       "82383                   10.85   \n",
       "82384                   17.28   \n",
       "82385                   20.01   \n",
       "82386                   19.32   \n",
       "82387                   13.12   \n",
       "82388                   18.19   \n",
       "82389                    5.22   \n",
       "82390                    0.57   \n",
       "82391                    0.14   \n",
       "\n",
       "      TOTAL POTABLE WATER PRODUCED USING REVISED UNITS  \\\n",
       "0                                               126403   \n",
       "1                                               118248   \n",
       "2                                               139399   \n",
       "3                                               127710   \n",
       "4                                               171440   \n",
       "5                                               237645   \n",
       "6                                               293738   \n",
       "7                                               276755   \n",
       "8                                               206123   \n",
       "9                                               145825   \n",
       "10                                               84083   \n",
       "11                                              210175   \n",
       "12                                              810158   \n",
       "13                                              751072   \n",
       "14                                              837333   \n",
       "15                                              457461   \n",
       "16                                              554163   \n",
       "17                                              525724   \n",
       "18                                              644280   \n",
       "19                                              695504   \n",
       "20                                              698935   \n",
       "21                                              644056   \n",
       "22                                              678351   \n",
       "23                                              675353   \n",
       "24                                                       \n",
       "25                                                       \n",
       "26                                                       \n",
       "27                                                       \n",
       "28                                                       \n",
       "29                                                       \n",
       "...                                                ...   \n",
       "82362                                            0.841   \n",
       "82363                                             0.81   \n",
       "82364                                             0.58   \n",
       "82365                                             0.41   \n",
       "82366                                             0.37   \n",
       "82367                                             0.32   \n",
       "82368                                          8232000   \n",
       "82369                                          7031000   \n",
       "82370                                          7642100   \n",
       "82371                                          9150700   \n",
       "82372                                         10594300   \n",
       "82373                                         15677100   \n",
       "82374                                         18598300   \n",
       "82375                                         17872300   \n",
       "82376                                         14439700   \n",
       "82377                                         11214600   \n",
       "82378                                          8779300   \n",
       "82379                                          8307400   \n",
       "82380                                             13.4   \n",
       "82381                                               13   \n",
       "82382                                               13   \n",
       "82383                                             26.9   \n",
       "82384                                             34.3   \n",
       "82385                                             37.8   \n",
       "82386                                             43.3   \n",
       "82387                                             50.5   \n",
       "82388                                             43.2   \n",
       "82389                                               28   \n",
       "82390                                             16.8   \n",
       "82391                                             16.3   \n",
       "\n",
       "      TOTAL POTABLE WATER IN GALLONS Population Of Service Area  \\\n",
       "0                       1.264030e+05                       50.0   \n",
       "1                       1.182480e+05                       50.0   \n",
       "2                       1.393990e+05                       50.0   \n",
       "3                       1.277100e+05                       50.0   \n",
       "4                       1.714400e+05                       50.0   \n",
       "5                       2.376450e+05                       50.0   \n",
       "6                       2.937380e+05                       50.0   \n",
       "7                       2.767550e+05                       50.0   \n",
       "8                       2.061230e+05                       50.0   \n",
       "9                       1.458250e+05                       50.0   \n",
       "10                      8.408300e+04                       50.0   \n",
       "11                      2.101750e+05                       50.0   \n",
       "12                      8.101580e+05                      543.0   \n",
       "13                      7.510720e+05                      543.0   \n",
       "14                      8.373330e+05                      543.0   \n",
       "15                      4.574610e+05                      543.0   \n",
       "16                      5.541630e+05                      543.0   \n",
       "17                      5.257240e+05                      543.0   \n",
       "18                      6.442800e+05                      543.0   \n",
       "19                      6.955040e+05                      543.0   \n",
       "20                      6.989350e+05                      543.0   \n",
       "21                      6.440560e+05                      543.0   \n",
       "22                      6.783510e+05                      543.0   \n",
       "23                      6.753530e+05                      543.0   \n",
       "24                      0.000000e+00                        NaN   \n",
       "25                      0.000000e+00                        NaN   \n",
       "26                      0.000000e+00                        NaN   \n",
       "27                      0.000000e+00                        NaN   \n",
       "28                      0.000000e+00                        NaN   \n",
       "29                      0.000000e+00                        NaN   \n",
       "...                              ...                        ...   \n",
       "82362                   8.410000e+05                      122.0   \n",
       "82363                   8.100000e+05                      122.0   \n",
       "82364                   5.800000e+05                      122.0   \n",
       "82365                   4.100000e+05                      122.0   \n",
       "82366                   3.700000e+05                      122.0   \n",
       "82367                   3.200000e+05                      122.0   \n",
       "82368                   8.232000e+12                     3105.0   \n",
       "82369                   7.031000e+12                     3105.0   \n",
       "82370                   7.642100e+12                     3105.0   \n",
       "82371                   9.150700e+12                     3105.0   \n",
       "82372                   1.059430e+13                     3105.0   \n",
       "82373                   1.567710e+13                     3105.0   \n",
       "82374                   1.859830e+13                     3105.0   \n",
       "82375                   1.787230e+13                     3105.0   \n",
       "82376                   1.443970e+13                     3105.0   \n",
       "82377                   1.121460e+13                     3105.0   \n",
       "82378                   8.779300e+12                     3105.0   \n",
       "82379                   8.307400e+12                     3105.0   \n",
       "82380                   1.340000e+07                     2100.0   \n",
       "82381                   1.300000e+07                     2100.0   \n",
       "82382                   1.300000e+07                     2100.0   \n",
       "82383                   2.690000e+07                     2100.0   \n",
       "82384                   3.430000e+07                     2100.0   \n",
       "82385                   3.780000e+07                     2100.0   \n",
       "82386                   4.330000e+07                     2100.0   \n",
       "82387                   5.050000e+07                     2100.0   \n",
       "82388                   4.320000e+07                     2100.0   \n",
       "82389                   2.800000e+07                     2100.0   \n",
       "82390                   1.680000e+07                     2100.0   \n",
       "82391                   1.630000e+07                     2100.0   \n",
       "\n",
       "      CALCULATED GPCD (Total Potable Produced in gallons per capita day)  \\\n",
       "0                                           8.155032e+01                   \n",
       "1                                           8.446286e+01                   \n",
       "2                                           8.993484e+01                   \n",
       "3                                           8.514000e+01                   \n",
       "4                                           1.106065e+02                   \n",
       "5                                           1.584300e+02                   \n",
       "6                                           1.895084e+02                   \n",
       "7                                           1.785516e+02                   \n",
       "8                                           1.374153e+02                   \n",
       "9                                           9.408065e+01                   \n",
       "10                                          5.605533e+01                   \n",
       "11                                          1.355968e+02                   \n",
       "12                                          4.812915e+01                   \n",
       "13                                          4.939963e+01                   \n",
       "14                                          4.974354e+01                   \n",
       "15                                          2.808232e+01                   \n",
       "16                                          3.292123e+01                   \n",
       "17                                          3.227281e+01                   \n",
       "18                                          3.827482e+01                   \n",
       "19                                          4.131789e+01                   \n",
       "20                                          4.290577e+01                   \n",
       "21                                          3.826151e+01                   \n",
       "22                                          4.164217e+01                   \n",
       "23                                          4.012077e+01                   \n",
       "24                                          0.000000e+00                   \n",
       "25                                          0.000000e+00                   \n",
       "26                                          0.000000e+00                   \n",
       "27                                          0.000000e+00                   \n",
       "28                                          0.000000e+00                   \n",
       "29                                          0.000000e+00                   \n",
       "...                                                  ...                   \n",
       "82362                                       2.223691e+02                   \n",
       "82363                                       2.141724e+02                   \n",
       "82364                                       1.584699e+02                   \n",
       "82365                                       1.084082e+02                   \n",
       "82366                                       1.010929e+02                   \n",
       "82367                                       8.461132e+01                   \n",
       "82368                                       8.552283e+07                   \n",
       "82369                                       8.087187e+07                   \n",
       "82370                                       7.939432e+07                   \n",
       "82371                                       9.823618e+07                   \n",
       "82372                                       1.100649e+08                   \n",
       "82373                                       1.682995e+08                   \n",
       "82374                                       1.932191e+08                   \n",
       "82375                                       1.856766e+08                   \n",
       "82376                                       1.550156e+08                   \n",
       "82377                                       1.165093e+08                   \n",
       "82378                                       9.424906e+07                   \n",
       "82379                                       8.630617e+07                   \n",
       "82380                                       2.058372e+02                   \n",
       "82381                                       2.210884e+02                   \n",
       "82382                                       1.996928e+02                   \n",
       "82383                                       4.269841e+02                   \n",
       "82384                                       5.268817e+02                   \n",
       "82385                                       6.000000e+02                   \n",
       "82386                                       6.651306e+02                   \n",
       "82387                                       7.757296e+02                   \n",
       "82388                                       6.857143e+02                   \n",
       "82389                                       4.301075e+02                   \n",
       "82390                                       2.666667e+02                   \n",
       "82391                                       2.503840e+02                   \n",
       "\n",
       "       WATER PRODUCED Water.Units in UNITS ORIGINALLY REPORTED  \\\n",
       "0                                                      G         \n",
       "1                                                      G         \n",
       "2                                                      G         \n",
       "3                                                      G         \n",
       "4                                                      G         \n",
       "5                                                      G         \n",
       "6                                                      G         \n",
       "7                                                      G         \n",
       "8                                                      G         \n",
       "9                                                      G         \n",
       "10                                                     G         \n",
       "11                                                     G         \n",
       "12                                                     G         \n",
       "13                                                     G         \n",
       "14                                                     G         \n",
       "15                                                     G         \n",
       "16                                                     G         \n",
       "17                                                     G         \n",
       "18                                                     G         \n",
       "19                                                     G         \n",
       "20                                                     G         \n",
       "21                                                     G         \n",
       "22                                                     G         \n",
       "23                                                     G         \n",
       "24                                                               \n",
       "25                                                               \n",
       "26                                                               \n",
       "27                                                               \n",
       "28                                                               \n",
       "29                                                               \n",
       "...                                                  ...         \n",
       "82362                                                 MG         \n",
       "82363                                                 MG         \n",
       "82364                                                 MG         \n",
       "82365                                                 MG         \n",
       "82366                                                 MG         \n",
       "82367                                                 MG         \n",
       "82368                                                 MG         \n",
       "82369                                                 MG         \n",
       "82370                                                 MG         \n",
       "82371                                                 MG         \n",
       "82372                                                 MG         \n",
       "82373                                                 MG         \n",
       "82374                                                 MG         \n",
       "82375                                                 MG         \n",
       "82376                                                 MG         \n",
       "82377                                                 MG         \n",
       "82378                                                 MG         \n",
       "82379                                                 MG         \n",
       "82380                                                 MG         \n",
       "82381                                                 MG         \n",
       "82382                                                 MG         \n",
       "82383                                                 MG         \n",
       "82384                                                 MG         \n",
       "82385                                                 MG         \n",
       "82386                                                 MG         \n",
       "82387                                                 MG         \n",
       "82388                                                 MG         \n",
       "82389                                                 MG         \n",
       "82390                                                 MG         \n",
       "82391                                                 MG         \n",
       "\n",
       "       WATER PRODUCED FROM SURFACEWATER  \n",
       "0                                     0  \n",
       "1                                     0  \n",
       "2                                     0  \n",
       "3                                     0  \n",
       "4                                     0  \n",
       "5                                     0  \n",
       "6                                     0  \n",
       "7                                     0  \n",
       "8                                     0  \n",
       "9                                     0  \n",
       "10                                    0  \n",
       "11                                    0  \n",
       "12                                    0  \n",
       "13                                    0  \n",
       "14                                    0  \n",
       "15                                    0  \n",
       "16                                    0  \n",
       "17                                    0  \n",
       "18                                    0  \n",
       "19                                    0  \n",
       "20                                    0  \n",
       "21                                    0  \n",
       "22                                    0  \n",
       "23                                    0  \n",
       "24                                       \n",
       "25                                       \n",
       "26                                       \n",
       "27                                       \n",
       "28                                       \n",
       "29                                       \n",
       "...                                 ...  \n",
       "82362                             0.841  \n",
       "82363                              0.81  \n",
       "82364                              0.58  \n",
       "82365                              0.41  \n",
       "82366                              0.37  \n",
       "82367                              0.32  \n",
       "82368                           8232000  \n",
       "82369                           7031000  \n",
       "82370                           7642100  \n",
       "82371                           9150700  \n",
       "82372                          10594300  \n",
       "82373                          15677100  \n",
       "82374                          18598300  \n",
       "82375                          17872300  \n",
       "82376                          14439700  \n",
       "82377                          11214600  \n",
       "82378                           8779300  \n",
       "82379                           8307400  \n",
       "82380                                 0  \n",
       "82381                                 0  \n",
       "82382                                 0  \n",
       "82383                                 0  \n",
       "82384                                 0  \n",
       "82385                                 0  \n",
       "82386                                 0  \n",
       "82387                                 0  \n",
       "82388                                 0  \n",
       "82389                                 0  \n",
       "82390                                 0  \n",
       "82391                                 0  \n",
       "\n",
       "[82392 rows x 23 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dList=[]\n",
    "for index,row in dataAll.iterrows():\n",
    "#for index,row in dataAll.head().iterrows():\n",
    "    for month,num_days in months.items():\n",
    "        gwStr = \"WP \" + month + \" GW\"\n",
    "        swStr = \"WP \" + month + \" SW\"\n",
    "        purStr = \"WP \" + month + \" Purchased\"\n",
    "        totStr = \"WP \" + month + \" Total\"\n",
    "        soldStr = \"WP \" + month + \" Sold\"\n",
    "        nonPStr = \"WP \" + month + \" NonPotable\"\n",
    "        recStr = \"WP \" + month + \" Recycled\"\n",
    "        dTemp = OrderedDict.fromkeys(colList)\n",
    "        dTemp['PWSID'] = index\n",
    "        dTemp['Water.System.Name'] = row['Water System Name']\n",
    "        dTemp['Water.System.Classification'] = row['Water System Classification']\n",
    "        dTemp['Year'] = 2016 #This is hardcoded for now, but this is not a good permanent solution\n",
    "        dTemp['Month'] = month\n",
    "        dTemp['Date'] = month + \" 1, 2016\"\n",
    "        dTemp['Days.In.Month'] = num_days\n",
    "        dTemp['WATER PRODUCED Water.Units in UNITS ORIGINALLY REPORTED'] = row['WP Units of Measure']\n",
    "        dTemp['Finished.Water.Vol.Type.Revised'] = row['WP Volume Type']\n",
    "        dTemp['WATER PRODUCED FROM GROUNDWATER'] = row[gwStr]\n",
    "        dTemp['WATER PRODUCED FROM SURFACEWATER'] = row[swStr]\n",
    "        dTemp['FINISHED WATER PURCHASED OR RECEIVED FROM ANOTHER PUBLIC WATER SYSTEM'] = row[purStr]\n",
    "        dTemp['WATER SOLD TO ANOTHER PUBLIC WATER SYSTEM'] = row[soldStr]\n",
    "        dTemp['Non-Potable Produced Water (EXCLUDING RECYCLING)'] = row[nonPStr]\n",
    "        dTemp['RECYCLED WATER PRODUCED'] = row[recStr]\n",
    "        dTemp['TOTAL POTABLE WATER PRODUCED USING REVISED UNITS'] = row[totStr] \n",
    "        num_gals = 0\n",
    "        totFl = 0\n",
    "        try:\n",
    "            totFl = float(row[totStr])\n",
    "        except ValueError:\n",
    "            totFl = 0\n",
    "        if row['WP Units of Measure'] in convToGal:\n",
    "            convFactor = convToGal[row['WP Units of Measure']]\n",
    "            num_gals = totFl*convFactor\n",
    "        dTemp['TOTAL POTABLE WATER IN GALLONS'] = num_gals\n",
    "        try:\n",
    "            num_pop = float(row['Population'])\n",
    "        except ValueError:\n",
    "            num_pop = 0\n",
    "        dTemp['Population Of Service Area'] = num_pop\n",
    "        gpcd = 0\n",
    "        if (num_pop > 0 and num_days > 0): \n",
    "            gpcd = (num_gals/num_pop)/num_days\n",
    "        dTemp['CALCULATED GPCD (Total Potable Produced in gallons per capita day)'] = gpcd\n",
    "        dList.append(dTemp)\n",
    "dfProd = pd.DataFrame(dList)\n",
    "dfProd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Potential quality control issues:\n",
    "\n",
    "-Units could be incorrect. This can be addressed by calculating the GPCD and comparing to average values. \n",
    "\n",
    "-Numerical values (e.g. population, number of gallons) could be given as strings. \n",
    "\n",
    "-Missing values. Since this report is filed each year by all public water systems, we can use the redundancy in the data to your advantage. For instance, if the population of the PWS is not given in 2017, we can use the 2016 value as a reasonable estimate.\n",
    "\n",
    "Further improvements: the analysis loop and output can be easily manipulated to produce the desired data format. More quality control can be integrated (either within or outside of the main analysis loop). \n",
    "\n",
    "Finally, we write the output to csv format."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfProd.to_csv(\"outputData/WaterProduced.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}