%matplotlib inline
import matplotlib.pyplot as plt
import pandas as pd
import seaborn as sns
import os
import glob
import numpy as np
I've been using state boundaries to define approximate NERC regions. How accurate is this? Use 2015 EIA 923 data to check.
path = os.path.join('Data storage', 'final NERC data from states', 'Monthly gen*')
mg_fns = glob.glob(path)
df_list = []
for f in mg_fns:
region = f.split()[-1][:-4]
df = pd.read_csv(f)
df['region'] = region
df_list.append(df)
full_mg = pd.concat(df_list)
full_mg.reset_index(inplace=True, drop=True)
full_mg['datetime'] = pd.to_datetime(full_mg['datetime'])
monthly_gen = pd.pivot_table(full_mg, index=['region', 'datetime'],
values='generation (MWh)', columns='fuel category 1')
monthly_gen.reset_index(inplace=True, drop=False)
monthly_gen['Year'] = monthly_gen['datetime'].dt.year
monthly_gen.replace(np.nan, 0, inplace=True)
monthly_gen.head()
fuel category 1 | region | datetime | Coal | Hydro | Natural Gas | Nuclear | Other | Other Renewables | Solar | Wind | Year |
---|---|---|---|---|---|---|---|---|---|---|---|
0 | ERCOT | 2001-01-01 | 11683911.0 | 138093.0 | 13750546.0 | 3545310.0 | 1707467.21 | 92021.80 | 0.0 | 83931.0 | 2001 |
1 | ERCOT | 2001-02-01 | 10236786.0 | 110148.0 | 11507834.0 | 3037626.0 | 510769.63 | 81710.37 | 0.0 | 141647.0 | 2001 |
2 | ERCOT | 2001-03-01 | 11004470.0 | 180140.0 | 13316335.0 | 2462837.0 | 447733.81 | 81192.19 | 0.0 | 87631.0 | 2001 |
3 | ERCOT | 2001-04-01 | 9767225.0 | 124232.0 | 14402417.0 | 2668816.0 | 331369.06 | 76768.94 | 0.0 | 115487.0 | 2001 |
4 | ERCOT | 2001-05-01 | 11449397.0 | 115102.0 | 16025878.0 | 3419870.0 | 383202.65 | 86697.35 | 0.0 | 103312.0 | 2001 |
annual_gen = monthly_gen.groupby(['region', 'Year']).sum()
annual_gen.reset_index(inplace=True, drop=False)
annual_gen = annual_gen.loc[annual_gen['Year'] == 2015]
annual_gen.index = annual_gen['region']
annual_gen.drop(['region', 'Year'], axis=1, inplace=True)
annual_gen['Total'] = annual_gen.sum(axis=1)
annual_gen
fuel category 1 | Coal | Hydro | Natural Gas | Nuclear | Other | Other Renewables | Solar | Wind | Total |
---|---|---|---|---|---|---|---|---|---|
region | |||||||||
ERCOT | 1.215543e+08 | 9.564100e+05 | 2.377314e+08 | 39354677.0 | 3555189.68 | 1440224.40 | 624241.61 | 44833365.01 | 4.500498e+08 |
FRCC | 4.290401e+07 | 2.442940e+05 | 1.558242e+08 | 28121916.0 | 5174833.88 | 4919495.46 | 379966.88 | 0.00 | 2.375687e+08 |
MRO | 1.451036e+08 | 1.277951e+07 | 2.509541e+07 | 37614963.0 | 934544.36 | 3709632.74 | 100400.01 | 41424538.21 | 2.667626e+08 |
NPCC | 6.261762e+06 | 3.291757e+07 | 1.108842e+08 | 76492798.0 | 5773251.84 | 9648512.66 | 2391473.06 | 6246282.16 | 2.506158e+08 |
RFC | 4.416527e+08 | 1.252052e+07 | 2.125528e+08 | 203192717.0 | 13554939.88 | 12255672.60 | 3379050.13 | 15705217.07 | 9.148136e+08 |
SERC | 3.540846e+08 | 3.602068e+07 | 3.103857e+08 | 344578736.0 | 8392496.24 | 20213420.09 | 2139545.83 | 11826187.00 | 1.087641e+09 |
SPP | 4.946037e+07 | 2.682864e+06 | 3.546023e+07 | 8630178.0 | -1782.11 | 398105.06 | 11023.80 | 25029398.02 | 1.216704e+08 |
WECC | 1.893719e+08 | 1.492681e+08 | 2.424304e+08 | 59191892.0 | 4708209.25 | 26322237.80 | 29186812.46 | 44881094.43 | 7.453607e+08 |
path = os.path.join('Data storage', 'EIA923_Schedules_2_3_4_5_M_12_2015_Final.xlsx')
eia = pd.read_excel(path, header=5, parse_cols='A,G,I,N,O,P,CR')
eia.columns = [col.replace('\n', ' ') for col in eia.columns]
eia.rename(columns={'Net Generation (Megawatthours)': 'Generation (MWh)',
'NERC Region': 'region'}, inplace=True)
eia.head()
Plant Id | Plant State | region | Reported Prime Mover | Reported Fuel Type Code | AER Fuel Type Code | Generation (MWh) | Fuel category | |
---|---|---|---|---|---|---|---|---|
0 | 2.0 | AL | SERC | HY | WAT | HYC | 25920.000 | Hydro |
1 | 3.0 | AL | SERC | CA | NG | NG | 2464536.000 | Natural Gas |
2 | 3.0 | AL | SERC | CT | NG | NG | 4318717.000 | Natural Gas |
3 | 3.0 | AL | SERC | ST | BIT | COL | 4559960.600 | Coal |
4 | 3.0 | AL | SERC | ST | NG | NG | 44348.399 | Natural Gas |
eia['AER Fuel Type Code'].unique()
array([u'HYC', u'NG', u'COL', u'WWW', u'DFO', u'NUC', u'WOO', u'RFO', u'ORW', u'WND', u'HPS', u'SUN', u'MLG', u'PC', u'GEO', u'OTH', u'OOG', u'WOC', nan], dtype=object)
fuel_cats = {'Coal': ['COL'],
'Natural Gas': ['NG'],
'Hydro': ['HYC', 'HPS'],
'Nuclear': ['NUC'],
'Wind': ['WND'],
'Solar': ['SUN'],
'Other Renewables': ['GEO', 'ORW', 'WWW', 'MLG'],
'Other': ['WOO', 'DFO', 'RFO', 'PC', 'OTH', 'OOG', 'WOC']}
for cat, fuels in fuel_cats.iteritems():
eia.loc[eia['AER Fuel Type Code'].isin(fuels), 'Fuel category'] = cat
eia_nerc = eia.groupby(['region', 'Fuel category']).sum()
eia_nerc.reset_index(inplace=True)
eia_nerc = eia_nerc.pivot_table(index='region', columns=['Fuel category'],
values='Generation (MWh)')
eia_nerc.rename(index={'TRE': 'ERCOT'}, inplace=True)
eia_nerc['Total'] = eia_nerc.sum(axis=1)
eia_nerc
Fuel category | Coal | Hydro | Natural Gas | Nuclear | Other | Other Renewables | Solar | Wind | Total |
---|---|---|---|---|---|---|---|---|---|
region | |||||||||
ASCC | 5.710906e+05 | 1.557639e+06 | 2.595667e+06 | NaN | 8.378348e+05 | 4.760678e+03 | NaN | 109132.00 | 5.676125e+06 |
FRCC | 3.833075e+07 | 2.442940e+05 | 1.497547e+08 | 28121916.0 | 5.131472e+06 | 4.454291e+06 | 223933.32 | NaN | 2.262613e+08 |
HICC | 1.276854e+06 | 1.140303e+05 | NaN | NaN | 7.427112e+06 | 5.512813e+05 | 17258.24 | 612772.00 | 9.999308e+06 |
MRO | 1.260326e+08 | 1.205585e+07 | 1.555511e+07 | 27606780.0 | 1.257706e+06 | 3.668414e+06 | 53406.00 | 39095309.20 | 2.253252e+08 |
NPCC | 6.144066e+06 | 3.201717e+07 | 1.102206e+08 | 76492798.0 | 6.542493e+06 | 9.634336e+06 | 659719.83 | 6246282.14 | 2.479575e+08 |
RFC | 3.965613e+08 | 7.258254e+06 | 1.928738e+08 | 273758844.0 | 2.090599e+07 | 8.586615e+06 | 1038624.99 | 23781461.00 | 9.247648e+08 |
SERC | 3.770525e+08 | 3.451772e+07 | 3.521520e+08 | 284020792.0 | 9.808552e+06 | 2.195996e+07 | 1585091.75 | 4066466.00 | 1.085163e+09 |
SPP | 1.068746e+08 | 6.234751e+06 | 6.913542e+07 | 8630178.0 | 3.519905e+06 | 2.560674e+06 | 10789.00 | 32245885.00 | 2.292122e+08 |
ERCOT | 9.762617e+07 | 7.068490e+05 | 1.935851e+08 | 39354677.0 | 2.725937e+06 | 1.129362e+06 | 373542.77 | 39697583.00 | 3.751992e+08 |
WECC | 1.876908e+08 | 1.492154e+08 | 2.336329e+08 | 59191892.0 | 5.355262e+06 | 2.624612e+07 | 20745916.63 | 44735219.56 | 7.268134e+08 |
(annual_gen - eia_nerc) / eia_nerc
fuel category 1 | Coal | Hydro | Natural Gas | Nuclear | Other | Other Renewables | Solar | Wind | Total |
---|---|---|---|---|---|---|---|---|---|
region | |||||||||
ASCC | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
ERCOT | 0.245099 | 3.530613e-01 | 0.228046 | 0.000000 | 0.304208 | 0.275255 | 0.671138 | 1.293727e-01 | 0.199496 |
FRCC | 0.119310 | 1.191344e-16 | 0.040529 | 0.000000 | 0.008450 | 0.104440 | 0.696786 | NaN | 0.049975 |
HICC | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
MRO | 0.151318 | 6.002571e-02 | 0.613323 | 0.362526 | -0.256945 | 0.011236 | 0.879939 | 5.957822e-02 | 0.183901 |
NPCC | 0.019156 | 2.812242e-02 | 0.006020 | 0.000000 | -0.117576 | 0.001471 | 2.624983 | 3.201905e-09 | 0.010721 |
RFC | 0.113706 | 7.250037e-01 | 0.102031 | -0.257767 | -0.351624 | 0.427300 | 2.253388 | -3.396025e-01 | -0.010761 |
SERC | -0.060914 | 4.354169e-02 | -0.118603 | 0.213217 | -0.144369 | -0.079533 | 0.349793 | 1.908222e+00 | 0.002284 |
SPP | -0.537211 | -5.696919e-01 | -0.487090 | 0.000000 | -1.000506 | -0.844531 | 0.021763 | -2.237956e-01 | -0.469180 |
WECC | 0.008957 | 3.537307e-04 | 0.037655 | 0.000000 | -0.120826 | 0.002900 | 0.406870 | 3.260851e-03 | 0.025519 |