export_logs_to_excel.py

"""
Aggregate the log files generated by aco_sim.py to load the data into a
pandas DataFrame and a sheet for each trial in an Excel file.
"""
import pandas as pd

NUM_TRIALS = 5
NUM_EVALUATIONS = 10000
file_name = input("Enter the name of the log file: ")

with open(file_name, "r") as file:
    file_lines = file.readlines()
# Split each line to get the metrics and add them to a pandas DataFrame.
file_lines = [line.strip().split(",") for line in file_lines]
print(f"Imported data from the following log file: {file_name}\n")

# Read the metrics from the log file and generate a DataFrame for each trial.
results = []
for trial_num in range(NUM_TRIALS):
    # Create a DataFrame with the metrics.
    df = pd.DataFrame(
        columns=[
            "Evaluation #",
            "Best Fitness in Evaluation",
            "Best Fitness in Trial",
            "Better Fitness Found Count",
            "Last Evaluation Improved",
        ],
    )
    df.set_index("Evaluation #", inplace=True)
    for i in range(1, NUM_EVALUATIONS + 1):
        (
            best_fitness_in_eval,
            best_fitness_in_trial,
            better_fitness_count,
            last_eval_improved,
        ) = file_lines[i]
        best_fitness_in_eval = float(best_fitness_in_eval.split(" ")[-1])
        best_fitness_in_trial = float(best_fitness_in_trial.split(" ")[-1])
        better_fitness_count = int(better_fitness_count.split(" ")[-1])
        last_eval_improved = int(last_eval_improved.split(" ")[-1])
        # Add the metrics to the DataFrame.
        df.loc[(len(df))] = [
            best_fitness_in_eval,
            best_fitness_in_trial,
            better_fitness_count,
            last_eval_improved,
        ]
    results.append(df)
    print(f"DataFrame for trial #{trial_num + 1} created.")

# Export the DataFrames to an Excel file, with a new sheet for each trial.
excel_file_name = file_name.split(".")[0] + ".xlsx"
with pd.ExcelWriter(excel_file_name) as writer:
    for trial_num, df in enumerate(results):
        df.to_excel(excel_writer=writer, sheet_name=f"Trial #{trial_num + 1}")
        print(f"Trial #{trial_num + 1} exported to Excel.")