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, write_to_pickle_flag = True):



    # 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:6, 2], name='iConv')       # changed to 6 from 7

    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:1, 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 = {'Zeitschritte':'t', 'Unnamed: 1':'Nachfrage'})
    #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 = {'Alle':'i', 'Unnamed: 1':'Wirkungsgrad'})
    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()

     ## Lifespan
    df_excel = pd.read_excel(url_excel, sheet_name = 'Lifespan')
    df_excel = df_excel.rename(columns = {'Alle':'i', 'Unnamed: 1':'Nutzungsdauer'})
    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')
    life_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 = {'Konventionell':'i', 'Unnamed: 1':'Brennstoffkosten'})
    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 = {'Konventionell':'i', 'Unnamed: 1':'Sonstige Variable Kosten'})
    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 = {'Alle':'i', 'Unnamed: 1':'Investitionen'})
    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')
    interest_rate = 0.07
    annuity_factor_i = (interest_rate * (1 + interest_rate)**life_i) / ((1 + interest_rate)**life_i - 1)
    c_inv_i = df_excel.iloc[:,0].to_xarray()*1000*annuity_factor_i

    # Emission factor
    df_excel = pd.read_excel(url_excel, sheet_name = 'EmFactor')
    df_excel = df_excel.rename(columns = {'Konventionell':'i', 'Unnamed: 1':'CO2-Faktor'})
    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(['Zeitschritte'])
    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','Zeitschritte':'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 = {'Alle':'i', 'Unnamed: 1':'Installierte Kapazität'})
    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 = {'Speicher':'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 = {'Zeitschritte':'t', 'Staudamm':'Zufluss'})
    # 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['life_i'] = life_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)
# # %%


if __name__ == "__main__":
        url_excel = r'Input_Jahr_2021.xlsx'
        sets_dict, params_dict= load_data_from_excel(url_excel, write_to_pickle_flag= False)