import pandas as pd

df = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final SAL/Parity Data "
    "08012026.xlsx"
)
df["wall_combined"] = df["Wall Construction"] + "+" + df["Wall Insulation"].fillna("Unknown Insulation")

df['SAP Score'].mean()

df[~pd.isnull(df["Lodged EPC Score"])]["Lodged EPC Score"].mean()
df[~pd.isnull(df["Lodged EPC Score"])]["SAP Score"].mean()

df['Difference'] = abs(df['SAP Score'] - df['Lodged EPC Score'])
df[~pd.isnull(df["Lodged EPC Score"])]["Difference"].mean()

df["Lodged EPC Band"].value_counts(normalize=True)
df["SAP Band"].value_counts(normalize=True)

z = df[df["SAP Band"] != df["Lodged EPC Band"]]
agg = z.groupby(["Lodged EPC Band", "SAP Band"]).size().reset_index(name="count")

recommendations_epc_c = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/Final SAL/EPC C - no "
    "solid floor, ashp 3.0 - corrected.xlsx"
)
recommendations_epc_c["uprn"] = recommendations_epc_c["uprn"].astype(int).astype(str)

combined = recommendations_epc_c.merge(
    df,
    left_on="uprn",
    right_on="UPRN",
    suffixes=("_rec", "_sal")
)

combined = combined[["uprn", "SAP Score", "current_sap_points", "walls", "wall_combined"]]

combined[combined["SAP Score"] < 69]["current_epc_rating"].value_counts()
combined[combined["SAP Score"] < 69]["SAP Band"].value_counts()
combined[combined["SAP Score"] < 69].shape
combined[combined["current_sap_points"] < 69]

combined["SAP Band"].value_counts()

# Our Cs
combined_cs = combined[combined["SAP Score"] < 69]
combined_cs["SAP Band"].value_counts()
# Their C and below


compare = recommendations_epc_c[recommendations_epc_c["current_sap_points"] < 69]

packages = recommendations_epc_c[recommendations_epc_c["total_retrofit_cost"] > 0]
packages["current_epc_rating"].value_counts()

# TODO: 612 units
23219 - 612
errors = recommendations_epc_c[
    (recommendations_epc_c["current_sap_points"] >= 69) &
    (recommendations_epc_c["total_retrofit_cost"] > 0)
    ]
errors["total_retrofit_cost"].sum()

below_epc_c = recommendations_epc_c[recommendations_epc_c["current_sap_points"] < 69]

below_epc_c_compare = below_epc_c.merge(
    df,
    left_on="uprn",
    right_on="UPRN",
    suffixes=("_rec", "_sal")
)

eg1 = below_epc_c_compare[below_epc_c_compare["SAP Band"] == "C"].copy()
eg1["wall_combined"].value_counts()

eg1_counts = eg1.groupby(["walls", "wall_combined"]).size().reset_index(name="count")
eg1_counts = eg1_counts.sort_values("count", ascending=False)

externally_insulated = eg1[
    (eg1["wall_combined"] == "Solid Brick+External") &
    pd.isnull(eg1["internal_wall_insulation"])
    ]

externally_insulated[externally_insulated.index == 823]["uprn"]

recommendations_epc_c[
    (recommendations_epc_c["current_sap_points"] < 69) &
    (recommendations_epc_c["current_sap_points"] > 68)
    ].shape

recommendations_epc_c[recommendations_epc_c["wall_combined"] == ""]
