import pandas as pd

# Get the wave 2 costing data and produce some breakdowns
costs = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/MOD/Pilot Programme/Measure cost study for MOD.xlsx",
    header=2
)

# Get the EPC data for these


# Cavity
cwi_costs = costs[
    ['Model', 'Total invoiced (including VAT)']
].copy()
cwi_costs["Model"] = "CWI - " + cwi_costs["Model"]
cwi_costs = cwi_costs[~pd.isnull(cwi_costs["Total invoiced (including VAT)"])]

# Loft
li_costs = costs[
    ['Model.2', 'Total invoiced (including VAT).2']
].copy()
li_costs["Model.2"] = "LI - " + li_costs["Model.2"]
li_costs = li_costs[~pd.isnull(li_costs["Total invoiced (including VAT).2"])]
# Rename
li_costs.columns = ["Model", "Total invoiced (including VAT)"]

# Windows
windows_costs = costs[
    ['Model.3', 'Total invoiced (including VAT).3']
].copy()
windows_costs["Model.3"] = "Windows - " + windows_costs["Model.3"]
windows_costs = windows_costs[~pd.isnull(windows_costs["Total invoiced (including VAT).3"])]
# Rename
windows_costs.columns = ["Model", "Total invoiced (including VAT)"]

# Doors
doors_costs = costs[
    ['Model.4', 'Total invoiced (including VAT).4']
].copy()
doors_costs["Model.4"] = "Doors - " + doors_costs["Model.4"]
doors_costs = doors_costs[~pd.isnull(doors_costs["Total invoiced (including VAT).4"])]
# Rename
doors_costs.columns = ["Model", "Total invoiced (including VAT)"]

# ASHP
ashps_costs = costs[
    ['Model.5', 'Total invoiced (including VAT).5']
].copy()
ashps_costs["Model.5"] = "ASHP - " + ashps_costs["Model.5"]
ashps_costs = ashps_costs[~pd.isnull(ashps_costs["Total invoiced (including VAT).5"])]
# Rename
ashps_costs.columns = ["Model", "Total invoiced (including VAT)"]

# Solar
solar_costs = costs[
    ['Model.6', 'Total invoiced (including VAT).6']
].copy()
solar_costs["Model.6"] = "Solar - " + solar_costs["Model.6"]
solar_costs = solar_costs[~pd.isnull(solar_costs["Total invoiced (including VAT).6"])]
# Rename
solar_costs.columns = ["Model", "Total invoiced (including VAT)"]

fabric_costing_data = pd.concat([cwi_costs, li_costs])
windows_doors_costing_data = pd.concat([windows_costs, doors_costs])

windows_doors_costing_data.to_csv(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/MOD/Pilot Programme/windows_doors_costs.csv"
)
fabric_costing_data.to_csv(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/MOD/Pilot Programme/fabric_costing_data.csv"
)
ashps_costs.to_csv("/Users/khalimconn-kowlessar/Documents/hestia/Customers/MOD/Pilot Programme/ashps_costs.csv")
solar_costs.to_csv("/Users/khalimconn-kowlessar/Documents/hestia/Customers/MOD/Pilot Programme/solar_costs.csv")

project_cost_by_age = costs[["Property age ", "TOTAL Cost of Works"]].groupby("Property age ").mean().reset_index()
