1-creating-table-schema.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Example for cleaning an existing dataset and creating a table schema\n",
    "\n",
    "- Inspecting fields\n",
    "- Setting consistent column names\n",
    "- Setting correct data types\n",
    "- Writing the table schema\n",
    "- Creating the data package/directory structure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data inspecting/cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 34510 entries, 0 to 34509\n",
      "Data columns (total 18 columns):\n",
      "STATION_ID            34510 non-null int64\n",
      "STATION_NAME          34510 non-null object\n",
      "FULL_STATION_NAME     34510 non-null object\n",
      "STATION_NUMBER        34510 non-null object\n",
      "STATION_TYPE          34510 non-null object\n",
      "LATITUDE              32614 non-null float64\n",
      "LONGITUDE             32614 non-null float64\n",
      "COUNTY_NAME           34510 non-null object\n",
      "SAMPLE_CODE           34510 non-null object\n",
      "SAMPLE_DATE           34510 non-null object\n",
      "SAMPLE_DEPTH          34311 non-null float64\n",
      "SAMPLE_DEPTH_UNITS    34510 non-null object\n",
      "PARAMETER             34510 non-null object\n",
      "RESULT                33481 non-null float64\n",
      "REPORTING_LIMIT       34509 non-null float64\n",
      "UNITS                 34510 non-null object\n",
      "METHOD_NAME           34510 non-null object\n",
      "sampleYear            34510 non-null int64\n",
      "dtypes: float64(5), int64(2), object(11)\n",
      "memory usage: 4.7+ MB\n"
     ]
    }
   ],
   "source": [
    "DATA = pd.read_csv('PMCLs_00-18.csv')\n",
    "DATA.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Column/field names"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "From `info()` we see that the column names are in good shape. \n",
    "\n",
    "The only things to do are:\n",
    "- Convert to lowercase\n",
    "- Use consistent casing\n",
    "\n",
    "This can be done either programmatically or manually, or with a mix of the two."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "COLUMN_NAMES_MANUAL = {\n",
    "    'FULL_STATION_NAME': 'station_name_full',\n",
    "    'sampleYear': 'sample_year',\n",
    "}\n",
    "\n",
    "def get_fixed_name(col):\n",
    "    # if the input column is not in the dictionary representing manual assignment, simply use str.lower()\n",
    "    return COLUMN_NAMES_MANUAL.get(col, col.lower())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['station_id', 'station_name', 'station_name_full', 'station_number',\n",
       "       'station_type', 'latitude', 'longitude', 'county_name', 'sample_code',\n",
       "       'sample_date', 'sample_depth', 'sample_depth_units', 'parameter',\n",
       "       'result', 'reporting_limit', 'units', 'method_name', 'sample_year'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DATA = DATA.rename(columns=get_fixed_name)\n",
    "DATA.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Categorical fields\n",
    "\n",
    "- Categorical fields are fields that can have only a limited set of values\n",
    "    - Equivalent to \"enum\" types in most programming languages\n",
    "- They are expressed in pandas with the `Categorical` dtype\n",
    "- If categorical dtypes are set on a DataFrame column, the corresponding field will have a `constraints.enum` property set wit the correct values when generating the table schema from the DataFrame object\n",
    "\n",
    "The decision whether a field should be interpreted as categorical or not is sometimes not easy. Inspecting the number of unique values present in the data is a good first criterion:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "station_id             1101\n",
       "station_name           1099\n",
       "station_name_full      1092\n",
       "station_number         1101\n",
       "station_type              3\n",
       "latitude                848\n",
       "longitude               726\n",
       "county_name              37\n",
       "sample_code           15748\n",
       "sample_date           13895\n",
       "sample_depth            119\n",
       "sample_depth_units        2\n",
       "parameter                 5\n",
       "result                 2566\n",
       "reporting_limit         113\n",
       "units                     3\n",
       "method_name               8\n",
       "sample_year              19\n",
       "dtype: int64"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DATA.apply(lambda s: s.nunique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "In this case, `station_type`, `sample_depth_units`, `parameter`, `units` and `method_name` all have below 10 values, so it makes sense to consider them as categorical. \n",
    "`sample_year` has low number of values, but it doesn't really represent a category.\n",
    "It could be argued that `county_name` should be categorical, but since we know that there are other counties not present in this dataset, we can leave it as it is.\n",
    "\n",
    "We can inspect the individual values for the categorical candidates to confirm:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Surface Water    28975\n",
       "Groundwater       3608\n",
       "Other             1927\n",
       "Name: station_type, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Meters    32814\n",
       "Feet       1696\n",
       "Name: sample_depth_units, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Dissolved Nitrate    10264\n",
       "Total Arsenic         7747\n",
       "Total Lead            7423\n",
       "Total Selenium        7237\n",
       "Total Mercury         1839\n",
       "Name: parameter, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "ug/L    18232\n",
       "mg/L    14575\n",
       "ng/L     1703\n",
       "Name: units, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "EPA 1638 (T)            18232\n",
       "EPA 300.0 28d Hold       9089\n",
       "EPA 200.8 (T)            4156\n",
       "EPA 1631 E (T)           1703\n",
       "EPA 300.0 48 hr          1175\n",
       "EPA 245.1(T)              135\n",
       "EPA 200.8 (D)              19\n",
       "EPA 200.8 (Hg Total)        1\n",
       "Name: method_name, dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "def inspect_values(df, cols):\n",
    "    separator = '-' * 10\n",
    "    for col in cols:\n",
    "        print(separator)\n",
    "        display(df[col].value_counts())\n",
    "\n",
    "\n",
    "DATA.pipe(inspect_values, ['station_type', 'sample_depth_units', 'parameter', 'units', 'method_name'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 34510 entries, 0 to 34509\n",
      "Data columns (total 18 columns):\n",
      "station_id            34510 non-null int64\n",
      "station_name          34510 non-null object\n",
      "station_name_full     34510 non-null object\n",
      "station_number        34510 non-null object\n",
      "station_type          34510 non-null category\n",
      "latitude              32614 non-null float64\n",
      "longitude             32614 non-null float64\n",
      "county_name           34510 non-null object\n",
      "sample_code           34510 non-null object\n",
      "sample_date           34510 non-null object\n",
      "sample_depth          34311 non-null float64\n",
      "sample_depth_units    34510 non-null category\n",
      "parameter             34510 non-null category\n",
      "result                33481 non-null float64\n",
      "reporting_limit       34509 non-null float64\n",
      "units                 34510 non-null category\n",
      "method_name           34510 non-null category\n",
      "sample_year           34510 non-null int64\n",
      "dtypes: category(5), float64(5), int64(2), object(6)\n",
      "memory usage: 3.6+ MB\n"
     ]
    }
   ],
   "source": [
    "def set_as_categorical(df, cols):\n",
    "    return df.astype({col: 'category' for col in cols})\n",
    "\n",
    "DATA = DATA.pipe(set_as_categorical, ['station_type', 'sample_depth_units', 'parameter', 'units', 'method_name'])\n",
    "DATA.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Datetime fields\n",
    "\n",
    "As with categorical data, using the native pandas datetime dtypes is not strictly necessary for the purpose of building the table schema, but helps with validation (making sure that the output format is consistent), and we'll have the correct type in the table schema field.\n",
    "\n",
    "The `to_datetime()` function in the main pandas module should be robust enough to convert automatically from many input formats."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 34510 entries, 0 to 34509\n",
      "Data columns (total 18 columns):\n",
      "station_id            34510 non-null int64\n",
      "station_name          34510 non-null object\n",
      "station_name_full     34510 non-null object\n",
      "station_number        34510 non-null object\n",
      "station_type          34510 non-null category\n",
      "latitude              32614 non-null float64\n",
      "longitude             32614 non-null float64\n",
      "county_name           34510 non-null object\n",
      "sample_code           34510 non-null object\n",
      "sample_date           34510 non-null datetime64[ns]\n",
      "sample_depth          34311 non-null float64\n",
      "sample_depth_units    34510 non-null category\n",
      "parameter             34510 non-null category\n",
      "result                33481 non-null float64\n",
      "reporting_limit       34509 non-null float64\n",
      "units                 34510 non-null category\n",
      "method_name           34510 non-null category\n",
      "sample_year           34510 non-null int64\n",
      "dtypes: category(5), datetime64[ns](1), float64(5), int64(2), object(5)\n",
      "memory usage: 3.6+ MB\n"
     ]
    }
   ],
   "source": [
    "DATA = (DATA\n",
    "        .assign(sample_date=lambda d: pd.to_datetime(d.sample_date))\n",
    ")\n",
    "# this should give the same result as\n",
    "# DATA['sample_year'] = pd.to_datetime(DATA['sample_year'])\n",
    "# but is somewhat more robust\n",
    "DATA.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We see that the `sample_year` column matches exactly the year in `sample_date`. In principle, then, `sample_year` is redundant, and could be dropped."
   ]
  },
  {
   "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>station_id</th>\n",
       "      <th>station_name</th>\n",
       "      <th>station_name_full</th>\n",
       "      <th>station_number</th>\n",
       "      <th>station_type</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>county_name</th>\n",
       "      <th>sample_code</th>\n",
       "      <th>sample_date</th>\n",
       "      <th>sample_depth</th>\n",
       "      <th>sample_depth_units</th>\n",
       "      <th>parameter</th>\n",
       "      <th>result</th>\n",
       "      <th>reporting_limit</th>\n",
       "      <th>units</th>\n",
       "      <th>method_name</th>\n",
       "      <th>sample_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [station_id, station_name, station_name_full, station_number, station_type, latitude, longitude, county_name, sample_code, sample_date, sample_depth, sample_depth_units, parameter, result, reporting_limit, units, method_name, sample_year]\n",
       "Index: []"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "DATA[lambda d: d.sample_date.dt.year != d.sample_year]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Table schema\n",
    "\n",
    "## Creating table schema from the DataFrame\n",
    "\n",
    "The `build_table_schema()` function allows to get a \n",
    "\n",
    "Few things to note:\n",
    "- The output of `build_table_schema()` is a Python object (dictionary)\n",
    "    - It can be modified (either manually or programmatically) from within the notebook\n",
    "- It must be converted to a string (for copy-pasting) or saved to a file to be used with other applications\n",
    "    - Use the `json` module of the standard library for this\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'{\"fields\": [{\"name\": \"station_id\", \"type\": \"integer\"}, {\"name\": \"station_name\", \"type\": \"string\"}, {\"name\": \"station_name_full\", \"type\": \"string\"}, {\"name\": \"station_number\", \"type\": \"string\"}, {\"name\": \"station_type\", \"type\": \"any\", \"constraints\": {\"enum\": [\"Groundwater\", \"Other\", \"Surface Water\"]}, \"ordered\": false}, {\"name\": \"latitude\", \"type\": \"number\"}, {\"name\": \"longitude\", \"type\": \"number\"}, {\"name\": \"county_name\", \"type\": \"string\"}, {\"name\": \"sample_code\", \"type\": \"string\"}, {\"name\": \"sample_date\", \"type\": \"datetime\"}, {\"name\": \"sample_depth\", \"type\": \"number\"}, {\"name\": \"sample_depth_units\", \"type\": \"any\", \"constraints\": {\"enum\": [\"Feet\", \"Meters\"]}, \"ordered\": false}, {\"name\": \"parameter\", \"type\": \"any\", \"constraints\": {\"enum\": [\"Dissolved Nitrate\", \"Total Arsenic\", \"Total Lead\", \"Total Mercury\", \"Total Selenium\"]}, \"ordered\": false}, {\"name\": \"result\", \"type\": \"number\"}, {\"name\": \"reporting_limit\", \"type\": \"number\"}, {\"name\": \"units\", \"type\": \"any\", \"constraints\": {\"enum\": [\"mg/L\", \"ng/L\", \"ug/L\"]}, \"ordered\": false}, {\"name\": \"method_name\", \"type\": \"any\", \"constraints\": {\"enum\": [\"EPA 1631 E (T)\", \"EPA 1638 (T)\", \"EPA 200.8 (D)\", \"EPA 200.8 (Hg Total)\", \"EPA 200.8 (T)\", \"EPA 245.1(T)\", \"EPA 300.0 28d Hold\", \"EPA 300.0 48 hr\"]}, \"ordered\": false}, {\"name\": \"sample_year\", \"type\": \"integer\"}]}'"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pandas.io.json import build_table_schema\n",
    "import json\n",
    "\n",
    "def get_table_schema(df):\n",
    "    return build_table_schema(df, index=False, version=False)\n",
    "\n",
    "json.dumps(DATA.pipe(get_table_schema))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Editing the table schema\n",
    "\n",
    "Although this method works reasonably well, some parts of the table schema cannot be inferred from the existing data and must be filed in manually.  \n",
    "The most relevant properties are:\n",
    "- `title`: the human-readable counterpart to `name` (what one would e.g. use for an axis label in a figure, or on the header of a table in a published format)\n",
    "- `description`: the data dictionary entry for the field. It can contain both data knowledge (e.g. additional properties of the data), and, most importantly, domain knowledge, in the form of metadata, specifications, sources, and so on\n",
    "\n",
    "Unlike `name`, these metadata fields of the table schema are not mandatory, but having them filled in the schema, since it allows to concentrate the information in one place (instead of e.g. maintaining a separate PDF file for the data dictionary).\n",
    "\n",
    "JSON files are somewhat annoying to edit directly. A few options to make things easier:\n",
    "- Using a code editor with good JSON support, such as VSCode\n",
    "- Using a dedicated JSON editor (online or offline) such as https://jsoneditoronline.org/\n",
    "\n",
    "Once the schema is filled in, it can be incorporated within the `datapackage.json` file."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----\n",
    "# Bonus: Normalizing tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>station_name</th>\n",
       "      <th>station_name_full</th>\n",
       "      <th>station_number</th>\n",
       "      <th>station_type</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>county_name</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>station_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AMERICAN</td>\n",
       "      <td>American River at Water Treatment Plant</td>\n",
       "      <td>A0714010</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>38.5596</td>\n",
       "      <td>-121.417</td>\n",
       "      <td>Sacramento</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>CLIFTON</td>\n",
       "      <td>Clifton Court Intake</td>\n",
       "      <td>KA000000</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>37.8298</td>\n",
       "      <td>-121.557</td>\n",
       "      <td>Contra Costa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>H.O. Banks Headworks</td>\n",
       "      <td>Delta P.P. Headworks at H.O. Banks PP</td>\n",
       "      <td>KA000331</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>37.8019</td>\n",
       "      <td>-121.620</td>\n",
       "      <td>Alameda</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>COLUSA</td>\n",
       "      <td>Ag Drain on Colusa Basin Main Drain</td>\n",
       "      <td>A0294500</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>38.7993</td>\n",
       "      <td>-121.725</td>\n",
       "      <td>Yolo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>TWITCHELLPP01</td>\n",
       "      <td>Ag Drain on Twitchell Isl., PP. No. 1</td>\n",
       "      <td>B9V80661391</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>38.0966</td>\n",
       "      <td>-121.651</td>\n",
       "      <td>Sacramento</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>CHECK 13</td>\n",
       "      <td>CA Aqueduct, Ck 13, O'Neill Outlet</td>\n",
       "      <td>KA007089</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>37.0742</td>\n",
       "      <td>-121.015</td>\n",
       "      <td>Merced</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>Del Valle Reservoir</td>\n",
       "      <td>Lake Del Valle at Glory Hole</td>\n",
       "      <td>DV001000</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>37.6147</td>\n",
       "      <td>-121.746</td>\n",
       "      <td>Alameda</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>OR nr Byron Hwy 4 Br</td>\n",
       "      <td>Old R. nr. Byron (St 9) (NEAR HWY 4 BRIDGE)</td>\n",
       "      <td>B9D75351342</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>37.8910</td>\n",
       "      <td>-121.570</td>\n",
       "      <td>Contra Costa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114</th>\n",
       "      <td>SACWSACINT</td>\n",
       "      <td>Sacramento River at W. Sac Intake Structure</td>\n",
       "      <td>A0210451</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>38.5980</td>\n",
       "      <td>-121.549</td>\n",
       "      <td>Yolo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>122</th>\n",
       "      <td>BaconAgDrain1</td>\n",
       "      <td>Ag Drain on Bacon Island, PP. No. 1</td>\n",
       "      <td>B9V75881342</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>37.9794</td>\n",
       "      <td>-121.570</td>\n",
       "      <td>San Joaquin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>127</th>\n",
       "      <td>CONCOSPP1</td>\n",
       "      <td>Contra Costa PP Number 01</td>\n",
       "      <td>B9591000</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>37.9788</td>\n",
       "      <td>-121.702</td>\n",
       "      <td>Contra Costa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>STATENPP02</td>\n",
       "      <td>Ag Drain on Staten Island PP. No. 2</td>\n",
       "      <td>B9V80751335</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>38.1252</td>\n",
       "      <td>-121.559</td>\n",
       "      <td>San Joaquin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>PROSL@LHOLTR</td>\n",
       "      <td>Prospect Slough @ S. Tip of Little Holland Tract</td>\n",
       "      <td>B9S81701398</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>38.2835</td>\n",
       "      <td>-121.664</td>\n",
       "      <td>Solano</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>145</th>\n",
       "      <td>Toe Drain YB LISBON</td>\n",
       "      <td>Yolo Bypass Toe Drain Below Lisbon Weir</td>\n",
       "      <td>B9D82851352</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>38.4749</td>\n",
       "      <td>-121.588</td>\n",
       "      <td>Yolo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>OR@Bacon Is</td>\n",
       "      <td>Old River at Bacon Island</td>\n",
       "      <td>B9D75811344</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>37.9694</td>\n",
       "      <td>-121.572</td>\n",
       "      <td>San Joaquin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>SJOAQHWY4</td>\n",
       "      <td>San Joaquin R. @ Hwy 4</td>\n",
       "      <td>B9D75571196</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>37.9283</td>\n",
       "      <td>-121.328</td>\n",
       "      <td>San Joaquin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189</th>\n",
       "      <td>MOKSFSTATEN</td>\n",
       "      <td>Mokelumne River S.F. at Staten Island</td>\n",
       "      <td>B9D80701299</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>38.1171</td>\n",
       "      <td>-121.499</td>\n",
       "      <td>San Joaquin</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192</th>\n",
       "      <td>LYTLE CRK NR SB</td>\n",
       "      <td>Lytle Creek near San Bernardino</td>\n",
       "      <td>Y3165000</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>34.2309</td>\n",
       "      <td>-117.483</td>\n",
       "      <td>San Bernardino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>218</th>\n",
       "      <td>Castaic Tower Outlet</td>\n",
       "      <td>Castaic Lake Outlet Tower</td>\n",
       "      <td>CA002000</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>34.5152</td>\n",
       "      <td>-118.610</td>\n",
       "      <td>Los Angeles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>234</th>\n",
       "      <td>DKA13481</td>\n",
       "      <td>DRAINAGE INLET AT MILE POST 134.81, CANTUA CREEK</td>\n",
       "      <td>DKA13481</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>36.4298</td>\n",
       "      <td>-120.296</td>\n",
       "      <td>Fresno</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>259</th>\n",
       "      <td>Del Valle COW</td>\n",
       "      <td>Del Valle Conserv. Outlet Works</td>\n",
       "      <td>DV000000</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>37.6183</td>\n",
       "      <td>-121.749</td>\n",
       "      <td>Alameda</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>266</th>\n",
       "      <td>Frenchman Lk</td>\n",
       "      <td>FRENCHMAN LK NR DM</td>\n",
       "      <td>FR001000</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>39.8944</td>\n",
       "      <td>-120.192</td>\n",
       "      <td>Plumas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>275</th>\n",
       "      <td>GKA26966</td>\n",
       "      <td>NON-PROJECT IMPORT STATION (WHEELER RIDGE)</td>\n",
       "      <td>GKA26966</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>35.0684</td>\n",
       "      <td>-119.141</td>\n",
       "      <td>Kern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>277</th>\n",
       "      <td>GKA27210</td>\n",
       "      <td>NON-PROJECT IMPORT STATION (WHEELER RIDGE)</td>\n",
       "      <td>GKA27210</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>35.0608</td>\n",
       "      <td>-119.099</td>\n",
       "      <td>Kern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>278</th>\n",
       "      <td>GKA27231</td>\n",
       "      <td>NON-PROJECT IMPORT STATION (WHEELER RIDGE)</td>\n",
       "      <td>GKA27231</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>35.0591</td>\n",
       "      <td>-119.095</td>\n",
       "      <td>Kern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>279</th>\n",
       "      <td>GKA27253</td>\n",
       "      <td>NON-PROJECT IMPORT STATION (WHEELER RIDGE)</td>\n",
       "      <td>GKA27253</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>35.0581</td>\n",
       "      <td>-119.091</td>\n",
       "      <td>Kern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>280</th>\n",
       "      <td>GKA27280</td>\n",
       "      <td>NON-PROJECT IMPORT STATION (WHEELER RIDGE)</td>\n",
       "      <td>GKA27280</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>35.0568</td>\n",
       "      <td>-119.086</td>\n",
       "      <td>Kern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>282</th>\n",
       "      <td>GKA27375</td>\n",
       "      <td>NON-PROJECT IMPORT STATION (WHEELER RIDGE)</td>\n",
       "      <td>GKA27375</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>35.0480</td>\n",
       "      <td>-119.074</td>\n",
       "      <td>Kern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>283</th>\n",
       "      <td>GKA27728</td>\n",
       "      <td>NON-PROJECT IMPORT STATION (WHEELER RIDGE)</td>\n",
       "      <td>GKA27728</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>35.0377</td>\n",
       "      <td>-119.021</td>\n",
       "      <td>Kern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>297</th>\n",
       "      <td>Check 11 1/2</td>\n",
       "      <td>CALIFORNIA AQUEDUCT NEAR CHECK 12</td>\n",
       "      <td>KA006633</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>37.1183</td>\n",
       "      <td>-121.057</td>\n",
       "      <td>Merced</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47161</th>\n",
       "      <td>SutterCo_MW-1D</td>\n",
       "      <td>14N02E17C004M</td>\n",
       "      <td>14N02E17C004M</td>\n",
       "      <td>Groundwater</td>\n",
       "      <td>39.0696</td>\n",
       "      <td>-121.778</td>\n",
       "      <td>Sutter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47162</th>\n",
       "      <td>AV-3A</td>\n",
       "      <td>14N14W34R005M</td>\n",
       "      <td>14N14W34R005M</td>\n",
       "      <td>Groundwater</td>\n",
       "      <td>39.0254</td>\n",
       "      <td>-123.382</td>\n",
       "      <td>Mendocino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47163</th>\n",
       "      <td>AV-3B</td>\n",
       "      <td>14N14W34R006M</td>\n",
       "      <td>14N14W34R006M</td>\n",
       "      <td>Groundwater</td>\n",
       "      <td>39.0254</td>\n",
       "      <td>-123.382</td>\n",
       "      <td>Mendocino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47164</th>\n",
       "      <td>AV-3C</td>\n",
       "      <td>14N14W34R007M</td>\n",
       "      <td>14N14W34R007M</td>\n",
       "      <td>Groundwater</td>\n",
       "      <td>39.0254</td>\n",
       "      <td>-123.382</td>\n",
       "      <td>Mendocino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47168</th>\n",
       "      <td>22N01W29N001M</td>\n",
       "      <td>22N01W29N001M</td>\n",
       "      <td>22N01W29N001M</td>\n",
       "      <td>Groundwater</td>\n",
       "      <td>39.7264</td>\n",
       "      <td>-122.010</td>\n",
       "      <td>Glenn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47169</th>\n",
       "      <td>22N01W29N002M</td>\n",
       "      <td>22N01W29N002M</td>\n",
       "      <td>22N01W29N002M</td>\n",
       "      <td>Groundwater</td>\n",
       "      <td>39.7264</td>\n",
       "      <td>-122.010</td>\n",
       "      <td>Glenn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47170</th>\n",
       "      <td>22N01W29N003M</td>\n",
       "      <td>22N01W29N003M</td>\n",
       "      <td>22N01W29N003M</td>\n",
       "      <td>Groundwater</td>\n",
       "      <td>39.7264</td>\n",
       "      <td>-122.010</td>\n",
       "      <td>Glenn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47171</th>\n",
       "      <td>22N01W29N004M</td>\n",
       "      <td>22N01W29N004M</td>\n",
       "      <td>22N01W29N004M</td>\n",
       "      <td>Groundwater</td>\n",
       "      <td>39.7264</td>\n",
       "      <td>-122.010</td>\n",
       "      <td>Glenn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47172</th>\n",
       "      <td>21N01W31H001M</td>\n",
       "      <td>21N01W31H001M</td>\n",
       "      <td>21N01W31H001M</td>\n",
       "      <td>Groundwater</td>\n",
       "      <td>39.6830</td>\n",
       "      <td>-122.011</td>\n",
       "      <td>Glenn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47175</th>\n",
       "      <td>Clear C NR MO NR Red</td>\n",
       "      <td>Clear Creek near Mouth near Redding</td>\n",
       "      <td>A3601000</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>40.5092</td>\n",
       "      <td>-122.380</td>\n",
       "      <td>Shasta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47176</th>\n",
       "      <td>Paynes C A P Xing</td>\n",
       "      <td>Paynes C at Paynes Crossing</td>\n",
       "      <td>A0460200</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>40.2758</td>\n",
       "      <td>-122.181</td>\n",
       "      <td>Tehama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47195</th>\n",
       "      <td>29N04W03R002M</td>\n",
       "      <td>29N04W03R002M</td>\n",
       "      <td>29N04W03R002M</td>\n",
       "      <td>Groundwater</td>\n",
       "      <td>40.3928</td>\n",
       "      <td>-122.294</td>\n",
       "      <td>Shasta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47196</th>\n",
       "      <td>29N04W03R003M</td>\n",
       "      <td>29N04W03R003M</td>\n",
       "      <td>29N04W03R003M</td>\n",
       "      <td>Groundwater</td>\n",
       "      <td>40.3928</td>\n",
       "      <td>-122.294</td>\n",
       "      <td>Shasta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47197</th>\n",
       "      <td>29N04W03R004M</td>\n",
       "      <td>29N04W03R004M</td>\n",
       "      <td>29N04W03R004M</td>\n",
       "      <td>Groundwater</td>\n",
       "      <td>40.3928</td>\n",
       "      <td>-122.294</td>\n",
       "      <td>Shasta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47198</th>\n",
       "      <td>29N04W03R005M</td>\n",
       "      <td>29N04W03R005M</td>\n",
       "      <td>29N04W03R005M</td>\n",
       "      <td>Groundwater</td>\n",
       "      <td>40.3928</td>\n",
       "      <td>-122.294</td>\n",
       "      <td>Shasta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47199</th>\n",
       "      <td>29N04W03R006M</td>\n",
       "      <td>29N04W03R006M</td>\n",
       "      <td>29N04W03R006M</td>\n",
       "      <td>Groundwater</td>\n",
       "      <td>40.3928</td>\n",
       "      <td>-122.294</td>\n",
       "      <td>Shasta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47570</th>\n",
       "      <td>AB-4 deep</td>\n",
       "      <td>10N04E31M001M</td>\n",
       "      <td>10N04E31M001M</td>\n",
       "      <td>Groundwater</td>\n",
       "      <td>38.6669</td>\n",
       "      <td>-121.584</td>\n",
       "      <td>Sacramento</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47571</th>\n",
       "      <td>AB-4 intermediate-de</td>\n",
       "      <td>10N04E31M002M</td>\n",
       "      <td>10N04E31M002M</td>\n",
       "      <td>Groundwater</td>\n",
       "      <td>38.6669</td>\n",
       "      <td>-121.584</td>\n",
       "      <td>Sacramento</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47572</th>\n",
       "      <td>AB-4 intermediate-sh</td>\n",
       "      <td>10N04E31M003M</td>\n",
       "      <td>10N04E31M003M</td>\n",
       "      <td>Groundwater</td>\n",
       "      <td>38.6669</td>\n",
       "      <td>-121.584</td>\n",
       "      <td>Sacramento</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47573</th>\n",
       "      <td>AB-4 shallow</td>\n",
       "      <td>10N04E31M004M</td>\n",
       "      <td>10N04E31M004M</td>\n",
       "      <td>Groundwater</td>\n",
       "      <td>38.6669</td>\n",
       "      <td>-121.584</td>\n",
       "      <td>Sacramento</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47574</th>\n",
       "      <td>LCB-5 deep</td>\n",
       "      <td>13N01E32K001M</td>\n",
       "      <td>13N01E32K001M</td>\n",
       "      <td>Groundwater</td>\n",
       "      <td>38.9169</td>\n",
       "      <td>-121.883</td>\n",
       "      <td>Colusa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47575</th>\n",
       "      <td>LCB-5 intermediate</td>\n",
       "      <td>13N01E32K002M</td>\n",
       "      <td>13N01E32K002M</td>\n",
       "      <td>Groundwater</td>\n",
       "      <td>38.9169</td>\n",
       "      <td>-121.883</td>\n",
       "      <td>Colusa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47576</th>\n",
       "      <td>LCB-5 shallow</td>\n",
       "      <td>13N01E32K003M</td>\n",
       "      <td>13N01E32K003M</td>\n",
       "      <td>Groundwater</td>\n",
       "      <td>38.9169</td>\n",
       "      <td>-121.883</td>\n",
       "      <td>Colusa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47577</th>\n",
       "      <td>SUT-P4</td>\n",
       "      <td>11N04E04N001M</td>\n",
       "      <td>11N04E04N001M</td>\n",
       "      <td>Groundwater</td>\n",
       "      <td>38.8236</td>\n",
       "      <td>-121.543</td>\n",
       "      <td>Sutter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47578</th>\n",
       "      <td>SUT-P3</td>\n",
       "      <td>11N04E04N002M</td>\n",
       "      <td>11N04E04N002M</td>\n",
       "      <td>Groundwater</td>\n",
       "      <td>38.8236</td>\n",
       "      <td>-121.543</td>\n",
       "      <td>Sutter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47579</th>\n",
       "      <td>SUT-P2</td>\n",
       "      <td>11N04E04N003M</td>\n",
       "      <td>11N04E04N003M</td>\n",
       "      <td>Groundwater</td>\n",
       "      <td>38.8236</td>\n",
       "      <td>-121.543</td>\n",
       "      <td>Sutter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47580</th>\n",
       "      <td>SUT-P1</td>\n",
       "      <td>11N04E04N004M</td>\n",
       "      <td>11N04E04N004M</td>\n",
       "      <td>Groundwater</td>\n",
       "      <td>38.8236</td>\n",
       "      <td>-121.543</td>\n",
       "      <td>Sutter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47800</th>\n",
       "      <td>Teerink PP</td>\n",
       "      <td>Teerink Pumping Plant</td>\n",
       "      <td>KA027813</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>35.0328</td>\n",
       "      <td>-119.008</td>\n",
       "      <td>Kern</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47838</th>\n",
       "      <td>Liberty Cut @ USGS</td>\n",
       "      <td>Liberty Cut at USGS Piling</td>\n",
       "      <td>B9D81971401</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>38.3289</td>\n",
       "      <td>-121.668</td>\n",
       "      <td>Yolo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47839</th>\n",
       "      <td>LibCut ds Stairsteps</td>\n",
       "      <td>Liberty Cut downstream of the Stairsteps</td>\n",
       "      <td>B9D82001400</td>\n",
       "      <td>Surface Water</td>\n",
       "      <td>38.3333</td>\n",
       "      <td>-121.667</td>\n",
       "      <td>Yolo</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1101 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                    station_name  \\\n",
       "station_id                         \n",
       "1                       AMERICAN   \n",
       "10                       CLIFTON   \n",
       "12          H.O. Banks Headworks   \n",
       "73                        COLUSA   \n",
       "78                 TWITCHELLPP01   \n",
       "80                      CHECK 13   \n",
       "87           Del Valle Reservoir   \n",
       "99          OR nr Byron Hwy 4 Br   \n",
       "114                   SACWSACINT   \n",
       "122                BaconAgDrain1   \n",
       "127                    CONCOSPP1   \n",
       "133                   STATENPP02   \n",
       "143                 PROSL@LHOLTR   \n",
       "145          Toe Drain YB LISBON   \n",
       "154                  OR@Bacon Is   \n",
       "158                    SJOAQHWY4   \n",
       "189                  MOKSFSTATEN   \n",
       "192              LYTLE CRK NR SB   \n",
       "218         Castaic Tower Outlet   \n",
       "234                     DKA13481   \n",
       "259                Del Valle COW   \n",
       "266                 Frenchman Lk   \n",
       "275                     GKA26966   \n",
       "277                     GKA27210   \n",
       "278                     GKA27231   \n",
       "279                     GKA27253   \n",
       "280                     GKA27280   \n",
       "282                     GKA27375   \n",
       "283                     GKA27728   \n",
       "297                 Check 11 1/2   \n",
       "...                          ...   \n",
       "47161             SutterCo_MW-1D   \n",
       "47162                      AV-3A   \n",
       "47163                      AV-3B   \n",
       "47164                      AV-3C   \n",
       "47168              22N01W29N001M   \n",
       "47169              22N01W29N002M   \n",
       "47170              22N01W29N003M   \n",
       "47171              22N01W29N004M   \n",
       "47172              21N01W31H001M   \n",
       "47175       Clear C NR MO NR Red   \n",
       "47176          Paynes C A P Xing   \n",
       "47195              29N04W03R002M   \n",
       "47196              29N04W03R003M   \n",
       "47197              29N04W03R004M   \n",
       "47198              29N04W03R005M   \n",
       "47199              29N04W03R006M   \n",
       "47570                  AB-4 deep   \n",
       "47571       AB-4 intermediate-de   \n",
       "47572       AB-4 intermediate-sh   \n",
       "47573               AB-4 shallow   \n",
       "47574                 LCB-5 deep   \n",
       "47575         LCB-5 intermediate   \n",
       "47576              LCB-5 shallow   \n",
       "47577                     SUT-P4   \n",
       "47578                     SUT-P3   \n",
       "47579                     SUT-P2   \n",
       "47580                     SUT-P1   \n",
       "47800                 Teerink PP   \n",
       "47838         Liberty Cut @ USGS   \n",
       "47839       LibCut ds Stairsteps   \n",
       "\n",
       "                                           station_name_full station_number  \\\n",
       "station_id                                                                    \n",
       "1                    American River at Water Treatment Plant       A0714010   \n",
       "10                                      Clifton Court Intake       KA000000   \n",
       "12                     Delta P.P. Headworks at H.O. Banks PP       KA000331   \n",
       "73                       Ag Drain on Colusa Basin Main Drain       A0294500   \n",
       "78                     Ag Drain on Twitchell Isl., PP. No. 1    B9V80661391   \n",
       "80                        CA Aqueduct, Ck 13, O'Neill Outlet       KA007089   \n",
       "87                              Lake Del Valle at Glory Hole       DV001000   \n",
       "99               Old R. nr. Byron (St 9) (NEAR HWY 4 BRIDGE)    B9D75351342   \n",
       "114              Sacramento River at W. Sac Intake Structure       A0210451   \n",
       "122                      Ag Drain on Bacon Island, PP. No. 1    B9V75881342   \n",
       "127                                Contra Costa PP Number 01       B9591000   \n",
       "133                      Ag Drain on Staten Island PP. No. 2    B9V80751335   \n",
       "143         Prospect Slough @ S. Tip of Little Holland Tract    B9S81701398   \n",
       "145                  Yolo Bypass Toe Drain Below Lisbon Weir    B9D82851352   \n",
       "154                                Old River at Bacon Island    B9D75811344   \n",
       "158                                   San Joaquin R. @ Hwy 4    B9D75571196   \n",
       "189                    Mokelumne River S.F. at Staten Island    B9D80701299   \n",
       "192                          Lytle Creek near San Bernardino       Y3165000   \n",
       "218                                Castaic Lake Outlet Tower       CA002000   \n",
       "234         DRAINAGE INLET AT MILE POST 134.81, CANTUA CREEK       DKA13481   \n",
       "259                          Del Valle Conserv. Outlet Works       DV000000   \n",
       "266                                       FRENCHMAN LK NR DM       FR001000   \n",
       "275               NON-PROJECT IMPORT STATION (WHEELER RIDGE)       GKA26966   \n",
       "277               NON-PROJECT IMPORT STATION (WHEELER RIDGE)       GKA27210   \n",
       "278               NON-PROJECT IMPORT STATION (WHEELER RIDGE)       GKA27231   \n",
       "279               NON-PROJECT IMPORT STATION (WHEELER RIDGE)       GKA27253   \n",
       "280               NON-PROJECT IMPORT STATION (WHEELER RIDGE)       GKA27280   \n",
       "282               NON-PROJECT IMPORT STATION (WHEELER RIDGE)       GKA27375   \n",
       "283               NON-PROJECT IMPORT STATION (WHEELER RIDGE)       GKA27728   \n",
       "297                        CALIFORNIA AQUEDUCT NEAR CHECK 12       KA006633   \n",
       "...                                                      ...            ...   \n",
       "47161                                          14N02E17C004M  14N02E17C004M   \n",
       "47162                                          14N14W34R005M  14N14W34R005M   \n",
       "47163                                          14N14W34R006M  14N14W34R006M   \n",
       "47164                                          14N14W34R007M  14N14W34R007M   \n",
       "47168                                          22N01W29N001M  22N01W29N001M   \n",
       "47169                                          22N01W29N002M  22N01W29N002M   \n",
       "47170                                          22N01W29N003M  22N01W29N003M   \n",
       "47171                                          22N01W29N004M  22N01W29N004M   \n",
       "47172                                          21N01W31H001M  21N01W31H001M   \n",
       "47175                    Clear Creek near Mouth near Redding       A3601000   \n",
       "47176                            Paynes C at Paynes Crossing       A0460200   \n",
       "47195                                          29N04W03R002M  29N04W03R002M   \n",
       "47196                                          29N04W03R003M  29N04W03R003M   \n",
       "47197                                          29N04W03R004M  29N04W03R004M   \n",
       "47198                                          29N04W03R005M  29N04W03R005M   \n",
       "47199                                          29N04W03R006M  29N04W03R006M   \n",
       "47570                                          10N04E31M001M  10N04E31M001M   \n",
       "47571                                          10N04E31M002M  10N04E31M002M   \n",
       "47572                                          10N04E31M003M  10N04E31M003M   \n",
       "47573                                          10N04E31M004M  10N04E31M004M   \n",
       "47574                                          13N01E32K001M  13N01E32K001M   \n",
       "47575                                          13N01E32K002M  13N01E32K002M   \n",
       "47576                                          13N01E32K003M  13N01E32K003M   \n",
       "47577                                          11N04E04N001M  11N04E04N001M   \n",
       "47578                                          11N04E04N002M  11N04E04N002M   \n",
       "47579                                          11N04E04N003M  11N04E04N003M   \n",
       "47580                                          11N04E04N004M  11N04E04N004M   \n",
       "47800                                  Teerink Pumping Plant       KA027813   \n",
       "47838                             Liberty Cut at USGS Piling    B9D81971401   \n",
       "47839               Liberty Cut downstream of the Stairsteps    B9D82001400   \n",
       "\n",
       "             station_type  latitude  longitude     county_name  \n",
       "station_id                                                      \n",
       "1           Surface Water   38.5596   -121.417      Sacramento  \n",
       "10          Surface Water   37.8298   -121.557    Contra Costa  \n",
       "12          Surface Water   37.8019   -121.620         Alameda  \n",
       "73          Surface Water   38.7993   -121.725            Yolo  \n",
       "78          Surface Water   38.0966   -121.651      Sacramento  \n",
       "80          Surface Water   37.0742   -121.015          Merced  \n",
       "87          Surface Water   37.6147   -121.746         Alameda  \n",
       "99          Surface Water   37.8910   -121.570    Contra Costa  \n",
       "114         Surface Water   38.5980   -121.549            Yolo  \n",
       "122         Surface Water   37.9794   -121.570     San Joaquin  \n",
       "127         Surface Water   37.9788   -121.702    Contra Costa  \n",
       "133         Surface Water   38.1252   -121.559     San Joaquin  \n",
       "143         Surface Water   38.2835   -121.664          Solano  \n",
       "145         Surface Water   38.4749   -121.588            Yolo  \n",
       "154         Surface Water   37.9694   -121.572     San Joaquin  \n",
       "158         Surface Water   37.9283   -121.328     San Joaquin  \n",
       "189         Surface Water   38.1171   -121.499     San Joaquin  \n",
       "192         Surface Water   34.2309   -117.483  San Bernardino  \n",
       "218         Surface Water   34.5152   -118.610     Los Angeles  \n",
       "234         Surface Water   36.4298   -120.296          Fresno  \n",
       "259         Surface Water   37.6183   -121.749         Alameda  \n",
       "266         Surface Water   39.8944   -120.192          Plumas  \n",
       "275         Surface Water   35.0684   -119.141            Kern  \n",
       "277         Surface Water   35.0608   -119.099            Kern  \n",
       "278         Surface Water   35.0591   -119.095            Kern  \n",
       "279         Surface Water   35.0581   -119.091            Kern  \n",
       "280         Surface Water   35.0568   -119.086            Kern  \n",
       "282         Surface Water   35.0480   -119.074            Kern  \n",
       "283         Surface Water   35.0377   -119.021            Kern  \n",
       "297         Surface Water   37.1183   -121.057          Merced  \n",
       "...                   ...       ...        ...             ...  \n",
       "47161         Groundwater   39.0696   -121.778          Sutter  \n",
       "47162         Groundwater   39.0254   -123.382       Mendocino  \n",
       "47163         Groundwater   39.0254   -123.382       Mendocino  \n",
       "47164         Groundwater   39.0254   -123.382       Mendocino  \n",
       "47168         Groundwater   39.7264   -122.010           Glenn  \n",
       "47169         Groundwater   39.7264   -122.010           Glenn  \n",
       "47170         Groundwater   39.7264   -122.010           Glenn  \n",
       "47171         Groundwater   39.7264   -122.010           Glenn  \n",
       "47172         Groundwater   39.6830   -122.011           Glenn  \n",
       "47175       Surface Water   40.5092   -122.380          Shasta  \n",
       "47176       Surface Water   40.2758   -122.181          Tehama  \n",
       "47195         Groundwater   40.3928   -122.294          Shasta  \n",
       "47196         Groundwater   40.3928   -122.294          Shasta  \n",
       "47197         Groundwater   40.3928   -122.294          Shasta  \n",
       "47198         Groundwater   40.3928   -122.294          Shasta  \n",
       "47199         Groundwater   40.3928   -122.294          Shasta  \n",
       "47570         Groundwater   38.6669   -121.584      Sacramento  \n",
       "47571         Groundwater   38.6669   -121.584      Sacramento  \n",
       "47572         Groundwater   38.6669   -121.584      Sacramento  \n",
       "47573         Groundwater   38.6669   -121.584      Sacramento  \n",
       "47574         Groundwater   38.9169   -121.883          Colusa  \n",
       "47575         Groundwater   38.9169   -121.883          Colusa  \n",
       "47576         Groundwater   38.9169   -121.883          Colusa  \n",
       "47577         Groundwater   38.8236   -121.543          Sutter  \n",
       "47578         Groundwater   38.8236   -121.543          Sutter  \n",
       "47579         Groundwater   38.8236   -121.543          Sutter  \n",
       "47580         Groundwater   38.8236   -121.543          Sutter  \n",
       "47800       Surface Water   35.0328   -119.008            Kern  \n",
       "47838       Surface Water   38.3289   -121.668            Yolo  \n",
       "47839       Surface Water   38.3333   -121.667            Yolo  \n",
       "\n",
       "[1101 rows x 7 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def get_normalized(df, pk=None, **kwargs):\n",
    "    return df.drop_duplicates(subset=pk, **kwargs)\n",
    "\n",
    "(get_normalized(DATA[[\n",
    "    'station_id', 'station_name', 'station_name_full', 'station_number', 'station_type',\n",
    "    'latitude', 'longitude', 'county_name',\n",
    "    ]],\n",
    "    pk='station_id')\n",
    " .set_index('station_id').sort_index()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "cawc",
   "language": "python",
   "name": "cawc"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}