property.py

import marimo

__generated_with = "0.14.16"
app = marimo.App(width="medium")


@app.cell
def _():
    import marimo as mo
    import pandas as pd

    return (pd,)


@app.cell
def _(pd):
    import os

    directory = "./Excel Data"
    summary_list = []

    for filename in os.listdir(directory):
        if filename.endswith(".xlsx"):
            filepath = os.path.join(directory, filename)
            df = pd.DataFrame(pd.read_excel(filepath))
            county = filename.replace(".xlsx", "")

            properties_str = df.iloc[0, 0]
            properties_num = int(properties_str.split()[0])

            df.columns = df.iloc[1]
            df = df.drop([0, 1]).reset_index(drop=True)

            df["SqFt"] = pd.to_numeric(df["SqFt"], errors="coerce")
            df["Asking Price"] = pd.to_numeric(df["Asking Price"], errors="coerce")
            df["Price/SqFt"] = pd.to_numeric(df["Price/SqFt"], errors="coerce")

            large_props = df[df["SqFt"] > 100_000]
            num_large_props = len(large_props)
            price_per_sqft_large = large_props["Price/SqFt"].mean(skipna=True)
            num_properties = len(df)
            average_price = df["Asking Price"].mean(skipna=True)
            price_per_sqft_all = df["Price/SqFt"].mean(skipna=True)

            summary_list.append(
                {
                    "County": county,
                    "Total Properties": num_properties,
                    "Properties >100k SqFt": num_large_props,
                    "Avg Price/SqFt (All)": price_per_sqft_all,
                    "Avg Asking Price": average_price,
                }
            )

    summary_df = pd.DataFrame(summary_list)
    summary_df.columns = [
        "name",
        "Total Properties",
        "Properties >100k SqFt",
        "Avg Price/SqFt (All)",
        "Avg Asking Price",
    ]
    summary_df
    return (summary_df,)


@app.cell
def _(summary_df):
    summary_df.to_csv("outputData/county_property_summary.csv", index=False)
    # print("Summary saved to county_property_summary.csv")
    return


@app.cell
def _(summary_df):
    def calculate_investment_potential(df):
        """
        Calculate investment potential score for each city based on multiple factors
        """
        # Create a copy to avoid modifying original dataframe
        score_df = df.copy()

        # Normalize each metric to 0-1 scale
        metrics = {}

        # 1. Total Properties (more properties = more opportunities)
        metrics["properties_score"] = (
            score_df["Total Properties"] - score_df["Total Properties"].min()
        ) / (score_df["Total Properties"].max() - score_df["Total Properties"].min())

        # 2. Large Properties Ratio (properties >100k SqFt as percentage of total)
        score_df["Large Properties Ratio"] = (
            score_df["Properties >100k SqFt"] / score_df["Total Properties"]
        )
        metrics["large_properties_score"] = (
            score_df["Large Properties Ratio"]
            - score_df["Large Properties Ratio"].min()
        ) / (
            score_df["Large Properties Ratio"].max()
            - score_df["Large Properties Ratio"].min()
        )

        # 3. Price/SqFt (lower price per sqft = better value)
        # Inverse because lower price is better for buying
        metrics["price_per_sqft_score"] = 1 - (
            (score_df["Avg Price/SqFt (All)"] - score_df["Avg Price/SqFt (All)"].min())
            / (
                score_df["Avg Price/SqFt (All)"].max()
                - score_df["Avg Price/SqFt (All)"].min()
            )
        )

        # 4. Asking Price (lower asking price = better affordability)
        # Inverse because lower price is better for buying
        metrics["asking_price_score"] = 1 - (
            (score_df["Avg Asking Price"] - score_df["Avg Asking Price"].min())
            / (score_df["Avg Asking Price"].max() - score_df["Avg Asking Price"].min())
        )

        # Calculate weighted composite score
        weights = {
            "properties_score": 0.45,  # Market size importance
            "large_properties_score": 0.30,  # Premium properties importance
            "price_per_sqft_score": 0.25,  # Value importance
        }

        # Calculate final score
        score_df["Investment_Score"] = (
            metrics["properties_score"] * weights["properties_score"]
            + metrics["large_properties_score"] * weights["large_properties_score"]
            + metrics["price_per_sqft_score"] * weights["price_per_sqft_score"]
            + metrics["asking_price_score"] * weights["asking_price_score"]
        )

        # Rank cities by investment score
        score_df["Rank"] = (
            score_df["Investment_Score"]
            .rank(ascending=False, method="dense")
            .astype(int)
        )

        # Sort by rank
        score_df = score_df.sort_values("Rank")

        return score_df

    # Comprehensive analysis
    investment_ranking = calculate_investment_potential(summary_df)
    investment_ranking.to_csv("outputData/county_property_score.csv", index=False)
    return


@app.cell
def _():
    return


if __name__ == "__main__":
    app.run()