process_and_send_data_details.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "02598747",
   "metadata": {},
   "source": [
    "# Traitement des données de financements "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "a59f7608",
   "metadata": {},
   "outputs": [],
   "source": [
    "#importer les packages\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "from code_utils.utils import extract_first_name,replace_all,get_scanR_structure,get_id\n",
    "from code_utils.cached_data_handler import get_structure, get_person\n",
    "from code_utils.features_into_dictionnary import persons, projects, address\n",
    "from tqdm import tqdm\n",
    "import pprint as pp\n",
    "tqdm.pandas()\n",
    "from code_utils.pickle import load_cache,write_cache\n",
    "import os\n",
    "from dotenv import load_dotenv\n",
    "import requests\n",
    "\n",
    "load_dotenv()\n",
    "\n",
    "Authorization = os.getenv('Authorization_access_185')\n",
    "Authorization_ORCID = os.getenv('Authorization_cluster_BSO_ORCID')\n",
    "url_cluster = os.getenv('url_cluster')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "b60621a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "sources=pd.read_json('sources.json')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "95e2a545",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'ANSES'"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "source=list(sources.keys())[1]\n",
    "source"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "7fad3e27",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "971 data in cache\n",
      "0 data in cache\n",
      "0 data in cache\n"
     ]
    }
   ],
   "source": [
    "#cache structures, personnes et orcid avec differentes sources de donnees\n",
    "cached_data = {}\n",
    "try:\n",
    "    cached_data = load_cache(cached_data,f\"./DATA/{source}/caches/cached_{source.lower()}_data.pkl\")\n",
    "except:\n",
    "    write_cache(cached_data,f\"./DATA/{source}/caches/cached_{source.lower()}_data.pkl\")\n",
    "    \n",
    "cached_data_persons = {}\n",
    "try:\n",
    "    cached_data_persons = load_cache(cached_data_persons,f\"./DATA/{source}/caches/cached_{source.lower()}_data_persons.pkl\")\n",
    "except:\n",
    "    write_cache(cached_data_persons,f\"./DATA/{source}/caches/cached_{source.lower()}_data_persons.pkl\")\n",
    "    \n",
    "cached_data_orcid = {}\n",
    "try:\n",
    "    cached_data_orcid = load_cache(cached_data_orcid,f\"./DATA/{source}/caches/cached_{source.lower()}_data_orcid.pkl\")\n",
    "except:\n",
    "    write_cache(cached_data_orcid,f\"./DATA/{source}/caches/cached_{source.lower()}_data_orcid.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6bcfb200",
   "metadata": {},
   "source": [
    "# Données partenaires"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "3c82fbd0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# amener les partenaires depuis le site \n",
    "if source=='ANR':\n",
    "    page_partenaires_10 = requests.get(sources[source]['url_partenaires']).json()\n",
    "    colonnes_partenaires_10 = page_partenaires_10['columns']\n",
    "    donnees_partenaires_10 = page_partenaires_10['data']\n",
    "    df_partenaires=pd.DataFrame(data=donnees_partenaires_10,columns=colonnes_partenaires_10)\n",
    "elif source=='ANSES':\n",
    "    df_from_anses=pd.read_excel(sources[source]['url_partenaires'])\n",
    "    df=df_from_anses.iloc[1:,:]\n",
    "    df.columns=list(df_from_anses.iloc[0,:])\n",
    "    dict_equipe={list(df_from_anses.columns)[k].replace('Équipe 10 ','Équipe 10').replace('Équipe13','Équipe 13'):k for k in range (len(list(df_from_anses.columns))) if list(df_from_anses.columns)[k].find('Équipe')>=0}\n",
    "    list_df=[]\n",
    "    number=3\n",
    "    for n in range(1,len(dict_equipe)+1):\n",
    "        equipe_n=pd.concat([df.iloc[:,0:3],df.iloc[:,number:number+6]], axis=1)\n",
    "        list_df.append(equipe_n)\n",
    "        number+=6\n",
    "    df_partenaires=pd.concat([list_df[k].dropna(subset=[sources[source]['nom'], sources[source]['prenom'],sources[source]['nom_structure'], sources[source]['nom'], 'Pays'], how='all') for k in range(len(list_df))])\n",
    "    \n",
    "elif source=='IRESP':\n",
    "    df_partenaires1=pd.read_csv(sources[source]['url_partenaires1'] ,sep=\";\", encoding='UTF-8')\n",
    "    df_partenaires2=pd.read_csv(sources[source]['url_partenaires2'] ,sep=\";\", encoding='UTF-8')\n",
    "    df_partenaires=pd.concat([df_partenaires1,df_partenaires2])\n",
    "elif source=='ADEME':\n",
    "    df_partenaires=pd.read_csv(sources[source]['url_partenaires'] ,sep=\",\", encoding='ISO-8859-1', on_bad_lines='skip')\n",
    "else:    \n",
    "    df_partenaires=pd.read_csv(sources[source]['url_partenaires'] ,sep=\";\", encoding='ISO-8859-1')\n",
    "\n",
    "df_partenaires=df_partenaires.reset_index()\n",
    "del df_partenaires['index']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "230e93cc",
   "metadata": {},
   "source": [
    "# Matcher établissement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "eca75cf4",
   "metadata": {},
   "outputs": [],
   "source": [
    "id_struct=df_partenaires\n",
    "id_struct[f\"{sources[source]['nom_structure']}2\"]=id_struct.loc[:,sources[source]['nom_structure']].apply(lambda x: replace_all(str(x).lower().replace(\" d e\",\" d'e\").replace(\" d a\",\" d'a\").replace(\" d i\",\" d'i\").replace(\" d o\",\" d'o\").replace(\" d u\",\" d'u\").replace(\" d y\",\" d'y\").replace(\" d h\",\" d'h\").replace(\" l e\",\" l'e\").replace(\" l a\",\" l'a\").replace(\" l i\",\" l'i\").replace(\" l o\",\" l'o\").replace(\" l u\",\" l'u\").replace(\" l y\",\" l'y\").replace(\" l h\",\" l'h\")))\n",
    "id_struct=id_struct.drop_duplicates(subset=[f\"{sources[source]['nom_structure']}2\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc0b7e40",
   "metadata": {},
   "outputs": [],
   "source": [
    "id_struct.progress_apply(lambda row: get_structure(row,source,cached_data,sources[source]['nom_structure'],sources[source]['ville'],sources[source]['pays'],sources[source]['code_projet'],False), axis=1) \n",
    "write_cache(cached_data,f\"./DATA/{source}/caches/cached_{source}_data.pkl\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "84ae48b2",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(cached_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "feeebfff",
   "metadata": {},
   "outputs": [],
   "source": [
    "id_struct['id_structure_matcher']=id_struct.loc[:,sources[source]['nom_structure']].apply(lambda x: cached_data[x])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0a6033b7",
   "metadata": {},
   "outputs": [],
   "source": [
    "id_struct"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "d968cf8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "id_struct=id_struct.reset_index()\n",
    "del id_struct['index']\n",
    "id_struct.to_excel(f\"./DATA/{source}/df_partenaires.xlsx\")\n",
    "id_struct.to_json(f\"./DATA/{source}/df_partenaires.json\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5f3afeca",
   "metadata": {},
   "outputs": [],
   "source": [
    "id_struct=pd.read_json(f\"./DATA/{source}/df_partenaires.json\")\n",
    "id_struct=id_struct[[sources[source]['nom_structure'],'id_structure_matcher']]\n",
    "id_struct[f\"{sources[source]['nom_structure']}2\"]=id_struct.loc[:,sources[source]['nom_structure']].apply(lambda x: replace_all(str(x).lower().replace(\" d e\",\" d'e\").replace(\" d a\",\" d'a\").replace(\" d i\",\" d'i\").replace(\" d o\",\" d'o\").replace(\" d u\",\" d'u\").replace(\" d y\",\" d'y\").replace(\" d h\",\" d'h\").replace(\" l e\",\" l'e\").replace(\" l a\",\" l'a\").replace(\" l i\",\" l'i\").replace(\" l o\",\" l'o\").replace(\" l u\",\" l'u\").replace(\" l y\",\" l'y\").replace(\" l h\",\" l'h\")))\n",
    "\n",
    "df_partenaires[f\"{sources[source]['nom_structure']}2\"]=df_partenaires.loc[:,sources[source]['nom_structure']].apply(lambda x: replace_all(str(x).lower().replace(\" d e\",\" d'e\").replace(\" d a\",\" d'a\").replace(\" d i\",\" d'i\").replace(\" d o\",\" d'o\").replace(\" d u\",\" d'u\").replace(\" d y\",\" d'y\").replace(\" d h\",\" d'h\").replace(\" l e\",\" l'e\").replace(\" l a\",\" l'a\").replace(\" l i\",\" l'i\").replace(\" l o\",\" l'o\").replace(\" l u\",\" l'u\").replace(\" l y\",\" l'y\").replace(\" l h\",\" l'h\")))\n",
    "df_partenaires_struct=pd.merge(df_partenaires,id_struct[[f\"{sources[source]['nom_structure']}2\",'id_structure_matcher']], on=f\"{sources[source]['nom_structure']}2\", how='left')\n",
    "df_partenaires_struct"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d5f9b64f",
   "metadata": {},
   "outputs": [],
   "source": [
    "#compléter les données avec scanR\n",
    "url_scanr='https://storage.gra.cloud.ovh.net/v1/AUTH_32c5d10cb0fe4519b957064a111717e3/scanR/projects.json'\n",
    "requete_scanR = requests.get(url_scanr)\n",
    "page_scanR= requete_scanR.json()\n",
    "df_scanR=pd.DataFrame(page_scanR)\n",
    "scanR=df_scanR.explode('participants').loc[:,['id','participants']]\n",
    "scanR=scanR.rename(columns={'id':'id_anr'})\n",
    "scanR['index']=[x for x in range(len(scanR))]\n",
    "scanR=scanR.set_index('index')\n",
    "scanR['id_structure_scanr']=scanR['participants'].apply(lambda x: x.get(str('structure')) if isinstance(x, dict) else None )\n",
    "scanR['nom_struct']=scanR['participants'].apply(lambda x: get_scanR_structure(x))\n",
    "del scanR['participants']\n",
    "scanR_nettoye=scanR.drop_duplicates(subset='nom_struct')\n",
    "scanR_nettoye[f\"{sources[source]['nom_structure']}2\"]=scanR_nettoye.loc[:,'nom_struct'].apply(lambda x: replace_all(str(x).lower()))\n",
    "scanR_nettoye=scanR_nettoye[['id_structure_scanr',f\"{sources[source]['nom_structure']}2\"]]\n",
    "scanR_nettoye=scanR_nettoye.drop_duplicates(subset=f\"{sources[source]['nom_structure']}2\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ed71b425",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_partenaires_struct=pd.merge(df_partenaires_struct,scanR_nettoye, on=f\"{sources[source]['nom_structure']}2\", how='left')\n",
    "df_partenaires_struct"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "89dbba33",
   "metadata": {},
   "outputs": [],
   "source": [
    "#######fichier avec les identifiants structures rettrouvés à la main par Emmanuel ==> 'code'\n",
    "scanr_structures=pd.read_excel('scanr_partenaires_non_identifies.xlsx')\n",
    "scanr_structures[f\"{sources[source]['nom_structure']}2\"]=scanr_structures.loc[:,'Nom'].apply(lambda x: replace_all(str(x).lower().replace(\" d e\",\" d'e\").replace(\" d a\",\" d'a\").replace(\" d i\",\" d'i\").replace(\" d o\",\" d'o\").replace(\" d u\",\" d'u\").replace(\" d y\",\" d'y\").replace(\" d h\",\" d'h\").replace(\" l e\",\" l'e\").replace(\" l a\",\" l'a\").replace(\" l i\",\" l'i\").replace(\" l o\",\" l'o\").replace(\" l u\",\" l'u\").replace(\" l y\",\" l'y\").replace(\" l h\",\" l'h\")))\n",
    "scanr_structures=scanr_structures[[f\"{sources[source]['nom_structure']}2\",'code']]\n",
    "scanr_structures=scanr_structures.dropna().drop_duplicates(subset=f\"{sources[source]['nom_structure']}2\")\n",
    "df_partenaires_complet=pd.merge(df_partenaires_struct,scanr_structures, on=f\"{sources[source]['nom_structure']}2\", how='left')\n",
    "df_partenaires_complet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "03c18c20",
   "metadata": {},
   "outputs": [],
   "source": [
    "if 'finess' in list(df_partenaires.columns):\n",
    "    finess_siret=pd.read_json(\"finess_siret-siege.json\")\n",
    "    df_partenaires_complet=pd.merge(df_partenaires_complet,finess_siret,how='left', on='finess')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f8c8d110",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_partenaires_complet[sources[source]['identifiants_preferes_structure']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a67832c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_partenaires_complet.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "744c95bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_partenaires_complet['id_structure']=df_partenaires_complet.apply(lambda row: identifiant_prefere(row,sources[source]['identifiants_preferes_structure']), axis=1)\n",
    "df_partenaires_complet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18b49f3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_partenaires_complet[pd.isna(df_partenaires_complet.id_structure)]\n",
    "df_partenaires_complet.loc[(pd.isna(df_partenaires_complet['id_structure']))|(str(df_partenaires_complet['id_structure'])=='None')|(str(df_partenaires_complet['id_structure'])=='nan')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "42cb300a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_partenaires_complet.to_excel(f\"./DATA/{source}/df_partenaires_id_structures.xlsx\")\n",
    "df_partenaires_complet.to_json(f\"./DATA/{source}/df_partenaires_id_structures.json\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "1c690f9a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_partenaires_complet=pd.read_json(f\"./DATA/{source}/df_partenaires_id_structures.json\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "ec322580",
   "metadata": {},
   "outputs": [],
   "source": [
    "########récupération des structures sans identifiants pour les donner à Emmanuel\n",
    "identifiants_a_remplir=df_partenaires_complet.loc[(pd.isna(df_partenaires_complet['id_structure']))|(str(df_partenaires_complet['id_structure'])=='None')|(str(df_partenaires_complet['id_structure'])=='nan')]\n",
    "identifiants_a_remplir\n",
    "identifiants_a_remplir=identifiants_a_remplir.drop_duplicates(subset=f\"{sources[source]['nom_structure']}2\")\n",
    "identifiants_a_remplir=identifiants_a_remplir.reset_index()\n",
    "del identifiants_a_remplir['index']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "51ae89d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "if sources[source]['ville'] in list(identifiants_a_remplir.columns) and sources[source]['pays'] in list(identifiants_a_remplir.columns) and sources[source]['adresse'] not in list(identifiants_a_remplir.columns):\n",
    "    identifiants_a_remplir=identifiants_a_remplir[[sources[source]['nom_structure'],sources[source]['ville'],sources[source]['pays']]]\n",
    "elif sources[source]['ville'] in list(identifiants_a_remplir.columns) and sources[source]['pays'] in list(identifiants_a_remplir.columns) and sources[source]['adresse'] in list(identifiants_a_remplir.columns):\n",
    "    identifiants_a_remplir=identifiants_a_remplir[[sources[source]['nom_structure'],sources[source]['adresse'],sources[source]['ville'],sources[source]['pays']]]\n",
    "elif sources[source]['region'] in list(identifiants_a_remplir.columns):\n",
    "    identifiants_a_remplir=identifiants_a_remplir[[sources[source]['nom_structure'],sources[source]['region']]]\n",
    "elif sources[source]['ville'] in list(identifiants_a_remplir.columns) and sources[source]['pays'] not in list(identifiants_a_remplir.columns):\n",
    "    identifiants_a_remplir=identifiants_a_remplir[[sources[source]['nom_structure'],sources[source]['ville']]]\n",
    "identifiants_a_remplir"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "90b660db",
   "metadata": {},
   "outputs": [],
   "source": [
    "identifiants_a_remplir.to_excel(f\"./structures_manquantes/partenaires_non_identifies_{source}.xlsx\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "55b3e1da",
   "metadata": {},
   "source": [
    "# Matcher des chercheurs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "89e9d1dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_partenaires=pd.read_json(f\"./DATA/{source}/df_partners_id_structures.json\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba8ba43e",
   "metadata": {},
   "outputs": [],
   "source": [
    "if len([x for x in ['nom', 'prenom'] if x in list(sources[source].keys())])==2:\n",
    "    df_partenaires['id_personne']=df_partenaires.progress_apply(lambda row: get_person(row, cached_data_persons,sources[source]['nom'],sources[source]['prenom']), axis=1)\n",
    "    write_cache(cached_data_persons,f\"./DATA/{source}/caches/cached_data_persons.pkl\")\n",
    "    df_partenaires.to_excel(f\"./DATA/{source}/df_partenaires_id_personne.xlsx\")\n",
    "    df_partenaires.to_json(f\"./DATA/{source}/df_partenaires_id_personne.json\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "14039fb3",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_partenaires['id_personne']=df_partenaires.progress_apply(lambda row: get_person(row, cached_data_persons,sources[source]['nom'],sources[source]['prenom']), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "adc7546d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_partenaires.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c65d4fd7",
   "metadata": {},
   "outputs": [],
   "source": [
    "if sources[source]['id_ORCID'] in list(df_partenaires.columns):\n",
    "    df_partenaires=pd.read_json(f\"./DATA/{source}/df_partenaires_id_personne.json\")\n",
    "    df_partenaires['idref_ORCID']=df_partenaires.progress_apply(lambda row: orcid_to_idref(row,cached_data_orcid,sources[source]['id_ORCID'],Authorization_ORCID), axis=1)\n",
    "    write_cache(cached_data_orcid,f\"./DATA/{source}/caches/cached_data_orcid.pkl\")\n",
    "    df_partenaires.to_excel(f\"./DATA/{source}/df_partenaires_id_personne_ORCID.xlsx\")\n",
    "    df_partenaires.to_json(f\"./DATA/{source}/df_partenaires_id_personne_ORCID.json\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12332b18",
   "metadata": {},
   "source": [
    "# ENVOI DES PROJETS SUR SCANR"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "21cef2ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "if len(sources[source]['identifiants_preferes_personne'])==2:\n",
    "    df_partenaires=pd.read_json(f\"./DATA/{source}/df_partners_id_person_ORCID.json\")\n",
    "elif len(sources[source]['identifiants_preferes_personne'])==1:\n",
    "    df_partenaires=pd.read_json(f\"./DATA/{source}/df_partners_id_person.json\")\n",
    "else:\n",
    "    df_partenaires=pd.read_json(f\"./DATA/{source}/df_partners_id_structures.json\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "57413951",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_partenaires.loc[df_partenaires.id_structure.apply(lambda x :isinstance(x,list)),'id_structure']=df_partenaires.loc[df_partenaires.id_structure.apply(lambda x :isinstance(x,list)),'id_structure'].apply(lambda y: y[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "a9ed3582",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 1737/1737 [00:00<00:00, 28651.96it/s]\n"
     ]
    }
   ],
   "source": [
    "if len([x for x in ['nom', 'prenom'] if x in list(sources[source].keys())])==2:\n",
    "    df_partenaires['id_person']=df_partenaires.apply(lambda row: get_id(row,sources[source]['identifiants_preferes_personne']), axis=1)\n",
    "    df_partenaires['persons']=df_partenaires.progress_apply(lambda row: persons(row,sources[source]['prenom'],sources[source]['nom']) ,axis=1)\n",
    "else:\n",
    "    df_partenaires['persons']=np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "9ee1687d",
   "metadata": {},
   "outputs": [],
   "source": [
    "if source != 'SIRANO':\n",
    "    df_partenaires=df_partenaires.groupby([sources[source]['code_projet']]).agg({'persons': lambda x: [ y for y in x.tolist() if pd.isna(y)==False]}, dropna=False).reset_index()\n",
    "else:\n",
    "    df_projets=df_partenaires.groupby([sources[source]['code_projet'], sources[source]['annee'], sources[source]['acronyme'],sources[source]['titre'],sources[source]['budget']], dropna=False).agg({'persons': lambda x: [ y for y in x.tolist() if pd.isna(y)==False]}, dropna=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "e6ff1d0f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# amener les projets depuis le site \n",
    "if source=='ANR':\n",
    "    page_projets_10 = requests.get(sources[source]['url_projets']).json()\n",
    "    colonnes_projets_10 = page_projets_10['columns']\n",
    "    donnees_projets_10 = page_projets_10['data']\n",
    "    df_projets=pd.DataFrame(data=donnees_projets_10,columns=colonnes_projets_10)\n",
    "elif source=='IRESP':\n",
    "    df_projets1=pd.read_csv(sources[source]['url_projets1'] ,sep=\";\", encoding='UTF-8')\n",
    "    df_projets2=pd.read_csv(sources[source]['url_projets2'] ,sep=\";\", encoding='UTF-8')\n",
    "    df_projets=pd.concat([df_projets1,df_projets2])\n",
    "    df_projets.loc[pd.isna(df_projets['Titre_du_projet_FR']),'Titre_du_projet_FR']=df_projets.loc[pd.isna(df_projets['Titre_du_projet_FR']),'Titre_du_projet']\n",
    "elif source!='SIRANO':\n",
    "    df_projets=pd.read_csv(sources[source]['url_projects'] ,sep=\";\", encoding='ISO-8859-1')\n",
    "\n",
    "df_projets=df_projets.reset_index()\n",
    "del df_projets['index']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "7dd3868b",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 592/592 [00:00<00:00, 25594.80it/s]\n",
      "100%|██████████| 592/592 [00:00<00:00, 20809.30it/s]\n"
     ]
    }
   ],
   "source": [
    "if source!='SIRANO':\n",
    "    df_projets=pd.merge(df_projets,df_partenaires,on=sources[source]['code_projet'], how='left')\n",
    "else :\n",
    "    df_projets['id']=df_partenaires.apply(lambda row: f\"{row[sources[source]['code_projet']]}-{row[sources[source]['annee']]}-{row[sources[source]['acronyme']]}\" , axis=1)\n",
    "    del df_projets['code_projet']\n",
    "    sources[source]['code_projet']='id'\n",
    "\n",
    "df_projets['type']=source\n",
    "df_projets['name']=df_projets.progress_apply(lambda row: projects(row,sources[source]['titre_fr'],sources[source]['titre_en']) ,axis=1)\n",
    "df_projets['description']=df_projets.progress_apply(lambda row: projects(row,sources[source]['resume_fr'],sources[source]['resume_en']) ,axis=1)\n",
    "df_projets.loc[:,sources[source]['budget']]=df_projets.loc[:,sources[source]['budget']].apply(lambda x : float(str(x).replace('.0','').replace('.00','').replace(' ','').replace(',','.').replace('€','').replace('\\x80','')))\n",
    "df_projets=df_projets.rename(columns={sources[source]['annee']:'year',sources[source]['acronyme']:'acronym',\n",
    "                                      sources[source]['budget']:'budget_financed',sources[source]['code_projet']:'id'})\n",
    "df_projets=df_projets[['id','type','name','description','acronym','year','budget_financed','persons']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "3c620c25",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'id': 'ANSES-06-EST-002',\n",
       " 'type': 'ANSES',\n",
       " 'name': {'fr': 'Projet PAISIM : Pollution Atmosphérique, Inégalités Sociales et Infarctus du Myocarde',\n",
       "  'en': 'The PAISIM project: Air Pollution, Social Inequalities & Myocardial Infarction'},\n",
       " 'description': {'fr': 'Depuis une quinzaine d\\x92années, plusieurs équipes se sont intéressées aux liens existant entre le polymorphisme de l\\x92ALAD G177C (acide delta-aminolévulinique déshydratase) (rs1800435) et l\\x92intoxication par le plomb. Les études publiées ne sont pas concordantes quant aux liens existants entre ce polymorphisme et les effets du plomb sur la fonction rénale. L\\x92opportunité nous est donnée d\\x92analyser ces liens sur une cohorte d\\x92ex-salariés d\\x92une fonderie de plomb et de zinc de la Région Nord \\x96 Pas de Calais, qui bénéficient depuis 2003 d\\x92un suivi clinique et biologique post-expositionnel. Ce suivi va être prolongé jusqu\\x92en 2009, dans le cadre d\\x92une étude scientifique visant à étudier les facteurs de variation de la toxicocinétique de décroissance du plomb sanguin, permettant d\\x92associer d\\x92autres recherches plus fondamentales, telle que celle que nous proposons dans le cadre de cet appel d\\x92offre. L\\x92objectif principal de l\\x92étude proposée est d\\x92analyser l\\x92impact du polymorphisme de l\\x92ALAD G177C sur la toxicité rénale du plomb, ainsi que les éventuelles interactions gène - environnement. L\\x92étude que nous envisageons est une étude transversale, s\\x92intéressant à la population d\\x92ex-salariés adhérant au suivi (environ 600 sujets). Chaque ex-salarié volontaire répondra à un questionnaire standardisé permettant le recueil d\\x92informations personnelles et professionnelles. Les prélèvements biologiques permettront la détermination du polymorphisme de l\\x92ALAD G177C, la plombémie, et l\\x92évaluation de la fonction rénale (urée sanguine, créatinine sérique, clairance de la créatinine, rétinol binding protein (RBP) et activité de la N-acétyl-glucosaminidase (NAG). Une base de données sera élaborée, fusionnant l\\x92ensemble des données biologiques de suivi médical des expositions en cours d\\x92activité et les données biologiques recueillies depuis le suivi post-expositionnel. Ceci nous permettra de calculer un index cumulé d\\x92exposition au plomb (CBLI). Les variations des marqueurs de la fonction rénale seront étudiées en fonction des paramètres d\\x92imprégnation au plomb (CBLI) et en fonction du polymorphisme de l\\x92ALAD G177C. Des modèles de régression linéaires multiples seront utilisés pour l\\x92analyse des données. La réalisation de ce projet est prévue sur 3 années (2006-2008). Cette étude va permettre de donner un nouveau point de vue concernant les liens entre polymorphisme de l\\x92ALAD G177C et la toxicité rénale du plomb, permettant d\\x92élargir le débat scientifique. Elle peut avoir des conséquences sur le suivi des salariés exposés au plomb, si la détermination du polymorphisme de l\\x92ALAD G177C peut permettre d\\x92adapter la surveillance de ces sujets. Enfin, cette étude peut aussi avoir des conséquences sur les modalités de surveillance de l\\x92exposition au plomb en population générale, intégrant une éventuelle détermination du polymorphisme de l\\x92ALAD G177C.',\n",
       "  'en': 'Since about fifteen years, several teams have been interesting in the relations existing between polymorphism of ALAD G177C (delta-aminolevulinic acid dehydratase) (rs1800435) and the intoxication by lead. The published studies are not concordant about the links between this polymorphism and the renal effects of lead. The advisability is given to us to analyze these relations among ex-employees of a lead and zinc smelter of the \\x93Région Nord - Pas-de-Calais\\x94. This smelter stopped its production and dismissed all the workers in March 2003. Since this date, the ex-employees benefit of a clinical and biological follow-up. This follow-up will be prolonged until 2009 within the framework of a scientific study, which aim is to study the factors of variation of the toxicocinetic of the blood lead level decrease. We want to associate to this follow-up another fundamental research, such as the study we will explain below. The main objective of our study is to analyze the impact of the polymorphism of ALAD G177C on the renal toxicity of lead, and to explore the potential gene - environment interactions. It is a cross-sectional study, interesting all the population of ex-employees adhering to the follow-up of the blood lead level (about 600 subjects). Each ex-employee voluntary for this study will answer a standardized questionnaire allowing the collection of personal and professional information. The biological sampling will allow the determination of the polymorphism of ALAD G177C, the blood lead level, and the evaluation of the renal function (blood urea nitrogen, serum creatinin, clearance of creatinin, retinol binding protein (RBP) and activity of N-acetyl-glucosaminidase (NAG). A data base will be elaborate, amalgamating whole of the biological data of medical follow-up of the lead exposures during occupational activity and the biological data collected since the follow-up. A cumulative blood lead index (CBLI) will be calculated from the time weighted integration of available data of blood lead. The variations of the renal markers will be studied according to the parameters of impregnation to lead (CBLI) and according to the polymorphism of ALAD G177C. Statistical analysis will be conducted by multiple linear regressions. The realization of this project is scheduled over 3 years (2006-2008). This study will make possible to give a new point of view concerning the relations between polymorphism of ALAD G177C and the renal toxicity of lead, leading to widen the scientific debate. If the determination of the polymorphism of ALAD G177C can make it possible to adapt the monitoring of lead exposed employees, this study can have consequences on the follow-up of these subjects. Lastly, this study can also have consequences on the methods of monitoring of the exposure to lead in general population, integrating a possible determination of the polymorphism of ALAD G177C.'},\n",
       " 'acronym': 'PAISIM',\n",
       " 'year': 2006,\n",
       " 'budget_financed': 9000.0,\n",
       " 'persons': [{'first_name': 'Denis ',\n",
       "   'last_name': 'Bard',\n",
       "   'role': 'scientific-officer###201222588E'},\n",
       "  {'id': 'idref08471591X',\n",
       "   'first_name': 'ARVEILER',\n",
       "   'last_name': 'Dominique',\n",
       "   'role': 'scientific-officer###e1S12'},\n",
       "  {'id': 'idref060829419',\n",
       "   'first_name': 'EILSTEIN',\n",
       "   'last_name': 'Daniel',\n",
       "   'role': 'scientific-officer###X3DyA'}]}"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dict_row=df_projets.iloc[0,:].to_dict()\n",
    "dict_row2={k:v for k,v in list(dict_row.items()) if ((str(v)!='nan')&(str(v)!='NaN')&(str(v)!='None')&(str(v)!='x')&(str(v)!='[]'))}\n",
    "dict_row2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3040b95d",
   "metadata": {},
   "outputs": [],
   "source": [
    "#envoi\n",
    "err=[]\n",
    "for i,row in df_projets.iterrows():\n",
    "    dict_row=row.to_dict()\n",
    "    dict_row2={k:v for k,v in list(dict_row.items()) if ((str(v)!='nan')&(str(v)!='NaN')&(str(v)!='None')&(str(v)!='x')&(str(v)!='[]'))}\n",
    "    try:\n",
    "       r=requests.post('http://185.161.45.213/projects/projects', json = dict_row2, headers={\"Authorization\":Authorization})\n",
    "       res= r.json()\n",
    "       if res.get('status')=='ERR':\n",
    "           err.append(res)\n",
    "           if res.get('error').get('code')!=422:\n",
    "               print(err)\n",
    "               pp.pprint(err)\n",
    "    except Exception as e:\n",
    "        pp.pprint(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "23a56567",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.Series([x.get('issues').get('id') for x in err]).drop_duplicates().tolist() "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff7e90ab",
   "metadata": {},
   "source": [
    "pour mettre à jour"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "51462f6b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "page 1\n",
      "k 0\n",
      "k 1\n",
      "k 2\n",
      "k 3\n",
      "k 4\n",
      "k 5\n",
      "k 6\n",
      "k 7\n",
      "k 8\n",
      "k 9\n",
      "k 10\n",
      "k 11\n",
      "k 12\n",
      "k 13\n",
      "k 14\n",
      "k 15\n",
      "k 16\n",
      "k 17\n",
      "k 18\n",
      "k 19\n",
      "k 20\n",
      "k 21\n",
      "k 22\n",
      "k 23\n",
      "k 24\n",
      "k 25\n",
      "k 26\n",
      "k 27\n",
      "k 28\n",
      "k 29\n",
      "k 30\n",
      "k 31\n",
      "k 32\n",
      "k 33\n",
      "k 34\n",
      "k 35\n",
      "k 36\n",
      "k 37\n",
      "k 38\n",
      "k 39\n",
      "k 40\n",
      "k 41\n",
      "k 42\n",
      "k 43\n",
      "k 44\n",
      "k 45\n",
      "k 46\n",
      "k 47\n",
      "k 48\n",
      "k 49\n",
      "k 50\n",
      "k 51\n",
      "k 52\n",
      "k 53\n",
      "k 54\n",
      "k 55\n",
      "k 56\n",
      "k 57\n",
      "k 58\n",
      "k 59\n",
      "k 60\n",
      "k 61\n",
      "k 62\n",
      "k 63\n",
      "k 64\n",
      "k 65\n",
      "k 66\n",
      "k 67\n",
      "k 68\n",
      "k 69\n",
      "k 70\n",
      "k 71\n",
      "k 72\n",
      "k 73\n",
      "k 74\n",
      "k 75\n",
      "k 76\n",
      "k 77\n",
      "k 78\n",
      "k 79\n",
      "k 80\n",
      "k 81\n",
      "k 82\n",
      "k 83\n",
      "k 84\n",
      "k 85\n",
      "k 86\n",
      "k 87\n",
      "k 88\n",
      "k 89\n",
      "k 90\n",
      "k 91\n",
      "k 92\n",
      "k 93\n",
      "k 94\n",
      "k 95\n",
      "k 96\n",
      "k 97\n",
      "k 98\n",
      "k 99\n",
      "k 100\n",
      "k 101\n",
      "k 102\n",
      "k 103\n",
      "k 104\n",
      "k 105\n",
      "k 106\n",
      "k 107\n",
      "k 108\n",
      "k 109\n",
      "k 110\n",
      "k 111\n",
      "k 112\n",
      "k 113\n",
      "k 114\n",
      "k 115\n",
      "k 116\n",
      "k 117\n",
      "k 118\n",
      "k 119\n",
      "k 120\n",
      "k 121\n",
      "k 122\n",
      "k 123\n",
      "k 124\n",
      "k 125\n",
      "k 126\n",
      "k 127\n",
      "k 128\n",
      "k 129\n",
      "k 130\n",
      "k 131\n",
      "k 132\n",
      "k 133\n",
      "k 134\n",
      "k 135\n",
      "k 136\n",
      "k 137\n",
      "k 138\n",
      "k 139\n",
      "k 140\n",
      "k 141\n",
      "k 142\n",
      "k 143\n",
      "k 144\n",
      "k 145\n",
      "k 146\n",
      "k 147\n",
      "k 148\n",
      "k 149\n",
      "k 150\n",
      "k 151\n",
      "k 152\n",
      "k 153\n",
      "k 154\n",
      "k 155\n",
      "k 156\n",
      "k 157\n",
      "k 158\n",
      "k 159\n",
      "k 160\n",
      "k 161\n",
      "k 162\n",
      "k 163\n",
      "k 164\n",
      "k 165\n",
      "k 166\n",
      "k 167\n",
      "k 168\n",
      "k 169\n",
      "k 170\n",
      "k 171\n",
      "k 172\n",
      "k 173\n",
      "k 174\n",
      "k 175\n",
      "k 176\n",
      "k 177\n",
      "k 178\n",
      "k 179\n",
      "k 180\n",
      "k 181\n",
      "k 182\n",
      "k 183\n",
      "k 184\n",
      "k 185\n",
      "k 186\n",
      "k 187\n",
      "k 188\n",
      "k 189\n",
      "k 190\n",
      "k 191\n",
      "k 192\n",
      "k 193\n",
      "k 194\n",
      "k 195\n",
      "k 196\n",
      "k 197\n",
      "k 198\n",
      "k 199\n",
      "k 200\n",
      "k 201\n",
      "k 202\n",
      "k 203\n",
      "k 204\n",
      "k 205\n",
      "k 206\n",
      "k 207\n",
      "k 208\n",
      "k 209\n",
      "k 210\n",
      "k 211\n",
      "k 212\n",
      "k 213\n",
      "k 214\n",
      "k 215\n",
      "k 216\n",
      "k 217\n",
      "k 218\n",
      "k 219\n",
      "k 220\n",
      "k 221\n",
      "k 222\n",
      "k 223\n",
      "k 224\n",
      "k 225\n",
      "k 226\n",
      "k 227\n",
      "k 228\n",
      "k 229\n",
      "k 230\n",
      "k 231\n",
      "k 232\n",
      "k 233\n",
      "k 234\n",
      "k 235\n",
      "k 236\n",
      "k 237\n",
      "k 238\n",
      "k 239\n",
      "k 240\n",
      "k 241\n",
      "k 242\n",
      "k 243\n",
      "k 244\n",
      "k 245\n",
      "k 246\n",
      "k 247\n",
      "k 248\n",
      "k 249\n",
      "k 250\n",
      "k 251\n",
      "k 252\n",
      "k 253\n",
      "k 254\n",
      "k 255\n",
      "k 256\n",
      "k 257\n",
      "k 258\n",
      "k 259\n",
      "k 260\n",
      "k 261\n",
      "k 262\n",
      "k 263\n",
      "k 264\n",
      "k 265\n",
      "k 266\n",
      "k 267\n",
      "k 268\n",
      "k 269\n",
      "k 270\n",
      "k 271\n",
      "k 272\n",
      "k 273\n",
      "k 274\n",
      "k 275\n",
      "k 276\n",
      "k 277\n",
      "k 278\n",
      "k 279\n",
      "k 280\n",
      "k 281\n",
      "k 282\n",
      "k 283\n",
      "k 284\n",
      "k 285\n",
      "k 286\n",
      "k 287\n",
      "k 288\n",
      "k 289\n",
      "k 290\n",
      "k 291\n",
      "k 292\n",
      "k 293\n",
      "k 294\n",
      "k 295\n",
      "k 296\n",
      "k 297\n",
      "k 298\n",
      "k 299\n",
      "k 300\n",
      "k 301\n",
      "k 302\n",
      "k 303\n",
      "k 304\n",
      "k 305\n",
      "k 306\n",
      "k 307\n",
      "k 308\n",
      "k 309\n",
      "k 310\n",
      "k 311\n",
      "k 312\n",
      "k 313\n",
      "k 314\n",
      "k 315\n",
      "k 316\n",
      "k 317\n",
      "k 318\n",
      "k 319\n",
      "k 320\n",
      "k 321\n",
      "k 322\n",
      "k 323\n",
      "k 324\n",
      "k 325\n",
      "k 326\n",
      "k 327\n",
      "k 328\n",
      "k 329\n",
      "k 330\n",
      "k 331\n",
      "k 332\n",
      "k 333\n",
      "k 334\n",
      "k 335\n",
      "k 336\n",
      "k 337\n",
      "k 338\n",
      "k 339\n",
      "k 340\n",
      "k 341\n",
      "k 342\n",
      "k 343\n",
      "k 344\n",
      "k 345\n",
      "k 346\n",
      "k 347\n",
      "k 348\n",
      "k 349\n",
      "k 350\n",
      "k 351\n",
      "k 352\n",
      "k 353\n",
      "k 354\n",
      "k 355\n",
      "k 356\n",
      "k 357\n",
      "k 358\n",
      "k 359\n",
      "k 360\n",
      "k 361\n",
      "k 362\n",
      "k 363\n",
      "k 364\n",
      "k 365\n",
      "k 366\n",
      "k 367\n",
      "k 368\n",
      "k 369\n",
      "k 370\n",
      "k 371\n",
      "k 372\n",
      "k 373\n",
      "k 374\n",
      "k 375\n",
      "k 376\n",
      "k 377\n",
      "k 378\n",
      "k 379\n",
      "k 380\n",
      "k 381\n",
      "k 382\n",
      "k 383\n",
      "k 384\n",
      "k 385\n",
      "k 386\n",
      "k 387\n",
      "k 388\n",
      "k 389\n",
      "k 390\n",
      "k 391\n",
      "k 392\n",
      "k 393\n",
      "k 394\n",
      "k 395\n",
      "k 396\n",
      "k 397\n",
      "k 398\n",
      "k 399\n",
      "k 400\n",
      "k 401\n",
      "k 402\n",
      "k 403\n",
      "k 404\n",
      "k 405\n",
      "k 406\n",
      "k 407\n",
      "k 408\n",
      "k 409\n",
      "k 410\n",
      "k 411\n",
      "k 412\n",
      "k 413\n",
      "k 414\n",
      "k 415\n",
      "k 416\n",
      "k 417\n",
      "k 418\n",
      "k 419\n",
      "k 420\n",
      "k 421\n",
      "k 422\n",
      "k 423\n",
      "k 424\n",
      "k 425\n",
      "k 426\n",
      "k 427\n",
      "k 428\n",
      "k 429\n",
      "k 430\n",
      "k 431\n",
      "k 432\n",
      "k 433\n",
      "k 434\n",
      "k 435\n",
      "k 436\n",
      "k 437\n",
      "k 438\n",
      "k 439\n",
      "k 440\n",
      "k 441\n",
      "k 442\n",
      "k 443\n",
      "k 444\n",
      "k 445\n",
      "k 446\n",
      "k 447\n",
      "k 448\n",
      "k 449\n",
      "k 450\n",
      "k 451\n",
      "k 452\n",
      "k 453\n",
      "k 454\n",
      "k 455\n",
      "k 456\n",
      "k 457\n",
      "k 458\n",
      "k 459\n",
      "k 460\n",
      "k 461\n",
      "k 462\n",
      "k 463\n",
      "k 464\n",
      "k 465\n",
      "k 466\n",
      "k 467\n",
      "k 468\n",
      "k 469\n",
      "k 470\n",
      "k 471\n",
      "k 472\n",
      "k 473\n",
      "k 474\n",
      "k 475\n",
      "k 476\n",
      "k 477\n",
      "k 478\n",
      "k 479\n",
      "k 480\n",
      "k 481\n",
      "k 482\n",
      "k 483\n",
      "k 484\n",
      "k 485\n",
      "k 486\n",
      "k 487\n",
      "k 488\n",
      "k 489\n",
      "k 490\n",
      "k 491\n",
      "k 492\n",
      "k 493\n",
      "k 494\n",
      "k 495\n",
      "k 496\n",
      "k 497\n",
      "k 498\n",
      "k 499\n",
      "page 2\n",
      "k 0\n",
      "k 1\n",
      "k 2\n",
      "k 3\n",
      "k 4\n",
      "k 5\n",
      "k 6\n",
      "k 7\n",
      "k 8\n",
      "k 9\n",
      "k 10\n",
      "k 11\n",
      "k 12\n",
      "k 13\n",
      "k 14\n",
      "k 15\n",
      "k 16\n",
      "k 17\n",
      "k 18\n",
      "k 19\n",
      "k 20\n",
      "k 21\n",
      "k 22\n",
      "k 23\n",
      "k 24\n",
      "k 25\n",
      "k 26\n",
      "k 27\n",
      "k 28\n",
      "k 29\n",
      "k 30\n",
      "k 31\n",
      "k 32\n",
      "k 33\n",
      "k 34\n",
      "k 35\n",
      "k 36\n",
      "k 37\n",
      "k 38\n",
      "k 39\n",
      "k 40\n",
      "k 41\n",
      "k 42\n",
      "k 43\n",
      "k 44\n",
      "k 45\n",
      "k 46\n",
      "k 47\n",
      "k 48\n",
      "k 49\n",
      "k 50\n",
      "k 51\n",
      "k 52\n",
      "k 53\n",
      "k 54\n",
      "k 55\n",
      "k 56\n",
      "k 57\n",
      "k 58\n",
      "k 59\n",
      "k 60\n",
      "k 61\n",
      "k 62\n",
      "k 63\n",
      "k 64\n",
      "k 65\n",
      "k 66\n",
      "k 67\n",
      "k 68\n",
      "k 69\n",
      "k 70\n",
      "k 71\n",
      "k 72\n",
      "k 73\n",
      "k 74\n",
      "k 75\n",
      "k 76\n",
      "k 77\n",
      "k 78\n",
      "k 79\n",
      "k 80\n",
      "k 81\n",
      "k 82\n",
      "k 83\n",
      "k 84\n",
      "k 85\n",
      "k 86\n",
      "k 87\n",
      "k 88\n",
      "k 89\n",
      "k 90\n",
      "k 91\n"
     ]
    }
   ],
   "source": [
    "nbr_page=int(requests.get('http://185.161.45.213/projects/projects?where={\"type\":\"ANSES\"}&projection={\"id\":1}&max_results=500&page=1', headers={\"Authorization\":Authorization}).json()['hrefs']['last']['href'].split('page=')[1])\n",
    "\n",
    "list_ids=[]\n",
    "for i in range(1,nbr_page+1):\n",
    "    print(\"page\",i)\n",
    "    page=requests.get('http://185.161.45.213/projects/projects?where={\"type\":\"ANSES\"}&projection={\"id\":1}&max_results=500'+f\"&page={i}\", headers={\"Authorization\":Authorization}).json()\n",
    "    for k in range(len(page['data'])):\n",
    "        print(\"k\",k)\n",
    "        list_ids.append(page['data'][k]['id'])\n",
    "    \n",
    "projets_a_ajouter=[x for x in list(df_projets['id']) if x not in list_ids]\n",
    "\n",
    "projets_a_retirer=[x for x in list_ids if x not in list(df_projets['id'])]\n",
    "\n",
    "df_projets = df_projets[df_projets['id'].apply(lambda x: x in projets_a_ajouter)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "da0c308a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(projets_a_ajouter)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "d4747378",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_projets[df_projets['id'].apply(lambda x: x in projets_a_ajouter)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8cac5958",
   "metadata": {},
   "outputs": [],
   "source": [
    "dict_row=df_projets.iloc[0,:].to_dict()\n",
    "dict_row2={k:v for k,v in list(dict_row.items()) if ((str(v)!='nan')&(str(v)!='NaN')&(str(v)!='None')&(str(v)!='x')&(str(v)!='[]'))}\n",
    "dict_row2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "611054ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "err=[]\n",
    "for i,row in df_projets.iterrows():\n",
    "    dict_row=row.to_dict()\n",
    "    dict_row2={k:v for k,v in list(dict_row.items()) if ((str(v)!='nan')&(str(v)!='NaN')&(str(v)!='None')&(str(v)!='x')&(str(v)!='[]'))}\n",
    "    try:\n",
    "       r=requests.post('http://185.161.45.213/projects/projects', json = dict_row2, headers={\"Authorization\":Authorization})\n",
    "       res= r.json()\n",
    "       if res.get('status')=='ERR':\n",
    "           err.append(res)\n",
    "           if res.get('error').get('code')!=422:\n",
    "               print(err)\n",
    "               pp.pprint(err)\n",
    "    except Exception as e:\n",
    "        pp.pprint(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d86e1a09",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(projets_a_ajouter)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "28038ffa",
   "metadata": {},
   "source": [
    "Modifications"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4858669e",
   "metadata": {},
   "outputs": [],
   "source": [
    "nbr_page=int(requests.get('http://185.161.45.213/projects/projects?where={\"type\":\"ANR\"}&projection={\"id\":1,\"year\":1}&max_results=500'+f\"&page={1}\", headers={\"Authorization\":Authorization}).json()['hrefs']['last']['href'].split('page=')[1])\n",
    "nbr_page"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "73786090",
   "metadata": {},
   "outputs": [],
   "source": [
    "list_ids=[]\n",
    "for i in range(1,nbr_page+1):\n",
    "    print(\"page\",i)\n",
    "    page=requests.get('http://185.161.45.213/projects/projects?where={%22type%22:%22ANR%22}&projection={%22id%22:1,%22year%22:1}&max_results=500'+f\"&page={i}\", headers={\"Authorization\":Authorization}).json()\n",
    "    for k in range(len(page['data'])):\n",
    "        list_ids.append(page['data'][k]['year'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aacd7b75",
   "metadata": {},
   "outputs": [],
   "source": [
    "max(list_ids)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "227532a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_projets = df_projets[df_projets['id'].apply(lambda x: x in list_ids)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5a0578c8",
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"\n",
    "en principe, il faut que je mette à jour tous les projets \n",
    "qui ne sont pas clos avec les titres résumés et persons, mais \n",
    "peut etre qu'on peut mettre à jour automatiquement les projets\n",
    "des 3 dernières années ? \n",
    "\n",
    "\"\"\"\n",
    "\n",
    "err=[]\n",
    "for id in df_projets.iterrows() :\n",
    "    url = f\"http://185.161.45.213/projects/projects/{id}\"\n",
    "    project=requests.get(url, headers={'Authorization': Authorization}).json()\n",
    "    head = {\"Authorization\": Authorization, \"If-Match\": project['etag'], \"Content-Type\": \"application/json\"}\n",
    "\n",
    "    r = requests.patch(url, json = {\"year\": 2023}, headers=head)\n",
    "    res= r.json()\n",
    "    if res.get('status')=='ERR':\n",
    "        err.append(res)\n",
    "        if res.get('error').get('code')!=422:\n",
    "            print(err)\n",
    "            pp.pprint(err)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a41e50a5",
   "metadata": {},
   "source": [
    "# ENVOI DES PARTENAIRES SUR SCANR"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "11136681",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_partenaires=pd.read_json(f\"./DATA/{source}/df_partenaires_id_structures.json\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a42766a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_partenaires.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "id": "c68b4f62",
   "metadata": {},
   "outputs": [],
   "source": [
    "### ATTENTION, vérifier que les projets sirano sont dans des structures françaises\n",
    "if source=='IRESP':\n",
    "    df_partenaires[sources[source]['pays']]=df_partenaires.loc[:,sources[source]['ville']].apply(lambda x: x.split('(')[1].replace(')','') if x.find('(')>=0 else 'France')\n",
    "    df_partenaires.loc[:,sources[source]['ville']]=df_partenaires.loc[:,sources[source]['ville']].apply(lambda x: x.split('(')[0] if x.find('(')>=0 else x)\n",
    "  \n",
    "df_partenaires['address']=df_partenaires.apply(lambda row: address(row,sources[source]['pays'],sources[source]['ville'],source), axis=1)\n",
    "df_partenaires.loc[:,'id_structure']=df_partenaires.loc[:,'id_structure'].apply(lambda x: x[0] if isinstance(x,list) else x )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cf09f899",
   "metadata": {},
   "outputs": [],
   "source": [
    "if source in ['ANSES','SIRANO']:\n",
    "    df_partenaires['id']=df_partenaires.apply(lambda row: f\"{row[sources[source]['code_projet']]}-{row[{sources[source]['nom_structure']}+'2']}-{row[sources[source]['nom']]}-{row[sources[source]['prenom']]}\" , axis=1)\n",
    "if source =='REG_IDF':\n",
    "    df_partenaires['id']=df_partenaires.apply(lambda row: f\"{row[sources[source]['code_projet']]}-{row[str(sources[source]['nom_structure'])+'2']}-{row['entite_role']}\" , axis=1)\n",
    "df_partenaires['address']=df_partenaires.apply(lambda row: address(row,sources[source]['pays'],sources[source]['ville'],source), axis=1)\n",
    "df_partenaires=df_partenaires.rename(columns={sources[source]['nom_structure']: 'name', sources[source]['code_projet']: 'project_id', 'id_structure':'participant_id','Projet.Partenaire.Code_Decision_ANR':'id'})\n",
    "df_partenaires=df_partenaires[['name','id','project_id','participant_id','address']]\n",
    "df_partenaires['project_type']=source\n",
    "df_partenaires['participant_id']=df_partenaires.loc[:,'participant_id'].apply(lambda x: str(x[0]).replace('.0','') if isinstance(x,list) else str(x).split(';')[0].replace('.0',''))\n",
    "df_partenaires=df_partenaires[['id','project_id', 'project_type', 'participant_id', 'name','address']]\n",
    "df_partenaires['name'] = df_partenaires['name'].astype(str)\n",
    "df_partenaires"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d3495663",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_partenaires[df_partenaires.duplicated(subset=['id'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dae2cc5b",
   "metadata": {},
   "outputs": [],
   "source": [
    "dict_row=df_partenaires.iloc[1,:].to_dict()\n",
    "dict_row2={k:v for k,v in list(dict_row.items()) if ((str(v)!='nan')&(str(v)!='NaN')&(str(v)!='None')&(str(v)!='x'))}\n",
    "dict_row2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e2f8d3b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(df_partenaires)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b489768",
   "metadata": {},
   "outputs": [],
   "source": [
    "err=[]\n",
    "for i,row in df_partenaires.iterrows():\n",
    "    dict_row=row.to_dict()\n",
    "    dict_row2={k:v for k,v in list(dict_row.items()) if ((str(v)!='nan')&(str(v)!='NaN')&(str(v)!='None')&(str(v)!='x'))}\n",
    "    try:\n",
    "       r=requests.post('http://185.161.45.213/projects/participations', json = dict_row2, headers={\"Authorization\":Authorization})\n",
    "       res= r.json()\n",
    "       if res.get('status')=='ERR':\n",
    "           print(i)\n",
    "           err.append(res)\n",
    "           if res.get('error').get('code')!=422:\n",
    "               print(err)\n",
    "               pp.pprint(err)\n",
    "    except Exception as e:\n",
    "        pp.pprint(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e2ea3fe3",
   "metadata": {},
   "source": [
    "Mise à jour"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "137c389e",
   "metadata": {},
   "outputs": [],
   "source": [
    "nbr_page=int(requests.get('http://185.161.45.213/projects/participations?where={\"project_type\":\"ANR\"}&projection={\"id\":1}&max_results=500&page=1', headers={\"Authorization\":Authorization}).json()['hrefs']['last']['href'].split('page=')[1])\n",
    "\n",
    "list_ids=[]\n",
    "for i in range(1,nbr_page+1):\n",
    "    print(\"page\",i)\n",
    "    page=requests.get('http://185.161.45.213/projects/participations?where={\"project_type\":\"ANR\"}&projection={\"id\":1}&max_results=500'+f\"&page={i}\", headers={\"Authorization\":Authorization}).json()\n",
    "    for k in range(len(page['data'])):\n",
    "        print(\"k\",k)\n",
    "        list_ids.append(page['data'][k]['id'])\n",
    "    \n",
    "projets_a_ajouter=[x for x in list(df_partenaires['id'].drop_duplicates()) if x not in list(pd.Series(list_ids).drop_duplicates())]\n",
    "\n",
    "projets_a_retirer=[x for x in list_ids if x not in list(df_partenaires['id'])]\n",
    "\n",
    "df_partenaires = df_partenaires[df_partenaires['id'].apply(lambda x: x in projets_a_ajouter)]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c0a930be",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(projets_a_ajouter)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "id": "a8b50023",
   "metadata": {},
   "outputs": [],
   "source": [
    "err=[]\n",
    "for i,row in df_partenaires.iterrows():\n",
    "    dict_row=row.to_dict()\n",
    "    dict_row2={k:v for k,v in list(dict_row.items()) if ((str(v)!='nan')&(str(v)!='NaN')&(str(v)!='None')&(str(v)!='x'))}\n",
    "    try:\n",
    "       r=requests.post('http://185.161.45.213/projects/participations', json = dict_row2, headers={\"Authorization\":Authorization})\n",
    "       res= r.json()\n",
    "       if res.get('status')=='ERR':\n",
    "           print(i)\n",
    "           err.append(res)\n",
    "           if res.get('error').get('code')!=422:\n",
    "               print(err)\n",
    "               pp.pprint(err)\n",
    "    except Exception as e:\n",
    "        pp.pprint(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b66d0830",
   "metadata": {},
   "source": [
    "Modifications"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "041def3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "nbr_page=int(requests.get('http://185.161.45.213/projects/participations?where={\"project_type\":\"ANR\",\"participant_id\":{\"$exists\":false}}&projection={\"id\":1}&max_results=500'+f\"&page={1}\", headers={\"Authorization\":Authorization}).json()['hrefs']['last']['href'].split('page=')[1])\n",
    "\n",
    "list_ids=[]\n",
    "for i in range(1,nbr_page+1):\n",
    "    print(\"page\",i)\n",
    "    page=requests.get('http://185.161.45.213/projects/participations?where={\"project_type\":\"ANR\",\"participant_id\":{\"$exists\":false}}&projection={\"id\":1}&max_results=500'+f\"&page={i}\", headers={\"Authorization\":Authorization}).json()\n",
    "    for k in range(len(page['data'])):\n",
    "        print(\"k\",k)\n",
    "        list_ids.append(page['data'][k]['id'])\n",
    "\n",
    "df_partenaires = df_partenaires[df_partenaires['id'].apply(lambda x: x in list_ids)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ae447f6e",
   "metadata": {},
   "outputs": [],
   "source": [
    "len(list_ids)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "id": "8c3dd659",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_partenaires=df_partenaires[(df_partenaires.participant_id!='x') & (df_partenaires.participant_id!='None')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e8a62be8",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_partenaires"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0eef494c",
   "metadata": {},
   "outputs": [],
   "source": [
    "err=[]\n",
    "for i,row in df_partenaires.iterrows() :\n",
    "    print(i)\n",
    "    id=row['id']\n",
    "    url = f\"http://185.161.45.213/projects/participations/{id}\"\n",
    "    project=requests.get(url, headers={'Authorization': Authorization}).json()\n",
    "    head = {\"Authorization\": Authorization, \"If-Match\": project['etag'], \"Content-Type\": \"application/json\"}\n",
    "\n",
    "    r = requests.patch(url, json = {\"participant_id\": row['participant_id']}, headers=head)\n",
    "    res= r.json()\n",
    "    if res.get('status')=='ERR':\n",
    "        err.append(res)\n",
    "        if res.get('error').get('code')!=422:\n",
    "            print(err)\n",
    "            pp.pprint(err)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ab8add7",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.Series([x.get('issues').get('id')[25:] for x in err]).drop_duplicates().tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14b25fb0",
   "metadata": {},
   "source": [
    "Faire un nouveau fichier avec toutes les structures manquantes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "41dd26a6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "73cf7e77",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7a22e4ca",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0a973ce1",
   "metadata": {},
   "outputs": [],
   "source": [
    "struct_manq=pd.read_excel('scanr_partenaires_non_identifies.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "ae5cf822",
   "metadata": {},
   "outputs": [],
   "source": [
    "nom1=struct_manq[struct_manq.New=='IRESP'].drop_duplicates(subset='Nom').Nom"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "eab48e23",
   "metadata": {},
   "outputs": [],
   "source": [
    "iresp_actuel=pd.read_excel('./missing_ids_structures/partenaires_non_identifies_IRESP.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "72f4933c",
   "metadata": {},
   "outputs": [],
   "source": [
    "nom2=iresp_actuel['Nom_equipe']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a2a772bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.Series(list(nom1)+list(nom2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c28cc4ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.Series(list(nom1)+list(nom2)).drop_duplicates()"
   ]
  }
 ],
 "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.9.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}