enrich_piaac2esco.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Enriched PIAAC2ESCO \n",
    "We enrich the existing validated mapping by introducing alernative labels and hidden labels as synonyms of the skill that is matched.\n",
    "\n",
    "## To do:\n",
    "- add other ESCO Skills that are complement to those matched and enriched"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Libraries\n",
    "import zipfile\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import ast"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Import ESCO and prepare the dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "with zipfile.ZipFile('utils/ESCO dataset - v1.0.3 - classification - en - csv.zip') as z:\n",
    "   df=pd.read_csv(z.open('skills_en.csv'))\n",
    "\n",
    "# Create a newcolumn to use hidden labels as preferred labels. \n",
    "df['newcol']=df['preferredLabel']+'\\n'+df['altLabels']+'\\n'+df['hiddenLabels'].astype(str)\n",
    "df['newcol'].replace({'\\nnan':''},regex=True, inplace=True)\n",
    "# Newcol: From string to list\n",
    "df['newcol']=df['newcol'].str.split(pat='\\n') \n",
    "\n",
    "## alternative and hidden lables: From string to list\n",
    "# df['altLabels']=df['altLabels'].str.split(pat='\\n')\n",
    "# df['hiddenLabels']=df['hiddenLabels'].str.split(pat='\\n')\n",
    "\n",
    "# Select \n",
    "df_morelabels=df[['preferredLabel','newcol','conceptUri']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # TEST ESCOskill use word processing software\n",
    "# df_morelabels[df_morelabels['preferredLabel']=='use word processing software'].explode('newcol')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Import the PIAAC-ESCO mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_piaac2esco=pd.read_excel('utils/ESCO_PIAAC_results_mapped_v2.xlsx',usecols=['Isco_Level_2', 'preferredLabel', 'Question Id', 'Question Description','ESCO skill', 'others_skill_rca']).rename(columns={'preferredLabel':'Isco_Level_2_preferredLabel','Question Id':'PIAAC_QuestionId','Question Description':'PIAAC_QuestionDescription','ESCO skill':'ESCOskill','others_skill_rca':'ESCOskill_others'})\n",
    "# Convert lists from string to list dtypes\n",
    "df_piaac2esco.loc[:,['ESCOskill','ESCOskill_others']]=df_piaac2esco.loc[:,['ESCOskill','ESCOskill_others']].applymap(lambda x : ast.literal_eval(x))\n",
    "# Explode the dataset\n",
    "df_piaac2esco_xlpd=df_piaac2esco.explode('ESCOskill')\n",
    "df_piaac2esco_xlpd=df_piaac2esco_xlpd[['PIAAC_QuestionId','PIAAC_QuestionDescription','ESCOskill']].drop_duplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Merge the piaac2esco mapping and the enriched set of labels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_morelabels_xpld=df_morelabels.merge(df_piaac2esco_xlpd,left_on='preferredLabel', right_on='ESCOskill', how='inner').iloc[:,1:-1].rename(columns={'newcol':'ESCOskill'}).explode('ESCOskill')\n",
    "\n",
    "# concat in final dataset\n",
    "df_final=pd.concat([df_piaac2esco_xlpd, df_morelabels_xpld])\n",
    "\n",
    "# Select and polish\n",
    "df_final=df_final.sort_values(['PIAAC_QuestionId','ESCOskill']).loc[:,['PIAAC_QuestionId','PIAAC_QuestionDescription','ESCOskill','conceptUri']]\n",
    "df_final=df_final.loc[df_final['conceptUri'].notna()].drop_duplicates()\n",
    "df_final.rename(columns={'ESCOskill':'ESCO_skill_en','conceptUri':'ESCO_skill_conceptUri'},inplace=True)\n",
    "df_final['ESCO_version']='v1.0.8'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export\n",
    "df_final[['PIAAC_QuestionId','PIAAC_QuestionDescription','ESCO_skill_conceptUri','ESCO_skill_en','ESCO_version']].to_csv('output/PIAAC2ESCO.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "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>PIAAC_QuestionId</th>\n",
       "      <th>PIAAC_QuestionDescription</th>\n",
       "      <th>ESCO_skill_conceptUri</th>\n",
       "      <th>ESCO_skill_en</th>\n",
       "      <th>ESCO_version</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>F_Q02b</td>\n",
       "      <td>instructing training or teaching people indivi...</td>\n",
       "      <td>http://data.europa.eu/esco/skill/87439d9b-b537...</td>\n",
       "      <td>coach young people</td>\n",
       "      <td>v1.0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>F_Q02b</td>\n",
       "      <td>instructing training or teaching people indivi...</td>\n",
       "      <td>http://data.europa.eu/esco/skill/87439d9b-b537...</td>\n",
       "      <td>coach youngsters</td>\n",
       "      <td>v1.0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>F_Q02b</td>\n",
       "      <td>instructing training or teaching people indivi...</td>\n",
       "      <td>http://data.europa.eu/esco/skill/699e7c26-6502...</td>\n",
       "      <td>educate others</td>\n",
       "      <td>v1.0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>F_Q02b</td>\n",
       "      <td>instructing training or teaching people indivi...</td>\n",
       "      <td>http://data.europa.eu/esco/skill/87439d9b-b537...</td>\n",
       "      <td>educate young people</td>\n",
       "      <td>v1.0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>F_Q02b</td>\n",
       "      <td>instructing training or teaching people indivi...</td>\n",
       "      <td>http://data.europa.eu/esco/skill/87439d9b-b537...</td>\n",
       "      <td>facilitate young peoples education</td>\n",
       "      <td>v1.0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>I_Q04l</td>\n",
       "      <td>I like to figure out how different ideas fit t...</td>\n",
       "      <td>http://data.europa.eu/esco/skill/c624c6a3-b0ba...</td>\n",
       "      <td>thinking creatively</td>\n",
       "      <td>v1.0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>I_Q04l</td>\n",
       "      <td>I like to figure out how different ideas fit t...</td>\n",
       "      <td>http://data.europa.eu/esco/skill/e186976a-64f0...</td>\n",
       "      <td>thinking proactively</td>\n",
       "      <td>v1.0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>I_Q04l</td>\n",
       "      <td>I like to figure out how different ideas fit t...</td>\n",
       "      <td>http://data.europa.eu/esco/skill/c624c6a3-b0ba...</td>\n",
       "      <td>use imagination</td>\n",
       "      <td>v1.0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>I_Q04l</td>\n",
       "      <td>I like to figure out how different ideas fit t...</td>\n",
       "      <td>http://data.europa.eu/esco/skill/c624c6a3-b0ba...</td>\n",
       "      <td>visualise completed project</td>\n",
       "      <td>v1.0.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>I_Q04l</td>\n",
       "      <td>I like to figure out how different ideas fit t...</td>\n",
       "      <td>http://data.europa.eu/esco/skill/c624c6a3-b0ba...</td>\n",
       "      <td>visualise completed work</td>\n",
       "      <td>v1.0.8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>471 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   PIAAC_QuestionId                          PIAAC_QuestionDescription  \\\n",
       "32           F_Q02b  instructing training or teaching people indivi...   \n",
       "32           F_Q02b  instructing training or teaching people indivi...   \n",
       "20           F_Q02b  instructing training or teaching people indivi...   \n",
       "32           F_Q02b  instructing training or teaching people indivi...   \n",
       "32           F_Q02b  instructing training or teaching people indivi...   \n",
       "..              ...                                                ...   \n",
       "39           I_Q04l  I like to figure out how different ideas fit t...   \n",
       "45           I_Q04l  I like to figure out how different ideas fit t...   \n",
       "39           I_Q04l  I like to figure out how different ideas fit t...   \n",
       "39           I_Q04l  I like to figure out how different ideas fit t...   \n",
       "39           I_Q04l  I like to figure out how different ideas fit t...   \n",
       "\n",
       "                                ESCO_skill_conceptUri  \\\n",
       "32  http://data.europa.eu/esco/skill/87439d9b-b537...   \n",
       "32  http://data.europa.eu/esco/skill/87439d9b-b537...   \n",
       "20  http://data.europa.eu/esco/skill/699e7c26-6502...   \n",
       "32  http://data.europa.eu/esco/skill/87439d9b-b537...   \n",
       "32  http://data.europa.eu/esco/skill/87439d9b-b537...   \n",
       "..                                                ...   \n",
       "39  http://data.europa.eu/esco/skill/c624c6a3-b0ba...   \n",
       "45  http://data.europa.eu/esco/skill/e186976a-64f0...   \n",
       "39  http://data.europa.eu/esco/skill/c624c6a3-b0ba...   \n",
       "39  http://data.europa.eu/esco/skill/c624c6a3-b0ba...   \n",
       "39  http://data.europa.eu/esco/skill/c624c6a3-b0ba...   \n",
       "\n",
       "                         ESCO_skill_en ESCO_version  \n",
       "32                  coach young people       v1.0.8  \n",
       "32                    coach youngsters       v1.0.8  \n",
       "20                      educate others       v1.0.8  \n",
       "32                educate young people       v1.0.8  \n",
       "32  facilitate young peoples education       v1.0.8  \n",
       "..                                 ...          ...  \n",
       "39                 thinking creatively       v1.0.8  \n",
       "45                thinking proactively       v1.0.8  \n",
       "39                     use imagination       v1.0.8  \n",
       "39         visualise completed project       v1.0.8  \n",
       "39            visualise completed work       v1.0.8  \n",
       "\n",
       "[471 rows x 5 columns]"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_final[['PIAAC_QuestionId','PIAAC_QuestionDescription','ESCO_skill_conceptUri','ESCO_skill_en','ESCO_version']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8.13 ('base')",
   "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.13"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "40d3a090f54c6569ab1632332b64b2c03c39dcf918b08424e98f38b5ae0af88f"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}