def populate_dudetailsummary(engine=SQLITE):
df = mmsds_reader.download(dataset="dudetailsummary", y=2019, m=2)
cols = [
'DUID', 'REGIONID', 'STATIONID', 'PARTICIPANTID', 'CONNECTIONPOINTID',
'DISPATCHTYPE', 'SCHEDULE_TYPE', 'STARTTYPE', 'TRANSMISSIONLOSSFACTOR',
'DISTRIBUTIONLOSSFACTOR', 'MIN_RAMP_RATE_UP', 'MIN_RAMP_RATE_DOWN',
'MAX_RAMP_RATE_UP', 'MAX_RAMP_RATE_DOWN', 'IS_AGGREGATED',
'START_DATE', 'END_DATE', 'LASTCHANGED'
]
for col in ['START_DATE', 'END_DATE', 'LASTCHANGED']:
df[col] = df[col].apply(lambda x: date_parse(x))
for id_table, column in {
"STATION": "STATIONID",
"REGION": "REGIONID",
"PARTICIPANT": "PARTICIPANTID",
"CONNECTIONPOINT": "CONNECTIONPOINTID",
"DISPATCHTYPE": "DISPATCHTYPE",
"SCHEDULE_TYPE": "SCHEDULE_TYPE",
"DU": "DUID"
}.items():
id_key_map = key_mapper(id_table, column)
df[column] = df[column].apply(lambda x: id_key_map[x])
id_key_map = key_mapper("STARTTYPE", "STARTTYPE")
df['STARTTYPE'] = df['STARTTYPE'].apply(lambda x: nan_parse(id_key_map, x))
df[cols].to_sql("DUDETAILSUMMARY",
con=engine,
index=False,
if_exists='append')
def populate_participants(engine=SQLITE):
key_map = key_mapper("PARTICIPANTCLASS", engine=engine)
df = mmsds_reader.download(dataset="participant", y=2019, m=2)
df.PARTICIPANTCLASSID = df.PARTICIPANTCLASSID.apply(lambda x: key_map[x])
dx = df[['PARTICIPANTID', 'PARTICIPANTCLASSID', 'NAME']]
dx.to_sql("PARTICIPANT", con=engine, index=False, if_exists='append')
def populate_connection_points(engine=SQLITE):
df = mmsds_reader.download(dataset="dudetail")
df_conn = df.CONNECTIONPOINTID.copy().drop_duplicates()
df_conn.sort_values(inplace=True)
df_conn.to_sql("CONNECTIONPOINT",
con=engine,
index=False,
if_exists='append')
def populate_operating_status(engine=SQLITE):
df_os = mmsds_reader.download(dataset='operatingstatus')
station_key_map = key_mapper("STATION", "STATIONID")
df_os['STATIONID'] = df_os.STATIONID.apply(lambda x: station_key_map[x])
status_key_map = key_mapper("STATUS", "STATUS")
df_os['STATUS'] = df_os['STATUS'].apply(lambda x: status_key_map[x])
df_os[['STATIONID', 'STATUS',
'EFFECTIVEDATE']].to_sql("STATIONOPERATINGSTATUS",
con=engine,
if_exists='append',
index=None)
def populate_genset_table(engine=SQLITE):
# could be added to genunit create
df_g = mmsds_reader.download(dataset="genunits", y=2019, m=2)
#all duids
duid_key_map = key_mapper("FULL_REGISTER", "DUID", engine=legacy)
df_g['ID'] = df_g.GENSETID.apply(lambda x: duid_parse(duid_key_map, x))
genunitid = df_g[['ID', 'GENSETID']]
da = genunitid[genunitid.ID.notna()]
da.to_sql("GENSET", con=engine, if_exists='append', index=None)
#new duids
db = genunitid[genunitid.ID.isna()]
db.to_sql("GENSET", con=engine, if_exists='append', index=None)
def populate_duid_table(engine=SQLITE):
df_ds = mmsds_reader.download(dataset="dudetailsummary", y=2019, m=2)
#all duids
duid_key_map = key_mapper("FULL_REGISTER", "DUID", engine=legacy)
df_ds['ID'] = df_ds.DUID.apply(lambda x: duid_parse(duid_key_map, x))
unique_duids = df_ds[['ID', 'DUID']].drop_duplicates()
#duids with legacy ids
da = unique_duids[unique_duids.ID.notna()]
da.to_sql("DU", con=engine, if_exists='append', index=None)
#new duids
db = unique_duids[unique_duids.ID.isna()]
db.to_sql("DU", con=engine, if_exists='append', index=None)
def populate_genunits(engine=SQLITE):
df = mmsds_reader.download(dataset="genunits", y=2019, m=2)
cols = [
'GENSETID', 'STATIONID', 'CDINDICATOR', 'AGCFLAG', 'SPINNINGFLAG',
'VOLTLEVEL', 'REGISTEREDCAPACITY', 'STARTTYPE', 'MKTGENERATORIND',
'NORMALSTATUS', 'MAXCAPACITY', 'GENSETTYPE', 'LASTCHANGED',
'CO2E_EMISSIONS_FACTOR', 'CO2E_ENERGY_SOURCE', 'CO2E_DATA_SOURCE'
]
df = df[~df.GENSETID.isin([
"GE01", "GE03", "GK03", "GE04", "GE02", "GK04", "GH01", "GH02", "GK01",
"GK02"
])]
gen_unit_map(df)
for col in ['LASTCHANGED']:
df[col] = df[col].apply(lambda x: date_parse(x))
for col in [
'CDINDICATOR', 'AGCFLAG', 'SPINNINGFLAG', 'MKTGENERATORIND',
'NORMALSTATUS'
]:
df[col] = df[col].apply(lambda x: True if x == "Y" else False)
id_key_map = key_mapper("DISPATCHTYPE", "DISPATCHTYPE")
df["GENSETTYPE"] = df["GENSETTYPE"].apply(lambda x: id_key_map[x])
id_key_map = key_mapper("GENSET", "GENSETID")
df["GENSETID"] = df["GENSETID"].apply(lambda x: id_key_map[x])
id_key_map = key_mapper("STATION", "STATIONID")
df["STATIONID"] = df["STATIONID"].apply(lambda x: id_key_map[x])
for id_table, column in {
"CO2E_ENERGY_SOURCE": "CO2E_ENERGY_SOURCE",
"CO2E_DATA_SOURCE": "CO2E_DATA_SOURCE",
"STARTTYPE": "STARTTYPE"
}.items():
id_key_map = key_mapper(id_table, column)
df[column] = df[column].apply(lambda x: nan_parse(id_key_map, x))
df[cols].to_sql("GENUNITS", con=engine, index=False, if_exists='append')
def populate_stations(engine=SQLITE):
state_keys = pd.read_sql(
"SELECT STATE as _KEY, ID FROM STATE UNION SELECT STATENAME as _KEY, ID FROM STATE",
con=engine,
index_col="_KEY")
key_map = state_keys.to_dict(orient='dict')['ID']
df = mmsds_reader.download(dataset="station", y=2019, m=2)
df.STATE = df.STATE.apply(lambda x: key_map[x])
for string in ['COOMA', 'BRISBANE', 'ADELAIDE', 'PORTLAND']:
df.loc[df.POSTCODE == string, "POSTCODE"] = pd.np.nan
dx = df[['STATIONID', 'STATIONNAME', 'STATE', 'POSTCODE']].copy()
dx['STATIONNAME'] = dx['STATIONNAME'].apply(str.strip)
dx['DISPLAYNAME'] = df['STATIONNAME'].apply(display_names.display_names)
dx.sort_values("STATIONID", inplace=True)
df_latlon = load_latlon()
df_comb = dx.merge(df_latlon, on="STATIONID", how="left")
df_comb.to_sql("STATION", con=engine, index=False, if_exists='append')
