1.process-titles.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Process Scopus title (e.g. journal) metadata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pathlib\n",
    "import re\n",
    "\n",
    "import pandas\n",
    "import slugify\n",
    "\n",
    "from utilities import format_issn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load dataset\n",
    "path = pathlib.Path('download/extlistJuly2021.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>Sourcerecord ID</th>\n",
       "      <th>Source Title (Medline-sourced journals are indicated in Green)</th>\n",
       "      <th>Print-ISSN</th>\n",
       "      <th>E-ISSN</th>\n",
       "      <th>Active or Inactive</th>\n",
       "      <th>Coverage</th>\n",
       "      <th>Titles discontinued by Scopus due to quality issues</th>\n",
       "      <th>Article language in source (three-letter ISO language codes)</th>\n",
       "      <th>2019\\nCiteScore</th>\n",
       "      <th>2020\\nCiteScore</th>\n",
       "      <th>...</th>\n",
       "      <th>2700\\nMedicine</th>\n",
       "      <th>2800\\nNeuroscience</th>\n",
       "      <th>2900\\nNursing</th>\n",
       "      <th>3000\\nPharmacology, Toxicology and Pharmaceutics</th>\n",
       "      <th>3100\\nPhysics and Astronomy</th>\n",
       "      <th>3200\\nPsychology</th>\n",
       "      <th>3300\\nSocial Sciences</th>\n",
       "      <th>3400\\nVeterinary</th>\n",
       "      <th>3500\\nDentistry</th>\n",
       "      <th>3600\\nHealth Professions</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>21101021140</td>\n",
       "      <td>1700-tal: Nordic Journal for Eighteenth-Centur...</td>\n",
       "      <td>16524772</td>\n",
       "      <td>20019866</td>\n",
       "      <td>Active</td>\n",
       "      <td>2019-ongoing</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DAN;ENG;GER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.0</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>Social Sciences</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>18500162600</td>\n",
       "      <td>21st Century Music</td>\n",
       "      <td>15343219</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Inactive</td>\n",
       "      <td>2002-2011</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ENG</td>\n",
       "      <td>NaN</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 54 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Sourcerecord ID  \\\n",
       "0      21101021140   \n",
       "1      18500162600   \n",
       "\n",
       "  Source Title (Medline-sourced journals are indicated in Green) Print-ISSN  \\\n",
       "0  1700-tal: Nordic Journal for Eighteenth-Centur...               16524772   \n",
       "1                                 21st Century Music               15343219   \n",
       "\n",
       "     E-ISSN Active or Inactive      Coverage  \\\n",
       "0  20019866             Active  2019-ongoing   \n",
       "1       NaN           Inactive     2002-2011   \n",
       "\n",
       "  Titles discontinued by Scopus due to quality issues  \\\n",
       "0                                                NaN    \n",
       "1                                                NaN    \n",
       "\n",
       "  Article language in source (three-letter ISO language codes)  \\\n",
       "0                                        DAN;ENG;GER             \n",
       "1                                                ENG             \n",
       "\n",
       "   2019\\nCiteScore  2020\\nCiteScore  ... 2700\\nMedicine 2800\\nNeuroscience  \\\n",
       "0              NaN              0.0  ...            NaN                NaN   \n",
       "1              NaN              NaN  ...            NaN                NaN   \n",
       "\n",
       "  2900\\nNursing 3000\\nPharmacology, Toxicology and Pharmaceutics  \\\n",
       "0           NaN                                              NaN   \n",
       "1           NaN                                              NaN   \n",
       "\n",
       "  3100\\nPhysics and Astronomy 3200\\nPsychology 3300\\nSocial Sciences  \\\n",
       "0                         NaN              NaN       Social Sciences   \n",
       "1                         NaN              NaN                   NaN   \n",
       "\n",
       "  3400\\nVeterinary 3500\\nDentistry 3600\\nHealth Professions  \n",
       "0              NaN             NaN                      NaN  \n",
       "1              NaN             NaN                      NaN  \n",
       "\n",
       "[2 rows x 54 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "source_df = pandas.read_excel(path, sheet_name='Scopus Sources May 2021')\n",
    "source_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Sourcerecord ID',\n",
       "       'Source Title (Medline-sourced journals are indicated in Green)',\n",
       "       'Print-ISSN', 'E-ISSN', 'Active or Inactive', 'Coverage',\n",
       "       'Titles discontinued by Scopus due to quality issues',\n",
       "       'Article language in source (three-letter ISO language codes)',\n",
       "       '2019\\nCiteScore', '2020\\nCiteScore',\n",
       "       'Medline-sourced Title? (see more info under separate tab)',\n",
       "       'Open Access status, i.e., registered in DOAJ. Status May 2020\\n',\n",
       "       'Articles in Press included?', 'Added to list May 2021', 'Source Type',\n",
       "       'Title history indication', 'Related title to title history indication',\n",
       "       'Other related title 1', 'Other related title 2',\n",
       "       'Other related title 3', 'Publisher's Name',\n",
       "       'Publisher imprints grouped to main Publisher',\n",
       "       'All Science Journal Classification Codes (ASJC)',\n",
       "       'Top level:\\n\\nLife Sciences', 'Top level:\\n\\nSocial Sciences',\n",
       "       'Top level:\\n\\nPhysical Sciences', 'Top level:\\n\\nHealth Sciences',\n",
       "       '1000 \\nGeneral', '1100\\nAgricultural and Biological Sciences',\n",
       "       '1200\\nArts and Humanities',\n",
       "       '1300\\nBiochemistry, Genetics and Molecular Biology',\n",
       "       '1400\\nBusiness, Management and Accounting',\n",
       "       '1500\\nChemical Engineering', '1600\\nChemistry',\n",
       "       '1700\\nComputer Science', '1800\\nDecision Sciences',\n",
       "       '1900\\nEarth and Planetary Sciences',\n",
       "       '2000\\nEconomics, Econometrics and Finance', '2100\\nEnergy',\n",
       "       '2200\\nEngineering', '2300\\nEnvironmental Science',\n",
       "       '2400\\nImmunology and Microbiology', '2500\\nMaterials Science',\n",
       "       '2600\\nMathematics', '2700\\nMedicine', '2800\\nNeuroscience',\n",
       "       '2900\\nNursing', '3000\\nPharmacology, Toxicology and Pharmaceutics',\n",
       "       '3100\\nPhysics and Astronomy', '3200\\nPsychology',\n",
       "       '3300\\nSocial Sciences', '3400\\nVeterinary', '3500\\nDentistry',\n",
       "       '3600\\nHealth Professions'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "source_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Sourcerecord ID</th>\n",
       "      <th>Print-ISSN</th>\n",
       "      <th>E-ISSN</th>\n",
       "      <th>Conference Name</th>\n",
       "      <th>Conference Title</th>\n",
       "      <th>Publication Year</th>\n",
       "      <th>2016\\nCiteScore</th>\n",
       "      <th>2016\\nSJR</th>\n",
       "      <th>2016\\nSNIP</th>\n",
       "      <th>2017\\nCiteScore</th>\n",
       "      <th>2017\\nSJR</th>\n",
       "      <th>2017\\nSNIP</th>\n",
       "      <th>2018\\nCiteScore</th>\n",
       "      <th>2018\\nSJR</th>\n",
       "      <th>2018\\nSNIP</th>\n",
       "      <th>ASJC Code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>25674</td>\n",
       "      <td>03029743</td>\n",
       "      <td>16113349</td>\n",
       "      <td>\"11th International Conferenceon Fundamental A...</td>\n",
       "      <td>Fundamental Approaches to Software Engineering...</td>\n",
       "      <td>2008</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1700; 2614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>25674</td>\n",
       "      <td>03029743</td>\n",
       "      <td>16113349</td>\n",
       "      <td>\"11th International Conferenceon the Foundatio...</td>\n",
       "      <td>Foundations of Software Science and Computatio...</td>\n",
       "      <td>2008</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1700; 2614</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Sourcerecord ID Print-ISSN    E-ISSN  \\\n",
       "0            25674   03029743  16113349   \n",
       "1            25674   03029743  16113349   \n",
       "\n",
       "                                     Conference Name  \\\n",
       "0  \"11th International Conferenceon Fundamental A...   \n",
       "1  \"11th International Conferenceon the Foundatio...   \n",
       "\n",
       "                                    Conference Title  Publication Year  \\\n",
       "0  Fundamental Approaches to Software Engineering...              2008   \n",
       "1  Foundations of Software Science and Computatio...              2008   \n",
       "\n",
       "   2016\\nCiteScore  2016\\nSJR  2016\\nSNIP  2017\\nCiteScore  2017\\nSJR  \\\n",
       "0              NaN        NaN         NaN              NaN        NaN   \n",
       "1              NaN        NaN         NaN              NaN        NaN   \n",
       "\n",
       "   2017\\nSNIP  2018\\nCiteScore  2018\\nSJR  2018\\nSNIP   ASJC Code  \n",
       "0         NaN              NaN        NaN         NaN  1700; 2614  \n",
       "1         NaN              NaN        NaN         NaN  1700; 2614  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conf_df = pandas.read_excel(path, sheet_name='All Conf. Proceedings')\n",
    "conf_df.head(2)"
   ]
  },
  {
   "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>Code</th>\n",
       "      <th>Description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000</td>\n",
       "      <td>Multidisciplinary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1100</td>\n",
       "      <td>General Agricultural and Biological Sciences</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Code                                   Description\n",
       "1  1000                             Multidisciplinary\n",
       "3  1100  General Agricultural and Biological Sciences"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "code_df = pandas.read_excel(path, sheet_name='ASJC classification codes', usecols=[\"Code\", \"Description\"], skiprows=8).dropna()\n",
    "code_df.Code = code_df.Code.astype(int)\n",
    "code_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rename columns on dataframes\n",
    "renamer = {\n",
    "    'Sourcerecord ID': 'scopus_id',\n",
    "    \"Source Title (Medline-sourced journals are indicated in Green)\": \"title_name\",\n",
    "    \"Conference Name\": \"title_name\",\n",
    "    'All Science Classification Codes (ASJC)': 'asjc_codes',\n",
    "    'ASJC Code': 'asjc_codes',\n",
    "    \"All Science Journal Classification Codes (ASJC)\": \"asjc_codes\",\n",
    "    'Print-ISSN': 'issn_print',\n",
    "    'E-ISSN': 'issn_electronic',\n",
    "    'ISSN': 'issn_unspecified',\n",
    "}\n",
    "\n",
    "for df in source_df, conf_df:\n",
    "    df.rename(columns=renamer, inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Titles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Extract title names\n",
    "rows = list()\n",
    "for df in source_df, conf_df:\n",
    "    rows.extend(zip(df.scopus_id, df.title_name))\n",
    "\n",
    "rows.sort()\n",
    "title_df = pandas.DataFrame(rows, columns=['scopus_id', 'title_name'])\n",
    "title_df = title_df.drop_duplicates('scopus_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>scopus_id</th>\n",
       "      <th>title_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12000</td>\n",
       "      <td>Journal of Technology in Counseling</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12001</td>\n",
       "      <td>Journal of the Experimental Analysis of Behavior</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   scopus_id                                        title_name\n",
       "0      12000               Journal of Technology in Counseling\n",
       "1      12001  Journal of the Experimental Analysis of Behavior"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "title_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "82234"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(title_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = pathlib.Path('data/titles.tsv')\n",
    "title_df.to_csv(path, index=False, sep='\\t')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ISSN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>issn</th>\n",
       "      <th>linking_issn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0000-0019</td>\n",
       "      <td>0000-0019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0000-0027</td>\n",
       "      <td>0000-0027</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        issn linking_issn\n",
       "0  0000-0019    0000-0019\n",
       "1  0000-0027    0000-0027"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load linking ISSN map as per https://github.com/dhimmel/scopus/issues/7\n",
    "path = pathlib.Path('download/20210912.ISSN-to-ISSN-L.txt.gz')\n",
    "issnl_df = (\n",
    "    pandas.read_table(path)\n",
    "    .rename(columns={\"ISSN\": \"issn\", \"ISSN-L\": \"linking_issn\"})\n",
    ")\n",
    "issnl_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>scopus_id</th>\n",
       "      <th>issn_type</th>\n",
       "      <th>issn</th>\n",
       "      <th>linking_issn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>38391</th>\n",
       "      <td>12000</td>\n",
       "      <td>print</td>\n",
       "      <td>1527-6228</td>\n",
       "      <td>1527-6228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38620</th>\n",
       "      <td>12001</td>\n",
       "      <td>print</td>\n",
       "      <td>0022-5002</td>\n",
       "      <td>0022-5002</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       scopus_id issn_type       issn linking_issn\n",
       "38391      12000     print  1527-6228    1527-6228\n",
       "38620      12001     print  0022-5002    0022-5002"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "value_vars = {'issn_print', 'issn_electronic', 'issn_unspecified'}\n",
    "issn_dfs = list()\n",
    "for df in source_df, conf_df:\n",
    "    df_value_vars = value_vars.intersection(df.columns)\n",
    "    df = pandas.melt(df, id_vars='scopus_id', value_vars=df_value_vars, var_name='issn_type', value_name='issn')\n",
    "    df.dropna(inplace=True)\n",
    "    df.issn = df.issn.map(format_issn)\n",
    "    df.issn_type = df.issn_type.map(lambda x: x.split('_')[1])\n",
    "    issn_dfs.append(df.dropna())\n",
    "issn_df = (\n",
    "    pandas.concat(issn_dfs)\n",
    "    .dropna()\n",
    "    .drop_duplicates(['scopus_id', 'issn'])\n",
    "    .merge(issnl_df, how=\"left\")\n",
    "    .sort_values(['scopus_id', 'issn_type', 'issn', 'linking_issn'])\n",
    ")\n",
    "issn_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(55830, 2304414)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(issn_df), len(issnl_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>scopus_id</th>\n",
       "      <th>issn_type</th>\n",
       "      <th>issn</th>\n",
       "      <th>linking_issn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>38391</th>\n",
       "      <td>12000</td>\n",
       "      <td>print</td>\n",
       "      <td>1527-6228</td>\n",
       "      <td>1527-6228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38620</th>\n",
       "      <td>12001</td>\n",
       "      <td>print</td>\n",
       "      <td>0022-5002</td>\n",
       "      <td>0022-5002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9825</th>\n",
       "      <td>12002</td>\n",
       "      <td>electronic</td>\n",
       "      <td>1520-6696</td>\n",
       "      <td>0022-5061</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       scopus_id   issn_type       issn linking_issn\n",
       "38391      12000       print  1527-6228    1527-6228\n",
       "38620      12001       print  0022-5002    0022-5002\n",
       "9825       12002  electronic  1520-6696    0022-5061"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "issn_df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "316\n"
     ]
    },
    {
     "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>scopus_id</th>\n",
       "      <th>issn_type</th>\n",
       "      <th>issn</th>\n",
       "      <th>linking_issn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17235</th>\n",
       "      <td>66910</td>\n",
       "      <td>print</td>\n",
       "      <td>1356-918X</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23197</th>\n",
       "      <td>21100435894</td>\n",
       "      <td>print</td>\n",
       "      <td>1574-8626</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16887</th>\n",
       "      <td>24698</td>\n",
       "      <td>print</td>\n",
       "      <td>1093-2658</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6667</th>\n",
       "      <td>19700174926</td>\n",
       "      <td>electronic</td>\n",
       "      <td>1596-9819</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1441</th>\n",
       "      <td>21100199113</td>\n",
       "      <td>electronic</td>\n",
       "      <td>2180-3137</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         scopus_id   issn_type       issn linking_issn\n",
       "17235        66910       print  1356-918X          NaN\n",
       "23197  21100435894       print  1574-8626          NaN\n",
       "16887        24698       print  1093-2658          NaN\n",
       "6667   19700174926  electronic  1596-9819          NaN\n",
       "1441   21100199113  electronic  2180-3137          NaN"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Scopus ISSNs that don't have an ISSN-L\n",
    "bad_issn_df = issn_df[issn_df.linking_issn.isna()]\n",
    "print(len(bad_issn_df))\n",
    "bad_issn_df.sample(5, random_state=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = pathlib.Path('data/issn.tsv')\n",
    "issn_df.to_csv(path, sep='\\t', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ASJC Codes\n",
    "\n",
    "All Science Journal Classification (ASJC) Codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>asjc_code</th>\n",
       "      <th>asjc_description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000</td>\n",
       "      <td>Multidisciplinary</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1100</td>\n",
       "      <td>General Agricultural and Biological Sciences</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   asjc_code                              asjc_description\n",
       "1       1000                             Multidisciplinary\n",
       "3       1100  General Agricultural and Biological Sciences"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Extract ASJC code descriptions\n",
    "code_df = code_df.rename(columns={'Code': 'asjc_code', 'Description': 'asjc_description'})\n",
    "code_df = code_df[['asjc_code', 'asjc_description']]\n",
    "code_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = pathlib.Path('data/asjc-codes.tsv')\n",
    "code_df.to_csv(path, index=False, sep='\\t')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>scopus_id</th>\n",
       "      <th>asjc_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12000</td>\n",
       "      <td>1705</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12000</td>\n",
       "      <td>3304</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   scopus_id  asjc_code\n",
       "0      12000       1705\n",
       "1      12000       3304"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Extract title to code mapping\n",
    "rows = list()\n",
    "for df in source_df, conf_df:\n",
    "    for scopus_id, codes in zip(df.scopus_id, df.asjc_codes):\n",
    "        for code in re.split(r'[,;] *', str(codes)):\n",
    "            code = code.strip()\n",
    "            try:\n",
    "                code = int(code)\n",
    "            except ValueError:\n",
    "                continue\n",
    "            rows.append((scopus_id, code))\n",
    "\n",
    "rows.sort()\n",
    "title_codes_df = pandas.DataFrame(rows, columns=['scopus_id', 'asjc_code'])\n",
    "title_codes_df = title_codes_df.drop_duplicates()\n",
    "title_codes_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = pathlib.Path('data/titles-asjc-codes.tsv')\n",
    "title_codes_df.to_csv(path, index=False, sep='\\t')"
   ]
  },
  {
   "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>scopus_id</th>\n",
       "      <th>asjc_code</th>\n",
       "      <th>asjc_description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12000</td>\n",
       "      <td>1700</td>\n",
       "      <td>Computer Science</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23442</th>\n",
       "      <td>12000</td>\n",
       "      <td>3300</td>\n",
       "      <td>Social Sciences</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       scopus_id  asjc_code  asjc_description\n",
       "0          12000       1700  Computer Science\n",
       "23442      12000       3300   Social Sciences"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Extract title to subject area mapping\n",
    "subject_df = title_codes_df.copy()\n",
    "subject_df.asjc_code = 100 * (subject_df.asjc_code // 100)\n",
    "subject_df = subject_df.drop_duplicates()\n",
    "subject_df = subject_df.merge(code_df)\n",
    "subject_df.asjc_description = subject_df.asjc_description.map(lambda x: re.sub(r'^General ', '', x))\n",
    "subject_df = subject_df.sort_values(['scopus_id', 'asjc_code'])\n",
    "subject_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = pathlib.Path('data/subject-areas.tsv')\n",
    "subject_df.to_csv(path, index=False, sep='\\t')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Title attributes"
   ]
  },
  {
   "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>scopus_id</th>\n",
       "      <th>active</th>\n",
       "      <th>open_access</th>\n",
       "      <th>main_publisher</th>\n",
       "      <th>source_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24571</th>\n",
       "      <td>12000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Columbus State University</td>\n",
       "      <td>Journal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24809</th>\n",
       "      <td>12001</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>Wiley-Blackwell</td>\n",
       "      <td>Journal</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       scopus_id  active  open_access             main_publisher source_type\n",
       "24571      12000       0            0  Columbus State University     Journal\n",
       "24809      12001       1            0            Wiley-Blackwell     Journal"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Extract title attributes for non-conference-proceedings\n",
    "renamer = {\n",
    "    'Open Access status, i.e., registered in DOAJ. Status May 2020\\n': 'open_access',\n",
    "    'Active or Inactive': 'active',\n",
    "    'Source Type': 'source_type',\n",
    "    'Publisher imprints grouped to main Publisher': 'main_publisher',\n",
    "}\n",
    "attribute_df = source_df.copy()\n",
    "attribute_df = attribute_df.rename(columns=renamer)\n",
    "attribute_df = attribute_df[['scopus_id'] + list(renamer.values())]\n",
    "attribute_df.active = (attribute_df.active == 'Active').astype(int)\n",
    "attribute_df.open_access = attribute_df.open_access.notnull().astype(int)\n",
    "attribute_df = attribute_df.sort_values('scopus_id')\n",
    "# Enforce column order for diff viewing\n",
    "columns = ['scopus_id', 'active', 'open_access', 'main_publisher', 'source_type']\n",
    "attribute_df = attribute_df[columns]\n",
    "attribute_df = attribute_df.drop_duplicates('scopus_id')\n",
    "attribute_df.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fix publishers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Publishers before filtering: 12,155\n",
      "Publishers after whitespace stripping: 12,154\n",
      "Publishers after renaming: 12,052\n"
     ]
    }
   ],
   "source": [
    "print(f'Publishers before filtering: {attribute_df.main_publisher.nunique():,}')\n",
    "\n",
    "# Strip whitespace sourrounding publisher names\n",
    "attribute_df.main_publisher = attribute_df.main_publisher.str.strip()\n",
    "print(f'Publishers after whitespace stripping: {attribute_df.main_publisher.nunique():,}')\n",
    "\n",
    "# Apply manual publisher name patches\n",
    "path = pathlib.Path('data/publisher-name-patches.tsv')\n",
    "patch_df = pandas.read_table(path)\n",
    "publisher_renamer = dict(zip(patch_df.scopus_main_publisher, patch_df.publisher_name))\n",
    "attribute_df.main_publisher.replace(publisher_renamer, inplace=True)\n",
    "print(f'Publishers after renaming: {attribute_df.main_publisher.nunique():,}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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>main_publisher_slug</th>\n",
       "      <th>n_journals</th>\n",
       "      <th>main_publisher</th>\n",
       "      <th>name_variants</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1105-media-inc</td>\n",
       "      <td>1</td>\n",
       "      <td>1105 Media Inc.</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3g-publishing</td>\n",
       "      <td>1</td>\n",
       "      <td>3G Publishing</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  main_publisher_slug  n_journals   main_publisher  name_variants\n",
       "0      1105-media-inc           1  1105 Media Inc.              1\n",
       "1       3g-publishing           1    3G Publishing              1"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create a publsher dataframe\n",
    "publisher_df = (\n",
    "    attribute_df\n",
    "    .groupby('main_publisher')\n",
    "    .apply(len)\n",
    "    .rename('n_journals')\n",
    "    .reset_index()\n",
    ")\n",
    "slugger = slugify.Slugify(to_lower=True)\n",
    "publisher_df['main_publisher_slug'] = publisher_df.main_publisher.map(slugger)\n",
    "\n",
    "def summarize(df):\n",
    "    \"\"\"\n",
    "    Condense multiple publishers with the same slug\n",
    "    \"\"\"\n",
    "    # Select name variant used for the most number of titles\n",
    "    df = df.sort_values(['n_journals', 'main_publisher'])\n",
    "    return {\n",
    "        'n_journals': sum(df.n_journals),\n",
    "        'main_publisher': df.main_publisher.iloc[0],\n",
    "        'name_variants': len(df),\n",
    "    }\n",
    "\n",
    "results = (\n",
    "    publisher_df\n",
    "    .groupby('main_publisher_slug')\n",
    "    .apply(summarize)\n",
    ")\n",
    "publisher_df = pandas.DataFrame.from_records(results, index=results.index).reset_index()\n",
    "publisher_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1    11983\n",
       "2       33\n",
       "3        1\n",
       "Name: name_variants, dtype: int64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# How many publishers had name variants (identified using slug reduction)\n",
    "publisher_df.name_variants.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Publishers after slug reductions: 12,017\n"
     ]
    }
   ],
   "source": [
    "# Standardize name variants using slug reductionism\n",
    "slug_to_publisher = dict(zip(publisher_df.main_publisher_slug, publisher_df.main_publisher))\n",
    "attribute_df.main_publisher = attribute_df.main_publisher.map(slugger, na_action='ignore').map(slug_to_publisher)\n",
    "print(f'Publishers after slug reductions: {attribute_df.main_publisher.nunique():,}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = pathlib.Path('data/title-attributes.tsv')\n",
    "attribute_df.to_csv(path, index=False, sep='\\t')\n",
    "\n",
    "path = pathlib.Path('data/publishers.tsv')\n",
    "publisher_df.to_csv(path, index=False, sep='\\t')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>scopus_id</th>\n",
       "      <th>top_level_subject</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12000</td>\n",
       "      <td>Physical Sciences</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12000</td>\n",
       "      <td>Social Sciences</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   scopus_id  top_level_subject\n",
       "0      12000  Physical Sciences\n",
       "1      12000    Social Sciences"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Top level subject areas\n",
    "rows = []\n",
    "tl_cols = list(source_df.columns[source_df.columns.str.startswith('Top level:')])\n",
    "for i, series in source_df[['scopus_id'] + tl_cols].iterrows():\n",
    "    scopus_id = series.scopus_id\n",
    "    for value in series.iloc[1:].dropna():\n",
    "        rows.append((scopus_id, value))\n",
    "rows.sort()\n",
    "top_df = pandas.DataFrame(rows, columns=['scopus_id', 'top_level_subject'])\n",
    "top_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "path = pathlib.Path('data/title-top-levels.tsv')\n",
    "top_df.to_csv(path, index=False, sep='\\t')"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "interpreter": {
   "hash": "0e356716c2af146b4f1229c16fd80fd2e17e74cbf6057515b63f09e4d327206c"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}