Notebook_1_SQL_database.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# COVID19-related literature SQL database\n",
    "\n",
    "In this notebook, we create a relational database dump of a set of COVID19-related publication datasets. These include:\n",
    "\n",
    "* CORD19: https://pages.semanticscholar.org/coronavirus-research\n",
    "* Dimensions: https://docs.google.com/spreadsheets/d/1-kTZJZ1GAhJ2m4GAIhw1ZdlgO46JpvX0ZQa232VWRmw/edit#gid=2034285255"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# magics, warnings and imports\n",
    "\n",
    "%load_ext autoreload\n",
    "%autoreload 2\n",
    "import warnings; warnings.simplefilter('ignore')\n",
    "\n",
    "import os, random, codecs, json, math, re\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from tqdm.notebook import tqdm\n",
    "import pymysql\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy import Integer,String,Boolean,DateTime\n",
    "\n",
    "seed = 99\n",
    "random.seed(seed)\n",
    "np.random.seed(seed)\n",
    "\n",
    "import nltk, sklearn\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "sns.set(style=\"white\")\n",
    "sns.set_context(\"notebook\", font_scale=1.2, rc={\"lines.linewidth\": 2.5})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Load datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# point here to the versions of the datasets you want to use\n",
    "dimensions_filename = \"datasets_input/Dimensions_01_07_2020.csv\"\n",
    "cord19_folder = \"datasets_input/CORD19_01_07_2020\"\n",
    "\n",
    "df_dimensions = pd.read_csv(dimensions_filename, dtype=str, sep=\";\")\n",
    "df_cord = pd.read_csv(os.path.join(cord19_folder,\"metadata.csv\"), dtype=str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(169821, 19)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cord.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(61563, 31)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dimensions.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Prepare dataframes for ingestion"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Clean-up data frames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Dimensions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Date added</th>\n",
       "      <th>Publication ID</th>\n",
       "      <th>DOI</th>\n",
       "      <th>PMID</th>\n",
       "      <th>PMCID</th>\n",
       "      <th>Title</th>\n",
       "      <th>Abstract</th>\n",
       "      <th>Source title</th>\n",
       "      <th>Source UID</th>\n",
       "      <th>Publisher</th>\n",
       "      <th>...</th>\n",
       "      <th>Research Organizations - standardized</th>\n",
       "      <th>GRID IDs</th>\n",
       "      <th>City of Research organization</th>\n",
       "      <th>Country of Research organization</th>\n",
       "      <th>Funder</th>\n",
       "      <th>UIDs of supporting grants</th>\n",
       "      <th>Times cited</th>\n",
       "      <th>Altmetric</th>\n",
       "      <th>Source Linkout</th>\n",
       "      <th>Dimensions URL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-06-24</td>\n",
       "      <td>pub.1128710293</td>\n",
       "      <td>10.4267/2042/70788</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>L’association hydroxychloroquine-azythromycine...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Bulletin de l Académie vétérinaire de France</td>\n",
       "      <td>jour.1082072</td>\n",
       "      <td>INIST-CNRS</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>http://documents.irevues.inist.fr/bitstream/20...</td>\n",
       "      <td>https://app.dimensions.ai/details/publication/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-06-24</td>\n",
       "      <td>pub.1128679859</td>\n",
       "      <td>10.1038/s41590-020-0729-y</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Nature Immunology turns 20</td>\n",
       "      <td>Twenty years ago, a new immunology journal was...</td>\n",
       "      <td>Nature Immunology</td>\n",
       "      <td>jour.1022778</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://www.nature.com/articles/s41590-020-072...</td>\n",
       "      <td>https://app.dimensions.ai/details/publication/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-06-24</td>\n",
       "      <td>pub.1128683930</td>\n",
       "      <td>10.1002/alr.22646</td>\n",
       "      <td>32573106</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Management of chronic rhinosinusitis with ster...</td>\n",
       "      <td>BACKGROUND: High-dose, high-volume steroid nas...</td>\n",
       "      <td>International Forum of Allergy &amp; Rhinology</td>\n",
       "      <td>jour.1044553</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Temple University; University of Minnesota; Un...</td>\n",
       "      <td>grid.264727.2; grid.17635.36; grid.25879.31</td>\n",
       "      <td>Philadelphia; Minneapolis; Philadelphia</td>\n",
       "      <td>United States; United States; United States</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://app.dimensions.ai/details/publication/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-06-24</td>\n",
       "      <td>pub.1128676970</td>\n",
       "      <td>10.1111/ejh.13475</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COVID‐19 complicated by parainfluenza co‐infec...</td>\n",
       "      <td>The number of people suffering from the new co...</td>\n",
       "      <td>European Journal Of Haematology</td>\n",
       "      <td>jour.1018712</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>University Hospital Cologne</td>\n",
       "      <td>grid.411097.a</td>\n",
       "      <td>Cologne</td>\n",
       "      <td>Germany</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://app.dimensions.ai/details/publication/...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-06-24</td>\n",
       "      <td>pub.1128677370</td>\n",
       "      <td>10.1002/jcp.29870</td>\n",
       "      <td>32572955</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Expression of NF‐κB, IL‐6, and IL‐10 genes, bo...</td>\n",
       "      <td>Nonalcoholic fatty liver disease (NAFLD) is on...</td>\n",
       "      <td>Journal of Cellular Physiology</td>\n",
       "      <td>jour.1005966</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>Tabriz University of Medical Sciences</td>\n",
       "      <td>grid.412888.f</td>\n",
       "      <td>Tabriz</td>\n",
       "      <td>Iran</td>\n",
       "      <td>Iran National Science Foundation</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://app.dimensions.ai/details/publication/...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Date added  Publication ID                        DOI      PMID PMCID  \\\n",
       "0  2020-06-24  pub.1128710293         10.4267/2042/70788       NaN   NaN   \n",
       "1  2020-06-24  pub.1128679859  10.1038/s41590-020-0729-y       NaN   NaN   \n",
       "2  2020-06-24  pub.1128683930          10.1002/alr.22646  32573106   NaN   \n",
       "3  2020-06-24  pub.1128676970          10.1111/ejh.13475       NaN   NaN   \n",
       "4  2020-06-24  pub.1128677370          10.1002/jcp.29870  32572955   NaN   \n",
       "\n",
       "                                               Title  \\\n",
       "0  L’association hydroxychloroquine-azythromycine...   \n",
       "1                         Nature Immunology turns 20   \n",
       "2  Management of chronic rhinosinusitis with ster...   \n",
       "3  COVID‐19 complicated by parainfluenza co‐infec...   \n",
       "4  Expression of NF‐κB, IL‐6, and IL‐10 genes, bo...   \n",
       "\n",
       "                                            Abstract  \\\n",
       "0                                                NaN   \n",
       "1  Twenty years ago, a new immunology journal was...   \n",
       "2  BACKGROUND: High-dose, high-volume steroid nas...   \n",
       "3  The number of people suffering from the new co...   \n",
       "4  Nonalcoholic fatty liver disease (NAFLD) is on...   \n",
       "\n",
       "                                   Source title    Source UID   Publisher  \\\n",
       "0  Bulletin de l Académie vétérinaire de France  jour.1082072  INIST-CNRS   \n",
       "1                             Nature Immunology  jour.1022778         NaN   \n",
       "2    International Forum of Allergy & Rhinology  jour.1044553         NaN   \n",
       "3               European Journal Of Haematology  jour.1018712         NaN   \n",
       "4                Journal of Cellular Physiology  jour.1005966         NaN   \n",
       "\n",
       "   ...              Research Organizations - standardized  \\\n",
       "0  ...                                                NaN   \n",
       "1  ...                                                NaN   \n",
       "2  ...  Temple University; University of Minnesota; Un...   \n",
       "3  ...                        University Hospital Cologne   \n",
       "4  ...              Tabriz University of Medical Sciences   \n",
       "\n",
       "                                      GRID IDs  \\\n",
       "0                                          NaN   \n",
       "1                                          NaN   \n",
       "2  grid.264727.2; grid.17635.36; grid.25879.31   \n",
       "3                                grid.411097.a   \n",
       "4                                grid.412888.f   \n",
       "\n",
       "             City of Research organization  \\\n",
       "0                                      NaN   \n",
       "1                                      NaN   \n",
       "2  Philadelphia; Minneapolis; Philadelphia   \n",
       "3                                  Cologne   \n",
       "4                                   Tabriz   \n",
       "\n",
       "              Country of Research organization  \\\n",
       "0                                          NaN   \n",
       "1                                          NaN   \n",
       "2  United States; United States; United States   \n",
       "3                                      Germany   \n",
       "4                                         Iran   \n",
       "\n",
       "                             Funder UIDs of supporting grants Times cited  \\\n",
       "0                               NaN                       NaN           0   \n",
       "1                               NaN                       NaN           0   \n",
       "2                               NaN                       NaN           0   \n",
       "3                               NaN                       NaN           0   \n",
       "4  Iran National Science Foundation                       NaN           0   \n",
       "\n",
       "  Altmetric                                     Source Linkout  \\\n",
       "0       NaN  http://documents.irevues.inist.fr/bitstream/20...   \n",
       "1       NaN  https://www.nature.com/articles/s41590-020-072...   \n",
       "2         1                                                NaN   \n",
       "3         2                                                NaN   \n",
       "4         1                                                NaN   \n",
       "\n",
       "                                      Dimensions URL  \n",
       "0  https://app.dimensions.ai/details/publication/...  \n",
       "1  https://app.dimensions.ai/details/publication/...  \n",
       "2  https://app.dimensions.ai/details/publication/...  \n",
       "3  https://app.dimensions.ai/details/publication/...  \n",
       "4  https://app.dimensions.ai/details/publication/...  \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dimensions.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Date added', 'Publication ID', 'DOI', 'PMID', 'PMCID', 'Title',\n",
       "       'Abstract', 'Source title', 'Source UID', 'Publisher', 'MeSH terms',\n",
       "       'Publication Date', 'PubYear', 'Volume', 'Issue', 'Pagination',\n",
       "       'Open Access', 'Publication Type', 'Authors', 'Corresponding Authors',\n",
       "       'Authors Affiliations', 'Research Organizations - standardized',\n",
       "       'GRID IDs', 'City of Research organization',\n",
       "       'Country of Research organization', 'Funder',\n",
       "       'UIDs of supporting grants', 'Times cited', 'Altmetric',\n",
       "       'Source Linkout', 'Dimensions URL'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dimensions.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_dimensions.drop(columns=['Date added', 'Publisher', 'Authors', 'Corresponding Authors',\n",
    "       'Authors Affiliations', 'Research Organizations - standardized',\n",
    "       'GRID IDs', 'City of Research organization',\n",
    "       'Country of Research organization', 'Funder',\n",
    "       'UIDs of supporting grants', 'Times cited', 'Altmetric',\n",
    "       'Source Linkout'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Publication ID', 'DOI', 'PMID', 'PMCID', 'Title', 'Abstract',\n",
       "       'Source title', 'Source UID', 'MeSH terms', 'Publication Date',\n",
       "       'PubYear', 'Volume', 'Issue', 'Pagination', 'Open Access',\n",
       "       'Publication Type', 'Dimensions URL'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dimensions.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_dimensions.rename(columns={'Publication ID':'dimensions_id', 'DOI':'doi', 'PMID':'pmid', 'PMCID':'pmcid', 'Title':'title', 'Abstract':'abstract',\n",
    "       'Source title':'journal', 'Source UID':'source_uid', 'MeSH terms':'mesh_terms', 'Publication Date':'publication_date',\n",
    "       'PubYear':'publication_year', 'Volume':'volume', 'Issue':'issue', 'Pagination':'pages', 'Open Access':'open_access',\n",
    "       'Publication Type':'publication_type', 'Dimensions URL':'dimensions_url'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(23749, 17)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dimensions[pd.isnull(df_dimensions.abstract)].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_year(date):\n",
    "    if isinstance(date,str) and len(date)>3 and date[:4].isdigit():\n",
    "        return date[:4]\n",
    "    return \"\"\n",
    "\n",
    "month_to_number = {\"Jan\":\"1\",\"Feb\":\"2\",\"Mar\":\"3\",\"Apr\":\"4\",\"May\":\"5\",\"Jun\":\"6\",\"Jul\":\"7\",\"Aug\":\"8\",\"Sep\":\"9\",\"Oct\":\"10\",\"Nov\":\"11\",\"Dec\":\"12\"}\n",
    "\n",
    "def get_month(date):\n",
    "    if isinstance(date,str) and len(date)>6:\n",
    "        if \"-\" in date and date.split(\"-\")[1].isdigit():\n",
    "            return str(int(date.split(\"-\")[1]))\n",
    "        else:\n",
    "            try:\n",
    "                return month_to_number[date.split()[1]]\n",
    "            except:\n",
    "                return \"\"\n",
    "    return \"\"\n",
    "\n",
    "def sanitize_string(s):\n",
    "    return \" \".join(s.split())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_dimensions[\"publication_year\"] = df_dimensions[\"publication_year\"].apply(get_year)\n",
    "df_dimensions[\"publication_month\"] = df_dimensions[\"publication_date\"].apply(get_month)\n",
    "df_dimensions[\"arxiv_id\"] = \"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_dimensions.drop(columns=\"publication_date\", inplace=True)\n",
    "df_dimensions = df_dimensions.fillna('')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Retrieve arXiv ID for Dimensions papers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\nimport requests, re\\n\\nfor index, row in df_dimensions[df_dimensions.journal==\"arXiv\"].iterrows():\\n    r = requests.get(row[\"dimensions_url\"])\\n    if r.status_code == 200:\\n        arxiv_url = re.search(\"https://arxiv.org/pdf/arXiv:[0-9]+.[0-9]+\",r.text).group()\\n        arxiv_id = arxiv_url.split(\":\")[-1]\\n        row[\"arxiv_id\"] = arxiv_id\\n'"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Sync option, quite slow\n",
    "\n",
    "\"\"\"\n",
    "import requests, re\n",
    "\n",
    "for index, row in df_dimensions[df_dimensions.journal==\"arXiv\"].iterrows():\n",
    "    r = requests.get(row[\"dimensions_url\"])\n",
    "    if r.status_code == 200:\n",
    "        arxiv_url = re.search(\"https://arxiv.org/pdf/arXiv:[0-9]+.[0-9]+\",r.text).group()\n",
    "        arxiv_id = arxiv_url.split(\":\")[-1]\n",
    "        row[\"arxiv_id\"] = arxiv_id\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# from https://towardsdatascience.com/fast-and-async-in-python-accelerate-your-requests-using-asyncio-62dafca83c33\n",
    "# aSync option, fast\n",
    "\n",
    "import aiohttp\n",
    "import requests\n",
    "import asyncio\n",
    "\n",
    "from aiohttp import ClientSession\n",
    "\n",
    "def extract_fields_from_response(response):\n",
    "    \"\"\"Extract arxiv id from API's response\"\"\"\n",
    "    search_res = re.search(\"arXiv:[0-9]+.[0-9]+\",response)\n",
    "    if search_res:\n",
    "        arxiv_url = search_res.group()\n",
    "        arxiv_id = arxiv_url.split(\":\")[-1]\n",
    "        return arxiv_id\n",
    "    return \"\"\n",
    "\n",
    "async def get_details_async(session, payload):   \n",
    "    response = await session.request(method='GET', url=payload[1][\"dimensions_url\"])\n",
    "    if response.status != 200:\n",
    "        print(payload[0], response.status)\n",
    "        pass\n",
    "    return await response.text()\n",
    "\n",
    "async def run_program(session, payload):\n",
    "    \"\"\"Wrapper for running program in an asynchronous manner\"\"\"\n",
    "    response = await get_details_async(session, payload)\n",
    "    arxiv_id = extract_fields_from_response(response)\n",
    "    return (arxiv_id,payload[1][\"dimensions_url\"])\n",
    "\n",
    "payloads = df_dimensions[df_dimensions.journal==\"arXiv\"]\n",
    "connector = aiohttp.TCPConnector(limit=5)\n",
    "async with aiohttp.ClientSession(connector=connector) as session:\n",
    "    results = await asyncio.gather(*[run_program(session, payload) for payload in payloads.iterrows()])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_tmp = pd.DataFrame.from_dict({\"arxiv_id\":[x[0] for x in results],\"dimensions_url\":[x[1] for x in results]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1349, 2)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tmp.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_dimensions = df_dimensions.merge(df_tmp, how=\"left\", left_on=\"dimensions_url\", right_on=\"dimensions_url\")\n",
    "df_dimensions.drop(columns=\"arxiv_id_x\", inplace=True)\n",
    "df_dimensions.rename(columns={\"arxiv_id_y\":\"arxiv_id\"}, inplace=True)\n",
    "df_dimensions = df_dimensions.fillna('')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(61563, 18)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dimensions.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1349, 18)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dimensions[df_dimensions.journal==\"arXiv\"].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>dimensions_id</th>\n",
       "      <th>doi</th>\n",
       "      <th>pmid</th>\n",
       "      <th>pmcid</th>\n",
       "      <th>title</th>\n",
       "      <th>abstract</th>\n",
       "      <th>journal</th>\n",
       "      <th>source_uid</th>\n",
       "      <th>mesh_terms</th>\n",
       "      <th>publication_year</th>\n",
       "      <th>volume</th>\n",
       "      <th>issue</th>\n",
       "      <th>pages</th>\n",
       "      <th>open_access</th>\n",
       "      <th>publication_type</th>\n",
       "      <th>dimensions_url</th>\n",
       "      <th>publication_month</th>\n",
       "      <th>arxiv_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>pub.1128710293</td>\n",
       "      <td>10.4267/2042/70788</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>L’association hydroxychloroquine-azythromycine...</td>\n",
       "      <td></td>\n",
       "      <td>Bulletin de l Académie vétérinaire de France</td>\n",
       "      <td>jour.1082072</td>\n",
       "      <td></td>\n",
       "      <td>2020</td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>All OA; Hybrid</td>\n",
       "      <td>article</td>\n",
       "      <td>https://app.dimensions.ai/details/publication/...</td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>pub.1128679859</td>\n",
       "      <td>10.1038/s41590-020-0729-y</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Nature Immunology turns 20</td>\n",
       "      <td>Twenty years ago, a new immunology journal was...</td>\n",
       "      <td>Nature Immunology</td>\n",
       "      <td>jour.1022778</td>\n",
       "      <td></td>\n",
       "      <td>2020</td>\n",
       "      <td>21</td>\n",
       "      <td>7</td>\n",
       "      <td>697-697</td>\n",
       "      <td>All OA; Bronze</td>\n",
       "      <td>article</td>\n",
       "      <td>https://app.dimensions.ai/details/publication/...</td>\n",
       "      <td>7</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>pub.1128683930</td>\n",
       "      <td>10.1002/alr.22646</td>\n",
       "      <td>32573106</td>\n",
       "      <td></td>\n",
       "      <td>Management of chronic rhinosinusitis with ster...</td>\n",
       "      <td>BACKGROUND: High-dose, high-volume steroid nas...</td>\n",
       "      <td>International Forum of Allergy &amp; Rhinology</td>\n",
       "      <td>jour.1044553</td>\n",
       "      <td></td>\n",
       "      <td>2020</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Closed</td>\n",
       "      <td>article</td>\n",
       "      <td>https://app.dimensions.ai/details/publication/...</td>\n",
       "      <td>6</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>pub.1128676970</td>\n",
       "      <td>10.1111/ejh.13475</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>COVID‐19 complicated by parainfluenza co‐infec...</td>\n",
       "      <td>The number of people suffering from the new co...</td>\n",
       "      <td>European Journal Of Haematology</td>\n",
       "      <td>jour.1018712</td>\n",
       "      <td></td>\n",
       "      <td>2020</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Closed</td>\n",
       "      <td>article</td>\n",
       "      <td>https://app.dimensions.ai/details/publication/...</td>\n",
       "      <td>6</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>pub.1128677370</td>\n",
       "      <td>10.1002/jcp.29870</td>\n",
       "      <td>32572955</td>\n",
       "      <td></td>\n",
       "      <td>Expression of NF‐κB, IL‐6, and IL‐10 genes, bo...</td>\n",
       "      <td>Nonalcoholic fatty liver disease (NAFLD) is on...</td>\n",
       "      <td>Journal of Cellular Physiology</td>\n",
       "      <td>jour.1005966</td>\n",
       "      <td></td>\n",
       "      <td>2020</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Closed</td>\n",
       "      <td>article</td>\n",
       "      <td>https://app.dimensions.ai/details/publication/...</td>\n",
       "      <td>6</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    dimensions_id                        doi      pmid pmcid  \\\n",
       "0  pub.1128710293         10.4267/2042/70788                   \n",
       "1  pub.1128679859  10.1038/s41590-020-0729-y                   \n",
       "2  pub.1128683930          10.1002/alr.22646  32573106         \n",
       "3  pub.1128676970          10.1111/ejh.13475                   \n",
       "4  pub.1128677370          10.1002/jcp.29870  32572955         \n",
       "\n",
       "                                               title  \\\n",
       "0  L’association hydroxychloroquine-azythromycine...   \n",
       "1                         Nature Immunology turns 20   \n",
       "2  Management of chronic rhinosinusitis with ster...   \n",
       "3  COVID‐19 complicated by parainfluenza co‐infec...   \n",
       "4  Expression of NF‐κB, IL‐6, and IL‐10 genes, bo...   \n",
       "\n",
       "                                            abstract  \\\n",
       "0                                                      \n",
       "1  Twenty years ago, a new immunology journal was...   \n",
       "2  BACKGROUND: High-dose, high-volume steroid nas...   \n",
       "3  The number of people suffering from the new co...   \n",
       "4  Nonalcoholic fatty liver disease (NAFLD) is on...   \n",
       "\n",
       "                                        journal    source_uid mesh_terms  \\\n",
       "0  Bulletin de l Académie vétérinaire de France  jour.1082072              \n",
       "1                             Nature Immunology  jour.1022778              \n",
       "2    International Forum of Allergy & Rhinology  jour.1044553              \n",
       "3               European Journal Of Haematology  jour.1018712              \n",
       "4                Journal of Cellular Physiology  jour.1005966              \n",
       "\n",
       "  publication_year volume issue    pages     open_access publication_type  \\\n",
       "0             2020            2           All OA; Hybrid          article   \n",
       "1             2020     21     7  697-697  All OA; Bronze          article   \n",
       "2             2020                                Closed          article   \n",
       "3             2020                                Closed          article   \n",
       "4             2020                                Closed          article   \n",
       "\n",
       "                                      dimensions_url publication_month  \\\n",
       "0  https://app.dimensions.ai/details/publication/...                 1   \n",
       "1  https://app.dimensions.ai/details/publication/...                 7   \n",
       "2  https://app.dimensions.ai/details/publication/...                 6   \n",
       "3  https://app.dimensions.ai/details/publication/...                 6   \n",
       "4  https://app.dimensions.ai/details/publication/...                 6   \n",
       "\n",
       "  arxiv_id  \n",
       "0           \n",
       "1           \n",
       "2           \n",
       "3           \n",
       "4           "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dimensions.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1890, 18)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dimensions[df_dimensions.doi==\"\"].shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### CORD19"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>cord_uid</th>\n",
       "      <th>sha</th>\n",
       "      <th>source_x</th>\n",
       "      <th>title</th>\n",
       "      <th>doi</th>\n",
       "      <th>pmcid</th>\n",
       "      <th>pubmed_id</th>\n",
       "      <th>license</th>\n",
       "      <th>abstract</th>\n",
       "      <th>publish_time</th>\n",
       "      <th>authors</th>\n",
       "      <th>journal</th>\n",
       "      <th>mag_id</th>\n",
       "      <th>who_covidence_id</th>\n",
       "      <th>arxiv_id</th>\n",
       "      <th>pdf_json_files</th>\n",
       "      <th>pmc_json_files</th>\n",
       "      <th>url</th>\n",
       "      <th>s2_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ug7v899j</td>\n",
       "      <td>d1aafb70c066a2068b02786f8929fd9c900897fb</td>\n",
       "      <td>PMC</td>\n",
       "      <td>Clinical features of culture-proven Mycoplasma...</td>\n",
       "      <td>10.1186/1471-2334-1-6</td>\n",
       "      <td>PMC35282</td>\n",
       "      <td>11472636</td>\n",
       "      <td>no-cc</td>\n",
       "      <td>OBJECTIVE: This retrospective chart review des...</td>\n",
       "      <td>2001-07-04</td>\n",
       "      <td>Madani, Tariq A; Al-Ghamdi, Aisha A</td>\n",
       "      <td>BMC Infect Dis</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>document_parses/pdf_json/d1aafb70c066a2068b027...</td>\n",
       "      <td>document_parses/pmc_json/PMC35282.xml.json</td>\n",
       "      <td>https://www.ncbi.nlm.nih.gov/pmc/articles/PMC3...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>02tnwd4m</td>\n",
       "      <td>6b0567729c2143a66d737eb0a2f63f2dce2e5a7d</td>\n",
       "      <td>PMC</td>\n",
       "      <td>Nitric oxide: a pro-inflammatory mediator in l...</td>\n",
       "      <td>10.1186/rr14</td>\n",
       "      <td>PMC59543</td>\n",
       "      <td>11667967</td>\n",
       "      <td>no-cc</td>\n",
       "      <td>Inflammatory diseases of the respiratory tract...</td>\n",
       "      <td>2000-08-15</td>\n",
       "      <td>Vliet, Albert van der; Eiserich, Jason P; Cros...</td>\n",
       "      <td>Respir Res</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>document_parses/pdf_json/6b0567729c2143a66d737...</td>\n",
       "      <td>document_parses/pmc_json/PMC59543.xml.json</td>\n",
       "      <td>https://www.ncbi.nlm.nih.gov/pmc/articles/PMC5...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ejv2xln0</td>\n",
       "      <td>06ced00a5fc04215949aa72528f2eeaae1d58927</td>\n",
       "      <td>PMC</td>\n",
       "      <td>Surfactant protein-D and pulmonary host defense</td>\n",
       "      <td>10.1186/rr19</td>\n",
       "      <td>PMC59549</td>\n",
       "      <td>11667972</td>\n",
       "      <td>no-cc</td>\n",
       "      <td>Surfactant protein-D (SP-D) participates in th...</td>\n",
       "      <td>2000-08-25</td>\n",
       "      <td>Crouch, Erika C</td>\n",
       "      <td>Respir Res</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>document_parses/pdf_json/06ced00a5fc04215949aa...</td>\n",
       "      <td>document_parses/pmc_json/PMC59549.xml.json</td>\n",
       "      <td>https://www.ncbi.nlm.nih.gov/pmc/articles/PMC5...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2b73a28n</td>\n",
       "      <td>348055649b6b8cf2b9a376498df9bf41f7123605</td>\n",
       "      <td>PMC</td>\n",
       "      <td>Role of endothelin-1 in lung disease</td>\n",
       "      <td>10.1186/rr44</td>\n",
       "      <td>PMC59574</td>\n",
       "      <td>11686871</td>\n",
       "      <td>no-cc</td>\n",
       "      <td>Endothelin-1 (ET-1) is a 21 amino acid peptide...</td>\n",
       "      <td>2001-02-22</td>\n",
       "      <td>Fagan, Karen A; McMurtry, Ivan F; Rodman, David M</td>\n",
       "      <td>Respir Res</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>document_parses/pdf_json/348055649b6b8cf2b9a37...</td>\n",
       "      <td>document_parses/pmc_json/PMC59574.xml.json</td>\n",
       "      <td>https://www.ncbi.nlm.nih.gov/pmc/articles/PMC5...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9785vg6d</td>\n",
       "      <td>5f48792a5fa08bed9f56016f4981ae2ca6031b32</td>\n",
       "      <td>PMC</td>\n",
       "      <td>Gene expression in epithelial cells in respons...</td>\n",
       "      <td>10.1186/rr61</td>\n",
       "      <td>PMC59580</td>\n",
       "      <td>11686888</td>\n",
       "      <td>no-cc</td>\n",
       "      <td>Respiratory syncytial virus (RSV) and pneumoni...</td>\n",
       "      <td>2001-05-11</td>\n",
       "      <td>Domachowske, Joseph B; Bonville, Cynthia A; Ro...</td>\n",
       "      <td>Respir Res</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>document_parses/pdf_json/5f48792a5fa08bed9f560...</td>\n",
       "      <td>document_parses/pmc_json/PMC59580.xml.json</td>\n",
       "      <td>https://www.ncbi.nlm.nih.gov/pmc/articles/PMC5...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cord_uid                                       sha source_x  \\\n",
       "0  ug7v899j  d1aafb70c066a2068b02786f8929fd9c900897fb      PMC   \n",
       "1  02tnwd4m  6b0567729c2143a66d737eb0a2f63f2dce2e5a7d      PMC   \n",
       "2  ejv2xln0  06ced00a5fc04215949aa72528f2eeaae1d58927      PMC   \n",
       "3  2b73a28n  348055649b6b8cf2b9a376498df9bf41f7123605      PMC   \n",
       "4  9785vg6d  5f48792a5fa08bed9f56016f4981ae2ca6031b32      PMC   \n",
       "\n",
       "                                               title                    doi  \\\n",
       "0  Clinical features of culture-proven Mycoplasma...  10.1186/1471-2334-1-6   \n",
       "1  Nitric oxide: a pro-inflammatory mediator in l...           10.1186/rr14   \n",
       "2    Surfactant protein-D and pulmonary host defense           10.1186/rr19   \n",
       "3               Role of endothelin-1 in lung disease           10.1186/rr44   \n",
       "4  Gene expression in epithelial cells in respons...           10.1186/rr61   \n",
       "\n",
       "      pmcid pubmed_id license  \\\n",
       "0  PMC35282  11472636   no-cc   \n",
       "1  PMC59543  11667967   no-cc   \n",
       "2  PMC59549  11667972   no-cc   \n",
       "3  PMC59574  11686871   no-cc   \n",
       "4  PMC59580  11686888   no-cc   \n",
       "\n",
       "                                            abstract publish_time  \\\n",
       "0  OBJECTIVE: This retrospective chart review des...   2001-07-04   \n",
       "1  Inflammatory diseases of the respiratory tract...   2000-08-15   \n",
       "2  Surfactant protein-D (SP-D) participates in th...   2000-08-25   \n",
       "3  Endothelin-1 (ET-1) is a 21 amino acid peptide...   2001-02-22   \n",
       "4  Respiratory syncytial virus (RSV) and pneumoni...   2001-05-11   \n",
       "\n",
       "                                             authors         journal mag_id  \\\n",
       "0                Madani, Tariq A; Al-Ghamdi, Aisha A  BMC Infect Dis    NaN   \n",
       "1  Vliet, Albert van der; Eiserich, Jason P; Cros...      Respir Res    NaN   \n",
       "2                                    Crouch, Erika C      Respir Res    NaN   \n",
       "3  Fagan, Karen A; McMurtry, Ivan F; Rodman, David M      Respir Res    NaN   \n",
       "4  Domachowske, Joseph B; Bonville, Cynthia A; Ro...      Respir Res    NaN   \n",
       "\n",
       "  who_covidence_id arxiv_id  \\\n",
       "0              NaN      NaN   \n",
       "1              NaN      NaN   \n",
       "2              NaN      NaN   \n",
       "3              NaN      NaN   \n",
       "4              NaN      NaN   \n",
       "\n",
       "                                      pdf_json_files  \\\n",
       "0  document_parses/pdf_json/d1aafb70c066a2068b027...   \n",
       "1  document_parses/pdf_json/6b0567729c2143a66d737...   \n",
       "2  document_parses/pdf_json/06ced00a5fc04215949aa...   \n",
       "3  document_parses/pdf_json/348055649b6b8cf2b9a37...   \n",
       "4  document_parses/pdf_json/5f48792a5fa08bed9f560...   \n",
       "\n",
       "                               pmc_json_files  \\\n",
       "0  document_parses/pmc_json/PMC35282.xml.json   \n",
       "1  document_parses/pmc_json/PMC59543.xml.json   \n",
       "2  document_parses/pmc_json/PMC59549.xml.json   \n",
       "3  document_parses/pmc_json/PMC59574.xml.json   \n",
       "4  document_parses/pmc_json/PMC59580.xml.json   \n",
       "\n",
       "                                                 url s2_id  \n",
       "0  https://www.ncbi.nlm.nih.gov/pmc/articles/PMC3...   NaN  \n",
       "1  https://www.ncbi.nlm.nih.gov/pmc/articles/PMC5...   NaN  \n",
       "2  https://www.ncbi.nlm.nih.gov/pmc/articles/PMC5...   NaN  \n",
       "3  https://www.ncbi.nlm.nih.gov/pmc/articles/PMC5...   NaN  \n",
       "4  https://www.ncbi.nlm.nih.gov/pmc/articles/PMC5...   NaN  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cord.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "unk            81125\n",
       "no-cc          44301\n",
       "cc-by          15105\n",
       "els-covid      14973\n",
       "medrxiv         4703\n",
       "cc-by-nc        2097\n",
       "arxiv           2072\n",
       "cc-by-nc-nd     1464\n",
       "biorxiv         1399\n",
       "green-oa         876\n",
       "cc-by-nc-sa      635\n",
       "bronze-oa        580\n",
       "cc0              282\n",
       "hybrid-oa        134\n",
       "gold-oa           40\n",
       "cc-by-nd          33\n",
       "cc-by-sa           2\n",
       "Name: license, dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cord[\"license\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Medline                               49413\n",
       "WHO                                   30923\n",
       "Medline; PMC                          26653\n",
       "PMC                                   26563\n",
       "Elsevier; Medline; PMC                19370\n",
       "MedRxiv                                4397\n",
       "Elsevier; PMC                          2971\n",
       "ArXiv                                  2044\n",
       "Elsevier; PMC; WHO                     1590\n",
       "Medline; PMC; WHO                      1407\n",
       "BioRxiv                                1158\n",
       "PMC; WHO                               1084\n",
       "Medline; WHO                            661\n",
       "Elsevier; Medline; PMC; WHO             409\n",
       "BioRxiv; MedRxiv                        321\n",
       "Elsevier                                255\n",
       "MedRxiv; Medline; PMC                   176\n",
       "MedRxiv; Medline                        131\n",
       "BioRxiv; Medline; PMC                    69\n",
       "BioRxiv; Medline                         60\n",
       "ArXiv; Medline; PMC                      31\n",
       "BioRxiv; Medline; PMC; WHO               24\n",
       "ArXiv; Elsevier; Medline; PMC            22\n",
       "ArXiv; Medline                           19\n",
       "BioRxiv; Medline; WHO                    12\n",
       "BioRxiv; MedRxiv; Medline                10\n",
       "BioRxiv; MedRxiv; Medline; PMC            7\n",
       "BioRxiv; WHO                              6\n",
       "Elsevier; WHO                             6\n",
       "Elsevier; Medline                         5\n",
       "ArXiv; Elsevier; Medline; PMC; WHO        4\n",
       "ArXiv; Elsevier; PMC; WHO                 4\n",
       "ArXiv; WHO                                4\n",
       "ArXiv; Elsevier                           3\n",
       "MedRxiv; WHO                              2\n",
       "ArXiv; Medline; PMC; WHO                  2\n",
       "ArXiv; PMC                                2\n",
       "ArXiv; MedRxiv                            1\n",
       "ArXiv; PMC; WHO                           1\n",
       "ArXiv; Elsevier; PMC                      1\n",
       "Name: source_x, dtype: int64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cord[\"source_x\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(77777, 19)"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# how many full text files\n",
    "df_cord[(pd.notnull(df_cord.pdf_json_files))|(pd.notnull(df_cord.pmc_json_files))].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.4579940054528003"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cord[(pd.notnull(df_cord.pdf_json_files))|(pd.notnull(df_cord.pmc_json_files))].shape[0]/df_cord.shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.0"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check Medline\n",
    "df_cord[(df_cord.source_x==\"Medline\")&((pd.notnull(df_cord.pdf_json_files))|(pd.notnull(df_cord.pmc_json_files)))].shape[0]/df_cord[(df_cord.source_x==\"Medline\")].shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "49413"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cord[(df_cord.source_x==\"Medline\")].shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.6459454521294267"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check NOT Medline\n",
    "df_cord[(df_cord.source_x!=\"Medline\")&((pd.notnull(df_cord.pdf_json_files))|(pd.notnull(df_cord.pmc_json_files)))].shape[0]/df_cord[(df_cord.source_x!=\"Medline\")].shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "120408"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cord[(df_cord.source_x!=\"Medline\")].shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# NEW columns (for now, we drop)\n",
    "df_cord.drop(columns=[\"cord_uid\",\"url\",\"pdf_json_files\",\"pmc_json_files\",\"s2_id\"],inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['sha', 'source_x', 'title', 'doi', 'pmcid', 'pubmed_id', 'license',\n",
       "       'abstract', 'publish_time', 'authors', 'journal', 'mag_id',\n",
       "       'who_covidence_id', 'arxiv_id'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cord.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cord.drop(columns='authors', inplace=True)\n",
    "df_cord = df_cord.fillna('')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cord.rename(columns={'source_x':'source', 'pubmed_id': 'pmid',\n",
    "       'mag_id': 'ms_academic_id', 'who_covidence_id': 'who_covidence'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cord[\"publication_year\"] = df_cord[\"publish_time\"].apply(get_year)\n",
    "df_cord[\"publication_month\"] = df_cord[\"publish_time\"].apply(get_month)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cord.drop(columns='publish_time', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cord['pages'] = \"\"\n",
    "df_cord['volume'] = \"\"\n",
    "df_cord['issue'] = \"\"\n",
    "df_cord[\"dimensions_id\"] = \"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>sha</th>\n",
       "      <th>source</th>\n",
       "      <th>title</th>\n",
       "      <th>doi</th>\n",
       "      <th>pmcid</th>\n",
       "      <th>pmid</th>\n",
       "      <th>license</th>\n",
       "      <th>abstract</th>\n",
       "      <th>journal</th>\n",
       "      <th>ms_academic_id</th>\n",
       "      <th>who_covidence</th>\n",
       "      <th>arxiv_id</th>\n",
       "      <th>publication_year</th>\n",
       "      <th>publication_month</th>\n",
       "      <th>pages</th>\n",
       "      <th>volume</th>\n",
       "      <th>issue</th>\n",
       "      <th>dimensions_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>d1aafb70c066a2068b02786f8929fd9c900897fb</td>\n",
       "      <td>PMC</td>\n",
       "      <td>Clinical features of culture-proven Mycoplasma...</td>\n",
       "      <td>10.1186/1471-2334-1-6</td>\n",
       "      <td>PMC35282</td>\n",
       "      <td>11472636</td>\n",
       "      <td>no-cc</td>\n",
       "      <td>OBJECTIVE: This retrospective chart review des...</td>\n",
       "      <td>BMC Infect Dis</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2001</td>\n",
       "      <td>7</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6b0567729c2143a66d737eb0a2f63f2dce2e5a7d</td>\n",
       "      <td>PMC</td>\n",
       "      <td>Nitric oxide: a pro-inflammatory mediator in l...</td>\n",
       "      <td>10.1186/rr14</td>\n",
       "      <td>PMC59543</td>\n",
       "      <td>11667967</td>\n",
       "      <td>no-cc</td>\n",
       "      <td>Inflammatory diseases of the respiratory tract...</td>\n",
       "      <td>Respir Res</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2000</td>\n",
       "      <td>8</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>06ced00a5fc04215949aa72528f2eeaae1d58927</td>\n",
       "      <td>PMC</td>\n",
       "      <td>Surfactant protein-D and pulmonary host defense</td>\n",
       "      <td>10.1186/rr19</td>\n",
       "      <td>PMC59549</td>\n",
       "      <td>11667972</td>\n",
       "      <td>no-cc</td>\n",
       "      <td>Surfactant protein-D (SP-D) participates in th...</td>\n",
       "      <td>Respir Res</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2000</td>\n",
       "      <td>8</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>348055649b6b8cf2b9a376498df9bf41f7123605</td>\n",
       "      <td>PMC</td>\n",
       "      <td>Role of endothelin-1 in lung disease</td>\n",
       "      <td>10.1186/rr44</td>\n",
       "      <td>PMC59574</td>\n",
       "      <td>11686871</td>\n",
       "      <td>no-cc</td>\n",
       "      <td>Endothelin-1 (ET-1) is a 21 amino acid peptide...</td>\n",
       "      <td>Respir Res</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2001</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5f48792a5fa08bed9f56016f4981ae2ca6031b32</td>\n",
       "      <td>PMC</td>\n",
       "      <td>Gene expression in epithelial cells in respons...</td>\n",
       "      <td>10.1186/rr61</td>\n",
       "      <td>PMC59580</td>\n",
       "      <td>11686888</td>\n",
       "      <td>no-cc</td>\n",
       "      <td>Respiratory syncytial virus (RSV) and pneumoni...</td>\n",
       "      <td>Respir Res</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2001</td>\n",
       "      <td>5</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        sha source  \\\n",
       "0  d1aafb70c066a2068b02786f8929fd9c900897fb    PMC   \n",
       "1  6b0567729c2143a66d737eb0a2f63f2dce2e5a7d    PMC   \n",
       "2  06ced00a5fc04215949aa72528f2eeaae1d58927    PMC   \n",
       "3  348055649b6b8cf2b9a376498df9bf41f7123605    PMC   \n",
       "4  5f48792a5fa08bed9f56016f4981ae2ca6031b32    PMC   \n",
       "\n",
       "                                               title                    doi  \\\n",
       "0  Clinical features of culture-proven Mycoplasma...  10.1186/1471-2334-1-6   \n",
       "1  Nitric oxide: a pro-inflammatory mediator in l...           10.1186/rr14   \n",
       "2    Surfactant protein-D and pulmonary host defense           10.1186/rr19   \n",
       "3               Role of endothelin-1 in lung disease           10.1186/rr44   \n",
       "4  Gene expression in epithelial cells in respons...           10.1186/rr61   \n",
       "\n",
       "      pmcid      pmid license  \\\n",
       "0  PMC35282  11472636   no-cc   \n",
       "1  PMC59543  11667967   no-cc   \n",
       "2  PMC59549  11667972   no-cc   \n",
       "3  PMC59574  11686871   no-cc   \n",
       "4  PMC59580  11686888   no-cc   \n",
       "\n",
       "                                            abstract         journal  \\\n",
       "0  OBJECTIVE: This retrospective chart review des...  BMC Infect Dis   \n",
       "1  Inflammatory diseases of the respiratory tract...      Respir Res   \n",
       "2  Surfactant protein-D (SP-D) participates in th...      Respir Res   \n",
       "3  Endothelin-1 (ET-1) is a 21 amino acid peptide...      Respir Res   \n",
       "4  Respiratory syncytial virus (RSV) and pneumoni...      Respir Res   \n",
       "\n",
       "  ms_academic_id who_covidence arxiv_id publication_year publication_month  \\\n",
       "0                                                   2001                 7   \n",
       "1                                                   2000                 8   \n",
       "2                                                   2000                 8   \n",
       "3                                                   2001                 2   \n",
       "4                                                   2001                 5   \n",
       "\n",
       "  pages volume issue dimensions_id  \n",
       "0                                   \n",
       "1                                   \n",
       "2                                   \n",
       "3                                   \n",
       "4                                   "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cord.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(42268, 18)"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cord[(df_cord.doi==\"\") & ((df_cord.sha!=\"\") | (df_cord.pmid!=\"\") | (df_cord.pmcid!=\"\") | df_cord.arxiv_id!=\"\")].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(30947, 18)"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cord[(df_cord.doi==\"\") & (df_cord.pmid==\"\") & (df_cord.pmcid==\"\")].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1953, 18)"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cord[((df_cord.doi==\"\") & (df_cord.pmid==\"\") & (df_cord.pmcid==\"\"))&(df_cord.arxiv_id!=\"\")].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(61563, 18)"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dimensions.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(169821, 18)"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cord.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1336, 18)"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dimensions[((df_dimensions.doi==\"\") & (df_dimensions.pmid==\"\") & (df_dimensions.pmcid==\"\"))&(df_dimensions.journal==\"arXiv\")].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1349, 18)"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dimensions[df_dimensions.journal==\"arXiv\"].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2138, 18)"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cord[df_cord.arxiv_id!=\"\"].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>sha</th>\n",
       "      <th>source</th>\n",
       "      <th>title</th>\n",
       "      <th>doi</th>\n",
       "      <th>pmcid</th>\n",
       "      <th>pmid</th>\n",
       "      <th>license</th>\n",
       "      <th>abstract</th>\n",
       "      <th>journal</th>\n",
       "      <th>ms_academic_id</th>\n",
       "      <th>who_covidence</th>\n",
       "      <th>arxiv_id</th>\n",
       "      <th>publication_year</th>\n",
       "      <th>publication_month</th>\n",
       "      <th>pages</th>\n",
       "      <th>volume</th>\n",
       "      <th>issue</th>\n",
       "      <th>dimensions_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>92679</th>\n",
       "      <td>bc411487f305e451d7485e53202ec241fcc97d3b</td>\n",
       "      <td>ArXiv</td>\n",
       "      <td>CORD-19: The Covid-19 Open Research Dataset</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>arxiv</td>\n",
       "      <td>The Covid-19 Open Research Dataset (CORD-19) i...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2004.10706</td>\n",
       "      <td>2020</td>\n",
       "      <td>4</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            sha source  \\\n",
       "92679  bc411487f305e451d7485e53202ec241fcc97d3b  ArXiv   \n",
       "\n",
       "                                             title doi pmcid pmid license  \\\n",
       "92679  CORD-19: The Covid-19 Open Research Dataset                  arxiv   \n",
       "\n",
       "                                                abstract journal  \\\n",
       "92679  The Covid-19 Open Research Dataset (CORD-19) i...           \n",
       "\n",
       "      ms_academic_id who_covidence    arxiv_id publication_year  \\\n",
       "92679                               2004.10706             2020   \n",
       "\n",
       "      publication_month pages volume issue dimensions_id  \n",
       "92679                 4                                   "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cord[df_cord.title==\"CORD-19: The Covid-19 Open Research Dataset\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>dimensions_id</th>\n",
       "      <th>doi</th>\n",
       "      <th>pmid</th>\n",
       "      <th>pmcid</th>\n",
       "      <th>title</th>\n",
       "      <th>abstract</th>\n",
       "      <th>journal</th>\n",
       "      <th>source_uid</th>\n",
       "      <th>mesh_terms</th>\n",
       "      <th>publication_year</th>\n",
       "      <th>volume</th>\n",
       "      <th>issue</th>\n",
       "      <th>pages</th>\n",
       "      <th>open_access</th>\n",
       "      <th>publication_type</th>\n",
       "      <th>dimensions_url</th>\n",
       "      <th>publication_month</th>\n",
       "      <th>arxiv_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>42642</th>\n",
       "      <td>pub.1126924428</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>CORD-19: The Covid-19 Open Research Dataset</td>\n",
       "      <td>The Covid-19 Open Research Dataset (CORD-19) i...</td>\n",
       "      <td>arXiv</td>\n",
       "      <td>jour.1371339</td>\n",
       "      <td></td>\n",
       "      <td>2020</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>All OA; Green, Submitted</td>\n",
       "      <td>preprint</td>\n",
       "      <td>https://app.dimensions.ai/details/publication/...</td>\n",
       "      <td>4</td>\n",
       "      <td>2004.10706</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        dimensions_id doi pmid pmcid  \\\n",
       "42642  pub.1126924428                  \n",
       "\n",
       "                                             title  \\\n",
       "42642  CORD-19: The Covid-19 Open Research Dataset   \n",
       "\n",
       "                                                abstract journal  \\\n",
       "42642  The Covid-19 Open Research Dataset (CORD-19) i...   arXiv   \n",
       "\n",
       "         source_uid mesh_terms publication_year volume issue pages  \\\n",
       "42642  jour.1371339                        2020                      \n",
       "\n",
       "                    open_access publication_type  \\\n",
       "42642  All OA; Green, Submitted         preprint   \n",
       "\n",
       "                                          dimensions_url publication_month  \\\n",
       "42642  https://app.dimensions.ai/details/publication/...                 4   \n",
       "\n",
       "         arxiv_id  \n",
       "42642  2004.10706  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dimensions[df_dimensions.title==\"CORD-19: The Covid-19 Open Research Dataset\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAccAAAERCAYAAAAOm2yAAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjMsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+AADFEAAAgAElEQVR4nOzde1yP9//48UcqiymKxDZZDr2dKo1YIywih04OW6IcyqkNOVbmsJyPMzWJcljEdzlMidiwGT7GYiaLlSJlH8bE1syn0/v3RzfXz1sHocOy5/1263Z7v1/X63pdr+tyu72fXq/rup4vLbVarUYIIYQQihpV3QEhhBDin0aCoxBCCPEECY5CCCHEEyQ4CiGEEE+Q4CiEEEI8QaeqOyBezMOHD7l48SLGxsZoa2tXdXeEEKJayM/P5/bt27Rr1w49Pb0i2yU4VnMXL15k2LBhVd0NIYSolqKioujYsWORcgmOVez06dN4eXlx7NgxGjVq9Mz7GxsbA4X/wM+zvxBCVFfq/AK0tJ/v7uDNmzcZNmyY8hv6JAmO1dyjqdSah7/nlbr1qrg3QghReYwnDH/hNkq6HSUP5AghhBBPkOD4glQqFTExMSWWBQQEMGvWLBYuXEjnzp2xtbVl+vTpZGdnF9veiRMnsLCw4PPPP6/wvgshhCieBMdKEBsbS35+Pjt27GDOnDkcOnSIyMjIIvXOnDnDhx9+yIwZMxgxYkQV9FQIIQTIPcdKUa9ePWbPno22tjbNmjUjLi6O8+fPa9Q5f/48s2bNYtq0aXh6elZRT4UQQoCMHCuFqampxk1fAwMDcnNzNerMnDmTBw8e8MYbb1R294QQQjxBRo7lLC8vr0hZzZo1i5Q9uVKYn58f165dY968eXTs2BF9ff0K66MQQojSycjxBenq6mo8XJOenv5c7fTr14/p06dTUFDA8uXLy6t7QgghnoMExxfUvn17oqOjuXz5Mj///DPz5s0rdqRYFgYGBsyaNYvo6GhOnTpVzj0VQghRVjKt+oI+/vhjPv74Y4YMGULDhg2ZPHkyt27deu72+vXrR0xMDHPmzGHfvn3UqlWrTPsZDXfFWO5XCiH+RdR5+WjpVExOaS31kze/RLWSmZlJz549OXLkiDzMI4QQZfS0306ZVhVCCCGeIMFRCFEl1Hm5T68kRBWRe44vid+2rUTb4NWq7oYQZdbYd1FVd0GIEsnIUQghhHiCBMcnqFQqdu3axbBhw7C0tMTR0ZEvvvhCo86uXbsYMGAAlpaWODg4sG3btqe2e+HCBTw9PWnfvj1du3Zl+fLlSsKA3NxcVq9eTffu3Wnfvj3u7u5F0ssJIYSoPBIci7Fy5UqGDRvGl19+SceOHfn444+5ceMGAJs3b2bBggWMGDGC2NhYvL29Wb58OZs2bSqxvYyMDLy8vGjatCm7du1ixYoVxMbGEhISAsDChQvZvXs3c+bMISYmhtatW+Pj48Pdu3cr5XyFEEJoknuOxRg0aBD9+vUDCnOe7ty5kwsXLvDaa68RERHBiBEjGDJkCABvvvkmGRkZhIeHM2rUKLS0tIq0Fx0dTYMGDQgKCkJbW5sWLVqwYMECfv31V7Kzs9m9ezfz58+nV69eAHz00Ufo6elx7949jIyMKu/EhRBCABIci/Xmm28qnw0MDIDCqc+7d+9y584d3nrrLY36NjY2RERE8PvvvzNixAh+/fVXZdv+/ftJTk6mbdu2GsnH3333XQASExPJzc3F0tJS2aajo4O/v39FnJoQQogykOBYjJIShb/yyivF1s/PzwcKg9qGDRs0ko83bNgQHZ2SL7Ouru4L9lYIIUR5k3uOz6BOnTo0atSIs2fPapSfPXsWY2Nj6taty+uvv07Tpk2VPx0dHZo3b05SUhIFBQXKPl988QUDBw7E1NQUHR0dLl68qGwrKCigT58+7N+/v9LOTQghxP8nwfEZTZgwgcjISHbu3El6ejrR0dFs27aNkSNHFnu/EWDYsGHcuXOHBQsWkJqaysmTJwkJCaF79+7Url0bDw8PVq9ezbFjx7h27Rrz58/n/v37dO7cuZLPTgghBMi06jNzd3fn4cOHrF+/nqCgIJo0aUJAQAAeHh4l7mNiYkJ4eDgrV67E1dUVIyMjBg8ezIcffgjAjBkz0NbWZtasWfz1119YWFiwceNGGjRoUOZ+NRw+ncaSW1VUI+q8XLR05LaC+GeSxOPVnCQeF0KIZyeJx4UQQohnJMGxgqlUKmJiYqq6Gy+Vgrycqu6CEOIlJ/ccXxI/R/lwu+6/4/6N9fh9Vd0FIcRLTkaOQgghxBMkOBYjOzuboKAg3nnnHaytrfH29iYtLQ2AY8eOMWTIEKysrLC3tyciIuKp7V25coUhQ4bQrl07XFxclKTiSUlJqFQqLly4oFHfw8ODxYsXl/+JCSGEKBMJjsXw8/Pj1KlTrFq1it27d1O7dm18fHxISEhg/PjxdOnShb179xIYGMjatWuJjo4utb3IyEjc3d2JiYmhQ4cOeHl5cfPmTdq0aUOrVq2IjY1V6mZmZnLu3Dnc3Nwq+jSFEEKUQILjE9LS0jh+/DhBQUHY2trSrFkz5s+fT+/evVmzZg0dO3bEz88PMzMzHBwcmDdvHrVq1Sq1TU9PTwYNGkTz5s2ZPXs2JiYm7NixAwA3NzcOHDigpJyLjY3F3Nyc1q1bV/i5CiGEKJ4ExyckJycDaCQCNzQ0JCAggKysLCwsLDTqu7q64uTkRFhYGNbW1spfWFiYUsfa2lr5XKNGDdq0aUNKSgoAzs7O/PHHH5w8eRIoDI4DBw6ssPMTQgjxdPK06hNKSxJe2jZ3d3f69u2rfK9bt67y+fHVOKAwifmj5OZGRkbY2dkRFxeHoaEhGRkZODk5PW/3hRBClAMZOT6hefPmABqJwLOzs7G1tcXAwECjHGD16tX4+vpSr149jYTj9erVU+okJSUpn3Nzc0lMTKRFixZK2cCBA/n222+Jj4/Hzs6O+vXrV9TpCSGEKAMJjk8wMzOjZ8+eBAUFkZCQQGpqKoGBgejr6zNt2jR++OEHQkNDSU9P59ChQ0RGRmJvb19qmxEREcTFxZGamsqcOXPIzs7WyMXao0cPtLW12b59u0ypCiHEP4BMqxZj6dKlLFmyBF9fX/Lz85XFjE1NTQkJCSE4OJjQ0FAaNWrElClTGDx4cKnt+fr6Eh4eTmpqKm3btmXjxo0YGRkp23V1denfvz9xcXH06NHjufrcdljEvya3akFeDjV0iq65KYQQ5UWCYzEMDAxYsmRJsdt69epFr169ytzWL7/8AoCPj0+p9W7fvo2Tk1OxCy0LTRIYhRAV7aWcVg0ICGDkyJFV3Y1ihYSE4ODgoHw/ceIEERERHD16lKFDh5KQkIBKpSIzM7MKeymEEP9uL2Vw/CcbPXo0X3zxhfI9OjqadevWERAQoDwMJEqWL0nHhRCVQKZVK9mrr77Kq6++qnwPDg4ul3a/jR6F0b8g8Xg/7wNV3QUhxL9AtR053r9/n8DAQDp37kynTp0YM2aMkv/0cfn5+Sxbtgw7OzvatWuHk5MT8fHxpbatVqvZsmULvXv3xsrKChcXF44dO6ZsT05OZsyYMdjY2NCpUydmzpzJ3bt3AfD398fT01OjvQsXLqBSqUhPTy8yrXr58mWGDx+OlZUVAwYM4Oeff36RyyKEEKIcVMvgqFarGTt2LL/99hsRERFs376d1157DQ8PD7KysjTqbt++na+//pqQkBAOHjyIo6Mj06ZNIyMjo8T2w8PDCQ4OxtfXl3379uHo6MgHH3xASkoKmZmZDB06lLp16xIVFUVoaCiXL19m9OjR5Ofn4+rqSkJCArdu3VLa27dvH9bW1jRt2lTjOPfv32fkyJE0aNCA3bt34+fnp5FZRwghRNWoltOqp06dIjExkTNnzlCnTh0AgoKC+P7774skAU9PT6dWrVq8/vrrGBsb4+vri6WlpcZL+o9Tq9VERkYyatQoXF1dAZgwYQJ5eXk8ePCAL7/8UnmaVVe3cBpz9erV9OvXj+PHj9O9e3caNWrEgQMHGDVqFPn5+cTHx/Phhx8WOdb+/fvJzc1l0aJFvPrqq7Ro0YJbt24xf/788rxcQgghnlG1HDkmJSWRn5+PnZ2dRj7TjIwMUlNTNep6eHjwxx9/0K1bN4YMGUJISAimpqbo6+uTkJCgsb+Pjw9ZWVncvn1bI7cqwMSJE7GysiIlJQULCwslMEJhVh1DQ0OSk5PR0tLC2dmZuLg4oDCQ379/n379+hU5j5SUFMzMzDTuQbZv3748L5UQQojnUC1Hjrq6utSrV6/YpaJq167NqlWrlO/NmjXj8OHDnDp1ipMnT7J//342bdrE+vXrsbKyYu/evUpdPT09jaBXnFdeeaXY8oKCAmVfNzc3wsLCuHbtGnFxcdjb22NgYFBkHy0tLdRqdZFzE0IIUbWq5cixZcuW3Lt3D0DJZfrGG2/w6aef8sMPP2jUjYqK4quvvqJbt24EBgYSHx/PG2+8QXx8PHp6ehr5UE1MTNDX18fY2JjExESNdjw9PYmIiKBFixYkJiaSm5urbLty5Qr3799XXsV48803sba2Zv/+/Rw+fLjEtRlbt25NWloa9+/fV8qezN0qhBCi8lXL4Ghra0v79u3x8/MjISGBq1evMnv2bL755hvMzc016mZlZbFgwQK++eYbbty4wZEjR8jMzMTKyqrE9n18fNiyZQv79+/n+vXrhIaG8tNPP9G9e3eGDx/On3/+SWBgICkpKSQkJDB9+nRatWqFra2t0oarqysbN26kZs2adO3atdjj9O3bl7p16zJz5kySk5M5fvx4ub3aIYQQ4vlVy2lVLS0t1q5dy7Jly/D19SUnJ4fWrVsrI7vHjR8/nocPHxIUFMSdO3do3LgxEydOLHE0B+Dl5cXDhw9ZsWIFd+/epWXLloSFhdGyZUsANm3axIoVKxg0aBC1atXC3t6eGTNmaEyJ9uvXj8WLFzNgwIASl7qqU6cOn3/+OfPnz2fIkCE0bNiQMWPGyAM5QghRxbTUT970EtVKZmYmPXv25MiRI/+KxOP5eTloS25VIcQLetpvZ7WcVhX/XhIYhRCVQYLjc7h58yYqlYrTp08/1/6ZmZmoVCoSEhLKuWcvLi9fcpcKIUS1vOdY3TVu3JgTJ06UmIjgeezc7UXdei/+zzl6xFfl0BshhKjeJDhWAW1tbYyNjau6G0IIIUrw0k2rqlQq4uLiGDp0KBYWFvTr14/z58+zfft2unfvTocOHZg6dSo5Of9/+jAhIQF3d3csLS3p2bMnq1at4n//+5+y/caNG4wdOxZra2t69uzJ8ePHNY4ZEBCAn58fnp6edOjQgfXr19OtWzf8/PyUOnv37qVt27acP39eY1p19+7dWFtb8+DBA6VuTk4ONjY27Ny5swKvlBBCiJK8dMERYMmSJYwZM4aYmBjq1KnD2LFjOXr0KOHh4SxevJivvvqKXbt2AXDp0iW8vb1xcHBg3759LFy4kG+++YaPP/4YgNzcXHx8fPj777/ZsWMHixYtYsOGDUWOGR8fj4ODA9HR0QwcOJBFixYRHx/PkSNH+O9//8vChQvx9fUtkh7O0dERtVrN0aNHlbLvvvuOnJwcHB0dK+4iCSGEKNFLGRwHDx6Mvb09zZo1w8XFhfv37zNv3jzMzc3p06cPrVu3JiUlBYCNGzfSvXt3vL29adq0Kba2tgQFBbFnzx5+++03/vOf/3D16lWWLVtGq1atePvtt5k9e3aRYxobG+Pl5UXz5s0xNjbGzs4Od3d3Fi5cyMyZMzE3N2f8+PFF9nv11VdxcHBQcrECxMbG0rNnT/T19SvuIgkhhCjRS3nP0dTUVPlcq1YtatSoofEei56enjKteunSJdLT07G2tla2P3r1MzU1lZSUFAwNDXnttdeU7cVl1ynuPRl/f3++++47fvzxRw4dOoS2tnax/XVzc2Ps2LHcv38fbW1tvv32Wz777LNnPGshhBDl5aUMjk9mpNHS0kJLS6vYurq6uri6ujJmzJgi24yNjUlKSipTcnA9Pb0iZf/973+5e/cuBQUFnDlzpsSsPG+//TYNGjTgq6++QltbGwMDA7p06VLi+QkhhKhYL+W06rNo0aIFqampGgnI7969y7Jly/jrr79o3bo1WVlZXLt2TdmnLMnB8/Ly8Pf3x9bWlilTprBo0SL++9//Flu3Ro0aODs7c+jQIeLj43FycipxlCmEEKLi/euD45gxY7hw4QJLliwhNTWVM2fO4O/vz59//omxsTGdO3embdu2zJgxg8TERM6dO8fChQuf2u769eu5evUqQUFBjBo1iiZNmhAYGFhkFPqIq6srp0+f5tSpU8oiy0IIIarGvz44qlQq1q9fz7lz53B1dcXPzw8bGxvlnp+2tjbh4eE0btwYLy8vJk+ezMiRI0ttMykpiXXr1jFjxgxMTEzQ0dFh4cKFnDlzhqioqGL3adasGa1bt6ZFixaoVKryPk0hhBDPQBKPV3PlnXg8Lz8HHW3JXyqEeLlJ4nHxTCQwCiGEBMciVCoVMTExVd2NciXJxIUQ4tm8lK9y/Bt9FudFHcOir5gAzH7/UCX3RgghqjcZOQohhBBPeCmCY3Z2NkFBQbzzzjtYW1vj7e1NWloaAMeOHWPIkCFYWVlhb29PRETEU9u7cuUKQ4YMoV27dri4uHD+/Hmg8ClUlUrFhQsXNOp7eHiwePHiYtt6lGT80KFDuLm5YWVlhYuLC4cPH1bqqNVqtmzZQu/evZXtx44de97LIYQQ4gW9FMHRz8+PU6dOsWrVKnbv3k3t2rXx8fEhISGB8ePH06VLF/bu3UtgYCBr164lOjq61PYiIyNxd3cnJiaGDh064OXlxc2bN2nTpg2tWrUiNjZWqZuZmcm5c+dKzH7zyPLly5kyZQo7d+6kcePG+Pv7KytxhIeHExwcjK+vL/v27cPR0ZEPPvhAyf8qhBCiclX74JiWlsbx48cJCgrC1taWZs2aMX/+fHr37s2aNWvo2LEjfn5+mJmZ4eDgwLx586hVq1apbXp6ejJo0CCaN2/O7NmzMTExYceOHUBhHtQDBw6Ql5cHFCYJNzc3p3Xr1qW26e3tTbdu3TA3N2fy5MlkZ2dz5coV1Go1kZGRjBo1CldXV0xNTZkwYQLjxo3TWMZKCCFE5an2wTE5ORkAS0tLpczQ0JCAgACysrKwsLDQqO/q6oqTkxNhYWFYW1srf2FhYUqdx5OQ16hRgzZt2iijOGdnZ/744w9OnjwJFAbHgQMHAjB37lyNNh8fYZqZmSmfH622kZubS1ZWFrdv39boP8DEiROLTXAuhBCi4lX7p1WfTDJe1m3u7u707dtX+V63bl3l85N5TdVqNTVrFr7/Z2RkhJ2dHXFxcRgaGpKRkYGTkxMAkydPxtvbW9mvfv363Lt3Dyg+WblarS62XAghRNWq9iPH5s2bA5rJwLOzs7G1tcXAwKBIkvDVq1fj6+tLvXr1NJKN16tXT6mTlJSkfM7NzSUxMZEWLVooZQMHDuTbb78lPj4eOzs76tevDxQGw8fbrFOnzlP7r6+vj7GxMYmJiRrlnp6eZXp4SAghRPmr9sHRzMyMnj17EhQUREJCAqmpqQQGBqKvr8+0adP44YcfCA0NJT09nUOHDhEZGYm9vX2pbUZERBAXF0dqaipz5swhOzsbDw8PZXuPHj3Q1tZm+/btypTqi/Dx8WHLli3s37+f69evExoayk8//UT37t1fuG0hhBDPrtpPqwIsXbqUJUuW4OvrS35+PjY2NkRERGBqakpISAjBwcGEhobSqFEjpkyZwuDBg0ttz9fXl/DwcFJTU2nbti0bN27EyMhI2a6rq0v//v2Ji4ujR48eL9x/Ly8vHj58yIoVK7h79y4tW7YkLCyMli1blrmNDwdElphbVfKlCiHEs5HE489p0qRJNGzYkNmzZ1dpP8o78bgQQvwbPO2386UYOVamEydOcPnyZY4ePfrS5WAVQghRqNrfc6xs0dHRrFu3joCAAOVhoGd1+vRpVCoVN2/eLOfeFS9HEo8LIcQzkZHjMwoODn7hNqytrTlx4oTylGt5GPn1JHSNXil2W7zLjnI7jhBC/BtIcKwCNWvWxNjYuKq7IYQQogQyrVpGly9fZsyYMXTs2JF27drRp08f9u7dCxS+kzh37lwGDhyIjY0NW7duxdramhUrVij7r127lrfeeouMjAyNadXg4OAir5bcvn2bNm3a8J///KdSz1EIIUQhCY5l8ODBA0aPHk3Dhg2Jjo4mJiYGGxsbZs+ezZ07dwDYuXMnY8eOZevWrbi5uREQEMDmzZv5+eefuXjxIuvWrWPu3Lk0adJEo21XV1du3LjBjz/+qJTt378fY2Nj3n777Uo9TyGEEIVkWrUM/v77b0aOHImnp6eStHzcuHHs3LmTa9euAYW5XR0dHZV93n//fQ4fPsy8efN48OABffr0wdXVtUjbpqamdOjQgf379ys5Xfft24ezszM1asj/XYQQoirIr28Z1K9fHw8PD/bu3cvcuXPx8vJi0KBBAOTn5wMU+57MwoULSUtLIysri48//rjE9t3c3IiPjyc/P5+rV69y8eLFYgOpEEKIyiHBsQx+++03nJyciImJ4fXXX2fkyJFs2rRJo46enl6R/a5du8bDhw+5f/8+P//8c4nt9+3bl+zsbE6fPk1cXBwWFhbP/ZqIEEKIF/dc06p5eXmlrnjxsvn666/566+/iIqKUlbsOH78OFC4skZxsrOzCQwMZOjQoeTn5zNr1ixiY2OLTUZep04devXqxaFDh/jhhx8YNmxYxZ2MEEKIp3qmkePevXtxdHSkffv2ZGRkMG/ePNauXVtRffvHMDQ0JDs7m0OHDnHjxg2OHDnCvHnzAMjJKf4F+8WLFwMwdepUpk+fTl5enlJWHFdXV/bt28f169fp169f+Z+EEEKIMivz8G/v3r0sXryY0aNHs27dOgBatWrFsmXLqFmzJmPGjKmwTla1vn37kpiYyMKFC3nw4AGmpqb4+vqyYcOGIktNAXzzzTfs3r2biIgIXn31VQDmzZuHr68vDg4O1K5du8g+Xbp0oU6dOlhYWGBoaPjMfdziEFxibtWc/BxqSuJxIYQoszInHnd2dmbMmDE4OTkpq9w3adKEffv2ERwczNdff13RfRXFkMTjQgjx7J7221nmadX09HTat29fpLx9+/bcunXrxXophBBC/IOUOTg2btyYy5cvFyk/deoUjRs3LtdOlYVKpXppV8Vo06YNe/bseeF2cvLzyqE3Qgjx71Pme46jR4/m448/5vbt26jVas6cOcOePXvYsmULU6dOrcg+ijIYdSgMXSN9jbIDbv5V1BshhKjeyhwc33vvPfLy8li/fj0PHz7ko48+wsTEBH9/f9zd3Suyj0IIIUSleqZXOTw8PDh27Bj/+c9/SEhI4NixY88UGLOzswkKCuKdd97B2toab29v0tLSADh27BhDhgzBysoKe3t7IiIintrelStXGDJkCO3atcPFxYXz588DkJSUhEql4sKFC0X6X9LrFJmZmahUKg4dOoSbmxtWVla4uLhw+PBhpY5arWbLli307t1b2X7s2LES+3f69GksLCw4fPiw8grMe++9R0JCglLn3r17TJs2jQ4dOtC1a1e+/PLLp563EEKIivVMwTEhIYG7d+9iZGTEsWPHGDduHKGhoRQUFJRpfz8/P06dOsWqVavYvXs3tWvXxsfHh4SEBMaPH0+XLl3Yu3cvgYGBrF27lujo6FLbi4yMxN3dnZiYGDp06ICXlxc3b96kTZs2tGrVitjYWKVuZmYm586dw83NrdQ2ly9fzpQpU9i5cyeNGzfG39+fBw8eABAeHk5wcDC+vr7s27cPR0dHPvjgA1JSUkpsLzc3l88++4yFCxeyfft2AGbNmqUkD5g8eTLJyclEREQQGhrKtm3blJR0QgghqkaZg2NUVBReXl6kpKRw+fJlZs6ciVqtZvv27axZs+ap+6elpXH8+HGCgoKwtbWlWbNmzJ8/n969e7NmzRo6duyIn58fZmZmODg4MG/ePCXJd0k8PT0ZNGgQzZs3Z/bs2ZiYmLBjR+HCvm5ubhw4cIC8vMKHUmJjYzE3N6d169altunt7U23bt0wNzdn8uTJZGdnc+XKFdRqNZGRkYwaNQpXV1dMTU2ZMGEC48aNU4JncdRqNVOmTKFjx460adOGsWPHkp6eTlZWFqmpqXz//ffMmzcPa2trLC0tWbZs2VOvpRBCiIpV5uAYGRnJ/Pnz6dy5M7GxsahUKjZs2MCKFSs0RmglSU5OBgpXr3jE0NCQgIAAsrKysLCw0Kjv6uqKk5MTYWFhWFtbK39hYWFKnUerWADUqFGDNm3aKKM4Z2dn/vjjD06ePAkUBseBAwcCMHfuXI02H++/mZmZ8llfv/ABl9zcXLKysrh9+7ZG/wEmTpyIlZUVPj4+Gm0+PnVaUpuPrknbtm2V7S1atFASBwghhKgaZX4g59dff6VLly4AnDhxQlmgt2nTpvz+++9PP1ApuVhL2+bu7k7fvn2V73Xr1lU+P8pz+oharaZmzcJMMEZGRtjZ2REXF4ehoSEZGRk4OTkBhVOZ3t7eyn7169fn3r17AOjq6hbpg1qtLrb8cYsWLeLhw4fKdxMTE3766ScApU9PtqmlpaV8ftzTjiWEEKJilXnkaGJiwvXr17l+/TrJycl07doVgLNnz5bpPcdHq0xcvHhRKcvOzsbW1hYDAwONcoDVq1fj6+tLvXr1aNq0qfJXr149pU5SUpLyOTc3l8TERFq0aKGUDRw4kG+//Zb4+Hjs7OyoX78+UBgMH2+zuGTgT9LX18fY2LhIujhPT08iIiIwMTHRaLO4VTqe9GiK9/GFjjMzM5VALYQQomqUOTi+9957TJo0iWHDhtGyZUs6duxIVFQUs2fPLtMTq2ZmZvTs2ZOgoCASEhJITU0lMDAQfX19pk2bxg8//EBoaCjp6ekcOnSIyMhIZXRakoiICOLi4khNTWXOnDlkZ2fj4eGhbO/Rowfa2tps375dmVJ9ET4+PmzZsoX9+/dz/fp1QkND+emnn+jevftztde0aciQ+pYAACAASURBVFPlmpw5c4ZLly7h7+8vixwLIUQVK/O06tixY2nRogXXr1/H2dkZKLxnGBQUVOaFeZcuXcqSJUvw9fUlPz8fGxsbIiIiMDU1JSQkhODgYEJDQ2nUqBFTpkxh8ODBpbbn6+tLeHg4qamptG3blo0bN2JkZKRs19XVpX///sTFxdGjR4+ynmqJvLy8ePjwIStWrODu3bu0bNmSsLAwWrZs+dxtrly5kiVLlvDBBx9Qo0YNxowZQ0ZGxjO3s7nP+CL5AXPy86ip/e9ZWkwIIcpLmROPV1eTJk2iYcOGzJ49u6q7UiEk8bgQQjy7p/12lnlYcfv2bYKDgzl//nyxaxgeOnToxXpazk6cOMHly5c5evRokRysKpWK5cuX4+LiUuy+AQEB3Lx5ky1btlRI30JCQoiNja3wlUxk5CiEEM+nzL+cc+fO5dy5c7i4uGBgYFCRfSoX0dHRnDx5koCAAOVhoLL66KOPypzY4HmMHj2aYcOGlWub3gej0TWsq1EWN8i7hNpCCCFKU+bgePbsWT799FPeeeediuxPuQkODn7ufR+9i1hRXn31VXmXUQgh/sHK/FhkzZo1MTExqci+VKqS8rJC4bTqyJEjle/JycmMGTMGGxsbOnXqxMyZM7l7966yXaVSsWbNGrp160a3bt3Yu3cvKpVKybuqVqsZNWoUbm5u5OTkEBISgoODAwDDhw8nICBAo28HDhzAysqK7OzsCrwCQgghSlLm4Ojh4UFwcDB///13Rfan0pSUl/VJmZmZDB06lLp16xIVFUVoaCiXL19m9OjRGjlQd+7cyfr16/nss89wdXVlwIABfPzxx/z1119s3bqVH3/8kZUrVxZJCODq6srXX3/N//73P6UsLi6OXr16len9SyGEEOWvzMHx3LlzHD16FBsbG95991369Omj8VfdlJaX9XHbt2/HwMCAJUuWYG5uTseOHVm9ejWXLl3i+PHjSj03Nzdat26tpJebO3cueXl5fPTRR6xatYrAwMBi7306OjqSn5+vjDLv37/Pd999V+bXY4QQQpS/Mt9zbN++Pe3bt6/IvlSq0vKyPi4lJQULCwuNlG7NmzfH0NCQ5ORk5f3JJk2aaOxXt25d5s+fz/jx4+natSvvv/9+sf2oU6cOvXr1Ii4ujt69e3Pw4EEMDQ2rzb1dIYR4GZU5OH744YcV2Y9KV1pe1se98sorxe5fUFCgETCLq/fzzz+jra1NUlKSstRXcdzc3JgwYQLZ2dnExcXh5ORUpH9CCCEqzzPlKUtPT2fevHl4enpy69YtoqKiOH36dEX1rUI9LS/rIy1atCAxMZHc3Fyl7MqVK9y/f7/UV0QuXrzIunXrWLlyJcbGxsydO7fEura2thgaGrJ7924SEhJkSlUIIapYmYPjTz/9hIuLCxkZGfz444/k5ORw5coVRo8ezTfffFORfawQT8vL+sjw4cP5888/CQwMJCUlhYSEBKZPn06rVq2wtbUttu2cnBz8/f2xt7enX79+LFy4kCNHjrB3795i69eoUQNnZ2fWrFlD69atMTc3L9dzFUII8WzKHBxXrlzJmDFj2LRpkzKdOG/ePHx8fAgJCamwDlaUR3lZXVxcuHr1apG8rI80aNCATZs2cevWLQYNGsQHH3xA69at2bx5c4lLS61evZrbt28ro0VLS0u8vLxYtGhRsU/EQuFTq3/99VeJWXuEEEJUnjLnVu3QoQN79uyhadOmygLBTZo0UdZJfPw9QVF5SssPKOnjhBCieE/LrVrmkWOtWrWKXdT46tWr8j7eP5QERiGEeD5lDo4DBgxgyZIlpKamoqWlxf/+9z9OnTrFggULcHR0rMg+imeU81hyAiGEEM+uzEOLqVOnMnPmTPr37w+Ak5MTarWafv36MW3atArroCgbn/iD6BoaArBv8KAq7o0QQlRvZQ6ONWvW5NNPP+X69eskJSWhq6tLy5YtMTU1rcj+CSGEEJWuzMExPz+fkJAQGjZsqLzyMGjQIN59910++OADtLS0KqyTT7pz5w5BQUGcPHmS2rVrM3LkSL744gsmTJjAmTNnePjwIb///jtJSUlMmzYNDw8PoqOj2bhxI//9739p2rQpo0ePxs3NTWkzOTmZZcuWkZCQQN26denRowfTp09Xlueyt7dn+PDhnDlzhlOnTtGgQQPc3NyemhzhyJEjrF27ltTUVAwNDRk0aBATJkxAR6fw0h86dIgNGzaQkpKClpYWrVu3ZtasWUoaOiGEEJWvzPccP/nkE3bt2sXrr7+ulL333nt88cUXfPbZZxXSueIUFBQwbtw4bt26xeeff05ISAj79u0jIyNDqRMfH4+DgwPR0dE4ODiwfft2Vq9ezZQpU4iLi8PHx4dFixbx5ZdfAnDr1i08PT0xNzfnyy+/JDg4mCtXrhQJfGvWrOHdd99l7969ODk5ERISQkJCQol9/eqrr5g4cSJ9+/Zl7969zJw5k61bt7JkyRIALly4gJ+fHwMHDuTAgQNs3boVgDlz5pT3ZRNCCPEs1GVkZ2en/v7774uUnzhxQt2jR4+yNvPCTp06pTY3N1dfv35dKfvll1/U5ubm6t27d6v9/f3VXbp00djHzs5OvW3bNo2y0NBQde/evdVqtVr9ySefqAcOHKix/ebNm2pzc3P1uXPn1Gq1Wv3uu++qJ0+erFGnY8eO6vDw8BL7OmjQIPXUqVM1yqKiotRt2rRR//HHH+qkpCT1jh07NLbv2rVL3apVq9IugYaMjAy1ubm5us+GcPWAnbvUA3buKvO+Qgjxb/XotzMjI6PY7WWeVv3zzz9p0KBBkfLGjRtrrG1Y0ZKSkqhfv75Gom9zc3Nl+hPQeGfl7t273Lp1i2XLlrFy5UqlPC8vj/z8fHJycrh06RKXLl3SSEb+SGpqqlL+5ptvamzT19dX0so9ue+PP/5ISkqKxtQtgI2NDXl5eaSlpWFlZYW+vj7r16/nypUrpKenc+nSJQoKCp7xqgghhChPZQ6OFhYWfP755wQFBWncX4yKiqJNmzYV0rniaGtrFxs81I/lMtDT01M+P8piM2fOHDp16lRkPx0dHXR1denSpQuzZ88usv3xrDnFJSZ/dNziUsM93o9HHq0BqaOjw/fff8+YMWPo2bMnb731FoMGDeLatWvMmzevyH5CCCEqT5mD4/Tp0xkxYgTff/89bdu2BQpHcbdv3yYiIqLCOvgklUpFVlYW169fV56UTUtL488//yy2vr6+PiYmJmRmZjJkyBClfMeOHVy6dIn58+fTokUL9u3bx2uvvaYE04yMDBYuXMi0adPQ19d/ar+aNm1apKx58+acPXuWYcOGKWVnz55FV1cXU1NTPvroI7p06cKnn36qbD958iRQGHQr8yEnIYQQ/1+ZH8ixtLQkNjaWPn368Pfff5Obm4ujoyPx8fG89dZbFdlHDW+//TYWFhbMnDmTixcvcuHCBWbOnAlQYjCZMGECW7Zs4YsvvuD69evs27ePpUuXYmxsDBQmF//jjz8ICAjgl19+ITExkalTp3Lt2rUiU6nPYsKECcTHxxMeHs61a9eIj48nODiYIUOGoK+vj5GREb/88gvnz58nIyODrVu38vnnnwOFycuFEEJUjWfKL9akSZN/xAv/ISEhBAUFMWzYMPT19Rk7diwXL14sMRH40KFDycnJYePGjSxYsAATExN8fX0ZO3YsAMbGxmzevJmVK1fy3nvvoaenR+fOnVmzZk2xU6llZWdnx7Jly1i/fj1r1qyhYcOGeHl5MW7cOAAmTZrEb7/9hre3N9ra2qhUKpYuXcqUKVNITEykY8eOZT5WRF9H5V5rTn4+NWU9SCGEeG6lJh6fM2cOAQEBvPrqq099vWDBggXl3rni3L17lwsXLmBnZ6csCHz79m26du1KVFTUMwWUl8HTkucKIYQo6mm/naWOHK9du6Y8QHLt2rUS61XmvTFtbW0mT57MyJEjGTx4MH/99Rdr1qyhadOmWFlZVVo/hBBCvLxKDY6PXkp/8vPjLly4wLJly8q3V6WoW7cuYWFhfPrpp4SFhaGnp0e3bt001pn8N5MpVSGEeHEvvKbR/fv3OXfuXHn0pcxsbW2xtbVFpVIxf/58WSAYGHfwFDUNG/DloHeruitCCFHtlflpVSGEEOLfotKDY3Z2NkFBQbzzzjtYW1vj7e1NWloaAMeOHWPIkCFYWVlhb29fpvcnr1y5wpAhQ2jXrh0uLi6cP38eKHwHU6VSceHCBY36Hh4eLF68uNi2MjMzUalUHDp0CDc3N6ysrHBxceHw4cNKHbVazZYtW+jdu7ey/dixYyX2Lz8/n2XLlmFnZ0e7du1wcnIiPj5e2X7v3j0CAwPp2rUrbdu2pWvXrixbtkyy5AghRBWq9ODo5+fHqVOnWLVqFbt376Z27dr4+PiQkJDA+PHj6dKlC3v37iUwMJC1a9cSHR1danuRkZG4u7sTExNDhw4d8PLy4ubNm7Rp04ZWrVoRGxur1M3MzOTcuXNFUro9afny5UyZMoWdO3fSuHFj/P39efDgAQDh4eEEBwfj6+vLvn37cHR05IMPPiAlJaXYtrZv387XX39NSEgIBw8exNHRkWnTpimJ0v39/UlNTWXdunUcPHiQCRMmsHnzZo4ePfosl1UIIUQ5KvWeY1hY2FMbKO0p1ielpaVx/PhxIiMj6dy5MwDz589X3gPs2LEjfn5+AJiZmTFv3jzldY2SeHp6MmhQ4eK+s2fP5vjx4+zYsYMpU6bg5ubGhg0bCAgIQEdHh9jYWMzNzWndunWpbXp7e9OtWzcAJk+ejKurK1euXMHCwoLIyEhGjRqFq6srUPiif15enhI8n5Senk6tWrV4/fXXMTY2xtfXF0tLS+rVqwcUvgvZuXNnWrZsCcCwYcOIiIjgl19+oVevXmW5rEIIIcpZqcHxaaO2Rxo3blymesnJyQAaaxUaGhoSEBDAgAEDlID0yKMAFBYWxvr165XycePGMX78eEAz4XeNGjVo06aNMopzdnZm5cqVnDx5ku7duxMbG4u7uzsAc+fOZd++fcq+QUFBSqYfMzMzpfxR6rjc3FyysrK4fft2kbUWJ06cCICPjw9nz55VysPDw/Hw8ODrr7+mW7dutGvXDjs7O1xcXJR2hw4dypEjR9i5cyfXrl3jl19+4ebNmzKtKoQQVajU4FjeU3uPFvh91m3u7u707dtX+V63bl3l85MjS7VarWS1MTIyws7Ojri4OAwNDcnIyMDJyQkoHBF6e3sr+9WvX5979+4BFPtKiFqtfuqrIosWLeLhw4fKdxMTE/T09Dh8+DCnTp3i5MmT7N+/n02bNrF+/Xo6derE2LFjuXr1Kk5OTri4uGBpacmIESNKPY4QQoiK9cKvcjyL5s2bA3Dx4kVsbGyAwgd0HBwcaNmyJRcvXtSov3r1alJSUggNDVWmIZ+UlJREjx49gMLRXWJiojLNCjBw4EBmzZpFgwYNsLOzo379+kBhMHz0+ZFHwbEk+vr6GBsbk5iYSPfu3ZVyT09Punfvjo+PT5F9oqKiqFevHv3796dbt274+/vj7OxMfHw8RkZGnDhxgj179ijJ3LOzs7l9+zalJC4SQghRwSr1gRwzMzN69uxJUFAQCQkJpKamEhgYiL6+PtOmTeOHH34gNDSU9PR0Dh06RGRkJPb29qW2GRERQVxcHKmpqcyZM4fs7Gw8PDyU7T169EBbW5vt27czcODAFz4HHx8ftmzZwv79+7l+/TqhoaH89NNPGsHycVlZWSxYsIBvvvmGGzducOTIETIzM7GyssLAwAAdHR3i4+PJzMzkxx9/xNfXl5ycHEk8LoQQVahSR44AS5cuZcmSJfj6+pKfn4+NjQ0RERGYmpoSEhJCcHAwoaGhNGrUiClTpjB48OBS2/P19SU8PJzU1FTatm3Lxo0bNdZg1NXVpX///sTFxSkjzBfh5eXFw4cPWbFiBXfv3qVly5aEhYUpD9Q8afz48Tx8+JCgoCDu3LlD48aNmThxovLE7OLFiwkJCeHzzz/HxMSEvn37YmJiQmJi4jP1a72jLW+88YZkyBFCiHJQauLxl8WkSZNo2LBhsYsZV3eSeFwIIZ7dCyUer+5OnDjB5cuXOXr0KDExMVXdHSGEENXEPzJ9nEqlKpdgFh0dzbp16wgICFAeBnqZ5eTL6x9CCFEeXuqRY3BwcFV3odJ8ePAqe31kWlUIIcrDP3LkKIQQQlSlf2xwLCmhOEBAQAB+fn54enrSoUMHtm/fDsCuXbsYMGAAlpaWODg4sG3bNmWfPXv24OjoyBdffIG9vT3W1tZ4eXmRmpoKQEhICCqVqsjf46+SHD58GGdnZywsLHB0dGTjxo1PzWTz66+/MmXKFGxtbbG2tsbX11fJqwqFN4UnTZpE586dadu2bZkTrgshhKg4/9jgWFJC8Ufi4+NxcHAgOjoaBwcHNm/ezIIFCxgxYgSxsbF4e3uzfPlyNm3apOyTmZnJvn37CA4OZtOmTfz6668sWLAAgNGjR3PixAnlLywsDB0dHSZMmAAUrhgyffp0vLy82L9/PzNmzCAyMpLQ0NASzyE7O5uhQ4dy//59IiIi2Lp1K3/++SfDhw/nzz//BApzs+bk5BAZGcmBAwdwcXFhxYoVXLp0qSIuqxBCiLJQ/wOZm5urV6xYoXzPz89X9+rVS/3JJ5+o1Wq12t/fX92lSxdle0FBgfqdd95Rr1q1SqOd5cuXq99++211QUGBevfu3Wpzc3P1lStXlO2bN29WW1lZFTn+zZs31V27dlUvWrRIKXN3d1cvXbpUo15MTIza0tJSnZ+fX+x5bNu2Td2+fXt1VlaWUvb777+rrays1Nu2bVP//fff6o0bN6pv3rypbM/Ly1O3atVK/eWXX5Z6jR7JyMhQm5ubq13CvytTfSGEEP//tzMjI6PY7f/YB3JKSygOaLyXcvfuXe7cuaMkDn/kUYKB33//HQAtLS2aNm2qbDcwMCA3N1djn7///psJEybQsmVL/P39lfJLly6RmJjI//3f/yllBQUFPHz4kBs3bhAeHl4kkXlKSgrNmjXTSH1nZGRE8+bNSU5ORk9Pj+HDh3PgwAEuXLhAeno6ly5doqCgQBKPCyFEFfrHBsfSEooD6OnpKZ9feeWVYtvIz88H/n9S8xo1ahRJcK5+LAeCWq1m5syZ/PXXX2zZskWjD7q6uvj4+CiJyx9nYmJSbCLzn3/+udh+FRQUoKury4MHD/Dw8CA/P58+ffrQuXNnrKysePfdd4vdTwghROX4x95zTEpKUj4/SijeokWLYuvWqVOHRo0aaSwXBXD27FmMjY01VvEozaeffsqpU6dYt24dBgYGGttatGjBtWvXaNq0qfKXnJzM6tWrgcJg+Pi2OnXq0KJFC9LS0jQSmt+9e5erV6/SvHlzzpw5w6VLl9i6dSsffvghffr04cGDBxQUFEjicSGEqEL/2OD4tITiT5owYQKRkZHs3LmT9PR0oqOj2bZtGyNHjkRLS+upx4uNjSUiIoJFixZRt25dbt++rfzl5+czYcIE9u/fz4YNG7h27Rrffvstc+fORU9PT2NE+zhnZ2eMjIyYOnUqSUlJ/Pzzz0ydOhUDAwP69++v5IDdt28fN27c4NSpU8piz5J4XAghqs4/dlr1aQnFn+Tu7s7Dhw9Zv349QUFBNGnShICAgFID6uN27txJXl4ekyZNKrLtyJEjdOvWjeXLl7NhwwaCg4MxMjLC1dWVKVOmlNjmK6+8wsaNG1m6dCnDhg1DW1sbW1tboqKiMDAwwNLSkpkzZxIeHs6KFSt47bXXGDx4MN999x2JiYkMHTq0TH0H+MzRjJz8Ampq/2P/vyOEENXGvyLx+MtMEo8LIcSze9pvpwwzhBBCiCdIcKxi+fn5rFq1iq5du2Jtbc2kSZO4c+fOs7dTIBMAQghRXiQ4VrGQkBC+/PJLli1bxrZt27h58yYTJ0585na0azz9oSMhhBBlI8GxCj1KGzd16lS6dOlC27Zt+eSTTzh37hznzp2r6u4JIcS/lgTHcpKdnU1QUBDvvPMO1tbWeHt7k5aWBhTmZR0yZAhWVlYaicUvX77MX3/9RadOnZR23njjDV5//XUSEhKq5DyEEEJIcCw3fn5+nDp1ilWrVrF7925q166Nj48PCQkJjB8/ni5durB3714CAwNZu3Yt0dHRSiJ1ExMTjbYaNmyokWRdCCFE5frHvudYnaSlpXH8+HEiIyPp3LkzAPPnz2f9+vWsWbOGjh07Ki/3m5mZMW/ePLS1tfn777+pUaMGurq6Gu3VrFmT//3vf5V+HkIIIQrJyLEcJCcnA2BpaamUGRoaEhAQQFZWFhYWFhr1XV1dcXJyQk9Pj4KCAvLy8jS25+TkUKtWrYrvuBBCiGJJcCwHTyYzL+u2xo0bA3D79m2N8t9++63IVKsQQojKI8GxHDRv3hyAixcvKmXZ2dnY2tpiYGCgUQ6wevVqfH19adWqFa+++ipnzpxRtmVmZnLjxg1sbGwqp/NCCCGKkOBYDszMzOjZsydBQUEkJCSQmppKYGAg+vr6TJs2jR9++IHQ0FDS09M5dOgQkZGR2NvbU7NmTTw8PFi+fDnfffedkpi8U6dOtG/fvqpPSwgh/rXkgZxysnTpUpYsWYKvry/5+fnKQsumpqaEhIQQHBxMaGgojRo1YsqUKQwePBgofMo1Ly+PGTNmkJeXh52dHXPnzn3m40uGHCGEKD+SeLyak8TjQgjx7F6axOMqlYqYmJjn2jczMxOVSvXML9Z7enry0Ucflbg9NDQUe3v75+pTeSuQkaMQQpQbmVYtRUhISKlPm/6T1JDcqkIIUW6qxy9/FalXr15Vd0EIIUQVqDbTqgCpqal4enpiYWGBvb09u3btAiApKQmVSsWFCxc06nt4eLB48WLle0JCAv369cPCwgIPDw8l9ykUTqHOnTuXgQMHYmNjw9GjR4tMqx44cIC+fftiaWmJj48PWVlZT+1zVlYWc+fOxc7ODisrK0aMGEFSUpKy/d69ewQGBtK1a1fatm1L165dWbZsGQUFBc99nYQQQryYahUco6KiGDp0KAcOHMDe3p45c+aQkZFBmzZtaNWqFbGxsUrdzMxMzp07h5ubm1K2efNmpk6dyp49e2jQoAGenp48ePBA2b5z507Gjh3L1q1bNZKBA/zwww9MnToVNzc3YmJi6Nq1K1FRUaX2Nz8/n9GjR5OYmMinn35KdHQ0hoaGDB8+nMzMTAD8/f1JTU1l3bp1HDx4kAkTJrB582aOHj1aHpdMCCHEc6hWwXH48OH069ePJk2aMHHiRAoKCrh06RIAbm5uHDhwQEnFFhsbi7m5Oa1bt1b29/Pzo1evXrRs2ZLFixfz999/s3//fmW7paUljo6OtGrVijp16mgcOyoqis6dOzN27FjMzMwYOXIkDg4Opfb3xIkTJCUl8cknn9ChQwdUKhXLly/HwMBACax2dnYsWrQICwsLmjRpwrBhw2jcuDG//PJLuVwzIYQQz65aBcc333xT+Vy3bl0AHj58CICzszN//PEHJ0+eBAqD48CBAzX2t7a2Vj7XqVOHZs2aKXlRgVJfhUhJSaFt27YaZY+/qB8WFoa1tbXyFxYWRnJyMoaGhpiZmSn1atasiaWlJSkpKQAMHTqUq1evsnjxYsaOHUv37t359ddfZVpVCCGqULV6IKdGjaKx/NFrmkZGRtjZ2REXF4ehoSEZGRk4OTlp1NXW1tb4XlBQQM2aNZXvenp6JR5bS0uLJ18JfXw1DXd3d/r27at8r1u3rsY075PH1dHRQa1WM3bsWK5evYqTkxMuLi5YWloyYsSIEvshhBCi4lWr4Pg0AwcOZNasWTRo0AA7Ozvq16+vsT0pKYmWLVsChQ/CXL16FS8vrzK13apVK3788UeNssdzptarV6/I060tW7YkKyuLtLQ0mjVrBhSuuJGYmIiTkxNXrlzhxIkT7NmzRxmVZmdnc/v27SKBWAghROWpVtOqT9OjRw+0tbXZvn17kSlVgBUrVnDs2DF++eUXpk+fToMGDejXr1+Z2h4xYgSJiYmsXr2aq1ev8n//938cOHCg1H3efvttrK2tmT59OmfPniU5OZnAwED++OMP3n//fQwMDNDR0SE+Pp7MzEx+/PFHfH19ycnJIScn57mugRBCiBf3UgVHXV1d+vfvj56eHj169Ciy3dfXl0WLFjF48GAKCgqIiIjQmFYtjYWFBevWrePbb7/F2dmZmJgYRo0aVeo+WlpafPbZZ5iZmTFu3Djef/99srKy2L59O02aNMHExITFixdz8OBB+vbty4wZM7CyssLZ2ZnExMTnuQRCCCHKwUuXW3XSpEk0bNiQ2bNnV3VXKsWj/IBff30YU9MmVd0dIYSoFp6WW/Wlued44sQJLl++zNGjR587B2t1JunjhBCi/Lw006rR0dGsW7eOgIAAZfHhF7Fnzx7atGlT4vabN2+iUqk4ffr0Cx9LCCHEP8tLM3IMDg6u6i4IIYR4Sbw0I0chhBCivLzUwfHOnTtMnDiRt956i65duxIREYGDgwN79uwBYNeuXQwYMABLS0scHBzYtm1biW3duHGDsWPHYm1tTc+ePTl+/HiZ+vC0Y+zYsYMBAwZgYWGBtbU1o0ePJj09/flPWgghxAt7aaZVn1RQUMC4cePQ1tbm888/Jy8vj48//piMjAygMAn5p59+yuzZs7GxseH7779n8eLF5OTkMHr0aI22cnNz8fHxoUGDBuzYsYN79+4xZ86cp/bhacc4ePAgS5YsYenSpVhZWXHjxg3mzJnDsmXLCA0NrZDrIoQQ4ule2uB45swZLl68yOHDh2nSpPAVhxUrVuDk5IRarSYiIoIRI0YwZMgQoDBva0ZGBuHh4UXeX/zPf/7D1atX2bhxI6+99hoAs2fPZuzYsSUevyzHMDIyYvHixUoigtdfMht5/QAAF0hJREFUf53+/fuXmHZOCCFE5Xhpg2NSUhL169dXAiOAubk5BgYG3L17lzt37vDWW29p7GNjY0NERAS///67RnlKSgqGhoZKYASwsrJSPv/666/0799f+f7aa68RGRn51GN06tSJ5ORkPvvsM9LS0rh69SrJycmYmJiUyzUQQgjxfF7a4KitrV3syhZqtZpXXnml2H3y8/MB0NHRvCxPSzresGFD9u7dq3zX0dEp0zFiYmL46KOPcHZ2pmPHjgwfPpzvvvtORo5CCFHFXtoHclQqFVlZWVy/fl0pS0tL488//6ROnTo0atSIs2fPauxz9uxZjI2NleWwHmndujVZWVlcu3ZNKXs86biOjg5NmzZV/l5//fUyHSMyMhJ3d3cWL16Mh4cHb731FtevX5ek40IIUcVe2pHj22+/jYWFBTNnzmT27NkUFBQwf/58oHAkOGHCBJYsWYKpqSmdOnXi9OnTbNu2jUmTJqGlpZltpnPnzrRt25YZM2Ywd+5ccnNzWbhw4VP78LRjGBkZcfbsWS5fvoyenh5xcXEcOHCgyGoiQgghKtdLO3IECAkJoV69egwbNgxfX1+cnZ3R0tJCV1cXd/f/1969B0V1X3EA/wKCCoII1kh8NGBFhIVleSUoPsC6SVEwoCgKahSdUDqBYKYjqJgRjQYtAlIREavVIPVFdEVTZ9TIRKsEBKMEHywIggEfGCWLuDz29I8Mty4QQOW19nxmmGF/v7u/+/sdgeO9e++5/ggPD8fOnTsxY8YM7NmzBxEREVi2bFmrcXR0dLBr1y6YmZlh0aJFCAsLw0cffdTh/jvaR1RUFAwNDeHv74/58+fj+vXriI6ORnV1NX766aeuDgdjjLFOeuMKjzd7/Pgxrl27hkmTJgkPOX748CHc3NyQlpYGJyenXp5h1+ioeC5jjLHW/m8Kj7eko6MjHOHNmTMHtbW1SEhIwO9//3u1K00ZY4yxlt7Y06qDBw9GcnIyLl++DC8vLyxcuBD9+vXDP/7xD7UrTV80bty4PvFEj+nTpyMxMbG3p8EYY/+33tgjRwBwdXWFq6trb0+DMcaYhnljjxwZY4yxV6XxyVGhUGDdunWYMGECJBIJgoKCUFJSAgDIysqCn58fxGIxPDw8kJqa2uF4crkcfn5+EIlEmDVrFq5evQrg14o748aNw7Vr19S2X7BgATZu3NjmWBUVFRg3bhxOnz4NHx8fiMVizJo1C2fOnBG2USqViI6OxrvvvgsXFxekpKS8aigYY4x1EY1Pjp9++ikuXbqE2NhYHD16FPr6+li2bBlyc3MRHByMiRMn4tixY4iMjMT27dtx6NChdsdrvjH/+PHjcHR0xKJFi1BVVQVra2tYWVmpVa+pqKhAXl4efHx82h1z8+bNCA8Px+HDh2FmZoaVK1fi2bNnAIB169bh7Nmz2Lp1K/bv34/vv/9erXABY4yxnqfRybGkpATfffcd1q1bB1dXV1hYWCA6OhpSqRQJCQlwcnLCp59+CnNzc0yfPh2ff/45Bg4c2O6YCxcuxOzZszFmzBisWbMGb731FtLT0wEAPj4+OHXqFBobGwEAMpkMlpaWGD9+fLtjBgUFYfLkybC0tERYWBgUCgXkcjkUCgVkMhnCw8MxceJEjBs3Dlu2bMGAAQO6JkCMMcZeiUYnx9u3bwMA7OzshLYhQ4YgIiICP//8M2xtbdW2//DDD+Hl5YXk5GRIJBLhKzk5WdhGIpEI32tra8Pa2hpFRUUAAG9vb9TU1ODixYsAfk2Ovr6+AIC1a9eqjfniEaa5ubnwvaGhIYBfH4NVUlKChoYGiEQitfmPHj369QLDGGPstWj01aotC4R3ts/f3x9/+tOfhNcv1lJtLhjQjIigp6cHADAxMcGkSZOQmZmJIUOGoLy8HF5eXgCAsLAwBAUFCe8zNTXFkydPAKDNW0eISChT115Rc8YYYz1Po48cx4wZA0C9CLhCoYCrqyuMjIzU2gEgLi4OISEhMDY2VisUbmxsLGxTWFgofN/Q0IDr16/jD3/4g9Dm6+uL8+fP45tvvsGkSZOEOqimpqZqYw4aNKjD+VtYWEBPTw/5+flq83+xwDljjLGep9HJ0dzcHNOmTcO6deuQm5uL4uJiREZGwtDQEJ999hlycnKQlJSEsrIynD59Gvv27YOHh0e7Y6ampiIzMxPFxcWIioqCQqHAggULhP6pU6dCR0cHBw4cEE6pvioDAwP4+/sjPj4e586dg1wux6pVq/D8+fPXGpcxxtjr0ejTqgDw5ZdfYtOmTQgJCUFTU5PwMOHRo0cjMTER27ZtQ1JSEoYPH47w8HDMmTOn3fFCQkKwa9cuFBcXw8bGBrt374aJiYnQr6urixkzZiAzMxNTp0597fmvXLkSAwYMwOrVq6FUKuHn56f2GSpjjLGe98YWHu9OoaGhGDZsGNasWdPbU+HC44wx9gr+bwuPd4cLFy7g5s2bOHfuXJ+owcoYY6x7aPRnjt2hveLjhw4dwo4dOxARESFcDMQYY+zNw0eOL2Hbtm29PQXGGGM9gI8cGWOMsRbeiOTYl4uPA0BKSgqmTZsGkUiE999/H2lpaUKfUqnEpk2b4O7uDpFIhPfeew+RkZGoq6t7lVAwxhjrAm9EcuzLxcfPnTuH3bt3Y8OGDTh9+jSWLVuG9evXIycnBwAQExODb7/9Flu2bMG///1vrF27FidPnsTBgwe7LkCMMcZeisYnx75efPzu3bvQ1dXF22+/jREjRsDPzw979uyBhYUFAEAsFmPTpk1wcnLCyJEj4enpCTs7O6FuLGOMsZ6n8cmxrxcf9/LygrGxMaRSKby8vBATEwNjY2Oh7NysWbNQV1eHzZs3IyQkBFKpFLm5uVCpVN0QLcYYY52h8Ver9vXi44MGDYJMJsOVK1dw4cIFZGVl4Z///CdiYmLg5eWFNWvW4MyZM/Dx8YFUKkV4eDiio6NfLgiMMca6lMYnxxeLjzs7OwP49QKd6dOnY+zYsW0WHy8qKkJSUpJawfEXFRYWCqXhmouPz549W+j39fXFqlWrMHTo0FbFx5u/b3bq1Cn8/PPPCAgIgLOzM8LDw7F8+XLIZDK4u7vj6NGjSEhIgFQqBQA0NjaivLwcb7/99usHhzHG2CvR+NOqfb34eH19PWJiYiCTyXDv3j1cunQJhYWFEIvF6N+/P/T19XH27FncvXsXhYWF+Oyzz1BZWYn6+vouiQ9jjLGXp/FHjkDfLj7+4Ycforq6GomJiaisrISpqSl8fX0RHByMfv36IT4+HjExMZg5cyZMTEwwefJkLF26FGfOnOnU2puamgAAVVVVndqeMcbY//5mNv8NbYkLj7+ivlJ8PDc3FwEBAb06B8YY01RpaWlwcnJq1f5GHDn2pL5WfFwkEiEtLQ2/+93vWl1IxBhjrG1NTU14+PAhRCJRm/185PiSQkNDcfHiRYSHhyMwMLC3p8MYY6wbcHJkjDHGWtD4q1UZY4yxrsbJkTHGGGuBkyNjjDHWAidHxhhjrAVOjowxxlgLnBwZY4yxFjg5arCmpibExsbCzc0NEokEoaGhePToUW9Pq1s9evQIK1euhJubG5ycnBAUFKT27EuZTIb3338fdnZ2mDt3Lq5du6b2/rKyMgQFBUEikWDKlClITU1V6+9MTC9cuIBZs2bBzs4OXl5eyMrK6r4Fd5OrV6/C2toa2dnZQltH66qurkZYWBicnJzg6uqKLVu2CM81bbZ37164u7tDLBZjyZIlKC0tVeu/fv06/P39IRaLIZVKcezYsW5bY1c7fPiw8LPl6+uLS5cuCX0cu7Y9e/YM69evF35fly1bBrlcLvT36bgR01hxcXE0ceJEunDhAhUUFJCfnx/5+/v39rS6TVNTE82bN4/mzp1LP/zwAxUVFVFoaCi5urrS48eP6eLFi2RjY0P/+te/SC6X0+rVq8nJyYmqq6uJiEipVNIf//hH+uSTT6ioqIhkMhmJxWI6ePCgsI+OYlpUVEQikYiSkpJILpdTXFwc2djY0O3bt3s8Hq+qtraWpk+fTpaWlnT58mUi6ty65s+fTwsWLKAbN27Q+fPn6b333qOtW7cK/YcOHSKJRELffPMN3bx5kz7++GOaNm0aKZVKIiKqrq4mFxcXio6OJrlcTvv27SNra2v67rvvejYAryAjI4NsbGzo8OHDVFpaShs3biR7e3sqLy/n2LVj1apV9MEHH1Bubi7J5XIKCQmhKVOm0PPnz/t83Dg5aiilUkkSiYSOHj0qtJWXl5OlpSVduXKlF2fWfX788UeytLQkuVwutCmVShKLxfT111/T0qVLaeXKlUJfU1MTTZs2jXbs2EFERCdOnCB7e3tSKBTCNomJiSSVSoWxOoppVFQUBQYGqs0rMDCQ1qxZ0/UL7ibNa3gxOXa0rry8PLK0tKS7d+8K/RkZGSSRSIQ/RFKplLZt2yb0KxQKsre3J5lMRkREycnJ5OHhQU1NTcI2ERERtGTJku5ZaBdRqVTk7u5O8fHxQltTUxN5e3uTTCbj2LXDxcWF9u3bJ7wuKioiS0tLKigo6PNx49OqGurmzZuora2Fi4uL0DZy5EiMGDECubm5vTiz7mNmZoadO3fC3NxcaNPS0gIR4enTp8jLy1OLh7a2NpydnYV45ObmQiQSwcDAQNjGxcUFpaWlePToUadimpubq9YPAO+++67GxDwrKwvnz59vVTC/o3Xl5uZixIgRGDVqlNDv4uKC2tpa3LhxA9XV1SgtLVUbw8DAACKRSG0MZ2dnaGtrq42Rl5cHlUrV5WvtKiUlJbh37x48PT2FNm1tbRw/fhxeXl4cu3aYmJjg1KlTqK6uRn19PY4cOYLBgwdj1KhRfT5unBw1VPPjVt566y219mHDhr2xj68aMmQIpk6dqvaDvn//fiiVSohEIjx79qzdeFRVVWHYsGGt+gGgsrKyUzGtqqrS2Jg/fvwYq1evxoYNGzB48GC1vo7Wdf/+/W6LXV1dHZ48efKaq+s+zZ9h1dTUYNGiRXB1dUVAQADy8vIAcOzas379elRVVWHChAmwt7fHoUOHkJKSAiMjoz4fN06OGqqurg7a2trQ1dVVa9fT04NSqeylWfWss2fPYuvWrViyZAlGjBgBAOjfv7/aNrq6ukI8nj9/3qpfT08PAKBUKjsV0+fPnwvvaau/L/v888/h4eGByZMnt+rraF11dXVtxlZLS0uIHdA6/p2JHYA+/XBvhUIBAIiIiICfnx9SU1MxduxYLF68GMXFxRy7dpSVlWHo0KFISUlBeno63NzcEBoaiqqqqj4fN35klYYaMGAAVCoVGhsb0a/f//4Z6+vrMXDgwF6cWc/IyMhAVFQUPD098de//hVPnz4F0PoHvqGhQYjHgAEDWvU3v9bX1+9UTPv374+GhoZWY/T1mH/99dcoLCyETCZrs7+jdbUVu4aGBhCRELvm97zMGM2v+3L8mv+zFBwcDC8vLwCAtbU1rly5gvT0dI7dbygvL0dUVBQOHDgAe3t7AEBsbCw8PT2xd+/ePh83PnLUUGZmZgCAhw8fqrU/ePCg1WmEN82OHTsQGRkJf39/bN68Gdra2jA2Noa+vj4ePHigtu2L8Rg+fHib8QJ+PTXTmZiamZm1u4++KiMjA/fv3xduUfnggw8AAMuXL8fatWs7XFdXxO63xtDX14ehoWEXrbTrNZ/Ks7S0FNq0tLRgYWGBiooKjt1vKCgoQFNTk9rzEnV1dTF+/HiUlZX1+bhxctRQVlZWMDAwwPfffy+0VVRU4N69e3B2du7FmXWvXbt2IT4+HqGhoYiKioKWlhaAX/9YSSQS5OTkCNuqVCrk5OQI8XB0dERBQYFwOgYAsrOzYW5uDlNT007F1NHRUW0fzWO09STxvuRvf/sbTp48iWPHjuHYsWPC/Z0bNmxAWFhYh+tydHREeXk5Kisr1foNDAxgZWUFU1NTvPPOO2qxq62tRUFBgVrscnNzQS88JS87OxsODg5qnyP3NTY2NtDX18f169eFNiJCcXExRo0axbH7DcOHDwcA3Lp1S2hrjts777zT9+PW+YtyWV+zZcsWmjBhAmVlZQn35LW8NPpNcuPGDRo/fjxFRkbSgwcP1L5qa2spKyuLrK2t6auvvhLuc3RxcRHuc6yrqyN3d3f685//TLdu3aITJ06QWCxWu3Wjo5jevHmTbGxsKCEhgeRyOcXHx5Otra3a7SWaoLKyUu1Wjo7WpVKpaO7cuTRv3jwqKCig8+fPk6urq9pl9AcOHCB7e3vKzMykW7du0ccff0xSqVS47P7hw4fk6OhIUVFRwj1nNjY29J///KfnA/CS4uLiyNnZmU6fPk137tyhL774gmxtbam4uJhj9xsaGxtp3rx5NHPmTMrJySG5XE5RUVFkb29PFRUVfT5unBw1WENDA23atIlcXFzIwcGBwsLChETwJoqNjSVLS8s2v7Zv305EREeOHCEPDw+ytbUVfqleVFxcTAsXLiRbW1uaOnUq7d27V62/MzH99ttvydPTk0QiEXl7e9PFixe7d+HdoGVyJOp4XQ8ePKCQkBASi8U0YcIEio2NVbt/jIho586dNHHiRLK3t6elS5eq3aNGRJSfn0+zZ88mkUhEUqmUMjMzu2+RXUilUlFycjJNmTKFRCIR+fn5UU5OjtDPsWtbdXU1rV69miZNmkSOjo60ePFiunHjhtDfl+OmRfTC8SZjjDHG+DNHxhhjrCVOjowxxlgLnBwZY4yxFjg5MsYYYy1wcmSMMcZa4OTIGGOMtcC1VRljbaqvr8f+/ftx4sQJlJWVYeDAgbCzs8Nf/vIX2Nra9tg88vPzoVKp4Ojo2GP7ZIyPHBljrdTV1SEgIADp6elYunSpUHLO2NgYAQEBuHz5co/NJTAwEGVlZT22P8YAPnJkjLUhPj4epaWlyMzMVCuq/uWXX6K6uhrr169HZmamUNu2O3GdEtYb+MiRMaamvr4eGRkZmDNnTptPG1m7di1iY2OhpaWFn376CeHh4XB1dYVEIkFISAjKy8uFbT08PJCUlKT2/hfbEhMTERQUhO3bt8PNzQ3Ozs4IDg7G/fv3hW2bmpoQGRmJhQsXduOqGVPHyZExpqa8vBw1NTUQi8Vt9o8aNQpWVlZQKBSYP38+nj59itTUVOzfvx+//PILAgMD8csvv3R6f9nZ2bh16xb27NmDuLg45OfnY9u2bQCAI0eOQEdHB6tWrUJiYmKXrI+xzuDkyBhTU1NTAwAwMjJqd7vjx4+jpqYGW7duhY2NDUQiERISEvD06dPffKhyW4gIGzduxNixY+Hm5gZvb29cvXoVAGBiYgIAMDQ0hLGx8SuuiLGXx8mRMaZmyJAhAIAnT560u11RUREsLCzUkpaJiQnGjBmD27dvd3p/Q4cOxaBBg4TXRkZGrZ4Qz1hP4+TIGFMzevRomJqa4ocffmizPzs7G8HBwVAqlW32q1Qq6Orq/ub4jY2Naq/19PRabcMX4bDexsmRMaZGW1sbPj4+OHr0qHBhTDMiQkpKCu7cuQMHBweUlJSoHWE+fvwYd+7cwZgxYwAAurq6UCgUQr9CoUB1dfVLzacnrohlrCVOjoyxVkJCQjBy5EgsWLAAmZmZKC8vR35+PkJDQ5GTk4MvvvgC3t7eMDExwYoVK1BYWIgff/wRK1asgJGREWbMmAEAsLe3x8mTJ5Gfn4+ioiJERERAR0fnpeZiYGAAuVz+0kmVsdfByZEx1oqBgQG++uorzJw5E3//+98xc+ZMfPLJJ1CpVDh48CCcnJzQv39/7N69G3p6eggICMDixYthaGiItLQ04WKeFStWwMrKCh999BGWLFkCBwcHODg4vNRcli9fjvT0dAQFBXXHUhlrkxbxyX3GGGNMDR85MsYYYy1wcmSMMcZa4OTIGGOMtcDJkTHGGGuBkyNjjDHWAidHxhhjrAVOjowxxlgLnBwZY4yxFjg5MsYYYy38F54EI8pGlowQAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# license\n",
    "df_sub = df_cord[df_cord.license.isin(df_cord.license.value_counts()[:30].index.tolist())]\n",
    "b = sns.countplot(y=\"license\", data=df_sub, order=df_sub['license'].value_counts().index)\n",
    "#b.axes.set_title(\"Title\",fontsize=50)\n",
    "b.set_xlabel(\"Count\",fontsize=15)\n",
    "b.set_ylabel(\"License\",fontsize=15)\n",
    "b.tick_params(labelsize=15)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Prepare tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "# the main table: pub"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "pub_table_columns = ['title','abstract','publication_year','publication_month','journal','volume','issue','pages','doi','pmid','pmcid','dimensions_id','arxiv_id']\n",
    "\n",
    "df_pub = df_dimensions[pub_table_columns].append(df_cord[pub_table_columns], ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pub[\"title\"] = df_pub[\"title\"].apply(sanitize_string)\n",
    "df_pub[\"abstract\"] = df_pub[\"abstract\"].apply(sanitize_string)\n",
    "df_pub[\"doi\"] = df_pub[\"doi\"].apply(str.lower)\n",
    "df_pub[\"pmid\"] = df_pub[\"pmid\"].apply(str.lower)\n",
    "df_pub[\"pmcid\"] = df_pub[\"pmcid\"].apply(str.lower)\n",
    "df_pub[\"dimensions_id\"] = df_pub[\"dimensions_id\"].apply(str.lower)\n",
    "df_pub[\"arxiv_id\"] = df_pub[\"arxiv_id\"].apply(str.lower)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(231384, 13)"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pub.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(28994, 13)"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pub[(df_pub.doi==\"\") & (df_pub.pmid==\"\") & (df_pub.pmcid==\"\") & (df_pub.dimensions_id==\"\") & (df_pub.arxiv_id==\"\")].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "# check to have at least one valid identifier per publication\n",
    "# we drop publications which do not: hopefully, they will be equipped with an identifier in future releases\n",
    "\n",
    "df_pub = df_pub[~((df_pub.doi==\"\") & (df_pub.pmid==\"\") & (df_pub.pmcid==\"\") & (df_pub.dimensions_id==\"\") & (df_pub.arxiv_id==\"\"))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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>title</th>\n",
       "      <th>abstract</th>\n",
       "      <th>publication_year</th>\n",
       "      <th>publication_month</th>\n",
       "      <th>journal</th>\n",
       "      <th>volume</th>\n",
       "      <th>issue</th>\n",
       "      <th>pages</th>\n",
       "      <th>doi</th>\n",
       "      <th>pmid</th>\n",
       "      <th>pmcid</th>\n",
       "      <th>dimensions_id</th>\n",
       "      <th>arxiv_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [title, abstract, publication_year, publication_month, journal, volume, issue, pages, doi, pmid, pmcid, dimensions_id, arxiv_id]\n",
       "Index: []"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pub[df_pub.doi==\"0.1126/science.abb7331\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "# drop duplicates, first on dois then pmids then pmcids. We need this to keep empty values!\n",
    "df_tmp = df_pub[df_pub.doi==\"\"]\n",
    "df_pub1 = df_pub[df_pub.doi!=\"\"].groupby('doi').first()\n",
    "df_pub1.reset_index(inplace=True)\n",
    "df_tmp2 = df_tmp[df_tmp.pmid==\"\"]\n",
    "df_pub2 = df_tmp[df_tmp.pmid!=\"\"].groupby('pmid').first()\n",
    "df_pub2.reset_index(inplace=True)\n",
    "df_tmp3 = df_tmp2[df_tmp2.pmcid==\"\"]\n",
    "df_pub3 = df_tmp2[df_tmp2.pmcid!=\"\"].groupby('pmcid').first()\n",
    "df_pub3.reset_index(inplace=True)\n",
    "df_tmp4 = df_tmp3[df_tmp3.arxiv_id==\"\"]\n",
    "df_pub4 = df_tmp3[df_tmp3.arxiv_id!=\"\"].groupby('arxiv_id').first()\n",
    "df_pub4.reset_index(inplace=True)\n",
    "df_pub5 = df_tmp4[df_tmp4.dimensions_id!=\"\"].groupby('dimensions_id').first()\n",
    "df_pub5.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>doi</th>\n",
       "      <th>title</th>\n",
       "      <th>abstract</th>\n",
       "      <th>publication_year</th>\n",
       "      <th>publication_month</th>\n",
       "      <th>journal</th>\n",
       "      <th>volume</th>\n",
       "      <th>issue</th>\n",
       "      <th>pages</th>\n",
       "      <th>pmid</th>\n",
       "      <th>pmcid</th>\n",
       "      <th>dimensions_id</th>\n",
       "      <th>arxiv_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [doi, title, abstract, publication_year, publication_month, journal, volume, issue, pages, pmid, pmcid, dimensions_id, arxiv_id]\n",
       "Index: []"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pub1[df_pub1.doi==\"0.1126/science.abb7331\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pub = pd.concat([df_pub1,df_pub2,df_pub3,df_pub4,df_pub5])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add PK and reset index\n",
    "df_pub.reset_index(drop=True,inplace=True)\n",
    "df_pub[\"pub_id\"] = df_pub.index.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(160656, 14)"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pub.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>doi</th>\n",
       "      <th>title</th>\n",
       "      <th>abstract</th>\n",
       "      <th>publication_year</th>\n",
       "      <th>publication_month</th>\n",
       "      <th>journal</th>\n",
       "      <th>volume</th>\n",
       "      <th>issue</th>\n",
       "      <th>pages</th>\n",
       "      <th>pmid</th>\n",
       "      <th>pmcid</th>\n",
       "      <th>dimensions_id</th>\n",
       "      <th>arxiv_id</th>\n",
       "      <th>pub_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>140612</th>\n",
       "      <td>10.3934/mine.2021013</td>\n",
       "      <td>A simple SIR model with a large set of asympto...</td>\n",
       "      <td>There is increasing evidence that one of the m...</td>\n",
       "      <td>2020</td>\n",
       "      <td>3</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2003.08720</td>\n",
       "      <td>140612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158867</th>\n",
       "      <td></td>\n",
       "      <td>Predicting the evolution of the COVID-19 epide...</td>\n",
       "      <td>The presence of a large number of infected ind...</td>\n",
       "      <td>2020</td>\n",
       "      <td>5</td>\n",
       "      <td>arXiv</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>pub.1127904057</td>\n",
       "      <td>2003.08720</td>\n",
       "      <td>158867</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         doi  \\\n",
       "140612  10.3934/mine.2021013   \n",
       "158867                         \n",
       "\n",
       "                                                    title  \\\n",
       "140612  A simple SIR model with a large set of asympto...   \n",
       "158867  Predicting the evolution of the COVID-19 epide...   \n",
       "\n",
       "                                                 abstract publication_year  \\\n",
       "140612  There is increasing evidence that one of the m...             2020   \n",
       "158867  The presence of a large number of infected ind...             2020   \n",
       "\n",
       "       publication_month journal volume issue pages pmid pmcid  \\\n",
       "140612                 3                                         \n",
       "158867                 5   arXiv                                 \n",
       "\n",
       "         dimensions_id    arxiv_id  pub_id  \n",
       "140612                  2003.08720  140612  \n",
       "158867  pub.1127904057  2003.08720  158867  "
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pub[df_pub.arxiv_id==\"2003.08720\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "# create other tables via joins\n",
    "\n",
    "df_datasource = pd.DataFrame.from_dict({\"source\":[\"CORD19\",\"Dimensions\"],\"url\":[\"https://pages.semanticscholar.org/coronavirus-research\",\"https://docs.google.com/spreadsheets/d/1-kTZJZ1GAhJ2m4GAIhw1ZdlgO46JpvX0ZQa232VWRmw/edit#gid=2034285255\"]})\n",
    "df_cord_metadata = df_cord[['source','license','ms_academic_id','who_covidence','doi','pmid','pmcid','sha','arxiv_id']]\n",
    "df_dimensions_metadata = df_dimensions[['dimensions_id', 'doi', 'pmid', 'pmcid','arxiv_id', 'source_uid', 'mesh_terms',\n",
    "       'open_access', 'publication_type', 'dimensions_url']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cord_metadata[\"doi\"] = df_cord_metadata[\"doi\"].apply(str.lower)\n",
    "df_cord_metadata[\"pmid\"] = df_cord_metadata[\"pmid\"].apply(str.lower)\n",
    "df_cord_metadata[\"pmcid\"] = df_cord_metadata[\"pmcid\"].apply(str.lower)\n",
    "df_dimensions_metadata[\"doi\"] = df_dimensions_metadata[\"doi\"].apply(str.lower)\n",
    "df_dimensions_metadata[\"pmid\"] = df_dimensions_metadata[\"pmid\"].apply(str.lower)\n",
    "df_dimensions_metadata[\"pmcid\"] = df_dimensions_metadata[\"pmcid\"].apply(str.lower)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "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>source</th>\n",
       "      <th>url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CORD19</td>\n",
       "      <td>https://pages.semanticscholar.org/coronavirus-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Dimensions</td>\n",
       "      <td>https://docs.google.com/spreadsheets/d/1-kTZJZ...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       source                                                url\n",
       "0      CORD19  https://pages.semanticscholar.org/coronavirus-...\n",
       "1  Dimensions  https://docs.google.com/spreadsheets/d/1-kTZJZ..."
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_datasource.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "# CORD19 metadata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(169821, 9)"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cord_metadata.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>doi</th>\n",
       "      <th>title</th>\n",
       "      <th>abstract</th>\n",
       "      <th>publication_year</th>\n",
       "      <th>publication_month</th>\n",
       "      <th>journal</th>\n",
       "      <th>volume</th>\n",
       "      <th>issue</th>\n",
       "      <th>pages</th>\n",
       "      <th>pmid</th>\n",
       "      <th>pmcid</th>\n",
       "      <th>dimensions_id</th>\n",
       "      <th>arxiv_id</th>\n",
       "      <th>pub_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [doi, title, abstract, publication_year, publication_month, journal, volume, issue, pages, pmid, pmcid, dimensions_id, arxiv_id, pub_id]\n",
       "Index: []"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pub[df_pub.doi==\"0.1126/science.abb7331\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "#We need this to keep empty values!\n",
    "\n",
    "df_tmp = df_cord_metadata[df_cord_metadata.doi==\"\"]\n",
    "df_cord_metadata1 = pd.merge(df_cord_metadata[df_cord_metadata.doi!=\"\"], df_pub[['pub_id','doi']],  how='inner', left_on=['doi'], right_on=['doi'])\n",
    "df_tmp2 = df_tmp[df_tmp.pmid==\"\"]\n",
    "df_cord_metadata2 = pd.merge(df_tmp[df_tmp.pmid!=\"\"], df_pub[['pub_id','pmid']],  how='inner', left_on=['pmid'], right_on=['pmid'])\n",
    "df_tmp3 = df_tmp2[df_tmp2.pmcid==\"\"]\n",
    "df_cord_metadata3 = pd.merge(df_tmp2[df_tmp2.pmcid!=\"\"], df_pub[['pub_id','pmcid']],  how='inner', left_on=['pmcid'], right_on=['pmcid'])\n",
    "df_cord_metadata4 = pd.merge(df_tmp3[df_tmp3.arxiv_id!=\"\"], df_pub[['pub_id','arxiv_id']],  how='inner', left_on=['arxiv_id'], right_on=['arxiv_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cord_metadata1 = df_cord_metadata1.groupby(\"doi\").first()\n",
    "df_cord_metadata1.reset_index(inplace=True)\n",
    "df_cord_metadata2 = df_cord_metadata2.groupby(\"pmid\").first()\n",
    "df_cord_metadata2.reset_index(inplace=True)\n",
    "df_cord_metadata3 = df_cord_metadata3.groupby(\"pmcid\").first()\n",
    "df_cord_metadata3.reset_index(inplace=True)\n",
    "df_cord_metadata4 = df_cord_metadata4.groupby(\"arxiv_id\").first()\n",
    "df_cord_metadata4.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cord_metadata = pd.concat([df_cord_metadata1,df_cord_metadata2,df_cord_metadata3,df_cord_metadata4])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(140351, 10)"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cord_metadata.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "b2de4375e1b84e7ca074eb3c60279e03",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(FloatProgress(value=0.0, max=78741.0), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "# read full texts in\n",
    "folders = ['document_parses/pdf_json']\n",
    "shas = list()\n",
    "full_texts = list()\n",
    "\n",
    "for folder in folders:\n",
    "    for root, dirs, files in os.walk(os.path.join(cord19_folder,folder)):\n",
    "        for file in tqdm(files):\n",
    "            if \".json\" in file: # read\n",
    "                data = json.loads(codecs.open(os.path.join(root,file)).read())\n",
    "                sha = data[\"paper_id\"]\n",
    "                full_text = \" \".join(sanitize_string(section[\"text\"]) for section in data[\"body_text\"])\n",
    "                shas.append(sha)\n",
    "                full_texts.append(full_text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cord_fulltext = pd.DataFrame.from_dict({\"sha\":shas,\"full_text\":full_texts})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(78741, 2)"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cord_fulltext.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cord_metadata = pd.merge(df_cord_metadata, df_cord_fulltext,  how='left', left_on=['sha'], right_on=['sha'])\n",
    "df_cord_metadata = df_cord_metadata.fillna('')\n",
    "df_cord_metadata.rename(columns={\"id\":\"pub_id\"},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>doi</th>\n",
       "      <th>source</th>\n",
       "      <th>license</th>\n",
       "      <th>ms_academic_id</th>\n",
       "      <th>who_covidence</th>\n",
       "      <th>pmid</th>\n",
       "      <th>pmcid</th>\n",
       "      <th>sha</th>\n",
       "      <th>arxiv_id</th>\n",
       "      <th>pub_id</th>\n",
       "      <th>full_text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.0376/cma.j.issn.0376-2491.2020.0002</td>\n",
       "      <td>Medline; WHO</td>\n",
       "      <td>unk</td>\n",
       "      <td></td>\n",
       "      <td>#615</td>\n",
       "      <td>32036640</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.1001/amajethics.2020.344</td>\n",
       "      <td>Medline</td>\n",
       "      <td>unk</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>32345430</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10.1001/amajethics.2020.441</td>\n",
       "      <td>Medline</td>\n",
       "      <td>unk</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>32449663</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>3</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10.1001/amajethics.2020.55</td>\n",
       "      <td>Medline</td>\n",
       "      <td>unk</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>31958392</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>4</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10.1001/archdermatol.2010.96</td>\n",
       "      <td>Medline</td>\n",
       "      <td>unk</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>20566928</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>5</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      doi        source license  \\\n",
       "0  10.0376/cma.j.issn.0376-2491.2020.0002  Medline; WHO     unk   \n",
       "1             10.1001/amajethics.2020.344       Medline     unk   \n",
       "2             10.1001/amajethics.2020.441       Medline     unk   \n",
       "3              10.1001/amajethics.2020.55       Medline     unk   \n",
       "4            10.1001/archdermatol.2010.96       Medline     unk   \n",
       "\n",
       "  ms_academic_id who_covidence      pmid pmcid sha arxiv_id  pub_id full_text  \n",
       "0                         #615  32036640                          1            \n",
       "1                               32345430                          2            \n",
       "2                               32449663                          3            \n",
       "3                               31958392                          4            \n",
       "4                               20566928                          5            "
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cord_metadata.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>doi</th>\n",
       "      <th>source</th>\n",
       "      <th>license</th>\n",
       "      <th>ms_academic_id</th>\n",
       "      <th>who_covidence</th>\n",
       "      <th>pmid</th>\n",
       "      <th>pmcid</th>\n",
       "      <th>sha</th>\n",
       "      <th>arxiv_id</th>\n",
       "      <th>pub_id</th>\n",
       "      <th>full_text</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3260</th>\n",
       "      <td>10.1002/jmv.25885</td>\n",
       "      <td>ArXiv; Medline; PMC</td>\n",
       "      <td>no-cc</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>32329522</td>\n",
       "      <td>pmc7264574</td>\n",
       "      <td>19bc551f5c9f522311412afff47f9569173de02f</td>\n",
       "      <td>2003.06933</td>\n",
       "      <td>3756</td>\n",
       "      <td>. 3, 4 The name originates from the morphology...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18442</th>\n",
       "      <td>10.1007/s10494-011-9345-0</td>\n",
       "      <td>ArXiv</td>\n",
       "      <td>arxiv</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>1305.2885</td>\n",
       "      <td>19690</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19233</th>\n",
       "      <td>10.1007/s10955-013-0690-3</td>\n",
       "      <td>ArXiv</td>\n",
       "      <td>arxiv</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>67326b132f0b8258c4cd41a517b2d65404d58901</td>\n",
       "      <td>1211.2163</td>\n",
       "      <td>20490</td>\n",
       "      <td>that the population within a single subpopulat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19234</th>\n",
       "      <td>10.1007/s10955-014-1024-9</td>\n",
       "      <td>ArXiv; Medline; PMC</td>\n",
       "      <td>no-cc</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>26074625</td>\n",
       "      <td>pmc4457089</td>\n",
       "      <td>2305deb2b01f516b57b37719a3c62804637a9139; 5354...</td>\n",
       "      <td>1402.7011</td>\n",
       "      <td>20491</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19579</th>\n",
       "      <td>10.1007/s11192-020-03525-2</td>\n",
       "      <td>ArXiv; PMC; WHO</td>\n",
       "      <td>no-cc</td>\n",
       "      <td></td>\n",
       "      <td>#361454</td>\n",
       "      <td></td>\n",
       "      <td>pmc7246301</td>\n",
       "      <td>f760dc111aa33ab7a50d3944737d78f0bb4eb08e; b317...</td>\n",
       "      <td>2005.09433</td>\n",
       "      <td>20840</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              doi               source license ms_academic_id  \\\n",
       "3260            10.1002/jmv.25885  ArXiv; Medline; PMC   no-cc                  \n",
       "18442   10.1007/s10494-011-9345-0                ArXiv   arxiv                  \n",
       "19233   10.1007/s10955-013-0690-3                ArXiv   arxiv                  \n",
       "19234   10.1007/s10955-014-1024-9  ArXiv; Medline; PMC   no-cc                  \n",
       "19579  10.1007/s11192-020-03525-2      ArXiv; PMC; WHO   no-cc                  \n",
       "\n",
       "      who_covidence      pmid       pmcid  \\\n",
       "3260                 32329522  pmc7264574   \n",
       "18442                                       \n",
       "19233                                       \n",
       "19234                26074625  pmc4457089   \n",
       "19579       #361454            pmc7246301   \n",
       "\n",
       "                                                     sha    arxiv_id  pub_id  \\\n",
       "3260            19bc551f5c9f522311412afff47f9569173de02f  2003.06933    3756   \n",
       "18442                                                      1305.2885   19690   \n",
       "19233           67326b132f0b8258c4cd41a517b2d65404d58901   1211.2163   20490   \n",
       "19234  2305deb2b01f516b57b37719a3c62804637a9139; 5354...   1402.7011   20491   \n",
       "19579  f760dc111aa33ab7a50d3944737d78f0bb4eb08e; b317...  2005.09433   20840   \n",
       "\n",
       "                                               full_text  \n",
       "3260   . 3, 4 The name originates from the morphology...  \n",
       "18442                                                     \n",
       "19233  that the population within a single subpopulat...  \n",
       "19234                                                     \n",
       "19579                                                     "
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cord_metadata[df_cord_metadata.arxiv_id!=\"\"].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Dimensions metadata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_tmp = df_dimensions_metadata[df_dimensions_metadata.dimensions_id==\"\"]\n",
    "df_dimensions_metadata1 = pd.merge(df_dimensions_metadata[df_dimensions_metadata.dimensions_id!=\"\"], df_pub[['pub_id','dimensions_id']],  how='inner', left_on=['dimensions_id'], right_on=['dimensions_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_dimensions_metadata1 = df_dimensions_metadata1.groupby(\"dimensions_id\").first()\n",
    "df_dimensions_metadata1.reset_index(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_dimensions_metadata = pd.concat([df_dimensions_metadata1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(61297, 11)"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dimensions_metadata.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_dimensions_metadata.rename(columns={\"id\":\"pub_id\"},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create datasource tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "cord_source_id = df_datasource[df_datasource.source==\"CORD19\"].index.values[0]\n",
    "dimensions_source_id = df_datasource[df_datasource.source==\"Dimensions\"].index.values[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_cord_metadata[\"source_id\"] = cord_source_id\n",
    "df_dimensions_metadata[\"source_id\"] = dimensions_source_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pub_to_datasource = df_cord_metadata[[\"pub_id\",\"source_id\"]]\n",
    "df_pub_to_datasource = df_pub_to_datasource.append(df_dimensions_metadata[[\"pub_id\",\"source_id\"]],ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pub_to_datasource.drop_duplicates(inplace=True)\n",
    "df_pub_to_datasource.rename(columns={\"source_id\":\"datasource_id\"},inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(201599, 2)"
      ]
     },
     "execution_count": 94,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pub_to_datasource.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "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>pub_id</th>\n",
       "      <th>datasource_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>21495</th>\n",
       "      <td>22787</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       pub_id  datasource_id\n",
       "21495   22787              0"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pub_to_datasource[df_pub_to_datasource.pub_id==22787]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove unnecessary columns\n",
    "df_cord_metadata.drop(columns=['doi','pmid','pmcid','arxiv_id','source_id'],inplace=True)\n",
    "df_dimensions_metadata.drop(columns=['doi','pmid','pmcid','arxiv_id','source_id'],inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "# reset all indexes which will become PKs\n",
    "df_cord_metadata.reset_index(drop=True,inplace=True)\n",
    "df_dimensions_metadata.reset_index(drop=True,inplace=True)\n",
    "df_datasource.reset_index(drop=True,inplace=True)\n",
    "df_cord_metadata[\"cord19_metadata_id\"] = df_cord_metadata.index.values\n",
    "df_dimensions_metadata[\"dimensions_metadata_id\"] = df_dimensions_metadata.index.values\n",
    "df_datasource[\"datasource_metadata_id\"] = df_datasource.index.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [],
   "source": [
    "# make numeric where needed\n",
    "df_pub[\"publication_year\"] = pd.to_numeric(df_pub[\"publication_year\"])\n",
    "df_pub[\"publication_month\"] = pd.to_numeric(df_pub[\"publication_month\"])\n",
    "df_pub[\"pmid\"] = pd.to_numeric(df_pub[\"pmid\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [],
   "source": [
    "# add timestamp\n",
    "df_pub[\"timestamp\"] = pd.Timestamp.now()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [],
   "source": [
    "# clean-up text (optional)\n",
    "replaces = [\"\"]\n",
    "\n",
    "def clean_up(txt):\n",
    "    for r in replaces:\n",
    "        txt = txt.replace(r,\"\")\n",
    "    return txt.encode('utf8', 'ignore').decode('utf8')\n",
    "df_pub[\"abstract\"] = [clean_up(a) for a in df_pub[\"abstract\"].values]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>doi</th>\n",
       "      <th>title</th>\n",
       "      <th>abstract</th>\n",
       "      <th>publication_year</th>\n",
       "      <th>publication_month</th>\n",
       "      <th>journal</th>\n",
       "      <th>volume</th>\n",
       "      <th>issue</th>\n",
       "      <th>pages</th>\n",
       "      <th>pmid</th>\n",
       "      <th>pmcid</th>\n",
       "      <th>dimensions_id</th>\n",
       "      <th>arxiv_id</th>\n",
       "      <th>pub_id</th>\n",
       "      <th>timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10-5830-cvja-2020-016</td>\n",
       "      <td>Coronavirus disease 2019 (COVID-19) and simult...</td>\n",
       "      <td>Coronavirus disease 2019 (COVID-19) is a recen...</td>\n",
       "      <td>2020.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>CardioVascular Journal of Africa</td>\n",
       "      <td>31</td>\n",
       "      <td></td>\n",
       "      <td>1-4</td>\n",
       "      <td>32490506.0</td>\n",
       "      <td></td>\n",
       "      <td>pub.1128171889</td>\n",
       "      <td></td>\n",
       "      <td>0</td>\n",
       "      <td>2020-07-02 10:54:27.347501</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.0376/cma.j.issn.0376-2491.2020.0002</td>\n",
       "      <td>[Ten hot issues of breast cancer under the nov...</td>\n",
       "      <td></td>\n",
       "      <td>2020.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>Chinese medical journal</td>\n",
       "      <td>100</td>\n",
       "      <td>0</td>\n",
       "      <td>e002</td>\n",
       "      <td>32036640.0</td>\n",
       "      <td></td>\n",
       "      <td>pub.1124777091</td>\n",
       "      <td></td>\n",
       "      <td>1</td>\n",
       "      <td>2020-07-02 10:54:27.347501</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10.1001/amajethics.2020.344</td>\n",
       "      <td>Cohesion in Distancing.</td>\n",
       "      <td>In isolation, we are physically apart; in soli...</td>\n",
       "      <td>2020.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>The AMA Journal of Ethic</td>\n",
       "      <td>22</td>\n",
       "      <td>4</td>\n",
       "      <td>e344-345</td>\n",
       "      <td>32345430.0</td>\n",
       "      <td></td>\n",
       "      <td>pub.1127182341</td>\n",
       "      <td></td>\n",
       "      <td>2</td>\n",
       "      <td>2020-07-02 10:54:27.347501</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10.1001/amajethics.2020.441</td>\n",
       "      <td>Salvation in a Time of Plague.</td>\n",
       "      <td>Health workers offer their skills and care to ...</td>\n",
       "      <td>2020.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>The AMA Journal of Ethic</td>\n",
       "      <td>22</td>\n",
       "      <td>5</td>\n",
       "      <td>e441-445</td>\n",
       "      <td>32449663.0</td>\n",
       "      <td></td>\n",
       "      <td>pub.1127905371</td>\n",
       "      <td></td>\n",
       "      <td>3</td>\n",
       "      <td>2020-07-02 10:54:27.347501</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10.1001/amajethics.2020.55</td>\n",
       "      <td>What Should Health Science Journalists Do in E...</td>\n",
       "      <td>Journalists have long covered outbreaks of inf...</td>\n",
       "      <td>2020.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>AMA journal of ethics</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>31958392.0</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>4</td>\n",
       "      <td>2020-07-02 10:54:27.347501</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                      doi  \\\n",
       "0                   10-5830-cvja-2020-016   \n",
       "1  10.0376/cma.j.issn.0376-2491.2020.0002   \n",
       "2             10.1001/amajethics.2020.344   \n",
       "3             10.1001/amajethics.2020.441   \n",
       "4              10.1001/amajethics.2020.55   \n",
       "\n",
       "                                               title  \\\n",
       "0  Coronavirus disease 2019 (COVID-19) and simult...   \n",
       "1  [Ten hot issues of breast cancer under the nov...   \n",
       "2                            Cohesion in Distancing.   \n",
       "3                     Salvation in a Time of Plague.   \n",
       "4  What Should Health Science Journalists Do in E...   \n",
       "\n",
       "                                            abstract  publication_year  \\\n",
       "0  Coronavirus disease 2019 (COVID-19) is a recen...            2020.0   \n",
       "1                                                               2020.0   \n",
       "2  In isolation, we are physically apart; in soli...            2020.0   \n",
       "3  Health workers offer their skills and care to ...            2020.0   \n",
       "4  Journalists have long covered outbreaks of inf...            2020.0   \n",
       "\n",
       "   publication_month                           journal volume issue     pages  \\\n",
       "0                5.0  CardioVascular Journal of Africa     31             1-4   \n",
       "1                2.0           Chinese medical journal    100     0      e002   \n",
       "2                4.0          The AMA Journal of Ethic     22     4  e344-345   \n",
       "3                5.0          The AMA Journal of Ethic     22     5  e441-445   \n",
       "4                1.0             AMA journal of ethics                          \n",
       "\n",
       "         pmid pmcid   dimensions_id arxiv_id  pub_id  \\\n",
       "0  32490506.0        pub.1128171889                0   \n",
       "1  32036640.0        pub.1124777091                1   \n",
       "2  32345430.0        pub.1127182341                2   \n",
       "3  32449663.0        pub.1127905371                3   \n",
       "4  31958392.0                                      4   \n",
       "\n",
       "                   timestamp  \n",
       "0 2020-07-02 10:54:27.347501  \n",
       "1 2020-07-02 10:54:27.347501  \n",
       "2 2020-07-02 10:54:27.347501  \n",
       "3 2020-07-02 10:54:27.347501  \n",
       "4 2020-07-02 10:54:27.347501  "
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pub.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['source', 'url', 'datasource_metadata_id'], dtype='object')"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# reorder the columns to match the SQL schema\n",
    "\n",
    "df_datasource.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pub = df_pub[['pub_id', 'title', 'abstract', 'publication_year', 'publication_month', 'journal',\n",
    "       'volume', 'issue', 'pages', 'doi', 'pmid', 'pmcid', 'dimensions_id', 'arxiv_id',\n",
    "       'timestamp']]\n",
    "df_dimensions_metadata = df_dimensions_metadata[['dimensions_metadata_id', 'dimensions_id', 'source_uid', 'open_access',\n",
    "       'publication_type', 'dimensions_url', 'mesh_terms', 'pub_id']]\n",
    "df_cord_metadata = df_cord_metadata[[ 'cord19_metadata_id', 'source', 'license', 'ms_academic_id',\n",
    "       'who_covidence', 'sha', 'full_text', 'pub_id']]\n",
    "df_datasource = df_datasource[['datasource_metadata_id', 'source', 'url']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                 13400\n",
       "10.1111/medu.14217                   1\n",
       "10.1101/2020.04.16.20068312          1\n",
       "10.1136/bmj.m1975                    1\n",
       "10.1007/s00467-020-04585-5           1\n",
       "                                 ...  \n",
       "10.1080/0163660x.2020.1770959        1\n",
       "10.3201/eid1205.051424               1\n",
       "10.1016/s0165-2427(99)00100-2        1\n",
       "10.1371/journal.pone.0157820         1\n",
       "10.1186/s12938-016-0205-y            1\n",
       "Name: doi, Length: 147257, dtype: int64"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pub.doi.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "              158498\n",
       "2004.01714         2\n",
       "2006.08365         2\n",
       "2005.08922         2\n",
       "2005.14585         2\n",
       "               ...  \n",
       "2006.10488         1\n",
       "2005.12881         1\n",
       "2006.00765         1\n",
       "2004.10305         1\n",
       "2004.07742         1\n",
       "Name: arxiv_id, Length: 2134, dtype: int64"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pub.arxiv_id.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'57258    10.1016/s0140-6736(20)30607-3'"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pub[df_pub.doi == \"10.1016/s0140-6736(20)30607-3\"].doi.to_string()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['cord19_metadata_id', 'source', 'license', 'ms_academic_id',\n",
       "       'who_covidence', 'sha', 'full_text', 'pub_id'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_cord_metadata.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dump to CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [],
   "source": [
    "### Export the df_pub dataframe for further use\n",
    "\n",
    "df_pub.to_csv(\"datasets_output/df_pub.csv\", compression=\"gzip\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [],
   "source": [
    "# export TSV for ingestion\n",
    "\n",
    "df_pub.to_csv(\"datasets_output/sql_tables/pub.csv\",index=False,sep=\"\\t\",header=False)\n",
    "df_cord_metadata.to_csv(\"datasets_output/sql_tables/cord19_metadata.csv\",index=False,sep=\"\\t\",header=False)\n",
    "df_dimensions_metadata.to_csv(\"datasets_output/sql_tables/dimensions_metadata.csv\",index=False,sep=\"\\t\",header=False)\n",
    "df_datasource.to_csv(\"datasets_output/sql_tables/datasource.csv\",index=False,sep=\"\\t\",header=False)\n",
    "df_pub_to_datasource.to_csv(\"datasets_output/sql_tables/pub_datasource.csv\",index=False,sep=\"\\t\",header=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dump to MySQL\n",
    "\n",
    "Use this if you want to create a MySQL db."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dtype_dict = {'pub_id':Integer, 'title':String, 'abstract':String, 'publication_year':Integer, 'publication_month':Integer, 'journal':String,\n",
    "       'volume':String, 'issue':String, 'pages':String, 'doi':String, 'pmid':Integer, 'pmcid':String, 'timestamp':DateTime}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get API key\n",
    "import configparser\n",
    "config = configparser.ConfigParser()\n",
    "config.read(\"credentials/conf.ini\")\n",
    "mysql_username = config[\"MYSQL\"][\"username\"]\n",
    "mysql_password = config[\"MYSQL\"][\"password\"]\n",
    "mysql_database = config[\"MYSQL\"][\"database\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sqlEngine = create_engine('mysql+pymysql://%s:%s@127.0.0.1/%s'%(mysql_username,mysql_password,mysql_database), pool_recycle=3600)\n",
    "dbConnection = sqlEngine.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# main table\n",
    "table_name = \"pub\"\n",
    "try:\n",
    "    frame = df_pub.to_sql(table_name, dbConnection, if_exists='append', index=False, index_label=\"pub_id\", dtype=dtype_dict);\n",
    "except ValueError as vx:\n",
    "    print(vx)\n",
    "except Exception as ex:   \n",
    "    print(ex)\n",
    "else:\n",
    "    print(\"Table %s created successfully.\"%table_name);   \n",
    "finally:\n",
    "    dbConnection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sqlEngine = create_engine('mysql+pymysql://%s:%s@127.0.0.1/%s'%(mysql_username,mysql_password,mysql_database), pool_recycle=3600)\n",
    "dbConnection = sqlEngine.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# other tables\n",
    "try:\n",
    "    frame = df_cord_metadata.to_sql(\"cord19_metadata\", dbConnection, if_exists='append', index=True, index_label=\"cord19_metadata_id\")\n",
    "    frame = df_who_metadata.to_sql(\"who_metadata\", dbConnection, if_exists='append', index=True, index_label=\"who_metadata_id\")\n",
    "    frame = df_dimensions_metadata.to_sql(\"dimensions_metadata\", dbConnection, if_exists='append', index=True, index_label=\"dimensions_metadata_id\")\n",
    "    frame = df_datasource.to_sql(\"datasource\", dbConnection, if_exists='append', index=True, index_label=\"datasource_id\")\n",
    "except ValueError as vx:\n",
    "    print(vx)\n",
    "except Exception as ex:   \n",
    "    print(ex)\n",
    "else:\n",
    "    print(\"Tables created successfully.\");   \n",
    "finally:\n",
    "    dbConnection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sqlEngine = create_engine('mysql+pymysql://%s:%s@127.0.0.1/%s'%(mysql_username,mysql_password,mysql_database), pool_recycle=3600)\n",
    "dbConnection = sqlEngine.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# last table\n",
    "try:\n",
    "    frame = df_pub_to_datasource.to_sql(\"pub_datasource\", dbConnection, if_exists='append', index=False, index_label=[\"pub_id\",\"datasource_id\"])\n",
    "except ValueError as vx:\n",
    "    print(vx)\n",
    "except Exception as ex:   \n",
    "    print(ex)\n",
    "else:\n",
    "    print(\"Table created successfully.\");   \n",
    "finally:\n",
    "    dbConnection.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "covid",
   "language": "python",
   "name": "covid"
  },
  "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}