Normal vs. Drought Year Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Normal Year vs. Multi-Year Drought\n",
    "\n",
    "## Retail Comparison\n",
    "\n",
    "Identifies which utilities experience a significant change in water supply and demand in normal years and multi-year droughts."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Set-up\n",
    "\n",
    "Import packages and data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import packages\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import normal year retail excel table\n",
    "normal = pd.read_csv('normal_retail.csv')\n",
    "\n",
    "# Import multi-year drought retail excel table\n",
    "drought = pd.read_csv('drought_retail.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create normal and drought supply & demand tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Normal Supply Retail\n",
    "normal_supply = normal[normal['TOTAL_TYPE'] == 'Supply totals']\n",
    "normal_supply = normal_supply.drop(columns = ['ORG_ID', 'WORKSHEET_NAME', 'REVIEWED_BY_DWR', 'REQUIREMENTS_ADDRESSED', 'TOTAL_TYPE'])\n",
    "normal_supply = normal_supply.rename(index = str, columns = {'WATER_SUPPLIER_NAME':'water_supplier', 'TOTAL_VOLUME_2020_AF':'2020', 'TOTAL_VOLUME_2025_AF':'2025', 'TOTAL_VOLUME_2030_AF':'2030', 'TOTAL_VOLUME_2035_AF':'2035', 'TOTAL_VOLUME_2040_AF':'2040'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Normal Demand Retail\n",
    "normal_demand = normal[normal['TOTAL_TYPE'] == 'Demand totals']\n",
    "normal_demand = normal_demand.drop(columns = ['ORG_ID', 'WORKSHEET_NAME', 'REVIEWED_BY_DWR', 'REQUIREMENTS_ADDRESSED', 'TOTAL_TYPE'])\n",
    "normal_demand = normal_demand.rename(index = str, columns = {'WATER_SUPPLIER_NAME':'water_supplier', 'TOTAL_VOLUME_2020_AF':'2020', 'TOTAL_VOLUME_2025_AF':'2025', 'TOTAL_VOLUME_2030_AF':'2030', 'TOTAL_VOLUME_2035_AF':'2035', 'TOTAL_VOLUME_2040_AF':'2040'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drought Supply Retail\n",
    "drought_supply = drought[drought['TOTAL_TYPE'] == 'Supply totals']\n",
    "drought_supply = drought_supply.drop(columns = ['ORG_ID', 'WORKSHEET_NAME', 'REVIEWED_BY_DWR', 'REQUIREMENTS_ADDRESSED', 'WP_WUEDATA_PLAN_ID', 'TOTAL_TYPE'])\n",
    "drought_supply = drought_supply.rename(index = str, columns = {'WATER_SUPPLIER_NAME':'water_supplier', 'YEAR_TYPE':'year_type', 'TOTAL_VOLUME_2020_AF':'2020', 'TOTAL_VOLUME_2025_AF':'2025', 'TOTAL_VOLUME_2030_AF':'2030', 'TOTAL_VOLUME_2035_AF':'2035', 'TOTAL_VOLUME_2040_AF':'2040'})\n",
    "drought_supply = drought_supply.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drought Demand Retail\n",
    "drought_demand = drought[drought['TOTAL_TYPE'] == 'Demand totals']\n",
    "drought_demand = drought_demand.drop(columns = ['ORG_ID', 'WORKSHEET_NAME', 'REVIEWED_BY_DWR', 'REQUIREMENTS_ADDRESSED', 'WP_WUEDATA_PLAN_ID', 'TOTAL_TYPE'])\n",
    "drought_demand = drought_demand.rename(index = str, columns = {'WATER_SUPPLIER_NAME':'water_supplier', 'YEAR_TYPE':'year_type', 'TOTAL_VOLUME_2020_AF':'2020', 'TOTAL_VOLUME_2025_AF':'2025', 'TOTAL_VOLUME_2030_AF':'2030', 'TOTAL_VOLUME_2035_AF':'2035', 'TOTAL_VOLUME_2040_AF':'2040'})\n",
    "drought_demand = drought_demand.dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1) Supply\n",
    "\n",
    "Find percent change in utilities' supplies."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Select suppliers that are present in both normal & drought data\n",
    "drt_sup = drought_supply.loc[drought_supply['water_supplier'].isin(normal_supply['water_supplier'])]\n",
    "nrm_sup = normal_supply.loc[normal_supply['water_supplier'].isin(drt_sup['water_supplier'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Normal & drought water suppliers\n",
    "norm_sup = nrm_sup['water_supplier']\n",
    "drt_sup = drt_sup['water_supplier']\n",
    "\n",
    "# Check if suppliers are the same\n",
    "# norm_sup.unique() == drt_sup.unique()\n",
    "\n",
    "# Create supply change DataFrame\n",
    "chg_sup = pd.DataFrame(columns = ['water_supplier', 'supply_change'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Change in supplies for third year drought & normal year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "scrolled": true
   },
   "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>water_supplier</th>\n",
       "      <th>2020</th>\n",
       "      <th>2025</th>\n",
       "      <th>2030</th>\n",
       "      <th>2035</th>\n",
       "      <th>2040</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Adelanto  City Of</td>\n",
       "      <td>9300.0</td>\n",
       "      <td>10444.0</td>\n",
       "      <td>11581.0</td>\n",
       "      <td>12640.0</td>\n",
       "      <td>13750.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Alameda County Water District</td>\n",
       "      <td>77200.0</td>\n",
       "      <td>76900.0</td>\n",
       "      <td>76600.0</td>\n",
       "      <td>76300.0</td>\n",
       "      <td>76000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Alhambra  City Of</td>\n",
       "      <td>12913.0</td>\n",
       "      <td>13273.0</td>\n",
       "      <td>13634.0</td>\n",
       "      <td>13995.0</td>\n",
       "      <td>14354.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Amador Water Agency</td>\n",
       "      <td>18343.0</td>\n",
       "      <td>18884.0</td>\n",
       "      <td>18884.0</td>\n",
       "      <td>18884.0</td>\n",
       "      <td>18884.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>American Canyon  City Of</td>\n",
       "      <td>7560.0</td>\n",
       "      <td>8265.0</td>\n",
       "      <td>8470.0</td>\n",
       "      <td>8981.0</td>\n",
       "      <td>8981.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  water_supplier     2020     2025     2030     2035     2040\n",
       "1              Adelanto  City Of   9300.0  10444.0  11581.0  12640.0  13750.0\n",
       "3  Alameda County Water District  77200.0  76900.0  76600.0  76300.0  76000.0\n",
       "5              Alhambra  City Of  12913.0  13273.0  13634.0  13995.0  14354.0\n",
       "6            Amador Water Agency  18343.0  18884.0  18884.0  18884.0  18884.0\n",
       "8       American Canyon  City Of   7560.0   8265.0   8470.0   8981.0   8981.0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "normal_supply.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1                Adelanto  City Of\n",
       "3    Alameda County Water District\n",
       "5                Alhambra  City Of\n",
       "6              Amador Water Agency\n",
       "8         American Canyon  City Of\n",
       "Name: water_supplier, dtype: object"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "norm_sup.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:29: RuntimeWarning: divide by zero encountered in double_scalars\n",
      "/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:29: RuntimeWarning: divide by zero encountered in true_divide\n"
     ]
    }
   ],
   "source": [
    "# For each normal water supplier \n",
    "for n_index, n_utility in norm_sup.iteritems():\n",
    "    \n",
    "    # Create array of normal supplies - each column is a year (2020, 2025, etc.)\n",
    "    nrm = normal_supply.loc[n_index, :][1:6].to_numpy()\n",
    "    \n",
    "    # For each drought water supplier\n",
    "    for d_index, d_utility in drt_sup.iteritems():\n",
    "        \n",
    "        # If normal supplier == drought supplier\n",
    "        if n_utility == d_utility:\n",
    "            \n",
    "            # Year type of drought (i.e. First year, Second year, Third Year)\n",
    "            year_type = drought_supply['year_type'][d_index]\n",
    "           \n",
    "            # If Year type = Third year\n",
    "            if 'Third' in year_type:\n",
    "                \n",
    "                # Convert drought supply for third year into array\n",
    "                drt = drought_supply.loc[d_index, :][2:7].to_numpy()\n",
    "        \n",
    "                # Find difference between normal & drought supplies for third year\n",
    "                differ = drt - nrm\n",
    "                \n",
    "                # If normal & drought supplies are not the same for all five years (2020, 2025, etc.)\n",
    "                if all([difference != 0 for difference in differ]):\n",
    "                    \n",
    "                    # Find change between normal & drought supplies\n",
    "                    change = differ / nrm\n",
    "                    \n",
    "                    # Add utility name & supply change to the supply change DataFrame\n",
    "                    chg_sup = chg_sup.append({'water_supplier': d_utility, 'supply_change': change}, ignore_index = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Average change in supplies for third year drought & normal year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Change supplies\n",
    "chg_supply = chg_sup['supply_change']\n",
    "\n",
    "# Create average change DataFrame\n",
    "avg_chg_sup = pd.DataFrame(columns = ['water_supplier', 'supply_avg_chg', '+/-'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# For each water supplier\n",
    "for c_index, c_change in chg_supply.iteritems():\n",
    "    \n",
    "    # Determine utility name\n",
    "    utility = chg_sup.loc[c_index, 'water_supplier']\n",
    "    \n",
    "    # For yearly supply change\n",
    "    for i, change in enumerate(c_change):\n",
    "        \n",
    "        # If change is infite\n",
    "        if change == np.inf :\n",
    "            \n",
    "            # Set change to zero\n",
    "            c_change[i] = 0\n",
    "\n",
    "    # Find average supply change for each supplier\n",
    "    average = np.mean(c_change)\n",
    "    \n",
    "    # Determine if change is positive or negative (positive = supplies increase in drought, negative = supplies decrease in drought)\n",
    "    if average > 0:\n",
    "        sign = '+'\n",
    "    else:\n",
    "        sign = '-'\n",
    "    \n",
    "    # Add utility name & average supply change to the average change DataFrame\n",
    "    avg_chg_sup = avg_chg_sup.append({'water_supplier': utility, 'supply_avg_chg': abs(average), '+/-': sign}, ignore_index = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Top 50 utilities in regards to supply changes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Top 50 absolute supply changes\n",
    "top_50_sup = avg_chg_sup.nlargest(50, 'supply_avg_chg')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Chowchilla Report Discrepancy?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Supplies drastically increase in drought years compared to normal years, by a whole order of magnitude. Possible reporting error."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>water_supplier</th>\n",
       "      <th>2020</th>\n",
       "      <th>2025</th>\n",
       "      <th>2030</th>\n",
       "      <th>2035</th>\n",
       "      <th>2040</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>Chowchilla, City of Water Department</td>\n",
       "      <td>5149.593812</td>\n",
       "      <td>6898.859887</td>\n",
       "      <td>8467.061578</td>\n",
       "      <td>10701.21193</td>\n",
       "      <td>11661.77383</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           water_supplier         2020         2025  \\\n",
       "150  Chowchilla, City of Water Department  5149.593812  6898.859887   \n",
       "\n",
       "            2030         2035         2040  \n",
       "150  8467.061578  10701.21193  11661.77383  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "normal_supply[normal_supply['water_supplier'] == 'Chowchilla, City of Water Department']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:1: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>water_supplier</th>\n",
       "      <th>year_type</th>\n",
       "      <th>2020</th>\n",
       "      <th>2025</th>\n",
       "      <th>2030</th>\n",
       "      <th>2035</th>\n",
       "      <th>2040</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>907</th>\n",
       "      <td>Chowchilla, City of Water Department</td>\n",
       "      <td>Third year</td>\n",
       "      <td>491807.6913</td>\n",
       "      <td>491807.6913</td>\n",
       "      <td>491807.6913</td>\n",
       "      <td>491807.6913</td>\n",
       "      <td>491807.6913</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           water_supplier   year_type         2020  \\\n",
       "907  Chowchilla, City of Water Department  Third year  491807.6913   \n",
       "\n",
       "            2025         2030         2035         2040  \n",
       "907  491807.6913  491807.6913  491807.6913  491807.6913  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drought_supply[drought_supply['water_supplier'] == 'Chowchilla, City of Water Department'][drought_supply['year_type'] == 'Third year']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2) Demand\n",
    "\n",
    "Find percent change in utilities' demands."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Select suppliers that are present in both normal & drought data\n",
    "drt_dem = drought_demand.loc[drought_demand['water_supplier'].isin(normal_supply['water_supplier'])]\n",
    "nrm_dem = normal_demand.loc[normal_demand['water_supplier'].isin(drt_dem['water_supplier'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Normal & drought water suppliers\n",
    "norm_dem = nrm_dem['water_supplier']\n",
    "drt_dem = drt_dem['water_supplier']\n",
    "\n",
    "# Check if suppliers are the same\n",
    "# norm_dem.unique() == drt_dem.unique()\n",
    "\n",
    "# Create demand change DataFrame\n",
    "chg_dem = pd.DataFrame(columns = ['water_supplier', 'demand_change'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Change in demands for third year drought & normal year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For each normal water supplier \n",
    "for n_index, n_utility in norm_dem.iteritems():\n",
    "    \n",
    "    # Create array of normal demand - each column is a year (2020, 2025, etc.)\n",
    "    nrm = normal_demand.loc[n_index, :][1:6].to_numpy()\n",
    "    \n",
    "    # For each drought water supplier\n",
    "    for d_index, d_utility in drt_dem.iteritems():\n",
    "        \n",
    "        # If normal supplier == drought supplier\n",
    "        if n_utility == d_utility:\n",
    "            \n",
    "            # Year type of drought (i.e. First year, Second year, Third Year)\n",
    "            year_type = drought_demand['year_type'][d_index]\n",
    "\n",
    "            # If Year type = Third year\n",
    "            if 'Third' in year_type:\n",
    "                \n",
    "                # Convert drought supply for third year into array\n",
    "                drt = drought_demand.loc[d_index, :][2:7].to_numpy()\n",
    "        \n",
    "                # Find difference between normal & drought demand for third year\n",
    "                differ = drt - nrm\n",
    "                \n",
    "                # If normal & drought demand are not the same for all five years (2020, 2025, etc.)\n",
    "                if all([difference != 0 for difference in differ]):\n",
    "                    \n",
    "                    # Find change between normal & drought demand\n",
    "                    change = differ / nrm\n",
    "                    \n",
    "                    # Add utility name & demand change to the demand change DataFrame\n",
    "                    chg_dem = chg_dem.append({'water_supplier': d_utility, 'demand_change': change}, ignore_index = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Average change in demands for third year drought & normal year."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Change demands\n",
    "chg_demand = chg_dem['demand_change']\n",
    "\n",
    "# Create average change DataFrame\n",
    "avg_chg_dem = pd.DataFrame(columns = ['water_supplier', 'demand_avg_chg', '+/-'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For each water supplier\n",
    "for c_index, c_change in chg_demand.iteritems():\n",
    "    \n",
    "    # Determine utility name\n",
    "    utility = chg_dem.loc[c_index, 'water_supplier']\n",
    "    \n",
    "    # For yearly supply change\n",
    "    for i, change in enumerate(c_change):\n",
    "        \n",
    "        # If change is infite\n",
    "        if change == np.inf :\n",
    "            \n",
    "            # Set change to zero\n",
    "            c_change[i] = 0\n",
    "\n",
    "    # Find average demand change for each supplier\n",
    "    average = np.mean(c_change)\n",
    "    \n",
    "    # Determine if change is positive or negative (positive = demands increase in drought, negative = demands decrease in drought)\n",
    "    if average > 0:\n",
    "        sign = '+'\n",
    "    else:\n",
    "        sign = '-'\n",
    "    \n",
    "    # Add utility name & average demand change to the average change DataFrame\n",
    "    avg_chg_dem = avg_chg_dem.append({'water_supplier': utility, 'demand_avg_chg': abs(average), '+/-': sign}, ignore_index = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Top 50 utilities in regards to supply changes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Top 50 absolute demand changes\n",
    "top_50_dem = avg_chg_dem.nlargest(50, 'demand_avg_chg')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### South Feather Report Discrepancy?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Demands drastically increase in drought years compared to normal years, by a whole order of magnitude. Possible reporting error."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "scrolled": true
   },
   "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>water_supplier</th>\n",
       "      <th>2020</th>\n",
       "      <th>2025</th>\n",
       "      <th>2030</th>\n",
       "      <th>2035</th>\n",
       "      <th>2040</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>681</th>\n",
       "      <td>South Feather Water and Power</td>\n",
       "      <td>4004.898644</td>\n",
       "      <td>4118.447495</td>\n",
       "      <td>4176.756364</td>\n",
       "      <td>4231.996345</td>\n",
       "      <td>4290.305214</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    water_supplier         2020         2025         2030  \\\n",
       "681  South Feather Water and Power  4004.898644  4118.447495  4176.756364   \n",
       "\n",
       "            2035         2040  \n",
       "681  4231.996345  4290.305214  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "normal_demand[normal_demand['water_supplier'] == 'South Feather Water and Power']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:1: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>water_supplier</th>\n",
       "      <th>year_type</th>\n",
       "      <th>2020</th>\n",
       "      <th>2025</th>\n",
       "      <th>2030</th>\n",
       "      <th>2035</th>\n",
       "      <th>2040</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4091</th>\n",
       "      <td>South Feather Water and Power</td>\n",
       "      <td>Third year</td>\n",
       "      <td>12290.89584</td>\n",
       "      <td>12637.68017</td>\n",
       "      <td>12818.74455</td>\n",
       "      <td>12987.53338</td>\n",
       "      <td>13165.52888</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     water_supplier   year_type         2020         2025  \\\n",
       "4091  South Feather Water and Power  Third year  12290.89584  12637.68017   \n",
       "\n",
       "             2030         2035         2040  \n",
       "4091  12818.74455  12987.53338  13165.52888  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "drought_demand[drought_demand['water_supplier'] == 'South Feather Water and Power'][drought_demand['year_type'] == 'Third year']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.5) Top 50 Supply & Demand"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Determine which utilities are in the top 50 for both supply and demand."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Supply & demand water suppliers\n",
    "sup_50 = top_50_sup['water_supplier']\n",
    "dem_50 = top_50_dem['water_supplier']\n",
    "\n",
    "# Create both top 50 dataframe\n",
    "both_top_50 = pd.DataFrame(columns = ['water_supplier', 'supply_avg_chg', '+/-', 'demand_avg_chg', '+/-'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For each top 50 supplier \n",
    "for s_index, s_utility in sup_50.iteritems():\n",
    "    \n",
    "    # For each top 50 demander\n",
    "    for d_index, d_utility in dem_50.iteritems():\n",
    "        \n",
    "        # If supplier == demander\n",
    "        if s_utility == d_utility:\n",
    "            \n",
    "            # If utility not already listed in both_top_50\n",
    "            if d_utility not in both_top_50['water_supplier']:\n",
    "                \n",
    "                # Find supply data\n",
    "                sup_chg = top_50_sup['supply_avg_chg'][s_index]\n",
    "                sup_sign = top_50_sup['+/-'][s_index]\n",
    "\n",
    "                # Find demand data\n",
    "                dem_chg = top_50_dem['demand_avg_chg'][d_index]\n",
    "                dem_sign = top_50_dem['+/-'][d_index]\n",
    "\n",
    "                # Add utility name, change & sign to the both top 50 DataFrame\n",
    "                both_top_50 = both_top_50.append({'water_supplier': d_utility, 'supply_avg_chg': sup_chg, '+/-': sup_sign, 'demand_avg_chg': dem_chg, '+/-': dem_sign}, ignore_index = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3) Supply - Demand"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Supply minus demand for third year drought, years 2020-2035."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Third year drought supply\n",
    "drt3_sup = drought_supply[drought_supply['year_type'] == 'Third year'].drop(columns = ['year_type', '2040'])\n",
    "\n",
    "# Third year drought demand\n",
    "drt3_dem = drought_demand[drought_demand['year_type'] == 'Third year'].drop(columns = ['year_type', '2040'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Third year drought water suppliers & demanders\n",
    "drought3_sup = drt3_sup['water_supplier']\n",
    "drought3_dem = drt3_dem['water_supplier']\n",
    "\n",
    "# Create third year supply minus demand DataFrame\n",
    "sup_minus_dem = pd.DataFrame(columns = ['water_supplier', '2020', '2025', '2030', '2035'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# For each third year drought supplier\n",
    "for s_index, s_utility in drought3_sup.iteritems():\n",
    "    \n",
    "    # For each third year drought demander\n",
    "    for d_index, d_utility in drought3_dem.iteritems():\n",
    "        \n",
    "        # If supplier == demander\n",
    "        if s_utility == d_utility:\n",
    "            \n",
    "            # Find supplier quantities\n",
    "            sup_values = drt3_sup.loc[s_index][1:6].to_numpy()\n",
    "            \n",
    "            # Find demander quantities\n",
    "            dem_values = drt3_dem.loc[d_index][1:6].to_numpy()\n",
    "            \n",
    "            # Find supply - demand\n",
    "            dif = sup_values - dem_values\n",
    "                    \n",
    "            # Add utility name & supply - demand to sup_minus_dem DataFrame\n",
    "            sup_minus_dem = sup_minus_dem.append({'water_supplier': d_utility, '2020': dif[0], '2025': dif[1], '2030': dif[2], '2035': dif[3]}, ignore_index = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Bottom 50 supply minus demand for year 2035. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Bottom 50 supply - demand for 2035\n",
    "bot_50_sup_dem = sup_minus_dem.sort_values(by = ['2035'], ascending = False).nsmallest(50, '2035')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.5) Top 50 Supply &/Minus Demand "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Determine which utilities are in top 50 for supply minus demand as well as supply and demand."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Supply &/minus demand water suppliers\n",
    "and_50 = both_top_50['water_supplier']\n",
    "minus_50 = bot_50_sup_dem['water_supplier']\n",
    "\n",
    "# Create all top 50 dataframe\n",
    "all_top_50 = pd.DataFrame(columns = ['water_supplier', 'supply_avg_chg', '+/-', 'demand_avg_chg', '+/-', '2020', '2025', '2030', '2035'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For each top 50 supply - demand\n",
    "for m_index, m_utility in minus_50.iteritems():\n",
    "    \n",
    "    # For each top 50 supply & minus\n",
    "    for a_index, a_utility in and_50.iteritems():\n",
    "        \n",
    "        # If supply & demand == supply - minus\n",
    "        if m_utility == a_utility:\n",
    "            \n",
    "            # If utility not already listed in all_top_50\n",
    "            if a_utility not in all_top_50['water_supplier']:\n",
    "                \n",
    "                # Find supply & demand data\n",
    "                sad = both_top_50.loc[a_index][1:5].to_numpy()\n",
    "\n",
    "                # Find supply - demand data\n",
    "                smd = bot_50_sup_dem.loc[m_index][1:5].to_numpy()\n",
    "\n",
    "                # Add utility name, change, sign & difference to the all top 50 DataFrame\n",
    "                all_top_50 = all_top_50.append({'water_supplier': a_utility, 'supply_avg_chg': sad[0], '+/-': sad[1], 'demand_avg_chg': sad[2], '+/-': sad[3], '2020': smd[0], '2025': smd[1], '2030': smd[2], '2035': smd[3]}, ignore_index = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4) Results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Top 50 supply changes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>water_supplier</th>\n",
       "      <th>supply_avg_chg</th>\n",
       "      <th>+/-</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>Chowchilla, City of Water Department</td>\n",
       "      <td>61.601601</td>\n",
       "      <td>+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>Hi Desert Water District</td>\n",
       "      <td>0.783197</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Beaumont - Cherry Valley Water District</td>\n",
       "      <td>0.639903</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td>South Feather Water and Power</td>\n",
       "      <td>0.611071</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>Marin Municipal Water District</td>\n",
       "      <td>0.603575</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Benicia  City Of</td>\n",
       "      <td>0.596912</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>Paradise Irrigation District</td>\n",
       "      <td>0.565220</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>Ripon  City Of</td>\n",
       "      <td>0.529960</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>188</th>\n",
       "      <td>Shasta Lake  City Of</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>203</th>\n",
       "      <td>Ukiah  City Of</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221</th>\n",
       "      <td>Yuba City</td>\n",
       "      <td>0.487050</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>Morro Bay  City Of</td>\n",
       "      <td>0.443116</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Carmichael Water District</td>\n",
       "      <td>0.437586</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163</th>\n",
       "      <td>Rubio Canyon Land And Water Association</td>\n",
       "      <td>0.431537</td>\n",
       "      <td>+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Bella Vista Water District</td>\n",
       "      <td>0.426750</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Brentwood  City Of</td>\n",
       "      <td>0.409148</td>\n",
       "      <td>+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>Morgan Hill  City Of</td>\n",
       "      <td>0.404793</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>Gilroy  City Of</td>\n",
       "      <td>0.395735</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>El Dorado Irrigation District</td>\n",
       "      <td>0.388685</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>Vallejo  City Of</td>\n",
       "      <td>0.381275</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>Coastside County Water District</td>\n",
       "      <td>0.363000</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>Millbrae  City Of</td>\n",
       "      <td>0.361519</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>Santa Maria  City Of</td>\n",
       "      <td>0.350458</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>214</th>\n",
       "      <td>Westborough Water District</td>\n",
       "      <td>0.348548</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>Estero Municipal Improvement District</td>\n",
       "      <td>0.335190</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>Hayward  City Of</td>\n",
       "      <td>0.332735</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>Madera  City Of</td>\n",
       "      <td>0.325465</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>176</th>\n",
       "      <td>San Jose Water Company</td>\n",
       "      <td>0.324569</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>212</th>\n",
       "      <td>West Kern Water District</td>\n",
       "      <td>0.321277</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>119</th>\n",
       "      <td>Menlo Park  City Of</td>\n",
       "      <td>0.319632</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200</th>\n",
       "      <td>Triunfo Sanitation District/Oak Park Water Ser...</td>\n",
       "      <td>0.318807</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>East Bay Municipal Utility District</td>\n",
       "      <td>0.312589</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>Groveland Community Services District</td>\n",
       "      <td>0.300606</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>Reedley  City Of</td>\n",
       "      <td>0.300026</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179</th>\n",
       "      <td>Sanger  City Of</td>\n",
       "      <td>0.300022</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>Livingston  City Of</td>\n",
       "      <td>0.299992</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>135</th>\n",
       "      <td>Newman  City of</td>\n",
       "      <td>0.299892</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>Kerman  City Of</td>\n",
       "      <td>0.299887</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>Nevada Irrigation District</td>\n",
       "      <td>0.298268</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>Hillsborough  Town Of</td>\n",
       "      <td>0.290985</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>Fillmore  City Of</td>\n",
       "      <td>0.287053</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>Roseville  City Of</td>\n",
       "      <td>0.285384</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194</th>\n",
       "      <td>Suburban Water Systems - San Jose Hills</td>\n",
       "      <td>0.283553</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>American Canyon  City Of</td>\n",
       "      <td>0.280479</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Burlingame  City Of</td>\n",
       "      <td>0.279204</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>Calaveras County Water District</td>\n",
       "      <td>0.278258</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>Mid-Peninsula Water District</td>\n",
       "      <td>0.277465</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>Fresno  City Of</td>\n",
       "      <td>0.273912</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>Pico Water District</td>\n",
       "      <td>0.270236</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>195</th>\n",
       "      <td>Suburban Water Systems - Whittier/La Mirada</td>\n",
       "      <td>0.258443</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        water_supplier  supply_avg_chg +/-\n",
       "54                Chowchilla, City of Water Department       61.601601   +\n",
       "94                            Hi Desert Water District        0.783197   -\n",
       "10             Beaumont - Cherry Valley Water District        0.639903   -\n",
       "190                      South Feather Water and Power        0.611071   -\n",
       "117                     Marin Municipal Water District        0.603575   -\n",
       "13                                    Benicia  City Of        0.596912   -\n",
       "144                       Paradise Irrigation District        0.565220   -\n",
       "160                                     Ripon  City Of        0.529960   -\n",
       "188                               Shasta Lake  City Of        0.500000   -\n",
       "203                                     Ukiah  City Of        0.500000   -\n",
       "221                                          Yuba City        0.487050   -\n",
       "130                                 Morro Bay  City Of        0.443116   -\n",
       "50                           Carmichael Water District        0.437586   -\n",
       "163            Rubio Canyon Land And Water Association        0.431537   +\n",
       "11                          Bella Vista Water District        0.426750   -\n",
       "17                                  Brentwood  City Of        0.409148   +\n",
       "129                               Morgan Hill  City Of        0.404793   -\n",
       "85                                     Gilroy  City Of        0.395735   -\n",
       "72                       El Dorado Irrigation District        0.388685   -\n",
       "206                                   Vallejo  City Of        0.381275   -\n",
       "56                     Coastside County Water District        0.363000   -\n",
       "122                                  Millbrae  City Of        0.361519   -\n",
       "184                               Santa Maria  City Of        0.350458   -\n",
       "214                         Westborough Water District        0.348548   -\n",
       "77               Estero Municipal Improvement District        0.335190   -\n",
       "92                                    Hayward  City Of        0.332735   -\n",
       "114                                    Madera  City Of        0.325465   -\n",
       "176                             San Jose Water Company        0.324569   -\n",
       "212                           West Kern Water District        0.321277   -\n",
       "119                                Menlo Park  City Of        0.319632   -\n",
       "200  Triunfo Sanitation District/Oak Park Water Ser...        0.318807   -\n",
       "66                 East Bay Municipal Utility District        0.312589   -\n",
       "90               Groveland Community Services District        0.300606   -\n",
       "158                                   Reedley  City Of        0.300026   -\n",
       "179                                    Sanger  City Of        0.300022   -\n",
       "109                                Livingston  City Of        0.299992   -\n",
       "135                                    Newman  City of        0.299892   -\n",
       "99                                     Kerman  City Of        0.299887   -\n",
       "133                         Nevada Irrigation District        0.298268   -\n",
       "95                               Hillsborough  Town Of        0.290985   -\n",
       "79                                   Fillmore  City Of        0.287053   -\n",
       "162                                 Roseville  City Of        0.285384   -\n",
       "194            Suburban Water Systems - San Jose Hills        0.283553   -\n",
       "2                             American Canyon  City Of        0.280479   -\n",
       "20                                 Burlingame  City Of        0.279204   -\n",
       "21                     Calaveras County Water District        0.278258   -\n",
       "121                       Mid-Peninsula Water District        0.277465   -\n",
       "82                                     Fresno  City Of        0.273912   -\n",
       "147                                Pico Water District        0.270236   -\n",
       "195        Suburban Water Systems - Whittier/La Mirada        0.258443   -"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top_50_sup"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Top 50 demand changes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>water_supplier</th>\n",
       "      <th>demand_avg_chg</th>\n",
       "      <th>+/-</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>177</th>\n",
       "      <td>South Feather Water and Power</td>\n",
       "      <td>2.068829</td>\n",
       "      <td>+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>Daly City</td>\n",
       "      <td>0.593120</td>\n",
       "      <td>+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>Corona  City Of</td>\n",
       "      <td>0.392462</td>\n",
       "      <td>+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>Lincoln  City Of</td>\n",
       "      <td>0.352347</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>142</th>\n",
       "      <td>Placer County Water Agency</td>\n",
       "      <td>0.345214</td>\n",
       "      <td>+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113</th>\n",
       "      <td>Madera  City Of</td>\n",
       "      <td>0.325465</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>Groveland Community Services District</td>\n",
       "      <td>0.300606</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>Reedley  City Of</td>\n",
       "      <td>0.300026</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>Sanger  City Of</td>\n",
       "      <td>0.300022</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>Livingston  City Of</td>\n",
       "      <td>0.299992</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>Kerman  City Of</td>\n",
       "      <td>0.299942</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>Newman  City of</td>\n",
       "      <td>0.299701</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>Hi Desert Water District</td>\n",
       "      <td>0.276684</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>Pico Water District</td>\n",
       "      <td>0.270236</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>Paradise Irrigation District</td>\n",
       "      <td>0.266548</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Arcata  City Of</td>\n",
       "      <td>0.264837</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>Pomona  City Of</td>\n",
       "      <td>0.257593</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>East Bay Municipal Utility District</td>\n",
       "      <td>0.253524</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>San Gabriel Valley Water Company   Fontana Div...</td>\n",
       "      <td>0.252652</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Bakman Water Company</td>\n",
       "      <td>0.250141</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>Del Oro Water Company</td>\n",
       "      <td>0.250000</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Amador Water Agency</td>\n",
       "      <td>0.249999</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>Oakdale  City Of</td>\n",
       "      <td>0.249982</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Arvin Community Service District</td>\n",
       "      <td>0.249888</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td>Valley County Water District</td>\n",
       "      <td>0.239998</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Apple Valley Ranchos Water Company</td>\n",
       "      <td>0.235522</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>Fresno  City Of</td>\n",
       "      <td>0.224200</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>Huntington Beach  City Of</td>\n",
       "      <td>0.221268</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192</th>\n",
       "      <td>Valley Water Company</td>\n",
       "      <td>0.221239</td>\n",
       "      <td>+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>Elk Grove Water District</td>\n",
       "      <td>0.220038</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Banning  City Of</td>\n",
       "      <td>0.214384</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>Lincoln Avenue Water Company</td>\n",
       "      <td>0.213558</td>\n",
       "      <td>+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>Elsinore Valley Municipal Water District</td>\n",
       "      <td>0.212806</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alhambra  City Of</td>\n",
       "      <td>0.209993</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>San Bruno  City Of</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>Ontario  City Of</td>\n",
       "      <td>0.199994</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>Ripon  City Of</td>\n",
       "      <td>0.199946</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>Crescenta Valley Community Water District</td>\n",
       "      <td>0.199147</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202</th>\n",
       "      <td>Yucaipa Valley Water District</td>\n",
       "      <td>0.183676</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>Triunfo Sanitation District/Oak Park Water Ser...</td>\n",
       "      <td>0.182780</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>Porterville  City Of</td>\n",
       "      <td>0.169468</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>Folsom  City Of</td>\n",
       "      <td>0.168027</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>Lake Arrowhead Community Services District</td>\n",
       "      <td>0.163532</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Carlsbad Municipal Water District</td>\n",
       "      <td>0.161998</td>\n",
       "      <td>+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>Carmichael Water District</td>\n",
       "      <td>0.159969</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>196</th>\n",
       "      <td>West Kern Water District</td>\n",
       "      <td>0.159557</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Cambria Community Service District</td>\n",
       "      <td>0.150126</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>Manhattan Beach  City Of</td>\n",
       "      <td>0.137000</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>Oceanside  City Of</td>\n",
       "      <td>0.134740</td>\n",
       "      <td>+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Alameda County Water District</td>\n",
       "      <td>0.132667</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        water_supplier  demand_avg_chg +/-\n",
       "177                      South Feather Water and Power        2.068829   +\n",
       "61                                           Daly City        0.593120   +\n",
       "58                                     Corona  City Of        0.392462   +\n",
       "105                                   Lincoln  City Of        0.352347   -\n",
       "142                         Placer County Water Agency        0.345214   +\n",
       "113                                    Madera  City Of        0.325465   -\n",
       "89               Groveland Community Services District        0.300606   -\n",
       "149                                   Reedley  City Of        0.300026   -\n",
       "166                                    Sanger  City Of        0.300022   -\n",
       "107                                Livingston  City Of        0.299992   -\n",
       "98                                     Kerman  City Of        0.299942   -\n",
       "127                                    Newman  City of        0.299701   -\n",
       "93                            Hi Desert Water District        0.276684   -\n",
       "140                                Pico Water District        0.270236   -\n",
       "137                       Paradise Irrigation District        0.266548   -\n",
       "8                                      Arcata  City Of        0.264837   -\n",
       "144                                    Pomona  City Of        0.257593   -\n",
       "67                 East Bay Municipal Utility District        0.253524   -\n",
       "160  San Gabriel Valley Water Company   Fontana Div...        0.252652   -\n",
       "13                                Bakman Water Company        0.250141   -\n",
       "62                               Del Oro Water Company        0.250000   -\n",
       "3                                  Amador Water Agency        0.249999   -\n",
       "132                                   Oakdale  City Of        0.249982   -\n",
       "9                     Arvin Community Service District        0.249888   -\n",
       "191                       Valley County Water District        0.239998   -\n",
       "6                   Apple Valley Ranchos Water Company        0.235522   -\n",
       "80                                     Fresno  City Of        0.224200   -\n",
       "94                           Huntington Beach  City Of        0.221268   -\n",
       "192                               Valley Water Company        0.221239   +\n",
       "73                            Elk Grove Water District        0.220038   -\n",
       "14                                    Banning  City Of        0.214384   -\n",
       "106                       Lincoln Avenue Water Company        0.213558   +\n",
       "74            Elsinore Valley Municipal Water District        0.212806   -\n",
       "2                                    Alhambra  City Of        0.209993   -\n",
       "154                                 San Bruno  City Of        0.200000   -\n",
       "134                                   Ontario  City Of        0.199994   -\n",
       "150                                     Ripon  City Of        0.199946   -\n",
       "60           Crescenta Valley Community Water District        0.199147   -\n",
       "202                      Yucaipa Valley Water District        0.183676   -\n",
       "185  Triunfo Sanitation District/Oak Park Water Ser...        0.182780   -\n",
       "145                               Porterville  City Of        0.169468   -\n",
       "78                                     Folsom  City Of        0.168027   -\n",
       "102         Lake Arrowhead Community Services District        0.163532   -\n",
       "50                   Carlsbad Municipal Water District        0.161998   +\n",
       "51                           Carmichael Water District        0.159969   -\n",
       "196                           West Kern Water District        0.159557   -\n",
       "49                  Cambria Community Service District        0.150126   -\n",
       "114                           Manhattan Beach  City Of        0.137000   -\n",
       "133                                 Oceanside  City Of        0.134740   +\n",
       "1                        Alameda County Water District        0.132667   -"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "top_50_dem"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Utilities that appear in top 50 supplies & demands."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "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>water_supplier</th>\n",
       "      <th>supply_avg_chg</th>\n",
       "      <th>+/-</th>\n",
       "      <th>demand_avg_chg</th>\n",
       "      <th>+/-</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Hi Desert Water District</td>\n",
       "      <td>0.783197</td>\n",
       "      <td>-</td>\n",
       "      <td>0.276684</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>South Feather Water and Power</td>\n",
       "      <td>0.611071</td>\n",
       "      <td>+</td>\n",
       "      <td>2.068829</td>\n",
       "      <td>+</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Paradise Irrigation District</td>\n",
       "      <td>0.565220</td>\n",
       "      <td>-</td>\n",
       "      <td>0.266548</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Ripon  City Of</td>\n",
       "      <td>0.529960</td>\n",
       "      <td>-</td>\n",
       "      <td>0.199946</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Carmichael Water District</td>\n",
       "      <td>0.437586</td>\n",
       "      <td>-</td>\n",
       "      <td>0.159969</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Madera  City Of</td>\n",
       "      <td>0.325465</td>\n",
       "      <td>-</td>\n",
       "      <td>0.325465</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>West Kern Water District</td>\n",
       "      <td>0.321277</td>\n",
       "      <td>-</td>\n",
       "      <td>0.159557</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Triunfo Sanitation District/Oak Park Water Ser...</td>\n",
       "      <td>0.318807</td>\n",
       "      <td>-</td>\n",
       "      <td>0.182780</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>East Bay Municipal Utility District</td>\n",
       "      <td>0.312589</td>\n",
       "      <td>-</td>\n",
       "      <td>0.253524</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Groveland Community Services District</td>\n",
       "      <td>0.300606</td>\n",
       "      <td>-</td>\n",
       "      <td>0.300606</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Reedley  City Of</td>\n",
       "      <td>0.300026</td>\n",
       "      <td>-</td>\n",
       "      <td>0.300026</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Sanger  City Of</td>\n",
       "      <td>0.300022</td>\n",
       "      <td>-</td>\n",
       "      <td>0.300022</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Livingston  City Of</td>\n",
       "      <td>0.299992</td>\n",
       "      <td>-</td>\n",
       "      <td>0.299992</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Newman  City of</td>\n",
       "      <td>0.299892</td>\n",
       "      <td>-</td>\n",
       "      <td>0.299701</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Kerman  City Of</td>\n",
       "      <td>0.299887</td>\n",
       "      <td>-</td>\n",
       "      <td>0.299942</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Fresno  City Of</td>\n",
       "      <td>0.273912</td>\n",
       "      <td>-</td>\n",
       "      <td>0.224200</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Pico Water District</td>\n",
       "      <td>0.270236</td>\n",
       "      <td>-</td>\n",
       "      <td>0.270236</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       water_supplier  supply_avg_chg +/-  \\\n",
       "0                            Hi Desert Water District        0.783197   -   \n",
       "1                       South Feather Water and Power        0.611071   +   \n",
       "2                        Paradise Irrigation District        0.565220   -   \n",
       "3                                      Ripon  City Of        0.529960   -   \n",
       "4                           Carmichael Water District        0.437586   -   \n",
       "5                                     Madera  City Of        0.325465   -   \n",
       "6                            West Kern Water District        0.321277   -   \n",
       "7   Triunfo Sanitation District/Oak Park Water Ser...        0.318807   -   \n",
       "8                 East Bay Municipal Utility District        0.312589   -   \n",
       "9               Groveland Community Services District        0.300606   -   \n",
       "10                                   Reedley  City Of        0.300026   -   \n",
       "11                                    Sanger  City Of        0.300022   -   \n",
       "12                                Livingston  City Of        0.299992   -   \n",
       "13                                    Newman  City of        0.299892   -   \n",
       "14                                    Kerman  City Of        0.299887   -   \n",
       "15                                    Fresno  City Of        0.273912   -   \n",
       "16                                Pico Water District        0.270236   -   \n",
       "\n",
       "    demand_avg_chg +/-  \n",
       "0         0.276684   -  \n",
       "1         2.068829   +  \n",
       "2         0.266548   -  \n",
       "3         0.199946   -  \n",
       "4         0.159969   -  \n",
       "5         0.325465   -  \n",
       "6         0.159557   -  \n",
       "7         0.182780   -  \n",
       "8         0.253524   -  \n",
       "9         0.300606   -  \n",
       "10        0.300026   -  \n",
       "11        0.300022   -  \n",
       "12        0.299992   -  \n",
       "13        0.299701   -  \n",
       "14        0.299942   -  \n",
       "15        0.224200   -  \n",
       "16        0.270236   -  "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "both_top_50"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Bottom 50 supply minus demand for 2035."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>water_supplier</th>\n",
       "      <th>2020</th>\n",
       "      <th>2025</th>\n",
       "      <th>2030</th>\n",
       "      <th>2035</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>240</th>\n",
       "      <td>San Jose Water Company</td>\n",
       "      <td>-42884.638790</td>\n",
       "      <td>-22120.543700</td>\n",
       "      <td>-37041.476300</td>\n",
       "      <td>-54509.585900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>East Bay Municipal Utility District</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1120.144100</td>\n",
       "      <td>-13441.728800</td>\n",
       "      <td>-23523.025300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Beaumont - Cherry Valley Water District</td>\n",
       "      <td>-10517.000000</td>\n",
       "      <td>-11731.000000</td>\n",
       "      <td>-13451.000000</td>\n",
       "      <td>-14768.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>303</th>\n",
       "      <td>Yuba City</td>\n",
       "      <td>-2163.565940</td>\n",
       "      <td>-4980.804980</td>\n",
       "      <td>-8246.101660</td>\n",
       "      <td>-12033.109260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>Hayward  City Of</td>\n",
       "      <td>-8715.641500</td>\n",
       "      <td>-9482.863460</td>\n",
       "      <td>-10587.663090</td>\n",
       "      <td>-11140.062900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>239</th>\n",
       "      <td>San Jose  City Of</td>\n",
       "      <td>-3796.000000</td>\n",
       "      <td>843.000000</td>\n",
       "      <td>-3782.000000</td>\n",
       "      <td>-9633.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>California Water Service Company Los Altos/Sub...</td>\n",
       "      <td>-4318.000000</td>\n",
       "      <td>-2161.000000</td>\n",
       "      <td>-3532.000000</td>\n",
       "      <td>-5077.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>230</th>\n",
       "      <td>San Buenaventura  City Of (Ventura)</td>\n",
       "      <td>-2645.000000</td>\n",
       "      <td>-3203.000000</td>\n",
       "      <td>-3653.000000</td>\n",
       "      <td>-4117.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>California Water Service Company Mid Peninsula</td>\n",
       "      <td>-2436.000000</td>\n",
       "      <td>-2474.000000</td>\n",
       "      <td>-2620.000000</td>\n",
       "      <td>-2850.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>212</th>\n",
       "      <td>Redwood City</td>\n",
       "      <td>-1700.000000</td>\n",
       "      <td>-2011.000000</td>\n",
       "      <td>-2137.000000</td>\n",
       "      <td>-2334.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>California Water Service Company Bear Gulch</td>\n",
       "      <td>-2001.000000</td>\n",
       "      <td>-2031.000000</td>\n",
       "      <td>-2148.000000</td>\n",
       "      <td>-2327.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td>Otay Water District</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-2118.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>221</th>\n",
       "      <td>Roseville  City Of</td>\n",
       "      <td>4372.000000</td>\n",
       "      <td>2126.000000</td>\n",
       "      <td>852.000000</td>\n",
       "      <td>-1836.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193</th>\n",
       "      <td>Palmdale Water District</td>\n",
       "      <td>4780.000000</td>\n",
       "      <td>1540.000000</td>\n",
       "      <td>-830.000000</td>\n",
       "      <td>-1830.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Burlingame  City Of</td>\n",
       "      <td>-1144.695168</td>\n",
       "      <td>-1270.519570</td>\n",
       "      <td>-1420.895074</td>\n",
       "      <td>-1592.752794</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>283</th>\n",
       "      <td>Valley Center Municipal Water District</td>\n",
       "      <td>3564.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-352.000000</td>\n",
       "      <td>-1395.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>Menlo Park  City Of</td>\n",
       "      <td>-711.981981</td>\n",
       "      <td>-902.253028</td>\n",
       "      <td>-1101.730738</td>\n",
       "      <td>-1319.621775</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>California Water Service Company South San Fra...</td>\n",
       "      <td>-1208.000000</td>\n",
       "      <td>-1210.000000</td>\n",
       "      <td>-1222.000000</td>\n",
       "      <td>-1237.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>291</th>\n",
       "      <td>Vista Irrigation District</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-967.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165</th>\n",
       "      <td>Millbrae  City Of</td>\n",
       "      <td>-669.017551</td>\n",
       "      <td>-779.497514</td>\n",
       "      <td>-779.497514</td>\n",
       "      <td>-896.115252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255</th>\n",
       "      <td>Shasta Lake  City Of</td>\n",
       "      <td>-377.000000</td>\n",
       "      <td>-506.000000</td>\n",
       "      <td>-642.000000</td>\n",
       "      <td>-785.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>Lathrop  City Of</td>\n",
       "      <td>4594.000000</td>\n",
       "      <td>3083.000000</td>\n",
       "      <td>873.000000</td>\n",
       "      <td>-650.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164</th>\n",
       "      <td>Mid-Peninsula Water District</td>\n",
       "      <td>-521.710935</td>\n",
       "      <td>-561.606477</td>\n",
       "      <td>-586.157579</td>\n",
       "      <td>-629.122009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>207</th>\n",
       "      <td>Poway  City Of</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-496.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>Estero Municipal Improvement District</td>\n",
       "      <td>-224.028813</td>\n",
       "      <td>-239.373253</td>\n",
       "      <td>-322.233224</td>\n",
       "      <td>-389.748757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192</th>\n",
       "      <td>Oxnard  City Of</td>\n",
       "      <td>-2207.000000</td>\n",
       "      <td>2402.000000</td>\n",
       "      <td>1011.000000</td>\n",
       "      <td>-379.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>176</th>\n",
       "      <td>Mountain View  City Of</td>\n",
       "      <td>-306.000000</td>\n",
       "      <td>-263.000000</td>\n",
       "      <td>-294.000000</td>\n",
       "      <td>-336.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>Manteca  City Of</td>\n",
       "      <td>400.000000</td>\n",
       "      <td>4160.000000</td>\n",
       "      <td>1760.000000</td>\n",
       "      <td>-320.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>Golden State Water Company - Bay Point</td>\n",
       "      <td>-219.700000</td>\n",
       "      <td>-232.400000</td>\n",
       "      <td>-245.900000</td>\n",
       "      <td>-311.700000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>282</th>\n",
       "      <td>Vallejo  City Of</td>\n",
       "      <td>-2387.594750</td>\n",
       "      <td>-1718.577200</td>\n",
       "      <td>-580.019800</td>\n",
       "      <td>-153.444390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201</th>\n",
       "      <td>Pittsburg  City Of</td>\n",
       "      <td>320.000000</td>\n",
       "      <td>256.000000</td>\n",
       "      <td>188.000000</td>\n",
       "      <td>-129.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>East Palo Alto  City Of</td>\n",
       "      <td>512.000000</td>\n",
       "      <td>366.000000</td>\n",
       "      <td>185.000000</td>\n",
       "      <td>-83.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>274</th>\n",
       "      <td>Triunfo Sanitation District/Oak Park Water Ser...</td>\n",
       "      <td>-30.000000</td>\n",
       "      <td>-30.000000</td>\n",
       "      <td>-30.000000</td>\n",
       "      <td>-30.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>Golden State Water Company - San Dimas</td>\n",
       "      <td>-0.050000</td>\n",
       "      <td>-0.050000</td>\n",
       "      <td>-0.050000</td>\n",
       "      <td>-0.050000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Beverly Hills  City Of</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>234</th>\n",
       "      <td>San Francisco Public Utilities Commision</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>235</th>\n",
       "      <td>San Gabriel County Water District</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>236</th>\n",
       "      <td>San Gabriel Valley Water Company</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>237</th>\n",
       "      <td>San Gabriel Valley Water Company   Fontana Div...</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>238</th>\n",
       "      <td>San Jacinto  City Of</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>216</th>\n",
       "      <td>Ripon  City Of</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>Dublin San Ramon Services District</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241</th>\n",
       "      <td>San Juan Capistrano  City Of</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>Fullerton  City Of</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>242</th>\n",
       "      <td>San Juan Water District</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>200</th>\n",
       "      <td>Pico Water District</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>243</th>\n",
       "      <td>Sanger  City Of</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>203</th>\n",
       "      <td>Pleasanton  City Of</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>232</th>\n",
       "      <td>San Diego  City Of</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>Porterville  City Of</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        water_supplier          2020  \\\n",
       "240                             San Jose Water Company -42884.638790   \n",
       "80                 East Bay Municipal Utility District      0.000000   \n",
       "16             Beaumont - Cherry Valley Water District -10517.000000   \n",
       "303                                          Yuba City  -2163.565940   \n",
       "124                                   Hayward  City Of  -8715.641500   \n",
       "239                                  San Jose  City Of  -3796.000000   \n",
       "42   California Water Service Company Los Altos/Sub...  -4318.000000   \n",
       "230                San Buenaventura  City Of (Ventura)  -2645.000000   \n",
       "44      California Water Service Company Mid Peninsula  -2436.000000   \n",
       "212                                       Redwood City  -1700.000000   \n",
       "33         California Water Service Company Bear Gulch  -2001.000000   \n",
       "191                                Otay Water District      0.000000   \n",
       "221                                 Roseville  City Of   4372.000000   \n",
       "193                            Palmdale Water District   4780.000000   \n",
       "27                                 Burlingame  City Of  -1144.695168   \n",
       "283             Valley Center Municipal Water District   3564.000000   \n",
       "162                                Menlo Park  City Of   -711.981981   \n",
       "50   California Water Service Company South San Fra...  -1208.000000   \n",
       "291                          Vista Irrigation District      0.000000   \n",
       "165                                  Millbrae  City Of   -669.017551   \n",
       "255                               Shasta Lake  City Of   -377.000000   \n",
       "143                                   Lathrop  City Of   4594.000000   \n",
       "164                       Mid-Peninsula Water District   -521.710935   \n",
       "207                                     Poway  City Of      0.000000   \n",
       "91               Estero Municipal Improvement District   -224.028813   \n",
       "192                                    Oxnard  City Of  -2207.000000   \n",
       "176                             Mountain View  City Of   -306.000000   \n",
       "159                                   Manteca  City Of    400.000000   \n",
       "105             Golden State Water Company - Bay Point   -219.700000   \n",
       "282                                   Vallejo  City Of  -2387.594750   \n",
       "201                                 Pittsburg  City Of    320.000000   \n",
       "82                             East Palo Alto  City Of    512.000000   \n",
       "274  Triunfo Sanitation District/Oak Park Water Ser...    -30.000000   \n",
       "114             Golden State Water Company - San Dimas     -0.050000   \n",
       "20                              Beverly Hills  City Of      0.000000   \n",
       "234           San Francisco Public Utilities Commision      0.000000   \n",
       "235                  San Gabriel County Water District      0.000000   \n",
       "236                   San Gabriel Valley Water Company      0.000000   \n",
       "237  San Gabriel Valley Water Company   Fontana Div...      0.000000   \n",
       "238                               San Jacinto  City Of      0.000000   \n",
       "216                                     Ripon  City Of      0.000000   \n",
       "79                  Dublin San Ramon Services District      0.000000   \n",
       "241                       San Juan Capistrano  City Of      0.000000   \n",
       "98                                  Fullerton  City Of      0.000000   \n",
       "242                            San Juan Water District      0.000000   \n",
       "200                                Pico Water District      0.000000   \n",
       "243                                    Sanger  City Of      0.000000   \n",
       "203                                Pleasanton  City Of      0.000000   \n",
       "232                                 San Diego  City Of      0.000000   \n",
       "206                               Porterville  City Of      0.000000   \n",
       "\n",
       "             2025          2030          2035  \n",
       "240 -22120.543700 -37041.476300 -54509.585900  \n",
       "80   -1120.144100 -13441.728800 -23523.025300  \n",
       "16  -11731.000000 -13451.000000 -14768.000000  \n",
       "303  -4980.804980  -8246.101660 -12033.109260  \n",
       "124  -9482.863460 -10587.663090 -11140.062900  \n",
       "239    843.000000  -3782.000000  -9633.000000  \n",
       "42   -2161.000000  -3532.000000  -5077.000000  \n",
       "230  -3203.000000  -3653.000000  -4117.000000  \n",
       "44   -2474.000000  -2620.000000  -2850.000000  \n",
       "212  -2011.000000  -2137.000000  -2334.000000  \n",
       "33   -2031.000000  -2148.000000  -2327.000000  \n",
       "191      0.000000      0.000000  -2118.000000  \n",
       "221   2126.000000    852.000000  -1836.000000  \n",
       "193   1540.000000   -830.000000  -1830.000000  \n",
       "27   -1270.519570  -1420.895074  -1592.752794  \n",
       "283      0.000000   -352.000000  -1395.000000  \n",
       "162   -902.253028  -1101.730738  -1319.621775  \n",
       "50   -1210.000000  -1222.000000  -1237.000000  \n",
       "291      0.000000      0.000000   -967.000000  \n",
       "165   -779.497514   -779.497514   -896.115252  \n",
       "255   -506.000000   -642.000000   -785.000000  \n",
       "143   3083.000000    873.000000   -650.000000  \n",
       "164   -561.606477   -586.157579   -629.122009  \n",
       "207      0.000000      0.000000   -496.000000  \n",
       "91    -239.373253   -322.233224   -389.748757  \n",
       "192   2402.000000   1011.000000   -379.000000  \n",
       "176   -263.000000   -294.000000   -336.000000  \n",
       "159   4160.000000   1760.000000   -320.000000  \n",
       "105   -232.400000   -245.900000   -311.700000  \n",
       "282  -1718.577200   -580.019800   -153.444390  \n",
       "201    256.000000    188.000000   -129.000000  \n",
       "82     366.000000    185.000000    -83.000000  \n",
       "274    -30.000000    -30.000000    -30.000000  \n",
       "114     -0.050000     -0.050000     -0.050000  \n",
       "20       0.000000      0.000000      0.000000  \n",
       "234      0.000000      0.000000      0.000000  \n",
       "235      0.000000      0.000000      0.000000  \n",
       "236      0.000000      0.000000      0.000000  \n",
       "237      0.000000      0.000000      0.000000  \n",
       "238      0.000000      0.000000      0.000000  \n",
       "216      0.000000      0.000000      0.000000  \n",
       "79       0.000000      0.000000      0.000000  \n",
       "241      0.000000      0.000000      0.000000  \n",
       "98       0.000000      0.000000      0.000000  \n",
       "242      0.000000      0.000000      0.000000  \n",
       "200      0.000000      0.000000      0.000000  \n",
       "243      0.000000      0.000000      0.000000  \n",
       "203      0.000000      0.000000      0.000000  \n",
       "232      0.000000      0.000000      0.000000  \n",
       "206      0.000000      0.000000      0.000000  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bot_50_sup_dem"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Utilities that appear in top 50 supplies & demands as well as bottom 50 supply - demand."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>water_supplier</th>\n",
       "      <th>supply_avg_chg</th>\n",
       "      <th>+/-</th>\n",
       "      <th>demand_avg_chg</th>\n",
       "      <th>+/-</th>\n",
       "      <th>2020</th>\n",
       "      <th>2025</th>\n",
       "      <th>2030</th>\n",
       "      <th>2035</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>East Bay Municipal Utility District</td>\n",
       "      <td>0.312589</td>\n",
       "      <td>-</td>\n",
       "      <td>0.253524</td>\n",
       "      <td>-</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-1120.1441</td>\n",
       "      <td>-13441.7288</td>\n",
       "      <td>-23523.0253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Triunfo Sanitation District/Oak Park Water Ser...</td>\n",
       "      <td>0.318807</td>\n",
       "      <td>-</td>\n",
       "      <td>0.182780</td>\n",
       "      <td>-</td>\n",
       "      <td>-30.0</td>\n",
       "      <td>-30.0000</td>\n",
       "      <td>-30.0000</td>\n",
       "      <td>-30.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ripon  City Of</td>\n",
       "      <td>0.529960</td>\n",
       "      <td>-</td>\n",
       "      <td>0.199946</td>\n",
       "      <td>-</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Pico Water District</td>\n",
       "      <td>0.270236</td>\n",
       "      <td>-</td>\n",
       "      <td>0.270236</td>\n",
       "      <td>-</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Sanger  City Of</td>\n",
       "      <td>0.300022</td>\n",
       "      <td>-</td>\n",
       "      <td>0.300022</td>\n",
       "      <td>-</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      water_supplier  supply_avg_chg +/-  \\\n",
       "0                East Bay Municipal Utility District        0.312589   -   \n",
       "1  Triunfo Sanitation District/Oak Park Water Ser...        0.318807   -   \n",
       "2                                     Ripon  City Of        0.529960   -   \n",
       "3                                Pico Water District        0.270236   -   \n",
       "4                                    Sanger  City Of        0.300022   -   \n",
       "\n",
       "   demand_avg_chg +/-  2020       2025        2030        2035  \n",
       "0        0.253524   -   0.0 -1120.1441 -13441.7288 -23523.0253  \n",
       "1        0.182780   - -30.0   -30.0000    -30.0000    -30.0000  \n",
       "2        0.199946   -   0.0     0.0000      0.0000      0.0000  \n",
       "3        0.270236   -   0.0     0.0000      0.0000      0.0000  \n",
       "4        0.300022   -   0.0     0.0000      0.0000      0.0000  "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_top_50"
   ]
  }
 ],
 "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}