test.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "import requests, pandas as pd\n",
    "from config_path import PATH_SOURCE, PATH_CLEAN, PATH\n",
    "from step3_entities.references import *\n",
    "from step3_entities.merge_referentiels import *\n",
    "from step3_entities.categories import *\n",
    "from step3_entities.ID_getSourceRef import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "### FP7\n",
      "\n",
      "### LOADING REF_SOURCE\n",
      "- size of ref_source : 503464\n",
      "### 2d - REF_SOURCE -> REF\n",
      "- size remplacement pic: 445\n",
      "- longueur de ref:58605\n",
      "- nb id: 58520\n",
      "- nb id after fill: 58520\n",
      "size _FP7 load: 730185\n",
      "Index(['project_id', 'participant_order', 'role', 'generalPic',\n",
      "       'participant_type_code', 'legalName', 'businessName', 'countryCode',\n",
      "       'website', 'global_costs', 'funding', 'status.x', 'ADRESS', 'city',\n",
      "       'post_code', 'nutsCode', 'pme', 'contact_role', 'title_name',\n",
      "       'last_name', 'first_name', 'stage', 'CD_PART_PIC', 'nom',\n",
      "       'countryCode_parent', 'vat_id', 'country_code_mapping', 'country_code',\n",
      "       'id', 'ZONAGE', 'participant_id', 'call_id', 'call_deadline',\n",
      "       'submission_date', 'instrument', 'call_year', 'status_code', 'acronym',\n",
      "       'abstract', 'title', 'number_involved', 'cost_total', 'eu_reqrec_grant',\n",
      "       'free_keywords', 'start_date', 'signature_date', 'end_date', 'duration',\n",
      "       'pilier', 'prog_abbr', 'prog_lib', 'area_abbr', 'area_lib',\n",
      "       'panel_code', 'panel_name'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "print(\"\\n### FP7\")\n",
    "def call_api():\n",
    "    call=pd.read_json(open(f\"data_files/FP7_calls.json\", 'r+', encoding='utf-8'))\n",
    "    call = pd.DataFrame(call)\n",
    "    call['call_budget'] = call['call_budget'].str.replace(',', '').astype('float')\n",
    "    return call\n",
    "call=call_api()\n",
    "\n",
    "def ref_select(FP):\n",
    "    ref_source = ref_source_load('ref')\n",
    "    # traitement ref select le FP, id non null ou/et ZONAGE non null\n",
    "    ref = ref_source_2d_select(ref_source, FP)\n",
    "    return ref\n",
    "ref, genPic_to_new=ref_select('FP7')\n",
    "\n",
    "def FP7_load():\n",
    "    FP7_PATH=f'{PATH}FP7/2022/'\n",
    "    _FP7 = pd.read_pickle(f\"{FP7_PATH}FP7_data.pkl\")\n",
    "    _FP7.rename(columns={'name_source':'legalName', 'acronym_source':'businessName'}, inplace=True)\n",
    "    print(f\"size _FP7 load: {len(_FP7)}\\n{_FP7.columns}\")\n",
    "    return _FP7\n",
    "_FP7=FP7_load()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "- size _FP7 after clean status: 730185, size with id: 414064\n",
      "- size _FP7 sans country_code: 61\n",
      "- size _FP7 with country: 730185, 45263341012.4\n",
      "- size de p: 294912\n",
      "- size p1 pic+cc: 54253\n",
      "- size p2 pic cc_parent: 0\n",
      "1 - A faire si possible, vérifier pourquoi des participations avec pic identiques ont un id ou pas nb pic: 9\n",
      "2 - size de new p: 294912, cols: Index(['generalPic', 'country_code_mapping', 'country_code', 'id',\n",
      "       'id_secondaire', 'ZONAGE', '_merge'],\n",
      "      dtype='object')\n",
      "- size _FP7 with ref: 730185, size FP7: 730185,  size with id: 431560\n",
      "country_code null        country_code_mapping country_name_mapping\n",
      "3131                    ZOE                  NaN\n",
      "43174                   GUF                  NaN\n",
      "141266                  SGS                  NaN\n",
      "423645                  IOT                  NaN\n",
      "size FP7 with country assoc: 730185,\n",
      "cols: Index(['project_id', 'participant_order', 'role', 'generalPic',\n",
      "       'participant_type_code', 'legalName', 'businessName', 'countryCode',\n",
      "       'website', 'global_costs', 'funding', 'status.x', 'ADRESS', 'city',\n",
      "       'post_code', 'nutsCode', 'pme', 'contact_role', 'title_name',\n",
      "       'last_name', 'first_name', 'stage', 'CD_PART_PIC', 'nom',\n",
      "       'countryCode_parent', 'vat_id', 'country_code_mapping',\n",
      "       'participant_id', 'call_id', 'call_deadline', 'submission_date',\n",
      "       'instrument', 'call_year', 'status_code', 'acronym', 'abstract',\n",
      "       'title', 'number_involved', 'cost_total', 'eu_reqrec_grant',\n",
      "       'free_keywords', 'start_date', 'signature_date', 'end_date', 'duration',\n",
      "       'pilier', 'prog_abbr', 'prog_lib', 'area_abbr', 'area_lib',\n",
      "       'panel_code', 'panel_name', 'coordination_number', 'id_secondaire',\n",
      "       'id', 'ZONAGE', 'country_name_mapping', 'country_code',\n",
      "       'country_name_en', 'country_association_code',\n",
      "       'country_association_name_en', 'country_group_association_code',\n",
      "       'country_group_association_name_en',\n",
      "       'country_group_association_name_fr', 'country_name_fr', 'article1',\n",
      "       'article2'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "country = pd.read_csv(f\"{PATH_SOURCE}H2020/country_current.csv\", sep=';', encoding='utf-8')\n",
    "def FP7_cleaning(_FP7, country):\n",
    "    _FP7 = _FP7.loc[~_FP7.status_code.isin(['INELIGIBLE','WITHDRAWN'])]\n",
    "    _FP7.loc[_FP7.status_code=='Project Closed', 'status_code'] = 'CLOSED'\n",
    "    _FP7.loc[_FP7.status_code=='Project Terminated', 'status_code'] = 'TERMINATED'\n",
    "\n",
    "    _FP7.loc[_FP7.participant_type_code=='N/A', 'participant_type_code'] = 'NA'\n",
    "    _FP7['role'] = _FP7['role'].str.lower()\n",
    "    _FP7.loc[_FP7.role=='participant', 'role'] = 'partner'\n",
    "    _FP7['coordination_number']=np.where(_FP7['role']=='coordinator', 1, 0)\n",
    "    _FP7.loc[(_FP7.generalPic=='998133396')&(_FP7.countryCode=='ZZ'), 'country_code_mapping'] = 'USA' # bristol meyer\n",
    "    print(f\"- size _FP7 after clean status: {len(_FP7)}, size with id: {len(_FP7.loc[~_FP7.id.isnull()])}\")\n",
    "    \n",
    "    zz = _FP7.loc[(_FP7.country_code_mapping=='ZZZ')]\n",
    "    print(f\"- size _FP7 sans country_code: {len(zz)}\")\n",
    "    zz = ref.loc[ref.generalPic.isin(zz.generalPic.unique())]\n",
    "    _FP7 = _FP7.merge(zz, how='left', on='generalPic', suffixes=['','_ref'])\n",
    "    for i in ['id', 'country_code_mapping', 'ZONAGE']:\n",
    "        _FP7.loc[~_FP7[f\"{i}_ref\"].isnull(), i] = _FP7[f\"{i}_ref\"]\n",
    "    _FP7 = _FP7.drop(_FP7.filter(regex='_ref$').columns, axis=1)\n",
    "    print(f\"- size _FP7 with country: {len(_FP7)}, {_FP7.loc[_FP7.stage=='successful', 'funding'].sum()}\")\n",
    "    \n",
    "    p = _FP7[['generalPic', 'country_code_mapping','country_code']].drop_duplicates()\n",
    "    print(f\"- size de p: {len(p)}\")\n",
    "    #lien part et ref\n",
    "    p = p.merge(ref, how='outer', on=['generalPic', 'country_code_mapping'], indicator=True).drop_duplicates()\n",
    "    p = p.loc[p._merge.isin(['both', 'left_only'])]\n",
    "    # print(f\"cols de p: {p.columns}\")\n",
    "\n",
    "    # p1 pic+ccm commun\n",
    "    p1 = p.loc[p['_merge']=='both'].drop(columns=['_merge', 'country_code'])\n",
    "    print(f\"- size p1 pic+cc: {len(p1)}\")\n",
    "\n",
    "    # p2 pic cc\n",
    "    p2 = (p.loc[p['_merge']=='left_only'].drop(columns=['_merge', 'id', 'ZONAGE'])\n",
    "        .merge(ref.rename(columns={'country_code_mapping':'country_code'}), \n",
    "                how='inner', on=['generalPic', 'country_code']).drop_duplicates()\n",
    "        .drop(columns='country_code'))\n",
    "    print(f\"- size p2 pic cc_parent: {len(p2)}\")\n",
    "\n",
    "    # acteurs sans identifiant dont le pic à plusieurs pays ou le pic certaines participations ont un identifiant et pas d'autres \n",
    "    p3 = (p.loc[p['_merge']=='left_only'].drop(columns=['_merge', 'country_code_mapping', 'id', 'ZONAGE'])\n",
    "        .merge(ref, how='inner', on=['generalPic']).drop_duplicates())\n",
    "    if not p3.empty:\n",
    "        print(f\"1 - A faire si possible, vérifier pourquoi des participations avec pic identiques ont un id ou pas nb pic: {len(p3.generalPic.unique())}\")\n",
    "\n",
    "    if 'p2' in globals() or 'p2' in locals():\n",
    "        p1 = pd.concat([p1,p2], ignore_index=True).drop_duplicates()\n",
    "        print(f\"2 - size de new p: {len(p)}, cols: {p.columns}\") \n",
    "\n",
    "    FP7 = (_FP7.drop(columns=['id', 'ZONAGE', 'country_code'])\n",
    "            .merge(p1[['generalPic', 'country_code_mapping', 'id', 'ZONAGE']], \n",
    "                how='left', on=['generalPic', 'country_code_mapping']))\n",
    "    print(f\"- size _FP7 with ref: {len(_FP7)}, size FP7: {len(FP7)},  size with id: {len(FP7.loc[~FP7.id.isnull()])}\")\n",
    "    \n",
    "    FP7 = FP7.merge(country[['country_code_mapping', 'country_name_mapping', 'country_code']].drop_duplicates(), how='left', on='country_code_mapping')\n",
    "    # FP7.loc[~FP7.ZONAGE.isnull(), 'country_code'] = FP7.ZONAGE\n",
    "    if any(FP7.country_code.isnull()):\n",
    "        print(f\"country_code null {FP7.loc[FP7.country_code.isnull(), ['country_code_mapping', 'country_name_mapping']].drop_duplicates()}\")\n",
    "        FP7.loc[FP7.country_code_mapping=='GUF', 'country_code'] = 'FRA'\n",
    "        FP7.loc[FP7.country_code_mapping=='GUF', 'country_name_mapping'] = 'French Guiana'\n",
    "        FP7.loc[FP7.country_code_mapping.isin(['SGS', 'IOT']), 'country_code'] = 'GBR'\n",
    "        FP7.loc[FP7.country_code_mapping=='IOT', 'country_name_mapping'] = 'British Indian Ocean Territory'\n",
    "        FP7.loc[FP7.country_code_mapping=='SGS', 'country_name_mapping'] = 'South Georgia and the South Sandwich Islands'\n",
    "\n",
    "    cc = country.drop(columns=['country_code_mapping', 'country_name_mapping', 'countryCode', 'countryCode_parent']).drop_duplicates()\n",
    "    FP7 = FP7.merge(cc, how='left', on='country_code')\n",
    "    FP7.loc[FP7.country_code_mapping=='ZOE', 'country_name_mapping'] = 'European organisations area'\n",
    "\n",
    "    FP7.loc[FP7.country_code_mapping=='ZOE', 'country_code'] = 'ZOE'\n",
    "    FP7.loc[FP7.country_code=='ZOE', 'country_name_fr'] = 'Union Européenne'\n",
    "    FP7.loc[FP7.country_code=='ZOE', 'country_name_en'] = 'European organisations area'\n",
    "\n",
    "    print(f\"size FP7 with country assoc: {len(FP7)},\\ncols: {FP7.columns}\")    \n",
    "    return FP7\n",
    "FP7=FP7_cleaning(_FP7, country)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "np.float64(7069381181.86)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "FP7.loc[(FP7.country_code=='DEU')&(FP7.stage=='successful')&(FP7.pilier!='EURATOM')].funding.sum()\n",
    "# FP7.pilier.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "## FP7 entities\n",
      "- size entities 50387\n",
      "2 - size entities si multi id -> entities_size_to_keep = 50605\n",
      "### merge ROR\n",
      "- End size entities_tmp+ror_info: 50605\n",
      "size entities_tmp after add ror_info: 50605, entities_size_to_keep: 50605\n",
      "### CATEGORY paysage\n",
      "### merge PAYSAGE\n",
      "- End size entities_tmp+paysage_info: 50605\n",
      "### merge SIRENE\n",
      "- first size sirene : 15655\n",
      "- size sirene : 15655\n",
      "1 - A vérifier -> liste des noms à traiter:\n",
      "       ens denom_us                                             nom_ul\n",
      "0     NaN     None           RESEAU DES BARS DES SCIENCES FRANCILIENS\n",
      "1     NaN     None                                          IM PROJET\n",
      "2     NaN     None                                           PHARNEXT\n",
      "3     NaN     None                       INSTITUT DE VEILLE SANITAIRE\n",
      "4     NaN     None                                         HEALTHGRID\n",
      "...   ...      ...                                                ...\n",
      "3055  NaN     None                                          AQUAFADAS\n",
      "3056  NaN     None  COMMUNAUTE DE COMMUNES DU CANTON DE SAINT LAUR...\n",
      "3057  NaN     None                             CELLECTIS THERAPEUTICS\n",
      "3058  NaN     None                               COOPERATION MARITIME\n",
      "3059  NaN     None                                           FILCLAIR\n",
      "\n",
      "[3060 rows x 3 columns]\n",
      "#####\n",
      "- End size entities_tmp+sirene: 50605\n",
      "2 - taille de entities_tmp avant groupe:50605\n",
      "- size entities_tmp after groupe 50605\n",
      "### sourcer les identifiants pour getInformations\n",
      "\n",
      "## category woven\n",
      "- categorization missing\n",
      "      source_id                                   entities_name  \\\n",
      "1997      siren                                             NaN   \n",
      "3549      siren                                             NaN   \n",
      "4391      siren                                             NaN   \n",
      "4689      siren                                             NaN   \n",
      "6102      siren                                             NaN   \n",
      "7693    paysage  Institut des sciences et technologies de Paris   \n",
      "9283      siren                                             NaN   \n",
      "16027     siren         Organisat. europeen pr protect. plantes   \n",
      "22630     siret                             The british council   \n",
      "23294     siret                                             NaN   \n",
      "47406     siret                             The british council   \n",
      "47802     siret                                             NaN   \n",
      "49783     siren                           Gendarmerie nationale   \n",
      "\n",
      "          entities_id  siren_cj paysage_category  \n",
      "1997        152000014       NaN              NaN  \n",
      "3549        326522580       NaN              NaN  \n",
      "4391        939723457       NaN              NaN  \n",
      "4689        448428608       NaN              NaN  \n",
      "6102        233100001       NaN              NaN  \n",
      "7693            Flp7H       NaN              NaN  \n",
      "9283        777675141       NaN              NaN  \n",
      "16027       784669269  ETAT_ETR              NaN  \n",
      "22630  77575081300047  ETAT_ETR              NaN  \n",
      "23294  30460379800022       NaN              NaN  \n",
      "47406  77575081300047  ETAT_ETR              NaN  \n",
      "47802  51133927700010       NaN              NaN  \n",
      "49783       786262410       NaN              NaN  \n",
      "- taille de df après cat: 50605\n"
     ]
    }
   ],
   "source": [
    "def FP7_entities(FP7, country):\n",
    "    print(\"\\n## FP7 entities\")\n",
    "    # part.country_code.unique()\n",
    "    entities = FP7.loc[~FP7.id.isnull(), ['generalPic','id', 'country_code_mapping']].drop_duplicates()\n",
    "    print(f\"- size entities {len(entities)}\")\n",
    "    if any(entities.id.str.contains(';')):\n",
    "        entities = entities.assign(id_extend=entities.id.str.split(';')).explode('id_extend')\n",
    "        entities.loc[(entities.id.str.contains(';', na=False))&(entities.id_extend.str.len()==14), 'id_extend'] = entities.loc[(entities.id.str.contains(';', na=False))&(entities.id_extend.str.len()==14)].id_extend.str[:9]\n",
    "        entities = entities.drop_duplicates()\n",
    "        entities_size_to_keep = len(entities)\n",
    "        print(f\"2 - size entities si multi id -> entities_size_to_keep = {entities_size_to_keep}\")\n",
    "\n",
    "    ror = pd.read_pickle(f\"{PATH_REF}ror_df.pkl\")\n",
    "    entities_tmp = merge_ror(entities, ror, country)\n",
    "    print(f\"size entities_tmp after add ror_info: {len(entities_tmp)}, entities_size_to_keep: {entities_size_to_keep}\")\n",
    "\n",
    "\n",
    "    # PAYSAGE\n",
    "    ### si besoin de charger paysage pickle\n",
    "    paysage = pd.read_pickle(f\"{PATH_REF}paysage_df.pkl\")\n",
    "    if any(paysage.groupby('id')['id_clean'].transform('count')>1):\n",
    "        print(f\"1 - paysage doublon oublié: {paysage[paysage.groupby('id')['id_clean'].transform('count')>1][['id', 'id_clean']].sort_values('id')}\")\n",
    "        paysage = paysage.loc[~((paysage.id_clean=='vey7g')&(paysage.id.str.contains('265100057', na=False)))]    \n",
    "    \n",
    "    paysage_category = pd.read_pickle(f\"{PATH_SOURCE}paysage_category.pkl\")\n",
    "    cat_filter = category_paysage(paysage_category)\n",
    "    entities_tmp = merge_paysage(entities_tmp, paysage, cat_filter)\n",
    "\n",
    "    sirene = pd.read_pickle(f\"{PATH_REF}sirene_df.pkl\")\n",
    "    entities_tmp = merge_sirene(entities_tmp, sirene)\n",
    "\n",
    "    # traitement des id identifiés mais sans referentiels liés\n",
    "    entities_tmp.loc[(entities_tmp.entities_id.isnull())&(~entities_tmp.id_extend.str.contains('-', na=False)), 'entities_id'] = entities_tmp['id_extend']\n",
    "\n",
    "    entities_tmp['siren']=entities_tmp.loc[entities_tmp.entities_id.str.contains('^[0-9]{9}$|^[0-9]{14}$', na=False)].entities_id.str[:9]\n",
    "    entities_tmp.loc[entities_tmp.siren.isnull(), 'siren']=entities_tmp.paysage_siren\n",
    "\n",
    "    #groupe\n",
    "\n",
    "    # recuperation tous les siren pour lien avec groupe -> creation var SIREN \n",
    "    entities_tmp.loc[~entities_tmp.siren.isnull(), \"siren\"] = entities_tmp.loc[~entities_tmp.siren.isnull(), \"siren\"].str.split().apply(set).str.join(\";\")\n",
    "\n",
    "    if any(entities_tmp.siren.str.contains(';', na=False)):\n",
    "        print(\"1 - ATTENTION faire code pour traiter deux siren différents -> ce qui serait bizarre qu'il y ait 2 siren\")\n",
    "    else:\n",
    "        ### si besoin de charger groupe\n",
    "        file_name = f\"{PATH_REF}H20_groupe.pkl\"\n",
    "        groupe = pd.read_pickle(file_name)\n",
    "        print(f\"2 - taille de entities_tmp avant groupe:{len(entities_tmp)}\")\n",
    "\n",
    "        entities_tmp=entities_tmp.merge(groupe, how='left', on='siren')\n",
    "\n",
    "        # entities_tmp.loc[~entities_tmp.groupe_id.isnull(), 'entities_id']= entities_tmp.groupe_id\n",
    "        # entities_tmp.loc[~entities_tmp.groupe_id.isnull(), 'entities_acronym'] = entities_tmp.groupe_acronym\n",
    "        # entities_tmp.loc[~entities_tmp.groupe_id.isnull(), 'entities_name'] = entities_tmp.groupe_name\n",
    "\n",
    "        # entities_tmp.loc[entities_tmp.entities_id.str.contains('gent', na=False), 'siren_cj'] = 'GE_ENT'\n",
    "        \n",
    "        # entities_tmp = entities_tmp.drop(['groupe_id','groupe_name','groupe_acronym'], axis=1).drop_duplicates()\n",
    "        print(f\"- size entities_tmp after groupe {len(entities_tmp)}\")\n",
    "\n",
    "    entities_tmp = entities_tmp.merge(get_source_ID(entities_tmp, 'entities_id'), how='left', on='entities_id')\n",
    "        # traitement catégorie\n",
    "    # entities_tmp = category_cleaning(entities_tmp, sirene)\n",
    "    entities_tmp = category_woven(entities_tmp, sirene)\n",
    "    entities_tmp = category_agreg(entities_tmp)\n",
    "    return  entities_tmp\n",
    "entities_tmp=FP7_entities(FP7, country)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "## FP7 calculation\n",
      "- size part before: 730185\n",
      "- size part before: 730796\n"
     ]
    }
   ],
   "source": [
    "print(\"\\n## FP7 calculation\")\n",
    "print(f\"- size part before: {len(FP7)}\")\n",
    "part1 = (FP7[['project_id', 'participant_order', 'role', 'generalPic', 'global_costs',\n",
    "    'participant_type_code', 'legalName', 'businessName', 'countryCode', 'nutsCode',\n",
    "    'funding', 'status.x', 'ADRESS', 'city', 'post_code', 'pme', 'stage', 'nom', 'countryCode_parent', 'vat_id',\n",
    "    'country_code_mapping', 'participant_id', 'number_involved', 'coordination_number', 'id', 'ZONAGE',\n",
    "    'country_name_mapping', 'country_code', 'country_name_en','country_association_code', 'country_association_name_en',\n",
    "    'country_group_association_code', 'country_group_association_name_en','country_group_association_name_fr', \n",
    "    'country_name_fr', 'article1', 'article2']]\n",
    "        .merge(entities_tmp, how='left', on=['generalPic', 'country_code_mapping', 'id']))\n",
    "print(f\"- size part before: {len(part1)}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "- size part after: 730796\n"
     ]
    }
   ],
   "source": [
    "part1 = (FP7[['project_id', 'participant_order', 'role', 'generalPic', 'global_costs',\n",
    "    'participant_type_code', 'legalName', 'businessName', 'countryCode', 'nutsCode',\n",
    "    'funding', 'status.x', 'ADRESS', 'city', 'post_code', 'pme', 'stage', 'nom', 'countryCode_parent', 'vat_id',\n",
    "    'country_code_mapping', 'participant_id', 'number_involved', 'coordination_number', 'id', 'ZONAGE',\n",
    "    'country_name_mapping', 'country_code', 'country_name_en','country_association_code', 'country_association_name_en',\n",
    "    'country_group_association_code', 'country_group_association_name_en','country_group_association_name_fr', \n",
    "    'country_name_fr', 'article1', 'article2']]\n",
    "        .merge(entities_tmp, how='left', on=['generalPic', 'country_code_mapping', 'id']))\n",
    "\n",
    "part2=(part1.loc[part1.entities_name.isnull()].drop_duplicates())\n",
    "part3=(part2.sort_values(['legalName', 'businessName'], ascending=False)\n",
    "    .groupby(['generalPic', 'country_code_mapping'])\n",
    "    .first().reset_index()[['generalPic', 'country_code_mapping', 'legalName', 'businessName']]\n",
    "    .rename(columns={'legalName':'entities_name', 'businessName':'entities_acronym'}))\n",
    "\n",
    "part2 = (part2.drop(columns=['entities_name', 'entities_acronym', 'nom'])\n",
    "        .merge(part3, how='left', on=['generalPic', 'country_code_mapping']))\n",
    "part2['entities_name'] = part2.entities_name.str.capitalize().str.strip()\n",
    "part2['entities_id'] = \"pic\"+part2.generalPic.map(str)\n",
    "\n",
    "part1=part1.loc[~part1.entities_name.isnull()].drop_duplicates()\n",
    "\n",
    "part1=pd.concat([part1, part2], ignore_index=True).assign(number_involved=1)\n",
    "\n",
    "part1['nb'] = part1.id.str.split(';').str.len()\n",
    "for i in ['funding', 'coordination_number', 'number_involved']:\n",
    "    part1[i] = np.where(part1['nb']>1, part1[i]/part1['nb'], part1[i])\n",
    "\n",
    "# 'requestedGrant'\n",
    "print(f\"- size part after: {len(part1)}\")\n",
    "\n",
    "if any(part1.entities_id=='nan')|any(part1.entities_id.isnull()):\n",
    "    print(f\"1 - attention il reste des entities sans entities_id valides\")\n",
    "\n",
    "type_entity = pd.read_json(open('data_files/legalEntityType.json', 'r', encoding='UTF-8'))\n",
    "# part1.loc[part1.participant_type_code=='N/A', 'participant_type_code'] = 'NA'\n",
    "part1 = (part1.merge(type_entity, how='left', left_on='participant_type_code', right_on='cordis_type_entity_code')\n",
    ".drop(columns='participant_type_code'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "np.float64(7069381181.86)"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "part1.loc[(part1.country_code=='DEU')&(part1.stage=='successful')&(part1.project_id.isin(pp))].funding.sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "730796"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(part1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "- size part1 with code after cleanup nuts: 675001\n",
      "- nuts code without name: 894\n",
      "45263341012.399994\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "# # # gestion code nuts\n",
    "# nuts = pd.read_pickle(\"data_files/nuts_complet.pkl\")\n",
    "# nuts = (nuts[['nuts_code_2013','nutsCode', 'lvl1Description', 'lvl2Description', 'lvl3Description']]\n",
    "#         .drop_duplicates()\n",
    "#         .rename(columns={'nuts_code_2013':'nuts_code_tmp', 'nutsCode':'nuts_code','lvl1Description':'region_1_name', 'lvl2Description': 'region_2_name', 'lvl3Description':'regional_unit_name'}))\n",
    "# # nuts['region_1_name'] = nuts['region_1_name'].str.title()\n",
    "# print(len(nuts))\n",
    "\n",
    "part1['nuts_code_tmp'] = np.where(part1.nutsCode.str.len()<3, np.nan, part1.nutsCode)\n",
    "\n",
    "print(f\"- size part1 with code after cleanup nuts: {len(part1[~part1.nuts_code_tmp.isnull()])}\")\n",
    "\n",
    "nuts = nuts.loc[(nuts.nuts_code_tmp.isin(part1.nuts_code_tmp.unique()))&(~nuts.nuts_code_tmp.isnull())]\n",
    "part1 = part1.merge(nuts, how='left', on='nuts_code_tmp').drop_duplicates()\n",
    "print(f\"- nuts code without name: {len(part1[(~part1.nuts_code.isnull())&(part1.region_1_name.isnull())])}\")\n",
    "\n",
    "# print(part1.groupby(['stage'], dropna=True )['nuts_code'].size())\n",
    "print(part1.loc[part1.stage=='successful', 'funding'].sum())\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "## FP7 themes\n",
      "- size proj before cleaning: 166230\n",
      "- size proj after instru: 166230\n",
      "- size proj after ERC: 166230\n",
      "- size proj success after msca: 25363, nb project_id: 25363\n",
      "- size proj after msca: 166230\n",
      "- size proj after euratom: 166230\n",
      "- size proj after ju-jti: 166230\n",
      "- size proj after cost: 166230\n",
      "- size proj after cost: 166230\n",
      "- size proj after cost: 166230\n",
      "- size proj after cost: 166230\n",
      "- size proj after thema_code: 166230\n",
      "       programme_code                                  programme_name_en  \\\n",
      "0             SP1-JTI        Joint Technology Initiatives (Annex IV-SP1)   \n",
      "7             SP1-JTI        Joint Technology Initiatives (Annex IV-SP1)   \n",
      "84               MSCA              Marie Skłodowska-Curie Actions (MSCA)   \n",
      "85                ERC                    European Research Council (ERC)   \n",
      "87               MSCA              Marie Skłodowska-Curie Actions (MSCA)   \n",
      "...               ...                                                ...   \n",
      "22923             ERC                    European Research Council (ERC)   \n",
      "25251             COH  Support for the coherent development of resear...   \n",
      "30343             ICT         Information and Communication Technologies   \n",
      "103200            ERC                    European Research Council (ERC)   \n",
      "119131            COH  Support for the coherent development of resear...   \n",
      "\n",
      "                                            thema_name_en    destination_code  \\\n",
      "0                             Public-private partnerships               Chips   \n",
      "7                             Public-private partnerships                 IHI   \n",
      "84                                 Marie Skłodowska-Curie            CITIZENS   \n",
      "85                              European research council                 STG   \n",
      "87                                 Marie Skłodowska-Curie                  PF   \n",
      "...                                                   ...                 ...   \n",
      "22923                           European research council                 COG   \n",
      "25251   Support for the coherent development of resear...               COH-3   \n",
      "30343          Information and Communication Technologies                 ICT   \n",
      "103200                          European research council                 POC   \n",
      "119131  Support for the coherent development of resear...  COH-2012-PROCURERS   \n",
      "\n",
      "                                      destination_name_en  \\\n",
      "0                             Chips for Europe Initiative   \n",
      "7                            Innovative Health Initiative   \n",
      "84                            European Researchers' Night   \n",
      "85                                        Starting grants   \n",
      "87                               Postdoctoral Fellowships   \n",
      "...                                                   ...   \n",
      "22923                                 Consolidator grants   \n",
      "25251   Supporting a single market for R&I and deliver...   \n",
      "30343          Information and Communication Technologies   \n",
      "103200                            Proof of concept grants   \n",
      "119131    Support to trans-national networks of procurers   \n",
      "\n",
      "       destination_detail_code  \\\n",
      "0                          NaN   \n",
      "7                          NaN   \n",
      "84                    CITIZENS   \n",
      "85                         NaN   \n",
      "87                       PF-EF   \n",
      "...                        ...   \n",
      "22923                      NaN   \n",
      "25251                      NaN   \n",
      "30343                      NaN   \n",
      "103200                     NaN   \n",
      "119131                     NaN   \n",
      "\n",
      "                             destination_detail_name_en  \n",
      "0                                                   NaN  \n",
      "7                                                   NaN  \n",
      "84                          European Researchers' Night  \n",
      "85                                                  NaN  \n",
      "87      Postdoctoral Fellowships - European Fellowships  \n",
      "...                                                 ...  \n",
      "22923                                               NaN  \n",
      "25251                                               NaN  \n",
      "30343                                               NaN  \n",
      "103200                                              NaN  \n",
      "119131                                              NaN  \n",
      "\n",
      "[171 rows x 7 columns]\n",
      "166230\n"
     ]
    }
   ],
   "source": [
    "instr = pd.read_csv('data_files/instru_nomenclature.csv', sep=';')\n",
    "act=pd.read_json(open(\"data_files/actions_name.json\", 'r', encoding='utf-8'))\n",
    "msca_correspondence = pd.read_table('data_files/msca_correspondence.csv', sep=\";\").drop(columns='framework')\n",
    "erc_correspondence = pd.read_json(open(\"data_files/ERC_correspondance.json\", 'r', encoding='utf-8'))\n",
    "thema = pd.read_json(open(\"data_files/thema.json\", 'r', encoding='utf-8'))\n",
    "destination = pd.read_json(open(\"data_files/destination.json\", 'r', encoding='utf-8'))\n",
    "\n",
    "def themes_cleaning(FP7):\n",
    "    print(\"## FP7 themes\")\n",
    "    print(f\"- size proj before cleaning: {len(FP7[['project_id', 'stage']].drop_duplicates())}\")\n",
    "    proj = (FP7.assign(stage_name=np.where(FP7.stage=='successful', 'projets lauréats', 'projets évalués'))\n",
    "            [['project_id', 'stage', 'acronym', 'abstract', 'title', 'call_id', 'stage_name',\n",
    "            'call_deadline', 'instrument',  'panel_code', 'panel_name', 'call_year', 'duration', 'status_code', \n",
    "        'cost_total', 'eu_reqrec_grant', 'free_keywords', 'number_involved', 'submission_date',\n",
    "        'start_date', 'signature_date', 'end_date',  'pilier', 'prog_abbr', 'prog_lib', 'area_abbr', 'area_lib']]\n",
    "            .drop_duplicates())\n",
    "\n",
    "    proj.loc[(proj.prog_abbr=='ERC')&(proj.instrument=='POC'), 'instrument'] = 'ERC-POC'\n",
    "    proj.loc[proj.prog_abbr=='PEOPLE', 'thema_code'] = 'MSCA'\n",
    "    proj.loc[proj.prog_abbr=='ERC', 'thema_code'] = 'ERC'\n",
    "\n",
    "    # print(f\"- size proj: {len(proj)}\")\n",
    "\n",
    "    proj = proj.merge(instr, how='left', on='instrument').drop(columns=['name'])\n",
    "    proj.loc[proj.instrument.str.contains('MC-'), 'action_code'] = 'MSCA'        \n",
    "\n",
    "    if any(proj.action_code.isnull()):\n",
    "        print(proj[proj.action_code.isnull()].instrument.unique())   \n",
    "        \n",
    "    print(f\"- size proj after instru: {len(proj)}\")\n",
    "\n",
    "    # ERC\n",
    "    proj = proj.merge(erc_correspondence, how='left', left_on=['instrument'], right_on=['old'])\n",
    "\n",
    "    proj.loc[(proj.thema_code=='ERC')&(proj.destination_code.isnull()), 'destination_code'] = 'ERC-OTHER'\n",
    "\n",
    "    proj.loc[proj.thema_code=='ERC', 'programme_code'] = 'ERC'\n",
    "    proj.loc[proj.thema_code=='ERC', 'programme_name_en'] = 'European Research Council (ERC)'\n",
    "    print(f\"- size proj after ERC: {len(proj)}\")\n",
    "\n",
    "    # MSCA\n",
    "    proj = proj.merge(msca_correspondence, how='left', left_on=['instrument'], right_on=['old'])\n",
    "    proj.loc[proj.call_id.str.contains('NIGHT'), 'destination_detail_code'] = 'CITIZENS'\n",
    "    proj.loc[~proj.destination_detail_code.isnull(), 'destination_code'] = proj.destination_detail_code.str.split('-').str[0]\n",
    "    proj.loc[(proj.destination_code.isnull())&(proj.thema_code=='MSCA'), 'destination_code'] = 'MSCA-OTHER'\n",
    "    proj.loc[proj.thema_code=='MSCA', 'programme_code'] = 'MSCA'\n",
    "    proj.loc[proj.thema_code=='MSCA', 'programme_name_en'] = 'Marie Skłodowska-Curie Actions (MSCA)'\n",
    "\n",
    "    proj.rename(columns={'instrument':'fp_specific_instrument'}, inplace=True)\n",
    "\n",
    "    print(f\"- size proj success after msca: {proj.loc[proj.stage=='successful'].project_id.nunique()}, nb project_id: {len(proj.loc[proj.stage=='successful'])}\")\n",
    "    print(f\"- size proj after msca: {len(proj)}\")\n",
    "    #euratom\n",
    "    proj.loc[(proj.pilier.isin(['EURATOM']))&(proj.prog_abbr=='Fission'), 'programme_code'] = 'NFRP'\n",
    "    proj.loc[(proj.pilier.isin(['EURATOM']))&(proj.programme_code=='NFRP'), 'programme_name_en'] = 'Nuclear fission and radiation protection'\n",
    "    proj.loc[proj.prog_abbr=='Fusion', 'programme_code'] = 'Fusion'\n",
    "    proj.loc[proj.prog_abbr=='Fusion', 'programme_name_en'] = 'Fusion Energy'\n",
    "\n",
    "    euratom = pd.read_csv('data_files/euratom_thema_all_FP.csv', sep=';', na_values='')\n",
    "    proj = proj.merge(euratom[['topic_area', 'thema_code', 'thema_name_en']], how='left', left_on='area_abbr', right_on='topic_area', suffixes=['', '_t'])\n",
    "    proj.loc[(~proj.thema_code_t.isnull()), 'thema_code'] = proj.loc[(~proj.thema_code_t.isnull()), 'thema_code_t']\n",
    "    proj = proj.filter(regex=r'.*(?<!_t)$')\n",
    "    print(f\"- size proj after euratom: {len(proj)}\")\n",
    "\n",
    "    #ju_jti\n",
    "    proj.loc[proj.prog_abbr=='SP1-JTI', 'thema_code'] = 'JU-JTI'\n",
    "    proj.loc[proj.prog_abbr=='SP1-JTI', 'destination_code'] = proj.area_abbr.str.split('-').str[-1]\n",
    "    proj.loc[proj.area_abbr=='JTI-CS', 'destination_code'] = 'CLEAN-AVIATION'\n",
    "\n",
    "    proj.loc[(proj.destination_code=='CLEAN-SKY'), 'destination_code'] = 'CLEAN-AVIATION'\n",
    "    proj.loc[(proj.destination_code=='FCH'), 'destination_code'] = 'CLEANH2'\n",
    "    proj.loc[(proj.destination_code=='IMI'), 'destination_code'] = 'IHI'\n",
    "    proj.loc[(proj.destination_code.isin(['ENIAC','ARTEMIS'])), 'destination_code'] = 'Chips'\n",
    "    proj.loc[proj.thema_code=='JU-JTI', 'action_code'] = proj.fp_specific_instrument.str.split('-').str[1]\n",
    "    print(f\"- size proj after ju-jti: {len(proj)}\")\n",
    "\n",
    "    # WIDENING COST\n",
    "    proj.loc[proj.area_abbr.str.contains('COST', na=False), 'thema_code'] = 'COST'\n",
    "    proj.loc[proj.area_abbr.str.contains('COST', na=False), 'programme_code'] = 'Widening'\n",
    "    proj.loc[proj.area_abbr.str.contains('COST', na=False), 'programme_name_en'] = 'Widening participation and spreading excellence'\n",
    "    print(f\"- size proj after cost: {len(proj)}\")\n",
    "\n",
    "    proj.loc[proj.pilier=='EURATOM', 'pilier_name_en'] = 'Euratom'\n",
    "    proj.loc[(proj.prog_abbr.isin(['PEOPLE','ERC']))|(proj.prog_abbr=='INFRA'), 'pilier_name_en'] = 'Excellent Science'\n",
    "    proj.loc[proj.pilier_name_en.isnull(), 'pilier_name_en'] = proj.pilier.str.capitalize()\n",
    "    print(f\"- size proj after cost: {len(proj)}\")\n",
    "\n",
    "    proj.loc[proj.programme_code.isnull(), 'programme_code'] = proj.prog_abbr\n",
    "    proj.loc[proj.programme_name_en.isnull(), 'programme_name_en'] = proj.prog_lib\n",
    "    print(f\"- size proj after cost: {len(proj)}\")\n",
    "\n",
    "    proj.loc[(~proj.thema_code.isin(['MSCA','ERC']))&(proj.destination_code.isnull()), 'destination_code'] = proj.area_abbr\n",
    "    proj.loc[proj.destination_code.isnull(), 'destination_code'] = proj.thema_code+'-OTHER'\n",
    "    proj = proj.merge(destination[['destination_code', 'destination_name_en']], how='left', on='destination_code')\n",
    "    proj = (proj\n",
    "            .merge(destination.rename(columns={'destination_code':'destination_detail_code', 'destination_name_en':'destination_detail_name_en'})\n",
    "            [['destination_detail_code', 'destination_detail_name_en']], how='left', on='destination_detail_code')\n",
    "            .drop_duplicates())\n",
    "    print(f\"- size proj after cost: {len(proj)}\")\n",
    "\n",
    "    proj.loc[(~proj.thema_code.isin(['MSCA','ERC']))&(proj.destination_name_en.isnull()), 'destination_name_en'] = proj.area_lib\n",
    "    proj.loc[proj.thema_code.isnull(), 'thema_code'] = proj.prog_abbr\n",
    "    proj = proj.merge(thema[['thema_code', 'thema_name_en']], how='left', on='thema_code', suffixes=['', '_t'])\n",
    "    proj.loc[proj.thema_name_en.isnull(), 'thema_name_en'] = proj.thema_name_en_t\n",
    "    proj.loc[proj.thema_name_en.isnull(),'thema_name_en'] = proj.prog_lib\n",
    "    proj = proj.filter(regex=r'.*(?<!_t)$')\n",
    "    print(f\"- size proj after thema_code: {len(proj)}\")\n",
    "\n",
    "    proj = (proj.drop(columns=['area_abbr', 'area_lib'])\n",
    "            .rename(columns={'prog_lib':'fp_specific_programme', 'pilier':'fp_specific_pilier'}))\n",
    "    \n",
    "    print(proj[['programme_code',\n",
    "    'programme_name_en', 'thema_name_en', 'destination_code', 'destination_name_en',\n",
    "    'destination_detail_code','destination_detail_name_en']].drop_duplicates())\n",
    "    print(len(proj))\n",
    "    return proj\n",
    "proj=themes_cleaning(FP7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "size proj: 25363, nb project_id: 151951, 51791547081.31999\n"
     ]
    }
   ],
   "source": [
    "def proj_cleaning(proj):\n",
    "    print(f\"- size proj before cleaning: {len(proj)}\")\n",
    "    proj = proj.merge(act, how='left', on='action_code')\n",
    "    proj = proj.merge(call, how='left', on='call_id').assign(ecorda_date=pd.to_datetime('2021-04-30'), framework='FP7')\n",
    "    proj = proj.assign(ecorda_date=pd.to_datetime('2021-04-30'), framework='FP7')\n",
    "    for i in ['title', 'abstract', 'free_keywords']:\n",
    "        proj[i]=proj[i].str.replace('\\\\n|\\\\t|\\\\r|\\\\s+', ' ', regex=True).str.strip()\n",
    "\n",
    "    kw = proj[['project_id', 'free_keywords']]\n",
    "    kw = kw.assign(free_keywords = kw.free_keywords.str.split(';|,')).explode('free_keywords')\n",
    "    kw = kw.loc[kw.free_keywords.str.len()>3].drop_duplicates()\n",
    "    kw.free_keywords = kw.free_keywords.groupby(level=0).apply(lambda x: '|'.join(x.str.strip().unique()))\n",
    "\n",
    "    proj = proj.drop(columns='free_keywords').merge(kw.drop_duplicates(), how='left', on='project_id')\n",
    "    proj.mask(proj=='', inplace=True)  \n",
    "\n",
    "    for d in ['call_deadline', 'signature_date',  'start_date',  'end_date', 'submission_date']:\n",
    "        proj[d] = pd.to_datetime(proj[d],format='%d/%m/%Y %H:%M:%S')\n",
    "    print(f\"- size proj cleaned: {len(proj)}\")\n",
    "    return proj\n",
    "proj=proj_cleaning(proj)\n",
    "# def proj_ods(proj, part1):\n",
    "#     country=(part1.loc[part1.stage=='successful',\n",
    "#                 ['project_id','country_code','country_name_fr','country_code_mapping', 'ZONAGE',\n",
    "#                     'country_name_mapping', 'nuts_code', 'region_1_name', 'region_2_name','regional_unit_name']]\n",
    "#         .drop_duplicates()\n",
    "#         .groupby(['project_id'], as_index = False).agg(lambda x: ';'.join(map(str,filter(None, x))))\n",
    "#         .drop_duplicates())\n",
    "\n",
    "#     prop = (proj.loc[proj.stage=='evaluated', ['project_id', 'cost_total', 'eu_reqrec_grant', 'number_involved']]\n",
    "#         .rename(columns={'number_involved':'proposal_numberofapplicants', 'eu_reqrec_grant':'proposal_requestedgrant', 'cost_total':'proposal_budget'})\n",
    "#         .drop_duplicates())\n",
    "\n",
    "#     p = (proj.loc[proj.stage=='successful', ['project_id', 'eu_reqrec_grant', 'number_involved', 'cost_total']]\n",
    "#         .rename(columns={'eu_reqrec_grant':'project_eucontribution', 'number_involved':'project_numberofparticipants','cost_total':'project_totalcost'})\n",
    "#         .drop_duplicates())\n",
    "\n",
    "#     # # PROVISOIRE quand def call refonctionnera\n",
    "#     # proj=proj.assign(call_budget=np.nan)\n",
    "\n",
    "#     project = (proj.loc[proj.stage=='successful', \n",
    "#             ['abstract', 'acronym', 'action_code', 'action_name', 'call_budget','call_deadline', 'call_id', 'call_year',\n",
    "#             'destination_code','destination_detail_code', 'destination_detail_name_en', 'destination_name_en', \n",
    "#             'duration', 'ecorda_date', 'end_date', 'fp_specific_instrument', 'framework', 'free_keywords', \n",
    "#             'panel_code', 'panel_name', 'fp_specific_programme', 'fp_specific_pilier',\n",
    "#             'pilier_name_en', 'programme_code', 'programme_name_en', 'project_id', 'signature_date', 'stage', 'stage_name', \n",
    "#             'start_date', 'status_code', 'submission_date', 'thema_code', 'thema_name_en', 'title']]\n",
    "            \n",
    "#         .drop_duplicates())\n",
    "\n",
    "#     project = project.merge(p, how='left', on='project_id').merge(country, how='inner', on='project_id').merge(prop, how='left' , on='project_id')\n",
    "\n",
    "#     print(f\"1 - size project lauréats: {len(project)}, {len(p)}, fund: {'{:,.1f}'.format(p['project_eucontribution'].sum())}\")\n",
    "\n",
    "#     with open(f\"{PATH_CLEAN}FP7_successful_projects.pkl\", 'wb') as file:\n",
    "#         pd.to_pickle(project, file)\n",
    "#     return project\n",
    "# proj_ods(proj, part1)\n",
    "\n",
    "def FP7_all(proj, part1):\n",
    "    t = (proj.drop(columns=['cost_total', 'duration', 'end_date', 'eu_reqrec_grant', 'fp_specific_instrument', \n",
    "                        'fp_specific_programme', 'fp_specific_pilier',\n",
    "                        'number_involved', 'signature_date', 'start_date', 'submission_date'])\n",
    "        .merge(part1, how='inner', on=['project_id', 'stage'])\n",
    "        .rename(columns={'funding':'calculated_fund', 'ZONAGE':'extra_joint_organization'}))\n",
    "    \n",
    "    t = (t.assign(is_ejo=np.where(t.extra_joint_organization.isnull(), 'Sans', 'Avec')))\n",
    "\n",
    "    t.loc[(t.destination_code.isin(['PF', 'ERARESORG', 'GA']))|((t.thema_code.isin(['ERC', 'COST']))&(t.destination_code!='SyG')), 'coordination_number'] = 0\n",
    "    t=t.assign(with_coord=True)\n",
    "    t.loc[(t.destination_code.isin(['PF', 'ERARESORG', 'GA']))|((t.thema_code.isin(['ERC', 'COST']))&(t.destination_code!='SyG')), 'with_coord'] = False\n",
    "\n",
    "    t.loc[t.thema_code=='ERC', 'erc_role'] = 'partner'\n",
    "\n",
    "    t.loc[(t.destination_code=='SyG'), 'erc_role'] = 'PI'\n",
    "    t.loc[(t.action_code=='ERC')&(t.destination_code!='SyG')&(t.role=='coordinator'), 'erc_role'] = 'PI'\n",
    "    t.loc[(t.destination_code=='ERC-OTHER'), 'erc_role'] = np.nan\n",
    "\n",
    "\n",
    "    file_name = f\"{PATH_CLEAN}FP7_data.pkl\"\n",
    "    with open(file_name, 'wb') as file:\n",
    "        pd.to_pickle(t, file)\n",
    "\n",
    "    print(f\"size proj: {t.loc[t.stage=='successful'].project_id.nunique()}, nb project_id: {len(t.loc[t.stage=='successful'])}, {t.loc[t.stage=='successful', 'calculated_fund'].sum()}\")\n",
    "    return t\n",
    "t=FP7_all(proj, part1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "np.float64(1345187910.71)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t.loc[(t.country_code=='DEU')&(t.stage=='successful')&(t.call_year=='2007')&(t.pilier_name_en!='Euratom')].calculated_fund.sum()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from functions_shared import *\n",
    "t=t.drop_duplicates().loc[(t.stage=='successful')&(t.pilier_name_en!='Euratom')]\n",
    "print(len(t))\n",
    "x=pd.crosstab(t['country_code'], t['call_year'], values=t['calculated_fund'], aggfunc='sum',margins=True, margins_name= 'All').reset_index()\n",
    "work_csv(x, 'fp7_count')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "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.13.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}