emissions = pd.read_csv(fn1)

emissions.head()

emissions = pd.read_csv(fn1, index_col=False)

emissions.head()

emissions.tail()

"""
Explanation: Load emissions data
End of explanation
"""


emissions.columns

"""
Explanation: Access parts of the dataframe
End of explanation
"""


columns_strip = [name.strip() for name in emissions.columns]
columns_strip

emissions.columns = columns_strip
emissions.columns

emissions.dtypes

"""
Explanation: Notice that most of the columns have a leading space?
End of explanation
"""


type(emissions)

type(emissions['Operating Time'])

emissions['Operating Time']

"""
Explanation: A single column from a dataframe is called a Series
End of explanation
"""


emissions.loc[0:5,'Operating Time']

emissions.iloc[0:5,:3]

"""
Explanation: Index into a dataframe using .loc or .iloc with square brackets and row,column notation
End of explanation
"""


emissions.groupby('Facility ID (ORISPL)')

"""
Explanation: Sum unit emissions for each facility using groupby
End of explanation
"""


facility_emiss = emissions.groupby('Facility ID (ORISPL)').sum()
# facility_emiss = facility_emiss.iloc[:,2:]
facility_emiss

"""
Explanation: Not all columns sum well
End of explanation
"""


def correct_region(row):
    num_units = row['Month'] / 5
    region = row['EPA Region'] / num_units
    return region

"""
Explanation: Use apply to apply a function to every row of the dataframe
If we want to keep the EPA Region, there are probably better ways to do it than this. We will write a little function that divides the month by 5 (May) and then divides the region by that result.
End of explanation
"""


facility_emiss.apply(correct_region, axis=1)

facility_emiss.loc[:,'EPA Region'] = facility_emiss.apply(correct_region, axis=1)
facility_emiss = facility_emiss.iloc[:,2:]
facility_emiss

"""
Explanation: Not sure why this is returning a float. Go back to the function and return an int instead.
End of explanation
"""


capacity = pd.read_excel(fn2, sheetname='Operating', header=1)

capacity.head()

capacity.tail()

capacity.drop(20187, inplace=True)

capacity.loc[:,'Plant ID'] = capacity.loc[:,'Plant ID'].astype(int)

capacity.head()

"""
Explanation: Load capacity data
End of explanation
"""


capacity.columns

capacity.columns = [name.strip() for name in capacity.columns]
capacity.columns

"""
Explanation: Check the column names
Sure enough, there are some weird issues
End of explanation
"""


PA_cap = capacity.loc[capacity['Plant State']=='PA',:]
PA_cap

PA_NGCC_cap = capacity.loc[(capacity['Plant State']=='PA') &
             (capacity['Technology']=='Natural Gas Fired Combined Cycle'),:]
PA_NGCC_cap

"""
Explanation: Boolean filtering
End of explanation
"""


cols = ['Plant ID', 'Nameplate Capacity (MW)']
facility_cap = capacity.loc[:,cols].groupby('Plant ID').sum()
facility_cap

"""
Explanation: Repeat groupby and sum to get capacity of facilities
End of explanation
"""


generation = pd.read_excel(fn3, header=5)

"""
Explanation: Load generation data
End of explanation
"""


generation.describe()

generation.head()

"""
Explanation: Something weird is going on here. I know there are lots of rows with numeric data that are missing from this describe table.
End of explanation
"""


generation.tail()

generation.replace('.', 0, inplace=True)

"""
Explanation: Turns out that there are lots of dots (.) where it is no value. I'm going to replace these with zeros.
End of explanation
"""


generation.columns

generation.columns = [name.strip().replace('\n', ' ') for name in generation.columns]
generation.columns

"""
Explanation: There are line breaks in the middle of column names. I don't see any breaks or spaces at the beginning or end of names, but will still strip just to be safe.
End of explanation
"""


# could have done this as a list comprehension, but it would have been harder to read
months = []
for name in generation.columns:
    if 'Netgen' in name:
        month = name.split()[-1]
        months.append(month)
months

id_cols = ['Plant Id', 'Plant State', 'NERC Region', 'AER Fuel Type Code']
monthly_cols = []
def find_col_names(cols):
    for col in cols:
        if 'January' in col:
            monthly_cols.append(col.split()[0])

find_col_names(generation.columns)
id_cols + monthly_cols

pd.DataFrame(columns=id_cols + monthly_cols + ['Month'])

gen_list = []
for month in months:
    gen_df = pd.DataFrame(columns=id_cols + monthly_cols)
    
    # Took me a few tries to figure out that I couldn't use .loc for gen_df
    gen_df[id_cols] = generation.loc[:,id_cols]
    gen_df['Month'] = month
    
    for col in monthly_cols:
        gen_df.loc[:,col] = generation.loc[:,col + ' ' + month]
    
    gen_list.append(gen_df)

gen_stack = pd.concat(gen_list)
gen_stack.describe()

"""
Explanation: Stack data by month rather than having multiple columns
Not sure if I'll have time for this section
I'm lazy and want to get a list of month names without typing them all
End of explanation
"""


gen_stack['AER Fuel Type Code'].unique()

non_combust = ['HYC', 'NUC', 'SUN', 'GEO', 'WND'] # might be incomplete

def tag_combust(row):
    if row['AER Fuel Type Code'] in non_combust:
        return 0
    else:
        return 1

gen_stack['Combust'] = gen_stack.apply(tag_combust, axis=1)

gen_stack.head()

"""
Explanation: Tag lines as using a combustion fuel or not
End of explanation
"""


test = gen_stack.loc[gen_stack['Month']=='May',:].groupby(['Plant Id', 'NERC Region']).sum()
test.head()

test.reset_index('NERC Region')

facility_gen = gen_stack.loc[gen_stack['Month']=='May',:].groupby('Plant Id').sum()

facility_gen.head()

"""
Explanation: Now group and sum
Only keep data for May
End of explanation
"""


facility_gen = gen_stack.loc[gen_stack['Month']=='May',:].groupby(['Plant Id', 'NERC Region']).sum()
facility_gen.head()

facility_gen.reset_index('NERC Region', inplace=True)
facility_gen.head()

"""
Explanation: If I want to keep the NERC Region, I can do that in the groupby
End of explanation
"""


merged = facility_gen.merge(facility_cap, how='inner', left_index=True, right_index=True)

merged.describe()

"""
Explanation: Merge data from all three sources
End of explanation
"""


non_combust = merged.loc[merged['Combust']==0,:]
non_combust.describe()

non_combust.head()

merged = merged.merge(facility_emiss, how='inner', left_index=True, right_index=True)
merged.describe()

merged.head()

"""
Explanation: Save the non-combustion units, because I'm going to join the merged dataframe with the emissions dataframe and want to add back in the non-combustion
End of explanation
"""


final = pd.concat([merged, non_combust])
final

final.index.rename('Plant ID', inplace=True)

final.describe()

final['CO2 (short tons)'].sum() * 2000 * 2.2046 / final['Netgen'].sum()

"""
Explanation: Now concat the two dataframes
End of explanation
"""


