<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Combine national and NERC data
Take the various file (EIA facilities, EIA state-level totals, EPA emissions, etc) and combine them to determine total generation, CO₂ emissions, etc nationally and at NERC regions.
Instructions
Make sure the file_date parameter below is set to whatever value you would like appended to file names.
In Calculate the extra gen/fuel consumption at the state levels add years after 2017 in the for loop if needed.
In Fraction of estimated gen/fuels in each NERC region update the years that plant ids for annual plants are fetched from EIA-923 files.
The entire notebook can be run at once using Run All Cells
Step1:  Date string for filenames
This will be inserted into all filenames (reading and writing)
Step2:  Load data
Step3:  Adjusted facility emissions and generation
I have both the state and custom fuel categories here, but am only using the state fuel categories for now.
Step4:  Extra gen/fuels from non-reporting
Step5:  Total EIA generation/fuel consumption and emission factors
Step6:  Calculate CO₂, generation, and fuel consumption that is not captured by facilities
Step7:  Total CO₂ (national)
Combine adjusted CO₂ at facilities and CO₂ from fuel consumption that is not captured by facility data.
Step8:  National Index and gen by fuels
Total national generation (all fuels)
Add generation/fuel consumption reported by facilities (with state-level fuel codes) and extra generation/fuel consumption (which also uses state-level fuel codes).
Step9:  Regroup generation from state codes to my custom fuel codes
Step10:  National Index
Step11:  Percent generation by custom fuel type
Step12:  Facility state and lat/lon file generation
~~Create a .csv with lat/lon and state code for each facility. Need to manually add the NERC region label.~~ ~~I've done this with a spatial join between the lat/lon and NERC shapefiles in QGIS. It should be possible to do the spatial join in GeoPandas.  Could also add custom region labels (e.g. eGRID subregions, ISO/RTO boundaries, etc).~~
This isn't possible with a spatial join because NERC regions aren't strictly based on geography. Instead, I've taken the NERC codes from EIA-860 and assigned unknown plants (mostly those that retired before 2012 when modern NERCs were mostly defined, and those that were assigned a plant id in 2017) NERC labels using a k-nearest neighbors algorithm and lat/lon information.
See the Assign NERC region labels notebook for the creation of this file.
Fraction of estimated gen/fuels in each NERC region
NOTE EIA changed the reporting requirements for wind and solar facilities in 2017. I'm now going to use the list of annual facilities from 2017 rather than from 2015, but still use the reported generation by those facilities in 2015.
Step13:  Combine the lists of annual plants in 2015 and 2017. This lets us catch facilities that have gone from monthly to annual since 2015, but it also includes plants that were annual in 2015 an may have retired.
There is the possibility of an error in allocation to NERC regions for 2016 state-level generation when using the facilities that changed to annual in 2017. But since the state-level generation is so much smaller in 2016 I don't think this is much of an issue.
Step14:  Add NERC region labels
Step15:  Added the filter that year must be 2015 - was getting all 2015 annual plants, but for all years!
Step16:  This is 2015 data on annual reporting facilities (from both 2015 and 2017)
Step17:  With the values below I can allocate extra state-level generation and fuel use to each of the NERC regions!
Making sure that no values are greater than 1 (within tolerance)
Step18:  Allocate extra gen from the state-level to regions
I still need to generate state-level total generation and fuel use!
Step19:  Load state-level total gen/fuel consumption
Step20:  Simplify the dataframe
Step21:  Group the facility data to state fuel categories and add state labels
Step22:  Calculate the extra gen/fuel consumption at the state levels
Only worrying about extra generation from 2016 forward.
Step23:  Sort the index of each dataframe to make sure they can be easily combined.
Step24:  Create a copy of the nerc_fraction dataframe with repeated values for every month of the year, so that they MultiIndex matches the state_extra MultiIndex
Step25:  Cycle through each year (2016 and 2017 in this case) and each NERC, multiplying the state-level extra generation, total fuel consumption, and fuel consumption for electricity by the share that should be allocated to each NERC.
Step26:  Although the dataframe is called final, it's really just the final allocated extra state-level generation/fuel consumption
<ASSISTANT_TASK:>
Python Code:
import pandas as pd
import numpy as np
import os
from os.path import join
import sys
import json
idx = pd.IndexSlice
cwd = os.getcwd()
data_path = join(cwd, '..', 'Data storage')
Explanation: Combine national and NERC data
Take the various file (EIA facilities, EIA state-level totals, EPA emissions, etc) and combine them to determine total generation, CO₂ emissions, etc nationally and at NERC regions.
Instructions
Make sure the file_date parameter below is set to whatever value you would like appended to file names.
In Calculate the extra gen/fuel consumption at the state levels add years after 2017 in the for loop if needed.
In Fraction of estimated gen/fuels in each NERC region update the years that plant ids for annual plants are fetched from EIA-923 files.
The entire notebook can be run at once using Run All Cells
End of explanation
file_date = '2018-03-06'
%load_ext watermark
%watermark -iv -v
# Load the "autoreload" extension
%load_ext autoreload
# always reload modules marked with "%aimport"
%autoreload 1
# add the 'src' directory as one where we can import modules
src_dir = join(os.getcwd(), os.pardir, 'src')
sys.path.append(src_dir)
%aimport Data.make_data
from Data.make_data import states_in_nerc
%aimport Analysis.index
from Analysis.index import facility_emission_gen, group_facility_data, add_quarter
%aimport Analysis.index
from Analysis.index import g2lb, change_since_2005, generation_index
%aimport Analysis.index
from Analysis.index import facility_co2, adjust_epa_emissions, group_fuel_cats
%aimport util.utils
from util.utils import rename_cols, add_facility_location
Explanation: Date string for filenames
This will be inserted into all filenames (reading and writing)
End of explanation
cwd = os.getcwd()
path = join(data_path, 'Derived data',
            'Facility gen fuels and CO2 {}.csv'.format(file_date))
eia_fac = pd.read_csv(path)
rename_cols(eia_fac)
path = join(data_path, 'Derived data',
            'Monthly EPA emissions {}.csv'.format(file_date))
epa = pd.read_csv(path)
Explanation: Load data
End of explanation
fuel_cat_folder = join(data_path, 'Fuel categories')
state_cats_path = join(fuel_cat_folder, 'State_facility.json')
with open(state_cats_path, 'r') as f:
    state_fuel_cat = json.load(f)
    
custom_cats_path = join(fuel_cat_folder, 'Custom_results.json')
with open(custom_cats_path, 'r') as f:
    custom_fuel_cat = json.load(f)
co2, gen_fuels_state = facility_emission_gen(eia_facility=eia_fac, epa=epa,
                                              state_fuel_cat=state_fuel_cat,
                                              custom_fuel_cat=custom_fuel_cat,
                                              export_state_cats=True)
Explanation: Adjusted facility emissions and generation
I have both the state and custom fuel categories here, but am only using the state fuel categories for now.
End of explanation
%aimport Analysis.index
from Analysis.index import extra_emissions_gen
Explanation: Extra gen/fuels from non-reporting
End of explanation
cwd = os.getcwd()
path = join(data_path, 'Derived data',
            'EIA country-wide gen fuel CO2 {}.csv'.format(file_date))
eia_total = pd.read_csv(path)
path = join(data_path,
            'Final emission factors.csv')
ef = pd.read_csv(path, index_col=0)
Explanation: Total EIA generation/fuel consumption and emission factors
End of explanation
extra_co2, extra_gen_fuel = extra_emissions_gen(gen_fuels_state, eia_total, ef)
Explanation: Calculate CO₂, generation, and fuel consumption that is not captured by facilities
End of explanation
facility_co2 = co2.groupby(['year', 'month']).sum()
national_co2 = (facility_co2.loc[:, 'final co2 (kg)']
                + extra_co2.loc[:, 'elec fuel co2 (kg)']
                           .groupby(['year', 'month']).sum())
national_co2.name = 'final co2 (kg)'
Explanation: Total CO₂ (national)
Combine adjusted CO₂ at facilities and CO₂ from fuel consumption that is not captured by facility data.
End of explanation
gen_fuels_state.groupby(['type', 'year', 'month']).sum().head()
national_gen = (gen_fuels_state
                .groupby(['type', 'year', 'month'])['generation (mwh)'].sum()
                .add(extra_gen_fuel['generation (mwh)'], fill_value=0))
Explanation: National Index and gen by fuels
Total national generation (all fuels)
Add generation/fuel consumption reported by facilities (with state-level fuel codes) and extra generation/fuel consumption (which also uses state-level fuel codes).
End of explanation
national_gen = group_fuel_cats(national_gen.reset_index(), custom_fuel_cat,
                               'type', 'fuel category').set_index(['fuel category', 'year', 'month'])
total_gen = national_gen.groupby(['year', 'month']).sum()
Explanation: Regroup generation from state codes to my custom fuel codes
End of explanation
national_index = total_gen.copy()
national_index['final co2 (kg)'] = national_co2
national_index['index (g/kwh)'] = (national_index['final co2 (kg)']
                                   / national_index['generation (mwh)'])
national_index.reset_index(inplace=True)
add_quarter(national_index)
g2lb(national_index)
change_since_2005(national_index)
path = join(data_path, 'National data',
            'National index {}.csv'.format(file_date))
national_index.to_csv(path, index=False)
Explanation: National Index
End of explanation
df_list = []
for fuel in national_gen.index.get_level_values('fuel category').unique():
    percent_gen = national_gen.loc[fuel].divide(total_gen, fill_value=0)
    percent_gen['fuel category'] = fuel
    percent_gen.set_index('fuel category', inplace=True, append=True)
    df_list.append(percent_gen)
percent_gen = pd.concat(df_list)
path = join(data_path, 'National data',
            'National generation {}.csv'.format(file_date))
national_gen.to_csv(path)
path = join(data_path, 'National data',
            'National percent gen {}.csv'.format(file_date))
percent_gen.to_csv(path)
Explanation: Percent generation by custom fuel type
End of explanation
%aimport Data.make_data
from Data.make_data import get_annual_plants
annual_ids_2015 = get_annual_plants(2015)
annual_ids_2017 = get_annual_plants(2017)
Explanation: Facility state and lat/lon file generation
~~Create a .csv with lat/lon and state code for each facility. Need to manually add the NERC region label.~~ ~~I've done this with a spatial join between the lat/lon and NERC shapefiles in QGIS. It should be possible to do the spatial join in GeoPandas.  Could also add custom region labels (e.g. eGRID subregions, ISO/RTO boundaries, etc).~~
This isn't possible with a spatial join because NERC regions aren't strictly based on geography. Instead, I've taken the NERC codes from EIA-860 and assigned unknown plants (mostly those that retired before 2012 when modern NERCs were mostly defined, and those that were assigned a plant id in 2017) NERC labels using a k-nearest neighbors algorithm and lat/lon information.
See the Assign NERC region labels notebook for the creation of this file.
Fraction of estimated gen/fuels in each NERC region
NOTE EIA changed the reporting requirements for wind and solar facilities in 2017. I'm now going to use the list of annual facilities from 2017 rather than from 2015, but still use the reported generation by those facilities in 2015.
End of explanation
annual_ids = set(annual_ids_2015.tolist() + annual_ids_2017.tolist())
len(annual_ids)
Explanation: Combine the lists of annual plants in 2015 and 2017. This lets us catch facilities that have gone from monthly to annual since 2015, but it also includes plants that were annual in 2015 an may have retired.
There is the possibility of an error in allocation to NERC regions for 2016 state-level generation when using the facilities that changed to annual in 2017. But since the state-level generation is so much smaller in 2016 I don't think this is much of an issue.
End of explanation
%aimport Analysis.state2nerc
from Analysis.state2nerc import fraction_state2nerc, add_region
cwd = os.getcwd()
path = join(data_path, 'Facility labels',
            'Facility locations_RF.csv')
location_labels = pd.read_csv(path)
nerc_state_path = join(data_path, 'Derived data',
                  'NERC_states.json')
with open(nerc_state_path, 'r') as f:
    nerc_states = json.load(f)
Explanation: Add NERC region labels
End of explanation
eia_2015_annual = eia_fac.loc[(eia_fac['plant id'].isin(annual_ids)) & 
                              (eia_fac['year'] == 2015)].copy()
# Group to state-level fuel categories
eia_2015_annual = group_fuel_cats(eia_2015_annual, state_fuel_cat)
eia_2015_annual_nerc = add_facility_location(eia_2015_annual, location_labels, 
                                        labels=['state', 'nerc', 'year'])
eia_2015_annual_nerc.head()
Explanation: Added the filter that year must be 2015 - was getting all 2015 annual plants, but for all years!
End of explanation
# Get a list of all state abbreviations
all_states = []
for value in nerc_states.values():
    all_states.extend(value)
all_states = set(all_states)
df_list = []
for state in all_states:
    try:
        df_list.append(fraction_state2nerc(eia_2015_annual_nerc,
                       state, region_col='nerc', fuel_col='type'))
    except:
        print(state)
        pass
nerc_fraction = pd.concat(df_list)
nerc_fraction.set_index(['state', 'nerc', 'type'], inplace=True)
nerc_fraction.sort_index(inplace=True)
Explanation: This is 2015 data on annual reporting facilities (from both 2015 and 2017)
End of explanation
(nerc_fraction.groupby(['state', 'type']).sum() > 1.0001).any()
Explanation: With the values below I can allocate extra state-level generation and fuel use to each of the NERC regions!
Making sure that no values are greater than 1 (within tolerance)
End of explanation
idx = pd.IndexSlice
# a dictionary to match column names
nerc_frac_match = {'% generation': 'generation (mwh)',
                   '% total fuel': 'total fuel (mmbtu)',
                   '% elec fuel': 'elec fuel (mmbtu)'}
Explanation: Allocate extra gen from the state-level to regions
I still need to generate state-level total generation and fuel use!
End of explanation
path = join(data_path, 'Derived data',
            'EIA state-level gen fuel CO2 {}.csv'.format(file_date))
state_total = pd.read_csv(path, parse_dates=['datetime'])
rename_cols(state_total)
state_total['state'] = state_total['geography'].str[-2:]
Explanation: Load state-level total gen/fuel consumption
End of explanation
cols = list(nerc_frac_match.values())
state_total = state_total.groupby(['state', 'year', 'month', 'type'])[cols].sum()
# list of NERC regions
nercs = nerc_fraction.index.get_level_values('nerc').unique()
Explanation: Simplify the dataframe
End of explanation
cols = list(nerc_frac_match.values())
eia_fac_type = group_fuel_cats(eia_fac, state_fuel_cat)
eia_fac_type = add_facility_location(eia_fac_type, location_labels, ['state', 'year'])
eia_fac_type = eia_fac_type.groupby(['state', 'year', 'month', 'type'])[cols].sum()
Explanation: Group the facility data to state fuel categories and add state labels
End of explanation
state_extra = (state_total.loc[idx[:, 2016:, :, :], :]
               - eia_fac_type.loc[idx[:, 2016:, :, :], :])
state_extra.dropna(how='all', inplace=True)
state_extra = state_extra.reorder_levels(['year', 'state', 'month', 'type'])
state_extra.sort_index(inplace=True)
Explanation: Calculate the extra gen/fuel consumption at the state levels
Only worrying about extra generation from 2016 forward.
End of explanation
nerc_fraction.sort_index(inplace=True)
state_extra.sort_index(inplace=True)
Explanation: Sort the index of each dataframe to make sure they can be easily combined.
End of explanation
df_list = []
for month in range(1, 13):
    df = nerc_fraction.copy()
    df['month'] = month
    df.set_index('month', append=True, inplace=True)
    df_list.append(df)
nerc_frac_monthly = pd.concat(df_list, axis=0)
nerc_frac_monthly.sort_index(inplace=True)
nerc_frac_monthly = (nerc_frac_monthly
                     .reorder_levels(['nerc', 'state', 'month', 'type']))
Explanation: Create a copy of the nerc_fraction dataframe with repeated values for every month of the year, so that they MultiIndex matches the state_extra MultiIndex
End of explanation
df_list_outer = []
for year in [2016, 2017]:
    df_list_inner = []
    for nerc in nercs:
        df = pd.concat([(nerc_frac_monthly
                         .loc[nerc]['% generation']
                         * state_extra
                         .loc[year]['generation (mwh)']).dropna(),
                        (nerc_frac_monthly.
                         loc[nerc]['% total fuel']
                         * state_extra
                         .loc[year]['total fuel (mmbtu)']).dropna(),
                        (nerc_frac_monthly
                         .loc[nerc]['% elec fuel']
                         * state_extra
                         .loc[year]['elec fuel (mmbtu)']).dropna()],
                        axis=1)
        df.columns = nerc_frac_match.values()
        df['nerc'] = nerc
        df['year'] = year
        df = df.groupby(['year', 'nerc', 'month', 'type']).sum()
        df_list_inner.append(df)
    df_list_outer.append(pd.concat(df_list_inner))
final = pd.concat(df_list_outer)
final.sort_index(inplace=True)
nerc_frac_monthly.sort_index(inplace=True)
Explanation: Cycle through each year (2016 and 2017 in this case) and each NERC, multiplying the state-level extra generation, total fuel consumption, and fuel consumption for electricity by the share that should be allocated to each NERC.
End of explanation
path = join(data_path, 'Derived data',
            'NERC extra gen fuels {}.csv'.format(file_date))
final.to_csv(path)
Explanation: Although the dataframe is called final, it's really just the final allocated extra state-level generation/fuel consumption
End of explanation
<END_TASK>