parsing_pipeline.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Aggrégation des fichiers de data.gouv.fr pour les PLF, PLR, Recettes - Introduction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "import json\n",
    "import os\n",
    "import re\n",
    "\n",
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Décodage des fichiers\n",
    "Réécrire les fichier dans un encoding compatible `UTF-8`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "RAW_FOLDER = './input_data/raw_data/'\n",
    "\n",
    "for folderName in os.listdir(RAW_FOLDER):\n",
    "    DECODED_FOLDER = './input_data/decoded_data/{}'.format(folderName)\n",
    "\n",
    "    if not os.path.exists(DECODED_FOLDER):\n",
    "        os.makedirs(DECODED_FOLDER)\n",
    "\n",
    "    currentPath = os.path.join(RAW_FOLDER, folderName)\n",
    "    for fileName in os.listdir(currentPath):\n",
    "        os.system('iconv -f WINDOWS-1252 -t UTF-8 {} > {}'.format(os.path.join(currentPath, fileName), os.path.join(DECODED_FOLDER, fileName)))\n",
    "#         os.system('iconv -f ISO-8859-1 -t UTF-8 {} > {}'.format(os.path.join(currentPath, fileName), os.path.join(DECODED_FOLDER, fileName)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Conversion des nomenclatures en hiérarchies D3"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Utilitaires"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Une fonction pour calculer un attribut d'un noeud récursivement."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "def recKey(node, key, default_value=0):\n",
    "    # Si le noeud n'est pas une feuille, on calcule sa valeur récursivement\n",
    "    if key not in node:\n",
    "        node[key] = sum(list(map(lambda x: recKey(x, key), node['children'])))\n",
    "\n",
    "    return node[key] if node[key] else default_value"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`build_flat_tree` prend un dictionnaire `d`, un arbre `node` et une clé `key`.\n",
    "\n",
    "Elle remplit `d` en parcourant `node` récursivement, afin de construire une représentation plate de `node` triée avec la clé `key`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": [
    "def build_flat_tree(d, node, key='code'):\n",
    "    rnode = node.copy()\n",
    "\n",
    "    if 'children' in rnode:\n",
    "        del rnode['children']\n",
    "\n",
    "    d[node[key]] = rnode\n",
    "\n",
    "    if 'children' in node:\n",
    "        for child in node['children']:\n",
    "            build_flat_tree(d, child, key)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`build_d3_hierarchy` parcourt un fichier CSV contenant les feuilles d'un arbre et construit une hiérarchie D3 représentant cet arbre."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "def build_d3_hierarchy(rows, root_node, text_indices, code_indices, modify_current_node=None, callback=None):\n",
    "    for row in rows:\n",
    "        current_node = root_node\n",
    "\n",
    "        nodes = [field for field in [row[i] for i in text_indices] if field != ''] \n",
    "        for node_index, node in enumerate(nodes):\n",
    "            # On cherche si le chemin existe déjà.\n",
    "            # S'il n'existe pas, on le crée.\n",
    "            found = False\n",
    "            if not 'children' in current_node:\n",
    "                current_node['children'] = []\n",
    "            for index, child in enumerate(current_node['children']):\n",
    "                if child['name'] == node:\n",
    "                    current_node = current_node['children'][index]\n",
    "                    found = True\n",
    "                    break\n",
    "\n",
    "            if not found:\n",
    "                current_node['children'].append(dict({\n",
    "                    'name': node,\n",
    "                    'code': '-'.join([row[code_indices[i]] for i in range(node_index+1)])\n",
    "                }))\n",
    "                current_node = current_node['children'][-1]\n",
    "\n",
    "                # Si le noeud n'est pas une feuille, il a des enfants...\n",
    "                if node_index != len(nodes)-1:\n",
    "                    current_node['children'] = []\n",
    "                else:\n",
    "                    if (modify_current_node is not None):\n",
    "                        modify_current_node(current_node, row)\n",
    "\n",
    "    # On calcule des cp et les ae des noeuds qui ne\n",
    "    # sont pas des feuilles.\n",
    "    if callback is not None:\n",
    "        callback(root_node)\n",
    "        \n",
    "    return root_node"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Deux fonctions pour exporter les résultats calculés."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "def write_d3_hierarchy(root_node, target_file_name):\n",
    "    with open(target_file_name, 'w+') as output_file:\n",
    "        json.dump(root_node, output_file)\n",
    "\n",
    "def write_flat_hierarchy(root_node, target_flat_file_name):\n",
    "    with open(target_flat_file_name, 'w+') as output_file:\n",
    "        d = dict()\n",
    "        build_flat_tree(d, root_node)\n",
    "\n",
    "        json.dump(d, output_file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "init_node = dict({\n",
    "    'name': 'PLF',\n",
    "    'children': [],\n",
    "    'code': 'PLF'\n",
    "})\n",
    "\n",
    "with open('./input_data/nomenclatures_plf/plf_2019.csv', 'r') as in_file:\n",
    "    csv_reader = csv.reader(in_file, delimiter=',')\n",
    "    rows = [row for row in csv_reader]\n",
    "\n",
    "root_node = build_d3_hierarchy(\n",
    "    rows, init_node,                        \n",
    "    [2, 4, 6, 8, 10], [1, 3, 5, 7, 9]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Création des hiérarchies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "type_de_mission_to_code = {\n",
    "    \"Budget général\": \"1\",\n",
    "    \"Budgets annexes\": \"2\",\n",
    "    \"Comptes d'affectation spéciale\": \"3\",\n",
    "    \"Comptes de concours financiers\": \"4\"\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2019"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### PLF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "SOURCE_FOLDER = './input_data/decoded_data/plf_2019'\n",
    "\n",
    "type_de_mission_to_filename = {\n",
    "    \"Budget général\": \"bg\",\n",
    "    \"Budgets annexes\": \"ba\",\n",
    "    \"Comptes d'affectation spéciale\": \"cs\",\n",
    "    \"Comptes de concours financiers\": \"cf\"\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "mission_to_code = {}\n",
    "\n",
    "with open(os.path.join(SOURCE_FOLDER, 'plf2019-nomenclature-mpa.csv'), 'r') as csv_file:\n",
    "    csv_reader = csv.reader(csv_file, delimiter=';')\n",
    "    # On saute la première ligne parce que c'est un header\n",
    "    next(csv_reader, None)\n",
    "\n",
    "    for row in csv_reader:\n",
    "        code_mission = row[2]\n",
    "        mission = row[3]\n",
    "\n",
    "        mission_to_code[mission] = code_mission"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On empile les fichiers qui nous intéressent (ici, la comptabilité générale du BG, CAS CCF et BA)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "675353577993 689624049061\n"
     ]
    }
   ],
   "source": [
    "rows = []\n",
    "credits_paiement_total = 0\n",
    "autorisations_engagement_total = 0\n",
    "\n",
    "for type_de_mission in type_de_mission_to_code:\n",
    "    code_type_de_mission = type_de_mission_to_code[type_de_mission]\n",
    "    code_filename = type_de_mission_to_filename[type_de_mission]\n",
    "\n",
    "    with open(os.path.join(\n",
    "        SOURCE_FOLDER,\n",
    "        'plf2019-{}-msn-dest.csv'.format(code_filename)), 'r') as csv_file:\n",
    "\n",
    "        csv_reader = csv.reader(csv_file, delimiter=';')\n",
    "        # On saute la première ligne parce que c'est un header\n",
    "        next(csv_reader, None)\n",
    "\n",
    "        for index, row in enumerate(csv_reader):\n",
    "            # Petite subtilité pour le cas 2019, certains documents csv ont trop de lignes (il y a des lignes vides à la fin)\n",
    "            if (row[2] == ''):\n",
    "                continue\n",
    "\n",
    "            autorisations_engagement = int(row[9].replace(' ', '')) if row[9].replace(' ', '') != '' else 0\n",
    "            autorisations_engagement_total += autorisations_engagement\n",
    "            credits_paiement = int(row[10].replace(' ', '')) if row[10].replace(' ', '') != '' else 0\n",
    "            credits_paiement_total += credits_paiement\n",
    "\n",
    "            code_action = '0' + row[5] if len(row[5]) == 1 else row[5]\n",
    "            code_sousaction = '0' + row[7] if len(row[7]) == 1 else row[7]\n",
    "\n",
    "            # On écrit dans le fichier cible toutes les informations utiles\n",
    "            rows.append([\n",
    "                type_de_mission,\n",
    "                code_type_de_mission,\n",
    "                row[2],\n",
    "                mission_to_code[row[2]],\n",
    "                row[4],\n",
    "                row[3],\n",
    "                row[6],\n",
    "                code_action,\n",
    "                row[8],\n",
    "                code_sousaction,\n",
    "                autorisations_engagement,\n",
    "                credits_paiement\n",
    "            ])\n",
    "\n",
    "print(credits_paiement_total, autorisations_engagement_total)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On construit la hiéarchie D3."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "init_node = dict({\n",
    "    'name': 'PLF',\n",
    "    'children': [],\n",
    "    'code': 'PLF'\n",
    "})\n",
    "\n",
    "def modify_current_node(current_node, row):\n",
    "    current_node['cp'] = int(row[11])\n",
    "    current_node['ae'] = int(row[10])\n",
    "    current_node['size'] = int(row[11])\n",
    "\n",
    "def callback(root_node):\n",
    "    recKey(root_node, 'cp')\n",
    "    recKey(root_node, 'ae')\n",
    "    recKey(root_node, 'size')\n",
    "\n",
    "root_node = build_d3_hierarchy(\n",
    "    rows, init_node,\n",
    "    [0, 2, 4, 6, 8], [1, 3, 5, 7, 9],\n",
    "    modify_current_node=modify_current_node,\n",
    "    callback=callback\n",
    ")\n",
    "\n",
    "write_d3_hierarchy(root_node, '../server/resources/hierarchy/plf/2019.json')\n",
    "write_flat_hierarchy(root_node, '../server/resources/flat/plf/2019.json')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Recettes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('./input_data/decoded_data/plf_2019/plf2019-recettes-fiscales-etata.csv', 'r', encoding='utf-8') as recettes:\n",
    "    csv_reader = csv.reader(recettes, delimiter=';')\n",
    "    next(csv_reader)\n",
    "\n",
    "    root_node = dict({\n",
    "        'name': 'Recettes',\n",
    "        'children': [],\n",
    "        'code': 'REC'\n",
    "    })\n",
    "\n",
    "    for row in csv_reader:\n",
    "        line_number = row[0]\n",
    "        label = row[1]\n",
    "        montant = int(row[2].replace(\" \", '')) if row[2] else None\n",
    "\n",
    "        if line_number:\n",
    "            codes = [line_number[0], line_number[1], line_number[2:]]\n",
    "        else:\n",
    "            m = re.match('(.*)\\.\\s?(.*)', label)\n",
    "            codes = list(m.group(1))\n",
    "            label = m.group(2)\n",
    "\n",
    "        current_node = root_node\n",
    "\n",
    "        for code_index, code in enumerate(codes[:-1]):\n",
    "            if 'children' not in current_node:\n",
    "                current_node['children'] = []\n",
    "\n",
    "            for index, child in enumerate(current_node['children']):\n",
    "                if child['code'].startswith('-'.join(codes[:code_index+1])):\n",
    "                    current_node = current_node['children'][index]\n",
    "        \n",
    "        if 'children' not in current_node:\n",
    "            current_node['children'] = []\n",
    "\n",
    "        current_node['children'].append(dict({\n",
    "            'name': label,\n",
    "            'code': '-'.join(codes),\n",
    "        }))\n",
    "\n",
    "        # Terrible workaround for finding tree leaves\n",
    "        if len(codes) == 3 or codes == ['4', '1']:\n",
    "            current_node['children'][-1]['size'] = montant\n",
    "\n",
    "    recKey(root_node, 'size')\n",
    "\n",
    "write_d3_hierarchy(root_node, '../server/resources/hierarchy/recettes/2019.json')\n",
    "write_flat_hierarchy(root_node, '../server/resources/flat/recettes/2019.json')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2018"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "SOURCE_FOLDER = './input_data/decoded_data/plf_2018'\n",
    "\n",
    "type_de_mission_to_filename = {\n",
    "    \"Budget général\": \"BG\",\n",
    "    \"Budgets annexes\": \"BA\",\n",
    "    \"Comptes d'affectation spéciale\": \"CS\",\n",
    "    \"Comptes de concours financiers\": \"CF\"\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "mission_to_code = {}\n",
    "code_to_programme = {}\n",
    "\n",
    "with open(os.path.join('./input_data/nomenclatures_plf/plf_2018.csv'), 'r') as csv_file:\n",
    "    csv_reader = csv.reader(csv_file, delimiter=',')\n",
    "    # On saute la première ligne parce que c'est un header\n",
    "    next(csv_reader, None)\n",
    "\n",
    "    for row in csv_reader:\n",
    "        code_mission = row[3]\n",
    "        mission = row[4]\n",
    "        code_programme = row[5]\n",
    "        programme = row[6]\n",
    "\n",
    "        mission_to_code[mission] = code_mission\n",
    "        code_to_programme[code_programme] = programme"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "648238506383 651922195355\n"
     ]
    }
   ],
   "source": [
    "rows = []\n",
    "credits_paiement_total = 0\n",
    "autorisations_engagement_total = 0\n",
    "\n",
    "for type_de_mission in type_de_mission_to_code:\n",
    "    code_type_de_mission = type_de_mission_to_code[type_de_mission]\n",
    "    code_filename = type_de_mission_to_filename[type_de_mission]\n",
    "\n",
    "    with open(os.path.join(\n",
    "        SOURCE_FOLDER,\n",
    "        'PLF2018-{}-Msn-Dest.csv'.format(code_filename)), 'r') as csv_file:\n",
    "\n",
    "        csv_reader = csv.reader(csv_file, delimiter=';')\n",
    "        # On saute les headers (4 lignes)\n",
    "        for i in range(4):\n",
    "            next(csv_reader, None)\n",
    "\n",
    "        for index, row in enumerate(csv_reader):\n",
    "            # Petite subtilité pour le cas 2019, certains documents csv ont trop de lignes (il y a des lignes vides à la fin)\n",
    "            if (row[2] == ''):\n",
    "                continue\n",
    "\n",
    "            autorisations_engagement = int(row[7].replace(' ', '')) if row[7].replace(' ', '') != '' else 0\n",
    "            autorisations_engagement_total += autorisations_engagement\n",
    "            credits_paiement = int(row[9].replace(' ', '')) if row[9].replace(' ', '') != '' else 0\n",
    "            credits_paiement_total += credits_paiement\n",
    "\n",
    "            # On écrit dans le fichier cible toutes les informations utiles\n",
    "            rows.append([\n",
    "                type_de_mission,\n",
    "                code_type_de_mission,\n",
    "                row[0],\n",
    "                mission_to_code[row[0]],\n",
    "                code_to_programme[row[1]] if row[1] in code_to_programme else 'Nom de programme introuvable',\n",
    "                row[1],\n",
    "                row[3],\n",
    "                row[2],\n",
    "                row[5],\n",
    "                row[4],\n",
    "                autorisations_engagement,\n",
    "                credits_paiement\n",
    "            ])\n",
    "\n",
    "print(credits_paiement_total, autorisations_engagement_total)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "init_node = dict({\n",
    "    'name': 'PLF',\n",
    "    'children': [],\n",
    "    'code': 'PLF'\n",
    "})\n",
    "\n",
    "def modify_current_node(current_node, row):\n",
    "    current_node['cp'] = int(row[11])\n",
    "    current_node['ae'] = int(row[10])\n",
    "    current_node['size'] = int(row[11])\n",
    "\n",
    "def callback(root_node):\n",
    "    recKey(root_node, 'cp')\n",
    "    recKey(root_node, 'ae')\n",
    "    recKey(root_node, 'size')\n",
    "\n",
    "root_node = build_d3_hierarchy(\n",
    "    rows, init_node,\n",
    "    [0, 2, 4, 6, 8], [1, 3, 5, 7, 9],\n",
    "    modify_current_node=modify_current_node,\n",
    "    callback=callback\n",
    ")\n",
    "\n",
    "write_d3_hierarchy(root_node, '../server/resources/hierarchy/plf/2018.json')\n",
    "write_flat_hierarchy(root_node, '../server/resources/flat/plf/2018.json')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Recettes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('./input_data/decoded_data/plf_2018/PLF2018-Recettes_fiscales-EtatA.csv', 'r') as recettes:\n",
    "    csv_reader = csv.reader(recettes, delimiter=';')\n",
    "    for _ in range(4):\n",
    "        next(csv_reader)\n",
    "\n",
    "    root_node = dict({\n",
    "        'name': 'Recettes',\n",
    "        'children': [],\n",
    "        'code': 'REC'\n",
    "    })\n",
    "\n",
    "    for row in csv_reader:\n",
    "        line_number = row[0]\n",
    "        label = row[1]\n",
    "        montant = int(row[2].replace(\" \", '')) if row[2] else None\n",
    "\n",
    "        if line_number:\n",
    "            codes = [line_number[0], line_number[1], line_number[2:]]\n",
    "        else:\n",
    "            m = re.match('(.*)\\.\\s?(.*)', label)\n",
    "            codes = list(m.group(1))\n",
    "            label = m.group(2)\n",
    "\n",
    "        current_node = root_node\n",
    "        for code_index, code in enumerate(codes[:-1]):\n",
    "            if 'children' not in current_node:\n",
    "                current_node['children'] = []\n",
    "\n",
    "            for index, child in enumerate(current_node['children']):\n",
    "                if child['code'].startswith('-'.join(codes[:code_index+1])):\n",
    "                    current_node = current_node['children'][index]\n",
    "\n",
    "        if 'children' not in current_node:\n",
    "            current_node['children'] = []\n",
    "\n",
    "        current_node['children'].append(dict({\n",
    "            'name': label,\n",
    "            'code': '-'.join(codes),\n",
    "        }))\n",
    "\n",
    "        # Terrible workaround for finding tree leaves\n",
    "        if len(codes) == 3 or codes == ['4', '1']:\n",
    "            current_node['children'][-1]['size'] = montant\n",
    "\n",
    "    recKey(root_node, 'size')\n",
    "\n",
    "write_d3_hierarchy(root_node, '../server/resources/hierarchy/recettes/2018.json')\n",
    "write_flat_hierarchy(root_node, '../server/resources/flat/recettes/2018.json')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2017"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 258,
   "metadata": {},
   "outputs": [],
   "source": [
    "SOURCE_FOLDER = './input_data/decoded_data/plf_2017'\n",
    "\n",
    "type_de_mission_to_filename = {\n",
    "    \"Budget général\": \"BG\",\n",
    "#     \"Budgets annexes\": \"BA\",\n",
    "    \"Comptes d'affectation spéciale\": \"CS\",\n",
    "    \"Comptes de concours financiers\": \"CF\"\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 260,
   "metadata": {},
   "outputs": [],
   "source": [
    "mission_to_code = {}\n",
    "code_to_programme = {}\n",
    "code_to_action = {}\n",
    "\n",
    "with open(os.path.join('./input_data/nomenclatures_plf/plf_2017.csv'), 'r') as csv_file:\n",
    "    csv_reader = csv.reader(csv_file, delimiter=',')\n",
    "    # On saute la première ligne parce que c'est un header\n",
    "    next(csv_reader, None)\n",
    "\n",
    "    for row in csv_reader:\n",
    "        code_mission = row[3]\n",
    "        mission = row[4]\n",
    "        code_programme = row[5]\n",
    "        programme = row[6]\n",
    "        code_action = row[7]\n",
    "        action = row[8]\n",
    "\n",
    "        mission_to_code[mission] = code_mission\n",
    "        code_to_programme[code_programme] = programme\n",
    "        code_to_action['-'.join([code_programme, code_action])] = action"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 261,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "630390141338 650044472748\n"
     ]
    }
   ],
   "source": [
    "rows = []\n",
    "credits_paiement_total = 0\n",
    "autorisations_engagement_total = 0\n",
    "\n",
    "for type_de_mission in type_de_mission_to_code:\n",
    "    if type_de_mission == 'Budgets annexes':\n",
    "        continue\n",
    "\n",
    "    code_type_de_mission = type_de_mission_to_code[type_de_mission]\n",
    "    code_filename = type_de_mission_to_filename[type_de_mission]\n",
    "\n",
    "    with open(os.path.join(\n",
    "        SOURCE_FOLDER,\n",
    "        'PLF2017-{}-Msn-Nat.csv'.format(code_filename)), 'r') as csv_file:\n",
    "\n",
    "        csv_reader = csv.reader(csv_file, delimiter=';')\n",
    "        # On saute les headers (4 lignes)\n",
    "        for i in range(4):\n",
    "            next(csv_reader, None)\n",
    "\n",
    "        for index, row in enumerate(csv_reader):\n",
    "            autorisations_engagement = int(row[6].replace(' ', '')) if row[6].replace(' ', '') != '' else 0\n",
    "            autorisations_engagement_total += autorisations_engagement\n",
    "            credits_paiement = int(row[8].replace(' ', '')) if row[8].replace(' ', '') != '' else 0\n",
    "            credits_paiement_total += credits_paiement\n",
    "\n",
    "            code_action = '0' + row[2] if len(row[2]) == 1 else row[2]\n",
    "            ccode_action = '-'.join([row[1], code_action])\n",
    "\n",
    "            # On écrit dans le fichier cible toutes les informations utiles\n",
    "            rows.append([\n",
    "                type_de_mission,\n",
    "                code_type_de_mission,\n",
    "                row[0],\n",
    "                mission_to_code[row[0]],\n",
    "                code_to_programme[row[1]] if row[1] in code_to_programme else 'Nom de programme introuvable',\n",
    "                row[1],\n",
    "                code_to_action[ccode_action] if ccode_action in code_to_action else 'Nom d\\'action introuvable',\n",
    "                code_action,\n",
    "                row[4],\n",
    "                row[3],\n",
    "                autorisations_engagement,\n",
    "                credits_paiement\n",
    "            ])\n",
    "\n",
    "print(credits_paiement_total, autorisations_engagement_total)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 270,
   "metadata": {},
   "outputs": [],
   "source": [
    "init_node = dict({\n",
    "    'name': 'PLF',\n",
    "    'children': [],\n",
    "    'code': 'PLF'\n",
    "})\n",
    "\n",
    "def modify_current_node(current_node, row):\n",
    "    current_node['cp'] = int(row[11])\n",
    "    current_node['ae'] = int(row[10])\n",
    "    current_node['size'] = int(row[11])\n",
    "\n",
    "def callback(root_node):\n",
    "    recKey(root_node, 'cp')\n",
    "    recKey(root_node, 'ae')\n",
    "    recKey(root_node, 'size')\n",
    "\n",
    "root_node = build_d3_hierarchy(\n",
    "    rows, init_node,\n",
    "    [0, 2, 4, 6, 8], [1, 3, 5, 7, 9],\n",
    "    modify_current_node=modify_current_node,\n",
    "    callback=callback\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On a calculé le PLF 2017 avec les données de la comptabilité générale... Il faut couper l'arbre après le niveau `action`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 271,
   "metadata": {},
   "outputs": [],
   "source": [
    "def rec_cut_tree(node, depth, max_depth=4):\n",
    "    d = dict({\n",
    "        'name': node['name'],\n",
    "        'code': node['code'],\n",
    "        'ae': node['ae'],\n",
    "        'cp': node['cp'],\n",
    "        'size': node['size']\n",
    "    })\n",
    "\n",
    "    if depth < max_depth:\n",
    "        d['children'] = [rec_cut_tree(child, depth+1, max_depth=max_depth) for child in node['children']]\n",
    "\n",
    "    return d"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 272,
   "metadata": {},
   "outputs": [],
   "source": [
    "cut_tree = rec_cut_tree(root_node, 0, max_depth=4)\n",
    "with open('../server/resources/hierarchy/plf/2017.json', 'w') as output_json:\n",
    "    json.dump(cut_tree, output_json)\n",
    "\n",
    "with open('../server/resources/flat/plf/2017.json', 'w+') as output_file:\n",
    "    d = dict()\n",
    "    build_flat_tree(d, cut_tree)\n",
    "\n",
    "    json.dump(d, output_file)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2016"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 277,
   "metadata": {},
   "outputs": [],
   "source": [
    "SOURCE_FOLDER = './input_data/decoded_data/plf_2016'\n",
    "\n",
    "type_de_mission_to_filename = {\n",
    "    \"Budget général\": \"BG\",\n",
    "    \"Budgets annexes\": \"BA\",\n",
    "    \"Comptes d'affectation spéciale\": \"CS\",\n",
    "#     \"Comptes de concours financiers\": \"CF\"\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 279,
   "metadata": {},
   "outputs": [],
   "source": [
    "mission_to_code = {}\n",
    "code_to_programme = {}\n",
    "\n",
    "with open(os.path.join('./input_data/nomenclatures_plf/plf_2016.csv'), 'r') as csv_file:\n",
    "    csv_reader = csv.reader(csv_file, delimiter=',')\n",
    "    # On saute la première ligne parce que c'est un header\n",
    "    next(csv_reader, None)\n",
    "\n",
    "    for row in csv_reader:\n",
    "        code_mission = row[3]\n",
    "        mission = row[4]\n",
    "        code_programme = row[5]\n",
    "        programme = row[6]\n",
    "\n",
    "        mission_to_code[mission] = code_mission\n",
    "        code_to_programme[code_programme] = programme"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 280,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "744156246731 744156246731\n"
     ]
    }
   ],
   "source": [
    "rows = []\n",
    "credits_paiement_total = 0\n",
    "autorisations_engagement_total = 0\n",
    "\n",
    "for type_de_mission in type_de_mission_to_code:\n",
    "     # CAS SPECIAL ICI : il n'y a pas de CF, il me semble qu'ils sont dans les CS...\n",
    "    if type_de_mission == 'Comptes de concours financiers':\n",
    "        continue\n",
    "    code_type_de_mission = type_de_mission_to_code[type_de_mission]\n",
    "    code_filename = type_de_mission_to_filename[type_de_mission]\n",
    "\n",
    "    with open(os.path.join(\n",
    "        SOURCE_FOLDER,\n",
    "        'PLF2016-{}-Msn-Dest.csv'.format(code_filename)), 'r') as csv_file:\n",
    "\n",
    "        csv_reader = csv.reader(csv_file, delimiter=';')\n",
    "        # On saute les headers (4 lignes)\n",
    "        for i in range(4):\n",
    "            next(csv_reader, None)\n",
    "\n",
    "        for index, row in enumerate(csv_reader):\n",
    "            # Petite subtilité pour le cas 2019, certains documents csv ont trop de lignes (il y a des lignes vides à la fin)\n",
    "            if (row[2] == ''):\n",
    "                continue\n",
    "\n",
    "            autorisations_engagement = int(row[7].replace(' ', '')) if row[7].replace(' ', '') != '' else 0\n",
    "            autorisations_engagement_total += autorisations_engagement\n",
    "            credits_paiement = int(row[9].replace(' ', '')) if row[9].replace(' ', '') != '' else 0\n",
    "            credits_paiement_total += credits_paiement\n",
    "\n",
    "            # On écrit dans le fichier cible toutes les informations utiles\n",
    "            rows.append([\n",
    "                type_de_mission,\n",
    "                code_type_de_mission,\n",
    "                row[0],\n",
    "                mission_to_code[row[0]],\n",
    "                code_to_programme[row[1]] if row[1] in code_to_programme else 'Nom de programme introuvable',\n",
    "                row[1],\n",
    "                row[3],\n",
    "                row[2],\n",
    "                row[5],\n",
    "                row[4],\n",
    "                autorisations_engagement,\n",
    "                credits_paiement\n",
    "            ])\n",
    "\n",
    "print(credits_paiement_total, autorisations_engagement_total)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 281,
   "metadata": {},
   "outputs": [],
   "source": [
    "init_node = dict({\n",
    "    'name': 'PLF',\n",
    "    'children': [],\n",
    "    'code': 'PLF'\n",
    "})\n",
    "\n",
    "def modify_current_node(current_node, row):\n",
    "    current_node['cp'] = int(row[11])\n",
    "    current_node['ae'] = int(row[10])\n",
    "    current_node['size'] = int(row[11])\n",
    "\n",
    "def callback(root_node):\n",
    "    recKey(root_node, 'cp')\n",
    "    recKey(root_node, 'ae')\n",
    "    recKey(root_node, 'size')\n",
    "\n",
    "root_node = build_d3_hierarchy(\n",
    "    rows, init_node,\n",
    "    [0, 2, 4, 6, 8], [1, 3, 5, 7, 9],\n",
    "    modify_current_node=modify_current_node,\n",
    "    callback=callback\n",
    ")\n",
    "\n",
    "write_d3_hierarchy(root_node, '../server/resources/hierarchy/plf/2016.json')\n",
    "write_flat_hierarchy(root_node, '../server/resources/flat/plf/2016.json')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2015"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 282,
   "metadata": {},
   "outputs": [],
   "source": [
    "SOURCE_FOLDER = './input_data/decoded_data/plf_2015'\n",
    "\n",
    "type_de_mission_to_filename = {\n",
    "    \"Budget général\": \"BG\",\n",
    "    \"Budgets annexes\": \"BA\",\n",
    "    \"Comptes d'affectation spéciale\": \"CS\",\n",
    "#     \"Comptes de concours financiers\": \"CF\"\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 283,
   "metadata": {},
   "outputs": [],
   "source": [
    "mission_to_code = {}\n",
    "code_to_programme = {}\n",
    "\n",
    "with open(os.path.join('./input_data/nomenclatures_plf/plf_2015.csv'), 'r') as csv_file:\n",
    "    csv_reader = csv.reader(csv_file, delimiter=',')\n",
    "    # On saute la première ligne parce que c'est un header\n",
    "    next(csv_reader, None)\n",
    "\n",
    "    for row in csv_reader:\n",
    "        code_mission = row[3]\n",
    "        mission = row[4]\n",
    "        code_programme = row[5]\n",
    "        programme = row[6]\n",
    "\n",
    "        mission_to_code[mission] = code_mission\n",
    "        code_to_programme[code_programme] = programme"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 284,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "\"Gestion et valorisation des ressources tirées de l'utilisation du spectre hertzien, des systèmes et des infrastructures de télécommunications de l'État\"",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-284-46bf570ad9f3>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m     34\u001b[0m                 \u001b[0mcode_type_de_mission\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     35\u001b[0m                 \u001b[0mrow\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 36\u001b[0;31m                 \u001b[0mmission_to_code\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mrow\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     37\u001b[0m                 \u001b[0mcode_to_programme\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mrow\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mrow\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mcode_to_programme\u001b[0m \u001b[0;32melse\u001b[0m \u001b[0;34m'Nom de programme introuvable'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     38\u001b[0m                 \u001b[0mrow\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mKeyError\u001b[0m: \"Gestion et valorisation des ressources tirées de l'utilisation du spectre hertzien, des systèmes et des infrastructures de télécommunications de l'État\""
     ]
    }
   ],
   "source": [
    "rows = []\n",
    "credits_paiement_total = 0\n",
    "autorisations_engagement_total = 0\n",
    "\n",
    "for type_de_mission in type_de_mission_to_code:\n",
    "     # CAS SPECIAL ICI : il n'y a pas de CF, il me semble qu'ils sont dans les CS...\n",
    "    if type_de_mission == 'Comptes de concours financiers':\n",
    "        continue\n",
    "    code_type_de_mission = type_de_mission_to_code[type_de_mission]\n",
    "    code_filename = type_de_mission_to_filename[type_de_mission]\n",
    "\n",
    "    with open(os.path.join(\n",
    "        SOURCE_FOLDER,\n",
    "        'PLF2015-{}-Msn-Dest.csv'.format(code_filename)), 'r') as csv_file:\n",
    "\n",
    "        csv_reader = csv.reader(csv_file, delimiter=';')\n",
    "        # On saute les headers (4 lignes)\n",
    "        for i in range(4):\n",
    "            next(csv_reader, None)\n",
    "\n",
    "        for index, row in enumerate(csv_reader):\n",
    "            # Petite subtilité pour le cas 2019, certains documents csv ont trop de lignes (il y a des lignes vides à la fin)\n",
    "            if (row[2] == ''):\n",
    "                continue\n",
    "\n",
    "            autorisations_engagement = int(row[7].replace(' ', '')) if row[7].replace(' ', '') != '' else 0\n",
    "            autorisations_engagement_total += autorisations_engagement\n",
    "            credits_paiement = int(row[9].replace(' ', '')) if row[9].replace(' ', '') != '' else 0\n",
    "            credits_paiement_total += credits_paiement\n",
    "\n",
    "            # On écrit dans le fichier cible toutes les informations utiles\n",
    "            rows.append([\n",
    "                type_de_mission,\n",
    "                code_type_de_mission,\n",
    "                row[0],\n",
    "                mission_to_code[row[0]],\n",
    "                code_to_programme[row[1]] if row[1] in code_to_programme else 'Nom de programme introuvable',\n",
    "                row[1],\n",
    "                row[3],\n",
    "                row[2],\n",
    "                row[5],\n",
    "                row[4],\n",
    "                autorisations_engagement,\n",
    "                credits_paiement\n",
    "            ])\n",
    "\n",
    "print(credits_paiement_total, autorisations_engagement_total)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "```Toute chose appartient à qui sait en jouir.```"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}