cookbooks/1-getting-started/3-Working-with-dataframes.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false",
    "id": "8XNB2EeXR4DA"
   },
   "source": [
    "# The Dimcli Python library: Working with Pandas Dataframes\n",
    "\n",
    "[Dimcli](https://github.com/lambdamusic/dimcli) includes a few utilities that make it easier to transform Dimensions JSON data into Pandas [dataframe objects](https://pandas.pydata.org/pandas-docs/stable/getting_started/dsintro.html#dataframe).\n",
    "\n",
    "Dataframes are then easy to sort, analyse, export as CSV and use within visualisation softwares.\n",
    "\n",
    ">  [pandas](https://pandas.pydata.org/pandas-docs/stable/) is a popular software library written for the Python programming language for data manipulation and analysis.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "AX6GU4bvBCIu",
    "outputId": "5f4a1ffd-cf3d-48bc-89f5-3d029ed681f9"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==\n",
      "CHANGELOG\n",
      "This notebook was last run on Aug 10, 2023\n",
      "==\n"
     ]
    }
   ],
   "source": [
    "import datetime\n",
    "print(\"==\\nCHANGELOG\\nThis notebook was last run on %s\\n==\" % datetime.date.today().strftime('%b %d, %Y'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false",
    "id": "hMaQlB7DG8Vw"
   },
   "source": [
    "## Prerequisites\n",
    "\n",
    "This notebook assumes you have installed the [Dimcli](https://pypi.org/project/dimcli/) library and are familiar with the ['Getting Started' tutorial](https://api-lab.dimensions.ai/cookbooks/1-getting-started/1-Using-the-Dimcli-library-to-query-the-API.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "Collapsed": "false",
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "J2XpKv-sBCIw",
    "outputId": "3f6a1d70-921b-49bd-b45e-9fe37649d678"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[2mSearching config file credentials for 'https://app.dimensions.ai' endpoint..\u001b[0m\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==\n",
      "Logging in..\n",
      "\u001b[2mDimcli - Dimensions API Client (v1.1)\u001b[0m\n",
      "\u001b[2mConnected to: <https://app.dimensions.ai/api/dsl> - DSL v2.7\u001b[0m\n",
      "\u001b[2mMethod: dsl.ini file\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "!pip install dimcli --quiet\n",
    "\n",
    "import dimcli\n",
    "from dimcli.utils import *\n",
    "import json\n",
    "import sys\n",
    "import pandas as pd\n",
    "#\n",
    "\n",
    "print(\"==\\nLogging in..\")\n",
    "# https://digital-science.github.io/dimcli/getting-started.html#authentication\n",
    "ENDPOINT = \"https://app.dimensions.ai\"\n",
    "if 'google.colab' in sys.modules:\n",
    "  import getpass\n",
    "  KEY = getpass.getpass(prompt='API Key: ')\n",
    "  dimcli.login(key=KEY, endpoint=ENDPOINT)\n",
    "else:\n",
    "  KEY = \"\"\n",
    "  dimcli.login(key=KEY, endpoint=ENDPOINT)\n",
    "dsl = dimcli.Dsl()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false",
    "id": "oMLG9WL0R4DE"
   },
   "source": [
    "## 1. General method to transform JSON query results into a dataframe\n",
    "\n",
    "The `DslDataset.as_dataframe` method allows to quickly turn any query results into a dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "Collapsed": "false",
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "19qgos2dR4DF",
    "outputId": "2a38637a-0b9d-42c9-fb95-9ad45af2c923"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Returned Publications: 1000 (total = 504501)\n",
      "\u001b[2mTime: 2.52s\u001b[0m\n",
      "WARNINGS [1]\n",
      "Field current_organization_id of the authors field is deprecated and will be removed in the next major release.\n"
     ]
    }
   ],
   "source": [
    "# we'll reuse this query later on\n",
    "query = \"\"\"search publications for \"graphene\"\n",
    "            where year in [2013:2019]\n",
    "            return publications[id+type+volume+year+issue+title+journal+authors] sort by times_cited limit 1000\"\"\"\n",
    "res = dsl.query(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "Collapsed": "false",
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 5672
    },
    "id": "6LCGKJPwR4DG",
    "outputId": "9a192fbd-8efa-42ea-d0ba-8a2cceef3ddb"
   },
   "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>id</th>\n",
       "      <th>title</th>\n",
       "      <th>authors</th>\n",
       "      <th>issue</th>\n",
       "      <th>type</th>\n",
       "      <th>volume</th>\n",
       "      <th>year</th>\n",
       "      <th>journal.id</th>\n",
       "      <th>journal.title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>pub.1024857999</td>\n",
       "      <td>Van der Waals heterostructures</td>\n",
       "      <td>[{'affiliations': [{'city': 'Manchester', 'cit...</td>\n",
       "      <td>7459</td>\n",
       "      <td>article</td>\n",
       "      <td>499</td>\n",
       "      <td>2013</td>\n",
       "      <td>jour.1018957</td>\n",
       "      <td>Nature</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>pub.1050119463</td>\n",
       "      <td>The chemistry of two-dimensional layered trans...</td>\n",
       "      <td>[{'affiliations': [{'city': 'New Brunswick', '...</td>\n",
       "      <td>4</td>\n",
       "      <td>article</td>\n",
       "      <td>5</td>\n",
       "      <td>2013</td>\n",
       "      <td>jour.1041224</td>\n",
       "      <td>Nature Chemistry</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>pub.1007330696</td>\n",
       "      <td>Combining theory and experiment in electrocata...</td>\n",
       "      <td>[{'affiliations': [{'city': 'Stanford', 'city_...</td>\n",
       "      <td>6321</td>\n",
       "      <td>article</td>\n",
       "      <td>355</td>\n",
       "      <td>2017</td>\n",
       "      <td>jour.1346339</td>\n",
       "      <td>Science</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>pub.1019126274</td>\n",
       "      <td>The Li-Ion Rechargeable Battery: A Perspective</td>\n",
       "      <td>[{'affiliations': [{'city': 'Austin', 'city_id...</td>\n",
       "      <td>4</td>\n",
       "      <td>article</td>\n",
       "      <td>135</td>\n",
       "      <td>2013</td>\n",
       "      <td>jour.1081898</td>\n",
       "      <td>Journal of the American Chemical Society</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>pub.1032956475</td>\n",
       "      <td>Black phosphorus field-effect transistors</td>\n",
       "      <td>[{'affiliations': [{'city': 'Shanghai', 'city_...</td>\n",
       "      <td>5</td>\n",
       "      <td>article</td>\n",
       "      <td>9</td>\n",
       "      <td>2014</td>\n",
       "      <td>jour.1037429</td>\n",
       "      <td>Nature Nanotechnology</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>pub.1036410643</td>\n",
       "      <td>Review of Particle Physics</td>\n",
       "      <td>[{'affiliations': [], 'corresponding': '', 'cu...</td>\n",
       "      <td>9</td>\n",
       "      <td>article</td>\n",
       "      <td>38</td>\n",
       "      <td>2014</td>\n",
       "      <td>jour.1327822</td>\n",
       "      <td>Chinese Physics C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>pub.1055085164</td>\n",
       "      <td>Aggregation-Induced Emission: Together We Shin...</td>\n",
       "      <td>[{'affiliations': [{'city': 'Shenzhen', 'city_...</td>\n",
       "      <td>21</td>\n",
       "      <td>article</td>\n",
       "      <td>115</td>\n",
       "      <td>2015</td>\n",
       "      <td>jour.1077147</td>\n",
       "      <td>Chemical Reviews</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>pub.1004394295</td>\n",
       "      <td>Interface engineering of highly efficient pero...</td>\n",
       "      <td>[{'affiliations': [{'city': 'Los Angeles', 'ci...</td>\n",
       "      <td>6196</td>\n",
       "      <td>article</td>\n",
       "      <td>345</td>\n",
       "      <td>2014</td>\n",
       "      <td>jour.1346339</td>\n",
       "      <td>Science</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>pub.1045181228</td>\n",
       "      <td>The emergence of perovskite solar cells</td>\n",
       "      <td>[{'affiliations': [{'city': 'Sydney', 'city_id...</td>\n",
       "      <td>7</td>\n",
       "      <td>article</td>\n",
       "      <td>8</td>\n",
       "      <td>2014</td>\n",
       "      <td>jour.1037430</td>\n",
       "      <td>Nature Photonics</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>pub.1015305822</td>\n",
       "      <td>Raman spectroscopy as a versatile tool for stu...</td>\n",
       "      <td>[{'affiliations': [{'city': 'Cambridge', 'city...</td>\n",
       "      <td>4</td>\n",
       "      <td>article</td>\n",
       "      <td>8</td>\n",
       "      <td>2013</td>\n",
       "      <td>jour.1037429</td>\n",
       "      <td>Nature Nanotechnology</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               id                                              title  \\\n",
       "0  pub.1024857999                     Van der Waals heterostructures   \n",
       "1  pub.1050119463  The chemistry of two-dimensional layered trans...   \n",
       "2  pub.1007330696  Combining theory and experiment in electrocata...   \n",
       "3  pub.1019126274     The Li-Ion Rechargeable Battery: A Perspective   \n",
       "4  pub.1032956475          Black phosphorus field-effect transistors   \n",
       "5  pub.1036410643                         Review of Particle Physics   \n",
       "6  pub.1055085164  Aggregation-Induced Emission: Together We Shin...   \n",
       "7  pub.1004394295  Interface engineering of highly efficient pero...   \n",
       "8  pub.1045181228            The emergence of perovskite solar cells   \n",
       "9  pub.1015305822  Raman spectroscopy as a versatile tool for stu...   \n",
       "\n",
       "                                             authors issue     type volume  \\\n",
       "0  [{'affiliations': [{'city': 'Manchester', 'cit...  7459  article    499   \n",
       "1  [{'affiliations': [{'city': 'New Brunswick', '...     4  article      5   \n",
       "2  [{'affiliations': [{'city': 'Stanford', 'city_...  6321  article    355   \n",
       "3  [{'affiliations': [{'city': 'Austin', 'city_id...     4  article    135   \n",
       "4  [{'affiliations': [{'city': 'Shanghai', 'city_...     5  article      9   \n",
       "5  [{'affiliations': [], 'corresponding': '', 'cu...     9  article     38   \n",
       "6  [{'affiliations': [{'city': 'Shenzhen', 'city_...    21  article    115   \n",
       "7  [{'affiliations': [{'city': 'Los Angeles', 'ci...  6196  article    345   \n",
       "8  [{'affiliations': [{'city': 'Sydney', 'city_id...     7  article      8   \n",
       "9  [{'affiliations': [{'city': 'Cambridge', 'city...     4  article      8   \n",
       "\n",
       "   year    journal.id                             journal.title  \n",
       "0  2013  jour.1018957                                    Nature  \n",
       "1  2013  jour.1041224                          Nature Chemistry  \n",
       "2  2017  jour.1346339                                   Science  \n",
       "3  2013  jour.1081898  Journal of the American Chemical Society  \n",
       "4  2014  jour.1037429                     Nature Nanotechnology  \n",
       "5  2014  jour.1327822                         Chinese Physics C  \n",
       "6  2015  jour.1077147                          Chemical Reviews  \n",
       "7  2014  jour.1346339                                   Science  \n",
       "8  2014  jour.1037430                          Nature Photonics  \n",
       "9  2013  jour.1037429                     Nature Nanotechnology  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = res.as_dataframe()\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false",
    "id": "tCq1lF6HR4DI"
   },
   "source": [
    "Pandas dataframes offer a myriad of utilities for inspecting data. Check out the [official docs](https://pandas.pydata.org/pandas-docs/stable/getting_started/10min.html) or google a [pandas tutorial](https://www.google.com/search?q=pandas+tutorial) to lean more about it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "Collapsed": "false",
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "k_3GoeDkR4DJ",
    "outputId": "db674432-fb94-4b7c-c0b6-bbb84d7318d5"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1000, 9)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the table shape\n",
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "Collapsed": "false",
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "CjvMBNCBR4DK",
    "outputId": "7c990178-2ef8-4fc9-cc8f-0fc4c74f3ea0"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2014    222\n",
       "2013    183\n",
       "2015    173\n",
       "2016    155\n",
       "2017    125\n",
       "2018     83\n",
       "2019     59\n",
       "Name: year, dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# the 'value_counts' method returns the distribution of a specific field eg publication [years]\n",
    "df['year'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "Collapsed": "false",
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "vndAnGmyR4DM",
    "outputId": "4b15e9a3-c914-4fcb-9982-cb47fac9caeb"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "article      995\n",
       "monograph      4\n",
       "book           1\n",
       "Name: type, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# eg distribution of publication [type]\n",
    "df['type'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false",
    "id": "ye1VswB3R4DO"
   },
   "source": [
    "## 2. Dataframe Methods for 'Publications' queries\n",
    "\n",
    "What follows are specialized versions of the `as_dataframe` method for results sets composed of publication records."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false",
    "id": "j75inlUMR4DO"
   },
   "source": [
    "###  Extracting authors: `as_dataframe_authors`\n",
    "\n",
    "Publications authors are usually returned by the Dimensions API inside a nested JSON object in the `authors_affiliations` sub-key.\n",
    "\n",
    "> Note: the order of authors in the JSON is consistent with the ordering of authors in the original publication\n",
    "\n",
    "This methods allows to quickly extract that data and return a dataframe with **one row per author**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "Collapsed": "false",
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 808
    },
    "id": "0_KbE4ylR4DP",
    "outputId": "8b12e23a-18b7-450e-c50c-5d6098988a4e"
   },
   "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>affiliations</th>\n",
       "      <th>corresponding</th>\n",
       "      <th>current_organization_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "      <th>orcid</th>\n",
       "      <th>raw_affiliation</th>\n",
       "      <th>researcher_id</th>\n",
       "      <th>pub_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>[{'city': 'Manchester', 'city_id': 2643123, 'c...</td>\n",
       "      <td></td>\n",
       "      <td>grid.5379.8</td>\n",
       "      <td>A. K.</td>\n",
       "      <td>Geim</td>\n",
       "      <td>[0000-0003-2861-8331]</td>\n",
       "      <td>[School of Physics and Astronomy, University o...</td>\n",
       "      <td>ur.0721730631.45</td>\n",
       "      <td>pub.1024857999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>[{'city': 'Manchester', 'city_id': 2643123, 'c...</td>\n",
       "      <td>True</td>\n",
       "      <td>grid.5379.8</td>\n",
       "      <td>I. V.</td>\n",
       "      <td>Grigorieva</td>\n",
       "      <td>[0000-0001-5991-7778]</td>\n",
       "      <td>[School of Physics and Astronomy, University o...</td>\n",
       "      <td>ur.0767105504.29</td>\n",
       "      <td>pub.1024857999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>[{'city': 'New Brunswick', 'city_id': 5101717,...</td>\n",
       "      <td>True</td>\n",
       "      <td>grid.5335.0</td>\n",
       "      <td>Manish</td>\n",
       "      <td>Chhowalla</td>\n",
       "      <td>None</td>\n",
       "      <td>[Materials Science and Engineering, Rutgers Un...</td>\n",
       "      <td>ur.0633062306.03</td>\n",
       "      <td>pub.1050119463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>[{'city': 'Ulsan', 'city_id': 1833747, 'countr...</td>\n",
       "      <td></td>\n",
       "      <td>grid.42687.3f</td>\n",
       "      <td>Hyeon Suk</td>\n",
       "      <td>Shin</td>\n",
       "      <td>None</td>\n",
       "      <td>[Interdisciplinary School of Green Energy and ...</td>\n",
       "      <td>ur.07617630407.83</td>\n",
       "      <td>pub.1050119463</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>[{'city': 'Singapore', 'city_id': 1880252, 'co...</td>\n",
       "      <td></td>\n",
       "      <td>grid.4280.e</td>\n",
       "      <td>Goki</td>\n",
       "      <td>Eda</td>\n",
       "      <td>None</td>\n",
       "      <td>[Department of Physics, National University of...</td>\n",
       "      <td>ur.01150450507.27</td>\n",
       "      <td>pub.1050119463</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        affiliations corresponding  \\\n",
       "0  [{'city': 'Manchester', 'city_id': 2643123, 'c...                 \n",
       "1  [{'city': 'Manchester', 'city_id': 2643123, 'c...          True   \n",
       "2  [{'city': 'New Brunswick', 'city_id': 5101717,...          True   \n",
       "3  [{'city': 'Ulsan', 'city_id': 1833747, 'countr...                 \n",
       "4  [{'city': 'Singapore', 'city_id': 1880252, 'co...                 \n",
       "\n",
       "  current_organization_id first_name   last_name                  orcid  \\\n",
       "0             grid.5379.8      A. K.        Geim  [0000-0003-2861-8331]   \n",
       "1             grid.5379.8      I. V.  Grigorieva  [0000-0001-5991-7778]   \n",
       "2             grid.5335.0     Manish   Chhowalla                   None   \n",
       "3           grid.42687.3f  Hyeon Suk        Shin                   None   \n",
       "4             grid.4280.e       Goki         Eda                   None   \n",
       "\n",
       "                                     raw_affiliation      researcher_id  \\\n",
       "0  [School of Physics and Astronomy, University o...   ur.0721730631.45   \n",
       "1  [School of Physics and Astronomy, University o...   ur.0767105504.29   \n",
       "2  [Materials Science and Engineering, Rutgers Un...   ur.0633062306.03   \n",
       "3  [Interdisciplinary School of Green Energy and ...  ur.07617630407.83   \n",
       "4  [Department of Physics, National University of...  ur.01150450507.27   \n",
       "\n",
       "           pub_id  \n",
       "0  pub.1024857999  \n",
       "1  pub.1024857999  \n",
       "2  pub.1050119463  \n",
       "3  pub.1050119463  \n",
       "4  pub.1050119463  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "authors = res.as_dataframe_authors()\n",
    "authors.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false",
    "id": "N1VtHVQbR4DQ"
   },
   "source": [
    "Using the authors dataframe, we can easily get the top ten values for `current_organization_id`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "Collapsed": "false",
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "ut6y8Di0R4DR",
    "outputId": "39c1533c-56c6-4959-8160-e9a206c5f545"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "grid.168010.e    148\n",
       "                 133\n",
       "grid.59025.3b     83\n",
       "grid.12527.33     81\n",
       "grid.59053.3a     78\n",
       "grid.19006.3e     78\n",
       "grid.166341.7     65\n",
       "grid.41156.37     64\n",
       "grid.89336.37     63\n",
       "grid.116068.8     60\n",
       "Name: current_organization_id, dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "authors['current_organization_id'].value_counts()[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false",
    "id": "6NpFRYBGR4DS"
   },
   "source": [
    "> Explanation: the most frequent organization turns out to be [grid.168010.e](https://www.grid.ac/institutes/grid.168010.e). The first result is empty, meaning that for those authors Dimensions has no info about `current_organization_id`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false",
    "id": "p2eN2Y8GR4DT"
   },
   "source": [
    "### Extracting Affiliations: `as_dataframe_authors_affiliations`\n",
    "\n",
    "As you can see from the results of the previous section, the `affiliations` of each author is yet another nested JSON object.\n",
    "\n",
    "> Note: the order of affiliations in the JSON is consistent with the affiliations order in the original publication\n",
    "\n",
    "The `as_dataframe_authors_affiliations` method allows to quickly extract that affiliations data and return a dataframe with **one row per affiliation**.\n",
    "\n",
    "This can be useful e.g. if one wants to count research organizations at *the time of writing* (as opposed to `current_organization_id`, which is the *most recent organization* of a researcher)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "Collapsed": "false",
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 477
    },
    "id": "fszvKPHdR4DT",
    "outputId": "a053e7e2-4925-4037-a5ee-e93352a85fad"
   },
   "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>aff_city</th>\n",
       "      <th>aff_city_id</th>\n",
       "      <th>aff_country</th>\n",
       "      <th>aff_country_code</th>\n",
       "      <th>aff_id</th>\n",
       "      <th>aff_name</th>\n",
       "      <th>aff_raw_affiliation</th>\n",
       "      <th>aff_state</th>\n",
       "      <th>aff_state_code</th>\n",
       "      <th>pub_id</th>\n",
       "      <th>researcher_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Manchester</td>\n",
       "      <td>2643123.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>GB</td>\n",
       "      <td>grid.5379.8</td>\n",
       "      <td>University of Manchester</td>\n",
       "      <td>School of Physics and Astronomy, University of...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>pub.1024857999</td>\n",
       "      <td>ur.0721730631.45</td>\n",
       "      <td>A. K.</td>\n",
       "      <td>Geim</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Manchester</td>\n",
       "      <td>2643123.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>GB</td>\n",
       "      <td>grid.5379.8</td>\n",
       "      <td>University of Manchester</td>\n",
       "      <td>Centre for Mesoscience and Nanotechnology, Uni...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>pub.1024857999</td>\n",
       "      <td>ur.0721730631.45</td>\n",
       "      <td>A. K.</td>\n",
       "      <td>Geim</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Manchester</td>\n",
       "      <td>2643123.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>GB</td>\n",
       "      <td>grid.5379.8</td>\n",
       "      <td>University of Manchester</td>\n",
       "      <td>School of Physics and Astronomy, University of...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>pub.1024857999</td>\n",
       "      <td>ur.0767105504.29</td>\n",
       "      <td>I. V.</td>\n",
       "      <td>Grigorieva</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>New Brunswick</td>\n",
       "      <td>5101717.0</td>\n",
       "      <td>United States</td>\n",
       "      <td>US</td>\n",
       "      <td>grid.430387.b</td>\n",
       "      <td>Rutgers, The State University of New Jersey</td>\n",
       "      <td>Materials Science and Engineering, Rutgers Uni...</td>\n",
       "      <td>New Jersey</td>\n",
       "      <td>US-NJ</td>\n",
       "      <td>pub.1050119463</td>\n",
       "      <td>ur.0633062306.03</td>\n",
       "      <td>Manish</td>\n",
       "      <td>Chhowalla</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Ulsan</td>\n",
       "      <td>1833747.0</td>\n",
       "      <td>South Korea</td>\n",
       "      <td>KR</td>\n",
       "      <td>grid.42687.3f</td>\n",
       "      <td>Ulsan National Institute of Science and Techno...</td>\n",
       "      <td>Interdisciplinary School of Green Energy and L...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>pub.1050119463</td>\n",
       "      <td>ur.07617630407.83</td>\n",
       "      <td>Hyeon Suk</td>\n",
       "      <td>Shin</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        aff_city aff_city_id     aff_country aff_country_code         aff_id  \\\n",
       "0     Manchester   2643123.0  United Kingdom               GB    grid.5379.8   \n",
       "1     Manchester   2643123.0  United Kingdom               GB    grid.5379.8   \n",
       "2     Manchester   2643123.0  United Kingdom               GB    grid.5379.8   \n",
       "3  New Brunswick   5101717.0   United States               US  grid.430387.b   \n",
       "4          Ulsan   1833747.0     South Korea               KR  grid.42687.3f   \n",
       "\n",
       "                                            aff_name  \\\n",
       "0                           University of Manchester   \n",
       "1                           University of Manchester   \n",
       "2                           University of Manchester   \n",
       "3        Rutgers, The State University of New Jersey   \n",
       "4  Ulsan National Institute of Science and Techno...   \n",
       "\n",
       "                                 aff_raw_affiliation   aff_state  \\\n",
       "0  School of Physics and Astronomy, University of...               \n",
       "1  Centre for Mesoscience and Nanotechnology, Uni...               \n",
       "2  School of Physics and Astronomy, University of...               \n",
       "3  Materials Science and Engineering, Rutgers Uni...  New Jersey   \n",
       "4  Interdisciplinary School of Green Energy and L...               \n",
       "\n",
       "  aff_state_code          pub_id      researcher_id first_name   last_name  \n",
       "0                 pub.1024857999   ur.0721730631.45      A. K.        Geim  \n",
       "1                 pub.1024857999   ur.0721730631.45      A. K.        Geim  \n",
       "2                 pub.1024857999   ur.0767105504.29      I. V.  Grigorieva  \n",
       "3          US-NJ  pub.1050119463   ur.0633062306.03     Manish   Chhowalla  \n",
       "4                 pub.1050119463  ur.07617630407.83  Hyeon Suk        Shin  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "affiliations = res.as_dataframe_authors_affiliations()\n",
    "affiliations.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "Collapsed": "false",
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 224
    },
    "id": "Ci5OzQyJR4DV",
    "outputId": "140e4459-d7ea-417c-b068-fdb52687945a"
   },
   "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>aff_city</th>\n",
       "      <th>aff_city_id</th>\n",
       "      <th>aff_country</th>\n",
       "      <th>aff_country_code</th>\n",
       "      <th>aff_id</th>\n",
       "      <th>aff_name</th>\n",
       "      <th>aff_raw_affiliation</th>\n",
       "      <th>aff_state</th>\n",
       "      <th>aff_state_code</th>\n",
       "      <th>pub_id</th>\n",
       "      <th>researcher_id</th>\n",
       "      <th>first_name</th>\n",
       "      <th>last_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>8353</td>\n",
       "      <td>8353</td>\n",
       "      <td>8353</td>\n",
       "      <td>8353</td>\n",
       "      <td>8353</td>\n",
       "      <td>8353</td>\n",
       "      <td>8353</td>\n",
       "      <td>8353</td>\n",
       "      <td>8353</td>\n",
       "      <td>8353</td>\n",
       "      <td>8353</td>\n",
       "      <td>8353</td>\n",
       "      <td>8353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>524</td>\n",
       "      <td>527</td>\n",
       "      <td>52</td>\n",
       "      <td>52</td>\n",
       "      <td>852</td>\n",
       "      <td>1132</td>\n",
       "      <td>3155</td>\n",
       "      <td>106</td>\n",
       "      <td>53</td>\n",
       "      <td>990</td>\n",
       "      <td>4619</td>\n",
       "      <td>3645</td>\n",
       "      <td>2406</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>United States</td>\n",
       "      <td>US</td>\n",
       "      <td></td>\n",
       "      <td>Stanford University</td>\n",
       "      <td>Department of Materials Science and Engineerin...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>pub.1019661721</td>\n",
       "      <td></td>\n",
       "      <td>Yi</td>\n",
       "      <td>Wang</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>555</td>\n",
       "      <td>555</td>\n",
       "      <td>2758</td>\n",
       "      <td>2758</td>\n",
       "      <td>830</td>\n",
       "      <td>300</td>\n",
       "      <td>48</td>\n",
       "      <td>5129</td>\n",
       "      <td>5333</td>\n",
       "      <td>108</td>\n",
       "      <td>161</td>\n",
       "      <td>70</td>\n",
       "      <td>308</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       aff_city aff_city_id    aff_country aff_country_code aff_id  \\\n",
       "count      8353        8353           8353             8353   8353   \n",
       "unique      524         527             52               52    852   \n",
       "top                          United States               US          \n",
       "freq        555         555           2758             2758    830   \n",
       "\n",
       "                   aff_name  \\\n",
       "count                  8353   \n",
       "unique                 1132   \n",
       "top     Stanford University   \n",
       "freq                    300   \n",
       "\n",
       "                                      aff_raw_affiliation aff_state  \\\n",
       "count                                                8353      8353   \n",
       "unique                                               3155       106   \n",
       "top     Department of Materials Science and Engineerin...             \n",
       "freq                                                   48      5129   \n",
       "\n",
       "       aff_state_code          pub_id researcher_id first_name last_name  \n",
       "count            8353            8353          8353       8353      8353  \n",
       "unique             53             990          4619       3645      2406  \n",
       "top                    pub.1019661721                       Yi      Wang  \n",
       "freq             5333             108           161         70       308  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "affiliations.describe(include=\"all\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false",
    "id": "at7DTaU5R4DW"
   },
   "source": [
    "Let's get the top ten values for `aff_id`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "Collapsed": "false",
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "o2zUnRg0R4DX",
    "outputId": "26c2987b-a13d-44a8-acf0-05c10a80ddf3"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                 830\n",
       "grid.168010.e    300\n",
       "grid.59025.3b    185\n",
       "grid.166341.7    146\n",
       "grid.8217.c      130\n",
       "grid.19006.3e    119\n",
       "grid.12527.33    105\n",
       "grid.21729.3f     99\n",
       "grid.5379.8       94\n",
       "grid.116068.8     93\n",
       "Name: aff_id, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "affiliations['aff_id'].value_counts()[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false",
    "id": "Zmv0M_PkR4DZ"
   },
   "source": [
    "Another example: we can now easily analyze the data by country too."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "Collapsed": "false",
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "OnmvjI-8R4DZ",
    "outputId": "dc94ee57-03d5-4a44-b0be-2605ebeaac32"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "United States     2758\n",
       "China             2072\n",
       "United Kingdom     377\n",
       "                   375\n",
       "Germany            311\n",
       "Singapore          298\n",
       "South Korea        231\n",
       "Japan              225\n",
       "Australia          187\n",
       "Switzerland        140\n",
       "Name: aff_country, dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "affiliations['aff_country'].value_counts()[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false",
    "id": "a83GEMfQR4Db"
   },
   "source": [
    "> Explanation: the vast majority of authors in this dataset are from China, closely followed by the USA."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false",
    "id": "69JItbFgR4Db"
   },
   "source": [
    "## 3. Dataframe Methods for 'Grants' queries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false",
    "id": "P0FLXthRR4Dc"
   },
   "source": [
    "###  Extracting Funders: `as_dataframe_funders`\n",
    "\n",
    "Grant funders authors are usually returned by the Dimensions API inside a nested JSON object in the `funders` sub-key.\n",
    "\n",
    "This methods allows to quickly extract that data and return a dataframe with **one row per funder**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "Collapsed": "false",
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "kjxgoy_VR4Dc",
    "outputId": "3dbf9e05-9617-4bdb-ec7e-aaa89f033ab8"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Returned Grants: 1000 (total = 14738)\n",
      "\u001b[2mTime: 1.13s\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "# get a sample list of grants\n",
    "query = \"\"\"search grants for \"malaria\" return grants limit 1000\"\"\"\n",
    "res = dsl.query(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "Collapsed": "false",
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 122
    },
    "id": "56iPhGTUR4Dd",
    "outputId": "022dd921-5f8a-4f8a-d629-005a97dfa010"
   },
   "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>grant_id</th>\n",
       "      <th>grant_title</th>\n",
       "      <th>grant_start_date</th>\n",
       "      <th>grant_end_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [grant_id, grant_title, grant_start_date, grant_end_date]\n",
       "Index: []"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res.as_dataframe_funders().head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false",
    "id": "UqanXsrmR4Df"
   },
   "source": [
    "### Extracting investigators: `as_dataframe_investigators`\n",
    "\n",
    "Grant investigators are usually returned by the Dimensions API inside a nested JSON object in the `investigators` sub-key.\n",
    "\n",
    "This methods allows to quickly extract that data and return a dataframe with **one row per investigator**.\n",
    "\n",
    "> NOTE: `investigators` are not returned by default in a grants query hence one must specify this in the query results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "Collapsed": "false",
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "QZS6TePGR4Df",
    "outputId": "bfba06cf-f71c-4417-db19-b02a75440e35"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Returned Grants: 1000 (total = 14738)\n",
      "\u001b[2mTime: 1.74s\u001b[0m\n",
      "WARNINGS [1]\n",
      "Field 'grant_number' is deprecated in favor of project_numbers. Please refer to https://docs.dimensions.ai/dsl/releasenotes.html for more details\n"
     ]
    }
   ],
   "source": [
    "# get a sample list of grants\n",
    "query = \"\"\"search grants for \"malaria\" return grants[basics+investigators] limit 1000\"\"\"\n",
    "res = dsl.query(query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "Collapsed": "false",
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 899
    },
    "id": "5Y4YSw-YBCI2",
    "outputId": "16aceae8-68fd-416d-e76d-f7df61fc3f46"
   },
   "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>affiliations</th>\n",
       "      <th>first_name</th>\n",
       "      <th>id</th>\n",
       "      <th>last_name</th>\n",
       "      <th>middle_name</th>\n",
       "      <th>role</th>\n",
       "      <th>grant_id</th>\n",
       "      <th>grant_title</th>\n",
       "      <th>grant_start_date</th>\n",
       "      <th>grant_end_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>[{'city': 'Melbourne', 'city_id': '2158177', '...</td>\n",
       "      <td>Coralie</td>\n",
       "      <td>None</td>\n",
       "      <td>Boulet</td>\n",
       "      <td></td>\n",
       "      <td>PI</td>\n",
       "      <td>grant.13535694</td>\n",
       "      <td>Regulation of Plasmodium falciparum egress fro...</td>\n",
       "      <td>2024-02-01</td>\n",
       "      <td>2026-01-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>[{'city': 'São Paulo', 'city_id': '3448439', '...</td>\n",
       "      <td>Célia</td>\n",
       "      <td>ur.01311140517.22</td>\n",
       "      <td>da Silva Garcia</td>\n",
       "      <td>Regina</td>\n",
       "      <td>PI</td>\n",
       "      <td>grant.9698278</td>\n",
       "      <td>Calcium signaling in Plasmodium falciparum gam...</td>\n",
       "      <td>2023-11-01</td>\n",
       "      <td>2023-12-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>[{'city': 'Allschwil 3', 'city_id': '2661604',...</td>\n",
       "      <td>Penelope</td>\n",
       "      <td>ur.01356421006.17</td>\n",
       "      <td>Vounatsou</td>\n",
       "      <td></td>\n",
       "      <td>PI</td>\n",
       "      <td>grant.13219531</td>\n",
       "      <td>Spatio-temporal modelling of climate variabili...</td>\n",
       "      <td>2023-10-01</td>\n",
       "      <td>2026-09-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>[{'city': 'Heidelberg', 'city_id': '2907911', ...</td>\n",
       "      <td>Ina</td>\n",
       "      <td>ur.01006224125.53</td>\n",
       "      <td>Danquah</td>\n",
       "      <td></td>\n",
       "      <td>PI</td>\n",
       "      <td>grant.13219531</td>\n",
       "      <td>Spatio-temporal modelling of climate variabili...</td>\n",
       "      <td>2023-10-01</td>\n",
       "      <td>2026-09-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>[{'city': None, 'city_id': '184745', 'country'...</td>\n",
       "      <td>Stephen</td>\n",
       "      <td>ur.01363557726.68</td>\n",
       "      <td>Munga</td>\n",
       "      <td></td>\n",
       "      <td>Co-PI</td>\n",
       "      <td>grant.13219531</td>\n",
       "      <td>Spatio-temporal modelling of climate variabili...</td>\n",
       "      <td>2023-10-01</td>\n",
       "      <td>2026-09-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>[{'city': None, 'city_id': '2357163', 'country...</td>\n",
       "      <td>Ali</td>\n",
       "      <td>ur.01057017117.60</td>\n",
       "      <td>Sié</td>\n",
       "      <td></td>\n",
       "      <td>Co-PI</td>\n",
       "      <td>grant.13219531</td>\n",
       "      <td>Spatio-temporal modelling of climate variabili...</td>\n",
       "      <td>2023-10-01</td>\n",
       "      <td>2026-09-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>[{'city': 'SAN ANTONIO', 'city_id': '4726206',...</td>\n",
       "      <td>Zhuolin</td>\n",
       "      <td>None</td>\n",
       "      <td>Qu</td>\n",
       "      <td>None</td>\n",
       "      <td>PI</td>\n",
       "      <td>grant.13535333</td>\n",
       "      <td>LEAPS-MPS: Development of Novel Multistage Mod...</td>\n",
       "      <td>2023-09-01</td>\n",
       "      <td>2025-08-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>[{'city': None, 'city_id': '2648579', 'country...</td>\n",
       "      <td>Matthias</td>\n",
       "      <td>ur.01315014330.55</td>\n",
       "      <td>Marti</td>\n",
       "      <td></td>\n",
       "      <td>PI</td>\n",
       "      <td>grant.13306682</td>\n",
       "      <td>Defining molecular determinants of Plasmodium ...</td>\n",
       "      <td>2023-08-31</td>\n",
       "      <td>2025-08-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>[{'city': None, 'city_id': '2648579', 'country...</td>\n",
       "      <td>Barbara</td>\n",
       "      <td>ur.013052533674.51</td>\n",
       "      <td>Stokes</td>\n",
       "      <td>Hoyt</td>\n",
       "      <td>PI</td>\n",
       "      <td>grant.13306682</td>\n",
       "      <td>Defining molecular determinants of Plasmodium ...</td>\n",
       "      <td>2023-08-31</td>\n",
       "      <td>2025-08-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Victor</td>\n",
       "      <td>None</td>\n",
       "      <td>Yman</td>\n",
       "      <td></td>\n",
       "      <td>PI</td>\n",
       "      <td>grant.13528667</td>\n",
       "      <td>Integrated serological surveillance of malaria...</td>\n",
       "      <td>2023-07-01</td>\n",
       "      <td>2026-06-30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        affiliations first_name  \\\n",
       "0  [{'city': 'Melbourne', 'city_id': '2158177', '...    Coralie   \n",
       "1  [{'city': 'São Paulo', 'city_id': '3448439', '...      Célia   \n",
       "2  [{'city': 'Allschwil 3', 'city_id': '2661604',...   Penelope   \n",
       "3  [{'city': 'Heidelberg', 'city_id': '2907911', ...        Ina   \n",
       "4  [{'city': None, 'city_id': '184745', 'country'...    Stephen   \n",
       "5  [{'city': None, 'city_id': '2357163', 'country...        Ali   \n",
       "6  [{'city': 'SAN ANTONIO', 'city_id': '4726206',...    Zhuolin   \n",
       "7  [{'city': None, 'city_id': '2648579', 'country...   Matthias   \n",
       "8  [{'city': None, 'city_id': '2648579', 'country...    Barbara   \n",
       "9                                                NaN     Victor   \n",
       "\n",
       "                   id        last_name middle_name   role        grant_id  \\\n",
       "0                None           Boulet                 PI  grant.13535694   \n",
       "1   ur.01311140517.22  da Silva Garcia      Regina     PI   grant.9698278   \n",
       "2   ur.01356421006.17        Vounatsou                 PI  grant.13219531   \n",
       "3   ur.01006224125.53          Danquah                 PI  grant.13219531   \n",
       "4   ur.01363557726.68            Munga              Co-PI  grant.13219531   \n",
       "5   ur.01057017117.60              Sié              Co-PI  grant.13219531   \n",
       "6                None               Qu        None     PI  grant.13535333   \n",
       "7   ur.01315014330.55            Marti                 PI  grant.13306682   \n",
       "8  ur.013052533674.51           Stokes        Hoyt     PI  grant.13306682   \n",
       "9                None             Yman                 PI  grant.13528667   \n",
       "\n",
       "                                         grant_title grant_start_date  \\\n",
       "0  Regulation of Plasmodium falciparum egress fro...       2024-02-01   \n",
       "1  Calcium signaling in Plasmodium falciparum gam...       2023-11-01   \n",
       "2  Spatio-temporal modelling of climate variabili...       2023-10-01   \n",
       "3  Spatio-temporal modelling of climate variabili...       2023-10-01   \n",
       "4  Spatio-temporal modelling of climate variabili...       2023-10-01   \n",
       "5  Spatio-temporal modelling of climate variabili...       2023-10-01   \n",
       "6  LEAPS-MPS: Development of Novel Multistage Mod...       2023-09-01   \n",
       "7  Defining molecular determinants of Plasmodium ...       2023-08-31   \n",
       "8  Defining molecular determinants of Plasmodium ...       2023-08-31   \n",
       "9  Integrated serological surveillance of malaria...       2023-07-01   \n",
       "\n",
       "  grant_end_date  \n",
       "0     2026-01-31  \n",
       "1     2023-12-15  \n",
       "2     2026-09-30  \n",
       "3     2026-09-30  \n",
       "4     2026-09-30  \n",
       "5     2026-09-30  \n",
       "6     2025-08-31  \n",
       "7     2025-08-30  \n",
       "8     2025-08-30  \n",
       "9     2026-06-30  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "res.as_dataframe_investigators().head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false",
    "id": "kzfxeiIABCI3"
   },
   "source": [
    "## 4. Dataframe Methods for 'Concepts' queries\n",
    "\n",
    "These methods can be used with all content types that support the extraction of concepts, i.e., `publications` or `grants`. See the [official documentation](https://docs.dimensions.ai/dsl/data-sources.html) for more details."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false",
    "id": "PQ6N6A7KBCI3"
   },
   "source": [
    "### Extracting Concepts: `as_dataframe_concepts`\n",
    "\n",
    "The `as_dataframe_concepts` method allows to quickly extract all concepts attached to a record, **one row per concept**, so to make it easier to do operations like counting or plotting the results.\n",
    "\n",
    "NOTE: concepts are normalized *noun phrases* describing the main topics of a document, which are automatically derived from the full text  using machine learning. In the JSON data, concepts are available as an ordered list (=first items are the most relevant), including a relevance score. E.g. for the publications with ID 'pub.1122072646':\n",
    "\n",
    "```\n",
    "{'id': 'pub.1122072646',\n",
    "'concepts_scores': [{'concept': 'acid', 'relevance': 0.07450046286579201},\n",
    "                    {'concept': 'conversion', 'relevance': 0.055053872555463006},\n",
    "                    {'concept': 'formic acid', 'relevance': 0.048144671935356},\n",
    "                    {'concept': 'CO2', 'relevance': 0.032150964737607}\n",
    "                    [........]\n",
    "                    ],\n",
    " }\n",
    "```\n",
    "\n",
    "The `as_dataframe_concepts` extracts all concepts data from JSON to a dataframe (ps this is functionally similarly to pandas's [explode method](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.explode.html)). Moreover, it automatically creates a number of metrics that can be used to carry out further analyses, like `frequency` and `score_avg`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "Collapsed": "false",
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 299
    },
    "id": "xc8fYGZtBCI3",
    "outputId": "dd2f5478-7f37-40e0-dc32-1e6993b84617"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Returned Publications: 100 (total = 119693)\n",
      "\u001b[2mTime: 5.27s\u001b[0m\n",
      "Concepts found:  5053\n",
      "Unique:  3213\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>id</th>\n",
       "      <th>title</th>\n",
       "      <th>year</th>\n",
       "      <th>concepts_count</th>\n",
       "      <th>concept</th>\n",
       "      <th>score</th>\n",
       "      <th>frequency</th>\n",
       "      <th>score_avg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>pub.1146069136</td>\n",
       "      <td>Physical Characteristics of Cement Mortar Prep...</td>\n",
       "      <td>2019</td>\n",
       "      <td>7</td>\n",
       "      <td>characteristics</td>\n",
       "      <td>0.070</td>\n",
       "      <td>10</td>\n",
       "      <td>0.30910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>pub.1146069136</td>\n",
       "      <td>Physical Characteristics of Cement Mortar Prep...</td>\n",
       "      <td>2019</td>\n",
       "      <td>7</td>\n",
       "      <td>physical characteristics</td>\n",
       "      <td>0.068</td>\n",
       "      <td>1</td>\n",
       "      <td>0.06800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>pub.1146069136</td>\n",
       "      <td>Physical Characteristics of Cement Mortar Prep...</td>\n",
       "      <td>2019</td>\n",
       "      <td>7</td>\n",
       "      <td>oxide</td>\n",
       "      <td>0.058</td>\n",
       "      <td>14</td>\n",
       "      <td>0.42843</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>pub.1146069136</td>\n",
       "      <td>Physical Characteristics of Cement Mortar Prep...</td>\n",
       "      <td>2019</td>\n",
       "      <td>7</td>\n",
       "      <td>Prepared</td>\n",
       "      <td>0.046</td>\n",
       "      <td>1</td>\n",
       "      <td>0.04600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>pub.1146069136</td>\n",
       "      <td>Physical Characteristics of Cement Mortar Prep...</td>\n",
       "      <td>2019</td>\n",
       "      <td>7</td>\n",
       "      <td>glass</td>\n",
       "      <td>0.026</td>\n",
       "      <td>2</td>\n",
       "      <td>0.22250</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               id                                              title  year  \\\n",
       "0  pub.1146069136  Physical Characteristics of Cement Mortar Prep...  2019   \n",
       "1  pub.1146069136  Physical Characteristics of Cement Mortar Prep...  2019   \n",
       "2  pub.1146069136  Physical Characteristics of Cement Mortar Prep...  2019   \n",
       "3  pub.1146069136  Physical Characteristics of Cement Mortar Prep...  2019   \n",
       "4  pub.1146069136  Physical Characteristics of Cement Mortar Prep...  2019   \n",
       "\n",
       "   concepts_count                   concept  score  frequency  score_avg  \n",
       "0               7           characteristics  0.070         10    0.30910  \n",
       "1               7  physical characteristics  0.068          1    0.06800  \n",
       "2               7                     oxide  0.058         14    0.42843  \n",
       "3               7                  Prepared  0.046          1    0.04600  \n",
       "4               7                     glass  0.026          2    0.22250  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "q = \"\"\"search publications for \"graphene\"\n",
    "            where year=2019\n",
    "       return publications[id+title+year+concepts_scores] limit 100\"\"\"\n",
    "\n",
    "concepts = dsl.query(q).as_dataframe_concepts()\n",
    "\n",
    "print(\"Concepts found: \", len(concepts))\n",
    "print(\"Unique: \", len(concepts.drop_duplicates(\"concept\")))\n",
    "\n",
    "concepts.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "Collapsed": "false",
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 337
    },
    "id": "NyZrwMZABCI3",
    "outputId": "bc32f2dd-105a-4d01-a469-db6a965863d3"
   },
   "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>concept</th>\n",
       "      <th>score_avg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1693</th>\n",
       "      <td>mesoporous silica nanoparticles</td>\n",
       "      <td>0.918</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>558</th>\n",
       "      <td>two-dimensional transition metal dichalcogenides</td>\n",
       "      <td>0.916</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3216</th>\n",
       "      <td>quantum state transfer</td>\n",
       "      <td>0.906</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>559</th>\n",
       "      <td>spin-orbit coupling</td>\n",
       "      <td>0.901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4377</th>\n",
       "      <td>MoP/SiO2 catalyst</td>\n",
       "      <td>0.886</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3699</th>\n",
       "      <td>hydrogen evolution reaction</td>\n",
       "      <td>0.884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3700</th>\n",
       "      <td>oxygen reduction reaction</td>\n",
       "      <td>0.879</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2862</th>\n",
       "      <td>silica nano particles</td>\n",
       "      <td>0.871</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>560</th>\n",
       "      <td>quantum spin Hall effect</td>\n",
       "      <td>0.870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>561</th>\n",
       "      <td>Ising spin-orbit coupling</td>\n",
       "      <td>0.868</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               concept  score_avg\n",
       "1693                   mesoporous silica nanoparticles      0.918\n",
       "558   two-dimensional transition metal dichalcogenides      0.916\n",
       "3216                            quantum state transfer      0.906\n",
       "559                                spin-orbit coupling      0.901\n",
       "4377                                 MoP/SiO2 catalyst      0.886\n",
       "3699                       hydrogen evolution reaction      0.884\n",
       "3700                         oxygen reduction reaction      0.879\n",
       "2862                             silica nano particles      0.871\n",
       "560                           quantum spin Hall effect      0.870\n",
       "561                          Ising spin-orbit coupling      0.868"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "concepts.drop_duplicates(\"concept\").sort_values(\"score_avg\", ascending=False).head(10)[['concept', 'score_avg']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false",
    "id": "Q303kmI-BCI3"
   },
   "source": [
    "Please note that (as of version 1.25 of the DSL API) it is possible to return either `concepts_scores` or `concepts` with Publications queries, but only `concepts` with Grants queries.  \n",
    "\n",
    "If a relevancy score is not available, the `as_dataframe_concepts` function will simulate one based on the concept rank (=its position in the document-concepts list).  \n",
    "\n",
    "For more information on how to make use of `concepts` please see the [Topic Modeling Analysis Tutorial](https://api-lab.dimensions.ai/cookbooks/2-publications/Simple-topic-analysis.html) and the [Official documentation](https://docs.dimensions.ai/dsl/)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "Collapsed": "false",
    "id": "WkolG6VKR4Dj"
   },
   "source": [
    "## Conclusions\n",
    "\n",
    "Moving Dimensions API results to pandas dataframes **makes it easier** to **analyze the data** and **answer research questions**.\n",
    "\n",
    "Note: the examples above only scratch the surface of what can be done with pandas!\n",
    "\n",
    "> Tip: see also the *Dimcli: Magic Commands* notebook to find out what shortcuts are available for these dataframe methods."
   ]
  }
 ],
 "metadata": {
  "colab": {
   "provenance": [],
   "toc_visible": true
  },
  "kernel_info": {
   "name": "python3"
  },
  "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.16"
  },
  "nteract": {
   "version": "0.14.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}