pull_paper_metadata.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import glob\n",
    "import pandas as pd\n",
    "import urllib\n",
    "import requests\n",
    "import xml.etree.ElementTree as ET\n",
    "import time\n",
    "import numpy as np\n",
    "from itertools import zip_longest\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def grouper(iterable, n, fillvalue=None):\n",
    "    args = [iter(iterable)] * n\n",
    "    return zip_longest(*args, fillvalue=fillvalue)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/riddleta/ac_knowl/ac_knowl/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3049: DtypeWarning: Columns (1) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_csv('/home/riddleta/ac_knowl/output/file_index.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>file</th>\n",
       "      <th>pmcid</th>\n",
       "      <th>paper_number</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/home/riddleta/ac_knowl/output/full_texts/pape...</td>\n",
       "      <td>3972225</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>/home/riddleta/ac_knowl/output/full_texts/pape...</td>\n",
       "      <td>3972226</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>/home/riddleta/ac_knowl/output/full_texts/pape...</td>\n",
       "      <td>3972227</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>/home/riddleta/ac_knowl/output/full_texts/pape...</td>\n",
       "      <td>3972228</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>/home/riddleta/ac_knowl/output/full_texts/pape...</td>\n",
       "      <td>3972229</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                file    pmcid  paper_number\n",
       "0  /home/riddleta/ac_knowl/output/full_texts/pape...  3972225             0\n",
       "1  /home/riddleta/ac_knowl/output/full_texts/pape...  3972226             1\n",
       "2  /home/riddleta/ac_knowl/output/full_texts/pape...  3972227             2\n",
       "3  /home/riddleta/ac_knowl/output/full_texts/pape...  3972228             3\n",
       "4  /home/riddleta/ac_knowl/output/full_texts/pape...  3972229             4"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('api_key.txt', 'r') as infile:\n",
    "    line = infile.readlines()\n",
    "    api_key = line[0][10:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5846053*******5846376\n",
      "'fulljournalname'\n",
      "6450715*******6456894\n",
      "'fulljournalname'\n",
      "5390906*******5391162\n",
      "'fulljournalname'\n",
      "4775292*******4776336\n",
      "'fulljournalname'\n",
      "5929106*******5931393\n",
      "'fulljournalname'\n",
      "5822532*******5822787\n",
      "'fulljournalname'\n",
      "5819009*******4370912\n",
      "Expecting value: line 1 column 1 (char 0)\n",
      "4370913*******4371426\n",
      "Expecting value: line 1 column 1 (char 0)\n",
      "4107366*******4108450\n",
      "'fulljournalname'\n",
      "4015299*******4015605\n",
      "'fulljournalname'\n",
      "5131257*******5133195\n",
      "'fulljournalname'\n",
      "4156544*******4157118\n",
      "'fulljournalname'\n",
      "5727783*******5728061\n",
      "'fulljournalname'\n",
      "4643749*******4644007\n",
      "'fulljournalname'\n",
      "5669235*******5669591\n",
      "'fulljournalname'\n",
      "6373394*******6373698\n",
      "'fulljournalname'\n",
      "4023268*******4023626\n",
      "'fulljournalname'\n",
      "4020188*******4020463\n",
      "'fulljournalname'\n",
      "5641422*******5642984\n",
      "'fulljournalname'\n",
      "6458428*******6458657\n",
      "'fulljournalname'\n"
     ]
    }
   ],
   "source": [
    "mesh_labs = []\n",
    "mesh_ids = []\n",
    "grant_ids = []\n",
    "grant_agency = []\n",
    "journal_out = []\n",
    "pmid_out = []\n",
    "auths_out = []\n",
    "pmcid_out = []\n",
    "total_papes = 0\n",
    "for i in grouper(df.pmcid.tolist(), 200):\n",
    "    try:\n",
    "        i = list(map(str, i)) #ensure strings\n",
    "        pth = 'https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esummary.fcgi?db=pmc&id=' + str(','.join(i)) + '&retmode=json&api_key=' + str(api_key)\n",
    "        r = requests.get(pth)\n",
    "        r_out = r.json()\n",
    "        jr=[] #store local group results\n",
    "        pmr=[]\n",
    "        ar=[]\n",
    "        pmcr=[]\n",
    "        for j in r_out['result']['uids']:\n",
    "            jr.append(r_out['result'][str(j)]['fulljournalname'])\n",
    "            pmr.append([art_id['value'] for art_id in r_out['result'][str(j)]['articleids'] if art_id['idtype']=='pmid'][0])\n",
    "            ar.append(r_out['result'][str(j)]['authors'])\n",
    "            pmcr.append(j)\n",
    "\n",
    "\n",
    "        journal_out.extend(jr) #build up lists for writing\n",
    "        pmid_out.extend(pmr)\n",
    "        auths_out.extend(ar)\n",
    "        pmcid_out.extend(pmcr)\n",
    "\n",
    "        out_df = pd.DataFrame({'journal':journal_out,\n",
    "                          'pmid':pmid_out,\n",
    "                          'auths':auths_out,\n",
    "                          'pmcid':pmcid_out})\n",
    "        if out_df.shape[0]>100000:\n",
    "            total_papes = total_papes + out_df.shape[0]\n",
    "            fn = 'grant_id_'\n",
    "            out_df.to_csv('/home/riddleta/ac_knowl/output/grant_identifiers/'+fn+str(total_papes)+'.csv', index=False)\n",
    "            journal_out = []\n",
    "            pmid_out = []\n",
    "            auths_out = []\n",
    "            pmcid_out = []\n",
    "\n",
    "        time.sleep(.11)\n",
    "    except Exception as e:\n",
    "        print(str(i[0])+'*******'+str(i[199]))\n",
    "        print(e)\n",
    "        time.sleep(.11)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_papes = total_papes + out_df.shape[0]\n",
    "fn = 'grant_id_'\n",
    "out_df.to_csv('/home/riddleta/ac_knowl/output/grant_identifiers/'+fn+str(total_papes)+'.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2544923"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_papes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(39924, 4)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "out_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "li = []\n",
    "all_files = glob.glob('/home/riddleta/ac_knowl/output/grant_identifiers/*')\n",
    "for filename in all_files:\n",
    "    tempdat = pd.read_csv(filename, index_col=None, header=0)\n",
    "    li.append(tempdat)\n",
    "\n",
    "frame = pd.concat(li, axis=0, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2544923, 4)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(316144, 3)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stragglers = df[~df.pmcid.isin(frame.pmcid)]\n",
    "stragglers.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5819607*******4371060\n",
      "('Connection broken: IncompleteRead(0 bytes read)', IncompleteRead(0 bytes read))\n"
     ]
    }
   ],
   "source": [
    "mesh_labs = []\n",
    "mesh_ids = []\n",
    "grant_ids = []\n",
    "grant_agency = []\n",
    "journal_out = []\n",
    "pmid_out = []\n",
    "auths_out = []\n",
    "pmcid_out = []\n",
    "#total_papes = 2504999\n",
    "for i in grouper(stragglers.pmcid.tolist(), 200):\n",
    "    try:\n",
    "        i = list(map(str, i)) #ensure strings\n",
    "        pth = 'https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esummary.fcgi?db=pmc&id=' + str(','.join(i)) + '&retmode=json&api_key=' + str(api_key)\n",
    "        r = requests.get(pth)\n",
    "        r_out = r.json()\n",
    "        jr=[] #store local group results\n",
    "        pmr=[]\n",
    "        ar=[]\n",
    "        pmcr=[]\n",
    "        for j in r_out['result']['uids']:\n",
    "            try:\n",
    "                jr.append(r_out['result'][str(j)]['fulljournalname'])\n",
    "                pmr.append([art_id['value'] for art_id in r_out['result'][str(j)]['articleids'] if art_id['idtype']=='pmid'][0])\n",
    "                ar.append(r_out['result'][str(j)]['authors'])\n",
    "                pmcr.append(j)\n",
    "            except:\n",
    "                jr.append(np.nan)\n",
    "                pmr.append(np.nan)\n",
    "                ar.append(np.nan)\n",
    "                pmcr.append(j)\n",
    "\n",
    "\n",
    "        journal_out.extend(jr) #build up lists for writing\n",
    "        pmid_out.extend(pmr)\n",
    "        auths_out.extend(ar)\n",
    "        pmcid_out.extend(pmcr)\n",
    "\n",
    "        out_df = pd.DataFrame({'journal':journal_out,\n",
    "                          'pmid':pmid_out,\n",
    "                          'auths':auths_out,\n",
    "                          'pmcid':pmcid_out})\n",
    "        if out_df.shape[0]>100000:\n",
    "            total_papes = total_papes + out_df.shape[0]\n",
    "            fn = 'grant_id_'\n",
    "            out_df.to_csv('/home/riddleta/ac_knowl/output/grant_identifiers/'+fn+str(total_papes)+'.csv', index=False)\n",
    "            journal_out = []\n",
    "            pmid_out = []\n",
    "            auths_out = []\n",
    "            pmcid_out = []\n",
    "\n",
    "        time.sleep(.11)\n",
    "    except Exception as e:\n",
    "        print(str(i[0])+'*******'+str(i[199]))\n",
    "        print(e)\n",
    "        time.sleep(.11)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_papes = total_papes + out_df.shape[0]\n",
    "fn = 'grant_id_'\n",
    "out_df.to_csv('/home/riddleta/ac_knowl/output/grant_identifiers/'+fn+str(total_papes)+'.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "li = []\n",
    "all_files = glob.glob('/home/riddleta/ac_knowl/output/grant_identifiers/*')\n",
    "for filename in all_files:\n",
    "    tempdat = pd.read_csv(filename, index_col=None, header=0)\n",
    "    li.append(tempdat)\n",
    "\n",
    "frame = pd.concat(li, axis=0, ignore_index=True)\n",
    "frame = frame.drop_duplicates()\n",
    "frame['pmcid'] = frame.pmcid.astype('str')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2599324, 4)"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2599524, 3)"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['pmcid'] = df.pmcid.astype('str')\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(201, 3)"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stragglers = df[~df.pmcid.isin(frame.pmcid)]\n",
    "stragglers.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "unknown\n",
      "'unknown'\n"
     ]
    }
   ],
   "source": [
    "journal_out = []\n",
    "pmid_out = []\n",
    "auths_out = []\n",
    "pmcid_out = []\n",
    "for i in stragglers.pmcid.tolist():\n",
    "    try:\n",
    "        pth = 'https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esummary.fcgi?db=pmc&id=' + str(i) + '&retmode=json&api_key=' + str(api_key)\n",
    "        r = requests.get(pth)\n",
    "        r_out = r.json()\n",
    "        journal_out.append(r_out['result'][str(i)]['fulljournalname'])\n",
    "        pmid_out.append([art_id['value'] for art_id in r_out['result'][str(i)]['articleids'] if art_id['idtype']=='pmid'][0])\n",
    "        auths_out.append(r_out['result'][str(i)]['authors'])\n",
    "        pmcid_out.append(i)\n",
    "\n",
    "        time.sleep(.11)\n",
    "    except Exception as e:\n",
    "        print(str(i))\n",
    "        print(e)\n",
    "        journal_out.append(np.nan)\n",
    "        pmid_out.append(np.nan)\n",
    "        auths_out.append(np.nan)\n",
    "        pmcid_out.append(i)\n",
    "        time.sleep(.11)\n",
    "\n",
    "\n",
    "out_df = pd.DataFrame({'journal':journal_out,\n",
    "                  'pmid':pmid_out,\n",
    "                  'auths':auths_out,\n",
    "                  'pmcid':pmcid_out})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_papes = total_papes + out_df.shape[0]\n",
    "fn = 'grant_id_'\n",
    "out_df.to_csv('/home/riddleta/ac_knowl/output/grant_identifiers/'+fn+str(total_papes)+'.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Hit the grant API\n",
    "pull the mesh labels, ids, grant ids, and grant agency for each paper"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "li = []\n",
    "all_files = glob.glob('/home/riddleta/ac_knowl/output/pmid_pmcid_link/*')\n",
    "for filename in all_files:\n",
    "    tempdat = pd.read_csv(filename, index_col=None, header=0)\n",
    "    li.append(tempdat)\n",
    "\n",
    "frame = pd.concat(li, axis=0, ignore_index=True)\n",
    "frame['pmcid'] = frame.pmcid.astype('str')\n",
    "#frame['pmid'] = frame.pmid.astype('int')\n",
    "#frame['pmid'] = frame.pmid.astype('str')\n",
    "frame = frame.drop_duplicates()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    28674422\n",
       "1    28674426\n",
       "2    28674452\n",
       "3    28674872\n",
       "4    28674848\n",
       "Name: pmid2, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame['pmid2'] = frame.pmid.fillna(0).astype('int')\n",
    "frame.pmid2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2599525, 5)"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "23590562*******23504439\n",
      "('Connection broken: IncompleteRead(0 bytes read)', IncompleteRead(0 bytes read))\n",
      "30950790*******23256870\n",
      "undefined entity &nbsp;: line 28, column 94\n"
     ]
    }
   ],
   "source": [
    "#initialize some lists to hold data at the paper level\n",
    "paper_level_meshlabs = []\n",
    "paper_level_meshids = []\n",
    "paper_level_grant_ids = []\n",
    "paper_level_grant_agency = []\n",
    "paper_level_kw = []\n",
    "pmid_out = []\n",
    "#keep track of how many papers\n",
    "total_papes = 0\n",
    "fn = 'grant_id_'\n",
    "#for each group of 200 papers\n",
    "for i in grouper(frame.pmid2.tolist(), 200):\n",
    "    try:\n",
    "        i = list(map(str, i)) #ensure strings\n",
    "        pth = 'https://eutils.ncbi.nlm.nih.gov/entrez/eutils/efetch.fcgi?db=pubmed&id=' + str(','.join(i)) + '&retmode=xml&api_key=' + str(api_key)\n",
    "        #get the data\n",
    "        r = requests.get(pth)\n",
    "        root = ET.fromstring(r.content)\n",
    "        # for each paper in the group of 200\n",
    "        for paper in root.iter('PubmedArticle'):\n",
    "            #initialize some lists to hold the variable-length content from a paper\n",
    "            tmp_mesh_labs = []\n",
    "            tmp_mesh_ids = []\n",
    "            tmp_grant_ids = []\n",
    "            tmp_grant_agency = []\n",
    "            tmp_kw = []\n",
    "            if paper.find('.//MeshHeading'):\n",
    "                for mesh in paper.iter('MeshHeading'):\n",
    "                    tmp_mesh_labs.append([j.text for j in mesh])\n",
    "                    tmp_mesh_ids.append([j.attrib['UI'] for j in mesh])\n",
    "            else:\n",
    "                tmp_mesh_labs.append(np.nan)\n",
    "                tmp_mesh_ids.append(np.nan)\n",
    "            if paper.find('.//KeywordList'):\n",
    "                for keyword in paper.iter('KeywordList'):\n",
    "                    tmp_kw.append([j.text for j in keyword])\n",
    "            else:\n",
    "                tmp_kw.append(np.nan)\n",
    "            if paper.find('.//Grant'):\n",
    "                for grant in paper.iter('Grant'):\n",
    "                    tmp_grant_ids.append([j.text for j in grant if j.tag=='GrantID'])\n",
    "                    tmp_grant_agency.append([j.text for j in grant if j.tag=='Agency'])\n",
    "            else:\n",
    "                tmp_grant_ids.append(np.nan)\n",
    "                tmp_grant_agency.append(np.nan)\n",
    "\n",
    "            paper_level_meshlabs.append(tmp_mesh_labs)\n",
    "            paper_level_meshids.append(tmp_mesh_ids)\n",
    "            paper_level_grant_ids.append(tmp_grant_ids)\n",
    "            paper_level_grant_agency.append(tmp_grant_agency)\n",
    "            paper_level_kw.append(tmp_kw)\n",
    "            pmid_out.append(paper[0][0].text)\n",
    "    \n",
    "        out_df = pd.DataFrame({'mesh':paper_level_meshlabs,\n",
    "                               'mesh_id':paper_level_meshids,\n",
    "                               'grant_ids':paper_level_grant_ids,\n",
    "                               'grant_agency':paper_level_grant_agency,\n",
    "                               'keyword':paper_level_kw,\n",
    "                               'pmid':pmid_out})\n",
    "        \n",
    "        if out_df.shape[0]>100000:\n",
    "            total_papes = total_papes + out_df.shape[0]\n",
    "            out_df.to_csv('/home/riddleta/ac_knowl/output/grant_identifiers/'+fn+str(total_papes)+'.csv', index=False)\n",
    "            paper_level_meshlabs = []\n",
    "            paper_level_meshids = []\n",
    "            paper_level_grant_ids = []\n",
    "            paper_level_grant_agency = []\n",
    "            paper_level_kw = []\n",
    "            pmid_out = []\n",
    "        \n",
    "        time.sleep(.11)\n",
    "            \n",
    "    except Exception as e:\n",
    "        print(str(i[0])+'*******'+str(i[199]))\n",
    "        print(e)\n",
    "        time.sleep(.11)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2502692"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "total_papes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_papes = total_papes + out_df.shape[0]\n",
    "out_df.to_csv('/home/riddleta/ac_knowl/output/grant_identifiers/'+fn+str(total_papes)+'.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "li = []\n",
    "all_files = glob.glob('/home/riddleta/ac_knowl/output/grant_identifiers/*')\n",
    "for filename in all_files:\n",
    "    tempdat = pd.read_csv(filename, index_col=None, header=0)\n",
    "    li.append(tempdat)\n",
    "\n",
    "done_dat = pd.concat(li, axis=0, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(75741, 5)"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stragglers = frame[~frame.pmid2.isin(done_dat.pmid)]\n",
    "stragglers.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "#initialize some lists to hold data at the paper level\n",
    "paper_level_meshlabs = []\n",
    "paper_level_meshids = []\n",
    "paper_level_grant_ids = []\n",
    "paper_level_grant_agency = []\n",
    "paper_level_kw = []\n",
    "pmid_out = []\n",
    "#keep track of how many papers\n",
    "total_papes = 0\n",
    "fn = 'grant_id_'\n",
    "#for each group of 200 papers\n",
    "for i in grouper(stragglers.pmid2.tolist(), 200):\n",
    "    try:\n",
    "        i = list(map(str, i)) #ensure strings\n",
    "        pth = 'https://eutils.ncbi.nlm.nih.gov/entrez/eutils/efetch.fcgi?db=pubmed&id=' + str(','.join(i)) + '&retmode=xml&api_key=' + str(api_key)\n",
    "        #get the data\n",
    "        r = requests.get(pth)\n",
    "        root = ET.fromstring(r.content)\n",
    "        # for each paper in the group of 200\n",
    "        for paper in root.iter('PubmedArticle'):\n",
    "            #initialize some lists to hold the variable-length content from a paper\n",
    "            tmp_mesh_labs = []\n",
    "            tmp_mesh_ids = []\n",
    "            tmp_grant_ids = []\n",
    "            tmp_grant_agency = []\n",
    "            tmp_kw = []\n",
    "            if paper.find('.//MeshHeading'):\n",
    "                for mesh in paper.iter('MeshHeading'):\n",
    "                    tmp_mesh_labs.append([j.text for j in mesh])\n",
    "                    tmp_mesh_ids.append([j.attrib['UI'] for j in mesh])\n",
    "            else:\n",
    "                tmp_mesh_labs.append(np.nan)\n",
    "                tmp_mesh_ids.append(np.nan)\n",
    "            if paper.find('.//KeywordList'):\n",
    "                for keyword in paper.iter('KeywordList'):\n",
    "                    tmp_kw.append([j.text for j in keyword])\n",
    "            else:\n",
    "                tmp_kw.append(np.nan)\n",
    "            if paper.find('.//Grant'):\n",
    "                for grant in paper.iter('Grant'):\n",
    "                    tmp_grant_ids.append([j.text for j in grant if j.tag=='GrantID'])\n",
    "                    tmp_grant_agency.append([j.text for j in grant if j.tag=='Agency'])\n",
    "            else:\n",
    "                tmp_grant_ids.append(np.nan)\n",
    "                tmp_grant_agency.append(np.nan)\n",
    "\n",
    "            paper_level_meshlabs.append(tmp_mesh_labs)\n",
    "            paper_level_meshids.append(tmp_mesh_ids)\n",
    "            paper_level_grant_ids.append(tmp_grant_ids)\n",
    "            paper_level_grant_agency.append(tmp_grant_agency)\n",
    "            paper_level_kw.append(tmp_kw)\n",
    "            pmid_out.append(paper[0][0].text)\n",
    "    \n",
    "        out_df = pd.DataFrame({'mesh':paper_level_meshlabs,\n",
    "                               'mesh_id':paper_level_meshids,\n",
    "                               'grant_ids':paper_level_grant_ids,\n",
    "                               'grant_agency':paper_level_grant_agency,\n",
    "                               'keyword':paper_level_kw,\n",
    "                               'pmid':pmid_out})\n",
    "        time.sleep(.11)\n",
    "            \n",
    "    except Exception as e:\n",
    "        print(str(i[0])+'*******'+str(i[199]))\n",
    "        print(e)\n",
    "        time.sleep(.11)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True     75348\n",
       "False      393\n",
       "Name: pmid2, dtype: int64"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Lots of these are just zero. missing data i guess\n",
    "pd.Series(stragglers.pmid2==0).value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "total_papes = total_papes + out_df.shape[0]\n",
    "out_df.to_csv('/home/riddleta/ac_knowl/output/grant_identifiers/'+fn+str(total_papes)+'.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "li = []\n",
    "all_files = glob.glob('/home/riddleta/ac_knowl/output/grant_identifiers/*')\n",
    "for filename in all_files:\n",
    "    tempdat = pd.read_csv(filename, index_col=None, header=0)\n",
    "    li.append(tempdat)\n",
    "\n",
    "done_dat = pd.concat(li, axis=0, ignore_index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# reformat grant-pmid pairing\n",
    "Now, I want a file that has a grant id, the grant agency, and pmid"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(921814, 6)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grant_funded = done_dat[done_dat.grant_ids!='[nan]']\n",
    "grant_funded.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mesh</th>\n",
       "      <th>mesh_id</th>\n",
       "      <th>grant_ids</th>\n",
       "      <th>grant_agency</th>\n",
       "      <th>keyword</th>\n",
       "      <th>pmid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>[nan]</td>\n",
       "      <td>[nan]</td>\n",
       "      <td>[['RC2 GM092561'], ['T32 GM083864']]</td>\n",
       "      <td>[['NIGMS NIH HHS'], ['NIGMS NIH HHS']]</td>\n",
       "      <td>[['gas chromatography', 'high performance liqu...</td>\n",
       "      <td>25789275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>[nan]</td>\n",
       "      <td>[nan]</td>\n",
       "      <td>[['R01 DA007606'], ['R01 DA035499']]</td>\n",
       "      <td>[['NIDA NIH HHS'], ['NIDA NIH HHS']]</td>\n",
       "      <td>[['blood-brain barrier', 'glutamate', 'methamp...</td>\n",
       "      <td>25788874</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>[['Adult'], ['Aged'], ['Biomarkers, Tumor', 'm...</td>\n",
       "      <td>[['D000328'], ['D000368'], ['D014408', 'Q00037...</td>\n",
       "      <td>[['G1000143'], ['MC_UU_12015/1'], ['MC_U106179...</td>\n",
       "      <td>[['Medical Research Council'], ['Medical Resea...</td>\n",
       "      <td>[nan]</td>\n",
       "      <td>25637171</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>[['Animals'], ['Birds', 'classification', 'gen...</td>\n",
       "      <td>[['D000818'], ['D001717', 'Q000145', 'Q000235'...</td>\n",
       "      <td>[['DP1 OD000448'], ['R01 HL087216']]</td>\n",
       "      <td>[['NIH HHS'], ['NHLBI NIH HHS']]</td>\n",
       "      <td>[['Avian genomes', 'Gene trees', 'Indels', 'Ph...</td>\n",
       "      <td>25741440</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>[['Health Facilities'], ['Humans'], ['Malaria'...</td>\n",
       "      <td>[['D006268'], ['D006801'], ['D008288', 'Q00017...</td>\n",
       "      <td>[['U19 AI089674'], ['U19AI089674']]</td>\n",
       "      <td>[['NIAID NIH HHS'], ['NIAID NIH HHS']]</td>\n",
       "      <td>[nan]</td>\n",
       "      <td>25366929</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 mesh  \\\n",
       "3                                               [nan]   \n",
       "6                                               [nan]   \n",
       "10  [['Adult'], ['Aged'], ['Biomarkers, Tumor', 'm...   \n",
       "11  [['Animals'], ['Birds', 'classification', 'gen...   \n",
       "21  [['Health Facilities'], ['Humans'], ['Malaria'...   \n",
       "\n",
       "                                              mesh_id  \\\n",
       "3                                               [nan]   \n",
       "6                                               [nan]   \n",
       "10  [['D000328'], ['D000368'], ['D014408', 'Q00037...   \n",
       "11  [['D000818'], ['D001717', 'Q000145', 'Q000235'...   \n",
       "21  [['D006268'], ['D006801'], ['D008288', 'Q00017...   \n",
       "\n",
       "                                            grant_ids  \\\n",
       "3                [['RC2 GM092561'], ['T32 GM083864']]   \n",
       "6                [['R01 DA007606'], ['R01 DA035499']]   \n",
       "10  [['G1000143'], ['MC_UU_12015/1'], ['MC_U106179...   \n",
       "11               [['DP1 OD000448'], ['R01 HL087216']]   \n",
       "21                [['U19 AI089674'], ['U19AI089674']]   \n",
       "\n",
       "                                         grant_agency  \\\n",
       "3              [['NIGMS NIH HHS'], ['NIGMS NIH HHS']]   \n",
       "6                [['NIDA NIH HHS'], ['NIDA NIH HHS']]   \n",
       "10  [['Medical Research Council'], ['Medical Resea...   \n",
       "11                   [['NIH HHS'], ['NHLBI NIH HHS']]   \n",
       "21             [['NIAID NIH HHS'], ['NIAID NIH HHS']]   \n",
       "\n",
       "                                              keyword      pmid  \n",
       "3   [['gas chromatography', 'high performance liqu...  25789275  \n",
       "6   [['blood-brain barrier', 'glutamate', 'methamp...  25788874  \n",
       "10                                              [nan]  25637171  \n",
       "11  [['Avian genomes', 'Gene trees', 'Indels', 'Ph...  25741440  \n",
       "21                                              [nan]  25366929  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grant_funded.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/riddleta/ac_knowl/ac_knowl/lib/python3.7/site-packages/ipykernel_launcher.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n",
      "/home/riddleta/ac_knowl/ac_knowl/lib/python3.7/site-packages/ipykernel_launcher.py:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "grant_funded['grant_ids_cleaned'] = grant_funded.grant_ids.apply(lambda x: re.sub(\"\\[|'\", '', x))\n",
    "grant_funded['grant_agency_cleaned'] = grant_funded.grant_agency.apply(lambda x: re.sub(\"\\[|'\", '', x))\n",
    "#re.sub(\"\\[|\\]|'\", '', grant_funded.grant_ids.iloc[0]).split(',')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>mesh</th>\n",
       "      <th>mesh_id</th>\n",
       "      <th>grant_ids</th>\n",
       "      <th>grant_agency</th>\n",
       "      <th>keyword</th>\n",
       "      <th>pmid</th>\n",
       "      <th>grant_ids_cleaned</th>\n",
       "      <th>grant_agency_cleaned</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>[nan]</td>\n",
       "      <td>[nan]</td>\n",
       "      <td>[['RC2 GM092561'], ['T32 GM083864']]</td>\n",
       "      <td>[['NIGMS NIH HHS'], ['NIGMS NIH HHS']]</td>\n",
       "      <td>[['gas chromatography', 'high performance liqu...</td>\n",
       "      <td>25789275</td>\n",
       "      <td>RC2 GM092561], T32 GM083864]]</td>\n",
       "      <td>NIGMS NIH HHS], NIGMS NIH HHS]]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>[nan]</td>\n",
       "      <td>[nan]</td>\n",
       "      <td>[['R01 DA007606'], ['R01 DA035499']]</td>\n",
       "      <td>[['NIDA NIH HHS'], ['NIDA NIH HHS']]</td>\n",
       "      <td>[['blood-brain barrier', 'glutamate', 'methamp...</td>\n",
       "      <td>25788874</td>\n",
       "      <td>R01 DA007606], R01 DA035499]]</td>\n",
       "      <td>NIDA NIH HHS], NIDA NIH HHS]]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>[['Adult'], ['Aged'], ['Biomarkers, Tumor', 'm...</td>\n",
       "      <td>[['D000328'], ['D000368'], ['D014408', 'Q00037...</td>\n",
       "      <td>[['G1000143'], ['MC_UU_12015/1'], ['MC_U106179...</td>\n",
       "      <td>[['Medical Research Council'], ['Medical Resea...</td>\n",
       "      <td>[nan]</td>\n",
       "      <td>25637171</td>\n",
       "      <td>G1000143], MC_UU_12015/1], MC_U106179471], 141...</td>\n",
       "      <td>Medical Research Council], Medical Research Co...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>[['Animals'], ['Birds', 'classification', 'gen...</td>\n",
       "      <td>[['D000818'], ['D001717', 'Q000145', 'Q000235'...</td>\n",
       "      <td>[['DP1 OD000448'], ['R01 HL087216']]</td>\n",
       "      <td>[['NIH HHS'], ['NHLBI NIH HHS']]</td>\n",
       "      <td>[['Avian genomes', 'Gene trees', 'Indels', 'Ph...</td>\n",
       "      <td>25741440</td>\n",
       "      <td>DP1 OD000448], R01 HL087216]]</td>\n",
       "      <td>NIH HHS], NHLBI NIH HHS]]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>[['Health Facilities'], ['Humans'], ['Malaria'...</td>\n",
       "      <td>[['D006268'], ['D006801'], ['D008288', 'Q00017...</td>\n",
       "      <td>[['U19 AI089674'], ['U19AI089674']]</td>\n",
       "      <td>[['NIAID NIH HHS'], ['NIAID NIH HHS']]</td>\n",
       "      <td>[nan]</td>\n",
       "      <td>25366929</td>\n",
       "      <td>U19 AI089674], U19AI089674]]</td>\n",
       "      <td>NIAID NIH HHS], NIAID NIH HHS]]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 mesh  \\\n",
       "3                                               [nan]   \n",
       "6                                               [nan]   \n",
       "10  [['Adult'], ['Aged'], ['Biomarkers, Tumor', 'm...   \n",
       "11  [['Animals'], ['Birds', 'classification', 'gen...   \n",
       "21  [['Health Facilities'], ['Humans'], ['Malaria'...   \n",
       "\n",
       "                                              mesh_id  \\\n",
       "3                                               [nan]   \n",
       "6                                               [nan]   \n",
       "10  [['D000328'], ['D000368'], ['D014408', 'Q00037...   \n",
       "11  [['D000818'], ['D001717', 'Q000145', 'Q000235'...   \n",
       "21  [['D006268'], ['D006801'], ['D008288', 'Q00017...   \n",
       "\n",
       "                                            grant_ids  \\\n",
       "3                [['RC2 GM092561'], ['T32 GM083864']]   \n",
       "6                [['R01 DA007606'], ['R01 DA035499']]   \n",
       "10  [['G1000143'], ['MC_UU_12015/1'], ['MC_U106179...   \n",
       "11               [['DP1 OD000448'], ['R01 HL087216']]   \n",
       "21                [['U19 AI089674'], ['U19AI089674']]   \n",
       "\n",
       "                                         grant_agency  \\\n",
       "3              [['NIGMS NIH HHS'], ['NIGMS NIH HHS']]   \n",
       "6                [['NIDA NIH HHS'], ['NIDA NIH HHS']]   \n",
       "10  [['Medical Research Council'], ['Medical Resea...   \n",
       "11                   [['NIH HHS'], ['NHLBI NIH HHS']]   \n",
       "21             [['NIAID NIH HHS'], ['NIAID NIH HHS']]   \n",
       "\n",
       "                                              keyword      pmid  \\\n",
       "3   [['gas chromatography', 'high performance liqu...  25789275   \n",
       "6   [['blood-brain barrier', 'glutamate', 'methamp...  25788874   \n",
       "10                                              [nan]  25637171   \n",
       "11  [['Avian genomes', 'Gene trees', 'Indels', 'Ph...  25741440   \n",
       "21                                              [nan]  25366929   \n",
       "\n",
       "                                    grant_ids_cleaned  \\\n",
       "3                       RC2 GM092561], T32 GM083864]]   \n",
       "6                       R01 DA007606], R01 DA035499]]   \n",
       "10  G1000143], MC_UU_12015/1], MC_U106179471], 141...   \n",
       "11                      DP1 OD000448], R01 HL087216]]   \n",
       "21                       U19 AI089674], U19AI089674]]   \n",
       "\n",
       "                                 grant_agency_cleaned  \n",
       "3                     NIGMS NIH HHS], NIGMS NIH HHS]]  \n",
       "6                       NIDA NIH HHS], NIDA NIH HHS]]  \n",
       "10  Medical Research Council], Medical Research Co...  \n",
       "11                          NIH HHS], NHLBI NIH HHS]]  \n",
       "21                    NIAID NIH HHS], NIAID NIH HHS]]  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grant_funded.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [],
   "source": [
    "def interleave_lists(l1, l2):\n",
    "    if len(l1)>1:\n",
    "        outlist = [\"{}---{}\".format(b_, a_) for a_, b_ in zip(l1, l2)]\n",
    "    else:\n",
    "        outlist = [l1[0]+'---'+l2[0]]\n",
    "        \n",
    "    return(outlist)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/riddleta/ac_knowl/ac_knowl/lib/python3.7/site-packages/ipykernel_launcher.py:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  This is separate from the ipykernel package so we can avoid doing imports until\n"
     ]
    }
   ],
   "source": [
    "grant_funded['grant_list'] = grant_funded.apply(lambda x: \n",
    "                                                interleave_lists(x['grant_ids_cleaned'].split('], '), \n",
    "                                                                 x['grant_agency_cleaned'].split('], ')), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "grant_pmids = grant_funded.explode('grant_list')\n",
    "grant_pmids = grant_pmids[['pmid', 'grant_list']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "grant_pmids['grant_list'] = grant_pmids.grant_list.apply(lambda x: re.sub('\\]', '', x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "grant_pmids[['agency', 'number', 'x1', 'x2']] = grant_pmids.grant_list.str.split('---', expand=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "#nothing in x1 or x2 are from the NIH\n",
    "grant_pmids = grant_pmids[grant_pmids.x1.isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "grant_pmids.to_csv('output/pmcidextracted_grant_pmid_pairing.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [],
   "source": [
    "agency_counts = grant_pmids.agency.value_counts()\n",
    "grant_id_counts = grant_pmids.number.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "NCI NIH HHS      455167\n",
       "NHLBI NIH HHS    325312\n",
       "NIGMS NIH HHS    266381\n",
       "NIAID NIH HHS    240204\n",
       "NIDDK NIH HHS    206903\n",
       "NIMH NIH HHS     177469\n",
       "NIA NIH HHS      170175\n",
       "NINDS NIH HHS    163838\n",
       "NCRR NIH HHS     140881\n",
       "NICHD NIH HHS    137069\n",
       "Name: agency, dtype: int64"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agency_counts.iloc[0:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "NIDA NIH HHS                120229\n",
       "Medical Research Council     87882\n",
       "NIAMS NIH HHS                75422\n",
       "NEI NIH HHS                  67507\n",
       "NIEHS NIH HHS                66584\n",
       "Wellcome Trust               64693\n",
       "NCATS NIH HHS                59774\n",
       "NIAAA NIH HHS                57386\n",
       "NIBIB NIH HHS                56905\n",
       "NIDCR NIH HHS                35811\n",
       "NIDCD NIH HHS                35618\n",
       "Intramural NIH HHS           31083\n",
       "PHS HHS                      30654\n",
       "NHGRI NIH HHS                26439\n",
       "NIH HHS                      23841\n",
       "Name: agency, dtype: int64"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agency_counts.iloc[10:25]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Biotechnology and Biological Sciences Research Council                                 20526\n",
       "                                                                                       18366\n",
       "NIMHD NIH HHS                                                                          15317\n",
       "NINR NIH HHS                                                                           14645\n",
       "FIC NIH HHS                                                                            13409\n",
       "Howard Hughes Medical Institute                                                        13356\n",
       "Cancer Research UK                                                                     12814\n",
       "British Heart Foundation                                                               12118\n",
       "NCCIH NIH HHS                                                                          11891\n",
       "Canadian Institutes of Health Research                                                  9749\n",
       "WHI NIH HHS                                                                             9327\n",
       "NLM NIH HHS                                                                             8885\n",
       "National Natural Science Foundation of China                                            8713\n",
       "AHRQ HHS                                                                                7058\n",
       "Department of Health                                                                    6942\n",
       "BLRD VA                                                                                 5875\n",
       "NCCDPHP CDC HHS                                                                         5639\n",
       "European Research Council                                                               3959\n",
       "CC999999                                                                                3843\n",
       "National Natural Science Foundation of China (National Science Foundation of China)     2930\n",
       "Arthritis Research UK                                                                   2660\n",
       "National Institutes of Health                                                           2510\n",
       "CIHR                                                                                    2487\n",
       "Austrian Science Fund FWF                                                               2485\n",
       "001                                                                                     2214\n",
       "Name: agency, dtype: int64"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agency_counts.iloc[25:50]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7500"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "75*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "42.857142857142854"
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "7500/175"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "ac_knowl",
   "language": "python",
   "name": "ac_knowl"
  },
  "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}