01_extraction_donnees_sante.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "cc8f9942",
   "metadata": {},
   "source": [
    "# Objectif:\n",
    "\n",
    "On souhaite évaluer le nombre de publications qui sont le résultat d'études liées à l'INSERM.\n",
    "\n",
    "En particulier, on souhaite identifier les études qui ne donnent lieu à aucune publication et essayer de comprendre les raisons de cette absence de publication."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "f4027005",
   "metadata": {},
   "source": [
    "# Organisation :\n",
    "Dans ce premier notebook, l'objectif est d'extraire de manière automatique les données des études:\n",
    "\n",
    "- On récupère via l'API de *ClinicalTrial* les *ID* (**NCTId**) des études qui ont :\n",
    "  + pour sponsors l'*INSERM*, l'*ANRS* etc.\n",
    "  + un statut *'COMPLETED'*\n",
    "  + **2013** ≤ *date de fin d'étude* ≤ **2022** (mettre à jour cette valeur pour la mise à jour annuelle)  "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df671544-b95b-4ed6-8338-f67e1ccf4529",
   "metadata": {},
   "source": [
    "- A partir de ces **NCTIds**:\n",
    "   + on récupère sur *ClinicalTrial* les informations liées à ces études, en particulier des publications et leurs **PMIDs**.  \n",
    "    Ces publications sont de 2 types:\n",
    "        1. Elles ont été uploadées sur *CT* par les auteurs de l'étude: `BACKGROUND, RESULT`\n",
    "        2. Elles ont été automatiquement récupérés sur PubMed par *CT*: `DERIVED`\n",
    "    + on récupère sur *PubMed* les **PMIDs** des publications liées à ces études.  \n",
    "      On retrouve général un peu plus de publications que le traitement automatique réalisé par *CT*. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0998ee50-7726-4363-9c1a-a5e4eba44d5a",
   "metadata": {},
   "source": [
    "- Pour chaque **NCTIds**, on fusionne l'ensemble des **PMIDs** retournées par *CT* et *PubMed*.\n",
    "\n",
    "- A partir de cet ensemble de **PMIDs**, on récupère les infos des publications via *Pubmed*: `titre, auteurs, doi`...\n",
    "\n",
    "- On récupère le statut Open Access avec le DOI et l'API d'Unpaywall\n",
    "\n",
    "- On sauvegarde le résultat sous la forme d'une fichier CSV."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ebae2404-f12e-4810-b72d-e704146caf69",
   "metadata": {},
   "source": [
    "<span style=\"color:red\">**Mettre à jour `max_year` pour la mise à jour annuelle**</span>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "05555d09-edb4-4de4-ab7d-02ca1d6a4dd6",
   "metadata": {},
   "source": [
    "Les études terminées doivent normalement partager des données brut de ces essais, dans les 12 mois au plus tard après\n",
    "la date de la fin d’étude.  \n",
    "Voir: [Recherche clinique à l’Inserm : qualité et\n",
    "transparence (Juin 2021)](https://www.inserm.fr/wp-content/uploads/inserm-rechercheclinique-qualitetransparence-juin2021.pdf)  \n",
    "**Pour des données récoltées en 2024, on va donc se limiter aux essais terminés en 2022 et qui ont pu faire l'objet de publications en 2023.**  \n",
    "De la même façon, on exclut les études dont la date de fin est antérieure à 2013."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b1b1e17-db81-48e7-ab60-b772a4f37875",
   "metadata": {},
   "outputs": [],
   "source": [
    "min_year = 2013\n",
    "max_year = 2022"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65464cb1-f3cd-42cd-9f89-9aa323196250",
   "metadata": {},
   "source": [
    "# Extraction des NCTIds dans ClinicalTrial:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a3ef6948-8a2a-460c-a3ba-7d300527c78b",
   "metadata": {},
   "source": [
    "<span style=\"color:red\">**Obsolète**</span>  \n",
    "<span style=\"color:red\">**L'API v1 n'est plus disponible depuis mi-2024.**  \n",
    "**Aller directement à [la section API v2](#API-v2:)**</span>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "338bf2ef-bbcc-427a-8840-0f2b91b17292",
   "metadata": {},
   "source": [
    "## API v1:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "548c1a1f-38fd-455f-abd2-6111b492a446",
   "metadata": {},
   "source": [
    "Pour faciliter la récupération des données via l'API v1 de ClinicalTrial, on utilise le wrapper Python: [pytrials](https://github.com/jvfe/pytrials)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7de1cbc9-3b95-40cf-8ceb-73efdc8e9661",
   "metadata": {},
   "source": [
    "Installer ***pytrials***:\n",
    "- Dans Conda Navigator, se placer dans le meme environnement que celui qui execute Jupyter\n",
    "- Lancer Powershell Prompt dans cet environnement\n",
    "- Taper: `pip install pytrials`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2ae35742-d8a3-448b-b616-636604a16a50",
   "metadata": {},
   "outputs": [],
   "source": [
    "# from pytrials.client import ClinicalTrials\n",
    "# import urllib.parse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e22ac03f-cafe-4757-9c41-4bf21940f058",
   "metadata": {},
   "outputs": [],
   "source": [
    "# ct = ClinicalTrials()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5f94dc3-f00c-4a26-b360-63d46912aab4",
   "metadata": {},
   "source": [
    "### Création de la requête:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "479b8333-7891-4eac-a718-775fb3da2616",
   "metadata": {},
   "source": [
    "On crée la requête qui sera envoyé à l'API de ClinicalTrial"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "979cef8b-2f59-4ffc-b8e1-713c2df54f7d",
   "metadata": {},
   "source": [
    "#### Sponsors:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "fa86d5bc-e8ba-498a-a20d-ba223e05ef89",
   "metadata": {},
   "outputs": [],
   "source": [
    "# sponsors = [\n",
    "#     'anrs',\n",
    "#     'inserm',\n",
    "#     'institut national de la santé et de la recherche médicale',\n",
    "#     'french national agency for research on aids and viral hepatitis',\n",
    "# ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "11a94424-1569-4107-ba6d-6310db1079ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "# sponsors_expr = [f'AREA[LeadSponsorName]{sponsor}' for sponsor in sponsors]\n",
    "\n",
    "# # Add OR keyword\n",
    "# sponsors_expr = ' OR '.join(sponsors_expr)\n",
    "\n",
    "# # Add parenthesis for correct interpretation of OR expression\n",
    "# sponsors_expr = f'({sponsors_expr})'\n",
    "\n",
    "# sponsors_expr"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f673c610-bd07-4441-8954-bb9471b3eb4d",
   "metadata": {},
   "source": [
    "#### Status:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b7faf8d7-45d7-42c9-8769-05b4b161b0d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# status = 'completed'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "718888a1-e9b7-48e6-bb19-d40adbeb12a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# status_expr = f'AREA[OverallStatus]{status}'\n",
    "# status_expr"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32149f4c-89b5-4c66-bd2a-7ac7373add9f",
   "metadata": {},
   "source": [
    "#### Date de fin de l'étude :"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f3a0a01-59f4-439c-bb6d-65ed4e211b5d",
   "metadata": {},
   "source": [
    "<span style=\"color:red\">**Mettre à jour `max_year` en haut de ce document pour la mise à jour annuelle**</span>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "643616d1-3184-459f-9aa9-3677975acf08",
   "metadata": {},
   "outputs": [],
   "source": [
    "# /!\\ US date format\n",
    "# date_expr = f'AREA[CompletionDate]RANGE[01/01/{min_year},12/31/{max_year}]'\n",
    "# date_expr"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0fba5e7-f5ac-4522-87f4-59750f158c9c",
   "metadata": {},
   "source": [
    "#### Search Expression:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "2a93eed1-3da5-483b-9641-93e073d72eb3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# search_expr = ' AND '.join([sponsors_expr, status_expr, date_expr])\n",
    "# search_expr"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5317cc15-f232-43d2-84a0-dffd039866ff",
   "metadata": {},
   "source": [
    "#### URL encode: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "74564518-2bff-4de8-854e-5690d8b3be95",
   "metadata": {},
   "outputs": [],
   "source": [
    "# search_expr_url_encode = urllib.parse.quote_plus(search_expr)\n",
    "# search_expr_url_encode"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4ba1adce-a84f-494b-8724-ee555d5c7d57",
   "metadata": {},
   "source": [
    "#### Fields:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c5114e6f-8606-4109-ac13-62d2cf22975a",
   "metadata": {},
   "source": [
    "Les champs que l'on veut récupérer:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "af5a1984-b35d-4af6-85ca-d276ffe37af5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# fields = [\n",
    "#     'NCTId',\n",
    "#     'BriefTitle',\n",
    "#     'OverallStatus',\n",
    "#     'StudyType',\n",
    "#     'LeadSponsorName',\n",
    "#     'CollaboratorName',\n",
    "#     'OrgStudyId',\n",
    "#     'SecondaryId',\n",
    "#     'StudyFirstPostDate',\n",
    "#     'ReferencePMID',\n",
    "#     'ReferenceCitation',\n",
    "#     'ReferenceType',\n",
    "# ]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "67d2fde1-5386-4773-b10a-90a8ff153dc5",
   "metadata": {},
   "source": [
    "### Envoi de la requête :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "ee115e0b-e412-4465-994e-b25dd746c5f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# study_fields = ct.get_study_fields(\n",
    "#     search_expr=search_expr_url_encode,\n",
    "#     fields=fields,\n",
    "#     max_studies=1000,\n",
    "#     fmt='csv',\n",
    "# )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "0a86dcfc-d52b-4d7c-8041-b3ffac03b982",
   "metadata": {},
   "outputs": [],
   "source": [
    "# print(f'NStudiesReturned: {len(study_fields[1:])}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "17dec420-05d4-4724-b626-931f5f23c115",
   "metadata": {},
   "source": [
    "### Lire le résultat de la requête dans Pandas :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "3de5e52f-2e18-4c00-85f2-75b4c3249706",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "34aced60-0e4c-4fec-8379-37026be31837",
   "metadata": {},
   "outputs": [],
   "source": [
    "# pd.DataFrame.from_records(study_fields[1:], index='Rank', columns=study_fields[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21b09539-cd2a-44e6-85bd-fd1a77c2eac7",
   "metadata": {},
   "source": [
    "## API v2:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e47cc848-f6eb-4c27-8f79-c31d2b6f6a27",
   "metadata": {},
   "source": [
    "L'API v1 ne sera plus supporté a [partir de mi-2024](https://clinicaltrials.gov/data-api/api) :"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ccb8b923-cd0d-4704-af4d-537bc9ff9448",
   "metadata": {},
   "source": [
    ">***Notice to API users:  \n",
    ">The new ClinicalTrials.gov API, version 2.0 is available. Classic API users are strongly encouraged to switch to the modernized API. We will continue to support the classic API until mid-2024 and are planning blackouts for the spring to help with the transition to the modernized API.***"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4760cf2e-125c-4961-bde4-5939a55a9159",
   "metadata": {},
   "source": [
    "De plus, l'API v2 supporte un nouveau champs **\"HasResults\"**, qui pour l'instant n'est que très peu utilisé mais qui pourrait être utile à l'avenir.\n",
    "\n",
    "En contre-partie, l'export des données sous forme de CSV est limité à un certain nombre de champs visible sur cette page: https://clinicaltrials.gov/data-api/about-api/csv-download\n",
    "\n",
    "On est donc obligé d'utiliser l'export de données sous forme de JSON."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "dd0f1b6c-08c4-46a4-bba6-7a4a5254aa3d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import urllib.parse"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cc42bfa7-3115-410a-8244-d1ad36551a3f",
   "metadata": {},
   "source": [
    "### Création de la requête:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f8d8cb3-4485-4899-9080-09fa01fa4e46",
   "metadata": {},
   "source": [
    "`pytrials` n'étant pas compatible avec la v2, on envoie la requête manuellement en utilisant [Requests](https://requests.readthedocs.io/en/latest/) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a5fed10d-6587-43fd-8e93-9dfd937db9e0",
   "metadata": {},
   "source": [
    "#### Format:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "c9c74f1a-8345-44a7-8a5e-e034ed6bac96",
   "metadata": {},
   "outputs": [],
   "source": [
    "format = 'json'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "00b5564b-8a48-4fba-b0eb-f396af6c2f93",
   "metadata": {},
   "source": [
    "#### Sponsors:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "28745196-a342-47e7-9b95-d1f214ba4372",
   "metadata": {},
   "outputs": [],
   "source": [
    "sponsors = [\n",
    "    'anrs',\n",
    "    'inserm',\n",
    "    'institut national de la santé et de la recherche médicale',\n",
    "    'french national agency for research on aids and viral hepatitis',\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "97255a51-e80a-4ded-878e-116f62b761d9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'anrs+OR+inserm+OR+institut+national+de+la+sant%C3%A9+et+de+la+recherche+m%C3%A9dicale+OR+french+national+agency+for+research+on+aids+and+viral+hepatitis'"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sponsors_expr_v2 = ' OR '.join(sponsors)\n",
    "sponsors_expr_v2 = urllib.parse.quote_plus(sponsors_expr_v2)\n",
    "sponsors_expr_v2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2014ccb-b5ba-4bdb-90e0-040900c08c87",
   "metadata": {},
   "source": [
    "#### Overall_status:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "380fae7f-a745-4cfc-93f2-53d64f75717c",
   "metadata": {},
   "outputs": [],
   "source": [
    "overall_status = 'COMPLETED'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7a1431ad-b7a8-4c7c-8f97-cfa13cc1aa61",
   "metadata": {},
   "source": [
    "#### Fields:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "98e9a105-7e78-47cc-b572-b42a67b3aaa7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['NCTId',\n",
       " 'BriefTitle',\n",
       " 'OverallStatus',\n",
       " 'StudyType',\n",
       " 'LeadSponsorName',\n",
       " 'CollaboratorName',\n",
       " 'StudyFirstPostDate',\n",
       " 'StartDate',\n",
       " 'CompletionDate',\n",
       " 'ReferencePMID',\n",
       " 'ReferenceCitation',\n",
       " 'ReferenceType',\n",
       " 'hasResults']"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fields_v2 = [\n",
    "    'NCTId',\n",
    "    'BriefTitle',\n",
    "    # 'OfficialTitle',\n",
    "    'OverallStatus',\n",
    "    'StudyType',\n",
    "    'LeadSponsorName',\n",
    "    'CollaboratorName',\n",
    "    # 'OrgStudyId',\n",
    "    # 'SecondaryId',\n",
    "    'StudyFirstPostDate',\n",
    "    'StartDate',\n",
    "    # 'PrimaryCompletionDate',\n",
    "    'CompletionDate',\n",
    "    'ReferencePMID',\n",
    "    'ReferenceCitation',\n",
    "    'ReferenceType',\n",
    "    'hasResults',\n",
    "]\n",
    "fields_v2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "7830602f-81b0-425c-bc69-f4f96db82948",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'NCTId%2CBriefTitle%2COverallStatus%2CStudyType%2CLeadSponsorName%2CCollaboratorName%2CStudyFirstPostDate%2CStartDate%2CCompletionDate%2CReferencePMID%2CReferenceCitation%2CReferenceType%2ChasResults'"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fields_expr_v2 = ','.join(fields_v2)\n",
    "fields_expr_v2 = urllib.parse.quote_plus(fields_expr_v2)\n",
    "fields_expr_v2"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "047b4eee-4ee5-4691-9c12-68d53ee44db7",
   "metadata": {},
   "source": [
    "#### Date de fin de l'étude :"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bad0c9ed-5ac8-4413-ac32-43daa44c6874",
   "metadata": {},
   "source": [
    "<span style=\"color:red\">**Mettre à jour `max_year` en haut de ce document pour la mise à jour annuelle**</span>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "e12ffc32-06d6-431d-896e-a6b5569a5817",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'AREA%5BCompletionDate%5DRANGE%5B01%2F01%2F2013%2C12%2F31%2F2022%5D'"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# /!\\ US date format\n",
    "date_expr = urllib.parse.quote_plus(\n",
    "    f'AREA[CompletionDate]RANGE[01/01/{min_year},12/31/{max_year}]'\n",
    ")\n",
    "date_expr"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fcd86e2b-ebad-4806-b5f4-c9e283db79f4",
   "metadata": {},
   "source": [
    "#### Nombre de résultats max :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "fd0b7d3d-0a8b-43bc-a078-00464b940972",
   "metadata": {},
   "outputs": [],
   "source": [
    "count_total = 'true'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "46cc35ff-1519-4433-a6bc-a49bc4c28e1a",
   "metadata": {},
   "outputs": [],
   "source": [
    "page_size = 1000"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c6c28d63-9bcf-4de6-bea0-4d143fcc002d",
   "metadata": {},
   "source": [
    "#### URL api:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "42d61bdd-b160-45b2-a782-d3b1f887d631",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'https://clinicaltrials.gov/api/v2/studies?format=json&query.lead=anrs+OR+inserm+OR+institut+national+de+la+sant%C3%A9+et+de+la+recherche+m%C3%A9dicale+OR+french+national+agency+for+research+on+aids+and+viral+hepatitis&filter.overallStatus=COMPLETED&fields=NCTId%2CBriefTitle%2COverallStatus%2CStudyType%2CLeadSponsorName%2CCollaboratorName%2CStudyFirstPostDate%2CStartDate%2CCompletionDate%2CReferencePMID%2CReferenceCitation%2CReferenceType%2ChasResults&filter.advanced=AREA%5BCompletionDate%5DRANGE%5B01%2F01%2F2013%2C12%2F31%2F2022%5D&countTotal=true&pageSize=1000'"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Make sure we don't have more than 1000 results, otherwise we need to handle several page of results\n",
    "query_url = f'https://clinicaltrials.gov/api/v2/studies?format={format}&query.lead={sponsors_expr_v2}&filter.overallStatus={overall_status}&fields={fields_expr_v2}&filter.advanced={date_expr}&countTotal={count_total}&pageSize={page_size}'\n",
    "query_url"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da875083-d048-4247-bba1-e71f88d23d6b",
   "metadata": {},
   "source": [
    "### Envoi de la requête :"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe125310-7a06-4d21-8ec7-19f802355915",
   "metadata": {},
   "source": [
    "Installer [Requests](https://requests.readthedocs.io/en/latest/) et [Requests_cache](https://requests-cache.readthedocs.io/en/stable/index.html), si ils ne sont pas déjà présent:\n",
    "\n",
    "`python -m pip install requests`  \n",
    "`python -m pip install requests-cache`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "5c83ef54-aa5b-417b-9948-0917421dbb92",
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import timedelta\n",
    "# import requests\n",
    "from requests_cache import CachedSession\n",
    "\n",
    "# Add caching to all requests functions using the session\n",
    "session = CachedSession(\n",
    "    cache_control=True,  # Use Cache-Control response headers for expiration, if available\n",
    "    expire_after=timedelta(days=1),  # Otherwise expire responses after one day\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "bfa9259e-3c18-458b-9286-9e16a08123da",
   "metadata": {},
   "outputs": [],
   "source": [
    "response = session.get(query_url)\n",
    "response.raise_for_status()\n",
    "# response"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "83cb6e68-fe6d-4fd8-90f8-bcd57450af69",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Studies returned: 190\n"
     ]
    }
   ],
   "source": [
    "print(f'Studies returned: {response.json()[\"totalCount\"]}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "044debef-36e1-4a84-a483-1e2804c4f79d",
   "metadata": {},
   "source": [
    "### Traitement du JSON retourné:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "9032d2aa-2d5c-4b9f-ba47-a0e591129dc9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "e105fd04-80ac-436b-846c-def4df8c2591",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# print(json.dumps(response.json(), indent=2))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8239b23-3a6b-4541-a657-98ee97e80ea5",
   "metadata": {},
   "source": [
    "***La structure du JSON est bien trop imbriquée pour le normaliser avec Pandas, du coup on l'applatit à la main:***"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1de1fc10-a370-4b0d-8dfc-eaa17dfab0f2",
   "metadata": {},
   "source": [
    "A partir du JSON on construit un dictionnaire équivalent mais beaucoup plus \"plat\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "0abd0476-5bc8-44d7-bcf2-a83fd3917852",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Si la liste des collaborateurs est vide on renvoie None, sinon on concatène les valeurs de la liste\n",
    "# sous la forme \"collaborateur_0 | collaborateur_1 | ...\"\n",
    "def concatenate_collaborator_list(collaborator_list):\n",
    "    if collaborator_list == []:\n",
    "        return None\n",
    "    else:\n",
    "        return ' | '.join(collaborator_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "c0192b08-9e25-4254-a542-b5b8d55d1388",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "studies_list = []\n",
    "for study in response.json()['studies']:\n",
    "    #\n",
    "    study_pro = study['protocolSection']\n",
    "    ##\n",
    "    study_id = study_pro['identificationModule']\n",
    "    study_col = study_pro['sponsorCollaboratorsModule']\n",
    "    study_stat = study_pro['statusModule']\n",
    "\n",
    "    # fmt: off\n",
    "    study_dict = {\n",
    "        'NCTId': study_id['nctId'],\n",
    "        'BriefTitle': study_id['briefTitle'],\n",
    "        'LeadSponsorName': study_col['leadSponsor']['name'],\n",
    "        'CollaboratorName': concatenate_collaborator_list(\n",
    "            [\n",
    "                c['name']\n",
    "                for c in study_col.get('collaborators', [])  # can be missing\n",
    "            ]\n",
    "        ),\n",
    "        'OverallStatus': study_stat['overallStatus'],\n",
    "        'StudyType': study_pro['designModule']['studyType'],\n",
    "        'HasResults': study['hasResults'],\n",
    "        'StudyFirstPostDate': study_stat['studyFirstPostDateStruct']['date'],\n",
    "        'StartDate': (\n",
    "            study_stat\n",
    "            .get('startDateStruct', {})\n",
    "            .get('date', None)  # can be missing\n",
    "        ),\n",
    "        # 'PrimaryCompletionDate' : (\n",
    "        #     study_stat\n",
    "        #     .get('primaryCompletionDateStruct', {})\n",
    "        #     .get('date', None), # can be missing\n",
    "        # ),\n",
    "        'CompletionDate': (\n",
    "            study_stat\n",
    "            .get('completionDateStruct', {})\n",
    "            .get('date', None)  # can be missing\n",
    "        ),\n",
    "        'Reference': (\n",
    "            study_pro\n",
    "            .get('referencesModule', {})\n",
    "            .get('references', [])  # can be missing\n",
    "        ),\n",
    "    }\n",
    "    # fmt: on\n",
    "    studies_list.append(study_dict)\n",
    "\n",
    "# print(json.dumps(studies_list, indent=2))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "79beae31-da09-4108-8900-26d423062495",
   "metadata": {},
   "source": [
    "On vérifie que l'on n'a pas perdu de NCTId en route:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "591c6902-e73c-4db6-b27c-89d68e06e2c0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Nombre de NCTId: 190\n"
     ]
    }
   ],
   "source": [
    "print(f'Nombre de NCTId: {len(studies_list)}')\n",
    "assert response.json()['totalCount'] == len(studies_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2536328d-746c-4fab-8245-d498eaa2fc55",
   "metadata": {},
   "source": [
    "### Import dans Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "d63775da-9863-4d80-becc-d9cb776e75f2",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "a6866a1d-8c4a-4992-815c-66faf4a42a46",
   "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>NCTId</th>\n",
       "      <th>BriefTitle</th>\n",
       "      <th>LeadSponsorName</th>\n",
       "      <th>CollaboratorName</th>\n",
       "      <th>OverallStatus</th>\n",
       "      <th>StudyType</th>\n",
       "      <th>HasResults</th>\n",
       "      <th>StudyFirstPostDate</th>\n",
       "      <th>StartDate</th>\n",
       "      <th>CompletionDate</th>\n",
       "      <th>Reference</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NCT02099474</td>\n",
       "      <td>Evaluation of Raltegravir During the Third Tri...</td>\n",
       "      <td>ANRS, Emerging Infectious Diseases</td>\n",
       "      <td>None</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2014-03-31</td>\n",
       "      <td>2014-06-30</td>\n",
       "      <td>2017-04</td>\n",
       "      <td>[{'pmid': '32661003', 'type': 'DERIVED', 'cita...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NCT02497274</td>\n",
       "      <td>Obesity and Lipids: a Matter of Taste?</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>None</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2015-07-14</td>\n",
       "      <td>2016-01-11</td>\n",
       "      <td>2019-01</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NCT02592174</td>\n",
       "      <td>Prevalence, Characteristics and Risk Factors o...</td>\n",
       "      <td>ANRS, Emerging Infectious Diseases</td>\n",
       "      <td>Merck Sharp &amp; Dohme LLC</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2015-10-30</td>\n",
       "      <td>2016-01-26</td>\n",
       "      <td>2017-10-27</td>\n",
       "      <td>[{'pmid': '31755936', 'type': 'DERIVED', 'cita...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NCT01490489</td>\n",
       "      <td>EG-VEGF : Potential Marker of Pre-eclampsia an...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>None</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2011-12-13</td>\n",
       "      <td>2011-07-11</td>\n",
       "      <td>2015-03-13</td>\n",
       "      <td>[{'pmid': '17531315', 'type': 'RESULT', 'citat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NCT01552044</td>\n",
       "      <td>Effect of Spironolactone in Treating Chronic N...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>None</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2012-03-13</td>\n",
       "      <td>2012-01</td>\n",
       "      <td>2013-02</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>NCT01037777</td>\n",
       "      <td>RISCA : Prospective Study of Individuals at Ri...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>None</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>OBSERVATIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2009-12-23</td>\n",
       "      <td>2009-05-07</td>\n",
       "      <td>2017-12-14</td>\n",
       "      <td>[{'pmid': '35264424', 'type': 'DERIVED', 'cita...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>186</th>\n",
       "      <td>NCT02833961</td>\n",
       "      <td>Diffusion Spectroscopy in Stroke</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>None</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2016-07-14</td>\n",
       "      <td>2016-07-28</td>\n",
       "      <td>2019-10-25</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187</th>\n",
       "      <td>NCT02976298</td>\n",
       "      <td>Effects of Cerebellum or Supplementary Motor A...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>None</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2016-11-29</td>\n",
       "      <td>2013-02</td>\n",
       "      <td>2015-01</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>188</th>\n",
       "      <td>NCT01842477</td>\n",
       "      <td>Evaluation of Efficacy and Safety of Autologou...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>None</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2013-04-29</td>\n",
       "      <td>2013-05</td>\n",
       "      <td>2016-02-05</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189</th>\n",
       "      <td>NCT02721745</td>\n",
       "      <td>Cause and Consequence of Neural Fatigue</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>None</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2016-03-29</td>\n",
       "      <td>2016-05-23</td>\n",
       "      <td>2020-12</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>190 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           NCTId                                         BriefTitle  \\\n",
       "0    NCT02099474  Evaluation of Raltegravir During the Third Tri...   \n",
       "1    NCT02497274             Obesity and Lipids: a Matter of Taste?   \n",
       "2    NCT02592174  Prevalence, Characteristics and Risk Factors o...   \n",
       "3    NCT01490489  EG-VEGF : Potential Marker of Pre-eclampsia an...   \n",
       "4    NCT01552044  Effect of Spironolactone in Treating Chronic N...   \n",
       "..           ...                                                ...   \n",
       "185  NCT01037777  RISCA : Prospective Study of Individuals at Ri...   \n",
       "186  NCT02833961                   Diffusion Spectroscopy in Stroke   \n",
       "187  NCT02976298  Effects of Cerebellum or Supplementary Motor A...   \n",
       "188  NCT01842477  Evaluation of Efficacy and Safety of Autologou...   \n",
       "189  NCT02721745            Cause and Consequence of Neural Fatigue   \n",
       "\n",
       "                                       LeadSponsorName  \\\n",
       "0                   ANRS, Emerging Infectious Diseases   \n",
       "1    Institut National de la Santé Et de la Recherc...   \n",
       "2                   ANRS, Emerging Infectious Diseases   \n",
       "3    Institut National de la Santé Et de la Recherc...   \n",
       "4    Institut National de la Santé Et de la Recherc...   \n",
       "..                                                 ...   \n",
       "185  Institut National de la Santé Et de la Recherc...   \n",
       "186  Institut National de la Santé Et de la Recherc...   \n",
       "187  Institut National de la Santé Et de la Recherc...   \n",
       "188  Institut National de la Santé Et de la Recherc...   \n",
       "189  Institut National de la Santé Et de la Recherc...   \n",
       "\n",
       "            CollaboratorName OverallStatus       StudyType  HasResults  \\\n",
       "0                       None     COMPLETED  INTERVENTIONAL       False   \n",
       "1                       None     COMPLETED  INTERVENTIONAL       False   \n",
       "2    Merck Sharp & Dohme LLC     COMPLETED  INTERVENTIONAL       False   \n",
       "3                       None     COMPLETED  INTERVENTIONAL       False   \n",
       "4                       None     COMPLETED  INTERVENTIONAL       False   \n",
       "..                       ...           ...             ...         ...   \n",
       "185                     None     COMPLETED   OBSERVATIONAL       False   \n",
       "186                     None     COMPLETED  INTERVENTIONAL       False   \n",
       "187                     None     COMPLETED  INTERVENTIONAL       False   \n",
       "188                     None     COMPLETED  INTERVENTIONAL       False   \n",
       "189                     None     COMPLETED  INTERVENTIONAL       False   \n",
       "\n",
       "    StudyFirstPostDate   StartDate CompletionDate  \\\n",
       "0           2014-03-31  2014-06-30        2017-04   \n",
       "1           2015-07-14  2016-01-11        2019-01   \n",
       "2           2015-10-30  2016-01-26     2017-10-27   \n",
       "3           2011-12-13  2011-07-11     2015-03-13   \n",
       "4           2012-03-13     2012-01        2013-02   \n",
       "..                 ...         ...            ...   \n",
       "185         2009-12-23  2009-05-07     2017-12-14   \n",
       "186         2016-07-14  2016-07-28     2019-10-25   \n",
       "187         2016-11-29     2013-02        2015-01   \n",
       "188         2013-04-29     2013-05     2016-02-05   \n",
       "189         2016-03-29  2016-05-23        2020-12   \n",
       "\n",
       "                                             Reference  \n",
       "0    [{'pmid': '32661003', 'type': 'DERIVED', 'cita...  \n",
       "1                                                   []  \n",
       "2    [{'pmid': '31755936', 'type': 'DERIVED', 'cita...  \n",
       "3    [{'pmid': '17531315', 'type': 'RESULT', 'citat...  \n",
       "4                                                   []  \n",
       "..                                                 ...  \n",
       "185  [{'pmid': '35264424', 'type': 'DERIVED', 'cita...  \n",
       "186                                                 []  \n",
       "187                                                 []  \n",
       "188                                                 []  \n",
       "189                                                 []  \n",
       "\n",
       "[190 rows x 11 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ct = pd.json_normalize(data=studies_list)\n",
    "df_ct"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bcf7da5f-f67d-4cf6-ab62-136eae59d865",
   "metadata": {},
   "source": [
    "#### On \"explose\" la colonne \"References\":\n",
    "\n",
    "Pour chaque NCTId, la colonne réferences contient potentiellement une liste de plusieurs références.  \n",
    "Si par exemple, on a 3 références, on veut se retrouver avec 3 lignes, chacune indexée par le même NCTId et contenant une unique réference.  \n",
    "\n",
    "*Avant*:  \n",
    "**`NCTId    References`**  \n",
    "`NCT0001, [Ref1, Ref2, Ref3]`   \n",
    "\n",
    "*Après*:  \n",
    "**`NCTId    References`**  \n",
    "`NCT0001,   Ref1`  \n",
    "`NCT0001,   Ref2`  \n",
    "`NCT0001,   Ref3`   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "3af5b2f7-9a14-4d85-823f-5335ec9d66e7",
   "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>NCTId</th>\n",
       "      <th>Reference</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NCT02099474</td>\n",
       "      <td>{'pmid': '32661003', 'type': 'DERIVED', 'citat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NCT02497274</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NCT02592174</td>\n",
       "      <td>{'pmid': '31755936', 'type': 'DERIVED', 'citat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NCT01490489</td>\n",
       "      <td>{'pmid': '17531315', 'type': 'RESULT', 'citati...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NCT01490489</td>\n",
       "      <td>{'pmid': '16384869', 'type': 'RESULT', 'citati...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>448</th>\n",
       "      <td>NCT01037777</td>\n",
       "      <td>{'pmid': '23707147', 'type': 'DERIVED', 'citat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449</th>\n",
       "      <td>NCT02833961</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450</th>\n",
       "      <td>NCT02976298</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>451</th>\n",
       "      <td>NCT01842477</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>452</th>\n",
       "      <td>NCT02721745</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>453 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           NCTId                                          Reference\n",
       "0    NCT02099474  {'pmid': '32661003', 'type': 'DERIVED', 'citat...\n",
       "1    NCT02497274                                                NaN\n",
       "2    NCT02592174  {'pmid': '31755936', 'type': 'DERIVED', 'citat...\n",
       "3    NCT01490489  {'pmid': '17531315', 'type': 'RESULT', 'citati...\n",
       "4    NCT01490489  {'pmid': '16384869', 'type': 'RESULT', 'citati...\n",
       "..           ...                                                ...\n",
       "448  NCT01037777  {'pmid': '23707147', 'type': 'DERIVED', 'citat...\n",
       "449  NCT02833961                                                NaN\n",
       "450  NCT02976298                                                NaN\n",
       "451  NCT01842477                                                NaN\n",
       "452  NCT02721745                                                NaN\n",
       "\n",
       "[453 rows x 2 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ct = df_ct.explode('Reference', ignore_index=True)\n",
    "df_ct.loc[:, ['NCTId', 'Reference']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8a141674-4974-432d-8c4a-4b1c0b9f9970",
   "metadata": {},
   "source": [
    "Pour chaque NCTId, la colonne réference contient maintenant un dictionnaire de la forme:  \n",
    "`{  \n",
    "\"pmid\": \"17545707\",  \n",
    "\"type\": \"BACKGROUND\",  \n",
    "\"citation\": \"...\",  \n",
    "}`\n",
    "\n",
    "Dont on veut extraire de nouvelles colonnes en utilant les clés du dictionnaire:  \n",
    "**`pmid     type        citation`**  \n",
    "`17545707, BACKGROUND, \"...\"`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "0b7f8365-bbf5-4e6a-96b0-23946df93a8b",
   "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>pmid</th>\n",
       "      <th>type</th>\n",
       "      <th>citation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>32661003</td>\n",
       "      <td>DERIVED</td>\n",
       "      <td>Zheng Y, Hirt D, Delmas S, Lui G, Benaboud S, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>31755936</td>\n",
       "      <td>DERIVED</td>\n",
       "      <td>Makinson A, Dubois J, Eymard-Duvernay S, Lecle...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17531315</td>\n",
       "      <td>RESULT</td>\n",
       "      <td>Hoffmann P, Feige JJ, Alfaidy N. Placental exp...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>16384869</td>\n",
       "      <td>RESULT</td>\n",
       "      <td>Hoffmann P, Feige JJ, Alfaidy N. Expression an...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>448</th>\n",
       "      <td>23707147</td>\n",
       "      <td>DERIVED</td>\n",
       "      <td>Jacobi H, Reetz K, du Montcel ST, Bauer P, Mar...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>451</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>452</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>453 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         pmid     type                                           citation\n",
       "0    32661003  DERIVED  Zheng Y, Hirt D, Delmas S, Lui G, Benaboud S, ...\n",
       "1         NaN      NaN                                                NaN\n",
       "2    31755936  DERIVED  Makinson A, Dubois J, Eymard-Duvernay S, Lecle...\n",
       "3    17531315   RESULT  Hoffmann P, Feige JJ, Alfaidy N. Placental exp...\n",
       "4    16384869   RESULT  Hoffmann P, Feige JJ, Alfaidy N. Expression an...\n",
       "..        ...      ...                                                ...\n",
       "448  23707147  DERIVED  Jacobi H, Reetz K, du Montcel ST, Bauer P, Mar...\n",
       "449       NaN      NaN                                                NaN\n",
       "450       NaN      NaN                                                NaN\n",
       "451       NaN      NaN                                                NaN\n",
       "452       NaN      NaN                                                NaN\n",
       "\n",
       "[453 rows x 3 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ct_references = pd.json_normalize(df_ct.pop('Reference'))\n",
    "df_ct_references"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb7ea35e-3816-48f8-b91a-526e99768b8e",
   "metadata": {},
   "source": [
    "On réassemble la dataFrame complète:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "0f55da6b-f2f3-4554-88b0-dac5850614d4",
   "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>NCTId</th>\n",
       "      <th>BriefTitle</th>\n",
       "      <th>LeadSponsorName</th>\n",
       "      <th>CollaboratorName</th>\n",
       "      <th>OverallStatus</th>\n",
       "      <th>StudyType</th>\n",
       "      <th>HasResults</th>\n",
       "      <th>StudyFirstPostDate</th>\n",
       "      <th>StartDate</th>\n",
       "      <th>CompletionDate</th>\n",
       "      <th>pmid</th>\n",
       "      <th>type</th>\n",
       "      <th>citation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NCT02099474</td>\n",
       "      <td>Evaluation of Raltegravir During the Third Tri...</td>\n",
       "      <td>ANRS, Emerging Infectious Diseases</td>\n",
       "      <td>None</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2014-03-31</td>\n",
       "      <td>2014-06-30</td>\n",
       "      <td>2017-04</td>\n",
       "      <td>32661003</td>\n",
       "      <td>DERIVED</td>\n",
       "      <td>Zheng Y, Hirt D, Delmas S, Lui G, Benaboud S, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NCT02497274</td>\n",
       "      <td>Obesity and Lipids: a Matter of Taste?</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>None</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2015-07-14</td>\n",
       "      <td>2016-01-11</td>\n",
       "      <td>2019-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NCT02592174</td>\n",
       "      <td>Prevalence, Characteristics and Risk Factors o...</td>\n",
       "      <td>ANRS, Emerging Infectious Diseases</td>\n",
       "      <td>Merck Sharp &amp; Dohme LLC</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2015-10-30</td>\n",
       "      <td>2016-01-26</td>\n",
       "      <td>2017-10-27</td>\n",
       "      <td>31755936</td>\n",
       "      <td>DERIVED</td>\n",
       "      <td>Makinson A, Dubois J, Eymard-Duvernay S, Lecle...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NCT01490489</td>\n",
       "      <td>EG-VEGF : Potential Marker of Pre-eclampsia an...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>None</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2011-12-13</td>\n",
       "      <td>2011-07-11</td>\n",
       "      <td>2015-03-13</td>\n",
       "      <td>17531315</td>\n",
       "      <td>RESULT</td>\n",
       "      <td>Hoffmann P, Feige JJ, Alfaidy N. Placental exp...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NCT01490489</td>\n",
       "      <td>EG-VEGF : Potential Marker of Pre-eclampsia an...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>None</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2011-12-13</td>\n",
       "      <td>2011-07-11</td>\n",
       "      <td>2015-03-13</td>\n",
       "      <td>16384869</td>\n",
       "      <td>RESULT</td>\n",
       "      <td>Hoffmann P, Feige JJ, Alfaidy N. Expression an...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>448</th>\n",
       "      <td>NCT01037777</td>\n",
       "      <td>RISCA : Prospective Study of Individuals at Ri...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>None</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>OBSERVATIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2009-12-23</td>\n",
       "      <td>2009-05-07</td>\n",
       "      <td>2017-12-14</td>\n",
       "      <td>23707147</td>\n",
       "      <td>DERIVED</td>\n",
       "      <td>Jacobi H, Reetz K, du Montcel ST, Bauer P, Mar...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449</th>\n",
       "      <td>NCT02833961</td>\n",
       "      <td>Diffusion Spectroscopy in Stroke</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>None</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2016-07-14</td>\n",
       "      <td>2016-07-28</td>\n",
       "      <td>2019-10-25</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450</th>\n",
       "      <td>NCT02976298</td>\n",
       "      <td>Effects of Cerebellum or Supplementary Motor A...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>None</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2016-11-29</td>\n",
       "      <td>2013-02</td>\n",
       "      <td>2015-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>451</th>\n",
       "      <td>NCT01842477</td>\n",
       "      <td>Evaluation of Efficacy and Safety of Autologou...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>None</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2013-04-29</td>\n",
       "      <td>2013-05</td>\n",
       "      <td>2016-02-05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>452</th>\n",
       "      <td>NCT02721745</td>\n",
       "      <td>Cause and Consequence of Neural Fatigue</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>None</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2016-03-29</td>\n",
       "      <td>2016-05-23</td>\n",
       "      <td>2020-12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>453 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           NCTId                                         BriefTitle  \\\n",
       "0    NCT02099474  Evaluation of Raltegravir During the Third Tri...   \n",
       "1    NCT02497274             Obesity and Lipids: a Matter of Taste?   \n",
       "2    NCT02592174  Prevalence, Characteristics and Risk Factors o...   \n",
       "3    NCT01490489  EG-VEGF : Potential Marker of Pre-eclampsia an...   \n",
       "4    NCT01490489  EG-VEGF : Potential Marker of Pre-eclampsia an...   \n",
       "..           ...                                                ...   \n",
       "448  NCT01037777  RISCA : Prospective Study of Individuals at Ri...   \n",
       "449  NCT02833961                   Diffusion Spectroscopy in Stroke   \n",
       "450  NCT02976298  Effects of Cerebellum or Supplementary Motor A...   \n",
       "451  NCT01842477  Evaluation of Efficacy and Safety of Autologou...   \n",
       "452  NCT02721745            Cause and Consequence of Neural Fatigue   \n",
       "\n",
       "                                       LeadSponsorName  \\\n",
       "0                   ANRS, Emerging Infectious Diseases   \n",
       "1    Institut National de la Santé Et de la Recherc...   \n",
       "2                   ANRS, Emerging Infectious Diseases   \n",
       "3    Institut National de la Santé Et de la Recherc...   \n",
       "4    Institut National de la Santé Et de la Recherc...   \n",
       "..                                                 ...   \n",
       "448  Institut National de la Santé Et de la Recherc...   \n",
       "449  Institut National de la Santé Et de la Recherc...   \n",
       "450  Institut National de la Santé Et de la Recherc...   \n",
       "451  Institut National de la Santé Et de la Recherc...   \n",
       "452  Institut National de la Santé Et de la Recherc...   \n",
       "\n",
       "            CollaboratorName OverallStatus       StudyType  HasResults  \\\n",
       "0                       None     COMPLETED  INTERVENTIONAL       False   \n",
       "1                       None     COMPLETED  INTERVENTIONAL       False   \n",
       "2    Merck Sharp & Dohme LLC     COMPLETED  INTERVENTIONAL       False   \n",
       "3                       None     COMPLETED  INTERVENTIONAL       False   \n",
       "4                       None     COMPLETED  INTERVENTIONAL       False   \n",
       "..                       ...           ...             ...         ...   \n",
       "448                     None     COMPLETED   OBSERVATIONAL       False   \n",
       "449                     None     COMPLETED  INTERVENTIONAL       False   \n",
       "450                     None     COMPLETED  INTERVENTIONAL       False   \n",
       "451                     None     COMPLETED  INTERVENTIONAL       False   \n",
       "452                     None     COMPLETED  INTERVENTIONAL       False   \n",
       "\n",
       "    StudyFirstPostDate   StartDate CompletionDate      pmid     type  \\\n",
       "0           2014-03-31  2014-06-30        2017-04  32661003  DERIVED   \n",
       "1           2015-07-14  2016-01-11        2019-01       NaN      NaN   \n",
       "2           2015-10-30  2016-01-26     2017-10-27  31755936  DERIVED   \n",
       "3           2011-12-13  2011-07-11     2015-03-13  17531315   RESULT   \n",
       "4           2011-12-13  2011-07-11     2015-03-13  16384869   RESULT   \n",
       "..                 ...         ...            ...       ...      ...   \n",
       "448         2009-12-23  2009-05-07     2017-12-14  23707147  DERIVED   \n",
       "449         2016-07-14  2016-07-28     2019-10-25       NaN      NaN   \n",
       "450         2016-11-29     2013-02        2015-01       NaN      NaN   \n",
       "451         2013-04-29     2013-05     2016-02-05       NaN      NaN   \n",
       "452         2016-03-29  2016-05-23        2020-12       NaN      NaN   \n",
       "\n",
       "                                              citation  \n",
       "0    Zheng Y, Hirt D, Delmas S, Lui G, Benaboud S, ...  \n",
       "1                                                  NaN  \n",
       "2    Makinson A, Dubois J, Eymard-Duvernay S, Lecle...  \n",
       "3    Hoffmann P, Feige JJ, Alfaidy N. Placental exp...  \n",
       "4    Hoffmann P, Feige JJ, Alfaidy N. Expression an...  \n",
       "..                                                 ...  \n",
       "448  Jacobi H, Reetz K, du Montcel ST, Bauer P, Mar...  \n",
       "449                                                NaN  \n",
       "450                                                NaN  \n",
       "451                                                NaN  \n",
       "452                                                NaN  \n",
       "\n",
       "[453 rows x 13 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ct = df_ct.join(\n",
    "    df_ct_references,\n",
    "    validate='one_to_one',\n",
    ")\n",
    "df_ct"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e42f325-90f5-415a-a500-e4caea12146a",
   "metadata": {},
   "source": [
    "***On reconstruit l'index :***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "a76d82bd-7c03-4a42-bca1-1d56efa90532",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_studies_v2.set_index('NCTId', inplace = True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd0416a3-4af4-45d2-b4a0-167004409a31",
   "metadata": {},
   "source": [
    "***On précise les types :***"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "b191aeb2-c4cb-431d-a7d1-074f30454ad7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 453 entries, 0 to 452\n",
      "Data columns (total 13 columns):\n",
      " #   Column              Non-Null Count  Dtype   \n",
      "---  ------              --------------  -----   \n",
      " 0   NCTId               453 non-null    string  \n",
      " 1   BriefTitle          453 non-null    string  \n",
      " 2   LeadSponsorName     453 non-null    string  \n",
      " 3   CollaboratorName    212 non-null    string  \n",
      " 4   OverallStatus       453 non-null    category\n",
      " 5   StudyType           453 non-null    category\n",
      " 6   HasResults          453 non-null    boolean \n",
      " 7   StudyFirstPostDate  453 non-null    string  \n",
      " 8   StartDate           453 non-null    string  \n",
      " 9   CompletionDate      453 non-null    string  \n",
      " 10  pmid                342 non-null    string  \n",
      " 11  type                342 non-null    category\n",
      " 12  citation            342 non-null    string  \n",
      "dtypes: boolean(1), category(3), string(9)\n",
      "memory usage: 34.6 KB\n"
     ]
    }
   ],
   "source": [
    "df_ct = df_ct.convert_dtypes()\n",
    "df_ct = df_ct.astype(\n",
    "    {\n",
    "        'OverallStatus': 'category',\n",
    "        'StudyType': 'category',\n",
    "        'type': 'category',\n",
    "    }\n",
    ")\n",
    "df_ct.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "c5c3a664-539b-40d0-af12-fdc0d7f7f3ff",
   "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>NCTId</th>\n",
       "      <th>BriefTitle</th>\n",
       "      <th>LeadSponsorName</th>\n",
       "      <th>CollaboratorName</th>\n",
       "      <th>OverallStatus</th>\n",
       "      <th>StudyType</th>\n",
       "      <th>HasResults</th>\n",
       "      <th>StudyFirstPostDate</th>\n",
       "      <th>StartDate</th>\n",
       "      <th>CompletionDate</th>\n",
       "      <th>pmid</th>\n",
       "      <th>type</th>\n",
       "      <th>citation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NCT02099474</td>\n",
       "      <td>Evaluation of Raltegravir During the Third Tri...</td>\n",
       "      <td>ANRS, Emerging Infectious Diseases</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2014-03-31</td>\n",
       "      <td>2014-06-30</td>\n",
       "      <td>2017-04</td>\n",
       "      <td>32661003</td>\n",
       "      <td>DERIVED</td>\n",
       "      <td>Zheng Y, Hirt D, Delmas S, Lui G, Benaboud S, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NCT02497274</td>\n",
       "      <td>Obesity and Lipids: a Matter of Taste?</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2015-07-14</td>\n",
       "      <td>2016-01-11</td>\n",
       "      <td>2019-01</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NCT02592174</td>\n",
       "      <td>Prevalence, Characteristics and Risk Factors o...</td>\n",
       "      <td>ANRS, Emerging Infectious Diseases</td>\n",
       "      <td>Merck Sharp &amp; Dohme LLC</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2015-10-30</td>\n",
       "      <td>2016-01-26</td>\n",
       "      <td>2017-10-27</td>\n",
       "      <td>31755936</td>\n",
       "      <td>DERIVED</td>\n",
       "      <td>Makinson A, Dubois J, Eymard-Duvernay S, Lecle...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NCT01490489</td>\n",
       "      <td>EG-VEGF : Potential Marker of Pre-eclampsia an...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2011-12-13</td>\n",
       "      <td>2011-07-11</td>\n",
       "      <td>2015-03-13</td>\n",
       "      <td>17531315</td>\n",
       "      <td>RESULT</td>\n",
       "      <td>Hoffmann P, Feige JJ, Alfaidy N. Placental exp...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NCT01490489</td>\n",
       "      <td>EG-VEGF : Potential Marker of Pre-eclampsia an...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2011-12-13</td>\n",
       "      <td>2011-07-11</td>\n",
       "      <td>2015-03-13</td>\n",
       "      <td>16384869</td>\n",
       "      <td>RESULT</td>\n",
       "      <td>Hoffmann P, Feige JJ, Alfaidy N. Expression an...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>448</th>\n",
       "      <td>NCT01037777</td>\n",
       "      <td>RISCA : Prospective Study of Individuals at Ri...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>OBSERVATIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2009-12-23</td>\n",
       "      <td>2009-05-07</td>\n",
       "      <td>2017-12-14</td>\n",
       "      <td>23707147</td>\n",
       "      <td>DERIVED</td>\n",
       "      <td>Jacobi H, Reetz K, du Montcel ST, Bauer P, Mar...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449</th>\n",
       "      <td>NCT02833961</td>\n",
       "      <td>Diffusion Spectroscopy in Stroke</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2016-07-14</td>\n",
       "      <td>2016-07-28</td>\n",
       "      <td>2019-10-25</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>450</th>\n",
       "      <td>NCT02976298</td>\n",
       "      <td>Effects of Cerebellum or Supplementary Motor A...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2016-11-29</td>\n",
       "      <td>2013-02</td>\n",
       "      <td>2015-01</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>451</th>\n",
       "      <td>NCT01842477</td>\n",
       "      <td>Evaluation of Efficacy and Safety of Autologou...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2013-04-29</td>\n",
       "      <td>2013-05</td>\n",
       "      <td>2016-02-05</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>452</th>\n",
       "      <td>NCT02721745</td>\n",
       "      <td>Cause and Consequence of Neural Fatigue</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2016-03-29</td>\n",
       "      <td>2016-05-23</td>\n",
       "      <td>2020-12</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>453 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           NCTId                                         BriefTitle  \\\n",
       "0    NCT02099474  Evaluation of Raltegravir During the Third Tri...   \n",
       "1    NCT02497274             Obesity and Lipids: a Matter of Taste?   \n",
       "2    NCT02592174  Prevalence, Characteristics and Risk Factors o...   \n",
       "3    NCT01490489  EG-VEGF : Potential Marker of Pre-eclampsia an...   \n",
       "4    NCT01490489  EG-VEGF : Potential Marker of Pre-eclampsia an...   \n",
       "..           ...                                                ...   \n",
       "448  NCT01037777  RISCA : Prospective Study of Individuals at Ri...   \n",
       "449  NCT02833961                   Diffusion Spectroscopy in Stroke   \n",
       "450  NCT02976298  Effects of Cerebellum or Supplementary Motor A...   \n",
       "451  NCT01842477  Evaluation of Efficacy and Safety of Autologou...   \n",
       "452  NCT02721745            Cause and Consequence of Neural Fatigue   \n",
       "\n",
       "                                       LeadSponsorName  \\\n",
       "0                   ANRS, Emerging Infectious Diseases   \n",
       "1    Institut National de la Santé Et de la Recherc...   \n",
       "2                   ANRS, Emerging Infectious Diseases   \n",
       "3    Institut National de la Santé Et de la Recherc...   \n",
       "4    Institut National de la Santé Et de la Recherc...   \n",
       "..                                                 ...   \n",
       "448  Institut National de la Santé Et de la Recherc...   \n",
       "449  Institut National de la Santé Et de la Recherc...   \n",
       "450  Institut National de la Santé Et de la Recherc...   \n",
       "451  Institut National de la Santé Et de la Recherc...   \n",
       "452  Institut National de la Santé Et de la Recherc...   \n",
       "\n",
       "            CollaboratorName OverallStatus       StudyType  HasResults  \\\n",
       "0                       <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "1                       <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "2    Merck Sharp & Dohme LLC     COMPLETED  INTERVENTIONAL       False   \n",
       "3                       <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "4                       <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "..                       ...           ...             ...         ...   \n",
       "448                     <NA>     COMPLETED   OBSERVATIONAL       False   \n",
       "449                     <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "450                     <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "451                     <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "452                     <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "\n",
       "    StudyFirstPostDate   StartDate CompletionDate      pmid     type  \\\n",
       "0           2014-03-31  2014-06-30        2017-04  32661003  DERIVED   \n",
       "1           2015-07-14  2016-01-11        2019-01      <NA>     <NA>   \n",
       "2           2015-10-30  2016-01-26     2017-10-27  31755936  DERIVED   \n",
       "3           2011-12-13  2011-07-11     2015-03-13  17531315   RESULT   \n",
       "4           2011-12-13  2011-07-11     2015-03-13  16384869   RESULT   \n",
       "..                 ...         ...            ...       ...      ...   \n",
       "448         2009-12-23  2009-05-07     2017-12-14  23707147  DERIVED   \n",
       "449         2016-07-14  2016-07-28     2019-10-25      <NA>     <NA>   \n",
       "450         2016-11-29     2013-02        2015-01      <NA>     <NA>   \n",
       "451         2013-04-29     2013-05     2016-02-05      <NA>     <NA>   \n",
       "452         2016-03-29  2016-05-23        2020-12      <NA>     <NA>   \n",
       "\n",
       "                                              citation  \n",
       "0    Zheng Y, Hirt D, Delmas S, Lui G, Benaboud S, ...  \n",
       "1                                                 <NA>  \n",
       "2    Makinson A, Dubois J, Eymard-Duvernay S, Lecle...  \n",
       "3    Hoffmann P, Feige JJ, Alfaidy N. Placental exp...  \n",
       "4    Hoffmann P, Feige JJ, Alfaidy N. Expression an...  \n",
       "..                                                 ...  \n",
       "448  Jacobi H, Reetz K, du Montcel ST, Bauer P, Mar...  \n",
       "449                                               <NA>  \n",
       "450                                               <NA>  \n",
       "451                                               <NA>  \n",
       "452                                               <NA>  \n",
       "\n",
       "[453 rows x 13 columns]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ct"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7b5fc9d-ac46-475b-b842-6fb462dd4860",
   "metadata": {},
   "source": [
    "#### Export en CSV :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "48250189-42cc-4bc8-ab9d-f143850c541d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_ct.to_csv(\n",
    "#     'Data/outputs/extract_CT_api_v2.csv',\n",
    "#     sep=';',\n",
    "#     index=False,\n",
    "#     encoding='utf-8-sig',\n",
    "# )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4754b32-ee8e-4b1c-b634-7177c4e273ab",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "5dc82c20-529d-463f-a5ae-7a8ba6a07684",
   "metadata": {},
   "source": [
    "# PubMed"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cde7add4-bea7-4155-a522-a4bc75979ebd",
   "metadata": {},
   "source": [
    "### Utilisation d'une clé pour l'API Pubmed : "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "9679e4c2-6f3c-4754-bd37-44646d6f7633",
   "metadata": {},
   "source": [
    "Il est recommandé d'utiliser une clé pour accéder à l'API Pubmed, ce qui permet de lancer jusqu'à 10 requêtes par seconde.  \n",
    "Sans clé, la limite est de 3 requêtes par seconde.  \n",
    "\n",
    "> E-utils users are allowed 3 requests/second without an API key. Create an API key to increase your e-utils limit to 10 requests/second.\n",
    "\n",
    "**En pratique, l'API Pubmed étant beaucoup plus lente, cela ne semble pas changer grand chose.**\n",
    "\n",
    "Pour récupérer sa clé, se rendre sur la page en étant loggué:\n",
    "https://account.ncbi.nlm.nih.gov/settings/\n",
    "\n",
    "Une fois la clé récupérée, l'ajouter aux variables d'environnement avec la commande suivante dans un Terminal:\n",
    "\n",
    "**Windows :** \n",
    "\n",
    "`setx NCBI_API_KEY “123456\"`\n",
    "\n",
    "**Linux/MacOS :**\n",
    "\n",
    "`export NCBI_API_KEY=123456`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "ccc06834-ea85-48c2-b66b-f9ef01329ec7",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "assert os.getenv('NCBI_API_KEY', None) is not None"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7103b475-2af0-4a49-9581-4f8196e29d87",
   "metadata": {},
   "source": [
    "### Récupération des PMIDs via PubMed:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9bc92598-3bee-4975-8fd2-efe55de4050d",
   "metadata": {},
   "source": [
    "Pour chaque NCTId de CT, on récupère les PMIDs des publications associées via PubMed:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "36cb49cf-5ccb-47a6-a4c2-0e722e985c38",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "190/190..."
     ]
    }
   ],
   "source": [
    "# Liste unique des NCTId extraits de ClinicalTrial\n",
    "nctids = df_ct.loc[:, 'NCTId'].unique()\n",
    "num_nctids = len(nctids)\n",
    "\n",
    "pmids_pubmed_dict = {}\n",
    "for i, nctid in enumerate(nctids):\n",
    "    # Display the progress on a single line\n",
    "    print(f'\\r{i+1}/{num_nctids}...', end='', flush=True)\n",
    "\n",
    "    # Query Pubmed's API\n",
    "    query_url = f'https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&retmode=json&term={nctid}&api_key={os.getenv('NCBI_API_KEY')}'\n",
    "    response = session.get(query_url)\n",
    "    response.raise_for_status()\n",
    "\n",
    "    pmids = response.json()['esearchresult']['idlist']\n",
    "    pmids_pubmed_dict[nctid] = set(pmids)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "6724fc4b-eff7-4c44-b5c8-63a4045f9507",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# pmids_pubmed_dict"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "351d2549-e3c2-4331-b90b-3ea14530008e",
   "metadata": {},
   "source": [
    "**On veut fusionner la liste PMIDs que l'on vient de récupérer sur PubMed, à la liste des PMIDs déjà récupérés via CT.**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4c8b94f1-2851-40b8-9ed2-77348b74477e",
   "metadata": {},
   "source": [
    "On met les PMIDs de CT sous la même forme:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "6ddaabb3-f3ce-4a4d-9ef9-4d391c7205df",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "pmids_ct_dict = {}\n",
    "for nctid in nctids:\n",
    "    pmids = df_ct[df_ct.loc[:, 'NCTId'] == nctid].loc[:, 'pmid'].dropna()\n",
    "    pmids_ct_dict[nctid] = set(pmids)\n",
    "# pmids_ct_dict"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bead8f40-8dab-40f8-8650-87043c1c803a",
   "metadata": {},
   "source": [
    "Pour un NCTDId donné, on fait l'union des deux ensembles de PMIDs:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "1d60a430-c91c-4094-8a93-7d948dc0c29c",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "pmids_complete_dict = {}\n",
    "for nctid in nctids:\n",
    "    # L'ensemble des PMIds présents dans PubMed et CT\n",
    "    pmids_complete_dict[nctid] = pmids_pubmed_dict[nctid] | pmids_ct_dict[nctid]\n",
    "# pmids_complete_dict"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a808cce0-ad9a-4236-a7d7-5bf8a796eca9",
   "metadata": {},
   "source": [
    "#### Verifications:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "d181cb92-9626-406f-b36d-b15845b45b0d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Nombre total de publications issus de CT: 342\n"
     ]
    }
   ],
   "source": [
    "num_pmids_ct = sum((len(v) for v in pmids_ct_dict.values()))\n",
    "print(f'Nombre total de publications issus de CT: {num_pmids_ct}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "b4be23a4-d17f-4369-924b-44e0eff2783d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Nombre total de publications issus de Pubmed: 174\n"
     ]
    }
   ],
   "source": [
    "num_pmids_pubmed = sum((len(v) for v in pmids_pubmed_dict.values()))\n",
    "print(f'Nombre total de publications issus de Pubmed: {num_pmids_pubmed}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "880fadf5-199b-49b8-b81b-99f7ba7b2343",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Nombre total de publications après consultation CT + PubMed: 370\n"
     ]
    }
   ],
   "source": [
    "num_pmids_complete = sum((len(v) for v in pmids_complete_dict.values()))\n",
    "print(\n",
    "    f'Nombre total de publications après consultation CT + PubMed: {num_pmids_complete}'\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "ed3165a6-3f0a-4b95-b7c0-b2caf0167cc7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# L'ensemble des PMIds présents dans PubMed uniquement\n",
    "pmids_pubmed_only_dict = {}\n",
    "for nctid in nctids:\n",
    "    pmids_pubmed_only_dict[nctid] = pmids_pubmed_dict[nctid] - pmids_ct_dict[nctid]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "c9201565-039e-469b-979b-2a496d4a03ae",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Nombre de nouveaux PMIDs trouvés via Pubmed: 28\n"
     ]
    }
   ],
   "source": [
    "num_pmids_pubmed_only = sum((len(v) for v in pmids_pubmed_only_dict.values()))\n",
    "print(f'Nombre de nouveaux PMIDs trouvés via Pubmed: {num_pmids_pubmed_only}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "3dcf6bbb-ad35-4d3e-9224-f13dc3052571",
   "metadata": {},
   "outputs": [],
   "source": [
    "assert num_pmids_complete - num_pmids_ct == num_pmids_pubmed_only"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "eab933fe-2aa5-4ece-a4e6-9a07bec66d04",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NCTId des nouveaux PMIDs trouvés via Pubmed:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'NCT03078439': {'38408861'},\n",
       " 'NCT01453192': {'30688008'},\n",
       " 'NCT02777229': {'37851566', '38156046'},\n",
       " 'NCT00640263': {'34425825'},\n",
       " 'NCT01473472': {'36601747'},\n",
       " 'NCT02542891': {'38819882'},\n",
       " 'NCT05349162': {'36735263'},\n",
       " 'NCT01703962': {'37668523'},\n",
       " 'NCT02212379': {'31269208'},\n",
       " 'NCT03335995': {'37497675'},\n",
       " 'NCT03870438': {'38484756'},\n",
       " 'NCT01426243': {'26314624'},\n",
       " 'NCT01089387': {'26439886'},\n",
       " 'NCT02057796': {'36883573'},\n",
       " 'NCT01801618': {'29662875'},\n",
       " 'NCT02405013': {'36686592'},\n",
       " 'NCT05311865': {'36438274', '37795682'},\n",
       " 'NCT03005652': {'38100477'},\n",
       " 'NCT03215732': {'37143029'},\n",
       " 'NCT01688453': {'35272723'},\n",
       " 'NCT02150993': {'38740027'},\n",
       " 'NCT04288128': {'38419144', '38421662'},\n",
       " 'NCT04409405': {'38043556'},\n",
       " 'NCT02481453': {'38273639'},\n",
       " 'NCT02833961': {'36318030'}}"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print('NCTId des nouveaux PMIDs trouvés via Pubmed:')\n",
    "{k: v for k, v in pmids_pubmed_only_dict.items() if v != set()}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "b28d3dd2-8dd8-4231-b29b-aa55563d4336",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Nombre d'études sans PMIDs issus de CT: 111\n"
     ]
    }
   ],
   "source": [
    "num_nctid_empty_ct = sum((1 for v in pmids_ct_dict.values() if v == set()))\n",
    "print(f\"Nombre d'études sans PMIDs issus de CT: {num_nctid_empty_ct}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "0e4ba4ae-ce84-4b25-82ea-98e4c8972a7b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Nombre d'études sans PMIDs après consultation PubMed: 99\n"
     ]
    }
   ],
   "source": [
    "num_nctid_empty_pubmed = sum((1 for v in pmids_complete_dict.values() if v == set()))\n",
    "print(f\"Nombre d'études sans PMIDs après consultation PubMed: {num_nctid_empty_pubmed}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "0f2f7862-2d98-4e8a-a707-fa4be829e133",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NCTIds qui n'avait aucun PMIDs sous CT, mais enrichis via Pubmed:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "{'NCT02405013': {'36686592'},\n",
       " 'NCT01453192': {'30688008'},\n",
       " 'NCT05349162': {'36735263'},\n",
       " 'NCT02212379': {'31269208'},\n",
       " 'NCT04409405': {'38043556'},\n",
       " 'NCT01801618': {'29662875'},\n",
       " 'NCT02833961': {'36318030'},\n",
       " 'NCT05311865': {'36438274', '37795682'},\n",
       " 'NCT04288128': {'38419144', '38421662'},\n",
       " 'NCT02150993': {'38740027'},\n",
       " 'NCT01703962': {'37668523'},\n",
       " 'NCT03078439': {'38408861'}}"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nctids_empty_ct = {k for k, v in pmids_ct_dict.items() if v == set()}\n",
    "nctids_empty_complete = {k for k, v in pmids_complete_dict.items() if v == set()}\n",
    "nctids_previously_empty = nctids_empty_ct - nctids_empty_complete\n",
    "\n",
    "print(\"NCTIds qui n'avait aucun PMIDs sous CT, mais enrichis via Pubmed:\")\n",
    "{k: pmids_complete_dict[k] for k in nctids_previously_empty}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "a9f76a73-72da-4319-a441-365a7cd8f9db",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "12"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(nctids_previously_empty)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "9b818f69-9f1e-4778-9c2e-603ca9fd378e",
   "metadata": {},
   "outputs": [],
   "source": [
    "assert num_nctid_empty_ct - num_nctid_empty_pubmed == len(nctids_previously_empty)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ccd4072-b497-4a87-baf0-67b55c8a4845",
   "metadata": {},
   "source": [
    "### Enrichissement des PMIDs via l'API Pubmed"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6fffb32b-b3d8-4814-ae72-9d2656a6ecc0",
   "metadata": {},
   "source": [
    "Pour chaque PMID récupéré, on l'enrichit avec les données de PubMed tel que le titre, les auteurs, ...:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "7476b831-b2de-4e50-92af-7b17ec286350",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Si la liste des auteurs est vide on renvoie None, sinon on concatène les valeurs de la liste\n",
    "# sous la forme \"auteur0; auteur1 ...\"\n",
    "def concatenate_authors_list(authors_list):\n",
    "    if authors_list == []:\n",
    "        return None\n",
    "    else:\n",
    "        return '; '.join([author['name'] for author in authors_list])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "14bd2b9b-7f4a-4f50-b651-0cb302ccabcf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "370 / 370..."
     ]
    }
   ],
   "source": [
    "# We return a list of dictionnaries, each dictionnary containing the NCTId and the publication associated with it\n",
    "# total_publications_list = [{'NCTId': '...', 'publications': [{'pmid': '...', 'title': '...'}, {'pmid': '...', 'title': '...'}]}, {'NCTId': ...}]\n",
    "total_publications_list = []\n",
    "\n",
    "books_list = []\n",
    "counter = 0  # To keep track of progress of pmids\n",
    "\n",
    "# For each NTCID...\n",
    "for nctid, pmids in pmids_complete_dict.items():\n",
    "    # [{'pmid': '...', 'title': '...'}, {'pmid': '...', 'title': '...'}]\n",
    "    publications_list = []\n",
    "\n",
    "    # If the set of pmids is not empty\n",
    "    if pmids != set():\n",
    "        # Query Pubmed's API with several pmids at the same time\n",
    "        pmids_str = ','.join(pmids)\n",
    "        query_url = f'https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esummary.fcgi?db=pubmed&retmode=json&id={pmids_str}&api_key={os.getenv('NCBI_API_KEY')}'\n",
    "        response = session.get(query_url)\n",
    "        response.raise_for_status()\n",
    "        result = response.json()['result']\n",
    "\n",
    "        # We process each PMID...\n",
    "        for pmid in result['uids']:\n",
    "            # Check that the pmid returned by Pubmed was part of the query\n",
    "            assert pmid in pmids\n",
    "\n",
    "            publication = result[pmid]\n",
    "\n",
    "            # Display the progress on a single line\n",
    "            print(f'\\r{counter+1} / {num_pmids_complete}...', end='', flush=True)\n",
    "\n",
    "            # We are not interested by articles with type 'book'\n",
    "            # TODO: book special case ?\n",
    "            if publication['doctype'] == 'citation':\n",
    "                # Find the DOI amongst the different ids\n",
    "                doi = None\n",
    "                for id in publication['articleids']:\n",
    "                    if id['idtype'] == 'doi':\n",
    "                        doi = id['value']\n",
    "                        break\n",
    "\n",
    "                # Add the infos for the publication\n",
    "                publications_list.append(\n",
    "                    {\n",
    "                        'pmid': pmid,\n",
    "                        'title': publication['title'],\n",
    "                        'authors': concatenate_authors_list(publication['authors']),\n",
    "                        'doi': doi,\n",
    "                        # 'date': publication['sortpubdate'], # We use Unpaywall 'published_date' instead\n",
    "                        'publication_types': publication['pubtype'],\n",
    "                    }\n",
    "                )\n",
    "            else:\n",
    "                # type different from 'article', gets ignored\n",
    "                books_list.append((nctid, pmid))\n",
    "\n",
    "            counter += 1\n",
    "\n",
    "    publication_dict = {'NCTId': nctid, 'publications': publications_list}\n",
    "    total_publications_list.append(publication_dict)\n",
    "\n",
    "# total_publications_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "66dc9fed-b144-47c8-9637-0685aed7d3da",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('NCT03537196', '27227200')]\n"
     ]
    }
   ],
   "source": [
    "print(books_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "3f26319c-fb30-4810-8f0e-2ee72592866e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# print(json.dumps(total_publications_list, indent=2))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c57b84a6-8c2d-4b97-af3a-4ee12c51b69c",
   "metadata": {},
   "source": [
    "Publications sans DOI:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "85f0cd7d-2ba9-4f83-b327-307e54ee6de7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PMID without DOI: 19839502\n"
     ]
    }
   ],
   "source": [
    "# PMID without DOI\n",
    "for study in total_publications_list:\n",
    "    for publi in study['publications']:\n",
    "        if publi['doi'] is None:\n",
    "            print(f'PMID without DOI: {publi['pmid']}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "e319963c-1e2c-4dde-aae6-c86f161be7ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "# The number of NCTId didn't change\n",
    "assert len(total_publications_list) == len(studies_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22ec89f9-6196-4a75-b71f-ef5b6aad595d",
   "metadata": {},
   "source": [
    "### Import dans Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "eef6d4fe-795d-447a-8184-61e8f6522c0d",
   "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>NCTId</th>\n",
       "      <th>publications</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NCT02099474</td>\n",
       "      <td>[{'pmid': '32661003', 'title': 'Effect of Preg...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NCT02497274</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NCT02592174</td>\n",
       "      <td>[{'pmid': '31755936', 'title': 'Increased Prev...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NCT01490489</td>\n",
       "      <td>[{'pmid': '19602057', 'title': 'Role of EG-VEG...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NCT01552044</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>NCT01037777</td>\n",
       "      <td>[{'pmid': '35264424', 'title': 'Levels of Neur...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>186</th>\n",
       "      <td>NCT02833961</td>\n",
       "      <td>[{'pmid': '36318030', 'title': 'Longitudinal M...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>187</th>\n",
       "      <td>NCT02976298</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>188</th>\n",
       "      <td>NCT01842477</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>189</th>\n",
       "      <td>NCT02721745</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>190 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           NCTId                                       publications\n",
       "0    NCT02099474  [{'pmid': '32661003', 'title': 'Effect of Preg...\n",
       "1    NCT02497274                                                 []\n",
       "2    NCT02592174  [{'pmid': '31755936', 'title': 'Increased Prev...\n",
       "3    NCT01490489  [{'pmid': '19602057', 'title': 'Role of EG-VEG...\n",
       "4    NCT01552044                                                 []\n",
       "..           ...                                                ...\n",
       "185  NCT01037777  [{'pmid': '35264424', 'title': 'Levels of Neur...\n",
       "186  NCT02833961  [{'pmid': '36318030', 'title': 'Longitudinal M...\n",
       "187  NCT02976298                                                 []\n",
       "188  NCT01842477                                                 []\n",
       "189  NCT02721745                                                 []\n",
       "\n",
       "[190 rows x 2 columns]"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pubmed = pd.DataFrame.from_records(total_publications_list)\n",
    "df_pubmed"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c8f8e322-5468-427d-951a-52769f39d215",
   "metadata": {},
   "source": [
    "#### On \"explose\" la colonne \"publications\":\n",
    "\n",
    "Pour chaque NCTId, la colonne 'publications' contient potentiellement une liste de plusieurs publications.  \n",
    "Si par exemple, on a 3 publications, on veut se retrouver avec 3 lignes, chacune indexée par le même NCTId et contenant une unique publication.  \n",
    "\n",
    "*Avant*:  \n",
    "**`NCTId    Reference`**  \n",
    "`NCT0001, [Pub1, Pub2, Pub3]`   \n",
    "\n",
    "*Après*:  \n",
    "**`NCTId    Reference`**  \n",
    "`NCT0001,   Pub1`  \n",
    "`NCT0001,   Pub2`  \n",
    "`NCT0001,   Pub3`   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "2d2b41c7-a86c-40d8-804a-dc5fe197aca1",
   "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>NCTId</th>\n",
       "      <th>publications</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NCT02099474</td>\n",
       "      <td>{'pmid': '32661003', 'title': 'Effect of Pregn...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NCT02497274</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NCT02592174</td>\n",
       "      <td>{'pmid': '31755936', 'title': 'Increased Preva...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NCT01490489</td>\n",
       "      <td>{'pmid': '19602057', 'title': 'Role of EG-VEGF...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NCT01490489</td>\n",
       "      <td>{'pmid': '17531315', 'title': 'Placental expre...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>463</th>\n",
       "      <td>NCT01037777</td>\n",
       "      <td>{'pmid': '23707147', 'title': 'Biological and ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464</th>\n",
       "      <td>NCT02833961</td>\n",
       "      <td>{'pmid': '36318030', 'title': 'Longitudinal Mo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>465</th>\n",
       "      <td>NCT02976298</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>466</th>\n",
       "      <td>NCT01842477</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>467</th>\n",
       "      <td>NCT02721745</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>468 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           NCTId                                       publications\n",
       "0    NCT02099474  {'pmid': '32661003', 'title': 'Effect of Pregn...\n",
       "1    NCT02497274                                                NaN\n",
       "2    NCT02592174  {'pmid': '31755936', 'title': 'Increased Preva...\n",
       "3    NCT01490489  {'pmid': '19602057', 'title': 'Role of EG-VEGF...\n",
       "4    NCT01490489  {'pmid': '17531315', 'title': 'Placental expre...\n",
       "..           ...                                                ...\n",
       "463  NCT01037777  {'pmid': '23707147', 'title': 'Biological and ...\n",
       "464  NCT02833961  {'pmid': '36318030', 'title': 'Longitudinal Mo...\n",
       "465  NCT02976298                                                NaN\n",
       "466  NCT01842477                                                NaN\n",
       "467  NCT02721745                                                NaN\n",
       "\n",
       "[468 rows x 2 columns]"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pubmed = df_pubmed.explode('publications', ignore_index=True)\n",
    "df_pubmed"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9457360-7404-4027-a392-4f0f72e765fa",
   "metadata": {},
   "source": [
    "On vérifie que l'on a retrouvé plus de publications avec PubMed + CT que CT tout seul:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "9439fcc7-84f2-4cbe-ace4-b278cf26bac9",
   "metadata": {},
   "outputs": [],
   "source": [
    "assert len(df_pubmed) >= len(df_ct)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d4bd8f4-3e7b-4845-8016-ce08f3d86aa3",
   "metadata": {},
   "source": [
    "Pour chaque NCTId, la colonne 'publications' contient maintenant un dictionnaire de la forme:  \n",
    "`{  \n",
    "\"pmid\": \"17545707\",  \n",
    "\"title\": \"Haematological ...\",  \n",
    "\"authors\": \"Smith DJ; ...\",  \n",
    "}`\n",
    "\n",
    "Dont on veut extraire de nouvelles colonnes en utilant les clés du dictionnaire:  \n",
    "**`pmid      title                 authors`**  \n",
    "`17545707, \"Haematological ...\", \"Smith DJ; ...\"`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "de0d46f5-ad8f-47ba-9cb2-900c74ceda72",
   "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>pmid</th>\n",
       "      <th>title</th>\n",
       "      <th>authors</th>\n",
       "      <th>doi</th>\n",
       "      <th>publication_types</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>32661003</td>\n",
       "      <td>Effect of Pregnancy on Unbound Raltegravir Con...</td>\n",
       "      <td>Zheng Y; Hirt D; Delmas S; Lui G; Benaboud S; ...</td>\n",
       "      <td>10.1128/AAC.00759-20</td>\n",
       "      <td>[Journal Article, Multicenter Study]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>31755936</td>\n",
       "      <td>Increased Prevalence of Neurocognitive Impairm...</td>\n",
       "      <td>Makinson A; Dubois J; Eymard-Duvernay S; Lecle...</td>\n",
       "      <td>10.1093/cid/ciz670</td>\n",
       "      <td>[Journal Article]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>19602057</td>\n",
       "      <td>Role of EG-VEGF in human placentation: Physiol...</td>\n",
       "      <td>Hoffmann P; Saoudi Y; Benharouga M; Graham CH;...</td>\n",
       "      <td>10.1111/j.1582-4934.2008.00554.x</td>\n",
       "      <td>[Journal Article]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17531315</td>\n",
       "      <td>Placental expression of EG-VEGF and its recept...</td>\n",
       "      <td>Hoffmann P; Feige JJ; Alfaidy N</td>\n",
       "      <td>10.1016/j.placenta.2007.03.008</td>\n",
       "      <td>[Journal Article]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>463</th>\n",
       "      <td>23707147</td>\n",
       "      <td>Biological and clinical characteristics of ind...</td>\n",
       "      <td>Jacobi H; Reetz K; du Montcel ST; Bauer P; Mar...</td>\n",
       "      <td>10.1016/S1474-4422(13)70104-2</td>\n",
       "      <td>[Journal Article]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464</th>\n",
       "      <td>36318030</td>\n",
       "      <td>Longitudinal Monitoring of Microstructural Alt...</td>\n",
       "      <td>Genovese G; Diaz-Fernandez B; Lejeune FX; Rone...</td>\n",
       "      <td>10.1148/radiol.220430</td>\n",
       "      <td>[Journal Article]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>465</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>466</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>467</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>468 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         pmid                                              title  \\\n",
       "0    32661003  Effect of Pregnancy on Unbound Raltegravir Con...   \n",
       "1         NaN                                                NaN   \n",
       "2    31755936  Increased Prevalence of Neurocognitive Impairm...   \n",
       "3    19602057  Role of EG-VEGF in human placentation: Physiol...   \n",
       "4    17531315  Placental expression of EG-VEGF and its recept...   \n",
       "..        ...                                                ...   \n",
       "463  23707147  Biological and clinical characteristics of ind...   \n",
       "464  36318030  Longitudinal Monitoring of Microstructural Alt...   \n",
       "465       NaN                                                NaN   \n",
       "466       NaN                                                NaN   \n",
       "467       NaN                                                NaN   \n",
       "\n",
       "                                               authors  \\\n",
       "0    Zheng Y; Hirt D; Delmas S; Lui G; Benaboud S; ...   \n",
       "1                                                  NaN   \n",
       "2    Makinson A; Dubois J; Eymard-Duvernay S; Lecle...   \n",
       "3    Hoffmann P; Saoudi Y; Benharouga M; Graham CH;...   \n",
       "4                      Hoffmann P; Feige JJ; Alfaidy N   \n",
       "..                                                 ...   \n",
       "463  Jacobi H; Reetz K; du Montcel ST; Bauer P; Mar...   \n",
       "464  Genovese G; Diaz-Fernandez B; Lejeune FX; Rone...   \n",
       "465                                                NaN   \n",
       "466                                                NaN   \n",
       "467                                                NaN   \n",
       "\n",
       "                                  doi                     publication_types  \n",
       "0                10.1128/AAC.00759-20  [Journal Article, Multicenter Study]  \n",
       "1                                 NaN                                   NaN  \n",
       "2                  10.1093/cid/ciz670                     [Journal Article]  \n",
       "3    10.1111/j.1582-4934.2008.00554.x                     [Journal Article]  \n",
       "4      10.1016/j.placenta.2007.03.008                     [Journal Article]  \n",
       "..                                ...                                   ...  \n",
       "463     10.1016/S1474-4422(13)70104-2                     [Journal Article]  \n",
       "464             10.1148/radiol.220430                     [Journal Article]  \n",
       "465                               NaN                                   NaN  \n",
       "466                               NaN                                   NaN  \n",
       "467                               NaN                                   NaN  \n",
       "\n",
       "[468 rows x 5 columns]"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pubmed_publications = pd.json_normalize(df_pubmed.pop('publications'))\n",
    "df_pubmed_publications"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d9d8656-c1b0-459b-aebb-7846b57288a6",
   "metadata": {},
   "source": [
    "On réassemble la dataFrame complète:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "cce27392-459f-45e0-b262-300df63184b2",
   "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>NCTId</th>\n",
       "      <th>pmid</th>\n",
       "      <th>title</th>\n",
       "      <th>authors</th>\n",
       "      <th>doi</th>\n",
       "      <th>publication_types</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NCT02099474</td>\n",
       "      <td>32661003</td>\n",
       "      <td>Effect of Pregnancy on Unbound Raltegravir Con...</td>\n",
       "      <td>Zheng Y; Hirt D; Delmas S; Lui G; Benaboud S; ...</td>\n",
       "      <td>10.1128/AAC.00759-20</td>\n",
       "      <td>[Journal Article, Multicenter Study]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NCT02497274</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NCT02592174</td>\n",
       "      <td>31755936</td>\n",
       "      <td>Increased Prevalence of Neurocognitive Impairm...</td>\n",
       "      <td>Makinson A; Dubois J; Eymard-Duvernay S; Lecle...</td>\n",
       "      <td>10.1093/cid/ciz670</td>\n",
       "      <td>[Journal Article]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NCT01490489</td>\n",
       "      <td>19602057</td>\n",
       "      <td>Role of EG-VEGF in human placentation: Physiol...</td>\n",
       "      <td>Hoffmann P; Saoudi Y; Benharouga M; Graham CH;...</td>\n",
       "      <td>10.1111/j.1582-4934.2008.00554.x</td>\n",
       "      <td>[Journal Article]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NCT01490489</td>\n",
       "      <td>17531315</td>\n",
       "      <td>Placental expression of EG-VEGF and its recept...</td>\n",
       "      <td>Hoffmann P; Feige JJ; Alfaidy N</td>\n",
       "      <td>10.1016/j.placenta.2007.03.008</td>\n",
       "      <td>[Journal Article]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>463</th>\n",
       "      <td>NCT01037777</td>\n",
       "      <td>23707147</td>\n",
       "      <td>Biological and clinical characteristics of ind...</td>\n",
       "      <td>Jacobi H; Reetz K; du Montcel ST; Bauer P; Mar...</td>\n",
       "      <td>10.1016/S1474-4422(13)70104-2</td>\n",
       "      <td>[Journal Article]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464</th>\n",
       "      <td>NCT02833961</td>\n",
       "      <td>36318030</td>\n",
       "      <td>Longitudinal Monitoring of Microstructural Alt...</td>\n",
       "      <td>Genovese G; Diaz-Fernandez B; Lejeune FX; Rone...</td>\n",
       "      <td>10.1148/radiol.220430</td>\n",
       "      <td>[Journal Article]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>465</th>\n",
       "      <td>NCT02976298</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>466</th>\n",
       "      <td>NCT01842477</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>467</th>\n",
       "      <td>NCT02721745</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>468 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           NCTId      pmid                                              title  \\\n",
       "0    NCT02099474  32661003  Effect of Pregnancy on Unbound Raltegravir Con...   \n",
       "1    NCT02497274       NaN                                                NaN   \n",
       "2    NCT02592174  31755936  Increased Prevalence of Neurocognitive Impairm...   \n",
       "3    NCT01490489  19602057  Role of EG-VEGF in human placentation: Physiol...   \n",
       "4    NCT01490489  17531315  Placental expression of EG-VEGF and its recept...   \n",
       "..           ...       ...                                                ...   \n",
       "463  NCT01037777  23707147  Biological and clinical characteristics of ind...   \n",
       "464  NCT02833961  36318030  Longitudinal Monitoring of Microstructural Alt...   \n",
       "465  NCT02976298       NaN                                                NaN   \n",
       "466  NCT01842477       NaN                                                NaN   \n",
       "467  NCT02721745       NaN                                                NaN   \n",
       "\n",
       "                                               authors  \\\n",
       "0    Zheng Y; Hirt D; Delmas S; Lui G; Benaboud S; ...   \n",
       "1                                                  NaN   \n",
       "2    Makinson A; Dubois J; Eymard-Duvernay S; Lecle...   \n",
       "3    Hoffmann P; Saoudi Y; Benharouga M; Graham CH;...   \n",
       "4                      Hoffmann P; Feige JJ; Alfaidy N   \n",
       "..                                                 ...   \n",
       "463  Jacobi H; Reetz K; du Montcel ST; Bauer P; Mar...   \n",
       "464  Genovese G; Diaz-Fernandez B; Lejeune FX; Rone...   \n",
       "465                                                NaN   \n",
       "466                                                NaN   \n",
       "467                                                NaN   \n",
       "\n",
       "                                  doi                     publication_types  \n",
       "0                10.1128/AAC.00759-20  [Journal Article, Multicenter Study]  \n",
       "1                                 NaN                                   NaN  \n",
       "2                  10.1093/cid/ciz670                     [Journal Article]  \n",
       "3    10.1111/j.1582-4934.2008.00554.x                     [Journal Article]  \n",
       "4      10.1016/j.placenta.2007.03.008                     [Journal Article]  \n",
       "..                                ...                                   ...  \n",
       "463     10.1016/S1474-4422(13)70104-2                     [Journal Article]  \n",
       "464             10.1148/radiol.220430                     [Journal Article]  \n",
       "465                               NaN                                   NaN  \n",
       "466                               NaN                                   NaN  \n",
       "467                               NaN                                   NaN  \n",
       "\n",
       "[468 rows x 6 columns]"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pubmed = df_pubmed.join(\n",
    "    df_pubmed_publications,\n",
    "    validate='one_to_one',\n",
    ")\n",
    "df_pubmed"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb881be8-1255-4040-bad6-6e2952e20a06",
   "metadata": {},
   "source": [
    "### Jointure des DataFrame de CT et Pubmed:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "f4df0c6f-686a-4553-b2c4-de58040349af",
   "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>NCTId</th>\n",
       "      <th>BriefTitle</th>\n",
       "      <th>LeadSponsorName</th>\n",
       "      <th>CollaboratorName</th>\n",
       "      <th>OverallStatus</th>\n",
       "      <th>StudyType</th>\n",
       "      <th>HasResults</th>\n",
       "      <th>StudyFirstPostDate</th>\n",
       "      <th>StartDate</th>\n",
       "      <th>CompletionDate</th>\n",
       "      <th>pmid</th>\n",
       "      <th>type</th>\n",
       "      <th>title</th>\n",
       "      <th>authors</th>\n",
       "      <th>doi</th>\n",
       "      <th>publication_types</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NCT02099474</td>\n",
       "      <td>Evaluation of Raltegravir During the Third Tri...</td>\n",
       "      <td>ANRS, Emerging Infectious Diseases</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2014-03-31</td>\n",
       "      <td>2014-06-30</td>\n",
       "      <td>2017-04</td>\n",
       "      <td>32661003</td>\n",
       "      <td>DERIVED</td>\n",
       "      <td>Effect of Pregnancy on Unbound Raltegravir Con...</td>\n",
       "      <td>Zheng Y; Hirt D; Delmas S; Lui G; Benaboud S; ...</td>\n",
       "      <td>10.1128/AAC.00759-20</td>\n",
       "      <td>[Journal Article, Multicenter Study]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NCT02497274</td>\n",
       "      <td>Obesity and Lipids: a Matter of Taste?</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2015-07-14</td>\n",
       "      <td>2016-01-11</td>\n",
       "      <td>2019-01</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NCT02592174</td>\n",
       "      <td>Prevalence, Characteristics and Risk Factors o...</td>\n",
       "      <td>ANRS, Emerging Infectious Diseases</td>\n",
       "      <td>Merck Sharp &amp; Dohme LLC</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2015-10-30</td>\n",
       "      <td>2016-01-26</td>\n",
       "      <td>2017-10-27</td>\n",
       "      <td>31755936</td>\n",
       "      <td>DERIVED</td>\n",
       "      <td>Increased Prevalence of Neurocognitive Impairm...</td>\n",
       "      <td>Makinson A; Dubois J; Eymard-Duvernay S; Lecle...</td>\n",
       "      <td>10.1093/cid/ciz670</td>\n",
       "      <td>[Journal Article]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NCT01490489</td>\n",
       "      <td>EG-VEGF : Potential Marker of Pre-eclampsia an...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2011-12-13</td>\n",
       "      <td>2011-07-11</td>\n",
       "      <td>2015-03-13</td>\n",
       "      <td>19602057</td>\n",
       "      <td>RESULT</td>\n",
       "      <td>Role of EG-VEGF in human placentation: Physiol...</td>\n",
       "      <td>Hoffmann P; Saoudi Y; Benharouga M; Graham CH;...</td>\n",
       "      <td>10.1111/j.1582-4934.2008.00554.x</td>\n",
       "      <td>[Journal Article]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NCT01490489</td>\n",
       "      <td>EG-VEGF : Potential Marker of Pre-eclampsia an...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2011-12-13</td>\n",
       "      <td>2011-07-11</td>\n",
       "      <td>2015-03-13</td>\n",
       "      <td>17531315</td>\n",
       "      <td>RESULT</td>\n",
       "      <td>Placental expression of EG-VEGF and its recept...</td>\n",
       "      <td>Hoffmann P; Feige JJ; Alfaidy N</td>\n",
       "      <td>10.1016/j.placenta.2007.03.008</td>\n",
       "      <td>[Journal Article]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>463</th>\n",
       "      <td>NCT01037777</td>\n",
       "      <td>RISCA : Prospective Study of Individuals at Ri...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>OBSERVATIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2009-12-23</td>\n",
       "      <td>2009-05-07</td>\n",
       "      <td>2017-12-14</td>\n",
       "      <td>23707147</td>\n",
       "      <td>DERIVED</td>\n",
       "      <td>Biological and clinical characteristics of ind...</td>\n",
       "      <td>Jacobi H; Reetz K; du Montcel ST; Bauer P; Mar...</td>\n",
       "      <td>10.1016/S1474-4422(13)70104-2</td>\n",
       "      <td>[Journal Article]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464</th>\n",
       "      <td>NCT02833961</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>36318030</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>Longitudinal Monitoring of Microstructural Alt...</td>\n",
       "      <td>Genovese G; Diaz-Fernandez B; Lejeune FX; Rone...</td>\n",
       "      <td>10.1148/radiol.220430</td>\n",
       "      <td>[Journal Article]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>465</th>\n",
       "      <td>NCT02976298</td>\n",
       "      <td>Effects of Cerebellum or Supplementary Motor A...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2016-11-29</td>\n",
       "      <td>2013-02</td>\n",
       "      <td>2015-01</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>466</th>\n",
       "      <td>NCT01842477</td>\n",
       "      <td>Evaluation of Efficacy and Safety of Autologou...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2013-04-29</td>\n",
       "      <td>2013-05</td>\n",
       "      <td>2016-02-05</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>467</th>\n",
       "      <td>NCT02721745</td>\n",
       "      <td>Cause and Consequence of Neural Fatigue</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2016-03-29</td>\n",
       "      <td>2016-05-23</td>\n",
       "      <td>2020-12</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>468 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           NCTId                                         BriefTitle  \\\n",
       "0    NCT02099474  Evaluation of Raltegravir During the Third Tri...   \n",
       "1    NCT02497274             Obesity and Lipids: a Matter of Taste?   \n",
       "2    NCT02592174  Prevalence, Characteristics and Risk Factors o...   \n",
       "3    NCT01490489  EG-VEGF : Potential Marker of Pre-eclampsia an...   \n",
       "4    NCT01490489  EG-VEGF : Potential Marker of Pre-eclampsia an...   \n",
       "..           ...                                                ...   \n",
       "463  NCT01037777  RISCA : Prospective Study of Individuals at Ri...   \n",
       "464  NCT02833961                                               <NA>   \n",
       "465  NCT02976298  Effects of Cerebellum or Supplementary Motor A...   \n",
       "466  NCT01842477  Evaluation of Efficacy and Safety of Autologou...   \n",
       "467  NCT02721745            Cause and Consequence of Neural Fatigue   \n",
       "\n",
       "                                       LeadSponsorName  \\\n",
       "0                   ANRS, Emerging Infectious Diseases   \n",
       "1    Institut National de la Santé Et de la Recherc...   \n",
       "2                   ANRS, Emerging Infectious Diseases   \n",
       "3    Institut National de la Santé Et de la Recherc...   \n",
       "4    Institut National de la Santé Et de la Recherc...   \n",
       "..                                                 ...   \n",
       "463  Institut National de la Santé Et de la Recherc...   \n",
       "464                                               <NA>   \n",
       "465  Institut National de la Santé Et de la Recherc...   \n",
       "466  Institut National de la Santé Et de la Recherc...   \n",
       "467  Institut National de la Santé Et de la Recherc...   \n",
       "\n",
       "            CollaboratorName OverallStatus       StudyType  HasResults  \\\n",
       "0                       <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "1                       <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "2    Merck Sharp & Dohme LLC     COMPLETED  INTERVENTIONAL       False   \n",
       "3                       <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "4                       <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "..                       ...           ...             ...         ...   \n",
       "463                     <NA>     COMPLETED   OBSERVATIONAL       False   \n",
       "464                     <NA>          <NA>            <NA>        <NA>   \n",
       "465                     <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "466                     <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "467                     <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "\n",
       "    StudyFirstPostDate   StartDate CompletionDate      pmid     type  \\\n",
       "0           2014-03-31  2014-06-30        2017-04  32661003  DERIVED   \n",
       "1           2015-07-14  2016-01-11        2019-01      <NA>     <NA>   \n",
       "2           2015-10-30  2016-01-26     2017-10-27  31755936  DERIVED   \n",
       "3           2011-12-13  2011-07-11     2015-03-13  19602057   RESULT   \n",
       "4           2011-12-13  2011-07-11     2015-03-13  17531315   RESULT   \n",
       "..                 ...         ...            ...       ...      ...   \n",
       "463         2009-12-23  2009-05-07     2017-12-14  23707147  DERIVED   \n",
       "464               <NA>        <NA>           <NA>  36318030     <NA>   \n",
       "465         2016-11-29     2013-02        2015-01      <NA>     <NA>   \n",
       "466         2013-04-29     2013-05     2016-02-05      <NA>     <NA>   \n",
       "467         2016-03-29  2016-05-23        2020-12      <NA>     <NA>   \n",
       "\n",
       "                                                 title  \\\n",
       "0    Effect of Pregnancy on Unbound Raltegravir Con...   \n",
       "1                                                  NaN   \n",
       "2    Increased Prevalence of Neurocognitive Impairm...   \n",
       "3    Role of EG-VEGF in human placentation: Physiol...   \n",
       "4    Placental expression of EG-VEGF and its recept...   \n",
       "..                                                 ...   \n",
       "463  Biological and clinical characteristics of ind...   \n",
       "464  Longitudinal Monitoring of Microstructural Alt...   \n",
       "465                                                NaN   \n",
       "466                                                NaN   \n",
       "467                                                NaN   \n",
       "\n",
       "                                               authors  \\\n",
       "0    Zheng Y; Hirt D; Delmas S; Lui G; Benaboud S; ...   \n",
       "1                                                  NaN   \n",
       "2    Makinson A; Dubois J; Eymard-Duvernay S; Lecle...   \n",
       "3    Hoffmann P; Saoudi Y; Benharouga M; Graham CH;...   \n",
       "4                      Hoffmann P; Feige JJ; Alfaidy N   \n",
       "..                                                 ...   \n",
       "463  Jacobi H; Reetz K; du Montcel ST; Bauer P; Mar...   \n",
       "464  Genovese G; Diaz-Fernandez B; Lejeune FX; Rone...   \n",
       "465                                                NaN   \n",
       "466                                                NaN   \n",
       "467                                                NaN   \n",
       "\n",
       "                                  doi                     publication_types  \n",
       "0                10.1128/AAC.00759-20  [Journal Article, Multicenter Study]  \n",
       "1                                 NaN                                   NaN  \n",
       "2                  10.1093/cid/ciz670                     [Journal Article]  \n",
       "3    10.1111/j.1582-4934.2008.00554.x                     [Journal Article]  \n",
       "4      10.1016/j.placenta.2007.03.008                     [Journal Article]  \n",
       "..                                ...                                   ...  \n",
       "463     10.1016/S1474-4422(13)70104-2                     [Journal Article]  \n",
       "464             10.1148/radiol.220430                     [Journal Article]  \n",
       "465                               NaN                                   NaN  \n",
       "466                               NaN                                   NaN  \n",
       "467                               NaN                                   NaN  \n",
       "\n",
       "[468 rows x 16 columns]"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final = df_ct.merge(\n",
    "    df_pubmed,\n",
    "    on=['NCTId', 'pmid'],\n",
    "    how='right',\n",
    "    validate='one_to_one',\n",
    ")\n",
    "\n",
    "# Remove 'citation' column\n",
    "df_final = df_final.drop(columns='citation')\n",
    "\n",
    "df_final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d9e57d72-548d-43df-a342-ee08c83dd14c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "7d497973-5788-41e4-a541-c326b32ffb83",
   "metadata": {},
   "source": [
    "Les nouveaux PMIDs trouvés via Pubmed, n'ont aucune des infos associées avec CT présentes: BriefTitle, LeadSponsorName, etc ..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "e83f92d2-39b0-4653-8ab5-9766b16f4da4",
   "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>NCTId</th>\n",
       "      <th>BriefTitle</th>\n",
       "      <th>LeadSponsorName</th>\n",
       "      <th>CollaboratorName</th>\n",
       "      <th>OverallStatus</th>\n",
       "      <th>StudyType</th>\n",
       "      <th>HasResults</th>\n",
       "      <th>StudyFirstPostDate</th>\n",
       "      <th>StartDate</th>\n",
       "      <th>CompletionDate</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>NCT03078439</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>NCT01453192</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>NCT02777229</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>NCT02777229</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>NCT00640263</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>NCT01473472</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>NCT02542891</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>NCT05349162</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>NCT01703962</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>NCT02212379</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           NCTId BriefTitle LeadSponsorName CollaboratorName OverallStatus  \\\n",
       "16   NCT03078439       <NA>            <NA>             <NA>          <NA>   \n",
       "17   NCT01453192       <NA>            <NA>             <NA>          <NA>   \n",
       "19   NCT02777229       <NA>            <NA>             <NA>          <NA>   \n",
       "21   NCT02777229       <NA>            <NA>             <NA>          <NA>   \n",
       "95   NCT00640263       <NA>            <NA>             <NA>          <NA>   \n",
       "103  NCT01473472       <NA>            <NA>             <NA>          <NA>   \n",
       "132  NCT02542891       <NA>            <NA>             <NA>          <NA>   \n",
       "143  NCT05349162       <NA>            <NA>             <NA>          <NA>   \n",
       "148  NCT01703962       <NA>            <NA>             <NA>          <NA>   \n",
       "155  NCT02212379       <NA>            <NA>             <NA>          <NA>   \n",
       "\n",
       "    StudyType  HasResults StudyFirstPostDate StartDate CompletionDate  type  \n",
       "16       <NA>        <NA>               <NA>      <NA>           <NA>  <NA>  \n",
       "17       <NA>        <NA>               <NA>      <NA>           <NA>  <NA>  \n",
       "19       <NA>        <NA>               <NA>      <NA>           <NA>  <NA>  \n",
       "21       <NA>        <NA>               <NA>      <NA>           <NA>  <NA>  \n",
       "95       <NA>        <NA>               <NA>      <NA>           <NA>  <NA>  \n",
       "103      <NA>        <NA>               <NA>      <NA>           <NA>  <NA>  \n",
       "132      <NA>        <NA>               <NA>      <NA>           <NA>  <NA>  \n",
       "143      <NA>        <NA>               <NA>      <NA>           <NA>  <NA>  \n",
       "148      <NA>        <NA>               <NA>      <NA>           <NA>  <NA>  \n",
       "155      <NA>        <NA>               <NA>      <NA>           <NA>  <NA>  "
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Index of empty rows we need to fill\n",
    "index_empty_rows = df_final.loc[:, 'BriefTitle'].isna()\n",
    "\n",
    "# Columns we need to fill\n",
    "columns_to_fill = [\n",
    "    'BriefTitle',\n",
    "    'LeadSponsorName',\n",
    "    'CollaboratorName',\n",
    "    'OverallStatus',\n",
    "    'StudyType',\n",
    "    'HasResults',\n",
    "    'StudyFirstPostDate',\n",
    "    'StartDate',\n",
    "    'CompletionDate',\n",
    "    'type',\n",
    "]\n",
    "\n",
    "df_final.loc[index_empty_rows, ['NCTId'] + columns_to_fill].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2d9866a6",
   "metadata": {},
   "source": [
    "On rajoute un type 'PUBMED' pour les PMIDs issues de PubMed uniquement:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "05507df2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add new 'PUBMED' category\n",
    "df_final['type'] = df_final['type'].cat.add_categories('PUBMED').astype('category')\n",
    "\n",
    "# We add a 'PUBMED' type to the PMIDs extracted from Pubmed exclusively\n",
    "df_final.loc[index_empty_rows, 'type'] = 'PUBMED'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "50ec3c16",
   "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>NCTId</th>\n",
       "      <th>BriefTitle</th>\n",
       "      <th>LeadSponsorName</th>\n",
       "      <th>CollaboratorName</th>\n",
       "      <th>OverallStatus</th>\n",
       "      <th>StudyType</th>\n",
       "      <th>HasResults</th>\n",
       "      <th>StudyFirstPostDate</th>\n",
       "      <th>StartDate</th>\n",
       "      <th>CompletionDate</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>NCT03078439</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>PUBMED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>NCT01453192</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>PUBMED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>NCT02777229</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>PUBMED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>NCT02777229</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>PUBMED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>NCT00640263</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>PUBMED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>NCT01473472</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>PUBMED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>NCT02542891</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>PUBMED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>NCT05349162</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>PUBMED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>NCT01703962</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>PUBMED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>NCT02212379</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>PUBMED</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           NCTId BriefTitle LeadSponsorName CollaboratorName OverallStatus  \\\n",
       "16   NCT03078439       <NA>            <NA>             <NA>          <NA>   \n",
       "17   NCT01453192       <NA>            <NA>             <NA>          <NA>   \n",
       "19   NCT02777229       <NA>            <NA>             <NA>          <NA>   \n",
       "21   NCT02777229       <NA>            <NA>             <NA>          <NA>   \n",
       "95   NCT00640263       <NA>            <NA>             <NA>          <NA>   \n",
       "103  NCT01473472       <NA>            <NA>             <NA>          <NA>   \n",
       "132  NCT02542891       <NA>            <NA>             <NA>          <NA>   \n",
       "143  NCT05349162       <NA>            <NA>             <NA>          <NA>   \n",
       "148  NCT01703962       <NA>            <NA>             <NA>          <NA>   \n",
       "155  NCT02212379       <NA>            <NA>             <NA>          <NA>   \n",
       "\n",
       "    StudyType  HasResults StudyFirstPostDate StartDate CompletionDate    type  \n",
       "16       <NA>        <NA>               <NA>      <NA>           <NA>  PUBMED  \n",
       "17       <NA>        <NA>               <NA>      <NA>           <NA>  PUBMED  \n",
       "19       <NA>        <NA>               <NA>      <NA>           <NA>  PUBMED  \n",
       "21       <NA>        <NA>               <NA>      <NA>           <NA>  PUBMED  \n",
       "95       <NA>        <NA>               <NA>      <NA>           <NA>  PUBMED  \n",
       "103      <NA>        <NA>               <NA>      <NA>           <NA>  PUBMED  \n",
       "132      <NA>        <NA>               <NA>      <NA>           <NA>  PUBMED  \n",
       "143      <NA>        <NA>               <NA>      <NA>           <NA>  PUBMED  \n",
       "148      <NA>        <NA>               <NA>      <NA>           <NA>  PUBMED  \n",
       "155      <NA>        <NA>               <NA>      <NA>           <NA>  PUBMED  "
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final.loc[index_empty_rows, ['NCTId'] + columns_to_fill].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fc08104a-116a-4a29-9212-2e5b60fbee3f",
   "metadata": {},
   "source": [
    "On va remplir ces colonnes avec les infos contenus dans la DataFrame CT:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "e2bb2823",
   "metadata": {},
   "outputs": [],
   "source": [
    "# NCTIds of empty rows\n",
    "NCTIds_empty_rows = df_final.loc[index_empty_rows, 'NCTId']\n",
    "\n",
    "# Columns we wish to copy\n",
    "columns_to_copy = [\n",
    "    'BriefTitle',\n",
    "    'LeadSponsorName',\n",
    "    'CollaboratorName',\n",
    "    'OverallStatus',\n",
    "    'StudyType',\n",
    "    'HasResults',\n",
    "    'StudyFirstPostDate',\n",
    "    'StartDate',\n",
    "    'CompletionDate',\n",
    "]\n",
    "\n",
    "# We copy the missing values from the CT dataframe\n",
    "for index, nctid in NCTIds_empty_rows.items():\n",
    "    # For an NCTId, we look in the CT dataframe for the first row with this NCTDId\n",
    "    # and copy the missings columns\n",
    "    df_final.loc[index, columns_to_copy] = df_ct.loc[\n",
    "        df_ct.loc[:, 'NCTId'] == nctid, columns_to_copy\n",
    "    ].iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "cce5605e",
   "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>NCTId</th>\n",
       "      <th>BriefTitle</th>\n",
       "      <th>LeadSponsorName</th>\n",
       "      <th>CollaboratorName</th>\n",
       "      <th>OverallStatus</th>\n",
       "      <th>StudyType</th>\n",
       "      <th>HasResults</th>\n",
       "      <th>StudyFirstPostDate</th>\n",
       "      <th>StartDate</th>\n",
       "      <th>CompletionDate</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>NCT03078439</td>\n",
       "      <td>EPIPAGE2 Cohort Study Follow up at Five and a ...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>OBSERVATIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2017-03-13</td>\n",
       "      <td>2016-09-02</td>\n",
       "      <td>2018-01-08</td>\n",
       "      <td>PUBMED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>NCT01453192</td>\n",
       "      <td>Renal Transplantation and Raltegravir in HIV-I...</td>\n",
       "      <td>ANRS, Emerging Infectious Diseases</td>\n",
       "      <td>Merck Sharp &amp; Dohme LLC</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2011-10-17</td>\n",
       "      <td>2011-12</td>\n",
       "      <td>2015-11</td>\n",
       "      <td>PUBMED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>NCT02777229</td>\n",
       "      <td>Efficacy and Safety of a Dolutegravir-based Re...</td>\n",
       "      <td>ANRS, Emerging Infectious Diseases</td>\n",
       "      <td>Institut de Recherche pour le Developpement | ...</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2016-05-19</td>\n",
       "      <td>2016-07</td>\n",
       "      <td>2021-07</td>\n",
       "      <td>PUBMED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>NCT02777229</td>\n",
       "      <td>Efficacy and Safety of a Dolutegravir-based Re...</td>\n",
       "      <td>ANRS, Emerging Infectious Diseases</td>\n",
       "      <td>Institut de Recherche pour le Developpement | ...</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2016-05-19</td>\n",
       "      <td>2016-07</td>\n",
       "      <td>2021-07</td>\n",
       "      <td>PUBMED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>NCT00640263</td>\n",
       "      <td>Comparison of Efficacy and Safety of Infant Pe...</td>\n",
       "      <td>French National Agency for Research on AIDS an...</td>\n",
       "      <td>European and Developing Countries Clinical Tri...</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2008-03-21</td>\n",
       "      <td>2009-12</td>\n",
       "      <td>2014-02</td>\n",
       "      <td>PUBMED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>NCT01473472</td>\n",
       "      <td>On Demand Antiretroviral Pre-exposure Prophyla...</td>\n",
       "      <td>ANRS, Emerging Infectious Diseases</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2011-11-17</td>\n",
       "      <td>2012-01</td>\n",
       "      <td>2016-12-15</td>\n",
       "      <td>PUBMED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>NCT02542891</td>\n",
       "      <td>European Comparative Effectiveness Research on...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>European Commission | Fondation FondaMental | ...</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2015-09-07</td>\n",
       "      <td>2015-09</td>\n",
       "      <td>2018-09-15</td>\n",
       "      <td>PUBMED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>143</th>\n",
       "      <td>NCT05349162</td>\n",
       "      <td>Epicardial vs. Transvenous ICDs in Children</td>\n",
       "      <td>Paris Cardiovascular Research Center (Inserm U...</td>\n",
       "      <td>Hôpital Necker-Enfants Malades</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>OBSERVATIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-04-27</td>\n",
       "      <td>2003-01-01</td>\n",
       "      <td>2022-04-01</td>\n",
       "      <td>PUBMED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>NCT01703962</td>\n",
       "      <td>Non Invasive IDentification of Gliomas With ID...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>OBSERVATIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2012-10-11</td>\n",
       "      <td>2012-03-14</td>\n",
       "      <td>2014-03-20</td>\n",
       "      <td>PUBMED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>NCT02212379</td>\n",
       "      <td>Capacity of the Dual Combination Raltegravir/E...</td>\n",
       "      <td>ANRS, Emerging Infectious Diseases</td>\n",
       "      <td>Merck Sharp &amp; Dohme LLC | Janssen-Cilag Ltd.</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>True</td>\n",
       "      <td>2014-08-08</td>\n",
       "      <td>2015-01</td>\n",
       "      <td>2018-04</td>\n",
       "      <td>PUBMED</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           NCTId                                         BriefTitle  \\\n",
       "16   NCT03078439  EPIPAGE2 Cohort Study Follow up at Five and a ...   \n",
       "17   NCT01453192  Renal Transplantation and Raltegravir in HIV-I...   \n",
       "19   NCT02777229  Efficacy and Safety of a Dolutegravir-based Re...   \n",
       "21   NCT02777229  Efficacy and Safety of a Dolutegravir-based Re...   \n",
       "95   NCT00640263  Comparison of Efficacy and Safety of Infant Pe...   \n",
       "103  NCT01473472  On Demand Antiretroviral Pre-exposure Prophyla...   \n",
       "132  NCT02542891  European Comparative Effectiveness Research on...   \n",
       "143  NCT05349162        Epicardial vs. Transvenous ICDs in Children   \n",
       "148  NCT01703962  Non Invasive IDentification of Gliomas With ID...   \n",
       "155  NCT02212379  Capacity of the Dual Combination Raltegravir/E...   \n",
       "\n",
       "                                       LeadSponsorName  \\\n",
       "16   Institut National de la Santé Et de la Recherc...   \n",
       "17                  ANRS, Emerging Infectious Diseases   \n",
       "19                  ANRS, Emerging Infectious Diseases   \n",
       "21                  ANRS, Emerging Infectious Diseases   \n",
       "95   French National Agency for Research on AIDS an...   \n",
       "103                 ANRS, Emerging Infectious Diseases   \n",
       "132  Institut National de la Santé Et de la Recherc...   \n",
       "143  Paris Cardiovascular Research Center (Inserm U...   \n",
       "148  Institut National de la Santé Et de la Recherc...   \n",
       "155                 ANRS, Emerging Infectious Diseases   \n",
       "\n",
       "                                      CollaboratorName OverallStatus  \\\n",
       "16                                                <NA>     COMPLETED   \n",
       "17                             Merck Sharp & Dohme LLC     COMPLETED   \n",
       "19   Institut de Recherche pour le Developpement | ...     COMPLETED   \n",
       "21   Institut de Recherche pour le Developpement | ...     COMPLETED   \n",
       "95   European and Developing Countries Clinical Tri...     COMPLETED   \n",
       "103                                               <NA>     COMPLETED   \n",
       "132  European Commission | Fondation FondaMental | ...     COMPLETED   \n",
       "143                     Hôpital Necker-Enfants Malades     COMPLETED   \n",
       "148                                               <NA>     COMPLETED   \n",
       "155       Merck Sharp & Dohme LLC | Janssen-Cilag Ltd.     COMPLETED   \n",
       "\n",
       "          StudyType  HasResults StudyFirstPostDate   StartDate CompletionDate  \\\n",
       "16    OBSERVATIONAL       False         2017-03-13  2016-09-02     2018-01-08   \n",
       "17   INTERVENTIONAL       False         2011-10-17     2011-12        2015-11   \n",
       "19   INTERVENTIONAL       False         2016-05-19     2016-07        2021-07   \n",
       "21   INTERVENTIONAL       False         2016-05-19     2016-07        2021-07   \n",
       "95   INTERVENTIONAL       False         2008-03-21     2009-12        2014-02   \n",
       "103  INTERVENTIONAL       False         2011-11-17     2012-01     2016-12-15   \n",
       "132  INTERVENTIONAL       False         2015-09-07     2015-09     2018-09-15   \n",
       "143   OBSERVATIONAL       False         2022-04-27  2003-01-01     2022-04-01   \n",
       "148   OBSERVATIONAL       False         2012-10-11  2012-03-14     2014-03-20   \n",
       "155  INTERVENTIONAL        True         2014-08-08     2015-01        2018-04   \n",
       "\n",
       "       type  \n",
       "16   PUBMED  \n",
       "17   PUBMED  \n",
       "19   PUBMED  \n",
       "21   PUBMED  \n",
       "95   PUBMED  \n",
       "103  PUBMED  \n",
       "132  PUBMED  \n",
       "143  PUBMED  \n",
       "148  PUBMED  \n",
       "155  PUBMED  "
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final.loc[index_empty_rows, ['NCTId'] + columns_to_fill].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4c772a3-370d-44e0-aa17-911fde201acc",
   "metadata": {},
   "source": [
    "# Verification du statut Open Access via l'API d'Unpaywall:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a3a98bf-8789-440d-80f8-90d249c9c325",
   "metadata": {},
   "source": [
    "Ne pas oublier que le DOI peut être vide :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "82977170-c66d-43dd-beaf-e22702b402d6",
   "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>NCTId</th>\n",
       "      <th>pmid</th>\n",
       "      <th>type</th>\n",
       "      <th>title</th>\n",
       "      <th>doi</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>343</th>\n",
       "      <td>NCT03537196</td>\n",
       "      <td>19839502</td>\n",
       "      <td>BACKGROUND</td>\n",
       "      <td>High prevalence of Hepatitis C virus genotype ...</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           NCTId      pmid        type  \\\n",
       "343  NCT03537196  19839502  BACKGROUND   \n",
       "\n",
       "                                                 title   doi  \n",
       "343  High prevalence of Hepatitis C virus genotype ...  None  "
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mask_doi_na = df_final.loc[:, 'pmid'].notna() & df_final.loc[:, 'doi'].isna()\n",
    "df_final.loc[mask_doi_na, ['NCTId', 'pmid', 'type', 'title', 'doi']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83d2616f-ce2e-4bff-847f-0236232e36b5",
   "metadata": {},
   "source": [
    "#### Envoi de la requête :"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "63ee8308-ef84-4e66-bcbf-84b9a69c2ca0",
   "metadata": {},
   "source": [
    "L'utilisation de l'API d'Unpaywall nécessite d'inclure son adresse email dans la requête:\n",
    "\n",
    "> **Authentication**  \n",
    "> Requests must include your email as a parameter at the end of the URL, like this: api.unpaywall.org/my/request?email=YOUR_EMAIL.\n",
    "\n",
    "Pour éviter de diffuser trop largement son adresse email, plutôt que de l'inclure dans le notebook, on l'exporte comme variable d'environnement, comme on l'a fait pour la clé API Pubmed.\n",
    "\n",
    "Ajouter `EMAIL_ADDRESS` aux variables d'environnement avec la commande suivante dans un Terminal:\n",
    "\n",
    "**Windows :** \n",
    "\n",
    "`setx EMAIL_ADDRESS \"unpaywall_01@example.com\"`\n",
    "\n",
    "**Linux/MacOS :**\n",
    "\n",
    "`export EMAIL_ADDRESS=unpaywall_01@example.com`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "bae4b08d-ff66-428e-a086-b657a011c1e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "assert os.getenv('EMAIL_ADDRESS', None) is not None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "945c7a38-4f95-475c-aadd-5ae3d2444635",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "362 / 362..."
     ]
    }
   ],
   "source": [
    "# We return a list of dictionnaries, each dictionnary containing\n",
    "# publications_list = [{'doi': ..., 'is_oa': ...}, {'doi': ..., 'is_oa': ...}]\n",
    "publications_list_un = []\n",
    "\n",
    "# Set of all the DOIs we need information for\n",
    "dois = df_final['doi'].dropna().unique()\n",
    "num_dois = len(dois)\n",
    "\n",
    "for i, doi in enumerate(dois):\n",
    "    # Display the progress on a single line\n",
    "    print(f'\\r{i+1} / {num_dois}...', end='', flush=True)\n",
    "\n",
    "    # Query Unpaywall's API\n",
    "    query_url = f'https://api.unpaywall.org/v2/{doi}?email={os.getenv('EMAIL_ADDRESS')}'\n",
    "    response = session.get(query_url)\n",
    "    response.raise_for_status()\n",
    "    result = response.json()\n",
    "\n",
    "    # Check we have the correct doi as a result\n",
    "    doi_unpaywall = result['doi']\n",
    "    assert doi.casefold() == doi_unpaywall\n",
    "\n",
    "    # Check not a paratext\n",
    "    if result['is_paratext']:\n",
    "        print(f'warning: {doi}, paratext detected!')\n",
    "\n",
    "    # Add the info for the publication\n",
    "    publications_list_un.append(\n",
    "        {\n",
    "            'doi': doi,\n",
    "            # 'genre': result['genre'], # always 'journal-article' for now, add a check ?\n",
    "            'is_paratext': result['is_paratext'],\n",
    "            'published_date': result['published_date'],\n",
    "            'is_oa': result['is_oa'],\n",
    "            'oa_status': result['oa_status'],\n",
    "            'has_repository_copy': result['has_repository_copy'],\n",
    "            # add 'oa_date' to compare with 'published_date' to calculate embargo period ?\n",
    "        }\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "96f7e33a-718c-470d-ba33-1ad16caeb866",
   "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>doi</th>\n",
       "      <th>is_paratext</th>\n",
       "      <th>published_date</th>\n",
       "      <th>is_oa</th>\n",
       "      <th>oa_status</th>\n",
       "      <th>has_repository_copy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.1128/AAC.00759-20</td>\n",
       "      <td>False</td>\n",
       "      <td>2020-09-21</td>\n",
       "      <td>True</td>\n",
       "      <td>green</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.1093/cid/ciz670</td>\n",
       "      <td>False</td>\n",
       "      <td>2019-07-25</td>\n",
       "      <td>True</td>\n",
       "      <td>hybrid</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10.1111/j.1582-4934.2008.00554.x</td>\n",
       "      <td>False</td>\n",
       "      <td>2008-10-16</td>\n",
       "      <td>True</td>\n",
       "      <td>green</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10.1016/j.placenta.2007.03.008</td>\n",
       "      <td>False</td>\n",
       "      <td>2007-10-01</td>\n",
       "      <td>False</td>\n",
       "      <td>closed</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10.1210/en.2005-0912</td>\n",
       "      <td>False</td>\n",
       "      <td>2006-04-01</td>\n",
       "      <td>False</td>\n",
       "      <td>closed</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>357</th>\n",
       "      <td>10.1016/S2665-9913(20)30280-0</td>\n",
       "      <td>False</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>True</td>\n",
       "      <td>bronze</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>358</th>\n",
       "      <td>10.1212/WNL.0000000000200257</td>\n",
       "      <td>False</td>\n",
       "      <td>2022-05-17</td>\n",
       "      <td>True</td>\n",
       "      <td>green</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>359</th>\n",
       "      <td>10.1016/S1474-4422(20)30235-0</td>\n",
       "      <td>False</td>\n",
       "      <td>2020-09-01</td>\n",
       "      <td>True</td>\n",
       "      <td>green</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>360</th>\n",
       "      <td>10.1016/S1474-4422(13)70104-2</td>\n",
       "      <td>False</td>\n",
       "      <td>2013-07-01</td>\n",
       "      <td>False</td>\n",
       "      <td>closed</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>361</th>\n",
       "      <td>10.1148/radiol.220430</td>\n",
       "      <td>False</td>\n",
       "      <td>2023-03-01</td>\n",
       "      <td>True</td>\n",
       "      <td>green</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>362 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  doi  is_paratext published_date  is_oa  \\\n",
       "0                10.1128/AAC.00759-20        False     2020-09-21   True   \n",
       "1                  10.1093/cid/ciz670        False     2019-07-25   True   \n",
       "2    10.1111/j.1582-4934.2008.00554.x        False     2008-10-16   True   \n",
       "3      10.1016/j.placenta.2007.03.008        False     2007-10-01  False   \n",
       "4                10.1210/en.2005-0912        False     2006-04-01  False   \n",
       "..                                ...          ...            ...    ...   \n",
       "357     10.1016/S2665-9913(20)30280-0        False     2021-01-01   True   \n",
       "358      10.1212/WNL.0000000000200257        False     2022-05-17   True   \n",
       "359     10.1016/S1474-4422(20)30235-0        False     2020-09-01   True   \n",
       "360     10.1016/S1474-4422(13)70104-2        False     2013-07-01  False   \n",
       "361             10.1148/radiol.220430        False     2023-03-01   True   \n",
       "\n",
       "    oa_status  has_repository_copy  \n",
       "0       green                 True  \n",
       "1      hybrid                False  \n",
       "2       green                 True  \n",
       "3      closed                False  \n",
       "4      closed                False  \n",
       "..        ...                  ...  \n",
       "357    bronze                 True  \n",
       "358     green                 True  \n",
       "359     green                 True  \n",
       "360    closed                False  \n",
       "361     green                 True  \n",
       "\n",
       "[362 rows x 6 columns]"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_unpaywall = pd.DataFrame(publications_list_un).astype(\n",
    "    {\n",
    "        'is_paratext': 'boolean',\n",
    "        'is_oa': 'boolean',\n",
    "        'oa_status': 'category',\n",
    "        'has_repository_copy': 'boolean',\n",
    "    }\n",
    ")\n",
    "df_unpaywall"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "87115f5c-a23e-4e3f-904b-fc6a45ff8b05",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "is_paratext\n",
       "False    362\n",
       "Name: count, dtype: Int64"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Assert ? Test in the loop ?\n",
    "df_unpaywall['is_paratext'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8feb0603-7f42-4edd-8b25-8942980b83f5",
   "metadata": {},
   "source": [
    "#### Jointure des dataframes :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "15a6c313-d2cd-4ba5-a6be-3cb752de7f22",
   "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>NCTId</th>\n",
       "      <th>BriefTitle</th>\n",
       "      <th>LeadSponsorName</th>\n",
       "      <th>CollaboratorName</th>\n",
       "      <th>OverallStatus</th>\n",
       "      <th>StudyType</th>\n",
       "      <th>HasResults</th>\n",
       "      <th>StudyFirstPostDate</th>\n",
       "      <th>StartDate</th>\n",
       "      <th>CompletionDate</th>\n",
       "      <th>...</th>\n",
       "      <th>type</th>\n",
       "      <th>title</th>\n",
       "      <th>authors</th>\n",
       "      <th>doi</th>\n",
       "      <th>publication_types</th>\n",
       "      <th>is_paratext</th>\n",
       "      <th>published_date</th>\n",
       "      <th>is_oa</th>\n",
       "      <th>oa_status</th>\n",
       "      <th>has_repository_copy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NCT02099474</td>\n",
       "      <td>Evaluation of Raltegravir During the Third Tri...</td>\n",
       "      <td>ANRS, Emerging Infectious Diseases</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2014-03-31</td>\n",
       "      <td>2014-06-30</td>\n",
       "      <td>2017-04</td>\n",
       "      <td>...</td>\n",
       "      <td>DERIVED</td>\n",
       "      <td>Effect of Pregnancy on Unbound Raltegravir Con...</td>\n",
       "      <td>Zheng Y; Hirt D; Delmas S; Lui G; Benaboud S; ...</td>\n",
       "      <td>10.1128/AAC.00759-20</td>\n",
       "      <td>[Journal Article, Multicenter Study]</td>\n",
       "      <td>False</td>\n",
       "      <td>2020-09-21</td>\n",
       "      <td>True</td>\n",
       "      <td>green</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NCT02497274</td>\n",
       "      <td>Obesity and Lipids: a Matter of Taste?</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2015-07-14</td>\n",
       "      <td>2016-01-11</td>\n",
       "      <td>2019-01</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NCT02592174</td>\n",
       "      <td>Prevalence, Characteristics and Risk Factors o...</td>\n",
       "      <td>ANRS, Emerging Infectious Diseases</td>\n",
       "      <td>Merck Sharp &amp; Dohme LLC</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2015-10-30</td>\n",
       "      <td>2016-01-26</td>\n",
       "      <td>2017-10-27</td>\n",
       "      <td>...</td>\n",
       "      <td>DERIVED</td>\n",
       "      <td>Increased Prevalence of Neurocognitive Impairm...</td>\n",
       "      <td>Makinson A; Dubois J; Eymard-Duvernay S; Lecle...</td>\n",
       "      <td>10.1093/cid/ciz670</td>\n",
       "      <td>[Journal Article]</td>\n",
       "      <td>False</td>\n",
       "      <td>2019-07-25</td>\n",
       "      <td>True</td>\n",
       "      <td>hybrid</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NCT01490489</td>\n",
       "      <td>EG-VEGF : Potential Marker of Pre-eclampsia an...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2011-12-13</td>\n",
       "      <td>2011-07-11</td>\n",
       "      <td>2015-03-13</td>\n",
       "      <td>...</td>\n",
       "      <td>RESULT</td>\n",
       "      <td>Role of EG-VEGF in human placentation: Physiol...</td>\n",
       "      <td>Hoffmann P; Saoudi Y; Benharouga M; Graham CH;...</td>\n",
       "      <td>10.1111/j.1582-4934.2008.00554.x</td>\n",
       "      <td>[Journal Article]</td>\n",
       "      <td>False</td>\n",
       "      <td>2008-10-16</td>\n",
       "      <td>True</td>\n",
       "      <td>green</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NCT01490489</td>\n",
       "      <td>EG-VEGF : Potential Marker of Pre-eclampsia an...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2011-12-13</td>\n",
       "      <td>2011-07-11</td>\n",
       "      <td>2015-03-13</td>\n",
       "      <td>...</td>\n",
       "      <td>RESULT</td>\n",
       "      <td>Placental expression of EG-VEGF and its recept...</td>\n",
       "      <td>Hoffmann P; Feige JJ; Alfaidy N</td>\n",
       "      <td>10.1016/j.placenta.2007.03.008</td>\n",
       "      <td>[Journal Article]</td>\n",
       "      <td>False</td>\n",
       "      <td>2007-10-01</td>\n",
       "      <td>False</td>\n",
       "      <td>closed</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>463</th>\n",
       "      <td>NCT01037777</td>\n",
       "      <td>RISCA : Prospective Study of Individuals at Ri...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>OBSERVATIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2009-12-23</td>\n",
       "      <td>2009-05-07</td>\n",
       "      <td>2017-12-14</td>\n",
       "      <td>...</td>\n",
       "      <td>DERIVED</td>\n",
       "      <td>Biological and clinical characteristics of ind...</td>\n",
       "      <td>Jacobi H; Reetz K; du Montcel ST; Bauer P; Mar...</td>\n",
       "      <td>10.1016/S1474-4422(13)70104-2</td>\n",
       "      <td>[Journal Article]</td>\n",
       "      <td>False</td>\n",
       "      <td>2013-07-01</td>\n",
       "      <td>False</td>\n",
       "      <td>closed</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464</th>\n",
       "      <td>NCT02833961</td>\n",
       "      <td>Diffusion Spectroscopy in Stroke</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2016-07-14</td>\n",
       "      <td>2016-07-28</td>\n",
       "      <td>2019-10-25</td>\n",
       "      <td>...</td>\n",
       "      <td>PUBMED</td>\n",
       "      <td>Longitudinal Monitoring of Microstructural Alt...</td>\n",
       "      <td>Genovese G; Diaz-Fernandez B; Lejeune FX; Rone...</td>\n",
       "      <td>10.1148/radiol.220430</td>\n",
       "      <td>[Journal Article]</td>\n",
       "      <td>False</td>\n",
       "      <td>2023-03-01</td>\n",
       "      <td>True</td>\n",
       "      <td>green</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>465</th>\n",
       "      <td>NCT02976298</td>\n",
       "      <td>Effects of Cerebellum or Supplementary Motor A...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2016-11-29</td>\n",
       "      <td>2013-02</td>\n",
       "      <td>2015-01</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>466</th>\n",
       "      <td>NCT01842477</td>\n",
       "      <td>Evaluation of Efficacy and Safety of Autologou...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2013-04-29</td>\n",
       "      <td>2013-05</td>\n",
       "      <td>2016-02-05</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>467</th>\n",
       "      <td>NCT02721745</td>\n",
       "      <td>Cause and Consequence of Neural Fatigue</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2016-03-29</td>\n",
       "      <td>2016-05-23</td>\n",
       "      <td>2020-12</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>468 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           NCTId                                         BriefTitle  \\\n",
       "0    NCT02099474  Evaluation of Raltegravir During the Third Tri...   \n",
       "1    NCT02497274             Obesity and Lipids: a Matter of Taste?   \n",
       "2    NCT02592174  Prevalence, Characteristics and Risk Factors o...   \n",
       "3    NCT01490489  EG-VEGF : Potential Marker of Pre-eclampsia an...   \n",
       "4    NCT01490489  EG-VEGF : Potential Marker of Pre-eclampsia an...   \n",
       "..           ...                                                ...   \n",
       "463  NCT01037777  RISCA : Prospective Study of Individuals at Ri...   \n",
       "464  NCT02833961                   Diffusion Spectroscopy in Stroke   \n",
       "465  NCT02976298  Effects of Cerebellum or Supplementary Motor A...   \n",
       "466  NCT01842477  Evaluation of Efficacy and Safety of Autologou...   \n",
       "467  NCT02721745            Cause and Consequence of Neural Fatigue   \n",
       "\n",
       "                                       LeadSponsorName  \\\n",
       "0                   ANRS, Emerging Infectious Diseases   \n",
       "1    Institut National de la Santé Et de la Recherc...   \n",
       "2                   ANRS, Emerging Infectious Diseases   \n",
       "3    Institut National de la Santé Et de la Recherc...   \n",
       "4    Institut National de la Santé Et de la Recherc...   \n",
       "..                                                 ...   \n",
       "463  Institut National de la Santé Et de la Recherc...   \n",
       "464  Institut National de la Santé Et de la Recherc...   \n",
       "465  Institut National de la Santé Et de la Recherc...   \n",
       "466  Institut National de la Santé Et de la Recherc...   \n",
       "467  Institut National de la Santé Et de la Recherc...   \n",
       "\n",
       "            CollaboratorName OverallStatus       StudyType  HasResults  \\\n",
       "0                       <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "1                       <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "2    Merck Sharp & Dohme LLC     COMPLETED  INTERVENTIONAL       False   \n",
       "3                       <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "4                       <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "..                       ...           ...             ...         ...   \n",
       "463                     <NA>     COMPLETED   OBSERVATIONAL       False   \n",
       "464                     <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "465                     <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "466                     <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "467                     <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "\n",
       "    StudyFirstPostDate   StartDate CompletionDate  ...     type  \\\n",
       "0           2014-03-31  2014-06-30        2017-04  ...  DERIVED   \n",
       "1           2015-07-14  2016-01-11        2019-01  ...      NaN   \n",
       "2           2015-10-30  2016-01-26     2017-10-27  ...  DERIVED   \n",
       "3           2011-12-13  2011-07-11     2015-03-13  ...   RESULT   \n",
       "4           2011-12-13  2011-07-11     2015-03-13  ...   RESULT   \n",
       "..                 ...         ...            ...  ...      ...   \n",
       "463         2009-12-23  2009-05-07     2017-12-14  ...  DERIVED   \n",
       "464         2016-07-14  2016-07-28     2019-10-25  ...   PUBMED   \n",
       "465         2016-11-29     2013-02        2015-01  ...      NaN   \n",
       "466         2013-04-29     2013-05     2016-02-05  ...      NaN   \n",
       "467         2016-03-29  2016-05-23        2020-12  ...      NaN   \n",
       "\n",
       "                                                 title  \\\n",
       "0    Effect of Pregnancy on Unbound Raltegravir Con...   \n",
       "1                                                  NaN   \n",
       "2    Increased Prevalence of Neurocognitive Impairm...   \n",
       "3    Role of EG-VEGF in human placentation: Physiol...   \n",
       "4    Placental expression of EG-VEGF and its recept...   \n",
       "..                                                 ...   \n",
       "463  Biological and clinical characteristics of ind...   \n",
       "464  Longitudinal Monitoring of Microstructural Alt...   \n",
       "465                                                NaN   \n",
       "466                                                NaN   \n",
       "467                                                NaN   \n",
       "\n",
       "                                               authors  \\\n",
       "0    Zheng Y; Hirt D; Delmas S; Lui G; Benaboud S; ...   \n",
       "1                                                  NaN   \n",
       "2    Makinson A; Dubois J; Eymard-Duvernay S; Lecle...   \n",
       "3    Hoffmann P; Saoudi Y; Benharouga M; Graham CH;...   \n",
       "4                      Hoffmann P; Feige JJ; Alfaidy N   \n",
       "..                                                 ...   \n",
       "463  Jacobi H; Reetz K; du Montcel ST; Bauer P; Mar...   \n",
       "464  Genovese G; Diaz-Fernandez B; Lejeune FX; Rone...   \n",
       "465                                                NaN   \n",
       "466                                                NaN   \n",
       "467                                                NaN   \n",
       "\n",
       "                                  doi                     publication_types  \\\n",
       "0                10.1128/AAC.00759-20  [Journal Article, Multicenter Study]   \n",
       "1                                 NaN                                   NaN   \n",
       "2                  10.1093/cid/ciz670                     [Journal Article]   \n",
       "3    10.1111/j.1582-4934.2008.00554.x                     [Journal Article]   \n",
       "4      10.1016/j.placenta.2007.03.008                     [Journal Article]   \n",
       "..                                ...                                   ...   \n",
       "463     10.1016/S1474-4422(13)70104-2                     [Journal Article]   \n",
       "464             10.1148/radiol.220430                     [Journal Article]   \n",
       "465                               NaN                                   NaN   \n",
       "466                               NaN                                   NaN   \n",
       "467                               NaN                                   NaN   \n",
       "\n",
       "    is_paratext  published_date  is_oa  oa_status has_repository_copy  \n",
       "0         False      2020-09-21   True      green                True  \n",
       "1          <NA>             NaN   <NA>        NaN                <NA>  \n",
       "2         False      2019-07-25   True     hybrid               False  \n",
       "3         False      2008-10-16   True      green                True  \n",
       "4         False      2007-10-01  False     closed               False  \n",
       "..          ...             ...    ...        ...                 ...  \n",
       "463       False      2013-07-01  False     closed               False  \n",
       "464       False      2023-03-01   True      green                True  \n",
       "465        <NA>             NaN   <NA>        NaN                <NA>  \n",
       "466        <NA>             NaN   <NA>        NaN                <NA>  \n",
       "467        <NA>             NaN   <NA>        NaN                <NA>  \n",
       "\n",
       "[468 rows x 21 columns]"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_with_oa_status = df_final.merge(\n",
    "    df_unpaywall,\n",
    "    on=['doi'],\n",
    "    how='left',\n",
    "    validate='many_to_one',\n",
    ")\n",
    "\n",
    "# df_with_oa_status.loc[:, ['NCTId', 'pmid', 'doi', 'is_oa']]\n",
    "df_with_oa_status"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6453906c-07fd-4300-aff0-3f176d077aa5",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "f6a7b3db-9198-4441-840d-1ec0f19f3002",
   "metadata": {},
   "source": [
    "# OpenAlex :"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "559c6ca3-f527-4559-ab90-4e33529c651d",
   "metadata": {},
   "source": [
    "De la même façon que Unpaywall, l'API d'OpenAlex suggère fortement d'inclure son adresse email dans la requête:\n",
    "\n",
    "> For best performance, add your email to all API requests, like mailto=example@domain.com."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "d0b823bf-8494-4274-b885-d8af263d67b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "assert os.getenv('EMAIL_ADDRESS', None) is not None"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eb1c5a60-5a1b-4db8-a087-bbb18d2085a0",
   "metadata": {},
   "source": [
    "#### Envoi de la requête :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "b2be041b-366d-4d69-bcc8-75cc12d8234f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "362 / 362..."
     ]
    }
   ],
   "source": [
    "# Query OpenAlex' API\n",
    "select = [\n",
    "    # 'id',\n",
    "    'doi',\n",
    "    # 'publication_date',\n",
    "    'type',\n",
    "    # 'open_access',\n",
    "    # 'authorships',\n",
    "    'is_paratext',\n",
    "    'primary_topic',\n",
    "]\n",
    "\n",
    "# We return a list of dictionnaries, each dictionnary containing\n",
    "# publications_list = [{'doi': ..., 'primary_topic': ...}, {'doi': ..., 'primary_topic': ...}]\n",
    "publications_list_opa = []\n",
    "\n",
    "# Set of all the DOIs we need information for\n",
    "dois = df_with_oa_status['doi'].dropna().unique()\n",
    "num_dois = len(dois)\n",
    "\n",
    "# For each DOI...\n",
    "for i, doi in enumerate(dois):\n",
    "    # Display the progress on a single line\n",
    "    print(f'\\r{i+1} / {num_dois}...', end='', flush=True)\n",
    "\n",
    "    # Query OpenAlex's API\n",
    "    query_url = f\"https://api.openalex.org/works?filter=doi:{doi}&select={','.join(select)}&mailto={os.getenv('EMAIL_ADDRESS')}\"\n",
    "    response = session.get(query_url)\n",
    "    response.raise_for_status()\n",
    "    r_json = response.json()\n",
    "\n",
    "    if r_json['meta']['count'] == 1:\n",
    "        result = response.json()['results'][0]\n",
    "\n",
    "        # Check we have the correct doi as a result\n",
    "        doi_openalex = result['doi']\n",
    "        assert doi.casefold() == doi_openalex.removeprefix('https://doi.org/')\n",
    "\n",
    "        # Check not a paratext\n",
    "        if result['is_paratext']:\n",
    "            print(f'warning: {doi}, paratext detected!')\n",
    "\n",
    "        # open_access = result['open_access']\n",
    "        primary_topic = result['primary_topic']\n",
    "\n",
    "        # Add the infos for the publication\n",
    "        publications_list_opa.append(\n",
    "            {\n",
    "                'doi': doi,\n",
    "                # 'publication_date': result['publication_date'], similar to 'published_date' from Unpaywall\n",
    "                'type_openalex': result['type'],\n",
    "\n",
    "                # Data from Unpaywall seems to be more up-to-date for now,\n",
    "                # might be used in the futur in place of Unpaywall ?\n",
    "                # 'is_oa_openalex': open_access['is_oa'],\n",
    "                # 'oa_status': open_access['oa_status'],\n",
    "                # 'repository': open_access['any_repository_has_fulltext'],\n",
    "\n",
    "                # 'is_paratext': result['is_paratext'], # same as Unpaywall\n",
    "                \n",
    "                'primary_topic': primary_topic['display_name'],\n",
    "                'subfield': primary_topic['subfield']['display_name'],\n",
    "                'field': primary_topic['field']['display_name'],\n",
    "                'domain': primary_topic['domain']['display_name'],\n",
    "            }\n",
    "        )  # fmt: skip\n",
    "    else:\n",
    "        # 0 or 2 results for a DOI\n",
    "        print(f'error: {doi}, {r_json['meta']['count']} result(s) returned!')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "fdb1cec1-d9da-496d-904d-d21432d8e550",
   "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>doi</th>\n",
       "      <th>type_openalex</th>\n",
       "      <th>primary_topic</th>\n",
       "      <th>subfield</th>\n",
       "      <th>field</th>\n",
       "      <th>domain</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.1128/AAC.00759-20</td>\n",
       "      <td>article</td>\n",
       "      <td>Efficacy and Safety of Antiretroviral Therapy ...</td>\n",
       "      <td>Infectious Diseases</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Health Sciences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.1093/cid/ciz670</td>\n",
       "      <td>article</td>\n",
       "      <td>Human Immunodeficiency Virus/Acquired Immunode...</td>\n",
       "      <td>Virology</td>\n",
       "      <td>Immunology and Microbiology</td>\n",
       "      <td>Life Sciences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10.1111/j.1582-4934.2008.00554.x</td>\n",
       "      <td>preprint</td>\n",
       "      <td>Pathophysiology and Management of Preeclampsia</td>\n",
       "      <td>Obstetrics and Gynecology</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Health Sciences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10.1016/j.placenta.2007.03.008</td>\n",
       "      <td>article</td>\n",
       "      <td>Neuroendocrine Regulation of Appetite and Body...</td>\n",
       "      <td>Endocrine and Autonomic Systems</td>\n",
       "      <td>Neuroscience</td>\n",
       "      <td>Life Sciences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10.1210/en.2005-0912</td>\n",
       "      <td>article</td>\n",
       "      <td>Pathophysiology and Management of Preeclampsia</td>\n",
       "      <td>Obstetrics and Gynecology</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Health Sciences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>357</th>\n",
       "      <td>10.1016/S2665-9913(20)30280-0</td>\n",
       "      <td>article</td>\n",
       "      <td>Epidemiology, Classification, and Clinical Fea...</td>\n",
       "      <td>Epidemiology</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Health Sciences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>358</th>\n",
       "      <td>10.1212/WNL.0000000000200257</td>\n",
       "      <td>article</td>\n",
       "      <td>Molecular Mechanisms of Neurodegenerative Dise...</td>\n",
       "      <td>Cellular and Molecular Neuroscience</td>\n",
       "      <td>Neuroscience</td>\n",
       "      <td>Life Sciences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>359</th>\n",
       "      <td>10.1016/S1474-4422(20)30235-0</td>\n",
       "      <td>article</td>\n",
       "      <td>Molecular Mechanisms of Neurodegenerative Dise...</td>\n",
       "      <td>Cellular and Molecular Neuroscience</td>\n",
       "      <td>Neuroscience</td>\n",
       "      <td>Life Sciences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>360</th>\n",
       "      <td>10.1016/S1474-4422(13)70104-2</td>\n",
       "      <td>article</td>\n",
       "      <td>Molecular Mechanisms of Neurodegenerative Dise...</td>\n",
       "      <td>Cellular and Molecular Neuroscience</td>\n",
       "      <td>Neuroscience</td>\n",
       "      <td>Life Sciences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>361</th>\n",
       "      <td>10.1148/radiol.220430</td>\n",
       "      <td>article</td>\n",
       "      <td>Diffusion Magnetic Resonance Imaging</td>\n",
       "      <td>Radiology, Nuclear Medicine and Imaging</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Health Sciences</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>362 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  doi type_openalex  \\\n",
       "0                10.1128/AAC.00759-20       article   \n",
       "1                  10.1093/cid/ciz670       article   \n",
       "2    10.1111/j.1582-4934.2008.00554.x      preprint   \n",
       "3      10.1016/j.placenta.2007.03.008       article   \n",
       "4                10.1210/en.2005-0912       article   \n",
       "..                                ...           ...   \n",
       "357     10.1016/S2665-9913(20)30280-0       article   \n",
       "358      10.1212/WNL.0000000000200257       article   \n",
       "359     10.1016/S1474-4422(20)30235-0       article   \n",
       "360     10.1016/S1474-4422(13)70104-2       article   \n",
       "361             10.1148/radiol.220430       article   \n",
       "\n",
       "                                         primary_topic  \\\n",
       "0    Efficacy and Safety of Antiretroviral Therapy ...   \n",
       "1    Human Immunodeficiency Virus/Acquired Immunode...   \n",
       "2       Pathophysiology and Management of Preeclampsia   \n",
       "3    Neuroendocrine Regulation of Appetite and Body...   \n",
       "4       Pathophysiology and Management of Preeclampsia   \n",
       "..                                                 ...   \n",
       "357  Epidemiology, Classification, and Clinical Fea...   \n",
       "358  Molecular Mechanisms of Neurodegenerative Dise...   \n",
       "359  Molecular Mechanisms of Neurodegenerative Dise...   \n",
       "360  Molecular Mechanisms of Neurodegenerative Dise...   \n",
       "361               Diffusion Magnetic Resonance Imaging   \n",
       "\n",
       "                                    subfield                        field  \\\n",
       "0                        Infectious Diseases                     Medicine   \n",
       "1                                   Virology  Immunology and Microbiology   \n",
       "2                  Obstetrics and Gynecology                     Medicine   \n",
       "3            Endocrine and Autonomic Systems                 Neuroscience   \n",
       "4                  Obstetrics and Gynecology                     Medicine   \n",
       "..                                       ...                          ...   \n",
       "357                             Epidemiology                     Medicine   \n",
       "358      Cellular and Molecular Neuroscience                 Neuroscience   \n",
       "359      Cellular and Molecular Neuroscience                 Neuroscience   \n",
       "360      Cellular and Molecular Neuroscience                 Neuroscience   \n",
       "361  Radiology, Nuclear Medicine and Imaging                     Medicine   \n",
       "\n",
       "              domain  \n",
       "0    Health Sciences  \n",
       "1      Life Sciences  \n",
       "2    Health Sciences  \n",
       "3      Life Sciences  \n",
       "4    Health Sciences  \n",
       "..               ...  \n",
       "357  Health Sciences  \n",
       "358    Life Sciences  \n",
       "359    Life Sciences  \n",
       "360    Life Sciences  \n",
       "361  Health Sciences  \n",
       "\n",
       "[362 rows x 6 columns]"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_openalex = pd.DataFrame(publications_list_opa).astype(\n",
    "    {\n",
    "        'type_openalex': 'category',\n",
    "        'primary_topic': 'category',\n",
    "        'subfield': 'category',\n",
    "        'field': 'category',\n",
    "        'domain': 'category',\n",
    "    }\n",
    ")\n",
    "df_openalex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "84e9dd04-1cde-4292-ad1a-08f9653e1bcc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "type_openalex\n",
       "article     358\n",
       "preprint      2\n",
       "review        2\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_openalex['type_openalex'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a8e123b7-5222-49c3-a546-54972eb3bd9e",
   "metadata": {},
   "source": [
    "#### Jointure des dataframes :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "6e0701bc-e657-4879-b6ef-28068ab30b58",
   "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>NCTId</th>\n",
       "      <th>BriefTitle</th>\n",
       "      <th>LeadSponsorName</th>\n",
       "      <th>CollaboratorName</th>\n",
       "      <th>OverallStatus</th>\n",
       "      <th>StudyType</th>\n",
       "      <th>HasResults</th>\n",
       "      <th>StudyFirstPostDate</th>\n",
       "      <th>StartDate</th>\n",
       "      <th>CompletionDate</th>\n",
       "      <th>...</th>\n",
       "      <th>is_paratext</th>\n",
       "      <th>published_date</th>\n",
       "      <th>is_oa</th>\n",
       "      <th>oa_status</th>\n",
       "      <th>has_repository_copy</th>\n",
       "      <th>type_openalex</th>\n",
       "      <th>primary_topic</th>\n",
       "      <th>subfield</th>\n",
       "      <th>field</th>\n",
       "      <th>domain</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NCT02099474</td>\n",
       "      <td>Evaluation of Raltegravir During the Third Tri...</td>\n",
       "      <td>ANRS, Emerging Infectious Diseases</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2014-03-31</td>\n",
       "      <td>2014-06-30</td>\n",
       "      <td>2017-04</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2020-09-21</td>\n",
       "      <td>True</td>\n",
       "      <td>green</td>\n",
       "      <td>True</td>\n",
       "      <td>article</td>\n",
       "      <td>Efficacy and Safety of Antiretroviral Therapy ...</td>\n",
       "      <td>Infectious Diseases</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Health Sciences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NCT02497274</td>\n",
       "      <td>Obesity and Lipids: a Matter of Taste?</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2015-07-14</td>\n",
       "      <td>2016-01-11</td>\n",
       "      <td>2019-01</td>\n",
       "      <td>...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NCT02592174</td>\n",
       "      <td>Prevalence, Characteristics and Risk Factors o...</td>\n",
       "      <td>ANRS, Emerging Infectious Diseases</td>\n",
       "      <td>Merck Sharp &amp; Dohme LLC</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2015-10-30</td>\n",
       "      <td>2016-01-26</td>\n",
       "      <td>2017-10-27</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2019-07-25</td>\n",
       "      <td>True</td>\n",
       "      <td>hybrid</td>\n",
       "      <td>False</td>\n",
       "      <td>article</td>\n",
       "      <td>Human Immunodeficiency Virus/Acquired Immunode...</td>\n",
       "      <td>Virology</td>\n",
       "      <td>Immunology and Microbiology</td>\n",
       "      <td>Life Sciences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NCT01490489</td>\n",
       "      <td>EG-VEGF : Potential Marker of Pre-eclampsia an...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2011-12-13</td>\n",
       "      <td>2011-07-11</td>\n",
       "      <td>2015-03-13</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2008-10-16</td>\n",
       "      <td>True</td>\n",
       "      <td>green</td>\n",
       "      <td>True</td>\n",
       "      <td>preprint</td>\n",
       "      <td>Pathophysiology and Management of Preeclampsia</td>\n",
       "      <td>Obstetrics and Gynecology</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Health Sciences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NCT01490489</td>\n",
       "      <td>EG-VEGF : Potential Marker of Pre-eclampsia an...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2011-12-13</td>\n",
       "      <td>2011-07-11</td>\n",
       "      <td>2015-03-13</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2007-10-01</td>\n",
       "      <td>False</td>\n",
       "      <td>closed</td>\n",
       "      <td>False</td>\n",
       "      <td>article</td>\n",
       "      <td>Neuroendocrine Regulation of Appetite and Body...</td>\n",
       "      <td>Endocrine and Autonomic Systems</td>\n",
       "      <td>Neuroscience</td>\n",
       "      <td>Life Sciences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>463</th>\n",
       "      <td>NCT01037777</td>\n",
       "      <td>RISCA : Prospective Study of Individuals at Ri...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>OBSERVATIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2009-12-23</td>\n",
       "      <td>2009-05-07</td>\n",
       "      <td>2017-12-14</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2013-07-01</td>\n",
       "      <td>False</td>\n",
       "      <td>closed</td>\n",
       "      <td>False</td>\n",
       "      <td>article</td>\n",
       "      <td>Molecular Mechanisms of Neurodegenerative Dise...</td>\n",
       "      <td>Cellular and Molecular Neuroscience</td>\n",
       "      <td>Neuroscience</td>\n",
       "      <td>Life Sciences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>464</th>\n",
       "      <td>NCT02833961</td>\n",
       "      <td>Diffusion Spectroscopy in Stroke</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2016-07-14</td>\n",
       "      <td>2016-07-28</td>\n",
       "      <td>2019-10-25</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>2023-03-01</td>\n",
       "      <td>True</td>\n",
       "      <td>green</td>\n",
       "      <td>True</td>\n",
       "      <td>article</td>\n",
       "      <td>Diffusion Magnetic Resonance Imaging</td>\n",
       "      <td>Radiology, Nuclear Medicine and Imaging</td>\n",
       "      <td>Medicine</td>\n",
       "      <td>Health Sciences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>465</th>\n",
       "      <td>NCT02976298</td>\n",
       "      <td>Effects of Cerebellum or Supplementary Motor A...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2016-11-29</td>\n",
       "      <td>2013-02</td>\n",
       "      <td>2015-01</td>\n",
       "      <td>...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>466</th>\n",
       "      <td>NCT01842477</td>\n",
       "      <td>Evaluation of Efficacy and Safety of Autologou...</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2013-04-29</td>\n",
       "      <td>2013-05</td>\n",
       "      <td>2016-02-05</td>\n",
       "      <td>...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>467</th>\n",
       "      <td>NCT02721745</td>\n",
       "      <td>Cause and Consequence of Neural Fatigue</td>\n",
       "      <td>Institut National de la Santé Et de la Recherc...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>COMPLETED</td>\n",
       "      <td>INTERVENTIONAL</td>\n",
       "      <td>False</td>\n",
       "      <td>2016-03-29</td>\n",
       "      <td>2016-05-23</td>\n",
       "      <td>2020-12</td>\n",
       "      <td>...</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>&lt;NA&gt;</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>468 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           NCTId                                         BriefTitle  \\\n",
       "0    NCT02099474  Evaluation of Raltegravir During the Third Tri...   \n",
       "1    NCT02497274             Obesity and Lipids: a Matter of Taste?   \n",
       "2    NCT02592174  Prevalence, Characteristics and Risk Factors o...   \n",
       "3    NCT01490489  EG-VEGF : Potential Marker of Pre-eclampsia an...   \n",
       "4    NCT01490489  EG-VEGF : Potential Marker of Pre-eclampsia an...   \n",
       "..           ...                                                ...   \n",
       "463  NCT01037777  RISCA : Prospective Study of Individuals at Ri...   \n",
       "464  NCT02833961                   Diffusion Spectroscopy in Stroke   \n",
       "465  NCT02976298  Effects of Cerebellum or Supplementary Motor A...   \n",
       "466  NCT01842477  Evaluation of Efficacy and Safety of Autologou...   \n",
       "467  NCT02721745            Cause and Consequence of Neural Fatigue   \n",
       "\n",
       "                                       LeadSponsorName  \\\n",
       "0                   ANRS, Emerging Infectious Diseases   \n",
       "1    Institut National de la Santé Et de la Recherc...   \n",
       "2                   ANRS, Emerging Infectious Diseases   \n",
       "3    Institut National de la Santé Et de la Recherc...   \n",
       "4    Institut National de la Santé Et de la Recherc...   \n",
       "..                                                 ...   \n",
       "463  Institut National de la Santé Et de la Recherc...   \n",
       "464  Institut National de la Santé Et de la Recherc...   \n",
       "465  Institut National de la Santé Et de la Recherc...   \n",
       "466  Institut National de la Santé Et de la Recherc...   \n",
       "467  Institut National de la Santé Et de la Recherc...   \n",
       "\n",
       "            CollaboratorName OverallStatus       StudyType  HasResults  \\\n",
       "0                       <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "1                       <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "2    Merck Sharp & Dohme LLC     COMPLETED  INTERVENTIONAL       False   \n",
       "3                       <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "4                       <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "..                       ...           ...             ...         ...   \n",
       "463                     <NA>     COMPLETED   OBSERVATIONAL       False   \n",
       "464                     <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "465                     <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "466                     <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "467                     <NA>     COMPLETED  INTERVENTIONAL       False   \n",
       "\n",
       "    StudyFirstPostDate   StartDate CompletionDate  ... is_paratext  \\\n",
       "0           2014-03-31  2014-06-30        2017-04  ...       False   \n",
       "1           2015-07-14  2016-01-11        2019-01  ...        <NA>   \n",
       "2           2015-10-30  2016-01-26     2017-10-27  ...       False   \n",
       "3           2011-12-13  2011-07-11     2015-03-13  ...       False   \n",
       "4           2011-12-13  2011-07-11     2015-03-13  ...       False   \n",
       "..                 ...         ...            ...  ...         ...   \n",
       "463         2009-12-23  2009-05-07     2017-12-14  ...       False   \n",
       "464         2016-07-14  2016-07-28     2019-10-25  ...       False   \n",
       "465         2016-11-29     2013-02        2015-01  ...        <NA>   \n",
       "466         2013-04-29     2013-05     2016-02-05  ...        <NA>   \n",
       "467         2016-03-29  2016-05-23        2020-12  ...        <NA>   \n",
       "\n",
       "    published_date  is_oa oa_status has_repository_copy type_openalex  \\\n",
       "0       2020-09-21   True     green                True       article   \n",
       "1              NaN   <NA>       NaN                <NA>           NaN   \n",
       "2       2019-07-25   True    hybrid               False       article   \n",
       "3       2008-10-16   True     green                True      preprint   \n",
       "4       2007-10-01  False    closed               False       article   \n",
       "..             ...    ...       ...                 ...           ...   \n",
       "463     2013-07-01  False    closed               False       article   \n",
       "464     2023-03-01   True     green                True       article   \n",
       "465            NaN   <NA>       NaN                <NA>           NaN   \n",
       "466            NaN   <NA>       NaN                <NA>           NaN   \n",
       "467            NaN   <NA>       NaN                <NA>           NaN   \n",
       "\n",
       "                                         primary_topic  \\\n",
       "0    Efficacy and Safety of Antiretroviral Therapy ...   \n",
       "1                                                  NaN   \n",
       "2    Human Immunodeficiency Virus/Acquired Immunode...   \n",
       "3       Pathophysiology and Management of Preeclampsia   \n",
       "4    Neuroendocrine Regulation of Appetite and Body...   \n",
       "..                                                 ...   \n",
       "463  Molecular Mechanisms of Neurodegenerative Dise...   \n",
       "464               Diffusion Magnetic Resonance Imaging   \n",
       "465                                                NaN   \n",
       "466                                                NaN   \n",
       "467                                                NaN   \n",
       "\n",
       "                                    subfield                        field  \\\n",
       "0                        Infectious Diseases                     Medicine   \n",
       "1                                        NaN                          NaN   \n",
       "2                                   Virology  Immunology and Microbiology   \n",
       "3                  Obstetrics and Gynecology                     Medicine   \n",
       "4            Endocrine and Autonomic Systems                 Neuroscience   \n",
       "..                                       ...                          ...   \n",
       "463      Cellular and Molecular Neuroscience                 Neuroscience   \n",
       "464  Radiology, Nuclear Medicine and Imaging                     Medicine   \n",
       "465                                      NaN                          NaN   \n",
       "466                                      NaN                          NaN   \n",
       "467                                      NaN                          NaN   \n",
       "\n",
       "              domain  \n",
       "0    Health Sciences  \n",
       "1                NaN  \n",
       "2      Life Sciences  \n",
       "3    Health Sciences  \n",
       "4      Life Sciences  \n",
       "..               ...  \n",
       "463    Life Sciences  \n",
       "464  Health Sciences  \n",
       "465              NaN  \n",
       "466              NaN  \n",
       "467              NaN  \n",
       "\n",
       "[468 rows x 26 columns]"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_with_openalex = df_with_oa_status.merge(\n",
    "    df_openalex,\n",
    "    on=['doi'],\n",
    "    how='left',\n",
    "    validate='many_to_one',\n",
    ")\n",
    "df_with_openalex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2c741cd4-91f7-4bb6-b3d9-230054902803",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "912957bb-e61d-4604-a12b-b73b95916502",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "d5132e99-076f-46b6-9007-fa34d0abef35",
   "metadata": {},
   "source": [
    "### Resultat final:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "d31b1848-6436-4ac9-a8bd-bd3d63558aa3",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 468 entries, 0 to 467\n",
      "Data columns (total 26 columns):\n",
      " #   Column               Non-Null Count  Dtype   \n",
      "---  ------               --------------  -----   \n",
      " 0   NCTId                468 non-null    object  \n",
      " 1   BriefTitle           468 non-null    string  \n",
      " 2   LeadSponsorName      468 non-null    string  \n",
      " 3   CollaboratorName     220 non-null    string  \n",
      " 4   OverallStatus        468 non-null    category\n",
      " 5   StudyType            468 non-null    category\n",
      " 6   HasResults           468 non-null    boolean \n",
      " 7   StudyFirstPostDate   468 non-null    string  \n",
      " 8   StartDate            468 non-null    string  \n",
      " 9   CompletionDate       468 non-null    string  \n",
      " 10  pmid                 369 non-null    object  \n",
      " 11  type                 369 non-null    category\n",
      " 12  title                369 non-null    object  \n",
      " 13  authors              369 non-null    object  \n",
      " 14  doi                  368 non-null    object  \n",
      " 15  publication_types    369 non-null    object  \n",
      " 16  is_paratext          368 non-null    boolean \n",
      " 17  published_date       368 non-null    object  \n",
      " 18  is_oa                368 non-null    boolean \n",
      " 19  oa_status            368 non-null    category\n",
      " 20  has_repository_copy  368 non-null    boolean \n",
      " 21  type_openalex        368 non-null    category\n",
      " 22  primary_topic        368 non-null    category\n",
      " 23  subfield             368 non-null    category\n",
      " 24  field                368 non-null    category\n",
      " 25  domain               368 non-null    category\n",
      "dtypes: boolean(4), category(9), object(7), string(6)\n",
      "memory usage: 60.8+ KB\n"
     ]
    }
   ],
   "source": [
    "df_with_openalex.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "52dafbcd-284c-4aa4-81f5-e2f6506a8105",
   "metadata": {},
   "source": [
    "# Export en CSV: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "062a8821-b680-4e5a-823e-256adda183e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_with_openalex.to_csv(\n",
    "    'Data/outputs/df_extract.csv',\n",
    "    sep=';',\n",
    "    index=False,\n",
    "    encoding='utf-8-sig',\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fb7433f5",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49f4e637-25ae-4ddf-871a-1d93e4f8b3dc",
   "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.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}