# -*- coding: utf-8 -*- """ This file takes collated scenario results and creates additional tables of summary results (additional to the ones in 'Results_tables.py') used in reporting... labelled 'working' in the file name because it is the file that is amended as needed if new tables are requested or no longer needed otherwise the basis is the same as for Results_tables.py @author: theot """ import numpy as np import pandas as pd output_fn="C:/Users/theot/Dropbox (Sense.)/2018-19 EA TPM CBA (Shared)/Step 3/AoB model/Output/Results/" main_fn="C:/Users/theot/Dropbox (Sense.)/2018-19 EA TPM CBA (Shared)/Step 3/AoB model/Output/" tables_fn="C:/Users/theot/Dropbox (Sense.)/2018-19 EA TPM CBA (Shared)/Step 3/AoB model/Output/Results/Report tables/" r=1.06 aob_yr=2022 scenarios = ['All', 'All_major_capex', 'Demand', 'Demand_and_DG_investment', 'Demand_and_gen_investment', 'All_major_capex_alternative', 'WUNI', 'No_AoB_on_existing', 'Demand_no_aob_on_existing', 'All_major_capex_tiwai_off', 'Demand_major_capex', 'MWh_Demand_major_capex', 'All_major_capex_2024', 'All_major_capex_gen_benefits', 'All_Major_Capex_30_70'] #Sensitivities sens = ['All_major_capex_demand_sensitivities','All_major_capex_DG_sensitivities', 'All_major_capex_gen_sensitivities','All_major_capex_gen_sensitivities_ex_DG'] df=pd.read_csv(output_fn+'load_results.csv') cv = pd.read_csv(output_fn+'welfare.csv') cs = pd.read_csv(output_fn+'welfare_cs.csv') costs = pd.read_csv(output_fn+'transmission_costs.csv') rev = pd.read_csv(output_fn+'revenue.csv') plant = pd.read_csv(output_fn+'plant_investment.csv') gen = pd.read_csv(output_fn+'gen_results.csv') poss = pd.read_csv("C:/Users/theot/Dropbox (Sense.)/2018-19 EA TPM CBA (Shared)/Step 3/AoB model/Data/possible_gen.csv") dg = pd.read_csv(output_fn+'dg.csv') #Add backbone node order and info bb_info=pd.read_csv(output_fn+'bb_info.csv') df=df.merge(bb_info) cv=cv.merge(bb_info) cs=cs.merge(bb_info) plant = plant.merge(bb_info) gen = gen.merge(bb_info) # Metrics for allocating non AOB model costs and benefits df_metrics = df[['scenario','policy','type','m_yr','island','bb_num','bb','pk_rev','dg_rev','sh_rev','off_rev', 'pk_pt']].copy() bb_res_info=pd.read_csv(output_fn+'bb_res_info.csv') df_metrics=df_metrics.merge(bb_res_info) df_metrics['pk_rev_res'] = df_metrics.pk_rev*df_metrics.pk_res df_metrics['dg_rev_res'] = df_metrics.dg_rev*df_metrics.pk_res df_metrics['sh_rev_res'] = df_metrics.sh_rev*df_metrics.sh_res df_metrics['off_rev_res'] = df_metrics.off_rev*df_metrics.off_res df_metrics['pk_rev_nonres'] = df_metrics.pk_rev*(1-df_metrics.pk_res) df_metrics['dg_rev_nonres'] = df_metrics.dg_rev*(1-df_metrics.pk_res) df_metrics['sh_rev_nonres'] = df_metrics.sh_rev*(1-df_metrics.sh_res) df_metrics['off_rev_nonres'] = df_metrics.off_rev*(1-df_metrics.off_res) df_metrics['pk_rev_res_pv'] = df_metrics.pk_rev_res/(r**(df_metrics.m_yr-2019)) df_metrics['dg_rev_res_pv'] = df_metrics.dg_rev_res/(r**(df_metrics.m_yr-2019)) df_metrics['sh_rev_res_pv'] = df_metrics.sh_rev_res/(r**(df_metrics.m_yr-2019)) df_metrics['off_rev_res_pv'] = df_metrics.off_rev_res/(r**(df_metrics.m_yr-2019)) df_metrics['pk_rev_nonres_pv'] = df_metrics.pk_rev_nonres/(r**(df_metrics.m_yr-2019)) df_metrics['dg_rev_nonres_pv'] = df_metrics.dg_rev_nonres/(r**(df_metrics.m_yr-2019)) df_metrics['sh_rev_nonres_pv'] = df_metrics.sh_rev_nonres/(r**(df_metrics.m_yr-2019)) df_metrics['off_rev_nonres_pv'] = df_metrics.off_rev_nonres/(r**(df_metrics.m_yr-2019)) df_metrics['pk_rev_pv'] = df_metrics.pk_rev/(r**(df_metrics.m_yr-2019)) df_metrics['dg_rev_pv'] = df_metrics.dg_rev/(r**(df_metrics.m_yr-2019)) df_metrics['sh_rev_pv'] = df_metrics.sh_rev/(r**(df_metrics.m_yr-2019)) df_metrics['off_rev_pv'] = df_metrics.off_rev/(r**(df_metrics.m_yr-2019)) df_metrics['rev_pv'] = df_metrics.pk_rev_pv + df_metrics.dg_rev_pv + df_metrics.sh_rev_pv + df_metrics.off_rev_pv df_metrics['rev_res_pv'] = df_metrics.pk_rev_res_pv + df_metrics.dg_rev_res_pv + df_metrics.sh_rev_res_pv + df_metrics.off_rev_res_pv df_metrics['rev_nonres_pv'] = df_metrics.pk_rev_nonres_pv + df_metrics.dg_rev_nonres_pv + df_metrics.sh_rev_nonres_pv + df_metrics.off_rev_nonres_pv df_metrics['pk_pt_res'] = df_metrics.pk_pt*df_metrics.pk_res df_metrics['pk_pt_nonres'] = df_metrics.pk_pt - df_metrics.pk_pt_res df_metrics['pk_pt_pv'] = df_metrics.pk_pt/(r**(df_metrics.m_yr-2019)) df_metrics['pk_pt_res_pv'] = df_metrics.pk_pt_res/(r**(df_metrics.m_yr-2019)) df_metrics['pk_pt_nonres_pv'] = df_metrics.pk_pt_nonres/(r**(df_metrics.m_yr-2019)) rev_bb_table = df_metrics[df_metrics.m_yr>=2022].pivot_table(index=['scenario','island','bb_num','bb','type'], columns="policy", values=["rev_pv","rev_res_pv","rev_nonres_pv","pk_pt_pv","pk_pt_res_pv","pk_pt_nonres_pv"], aggfunc=np.sum, fill_value=0).reset_index() rev_bb_table.to_csv(output_fn+"pv_revenue_by_bb.csv") #Welfare results tables - creates tables cv_results = cv.groupby(['scenario','type','island','bb_num','bb']).agg({'npv_cv': 'sum','npv_cv_p0': 'sum'}).reset_index() cv_results['welfare change']=cv_results.npv_cv/1000000 cv_results['allocative efficiency']=cv_results.npv_cv_p0/1000000 cv_table = cv.pivot_table(index=['scenario','island','bb_num','bb'], columns="type", values=["npv_cv","npv_cv_p0"], aggfunc=np.sum, fill_value=0).reset_index() #Welfare results tables - creates tables - FOR CONSUMER SURPLUS MEASURES cs_results = cs.groupby(['scenario','type','island','bb_num','bb']).agg({'npv_cv': 'sum','npv_cv_p0': 'sum'}).reset_index() cs_results['welfare change']=cs_results.npv_cv/1000000 cs_results['allocative efficiency']=cs_results.npv_cv_p0/1000000 cs_table = cs.pivot_table(index=['scenario','island','bb_num','bb'], columns="type", values=["npv_cv","npv_cv_p0"], aggfunc=np.sum, fill_value=0).reset_index() # Revenue changes rev['rev0_load_pv']=rev.load_rcpd*(1/(r**(rev.m_yr-2019))) rev['rev1_load_pv']=rev.load_aob*(1/(r**(rev.m_yr-2019))) rev['rev0_gen_pv']=rev.gen_rcpd*(1/(r**(rev.m_yr-2019))) rev['rev1_gen_pv']=rev.gen_aob*(1/(r**(rev.m_yr-2019))) # Net benefits cv_totals = cv.groupby(['scenario']).agg({'npv_cv': 'sum','npv_cv_p0': 'sum'}).reset_index() cost_totals = costs.groupby(['scenario']).agg({'pv_net_cost': 'sum'}).reset_index() rev_totals = rev.groupby(['scenario']).agg({'rev0_load_pv': 'sum', 'rev1_load_pv':'sum','rev0_gen_pv':'sum', 'rev1_gen_pv':'sum'}).reset_index() dg_totals = dg.groupby(['scenario']).agg({'pv_inv_change': 'sum'}).reset_index() net_benefits = pd.merge(pd.merge(pd.merge(cv_totals,cost_totals),rev_totals),dg_totals) net_benefits['drev_load'] = net_benefits.rev1_load_pv-net_benefits.rev0_load_pv net_benefits['drev_gen'] = net_benefits.rev1_gen_pv-net_benefits.rev0_gen_pv net_benefits['pv_net']=(net_benefits.npv_cv-net_benefits.pv_net_cost)+net_benefits.drev_load-net_benefits.pv_inv_change net_benefits['pv_net_p0']=(net_benefits.npv_cv_p0-net_benefits.pv_net_cost)+net_benefits.drev_load-net_benefits.pv_inv_change net_benefits['WelfareMeasure']="CV" # Net benefits Consumer surplus measure cs_totals = cs.groupby(['scenario']).agg({'npv_cv': 'sum','npv_cv_p0': 'sum'}).reset_index() cost_totals = costs.groupby(['scenario']).agg({'pv_net_cost': 'sum'}).reset_index() rev_totals = rev.groupby(['scenario']).agg({'rev0_load_pv': 'sum', 'rev1_load_pv':'sum','rev0_gen_pv':'sum', 'rev1_gen_pv':'sum'}).reset_index() net_benefits_cs = pd.merge(pd.merge(pd.merge(cs_totals,cost_totals),rev_totals),dg_totals) net_benefits_cs['drev_load'] = net_benefits_cs.rev1_load_pv-net_benefits_cs.rev0_load_pv net_benefits_cs['drev_gen'] = net_benefits_cs.rev1_gen_pv-net_benefits_cs.rev0_gen_pv net_benefits_cs['pv_net']=(net_benefits_cs.npv_cv-net_benefits_cs.pv_net_cost)+net_benefits_cs.drev_load-net_benefits_cs.pv_inv_change net_benefits_cs['pv_net_p0']=(net_benefits_cs.npv_cv_p0-net_benefits_cs.pv_net_cost)+net_benefits_cs.drev_load-net_benefits_cs.pv_inv_change net_benefits_cs['WelfareMeasure']="CS" net_benefits_all = pd.concat([net_benefits,net_benefits_cs]) # Add transport costs and interconnection charges to plant investment gen_pt_ic0 = gen[gen.policy=='status quo'].groupby(['m_yr','scenario','island','bb_num','bb']).agg({'pk_pr': 'mean', 'sh_pr': 'mean','off_pr': 'mean','pk_pt_mu': 'mean'}).reset_index() gen_pt_ic1 = gen[gen.policy=='proposal'].groupby(['m_yr','scenario','island','bb_num','bb']).agg({'pk_pr': 'mean', 'sh_pr': 'mean','off_pr': 'mean','pk_pt_mu': 'mean'}).reset_index() gen_pt_ic0.columns = ['rcpd_yr','scenario','island','bb_num','bb', 'rcpd_peak_charge','rcpd_shoulder_charge','rcpd_offpeak_charge', 'rcpd_peak_pt'] gen_pt_ic1.columns = ['aob_yr','scenario','island','bb_num','bb', 'aob_peak_charge','aob_shoulder_charge','aob_offpeak_charge', 'aob_peak_pt'] gen_pt_ic = pd.merge(gen_pt_ic0,gen_pt_ic1,how='left',on=['scenario','island','bb_num','bb']) plant_investment = pd.merge(plant,gen_pt_ic0,how='left',on=['rcpd_yr','scenario','island','bb_num','bb']) plant_investment = pd.merge(plant_investment,gen_pt_ic1,how='left',on=['aob_yr','scenario','island','bb_num','bb']) plant_investment = pd.merge(plant_investment,poss[['name','sh_mw','off_mw']],how='left',on='name') #Decomposing results by scenario # (a) revenue paid # (b) prices # (b) volumes # Summarise df['Exp']=df.pk_e+df.dg_e+df.sh_e+df.off_e df['Q']=df.pk_q+df.dg_q+df.sh_q+df.off_q df['Rev']=df.pk_rev+df.dg_rev+df.sh_rev+df.off_rev df['LCE']=df.pk_q*df.pk_pt+df.sh_q*df.sh_pt+df.off_q*df.off_pt df['Exp_pg']=df.pk_q*df.pk_pg+df.sh_q*df.sh_pg+df.off_q*df.off_pg #Get a copy of the dataframe that is just 2022 onwards df_aob = df.loc[df['m_yr']>=2022,] all_vals = df_aob.pivot_table(index=['scenario','island','type','bb_num','bb'], columns=['policy'], values=["Exp","Rev","Q","LCE"], aggfunc=np.sum, fill_value=0).reset_index() exp = df_aob.pivot_table(index=['scenario',"type",'island','bb_num','bb'], columns=['policy'], values="Exp", aggfunc=np.sum, fill_value=0).reset_index() exp.columns=['scenario','type','island','bb_num','bb',"Exp_1","Exp_0"] rev = df_aob.pivot_table(index=['scenario','type','island','bb_num','bb'], columns=['policy'], values="Rev", aggfunc=np.sum, fill_value=0).reset_index() rev.columns=['scenario',"type",'island','bb_num','bb',"Rev_1","Rev_0"] quant = df_aob.pivot_table(index=['scenario',"type",'island','bb_num','bb'], columns=['policy'], values="Q", aggfunc=np.sum, fill_value=0).reset_index() quant.columns=['scenario',"type",'island','bb_num','bb',"Q_1","Q_0"] lce = df_aob.pivot_table(index=['scenario',"type",'island','bb_num','bb'], columns=['policy'], values="LCE", aggfunc=np.sum, fill_value=0).reset_index() lce.columns=['scenario',"type",'island','bb_num','bb',"LCE_1","LCE_0"] pg = df_aob.pivot_table(index=['scenario',"type",'island','bb_num','bb'], columns=['policy'], values="Exp_pg", aggfunc=np.sum, fill_value=0).reset_index() pg.columns=['scenario',"type",'island','bb_num','bb',"Exp_pg_1","Exp_pg_0"] out=pd.concat((exp,rev[['Rev_1','Rev_0']], quant[['Q_1','Q_0']], lce[['LCE_1','LCE_0']], pg[['Exp_pg_1','Exp_pg_0']]),axis=1) out['P_0']=out.Exp_0/out.Q_0 out['P_1']=out.Exp_1/out.Q_1 out['Pg_0']=out.Exp_pg_0/out.Q_0 out['Pg_1']=out.Exp_pg_1/out.Q_1 out['dExp'] = out.Exp_1/out.Exp_0-1 out['dRev'] = out.Rev_1/out.Rev_0-1 out['dQ'] = out.Q_1/out.Q_0-1 out['dP'] = out.P_1/out.P_0-1 out['dPg'] = out.Pg_1/out.Pg_0-1 out['dLCE'] = out.LCE_1/out.LCE_0-1 out.to_csv(output_fn+'load_summary_table.csv') # Report tables # Shares of transmission revenue by backbone node rev['Rev_0_tot']= rev['Rev_0'].groupby(rev['scenario']).transform('sum') rev['Rev_1_tot']= rev['Rev_1'].groupby(rev['scenario']).transform('sum') rev['Rev_0_shr'] = rev.Rev_0/rev.Rev_0_tot rev['Rev_1_shr'] = rev.Rev_1/rev.Rev_1_tot rev['dRev_shr'] = rev.Rev_1_shr-rev.Rev_0_shr rev_Demand = rev.loc[rev['scenario']=="Demand",].copy() rev_shares = rev.pivot_table(index=['scenario','island','bb_num','bb'], columns = ['type'], values = ["Rev_0_shr","Rev_1_shr","dRev_shr"], aggfunc=np.sum, fill_value=0).reset_index() rev_shares.columns = ['Scenario','Island','Order','Backbone node', 'Baseline, mass market','Baseline, large industrial', 'Proposal, mass market','Proposal, large industrial', 'Change, mass market','Change, large industrial'] rev_shares.loc[rev_shares['Scenario']=="Demand",].to_csv(tables_fn+"Demand_revenue_shares.csv") # Impacts on prices by backbone node ave_p = out.pivot_table(index=['scenario','island','bb_num','bb'], columns = ['type'], values = ["P_0","P_1","dP"], aggfunc=np.sum, fill_value=0).reset_index() ave_p.columns= ['Scenario','Island','Order','Backbone node', 'Baseline, mass market','Baseline, large industrial', 'Proposal, mass market','Proposal, large industrial', 'Change, mass market','Change, large industrial'] ave_p.loc[ave_p['Scenario']=="Demand",].to_csv(tables_fn+"Expenditure_per_MWh.csv") # Welfare gains - gen investment and ['Scenario','Island','Order','Backbone node', 'Compensating variation, mass market','Compensating variation, large industrial', 'Compensating variation excl energy price effect, mass market', 'Compensating variation excl energy price effect, large industrial'] cv_table.columns = ['Scenario','Island','Order','Backbone node', 'CV_MM','CV_LI','CV_MM_exP','CV_LI_exP'] cs_table.columns = ['Scenario','Island','Order','Backbone node', 'CS_MM','CS_LI','CS_MM_exP','CS_LI_exP'] welfare_table=pd.merge(cv_table,cs_table) table_x = welfare_table.loc[welfare_table['Scenario'].isin(["MWh_Demand_major_capex","Demand_major_capex"])].copy() table_y = table_x.pivot_table(index=['Island','Order','Backbone node'], columns = ['Scenario'], values = ["CV_MM","CV_LI","CS_MM","CS_LI"], aggfunc=np.sum, fill_value=0).reset_index() table_y.columns = ['Island','Order','Backbone node', 'Proposal consumer surplus change, large industrial', 'Alternative consumer surplus change, large industrial', 'Proposal consumer surplus change, mass market', 'Alternative consumer surplus change, mass market', 'Proposal compensating variation, large industrial', 'Alternative compensating variation, large industrial', 'Proposal compensating variation, mass market', 'Alternative compensating variation, mass market'] y_order = ['Island','Order','Backbone node', 'Alternative compensating variation, mass market', 'Alternative compensating variation, large industrial', 'Proposal compensating variation, mass market', 'Proposal compensating variation, large industrial', 'Alternative consumer surplus change, mass market', 'Alternative consumer surplus change, large industrial', 'Proposal consumer surplus change, mass market', 'Proposal consumer surplus change, large industrial'] table_z = table_y[y_order] table_z.to_csv(tables_fn+"Consumer_welfare_change_demand_scenario.csv") #Change in demand n_yr = 28 pk_hr = 1600/2 sh_hr = 3075/2 off_hr = 12845/2 demand_totals = df_aob.groupby(['scenario','policy']).agg({'pk_q': 'sum', 'dg_q': 'sum','sh_q': 'sum', 'off_q': 'sum'}).reset_index() demand_totals['Peak MW']=demand_totals.pk_q/(pk_hr*n_yr) demand_totals['Distributed generation peak MW']=demand_totals.dg_q/(pk_hr*n_yr) demand_totals['Shoulder MW']=demand_totals.sh_q/(sh_hr*n_yr) demand_totals['Off-peak MW']=demand_totals.off_q/(off_hr*n_yr) demand_totals['Peak MWh']=demand_totals.pk_q/(n_yr) demand_totals['Distributed generation peak MWh']=demand_totals.dg_q/(n_yr) demand_totals['Shoulder MWh']=demand_totals.sh_q/(n_yr) demand_totals['Off-peak MWh']=demand_totals.off_q/(n_yr) demand_totals = demand_totals[['scenario','policy','Peak MW', 'Distributed generation peak MW', 'Shoulder MW','Off-peak MW']] demand_totals = demand_totals.loc[demand_totals['scenario'].isin(["Demand"])].to_csv(tables_fn+"Demand_change_demand_scenario.csv") # Revenue - demand and generation scenarios gen['Exp']=gen.pk_e+gen.dg_e+gen.sh_e+gen.off_e gen['Q']=gen.pk_q+gen.dg_q+gen.sh_q+gen.off_q gen['Rev']=gen.pk_rev+gen.dg_rev+gen.sh_rev+gen.off_rev gen['LCE']=gen.pk_q*gen.pk_pt+gen.sh_q*gen.sh_pt+gen.off_q*gen.off_pt gen['Exp_pg']=gen.pk_q*gen.pk_pg+gen.sh_q*gen.sh_pg+gen.off_q*gen.off_pg gen_aob = gen.loc[gen['m_yr']>=2022,] gen_rev = gen_aob.pivot_table(index=['scenario','island','bb_num','bb'], columns=['policy'], values="Rev", aggfunc=np.sum, fill_value=0).reset_index() gen_rev.columns=['scenario','island','bb_num','bb',"Rev_1","Rev_0"] gen_rev['Ave_0']=gen_rev.Rev_0/n_yr gen_rev['Ave_1']=gen_rev.Rev_1/n_yr gen_rev['dAve'] = gen_rev.Ave_1-gen_rev.Ave_0 rev2 = gen_rev.groupby(['scenario','island','bb_num','bb']).agg({'Rev_0':'sum', 'Rev_1':'sum'}).reset_index() rev2['Ave_0']=rev2.Rev_0/n_yr rev2['Ave_1']=rev2.Rev_1/n_yr rev2['dAve'] = rev2.Ave_1-rev2.Ave_0 ave_rev = rev2[['scenario','island','bb_num','bb','Ave_0','Ave_1','dAve']] ave_rev.columns = ["Scenario","Island","Order","Backbone","Baseline","Proposal","Difference"] ave_rev.loc[ave_rev['Scenario'].isin(["Demand_and_gen_investment"])].to_csv(tables_fn+"Gen_Revenue_demand_and_gen_scenario.csv") #Welfare tables - gen investment table_x = welfare_table.loc[welfare_table['Scenario'].isin(["Demand","Demand_and_gen_investment"])].copy() table_y = table_x.pivot_table(index=['Island','Order','Backbone node'], columns = ['Scenario'], values = ["CV_MM","CV_LI","CS_MM","CS_LI"], aggfunc=np.sum, fill_value=0).reset_index() table_y.columns = ['Island','Order','Backbone node', 'Demand consumer surplus change, large industrial', 'Generation investment consumer surplus change, large industrial', 'Demand consumer surplus change, mass market', 'Generation investment consumer surplus change, mass market', 'Demand compensating variation, large industrial', 'Generation investment compensating variation, large industrial', 'Demand compensating variation, mass market', 'Generation investment compensating variation, mass market'] table_y['Difference consumer surplus change, mass market'] = ( table_y['Generation investment consumer surplus change, mass market']- table_y['Demand consumer surplus change, mass market']) table_y['Difference consumer surplus change, large industrial'] = ( table_y['Generation investment consumer surplus change, large industrial']- table_y['Demand consumer surplus change, large industrial']) table_y['Difference compensating variation, mass market'] = ( table_y['Generation investment compensating variation, mass market']- table_y['Demand compensating variation, mass market']) table_y['Difference compensating variation, large industrial'] = ( table_y['Generation investment compensating variation, large industrial']- table_y['Demand compensating variation, large industrial']) y_order = ['Island','Order','Backbone node', 'Demand compensating variation, mass market', 'Demand compensating variation, large industrial', 'Generation investment compensating variation, mass market', 'Generation investment compensating variation, large industrial', 'Difference compensating variation, mass market', 'Difference compensating variation, large industrial', 'Demand consumer surplus change, mass market', 'Demand consumer surplus change, large industrial', 'Generation investment consumer surplus change, mass market', 'Generation investment consumer surplus change, large industrial', 'Difference consumer surplus change, mass market', 'Difference consumer surplus change, large industrial'] table_z = table_y[y_order] table_z.to_csv(tables_fn+"Consumer_welfare_change_gen_investment_scenario.csv") # Major capex consumer welfare changes #Welfare tables - gen investment table_x = welfare_table.loc[welfare_table['Scenario'].isin(["All_major_capex"])].copy() table_x.columns = ['Scenario','Island','Order','Backbone node', 'Compensating variation, mass market', 'Compensating variation, large industrial', 'Compensating variation excl energy price effects, mass market', 'Compensating variation excl energy price effects, large industrial', 'Consumer surplus change, mass market', 'Consumer surplus change, large industrial', 'Consumer surplus change excl energy price effects, mass market', 'Consumer surplus change excl energy price effects, large industrial'] table_x.to_csv(tables_fn+"Consumer_welfare_change_major_capex_scenario.csv") #Welfare tables - WUNI table_x = welfare_table.loc[welfare_table['Scenario'].isin(["WUNI"])].copy() table_x.columns = ['Scenario','Island','Order','Backbone node', 'Compensating variation, mass market', 'Compensating variation, large industrial', 'Compensating variation excl energy price effects, mass market', 'Compensating variation excl energy price effects, large industrial', 'Consumer surplus change, mass market', 'Consumer surplus change, large industrial', 'Consumer surplus change excl energy price effects, mass market', 'Consumer surplus change excl energy price effects, large industrial'] table_x.to_csv(tables_fn+"Consumer_welfare_change_WUNI_scenario.csv") #Welfare tables - No aob on existing table_x = welfare_table.loc[welfare_table['Scenario'].isin(["No_AoB_on_existing"])].copy() table_x.columns = ['Scenario','Island','Order','Backbone node', 'Compensating variation, mass market', 'Compensating variation, large industrial', 'Compensating variation excl energy price effects, mass market', 'Compensating variation excl energy price effects, large industrial', 'Consumer surplus change, mass market', 'Consumer surplus change, large industrial', 'Consumer surplus change excl energy price effects, mass market', 'Consumer surplus change excl energy price effects, large industrial'] table_x.to_csv(tables_fn+"Consumer_welfare_change_no_aob_on_existing.csv") #Sensitivities #Demand pv_cs= pd.read_csv(main_fn+sens[0]+'/'+'npv_results_cs.csv').set_index(['p_e','p_e_i']) pv_cv= pd.read_csv(main_fn+sens[0]+'/'+'npv_results.csv').set_index(['p_e','p_e_i']) pv_cs_xnrg= pd.read_csv(main_fn+sens[0]+'/'+'npv_p0_results_cs.csv').set_index(['p_e','p_e_i']) pv_cv_xnrg= pd.read_csv(main_fn+sens[0]+'/'+'npv_p0_results.csv').set_index(['p_e','p_e_i']) demand_sensitivity=pd.concat([pv_cs,pv_cv,pv_cs_xnrg,pv_cv_xnrg],axis=1) demand_sensitivity.columns = ['CS','CV','CS ex energy price','CV ex energy price'] demand_sensitivity.to_csv(tables_fn+"Demand sensitivity table.csv") #DG pv_cs= pd.read_csv(main_fn+sens[1]+'/'+'npv_results_cs.csv').set_index(['dg_lrmc_g','dg_ds']) pv_cv= pd.read_csv(main_fn+sens[1]+'/'+'npv_results.csv').set_index(['dg_lrmc_g','dg_ds']) pv_cs_xnrg= pd.read_csv(main_fn+sens[1]+'/'+'npv_p0_results_cs.csv').set_index(['dg_lrmc_g','dg_ds']) pv_cv_xnrg= pd.read_csv(main_fn+sens[1]+'/'+'npv_p0_results.csv').set_index(['dg_lrmc_g','dg_ds']) DG_sensitivity=pd.concat([pv_cs,pv_cv,pv_cs_xnrg,pv_cv_xnrg],axis=1) DG_sensitivity.columns = ['CS','CV','CS ex energy price','CV ex energy price'] DG_sensitivity.to_csv(tables_fn+"DG sensitivity table.csv") #Generation pv_cs= pd.read_csv(main_fn+sens[2]+'/'+'npv_results_cs.csv').set_index(['uplift','max_inv']) pv_cv= pd.read_csv(main_fn+sens[2]+'/'+'npv_results.csv').set_index(['uplift','max_inv']) pv_cs_xnrg= pd.read_csv(main_fn+sens[2]+'/'+'npv_p0_results_cs.csv').set_index(['uplift','max_inv']) pv_cv_xnrg= pd.read_csv(main_fn+sens[2]+'/'+'npv_p0_results.csv').set_index(['uplift','max_inv']) Gen_sensitivity=pd.concat([pv_cs,pv_cv,pv_cs_xnrg,pv_cv_xnrg],axis=1) Gen_sensitivity.columns = ['CS','CV','CS ex energy price','CV ex energy price'] Gen_sensitivity.to_csv(tables_fn+"Gen sensitivity table.csv") #Generation ex DG pv_cs= pd.read_csv(main_fn+sens[3]+'/'+'npv_results_cs.csv').set_index(['uplift','max_inv']) pv_cv= pd.read_csv(main_fn+sens[3]+'/'+'npv_results.csv').set_index(['uplift','max_inv']) pv_cs_xnrg= pd.read_csv(main_fn+sens[3]+'/'+'npv_p0_results_cs.csv').set_index(['uplift','max_inv']) pv_cv_xnrg= pd.read_csv(main_fn+sens[3]+'/'+'npv_p0_results.csv').set_index(['uplift','max_inv']) Gen_exDG_sensitivity=pd.concat([pv_cs,pv_cv,pv_cs_xnrg,pv_cv_xnrg],axis=1) Gen_exDG_sensitivity.columns = ['CS','CV','CS ex energy price','CV ex energy price'] Gen_exDG_sensitivity.to_csv(tables_fn+"Gen ex DG sensitivity table.csv") #CS decomposition # New price cs['pk_p1']=cs.pk_p+cs.pk_dp cs['dg_p1']=cs.dg_p+cs.dg_dp cs['sh_p1']=cs.sh_p+cs.sh_dp cs['off_p1']=cs.off_p+cs.off_dp # New quantity cs['pk_q1']=cs.pk_q+cs.pk_dq cs['dg_q1']=cs.dg_q+cs.dg_dq cs['sh_q1']=cs.sh_q+cs.sh_dq cs['off_q1']=cs.off_q+cs.off_dq # Change in expenditure at old quantity and new transmission prices cs['pk_dTQ']= (cs.pk_q*cs.pk_dp_p0) cs['dg_dTQ']= (cs.dg_q*cs.dg_dp_p0) cs['sh_dTQ']= (cs.sh_q*cs.sh_dp_p0) cs['off_dTQ']= (cs.off_q*cs.off_dp_p0) # Change in expenditure at old quantity and new prices cs['pk_dPQ']= (cs.pk_q*cs.pk_dp) cs['dg_dPQ']= (cs.dg_q*cs.dg_dp) cs['sh_dPQ']= (cs.sh_q*cs.sh_dp) cs['off_dPQ']= (cs.off_q*cs.off_dp) # Change in Consumer surplus at new transmission prices cs['pk_dCSdT']= -1*cs.pk_dTQ-0.5*(cs.pk_dq*cs.pk_dp_p0) cs['dg_dCSdT']= -1*cs.dg_dTQ-0.5*(cs.dg_dq*cs.dg_dp_p0) cs['sh_dCSdT']= -1*cs.sh_dTQ-0.5*(cs.sh_dq*cs.sh_dp_p0) cs['off_dCSdT']= -1*cs.off_dTQ-0.5*(cs.off_dq*cs.off_dp_p0) # Change in Consumer surplus at new prices cs['pk_dCSdP']= -1*cs.pk_dPQ-0.5*(cs.pk_dq*cs.pk_dp) cs['dg_dCSdP']= -1*cs.dg_dPQ-0.5*(cs.dg_dq*cs.dg_dp) cs['sh_dCSdP']= -1*cs.sh_dPQ-0.5*(cs.sh_dq*cs.sh_dp) cs['off_dCSdP']= -1*cs.off_dPQ-0.5*(cs.off_dq*cs.off_dp) # Residential expenditure shares information bb_res_info=pd.read_csv(output_fn+'bb_res_info.csv') cs=cs.merge(bb_res_info) # Residential effects cs['exp0_res'] = (cs.pk_p*cs.pk_q*cs.pk_res+cs.dg_p*cs.dg_q*cs.dg_res+ cs.sh_p*cs.sh_q*cs.sh_res+cs.off_p*cs.off_q*cs.off_res) # Change in expenditure at old quantity and new transmission prices cs['pk_dTQ_res']= cs.pk_dTQ*cs.pk_res cs['dg_dTQ_res']= cs.dg_dTQ*cs.dg_res cs['sh_dTQ_res']= cs.sh_dTQ*cs.sh_res cs['off_dTQ_res']= cs.off_dTQ*cs.off_res cs['pk_dTQ_res_pv']= (cs.pk_dTQ*cs.pk_res)/(r**(cs.year-2019)) cs['dg_dTQ_res_pv']= (cs.dg_dTQ*cs.dg_res)/(r**(cs.year-2019)) cs['sh_dTQ_res_pv']= (cs.sh_dTQ*cs.sh_res)/(r**(cs.year-2019)) cs['off_dTQ_res_pv']= (cs.off_dTQ*cs.off_res)/(r**(cs.year-2019)) # Change in expenditure at old quantity and new prices cs['pk_dPQ_res']= cs.pk_dPQ*cs.pk_res cs['dg_dPQ_res']= cs.dg_dPQ*cs.dg_res cs['sh_dPQ_res']= cs.sh_dPQ*cs.sh_res cs['off_dPQ_res']= cs.off_dPQ*cs.off_res cs['pk_dPQ_res_pv']= (cs.pk_dPQ*cs.pk_res)/(r**(cs.year-2019)) cs['dg_dPQ_res_pv']= (cs.dg_dPQ*cs.dg_res)/(r**(cs.year-2019)) cs['sh_dPQ_res_pv']= (cs.sh_dPQ*cs.sh_res)/(r**(cs.year-2019)) cs['off_dPQ_res_pv']= (cs.off_dPQ*cs.off_res)/(r**(cs.year-2019)) # Change in Consumer surplus at new transmission prices cs['pk_dCSdT_res']= cs.pk_dCSdT*cs.pk_res cs['dg_dCSdT_res']= cs.dg_dCSdT*cs.dg_res cs['sh_dCSdT_res']= cs.sh_dCSdT*cs.sh_res cs['off_dCSdT_res']= cs.off_dCSdT*cs.off_res cs['pk_dCSdT_res_pv']= (cs.pk_dCSdT*cs.pk_res)/(r**(cs.year-2019)) cs['dg_dCSdT_res_pv']= (cs.dg_dCSdT*cs.dg_res)/(r**(cs.year-2019)) cs['sh_dCSdT_res_pv']= (cs.sh_dCSdT*cs.sh_res)/(r**(cs.year-2019)) cs['off_dCSdT_res_pv']= (cs.off_dCSdT*cs.off_res)/(r**(cs.year-2019)) # Change in Consumer surplus at new prices cs['pk_dCSdP_res']= cs.pk_dCSdP*cs.pk_res cs['dg_dCSdP_res']= cs.dg_dCSdP*cs.dg_res cs['sh_dCSdP_res']= cs.sh_dCSdP*cs.sh_res cs['off_dCSdP_res']= cs.off_dCSdP*cs.off_res cs['pk_dCSdP_res_pv']= (cs.pk_dCSdP*cs.pk_res)/(r**(cs.year-2019)) cs['dg_dCSdP_res_pv']= (cs.dg_dCSdP*cs.dg_res)/(r**(cs.year-2019)) cs['sh_dCSdP_res_pv']= (cs.sh_dCSdP*cs.sh_res)/(r**(cs.year-2019)) cs['off_dCSdP_res_pv']= (cs.off_dCSdP*cs.off_res)/(r**(cs.year-2019)) #Non residential cs['exp0_nonres'] = cs.exp0-cs.exp0_res # Change in expenditure at old quantity and new transmission prices cs['pk_dTQ_nonres']= cs.pk_dTQ*(1-cs.pk_res) cs['dg_dTQ_nonres']= cs.dg_dTQ*(1-cs.dg_res) cs['sh_dTQ_nonres']= cs.sh_dTQ*(1-cs.sh_res) cs['off_dTQ_nonres']= cs.off_dTQ*(1-cs.off_res) cs['pk_dTQ_nonres_pv']= (cs.pk_dTQ*(1-cs.pk_res))/(r**(cs.year-2019)) cs['dg_dTQ_nonres_pv']= (cs.dg_dTQ*(1-cs.dg_res))/(r**(cs.year-2019)) cs['sh_dTQ_nonres_pv']= (cs.sh_dTQ*(1-cs.sh_res))/(r**(cs.year-2019)) cs['off_dTQ_nonres_pv']= (cs.off_dTQ*(1-cs.off_res))/(r**(cs.year-2019)) # Change in expenditure at old quantity and new prices cs['pk_dPQ_nonres']= cs.pk_dPQ*(1-cs.pk_res) cs['dg_dPQ_nonres']= cs.dg_dPQ*(1-cs.dg_res) cs['sh_dPQ_nonres']= cs.sh_dPQ*(1-cs.sh_res) cs['off_dPQ_nonres']= cs.off_dPQ*(1-cs.off_res) cs['pk_dPQ_nonres_pv']= (cs.pk_dPQ*(1-cs.pk_res))/(r**(cs.year-2019)) cs['dg_dPQ_nonres_pv']= (cs.dg_dPQ*(1-cs.dg_res))/(r**(cs.year-2019)) cs['sh_dPQ_nonres_pv']= (cs.sh_dPQ*(1-cs.sh_res))/(r**(cs.year-2019)) cs['off_dPQ_nonres_pv']= (cs.off_dPQ*(1-cs.off_res))/(r**(cs.year-2019)) # Change in Consumer surplus at new transmission prices cs['pk_dCSdT_nonres']= cs.pk_dCSdT*(1-cs.pk_res) cs['dg_dCSdT_nonres']= cs.dg_dCSdT*(1-cs.dg_res) cs['sh_dCSdT_nonres']= cs.sh_dCSdT*(1-cs.sh_res) cs['off_dCSdT_nonres']= cs.off_dCSdT*(1-cs.off_res) cs['pk_dCSdT_nonres_pv']= (cs.pk_dCSdT*(1-cs.pk_res))/(r**(cs.year-2019)) cs['dg_dCSdT_nonres_pv']= (cs.dg_dCSdT*(1-cs.dg_res))/(r**(cs.year-2019)) cs['sh_dCSdT_nonres_pv']= (cs.sh_dCSdT*(1-cs.sh_res))/(r**(cs.year-2019)) cs['off_dCSdT_nonres_pv']= (cs.off_dCSdT*(1-cs.off_res))/(r**(cs.year-2019)) # Change in Consumer surplus at new prices cs['pk_dCSdP_nonres']= cs.pk_dCSdP*(1-cs.pk_res) cs['dg_dCSdP_nonres']= cs.dg_dCSdP*(1-cs.dg_res) cs['sh_dCSdP_nonres']= cs.sh_dCSdP*(1-cs.sh_res) cs['off_dCSdP_nonres']= cs.off_dCSdP*(1-cs.off_res) cs['pk_dCSdP_nonres_pv']= (cs.pk_dCSdP*(1-cs.pk_res))/(r**(cs.year-2019)) cs['dg_dCSdP_nonres_pv']= (cs.dg_dCSdP*(1-cs.dg_res))/(r**(cs.year-2019)) cs['sh_dCSdP_nonres_pv']= (cs.sh_dCSdP*(1-cs.sh_res))/(r**(cs.year-2019)) cs['off_dCSdP_nonres_pv']= (cs.off_dCSdP*(1-cs.off_res))/(r**(cs.year-2019)) # Change in total expenditure at old quantity and new transmission prices cs['dTQ'] = cs.pk_dTQ + cs.dg_dTQ + cs.sh_dTQ + cs.off_dTQ cs['dTQ_res'] = cs.pk_dTQ_res + cs.dg_dTQ_res + cs.sh_dTQ_res + cs.off_dTQ_res cs['dTQ_nonres'] = cs.dTQ-cs.dTQ_res cs['dTQ_pv'] = cs.dTQ/(r**(cs.year-2019)) cs['dTQ_res_pv'] = cs.dTQ_res/(r**(cs.year-2019)) cs['dTQ_nonres_pv'] = cs.dTQ_nonres/(r**(cs.year-2019)) # Change in total expenditure at old quantity and new prices cs['dPQ'] = cs.pk_dPQ + cs.dg_dPQ + cs.sh_dPQ + cs.off_dPQ cs['dPQ_res'] = cs.pk_dPQ_res + cs.dg_dPQ_res + cs.sh_dPQ_res + cs.off_dPQ_res cs['dPQ_nonres'] = cs.dPQ-cs.dPQ_res cs['dPQ_pv'] = cs.dPQ/(r**(cs.year-2019)) cs['dPQ_res_pv'] = cs.dPQ_res/(r**(cs.year-2019)) cs['dPQ_nonres_pv'] = cs.dPQ_nonres/(r**(cs.year-2019)) # Change in total Consumer Surplus at new transmission prices cs['dCSdT'] = cs.pk_dCSdT + cs.dg_dCSdT + cs.sh_dCSdT + cs.off_dCSdT cs['dCSdT_res'] = cs.pk_dCSdT_res + cs.dg_dCSdT_res + cs.sh_dCSdT_res + cs.off_dCSdT_res cs['dCSdT_nonres'] = cs.dCSdT-cs.dCSdT_res cs['dCSdT_pv'] = cs.dCSdT/(r**(cs.year-2019)) cs['dCSdT_res_pv'] = cs.dCSdT_res/(r**(cs.year-2019)) cs['dCSdT_nonres_pv'] = cs.dCSdT_nonres/(r**(cs.year-2019)) # Change in total Consumer Surplus at new prices cs['dCSdP'] = cs.pk_dCSdP + cs.dg_dCSdP + cs.sh_dCSdP + cs.off_dCSdP cs['dCSdP_res'] = cs.pk_dCSdP_res + cs.dg_dCSdP_res + cs.sh_dCSdP_res + cs.off_dCSdP_res cs['dCSdP_nonres'] = cs.dCSdP-cs.dCSdP_res cs['dCSdP_pv'] = cs.dCSdP/(r**(cs.year-2019)) cs['dCSdP_res_pv'] = cs.dCSdP_res/(r**(cs.year-2019)) cs['dCSdP_nonres_pv'] = cs.dCSdP_nonres/(r**(cs.year-2019)) # Change in total expenditure at old quantity and new transmission prices, % of expenditure cs['dTQ_pcnt'] = cs.dTQ/cs.exp0 cs['dTQ_res_pcnt'] = cs.dTQ_res/cs.exp0_res cs['dTQ_nonres_pcnt'] = cs.dTQ_nonres/cs.exp0_nonres # Change in total expenditure at old quantity and new prices, % of expenditure cs['dPQ_pcnt'] = cs.dPQ/cs.exp0 cs['dPQ_res_pcnt'] = cs.dPQ_res/cs.exp0_res cs['dPQ_nonres_pcnt'] = cs.dPQ_nonres/cs.exp0_nonres # Change in Consumer surplus at new transmission prices, % of expenditure cs['dCSdT_pcnt'] = cs.dCSdT/cs.exp0 cs['dCSdT_res_pcnt'] = cs.dCSdT_res/cs.exp0_res cs['dCSdT_nonres_pcnt'] = cs.dCSdT_nonres/cs.exp0_nonres # Change in Consumer surplus at new prices, % of expenditure cs['dCSdP_pcnt'] = cs.dCSdP/cs.exp0 cs['dCSdP_res_pcnt'] = cs.dCSdP_res/cs.exp0_res cs['dCSdP_nonres_pcnt'] = cs.dCSdP_nonres/cs.exp0_nonres # Present values cs['exp0_pv'] = cs.exp0/(r**(cs.year-2019)) cs['exp0_res_pv'] = cs.exp0_res/(r**(cs.year-2019)) cs['exp0_nonres_pv'] = cs.exp0_nonres/(r**(cs.year-2019)) cs['exp0_pv'] = cs.exp0/(r**(cs.year-2019)) cs['exp0_res_pv'] = cs.exp0_res/(r**(cs.year-2019)) cs['exp0_nonres_pv'] = cs.exp0_nonres/(r**(cs.year-2019)) cs_results = cs.groupby(['scenario','type','island','bb_num','bb']).agg({'npv_cv': 'sum','npv_cv_p0': 'sum'}).reset_index() cs_results['welfare change']=cs_results.npv_cv/1000000 cs_results['allocative efficiency']=cs_results.npv_cv_p0/1000000 cs_dist = cs.groupby(['scenario','type','island','bb_num','bb'])[["exp0_pv","exp0_res_pv","exp0_nonres_pv", "dTQ_pv","dTQ_res_pv","dTQ_nonres_pv", "dPQ_pv","dPQ_res_pv","dPQ_nonres_pv", "dCSdT_pv","dCSdT_res_pv","dCSdT_nonres_pv", "dCSdP_pv","dCSdP_res_pv","dCSdP_nonres_pv", "pk_dTQ_res_pv","dg_dTQ_res_pv","sh_dTQ_res_pv","off_dTQ_res_pv", "pk_dCSdT_res_pv","dg_dCSdT_res_pv","sh_dCSdT_res_pv","off_dCSdT_res_pv", "pk_dTQ_nonres_pv","dg_dTQ_nonres_pv","sh_dTQ_nonres_pv","off_dTQ_nonres_pv", "pk_dCSdT_nonres_pv","dg_dCSdT_nonres_pv","sh_dCSdT_nonres_pv","off_dCSdT_nonres_pv"]].sum().reset_index() cs_dist = cs.pivot_table(index=['scenario','island','bb_num','bb'], columns="type", values=["exp0_pv","exp0_res_pv","exp0_nonres_pv", "dTQ_pv","dTQ_res_pv","dTQ_nonres_pv", "dPQ_pv","dPQ_res_pv","dPQ_nonres_pv", "dCSdT_pv","dCSdT_res_pv","dCSdT_nonres_pv", "dCSdP_pv","dCSdP_res_pv","dCSdP_nonres_pv", "pk_dTQ_res_pv","dg_dTQ_res_pv","sh_dTQ_res_pv","off_dTQ_res_pv", "pk_dCSdT_res_pv","dg_dCSdT_res_pv","sh_dCSdT_res_pv","off_dCSdT_res_pv", "pk_dTQ_nonres_pv","dg_dTQ_nonres_pv","sh_dTQ_nonres_pv","off_dTQ_nonres_pv", "pk_dCSdT_nonres_pv","dg_dCSdT_nonres_pv","sh_dCSdT_nonres_pv","off_dCSdT_nonres_pv"], aggfunc=np.sum, fill_value=0).reset_index() column_order = ["exp0_pv","exp0_res_pv","exp0_nonres_pv", "dTQ_pv","dTQ_res_pv","dTQ_nonres_pv", "dPQ_pv","dPQ_res_pv","dPQ_nonres_pv", "dCSdT_pv","dCSdT_res_pv","dCSdT_nonres_pv", "dCSdP_pv","dCSdP_res_pv","dCSdP_nonres_pv", "pk_dTQ_res_pv","dg_dTQ_res_pv","sh_dTQ_res_pv","off_dTQ_res_pv", "pk_dCSdT_res_pv","dg_dCSdT_res_pv","sh_dCSdT_res_pv","off_dCSdT_res_pv", "pk_dTQ_nonres_pv","dg_dTQ_nonres_pv","sh_dTQ_nonres_pv","off_dTQ_nonres_pv", "pk_dCSdT_nonres_pv","dg_dCSdT_nonres_pv","sh_dCSdT_nonres_pv","off_dCSdT_nonres_pv"] cs_dist.to_csv(tables_fn+"distributional_effects_summary.csv")