Clean EPA-EIA Crosswalk.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pathlib import Path\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import EPA-EIA Crosswalk spreadsheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "  plant_name  plant_id_epa  plant_id_eia unitid generator_id boiler_id  \\\n0      Barry             3           NaN      1            1         1   \n1      Barry             3           NaN      2            2         2   \n2      Barry             3           NaN      4            4         4   \n3      Barry             3           NaN      5            5         5   \n4      Barry             3           NaN     6A         A1ST        6A   \n\n      fuel_type_primary prime_mover_code  edat_capacity_mw  heat_input_mmbtu  \\\n0  Pipeline Natural Gas               ST             180.0      1.847805e+05   \n1  Pipeline Natural Gas               ST             180.0      1.888817e+05   \n2                  Coal               ST             400.0      1.224252e+07   \n3                  Coal               ST             800.0      3.322478e+07   \n4  Pipeline Natural Gas               CT             312.0      1.514752e+07   \n\n  generator_id_match_method op_status op_status_date  \n0        X-walk from Travis       OPR   Feb/11/1954   \n1        X-walk from Travis       OPR   Jun/18/1954   \n2        X-walk from Travis       OPR   May/28/1969   \n3        X-walk from Travis       OPR   Jul/24/1971   \n4        X-walk from Travis       OPR   Feb/22/2000   ",
      "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>plant_name</th>\n      <th>plant_id_epa</th>\n      <th>plant_id_eia</th>\n      <th>unitid</th>\n      <th>generator_id</th>\n      <th>boiler_id</th>\n      <th>fuel_type_primary</th>\n      <th>prime_mover_code</th>\n      <th>edat_capacity_mw</th>\n      <th>heat_input_mmbtu</th>\n      <th>generator_id_match_method</th>\n      <th>op_status</th>\n      <th>op_status_date</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>Barry</td>\n      <td>3</td>\n      <td>NaN</td>\n      <td>1</td>\n      <td>1</td>\n      <td>1</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>180.0</td>\n      <td>1.847805e+05</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Feb/11/1954</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Barry</td>\n      <td>3</td>\n      <td>NaN</td>\n      <td>2</td>\n      <td>2</td>\n      <td>2</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>180.0</td>\n      <td>1.888817e+05</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Jun/18/1954</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Barry</td>\n      <td>3</td>\n      <td>NaN</td>\n      <td>4</td>\n      <td>4</td>\n      <td>4</td>\n      <td>Coal</td>\n      <td>ST</td>\n      <td>400.0</td>\n      <td>1.224252e+07</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>May/28/1969</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Barry</td>\n      <td>3</td>\n      <td>NaN</td>\n      <td>5</td>\n      <td>5</td>\n      <td>5</td>\n      <td>Coal</td>\n      <td>ST</td>\n      <td>800.0</td>\n      <td>3.322478e+07</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Jul/24/1971</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Barry</td>\n      <td>3</td>\n      <td>NaN</td>\n      <td>6A</td>\n      <td>A1ST</td>\n      <td>6A</td>\n      <td>Pipeline Natural Gas</td>\n      <td>CT</td>\n      <td>312.0</td>\n      <td>1.514752e+07</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Feb/22/2000</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 76
    }
   ],
   "source": [
    "crosswalk_file = Path.cwd() / 'epa_eia_crosswalk_from_epa.csv'\n",
    "df = pd.read_csv(crosswalk_file, \n",
    "                       names=['plant_name',\n",
    "                              'plant_id_epa',\n",
    "                              'plant_id_eia',\n",
    "                              'unitid',\n",
    "                              'generator_id',\n",
    "                              'boiler_id',\n",
    "                              'fuel_type_primary',\n",
    "                              'prime_mover_code',\n",
    "                              'edat_capacity_mw',\n",
    "                              'heat_input_mmbtu',\n",
    "                              'generator_id_match_method',\n",
    "                              'op_status',\n",
    "                              'op_status_date'],\n",
    "                      header=0,\n",
    "                      index_col=False)\n",
    "df.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Fill in plant_id_eia\n",
    "Some plant ids have been matched across EPA and EIA, but many of the associations are missing. We will take a layered approach to matching the missing plant ids:\n",
    "\n",
    "1. Attempt to match based on plant name strings from EIA and EPA\n",
    "2. In most cases, the EPA has already associated an EIA generator_id with each EPA unit_id. In this case, if we are able to find an EIA plant_id generator_id pair that matches an EPA plant_id generator_id pair, we will assume that the EPA and EIA plant_ids are identical.\n",
    "3. For whatever is left, we will simply look for instances where there is an EIA plant_id that matches an EPA plant_id. By comparing the plant names, we should be able to identify if these plants are the same.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "   index plant_name  plant_id_epa  plant_id_eia unitid generator_id boiler_id  \\\n0      0      Barry             3           NaN      1            1         1   \n1      1      Barry             3           NaN      2            2         2   \n2      2      Barry             3           NaN      4            4         4   \n3      3      Barry             3           NaN      5            5         5   \n4      4      Barry             3           NaN     6A         A1ST        6A   \n\n      fuel_type_primary prime_mover_code  edat_capacity_mw  heat_input_mmbtu  \\\n0  Pipeline Natural Gas               ST             180.0      1.847805e+05   \n1  Pipeline Natural Gas               ST             180.0      1.888817e+05   \n2                  Coal               ST             400.0      1.224252e+07   \n3                  Coal               ST             800.0      3.322478e+07   \n4  Pipeline Natural Gas               CT             312.0      1.514752e+07   \n\n  generator_id_match_method op_status op_status_date  \n0        X-walk from Travis       OPR   Feb/11/1954   \n1        X-walk from Travis       OPR   Jun/18/1954   \n2        X-walk from Travis       OPR   May/28/1969   \n3        X-walk from Travis       OPR   Jul/24/1971   \n4        X-walk from Travis       OPR   Feb/22/2000   ",
      "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>index</th>\n      <th>plant_name</th>\n      <th>plant_id_epa</th>\n      <th>plant_id_eia</th>\n      <th>unitid</th>\n      <th>generator_id</th>\n      <th>boiler_id</th>\n      <th>fuel_type_primary</th>\n      <th>prime_mover_code</th>\n      <th>edat_capacity_mw</th>\n      <th>heat_input_mmbtu</th>\n      <th>generator_id_match_method</th>\n      <th>op_status</th>\n      <th>op_status_date</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0</td>\n      <td>Barry</td>\n      <td>3</td>\n      <td>NaN</td>\n      <td>1</td>\n      <td>1</td>\n      <td>1</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>180.0</td>\n      <td>1.847805e+05</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Feb/11/1954</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1</td>\n      <td>Barry</td>\n      <td>3</td>\n      <td>NaN</td>\n      <td>2</td>\n      <td>2</td>\n      <td>2</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>180.0</td>\n      <td>1.888817e+05</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Jun/18/1954</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2</td>\n      <td>Barry</td>\n      <td>3</td>\n      <td>NaN</td>\n      <td>4</td>\n      <td>4</td>\n      <td>4</td>\n      <td>Coal</td>\n      <td>ST</td>\n      <td>400.0</td>\n      <td>1.224252e+07</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>May/28/1969</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>3</td>\n      <td>Barry</td>\n      <td>3</td>\n      <td>NaN</td>\n      <td>5</td>\n      <td>5</td>\n      <td>5</td>\n      <td>Coal</td>\n      <td>ST</td>\n      <td>800.0</td>\n      <td>3.322478e+07</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Jul/24/1971</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>4</td>\n      <td>Barry</td>\n      <td>3</td>\n      <td>NaN</td>\n      <td>6A</td>\n      <td>A1ST</td>\n      <td>6A</td>\n      <td>Pipeline Natural Gas</td>\n      <td>CT</td>\n      <td>312.0</td>\n      <td>1.514752e+07</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Feb/22/2000</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 77
    }
   ],
   "source": [
    "df.reset_index(inplace=True)\n",
    "#extract rows wheere plants_id_eia is missing\n",
    "missing_plant_eia = df.query(\"plant_id_eia == 'NaN'\")\n",
    "missing_plant_eia.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "6747"
     },
     "metadata": {},
     "execution_count": 78
    }
   ],
   "source": [
    "#how many missing values are there?\n",
    "missing_plant_eia.plant_id_eia.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Attempt to match by plant name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "   plant_id_eia            plant_name_eia\n0         62940            Starratt Solar\n1         62939           South Peak Wind\n2         62938  Glen Ullin Energy Center\n3         62937               Athens BESS\n4         62936               Rattlesnake",
      "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>plant_id_eia</th>\n      <th>plant_name_eia</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>62940</td>\n      <td>Starratt Solar</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>62939</td>\n      <td>South Peak Wind</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>62938</td>\n      <td>Glen Ullin Energy Center</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>62937</td>\n      <td>Athens BESS</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>62936</td>\n      <td>Rattlesnake</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 79
    }
   ],
   "source": [
    "#import plants_eia file, which contains the plant id and plant name from the EIA records\n",
    "datapkg = Path.home() / 'Box/PUDL/datapkg/pudl-data-release/pudl-eia860-eia923-epacems/data'\n",
    "plants_eia = pd.read_csv(datapkg / 'plants_entity_eia.csv')\n",
    "\n",
    "#drop rows with mostly null values, as these seem to be from non-EIA-860 sources\n",
    "#plants_eia.dropna(axis='index',thresh=10,inplace=True)\n",
    "\n",
    "plants_eia = plants_eia.filter(['plant_id_eia','plant_name_eia'])\n",
    "\n",
    "plants_eia.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "#need to drop two plants with duplicate names\n",
    "plants_eia.drop(plants_eia[plants_eia['plant_id_eia']==880081].index,inplace=True) #duplicate value for \"DTE Pontiac North LLC\"\n",
    "plants_eia.drop(plants_eia[plants_eia['plant_id_eia']==14013].index,inplace=True) #duplicate value for \"The Ohio State University\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "   index plant_name  plant_id_epa  plant_id_eia_x unitid generator_id  \\\n0      0      Barry             3             NaN      1            1   \n1      1      Barry             3             NaN      2            2   \n2      2      Barry             3             NaN      4            4   \n3      3      Barry             3             NaN      5            5   \n4      4      Barry             3             NaN     6A         A1ST   \n\n  boiler_id     fuel_type_primary prime_mover_code  edat_capacity_mw  \\\n0         1  Pipeline Natural Gas               ST             180.0   \n1         2  Pipeline Natural Gas               ST             180.0   \n2         4                  Coal               ST             400.0   \n3         5                  Coal               ST             800.0   \n4        6A  Pipeline Natural Gas               CT             312.0   \n\n   heat_input_mmbtu generator_id_match_method op_status op_status_date  \\\n0      1.847805e+05        X-walk from Travis       OPR   Feb/11/1954    \n1      1.888817e+05        X-walk from Travis       OPR   Jun/18/1954    \n2      1.224252e+07        X-walk from Travis       OPR   May/28/1969    \n3      3.322478e+07        X-walk from Travis       OPR   Jul/24/1971    \n4      1.514752e+07        X-walk from Travis       OPR   Feb/22/2000    \n\n   plant_id_eia_y plant_name_eia  \n0             3.0          Barry  \n1             3.0          Barry  \n2             3.0          Barry  \n3             3.0          Barry  \n4             3.0          Barry  ",
      "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>index</th>\n      <th>plant_name</th>\n      <th>plant_id_epa</th>\n      <th>plant_id_eia_x</th>\n      <th>unitid</th>\n      <th>generator_id</th>\n      <th>boiler_id</th>\n      <th>fuel_type_primary</th>\n      <th>prime_mover_code</th>\n      <th>edat_capacity_mw</th>\n      <th>heat_input_mmbtu</th>\n      <th>generator_id_match_method</th>\n      <th>op_status</th>\n      <th>op_status_date</th>\n      <th>plant_id_eia_y</th>\n      <th>plant_name_eia</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0</td>\n      <td>Barry</td>\n      <td>3</td>\n      <td>NaN</td>\n      <td>1</td>\n      <td>1</td>\n      <td>1</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>180.0</td>\n      <td>1.847805e+05</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Feb/11/1954</td>\n      <td>3.0</td>\n      <td>Barry</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1</td>\n      <td>Barry</td>\n      <td>3</td>\n      <td>NaN</td>\n      <td>2</td>\n      <td>2</td>\n      <td>2</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>180.0</td>\n      <td>1.888817e+05</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Jun/18/1954</td>\n      <td>3.0</td>\n      <td>Barry</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2</td>\n      <td>Barry</td>\n      <td>3</td>\n      <td>NaN</td>\n      <td>4</td>\n      <td>4</td>\n      <td>4</td>\n      <td>Coal</td>\n      <td>ST</td>\n      <td>400.0</td>\n      <td>1.224252e+07</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>May/28/1969</td>\n      <td>3.0</td>\n      <td>Barry</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>3</td>\n      <td>Barry</td>\n      <td>3</td>\n      <td>NaN</td>\n      <td>5</td>\n      <td>5</td>\n      <td>5</td>\n      <td>Coal</td>\n      <td>ST</td>\n      <td>800.0</td>\n      <td>3.322478e+07</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Jul/24/1971</td>\n      <td>3.0</td>\n      <td>Barry</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>4</td>\n      <td>Barry</td>\n      <td>3</td>\n      <td>NaN</td>\n      <td>6A</td>\n      <td>A1ST</td>\n      <td>6A</td>\n      <td>Pipeline Natural Gas</td>\n      <td>CT</td>\n      <td>312.0</td>\n      <td>1.514752e+07</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Feb/22/2000</td>\n      <td>3.0</td>\n      <td>Barry</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 81
    }
   ],
   "source": [
    "#merge the EIA plant name data with the crosswalk on the plant name\n",
    "missing_plant_eia = missing_plant_eia.merge(plants_eia, how='left', left_on='plant_name', right_on='plant_name_eia')\n",
    "missing_plant_eia.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "Empty DataFrame\nColumns: [index, plant_name, plant_id_epa, plant_id_eia_x, unitid, generator_id, boiler_id, fuel_type_primary, prime_mover_code, edat_capacity_mw, heat_input_mmbtu, generator_id_match_method, op_status, op_status_date, plant_id_eia_y, plant_name_eia]\nIndex: []",
      "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>index</th>\n      <th>plant_name</th>\n      <th>plant_id_epa</th>\n      <th>plant_id_eia_x</th>\n      <th>unitid</th>\n      <th>generator_id</th>\n      <th>boiler_id</th>\n      <th>fuel_type_primary</th>\n      <th>prime_mover_code</th>\n      <th>edat_capacity_mw</th>\n      <th>heat_input_mmbtu</th>\n      <th>generator_id_match_method</th>\n      <th>op_status</th>\n      <th>op_status_date</th>\n      <th>plant_id_eia_y</th>\n      <th>plant_name_eia</th>\n    </tr>\n  </thead>\n  <tbody>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 82
    }
   ],
   "source": [
    "#need to drop any duplicates based on merge\n",
    "missing_plant_eia[missing_plant_eia.duplicated(subset=\"index\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "   index plant_name  plant_id_epa  plant_id_eia unitid generator_id boiler_id  \\\n0      0      Barry             3           3.0      1            1         1   \n1      1      Barry             3           3.0      2            2         2   \n2      2      Barry             3           3.0      4            4         4   \n3      3      Barry             3           3.0      5            5         5   \n4      4      Barry             3           3.0     6A         A1ST        6A   \n\n      fuel_type_primary prime_mover_code  edat_capacity_mw  heat_input_mmbtu  \\\n0  Pipeline Natural Gas               ST             180.0      1.847805e+05   \n1  Pipeline Natural Gas               ST             180.0      1.888817e+05   \n2                  Coal               ST             400.0      1.224252e+07   \n3                  Coal               ST             800.0      3.322478e+07   \n4  Pipeline Natural Gas               CT             312.0      1.514752e+07   \n\n  generator_id_match_method op_status op_status_date  \n0        X-walk from Travis       OPR   Feb/11/1954   \n1        X-walk from Travis       OPR   Jun/18/1954   \n2        X-walk from Travis       OPR   May/28/1969   \n3        X-walk from Travis       OPR   Jul/24/1971   \n4        X-walk from Travis       OPR   Feb/22/2000   ",
      "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>index</th>\n      <th>plant_name</th>\n      <th>plant_id_epa</th>\n      <th>plant_id_eia</th>\n      <th>unitid</th>\n      <th>generator_id</th>\n      <th>boiler_id</th>\n      <th>fuel_type_primary</th>\n      <th>prime_mover_code</th>\n      <th>edat_capacity_mw</th>\n      <th>heat_input_mmbtu</th>\n      <th>generator_id_match_method</th>\n      <th>op_status</th>\n      <th>op_status_date</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0</td>\n      <td>Barry</td>\n      <td>3</td>\n      <td>3.0</td>\n      <td>1</td>\n      <td>1</td>\n      <td>1</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>180.0</td>\n      <td>1.847805e+05</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Feb/11/1954</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1</td>\n      <td>Barry</td>\n      <td>3</td>\n      <td>3.0</td>\n      <td>2</td>\n      <td>2</td>\n      <td>2</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>180.0</td>\n      <td>1.888817e+05</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Jun/18/1954</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2</td>\n      <td>Barry</td>\n      <td>3</td>\n      <td>3.0</td>\n      <td>4</td>\n      <td>4</td>\n      <td>4</td>\n      <td>Coal</td>\n      <td>ST</td>\n      <td>400.0</td>\n      <td>1.224252e+07</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>May/28/1969</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>3</td>\n      <td>Barry</td>\n      <td>3</td>\n      <td>3.0</td>\n      <td>5</td>\n      <td>5</td>\n      <td>5</td>\n      <td>Coal</td>\n      <td>ST</td>\n      <td>800.0</td>\n      <td>3.322478e+07</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Jul/24/1971</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>4</td>\n      <td>Barry</td>\n      <td>3</td>\n      <td>3.0</td>\n      <td>6A</td>\n      <td>A1ST</td>\n      <td>6A</td>\n      <td>Pipeline Natural Gas</td>\n      <td>CT</td>\n      <td>312.0</td>\n      <td>1.514752e+07</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Feb/22/2000</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 83
    }
   ],
   "source": [
    "missing_plant_eia.plant_id_eia_x = missing_plant_eia.plant_id_eia_y #replace plant id values with those from the string match\n",
    "missing_plant_eia.drop(columns=['plant_id_eia_y','plant_name_eia'],inplace=True) #drop the columns that were merged in from EIA\n",
    "missing_plant_eia.rename(columns={\"plant_id_eia_x\":\"plant_id_eia\"},inplace=True)\n",
    "missing_plant_eia.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "3845"
     },
     "metadata": {},
     "execution_count": 84
    }
   ],
   "source": [
    "missing_plant_eia.plant_id_eia.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Attempt to match by plant_id and generator_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "   plant_id_eia generator_id\n0         62940           PV\n1         62939        41001\n2         62938        39001\n3         62937           BA\n4         62936          RAT",
      "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>plant_id_eia</th>\n      <th>generator_id</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>62940</td>\n      <td>PV</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>62939</td>\n      <td>41001</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>62938</td>\n      <td>39001</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>62937</td>\n      <td>BA</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>62936</td>\n      <td>RAT</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 85
    }
   ],
   "source": [
    "generators_eia = pd.read_csv(datapkg / 'generators_entity_eia.csv', usecols=['plant_id_eia','generator_id'])\n",
    "generators_eia.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "   index plant_name  plant_id_epa  plant_id_eia_x unitid generator_id  \\\n0      0      Barry             3             3.0      1            1   \n1      1      Barry             3             3.0      2            2   \n2      2      Barry             3             3.0      4            4   \n3      3      Barry             3             3.0      5            5   \n4      4      Barry             3             3.0     6A         A1ST   \n\n  boiler_id     fuel_type_primary prime_mover_code  edat_capacity_mw  \\\n0         1  Pipeline Natural Gas               ST             180.0   \n1         2  Pipeline Natural Gas               ST             180.0   \n2         4                  Coal               ST             400.0   \n3         5                  Coal               ST             800.0   \n4        6A  Pipeline Natural Gas               CT             312.0   \n\n   heat_input_mmbtu generator_id_match_method op_status op_status_date  \\\n0      1.847805e+05        X-walk from Travis       OPR   Feb/11/1954    \n1      1.888817e+05        X-walk from Travis       OPR   Jun/18/1954    \n2      1.224252e+07        X-walk from Travis       OPR   May/28/1969    \n3      3.322478e+07        X-walk from Travis       OPR   Jul/24/1971    \n4      1.514752e+07        X-walk from Travis       OPR   Feb/22/2000    \n\n   plant_id_eia_y  \n0             3.0  \n1             3.0  \n2             3.0  \n3             3.0  \n4             3.0  ",
      "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>index</th>\n      <th>plant_name</th>\n      <th>plant_id_epa</th>\n      <th>plant_id_eia_x</th>\n      <th>unitid</th>\n      <th>generator_id</th>\n      <th>boiler_id</th>\n      <th>fuel_type_primary</th>\n      <th>prime_mover_code</th>\n      <th>edat_capacity_mw</th>\n      <th>heat_input_mmbtu</th>\n      <th>generator_id_match_method</th>\n      <th>op_status</th>\n      <th>op_status_date</th>\n      <th>plant_id_eia_y</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>0</td>\n      <td>Barry</td>\n      <td>3</td>\n      <td>3.0</td>\n      <td>1</td>\n      <td>1</td>\n      <td>1</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>180.0</td>\n      <td>1.847805e+05</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Feb/11/1954</td>\n      <td>3.0</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>1</td>\n      <td>Barry</td>\n      <td>3</td>\n      <td>3.0</td>\n      <td>2</td>\n      <td>2</td>\n      <td>2</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>180.0</td>\n      <td>1.888817e+05</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Jun/18/1954</td>\n      <td>3.0</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>2</td>\n      <td>Barry</td>\n      <td>3</td>\n      <td>3.0</td>\n      <td>4</td>\n      <td>4</td>\n      <td>4</td>\n      <td>Coal</td>\n      <td>ST</td>\n      <td>400.0</td>\n      <td>1.224252e+07</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>May/28/1969</td>\n      <td>3.0</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>3</td>\n      <td>Barry</td>\n      <td>3</td>\n      <td>3.0</td>\n      <td>5</td>\n      <td>5</td>\n      <td>5</td>\n      <td>Coal</td>\n      <td>ST</td>\n      <td>800.0</td>\n      <td>3.322478e+07</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Jul/24/1971</td>\n      <td>3.0</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>4</td>\n      <td>Barry</td>\n      <td>3</td>\n      <td>3.0</td>\n      <td>6A</td>\n      <td>A1ST</td>\n      <td>6A</td>\n      <td>Pipeline Natural Gas</td>\n      <td>CT</td>\n      <td>312.0</td>\n      <td>1.514752e+07</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Feb/22/2000</td>\n      <td>3.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 86
    }
   ],
   "source": [
    "#if the epa plant has already been matched with a generator_id from EIA, then pull the EIA plant_id\n",
    "missing_plant_eia = missing_plant_eia.merge(generators_eia, \n",
    "                                            how='left', \n",
    "                                            left_on=['plant_id_epa','generator_id'], \n",
    "                                            right_on=['plant_id_eia','generator_id'])\n",
    "missing_plant_eia.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "missing_plant_eia.plant_id_eia_x.fillna(missing_plant_eia.plant_id_eia_y, inplace=True) #if a match was found, fill any missing values with that id\n",
    "\n",
    "#let's do some cleanup\n",
    "missing_plant_eia.drop(columns=['plant_id_eia_y'],inplace=True)\n",
    "missing_plant_eia.rename(columns={'plant_id_eia_x':'plant_id_eia'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "52"
     },
     "metadata": {},
     "execution_count": 88
    }
   ],
   "source": [
    "#how many are missing now?\n",
    "missing_plant_eia.plant_id_eia.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## See if there is a matching EIA plant_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "#find where there is a matching plant_id\n",
    "missing_plant_eia = missing_plant_eia.merge(plants_eia, how='left', left_on='plant_id_epa', right_on='plant_id_eia')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "      index                               plant_name  plant_id_epa  \\\n2196   2234                               Cumberland          3399   \n2197   2235                               Cumberland          3399   \n3175   3233           KapStone Charleston Kraft, LLC          7737   \n3176   3234           KapStone Charleston Kraft, LLC          7737   \n3177   3235           KapStone Charleston Kraft, LLC          7737   \n3773   3845  Gilroy Energy Center, LLC for King City         10294   \n3812   3884         Coastal Carolina Clean Power LLC         10381   \n3813   3885         Coastal Carolina Clean Power LLC         10381   \n3847   3919  ArcelorMittal USA - Indiana Harbor East         10474   \n3848   3920  ArcelorMittal USA - Indiana Harbor East         10474   \n3849   3921  ArcelorMittal USA - Indiana Harbor East         10474   \n3980   4058                     Chemours Belle Plant         10788   \n4057   4135               Archer Daniels Midland Co.         10865   \n4058   4136               Archer Daniels Midland Co.         10865   \n4122   4202                  Weyerhaeuser - New Bern         50188   \n4123   4203                  Weyerhaeuser - New Bern         50188   \n4161   4241           Blue Ridge Paper Products Inc.         50244   \n4221   4301            Kimberly-Clark Tissue Company         50410   \n4234   4314                          Covanta Niagara         50472   \n4239   4319                       GP Big Island, LLC         50479   \n4298   4378  Veolia Energy Philadelphia - Schuylkill         50607   \n4299   4379  Veolia Energy Philadelphia - Schuylkill         50607   \n4300   4380  Veolia Energy Philadelphia - Schuylkill         50607   \n4301   4381  Veolia Energy Philadelphia - Schuylkill         50607   \n4302   4382  Veolia Energy Philadelphia - Schuylkill         50607   \n4308   4388               Exxonmobil Oil Corporation         50627   \n4316   4396           Paulsboro Refining Company LLC         50628   \n4336   4416               US Steel (Fairfield Works)         50730   \n4337   4417               US Steel (Fairfield Works)         50730   \n4338   4418               US Steel (Fairfield Works)         50730   \n4339   4419               US Steel (Fairfield Works)         50730   \n4388   4468      MeadWestvaco of Virginia, Covington         50900   \n4389   4469      MeadWestvaco of Virginia, Covington         50900   \n4390   4470      MeadWestvaco of Virginia, Covington         50900   \n4391   4471      MeadWestvaco of Virginia, Covington         50900   \n4392   4472      MeadWestvaco of Virginia, Covington         50900   \n4393   4473      MeadWestvaco of Virginia, Covington         50900   \n4403   4483   Resolute Forest Products - Calhoun Ops         50956   \n4404   4484   Resolute Forest Products - Calhoun Ops         50956   \n4519   4599              Riverbay Corp. - Co-Op City         52168   \n4529   4609                   Delaware City Refinery         52193   \n4530   4610                   Delaware City Refinery         52193   \n4531   4611                   Delaware City Refinery         52193   \n4532   4612                   Delaware City Refinery         52193   \n4533   4613                   Delaware City Refinery         52193   \n4534   4614                   Delaware City Refinery         52193   \n4601   4681             University of NC Chapel Hill         54276   \n5152   5236                 Morris Cogeneration, LLC         55216   \n5153   5237                 Morris Cogeneration, LLC         55216   \n5524   5620                    DTE Calvert City, LLC         55308   \n5525   5621                    DTE Calvert City, LLC         55308   \n6050   6164   Valero Refining Company-Tennessee, LLC         55703   \n\n      plant_id_eia_x  unitid      generator_id boiler_id  \\\n2196             NaN      A1               NaN       NaN   \n2197             NaN      B1               NaN       NaN   \n3175             NaN    B002               NaN       NaN   \n3176             NaN    B003               NaN       NaN   \n3177             NaN    B004               NaN       NaN   \n3773             NaN       2               NaN       NaN   \n3812             NaN  BLR01A  Plant not in EIA       NaN   \n3813             NaN  BLR01B  Plant not in EIA       NaN   \n3847             NaN     211               NaN       NaN   \n3848             NaN     212               NaN       NaN   \n3849             NaN     213               NaN       NaN   \n3980             NaN     612  Plant not in EIA       NaN   \n4057             NaN     GB1               NaN       NaN   \n4058             NaN     GB2               NaN       NaN   \n4122             NaN     105               NaN       NaN   \n4123             NaN       6               NaN       NaN   \n4161             NaN      RC               GN8       RCB   \n4221             NaN     034               NaN       NaN   \n4234             NaN   BLR05               NaN       NaN   \n4239             NaN       6               NaN       NaN   \n4298             NaN      23  Plant not in EIA       NaN   \n4299             NaN      24  Plant not in EIA       NaN   \n4300             NaN      26  Plant not in EIA       NaN   \n4301             NaN    RSB1  Plant not in EIA       NaN   \n4302             NaN    RSB2  Plant not in EIA       NaN   \n4308             NaN  55B100               NaN       NaN   \n4316             NaN  780001               NaN       NaN   \n4336             NaN     206               NaN       NaN   \n4337             NaN     208               NaN       NaN   \n4338             NaN     209               NaN       NaN   \n4339             NaN     210               NaN       NaN   \n4388             NaN     001               NaN  1PB/1RB?   \n4389             NaN     002               NaN      2RB?   \n4390             NaN     003               NaN       NaN   \n4391             NaN     004               NaN       NaN   \n4392             NaN     005               NaN       NaN   \n4393             NaN     011               NaN     11PB?   \n4403             NaN      11               NaN       NaN   \n4404             NaN      12               NaN       NaN   \n4519             NaN   00001               NaN       NaN   \n4529             NaN    21H1               NaN       NaN   \n4530             NaN  21H701               NaN       NaN   \n4531             NaN    37H1               NaN       NaN   \n4532             NaN  42H123               NaN       NaN   \n4533             NaN  CATCOB               NaN       NaN   \n4534             NaN  COKCOB               NaN       NaN   \n4601             NaN   ES003               NaN       NaN   \n5152             NaN     B-5               NaN       NaN   \n5153             NaN     B-6               NaN       NaN   \n5524             NaN       A               NaN       NaN   \n5525             NaN       B               NaN       NaN   \n6050             NaN    P049  Plant not in EIA       NaN   \n\n         fuel_type_primary prime_mover_code  edat_capacity_mw  \\\n2196            Diesel Oil               ST             349.0   \n2197            Diesel Oil               ST             349.0   \n3175           Natural Gas               ST             500.0   \n3176           Natural Gas               ST             500.0   \n3177           Natural Gas               ST             500.0   \n3773  Pipeline Natural Gas               GT              47.0   \n3812                  Wood               ST             160.0   \n3813                  Wood               ST             160.0   \n3847           Process Gas               ST             200.0   \n3848           Process Gas               ST             200.0   \n3849           Process Gas               ST             200.0   \n3980           Natural Gas               ST             200.0   \n4057  Pipeline Natural Gas               ST             250.0   \n4058  Pipeline Natural Gas               ST             250.0   \n4122  Pipeline Natural Gas               ST             388.0   \n4123  Pipeline Natural Gas               ST             120.0   \n4161                  Coal               ST             300.0   \n4221  Pipeline Natural Gas               ST             249.0   \n4234  Pipeline Natural Gas               ST             225.0   \n4239  Pipeline Natural Gas               ST             200.0   \n4298          Residual Oil               ST             660.0   \n4299          Residual Oil               ST             660.0   \n4300          Residual Oil               ST             600.0   \n4301  Pipeline Natural Gas               ST             293.0   \n4302  Pipeline Natural Gas               ST             293.0   \n4308           Process Gas               ST             400.0   \n4316           Process Gas               ST               NaN   \n4336           Process Gas               ST             300.0   \n4337           Process Gas               ST             300.0   \n4338           Process Gas               ST             495.0   \n4339           Process Gas               ST             495.0   \n4388                  Coal               ST             390.0   \n4389                  Coal               ST             245.0   \n4390                  Coal               ST             355.0   \n4391                  Coal               ST             560.0   \n4392           Natural Gas               ST             225.0   \n4393           Natural Gas               ST             225.0   \n4403                  Coal               ST             385.0   \n4404                  Coal               ST             385.0   \n4519  Pipeline Natural Gas               ST             335.0   \n4529                   NaN               ST               NaN   \n4530           Process Gas               ST               NaN   \n4531           Process Gas               ST               NaN   \n4532           Process Gas               ST               NaN   \n4533           Process Gas               ST             547.0   \n4534           Process Gas               ST             501.0   \n4601  Pipeline Natural Gas               ST             250.0   \n5152  Pipeline Natural Gas               ST             225.0   \n5153  Pipeline Natural Gas               ST             250.0   \n5524  Pipeline Natural Gas               ST             250.0   \n5525  Pipeline Natural Gas               ST             250.0   \n6050           Process Gas               ST             600.0   \n\n      heat_input_mmbtu                          generator_id_match_method  \\\n2196         33879.500                                                NaN   \n2197         33216.100                                                NaN   \n3175         25583.300                                                NaN   \n3176         40862.600                                                NaN   \n3177         89275.800                                                NaN   \n3773         52704.141                                                NaN   \n3812               NaN                                   Plant not in EIA   \n3813               NaN                                   Plant not in EIA   \n3847               NaN                                                NaN   \n3848               NaN                                                NaN   \n3849               NaN                                                NaN   \n3980        539120.040                                   Plant not in EIA   \n4057        258723.151                                                NaN   \n4058        276998.069                                                NaN   \n4122         56426.945                                                NaN   \n4123       2277064.512                                                NaN   \n4161               NaN  Manual match for boiler id, 860 gen boiler x-w...   \n4221         53403.800                                                NaN   \n4234        250427.175                                                NaN   \n4239        485059.542                                                NaN   \n4298               NaN                                   Plant not in EIA   \n4299               NaN                                   Plant not in EIA   \n4300       1318941.441                                   Plant not in EIA   \n4301        223114.519                                   Plant not in EIA   \n4302        232713.427                                   Plant not in EIA   \n4308               NaN                                                NaN   \n4316               NaN                                                NaN   \n4336               NaN                                                NaN   \n4337               NaN                                                NaN   \n4338               NaN                                                NaN   \n4339               NaN                                                NaN   \n4388       2268210.957                                                NaN   \n4389               NaN                                                NaN   \n4390               NaN                                                NaN   \n4391       5907240.408                                                NaN   \n4392        553838.339                                                NaN   \n4393        549640.263                                                NaN   \n4403        234945.533                                                NaN   \n4404        148711.850                                                NaN   \n4519        406366.300                                                NaN   \n4529               NaN                                                NaN   \n4530               NaN                                                NaN   \n4531               NaN                                                NaN   \n4532               NaN                                                NaN   \n4533               NaN                                                NaN   \n4534               NaN                                                NaN   \n4601         16457.450                                                NaN   \n5152         28858.844                                                NaN   \n5153        110973.810                                                NaN   \n5524        333822.200                                                NaN   \n5525        329085.400                                                NaN   \n6050       1213754.893                                   Plant not in EIA   \n\n      op_status op_status_date  plant_id_eia_y  \\\n2196  Operating            NaN            3399   \n2197  Operating            NaN            3399   \n3175  Operating            NaN            7737   \n3176  Operating            NaN            7737   \n3177  Operating            NaN            7737   \n3773  Operating            NaN           10294   \n3812  Operating            NaN           10381   \n3813  Operating            NaN           10381   \n3847  Operating            NaN           10474   \n3848  Operating            NaN           10474   \n3849  Operating            NaN           10474   \n3980  Operating            NaN           10788   \n4057  Operating            NaN           10865   \n4058  Operating            NaN           10865   \n4122  Operating            NaN           50188   \n4123  Operating            NaN           50188   \n4161  Operating            NaN           50244   \n4221  Operating            NaN           50410   \n4234  Operating            NaN           50472   \n4239  Operating            NaN           50479   \n4298  Operating            NaN           50607   \n4299  Operating            NaN           50607   \n4300  Operating            NaN           50607   \n4301  Operating            NaN           50607   \n4302  Operating            NaN           50607   \n4308  Operating            NaN           50627   \n4316  Operating            NaN           50628   \n4336  Operating            NaN           50730   \n4337  Operating            NaN           50730   \n4338  Operating            NaN           50730   \n4339  Operating            NaN           50730   \n4388  Operating            NaN           50900   \n4389  Operating            NaN           50900   \n4390  Operating            NaN           50900   \n4391  Operating            NaN           50900   \n4392  Operating            NaN           50900   \n4393  Operating            NaN           50900   \n4403  Operating            NaN           50956   \n4404  Operating            NaN           50956   \n4519  Operating            NaN           52168   \n4529  Operating            NaN           52193   \n4530  Operating            NaN           52193   \n4531  Operating            NaN           52193   \n4532  Operating            NaN           52193   \n4533  Operating            NaN           52193   \n4534  Operating            NaN           52193   \n4601  Operating            NaN           54276   \n5152  Operating            NaN           55216   \n5153  Operating            NaN           55216   \n5524  Operating            NaN           55308   \n5525  Operating            NaN           55308   \n6050  Operating            NaN           55703   \n\n                             plant_name_eia  \n2196                        Cumberland (TN)  \n2197                        Cumberland (TN)  \n3175                               Kapstone  \n3176                               Kapstone  \n3177                               Kapstone  \n3773                  King City Power Plant  \n3812           Coastal Carolina Clean Power  \n3813           Coastal Carolina Clean Power  \n3847                           4 AC Station  \n3848                           4 AC Station  \n3849                           4 AC Station  \n3980              Belle West Virginia Plant  \n4057         Archer Daniels Midland Decatur  \n4058         Archer Daniels Midland Decatur  \n4122                                    NaN  \n4123                                    NaN  \n4161                  Canton North Carolina  \n4221                     Chester Operations  \n4234                                    NaN  \n4239             Georgia-Pacific Big Island  \n4298             Trigen Energy - Schuylkill  \n4299             Trigen Energy - Schuylkill  \n4300             Trigen Energy - Schuylkill  \n4301             Trigen Energy - Schuylkill  \n4302             Trigen Energy - Schuylkill  \n4308         ExxonMobil Oil Joliet Refinery  \n4316                     Paulsboro Refinery  \n4336                        Fairfield Works  \n4337                        Fairfield Works  \n4338                        Fairfield Works  \n4339                        Fairfield Works  \n4388                     Covington Facility  \n4389                     Covington Facility  \n4390                     Covington Facility  \n4391                     Covington Facility  \n4392                     Covington Facility  \n4393                     Covington Facility  \n4403    Bowater Newsprint Calhoun Operation  \n4404    Bowater Newsprint Calhoun Operation  \n4519                               Riverbay  \n4529                    Delaware City Plant  \n4530                    Delaware City Plant  \n4531                    Delaware City Plant  \n4532                    Delaware City Plant  \n4533                    Delaware City Plant  \n4534                    Delaware City Plant  \n4601  Univ of NC Chapel Hill Cogen Facility  \n5152                Morris Cogeneration LLC  \n5153                Morris Cogeneration LLC  \n5524                           Calvert City  \n5525                           Calvert City  \n6050                       Memphis Refinery  ",
      "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>index</th>\n      <th>plant_name</th>\n      <th>plant_id_epa</th>\n      <th>plant_id_eia_x</th>\n      <th>unitid</th>\n      <th>generator_id</th>\n      <th>boiler_id</th>\n      <th>fuel_type_primary</th>\n      <th>prime_mover_code</th>\n      <th>edat_capacity_mw</th>\n      <th>heat_input_mmbtu</th>\n      <th>generator_id_match_method</th>\n      <th>op_status</th>\n      <th>op_status_date</th>\n      <th>plant_id_eia_y</th>\n      <th>plant_name_eia</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>2196</th>\n      <td>2234</td>\n      <td>Cumberland</td>\n      <td>3399</td>\n      <td>NaN</td>\n      <td>A1</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Diesel Oil</td>\n      <td>ST</td>\n      <td>349.0</td>\n      <td>33879.500</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>3399</td>\n      <td>Cumberland (TN)</td>\n    </tr>\n    <tr>\n      <th>2197</th>\n      <td>2235</td>\n      <td>Cumberland</td>\n      <td>3399</td>\n      <td>NaN</td>\n      <td>B1</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Diesel Oil</td>\n      <td>ST</td>\n      <td>349.0</td>\n      <td>33216.100</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>3399</td>\n      <td>Cumberland (TN)</td>\n    </tr>\n    <tr>\n      <th>3175</th>\n      <td>3233</td>\n      <td>KapStone Charleston Kraft, LLC</td>\n      <td>7737</td>\n      <td>NaN</td>\n      <td>B002</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Natural Gas</td>\n      <td>ST</td>\n      <td>500.0</td>\n      <td>25583.300</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>7737</td>\n      <td>Kapstone</td>\n    </tr>\n    <tr>\n      <th>3176</th>\n      <td>3234</td>\n      <td>KapStone Charleston Kraft, LLC</td>\n      <td>7737</td>\n      <td>NaN</td>\n      <td>B003</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Natural Gas</td>\n      <td>ST</td>\n      <td>500.0</td>\n      <td>40862.600</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>7737</td>\n      <td>Kapstone</td>\n    </tr>\n    <tr>\n      <th>3177</th>\n      <td>3235</td>\n      <td>KapStone Charleston Kraft, LLC</td>\n      <td>7737</td>\n      <td>NaN</td>\n      <td>B004</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Natural Gas</td>\n      <td>ST</td>\n      <td>500.0</td>\n      <td>89275.800</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>7737</td>\n      <td>Kapstone</td>\n    </tr>\n    <tr>\n      <th>3773</th>\n      <td>3845</td>\n      <td>Gilroy Energy Center, LLC for King City</td>\n      <td>10294</td>\n      <td>NaN</td>\n      <td>2</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Pipeline Natural Gas</td>\n      <td>GT</td>\n      <td>47.0</td>\n      <td>52704.141</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>10294</td>\n      <td>King City Power Plant</td>\n    </tr>\n    <tr>\n      <th>3812</th>\n      <td>3884</td>\n      <td>Coastal Carolina Clean Power LLC</td>\n      <td>10381</td>\n      <td>NaN</td>\n      <td>BLR01A</td>\n      <td>Plant not in EIA</td>\n      <td>NaN</td>\n      <td>Wood</td>\n      <td>ST</td>\n      <td>160.0</td>\n      <td>NaN</td>\n      <td>Plant not in EIA</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>10381</td>\n      <td>Coastal Carolina Clean Power</td>\n    </tr>\n    <tr>\n      <th>3813</th>\n      <td>3885</td>\n      <td>Coastal Carolina Clean Power LLC</td>\n      <td>10381</td>\n      <td>NaN</td>\n      <td>BLR01B</td>\n      <td>Plant not in EIA</td>\n      <td>NaN</td>\n      <td>Wood</td>\n      <td>ST</td>\n      <td>160.0</td>\n      <td>NaN</td>\n      <td>Plant not in EIA</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>10381</td>\n      <td>Coastal Carolina Clean Power</td>\n    </tr>\n    <tr>\n      <th>3847</th>\n      <td>3919</td>\n      <td>ArcelorMittal USA - Indiana Harbor East</td>\n      <td>10474</td>\n      <td>NaN</td>\n      <td>211</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Process Gas</td>\n      <td>ST</td>\n      <td>200.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>10474</td>\n      <td>4 AC Station</td>\n    </tr>\n    <tr>\n      <th>3848</th>\n      <td>3920</td>\n      <td>ArcelorMittal USA - Indiana Harbor East</td>\n      <td>10474</td>\n      <td>NaN</td>\n      <td>212</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Process Gas</td>\n      <td>ST</td>\n      <td>200.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>10474</td>\n      <td>4 AC Station</td>\n    </tr>\n    <tr>\n      <th>3849</th>\n      <td>3921</td>\n      <td>ArcelorMittal USA - Indiana Harbor East</td>\n      <td>10474</td>\n      <td>NaN</td>\n      <td>213</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Process Gas</td>\n      <td>ST</td>\n      <td>200.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>10474</td>\n      <td>4 AC Station</td>\n    </tr>\n    <tr>\n      <th>3980</th>\n      <td>4058</td>\n      <td>Chemours Belle Plant</td>\n      <td>10788</td>\n      <td>NaN</td>\n      <td>612</td>\n      <td>Plant not in EIA</td>\n      <td>NaN</td>\n      <td>Natural Gas</td>\n      <td>ST</td>\n      <td>200.0</td>\n      <td>539120.040</td>\n      <td>Plant not in EIA</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>10788</td>\n      <td>Belle West Virginia Plant</td>\n    </tr>\n    <tr>\n      <th>4057</th>\n      <td>4135</td>\n      <td>Archer Daniels Midland Co.</td>\n      <td>10865</td>\n      <td>NaN</td>\n      <td>GB1</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>250.0</td>\n      <td>258723.151</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>10865</td>\n      <td>Archer Daniels Midland Decatur</td>\n    </tr>\n    <tr>\n      <th>4058</th>\n      <td>4136</td>\n      <td>Archer Daniels Midland Co.</td>\n      <td>10865</td>\n      <td>NaN</td>\n      <td>GB2</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>250.0</td>\n      <td>276998.069</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>10865</td>\n      <td>Archer Daniels Midland Decatur</td>\n    </tr>\n    <tr>\n      <th>4122</th>\n      <td>4202</td>\n      <td>Weyerhaeuser - New Bern</td>\n      <td>50188</td>\n      <td>NaN</td>\n      <td>105</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>388.0</td>\n      <td>56426.945</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>50188</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>4123</th>\n      <td>4203</td>\n      <td>Weyerhaeuser - New Bern</td>\n      <td>50188</td>\n      <td>NaN</td>\n      <td>6</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>120.0</td>\n      <td>2277064.512</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>50188</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>4161</th>\n      <td>4241</td>\n      <td>Blue Ridge Paper Products Inc.</td>\n      <td>50244</td>\n      <td>NaN</td>\n      <td>RC</td>\n      <td>GN8</td>\n      <td>RCB</td>\n      <td>Coal</td>\n      <td>ST</td>\n      <td>300.0</td>\n      <td>NaN</td>\n      <td>Manual match for boiler id, 860 gen boiler x-w...</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>50244</td>\n      <td>Canton North Carolina</td>\n    </tr>\n    <tr>\n      <th>4221</th>\n      <td>4301</td>\n      <td>Kimberly-Clark Tissue Company</td>\n      <td>50410</td>\n      <td>NaN</td>\n      <td>034</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>249.0</td>\n      <td>53403.800</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>50410</td>\n      <td>Chester Operations</td>\n    </tr>\n    <tr>\n      <th>4234</th>\n      <td>4314</td>\n      <td>Covanta Niagara</td>\n      <td>50472</td>\n      <td>NaN</td>\n      <td>BLR05</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>225.0</td>\n      <td>250427.175</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>50472</td>\n      <td>NaN</td>\n    </tr>\n    <tr>\n      <th>4239</th>\n      <td>4319</td>\n      <td>GP Big Island, LLC</td>\n      <td>50479</td>\n      <td>NaN</td>\n      <td>6</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>200.0</td>\n      <td>485059.542</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>50479</td>\n      <td>Georgia-Pacific Big Island</td>\n    </tr>\n    <tr>\n      <th>4298</th>\n      <td>4378</td>\n      <td>Veolia Energy Philadelphia - Schuylkill</td>\n      <td>50607</td>\n      <td>NaN</td>\n      <td>23</td>\n      <td>Plant not in EIA</td>\n      <td>NaN</td>\n      <td>Residual Oil</td>\n      <td>ST</td>\n      <td>660.0</td>\n      <td>NaN</td>\n      <td>Plant not in EIA</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>50607</td>\n      <td>Trigen Energy - Schuylkill</td>\n    </tr>\n    <tr>\n      <th>4299</th>\n      <td>4379</td>\n      <td>Veolia Energy Philadelphia - Schuylkill</td>\n      <td>50607</td>\n      <td>NaN</td>\n      <td>24</td>\n      <td>Plant not in EIA</td>\n      <td>NaN</td>\n      <td>Residual Oil</td>\n      <td>ST</td>\n      <td>660.0</td>\n      <td>NaN</td>\n      <td>Plant not in EIA</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>50607</td>\n      <td>Trigen Energy - Schuylkill</td>\n    </tr>\n    <tr>\n      <th>4300</th>\n      <td>4380</td>\n      <td>Veolia Energy Philadelphia - Schuylkill</td>\n      <td>50607</td>\n      <td>NaN</td>\n      <td>26</td>\n      <td>Plant not in EIA</td>\n      <td>NaN</td>\n      <td>Residual Oil</td>\n      <td>ST</td>\n      <td>600.0</td>\n      <td>1318941.441</td>\n      <td>Plant not in EIA</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>50607</td>\n      <td>Trigen Energy - Schuylkill</td>\n    </tr>\n    <tr>\n      <th>4301</th>\n      <td>4381</td>\n      <td>Veolia Energy Philadelphia - Schuylkill</td>\n      <td>50607</td>\n      <td>NaN</td>\n      <td>RSB1</td>\n      <td>Plant not in EIA</td>\n      <td>NaN</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>293.0</td>\n      <td>223114.519</td>\n      <td>Plant not in EIA</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>50607</td>\n      <td>Trigen Energy - Schuylkill</td>\n    </tr>\n    <tr>\n      <th>4302</th>\n      <td>4382</td>\n      <td>Veolia Energy Philadelphia - Schuylkill</td>\n      <td>50607</td>\n      <td>NaN</td>\n      <td>RSB2</td>\n      <td>Plant not in EIA</td>\n      <td>NaN</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>293.0</td>\n      <td>232713.427</td>\n      <td>Plant not in EIA</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>50607</td>\n      <td>Trigen Energy - Schuylkill</td>\n    </tr>\n    <tr>\n      <th>4308</th>\n      <td>4388</td>\n      <td>Exxonmobil Oil Corporation</td>\n      <td>50627</td>\n      <td>NaN</td>\n      <td>55B100</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Process Gas</td>\n      <td>ST</td>\n      <td>400.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>50627</td>\n      <td>ExxonMobil Oil Joliet Refinery</td>\n    </tr>\n    <tr>\n      <th>4316</th>\n      <td>4396</td>\n      <td>Paulsboro Refining Company LLC</td>\n      <td>50628</td>\n      <td>NaN</td>\n      <td>780001</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Process Gas</td>\n      <td>ST</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>50628</td>\n      <td>Paulsboro Refinery</td>\n    </tr>\n    <tr>\n      <th>4336</th>\n      <td>4416</td>\n      <td>US Steel (Fairfield Works)</td>\n      <td>50730</td>\n      <td>NaN</td>\n      <td>206</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Process Gas</td>\n      <td>ST</td>\n      <td>300.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>50730</td>\n      <td>Fairfield Works</td>\n    </tr>\n    <tr>\n      <th>4337</th>\n      <td>4417</td>\n      <td>US Steel (Fairfield Works)</td>\n      <td>50730</td>\n      <td>NaN</td>\n      <td>208</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Process Gas</td>\n      <td>ST</td>\n      <td>300.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>50730</td>\n      <td>Fairfield Works</td>\n    </tr>\n    <tr>\n      <th>4338</th>\n      <td>4418</td>\n      <td>US Steel (Fairfield Works)</td>\n      <td>50730</td>\n      <td>NaN</td>\n      <td>209</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Process Gas</td>\n      <td>ST</td>\n      <td>495.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>50730</td>\n      <td>Fairfield Works</td>\n    </tr>\n    <tr>\n      <th>4339</th>\n      <td>4419</td>\n      <td>US Steel (Fairfield Works)</td>\n      <td>50730</td>\n      <td>NaN</td>\n      <td>210</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Process Gas</td>\n      <td>ST</td>\n      <td>495.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>50730</td>\n      <td>Fairfield Works</td>\n    </tr>\n    <tr>\n      <th>4388</th>\n      <td>4468</td>\n      <td>MeadWestvaco of Virginia, Covington</td>\n      <td>50900</td>\n      <td>NaN</td>\n      <td>001</td>\n      <td>NaN</td>\n      <td>1PB/1RB?</td>\n      <td>Coal</td>\n      <td>ST</td>\n      <td>390.0</td>\n      <td>2268210.957</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>50900</td>\n      <td>Covington Facility</td>\n    </tr>\n    <tr>\n      <th>4389</th>\n      <td>4469</td>\n      <td>MeadWestvaco of Virginia, Covington</td>\n      <td>50900</td>\n      <td>NaN</td>\n      <td>002</td>\n      <td>NaN</td>\n      <td>2RB?</td>\n      <td>Coal</td>\n      <td>ST</td>\n      <td>245.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>50900</td>\n      <td>Covington Facility</td>\n    </tr>\n    <tr>\n      <th>4390</th>\n      <td>4470</td>\n      <td>MeadWestvaco of Virginia, Covington</td>\n      <td>50900</td>\n      <td>NaN</td>\n      <td>003</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Coal</td>\n      <td>ST</td>\n      <td>355.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>50900</td>\n      <td>Covington Facility</td>\n    </tr>\n    <tr>\n      <th>4391</th>\n      <td>4471</td>\n      <td>MeadWestvaco of Virginia, Covington</td>\n      <td>50900</td>\n      <td>NaN</td>\n      <td>004</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Coal</td>\n      <td>ST</td>\n      <td>560.0</td>\n      <td>5907240.408</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>50900</td>\n      <td>Covington Facility</td>\n    </tr>\n    <tr>\n      <th>4392</th>\n      <td>4472</td>\n      <td>MeadWestvaco of Virginia, Covington</td>\n      <td>50900</td>\n      <td>NaN</td>\n      <td>005</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Natural Gas</td>\n      <td>ST</td>\n      <td>225.0</td>\n      <td>553838.339</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>50900</td>\n      <td>Covington Facility</td>\n    </tr>\n    <tr>\n      <th>4393</th>\n      <td>4473</td>\n      <td>MeadWestvaco of Virginia, Covington</td>\n      <td>50900</td>\n      <td>NaN</td>\n      <td>011</td>\n      <td>NaN</td>\n      <td>11PB?</td>\n      <td>Natural Gas</td>\n      <td>ST</td>\n      <td>225.0</td>\n      <td>549640.263</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>50900</td>\n      <td>Covington Facility</td>\n    </tr>\n    <tr>\n      <th>4403</th>\n      <td>4483</td>\n      <td>Resolute Forest Products - Calhoun Ops</td>\n      <td>50956</td>\n      <td>NaN</td>\n      <td>11</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Coal</td>\n      <td>ST</td>\n      <td>385.0</td>\n      <td>234945.533</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>50956</td>\n      <td>Bowater Newsprint Calhoun Operation</td>\n    </tr>\n    <tr>\n      <th>4404</th>\n      <td>4484</td>\n      <td>Resolute Forest Products - Calhoun Ops</td>\n      <td>50956</td>\n      <td>NaN</td>\n      <td>12</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Coal</td>\n      <td>ST</td>\n      <td>385.0</td>\n      <td>148711.850</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>50956</td>\n      <td>Bowater Newsprint Calhoun Operation</td>\n    </tr>\n    <tr>\n      <th>4519</th>\n      <td>4599</td>\n      <td>Riverbay Corp. - Co-Op City</td>\n      <td>52168</td>\n      <td>NaN</td>\n      <td>00001</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>335.0</td>\n      <td>406366.300</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>52168</td>\n      <td>Riverbay</td>\n    </tr>\n    <tr>\n      <th>4529</th>\n      <td>4609</td>\n      <td>Delaware City Refinery</td>\n      <td>52193</td>\n      <td>NaN</td>\n      <td>21H1</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>ST</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>52193</td>\n      <td>Delaware City Plant</td>\n    </tr>\n    <tr>\n      <th>4530</th>\n      <td>4610</td>\n      <td>Delaware City Refinery</td>\n      <td>52193</td>\n      <td>NaN</td>\n      <td>21H701</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Process Gas</td>\n      <td>ST</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>52193</td>\n      <td>Delaware City Plant</td>\n    </tr>\n    <tr>\n      <th>4531</th>\n      <td>4611</td>\n      <td>Delaware City Refinery</td>\n      <td>52193</td>\n      <td>NaN</td>\n      <td>37H1</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Process Gas</td>\n      <td>ST</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>52193</td>\n      <td>Delaware City Plant</td>\n    </tr>\n    <tr>\n      <th>4532</th>\n      <td>4612</td>\n      <td>Delaware City Refinery</td>\n      <td>52193</td>\n      <td>NaN</td>\n      <td>42H123</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Process Gas</td>\n      <td>ST</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>52193</td>\n      <td>Delaware City Plant</td>\n    </tr>\n    <tr>\n      <th>4533</th>\n      <td>4613</td>\n      <td>Delaware City Refinery</td>\n      <td>52193</td>\n      <td>NaN</td>\n      <td>CATCOB</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Process Gas</td>\n      <td>ST</td>\n      <td>547.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>52193</td>\n      <td>Delaware City Plant</td>\n    </tr>\n    <tr>\n      <th>4534</th>\n      <td>4614</td>\n      <td>Delaware City Refinery</td>\n      <td>52193</td>\n      <td>NaN</td>\n      <td>COKCOB</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Process Gas</td>\n      <td>ST</td>\n      <td>501.0</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>52193</td>\n      <td>Delaware City Plant</td>\n    </tr>\n    <tr>\n      <th>4601</th>\n      <td>4681</td>\n      <td>University of NC Chapel Hill</td>\n      <td>54276</td>\n      <td>NaN</td>\n      <td>ES003</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>250.0</td>\n      <td>16457.450</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>54276</td>\n      <td>Univ of NC Chapel Hill Cogen Facility</td>\n    </tr>\n    <tr>\n      <th>5152</th>\n      <td>5236</td>\n      <td>Morris Cogeneration, LLC</td>\n      <td>55216</td>\n      <td>NaN</td>\n      <td>B-5</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>225.0</td>\n      <td>28858.844</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>55216</td>\n      <td>Morris Cogeneration LLC</td>\n    </tr>\n    <tr>\n      <th>5153</th>\n      <td>5237</td>\n      <td>Morris Cogeneration, LLC</td>\n      <td>55216</td>\n      <td>NaN</td>\n      <td>B-6</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>250.0</td>\n      <td>110973.810</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>55216</td>\n      <td>Morris Cogeneration LLC</td>\n    </tr>\n    <tr>\n      <th>5524</th>\n      <td>5620</td>\n      <td>DTE Calvert City, LLC</td>\n      <td>55308</td>\n      <td>NaN</td>\n      <td>A</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>250.0</td>\n      <td>333822.200</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>55308</td>\n      <td>Calvert City</td>\n    </tr>\n    <tr>\n      <th>5525</th>\n      <td>5621</td>\n      <td>DTE Calvert City, LLC</td>\n      <td>55308</td>\n      <td>NaN</td>\n      <td>B</td>\n      <td>NaN</td>\n      <td>NaN</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>250.0</td>\n      <td>329085.400</td>\n      <td>NaN</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>55308</td>\n      <td>Calvert City</td>\n    </tr>\n    <tr>\n      <th>6050</th>\n      <td>6164</td>\n      <td>Valero Refining Company-Tennessee, LLC</td>\n      <td>55703</td>\n      <td>NaN</td>\n      <td>P049</td>\n      <td>Plant not in EIA</td>\n      <td>NaN</td>\n      <td>Process Gas</td>\n      <td>ST</td>\n      <td>600.0</td>\n      <td>1213754.893</td>\n      <td>Plant not in EIA</td>\n      <td>Operating</td>\n      <td>NaN</td>\n      <td>55703</td>\n      <td>Memphis Refinery</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 90
    }
   ],
   "source": [
    "#what values are still missing?\n",
    "pd.set_option('display.max_rows',200)\n",
    "missing_plant_eia.query('plant_id_eia_x == \"NaN\"')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "0"
     },
     "metadata": {},
     "execution_count": 91
    }
   ],
   "source": [
    "missing_plant_eia.plant_id_eia_x.fillna(missing_plant_eia.plant_id_eia_y, inplace=True) #if a match was found, fill any missing values with that id\n",
    "\n",
    "#let's do some cleanup\n",
    "missing_plant_eia.drop(columns=['plant_id_eia_y','plant_name_eia'],inplace=True)\n",
    "missing_plant_eia.rename(columns={'plant_id_eia_x':'plant_id_eia'}, inplace=True)\n",
    "\n",
    "#how many are missing now?\n",
    "missing_plant_eia.plant_id_eia.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Replace missing values from original dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "      plant_name  plant_id_epa  plant_id_eia unitid generator_id boiler_id  \\\nindex                                                                        \n0          Barry             3           3.0      1            1         1   \n1          Barry             3           3.0      2            2         2   \n2          Barry             3           3.0      4            4         4   \n3          Barry             3           3.0      5            5         5   \n4          Barry             3           3.0     6A         A1ST        6A   \n\n          fuel_type_primary prime_mover_code  edat_capacity_mw  \\\nindex                                                            \n0      Pipeline Natural Gas               ST             180.0   \n1      Pipeline Natural Gas               ST             180.0   \n2                      Coal               ST             400.0   \n3                      Coal               ST             800.0   \n4      Pipeline Natural Gas               CT             312.0   \n\n       heat_input_mmbtu generator_id_match_method op_status op_status_date  \nindex                                                                       \n0          1.847805e+05        X-walk from Travis       OPR   Feb/11/1954   \n1          1.888817e+05        X-walk from Travis       OPR   Jun/18/1954   \n2          1.224252e+07        X-walk from Travis       OPR   May/28/1969   \n3          3.322478e+07        X-walk from Travis       OPR   Jul/24/1971   \n4          1.514752e+07        X-walk from Travis       OPR   Feb/22/2000   ",
      "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>plant_name</th>\n      <th>plant_id_epa</th>\n      <th>plant_id_eia</th>\n      <th>unitid</th>\n      <th>generator_id</th>\n      <th>boiler_id</th>\n      <th>fuel_type_primary</th>\n      <th>prime_mover_code</th>\n      <th>edat_capacity_mw</th>\n      <th>heat_input_mmbtu</th>\n      <th>generator_id_match_method</th>\n      <th>op_status</th>\n      <th>op_status_date</th>\n    </tr>\n    <tr>\n      <th>index</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></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>0</th>\n      <td>Barry</td>\n      <td>3</td>\n      <td>3.0</td>\n      <td>1</td>\n      <td>1</td>\n      <td>1</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>180.0</td>\n      <td>1.847805e+05</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Feb/11/1954</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>Barry</td>\n      <td>3</td>\n      <td>3.0</td>\n      <td>2</td>\n      <td>2</td>\n      <td>2</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>180.0</td>\n      <td>1.888817e+05</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Jun/18/1954</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>Barry</td>\n      <td>3</td>\n      <td>3.0</td>\n      <td>4</td>\n      <td>4</td>\n      <td>4</td>\n      <td>Coal</td>\n      <td>ST</td>\n      <td>400.0</td>\n      <td>1.224252e+07</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>May/28/1969</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>Barry</td>\n      <td>3</td>\n      <td>3.0</td>\n      <td>5</td>\n      <td>5</td>\n      <td>5</td>\n      <td>Coal</td>\n      <td>ST</td>\n      <td>800.0</td>\n      <td>3.322478e+07</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Jul/24/1971</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>Barry</td>\n      <td>3</td>\n      <td>3.0</td>\n      <td>6A</td>\n      <td>A1ST</td>\n      <td>6A</td>\n      <td>Pipeline Natural Gas</td>\n      <td>CT</td>\n      <td>312.0</td>\n      <td>1.514752e+07</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Feb/22/2000</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 92
    }
   ],
   "source": [
    "missing_plant_eia.set_index('index', drop=True, inplace=True)\n",
    "missing_plant_eia.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.update(missing_plant_eia)\n",
    "df.drop(columns=\"index\", inplace=True)\n",
    ""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Standardize fuel_type_primary column (TODO)\n",
    "Change fuel type description to the standard EIA fuel type code"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1) Replace unambiguous fuel type descriptions with EIA code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "Pipeline Natural Gas    3988\nCoal                     923\nDiesel Oil               430\nProcess Gas              120\nNatural Gas              118\nOther Gas                 44\nWood                      43\nResidual Oil              38\nOther Oil                 25\nCoal Refuse               14\nPetroleum Coke             9\nTire Derived Fuel          2\nOther Solid Fuel           2\nName: fuel_type_primary, dtype: int64"
     },
     "metadata": {},
     "execution_count": 94
    }
   ],
   "source": [
    "#how common is each fuel type?\n",
    "df.fuel_type_primary.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "NG     4106\nCBL     923\nDFO     430\nOG      164\nWDS      43\nRFO      38\nWO       25\nWC       14\nPC        9\nTDF       2\nOBS       2\nName: fuel_type_code, dtype: int64"
     },
     "metadata": {},
     "execution_count": 95
    }
   ],
   "source": [
    "fuel_type_dict = {'Pipeline Natural Gas':'NG', #natural gas\n",
    "                  'Coal':'CBL', #Coal, blended\n",
    "                  'Natural Gas':'NG', #natural gas\n",
    "                  'Other Oil':'WO', #Waste/Other Oil. \n",
    "                  'Residual Oil':'RFO', #Residual Fuel Oil. Including No. 5 & 6 fuel oils and bunker C fuel oil.\n",
    "                  'Diesel Oil':'DFO', #Distillate Fuel Oil. Including diesel, No. 1, No. 2, and No. 4 fuel oils.\n",
    "                  'Wood':'WDS', #Wood/Wood Waste Solids. Including paper pellets, railroad ties, utility polies, wood chips, bark, and other wood waste solids.\n",
    "                  'Other Gas':'OG', #Other Gas\n",
    "                  'Process Gas':'OG', #Other Gas\n",
    "                  'Petroleum Coke':'PC', #Petroleum Coke\n",
    "                  'Coal Refuse':'WC', #Waste/Other Coal. Including anthracite culm, bituminous gob, fine coal, lignite waste, waste coal.\n",
    "                  'Other Solid Fuel':'OBS', #Other Biomass Solids\n",
    "                  'Tire Derived Fuel':'TDF' #Tire-derived Fuels\n",
    "                  }\n",
    "df['fuel_type_code'] = df.fuel_type_primary #create a new column for the fuel type code\n",
    "df.fuel_type_code.replace(fuel_type_dict, inplace=True) #replace the unambiguous fuel types with the fuel type code\n",
    "df.fuel_type_code.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2) Check if Fuel Type Codes are correct (TODO)\n",
    "Cross reference with EIA data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "   plant_id_eia generator_id  report_date energy_source_code_1\n0         62940           PV         2018                  SUN\n1         62939        41001         2018                  WND\n2         62938        39001         2018                  WND",
      "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>plant_id_eia</th>\n      <th>generator_id</th>\n      <th>report_date</th>\n      <th>energy_source_code_1</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>62940</td>\n      <td>PV</td>\n      <td>2018</td>\n      <td>SUN</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>62939</td>\n      <td>41001</td>\n      <td>2018</td>\n      <td>WND</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>62938</td>\n      <td>39001</td>\n      <td>2018</td>\n      <td>WND</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 96
    }
   ],
   "source": [
    "#import eia data\n",
    "eia_file = Path.cwd() / '../../PUDL/datapkg/pudl-data-release/pudl-eia860-eia923/data/generators_eia860.csv'\n",
    "\n",
    "eia_data = pd.read_csv(eia_file, usecols=['plant_id_eia','generator_id','report_date','energy_source_code_1'])\n",
    "\n",
    "eia_data['report_date'] = (eia_data['report_date'].str[:4]).astype('int32')\n",
    "eia_data.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "'NG'"
     },
     "metadata": {},
     "execution_count": 97
    }
   ],
   "source": [
    "eia_data.query('plant_id_eia == 3 and generator_id == \"2\"')['energy_source_code_1'].iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [],
   "source": [
    "#define lookup function\n",
    "def fuel_code_lookup(row, eia_data):\n",
    "    plant_id_eia = row['plant_id_eia']\n",
    "    generator_id = row['generator_id']\n",
    "\n",
    "    try:\n",
    "        fuel_type = eia_data.query('plant_id_eia == @plant_id_eia and generator_id == @generator_id')['energy_source_code_1'].iloc[0]\n",
    "    except IndexError:\n",
    "        fuel_type = None\n",
    "    return fuel_type\n",
    ""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": "                            plant_name  plant_id_epa  plant_id_eia  unitid  \\\n2737                    Trimble County        6071.0        6071.0      10   \n4358        Bayonne Plant Holding, LLC       50497.0       50497.0  001001   \n1791     Ravenswood Generating Station        2500.0        2500.0  CT03-1   \n2051                      Southwestern        2964.0        2964.0    8003   \n349                         Fort Myers         612.0         612.0   GFM09   \n4357  Axiall Corporation Natrium Plant       50491.0       50491.0     003   \n6625             Panoche Energy Center       56803.0       56803.0       4   \n4804                Milford Power, LLC       54805.0       54805.0       1   \n371                         Lauderdale         613.0         613.0   GFL09   \n5989             Frank Knutson Station       55505.0       55505.0     BR1   \n\n     generator_id boiler_id     fuel_type_primary prime_mover_code  \\\n2737           10       NaN  Pipeline Natural Gas               GT   \n4358         GTG1       NaN  Pipeline Natural Gas               CT   \n1791         GT31       NaN  Pipeline Natural Gas               GT   \n2051            3      8003  Pipeline Natural Gas               ST   \n349             9       NaN            Diesel Oil               GT   \n4357         GEN7       NaN                  Coal               ST   \n6625            4       NaN  Pipeline Natural Gas               GT   \n4804         ST-1       NaN  Pipeline Natural Gas               CT   \n371             9       NaN  Pipeline Natural Gas               GT   \n5989          BR1       NaN  Pipeline Natural Gas               GT   \n\n      edat_capacity_mw  heat_input_mmbtu generator_id_match_method op_status  \\\n2737             200.0       1811507.313        X-walk from Travis       OPR   \n4358              48.0       1087315.653        X-walk from Travis       OPR   \n1791              50.0         26633.500        X-walk from Travis       OPR   \n2051             340.0       4658275.471        X-walk from Travis       OPR   \n349               63.0         27725.200        X-walk from Travis       OPR   \n4357             785.0       7056515.550        X-walk from Travis       OPR   \n6625             110.0        828986.732        X-walk from Travis       OPR   \n4804             129.0        799262.946        X-walk from Travis       OPR   \n371               42.0         18939.200        X-walk from Travis       OPR   \n5989              90.0        127505.845        X-walk from Travis       OPR   \n\n     op_status_date fuel_type_code fuel_type_eia  \n2737   Apr/06/2004              NG            NG  \n4358   Sep/15/1988              NG            NG  \n1791   Aug/01/1970              NG            NG  \n2051   May/01/1967              NG            NG  \n349    Apr/03/1974             DFO           DFO  \n4357   Jun/01/1966             CBL            NG  \n6625   Apr/01/2009              NG            NG  \n4804   May/01/1993              NG            NG  \n371    Aug/01/1970              NG            NG  \n5989   Apr/09/2002              NG            NG  ",
      "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>plant_name</th>\n      <th>plant_id_epa</th>\n      <th>plant_id_eia</th>\n      <th>unitid</th>\n      <th>generator_id</th>\n      <th>boiler_id</th>\n      <th>fuel_type_primary</th>\n      <th>prime_mover_code</th>\n      <th>edat_capacity_mw</th>\n      <th>heat_input_mmbtu</th>\n      <th>generator_id_match_method</th>\n      <th>op_status</th>\n      <th>op_status_date</th>\n      <th>fuel_type_code</th>\n      <th>fuel_type_eia</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>2737</th>\n      <td>Trimble County</td>\n      <td>6071.0</td>\n      <td>6071.0</td>\n      <td>10</td>\n      <td>10</td>\n      <td>NaN</td>\n      <td>Pipeline Natural Gas</td>\n      <td>GT</td>\n      <td>200.0</td>\n      <td>1811507.313</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Apr/06/2004</td>\n      <td>NG</td>\n      <td>NG</td>\n    </tr>\n    <tr>\n      <th>4358</th>\n      <td>Bayonne Plant Holding, LLC</td>\n      <td>50497.0</td>\n      <td>50497.0</td>\n      <td>001001</td>\n      <td>GTG1</td>\n      <td>NaN</td>\n      <td>Pipeline Natural Gas</td>\n      <td>CT</td>\n      <td>48.0</td>\n      <td>1087315.653</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Sep/15/1988</td>\n      <td>NG</td>\n      <td>NG</td>\n    </tr>\n    <tr>\n      <th>1791</th>\n      <td>Ravenswood Generating Station</td>\n      <td>2500.0</td>\n      <td>2500.0</td>\n      <td>CT03-1</td>\n      <td>GT31</td>\n      <td>NaN</td>\n      <td>Pipeline Natural Gas</td>\n      <td>GT</td>\n      <td>50.0</td>\n      <td>26633.500</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Aug/01/1970</td>\n      <td>NG</td>\n      <td>NG</td>\n    </tr>\n    <tr>\n      <th>2051</th>\n      <td>Southwestern</td>\n      <td>2964.0</td>\n      <td>2964.0</td>\n      <td>8003</td>\n      <td>3</td>\n      <td>8003</td>\n      <td>Pipeline Natural Gas</td>\n      <td>ST</td>\n      <td>340.0</td>\n      <td>4658275.471</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>May/01/1967</td>\n      <td>NG</td>\n      <td>NG</td>\n    </tr>\n    <tr>\n      <th>349</th>\n      <td>Fort Myers</td>\n      <td>612.0</td>\n      <td>612.0</td>\n      <td>GFM09</td>\n      <td>9</td>\n      <td>NaN</td>\n      <td>Diesel Oil</td>\n      <td>GT</td>\n      <td>63.0</td>\n      <td>27725.200</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Apr/03/1974</td>\n      <td>DFO</td>\n      <td>DFO</td>\n    </tr>\n    <tr>\n      <th>4357</th>\n      <td>Axiall Corporation Natrium Plant</td>\n      <td>50491.0</td>\n      <td>50491.0</td>\n      <td>003</td>\n      <td>GEN7</td>\n      <td>NaN</td>\n      <td>Coal</td>\n      <td>ST</td>\n      <td>785.0</td>\n      <td>7056515.550</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Jun/01/1966</td>\n      <td>CBL</td>\n      <td>NG</td>\n    </tr>\n    <tr>\n      <th>6625</th>\n      <td>Panoche Energy Center</td>\n      <td>56803.0</td>\n      <td>56803.0</td>\n      <td>4</td>\n      <td>4</td>\n      <td>NaN</td>\n      <td>Pipeline Natural Gas</td>\n      <td>GT</td>\n      <td>110.0</td>\n      <td>828986.732</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Apr/01/2009</td>\n      <td>NG</td>\n      <td>NG</td>\n    </tr>\n    <tr>\n      <th>4804</th>\n      <td>Milford Power, LLC</td>\n      <td>54805.0</td>\n      <td>54805.0</td>\n      <td>1</td>\n      <td>ST-1</td>\n      <td>NaN</td>\n      <td>Pipeline Natural Gas</td>\n      <td>CT</td>\n      <td>129.0</td>\n      <td>799262.946</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>May/01/1993</td>\n      <td>NG</td>\n      <td>NG</td>\n    </tr>\n    <tr>\n      <th>371</th>\n      <td>Lauderdale</td>\n      <td>613.0</td>\n      <td>613.0</td>\n      <td>GFL09</td>\n      <td>9</td>\n      <td>NaN</td>\n      <td>Pipeline Natural Gas</td>\n      <td>GT</td>\n      <td>42.0</td>\n      <td>18939.200</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Aug/01/1970</td>\n      <td>NG</td>\n      <td>NG</td>\n    </tr>\n    <tr>\n      <th>5989</th>\n      <td>Frank Knutson Station</td>\n      <td>55505.0</td>\n      <td>55505.0</td>\n      <td>BR1</td>\n      <td>BR1</td>\n      <td>NaN</td>\n      <td>Pipeline Natural Gas</td>\n      <td>GT</td>\n      <td>90.0</td>\n      <td>127505.845</td>\n      <td>X-walk from Travis</td>\n      <td>OPR</td>\n      <td>Apr/09/2002</td>\n      <td>NG</td>\n      <td>NG</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "metadata": {},
     "execution_count": 99
    }
   ],
   "source": [
    "df['fuel_type_eia'] = df.apply(fuel_code_lookup, axis=1, eia_data=eia_data)\n",
    "df.sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Fill Missing Boiler-Generator Associations (TODO)\n",
    "- There are currently 71 EPA units that are not matched to EIA generators\n",
    "- Most boiler associations are missing\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Export CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "#re-order columns\n",
    "df = df[['plant_name',\n",
    "        'plant_id_epa',\n",
    "        'plant_id_eia',\n",
    "        'unitid',\n",
    "        'generator_id',\n",
    "        'boiler_id',\n",
    "        'fuel_type_primary',\n",
    "        'fuel_type_code',\n",
    "        'fuel_type_eia',\n",
    "        'prime_mover_code',\n",
    "        'edat_capacity_mw',\n",
    "        'heat_input_mmbtu',\n",
    "        'generator_id_match_method',\n",
    "        'op_status',\n",
    "        'op_status_date']]\n",
    "\n",
    "df.to_csv('eia_epa_id_crosswalk.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.7.3 32-bit",
   "language": "python",
   "name": "python37332bitfe38d12844834eeb83b77e60780d0739"
  },
  "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-final"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}