Data Analysis¶

AgentSociety provides complete data storage capabilities, with all experimental data persisted to a database to support subsequent data analysis work. This document introduces how to extract experimental data from an SQLite database and convert it to pandas DataFrame for data analysis.

AgentSociety stores experimental data in an SQLite database (default path: ./agentsociety_data/sqlite.db).

Table Structure¶

The database contains the following tables:

Experiment Table (as_experiment)¶

Stores basic information and metadata of experiments:

tenant_id: Tenant ID
id: Experiment UUID
name: Experiment name
num_day: Total experiment days
status: Experiment status, 0 means experiment is preparing, 1 means experiment is running, 2 means experiment is completed, 3 means experiment has errors
cur_day: Current day
cur_t: Current time (unit: seconds), together with cur_day field determines the time in simulation
config: Experiment configuration (JSON format)
error: Error information
input_tokens: Number of input tokens
output_tokens: Number of output tokens
created_at: Creation time
updated_at: Update time

The main purpose of the experiment table is to help users find the ID of experiments to be analyzed in order to locate data tables.

Dynamically Generated Experiment Data Tables¶

Each experiment generates a set of data tables named after the experiment ID:

Table Naming Rules

The UUID format of the experiment ID is xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, where x is a hexadecimal digit. In the database, the UUID format of the experiment ID is converted to xxxxxxxx_xxxx_xxxx_xxxx_xxxxxxxxxxxx format to comply with SQLite table naming rules.

Agent Profile Table (as_{exp_id}_agent_profile)
- id: Agent ID
- name: Agent name
- profile: Agent profile information (JSON format)
Agent Status Table (as_{exp_id}_agent_status)
- id: Agent ID
- day: Experiment day
- t: Simulation time (unit: seconds), together with day field determines the time in simulation
- lng: Longitude
- lat: Latitude
- parent_id: ID of the lane, sidewalk, or AOI where the agent is located
- action: Current action
- status: Status information (JSON format)
- created_at: Record creation time
Dialogue Record Table (as_{exp_id}_agent_dialog)
- id: Agent ID
- day: Experiment day
- t: Simulation time (unit: seconds), together with day field determines the time in simulation
- type: Dialogue type, 0 represents inner thoughts, 1 represents dialogue with other agents, 2 represents dialogue with users
- speaker: Dialogue partner ID, not empty means the dialogue content is spoken by another agent, otherwise it is spoken by this agent
- content: Dialogue content
- created_at: Record creation time
Questionnaire Results Table (as_{exp_id}_agent_survey)
- id: Agent ID
- day: Experiment day
- t: Simulation time (unit: seconds), together with day field determines the time in simulation
- survey_id: Questionnaire ID
- result: Questionnaire results (JSON format)
- created_at: Record creation time
Global Prompt Table (as_{exp_id}_global_prompt)
- day: Experiment day
- t: Simulation time (unit: seconds), together with day field determines the time in simulation
- prompt: Prompt content
- created_at: Record creation time
Metrics Table (as_{exp_id}_metric)
- id: Record ID
- key: Metric name
- value: Metric value
- step: Metric step
- created_at: Record creation time

Database Access¶

This demonstrates using duckdb to connect to the database and read experimental data. In practice, other database connection tools such as sqlite3, psycopg2, etc. can also be used.

Installing duckdb

Use pip install duckdb to install duckdb

Reading Experiment Table to Get Experiment ID¶

First, connect to the database and query available experiments. Import the necessary libraries:

import duckdb
import pandas as pd
import uuid
from datetime import datetime

Connect to the SQLite database:

db_path = "./agentsociety_data/sqlite.db"
conn = duckdb.connect()
conn.execute(f"ATTACH '{db_path}' AS agentsociety (TYPE sqlite)")

Define a function to query all experiment information:

def get_experiments():
    query = """
    SELECT 
        tenant_id,
        id,
        name,
        num_day,
        status,
        cur_day,
        cur_t,
        created_at,
        updated_at,
        input_tokens,
        output_tokens
    FROM agentsociety.as_experiment
    ORDER BY created_at DESC
    """
    
    df_experiments = conn.execute(query).df()
    return df_experiments

Get the experiment list and select the experiment to analyze:

experiments_df = get_experiments()
print("Available experiments:")
print(experiments_df[['id', 'name', 'status', 'created_at']])

exp_id = experiments_df.iloc[0]['id']
print(f"Selected experiment: {exp_id}")

Reading Simulation Results Data by Experiment ID¶

After obtaining the experiment ID, all related data tables for that experiment can be read. First, define a data extraction function, noting that hyphens in the UUID need to be replaced with underscores to match the table naming rules:

def get_experiment_data(exp_id):
    table_suffix = str(exp_id).replace('-', '_')
    data = {}
    
    # TODO

    return data

The TODO parts are as follows:

Reading Agent Profile Data

try:
    query = f"""
    SELECT * FROM agentsociety.as_{table_suffix}_agent_profile
    """
    data['profiles'] = conn.execute(query).df()
    print(f"Agent profiles: {len(data['profiles'])} records")
except Exception as e:
    print(f"Failed to read agent profiles: {e}")
    data['profiles'] = pd.DataFrame()

Reading Agent Status Data

try:
    query = f"""
    SELECT * FROM agentsociety.as_{table_suffix}_agent_status
    ORDER BY day, t
    """
    data['statuses'] = conn.execute(query).df()
    print(f"Agent statuses: {len(data['statuses'])} records")
except Exception as e:
    print(f"Failed to read agent statuses: {e}")
    data['statuses'] = pd.DataFrame()

Reading Dialogue Record Data

try:
    query = f"""
    SELECT * FROM agentsociety.as_{table_suffix}_agent_dialog
    ORDER BY day, t
    """
    data['dialogs'] = conn.execute(query).df()
    print(f"Dialog records: {len(data['dialogs'])} records")
except Exception as e:
    print(f"Failed to read dialogs: {e}")
    data['dialogs'] = pd.DataFrame()

Reading Questionnaire Results Data

try:
    query = f"""
    SELECT * FROM agentsociety.as_{table_suffix}_agent_survey
    ORDER BY day, t
    """
    data['surveys'] = conn.execute(query).df()
    print(f"Survey results: {len(data['surveys'])} records")
except Exception as e:
    print(f"Failed to read surveys: {e}")
    data['surveys'] = pd.DataFrame()

Reading Global Prompt Data

try:
    query = f"""
    SELECT * FROM agentsociety.as_{table_suffix}_global_prompt
    ORDER BY day, t
    """
    data['global_prompts'] = conn.execute(query).df()
    print(f"Global prompts: {len(data['global_prompts'])} records")
except Exception as e:
    print(f"Failed to read global prompts: {e}")
    data['global_prompts'] = pd.DataFrame()

Reading Metrics Data

try:
    query = f"""
    SELECT * FROM agentsociety.as_{table_suffix}_metric
    ORDER BY step
    """
    data['metrics'] = conn.execute(query).df()
    print(f"Metrics: {len(data['metrics'])} records")
except Exception as e:
    print(f"Failed to read metrics: {e}")
    data['metrics'] = pd.DataFrame()

Get experimental data and view overview:

experiment_data = get_experiment_data(exp_id)

print("\n=== Data Overview ===")
for table_name, df in experiment_data.items():
    if not df.empty:
        print(f"{table_name}: {len(df)} records, {len(df.columns)} columns")
        if 'day' in df.columns:
            print(f"  Time range: {df['day'].min()} - {df['day'].max()} days")

Data Analysis¶

Now, all experimental data has been extracted into pandas DataFrames for convenient analysis. You can start data analysis according to your experimental needs!