# %% 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) # # %%