Glossary_NamedEntities_V3b.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Testing the Named Entities Recognition engine of Spacy\n",
    "\n",
    "### Step 1. Loading Spacy models\n",
    "***\n",
    "\n",
    "We install Spacy's language library for the first run. Then we can comment-out the download command. Note that we are loading Spacy's \"medium\" model.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: en-core-web-md==3.0.0 from https://github.com/explosion/spacy-models/releases/download/en_core_web_md-3.0.0/en_core_web_md-3.0.0-py3-none-any.whl#egg=en_core_web_md==3.0.0 in d:\\programdata\\anaconda3\\lib\\site-packages (3.0.0)\n",
      "Requirement already satisfied: spacy<3.1.0,>=3.0.0 in d:\\programdata\\anaconda3\\lib\\site-packages (from en-core-web-md==3.0.0) (3.0.3)\n",
      "Requirement already satisfied: preshed<3.1.0,>=3.0.2 in d:\\programdata\\anaconda3\\lib\\site-packages (from spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (3.0.5)\n",
      "Requirement already satisfied: blis<0.8.0,>=0.4.0 in d:\\programdata\\anaconda3\\lib\\site-packages (from spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (0.7.4)\n",
      "Requirement already satisfied: jinja2 in d:\\programdata\\anaconda3\\lib\\site-packages (from spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (2.11.2)\n",
      "Requirement already satisfied: wasabi<1.1.0,>=0.8.1 in d:\\programdata\\anaconda3\\lib\\site-packages (from spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (0.8.2)\n",
      "Requirement already satisfied: cymem<2.1.0,>=2.0.2 in d:\\programdata\\anaconda3\\lib\\site-packages (from spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (2.0.5)\n",
      "Requirement already satisfied: requests<3.0.0,>=2.13.0 in d:\\programdata\\anaconda3\\lib\\site-packages (from spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (2.24.0)\n",
      "Requirement already satisfied: tqdm<5.0.0,>=4.38.0 in d:\\programdata\\anaconda3\\lib\\site-packages (from spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (4.50.2)\n",
      "Requirement already satisfied: murmurhash<1.1.0,>=0.28.0 in d:\\programdata\\anaconda3\\lib\\site-packages (from spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (1.0.5)\n",
      "Requirement already satisfied: typer<0.4.0,>=0.3.0 in d:\\programdata\\anaconda3\\lib\\site-packages (from spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (0.3.2)\n",
      "Requirement already satisfied: setuptools in d:\\programdata\\anaconda3\\lib\\site-packages (from spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (50.3.1.post20201107)\n",
      "Requirement already satisfied: srsly<3.0.0,>=2.4.0 in d:\\programdata\\anaconda3\\lib\\site-packages (from spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (2.4.0)\n",
      "Requirement already satisfied: pydantic<1.8.0,>=1.7.1 in d:\\programdata\\anaconda3\\lib\\site-packages (from spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (1.7.3)\n",
      "Requirement already satisfied: thinc<8.1.0,>=8.0.0 in d:\\programdata\\anaconda3\\lib\\site-packages (from spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (8.0.1)\n",
      "Requirement already satisfied: packaging>=20.0 in d:\\programdata\\anaconda3\\lib\\site-packages (from spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (20.4)\n",
      "Requirement already satisfied: numpy>=1.15.0 in d:\\programdata\\anaconda3\\lib\\site-packages (from spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (1.19.2)\n",
      "Requirement already satisfied: catalogue<2.1.0,>=2.0.1 in d:\\programdata\\anaconda3\\lib\\site-packages (from spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (2.0.1)\n",
      "Requirement already satisfied: spacy-legacy<3.1.0,>=3.0.0 in d:\\programdata\\anaconda3\\lib\\site-packages (from spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (3.0.1)\n",
      "Requirement already satisfied: pathy in d:\\programdata\\anaconda3\\lib\\site-packages (from spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (0.3.5)\n",
      "Requirement already satisfied: MarkupSafe>=0.23 in d:\\programdata\\anaconda3\\lib\\site-packages (from jinja2->spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (1.1.1)\n",
      "Requirement already satisfied: urllib3!=1.25.0,!=1.25.1,<1.26,>=1.21.1 in d:\\programdata\\anaconda3\\lib\\site-packages (from requests<3.0.0,>=2.13.0->spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (1.25.11)\n",
      "Requirement already satisfied: idna<3,>=2.5 in d:\\programdata\\anaconda3\\lib\\site-packages (from requests<3.0.0,>=2.13.0->spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (2.10)\n",
      "Requirement already satisfied: certifi>=2017.4.17 in d:\\programdata\\anaconda3\\lib\\site-packages (from requests<3.0.0,>=2.13.0->spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (2020.6.20)\n",
      "Requirement already satisfied: chardet<4,>=3.0.2 in d:\\programdata\\anaconda3\\lib\\site-packages (from requests<3.0.0,>=2.13.0->spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (3.0.4)\n",
      "Requirement already satisfied: click<7.2.0,>=7.1.1 in d:\\programdata\\anaconda3\\lib\\site-packages (from typer<0.4.0,>=0.3.0->spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (7.1.2)\n",
      "Requirement already satisfied: six in d:\\programdata\\anaconda3\\lib\\site-packages (from packaging>=20.0->spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (1.15.0)\n",
      "Requirement already satisfied: pyparsing>=2.0.2 in d:\\programdata\\anaconda3\\lib\\site-packages (from packaging>=20.0->spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (2.4.7)\n",
      "Requirement already satisfied: smart-open<4.0.0,>=2.2.0 in d:\\programdata\\anaconda3\\lib\\site-packages (from pathy->spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (2.2.1)\n",
      "Requirement already satisfied: boto3 in d:\\programdata\\anaconda3\\lib\\site-packages (from smart-open<4.0.0,>=2.2.0->pathy->spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (1.17.7)\n",
      "Requirement already satisfied: jmespath<1.0.0,>=0.7.1 in d:\\programdata\\anaconda3\\lib\\site-packages (from boto3->smart-open<4.0.0,>=2.2.0->pathy->spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (0.10.0)\n",
      "Requirement already satisfied: s3transfer<0.4.0,>=0.3.0 in d:\\programdata\\anaconda3\\lib\\site-packages (from boto3->smart-open<4.0.0,>=2.2.0->pathy->spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (0.3.4)\n",
      "Requirement already satisfied: botocore<1.21.0,>=1.20.7 in d:\\programdata\\anaconda3\\lib\\site-packages (from boto3->smart-open<4.0.0,>=2.2.0->pathy->spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (1.20.7)\n",
      "Requirement already satisfied: python-dateutil<3.0.0,>=2.1 in d:\\programdata\\anaconda3\\lib\\site-packages (from botocore<1.21.0,>=1.20.7->boto3->smart-open<4.0.0,>=2.2.0->pathy->spacy<3.1.0,>=3.0.0->en-core-web-md==3.0.0) (2.8.1)\n",
      "[+] Download and installation successful\n",
      "You can now load the package via spacy.load('en_core_web_md')\n",
      "Finished loading.\n"
     ]
    }
   ],
   "source": [
    "import re\n",
    "import pandas as pd\n",
    "import spacy\n",
    "import sys\n",
    "from collections import Counter\n",
    "import re\n",
    "\n",
    "\n",
    "## Run to install the language library, then comment-out\n",
    "## !{sys.executable} -m spacy download en\n",
    "!{sys.executable} -m spacy download en_core_web_md\n",
    "\n",
    "nlp = spacy.load('en_core_web_md')\n",
    "print('Finished loading.')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 2. Pre-processing\n",
    "***\n",
    "\n",
    "* Read two files exported from the database which contain the SE Glossary articles definitions and their titles.\n",
    "* In later versions, the corresponding tables will be directly exported from the database.\n",
    "* Merge by id and discard records with duplicate titles and/or definitions.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>title</th>\n",
       "      <th>definition</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Arrival of tourist at a tourist accommodation ...</td>\n",
       "      <td>See      arrival disambiguation page     fo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>Nights spent</td>\n",
       "      <td>A     night spent    or     tourism night   ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>Supply side tourism statistics</td>\n",
       "      <td>Regulation (EU) No 692/2011 of 6 July 2011 e...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>Tourist</td>\n",
       "      <td>For     tourism    statistics, a     travell...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "      <td>Asia-Europe Meeting (ASEM)</td>\n",
       "      <td>The     Asia-Europe Meeting    , abbreviated...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                                              title  \\\n",
       "0   1  Arrival of tourist at a tourist accommodation ...   \n",
       "1   3                                    Nights spent      \n",
       "2   4                  Supply side tourism statistics      \n",
       "3   5                                         Tourist      \n",
       "4   7                         Asia-Europe Meeting (ASEM)   \n",
       "\n",
       "                                          definition  \n",
       "0     See      arrival disambiguation page     fo...  \n",
       "1    A     night spent    or     tourism night   ...  \n",
       "2    Regulation (EU) No 692/2011 of 6 July 2011 e...  \n",
       "3    For     tourism    statistics, a     travell...  \n",
       "4    The     Asia-Europe Meeting    , abbreviated...  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dat1= pd.read_csv(\"ESTAT_dat_concepts_2021_04_08.csv\",sep=\";\")\n",
    "dat2= pd.read_csv(\"ESTAT_dat_link_info_2021_04_08.csv\",sep=\";\")\n",
    "Gloss_concepts = pd.merge(dat1,dat2,on=['id'])\n",
    "del(dat1,dat2)\n",
    "\n",
    "Gloss_concepts = Gloss_concepts[['id','title','definition']]\n",
    "\n",
    "Gloss_concepts = Gloss_concepts.drop_duplicates(subset=[\"definition\"])\n",
    "Gloss_concepts = Gloss_concepts.dropna(axis=0,subset=[\"definition\"])\n",
    "Gloss_concepts = Gloss_concepts.drop_duplicates(subset=[\"title\"])\n",
    "Gloss_concepts = Gloss_concepts.dropna(axis=0,subset=[\"title\"])\n",
    "\n",
    "Gloss_concepts.reset_index(drop=True, inplace=True)\n",
    "Gloss_concepts.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 3. Pre-processing (cont)\n",
    "*** \n",
    "\n",
    "Keep a backup of the original definition and do some data cleansing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>title</th>\n",
       "      <th>definition</th>\n",
       "      <th>definition_init</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Arrival of tourist at a tourist accommodation ...</td>\n",
       "      <td>See arrival disambiguation page for other mean...</td>\n",
       "      <td>See      arrival disambiguation page     fo...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>Nights spent</td>\n",
       "      <td>A night spent or tourism night overnight stay ...</td>\n",
       "      <td>A     night spent    or     tourism night   ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>Supply side tourism statistics</td>\n",
       "      <td>Regulation EU No 692 2011 of 6 July 2011 estab...</td>\n",
       "      <td>Regulation (EU) No 692/2011 of 6 July 2011 e...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>Tourist</td>\n",
       "      <td>For tourism statistics, a traveller is someone...</td>\n",
       "      <td>For     tourism    statistics, a     travell...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "      <td>Asia-Europe Meeting (ASEM)</td>\n",
       "      <td>The Asia Europe Meeting, abbreviated as ASEM, ...</td>\n",
       "      <td>The     Asia-Europe Meeting    , abbreviated...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                                              title  \\\n",
       "0   1  Arrival of tourist at a tourist accommodation ...   \n",
       "1   3                                    Nights spent      \n",
       "2   4                  Supply side tourism statistics      \n",
       "3   5                                         Tourist      \n",
       "4   7                         Asia-Europe Meeting (ASEM)   \n",
       "\n",
       "                                          definition  \\\n",
       "0  See arrival disambiguation page for other mean...   \n",
       "1  A night spent or tourism night overnight stay ...   \n",
       "2  Regulation EU No 692 2011 of 6 July 2011 estab...   \n",
       "3  For tourism statistics, a traveller is someone...   \n",
       "4  The Asia Europe Meeting, abbreviated as ASEM, ...   \n",
       "\n",
       "                                     definition_init  \n",
       "0     See      arrival disambiguation page     fo...  \n",
       "1    A     night spent    or     tourism night   ...  \n",
       "2    Regulation (EU) No 692/2011 of 6 July 2011 e...  \n",
       "3    For     tourism    statistics, a     travell...  \n",
       "4    The     Asia-Europe Meeting    , abbreviated...  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Gloss_concepts['definition_init'] = Gloss_concepts['definition']\n",
    "\n",
    "Gloss_concepts['definition'] = Gloss_concepts['definition'].apply(lambda x: re.sub(\"[^a-z\\\\.,A-Z0-9]\", \" \",x)) ## replace anything except digits,letters,comma and dot by space \n",
    "Gloss_concepts['definition'] = Gloss_concepts['definition'].apply(lambda x: re.sub(r'[a-zA-Z]+\\d+', '.', x))## letters+digits -> .\n",
    "Gloss_concepts['definition'] = Gloss_concepts['definition'].apply(lambda x: re.sub(' +', ' ',x)) ## remove more than one spaces\n",
    "Gloss_concepts['definition'] = Gloss_concepts['definition'].apply(lambda x: re.sub('^ +| +$', '',x)) ## remove start and end spaces\n",
    "Gloss_concepts['definition'] = Gloss_concepts['definition'].apply(lambda x: re.sub(' +', ' ',x)) ## remove more than one spaces\n",
    "Gloss_concepts['definition'] = Gloss_concepts['definition'].apply(lambda x: re.sub(' , ',', ',x)) ## space-comma-space -> comma-space\n",
    "\n",
    "\n",
    "Gloss_concepts.head()\n",
    "#len(Gloss_concepts.definition.values.tolist())\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 4. Apply the NER engine\n",
    "***\n",
    "\n",
    "Create columns ORG, GPE, NORP, LOCATION which will hold dictionaries with entities recognized as:\n",
    "* Organizations;\n",
    "* Countries, cities, states;\n",
    "* Nationalities or religious or political groups;\n",
    "* Non-GPE locations, mountain ranges, bodies of water, respectively. \n",
    "\n",
    "In each dictionary in a record, the key is the entity and the values are a list with the token span's *start* index position, the token span's *stop* index position and the count in the definition of the glossary article."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "i =  0  of  1284\n",
      "i =  100  of  1284\n",
      "i =  200  of  1284\n",
      "i =  300  of  1284\n",
      "i =  400  of  1284\n",
      "i =  500  of  1284\n",
      "i =  600  of  1284\n",
      "i =  700  of  1284\n",
      "i =  800  of  1284\n",
      "i =  900  of  1284\n",
      "i =  1000  of  1284\n",
      "i =  1100  of  1284\n",
      "i =  1200  of  1284\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>title</th>\n",
       "      <th>definition</th>\n",
       "      <th>definition_init</th>\n",
       "      <th>ORG</th>\n",
       "      <th>GPE</th>\n",
       "      <th>NORP</th>\n",
       "      <th>LOCATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Arrival of tourist at a tourist accommodation ...</td>\n",
       "      <td>See arrival disambiguation page for other mean...</td>\n",
       "      <td>See      arrival disambiguation page     fo...</td>\n",
       "      <td>{'EUROPEAN UNION': [[(14, 16)], 1], 'EU': [[(1...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>Nights spent</td>\n",
       "      <td>A night spent or tourism night overnight stay ...</td>\n",
       "      <td>A     night spent    or     tourism night   ...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>Supply side tourism statistics</td>\n",
       "      <td>Regulation EU No 692 2011 of 6 July 2011 estab...</td>\n",
       "      <td>Regulation (EU) No 692/2011 of 6 July 2011 e...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{'EUROPEAN': [[(22, 23)], 1]}</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>Tourist</td>\n",
       "      <td>For tourism statistics, a traveller is someone...</td>\n",
       "      <td>For     tourism    statistics, a     travell...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "      <td>Asia-Europe Meeting (ASEM)</td>\n",
       "      <td>The Asia Europe Meeting, abbreviated as ASEM, ...</td>\n",
       "      <td>The     Asia-Europe Meeting    , abbreviated...</td>\n",
       "      <td>{'ASEM': [[(7, 8), (36, 37)], 2], 'EUROPEAN UN...</td>\n",
       "      <td>{'OCEANIA': [[(27, 28)], 1], 'NORWAY': [[(49, ...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1279</th>\n",
       "      <td>2879</td>\n",
       "      <td>Statistical territory</td>\n",
       "      <td>The statistical territory of the European Unio...</td>\n",
       "      <td>The     statistical territory    of the     ...</td>\n",
       "      <td>{'EUROPEAN UNION': [[(5, 7)], 1], 'EU': [[(7, ...</td>\n",
       "      <td>{'GERMANY': [[(37, 38)], 1], 'HELIGOLAND': [[(...</td>\n",
       "      <td>{'FRENCH': [[(59, 60)], 1], 'MARTINIQUE': [[(6...</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1280</th>\n",
       "      <td>2881</td>\n",
       "      <td>Statistical indicator</td>\n",
       "      <td>A statistical indicator is the representation ...</td>\n",
       "      <td>A     statistical indicator    is the repres...</td>\n",
       "      <td>{'EUROPEAN UNION': [[(80, 82)], 1], 'EU': [[(8...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1281</th>\n",
       "      <td>2885</td>\n",
       "      <td>Tourism industries</td>\n",
       "      <td>For the article Tourism industries economic an...</td>\n",
       "      <td>For the article     Tourism industries - eco...</td>\n",
       "      <td>{'UNWTO': [[(19, 20)], 1], 'STS': [[(128, 129)...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{'EUROPEAN': [[(27, 28)], 1]}</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1282</th>\n",
       "      <td>2891</td>\n",
       "      <td>Sawnwood</td>\n",
       "      <td>Sawnwood is wood that has been produced either...</td>\n",
       "      <td>Sawnwood    is wood that has been produced ...</td>\n",
       "      <td>{'SAWNWOOD': [[(0, 1)], 1]}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1283</th>\n",
       "      <td>2896</td>\n",
       "      <td>LeaMickatest</td>\n",
       "      <td>test</td>\n",
       "      <td>test</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1284 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        id                                              title  \\\n",
       "0        1  Arrival of tourist at a tourist accommodation ...   \n",
       "1        3                                    Nights spent      \n",
       "2        4                  Supply side tourism statistics      \n",
       "3        5                                         Tourist      \n",
       "4        7                         Asia-Europe Meeting (ASEM)   \n",
       "...    ...                                                ...   \n",
       "1279  2879                              Statistical territory   \n",
       "1280  2881                              Statistical indicator   \n",
       "1281  2885                                 Tourism industries   \n",
       "1282  2891                                           Sawnwood   \n",
       "1283  2896                                       LeaMickatest   \n",
       "\n",
       "                                             definition  \\\n",
       "0     See arrival disambiguation page for other mean...   \n",
       "1     A night spent or tourism night overnight stay ...   \n",
       "2     Regulation EU No 692 2011 of 6 July 2011 estab...   \n",
       "3     For tourism statistics, a traveller is someone...   \n",
       "4     The Asia Europe Meeting, abbreviated as ASEM, ...   \n",
       "...                                                 ...   \n",
       "1279  The statistical territory of the European Unio...   \n",
       "1280  A statistical indicator is the representation ...   \n",
       "1281  For the article Tourism industries economic an...   \n",
       "1282  Sawnwood is wood that has been produced either...   \n",
       "1283                                               test   \n",
       "\n",
       "                                        definition_init  \\\n",
       "0        See      arrival disambiguation page     fo...   \n",
       "1       A     night spent    or     tourism night   ...   \n",
       "2       Regulation (EU) No 692/2011 of 6 July 2011 e...   \n",
       "3       For     tourism    statistics, a     travell...   \n",
       "4       The     Asia-Europe Meeting    , abbreviated...   \n",
       "...                                                 ...   \n",
       "1279    The     statistical territory    of the     ...   \n",
       "1280    A     statistical indicator    is the repres...   \n",
       "1281    For the article     Tourism industries - eco...   \n",
       "1282     Sawnwood    is wood that has been produced ...   \n",
       "1283                                               test   \n",
       "\n",
       "                                                    ORG  \\\n",
       "0     {'EUROPEAN UNION': [[(14, 16)], 1], 'EU': [[(1...   \n",
       "1                                                    {}   \n",
       "2                                                    {}   \n",
       "3                                                    {}   \n",
       "4     {'ASEM': [[(7, 8), (36, 37)], 2], 'EUROPEAN UN...   \n",
       "...                                                 ...   \n",
       "1279  {'EUROPEAN UNION': [[(5, 7)], 1], 'EU': [[(7, ...   \n",
       "1280  {'EUROPEAN UNION': [[(80, 82)], 1], 'EU': [[(8...   \n",
       "1281  {'UNWTO': [[(19, 20)], 1], 'STS': [[(128, 129)...   \n",
       "1282                        {'SAWNWOOD': [[(0, 1)], 1]}   \n",
       "1283                                                 {}   \n",
       "\n",
       "                                                    GPE  \\\n",
       "0                                                    {}   \n",
       "1                                                    {}   \n",
       "2                                                    {}   \n",
       "3                                                    {}   \n",
       "4     {'OCEANIA': [[(27, 28)], 1], 'NORWAY': [[(49, ...   \n",
       "...                                                 ...   \n",
       "1279  {'GERMANY': [[(37, 38)], 1], 'HELIGOLAND': [[(...   \n",
       "1280                                                 {}   \n",
       "1281                                                 {}   \n",
       "1282                                                 {}   \n",
       "1283                                                 {}   \n",
       "\n",
       "                                                   NORP LOCATION  \n",
       "0                                                    {}       {}  \n",
       "1                                                    {}       {}  \n",
       "2                         {'EUROPEAN': [[(22, 23)], 1]}       {}  \n",
       "3                                                    {}       {}  \n",
       "4                                                    {}       {}  \n",
       "...                                                 ...      ...  \n",
       "1279  {'FRENCH': [[(59, 60)], 1], 'MARTINIQUE': [[(6...       {}  \n",
       "1280                                                 {}       {}  \n",
       "1281                      {'EUROPEAN': [[(27, 28)], 1]}       {}  \n",
       "1282                                                 {}       {}  \n",
       "1283                                                 {}       {}  \n",
       "\n",
       "[1284 rows x 8 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nlp.max_length = 1500000\n",
    "\n",
    "Gloss_concepts['ORG'] = [dict() for i in range(len(Gloss_concepts))]\n",
    "Gloss_concepts['GPE'] = [dict() for i in range(len(Gloss_concepts))]\n",
    "Gloss_concepts['NORP'] = [dict() for i in range(len(Gloss_concepts))]\n",
    "Gloss_concepts['LOCATION'] = [dict() for i in range(len(Gloss_concepts))]\n",
    "\n",
    "for i in range(len(Gloss_concepts)):\n",
    "    if i % 100 == 0: print('i = ',i,' of ',len(Gloss_concepts))\n",
    "    tokens = nlp(Gloss_concepts.loc[i,'definition'])\n",
    "    entities = tokens.ents\n",
    "    for ent in entities:\n",
    "        #print(ent.text, ent.label_)\n",
    "        if ent.label_ == 'ORG':\n",
    "            if ent.text.upper() in Gloss_concepts.loc[i,'ORG'].keys():\n",
    "                Gloss_concepts.loc[i,'ORG'][ent.text.upper()][0].append((ent.start,ent.end)) \n",
    "                Gloss_concepts.loc[i,'ORG'][ent.text.upper()][1] += 1 \n",
    "            else:    \n",
    "                Gloss_concepts.loc[i,'ORG'][ent.text.upper()] = [[(ent.start,ent.end)],1]\n",
    "        \n",
    "        elif ent.label_ == 'GPE':\n",
    "            if ent.text.upper() in Gloss_concepts.loc[i,'GPE'].keys():\n",
    "                Gloss_concepts.loc[i,'GPE'][ent.text.upper()][0].append((ent.start,ent.end)) \n",
    "                Gloss_concepts.loc[i,'GPE'][ent.text.upper()][1] += 1 \n",
    "            else:    \n",
    "                Gloss_concepts.loc[i,'GPE'][ent.text.upper()] = [[(ent.start,ent.end)],1]\n",
    "                \n",
    "        elif ent.label_ == 'NORP':\n",
    "            if ent.text.upper() in Gloss_concepts.loc[i,'NORP'].keys():\n",
    "                Gloss_concepts.loc[i,'NORP'][ent.text.upper()][0].append((ent.start,ent.end)) \n",
    "                Gloss_concepts.loc[i,'NORP'][ent.text.upper()][1] += 1 \n",
    "            else:    \n",
    "                Gloss_concepts.loc[i,'NORP'][ent.text.upper()] = [[(ent.start,ent.end)],1]\n",
    "                \n",
    "        elif ent.label_ == 'LOCATION':\n",
    "            if ent.text.upper() in Gloss_concepts.loc[i,'LOCATION'].keys():\n",
    "                Gloss_concepts.loc[i,'LOCATION'][ent.text.upper()][0].append((ent.start,ent.end)) \n",
    "                Gloss_concepts.loc[i,'LOCATION'][ent.text.upper()][1] += 1 \n",
    "            else:    \n",
    "                Gloss_concepts.loc[i,'LOCATION'][ent.text.upper()] = [[(ent.start,ent.end)],1]         \n",
    "    \n",
    "Gloss_concepts\n",
    "\n",
    "#PERSON People, including fictional\n",
    "#NORP Nationalities or religious or political groups\n",
    "#FACILITY Buildings, airports, highways, bridges, etc.\n",
    "#ORGANIZATION Companies, agencies, institutions, etc.\n",
    "#GPE Countries, cities, states\n",
    "#LOCATION Non-GPE locations, mountain ranges, bodies of water\n",
    "#PRODUCT Vehicles, weapons, foods, etc. (Not services)\n",
    "#EVENT Named hurricanes, battles, wars, sports events, etc.\n",
    "#WORK OF ART Titles of books, songs, etc.\n",
    "#LAW Named documents made into laws \n",
    "#LANGUAGE Any named language\n",
    "#The following values are also annotated in a style similar to names:\n",
    "#DATE Absolute or relative dates or periods\n",
    "#TIME Times smaller than a day\n",
    "#PERCENT Percentage (including “%”)\n",
    "#MONEY Monetary values, including unit\n",
    "#QUANTITY Measurements, as of weight or distance\n",
    "#ORDINAL “first”, “second”\n",
    "#CARDINAL Numerals that do not fall under another typ\n",
    "\n",
    "Gloss_concepts.loc[50,'ORG']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 5. Gathering the most common entities: example with ORG entities\n",
    "***\n",
    "\n",
    "We can see a few errors and repetitions. These require some further cleansing steps and fine-tuning of the NER engine (not yet carried out). There are in total 947 terms identified as named entities - organizations.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total terms identified as ORG:  947\n",
      "\n",
      "100 most common:\n",
      "\n",
      "[('EU', 203), ('THE EUROPEAN UNION', 88), ('EUROPEAN UNION', 72), ('EUROSTAT', 51), ('NACE', 31), ('EC', 23), ('THE EUROPEAN COMMISSION', 20), ('ESA', 19), ('EFTA', 18), ('ICT', 17), ('REGULATION EC', 17), ('THE EUROPEAN COUNCIL', 11), ('THE EUROPEAN PARLIAMENT', 11), ('OECD', 10), ('FSS', 8), ('ICD', 8), ('STS', 8), ('EEC', 7), ('EMU', 7), ('HICP', 7), ('LFS', 7), ('REGULATION EU', 7), ('THE EUROPEAN COMMUNITY', 7), ('CO 2', 6), ('FDI', 6), ('NL', 6), ('THE EUROPEAN STATISTICAL SYSTEM', 6), ('THE UNITED NATIONS', 6), ('UAA', 6), ('BMI', 5), ('CFC', 5), ('COMMISSION', 5), ('COUNCIL', 5), ('GNI', 5), ('HRST', 5), ('PPS', 5), ('SAPM', 5), ('THE COUNCIL OF THE EUROPEAN UNION', 5), ('THE EUROPEAN CENTRAL BANK', 5), ('THE EUROPEAN PATENT OFFICE', 5), ('THE UNITED NATIONS UN', 5), ('TPP', 5), ('COUNCIL DIRECTIVE', 4), ('DIRECTIVES', 4), ('EDP', 4), ('EEA', 4), ('ESA 2010', 4), ('EU SILC', 4), ('EUROPEAN BUSINESS STATISTICS REGULATION', 4), ('EUROPEAN ENVIRONMENTAL ECONOMIC ACCOUNTS', 4), ('GFCF', 4), ('INTEGRATED FARM STATISTICS', 4), ('ISCED', 4), ('KAU', 4), ('LABOUR', 4), ('NA', 4), ('NPISH', 4), ('NUAA', 4), ('SBS', 4), ('SGM', 4), ('SNA', 4), ('STATISTICAL DATA', 4), ('THE EUROPEAN ECONOMIC AREA EEA', 4), ('THE INTERNATIONAL MONETARY FUND', 4), ('THE WORLD HEALTH ORGANIZATION', 4), ('URBAN', 4), ('AEI', 3), ('ASEAN', 3), ('CAP', 3), ('CIS', 3), ('COICOP', 3), ('COMMISSION DECISION', 3), ('COUNCIL REGULATION', 3), ('COUNCIL REGULATION EC', 3), ('CROPS', 3), ('DE', 3), ('DIGITAL', 3), ('EPEA', 3), ('EUROBASE', 3), ('EUROPEAN COUNCIL', 3), ('FADN', 3), ('FISHER', 3), ('IPC', 3), ('ISCO', 3), ('ISIC', 3), ('LASPEYRES', 3), ('LIVESTOCK', 3), ('LSU', 3), ('MFA', 3), ('NATIONAL STATISTICAL INSTITUTES', 3), ('PAASCHE', 3), ('PEFA', 3), ('REUSE', 3), ('SDR', 3), ('SES', 3), ('SITC', 3), ('SYSTEM', 3), ('THE COUNCIL OF EUROPE', 3), ('THE EUROPEAN COMMISSION S', 3), ('THE EUROPEAN HEALTH INTERVIEW SURVEY EHIS', 3)]\n"
     ]
    }
   ],
   "source": [
    "from itertools import chain\n",
    "org_list=sorted(list(chain.from_iterable(Gloss_concepts['ORG'].apply(lambda x: x.keys()))))\n",
    "org_all_freqs = sorted(Counter(org_list))\n",
    "print('Total terms identified as ORG: ',len(org_all_freqs))\n",
    "\n",
    "print('\\n100 most common:\\n')\n",
    "org_common_freqs = Counter(org_list).most_common(100)\n",
    "org_common = sorted([x[0] for x in org_common_freqs])\n",
    "print(org_common_freqs)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 6. Storing information on these most common entities per article: example with ORG entities\n",
    "***\n",
    "\n",
    "This is one way of storing the information on both all entities and counts and on the most common ones in a Pandas dataframe.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>title</th>\n",
       "      <th>definition</th>\n",
       "      <th>definition_init</th>\n",
       "      <th>ORG</th>\n",
       "      <th>GPE</th>\n",
       "      <th>NORP</th>\n",
       "      <th>LOCATION</th>\n",
       "      <th>ORG_COMMON_100</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Arrival of tourist at a tourist accommodation ...</td>\n",
       "      <td>See arrival disambiguation page for other mean...</td>\n",
       "      <td>See      arrival disambiguation page     fo...</td>\n",
       "      <td>{'EUROPEAN UNION': [[(14, 16)], 1], 'EU': [[(1...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{'EUROPEAN UNION': [[(14, 16)], 1], 'EU': [[(1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>Nights spent</td>\n",
       "      <td>A night spent or tourism night overnight stay ...</td>\n",
       "      <td>A     night spent    or     tourism night   ...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>Supply side tourism statistics</td>\n",
       "      <td>Regulation EU No 692 2011 of 6 July 2011 estab...</td>\n",
       "      <td>Regulation (EU) No 692/2011 of 6 July 2011 e...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{'EUROPEAN': [[(22, 23)], 1]}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>Tourist</td>\n",
       "      <td>For tourism statistics, a traveller is someone...</td>\n",
       "      <td>For     tourism    statistics, a     travell...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "      <td>Asia-Europe Meeting (ASEM)</td>\n",
       "      <td>The Asia Europe Meeting, abbreviated as ASEM, ...</td>\n",
       "      <td>The     Asia-Europe Meeting    , abbreviated...</td>\n",
       "      <td>{'ASEM': [[(7, 8), (36, 37)], 2], 'EUROPEAN UN...</td>\n",
       "      <td>{'OCEANIA': [[(27, 28)], 1], 'NORWAY': [[(49, ...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{'EUROPEAN UNION': [[(39, 41)], 1], 'EU': [[(4...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1279</th>\n",
       "      <td>2879</td>\n",
       "      <td>Statistical territory</td>\n",
       "      <td>The statistical territory of the European Unio...</td>\n",
       "      <td>The     statistical territory    of the     ...</td>\n",
       "      <td>{'EUROPEAN UNION': [[(5, 7)], 1], 'EU': [[(7, ...</td>\n",
       "      <td>{'GERMANY': [[(37, 38)], 1], 'HELIGOLAND': [[(...</td>\n",
       "      <td>{'FRENCH': [[(59, 60)], 1], 'MARTINIQUE': [[(6...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{'EUROPEAN UNION': [[(5, 7)], 1], 'EU': [[(7, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1280</th>\n",
       "      <td>2881</td>\n",
       "      <td>Statistical indicator</td>\n",
       "      <td>A statistical indicator is the representation ...</td>\n",
       "      <td>A     statistical indicator    is the repres...</td>\n",
       "      <td>{'EUROPEAN UNION': [[(80, 82)], 1], 'EU': [[(8...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{'EUROPEAN UNION': [[(80, 82)], 1], 'EU': [[(8...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1281</th>\n",
       "      <td>2885</td>\n",
       "      <td>Tourism industries</td>\n",
       "      <td>For the article Tourism industries economic an...</td>\n",
       "      <td>For the article     Tourism industries - eco...</td>\n",
       "      <td>{'UNWTO': [[(19, 20)], 1], 'STS': [[(128, 129)...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{'EUROPEAN': [[(27, 28)], 1]}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{'STS': [[(128, 129), (218, 219)], 2], 'SBS': ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1282</th>\n",
       "      <td>2891</td>\n",
       "      <td>Sawnwood</td>\n",
       "      <td>Sawnwood is wood that has been produced either...</td>\n",
       "      <td>Sawnwood    is wood that has been produced ...</td>\n",
       "      <td>{'SAWNWOOD': [[(0, 1)], 1]}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1283</th>\n",
       "      <td>2896</td>\n",
       "      <td>LeaMickatest</td>\n",
       "      <td>test</td>\n",
       "      <td>test</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1284 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        id                                              title  \\\n",
       "0        1  Arrival of tourist at a tourist accommodation ...   \n",
       "1        3                                    Nights spent      \n",
       "2        4                  Supply side tourism statistics      \n",
       "3        5                                         Tourist      \n",
       "4        7                         Asia-Europe Meeting (ASEM)   \n",
       "...    ...                                                ...   \n",
       "1279  2879                              Statistical territory   \n",
       "1280  2881                              Statistical indicator   \n",
       "1281  2885                                 Tourism industries   \n",
       "1282  2891                                           Sawnwood   \n",
       "1283  2896                                       LeaMickatest   \n",
       "\n",
       "                                             definition  \\\n",
       "0     See arrival disambiguation page for other mean...   \n",
       "1     A night spent or tourism night overnight stay ...   \n",
       "2     Regulation EU No 692 2011 of 6 July 2011 estab...   \n",
       "3     For tourism statistics, a traveller is someone...   \n",
       "4     The Asia Europe Meeting, abbreviated as ASEM, ...   \n",
       "...                                                 ...   \n",
       "1279  The statistical territory of the European Unio...   \n",
       "1280  A statistical indicator is the representation ...   \n",
       "1281  For the article Tourism industries economic an...   \n",
       "1282  Sawnwood is wood that has been produced either...   \n",
       "1283                                               test   \n",
       "\n",
       "                                        definition_init  \\\n",
       "0        See      arrival disambiguation page     fo...   \n",
       "1       A     night spent    or     tourism night   ...   \n",
       "2       Regulation (EU) No 692/2011 of 6 July 2011 e...   \n",
       "3       For     tourism    statistics, a     travell...   \n",
       "4       The     Asia-Europe Meeting    , abbreviated...   \n",
       "...                                                 ...   \n",
       "1279    The     statistical territory    of the     ...   \n",
       "1280    A     statistical indicator    is the repres...   \n",
       "1281    For the article     Tourism industries - eco...   \n",
       "1282     Sawnwood    is wood that has been produced ...   \n",
       "1283                                               test   \n",
       "\n",
       "                                                    ORG  \\\n",
       "0     {'EUROPEAN UNION': [[(14, 16)], 1], 'EU': [[(1...   \n",
       "1                                                    {}   \n",
       "2                                                    {}   \n",
       "3                                                    {}   \n",
       "4     {'ASEM': [[(7, 8), (36, 37)], 2], 'EUROPEAN UN...   \n",
       "...                                                 ...   \n",
       "1279  {'EUROPEAN UNION': [[(5, 7)], 1], 'EU': [[(7, ...   \n",
       "1280  {'EUROPEAN UNION': [[(80, 82)], 1], 'EU': [[(8...   \n",
       "1281  {'UNWTO': [[(19, 20)], 1], 'STS': [[(128, 129)...   \n",
       "1282                        {'SAWNWOOD': [[(0, 1)], 1]}   \n",
       "1283                                                 {}   \n",
       "\n",
       "                                                    GPE  \\\n",
       "0                                                    {}   \n",
       "1                                                    {}   \n",
       "2                                                    {}   \n",
       "3                                                    {}   \n",
       "4     {'OCEANIA': [[(27, 28)], 1], 'NORWAY': [[(49, ...   \n",
       "...                                                 ...   \n",
       "1279  {'GERMANY': [[(37, 38)], 1], 'HELIGOLAND': [[(...   \n",
       "1280                                                 {}   \n",
       "1281                                                 {}   \n",
       "1282                                                 {}   \n",
       "1283                                                 {}   \n",
       "\n",
       "                                                   NORP LOCATION  \\\n",
       "0                                                    {}       {}   \n",
       "1                                                    {}       {}   \n",
       "2                         {'EUROPEAN': [[(22, 23)], 1]}       {}   \n",
       "3                                                    {}       {}   \n",
       "4                                                    {}       {}   \n",
       "...                                                 ...      ...   \n",
       "1279  {'FRENCH': [[(59, 60)], 1], 'MARTINIQUE': [[(6...       {}   \n",
       "1280                                                 {}       {}   \n",
       "1281                      {'EUROPEAN': [[(27, 28)], 1]}       {}   \n",
       "1282                                                 {}       {}   \n",
       "1283                                                 {}       {}   \n",
       "\n",
       "                                         ORG_COMMON_100  \n",
       "0     {'EUROPEAN UNION': [[(14, 16)], 1], 'EU': [[(1...  \n",
       "1                                                    {}  \n",
       "2                                                    {}  \n",
       "3                                                    {}  \n",
       "4     {'EUROPEAN UNION': [[(39, 41)], 1], 'EU': [[(4...  \n",
       "...                                                 ...  \n",
       "1279  {'EUROPEAN UNION': [[(5, 7)], 1], 'EU': [[(7, ...  \n",
       "1280  {'EUROPEAN UNION': [[(80, 82)], 1], 'EU': [[(8...  \n",
       "1281  {'STS': [[(128, 129), (218, 219)], 2], 'SBS': ...  \n",
       "1282                                                 {}  \n",
       "1283                                                 {}  \n",
       "\n",
       "[1284 rows x 9 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Gloss_concepts['ORG_COMMON_100'] = Gloss_concepts['ORG'].apply(lambda x: {y:x[y] for y in x.keys() if y in org_common})\n",
    "Gloss_concepts\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 7. Exporting the dataframe to Excel\n",
    "***\n",
    "This is useful for the manual inspection and the design of rules for the fine-tuning of the NER engine. This output can then directly be imported in the database.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "Gloss_concepts.to_excel('Gloss_concepts.xlsx')"
   ]
  }
 ],
 "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}