Content Database/ESTAT_Explore_CDB_tables.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Explore the Content DB tables"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load libraries and connect to Virtuoso"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os \n",
    "import re\n",
    "import logging\n",
    "import sys\n",
    "import pyodbc\n",
    "import hashlib\n",
    "import pandas as pd\n",
    "from datetime import datetime\n",
    "from SPARQLWrapper import SPARQLWrapper, POST, DIGEST, GET\n",
    "from SPARQLWrapper import JSON, INSERT, DELETE\n",
    "import sparql_dataframe\n",
    "from itertools import chain"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def connect_db(DSN, DBA, UID, PWD):\n",
    "\n",
    "    connection = pyodbc.connect('DSN={};DBA={};UID={};PWD={}'.format(DSN, \n",
    "                                                                     DBA,\n",
    "                                                                     UID,\n",
    "                                                                     PWD))\n",
    "    cursor = connection.cursor()\n",
    "\n",
    "    return connection, cursor\n",
    "\n",
    "\n",
    "def connect_virtuoso(DSN, UID, PWD):\n",
    "\n",
    "    sparql = SPARQLWrapper(DSN)\n",
    "    sparql.setHTTPAuth(DIGEST)\n",
    "    sparql.setCredentials(UID, PWD)\n",
    "    sparql.setMethod(GET)\n",
    "\n",
    "    return sparql\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Replace the values with your own logins :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "user = \"dba\"\n",
    "login = \"30gFcpQzj7sPtRu5bkes\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connection to CDB \n",
    "connection, cursor = connect_db('Virtuoso All', \n",
    "                                'ESTAT', \n",
    "                                user, \n",
    "                                login)\n",
    "\n",
    "\n",
    "# Connection to the KDB \n",
    "endpoint = \"http://virtuoso-test.kapcode.fr:8890/sparql/\"\n",
    "sparql = connect_virtuoso(endpoint, \n",
    "                          user, \n",
    "                          login)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Define content selection functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def select_query(columns, table, conditions=None): \n",
    "\n",
    "    if conditions:\n",
    "\n",
    "        query = \"\"\"\n",
    "            SELECT {}\n",
    "            FROM {}\n",
    "            WHERE {}\n",
    "\n",
    "        \"\"\".format(columns, table, conditions)\n",
    "\n",
    "    else:\n",
    "\n",
    "        query = \"\"\"\n",
    "\n",
    "        SELECT {}\n",
    "        FROM {}\n",
    "        \"\"\".format(columns, table)\n",
    "\n",
    "    return query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Get all tables name from the CDB "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                     dat_article\n",
       "1           dat_article_paragraph\n",
       "2         dat_article_shared_link\n",
       "3                dat_code_dataset\n",
       "4                   dat_code_dico\n",
       "5                  dat_collection\n",
       "6         dat_collection_resource\n",
       "7                     dat_dataset\n",
       "8              dat_estat_glossary\n",
       "9     dat_estatg_measurement_unit\n",
       "10           dat_estatg_stat_unit\n",
       "11               dat_further_info\n",
       "12                   dat_glossary\n",
       "13                  dat_link_info\n",
       "14           dat_paragraph_figure\n",
       "15               dat_redirections\n",
       "16           dat_related_concepts\n",
       "17                   dat_resource\n",
       "18          dat_resource_altlabel\n",
       "19                    dat_sources\n",
       "20           dat_statistical_data\n",
       "21           mod_article_division\n",
       "22               mod_concept_type\n",
       "23                mod_dictionnary\n",
       "24                   mod_infotype\n",
       "25               mod_lexical_type\n",
       "26           mod_measurement_unit\n",
       "27             mod_ramon_category\n",
       "28       mod_resource_information\n",
       "29              mod_resource_type\n",
       "30           mod_statistical_unit\n",
       "31                     mod_status\n",
       "32          tm_articles_to_topics\n",
       "33                       tm_terms\n",
       "34                      tm_topics\n",
       "35                      tm_values\n",
       "Name: TABLE_NAME, dtype: object"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ESTAT_V1_tables_names = pd.read_sql(select_query('*',\n",
    "                                          'ESTAT.information_schema.tables', \n",
    "                                          ''), \n",
    "                             connection)\n",
    "ESTAT_V1_tables_names['TABLE_NAME']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "    \"ESTAT.V1.mod_article_division\" # Type de parties des articles \n",
    "    \"ESTAT.V1.mod_concept_type\"#type de concepts (term ou var)\n",
    "    \"ESTAT.V1.mod_dictionnary\"# code du dictionnaire \n",
    "\n",
    "    \"ESTAT.V1.mod_infotype\"# ressource type ou info \n",
    "    \"ESTAT.V1.mod_lexical_type\"# type de lexiques \n",
    "    \"ESTAT.V1.mod_measurement_unit\"# type de mesures\n",
    "    \"ESTAT.V1.mod_ramon_category\"# ramon nomenclatures \n",
    "    \"ESTAT.V1.mod_resource_information\"# resource info DEPRECATED\n",
    "    \"ESTAT.V1.mod_resource_type\"# resource type DEPRECATED\n",
    "    \"ESTAT.V1.mod_statistical_unit\"# type of statistical units estatglossary\n",
    "    \"ESTAT.V1.mod_status\"# current or deprecated (status in ressources )\n",
    "\n",
    "    #Topic related tables : \n",
    "    \"ESTAT.V1.tm_articles_to_topics\"# \n",
    "     \"ESTAT.V1.tm_terms\"#\n",
    "    \"ESTAT.V1.tm_topics\"#\n",
    "    \"ESTAT.V1.tm_values\"#\n",
    "\n",
    "    \"ESTAT.V1.dat_article\" #SA articles \n",
    "    \"ESTAT.V1.dat_article_paragraph\" #paragraphs inside articles \n",
    "    \"ESTAT.V1.dat_article_shared_link\" #links inside articles \n",
    "    \"ESTAT.V1.dat_code_dataset\" #dataset linked to code list elements \n",
    "    \"ESTAT.V1.dat_code_dico\" #tout ce qu'il y a dans le dictionnaire \n",
    "    \"ESTAT.V1.dat_collection\" #describe collection of ressources types (editorial, legal, ...)\n",
    "    \"ESTAT.V1.dat_collection_resource\" #liens collections/ressources \n",
    "    \"ESTAT.V1.dat_dataset\" #datasets labels \n",
    "    \"ESTAT.V1.dat_estat_glossary\" #Estat glossary \n",
    "    \"ESTAT.V1.dat_estatg_measurement_unit\" #measurement units for each element \n",
    "    \"ESTAT.V1.dat_estatg_stat_unit\" #statistical  units for each element \n",
    "    \"ESTAT.V1.dat_further_info\" #further links of SEG\n",
    "    \"ESTAT.V1.dat_glossary\" #SEG elements \n",
    "    \"ESTAT.V1.dat_link_info\" #urls and titles \n",
    "    \"ESTAT.V1.dat_paragraph_figure\" #figure inside articles paragraphs \n",
    "    \"ESTAT.V1.dat_redirections\" #redirections from SEG elements \n",
    "    \"ESTAT.V1.dat_related_concepts\" #related SEG concepts \n",
    "    \"ESTAT.V1.dat_resource\" #table of resources \n",
    "    \"ESTAT.V1.dat_resource_altlabel\" #other labels for the resources \n",
    "    \"ESTAT.V1.dat_sources\" #source of the SEG elements \n",
    "    \"ESTAT.V1.dat_statistical_data\" #statistical data of the SEG elements "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Load the ressource info table "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(55, 14)\n"
     ]
    }
   ],
   "source": [
    "ESTAT_V1_dat_resource = pd.read_sql(select_query('*',\n",
    "                                          ' ESTAT.V1.dat_resource', \n",
    "                                          ''), \n",
    "                             connection)\n",
    "print(ESTAT_V1_dat_resource.shape)"
   ]
  },
  {
   "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>id</th>\n",
       "      <th>label_en</th>\n",
       "      <th>label_fr</th>\n",
       "      <th>label_de</th>\n",
       "      <th>uri</th>\n",
       "      <th>date_created</th>\n",
       "      <th>date_modified</th>\n",
       "      <th>status_id</th>\n",
       "      <th>date_deprecated</th>\n",
       "      <th>definition</th>\n",
       "      <th>editorial_note</th>\n",
       "      <th>change_note</th>\n",
       "      <th>scope_note</th>\n",
       "      <th>infotype_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Other</td>\n",
       "      <td>Autre</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaT</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>Eurostat</td>\n",
       "      <td>Eurostat</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-01</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>Eurostat resource.</td>\n",
       "      <td>None</td>\n",
       "      <td>18/05/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>European Agency for Safety and Health at Work</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>29/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>European Asylum support office</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>29/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>European Central Bank</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>29/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>European Centre for the Development of vocatio...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>29/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>European Council and council of the European U...</td>\n",
       "      <td>Conseil europÃ©en et Conseil de l'Union europÃ...</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, Acronyms, Abbreviations, Syntact...</td>\n",
       "      <td>29/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>European Court of Auditors</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>29/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>European Environment Agency</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>29/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>European Environment Information and Observati...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>29/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>European Foundation for the improvement of liv...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>29/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>European Institue for Gender Equality</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>29/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>12</td>\n",
       "      <td>European Monitoring Centre for Drugs and Drug ...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>29/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>13</td>\n",
       "      <td>European Parliament</td>\n",
       "      <td>Parlement europÃ©en</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-01</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>European Parliament resource.</td>\n",
       "      <td>ToDo : Acronyms, Abbreviations, SyntacticVariants</td>\n",
       "      <td>01/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14</td>\n",
       "      <td>European Union Agency for Fundamental rights</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>29/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>15</td>\n",
       "      <td>European Union Agency for Railways</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>29/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>16</td>\n",
       "      <td>European Union</td>\n",
       "      <td>Union euopÃ©enne</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-01</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>European union resource.</td>\n",
       "      <td>ToDo : Acronyms, Abbreviations, SyntacticVariants</td>\n",
       "      <td>01/06/2021 - Creation</td>\n",
       "      <td>Information only used for EU resource which co...</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>17</td>\n",
       "      <td>European Patent Convention</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, @lang, Acronyms, Abbreviations, ...</td>\n",
       "      <td>18/05/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>18</td>\n",
       "      <td>European Investment Bank</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, @lang, Acronyms, Abbreviations, ...</td>\n",
       "      <td>18/05/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>19</td>\n",
       "      <td>European Free Trade Association</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, @lang, Acronyms, Abbreviations, ...</td>\n",
       "      <td>18/05/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>20</td>\n",
       "      <td>International Household Survey Network</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>18/05/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>21</td>\n",
       "      <td>International Input Output organization</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>18/05/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>22</td>\n",
       "      <td>International Monetary Fund</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>18/05/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>23</td>\n",
       "      <td>International Statistics Institute</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>18/05/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>24</td>\n",
       "      <td>Organisation For Economic co-operation and dev...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>18/05/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>25</td>\n",
       "      <td>Paris21</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>29/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>26</td>\n",
       "      <td>The World Bank</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>29/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>27</td>\n",
       "      <td>World Health Organization</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>29/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>28</td>\n",
       "      <td>United Nations Economic Commission for Europe</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>29/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>29</td>\n",
       "      <td>United Nations Educational Scientific and Cult...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>18/05/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>30</td>\n",
       "      <td>United Nations Development Program</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>18/05/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>31</td>\n",
       "      <td>Food and Agriculture Organization of the Unite...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>29/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>32</td>\n",
       "      <td>United Nations Framework Convention on climate...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>29/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>33</td>\n",
       "      <td>United Nations Office on Drugs and Crime</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-05-18</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>18/05/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>34</td>\n",
       "      <td>United Nations Statistics Division</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>29/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>35</td>\n",
       "      <td>Wikipedia</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...</td>\n",
       "      <td>29/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>36</td>\n",
       "      <td>Article</td>\n",
       "      <td>Article</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-05-28</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes</td>\n",
       "      <td>28/05/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>37</td>\n",
       "      <td>European Union Law</td>\n",
       "      <td>Droit de l'Union europÃ©enne</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-05-25</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes</td>\n",
       "      <td>25/05/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>38</td>\n",
       "      <td>No-use</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-22</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Label to be confirmed, Notes, prefLabel...</td>\n",
       "      <td>22/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>39</td>\n",
       "      <td>Glossary concept</td>\n",
       "      <td>Concept du glossaire</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-05-25</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes.</td>\n",
       "      <td>25/05/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>40</td>\n",
       "      <td>Glossary home page</td>\n",
       "      <td>Page d'accueil du glossaire</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-01</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, Patterns.</td>\n",
       "      <td>01/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>41</td>\n",
       "      <td>Infography</td>\n",
       "      <td>Infographie</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-25</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr</td>\n",
       "      <td>25/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>42</td>\n",
       "      <td>Legal context</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-05-28</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr</td>\n",
       "      <td>28/05/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>43</td>\n",
       "      <td>Miscellaneous</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr</td>\n",
       "      <td>29/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>44</td>\n",
       "      <td>News</td>\n",
       "      <td>ActualitÃ©</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-05-28</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes</td>\n",
       "      <td>28/05/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>45</td>\n",
       "      <td>Publication</td>\n",
       "      <td>Publication</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-05-28</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes</td>\n",
       "      <td>28/05/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>46</td>\n",
       "      <td>Statistics Database</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-22</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr</td>\n",
       "      <td>22/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>47</td>\n",
       "      <td>Statistics Reference Metadata</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr</td>\n",
       "      <td>29/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>48</td>\n",
       "      <td>Statistics Explained Data</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-05-25</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr</td>\n",
       "      <td>25/05/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>49</td>\n",
       "      <td>Statistics Table</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-22</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes, prefLabel @fr</td>\n",
       "      <td>22/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>50</td>\n",
       "      <td>Taxonomy</td>\n",
       "      <td>Taxonomie</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes</td>\n",
       "      <td>29/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>51</td>\n",
       "      <td>Terminology</td>\n",
       "      <td>Terminologie</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes</td>\n",
       "      <td>29/06/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>52</td>\n",
       "      <td>Statistical article</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>53</td>\n",
       "      <td>Background article</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-06-29</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>54</td>\n",
       "      <td>Controlled vocabulary</td>\n",
       "      <td>Vocabulaire contrÃ´lÃ©</td>\n",
       "      <td>None</td>\n",
       "      <td>https://nlp4statref/knowledge/resource/authori...</td>\n",
       "      <td>2021-08-25</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>ToDo : Notes</td>\n",
       "      <td>25/08/2021 - Creation</td>\n",
       "      <td>None</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id                                           label_en  \\\n",
       "0    0                                              Other   \n",
       "1    1                                           Eurostat   \n",
       "2    2      European Agency for Safety and Health at Work   \n",
       "3    3                     European Asylum support office   \n",
       "4    4                              European Central Bank   \n",
       "5    5  European Centre for the Development of vocatio...   \n",
       "6    6  European Council and council of the European U...   \n",
       "7    7                         European Court of Auditors   \n",
       "8    8                        European Environment Agency   \n",
       "9    9  European Environment Information and Observati...   \n",
       "10  10  European Foundation for the improvement of liv...   \n",
       "11  11              European Institue for Gender Equality   \n",
       "12  12  European Monitoring Centre for Drugs and Drug ...   \n",
       "13  13                                European Parliament   \n",
       "14  14       European Union Agency for Fundamental rights   \n",
       "15  15                 European Union Agency for Railways   \n",
       "16  16                                     European Union   \n",
       "17  17                         European Patent Convention   \n",
       "18  18                           European Investment Bank   \n",
       "19  19                    European Free Trade Association   \n",
       "20  20             International Household Survey Network   \n",
       "21  21            International Input Output organization   \n",
       "22  22                        International Monetary Fund   \n",
       "23  23                 International Statistics Institute   \n",
       "24  24  Organisation For Economic co-operation and dev...   \n",
       "25  25                                            Paris21   \n",
       "26  26                                     The World Bank   \n",
       "27  27                          World Health Organization   \n",
       "28  28      United Nations Economic Commission for Europe   \n",
       "29  29  United Nations Educational Scientific and Cult...   \n",
       "30  30                 United Nations Development Program   \n",
       "31  31  Food and Agriculture Organization of the Unite...   \n",
       "32  32  United Nations Framework Convention on climate...   \n",
       "33  33           United Nations Office on Drugs and Crime   \n",
       "34  34                 United Nations Statistics Division   \n",
       "35  35                                          Wikipedia   \n",
       "36  36                                            Article   \n",
       "37  37                                 European Union Law   \n",
       "38  38                                             No-use   \n",
       "39  39                                   Glossary concept   \n",
       "40  40                                 Glossary home page   \n",
       "41  41                                         Infography   \n",
       "42  42                                      Legal context   \n",
       "43  43                                      Miscellaneous   \n",
       "44  44                                               News   \n",
       "45  45                                        Publication   \n",
       "46  46                                Statistics Database   \n",
       "47  47                      Statistics Reference Metadata   \n",
       "48  48                          Statistics Explained Data   \n",
       "49  49                                   Statistics Table   \n",
       "50  50                                           Taxonomy   \n",
       "51  51                                        Terminology   \n",
       "52  52                                Statistical article   \n",
       "53  53                                 Background article   \n",
       "54  54                              Controlled vocabulary   \n",
       "\n",
       "                                             label_fr label_de  \\\n",
       "0                                               Autre     None   \n",
       "1                                            Eurostat     None   \n",
       "2                                                None     None   \n",
       "3                                                None     None   \n",
       "4                                                None     None   \n",
       "5                                                None     None   \n",
       "6   Conseil europÃ©en et Conseil de l'Union europÃ...     None   \n",
       "7                                                None     None   \n",
       "8                                                None     None   \n",
       "9                                                None     None   \n",
       "10                                               None     None   \n",
       "11                                               None     None   \n",
       "12                                               None     None   \n",
       "13                                Parlement europÃ©en     None   \n",
       "14                                               None     None   \n",
       "15                                               None     None   \n",
       "16                                   Union euopÃ©enne     None   \n",
       "17                                               None     None   \n",
       "18                                               None     None   \n",
       "19                                               None     None   \n",
       "20                                               None     None   \n",
       "21                                               None     None   \n",
       "22                                               None     None   \n",
       "23                                               None     None   \n",
       "24                                               None     None   \n",
       "25                                               None     None   \n",
       "26                                               None     None   \n",
       "27                                               None     None   \n",
       "28                                               None     None   \n",
       "29                                               None     None   \n",
       "30                                               None     None   \n",
       "31                                               None     None   \n",
       "32                                               None     None   \n",
       "33                                               None     None   \n",
       "34                                               None     None   \n",
       "35                                               None     None   \n",
       "36                                            Article     None   \n",
       "37                       Droit de l'Union europÃ©enne     None   \n",
       "38                                               None     None   \n",
       "39                               Concept du glossaire     None   \n",
       "40                        Page d'accueil du glossaire     None   \n",
       "41                                        Infographie     None   \n",
       "42                                               None     None   \n",
       "43                                               None     None   \n",
       "44                                         ActualitÃ©     None   \n",
       "45                                        Publication     None   \n",
       "46                                               None     None   \n",
       "47                                               None     None   \n",
       "48                                               None     None   \n",
       "49                                               None     None   \n",
       "50                                          Taxonomie     None   \n",
       "51                                       Terminologie     None   \n",
       "52                                               None     None   \n",
       "53                                               None     None   \n",
       "54                             Vocabulaire contrÃ´lÃ©     None   \n",
       "\n",
       "                                                  uri date_created  \\\n",
       "0                                                None          NaT   \n",
       "1   https://nlp4statref/knowledge/resource/authori...   2021-06-01   \n",
       "2   https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "3   https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "4   https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "5   https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "6   https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "7   https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "8   https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "9   https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "10  https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "11  https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "12  https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "13  https://nlp4statref/knowledge/resource/authori...   2021-06-01   \n",
       "14  https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "15  https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "16  https://nlp4statref/knowledge/resource/authori...   2021-06-01   \n",
       "17  https://nlp4statref/knowledge/resource/authori...   2021-05-18   \n",
       "18  https://nlp4statref/knowledge/resource/authori...   2021-05-18   \n",
       "19  https://nlp4statref/knowledge/resource/authori...   2021-05-18   \n",
       "20  https://nlp4statref/knowledge/resource/authori...   2021-05-18   \n",
       "21  https://nlp4statref/knowledge/resource/authori...   2021-05-18   \n",
       "22  https://nlp4statref/knowledge/resource/authori...   2021-05-18   \n",
       "23  https://nlp4statref/knowledge/resource/authori...   2021-05-18   \n",
       "24  https://nlp4statref/knowledge/resource/authori...   2021-05-18   \n",
       "25  https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "26  https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "27  https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "28  https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "29  https://nlp4statref/knowledge/resource/authori...   2021-05-18   \n",
       "30  https://nlp4statref/knowledge/resource/authori...   2021-05-18   \n",
       "31  https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "32  https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "33  https://nlp4statref/knowledge/resource/authori...   2021-05-18   \n",
       "34  https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "35  https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "36  https://nlp4statref/knowledge/resource/authori...   2021-05-28   \n",
       "37  https://nlp4statref/knowledge/resource/authori...   2021-05-25   \n",
       "38  https://nlp4statref/knowledge/resource/authori...   2021-06-22   \n",
       "39  https://nlp4statref/knowledge/resource/authori...   2021-05-25   \n",
       "40  https://nlp4statref/knowledge/resource/authori...   2021-06-01   \n",
       "41  https://nlp4statref/knowledge/resource/authori...   2021-06-25   \n",
       "42  https://nlp4statref/knowledge/resource/authori...   2021-05-28   \n",
       "43  https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "44  https://nlp4statref/knowledge/resource/authori...   2021-05-28   \n",
       "45  https://nlp4statref/knowledge/resource/authori...   2021-05-28   \n",
       "46  https://nlp4statref/knowledge/resource/authori...   2021-06-22   \n",
       "47  https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "48  https://nlp4statref/knowledge/resource/authori...   2021-05-25   \n",
       "49  https://nlp4statref/knowledge/resource/authori...   2021-06-22   \n",
       "50  https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "51  https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "52  https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "53  https://nlp4statref/knowledge/resource/authori...   2021-06-29   \n",
       "54  https://nlp4statref/knowledge/resource/authori...   2021-08-25   \n",
       "\n",
       "   date_modified  status_id date_deprecated                     definition  \\\n",
       "0           None        NaN            None                           None   \n",
       "1           None        1.0            None             Eurostat resource.   \n",
       "2           None        1.0            None                           None   \n",
       "3           None        1.0            None                           None   \n",
       "4           None        1.0            None                           None   \n",
       "5           None        1.0            None                           None   \n",
       "6           None        1.0            None                           None   \n",
       "7           None        1.0            None                           None   \n",
       "8           None        1.0            None                           None   \n",
       "9           None        1.0            None                           None   \n",
       "10          None        1.0            None                           None   \n",
       "11          None        1.0            None                           None   \n",
       "12          None        1.0            None                           None   \n",
       "13          None        1.0            None  European Parliament resource.   \n",
       "14          None        1.0            None                           None   \n",
       "15          None        1.0            None                           None   \n",
       "16          None        1.0            None       European union resource.   \n",
       "17          None        1.0            None                           None   \n",
       "18          None        1.0            None                           None   \n",
       "19          None        1.0            None                           None   \n",
       "20          None        1.0            None                           None   \n",
       "21          None        1.0            None                           None   \n",
       "22          None        1.0            None                           None   \n",
       "23          None        1.0            None                           None   \n",
       "24          None        1.0            None                           None   \n",
       "25          None        1.0            None                           None   \n",
       "26          None        1.0            None                           None   \n",
       "27          None        1.0            None                           None   \n",
       "28          None        1.0            None                           None   \n",
       "29          None        1.0            None                           None   \n",
       "30          None        1.0            None                           None   \n",
       "31          None        1.0            None                           None   \n",
       "32          None        1.0            None                           None   \n",
       "33          None        1.0            None                           None   \n",
       "34          None        1.0            None                           None   \n",
       "35          None        1.0            None                           None   \n",
       "36          None        1.0            None                           None   \n",
       "37          None        1.0            None                           None   \n",
       "38          None        1.0            None                           None   \n",
       "39          None        1.0            None                           None   \n",
       "40          None        1.0            None                           None   \n",
       "41          None        1.0            None                           None   \n",
       "42          None        1.0            None                           None   \n",
       "43          None        1.0            None                           None   \n",
       "44          None        1.0            None                           None   \n",
       "45          None        1.0            None                           None   \n",
       "46          None        1.0            None                           None   \n",
       "47          None        1.0            None                           None   \n",
       "48          None        1.0            None                           None   \n",
       "49          None        1.0            None                           None   \n",
       "50          None        1.0            None                           None   \n",
       "51          None        1.0            None                           None   \n",
       "52          None        1.0            None                           None   \n",
       "53          None        1.0            None                           None   \n",
       "54          None        1.0            None                           None   \n",
       "\n",
       "                                       editorial_note            change_note  \\\n",
       "0                                                None                   None   \n",
       "1                                                None  18/05/2021 - Creation   \n",
       "2   ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  29/06/2021 - Creation   \n",
       "3   ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  29/06/2021 - Creation   \n",
       "4   ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  29/06/2021 - Creation   \n",
       "5   ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  29/06/2021 - Creation   \n",
       "6   ToDo : Notes, Acronyms, Abbreviations, Syntact...  29/06/2021 - Creation   \n",
       "7   ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  29/06/2021 - Creation   \n",
       "8   ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  29/06/2021 - Creation   \n",
       "9   ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  29/06/2021 - Creation   \n",
       "10  ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  29/06/2021 - Creation   \n",
       "11  ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  29/06/2021 - Creation   \n",
       "12  ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  29/06/2021 - Creation   \n",
       "13  ToDo : Acronyms, Abbreviations, SyntacticVariants  01/06/2021 - Creation   \n",
       "14  ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  29/06/2021 - Creation   \n",
       "15  ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  29/06/2021 - Creation   \n",
       "16  ToDo : Acronyms, Abbreviations, SyntacticVariants  01/06/2021 - Creation   \n",
       "17  ToDo : Notes, @lang, Acronyms, Abbreviations, ...  18/05/2021 - Creation   \n",
       "18  ToDo : Notes, @lang, Acronyms, Abbreviations, ...  18/05/2021 - Creation   \n",
       "19  ToDo : Notes, @lang, Acronyms, Abbreviations, ...  18/05/2021 - Creation   \n",
       "20  ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  18/05/2021 - Creation   \n",
       "21  ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  18/05/2021 - Creation   \n",
       "22  ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  18/05/2021 - Creation   \n",
       "23  ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  18/05/2021 - Creation   \n",
       "24  ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  18/05/2021 - Creation   \n",
       "25  ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  29/06/2021 - Creation   \n",
       "26  ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  29/06/2021 - Creation   \n",
       "27  ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  29/06/2021 - Creation   \n",
       "28  ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  29/06/2021 - Creation   \n",
       "29  ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  18/05/2021 - Creation   \n",
       "30  ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  18/05/2021 - Creation   \n",
       "31  ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  29/06/2021 - Creation   \n",
       "32  ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  29/06/2021 - Creation   \n",
       "33  ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  18/05/2021 - Creation   \n",
       "34  ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  29/06/2021 - Creation   \n",
       "35  ToDo : Notes, prefLabel @fr, Acronyms, Abbrevi...  29/06/2021 - Creation   \n",
       "36                                       ToDo : Notes  28/05/2021 - Creation   \n",
       "37                                       ToDo : Notes  25/05/2021 - Creation   \n",
       "38  ToDo : Label to be confirmed, Notes, prefLabel...  22/06/2021 - Creation   \n",
       "39                                      ToDo : Notes.  25/05/2021 - Creation   \n",
       "40                            ToDo : Notes, Patterns.  01/06/2021 - Creation   \n",
       "41                        ToDo : Notes, prefLabel @fr  25/06/2021 - Creation   \n",
       "42                        ToDo : Notes, prefLabel @fr  28/05/2021 - Creation   \n",
       "43                        ToDo : Notes, prefLabel @fr  29/06/2021 - Creation   \n",
       "44                                       ToDo : Notes  28/05/2021 - Creation   \n",
       "45                                       ToDo : Notes  28/05/2021 - Creation   \n",
       "46                        ToDo : Notes, prefLabel @fr  22/06/2021 - Creation   \n",
       "47                        ToDo : Notes, prefLabel @fr  29/06/2021 - Creation   \n",
       "48                        ToDo : Notes, prefLabel @fr  25/05/2021 - Creation   \n",
       "49                        ToDo : Notes, prefLabel @fr  22/06/2021 - Creation   \n",
       "50                                       ToDo : Notes  29/06/2021 - Creation   \n",
       "51                                       ToDo : Notes  29/06/2021 - Creation   \n",
       "52                                               None                   None   \n",
       "53                                               None                   None   \n",
       "54                                       ToDo : Notes  25/08/2021 - Creation   \n",
       "\n",
       "                                           scope_note  infotype_id  \n",
       "0                                                None          NaN  \n",
       "1                                                None          1.0  \n",
       "2                                                None          1.0  \n",
       "3                                                None          1.0  \n",
       "4                                                None          1.0  \n",
       "5                                                None          1.0  \n",
       "6                                                None          1.0  \n",
       "7                                                None          1.0  \n",
       "8                                                None          1.0  \n",
       "9                                                None          1.0  \n",
       "10                                               None          1.0  \n",
       "11                                               None          1.0  \n",
       "12                                               None          1.0  \n",
       "13                                               None          1.0  \n",
       "14                                               None          1.0  \n",
       "15                                               None          1.0  \n",
       "16  Information only used for EU resource which co...          1.0  \n",
       "17                                               None          1.0  \n",
       "18                                               None          1.0  \n",
       "19                                               None          1.0  \n",
       "20                                               None          1.0  \n",
       "21                                               None          1.0  \n",
       "22                                               None          1.0  \n",
       "23                                               None          1.0  \n",
       "24                                               None          1.0  \n",
       "25                                               None          1.0  \n",
       "26                                               None          1.0  \n",
       "27                                               None          1.0  \n",
       "28                                               None          1.0  \n",
       "29                                               None          1.0  \n",
       "30                                               None          1.0  \n",
       "31                                               None          1.0  \n",
       "32                                               None          1.0  \n",
       "33                                               None          1.0  \n",
       "34                                               None          1.0  \n",
       "35                                               None          1.0  \n",
       "36                                               None          2.0  \n",
       "37                                               None          2.0  \n",
       "38                                               None          2.0  \n",
       "39                                               None          2.0  \n",
       "40                                               None          2.0  \n",
       "41                                               None          2.0  \n",
       "42                                               None          2.0  \n",
       "43                                               None          2.0  \n",
       "44                                               None          2.0  \n",
       "45                                               None          2.0  \n",
       "46                                               None          2.0  \n",
       "47                                               None          2.0  \n",
       "48                                               None          2.0  \n",
       "49                                               None          2.0  \n",
       "50                                               None          2.0  \n",
       "51                                               None          2.0  \n",
       "52                                               None          2.0  \n",
       "53                                               None          2.0  \n",
       "54                                               None          2.0  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ESTAT_V1_dat_resource"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Load the Link info table "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(10556, 5)\n"
     ]
    }
   ],
   "source": [
    "ESTAT_V1_dat_link_info = pd.read_sql(select_query('*',\n",
    "                                          ' ESTAT.V1.dat_link_info', \n",
    "                                          ''), \n",
    "                             connection)\n",
    "print(ESTAT_V1_dat_link_info.shape)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>url</th>\n",
       "      <th>resource_information_id</th>\n",
       "      <th>resource_type_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Accident at work</td>\n",
       "      <td>https://ec.europa.eu/eurostat/statistics-expla...</td>\n",
       "      <td>1</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Accidents at work (ESAW, 2008 onwards)</td>\n",
       "      <td>http://ec.europa.eu/eurostat/cache/metadata/en...</td>\n",
       "      <td>1</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Commission Regulation (EU) No 349/2011</td>\n",
       "      <td>http://eur-lex.europa.eu/LexUriServ/LexUriServ...</td>\n",
       "      <td>16</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>European Statistics on Accidents at Work (E...</td>\n",
       "      <td>http://ec.europa.eu/eurostat/product?code=KS-R...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Fatal accident at work</td>\n",
       "      <td>https://ec.europa.eu/eurostat/statistics-expla...</td>\n",
       "      <td>1</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                                              title  \\\n",
       "0   1                             Accident at work         \n",
       "1   2          Accidents at work (ESAW, 2008 onwards)      \n",
       "2   3          Commission Regulation (EU) No 349/2011      \n",
       "3   4     European Statistics on Accidents at Work (E...   \n",
       "4   5                          Fatal accident at work      \n",
       "\n",
       "                                                 url  resource_information_id  \\\n",
       "0  https://ec.europa.eu/eurostat/statistics-expla...                        1   \n",
       "1  http://ec.europa.eu/eurostat/cache/metadata/en...                        1   \n",
       "2  http://eur-lex.europa.eu/LexUriServ/LexUriServ...                       16   \n",
       "3  http://ec.europa.eu/eurostat/product?code=KS-R...                        1   \n",
       "4  https://ec.europa.eu/eurostat/statistics-expla...                        1   \n",
       "\n",
       "   resource_type_id  \n",
       "0                39  \n",
       "1                46  \n",
       "2                37  \n",
       "3                 0  \n",
       "4                39  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ESTAT_V1_dat_link_info.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Load Glossary related tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "ESTAT_V1_dat_glossary = pd.read_sql(select_query('*',\n",
    "                                          'ESTAT.V1.dat_glossary', \n",
    "                                          ''), \n",
    "                             connection)\n",
    "\n",
    "ESTAT_V1_dat_related_concepts = pd.read_sql(select_query('*',\n",
    "                                          \"ESTAT.V1.dat_related_concepts\", \n",
    "                                          ''), \n",
    "                                            connection)      \n",
    "\n",
    "ESTAT_V1_dat_sources = pd.read_sql(select_query('*',\n",
    "                                          \"ESTAT.V1.dat_sources\", \n",
    "                                          ''), \n",
    "                             connection)\n",
    "ESTAT_V1_dat_redirections = pd.read_sql(select_query('*',\n",
    "                                          \"ESTAT.V1.dat_redirections\", \n",
    "                                          ''), \n",
    "                             connection)\n",
    "\n",
    "ESTAT_V1_dat_further_info = pd.read_sql(select_query('*',\n",
    "                                          'ESTAT.V1.dat_further_info', \n",
    "                                          ''), \n",
    "                             connection)\n",
    "ESTAT_V1_dat_statistical_data = pd.read_sql(select_query('*',\n",
    "                                          'ESTAT.V1.dat_statistical_data', \n",
    "                                          ''), \n",
    "                             connection)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Load Statistical Articles related tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "ESTAT_V1_dat_article_shared_link = pd.read_sql(select_query('*',\n",
    "                                          'ESTAT.V1.dat_article_shared_link', \n",
    "                                          ''), \n",
    "                             connection)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "ESTAT_V1_dat_article_paragraph = pd.read_sql(select_query('*',\n",
    "                                          'ESTAT.V1.dat_article_paragraph', \n",
    "                                          ''), \n",
    "                             connection)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "ESTAT_V1_dat_article = pd.read_sql(select_query('*',\n",
    "                                          'ESTAT.V1.dat_article', \n",
    "                                          'background_article = 0'), \n",
    "                             connection)\n",
    "\n",
    "ESTAT_V1_dat_article_core_data = pd.read_sql('SELECT article_id, link_id, url, data_sources FROM ESTAT.V1.dat_article_shared_link INNER JOIN ESTAT.V1.dat_article ON ESTAT.V1.dat_article.id = article_id INNER JOIN ESTAT.V1.dat_link_info ON link_id = ESTAT.V1.dat_link_info.id WHERE background_article = 0 AND article_division_id=1',connection)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Load Statistical Background Articles related tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "ESTAT_V1_dat_background_article = pd.read_sql(select_query('*',\n",
    "                                          'ESTAT.V1.dat_article', \n",
    "                                          'background_article = 1'), \n",
    "                             connection)\n",
    "\n",
    "ESTAT_V1_dat_background_article_core_data = pd.read_sql('SELECT article_id, link_id, url, data_sources FROM ESTAT.V1.dat_article_shared_link INNER JOIN ESTAT.V1.dat_article ON ESTAT.V1.dat_article.id = article_id INNER JOIN ESTAT.V1.dat_link_info ON link_id = ESTAT.V1.dat_link_info.id WHERE background_article = 1 AND article_division_id=1',connection)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Load Eurostat Glossary related tables "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "ESTAT_V1_dat_estat_glossary = pd.read_sql(select_query('*',\n",
    "                                          'ESTAT.V1.dat_estat_glossary', \n",
    "                                          ''), \n",
    "                             connection)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Load Code List related tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "ESTAT_V1_dat_code_dicos = pd.read_sql(select_query('*',\n",
    "                                  'ESTAT.V1.dat_code_dico', \n",
    "                                  ''), \n",
    "                                    connection)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Explore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Other                            5942\n",
       "Glossary concept                 2049\n",
       "Statistical article               642\n",
       "Statistics Reference Metadata     578\n",
       "European Union Law                560\n",
       "Statistics Database               257\n",
       "Background article                250\n",
       "News                              128\n",
       "Miscellaneous                      61\n",
       "Publication                        43\n",
       "Infography                         29\n",
       "Article                             8\n",
       "Legal context                       3\n",
       "Statistics Explained Data           3\n",
       "Glossary home page                  2\n",
       "Statistics Table                    1\n",
       "Name: label_en, dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Get the count of each ressource type in the Content:\n",
    "temp = ESTAT_V1_dat_link_info.merge(ESTAT_V1_dat_resource, left_on='resource_type_id', right_on='id')\n",
    "resource_type_count = temp.label_en.value_counts()\n",
    "resource_type_count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>definition</th>\n",
       "      <th>redirection</th>\n",
       "      <th>original_title</th>\n",
       "      <th>homepage</th>\n",
       "      <th>last_update</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>An     accident at work    in the framework ...</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>2021-03-10 11:13:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>A     fatal accident at work    refers to an...</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>2019-03-06 12:08:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>A     non-fatal      accident at work     is...</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>2018-09-12 15:42:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8</td>\n",
       "      <td>Aggregate demand    is the total amount of ...</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>2017-07-28 11:15:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9</td>\n",
       "      <td>The     goods and services account    shows ...</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>2017-08-03 16:48:00</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1309</th>\n",
       "      <td>2319</td>\n",
       "      <td>Actual individual consumption    , abbrevia...</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>2017-07-28 11:21:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1310</th>\n",
       "      <td>2321</td>\n",
       "      <td>Activity rate    is the percentage of     a...</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-11-05 15:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1311</th>\n",
       "      <td>2322</td>\n",
       "      <td>The     activation policies    are policies ...</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>2018-08-24 17:02:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1312</th>\n",
       "      <td>2324</td>\n",
       "      <td>&lt;Brief user-oriented definition, one or a fe...</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>2020-08-05 16:44:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1313</th>\n",
       "      <td>10554</td>\n",
       "      <td>The Glossary contains short definitions of all...</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>2011-12-20 01:40:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1314 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         id                                         definition  redirection  \\\n",
       "0         1    An     accident at work    in the framework ...            0   \n",
       "1         5    A     fatal accident at work    refers to an...            0   \n",
       "2         6    A     non-fatal      accident at work     is...            0   \n",
       "3         8     Aggregate demand    is the total amount of ...            0   \n",
       "4         9    The     goods and services account    shows ...            0   \n",
       "...     ...                                                ...          ...   \n",
       "1309   2319     Actual individual consumption    , abbrevia...            0   \n",
       "1310   2321     Activity rate    is the percentage of     a...            0   \n",
       "1311   2322    The     activation policies    are policies ...            0   \n",
       "1312   2324    <Brief user-oriented definition, one or a fe...            0   \n",
       "1313  10554  The Glossary contains short definitions of all...            0   \n",
       "\n",
       "     original_title  homepage         last_update  \n",
       "0              None         0 2021-03-10 11:13:00  \n",
       "1              None         0 2019-03-06 12:08:00  \n",
       "2              None         0 2018-09-12 15:42:00  \n",
       "3              None         0 2017-07-28 11:15:00  \n",
       "4              None         0 2017-08-03 16:48:00  \n",
       "...             ...       ...                 ...  \n",
       "1309           None         0 2017-07-28 11:21:00  \n",
       "1310           None         0 2020-11-05 15:00:00  \n",
       "1311           None         0 2018-08-24 17:02:00  \n",
       "1312           None         0 2020-08-05 16:44:00  \n",
       "1313           None         1 2011-12-20 01:40:00  \n",
       "\n",
       "[1314 rows x 6 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ESTAT_V1_dat_glossary #print the whole table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(241367, 4)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ESTAT_V1_dat_code_dicos.shape #get the shape "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>order_id</th>\n",
       "      <th>code_id</th>\n",
       "      <th>term</th>\n",
       "      <th>ramon_cat_id</th>\n",
       "      <th>concept_type_id</th>\n",
       "      <th>definition</th>\n",
       "      <th>context</th>\n",
       "      <th>remark</th>\n",
       "      <th>date_create</th>\n",
       "      <th>date_update</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>753479</td>\n",
       "      <td>12789</td>\n",
       "      <td>(n,k) rule</td>\n",
       "      <td>33</td>\n",
       "      <td>1</td>\n",
       "      <td>A cell is regarded as confidential, if the n l...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2010-05-03</td>\n",
       "      <td>2019-05-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>753489</td>\n",
       "      <td>12799</td>\n",
       "      <td>(p,q) rule</td>\n",
       "      <td>33</td>\n",
       "      <td>1</td>\n",
       "      <td>It is assumed that out of publicly available i...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2010-05-03</td>\n",
       "      <td>2019-05-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>1744196</td>\n",
       "      <td>19247</td>\n",
       "      <td>Âµ-ARGUS</td>\n",
       "      <td>33</td>\n",
       "      <td>1</td>\n",
       "      <td>Software that creates safe micro-data files.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2017-06-15</td>\n",
       "      <td>2019-05-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>336744</td>\n",
       "      <td>5545</td>\n",
       "      <td>Abandoned wine-growing area</td>\n",
       "      <td>12</td>\n",
       "      <td>1</td>\n",
       "      <td>Total area of land under vines which is no lon...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2005-05-03</td>\n",
       "      <td>2019-05-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>1854753</td>\n",
       "      <td>20003</td>\n",
       "      <td>Abduction by a legal guardian</td>\n",
       "      <td>151</td>\n",
       "      <td>1</td>\n",
       "      <td>Abduction of a minor by a legal guardian who i...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>2017-12-05</td>\n",
       "      <td>2019-05-10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  order_id  code_id                           term  ramon_cat_id  \\\n",
       "0   1    753479    12789                     (n,k) rule            33   \n",
       "1   2    753489    12799                     (p,q) rule            33   \n",
       "2   3   1744196    19247                       Âµ-ARGUS            33   \n",
       "3   4    336744     5545    Abandoned wine-growing area            12   \n",
       "4   5   1854753    20003  Abduction by a legal guardian           151   \n",
       "\n",
       "   concept_type_id                                         definition context  \\\n",
       "0                1  A cell is regarded as confidential, if the n l...           \n",
       "1                1  It is assumed that out of publicly available i...           \n",
       "2                1       Software that creates safe micro-data files.           \n",
       "3                1  Total area of land under vines which is no lon...           \n",
       "4                1  Abduction of a minor by a legal guardian who i...           \n",
       "\n",
       "  remark date_create date_update  \n",
       "0         2010-05-03  2019-05-10  \n",
       "1         2010-05-03  2019-05-10  \n",
       "2         2017-06-15  2019-05-10  \n",
       "3         2005-05-03  2019-05-10  \n",
       "4         2017-12-05  2019-05-10  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ESTAT_V1_dat_estat_glossary.head() # get an extract"
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}