In [1]:
%load_ext sql
In [22]:
%sql sqlite:///2012.db
%sql select distinct(fuel_actual), count(distinct(orispl_code)) from data group by fuel_actual
Done.
Out[22]:
fuel_actual
count(distinct(orispl_code))
Coal
45
NG
200
Other Gas
1
Residual Oil
1
In [29]:
%sql select distinct(fuel_type), count(distinct(orispl_code)) from data group by fuel_type
Done.
Out[29]:
fuel_type
count(distinct(orispl_code))
Coal
45
Natural Gas
14
Other Gas
1
Pipeline Natural Gas
186
Residual Oil
1
In [ ]:
# Getting the total number of units per state
In [49]:
%%sql select sum(num_units) as total_units, state from (
select count(distinct(unitid)) as num_units, state from data where fuel_type is 'Coal' group by state, orispl_code)
group by state
Done.
Out[49]:
total_units
state
16
AZ
22
CO
7
MT
10
NM
7
NV
1
OR
10
UT
2
WA
20
WY
In [27]:
%sql select distinct(fuel_type), state, count(distinct(orispl_code)), county from data where fuel_type='Natural Gas' group by orispl_code
Done.
Out[27]:
fuel_type
state
count(distinct(orispl_code))
county
Natural Gas
AZ
1
Pima
Natural Gas
WA
1
Skagit
Natural Gas
WA
1
Lewis
Natural Gas
CA
1
Sacramento
Natural Gas
WA
1
Clark
Natural Gas
WA
1
Whatcom
Natural Gas
WA
1
Grays Harbor
Natural Gas
WA
1
Cowlitz
Natural Gas
OR
1
Columbia
Natural Gas
AZ
1
Mohave
In [17]:
%sql select *, substr(op_date, -4) as year, count(distinct unitid) as number_of_units from data group by orispl_code, substr(op_date, -4)
Done.
Out[17]:
state
name
orispl_code
unitid
op_date
op_hour
op_time
gload
SO2_MASS
NOX_MASS
CO2_MASS
heat_input
county
latitude
longitude
fuel_type
year
number_of_units
WA
Fredonia Generating Station
607
CT4
12-31-2001
23
0.0
Skagit
48.4553
-122.435
Pipeline Natural Gas
2001
2
WA
Fredonia Generating Station
607
CT4
12-31-2002
23
0.0
Skagit
48.4553
-122.435
Pipeline Natural Gas
2002
2
WA
Fredonia Generating Station
607
CT4
12-31-2003
23
0.0
Skagit
48.4553
-122.435
Pipeline Natural Gas
2003
2
WA
Fredonia Generating Station
607
CT4
12-31-2004
23
0.0
Skagit
48.4553
-122.435
Pipeline Natural Gas
2004
2
WA
Fredonia Generating Station
607
CT4
12-31-2005
23
0.0
Skagit
48.4553
-122.435
Pipeline Natural Gas
2005
2
WA
Fredonia Generating Station
607
CT4
12-31-2006
23
0.0
Skagit
48.4553
-122.435
Pipeline Natural Gas
2006
2
WA
Fredonia Generating Station
607
CT4
12-03-2007
15
0.0
Skagit
48.4553
-122.435
Pipeline Natural Gas
2007
2
WA
Fredonia Generating Station
607
CT4
12-30-2008
5
0.0
Skagit
48.4553
-122.435
Pipeline Natural Gas
2008
2
WA
Fredonia Generating Station
607
CT4
12-31-2009
23
0.0
Skagit
48.4553
-122.435
Natural Gas
2009
2
WA
Fredonia Generating Station
607
CT4
12-31-2010
23
0.0
Skagit
48.4553
-122.435
Natural Gas
2010
2
WA
Fredonia Generating Station
607
CT4
12-31-2011
23
0.0
Skagit
48.4553
-122.435
Natural Gas
2011
2
WA
Fredonia Generating Station
607
CT4
12-31-2012
23
0.0
Skagit
48.4553
-122.435
Natural Gas
2012
2
WA
Fredonia Generating Station
607
CT4
12-31-2013
23
0.0
Skagit
48.4553
-122.435
Natural Gas
2013
2
WA
Fredonia Generating Station
607
CT4
12-31-2014
23
0.0
Skagit
48.4553
-122.435
Natural Gas
2014
2
WA
Fredonia Generating Station
607
CT4
12-31-2015
23
0.0
Skagit
48.4553
-122.435
Natural Gas
2015
2
WA
Fredonia Generating Station
607
CT4
12-31-2016
23
0.0
Skagit
48.4553
-122.435
Natural Gas
2016
2
WA
Centralia
3845
BW22
12-31-2001
23
1.0
738.0
672.9
2326.467
834.6
8134.5
Lewis
46.7559
-122.8578
Coal
2001
2
WA
Centralia
3845
BW22
12-31-2002
23
1.0
712.0
1349.2
4384.567
718.6
7004.1
Lewis
46.7559
-122.8578
Coal
2002
6
WA
Centralia
3845
BW22
12-31-2003
23
1.0
740.0
868.6
1635.174
802.7
7823.8
Lewis
46.7559
-122.8578
Coal
2003
6
WA
Centralia
3845
BW22
12-31-2004
23
1.0
746.0
434.0
2014.768
738.3
7195.6
Lewis
46.7559
-122.8578
Coal
2004
6
WA
Centralia
3845
BW22
12-31-2005
23
1.0
281.0
46.1
852.922
339.2
3305.9
Lewis
46.7559
-122.8578
Coal
2005
6
WA
Centralia
3845
50
12-31-2006
23
0.0
Lewis
46.7559
-122.8578
Natural Gas
2006
6
WA
Centralia
3845
BW22
12-31-2007
23
1.0
691.0
162.6
2107.184
777.7
7579.8
Lewis
46.7559
-122.8578
Coal
2007
6
WA
Centralia
3845
BW22
12-31-2008
23
1.0
722.0
41.0
1535.547
807.9
7874.6
Lewis
46.7559
-122.8578
Coal
2008
6
WA
Centralia
3845
BW22
12-31-2009
23
1.0
706.0
186.5
1830.834
846.1
8247.0
Lewis
46.7559
-122.8578
Coal
2009
6
WA
Centralia
3845
BW22
12-31-2010
23
1.0
708.0
169.8
1682.584
781.1
7613.5
Lewis
46.7559
-122.8578
Coal
2010
6
WA
Centralia
3845
BW22
12-31-2011
23
1.0
657.0
196.7
1626.456
695.3
6776.9
Lewis
46.7559
-122.8578
Coal
2011
6
WA
Centralia
3845
BW22
12-31-2012
23
1.0
445.0
200.9
679.918
524.3
4999.4
Lewis
46.7559
-122.8578
Coal
2012
6
WA
Centralia
3845
BW22
12-31-2013
23
1.0
713.0
1082.4
1767.141
831.1
7924.4
Lewis
46.7559
-122.8578
Coal
2013
6
WA
Centralia
3845
BW22
12-31-2014
23
1.0
466.0
231.7
724.565
527.7
5031.7
Lewis
46.7559
-122.8578
Coal
2014
6
WA
Centralia
3845
BW22
12-31-2015
23
1.0
709.0
446.2
1494.1
783.5
7470.5
Lewis
46.7559
-122.8578
Coal
2015
2
WA
Centralia
3845
BW22
12-31-2016
23
1.0
701.0
699.8
1554.011
798.9
7617.7
Lewis
46.7559
-122.8578
Coal
2016
2
WA
River Road
7605
1
12-31-2001
23
1.0
254.0
1.064
23.056
105.4
1773.5
Clark
45.65
-122.7231
Natural Gas
2001
1
WA
River Road
7605
1
12-31-2002
23
1.0
255.0
1.061
22.994
105.1
1768.8
Clark
45.65
-122.7231
Natural Gas
2002
1
WA
River Road
7605
1
12-31-2003
23
1.0
257.0
1.082
18.032
107.2
1803.2
Clark
45.65
-122.7231
Natural Gas
2003
1
WA
River Road
7605
1
12-31-2004
23
1.0
251.0
2.857
20.96
103.8
1746.7
Clark
45.65
-122.7231
Natural Gas
2004
1
WA
River Road
7605
1
12-31-2005
23
1.0
246.0
2.867
22.177
101.4
1705.9
Clark
45.65
-122.7231
Natural Gas
2005
1
WA
River Road
7605
1
12-31-2006
23
1.0
263.0
1.013
21.689
107.4
1807.4
Clark
45.65
-122.7231
Natural Gas
2006
1
WA
River Road
7605
1
12-29-2007
7
1.0
253.0
0.976
21.186
104.9
1765.5
Clark
45.65
-122.7231
Natural Gas
2007
1
WA
River Road
7605
1
12-27-2008
7
1.0
249.0
1.0
21.503
106.5
1791.9
Clark
45.65
-122.7231
Natural Gas
2008
1
WA
River Road
7605
1
12-31-2009
23
1.0
251.0
1.0
21.49
106.4
1790.8
Clark
45.65
-122.7231
Natural Gas
2009
1
WA
River Road
7605
1
12-31-2010
23
1.0
261.0
1.015
21.864
108.3
1822.0
Clark
45.65
-122.7231
Natural Gas
2010
1
WA
River Road
7605
1
12-31-2011
23
0.0
Clark
45.65
-122.7231
Natural Gas
2011
1
WA
River Road
7605
1
12-31-2012
23
1.0
267.0
1.039
22.546
111.7
1878.8
Clark
45.65
-122.7231
Natural Gas
2012
1
WA
River Road
7605
1
12-31-2013
23
1.0
257.0
1.01
23.746
108.6
1826.6
Clark
45.65
-122.7231
Natural Gas
2013
1
WA
River Road
7605
1
12-31-2014
23
1.0
264.0
1.025
24.379
111.4
1875.3
Clark
45.65
-122.7231
Natural Gas
2014
1
WA
River Road
7605
1
12-31-2015
23
1.0
256.0
0.984
22.284
110.4
1857.0
Clark
45.65
-122.7231
Natural Gas
2015
1
WA
River Road
7605
1
12-31-2016
23
1.0
255.0
0.995
22.666
112.2
1888.8
Clark
45.65
-122.7231
Natural Gas
2016
1
WA
Encogen Generating Station
7870
CT2
12-31-2006
23
0.0
Whatcom
48.746
-122.486
Natural Gas
2006
3
WA
Encogen Generating Station
7870
CT3
12-27-2007
7
0.0
Whatcom
48.746
-122.486
Natural Gas
2007
3
WA
Encogen Generating Station
7870
CT3
12-01-2008
23
0.27
9.0
0.189
1.899
2.97
49.977
Whatcom
48.746
-122.486
Natural Gas
2008
3
WA
Encogen Generating Station
7870
CT3
12-31-2009
23
0.0
Whatcom
48.746
-122.486
Natural Gas
2009
3
WA
Encogen Generating Station
7870
CT3
12-31-2010
23
1.0
22.0
0.9
7.03
17.4
292.9
Whatcom
48.746
-122.486
Natural Gas
2010
3
WA
Encogen Generating Station
7870
CT3
12-31-2011
23
0.0
Whatcom
48.746
-122.486
Natural Gas
2011
3
WA
Encogen Generating Station
7870
CT3
12-31-2012
23
0.0
Whatcom
48.746
-122.486
Natural Gas
2012
3
WA
Encogen Generating Station
7870
CT3
12-31-2013
23
0.0
Whatcom
48.746
-122.486
Natural Gas
2013
3
WA
Encogen Generating Station
7870
CT3
12-31-2014
23
1.0
29.0
0.809
10.117
22.3
374.7
Whatcom
48.746
-122.486
Natural Gas
2014
3
WA
Encogen Generating Station
7870
CT3
12-31-2015
23
1.0
38.0
0.98
12.134
27.7
466.7
Whatcom
48.746
-122.486
Natural Gas
2015
3
WA
Encogen Generating Station
7870
CT3
12-31-2016
23
0.0
Whatcom
48.746
-122.486
Natural Gas
2016
3
WA
Finley Combustion Turbine
7945
1
12-31-2001
23
0.0
Benton
46.1572
-119.0094
Natural Gas
2001
1
WA
Finley Combustion Turbine
7945
1
12-31-2002
23
0.0
Benton
46.1572
-119.0094
Natural Gas
2002
1
WA
Finley Combustion Turbine
7945
1
12-31-2003
23
0.0
Benton
46.1572
-119.0094
Natural Gas
2003
1
WA
Finley Combustion Turbine
7945
1
12-31-2004
23
0.0
Benton
46.1572
-119.0094
Natural Gas
2004
1
WA
Finley Combustion Turbine
7945
1
12-31-2005
23
0.0
Benton
46.1572
-119.0094
Natural Gas
2005
1
WA
Finley Combustion Turbine
7945
1
12-31-2006
23
0.0
Benton
46.1572
-119.0094
Natural Gas
2006
1
WA
Finley Combustion Turbine
7945
1
03-27-2007
7
0.0
Benton
46.1572
-119.0094
Natural Gas
2007
1
WA
Grays Harbor Energy Center
7999
2
12-27-2008
23
Grays Harbor
46.9688
-123.48
Natural Gas
2008
2
WA
Grays Harbor Energy Center
7999
2
12-31-2009
23
0.0
Grays Harbor
46.9688
-123.48
Natural Gas
2009
2
WA
Grays Harbor Energy Center
7999
2
12-31-2010
23
0.0
Grays Harbor
46.9688
-123.48
Natural Gas
2010
2
WA
Grays Harbor Energy Center
7999
2
12-31-2011
23
0.0
Grays Harbor
46.9688
-123.48
Natural Gas
2011
2
WA
Grays Harbor Energy Center
7999
2
12-31-2012
23
0.0
Grays Harbor
46.9688
-123.48
Natural Gas
2012
2
WA
Grays Harbor Energy Center
7999
2
12-31-2013
23
0.0
Grays Harbor
46.9688
-123.48
Natural Gas
2013
2
WA
Grays Harbor Energy Center
7999
2
12-31-2014
23
0.0
Grays Harbor
46.9688
-123.48
Natural Gas
2014
2
WA
Grays Harbor Energy Center
7999
2
12-31-2015
23
1.0
265.0
0.485
12.883
109.4
1840.4
Grays Harbor
46.9688
-123.48
Natural Gas
2015
2
WA
Grays Harbor Energy Center
7999
2
12-31-2016
23
0.0
Grays Harbor
46.9688
-123.48
Natural Gas
2016
2
WA
Sumas Generating Station
54476
CT-1
12-31-2009
23
0.0
Whatcom
48.9905
-122.2733
Natural Gas
2009
1
WA
Sumas Generating Station
54476
CT-1
12-31-2010
23
0.0
Whatcom
48.9905
-122.2733
Natural Gas
2010
1
WA
Sumas Generating Station
54476
CT-1
12-31-2011
23
0.0
Whatcom
48.9905
-122.2733
Natural Gas
2011
1
WA
Sumas Generating Station
54476
CT-1
12-31-2012
23
0.0
Whatcom
48.9905
-122.2733
Natural Gas
2012
1
WA
Sumas Generating Station
54476
CT-1
12-31-2013
23
1.0
60.0
1.882
15.254
45.3
762.7
Whatcom
48.9905
-122.2733
Natural Gas
2013
1
WA
Sumas Generating Station
54476
CT-1
12-31-2014
23
1.0
57.0
1.596
16.269
43.9
739.5
Whatcom
48.9905
-122.2733
Natural Gas
2014
1
WA
Sumas Generating Station
54476
CT-1
12-31-2015
23
1.0
87.0
2.181
18.7
61.7
1038.9
Whatcom
48.9905
-122.2733
Natural Gas
2015
1
WA
Sumas Generating Station
54476
CT-1
12-31-2016
23
0.0
Whatcom
48.9905
-122.2733
Natural Gas
2016
1
WA
Ferndale Generating Station
54537
CT-1B
12-31-2012
23
0.0
Whatcom
48.8283
-122.6853
Natural Gas
2012
2
WA
Ferndale Generating Station
54537
CT-1B
12-31-2013
23
0.0
Whatcom
48.8283
-122.6853
Natural Gas
2013
2
WA
Ferndale Generating Station
54537
CT-1B
12-31-2014
23
1.0
35.0
1.231
10.704
31.8
535.2
Whatcom
48.8283
-122.6853
Natural Gas
2014
2
WA
Ferndale Generating Station
54537
CT-1B
12-31-2015
23
1.0
86.0
1.732
25.468
60.5
1018.7
Whatcom
48.8283
-122.6853
Natural Gas
2015
2
WA
Ferndale Generating Station
54537
CT-1B
12-31-2016
23
0.0
Whatcom
48.8283
-122.6853
Natural Gas
2016
2
WA
Goldendale Generating Station
55482
CT-1
12-31-2004
23
0.0
Klickitat
45.8114
-120.833
Pipeline Natural Gas
2004
1
WA
Goldendale Generating Station
55482
CT-1
12-31-2005
23
0.0
Klickitat
45.8114
-120.833
Pipeline Natural Gas
2005
1
WA
Goldendale Generating Station
55482
CT-1
12-31-2006
23
1.0
167.0
0.974
9.737
96.4
1622.9
Klickitat
45.8114
-120.833
Pipeline Natural Gas
2006
1
WA
Goldendale Generating Station
55482
CT-1
12-30-2007
15
0.0
Klickitat
45.8114
-120.833
Pipeline Natural Gas
2007
1
WA
Goldendale Generating Station
55482
CT-1
12-28-2008
15
1.0
114.0
0.6
6.628
56.3
946.8
Klickitat
45.8114
-120.833
Pipeline Natural Gas
2008
1
WA
Goldendale Generating Station
55482
CT-1
12-31-2009
23
1.0
188.0
0.786
9.168
77.8
1309.7
Klickitat
45.8114
-120.833
Pipeline Natural Gas
2009
1
WA
Goldendale Generating Station
55482
CT-1
12-31-2010
23
1.0
273.0
1.061
12.373
105.0
1767.5
Klickitat
45.8114
-120.833
Pipeline Natural Gas
2010
1
WA
Goldendale Generating Station
55482
CT-1
12-31-2011
23
1.0
260.0
1.003
11.702
99.3
1671.7
Klickitat
45.8114
-120.833
Pipeline Natural Gas
2011
1
WA
Goldendale Generating Station
55482
CT-1
12-31-2012
23
0.0
Klickitat
45.8114
-120.833
Pipeline Natural Gas
2012
1
WA
Goldendale Generating Station
55482
CT-1
12-31-2013
23
1.0
264.0
1.046
12.199
103.6
1742.7
Klickitat
45.8114
-120.833
Pipeline Natural Gas
2013
1
WA
Goldendale Generating Station
55482
CT-1
12-31-2014
23
1.0
206.0
0.851
9.925
84.3
1417.8
Klickitat
45.8114
-120.833
Pipeline Natural Gas
2014
1
WA
Goldendale Generating Station
55482
CT-1
12-31-2015
23
1.0
264.0
1.035
12.072
102.5
1724.6
Klickitat
45.8114
-120.833
Pipeline Natural Gas
2015
1
WA
Goldendale Generating Station
55482
CT-1
12-31-2016
23
0.0
Klickitat
45.8114
-120.833
Pipeline Natural Gas
2016
1
WA
Chehalis Generation Facility
55662
CT2
12-31-2003
23
1.0
181.0
1.151
17.264
114.0
1918.2
Lewis
46.6226
-122.9145
Pipeline Natural Gas
2003
2
WA
Chehalis Generation Facility
55662
CT2
12-31-2004
23
0.0
Lewis
46.6226
-122.9145
Pipeline Natural Gas
2004
2
WA
Chehalis Generation Facility
55662
CT2
12-31-2005
23
0.0
Lewis
46.6226
-122.9145
Pipeline Natural Gas
2005
2
WA
Chehalis Generation Facility
55662
CT1
12-31-2006
23
1.0
172.0
4.131
16.165
106.7
1796.1
Lewis
46.6226
-122.9145
Pipeline Natural Gas
2006
2
WA
Chehalis Generation Facility
55662
CT2
12-28-2007
15
0.0
Lewis
46.6226
-122.9145
Pipeline Natural Gas
2007
2
WA
Chehalis Generation Facility
55662
CT2
12-02-2008
23
0.0
Lewis
46.6226
-122.9145
Pipeline Natural Gas
2008
2
WA
Chehalis Generation Facility
55662
CT2
12-31-2009
23
0.0
Lewis
46.6226
-122.9145
Natural Gas
2009
2
WA
Chehalis Generation Facility
55662
CT2
12-31-2010
23
0.0
Lewis
46.6226
-122.9145
Natural Gas
2010
2
WA
Chehalis Generation Facility
55662
CT2
12-31-2011
23
0.0
Lewis
46.6226
-122.9145
Natural Gas
2011
2
WA
Chehalis Generation Facility
55662
CT2
12-31-2012
23
0.0
Lewis
46.6226
-122.9145
Natural Gas
2012
2
WA
Chehalis Generation Facility
55662
CT2
12-31-2013
23
0.0
Lewis
46.6226
-122.9145
Natural Gas
2013
2
WA
Chehalis Generation Facility
55662
CT2
12-31-2014
23
1.0
215.0
1.26
12.138
90.2
1517.2
Lewis
46.6226
-122.9145
Natural Gas
2014
2
WA
Chehalis Generation Facility
55662
CT2
12-31-2015
23
0.0
Lewis
46.6226
-122.9145
Natural Gas
2015
2
WA
Chehalis Generation Facility
55662
CT2
12-31-2016
23
1.0
186.0
1.443
10.998
81.7
1374.7
Lewis
46.6226
-122.9145
Natural Gas
2016
2
WA
Mint Farm Generating Station
55700
CTG1
12-31-2008
21
0.0
Cowlitz
46.1403
-122.9842
Pipeline Natural Gas
2008
1
WA
Mint Farm Generating Station
55700
CTG1
12-31-2009
23
0.0
Cowlitz
46.1403
-122.9842
Natural Gas
2009
1
WA
Mint Farm Generating Station
55700
CTG1
12-31-2010
23
1.0
278.0
4.795
13.523
114.8
1931.9
Cowlitz
46.1403
-122.9842
Natural Gas
2010
1
WA
Mint Farm Generating Station
55700
CTG1
12-31-2011
23
0.0
Cowlitz
46.1403
-122.9842
Natural Gas
2011
1
WA
Mint Farm Generating Station
55700
CTG1
12-31-2012
23
0.0
Cowlitz
46.1403
-122.9842
Natural Gas
2012
1
WA
Mint Farm Generating Station
55700
CTG1
12-31-2013
23
1.0
264.0
2.048
13.045
110.7
1863.5
Cowlitz
46.1403
-122.9842
Natural Gas
2013
1
WA
Mint Farm Generating Station
55700
CTG1
12-31-2014
23
1.0
225.0
2.19
11.353
96.4
1621.8
Cowlitz
46.1403
-122.9842
Natural Gas
2014
1
WA
Mint Farm Generating Station
55700
CTG1
12-31-2015
23
1.0
258.0
2.464
15.024
111.6
1878.0
Cowlitz
46.1403
-122.9842
Natural Gas
2015
1
WA
Mint Farm Generating Station
55700
CTG1
12-31-2016
23
0.0
Cowlitz
46.1403
-122.9842
Natural Gas
2016
1
WA
Frederickson Power LP
55818
F1CT
12-31-2002
23
0.0
Pierce
47.0864
-122.3644
Pipeline Natural Gas
2002
1
WA
Frederickson Power LP
55818
F1CT
12-31-2003
23
0.0
Pierce
47.0864
-122.3644
Pipeline Natural Gas
2003
1
WA
Frederickson Power LP
55818
F1CT
12-31-2004
23
0.0
Pierce
47.0864
-122.3644
Pipeline Natural Gas
2004
1
WA
Frederickson Power LP
55818
F1CT
12-31-2005
23
0.0
Pierce
47.0864
-122.3644
Pipeline Natural Gas
2005
1
WA
Frederickson Power LP
55818
F1CT
12-31-2006
23
0.0
Pierce
47.0864
-122.3644
Pipeline Natural Gas
2006
1
WA
Frederickson Power LP
55818
F1CT
12-27-2007
7
0.0
Pierce
47.0864
-122.3644
Pipeline Natural Gas
2007
1
WA
Frederickson Power LP
55818
F1CT
12-29-2008
7
Pierce
47.0864
-122.3644
Pipeline Natural Gas
2008
1
WA
Frederickson Power LP
55818
F1CT
12-31-2009
23
0.0
Pierce
47.0864
-122.3644
Pipeline Natural Gas
2009
1
WA
Frederickson Power LP
55818
F1CT
12-31-2010
23
0.0
Pierce
47.0864
-122.3644
Pipeline Natural Gas
2010
1
WA
Frederickson Power LP
55818
F1CT
12-31-2011
23
0.0
Pierce
47.0864
-122.3644
Pipeline Natural Gas
2011
1
WA
Frederickson Power LP
55818
F1CT
12-31-2012
23
0.0
Pierce
47.0864
-122.3644
Pipeline Natural Gas
2012
1
WA
Frederickson Power LP
55818
F1CT
12-31-2013
23
1.0
253.0
1.075
17.908
106.4
1790.8
Pierce
47.0864
-122.3644
Pipeline Natural Gas
2013
1
WA
Frederickson Power LP
55818
F1CT
12-31-2014
23
1.0
255.0
1.106
18.436
109.6
1843.6
Pierce
47.0864
-122.3644
Pipeline Natural Gas
2014
1
WA
Frederickson Power LP
55818
F1CT
12-31-2015
23
1.0
265.0
1.15
21.075
113.9
1915.9
Pierce
47.0864
-122.3644
Pipeline Natural Gas
2015
1
WA
Frederickson Power LP
55818
F1CT
12-31-2016
23
0.0
Pierce
47.0864
-122.3644
Pipeline Natural Gas
2016
1
In [13]:
%sql select fuel_type, substr(op_date, -4) as year, count(distinct orispl_code) as number_of_plants from data group by fuel_type, substr(op_date, -4)
Done.
Out[13]:
fuel_type
year
number_of_plants
Coal
2001
1
Coal
2002
1
Coal
2003
1
Coal
2004
1
Coal
2005
1
Coal
2006
1
Coal
2007
1
Coal
2008
1
Coal
2009
1
Coal
2010
1
Coal
2011
1
Coal
2012
1
Coal
2013
1
Coal
2014
1
Coal
2015
1
Coal
2016
1
Natural Gas
2001
2
Natural Gas
2002
3
Natural Gas
2003
3
Natural Gas
2004
3
Natural Gas
2005
3
Natural Gas
2006
4
Natural Gas
2007
4
Natural Gas
2008
4
Natural Gas
2009
8
Natural Gas
2010
8
Natural Gas
2011
8
Natural Gas
2012
9
Natural Gas
2013
9
Natural Gas
2014
9
Natural Gas
2015
8
Natural Gas
2016
8
Pipeline Natural Gas
2001
1
Pipeline Natural Gas
2002
2
Pipeline Natural Gas
2003
3
Pipeline Natural Gas
2004
4
Pipeline Natural Gas
2005
4
Pipeline Natural Gas
2006
4
Pipeline Natural Gas
2007
4
Pipeline Natural Gas
2008
5
Pipeline Natural Gas
2009
2
Pipeline Natural Gas
2010
2
Pipeline Natural Gas
2011
2
Pipeline Natural Gas
2012
2
Pipeline Natural Gas
2013
2
Pipeline Natural Gas
2014
2
Pipeline Natural Gas
2015
2
Pipeline Natural Gas
2016
2
In [14]:
%sql sqlite:///smaller.db
%sql select state, fuel_type, substr(op_date, -4) as year, count(distinct orispl_code) as number_of_plants from data group by state, fuel_type, substr(op_date, -4)
Done.
Out[14]:
state
fuel_type
year
number_of_plants
AZ
Coal
2001
6
AZ
Coal
2002
6
AZ
Coal
2003
6
AZ
Coal
2004
6
AZ
Coal
2005
6
AZ
Coal
2006
6
AZ
Coal
2007
6
AZ
Coal
2008
6
AZ
Coal
2009
6
AZ
Coal
2010
6
AZ
Coal
2011
6
AZ
Coal
2012
6
AZ
Coal
2013
6
AZ
Coal
2014
6
AZ
Coal
2015
6
AZ
Coal
2016
6
AZ
Diesel Oil, Pipeline Natural Gas
2001
1
AZ
Diesel Oil, Pipeline Natural Gas
2002
1
AZ
Diesel Oil, Pipeline Natural Gas
2003
1
AZ
Diesel Oil, Pipeline Natural Gas
2004
1
AZ
Natural Gas
2009
3
AZ
Natural Gas
2010
3
AZ
Natural Gas
2011
3
AZ
Natural Gas
2012
3
AZ
Natural Gas
2013
3
AZ
Natural Gas
2014
3
AZ
Natural Gas
2015
3
AZ
Natural Gas
2016
3
AZ
Natural Gas, Pipeline Natural Gas
2008
3
AZ
Natural Gas, Pipeline Natural Gas
2016
1
AZ
Pipeline Natural Gas
2001
12
AZ
Pipeline Natural Gas
2002
15
AZ
Pipeline Natural Gas
2003
18
AZ
Pipeline Natural Gas
2004
18
AZ
Pipeline Natural Gas
2005
19
AZ
Pipeline Natural Gas
2006
19
AZ
Pipeline Natural Gas
2007
19
AZ
Pipeline Natural Gas
2008
17
AZ
Pipeline Natural Gas
2009
17
AZ
Pipeline Natural Gas
2010
18
AZ
Pipeline Natural Gas
2011
18
AZ
Pipeline Natural Gas
2012
18
AZ
Pipeline Natural Gas
2013
18
AZ
Pipeline Natural Gas
2014
18
AZ
Pipeline Natural Gas
2015
18
AZ
Pipeline Natural Gas
2016
18
CA
2002
1
CA
2003
1
CA
2004
1
CA
2005
1
CA
2006
1
CA
2007
1
CA
2008
1
CA
2009
1
CA
2010
1
CA
2011
1
CA
Natural Gas
2001
1
CA
Natural Gas
2002
1
CA
Natural Gas
2003
1
CA
Natural Gas
2004
1
CA
Natural Gas
2005
1
CA
Natural Gas
2006
1
CA
Natural Gas
2007
1
CA
Natural Gas
2008
1
CA
Natural Gas
2009
1
CA
Natural Gas
2010
1
CA
Natural Gas
2011
1
CA
Natural Gas
2012
1
CA
Natural Gas
2013
1
CA
Natural Gas
2014
1
CA
Natural Gas
2015
1
CA
Natural Gas
2016
1
CA
Other Gas
2001
1
CA
Other Gas
2002
1
CA
Other Gas
2003
1
CA
Other Gas
2004
1
CA
Other Gas
2005
1
CA
Other Gas
2006
1
CA
Other Gas
2007
1
CA
Other Gas
2008
1
CA
Other Gas
2009
1
CA
Other Gas
2010
1
CA
Other Gas
2012
1
CA
Other Gas
2013
1
CA
Other Gas
2014
1
CA
Other Gas
2015
1
CA
Other Gas
2016
1
CA
Other Gas, Pipeline Natural Gas
2011
1
CA
Pipeline Natural Gas
2001
41
CA
Pipeline Natural Gas
2002
54
CA
Pipeline Natural Gas
2003
65
CA
Pipeline Natural Gas
2004
65
CA
Pipeline Natural Gas
2005
74
CA
Pipeline Natural Gas
2006
80
CA
Pipeline Natural Gas
2007
88
CA
Pipeline Natural Gas
2008
89
CA
Pipeline Natural Gas
2009
95
CA
Pipeline Natural Gas
2010
98
CA
Pipeline Natural Gas
2011
97
CA
Pipeline Natural Gas
2012
102
CA
Pipeline Natural Gas
2013
109
CA
Pipeline Natural Gas
2014
107
CA
Pipeline Natural Gas
2015
105
CA
Pipeline Natural Gas
2016
107
CA
Wood
2014
1
CA
Wood
2015
1
CA
Wood
2016
1
CO
Coal
2001
12
CO
Coal
2002
12
CO
Coal
2003
12
CO
Coal
2004
12
CO
Coal
2005
12
CO
Coal
2006
12
CO
Coal
2007
12
CO
Coal
2008
12
CO
Coal
2009
13
CO
Coal
2010
13
CO
Coal
2011
12
CO
Coal
2012
12
CO
Coal
2013
12
CO
Coal
2014
11
CO
Coal
2015
11
CO
Coal
2016
11
CO
Natural Gas
2003
1
CO
Natural Gas
2004
1
CO
Natural Gas
2005
1
CO
Pipeline Natural Gas
2001
8
CO
Pipeline Natural Gas
2002
11
CO
Pipeline Natural Gas
2003
12
CO
Pipeline Natural Gas
2004
13
CO
Pipeline Natural Gas
2005
13
CO
Pipeline Natural Gas
2006
14
CO
Pipeline Natural Gas
2007
15
CO
Pipeline Natural Gas
2008
15
CO
Pipeline Natural Gas
2009
16
CO
Pipeline Natural Gas
2010
16
CO
Pipeline Natural Gas
2011
16
CO
Pipeline Natural Gas
2012
17
CO
Pipeline Natural Gas
2013
17
CO
Pipeline Natural Gas
2014
17
CO
Pipeline Natural Gas
2015
18
CO
Pipeline Natural Gas
2016
18
ID
Pipeline Natural Gas
2001
3
ID
Pipeline Natural Gas
2002
3
ID
Pipeline Natural Gas
2003
3
ID
Pipeline Natural Gas
2004
3
ID
Pipeline Natural Gas
2005
4
ID
Pipeline Natural Gas
2006
4
ID
Pipeline Natural Gas
2007
4
ID
Pipeline Natural Gas
2008
4
ID
Pipeline Natural Gas
2009
4
ID
Pipeline Natural Gas
2010
4
ID
Pipeline Natural Gas
2011
4
ID
Pipeline Natural Gas
2012
5
ID
Pipeline Natural Gas
2013
5
ID
Pipeline Natural Gas
2014
5
ID
Pipeline Natural Gas
2015
5
ID
Pipeline Natural Gas
2016
5
MT
Coal
2001
3
MT
Coal
2002
3
MT
Coal
2003
3
MT
Coal
2004
3
MT
Coal
2005
3
MT
Coal
2006
4
MT
Coal
2007
4
MT
Coal
2008
4
MT
Coal
2009
4
MT
Coal
2010
4
MT
Coal
2011
4
MT
Coal
2012
4
MT
Coal
2013
4
MT
Coal
2014
4
MT
Coal
2015
4
MT
Coal
2016
3
MT
Pipeline Natural Gas
2003
1
MT
Pipeline Natural Gas
2004
1
MT
Pipeline Natural Gas
2005
1
MT
Pipeline Natural Gas
2006
1
MT
Pipeline Natural Gas
2007
1
MT
Pipeline Natural Gas
2008
1
MT
Pipeline Natural Gas
2009
1
MT
Pipeline Natural Gas
2010
3
MT
Pipeline Natural Gas
2011
3
MT
Pipeline Natural Gas
2012
3
MT
Pipeline Natural Gas
2013
4
MT
Pipeline Natural Gas
2014
4
MT
Pipeline Natural Gas
2015
4
MT
Pipeline Natural Gas
2016
3
NM
Coal
2001
3
NM
Coal
2002
3
NM
Coal
2003
3
NM
Coal
2004
3
NM
Coal
2005
3
NM
Coal
2006
3
NM
Coal
2007
3
NM
Coal
2008
3
NM
Coal
2009
3
NM
Coal
2010
3
NM
Coal
2011
3
NM
Coal
2012
3
NM
Coal
2013
3
NM
Coal
2014
3
NM
Coal
2015
3
NM
Coal
2016
3
NM
Natural Gas
2014
1
NM
Natural Gas
2015
1
NM
Natural Gas
2016
1
NM
Pipeline Natural Gas
2001
6
NM
Pipeline Natural Gas
2002
8
NM
Pipeline Natural Gas
2003
9
NM
Pipeline Natural Gas
2004
9
NM
Pipeline Natural Gas
2005
10
NM
Pipeline Natural Gas
2006
11
NM
Pipeline Natural Gas
2007
11
NM
Pipeline Natural Gas
2008
13
NM
Pipeline Natural Gas
2009
13
NM
Pipeline Natural Gas
2010
13
NM
Pipeline Natural Gas
2011
13
NM
Pipeline Natural Gas
2012
13
NM
Pipeline Natural Gas
2013
13
NM
Pipeline Natural Gas
2014
12
NM
Pipeline Natural Gas
2015
12
NM
Pipeline Natural Gas
2016
13
NV
Coal
2001
3
NV
Coal
2002
3
NV
Coal
2003
3
NV
Coal
2004
3
NV
Coal
2005
3
NV
Coal
2006
3
NV
Coal
2007
3
NV
Coal
2008
4
NV
Coal
2009
4
NV
Coal
2010
4
NV
Coal
2011
3
NV
Coal
2012
3
NV
Coal
2013
3
NV
Coal
2014
3
NV
Coal
2015
3
NV
Coal
2016
3
NV
Other Gas, Pipeline Natural Gas
2001
1
NV
Other Gas, Pipeline Natural Gas
2002
1
NV
Other Gas, Pipeline Natural Gas
2003
1
NV
Pipeline Natural Gas
2001
6
NV
Pipeline Natural Gas
2002
7
NV
Pipeline Natural Gas
2003
10
NV
Pipeline Natural Gas
2004
11
NV
Pipeline Natural Gas
2005
11
NV
Pipeline Natural Gas
2006
11
NV
Pipeline Natural Gas
2007
11
NV
Pipeline Natural Gas
2008
12
NV
Pipeline Natural Gas
2009
12
NV
Pipeline Natural Gas
2010
12
NV
Pipeline Natural Gas
2011
12
NV
Pipeline Natural Gas
2012
11
NV
Pipeline Natural Gas
2013
11
NV
Pipeline Natural Gas
2014
11
NV
Pipeline Natural Gas
2015
12
NV
Pipeline Natural Gas
2016
12
NV
Residual Oil
2001
1
NV
Residual Oil
2002
1
NV
Residual Oil
2003
1
NV
Residual Oil
2004
1
NV
Residual Oil
2005
1
NV
Residual Oil
2006
1
NV
Residual Oil
2007
1
NV
Residual Oil
2008
1
NV
Residual Oil
2009
1
NV
Residual Oil
2010
1
NV
Residual Oil
2011
1
NV
Residual Oil
2012
1
NV
Residual Oil
2013
1
NV
Residual Oil
2014
1
OR
Coal
2001
1
OR
Coal
2002
1
OR
Coal
2003
1
OR
Coal
2004
1
OR
Coal
2005
1
OR
Coal
2006
1
OR
Coal
2007
1
OR
Coal
2008
1
OR
Coal
2009
1
OR
Coal
2010
1
OR
Coal
2011
1
OR
Coal
2012
1
OR
Coal
2013
1
OR
Coal
2014
1
OR
Coal
2015
1
OR
Coal
2016
1
OR
Natural Gas
2008
1
OR
Natural Gas
2009
1
OR
Natural Gas
2010
1
OR
Natural Gas
2011
1
OR
Natural Gas
2012
1
OR
Natural Gas
2013
1
OR
Natural Gas
2014
1
OR
Natural Gas
2015
1
OR
Natural Gas
2016
1
OR
Natural Gas, Pipeline Natural Gas
2007
1
OR
Pipeline Natural Gas
2001
3
OR
Pipeline Natural Gas
2002
5
OR
Pipeline Natural Gas
2003
5
OR
Pipeline Natural Gas
2004
6
OR
Pipeline Natural Gas
2005
6
OR
Pipeline Natural Gas
2006
6
OR
Pipeline Natural Gas
2007
6
OR
Pipeline Natural Gas
2008
6
OR
Pipeline Natural Gas
2009
5
OR
Pipeline Natural Gas
2010
5
OR
Pipeline Natural Gas
2011
5
OR
Pipeline Natural Gas
2012
5
OR
Pipeline Natural Gas
2013
5
OR
Pipeline Natural Gas
2014
5
OR
Pipeline Natural Gas
2015
5
OR
Pipeline Natural Gas
2016
6
TX
Coal
2001
18
TX
Coal
2002
18
TX
Coal
2003
18
TX
Coal
2004
18
TX
Coal
2005
18
TX
Coal
2006
18
TX
Coal
2007
18
TX
Coal
2008
18
TX
Coal
2009
20
TX
Coal
2010
20
TX
Coal
2011
20
TX
Coal
2012
21
TX
Coal
2013
21
TX
Coal
2014
21
TX
Coal
2015
21
TX
Coal
2016
21
TX
Natural Gas
2001
1
TX
Natural Gas
2002
1
TX
Natural Gas
2003
1
TX
Natural Gas
2004
1
TX
Natural Gas
2005
1
TX
Natural Gas
2015
1
TX
Natural Gas
2016
1
TX
Natural Gas, Pipeline Natural Gas
2001
1
TX
Natural Gas, Pipeline Natural Gas
2006
1
TX
Other Gas
2001
1
TX
Other Gas
2002
1
TX
Other Gas
2003
1
TX
Other Gas
2004
1
TX
Other Gas
2005
1
TX
Other Gas
2006
1
TX
Other Gas
2007
1
TX
Other Gas
2008
1
TX
Other Gas
2009
1
TX
Other Gas
2010
1
TX
Other Gas
2011
1
TX
Other Gas
2012
1
TX
Other Gas
2013
1
TX
Other Gas
2014
1
TX
Other Gas
2015
1
TX
Other Gas
2016
1
TX
Petroleum Coke
2002
1
TX
Petroleum Coke
2003
1
TX
Petroleum Coke
2004
1
TX
Petroleum Coke
2005
1
TX
Petroleum Coke
2006
1
TX
Petroleum Coke
2007
1
TX
Petroleum Coke
2008
1
TX
Petroleum Coke
2009
1
TX
Petroleum Coke
2010
1
TX
Petroleum Coke
2011
1
TX
Petroleum Coke
2012
1
TX
Petroleum Coke
2013
1
TX
Pipeline Natural Gas
2001
93
TX
Pipeline Natural Gas
2002
102
TX
Pipeline Natural Gas
2003
107
TX
Pipeline Natural Gas
2004
107
TX
Pipeline Natural Gas
2005
103
TX
Pipeline Natural Gas
2006
103
TX
Pipeline Natural Gas
2007
104
TX
Pipeline Natural Gas
2008
108
TX
Pipeline Natural Gas
2009
105
TX
Pipeline Natural Gas
2010
105
TX
Pipeline Natural Gas
2011
105
TX
Pipeline Natural Gas
2012
103
TX
Pipeline Natural Gas
2013
102
TX
Pipeline Natural Gas
2014
104
TX
Pipeline Natural Gas
2015
105
TX
Pipeline Natural Gas
2016
103
TX
Wood
2012
1
TX
Wood
2013
1
TX
Wood
2014
1
TX
Wood
2015
1
TX
Wood
2016
1
UT
Coal
2001
5
UT
Coal
2002
5
UT
Coal
2003
5
UT
Coal
2004
5
UT
Coal
2005
5
UT
Coal
2006
5
UT
Coal
2007
5
UT
Coal
2008
5
UT
Coal
2009
5
UT
Coal
2010
5
UT
Coal
2011
5
UT
Coal
2012
5
UT
Coal
2013
5
UT
Coal
2014
5
UT
Coal
2015
6
UT
Coal
2016
6
UT
Pipeline Natural Gas
2001
2
UT
Pipeline Natural Gas
2002
3
UT
Pipeline Natural Gas
2003
3
UT
Pipeline Natural Gas
2004
4
UT
Pipeline Natural Gas
2005
5
UT
Pipeline Natural Gas
2006
6
UT
Pipeline Natural Gas
2007
7
UT
Pipeline Natural Gas
2008
6
UT
Pipeline Natural Gas
2009
6
UT
Pipeline Natural Gas
2010
6
UT
Pipeline Natural Gas
2011
6
UT
Pipeline Natural Gas
2012
6
UT
Pipeline Natural Gas
2013
6
UT
Pipeline Natural Gas
2014
6
UT
Pipeline Natural Gas
2015
6
UT
Pipeline Natural Gas
2016
6
WA
Coal
2001
1
WA
Coal
2002
1
WA
Coal
2003
1
WA
Coal
2004
1
WA
Coal
2005
1
WA
Coal
2006
1
WA
Coal
2007
1
WA
Coal
2008
1
WA
Coal
2009
1
WA
Coal
2010
1
WA
Coal
2011
1
WA
Coal
2012
1
WA
Coal
2013
1
WA
Coal
2014
1
WA
Coal
2015
1
WA
Coal
2016
1
WA
Natural Gas
2001
2
WA
Natural Gas
2002
3
WA
Natural Gas
2003
3
WA
Natural Gas
2004
3
WA
Natural Gas
2005
3
WA
Natural Gas
2006
4
WA
Natural Gas
2007
4
WA
Natural Gas
2008
4
WA
Natural Gas
2009
8
WA
Natural Gas
2010
8
WA
Natural Gas
2011
8
WA
Natural Gas
2012
9
WA
Natural Gas
2013
9
WA
Natural Gas
2014
9
WA
Natural Gas
2015
8
WA
Natural Gas
2016
8
WA
Pipeline Natural Gas
2001
1
WA
Pipeline Natural Gas
2002
2
WA
Pipeline Natural Gas
2003
3
WA
Pipeline Natural Gas
2004
4
WA
Pipeline Natural Gas
2005
4
WA
Pipeline Natural Gas
2006
4
WA
Pipeline Natural Gas
2007
4
WA
Pipeline Natural Gas
2008
5
WA
Pipeline Natural Gas
2009
2
WA
Pipeline Natural Gas
2010
2
WA
Pipeline Natural Gas
2011
2
WA
Pipeline Natural Gas
2012
2
WA
Pipeline Natural Gas
2013
2
WA
Pipeline Natural Gas
2014
2
WA
Pipeline Natural Gas
2015
2
WA
Pipeline Natural Gas
2016
2
WY
Coal
2001
6
WY
Coal
2002
6
WY
Coal
2003
7
WY
Coal
2004
7
WY
Coal
2005
7
WY
Coal
2006
7
WY
Coal
2007
7
WY
Coal
2008
8
WY
Coal
2009
8
WY
Coal
2010
9
WY
Coal
2011
10
WY
Coal
2012
10
WY
Coal
2013
10
WY
Coal
2014
10
WY
Coal
2015
10
WY
Coal
2016
10
WY
Natural Gas
2001
2
WY
Natural Gas
2002
2
WY
Natural Gas
2003
2
WY
Natural Gas, Pipeline Natural Gas
2004
2
WY
Pipeline Natural Gas
2005
2
WY
Pipeline Natural Gas
2006
2
WY
Pipeline Natural Gas
2007
2
WY
Pipeline Natural Gas
2008
2
WY
Pipeline Natural Gas
2009
2
WY
Pipeline Natural Gas
2010
2
WY
Pipeline Natural Gas
2011
2
WY
Pipeline Natural Gas
2012
2
WY
Pipeline Natural Gas
2013
2
WY
Pipeline Natural Gas
2014
2
WY
Pipeline Natural Gas
2015
3
WY
Pipeline Natural Gas
2016
3
In [ ]:
Content source: maxoboe/CEMS_Project
Similar notebooks: