PubMed_and_NIH_data_downloader_master.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "trying-health",
   "metadata": {},
   "source": [
    "# Initial Steps: load packages, files and functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "difficult-thing",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import urllib\n",
    "import time\n",
    "import requests\n",
    "import os\n",
    "from datetime import datetime\n",
    "import math\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "sustained-distinction",
   "metadata": {},
   "outputs": [],
   "source": [
    "cwd=os.getcwd()\n",
    "cwd_Raw_Data_outputs=os.path.join(cwd,'RawData')#heres where we store freezes of the raw data\n",
    "cwd_Figures=os.path.join(cwd,'Figures')#figures and code for generating them can go here\n",
    "cwd_Output=os.path.join(cwd,'Output Dataframes')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "chief-designation",
   "metadata": {},
   "outputs": [],
   "source": [
    "dfSynonym=pd.read_excel(os.path.join(cwd,'Synonyms_filtered_v3.xlsx'),engine=\"openpyxl\")\n",
    "dfSynonym=dfSynonym.sort_values(by=\"Symbol\")\n",
    "dfSynonym=dfSynonym.reset_index()\n",
    "dfSynonym=dfSynonym.drop(columns=\"index\")\n",
    "dfSynonym.index=dfSynonym[\"Symbol\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "closed-builder",
   "metadata": {},
   "outputs": [],
   "source": [
    "gene_terms=str(\"(\\\"+_gene_+\\\"[ti])\")\n",
    "CT1_queryPM=str(\"\")\n",
    "CT1_queryNIH=str(\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "thousand-medline",
   "metadata": {},
   "outputs": [],
   "source": [
    "def GetAwardAmount(input_String, Lists):\n",
    "    #function takes three arguments; the Reporter output, the destination where we store results, and an additional list for storing a freeze of the data\n",
    "    GetResults = input_String.find(\"\\\"results\\\"\") #find the part of the output detailing grant award amount, found after the \"results\" block of the ouput\n",
    "    \n",
    "    ResultsList = input_String[GetResults:].replace(\"},\", \"\")# each grant's information is separated by curly brackets; splitting along curly brackets divides info from each grant\n",
    "    ResultsList = (ResultsList.split(\"{\\\"\"))[1:]    #saving the individual grant amount as a an element in a list of grants\n",
    "    for iGrant in ResultsList:# for each grant returned by the query\n",
    "        Award_Start = iGrant.find(\"\\\"award_amount\\\":\")#find the part detailing award amount\n",
    "        Award_End = iGrant.find(\"\\\"project_start_date\\\":\")#find the part that comes after the award amount\n",
    "        DirectCost = iGrant.find(\"\\\"direct_cost_amt\\\":\")\n",
    "        direct_End = iGrant.find(\"\\\"indirect_cost_amt\\\":\")\n",
    "        Award_string = iGrant[Award_Start:Award_End].replace(\",\", \"\").split(\":\")[1]# the amount of money for grant will be between the part addressed as award amount and the direct cost amount\n",
    "        try:\n",
    "            directCost = iGrant[DirectCost:direct_End].replace(\",\", \"\").split(':', 1)[1]\n",
    "            indirectCost = iGrant[direct_End:].replace(\",\", \"\").split(':', 1)[1]\n",
    "        except:\n",
    "            print(\"COST ERROR\")\n",
    "            indirectCost=0\n",
    "            print(iGrant)\n",
    "            print(input_String)\n",
    "        if not Award_string == \"null\": # for some reason, some grants do not have an award amount stored in NIH Reporter\n",
    "            Lists[0] = Lists[0] + int(Award_string)\n",
    "            if not directCost == \"null\":\n",
    "                Lists[1]=Lists[1]+int(directCost)\n",
    "            if not \"null\" in indirectCost:\n",
    "                indirectCost=indirectCost.replace(\"}]}\",\"\")\n",
    "                Lists[2]=Lists[2]+int(indirectCost)\n",
    "    return Lists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "powered-weight",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "This is the main function for pulling data from both PubMed and NIH RePORTER\n",
    "The function takes 7 arguments; \n",
    "the dataframe where data is stored at the end, the url for accessing PubMed, the NIH search parameters in JSON format, and the destination for all Raw data\n",
    "this function is used for any searches that include grants and publications searches for both title and title/abstract, and all relevant synonyms\n",
    "returns a dataframe with all the relevant data obtained during the search\n",
    "The structure of other blocks mimic this almost exactly save some additional tweaks where needed\n",
    "\"\"\"\n",
    "def AccessPubMed_and_Reporter_Master_function(OutputDF,PubMedUrl,NIH_param,PM_title_raw,PM_tiab_raw,NIH_title_raw,NIH_tiab_raw):\n",
    "    #Step 1:preliminary steps\n",
    "    \n",
    "    gene_terms=str(\"(\\\"+_gene_+\\\"[ti])\")\n",
    "    CT1_queryPM=str(\"\")\n",
    "    CT1_queryNIH=str(\"\")\n",
    "    frontParticiple=str(NIH_param[\"criteria\"][\"advanced_text_search\"]['search_text'])\n",
    "    for i in dfSynonym.index:\n",
    "        print(i)\n",
    "    #Step 2: construct gene search with synonyms\n",
    "        Excluded=False\n",
    "        term = gene_terms.replace(\"_gene_\", str(i))\n",
    "        if i not in str(dfSynonym.loc[i][\"Removed Synonyms\"]):\n",
    "            CT1_queryPM = str(CT1_queryPM) + \"(\" + str(term)\n",
    "            CT1_queryNIH = str(CT1_queryNIH) + \"(\" + \"\\\"\" + str(i) + \"\\\" \"\n",
    "        else: #if the main gene name is something from exclude list, we take its first synonym\n",
    "            Excluded=True\n",
    "        Synonym_string=\"\"\n",
    "        if not isinstance(dfSynonym.loc[i][\"Synonyms\"],float):  # first check if a list of synonyms exists. if there are no synonyms, value is nan, Nan values are expressed as a float\n",
    "            CT1_queryPM = str(CT1_queryPM) + str(\"+OR+\")\n",
    "            CT1_queryNIH = str(CT1_queryNIH) + str(\" OR \")\n",
    "            if \"|\" in str(dfSynonym.loc[i][\"Synonyms\"]):  # if multiple terms are included, they will be separated by \"|\" character, so if character is in list of synonyms, means there are multiple synonyms\n",
    "                Synonyms_list = str(dfSynonym.loc[i][\"Synonyms\"]).split(\"|\")  # split terms into list\n",
    "                for x in Synonyms_list:  # add each item from list as an additional term to query, formatting the term appropriately\n",
    "                    if (x not in str(dfSynonym.loc[i][\"Removed Synonyms\"])) or (x in str(dfSynonym.loc[i][\"Added Synonyms\"])):\n",
    "                        igene_term = gene_terms.replace(\"_gene_\", str(x))\n",
    "                        Synonym_string=Synonym_string+str(x)+str(\"|\")\n",
    "                        CT1_queryPM = CT1_queryPM + str(igene_term) + str(\"+OR+\")\n",
    "                        CT1_queryNIH = str(CT1_queryNIH) + \"\\\"\" + str(x) + \"\\\"\" + str(\" OR \")\n",
    "                    #else:\n",
    "                        #print(\"excluded: \",x)\n",
    "                CT1_queryPM = str(CT1_queryPM[:(len(CT1_queryPM)) - 4]) + str(\")\")\n",
    "                CT1_queryNIH = CT1_queryNIH[:(len(CT1_queryNIH)) - 4] + str(\")\")\n",
    "            else:  # if \"|\" character is not present but list of synonyms is not null, then there is just one synonymous term\n",
    "                if str(dfSynonym.loc[i][\"Synonyms\"]) not in str(dfSynonym.loc[i][\"Removed Synonyms\"]):# add single synonym in the same format as above\n",
    "                    Synonym_string=Synonym_string+ str(dfSynonym.loc[i][\"Synonyms\"])\n",
    "                    igene_term = gene_terms.replace(\"_gene_\", str(dfSynonym.loc[i][\"Synonyms\"]))\n",
    "                    CT1_queryPM = CT1_queryPM + str(igene_term) + str(\"+OR+\")\n",
    "                    CT1_queryPM = str(CT1_queryPM[:(len(CT1_queryPM)) - 4]) + str(\")\")\n",
    "                    CT1_queryNIH = CT1_queryNIH + str(\"\\\"\" + dfSynonym.loc[i][\"Synonyms\"] + \"\\\"\") + str(\" OR \")\n",
    "                    CT1_queryNIH = str(CT1_queryNIH[:(len(CT1_queryNIH)) - 4]) + str(\")\")\n",
    "                #else:\n",
    "                    #print(\"excluded: \",str(dfSynonym.loc[i][\"Removed Synonyms\"]))\n",
    "        else:# same as above, but for genes with no synonyms, just close the parentheses\n",
    "            CT1_queryPM = str(CT1_queryPM) + str(\")\")\n",
    "            CT1_queryNIH = str(CT1_queryNIH) + str(\")\")\n",
    "        if Excluded==True:\n",
    "            CT1_queryPM =\"(\"+ str(CT1_queryPM[4:])\n",
    "            CT1_queryNIH = \"(\" + str(CT1_queryNIH[4:])\n",
    "            Excluded==False\n",
    "        #Step 3: Perform PubMed publication searches for title and title/abstract\n",
    "        url_Pubmed_title = PubMedUrl.replace(\"(\\\"+_gene_+\\\"[ti])\", CT1_queryPM)#once gene names are retireved, we put them into the url\n",
    "        url_Pubmed_title = urllib.parse.quote(url_Pubmed_title, safe=\"/+?:=&\")\n",
    "        PubTitleFreeze = os.path.join(cwd_Raw_Data_outputs, PM_title_raw + str(i) + '_PM_title.txt')\n",
    "        if os.path.exists(PubTitleFreeze):\n",
    "            file1 = open(PubTitleFreeze,'r',encoding=\"utf-8\")\n",
    "            data_title = file1.readlines()\n",
    "            for line in data_title:\n",
    "                if \"<eSearchResult><Count>\" in str(line):  # find the line which tells the counts of all terms combined in article titles\n",
    "                    get_count = str(line).split(\"Count>\")\n",
    "                    title_count = int(get_count[1].replace(\"</\", \"\"))\n",
    "            file1.close()\n",
    "        else:\n",
    "            data_title = urllib.request.urlopen(url_Pubmed_title)  # open url and get a copy of the data from the page\n",
    "            with open(PubTitleFreeze, 'w',encoding=\"utf-8\") as f:\n",
    "                for line in data_title:\n",
    "                    f.write(str(line))  # write a freeze of the data from the page\n",
    "                    f.write('\\n')\n",
    "                    if \"<eSearchResult><Count>\" in str(line):  # find the line which tells the counts of all terms combined in article titles\n",
    "                        get_count = str(line).split(\"Count>\")\n",
    "                        title_count = int(get_count[1].replace(\"</\", \"\"))\n",
    "                f.write('\\n')\n",
    "                f.write(\"url used for search:\")\n",
    "                f.write(url_Pubmed_title)  # store the freeze and search URL as a txt file\n",
    "                f.write('\\n')\n",
    "                f.write(\"date accessed:\")\n",
    "                GetDate = str(datetime.now())\n",
    "                f.write(GetDate)\n",
    "                f.close()\n",
    "                time.sleep(1)\n",
    "        url_tiab = url_Pubmed_title.replace(\"%5Bti%5D\", \"%5Btiab%5D\")# same idea as above, only this time rpelacing the [title] keyword with [tiab], querying the mention of a keyword in both title OR abstract\n",
    "        PubTiabFreeze = os.path.join(cwd_Raw_Data_outputs, PM_tiab_raw + str(i) + '_PM_tiab.txt')\n",
    "        if os.path.exists(PubTiabFreeze):\n",
    "            file2 = open(PubTiabFreeze,'r',encoding=\"utf-8\")\n",
    "            data_tiab = file2.readlines()\n",
    "            for line in data_tiab:\n",
    "                if \"<eSearchResult><Count>\" in str(line):  # find the line which tells the counts of all terms combined in article titles\n",
    "                    get_count = str(line).split(\"Count>\")\n",
    "                    tiab_count = int(get_count[1].replace(\"</\", \"\"))\n",
    "            file2.close()\n",
    "        else:   \n",
    "            data_tiab = urllib.request.urlopen(url_tiab)  # open url and get a copy of the data from the page\n",
    "            with open(PubTiabFreeze, 'w',encoding=\"utf-8\") as f:\n",
    "                for line in data_tiab:\n",
    "                    f.write(str(line))  # write a freeze of the data from the page\n",
    "                    f.write('\\n')\n",
    "                    if \"<eSearchResult><Count>\" in str(line):  # find the line which tells the counts of all terms combined in article titles                    \n",
    "                        get_count = str(line).split(\"Count>\")\n",
    "                        tiab_count = int(get_count[1].replace(\"</\", \"\"))\n",
    "                f.write('\\n')\n",
    "                f.write(\"url used for search:\")\n",
    "                f.write(url_tiab)  # store the freeze and search URL as a txt file\n",
    "                f.write('\\n')\n",
    "                f.write(\"date accessed:\")\n",
    "                GetDate = str(datetime.now())\n",
    "                f.write(GetDate)\n",
    "                f.close()\n",
    "                time.sleep(1)\n",
    "        if (CT1_queryNIH[-3:]==\"OR \"):\n",
    "            CT1_queryNIH=CT1_queryNIH[:-3]+(\")\")\n",
    "        #Step 4: Perform NIH title and title/abstract searches    \n",
    "        offset = 0\n",
    "        paramsPlus = NIH_param #define search parameters JSON for RePORTER searches\n",
    "        paramsPlus[\"criteria\"][\"advanced_text_search\"][\"search_field\"]=\"projecttitle,abstracttext\"#first we do title/abstract search\n",
    "        paramsPlus[\"criteria\"][\"advanced_text_search\"]['search_text']=frontParticiple+str(CT1_queryNIH)#add the list of genes to the JSON template\n",
    "        paramsPlus[\"offset\"]=offset\n",
    "        print(paramsPlus)\n",
    "        NIH_Raw_tiab = os.path.join(cwd_Raw_Data_outputs, NIH_tiab_raw + str(i) + '_NIH_tiab.txt')\n",
    "        NIH_Raw_title = os.path.join(cwd_Raw_Data_outputs, NIH_title_raw + str(i) + '_NIH_title.txt')\n",
    "        fileFound=False\n",
    "        if os.path.exists(NIH_Raw_tiab):\n",
    "            file3 = open(NIH_Raw_tiab, 'r',encoding=\"utf-8\")\n",
    "            lines3 = file3.readlines()\n",
    "            JSON_output=lines3[0]\n",
    "            GetSearchNumber=0\n",
    "            fileFound=True\n",
    "        else:\n",
    "            response = requests.post(\"https://api.reporter.nih.gov/v2/projects/Search\", json=paramsPlus)\n",
    "            JSON_output = response.text  # load JSON, get results in sets of 500\n",
    "            file3 = open(NIH_Raw_tiab, 'w',encoding=\"utf-8\")\n",
    "            file3.writelines(JSON_output)\n",
    "        if i ==\"EGFR\":\n",
    "            print(JSON_output)\n",
    "            \n",
    "        Amount_Start = JSON_output.find(\"\\\"total\\\":\") #find the total amount of grants\n",
    "        Amount_End = JSON_output.find(\"\\\"offset\\\":\")\n",
    "        Amount = JSON_output[Amount_Start:Amount_End].replace(\",\", \"\").split(\":\")\n",
    "        Grants = int(Amount[1])\n",
    "        Award = 0\n",
    "        Costs_list = [0,0,0]\n",
    "        if Grants >= 1:  # if there are grants for the gene,\n",
    "            if Grants < 500:  # if there are less than 500 grants, we only need 1 search\n",
    "                try:\n",
    "                    Costs_list = GetAwardAmount(JSON_output, Costs_list)\n",
    "                except:\n",
    "                    print(i)\n",
    "                    print(\"less than 500 error\")\n",
    "                    break\n",
    "            else:  # if there are more than 500 grants, we need to perform the search 500 grants at a time\n",
    "                Costs_list = GetAwardAmount(JSON_output, Costs_list)\n",
    "                GrantsLeft = Grants\n",
    "                while GrantsLeft > 0:\n",
    "                    if GrantsLeft > 500:\n",
    "                        GrantsLeft -= 500\n",
    "                        if fileFound==True:\n",
    "                            try:\n",
    "                                GetSearchNumber+=1\n",
    "                                JSON_output_offset=lines3[GetSearchNumber]\n",
    "                            except:\n",
    "                                print(i)\n",
    "                                print(\"excpetion occured, check NIH outputs for full reading\")\n",
    "                                GetSearchNumber+=1\n",
    "                        else:\n",
    "                            time.sleep(3)\n",
    "                            offset += 500\n",
    "                            paramsPlus2 = paramsPlus\n",
    "                            paramsPlus2[\"offset\"] = offset\n",
    "                            response_plus = requests.post(\"https://api.reporter.nih.gov/v2/projects/Search\", json=paramsPlus2)\n",
    "                            JSON_output_offset = response_plus.text\n",
    "                            file3.write(\"\\n\")\n",
    "                            file3.writelines(JSON_output_offset)\n",
    "                        Costs_list = GetAwardAmount(JSON_output_offset, Costs_list)\n",
    "                    else:\n",
    "                        GrantsLeft = 0\n",
    "        if fileFound==False:\n",
    "            file3.write(\"\\n\")\n",
    "            file3.write(\"Date accessed:\")\n",
    "            GetDate = str(datetime.now())\n",
    "            file3.write(GetDate)\n",
    "            file3.close()\n",
    "        Award =int(Costs_list[0])\n",
    "        time.sleep(1)\n",
    "        offset = 0\n",
    "        paramsPlus_Title = paramsPlus #same format as title/abstract JSON, only with \"abstracttext\" removed from search_field\n",
    "        paramsPlus_Title[\"criteria\"][\"advanced_text_search\"][\"search_field\"]=\"projecttitle\"\n",
    "        paramsPlus_Title[\"offset\"]=offset\n",
    "        fileFound_Title=False\n",
    "        if os.path.exists(NIH_Raw_title):\n",
    "            file4 = open(NIH_Raw_title, 'r',encoding=\"utf-8\")\n",
    "            lines4 = file4.readlines()\n",
    "            JSON_output_Title=lines4[0]\n",
    "            GetSearchNumber_title=0\n",
    "            fileFound_Title=True\n",
    "        else:\n",
    "            response_title = requests.post(\"https://api.reporter.nih.gov/v2/projects/Search\", json=paramsPlus_Title)\n",
    "            JSON_output_Title = response_title.text  # load JSON, get results in sets of 500\n",
    "            file4 = open(NIH_Raw_title, 'w',encoding=\"utf-8\")\n",
    "            file4.writelines(JSON_output_Title)\n",
    "        Amount_Start_Title = JSON_output_Title.find(\"\\\"total\\\":\")\n",
    "        Amount_End_Title = JSON_output_Title.find(\"\\\"offset\\\":\")\n",
    "        Amount_Title = JSON_output_Title[Amount_Start_Title:Amount_End_Title].replace(\",\", \"\").split(\":\")\n",
    "        Grants_Title = int(Amount_Title[1])\n",
    "        offset = 0\n",
    "        Award_Title = 0\n",
    "        Costs_list_Title = [0,0,0]\n",
    "        if Grants_Title >= 1:\n",
    "            if Grants_Title < 500:\n",
    "                Costs_list_Title = GetAwardAmount(JSON_output_Title, Costs_list_Title)\n",
    "            else:\n",
    "                Costs_list_Title = GetAwardAmount(JSON_output_Title, Costs_list_Title)\n",
    "                GrantsLeft_Title = Grants_Title\n",
    "                while GrantsLeft_Title > 0:\n",
    "                    if GrantsLeft_Title > 500:\n",
    "                        GrantsLeft_Title -= 500\n",
    "                        if fileFound_Title==True:\n",
    "\n",
    "                            if i==\"TP53\":\n",
    "                                try:                            \n",
    "                                    GetSearchNumber_title+=1\n",
    "                                    JSON_output_offset_Title=lines3[GetSearchNumber_title]\n",
    "                                except:\n",
    "                                    print(\"TP53ERROR\")\n",
    "                                    GetSearchNumber_title+=1\n",
    "                                    break\n",
    "                            else:\n",
    "                                GetSearchNumber_title+=1\n",
    "                                JSON_output_offset_Title=lines3[GetSearchNumber_title]\n",
    "                        else:\n",
    "                            time.sleep(3)\n",
    "                            offset += 500\n",
    "                            paramsPlus2_Title = paramsPlus_Title\n",
    "                            paramsPlus2_Title[\"offset\"] = offset\n",
    "                            response_plus_Title = requests.post(\"https://api.reporter.nih.gov/v2/projects/Search\", json=paramsPlus2_Title)\n",
    "                            JSON_output_offset_Title = response_plus_Title.text\n",
    "                            file4.write(\"\\n\")\n",
    "                            file4.writelines(JSON_output_offset_Title)\n",
    "                        Costs_list_Title= GetAwardAmount(JSON_output_offset_Title, Costs_list_Title)\n",
    "                    else:\n",
    "                        GrantsLeft_Title = 0\n",
    "        if fileFound_Title==False:\n",
    "            file4.write(\"\\n\")\n",
    "            file4.write(\"Date accessed:\")\n",
    "            GetDate = str(datetime.now())\n",
    "            file4.write(GetDate)\n",
    "            file4.close()\n",
    "        Award_Title =int(Costs_list_Title[0])\n",
    "        AppendSeries={\"Gene name\": str(i), \"Pubs[title]\": title_count, \"Pubs[title/abstract]\": tiab_count, \"Number of Grants[title/abstract]\": int(Amount[1]),\"Award Amount[title/abstract]\": Award, \"Number of Grants[title]\":int(Amount_Title[1]), \"Award Amount[title]\":Award_Title, \"Synonyms\":Synonym_string}\n",
    "        OutputDF = OutputDF.append(AppendSeries, ignore_index=True)\n",
    "        Award = 0\n",
    "        Award_Title = 0\n",
    "        #time.sleep(1)\n",
    "        CT1_queryPM = str(\"\")\n",
    "        CT1_queryNIH = str(\"\")\n",
    "    return OutputDF"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "lucky-tyler",
   "metadata": {},
   "source": [
    "# Search 1: Standard Search with cancer and genes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "intimate-expansion",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "url_Pubmed_S1='https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=(cancer[ti]+AND+(\\\"+_gene_+\\\"[ti]))&retmax=20'\n",
    "Out_DF_S1=pd.DataFrame(columns=[\"Gene name\",\"Pubs[title]\",\"Pubs[title/abstract]\", \"Number of Grants[title/abstract]\", \"Award Amount[title/abstract]\", \"Number of Grants[title]\", \"Award Amount[title]\", \"Synonyms\"])\n",
    "PM_Tiab_raw='Default Search Parameter Raw Data\\\\Standard Search with cancer and genes\\\\NCBI PubMed\\\\Title and abstract data\\\\'\n",
    "PM_Ti_raw='Default Search Parameter Raw Data\\\\Standard Search with cancer and genes\\\\NCBI PubMed\\\\Title only data\\\\'\n",
    "NIH_Ti_raw='Default Search Parameter Raw Data\\\\Standard Search with cancer and genes\\\\NIH RePORTER\\\\Title only data\\\\'\n",
    "NIH_Tiab_raw='Default Search Parameter Raw Data\\\\Standard Search with cancer and genes\\\\NIH RePORTER\\\\Title and abstract data\\\\'\n",
    "paramsDefault = {\n",
    "        \"criteria\": {\n",
    "    \"advanced_text_search\": {\"operator\": \"advanced\", \"search_field\": \"projecttitle,abstracttext\",\"search_text\": str(\"cancer AND \")}},\n",
    "        \"include_fields\": [\"ApplId\", \"ProjectTitle\", \"AwardAmount\", \"DirectCostAmt\", \"IndirectCostAmt\",\"ProjectStartDate\", \"ProjectEndDate\"],\"offset\": 0, \"limit\": 500, }\n",
    "Out_DF_S1CD=AccessPubMed_and_Reporter_Master_function(Out_DF_S1,url_Pubmed_S1,paramsDefault,PM_Ti_raw,PM_Tiab_raw,NIH_Ti_raw,NIH_Tiab_raw)\n",
    "print(Out_DF_S1CD)\n",
    "df=pd.read_csv(os.path.join(cwd_Raw_Data_outputs,\"NIH+PM_TP53.csv\"), sep=\",\", header=0)#path for manually downloaded TP53 title/abstract award amount\n",
    "df=df.iloc[:, :-1]\n",
    "df[\"Total\"]=df[\"Total Cost\"]+df[\"Total Cost (Sub Projects)\"]\n",
    "Total_TP53=0\n",
    "for i in df[\"Total\"]:\n",
    "    if (i!=\"\" and i!=\"  \"):#NIH stores empty values as spaces for some reason \n",
    "        Total_TP53=int(i)+Total_TP53\n",
    "Out_DF_S1CD.index=Out_DF_S1CD[\"Gene name\"]\n",
    "Out_DF_S1CD=Out_DF_S1CD.drop(columns=\"Gene name\")\n",
    "Out_DF_S1CD.loc[\"TP53\"][\"Award Amount[title/abstract]\"]=int(Total_TP53)\n",
    "Out_DF_S1CD.to_excel(os.path.join(cwd_Output,\"NIH+PM_Data.xlsx\"), engine=\"openpyxl\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "amazing-browse",
   "metadata": {},
   "source": [
    "# Search 2: AACR publications and NCI grants"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "double-married",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "url_Pubmed_S2AB='https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=((\\\"blood cancer discovery\\\"[ta]+OR+\\\"cancer discovery\\\"[ta]+OR+\\\"Cancer Epidemiology Biomarkers and Prevention\\\"[ta]+OR+\\\"cancer immunology research\\\"[ta]+OR+\\\"cancer prevention research\\\"[ta]+OR+\\\"cancer research\\\"[ta]+OR+\\\"clinical cancer research\\\"[ta]+OR+\\\"molecular cancer research\\\"[ta]+OR+\\\"molecular cancer therapeutics\\\"[ta]+OR+\\\"cancer research communications\\\"[ta])+AND+(\\\"+_gene_+\\\"[ti]))&retmax=20'\n",
    "Out_DF_S2AB=pd.DataFrame(columns=[\"Gene name\",\"Pubs[title]\",\"Pubs[title/abstract]\", \"Number of Grants[title/abstract]\", \"Award Amount[title/abstract]\", \"Number of Grants[title]\", \"Award Amount[title]\", \"Synonyms\"])\n",
    "params = {\n",
    "        \"criteria\": {\"agencies\": [\"NCI\"],\n",
    "    \"advanced_text_search\": {\"operator\": \"advanced\", \"search_field\": \"projecttitle,abstracttext\",\"search_text\": str(\"\")}},\n",
    "        \"include_fields\": [\"ApplId\", \"ProjectTitle\", \"AwardAmount\", \"DirectCostAmt\", \"IndirectCostAmt\",\"ProjectStartDate\", \"ProjectEndDate\"],\"offset\": 0, \"limit\": 500, }\n",
    "PM_Ti_Raw='Controls Raw Data\\\\AACR publications and NCI grants\\\\NCBI PubMed\\\\Title only data\\\\'\n",
    "PM_Tiab_Raw='Controls Raw Data\\\\AACR publications and NCI grants\\\\NCBI PubMed\\\\Title and abstract data\\\\'\n",
    "NIH_Ti_Raw='Controls Raw Data\\\\AACR publications and NCI grants\\\\NIH RePORTER\\\\Title only data\\\\'\n",
    "NIH_Tiab_Raw='Controls Raw Data\\\\AACR publications and NCI grants\\\\NIH RePORTER\\\\Title and abstract data\\\\'\n",
    "Out_DF_S2AB=AccessPubMed_and_Reporter_Master_function(Out_DF_S2AB,url_Pubmed_S2AB,params,PM_Ti_Raw,PM_Tiab_Raw,NIH_Ti_Raw,NIH_Tiab_Raw)\n",
    "df=pd.read_csv(os.path.join(cwd_Raw_Data_outputs,\"NIH+PM_TP53_AACR+NCI.csv\"), sep=\",\", header=0)#path for manually downloaded TP53 title/abstract award amount\n",
    "df=df.iloc[:, :-1]\n",
    "df[\"Total\"]=df[\"Total Cost\"]+df[\"Total Cost (Sub Projects)\"]\n",
    "Total_TP53=0\n",
    "for i in df[\"Total\"]:\n",
    "    if (i!=\"\" and i!=\"  \"):#NIH stores empty values as spaces for some reason \n",
    "        Total_TP53=int(i)+Total_TP53\n",
    "Out_DF_S2AB.index=Out_DF_S2AB[\"Gene name\"]\n",
    "Out_DF_S2AB=Out_DF_S2AB.drop(columns=\"Gene name\")\n",
    "Out_DF_S2AB.loc[\"TP53\"][\"Award Amount[title/abstract]\"]=Total_TP53\n",
    "Out_DF_S2AB.to_excel(os.path.join(cwd_Output,\"NCI_NIH+AACR_PM_Data.xlsx\"), engine=\"openpyxl\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "sapphire-product",
   "metadata": {},
   "source": [
    "# Search 3: search without \"cancer\" keyword"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bridal-uganda",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "url_Pubmed_S2C='https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=(\\\"+_gene_+\\\"[ti])&retmax=20'\n",
    "Out_DF_S2C=pd.DataFrame(columns=[\"Gene name\",\"Pubs[title]\",\"Pubs[title/abstract]\", \"Number of Grants[title/abstract]\", \"Award Amount[title/abstract]\", \"Number of Grants[title]\", \"Award Amount[title]\", \"Synonyms\"])\n",
    "NIH_Ti_Raw='Controls Raw Data\\\\search without cancer keyword\\\\NIH RePORTER\\\\Title only data\\\\'\n",
    "NIH_Tiab_Raw='Controls Raw Data\\\\search without cancer keyword\\\\NIH RePORTER\\\\Title and abstract data\\\\'\n",
    "PM_Ti_Raw='Controls Raw Data\\\\search without cancer keyword\\\\NCBI PubMed\\\\Title only data\\\\'\n",
    "PM_Tiab_Raw='Controls Raw Data\\\\search without cancer keyword\\\\NCBI PubMed\\\\Title and abstract data\\\\'\n",
    "params = {\n",
    "        \"criteria\": {\n",
    "    \"advanced_text_search\": {\"operator\": \"advanced\", \"search_field\": \"projecttitle,abstracttext\",\"search_text\": str(\"\")}},\n",
    "        \"include_fields\": [\"ApplId\", \"ProjectTitle\", \"AwardAmount\", \"DirectCostAmt\", \"IndirectCostAmt\",\"ProjectStartDate\", \"ProjectEndDate\"],\"offset\": 0, \"limit\": 500, }\n",
    "Out_DF_S2C=AccessPubMed_and_Reporter_Master_function(Out_DF_S2C,url_Pubmed_S2C,params,PM_Ti_Raw,PM_Tiab_Raw,NIH_Ti_Raw,NIH_Tiab_Raw)\n",
    "Out_DF_S2C.index=Out_DF_S2C[\"Gene name\"]\n",
    "Out_DF_S2C=Out_DF_S2C.drop(columns=\"Gene name\")\n",
    "Out_DF_S2C.to_excel(os.path.join(cwd_Output,\"NIH+PM_Data_no_cancer.xlsx\"), engine=\"openpyxl\")\n",
    "#Award amounts in this control are so high as to be unretrievable by manual or API search"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "constitutional-quality",
   "metadata": {},
   "source": [
    "# Search 4: publications limited to the United States"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "empirical-uzbekistan",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#separate block because this is just publications, cant be used by main function\n",
    "url_Pubmed_S2D='https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=(cancer[ti]+AND+(\"United States\"[pl] OR \"USA\"[Affiliation] OR \"US\"[Affiliation] OR \"United States\"[Affiliation] OR \"United States of America\"[Affiliation] OR \"U.S.A\"[Affiliation] OR \"U.S.\"[Affiliation])+AND+(\\\"+_gene_+\\\"[ti]))&retmax=20'\n",
    "Out_DF_S2D=pd.DataFrame(columns=[\"Gene name\",\"Pubs[title]\",\"Pubs[title/abstract]\",\"Synonyms\"])\n",
    "for i in dfSynonym.index:\n",
    "    gene_terms=str(\"(\\\"+_gene_+\\\"[ti])\")\n",
    "    CT1_queryPM=str(\"\")\n",
    "    RawData=[]\n",
    "    RawData_Title=[]\n",
    "    print(i)\n",
    "    Excluded=False\n",
    "    term = gene_terms.replace(\"_gene_\", str(i))\n",
    "    if i not in str(dfSynonym.loc[i][\"Removed Synonyms\"]):\n",
    "        CT1_queryPM = str(CT1_queryPM) + \"(\" + str(term)\n",
    "    else:\n",
    "        Excluded=True\n",
    "    Synonym_string=\"\"\n",
    "    if not isinstance(dfSynonym.loc[i][\"Synonyms\"],float):\n",
    "        CT1_queryPM = str(CT1_queryPM) + str(\"+OR+\")\n",
    "        if \"|\" in str(dfSynonym.loc[i][\"Synonyms\"]): \n",
    "            Synonyms_list = str(dfSynonym.loc[i][\"Synonyms\"]).split(\"|\")\n",
    "            for x in Synonyms_list:\n",
    "                if (x not in str(dfSynonym.loc[i][\"Removed Synonyms\"])) or (x in str(dfSynonym.loc[i][\"Added Synonyms\"])):\n",
    "                    igene_term = gene_terms.replace(\"_gene_\", str(x))\n",
    "                    Synonym_string=Synonym_string+str(x)+str(\"|\")\n",
    "                    CT1_queryPM = CT1_queryPM + str(igene_term) + str(\"+OR+\")\n",
    "            CT1_queryPM = str(CT1_queryPM[:(len(CT1_queryPM)) - 4]) + str(\")\")\n",
    "        else:\n",
    "            if str(dfSynonym.loc[i][\"Synonyms\"]) not in str(dfSynonym.loc[i][\"Removed Synonyms\"]):\n",
    "                Synonym_string=Synonym_string+ str(dfSynonym.loc[i][\"Synonyms\"])\n",
    "                igene_term = gene_terms.replace(\"_gene_\", str(dfSynonym.loc[i][\"Synonyms\"]))\n",
    "                CT1_queryPM = CT1_queryPM + str(igene_term) + str(\"+OR+\")\n",
    "                CT1_queryPM = str(CT1_queryPM[:(len(CT1_queryPM)) - 4]) + str(\")\")\n",
    "    else:\n",
    "        CT1_queryPM = str(CT1_queryPM) + str(\")\")\n",
    "    if Excluded==True:\n",
    "        CT1_queryPM =\"(\"+ str(CT1_queryPM[4:])\n",
    "        Excluded==False\n",
    "    url_Pubmed_title = url_Pubmed_S2D.replace(\"(\\\"+_gene_+\\\"[ti])\", CT1_queryPM)\n",
    "    url_Pubmed_title = urllib.parse.quote(url_Pubmed_title, safe=\"/+?:=&\")\n",
    "    PubTitleFreeze=os.path.join(cwd_Raw_Data_outputs,'Controls Raw Data\\\\publications limited to the United States\\\\NCBI PubMed\\\\Title only data\\\\' + str(i) + '_PM_title.txt')\n",
    "    if os.path.exists(PubTitleFreeze):\n",
    "        file1 = open(PubTitleFreeze,'r',encoding=\"utf-8\")\n",
    "        data_title = file1.readlines()\n",
    "        for line in data_title:\n",
    "            if \"<eSearchResult><Count>\" in str(line):  # find the line which tells the counts of all terms combined in article titles\n",
    "                get_count = str(line).split(\"Count>\")\n",
    "                title_count = int(get_count[1].replace(\"</\", \"\"))\n",
    "        file1.close()\n",
    "    else:   \n",
    "        data_title = urllib.request.urlopen(url_Pubmed_title)  # open url and get a copy of the data from the page\n",
    "        with open(PubTitleFreeze, 'w',encoding=\"utf-8\") as f:\n",
    "            for line in data_title:\n",
    "                f.write(str(line))  # write a freeze of the data from the page\n",
    "                f.write('\\n')\n",
    "                if \"<eSearchResult><Count>\" in str(line):  # find the line which tells the counts of all terms combined in article titles\n",
    "                    get_count = str(line).split(\"Count>\")\n",
    "                    title_count = int(get_count[1].replace(\"</\", \"\"))\n",
    "            f.write('\\n')\n",
    "            f.write(\"url used for search:\")\n",
    "            f.write(url_Pubmed_title)  # store the freeze and search URL as a txt file\n",
    "            f.write('\\n')\n",
    "            f.write(\"date accessed:\")\n",
    "            GetDate = str(datetime.now())\n",
    "            f.write(GetDate)\n",
    "            f.close()\n",
    "            time.sleep(1)\n",
    "    url_tiab = url_Pubmed_title.replace(\"%5Bti%5D\", \"%5Btiab%5D\")\n",
    "    PubTiabFreeze=os.path.join(cwd_Raw_Data_outputs,'Controls Raw Data\\\\publications limited to the United States\\\\NCBI PubMed\\\\Title and abstract data\\\\' + str(i) + '_PM_tiab.txt')\n",
    "    if os.path.exists(PubTiabFreeze):\n",
    "        print(\"file exists\")\n",
    "        file2 = open(PubTiabFreeze,'r',encoding=\"utf-8\")\n",
    "        data_tiab = file2.readlines()\n",
    "        for line in data_tiab:\n",
    "            if \"<eSearchResult><Count>\" in str(line):  # find the line which tells the counts of all terms combined in article titles\n",
    "                get_count = str(line).split(\"Count>\")\n",
    "                tiab_count = int(get_count[1].replace(\"</\", \"\"))\n",
    "        file2.close()\n",
    "    else:   \n",
    "        data_tiab = urllib.request.urlopen(url_tiab)  # open url and get a copy of the data from the page\n",
    "        with open(PubTiabFreeze, 'w',encoding=\"utf-8\") as f:\n",
    "            for line in data_tiab:\n",
    "                f.write(str(line))  # write a freeze of the data from the page\n",
    "                f.write('\\n')\n",
    "                if \"<eSearchResult><Count>\" in str(line):  # find the line which tells the counts of all terms combined in article titles\n",
    "                    get_count = str(line).split(\"Count>\")\n",
    "                    tiab_count = int(get_count[1].replace(\"</\", \"\"))\n",
    "            f.write('\\n')\n",
    "            f.write(\"url used for search:\")\n",
    "            f.write(url_tiab)  # store the freeze and search URL as a txt file\n",
    "            f.write('\\n')\n",
    "            f.write(\"date accessed:\")\n",
    "            GetDate = str(datetime.now())\n",
    "            f.write(GetDate)\n",
    "            f.close()\n",
    "            #time.sleep(1)\n",
    "    AppendSeries={\"Gene name\": str(i), \"Pubs[title]\": title_count, \"Pubs[title/abstract]\": tiab_count,\"Synonyms\":Synonym_string}\n",
    "    print(AppendSeries)\n",
    "    Out_DF_S2D = Out_DF_S2D.append(AppendSeries, ignore_index=True)\n",
    "Out_DF_S2D.index=Out_DF_S2D[\"Gene name\"]\n",
    "Out_DF_S2D=Out_DF_S2D.drop(columns=\"Gene name\")\n",
    "Out_DF_S2D.to_excel(os.path.join(cwd_Output,\"PM_Data_USA.xlsx\"), engine=\"openpyxl\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "handed-journal",
   "metadata": {},
   "source": [
    "# Search 5: Publications and grants with animal keywords"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "animated-celebration",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "url_Pubmed_S3A='https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=(cancer[ti]+AND+((Canine[mh])+OR+(feline[mh])+OR+(Bovine[mh])+OR+(equine[mh])+OR+(poultry[mh]))+AND+(\\\"+_gene_+\\\"[ti]))&retmax=20'\n",
    "Out_DF_S3A=pd.DataFrame(columns=[\"Gene name\",\"Pubs[title]\",\"Pubs[title/abstract]\", \"Number of Grants[title/abstract]\", \"Award Amount[title/abstract]\", \"Number of Grants[title]\", \"Award Amount[title]\", \"Synonyms\"])\n",
    "NIH_Ti_raw='Controls Raw Data\\\\Publications and grants with animal keywords\\\\NIH RePORTER\\\\Title only data\\\\'\n",
    "NIH_Tiab_raw='Controls Raw Data\\\\Publications and grants with animal keywords\\\\NIH RePORTER\\\\Title and abstract data\\\\'\n",
    "PM_Ti_raw='Controls Raw Data\\\\Publications and grants with animal keywords\\\\NCBI PubMed\\\\Title only data\\\\'\n",
    "PM_Tiab_raw='Controls Raw Data\\\\Publications and grants with animal keywords\\\\NCBI PubMed\\\\Title and abstract data\\\\'\n",
    "params = {\"criteria\": {\n",
    "    \"advanced_text_search\": {\"operator\": \"advanced\", \"search_field\": \"projecttitle,abstracttext\",\"search_text\": str(\"cancer AND (Canine OR Bovine OR feline OR equine OR poultry) AND \")}},\n",
    "        \"include_fields\": [\"ApplId\", \"ProjectTitle\", \"AwardAmount\", \"DirectCostAmt\", \"IndirectCostAmt\",\"ProjectStartDate\", \"ProjectEndDate\"],\"offset\": 0, \"limit\": 500, }\n",
    "Out_DF_S3A=AccessPubMed_and_Reporter_Master_function(Out_DF_S3A,url_Pubmed_S3A,params,PM_Ti_raw,PM_Tiab_raw,NIH_Ti_raw,NIH_Tiab_raw)\n",
    "Out_DF_S3A.index=Out_DF_S3A[\"Gene name\"]\n",
    "Out_DF_S3A=Out_DF_S3A.drop(columns=\"Gene name\")\n",
    "Out_DF_S3A.to_excel(os.path.join(cwd_Output,\"NIH+PM_Data_animal_keywords.xlsx\"), engine=\"openpyxl\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "typical-armstrong",
   "metadata": {},
   "source": [
    "# Search 6: search without synonyms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "immediate-saskatchewan",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "#separate block becuase this one cant include synonyms\n",
    "# it is almost exaclt identical to the main function block, save for the part that adds synonyms\n",
    "#this code only uses the default gene name, or the first synonym if that gene name is exlcuded\n",
    "url_Pubmed_S3B='https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=(cancer[ti]+AND+(\\\"+_gene_+\\\"[ti]))&retmax=20'\n",
    "Out_DF_S3B=pd.DataFrame(columns=[\"Gene name\",\"Pubs[title]\",\"Pubs[title/abstract]\", \"Number of Grants[title/abstract]\", \"Award Amount[title/abstract]\", \"Number of Grants[title]\", \"Award Amount[title]\"])\n",
    "PM_Ti_raw='Controls Raw Data\\\\search without synonyms\\\\NCBI PubMed\\\\Title only data\\\\'\n",
    "PM_Tiab_raw='Controls Raw Data\\\\search without synonyms\\\\NCBI PubMed\\\\Title and abstract data\\\\'\n",
    "NIH_Ti_raw='Controls Raw Data\\\\search without synonyms\\\\NIH RePORTER\\\\Title only data\\\\'\n",
    "NIH_Tiab_raw='Controls Raw Data\\\\search without synonyms\\\\NIH RePORTER\\\\Title and abstract data\\\\'\n",
    "for i in dfSynonym.index:\n",
    "    print(i)\n",
    "    Excluded=False\n",
    "    term = gene_terms.replace(\"_gene_\", str(i))\n",
    "    if i not in str(dfSynonym.loc[i][\"Removed Synonyms\"]):\n",
    "        CT1_queryPM = str(term)\n",
    "        CT1_queryNIH = str(\"(\" + \"\\\"\" + str(i) + \"\\\"\")\n",
    "    else:\n",
    "        print(\"Exlcuded gene name\")\n",
    "        Get_synonym=dfSynonym\n",
    "        CT1_queryPM =\"(\"+ str(CT1_queryPM[4:])\n",
    "        CT1_queryNIH = \"(\" + str(CT1_queryNIH[4:])\n",
    "        if not isinstance(dfSynonym.loc[i][\"Synonyms\"],float):\n",
    "            if \"|\" in str(dfSynonym.loc[i][\"Synonyms\"]):\n",
    "                Synonyms_list = str(dfSynonym.loc[i][\"Synonyms\"]).split(\"|\")\n",
    "                Synonym_term=Synonyms_list[0]\n",
    "                if str(Synonym_term) not in str(dfSynonym.loc[i][\"Removed Synonyms\"]):\n",
    "                    CT1_queryPM=gene_terms.replace(\"_gene_\", Synonym_term)\n",
    "                    CT1_queryNIH=str(\"(\" + \"\\\"\" + str(Synonym_term) + \"\\\"\")\n",
    "            else:\n",
    "                if str(dfSynonym.loc[i][\"Synonyms\"]) not in str(dfSynonym.loc[i][\"Removed Synonyms\"]):\n",
    "                    CT1_queryPM=gene_terms.replace(\"_gene_\", str(dfSynonym.loc[i][\"Synonyms\"]))\n",
    "                    CT1_queryNIH=str(\"(\" + \"\\\"\" + str(dfSynonym.loc[i][\"Synonyms\"]) + \"\\\"\")\n",
    "    CT1_queryPM = str(CT1_queryPM) + str(\")\")\n",
    "    CT1_queryNIH = str(CT1_queryNIH) + str(\")\")\n",
    "    Excluded==False\n",
    "    if i ==\"FES\":\n",
    "        CT1_queryPM=(\"(\\\"+FPS+\\\"[ti])\")\n",
    "        CT1_queryNIH=\"( FPS )\"\n",
    "    if i ==\"SET\":\n",
    "        CT1_queryPM=(\"(\\\"+2PP2A+\\\"[ti])\")\n",
    "        CT1_queryNIH=\"( 2PP2A)\"\n",
    "    url_Pubmed_title = url_Pubmed_S3B.replace(\"(\\\"+_gene_+\\\"[ti])\", CT1_queryPM)\n",
    "    url_Pubmed_title = urllib.parse.quote(url_Pubmed_title, safe=\"/+?:=&\")\n",
    "    PubTitleFreeze=os.path.join(cwd_Raw_Data_outputs,PM_Ti_raw + str(i) + '_PM_title.txt')\n",
    "    if os.path.exists(PubTitleFreeze):\n",
    "        file1 = open(PubTitleFreeze,'r',encoding=\"utf-8\")\n",
    "        data_title = file1.readlines()\n",
    "        for line in data_title:\n",
    "            if \"<eSearchResult><Count>\" in str(line):  # find the line which tells the counts of all terms combined in article titles\n",
    "                get_count = str(line).split(\"Count>\")\n",
    "                title_count = int(get_count[1].replace(\"</\", \"\"))\n",
    "        file1.close()\n",
    "    else:   \n",
    "        data_title = urllib.request.urlopen(url_Pubmed_title)  # open url and get a copy of the data from the page\n",
    "        with open(PubTitleFreeze, 'w',encoding=\"utf-8\") as f:\n",
    "            for line in data_title:\n",
    "                f.write(str(line))  # write a freeze of the data from the page\n",
    "                f.write('\\n')\n",
    "                if \"<eSearchResult><Count>\" in str(line):  # find the line which tells the counts of all terms combined in article titles\n",
    "                    get_count = str(line).split(\"Count>\")\n",
    "                    title_count = int(get_count[1].replace(\"</\", \"\"))\n",
    "            f.write('\\n')\n",
    "            f.write(\"url used for search:\")\n",
    "            f.write(url_Pubmed_title)  # store the freeze and search URL as a txt file\n",
    "            f.write('\\n')\n",
    "            f.write(\"date accessed:\")\n",
    "            GetDate = str(datetime.now())\n",
    "            f.write(GetDate)\n",
    "            f.close()\n",
    "            time.sleep(1)\n",
    "    url_tiab = url_Pubmed_title.replace(\"%5Bti%5D\", \"%5Btiab%5D\")# same idea as above, only this time rpelacing the [title] keyword with [tiab], querying the mention of a keyword in both title OR abstract\n",
    "    PubTiabFreeze=os.path.join(cwd_Raw_Data_outputs,'Controls Raw Data\\\\search without synonyms\\\\NCBI PubMed\\\\Title and abstract data\\\\' + str(i) + '_PM_tiab.txt')\n",
    "    if os.path.exists(PubTiabFreeze):\n",
    "        print(\"file exists\")\n",
    "        file2 = open(PubTiabFreeze,'r',encoding=\"utf-8\")\n",
    "        data_tiab = file2.readlines()\n",
    "        for line in data_tiab:\n",
    "            if \"<eSearchResult><Count>\" in str(line):  # find the line which tells the counts of all terms combined in article titles\n",
    "                get_count = str(line).split(\"Count>\")\n",
    "                tiab_count = int(get_count[1].replace(\"</\", \"\"))\n",
    "        file2.close()\n",
    "    else:   \n",
    "        data_tiab = urllib.request.urlopen(url_tiab)  # open url and get a copy of the data from the page\n",
    "        with open(PubTiabFreeze, 'w',encoding=\"utf-8\") as f:\n",
    "            for line in data_tiab:\n",
    "                f.write(str(line))  # write a freeze of the data from the page\n",
    "                f.write('\\n')\n",
    "                if \"<eSearchResult><Count>\" in str(line):  # find the line which tells the counts of all terms combined in article titles\n",
    "                    get_count = str(line).split(\"Count>\")\n",
    "                    tiab_count = int(get_count[1].replace(\"</\", \"\"))\n",
    "            f.write('\\n')\n",
    "            f.write(\"url used for search:\")\n",
    "            f.write(url_tiab)  # store the freeze and search URL as a txt file\n",
    "            f.write('\\n')\n",
    "            f.write(\"date accessed:\")\n",
    "            GetDate = str(datetime.now())\n",
    "            f.write(GetDate)\n",
    "            f.close()\n",
    "            time.sleep(1)\n",
    "    if (CT1_queryNIH[-3:]==\"OR \"):\n",
    "        CT1_queryNIH=CT1_queryNIH[:-3]+(\")\")\n",
    "    paramsPlus = {\n",
    "        \"criteria\": {\n",
    "    \"advanced_text_search\": {\"operator\": \"advanced\", \"search_field\": \"projecttitle,abstracttext\",\"search_text\": str(\"cancer AND (\" + str(CT1_queryNIH) + \")\")}},#(Canine OR Bovine OR feline OR equine OR poultry)\n",
    "        \"include_fields\": [\"ApplId\", \"ProjectTitle\", \"AwardAmount\", \"DirectCostAmt\", \"IndirectCostAmt\",\"ProjectStartDate\", \"ProjectEndDate\"],\"offset\": 0, \"limit\": 500, }\n",
    "    NIH_Raw_tiab = os.path.join(cwd_Raw_Data_outputs, NIH_Tiab_raw + str(i) + '_NIH_tiab.txt')\n",
    "    NIH_Raw_title = os.path.join(cwd_Raw_Data_outputs, NIH_Ti_raw + str(i) + '_NIH_title.txt')\n",
    "    fileFound=False\n",
    "    if os.path.exists(NIH_Raw_tiab):\n",
    "        file3 = open(NIH_Raw_tiab, 'r',encoding=\"utf-8\")\n",
    "        lines3 = file3.readlines()\n",
    "        JSON_output=lines3[0]\n",
    "        GetSearchNumber=0\n",
    "        fileFound=True\n",
    "    else:\n",
    "        response = requests.post(\"https://api.reporter.nih.gov/v2/projects/Search\", json=paramsPlus)\n",
    "        JSON_output = response.text  # load JSON, get results in sets of 500\n",
    "        file3 = open(NIH_Raw_tiab, 'w',encoding=\"utf-8\")\n",
    "        file3.writelines(JSON_output)\n",
    "    Amount_Start = JSON_output.find(\"\\\"total\\\":\") #find the total amount of grants\n",
    "    Amount_End = JSON_output.find(\"\\\"offset\\\":\")\n",
    "    Amount = JSON_output[Amount_Start:Amount_End].replace(\",\", \"\").split(\":\")\n",
    "    Grants = int(Amount[1])\n",
    "    Award = 0\n",
    "    Costs_list = [0,0,0]\n",
    "    if Grants >= 1:  # if there are grants for the gene,\n",
    "        if Grants < 500:  # if there are less than 500 grants, we only need 1 search\n",
    "            Costs_list = GetAwardAmount(JSON_output, Costs_list)\n",
    "        else:  # if there are more than 500 grants, we need to perform the search 500 grants at a time\n",
    "            Costs_list = GetAwardAmount(JSON_output, Costs_list)\n",
    "            GrantsLeft = Grants\n",
    "            while GrantsLeft > 0:\n",
    "                if GrantsLeft > 500:\n",
    "                    GrantsLeft -= 500\n",
    "                    if fileFound==True:\n",
    "                        GetSearchNumber+=1\n",
    "                        JSON_output_offset=lines3[GetSearchNumber]\n",
    "                    else:\n",
    "                        time.sleep(3)\n",
    "                        offset += 500\n",
    "                        paramsPlus2 = paramsPlus\n",
    "                        paramsPlus2[\"offset\"] = offset\n",
    "                        response_plus = requests.post(\"https://api.reporter.nih.gov/v2/projects/Search\", json=paramsPlus2)\n",
    "                        JSON_output_offset = response_plus.text\n",
    "                        file3.write(\"\\n\")\n",
    "                        file3.writelines(JSON_output_offset)\n",
    "                    Costs_list = GetAwardAmount(JSON_output_offset, Costs_list)\n",
    "                else:\n",
    "                    GrantsLeft = 0\n",
    "    if fileFound==False:\n",
    "        file3.write(\"\\n\")\n",
    "        file3.write(\"Date accessed:\")\n",
    "        GetDate = str(datetime.now())\n",
    "        file3.write(GetDate)\n",
    "        file3.close()\n",
    "    Award =int(Costs_list[0])\n",
    "    time.sleep(1)\n",
    "    paramsPlus_Title = {\n",
    "        \"criteria\": {\n",
    "            \"advanced_text_search\": {\"operator\": \"advanced\", \"search_field\": \"projecttitle\",\"search_text\": str(\"cancer AND (\" + str(CT1_queryNIH) + \")\")}},\n",
    "        \"include_fields\": [\"ApplId\", \"ProjectTitle\", \"AwardAmount\", \"DirectCostAmt\", \"IndirectCostAmt\",\"ProjectStartDate\", \"ProjectEndDate\"],\"offset\": 0, \"limit\": 500, }\n",
    "    fileFound_Title=False\n",
    "    if os.path.exists(NIH_Raw_title):\n",
    "        file4 = open(NIH_Raw_title, 'r',encoding=\"utf-8\")\n",
    "        lines4 = file4.readlines()\n",
    "        JSON_output_Title=lines4[0]\n",
    "        GetSearchNumber_title=0\n",
    "        fileFound_Title=True\n",
    "    else:\n",
    "        response_title = requests.post(\"https://api.reporter.nih.gov/v2/projects/Search\", json=paramsPlus_Title)\n",
    "        JSON_output_Title = response_title.text  # load JSON, get results in sets of 500\n",
    "        file4 = open(NIH_Raw_title, 'w',encoding=\"utf-8\")\n",
    "        file4.writelines(JSON_output_Title)\n",
    "    Amount_Start_Title = JSON_output_Title.find(\"\\\"total\\\":\")\n",
    "    Amount_End_Title = JSON_output_Title.find(\"\\\"offset\\\":\")\n",
    "    Amount_Title = JSON_output_Title[Amount_Start_Title:Amount_End_Title].replace(\",\", \"\").split(\":\")\n",
    "    Grants_Title = int(Amount_Title[1])\n",
    "    offset = 0\n",
    "    Award_Title = 0\n",
    "    Costs_list_Title = [0,0,0]\n",
    "    if Grants_Title >= 1:\n",
    "        if Grants_Title < 500:\n",
    "            Costs_list_Title = GetAwardAmount(JSON_output_Title, Costs_list_Title)\n",
    "        else:\n",
    "            Costs_list_Title = GetAwardAmount(JSON_output_Title, Costs_list_Title)\n",
    "            GrantsLeft_Title = Grants_Title\n",
    "            while GrantsLeft_Title > 0:\n",
    "                if GrantsLeft_Title > 500:\n",
    "                    GrantsLeft_Title -= 500\n",
    "                    if fileFound_Title==True:\n",
    "                        GetSearchNumber_title+=1\n",
    "                        JSON_output_offset_Title=lines3[GetSearchNumber_title]\n",
    "                    else:\n",
    "                        time.sleep(3)\n",
    "                        offset += 500\n",
    "                        paramsPlus2_Title = paramsPlus_Title\n",
    "                        paramsPlus2_Title[\"offset\"] = offset\n",
    "                        response_plus_Title = requests.post(\"https://api.reporter.nih.gov/v2/projects/Search\", json=paramsPlus2_Title)\n",
    "                        JSON_output_offset_Title = response_plus_Title.text\n",
    "                        file4.write(\"\\n\")\n",
    "                        file4.writelines(JSON_output_offset_Title)\n",
    "                    Costs_list_Title= GetAwardAmount(JSON_output_offset_Title, Costs_list_Title)\n",
    "                else:\n",
    "                    GrantsLeft_Title = 0\n",
    "    if fileFound_Title==False:\n",
    "        file4.write(\"\\n\")\n",
    "        file4.write(\"Date accessed:\")\n",
    "        GetDate = str(datetime.now())\n",
    "        file4.write(GetDate)\n",
    "        file4.close()\n",
    "    Award_Title =int(Costs_list_Title[0])\n",
    "    AppendSeries={\"Gene name\": str(i), \"Pubs[title]\": title_count, \"Pubs[title/abstract]\": tiab_count, \"Number of Grants[title/abstract]\": int(Amount[1]),\"Award Amount[title/abstract]\": Award, \"Number of Grants[title]\":int(Amount_Title[1]), \"Award Amount[title]\":Award_Title}\n",
    "    print(AppendSeries)\n",
    "    Out_DF_S3B = Out_DF_S3B.append(AppendSeries, ignore_index=True)\n",
    "    Award = 0\n",
    "    Award_Title = 0\n",
    "    #time.sleep(1)\n",
    "    CT1_queryPM = str(\"\")\n",
    "    CT1_queryNIH = str(\"\")\n",
    "Out_DF_S3B.index=Out_DF_S3B[\"Gene name\"]\n",
    "Out_DF_S3B=Out_DF_S3B.drop(columns=\"Gene name\")\n",
    "Out_DF_S3B.to_excel(os.path.join(cwd_Output,\"NIH+PM_Data_no_synonyms.xlsx\"), engine=\"openpyxl\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "liberal-efficiency",
   "metadata": {},
   "source": [
    "# Search 7: Trivial Synonyms only"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bronze-japan",
   "metadata": {},
   "outputs": [],
   "source": [
    "#a trivial synonym is a gene name synonym which is either a substring of the main gene name, or the gene name is a substring of the synonym\n",
    "#for a gene name x and a synonym y, y is a trivial synonym of x if y is a substring of x and vice versa\n",
    "url_Pubmed_S3C='https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=(cancer[ti]+AND+(\\\"+_gene_+\\\"[ti]))&retmax=20'\n",
    "Out_DF_S3C=pd.DataFrame(columns=[\"Gene name\",\"Pubs[title]\",\"Pubs[title/abstract]\", \"Number of Grants[title/abstract]\", \"Award Amount[title/abstract]\", \"Number of Grants[title]\", \"Award Amount[title]\", \"Synonyms\"])\n",
    "PM_Ti_raw='Controls Raw Data\\\\Trivial Synonyms only\\\\NCBI PubMed\\\\Title only data\\\\'\n",
    "PM_Tiab_raw='Controls Raw Data\\\\Trivial Synonyms only\\\\NCBI PubMed\\\\Title and abstract data\\\\'\n",
    "NIH_Ti_raw='Controls Raw Data\\\\Trivial Synonyms only\\\\NIH RePORTER\\\\Title only data\\\\'\n",
    "NIH_Tiab_raw='Controls Raw Data\\\\Trivial Synonyms only\\\\NIH RePORTER\\\\Title and abstract data\\\\'\n",
    "for i in dfSynonym.index:\n",
    "    print(i)\n",
    "    Excluded=False\n",
    "    term = gene_terms.replace(\"_gene_\", str(i))\n",
    "    if i not in str(dfSynonym.loc[i][\"Removed Synonyms\"]):\n",
    "        CT1_queryPM = str(CT1_queryPM) + \"(\" + str(term)\n",
    "        CT1_queryNIH = str(CT1_queryNIH) + \"(\" + \"\\\"\" + str(i) + \"\\\" \"\n",
    "    else: \n",
    "        Excluded=True\n",
    "    Synonym_string=\"\"\n",
    "    if not isinstance(dfSynonym.loc[i][\"Synonyms\"],float):  # first check if a list of synonyms exists. if there are no synonyms, value is nan, Nan values are expressed as a float\n",
    "        CT1_queryPM = str(CT1_queryPM) + str(\"+OR+\")\n",
    "        CT1_queryNIH = str(CT1_queryNIH) + str(\" OR \")\n",
    "        if \"|\" in str(dfSynonym.loc[i][\"Synonyms\"]):  # if multiple terms are included, they will be separated by \"|\" character, so if character is in list of synonyms, means there are multiple synonyms\n",
    "            Synonyms_list = str(dfSynonym.loc[i][\"Synonyms\"]).split(\"|\")  # split terms into list\n",
    "            for x in Synonyms_list:  # add each item from list as an additional term to query, formatting the term appropriately\n",
    "                if (x not in str(dfSynonym.loc[i][\"Removed Synonyms\"])) and (( x in str(i)) or (str(i) in x)):\n",
    "                    igene_term = gene_terms.replace(\"_gene_\", str(x))\n",
    "                    Synonym_string=Synonym_string+str(x)+str(\"|\")\n",
    "                    CT1_queryPM = CT1_queryPM + str(igene_term) + str(\"+OR+\")\n",
    "                    CT1_queryNIH = str(CT1_queryNIH) + \"\\\"\" + str(x) + \"\\\"\" + str(\" OR \")\n",
    "            CT1_queryPM = str(CT1_queryPM[:(len(CT1_queryPM)) - 4]) + str(\")\")\n",
    "            CT1_queryNIH = CT1_queryNIH[:(len(CT1_queryNIH)) - 4] + str(\")\")\n",
    "        else:  # if \"|\" character is not present but list of synonyms is not null, then there is just one synonymous term\n",
    "            if str(dfSynonym.loc[i][\"Synonyms\"]) not in str(dfSynonym.loc[i][\"Removed Synonyms\"]) and ((str(dfSynonym.loc[i][\"Synonyms\"]) in str(i)) or (str(i) in str(dfSynonym.loc[i][\"Synonyms\"]))):\n",
    "                Synonym_string=Synonym_string+ str(dfSynonym.loc[i][\"Synonyms\"])# add single synonym in the same format as above\n",
    "                igene_term = gene_terms.replace(\"_gene_\", str(dfSynonym.loc[i][\"Synonyms\"]))\n",
    "                CT1_queryPM = CT1_queryPM + str(igene_term) + str(\"+OR+\")\n",
    "                CT1_queryPM = str(CT1_queryPM[:(len(CT1_queryPM)) - 4]) + str(\")\")\n",
    "                CT1_queryNIH = CT1_queryNIH + str(\"\\\"\" + dfSynonym.loc[i][\"Synonyms\"] + \"\\\"\") + str(\" OR \")\n",
    "                CT1_queryNIH = str(CT1_queryNIH[:(len(CT1_queryNIH)) - 4]) + str(\")\")\n",
    "    else:# same as above, but for genes with no synonyms, just close the parentheses\n",
    "        CT1_queryPM = str(CT1_queryPM) + str(\")\")\n",
    "        CT1_queryNIH = str(CT1_queryNIH) + str(\")\")\n",
    "    if Excluded==True:\n",
    "        CT1_queryPM =\"(\"+ str(CT1_queryPM[4:])\n",
    "        CT1_queryNIH = \"(\" + str(CT1_queryNIH[4:])\n",
    "        Excluded==False\n",
    "    if i ==\"FES\":\n",
    "        CT1_queryPM=(\"(\\\"+FPS+\\\"[ti])\")\n",
    "        CT1_queryNIH=\"( FPS )\"\n",
    "    if i ==\"SET\":\n",
    "        CT1_queryPM=(\"(\\\"+2PP2A+\\\"[ti])\")\n",
    "        CT1_queryNIH=\"( 2PP2A)\"\n",
    "    url_Pubmed_title = url_Pubmed_S3C.replace(\"(\\\"+_gene_+\\\"[ti])\", CT1_queryPM)\n",
    "    url_Pubmed_title = urllib.parse.quote(url_Pubmed_title, safe=\"/+?:=&\")\n",
    "    PubTitleFreeze=os.path.join(cwd_Raw_Data_outputs,PM_Ti_raw + str(i) + '_PM_title.txt')\n",
    "    if os.path.exists(PubTitleFreeze):\n",
    "        file1 = open(PubTitleFreeze,'r',encoding=\"utf-8\")\n",
    "        data_title = file1.readlines()\n",
    "        for line in data_title:\n",
    "            if \"<eSearchResult><Count>\" in str(line):  # find the line which tells the counts of all terms combined in article titles\n",
    "                get_count = str(line).split(\"Count>\")\n",
    "                title_count = int(get_count[1].replace(\"</\", \"\"))\n",
    "        file1.close()\n",
    "    else:   \n",
    "        data_title = urllib.request.urlopen(url_Pubmed_title)  # open url and get a copy of the data from the page\n",
    "        with open(PubTitleFreeze, 'w',encoding=\"utf-8\") as f:\n",
    "            for line in data_title:\n",
    "                f.write(str(line))  # write a freeze of the data from the page\n",
    "                f.write('\\n')\n",
    "                if \"<eSearchResult><Count>\" in str(line):  # find the line which tells the counts of all terms combined in article titles\n",
    "                    get_count = str(line).split(\"Count>\")\n",
    "                    title_count = int(get_count[1].replace(\"</\", \"\"))\n",
    "            f.write('\\n')\n",
    "            f.write(\"url used for search:\")\n",
    "            f.write(url_Pubmed_title)  # store the freeze and search URL as a txt file\n",
    "            f.write('\\n')\n",
    "            f.write(\"date accessed:\")\n",
    "            GetDate = str(datetime.now())\n",
    "            f.write(GetDate)\n",
    "            f.close()\n",
    "            time.sleep(1)\n",
    "    url_tiab = url_Pubmed_title.replace(\"%5Bti%5D\", \"%5Btiab%5D\")# same idea as above, only this time rpelacing the [title] keyword with [tiab], querying the mention of a keyword in both title OR abstract\n",
    "    PubTiabFreeze=os.path.join(cwd_Raw_Data_outputs,'Controls Raw Data\\\\Supplementary Figure S3C Data\\\\NCBI PubMed\\\\Title and abstract data\\\\' + str(i) + '_PM_tiab.txt')\n",
    "    if os.path.exists(PubTiabFreeze):\n",
    "        print(\"file exists\")\n",
    "        file2 = open(PubTiabFreeze,'r',encoding=\"utf-8\")\n",
    "        data_tiab = file2.readlines()\n",
    "        for line in data_tiab:\n",
    "            if \"<eSearchResult><Count>\" in str(line):  # find the line which tells the counts of all terms combined in article titles\n",
    "                get_count = str(line).split(\"Count>\")\n",
    "                tiab_count = int(get_count[1].replace(\"</\", \"\"))\n",
    "        file2.close()\n",
    "    else:   \n",
    "        data_tiab = urllib.request.urlopen(url_tiab)  # open url and get a copy of the data from the page\n",
    "        with open(PubTiabFreeze, 'w',encoding=\"utf-8\") as f:\n",
    "            for line in data_tiab:\n",
    "                f.write(str(line))  # write a freeze of the data from the page\n",
    "                f.write('\\n')\n",
    "                if \"<eSearchResult><Count>\" in str(line):  # find the line which tells the counts of all terms combined in article titles\n",
    "                    get_count = str(line).split(\"Count>\")\n",
    "                    tiab_count = int(get_count[1].replace(\"</\", \"\"))\n",
    "            f.write('\\n')\n",
    "            f.write(\"url used for search:\")\n",
    "            f.write(url_tiab)  # store the freeze and search URL as a txt file\n",
    "            f.write('\\n')\n",
    "            f.write(\"date accessed:\")\n",
    "            GetDate = str(datetime.now())\n",
    "            f.write(GetDate)\n",
    "            f.close()\n",
    "            time.sleep(1)\n",
    "    if (CT1_queryNIH[-3:]==\"OR \"):\n",
    "        CT1_queryNIH=CT1_queryNIH[:-3]+(\")\")\n",
    "    paramsPlus = {\n",
    "        \"criteria\": {\n",
    "    \"advanced_text_search\": {\"operator\": \"advanced\", \"search_field\": \"projecttitle,abstracttext\",\"search_text\": str(\"cancer AND (\" + str(CT1_queryNIH) + \")\")}},\n",
    "        \"include_fields\": [\"ApplId\", \"ProjectTitle\", \"AwardAmount\", \"DirectCostAmt\", \"IndirectCostAmt\",\"ProjectStartDate\", \"ProjectEndDate\"],\"offset\": 0, \"limit\": 500, }\n",
    "    NIH_Raw_tiab = os.path.join(cwd_Raw_Data_outputs, NIH_Tiab_raw + str(i) + '_NIH_tiab.txt')\n",
    "    NIH_Raw_title = os.path.join(cwd_Raw_Data_outputs, NIH_Ti_raw + str(i) + '_NIH_title.txt')\n",
    "    fileFound=False\n",
    "    if os.path.exists(NIH_Raw_tiab):\n",
    "        file3 = open(NIH_Raw_tiab, 'r',encoding=\"utf-8\")\n",
    "        lines3 = file3.readlines()\n",
    "        JSON_output=lines3[0]\n",
    "        GetSearchNumber=0\n",
    "        fileFound=True\n",
    "    else:\n",
    "        response = requests.post(\"https://api.reporter.nih.gov/v2/projects/Search\", json=paramsPlus)\n",
    "        JSON_output = response.text  # load JSON, get results in sets of 500\n",
    "        file3 = open(NIH_Raw_tiab, 'w',encoding=\"utf-8\")\n",
    "        file3.writelines(JSON_output)\n",
    "    Amount_Start = JSON_output.find(\"\\\"total\\\":\") #find the total amount of grants\n",
    "    Amount_End = JSON_output.find(\"\\\"offset\\\":\")\n",
    "    Amount = JSON_output[Amount_Start:Amount_End].replace(\",\", \"\").split(\":\")\n",
    "    Grants = int(Amount[1])\n",
    "    Award = 0\n",
    "    Costs_list = [0,0,0]\n",
    "    if Grants >= 1:  # if there are grants for the gene,\n",
    "        if Grants < 500:  # if there are less than 500 grants, we only need 1 search\n",
    "            Costs_list = GetAwardAmount(JSON_output, Costs_list)\n",
    "        else:  # if there are more than 500 grants, we need to perform the search 500 grants at a time\n",
    "            Costs_list = GetAwardAmount(JSON_output, Costs_list)\n",
    "            GrantsLeft = Grants\n",
    "            while GrantsLeft > 0:\n",
    "                if GrantsLeft > 500:\n",
    "                    GrantsLeft -= 500\n",
    "                    if fileFound==True:\n",
    "                        GetSearchNumber+=1\n",
    "                        JSON_output_offset=lines3[GetSearchNumber]\n",
    "                    else:\n",
    "                        time.sleep(3)\n",
    "                        offset += 500\n",
    "                        paramsPlus2 = paramsPlus\n",
    "                        paramsPlus2[\"offset\"] = offset\n",
    "                        response_plus = requests.post(\"https://api.reporter.nih.gov/v2/projects/Search\", json=paramsPlus2)\n",
    "                        JSON_output_offset = response_plus.text\n",
    "                        file3.write(\"\\n\")\n",
    "                        file3.writelines(JSON_output_offset)\n",
    "                    Costs_list = GetAwardAmount(JSON_output_offset, Costs_list)\n",
    "                else:\n",
    "                    GrantsLeft = 0\n",
    "    if fileFound==False:\n",
    "        file3.write(\"\\n\")\n",
    "        file3.write(\"Date accessed:\")\n",
    "        GetDate = str(datetime.now())\n",
    "        file3.write(GetDate)\n",
    "        file3.close()\n",
    "    Award =int(Costs_list[0])\n",
    "    time.sleep(1)\n",
    "    paramsPlus_Title = {\n",
    "        \"criteria\": {\n",
    "            \"advanced_text_search\": {\"operator\": \"advanced\", \"search_field\": \"projecttitle\",\"search_text\": str(\"cancer AND (\" + str(CT1_queryNIH) + \")\")}},\n",
    "        \"include_fields\": [\"ApplId\", \"ProjectTitle\", \"AwardAmount\", \"DirectCostAmt\", \"IndirectCostAmt\",\"ProjectStartDate\", \"ProjectEndDate\"],\"offset\": 0, \"limit\": 500, }\n",
    "    fileFound_Title=False\n",
    "    if os.path.exists(NIH_Raw_title):\n",
    "        file4 = open(NIH_Raw_title, 'r',encoding=\"utf-8\")\n",
    "        lines4 = file4.readlines()\n",
    "        JSON_output_Title=lines4[0]\n",
    "        GetSearchNumber_title=0\n",
    "        fileFound_Title=True\n",
    "    else:\n",
    "        response_title = requests.post(\"https://api.reporter.nih.gov/v2/projects/Search\", json=paramsPlus_Title)\n",
    "        JSON_output_Title = response_title.text  # load JSON, get results in sets of 500\n",
    "        file4 = open(NIH_Raw_title, 'w',encoding=\"utf-8\")\n",
    "        file4.writelines(JSON_output_Title)\n",
    "    Amount_Start_Title = JSON_output_Title.find(\"\\\"total\\\":\")\n",
    "    Amount_End_Title = JSON_output_Title.find(\"\\\"offset\\\":\")\n",
    "    Amount_Title = JSON_output_Title[Amount_Start_Title:Amount_End_Title].replace(\",\", \"\").split(\":\")\n",
    "    Grants_Title = int(Amount_Title[1])\n",
    "    offset = 0\n",
    "    Award_Title = 0\n",
    "    Costs_list_Title = [0,0,0]\n",
    "    if Grants_Title >= 1:\n",
    "        if Grants_Title < 500:\n",
    "            Costs_list_Title = GetAwardAmount(JSON_output_Title, Costs_list_Title)\n",
    "        else:\n",
    "            Costs_list_Title = GetAwardAmount(JSON_output_Title, Costs_list_Title)\n",
    "            GrantsLeft_Title = Grants_Title\n",
    "            while GrantsLeft_Title > 0:\n",
    "                if GrantsLeft_Title > 500:\n",
    "                    GrantsLeft_Title -= 500\n",
    "                    if fileFound_Title==True:\n",
    "                        GetSearchNumber_title+=1\n",
    "                        JSON_output_offset_Title=lines3[GetSearchNumber_title]\n",
    "                    else:\n",
    "                        time.sleep(3)\n",
    "                        offset += 500\n",
    "                        paramsPlus2_Title = paramsPlus_Title\n",
    "                        paramsPlus2_Title[\"offset\"] = offset\n",
    "                        response_plus_Title = requests.post(\"https://api.reporter.nih.gov/v2/projects/Search\", json=paramsPlus2_Title)\n",
    "                        JSON_output_offset_Title = response_plus_Title.text\n",
    "                        file4.write(\"\\n\")\n",
    "                        file4.writelines(JSON_output_offset_Title)\n",
    "                    Costs_list_Title= GetAwardAmount(JSON_output_offset_Title, Costs_list_Title)\n",
    "                else:\n",
    "                    GrantsLeft_Title = 0\n",
    "    if fileFound_Title==False:\n",
    "        file4.write(\"\\n\")\n",
    "        file4.write(\"Date accessed:\")\n",
    "        GetDate = str(datetime.now())\n",
    "        file4.write(GetDate)\n",
    "        file4.close()\n",
    "    Award_Title =int(Costs_list_Title[0])\n",
    "    AppendSeries={\"Gene name\": str(i), \"Pubs[title]\": title_count, \"Pubs[title/abstract]\": tiab_count, \"Number of Grants[title/abstract]\": int(Amount[1]),\"Award Amount[title/abstract]\": Award, \"Number of Grants[title]\":int(Amount_Title[1]), \"Award Amount[title]\":Award_Title, \"Synonyms\":Synonym_string}\n",
    "    print(AppendSeries)\n",
    "    Out_DF_S3C = Out_DF_S3C.append(AppendSeries, ignore_index=True)\n",
    "    Award = 0\n",
    "    Award_Title = 0\n",
    "    #time.sleep(1)\n",
    "    CT1_queryPM = str(\"\")\n",
    "    CT1_queryNIH = str(\"\")\n",
    "df=pd.read_csv(os.path.join(cwd_Raw_Data_outputs,\"NIH+PM_TP53_trivial.csv\"), sep=\",\", header=0)#path for manually downloaded TP53 title/abstract award amount\n",
    "df=df.iloc[:, :-1]\n",
    "df[\"Total\"]=df[\"Total Cost\"]+df[\"Total Cost (Sub Projects)\"]\n",
    "Total_TP53=0\n",
    "for i in df[\"Total\"]:\n",
    "    if (i!=\"\" and i!=\"  \"):#NIH stores empty values as spaces for some reason \n",
    "        Total_TP53=int(i)+Total_TP53\n",
    "Out_DF_S3C.index=Out_DF_S3C[\"Gene name\"]\n",
    "Out_DF_S3C=Out_DF_S3C.drop(columns=\"Gene name\")\n",
    "Out_DF_S3C.loc[\"TP53\"][\"Award Amount[title/abstract]\"]=Total_TP53\n",
    "Out_DF_S3C.to_excel(os.path.join(cwd_Output,\"NIH+PM_Data_trivial_synonyms.xlsx\"), engine=\"openpyxl\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "abandoned-soviet",
   "metadata": {},
   "source": [
    "# Control 8: Grant and publication numbers by publication date/fiscal year in five year bins from 1990-Present"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "artificial-faculty",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "from IPython.display import clear_output\n",
    "getYearsNIH=[[1990,1991,1992,1993,1994], [1995,1996,1997,1998,1999], [2000,2001,2002,2003,2004], [2005, 2006, 2007, 2008, 2009], [2010,2011,2012,2013,2014],[2015,2016,2017,2018,2019],[2020,2021,2022]]\n",
    "getYearsPM=[\"1990:1994\",\"1995:1999\",\"2000:2004\",\"2005:2009\",\"2010:2014\",\"2015:2019\",\"2020:2022\"]\n",
    "getYears_folder=[\"1990-1994\",\"1995-1999\",\"2000-2004\",\"2005-2009\",\"2010-2014\",\"2015-2019\",\"2020-2022\"]\n",
    "destination=\"Controls Raw Data\\\\Publications and grants by 5 year bins\\\\\"\n",
    "for i in range(len(getYearsNIH)):\n",
    "    path=os.path.join(cwd_Output,\"NIH+PM_Data by year\\\\NIH+PM_Data_\"+str(getYearsNIH[i][0])+\".xlsx\")\n",
    "    url_Pubmed_S3D='https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=(cancer[ti]+AND+'+(getYearsPM[i])+'[dp]+AND+(\\\"+_gene_+\\\"[ti]))&retmax=20'\n",
    "    Out_DF_S3D=pd.DataFrame(columns=[\"Gene name\",\"Pubs[title]\",\"Pubs[title/abstract]\", \"Number of Grants[title/abstract]\", \"Award Amount[title/abstract]\", \"Number of Grants[title]\", \"Award Amount[title]\", \"Synonyms\"])\n",
    "    PM_Tiab_raw=destination+\"\\\\\"+getYears_folder[i]+'\\\\NCBI PubMed\\\\Title and abstract data\\\\'\n",
    "    PM_Ti_raw=destination+\"\\\\\"+getYears_folder[i]+'\\\\NCBI PubMed\\\\Title only data\\\\'\n",
    "    NIH_Ti_raw=destination+\"\\\\\"+getYears_folder[i]+'\\\\NIH RePORTER\\\\Title only data\\\\'\n",
    "    NIH_Tiab_raw=destination+\"\\\\\"+getYears_folder[i]+'\\\\NIH RePORTER\\\\Title and abstract data\\\\'\n",
    "    paramsDefault = {\n",
    "                \"criteria\": {\"fiscal_years\":getYearsNIH[i],\n",
    "            \"advanced_text_search\": {\"operator\": \"advanced\", \"search_field\": \"projecttitle,abstracttext\",\"search_text\": str(\"cancer AND \")}},\n",
    "                \"include_fields\": [\"ApplId\", \"ProjectTitle\", \"AwardAmount\", \"DirectCostAmt\", \"IndirectCostAmt\",\"ProjectStartDate\", \"ProjectEndDate\"],\"offset\": 0, \"limit\": 500, }\n",
    "    Out_DF_S3D=AccessPubMed_and_Reporter_Master_function(Out_DF_S3D,url_Pubmed_S3D,paramsDefault,PM_Ti_raw,PM_Tiab_raw,NIH_Ti_raw,NIH_Tiab_raw)\n",
    "    Out_DF_S3D.index=Out_DF_S3D[\"Gene name\"]\n",
    "    Out_DF_S3D=Out_DF_S3D.drop(columns=\"Gene name\")\n",
    "    clear_output(wait=True)\n",
    "    print(path)\n",
    "    Out_DF_S3D.to_excel(path, engine=\"openpyxl\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "governing-agent",
   "metadata": {},
   "outputs": [],
   "source": [
    "#add up dataframes\n",
    "getYearsNIH=[[1995,1996,1997,1998,1999], [2000,2001,2002,2003,2004], [2005, 2006, 2007, 2008, 2009], [2010,2011,2012,2013,2014],[2015,2016,2017,2018,2019],[2020,2021,2022]]\n",
    "getYearsPM=[\"1995:1999\",\"2000:2004\",\"2005:2009\",\"2010:2014\",\"2015:2019\",\"2020:2022\"]\n",
    "getYears_folder=[\"1995-1999\",\"2000-2004\",\"2005-2009\",\"2010-2014\",\"2015-2019\",\"2020-2022\"]\n",
    "destination=\"Controls Raw Data\\\\Publications and grants by 5 year bins\\\\\"\n",
    "pathDefault=os.path.join(cwd_Output,\"NIH+PM_Data by year\\\\NIH+PM_Data_1990.xlsx\")\n",
    "dfADD=pd.read_excel(pathDefault, engine=\"openpyxl\")\n",
    "for i in (range(len(getYearsNIH))):\n",
    "    path=os.path.join(cwd_Output,\"NIH+PM_Data by year\\\\NIH+PM_Data_\"+str(getYearsNIH[(i)][0])+\".xlsx\")\n",
    "    print(path)\n",
    "    getDF=pd.read_excel(path, engine=\"openpyxl\")\n",
    "    dfADD=dfADD.add(getDF)\n",
    "    print(dfADD)\n",
    "    "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "bf2ed49e",
   "metadata": {},
   "source": [
    "# Control 9: Grant and publication numbers including an exhaustive list of cancer synonyms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ca11c6ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "dfCancerSynoyms=pd.read_excel(os.path.join(cwd,\"cancer synonym list.xlsx\"),engine=\"openpyxl\", header=None)\n",
    "cancerSynonymPM=\"(\"\n",
    "cancerSynonymNIH=\"(\"\n",
    "lengthlist=[]\n",
    "for i in dfCancerSynoyms[0]:\n",
    "    partPM=\"\\\"\"+str(i)+\"\\\"\"+\"[ti]+OR+\"\n",
    "    cancerSynonymPM=cancerSynonymPM+partPM\n",
    "    partNIH=\"\\\"\"+str(i)+\"\\\" OR \"\n",
    "    cancerSynonymNIH=cancerSynonymNIH+partNIH\n",
    "    lengthlist.append(i)\n",
    "cancerSynonymPM=cancerSynonymPM[:-4]+\")\"\n",
    "cancerSynonymPM2=\"(\"\n",
    "cancerSynonymPM3=\"(\"\n",
    "for i in range(0,(int(len(lengthlist)/2))):\n",
    "    partPM2=\"\\\"\"+str(lengthlist[i])+\"\\\"\"+\"[ti]+OR+\"\n",
    "    cancerSynonymPM2=cancerSynonymPM2+partPM2\n",
    "cancerSynonymPM2=cancerSynonymPM2[:-4]+\")\"\n",
    "cancerSynonymNIH=cancerSynonymNIH[:-4]+\")\"\n",
    "for i in range((int(len(lengthlist)/2)),(int(len(lengthlist)))):\n",
    "    partPM3=\"\\\"\"+str(lengthlist[i])+\"\\\"\"+\"[ti]+OR+\"\n",
    "    cancerSynonymPM3=cancerSynonymPM3+partPM3\n",
    "\n",
    "from IPython.display import clear_output\n",
    "destination=[\"Controls Raw Data\\\\Publications and grants with all cancer synonyms\",\"Controls Raw Data\\\\Publications and grants with all cancer synonyms 2nd half\"]\n",
    "searchList=[cancerSynonymPM2, cancerSynonymPM3]\n",
    "path1=os.path.join(cwd_Output,\"NIH+PM_Data_Cancer_synonyms1sthalf.xlsx\")\n",
    "path2=os.path.join(cwd_Output,\"NIH+PM_Data_Cancer_synonyms2ndhalf.xlsx\")\n",
    "pathList=[path1,path2]\n",
    "DFList=[]\n",
    "for s in range(len(searchList)):\n",
    "    url_Pubmed_carc='https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=('+searchList[s]+'+AND+1990:2022/05/23[dp]+AND+(\\\"+_gene_+\\\"[ti]))&retmax=20'\n",
    "    url_Pubmed_title = urllib.parse.quote(url_Pubmed_carc, safe=\"/+?:=&\")\n",
    "\n",
    "    Out_DF_carc=pd.DataFrame(columns=[\"Gene name\",\"Pubs[title]\",\"Pubs[title/abstract]\", \"Number of Grants[title/abstract]\", \"Award Amount[title/abstract]\", \"Number of Grants[title]\", \"Award Amount[title]\", \"Synonyms\"])\n",
    "    PM_Tiab_raw=destination[s]+\"\\\\NCBI PubMed\\\\Title and abstract data\\\\\"\n",
    "    PM_Ti_raw=destination[s]+\"\\\\NCBI PubMed\\\\Title only data\\\\\"\n",
    "    NIH_Ti_raw=destination[s]+\"\\\\NIH RePORTER\\\\Title only data\\\\\"\n",
    "    NIH_Tiab_raw=destination[s]+\"\\\\NIH RePORTER\\\\Title and abstract data\\\\\"\n",
    "    paramsDefault = {\n",
    "                \"criteria\": {\"date_added\":{\"from_date\": \"\",\"to_date\": \"2022-05-23\"},\n",
    "            \"advanced_text_search\": {\"operator\": \"advanced\", \"search_field\": \"projecttitle,abstracttext\",\"search_text\": str(cancerSynonymNIH+\" AND \")}},\n",
    "                \"include_fields\": [\"ApplId\", \"ProjectTitle\", \"AwardAmount\", \"DirectCostAmt\", \"IndirectCostAmt\",\"ProjectStartDate\", \"ProjectEndDate\"],\"offset\": 0, \"limit\": 500, }\n",
    "    Out_DF_carc=AccessPubMed_and_Reporter_Master_function(Out_DF_carc,url_Pubmed_carc,paramsDefault,PM_Ti_raw,PM_Tiab_raw,NIH_Ti_raw,NIH_Tiab_raw)\n",
    "    Out_DF_carc.index=Out_DF_carc[\"Gene name\"]\n",
    "    Out_DF_carc=Out_DF_carc.drop(columns=\"Gene name\")\n",
    "    Out_DF_carc.to_excel(pathList[s], engine=\"openpyxl\")\n",
    "    clear_output(wait=True)\n",
    "    if s==0:\n",
    "        FirstHalf=Out_DF_carc\n",
    "    else:\n",
    "        SecHalf=Out_DF_carc\n",
    "FirstHalf[\"Pubs[title]\"]=FirstHalf[\"Pubs[title]\"]+SecHalf[\"Pubs[title]\"]\n",
    "FirstHalf[\"Pubs[title/abstract]\"]=FirstHalf[\"Pubs[title/abstract]\"]+SecHalf[\"Pubs[title/abstract]\"]\n",
    "FirstHalf.to_excel(os.path.join(cwd_Output,\"NIH+PM_Data_Cancer_synonyms.xlsx\"), engine=\"openpyxl\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "1421c8c4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5406689261\n"
     ]
    }
   ],
   "source": [
    "df=pd.read_csv(os.path.join(cwd_Raw_Data_outputs,\"Carc_TP53 search 1.csv\"), sep=\",\", header=0)#path for manually downloaded TP53 title/abstract award amount\n",
    "df=df.iloc[:, :-1]\n",
    "df[\"Total\"]=df[\"Total Cost\"]+df[\"Total Cost (Sub Projects)\"]\n",
    "Total_TP53=0\n",
    "for i in df[\"Total\"]:\n",
    "    if (i!=\"\" and i!=\"  \"):#NIH stores empty values as spaces for some reason \n",
    "        Total_TP53=int(i)+Total_TP53\n",
    "df2=pd.read_csv(os.path.join(cwd_Raw_Data_outputs,\"Carc_TP53 search 2.csv\"), sep=\",\", header=0)#path for manually downloaded TP53 title/abstract award amount\n",
    "df2=df2.iloc[:, :-1]\n",
    "df2[\"Total\"]=df2[\"Total Cost\"]+df2[\"Total Cost (Sub Projects)\"]\n",
    "for i in df2[\"Total\"]:\n",
    "    if (i!=\"\" and i!=\"  \"):#NIH stores empty values as spaces for some reason \n",
    "        Total_TP53=int(i)+Total_TP53\n",
    "print(Total_TP53)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "foreign-distinction",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ABI1\n",
      "ABL1\n",
      "ABL2\n",
      "ACKR3\n",
      "ACSL3\n",
      "ACVR1\n",
      "ACVR2A\n",
      "AFDN\n",
      "AFF1\n",
      "AFF3\n",
      "AFF4\n",
      "AKT1\n",
      "AKT2\n",
      "ALK\n",
      "AMER1\n",
      "APC\n",
      "APOBEC3B\n",
      "AR\n",
      "ARHGAP26\n",
      "ARHGEF12\n",
      "ARID1A\n",
      "ARID1B\n",
      "ARID2\n",
      "ARNT\n",
      "ASPSCR1\n",
      "ASXL1\n",
      "ATF1\n",
      "ATIC\n",
      "ATM\n",
      "ATP1A1\n",
      "ATP2B3\n",
      "ATR\n",
      "ATRX\n",
      "AXIN1\n",
      "AXIN2\n",
      "B2M\n",
      "BAP1\n",
      "BARD1\n",
      "BAX\n",
      "BCL10\n",
      "BCL11A\n",
      "BCL11B\n",
      "BCL2\n",
      "BCL3\n",
      "BCL6\n",
      "BCL7A\n",
      "BCL9\n",
      "BCL9L\n",
      "BCOR\n",
      "BCORL1\n",
      "BCR\n",
      "BIRC3\n",
      "BLM\n",
      "BMPR1A\n",
      "BRAF\n",
      "BRCA1\n",
      "BRCA2\n",
      "BRD3\n",
      "BRD4\n",
      "BRIP1\n",
      "BTG1\n",
      "BTK\n",
      "BUB1B\n",
      "CACNA1D\n",
      "CALR\n",
      "CAMTA1\n",
      "CANT1\n",
      "CARD11\n",
      "CARS\n",
      "CASP8\n",
      "CBFA2T3\n",
      "CBFB\n",
      "CBL\n",
      "CBLB\n",
      "CBLC\n",
      "CCDC6\n",
      "CCNB1IP1\n",
      "CCND1\n",
      "CCND2\n",
      "CCND3\n",
      "CCNE1\n",
      "CD274\n",
      "CD74\n",
      "CD79A\n",
      "CD79B\n",
      "CDC73\n",
      "CDH1\n",
      "CDH11\n",
      "CDK12\n",
      "CDK4\n",
      "CDK6\n",
      "CDKN1B\n",
      "CDKN2A\n",
      "CDKN2C\n",
      "CDX2\n",
      "CEBPA\n",
      "CHCHD7\n",
      "CHD4\n",
      "CHEK2\n",
      "CIC\n",
      "CIITA\n",
      "CLIP1\n",
      "CLTC\n",
      "CLTCL1\n",
      "CNBP\n",
      "CNOT3\n",
      "CNTRL\n",
      "COL1A1\n",
      "COL2A1\n",
      "CREB1\n",
      "CREB3L1\n",
      "CREB3L2\n",
      "CREBBP\n",
      "CRLF2\n",
      "CRTC1\n",
      "CRTC3\n",
      "CSF3R\n",
      "CTCF\n",
      "CTNNB1\n",
      "CUX1\n",
      "CXCR4\n",
      "CYLD\n",
      "DAXX\n",
      "DCTN1\n",
      "DDB2\n",
      "DDIT3\n",
      "DDR2\n",
      "DDX10\n",
      "DDX3X\n",
      "DDX5\n",
      "DDX6\n",
      "DEK\n",
      "DICER1\n",
      "DNAJB1\n",
      "DNM2\n",
      "DNMT3A\n",
      "DROSHA\n",
      "EBF1\n",
      "EGFR\n",
      "EIF3E\n",
      "EIF4A2\n",
      "ELF4\n",
      "ELK4\n",
      "ELL\n",
      "EML4\n",
      "EP300\n",
      "EPAS1\n",
      "EPS15\n",
      "ERBB2\n",
      "ERBB3\n",
      "ERBB4\n",
      "ERC1\n",
      "ERCC2\n",
      "ERCC3\n",
      "ERCC4\n",
      "ERCC5\n",
      "ERG\n",
      "ESR1\n",
      "ETNK1\n",
      "ETV1\n",
      "ETV4\n",
      "ETV5\n",
      "ETV6\n",
      "EWSR1\n",
      "EXT1\n",
      "EXT2\n",
      "EZH2\n",
      "EZR\n",
      "FANCA\n",
      "FANCC\n",
      "FANCD2\n",
      "FANCE\n",
      "FANCF\n",
      "FANCG\n",
      "FAS\n",
      "FAT1\n",
      "FAT4\n",
      "FBXO11\n",
      "FBXW7\n",
      "FCGR2B\n",
      "FCRL4\n",
      "FES\n",
      "FEV\n",
      "FGFR1\n",
      "FGFR1OP\n",
      "FGFR2\n",
      "FGFR3\n",
      "FGFR4\n",
      "FH\n",
      "FHIT\n",
      "FIP1L1\n",
      "FLCN\n",
      "FLI1\n",
      "FLT3\n",
      "FLT4\n",
      "FOXA1\n",
      "FOXL2\n",
      "FOXO1\n",
      "FOXO3\n",
      "FOXO4\n",
      "FOXP1\n",
      "FSTL3\n",
      "FUBP1\n",
      "FUS\n",
      "GAS7\n",
      "GATA1\n",
      "GATA2\n",
      "GATA3\n",
      "GNA11\n",
      "GNAQ\n",
      "GNAS\n",
      "GOLGA5\n",
      "GOPC\n",
      "GPC3\n",
      "GPHN\n",
      "GRIN2A\n",
      "H3F3A\n",
      "H3F3B\n",
      "HERPUD1\n",
      "HEY1\n",
      "HIF1A\n",
      "HIP1\n",
      "HIST1H3B\n",
      "HIST1H4I\n",
      "HLA-A\n",
      "HLF\n",
      "HMGA1\n",
      "HMGA2\n",
      "HNF1A\n",
      "HNRNPA2B1\n",
      "HOOK3\n",
      "HOXA11\n",
      "HOXA13\n",
      "HOXA9\n",
      "HOXC11\n",
      "HOXC13\n",
      "HOXD11\n",
      "HOXD13\n",
      "HRAS\n",
      "HSP90AA1\n",
      "HSP90AB1\n",
      "IDH1\n",
      "IDH2\n",
      "IKBKB\n",
      "IKZF1\n",
      "IL2\n",
      "IL21R\n",
      "IL6ST\n",
      "IL7R\n",
      "IRF4\n",
      "IRS4\n",
      "ITK\n",
      "JAK1\n",
      "JAK2\n",
      "JAK3\n",
      "JUN\n",
      "KAT6A\n",
      "KAT6B\n",
      "KCNJ5\n",
      "KDM5A\n",
      "KDM5C\n",
      "KDM6A\n",
      "KDR\n",
      "KDSR\n",
      "KEAP1\n",
      "KIF5B\n",
      "KIT\n",
      "KLF4\n",
      "KLF6\n",
      "KLK2\n",
      "KMT2A\n",
      "KMT2C\n",
      "KMT2D\n",
      "KNL1\n",
      "KRAS\n",
      "KTN1\n",
      "LASP1\n",
      "LATS1\n",
      "LATS2\n",
      "LCK\n",
      "LEF1\n",
      "LIFR\n",
      "LMNA\n",
      "LMO1\n",
      "LMO2\n",
      "LPP\n",
      "LRIG3\n",
      "LRP1B\n",
      "LYL1\n",
      "LZTR1\n",
      "MAF\n",
      "MAFB\n",
      "MALT1\n",
      "MAML2\n",
      "MAP2K1\n",
      "MAP2K2\n",
      "MAP2K4\n",
      "MAP3K1\n",
      "MAP3K13\n",
      "MAPK1\n",
      "MAX\n",
      "MDM2\n",
      "MDM4\n",
      "MECOM\n",
      "MED12\n",
      "MEN1\n",
      "MET\n",
      "MITF\n",
      "MLF1\n",
      "MLH1\n",
      "MLLT1\n",
      "MLLT10\n",
      "MLLT11\n",
      "MLLT3\n",
      "MLLT6\n",
      "MN1\n",
      "MPL\n",
      "MRTFA\n",
      "MSH2\n",
      "MSH6\n",
      "MSI2\n",
      "MSN\n",
      "MTCP1\n",
      "MTOR\n",
      "MUC1\n",
      "MUTYH\n",
      "MYB\n",
      "MYC\n",
      "MYCL\n",
      "MYCN\n",
      "MYD88\n",
      "MYH11\n",
      "MYH9\n",
      "MYO5A\n",
      "MYOD1\n",
      "NAB2\n",
      "NBN\n",
      "NCOA1\n",
      "NCOA2\n",
      "NCOA4\n",
      "NCOR1\n",
      "NCOR2\n",
      "NDRG1\n",
      "NF1\n",
      "NF2\n",
      "NFATC2\n",
      "NFE2L2\n",
      "NFIB\n",
      "NFKB2\n",
      "NFKBIE\n",
      "NIN\n",
      "NKX2-1\n",
      "NONO\n",
      "NOTCH1\n",
      "NOTCH2\n",
      "NPM1\n",
      "NR4A3\n",
      "NRAS\n",
      "NRG1\n",
      "NSD1\n",
      "NSD2\n",
      "NSD3\n",
      "NT5C2\n",
      "NTRK1\n",
      "NTRK3\n",
      "NUMA1\n",
      "NUP214\n",
      "NUP98\n",
      "NUTM1\n",
      "NUTM2B\n",
      "NUTM2D\n",
      "OLIG2\n",
      "P2RY8\n",
      "PAFAH1B2\n",
      "PALB2\n",
      "PATZ1\n",
      "PAX3\n",
      "PAX5\n",
      "PAX7\n",
      "PAX8\n",
      "PBRM1\n",
      "PBX1\n",
      "PCM1\n",
      "PDCD1LG2\n",
      "PDE4DIP\n",
      "PDGFB\n",
      "PDGFRA\n",
      "PDGFRB\n",
      "PER1\n",
      "PHF6\n",
      "PHOX2B\n",
      "PICALM\n",
      "PIK3CA\n",
      "PIK3CB\n",
      "PIK3R1\n",
      "PIM1\n",
      "PLAG1\n",
      "PLCG1\n",
      "PML\n",
      "PMS2\n",
      "POLD1\n",
      "POLE\n",
      "POLQ\n",
      "POT1\n",
      "POU2AF1\n",
      "POU5F1\n",
      "PPARG\n",
      "PPFIBP1\n",
      "PPM1D\n",
      "PPP2R1A\n",
      "PPP6C\n",
      "PRCC\n",
      "PRDM1\n",
      "PRDM16\n",
      "PREX2\n",
      "PRF1\n",
      "PRKACA\n",
      "PRKAR1A\n",
      "PRRX1\n",
      "PSIP1\n",
      "PTCH1\n",
      "PTEN\n",
      "PTK6\n",
      "PTPN11\n",
      "PTPN13\n",
      "PTPRB\n",
      "PTPRC\n",
      "PTPRK\n",
      "PTPRT\n",
      "QKI\n",
      "RABEP1\n",
      "RAC1\n",
      "RAD21\n",
      "RAD51B\n",
      "RAF1\n",
      "RANBP2\n",
      "RAP1GDS1\n",
      "RARA\n",
      "RB1\n",
      "RBM10\n",
      "RBM15\n",
      "RECQL4\n",
      "REL\n",
      "RET\n",
      "RHOA\n",
      "RHOH\n",
      "RMI2\n",
      "RNF213\n",
      "RNF43\n",
      "ROS1\n",
      "RPL10\n",
      "RPL22\n",
      "RPL5\n",
      "RPN1\n",
      "RSPO2\n",
      "RSPO3\n",
      "RUNX1\n",
      "RUNX1T1\n",
      "SALL4\n",
      "SBDS\n",
      "SDC4\n",
      "SDHA\n",
      "SDHAF2\n",
      "SDHB\n",
      "SDHC\n",
      "SDHD\n",
      "SET\n",
      "SETBP1\n",
      "SETD2\n",
      "SF3B1\n",
      "SFPQ\n",
      "SFRP4\n",
      "SH2B3\n",
      "SH3GL1\n",
      "SIX1\n",
      "SLC34A2\n",
      "SLC45A3\n",
      "SMAD2\n",
      "SMAD3\n",
      "SMAD4\n",
      "SMARCA4\n",
      "SMARCB1\n",
      "SMARCD1\n",
      "SMARCE1\n",
      "SMO\n",
      "SND1\n",
      "SOCS1\n",
      "SOX2\n",
      "SPEN\n",
      "SPOP\n",
      "SRC\n",
      "SRSF2\n",
      "SRSF3\n",
      "SS18\n",
      "SS18L1\n",
      "SSX1\n",
      "SSX4\n",
      "STAG2\n",
      "STAT3\n",
      "STAT5B\n",
      "STAT6\n",
      "STIL\n",
      "STK11\n",
      "STRN\n",
      "SUFU\n",
      "SUZ12\n",
      "SYK\n",
      "TAF15\n",
      "TAL1\n",
      "TAL2\n",
      "TBL1XR1\n",
      "TBX3\n",
      "TCEA1\n",
      "TCF12\n",
      "TCF3\n",
      "TCF7L2\n",
      "TCL1A\n",
      "TENT5C\n",
      "TERT\n",
      "TET1\n",
      "TET2\n",
      "TFE3\n",
      "TFEB\n",
      "TFG\n",
      "TGFBR2\n",
      "TLX1\n",
      "TLX3\n",
      "TMEM127\n",
      "TMPRSS2\n",
      "TNFAIP3\n",
      "TNFRSF14\n",
      "TNFRSF17\n",
      "TOP1\n",
      "TP53\n",
      "TP63\n",
      "TPM3\n",
      "TPM4\n",
      "TPR\n",
      "TRA\n",
      "TRAF7\n",
      "TRIM24\n",
      "TRIM27\n",
      "TRIM33\n",
      "TRIP11\n",
      "TRRAP\n",
      "TSC1\n",
      "TSC2\n",
      "TSHR\n",
      "U2AF1\n",
      "UBR5\n",
      "USP6\n",
      "USP8\n",
      "VHL\n",
      "WAS\n",
      "WDCP\n",
      "WIF1\n",
      "WRN\n",
      "WT1\n",
      "WWTR1\n",
      "XPA\n",
      "XPC\n",
      "XPO1\n",
      "YWHAE\n",
      "ZBTB16\n",
      "ZFHX3\n",
      "ZMYM2\n",
      "ZNF331\n",
      "ZNF384\n",
      "ZNF521\n",
      "ZRSR2\n"
     ]
    }
   ],
   "source": [
    "#bonus search: AACR publications for all genes\n",
    "url_Pubmed_AACR='https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=((\\\"blood cancer discovery\\\"[ta]+OR+\\\"cancer discovery\\\"[ta]+OR+\\\"Cancer Epidemiology Biomarkers and Prevention\\\"[ta]+OR+\\\"cancer immunology research\\\"[ta]+OR+\\\"cancer prevention research\\\"[ta]+OR+\\\"cancer research\\\"[ta]+OR+\\\"clinical cancer research\\\"[ta]+OR+\\\"molecular cancer research\\\"[ta]+OR+\\\"molecular cancer therapeutics\\\"[ta]+OR+\\\"cancer research communications\\\"[ta])+AND+(\\\"+_gene_+\\\"[ti]))&retmax=20000'\n",
    "Out_DF_AACR=pd.DataFrame(columns=[\"Gene name\",\"Pubs[title]\",\"Pubs[title/abstract]\",\"Synonyms\"])\n",
    "for i in dfSynonym.index:\n",
    "    gene_terms=str(\"(\\\"+_gene_+\\\"[ti])\")\n",
    "    CT1_queryPM=str(\"\")\n",
    "    RawData=[]\n",
    "    RawData_Title=[]\n",
    "\n",
    "    Excluded=False\n",
    "    term = gene_terms.replace(\"_gene_\", str(i))\n",
    "    if i not in str(dfSynonym.loc[i][\"Removed Synonyms\"]):\n",
    "        CT1_queryPM = str(CT1_queryPM) + \"(\" + str(term)\n",
    "    else:\n",
    "        Excluded=True\n",
    "    Synonym_string=\"\"\n",
    "    if not isinstance(dfSynonym.loc[i][\"Synonyms\"],float):\n",
    "        CT1_queryPM = str(CT1_queryPM) + str(\"+OR+\")\n",
    "        if \"|\" in str(dfSynonym.loc[i][\"Synonyms\"]): \n",
    "            Synonyms_list = str(dfSynonym.loc[i][\"Synonyms\"]).split(\"|\")\n",
    "            for x in Synonyms_list:\n",
    "                if (x not in str(dfSynonym.loc[i][\"Removed Synonyms\"])) or (x in str(dfSynonym.loc[i][\"Added Synonyms\"])):\n",
    "                    igene_term = gene_terms.replace(\"_gene_\", str(x))\n",
    "                    Synonym_string=Synonym_string+str(x)+str(\"|\")\n",
    "                    CT1_queryPM = CT1_queryPM + str(igene_term) + str(\"+OR+\")\n",
    "            CT1_queryPM = str(CT1_queryPM[:(len(CT1_queryPM)) - 4]) + str(\")\")\n",
    "        else:\n",
    "            if str(dfSynonym.loc[i][\"Synonyms\"]) not in str(dfSynonym.loc[i][\"Removed Synonyms\"]):\n",
    "                Synonym_string=Synonym_string+ str(dfSynonym.loc[i][\"Synonyms\"])\n",
    "                igene_term = gene_terms.replace(\"_gene_\", str(dfSynonym.loc[i][\"Synonyms\"]))\n",
    "                CT1_queryPM = CT1_queryPM + str(igene_term) + str(\"+OR+\")\n",
    "                CT1_queryPM = str(CT1_queryPM[:(len(CT1_queryPM)) - 4]) + str(\")\")\n",
    "    else:\n",
    "        CT1_queryPM = str(CT1_queryPM) + str(\")\")\n",
    "    if Excluded==True:\n",
    "        CT1_queryPM =\"(\"+ str(CT1_queryPM[4:])\n",
    "        Excluded==False\n",
    "    url_Pubmed_title = url_Pubmed_AACR.replace(\"(\\\"+_gene_+\\\"[ti])\", CT1_queryPM)\n",
    "    url_Pubmed_title = urllib.parse.quote(url_Pubmed_title, safe=\"/+?:=&\")\n",
    "    PubTitleFreeze=os.path.join(cwd_Raw_Data_outputs,'Controls Raw Data\\\\AACR publication IDs\\\\Title only data\\\\' + str(i) + '_PM_title.txt')\n",
    "    if os.path.exists(PubTitleFreeze):\n",
    "        file1 = open(PubTitleFreeze,'r',encoding=\"utf-8\")\n",
    "        data_title = file1.readlines()\n",
    "        for line in data_title:\n",
    "            if \"<eSearchResult><Count>\" in str(line):  # find the line which tells the counts of all terms combined in article titles\n",
    "                get_count = str(line).split(\"Count>\")\n",
    "                title_count = int(get_count[1].replace(\"</\", \"\"))\n",
    "        file1.close()\n",
    "    else:   \n",
    "        data_title = urllib.request.urlopen(url_Pubmed_title)  # open url and get a copy of the data from the page\n",
    "        with open(PubTitleFreeze, 'w',encoding=\"utf-8\") as f:\n",
    "            for line in data_title:\n",
    "                f.write(str(line))  # write a freeze of the data from the page\n",
    "                f.write('\\n')\n",
    "                if \"<eSearchResult><Count>\" in str(line):  # find the line which tells the counts of all terms combined in article titles\n",
    "                    get_count = str(line).split(\"Count>\")\n",
    "                    title_count = int(get_count[1].replace(\"</\", \"\"))\n",
    "            f.write('\\n')\n",
    "            f.write(\"url used for search:\")\n",
    "            f.write(url_Pubmed_title)  # store the freeze and search URL as a txt file\n",
    "            f.write('\\n')\n",
    "            f.write(\"date accessed:\")\n",
    "            GetDate = str(datetime.now())\n",
    "            f.write(GetDate)\n",
    "            f.close()\n",
    "            time.sleep(1)\n",
    "    url_tiab = url_Pubmed_title.replace(\"%5Bti%5D\", \"%5Btiab%5D\")\n",
    "    PubTiabFreeze=os.path.join(cwd_Raw_Data_outputs,'Controls Raw Data\\\\AACR publication IDs\\\\Title and abstract data\\\\' + str(i) + '_PM_tiab.txt')\n",
    "    if os.path.exists(PubTiabFreeze):\n",
    "        print(\"file exists\")\n",
    "        file2 = open(PubTiabFreeze,'r',encoding=\"utf-8\")\n",
    "        data_tiab = file2.readlines()\n",
    "        for line in data_tiab:\n",
    "            if \"<eSearchResult><Count>\" in str(line):  # find the line which tells the counts of all terms combined in article titles\n",
    "                get_count = str(line).split(\"Count>\")\n",
    "                tiab_count = int(get_count[1].replace(\"</\", \"\"))\n",
    "        file2.close()\n",
    "    else:   \n",
    "        data_tiab = urllib.request.urlopen(url_tiab)  # open url and get a copy of the data from the page\n",
    "        with open(PubTiabFreeze, 'w',encoding=\"utf-8\") as f:\n",
    "            for line in data_tiab:\n",
    "                f.write(str(line))  # write a freeze of the data from the page\n",
    "                f.write('\\n')\n",
    "                if \"<eSearchResult><Count>\" in str(line):  # find the line which tells the counts of all terms combined in article titles\n",
    "                    get_count = str(line).split(\"Count>\")\n",
    "                    tiab_count = int(get_count[1].replace(\"</\", \"\"))\n",
    "            f.write('\\n')\n",
    "            f.write(\"url used for search:\")\n",
    "            f.write(url_tiab)  # store the freeze and search URL as a txt file\n",
    "            f.write('\\n')\n",
    "            f.write(\"date accessed:\")\n",
    "            GetDate = str(datetime.now())\n",
    "            f.write(GetDate)\n",
    "            f.close()\n",
    "            time.sleep(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "induced-affiliation",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=%28%28%22cancer%22%5Bti%5D%29+AND+%28%22+_gene_+%22%5Bti%5D%29%29&retmax=100000&retstart=0\n",
      "https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=%28%28%22cancer%22%5Bti%5D%29+AND+%28%22+_gene_+%22%5Bti%5D%29%29&retmax=100000&retstart=100000\n",
      "https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=%28%28%22cancer%22%5Bti%5D%29+AND+%28%22+_gene_+%22%5Bti%5D%29%29&retmax=100000&retstart=200000\n",
      "https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=%28%28%22cancer%22%5Bti%5D%29+AND+%28%22+_gene_+%22%5Bti%5D%29%29&retmax=100000&retstart=300000\n",
      "https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=%28%28%22cancer%22%5Bti%5D%29+AND+%28%22+_gene_+%22%5Bti%5D%29%29&retmax=100000&retstart=400000\n",
      "https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=%28%28%22cancer%22%5Bti%5D%29+AND+%28%22+_gene_+%22%5Bti%5D%29%29&retmax=100000&retstart=500000\n",
      "https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=%28%28%22cancer%22%5Bti%5D%29+AND+%28%22+_gene_+%22%5Bti%5D%29%29&retmax=100000&retstart=600000\n",
      "https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=%28%28%22cancer%22%5Bti%5D%29+AND+%28%22+_gene_+%22%5Bti%5D%29%29&retmax=100000&retstart=700000\n",
      "https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=%28%28%22cancer%22%5Bti%5D%29+AND+%28%22+_gene_+%22%5Bti%5D%29%29&retmax=100000&retstart=800000\n",
      "https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=%28%28%22cancer%22%5Bti%5D%29+AND+%28%22+_gene_+%22%5Bti%5D%29%29&retmax=100000&retstart=900000\n",
      "https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=%28%28%22cancer%22%5Bti%5D%29+AND+%28%22+_gene_+%22%5Bti%5D%29%29&retmax=100000&retstart=1000000\n",
      "https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=%28%28%22cancer%22%5Bti%5D%29+AND+%28%22+_gene_+%22%5Bti%5D%29%29&retmax=100000&retstart=1100000\n"
     ]
    }
   ],
   "source": [
    "#bonus:all pm IDs with cancer in title\n",
    "url_Pubmed_All_cancer='https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi?db=pubmed&term=((\"cancer\"[ti])+AND+(\\\"+_gene_+\\\"[ti]))&retmax=100000&retstart='\n",
    "url_Pubmed_All_cancer = urllib.parse.quote(url_Pubmed_All_cancer, safe=\"/+?:=&\")  # open url and get a copy of the data from the page\n",
    "RetStart=0\n",
    "with open(os.path.join(cwd,\"cancer_list.txt\"), 'w',encoding=\"utf-8\") as f:\n",
    "    f.write(str(\"list of all PM UIDs with cancer in title\"))\n",
    "    f.write('\\n')\n",
    "    while RetStart<1200000:\n",
    "        url_cancer_retstart=str(url_Pubmed_All_cancer)+str(RetStart)\n",
    "        data_title = urllib.request.urlopen(url_cancer_retstart)\n",
    "        for line in data_title:\n",
    "            if str(\"<Id>\") in str(line):\n",
    "                size = len(line)\n",
    "                line = line[1:size - 6]\n",
    "                f.write(str(line))  # write a freeze of the data from the page\n",
    "                f.write('\\n')\n",
    "        time.sleep(1)\n",
    "        RetStart+=100000\n",
    "    f.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "devoted-pavilion",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    Gene name  New Amount[title]  Old Amount[title]        dif\n",
      "533      TP53          732046462          363040347  369006115\n",
      "327       MYC          317595409           40547378  277048031\n",
      "138      EGFR          343558766          131723895  211834871\n",
      "55      BRCA1          188758350           47356752  141401598\n",
      "274      KRAS          180385133           65830196  114554937\n",
      "..        ...                ...                ...        ...\n",
      "534      TP63           20390244            9944028   10446216\n",
      "206     GATA2           12469064            2160498   10308566\n",
      "520      TET2           10195699                  0   10195699\n",
      "443       RET           23257841           13113814   10144027\n",
      "12       AKT2            9405809             149034    9256775\n",
      "\n",
      "[100 rows x 4 columns]\n"
     ]
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXgAAAERCAYAAABxZrw0AAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjUuMCwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8/fFQqAAAACXBIWXMAAAsTAAALEwEAmpwYAAAg2klEQVR4nO3de5hddX3v8fdnT3ZmQi4QJ1EhIQSNXGMSYAQ0HBQQ5WLT4xPsEbFKtU3FilirRKE9iD089UBtFQvaVKjUplhMRDgULz02FrFKnWASCOiRS4ABCkMIkEAymcv3/LHXhD2TndlrZvaavfbm83qe/WTvdf3OSvKd3/6u3/r9FBGYmVnzKdQ7ADMzy4YTvJlZk3KCNzNrUk7wZmZNygnezKxJOcGbmTWp3CV4SddLelrSvSm2nSdpnaRfStok6ayJiNHMrBHkLsED3wDOSLntnwI3RcQxwHuBa7MKysys0eQuwUfEHcCz5cskvV7S9yWtl/QTSUcMbg7MSN7vDzwxgaGameXapHoHkNIq4CMR8RtJJ1BqqZ8KfA74oaQLganA2+sXoplZvuQ+wUuaBrwF+LakwcWtyZ/nAt+IiC9KejPwTUkLI2KgDqGameVK7hM8pTLScxGxpMK6D5PU6yPiZ5LagFnA0xMXnplZPuWuBj9cRLwAPCzpPQAqWZysfhQ4LVl+JNAGdNclUDOznFHeRpOUdCPwNkot8aeAy4B/A74KHAgUgW9FxOclHQX8HTCN0g3XiyPih/WI28wsb3KX4M3MrDZyX6IxM7OxydVN1lmzZsX8+fPrHYaZWcNYv379MxExu9K6XCX4+fPn09nZWe8wzMwahqRH9rXOJRozsyblBG9m1qSc4M3MmlSuavCV9Pb20tXVxa5du+odSuba2tqYO3cuxWKx3qGYWRPIfYLv6upi+vTpzJ8/n7KxaJpORLB161a6uro49NBD6x2OmTWB3Jdodu3aRXt7e1MndwBJtLe3vyK+qZjZy7bu6GHjY8+xdUdPzY+d+xY80PTJfdAr5ec0s5JbNjzOyrWbKBYK9A4McOXyRSxbMqdmx899C97MrBlt3dHDyrWb2NU7wPaePnb1DnDx2k01bck7wacwbdq0Eddv2bKFhQsXjuqY559/PmvWrBlPWGbWwLq27aRYGJqCi4UCXdt21uwcTvBmZnUwd+YUegeGzk3UOzDA3JlTanaOpkzwWd202LFjB6eddhrHHnssb3zjG7nlllv2rOvr6+ODH/wgixYt4pxzzuGll14CYP369bz1rW/luOOO453vfCdPPvlkTWMys8bUPq2VK5cvoq1YYHrrJNqKBa5cvoj2aa3Vd06pIW6yjkaWNy3a2tq4+eabmTFjBs888wwnnngiy5YtA+DXv/411113HUuXLuVDH/oQ1157LRdddBEXXnght9xyC7Nnz+af//mfufTSS7n++utrEo+ZNbZlS+awdMEsurbtZO7MKTVN7tBkCb78psUuSl99Ll67iaULZtXkwkUEl1xyCXfccQeFQoHHH3+cp556CoCDDz6YpUuXAvD+97+fq6++mjPOOIN7772X008/HYD+/n4OPPDAccdhZs2jfVprzRP7oKZK8IM3LQaTO7x806IWF3D16tV0d3ezfv16isUi8+fP39NvfXgXR0lEBEcffTQ/+9nPxn1uM7PRyqwGL+lwSRvKXi9I+kRW54Psb1o8//zzvPrVr6ZYLLJu3ToeeeTlUTofffTRPYn8xhtv5KSTTuLwww+nu7t7z/Le3l42b95ck1jMzKrJLMFHxK8jYklELAGOA14Cbs7qfJD9TYvzzjuPzs5OOjo6WL16NUccccSedUceeSQ33HADixYt4tlnn+WCCy5g8uTJrFmzhpUrV7J48WKWLFnCf/zHf9QkFjOzaiZkTlZJ7wAui4ilI23X0dERwyf8uP/++znyyCNHdb6tO3oyu2mRtbH8vGb2yiVpfUR0VFo3UTX49wI3VlohaQWwAmDevHk1OVmWNy3MzBpF5v3gJU0GlgHfrrQ+IlZFREdEdMyeXXFaQTMzG4OJeNDpTODuiHhqrAeYiDJSHrxSfk4zmxgTkeDPZR/lmTTa2trYunVr0ye/wfHg29ra6h2KmTWJTGvwkvYDTgf+cKzHmDt3Ll1dXXR3d9cusJwanNHJzKwWMk3wEfES0D6eYxSLRc9wZGY2Bk052JiZmTnBm5k1LSd4M7Mm5QRvZtaknODNzJqUE7yZWZNygjcza1JO8GZmTcoJ3sysSTnBm5k1KSd4M7Mm5QRvZtaknODNzJqUE7yZWZNygjcza1JO8GZmTcoJ3sysSTnBm5k1qUwTvKQDJK2R9CtJ90t6c5bnMzOzl2U6JyvwZeD7EXGOpMnAfhmfz8zMEpkleEkzgJOB8wEiYjewO6vzmZnZUFmWaF4HdAN/L+mXkr4uaerwjSStkNQpqbO7uzvDcMzMXlmyTPCTgGOBr0bEMcCLwGeGbxQRqyKiIyI6Zs+enWE4ZmavLFkm+C6gKyLuSj6voZTwzcxsAmSW4CPiv4DHJB2eLDoNuC+r85mZ2VBZ96K5EFid9KB5CPi9jM9nZmaJTBN8RGwAOrI8h5mZVeYnWc3MmpQTvJlZk3KCNzNrUk7wZmZNygnezKxJOcGbmTUpJ3gzsyblBG9m1qSc4M3MmpQTvJlZk3KCNzNrUk7wZmZNygnezKxJOcGbmTWpEYcLlnRrimM8GxHn1yYcMzOrlWrjwR8J/P4I6wVcU7twzMysVqol+Esj4t9H2kDS5TWMx8zMamTEGnxE3FT+WdLUatuYmVk+pLrJKuktku4D7k8+L5Z0bYr9tki6R9IGSZ3jjNXMzEYh7Zysfw28E7gVICI2Sjo55b6nRMQzYwnOzMzGLnU3yYh4bNii/hrHYmZmNZQ2wT8m6S1ASJos6VMk5ZoqAvihpPWSVlTaQNIKSZ2SOru7u1OGY2Zm1aRN8B8B/giYA3QBS5LP1SyNiGOBM4E/qlTWiYhVEdERER2zZ89OGY6ZmVWTqgaf1NDPG+3BI+KJ5M+nJd0MHA/cMdrjmJnZ6FV7kvUrlMosFUXEx0fYdypQiIjtyft3AJ8fa6BmZjY61Vrw4+na+BrgZkmD5/mniPj+OI5nZmajMGKCj4gbACS9JyK+Xb5O0nuq7PsQsHjcEZqZ2Zikvcn62ZTLzMwsJ6rV4M8EzgLmSLq6bNUMoC/LwMzMbHyq1eCfoFSHXwasL1u+HfjjrIIyM7Pxq1aD3whslLQ6ItxiNzNrINVKNDdFxO8Av5S0V3fJiFiUWWRmZjYu1Uo0FyV/vivrQMzMrLaqjQf/ZPL2oxHxSPkL+Gj24ZmZ2Vil7SZ5eoVlZ9YyEDMzq61qNfgLKLXUXydpU9mq6cBPswzMzMzGp1oN/p+A7wF/AXymbPn2iHg2s6jMzGzcqnWTfB54Hjh3YsIxM7NaGbEGL+nuagdIs42ZmU28aiWaI5Pau6g8bLCA/WselZmZjVu1BH9EimN4blYzsxyqVoN/BEDSNyPid8vXVVpmZmb5kbYf/NHlHyS1AMfVPhwzM6uVajdZPytpO7BI0gvJazvwNHDLhERoZmZjUm2ogr+IiOnAVRExI3lNj4j2iPCEH2ZmOVbtJisAEfFZSXOAQ8r3iYg7qu2blHM6gccjwoOWmZlNkFQJXtIXgPcC9/Fyr5kAqiZ4SiNS3k9pFigzM5sgqRI88G7g8IjoGc3BJc0FzgauAD45ytjMzGwc0vaieQgojuH4XwIuBgb2tYGkFZI6JXV2d3eP4RRmZlZJ2hb8S8AGST8C9rTiI+Lj+9pB0ruApyNivaS37Wu7iFgFrALo6Oio9LSsmZmNQdoEf2vyGo2lwDJJZwFtwAxJ/xgR7x/lcczMbAzS9qK5YbQHTrpRfhYgacF/ysndzGzipO1F8zAVBhuLiNfVPCIzM6uJtCWajrL3bcB7gFelPUlE/Bj4ceqozMxs3FL1oomIrWWvxyPiS8Cp2YZmZmbjkbZEc2zZxwKlFv30TCIyM7OaSFui+WLZ+z5gC/A7NY/GzMxqJm0vmlOyDsTMzGorVQ1e0v6S/mrwiVNJX5TkqfrMzHIs7VAF1wPbKZVlfgd4Afj7rIIyM7PxS1uDf31ELC/7fLmkDRnEY68gW3f00LVtJ3NnTqF9Wmu9wzFrOmkT/E5JJ0XEnQCSlgI7swvLmt0tGx5n5dpNFAsFegcGuHL5IpYtmVPvsMyaStoEfwFwQ1J3F/AscH5WQVlz27qjh5VrN7Grd4BdyUCjF6/dxNIFs9ySN6uhtL1oNgCLJc1IPr+QZVDW3Lq27aRYKOxJ7gDFQoGubTud4M1qKO2DTgcAHwDmA5MkASMPF2y2L3NnTqF3YOgUAb0DA8ydOaVOEZk1p7S9aG6nlNzvAdaXvcxGrX1aK1cuX0RbscD01km0FQtcuXyRW+9mNZa2Bt8WEZ5yz2pm2ZI5LF0wy71ozDKUNsF/U9IfALcxdEanZzOJqkG529/otE9r9XUyy1DaBL8buAq4lJfHhQ/A48En3O3PzPImbYL/JLAgIp7JMphG5W5/ZpZHaW+ybqY08bZVMNjtr9xgtz8zs3pJ24LvBzZIWsfQGry7SeJuf2aWT2kT/HeTV7m95mgtJ6kNuANoTc6zJiIuG2V8DWGw29/Fw2rwLs+YWT2lfZL1hvLPkg4G3ltltx7g1IjYIakI3CnpexHx87GFmm/u9mdmeZO2BY+kWZQm2z4XmAPcPNL2ERHAjuRjMXmN2OpvdO72Z2Z5MmKClzQdeDfwPuAwSkn9dRExN83BJbVQeuJ1AXBNRNxVYZsVwAqAefPmjSp4MzPbt2q9aJ4GPgxcQWlM+D+h1Cc+lYjoj4glwFzgeEkLK2yzKiI6IqJj9uzZ6SM3M7MRVUvwlwBtwFeBz0p6/VhOEhHPAT8GzhjL/mZmNnojJviI+OuIOAFYRmkc+O8CB0laKemwkfaVNDsZhRJJU4C3A7+qRdBmZlZdqgedIuKhiLgiIt4IvAnYH/held0OBNZJ2gT8AvjXiLhtXNGamVlqqXvRDIqIeygNG3xJle02AceMMS4zMxuntEMVmJlZg3GCNzNrUqkSvKRTJe2XdTBmZlY7aWvw5wNfk7QV+EnyujMitmUVmJmZjU/asWg+ACDpIOAc4BrgoLT7m5nZxEuVoCW9H/hvwBuBZ4C/odSKNzOznErbAv8S8CDwNWBdRGzJKiAzM6uNtA86zQI+RGnYgisk/aekb2YamZmZjUvaXjQzgHnAIcB8Sk+yDoy0j5mZ1VfaEs2dZa+/iYiu7EIyM7NaSNuLZhGApKkR8WK2IZmZWS2kLdG8WdJ9wP3J58WSrs00MjMzG5e0QxV8CXgnsBUgIjYCJ2cUk5mZ1UDqsWgi4rFhi/prHIuZmdVQ2pusj0l6CxCSJgMfJynXmJlZPqVtwX8E+CNgDtAFLEk+m5lZTqXtRfMMcF7GsVgdbd3RQ9e2ncydOYX2aa31DsfMamDEBC/pf46wOiLiz2scj9XBLRseZ+XaTRQLBXoHBrhy+SKWLZlT77DMbJyqlWherPAC+DCwcqQdJR0saZ2k+yVtlnTRuKO1mtu6o4eVazexq3eA7T197Ood4OK1m9i6o6feoZnZOI3Ygo+ILw6+lzQduAj4PeBbwBf3tV+iD/iTiLg72Xe9pH+NiPvGGbPVUNe2nRQLBXaVjTxRLBTo2rbTpRqzBlf1JqukV0n6X8AmSr8Qjo2IlRHx9Ej7RcSTEXF38n47pV43/t6fM3NnTqF3YOiwQr0DA8ydOaVOEZlZrYyY4CVdBfwC2A68MSI+N5ZZnCTNB44B7qqwboWkTkmd3d3doz107m3d0cPGx57LbcmjfVorVy5fRFuxwPTWSbQVC1y5fJFb72ZNQBGx75XSANBDqdxSvqEo3WSdUfUE0jTg34ErIuI7I23b0dERnZ2daeJuCI1089K9aMwak6T1EdFRaV21GnzqJ133ceIisBZYXS25N5vym5eD9e2L125i6YJZo0qgE5V426e1OrGbNZnM5lSVJOA64P6I+KuszpNXtbh52UjfAMwsf8bVQq9iKfC7wKmSNiSvszI8X66M9+aluy+a2XhlluAj4s6IUEQsioglyev2rM6XN+O9eTn4DaDc4DcAM7M0MivRGCxbMoelC2aNuoa+dUcPz+/cze7+oQN2uvuimY2GE3zGRnvzsrzuPhAwqQBTipP21OB9I9TM0nKCz5FKPW9aJxW45rxjOfqgGU7uZjYqWd5ktVGqVHef3FJg/ylFJ3czGzUn+Byp1POmp6+fqZNb6hSRmTUyJ/gcKe9509oiAAoF8a6/uZNbNzxedf+8D4tgZhPLNficWbZkDkcdOIOzvnInEOzqTfcUrB+KMrPh3ILPoRd399Pakr4PvB+KMrNKnOBzaLRPwfqhKDOrxAk+h0b7FKzHdDezSlyDz6nRPAU7+Avh4mE1eHetNHtlc4IfgzwO4TvWYRHMrHk5wY9Smt4q9Zo8w2O6m1m5pk7wtU60aSbxcHdFM8uLpkrw5Qn9zgeeqXmirTaJR61mcTIzq4WmSfDlLefd/QP0DwzQN8BeiRYYc6u+Wm+VWsziZGZWK02R4Cu1nIcrFgqsvutRrv3xA2Nu1VfrreLuimaWJ02R4Cu1nIfb3d/PNeseoKdvfOWTkXqruLuimeVJlpNuXw+8C3g6IhZmdR6o3HIutggRTCq00B8DfOyUN7Dqjofo6Rt/+WSk3irurmhmeZHlk6zfAM7I8Ph7tE9r5c/OPorJLWLq5BbaigXOfdPBSAUQgHjVtMkTVj5pn9bK4oMPcHI3s7rKctLtO4Bnszp+udU/f4TL/89mii2idyD45OmHcdP6Lnr6Bnhpdz89fQP8+W338WdnHzXmSbAr8fC8ZpZnda/BS1oBrACYN2/eqPdf/fNHuPS79wKwuz8A+Msf/JrJk/YefGvhnP356cpTq5ZP0vSfd393M8u7uif4iFgFrALo6OiI0ey7dUcPl992317LJxW0J9kPGizHVHvaM+2Tqu7vbmZ519CjSXZt28nkZOajcn0DwWW/NfpyTNpx1T08r5k1grq34Mdj7swp9A3s3ei/7LeO5rwTDuGMo187qt4slbpbFhCbn3iBkw+bPeS87u9uZnmXWQte0o3Az4DDJXVJ+nCtz1E+bvrU1hYmTypwxbsXct6Jh+xZP5reLJUS90u9/fzBP3QOmRN1tOO1m5nVgyJGVfbOVEdHR3R2do56v33dFB3LYGO3bnicT6/ZSE/f0OvSVizw05Wnjvv4Zma1JGl9RHRUWtfQJZpBlW6cDh2bpp+PnfIG3nfCvKoJetmSORywX5GP/OPdvLS7f8+2xUKBzU+8wP5TikNu1jqxm1leNWULfuuOHpb+739jV+/QckvrpAJXnVPqFTNSb5lK+08qQEuhwOQWd4s0s/xo6hZ8pUR9SPvUimPT9PQN8Ok1GylIXJyUYQa3+fSajRx14Axe3N3P3JlThowps7u/n4Eo7T841IG7RZpZ3jV0gq/UH/1T397IP/3+CXvdLB3U0xd86tt719h7+oIzvvwTphRb9vyiuO1jJ7HhsedoKxb47Hfupbe/b8/2HgbYzPKuoRN8pW6Nu/uDc79+F6cf9Wr+731PUVZG32NXX+Xk3zcQbO8pJfFP3rRhT0lmd/8Aff3uFmlmjaWhE/zcmVPY2du31/Le/uD2e55icosoKKjQVb6qvgHoGxgYMvrkoEkF3C3SzHKvoRM8MGLyHj5cQa20FAp7ZocyM8urhh6qYPMTL4ypdT65RbQVC3zgzfP2PKw0qQCFvUc92Mf+HpbAzPKvwVvw1bN7Afae50nito+dxILXTOei0w6ja9tOpk5u4eyv3LlXSWa/YoGXel1/N7PG09At+KMP2r/qNmctfA1tk4Y2zVtbCrw47O7rE8/vYnLL0MsxtbWFz//2Qq5490IPS2BmDaehW/DbXtxddZvb7n1qr2W7+/uZOrmFq3/0G65Z9xsmt7TQ09dP/7B6T/9AcMoRr6Z9WuuoBy4zM6u3hk7wGx57bkz79fYHZ179E3qTm7A9fUN74rS2CBU0pKXuYQnMrNE0dIKfNMYC00DAwAg9bPqBb33oeDoObR/bCczMcqCha/D/cs9/ZXLcvv7gfdf955Ahgs3MGk1DJ/iHurdnduzdfZVnczIzaxQNneBblLLj+ggmFeDsha+lWGHqP0/DZ2aNrKFr8K3FlnEf40v/4xjetfggHnhqO2d95U52l/WDd393M2tkDd2Cf3ZH9W6SI5lUgDe/vnQjdcFrpvOX53gaPjNrHpm24CWdAXwZaAG+HhFfqOXxH39hfPXxi047bEgCX7ZkDksXzHJ/dzNrCpkleEktwDXA6UAX8AtJt0bEfVmdczRaJxV43wnz9lru/u5m1iyyLNEcDzwQEQ9FxG7gW8BvZ3i+qoot2lN+ueocl1/MrLllWaKZAzxW9rkLOGH4RpJWACsA5s3bu0VdCyceOpNrzjuuFITLL2b2CpFlgq/Uh3Gvx0cjYhWwCkqTbtc6iEvOPIIVb339ns9O7Gb2SpFlgu8CDi77PBd4opYn2PKFs5n/mX+puO4v3r2Qdxz9Wid0M3vFyrIG/wvgDZIOlTQZeC9wa61PsuULZw/5fNEpr2PLF87m3BMOcXI3s1e0zFrwEdEn6WPADyh1k7w+IjZnca7hSd7MzDLuBx8RtwO3Z3kOMzOrrKGfZDUzs31zgjcza1JO8GZmTcoJ3sysSSmi5s8WjZmkbuCRMe4+C3imhuFkxXHWViPE2QgxguOstYmK85CImF1pRa4S/HhI6oyIjnrHUY3jrK1GiLMRYgTHWWt5iNMlGjOzJuUEb2bWpJopwa+qdwApOc7aaoQ4GyFGcJy1Vvc4m6YGb2ZmQzVTC97MzMo4wZuZNamGSvCSzpD0a0kPSPpMhfWSdHWyfpOkY3Ma59skPS9pQ/L6n3WK83pJT0u6dx/r83I9q8VZ9+sp6WBJ6yTdL2mzpIsqbFP365kyzjxczzZJ/ylpYxLn5RW2ycP1TBNn/a5nRDTEi9KQww8CrwMmAxuBo4ZtcxbwPUqzSZ0I3JXTON8G3JaDa3oycCxw7z7W1/16poyz7tcTOBA4Nnk/Hfh/Of33mSbOPFxPAdOS90XgLuDEHF7PNHHW7Xo2Ugs+zSTevw38Q5T8HDhA0oE5jDMXIuIO4NkRNsnD9UwTZ91FxJMRcXfyfjtwP6V5icvV/XqmjLPukmu0I/lYTF7De4Tk4XqmibNuGinBV5rEe/g/zDTbZC1tDG9OvtZ9T9LRExPaqOXheqaVm+spaT5wDKXWXLlcXc8R4oQcXE9JLZI2AE8D/xoRubyeKeKEOl3PRkrwaSbxTjXRd8bSxHA3pfEjFgNfAb6bdVBjlIfrmUZurqekacBa4BMR8cLw1RV2qcv1rBJnLq5nRPRHxBJK8zkfL2nhsE1ycT1TxFm369lICT7NJN6ZT/SdQtUYIuKFwa91UZr1qihp1sSFmFoermdVebmekoqUkubqiPhOhU1ycT2rxZmX61kWz3PAj4Ezhq3KxfUctK8463k9GynBp5nE+1bgA8nd9ROB5yPiybzFKem1kpS8P57S38PWCY4zjTxcz6rycD2T818H3B8Rf7WPzep+PdPEmZPrOVvSAcn7KcDbgV8N2ywP17NqnPW8npnOyVpLsY9JvCV9JFn/NUrzv54FPAC8BPxeTuM8B7hAUh+wE3hvJLfbJ5KkGynd4Z8lqQu4jNJNotxcz5Rx5uF6LgV+F7gnqccCXALMK4szD9czTZx5uJ4HAjdIaqGUEG+KiNvy9v89ZZx1u54eqsDMrEk1UonGzMxGwQnezKxJOcGbmTUpJ3gzsyblBG9mVieqMpDesG3nqTRQ3C+TwdXOqraPE7yZWf18g70f4NqXP6XUDfMYSs/XXFttByd4qytJcyXdIuk3kh6U9OXkAbHBYVZv28d+W/b1NKCkYySFpHdmGXs1ks6XdFDZ5y2S7pHUkXz+hKT9ytbfLumA5PXRsuXzq7XwJL1epaFod4y0neVLpYH0kr/L70taL+knko4Y3ByYkbzfnxRP7TrBW90kT/d9B/huRLwBOAyYBlwxzkOfC9yZ/FlP5wMHDVt2SkR0Ju8/AexJ8BFxVvK4+wHARxmFiHgwGQ/FGt8q4MKIOA74FC+31D8HvD952O924MJqB3KCt3o6FdgVEX8PpUGbgD8GPlTesgWQ1C7ph0n98W+pPNDU4C+Ncygl13dIakuWz5f0K0lfl3SvpNWS3i7pp8m3h+OT7V4l6btJjfPnkhYlyz8n6VNl57k3OeZ8lSbP+DuVJnz4oaQpks4BOoDVSct6yrA4P04p+a+TtC5ZNvit5AvAYIv8qmH7tUi6StIvkhj/cCwX3vJJpUHg3gJ8O3nS+G8pPS0LpQbLNyJiLqUneL8pacQc7gRv9XQ0sL58QTKy4aPAgmHbXgbcmdQfbyV5tL6CpcDDEfEgpYGfym9ELQC+DCwCjgDeB5xEqZV0SbLN5cAvI2JRsuwfUvwcbwCuiYijgeeA5RGxBugEzouIJRGxc9jPeTWlr9inRMQpw473GeDBZL9PD1v3YUpjrrwJeBPwB5IOTRGjNYYC8Fzydz/4OjJZ92HgJoCI+BnQBow4aJkTvNWTqDy8a6XlJwP/CBAR/wJs28cxz6U0yQrJn+Vlmocj4p6IGAA2Az9KxgS5B5ifbHMS8M3kPP8GtEvav8rP8XBEbEjery87VhbeQWmArQ2UxnFvp/QLxppA0sB5WNJ7YM+0hIuT1Y8CpyXLj6SU4LtHOl7DDDZmTWkzsLx8gaQZlIaAfZBS8io34sBJyYBPy4Flki6l9IuiXdL0ZJOess0Hyj4P8PL/hX2NMd7H0AZRW9n78uP2A0PKMTUmSvXZH2R4Dpsg+xhI7zzgq5L+lNKget+iNPXnnwB/J+mPKf2bPL/aoGVuwVs9/QjYT9IHYE+C/iKlOuNLw7a9g9I/fCSdCcyscLy3Axsj4uCImB8Rh1Aa9/y/jyKm8vO8DXgmaVVtoTQvLCpN7pymLLKd0ryno10/0n4/oDQyYTGJ5TBJU1PEYjkUEedGxIERUYyIuRFxXUQ8HBFnRMTiiDgqIj6fbHtfRCxNli+JiB9WO74TvNVN0vp4N/AeSb+hNAH0Ll6uh5e7HDhZ0t2UyhSPVtjmXODmYcvWUqq1p/U5oEPSJko3Oz9YdpxXJaWRC5JYq/kG8LVKN1kTq4DvDd5kHRQRW4GfJjdyrxq2z9eB+4C7k66Tf4u/ids+eLhgswkiaQvQERHPZHiOHRExLavjW2NxC95s4nQDPxp80KmWBh90Ap6q9bGtcbkFb2bWpNyCNzNrUk7wZmZNygnezKxJOcGbmTWp/w9+YudRDo7d9AAAAABJRU5ErkJggg==",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "dfCompare=pd.read_excel(os.path.join(cwd_Output,\"aacr_dif.xlsx\"), engine=\"openpyxl\")\n",
    "dfCompare=dfCompare[[\"Gene name\",\"New Amount[title]\",\"Old Amount[title]\"]]\n",
    "dfCompare[\"dif\"]=dfCompare[\"New Amount[title]\"]-dfCompare[\"Old Amount[title]\"]\n",
    "dfCompare=dfCompare.sort_values(by=\"dif\", ascending=False)\n",
    "print(dfCompare.head(100))\n",
    "dfCompare.plot.scatter(x='Old Amount[title]', y='New Amount[title]',label = 'label')\n",
    "plt.show()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}