In [5]:

    

%load_ext sql


    

    




The sql extension is already loaded. To reload it, use:
  %reload_ext sql

In [ ]:

    

%%sql
sqlite:///smaller.db
ATTACH DATABASE 'NG.db' AS NG;
create table NG.data as select * from data where fuel_actual is 'NG';


    

    




Done.
Done.






    
Out[ ]:





[]

In [6]:

    

%%sql
sqlite:///NG.db
drop table if exists cf;
create table cf as 
select name, d.orispl_code, d.unitid, state, op_date, op_hour, gload, 
cast(HEAT_INPUT as float) / cast(max_gen_hr as float) as capacity_factor_hr,
cast(gload as float) / cast(max_gen_gl as float) as capacity_factor_gl
from (select orispl_code, unitid, max(CAST(HEAT_INPUT as float)) as max_gen_hr,
      max(CAST(gload as float)) as max_gen_gl
from data group by orispl_code, unitid) as m, data as d
where d.orispl_code = m.orispl_code and d.unitid = m.unitid;
select * from cf limit 5;


    

    




Done.
Done.
Done.






    
Out[6]:





    

        
name
        
orispl_code
        
unitid
        
state
        
op_date
        
op_hour
        
gload
        
capacity_factor_hr
        
capacity_factor_gl
    
    

        
River Road
        
7605
        
1
        
WA
        
01-01-2001
        
0
        
258.0
        
0.916360481731
        
0.945054945055
    
    

        
River Road
        
7605
        
1
        
WA
        
01-01-2001
        
1
        
258.0
        
0.918758930394
        
0.945054945055
    
    

        
River Road
        
7605
        
1
        
WA
        
01-01-2001
        
2
        
258.0
        
0.921004286589
        
0.945054945055
    
    

        
River Road
        
7605
        
1
        
WA
        
01-01-2001
        
3
        
259.0
        
0.922790365381
        
0.948717948718
    
    

        
River Road
        
7605
        
1
        
WA
        
01-01-2001
        
4
        
259.0
        
0.920085731782
        
0.948717948718
    

In [7]:

    

import sqlite3
import pandas
import os.path
con = sqlite3.connect('NG.db')
labels = pandas.read_sql("select orispl_code, unitid from cf group by orispl_code, unitid", con)


    

In [8]:

    

# Generates csv file for each unit, for each year, of capacity factors.
if not os.path.exists('cf'):
    os.makedirs('cf')
for i, row in labels.iterrows():
    print row['orispl_code'], row['unitid']
    for year in range(2001, 2017):
        file_name = 'cf/' + str(row['orispl_code']) + '_' + row['unitid'].replace('*', '') + '_' + str(year) + '.csv'
        if os.path.isfile(file_name): continue
        table = pandas.read_sql("select name, orispl_code, unitid, op_date, op_hour, capacity_factor_gl, capacity_factor_hr from cf\
        where orispl_code is " + str(row['orispl_code']) + " and unitid is '" + row['unitid'] + "' and \
        substr(op_date, -4) is '" + str(year) + "'", con)
        table.to_csv(file_name)


    

    




9 CTG-1
116 1
116 2
117 CC4
117 CC5A
117 CC5B
118 1
118 2
118 CT3
120 1
120 CT5
120 CT6
124 GT1
126 1
126 2
126 3
141 1
141 2
141 3
147 K-1
147 K-2
147 K-7
160 1
160 4
228 10
228 9
246 1
246 2
247 3
247 4
247 5
247 6
247 7
259 1
259 2
259 3
259 4
260 1A
260 2A
260 3A
260 4A
260 6-1
260 7-1
271 1
271 2
271 3
271 4
271 5
271 6
271 7
273 3-1
302 1
302 2
302 3
302 4
302 5
310 1
310 2
310 3
310 4
315 1
315 2
315 3
315 4
315 5
315 6
329 1
329 2
329 31
329 32
329 41
329 42
330 3
330 4
330 5
330 7
331 1
331 2
331 3
331 4
334 1
334 2
334 3
334 4
335 1
335 2
335 3A
335 4A
341 1
341 2
341 3
341 4
345 1
345 2
350 1
350 2
356 17
356 5
356 6
356 7
356 8
358 1
358 2
358 3-1
358 3-2
358 4-1
358 4-2
375 M4
377 4
377 5
377 9
389 2-2
389 3
389 3-1
389 3-2
389 4
399 **10A
399 **10B
399 10
399 11
399 12
399 13
399 14
400 1
400 10
400 11
400 12
400 13
400 14
400 15
400 16
400 2
400 3
400 4
400 5
400 6
400 9
404 1
404 2
404 3
404 4
404 6
404 7
408 3
408 4
408 5
408 6
408 7
420 B1
420 B2
420 B3
422 GT3
422 GT4
422 GT5
469 5
469 6
478 1
478 2
478 3
607 CT3
607 CT4
2176 GT-2
2322 1
2322 11A
2322 11B
2322 12A
2322 12B
2322 13A
2322 13B
2322 14A
2322 14B
2322 15A
2322 15B
2322 16A
2322 16B
2322 17A
2322 17B
2322 18A
2322 18B
2322 19A
2322 19B
2322 2
2322 20A
2322 20B
2322 21A
2322 21B
2322 22A
2322 22B
2322 3
2326 1
2330 1
2330 2
2336 2
2336 3
2336 4
2336 5
2336 6
2336 8
2336 9
2444 6
2444 7
2444 8
2444 9
2446 051B
2450 1
2450 2
2450 3
2454 121B
2454 122B
2454 123T
2454 124T
3456 **4
3456 **5
3456 1
3456 2
3456 3
3456 GT-6A
3456 GT-6B
3648 1
3648 2
3648 3
3648 4
3648 5
3648 6
3845 30
3845 40
3845 50
3845 60
6013 01
6013 02
6112 2
6112 3
6112 4
6112 5
6112 6
6761 A
6761 B
6761 C
6761 D
6761 F
7082 **3
7082 **4
7082 **5
7082 **6
7266 1
7266 2
7307 5
7307 6
7315 1
7315 2
7315 3
7315 4
7350 CTG1
7350 CTG2
7449 NA1
7456 1
7456 2
7504 CT1
7527 1
7527 2
7551 1A
7551 1B
7551 1C
7552 1
7605 1
7693 1
7870 CT1
7870 CT2
7870 CT3
7945 1
7953 CT1
7953 CT2
7953 CT3
7967 1
7967 2
7975 1
7975 2
7975 3
7975 4
7987 01
7999 1
7999 2
8068 5A
8068 5B
8068 6A
8219 2
8219 3
10034 S-100
10156 GEN1
10169 D1
10294 2
10349 2
10350 1
10405 1
10682 GT2
10682 GT4
10682 GT5
10761 1
10761 2
10761 3
10761 4
10761 5
10812 CT1
50131 1
50707 S001
50707 S002
50707 S003
50707 S004
50707 S005
50864 1
50865 1
54476 CT-1
54537 CT-1A
54537 CT-1B
54694 4101
54749 1
54761 1
54761 2
54814 1
54814 2
54854 3
54854 4
54854 5
55039 GT-1
55077 EDE1
55077 EDE2
55103 CT1
55103 CT2
55112 CT01
55112 CT02
55124 P1
55124 P2
55127 CT1
55127 CT2
55129 DBG1
55129 DBG2
55151 CTG-1
55151 CTG-2
55151 CTG-3
55151 CTG-4
55177 A
55177 B
55179 CTGEN1
55182 CTG1
55182 CTG2
55200 CT5
55200 CT6
55207 CT7
55207 CT8
55210 0001
55217 X724
55217 X725
55282 CTG1
55282 CTG2
55283 1
55283 2
55295 1
55295 2
55306 1CTGA
55306 1CTGB
55306 2CTGA
55306 2CTGB
55306 3CTGA
55306 3CTGB
55306 4CTGA
55306 4CTGB
55322 CTG-1
55322 CTG-2
55322 CTG-3
55322 CTG-4
55328 CTG-1
55328 CTG-2
55333 1
55333 2
55333 3
55343 CTG1
55343 CTG2
55345 CTG-1
55345 CTG-2
55372 CTG1
55372 CTG2
55372 CTG3
55393 1
55393 2
55400 CTG-1
55400 CTG-2
55453 1
55453 2
55453 3
55453 4
55453 5
55453 6
55455 CC1A
55455 CC1B
55455 CC2A
55455 CC2B
55477 CT2
55481 1
55481 2
55481 5
55481 6
55482 CT-1
55494 CT1
55494 CT2
55494 CT3
55494 CT4
55494 CT5
55494 CT6
55499 GT-1
55504 L1
55504 L2
55505 BR1
55505 BR2
55508 GT-1
55510 GT-1
55512 GT-1
55513 GT-1
55514 CTG01
55514 CTG02
55518 CTG1
55518 CTG2
55518 CTG3
55522 CT01
55522 CT02
55522 CT03
55522 CT04
55522 CT05
55522 CT06
55522 CT07
55522 CT08
55522 CT09
55522 CT10
55538 CT1A
55538 CT1B
55538 CTG1
55540 1A
55540 1B
55541 1
55541 2
55541 3
55542 1
55542 2
55544 GT1
55544 GT2
55544 GT3
55544 GT4
55622 U1
55622 U2
55622 U3
55622 U4
55622 U5
55625 UNIT1
55626 UNIT1
55627 UNIT1
55645 CT-01
55645 CT-02
55656 CT001
55656 CT002
55656 CT004
55662 CT1
55662 CT2
55683 1
55687 BHG1
55687 BHG2
55698 HEP1
55698 HEP2
55700 CTG1
55733 CT01
55748 CTG1
55748 CTG2
55748 CTG3
55748 CTG4
55802 CTG1
55807 HPP1
55807 HPP2
55810 S-3
55810 S-4
55810 S-5
55818 F1CT
55835 1
55835 2
55841 A01
55841 A03
55847 UNIT1
55853 1
55853 2
55855 UNIT1
55858 UNT1
55858 UNT2
55875 GT1
55933 TPP1
55933 TPP2
55951 AMP-1
55963 1
55970 2
55970 3
55977 CTG-1
55985 CTG1
55985 CTG2
56026 PCT1
56026 PCT2
56041 M1
56041 M2
56046 1
56078 1
56078 2
56102 CTG1A
56102 CTG1B
56135 1
56135 2
56143 1
56143 2
56143 3
56143 4
56177 U1
56227 PWEU1
56232 1
56232 2
56237 CT01
56237 CT02
56237 CT03
56237 CT04
56239 GT-1
56239 GT-2
56253 MC-1
56253 MC-2
56298 CT001
56298 CT002
56445 CT-01
56445 CT-02
56458 HOBB1
56458 HOBB2
56467 CT1
56467 CT2
56471 CT1
56472 CT1
56473 CT1
56474 CT1
56475 CT1
56476 GT1
56476 GT2
56482 1
56482 2
56532 CT1
56532 CT2
56569 1
56569 2
56606 CT01
56639 1
56639 2
56803 1
56803 2
56803 3
56803 4
56908 1A
56908 1B
56908 2A
56908 2B
56908 3A
56908 3B
56914 CTG1
56914 CTG2
56948 CT01
56948 CT02
56948 CT03
56948 CT04
56948 CT05
56948 CT06
56948 CT07
56948 CT08
56948 CT09
56948 CT10
56948 CT11
56948 CT12
56998 CT01
56998 CT02
56998 CT04
56998 CT05
56998 CT06
56998 CT07
56998 CT08
57001 1
57027 1
57027 2
57027 3
57027 4
57028 CT1
57073 BLR1
57074 BLR1
57075 BLR1
57267 1
57267 2
57267 3
57267 4
57480 HGS1
57482 1
57482 2
57482 3
57482 4
57482 5
57482 6
57482 7
57482 8
57483 GT-1
57483 GT-2
57483 GT-3
57483 GT-4
57515 GT1
57515 GT2
57515 GT3
57515 GT4
57515 GT5
57555 CTG1
57555 CTG2
57555 CTG3
57703 CT01
57703 CT02
57703 CT03
57978 CT1
58122 GEN1
58284 1
58503 CTEU1

In [ ]: