scripts/2_2_py_EXTRACTING_TERMS.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#  Selection of inscriptions containing road-related terms\n",
    "\n",
    "**Research idea & domain expertise:** Petra Hermankova, Aarhus University\n",
    "\n",
    "**Script & technical solution:** Vojtech Kase, Aarhus University\n",
    "\n",
    "Source: https://github.com/sdam-au/social_diversity/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: nltk in /home/petra/.pyenv/versions/3.8.7/lib/python3.8/site-packages (3.5)\n",
      "Requirement already satisfied: gspread in /home/petra/.pyenv/versions/3.8.7/lib/python3.8/site-packages (3.7.0)\n",
      "Requirement already satisfied: sddk in /home/petra/.pyenv/versions/3.8.7/lib/python3.8/site-packages (2.8.2)\n",
      "Requirement already satisfied: gspread_dataframe in /home/petra/.pyenv/versions/3.8.7/lib/python3.8/site-packages (3.2.1)\n",
      "Requirement already satisfied: kaleido in /home/petra/.pyenv/versions/3.8.7/lib/python3.8/site-packages (0.2.1)\n",
      "Requirement already satisfied: google-auth>=1.12.0 in /home/petra/.pyenv/versions/3.8.7/lib/python3.8/site-packages (from gspread) (1.27.1)\n",
      "Requirement already satisfied: google-auth-oauthlib>=0.4.1 in /home/petra/.pyenv/versions/3.8.7/lib/python3.8/site-packages (from gspread) (0.4.3)\n",
      "Requirement already satisfied: pyasn1-modules>=0.2.1 in /home/petra/.pyenv/versions/3.8.7/lib/python3.8/site-packages (from google-auth>=1.12.0->gspread) (0.2.8)\n",
      "Requirement already satisfied: six>=1.9.0 in /home/petra/.local/lib/python3.8/site-packages (from google-auth>=1.12.0->gspread) (1.15.0)\n",
      "Requirement already satisfied: rsa<5,>=3.1.4 in /home/petra/.pyenv/versions/3.8.7/lib/python3.8/site-packages (from google-auth>=1.12.0->gspread) (4.7.2)\n",
      "Requirement already satisfied: cachetools<5.0,>=2.0.0 in /home/petra/.pyenv/versions/3.8.7/lib/python3.8/site-packages (from google-auth>=1.12.0->gspread) (4.2.1)\n",
      "Requirement already satisfied: setuptools>=40.3.0 in /home/petra/.local/lib/python3.8/site-packages (from google-auth>=1.12.0->gspread) (50.3.2)\n",
      "Requirement already satisfied: requests-oauthlib>=0.7.0 in /home/petra/.pyenv/versions/3.8.7/lib/python3.8/site-packages (from google-auth-oauthlib>=0.4.1->gspread) (1.3.0)\n",
      "Requirement already satisfied: pyasn1<0.5.0,>=0.4.6 in /home/petra/.pyenv/versions/3.8.7/lib/python3.8/site-packages (from pyasn1-modules>=0.2.1->google-auth>=1.12.0->gspread) (0.4.8)\n",
      "Requirement already satisfied: oauthlib>=3.0.0 in /home/petra/.pyenv/versions/3.8.7/lib/python3.8/site-packages (from requests-oauthlib>=0.7.0->google-auth-oauthlib>=0.4.1->gspread) (3.1.0)\n",
      "Requirement already satisfied: requests>=2.0.0 in /home/petra/.local/lib/python3.8/site-packages (from requests-oauthlib>=0.7.0->google-auth-oauthlib>=0.4.1->gspread) (2.25.0)\n",
      "Requirement already satisfied: idna<3,>=2.5 in /home/petra/.local/lib/python3.8/site-packages (from requests>=2.0.0->requests-oauthlib>=0.7.0->google-auth-oauthlib>=0.4.1->gspread) (2.10)\n",
      "Requirement already satisfied: urllib3<1.27,>=1.21.1 in /home/petra/.local/lib/python3.8/site-packages (from requests>=2.0.0->requests-oauthlib>=0.7.0->google-auth-oauthlib>=0.4.1->gspread) (1.26.2)\n",
      "Requirement already satisfied: chardet<4,>=3.0.2 in /home/petra/.local/lib/python3.8/site-packages (from requests>=2.0.0->requests-oauthlib>=0.7.0->google-auth-oauthlib>=0.4.1->gspread) (3.0.4)\n",
      "Requirement already satisfied: certifi>=2017.4.17 in /home/petra/.local/lib/python3.8/site-packages (from requests>=2.0.0->requests-oauthlib>=0.7.0->google-auth-oauthlib>=0.4.1->gspread) (2020.11.8)\n",
      "Requirement already satisfied: pandas>=0.24.0 in /home/petra/.local/lib/python3.8/site-packages (from gspread_dataframe) (1.2.2)\n",
      "Requirement already satisfied: pytz>=2017.3 in /home/petra/.local/lib/python3.8/site-packages (from pandas>=0.24.0->gspread_dataframe) (2021.1)\n",
      "Requirement already satisfied: python-dateutil>=2.7.3 in /home/petra/.local/lib/python3.8/site-packages (from pandas>=0.24.0->gspread_dataframe) (2.8.1)\n",
      "Requirement already satisfied: numpy>=1.16.5 in /home/petra/.pyenv/versions/3.8.7/lib/python3.8/site-packages (from pandas>=0.24.0->gspread_dataframe) (1.19.5)\n",
      "Requirement already satisfied: tqdm in /home/petra/.pyenv/versions/3.8.7/lib/python3.8/site-packages (from nltk) (4.59.0)\n",
      "Requirement already satisfied: click in /home/petra/.pyenv/versions/3.8.7/lib/python3.8/site-packages (from nltk) (7.1.2)\n",
      "Requirement already satisfied: joblib in /home/petra/.pyenv/versions/3.8.7/lib/python3.8/site-packages (from nltk) (1.0.1)\n",
      "Requirement already satisfied: regex in /home/petra/.pyenv/versions/3.8.7/lib/python3.8/site-packages (from nltk) (2021.3.17)\n",
      "Requirement already satisfied: beautifulsoup4 in /home/petra/.local/lib/python3.8/site-packages (from sddk) (4.9.3)\n",
      "Requirement already satisfied: pyarrow in /home/petra/.pyenv/versions/3.8.7/lib/python3.8/site-packages (from sddk) (3.0.0)\n",
      "Requirement already satisfied: matplotlib in /home/petra/.pyenv/versions/3.8.7/lib/python3.8/site-packages (from sddk) (3.3.4)\n",
      "Requirement already satisfied: soupsieve>1.2 in /home/petra/.local/lib/python3.8/site-packages (from beautifulsoup4->sddk) (2.0.1)\n",
      "Requirement already satisfied: pillow>=6.2.0 in /home/petra/.pyenv/versions/3.8.7/lib/python3.8/site-packages (from matplotlib->sddk) (8.1.2)\n",
      "Requirement already satisfied: pyparsing!=2.0.4,!=2.1.2,!=2.1.6,>=2.0.3 in /home/petra/.local/lib/python3.8/site-packages (from matplotlib->sddk) (2.4.7)\n",
      "Requirement already satisfied: cycler>=0.10 in /home/petra/.pyenv/versions/3.8.7/lib/python3.8/site-packages (from matplotlib->sddk) (0.10.0)\n",
      "Requirement already satisfied: kiwisolver>=1.0.1 in /home/petra/.pyenv/versions/3.8.7/lib/python3.8/site-packages (from matplotlib->sddk) (1.3.1)\n"
     ]
    }
   ],
   "source": [
    "!pip install nltk gspread sddk gspread_dataframe kaleido\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import nltk\n",
    "import requests\n",
    "import re\n",
    "import json\n",
    "import matplotlib as mpl\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.colors as mcolors\n",
    "tab_colors_list = list(mcolors.TABLEAU_COLORS.keys())\n",
    "import matplotlib.lines as mlines\n",
    "import seaborn as sns\n",
    "\n",
    "import gspread\n",
    "from gspread_dataframe import get_as_dataframe, set_with_dataframe\n",
    "from google.oauth2 import service_account # based on google-auth library\n",
    "\n",
    "import sddk"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sciencedata.dk username (format '123456@au.dk'): 648560@au.dk\n",
      "sciencedata.dk password: ········\n",
      "endpoint variable has been configured to: https://sciencedata.dk/files/\n"
     ]
    }
   ],
   "source": [
    "# to access gsheet, you need Google Service Account key json file\n",
    "# I have mine located in my personal space on sciencedata.dk, so I read it from there:\n",
    "conf = sddk.configure()\n",
    "\n",
    "# (1) read the file and parse its content\n",
    "file_data = conf[0].get(conf[1] + \"ServiceAccountsKey.json\").json()\n",
    "# (2) transform the content into crendentials object\n",
    "credentials = service_account.Credentials.from_service_account_info(file_data)\n",
    "# (3) specify your usage of the credentials\n",
    "scoped_credentials = credentials.with_scopes(['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive'])\n",
    "# (4) use the constrained credentials for authentication of gspread package\n",
    "gc = gspread.Client(auth=scoped_credentials)\n",
    "# (5) establish connection with spreadsheets specified by their url\n",
    "terms = gc.open_by_url(\"https://docs.google.com/spreadsheets/d/1tdtjPCoHY61FSZB0CxAdZXN9xDgl76KU-ObMp4uNG2A/edit#gid=0\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "reading file located in a public folder\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>responsible_individual</th>\n",
       "      <th>type_of_inscription</th>\n",
       "      <th>letter_size</th>\n",
       "      <th>not_after</th>\n",
       "      <th>literature</th>\n",
       "      <th>work_status</th>\n",
       "      <th>height</th>\n",
       "      <th>diplomatic_text</th>\n",
       "      <th>people</th>\n",
       "      <th>depth</th>\n",
       "      <th>...</th>\n",
       "      <th>clean_text_conservative</th>\n",
       "      <th>clean_text_interpretive_word</th>\n",
       "      <th>clean_text_interpretive_sentence</th>\n",
       "      <th>findspot</th>\n",
       "      <th>year_of_find</th>\n",
       "      <th>present_location</th>\n",
       "      <th>religion</th>\n",
       "      <th>geography</th>\n",
       "      <th>social_economic_legal_history</th>\n",
       "      <th>military</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Feraudi</td>\n",
       "      <td>epitaph</td>\n",
       "      <td>3.2-2 cm</td>\n",
       "      <td>0130</td>\n",
       "      <td>AE 1983, 0192.; M. Annecchino, Puteoli 4/5, 19...</td>\n",
       "      <td>provisional</td>\n",
       "      <td>33 cm</td>\n",
       "      <td>D M / NONIAE P F OPTATAE / ET C IVLIO ARTEMONI...</td>\n",
       "      <td>[{'name': 'Noniae P.f. Optatae', 'cognomen': '...</td>\n",
       "      <td>2.7 cm</td>\n",
       "      <td>...</td>\n",
       "      <td>D M Noniae P f Optatae et C Iulio Artemoni par...</td>\n",
       "      <td>Dis Manibus Noniae Publi filiae Optatae et Cai...</td>\n",
       "      <td>Dis Manibus Noniae Publi filiae Optatae et Cai...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Feraudi</td>\n",
       "      <td>epitaph</td>\n",
       "      <td>4 cm</td>\n",
       "      <td>0200</td>\n",
       "      <td>AE 1983, 0080. (A); A. Ferrua, RAL 36, 1981, 1...</td>\n",
       "      <td>no image</td>\n",
       "      <td>28 cm</td>\n",
       "      <td>C SEXTIVS PARIS / QVI VIXIT / ANNIS LXX</td>\n",
       "      <td>[{'nomen': 'Sextius', 'praenomen': 'C.', 'pers...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>C Sextius Paris qui vixit annis LXX</td>\n",
       "      <td>Caius Sextius Paris qui vixit annis LXX</td>\n",
       "      <td>Caius Sextius Paris qui vixit annis LXX</td>\n",
       "      <td>Via Nomentana, S. Alessandro, Kirche</td>\n",
       "      <td>1937</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Feraudi</td>\n",
       "      <td>honorific inscription</td>\n",
       "      <td>4.5-3 cm</td>\n",
       "      <td>0170</td>\n",
       "      <td>AE 1983, 0518. (B); J. González, ZPE 52, 1983,...</td>\n",
       "      <td>provisional</td>\n",
       "      <td>(37) cm</td>\n",
       "      <td>[ ]VMMIO [ ] / [ ]ISENNA[ ] / [ ] XV[ ] / [ ] / [</td>\n",
       "      <td>[{'nomen': 'Mummius+', 'gender': 'male', 'prae...</td>\n",
       "      <td>(12) cm</td>\n",
       "      <td>...</td>\n",
       "      <td>ummio isenna Xv</td>\n",
       "      <td>Publio Mummio Publi filio Galeria Sisennae Rut...</td>\n",
       "      <td>Publio Mummio Publi filio Galeria Sisennae Rut...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>before 1975</td>\n",
       "      <td>Sevilla, Privatbesitz</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Gräf</td>\n",
       "      <td>votive inscription</td>\n",
       "      <td>2.5 cm</td>\n",
       "      <td>0200</td>\n",
       "      <td>AE 1983, 0533. (B); A.U. Stylow, Gerión 1, 198...</td>\n",
       "      <td>checked with photo</td>\n",
       "      <td>(39) cm</td>\n",
       "      <td>[ ]AVS[ ]LLA / M PORCI NIGRI SER / DOMINAE VEN...</td>\n",
       "      <td>[{'name': '[---?]AV(?)S(?)[---]L(?)L(?)A M. Po...</td>\n",
       "      <td>18 cm</td>\n",
       "      <td>...</td>\n",
       "      <td>AVSLLA M Porci Nigri ser dominae Veneri aram p...</td>\n",
       "      <td>AVS LLA Marci Porci Nigri serva dominae Veneri...</td>\n",
       "      <td>AVS LLA Marci Porci Nigri serva dominae Veneri...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>before 1979</td>\n",
       "      <td>Carcabuey, Grupo Escolar</td>\n",
       "      <td>names of pagan deities</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Feraudi</td>\n",
       "      <td>epitaph</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0200</td>\n",
       "      <td>AE 1983, 0078. (B); A. Ferrua, RAL 36, 1981, 1...</td>\n",
       "      <td>no image</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[ ] L SVCCESSVS / [ ] L L IRENAEVS / [ ] C L T...</td>\n",
       "      <td>[{'person_id': '1', 'name': '[---]l. Successus...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>l Successus L l Irenaeus C l Tyches unt renti f</td>\n",
       "      <td>libertus Successus Luci libertus Irenaeus Cai ...</td>\n",
       "      <td>libertus Successus Luci libertus Irenaeus Cai ...</td>\n",
       "      <td>Via Cupa (ehem. Vigna Nardi)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 74 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  responsible_individual    type_of_inscription letter_size not_after  \\\n",
       "0                Feraudi                epitaph    3.2-2 cm      0130   \n",
       "1                Feraudi                epitaph        4 cm      0200   \n",
       "2                Feraudi  honorific inscription    4.5-3 cm      0170   \n",
       "3                   Gräf     votive inscription      2.5 cm      0200   \n",
       "4                Feraudi                epitaph         NaN      0200   \n",
       "\n",
       "                                          literature         work_status  \\\n",
       "0  AE 1983, 0192.; M. Annecchino, Puteoli 4/5, 19...         provisional   \n",
       "1  AE 1983, 0080. (A); A. Ferrua, RAL 36, 1981, 1...            no image   \n",
       "2  AE 1983, 0518. (B); J. González, ZPE 52, 1983,...         provisional   \n",
       "3  AE 1983, 0533. (B); A.U. Stylow, Gerión 1, 198...  checked with photo   \n",
       "4  AE 1983, 0078. (B); A. Ferrua, RAL 36, 1981, 1...            no image   \n",
       "\n",
       "    height                                    diplomatic_text  \\\n",
       "0    33 cm  D M / NONIAE P F OPTATAE / ET C IVLIO ARTEMONI...   \n",
       "1    28 cm            C SEXTIVS PARIS / QVI VIXIT / ANNIS LXX   \n",
       "2  (37) cm  [ ]VMMIO [ ] / [ ]ISENNA[ ] / [ ] XV[ ] / [ ] / [   \n",
       "3  (39) cm  [ ]AVS[ ]LLA / M PORCI NIGRI SER / DOMINAE VEN...   \n",
       "4      NaN  [ ] L SVCCESSVS / [ ] L L IRENAEVS / [ ] C L T...   \n",
       "\n",
       "                                              people    depth  ...  \\\n",
       "0  [{'name': 'Noniae P.f. Optatae', 'cognomen': '...   2.7 cm  ...   \n",
       "1  [{'nomen': 'Sextius', 'praenomen': 'C.', 'pers...      NaN  ...   \n",
       "2  [{'nomen': 'Mummius+', 'gender': 'male', 'prae...  (12) cm  ...   \n",
       "3  [{'name': '[---?]AV(?)S(?)[---]L(?)L(?)A M. Po...    18 cm  ...   \n",
       "4  [{'person_id': '1', 'name': '[---]l. Successus...      NaN  ...   \n",
       "\n",
       "                             clean_text_conservative  \\\n",
       "0  D M Noniae P f Optatae et C Iulio Artemoni par...   \n",
       "1                C Sextius Paris qui vixit annis LXX   \n",
       "2                                    ummio isenna Xv   \n",
       "3  AVSLLA M Porci Nigri ser dominae Veneri aram p...   \n",
       "4    l Successus L l Irenaeus C l Tyches unt renti f   \n",
       "\n",
       "                        clean_text_interpretive_word  \\\n",
       "0  Dis Manibus Noniae Publi filiae Optatae et Cai...   \n",
       "1            Caius Sextius Paris qui vixit annis LXX   \n",
       "2  Publio Mummio Publi filio Galeria Sisennae Rut...   \n",
       "3  AVS LLA Marci Porci Nigri serva dominae Veneri...   \n",
       "4  libertus Successus Luci libertus Irenaeus Cai ...   \n",
       "\n",
       "                    clean_text_interpretive_sentence  \\\n",
       "0  Dis Manibus Noniae Publi filiae Optatae et Cai...   \n",
       "1            Caius Sextius Paris qui vixit annis LXX   \n",
       "2  Publio Mummio Publi filio Galeria Sisennae Rut...   \n",
       "3  AVS LLA Marci Porci Nigri serva dominae Veneri...   \n",
       "4  libertus Successus Luci libertus Irenaeus Cai ...   \n",
       "\n",
       "                               findspot year_of_find  \\\n",
       "0                                   NaN          NaN   \n",
       "1  Via Nomentana, S. Alessandro, Kirche         1937   \n",
       "2                                   NaN  before 1975   \n",
       "3                                   NaN  before 1979   \n",
       "4          Via Cupa (ehem. Vigna Nardi)          NaN   \n",
       "\n",
       "           present_location                religion geography  \\\n",
       "0                       NaN                     NaN       NaN   \n",
       "1                       NaN                     NaN       NaN   \n",
       "2     Sevilla, Privatbesitz                     NaN       NaN   \n",
       "3  Carcabuey, Grupo Escolar  names of pagan deities       NaN   \n",
       "4                       NaN                     NaN       NaN   \n",
       "\n",
       "  social_economic_legal_history military  \n",
       "0                           NaN      NaN  \n",
       "1                           NaN      NaN  \n",
       "2                           NaN      NaN  \n",
       "3                           NaN      NaN  \n",
       "4                           NaN      NaN  \n",
       "\n",
       "[5 rows x 74 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read edh dataset\n",
    "publicfolder = \"b6b6afdb969d378b70929e86e58ad975\"\n",
    "EDH = sddk.read_file(\"EDH_text_cleaned_2021-01-21.json\", \"df\", publicfolder)\n",
    "EDH.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "reading file located in a public folder\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>EDCS-ID</th>\n",
       "      <th>publication</th>\n",
       "      <th>province</th>\n",
       "      <th>province_list</th>\n",
       "      <th>place</th>\n",
       "      <th>place_list</th>\n",
       "      <th>end_yr_list</th>\n",
       "      <th>notes_dating</th>\n",
       "      <th>status_list</th>\n",
       "      <th>inscr_type</th>\n",
       "      <th>...</th>\n",
       "      <th>dating from</th>\n",
       "      <th>dating to</th>\n",
       "      <th>start_yr</th>\n",
       "      <th>end_yr_1</th>\n",
       "      <th>status</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "      <th>photo</th>\n",
       "      <th>Material</th>\n",
       "      <th>Comment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>EDCS-31400030</td>\n",
       "      <td>CIL 03, 12297</td>\n",
       "      <td>Achaia</td>\n",
       "      <td>Achaia</td>\n",
       "      <td>?</td>\n",
       "      <td>?</td>\n",
       "      <td>{}</td>\n",
       "      <td></td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>EDCS-24700151</td>\n",
       "      <td>CIL 01, 02650 (p 1097) = IG-05-01, 00741 = ILL...</td>\n",
       "      <td>Achaia</td>\n",
       "      <td>Achaia</td>\n",
       "      <td>Afesou</td>\n",
       "      <td>Afesou</td>\n",
       "      <td>{}</td>\n",
       "      <td></td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>EDCS-24900077</td>\n",
       "      <td>CIL 01, 00746 (p 944) = D 00867 = ILLRP 00374 ...</td>\n",
       "      <td>Achaia</td>\n",
       "      <td>Achaia</td>\n",
       "      <td>Agia Triada / Merbaka / Midea</td>\n",
       "      <td>[Agia Triada, Merbaka, Midea]</td>\n",
       "      <td>-68</td>\n",
       "      <td></td>\n",
       "      <td>[officium/professio, ordo senatorius, tituli h...</td>\n",
       "      <td>tituli honorarii</td>\n",
       "      <td>...</td>\n",
       "      <td>-68</td>\n",
       "      <td>-68</td>\n",
       "      <td>-68.0</td>\n",
       "      <td>-68.0</td>\n",
       "      <td>officium/professio; ordo senatorius; tituli ho...</td>\n",
       "      <td>37.6381128</td>\n",
       "      <td>22.8052991</td>\n",
       "      <td>http://db.edcs.eu/epigr/bilder.php?bilder.php?...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>EDCS-03700724</td>\n",
       "      <td>ZPE-108-159 = Thesprotia 00001 = AE 1993, 0140...</td>\n",
       "      <td>Achaia</td>\n",
       "      <td>Achaia</td>\n",
       "      <td>Agios Athanasios / Photike</td>\n",
       "      <td>[Agios Athanasios, Photike]</td>\n",
       "      <td>313</td>\n",
       "      <td></td>\n",
       "      <td>[Augusti/Augustae, litterae erasae, ordo eques...</td>\n",
       "      <td>tituli honorarii</td>\n",
       "      <td>...</td>\n",
       "      <td>309</td>\n",
       "      <td>313</td>\n",
       "      <td>309.0</td>\n",
       "      <td>313.0</td>\n",
       "      <td>Augusti/Augustae; litterae erasae; ordo equest...</td>\n",
       "      <td>39.4512182</td>\n",
       "      <td>20.7667673</td>\n",
       "      <td>http://db.edcs.eu/epigr/bilder.php?bilder.php?...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>EDCS-55701593</td>\n",
       "      <td>AE 2009, 01286a</td>\n",
       "      <td>Achaia</td>\n",
       "      <td>Achaia</td>\n",
       "      <td>Agios Donatos / Photike</td>\n",
       "      <td>[Agios Donatos, Photike]</td>\n",
       "      <td>{}</td>\n",
       "      <td></td>\n",
       "      <td>[sigilla impressa, tituli fabricationis]</td>\n",
       "      <td>tituli fabricationis</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>sigilla impressa; tituli fabricationis</td>\n",
       "      <td>39.4759759</td>\n",
       "      <td>20.5069076</td>\n",
       "      <td>NaN</td>\n",
       "      <td>opus figlinae</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 29 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         EDCS-ID                                        publication province  \\\n",
       "0  EDCS-31400030                                      CIL 03, 12297   Achaia   \n",
       "1  EDCS-24700151  CIL 01, 02650 (p 1097) = IG-05-01, 00741 = ILL...   Achaia   \n",
       "2  EDCS-24900077  CIL 01, 00746 (p 944) = D 00867 = ILLRP 00374 ...   Achaia   \n",
       "3  EDCS-03700724  ZPE-108-159 = Thesprotia 00001 = AE 1993, 0140...   Achaia   \n",
       "4  EDCS-55701593                                    AE 2009, 01286a   Achaia   \n",
       "\n",
       "  province_list                          place                     place_list  \\\n",
       "0        Achaia                              ?                              ?   \n",
       "1        Achaia                         Afesou                         Afesou   \n",
       "2        Achaia  Agia Triada / Merbaka / Midea  [Agia Triada, Merbaka, Midea]   \n",
       "3        Achaia     Agios Athanasios / Photike    [Agios Athanasios, Photike]   \n",
       "4        Achaia        Agios Donatos / Photike       [Agios Donatos, Photike]   \n",
       "\n",
       "  end_yr_list notes_dating                                        status_list  \\\n",
       "0          {}                                                              {}   \n",
       "1          {}                                                              {}   \n",
       "2         -68               [officium/professio, ordo senatorius, tituli h...   \n",
       "3         313               [Augusti/Augustae, litterae erasae, ordo eques...   \n",
       "4          {}                        [sigilla impressa, tituli fabricationis]   \n",
       "\n",
       "             inscr_type  ... dating from dating to start_yr end_yr_1  \\\n",
       "0                    {}  ...         NaN       NaN      NaN      NaN   \n",
       "1                    {}  ...         NaN       NaN      NaN      NaN   \n",
       "2      tituli honorarii  ...         -68       -68    -68.0    -68.0   \n",
       "3      tituli honorarii  ...         309       313    309.0    313.0   \n",
       "4  tituli fabricationis  ...         NaN       NaN      NaN      NaN   \n",
       "\n",
       "                                              status    Latitude   Longitude  \\\n",
       "0                                                NaN         NaN         NaN   \n",
       "1                                                NaN         NaN         NaN   \n",
       "2  officium/professio; ordo senatorius; tituli ho...  37.6381128  22.8052991   \n",
       "3  Augusti/Augustae; litterae erasae; ordo equest...  39.4512182  20.7667673   \n",
       "4             sigilla impressa; tituli fabricationis  39.4759759  20.5069076   \n",
       "\n",
       "                                               photo       Material Comment  \n",
       "0                                                NaN            NaN     NaN  \n",
       "1                                                NaN            NaN     NaN  \n",
       "2  http://db.edcs.eu/epigr/bilder.php?bilder.php?...            NaN     NaN  \n",
       "3  http://db.edcs.eu/epigr/bilder.php?bilder.php?...            NaN     NaN  \n",
       "4                                                NaN  opus figlinae     NaN  \n",
       "\n",
       "[5 rows x 29 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# read edcs dataset\n",
    "publicfolder = \"1f5f56d09903fe259c0906add8b3a55e\"\n",
    "EDCS = sddk.read_file(\"EDCS_text_cleaned_2021-03-01.json\", \"df\", publicfolder)\n",
    "EDCS.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read declined terms\n",
    "with open(\"../output/terms_declined_dict.json\", \"r\") as fp:\n",
    "    terms_declined_dict = json.load(fp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['millia passuum',\n",
       " 'curator viarum',\n",
       " 'deverticulum',\n",
       " 'diverticulum',\n",
       " 'tabellarium',\n",
       " 'itinerarium',\n",
       " 'vehiculatio',\n",
       " 'caput viae',\n",
       " 'milliarium',\n",
       " 'angiportus',\n",
       " 'tabelarium',\n",
       " 'miliarium',\n",
       " 'carpentum',\n",
       " 'vehiculum',\n",
       " 'incessus',\n",
       " 'iumentum',\n",
       " 'compitum',\n",
       " 'terminus',\n",
       " 'mutatio',\n",
       " 'gressus']"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check that our terms are properly arranged (from the longest...)\n",
    "list(terms_declined_dict.keys())[:20]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#  Function to extract terms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def extract_terms_v1(inscription_text):\n",
    "    terms_found = []\n",
    "    for terms in terms_declined_dict.keys():\n",
    "        for terms_morph in terms_declined_dict[terms]:\n",
    "            try:\n",
    "                terms_morph_N = inscription_text.lower().count(terms_morph)\n",
    "                if terms_morph_N > 0:\n",
    "                    terms_found.extend([terms] * terms_morph_N)\n",
    "                    inscription_text = inscription_text.replace(terms_morph, \"\")\n",
    "                \n",
    "            except: pass\n",
    "    return terms_found"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def extract_terms_v2(inscription_text):\n",
    "    terms_found = []\n",
    "    try:\n",
    "        inscription_text = inscription_text.lower()\n",
    "    except:\n",
    "        inscription_text = \"\"\n",
    "    for terms in terms_declined_dict.keys():\n",
    "        for terms_morph in terms_declined_dict[terms]:\n",
    "            try:\n",
    "                if terms_morph in inscription_text: # first check it this way, otherwise skip\n",
    "                    terms_morph_N = len(re.findall(\"(\\W|^)\" + terms_morph + \"(\\W|$)\", inscription_text))\n",
    "                    terms_found.extend([terms] * terms_morph_N)\n",
    "                    inscription_text = inscription_text.replace(terms_morph, \"\")\n",
    "            except: pass\n",
    "    return terms_found"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def extract_terms(inscription_text):\n",
    "    terms_found = []\n",
    "    if not isinstance(inscription_text, str): # if not valid string\n",
    "        inscription_text = \"\"\n",
    "    for terms in terms_declined_dict.keys():\n",
    "        for terms_morph in terms_declined_dict[terms]:\n",
    "            try:\n",
    "                if terms_morph in inscription_text: # first check it this way, otherwise skip\n",
    "                    terms_morph_N = len(re.findall(\"(\\W|^)\" + terms_morph + \"(\\W|$)\", inscription_text))\n",
    "                    terms_found.extend([terms] * terms_morph_N)\n",
    "                    inscription_text = inscription_text.replace(terms_morph, \"\")\n",
    "            except: pass\n",
    "    return terms_found"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['miliarium']"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "extract_terms(\"fdfsdf. miliaria,\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Test on sample"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1000"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sample for testing\n",
    "EDH_sample = EDH[:1000]\n",
    "len(EDH_sample)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 95.3 ms, sys: 0 ns, total: 95.3 ms\n",
      "Wall time: 95.1 ms\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<timed exec>:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "EDH_sample[\"terms\"] = EDH_sample[\"clean_text_interpretive_word\"].apply(extract_terms)\n",
    "EDH_terms_list = [el for sublist in EDH_sample[\"terms\"].tolist() for el in sublist]\n",
    "len(EDH_terms_list) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-18-9d593ef465b3>:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  EDH_sample[\"terms_N\"] = EDH_sample[\"terms\"].apply(len)\n"
     ]
    }
   ],
   "source": [
    "EDH_sample[\"terms_N\"] = EDH_sample[\"terms\"].apply(len)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "14"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(EDH_sample[EDH_sample[\"terms_N\"] > 0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "ename": "APIError",
     "evalue": "{'code': 400, 'message': 'Invalid requests[0].addSheet: A sheet with the name \"EDH_terms_sample\" already exists. Please enter another name.', 'status': 'INVALID_ARGUMENT'}",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mAPIError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-20-6ff3e657c221>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0;31m# create sample and export it to gsheet\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[0mEDH_terms_sample\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mEDH_sample\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mEDH_sample\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"terms_N\"\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m>\u001b[0m \u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"id\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"clean_text_interpretive_word\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"terms\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"type_of_inscription_clean\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 3\u001b[0;31m \u001b[0mset_with_dataframe\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mterms\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0madd_worksheet\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"EDH_terms_sample\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m1\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m1\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mEDH_terms_sample\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m~/.pyenv/versions/3.8.7/lib/python3.8/site-packages/gspread/models.py\u001b[0m in \u001b[0;36madd_worksheet\u001b[0;34m(self, title, rows, cols, index)\u001b[0m\n\u001b[1;32m    356\u001b[0m             \u001b[0mbody\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"requests\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"addSheet\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"properties\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"index\"\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mindex\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    357\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 358\u001b[0;31m         \u001b[0mdata\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbatch_update\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mbody\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    359\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    360\u001b[0m         \u001b[0mproperties\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdata\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'replies'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'addSheet'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'properties'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/.pyenv/versions/3.8.7/lib/python3.8/site-packages/gspread/models.py\u001b[0m in \u001b[0;36mbatch_update\u001b[0;34m(self, body)\u001b[0m\n\u001b[1;32m    144\u001b[0m         \u001b[0;34m.\u001b[0m\u001b[0;34m.\u001b[0m \u001b[0mversionadded\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0;36m3.0\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    145\u001b[0m         \"\"\"\n\u001b[0;32m--> 146\u001b[0;31m         r = self.client.request(\n\u001b[0m\u001b[1;32m    147\u001b[0m             \u001b[0;34m'post'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mSPREADSHEET_BATCH_UPDATE_URL\u001b[0m \u001b[0;34m%\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mid\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mjson\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mbody\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    148\u001b[0m         )\n",
      "\u001b[0;32m~/.pyenv/versions/3.8.7/lib/python3.8/site-packages/gspread/client.py\u001b[0m in \u001b[0;36mrequest\u001b[0;34m(self, method, endpoint, params, data, json, files, headers)\u001b[0m\n\u001b[1;32m     74\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mresponse\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     75\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 76\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mAPIError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresponse\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     77\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     78\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mlist_spreadsheet_files\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtitle\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mAPIError\u001b[0m: {'code': 400, 'message': 'Invalid requests[0].addSheet: A sheet with the name \"EDH_terms_sample\" already exists. Please enter another name.', 'status': 'INVALID_ARGUMENT'}"
     ]
    }
   ],
   "source": [
    "# create sample and export it to gsheet\n",
    "EDH_terms_sample = EDH_sample[EDH_sample[\"terms_N\"] > 0][[\"id\", \"clean_text_interpretive_word\", \"terms\", \"type_of_inscription_clean\"]]\n",
    "set_with_dataframe(terms.add_worksheet(\"EDH_terms_sample\", 1, 1), EDH_terms_sample)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Application to the whole dataset (EDH)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 3.52 s, sys: 0 ns, total: 3.52 s\n",
      "Wall time: 3.53 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "EDH[\"terms\"] = EDH[\"clean_text_interpretive_word\"].apply(extract_terms)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1810\n",
      "[('via', 371), ('miliarium', 288), ('milliarium', 282), ('vicus', 216), ('leuga', 107), ('statio', 104), ('viator', 102), ('porta', 73), ('iter', 57), ('arcus', 54), ('terminus', 42), ('gradus', 22), ('curator viarum', 20), ('vehiculum', 18), ('iumentum', 10), ('pons', 8), ('cursus', 7), ('compitum', 7), ('clivus', 5), ('millia passuum', 5), ('scala', 4), ('actus', 2), ('mansio', 2), ('tabellarium', 1), ('semita', 1), ('deverticulum', 1), ('mutatio', 1)]\n"
     ]
    }
   ],
   "source": [
    "EDH_terms_list = [el for sublist in EDH[\"terms\"].tolist() for el in sublist]\n",
    "print(len(EDH_terms_list))\n",
    "print(nltk.FreqDist(EDH_terms_list).most_common(30))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "EDH[\"terms_N\"] = EDH[\"terms\"].apply(len)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Application to the whole dataset (EDCS)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 15.5 s, sys: 0 ns, total: 15.5 s\n",
      "Wall time: 15.5 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "EDCS[\"terms\"] = EDCS[\"clean_text_interpretive_word\"].apply(extract_terms)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4615\n",
      "[('via', 1109), ('miliarium', 1061), ('vicus', 585), ('statio', 334), ('viator', 307), ('porta', 221), ('leuga', 182), ('iter', 172), ('arcus', 141), ('terminus', 110), ('gradus', 76), ('curator viarum', 56), ('vehiculum', 46), ('cursus', 37), ('pons', 30), ('compitum', 25), ('actus', 25), ('clivus', 20), ('scala', 18), ('tabellarium', 15), ('iumentum', 15), ('gressus', 5), ('semita', 5), ('passus', 4), ('mansio', 4), ('carpentum', 4), ('mutatio', 3), ('angiportus', 2), ('deverticulum', 2), ('incessus', 1)]\n"
     ]
    }
   ],
   "source": [
    "EDCS_terms_list = [el for sublist in EDCS[\"terms\"].tolist() for el in sublist]\n",
    "print(len(EDCS_terms_list))\n",
    "print(nltk.FreqDist(EDCS_terms_list).most_common(30))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "EDCS[\"terms_N\"] = EDCS[\"terms\"].apply(len)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Terms overview"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "EDH - number of term occurances: 1810\n",
      "EDH - number of inscriptions with at least one term mentioned: 1370\n",
      "EDH - number of dated inscriptions with at least one term mentioned: 997\n",
      "EDH - number of term occurances in dated inscriptions: 1289\n"
     ]
    }
   ],
   "source": [
    "print(\"EDH - number of term occurances: \" + str(EDH[\"terms_N\"].sum()))\n",
    "print(\"EDH - number of inscriptions with at least one term mentioned: \" + str(len(EDH[EDH[\"terms_N\"] > 0])))\n",
    "print(\"EDH - number of dated inscriptions with at least one term mentioned: \"+ str(len(EDH[(EDH[\"not_before\"].notnull()) & (EDH[\"terms_N\"] > 0)])))\n",
    "print(\"EDH - number of term occurances in dated inscriptions: \" + str(EDH[EDH[\"not_before\"].notnull()][\"terms_N\"].sum()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "EDCS - number of term occurances: 4615\n",
      "EDCS - number of inscriptions with at least one term mentioned: 3597\n",
      "EDCS - number of dated inscriptions with at least one term mentioned: 2318\n",
      "EDCS - number of term occurances in dated inscriptions: 3163\n"
     ]
    }
   ],
   "source": [
    "print(\"EDCS - number of term occurances: \" + str(EDCS[\"terms_N\"].sum()))\n",
    "print(\"EDCS - number of inscriptions with at least one term mentioned: \" + str(len(EDCS[EDCS[\"terms_N\"] > 0])))\n",
    "print(\"EDCS - number of dated inscriptions with at least one term mentioned: \"+ str(len(EDCS[(EDCS[\"dating to\"].notnull()) & (EDCS[\"terms_N\"] > 0)])))\n",
    "print(\"EDCS - number of term occurances in dated inscriptions: \" + str(EDCS[EDCS[\"dating to\"].notnull()][\"terms_N\"].sum()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Saving to Sciencedata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sciencedata.dk username (format '123456@au.dk'): 648560@au.dk\n",
      "sciencedata.dk password: ········\n",
      "connection with shared folder established with you as its ordinary user\n",
      "endpoint variable has been configured to: https://sciencedata.dk/sharingout/648597%40au.dk/SDAM_root/\n"
     ]
    }
   ],
   "source": [
    "# login to our project folder, owned by my AU account 648597@au.dk\n",
    "conf = sddk.configure(\"SDAM_root\", \"648597@au.dk\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Your <class 'pandas.core.frame.DataFrame'> object has been succefully written as \"https://sciencedata.dk/sharingout/648597%40au.dk/SDAM_root/SDAM_data/epigraphic_roads/EDH_terms_2021-03-25.json\"\n",
      "Your <class 'pandas.core.frame.DataFrame'> object has been succefully written as \"https://sciencedata.dk/sharingout/648597%40au.dk/SDAM_root/SDAM_data/epigraphic_roads/EDCS_terms_2021-03-25.json\"\n"
     ]
    }
   ],
   "source": [
    "sddk.write_file(\"SDAM_data/epigraphic_roads/EDH_terms_2021-03-25.json\", EDH, conf)\n",
    "sddk.write_file(\"SDAM_data/epigraphic_roads/EDCS_terms_2021-03-25.json\", EDCS, conf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "hide_input": false,
  "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.8.7"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}