-
Notifications
You must be signed in to change notification settings - Fork 1
/
test.py
315 lines (304 loc) · 14.3 KB
/
test.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
from run import CET_Scenario
from models import EDCS_Query_Results
from login import user
import pandas as pd
sample_data_directory = '/home/rhansen/src/python/sqlserver/PythonCET/UserInputTables/'
acc_source_directory = '/home/rhansen/src/python/sqlserver/PythonCET/E3Tables/'
'''
cet_scenario = CET_Scenario(
user = {'id':'','passwd':''},
inputs_source='csv',
input_measures_source_name=sample_data_directory + 'InputMeasureCEDARS.csv',
input_programs_source_name=sample_data_directory + 'InputProgramCEDARS.csv',
acc_source='csv',
acc_source_directory=sample_data_directory,
acc_version=2018,
first_year=2018,
market_effects_benefits=0.05,
market_effects_costs=0.05,
parallelize=True,
match_sql=True
)
cet_scenario = CET_Scenario(
user = user,
inputs_source='csv',
input_measures_source_name=sample_data_directory + 'InputMeasureCEDARS.csv',
input_programs_source_name=sample_data_directory + 'InputProgramCEDARS.csv',
acc_source='database',
acc_source_directory=sample_data_directory,
acc_version=2018,
first_year=2018,
market_effects_benefits=0.05,
market_effects_costs=0.05,
parallelize=True,
match_sql=True
)
'''
cet_scenario = CET_Scenario(
user=user,
inputs_source='database',
input_measures_source_name='InputMeasureCEDARS',
input_programs_source_name='InputProgramCEDARS',
acc_source='database',
acc_version=2018,
first_year=2018,
market_effects_benefits=0.05,
market_effects_costs=0.05,
parallelize=False,
match_sql=True
)
'''
cet_scenario = CET_Scenario(
user=user,
inputs_source='database',
input_measures_source_name='InputMeasureCEDARS',
input_programs_source_name='InputProgramCEDARS',
acc_source='database',
acc_version=2018,
first_year=2018,
market_effects_benefits=0.05,
market_effects_costs=0.05,
parallelize=True,
match_sql=True
)
'''
cet_scenario.run_cet()
py_cet_output = cet_scenario.OutputMeasures.data
sql_cet_output = EDCS_Query_Results('''
SELECT *
FROM OutputCE AS A
LEFT JOIN (
SELECT
CET_ID AS CET_ID_2,
JobID,
NetElecCO2,
NetGasCO2,
GrossElecCO2,
GrossGasCO2,
NetElecCO2Lifecycle,
NetGasCO2Lifecycle,
GrossElecCO2Lifecycle,
GrossGasCO2Lifecycle,
NetElecNOx,
NetGasNOx,
GrossElecNOx,
GrossGasNOx,
NetElecNOxLifecycle,
NetGasNOxLifecycle,
GrossElecNOxLifecycle,
GrossGasNOxLifecycle,
NetPM10,
GrossPM10,
NetPM10Lifecycle,
GrossPM10Lifecycle
FROM OutputEmissions
) AS B
ON A.CET_ID=B.CET_ID_2
AND A.JobID=B.JobID
ORDER BY
A.CET_ID''',
user['id'],
user['passwd']
).data
comparison = sql_cet_output[[
'CET_ID',
'ElecBen',
'GasBen',
'TRCCost',
'TRCCostNoAdmin',
'TRCRatio',
'TRCRatioNoAdmin',
'PACCost',
'PACCostNoAdmin',
'PACRatio',
'PACRatioNoAdmin',
'BillReducElec',
'BillReducGas',
'RIMCost',
'NetElecCO2',
'NetGasCO2',
'NetElecCO2Lifecycle',
'NetGasCO2Lifecycle',
'NetElecNOx',
'NetGasNOx',
'NetElecNOxLifecycle',
'NetGasNOxLifecycle',
'NetPM10',
'NetPM10Lifecycle',
]].merge(
py_cet_output[[
'CET_ID',
'ElectricBenefitsNet',
'GasBenefitsNet',
'TotalResourceCostNet',
'TotalResourceCostNetNoAdmin',
'TotalResourceCostRatio',
'TotalResourceCostRatioNoAdmin',
'ProgramAdministratorCost',
'ProgramAdministratorCostNoAdmin',
'ProgramAdministratorCostRatio',
'ProgramAdministratorCostRatioNoAdmin',
'BillReductionElectric',
'BillReductionGas',
'RatepayerImpactMeasureCost',
'CO2NetElectricFirstYear',
'CO2NetGasFirstYear',
'CO2NetElectricLifecycle',
'CO2NetGasLifecycle',
'NOxNetElectricFirstYear',
'NOxNetGasFirstYear',
'NOxNetElectricLifecycle',
'NOxNetGasLifecycle',
'PM10NetFirstYear',
'PM10NetLifecycle',
]], how='left', on='CET_ID', suffixes=('_sql','_py')
)
column_name_map = [
['ElecBen','ElecBen_SQL'],
['GasBen','GasBen_SQL'],
['TRCCost','TRCCost_SQL'],
['TRCCostNoAdmin','TRCCostNoAdmin_SQL'],
['TRCRatio','TRCRatio_SQL'],
['TRCRatioNoAdmin','TRCRatioNoAdmin_SQL'],
['PACCost','PACCost_SQL'],
['PACCostNoAdmin','PACCostNoAdmin_SQL'],
['PACRatio','PACRatio_SQL'],
['PACRatioNoAdmin','PACRatioNoAdmin_SQL'],
['BillReducElec','BillReducElec_SQL'],
['BillReducGas','BillReducGas_SQL'],
['RIMCost','RIMCost_SQL'],
['NetElecCO2','CO2NetElectricFirstYear_SQL'],
['NetGasCO2','CO2NetGasFirstYear_SQL'],
['NetElecCO2Lifecycle','CO2NetElectricLifecycle_SQL'],
['NetGasCO2Lifecycle','CO2NetGasLifecycle_SQL'],
['NetElecNOx','NOxNetElectricFirstYear_SQL'],
['NetGasNOx','NOxNetGasFirstYear_SQL'],
['NetElecNOxLifecycle','NOxNetElectricLifecycle_SQL'],
['NetGasNOxLifecycle','NOxNetGasLifecycle_SQL'],
['NetPM10','PM10NetFirstYear_SQL'],
['NetPM10Lifecycle','PM10NetLifecycle_SQL'],
['ElectricBenefitsNet','ElecBen_PY'],
['GasBenefitsNet','GasBen_PY'],
['TotalResourceCostNet','TRCCost_PY'],
['TotalResourceCostNetNoAdmin','TRCCostNoAdmin_PY'],
['TotalResourceCostRatio','TRCRatio_PY'],
['TotalResourceCostRatioNoAdmin','TRCRatioNoAdmin_PY'],
['ProgramAdministratorCost','PACCost_PY'],
['ProgramAdministratorCostNoAdmin','PACCostNoAdmin_PY'],
['ProgramAdministratorCostRatio','PACRatio_PY'],
['ProgramAdministratorCostRatioNoAdmin','PACRatioNoAdmin_PY'],
['BillReductionElectric','BillReducElec_PY'],
['BillReductionGas','BillReducGas_PY'],
['RatepayerImpactMeasureCost','RIMCost_PY'],
['CO2NetElectricFirstYear','CO2NetElectricFirstYear_PY'],
['CO2NetGasFirstYear','CO2NetGasFirstYear_PY'],
['CO2NetElectricLifecycle','CO2NetElectricLifecycle_PY'],
['CO2NetGasLifecycle','CO2NetGasLifecycle_PY'],
['NOxNetElectricFirstYear','NOxNetElectricFirstYear_PY'],
['NOxNetGasFirstYear','NOxNetGasFirstYear_PY'],
['NOxNetElectricLifecycle','NOxNetElectricLifecycle_PY'],
['NOxNetGasLifecycle','NOxNetGasLifecycle_PY'],
['PM10NetFirstYear','PM10NetFirstYear_PY'],
['PM10NetLifecycle','PM10NetLifecycle_PY'],
]
for old_column,new_column in column_name_map:
comparison = comparison.rename(columns={old_column:new_column},index={})
comparison = pd.DataFrame({
'CET_ID' : comparison.CET_ID,
'ElecBen_SQL': comparison.ElecBen_SQL,
'ElecBen_PY' : comparison.ElecBen_PY,
'ElecBen_Diff' : comparison.ElecBen_PY - comparison.ElecBen_SQL,
'ElecBen_PercentDiff' : (comparison.ElecBen_PY - comparison.ElecBen_SQL) / comparison.ElecBen_SQL,
'GasBen_SQL' : comparison.GasBen_SQL,
'GasBen_PY' : comparison.GasBen_PY,
'GasBen_Diff' : comparison.GasBen_PY - comparison.GasBen_SQL,
'GasBen_PercentDiff' : (comparison.GasBen_PY - comparison.GasBen_SQL) / comparison.GasBen_SQL,
'TRCCost_SQL' : comparison.TRCCost_SQL,
'TRCCost_PY' : comparison.TRCCost_PY,
'TRCCost_Diff' : comparison.TRCCost_PY - comparison.TRCCost_SQL,
'TRCCost_PercentDiff' : (comparison.TRCCost_PY - comparison.TRCCost_SQL) / comparison.TRCCost_SQL,
'TRCCostNoAdmin_SQL' : comparison.TRCCostNoAdmin_SQL,
'TRCCostNoAdmin_PY' : comparison.TRCCostNoAdmin_PY,
'TRCCostNoAdmin_Diff' : comparison.TRCCostNoAdmin_PY - comparison.TRCCostNoAdmin_SQL,
'TRCCostNoAdmin_PercentDiff' : (comparison.TRCCostNoAdmin_PY - comparison.TRCCostNoAdmin_SQL) / comparison.TRCCostNoAdmin_SQL,
'TRCRatio_SQL' : comparison.TRCRatio_SQL,
'TRCRatio_PY' : comparison.TRCRatio_PY,
'TRCRatio_Diff' : comparison.TRCRatio_PY - comparison.TRCRatio_SQL,
'TRCRatio_PercentDiff' : (comparison.TRCRatio_PY - comparison.TRCRatio_SQL) / comparison.TRCRatio_SQL,
'TRCRatioNoAdmin_SQL' : comparison.TRCRatioNoAdmin_SQL,
'TRCRatioNoAdmin_PY' : comparison.TRCRatioNoAdmin_PY,
'TRCRatioNoAdmin_Diff' : comparison.TRCRatioNoAdmin_PY - comparison.TRCRatioNoAdmin_SQL,
'TRCRatioNoAdmin_PercentDiff' : (comparison.TRCRatioNoAdmin_PY - comparison.TRCRatioNoAdmin_SQL) / comparison.TRCRatioNoAdmin_SQL,
'PACCost_SQL' : comparison.PACCost_SQL,
'PACCost_PY' : comparison.PACCost_PY,
'PACCost_Diff' : comparison.PACCost_PY - comparison.PACCost_SQL,
'PACCost_PercentDiff' : (comparison.PACCost_PY - comparison.PACCost_SQL) / comparison.PACCost_SQL,
'PACCostNoAdmin_SQL' : comparison.PACCostNoAdmin_SQL,
'PACCostNoAdmin_PY' : comparison.PACCostNoAdmin_PY,
'PACCostNoAdmin_Diff' : comparison.PACCostNoAdmin_PY - comparison.PACCostNoAdmin_SQL,
'PACCostNoAdmin_PercentDiff' : (comparison.PACCostNoAdmin_PY - comparison.PACCostNoAdmin_SQL) / comparison.PACCostNoAdmin_SQL,
'PACRatio_SQL' : comparison.PACRatio_SQL,
'PACRatio_PY' : comparison.PACRatio_PY,
'PACRatio_Diff' : comparison.PACRatio_PY - comparison.PACRatio_SQL,
'PACRatio_PercentDiff' : (comparison.PACRatio_PY - comparison.PACRatio_SQL) / comparison.PACRatio_SQL,
'PACRatioNoAdmin_SQL' : comparison.PACRatioNoAdmin_SQL,
'PACRatioNoAdmin_PY' : comparison.PACRatioNoAdmin_PY,
'PACRatioNoAdmin_Diff' : comparison.PACRatioNoAdmin_PY - comparison.PACRatioNoAdmin_SQL,
'PACRatioNoAdmin_PercentDiff' : (comparison.PACRatioNoAdmin_PY - comparison.PACRatioNoAdmin_SQL) / comparison.PACRatioNoAdmin_SQL,
'BillReducElec_SQL' : comparison.BillReducElec_SQL,
'BillReducElec_PY' : comparison.BillReducElec_PY,
'BillReducElec_Diff' : comparison.BillReducElec_PY - comparison.BillReducElec_SQL,
'BillReducElec_PercentDiff' : (comparison.BillReducElec_PY - comparison.BillReducElec_SQL) / comparison.BillReducElec_SQL,
'BillReducGas_SQL' : comparison.BillReducGas_SQL,
'BillReducGas_PY' : comparison.BillReducGas_PY,
'BillReducGas_Diff' : comparison.BillReducGas_PY - comparison.BillReducGas_SQL,
'BillReducGas_PercentDiff' : (comparison.BillReducGas_PY - comparison.BillReducGas_SQL) / comparison.BillReducGas_SQL,
'RIMCost_SQL' : comparison.RIMCost_SQL,
'RIMCost_PY' : comparison.RIMCost_PY,
'RIMCost_Diff' : comparison.RIMCost_PY - comparison.RIMCost_SQL,
'RIMCost_PercentDiff' : (comparison.RIMCost_PY - comparison.RIMCost_SQL) / comparison.RIMCost_SQL,
'CO2NetElectricFirstYear_SQL' : comparison.CO2NetElectricFirstYear_SQL,
'CO2NetElectricFirstYear_PY' : comparison.CO2NetElectricFirstYear_PY,
'CO2NetElectricFirstYear_Diff' : comparison.CO2NetElectricFirstYear_PY - comparison.CO2NetElectricFirstYear_SQL,
'CO2NetElectricFirstYear_PercentDiff' : (comparison.CO2NetElectricFirstYear_PY - comparison.CO2NetElectricFirstYear_SQL) / comparison.CO2NetElectricFirstYear_SQL,
'CO2NetGasFirstYear_SQL': comparison.CO2NetGasFirstYear_SQL,
'CO2NetGasFirstYear_PY': comparison.CO2NetGasFirstYear_PY,
'CO2NetGasFirstYear_Diff' : comparison.CO2NetGasFirstYear_PY - comparison.CO2NetGasFirstYear_SQL,
'CO2NetGasFirstYear_PercentDiff' : (comparison.CO2NetGasFirstYear_PY - comparison.CO2NetGasFirstYear_SQL) / comparison.CO2NetGasFirstYear_SQL,
'CO2NetElectricLifecycle_SQL': comparison.CO2NetElectricLifecycle_SQL,
'CO2NetElectricLifecycle_PY': comparison.CO2NetElectricLifecycle_PY,
'CO2NetElectricLifecycle_Diff' : comparison.CO2NetElectricLifecycle_PY - comparison.CO2NetElectricLifecycle_SQL,
'CO2NetElectricLifecycle_PercentDiff' : (comparison.CO2NetElectricLifecycle_PY - comparison.CO2NetElectricLifecycle_SQL) / comparison.CO2NetElectricLifecycle_SQL,
'CO2NetGasLifecycle_SQL': comparison.CO2NetGasLifecycle_SQL,
'CO2NetGasLifecycle_PY': comparison.CO2NetGasLifecycle_PY,
'CO2NetGasLifecycle_Diff' : comparison.CO2NetGasLifecycle_PY - comparison.CO2NetGasLifecycle_SQL,
'CO2NetGasLifecycle_PercentDiff' : (comparison.CO2NetGasLifecycle_PY - comparison.CO2NetGasLifecycle_SQL) / comparison.CO2NetGasLifecycle_SQL,
'NOxNetElectricFirstYear_SQL': comparison.NOxNetElectricFirstYear_SQL,
'NOxNetElectricFirstYear_PY': comparison.NOxNetElectricFirstYear_PY,
'NOxNetElectricFirstYear_Diff' : comparison.NOxNetElectricFirstYear_PY - comparison.NOxNetElectricFirstYear_SQL,
'NOxNetElectricFirstYear_PercentDiff' : (comparison.NOxNetElectricFirstYear_PY - comparison.NOxNetElectricFirstYear_SQL) / comparison.NOxNetElectricFirstYear_SQL,
'NOxNetGasFirstYear_SQL': comparison.NOxNetGasFirstYear_SQL,
'NOxNetGasFirstYear_PY': comparison.NOxNetGasFirstYear_PY,
'NOxNetGasFirstYear_Diff' : comparison.NOxNetGasFirstYear_PY - comparison.NOxNetGasFirstYear_SQL,
'NOxNetGasFirstYear_PercentDiff' : (comparison.NOxNetGasFirstYear_PY - comparison.NOxNetGasFirstYear_SQL) / comparison.NOxNetGasFirstYear_SQL,
'NOxNetElectricLifecycle_SQL': comparison.NOxNetElectricLifecycle_SQL,
'NOxNetElectricLifecycle_PY': comparison.NOxNetElectricLifecycle_PY,
'NOxNetElectricLifecycle_Diff' : comparison.NOxNetElectricLifecycle_PY - comparison.NOxNetElectricLifecycle_SQL,
'NOxNetElectricLifecycle_PercentDiff' : (comparison.NOxNetElectricLifecycle_PY - comparison.NOxNetElectricLifecycle_SQL) / comparison.NOxNetElectricLifecycle_SQL,
'NOxNetGasLifecycle_SQL': comparison.NOxNetGasLifecycle_SQL,
'NOxNetGasLifecycle_PY': comparison.NOxNetGasLifecycle_PY,
'NOxNetGasLifecycle_Diff' : comparison.NOxNetGasLifecycle_PY - comparison.NOxNetGasLifecycle_SQL,
'NOxNetGasLifecycle_PercentDiff' : (comparison.NOxNetGasLifecycle_PY - comparison.NOxNetGasLifecycle_SQL) / comparison.NOxNetGasLifecycle_SQL,
'PM10NetFirstYear_SQL': comparison.PM10NetFirstYear_SQL,
'PM10NetFirstYear_PY': comparison.PM10NetFirstYear_PY,
'PM10NetFirstYear_Diff' : comparison.PM10NetFirstYear_PY - comparison.PM10NetFirstYear_SQL,
'PM10NetFirstYear_PercentDiff' : (comparison.PM10NetFirstYear_PY - comparison.PM10NetFirstYear_SQL) / comparison.PM10NetFirstYear_SQL,
'PM10NetLifecycle_SQL': comparison.PM10NetLifecycle_SQL,
'PM10NetLifecycle_PY': comparison.PM10NetLifecycle_PY,
'PM10NetLifecycle_Diff' : comparison.PM10NetLifecycle_PY - comparison.PM10NetLifecycle_SQL,
'PM10NetLifecycle_PercentDiff' : (comparison.PM10NetLifecycle_PY - comparison.PM10NetLifecycle_SQL) / comparison.PM10NetLifecycle_SQL,
})
comparison.to_csv(sample_data_directory+'Comparison.csv',index=False)
#cet_scenario.InputMeasures.data.to_csv(sample_data_directory+'InputMeasures.csv')
#cet_scenario.InputPrograms.data.to_csv(sample_data_directory+'InputPrograms.csv')
#cet_scenario.Settings.data.to_csv(sample_data_directory+'Settings.csv')