Spaces:
Sleeping
Sleeping
| import os | |
| import json | |
| import sqlite3 | |
| import glob | |
| import pandas as pd | |
| import gradio as gr | |
| from datetime import datetime | |
| from typing import Dict, List | |
| # Directory to store SQLite results | |
| db_dir = "results/" | |
| def find_or_download_db(): | |
| """Check if SQLite .db files exist; if not, attempt to download from cloud storage.""" | |
| if not os.path.exists(db_dir): | |
| os.makedirs(db_dir) | |
| db_files = glob.glob(os.path.join(db_dir, "*.db")) | |
| # Ensure the random bot database exists | |
| if "results/random_None.db" not in db_files: | |
| raise FileNotFoundError("Please upload results for the random agent in a file named 'random_None.db'.") | |
| return db_files | |
| def extract_agent_info(filename: str): | |
| """Extract agent type and model name from the filename.""" | |
| base_name = os.path.basename(filename).replace(".db", "") | |
| parts = base_name.split("_", 1) | |
| if len(parts) == 2: | |
| agent_type, model_name = parts | |
| else: | |
| agent_type, model_name = parts[0], "Unknown" | |
| return agent_type, model_name | |
| def get_available_games() -> List[str]: | |
| """Extracts all unique game names from all SQLite databases and includes 'Total Performance'.""" | |
| db_files = find_or_download_db() | |
| game_names = set() | |
| for db_file in db_files: | |
| conn = sqlite3.connect(db_file) | |
| try: | |
| query = "SELECT DISTINCT game_name FROM moves" | |
| df = pd.read_sql_query(query, conn) | |
| game_names.update(df["game_name"].tolist()) | |
| except Exception: | |
| pass # Ignore errors if table doesn't exist | |
| finally: | |
| conn.close() | |
| game_list = sorted(game_names) if game_names else ["No Games Found"] | |
| game_list.insert(0, "Total Performance") # Ensure 'Total Performance' is always first | |
| return game_list | |
| def extract_leaderboard_stats(game_name: str) -> pd.DataFrame: | |
| """Extract and aggregate leaderboard stats from all SQLite databases.""" | |
| db_files = find_or_download_db() | |
| all_stats = [] | |
| for db_file in db_files: | |
| conn = sqlite3.connect(db_file) | |
| agent_type, model_name = extract_agent_info(db_file) | |
| if game_name == "Total Performance": | |
| query = "SELECT game_name, COUNT(DISTINCT episode) AS games_played, " \ | |
| "SUM(reward) AS total_rewards " \ | |
| "FROM game_results GROUP BY game_name" | |
| df = pd.read_sql_query(query, conn) | |
| else: | |
| query = "SELECT COUNT(DISTINCT episode) AS games_played, " \ | |
| "SUM(reward) AS total_rewards " \ | |
| "FROM game_results WHERE game_name = ?" | |
| df = pd.read_sql_query(query, conn, params=(game_name,)) | |
| # Fetch average generation time from moves table | |
| gen_time_query = """ | |
| SELECT AVG(generation_time) FROM moves WHERE game_name = ? | |
| """ | |
| avg_gen_time = conn.execute(gen_time_query, (game_name,)).fetchone()[0] or 0 | |
| # Calculate win rate against random bot | |
| vs_random_query = """ | |
| SELECT COUNT(*) FROM game_results gr | |
| JOIN moves m ON gr.game_name = m.game_name AND gr.episode = m.episode | |
| WHERE gr.game_name = ? AND m.opponent = 'random_None' AND gr.reward > 0 | |
| """ | |
| total_vs_random_query = """ | |
| SELECT COUNT(*) FROM game_results gr | |
| JOIN moves m ON gr.game_name = m.game_name AND gr.episode = m.episode | |
| WHERE gr.game_name = ? AND m.opponent = 'random_None' | |
| """ | |
| wins_vs_random = conn.execute(vs_random_query, (game_name,)).fetchone()[0] or 0 | |
| total_vs_random = conn.execute(total_vs_random_query, (game_name,)).fetchone()[0] or 0 | |
| vs_random_rate = (wins_vs_random / total_vs_random |