test.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Merge all the trails"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import json\n",
    "\n",
    "def merge_csv_by_columns(folder_path, output_file):\n",
    "    # List all CSV files in the folder\n",
    "    csv_files = [file for file in os.listdir(folder_path) if file.endswith('.csv')]\n",
    "\n",
    "    # Initialize an empty list to store DataFrames\n",
    "    dataframes = []\n",
    "\n",
    "    # Read each CSV file and append it to the list\n",
    "    for file in csv_files:\n",
    "        df = pd.read_csv(os.path.join(folder_path, file))\n",
    "        dataframes.append(df)\n",
    "\n",
    "    # Merge all DataFrames based on common column names\n",
    "    merged_df = pd.concat(dataframes, ignore_index=True, sort=False)\n",
    "\n",
    "    # Remove rows where 'location' is empty\n",
    "    # merged_df = merged_df.dropna(subset=['Locations'])\n",
    "\n",
    "    # Remove duplicate rows\n",
    "    merged_df = merged_df.drop_duplicates()\n",
    "\n",
    "    # Save the merged DataFrame to a new CSV file\n",
    "    merged_df.to_csv(output_file, index=False)\n",
    "\n",
    "    return merged_df\n",
    "\n",
    "# Example usage\n",
    "folder_path = './trials/'\n",
    "output_file = 'merged_output.csv'\n",
    "clinical_trails_data = merge_csv_by_columns(folder_path, output_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NCT Number</th>\n",
       "      <th>Study Title</th>\n",
       "      <th>Study URL</th>\n",
       "      <th>Acronym</th>\n",
       "      <th>Study Status</th>\n",
       "      <th>Brief Summary</th>\n",
       "      <th>Study Results</th>\n",
       "      <th>Conditions</th>\n",
       "      <th>Interventions</th>\n",
       "      <th>Primary Outcome Measures</th>\n",
       "      <th>...</th>\n",
       "      <th>Study Design</th>\n",
       "      <th>Other IDs</th>\n",
       "      <th>Start Date</th>\n",
       "      <th>Primary Completion Date</th>\n",
       "      <th>Completion Date</th>\n",
       "      <th>First Posted</th>\n",
       "      <th>Results First Posted</th>\n",
       "      <th>Last Update Posted</th>\n",
       "      <th>Locations</th>\n",
       "      <th>Study Documents</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NCT03334747</td>\n",
       "      <td>Safety of KAE609 in Adults With Uncomplicated ...</td>\n",
       "      <td>https://clinicaltrials.gov/study/NCT03334747</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>KAE609 will be evaluated primarily for hepatic...</td>\n",
       "      <td>YES</td>\n",
       "      <td>Malaria</td>\n",
       "      <td>DRUG: KAE609|DRUG: Coartem</td>\n",
       "      <td>Number of Participants With at Least 2 CTCAE G...</td>\n",
       "      <td>...</td>\n",
       "      <td>Allocation: RANDOMIZED|Intervention Model: PAR...</td>\n",
       "      <td>CKAE609A2202|207813/Z/17/Z</td>\n",
       "      <td>2017-11-16</td>\n",
       "      <td>2019-11-23</td>\n",
       "      <td>2019-11-23</td>\n",
       "      <td>2017-11-07</td>\n",
       "      <td>2020-09-03</td>\n",
       "      <td>2021-10-11</td>\n",
       "      <td>Novartis Investigative Site, Lambarene, Gabon|...</td>\n",
       "      <td>Study Protocol, https://storage.googleapis.com...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NCT01190202</td>\n",
       "      <td>Epidemiology Study of Malaria Transmission Int...</td>\n",
       "      <td>https://clinicaltrials.gov/study/NCT01190202</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>The aim of this epidemiology study is to chara...</td>\n",
       "      <td>YES</td>\n",
       "      <td>Malaria|Malaria Vaccines</td>\n",
       "      <td>PROCEDURE: Blood sampling|PROCEDURE: Assessmen...</td>\n",
       "      <td>Number of Subjects With Plasmodium Falciparum ...</td>\n",
       "      <td>...</td>\n",
       "      <td>Allocation: NA|Intervention Model: SINGLE_GROU...</td>\n",
       "      <td>114001</td>\n",
       "      <td>2011-03-14</td>\n",
       "      <td>2013-12-20</td>\n",
       "      <td>2013-12-20</td>\n",
       "      <td>2010-08-27</td>\n",
       "      <td>2017-09-29</td>\n",
       "      <td>2020-08-18</td>\n",
       "      <td>GSK Investigational Site, Ouagadougou 01, Burk...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NCT01955382</td>\n",
       "      <td>Evaluation of Oral Activated Charcoal on Antim...</td>\n",
       "      <td>https://clinicaltrials.gov/study/NCT01955382</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>Background:\\n\\n- Malaria is caused by small pa...</td>\n",
       "      <td>YES</td>\n",
       "      <td>Malaria|Severe Malaria</td>\n",
       "      <td>DRUG: Actidose Aqua|DRUG: Artesunate|DRUG: Amo...</td>\n",
       "      <td>Parasite Clearance Half-life, To compare paras...</td>\n",
       "      <td>...</td>\n",
       "      <td>Allocation: RANDOMIZED|Intervention Model: PAR...</td>\n",
       "      <td>999913209|13-I-N209|NCT01955382</td>\n",
       "      <td>2013-09</td>\n",
       "      <td>2015-07</td>\n",
       "      <td>2015-07</td>\n",
       "      <td>2013-10-07</td>\n",
       "      <td>2017-04-20</td>\n",
       "      <td>2018-02-06</td>\n",
       "      <td>Universite des Sciencies, Techniques et Techno...</td>\n",
       "      <td>Study Protocol and Statistical Analysis Plan, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NCT01465048</td>\n",
       "      <td>Optimisation of Controlled Human Malaria Infec...</td>\n",
       "      <td>https://clinicaltrials.gov/study/NCT01465048</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>This is an open label, human pilot study to op...</td>\n",
       "      <td>YES</td>\n",
       "      <td>Malaria|Plasmodium Falciparum</td>\n",
       "      <td>BIOLOGICAL: Plasmodium falciparum sporozoites ...</td>\n",
       "      <td>Number of Participants Infected, To determine ...</td>\n",
       "      <td>...</td>\n",
       "      <td>Allocation: NON_RANDOMIZED|Intervention Model:...</td>\n",
       "      <td>VAC049</td>\n",
       "      <td>2011-10</td>\n",
       "      <td>2012-02</td>\n",
       "      <td>2013-02</td>\n",
       "      <td>2011-11-04</td>\n",
       "      <td>2013-04-08</td>\n",
       "      <td>2013-06-24</td>\n",
       "      <td>Centre for Clinical Vaccinology and Tropical M...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NCT02458092</td>\n",
       "      <td>Evaluate the Safety and Efficacy of Plasmodium...</td>\n",
       "      <td>https://clinicaltrials.gov/study/NCT02458092</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>The purpose of this study is to determine whet...</td>\n",
       "      <td>YES</td>\n",
       "      <td>Malaria</td>\n",
       "      <td>BIOLOGICAL: Plasmodium falciparum Malaria Prot...</td>\n",
       "      <td>Number of Participants With Solicited Adverse ...</td>\n",
       "      <td>...</td>\n",
       "      <td>Allocation: RANDOMIZED|Intervention Model: PAR...</td>\n",
       "      <td>A-14620.b|WRAIR 1417</td>\n",
       "      <td>2008-04</td>\n",
       "      <td>2008-12</td>\n",
       "      <td>2009-06</td>\n",
       "      <td>2015-05-29</td>\n",
       "      <td>2018-11-07</td>\n",
       "      <td>2023-06-28</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    NCT Number                                        Study Title  \\\n",
       "0  NCT03334747  Safety of KAE609 in Adults With Uncomplicated ...   \n",
       "1  NCT01190202  Epidemiology Study of Malaria Transmission Int...   \n",
       "2  NCT01955382  Evaluation of Oral Activated Charcoal on Antim...   \n",
       "3  NCT01465048  Optimisation of Controlled Human Malaria Infec...   \n",
       "4  NCT02458092  Evaluate the Safety and Efficacy of Plasmodium...   \n",
       "\n",
       "                                      Study URL Acronym Study Status  \\\n",
       "0  https://clinicaltrials.gov/study/NCT03334747     NaN    COMPLETED   \n",
       "1  https://clinicaltrials.gov/study/NCT01190202     NaN    COMPLETED   \n",
       "2  https://clinicaltrials.gov/study/NCT01955382     NaN    COMPLETED   \n",
       "3  https://clinicaltrials.gov/study/NCT01465048     NaN    COMPLETED   \n",
       "4  https://clinicaltrials.gov/study/NCT02458092     NaN    COMPLETED   \n",
       "\n",
       "                                       Brief Summary Study Results  \\\n",
       "0  KAE609 will be evaluated primarily for hepatic...           YES   \n",
       "1  The aim of this epidemiology study is to chara...           YES   \n",
       "2  Background:\\n\\n- Malaria is caused by small pa...           YES   \n",
       "3  This is an open label, human pilot study to op...           YES   \n",
       "4  The purpose of this study is to determine whet...           YES   \n",
       "\n",
       "                      Conditions  \\\n",
       "0                        Malaria   \n",
       "1       Malaria|Malaria Vaccines   \n",
       "2         Malaria|Severe Malaria   \n",
       "3  Malaria|Plasmodium Falciparum   \n",
       "4                        Malaria   \n",
       "\n",
       "                                       Interventions  \\\n",
       "0                         DRUG: KAE609|DRUG: Coartem   \n",
       "1  PROCEDURE: Blood sampling|PROCEDURE: Assessmen...   \n",
       "2  DRUG: Actidose Aqua|DRUG: Artesunate|DRUG: Amo...   \n",
       "3  BIOLOGICAL: Plasmodium falciparum sporozoites ...   \n",
       "4  BIOLOGICAL: Plasmodium falciparum Malaria Prot...   \n",
       "\n",
       "                            Primary Outcome Measures  ...  \\\n",
       "0  Number of Participants With at Least 2 CTCAE G...  ...   \n",
       "1  Number of Subjects With Plasmodium Falciparum ...  ...   \n",
       "2  Parasite Clearance Half-life, To compare paras...  ...   \n",
       "3  Number of Participants Infected, To determine ...  ...   \n",
       "4  Number of Participants With Solicited Adverse ...  ...   \n",
       "\n",
       "                                        Study Design  \\\n",
       "0  Allocation: RANDOMIZED|Intervention Model: PAR...   \n",
       "1  Allocation: NA|Intervention Model: SINGLE_GROU...   \n",
       "2  Allocation: RANDOMIZED|Intervention Model: PAR...   \n",
       "3  Allocation: NON_RANDOMIZED|Intervention Model:...   \n",
       "4  Allocation: RANDOMIZED|Intervention Model: PAR...   \n",
       "\n",
       "                         Other IDs  Start Date Primary Completion Date  \\\n",
       "0       CKAE609A2202|207813/Z/17/Z  2017-11-16              2019-11-23   \n",
       "1                           114001  2011-03-14              2013-12-20   \n",
       "2  999913209|13-I-N209|NCT01955382     2013-09                 2015-07   \n",
       "3                           VAC049     2011-10                 2012-02   \n",
       "4             A-14620.b|WRAIR 1417     2008-04                 2008-12   \n",
       "\n",
       "  Completion Date First Posted Results First Posted  Last Update Posted  \\\n",
       "0      2019-11-23   2017-11-07           2020-09-03          2021-10-11   \n",
       "1      2013-12-20   2010-08-27           2017-09-29          2020-08-18   \n",
       "2         2015-07   2013-10-07           2017-04-20          2018-02-06   \n",
       "3         2013-02   2011-11-04           2013-04-08          2013-06-24   \n",
       "4         2009-06   2015-05-29           2018-11-07          2023-06-28   \n",
       "\n",
       "                                           Locations  \\\n",
       "0  Novartis Investigative Site, Lambarene, Gabon|...   \n",
       "1  GSK Investigational Site, Ouagadougou 01, Burk...   \n",
       "2  Universite des Sciencies, Techniques et Techno...   \n",
       "3  Centre for Clinical Vaccinology and Tropical M...   \n",
       "4                                                NaN   \n",
       "\n",
       "                                     Study Documents  \n",
       "0  Study Protocol, https://storage.googleapis.com...  \n",
       "1                                                NaN  \n",
       "2  Study Protocol and Statistical Analysis Plan, ...  \n",
       "3                                                NaN  \n",
       "4                                                NaN  \n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clinical_trails_data.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Preprocess the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def preprocess(df):\n",
    "    for col in df.columns:\n",
    "        if df[col].dtype == object:\n",
    "            df[col] = df[col].str.lower()\n",
    "        df[col] = df[col].fillna('')\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NCT Number</th>\n",
       "      <th>Study Title</th>\n",
       "      <th>Study URL</th>\n",
       "      <th>Acronym</th>\n",
       "      <th>Study Status</th>\n",
       "      <th>Brief Summary</th>\n",
       "      <th>Study Results</th>\n",
       "      <th>Conditions</th>\n",
       "      <th>Interventions</th>\n",
       "      <th>Primary Outcome Measures</th>\n",
       "      <th>...</th>\n",
       "      <th>Study Design</th>\n",
       "      <th>Other IDs</th>\n",
       "      <th>Start Date</th>\n",
       "      <th>Primary Completion Date</th>\n",
       "      <th>Completion Date</th>\n",
       "      <th>First Posted</th>\n",
       "      <th>Results First Posted</th>\n",
       "      <th>Last Update Posted</th>\n",
       "      <th>Locations</th>\n",
       "      <th>Study Documents</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>nct03334747</td>\n",
       "      <td>safety of kae609 in adults with uncomplicated ...</td>\n",
       "      <td>https://clinicaltrials.gov/study/nct03334747</td>\n",
       "      <td></td>\n",
       "      <td>completed</td>\n",
       "      <td>kae609 will be evaluated primarily for hepatic...</td>\n",
       "      <td>yes</td>\n",
       "      <td>malaria</td>\n",
       "      <td>drug: kae609|drug: coartem</td>\n",
       "      <td>number of participants with at least 2 ctcae g...</td>\n",
       "      <td>...</td>\n",
       "      <td>allocation: randomized|intervention model: par...</td>\n",
       "      <td>ckae609a2202|207813/z/17/z</td>\n",
       "      <td>2017-11-16</td>\n",
       "      <td>2019-11-23</td>\n",
       "      <td>2019-11-23</td>\n",
       "      <td>2017-11-07</td>\n",
       "      <td>2020-09-03</td>\n",
       "      <td>2021-10-11</td>\n",
       "      <td>novartis investigative site, lambarene, gabon|...</td>\n",
       "      <td>study protocol, https://storage.googleapis.com...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>nct01190202</td>\n",
       "      <td>epidemiology study of malaria transmission int...</td>\n",
       "      <td>https://clinicaltrials.gov/study/nct01190202</td>\n",
       "      <td></td>\n",
       "      <td>completed</td>\n",
       "      <td>the aim of this epidemiology study is to chara...</td>\n",
       "      <td>yes</td>\n",
       "      <td>malaria|malaria vaccines</td>\n",
       "      <td>procedure: blood sampling|procedure: assessmen...</td>\n",
       "      <td>number of subjects with plasmodium falciparum ...</td>\n",
       "      <td>...</td>\n",
       "      <td>allocation: na|intervention model: single_grou...</td>\n",
       "      <td>114001</td>\n",
       "      <td>2011-03-14</td>\n",
       "      <td>2013-12-20</td>\n",
       "      <td>2013-12-20</td>\n",
       "      <td>2010-08-27</td>\n",
       "      <td>2017-09-29</td>\n",
       "      <td>2020-08-18</td>\n",
       "      <td>gsk investigational site, ouagadougou 01, burk...</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>nct01955382</td>\n",
       "      <td>evaluation of oral activated charcoal on antim...</td>\n",
       "      <td>https://clinicaltrials.gov/study/nct01955382</td>\n",
       "      <td></td>\n",
       "      <td>completed</td>\n",
       "      <td>background:\\n\\n- malaria is caused by small pa...</td>\n",
       "      <td>yes</td>\n",
       "      <td>malaria|severe malaria</td>\n",
       "      <td>drug: actidose aqua|drug: artesunate|drug: amo...</td>\n",
       "      <td>parasite clearance half-life, to compare paras...</td>\n",
       "      <td>...</td>\n",
       "      <td>allocation: randomized|intervention model: par...</td>\n",
       "      <td>999913209|13-i-n209|nct01955382</td>\n",
       "      <td>2013-09</td>\n",
       "      <td>2015-07</td>\n",
       "      <td>2015-07</td>\n",
       "      <td>2013-10-07</td>\n",
       "      <td>2017-04-20</td>\n",
       "      <td>2018-02-06</td>\n",
       "      <td>universite des sciencies, techniques et techno...</td>\n",
       "      <td>study protocol and statistical analysis plan, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>nct01465048</td>\n",
       "      <td>optimisation of controlled human malaria infec...</td>\n",
       "      <td>https://clinicaltrials.gov/study/nct01465048</td>\n",
       "      <td></td>\n",
       "      <td>completed</td>\n",
       "      <td>this is an open label, human pilot study to op...</td>\n",
       "      <td>yes</td>\n",
       "      <td>malaria|plasmodium falciparum</td>\n",
       "      <td>biological: plasmodium falciparum sporozoites ...</td>\n",
       "      <td>number of participants infected, to determine ...</td>\n",
       "      <td>...</td>\n",
       "      <td>allocation: non_randomized|intervention model:...</td>\n",
       "      <td>vac049</td>\n",
       "      <td>2011-10</td>\n",
       "      <td>2012-02</td>\n",
       "      <td>2013-02</td>\n",
       "      <td>2011-11-04</td>\n",
       "      <td>2013-04-08</td>\n",
       "      <td>2013-06-24</td>\n",
       "      <td>centre for clinical vaccinology and tropical m...</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>nct02458092</td>\n",
       "      <td>evaluate the safety and efficacy of plasmodium...</td>\n",
       "      <td>https://clinicaltrials.gov/study/nct02458092</td>\n",
       "      <td></td>\n",
       "      <td>completed</td>\n",
       "      <td>the purpose of this study is to determine whet...</td>\n",
       "      <td>yes</td>\n",
       "      <td>malaria</td>\n",
       "      <td>biological: plasmodium falciparum malaria prot...</td>\n",
       "      <td>number of participants with solicited adverse ...</td>\n",
       "      <td>...</td>\n",
       "      <td>allocation: randomized|intervention model: par...</td>\n",
       "      <td>a-14620.b|wrair 1417</td>\n",
       "      <td>2008-04</td>\n",
       "      <td>2008-12</td>\n",
       "      <td>2009-06</td>\n",
       "      <td>2015-05-29</td>\n",
       "      <td>2018-11-07</td>\n",
       "      <td>2023-06-28</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    NCT Number                                        Study Title  \\\n",
       "0  nct03334747  safety of kae609 in adults with uncomplicated ...   \n",
       "1  nct01190202  epidemiology study of malaria transmission int...   \n",
       "2  nct01955382  evaluation of oral activated charcoal on antim...   \n",
       "3  nct01465048  optimisation of controlled human malaria infec...   \n",
       "4  nct02458092  evaluate the safety and efficacy of plasmodium...   \n",
       "\n",
       "                                      Study URL Acronym Study Status  \\\n",
       "0  https://clinicaltrials.gov/study/nct03334747            completed   \n",
       "1  https://clinicaltrials.gov/study/nct01190202            completed   \n",
       "2  https://clinicaltrials.gov/study/nct01955382            completed   \n",
       "3  https://clinicaltrials.gov/study/nct01465048            completed   \n",
       "4  https://clinicaltrials.gov/study/nct02458092            completed   \n",
       "\n",
       "                                       Brief Summary Study Results  \\\n",
       "0  kae609 will be evaluated primarily for hepatic...           yes   \n",
       "1  the aim of this epidemiology study is to chara...           yes   \n",
       "2  background:\\n\\n- malaria is caused by small pa...           yes   \n",
       "3  this is an open label, human pilot study to op...           yes   \n",
       "4  the purpose of this study is to determine whet...           yes   \n",
       "\n",
       "                      Conditions  \\\n",
       "0                        malaria   \n",
       "1       malaria|malaria vaccines   \n",
       "2         malaria|severe malaria   \n",
       "3  malaria|plasmodium falciparum   \n",
       "4                        malaria   \n",
       "\n",
       "                                       Interventions  \\\n",
       "0                         drug: kae609|drug: coartem   \n",
       "1  procedure: blood sampling|procedure: assessmen...   \n",
       "2  drug: actidose aqua|drug: artesunate|drug: amo...   \n",
       "3  biological: plasmodium falciparum sporozoites ...   \n",
       "4  biological: plasmodium falciparum malaria prot...   \n",
       "\n",
       "                            Primary Outcome Measures  ...  \\\n",
       "0  number of participants with at least 2 ctcae g...  ...   \n",
       "1  number of subjects with plasmodium falciparum ...  ...   \n",
       "2  parasite clearance half-life, to compare paras...  ...   \n",
       "3  number of participants infected, to determine ...  ...   \n",
       "4  number of participants with solicited adverse ...  ...   \n",
       "\n",
       "                                        Study Design  \\\n",
       "0  allocation: randomized|intervention model: par...   \n",
       "1  allocation: na|intervention model: single_grou...   \n",
       "2  allocation: randomized|intervention model: par...   \n",
       "3  allocation: non_randomized|intervention model:...   \n",
       "4  allocation: randomized|intervention model: par...   \n",
       "\n",
       "                         Other IDs  Start Date Primary Completion Date  \\\n",
       "0       ckae609a2202|207813/z/17/z  2017-11-16              2019-11-23   \n",
       "1                           114001  2011-03-14              2013-12-20   \n",
       "2  999913209|13-i-n209|nct01955382     2013-09                 2015-07   \n",
       "3                           vac049     2011-10                 2012-02   \n",
       "4             a-14620.b|wrair 1417     2008-04                 2008-12   \n",
       "\n",
       "  Completion Date First Posted Results First Posted  Last Update Posted  \\\n",
       "0      2019-11-23   2017-11-07           2020-09-03          2021-10-11   \n",
       "1      2013-12-20   2010-08-27           2017-09-29          2020-08-18   \n",
       "2         2015-07   2013-10-07           2017-04-20          2018-02-06   \n",
       "3         2013-02   2011-11-04           2013-04-08          2013-06-24   \n",
       "4         2009-06   2015-05-29           2018-11-07          2023-06-28   \n",
       "\n",
       "                                           Locations  \\\n",
       "0  novartis investigative site, lambarene, gabon|...   \n",
       "1  gsk investigational site, ouagadougou 01, burk...   \n",
       "2  universite des sciencies, techniques et techno...   \n",
       "3  centre for clinical vaccinology and tropical m...   \n",
       "4                                                      \n",
       "\n",
       "                                     Study Documents  \n",
       "0  study protocol, https://storage.googleapis.com...  \n",
       "1                                                     \n",
       "2  study protocol and statistical analysis plan, ...  \n",
       "3                                                     \n",
       "4                                                     \n",
       "\n",
       "[5 rows x 30 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "file_path = r'Data/merged_output.csv'\n",
    "df_clinical = pd.read_csv(file_path)\n",
    "preprocessed_trials = preprocess(df_clinical)\n",
    "\n",
    "preprocessed_trials.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Convert the CSV data to a JSON-like structure (Updated Code)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[\n",
      "    {\n",
      "        \"Trial\": {\n",
      "            \"Trial ID\": \"nct03334747\",\n",
      "            \"Title\": \"safety of kae609 in adults with uncomplicated plasmodium falciparum malaria.\",\n",
      "            \"URL\": \"https://clinicaltrials.gov/study/nct03334747\",\n",
      "            \"Status\": \"completed\",\n",
      "            \"Brief Summary\": \"kae609 will be evaluated primarily for hepatic safety of single and multiple doses in sequential cohorts with increasing doses.this study aims to determine the maximum safe dose of the investigational drug kae609 in malaria patients.\",\n",
      "            \"Phase\": \"phase2\",\n",
      "            \"Study Start Date\": \"2017-11-16\",\n",
      "            \"Expected Completion Date\": \"2019-11-23\"\n",
      "        },\n",
      "        \"Drug/Intervention\": {\n",
      "            \"Drug\": [\n",
      "                \"kae609\",\n",
      "                \"coartem\"\n",
      "            ],\n",
      "            \"Procedure\": [],\n",
      "            \"Device\": [],\n",
      "            \"Behavioral\": [],\n",
      "            \"Combination_product\": [],\n",
      "            \"Biological\": [],\n",
      "            \"Radiation\": [],\n",
      "            \"Other\": []\n",
      "        },\n",
      "        \"Disease/Condition\": {\n",
      "            \"Disease Name\": [\n",
      "                \"malaria\"\n",
      "            ]\n",
      "        },\n",
      "        \"Locations\": [\n",
      "            {\n",
      "                \"Institution\": \"novartis investigative site\",\n",
      "                \"City\": \"lambarene\",\n",
      "                \"Country\": \"gabon\"\n",
      "            },\n",
      "            {\n",
      "                \"Institution\": \"novartis investigative site\",\n",
      "                \"City\": \"kintampo\",\n",
      "                \"Country\": \"ghana\"\n",
      "            },\n",
      "            {\n",
      "                \"Institution\": \"novartis investigative site\",\n",
      "                \"City\": \"navrango\",\n",
      "                \"Country\": \"ghana\"\n",
      "            },\n",
      "            {\n",
      "                \"Institution\": \"novartis investigative site\",\n",
      "                \"City\": \"bamako\",\n",
      "                \"Country\": \"mali\"\n",
      "            },\n",
      "            {\n",
      "                \"Institution\": \"novartis investigative site\",\n",
      "                \"City\": \"sotuba\",\n",
      "                \"Country\": \"mali\"\n",
      "            },\n",
      "            {\n",
      "                \"Institution\": \"novartis investigative site\",\n",
      "                \"City\": \"kigali\",\n",
      "                \"Country\": \"rwanda\"\n",
      "            },\n",
      "            {\n",
      "                \"Institution\": \"novartis investigative site\",\n",
      "                \"City\": \"bushenyi\",\n",
      "                \"Country\": \"uganda\"\n",
      "            },\n",
      "            {\n",
      "                \"Institution\": \"novartis investigative site\",\n",
      "                \"City\": \"kampala\",\n",
      "                \"Country\": \"uganda\"\n",
      "            },\n",
      "            {\n",
      "                \"Institution\": \"novartis investigative site\",\n",
      "                \"City\": \"tororo\",\n",
      "                \"Country\": \"uganda\"\n",
      "            }\n",
      "        ]\n",
      "    },\n",
      "    {\n",
      "        \"Trial\": {\n",
      "            \"Trial ID\": \"nct01190202\",\n",
      "            \"Title\": \"epidemiology study of malaria transmission intensity in africa\",\n",
      "            \"URL\": \"https://clinicaltrials.gov/study/nct01190202\",\n",
      "            \"Status\": \"completed\",\n",
      "            \"Brief Summary\": \"the aim of this epidemiology study is to characterize in a standardized way malaria transmission intensity at the clinical trial centers participating in study 110021 (nct00866619).\",\n",
      "            \"Phase\": \"\",\n",
      "            \"Study Start Date\": \"2011-03-14\",\n",
      "            \"Expected Completion Date\": \"2013-12-20\"\n",
      "        },\n",
      "        \"Drug/Intervention\": {\n",
      "            \"Drug\": [],\n",
      "            \"Procedure\": [\n",
      "                \"blood sampling\",\n",
      "                \"assessment of body temperature\"\n",
      "            ],\n",
      "            \"Device\": [],\n",
      "            \"Behavioral\": [],\n",
      "            \"Combination_product\": [],\n",
      "            \"Biological\": [],\n",
      "            \"Radiation\": [],\n",
      "            \"Other\": []\n",
      "        },\n",
      "        \"Disease/Condition\": {\n",
      "            \"Disease Name\": [\n",
      "                \"malaria\",\n",
      "                \"malaria vaccines\"\n",
      "            ]\n",
      "        },\n",
      "        \"Locations\": [\n",
      "            {\n",
      "                \"Institution\": \"gsk investigational site\",\n",
      "                \"City\": \"ouagadougou 01\",\n",
      "                \"Country\": \"burkina faso\"\n",
      "            },\n",
      "            {\n",
      "                \"Institution\": \"gsk investigational site\",\n",
      "                \"City\": \"lambarn\",\n",
      "                \"Country\": \"gabon\"\n",
      "            },\n",
      "            {\n",
      "                \"Institution\": \"gsk investigational site\",\n",
      "                \"City\": \"kintampo\",\n",
      "                \"Country\": \"ghana\"\n",
      "            },\n",
      "            {\n",
      "                \"Institution\": \"gsk investigational site\",\n",
      "                \"City\": \"kumasi\",\n",
      "                \"Country\": \"ghana\"\n",
      "            },\n",
      "            {\n",
      "                \"Institution\": \"gsk investigational site\",\n",
      "                \"City\": \"kisumu\",\n",
      "                \"Country\": \"kenya\"\n",
      "            },\n",
      "            {\n",
      "                \"Institution\": \"gsk investigational site\",\n",
      "                \"City\": \"lilongwe\",\n",
      "                \"Country\": \"malawi\"\n",
      "            },\n",
      "            {\n",
      "                \"Institution\": \"gsk investigational site\",\n",
      "                \"City\": \"dar-es-salaam\",\n",
      "                \"Country\": \"tanzania\"\n",
      "            },\n",
      "            {\n",
      "                \"Institution\": \"gsk investigational site\",\n",
      "                \"City\": \"korogwe\",\n",
      "                \"Country\": \"tanzania\"\n",
      "            }\n",
      "        ]\n",
      "    },\n",
      "    {\n",
      "        \"Trial\": {\n",
      "            \"Trial ID\": \"nct01955382\",\n",
      "            \"Title\": \"evaluation of oral activated charcoal on antimalarial drug's ability to kill parasites in malian children with malaria\",\n",
      "            \"URL\": \"https://clinicaltrials.gov/study/nct01955382\",\n",
      "            \"Status\": \"completed\",\n",
      "            \"Brief Summary\": \"background:\\n\\n- malaria is caused by small parasites carried by some mosquitoes. people can get malaria if an infected mosquito bites them. malaria destroys red blood cells. most malaria is mild, but some children develop severe malaria, which kills about 660,000 people annually. about 9 in 10 who die of malaria are sub-saharan african children, most under 5 years old. scientists can save many lives if they find out how to prevent or relieve severe malaria.\\n\\nobjective:\\n\\n- to know if a common medicine called activated charcoal can reduce severe malaria symptoms.\\n\\neligibility:\\n\\n- children 2 to 11 years old with mild malaria who live in kenieroba, mali.\\n\\ndesign:\\n\\n* for the first 2 days and nights, participants will stay in the hospital.\\n* they will have their medical history taken, and a physical exam.\\n* blood will be drawn from a thin tube inserted in their hand or forearm. this will be done 3 times overall. a drop of blood will be taken from a finger prick 12 times overall.\\n* an antimalarial drug will be injected into the tube in the arm 4 times. each time the drug is given, participants will drink a small cup of either water or activated charcoal.\\n* for the following 3 days, participants will take an antimalarial pill.\\n* on day 7, participants will visit the hospital. a drop of blood from a finger prick will be tested for malaria parasites.\",\n",
      "            \"Phase\": \"phase2\",\n",
      "            \"Study Start Date\": \"2013-09\",\n",
      "            \"Expected Completion Date\": \"2015-07\"\n",
      "        },\n",
      "        \"Drug/Intervention\": {\n",
      "            \"Drug\": [\n",
      "                \"actidose aqua\",\n",
      "                \"artesunate\",\n",
      "                \"amodiaquine\"\n",
      "            ],\n",
      "            \"Procedure\": [],\n",
      "            \"Device\": [],\n",
      "            \"Behavioral\": [],\n",
      "            \"Combination_product\": [],\n",
      "            \"Biological\": [],\n",
      "            \"Radiation\": [],\n",
      "            \"Other\": []\n",
      "        },\n",
      "        \"Disease/Condition\": {\n",
      "            \"Disease Name\": [\n",
      "                \"malaria\",\n",
      "                \"severe malaria\"\n",
      "            ]\n",
      "        },\n",
      "        \"Locations\": [\n",
      "            {\n",
      "                \"Institution\": \"universite des sciencies\",\n",
      "                \"City\": \"techniques et technologies de bamako\",\n",
      "                \"Country\": \"mali\"\n",
      "            }\n",
      "        ]\n",
      "    },\n",
      "    {\n",
      "        \"Trial\": {\n",
      "            \"Trial ID\": \"nct01465048\",\n",
      "            \"Title\": \"optimisation of controlled human malaria infection using sporozoites administered by needle and syringe\",\n",
      "            \"URL\": \"https://clinicaltrials.gov/study/nct01465048\",\n",
      "            \"Status\": \"completed\",\n",
      "            \"Brief Summary\": \"this is an open label, human pilot study to optimise controlled human malaria infection (chmi) administered by plasmodium falciparum sporozoites (pfspz. volunteers will be inoculated with pfspz challenge. the route of administration and dose will vary in order to identify the optimal regimen that achieves the greatest infection rate in volunteers with plasmodium falciparum. all volunteers recruited will be healthy adults aged between 18 and 45 years. safety and infectivity data will be collected for each of the regimens.\",\n",
      "            \"Phase\": \"\",\n",
      "            \"Study Start Date\": \"2011-10\",\n",
      "            \"Expected Completion Date\": \"2013-02\"\n",
      "        },\n",
      "        \"Drug/Intervention\": {\n",
      "            \"Drug\": [],\n",
      "            \"Procedure\": [],\n",
      "            \"Device\": [],\n",
      "            \"Behavioral\": [],\n",
      "            \"Combination_product\": [],\n",
      "            \"Biological\": [\n",
      "                \"plasmodium falciparum sporozoites 2sites\",\n",
      "                \"plasmodium falciparum sporozoites 1 site\",\n",
      "                \"plasmodium falciparum sporozoites 1site\"\n",
      "            ],\n",
      "            \"Radiation\": [],\n",
      "            \"Other\": []\n",
      "        },\n",
      "        \"Disease/Condition\": {\n",
      "            \"Disease Name\": [\n",
      "                \"malaria\",\n",
      "                \"plasmodium falciparum\"\n",
      "            ]\n",
      "        },\n",
      "        \"Locations\": [\n",
      "            {\n",
      "                \"Institution\": \"centre for clinical vaccinology and tropical medicine\",\n",
      "                \"City\": \"university of oxford\",\n",
      "                \"Country\": \"united kingdom\"\n",
      "            }\n",
      "        ]\n",
      "    },\n",
      "    {\n",
      "        \"Trial\": {\n",
      "            \"Trial ID\": \"nct02458092\",\n",
      "            \"Title\": \"evaluate the safety and efficacy of plasmodium falciparum malaria protein 010 (fmp010) administered with adjuvant as01b\",\n",
      "            \"URL\": \"https://clinicaltrials.gov/study/nct02458092\",\n",
      "            \"Status\": \"completed\",\n",
      "            \"Brief Summary\": \"the purpose of this study is to determine whether an investigational malaria vaccine is safe and induces an immune response against malaria \n"
     ]
    }
   ],
   "source": [
    "# Re-importing necessary libraries as the execution state was reset\n",
    "import pandas as pd\n",
    "import json\n",
    "\n",
    "\n",
    "# Function to clean unwanted unicode characters like \"\\u00e9\" from a string\n",
    "def clean_unicode(text):\n",
    "    if isinstance(text, str):\n",
    "        return text.encode('ascii', 'ignore').decode('ascii')\n",
    "    return text\n",
    "\n",
    "# Updated function to include different intervention types and convert each row to a JSON-like structure\n",
    "def row_to_json(row):\n",
    "    trial_info = {\n",
    "        \"Trial ID\": clean_unicode(row[\"NCT Number\"]),\n",
    "        \"Title\": clean_unicode(row[\"Study Title\"]),\n",
    "        \"URL\": clean_unicode(row[\"Study URL\"]),\n",
    "        \"Status\": clean_unicode(row[\"Study Status\"]),\n",
    "        \"Brief Summary\": clean_unicode(row[\"Brief Summary\"]),\n",
    "        \"Phase\": clean_unicode(row[\"Phases\"]),\n",
    "        \"Study Start Date\": clean_unicode(row[\"Start Date\"]),\n",
    "        \"Expected Completion Date\": clean_unicode(row[\"Completion Date\"])\n",
    "    }\n",
    "\n",
    "    # Splitting interventions into different types if they exist\n",
    "    intervention_types = [\"drug\", \"procedure\", \"device\", \"behavioral\", \"combination_product\",\"biological\",\"radiation\",\"other\"]\n",
    "    interventions_info = {key.capitalize(): [] for key in intervention_types}  # Initialize all intervention types with empty lists\n",
    "    if pd.notnull(row[\"Interventions\"]):\n",
    "        interventions = row[\"Interventions\"].split('|')\n",
    "        for intervention in interventions:\n",
    "            intervention = intervention.strip()\n",
    "            for type_key in intervention_types:\n",
    "                if intervention.startswith(f\"{type_key}:\"):\n",
    "                    # Clean the intervention name and add it to the corresponding list\n",
    "                    intervention_name = intervention[len(f\"{type_key}:\"):].strip()\n",
    "                    intervention_cleaned = clean_unicode(intervention_name)\n",
    "                    # Capitalize the first letter of each intervention type for the output\n",
    "                    intervention_type_key = type_key.capitalize()\n",
    "                    interventions_info[intervention_type_key].append(intervention_cleaned)\n",
    "\n",
    "    # Splitting disease names if multiple diseases are listed\n",
    "    disease_info = {\n",
    "        \"Disease Name\": [clean_unicode(disease) for disease in row[\"Conditions\"].split('|')] if pd.notnull(row[\"Conditions\"]) else []\n",
    "    }\n",
    "\n",
    "    # Parsing locations into structured format\n",
    "    locations_info = []\n",
    "    if pd.notnull(row[\"Locations\"]):\n",
    "        locations_list = row[\"Locations\"].split('|')\n",
    "        for location in locations_list:\n",
    "            parts = [clean_unicode(part.strip()) for part in location.split(',')]\n",
    "            location_dict = {\n",
    "                \"Institution\": parts[0] if parts else None,\n",
    "                \"City\": parts[1] if len(parts) > 1 else None,\n",
    "                \"Country\": parts[-1] if parts else None\n",
    "            }\n",
    "            locations_info.append(location_dict)\n",
    "\n",
    "    # Structuring the JSON object\n",
    "    json_structure = {\n",
    "        \"Trial\": trial_info,\n",
    "        \"Drug/Intervention\": interventions_info,\n",
    "        \"Disease/Condition\": disease_info,\n",
    "        \"Locations\": locations_info\n",
    "    }\n",
    "\n",
    "    return json_structure\n",
    "\n",
    "# Convert each row of the dataframe to JSON structure and store in a list\n",
    "json_data = [row_to_json(row) for index, row in preprocessed_trials.iterrows()]\n",
    "\n",
    "# Convert the list to JSON string for display\n",
    "json_string = json.dumps(json_data, indent=4)\n",
    "\n",
    "# Display the first few JSON structures as a sample\n",
    "print(json_string[:10000])  # Display only first 2000 characters for brevity\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'Trial': {'Trial ID': 'nct03334747',\n",
       "  'Title': 'safety of kae609 in adults with uncomplicated plasmodium falciparum malaria.',\n",
       "  'URL': 'https://clinicaltrials.gov/study/nct03334747',\n",
       "  'Status': 'completed',\n",
       "  'Brief Summary': 'kae609 will be evaluated primarily for hepatic safety of single and multiple doses in sequential cohorts with increasing doses.this study aims to determine the maximum safe dose of the investigational drug kae609 in malaria patients.',\n",
       "  'Phase': 'phase2',\n",
       "  'Study Start Date': '2017-11-16',\n",
       "  'Expected Completion Date': '2019-11-23'},\n",
       " 'Drug/Intervention': {'Drug': ['kae609', 'coartem'],\n",
       "  'Procedure': [],\n",
       "  'Device': [],\n",
       "  'Behavioral': [],\n",
       "  'Combination_product': [],\n",
       "  'Biological': [],\n",
       "  'Radiation': [],\n",
       "  'Other': []},\n",
       " 'Disease/Condition': {'Disease Name': ['malaria']},\n",
       " 'Locations': [{'Institution': 'novartis investigative site',\n",
       "   'City': 'lambarene',\n",
       "   'Country': 'gabon'},\n",
       "  {'Institution': 'novartis investigative site',\n",
       "   'City': 'kintampo',\n",
       "   'Country': 'ghana'},\n",
       "  {'Institution': 'novartis investigative site',\n",
       "   'City': 'navrango',\n",
       "   'Country': 'ghana'},\n",
       "  {'Institution': 'novartis investigative site',\n",
       "   'City': 'bamako',\n",
       "   'Country': 'mali'},\n",
       "  {'Institution': 'novartis investigative site',\n",
       "   'City': 'sotuba',\n",
       "   'Country': 'mali'},\n",
       "  {'Institution': 'novartis investigative site',\n",
       "   'City': 'kigali',\n",
       "   'Country': 'rwanda'},\n",
       "  {'Institution': 'novartis investigative site',\n",
       "   'City': 'bushenyi',\n",
       "   'Country': 'uganda'},\n",
       "  {'Institution': 'novartis investigative site',\n",
       "   'City': 'kampala',\n",
       "   'Country': 'uganda'},\n",
       "  {'Institution': 'novartis investigative site',\n",
       "   'City': 'tororo',\n",
       "   'Country': 'uganda'}]}"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "json_data = json.loads(json_string)\n",
    "json_data[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(json_data[:3])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load data into DB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "# from neo4j import GraphDatabase\n",
    "\n",
    "# class Neo4jGraphLoader:\n",
    "#     def __init__(self, uri, user, password):\n",
    "#         self.driver = GraphDatabase.driver(uri, auth=(user, password))\n",
    "\n",
    "#     def close(self):\n",
    "#         self.driver.close()\n",
    "\n",
    "#     def load_json_into_graph(self, json_data):\n",
    "#         with self.driver.session() as session:\n",
    "#             for trial_data in json_data:\n",
    "#                 print(f\"Loading trial....{trial_data['Trial']['Trial ID']}\")\n",
    "#                 session.write_transaction(self._create_and_link_nodes, trial_data)\n",
    "\n",
    "#     @staticmethod\n",
    "#     def _create_and_link_nodes(tx, trial_data):\n",
    "#         # Create or update the Trial node with all its attributes\n",
    "#         trial_props = trial_data[\"Trial\"]\n",
    "#         tx.run(\"\"\"\n",
    "#             MERGE (trial:Trial {TrialID: $TrialID})\n",
    "#             SET trial = $props\n",
    "#             \"\"\", TrialID=trial_props[\"Trial ID\"], props=trial_props)\n",
    "\n",
    "#         # Create Intervention nodes and link to the Trial node\n",
    "#         interventions = trial_data[\"Drug/Intervention\"]\n",
    "#         for intervention_type, intervention_names in interventions.items():\n",
    "#             for name in intervention_names:\n",
    "#                 tx.run(\"\"\"\n",
    "#                     MERGE (intervention:Intervention {Name: $name, Type: $type})\n",
    "#                     WITH intervention\n",
    "#                     MATCH (trial:Trial {TrialID: $TrialID})\n",
    "#                     MERGE (trial)-[:USES]->(intervention)\n",
    "#                     \"\"\", name=name, type=intervention_type, TrialID=trial_props[\"Trial ID\"])\n",
    "\n",
    "#         # Create Disease/Condition nodes and link to the Trial node\n",
    "#         for disease_name in trial_data[\"Disease/Condition\"][\"Disease Name\"]:\n",
    "#             tx.run(\"\"\"\n",
    "#                 MERGE (disease:Disease {Name: $name})\n",
    "#                 WITH disease\n",
    "#                 MATCH (trial:Trial {TrialID: $TrialID})\n",
    "#                 MERGE (trial)-[:TARGETS]->(disease)\n",
    "#                 \"\"\", name=disease_name, TrialID=trial_props[\"Trial ID\"])\n",
    "            \n",
    "#         for location in trial_data[\"Locations\"]:\n",
    "#             # Check if at least one attribute is present\n",
    "#             if any(location.get(attr) for attr in [\"Institution\", \"City\", \"Country\"]):\n",
    "#                 # Create or update Location node\n",
    "#                 tx.run(\"\"\"\n",
    "#                     MERGE (location:Location {Institution: $institution, City: $city, Country: $country})\n",
    "#                     ON CREATE SET location.Institution = $institution, location.City = $city, location.Country = $country\n",
    "#                     ON MATCH SET location += {Institution: $institution, City: $city, Country: $country}\n",
    "#                     WITH location\n",
    "#                     MATCH (trial:Trial {TrialID: $TrialID})\n",
    "#                     MERGE (trial)-[:CONDUCTED_AT]->(location)\n",
    "#                     \"\"\", \n",
    "#                         institution=location.get(\"Institution\", \"Unknown\"), \n",
    "#                         city=location.get(\"City\", \"Unknown\"), \n",
    "#                         country=location.get(\"Country\", \"Unknown\"), \n",
    "#                         TrialID=trial_data[\"Trial\"][\"Trial ID\"])\n",
    "#             else:\n",
    "#                 print(f\"Skipped a location due to insufficient data: {location}\")\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create an instance of the loader\n",
    "json_path = r'config.json'\n",
    "with open(json_path) as config_json:\n",
    "    config = json.load(config_json)\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "from neo4j import GraphDatabase\n",
    "uri = \"neo4j+s://cf1b9b39.databases.neo4j.io\" \n",
    "driver = GraphDatabase.driver(uri , auth = (\"neo4j\",\"vRDqUrdllXu6SkFA8vtxBk1bppbYHA-NFZc1mhjtgxY\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<neo4j._sync.driver.Neo4jDriver at 0x7f7e69d70c10>"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "NEO4J_URI=\n",
    "NEO4J_USERNAME=neo4j\n",
    "NEO4J_PASSWORD=vRDqUrdllXu6SkFA8vtxBk1bppbYHA-NFZc1mhjtgxY\n",
    "AURA_INSTANCEID=cf1b9b39\n",
    "AURA_INSTANCENAME=Instance01"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "def load_data_to_neo4j(data):\n",
    "    with driver.session() as session:\n",
    "        # Load Trial data\n",
    "        trial_data = data[\"Trial\"]\n",
    "        trial_node = session.run(\"\"\"\n",
    "            MERGE (t:Trial {TrialID: $TrialID})\n",
    "            ON CREATE SET t += $trial_data\n",
    "            RETURN t\n",
    "        \"\"\", TrialID=trial_data['Trial ID'], trial_data=trial_data).single()[\"t\"]\n",
    "\n",
    "        # Load Intervention data and connect to Trial\n",
    "        interventions = data[\"Drug/Intervention\"]\n",
    "        for intervention_type, intervention_list in interventions.items():\n",
    "            for intervention_name in intervention_list:\n",
    "                intervention_node = session.run(\"\"\"\n",
    "                    MERGE (i:Intervention {Name: $Name, Type: $Type})\n",
    "                    RETURN i\n",
    "                \"\"\", Name=intervention_name, Type=intervention_type).single()[\"i\"]\n",
    "                session.run(\"\"\"\n",
    "                    MATCH (t:Trial {TrialID: $TrialID}), (i:Intervention {Name: $Name, Type: $Type})\n",
    "                    MERGE (t)-[:USES]->(i)\n",
    "                \"\"\", TrialID=trial_data['Trial ID'], Name=intervention_name, Type=intervention_type)\n",
    "\n",
    "        # Load Disease/Condition data and connect to Trial\n",
    "        for disease_name in data[\"Disease/Condition\"][\"Disease Name\"]:\n",
    "            disease_node = session.run(\"\"\"\n",
    "                MERGE (d:Disease {Name: $Name})\n",
    "                RETURN d\n",
    "            \"\"\", Name=disease_name).single()[\"d\"]\n",
    "            session.run(\"\"\"\n",
    "                MATCH (t:Trial {TrialID: $TrialID}), (d:Disease {Name: $Name})\n",
    "                MERGE (t)-[:TARGETS]->(d)\n",
    "            \"\"\", TrialID=trial_data['Trial ID'], Name=disease_name)\n",
    "\n",
    "        # Load Location data and connect to Trial\n",
    "        for location in data[\"Locations\"]:\n",
    "            location_node = session.run(\"\"\"\n",
    "                MERGE (l:Location {Institution: $Institution, City: $City, Country: $Country})\n",
    "                RETURN l\n",
    "            \"\"\", Institution=location[\"Institution\"], City=location[\"City\"], Country=location[\"Country\"]).single()[\"l\"]\n",
    "            session.run(\"\"\"\n",
    "                MATCH (t:Trial {TrialID: $TrialID}), (l:Location {Institution: $Institution, City: $City, Country: $Country})\n",
    "                MERGE (t)-[:CONDUCTED_AT]->(l)\n",
    "            \"\"\", TrialID=trial_data['Trial ID'], Institution=location[\"Institution\"], City=location[\"City\"], Country=location[\"Country\"])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "ename": "AuthError",
     "evalue": "{code: Neo.ClientError.Security.Unauthorized} {message: The client is unauthorized due to authentication failure.}",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mAuthError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[78], line 2\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[39mfor\u001b[39;00m trial \u001b[39min\u001b[39;00m json_data[:\u001b[39m3\u001b[39m]:\n\u001b[0;32m----> 2\u001b[0m     load_data_to_neo4j(trial)\n",
      "Cell \u001b[0;32mIn[77], line 5\u001b[0m, in \u001b[0;36mload_data_to_neo4j\u001b[0;34m(data)\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[39mwith\u001b[39;00m driver\u001b[39m.\u001b[39msession() \u001b[39mas\u001b[39;00m session:\n\u001b[1;32m      3\u001b[0m     \u001b[39m# Load Trial data\u001b[39;00m\n\u001b[1;32m      4\u001b[0m     trial_data \u001b[39m=\u001b[39m data[\u001b[39m\"\u001b[39m\u001b[39mTrial\u001b[39m\u001b[39m\"\u001b[39m]\n\u001b[0;32m----> 5\u001b[0m     trial_node \u001b[39m=\u001b[39m session\u001b[39m.\u001b[39;49mrun(\u001b[39m\"\"\"\u001b[39;49m\n\u001b[1;32m      6\u001b[0m \u001b[39m        MERGE (t:Trial \u001b[39;49m\u001b[39m{\u001b[39;49m\u001b[39mTrialID: $TrialID})\u001b[39;49m\n\u001b[1;32m      7\u001b[0m \u001b[39m        ON CREATE SET t += $trial_data\u001b[39;49m\n\u001b[1;32m      8\u001b[0m \u001b[39m        RETURN t\u001b[39;49m\n\u001b[1;32m      9\u001b[0m \u001b[39m    \u001b[39;49m\u001b[39m\"\"\"\u001b[39;49m, TrialID\u001b[39m=\u001b[39;49mtrial_data[\u001b[39m'\u001b[39;49m\u001b[39mTrial ID\u001b[39;49m\u001b[39m'\u001b[39;49m], trial_data\u001b[39m=\u001b[39;49mtrial_data)\u001b[39m.\u001b[39msingle()[\u001b[39m\"\u001b[39m\u001b[39mt\u001b[39m\u001b[39m\"\u001b[39m]\n\u001b[1;32m     11\u001b[0m     \u001b[39m# Load Intervention data and connect to Trial\u001b[39;00m\n\u001b[1;32m     12\u001b[0m     interventions \u001b[39m=\u001b[39m data[\u001b[39m\"\u001b[39m\u001b[39mDrug/Intervention\u001b[39m\u001b[39m\"\u001b[39m]\n",
      "File \u001b[0;32m~/opt/anaconda3/envs/tf_env/lib/python3.10/site-packages/neo4j/_sync/work/session.py:302\u001b[0m, in \u001b[0;36mSession.run\u001b[0;34m(self, query, parameters, **kwargs)\u001b[0m\n\u001b[1;32m    299\u001b[0m     \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_auto_result\u001b[39m.\u001b[39m_buffer_all()\n\u001b[1;32m    301\u001b[0m \u001b[39mif\u001b[39;00m \u001b[39mnot\u001b[39;00m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_connection:\n\u001b[0;32m--> 302\u001b[0m     \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_connect(\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_config\u001b[39m.\u001b[39;49mdefault_access_mode)\n\u001b[1;32m    303\u001b[0m     \u001b[39massert\u001b[39;00m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_connection \u001b[39mis\u001b[39;00m \u001b[39mnot\u001b[39;00m \u001b[39mNone\u001b[39;00m\n\u001b[1;32m    304\u001b[0m cx \u001b[39m=\u001b[39m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_connection\n",
      "File \u001b[0;32m~/opt/anaconda3/envs/tf_env/lib/python3.10/site-packages/neo4j/_sync/work/session.py:130\u001b[0m, in \u001b[0;36mSession._connect\u001b[0;34m(self, access_mode, **acquire_kwargs)\u001b[0m\n\u001b[1;32m    128\u001b[0m     access_mode \u001b[39m=\u001b[39m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_config\u001b[39m.\u001b[39mdefault_access_mode\n\u001b[1;32m    129\u001b[0m \u001b[39mtry\u001b[39;00m:\n\u001b[0;32m--> 130\u001b[0m     \u001b[39msuper\u001b[39;49m()\u001b[39m.\u001b[39;49m_connect(\n\u001b[1;32m    131\u001b[0m         access_mode, auth\u001b[39m=\u001b[39;49m\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_config\u001b[39m.\u001b[39;49mauth, \u001b[39m*\u001b[39;49m\u001b[39m*\u001b[39;49macquire_kwargs\n\u001b[1;32m    132\u001b[0m     )\n\u001b[1;32m    133\u001b[0m \u001b[39mexcept\u001b[39;00m asyncio\u001b[39m.\u001b[39mCancelledError:\n\u001b[1;32m    134\u001b[0m     \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_handle_cancellation(message\u001b[39m=\u001b[39m\u001b[39m\"\u001b[39m\u001b[39m_connect\u001b[39m\u001b[39m\"\u001b[39m)\n",
      "File \u001b[0;32m~/opt/anaconda3/envs/tf_env/lib/python3.10/site-packages/neo4j/_sync/work/workspace.py:161\u001b[0m, in \u001b[0;36mWorkspace._connect\u001b[0;34m(self, access_mode, auth, **acquire_kwargs)\u001b[0m\n\u001b[1;32m    153\u001b[0m     \u001b[39melse\u001b[39;00m:\n\u001b[1;32m    154\u001b[0m         \u001b[39m# This is the first time we open a connection to a server in a\u001b[39;00m\n\u001b[1;32m    155\u001b[0m         \u001b[39m# cluster environment for this session without explicitly\u001b[39;00m\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m    158\u001b[0m         \u001b[39m# we shall use this database explicitly for all subsequent\u001b[39;00m\n\u001b[1;32m    159\u001b[0m         \u001b[39m# actions within this session.\u001b[39;00m\n\u001b[1;32m    160\u001b[0m         log\u001b[39m.\u001b[39mdebug(\u001b[39m\"\u001b[39m\u001b[39m[#0000]  _: <WORKSPACE> resolve home database\u001b[39m\u001b[39m\"\u001b[39m)\n\u001b[0;32m--> 161\u001b[0m         \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_pool\u001b[39m.\u001b[39;49mupdate_routing_table(\n\u001b[1;32m    162\u001b[0m             database\u001b[39m=\u001b[39;49m\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_config\u001b[39m.\u001b[39;49mdatabase,\n\u001b[1;32m    163\u001b[0m             imp_user\u001b[39m=\u001b[39;49m\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_config\u001b[39m.\u001b[39;49mimpersonated_user,\n\u001b[1;32m    164\u001b[0m             bookmarks\u001b[39m=\u001b[39;49m\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_get_bookmarks(),\n\u001b[1;32m    165\u001b[0m             auth\u001b[39m=\u001b[39;49mauth,\n\u001b[1;32m    166\u001b[0m             acquisition_timeout\u001b[39m=\u001b[39;49macquisition_timeout,\n\u001b[1;32m    167\u001b[0m             database_callback\u001b[39m=\u001b[39;49m\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_set_cached_database\n\u001b[1;32m    168\u001b[0m         )\n\u001b[1;32m    169\u001b[0m acquire_kwargs_ \u001b[39m=\u001b[39m {\n\u001b[1;32m    170\u001b[0m     \u001b[39m\"\u001b[39m\u001b[39maccess_mode\u001b[39m\u001b[39m\"\u001b[39m: access_mode,\n\u001b[1;32m    171\u001b[0m     \u001b[39m\"\u001b[39m\u001b[39mtimeout\u001b[39m\u001b[39m\"\u001b[39m: acquisition_timeout,\n\u001b[0;32m   (...)\u001b[0m\n\u001b[1;32m    175\u001b[0m     \u001b[39m\"\u001b[39m\u001b[39mliveness_check_timeout\u001b[39m\u001b[39m\"\u001b[39m: \u001b[39mNone\u001b[39;00m,\n\u001b[1;32m    176\u001b[0m }\n\u001b[1;32m    177\u001b[0m acquire_kwargs_\u001b[39m.\u001b[39mupdate(acquire_kwargs)\n",
      "File \u001b[0;32m~/opt/anaconda3/envs/tf_env/lib/python3.10/site-packages/neo4j/_sync/io/_pool.py:774\u001b[0m, in \u001b[0;36mNeo4jPool.update_routing_table\u001b[0;34m(self, database, imp_user, bookmarks, auth, acquisition_timeout, database_callback)\u001b[0m\n\u001b[1;32m    769\u001b[0m prefer_initial_routing_address \u001b[39m=\u001b[39m \\\n\u001b[1;32m    770\u001b[0m     \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mrouting_tables[database]\u001b[39m.\u001b[39minitialized_without_writers\n\u001b[1;32m    772\u001b[0m \u001b[39mif\u001b[39;00m prefer_initial_routing_address:\n\u001b[1;32m    773\u001b[0m     \u001b[39m# TODO: Test this state\u001b[39;00m\n\u001b[0;32m--> 774\u001b[0m     \u001b[39mif\u001b[39;00m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_update_routing_table_from(\n\u001b[1;32m    775\u001b[0m         \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49maddress, database\u001b[39m=\u001b[39;49mdatabase,\n\u001b[1;32m    776\u001b[0m         imp_user\u001b[39m=\u001b[39;49mimp_user, bookmarks\u001b[39m=\u001b[39;49mbookmarks, auth\u001b[39m=\u001b[39;49mauth,\n\u001b[1;32m    777\u001b[0m         acquisition_timeout\u001b[39m=\u001b[39;49macquisition_timeout,\n\u001b[1;32m    778\u001b[0m         database_callback\u001b[39m=\u001b[39;49mdatabase_callback\n\u001b[1;32m    779\u001b[0m     ):\n\u001b[1;32m    780\u001b[0m         \u001b[39m# Why is only the first initial routing address used?\u001b[39;00m\n\u001b[1;32m    781\u001b[0m         \u001b[39mreturn\u001b[39;00m\n\u001b[1;32m    782\u001b[0m \u001b[39mif\u001b[39;00m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_update_routing_table_from(\n\u001b[1;32m    783\u001b[0m     \u001b[39m*\u001b[39m(existing_routers \u001b[39m-\u001b[39m {\u001b[39mself\u001b[39m\u001b[39m.\u001b[39maddress}), database\u001b[39m=\u001b[39mdatabase,\n\u001b[1;32m    784\u001b[0m     imp_user\u001b[39m=\u001b[39mimp_user, bookmarks\u001b[39m=\u001b[39mbookmarks, auth\u001b[39m=\u001b[39mauth,\n\u001b[1;32m    785\u001b[0m     acquisition_timeout\u001b[39m=\u001b[39macquisition_timeout,\n\u001b[1;32m    786\u001b[0m     database_callback\u001b[39m=\u001b[39mdatabase_callback\n\u001b[1;32m    787\u001b[0m ):\n",
      "File \u001b[0;32m~/opt/anaconda3/envs/tf_env/lib/python3.10/site-packages/neo4j/_sync/io/_pool.py:720\u001b[0m, in \u001b[0;36mNeo4jPool._update_routing_table_from\u001b[0;34m(self, database, imp_user, bookmarks, auth, acquisition_timeout, database_callback, *routers)\u001b[0m\n\u001b[1;32m    716\u001b[0m \u001b[39mfor\u001b[39;00m router \u001b[39min\u001b[39;00m routers:\n\u001b[1;32m    717\u001b[0m     \u001b[39mfor\u001b[39;00m address \u001b[39min\u001b[39;00m NetworkUtil\u001b[39m.\u001b[39mresolve_address(\n\u001b[1;32m    718\u001b[0m         router, resolver\u001b[39m=\u001b[39m\u001b[39mself\u001b[39m\u001b[39m.\u001b[39mpool_config\u001b[39m.\u001b[39mresolver\n\u001b[1;32m    719\u001b[0m     ):\n\u001b[0;32m--> 720\u001b[0m         new_routing_table \u001b[39m=\u001b[39m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mfetch_routing_table(\n\u001b[1;32m    721\u001b[0m             address\u001b[39m=\u001b[39;49maddress, acquisition_timeout\u001b[39m=\u001b[39;49macquisition_timeout,\n\u001b[1;32m    722\u001b[0m             database\u001b[39m=\u001b[39;49mdatabase, imp_user\u001b[39m=\u001b[39;49mimp_user, bookmarks\u001b[39m=\u001b[39;49mbookmarks,\n\u001b[1;32m    723\u001b[0m             auth\u001b[39m=\u001b[39;49mauth\n\u001b[1;32m    724\u001b[0m         )\n\u001b[1;32m    725\u001b[0m         \u001b[39mif\u001b[39;00m new_routing_table \u001b[39mis\u001b[39;00m \u001b[39mnot\u001b[39;00m \u001b[39mNone\u001b[39;00m:\n\u001b[1;32m    726\u001b[0m             new_database \u001b[39m=\u001b[39m new_routing_table\u001b[39m.\u001b[39mdatabase\n",
      "File \u001b[0;32m~/opt/anaconda3/envs/tf_env/lib/python3.10/site-packages/neo4j/_sync/io/_pool.py:657\u001b[0m, in \u001b[0;36mNeo4jPool.fetch_routing_table\u001b[0;34m(self, address, acquisition_timeout, database, imp_user, bookmarks, auth)\u001b[0m\n\u001b[1;32m    655\u001b[0m new_routing_info \u001b[39m=\u001b[39m \u001b[39mNone\u001b[39;00m\n\u001b[1;32m    656\u001b[0m \u001b[39mtry\u001b[39;00m:\n\u001b[0;32m--> 657\u001b[0m     new_routing_info \u001b[39m=\u001b[39m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mfetch_routing_info(\n\u001b[1;32m    658\u001b[0m         address, database, imp_user, bookmarks, auth,\n\u001b[1;32m    659\u001b[0m         acquisition_timeout\n\u001b[1;32m    660\u001b[0m     )\n\u001b[1;32m    661\u001b[0m \u001b[39mexcept\u001b[39;00m Neo4jError \u001b[39mas\u001b[39;00m e:\n\u001b[1;32m    662\u001b[0m     \u001b[39m# checks if the code is an error that is caused by the client. In\u001b[39;00m\n\u001b[1;32m    663\u001b[0m     \u001b[39m# this case there is no sense in trying to fetch a RT from another\u001b[39;00m\n\u001b[1;32m    664\u001b[0m     \u001b[39m# router. Hence, the driver should fail fast during discovery.\u001b[39;00m\n\u001b[1;32m    665\u001b[0m     \u001b[39mif\u001b[39;00m e\u001b[39m.\u001b[39m_is_fatal_during_discovery():\n",
      "File \u001b[0;32m~/opt/anaconda3/envs/tf_env/lib/python3.10/site-packages/neo4j/_sync/io/_pool.py:625\u001b[0m, in \u001b[0;36mNeo4jPool.fetch_routing_info\u001b[0;34m(self, address, database, imp_user, bookmarks, auth, acquisition_timeout)\u001b[0m\n\u001b[1;32m    623\u001b[0m     auth \u001b[39m=\u001b[39m copy(auth)\n\u001b[1;32m    624\u001b[0m     auth\u001b[39m.\u001b[39mforce_auth \u001b[39m=\u001b[39m \u001b[39mFalse\u001b[39;00m\n\u001b[0;32m--> 625\u001b[0m cx \u001b[39m=\u001b[39m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_acquire(address, auth, deadline, \u001b[39mNone\u001b[39;49;00m)\n\u001b[1;32m    626\u001b[0m \u001b[39mtry\u001b[39;00m:\n\u001b[1;32m    627\u001b[0m     routing_table \u001b[39m=\u001b[39m cx\u001b[39m.\u001b[39mroute(\n\u001b[1;32m    628\u001b[0m         database\u001b[39m=\u001b[39mdatabase \u001b[39mor\u001b[39;00m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mworkspace_config\u001b[39m.\u001b[39mdatabase,\n\u001b[1;32m    629\u001b[0m         imp_user\u001b[39m=\u001b[39mimp_user \u001b[39mor\u001b[39;00m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mworkspace_config\u001b[39m.\u001b[39mimpersonated_user,\n\u001b[1;32m    630\u001b[0m         bookmarks\u001b[39m=\u001b[39mbookmarks\n\u001b[1;32m    631\u001b[0m     )\n",
      "File \u001b[0;32m~/opt/anaconda3/envs/tf_env/lib/python3.10/site-packages/neo4j/_sync/io/_pool.py:313\u001b[0m, in \u001b[0;36mIOPool._acquire\u001b[0;34m(self, address, auth, deadline, liveness_check_timeout)\u001b[0m\n\u001b[1;32m    308\u001b[0m             \u001b[39mraise\u001b[39;00m ClientError(\n\u001b[1;32m    309\u001b[0m                 \u001b[39m\"\u001b[39m\u001b[39mfailed to obtain a connection from the pool within \u001b[39m\u001b[39m\"\u001b[39m\n\u001b[1;32m    310\u001b[0m                 \u001b[39m\"\u001b[39m\u001b[39m{!r}\u001b[39;00m\u001b[39ms (timeout)\u001b[39m\u001b[39m\"\u001b[39m\u001b[39m.\u001b[39mformat(deadline\u001b[39m.\u001b[39moriginal_timeout)\n\u001b[1;32m    311\u001b[0m             )\n\u001b[1;32m    312\u001b[0m log\u001b[39m.\u001b[39mdebug(\u001b[39m\"\u001b[39m\u001b[39m[#0000]  _: <POOL> trying to hand out new connection\u001b[39m\u001b[39m\"\u001b[39m)\n\u001b[0;32m--> 313\u001b[0m \u001b[39mreturn\u001b[39;00m connection_creator()\n",
      "File \u001b[0;32m~/opt/anaconda3/envs/tf_env/lib/python3.10/site-packages/neo4j/_sync/io/_pool.py:165\u001b[0m, in \u001b[0;36mIOPool._acquire_new_later.<locals>.connection_creator\u001b[0;34m()\u001b[0m\n\u001b[1;32m    163\u001b[0m \u001b[39mtry\u001b[39;00m:\n\u001b[1;32m    164\u001b[0m     \u001b[39mtry\u001b[39;00m:\n\u001b[0;32m--> 165\u001b[0m         connection \u001b[39m=\u001b[39m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mopener(\n\u001b[1;32m    166\u001b[0m             address, auth \u001b[39mor\u001b[39;49;00m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mpool_config\u001b[39m.\u001b[39;49mauth, deadline\n\u001b[1;32m    167\u001b[0m         )\n\u001b[1;32m    168\u001b[0m     \u001b[39mexcept\u001b[39;00m ServiceUnavailable:\n\u001b[1;32m    169\u001b[0m         \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mdeactivate(address)\n",
      "File \u001b[0;32m~/opt/anaconda3/envs/tf_env/lib/python3.10/site-packages/neo4j/_sync/io/_pool.py:557\u001b[0m, in \u001b[0;36mNeo4jPool.open.<locals>.opener\u001b[0;34m(addr, auth_manager, deadline)\u001b[0m\n\u001b[1;32m    556\u001b[0m \u001b[39mdef\u001b[39;00m \u001b[39mopener\u001b[39m(addr, auth_manager, deadline):\n\u001b[0;32m--> 557\u001b[0m     \u001b[39mreturn\u001b[39;00m Bolt\u001b[39m.\u001b[39;49mopen(\n\u001b[1;32m    558\u001b[0m         addr, auth_manager\u001b[39m=\u001b[39;49mauth_manager, deadline\u001b[39m=\u001b[39;49mdeadline,\n\u001b[1;32m    559\u001b[0m         routing_context\u001b[39m=\u001b[39;49mrouting_context, pool_config\u001b[39m=\u001b[39;49mpool_config\n\u001b[1;32m    560\u001b[0m     )\n",
      "File \u001b[0;32m~/opt/anaconda3/envs/tf_env/lib/python3.10/site-packages/neo4j/_sync/io/_bolt.py:487\u001b[0m, in \u001b[0;36mBolt.open\u001b[0;34m(cls, address, auth_manager, deadline, routing_context, pool_config)\u001b[0m\n\u001b[1;32m    485\u001b[0m connection\u001b[39m.\u001b[39msocket\u001b[39m.\u001b[39mset_deadline(deadline)\n\u001b[1;32m    486\u001b[0m \u001b[39mtry\u001b[39;00m:\n\u001b[0;32m--> 487\u001b[0m     connection\u001b[39m.\u001b[39;49mhello()\n\u001b[1;32m    488\u001b[0m \u001b[39mfinally\u001b[39;00m:\n\u001b[1;32m    489\u001b[0m     connection\u001b[39m.\u001b[39msocket\u001b[39m.\u001b[39mset_deadline(\u001b[39mNone\u001b[39;00m)\n",
      "File \u001b[0;32m~/opt/anaconda3/envs/tf_env/lib/python3.10/site-packages/neo4j/_sync/io/_bolt5.py:579\u001b[0m, in \u001b[0;36mBolt5x2.hello\u001b[0;34m(self, dehydration_hooks, hydration_hooks)\u001b[0m\n\u001b[1;32m    577\u001b[0m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mlogon(dehydration_hooks, hydration_hooks)\n\u001b[1;32m    578\u001b[0m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39msend_all()\n\u001b[0;32m--> 579\u001b[0m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mfetch_all()\n\u001b[1;32m    580\u001b[0m check_supported_server_product(\u001b[39mself\u001b[39m\u001b[39m.\u001b[39mserver_info\u001b[39m.\u001b[39magent)\n",
      "File \u001b[0;32m~/opt/anaconda3/envs/tf_env/lib/python3.10/site-packages/neo4j/_sync/io/_bolt.py:863\u001b[0m, in \u001b[0;36mBolt.fetch_all\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    861\u001b[0m response \u001b[39m=\u001b[39m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mresponses[\u001b[39m0\u001b[39m]\n\u001b[1;32m    862\u001b[0m \u001b[39mwhile\u001b[39;00m \u001b[39mnot\u001b[39;00m response\u001b[39m.\u001b[39mcomplete:\n\u001b[0;32m--> 863\u001b[0m     detail_delta, summary_delta \u001b[39m=\u001b[39m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mfetch_message()\n\u001b[1;32m    864\u001b[0m     detail_count \u001b[39m+\u001b[39m\u001b[39m=\u001b[39m detail_delta\n\u001b[1;32m    865\u001b[0m     summary_count \u001b[39m+\u001b[39m\u001b[39m=\u001b[39m summary_delta\n",
      "File \u001b[0;32m~/opt/anaconda3/envs/tf_env/lib/python3.10/site-packages/neo4j/_sync/io/_bolt.py:849\u001b[0m, in \u001b[0;36mBolt.fetch_message\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    845\u001b[0m \u001b[39m# Receive exactly one message\u001b[39;00m\n\u001b[1;32m    846\u001b[0m tag, fields \u001b[39m=\u001b[39m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39minbox\u001b[39m.\u001b[39mpop(\n\u001b[1;32m    847\u001b[0m     hydration_hooks\u001b[39m=\u001b[39m\u001b[39mself\u001b[39m\u001b[39m.\u001b[39mresponses[\u001b[39m0\u001b[39m]\u001b[39m.\u001b[39mhydration_hooks\n\u001b[1;32m    848\u001b[0m )\n\u001b[0;32m--> 849\u001b[0m res \u001b[39m=\u001b[39m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_process_message(tag, fields)\n\u001b[1;32m    850\u001b[0m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39midle_since \u001b[39m=\u001b[39m perf_counter()\n\u001b[1;32m    851\u001b[0m \u001b[39mreturn\u001b[39;00m res\n",
      "File \u001b[0;32m~/opt/anaconda3/envs/tf_env/lib/python3.10/site-packages/neo4j/_sync/io/_bolt5.py:374\u001b[0m, in \u001b[0;36mBolt5x0._process_message\u001b[0;34m(self, tag, fields)\u001b[0m\n\u001b[1;32m    372\u001b[0m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_server_state_manager\u001b[39m.\u001b[39mstate \u001b[39m=\u001b[39m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mbolt_states\u001b[39m.\u001b[39mFAILED\n\u001b[1;32m    373\u001b[0m \u001b[39mtry\u001b[39;00m:\n\u001b[0;32m--> 374\u001b[0m     response\u001b[39m.\u001b[39;49mon_failure(summary_metadata \u001b[39mor\u001b[39;49;00m {})\n\u001b[1;32m    375\u001b[0m \u001b[39mexcept\u001b[39;00m (ServiceUnavailable, DatabaseUnavailable):\n\u001b[1;32m    376\u001b[0m     \u001b[39mif\u001b[39;00m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mpool:\n",
      "File \u001b[0;32m~/opt/anaconda3/envs/tf_env/lib/python3.10/site-packages/neo4j/_sync/io/_common.py:281\u001b[0m, in \u001b[0;36mLogonResponse.on_failure\u001b[0;34m(self, metadata)\u001b[0m\n\u001b[1;32m    279\u001b[0m handler \u001b[39m=\u001b[39m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mhandlers\u001b[39m.\u001b[39mget(\u001b[39m\"\u001b[39m\u001b[39mon_summary\u001b[39m\u001b[39m\"\u001b[39m)\n\u001b[1;32m    280\u001b[0m Util\u001b[39m.\u001b[39mcallback(handler)\n\u001b[0;32m--> 281\u001b[0m \u001b[39mraise\u001b[39;00m Neo4jError\u001b[39m.\u001b[39mhydrate(\u001b[39m*\u001b[39m\u001b[39m*\u001b[39mmetadata)\n",
      "\u001b[0;31mAuthError\u001b[0m: {code: Neo.ClientError.Security.Unauthorized} {message: The client is unauthorized due to authentication failure.}"
     ]
    }
   ],
   "source": [
    "for trial in json_data[:3]:\n",
    "    load_data_to_neo4j(trial)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "tf_env",
   "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.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}