2.process-metrics.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# Process [journalmetrics](https://journalmetrics.scopus.com/) data into tidy TSVs"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "source": [
    "import pathlib\n",
    "\n",
    "import pandas"
   ],
   "outputs": [],
   "metadata": {
    "collapsed": true
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Prepare a dataframe of metrics and values"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "source": [
    "path = pathlib.Path('download/CiteScore 2011-2020 new methodology - May 2021.xlsb')\n",
    "xlsx = pandas.ExcelFile(path)\n",
    "sheets = [sheet for sheet in xlsx.sheet_names if 'CiteScore ' in sheet]\n",
    "sheets"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "['CiteScore 2020',\n",
       " 'CiteScore 2019',\n",
       " 'CiteScore 2018',\n",
       " 'CiteScore 2017',\n",
       " 'CiteScore 2016',\n",
       " 'CiteScore 2015',\n",
       " 'CiteScore 2014',\n",
       " 'CiteScore 2013',\n",
       " 'CiteScore 2012',\n",
       " 'CiteScore 2011']"
      ]
     },
     "metadata": {},
     "execution_count": 2
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "source": [
    "renamer = {\n",
    "    'Scopus Source ID': 'scopus_id',\n",
    "    # 2020 CiteScore includes year in column name unlike other years\n",
    "    \"CiteScore 2020\": \"CiteScore\"\n",
    "}\n",
    "\n",
    "metrics = ['CiteScore', 'SNIP', 'SJR']\n",
    "\n",
    "dfs = list()\n",
    "for sheet in sheets:\n",
    "    print(sheet)\n",
    "    _, year = sheet.split()\n",
    "    year = int(year)\n",
    "\n",
    "    df = (\n",
    "        xlsx.parse(sheet)\n",
    "        .rename(columns=renamer)\n",
    "        .drop_duplicates(['scopus_id'] + metrics)\n",
    "        .assign(year=year)\n",
    "    )\n",
    "\n",
    "    df = pandas.melt(df, id_vars=['scopus_id', 'year'], value_vars=metrics, var_name='metric').dropna()\n",
    "    dfs.append(df)\n",
    "\n",
    "metric_df = pandas.concat(dfs).sort_values(['scopus_id', 'year', 'metric'])"
   ],
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "CiteScore 2020\n",
      "CiteScore 2019\n",
      "CiteScore 2018\n",
      "CiteScore 2017\n",
      "CiteScore 2016\n",
      "CiteScore 2015\n",
      "CiteScore 2014\n",
      "CiteScore 2013\n",
      "CiteScore 2012\n",
      "CiteScore 2011\n"
     ]
    }
   ],
   "metadata": {
    "collapsed": true
   }
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "source": [
    "len(metric_df)"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "678652"
      ]
     },
     "metadata": {},
     "execution_count": 4
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "source": [
    "metric_df.head(2)"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "       scopus_id  year     metric  value\n",
       "11535      12001  2011  CiteScore  1.700\n",
       "51467      12001  2011        SJR  0.565"
      ],
      "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>year</th>\n",
       "      <th>metric</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11535</th>\n",
       "      <td>12001</td>\n",
       "      <td>2011</td>\n",
       "      <td>CiteScore</td>\n",
       "      <td>1.700</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51467</th>\n",
       "      <td>12001</td>\n",
       "      <td>2011</td>\n",
       "      <td>SJR</td>\n",
       "      <td>0.565</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "execution_count": 5
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "source": [
    "pandas.crosstab(metric_df.year, metric_df.metric, margins=True)"
   ],
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "metric  CiteScore     SJR    SNIP     All\n",
       "year                                     \n",
       "2011        19966   19406   18758   58130\n",
       "2012        20946   20500   19855   61301\n",
       "2013        21748   21336   20717   63801\n",
       "2014        22246   21968   21327   65541\n",
       "2015        22762   22387   21579   66728\n",
       "2016        23284   22951   22360   68595\n",
       "2017        24296   23317   23189   70802\n",
       "2018        24754   23504   23653   71911\n",
       "2019        25300   24456   24117   73873\n",
       "2020        25990   25990   25990   77970\n",
       "All        231292  225815  221545  678652"
      ],
      "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>metric</th>\n",
       "      <th>CiteScore</th>\n",
       "      <th>SJR</th>\n",
       "      <th>SNIP</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2011</th>\n",
       "      <td>19966</td>\n",
       "      <td>19406</td>\n",
       "      <td>18758</td>\n",
       "      <td>58130</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <td>20946</td>\n",
       "      <td>20500</td>\n",
       "      <td>19855</td>\n",
       "      <td>61301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>21748</td>\n",
       "      <td>21336</td>\n",
       "      <td>20717</td>\n",
       "      <td>63801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014</th>\n",
       "      <td>22246</td>\n",
       "      <td>21968</td>\n",
       "      <td>21327</td>\n",
       "      <td>65541</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>22762</td>\n",
       "      <td>22387</td>\n",
       "      <td>21579</td>\n",
       "      <td>66728</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <td>23284</td>\n",
       "      <td>22951</td>\n",
       "      <td>22360</td>\n",
       "      <td>68595</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>24296</td>\n",
       "      <td>23317</td>\n",
       "      <td>23189</td>\n",
       "      <td>70802</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018</th>\n",
       "      <td>24754</td>\n",
       "      <td>23504</td>\n",
       "      <td>23653</td>\n",
       "      <td>71911</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019</th>\n",
       "      <td>25300</td>\n",
       "      <td>24456</td>\n",
       "      <td>24117</td>\n",
       "      <td>73873</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2020</th>\n",
       "      <td>25990</td>\n",
       "      <td>25990</td>\n",
       "      <td>25990</td>\n",
       "      <td>77970</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <td>231292</td>\n",
       "      <td>225815</td>\n",
       "      <td>221545</td>\n",
       "      <td>678652</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ]
     },
     "metadata": {},
     "execution_count": 6
    }
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "source": [
    "path = pathlib.Path('data/metrics.tsv.gz')\n",
    "metric_df.to_csv(path, sep='\\t', index=False, float_format='%.3g', compression={\"method\": 'gzip', \"mtime\": 0})"
   ],
   "outputs": [],
   "metadata": {
    "scrolled": true
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:scopus]",
   "language": "python",
   "name": "conda-env-scopus-py"
  },
  "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.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}