import re
import pandas as pd
from tqdm import tqdm
import Levenshtein
from backend.SearchEpc import SearchEpc
from utils.s3 import read_dataframe_from_s3_parquet

# Average value of a property in the midlands in 2024 was £238,000. Since these are EPC F & G properties, we assume
# £207,000 since they trade at a discount. This is based on the rightmove study where moving from an EPC F/G -> C has a
# +15% impact on valuation and D -> C has a +3% impact on valuation.
# The mode EPC rating is D, so we associate the £238k valuation with an EPC D property
# Therefore value_of_F * 1.15 = value_of_D * 1.03
# Therefore value_of_F = value_of_D * 1.03/1.15 = 238k * (1.03/1.15) = 213165
PROPERTY_VALUE_ESTIMATE = 213_165


def aggregate_matches(matching_lookup, company_ownership, properties):
    df = matching_lookup.merge(
        company_ownership, how="left", on="Title Number"
    ).merge(
        properties[["UPRN", "LOCAL_AUTHORITY_LABEL"]], how="left", on="UPRN"
    )
    counts = (
        df.groupby(["Company Registration No. (1)", "LOCAL_AUTHORITY_LABEL"])["UPRN"]
        .count()
        .reset_index(name="number_of_properties")
    )
    counts = counts.sort_values("number_of_properties", ascending=False)

    pivot_counts = counts.pivot_table(
        index=["Company Registration No. (1)"],  # Rows: companies and proprietors
        columns="LOCAL_AUTHORITY_LABEL",  # Columns: each local authority
        values="number_of_properties",  # The counts of properties
        fill_value=0  # Fill missing values with 0 (where there are no properties owned)
    ).reset_index()

    total_counts = (
        df.groupby(["Company Registration No. (1)"])["UPRN"]
        .count()
        .reset_index(name="total_number_of_properties")
    )

    # We have cases where the same company registration number results in the same company name, so we produce a best
    # name per company registration number
    best_names = (
        df.groupby(["Company Registration No. (1)"])["Proprietor Name (1)"]
        .first()
        .reset_index()
    )

    total_counts = best_names.merge(
        total_counts, how="left", on=["Company Registration No. (1)"]
    )

    pivot_counts = pivot_counts.merge(
        total_counts, how="left", on=["Company Registration No. (1)"]
    )

    pivot_counts = pivot_counts.sort_values("total_number_of_properties", ascending=False)

    pivot_counts["approx_value"] = PROPERTY_VALUE_ESTIMATE * pivot_counts["total_number_of_properties"]
    pivot_counts["cumulative_value"] = pivot_counts["approx_value"].cumsum()

    return pivot_counts


def find_f_g_properties(paths):
    data = []
    for path in tqdm(paths):
        epc_data = pd.read_csv(path, low_memory=False)

        epc_data = epc_data[~pd.isnull(epc_data["UPRN"])]
        epc_data["UPRN"] = epc_data["UPRN"].astype(int).astype(str)

        # Get the newest EPC for each UPRN. We use LODGEMENT_DATE as a proxy for this
        epc_data["LODGEMENT_DATETIME"] = pd.to_datetime(epc_data["LODGEMENT_DATETIME"], format='mixed')

        epc_data = epc_data.sort_values("LODGEMENT_DATETIME", ascending=False).drop_duplicates("UPRN")

        # Get G & F properties
        epc_data = epc_data[epc_data["CURRENT_ENERGY_RATING"].isin(["G", "F"])]
        data.append(epc_data)

    data = pd.concat(data)

    # Save as an excel
    data.to_excel("EPC F & G Properties.xlsx", index=False)


def remove_text_in_brackets(address: str) -> str:
    """
    Removes any text within parentheses, including the parentheses themselves.

    Parameters:
    - address (str): The address string to clean.

    Returns:
    - str: The cleaned address with text in parentheses removed.
    """
    # Regex to find and remove content in parentheses
    cleaned_address = re.sub(r'\s*\([^)]*\)', '', address)
    return cleaned_address


def extract_numeric_part(house_number: str) -> str:
    """
    Extracts only the numeric part from a house number that may contain letters.

    Parameters:
    - house_number (str): The house number string possibly containing letters.

    Returns:
    - str: The numeric part of the house number.
    """
    # Use regular expression to replace all non-digit characters with nothing
    numeric_part = re.sub(r'\D', '', house_number)
    return numeric_part


def levenstein_match(matching_string, df, address_col):
    match_to = df[address_col].tolist()
    # Strip out punctuation and spaces
    match_to = [re.sub(r'[^\w\s]', '', x) for x in match_to]
    match_to = [x.replace(" ", "") for x in match_to]

    # Perform matching between full key and match_to
    distances = [Levenshtein.distance(matching_string, s) for s in match_to]
    best_match_index = distances.index(min(distances))
    # We might want to consider a threshold for the distance, however for the momeny,
    # we don't consider this for the moment
    df = df.iloc[best_match_index:best_match_index + 1]

    return df


def extract_range_from_house_number(house_number_range: str):
    """
    Detects if the house number includes a numeric range (formatted as 'x-y') and extracts all values within this range.
    Non-numeric strings containing hyphens are ignored.

    Parameters:
    - house_number_range (str): The house number string that might contain a range.

    Returns:
    - list of str: A list of all numbers within the range if it is a range; otherwise, returns None.
    """

    if not house_number_range:
        return None

    if '-' in house_number_range:
        parts = house_number_range.split('-')
        if len(parts) == 2 and parts[0].isdigit() and parts[1].isdigit():
            # Both parts are numeric, so it's a valid range
            start, end = map(int, parts)  # Convert parts to integers
            return [str(x) for x in range(start, end + 1)]
        else:
            # Not a valid numeric range
            return None
    else:
        # No hyphen present or not a range
        return None


def is_in_range(row, house_no):
    """ Check if the house number is within the range provided in the row. """
    if row and any(house_no == num for num in row):
        return True
    return False


def remove_duplicate_matches(matching_lookup, properties, company_ownership):
    duplicated_titles = matching_lookup[matching_lookup["Title Number"].duplicated()]["Title Number"].unique()

    to_drop = []
    for dupe_title in duplicated_titles:
        dupe_data = matching_lookup[matching_lookup["Title Number"] == dupe_title].copy()
        matched_addresses = dupe_data.merge(
            properties[["UPRN", "ADDRESS"]].rename(columns={"ADDRESS": "epc_address"}),
            how="left", on="UPRN"
        ).merge(
            company_ownership[["Title Number", "Property Address"]],
            how="left", on="Title Number"
        )
        # We perform levenstein to get the best match
        best_match = levenstein_match(
            matching_string=matched_addresses["Property Address"].values[0],
            df=matched_addresses,
            address_col="epc_address"
        )
        matches_to_drop = matched_addresses[
            ~matched_addresses["UPRN"].isin(best_match["UPRN"].values)
        ]

        to_drop.append(
            matches_to_drop[["UPRN", "Title Number"]].copy()
        )

    to_drop = pd.concat(to_drop)

    if not to_drop.empty:
        merged = pd.merge(matching_lookup, to_drop, on=['UPRN', 'Title Number'], how='left', indicator=True)
        merged = merged[merged['_merge'] == 'left_only'].drop(columns=['_merge'])

        return merged

    return matching_lookup


def remove_duplicate_uprn_matches(matching_lookup, properties, company_ownership):
    dupe_uprns = matching_lookup[matching_lookup["UPRN"].duplicated()]["UPRN"].unique().tolist()

    to_drop = []
    for dupe_uprn in dupe_uprns:
        dupe_data = matching_lookup[matching_lookup["UPRN"] == dupe_uprn].copy()
        matched_addresses = dupe_data.merge(
            properties[["UPRN", "ADDRESS"]].rename(columns={"ADDRESS": "epc_address"}),
            how="left", on="UPRN"
        ).merge(
            company_ownership[["Title Number", "Property Address"]],
            how="left", on="Title Number"
        )
        # We perform levenstein to get the best match
        best_match = levenstein_match(
            matching_string=matched_addresses["Property Address"].values[0],
            df=matched_addresses,
            address_col="epc_address"
        )
        matches_to_drop = matched_addresses[
            ~matched_addresses["Title Number"].isin(best_match["Title Number"].values)
        ]

        to_drop.append(
            matches_to_drop[["UPRN", "Title Number"]].copy()
        )

    to_drop = pd.concat(to_drop)

    if not to_drop.empty:
        merged = pd.merge(matching_lookup, to_drop, on=['UPRN', 'Title Number'], how='left', indicator=True)
        merged = merged[merged['_merge'] == 'left_only'].drop(columns=['_merge'])

        return merged

    return matching_lookup


def app():
    """
    This script is for scoping property ownership for EPC F & G rated properties in Birmingam, for Goldman Sachs
    """

    # TODO: This property:
    #       https://epc.opendatacommunities.org/domestic/search?address=&postcode=&local-authority=&constituency
    #       =&uprn=100031179243&from-month=1&from-year=2008&to-month=12&to-year=2024
    #       is actually listed in two local authorities causing us to think it's an EPC F & G property, but it's
    #       it's actually EPC E. Need to handle this, probably by reading in all of the EPC data, concatenating together
    #       and performing a singular filter for most recent EPC by UPRN
    # paths = [
    #     "local_data/all-domestic-certificates/domestic-E08000025-Birmingham/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E08000031-Wolverhampton/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E08000026-Coventry/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E06000016-Leicester/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E06000015-Derby/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E06000021-Stoke-on-Trent/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E06000018-Nottingham/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E07000154-Northampton/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E06000061-North-Northamptonshire/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E06000062-West-Northamptonshire/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E07000152-East-Northamptonshire/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E07000155-South-Northamptonshire/certificates.csv",
    #     #
    #     "local_data/all-domestic-certificates/domestic-E08000027-Dudley/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E08000029-Solihull/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E07000234-Bromsgrove/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E08000030-Walsall/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E08000028-Sandwell/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E06000019-Herefordshire-County-of/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E06000020-Telford-and-Wrekin/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E07000218-North-Warwickshire/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E07000222-Warwick/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E07000237-Worcester/certificates.csv",
    #     # East midlands
    #     "local_data/all-domestic-certificates/domestic-E07000035-Derbyshire-Dales/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E07000038-North-East-Derbyshire/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E07000039-South-Derbyshire/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E06000012-North-East-Lincolnshire/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E06000013-North-Lincolnshire/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E07000138-Lincoln/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E07000134-North-West-Leicestershire/certificates.csv",
    #     "local_data/all-domestic-certificates/domestic-E06000017-Rutland/certificates.csv",
    # ]
    # paths = list(set(paths))
    # find_f_g_properties(paths)

    properties = pd.read_excel("EPC F & G Properties.xlsx")
    company_ownership = pd.read_csv("/Users/khalimconn-kowlessar/Downloads/CCOD_FULL_2024_04.csv")
    company_ownership["is_overseas"] = False
    overseas_company_ownership = pd.read_csv("/Users/khalimconn-kowlessar/Downloads/OCOD_FULL_2024_04 2.csv")
    overseas_company_ownership["is_overseas"] = True

    company_ownership = pd.concat([company_ownership, overseas_company_ownership])

    # FIlter on relevant postcodes
    company_ownership = company_ownership[
        company_ownership["Postcode"].str.lower().isin(properties["POSTCODE"].str.lower().unique())]

    # Now we filter properties the other way around
    properties = properties[properties["POSTCODE"].str.lower().isin(company_ownership["Postcode"].str.lower().unique())]
    # We end up with 7.4k entires on a postcode match, however we need to now do a direct address match
    # Take just private rentals
    properties = properties[
        properties["TENURE"].isin(["rental (private)", "Rented (private)", "owner-occupied", "Owner-occupied"])
    ]
    # We have some duplicated on UPRN
    # Take the newest UPRN
    properties = properties.sort_values("LODGEMENT_DATE", ascending=False).drop_duplicates("UPRN")

    # Remove entries where the address begins with the term "land adjoining", or other records that don't reference the
    # the property itself
    starting_terms = [
        "land adjoining", "land on the", "land to the rear of", "land and buildings on the",
        "garage adjoining", "car park adjoining", "the land adjoining", "land and buildings adjoining",
        "all royal mines"
    ]
    for starting_term in starting_terms:
        company_ownership = company_ownership[
            ~company_ownership["Property Address"].str.lower().str.startswith(starting_term)
        ]

    freehold_matching_lookup = []  # 634
    leasehold_matching_lookup = []  # 86
    shared_leasehold_match = []
    shared_freehold_match = []
    for _, address in tqdm(properties.iterrows(), total=len(properties)):
        match_type = "exact"
        filtered = company_ownership[
            company_ownership["Postcode"].str.lower() == address["POSTCODE"].lower()
            ].copy()

        # Remove postcode and remove trailing commas
        filtered["house_number"] = (
            filtered["Property Address"]
            .apply(remove_text_in_brackets)
            .apply(SearchEpc.get_house_number)
            .str.lower()
            .str.replace(",", "")
        )
        house_no = SearchEpc.get_house_number(address["ADDRESS1"])
        if house_no is not None:
            house_no = house_no.replace(",", "")

        if house_no is None:
            # It's hard for us to get a reliable match
            # filtered = filtered[filtered["Property Address"].str.contains(address["ADDRESS1"])]
            # if filtered.shape[0] > 1:
            #     raise Exception("No valid - maybe we should do levenstein?")
            continue

        else:

            if house_no not in filtered["house_number"].values:
                # If this happens, we check house_number for a x-y range of addresses
                filtered["house_number_range"] = filtered["house_number"].apply(extract_range_from_house_number)
                # If we have found a house number range, we check if the house number is in the range and if not,
                # we drop the row
                filtered['is_in_range'] = filtered['house_number_range'].apply(lambda x: is_in_range(x, house_no))

                if filtered['is_in_range'].any():
                    # If house_no is found in any range, keep only rows where it is in range
                    filtered = filtered[filtered['is_in_range']]
                else:
                    # If house_no is not found in any range, filter out rows where 'house_number_range' is not None
                    filtered = filtered[filtered['house_number_range'].isnull()]

                # Strip out letters from house_no and house_number
                house_no = extract_numeric_part(house_no)
                filtered["house_number"] = filtered["house_number"].astype(str).apply(extract_numeric_part)
                match_type = "approximate"

            filtered = filtered[filtered["house_number"] == house_no]

        if filtered.empty:
            continue

        filtered_freehold = filtered[filtered["Tenure"] == "Freehold"]
        filtered_leasehold = filtered[filtered["Tenure"] == "Leasehold"]

        if filtered_freehold.shape[0] > 1:
            matched = filtered_leasehold[["Title Number"]].copy()
            matched.insert(0, "UPRN", address["UPRN"])
            shared_freehold_match.append(matched)
        elif not filtered_freehold.empty:
            freehold_matching_lookup.append(
                {
                    "UPRN": address["UPRN"],
                    "Title Number": filtered_freehold["Title Number"].values[0],
                    "match_type": match_type,
                }
            )

        if filtered_leasehold.shape[0] > 1:
            matched = filtered_leasehold[["Title Number"]].copy()
            matched.insert(0, "UPRN", address["UPRN"])
            shared_leasehold_match.append(matched)
        elif not filtered_leasehold.empty:
            leasehold_matching_lookup.append(
                {
                    "UPRN": address["UPRN"],
                    "Title Number": filtered_leasehold["Title Number"].values[0],
                    "match_type": match_type,
                }
            )

    freehold_matching_lookup = pd.DataFrame(freehold_matching_lookup)
    leasehold_matching_lookup = pd.DataFrame(leasehold_matching_lookup)
    shared_leasehold_match = pd.concat(shared_leasehold_match)
    shared_freehold_match = pd.concat(shared_freehold_match)

    # freehold_matching_lookup.to_excel("freehold_matching_lookup_new.xlsx")
    # leasehold_matching_lookup.to_excel("leasehold_matching_lookup_new.xlsx")
    # shared_leasehold_match.to_excel("shared_leasehold_match_new.xlsx")
    # shared_freehold_match.to_excel("shared_freehold_match_new.xlsx")

    # The approximate matches aren't very good
    freehold_matching_lookup = freehold_matching_lookup[freehold_matching_lookup["match_type"] == "exact"]
    leasehold_matching_lookup = leasehold_matching_lookup[leasehold_matching_lookup["match_type"] == "exact"]

    # Combine
    combined_matching_lookup = pd.concat([freehold_matching_lookup, leasehold_matching_lookup])
    # Remove duplicates
    combined_matching_lookup = remove_duplicate_matches(combined_matching_lookup, properties, company_ownership)
    # We also have duplicates at a UPRN level
    combined_matching_lookup = remove_duplicate_uprn_matches(combined_matching_lookup, properties, company_ownership)

    # There are some cases where we have duplicates
    # freehold_matching_lookup = remove_duplicate_matches(freehold_matching_lookup, properties, company_ownership)
    # leasehold_matching_lookup = remove_duplicate_matches(leasehold_matching_lookup, properties, company_ownership)

    matched_addresses = combined_matching_lookup.merge(
        properties[["UPRN", "ADDRESS", "CURRENT_ENERGY_EFFICIENCY", "CURRENT_ENERGY_RATING"]].rename(
            columns={"ADDRESS": "epc_address"}),
        how="left", on="UPRN"
    ).merge(
        company_ownership[["Title Number", "Property Address", "Company Registration No. (1)", "Proprietor Name (1)"]],
        how="left", on="Title Number"
    )

    # shared_freehold_match = pd.DataFrame(shared_freehold_match)
    # Strore these files
    # freehold_matching_lookup.to_excel("freehold_matching_lookup.xlsx")
    # leasehold_matching_lookup.to_excel("leasehold_matching_lookup.xlsx")
    # shared_leasehold_match.to_excel("shared_leasehold_match.xlsx")
    # shared_freehold_match.to_excel("shared_freehold_match.xlsx")
    # read the files
    # freehold_matching_lookup = pd.read_excel("freehold_matching_lookup.xlsx")
    # leasehold_matching_lookup = pd.read_excel("leasehold_matching_lookup.xlsx")
    # shared_leasehold_match = pd.read_excel("shared_leasehold_match.xlsx")

    freehold_aggregate = aggregate_matches(freehold_matching_lookup, company_ownership, properties)
    leasehold_aggregate = aggregate_matches(leasehold_matching_lookup, company_ownership, properties)

    combined_aggregate = aggregate_matches(
        combined_matching_lookup, company_ownership, properties
    )

    investment_20m = combined_aggregate[combined_aggregate["cumulative_value"] <= 20_500_000]
    investment_50m = combined_aggregate[combined_aggregate["cumulative_value"] <= 51_000_000]

    investment_20m_properties = matched_addresses[
        matched_addresses["Company Registration No. (1)"].isin(investment_20m["Company Registration No. (1)"])
    ]

    investment_50m_properties = matched_addresses[
        matched_addresses["Company Registration No. (1)"].isin(investment_50m["Company Registration No. (1)"])
    ]

    portfolio_epc_data_50m = properties[properties["UPRN"].isin(investment_50m_properties["UPRN"])]
    portfolio_epc_data_20m = properties[properties["UPRN"].isin(investment_20m_properties["UPRN"])]

    investment_20m_properties.to_excel("investment_20m_properties 28th May.xlsx", index=False)
    investment_50m_properties.to_excel("investment_50m_properties 28th May.xlsx", index=False)

    # Store the EPC data
    portfolio_epc_data_50m.to_excel("portfolio_epc_data_50m 28th May.xlsx", index=False)
    portfolio_epc_data_20m.to_excel("portfolio_epc_data_20m 28th May.xlsx", index=False)

    # We check if any of these properties are in a conservation area
    valuations = pd.read_excel("property value.xlsx")

    uprn_filenames = read_dataframe_from_s3_parquet(
        bucket_name="retrofit-data-dev", file_key="spatial/filename_meta.parquet"
    )

    geospatial_data = []
    for _, row in tqdm(valuations.iterrows(), total=len(valuations)):
        filtered_df = uprn_filenames[
            (uprn_filenames["lower"] <= row["UPRN"])
            & (uprn_filenames["upper"] >= row["UPRN"])
            ]
        if filtered_df.empty:
            raise Exception("No match found")

        filename = filtered_df.iloc[0]["filenames"]

        spatial_data = read_dataframe_from_s3_parquet(
            bucket_name="retrofit-data-dev", file_key=f"spatial/{filename}"
        )
        spatial = spatial_data[
            spatial_data["UPRN"] == row["UPRN"]
            ][["UPRN", "conservation_status", "is_listed_building", "is_heritage_building"]]
        geospatial_data.append(spatial.to_dict("records")[0])

    geospatial_data = pd.DataFrame(geospatial_data)
    geospatial_data.to_excel("geospatial_data.xlsx", index=False)


def company_aggregation():
    company_ownership = pd.read_csv("/Users/khalimconn-kowlessar/Downloads/CCOD_FULL_2024_04.csv")
    aggregation = (
        company_ownership
        .groupby(["Proprietor Name (1)", "Company Registration No. (1)"])
        ["Property Address"]
        .count()
        .reset_index(name="Number of Properties")
    )
    aggregation = aggregation.sort_values("Number of Properties", ascending=False)

    aggregation.to_excel("Company ownership aggregation.xlsx")


def prepare_anonymised_data():
    investment_50m_properties = pd.read_excel("investment_50m_properties 28th May.xlsx", header=0)
    investment_epc_data = pd.read_excel("portfolio_epc_data_50m 28th May.xlsx", header=0)
    valuations = pd.read_excel("property value.xlsx", header=0)

    # Merge these datasets
    df = investment_50m_properties.merge(
        investment_epc_data[
            ["UPRN", "PROPERTY_TYPE", "BUILT_FORM", "TOTAL_FLOOR_AREA", "LODGEMENT_DATE", "POSTCODE"]
        ].rename(
            columns={
                "PROPERTY_TYPE": "Property Type",
                "BUILT_FORM": "Property Archetype",
                "TOTAL_FLOOR_AREA": "Total Floor Area",
                "LODGEMENT_DATE": "Date EPC Lodged",
                "POSTCODE": "Postcode on EPC"
            }
        ),
        how="inner",
        on="UPRN"
    ).merge(
        valuations.drop(columns=["ADDRESS", "POSTCODE"]).rename(
            columns={
                "Zoopla Valuation": "Expected Valuation",
                "Zoopla Lower Bound": "Valuation - Lower Bound",
                "Zoopla Upper Bound": "Valuation - Upper Bound",
            }
        ),
        how="inner",
        on="UPRN"
    ).rename(
        columns={
            "CURRENT_ENERGY_RATING": "Current EPC",
            "CURRENT_ENERGY_EFFICIENCY": "Current SAP Score",
            "epc_address": "Address on EPC"
        }
    ).drop(
        columns=["Title Number", "match_type", "UPRN"]
    )

    redacted_owner_names = df[["Company Registration No. (1)"]].drop_duplicates()
    redacted_owner_names["Owner"] = ["Owner" + str(i) for i in range(1, len(redacted_owner_names) + 1)]

    df = df.merge(
        redacted_owner_names, how="left", on="Company Registration No. (1)"
    )

    df = df.drop(columns=["Company Registration No. (1)", "Proprietor Name (1)", "Property Address"])
    df = df.sort_values(["Owner", "Date EPC Lodged"], ascending=False)

    redacted_index = []
    for _, owner_properties in df.groupby("Owner"):
        top_50_percent = round(owner_properties.shape[0] / 2 + 0.00001)
        indexes = owner_properties.tail(
            owner_properties.shape[0] - top_50_percent
        ).index

        redacted_index.extend(indexes.tolist())

    import numpy as np
    # Redact addresses and postcodes
    df["Address on EPC"] = np.where(
        df.index.isin(redacted_index),
        "Redacted",
        df["Address on EPC"]
    )

    df["Postcode on EPC"] = np.where(
        df.index.isin(redacted_index),
        "Redacted",
        df["Postcode on EPC"]
    )

    df.to_excel("Property List - 50% redacted.xlsx", index=False)
