The data in this notebook is generation and consumption by fuel type for the entire US. These values are larger than what would be calculated by summing facility-level data. Note that the fuel types are somewhat aggregated (coal rather than BIT, SUB, LIG, etc). So when we multiply the fuel consumption by an emissions factor there will be some level of error.
The code assumes that you have already downloaded an ELEC.txt
file from EIA's bulk download website.
%matplotlib inline
import matplotlib.pyplot as plt
import seaborn as sns
import io, time, json
import pandas as pd
import os
from os.path import join
import numpy as np
import math
idx = pd.IndexSlice
cwd = os.getcwd()
path = join(cwd, '..', 'Data storage', 'Raw EIA bulk',
'2017-08-31 ELEC_manual.txt')
with open(path, 'rb') as f:
raw_txt = f.readlines()
Only want monthly US data for all sectors
Fuel codes:
def line_to_df(line):
"""
Takes in a line (dictionary), returns a dataframe
"""
for key in ['latlon', 'source', 'copyright', 'description',
'geoset_id', 'iso3166', 'name', 'state']:
line.pop(key, None)
# Split the series_id up to extract information
# Example: ELEC.PLANT.GEN.388-WAT-ALL.M
series_id = line['series_id']
series_id_list = series_id.split('.')
# Use the second to last item in list rather than third
plant_fuel_mover = series_id_list[-2].split('-')
line['type'] = plant_fuel_mover[0]
# line['state'] = plant_fuel_mover[1]
line['sector'] = plant_fuel_mover[2]
temp_df = pd.DataFrame(line)
try:
temp_df['year'] = temp_df.apply(lambda x: x['data'][0][:4], axis=1).astype(int)
temp_df['month'] = temp_df.apply(lambda x: x['data'][0][-2:], axis=1).astype(int)
temp_df['value'] = temp_df.apply(lambda x: x['data'][1], axis=1)
temp_df.drop('data', axis=1, inplace=True)
return temp_df
except:
exception_list.append(line)
pass
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_geos = ['USA-{}'.format(state) for state in states]
type(json.loads(raw_txt[0]))
dict
json.loads(raw_txt[0])['geography']
u'USA-IL'
exception_list = []
gen_rows = [row for row in raw_txt if 'ELEC.GEN' in row
and 'series_id' in row
and '-99.M' in row
and 'ALL' not in row]
total_fuel_rows = [row for row in raw_txt if 'ELEC.CONS_TOT_BTU' in row
and 'series_id' in row
and '-99.M' in row
and 'ALL' not in row
and 'US-99.m' not in row]
eg_fuel_rows = [row for row in raw_txt if 'ELEC.CONS_EG_BTU' in row
and 'series_id' in row
and '-99.M' in row
and 'ALL' not in row
and 'US-99.m' not in row]
gen_dicts = [json.loads(row) for row in gen_rows]
gen_df = pd.concat([line_to_df(x) for x in gen_dicts
if x['geography'] in state_geos])
#drop
gen_df.head()
end | f | geography | last_updated | sector | series_id | start | type | units | year | month | value | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 201706 | M | USA-MN | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.AOR-MN-99.M | 200101 | AOR | thousand megawatthours | 2017 | 6 | 1066.98487 |
1 | 201706 | M | USA-MN | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.AOR-MN-99.M | 200101 | AOR | thousand megawatthours | 2017 | 5 | 1173.59609 |
2 | 201706 | M | USA-MN | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.AOR-MN-99.M | 200101 | AOR | thousand megawatthours | 2017 | 4 | 1250.62552 |
3 | 201706 | M | USA-MN | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.AOR-MN-99.M | 200101 | AOR | thousand megawatthours | 2017 | 3 | 1345.11365 |
4 | 201706 | M | USA-MN | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.AOR-MN-99.M | 200101 | AOR | thousand megawatthours | 2017 | 2 | 1222.31074 |
gen_df['geography'].unique()
array([u'USA-MN', u'USA-KY', u'USA-LA', u'USA-CA', u'USA-ME', u'USA-DE', u'USA-ID', u'USA-ND', u'USA-AR', u'USA-MI', u'USA-MA', u'USA-KS', u'USA-HI', u'USA-NH', u'USA-FL', u'USA-NC', u'USA-MD', u'USA-AK', u'USA-CO', u'USA-AZ', u'USA-AL', u'USA-NJ', u'USA-IL', u'USA-CT', u'USA-MT', u'USA-IA', u'USA-GA', u'USA-OK', u'USA-NY', u'USA-IN', u'USA-NV', u'USA-NM', u'USA-WA', u'USA-OH', u'USA-TX', u'USA-RI', u'USA-OR', u'USA-WY', u'USA-SD', u'USA-PA', u'USA-VA', u'USA-VT', u'USA-UT', u'USA-SC', u'USA-WI', u'USA-WV', u'USA-NE', u'USA-TN', u'USA-MO', u'USA-MS'], dtype=object)
Multiply generation values by 1000 and change the units to MWh
gen_df.loc[:,'value'] *= 1000
gen_df.loc[:,'units'] = 'megawatthours'
gen_df.rename_axis({'value':'generation (MWh)'}, axis=1, inplace=True)
gen_df.loc[gen_df.isnull().any(axis=1)]
end | f | geography | last_updated | sector | series_id | start | type | units | year | month | generation (MWh) | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
4 | 201706 | M | USA-AK | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.AOR-AK-99.M | 200101 | AOR | megawatthours | 2017 | 2 | NaN |
5 | 201706 | M | USA-AK | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.AOR-AK-99.M | 200101 | AOR | megawatthours | 2017 | 1 | NaN |
55 | 201706 | M | USA-AK | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.AOR-AK-99.M | 200101 | AOR | megawatthours | 2012 | 11 | NaN |
65 | 201706 | M | USA-AK | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.AOR-AK-99.M | 200101 | AOR | megawatthours | 2012 | 1 | NaN |
0 | 201706 | M | USA-ID | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.COW-ID-99.M | 200101 | COW | megawatthours | 2017 | 6 | NaN |
1 | 201706 | M | USA-ID | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.COW-ID-99.M | 200101 | COW | megawatthours | 2017 | 5 | NaN |
2 | 201706 | M | USA-ID | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.COW-ID-99.M | 200101 | COW | megawatthours | 2017 | 4 | NaN |
3 | 201706 | M | USA-ID | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.COW-ID-99.M | 200101 | COW | megawatthours | 2017 | 3 | NaN |
4 | 201706 | M | USA-ID | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.COW-ID-99.M | 200101 | COW | megawatthours | 2017 | 2 | NaN |
5 | 201706 | M | USA-ID | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.COW-ID-99.M | 200101 | COW | megawatthours | 2017 | 1 | NaN |
6 | 201706 | M | USA-ID | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.COW-ID-99.M | 200101 | COW | megawatthours | 2016 | 12 | NaN |
7 | 201706 | M | USA-ID | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.COW-ID-99.M | 200101 | COW | megawatthours | 2016 | 11 | NaN |
8 | 201706 | M | USA-ID | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.COW-ID-99.M | 200101 | COW | megawatthours | 2016 | 10 | NaN |
9 | 201706 | M | USA-ID | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.COW-ID-99.M | 200101 | COW | megawatthours | 2016 | 9 | NaN |
10 | 201706 | M | USA-ID | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.COW-ID-99.M | 200101 | COW | megawatthours | 2016 | 8 | NaN |
11 | 201706 | M | USA-ID | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.COW-ID-99.M | 200101 | COW | megawatthours | 2016 | 7 | NaN |
12 | 201706 | M | USA-ID | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.COW-ID-99.M | 200101 | COW | megawatthours | 2016 | 6 | NaN |
13 | 201706 | M | USA-ID | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.COW-ID-99.M | 200101 | COW | megawatthours | 2016 | 5 | NaN |
14 | 201706 | M | USA-ID | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.COW-ID-99.M | 200101 | COW | megawatthours | 2016 | 4 | NaN |
15 | 201706 | M | USA-ID | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.COW-ID-99.M | 200101 | COW | megawatthours | 2016 | 3 | NaN |
17 | 201706 | M | USA-ID | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.COW-ID-99.M | 200101 | COW | megawatthours | 2016 | 1 | NaN |
0 | 201706 | M | USA-AK | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.COW-AK-99.M | 200101 | COW | megawatthours | 2017 | 6 | NaN |
1 | 201706 | M | USA-AK | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.COW-AK-99.M | 200101 | COW | megawatthours | 2017 | 5 | NaN |
2 | 201706 | M | USA-AK | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.COW-AK-99.M | 200101 | COW | megawatthours | 2017 | 4 | NaN |
3 | 201706 | M | USA-AK | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.COW-AK-99.M | 200101 | COW | megawatthours | 2017 | 3 | NaN |
0 | 201706 | M | USA-GA | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.DPV-GA-99.M | 201401 | DPV | megawatthours | 2017 | 6 | NaN |
1 | 201706 | M | USA-GA | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.DPV-GA-99.M | 201401 | DPV | megawatthours | 2017 | 5 | NaN |
2 | 201706 | M | USA-GA | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.DPV-GA-99.M | 201401 | DPV | megawatthours | 2017 | 4 | NaN |
3 | 201706 | M | USA-GA | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.DPV-GA-99.M | 201401 | DPV | megawatthours | 2017 | 3 | NaN |
4 | 201706 | M | USA-GA | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.DPV-GA-99.M | 201401 | DPV | megawatthours | 2017 | 2 | NaN |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
30 | 201702 | M | USA-WV | 2017-05-24T14:26:30-04:00 | 99 | ELEC.GEN.WAS-WV-99.M | 200101 | WAS | megawatthours | 2014 | 8 | NaN |
31 | 201702 | M | USA-WV | 2017-05-24T14:26:30-04:00 | 99 | ELEC.GEN.WAS-WV-99.M | 200101 | WAS | megawatthours | 2014 | 7 | NaN |
32 | 201702 | M | USA-WV | 2017-05-24T14:26:30-04:00 | 99 | ELEC.GEN.WAS-WV-99.M | 200101 | WAS | megawatthours | 2014 | 6 | NaN |
33 | 201702 | M | USA-WV | 2017-05-24T14:26:30-04:00 | 99 | ELEC.GEN.WAS-WV-99.M | 200101 | WAS | megawatthours | 2014 | 5 | NaN |
34 | 201702 | M | USA-WV | 2017-05-24T14:26:30-04:00 | 99 | ELEC.GEN.WAS-WV-99.M | 200101 | WAS | megawatthours | 2014 | 4 | NaN |
35 | 201702 | M | USA-WV | 2017-05-24T14:26:30-04:00 | 99 | ELEC.GEN.WAS-WV-99.M | 200101 | WAS | megawatthours | 2014 | 3 | NaN |
36 | 201702 | M | USA-WV | 2017-05-24T14:26:30-04:00 | 99 | ELEC.GEN.WAS-WV-99.M | 200101 | WAS | megawatthours | 2014 | 2 | NaN |
37 | 201702 | M | USA-WV | 2017-05-24T14:26:30-04:00 | 99 | ELEC.GEN.WAS-WV-99.M | 200101 | WAS | megawatthours | 2014 | 1 | NaN |
0 | 201410 | M | USA-AK | 2016-12-19T17:19:30-05:00 | 99 | ELEC.GEN.OOG-AK-99.M | 201101 | OOG | megawatthours | 2014 | 10 | NaN |
1 | 201410 | M | USA-AK | 2016-12-19T17:19:30-05:00 | 99 | ELEC.GEN.OOG-AK-99.M | 201101 | OOG | megawatthours | 2014 | 9 | NaN |
2 | 201410 | M | USA-AK | 2016-12-19T17:19:30-05:00 | 99 | ELEC.GEN.OOG-AK-99.M | 201101 | OOG | megawatthours | 2014 | 8 | NaN |
3 | 201410 | M | USA-AK | 2016-12-19T17:19:30-05:00 | 99 | ELEC.GEN.OOG-AK-99.M | 201101 | OOG | megawatthours | 2014 | 7 | NaN |
4 | 201410 | M | USA-AK | 2016-12-19T17:19:30-05:00 | 99 | ELEC.GEN.OOG-AK-99.M | 201101 | OOG | megawatthours | 2014 | 6 | NaN |
5 | 201410 | M | USA-AK | 2016-12-19T17:19:30-05:00 | 99 | ELEC.GEN.OOG-AK-99.M | 201101 | OOG | megawatthours | 2014 | 5 | NaN |
6 | 201410 | M | USA-AK | 2016-12-19T17:19:30-05:00 | 99 | ELEC.GEN.OOG-AK-99.M | 201101 | OOG | megawatthours | 2014 | 4 | NaN |
7 | 201410 | M | USA-AK | 2016-12-19T17:19:30-05:00 | 99 | ELEC.GEN.OOG-AK-99.M | 201101 | OOG | megawatthours | 2014 | 3 | NaN |
8 | 201410 | M | USA-AK | 2016-12-19T17:19:30-05:00 | 99 | ELEC.GEN.OOG-AK-99.M | 201101 | OOG | megawatthours | 2014 | 2 | NaN |
9 | 201410 | M | USA-AK | 2016-12-19T17:19:30-05:00 | 99 | ELEC.GEN.OOG-AK-99.M | 201101 | OOG | megawatthours | 2014 | 1 | NaN |
10 | 201410 | M | USA-AK | 2016-12-19T17:19:30-05:00 | 99 | ELEC.GEN.OOG-AK-99.M | 201101 | OOG | megawatthours | 2013 | 12 | NaN |
11 | 201410 | M | USA-AK | 2016-12-19T17:19:30-05:00 | 99 | ELEC.GEN.OOG-AK-99.M | 201101 | OOG | megawatthours | 2013 | 11 | NaN |
12 | 201410 | M | USA-AK | 2016-12-19T17:19:30-05:00 | 99 | ELEC.GEN.OOG-AK-99.M | 201101 | OOG | megawatthours | 2013 | 10 | NaN |
13 | 201410 | M | USA-AK | 2016-12-19T17:19:30-05:00 | 99 | ELEC.GEN.OOG-AK-99.M | 201101 | OOG | megawatthours | 2013 | 9 | NaN |
14 | 201410 | M | USA-AK | 2016-12-19T17:19:30-05:00 | 99 | ELEC.GEN.OOG-AK-99.M | 201101 | OOG | megawatthours | 2013 | 8 | NaN |
15 | 201410 | M | USA-AK | 2016-12-19T17:19:30-05:00 | 99 | ELEC.GEN.OOG-AK-99.M | 201101 | OOG | megawatthours | 2013 | 7 | NaN |
16 | 201410 | M | USA-AK | 2016-12-19T17:19:30-05:00 | 99 | ELEC.GEN.OOG-AK-99.M | 201101 | OOG | megawatthours | 2013 | 6 | NaN |
17 | 201410 | M | USA-AK | 2016-12-19T17:19:30-05:00 | 99 | ELEC.GEN.OOG-AK-99.M | 201101 | OOG | megawatthours | 2013 | 5 | NaN |
18 | 201410 | M | USA-AK | 2016-12-19T17:19:30-05:00 | 99 | ELEC.GEN.OOG-AK-99.M | 201101 | OOG | megawatthours | 2013 | 4 | NaN |
19 | 201410 | M | USA-AK | 2016-12-19T17:19:30-05:00 | 99 | ELEC.GEN.OOG-AK-99.M | 201101 | OOG | megawatthours | 2013 | 3 | NaN |
20 | 201410 | M | USA-AK | 2016-12-19T17:19:30-05:00 | 99 | ELEC.GEN.OOG-AK-99.M | 201101 | OOG | megawatthours | 2013 | 2 | NaN |
21 | 201410 | M | USA-AK | 2016-12-19T17:19:30-05:00 | 99 | ELEC.GEN.OOG-AK-99.M | 201101 | OOG | megawatthours | 2013 | 1 | NaN |
1588 rows × 12 columns
gen_df.dropna(inplace=True)
gen_df.set_index(['type', 'year', 'month', 'geography'], inplace=True)
#drop
gen_df.head()
end | f | last_updated | sector | series_id | start | units | generation (MWh) | ||||
---|---|---|---|---|---|---|---|---|---|---|---|
type | year | month | geography | ||||||||
AOR | 2017 | 6 | USA-MN | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.AOR-MN-99.M | 200101 | megawatthours | 1066984.87 |
5 | USA-MN | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.AOR-MN-99.M | 200101 | megawatthours | 1173596.09 | ||
4 | USA-MN | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.AOR-MN-99.M | 200101 | megawatthours | 1250625.52 | ||
3 | USA-MN | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.AOR-MN-99.M | 200101 | megawatthours | 1345113.65 | ||
2 | USA-MN | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.AOR-MN-99.M | 200101 | megawatthours | 1222310.74 |
#drop
gen_df.loc['OOG'].head()
end | f | last_updated | sector | series_id | start | units | generation (MWh) | |||
---|---|---|---|---|---|---|---|---|---|---|
year | month | geography | ||||||||
2017 | 6 | USA-NV | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.OOG-NV-99.M | 200210 | megawatthours | 0.0 |
5 | USA-NV | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.OOG-NV-99.M | 200210 | megawatthours | 0.0 | |
4 | USA-NV | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.OOG-NV-99.M | 200210 | megawatthours | 0.0 | |
3 | USA-NV | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.OOG-NV-99.M | 200210 | megawatthours | 0.0 | |
2 | USA-NV | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.OOG-NV-99.M | 200210 | megawatthours | 0.0 |
total_fuel_dict = [json.loads(row) for row in total_fuel_rows]
total_fuel_df = pd.concat([line_to_df(x) for x in total_fuel_dict
if x['geography'] in state_geos])
#drop
total_fuel_df.head()
end | f | geography | last_updated | sector | series_id | start | type | units | year | month | value | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 201706 | M | USA-CA | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-CA-99.M | 200101 | PC | million MMBtu | 2017 | 6 | 0.0 |
1 | 201706 | M | USA-CA | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-CA-99.M | 200101 | PC | million MMBtu | 2017 | 5 | 0.0 |
2 | 201706 | M | USA-CA | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-CA-99.M | 200101 | PC | million MMBtu | 2017 | 4 | 0.0 |
3 | 201706 | M | USA-CA | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-CA-99.M | 200101 | PC | million MMBtu | 2017 | 3 | 0.0 |
4 | 201706 | M | USA-CA | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-CA-99.M | 200101 | PC | million MMBtu | 2017 | 2 | 0.0 |
Multiply generation values by 1,000,000 and change the units to MMBtu
total_fuel_df.loc[:,'value'] *= 1E6
total_fuel_df.loc[:,'units'] = 'mmbtu'
total_fuel_df.rename_axis({'value':'total fuel (mmbtu)'}, axis=1, inplace=True)
total_fuel_df.set_index(['type', 'year', 'month', 'geography'], inplace=True)
#drop
total_fuel_df.head()
end | f | last_updated | sector | series_id | start | units | total fuel (mmbtu) | ||||
---|---|---|---|---|---|---|---|---|---|---|---|
type | year | month | geography | ||||||||
PC | 2017 | 6 | USA-CA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-CA-99.M | 200101 | mmbtu | 0.0 |
5 | USA-CA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-CA-99.M | 200101 | mmbtu | 0.0 | ||
4 | USA-CA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-CA-99.M | 200101 | mmbtu | 0.0 | ||
3 | USA-CA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-CA-99.M | 200101 | mmbtu | 0.0 | ||
2 | USA-CA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-CA-99.M | 200101 | mmbtu | 0.0 |
#drop
total_fuel_df.loc[~(total_fuel_df['total fuel (mmbtu)'] >= 0) &
~(total_fuel_df['total fuel (mmbtu)'].isnull())]
end | f | last_updated | sector | series_id | start | units | total fuel (mmbtu) | ||||
---|---|---|---|---|---|---|---|---|---|---|---|
type | year | month | geography |
Drop nans
total_fuel_df.loc[total_fuel_df.isnull().any(axis=1)]
end | f | last_updated | sector | series_id | start | units | total fuel (mmbtu) | ||||
---|---|---|---|---|---|---|---|---|---|---|---|
type | year | month | geography | ||||||||
PC | 2012 | 12 | USA-CA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-CA-99.M | 200101 | mmbtu | NaN |
11 | USA-CA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-CA-99.M | 200101 | mmbtu | NaN | ||
10 | USA-CA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-CA-99.M | 200101 | mmbtu | NaN | ||
9 | USA-CA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-CA-99.M | 200101 | mmbtu | NaN | ||
8 | USA-CA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-CA-99.M | 200101 | mmbtu | NaN | ||
7 | USA-CA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-CA-99.M | 200101 | mmbtu | NaN | ||
6 | USA-CA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-CA-99.M | 200101 | mmbtu | NaN | ||
5 | USA-CA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-CA-99.M | 200101 | mmbtu | NaN | ||
3 | USA-CA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-CA-99.M | 200101 | mmbtu | NaN | ||
2016 | 11 | USA-IA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-IA-99.M | 200101 | mmbtu | NaN | |
10 | USA-IA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-IA-99.M | 200101 | mmbtu | NaN | ||
9 | USA-IA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-IA-99.M | 200101 | mmbtu | NaN | ||
8 | USA-IA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-IA-99.M | 200101 | mmbtu | NaN | ||
6 | USA-IA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-IA-99.M | 200101 | mmbtu | NaN | ||
5 | USA-IA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-IA-99.M | 200101 | mmbtu | NaN | ||
3 | USA-IA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-IA-99.M | 200101 | mmbtu | NaN | ||
1 | USA-IA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-IA-99.M | 200101 | mmbtu | NaN | ||
NG | 2016 | 6 | USA-WY | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.NG-WY-99.M | 200101 | mmbtu | NaN |
PC | 2017 | 6 | USA-GA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-GA-99.M | 200101 | mmbtu | NaN |
5 | USA-GA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-GA-99.M | 200101 | mmbtu | NaN | ||
4 | USA-GA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-GA-99.M | 200101 | mmbtu | NaN | ||
3 | USA-GA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-GA-99.M | 200101 | mmbtu | NaN | ||
2 | USA-GA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-GA-99.M | 200101 | mmbtu | NaN | ||
1 | USA-GA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-GA-99.M | 200101 | mmbtu | NaN | ||
2014 | 5 | USA-GA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PC-GA-99.M | 200101 | mmbtu | NaN | |
COW | 2017 | 6 | USA-ID | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.COW-ID-99.M | 200101 | mmbtu | NaN |
5 | USA-ID | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.COW-ID-99.M | 200101 | mmbtu | NaN | ||
4 | USA-ID | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.COW-ID-99.M | 200101 | mmbtu | NaN | ||
3 | USA-ID | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.COW-ID-99.M | 200101 | mmbtu | NaN | ||
2 | USA-ID | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.COW-ID-99.M | 200101 | mmbtu | NaN | ||
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
NG | 2016 | 8 | USA-VT | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.NG-VT-99.M | 200101 | mmbtu | NaN |
7 | USA-VT | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.NG-VT-99.M | 200101 | mmbtu | NaN | ||
6 | USA-VT | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.NG-VT-99.M | 200101 | mmbtu | NaN | ||
5 | USA-VT | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.NG-VT-99.M | 200101 | mmbtu | NaN | ||
4 | USA-VT | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.NG-VT-99.M | 200101 | mmbtu | NaN | ||
3 | USA-VT | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.NG-VT-99.M | 200101 | mmbtu | NaN | ||
2 | USA-VT | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.NG-VT-99.M | 200101 | mmbtu | NaN | ||
1 | USA-VT | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.NG-VT-99.M | 200101 | mmbtu | NaN | ||
PEL | 2017 | 3 | USA-VA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-VA-99.M | 200101 | mmbtu | NaN |
6 | USA-CO | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-CO-99.M | 200101 | mmbtu | NaN | ||
4 | USA-CO | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-CO-99.M | 200101 | mmbtu | NaN | ||
3 | USA-CO | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-CO-99.M | 200101 | mmbtu | NaN | ||
2 | USA-CO | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-CO-99.M | 200101 | mmbtu | NaN | ||
1 | USA-CO | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-CO-99.M | 200101 | mmbtu | NaN | ||
2016 | 12 | USA-CO | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-CO-99.M | 200101 | mmbtu | NaN | |
11 | USA-CO | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-CO-99.M | 200101 | mmbtu | NaN | ||
10 | USA-CO | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-CO-99.M | 200101 | mmbtu | NaN | ||
9 | USA-CO | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-CO-99.M | 200101 | mmbtu | NaN | ||
8 | USA-CO | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-CO-99.M | 200101 | mmbtu | NaN | ||
7 | USA-CO | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-CO-99.M | 200101 | mmbtu | NaN | ||
6 | USA-CO | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-CO-99.M | 200101 | mmbtu | NaN | ||
5 | USA-CO | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-CO-99.M | 200101 | mmbtu | NaN | ||
4 | USA-CO | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-CO-99.M | 200101 | mmbtu | NaN | ||
3 | USA-CO | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-CO-99.M | 200101 | mmbtu | NaN | ||
2 | USA-CO | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-CO-99.M | 200101 | mmbtu | NaN | ||
1 | USA-CO | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-CO-99.M | 200101 | mmbtu | NaN | ||
2017 | 4 | USA-CT | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-CT-99.M | 200101 | mmbtu | NaN | |
2016 | 10 | USA-CT | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-CT-99.M | 200101 | mmbtu | NaN | |
4 | USA-CT | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-CT-99.M | 200101 | mmbtu | NaN | ||
3 | USA-CT | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-CT-99.M | 200101 | mmbtu | NaN |
356 rows × 8 columns
total_fuel_df = total_fuel_df.dropna()
eg_fuel_dict = [json.loads(row) for row in eg_fuel_rows]
eg_fuel_df = pd.concat([line_to_df(x) for x in eg_fuel_dict
if x['geography'] in state_geos])
#drop
eg_fuel_df.head()
end | f | geography | last_updated | sector | series_id | start | type | units | year | month | value | |
---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 201706 | M | USA-VA | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_EG_BTU.NG-VA-99.M | 200101 | NG | million MMBtu | 2017 | 6 | 35.91854 |
1 | 201706 | M | USA-VA | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_EG_BTU.NG-VA-99.M | 200101 | NG | million MMBtu | 2017 | 5 | 24.96651 |
2 | 201706 | M | USA-VA | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_EG_BTU.NG-VA-99.M | 200101 | NG | million MMBtu | 2017 | 4 | 21.21269 |
3 | 201706 | M | USA-VA | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_EG_BTU.NG-VA-99.M | 200101 | NG | million MMBtu | 2017 | 3 | 28.41358 |
4 | 201706 | M | USA-VA | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_EG_BTU.NG-VA-99.M | 200101 | NG | million MMBtu | 2017 | 2 | 24.13910 |
Multiply generation values by 1,000,000 and change the units to MMBtu
eg_fuel_df.loc[:,'value'] *= 1E6
eg_fuel_df.loc[:,'units'] = 'mmbtu'
eg_fuel_df.rename_axis({'value':'elec fuel (mmbtu)'}, axis=1, inplace=True)
eg_fuel_df.set_index(['type', 'year', 'month', 'geography'], inplace=True)
#drop
eg_fuel_df.head()
end | f | last_updated | sector | series_id | start | units | elec fuel (mmbtu) | ||||
---|---|---|---|---|---|---|---|---|---|---|---|
type | year | month | geography | ||||||||
NG | 2017 | 6 | USA-VA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_EG_BTU.NG-VA-99.M | 200101 | mmbtu | 35918540.0 |
5 | USA-VA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_EG_BTU.NG-VA-99.M | 200101 | mmbtu | 24966510.0 | ||
4 | USA-VA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_EG_BTU.NG-VA-99.M | 200101 | mmbtu | 21212690.0 | ||
3 | USA-VA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_EG_BTU.NG-VA-99.M | 200101 | mmbtu | 28413580.0 | ||
2 | USA-VA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_EG_BTU.NG-VA-99.M | 200101 | mmbtu | 24139100.0 |
I verified on EIA's website that the values below are correct.
#drop
eg_fuel_df.loc[~(eg_fuel_df['elec fuel (mmbtu)'] >= 0) &
~(eg_fuel_df['elec fuel (mmbtu)'].isnull())]
end | f | last_updated | sector | series_id | start | units | elec fuel (mmbtu) | ||||
---|---|---|---|---|---|---|---|---|---|---|---|
type | year | month | geography | ||||||||
PEL | 2002 | 12 | USA-MN | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_EG_BTU.PEL-MN-99.M | 200101 | mmbtu | -43000.0 |
11 | USA-MN | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_EG_BTU.PEL-MN-99.M | 200101 | mmbtu | -32000.0 | ||
10 | USA-MN | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_EG_BTU.PEL-MN-99.M | 200101 | mmbtu | -15000.0 | ||
8 | USA-MN | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_EG_BTU.PEL-MN-99.M | 200101 | mmbtu | -16000.0 | ||
7 | USA-MN | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_EG_BTU.PEL-MN-99.M | 200101 | mmbtu | -1000.0 | ||
4 | USA-MN | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_EG_BTU.PEL-MN-99.M | 200101 | mmbtu | -6000.0 | ||
3 | USA-MN | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_EG_BTU.PEL-MN-99.M | 200101 | mmbtu | -10000.0 | ||
2 | USA-MN | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_EG_BTU.PEL-MN-99.M | 200101 | mmbtu | -30000.0 | ||
1 | USA-MN | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_EG_BTU.PEL-MN-99.M | 200101 | mmbtu | -34000.0 |
eg_fuel_df.dropna(inplace=True)
Need to estimate fuel use for OOG, because EIA doesn't include any (this is only ~2% of OOG fuel for electricity in 2015).
fuel_df = pd.concat([total_fuel_df, eg_fuel_df['elec fuel (mmbtu)']], axis=1)
Not sure how this happens in EIA's data, but we do see the negative fuel consumption for electricity generation.
#drop
fuel_df.loc[~(fuel_df['elec fuel (mmbtu)']>=0)]
end | f | last_updated | sector | series_id | start | units | total fuel (mmbtu) | elec fuel (mmbtu) | ||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
type | year | month | geography | |||||||||
PEL | 2002 | 1 | USA-MN | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-MN-99.M | 200101 | mmbtu | 51000.0 | -34000.0 |
2 | USA-MN | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-MN-99.M | 200101 | mmbtu | 62000.0 | -30000.0 | ||
3 | USA-MN | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-MN-99.M | 200101 | mmbtu | 99000.0 | -10000.0 | ||
4 | USA-MN | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-MN-99.M | 200101 | mmbtu | 84000.0 | -6000.0 | ||
7 | USA-MN | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-MN-99.M | 200101 | mmbtu | 93000.0 | -1000.0 | ||
8 | USA-MN | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-MN-99.M | 200101 | mmbtu | 66000.0 | -16000.0 | ||
10 | USA-MN | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-MN-99.M | 200101 | mmbtu | 64000.0 | -15000.0 | ||
11 | USA-MN | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-MN-99.M | 200101 | mmbtu | 49000.0 | -32000.0 | ||
12 | USA-MN | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.PEL-MN-99.M | 200101 | mmbtu | 50000.0 | -43000.0 |
#drop
fuel_df.loc[~(fuel_df['total fuel (mmbtu)']>=0)]
end | f | last_updated | sector | series_id | start | units | total fuel (mmbtu) | elec fuel (mmbtu) | ||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
type | year | month | geography |
The difficulty here is that EIA combines all types of coal fuel consumption together in the bulk download and API. Fortunately the emission factors for different coal types aren't too far off on an energy basis (BIT is 93.3 kg/mmbtu, SUB is 97.2 kg/mmbtu). I'm going to average the BIT and SUB factors rather than trying to do something more complicated. In 2015 BIT represented 45% of coal energy for electricity and SUB represented 48%.
Same issue with petroleum liquids. Using the average of DFO and RFO, which were the two largest share of petroleum liquids.
path = join(cwd, '..', 'Data storage', 'Final emission factors.csv')
ef = pd.read_csv(path, index_col=0)
#drop
ef.index
Index([u'BIT', u'DFO', u'GEO', u'JF', u'KER', u'LIG', u'MSW', u'NG', u'PC', u'PG', u'RC', u'RFO', u'SGC', u'SGP', u'SUB', u'TDF', u'WC', u'WO', u'BFG', u'MSN', u'SC', u'OG', u'AB', u'BLQ', u'LFG', u'MSB', u'NUC', u'OBG', u'OBL', u'OBS', u'OTH', u'PUR', u'SLW', u'SUN', u'WAT', u'WDL', u'WDS', u'WH', u'WND'], dtype='object', name=u'EIA Fuel Code')
Fuel codes:
#drop
ef.loc['NG', 'Fossil Factor']
53.07
fuel_factors = pd.Series({'NG' : ef.loc['NG', 'Fossil Factor'],
'PEL': ef.loc[['DFO', 'RFO'], 'Fossil Factor'].mean(),
'PC' : ef.loc['PC', 'Fossil Factor'],
'COW' : ef.loc[['BIT', 'SUB'], 'Fossil Factor'].mean(),
'OOG' : ef.loc['OG', 'Fossil Factor']}, name='type')
#drop
fuel_factors
COW 95.250 NG 53.070 OOG 59.000 PC 102.100 PEL 75.975 Name: type, dtype: float64
fuel_df.index.get_level_values('type').unique()
Index([u'COW', u'NG', u'PC', u'PEL'], dtype='object', name=u'type')
fuel_df['all fuel CO2 (kg)'] = (fuel_df['total fuel (mmbtu)']
.multiply(fuel_factors, level='type',
fill_value=0))
fuel_df['elec fuel CO2 (kg)'] = (fuel_df['elec fuel (mmbtu)']
.multiply(fuel_factors, level='type',
fill_value=0))
fuel_df.head()
end | f | last_updated | sector | series_id | start | units | total fuel (mmbtu) | elec fuel (mmbtu) | all fuel CO2 (kg) | elec fuel CO2 (kg) | ||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
type | year | month | geography | |||||||||||
COW | 2001 | 1 | USA-AK | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.COW-AK-99.M | 200101 | mmbtu | 1120000.0 | 872000.0 | 1.066800e+08 | 8.305800e+07 |
USA-AL | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.COW-AL-99.M | 200101 | mmbtu | 67999000.0 | 66582000.0 | 6.476905e+09 | 6.341935e+09 | |||
USA-AR | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.COW-AR-99.M | 200101 | mmbtu | 23099000.0 | 22700000.0 | 2.200180e+09 | 2.162175e+09 | |||
USA-AZ | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.COW-AZ-99.M | 200101 | mmbtu | 35873000.0 | 35483000.0 | 3.416903e+09 | 3.379756e+09 | |||
USA-CA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.CONS_TOT_BTU.COW-CA-99.M | 200101 | mmbtu | 3652000.0 | 2008000.0 | 3.478530e+08 | 1.912620e+08 |
fuel_cols = ['total fuel (mmbtu)', 'elec fuel (mmbtu)',
'all fuel CO2 (kg)', 'elec fuel CO2 (kg)']
gen_fuel_df = pd.concat([gen_df, fuel_df[fuel_cols]], axis=1)
gen_fuel_df.head()
end | f | last_updated | sector | series_id | start | units | generation (MWh) | total fuel (mmbtu) | elec fuel (mmbtu) | all fuel CO2 (kg) | elec fuel CO2 (kg) | ||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
type | year | month | geography | ||||||||||||
AOR | 2001 | 1 | USA-AK | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.AOR-AK-99.M | 200101 | megawatthours | 87.00 | NaN | NaN | NaN | NaN |
USA-AL | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.AOR-AL-99.M | 200101 | megawatthours | 401167.59 | NaN | NaN | NaN | NaN | |||
USA-AR | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.AOR-AR-99.M | 200101 | megawatthours | 136530.37 | NaN | NaN | NaN | NaN | |||
USA-AZ | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.AOR-AZ-99.M | 200101 | megawatthours | 453.00 | NaN | NaN | NaN | NaN | |||
USA-CA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.AOR-CA-99.M | 200101 | megawatthours | 1717398.41 | NaN | NaN | NaN | NaN |
years = gen_fuel_df.index.get_level_values('year').astype(str)
months = gen_fuel_df.index.get_level_values('month').astype(str)
datetime = pd.to_datetime(years + '-' + months, format='%Y-%m')
quarters = datetime.quarter
gen_fuel_df['datetime'] = datetime
gen_fuel_df['quarter'] = quarters
No records with positive fuel use but no generation
gen_fuel_df['generation (MWh)'].fillna(value=0, inplace=True)
#drop
gen_fuel_df.loc[gen_fuel_df['sector'].isnull()]
end | f | last_updated | sector | series_id | start | units | generation (MWh) | total fuel (mmbtu) | elec fuel (mmbtu) | all fuel CO2 (kg) | elec fuel CO2 (kg) | datetime | quarter | ||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
type | year | month | geography | ||||||||||||||
COW | 2016 | 1 | USA-ID | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 203250.0 | 33260.0 | 1.935956e+07 | 3.168015e+06 | 2016-01-01 | 1 |
3 | USA-ID | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 180840.0 | 29370.0 | 1.722501e+07 | 2.797492e+06 | 2016-03-01 | 1 | ||
2017 | 3 | USA-AK | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 907610.0 | 448030.0 | 8.644985e+07 | 4.267486e+07 | 2017-03-01 | 1 | |
USA-ME | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 259940.0 | 56050.0 | 2.475928e+07 | 5.338762e+06 | 2017-03-01 | 1 | |||
4 | USA-AK | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 817670.0 | 427170.0 | 7.788307e+07 | 4.068794e+07 | 2017-04-01 | 2 | ||
5 | USA-AK | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 841370.0 | 440000.0 | 8.014049e+07 | 4.191000e+07 | 2017-05-01 | 2 | ||
6 | USA-AK | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 759460.0 | 315130.0 | 7.233856e+07 | 3.001613e+07 | 2017-06-01 | 2 | ||
NG | 2010 | 4 | USA-MT | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 46590.0 | 9920.0 | 2.472531e+06 | 5.264544e+05 | 2010-04-01 | 2 |
8 | USA-MT | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 56400.0 | 8740.0 | 2.993148e+06 | 4.638318e+05 | 2010-08-01 | 3 | ||
2015 | 2 | USA-NE | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 66370.0 | 66320.0 | 3.522256e+06 | 3.519602e+06 | 2015-02-01 | 1 | |
2017 | 3 | USA-ND | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 622640.0 | 511610.0 | 3.304350e+07 | 2.715114e+07 | 2017-03-01 | 1 | |
4 | USA-ND | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 562040.0 | 467050.0 | 2.982746e+07 | 2.478634e+07 | 2017-04-01 | 2 | ||
5 | USA-ME | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 2218070.0 | 1345260.0 | 1.177130e+08 | 7.139295e+07 | 2017-05-01 | 2 | ||
USA-ND | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 368030.0 | 248830.0 | 1.953135e+07 | 1.320541e+07 | 2017-05-01 | 2 | |||
6 | USA-ND | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 431120.0 | 285430.0 | 2.287954e+07 | 1.514777e+07 | 2017-06-01 | 2 | ||
PC | 2011 | 4 | USA-NY | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 5760.0 | 5760.0 | 5.880960e+05 | 5.880960e+05 | 2011-04-01 | 2 |
6 | USA-NY | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 70340.0 | 70340.0 | 7.181714e+06 | 7.181714e+06 | 2011-06-01 | 2 | ||
2016 | 1 | USA-MI | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 495130.0 | 99490.0 | 5.055277e+07 | 1.015793e+07 | 2016-01-01 | 1 | |
USA-OH | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 474330.0 | 10400.0 | 4.842909e+07 | 1.061840e+06 | 2016-01-01 | 1 | |||
USA-TX | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 383400.0 | 198590.0 | 3.914514e+07 | 2.027604e+07 | 2016-01-01 | 1 | |||
2 | USA-IA | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 178520.0 | 51380.0 | 1.822689e+07 | 5.245898e+06 | 2016-02-01 | 1 | ||
4 | USA-IA | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 130230.0 | 62060.0 | 1.329648e+07 | 6.336326e+06 | 2016-04-01 | 2 | ||
7 | USA-IA | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 183010.0 | 66640.0 | 1.868532e+07 | 6.803944e+06 | 2016-07-01 | 3 | ||
12 | USA-IA | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 185790.0 | 60730.0 | 1.896916e+07 | 6.200533e+06 | 2016-12-01 | 4 | ||
2017 | 3 | USA-OH | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 1534660.0 | 1005180.0 | 1.566888e+08 | 1.026289e+08 | 2017-03-01 | 1 | |
PEL | 2012 | 8 | USA-OK | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 540.0 | 540.0 | 4.102650e+04 | 4.102650e+04 | 2012-08-01 | 3 |
2013 | 1 | USA-RI | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 1377260.0 | 86450.0 | 1.046373e+08 | 6.568039e+06 | 2013-01-01 | 1 | |
8 | USA-RI | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 8100.0 | 4450.0 | 6.153975e+05 | 3.380888e+05 | 2013-08-01 | 3 | ||
11 | USA-RI | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 29880.0 | 11310.0 | 2.270133e+06 | 8.592772e+05 | 2013-11-01 | 4 | ||
12 | USA-RI | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 1028040.0 | 130840.0 | 7.810534e+07 | 9.940569e+06 | 2013-12-01 | 4 | ||
2014 | 7 | USA-RI | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 11560.0 | 4980.0 | 8.782710e+05 | 3.783555e+05 | 2014-07-01 | 3 | |
2015 | 3 | USA-RI | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 21170.0 | 10160.0 | 1.608391e+06 | 7.719060e+05 | 2015-03-01 | 1 | |
6 | USA-CO | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 14220.0 | 14210.0 | 1.080364e+06 | 1.079605e+06 | 2015-06-01 | 2 | ||
2016 | 1 | USA-NH | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 43710.0 | 16170.0 | 3.320867e+06 | 1.228516e+06 | 2016-01-01 | 1 | |
2 | USA-NH | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 59990.0 | 17980.0 | 4.557740e+06 | 1.366030e+06 | 2016-02-01 | 1 | ||
3 | USA-ME | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 84780.0 | 38880.0 | 6.441160e+06 | 2.953908e+06 | 2016-03-01 | 1 | ||
USA-WA | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 64440.0 | 8570.0 | 4.895829e+06 | 6.511058e+05 | 2016-03-01 | 1 | |||
4 | USA-KS | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 38460.0 | 38460.0 | 2.921998e+06 | 2.921998e+06 | 2016-04-01 | 2 | ||
USA-ME | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 55680.0 | 30530.0 | 4.230288e+06 | 2.319517e+06 | 2016-04-01 | 2 | |||
USA-MS | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 13600.0 | 12040.0 | 1.033260e+06 | 9.147390e+05 | 2016-04-01 | 2 | |||
USA-SC | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 81930.0 | 55810.0 | 6.224632e+06 | 4.240165e+06 | 2016-04-01 | 2 | |||
USA-WA | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 67910.0 | 7700.0 | 5.159462e+06 | 5.850075e+05 | 2016-04-01 | 2 | |||
5 | USA-ME | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 72780.0 | 31530.0 | 5.529460e+06 | 2.395492e+06 | 2016-05-01 | 2 | ||
USA-WA | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 101990.0 | 12020.0 | 7.748690e+06 | 9.132195e+05 | 2016-05-01 | 2 | |||
9 | USA-ME | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 54280.0 | 20560.0 | 4.123923e+06 | 1.562046e+06 | 2016-09-01 | 3 | ||
10 | USA-WA | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 63460.0 | 16980.0 | 4.821374e+06 | 1.290056e+06 | 2016-10-01 | 4 | ||
11 | USA-CT | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 63950.0 | 61130.0 | 4.858601e+06 | 4.644352e+06 | 2016-11-01 | 4 | ||
USA-ME | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 62960.0 | 34820.0 | 4.783386e+06 | 2.645450e+06 | 2016-11-01 | 4 | |||
USA-NY | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 212900.0 | 149830.0 | 1.617508e+07 | 1.138333e+07 | 2016-11-01 | 4 | |||
USA-WA | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 64270.0 | 13500.0 | 4.882913e+06 | 1.025662e+06 | 2016-11-01 | 4 | |||
2017 | 1 | USA-RI | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 41220.0 | 23340.0 | 3.131689e+06 | 1.773256e+06 | 2017-01-01 | 1 | |
2 | USA-WA | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 62940.0 | 18030.0 | 4.781866e+06 | 1.369829e+06 | 2017-02-01 | 1 | ||
3 | USA-WA | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 61040.0 | 9630.0 | 4.637514e+06 | 7.316392e+05 | 2017-03-01 | 1 | ||
4 | USA-ME | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 64280.0 | 40760.0 | 4.883673e+06 | 3.096741e+06 | 2017-04-01 | 2 | ||
USA-SC | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 174000.0 | 156090.0 | 1.321965e+07 | 1.185894e+07 | 2017-04-01 | 2 | |||
5 | USA-ME | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 60470.0 | 38450.0 | 4.594208e+06 | 2.921239e+06 | 2017-05-01 | 2 | ||
USA-WA | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 | 91230.0 | 13090.0 | 6.931199e+06 | 9.945127e+05 | 2017-05-01 | 2 |
gen_fuel_df.loc['COW',:].head()
end | f | last_updated | sector | series_id | start | units | generation (MWh) | total fuel (mmbtu) | elec fuel (mmbtu) | all fuel CO2 (kg) | elec fuel CO2 (kg) | datetime | quarter | |||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
year | month | geography | ||||||||||||||
2001 | 1 | USA-AK | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.COW-AK-99.M | 200101 | megawatthours | 46903.0 | 1120000.0 | 872000.0 | 1.066800e+08 | 8.305800e+07 | 2001-01-01 | 1 |
USA-AL | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.COW-AL-99.M | 200101 | megawatthours | 6557913.0 | 67999000.0 | 66582000.0 | 6.476905e+09 | 6.341935e+09 | 2001-01-01 | 1 | ||
USA-AR | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.COW-AR-99.M | 200101 | megawatthours | 2149808.0 | 23099000.0 | 22700000.0 | 2.200180e+09 | 2.162175e+09 | 2001-01-01 | 1 | ||
USA-AZ | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.COW-AZ-99.M | 200101 | megawatthours | 3418454.0 | 35873000.0 | 35483000.0 | 3.416903e+09 | 3.379756e+09 | 2001-01-01 | 1 | ||
USA-CA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.COW-CA-99.M | 200101 | megawatthours | 199857.0 | 3652000.0 | 2008000.0 | 3.478530e+08 | 1.912620e+08 | 2001-01-01 | 1 |
#drop
gen_fuel_df.loc['OOG'].head()
end | f | last_updated | sector | series_id | start | units | generation (MWh) | total fuel (mmbtu) | elec fuel (mmbtu) | all fuel CO2 (kg) | elec fuel CO2 (kg) | datetime | quarter | |||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
year | month | geography | ||||||||||||||
2001 | 1 | USA-AL | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.OOG-AL-99.M | 200101 | megawatthours | 25283.0 | NaN | NaN | NaN | NaN | 2001-01-01 | 1 |
USA-CA | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.OOG-CA-99.M | 200101 | megawatthours | 97569.0 | NaN | NaN | NaN | NaN | 2001-01-01 | 1 | ||
USA-CT | 201012 | M | 2016-12-19T17:19:30-05:00 | 99 | ELEC.GEN.OOG-CT-99.M | 200101 | megawatthours | 137.0 | NaN | NaN | NaN | NaN | 2001-01-01 | 1 | ||
USA-DE | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.OOG-DE-99.M | 200101 | megawatthours | 12552.0 | NaN | NaN | NaN | NaN | 2001-01-01 | 1 | ||
USA-FL | 201706 | M | 2017-08-24T11:46:12-04:00 | 99 | ELEC.GEN.OOG-FL-99.M | 200101 | megawatthours | 9.0 | NaN | NaN | NaN | NaN | 2001-01-01 | 1 |
path = join(cwd, '..', 'Data storage', 'EIA state-level gen fuel CO2.csv')
gen_fuel_df.to_csv(path, index=False)