import csv
import re

# Input and output file paths
input_file = "mentor_result.csv"
output_file = "mentor_result_transformed.csv"

# Function to remove text within parentheses
def clean_topic(topic):
    return re.sub(r"\s*\(.*?\)", "", topic).strip()

# Read the input CSV and process the data
def process_csv(input_file, output_file):
    topics_set = set()
    data = {}

    # Read the input CSV
    with open(input_file, mode="r", encoding="utf-8") as csvfile:
        reader = csv.DictReader(csvfile)
        for row in reader:
            email = row["Email Address"]
            topics = row["Topics"].split(",")  # Split topics by comma
            topics = [clean_topic(topic) for topic in topics]  # Clean and strip topics
            topics_set.update(topics)  # Add topics to the set
            if email not in data:
                data[email] = set()
            data[email].update(topics)

    # Create a sorted list of all unique topics (Y-axis)
    topics_list = sorted(topics_set)

    # Write the output CSV
    with open(output_file, mode="w", encoding="utf-8", newline="") as csvfile:
        writer = csv.writer(csvfile)
        # Write the header row
        header = ["Topic"] + list(data.keys())
        writer.writerow(header)

        # Write the data rows
        for topic in topics_list:
            row = [topic] + ["X" if topic in data[email] else "" for email in data.keys()]
            writer.writerow(row)

# Run the function
process_csv(input_file, output_file)
print(f"Transformed CSV has been saved to {output_file}")