db_add_coreferee_resolved_texts.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "066f2d62-e7e9-4d10-a7ec-da8e60521af7",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "f196311f-f223-4d97-a214-0afbbb83b60e",
   "metadata": {},
   "source": [
    "## Création de l'environnement conda-pip\n",
    "\n",
    "Cf. the [Instructions](./instructions.md)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "712e116e-3e86-4aaa-8121-7d2c172078c4",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import spacy\n",
    "import psycopg2\n",
    "import pandas as pd\n",
    "from spacy.tokens import Span\n",
    "from spacy import displacy\n",
    "from time import strftime, gmtime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "b7a57bdf-defc-418c-910a-58d465781ca4",
   "metadata": {},
   "outputs": [],
   "source": [
    "from itables import init_notebook_mode, show\n",
    "import re\n",
    "from importlib import reload"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "fc9cfa55-0537-4cb6-9b61-5ed879eba7fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "import coreferee"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "537eb469-1c63-4013-b9c6-edab74d04db3",
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "3dbd57c8-850d-4697-90b5-a941dde522c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import postgresql_functions as pgf\n",
    "import settings as stt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "d9c09e9c-beac-4366-84f7-b1005e1e236d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# reload(pgf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9c95f4d1-09cd-4bd3-b849-3891917a49ef",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "555ae1ee-608a-46dd-b72a-cbba4becb720",
   "metadata": {},
   "outputs": [],
   "source": [
    "### connect to the local database\n",
    "conn = psycopg2.connect(host=\"localhost\", port = 5432, database=\"espace_intellectuel\", \n",
    "                        user=\"postgres\", password=stt.dbw)\n",
    "#conn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a5eb12a7-e779-4f77-853c-ccfc86ac9871",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "80bf535d-cbe0-4997-8955-e0419c393e8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "q1 = \"\"\"\n",
    "select pk_mathshistory, biography, length(biography) AS size\n",
    "from mathshistory.mathshistory m\n",
    "order by pk_mathshistory;\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "36cac17f-ae81-471b-909b-1172c96838b6",
   "metadata": {},
   "outputs": [],
   "source": [
    "result = pgf.sql_explore(q1, conn)\n",
    "# print(f'Lines count: {len(result[0])}, errors count: {len(result[1])}, \\nFirst lines: {result[0][:5]}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "6ff46ad1-b1f7-47cc-8302-04a6b6cb2ef0",
   "metadata": {},
   "outputs": [],
   "source": [
    "textes = pd.DataFrame(result[0])\n",
    "textes.columns = ['id', 'texte', 'size']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "cbfead55-0dd5-4393-8fac-16b6e6671f74",
   "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>texte</th>\n",
       "      <th>size</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Nothing was known about al-Nasawi in Europe un...</td>\n",
       "      <td>4282</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Jia Xian is also known as Chia Hsien. Almost n...</td>\n",
       "      <td>5492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Hermann of Reichenau is also called Hermann th...</td>\n",
       "      <td>12391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Sripati's father was Nagadeva (sometimes writt...</td>\n",
       "      <td>3575</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>We note that there are several versions of al-...</td>\n",
       "      <td>12675</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                                              texte   size\n",
       "0   1  Nothing was known about al-Nasawi in Europe un...   4282\n",
       "1   2  Jia Xian is also known as Chia Hsien. Almost n...   5492\n",
       "2   3  Hermann of Reichenau is also called Hermann th...  12391\n",
       "3   4  Sripati's father was Nagadeva (sometimes writt...   3575\n",
       "4   5  We note that there are several versions of al-...  12675"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "textes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "e51d160c-f17f-4baf-b1fa-c15facf28cfb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3010, 2927)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(textes), len(textes[textes['size'] > 1500])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "cc57c251-1549-42c2-a7ed-f4b725586764",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Jia Xian is also known as Chia Hsien. Almost nothing is known about his life. It is recorded that he was a pupil of Chu Yan who was a famous calendarist, astronomer and mathematician. We know that Chu Yan was productive over the years 1022 to 1054 so he must have tutored Jia Xian at some time betwee\n"
     ]
    }
   ],
   "source": [
    "### Choose one document\n",
    "txt = textes.iloc[1].texte \n",
    "print(txt[:300])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "a9b307ac-0875-4ce7-b215-0b242a42a7d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "#txt = \"We have quoted above from Biancani concerning his high regard for Galileo. However, he did not always agree with Galileo's views. The first disagreement came in 1611 and concerned the mountains on the moon. Galileo had observed the surface of the moon through a telescope in 1609 and had used certain mathematical techniques to prove that there were lunar mountains. His claim appeared in Sidereus Nuncius published in May 1610. In May 1611 a group of scientists, mostly Jesuits, was brought together by cardinal Ferdinando Gonzaga in Mantua to discuss Galileo's claims. One of the major points discussed was Galileo's proof that there were mountains on the moon, and the report from the group came down firmly in favour of the traditional belief that the moon was perfectly smooth. Galileo suspected that Biancani was the author of the report and letters were exchanged in which Biancani dissociated himself from any insult towards Galileo saying that he was sorry if he had been offended but, nevertheless, pointing out that he did believe that the moon was perfectly smooth. He also disagreed with Galileo in 1613 when a dispute broke out between Galileo and Christoph Scheiner over sunspots. Galileo unfairly accused Scheiner of plagiarism but, although Scheiner's discovery of sunspots was certainly independent of any work by Galileo, his explanation was quite wrong. Biancani, however, defended his fellow Jesuit Scheiner.\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "df06753e-d60e-44c6-8079-7033cfe224df",
   "metadata": {},
   "outputs": [],
   "source": [
    "#print(txt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f71f22f5-1c0a-4db3-ac64-894ab06e9228",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "6295a341-1352-4362-9bef-e67d2811cd57",
   "metadata": {},
   "source": [
    "## Coreferee"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "4a2736c1-92cb-4130-8e44-24a266322141",
   "metadata": {},
   "outputs": [],
   "source": [
    "nlp = spacy.load('en_core_web_lg')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "1a9e7245-764e-43a6-bccc-1022c0676f2a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<coreferee.manager.CorefereeBroker at 0x7f807ffb9480>"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### https://spacy.io/universe/project/coreferee\n",
    "nlp.add_pipe('coreferee')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "df4bfccd-ea15-4bb5-97b0-38cd96c6bcbf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'spacy_version': '3.5.3',\n",
       " 'location': '/home/francesco/miniconda3/envs/py310_pip_spacy/lib/python3.10/site-packages/spacy',\n",
       " 'platform': 'Linux-5.14.0-1059-oem-x86_64-with-glibc2.31',\n",
       " 'python_version': '3.10.11',\n",
       " 'pipelines': {'en_core_web_lg': '3.1.0', 'en_core_web_trf': '3.5.0'}}"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spacy.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "7df67835-c90f-4125-b110-ea913e4684c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "doc = nlp(txt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "19aead54-6f5f-4e45-a412-caad1b25093c",
   "metadata": {
    "scrolled": true,
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0: Xian(1), his(14), he(21), Xian(57), Xian(74), Xian(92)\n",
      "1: Yan(27), Yan(42), he(52), Yan(71), his(80)\n",
      "2: years(47), years(63)\n",
      "3: Emperor(110), his(125), women(137), men(142), Emperor(162)\n",
      "4: men(118), their(152), they(156)\n",
      "5: Both(215), its(231), it(240)\n",
      "6: Hui(256), Hui(297), Hui(320)\n",
      "7: Suanfa(260), he(303), his(306)\n",
      "8: work(288), work(311)\n",
      "9: Xian(325), Xian(342), Xian(371), Xian(419), He(473), Xian(520)\n",
      "10: Pascal(336), Pascal(366), Pascal(391)\n",
      "11: coefficients(378), coefficients(397)\n",
      "12: contribution(451), it(466)\n",
      "13: algorithm(454), algorithm(510)\n",
      "14: method(476), method(500)\n",
      "15: method(528), method(534)\n",
      "16: method(546), method(557)\n",
      "17: x3=146363183x^{3(561), x^{3(570)\n",
      "18: lt(573), lt(581), lt(585)\n",
      "19: Qiujian(843), she(849)\n",
      "20: Xian(853), Xian(908), Xian(961), Xian(1011)\n",
      "21: examination(866), It(897)\n",
      "22: Samawal(881), Samawal(914), Samawal(965), Samawal(994)\n",
      "23: approximation(925), approximation(945)\n"
     ]
    }
   ],
   "source": [
    "doc._.coref_chains.print()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "90902213-ff15-4910-9ba1-08d5cdef1b7b",
   "metadata": {
    "scrolled": true,
    "tags": []
   },
   "outputs": [],
   "source": [
    "### Produce resolved text\n",
    "# https://stackoverflow.com/questions/75204212/spacy-coreferee-how-to-cleanly-extract-coreferenced-text\n",
    "\n",
    "resolved_text = \"\"\n",
    "\n",
    "for token in doc[:]:\n",
    "  \n",
    "    repres = doc._.coref_chains.resolve(token)\n",
    "    # print(repres)\n",
    "    if repres:\n",
    "        c = \" and \".join([t.text for t in repres])\n",
    "        # resolved_text += \" \" + c\n",
    "        resolved_text += c + \" \"\n",
    "        # print(c)\n",
    "    else:\n",
    "        #resolved_text += \" \" + token.text\n",
    "        resolved_text += token.text_with_ws\n",
    "\n",
    "print(resolved_text)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "424663d4-5e23-4a8d-a977-6943aa6108f8",
   "metadata": {},
   "outputs": [],
   "source": [
    "def resolve_text(doc):\n",
    "    \n",
    "    resolved_text = \"\"\n",
    "    \n",
    "    for token in doc:\n",
    "  \n",
    "        repres = doc._.coref_chains.resolve(token)\n",
    "        # print(repres)\n",
    "        if repres:\n",
    "            c = \" and \".join([t.text for t in repres])\n",
    "            # resolved_text += \" \" + c\n",
    "            resolved_text += c + \" \"\n",
    "            # print(c)\n",
    "        else:\n",
    "            #resolved_text += \" \" + token.text\n",
    "            resolved_text += token.text_with_ws\n",
    "        \n",
    "    return resolved_text    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "b6f54fc7-e1c2-4f85-bb3d-e6639981fa22",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"Jia Xian is also known as Chia Hsien. Almost nothing is known about Xian life. It is recorded that Xian was a pupil of Chu Yan who was a famous calendarist, astronomer and mathematician. We know that Chu Yan was productive over the years 1022 to 1054 so Yan must have tutored Jia Xian at some time between these years . Other evidence would suggest that Chu Yan taught Jia Xian fairly near the beginning of Yan career.\\n\\nAccording to Qian [3], Jia Xian was a Palace Eunuch of the Left Duty Group. This requires a little explanation. The Emperor of China would employ eunuchs, castrated men, as guards and servants in Emperor Palace. Although the original role was that of guarding the Emperor 's quarters, these Emperor achieved real power and influence. In addition to men role as guards men became confidential advisers to the Emperor , and sometimes government ministers.\\n\\nJia Xian is known to have written two mathematics books: Huangdi Jiuzhang Suanjing Xicao (The Yellow Emperor's detailed solut\""
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "resolve_text(doc)[:1000]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "179c22d9-25b4-4461-8860-479ee3636fa6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "48e71954-a790-4df9-baf9-e24abaf3783a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "f25f5295-f4fb-4e86-af56-d111ca229d26",
   "metadata": {},
   "source": [
    "## Import into database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "667ad16b-8ee7-4f63-82ad-cb28416580cb",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "54c1dc81-1c1d-4e31-bed5-a875264509b5",
   "metadata": {
    "scrolled": true,
    "tags": []
   },
   "outputs": [],
   "source": [
    "### Je n'ai retenu que ceux à plus que 1500 !\n",
    "# il faudra reprendre les autres\n",
    "\n",
    "ll = textes[textes['size'] > 1500].values.tolist()\n",
    "len(ll), ll[:2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3a13ecd1-9efb-43c8-925b-c0d0d6afbb38",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "53eed81a-8fdc-4ca8-a20f-bd0b448c9067",
   "metadata": {},
   "source": [
    "UPDATE astronomers.mathshistory SET coreferenced_txt = null;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "1592b4ce-492c-4d3b-9777-00450cbfbe6e",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "### Next line commented to avoid disruption\n",
    "\n",
    "for t in ll:\n",
    "\n",
    "    error = []\n",
    "    \n",
    "    doc = nlp(t[1])\n",
    "        \n",
    "    rt = resolve_text(doc).replace(\"'\", \"\\\\\\'\")\n",
    "    # print(type(rt), len(rt))\n",
    "    \n",
    "    \n",
    "    with conn.cursor() as curs:\n",
    "        \n",
    "        ### commented to avoid disruption\n",
    "        #  try:\n",
    "            qs = f\"\"\"\n",
    "            UPDATE astronomers.mathshistory SET coreferenced_txt = E'{rt}'\n",
    "            WHERE pk_mathshistory = {t[0]};\n",
    "            \"\"\"\n",
    "            \n",
    "            curs.execute(qs)\n",
    "            conn.commit()\n",
    "        except Exception as e:\n",
    "            error.append([t[0], e])\n",
    "            # print(error)\n",
    "            with open('spacy/logs_errors_coreferenced.txt', 'a') as f:\n",
    "                f.write(f'd{str(error)} — Error — {strftime(\"%a, %d %b %Y %H:%M:%S +0000\", gmtime())}\\n\\n')\n",
    "            conn.rollback()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "960bfedb-a142-4e84-a441-f69d56982af7",
   "metadata": {},
   "source": [
    "### Errors\n",
    "\n",
    "I had three not treated texts: 2527, 2827, 2876 — cf. logs: logs_errors_coreferenced.txt\n",
    "\n",
    "[22 juin 2023] I decided not to treat them for the moment"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d00c5657-e730-4fca-b42f-30914511d4ab",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "py310_pip_spacy",
   "language": "python",
   "name": "py310_pip_spacy"
  },
  "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.10.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}