Create_NIH_dataset.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "fb6c66b4",
   "metadata": {},
   "source": [
    "# NIH grants dataset creation"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "3e36b938",
   "metadata": {},
   "source": [
    "### Through NIH API, data is mined for cross-referencing between winning applications and grant requests in this notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "c6c92f5c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import requests\n",
    "import pickle\n",
    "import json\n",
    "import csv\n",
    "import time\n",
    "import re\n",
    "from tqdm import tqdm\n",
    "# importing the libraries\n",
    "from bs4 import BeautifulSoup\n",
    "import requests\n",
    "from tqdm import tqdm\n",
    "tqdm.pandas()\n",
    "from ast import literal_eval\n",
    "import matplotlib.pyplot as plt\n",
    "import Levenshtein\n",
    "import io"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "4fb6c279",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "foas = pd.read_excel('nih_r01_funding_expired.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "471ff459",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4456"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(foas.Document_Number.tolist())"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "fbd868dc",
   "metadata": {},
   "source": [
    "In this notebook we will mine only the following research program grants: R01, R03, R21, R33 , R34:\n",
    "[This link](https://www.niaid.nih.gov/grants-contracts/research-project-grants) explains thoroughly the difference between R01 and R03/R21.<br>"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "5ddec0de",
   "metadata": {},
   "source": [
    "<u>Note:</u><br>\n",
    "FOA $\\rightarrow$ Fund Opportunity Announcement"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "d0f08983",
   "metadata": {},
   "source": [
    "## Mine R01 grants\n",
    "R01 is the NIH standard independent research project grant. An R01 is meant to give you four or five years of support to complete a project, publish, and reapply before the grant ends.\n",
    "\n",
    "An R01 is for mature research projects that are hypothesis-driven with strong preliminary data. R01s provide up to five years of support, with a budget that reflects the costs required to complete the project. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "9182188e",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|███████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 821/821 [8:33:18<00:00, 37.51s/it]\n"
     ]
    }
   ],
   "source": [
    "j=0\n",
    "for foa in tqdm(foas.Document_Number.tolist()[j::]):\n",
    "    if j%500==0 and j>0:\n",
    "        time.sleep(300)\n",
    "    headers = {\n",
    "        'accept': 'application/json',\n",
    "    }\n",
    "\n",
    "    json_data = {\n",
    "        \"criteria\": {\n",
    "            \"foa\": [\n",
    "                foa,\n",
    "            ]\n",
    "        },\n",
    "        \"limit\": 500,\n",
    "        \"sort_field\": \"appl_id\",\n",
    "        \"sort_order\": \"desc\"\n",
    "     }\n",
    "#     time.sleep(np.random.randint(2,5))\n",
    "    time.sleep(2)\n",
    "    response = requests.post('https://api.reporter.nih.gov/v2/projects/search', headers=headers, json=json_data)\n",
    "    data = json.loads(response.content)\n",
    "    df = df.append(pd.json_normalize(data['results'])).reset_index(drop=True)\n",
    "    j+=1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "6603fb71",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('nih_winning_grants_R01.csv',index=False)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "5acfb7b1",
   "metadata": {},
   "source": [
    "## Mine R03, R021, R33,R34 grants\n",
    "Small (**R03**) and exploratory/developmental research (**R21**) grants are designed for investigators who have small defined projects where preliminary data are not required.\n",
    "\n",
    "If researcher's project is not ready for an **R01**, he then can consider a two-year small grant (**R03**) or exploratory/developmental research grant (**R21**) to generate preliminary data or develop approaches that could support a hypothesis that can be tested under the **R01** activity code, later on.\n",
    "\n",
    "The NIH Planning Grant Program (**R34**) provides support for the initial development of a clinical trial or research project. This program may support:\n",
    "\n",
    "* establishment of the research team,\n",
    "* development of tools for data management and research oversight\n",
    "* development of a trial design or experimental research designs \n",
    "* finalization of the protocol\n",
    "* preparation of an operations/procedures manual\n",
    "* pilot studies or collection of feasibility data for subsequent research projects*\n",
    "\n",
    "The **R33** award is to provide a second phase for the support for innovative exploratory and development research activities initiated under the **R21** mechanism. - this is why all the **R33** requests will be later dropped.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e850656a",
   "metadata": {},
   "outputs": [],
   "source": [
    "foas = pd.read_excel('nih_R03R21R33R34_funding_expired.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fe2ebc58",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('nih_winning_data_R03R21R33R34.csv')\n",
    "foa = foas.Document_Number.tolist()\n",
    "range_start = 0 # if breaks at some number, change to the number where the mining stopped\n",
    "for i in tqdm(range(range_start,3535,5)):\n",
    "    if i%500==0 and i>0:\n",
    "        time.sleep(600)\n",
    "        \n",
    "    headers = {\n",
    "        'accept': 'application/json',\n",
    "    }\n",
    "\n",
    "    json_data = {\n",
    "        \"criteria\": {\n",
    "            \"foa\": foa[i:i+5]\n",
    "        },\n",
    "        \"limit\": 500,\n",
    "        \"sort_field\": \"appl_id\",\n",
    "        \"sort_order\": \"desc\"\n",
    "     }\n",
    "    time.sleep(2)\n",
    "    response = requests.post('https://api.reporter.nih.gov/v2/projects/search', headers=headers, json=json_data)\n",
    "    data = json.loads(response.content)\n",
    "    df = df.append(pd.json_normalize(data['results'])).reset_index(drop=True)\n",
    "    df.to_csv('nih_winning_data_R03R21R33R34.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "265ff073",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_r01 = pd.read_csv('nih_winning_grants_R01.csv')\n",
    "df_rest = pd.read_csv('nih_winning_data_R03R21R33R34.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "c059a72a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final = pd.concat([df_r01,df_rest]).reset_index(drop=True)\n",
    "df_final = df_final.drop_duplicates(subset='appl_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "ca355e02",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final.to_csv('nih_winning_data_ALL.csv',index=False)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "fd0d4c38",
   "metadata": {},
   "source": [
    "Drop R33 grants:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ebe03b57",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final = pd.read_csv('nih_winning_data_ALL.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "78cc98f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "foas = pd.read_excel('nih_R03R21R33R34_funding_expired.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "a906166a",
   "metadata": {},
   "outputs": [],
   "source": [
    "r21= foas['Activity_Code'].str.contains('R21',regex=False)\n",
    "r01 = foas['Activity_Code'].str.contains('R01',regex=False)\n",
    "r03 = foas['Activity_Code'].str.contains('R03',regex=False)\n",
    "r34 = foas['Activity_Code'].str.contains('R34',regex=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "322d42c6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3539, 11)"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "foas.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "9e98ba81",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3394, 11)"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "foas =foas.loc[r21 | r01 | r03 | r34]\n",
    "foas.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "50023c94",
   "metadata": {},
   "outputs": [],
   "source": [
    "foas = foas[~foas.Document_Number.str.contains('NOT',case=True,regex=False)].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "fcce640b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3390, 11)"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "foas.shape"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "fdc262b7",
   "metadata": {},
   "source": [
    "Create unified grant calls file and filtered winning applications file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "4e1e8238",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4456, 11)"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "foas_r01 = pd.read_excel('nih_r01_funding_expiredxlsx.xlsx')\n",
    "foas_r01.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "17b867cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "foas_final = pd.concat([foas_r01,foas]).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "9a94e776",
   "metadata": {},
   "outputs": [],
   "source": [
    "foas_final.to_excel('foas_final.xlsx',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "a5bbe693",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final = df_final[df_final['full_foa'].isin(foas_final['Document_Number'])].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "1bbbe36c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final.to_csv('nih_winning_data_filtered.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "3440707c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(226994, 80)"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final.shape"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "a120dacd",
   "metadata": {},
   "source": [
    "## Get grant calls information"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "a4f5d274",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('nih_winning_data_filtered.csv',low_memory=False)\n",
    "foas = pd.read_excel('foas_final.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "aaf8dd3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "RFA = foas[foas['Document_Number'].str.contains('RFA',regex=False)]\n",
    "PAR= foas[foas['Document_Number'].str.contains('PAR',regex=False)]\n",
    "PA =foas[foas['Document_Number'].str.contains('PA-',regex=False)]\n",
    "PAS =foas[foas['Document_Number'].str.contains('PAS',regex=False)]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "a08f2b36",
   "metadata": {},
   "source": [
    "### Create a function to scrape short text description out of nih grant calls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "5b6a12bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "def case_1(soup):\n",
    "    a = soup.find(attrs = {\"class\":'row'})\n",
    "    if a:\n",
    "        start = \"Funding Opportunity Purpose\"\n",
    "        end = \"Key Dates\"\n",
    "        abstract= a.text[a.text.find(start)+len(start)+1:a.text.find(end)].replace('\\n', '').strip()\\\n",
    "        .replace('\\r', '').strip()\\\n",
    "        .replace('\\t',' ').strip()\\\n",
    "        .replace('        ',' ').strip()\\\n",
    "        .replace('\\xa0','').strip()\\\n",
    "        .replace('--','-').strip()\\\n",
    "        .replace('   ',' ').strip()\\\n",
    "        .replace('  ',' ').strip()\n",
    "        abstract = abstract.replace('  ',' ').strip()\n",
    "        return abstract\n",
    "    return False\n",
    "\n",
    "def case_2(soup):\n",
    "    if soup.text.find(\"PA NUMBER:\") >0 and soup.text.find(\"\\n\\n\\n\\n\\n\\n\\n\\r\\n\\r\\n\")>0:\n",
    "        return case_panumber(soup)\n",
    "    \n",
    "    if case_6(soup):\n",
    "        return case_6(soup)\n",
    "    if soup.text.find(\"PURPOSE OF THIS PA\") > 0 or soup.text.find(\"PURPOSE OF THE PA\") >0 or soup.text.find(\"PURPOSE OF THIS PAR\") >0 or soup.text.find(\"PURPOSE OF THE PAR\") >0 or soup.text.find(\"PURPOSE OF THIS PAS\")>0:\n",
    "        return case_7(soup)\n",
    "    start = soup.text.find(\"PURPOSE\")\n",
    "    if start > 0 and (soup.text.find(\"PURPOSE OF THIS RFA\") >0 or soup.text.find(\"PURPOSE OF THE RFA\") >0):\n",
    "        return False #GO TO CASE 3\n",
    "    if start > 0 and soup.text.find(\"Executive Summary\") > 0:\n",
    "        return False #GO TO CASE 4\n",
    "    if start >0:\n",
    "        i=0\n",
    "        c = soup.text[start+len(\"PURPOSE\")::][i]\n",
    "        while c==' ' or c=='\\n' or c=='\\r' or c=='\\t':\n",
    "            i+=1\n",
    "            c=soup.text[start+len(\"PURPOSE\")::][i]\n",
    "        start_fixed = start+len(\"PURPOSE\")+i\n",
    "        end1 = soup.text.find('HEALTHY PEOPLE') if soup.text.find('HEALTHY PEOPLE') > 0 else 100000000\n",
    "        end2 = soup.text.find('RESEARCH OBJECTIVES') if soup.text.find('RESEARCH OBJECTIVES') > 0 else 100000000\n",
    "        if end1 ==100000000 and end2==100000000 :\n",
    "            end = -1\n",
    "        else:\n",
    "            end = min(end1,end2)     \n",
    "        if end > 0:\n",
    "            abstract = soup.text[start_fixed:end].split('\\r\\n\\r\\n')[0].replace('\\r\\n', ' ').strip()\\\n",
    "            .replace('\\n','').strip()\\\n",
    "            .replace('\\r', '').strip()\\\n",
    "            .replace('\\t',' ').strip()\\\n",
    "            .replace('        ',' ').strip()\\\n",
    "            .replace('\\xa0','').strip()\\\n",
    "            .replace('--','-').strip()\\\n",
    "            .replace('   ',' ').strip()\\\n",
    "            .replace('  ',' ').strip()\n",
    "            abstract = abstract.replace('  ',' ').strip()\n",
    "            return abstract\n",
    "        abstract = soup.text[ start_fixed::].split('\\r\\n\\r\\n')[0].replace('\\r\\n', ' ').strip()\\\n",
    "            .replace('\\n','').strip()\\\n",
    "            .replace('\\r', '').strip()\\\n",
    "            .replace('\\t',' ').strip()\\\n",
    "            .replace('        ',' ').strip()\\\n",
    "            .replace('\\xa0','').strip()\\\n",
    "            .replace('--','-').strip()\\\n",
    "            .replace('   ',' ').strip()\\\n",
    "            .replace('  ',' ').strip()\n",
    "        abstract = abstract.replace('  ',' ').strip()\n",
    "        return abstract\n",
    "    return False\n",
    "\n",
    "\n",
    "def case_3(soup):\n",
    "    start = -1\n",
    "    if soup.text.find(\"This is a reissue of RFA MH-03-001.\") > 0:\n",
    "        #specific case\n",
    "        souptext = soup.text.replace(\"This is a reissue of RFA MH-03-001.\",'')\n",
    "        start = souptext.find(\"PURPOSE OF THIS RFA\")\n",
    "        if start >0:\n",
    "            i=0\n",
    "            c = souptext[start+len(\"PURPOSE OF THIS RFA\")::][i]\n",
    "            while c==' ' or c=='\\n' or c=='\\r' or c=='\\t':\n",
    "                i+=1\n",
    "                c=souptext[start+len(\"PURPOSE OF THIS RFA\")::][i]\n",
    "            start_fixed = start+len(\"PURPOSE OF THIS RFA\")+i\n",
    "            abstract = souptext[start_fixed::].split('\\r\\n\\r\\n')[0].replace('\\r\\n', ' ').strip()\\\n",
    "                .replace('\\n','').strip()\\\n",
    "                .replace('\\r', '').strip()\\\n",
    "                .replace('\\t',' ').strip()\\\n",
    "                .replace('        ',' ').strip()\\\n",
    "                .replace('\\xa0','').strip()\\\n",
    "                .replace('--','-').strip()\\\n",
    "                .replace('   ',' ').strip()\\\n",
    "                .replace('  ',' ').strip()\n",
    "            abstract = abstract.replace('  ',' ').strip()\n",
    "            return abstract\n",
    "    if soup.text.find(\"PURPOSE OF THIS RFA\") > 0:\n",
    "        start = soup.text.find(\"PURPOSE OF THIS RFA\")\n",
    "        txt = \"PURPOSE OF THIS RFA\"\n",
    "    elif  soup.text.find(\"PURPOSE OF THE RFA\") >0:\n",
    "        start = soup.text.find(\"PURPOSE OF THE RFA\")\n",
    "        txt = \"PURPOSE OF THE RFA\"\n",
    "    if start >0:\n",
    "        i=0\n",
    "        c = soup.text[start+len(txt)::][i]\n",
    "        while c==' ' or c=='\\n' or c=='\\r' or c=='\\t':\n",
    "            i+=1\n",
    "            c=soup.text[start+len(txt)::][i]\n",
    "        start_fixed = start+len(txt)+i\n",
    "        end = soup.text.find(\"RESEARCH OBJECTIVES\")\n",
    "        \n",
    "        if end >0:\n",
    "            abstract = soup.text[start_fixed:end].split('\\r\\n\\r\\n')[0].replace('\\r\\n', ' ').strip()\\\n",
    "            .replace('\\n','').strip()\\\n",
    "            .replace('\\r', '').strip()\\\n",
    "            .replace('\\t',' ').strip()\\\n",
    "            .replace('        ',' ').strip()\\\n",
    "            .replace('\\xa0','').strip()\\\n",
    "            .replace('--','-').strip()\\\n",
    "            .replace('   ',' ').strip()\\\n",
    "            .replace('  ',' ').strip()\n",
    "            abstract = abstract.replace('  ',' ').strip()\n",
    "            return abstract\n",
    "        \n",
    "        abstract = soup.text[start_fixed::].split('\\r\\n\\r\\n')[0].replace('\\r\\n', ' ').strip()\\\n",
    "            .replace('\\n','').strip()\\\n",
    "            .replace('\\r', '').strip()\\\n",
    "            .replace('\\t',' ').strip()\\\n",
    "            .replace('        ',' ').strip()\\\n",
    "            .replace('\\xa0','').strip()\\\n",
    "            .replace('--','-').strip()\\\n",
    "            .replace('   ',' ').strip()\\\n",
    "            .replace('  ',' ').strip()\n",
    "        abstract = abstract.replace('  ',' ').strip()\n",
    "        return abstract\n",
    "    \n",
    "    return False\n",
    "\n",
    "def case_4(soup):\n",
    "    start = -1\n",
    "    if soup.text.find(\"Executive Summary\") > 0:\n",
    "        start = soup.text.find(\"Executive Summary\")\n",
    "        txt = \"Executive Summary\"\n",
    "    if soup.text.find(\"Executive\\r\\n    Summary\") > 0:\n",
    "        start = soup.text.find(\"Executive\\r\\n    Summary\")\n",
    "        txt = \"Executive\\r\\n    Summary\"\n",
    "    if start >0:\n",
    "        i=0\n",
    "        c = soup.text[start+len(txt)::][i]\n",
    "        while c==' ' or c=='\\n' or c=='\\r' or c=='\\t':\n",
    "            i+=1\n",
    "            c=soup.text[start+len(txt)::][i]\n",
    "        start_fixed = start+len(txt)+i\n",
    "    if start> 0:\n",
    "       if 'Purpose.' in soup.text[start_fixed::] or 'Purpose .' in soup.text[start_fixed::]:\n",
    "            start_fixed+=len('Purpose. ') if  'Purpose.' in soup.text[start_fixed::] else len('Purpose . ') \n",
    "            end1 = soup.text.find(\"Mechanism\\r\\n      of Support\") if soup.text.find(\"Mechanism\\r\\n      of Support\") > 0 else 100000000\n",
    "            end2 = soup.text.find(\"Mechanism of Support\") if soup.text.find(\"Mechanism of Support\") > 0 else 100000000\n",
    "            end3 = soup.text.find(\"Mechanism of\\r\\n      Support\") if soup.text.find(\"Mechanism of\\r\\n      Support\") > 0 else 100000000\n",
    "            end4 = soup.text.find(\"Mechanism\\r\\n     of Support\") if soup.text.find(\"Mechanism\\r\\n     of Support\") > 0 else 100000000\n",
    "\n",
    "\n",
    "            if end1 ==100000000 and end2==100000000 and end3==100000000 and end4==100000000:\n",
    "                end = -1\n",
    "            else:\n",
    "                end = min(end1,end2,end3,end4)              \n",
    "            abstract = soup.text[start_fixed:end].split('\\r\\n\\r\\n')[0].replace('\\r\\n', ' ').strip()\\\n",
    "            .replace('\\n','').strip()\\\n",
    "            .replace('\\r', '').strip()\\\n",
    "            .replace('\\t',' ').strip()\\\n",
    "            .replace('        ',' ').strip()\\\n",
    "            .replace('\\xa0','').strip()\\\n",
    "            .replace('--','-').strip()\\\n",
    "            .replace('   ',' ').strip()\\\n",
    "            .replace('  ',' ').strip()\n",
    "            abstract = abstract.replace('  ',' ').strip()\n",
    "            return abstract\n",
    "    return False\n",
    "\n",
    "def case_5(soup):\n",
    "#     Executive\\r\\n  Summary\n",
    "    start = -1\n",
    "    if soup.text.find(\"Executive Summary\") > 0:\n",
    "        start = soup.text.find(\"Executive Summary\")\n",
    "        txt = \"Executive Summary\"\n",
    "    if soup.text.find(\"Executive\\r\\n    Summary\") > 0:\n",
    "        start = soup.text.find(\"Executive\\r\\n    Summary\")\n",
    "        txt = \"Executive\\r\\n    Summary\"\n",
    "    if soup.text.find(\"Executive\\r\\n  Summary\") > 0:\n",
    "        start = soup.text.find(\"Executive\\r\\n  Summary\")\n",
    "        txt = \"Executive\\r\\n  Summary\"\n",
    "    if start >0:\n",
    "        i=0\n",
    "        c = soup.text[start+len(txt)::][i]\n",
    "        while c==' ' or c=='\\n' or c=='\\r' or c=='\\t':\n",
    "            i+=1\n",
    "            c=soup.text[start+len(txt)::][i]\n",
    "        start_fixed = start+len(txt)+i\n",
    "        end1 = soup.text[start_fixed::].find('.\\n') if soup.text[start_fixed::].find('.\\n') > 0 else 100000000\n",
    "        end2 = soup.text[start_fixed::].find('. \\n') if soup.text[start_fixed::].find('. \\n') > 0 else 100000000\n",
    "        if end1 ==100000000 and end2==100000000:\n",
    "            end = -1\n",
    "        else:\n",
    "            end = min(end1,end2)\n",
    "        if end <0:\n",
    "            return False\n",
    "        end= start_fixed+end\n",
    "        abstract = soup.text[start_fixed:end].split('\\r\\n\\r\\n')[0].replace('\\r\\n', ' ').strip()\\\n",
    "            .replace('\\n','').strip()\\\n",
    "            .replace('\\r', '').strip()\\\n",
    "            .replace('\\t',' ').strip()\\\n",
    "            .replace('        ',' ').strip()\\\n",
    "            .replace('\\xa0','').strip()\\\n",
    "            .replace('--','-').strip()\\\n",
    "            .replace('   ',' ').strip()\\\n",
    "            .replace('  ',' ').strip()\n",
    "        abstract = abstract.replace('  ',' ').strip()\n",
    "        return abstract\n",
    "    return False\n",
    "\n",
    "def case_6(soup):\n",
    "    start = -1\n",
    "    if soup.text.find(\"Funding Opportunity Purpose\") > 0:\n",
    "        start = soup.text.find(\"Funding Opportunity Purpose\")\n",
    "        txt = \"Funding Opportunity Purpose\"\n",
    "    if  soup.text.find(\"FOA Purpose\") >0:\n",
    "        start = soup.text.find(\"FOA Purpose\")\n",
    "        txt = \"FOA Purpose\"\n",
    "    if start <0:\n",
    "        return False\n",
    "    i=0\n",
    "    c = soup.text[start+len(txt)::][i]\n",
    "    while c==' ' or c=='\\n' or c=='\\r' or c=='\\t':\n",
    "        i+=1\n",
    "        c=soup.text[start+len(txt)::][i]\n",
    "    start_fixed = start+len(txt)+i\n",
    "    end =  soup.text[start_fixed::].find(\"Key Dates\") + start_fixed\n",
    "    abstract = soup.text[start_fixed:end].split('\\r\\n\\r\\n')[0].replace('\\r\\n', ' ').strip()\\\n",
    "        .replace('\\n','').strip()\\\n",
    "        .replace('\\r', '').strip()\\\n",
    "        .replace('\\t',' ').strip()\\\n",
    "        .replace('        ',' ').strip()\\\n",
    "        .replace('\\xa0','').strip()\\\n",
    "        .replace('--','-').strip()\\\n",
    "        .replace('   ',' ').strip()\\\n",
    "        .replace('  ',' ').strip()\n",
    "    abstract = abstract.replace('  ',' ').strip()\n",
    "    return abstract\n",
    "\n",
    "def case_7(soup):\n",
    "    start = -1\n",
    "    if  soup.text.find(\"PURPOSE OF THIS PAS\") >0:\n",
    "        start = soup.text.find(\"PURPOSE OF THIS PAS\")\n",
    "        txt = \"PURPOSE OF THIS PAS\"\n",
    "    elif soup.text.find(\"PURPOSE OF THIS PAR\") > 0:\n",
    "        start = soup.text.find(\"PURPOSE OF THIS PAR\")\n",
    "        txt = \"PURPOSE OF THIS PAR\"\n",
    "    elif soup.text.find(\"PURPOSE OF THIS PA:\") > 0:\n",
    "        start = soup.text.find(\"PURPOSE OF THIS PA:\")\n",
    "        txt = \"PURPOSE OF THIS PA:\"\n",
    "    elif soup.text.find(\"PURPOSE OF THIS PA\") > 0:\n",
    "        start = soup.text.find(\"PURPOSE OF THIS PA\")\n",
    "        txt = \"PURPOSE OF THIS PA\"\n",
    "    elif  soup.text.find(\"PURPOSE OF THE PAR\") >0:\n",
    "        start = soup.text.find(\"PURPOSE OF THE PAR\")\n",
    "        txt = \"PURPOSE OF THE PAR\"\n",
    "    elif  soup.text.find(\"PURPOSE OF THE PA\") >0:\n",
    "        start = soup.text.find(\"PURPOSE OF THE PA\")\n",
    "        txt = \"PURPOSE OF THE PA\"\n",
    "\n",
    "    if start >0:\n",
    "        i=0\n",
    "        c = soup.text[start+len(txt)::][i]\n",
    "        while c==' ' or c=='\\n' or c=='\\r' or c=='\\t':\n",
    "            i+=1\n",
    "            c=soup.text[start+len(txt)::][i]\n",
    "        start_fixed = start+len(txt)+i\n",
    "        end = soup.text.find(\"RESEARCH OBJECTIVES\")\n",
    "        \n",
    "        if end >0:\n",
    "            abstract = soup.text[start_fixed:end].split('\\r\\n\\r\\n')[0].replace('\\r\\n', ' ').strip()\\\n",
    "            .replace('\\n','').strip()\\\n",
    "            .replace('\\r', '').strip()\\\n",
    "            .replace('\\t',' ').strip()\\\n",
    "            .replace('        ',' ').strip()\\\n",
    "            .replace('\\xa0','').strip()\\\n",
    "            .replace('--','-').strip()\\\n",
    "            .replace('   ',' ').strip()\\\n",
    "            .replace('  ',' ').strip()\n",
    "            abstract = abstract.replace('  ',' ').strip()\n",
    "            return abstract\n",
    "        \n",
    "        abstract = soup.text[start_fixed::].split('\\r\\n\\r\\n')[0].replace('\\r\\n', ' ').strip()\\\n",
    "            .replace('\\n','').strip()\\\n",
    "            .replace('\\r', '').strip()\\\n",
    "            .replace('\\t',' ').strip()\\\n",
    "            .replace('        ',' ').strip()\\\n",
    "            .replace('\\xa0','').strip()\\\n",
    "            .replace('--','-').strip()\\\n",
    "            .replace('   ',' ').strip()\\\n",
    "            .replace('  ',' ').strip()\n",
    "        abstract = abstract.replace('  ',' ').strip()\n",
    "        return abstract\n",
    "    \n",
    "    return False\n",
    "def case_panumber(soup):\n",
    "    start = soup.text.find(\"PURPOSE\")\n",
    "    if start >0:\n",
    "        i=0\n",
    "        c = soup.text[start+len(\"PURPOSE\")::][i]\n",
    "        while c==' ' or c=='\\n' or c=='\\r' or c=='\\t':\n",
    "            i+=1\n",
    "            c=soup.text[start+len(\"PURPOSE\")::][i]\n",
    "        start_fixed = start+len(\"PURPOSE\")+i\n",
    "        end1 = soup.text.find('HEALTHY PEOPLE') if soup.text.find('HEALTHY PEOPLE') > 0 else 100000000\n",
    "        end2 = soup.text.find('RESEARCH OBJECTIVES') if soup.text.find('RESEARCH OBJECTIVES') > 0 else 100000000\n",
    "        end3 = soup.text.find('ELIGIBILITY REQUIREMENTS') if soup.text.find('ELIGIBILITY REQUIREMENTS') > 0 else 100000000\n",
    "        if end1 ==100000000 and end2==100000000 and end3==100000000 :\n",
    "            end = -1\n",
    "        else:\n",
    "            end = min(end1,end2,end3)     \n",
    "        if end > 0:\n",
    "            abstract = soup.text[start_fixed:end].replace('\\r\\n', ' ').strip()\\\n",
    "            .replace('\\n','').strip()\\\n",
    "            .replace('\\r', '').strip()\\\n",
    "            .replace('\\t',' ').strip()\\\n",
    "            .replace('        ',' ').strip()\\\n",
    "            .replace('\\xa0','').strip()\\\n",
    "            .replace('--','-').strip()\\\n",
    "            .replace('   ',' ').strip()\\\n",
    "            .replace('  ',' ').strip()\n",
    "            abstract = abstract.replace('  ',' ').strip()\n",
    "            return abstract\n",
    "        abstract = soup.text[ start_fixed::].split('\\r\\n\\r\\n')[0].replace('\\r\\n', ' ').strip()\\\n",
    "            .replace('\\n','').strip()\\\n",
    "            .replace('\\r', '').strip()\\\n",
    "            .replace('\\t',' ').strip()\\\n",
    "            .replace('        ',' ').strip()\\\n",
    "            .replace('\\xa0','').strip()\\\n",
    "            .replace('--','-').strip()\\\n",
    "            .replace('   ',' ').strip()\\\n",
    "            .replace('  ',' ').strip()\n",
    "        abstract = abstract.replace('  ',' ').strip()\n",
    "        return abstract\n",
    "    return False\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "c4fc3000",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_descript(url):\n",
    "    # Make a GET request to fetch the raw HTML content\n",
    "    html_content = requests.get(url).text\n",
    "\n",
    "    soup = BeautifulSoup(html_content, \"lxml\")\n",
    "    if case_1(soup):\n",
    "        return case_1(soup)\n",
    "    \n",
    "    elif case_2(soup):\n",
    "        return case_2(soup)\n",
    "    \n",
    "    elif case_3(soup):\n",
    "        return case_3(soup)\n",
    "    \n",
    "    elif case_4(soup):\n",
    "        return case_4(soup)\n",
    "    \n",
    "    elif case_5(soup):\n",
    "        return case_5(soup)\n",
    "    \n",
    "    elif case_6(soup):\n",
    "        return case_6(soup)\n",
    "    else:\n",
    "        return \"Empty\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "48f7453b",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 7846/7846 [6:20:06<00:00,  2.91s/it]\n"
     ]
    }
   ],
   "source": [
    "foas_descrip =[]\n",
    "k = 0\n",
    "for url in tqdm(foas['URL'].tolist()):\n",
    "    if k%2000==0 and k>0:\n",
    "        time.sleep(300)\n",
    "    time.sleep(2)\n",
    "    foas_descrip.append(get_descript(url))\n",
    "    k+=1\n",
    "    with open('foas_descript.pickle', 'wb') as handle:\n",
    "        pickle.dump(foas_descrip, handle, protocol=pickle.HIGHEST_PROTOCOL)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "5f622a0a",
   "metadata": {},
   "outputs": [],
   "source": [
    "foas['descrip'] = foas_descrip"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "279efd2e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7831\n"
     ]
    }
   ],
   "source": [
    "foas_final = foas.query('descrip!=\"Empty\"').reset_index(drop=True)\n",
    "print(len(foas_final))"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "9034f811",
   "metadata": {},
   "source": [
    "Filter failed scraping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "6d3715c8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7781\n"
     ]
    }
   ],
   "source": [
    "foas_final = foas_final[foas_final.descrip.apply(len) > 60].reset_index(drop=True)\n",
    "print(len(foas_final))"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "fbe84040",
   "metadata": {},
   "source": [
    "remove parenthesis from title"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "64b4f48f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "7126\n"
     ]
    }
   ],
   "source": [
    "foas_final['Title'] = foas_final['Title'].apply(lambda x: re.sub(r'\\([^)]*\\)', '', x))\n",
    "foas_final['Title'] = foas_final['Title'].apply(lambda x: re.sub(r'\\[.*\\]','', x))\n",
    "foas_final['Title'] = foas_final['Title'].apply(lambda x: re.sub(r'\\s+$','', x))\n",
    "foas_final['Title'] = foas_final['Title'].str.replace(\"\\n\",\"\")\n",
    "foas_final['Title'] = foas_final['Title'].str.replace(\"  \",\" \")\n",
    "foas_final['Title'] = foas_final['Title'].str.replace(\"   \",\" \")\n",
    "foas_final = foas_final.drop_duplicates().reset_index(drop=True)\n",
    "print(len(foas_final))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "ddd45b55",
   "metadata": {},
   "outputs": [],
   "source": [
    "foas_final.to_csv('NIH_GrantCalls_With_Short_Description.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "4286b3fc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7126, 4410)"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "foas_final['Document_Number'].unique().shape[0] , foas_final['Title'].unique().shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "41987b46",
   "metadata": {
    "collapsed": true
   },
   "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>appl_id</th>\n",
       "      <th>subproject_id</th>\n",
       "      <th>fiscal_year</th>\n",
       "      <th>project_num</th>\n",
       "      <th>project_serial_num</th>\n",
       "      <th>award_type</th>\n",
       "      <th>activity_code</th>\n",
       "      <th>award_amount</th>\n",
       "      <th>is_active</th>\n",
       "      <th>principal_investigators</th>\n",
       "      <th>...</th>\n",
       "      <th>full_study_section.srg_code</th>\n",
       "      <th>full_study_section.srg_flex</th>\n",
       "      <th>full_study_section.sra_designator_code</th>\n",
       "      <th>full_study_section.sra_flex_code</th>\n",
       "      <th>full_study_section.group_code</th>\n",
       "      <th>full_study_section.name</th>\n",
       "      <th>full_study_section.url</th>\n",
       "      <th>full_study_section.cmte_id</th>\n",
       "      <th>full_study_section.cluster_irg_code</th>\n",
       "      <th>full_study_section</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10672784</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023</td>\n",
       "      <td>1R01HD112031-01</td>\n",
       "      <td>HD112031</td>\n",
       "      <td>1</td>\n",
       "      <td>R01</td>\n",
       "      <td>586134.0</td>\n",
       "      <td>True</td>\n",
       "      <td>[{'profile_id': 8024549, 'first_name': 'John',...</td>\n",
       "      <td>...</td>\n",
       "      <td>ZHD1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DSR</td>\n",
       "      <td>H</td>\n",
       "      <td>55</td>\n",
       "      <td>Special Emphasis Panel[ZHD1 DSR-H (55)]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10672642</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023</td>\n",
       "      <td>1R01HD112028-01</td>\n",
       "      <td>HD112028</td>\n",
       "      <td>1</td>\n",
       "      <td>R01</td>\n",
       "      <td>612196.0</td>\n",
       "      <td>True</td>\n",
       "      <td>[{'profile_id': 8532773, 'first_name': 'James'...</td>\n",
       "      <td>...</td>\n",
       "      <td>ZHD1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DSR</td>\n",
       "      <td>H</td>\n",
       "      <td>55</td>\n",
       "      <td>Special Emphasis Panel[ZHD1 DSR-H (55)]</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>10670574</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023</td>\n",
       "      <td>1R01DE032868-01</td>\n",
       "      <td>DE032868</td>\n",
       "      <td>1</td>\n",
       "      <td>R01</td>\n",
       "      <td>553978.0</td>\n",
       "      <td>True</td>\n",
       "      <td>[{'profile_id': 10451583, 'first_name': 'MARIA...</td>\n",
       "      <td>...</td>\n",
       "      <td>ZDE1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TO</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12</td>\n",
       "      <td>Special Emphasis Panel[ZDE1 TO (12)]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10667157</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023</td>\n",
       "      <td>1R01AG082142-01</td>\n",
       "      <td>AG082142</td>\n",
       "      <td>1</td>\n",
       "      <td>R01</td>\n",
       "      <td>654613.0</td>\n",
       "      <td>True</td>\n",
       "      <td>[{'profile_id': 12467458, 'first_name': 'Wang-...</td>\n",
       "      <td>...</td>\n",
       "      <td>ZAG1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ZIJ</td>\n",
       "      <td>G</td>\n",
       "      <td>J1</td>\n",
       "      <td>Special Emphasis Panel[ZAG1 ZIJ-G (J1)]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10667151</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023</td>\n",
       "      <td>1R01AG082140-01</td>\n",
       "      <td>AG082140</td>\n",
       "      <td>1</td>\n",
       "      <td>R01</td>\n",
       "      <td>622753.0</td>\n",
       "      <td>True</td>\n",
       "      <td>[{'profile_id': 6721519, 'first_name': 'Mohani...</td>\n",
       "      <td>...</td>\n",
       "      <td>ZAG1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ZIJ</td>\n",
       "      <td>G</td>\n",
       "      <td>J1</td>\n",
       "      <td>Special Emphasis Panel[ZAG1 ZIJ-G (J1)]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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>226989</th>\n",
       "      <td>2053349</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1994</td>\n",
       "      <td>3R03AG012003-01S1</td>\n",
       "      <td>AG012003</td>\n",
       "      <td>3</td>\n",
       "      <td>R03</td>\n",
       "      <td>15000.0</td>\n",
       "      <td>False</td>\n",
       "      <td>[{'profile_id': 1875152, 'first_name': 'REIKO'...</td>\n",
       "      <td>...</td>\n",
       "      <td>ZAG1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLL</td>\n",
       "      <td>8</td>\n",
       "      <td>01</td>\n",
       "      <td>ZAG1-CLL-8(01)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226990</th>\n",
       "      <td>2053348</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1993</td>\n",
       "      <td>1R03AG012003-01</td>\n",
       "      <td>AG012003</td>\n",
       "      <td>1</td>\n",
       "      <td>R03</td>\n",
       "      <td>21600.0</td>\n",
       "      <td>False</td>\n",
       "      <td>[{'profile_id': 1875152, 'first_name': 'REIKO'...</td>\n",
       "      <td>...</td>\n",
       "      <td>ZAG1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLL</td>\n",
       "      <td>8</td>\n",
       "      <td>01</td>\n",
       "      <td>ZAG1-CLL-8(01)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226991</th>\n",
       "      <td>2053345</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1994</td>\n",
       "      <td>3R03AG012000-01S1</td>\n",
       "      <td>AG012000</td>\n",
       "      <td>3</td>\n",
       "      <td>R03</td>\n",
       "      <td>18036.0</td>\n",
       "      <td>False</td>\n",
       "      <td>[{'profile_id': 1950911, 'first_name': 'STEVEN...</td>\n",
       "      <td>...</td>\n",
       "      <td>ZAG1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLL</td>\n",
       "      <td>8</td>\n",
       "      <td>01</td>\n",
       "      <td>ZAG1-CLL-8(01)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226992</th>\n",
       "      <td>2053344</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1993</td>\n",
       "      <td>1R03AG012000-01</td>\n",
       "      <td>AG012000</td>\n",
       "      <td>1</td>\n",
       "      <td>R03</td>\n",
       "      <td>27000.0</td>\n",
       "      <td>False</td>\n",
       "      <td>[{'profile_id': 1950911, 'first_name': 'STEVEN...</td>\n",
       "      <td>...</td>\n",
       "      <td>ZAG1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLL</td>\n",
       "      <td>8</td>\n",
       "      <td>01</td>\n",
       "      <td>ZAG1-CLL-8(01)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226993</th>\n",
       "      <td>2053343</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1993</td>\n",
       "      <td>1R03AG011999-01</td>\n",
       "      <td>AG011999</td>\n",
       "      <td>1</td>\n",
       "      <td>R03</td>\n",
       "      <td>26127.0</td>\n",
       "      <td>False</td>\n",
       "      <td>[{'profile_id': 1862223, 'first_name': 'JANICE...</td>\n",
       "      <td>...</td>\n",
       "      <td>ZAG1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CLL</td>\n",
       "      <td>8</td>\n",
       "      <td>01</td>\n",
       "      <td>ZAG1-CLL-8(01)</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>226994 rows × 80 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         appl_id  subproject_id  fiscal_year        project_num  \\\n",
       "0       10672784            NaN         2023    1R01HD112031-01   \n",
       "1       10672642            NaN         2023    1R01HD112028-01   \n",
       "2       10670574            NaN         2023    1R01DE032868-01   \n",
       "3       10667157            NaN         2023    1R01AG082142-01   \n",
       "4       10667151            NaN         2023    1R01AG082140-01   \n",
       "...          ...            ...          ...                ...   \n",
       "226989   2053349            NaN         1994  3R03AG012003-01S1   \n",
       "226990   2053348            NaN         1993    1R03AG012003-01   \n",
       "226991   2053345            NaN         1994  3R03AG012000-01S1   \n",
       "226992   2053344            NaN         1993    1R03AG012000-01   \n",
       "226993   2053343            NaN         1993    1R03AG011999-01   \n",
       "\n",
       "       project_serial_num award_type activity_code  award_amount  is_active  \\\n",
       "0                HD112031          1           R01      586134.0       True   \n",
       "1                HD112028          1           R01      612196.0       True   \n",
       "2                DE032868          1           R01      553978.0       True   \n",
       "3                AG082142          1           R01      654613.0       True   \n",
       "4                AG082140          1           R01      622753.0       True   \n",
       "...                   ...        ...           ...           ...        ...   \n",
       "226989           AG012003          3           R03       15000.0      False   \n",
       "226990           AG012003          1           R03       21600.0      False   \n",
       "226991           AG012000          3           R03       18036.0      False   \n",
       "226992           AG012000          1           R03       27000.0      False   \n",
       "226993           AG011999          1           R03       26127.0      False   \n",
       "\n",
       "                                  principal_investigators  ...  \\\n",
       "0       [{'profile_id': 8024549, 'first_name': 'John',...  ...   \n",
       "1       [{'profile_id': 8532773, 'first_name': 'James'...  ...   \n",
       "2       [{'profile_id': 10451583, 'first_name': 'MARIA...  ...   \n",
       "3       [{'profile_id': 12467458, 'first_name': 'Wang-...  ...   \n",
       "4       [{'profile_id': 6721519, 'first_name': 'Mohani...  ...   \n",
       "...                                                   ...  ...   \n",
       "226989  [{'profile_id': 1875152, 'first_name': 'REIKO'...  ...   \n",
       "226990  [{'profile_id': 1875152, 'first_name': 'REIKO'...  ...   \n",
       "226991  [{'profile_id': 1950911, 'first_name': 'STEVEN...  ...   \n",
       "226992  [{'profile_id': 1950911, 'first_name': 'STEVEN...  ...   \n",
       "226993  [{'profile_id': 1862223, 'first_name': 'JANICE...  ...   \n",
       "\n",
       "       full_study_section.srg_code full_study_section.srg_flex  \\\n",
       "0                             ZHD1                         NaN   \n",
       "1                             ZHD1                         NaN   \n",
       "2                             ZDE1                         NaN   \n",
       "3                             ZAG1                         NaN   \n",
       "4                             ZAG1                         NaN   \n",
       "...                            ...                         ...   \n",
       "226989                        ZAG1                         NaN   \n",
       "226990                        ZAG1                         NaN   \n",
       "226991                        ZAG1                         NaN   \n",
       "226992                        ZAG1                         NaN   \n",
       "226993                        ZAG1                         NaN   \n",
       "\n",
       "       full_study_section.sra_designator_code  \\\n",
       "0                                         DSR   \n",
       "1                                         DSR   \n",
       "2                                          TO   \n",
       "3                                         ZIJ   \n",
       "4                                         ZIJ   \n",
       "...                                       ...   \n",
       "226989                                    CLL   \n",
       "226990                                    CLL   \n",
       "226991                                    CLL   \n",
       "226992                                    CLL   \n",
       "226993                                    CLL   \n",
       "\n",
       "       full_study_section.sra_flex_code full_study_section.group_code  \\\n",
       "0                                     H                            55   \n",
       "1                                     H                            55   \n",
       "2                                   NaN                            12   \n",
       "3                                     G                            J1   \n",
       "4                                     G                            J1   \n",
       "...                                 ...                           ...   \n",
       "226989                                8                            01   \n",
       "226990                                8                            01   \n",
       "226991                                8                            01   \n",
       "226992                                8                            01   \n",
       "226993                                8                            01   \n",
       "\n",
       "                        full_study_section.name full_study_section.url  \\\n",
       "0       Special Emphasis Panel[ZHD1 DSR-H (55)]                    NaN   \n",
       "1       Special Emphasis Panel[ZHD1 DSR-H (55)]                    NaN   \n",
       "2          Special Emphasis Panel[ZDE1 TO (12)]                    NaN   \n",
       "3       Special Emphasis Panel[ZAG1 ZIJ-G (J1)]                    NaN   \n",
       "4       Special Emphasis Panel[ZAG1 ZIJ-G (J1)]                    NaN   \n",
       "...                                         ...                    ...   \n",
       "226989                           ZAG1-CLL-8(01)                    NaN   \n",
       "226990                           ZAG1-CLL-8(01)                    NaN   \n",
       "226991                           ZAG1-CLL-8(01)                    NaN   \n",
       "226992                           ZAG1-CLL-8(01)                    NaN   \n",
       "226993                           ZAG1-CLL-8(01)                    NaN   \n",
       "\n",
       "       full_study_section.cmte_id full_study_section.cluster_irg_code  \\\n",
       "0                             NaN                                 NaN   \n",
       "1                             NaN                                 NaN   \n",
       "2                             NaN                                 NaN   \n",
       "3                             NaN                                 NaN   \n",
       "4                             NaN                                 NaN   \n",
       "...                           ...                                 ...   \n",
       "226989                        NaN                                 NaN   \n",
       "226990                        NaN                                 NaN   \n",
       "226991                        NaN                                 NaN   \n",
       "226992                        NaN                                 NaN   \n",
       "226993                        NaN                                 NaN   \n",
       "\n",
       "        full_study_section  \n",
       "0                      NaN  \n",
       "1                      NaN  \n",
       "2                      NaN  \n",
       "3                      NaN  \n",
       "4                      NaN  \n",
       "...                    ...  \n",
       "226989                 NaN  \n",
       "226990                 NaN  \n",
       "226991                 NaN  \n",
       "226992                 NaN  \n",
       "226993                 NaN  \n",
       "\n",
       "[226994 rows x 80 columns]"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('nih_winning_data_filtered.csv',low_memory=False)\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "5f50cf20",
   "metadata": {},
   "source": [
    "bind between winners and filtered grant calls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "f2475596",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final = df[df['full_foa'].isin(foas_final['Document_Number'])].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "5c5c51a5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7126, 4410)"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final = df_final[df_final.contact_pi_name.notnull()].reset_index(drop=True)\n",
    "foas_final['Document_Number'].unique().shape[0] , foas_final['Title'].unique().shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "cbc272eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final.to_csv('NIH_GrantCalls_Winners.csv',index=False)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "a682ad87",
   "metadata": {},
   "source": [
    "# Getting PI's publications"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "dfdc93f5",
   "metadata": {},
   "source": [
    "get PI's and prepare data for api request"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "cc676c09",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 225118/225118 [00:08<00:00, 26064.56it/s]\n"
     ]
    }
   ],
   "source": [
    "df_final.principal_investigators = df_final.principal_investigators.progress_apply(literal_eval)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "d3efaaed",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 225118/225118 [00:12<00:00, 17726.16it/s]\n"
     ]
    }
   ],
   "source": [
    "pi_data = []\n",
    "\n",
    "for index, row in tqdm(df_final.fillna('').iterrows(), total=df_final.shape[0]):\n",
    "    principal_investigators = row['principal_investigators']\n",
    "    contact_pi_name = row['contact_pi_name']\n",
    "    org_name = row['organization.org_name'].lower()\n",
    "    org_city = row['organization.org_city'].lower()\n",
    "    \n",
    "    for pi in principal_investigators:\n",
    "        pi['contact_pi_name'] = contact_pi_name\n",
    "        pi['organization.org_name'] = org_name\n",
    "        pi['organization.org_city'] = org_city\n",
    "        pi_data.append(pi)\n",
    "\n",
    "pi_df = pd.DataFrame(pi_data)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "0ac4d2a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "pi_df_only =pi_df[pi_df['is_contact_pi']==True].reset_index(drop=True)\n",
    "pi_df_only['org_clean'] = pi_df_only['organization.org_name'].str.replace('university','').str.replace('of','').str.replace('the','').str.replace(',','')\n",
    "pi_df_only = pi_df_only.drop_duplicates().reset_index(drop=True)\n",
    "pi_df_only.to_csv('pi_df_only_nih.csv',index=False)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "0704def0",
   "metadata": {},
   "source": [
    "Access SCOPUS with pybliometrics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "id": "91c0f6c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import pybliometrics.scopus\n",
    "import pybliometrics.scopus\n",
    "# pybliometrics.scopus.utils.create_config()\n",
    "from pybliometrics.scopus import config, ScopusSearch, AbstractRetrieval,AuthorSearch,AuthorRetrieval,SubjectClassifications\n",
    "from pybliometrics.scopus import AffiliationSearch,AffiliationRetrieval"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "e032f5a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "pi_df_only = pd.merge(pi_df_only,df_final[['contact_pi_name','organization.org_country']].drop_duplicates(),on='contact_pi_name',how = 'left')"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "51571567",
   "metadata": {},
   "source": [
    "## Firstly, get scopus ID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c3344f61",
   "metadata": {},
   "outputs": [],
   "source": [
    "pi_df_only=pd.read_csv('pi_df_only_nih.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f58dcf48",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final=pd.read_csv('NIH_GrantCalls_Winners.csv',low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "8d5b51b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "pi_df_only = pd.merge(pi_df_only,df_final[['contact_pi_name','organization.org_country']].drop_duplicates(),on='contact_pi_name',how = 'left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "d853e37b",
   "metadata": {},
   "outputs": [],
   "source": [
    "pi_df_only = pi_df_only.fillna('')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "54664f48",
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove names with brackets\n",
    "pi_df_only['first_name'] = pi_df_only['first_name'].apply(lambda x: x.split(' (')[0])\n",
    "pi_df_only['last_name'] = pi_df_only['last_name'].apply(lambda x: x.split(' (')[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "id": "dd356eb4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove names with brackets\n",
    "pi_df_only['first_name'] = pi_df_only['first_name'].apply(lambda x: x.split(' (')[0])\n",
    "pi_df_only['last_name'] = pi_df_only['last_name'].apply(lambda x: x.split(' (')[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "id": "aac14f6c",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 24964/24964 [31:35:24<00:00,  4.56s/it]\n"
     ]
    }
   ],
   "source": [
    "pis_scopid_dict = {}\n",
    "for index, row in tqdm(pi_df_only.iterrows(), total=pi_df_only.shape[0]): #stopped at 22901\n",
    "    scopid = -1\n",
    "    try:\n",
    "        if row['organization.org_country'] != '':\n",
    "            s = AuthorSearch(f\"AUTHLAST({row['last_name']}) and AUTHFIRST({row['first_name']}) and AFFILCOUNTRY({row['organization.org_country']})\")\n",
    "        else:\n",
    "            s = AuthorSearch(f\"AUTHLAST({row['last_name']}) and AUTHFIRST({row['first_name']})\")\n",
    "\n",
    "        tmp_df = pd.DataFrame(s.authors).fillna('')\n",
    "        if len(tmp_df)<1:\n",
    "            pis_scopid_dict[row['contact_pi_name']] = scopid\n",
    "            continue\n",
    "        # try first case:\n",
    "        if tmp_df['affiliation'].str.contains(row['organization.org_name'],case=False,regex=False).sum() > 0:\n",
    "            scopid = tmp_df[tmp_df['affiliation'].str.contains(row['organization.org_name'],case=False,regex=False)].sort_values(by='documents',ascending = False)['eid'].iloc[0].split('-')[-1]\n",
    "            pis_scopid_dict[row['contact_pi_name']] = scopid\n",
    "            pi_df_only.loc[index,'case_caught'] = 'FIRST_CASE'\n",
    "            pi_df_only.to_excel(\"pi_df_only_caught.xlsx\",index=False)\n",
    "            continue\n",
    "        # try second case:\n",
    "        if tmp_df['affiliation'].str.contains(row['org_clean'],case=False,regex=False).sum() > 0:\n",
    "            scopid = tmp_df[tmp_df['affiliation'].str.contains(row['org_clean'],case=False,regex=False)].sort_values(by='documents',ascending = False)['eid'].iloc[0].split('-')[-1]\n",
    "            pis_scopid_dict[row['contact_pi_name']] = scopid\n",
    "            pi_df_only.loc[index,'case_caught']  = 'SECOND_CASE'\n",
    "            pi_df_only.to_excel(\"pi_df_only_caught.xlsx\",index=False)\n",
    "            continue\n",
    "        # try third case:\n",
    "        if tmp_df['city'].str.contains(row['organization.org_city'],case=False,regex=False).sum() > 0:\n",
    "            scopid = tmp_df[tmp_df['city'].str.contains(row['organization.org_city'],case=False,regex=False)].sort_values(by='documents',ascending = False)['eid'].iloc[0].split('-')[-1]\n",
    "            pis_scopid_dict[row['contact_pi_name']] = scopid\n",
    "            pi_df_only.loc[index,'case_caught']  = 'THIRD_CASE'\n",
    "            pi_df_only.to_excel(\"pi_df_only_caught.xlsx\",index=False)\n",
    "            continue\n",
    "        pis_scopid_dict[row['contact_pi_name']] = scopid\n",
    "    except Exception as ae: \n",
    "        if isinstance(ae, ScopusQueryError):\n",
    "            pis_scopid_dict[row['contact_pi_name']] = scopid\n",
    "            continue\n",
    "        else:\n",
    "            pi_df_only.to_excel(\"pi_df_only_caught.xlsx\",index=False)\n",
    "            with open('pi_scopid.pickle', 'wb') as handle:\n",
    "                pickle.dump(pis_scopid_dict, handle, protocol=pickle.HIGHEST_PROTOCOL)\n",
    "            raise\n",
    "        \n",
    "with open('pi_scopid.pickle', 'wb') as handle:\n",
    "    pickle.dump(pis_scopid_dict, handle, protocol=pickle.HIGHEST_PROTOCOL)\n",
    "pi_df_only.to_excel(\"pi_df_only_caught.xlsx\",index=False)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "b86bbd17",
   "metadata": {},
   "source": [
    "# Load the mined PI Scopus ID's"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "id": "7595d96f",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('pi_scopid.pickle', \"rb\") as input_file:\n",
    "    pis_scopid_dict = pickle.load(input_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "id": "1189bb75",
   "metadata": {},
   "outputs": [],
   "source": [
    "pi_df_only = pd.read_excel(\"pi_df_only_caught.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "id": "f27e978c",
   "metadata": {},
   "outputs": [],
   "source": [
    "pi_df_only['ScopusID'] = pi_df_only['contact_pi_name'].map(pis_scopid_dict)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "9bd5d6a7",
   "metadata": {},
   "source": [
    "## Statistics on the retrival process:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "id": "92d968e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "found_pidf = pi_df_only[~((pi_df_only['ScopusID'] == -1) | (pi_df_only['case_caught'].isna()))].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "id": "c281f79c",
   "metadata": {},
   "outputs": [],
   "source": [
    "total_pis = len(pi_df_only)\n",
    "missing_ids = len(pi_df_only[(pi_df_only['ScopusID'] == -1) | (pi_df_only['case_caught'].isna())])\n",
    "\n",
    "pis_found = found_pidf['ScopusID'].shape[0]\n",
    "\n",
    "scop_ids_found = found_pidf['ScopusID'].unique().shape[0]\n",
    "\n",
    "duplicated_ids = (found_pidf['ScopusID'].value_counts() > 1).sum()\n",
    "duplicated_ids_vals = (found_pidf['ScopusID'].value_counts()[found_pidf['ScopusID'].value_counts() > 1]).index\n",
    "\n",
    "\n",
    "duplicated_names = (found_pidf['contact_pi_name'].value_counts() > 1).sum()\n",
    "duplicated_names_vals = (found_pidf['contact_pi_name'].value_counts()[found_pidf['contact_pi_name'].value_counts() > 1]).index\n",
    "\n",
    "\n",
    "profile_ids = (found_pidf['profile_id'].value_counts() > 1).sum()\n",
    "profile_ids_vals = found_pidf['profile_id'].value_counts()[found_pidf['profile_id'].value_counts() > 1].index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "id": "d4bb295f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "PI Summary:\n",
      "- 908 Scopus IDs were not retrieved out of 24,964 PIs.\n",
      "- 21,658 SCOPUS IDs were retrieved for 24,056 inital PIs, indicating duplicated names.\n",
      "- 2,131 IDs appeared more than once, while 1,967 PIs profile ids were duplicated\n",
      "- and there were 1524 PI names that repeated more than one\n"
     ]
    }
   ],
   "source": [
    "print(f\"PI Summary:\\n\\\n",
    "- {missing_ids} Scopus IDs were not retrieved out of {total_pis:,} PIs.\\n\\\n",
    "- {scop_ids_found:,} SCOPUS IDs were retrieved for {pis_found:,} inital PIs, indicating duplicated names.\\n\\\n",
    "- {duplicated_ids:,} IDs appeared more than once, while {profile_ids:,} PIs profile ids were duplicated\\n\\\n",
    "- and there were {duplicated_names} PI names that repeated more than one\")\n",
    "# - {duplicated_ids:,} IDs appeared more than once, while {duplicated_names:,} pi full names were duplicated and .\")\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "0c71ed5e",
   "metadata": {},
   "source": [
    "**We drop duplicated PIs with the same profile id, full name and scopus id and with the same full name and scopus id but diffrent profile id** "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "id": "23ff0083",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "22362"
      ]
     },
     "execution_count": 180,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pidf_stg1 = found_pidf.drop_duplicates(subset = ['profile_id','contact_pi_name','ScopusID'])\\\n",
    "        .drop_duplicates(subset = ['contact_pi_name','ScopusID'])\\\n",
    "        .reset_index(drop=True)\n",
    "len(pidf_stg1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "id": "4070ab90",
   "metadata": {},
   "outputs": [],
   "source": [
    "total_pis = len(pi_df_only)\n",
    "missing_ids = len(pi_df_only[(pi_df_only['ScopusID'] == -1) | (pi_df_only['case_caught'].isna())])\n",
    "\n",
    "pis_found = pidf_stg1['ScopusID'].shape[0]\n",
    "\n",
    "scop_ids_found = pidf_stg1['ScopusID'].unique().shape[0]\n",
    "\n",
    "duplicated_ids = (pidf_stg1['ScopusID'].value_counts() > 1).sum()\n",
    "duplicated_ids_vals = (pidf_stg1['ScopusID'].value_counts()[pidf_stg1['ScopusID'].value_counts() > 1]).index\n",
    "\n",
    "\n",
    "duplicated_names = (pidf_stg1['contact_pi_name'].value_counts() > 1).sum()\n",
    "duplicated_names_vals = (pidf_stg1['contact_pi_name'].value_counts()[pidf_stg1['contact_pi_name'].value_counts() > 1]).index\n",
    "\n",
    "\n",
    "profile_ids = (pidf_stg1['profile_id'].value_counts() > 1).sum()\n",
    "profile_ids_vals = pidf_stg1['profile_id'].value_counts()[pidf_stg1['profile_id'].value_counts() > 1].index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "id": "518d349c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "THIRD_CASE     49%\n",
       "FIRST_CASE     48%\n",
       "SECOND_CASE     3%\n",
       "Name: case_caught, dtype: object"
      ]
     },
     "execution_count": 184,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "((pidf_stg1['case_caught'].value_counts(normalize=True) * 100).round()).astype(int).astype(str)+\"%\""
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "ccccfcb4",
   "metadata": {},
   "source": [
    "## Merge Scopus Ids with winning appilcations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "038a96b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_final = pd.read_csv('NIH_GrantCalls_Winners.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 216,
   "id": "89a971fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_wins_final= pd.merge(df_final,pidf_stg1[['contact_pi_name','ScopusID']],on='contact_pi_name')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 219,
   "id": "9efce0b3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(128586, 128586)"
      ]
     },
     "execution_count": 219,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_wins_final.appl_id.unique().shape[0] , df_wins_final.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 222,
   "id": "0819e21b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_wins_final.to_csv('df_nih_wins_22362.csv',index=False)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "1899ae12",
   "metadata": {},
   "source": [
    "# Get publications based on scopus ID of extracted PIs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "98164840",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\users\\gzeevi\\pycharmprojects\\pythonproject\\venv\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3444: DtypeWarning: Columns (5,26,29,33,50,70,73) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
     ]
    }
   ],
   "source": [
    "df_nih = pd.read_csv('df_nih_wins_22362.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c474160c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# import pybliometrics.scopus\n",
    "import pybliometrics.scopus\n",
    "# pybliometrics.scopus.utils.create_config()\n",
    "from pybliometrics.scopus import config, ScopusSearch, AbstractRetrieval,AuthorSearch,AuthorRetrieval,SubjectClassifications\n",
    "from pybliometrics.scopus import AffiliationSearch,AffiliationRetrieval\n",
    "from pybliometrics.scopus.exception import Scopus429Error"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "fb90a9e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "pubs_df = pd.DataFrame(columns = ['eid', 'doi', 'pii', 'pubmed_id', 'title', 'subtype', 'subtypeDescription',\n",
    "                                  'creator', 'afid', 'affilname', 'affiliation_city', 'affiliation_country', 'author_count',\n",
    "                                  'author_names', 'author_ids', 'author_afids', 'coverDate', 'coverDisplayDate',\n",
    "                                  'publicationName', 'issn', 'source_id', 'eIssn', 'aggregationType', 'volume',\n",
    "                                  'issueIdentifier', 'article_number', 'pageRange', 'description',\n",
    "                                  'authkeywords', 'citedby_count', 'openaccess', 'fund_acr', 'fund_no', 'fund_sponsor','scopus_id','Name'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "4e3ac89f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_nih_iter = df_nih[['contact_pi_name','ScopusID']].drop_duplicates().reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2060e0be",
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    for index in tqdm(range(df_nih_iter.shape[0])):\n",
    "        row = df_nih_iter.iloc[index]\n",
    "        au = AuthorRetrieval(row['ScopusID'], refresh=True)\n",
    "        tmp_df = pd.DataFrame(au.get_documents())\n",
    "        tmp_df['scopus_id'] = row['ScopusID']\n",
    "        tmp_df['Name'] = row['contact_pi_name']\n",
    "        pubs_df = pd.concat([pubs_df, tmp_df])\n",
    "\n",
    "    pubs_df = pubs_df.reset_index(drop=True)\n",
    "    \n",
    "except Exception as e:\n",
    "    pubs_df.to_csv('nih_pubs.csv', index=False)\n",
    "    raise e\n",
    "\n",
    "# Save the final DataFrame to CSV\n",
    "pubs_df.to_csv('nih_pubs.csv', index=False)"
   ]
  }
 ],
 "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}