sourced.py

# %%
import pandas as pd

import pickle

# Define the file path for the pickle file
pickle_file_path = 'model_data.pkl'

# Function to save dictionaries to a pickle file
def save_to_pickle(sets_dict, params_dict):
    with open(pickle_file_path, 'wb') as file:
        pickle.dump({'sets': sets_dict, 'params': params_dict}, file)

# Function to load dictionaries from a pickle file
def load_from_pickle():
    with open(pickle_file_path, 'rb') as file:
        data = pickle.load(file)
    return data['sets'], data['params']



def load_data_from_excel(url_excel,load_from_pickle_flag = False, write_to_pickle_flag = True):


    if load_from_pickle_flag:
    # Load dictionaries from the pickle file
        loaded_sets_dict, loaded_params_dict = load_from_pickle()
        return loaded_sets_dict, loaded_params_dict

    # Timesteps
    df_excel = pd.read_excel(url_excel, sheet_name='Timesteps_All', header=None)
    t = pd.Index(df_excel.iloc[:, 0], name='t')

    # Technologies
    df_excel = pd.read_excel(url_excel, sheet_name='Technologies')
    i = pd.Index(df_excel.iloc[:, 0], name='i')




    df_excel = pd.read_excel(url_excel, sheet_name='Technologies')
    iConv = pd.Index(df_excel.iloc[0:7, 2], name='iConv')

    df_excel = pd.read_excel(url_excel, sheet_name='Technologies')
    iRes = pd.Index(df_excel.iloc[0:4, 4], name='iRes')

    df_excel = pd.read_excel(url_excel, sheet_name='Technologies')
    iSto = pd.Index(df_excel.iloc[0:2, 6], name='iSto')

    df_excel = pd.read_excel(url_excel, sheet_name='Technologies')
    iPtG = pd.Index(df_excel.iloc[0:1, 8], name='iPtG')


    df_excel = pd.read_excel(url_excel, sheet_name='Technologies')
    iHyRes = pd.Index(df_excel.iloc[0:1, 10], name='iHyRes')

    # Parameters
    l_co2 =  pd.read_excel(url_excel, sheet_name='CO2_Cap').iloc[0,0]
    p_co2 = 0
    dt = 1


    # Demand
    df_excel= pd.read_excel(url_excel, sheet_name = 'Demand')
    #df_melt = pd.melt(df_excel, id_vars='Zeit')
    df_excel = df_excel.rename(columns = {'Timesteps':'t', 'Unnamed: 1':'Demand'})
    #df_excel  = i.to_frame().reset_index(drop=True).merge(df_excel, how = 'left')
    df_excel = df_excel.fillna(0)
    df_excel = df_excel.set_index('t')
    D_t = df_excel.iloc[:,0].to_xarray()
    ## Efficiencies
    df_excel = pd.read_excel(url_excel, sheet_name = 'Efficiency')
    df_excel = df_excel.rename(columns = {'All':'i', 'Unnamed: 1':'Efficiency'})
    df_excel  = i.to_frame().reset_index(drop=True).merge(df_excel, how = 'left')
    df_excel = df_excel.fillna(0)
    df_excel = df_excel.set_index('i')
    eff_i = df_excel.iloc[:,0].to_xarray()

    ## Variable costs
    # Fuel costs
    df_excel = pd.read_excel(url_excel, sheet_name = 'FuelCosts')
    df_excel = df_excel.rename(columns = {'Conventionals':'i', 'Unnamed: 1':'FuelCosts'})
    df_excel  = i.to_frame().reset_index(drop=True).merge(df_excel, how = 'left')
    df_excel = df_excel.fillna(0)
    df_excel = df_excel.set_index('i')
    c_fuel_i = df_excel.iloc[:,0].to_xarray()
    # Apply slider value
    #c_fuel_i.loc[dict(i = 'Fossil Gas')]  = price_gas
    #c_fuel_i.loc[dict(i = 'H2')]  = price_h2

    # Other var. costs
    df_excel = pd.read_excel(url_excel, sheet_name = 'OtherVarCosts')
    df_excel = df_excel.rename(columns = {'Conventionals':'i', 'Unnamed: 1':'OtherVarCosts'})
    df_excel  = i.to_frame().reset_index(drop=True).merge(df_excel, how = 'left')
    df_excel = df_excel.fillna(0)
    df_excel = df_excel.set_index('i')
    c_other_i = df_excel.iloc[:,0].to_xarray()

    # Investment costs
    df_excel = pd.read_excel(url_excel, sheet_name = 'InvCosts')
    df_excel = df_excel.rename(columns = {'All':'i', 'Unnamed: 1':'InvCosts'})
    df_excel  = i.to_frame().reset_index(drop=True).merge(df_excel, how = 'left')
    df_excel = df_excel.fillna(0)
    df_excel = df_excel.set_index('i')
    c_inv_i = df_excel.iloc[:,0].to_xarray()*1000*0.1 # kw to MW and annuity factor

    # Emission factor
    df_excel = pd.read_excel(url_excel, sheet_name = 'EmFactor')
    df_excel = df_excel.rename(columns = {'Conventionals':'i', 'Unnamed: 1':'EmFactor'})
    df_excel  = i.to_frame().reset_index(drop=True).merge(df_excel, how = 'left')
    df_excel = df_excel.fillna(0)
    df_excel = df_excel.set_index('i')
    co2_factor_i = df_excel.iloc[:,0].to_xarray()

    ## Calculation of variable costs
    c_var_i = (c_fuel_i.sel(i = iConv) + p_co2 * co2_factor_i.sel(i = iConv)) / eff_i.sel(i = iConv) + c_other_i.sel(i = iConv)

    # RES capacity factors
    #df_excel = pd.read_excel(url_excel, sheet_name = 'RES',header=[0,1])
    #df_excel = pd.read_excel(url_excel, sheet_name = 'RES', index_col=['Timesteps'], columns=['PV', 'WindOn', 'WindOff', 'RoR'])
    df_excel = pd.read_excel(url_excel, sheet_name = 'RES')
    df_excel = df_excel.set_index(['Timesteps'])
    df_test = df_excel
    df_excel = df_excel.stack()
    #df_excel = df_excel.rename(columns={'PV', 'WindOn', 'WindOff', 'RoR'})
    df_test2 = df_excel
    #df_excel = i.to_frame().reset_index(drop=True).merge(df_excel, how = 'left')
    #df_excel = df_excel.fillna(0)

    #df_test = df_excel.set_index(['Timesteps', 'PV', 'WindOn', 'WindOff', 'RoR']).stack([0])
    #df_test.index = df_test.index.set_names(['t','i'])
    s_t_r_iRes = df_excel.to_xarray().rename({'level_1': 'i','Timesteps':'t'})

    #s_t_r_iRes = df_excel.iloc[:,0].to_xarray()

    # Base capacities
    df_excel = pd.read_excel(url_excel, sheet_name = 'InstalledCap')
    df_excel = df_excel.rename(columns = {'All':'i', 'Unnamed: 1':'InstalledCap'})
    df_excel  = i.to_frame().reset_index(drop=True).merge(df_excel, how = 'left')
    df_excel = df_excel.fillna(0)
    df_excel = df_excel.set_index('i')
    K_0_i = df_excel.iloc[:,0].to_xarray()

    # Energy-to-power ratio storages
    df_excel = pd.read_excel(url_excel, sheet_name = 'E2P')  
    df_excel = df_excel.rename(columns = {'Storage':'i', 'Unnamed: 1':'E2P ratio'})
    #df_excel  = i.to_frame().reset_index(drop=True).merge(df_excel, how = 'left')
    df_excel = df_excel.fillna(0)
    df_excel = df_excel.set_index('i')
    e2p_iSto = df_excel.iloc[:,0].to_xarray()

    # Inflow for hydro reservoir
    df_excel = pd.read_excel(url_excel, sheet_name = 'HydroInflow')
    df_excel = df_excel.rename(columns = {'Timesteps':'t', 'Hydro Water Reservoir':'Inflow'})
    df_excel = df_excel.fillna(0)
    df_excel = df_excel.set_index('t')
    h_t = df_excel.iloc[:,0].to_xarray()


    
    sets_dict = {}
    params_dict = {}
    # Append parameters to the dictionary
    sets_dict['t'] = t
    sets_dict['i'] = i
    sets_dict['iSto'] = iSto
    sets_dict['iConv'] = iConv
    sets_dict['iPtG'] = iPtG
    sets_dict['iRes'] = iRes
    sets_dict['iHyRes'] = iHyRes
    # Append parameters to the dictionary
    params_dict['l_co2'] = l_co2
    params_dict['p_co2'] = p_co2
    params_dict['dt'] = dt
    params_dict['D_t'] = D_t
    params_dict['eff_i'] = eff_i
    params_dict['c_fuel_i'] = c_fuel_i
    params_dict['c_other_i'] = c_other_i
    params_dict['c_inv_i'] = c_inv_i
    params_dict['co2_factor_i'] = co2_factor_i
    params_dict['c_var_i'] = c_var_i
    params_dict['s_t_r_iRes'] = s_t_r_iRes
    params_dict['K_0_i'] = K_0_i
    params_dict['e2p_iSto'] = e2p_iSto
    params_dict['h_t'] = h_t

    if write_to_pickle_flag:
        save_to_pickle(sets_dict, params_dict)

    return sets_dict, params_dict


# %%
# # Example usage:
# url_excel = "Input_Jahr_2021.xlsx"  # Replace with your actual file path
# limit_co2 = 0.5
# price_co2 = 50
# price_gas = 3
# price_h2 = 5

# sets, params = load_data_from_excel(url_excel,write_to_pickle_flag=True)

# # %%
# sets, params = load_data_from_excel(url_excel,load_from_pickle_flag=True)
# # %%