# author = tsherwoo

#!/usr/bin/env python
# (c) 2015 US EPA
# 5/8/15$

import pandas as pd
import os

scriptversion = 20161010.1
py_version = pd.DataFrame(pd.Series(['Script Version']))
py_version['Value'] = scriptversion

# read the TechCosts_Inputs file and name it 'inputsFile' for the script
inputsFile = r'OMEGA_TechCost_Inputs.xlsx'
outputFileID = input('Enter an output filename designation (e.g., a date stamp &/or a name for the output file)')
writeFile = pd.ExcelWriter('OMEGA_TechCosts_' + outputFileID + '.xlsx')

# read specific inputs
print("Reading inputs")
inputsTab = pd.read_excel(inputsFile, 'Inputs')
inputs = pd.DataFrame(inputsTab, columns = ['Item', 'Value'])
inputs = inputs.set_index('Item')
markup = inputs.ix['Markup']['Value']
rpe = inputs.ix['RPE']['Value']
yrStart = inputs.ix['Output_start_year']['Value']
yrEnd = inputs.ix['Output_end_year']['Value']
yrEnd = yrEnd + 1

learningFactors = pd.read_excel(inputsFile, 'lfTable')
icm_o = pd.read_excel(inputsFile, 'ICM_o')
icm_w = pd.read_excel(inputsFile, 'ICM_w')

# read the dmc tabs, then create 3 DataFrames for each with only the needed columns & add a column named cost_type, set value of cost_type to DMC/IC/TC
print("Creating and indexing necessary DataFrames")
et_DMC = pd.read_excel(inputsFile, 'et_dmc', skiprows = 9)
wr_DMC = pd.read_excel(inputsFile, 'wr_dmc', skiprows = 9)
ev1_DMC = pd.read_excel(inputsFile, 'ev1_dmc', skiprows = 9)
ev2_DMC = pd.read_excel(inputsFile, 'ev2_dmc', skiprows = 9)
aeroCurve_DMC = pd.read_excel(inputsFile, 'aero_curve', skiprows = 9)

# ensure that techs are in order of tech_#
et_DMC = et_DMC.sort_values(by=['Tech_#', 'key'], ascending=[True, True])
wr_DMC = wr_DMC.sort_values(by=['Tech_#', 'key'], ascending=[True, True])
ev1_DMC = ev1_DMC.sort_values(by=['Tech_#', 'key'], ascending=[True, True])
ev2_DMC = ev2_DMC.sort_values(by=['Tech_#', 'key'], ascending=[True, True])
aeroCurve_DMC = aeroCurve_DMC.sort_values(by=['Tech_#', 'key'], ascending=[True, True])

# and reindex
et_DMC.index = range(0, len(et_DMC))
wr_DMC.index = range(0, len(wr_DMC))
ev1_DMC.index = range(0, len(ev1_DMC))
ev2_DMC.index = range(0, len(ev2_DMC))
aeroCurve_DMC.index = range(0, len(aeroCurve_DMC))

# create dataframes with columns needed for outputs
df_et_DMC = pd.DataFrame(et_DMC, columns = ['Tech_#', 'Tech', 'key', 'CWC/VehType', 'LF_code', 'ST_Thru', 'Complexity', 'dmc_base', '[Reserved]', 'cost_type'])
df_et_IC = pd.DataFrame(et_DMC, columns = ['Tech_#', 'Tech', 'key', 'CWC/VehType', 'LF_code', 'ST_Thru', 'Complexity', 'dmc_base', '[Reserved]', 'cost_type'])
df_et_TC = pd.DataFrame(et_DMC, columns = ['Tech_#', 'Tech', 'key', 'CWC/VehType', 'LF_code', 'ST_Thru', 'Complexity', 'dmc_base', '[Reserved]', 'cost_type'])

df_wr_DMC = pd.DataFrame(wr_DMC, columns = ['Tech_#', 'Tech', 'key', 'CWC/VehType', 'LF_code', 'ST_Thru', 'Complexity', 'dmc_WRbase_pVeh', '[Reserved]', 'cost_type'])
df_wr_IC = pd.DataFrame(wr_DMC, columns = ['Tech_#', 'Tech', 'key', 'CWC/VehType', 'LF_code', 'ST_Thru', 'Complexity', 'dmc_WRbase_pVeh', '[Reserved]', 'cost_type'])
df_wr_TC = pd.DataFrame(wr_DMC, columns = ['Tech_#', 'Tech', 'key', 'CWC/VehType', 'LF_code', 'ST_Thru', 'Complexity', 'dmc_WRbase_pVeh', '[Reserved]', 'cost_type'])

df_ev1_DMC = pd.DataFrame(ev1_DMC, columns = ['Tech_#', 'Tech', 'key', 'CWC/VehType', 'LF_code', 'ST_Thru', 'Complexity', 'dmc_base_EV', 'WR_net', 'cost_type'])
df_ev1_IC = pd.DataFrame(ev1_DMC, columns = ['Tech_#', 'Tech', 'key', 'CWC/VehType', 'LF_code', 'ST_Thru', 'Complexity', 'dmc_base_EV', 'WR_net', 'cost_type'])
df_ev1_TC = pd.DataFrame(ev1_DMC, columns = ['Tech_#', 'Tech', 'key', 'CWC/VehType', 'LF_code', 'ST_Thru', 'Complexity', 'dmc_base_EV', 'WR_net', 'cost_type'])

df_ev2_DMC = pd.DataFrame(ev2_DMC, columns = ['Tech_#', 'Tech', 'key', 'CWC/VehType', 'LF_code', 'ST_Thru', 'Complexity', 'dmc_base_EV', 'WR_net', 'cost_type'])
df_ev2_IC = pd.DataFrame(ev2_DMC, columns = ['Tech_#', 'Tech', 'key', 'CWC/VehType', 'LF_code', 'ST_Thru', 'Complexity', 'dmc_base_EV', 'WR_net', 'cost_type'])
df_ev2_TC = pd.DataFrame(ev2_DMC, columns = ['Tech_#', 'Tech', 'key', 'CWC/VehType', 'LF_code', 'ST_Thru', 'Complexity', 'dmc_base_EV', 'WR_net', 'cost_type'])

df_aeroCurve_DMC = pd.DataFrame(aeroCurve_DMC, columns = ['Tech_#', 'Tech', 'key', 'CWC/VehType', 'LF_code', 'ST_Thru', 'Complexity', 'dmc_base', '[Reserved]', 'cost_type'])
df_aeroCurve_IC = pd.DataFrame(aeroCurve_DMC, columns = ['Tech_#', 'Tech', 'key', 'CWC/VehType', 'LF_code', 'ST_Thru', 'Complexity', 'dmc_base', '[Reserved]', 'cost_type'])
df_aeroCurve_TC = pd.DataFrame(aeroCurve_DMC, columns = ['Tech_#', 'Tech', 'key', 'CWC/VehType', 'LF_code', 'ST_Thru', 'Complexity', 'dmc_base', '[Reserved]', 'cost_type'])

df_ev_DMC = df_ev1_DMC.append(df_ev2_DMC)
df_ev_IC = df_ev1_IC.append(df_ev2_IC)
df_ev_TC = df_ev1_TC.append(df_ev2_TC)
df_ev_DMC = df_ev_DMC.sort_values(by=['Tech_#', 'key'], ascending=[True, True])
df_ev_IC = df_ev_IC.sort_values(by=['Tech_#', 'key'], ascending=[True, True])
df_ev_TC = df_ev_TC.sort_values(by=['Tech_#', 'key'], ascending=[True, True])
df_ev_DMC.index = range(0, len(df_ev_DMC))
df_ev_IC.index = range(0, len(df_ev_IC))
df_ev_TC.index = range(0, len(df_ev_TC))

df_et_DMC['cost_type'] = 'DMC'
df_et_IC['cost_type'] = 'IC'
df_et_TC['cost_type'] = 'TC'

df_wr_DMC['cost_type'] = 'DMC'
df_wr_IC['cost_type'] = 'IC'
df_wr_TC['cost_type'] = 'TC'

df_ev_DMC['cost_type'] = 'DMC'
df_ev_IC['cost_type'] = 'IC'
df_ev_TC['cost_type'] = 'TC'

df_aeroCurve_DMC['cost_type'] = 'DMC'
df_aeroCurve_IC['cost_type'] = 'IC'
df_aeroCurve_TC['cost_type'] = 'TC'

# merge lfTable & icmTables with et DMC/IC/TC DataFrames
df_et_learningFactors_s = df_et_DMC.merge(learningFactors, on = 'Tech_#')
df_et_icm_o_s = df_et_IC.merge(icm_o, on = 'Tech_#')
df_et_icm_w_s = df_et_IC.merge(icm_w, on = 'Tech_#')

# merge lfTable & icmTables with wr DMC/IC/TC DataFrames
df_wr_learningFactors_s = df_wr_DMC.merge(learningFactors, on = 'Tech_#')
df_wr_icm_o_s = df_wr_IC.merge(icm_o, on = 'Tech_#')
df_wr_icm_w_s = df_wr_IC.merge(icm_w, on = 'Tech_#')

# merge lfTable & icmTables with ev DataFrames
df_ev_learningFactors_s = df_ev_DMC.merge(learningFactors, on = 'Tech_#')
df_ev_icm_o_s = df_ev_IC.merge(icm_o, on = 'Tech_#')
df_ev_icm_w_s = df_ev_IC.merge(icm_w, on = 'Tech_#')

# merge lfTable & icmTables with aeroCurve DataFrames
df_aeroCurve_learningFactors_s = df_aeroCurve_DMC.merge(learningFactors, on = 'Tech_#')
df_aeroCurve_icm_o_s = df_aeroCurve_IC.merge(icm_o, on = 'Tech_#')
df_aeroCurve_icm_w_s = df_aeroCurve_IC.merge(icm_w, on = 'Tech_#')

# create calculation DataFrames indexed on 'key'
calc_et_DMCs = pd.DataFrame(df_et_DMC['key'])
calc_et_ICs = pd.DataFrame(df_et_IC['key'])
calc_et_TCs = pd.DataFrame(df_et_TC['key'])

calc_wr_DMCs = pd.DataFrame(df_wr_DMC['key'])
calc_wr_ICs = pd.DataFrame(df_wr_IC['key'])
calc_wr_TCs = pd.DataFrame(df_wr_TC['key'])

calc_ev_DMCs = pd.DataFrame(df_ev_DMC['key'])
calc_ev_ICs = pd.DataFrame(df_ev_IC['key'])
calc_ev_TCs = pd.DataFrame(df_ev_TC['key'])

calc_aeroCurve_DMCs = pd.DataFrame(df_aeroCurve_DMC['key'])
calc_aeroCurve_ICs = pd.DataFrame(df_aeroCurve_IC['key'])
calc_aeroCurve_TCs = pd.DataFrame(df_aeroCurve_TC['key'])

# >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
# This section consists of a loop to calculate DMCs, ICs, and TCs for Engine & Trans technologies
# >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

# create a loop to calc EngTrans DMCs/ICs/TCs
print("Calculating engine & trans DMCs/ICs/TCs")
for i in range(yrStart, yrEnd):
    yr = str(i)
    calc_et_DMCs[yr] = pd.Series(df_et_DMC['dmc_base'] * df_et_learningFactors_s[yr], name = yr)
    if markup == 'ICM':
        calc_et_ICs[yr] = pd.Series(abs(df_et_DMC['dmc_base']) * df_et_icm_o_s[yr] + abs(calc_et_DMCs[yr]) * df_et_icm_w_s[yr], name = yr)
    else:
        calc_et_ICs[yr] = pd.Series(abs(calc_et_DMCs[yr]) * (rpe -1), name = yr)
    calc_et_TCs[yr] = calc_et_DMCs[yr] + calc_et_ICs[yr]
df_et_DMC = df_et_DMC.merge(calc_et_DMCs, on = 'key')
df_et_IC = df_et_IC.merge(calc_et_ICs, on = 'key')
df_et_TC = df_et_TC.merge(calc_et_TCs, on = 'key')

# add aero1 and aero1.5 to get aero2 then append to TC DataFrame
df_aero1 = pd.DataFrame(df_et_TC[df_et_TC['Tech'] == 'Aero1'])
df_aero1_5 = pd.DataFrame(df_et_TC[df_et_TC['Tech'] == 'Aero1.5'])
df_aero1.index=range(0,1)
df_aero1_5.index=range(0,1)
df_aero2 = df_aero1.add(df_aero1_5)
df_aero2.index = range(len(df_et_TC), len(df_et_TC) + 1)
df_aero2['Tech_#'] = 178
df_aero2['Tech'] = 'Aero2'
df_aero2['key']='178_ClassNA'
df_aero2['CWC/VehType'] = 'ClassNA'
df_aero2['LF_code'] = ''
df_aero2['ST_Thru'] = ''
df_aero2['Complexity'] = ''
df_aero2['dmc_base'] = ''
df_aero2['cost_type']='TC'
df_et_TC = df_et_TC.append(df_aero2)

# >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
# This section consists of a loop to calculate DMCs, ICs, and TCs for Aero using the Aero cost curve
# >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

# create a loop to calc Aero DMCs/ICs/TCs
print("Calculating aero curve-based DMCs/ICs/TCs")
for i in range(yrStart, yrEnd):
    yr = str(i)
    calc_aeroCurve_DMCs[yr] = pd.Series(df_aeroCurve_DMC['dmc_base'] * df_aeroCurve_learningFactors_s[yr], name = yr)
    if markup == 'ICM':
        calc_aeroCurve_ICs[yr] = pd.Series(abs(df_aeroCurve_DMC['dmc_base']) * df_aeroCurve_icm_o_s[yr] + abs(calc_aeroCurve_DMCs[yr]) * df_aeroCurve_icm_w_s[yr], name = yr)
    else:
        calc_aeroCurve_ICs[yr] = pd.Series(abs(calc_aeroCurve_DMCs[yr]) * (rpe -1), name = yr)
    calc_aeroCurve_TCs[yr] = calc_aeroCurve_DMCs[yr] + calc_aeroCurve_ICs[yr]
df_aeroCurve_DMC = df_aeroCurve_DMC.merge(calc_aeroCurve_DMCs, on = 'key')
df_aeroCurve_IC = df_aeroCurve_IC.merge(calc_aeroCurve_ICs, on = 'key')
df_aeroCurve_TC = df_aeroCurve_TC.merge(calc_aeroCurve_TCs, on = 'key')

# >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
# This section consists of a loop to calculate DMCs, ICs, and TCs for weight reduction technologies using the FRM IC
# method. An alternative, new method is calculated below.
# >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

print("Calculating weight reduction DMCs/ICs/TCs")
for i in range(yrStart, yrEnd):
    yr = str(i)
    calc_wr_DMCs[yr] = pd.Series(df_wr_DMC['dmc_WRbase_pVeh'] * df_wr_learningFactors_s[yr], name = yr)
    if markup == 'ICM':
        calc_wr_ICs[yr] = pd.Series(abs(df_wr_DMC['dmc_WRbase_pVeh']) * df_wr_icm_o_s[yr] + abs(calc_wr_DMCs[yr]) * df_wr_icm_w_s[yr], name = yr)
    else:
        calc_wr_ICs[yr] = pd.Series(abs(calc_wr_DMCs[yr]) * (rpe -1), name = yr)
    calc_wr_TCs[yr] = calc_wr_DMCs[yr] + calc_wr_ICs[yr]
df_wr_DMC = df_wr_DMC.merge(calc_wr_DMCs, on = 'key')
df_wr_IC = df_wr_IC.merge(calc_wr_ICs, on = 'key')
df_wr_TC = df_wr_TC.merge(calc_wr_TCs, on = 'key')

# >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
# This section consists of a loop to calculate DMCs, ICs, and TCs for electrified vehicle technologies
# >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

print("Calculating electrified vehicle DMCs/ICs/TCs")
for i in range(yrStart, yrEnd):
    yr = str(i)
    calc_ev_DMCs[yr] = pd.Series(df_ev_DMC['dmc_base_EV'] * df_ev_learningFactors_s[yr], name = yr)
    if markup == 'ICM':
        calc_ev_ICs[yr] = pd.Series(abs(df_ev_DMC['dmc_base_EV']) * df_ev_icm_o_s[yr] + abs(calc_ev_DMCs[yr]) * df_ev_icm_w_s[yr], name = yr)
    else:
        calc_ev_ICs[yr] = pd.Series(abs(calc_ev_DMCs[yr]) * (rpe -1), name = yr)
    calc_ev_TCs[yr] = calc_ev_DMCs[yr] + calc_ev_ICs[yr]
df_ev_DMC = df_ev_DMC.merge(calc_ev_DMCs, on = 'key')
df_ev_IC = df_ev_IC.merge(calc_ev_ICs, on = 'key')
df_ev_TC = df_ev_TC.merge(calc_ev_TCs, on = 'key')



# >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
# Done with calculation loops for individual techs, the next section pulls everything together
# >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

# create DataFrames for all costs, then append DMCs/ICs/TCs into those DataFrames
df_et_ALL = df_et_DMC
df_et_ALL = df_et_ALL.append(df_et_IC)
df_et_ALL = df_et_ALL.append(df_et_TC)

df_aeroCurve_ALL = df_aeroCurve_DMC
df_aeroCurve_ALL = df_aeroCurve_ALL.append(df_aeroCurve_IC)
df_aeroCurve_ALL = df_aeroCurve_ALL.append(df_aeroCurve_TC)
df_et_ALL = df_et_ALL.append(df_aeroCurve_ALL)
df_et_ALL.fillna(0, inplace=True)

df_wr_ALL = df_wr_DMC
df_wr_ALL = df_wr_ALL.append(df_wr_IC)
df_wr_ALL = df_wr_ALL.append(df_wr_TC)

df_ev_ALL = df_ev_DMC
df_ev_ALL = df_ev_ALL.append(df_ev_IC)
df_ev_ALL = df_ev_ALL.append(df_ev_TC)

print("Calculating electrified vehicle system TCs")
# grab P2 techs and add together to form P2 system costs
df_p2battpack_TC = df_ev_TC[df_ev_TC['Tech_#'] == 248]
df_p2nonbatt_TC = df_ev_TC[df_ev_TC['Tech_#'] == 249]
df_p2battpack_TC.index = range(0, len(df_p2battpack_TC))
df_p2nonbatt_TC.index = range(0, len(df_p2nonbatt_TC))
df_p2_TC = pd.DataFrame(df_p2battpack_TC, columns = ['Tech_#', 'Tech', 'key', 'CWC/VehType', 'WR_net', 'cost_type'])
df_p2_TC['Tech_#'] = 247
df_p2_TC['Tech'] = 'P2'

for i in range(yrStart, yrEnd):
    yr = str(i)
    df_p2_TC[yr] = pd.Series(df_p2battpack_TC[yr] + df_p2nonbatt_TC[yr], name = yr)
df_p2_TC['key'] = df_p2_TC['Tech'] + '-' + df_p2_TC['WR_net'].map(str)

# grab MHEV (115V) techs and add together to form MHEV system costs
df_mhevbattpack_TC = df_ev_TC[df_ev_TC['Tech_#'] == 243]
df_mhevnonbatt_TC = df_ev_TC[df_ev_TC['Tech_#'] == 244]
df_mhevbattpack_TC.index = range(0, len(df_mhevbattpack_TC))
df_mhevnonbatt_TC.index = range(0, len(df_mhevnonbatt_TC))
df_mhev_TC = pd.DataFrame(df_mhevbattpack_TC, columns = ['Tech_#', 'Tech', 'key', 'CWC/VehType', 'WR_net', 'cost_type'])
df_mhev_TC['Tech_#'] = 242
df_mhev_TC['Tech'] = 'MHEV'

# grab MHEV48V techs and add together to form MHEV48V system costs
df_mhev48Vbattpack_TC = df_ev_TC[df_ev_TC['Tech_#'] == 325]
df_mhev48Vnonbatt_TC = df_ev_TC[df_ev_TC['Tech_#'] == 326]
df_mhev48Vbattpack_TC.index = range(0, len(df_mhev48Vbattpack_TC))
df_mhev48Vnonbatt_TC.index = range(0, len(df_mhev48Vnonbatt_TC))
df_mhev48V_TC = pd.DataFrame(df_mhev48Vbattpack_TC, columns = ['Tech_#', 'Tech', 'key', 'CWC/VehType', 'WR_net', 'cost_type'])
df_mhev48V_TC['Tech_#'] = 324
df_mhev48V_TC['Tech'] = 'MHEV48V'

for i in range(yrStart, yrEnd):
    yr = str(i)
    df_mhev_TC[yr] = pd.Series(df_mhevbattpack_TC[yr] + df_mhevnonbatt_TC[yr], name = yr)
    df_mhev48V_TC[yr] = pd.Series(df_mhev48Vbattpack_TC[yr] + df_mhev48Vnonbatt_TC[yr], name = yr)
df_mhev_TC['key'] = df_mhev_TC['Tech'] + '-' + df_mhev_TC['WR_net'].map(str)
df_mhev48V_TC['key'] = df_mhev48V_TC['Tech'] + '-' + df_mhev48V_TC['WR_net'].map(str)

# grab EV techs and add together to form EV system costs; start first with merging labor and charger costs on vClass/vType
df_ev75battpack_TC = df_ev_TC[df_ev_TC['Tech_#'] == 226]
df_ev75nonbatt_TC = df_ev_TC[df_ev_TC['Tech_#'] == 227]
df_ev100battpack_TC = df_ev_TC[df_ev_TC['Tech_#'] == 220]
df_ev100nonbatt_TC = df_ev_TC[df_ev_TC['Tech_#'] == 221]
df_ev200battpack_TC = df_ev_TC[df_ev_TC['Tech_#'] == 328]
df_ev200nonbatt_TC = df_ev_TC[df_ev_TC['Tech_#'] == 329]
df_reev20battpack_TC = df_ev_TC[df_ev_TC['Tech_#'] == 254]
df_reev20nonbatt_TC = df_ev_TC[df_ev_TC['Tech_#'] == 255]
df_reev40battpack_TC = df_ev_TC[df_ev_TC['Tech_#'] == 257]
df_reev40nonbatt_TC = df_ev_TC[df_ev_TC['Tech_#'] == 258]

df_ev75_vClass = pd.DataFrame(df_ev75battpack_TC['CWC/VehType'])
df_ev100_vClass = pd.DataFrame(df_ev100battpack_TC['CWC/VehType'])
df_ev200_vClass = pd.DataFrame(df_ev200battpack_TC['CWC/VehType'])
df_reev20_vClass = pd.DataFrame(df_reev20battpack_TC['CWC/VehType'])
df_reev40_vClass = pd.DataFrame(df_reev40battpack_TC['CWC/VehType'])

df_evCharger_TC = df_ev_TC[df_ev_TC['Tech_#'] == 187]
df_reev20Charger_TC = df_ev_TC[df_ev_TC['Tech_#'] == 189]
df_reev40Charger_TC = df_ev_TC[df_ev_TC['Tech_#'] == 190]
df_ev75charger_TC = df_ev75_vClass.merge(df_evCharger_TC, on = 'CWC/VehType')
df_ev100charger_TC = df_ev100_vClass.merge(df_evCharger_TC, on = 'CWC/VehType')
df_ev200charger_TC = df_ev200_vClass.merge(df_evCharger_TC, on = 'CWC/VehType')
df_reev20charger_TC = df_reev20_vClass.merge(df_reev20Charger_TC, on = 'CWC/VehType')
df_reev40charger_TC = df_reev40_vClass.merge(df_reev40Charger_TC, on = 'CWC/VehType')

df_evLabor_TC = df_ev_TC[df_ev_TC['Tech_#'] == 188]
df_ev75labor_TC = df_ev75_vClass.merge(df_evLabor_TC, on = 'CWC/VehType')
df_ev100labor_TC = df_ev100_vClass.merge(df_evLabor_TC, on = 'CWC/VehType')
df_ev200labor_TC = df_ev200_vClass.merge(df_evLabor_TC, on = 'CWC/VehType')
df_reev20labor_TC = df_reev20_vClass.merge(df_evLabor_TC, on = 'CWC/VehType')
df_reev40labor_TC = df_reev40_vClass.merge(df_evLabor_TC, on = 'CWC/VehType')

df_ev75battpack_TC.index = range(0, len(df_ev75battpack_TC))
df_ev75nonbatt_TC.index = range(0, len(df_ev75nonbatt_TC))
df_ev100battpack_TC.index = range(0, len(df_ev100battpack_TC))
df_ev100nonbatt_TC.index = range(0, len(df_ev100nonbatt_TC))
df_ev200battpack_TC.index = range(0, len(df_ev200battpack_TC))
df_ev200nonbatt_TC.index = range(0, len(df_ev200nonbatt_TC))
df_reev20battpack_TC.index = range(0, len(df_reev20battpack_TC))
df_reev20nonbatt_TC.index = range(0, len(df_reev20nonbatt_TC))
df_reev40battpack_TC.index = range(0, len(df_reev40battpack_TC))
df_reev40nonbatt_TC.index = range(0, len(df_reev40nonbatt_TC))

df_ev75_TC = pd.DataFrame(df_ev75battpack_TC, columns = ['Tech_#', 'Tech', 'key', 'CWC/VehType', 'WR_net', 'cost_type'])
df_ev100_TC = pd.DataFrame(df_ev100battpack_TC, columns = ['Tech_#', 'Tech', 'key', 'CWC/VehType', 'WR_net', 'cost_type'])
df_ev200_TC = pd.DataFrame(df_ev200battpack_TC, columns = ['Tech_#', 'Tech', 'key', 'CWC/VehType', 'WR_net', 'cost_type'])
df_reev20_TC = pd.DataFrame(df_reev20battpack_TC, columns = ['Tech_#', 'Tech', 'key', 'CWC/VehType', 'WR_net', 'cost_type'])
df_reev40_TC = pd.DataFrame(df_reev40battpack_TC, columns = ['Tech_#', 'Tech', 'key', 'CWC/VehType', 'WR_net', 'cost_type'])

df_ev75_TC['Tech_#'] = 225
df_ev75_TC['Tech'] = 'EV75'
df_ev100_TC['Tech_#'] = 219
df_ev100_TC['Tech'] = 'EV100'
df_ev200_TC['Tech_#'] = 327
df_ev200_TC['Tech'] = 'EV200'
df_reev20_TC['Tech_#'] = 253
df_reev20_TC['Tech'] = 'REEV20'
df_reev40_TC['Tech_#'] = 256
df_reev40_TC['Tech'] = 'REEV40'

for i in range(yrStart, yrEnd):
    yr = str(i)
    df_ev75_TC[yr] = pd.Series(df_ev75battpack_TC[yr] + df_ev75nonbatt_TC[yr] + df_ev75labor_TC[yr] + df_ev75charger_TC[yr], name = yr)
    df_ev100_TC[yr] = pd.Series(df_ev100battpack_TC[yr] + df_ev100nonbatt_TC[yr] + df_ev100labor_TC[yr] + df_ev100charger_TC[yr], name = yr)
    df_ev200_TC[yr] = pd.Series(df_ev200battpack_TC[yr] + df_ev200nonbatt_TC[yr] + df_ev200labor_TC[yr] + df_ev200charger_TC[yr], name = yr)
    df_reev20_TC[yr] = pd.Series(df_reev20battpack_TC[yr] + df_reev20nonbatt_TC[yr] + df_reev20labor_TC[yr] + df_reev20charger_TC[yr], name = yr)
    df_reev40_TC[yr] = pd.Series(df_reev40battpack_TC[yr] + df_reev40nonbatt_TC[yr] + df_reev40labor_TC[yr] + df_reev40charger_TC[yr], name = yr)
df_ev75_TC['key'] = df_ev75_TC['Tech'] + '-' + df_ev75_TC['WR_net'].map(str)
df_ev100_TC['key'] = df_ev100_TC['Tech'] + '-' + df_ev100_TC['WR_net'].map(str)
df_ev200_TC['key'] = df_ev200_TC['Tech'] + '-' + df_ev200_TC['WR_net'].map(str)
df_reev20_TC['key'] = df_reev20_TC['Tech'] + '-' + df_reev20_TC['WR_net'].map(str)
df_reev40_TC['key'] = df_reev40_TC['Tech'] + '-' + df_reev40_TC['WR_net'].map(str)

df_evSys_TC = df_p2_TC
df_evSys_TC = df_evSys_TC.append(df_mhev_TC)
df_evSys_TC = df_evSys_TC.append(df_mhev48V_TC)
df_evSys_TC = df_evSys_TC.append(df_ev75_TC)
df_evSys_TC = df_evSys_TC.append(df_ev100_TC)
df_evSys_TC = df_evSys_TC.append(df_ev200_TC)
df_evSys_TC = df_evSys_TC.append(df_reev20_TC)
df_evSys_TC = df_evSys_TC.append(df_reev40_TC)

#grab IMA and PowerSplit TCs to append to evSys_TC so all EV sys costs are on same tab
df_ima_TC = pd.DataFrame(df_ev_TC[df_ev_TC['Tech'] == 'IMA'])
df_powersplit_TC = pd.DataFrame(df_ev_TC[df_ev_TC['Tech'] == 'PowerSplit'])
df_ima_TC.drop(df_ima_TC.columns[[4, 5, 6, 7]], axis=1, inplace=True)
df_powersplit_TC.drop(df_powersplit_TC.columns[[4, 5, 6, 7]], axis=1, inplace=True)
df_ima_TC['key'] = df_ima_TC['Tech'] + '-' + df_ima_TC['WR_net'].map(str)
df_powersplit_TC['key'] = df_powersplit_TC['Tech'] + '-' + df_powersplit_TC['WR_net'].map(str)
df_evSys_TC = df_evSys_TC.append(df_ima_TC)
df_evSys_TC = df_evSys_TC.append(df_powersplit_TC)

# >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
# This section develops dataFrames & loops to calc WR DMCs on a per-pound basis, and to calc ICs on both a
# per-pound and per-vehicle basis using an alternative (new) methodology for WR ICs
# >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

# first calc WR costs on a per pound basis
wr_DMC_pPound = pd.read_excel(inputsFile, 'wr_dmc_TAR', skiprows = 9)
wr_IC_pPound = pd.read_excel(inputsFile, 'wr_ic_TAR', skiprows=9)

wr_DMC_pPound = wr_DMC_pPound.sort_values(by=['Tech_#', 'key'], ascending=[True, True])
wr_IC_pPound = wr_IC_pPound.sort_values(by=['Tech_#', 'key'], ascending=[True, True])
wr_DMC_pPound.index = range(0, len(wr_DMC_pPound))
wr_IC_pPound.index = range(0, len(wr_IC_pPound))

df_wr_DMC_pPound = pd.DataFrame(wr_DMC_pPound, columns=['Tech_#', 'Tech', 'key', 'CWC/VehType', 'LF_code',
                                                          'ST_Thru', 'Complexity', 'costPerPound_ST', 'costPerPound_LT',
                                                          'cost_type'])
df_wr_IC_pPound = pd.DataFrame(wr_IC_pPound, columns=['Tech_#', 'Tech', 'key', 'CWC/VehType', 'LF_code',
                                                        'ST_Thru', 'Complexity', 'costPerPound_ST', 'costPerPound_LT',
                                                        'cost_type'])
df_wr_TC_pPound = pd.DataFrame(wr_DMC_pPound, columns=['Tech_#', 'Tech', 'key', 'CWC/VehType', 'LF_code',
                                                         'ST_Thru', 'Complexity', 'costPerPound_ST', 'costPerPound_LT',
                                                         'cost_type'])

df_wr_DMC_pPound['cost_type'] = 'DMC'
df_wr_IC_pPound['cost_type'] = 'IC'
df_wr_TC_pPound['cost_type'] = 'TC'

# learningFactors = pd.read_excel(inputsFile, 'lfTable')
df_wr_learningFactors_s = df_wr_DMC_pPound.merge(learningFactors, on ='Tech_#')

calc_wr_DMCs_pPound = pd.DataFrame(df_wr_DMC_pPound['key'])
calc_wr_ICs_pPound = pd.DataFrame(df_wr_IC_pPound['key'])
calc_wr_TCs_pPound = pd.DataFrame(df_wr_TC_pPound['key'])

print("Calculating weight reduction DMCs/ICs/TCs")
for i in range(yrStart, yrEnd):
    yr = str(i)
    calc_wr_DMCs_pPound[yr] = pd.Series(df_wr_DMC_pPound['costPerPound_ST'] * df_wr_learningFactors_s[yr], name = yr)
    if i <= 2024:
        calc_wr_ICs_pPound[yr] = pd.Series(df_wr_IC_pPound['costPerPound_ST'], name = yr)
    else:
        calc_wr_ICs_pPound[yr] = pd.Series(df_wr_IC_pPound['costPerPound_LT'], name = yr)
    calc_wr_TCs_pPound[yr] = calc_wr_DMCs_pPound[yr] + calc_wr_ICs_pPound[yr]
df_wr_DMC_pPound = df_wr_DMC_pPound.merge(calc_wr_DMCs_pPound, on ='key')
df_wr_IC_pPound = df_wr_IC_pPound.merge(calc_wr_ICs_pPound, on ='key')
df_wr_TC_pPound = df_wr_TC_pPound.merge(calc_wr_TCs_pPound, on ='key')

df_wr_ALL_pPound = df_wr_DMC_pPound
df_wr_ALL_pPound = df_wr_ALL_pPound.append(df_wr_IC_pPound)
df_wr_ALL_pPound = df_wr_ALL_pPound.append(df_wr_TC_pPound)

# second calc WR costs on a per vehicle basis
wr_DMC_pVeh = pd.read_excel(inputsFile, 'wr_dmc_TAR', skiprows = 9)
wr_IC_pVeh = pd.read_excel(inputsFile, 'wr_ic_TAR', skiprows=9)
curbWts = pd.read_excel(inputsFile, 'CurbWts', skiprows=1)
curbWts=pd.DataFrame(curbWts, columns=['CWC/VehType', 'curbWt'])

wr_DMC_pVeh = wr_DMC_pVeh.sort_values(by=['Tech_#', 'key'], ascending=[True, True])
wr_IC_pVeh = wr_IC_pVeh.sort_values(by=['Tech_#', 'key'], ascending=[True, True])
wr_DMC_pVeh.index = range(0, len(wr_DMC_pVeh))
wr_IC_pVeh.index = range(0, len(wr_IC_pVeh))
df_wr_DMC_pVeh = pd.DataFrame(wr_DMC_pVeh, columns=['Tech_#', 'Tech', 'key', 'CWC/VehType', 'LF_code',
                                                          'ST_Thru', 'Complexity', 'costPerPound_ST', 'costPerPound_LT',
                                                        'cost_type'])
df_wr_IC_pVeh = pd.DataFrame(wr_IC_pVeh, columns=['Tech_#', 'Tech', 'key', 'CWC/VehType', 'LF_code',
                                                        'ST_Thru', 'Complexity', 'costPerPound_ST', 'costPerPound_LT',
                                                      'cost_type'])
df_wr_TC_pVeh = pd.DataFrame(wr_DMC_pVeh, columns=['Tech_#', 'Tech', 'key', 'CWC/VehType', 'LF_code',
                                                         'ST_Thru', 'Complexity', 'costPerPound_ST', 'costPerPound_LT',
                                                       'cost_type'])

df_wr_DMC_pVeh['cost_type'] = 'DMC'
df_wr_IC_pVeh['cost_type'] = 'IC'
df_wr_TC_pVeh['cost_type'] = 'TC'

df_wr_DMC_pVeh = df_wr_DMC_pVeh.merge(curbWts, on='CWC/VehType')
df_wr_IC_pVeh = df_wr_IC_pVeh.merge(curbWts, on='CWC/VehType')
df_wr_TC_pVeh = df_wr_TC_pVeh.merge(curbWts, on='CWC/VehType')
df_wr_DMC_pVeh = df_wr_DMC_pVeh.sort_values(by=['Tech_#', 'key'], ascending=[True, True])
df_wr_IC_pVeh = df_wr_IC_pVeh.sort_values(by=['Tech_#', 'key'], ascending=[True, True])
df_wr_TC_pVeh = df_wr_TC_pVeh.sort_values(by=['Tech_#', 'key'], ascending=[True, True])
df_wr_DMC_pVeh.index = range(0, len(df_wr_DMC_pVeh))
df_wr_IC_pVeh.index = range(0, len(df_wr_IC_pVeh))
df_wr_TC_pVeh.index = range(0, len(df_wr_TC_pVeh))
df_wr_DMC_pVeh['lbsRemoved'] = df_wr_DMC_pVeh['curbWt'] * df_wr_DMC_pVeh['Tech']/100
df_wr_IC_pVeh['lbsRemoved'] = df_wr_IC_pVeh['curbWt'] * df_wr_IC_pVeh['Tech']/100
df_wr_TC_pVeh['lbsRemoved'] = df_wr_TC_pVeh['curbWt'] * df_wr_TC_pVeh['Tech']/100

calc_wr_DMCs_pVeh = pd.DataFrame(df_wr_DMC_pVeh['key'])
calc_wr_ICs_pVeh = pd.DataFrame(df_wr_IC_pVeh['key'])
calc_wr_TCs_pVeh = pd.DataFrame(df_wr_TC_pVeh['key'])

for i in range(yrStart, yrEnd):
    yr = str(i)
    calc_wr_DMCs_pVeh[yr] = pd.Series(df_wr_DMC_pPound[yr] * df_wr_DMC_pVeh['lbsRemoved'], name = yr)
    calc_wr_ICs_pVeh[yr] = pd.Series(df_wr_IC_pPound[yr] * df_wr_IC_pVeh['lbsRemoved'], name = yr)
    calc_wr_TCs_pVeh[yr] = calc_wr_DMCs_pVeh[yr] + calc_wr_ICs_pVeh[yr]
df_wr_DMC_pVeh = df_wr_DMC_pVeh.merge(calc_wr_DMCs_pVeh, on ='key')
df_wr_IC_pVeh = df_wr_IC_pVeh.merge(calc_wr_ICs_pVeh, on ='key')
df_wr_TC_pVeh = df_wr_TC_pVeh.merge(calc_wr_TCs_pVeh, on ='key')

df_wr_ALL_pVeh = df_wr_DMC_pVeh
df_wr_ALL_pVeh = df_wr_ALL_pVeh.append(df_wr_IC_pVeh)
df_wr_ALL_pVeh = df_wr_ALL_pVeh.append(df_wr_TC_pVeh)

print("Writing to and saving Output file")

# writeFile = pd.ExcelWriter('OMEGA_TechCosts_8400.xlsx')
df_et_ALL.to_excel(writeFile, 'EngTrans', index=False)
df_wr_ALL.to_excel(writeFile, 'WR_FRM', index=False)
df_wr_ALL_pPound.to_excel(writeFile, 'WR_TAR_pPound', index=False)
df_wr_ALL_pVeh.to_excel(writeFile, 'WR_TAR_pVeh', index=False)
df_ev_ALL.to_excel(writeFile, 'EV_techs', index=False)
df_evSys_TC.to_excel(writeFile, 'EV_system', index=False)
inputs.to_excel(writeFile, 'Inputs')
py_version.to_excel(writeFile, 'script', index=False)
writeFile.save()

print("Complete")

