utils/data_processing.py

import pandas as pd


def format_docs(docs):
    """Print the contents of a list of Langchain Documents.
    Args:
        docs (str):
    """
    print(
        f"\n{'-' * 100}\n".join(
            [f"Document {i+1}:\n\n" + d.page_content for i, d in enumerate(docs)]
        )
    )


def clean_and_format_text(text):
    if isinstance(text, str):
        # Replace curly apostrophes with straight ones
        text = text.replace("\u2019", "'")
        words = text.split()
        # Title case words, preserving acronyms
        title_words = [
            word if word.isupper() and len(word) > 1 else word.capitalize()
            for word in words
        ]
        return " ".join(title_words)
    else:
        return text


def categorize_location(location):
    if any(place in location.lower() for place in ["cordova bay", "james bay"]):
        return "Victoria"
    return location


def excel_to_dataframe(data_directory: str) -> pd.DataFrame:
    """Load an Excel file, clean its contents, and generate a pd.Dataframe.

    Args:
        data_directory (str): File path to the directory where the Excel file is located.

    Raises:
        FileNotFoundError: If no Excel files are found in the specified directory.

    Returns:
        pd.Dataframe:

    """
    # Get the xls file name (one excel worksheet)
    excel_files = [file for file in data_directory.iterdir() if file.suffix == ".xlsx"]

    if not excel_files:
        raise FileNotFoundError("No Excel files found in the specified directory.")
    if len(excel_files) > 1:
        raise ValueError("More than one Excel file found in the specified directory.")

    path = excel_files[0]

    # Load Excel file
    df = pd.read_excel(path, engine="openpyxl")

    # Change column names to title case
    df.columns = df.columns.str.title()

    # Clean data
    for col in df.columns:
        if col.lower() != "booking link" and df[col].dtype == "object":
            df[col] = df[col].str.strip().apply(clean_and_format_text)

    # Handle missing values
    df.fillna("Information Not Available", inplace=True)

    # Add city column
    df["City"] = df["Location"].apply(categorize_location)

    return df