sas_to_python_atlas.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#importer les packages\n",
    "import requests\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import configparser\n",
    "from tqdm.notebook import tqdm\n",
    "tqdm.pandas()\n",
    "import matplotlib.pyplot as plt\n",
    "import itertools \n",
    "import re\n",
    "import time\n",
    "import json\n",
    "import pyreadstat\n",
    "import dask.dataframe as dd\n",
    "import dask\n",
    "import concurrent"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "DATA_PATH=\"C:/Users/haallat/Documents/SAS_to_python-Atlas\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "url='https://docs.google.com/spreadsheet/ccc?key=11NFXSIg6gQMCsMa8zWQQyypvvYBEmfyJfF2yytXqgMk&output=xls'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_all_correctifs_from_google():\n",
    "    CORRECTIFS_dict= dict()\n",
    "    VARS = ['COM_U','COM_M', 'ETABLI', 'MINISTER', 'DC', 'NATION', 'ETABLI2', 'FORMAT',\n",
    "    'CURSUS_LMD', 'DISCIPLI','RGP2', 'RGP3','O_DUTBUT','J_LMDDONT','GDDISCIPLI']\n",
    "    df_c = pd.read_excel(url, sheet_name=VARS, dtype=str)\n",
    "    for VAR in VARS:\n",
    "        correctifs = df_c.get(VAR).to_dict(orient='records')\n",
    "        for c in correctifs:\n",
    "            for f in c:\n",
    "                if c[f] != c[f]: # nan\n",
    "                    c[f] = ''\n",
    "                if 'annee' in f.lower() or 'rentree' in f.lower():\n",
    "                    c[f] = str(c[f])\n",
    "                if isinstance(c[f], str):\n",
    "                    c[f] = c[f].replace('.0','').strip()\n",
    "                elif isinstance(c[f], float) or isinstance(c[f], int):\n",
    "                    c[f] = str(c[f]).replace('.0','').strip()\n",
    "        CORRECTIFS_dict[f'{VAR}'] = correctifs\n",
    "    json.dump(CORRECTIFS_dict, open(f'{DATA_PATH}/correctifs.json', 'w'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "get_all_correctifs_from_google()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_all_correctifs():\n",
    "    try:\n",
    "        correctifs = json.load(open(f'{DATA_PATH}/correctifs.json', 'r'))\n",
    "    except:\n",
    "        correctifs = {}\n",
    "    for k in correctifs:\n",
    "        for ix, e in enumerate(correctifs[k]):\n",
    "            for f in e.copy():\n",
    "                if f.upper() != f:\n",
    "                    correctifs[k][ix][f.upper()] = correctifs[k][ix][f]\n",
    "    return correctifs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "CORRECTIFS_dict = get_all_correctifs()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def corrige_com_u(df):\n",
    "    VAR = 'COM_U'\n",
    "    print(VAR)\n",
    "    for c in CORRECTIFS_dict[VAR]:\n",
    "        if c['ANNEE']!= '' :\n",
    "            if c['COMPOS']!= '' :\n",
    "                if c['IN'] == \"' '\":\n",
    "                    df.loc[((df[VAR] == '')|(df[VAR] is np.nan)) & (df['COMPOS']==c['COMPOS']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "                else:\n",
    "                    df.loc[(df['COMPOS']==c['COMPOS']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "            else:\n",
    "                if c['IN'] == \"' '\":\n",
    "                    df.loc[((df[VAR] == '')|(pd.isna(df[VAR])) |(df[VAR]==None)) & (df['FINECOLE']==c['FINECOLE']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "                else:\n",
    "                    df.loc[(df['FINECOLE']==c['FINECOLE']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "        else:\n",
    "            if c['COMPOS']!= '' :\n",
    "                df.loc[(df['COMPOS']==c['COMPOS']), VAR]=c['OUT']\n",
    "            else:\n",
    "                if c['IN'] == \"' '\":\n",
    "                    df.loc[(df[VAR] == '') & (df['FINECOLE']==c['FINECOLE']) , VAR]=c['OUT']\n",
    "                else :\n",
    "                    if c['FINECOLE'] != '':\n",
    "                        df.loc[(df[VAR] == c['IN']) & (df['FINECOLE']==c['FINECOLE']) , VAR]=c['OUT']\n",
    "                    else:\n",
    "                        df.loc[(df[VAR] == c['IN']) , VAR]=c['OUT']\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def corrige_com_m(df):\n",
    "    VAR = 'COM_M'\n",
    "    print(VAR)\n",
    "    for c in CORRECTIFS_dict[VAR]:\n",
    "        if c['ANNEE']!= '' :\n",
    "            if c['COMPOS']!= '' :\n",
    "                if c['IN'] == \"' '\":\n",
    "                    df.loc[((df[VAR] == '')|(pd.isna(df[VAR])) |(df[VAR]==None)) & (df['COMPOS']==c['COMPOS']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "                elif c['ETABLI']!= '' :\n",
    "                    if c['FORMAT']!= '' :\n",
    "                        df.loc[(df['FORMAT']==c['FORMAT'])& (df['ETABLI']==c['ETABLI']) & (df['COMPOS']==c['COMPOS']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "                    else:\n",
    "                        df.loc[(df['ETABLI']==c['ETABLI']) & (df['COMPOS']==c['COMPOS']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "                elif c['DIPLOM']!= '' :\n",
    "                    df.loc[(df['DIPLOM']==c['DIPLOM']) & (df['COMPOS']==c['COMPOS']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "                elif c['COM_U']!= '' :\n",
    "                    df.loc[((df['COM_U'] == \"' '\")|(df['COM_U'] is np.nan)) & (df['COMPOS']==c['COMPOS']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "                else:\n",
    "                    if c['OUT'] == 'com_u':\n",
    "                        df.loc[(df['COMPOS']==c['COMPOS']) & (df['ANNEE']==c['ANNEE']), VAR]=df['COM_U']\n",
    "                    else:\n",
    "                        df.loc[(df['COMPOS']==c['COMPOS']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "            elif c['ETABLI']!= '' :\n",
    "                df.loc[(df['ETABLI']==c['ETABLI']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "            else:\n",
    "                df.loc[(df['FINECOLE']==c['FINECOLE']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "        else:\n",
    "            if c['COMPOS']!= '' :\n",
    "                df.loc[(df['COMPOS']==c['COMPOS']), VAR]=c['OUT']\n",
    "            else:\n",
    "                if c['FINECOLE'] != '':\n",
    "                    if c['IN'] == \"' '\":\n",
    "                        df.loc[((df[VAR] == '')|(pd.isna(df[VAR])) |(df[VAR]==None)) & (df['FINECOLE']==c['FINECOLE']) , VAR]=c['OUT']\n",
    "                    else:\n",
    "                        df.loc[(df[VAR] == c['IN']) & (df['FINECOLE']==c['FINECOLE']) , VAR]=c['OUT']\n",
    "                else :\n",
    "                    df.loc[(df[VAR] == c['IN']) , VAR]=c['OUT']\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def corrige_etabli(df):\n",
    "    VAR = 'ETABLI'\n",
    "    print(VAR)\n",
    "    for c in CORRECTIFS_dict[VAR]:\n",
    "        if c['ANNEE']!= '' :\n",
    "            if c['COMPOS']!= '' :\n",
    "                if c['IN'] != \"''\":\n",
    "                    if c['FORMAT'] != '':\n",
    "                        df.loc[(df[VAR] == c['IN']) & (df['FORMAT']==c['FORMAT']) & (df['COMPOS']==c['COMPOS']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "                    else:\n",
    "                        df.loc[(df[VAR] == c['IN']) & (df['COMPOS']==c['COMPOS']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "                if c['DIPLOM'] != '':\n",
    "                    df.loc[(df['DIPLOM']==c['DIPLOM']) & (df['COMPOS']==c['COMPOS']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "                else:\n",
    "                    df.loc[(df['COMPOS']==c['COMPOS']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "            else:\n",
    "                if c['DIPLOM'] != '':\n",
    "                    df.loc[(df[VAR] == c['IN']) & (df['DIPLOM']==c['DIPLOM']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "                elif c['FORMAT'] != '':\n",
    "                        df.loc[(df[VAR] == c['IN']) & (df['FORMAT']==c['FORMAT']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "                elif c['CURSUS_LMD'] != '':\n",
    "                        df.loc[(df[VAR] == c['IN']) & (df['CURSUS_LMD']==c['CURSUS_LMD']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "        else:\n",
    "            df.loc[(df['COMPOS']==c['COMPOS']), VAR]=c['OUT']\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "def corrige_minister(df):\n",
    "    VAR = 'MINISTER'\n",
    "    print(VAR)\n",
    "    for c in CORRECTIFS_dict[VAR]:\n",
    "        if c['ANNEE']!= '' :\n",
    "            df.loc[(df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "        else:\n",
    "            df.loc[(df['ETABLI']==c['ETABLI']), VAR]=c['OUT']\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def corrige_dc(df):\n",
    "    VAR = 'DC'\n",
    "    print(VAR)\n",
    "    for c in CORRECTIFS_dict[VAR]:\n",
    "        df.loc[(df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def corrige_nation(df):\n",
    "    VAR = 'NATION'\n",
    "    print(VAR)\n",
    "    for c in CORRECTIFS_dict[VAR]:\n",
    "        df.loc[(df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "def corrige_etabli2(df):\n",
    "    VAR = 'ETABLI2'\n",
    "    print(VAR)\n",
    "    for c in CORRECTIFS_dict[VAR]:\n",
    "        if c['COMPOS']!= '' :\n",
    "            df.loc[(df['COMPOS']==c['COMPOS']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "        else:\n",
    "            if c['ACA_U']!= '' :\n",
    "                df.loc[(df['ACA_U']==c['ACA_U']) & (df['FORMAT']==c['FORMAT']) & (df['ETABLI']==c['ETABLI']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "            else:\n",
    "                df.loc[(df['FORMAT']==c['FORMAT']) & (df['ETABLI']==c['ETABLI']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "def corrige_format(df):\n",
    "    VAR = 'FORMAT'\n",
    "    print(VAR)\n",
    "    for c in CORRECTIFS_dict[VAR]:\n",
    "        if c['ANNEE']!= '' :\n",
    "            if c['SECT']!= '' :\n",
    "                    df.loc[(df[VAR] == c['IN']) & (df['SECT']==c['SECT']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "            elif c['COMPOS']!= '' :\n",
    "                if c['IN']!= \"''\" :\n",
    "                    df.loc[(df[VAR] == c['IN']) & (df['COMPOS']==c['COMPOS']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "                elif c['DIPLOM']!= '' :\n",
    "                    df.loc[(df['DIPLOM'] == c['DIPLOM']) &(df['COMPOS']==c['COMPOS']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "                else:\n",
    "                    df.loc[(df['COMPOS']==c['COMPOS']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "            else:\n",
    "                if c['IN']!= \"''\" :\n",
    "                    df.loc[(df[VAR] == c['IN']) & (df['ETABLI']==c['ETABLI']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "                elif c['NOT IN']!= '' :\n",
    "                    df.loc[(df[VAR] != c['NOT IN']) & (df['ETABLI']==c['ETABLI']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "                elif c['TYP_DIPL']!= '' :\n",
    "                    if c['NOT DIPLOM']!= '' :\n",
    "                        df.loc[(df['DIPLOM'] != c['NOT DIPLOM']) & (df['TYP_DIPL']==c['TYP_DIPL']) & (df['ETABLI']==c['ETABLI']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "                    else:\n",
    "                        df.loc[(df['TYP_DIPL']==c['TYP_DIPL']) & (df['ETABLI']==c['ETABLI']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "                else:\n",
    "                    df.loc[(df['ETABLI']==c['ETABLI']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "        else:\n",
    "            if c['IN']!= \"''\" :\n",
    "                df.loc[(df[VAR] == c['IN']) & (df['ETABLI']==c['ETABLI']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "            else:\n",
    "                df.loc[(df[VAR] != c['NOT IN']) & (df['ETABLI']==c['ETABLI']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "def corrige_cursus_lmd(df):\n",
    "    VAR = 'CURSUS_LMD'\n",
    "    print(VAR)\n",
    "    for c in CORRECTIFS_dict[VAR]:\n",
    "        if c['ANNEE']!= '' :\n",
    "            if c['ETABLI']!= '' :\n",
    "                if c['IN']== \"' '\" :\n",
    "                    df.loc[((df[VAR] == '')|(pd.isna(df[VAR])) |(df[VAR]==None)) & (df['ETABLI']==c['ETABLI']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "                else:\n",
    "                    df.loc[(df[VAR] == c['IN']) & (df['ETABLI']==c['ETABLI']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "            elif c['TYP_DIPL']!= '' :\n",
    "                df.loc[((df[VAR] == '')|(df[VAR] is np.nan)) & (df['TYP_DIPL']==c['TYP_DIPL']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "            elif c['DIPLOM']!= '' :\n",
    "                df.loc[((df[VAR] == '')|(df[VAR] is np.nan)) & (df['DIPLOM'] == c['DIPLOM']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "            else:\n",
    "                if c['IN']== \"' '\" :\n",
    "                    df.loc[((df[VAR] == '')|(df[VAR] is np.nan)) & (df['COMPOS']==c['COMPOS']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "                else:\n",
    "                    df.loc[ (df['COMPOS']==c['COMPOS']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "        else:\n",
    "            df.loc[((df[VAR] == '')|(df[VAR] is np.nan)) & (df['ENQ']==c['ENQ']) & (df['ETABLI']==c['ETABLI']) , VAR]=c['OUT']\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "def corrige_discipli(df):\n",
    "    VAR = 'DISCIPLI'\n",
    "    print(VAR)\n",
    "    for c in CORRECTIFS_dict[VAR]:\n",
    "        if c['ETABLI']!= '' :\n",
    "            df.loc[(df[VAR] == c['IN']) & (df['ETABLI']==c['ETABLI']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "        else:\n",
    "            if c['IN']== \"' '\" :\n",
    "                df.loc[((df[VAR] == '')|(pd.isna(df[VAR])) |(df[VAR]==None)) & (df['COMPOS']==c['COMPOS']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "            elif c['IN']== '' :\n",
    "                df.loc[ (df['COMPOS']==c['COMPOS']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "            else:\n",
    "                df.loc[(df[VAR] == c['IN']) & (df['COMPOS']==c['COMPOS']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "def corrige_rgp2(df):\n",
    "    VAR = 'RGP2'\n",
    "    print(VAR)\n",
    "    for c in CORRECTIFS_dict[VAR]:\n",
    "        df.loc[(df['ETABLI']==c['ETABLI']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "def corrige_rgp3(df):\n",
    "    VAR = 'RGP3'\n",
    "    print(VAR)\n",
    "    for c in CORRECTIFS_dict[VAR]:\n",
    "        df.loc[(df['ETABLI']==c['ETABLI']) & (df['ANNEE']==c['ANNEE']), VAR]=c['OUT']\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "def corrige_all(df):\n",
    "    return corrige_discipli(corrige_cursus_lmd(corrige_format(corrige_nation(corrige_dc(corrige_minister(corrige_etabli(corrige_com_m(corrige_com_u(df)))))))))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Importer les formats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "communes=pyreadstat.read_sas7bdat(f'{DATA_PATH}/formats/les_communes.sas7bdat', encoding='iso-8859-1')[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>COM_CODE</th>\n",
       "      <th>COM_CODE1</th>\n",
       "      <th>COM_CODE2</th>\n",
       "      <th>COM_ID</th>\n",
       "      <th>COM_NOM_MAJ_COURT</th>\n",
       "      <th>COM_NOM_MAJ</th>\n",
       "      <th>COM_NOM</th>\n",
       "      <th>UU_CODE</th>\n",
       "      <th>UU_ID</th>\n",
       "      <th>UUCR_ID</th>\n",
       "      <th>...</th>\n",
       "      <th>REG_NOM</th>\n",
       "      <th>REGRGP_NOM</th>\n",
       "      <th>REG_CODE_OLD</th>\n",
       "      <th>REG_ID_OLD</th>\n",
       "      <th>REG_NOM_OLD</th>\n",
       "      <th>FD_ID</th>\n",
       "      <th>FR_ID</th>\n",
       "      <th>FE_ID</th>\n",
       "      <th>UU_ID_99</th>\n",
       "      <th>UU_ID_10</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01001</td>\n",
       "      <td>01001</td>\n",
       "      <td>01001</td>\n",
       "      <td>C01001</td>\n",
       "      <td>L ABERGEMENT CLEMENCIAT</td>\n",
       "      <td>L'ABERGEMENT-CLEMENCIAT</td>\n",
       "      <td>L'Abergement-Clémenciat</td>\n",
       "      <td></td>\n",
       "      <td>SO</td>\n",
       "      <td>CR01001</td>\n",
       "      <td>...</td>\n",
       "      <td>Auvergne-Rhône-Alpes</td>\n",
       "      <td>Province</td>\n",
       "      <td>82</td>\n",
       "      <td>R82</td>\n",
       "      <td>Rhône-Alpes</td>\n",
       "      <td>FD111</td>\n",
       "      <td>FR11</td>\n",
       "      <td>FE1</td>\n",
       "      <td>SO</td>\n",
       "      <td>SO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01002</td>\n",
       "      <td>01002</td>\n",
       "      <td>01002</td>\n",
       "      <td>C01002</td>\n",
       "      <td>L ABERGEMENT DE VAREY</td>\n",
       "      <td>L'ABERGEMENT-DE-VAREY</td>\n",
       "      <td>L'Abergement-de-Varey</td>\n",
       "      <td></td>\n",
       "      <td>SO</td>\n",
       "      <td>CR01002</td>\n",
       "      <td>...</td>\n",
       "      <td>Auvergne-Rhône-Alpes</td>\n",
       "      <td>Province</td>\n",
       "      <td>82</td>\n",
       "      <td>R82</td>\n",
       "      <td>Rhône-Alpes</td>\n",
       "      <td>FD111</td>\n",
       "      <td>FR11</td>\n",
       "      <td>FE1</td>\n",
       "      <td>SO</td>\n",
       "      <td>SO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01003</td>\n",
       "      <td>01003</td>\n",
       "      <td>01003</td>\n",
       "      <td>C01003</td>\n",
       "      <td>AMAREINS</td>\n",
       "      <td>AMAREINS</td>\n",
       "      <td>Amareins</td>\n",
       "      <td></td>\n",
       "      <td>SO</td>\n",
       "      <td>SO</td>\n",
       "      <td>...</td>\n",
       "      <td>Auvergne-Rhône-Alpes</td>\n",
       "      <td>Province</td>\n",
       "      <td>82</td>\n",
       "      <td>R82</td>\n",
       "      <td>Rhône-Alpes</td>\n",
       "      <td>FD111</td>\n",
       "      <td>FR11</td>\n",
       "      <td>FE1</td>\n",
       "      <td>SO</td>\n",
       "      <td>SO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01004</td>\n",
       "      <td>01004</td>\n",
       "      <td>01004</td>\n",
       "      <td>C01004</td>\n",
       "      <td>AMBERIEU EN BUGEY</td>\n",
       "      <td>AMBERIEU-EN-BUGEY</td>\n",
       "      <td>Ambérieu-en-Bugey</td>\n",
       "      <td>01303</td>\n",
       "      <td>UU01303</td>\n",
       "      <td>UU01303</td>\n",
       "      <td>...</td>\n",
       "      <td>Auvergne-Rhône-Alpes</td>\n",
       "      <td>Province</td>\n",
       "      <td>82</td>\n",
       "      <td>R82</td>\n",
       "      <td>Rhône-Alpes</td>\n",
       "      <td>FD111</td>\n",
       "      <td>FR11</td>\n",
       "      <td>FE1</td>\n",
       "      <td>UU01303</td>\n",
       "      <td>UU01302</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01005</td>\n",
       "      <td>01005</td>\n",
       "      <td>01005</td>\n",
       "      <td>C01005</td>\n",
       "      <td>AMBERIEUX EN DOMBES</td>\n",
       "      <td>AMBERIEUX-EN-DOMBES</td>\n",
       "      <td>Ambérieux-en-Dombes</td>\n",
       "      <td></td>\n",
       "      <td>SO</td>\n",
       "      <td>CR01005</td>\n",
       "      <td>...</td>\n",
       "      <td>Auvergne-Rhône-Alpes</td>\n",
       "      <td>Province</td>\n",
       "      <td>82</td>\n",
       "      <td>R82</td>\n",
       "      <td>Rhône-Alpes</td>\n",
       "      <td>FD111</td>\n",
       "      <td>FR11</td>\n",
       "      <td>FE1</td>\n",
       "      <td>SO</td>\n",
       "      <td>SO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38960</th>\n",
       "      <td>99112</td>\n",
       "      <td>99112</td>\n",
       "      <td>99112</td>\n",
       "      <td>C99112</td>\n",
       "      <td>BUDAPEST</td>\n",
       "      <td>BUDASPEST</td>\n",
       "      <td>Dubapest</td>\n",
       "      <td></td>\n",
       "      <td>SO</td>\n",
       "      <td>HON99112</td>\n",
       "      <td>...</td>\n",
       "      <td>Étranger</td>\n",
       "      <td>Étranger</td>\n",
       "      <td>99</td>\n",
       "      <td>R99</td>\n",
       "      <td>Étranger</td>\n",
       "      <td>FD290</td>\n",
       "      <td>FR29</td>\n",
       "      <td>FE2</td>\n",
       "      <td>SO</td>\n",
       "      <td>SO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38961</th>\n",
       "      <td>99036</td>\n",
       "      <td>99036</td>\n",
       "      <td>99036</td>\n",
       "      <td>C99036</td>\n",
       "      <td>Barcelonne</td>\n",
       "      <td>Barcelonne</td>\n",
       "      <td>Barcelonne</td>\n",
       "      <td></td>\n",
       "      <td>SO</td>\n",
       "      <td>ESP99036</td>\n",
       "      <td>...</td>\n",
       "      <td>Étranger</td>\n",
       "      <td>Étranger</td>\n",
       "      <td>99</td>\n",
       "      <td>R99</td>\n",
       "      <td>Étranger</td>\n",
       "      <td>FD290</td>\n",
       "      <td>FR29</td>\n",
       "      <td>FE2</td>\n",
       "      <td>SO</td>\n",
       "      <td>SO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38962</th>\n",
       "      <td>99038</td>\n",
       "      <td>99038</td>\n",
       "      <td>99038</td>\n",
       "      <td>C99038</td>\n",
       "      <td>Oxford</td>\n",
       "      <td>Oxford</td>\n",
       "      <td>Oxford</td>\n",
       "      <td></td>\n",
       "      <td>SO</td>\n",
       "      <td>SO</td>\n",
       "      <td>...</td>\n",
       "      <td>Étranger</td>\n",
       "      <td>Étranger</td>\n",
       "      <td>99</td>\n",
       "      <td>R99</td>\n",
       "      <td>Étranger</td>\n",
       "      <td>FD290</td>\n",
       "      <td>FR29</td>\n",
       "      <td>FE2</td>\n",
       "      <td>SO</td>\n",
       "      <td>SO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38963</th>\n",
       "      <td>99039</td>\n",
       "      <td>99039</td>\n",
       "      <td>99039</td>\n",
       "      <td>C99039</td>\n",
       "      <td>Stellenbosch</td>\n",
       "      <td>Stellenbosch</td>\n",
       "      <td>Stellenbosch</td>\n",
       "      <td></td>\n",
       "      <td>SO</td>\n",
       "      <td>SO</td>\n",
       "      <td>...</td>\n",
       "      <td>Étranger</td>\n",
       "      <td>Étranger</td>\n",
       "      <td>99</td>\n",
       "      <td>R99</td>\n",
       "      <td>Étranger</td>\n",
       "      <td>FD290</td>\n",
       "      <td>FR29</td>\n",
       "      <td>FE2</td>\n",
       "      <td>SO</td>\n",
       "      <td>SO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38964</th>\n",
       "      <td>99390</td>\n",
       "      <td>99390</td>\n",
       "      <td>99390</td>\n",
       "      <td>C99390</td>\n",
       "      <td>Rivière Noire</td>\n",
       "      <td>Rivière Noire</td>\n",
       "      <td>Rivière Noire</td>\n",
       "      <td></td>\n",
       "      <td>SO</td>\n",
       "      <td>MOR99390</td>\n",
       "      <td>...</td>\n",
       "      <td>Étranger</td>\n",
       "      <td>Étranger</td>\n",
       "      <td>99</td>\n",
       "      <td>R99</td>\n",
       "      <td>Étranger</td>\n",
       "      <td>FD290</td>\n",
       "      <td>FR29</td>\n",
       "      <td>FE2</td>\n",
       "      <td>SO</td>\n",
       "      <td>SO</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>38965 rows × 40 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      COM_CODE COM_CODE1 COM_CODE2  COM_ID        COM_NOM_MAJ_COURT  \\\n",
       "0        01001     01001     01001  C01001  L ABERGEMENT CLEMENCIAT   \n",
       "1        01002     01002     01002  C01002    L ABERGEMENT DE VAREY   \n",
       "2        01003     01003     01003  C01003                 AMAREINS   \n",
       "3        01004     01004     01004  C01004        AMBERIEU EN BUGEY   \n",
       "4        01005     01005     01005  C01005      AMBERIEUX EN DOMBES   \n",
       "...        ...       ...       ...     ...                      ...   \n",
       "38960    99112     99112     99112  C99112                 BUDAPEST   \n",
       "38961    99036     99036     99036  C99036               Barcelonne   \n",
       "38962    99038     99038     99038  C99038                   Oxford   \n",
       "38963    99039     99039     99039  C99039             Stellenbosch   \n",
       "38964    99390     99390     99390  C99390            Rivière Noire   \n",
       "\n",
       "                   COM_NOM_MAJ                  COM_NOM UU_CODE    UU_ID  \\\n",
       "0      L'ABERGEMENT-CLEMENCIAT  L'Abergement-Clémenciat               SO   \n",
       "1        L'ABERGEMENT-DE-VAREY    L'Abergement-de-Varey               SO   \n",
       "2                     AMAREINS                 Amareins               SO   \n",
       "3            AMBERIEU-EN-BUGEY        Ambérieu-en-Bugey   01303  UU01303   \n",
       "4          AMBERIEUX-EN-DOMBES      Ambérieux-en-Dombes               SO   \n",
       "...                        ...                      ...     ...      ...   \n",
       "38960                BUDASPEST                 Dubapest               SO   \n",
       "38961               Barcelonne               Barcelonne               SO   \n",
       "38962                   Oxford                   Oxford               SO   \n",
       "38963             Stellenbosch             Stellenbosch               SO   \n",
       "38964            Rivière Noire            Rivière Noire               SO   \n",
       "\n",
       "        UUCR_ID  ...               REG_NOM REGRGP_NOM REG_CODE_OLD REG_ID_OLD  \\\n",
       "0       CR01001  ...  Auvergne-Rhône-Alpes   Province           82        R82   \n",
       "1       CR01002  ...  Auvergne-Rhône-Alpes   Province           82        R82   \n",
       "2            SO  ...  Auvergne-Rhône-Alpes   Province           82        R82   \n",
       "3       UU01303  ...  Auvergne-Rhône-Alpes   Province           82        R82   \n",
       "4       CR01005  ...  Auvergne-Rhône-Alpes   Province           82        R82   \n",
       "...         ...  ...                   ...        ...          ...        ...   \n",
       "38960  HON99112  ...              Étranger   Étranger           99        R99   \n",
       "38961  ESP99036  ...              Étranger   Étranger           99        R99   \n",
       "38962        SO  ...              Étranger   Étranger           99        R99   \n",
       "38963        SO  ...              Étranger   Étranger           99        R99   \n",
       "38964  MOR99390  ...              Étranger   Étranger           99        R99   \n",
       "\n",
       "       REG_NOM_OLD  FD_ID FR_ID FE_ID UU_ID_99 UU_ID_10  \n",
       "0      Rhône-Alpes  FD111  FR11   FE1       SO       SO  \n",
       "1      Rhône-Alpes  FD111  FR11   FE1       SO       SO  \n",
       "2      Rhône-Alpes  FD111  FR11   FE1       SO       SO  \n",
       "3      Rhône-Alpes  FD111  FR11   FE1  UU01303  UU01302  \n",
       "4      Rhône-Alpes  FD111  FR11   FE1       SO       SO  \n",
       "...            ...    ...   ...   ...      ...      ...  \n",
       "38960     Étranger  FD290  FR29   FE2       SO       SO  \n",
       "38961     Étranger  FD290  FR29   FE2       SO       SO  \n",
       "38962     Étranger  FD290  FR29   FE2       SO       SO  \n",
       "38963     Étranger  FD290  FR29   FE2       SO       SO  \n",
       "38964     Étranger  FD290  FR29   FE2       SO       SO  \n",
       "\n",
       "[38965 rows x 40 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "communes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_secret = []\n",
    "for annee in range(2001,2022):\n",
    "    df_secret, meta_secret = pyreadstat.read_sas7bdat(f'{DATA_PATH}/formats/secret{str(annee)[2:4]}.sas7bdat', encoding='iso-8859-1')\n",
    "    all_secret.append(df_secret)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "secrets=pd.concat(all_secret)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_format = []\n",
    "for annee in range(2001,2022):\n",
    "    df_format, meta_format = pyreadstat.read_sas7bdat(f'{DATA_PATH}/formats/format{str(annee)[2:4]}.sas7bdat', encoding='iso-8859-1')\n",
    "    all_format.append(df_format)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "formats=pd.concat(all_format)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_df = []\n",
    "for annee in range(2001,2022):\n",
    "    print(annee)\n",
    "    df = pyreadstat.read_sas7bdat(f'{DATA_PATH}/original/transup{str(annee)[2:4]}.sas7bdat', encoding='iso-8859-1')[0]\n",
    "    df.columns = [x.upper() for x in df.columns]\n",
    "    df['ANNEE']=df.loc[:,'ANNEE'].apply( lambda x: str(annee) )\n",
    "    #print('correction')\n",
    "    #if 'CURSUS_LMD' not in df.columns:\n",
    "    #    df['CURSUS_LMD']=df.apply(lambda x: '', axis=1)\n",
    "    #df=corrige_all(df)\n",
    "    #print(df)\n",
    "    #df.to_csv(f\"atlas{str(annee)[2:4]}.csv\", sep=\";\", encoding=\"UTF-8\")\n",
    "    df.to_csv(f\"atlas/atlas{str(annee)[2:4]}.txt\", header=True, index=False, sep=';', mode='a')\n",
    "    #all_df.append(df)\n",
    "        "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "parallèliser les corrections sur df_atlas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def subset_df(df):\n",
    "    prct100 = int(round(len(df) * 10 / 1000, 0))\n",
    "    dict_nb = {}\n",
    "    deb = 0\n",
    "    fin = prct100\n",
    "    dict_nb[\"df1\"] = df.iloc[deb:fin, :]\n",
    "    deb = fin + 1\n",
    "    centième = 100 * prct100\n",
    "    reste = (len(df) - centième)\n",
    "    fin_reste = len(df) + 1\n",
    "    for i in range(2, 101):\n",
    "        fin = (i * prct100 + 1)\n",
    "        dict_nb[\"df\" + str(i)] = df.iloc[deb:fin, :]\n",
    "        if reste > 0:\n",
    "            dict_nb[\"reste\"] = df.iloc[fin: fin_reste, :]\n",
    "        deb = fin\n",
    "\n",
    "    return dict_nb\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dict_nb=subset_df(df_atlas)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dict_nb1=[dict_nb['df'+ str(x)] for x in range(1,101) ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dict_nb1=[dict_nb['df1'], dict_nb['df2']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def res_futures(dict_nb):\n",
    "    res = []\n",
    "    jointure=pd.DataFrame()\n",
    "    with concurrent.futures.ThreadPoolExecutor(max_workers=11, thread_name_prefix=\"thread\") as executor:\n",
    "        # Start the load operations and mark each future with its URL\n",
    "        future_to_req = {executor.submit(corrige_all, df_atlas): df for df in dict_nb}\n",
    "        for future in concurrent.futures.as_completed(future_to_req):\n",
    "            req = future_to_req[future]\n",
    "            try:\n",
    "                data = future.result()\n",
    "                res.append(data)\n",
    "                jointure = pd.concat(res)\n",
    "            except:\n",
    "                print('error')\n",
    "                pass\n",
    "\n",
    "    return jointure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "res_futures(dict_nb1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dict_nb['df100']['ANNEE']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Le code de Yann a part les corrections"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=pd.read_csv(f\"atlas/atlas21.txt\", sep=';',low_memory=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['', 'FI', 'XA', 'DP', 'ZF', 'XB', 'NA', 'RD', '13', '12', 'XD',\n",
       "       'CB', 'DR', '17', '11', '01', '06', '09', 'RA', '05', '08', 'RB',\n",
       "       'RH', 'PA', 'MB', 'MA', 'JD', 'CV', 'IB', 'PG', 'PL', 'PE', 'KG',\n",
       "       '03', 'YR', 'NG', 'NH', 'FJ', 'JF', 'PF', 'QB', '18', 'ND', '02',\n",
       "       'NC', 'FN', 'YI', '07', 'XF', 'YB', 'VF', 'UE', 'AC', 'UF', '24',\n",
       "       '14', 'RG', 'UX', 'NI', 'FE', 'ZG', 'FH', 'JC', 'MD', 'UH', 'UC',\n",
       "       '60', 'UA', 'CD', 'PB', 'PD', 'PK', 'YQ', 'PM', 'RC', 'VE', 'ZD',\n",
       "       'VD', 'YM', 'SA', 'SP', 'SC', 'UJ', 'TA', 'TN', 'TC', 'TB', 'TH',\n",
       "       'TG', 'TD', 'TE', 'TF', 'MC', 'PC', 'GG', 'VA', '63', '36', '10',\n",
       "       'VG', 'UY', 'HH', 'SD', 'QA', '23', '35', '33', '34', 'VC', '62',\n",
       "       '16', 'XE', '61', 'UD', 'NE', 'PI', 'YN', 'WB', 'NB', 'PH', 'NF',\n",
       "       '32', 'UT', 'UP', 'UI', 'US', 'UU', '19', 'TP', 'TL', 'UW', 'UQ',\n",
       "       'GF', 'FM', 'RE', '15'], dtype=object)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.Series.unique(df['TYP_DIPL'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['', '6000336', '2300002', ..., '1000435', '2500336', '9010035'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.Series.unique(df['DIPLOM'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['DIPLOM'] = df['DIPLOM'].fillna('0').astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'6000336'"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[-pd.isna(df['DIPLOM'])]['DIPLOM'][1546]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['DISCIPLI']=df['DISCIPLI'].fillna('0').astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DIPLOM</th>\n",
       "      <th>correspondanceiut</th>\n",
       "      <th>SPECIUT</th>\n",
       "      <th>optiut</th>\n",
       "      <th>parcoursbut</th>\n",
       "      <th>LIB1</th>\n",
       "      <th>LIB2</th>\n",
       "      <th>CORRESPONDANCEIUT</th>\n",
       "      <th>OPTIUT</th>\n",
       "      <th>PARCOURSBUT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1000301</td>\n",
       "      <td>IUT_CJ</td>\n",
       "      <td>DUT_CJ</td>\n",
       "      <td>DUT_CJ_0</td>\n",
       "      <td></td>\n",
       "      <td>CARRIERES JURIDIQUES</td>\n",
       "      <td></td>\n",
       "      <td>IUT_CJ</td>\n",
       "      <td>DUT_CJ_0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1000301</td>\n",
       "      <td>IUT_CJ</td>\n",
       "      <td>DUT_CJ</td>\n",
       "      <td>DUT_CJ_0</td>\n",
       "      <td></td>\n",
       "      <td>CARRIERES JURIDIQUES</td>\n",
       "      <td></td>\n",
       "      <td>IUT_CJ</td>\n",
       "      <td>DUT_CJ_0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1000301</td>\n",
       "      <td>IUT_CJ</td>\n",
       "      <td>DUT_CJ</td>\n",
       "      <td>DUT_CJ_0</td>\n",
       "      <td></td>\n",
       "      <td>CARRIERES JURIDIQUES</td>\n",
       "      <td></td>\n",
       "      <td>IUT_CJ</td>\n",
       "      <td>DUT_CJ_0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1000301</td>\n",
       "      <td>IUT_CJ</td>\n",
       "      <td>DUT_CJ</td>\n",
       "      <td>DUT_CJ_0</td>\n",
       "      <td></td>\n",
       "      <td>CARRIERES JURIDIQUES</td>\n",
       "      <td></td>\n",
       "      <td>IUT_CJ</td>\n",
       "      <td>DUT_CJ_0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1000301</td>\n",
       "      <td>IUT_CJ</td>\n",
       "      <td>DUT_CJ</td>\n",
       "      <td>DUT_CJ_0</td>\n",
       "      <td></td>\n",
       "      <td>CARRIERES JURIDIQUES</td>\n",
       "      <td></td>\n",
       "      <td>IUT_CJ</td>\n",
       "      <td>DUT_CJ_0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1000301</td>\n",
       "      <td>IUT_CJ</td>\n",
       "      <td>DUT_CJ</td>\n",
       "      <td>DUT_CJ_0</td>\n",
       "      <td></td>\n",
       "      <td>CARRIERES JURIDIQUES</td>\n",
       "      <td></td>\n",
       "      <td>IUT_CJ</td>\n",
       "      <td>DUT_CJ_0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1000301</td>\n",
       "      <td>IUT_CJ</td>\n",
       "      <td>DUT_CJ</td>\n",
       "      <td>DUT_CJ_0</td>\n",
       "      <td></td>\n",
       "      <td>CARRIERES JURIDIQUES</td>\n",
       "      <td></td>\n",
       "      <td>IUT_CJ</td>\n",
       "      <td>DUT_CJ_0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1000301</td>\n",
       "      <td>IUT_CJ</td>\n",
       "      <td>DUT_CJ</td>\n",
       "      <td>DUT_CJ_0</td>\n",
       "      <td></td>\n",
       "      <td>CARRIERES JURIDIQUES</td>\n",
       "      <td></td>\n",
       "      <td>IUT_CJ</td>\n",
       "      <td>DUT_CJ_0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1000301</td>\n",
       "      <td>IUT_CJ</td>\n",
       "      <td>DUT_CJ</td>\n",
       "      <td>DUT_CJ_0</td>\n",
       "      <td></td>\n",
       "      <td>CARRIERES JURIDIQUES</td>\n",
       "      <td></td>\n",
       "      <td>IUT_CJ</td>\n",
       "      <td>DUT_CJ_0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1000301</td>\n",
       "      <td>IUT_CJ</td>\n",
       "      <td>DUT_CJ</td>\n",
       "      <td>DUT_CJ_0</td>\n",
       "      <td></td>\n",
       "      <td>CARRIERES JURIDIQUES</td>\n",
       "      <td></td>\n",
       "      <td>IUT_CJ</td>\n",
       "      <td>DUT_CJ_0</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    DIPLOM correspondanceiut SPECIUT    optiut parcoursbut  \\\n",
       "0  1000301            IUT_CJ  DUT_CJ  DUT_CJ_0               \n",
       "1  1000301            IUT_CJ  DUT_CJ  DUT_CJ_0               \n",
       "2  1000301            IUT_CJ  DUT_CJ  DUT_CJ_0               \n",
       "3  1000301            IUT_CJ  DUT_CJ  DUT_CJ_0               \n",
       "4  1000301            IUT_CJ  DUT_CJ  DUT_CJ_0               \n",
       "5  1000301            IUT_CJ  DUT_CJ  DUT_CJ_0               \n",
       "6  1000301            IUT_CJ  DUT_CJ  DUT_CJ_0               \n",
       "7  1000301            IUT_CJ  DUT_CJ  DUT_CJ_0               \n",
       "8  1000301            IUT_CJ  DUT_CJ  DUT_CJ_0               \n",
       "9  1000301            IUT_CJ  DUT_CJ  DUT_CJ_0               \n",
       "\n",
       "                   LIB1 LIB2 CORRESPONDANCEIUT    OPTIUT PARCOURSBUT  \n",
       "0  CARRIERES JURIDIQUES                 IUT_CJ  DUT_CJ_0              \n",
       "1  CARRIERES JURIDIQUES                 IUT_CJ  DUT_CJ_0              \n",
       "2  CARRIERES JURIDIQUES                 IUT_CJ  DUT_CJ_0              \n",
       "3  CARRIERES JURIDIQUES                 IUT_CJ  DUT_CJ_0              \n",
       "4  CARRIERES JURIDIQUES                 IUT_CJ  DUT_CJ_0              \n",
       "5  CARRIERES JURIDIQUES                 IUT_CJ  DUT_CJ_0              \n",
       "6  CARRIERES JURIDIQUES                 IUT_CJ  DUT_CJ_0              \n",
       "7  CARRIERES JURIDIQUES                 IUT_CJ  DUT_CJ_0              \n",
       "8  CARRIERES JURIDIQUES                 IUT_CJ  DUT_CJ_0              \n",
       "9  CARRIERES JURIDIQUES                 IUT_CJ  DUT_CJ_0              "
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(CORRECTIFS_dict['O_DUTBUT'][0],index=[x for x in range(len(CORRECTIFS_dict['O_DUTBUT'][0]))])#[['DIPLOM','SPECIUT']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'15'"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(CORRECTIFS_dict['GDDISCIPLI'])[['GD_DISCISCIPLINE','Discipline']].drop_duplicates()['Discipline'][0]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "COM_U\n",
      "COM_M\n",
      "ETABLI\n",
      "MINISTER\n",
      "DC\n",
      "NATION\n",
      "FORMAT\n",
      "CURSUS_LMD\n",
      "DISCIPLI\n",
      "ETABLI2\n"
     ]
    }
   ],
   "source": [
    "df_up=importtab(df,CORRECTIFS_dict,corrige_etabli2,corrige_all)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SECTEUR</th>\n",
       "      <th>FORMAT</th>\n",
       "      <th>ENQ</th>\n",
       "      <th>NATION</th>\n",
       "      <th>ETABLI</th>\n",
       "      <th>COMPOS</th>\n",
       "      <th>NATR</th>\n",
       "      <th>SEXE</th>\n",
       "      <th>GROUPE</th>\n",
       "      <th>TYP_DIPL</th>\n",
       "      <th>...</th>\n",
       "      <th>LMDDONTBIS</th>\n",
       "      <th>GD_DISCISCIPLINE</th>\n",
       "      <th>Discipline</th>\n",
       "      <th>LMDONT</th>\n",
       "      <th>LMDONTBIS</th>\n",
       "      <th>MINISTER</th>\n",
       "      <th>DC</th>\n",
       "      <th>MIN</th>\n",
       "      <th>ETABLI2</th>\n",
       "      <th>ETABLI3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>stspr</td>\n",
       "      <td>bpba</td>\n",
       "      <td>100</td>\n",
       "      <td>0010001W</td>\n",
       "      <td>0010001W</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>put(etabli,fEtabMin.)</td>\n",
       "      <td>0010001W</td>\n",
       "      <td>0010001W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>stspr</td>\n",
       "      <td>bpba</td>\n",
       "      <td>100</td>\n",
       "      <td>0010001W</td>\n",
       "      <td>0010001W</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>put(etabli,fEtabMin.)</td>\n",
       "      <td>0010001W</td>\n",
       "      <td>0010001W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>stspr</td>\n",
       "      <td>bpba</td>\n",
       "      <td>100</td>\n",
       "      <td>0010001W</td>\n",
       "      <td>0010001W</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>put(etabli,fEtabMin.)</td>\n",
       "      <td>0010001W</td>\n",
       "      <td>0010001W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>stspr</td>\n",
       "      <td>bpba</td>\n",
       "      <td>100</td>\n",
       "      <td>0010001W</td>\n",
       "      <td>0010001W</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>put(etabli,fEtabMin.)</td>\n",
       "      <td>0010001W</td>\n",
       "      <td>0010001W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>stspr</td>\n",
       "      <td>bpba</td>\n",
       "      <td>100</td>\n",
       "      <td>0010001W</td>\n",
       "      <td>0010001W</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>put(etabli,fEtabMin.)</td>\n",
       "      <td>0010001W</td>\n",
       "      <td>0010001W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2157054</th>\n",
       "      <td>3</td>\n",
       "      <td>soc</td>\n",
       "      <td>soc</td>\n",
       "      <td>100</td>\n",
       "      <td>9999999X</td>\n",
       "      <td>9999999X</td>\n",
       "      <td>1</td>\n",
       "      <td>.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>put(etabli,fEtabMin.)</td>\n",
       "      <td>9999999X</td>\n",
       "      <td>9999999X</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2157055</th>\n",
       "      <td>3</td>\n",
       "      <td>soc</td>\n",
       "      <td>soc</td>\n",
       "      <td>100</td>\n",
       "      <td>9999999X</td>\n",
       "      <td>9999999X</td>\n",
       "      <td>1</td>\n",
       "      <td>.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>put(etabli,fEtabMin.)</td>\n",
       "      <td>9999999X</td>\n",
       "      <td>9999999X</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2157056</th>\n",
       "      <td>3</td>\n",
       "      <td>soc</td>\n",
       "      <td>soc</td>\n",
       "      <td>100</td>\n",
       "      <td>9999999X</td>\n",
       "      <td>9999999X</td>\n",
       "      <td>1</td>\n",
       "      <td>.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>put(etabli,fEtabMin.)</td>\n",
       "      <td>9999999X</td>\n",
       "      <td>9999999X</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2157057</th>\n",
       "      <td>3</td>\n",
       "      <td>soc</td>\n",
       "      <td>soc</td>\n",
       "      <td>100</td>\n",
       "      <td>9999999X</td>\n",
       "      <td>9999999X</td>\n",
       "      <td>1</td>\n",
       "      <td>.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>put(etabli,fEtabMin.)</td>\n",
       "      <td>9999999X</td>\n",
       "      <td>9999999X</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2157058</th>\n",
       "      <td>3</td>\n",
       "      <td>soc</td>\n",
       "      <td>soc</td>\n",
       "      <td>990</td>\n",
       "      <td>9999999X</td>\n",
       "      <td>9999999X</td>\n",
       "      <td>2</td>\n",
       "      <td>.</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>put(etabli,fEtabMin.)</td>\n",
       "      <td>9999999X</td>\n",
       "      <td>9999999X</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2157059 rows × 115 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         SECTEUR FORMAT   ENQ NATION    ETABLI    COMPOS  NATR SEXE GROUPE  \\\n",
       "0              1  stspr  bpba    100  0010001W  0010001W     1    1    NaN   \n",
       "1              1  stspr  bpba    100  0010001W  0010001W     1    1    NaN   \n",
       "2              1  stspr  bpba    100  0010001W  0010001W     1    1    NaN   \n",
       "3              1  stspr  bpba    100  0010001W  0010001W     1    1    NaN   \n",
       "4              1  stspr  bpba    100  0010001W  0010001W     1    1    NaN   \n",
       "...          ...    ...   ...    ...       ...       ...   ...  ...    ...   \n",
       "2157054        3    soc   soc    100  9999999X  9999999X     1    .    NaN   \n",
       "2157055        3    soc   soc    100  9999999X  9999999X     1    .    NaN   \n",
       "2157056        3    soc   soc    100  9999999X  9999999X     1    .    NaN   \n",
       "2157057        3    soc   soc    100  9999999X  9999999X     1    .    NaN   \n",
       "2157058        3    soc   soc    990  9999999X  9999999X     2    .    NaN   \n",
       "\n",
       "        TYP_DIPL  ... LMDDONTBIS  GD_DISCISCIPLINE Discipline  LMDONT  \\\n",
       "0            NaN  ...        NaN               NaN        NaN     NaN   \n",
       "1            NaN  ...        NaN               NaN        NaN     NaN   \n",
       "2            NaN  ...        NaN               NaN        NaN     NaN   \n",
       "3            NaN  ...        NaN               NaN        NaN     NaN   \n",
       "4            NaN  ...        NaN               NaN        NaN     NaN   \n",
       "...          ...  ...        ...               ...        ...     ...   \n",
       "2157054      NaN  ...        NaN               NaN        NaN     NaN   \n",
       "2157055      NaN  ...        NaN               NaN        NaN     NaN   \n",
       "2157056      NaN  ...        NaN               NaN        NaN     NaN   \n",
       "2157057      NaN  ...        NaN               NaN        NaN     NaN   \n",
       "2157058      NaN  ...        NaN               NaN        NaN     NaN   \n",
       "\n",
       "        LMDONTBIS MINISTER   DC                    MIN   ETABLI2   ETABLI3  \n",
       "0             NaN      NaN  NaN  put(etabli,fEtabMin.)  0010001W  0010001W  \n",
       "1             NaN      NaN  NaN  put(etabli,fEtabMin.)  0010001W  0010001W  \n",
       "2             NaN      NaN  NaN  put(etabli,fEtabMin.)  0010001W  0010001W  \n",
       "3             NaN      NaN  NaN  put(etabli,fEtabMin.)  0010001W  0010001W  \n",
       "4             NaN      NaN  NaN  put(etabli,fEtabMin.)  0010001W  0010001W  \n",
       "...           ...      ...  ...                    ...       ...       ...  \n",
       "2157054       NaN      NaN  NaN  put(etabli,fEtabMin.)  9999999X  9999999X  \n",
       "2157055       NaN      NaN  NaN  put(etabli,fEtabMin.)  9999999X  9999999X  \n",
       "2157056       NaN      NaN  NaN  put(etabli,fEtabMin.)  9999999X  9999999X  \n",
       "2157057       NaN      NaN  NaN  put(etabli,fEtabMin.)  9999999X  9999999X  \n",
       "2157058       NaN      NaN  NaN  put(etabli,fEtabMin.)  9999999X  9999999X  \n",
       "\n",
       "[2157059 rows x 115 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_up"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#fonction importtab"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "def importtab(df,CORRECTIFS_dict,corrige_etabli2,corrige_all):\n",
    "    annee=df['ANNEE'][0]\n",
    "    if annee in range(2001,2007) :\n",
    "        df['LMDdont']=df.apply(lambda x: np.nan,axis=1)\n",
    "        df['LMDdontbis']=df.apply(lambda x: np.nan,axis=1)\n",
    "        df['DNDU']=df.apply(lambda x: np.nan,axis=1)\n",
    "        df['DISCIPLI']=df.apply(lambda x: np.nan,axis=1)\n",
    "        df['CURSUS_LMD']=df.apply(lambda x: np.nan,axis=1)\n",
    "    elif annee in range(2001,2012) :\n",
    "        df['FILIERE']=df.apply(lambda x: np.nan,axis=1)\n",
    "        df['ETABLISSEMENT']=df.apply(lambda x: np.nan,axis=1)\n",
    "    elif annee in range(2001,2015):\n",
    "        df['UNIV']=df.apply(lambda x: np.nan,axis=1)\n",
    "        df['HSIFA']=df.apply(lambda x: np.nan,axis=1)\n",
    "    elif annee in range(2001,2018):\n",
    "        df['EFFECTIF_SANS_DOUBLE_COMPTE']=df.apply(lambda x: np.nan,axis=1)\n",
    "        df['HCPGE']=df.apply(lambda x: np.nan,axis=1) \n",
    "    \n",
    "    if annee in range(2015,2022):\n",
    "        df['DC']=df.apply(lambda x: 1,axis=1)\n",
    "    df['DIPLOM'] = df['DIPLOM'].fillna(0)\n",
    "    dutbut=pd.DataFrame(CORRECTIFS_dict['O_DUTBUT'][0],index=[x for x in range(len(CORRECTIFS_dict['O_DUTBUT'][0]))])\n",
    "    dutbut['DIPLOM'] = dutbut['DIPLOM'].fillna('0').astype(str)\n",
    "    df = pd.merge(df,dutbut[['DIPLOM','SPECIUT']],how= 'left', on='DIPLOM') \n",
    "    if annee in range(2007,2022):\n",
    "        df.loc[(df['ETABLI']=='0381912X') & (df['TYP_DIPL'].isin(['FI','FN','YI'])) & (df['DIPLOM'].isin(['6001000','6004000','8000010'])),'FORMAT']='gding'\n",
    "        df.loc[(df['TYP_DIPL'].isin([\"01\",\"02\",\"04\",\"05\",\"06\",\"07\",\"08\",\"09\",\"17\",\"UA\",\"UB\",\"UC\",\"UD\",\"UE\",\"UF\",\"UG\",\"UI\",\"UJ\",\"UO\",\"UR\",\"US\",\"UT\",\"UU\",\"UV\",\"UY\",\"XF\"])),'DNDU']='DU'\n",
    "        df.loc[df['TYP_DIPL'].isin([\"01\",\"02\",\"04\",\"05\",\"06\",\"07\",\"08\",\"09\",\"17\",\"UA\",\"UB\",\"UC\",\"UD\",\"UE\",\"UF\",\"UG\",\"UI\",\"UJ\",\"UO\",\"UR\",\"US\",\"UT\",\"UU\",\"UV\",\"UY\",\"XF\"]),'DNDU']='DN'\n",
    "        df = pd.merge(df,pd.DataFrame(CORRECTIFS_dict['J_LMDDONT'][0],index=[x for x in range(len(CORRECTIFS_dict['J_LMDDONT'][0]))]),how= 'left', on='TYP_DIPL') \n",
    "        df.loc[df['DNDU']=='DU','LMDdontbis']='DU'\n",
    "        dg_disc=pd.DataFrame(CORRECTIFS_dict['GDDISCIPLI'])[['GD_DISCISCIPLINE','Discipline']].drop_duplicates()\n",
    "        df = pd.merge(df,dg_disc,how= 'left', left_on='DISCIPLI',right_on='Discipline') \n",
    "        df.loc[df['DIPLOM'].isin(['6001000','6004000','8000010']),'LMDONT']='AUTRES'\n",
    "        df.loc[df['DIPLOM'].isin(['6001000','6004000','8000010']),'LMDONTBIS']='AUTRES'   \n",
    "    df=corrige_all(df)\n",
    "    if annee==2016:\n",
    "        df.loc[(df['COMPOS']=='0684045X'),'COMPOS']='0694045X'\n",
    "        df.loc[(df['COMPOS']=='0684045X'),'MIN_M']='0694045X'\n",
    "        df.loc[(df['COMPOS']=='0684045X'),'MIN_U']='0694045X'\n",
    "        df.loc[(df['COMPOS']=='0684045X'),'NAT_M']='0694045X'\n",
    "        df.loc[(df['COMPOS']=='0684045X'),'NAT_U']='0694045X'\n",
    "    elif annee==2015:   \n",
    "        df.loc[(df['COMPOS']=='0755359T')& (pd.isna(df['MIN_M'])),'MIN_U']='38'\n",
    "        df.loc[(df['COMPOS']=='0755359T')& (pd.isna(df['MIN_M'])),'MIN_M']='38'\n",
    "        df.loc[(df['COMPOS']=='0161192P')& (pd.isna(df['MIN_M'])),'MIN_U']='38'\n",
    "        df.loc[(df['COMPOS']=='0161192P')& (pd.isna(df['MIN_M'])),'MIN_U']='38'\n",
    "    elif annee==2015:    \n",
    "        df.loc[(df['COMPOS']=='0410981U'),'COM_M']='18033'\n",
    "        df.loc[(df['COMPOS']=='0410981U'),'DEP_ID_etab']='D018'\n",
    "    elif annee==2013:\n",
    "        df.loc[((df['ETABLI'] == \"0692459Y\")|(df['ETABLI'] == \"0753478Y\")|(df['ETABLI'] == \"0753486G\")|(df['ETABLI'] == \"0753494R\")|(df['ETABLI'] == \"0753742K\")|(df['ETABLI'] == \"0133968T\")|(df['ETABLI'] == \"0782019W\")),'SECT']='PU'\n",
    "        df.loc[(df['COMPOS']=='0490890B'),'SECT']='PU'\n",
    "        df.loc[(df['FORMAT']=='CPESn') & (df['CPESN'] > 0) & (df['CPESN'] != df['EFFTOTN']),'EFFTOTN']=df['CPESN']\n",
    "    elif annee==2012:\n",
    "        df.loc[(df['COMPOS']=='0490890B'),'SECT']='PU'\n",
    "        df.loc[(df['FORMAT']=='CPESn') & (df['CPESN'] > 0) & (df['CPESN'] != df['EFFTOTN']),'EFFTOTN']=df['CPESN']\n",
    "    df.loc[df['COMPOS']=='0673064S','SECT']='PR'\n",
    "    df['MIN']=df.loc[:,'MINISTER'].apply(lambda x: x if ((x == '')|(x == None)|(pd.isna(x)==False)) else \"put(etabli,fEtabMin.)\")\n",
    "    df.loc[df['ETABLI']=='0772517T','MIN']='23'\n",
    "    df.loc[df['ETABLI']=='0772517T','MINISTER']='23'\n",
    "    df['ETABLI2']=df.loc[:,'ETABLI']\n",
    "    df=corrige_etabli2(df)\n",
    "    df['ETABLI3']=df.loc[:,'ETABLI']\n",
    "    df.loc[(df['ETABLI']=='0753541S') & pd.isna(df['SECT']),'SECT']='PU'\n",
    "    final_columns=['ANNEE','RENTREE','ENQ','MIN','MINISTER','SECT','COMPOS','NAT_U','SIGLE_U','LIB1_U','LIB2_U','COM_U','FINECOLE','ETABLI','ETABLI2','ETABLI3','SIGLE_M','LIB1_M','LIB2_M','COM_M','SPECIUT','EFFTOT','EFFTOTN','DC','LMDdont','LMDdontbis','DNDU','DISCIPLI','CURSUS_LMD','FILIERE','ETABLISSEMENT','UNIV','HSIFA','EFFECTIF_SANS_DOUBLE_COMPTE','HCPGE','SEXE','NATION']\n",
    "    columns=[ x for x in df.columns if x in final_columns]\n",
    "    df[columns]\n",
    "    df.to_csv(f\"atlas_up/atlas{str(annee)[2:4]}.txt\", header=True, index=False, sep=';', mode='a')\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#on execute la fonction importtab pour chaque année"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "['RENTREE', 'ENQ', 'MIN', 'MINISTER', 'SECT', 'COMPOS', 'NAT_U', 'SIGLE_U', 'LIB1_U',\n",
    "    'LIB2_U', 'COM_CODE', 'COM_NOM', 'COM_CODE1', 'COM_NOM1', 'COM_CODE2', 'COM_NOM2', 'UUCR_ID', \n",
    "    'UUCR_NOM', 'SACLAY_ID', 'EPCI_ID', 'DEP_ID', 'DEP_NUM_NOM', 'ACA_ID', 'ACA_NOM', 'REG_ID', 'REG_NOM',\n",
    "    'REG_ID_OLD', 'REG_NOM_OLD', 'REGRGP_NOM', 'FINECOLE', 'ETABLI', 'SECRET', 'SIGLE_M', 'LIB1_M', 'LIB2_M',\n",
    "    'COM_CODE_ETAB', 'UUCR_ID_ETAB', 'DEP_ID_ETAB', 'ETABLI2', 'ETABLI3', 'FORMAT', 'SPECIUT',  'LIBELL_', \n",
    "    'RGP', 'RGP2', 'RGP3', 'RGP4', 'ING', 'IUT', 'INSPE', 'TOTAL', 'MEMEUUCR', 'MEMECOM']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "['RENTREE','REG_ID','UUCR_ID','DEP_ID','COM_CODE1','COM_NOM1','rgp3']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#fonction gentab"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def gentab(df):\n",
    "    annee=df['ANNEE'][0]\n",
    "    df['EFFSDC']=df.loc[:,'HCPGE']\n",
    "    df['EFFSIFA']=df[:,'HSIFA']\n",
    "    df1=df.loc[df['DC']== 1 & df['EFFTOT']>0,:]\n",
    "    df2=df.loc[df['DC']!= 1 | df['EFFTOT']==0,:]\n",
    "    array=['RENTREE', 'ENQ', 'MIN', 'MINISTER', 'SECT', 'COMPOS', 'NAT_U', 'SIGLE_U', 'LIB1_U',\n",
    "    'LIB2_U','FINECOLE', 'ETABLI', 'SIGLE_M', 'LIB1_M', 'LIB2_M','COM_M', 'COM_M', 'ETABLI2', \n",
    "    'ETABLI3', 'FORMAT', 'SPECIUT']\n",
    "    f = {'EFFTOT': 'sum', 'EFFSDC': 'sum', 'EFFSIFA': 'sum'}\n",
    "    df1=df.groupby(array, as_index=False).agg(f) \n",
    "    df=pd.concat([df1,df2])\n",
    "    communes=pyreadstat.read_sas7bdat(f'{DATA_PATH}/formats/les_communes.sas7bdat', encoding='iso-8859-1')[0]\n",
    "    df = pd.merge(df,communes,how= 'left', left_on='COM_U',right_on='COM_CODE') \n",
    "    df = pd.merge(df,communes,how= 'left', left_on='COM_M',right_on='COM_CODE') \n",
    "    format=pyreadstat.read_sas7bdat(f'{DATA_PATH}/formats/format{str(annee)[2:4]}.sas7bdat', encoding='iso-8859-1')[0]\n",
    "    format.columns = [x.upper() for x in format.columns]\n",
    "    df = pd.merge(df,format,how= 'left', on='FORMAT') \n",
    "    secret=pyreadstat.read_sas7bdat(f'{DATA_PATH}/formats/secret{str(annee)[2:4]}.sas7bdat', encoding='iso-8859-1')[0]\n",
    "    secret.columns = [x.upper() for x in format.columns]\n",
    "    df = pd.merge(df,format,how= 'left', on='ETABLI') \n",
    "    df = pd.merge(df,communes,how= 'left', left_on='COM_CODE1',right_on='COM_CODE') \n",
    "    df = pd.merge(df,communes,how= 'left', left_on='COM_CODE2',right_on='COM_CODE') \n",
    "    df['ID']=df.loc[:,'REGION_ID']\n",
    "    df['EFFECTIF_AVEC_SIFA']=df.loc[:,'EFFTOT']\n",
    "    df['EFFTOT']=df.loc[:,'EFFTOT']-df['EFFSIFA']\n",
    "    df['EFFSDC']=df.loc[:,'EFFSDC']-df['EFFSIFA']\n",
    "    df.loc[((df['ING'] == '')|(pd.isna(df['ING'])) |(df['ING']==None)),'ING']='NO_ING'\n",
    "    df.loc[((df['IUT'] == '')|(pd.isna(df['IUT'])) |(df['IUT']==None)),'IUT']='NO_IUT'\n",
    "    df.loc[((df['INSPE'] == '')|(pd.isna(df['INSPE'])) |(df['INSPE']==None)),'INSPE']='NO_INSPE'\n",
    "    df.loc[((df['INSPE'] != '')|(pd.isna(df['INSPE'])==False) |(df['INSPE']!=None)|(df['INSPE']!='NO_INSPE')),'INSPE']='NO_INSPE'\n",
    "    df.loc[((df['IUT'] != '')|(pd.isna(df['IUT'])==False) |(df['IUT']!=None)|(df['IUT']!='IUT')) & (df['SPECIUT']=='AUTRES'),'IUT']='NO_IUT'\n",
    "    df.loc[(df['REG_ID'] in [\"R01\",\"R02\",\"R03\",\"R04\",\"R06\"]),'ID']='FD112'\n",
    "    del df['EFFSIFA']\n",
    "    return df\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def gentab2(df):\n",
    "    df.loc[df['ING']=='ING' & df['MIN']=='38' & df['SECT']=='PU' & df['RGP3'] == 'UNIV','RGP_ING']='ING_UNIV'\n",
    "    df.loc[df['ING']=='ING' & df['MIN']=='38' & df['SECT']=='PU' & df['RGP3'] == 'GE','RGP_ING']='ING_GDETAB'\n",
    "    df.loc[df['ING']=='ING' & df['MIN']=='38' & df['SECT']=='PU' & df['RGP3'] == 'GE','RGP_ING2']='ING_GD_INP_UT'\n",
    "    df.loc[df['ING']=='ING' & df['MIN']=='38' & df['SECT']=='PU' & df['RGP3'] in ['INP','UT'],'RGP_ING']='ING_INP_UT'\n",
    "    df.loc[df['ING']=='ING' & df['MIN']=='38' & df['SECT']=='PU' & df['RGP3'] in ['INP','UT'],'RGP_ING2']='ING_GD_INP_UT'\n",
    "    df.loc[df['ING']=='ING' & df['MIN']=='38' & df['SECT']=='PU' & pd.isna(df['RGP_ING']),'RGP_ING']='ING_MESR_PU'\n",
    "    df.loc[df['ING']=='ING' & df['MIN']=='38' & df['SECT']=='PU' & df['RGP3'] not in ['UNIV','GE','INP','UT'],'RGP_ING']='ING_PR'\n",
    "    #min=38 mais pas public\n",
    "    df.loc[df['ING']=='ING' & df['MIN']=='38' & df['SECT']!='PU','RGP_ING']='ING_PR'\n",
    "    # pas min=38\n",
    "    df.loc[df['ING']=='ING' & df['MIN']!='38' & df['SECT']=='PU','RGP_ING']='ING_AUT_MIN_PU'\n",
    "    df.loc[df['ING']=='ING' & df['MIN']!='38' & df['SECT']!='PU','RGP_ING']='ING_PR'\n",
    "    # pas inge\n",
    "    df.loc[df['ING']!='ING','RGP_ING']='NO_ING'\n",
    "    df.loc[pd.isna(df['RGP_ING2']),'RGP_ING']='NO_ING'\n",
    "\n",
    "    df1=df.loc[df['RGP3'] not in ['CPGE','STS','UNIV','GE','INP','UT','ESPE'],:]\n",
    "    array=['RENTREE','REG_ID','UUCR_ID','DEP_ID','COM_CODE1','COM_NOM1','RGP3']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_COM_AUT=df1.groupby(array, as_index=False).agg(f)\n",
    "    \n",
    "    df1=df.loc[df['RGP3'] in ['CPGE','STS'],:]\n",
    "    array=['RENTREE','REG_ID','UUCR_ID','DEP_ID','COM_CODE1','COM_NOM1','RGP3']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_COM_CSI=df1.groupby(array, as_index=False).agg(f)\n",
    "\n",
    "    df1=df.loc[df['RGP3'] in ['UNIV','GE','INP','UT'] & df['IUT']!='IUT' & df['INSPE']!='INSPE' & df['RGP2']!='EPE',:]\n",
    "    array=['RENTREE','REG_ID','UUCR_ID','DEP_ID','COM_CODE1','COM_NOM1','RGP3','RGP2','COM_M']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_COM_UNIV=df1.groupby(array, as_index=False).agg(f)\n",
    "\n",
    "    df1=df.loc[df['IUT'] == 'IUT',:]\n",
    "    array=['RENTREE','REG_ID','UUCR_ID','DEP_ID','COM_CODE1','COM_NOM1','RGP3','RGP2','COM_M']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_COM_IUT=df1.groupby(array, as_index=False).agg(f)\n",
    "    df_COM_IUT.loc[:,'RGP3']=\"IUT\"\n",
    "\n",
    "    df1=df.loc[df['INSPE'] == 'ESPE',:]\n",
    "    array=['RENTREE','REG_ID','UUCR_ID','DEP_ID','COM_CODE1','COM_NOM1','INSPE','COM_M']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_COM_ESPE=df1.groupby(array, as_index=False).agg(f)\n",
    "    df_COM_ESPE.loc[:,'RGP3']=\"INSPE\"\n",
    "\n",
    "    df1=df.loc[df['RGP2'] == 'EPE',:]\n",
    "    array=['RENTREE','REG_ID','UUCR_ID','DEP_ID','COM_CODE1','COM_NOM1','RGP2','COM_M']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_COM_EPE=df1.groupby(array, as_index=False).agg(f)\n",
    "    df_COM_EPE['RGP3']=df1.loc[:,'RGP2']\n",
    "\n",
    "    df_COM_UNIV.loc[df['COM_U']!=1,'RGP3']=f\"{df['RGP3']}_S\"\n",
    "    (df_COM_CSI.pivot_table(index=['RENTREE','REG_ID','UUCR_ID','DEP_ID','COM_CODE1','COM_NOM1'], columns=['RGP3','EFFTOT'], values='spend')\n",
    "              .add_prefix('spend_')\n",
    "              .reset_index())\n",
    "    \n",
    "    df_COM_CSI.loc[df_COM_CSI['CPGE']!='.'&df_COM_CSI['STS']=='.','RGP3']='CPGE'\n",
    "    df_COM_CSI.loc[df_COM_CSI['CPGE']!='.'&df_COM_CSI['STS']!='.','RGP3']='STS_CPGE'\n",
    "    df_COM_CSI.loc[df_COM_CSI['CPGE']=='.'&df_COM_CSI['STS']!='.','RGP3']='STS'\n",
    "    df_COM_CSI['EFFTOT']=sum(df_COM_CSI['CPGE'],df_COM_CSI['STS'])\n",
    "    del df_COM_CSI['CPGE']\n",
    "    del df_COM_CSI['STS']\n",
    "\n",
    "    df_COM_INV=pd.concat([df_COM_UNIV,df_COM_CSI,df_COM_AUT,df_COM_IUT,df_COM_ESPE])\n",
    "    df_COM_INV.loc[:,'UAI']=f\"{df.loc[:,'COMECODE1']}_{df.loc[:,'RGP3']}\"\n",
    "    df_COM_INV.loc[:,'GEO_LIB']=df.loc[:,'COM_NOM1']\n",
    "    df_COM_INV.loc[:,'GEO_ID']=df.loc[:,'COME_CODE1']\n",
    "    df_COM_INV.loc[:,'REF_ID']=df.loc[:,'DEP_ID']\n",
    "    df_COM_INV.loc[:,'TYPE2']=df.loc[:,'RGP3']\n",
    "    del df_COM_INV['RENTREE']\n",
    "    del df_COM_INV['DEP_ID']\n",
    "    del df_COM_INV['COM_CODE1']\n",
    "    del df_COM_INV['COM_NOM1']\n",
    "    del df_COM_INV['RGP3']\n",
    "    del df_COM_INV['RGP2']\n",
    "    del df_COM_INV['INSPE']\n",
    "\n",
    "    df_COM_SIEGE_UNIV=df.loc[df['COM_U']==1 & df['RGP3'] == 'UNIV',:]\n",
    "    df_COM_SIEGE_UNIV.loc[:,'GEO_LIB']=df.loc[:,'COM_NOM1']\n",
    "    df_COM_SIEGE_UNIV.loc[:,'GEO_ID']=df.loc[:,'COME_CODE1']\n",
    "    df_COM_SIEGE_UNIV.loc[:,'REF_ID']=df.loc[:,'DEP_ID']\n",
    "\n",
    "    array=['REG_ID','UUCR_ID','REF_ID','GEO_ID','GEO_LIB']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_COM_SIEGE_UNIV=df_COM_SIEGE_UNIV.groupby(array, as_index=False).agg(f)\n",
    "\n",
    "    df_COM_SIEGE=df.loc[df['COM_U']==1 & (df['RGP3'] in ['UNIV','GE','INP','UT','ENS','ESPE','IUFM']| df['RGP_ING']== 'ING_MESR_PU'),:]\n",
    "    df_COM_SIEGE.loc[:,'GEO_LIB']=df.loc[:,'COM_NOM1']\n",
    "    df_COM_SIEGE.loc[:,'GEO_ID']=df.loc[:,'COME_CODE1']\n",
    "    df_COM_SIEGE.loc[:,'REF_ID']=df.loc[:,'DEP_ID']\n",
    "\n",
    "    array=['REG_ID','UUCR_ID','REF_ID','GEO_ID','GEO_LIB']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_COM_SIEGE=df_COM_SIEGE.groupby(array, as_index=False).agg(f)\n",
    "\n",
    "    df_COM_UNIV=df.loc[df['RGP3'] =='UNIV',:]\n",
    "    df_COM_UNIV.loc[:,'GEO_LIB']=df.loc[:,'COM_NOM1']\n",
    "    df_COM_UNIV.loc[:,'GEO_ID']=df.loc[:,'COME_CODE1']\n",
    "    df_COM_UNIV.loc[:,'REF_ID']=df.loc[:,'DEP_ID']\n",
    "\n",
    "    array=['REG_ID','UUCR_ID','REF_ID','GEO_ID','GEO_LIB']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_COM_UNIV=df_COM_UNIV.groupby(array, as_index=False).agg(f)\n",
    "\n",
    "    df_COM_UNIVERSITAIRE=df.loc[df['RGP3'] in ['UNIV','GE','INP','UT','ENS','ESPE','IUFM']| df['RGP_ING']== 'ING_MESR_PU',:]\n",
    "    df_COM_UNIVERSITAIRE.loc[:,'GEO_LIB']=df.loc[:,'COM_NOM1']\n",
    "    df_COM_UNIVERSITAIRE.loc[:,'GEO_ID']=df.loc[:,'COME_CODE1']\n",
    "    df_COM_UNIVERSITAIRE.loc[:,'REF_ID']=df.loc[:,'DEP_ID']\n",
    "\n",
    "    array=['REG_ID','UUCR_ID','REF_ID','GEO_ID','GEO_LIB']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_COM_UNIVERSITAIRE=df_COM_UNIVERSITAIRE.groupby(array, as_index=False).agg(f)\n",
    "    \n",
    "    df_COM_SITES=df_COM_INV.groupby(array, as_index=False).agg(f)\n",
    "\n",
    "    col=['REG_ID','UUCR_ID','REF_ID','GEO_ID','GEO_LIB']\n",
    "    df_COM_SITES.loc[df_COM_UNIVERSITAIRE[col].isin(df_COM_SITES[col]),'UNIVERSITAIRE']='ok'\n",
    "    df_COM_SITES.loc[df_COM_SIEGE[col].isin(df_COM_SITES[col]),'SIEGE']='ok'\n",
    "    df_COM_SITES.loc[df_COM_SIEGE_UNIV[col].isin(df_COM_SITES[col]),'SIEGE_UNIV']='ok'\n",
    "    df_COM_SITES.loc[df_COM_UNIV[col].isin(df_COM_SITES[col]),'UNIV']='ok'\n",
    "    df_COM_SITES.loc[df_COM_SIEGE[col].isin(df_COM_SITES[col]),'TYPE_SITE']='site_princ'\n",
    "    df_COM_SITES.loc[df_COM_UNIVERSITAIRE[col].isin(df_COM_SITES[col]),'TYPE_SITE']='site_sec'\n",
    "    df_COM_SITES.loc[(-df_COM_UNIVERSITAIRE[col].isin(df_COM_SITES[col]))|(-df_COM_SIEGE.isin(df_COM_SITES)[col]),'TYPE_SITE']='site_sec'\n",
    "    \n",
    "    df_UUCR_AUT=df.loc[df['RGP3'] not in ['CPGE','STS','UNIV','GE','INP','UT'],:]\n",
    "    array=['RENTREE','REG_ID','UUCR_ID','UUCR_NOM','RGP3']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_UUCR_AUT=df_UUCR_AUT.groupby(array, as_index=False).agg(f)\n",
    "     \n",
    "    df_UUCR_CSI=df.loc[df['RGP3'] in ['CPGE','STS'],:]\n",
    "    array=['RENTREE','REG_ID','UUCR_ID','UUCR_NOM','RGP3']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_UUCR_CSI=df_UUCR_CSI.groupby(array, as_index=False).agg(f)\n",
    "    \n",
    "    df_UUCR_UNIV=df.loc[df['RGP3'] in ['UNIV','GE','INP','UT'] & df['IUT'] !='IUT' & df['INSPE'] !='ESPE' & df['RGP2'] !='EPE' ,:]\n",
    "    array=['RENTREE','REG_ID','UUCR_ID','UUCR_NOM','RGP3','UUCR_ID ou memeUUCR']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_UUCR_UNIV=df_UUCR_UNIV.groupby(array, as_index=False).agg(f)\n",
    "    \n",
    "    df_UUCR_IUT=df.loc[ df['IUT'] =='IUT',:]\n",
    "    array=['RENTREE','REG_ID','UUCR_ID','UUCR_NOM','IUT','UUCR_ID ou memeUUCR']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_UUCR_IUT=df_UUCR_IUT.groupby(array, as_index=False).agg(f)\n",
    "    df_UUCR_IUT.loc[:,'RGP3']=df_UUCR_IUT.loc[:,'IUT']\n",
    "    \n",
    "    df_UUCR_ESPE=df.loc[ df['INSPE'] =='ESPE',:]\n",
    "    array=['RENTREE','REG_ID','UUCR_ID','UUCR_NOM','INSPE','UUCR_ID ou memeUUCR']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_UUCR_ESPE=df_UUCR_ESPE.groupby(array, as_index=False).agg(f)\n",
    "    \n",
    "    df_UUCR_EPE=df.loc[ df['RGP2'] =='EPE',:]\n",
    "    array=['RENTREE','REG_ID','UUCR_ID','UUCR_NOM','RGP2','UUCR_ID ou memeUUCR']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_UUCR_EPE=df_UUCR_EPE.groupby(array, as_index=False).agg(f)\n",
    "    df_UUCR_EPE.loc[:,'RGP3']=df_UUCR_IUT.loc[:,'RGP2']\n",
    "    \n",
    "    df_UUCR_UNIV.loc[df_UUCR_UNIV['memeUUCR']!=1,'RGP3']=f\"{df_UUCR_UNIV.loc[:,'RGP3']}_S\"\n",
    "    (df_UUCR_CSI.pivot_table(index=['RENTREE','REG_ID','UUCR_ID','UUCR_NOM'], columns=['RGP3','EFFTOT'], values='spend')\n",
    "              .add_prefix('spend_')\n",
    "              .reset_index())\n",
    "    \n",
    "    df_UUCR_CSI.loc[df_UUCR_CSI['CPGE']!='.'&df_UUCR_CSI['STS']=='.','RGP3']='CPGE'\n",
    "    df_UUCR_CSI.loc[df_UUCR_CSI['CPGE']!='.'&df_UUCR_CSI['STS']!='.','RGP3']='STS_CPGE'\n",
    "    df_UUCR_CSI.loc[df_UUCR_CSI['CPGE']=='.'&df_UUCR_CSI['STS']!='.','RGP3']='STS'\n",
    "    df_UUCR_CSI['EFFTOT']=sum(df_UUCR_CSI['CPGE'],df_UUCR_CSI['STS'])\n",
    "    del df_UUCR_CSI['CPGE']\n",
    "    del df_UUCR_CSI['STS']\n",
    "    \n",
    "    df_UUCR_INV=pd.concat([df_COM_UNIV,df_COM_CSI,df_COM_AUT,df_COM_IUT,df_COM_ESPE])\n",
    "    df_UUCR_INV.loc[:,'UAI']=f\"{df.loc[:,'COMECODE1']}_{df.loc[:,'RGP3']}\"\n",
    "    df_UUCR_INV.loc[:,'GEO_LIB']=df.loc[:,'COM_NOM1']\n",
    "    df_UUCR_INV.loc[:,'GEO_ID']=df.loc[:,'COME_CODE1']\n",
    "    df_UUCR_INV.loc[:,'REF_ID']=df.loc[:,'DEP_ID']\n",
    "    df_UUCR_INV.loc[:,'TYPE2']=df.loc[:,'RGP3']\n",
    "    del df_UUCR_INV['RENTREE']\n",
    "    del df_UUCR_INV['DEP_ID']\n",
    "    del df_UUCR_INV['COM_CODE1']\n",
    "    del df_UUCR_INV['COM_NOM1']\n",
    "    del df_UUCR_INV['RGP3']\n",
    "    del df_UUCR_INV['RGP2']\n",
    "    del df_UUCR_INV['INSPE']\n",
    "    \n",
    "    df_UUCR_SIEGE=df.loc[df['memeUUCR']==1 &(df['RGP3'] == ['UNIV','GE','INP','UT','ENS','ESPE','IUFM']|df['RGP_ING'] == 'ING_MESR_PU'),:]\n",
    "    df_UUCR_SIEGE.loc[:,'GEO_LIB']=df_UUCR_SIEGE.loc[:,'COM_NOM1']\n",
    "    df_UUCR_SIEGE.loc[:,'GEO_ID']=df_UUCR_SIEGE.loc[:,'COME_CODE1']\n",
    "    df_UUCR_SIEGE.loc[:,'REF_ID']=df_UUCR_SIEGE.loc[:,'DEP_ID']\n",
    "    \n",
    "    array=['REF_ID','GEO_ID','GEO_LIB']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_UUCR_UNIV=df_UUCR_UNIV.groupby(array, as_index=False).agg(f)\n",
    "\n",
    "    df_UUCR_SIEGE_UNIV=df.loc[df['COM_U']==1 & df['RGP3'] == 'UNIV',:]\n",
    "    df_UUCR_SIEGE_UNIV.loc[:,'GEO_LIB']=df_UUCR_SIEGE_UNIV.loc[:,'COM_NOM1']\n",
    "    df_UUCR_SIEGE_UNIV.loc[:,'GEO_ID']=df_UUCR_SIEGE_UNIV.loc[:,'COME_CODE1']\n",
    "    df_UUCR_SIEGE_UNIV.loc[:,'REF_ID']=df_UUCR_SIEGE_UNIV.loc[:,'DEP_ID']\n",
    "    \n",
    "    array=['REF_ID','GEO_ID','GEO_LIB']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_UUCR_UNIV=df_UUCR_UNIV.groupby(array, as_index=False).agg(f)\n",
    "    \n",
    "    df_UUCR_UNIVERSITAIRE=df.loc[df['RGP3'] in ['UNIV','GE','INP','UT','ENS','ESPE','IUFM']| df['RGP_ING']== 'ING_MESR_PU',:]\n",
    "    df_UUCR_UNIVERSITAIRE.loc[:,'GEO_LIB']=df.loc[:,'COM_NOM1']\n",
    "    df_UUCR_UNIVERSITAIRE.loc[:,'GEO_ID']=df.loc[:,'COME_CODE1']\n",
    "    df_UUCR_UNIVERSITAIRE.loc[:,'REF_ID']=df.loc[:,'DEP_ID']\n",
    "\n",
    "    array=['REG_ID','UUCR_ID','REF_ID','GEO_ID','GEO_LIB']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_UUCR_UNIVERSITAIRE=df_UUCR_UNIVERSITAIRE.groupby(array, as_index=False).agg(f)\n",
    "\n",
    "    array=['REG_ID','UUCR_ID','REF_ID','GEO_ID','GEO_LIB']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_UUCR_SITES=df_UUCR_INV.groupby(array, as_index=False).agg(f)\n",
    "\n",
    "    col=['REG_ID','UUCR_ID','REF_ID','GEO_ID','GEO_LIB']\n",
    "    df_UUCR_SITES.loc[df_UUCR_UNIVERSITAIRE[col].isin(df_UUCR_SITES[col]),'UNIVERSITAIRE']='ok'\n",
    "    df_UUCR_SITES.loc[df_UUCR_SIEGE[col].isin(df_UUCR_SITES[col]),'SIEGE']='ok'\n",
    "    df_UUCR_SITES.loc[df_UUCR_SIEGE_UNIV[col].isin(df_UUCR_SITES[col]),'SIEGE_UNIV']='ok'\n",
    "    df_UUCR_SITES.loc[df_UUCR_UNIV[col].isin(df_UUCR_SITES[col]),'UNIV']='ok'\n",
    "    df_UUCR_SITES.loc[df_UUCR_SIEGE[col].isin(df_UUCR_SITES[col]),'TYPE_SITE']='site_princ'\n",
    "    df_UUCR_SITES.loc[df_UUCR_UNIVERSITAIRE[col].isin(df_UUCR_SITES[col]),'TYPE_SITE']='site_sec'\n",
    "    df_UUCR_SITES.loc[(-df_UUCR_UNIVERSITAIRE[col].isin(df_UUCR_SITES[col]))|(-df_UUCR_SIEGE.isin(df_UUCR_SITES)[col]),'TYPE_SITE']='site_sec'\n",
    "    \n",
    "    return df, df_COM_INV, df_COM_SITES, df_UUCR_INV, df_UUCR_SITES\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def gentab3(df):\n",
    "    df1=df.loc[df['RGP3'] not in ['CPGE','STS','UNIV','GE','INP','UT','ESPE'],:]\n",
    "    array=['RENTREE','REG_ID','UUCR_ID','DEP_ID','COM_CODE1','COM_NOM1','RGP3']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_COM2_AUT=df1.groupby(array, as_index=False).agg(f)\n",
    "    \n",
    "    df1=df.loc[df['RGP3'] in ['CPGE','STS'],:]\n",
    "    array=['RENTREE','REG_ID','UUCR_ID','DEP_ID','COM_CODE1','COM_NOM1','RGP3']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_COM2_CSI=df1.groupby(array, as_index=False).agg(f)\n",
    "\n",
    "    df1=df.loc[df['RGP3'] in ['UNIV','GE','INP','UT'] & df['IUT']!='IUT' & df['INSPE']!='INSPE' & df['RGP2']!='EPE',:]\n",
    "    array=['RENTREE','REG_ID','UUCR_ID','DEP_ID','COM_CODE1','COM_NOM1','RGP3','RGP2','COM_M']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_COM2_UNIV=df1.groupby(array, as_index=False).agg(f)\n",
    "\n",
    "    df1=df.loc[df['IUT'] == 'IUT',:]\n",
    "    array=['RENTREE','REG_ID','UUCR_ID','DEP_ID','COM_CODE1','COM_NOM1','RGP3','RGP2','COM_M']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_COM2_IUT=df1.groupby(array, as_index=False).agg(f)\n",
    "    df_COM2_IUT.loc[:,'RGP3']=\"IUT\"\n",
    "\n",
    "    df1=df.loc[df['INSPE'] == 'ESPE',:]\n",
    "    array=['RENTREE','REG_ID','UUCR_ID','DEP_ID','COM_CODE1','COM_NOM1','INSPE','COM_M']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_COM2_ESPE=df1.groupby(array, as_index=False).agg(f)\n",
    "    df_COM2_ESPE.loc[:,'RGP3']=\"INSPE\"\n",
    "\n",
    "    df1=df.loc[df['RGP2'] == 'EPE',:]\n",
    "    array=['RENTREE','REG_ID','UUCR_ID','DEP_ID','COM_CODE1','COM_NOM1','RGP2','COM_M']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_COM2_EPE=df1.groupby(array, as_index=False).agg(f)\n",
    "    df_COM2_EPE['RGP3']=df1.loc[:,'RGP2']\n",
    "\n",
    "    df_COM2_UNIV.loc[df['COM_U']!=1,'RGP3']=f\"{df['RGP3']}_S\"\n",
    "    (df_COM2_CSI.pivot_table(index=['RENTREE','REG_ID','UUCR_ID','DEP_ID','COM_CODE1','COM_NOM1'], columns=['RGP3','EFFTOT'], values='spend')\n",
    "              .add_prefix('spend_')\n",
    "              .reset_index())\n",
    "    \n",
    "    df_COM2_CSI.loc[df_COM2_CSI['CPGE']!='.'&df_COM2_CSI['STS']=='.','RGP3']='CPGE'\n",
    "    df_COM2_CSI.loc[df_COM2_CSI['CPGE']!='.'&df_COM2_CSI['STS']!='.','RGP3']='STS_CPGE'\n",
    "    df_COM2_CSI.loc[df_COM2_CSI['CPGE']=='.'&df_COM2_CSI['STS']!='.','RGP3']='STS'\n",
    "    df_COM2_CSI['EFFTOT']=sum(df_COM2_CSI['CPGE'],df_COM2_CSI['STS'])\n",
    "    del df_COM2_CSI['CPGE']\n",
    "    del df_COM2_CSI['STS']\n",
    "\n",
    "    df_COM2_INV=pd.concat([df_COM2_UNIV,df_COM2_CSI,df_COM2_AUT,df_COM2_IUT,df_COM2_ESPE])\n",
    "    df_COM2_INV.loc[:,'UAI']=f\"{df.loc[:,'COMECODE1']}_{df.loc[:,'RGP3']}\"\n",
    "    df_COM2_INV.loc[:,'GEO_LIB']=df.loc[:,'COM_NOM1']\n",
    "    df_COM2_INV.loc[:,'GEO_ID']=df.loc[:,'COME_CODE1']\n",
    "    df_COM2_INV.loc[:,'REF_ID']=df.loc[:,'DEP_ID']\n",
    "    df_COM2_INV.loc[:,'TYPE2']=df.loc[:,'RGP3']\n",
    "    del df_COM2_INV['RENTREE']\n",
    "    del df_COM2_INV['DEP_ID']\n",
    "    del df_COM2_INV['COM_CODE1']\n",
    "    del df_COM2_INV['COM_NOM1']\n",
    "    del df_COM2_INV['RGP3']\n",
    "    del df_COM2_INV['RGP2']\n",
    "    del df_COM2_INV['INSPE']\n",
    "\n",
    "    df_COM2_SIEGE_UNIV=df.loc[df['COM_U']==1 & df['RGP3'] == 'UNIV',:]\n",
    "    df_COM2_SIEGE_UNIV.loc[:,'GEO_LIB']=df.loc[:,'COM_NOM1']\n",
    "    df_COM2_SIEGE_UNIV.loc[:,'GEO_ID']=df.loc[:,'COME_CODE1']\n",
    "    df_COM2_SIEGE_UNIV.loc[:,'REF_ID']=df.loc[:,'DEP_ID']\n",
    "\n",
    "    array=['REG_ID','UUCR_ID','REF_ID','GEO_ID','GEO_LIB']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_COM2_SIEGE_UNIV=df_COM2_SIEGE_UNIV.groupby(array, as_index=False).agg(f)\n",
    "\n",
    "    df_COM2_SIEGE=df.loc[df['COM_U']==1 & (df['RGP3'] in ['UNIV','GE','INP','UT','ENS','ESPE','IUFM']| df['RGP_ING']== 'ING_MESR_PU'),:]\n",
    "    df_COM2_SIEGE.loc[:,'GEO_LIB']=df.loc[:,'COM_NOM1']\n",
    "    df_COM2_SIEGE.loc[:,'GEO_ID']=df.loc[:,'COME_CODE1']\n",
    "    df_COM2_SIEGE.loc[:,'REF_ID']=df.loc[:,'DEP_ID']\n",
    "\n",
    "    array=['REG_ID','UUCR_ID','REF_ID','GEO_ID','GEO_LIB']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_COM2_SIEGE=df_COM2_SIEGE.groupby(array, as_index=False).agg(f)\n",
    "\n",
    "    df_COM2_UNIV=df.loc[df['RGP3'] =='UNIV',:]\n",
    "    df_COM2_UNIV.loc[:,'GEO_LIB']=df.loc[:,'COM_NOM1']\n",
    "    df_COM2_UNIV.loc[:,'GEO_ID']=df.loc[:,'COME_CODE1']\n",
    "    df_COM2_UNIV.loc[:,'REF_ID']=df.loc[:,'DEP_ID']\n",
    "\n",
    "    array=['REG_ID','UUCR_ID','REF_ID','GEO_ID','GEO_LIB']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_COM2_UNIV=df_COM2_UNIV.groupby(array, as_index=False).agg(f)\n",
    "\n",
    "    df_COM2_UNIVERSITAIRE=df.loc[df['RGP3'] in ['UNIV','GE','INP','UT','ENS','ESPE','IUFM']| df['RGP_ING']== 'ING_MESR_PU',:]\n",
    "    df_COM2_UNIVERSITAIRE.loc[:,'GEO_LIB']=df.loc[:,'COM_NOM1']\n",
    "    df_COM2_UNIVERSITAIRE.loc[:,'GEO_ID']=df.loc[:,'COME_CODE1']\n",
    "    df_COM2_UNIVERSITAIRE.loc[:,'REF_ID']=df.loc[:,'DEP_ID']\n",
    "\n",
    "    array=['REG_ID','UUCR_ID','REF_ID','GEO_ID','GEO_LIB']\n",
    "    f = {'EFFTOT': 'sum'}\n",
    "    df_COM2_UNIVERSITAIRE=df_COM2_UNIVERSITAIRE.groupby(array, as_index=False).agg(f)\n",
    "    \n",
    "    df_COM2_SITES=df_COM2_INV.groupby(array, as_index=False).agg(f)\n",
    "\n",
    "    col=['REG_ID','UUCR_ID','REF_ID','GEO_ID','GEO_LIB']\n",
    "    df_COM2_SITES.loc[df_COM2_UNIVERSITAIRE[col].isin(df_COM2_SITES[col]),'UNIVERSITAIRE']='ok'\n",
    "    df_COM2_SITES.loc[df_COM2_SIEGE[col].isin(df_COM2_SITES[col]),'SIEGE']='ok'\n",
    "    df_COM2_SITES.loc[df_COM2_SIEGE_UNIV[col].isin(df_COM2_SITES[col]),'SIEGE_UNIV']='ok'\n",
    "    df_COM2_SITES.loc[df_COM2_UNIV[col].isin(df_COM2_SITES[col]),'UNIV']='ok'\n",
    "    df_COM2_SITES.loc[df_COM2_SIEGE[col].isin(df_COM2_SITES[col]),'TYPE_SITE']='site_princ'\n",
    "    df_COM2_SITES.loc[df_COM2_UNIVERSITAIRE[col].isin(df_COM2_SITES[col]),'TYPE_SITE']='site_sec'\n",
    "    df_COM2_SITES.loc[(-df_COM2_UNIVERSITAIRE[col].isin(df_COM2_SITES[col]))|(-df_COM2_SIEGE.isin(df_COM2_SITES)[col]),'TYPE_SITE']='site_sec'\n",
    "    \n",
    "    return df_COM2_INV, df_COM2_SITES"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def gentab4(df_COM_SITES, df_COM2_SITES, df_UUCR_SITES):\n",
    "    \n",
    "    df_COM_SITES=df_COM_SITES[['GEO_ID','TYPE_SITE']]\n",
    "    df_COM2_SITES=df_COM2_SITES[['GEO_ID','TYPE_SITE']]\n",
    "    df_UUCR_SITES=df_UUCR_SITES[['GEO_ID','TYPE_SITE']]\n",
    "    df=pd.concat([df_COM_SITES,df_COM2_SITES,df_UUCR_SITES])\n",
    "    df.loc[:,'un']=1\n",
    "\n",
    "    array=['GEO_ID','TYPE_SITE']\n",
    "    f = {'un': 'sum'}\n",
    "    df_les_sites=df.groupby(array, as_index=False).agg(f)\n",
    "    \n",
    "    return df_les_sites"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def eff_sans_double_compte21(df):\n",
    "    df1=df[(np.isna(df['OPPOS']) | df['OPPOS']=='O')| df['SECT']!='PU' ]\n",
    "    array=['ETABLI','SECT','OPPOS']\n",
    "    f = {'EFFECTIF_SANS_DOUBLE_COMPTE': 'sum'}\n",
    "    df_OPPOS=df1.groupby(array, as_index=False).agg(f)\n",
    "\n",
    "    array=['ETABLI','SECT','OPPOS']\n",
    "    f = {'EFFECTIF_SANS_DOUBLE_COMPTE': 'sum'}\n",
    "    df_OPPOS=df.groupby(array, as_index=False).agg(f)\n",
    "\n",
    "    return df_OPPOS,df_format"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  },
  "vscode": {
   "interpreter": {
    "hash": "70f87b938433ef206dcc66a576f67ebf2dfa1db50e9e1cb92aefb44824dbd99c"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}