OpenAlexSparkTutorial.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e3e9f7eb",
   "metadata": {},
   "source": [
    "# 初始化"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2819d26b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "            <div>\n",
       "                <p><b>SparkSession - in-memory</b></p>\n",
       "                \n",
       "        <div>\n",
       "            <p><b>SparkContext</b></p>\n",
       "\n",
       "            <p><a href=\"http://172.17.60.211:4041\">Spark UI</a></p>\n",
       "\n",
       "            <dl>\n",
       "              <dt>Version</dt>\n",
       "                <dd><code>v3.1.2</code></dd>\n",
       "              <dt>Master</dt>\n",
       "                <dd><code>spark://c8mao:7077</code></dd>\n",
       "              <dt>AppName</dt>\n",
       "                <dd><code>MarioZZJ_OpenAlex</code></dd>\n",
       "            </dl>\n",
       "        </div>\n",
       "        \n",
       "            </div>\n",
       "        "
      ],
      "text/plain": [
       "<pyspark.sql.session.SparkSession at 0x7f22ad9c20f0>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# 初始化，请填写appname参数，其中包含你的用户名和本次使用数据集\n",
    "%run spark_openalex_init.py --appname=MarioZZJ_OpenAlex"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "99b7ea2f",
   "metadata": {},
   "source": [
    "可以在新标签页访问上面的 Spark UI 链接，查看任务执行进度，也可以去 Spark UI 总页面，查看多个 app 的并行运行情况。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b85fd468",
   "metadata": {},
   "source": [
    "# 查看 schema\n",
    "\n",
    "此时我们获取了 27 个 `pyspark.sql.dataframe.DataFrame` 对象，分别对应 27 张表，可以使用 `showSchema()` 方法查看字段。\n",
    "\n",
    "27 个对象的变量名：`Authors`, `AuthorsIds`, `AuthorsCountsByYear`, `Concepts`, `ConceptsAncestors`, `ConceptsCountsByYear`, `ConceptsIds`, `ConceptsRelatedConcepts`, `Institutions`, `InstitutionsAssociatedInstitutions`, `InstitutionsCountsByYear`, `InstitutionsGeo`, `InstitutionsIds`, `Works`, `WorksAuthorships`, `WorksAlternateHostVenues`, `WorksBiblio`, `WorksConcepts`, `WorksHostVenues`, `WorksIds`, `WorksMesh`, `WorksOpenAccess`, `WorksRelatedWorks`, `WorksReferencedWorks`, `Venues`, `VenuesCountsByYear`, `VenuesIds`。\n",
    "\n",
    "各表详细的 Schema 可以查看 [OpenAlex 文档](https://docs.openalex.org/download-snapshot/upload-to-your-database/load-to-a-relational-database/postgres-schema-diagram)。\n",
    "![schema](https://2520693015-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FSj6S26Opvy3KVj3QQGMc%2Fuploads%2FS3eqe0lsHYmqxJTcPa9V%2Fopenalex-schema.png?alt=media&token=64f070a8-ca96-4639-96d7-06d8a6ea659d)\n",
    "\n",
    "> 注：图中部分字段名打错了，如 [display_name_acronyms](https://gist.github.com/richard-orr/152d828356a7c47ed7e3e22d2253708d?permalink_comment_id=4366952#gistcomment-4366952)，建议以实际读取的 schema 为准。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "503a6a18",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- id: long (nullable = true)\n",
      " |-- orcid: string (nullable = true)\n",
      " |-- display_name: string (nullable = true)\n",
      " |-- display_name_alternatives: string (nullable = true)\n",
      " |-- works_count: integer (nullable = true)\n",
      " |-- cited_by_count: integer (nullable = true)\n",
      " |-- last_known_institution: string (nullable = true)\n",
      " |-- works_api_url: string (nullable = true)\n",
      " |-- updated_date: date (nullable = true)\n",
      " |-- display_name_alternatives_array: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "Authors.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "68a5f7e3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[id: bigint, doi: string, title: string, display_name: string, publication_year: int, publication_date: string, type: string, cited_by_count: int, is_retracted: boolean, is_paratext: boolean, host_venue: string]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Works"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e62e2ee",
   "metadata": {},
   "source": [
    "# 案例：模糊检索\n",
    "类似于 SQL 中的 `WHERE` `LIKE`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "7afa5bdd",
   "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>id</th>\n",
       "      <th>doi</th>\n",
       "      <th>title</th>\n",
       "      <th>display_name</th>\n",
       "      <th>publication_year</th>\n",
       "      <th>publication_date</th>\n",
       "      <th>type</th>\n",
       "      <th>cited_by_count</th>\n",
       "      <th>is_retracted</th>\n",
       "      <th>is_paratext</th>\n",
       "      <th>host_venue</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4206974766</td>\n",
       "      <td>https://doi.org/10.14393/ufu.di.2022.24</td>\n",
       "      <td>A atuação da UFU frente a pandemia de covid-19...</td>\n",
       "      <td>A atuação da UFU frente a pandemia de covid-19...</td>\n",
       "      <td>2022</td>\n",
       "      <td>2022-01-24</td>\n",
       "      <td>dissertation</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>https://api.openalex.org/works?filter=cites:W4...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4210273141</td>\n",
       "      <td>https://doi.org/10.7476/9786557081587.0027</td>\n",
       "      <td>A experiência do Proqualis na produção e divul...</td>\n",
       "      <td>A experiência do Proqualis na produção e divul...</td>\n",
       "      <td>2022</td>\n",
       "      <td>2022-01-26</td>\n",
       "      <td>book-chapter</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>https://api.openalex.org/works?filter=cites:W4...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4210584131</td>\n",
       "      <td>https://doi.org/10.1016/j.shaw.2021.12.1275</td>\n",
       "      <td>Which jobs are lucky against the “biologic” an...</td>\n",
       "      <td>Which jobs are lucky against the “biologic” an...</td>\n",
       "      <td>2022</td>\n",
       "      <td>2022-01-01</td>\n",
       "      <td>journal-article</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>https://api.openalex.org/works?filter=cites:W4...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3216835453</td>\n",
       "      <td>https://doi.org/10.47573/aya.88580.2.48.12</td>\n",
       "      <td>O impacto da covid-19 no sistema de obras públ...</td>\n",
       "      <td>O impacto da covid-19 no sistema de obras públ...</td>\n",
       "      <td>2021</td>\n",
       "      <td>2021-11-30</td>\n",
       "      <td>book-chapter</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>https://api.openalex.org/works?filter=cites:W3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3216821236</td>\n",
       "      <td>https://doi.org/10.1080/09647775.2021.2002509</td>\n",
       "      <td>Centering the museum: writings for the post-co...</td>\n",
       "      <td>Centering the museum: writings for the post-co...</td>\n",
       "      <td>2021</td>\n",
       "      <td>2021-11-29</td>\n",
       "      <td>journal-article</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>https://api.openalex.org/works?filter=cites:W3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4200127697</td>\n",
       "      <td>https://doi.org/10.1136/bmj.n2989</td>\n",
       "      <td>Could it be covid? Update information to stop ...</td>\n",
       "      <td>Could it be covid? Update information to stop ...</td>\n",
       "      <td>2021</td>\n",
       "      <td>2021-12-03</td>\n",
       "      <td>journal-article</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>https://api.openalex.org/works?filter=cites:W4...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4200006623</td>\n",
       "      <td>https://doi.org/10.31080/asol.2021.03.0362</td>\n",
       "      <td>Post-covid Rhino Cerebral Mucor Mycosis: Neuro...</td>\n",
       "      <td>Post-covid Rhino Cerebral Mucor Mycosis: Neuro...</td>\n",
       "      <td>2021</td>\n",
       "      <td>2021-12-01</td>\n",
       "      <td>journal-article</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>https://api.openalex.org/works?filter=cites:W4...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>4200077283</td>\n",
       "      <td>https://doi.org/10.1016/j.euroneuro.2021.10.199</td>\n",
       "      <td>P.0206 Mental health during covid-19 infection</td>\n",
       "      <td>P.0206 Mental health during covid-19 infection</td>\n",
       "      <td>2021</td>\n",
       "      <td>2021-12-01</td>\n",
       "      <td>journal-article</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>https://api.openalex.org/works?filter=cites:W4...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>4200263580</td>\n",
       "      <td>https://doi.org/10.1016/j.euroneuro.2021.10.249</td>\n",
       "      <td>P.0259 Topic modeling analysis of publications...</td>\n",
       "      <td>P.0259 Topic modeling analysis of publications...</td>\n",
       "      <td>2021</td>\n",
       "      <td>2021-12-01</td>\n",
       "      <td>journal-article</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>https://api.openalex.org/works?filter=cites:W4...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>4200419006</td>\n",
       "      <td>https://doi.org/10.1590/scielopreprints.3380</td>\n",
       "      <td>Como superar a estagnação da curva de cobertur...</td>\n",
       "      <td>Como superar a estagnação da curva de cobertur...</td>\n",
       "      <td>2021</td>\n",
       "      <td>2021-12-20</td>\n",
       "      <td>posted-content</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>https://api.openalex.org/works?filter=cites:W4...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           id                                              doi  \\\n",
       "0  4206974766          https://doi.org/10.14393/ufu.di.2022.24   \n",
       "1  4210273141       https://doi.org/10.7476/9786557081587.0027   \n",
       "2  4210584131      https://doi.org/10.1016/j.shaw.2021.12.1275   \n",
       "3  3216835453       https://doi.org/10.47573/aya.88580.2.48.12   \n",
       "4  3216821236    https://doi.org/10.1080/09647775.2021.2002509   \n",
       "5  4200127697                https://doi.org/10.1136/bmj.n2989   \n",
       "6  4200006623       https://doi.org/10.31080/asol.2021.03.0362   \n",
       "7  4200077283  https://doi.org/10.1016/j.euroneuro.2021.10.199   \n",
       "8  4200263580  https://doi.org/10.1016/j.euroneuro.2021.10.249   \n",
       "9  4200419006     https://doi.org/10.1590/scielopreprints.3380   \n",
       "\n",
       "                                               title  \\\n",
       "0  A atuação da UFU frente a pandemia de covid-19...   \n",
       "1  A experiência do Proqualis na produção e divul...   \n",
       "2  Which jobs are lucky against the “biologic” an...   \n",
       "3  O impacto da covid-19 no sistema de obras públ...   \n",
       "4  Centering the museum: writings for the post-co...   \n",
       "5  Could it be covid? Update information to stop ...   \n",
       "6  Post-covid Rhino Cerebral Mucor Mycosis: Neuro...   \n",
       "7     P.0206 Mental health during covid-19 infection   \n",
       "8  P.0259 Topic modeling analysis of publications...   \n",
       "9  Como superar a estagnação da curva de cobertur...   \n",
       "\n",
       "                                        display_name  publication_year  \\\n",
       "0  A atuação da UFU frente a pandemia de covid-19...              2022   \n",
       "1  A experiência do Proqualis na produção e divul...              2022   \n",
       "2  Which jobs are lucky against the “biologic” an...              2022   \n",
       "3  O impacto da covid-19 no sistema de obras públ...              2021   \n",
       "4  Centering the museum: writings for the post-co...              2021   \n",
       "5  Could it be covid? Update information to stop ...              2021   \n",
       "6  Post-covid Rhino Cerebral Mucor Mycosis: Neuro...              2021   \n",
       "7     P.0206 Mental health during covid-19 infection              2021   \n",
       "8  P.0259 Topic modeling analysis of publications...              2021   \n",
       "9  Como superar a estagnação da curva de cobertur...              2021   \n",
       "\n",
       "  publication_date             type  cited_by_count  is_retracted  \\\n",
       "0       2022-01-24     dissertation               0         False   \n",
       "1       2022-01-26     book-chapter               0         False   \n",
       "2       2022-01-01  journal-article               0         False   \n",
       "3       2021-11-30     book-chapter               0         False   \n",
       "4       2021-11-29  journal-article               0         False   \n",
       "5       2021-12-03  journal-article               0         False   \n",
       "6       2021-12-01  journal-article               0         False   \n",
       "7       2021-12-01  journal-article               0         False   \n",
       "8       2021-12-01  journal-article               0         False   \n",
       "9       2021-12-20   posted-content               0         False   \n",
       "\n",
       "   is_paratext                                         host_venue  \n",
       "0        False  https://api.openalex.org/works?filter=cites:W4...  \n",
       "1        False  https://api.openalex.org/works?filter=cites:W4...  \n",
       "2        False  https://api.openalex.org/works?filter=cites:W4...  \n",
       "3        False  https://api.openalex.org/works?filter=cites:W3...  \n",
       "4        False  https://api.openalex.org/works?filter=cites:W3...  \n",
       "5        False  https://api.openalex.org/works?filter=cites:W4...  \n",
       "6        False  https://api.openalex.org/works?filter=cites:W4...  \n",
       "7        False  https://api.openalex.org/works?filter=cites:W4...  \n",
       "8        False  https://api.openalex.org/works?filter=cites:W4...  \n",
       "9        False  https://api.openalex.org/works?filter=cites:W4...  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查找前 10 篇标题包含'covid'的论文\n",
    "works = Works.filter(\n",
    "            Works.display_name.like('%covid%') # filter() 过滤中填入表达式，可以用`==`精确匹配，也可用 like() 模糊匹配\n",
    "        ).limit(10).toPandas() # 建议探索时多用 limit，减少对资源的占用 \n",
    "                    # toPandas() 转化为 dataframe，直接在 notebook 展示\n",
    "works"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e490273a",
   "metadata": {},
   "source": [
    "# 案例：表连接\n",
    "类似于 SQL 中的 `JOIN`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b7be945a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[work_id: bigint, referenced_work_id: bigint]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "WorksReferencedWorks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "54ad8f6e",
   "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>id</th>\n",
       "      <th>doi</th>\n",
       "      <th>title</th>\n",
       "      <th>display_name</th>\n",
       "      <th>publication_year</th>\n",
       "      <th>publication_date</th>\n",
       "      <th>type</th>\n",
       "      <th>cited_by_count</th>\n",
       "      <th>is_retracted</th>\n",
       "      <th>is_paratext</th>\n",
       "      <th>host_venue</th>\n",
       "      <th>work_id</th>\n",
       "      <th>referenced_work_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3213651567</td>\n",
       "      <td>https://doi.org/10.48102/pi.v29i3.414</td>\n",
       "      <td>Psicología basada en la evidencia en tiempos d...</td>\n",
       "      <td>Psicología basada en la evidencia en tiempos d...</td>\n",
       "      <td>2021</td>\n",
       "      <td>2021-11-09</td>\n",
       "      <td>journal-article</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>https://api.openalex.org/works?filter=cites:W3...</td>\n",
       "      <td>3213651567</td>\n",
       "      <td>1493925022</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3213651567</td>\n",
       "      <td>https://doi.org/10.48102/pi.v29i3.414</td>\n",
       "      <td>Psicología basada en la evidencia en tiempos d...</td>\n",
       "      <td>Psicología basada en la evidencia en tiempos d...</td>\n",
       "      <td>2021</td>\n",
       "      <td>2021-11-09</td>\n",
       "      <td>journal-article</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>https://api.openalex.org/works?filter=cites:W3...</td>\n",
       "      <td>3213651567</td>\n",
       "      <td>1765504387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3213651567</td>\n",
       "      <td>https://doi.org/10.48102/pi.v29i3.414</td>\n",
       "      <td>Psicología basada en la evidencia en tiempos d...</td>\n",
       "      <td>Psicología basada en la evidencia en tiempos d...</td>\n",
       "      <td>2021</td>\n",
       "      <td>2021-11-09</td>\n",
       "      <td>journal-article</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>https://api.openalex.org/works?filter=cites:W3...</td>\n",
       "      <td>3213651567</td>\n",
       "      <td>3039521750</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3213651567</td>\n",
       "      <td>https://doi.org/10.48102/pi.v29i3.414</td>\n",
       "      <td>Psicología basada en la evidencia en tiempos d...</td>\n",
       "      <td>Psicología basada en la evidencia en tiempos d...</td>\n",
       "      <td>2021</td>\n",
       "      <td>2021-11-09</td>\n",
       "      <td>journal-article</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>https://api.openalex.org/works?filter=cites:W3...</td>\n",
       "      <td>3213651567</td>\n",
       "      <td>3081416251</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3213651567</td>\n",
       "      <td>https://doi.org/10.48102/pi.v29i3.414</td>\n",
       "      <td>Psicología basada en la evidencia en tiempos d...</td>\n",
       "      <td>Psicología basada en la evidencia en tiempos d...</td>\n",
       "      <td>2021</td>\n",
       "      <td>2021-11-09</td>\n",
       "      <td>journal-article</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>https://api.openalex.org/works?filter=cites:W3...</td>\n",
       "      <td>3213651567</td>\n",
       "      <td>3094350895</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3213651567</td>\n",
       "      <td>https://doi.org/10.48102/pi.v29i3.414</td>\n",
       "      <td>Psicología basada en la evidencia en tiempos d...</td>\n",
       "      <td>Psicología basada en la evidencia en tiempos d...</td>\n",
       "      <td>2021</td>\n",
       "      <td>2021-11-09</td>\n",
       "      <td>journal-article</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>https://api.openalex.org/works?filter=cites:W3...</td>\n",
       "      <td>3213651567</td>\n",
       "      <td>3132890237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3213651567</td>\n",
       "      <td>https://doi.org/10.48102/pi.v29i3.414</td>\n",
       "      <td>Psicología basada en la evidencia en tiempos d...</td>\n",
       "      <td>Psicología basada en la evidencia en tiempos d...</td>\n",
       "      <td>2021</td>\n",
       "      <td>2021-11-09</td>\n",
       "      <td>journal-article</td>\n",
       "      <td>0</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>https://api.openalex.org/works?filter=cites:W3...</td>\n",
       "      <td>3213651567</td>\n",
       "      <td>3172256592</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           id                                    doi  \\\n",
       "0  3213651567  https://doi.org/10.48102/pi.v29i3.414   \n",
       "1  3213651567  https://doi.org/10.48102/pi.v29i3.414   \n",
       "2  3213651567  https://doi.org/10.48102/pi.v29i3.414   \n",
       "3  3213651567  https://doi.org/10.48102/pi.v29i3.414   \n",
       "4  3213651567  https://doi.org/10.48102/pi.v29i3.414   \n",
       "5  3213651567  https://doi.org/10.48102/pi.v29i3.414   \n",
       "6  3213651567  https://doi.org/10.48102/pi.v29i3.414   \n",
       "\n",
       "                                               title  \\\n",
       "0  Psicología basada en la evidencia en tiempos d...   \n",
       "1  Psicología basada en la evidencia en tiempos d...   \n",
       "2  Psicología basada en la evidencia en tiempos d...   \n",
       "3  Psicología basada en la evidencia en tiempos d...   \n",
       "4  Psicología basada en la evidencia en tiempos d...   \n",
       "5  Psicología basada en la evidencia en tiempos d...   \n",
       "6  Psicología basada en la evidencia en tiempos d...   \n",
       "\n",
       "                                        display_name  publication_year  \\\n",
       "0  Psicología basada en la evidencia en tiempos d...              2021   \n",
       "1  Psicología basada en la evidencia en tiempos d...              2021   \n",
       "2  Psicología basada en la evidencia en tiempos d...              2021   \n",
       "3  Psicología basada en la evidencia en tiempos d...              2021   \n",
       "4  Psicología basada en la evidencia en tiempos d...              2021   \n",
       "5  Psicología basada en la evidencia en tiempos d...              2021   \n",
       "6  Psicología basada en la evidencia en tiempos d...              2021   \n",
       "\n",
       "  publication_date             type  cited_by_count  is_retracted  \\\n",
       "0       2021-11-09  journal-article               0         False   \n",
       "1       2021-11-09  journal-article               0         False   \n",
       "2       2021-11-09  journal-article               0         False   \n",
       "3       2021-11-09  journal-article               0         False   \n",
       "4       2021-11-09  journal-article               0         False   \n",
       "5       2021-11-09  journal-article               0         False   \n",
       "6       2021-11-09  journal-article               0         False   \n",
       "\n",
       "   is_paratext                                         host_venue     work_id  \\\n",
       "0        False  https://api.openalex.org/works?filter=cites:W3...  3213651567   \n",
       "1        False  https://api.openalex.org/works?filter=cites:W3...  3213651567   \n",
       "2        False  https://api.openalex.org/works?filter=cites:W3...  3213651567   \n",
       "3        False  https://api.openalex.org/works?filter=cites:W3...  3213651567   \n",
       "4        False  https://api.openalex.org/works?filter=cites:W3...  3213651567   \n",
       "5        False  https://api.openalex.org/works?filter=cites:W3...  3213651567   \n",
       "6        False  https://api.openalex.org/works?filter=cites:W3...  3213651567   \n",
       "\n",
       "   referenced_work_id  \n",
       "0          1493925022  \n",
       "1          1765504387  \n",
       "2          3039521750  \n",
       "3          3081416251  \n",
       "4          3094350895  \n",
       "5          3132890237  \n",
       "6          3172256592  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查找文献x的前10篇引文\n",
    "selected_citations = Works.filter(Works.id==3213651567).join(\n",
    "    WorksReferencedWorks, # 第一个参数：连接的表\n",
    "    Works.id == WorksReferencedWorks.work_id, # 第二个参数：连接依据，where\n",
    "    how = 'inner') # 第三个参数：连接方式，这里为 inner join\n",
    "selected_citations.limit(10).toPandas() # 如果没有调用 toPandas()、show()等，变量仅保留计算图，并未实际开始计算"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb50a30c",
   "metadata": {},
   "source": [
    "# 案例：聚合统计\n",
    "类似于 SQL 中的 `GROUP BY`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "63d156ca",
   "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>work_id</th>\n",
       "      <th>Mesh_D_Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2513796179</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      work_id  Mesh_D_Count\n",
       "0  2513796179             7"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 统计文献x的关联mesh heading数\n",
    "selected_meshes = WorksMesh.filter(WorksMesh.work_id == 2513796179)\\\n",
    "                    .groupBy(WorksMesh.work_id).agg(# agg() 聚合函数\n",
    "                    F.countDistinct(WorksMesh.descriptor_ui)# countDistinct为去重计数，更多函数可查看 pyspark.sql.functions 的手册\n",
    "                    .alias(\"Mesh_D_Count\"))# alias 为新生成的列指定别名\n",
    "\n",
    "selected_meshes.toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c53d926e",
   "metadata": {},
   "source": [
    "# 案例：列裁剪\n",
    "类似于 SQL 中的 `SELECT`。对于研究需要，大多数表我们只需要部分字段。尽早进行列裁剪对于减少计算压力具有一定帮助。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "126296ce",
   "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>id</th>\n",
       "      <th>doi</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3143064787</td>\n",
       "      <td>https://doi.org/10.18800/psico.202001.010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3144438434</td>\n",
       "      <td>https://doi.org/10.20878/cshr.2019.25.12.020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3146640534</td>\n",
       "      <td>https://doi.org/10.17759/sps.2020110105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3015405040</td>\n",
       "      <td>https://doi.org/10.3726/b16812</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3148351627</td>\n",
       "      <td>https://doi.org/10.1590/0034-7167-2018-0192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3149532180</td>\n",
       "      <td>https://doi.org/10.25178/nit.2019.1.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2949504104</td>\n",
       "      <td>https://doi.org/10.1097/01.hjh.0000570716.9836...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>3150456942</td>\n",
       "      <td>https://doi.org/10.31866/2616-745x.4.2019.177614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3150791969</td>\n",
       "      <td>https://doi.org/10.32431/kace.2020.23.1.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>3151025289</td>\n",
       "      <td>https://doi.org/10.35180/gse</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           id                                                doi\n",
       "0  3143064787          https://doi.org/10.18800/psico.202001.010\n",
       "1  3144438434       https://doi.org/10.20878/cshr.2019.25.12.020\n",
       "2  3146640534            https://doi.org/10.17759/sps.2020110105\n",
       "3  3015405040                     https://doi.org/10.3726/b16812\n",
       "4  3148351627        https://doi.org/10.1590/0034-7167-2018-0192\n",
       "5  3149532180              https://doi.org/10.25178/nit.2019.1.2\n",
       "6  2949504104  https://doi.org/10.1097/01.hjh.0000570716.9836...\n",
       "7  3150456942   https://doi.org/10.31866/2616-745x.4.2019.177614\n",
       "8  3150791969        https://doi.org/10.32431/kace.2020.23.1.005\n",
       "9  3151025289                       https://doi.org/10.35180/gse"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 取出 Works 表的前 10 条数据，只需要 id 和 doi 两个字段\n",
    "works_sub = Works.select(\n",
    "                Works.id,\n",
    "                Works.doi\n",
    ")\n",
    "works_sub.limit(10).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cea125fb",
   "metadata": {},
   "source": [
    "# 案例：保存数据\n",
    "有时我们需要将中间结果进行保存，如果数据量较小，可以对 toPandas() 的 dataframe 对象直接使用 pandas 库的方法保存，而当数据量较大时，建议使用 spark 的 `write()` 方法保存为多个文件，减小内存和运算压力。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "95dd86dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 取出 Works 表的前 1000 条数据，只需要 id 和 doi 两个字段，保存至指定文件夹\n",
    "works_sub.limit(1000).write.csv(\"./data/id_doi\",header=True,mode=\"overwrite\") # header 为 True 时，每个子文件均包含表头（建议，方便后续读取）"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "23cb0c71",
   "metadata": {},
   "source": [
    "此时在 `./data/id_doi` 文件夹下就会存储若干个文件，保存所有取出数据"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f473bbf",
   "metadata": {},
   "source": [
    "# 案例：读取数据\n",
    "对于上述保存的分片数据，同样可以使用 spark 的 `read()` 方法将其读取为 `pyspark.sql.dataframe.DataFrame` 对象，进行后续操作。 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "3855a5a4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pyspark.sql.dataframe.DataFrame'>\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>doi</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4205445286</td>\n",
       "      <td>https://doi.org/10.1109/auteee52864.2021.9668613</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4205722486</td>\n",
       "      <td>https://doi.org/10.1201/9781003181613-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4205932699</td>\n",
       "      <td>https://doi.org/10.37715/rmbe.v1i2.2414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4205601445</td>\n",
       "      <td>https://doi.org/10.1093/hepl/9780198840626.003...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4205604766</td>\n",
       "      <td>https://doi.org/10.1088/1755-1315/949/1/012104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4205398072</td>\n",
       "      <td>https://doi.org/10.3390/molecules27010277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4205115136</td>\n",
       "      <td>https://doi.org/10.1051/bioconf/20224301019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>4205115748</td>\n",
       "      <td>https://doi.org/10.24917/20831765.16.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>4205790373</td>\n",
       "      <td>https://doi.org/10.7311/acta.57.2021.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>4205181778</td>\n",
       "      <td>https://doi.org/10.1109/icic54025.2021.9632982</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           id                                                doi\n",
       "0  4205445286   https://doi.org/10.1109/auteee52864.2021.9668613\n",
       "1  4205722486           https://doi.org/10.1201/9781003181613-17\n",
       "2  4205932699            https://doi.org/10.37715/rmbe.v1i2.2414\n",
       "3  4205601445  https://doi.org/10.1093/hepl/9780198840626.003...\n",
       "4  4205604766     https://doi.org/10.1088/1755-1315/949/1/012104\n",
       "5  4205398072          https://doi.org/10.3390/molecules27010277\n",
       "6  4205115136        https://doi.org/10.1051/bioconf/20224301019\n",
       "7  4205115748             https://doi.org/10.24917/20831765.16.2\n",
       "8  4205790373            https://doi.org/10.7311/acta.57.2021.03\n",
       "9  4205181778     https://doi.org/10.1109/icic54025.2021.9632982"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "works_sub = spark.read.csv(\"./data/id_doi\", header=True, inferSchema=True)\n",
    "print(type(works_sub))\n",
    "works_sub.limit(10).toPandas()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7da1c1d0",
   "metadata": {},
   "source": [
    "更多使用方法，可以参考 [PySpark 手册](https://spark.apache.org/docs/3.1.2/api/python/getting_started/index.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d32184d1",
   "metadata": {},
   "source": [
    "# 重要：释放 session\n",
    "\n",
    "请在使用资源时保持良好习惯，结束使用时执行下列语句对占用资源进行释放；或 shutdown notebook 也可对资源进行释放。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "32e86b03",
   "metadata": {},
   "outputs": [],
   "source": [
    "spark.sparkContext.stop()\n",
    "spark.stop()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}