# -*- coding: utf-8 -*- """ This file takes collated scenario results and creates tables of summary results @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/" 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'] 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) #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() cv_results.to_csv(output_fn+'welfare_summary_all_scenarios.csv') cv_table.to_csv(output_fn+'welfare_table.csv') #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() cs_results.to_csv(output_fn+'welfare_summary_all_scenarios_cs.csv') cs_table.to_csv(output_fn+'welfare_table_cs.csv') # 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.to_csv(output_fn+'net_benefits.csv') # 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_cs.to_csv(output_fn+'net_benefits_cs.csv') net_benefits_all = pd.concat([net_benefits,net_benefits_cs]) net_benefits_all.to_csv(output_fn+'net_benefits_all.csv') # 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') #plant_investment=plant_investment.rename(columns = {'pk_pr_x':'rcpd_peak_charge', # 'pk_pt_mu_x':'rcpd_peak_pt', # 'pk_pr_y':'aob_peak_charge', # 'pk_pt_mu_y':'aob_peak_pt', # 'sh_pr_x':'rcpd_shoulder_charge', # 'off_pr_x':'rcpd_offpeak_charge', # 'sh_pr_y':'aob_shoulder_charge', # 'off_pr_y':'aob_offpeak_charge'}) # Add output values plant_investment.to_csv(output_fn+'plant_investment_table.csv') #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')