finess/clean.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "43f2e65e",
   "metadata": {},
   "source": [
    "# Production d'un csv utilisable de la base FINESS\n",
    "\n",
    "En l'état, l'export CSV de la [base FINESS][finess] n'est pas vraiment satisfaisant et utilisable.\n",
    "\n",
    "- Le fichier n'est pas réellement un CSV.\n",
    "    - Il est bizarrement découpé en deux sections qui correspondent au XML.\n",
    "    - Les colonnes n'ont pas de nom.\n",
    "- Le fichier est encodé au format windows.\n",
    "\n",
    "[finess]: https://www.data.gouv.fr/en/datasets/finess-extraction-du-fichier-des-etablissements/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "be92c340",
   "metadata": {
    "gradient": {
     "editing": false,
     "id": "4facc182",
     "kernelId": ""
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import requests"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "a7821b92",
   "metadata": {
    "gradient": {
     "editing": false,
     "id": "3f7b5d32",
     "kernelId": ""
    }
   },
   "outputs": [],
   "source": [
    "dataset_api = \"https://www.data.gouv.fr/api/1/datasets/finess-extraction-du-fichier-des-etablissements/\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "e3d1b3e4",
   "metadata": {
    "gradient": {
     "editing": false,
     "id": "58d641d4",
     "kernelId": ""
    }
   },
   "outputs": [],
   "source": [
    "resources = (requests\n",
    "    .get(dataset_api)\n",
    "    .json()\n",
    "    ['resources']\n",
    ")\n",
    "\n",
    "resource_geoloc = [ r for r in resources if r['type'] == 'main' and 'géolocalisés' in r['title']][0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "596f22fc",
   "metadata": {
    "gradient": {
     "editing": false,
     "id": "13dd939b",
     "kernelId": ""
    }
   },
   "outputs": [],
   "source": [
    "headers = [\n",
    "    'section',\n",
    "    'nofinesset',\n",
    "    'nofinessej',\n",
    "    'rs',\n",
    "    'rslongue',\n",
    "    'complrs',\n",
    "    'compldistrib',\n",
    "    'numvoie',\n",
    "    'typvoie',\n",
    "    'voie',\n",
    "    'compvoie',\n",
    "    'lieuditbp',\n",
    "    'commune',\n",
    "    'departement',\n",
    "    'libdepartement',\n",
    "    'ligneacheminement',\n",
    "    'telephone',\n",
    "    'telecopie',\n",
    "    'categetab',\n",
    "    'libcategetab',\n",
    "    'categagretab',\n",
    "    'libcategagretab',\n",
    "    'siret',\n",
    "    'codeape',\n",
    "    'codemft',\n",
    "    'libmft',\n",
    "    'codesph',\n",
    "    'libsph',\n",
    "    'dateouv',\n",
    "    'dateautor',\n",
    "    'maj',\n",
    "    'numuai'\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "d42a4473",
   "metadata": {
    "gradient": {
     "editing": false,
     "id": "b68dac89",
     "kernelId": ""
    }
   },
   "outputs": [],
   "source": [
    "geoloc_names = [\n",
    "    'nofinesset',\n",
    "    'coordxet',\n",
    "    'coordyet',\n",
    "    'sourcecoordet',\n",
    "    'datemaj'\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "cdf25f03",
   "metadata": {
    "gradient": {
     "editing": false,
     "id": "4492d3dd",
     "kernelId": ""
    }
   },
   "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>nofinesset</th>\n",
       "      <th>nofinessej</th>\n",
       "      <th>rs</th>\n",
       "      <th>rslongue</th>\n",
       "      <th>complrs</th>\n",
       "      <th>compldistrib</th>\n",
       "      <th>numvoie</th>\n",
       "      <th>typvoie</th>\n",
       "      <th>voie</th>\n",
       "      <th>compvoie</th>\n",
       "      <th>...</th>\n",
       "      <th>siret</th>\n",
       "      <th>codeape</th>\n",
       "      <th>codemft</th>\n",
       "      <th>libmft</th>\n",
       "      <th>codesph</th>\n",
       "      <th>libsph</th>\n",
       "      <th>dateouv</th>\n",
       "      <th>dateautor</th>\n",
       "      <th>maj</th>\n",
       "      <th>numuai</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>010000024</td>\n",
       "      <td>010780054</td>\n",
       "      <td>CH DE FLEYRIAT</td>\n",
       "      <td>CENTRE HOSPITALIER DE BOURG-EN-BRESSE FLEYRIAT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>900</td>\n",
       "      <td>RTE</td>\n",
       "      <td>DE PARIS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>26010004500012</td>\n",
       "      <td>8610Z</td>\n",
       "      <td>03</td>\n",
       "      <td>ARS établissements Publics de santé dotation g...</td>\n",
       "      <td>1</td>\n",
       "      <td>Etablissement public de santé</td>\n",
       "      <td>1979-02-13</td>\n",
       "      <td>1979-02-13</td>\n",
       "      <td>2020-02-04</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>010000032</td>\n",
       "      <td>010780062</td>\n",
       "      <td>CH BUGEY SUD</td>\n",
       "      <td>CENTRE HOSPITALIER BUGEY SUD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>700</td>\n",
       "      <td>AV</td>\n",
       "      <td>DE NARVIK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>26010003700068</td>\n",
       "      <td>8610Z</td>\n",
       "      <td>03</td>\n",
       "      <td>ARS établissements Publics de santé dotation g...</td>\n",
       "      <td>1</td>\n",
       "      <td>Etablissement public de santé</td>\n",
       "      <td>1901-01-01</td>\n",
       "      <td>1901-01-01</td>\n",
       "      <td>2021-07-07</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>010000065</td>\n",
       "      <td>010780096</td>\n",
       "      <td>CH DE TREVOUX - MONTPENSIER</td>\n",
       "      <td>CENTRE HOSPITALIER DE TREVOUX - MONTPENSIER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14</td>\n",
       "      <td>R</td>\n",
       "      <td>DE L'HOPITAL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>26010028400017</td>\n",
       "      <td>8610Z</td>\n",
       "      <td>03</td>\n",
       "      <td>ARS établissements Publics de santé dotation g...</td>\n",
       "      <td>1</td>\n",
       "      <td>Etablissement public de santé</td>\n",
       "      <td>1901-01-01</td>\n",
       "      <td>1901-01-01</td>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>010000081</td>\n",
       "      <td>010780112</td>\n",
       "      <td>CH DU PAYS DE GEX</td>\n",
       "      <td>CENTRE HOSPITALIER DU PAYS DE GEX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>160</td>\n",
       "      <td>R</td>\n",
       "      <td>MARC PANISSOD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>26010010200011</td>\n",
       "      <td>8610Z</td>\n",
       "      <td>03</td>\n",
       "      <td>ARS établissements Publics de santé dotation g...</td>\n",
       "      <td>1</td>\n",
       "      <td>Etablissement public de santé</td>\n",
       "      <td>1901-01-01</td>\n",
       "      <td>1901-01-01</td>\n",
       "      <td>2020-02-04</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>010000099</td>\n",
       "      <td>010780120</td>\n",
       "      <td>CH DE MEXIMIEUX</td>\n",
       "      <td>CENTRE HOSPITALIER DE MEXIMIEUX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13</td>\n",
       "      <td>AV</td>\n",
       "      <td>DU DOCTEUR BOYER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>26010013600019</td>\n",
       "      <td>8610Z</td>\n",
       "      <td>03</td>\n",
       "      <td>ARS établissements Publics de santé dotation g...</td>\n",
       "      <td>1</td>\n",
       "      <td>Etablissement public de santé</td>\n",
       "      <td>1945-01-01</td>\n",
       "      <td>1945-01-01</td>\n",
       "      <td>2020-06-30</td>\n",
       "      <td>NaN</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>191487</th>\n",
       "      <td>980501738</td>\n",
       "      <td>521486.0</td>\n",
       "      <td>8583077.0</td>\n",
       "      <td>1,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,UTM_S38</td>\n",
       "      <td>2022-11-04</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191488</th>\n",
       "      <td>980501779</td>\n",
       "      <td>524168.6</td>\n",
       "      <td>8588089.7</td>\n",
       "      <td>2,ATLASANTE,84,IGN,BD_ADRESSE,V2.2,UTM_S38</td>\n",
       "      <td>2022-11-04</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191489</th>\n",
       "      <td>980501878</td>\n",
       "      <td>514247.9</td>\n",
       "      <td>8582244.9</td>\n",
       "      <td>1,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,UTM_S38</td>\n",
       "      <td>2022-11-04</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191490</th>\n",
       "      <td>980502199</td>\n",
       "      <td>522250.7</td>\n",
       "      <td>8592077.7</td>\n",
       "      <td>3,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,UTM_S38</td>\n",
       "      <td>2022-11-04</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191491</th>\n",
       "      <td>980502439</td>\n",
       "      <td>520063.8</td>\n",
       "      <td>8580141.1</td>\n",
       "      <td>3,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,UTM_S38</td>\n",
       "      <td>2022-11-04</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>191492 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       nofinesset nofinessej                           rs  \\\n",
       "0       010000024  010780054               CH DE FLEYRIAT   \n",
       "1       010000032  010780062                 CH BUGEY SUD   \n",
       "2       010000065  010780096  CH DE TREVOUX - MONTPENSIER   \n",
       "3       010000081  010780112            CH DU PAYS DE GEX   \n",
       "4       010000099  010780120              CH DE MEXIMIEUX   \n",
       "...           ...        ...                          ...   \n",
       "191487  980501738   521486.0                    8583077.0   \n",
       "191488  980501779   524168.6                    8588089.7   \n",
       "191489  980501878   514247.9                    8582244.9   \n",
       "191490  980502199   522250.7                    8592077.7   \n",
       "191491  980502439   520063.8                    8580141.1   \n",
       "\n",
       "                                              rslongue     complrs  \\\n",
       "0       CENTRE HOSPITALIER DE BOURG-EN-BRESSE FLEYRIAT         NaN   \n",
       "1                         CENTRE HOSPITALIER BUGEY SUD         NaN   \n",
       "2          CENTRE HOSPITALIER DE TREVOUX - MONTPENSIER         NaN   \n",
       "3                    CENTRE HOSPITALIER DU PAYS DE GEX         NaN   \n",
       "4                      CENTRE HOSPITALIER DE MEXIMIEUX         NaN   \n",
       "...                                                ...         ...   \n",
       "191487     1,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,UTM_S38  2022-11-04   \n",
       "191488      2,ATLASANTE,84,IGN,BD_ADRESSE,V2.2,UTM_S38  2022-11-04   \n",
       "191489     1,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,UTM_S38  2022-11-04   \n",
       "191490     3,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,UTM_S38  2022-11-04   \n",
       "191491     3,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,UTM_S38  2022-11-04   \n",
       "\n",
       "       compldistrib numvoie typvoie              voie compvoie  ...  \\\n",
       "0               NaN     900     RTE          DE PARIS      NaN  ...   \n",
       "1               NaN     700      AV         DE NARVIK      NaN  ...   \n",
       "2               NaN      14       R      DE L'HOPITAL      NaN  ...   \n",
       "3               NaN     160       R     MARC PANISSOD      NaN  ...   \n",
       "4               NaN      13      AV  DU DOCTEUR BOYER      NaN  ...   \n",
       "...             ...     ...     ...               ...      ...  ...   \n",
       "191487          NaN     NaN     NaN               NaN      NaN  ...   \n",
       "191488          NaN     NaN     NaN               NaN      NaN  ...   \n",
       "191489          NaN     NaN     NaN               NaN      NaN  ...   \n",
       "191490          NaN     NaN     NaN               NaN      NaN  ...   \n",
       "191491          NaN     NaN     NaN               NaN      NaN  ...   \n",
       "\n",
       "                 siret codeape codemft  \\\n",
       "0       26010004500012   8610Z      03   \n",
       "1       26010003700068   8610Z      03   \n",
       "2       26010028400017   8610Z      03   \n",
       "3       26010010200011   8610Z      03   \n",
       "4       26010013600019   8610Z      03   \n",
       "...                ...     ...     ...   \n",
       "191487             NaN     NaN     NaN   \n",
       "191488             NaN     NaN     NaN   \n",
       "191489             NaN     NaN     NaN   \n",
       "191490             NaN     NaN     NaN   \n",
       "191491             NaN     NaN     NaN   \n",
       "\n",
       "                                                   libmft codesph  \\\n",
       "0       ARS établissements Publics de santé dotation g...       1   \n",
       "1       ARS établissements Publics de santé dotation g...       1   \n",
       "2       ARS établissements Publics de santé dotation g...       1   \n",
       "3       ARS établissements Publics de santé dotation g...       1   \n",
       "4       ARS établissements Publics de santé dotation g...       1   \n",
       "...                                                   ...     ...   \n",
       "191487                                                NaN     NaN   \n",
       "191488                                                NaN     NaN   \n",
       "191489                                                NaN     NaN   \n",
       "191490                                                NaN     NaN   \n",
       "191491                                                NaN     NaN   \n",
       "\n",
       "                               libsph     dateouv   dateautor         maj  \\\n",
       "0       Etablissement public de santé  1979-02-13  1979-02-13  2020-02-04   \n",
       "1       Etablissement public de santé  1901-01-01  1901-01-01  2021-07-07   \n",
       "2       Etablissement public de santé  1901-01-01  1901-01-01  2018-01-12   \n",
       "3       Etablissement public de santé  1901-01-01  1901-01-01  2020-02-04   \n",
       "4       Etablissement public de santé  1945-01-01  1945-01-01  2020-06-30   \n",
       "...                               ...         ...         ...         ...   \n",
       "191487                            NaN         NaN         NaN         NaN   \n",
       "191488                            NaN         NaN         NaN         NaN   \n",
       "191489                            NaN         NaN         NaN         NaN   \n",
       "191490                            NaN         NaN         NaN         NaN   \n",
       "191491                            NaN         NaN         NaN         NaN   \n",
       "\n",
       "       numuai  \n",
       "0         NaN  \n",
       "1         NaN  \n",
       "2         NaN  \n",
       "3         NaN  \n",
       "4         NaN  \n",
       "...       ...  \n",
       "191487    NaN  \n",
       "191488    NaN  \n",
       "191489    NaN  \n",
       "191490    NaN  \n",
       "191491    NaN  \n",
       "\n",
       "[191492 rows x 31 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "raw_df = (pd\n",
    "    .read_csv(resource_geoloc['url'],\n",
    "              sep=\";\", encoding=\"Windows-1252\", header=None, skiprows=1,\n",
    "              dtype='str',\n",
    "              names=headers)\n",
    "    .drop(columns=['section'])\n",
    ")\n",
    "\n",
    "raw_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7a46d4d2",
   "metadata": {
    "gradient": {
     "editing": false,
     "id": "2efc14bc",
     "kernelId": ""
    }
   },
   "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>nofinesset</th>\n",
       "      <th>nofinessej</th>\n",
       "      <th>rs</th>\n",
       "      <th>rslongue</th>\n",
       "      <th>complrs</th>\n",
       "      <th>compldistrib</th>\n",
       "      <th>numvoie</th>\n",
       "      <th>typvoie</th>\n",
       "      <th>voie</th>\n",
       "      <th>compvoie</th>\n",
       "      <th>...</th>\n",
       "      <th>siret</th>\n",
       "      <th>codeape</th>\n",
       "      <th>codemft</th>\n",
       "      <th>libmft</th>\n",
       "      <th>codesph</th>\n",
       "      <th>libsph</th>\n",
       "      <th>dateouv</th>\n",
       "      <th>dateautor</th>\n",
       "      <th>maj</th>\n",
       "      <th>numuai</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>010000024</td>\n",
       "      <td>010780054</td>\n",
       "      <td>CH DE FLEYRIAT</td>\n",
       "      <td>CENTRE HOSPITALIER DE BOURG-EN-BRESSE FLEYRIAT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>900</td>\n",
       "      <td>RTE</td>\n",
       "      <td>DE PARIS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>26010004500012</td>\n",
       "      <td>8610Z</td>\n",
       "      <td>03</td>\n",
       "      <td>ARS établissements Publics de santé dotation g...</td>\n",
       "      <td>1</td>\n",
       "      <td>Etablissement public de santé</td>\n",
       "      <td>1979-02-13</td>\n",
       "      <td>1979-02-13</td>\n",
       "      <td>2020-02-04</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>010000032</td>\n",
       "      <td>010780062</td>\n",
       "      <td>CH BUGEY SUD</td>\n",
       "      <td>CENTRE HOSPITALIER BUGEY SUD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>700</td>\n",
       "      <td>AV</td>\n",
       "      <td>DE NARVIK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>26010003700068</td>\n",
       "      <td>8610Z</td>\n",
       "      <td>03</td>\n",
       "      <td>ARS établissements Publics de santé dotation g...</td>\n",
       "      <td>1</td>\n",
       "      <td>Etablissement public de santé</td>\n",
       "      <td>1901-01-01</td>\n",
       "      <td>1901-01-01</td>\n",
       "      <td>2021-07-07</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>010000065</td>\n",
       "      <td>010780096</td>\n",
       "      <td>CH DE TREVOUX - MONTPENSIER</td>\n",
       "      <td>CENTRE HOSPITALIER DE TREVOUX - MONTPENSIER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14</td>\n",
       "      <td>R</td>\n",
       "      <td>DE L'HOPITAL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>26010028400017</td>\n",
       "      <td>8610Z</td>\n",
       "      <td>03</td>\n",
       "      <td>ARS établissements Publics de santé dotation g...</td>\n",
       "      <td>1</td>\n",
       "      <td>Etablissement public de santé</td>\n",
       "      <td>1901-01-01</td>\n",
       "      <td>1901-01-01</td>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>010000081</td>\n",
       "      <td>010780112</td>\n",
       "      <td>CH DU PAYS DE GEX</td>\n",
       "      <td>CENTRE HOSPITALIER DU PAYS DE GEX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>160</td>\n",
       "      <td>R</td>\n",
       "      <td>MARC PANISSOD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>26010010200011</td>\n",
       "      <td>8610Z</td>\n",
       "      <td>03</td>\n",
       "      <td>ARS établissements Publics de santé dotation g...</td>\n",
       "      <td>1</td>\n",
       "      <td>Etablissement public de santé</td>\n",
       "      <td>1901-01-01</td>\n",
       "      <td>1901-01-01</td>\n",
       "      <td>2020-02-04</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>010000099</td>\n",
       "      <td>010780120</td>\n",
       "      <td>CH DE MEXIMIEUX</td>\n",
       "      <td>CENTRE HOSPITALIER DE MEXIMIEUX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13</td>\n",
       "      <td>AV</td>\n",
       "      <td>DU DOCTEUR BOYER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>26010013600019</td>\n",
       "      <td>8610Z</td>\n",
       "      <td>03</td>\n",
       "      <td>ARS établissements Publics de santé dotation g...</td>\n",
       "      <td>1</td>\n",
       "      <td>Etablissement public de santé</td>\n",
       "      <td>1945-01-01</td>\n",
       "      <td>1945-01-01</td>\n",
       "      <td>2020-06-30</td>\n",
       "      <td>NaN</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>95741</th>\n",
       "      <td>980502389</td>\n",
       "      <td>590799730</td>\n",
       "      <td>SAMSAH TUTUBIYA - ALEFPA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>390</td>\n",
       "      <td>R</td>\n",
       "      <td>DES FRANGIPANIERS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>77562407502209</td>\n",
       "      <td>NaN</td>\n",
       "      <td>57</td>\n",
       "      <td>ARS /ARS PCD   Dotation forfait ou prix de jou...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-08</td>\n",
       "      <td>2021-12-20</td>\n",
       "      <td>2022-07-19</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95742</th>\n",
       "      <td>980502405</td>\n",
       "      <td>980500854</td>\n",
       "      <td>PLATEFORME SENSORIELLE - ADSM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>R</td>\n",
       "      <td>DE LA SIM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34</td>\n",
       "      <td>ARS / DG dotation globale</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-02-26</td>\n",
       "      <td>2021-04-12</td>\n",
       "      <td>2022-07-20</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95743</th>\n",
       "      <td>980502439</td>\n",
       "      <td>980500557</td>\n",
       "      <td>SEPRODOM MAYOTTE</td>\n",
       "      <td>SEPRODOM MAYOTTE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CHE</td>\n",
       "      <td>MAVIKI</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>99</td>\n",
       "      <td>Indéterminé</td>\n",
       "      <td>0</td>\n",
       "      <td>Non concerné</td>\n",
       "      <td>2020-12-01</td>\n",
       "      <td>2020-10-13</td>\n",
       "      <td>2022-10-06</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95744</th>\n",
       "      <td>980502454</td>\n",
       "      <td>980502447</td>\n",
       "      <td>LE CONFORT MEDICAL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>RTE</td>\n",
       "      <td>DES BADAMIERS</td>\n",
       "      <td>C</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>99</td>\n",
       "      <td>Indéterminé</td>\n",
       "      <td>9</td>\n",
       "      <td>indéterminé</td>\n",
       "      <td>2022-09-16</td>\n",
       "      <td>2022-09-16</td>\n",
       "      <td>2022-10-06</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95745</th>\n",
       "      <td>980502470</td>\n",
       "      <td>980502462</td>\n",
       "      <td>PHARMACIE AL SHIFAA</td>\n",
       "      <td>PHARMACIE AL SHIFAA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>54</td>\n",
       "      <td>R</td>\n",
       "      <td>Des Manguiers</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>01</td>\n",
       "      <td>Etablissement Tarif Libre</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-11-09</td>\n",
       "      <td>2022-07-25</td>\n",
       "      <td>2022-11-10</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>95746 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      nofinesset nofinessej                             rs  \\\n",
       "0      010000024  010780054                 CH DE FLEYRIAT   \n",
       "1      010000032  010780062                   CH BUGEY SUD   \n",
       "2      010000065  010780096    CH DE TREVOUX - MONTPENSIER   \n",
       "3      010000081  010780112              CH DU PAYS DE GEX   \n",
       "4      010000099  010780120                CH DE MEXIMIEUX   \n",
       "...          ...        ...                            ...   \n",
       "95741  980502389  590799730       SAMSAH TUTUBIYA - ALEFPA   \n",
       "95742  980502405  980500854  PLATEFORME SENSORIELLE - ADSM   \n",
       "95743  980502439  980500557               SEPRODOM MAYOTTE   \n",
       "95744  980502454  980502447             LE CONFORT MEDICAL   \n",
       "95745  980502470  980502462            PHARMACIE AL SHIFAA   \n",
       "\n",
       "                                             rslongue complrs compldistrib  \\\n",
       "0      CENTRE HOSPITALIER DE BOURG-EN-BRESSE FLEYRIAT     NaN          NaN   \n",
       "1                        CENTRE HOSPITALIER BUGEY SUD     NaN          NaN   \n",
       "2         CENTRE HOSPITALIER DE TREVOUX - MONTPENSIER     NaN          NaN   \n",
       "3                   CENTRE HOSPITALIER DU PAYS DE GEX     NaN          NaN   \n",
       "4                     CENTRE HOSPITALIER DE MEXIMIEUX     NaN          NaN   \n",
       "...                                               ...     ...          ...   \n",
       "95741                                             NaN     NaN          NaN   \n",
       "95742                                             NaN     NaN          NaN   \n",
       "95743                                SEPRODOM MAYOTTE     NaN          NaN   \n",
       "95744                                             NaN     NaN          NaN   \n",
       "95745                             PHARMACIE AL SHIFAA     NaN          NaN   \n",
       "\n",
       "      numvoie typvoie               voie compvoie  ...           siret  \\\n",
       "0         900     RTE           DE PARIS      NaN  ...  26010004500012   \n",
       "1         700      AV          DE NARVIK      NaN  ...  26010003700068   \n",
       "2          14       R       DE L'HOPITAL      NaN  ...  26010028400017   \n",
       "3         160       R      MARC PANISSOD      NaN  ...  26010010200011   \n",
       "4          13      AV   DU DOCTEUR BOYER      NaN  ...  26010013600019   \n",
       "...       ...     ...                ...      ...  ...             ...   \n",
       "95741     390       R  DES FRANGIPANIERS      NaN  ...  77562407502209   \n",
       "95742       4       R          DE LA SIM      NaN  ...             NaN   \n",
       "95743     NaN     CHE             MAVIKI      NaN  ...             NaN   \n",
       "95744     100     RTE      DES BADAMIERS        C  ...             NaN   \n",
       "95745      54       R      Des Manguiers      NaN  ...             NaN   \n",
       "\n",
       "      codeape codemft                                             libmft  \\\n",
       "0       8610Z      03  ARS établissements Publics de santé dotation g...   \n",
       "1       8610Z      03  ARS établissements Publics de santé dotation g...   \n",
       "2       8610Z      03  ARS établissements Publics de santé dotation g...   \n",
       "3       8610Z      03  ARS établissements Publics de santé dotation g...   \n",
       "4       8610Z      03  ARS établissements Publics de santé dotation g...   \n",
       "...       ...     ...                                                ...   \n",
       "95741     NaN      57  ARS /ARS PCD   Dotation forfait ou prix de jou...   \n",
       "95742     NaN      34                          ARS / DG dotation globale   \n",
       "95743     NaN      99                                        Indéterminé   \n",
       "95744     NaN      99                                        Indéterminé   \n",
       "95745     NaN      01                          Etablissement Tarif Libre   \n",
       "\n",
       "      codesph                         libsph     dateouv   dateautor  \\\n",
       "0           1  Etablissement public de santé  1979-02-13  1979-02-13   \n",
       "1           1  Etablissement public de santé  1901-01-01  1901-01-01   \n",
       "2           1  Etablissement public de santé  1901-01-01  1901-01-01   \n",
       "3           1  Etablissement public de santé  1901-01-01  1901-01-01   \n",
       "4           1  Etablissement public de santé  1945-01-01  1945-01-01   \n",
       "...       ...                            ...         ...         ...   \n",
       "95741     NaN                            NaN  2022-06-08  2021-12-20   \n",
       "95742     NaN                            NaN  2022-02-26  2021-04-12   \n",
       "95743       0                   Non concerné  2020-12-01  2020-10-13   \n",
       "95744       9                    indéterminé  2022-09-16  2022-09-16   \n",
       "95745     NaN                            NaN  2022-11-09  2022-07-25   \n",
       "\n",
       "              maj numuai  \n",
       "0      2020-02-04    NaN  \n",
       "1      2021-07-07    NaN  \n",
       "2      2018-01-12    NaN  \n",
       "3      2020-02-04    NaN  \n",
       "4      2020-06-30    NaN  \n",
       "...           ...    ...  \n",
       "95741  2022-07-19    NaN  \n",
       "95742  2022-07-20    NaN  \n",
       "95743  2022-10-06    NaN  \n",
       "95744  2022-10-06    NaN  \n",
       "95745  2022-11-10    NaN  \n",
       "\n",
       "[95746 rows x 31 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "structures = (raw_df\n",
    "    .iloc[:int(raw_df.index.size/2)]\n",
    ")\n",
    "\n",
    "structures"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "0c45bbd4",
   "metadata": {
    "gradient": {
     "editing": false,
     "id": "283be3bb",
     "kernelId": ""
    }
   },
   "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>nofinesset</th>\n",
       "      <th>coordxet</th>\n",
       "      <th>coordyet</th>\n",
       "      <th>sourcecoordet</th>\n",
       "      <th>datemaj</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>95746</th>\n",
       "      <td>020002978</td>\n",
       "      <td>714412.4</td>\n",
       "      <td>6946033.2</td>\n",
       "      <td>1,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,LAMBERT_93</td>\n",
       "      <td>2022-11-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95747</th>\n",
       "      <td>020012779</td>\n",
       "      <td>736596.2</td>\n",
       "      <td>6900631.2</td>\n",
       "      <td>1,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,LAMBERT_93</td>\n",
       "      <td>2022-11-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95748</th>\n",
       "      <td>030002208</td>\n",
       "      <td>705086.4</td>\n",
       "      <td>6591177.1</td>\n",
       "      <td>3,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,LAMBERT_93</td>\n",
       "      <td>2022-11-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95749</th>\n",
       "      <td>040000069</td>\n",
       "      <td>923147.4</td>\n",
       "      <td>6321804.0</td>\n",
       "      <td>3,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,LAMBERT_93</td>\n",
       "      <td>2022-11-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95750</th>\n",
       "      <td>040002313</td>\n",
       "      <td>950414.1</td>\n",
       "      <td>6331695.3</td>\n",
       "      <td>3,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,LAMBERT_93</td>\n",
       "      <td>2022-11-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191487</th>\n",
       "      <td>980501738</td>\n",
       "      <td>521486.0</td>\n",
       "      <td>8583077.0</td>\n",
       "      <td>1,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,UTM_S38</td>\n",
       "      <td>2022-11-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191488</th>\n",
       "      <td>980501779</td>\n",
       "      <td>524168.6</td>\n",
       "      <td>8588089.7</td>\n",
       "      <td>2,ATLASANTE,84,IGN,BD_ADRESSE,V2.2,UTM_S38</td>\n",
       "      <td>2022-11-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191489</th>\n",
       "      <td>980501878</td>\n",
       "      <td>514247.9</td>\n",
       "      <td>8582244.9</td>\n",
       "      <td>1,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,UTM_S38</td>\n",
       "      <td>2022-11-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191490</th>\n",
       "      <td>980502199</td>\n",
       "      <td>522250.7</td>\n",
       "      <td>8592077.7</td>\n",
       "      <td>3,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,UTM_S38</td>\n",
       "      <td>2022-11-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191491</th>\n",
       "      <td>980502439</td>\n",
       "      <td>520063.8</td>\n",
       "      <td>8580141.1</td>\n",
       "      <td>3,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,UTM_S38</td>\n",
       "      <td>2022-11-04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>95746 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       nofinesset  coordxet   coordyet  \\\n",
       "95746   020002978  714412.4  6946033.2   \n",
       "95747   020012779  736596.2  6900631.2   \n",
       "95748   030002208  705086.4  6591177.1   \n",
       "95749   040000069  923147.4  6321804.0   \n",
       "95750   040002313  950414.1  6331695.3   \n",
       "...           ...       ...        ...   \n",
       "191487  980501738  521486.0  8583077.0   \n",
       "191488  980501779  524168.6  8588089.7   \n",
       "191489  980501878  514247.9  8582244.9   \n",
       "191490  980502199  522250.7  8592077.7   \n",
       "191491  980502439  520063.8  8580141.1   \n",
       "\n",
       "                                         sourcecoordet     datemaj  \n",
       "95746   1,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,LAMBERT_93  2022-11-04  \n",
       "95747   1,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,LAMBERT_93  2022-11-04  \n",
       "95748   3,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,LAMBERT_93  2022-11-04  \n",
       "95749   3,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,LAMBERT_93  2022-11-04  \n",
       "95750   3,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,LAMBERT_93  2022-11-04  \n",
       "...                                                ...         ...  \n",
       "191487     1,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,UTM_S38  2022-11-04  \n",
       "191488      2,ATLASANTE,84,IGN,BD_ADRESSE,V2.2,UTM_S38  2022-11-04  \n",
       "191489     1,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,UTM_S38  2022-11-04  \n",
       "191490     3,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,UTM_S38  2022-11-04  \n",
       "191491     3,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,UTM_S38  2022-11-04  \n",
       "\n",
       "[95746 rows x 5 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geolocalisations = (raw_df\n",
    "    .iloc[int(raw_df.index.size/2):]\n",
    "    .drop(columns=raw_df.columns[5:])\n",
    "    .rename(columns=lambda x: geoloc_names[list(raw_df.columns).index(x)])\n",
    ")\n",
    "\n",
    "geolocalisations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "fec68b4a",
   "metadata": {
    "gradient": {
     "editing": false,
     "id": "b54e527e",
     "kernelId": ""
    }
   },
   "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>nofinesset</th>\n",
       "      <th>nofinessej</th>\n",
       "      <th>rs</th>\n",
       "      <th>rslongue</th>\n",
       "      <th>complrs</th>\n",
       "      <th>compldistrib</th>\n",
       "      <th>numvoie</th>\n",
       "      <th>typvoie</th>\n",
       "      <th>voie</th>\n",
       "      <th>compvoie</th>\n",
       "      <th>...</th>\n",
       "      <th>codesph</th>\n",
       "      <th>libsph</th>\n",
       "      <th>dateouv</th>\n",
       "      <th>dateautor</th>\n",
       "      <th>maj</th>\n",
       "      <th>numuai</th>\n",
       "      <th>coordxet</th>\n",
       "      <th>coordyet</th>\n",
       "      <th>sourcecoordet</th>\n",
       "      <th>datemaj</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>010000024</td>\n",
       "      <td>010780054</td>\n",
       "      <td>CH DE FLEYRIAT</td>\n",
       "      <td>CENTRE HOSPITALIER DE BOURG-EN-BRESSE FLEYRIAT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>900</td>\n",
       "      <td>RTE</td>\n",
       "      <td>DE PARIS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>Etablissement public de santé</td>\n",
       "      <td>1979-02-13</td>\n",
       "      <td>1979-02-13</td>\n",
       "      <td>2020-02-04</td>\n",
       "      <td>NaN</td>\n",
       "      <td>870215.7</td>\n",
       "      <td>6571590.5</td>\n",
       "      <td>1,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,LAMBERT_93</td>\n",
       "      <td>2022-11-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>010000032</td>\n",
       "      <td>010780062</td>\n",
       "      <td>CH BUGEY SUD</td>\n",
       "      <td>CENTRE HOSPITALIER BUGEY SUD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>700</td>\n",
       "      <td>AV</td>\n",
       "      <td>DE NARVIK</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>Etablissement public de santé</td>\n",
       "      <td>1901-01-01</td>\n",
       "      <td>1901-01-01</td>\n",
       "      <td>2021-07-07</td>\n",
       "      <td>NaN</td>\n",
       "      <td>908351.7</td>\n",
       "      <td>6520414.1</td>\n",
       "      <td>2,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,LAMBERT_93</td>\n",
       "      <td>2022-11-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>010000065</td>\n",
       "      <td>010780096</td>\n",
       "      <td>CH DE TREVOUX - MONTPENSIER</td>\n",
       "      <td>CENTRE HOSPITALIER DE TREVOUX - MONTPENSIER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14</td>\n",
       "      <td>R</td>\n",
       "      <td>DE L'HOPITAL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>Etablissement public de santé</td>\n",
       "      <td>1901-01-01</td>\n",
       "      <td>1901-01-01</td>\n",
       "      <td>2018-01-12</td>\n",
       "      <td>NaN</td>\n",
       "      <td>837272.3</td>\n",
       "      <td>6539470.4</td>\n",
       "      <td>2,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,LAMBERT_93</td>\n",
       "      <td>2022-11-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>010000081</td>\n",
       "      <td>010780112</td>\n",
       "      <td>CH DU PAYS DE GEX</td>\n",
       "      <td>CENTRE HOSPITALIER DU PAYS DE GEX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>160</td>\n",
       "      <td>R</td>\n",
       "      <td>MARC PANISSOD</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>Etablissement public de santé</td>\n",
       "      <td>1901-01-01</td>\n",
       "      <td>1901-01-01</td>\n",
       "      <td>2020-02-04</td>\n",
       "      <td>NaN</td>\n",
       "      <td>935201.9</td>\n",
       "      <td>6584824.4</td>\n",
       "      <td>1,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,LAMBERT_93</td>\n",
       "      <td>2022-11-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>010000099</td>\n",
       "      <td>010780120</td>\n",
       "      <td>CH DE MEXIMIEUX</td>\n",
       "      <td>CENTRE HOSPITALIER DE MEXIMIEUX</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13</td>\n",
       "      <td>AV</td>\n",
       "      <td>DU DOCTEUR BOYER</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>Etablissement public de santé</td>\n",
       "      <td>1945-01-01</td>\n",
       "      <td>1945-01-01</td>\n",
       "      <td>2020-06-30</td>\n",
       "      <td>NaN</td>\n",
       "      <td>870112.6</td>\n",
       "      <td>6536420.5</td>\n",
       "      <td>2,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,LAMBERT_93</td>\n",
       "      <td>2022-11-04</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>95741</th>\n",
       "      <td>980502389</td>\n",
       "      <td>590799730</td>\n",
       "      <td>SAMSAH TUTUBIYA - ALEFPA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>390</td>\n",
       "      <td>R</td>\n",
       "      <td>DES FRANGIPANIERS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-08</td>\n",
       "      <td>2021-12-20</td>\n",
       "      <td>2022-07-19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>521091.5</td>\n",
       "      <td>8572951.3</td>\n",
       "      <td>3,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,UTM_S38</td>\n",
       "      <td>2022-11-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95742</th>\n",
       "      <td>980502405</td>\n",
       "      <td>980500854</td>\n",
       "      <td>PLATEFORME SENSORIELLE - ADSM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>R</td>\n",
       "      <td>DE LA SIM</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-02-26</td>\n",
       "      <td>2021-04-12</td>\n",
       "      <td>2022-07-20</td>\n",
       "      <td>NaN</td>\n",
       "      <td>514540.0</td>\n",
       "      <td>8585984.0</td>\n",
       "      <td>2,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,UTM_S38</td>\n",
       "      <td>2022-11-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95743</th>\n",
       "      <td>980502439</td>\n",
       "      <td>980500557</td>\n",
       "      <td>SEPRODOM MAYOTTE</td>\n",
       "      <td>SEPRODOM MAYOTTE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CHE</td>\n",
       "      <td>MAVIKI</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>Non concerné</td>\n",
       "      <td>2020-12-01</td>\n",
       "      <td>2020-10-13</td>\n",
       "      <td>2022-10-06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>520063.8</td>\n",
       "      <td>8580141.1</td>\n",
       "      <td>3,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,UTM_S38</td>\n",
       "      <td>2022-11-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95744</th>\n",
       "      <td>980502454</td>\n",
       "      <td>980502447</td>\n",
       "      <td>LE CONFORT MEDICAL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100</td>\n",
       "      <td>RTE</td>\n",
       "      <td>DES BADAMIERS</td>\n",
       "      <td>C</td>\n",
       "      <td>...</td>\n",
       "      <td>9</td>\n",
       "      <td>indéterminé</td>\n",
       "      <td>2022-09-16</td>\n",
       "      <td>2022-09-16</td>\n",
       "      <td>2022-10-06</td>\n",
       "      <td>NaN</td>\n",
       "      <td>530514.6</td>\n",
       "      <td>8587199.1</td>\n",
       "      <td>2,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,UTM_S38</td>\n",
       "      <td>2022-11-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95745</th>\n",
       "      <td>980502470</td>\n",
       "      <td>980502462</td>\n",
       "      <td>PHARMACIE AL SHIFAA</td>\n",
       "      <td>PHARMACIE AL SHIFAA</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>54</td>\n",
       "      <td>R</td>\n",
       "      <td>Des Manguiers</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-11-09</td>\n",
       "      <td>2022-07-25</td>\n",
       "      <td>2022-11-10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>524730.8</td>\n",
       "      <td>8586737.5</td>\n",
       "      <td>2,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,UTM_S38</td>\n",
       "      <td>2022-11-04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>95746 rows × 35 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      nofinesset nofinessej                             rs  \\\n",
       "0      010000024  010780054                 CH DE FLEYRIAT   \n",
       "1      010000032  010780062                   CH BUGEY SUD   \n",
       "2      010000065  010780096    CH DE TREVOUX - MONTPENSIER   \n",
       "3      010000081  010780112              CH DU PAYS DE GEX   \n",
       "4      010000099  010780120                CH DE MEXIMIEUX   \n",
       "...          ...        ...                            ...   \n",
       "95741  980502389  590799730       SAMSAH TUTUBIYA - ALEFPA   \n",
       "95742  980502405  980500854  PLATEFORME SENSORIELLE - ADSM   \n",
       "95743  980502439  980500557               SEPRODOM MAYOTTE   \n",
       "95744  980502454  980502447             LE CONFORT MEDICAL   \n",
       "95745  980502470  980502462            PHARMACIE AL SHIFAA   \n",
       "\n",
       "                                             rslongue complrs compldistrib  \\\n",
       "0      CENTRE HOSPITALIER DE BOURG-EN-BRESSE FLEYRIAT     NaN          NaN   \n",
       "1                        CENTRE HOSPITALIER BUGEY SUD     NaN          NaN   \n",
       "2         CENTRE HOSPITALIER DE TREVOUX - MONTPENSIER     NaN          NaN   \n",
       "3                   CENTRE HOSPITALIER DU PAYS DE GEX     NaN          NaN   \n",
       "4                     CENTRE HOSPITALIER DE MEXIMIEUX     NaN          NaN   \n",
       "...                                               ...     ...          ...   \n",
       "95741                                             NaN     NaN          NaN   \n",
       "95742                                             NaN     NaN          NaN   \n",
       "95743                                SEPRODOM MAYOTTE     NaN          NaN   \n",
       "95744                                             NaN     NaN          NaN   \n",
       "95745                             PHARMACIE AL SHIFAA     NaN          NaN   \n",
       "\n",
       "      numvoie typvoie               voie compvoie  ... codesph  \\\n",
       "0         900     RTE           DE PARIS      NaN  ...       1   \n",
       "1         700      AV          DE NARVIK      NaN  ...       1   \n",
       "2          14       R       DE L'HOPITAL      NaN  ...       1   \n",
       "3         160       R      MARC PANISSOD      NaN  ...       1   \n",
       "4          13      AV   DU DOCTEUR BOYER      NaN  ...       1   \n",
       "...       ...     ...                ...      ...  ...     ...   \n",
       "95741     390       R  DES FRANGIPANIERS      NaN  ...     NaN   \n",
       "95742       4       R          DE LA SIM      NaN  ...     NaN   \n",
       "95743     NaN     CHE             MAVIKI      NaN  ...       0   \n",
       "95744     100     RTE      DES BADAMIERS        C  ...       9   \n",
       "95745      54       R      Des Manguiers      NaN  ...     NaN   \n",
       "\n",
       "                              libsph     dateouv   dateautor         maj  \\\n",
       "0      Etablissement public de santé  1979-02-13  1979-02-13  2020-02-04   \n",
       "1      Etablissement public de santé  1901-01-01  1901-01-01  2021-07-07   \n",
       "2      Etablissement public de santé  1901-01-01  1901-01-01  2018-01-12   \n",
       "3      Etablissement public de santé  1901-01-01  1901-01-01  2020-02-04   \n",
       "4      Etablissement public de santé  1945-01-01  1945-01-01  2020-06-30   \n",
       "...                              ...         ...         ...         ...   \n",
       "95741                            NaN  2022-06-08  2021-12-20  2022-07-19   \n",
       "95742                            NaN  2022-02-26  2021-04-12  2022-07-20   \n",
       "95743                   Non concerné  2020-12-01  2020-10-13  2022-10-06   \n",
       "95744                    indéterminé  2022-09-16  2022-09-16  2022-10-06   \n",
       "95745                            NaN  2022-11-09  2022-07-25  2022-11-10   \n",
       "\n",
       "      numuai  coordxet   coordyet  \\\n",
       "0        NaN  870215.7  6571590.5   \n",
       "1        NaN  908351.7  6520414.1   \n",
       "2        NaN  837272.3  6539470.4   \n",
       "3        NaN  935201.9  6584824.4   \n",
       "4        NaN  870112.6  6536420.5   \n",
       "...      ...       ...        ...   \n",
       "95741    NaN  521091.5  8572951.3   \n",
       "95742    NaN  514540.0  8585984.0   \n",
       "95743    NaN  520063.8  8580141.1   \n",
       "95744    NaN  530514.6  8587199.1   \n",
       "95745    NaN  524730.8  8586737.5   \n",
       "\n",
       "                                        sourcecoordet     datemaj  \n",
       "0      1,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,LAMBERT_93  2022-11-04  \n",
       "1      2,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,LAMBERT_93  2022-11-04  \n",
       "2      2,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,LAMBERT_93  2022-11-04  \n",
       "3      1,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,LAMBERT_93  2022-11-04  \n",
       "4      2,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,LAMBERT_93  2022-11-04  \n",
       "...                                               ...         ...  \n",
       "95741     3,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,UTM_S38  2022-11-04  \n",
       "95742     2,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,UTM_S38  2022-11-04  \n",
       "95743     3,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,UTM_S38  2022-11-04  \n",
       "95744     2,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,UTM_S38  2022-11-04  \n",
       "95745     2,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,UTM_S38  2022-11-04  \n",
       "\n",
       "[95746 rows x 35 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_df = (structures\n",
    "    .merge(geolocalisations, on=\"nofinesset\", how=\"left\")\n",
    ")\n",
    "\n",
    "clean_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "e9d7f2a3",
   "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>69461</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>nofinesset</th>\n",
       "      <td>750045676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>nofinessej</th>\n",
       "      <td>750721391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rs</th>\n",
       "      <td>SAVS CHAMPIONNET</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rslongue</th>\n",
       "      <td>SERVICE D ACCOMPAGNEMENT A LA VIE SOCIALE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>complrs</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>compldistrib</th>\n",
       "      <td>74-76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>numvoie</th>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>typvoie</th>\n",
       "      <td>R</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>voie</th>\n",
       "      <td>CHAMPIONNET</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>compvoie</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>lieuditbp</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>commune</th>\n",
       "      <td>118</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>departement</th>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>libdepartement</th>\n",
       "      <td>PARIS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ligneacheminement</th>\n",
       "      <td>75018 PARIS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>telephone</th>\n",
       "      <td>0142573307</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>telecopie</th>\n",
       "      <td>0142628739</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>categetab</th>\n",
       "      <td>446</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>libcategetab</th>\n",
       "      <td>Service d'Accompagnement à la Vie Sociale (S.A...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>categagretab</th>\n",
       "      <td>4305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>libcategagretab</th>\n",
       "      <td>Services de Maintien à Domicile pour Handicapés</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>siret</th>\n",
       "      <td>77567634900167</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>codeape</th>\n",
       "      <td>8810B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>codemft</th>\n",
       "      <td>08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>libmft</th>\n",
       "      <td>Président du Conseil Départemental</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>codesph</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>libsph</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dateouv</th>\n",
       "      <td>2006-10-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dateautor</th>\n",
       "      <td>2006-09-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>maj</th>\n",
       "      <td>2016-09-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>numuai</th>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>coordxet</th>\n",
       "      <td>651950.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>coordyet</th>\n",
       "      <td>6866408.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sourcecoordet</th>\n",
       "      <td>1,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,LAMBERT_93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>datemaj</th>\n",
       "      <td>2022-11-04</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                               69461\n",
       "nofinesset                                                 750045676\n",
       "nofinessej                                                 750721391\n",
       "rs                                                  SAVS CHAMPIONNET\n",
       "rslongue                   SERVICE D ACCOMPAGNEMENT A LA VIE SOCIALE\n",
       "complrs                                                          NaN\n",
       "compldistrib                                                   74-76\n",
       "numvoie                                                           74\n",
       "typvoie                                                            R\n",
       "voie                                                     CHAMPIONNET\n",
       "compvoie                                                         NaN\n",
       "lieuditbp                                                        NaN\n",
       "commune                                                          118\n",
       "departement                                                       75\n",
       "libdepartement                                                 PARIS\n",
       "ligneacheminement                                        75018 PARIS\n",
       "telephone                                                 0142573307\n",
       "telecopie                                                 0142628739\n",
       "categetab                                                        446\n",
       "libcategetab       Service d'Accompagnement à la Vie Sociale (S.A...\n",
       "categagretab                                                    4305\n",
       "libcategagretab      Services de Maintien à Domicile pour Handicapés\n",
       "siret                                                 77567634900167\n",
       "codeape                                                        8810B\n",
       "codemft                                                           08\n",
       "libmft                            Président du Conseil Départemental\n",
       "codesph                                                          NaN\n",
       "libsph                                                           NaN\n",
       "dateouv                                                   2006-10-25\n",
       "dateautor                                                 2006-09-12\n",
       "maj                                                       2016-09-07\n",
       "numuai                                                           NaN\n",
       "coordxet                                                    651950.4\n",
       "coordyet                                                   6866408.3\n",
       "sourcecoordet         1,ATLASANTE,100,IGN,BD_ADRESSE,V2.2,LAMBERT_93\n",
       "datemaj                                                   2022-11-04"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_df.sample().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "a4ab6a65",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        26010004500012\n",
       "1        26010003700068\n",
       "2        26010028400017\n",
       "3        26010010200011\n",
       "4        26010013600019\n",
       "              ...      \n",
       "95741    77562407502209\n",
       "95742               NaN\n",
       "95743               NaN\n",
       "95744               NaN\n",
       "95745               NaN\n",
       "Name: siret, Length: 95746, dtype: object"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_df[\"siret\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "546bf8dc",
   "metadata": {
    "gradient": {
     "editing": false,
     "id": "82306369-229c-418f-9138-d753e1b71ce4",
     "kernelId": ""
    }
   },
   "source": [
    "## Vérification de la qualité des données"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "49726305",
   "metadata": {
    "collapsed": false,
    "gradient": {
     "editing": false,
     "id": "64975e82-5f97-4bb4-b1d3-8aed85fa37cd",
     "kernelId": "",
     "source_hidden": false
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(95746,)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "intersection = pd.Series(np.intersect1d(structures.nofinesset.values, geolocalisations.nofinesset.values))\n",
    "\n",
    "intersection.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f0e851ae",
   "metadata": {
    "collapsed": false,
    "gradient": {
     "editing": false,
     "id": "07e3c1cb-7032-4d83-833c-0979d2592f3c",
     "kernelId": "",
     "source_hidden": false
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "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>nofinesset</th>\n",
       "      <th>nofinessej</th>\n",
       "      <th>rs</th>\n",
       "      <th>rslongue</th>\n",
       "      <th>complrs</th>\n",
       "      <th>compldistrib</th>\n",
       "      <th>numvoie</th>\n",
       "      <th>typvoie</th>\n",
       "      <th>voie</th>\n",
       "      <th>compvoie</th>\n",
       "      <th>...</th>\n",
       "      <th>siret</th>\n",
       "      <th>codeape</th>\n",
       "      <th>codemft</th>\n",
       "      <th>libmft</th>\n",
       "      <th>codesph</th>\n",
       "      <th>libsph</th>\n",
       "      <th>dateouv</th>\n",
       "      <th>dateautor</th>\n",
       "      <th>maj</th>\n",
       "      <th>numuai</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [nofinesset, nofinessej, rs, rslongue, complrs, compldistrib, numvoie, typvoie, voie, compvoie, lieuditbp, commune, departement, libdepartement, ligneacheminement, telephone, telecopie, categetab, libcategetab, categagretab, libcategagretab, siret, codeape, codemft, libmft, codesph, libsph, dateouv, dateautor, maj, numuai]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 31 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "only_structures = (structures\n",
    "    [ ~structures.nofinesset.isin(intersection) ]\n",
    ")\n",
    "\n",
    "only_structures"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "2ef0f1f1",
   "metadata": {
    "collapsed": false,
    "gradient": {
     "editing": false,
     "id": "cfb13e95-b622-4d89-be56-61397dc4370e",
     "kernelId": "",
     "source_hidden": false
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "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>nofinesset</th>\n",
       "      <th>coordxet</th>\n",
       "      <th>coordyet</th>\n",
       "      <th>sourcecoordet</th>\n",
       "      <th>datemaj</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [nofinesset, coordxet, coordyet, sourcecoordet, datemaj]\n",
       "Index: []"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "only_geolocalisations = (geolocalisations\n",
    "    [ ~geolocalisations.nofinesset.isin(intersection) ]\n",
    ")\n",
    "\n",
    "only_geolocalisations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "fcd6b341",
   "metadata": {
    "collapsed": false,
    "gradient": {
     "editing": false,
     "id": "92cd9e34-74c8-454c-96d8-3c628e7b94bd",
     "kernelId": "",
     "source_hidden": false
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "geolocalisations_missing = []"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1561e145",
   "metadata": {
    "gradient": {
     "editing": false,
     "id": "ff24d2da-6b7e-49ca-8ac9-cc1e90d32235",
     "kernelId": ""
    }
   },
   "source": [
    "## Export final"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "abb0d6e6",
   "metadata": {
    "collapsed": false,
    "gradient": {
     "editing": false,
     "id": "8f6f3c73-4c14-4e82-ac63-cdf9ab8e4b21",
     "kernelId": "",
     "source_hidden": false
    },
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "clean_df.to_csv('finess-clean.csv', encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "833bd767",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "jupytext": {
   "encoding": "# -*- coding: utf-8 -*-",
   "formats": "ipynb,py:hydrogen"
  },
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}