<jupyter_start><jupyter_text>Input Tables<jupyter_code># Input table: service_territory_eia861
columns_serv_terr = [
    'utility_id_eia', 'utility_name_eia', 'report_date','county', 'state'
]
data_serv_terr = [
    (3989, 'City of Colorado Springs - CO', '2017-01-01', 'El Paso', 'CO'),
    (3989, 'City of Colorado Springs - CO', '2017-01-01', 'Teller', 'CO'),
    (3989, 'City of Colorado Springs - (CO)', '2018-01-01', 'El Paso', 'CO'),
    (3989, 'City of Colorado Springs - (CO)', '2018-01-01', 'Teller', 'CO')
]

service_territory_eia861 = pd.DataFrame(
    data_serv_terr,
    columns=columns_serv_terr
)
# Input table: sales_eia861
columns_serv_terr = [
    'utility_id_eia', 'utility_name_eia', 'report_date','county', 'state', 'sales'
]
data_serv_terr = [
    (3989, 'City of Colorado Springs - (CO)', '2017-01-01', 'El Paso', 'CO', 127682),
    (3989, 'City of Colorado Springs - (CO)', '2017-01-01', 'Teller', 'CO', 733947),
    (3989, 'City of Colorado Springs - (CO)', '2018-01-01', 'El Paso', 'CO', 87729),
    (3989, 'City of Colorado Springs - (CO)', '2018-01-01', 'Teller', 'CO', 992734),
    # CO spring incursion on the bubble town
    (3989, 'City of Colorado Springs - (CO)', '2018-01-01', 'Boulder', 'CO', 66666),
]

service_territory_eia861 = pd.DataFrame(
    data_serv_terr,
    columns=columns_serv_terr
)
# Input table: generation_eia923
columns_gen = [ 
    'plant_id_eia', 'generator_id', 'report_date',
    'net_generation_mwh','prime_mover_code', 
    'topping_bottoming_code', 'balancing_authority_code_eia'
]
data_gen = [
    (3, '1', '2018-01-01', 10738., 'ST', 'T', 'SOCO'),
    (3, '1', '2018-02-01',  -348., 'ST', 'T', 'SOCO'),
    (3, '1', '2018-03-01',  -414., 'ST', 'T', 'SOCO'),
    (3, '1', '2018-04-01',  -411., 'ST', 'T', 'SOCO'),
    (3, '1', '2018-05-01',  pd.NA, 'CT', 'T', 'SOCO'),
    (3, '1', '2018-06-01',  -607., 'ST', 'Top', 'SOCO'),
    (3, '1', '2018-07-01',  5022., 'ST', 'Top', 'SOCO'),
    (3, '1', '2018-08-01',  -689., 'ST', 'T', 'SOCO'),
    (3, '1', '2018-09-01',  6718., 'ST', 'T', 'SOCO'),
    (3, '1', '2018-10-01',  3877., 'ST', 'T', 'SOCO'),
    (3, '1', '2018-11-01',  pd.NA, 'ST', 'T', 'SOCO'),
    (3, '1', '2018-12-01',  -494., 'ST', 'T', 'SOCO')]

generation_eia923 = pd.DataFrame(
    data_gen,
    columns=columns_gen
)
# Input table: generators_eia860
columns_gens = [ 
    'plant_id_eia', 'generator_id', 'report_date',
    'capacity_mw','prime_mover_code', 'utility_id_eia',
    'utility_name_eia', 'topping_bottoming_code',
    'state' 
    # we talked about renaming this to be state_plant
]
data_gens = [
    (3, '1', '2018-01-01', 153.1, 'ST', 195, 'Alabama Power Co', 'T', 'AL'),
    (3, '1', '2017-01-01', 153.1, 'ST', 195, 'Alabama Power Co', 'T', 'AL'),
    (3, '2', '2017-01-01', 50, 'ST', 195, 'Alabama Power Co', 'B', 'ALL'),
    (3, '2', '2018-01-01', 50, 'ST', 195, 'Alabama Power Co', 'B', 'AL'),
]

generators_eia860 = pd.DataFrame(
    data_gens,
    columns=columns_gens
)
# Input table: boiler_generator_assn_eia860
columns_bga = [ 
    'plant_id_eia', 'generator_id', 'report_date', 'boiler_id',
]
data_bga = [
    (3, '1', '2018-01-01', '1ST',),
    (3, '1', '2017-01-01', '1ST',),
    (3, '2', '2017-01-01', '2ST',),
    (3, '2', '2018-01-01', '2ST',),
    # oooo new plant/gen
    (4, 'a', '2017-01-01', 'a1',),
    (4, 'a', '2017-01-01', 'a2',),
    (4, 'a', '2017-01-01', 'a2',),
    (4, 'b', '2017-01-01', 'b1',)
]

boiler_generator_assn_eia860 = pd.DataFrame(
    data_bga,
    columns=columns_bga
)<jupyter_output><empty_output><jupyter_text>Output tables (post-harvest)<jupyter_code># Output table: plant_entity_eia860
#    Inputs: generators_eia860 + generation_eia923
#    PK columns: 'plant_id_eia'
#    Harvested column: 'prime_mover_code'

idx_cols_gen_entity = [
    'plant_id_eia',
]
columns_gen_entity = idx_cols_gen_entity + ['state', 'balancing_authority_code_eia']

data_gen_entity = [
    (3, 'AL','SOCO'),
    (4, pd.NA, pd.NA,),
]

generator_entity_eia860 = pd.DataFrame(
    data_gen_entity,
    columns=columns_gen_entity
)
# Output table: generator_entity_eia860
#    Inputs: generators_eia860 + generation_eia923
#    PK columns: 'plant_id_eia', 'generator_id'
#    Harvested column: 'prime_mover_code'

idx_cols_gen_entity = [
    'plant_id_eia', 'generator_id',
]
columns_gen_entity = idx_cols_gen_entity + ['prime_mover_code', 'topping_bottoming_code']

data_gen_entity = [
    (3, '1', 'ST', 'T'),
    (3, '2', 'ST', 'B'),
    (4, 'a', pd.NA, pd.NA,),
]

generator_entity_eia860 = pd.DataFrame(
    data_gen_entity,
    columns=columns_gen_entity
)
# Output table: generators_eia860 (annual generator table)
#    Inputs: generators_eia860
#    PK cols: 'plant_id_eia', 'generator_id', 'report_date',
#    Harvested cols: 'capacity_mw'

idx_cols_gen_annual = [
    'plant_id_eia', 'generator_id', 'report_date',
]
columns_gens = idx_cols_gen_annual + ['capacity_mw']
data_gens = [
    (3, '1', '2018-01-01', 153.1),
    (3, '1', '2017-01-01', 153.1),
    (3, '2', '2018-01-01', 50),
    (3, '2', '2017-01-01', 50),
    (4, 'a', '2017-01-01', pd.NA,),
    (4, 'b', '2017-01-01', pd.NA,),
]

generators_eia860 = pd.DataFrame(
    data_gens,
    columns=columns_gens
)
# Output: utility_entity_eia table
#    Inputs: utility_ent_eia, sales_eia861, generators_eia860
#    Idx_cols: 'utility_id_eia', 
columns_util_ent = [
    'utility_id_eia', 'utility_name_eia',
]
data_util_ent = [
    (3989, 'City of Colorado Springs - (CO)'),
    (195, 'Alabama Power Co')
]

utility_ent_eia = pd.DataFrame(
    data_util_ent,
    columns=columns_util_ent
)
# Association table: grab all instances of a set of primary keys
# Note: I believe the only difference w/ these association tables is
# that we basically just want to 'harvest' the PKs

# Output: utility_assn_eia table
#    Inputs: service_territory_eia861, sales_eia861
#    PKs: 'utility_id_eia', 'report_date', 'county', 'state'
columns_util_ent = [
    'utility_id_eia', 'report_date', 'county', 'state'
]
data_util_ent = [
    (3989, '2017-01-01', 'El Paso', 'CO'),
    (3989, '2017-01-01', 'Teller', 'CO'),
    (3989, '2018-01-01', 'El Paso', 'CO'),
    (3989, '2018-01-01', 'Teller', 'CO'),
    (3989, '2018-01-01', 'Boulder', 'CO')
]

utility_entity_eia = pd.DataFrame(
    data_util_ent,
    columns=columns_util_ent
)
# Output: generation table (data table, not entity table)
#    Inputs: generation_eia923 (w/ cols removed via harvesting)
columns_gen = [ 
    'plant_id_eia', 'generator_id', 'report_date',
    'net_generation_mwh',
]
data_gen = [
    (3, '1', '2018-01-01', 10738.),
    (3, '1', '2018-02-01',  -348.),
    (3, '1', '2018-03-01',  -414.),
    (3, '1', '2018-04-01',  -411.),
    (3, '1', '2018-05-01',  pd.NA),
    (3, '1', '2018-06-01',  -607.),
    (3, '1', '2018-07-01',  5022.),
    (3, '1', '2018-08-01',  -689.),
    (3, '1', '2018-09-01',  6718.),
    (3, '1', '2018-10-01',  3877.),
    (3, '1', '2018-11-01',  pd.NA),
    (3, '1', '2018-12-01',  -494.)]

generation_eia923 = pd.DataFrame(
    data_gen,
    columns=columns_gen
)
# Output table: sales_eia861 (data table, not entity table)
#    Inputs: sales_eia861 (w/ cols removed via harvesting)
#    FK: 'utility_id_eia', 'report_date','county', 'state' 

columns_sales = [
    'utility_id_eia', 'report_date','county', 'state', 'sales'
]
data_sales = [
    (3989, '2017-01-01', '<NAME>', 'CO', 127682),
    (3989, '2017-01-01', 'Teller', 'CO', 733947),
    (3989, '2018-01-01', '<NAME>', 'CO', 87729),
    (3989, '2018-01-01', 'Teller', 'CO', 992734),
    # CO spring incursion on the bubble town
    (3989, '2018-01-01', 'Boulder', 'CO', 66666),
]

sales_eia861 = pd.DataFrame(
    data_sales,
    columns=columns_sales
)
# Output: boiler_generator_assn_eia860
#     Input: boiler_generator_assn_eia860
columns_bga = [ 
    'plant_id_eia', 'generator_id', 'report_date', 'boiler_id',
]
data_bga = [
    (3, '1', '2018-01-01', '1ST',),
    (3, '1', '2017-01-01', '1ST',),
    (3, '2', '2017-01-01', '2ST',),
    (3, '2', '2018-01-01', '2ST',),
    (4, 'a', '2017-01-01', 'a1',),
    (4, 'a', '2017-01-01', 'a2',),
    (4, 'a', '2017-01-01', 'a2',),
    (4, 'b', '2017-01-01', 'b1',)
]

boiler_generator_assn_eia860 = pd.DataFrame(
    data_bga,
    columns=columns_bga
)<jupyter_output><empty_output>