SE_NamedEntities_V3b.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Testing the Named Entities Recognition engine of Spacy with the SE articles\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": 9,
   "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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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: 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 pprint\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 the file with the scraped content from the SE articles. \n",
    "* In later versions, the corresponding tables will be directly exported from the database.\n",
    "* Discard records with duplicate titles and/or abstracts and/or contents and do some data cleansing.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>title</th>\n",
       "      <th>abstract</th>\n",
       "      <th>categories</th>\n",
       "      <th>raw content</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Absences from work - quarterly statistics</td>\n",
       "      <td>Absences from work can be classified into two...</td>\n",
       "      <td>['Employment', 'Labour market', 'Statistical a...</td>\n",
       "      <td>Absences from work sharply increase in first h...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Balance of payments statistics - quarterly data</td>\n",
       "      <td>This article presents quarterly statistics on ...</td>\n",
       "      <td>['Balance of payments', 'Statistical article']</td>\n",
       "      <td>Current account. The EU non seasonally adjuste...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Accidents and injuries statistics</td>\n",
       "      <td>This article presents an overview of  European...</td>\n",
       "      <td>['Health', 'Health status', 'Statistical artic...</td>\n",
       "      <td>Deaths from accidents, injuries and assault. I...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Accidents at work statistics</td>\n",
       "      <td>This article presents a set of main statistica...</td>\n",
       "      <td>['Accidents at work', 'Health', 'Health and sa...</td>\n",
       "      <td>Number of accidents. In 2018, there were 3.1 m...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Accidents at work - statistics by economic act...</td>\n",
       "      <td>This article presents a set of main statistic...</td>\n",
       "      <td>['Accidents at work', 'Health', 'Health and sa...</td>\n",
       "      <td>Developments over time. Non fatal accidents In...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>610</th>\n",
       "      <td>Africa-EU - international trade in goods stati...</td>\n",
       "      <td>This article provides a picture of internation...</td>\n",
       "      <td>['Non-EU countries', 'Trade in goods', 'Statis...</td>\n",
       "      <td>Africa s main trade in goods partner is the EU...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>611</th>\n",
       "      <td>Adult learning statistics</td>\n",
       "      <td>This article provides an overview of adult l...</td>\n",
       "      <td>['Education and training', 'Lifelong learning'...</td>\n",
       "      <td>Participation rate of adults in learning in th...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>612</th>\n",
       "      <td>Acquisition of citizenship statistics</td>\n",
       "      <td>This article presents recent statistics on th...</td>\n",
       "      <td>['Asylum and migration', 'Population', 'Acquis...</td>\n",
       "      <td>EU 27 Member States granted citizenship to 706...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>613</th>\n",
       "      <td>Adult learning statistics - characteristics of...</td>\n",
       "      <td>This article presents an overview of  European...</td>\n",
       "      <td>['Education and training', 'Participation in e...</td>\n",
       "      <td>Formal and non formal adult education and trai...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>614</th>\n",
       "      <td>Accommodation and food service statistics - NA...</td>\n",
       "      <td>This article presents an overview of statist...</td>\n",
       "      <td>['Services', 'Statistical article', 'Structura...</td>\n",
       "      <td>Structural profile. The accommodation and food...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>615 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 title  \\\n",
       "0            Absences from work - quarterly statistics   \n",
       "1      Balance of payments statistics - quarterly data   \n",
       "2                    Accidents and injuries statistics   \n",
       "3                         Accidents at work statistics   \n",
       "4    Accidents at work - statistics by economic act...   \n",
       "..                                                 ...   \n",
       "610  Africa-EU - international trade in goods stati...   \n",
       "611                          Adult learning statistics   \n",
       "612              Acquisition of citizenship statistics   \n",
       "613  Adult learning statistics - characteristics of...   \n",
       "614  Accommodation and food service statistics - NA...   \n",
       "\n",
       "                                              abstract  \\\n",
       "0     Absences from work can be classified into two...   \n",
       "1    This article presents quarterly statistics on ...   \n",
       "2    This article presents an overview of  European...   \n",
       "3    This article presents a set of main statistica...   \n",
       "4     This article presents a set of main statistic...   \n",
       "..                                                 ...   \n",
       "610  This article provides a picture of internation...   \n",
       "611    This article provides an overview of adult l...   \n",
       "612   This article presents recent statistics on th...   \n",
       "613  This article presents an overview of  European...   \n",
       "614    This article presents an overview of statist...   \n",
       "\n",
       "                                            categories  \\\n",
       "0    ['Employment', 'Labour market', 'Statistical a...   \n",
       "1       ['Balance of payments', 'Statistical article']   \n",
       "2    ['Health', 'Health status', 'Statistical artic...   \n",
       "3    ['Accidents at work', 'Health', 'Health and sa...   \n",
       "4    ['Accidents at work', 'Health', 'Health and sa...   \n",
       "..                                                 ...   \n",
       "610  ['Non-EU countries', 'Trade in goods', 'Statis...   \n",
       "611  ['Education and training', 'Lifelong learning'...   \n",
       "612  ['Asylum and migration', 'Population', 'Acquis...   \n",
       "613  ['Education and training', 'Participation in e...   \n",
       "614  ['Services', 'Statistical article', 'Structura...   \n",
       "\n",
       "                                           raw content  \n",
       "0    Absences from work sharply increase in first h...  \n",
       "1    Current account. The EU non seasonally adjuste...  \n",
       "2    Deaths from accidents, injuries and assault. I...  \n",
       "3    Number of accidents. In 2018, there were 3.1 m...  \n",
       "4    Developments over time. Non fatal accidents In...  \n",
       "..                                                 ...  \n",
       "610  Africa s main trade in goods partner is the EU...  \n",
       "611  Participation rate of adults in learning in th...  \n",
       "612  EU 27 Member States granted citizenship to 706...  \n",
       "613  Formal and non formal adult education and trai...  \n",
       "614  Structural profile. The accommodation and food...  \n",
       "\n",
       "[615 rows x 4 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dat = pd.read_excel('articles_5_1_15_25.xlsx')\n",
    "dat = dat[['title','abstract','categories','raw content']]\n",
    "\n",
    "dat = dat.dropna(axis=0,subset=[\"title\"])\n",
    "dat = dat.dropna(axis=0,subset=[\"abstract\"])\n",
    "dat = dat.dropna(axis=0,subset=[\"raw content\"])\n",
    "dat.reset_index(drop=True, inplace=True)\n",
    "\n",
    "dat['raw content'] = dat['raw content'].apply(lambda x: re.sub(\"[^a-z\\\\.,A-Z0-9]\", \" \",x)) ## replace anything except digits,letters,comma and dot by space \n",
    "dat['raw content'] = dat['raw content'].apply(lambda x: re.sub(r'[a-zA-Z]+\\d+', ' ', x))## letters+digits -> space\n",
    "dat['raw content'] = dat['raw content'].apply(lambda x: re.sub(' +', ' ',x)) ## remove more than one spaces\n",
    "dat['raw content'] = dat['raw content'].apply(lambda x: re.sub('^ +| +$', '',x)) ## remove start and end spaces\n",
    "dat['raw content'] = dat['raw content'].apply(lambda x: re.sub(' ,',',',x)) ## space-comma-space -> comma-space\n",
    "\n",
    "dat"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 3. 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 of occurence in the content of the SE article."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "i =  0  of  615\n",
      "i =  100  of  615\n",
      "i =  200  of  615\n",
      "i =  300  of  615\n",
      "i =  400  of  615\n",
      "i =  500  of  615\n",
      "i =  600  of  615\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>title</th>\n",
       "      <th>abstract</th>\n",
       "      <th>categories</th>\n",
       "      <th>raw content</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>Absences from work - quarterly statistics</td>\n",
       "      <td>Absences from work can be classified into two...</td>\n",
       "      <td>['Employment', 'Labour market', 'Statistical a...</td>\n",
       "      <td>Absences from work sharply increase in first h...</td>\n",
       "      <td>{'EU': [[(59, 60), (181, 182), (261, 262), (80...</td>\n",
       "      <td>{'THE MEMBER STATES': [[(1099, 1102), (1413, 1...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Balance of payments statistics - quarterly data</td>\n",
       "      <td>This article presents quarterly statistics on ...</td>\n",
       "      <td>['Balance of payments', 'Statistical article']</td>\n",
       "      <td>Current account. The EU non seasonally adjuste...</td>\n",
       "      <td>{'EU': [[(4, 5), (148, 149), (470, 471), (569,...</td>\n",
       "      <td>{'THE MEMBER STATES': [[(202, 205)], 1], 'MEMB...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Accidents and injuries statistics</td>\n",
       "      <td>This article presents an overview of  European...</td>\n",
       "      <td>['Health', 'Health status', 'Statistical artic...</td>\n",
       "      <td>Deaths from accidents, injuries and assault. I...</td>\n",
       "      <td>{'EU': [[(18, 19), (55, 56), (110, 111), (132,...</td>\n",
       "      <td>{'SLOVENIA': [[(44, 45), (531, 532), (563, 564...</td>\n",
       "      <td>{'BALTIC': [[(161, 162), (258, 259), (708, 709...</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Accidents at work statistics</td>\n",
       "      <td>This article presents a set of main statistica...</td>\n",
       "      <td>['Accidents at work', 'Health', 'Health and sa...</td>\n",
       "      <td>Number of accidents. In 2018, there were 3.1 m...</td>\n",
       "      <td>{'EU': [[(33, 34), (72, 73), (96, 97), (145, 1...</td>\n",
       "      <td>{'FINLAND': [[(374, 375), (1679, 1680)], 2], '...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Accidents at work - statistics by economic act...</td>\n",
       "      <td>This article presents a set of main statistic...</td>\n",
       "      <td>['Accidents at work', 'Health', 'Health and sa...</td>\n",
       "      <td>Developments over time. Non fatal accidents In...</td>\n",
       "      <td>{'EU': [[(31, 32), (48, 49), (119, 120), (191,...</td>\n",
       "      <td>{}</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>610</th>\n",
       "      <td>Africa-EU - international trade in goods stati...</td>\n",
       "      <td>This article provides a picture of internation...</td>\n",
       "      <td>['Non-EU countries', 'Trade in goods', 'Statis...</td>\n",
       "      <td>Africa s main trade in goods partner is the EU...</td>\n",
       "      <td>{'EU': [[(9, 10), (22, 23), (68, 69), (184, 18...</td>\n",
       "      <td>{'CHINA': [[(42, 43), (53, 54)], 2], 'NORTHERN...</td>\n",
       "      <td>{'AFRICAN': [[(38, 39), (59, 60), (437, 438), ...</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>611</th>\n",
       "      <td>Adult learning statistics</td>\n",
       "      <td>This article provides an overview of adult l...</td>\n",
       "      <td>['Education and training', 'Lifelong learning'...</td>\n",
       "      <td>Participation rate of adults in learning in th...</td>\n",
       "      <td>{'EU': [[(80, 81), (156, 157), (255, 256), (29...</td>\n",
       "      <td>{'DENMARK': [[(146, 147)], 1], 'FINLAND': [[(1...</td>\n",
       "      <td>{'EUROPEAN': [[(16, 17)], 1]}</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>612</th>\n",
       "      <td>Acquisition of citizenship statistics</td>\n",
       "      <td>This article presents recent statistics on th...</td>\n",
       "      <td>['Asylum and migration', 'Population', 'Acquis...</td>\n",
       "      <td>EU 27 Member States granted citizenship to 706...</td>\n",
       "      <td>{'EU': [[(0, 1), (23, 24), (133, 134), (282, 2...</td>\n",
       "      <td>{'GERMANY': [[(47, 48), (136, 137), (360, 361)...</td>\n",
       "      <td>{'GERMAN': [[(54, 55), (678, 679), (698, 699)]...</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>613</th>\n",
       "      <td>Adult learning statistics - characteristics of...</td>\n",
       "      <td>This article presents an overview of  European...</td>\n",
       "      <td>['Education and training', 'Participation in e...</td>\n",
       "      <td>Formal and non formal adult education and trai...</td>\n",
       "      <td>{'EU': [[(35, 36), (112, 113), (239, 240), (39...</td>\n",
       "      <td>{'NETHERLANDS': [[(218, 219), (277, 278)], 2],...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>614</th>\n",
       "      <td>Accommodation and food service statistics - NA...</td>\n",
       "      <td>This article presents an overview of statist...</td>\n",
       "      <td>['Services', 'Statistical article', 'Structura...</td>\n",
       "      <td>Structural profile. The accommodation and food...</td>\n",
       "      <td>{'EU': [[(18, 19), (101, 102), (152, 153), (27...</td>\n",
       "      <td>{'GERMANY': [[(717, 718), (842, 843), (1396, 1...</td>\n",
       "      <td>{'GREEK': [[(1746, 1747)], 1]}</td>\n",
       "      <td>{}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>615 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 title  \\\n",
       "0            Absences from work - quarterly statistics   \n",
       "1      Balance of payments statistics - quarterly data   \n",
       "2                    Accidents and injuries statistics   \n",
       "3                         Accidents at work statistics   \n",
       "4    Accidents at work - statistics by economic act...   \n",
       "..                                                 ...   \n",
       "610  Africa-EU - international trade in goods stati...   \n",
       "611                          Adult learning statistics   \n",
       "612              Acquisition of citizenship statistics   \n",
       "613  Adult learning statistics - characteristics of...   \n",
       "614  Accommodation and food service statistics - NA...   \n",
       "\n",
       "                                              abstract  \\\n",
       "0     Absences from work can be classified into two...   \n",
       "1    This article presents quarterly statistics on ...   \n",
       "2    This article presents an overview of  European...   \n",
       "3    This article presents a set of main statistica...   \n",
       "4     This article presents a set of main statistic...   \n",
       "..                                                 ...   \n",
       "610  This article provides a picture of internation...   \n",
       "611    This article provides an overview of adult l...   \n",
       "612   This article presents recent statistics on th...   \n",
       "613  This article presents an overview of  European...   \n",
       "614    This article presents an overview of statist...   \n",
       "\n",
       "                                            categories  \\\n",
       "0    ['Employment', 'Labour market', 'Statistical a...   \n",
       "1       ['Balance of payments', 'Statistical article']   \n",
       "2    ['Health', 'Health status', 'Statistical artic...   \n",
       "3    ['Accidents at work', 'Health', 'Health and sa...   \n",
       "4    ['Accidents at work', 'Health', 'Health and sa...   \n",
       "..                                                 ...   \n",
       "610  ['Non-EU countries', 'Trade in goods', 'Statis...   \n",
       "611  ['Education and training', 'Lifelong learning'...   \n",
       "612  ['Asylum and migration', 'Population', 'Acquis...   \n",
       "613  ['Education and training', 'Participation in e...   \n",
       "614  ['Services', 'Statistical article', 'Structura...   \n",
       "\n",
       "                                           raw content  \\\n",
       "0    Absences from work sharply increase in first h...   \n",
       "1    Current account. The EU non seasonally adjuste...   \n",
       "2    Deaths from accidents, injuries and assault. I...   \n",
       "3    Number of accidents. In 2018, there were 3.1 m...   \n",
       "4    Developments over time. Non fatal accidents In...   \n",
       "..                                                 ...   \n",
       "610  Africa s main trade in goods partner is the EU...   \n",
       "611  Participation rate of adults in learning in th...   \n",
       "612  EU 27 Member States granted citizenship to 706...   \n",
       "613  Formal and non formal adult education and trai...   \n",
       "614  Structural profile. The accommodation and food...   \n",
       "\n",
       "                                                   ORG  \\\n",
       "0    {'EU': [[(59, 60), (181, 182), (261, 262), (80...   \n",
       "1    {'EU': [[(4, 5), (148, 149), (470, 471), (569,...   \n",
       "2    {'EU': [[(18, 19), (55, 56), (110, 111), (132,...   \n",
       "3    {'EU': [[(33, 34), (72, 73), (96, 97), (145, 1...   \n",
       "4    {'EU': [[(31, 32), (48, 49), (119, 120), (191,...   \n",
       "..                                                 ...   \n",
       "610  {'EU': [[(9, 10), (22, 23), (68, 69), (184, 18...   \n",
       "611  {'EU': [[(80, 81), (156, 157), (255, 256), (29...   \n",
       "612  {'EU': [[(0, 1), (23, 24), (133, 134), (282, 2...   \n",
       "613  {'EU': [[(35, 36), (112, 113), (239, 240), (39...   \n",
       "614  {'EU': [[(18, 19), (101, 102), (152, 153), (27...   \n",
       "\n",
       "                                                   GPE  \\\n",
       "0    {'THE MEMBER STATES': [[(1099, 1102), (1413, 1...   \n",
       "1    {'THE MEMBER STATES': [[(202, 205)], 1], 'MEMB...   \n",
       "2    {'SLOVENIA': [[(44, 45), (531, 532), (563, 564...   \n",
       "3    {'FINLAND': [[(374, 375), (1679, 1680)], 2], '...   \n",
       "4                                                   {}   \n",
       "..                                                 ...   \n",
       "610  {'CHINA': [[(42, 43), (53, 54)], 2], 'NORTHERN...   \n",
       "611  {'DENMARK': [[(146, 147)], 1], 'FINLAND': [[(1...   \n",
       "612  {'GERMANY': [[(47, 48), (136, 137), (360, 361)...   \n",
       "613  {'NETHERLANDS': [[(218, 219), (277, 278)], 2],...   \n",
       "614  {'GERMANY': [[(717, 718), (842, 843), (1396, 1...   \n",
       "\n",
       "                                                  NORP LOCATION  \n",
       "0                                                   {}       {}  \n",
       "1                                                   {}       {}  \n",
       "2    {'BALTIC': [[(161, 162), (258, 259), (708, 709...       {}  \n",
       "3                                                   {}       {}  \n",
       "4                                                   {}       {}  \n",
       "..                                                 ...      ...  \n",
       "610  {'AFRICAN': [[(38, 39), (59, 60), (437, 438), ...       {}  \n",
       "611                      {'EUROPEAN': [[(16, 17)], 1]}       {}  \n",
       "612  {'GERMAN': [[(54, 55), (678, 679), (698, 699)]...       {}  \n",
       "613                                                 {}       {}  \n",
       "614                     {'GREEK': [[(1746, 1747)], 1]}       {}  \n",
       "\n",
       "[615 rows x 8 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nlp.max_length = 1500000\n",
    "\n",
    "dat['ORG'] = [dict() for i in range(len(dat))]\n",
    "dat['GPE'] = [dict() for i in range(len(dat))]\n",
    "dat['NORP'] = [dict() for i in range(len(dat))]\n",
    "dat['LOCATION'] = [dict() for i in range(len(dat))]\n",
    "\n",
    "for i in range(len(dat)):\n",
    "    if i % 100 == 0: print('i = ',i,' of ',len(dat))\n",
    "    tokens = nlp(dat.loc[i,'raw content'])\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 dat.loc[i,'ORG'].keys():\n",
    "                dat.loc[i,'ORG'][ent.text.upper()][0].append((ent.start,ent.end)) \n",
    "                dat.loc[i,'ORG'][ent.text.upper()][1] += 1 \n",
    "            else:    \n",
    "                dat.loc[i,'ORG'][ent.text.upper()] = [[(ent.start,ent.end)],1]\n",
    "        \n",
    "        elif ent.label_ == 'GPE':\n",
    "            if ent.text.upper() in dat.loc[i,'GPE'].keys():\n",
    "                dat.loc[i,'GPE'][ent.text.upper()][0].append((ent.start,ent.end)) \n",
    "                dat.loc[i,'GPE'][ent.text.upper()][1] += 1 \n",
    "            else:    \n",
    "                dat.loc[i,'GPE'][ent.text.upper()] = [[(ent.start,ent.end)],1]\n",
    "                \n",
    "        elif ent.label_ == 'NORP':\n",
    "            if ent.text.upper() in dat.loc[i,'NORP'].keys():\n",
    "                dat.loc[i,'NORP'][ent.text.upper()][0].append((ent.start,ent.end)) \n",
    "                dat.loc[i,'NORP'][ent.text.upper()][1] += 1 \n",
    "            else:    \n",
    "                dat.loc[i,'NORP'][ent.text.upper()] = [[(ent.start,ent.end)],1]\n",
    "                \n",
    "        elif ent.label_ == 'LOCATION':\n",
    "            if ent.text.upper() in dat.loc[i,'LOCATION'].keys():\n",
    "                dat.loc[i,'LOCATION'][ent.text.upper()][0].append((ent.start,ent.end)) \n",
    "                dat.loc[i,'LOCATION'][ent.text.upper()][1] += 1 \n",
    "            else:    \n",
    "                dat.loc[i,'LOCATION'][ent.text.upper()] = [[(ent.start,ent.end)],1]         \n",
    "    \n",
    "dat\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"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 4. 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 1240 terms identified as named entities - organizations.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total terms identified as ORG:  1240\n",
      "\n",
      "100 most common:\n",
      "\n",
      "['AAGR', 'ALGECIRAS', 'AROPE', 'ASEAN', 'ASEM', 'ATTIKI', 'BAYERN', 'BMI', 'CO 2', 'COFOG', 'COICOP', 'COMMISSION', 'CORSE', 'COVID 19', 'CZECHIA', 'CZECHIA EUR', 'DATA', 'DE BRUXELLES CAPITALE', 'DMC', 'EA', 'EA 19', 'EASTERN', 'EC', 'EEA', 'EFTA', 'EHIS', 'ENP EAST', 'ENP SOUTH', 'ESA', 'EU', 'EU 27', 'EU 28', 'EU SILC', 'EUR', 'EUROBASE', 'EUROPEAN UNION', 'EUROSTAT', 'FDI', 'FLEVOLAND', 'FOOD', 'FTE', 'GHG', 'GNI', 'GROUP', 'GVA', 'HBS', 'HEALTHCARE', 'HICP', 'HOUSEHOLDS', 'ICD', 'ICT', 'INSTAGRAM', 'INTRA EU', 'IRELAND EUR', 'ISCED', 'ISCO', 'LFS', 'LPG', 'MACHINERY', 'MAYOTTE', 'MEMBER STATE', 'METROPOLITANA DE LISBOA', 'NACE', 'NATURA', 'NEET', 'NESA', 'NPISH', 'OECD', 'PLI', 'PPS', 'PRINCIPADO DE ASTURIAS', 'PROV', 'REGI N DE MURCIA', 'REGULATION EC', 'SDG', 'SEVEREN', 'SITC', 'SOSTIN', 'STATE', 'STS', 'THE COMUNIDAD DE MADRID', 'THE EU LABOUR FORCE SURVEY', 'THE EU STATISTICS ON INCOME AND LIVING CONDITIONS EU SILC', 'THE EUROPEAN COMMISSION', 'THE EUROPEAN COMMISSION S', 'THE EUROPEAN ENVIRONMENT AGENCY EEA', 'THE EUROPEAN GREEN DEAL', 'THE EUROPEAN PARLIAMENT', 'THE EUROPEAN UNION', 'THE INTERNATIONAL LABOUR ORGANISATION ILO', 'THE LE DE FRANCE', 'THE MEMBER STATE', 'THE RUSSIAN FEDERATION', 'THE UNITED ARAB EMIRATES', 'THE UNITED NATIONS', 'THE UNITED NATIONS UN', 'THE WORLD HEALTH ORGANISATION', 'THE WORLD TRADE ORGANISATION WTO', 'UAA', 'UN']\n",
      "[('EU', 597), ('CZECHIA', 214), ('EUROSTAT', 86), ('EFTA', 66), ('EUR', 55), ('THE EUROPEAN UNION', 52), ('NACE', 45), ('PPS', 35), ('EA', 26), ('THE EUROPEAN COMMISSION', 24), ('ISCED', 23), ('DATA', 21), ('OECD', 21), ('SITC', 21), ('SDG', 19), ('EUROPEAN UNION', 17), ('MEMBER STATE', 17), ('STATE', 17), ('EHIS', 15), ('FDI', 15), ('CO 2', 14), ('ENP EAST', 13), ('EU 27', 13), ('UAA', 13), ('ASEAN', 12), ('EEA', 12), ('GHG', 12), ('ICT', 12), ('LFS', 11), ('DE BRUXELLES CAPITALE', 10), ('ENP SOUTH', 10), ('EU SILC', 10), ('PROV', 10), ('THE EUROPEAN ENVIRONMENT AGENCY EEA', 10), ('THE LE DE FRANCE', 10), ('IRELAND EUR', 9), ('NEET', 9), ('THE EU STATISTICS ON INCOME AND LIVING CONDITIONS EU SILC', 9), ('ATTIKI', 8), ('EC', 8), ('THE UNITED ARAB EMIRATES', 8), ('UN', 8), ('GNI', 7), ('ASEM', 6), ('COFOG', 6), ('COICOP', 6), ('EASTERN', 6), ('ESA', 6), ('HEALTHCARE', 6), ('NATURA', 6), ('NESA', 6), ('NPISH', 6), ('REGULATION EC', 6), ('STS', 6), ('THE RUSSIAN FEDERATION', 6), ('THE UNITED NATIONS', 6), ('AROPE', 5), ('BAYERN', 5), ('COMMISSION', 5), ('COVID 19', 5), ('DMC', 5), ('FLEVOLAND', 5), ('FTE', 5), ('GROUP', 5), ('HICP', 5), ('HOUSEHOLDS', 5), ('ICD', 5), ('INTRA EU', 5), ('MACHINERY', 5), ('PRINCIPADO DE ASTURIAS', 5), ('THE EU LABOUR FORCE SURVEY', 5), ('THE EUROPEAN GREEN DEAL', 5), ('THE MEMBER STATE', 5), ('THE WORLD HEALTH ORGANISATION', 5), ('AAGR', 4), ('ALGECIRAS', 4), ('BMI', 4), ('CORSE', 4), ('CZECHIA EUR', 4), ('EA 19', 4), ('EU 28', 4), ('EUROBASE', 4), ('FOOD', 4), ('GVA', 4), ('HBS', 4), ('INSTAGRAM', 4), ('ISCO', 4), ('LPG', 4), ('MAYOTTE', 4), ('METROPOLITANA DE LISBOA', 4), ('PLI', 4), ('REGI N DE MURCIA', 4), ('SEVEREN', 4), ('SOSTIN', 4), ('THE COMUNIDAD DE MADRID', 4), ('THE EUROPEAN COMMISSION S', 4), ('THE EUROPEAN PARLIAMENT', 4), ('THE INTERNATIONAL LABOUR ORGANISATION ILO', 4), ('THE UNITED NATIONS UN', 4), ('THE WORLD TRADE ORGANISATION WTO', 4)]\n"
     ]
    }
   ],
   "source": [
    "from itertools import chain\n",
    "org_list=sorted(list(chain.from_iterable(dat['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",
    "#import pprint\n",
    "#pp = pprint.PrettyPrinter(indent=4)\n",
    "print(org_common_freqs)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 5. 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": 62,
   "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>title</th>\n",
       "      <th>abstract</th>\n",
       "      <th>categories</th>\n",
       "      <th>raw content</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>Absences from work - quarterly statistics</td>\n",
       "      <td>Absences from work can be classified into two...</td>\n",
       "      <td>['Employment', 'Labour market', 'Statistical a...</td>\n",
       "      <td>Absences from work sharply increase in first h...</td>\n",
       "      <td>{'EU': [[(59, 60), (181, 182), (261, 262), (80...</td>\n",
       "      <td>{'THE MEMBER STATES': [[(1099, 1102), (1413, 1...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{'EU': [[(59, 60), (181, 182), (261, 262), (80...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Balance of payments statistics - quarterly data</td>\n",
       "      <td>This article presents quarterly statistics on ...</td>\n",
       "      <td>['Balance of payments', 'Statistical article']</td>\n",
       "      <td>Current account. The EU non seasonally adjuste...</td>\n",
       "      <td>{'EU': [[(4, 5), (148, 149), (470, 471), (569,...</td>\n",
       "      <td>{'THE MEMBER STATES': [[(202, 205)], 1], 'MEMB...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{'EU': [[(4, 5), (148, 149), (470, 471), (569,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Accidents and injuries statistics</td>\n",
       "      <td>This article presents an overview of  European...</td>\n",
       "      <td>['Health', 'Health status', 'Statistical artic...</td>\n",
       "      <td>Deaths from accidents, injuries and assault. I...</td>\n",
       "      <td>{'EU': [[(18, 19), (55, 56), (110, 111), (132,...</td>\n",
       "      <td>{'SLOVENIA': [[(44, 45), (531, 532), (563, 564...</td>\n",
       "      <td>{'BALTIC': [[(161, 162), (258, 259), (708, 709...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{'EU': [[(18, 19), (55, 56), (110, 111), (132,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Accidents at work statistics</td>\n",
       "      <td>This article presents a set of main statistica...</td>\n",
       "      <td>['Accidents at work', 'Health', 'Health and sa...</td>\n",
       "      <td>Number of accidents. In 2018, there were 3.1 m...</td>\n",
       "      <td>{'EU': [[(33, 34), (72, 73), (96, 97), (145, 1...</td>\n",
       "      <td>{'FINLAND': [[(374, 375), (1679, 1680)], 2], '...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{'EU': [[(33, 34), (72, 73), (96, 97), (145, 1...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Accidents at work - statistics by economic act...</td>\n",
       "      <td>This article presents a set of main statistic...</td>\n",
       "      <td>['Accidents at work', 'Health', 'Health and sa...</td>\n",
       "      <td>Developments over time. Non fatal accidents In...</td>\n",
       "      <td>{'EU': [[(31, 32), (48, 49), (119, 120), (191,...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{'EU': [[(31, 32), (48, 49), (119, 120), (191,...</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>610</th>\n",
       "      <td>Africa-EU - international trade in goods stati...</td>\n",
       "      <td>This article provides a picture of internation...</td>\n",
       "      <td>['Non-EU countries', 'Trade in goods', 'Statis...</td>\n",
       "      <td>Africa s main trade in goods partner is the EU...</td>\n",
       "      <td>{'EU': [[(9, 10), (22, 23), (68, 69), (184, 18...</td>\n",
       "      <td>{'CHINA': [[(42, 43), (53, 54)], 2], 'NORTHERN...</td>\n",
       "      <td>{'AFRICAN': [[(38, 39), (59, 60), (437, 438), ...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{'EU': [[(9, 10), (22, 23), (68, 69), (184, 18...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>611</th>\n",
       "      <td>Adult learning statistics</td>\n",
       "      <td>This article provides an overview of adult l...</td>\n",
       "      <td>['Education and training', 'Lifelong learning'...</td>\n",
       "      <td>Participation rate of adults in learning in th...</td>\n",
       "      <td>{'EU': [[(80, 81), (156, 157), (255, 256), (29...</td>\n",
       "      <td>{'DENMARK': [[(146, 147)], 1], 'FINLAND': [[(1...</td>\n",
       "      <td>{'EUROPEAN': [[(16, 17)], 1]}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{'EU': [[(80, 81), (156, 157), (255, 256), (29...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>612</th>\n",
       "      <td>Acquisition of citizenship statistics</td>\n",
       "      <td>This article presents recent statistics on th...</td>\n",
       "      <td>['Asylum and migration', 'Population', 'Acquis...</td>\n",
       "      <td>EU 27 Member States granted citizenship to 706...</td>\n",
       "      <td>{'EU': [[(0, 1), (23, 24), (133, 134), (282, 2...</td>\n",
       "      <td>{'GERMANY': [[(47, 48), (136, 137), (360, 361)...</td>\n",
       "      <td>{'GERMAN': [[(54, 55), (678, 679), (698, 699)]...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{'EU': [[(0, 1), (23, 24), (133, 134), (282, 2...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>613</th>\n",
       "      <td>Adult learning statistics - characteristics of...</td>\n",
       "      <td>This article presents an overview of  European...</td>\n",
       "      <td>['Education and training', 'Participation in e...</td>\n",
       "      <td>Formal and non formal adult education and trai...</td>\n",
       "      <td>{'EU': [[(35, 36), (112, 113), (239, 240), (39...</td>\n",
       "      <td>{'NETHERLANDS': [[(218, 219), (277, 278)], 2],...</td>\n",
       "      <td>{}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{'EU': [[(35, 36), (112, 113), (239, 240), (39...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>614</th>\n",
       "      <td>Accommodation and food service statistics - NA...</td>\n",
       "      <td>This article presents an overview of statist...</td>\n",
       "      <td>['Services', 'Statistical article', 'Structura...</td>\n",
       "      <td>Structural profile. The accommodation and food...</td>\n",
       "      <td>{'EU': [[(18, 19), (101, 102), (152, 153), (27...</td>\n",
       "      <td>{'GERMANY': [[(717, 718), (842, 843), (1396, 1...</td>\n",
       "      <td>{'GREEK': [[(1746, 1747)], 1]}</td>\n",
       "      <td>{}</td>\n",
       "      <td>{'EU': [[(18, 19), (101, 102), (152, 153), (27...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>615 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 title  \\\n",
       "0            Absences from work - quarterly statistics   \n",
       "1      Balance of payments statistics - quarterly data   \n",
       "2                    Accidents and injuries statistics   \n",
       "3                         Accidents at work statistics   \n",
       "4    Accidents at work - statistics by economic act...   \n",
       "..                                                 ...   \n",
       "610  Africa-EU - international trade in goods stati...   \n",
       "611                          Adult learning statistics   \n",
       "612              Acquisition of citizenship statistics   \n",
       "613  Adult learning statistics - characteristics of...   \n",
       "614  Accommodation and food service statistics - NA...   \n",
       "\n",
       "                                              abstract  \\\n",
       "0     Absences from work can be classified into two...   \n",
       "1    This article presents quarterly statistics on ...   \n",
       "2    This article presents an overview of  European...   \n",
       "3    This article presents a set of main statistica...   \n",
       "4     This article presents a set of main statistic...   \n",
       "..                                                 ...   \n",
       "610  This article provides a picture of internation...   \n",
       "611    This article provides an overview of adult l...   \n",
       "612   This article presents recent statistics on th...   \n",
       "613  This article presents an overview of  European...   \n",
       "614    This article presents an overview of statist...   \n",
       "\n",
       "                                            categories  \\\n",
       "0    ['Employment', 'Labour market', 'Statistical a...   \n",
       "1       ['Balance of payments', 'Statistical article']   \n",
       "2    ['Health', 'Health status', 'Statistical artic...   \n",
       "3    ['Accidents at work', 'Health', 'Health and sa...   \n",
       "4    ['Accidents at work', 'Health', 'Health and sa...   \n",
       "..                                                 ...   \n",
       "610  ['Non-EU countries', 'Trade in goods', 'Statis...   \n",
       "611  ['Education and training', 'Lifelong learning'...   \n",
       "612  ['Asylum and migration', 'Population', 'Acquis...   \n",
       "613  ['Education and training', 'Participation in e...   \n",
       "614  ['Services', 'Statistical article', 'Structura...   \n",
       "\n",
       "                                           raw content  \\\n",
       "0    Absences from work sharply increase in first h...   \n",
       "1    Current account. The EU non seasonally adjuste...   \n",
       "2    Deaths from accidents, injuries and assault. I...   \n",
       "3    Number of accidents. In 2018, there were 3.1 m...   \n",
       "4    Developments over time. Non fatal accidents In...   \n",
       "..                                                 ...   \n",
       "610  Africa s main trade in goods partner is the EU...   \n",
       "611  Participation rate of adults in learning in th...   \n",
       "612  EU 27 Member States granted citizenship to 706...   \n",
       "613  Formal and non formal adult education and trai...   \n",
       "614  Structural profile. The accommodation and food...   \n",
       "\n",
       "                                                   ORG  \\\n",
       "0    {'EU': [[(59, 60), (181, 182), (261, 262), (80...   \n",
       "1    {'EU': [[(4, 5), (148, 149), (470, 471), (569,...   \n",
       "2    {'EU': [[(18, 19), (55, 56), (110, 111), (132,...   \n",
       "3    {'EU': [[(33, 34), (72, 73), (96, 97), (145, 1...   \n",
       "4    {'EU': [[(31, 32), (48, 49), (119, 120), (191,...   \n",
       "..                                                 ...   \n",
       "610  {'EU': [[(9, 10), (22, 23), (68, 69), (184, 18...   \n",
       "611  {'EU': [[(80, 81), (156, 157), (255, 256), (29...   \n",
       "612  {'EU': [[(0, 1), (23, 24), (133, 134), (282, 2...   \n",
       "613  {'EU': [[(35, 36), (112, 113), (239, 240), (39...   \n",
       "614  {'EU': [[(18, 19), (101, 102), (152, 153), (27...   \n",
       "\n",
       "                                                   GPE  \\\n",
       "0    {'THE MEMBER STATES': [[(1099, 1102), (1413, 1...   \n",
       "1    {'THE MEMBER STATES': [[(202, 205)], 1], 'MEMB...   \n",
       "2    {'SLOVENIA': [[(44, 45), (531, 532), (563, 564...   \n",
       "3    {'FINLAND': [[(374, 375), (1679, 1680)], 2], '...   \n",
       "4                                                   {}   \n",
       "..                                                 ...   \n",
       "610  {'CHINA': [[(42, 43), (53, 54)], 2], 'NORTHERN...   \n",
       "611  {'DENMARK': [[(146, 147)], 1], 'FINLAND': [[(1...   \n",
       "612  {'GERMANY': [[(47, 48), (136, 137), (360, 361)...   \n",
       "613  {'NETHERLANDS': [[(218, 219), (277, 278)], 2],...   \n",
       "614  {'GERMANY': [[(717, 718), (842, 843), (1396, 1...   \n",
       "\n",
       "                                                  NORP LOCATION  \\\n",
       "0                                                   {}       {}   \n",
       "1                                                   {}       {}   \n",
       "2    {'BALTIC': [[(161, 162), (258, 259), (708, 709...       {}   \n",
       "3                                                   {}       {}   \n",
       "4                                                   {}       {}   \n",
       "..                                                 ...      ...   \n",
       "610  {'AFRICAN': [[(38, 39), (59, 60), (437, 438), ...       {}   \n",
       "611                      {'EUROPEAN': [[(16, 17)], 1]}       {}   \n",
       "612  {'GERMAN': [[(54, 55), (678, 679), (698, 699)]...       {}   \n",
       "613                                                 {}       {}   \n",
       "614                     {'GREEK': [[(1746, 1747)], 1]}       {}   \n",
       "\n",
       "                                        ORG_COMMON_100  \n",
       "0    {'EU': [[(59, 60), (181, 182), (261, 262), (80...  \n",
       "1    {'EU': [[(4, 5), (148, 149), (470, 471), (569,...  \n",
       "2    {'EU': [[(18, 19), (55, 56), (110, 111), (132,...  \n",
       "3    {'EU': [[(33, 34), (72, 73), (96, 97), (145, 1...  \n",
       "4    {'EU': [[(31, 32), (48, 49), (119, 120), (191,...  \n",
       "..                                                 ...  \n",
       "610  {'EU': [[(9, 10), (22, 23), (68, 69), (184, 18...  \n",
       "611  {'EU': [[(80, 81), (156, 157), (255, 256), (29...  \n",
       "612  {'EU': [[(0, 1), (23, 24), (133, 134), (282, 2...  \n",
       "613  {'EU': [[(35, 36), (112, 113), (239, 240), (39...  \n",
       "614  {'EU': [[(18, 19), (101, 102), (152, 153), (27...  \n",
       "\n",
       "[615 rows x 9 columns]"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dat['ORG_COMMON_100'] = dat['ORG'].apply(lambda x: {y:x[y] for y in x.keys() if y in org_common})\n",
    "dat"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Step 6. Exporting the dataframe to Excel\n",
    "***\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": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "dat.to_excel('SE_NERs.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
}