%matplotlib inline
import matplotlib.pyplot as plt
import seaborn as sns
import io, time, json
import requests
from bs4 import BeautifulSoup
import pandas as pd
import urllib
import re
import os
import numpy as np
from scripts import facility_line_to_df
from joblib import Parallel, delayed

"""
Explanation: Extract facility generation and fuel use data
This notebook creates dataframes with monthly facility generation and fuel use data, merges them, and exports the results. The code assumes that you have already downloaded an ELEC.txt file from EIA's bulk download website.
End of explanation
"""


path = os.path.join('Raw data', 'Electricity data', '2017-03-15 ELEC.txt')
with open(path, 'rb') as f:
    raw_txt = f.readlines()

"""
Explanation: Read ELEC.txt file
End of explanation
"""


gen_rows = [row for row in raw_txt if 'ELEC.PLANT.GEN' in row and 'series_id' in row and 'ALL.M' in row and 'ALL-' not in row]
total_fuel_rows = [row for row in raw_txt if 'ELEC.PLANT.CONS_TOT_BTU' in row and 'series_id' in row and 'ALL.M' in row and 'ALL-' not in row]
eg_fuel_rows = [row for row in raw_txt if 'ELEC.PLANT.CONS_EG_BTU' in row and 'series_id' in row and 'ALL.M' in row and 'ALL-' not in row]

"""
Explanation: Filter lines  to only include facility generation

Include ELEC.PLANT in the series_id
Include "All" as the only allowable prime mover
Some facilities have incorrect data at the individual prime mover level


Do not include "All" as a fuel code
Only monthly frequency
End of explanation
"""


if __name__ == '__main__':
    exception_list = []
    facility_gen = pd.concat(Parallel(n_jobs=-1)(delayed(facility_line_to_df)(json.loads(row)) for row in gen_rows))
    facility_gen.reset_index(drop=True, inplace=True)
    facility_gen.rename_axis({'value':'generation (MWh)'}, axis=1, inplace=True)

facility_gen.loc[:,'lat'] = facility_gen.loc[:,'lat'].astype(float)
facility_gen.loc[:,'lon'] = facility_gen.loc[:,'lon'].astype(float)
facility_gen.loc[:, 'plant id'] = facility_gen.loc[:, 'plant id'].astype(int)

#drop
facility_gen.tail()

#drop
facility_gen['fuel'].unique(), facility_gen['prime mover'].unique()

#drop
path = os.path.join('Clean data', 'Facility generation.csv')
facility_gen.to_csv(path, index=False)

"""
Explanation: Combine generation into one large dataframe
End of explanation
"""


if __name__ == '__main__':
    exception_list = []
    facility_all_fuel = pd.concat(Parallel(n_jobs=-1)(delayed(facility_line_to_df)(json.loads(row)) for row in total_fuel_rows))
    facility_all_fuel.reset_index(drop=True, inplace=True)
    facility_all_fuel.rename_axis({'value':'total fuel (mmbtu)'}, axis=1, inplace=True)

facility_all_fuel.loc[:,'lat'] = facility_all_fuel.loc[:,'lat'].astype(float)
facility_all_fuel.loc[:,'lon'] = facility_all_fuel.loc[:,'lon'].astype(float)
facility_all_fuel.loc[:,'plant id'] = facility_all_fuel.loc[:,'plant id'].astype(int)

#drop
facility_all_fuel.head()

#drop
path = os.path.join('Clean data', 'Facility total fuel consumption.csv')
facility_all_fuel.to_csv(path, index=False)

"""
Explanation: Combine total fuel use into one large dataframe
End of explanation
"""


if __name__ == '__main__':
    exception_list = []
    facility_eg_fuel = pd.concat(Parallel(n_jobs=-1)(delayed(facility_line_to_df)(json.loads(row)) for row in eg_fuel_rows))
    facility_eg_fuel.reset_index(drop=True, inplace=True)
    facility_eg_fuel.rename_axis({'value':'elec fuel (mmbtu)'}, axis=1, inplace=True)

facility_eg_fuel.loc[:,'lat'] = facility_eg_fuel.loc[:,'lat'].astype(float)
facility_eg_fuel.loc[:,'lon'] = facility_eg_fuel.loc[:,'lon'].astype(float)
facility_eg_fuel.loc[:,'plant id'] = facility_eg_fuel.loc[:,'plant id'].astype(int)

#drop
facility_eg_fuel.tail()

#drop
path = os.path.join('Clean data', 'Facility electric fuel consumption.csv')
facility_eg_fuel.to_csv(path, index=False)

"""
Explanation: Combine total fuel use for electricity into one large dataframe
End of explanation
"""


#drop
path1 = os.path.join('Clean data', 'Facility generation.csv')
path2 = os.path.join('Clean data', 'Facility total fuel consumption.csv')
path3 = os.path.join('Clean data', 'Facility electric fuel consumption.csv')

#drop
facility_gen = pd.read_csv(path1)

#drop
facility_gen.head()

#drop
facility_all_fuel = pd.read_csv(path2)

#drop
facility_eg_fuel = pd.read_csv(path3)

"""
Explanation: Reload dataframes from files
Goal is to merge the fuel use and fuel use for electricity back into the generation dataframe
End of explanation
"""


keep_cols = ['fuel', 'generation (MWh)', 'month', 'plant id', 'prime mover', 'year',
             'geography', 'lat', 'lon', 'last_updated']
merge_cols = ['fuel', 'month', 'plant id', 'year']
gen_total_fuel = facility_all_fuel.merge(facility_gen.loc[:,keep_cols], 
                                    how='outer', on=merge_cols)

#drop
gen_total_fuel.loc[gen_total_fuel['geography_x'].isnull()].head()

"""
Explanation: Merge dataframes
Need to be careful here because there are fuel/prime mover combinations that have generation but no fuel use (e.g. the steam cycle of a combined cycle system - but only in some cases).
End of explanation
"""


def fill_missing(df):
    cols = [col[:-2] for col in df.columns if '_x' in col]
    
    # Create new column from the _x version, fill missing values from the _y version
    for col in cols:
        df[col] = df.loc[:, col + '_x']
        df.loc[df[col].isnull(), col] = df.loc[df[col].isnull(), col + '_y']
        
        df.drop([col+'_x', col+'_y'], axis=1, inplace=True)

fill_missing(gen_total_fuel)

#drop
gen_total_fuel.loc[gen_total_fuel['geography'].isnull()]

keep_cols = ['fuel', 'elec fuel (mmbtu)', 'month', 'plant id', 'prime mover', 'year',
             'geography', 'lat', 'lon', 'last_updated']
all_facility_data = gen_total_fuel.merge(facility_eg_fuel.loc[:,keep_cols], 
                                         how='outer', on=merge_cols)

"""
Explanation: Fill in missing values from the first merge
End of explanation
"""


fill_missing(all_facility_data)

#drop
all_facility_data.loc[all_facility_data['geography'].isnull()]

all_facility_data.drop(['units', 'series_id'], axis=1, inplace=True)
all_facility_data.head()

#drop
all_facility_data.dtypes

"""
Explanation: FIll in missing values from second merge and drop units/series_id columns
End of explanation
"""


all_facility_data['datetime'] = pd.to_datetime(all_facility_data['year'].astype(str) + 
                                               '-' + all_facility_data['month'].astype(str), 
                                               format='%Y-%m')
all_facility_data['quarter'] = all_facility_data['datetime'].dt.quarter

#drop
all_facility_data.head()

"""
Explanation: Add datetime and quarter columns
End of explanation
"""


path = os.path.join('Clean data', 'Final emission factors.csv')
ef = pd.read_csv(path, index_col=0)

"""
Explanation: Load emission factors
These are mostly EIA emission factors
End of explanation
"""


fossil_factors = dict(zip(ef.index, ef['Fossil Factor']))
total_factors = dict(zip(ef.index, ef['Total Factor']))
fossil_factors, total_factors

"""
Explanation: Apply factors to facility generation
End of explanation
"""


# Start with 0 emissions in all rows
# For fuels where we have an emission factor, replace the 0 with the calculated value
all_facility_data['all fuel fossil CO2 (kg)'] = 0
all_facility_data['elec fuel fossil CO2 (kg)'] = 0
all_facility_data['all fuel total CO2 (kg)'] = 0
all_facility_data['elec fuel total CO2 (kg)'] = 0
for fuel in total_factors.keys():
    # All fuel CO2 emissions
    all_facility_data.loc[all_facility_data['fuel']==fuel,'all fuel fossil CO2 (kg)'] = \
        all_facility_data.loc[all_facility_data['fuel']==fuel,'total fuel (mmbtu)'] * fossil_factors[fuel]      
    all_facility_data.loc[all_facility_data['fuel']==fuel,'all fuel total CO2 (kg)'] = \
        all_facility_data.loc[all_facility_data['fuel']==fuel,'total fuel (mmbtu)'] * total_factors[fuel]
    
    # Electric fuel CO2 emissions
    all_facility_data.loc[all_facility_data['fuel']==fuel,'elec fuel fossil CO2 (kg)'] = \
        all_facility_data.loc[all_facility_data['fuel']==fuel,'elec fuel (mmbtu)'] * fossil_factors[fuel]
    all_facility_data.loc[all_facility_data['fuel']==fuel,'elec fuel total CO2 (kg)'] = \
        all_facility_data.loc[all_facility_data['fuel']==fuel,'elec fuel (mmbtu)'] * total_factors[fuel]

"""
Explanation: Apply emission factors
Fuel emission factor is kg/mmbtu
End of explanation
"""


# Fossil CO2
all_facility_data.loc[~(all_facility_data['all fuel fossil CO2 (kg)']>=0),
                      'all fuel fossil CO2 (kg)'] = 0
all_facility_data.loc[~(all_facility_data['elec fuel fossil CO2 (kg)']>=0),
                      'elec fuel fossil CO2 (kg)'] = 0
# Total CO2
all_facility_data.loc[~(all_facility_data['all fuel total CO2 (kg)']>=0),
                      'all fuel total CO2 (kg)'] = 0
all_facility_data.loc[~(all_facility_data['elec fuel total CO2 (kg)']>=0),
                      'elec fuel total CO2 (kg)'] = 0

"""
Explanation: Set nan and negative emissions to 0
When no fuel was used for electricity production, or when negative fuel is somehow reported by EIA, set the emissions to 0. This is implemented by filtering out all values that are greater than or equal to 0.
End of explanation
"""


path = os.path.join('Clean data', 'Facility gen fuels and CO2.csv')
all_facility_data.to_csv(path, index=False)

"""
Explanation: Export
End of explanation
"""


