generate_gpt4_results.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "187869b5-b9ef-4143-ae78-a4dbe13d6429",
   "metadata": {},
   "outputs": [],
   "source": [
    "import functools\n",
    "import getpass\n",
    "import json\n",
    "import os\n",
    "from typing import List, Optional\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "# Settings\n",
    "os.environ[\"QUERY_OPENAI\"] = \"True\"  # if set to True, this generates a random result instead of prompting the LLM\n",
    "os.environ[\"OPENAI_MODEL\"] = \"gpt-4-0125-preview\"  # the model to use on the OpenAI API\n",
    "os.environ[\"OPENAI_N\"] = \"15\"  # the number of answers to generate per prompt\n",
    "os.environ[\"OPENAI_TEMPERATURE\"] = \"1.0\"  # the model temperature to use\n",
    "os.environ[\"OPENAI_TIMEOUT\"] = \"60\"  # the timeout for OpenAI API calls\n",
    "os.environ[\"TEMPLATE_DIR\"] = \"templates/\"\n",
    "os.environ[\"SQLITE_PATH\"] = \"gpt4_results.sqlite3\"  # the path to the SQLite database file. Set this to None to disable storage."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1af61b7c-a5f6-49d4-93a8-bf95123438d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# set the environment variables before loading fm_matcher, otherwise the settings above will be ignored\n",
    "from fm_matcher.utils import storage\n",
    "from fm_matcher.utils.models import Attribute, Parameters, Relation, Result\n",
    "from fm_matcher.utils.prompt_building import PromptDesign, build_prompts\n",
    "from fm_matcher.utils.prompt_sending import process_prompt_list\n",
    "from fm_matcher.utils.prompt_postprocessing import postprocess_answers\n",
    "\n",
    "all_templates = [f\"{mode}\" for mode in (\"oneToN\", \"nToOne\", \"nToN\")]\n",
    "all_modes = [PromptDesign.oneToN, PromptDesign.nToOne, PromptDesign.nToN]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2131a14c-9c49-4051-9afc-96de6c979763",
   "metadata": {},
   "outputs": [],
   "source": [
    "benchmark = pd.read_csv(\"benchmark/ground_truth.csv\")\n",
    "for side in (\"source\", \"target\"):\n",
    "    benchmark[[f\"{side}_schema\", f\"{side}_relation\", f\"{side}_attribute\"]] = benchmark[side].str.split(\".\", expand=True)\n",
    "    benchmark[side] = benchmark[side].str.lower()\n",
    "benchmark[\"benchmark\"] = True\n",
    "\n",
    "relation_combinations = benchmark[[\"source_relation\", \"target_relation\"]].drop_duplicates().values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2f237dbe-c7eb-4bad-8f73-bd50549d67d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "@functools.cache\n",
    "def get_description(schema: str, table: str, attribute: Optional[str] = None) -> str:\n",
    "    if attribute:\n",
    "        filename = f\"{schema}_{table}_{attribute}.txt\"\n",
    "    else:\n",
    "        filename = f\"{schema}_table_{table}.txt\"\n",
    "    filename_filter = lambda f: f.lower() == filename.lower()\n",
    "    filename = next(filter(filename_filter, os.listdir(\"schema_documentations\")))\n",
    "    with open(os.path.join(\"schema_documentations\", filename), \"r\") as desc_file:\n",
    "        description = desc_file.read()\n",
    "    return description\n",
    "\n",
    "\n",
    "@functools.cache\n",
    "def get_attributes(schema: str, table: str) -> List[str]:\n",
    "    table_filter = lambda f: f.lower().startswith(f\"{schema}_{table}_\") and f.endswith(\".txt\")\n",
    "    extract_attr_name = lambda f: f[len(f\"{schema}_{table}_\"):-len(\".txt\")]\n",
    "    return [\n",
    "        extract_attr_name(attr_file)\n",
    "        for attr_file in filter(table_filter, os.listdir(\"schema_documentations/\"))\n",
    "    ]\n",
    "\n",
    "\n",
    "@functools.cache\n",
    "def get_relation(schema: str, table: str) -> Relation:\n",
    "    schema, table = schema.lower(), table.lower()\n",
    "    description = get_description(schema, table)\n",
    "    attributes = [\n",
    "        Attribute(\n",
    "            name=attr_name.capitalize(),\n",
    "            description=get_description(schema, table, attr_name),\n",
    "        ) for attr_name in get_attributes(schema, table)\n",
    "    ]\n",
    "    return Relation(\n",
    "        name=table.capitalize(),\n",
    "        description=description,\n",
    "        attributes=attributes,\n",
    "    )\n",
    "\n",
    "\n",
    "parameters = [\n",
    "    Parameters(\n",
    "        source_relation=get_relation(\"mimic\", source),\n",
    "        target_relation=get_relation(\"omop\", target),\n",
    "    ) for source, target in relation_combinations\n",
    "]\n",
    "parameters = [storage.store_parameters(p) for p in parameters]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "836afe62-0b01-4a52-a57c-fdf500426643",
   "metadata": {},
   "outputs": [],
   "source": [
    "templates = {\n",
    "    \"oneToN\": [\n",
    "        {\n",
    "            \"role\": \"user\",\n",
    "            \"content\": (\n",
    "                \"Act as a schema matcher for relational schemas. Your task is to create semantic matches\"\n",
    "                \" that specify how the elements of the source schema and the target schema semantically\"\n",
    "                \" correspond to one another. Two attributes semantically match if and only if there exists\"\n",
    "                \" an invertible function that maps all values of one attribute to the other. First, I will\"\n",
    "                \" input the name of an attribute from the source schema, a description of the attribute,\"\n",
    "                \" the name of the relation in belongs to and a description of this relation. After that, I\"\n",
    "                \" will input the same information of a single relation and all its attributes from the\"\n",
    "                \" target schema.\"\n",
    "            )\n",
    "        },\n",
    "        {\n",
    "            \"role\": \"user\",\n",
    "            \"content\": (\n",
    "                \"The attribute from the source schema is the following:\\n\"\n",
    "                \"Attribute name: '{{source_attribute.name}}'\\n\"\n",
    "                \"Attribute description: '{{source_attribute.description}}'\\n\"\n",
    "                \"Relation name: '{{source_relation.name}}'\\n\"\n",
    "                \"Relation description: '{{source_relation.description}}'\"\n",
    "            )\n",
    "        },\n",
    "        {\n",
    "            \"role\": \"user\",\n",
    "            \"content\": (\n",
    "                \"The relation from the target schema is the following:\\n\"\n",
    "                \"Relation name: '{{target_relation.name}}'\\n\"\n",
    "                \"Relation description: '{{target_relation.description}}'\\n\"\n",
    "                \"In the following, I will list all attributes of '{{target_relation.name}}'.\"\n",
    "            )\n",
    "        },\n",
    "        {\n",
    "            \"role\": \"user\",\n",
    "            \"content\": (\n",
    "                \"Attribute name: '{{target_attribute.name}}'\\n\"\n",
    "                \"Attribute description: '{{target_attribute.description}}'\"\n",
    "            )\n",
    "        },\n",
    "        {\n",
    "            \"role\": \"user\",\n",
    "            \"content\": (\n",
    "                \"Explain which of the target attributes semantically match to '{{source_attribute.name}}'\"\n",
    "                \" from '{{source_relation.name}}' of the source schema. Lets work this out step by step to\"\n",
    "                \" make sure we get it correct. After your explanation, give a final decision\"\n",
    "                \" JSON-formatted like this: `{ \\\"yes\\\": [], \\\"no\\\": [] }`. Under each of the following\"\n",
    "                \" keys, list all target attributes of '{{target_relation.name}}' that apply: yes - if\"\n",
    "                \" there is an invertible function that maps all values of the source attribute to the\"\n",
    "                \" values of the target attribute; no - if there is no such function. Do not mention an\"\n",
    "                \" attribute if there is not enough information to decide.\"\n",
    "            )\n",
    "        }\n",
    "    ],\n",
    "    \"nToOne\": [\n",
    "        {\n",
    "            \"role\": \"user\",\n",
    "            \"content\": (\n",
    "                \"Act as a schema matcher for relational schemas. Your task is to create semantic matches\"\n",
    "                \" that specify how the elements of the source schema and the target schema semantically\"\n",
    "                \" correspond to one another. Two attributes semantically match if and only if there exists\"\n",
    "                \" an invertible function that maps all values of one attribute to the other. First, I will\"\n",
    "                \" input the name of a single relation from the source schema, the description of the\"\n",
    "                \" relation and the name and description of all its attributes. After that, I will input\"\n",
    "                \" the same information of a single relation and a single attribute from the target schema.\"\n",
    "            ),\n",
    "        },\n",
    "        {\n",
    "            \"role\": \"user\",\n",
    "            \"content\": (\n",
    "                \"The relation from the source schema is the following:\\n\"\n",
    "                \"Relation name: '{{source_relation.name}}'\\n\"\n",
    "                \"Relation description: '{{source_relation.description}}'\\n\"\n",
    "                \"In the following, I will list all attributes of '{{source_relation.name}}'.\"\n",
    "            ),\n",
    "        },\n",
    "        {\n",
    "            \"role\": \"user\",\n",
    "            \"content\": (\n",
    "                \"Attribute name: '{{source_attribute.name}}'\\n\"\n",
    "                \"Attribute description: '{{source_attribute.description}}'\"\n",
    "            ),\n",
    "        },\n",
    "        {\n",
    "            \"role\": \"user\",\n",
    "            \"content\": (\n",
    "                \"The attribute from the target schema is the following:\\n\"\n",
    "                \"Relation name: '{{target_relation.name}}'\\n\"\n",
    "                \"Relation description: '{{target_relation.description}}'\\n\"\n",
    "                \"Attribute name: '{{target_attribute.name}}'\\n\"\n",
    "                \"Attribute description: '{{target_attribute.description}}'\"\n",
    "            ),\n",
    "        },\n",
    "        {\n",
    "            \"role\": \"user\",\n",
    "            \"content\": (\n",
    "                \"Explain which of the source attributes semantically match to '{{target_attribute.name}}'\"\n",
    "                \" from '{{target_relation.name}}' of the target schema. Lets work this out step by step to\"\n",
    "                \" make sure we get it correct. After your explanation, give a final decision\"\n",
    "                \" JSON-formatted like this: `{ \\\"yes\\\": [], \\\"no\\\": [] }`. Under each of the following\"\n",
    "                \" keys, list all target attributes of '{{source_relation.name}}' that apply: yes - if\"\n",
    "                \" there is an invertible function that maps all values of the source attribute to the\"\n",
    "                \" values of the target attribute; no - if there is no such function. Do not mention an\"\n",
    "                \" attribute if there is not enough information to decide.\"\n",
    "            ),\n",
    "        }\n",
    "    ],\n",
    "    \"nToN\": [\n",
    "        {\n",
    "            \"role\": \"user\",\n",
    "            \"content\": (\n",
    "                \"Act as a schema matcher for relational schemas. Your task is to create semantic matches\"\n",
    "                \" that specify how the elements of the source schema and the target schema semantically\"\n",
    "                \" correspond to one another. Two attributes semantically match if and only if there exists\"\n",
    "                \" an invertible function that maps all values of one attribute to the other. First, I will\"\n",
    "                \" input the name of a single relation from the source schema, the description of the\"\n",
    "                \" relation and the name and description of all its attributes. After that, I will input\"\n",
    "                \" the same information of one relation and all its attribute from the target schema.\"\n",
    "            ),\n",
    "        },\n",
    "        {\n",
    "            \"role\": \"user\",\n",
    "            \"content\": (\n",
    "                \"The relation from the source schema is the following:\\n\"\n",
    "                \"Relation name: '{{source_relation.name}}'\\n\"\n",
    "                \"Relation description: '{{source_relation.description}}'\\n\"\n",
    "                \"In the following, I will list all attributes of '{{source_relation.name}}'.\"\n",
    "            ),\n",
    "        },\n",
    "        {\n",
    "            \"role\": \"user\",\n",
    "            \"content\": (\n",
    "                \"Attribute name: '{{source_attribute.name}}'\\n\"\n",
    "                \"Attribute description: '{{source_attribute.description}}'\"\n",
    "            ),\n",
    "        },\n",
    "        {\n",
    "            \"role\": \"user\",\n",
    "            \"content\": (\n",
    "                \"The relation from the target schema is the following:\\n\"\n",
    "                \"Relation name: '{{target_relation.name}}'\\n\"\n",
    "                \"Relation description: '{{target_relation.description}}'\\n\"\n",
    "                \"In the following, I will list all attributes of '{{target_relation.name}}'.\"\n",
    "            ),\n",
    "        },\n",
    "        {\n",
    "            \"role\": \"user\",\n",
    "            \"content\": (\n",
    "                \"Attribute name: '{{target_attribute.name}}'\\n\"\n",
    "                \"Attribute description: '{{target_attribute.description}}'\"\n",
    "            ),\n",
    "        },\n",
    "        {\n",
    "            \"role\": \"user\",\n",
    "            \"content\": (\n",
    "                \"Explain which pairs of attributes from the source and target schema semantically match.\"\n",
    "                \" Lets work this out step by step to make sure we get it correct. After your explanation,\"\n",
    "                \" give a final decision JSON-formatted like this: `{ \\\"yes\\\": [], \\\"no\\\": [] }`. List all\"\n",
    "                \" attribute pairs written as `<source attribute>,<target attribute>` under one the\"\n",
    "                \" following keys where they apply: yes - if there is an invertible function that maps all\"\n",
    "                \" values of the source attribute to the values of the target attribute; no - if there is\"\n",
    "                \" no such function. Do not mention an attribute pair if there is not enough information to\"\n",
    "                \" decide.\"\n",
    "            ),\n",
    "        }\n",
    "    ],\n",
    "}\n",
    "\n",
    "os.makedirs(os.getenv(\"TEMPLATE_DIR\"), exist_ok=True)\n",
    "for tpl_name in all_templates:\n",
    "    with open(os.path.join(os.getenv(\"TEMPLATE_DIR\"), f\"{tpl_name}.json\"), \"w\") as jsn:\n",
    "        json.dump(templates[tpl_name.split(\"_\")[-1]], jsn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9f4a68db-176e-4fdf-9a49-3986cfaca8ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "prompts = {\n",
    "    parameter.meta[\"path\"]: build_prompts(parameters=parameter, templates=all_templates, modes=all_modes)\n",
    "    for parameter in parameters\n",
    "}\n",
    "\n",
    "prompts = {\n",
    "    param_path: [storage.store_prompt(prompt) for prompt in prompt_list]\n",
    "    for param_path, prompt_list in prompts.items()\n",
    "}\n",
    "\n",
    "\n",
    "os.environ[\"OPENAI_API_KEY\"] = getpass.getpass(\"Your OpenAI API key please:\")\n",
    "results = {}\n",
    "for parameter in parameters:\n",
    "    answers = await process_prompt_list(parameter, prompts[parameter.meta[\"path\"]])\n",
    "    results[parameter.meta[\"path\"]] = storage.store_result(postprocess_answers(parameter, answers))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de7e5147-b006-4e74-a5d9-cd61f2725405",
   "metadata": {},
   "outputs": [],
   "source": [
    "def result_to_dataframe(result: Result) -> pd.DataFrame:\n",
    "    df_data = []\n",
    "    for attribute_pair, result_pair in result.pairs.items():\n",
    "        for vote in result_pair.votes:\n",
    "            left_scope = \"1\" if len(vote.answer.attributes.sources) == 1 else \"n\"\n",
    "            right_scope = \"1\" if len(vote.answer.attributes.targets) == 1 else \"n\"\n",
    "            task_scope = left_scope + \"-to-\" + right_scope\n",
    "            source = \"mimic.\" + result.parameters.source_relation.name + \".\" + attribute_pair.source.name\n",
    "            target = \"omop.\" + result.parameters.target_relation.name + \".\" + attribute_pair.target.name\n",
    "            df_data.append({\n",
    "                \"task_scope\": task_scope,\n",
    "                \"source\": source.lower(),\n",
    "                \"source_attribute\": attribute_pair.source.name,\n",
    "                \"source_relation\": result.parameters.source_relation.name,\n",
    "                \"target\": target.lower(),\n",
    "                \"target_attribute\": attribute_pair.target.name,\n",
    "                \"target_relation\": result.parameters.target_relation.name,\n",
    "                \"decision\": str(vote.vote),\n",
    "                \"decision_index\": vote.answer.index,\n",
    "            })\n",
    "    return pd.DataFrame(df_data)\n",
    "\n",
    "\n",
    "result_df = pd.DataFrame()\n",
    "for result in results.values():\n",
    "    result_df = pd.concat((result_df, result_to_dataframe(result)))\n",
    "\n",
    "result_df = result_df.merge(benchmark[[\"source\", \"target\", \"benchmark\"]], on=[\"source\", \"target\"], how=\"left\").copy()\n",
    "result_df[\"benchmark\"] = result_df[\"benchmark\"].fillna(False)\n",
    "result_df.to_csv(\"results/gpt4_results.csv\", index=False)\n",
    "result_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d656d8e8-1d30-4f7e-90ba-e77bc7add305",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}