{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-07-13T11:47:56.115738",
     "start_time": "2017-07-13T11:47:54.147563"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import matplotlib.pyplot as plt\n",
    "import pandas as pd\n",
    "import seaborn as sns\n",
    "import os\n",
    "import glob\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "I've been using state boundaries to define approximate NERC regions. How accurate is this? Use 2015 EIA 923 data to check."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read NERC by state data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-07-13T11:50:29.389818",
     "start_time": "2017-07-13T11:50:29.384300"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "path = os.path.join('Data storage', 'final NERC data from states', 'Monthly gen*')\n",
    "mg_fns = glob.glob(path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-07-13T11:51:05.301128",
     "start_time": "2017-07-13T11:51:05.062885"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_list = []\n",
    "for f in mg_fns:\n",
    "    region = f.split()[-1][:-4]\n",
    "    df = pd.read_csv(f)\n",
    "    df['region'] = region\n",
    "    df_list.append(df)\n",
    "full_mg = pd.concat(df_list)\n",
    "full_mg.reset_index(inplace=True, drop=True)\n",
    "full_mg['datetime'] = pd.to_datetime(full_mg['datetime'])\n",
    "\n",
    "monthly_gen = pd.pivot_table(full_mg, index=['region', 'datetime'], \n",
    "                             values='generation (MWh)', columns='fuel category 1')\n",
    "monthly_gen.reset_index(inplace=True, drop=False)\n",
    "monthly_gen['Year'] = monthly_gen['datetime'].dt.year\n",
    "monthly_gen.replace(np.nan, 0, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-07-13T11:51:46.685892",
     "start_time": "2017-07-13T11:51:46.651800"
    },
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>fuel category 1</th>\n",
       "      <th>region</th>\n",
       "      <th>datetime</th>\n",
       "      <th>Coal</th>\n",
       "      <th>Hydro</th>\n",
       "      <th>Natural Gas</th>\n",
       "      <th>Nuclear</th>\n",
       "      <th>Other</th>\n",
       "      <th>Other Renewables</th>\n",
       "      <th>Solar</th>\n",
       "      <th>Wind</th>\n",
       "      <th>Year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ERCOT</td>\n",
       "      <td>2001-01-01</td>\n",
       "      <td>11683911.0</td>\n",
       "      <td>138093.0</td>\n",
       "      <td>13750546.0</td>\n",
       "      <td>3545310.0</td>\n",
       "      <td>1707467.21</td>\n",
       "      <td>92021.80</td>\n",
       "      <td>0.0</td>\n",
       "      <td>83931.0</td>\n",
       "      <td>2001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ERCOT</td>\n",
       "      <td>2001-02-01</td>\n",
       "      <td>10236786.0</td>\n",
       "      <td>110148.0</td>\n",
       "      <td>11507834.0</td>\n",
       "      <td>3037626.0</td>\n",
       "      <td>510769.63</td>\n",
       "      <td>81710.37</td>\n",
       "      <td>0.0</td>\n",
       "      <td>141647.0</td>\n",
       "      <td>2001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ERCOT</td>\n",
       "      <td>2001-03-01</td>\n",
       "      <td>11004470.0</td>\n",
       "      <td>180140.0</td>\n",
       "      <td>13316335.0</td>\n",
       "      <td>2462837.0</td>\n",
       "      <td>447733.81</td>\n",
       "      <td>81192.19</td>\n",
       "      <td>0.0</td>\n",
       "      <td>87631.0</td>\n",
       "      <td>2001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ERCOT</td>\n",
       "      <td>2001-04-01</td>\n",
       "      <td>9767225.0</td>\n",
       "      <td>124232.0</td>\n",
       "      <td>14402417.0</td>\n",
       "      <td>2668816.0</td>\n",
       "      <td>331369.06</td>\n",
       "      <td>76768.94</td>\n",
       "      <td>0.0</td>\n",
       "      <td>115487.0</td>\n",
       "      <td>2001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ERCOT</td>\n",
       "      <td>2001-05-01</td>\n",
       "      <td>11449397.0</td>\n",
       "      <td>115102.0</td>\n",
       "      <td>16025878.0</td>\n",
       "      <td>3419870.0</td>\n",
       "      <td>383202.65</td>\n",
       "      <td>86697.35</td>\n",
       "      <td>0.0</td>\n",
       "      <td>103312.0</td>\n",
       "      <td>2001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "fuel category 1 region   datetime        Coal     Hydro  Natural Gas  \\\n",
       "0                ERCOT 2001-01-01  11683911.0  138093.0   13750546.0   \n",
       "1                ERCOT 2001-02-01  10236786.0  110148.0   11507834.0   \n",
       "2                ERCOT 2001-03-01  11004470.0  180140.0   13316335.0   \n",
       "3                ERCOT 2001-04-01   9767225.0  124232.0   14402417.0   \n",
       "4                ERCOT 2001-05-01  11449397.0  115102.0   16025878.0   \n",
       "\n",
       "fuel category 1    Nuclear       Other  Other Renewables  Solar      Wind  \\\n",
       "0                3545310.0  1707467.21          92021.80    0.0   83931.0   \n",
       "1                3037626.0   510769.63          81710.37    0.0  141647.0   \n",
       "2                2462837.0   447733.81          81192.19    0.0   87631.0   \n",
       "3                2668816.0   331369.06          76768.94    0.0  115487.0   \n",
       "4                3419870.0   383202.65          86697.35    0.0  103312.0   \n",
       "\n",
       "fuel category 1  Year  \n",
       "0                2001  \n",
       "1                2001  \n",
       "2                2001  \n",
       "3                2001  \n",
       "4                2001  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "monthly_gen.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-07-13T12:21:38.912792",
     "start_time": "2017-07-13T12:21:38.903103"
    },
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "annual_gen = monthly_gen.groupby(['region', 'Year']).sum()\n",
    "annual_gen.reset_index(inplace=True, drop=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-07-13T12:21:39.277759",
     "start_time": "2017-07-13T12:21:39.186982"
    },
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>fuel category 1</th>\n",
       "      <th>Coal</th>\n",
       "      <th>Hydro</th>\n",
       "      <th>Natural Gas</th>\n",
       "      <th>Nuclear</th>\n",
       "      <th>Other</th>\n",
       "      <th>Other Renewables</th>\n",
       "      <th>Solar</th>\n",
       "      <th>Wind</th>\n",
       "      <th>Total</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region</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>ERCOT</th>\n",
       "      <td>1.215543e+08</td>\n",
       "      <td>9.564100e+05</td>\n",
       "      <td>2.377314e+08</td>\n",
       "      <td>39354677.0</td>\n",
       "      <td>3555189.68</td>\n",
       "      <td>1440224.40</td>\n",
       "      <td>624241.61</td>\n",
       "      <td>44833365.01</td>\n",
       "      <td>4.500498e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FRCC</th>\n",
       "      <td>4.290401e+07</td>\n",
       "      <td>2.442940e+05</td>\n",
       "      <td>1.558242e+08</td>\n",
       "      <td>28121916.0</td>\n",
       "      <td>5174833.88</td>\n",
       "      <td>4919495.46</td>\n",
       "      <td>379966.88</td>\n",
       "      <td>0.00</td>\n",
       "      <td>2.375687e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MRO</th>\n",
       "      <td>1.451036e+08</td>\n",
       "      <td>1.277951e+07</td>\n",
       "      <td>2.509541e+07</td>\n",
       "      <td>37614963.0</td>\n",
       "      <td>934544.36</td>\n",
       "      <td>3709632.74</td>\n",
       "      <td>100400.01</td>\n",
       "      <td>41424538.21</td>\n",
       "      <td>2.667626e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NPCC</th>\n",
       "      <td>6.261762e+06</td>\n",
       "      <td>3.291757e+07</td>\n",
       "      <td>1.108842e+08</td>\n",
       "      <td>76492798.0</td>\n",
       "      <td>5773251.84</td>\n",
       "      <td>9648512.66</td>\n",
       "      <td>2391473.06</td>\n",
       "      <td>6246282.16</td>\n",
       "      <td>2.506158e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RFC</th>\n",
       "      <td>4.416527e+08</td>\n",
       "      <td>1.252052e+07</td>\n",
       "      <td>2.125528e+08</td>\n",
       "      <td>203192717.0</td>\n",
       "      <td>13554939.88</td>\n",
       "      <td>12255672.60</td>\n",
       "      <td>3379050.13</td>\n",
       "      <td>15705217.07</td>\n",
       "      <td>9.148136e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SERC</th>\n",
       "      <td>3.540846e+08</td>\n",
       "      <td>3.602068e+07</td>\n",
       "      <td>3.103857e+08</td>\n",
       "      <td>344578736.0</td>\n",
       "      <td>8392496.24</td>\n",
       "      <td>20213420.09</td>\n",
       "      <td>2139545.83</td>\n",
       "      <td>11826187.00</td>\n",
       "      <td>1.087641e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SPP</th>\n",
       "      <td>4.946037e+07</td>\n",
       "      <td>2.682864e+06</td>\n",
       "      <td>3.546023e+07</td>\n",
       "      <td>8630178.0</td>\n",
       "      <td>-1782.11</td>\n",
       "      <td>398105.06</td>\n",
       "      <td>11023.80</td>\n",
       "      <td>25029398.02</td>\n",
       "      <td>1.216704e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WECC</th>\n",
       "      <td>1.893719e+08</td>\n",
       "      <td>1.492681e+08</td>\n",
       "      <td>2.424304e+08</td>\n",
       "      <td>59191892.0</td>\n",
       "      <td>4708209.25</td>\n",
       "      <td>26322237.80</td>\n",
       "      <td>29186812.46</td>\n",
       "      <td>44881094.43</td>\n",
       "      <td>7.453607e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "fuel category 1          Coal         Hydro   Natural Gas      Nuclear  \\\n",
       "region                                                                   \n",
       "ERCOT            1.215543e+08  9.564100e+05  2.377314e+08   39354677.0   \n",
       "FRCC             4.290401e+07  2.442940e+05  1.558242e+08   28121916.0   \n",
       "MRO              1.451036e+08  1.277951e+07  2.509541e+07   37614963.0   \n",
       "NPCC             6.261762e+06  3.291757e+07  1.108842e+08   76492798.0   \n",
       "RFC              4.416527e+08  1.252052e+07  2.125528e+08  203192717.0   \n",
       "SERC             3.540846e+08  3.602068e+07  3.103857e+08  344578736.0   \n",
       "SPP              4.946037e+07  2.682864e+06  3.546023e+07    8630178.0   \n",
       "WECC             1.893719e+08  1.492681e+08  2.424304e+08   59191892.0   \n",
       "\n",
       "fuel category 1        Other  Other Renewables        Solar         Wind  \\\n",
       "region                                                                     \n",
       "ERCOT             3555189.68        1440224.40    624241.61  44833365.01   \n",
       "FRCC              5174833.88        4919495.46    379966.88         0.00   \n",
       "MRO                934544.36        3709632.74    100400.01  41424538.21   \n",
       "NPCC              5773251.84        9648512.66   2391473.06   6246282.16   \n",
       "RFC              13554939.88       12255672.60   3379050.13  15705217.07   \n",
       "SERC              8392496.24       20213420.09   2139545.83  11826187.00   \n",
       "SPP                 -1782.11         398105.06     11023.80  25029398.02   \n",
       "WECC              4708209.25       26322237.80  29186812.46  44881094.43   \n",
       "\n",
       "fuel category 1         Total  \n",
       "region                         \n",
       "ERCOT            4.500498e+08  \n",
       "FRCC             2.375687e+08  \n",
       "MRO              2.667626e+08  \n",
       "NPCC             2.506158e+08  \n",
       "RFC              9.148136e+08  \n",
       "SERC             1.087641e+09  \n",
       "SPP              1.216704e+08  \n",
       "WECC             7.453607e+08  "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "annual_gen = annual_gen.loc[annual_gen['Year'] == 2015]\n",
    "annual_gen.index = annual_gen['region']\n",
    "annual_gen.drop(['region', 'Year'], axis=1, inplace=True)\n",
    "annual_gen['Total'] = annual_gen.sum(axis=1)\n",
    "annual_gen"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read EIA-923 data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-07-13T11:56:34.828112",
     "start_time": "2017-07-13T11:56:05.087077"
    },
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "path = os.path.join('Data storage', 'EIA923_Schedules_2_3_4_5_M_12_2015_Final.xlsx')\n",
    "eia = pd.read_excel(path, header=5, parse_cols='A,G,I,N,O,P,CR')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-07-13T12:14:03.039063",
     "start_time": "2017-07-13T12:14:03.032363"
    },
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "eia.columns = [col.replace('\\n', ' ') for col in eia.columns]\n",
    "eia.rename(columns={'Net Generation (Megawatthours)': 'Generation (MWh)',\n",
    "                  'NERC Region': 'region'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-07-13T12:14:03.615974",
     "start_time": "2017-07-13T12:14:03.590972"
    },
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Plant Id</th>\n",
       "      <th>Plant State</th>\n",
       "      <th>region</th>\n",
       "      <th>Reported Prime Mover</th>\n",
       "      <th>Reported Fuel Type Code</th>\n",
       "      <th>AER Fuel Type Code</th>\n",
       "      <th>Generation (MWh)</th>\n",
       "      <th>Fuel category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2.0</td>\n",
       "      <td>AL</td>\n",
       "      <td>SERC</td>\n",
       "      <td>HY</td>\n",
       "      <td>WAT</td>\n",
       "      <td>HYC</td>\n",
       "      <td>25920.000</td>\n",
       "      <td>Hydro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.0</td>\n",
       "      <td>AL</td>\n",
       "      <td>SERC</td>\n",
       "      <td>CA</td>\n",
       "      <td>NG</td>\n",
       "      <td>NG</td>\n",
       "      <td>2464536.000</td>\n",
       "      <td>Natural Gas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.0</td>\n",
       "      <td>AL</td>\n",
       "      <td>SERC</td>\n",
       "      <td>CT</td>\n",
       "      <td>NG</td>\n",
       "      <td>NG</td>\n",
       "      <td>4318717.000</td>\n",
       "      <td>Natural Gas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.0</td>\n",
       "      <td>AL</td>\n",
       "      <td>SERC</td>\n",
       "      <td>ST</td>\n",
       "      <td>BIT</td>\n",
       "      <td>COL</td>\n",
       "      <td>4559960.600</td>\n",
       "      <td>Coal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3.0</td>\n",
       "      <td>AL</td>\n",
       "      <td>SERC</td>\n",
       "      <td>ST</td>\n",
       "      <td>NG</td>\n",
       "      <td>NG</td>\n",
       "      <td>44348.399</td>\n",
       "      <td>Natural Gas</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Plant Id Plant State region Reported Prime Mover Reported Fuel Type Code  \\\n",
       "0       2.0          AL   SERC                   HY                     WAT   \n",
       "1       3.0          AL   SERC                   CA                      NG   \n",
       "2       3.0          AL   SERC                   CT                      NG   \n",
       "3       3.0          AL   SERC                   ST                     BIT   \n",
       "4       3.0          AL   SERC                   ST                      NG   \n",
       "\n",
       "  AER Fuel Type Code  Generation (MWh) Fuel category  \n",
       "0                HYC         25920.000         Hydro  \n",
       "1                 NG       2464536.000   Natural Gas  \n",
       "2                 NG       4318717.000   Natural Gas  \n",
       "3                COL       4559960.600          Coal  \n",
       "4                 NG         44348.399   Natural Gas  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eia.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-07-13T12:00:57.298010",
     "start_time": "2017-07-13T12:00:57.289405"
    },
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([u'HYC', u'NG', u'COL', u'WWW', u'DFO', u'NUC', u'WOO', u'RFO',\n",
       "       u'ORW', u'WND', u'HPS', u'SUN', u'MLG', u'PC', u'GEO', u'OTH',\n",
       "       u'OOG', u'WOC', nan], dtype=object)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eia['AER Fuel Type Code'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-07-13T12:06:48.630813",
     "start_time": "2017-07-13T12:06:48.623508"
    },
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "fuel_cats = {'Coal': ['COL'],\n",
    "             'Natural Gas': ['NG'],\n",
    "             'Hydro': ['HYC', 'HPS'],\n",
    "             'Nuclear': ['NUC'],\n",
    "             'Wind': ['WND'],\n",
    "             'Solar': ['SUN'],\n",
    "             'Other Renewables': ['GEO', 'ORW', 'WWW', 'MLG'],\n",
    "             'Other': ['WOO', 'DFO', 'RFO', 'PC', 'OTH', 'OOG', 'WOC']}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-07-13T12:10:01.310258",
     "start_time": "2017-07-13T12:10:01.273892"
    },
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "for cat, fuels in fuel_cats.iteritems():\n",
    "    eia.loc[eia['AER Fuel Type Code'].isin(fuels), 'Fuel category'] = cat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-07-13T12:22:09.533041",
     "start_time": "2017-07-13T12:22:09.520828"
    },
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "eia_nerc = eia.groupby(['region', 'Fuel category']).sum()\n",
    "eia_nerc.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-07-13T12:22:09.947309",
     "start_time": "2017-07-13T12:22:09.840237"
    },
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>Fuel category</th>\n",
       "      <th>Coal</th>\n",
       "      <th>Hydro</th>\n",
       "      <th>Natural Gas</th>\n",
       "      <th>Nuclear</th>\n",
       "      <th>Other</th>\n",
       "      <th>Other Renewables</th>\n",
       "      <th>Solar</th>\n",
       "      <th>Wind</th>\n",
       "      <th>Total</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region</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>ASCC</th>\n",
       "      <td>5.710906e+05</td>\n",
       "      <td>1.557639e+06</td>\n",
       "      <td>2.595667e+06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8.378348e+05</td>\n",
       "      <td>4.760678e+03</td>\n",
       "      <td>NaN</td>\n",
       "      <td>109132.00</td>\n",
       "      <td>5.676125e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FRCC</th>\n",
       "      <td>3.833075e+07</td>\n",
       "      <td>2.442940e+05</td>\n",
       "      <td>1.497547e+08</td>\n",
       "      <td>28121916.0</td>\n",
       "      <td>5.131472e+06</td>\n",
       "      <td>4.454291e+06</td>\n",
       "      <td>223933.32</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.262613e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HICC</th>\n",
       "      <td>1.276854e+06</td>\n",
       "      <td>1.140303e+05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.427112e+06</td>\n",
       "      <td>5.512813e+05</td>\n",
       "      <td>17258.24</td>\n",
       "      <td>612772.00</td>\n",
       "      <td>9.999308e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MRO</th>\n",
       "      <td>1.260326e+08</td>\n",
       "      <td>1.205585e+07</td>\n",
       "      <td>1.555511e+07</td>\n",
       "      <td>27606780.0</td>\n",
       "      <td>1.257706e+06</td>\n",
       "      <td>3.668414e+06</td>\n",
       "      <td>53406.00</td>\n",
       "      <td>39095309.20</td>\n",
       "      <td>2.253252e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NPCC</th>\n",
       "      <td>6.144066e+06</td>\n",
       "      <td>3.201717e+07</td>\n",
       "      <td>1.102206e+08</td>\n",
       "      <td>76492798.0</td>\n",
       "      <td>6.542493e+06</td>\n",
       "      <td>9.634336e+06</td>\n",
       "      <td>659719.83</td>\n",
       "      <td>6246282.14</td>\n",
       "      <td>2.479575e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RFC</th>\n",
       "      <td>3.965613e+08</td>\n",
       "      <td>7.258254e+06</td>\n",
       "      <td>1.928738e+08</td>\n",
       "      <td>273758844.0</td>\n",
       "      <td>2.090599e+07</td>\n",
       "      <td>8.586615e+06</td>\n",
       "      <td>1038624.99</td>\n",
       "      <td>23781461.00</td>\n",
       "      <td>9.247648e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SERC</th>\n",
       "      <td>3.770525e+08</td>\n",
       "      <td>3.451772e+07</td>\n",
       "      <td>3.521520e+08</td>\n",
       "      <td>284020792.0</td>\n",
       "      <td>9.808552e+06</td>\n",
       "      <td>2.195996e+07</td>\n",
       "      <td>1585091.75</td>\n",
       "      <td>4066466.00</td>\n",
       "      <td>1.085163e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SPP</th>\n",
       "      <td>1.068746e+08</td>\n",
       "      <td>6.234751e+06</td>\n",
       "      <td>6.913542e+07</td>\n",
       "      <td>8630178.0</td>\n",
       "      <td>3.519905e+06</td>\n",
       "      <td>2.560674e+06</td>\n",
       "      <td>10789.00</td>\n",
       "      <td>32245885.00</td>\n",
       "      <td>2.292122e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ERCOT</th>\n",
       "      <td>9.762617e+07</td>\n",
       "      <td>7.068490e+05</td>\n",
       "      <td>1.935851e+08</td>\n",
       "      <td>39354677.0</td>\n",
       "      <td>2.725937e+06</td>\n",
       "      <td>1.129362e+06</td>\n",
       "      <td>373542.77</td>\n",
       "      <td>39697583.00</td>\n",
       "      <td>3.751992e+08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WECC</th>\n",
       "      <td>1.876908e+08</td>\n",
       "      <td>1.492154e+08</td>\n",
       "      <td>2.336329e+08</td>\n",
       "      <td>59191892.0</td>\n",
       "      <td>5.355262e+06</td>\n",
       "      <td>2.624612e+07</td>\n",
       "      <td>20745916.63</td>\n",
       "      <td>44735219.56</td>\n",
       "      <td>7.268134e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Fuel category          Coal         Hydro   Natural Gas      Nuclear  \\\n",
       "region                                                                 \n",
       "ASCC           5.710906e+05  1.557639e+06  2.595667e+06          NaN   \n",
       "FRCC           3.833075e+07  2.442940e+05  1.497547e+08   28121916.0   \n",
       "HICC           1.276854e+06  1.140303e+05           NaN          NaN   \n",
       "MRO            1.260326e+08  1.205585e+07  1.555511e+07   27606780.0   \n",
       "NPCC           6.144066e+06  3.201717e+07  1.102206e+08   76492798.0   \n",
       "RFC            3.965613e+08  7.258254e+06  1.928738e+08  273758844.0   \n",
       "SERC           3.770525e+08  3.451772e+07  3.521520e+08  284020792.0   \n",
       "SPP            1.068746e+08  6.234751e+06  6.913542e+07    8630178.0   \n",
       "ERCOT          9.762617e+07  7.068490e+05  1.935851e+08   39354677.0   \n",
       "WECC           1.876908e+08  1.492154e+08  2.336329e+08   59191892.0   \n",
       "\n",
       "Fuel category         Other  Other Renewables        Solar         Wind  \\\n",
       "region                                                                    \n",
       "ASCC           8.378348e+05      4.760678e+03          NaN    109132.00   \n",
       "FRCC           5.131472e+06      4.454291e+06    223933.32          NaN   \n",
       "HICC           7.427112e+06      5.512813e+05     17258.24    612772.00   \n",
       "MRO            1.257706e+06      3.668414e+06     53406.00  39095309.20   \n",
       "NPCC           6.542493e+06      9.634336e+06    659719.83   6246282.14   \n",
       "RFC            2.090599e+07      8.586615e+06   1038624.99  23781461.00   \n",
       "SERC           9.808552e+06      2.195996e+07   1585091.75   4066466.00   \n",
       "SPP            3.519905e+06      2.560674e+06     10789.00  32245885.00   \n",
       "ERCOT          2.725937e+06      1.129362e+06    373542.77  39697583.00   \n",
       "WECC           5.355262e+06      2.624612e+07  20745916.63  44735219.56   \n",
       "\n",
       "Fuel category         Total  \n",
       "region                       \n",
       "ASCC           5.676125e+06  \n",
       "FRCC           2.262613e+08  \n",
       "HICC           9.999308e+06  \n",
       "MRO            2.253252e+08  \n",
       "NPCC           2.479575e+08  \n",
       "RFC            9.247648e+08  \n",
       "SERC           1.085163e+09  \n",
       "SPP            2.292122e+08  \n",
       "ERCOT          3.751992e+08  \n",
       "WECC           7.268134e+08  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "eia_nerc = eia_nerc.pivot_table(index='region', columns=['Fuel category'],\n",
    "                     values='Generation (MWh)')\n",
    "eia_nerc.rename(index={'TRE': 'ERCOT'}, inplace=True)\n",
    "eia_nerc['Total'] = eia_nerc.sum(axis=1)\n",
    "eia_nerc"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Compare 2 data sets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-07-13T12:23:16.905581",
     "start_time": "2017-07-13T12:23:16.862899"
    },
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>fuel category 1</th>\n",
       "      <th>Coal</th>\n",
       "      <th>Hydro</th>\n",
       "      <th>Natural Gas</th>\n",
       "      <th>Nuclear</th>\n",
       "      <th>Other</th>\n",
       "      <th>Other Renewables</th>\n",
       "      <th>Solar</th>\n",
       "      <th>Wind</th>\n",
       "      <th>Total</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>region</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>ASCC</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ERCOT</th>\n",
       "      <td>0.245099</td>\n",
       "      <td>3.530613e-01</td>\n",
       "      <td>0.228046</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.304208</td>\n",
       "      <td>0.275255</td>\n",
       "      <td>0.671138</td>\n",
       "      <td>1.293727e-01</td>\n",
       "      <td>0.199496</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FRCC</th>\n",
       "      <td>0.119310</td>\n",
       "      <td>1.191344e-16</td>\n",
       "      <td>0.040529</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.008450</td>\n",
       "      <td>0.104440</td>\n",
       "      <td>0.696786</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.049975</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>HICC</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MRO</th>\n",
       "      <td>0.151318</td>\n",
       "      <td>6.002571e-02</td>\n",
       "      <td>0.613323</td>\n",
       "      <td>0.362526</td>\n",
       "      <td>-0.256945</td>\n",
       "      <td>0.011236</td>\n",
       "      <td>0.879939</td>\n",
       "      <td>5.957822e-02</td>\n",
       "      <td>0.183901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NPCC</th>\n",
       "      <td>0.019156</td>\n",
       "      <td>2.812242e-02</td>\n",
       "      <td>0.006020</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-0.117576</td>\n",
       "      <td>0.001471</td>\n",
       "      <td>2.624983</td>\n",
       "      <td>3.201905e-09</td>\n",
       "      <td>0.010721</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RFC</th>\n",
       "      <td>0.113706</td>\n",
       "      <td>7.250037e-01</td>\n",
       "      <td>0.102031</td>\n",
       "      <td>-0.257767</td>\n",
       "      <td>-0.351624</td>\n",
       "      <td>0.427300</td>\n",
       "      <td>2.253388</td>\n",
       "      <td>-3.396025e-01</td>\n",
       "      <td>-0.010761</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SERC</th>\n",
       "      <td>-0.060914</td>\n",
       "      <td>4.354169e-02</td>\n",
       "      <td>-0.118603</td>\n",
       "      <td>0.213217</td>\n",
       "      <td>-0.144369</td>\n",
       "      <td>-0.079533</td>\n",
       "      <td>0.349793</td>\n",
       "      <td>1.908222e+00</td>\n",
       "      <td>0.002284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SPP</th>\n",
       "      <td>-0.537211</td>\n",
       "      <td>-5.696919e-01</td>\n",
       "      <td>-0.487090</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-1.000506</td>\n",
       "      <td>-0.844531</td>\n",
       "      <td>0.021763</td>\n",
       "      <td>-2.237956e-01</td>\n",
       "      <td>-0.469180</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WECC</th>\n",
       "      <td>0.008957</td>\n",
       "      <td>3.537307e-04</td>\n",
       "      <td>0.037655</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-0.120826</td>\n",
       "      <td>0.002900</td>\n",
       "      <td>0.406870</td>\n",
       "      <td>3.260851e-03</td>\n",
       "      <td>0.025519</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "fuel category 1      Coal         Hydro  Natural Gas   Nuclear     Other  \\\n",
       "region                                                                     \n",
       "ASCC                  NaN           NaN          NaN       NaN       NaN   \n",
       "ERCOT            0.245099  3.530613e-01     0.228046  0.000000  0.304208   \n",
       "FRCC             0.119310  1.191344e-16     0.040529  0.000000  0.008450   \n",
       "HICC                  NaN           NaN          NaN       NaN       NaN   \n",
       "MRO              0.151318  6.002571e-02     0.613323  0.362526 -0.256945   \n",
       "NPCC             0.019156  2.812242e-02     0.006020  0.000000 -0.117576   \n",
       "RFC              0.113706  7.250037e-01     0.102031 -0.257767 -0.351624   \n",
       "SERC            -0.060914  4.354169e-02    -0.118603  0.213217 -0.144369   \n",
       "SPP             -0.537211 -5.696919e-01    -0.487090  0.000000 -1.000506   \n",
       "WECC             0.008957  3.537307e-04     0.037655  0.000000 -0.120826   \n",
       "\n",
       "fuel category 1  Other Renewables     Solar          Wind     Total  \n",
       "region                                                               \n",
       "ASCC                          NaN       NaN           NaN       NaN  \n",
       "ERCOT                    0.275255  0.671138  1.293727e-01  0.199496  \n",
       "FRCC                     0.104440  0.696786           NaN  0.049975  \n",
       "HICC                          NaN       NaN           NaN       NaN  \n",
       "MRO                      0.011236  0.879939  5.957822e-02  0.183901  \n",
       "NPCC                     0.001471  2.624983  3.201905e-09  0.010721  \n",
       "RFC                      0.427300  2.253388 -3.396025e-01 -0.010761  \n",
       "SERC                    -0.079533  0.349793  1.908222e+00  0.002284  \n",
       "SPP                     -0.844531  0.021763 -2.237956e-01 -0.469180  \n",
       "WECC                     0.002900  0.406870  3.260851e-03  0.025519  "
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(annual_gen - eia_nerc) / eia_nerc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}