path = os.path.join('Data storage', 'Facility gen fuels and CO2 2017-05-25.zip')
facility_df = pd.read_csv(path, parse_dates=['datetime'])

facility_df.head()

facility_df.dropna(inplace=True, subset=['lat', 'lon'])

cols = ['lat', 'lon', 'plant id', 'year']
small_facility = facility_df.loc[:, cols].drop_duplicates()

geometry = [Point(xy) for xy in zip(small_facility.lon, small_facility.lat)]
# small_facility = small_facility.drop(['lon', 'lat'], axis=1)
crs = {'init': 'epsg:4326'}
geo_df = GeoDataFrame(small_facility, crs=crs, geometry=geometry)

"""
Explanation: Import facility data and NERC labels
End of explanation
"""


path = os.path.join('Data storage', 'NERC_Regions_EIA', 'NercRegions_201610.shp')
regions = gpd.read_file(path)

facility_nerc = gpd.sjoin(geo_df, regions, how='inner', op='within')

facility_nerc.head()

"""
Explanation: Read NERC shapefile and merge with geo_df
End of explanation
"""


cols = ['plant id', 'year', 'NERC']
facility_df = facility_df.merge(facility_nerc.loc[:, cols],
                                on=['plant id', 'year'], how='left')

facility_df.head()

"""
Explanation: Merge NERC labels into the facility df
End of explanation
"""


facility_df['state'] = facility_df['geography'].str[-2:]
keep_cols = ['fuel', 'year', 'month', 'datetime', 'state', 'plant id', 'NERC',
             'generation (MWh)', 'total fuel (mmbtu)', 'elec fuel (mmbtu)']
facility_df = facility_df.loc[facility_df['year'] >= 2014, keep_cols]

facility_fuel_cats = {'COW': ['SUB', 'BIT', 'LIG', 'WC', 'SC', 'RC', 'SGC'],
                      'NG': ['NG'],
                      'PEL': ['DFO', 'RFO', 'KER', 'JF',
                              'PG', 'WO', 'SGP'],
                      'PC': ['PC'],
                      'HYC': ['WAT'],
                      'HPS': [],
                      'GEO': ['GEO'],
                      'NUC': ['NUC'],
                      'OOG': ['BFG', 'OG', 'LFG'],
                      'OTH': ['OTH', 'MSN', 'MSW', 'PUR', 'TDF', 'WH'],
                      'SUN': ['SUN'],
                      'DPV': [],
                      'WAS': ['OBL', 'OBS', 'OBG', 'MSB', 'SLW'],
                      'WND': ['WND'],
                      'WWW': ['WDL', 'WDS', 'AB', 'BLQ']
                      }

for category in facility_fuel_cats.keys():
    fuels = facility_fuel_cats[category]
    facility_df.loc[facility_df['fuel'].isin(fuels),
                    'fuel category'] = category

facility_df.head()

facility_df.dtypes

facility_df.loc[facility_df['NERC'].isnull(), 'state'].unique()

"""
Explanation: Filter out data older than 2014 to reduce size
End of explanation
"""


folder = os.path.join('Data storage', 'Derived data', 'state gen data')
states = ["AL", "AK", "AZ", "AR", "CA", "CO", "CT", "DE", 
          "FL", "GA", "HI", "ID", "IL", "IN", "IA", "KS", 
          "KY", "LA", "ME", "MD", "MA", "MI", "MN", "MS", 
          "MO", "MT", "NE", "NV", "NH", "NJ", "NM", "NY", 
          "NC", "ND", "OH", "OK", "OR", "PA", "RI", "SC", 
          "SD", "TN", "TX", "UT", "VT", "VA", "WA", "WV", "WI", "WY"]

state_list = []
for state in states:
    path = os.path.join(folder, '{} fuels gen.csv'.format(state))
    df = pd.read_csv(path, parse_dates=['datetime'])
    state_list.append(df)
state_df = pd.concat(state_list)
state_df.reset_index(inplace=True, drop=True)

state_df.dtypes

state_df['state'] = state_df['geography'].str[-2:]
keep_cols = ['state', 'type', 'year', 'datetime', 'generation (MWh)',
             'elec fuel (mmbtu)']

fuel_cats = facility_fuel_cats.keys()
state_df = state_df.loc[(state_df['year'] >= 2014) &
                        (state_df['type'].isin(fuel_cats)), keep_cols]

state_df['type'].unique()

"""
Explanation: Import state-level generation data
End of explanation
"""


annual_facility = facility_df.groupby(['year', 'state', 'fuel category']).sum()
# annual_facility.reset_index(inplace=True)
annual_facility.drop('plant id', axis=1, inplace=True)

annual_facility.head()

annual_state = state_df.groupby(['year', 'state', 'type']).sum()
# annual_state.reset_index(inplace=True)

annual_state.head(n=25)

"""
Explanation: Total generation and fuel consumption for each fuel category
Annual
End of explanation
"""


annual_state.loc[2016, 'CA', 'SUN']

annual_facility.loc[2016, 'CA', 'SUN']

"""
Explanation: It's interesting that the facility data has fuel consumption for solar generation and the state data doesn't. Looking at a 923 data file, it's clear that the fuel consumption is just based on a conversion efficiency of 36.6% across all facilities.
End of explanation
"""


for fuel in ['HYC', 'WND', 'COW', 'NG', 'NUC', 'SUN']:
    state_total = annual_state.loc[2016, :, fuel]['generation (MWh)'].sum()
    facility_total = annual_facility.loc[2016, :, fuel]['generation (MWh)'].sum()
    
    error = (state_total - facility_total) / state_total
    print('{} has an error of {:.2f}%'.format(fuel, error * 100))

"""
Explanation: How much generation from large sources (Hydro, wind, coal, natural gas, and nuclear) is missed by monthly 923 data?
End of explanation
"""


path = os.path.join('Data storage', 'EIA923_Schedules_2_3_4_5_M_12_2015_Final.xlsx')
frequency = pd.read_excel(path, sheetname='Page 6 Plant Frame', header=4)

frequency.head()

frequency.rename(columns={'Plant Id': 'plant id',
                          'Plant State': 'state',
                          'YEAR': 'year',
                          'Reporting\nFrequency': 'Reporting Frequency'}, inplace=True)

frequency.head()

frequency.dtypes

"""
Explanation: 2015 generation and fuel consumption from annual vs monthly reporting plants
The goal here is to figure out how much of generation and fuel consumption from facilities that only report annually is in each NERC region (by state)
End of explanation
"""


freq_cols = ['year', 'plant id', 'Reporting Frequency']
df = pd.merge(facility_df, frequency.loc[:, freq_cols], on=['year', 'plant id'])

df.head()

g = sns.factorplot(x='fuel category', y='generation (MWh)', hue='Reporting Frequency',
                   col='NERC', col_wrap=3, data=df, estimator=np.sum, ci=0, kind='bar',
                   palette='tab10')

g.set_xticklabels(rotation=30)

"""
Explanation: Make a dataframe with generation, fuel consumption, and reporting frequency of facilities in 2015
End of explanation
"""


df.loc[df['state'] == 'TX', 'NERC'].nunique()

"""
Explanation: Number of NERC regions in a state
End of explanation
"""


def annual(df, state):
    """Return the percent of gen & consumption by fuel type in each NERC region
    for a state"""
    a = df.loc[(df.state == state) & 
                     (df['Reporting Frequency'] == 'A')].copy()
    a.drop(['plant id', 'year'], axis=1, inplace=True)
    a = a.groupby(['NERC', 'fuel category']).sum()
    
    fuels = set(a.index.get_level_values('fuel category'))
    
    temp_list = []
    for fuel in fuels:
        temp = (a.xs(fuel, level='fuel category')
                / a.xs(fuel, level='fuel category').sum())
        temp['fuel category'] = fuel
        temp_list.append(temp)
    
    result = pd.concat(temp_list)
    result.reset_index(inplace=True)
    result['state'] = state
    
    rename_cols = {'generation (MWh)': '% generation',
                   'total fuel (mmbtu)': '% total fuel',
                   'elec fuel (mmbtu)': '% elec fuel'}
    
    result.rename(columns=rename_cols, inplace=True)
    
    return result

def annual_month(df, state):
    """Return the percent of gen & consumption by fuel type and month in each 
    NERC region for a state"""
    a = df.loc[(df.state == state) & 
                     (df['Reporting Frequency'] == 'A')].copy()
    a.drop(['plant id', 'year'], axis=1, inplace=True)
    a = a.groupby(['NERC', 'fuel category', 'month']).sum()
    
    fuels = set(a.index.get_level_values('fuel category'))
    
    temp_list = []
    for fuel in fuels:
        for month in range(1, 13):
            temp = (a.xs(fuel, level='fuel category')
                     .xs(month, level='month')
                    / a.xs(fuel, level='fuel category')
                       .xs(month, level='month')
                       .sum())
            temp['fuel category'] = fuel
            temp['month'] = month
            temp_list.append(temp)
    
    result = pd.concat(temp_list)
    result.reset_index(inplace=True)
    result['state'] = state
    
    rename_cols = {'generation (MWh)': '% generation',
                   'total fuel (mmbtu)': '% total fuel',
                   'elec fuel (mmbtu)': '% elec fuel'}
    
    result.rename(columns=rename_cols, inplace=True)
    
    return result

"""
Explanation: Fraction of generation/consumption from Annual reporting facilities in each NERC region of a state
This is development of a method that will be used to approximate the fraction of EIA-estimated generation and consumption within each state that gets apportioned to each NERC regions (when there is more than one). The idea is to take data from the most recent "final" EIA-923 and use the annual reporting facilities to approximate the divisions for more recent data. I still need to figure out if it's better to do the calculation by month within a year or just for the year as a whole.
Determining if it's better to do month-by-month vs a single value for the whole year will depend on if the share of generation/consumption from Annual reporting facilities in each NERC changes much over the course of the year. There is the potential for error either way, and maybe even differences by state. Annual is certainly simpler.
While looking at data for Texas, I've discovered that generation from Annual reporting facilities can be negative. Need to figure out how (if?) to deal with this...
Conclusion
While there can be variation of % generation in each NERC within a state over the course of 2015, most fuel categories across most states are quite stable. And when fuels do a have a wide spread over the year, they also tend to not be a large fraction of total generation within the NERC region. Given these observations, I'm going to stick with a split calculated as the average over an entire year.
End of explanation
"""


df_list = []
for state in states:
    num_nerc = df.loc[df.state == state, 'NERC'].nunique()
    if num_nerc > 1:
        df_list.append(annual(df, state))

df_list = []
for state in states:
    num_nerc = df.loc[df.state == state, 'NERC'].nunique()
    if num_nerc > 1:
        df_list.append(annual_month(df, state))

fuel_by_nerc_month = pd.concat(df_list).reset_index(drop=True)

fuel_by_nerc = pd.concat(df_list).reset_index(drop=True)

fuel_by_nerc.head()

fuel_by_nerc_month.tail()

st

split_states = []
for state in states:
    if df.loc[df.state == state, 'NERC'].nunique() > 1:
        split_states.append(state)

split_states

cols = ['state', 'NERC', 'fuel category']
a = fuel_by_nerc_month.groupby(cols).std()
a.drop('month', axis=1, inplace=True)

a.xs('AR', level='state')

a[a > .1].dropna(how='all')

fuels = ['NG', 'HYC', 'COW', 'GEO', 'WND', 'SUN']

sns.factorplot(x='month', y='% generation', hue='fuel category', col='NERC',
               row='state',
               data=fuel_by_nerc_month.loc[(fuel_by_nerc_month['fuel category'].isin(fuels)) &
                                           (fuel_by_nerc_month['NERC'] != '-')],
               n_boot=1)
path = os.path.join('Figures', 'SI', 'Annual facility seasonal gen variation.pdf')
# plt.savefig(path, bbox_inches='tight')

fuel_by_nerc_month.loc[(fuel_by_nerc_month.state=='TX') &
                       (fuel_by_nerc_month['fuel category'] == 'WWW')]

df.loc[(df.state == 'TX') &
       (df['fuel category'] == 'WWW') &
       (df['Reporting Frequency'] == 'A')].groupby(['NERC', 'month', 'fuel category']).sum()

df.loc[(df.state == 'TX') &
       (df['fuel category'] == 'WWW') &
       (df['Reporting Frequency'] == 'A')].groupby(['NERC', 'fuel category']).sum()

"""
Explanation: This is the percent of generation, total fuel consumption, and electric fuel consumption from facilities that report annually to EIA-923
End of explanation
"""


NERC_states = ['WY', 'SD', 'NE', 'OK', 'TX', 'NM', 'LA', 'AR',
               'MO', 'MN', 'IL', 'KY', 'VA', 'FL']

error_list = []
for state in NERC_states:
    error = (annual_state.loc[2016, state]
             - annual_facility.loc[2016, state]) / annual_state.loc[2016, state]
    error['state'] = state
    
    for col in ['generation (MWh)']:#, 'elec fuel (mmbtu)']:
        if error.loc[error[col] > 0.05, col].any():
            error_list.append(error.loc[error[col] > 0.05])

"""
Explanation: States that include more than one NERC region
End of explanation
"""


pd.concat(error_list)

"""
Explanation: The dataframe below shows all states with more than one NERC region where facility generation is at least 5% below EIA's state-level estimate in 2016.
End of explanation
"""


