Etoiles_Europe.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%reload_ext autoreload\n",
    "%autoreload\n",
    "%reload_ext dotenv\n",
    "%dotenv\n",
    "%run C:/Users/zfriant/Documents/GitHub/pydref/pydref.py\n",
    "pydref = Pydref()\n",
    "\n",
    "import pandas as pd, requests, re, string, time, urllib3,  os, dotenv\n",
    "requests.packages.urllib3.disable_warnings(requests.packages.urllib3.exceptions.InsecureRequestWarning)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# déclaration variable année de remise du prix\n",
    "INPUT_FILE = \"datas-source/etoiles_\"\n",
    "an = 2022\n",
    "edition = 10\n",
    "PATH = \"C:/Users/zfriant/Documents/OneDrive/PCRI/Etoiles/\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# export de la dernière année à traiter: etoiles_2020.xlsx\n",
    "etoiles = pd.DataFrame(pd.read_excel(PATH + INPUT_FILE + str(an) + \".xlsx\", na_values=\" \", sheet_name=0, keep_default_na=False,\n",
    "                                 dtype={'id_struct': str} ))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "etoiles.columns.values.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "etoiles.columns = etoiles.columns.str.lower()\n",
    "etoiles.columns = etoiles.columns.str.normalize('NFKD').str.encode('ascii', errors='ignore').str.decode('utf-8')\n",
    "etoiles.columns.values.tolist()\n",
    "\n",
    "l = ['prenom', 'nom']\n",
    "for i in l:\n",
    "    etoiles[i] = etoiles[i].apply(lambda x: str(x).replace(u'\\xa0', u'').strip())\n",
    "    \n",
    "# renommage des vars à utiliser dans les traitements\n",
    "etoiles = etoiles.rename(columns={'project_nbr':\"project_id\", \"genre\":\"sexe\"})\n",
    "etoiles.columns.values.tolist()\n",
    "\n",
    "etoiles = etoiles.loc[:, [\"project_id\", \"prenom\", \"nom\", \"id_struct\", \"sexe\", 'mention']]\n",
    "etoiles = etoiles.assign(person =  etoiles[\"prenom\"] +\" \"+ etoiles[\"nom\"])\n",
    "\n",
    "# etoiles = etoiles.assign(type_de_prix = \"Etoiles de l'Europe\")\n",
    "etoiles = etoiles.assign(type_de_prix=\"Etoiles de l'Europe\", edition=str(an), person=etoiles[\"prenom\"] +\" \"+ etoiles[\"nom\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "et = etoiles.to_dict(orient = \"records\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # fonction de match des personnes pour récupérer le idref\n",
    "counter = 0\n",
    "for e in et:\n",
    "    try:\n",
    "        response = pydref.identify(e['person'])\n",
    "    except:\n",
    "        print(f\"erreur pour {e['person']} on attend 5s.\")\n",
    "        time.sleep(5)\n",
    "        continue\n",
    "    if not response.get('idref'):\n",
    "        print(f\"NO idref:{e['person']}\" )\n",
    "    else:\n",
    "        counter += 1\n",
    "        print(counter)\n",
    "        e['idref'] = response.get('idref')\n",
    "    \n",
    "        \n",
    "#     del(e['person'])\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# match avec idref par le nom et prénom, update avec le idref detecté sinon récupération du idrefold\n",
    "\n",
    "for e in et:     \n",
    "#     e[\"idref\"] = persons_matcher(e)\n",
    "#     if e[\"idref\"] is None and e[\"idrefold\"]:\n",
    "#         e[\"idref\"] = e[\"idrefold\"]\n",
    "#     del(e[\"idrefold\"])\n",
    "    if not e[\"idref\"] is None:\n",
    "        e[\"idref\"] = e[\"idref\"].strip(\"idref\")\n",
    "        e[\"idref_lien\"] = \"http://www.idref.fr/\" + e[\"idref\"]\n",
    "    else:\n",
    "        e[\"idref\"] = \"\"\n",
    "        e[\"idref_lien\"] = \"\"\n",
    "    \n",
    "    if e['sexe']:\n",
    "        e['sexe'] = e['sexe'].lower()\n",
    "        if re.search(r'^h', e['sexe']):\n",
    "            e['sexe'] = 'homme'\n",
    "            e['sexe_code'] = '1'\n",
    "        if re.search(r'^f', e['sexe']):\n",
    "            e['sexe'] = 'femme'\n",
    "            e['sexe_code'] = '2'\n",
    "        e['sexe'] = e['sexe'].capitalize()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "project_dict = {}\n",
    "for e in et:\n",
    "    list_to_change=['prenom', 'nom', 'idref', 'idref_lien', 'sexe_code', 'sexe']\n",
    "    project_id = e['project_id']\n",
    "    if project_id not in project_dict:\n",
    "        new_elt = {'project_id': project_id}\n",
    "        for col in ['mention', 'edition', 'type_de_prix', 'id_struct']:\n",
    "            new_elt[col] = e[col]\n",
    "        for col in list_to_change:\n",
    "            new_elt[col] = []\n",
    "    else:\n",
    "        new_elt = project_dict[project_id]\n",
    "    for col in list_to_change:    \n",
    "        new_elt[col].append(e[col])\n",
    "    project_dict[project_id] = new_elt\n",
    "\n",
    "for e in project_dict:\n",
    "    elt = project_dict[e]\n",
    "    for f in elt:\n",
    "        if isinstance(elt[f], list):\n",
    "            project_dict[e][f] = ';'.join(elt[f])\n",
    "            \n",
    "tmp=pd.DataFrame(project_dict.values())\n",
    "tmp=tmp.to_dict(orient=\"records\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# fonction de match pour les organisations: lien avec id_struct rnsr, siren,...\n",
    "\n",
    "def struct_matcher(data):\n",
    "    print(data.get('id_struct'))\n",
    "    url = f\"http://185.161.45.213/organizations/organizations/_match?id={data.get('id_struct')}\"\n",
    "    rq = requests.get(url=url, headers={'accept':'application/json', 'Authorization': os.environ.get('token')})\n",
    "    response = rq.json()\n",
    "    if response.get(\"hits\") == 1:\n",
    "        result = response.get(\"data\")[0].get(\"id\")\n",
    "        return result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# match pour récupérer le id_scanr des structures et créer un url scanr\n",
    "for e in tmp: \n",
    "    e[\"structure_identifiant\"] = struct_matcher(e)\n",
    "    if e[\"structure_identifiant\"]:\n",
    "        e[\"structure_lien_scanr\"] = \"https://scanr.enseignementsup-recherche.gouv.fr/structure/\" + e.get(\"structure_identifiant\")\n",
    "    if e[\"structure_identifiant\"] is None and e[\"id_struct\"]:\n",
    "        e[\"structure_identifiant\"] = e[\"id_struct\"]\n",
    "    del(e[\"id_struct\"])    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# fonction récupérer les infos des structures libell, sigle, com_code, geoloc...\n",
    "\n",
    "def struct_info(data):\n",
    "    url_match = \"http://185.161.45.213/organizations/scanr/\"\n",
    "    if data.get(\"structure_identifiant\"):\n",
    "        url = url_match + data.get(\"structure_identifiant\")\n",
    "        rq = requests.get(url = url,  headers={'accept':'application/json', 'Authorization': os.environ.get('token')})\n",
    "        response = {\n",
    "            \"structure_libelle\": rq.json().get(\"label\", {}).get(\"default\"),\n",
    "            \"structure_sigle\": rq.json().get(\"acronym\", {}).get(\"fr\"),\n",
    "#             \"structure_nature\": rq.json().get(\"kind\")\n",
    "        }\n",
    "        if rq.json().get(\"address\"):\n",
    "            resp_temp = {\n",
    "            \"commune_code\": rq.json().get(\"address\")[0].get(\"citycode\"),\n",
    "            \"lat\": str(rq.json().get(\"address\")[0].get(\"gps\", {}).get(\"lat\")),\n",
    "            \"lon\": str(rq.json().get(\"address\")[0].get(\"gps\", {}).get(\"lon\"))\n",
    "        }\n",
    "            response.update(resp_temp)\n",
    "        return response"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# lancement fonction recup infos des structures et remplace les sauts de ligne par un espace (pas sur que ça marche !)\n",
    "for e in tmp:\n",
    "    if struct_info(e) is not None:\n",
    "        e.update(struct_info(e))\n",
    "    if e.get(\"lat\"):\n",
    "        e[\"geolocalisation\"] = e.pop(\"lat\") + \", \" + e.pop(\"lon\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# fonction récupération des codes de geoloc region, dep, unite urbaine...\n",
    "def geocod(data):\n",
    "    url_match = \"http://185.161.45.213/datastore/geocodes/\"\n",
    "    if data.get(\"commune_code\"):\n",
    "        url = url_match + data.get(\"commune_code\")\n",
    "        rq = requests.get(url = url,  headers={'accept':'application/json', 'Authorization': os.environ.get('token')})\n",
    "        response = {\n",
    "            \"commune_nom\": rq.json().get(\"com_nom\"),\n",
    "            \"unite_urbaine_code\": rq.json().get(\"uu_id\"),\n",
    "            \"unite_urbaine_nom\": rq.json().get(\"uucr_nom\"),\n",
    "            \"departement_code\": rq.json().get(\"dep_id\"),\n",
    "            \"departement_nom\": rq.json().get(\"dep_nom\"),\n",
    "            \"region_code\": rq.json().get(\"reg_id\"),\n",
    "            \"region_nom\": rq.json().get(\"reg_nom\")\n",
    "        }\n",
    "        return response"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for e in tmp:\n",
    "    if geocod(e):       \n",
    "        e.update(geocod(e))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def project_info(data):\n",
    "    url_match = \"http://185.161.45.213/projects/projects/\"\n",
    "    id = str(data.get(\"project_id\"))\n",
    "    if id:\n",
    "        url = url_match + id\n",
    "        rq = requests.get(url = url,  headers={'accept':'application/json', 'Authorization': os.environ.get('token')})\n",
    "        if rq.status_code == 200:\n",
    "            if rq.json().get(\"type\") == \"H2020\":\n",
    "                response = {\n",
    "                    \"financement_type\": rq.json().get(\"action\")[0]['code'],\n",
    "                    \"financement_name\": rq.json().get(\"action\")[0]['name'],\n",
    "                    \"programme_code\": rq.json().get(\"priorities\")[1]['code'],\n",
    "                    \"programme_nom\": rq.json().get(\"priorities\")[1]['name'],\n",
    "                    \"appel_a_projet_code\": rq.json().get(\"call_code\"),\n",
    "                    \"projet_acronyme\": rq.json().get(\"acronym\"),\n",
    "                    \"projet_titre\": rq.json().get(\"name\").get(\"en\"),\n",
    "                    \"projet_resume\": rq.json().get(\"description\").get(\"en\", \"\")\n",
    "                }\n",
    "\n",
    "                return response\n",
    "            \n",
    "            if rq.json().get(\"type\") == \"FP7\":\n",
    "                response = {\n",
    "                    \"financement_type\": rq.json().get(\"action\")[0]['code'],\n",
    "                    \"financement_name\": rq.json().get(\"action\")[0]['name'],\n",
    "                    \"programme_code\": rq.json().get(\"priorities\")[1]['code'],\n",
    "                    \"programme_nom\": rq.json().get(\"priorities\")[1]['name'],\n",
    "                    \"appel_a_projet_code\": rq.json().get(\"call_name\"),\n",
    "                    \"projet_acronyme\": rq.json().get(\"acronym\"),\n",
    "                    \"projet_titre\": rq.json().get(\"name\").get(\"en\"),\n",
    "                    \"projet_resume\": rq.json().get(\"description\").get(\"en\", \"\")\n",
    "                }\n",
    "\n",
    "                return response"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for e in tmp: \n",
    "    e[\"projet_lien_scanr\"] = \"https://scanr.enseignementsup-recherche.gouv.fr/project/\" + str(e[\"project_id\"])\n",
    "    e[\"projet_lien_cordis\"] = \"https://cordis.europa.eu/project/id/\" + str(e[\"project_id\"])\n",
    "    \n",
    "    if project_info(e):  \n",
    "        e.update(project_info(e))\n",
    "\n",
    "    l = [\"projet_resume\", \"projet_titre\"]\n",
    "    for i in l:\n",
    "        if e.get(i):\n",
    "            e[i] = re.sub('[\\t\\n\\r]', ' ', e[i])\n",
    "            e[i] = re.sub('\\s+', ' ', e[i])           "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "full=pd.DataFrame(pd.read_excel(PATH + \"etoiles_europe_full.xlsx\", na_values=\" \", sheet_name=0, keep_default_na=False ))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "export = pd.DataFrame.from_dict(tmp, orient='columns')\n",
    "export = pd.concat([full, export], ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "export = export[['type_de_prix', 'edition', 'mention', 'prenom', 'nom', 'sexe_code', 'sexe', 'idref', \n",
    "            'projet_acronyme', 'projet_titre', 'appel_a_projet_code', 'thematique', \n",
    "            'programme_code', 'programme_nom', 'financement_type', 'financement_name', 'project_id', 'projet_resume',\n",
    "            'structure_identifiant', 'structure_libelle', 'structure_sigle', \n",
    "            'commune_code', 'commune_nom', 'unite_urbaine_code', 'unite_urbaine_nom', 'departement_code', 'departement_nom',\n",
    "            'region_code', 'region_nom', 'geolocalisation', 'idref_lien', 'projet_lien_scanr', 'projet_lien_cordis',\n",
    "            'structure_lien_scanr']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "export.to_csv(PATH + \"etoiles_europe_2013-\" + str(an) + \".csv\", sep=\";\", \n",
    "              encoding=\"ANSI\", na_rep=\"\", index=False, line_terminator='\\r')\n",
    "\n",
    "'''VERIFIER dans le fichier CSV qu\\'il ne manque pas des infos existantes comme les idref non trouvés et sauver ce fichier\n",
    "à nouveau dans le dossier OPENDATA sous le nom fr-esr-etoile-de-l-europe.csv'''\n",
    "\n",
    "# export.to_csv(PATH + \"open data/fr-esr-etoile-de-l-europe.csv\", sep=\";\", \n",
    "#               encoding=\"ANSI\", na_rep=\"\", index=False, line_terminator='\\r')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.7"
  },
  "vscode": {
   "interpreter": {
    "hash": "2ffef566509b1d8a273c05e2b86ccf589d4457cd94cfc8600149faabe1b9e6e1"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}