crime_to_db.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from region_dpts_France import REGIONS, DEPARTMENTS"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Insertion de la table Région et Département et Ville\n",
    "\n",
    "Les régions et les départements sont chargés à partir des dictionnaires d'un [module python](./region_dpts_France.py)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>lib_region</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Auvergne-Rhône-Alpes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Bourgogne-Franche-Comté</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Bretagne</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Centre-Val de Loire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Corse</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Grand Est</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Guadeloupe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Guyane</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Hauts-de-France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Île-de-France</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>La Réunion</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Martinique</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Mayotte</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Normandie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Nouvelle-Aquitaine</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>Occitanie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Pays de la Loire</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>Provence-Alpes-Côte d'Azur</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    lib_region\n",
       "1         Auvergne-Rhône-Alpes\n",
       "2      Bourgogne-Franche-Comté\n",
       "3                     Bretagne\n",
       "4          Centre-Val de Loire\n",
       "5                        Corse\n",
       "6                    Grand Est\n",
       "7                   Guadeloupe\n",
       "8                       Guyane\n",
       "9              Hauts-de-France\n",
       "10               Île-de-France\n",
       "11                  La Réunion\n",
       "12                  Martinique\n",
       "13                     Mayotte\n",
       "14                   Normandie\n",
       "15          Nouvelle-Aquitaine\n",
       "16                   Occitanie\n",
       "17            Pays de la Loire\n",
       "18  Provence-Alpes-Côte d'Azur"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Régions\n",
    "df_regions = pd.DataFrame(REGIONS.keys())\n",
    "df_regions = df_regions.rename(columns={0: 'lib_region'})\n",
    "df_regions.index += 1\n",
    "df_regions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "#Départements\n",
    "df_departement = pd.DataFrame(DEPARTMENTS.items(), columns=['id_dep', 'lib_dep'])\n",
    "region_for_dep_array = []\n",
    "for i, row in df_departement.iterrows():\n",
    "    region = [index+1 for index, [reg, dep_list] in enumerate(REGIONS.items()) if row['id_dep'] in dep_list]\n",
    "    region_for_dep_array.append(region[0] if len(region) else None)\n",
    "df_departement['id_region'] = region_for_dep_array"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Les villes sont retrouvées à partir d'un fichier excel listant les IRIS (Ilots Regroupés pour l'Information Statistique) des villes de frances.\n",
    "Il est donc nécessaire de faire quelques opérations afin de retrouver  notre tableau ville.\n",
    "\n",
    "Ce fichier sera aussi réutilisé pour calculer les revenus par ville.\n",
    "\n",
    "<span style=\"color: red\">Attention fichier lourd</span>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "#Ville\n",
    "df_IRIS = pd.read_excel('data/base-ic-evol-struct-pop-2018.xlsx', sheet_name=\"IRIS\", skiprows=5, usecols='A,C,E,F,CE') #CF = Pop Hors ménage (Sans domicile)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>id_ville</th>\n",
       "      <th>id_dep</th>\n",
       "      <th>lib_ville</th>\n",
       "      <th>nb_menages</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01001</td>\n",
       "      <td>01</td>\n",
       "      <td>L'Abergement-Clémenciat</td>\n",
       "      <td>771</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01002</td>\n",
       "      <td>01</td>\n",
       "      <td>L'Abergement-de-Varey</td>\n",
       "      <td>253</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01004</td>\n",
       "      <td>01</td>\n",
       "      <td>Ambérieu-en-Bugey</td>\n",
       "      <td>13697</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01005</td>\n",
       "      <td>01</td>\n",
       "      <td>Ambérieux-en-Dombes</td>\n",
       "      <td>1720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01006</td>\n",
       "      <td>01</td>\n",
       "      <td>Ambléon</td>\n",
       "      <td>112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34988</th>\n",
       "      <td>97420</td>\n",
       "      <td>974</td>\n",
       "      <td>Sainte-Suzanne</td>\n",
       "      <td>23614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34989</th>\n",
       "      <td>97421</td>\n",
       "      <td>974</td>\n",
       "      <td>Salazie</td>\n",
       "      <td>7221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34990</th>\n",
       "      <td>97422</td>\n",
       "      <td>974</td>\n",
       "      <td>Le Tampon</td>\n",
       "      <td>78973</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34991</th>\n",
       "      <td>97423</td>\n",
       "      <td>974</td>\n",
       "      <td>Les Trois-Bassins</td>\n",
       "      <td>7070</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34992</th>\n",
       "      <td>97424</td>\n",
       "      <td>974</td>\n",
       "      <td>Cilaos</td>\n",
       "      <td>5487</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>34993 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      id_ville id_dep                lib_ville  nb_menages\n",
       "0        01001     01  L'Abergement-Clémenciat         771\n",
       "1        01002     01    L'Abergement-de-Varey         253\n",
       "2        01004     01        Ambérieu-en-Bugey       13697\n",
       "3        01005     01      Ambérieux-en-Dombes        1720\n",
       "4        01006     01                  Ambléon         112\n",
       "...        ...    ...                      ...         ...\n",
       "34988    97420    974           Sainte-Suzanne       23614\n",
       "34989    97421    974                  Salazie        7221\n",
       "34990    97422    974                Le Tampon       78973\n",
       "34991    97423    974        Les Trois-Bassins        7070\n",
       "34992    97424    974                   Cilaos        5487\n",
       "\n",
       "[34993 rows x 4 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_IRIS_ville = df_IRIS.groupby(['COM', 'DEP', 'LIBCOM']).sum().reset_index().rename(columns={'COM': 'id_ville', 'DEP': 'id_dep','P18_PMEN': 'nb_menages', 'LIBCOM': 'lib_ville'})\n",
    "df_IRIS_ville['nb_menages'] = df_IRIS_ville['nb_menages'].astype('int64')\n",
    "df_IRIS_ville"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## Insertion de la table Revenus\n",
    "\n",
    "Pour la tables des revenus disponibles, on rencontre quelques difficultés. Premièrement, les données récupérées ne sont pas par ville mais par IRIS (Ilots Regroupés pour l'Information Statistique), ce qui nous oblige à faire quelques étapes intermédiaires.\n",
    "\n",
    "Le nombre de ménage par IRIS se situe dans [le fichier](./data/base-ic-evol-struct-pop-2018.xlsx) des villes précédements chargés. On effectue donc tout d'abord **une jointure** de ces deux bases de données en fonction du numéro IRIS.\n",
    "\n",
    "Deuxièmement, il faut faire attention aux données, certains IRIS ne sont pas répertoriés dans les revenues disponibles, donc on effectuere **un inner-join**.\n",
    "\n",
    "<span style=\"color: rgb(150,50,50)\">Les données des revenus concernant une ville ne sont donc pas complètes, on moyennera donc la ville avec les IRIS disponibles. On attribura un coefficient du nombre de ménages de l'IRIS sur le nombre de ménages totale des IRIS référenciés.</span>\n",
    "\n",
    "Commençont par le chargement du fichier des revenus et la jointure."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>id_dep</th>\n",
       "      <th>id_ville</th>\n",
       "      <th>lib_ville</th>\n",
       "      <th>nb_menages</th>\n",
       "      <th>taux_pauv</th>\n",
       "      <th>median</th>\n",
       "      <th>d1</th>\n",
       "      <th>d9</th>\n",
       "      <th>taux_activite</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IRIS</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>010040101</th>\n",
       "      <td>01</td>\n",
       "      <td>01004</td>\n",
       "      <td>Ambérieu-en-Bugey</td>\n",
       "      <td>1552.119901</td>\n",
       "      <td>20.1</td>\n",
       "      <td>19300.0</td>\n",
       "      <td>10340.0</td>\n",
       "      <td>31610.0</td>\n",
       "      <td>71.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>010040102</th>\n",
       "      <td>01</td>\n",
       "      <td>01004</td>\n",
       "      <td>Ambérieu-en-Bugey</td>\n",
       "      <td>3588.320330</td>\n",
       "      <td>25.7</td>\n",
       "      <td>17530.0</td>\n",
       "      <td>9970.0</td>\n",
       "      <td>29840.0</td>\n",
       "      <td>69.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>010040201</th>\n",
       "      <td>01</td>\n",
       "      <td>01004</td>\n",
       "      <td>Ambérieu-en-Bugey</td>\n",
       "      <td>4092.419670</td>\n",
       "      <td>19.1</td>\n",
       "      <td>19580.0</td>\n",
       "      <td>11000.0</td>\n",
       "      <td>33210.0</td>\n",
       "      <td>70.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>010040202</th>\n",
       "      <td>01</td>\n",
       "      <td>01004</td>\n",
       "      <td>Ambérieu-en-Bugey</td>\n",
       "      <td>4464.688022</td>\n",
       "      <td>8.9</td>\n",
       "      <td>24530.0</td>\n",
       "      <td>13600.0</td>\n",
       "      <td>40790.0</td>\n",
       "      <td>71.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>010330102</th>\n",
       "      <td>01</td>\n",
       "      <td>01033</td>\n",
       "      <td>Valserhône</td>\n",
       "      <td>2958.728809</td>\n",
       "      <td>18.6</td>\n",
       "      <td>19960.0</td>\n",
       "      <td>10230.0</td>\n",
       "      <td>39080.0</td>\n",
       "      <td>74.4</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>974221001</th>\n",
       "      <td>974</td>\n",
       "      <td>97422</td>\n",
       "      <td>Le Tampon</td>\n",
       "      <td>3196.457258</td>\n",
       "      <td>44.1</td>\n",
       "      <td>14100.0</td>\n",
       "      <td>7160.0</td>\n",
       "      <td>32410.0</td>\n",
       "      <td>74.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>974221101</th>\n",
       "      <td>974</td>\n",
       "      <td>97422</td>\n",
       "      <td>Le Tampon</td>\n",
       "      <td>2372.638529</td>\n",
       "      <td>43.3</td>\n",
       "      <td>14390.0</td>\n",
       "      <td>7900.0</td>\n",
       "      <td>31470.0</td>\n",
       "      <td>73.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>974221201</th>\n",
       "      <td>974</td>\n",
       "      <td>97422</td>\n",
       "      <td>Le Tampon</td>\n",
       "      <td>6924.584727</td>\n",
       "      <td>48.0</td>\n",
       "      <td>13410.0</td>\n",
       "      <td>7310.0</td>\n",
       "      <td>29090.0</td>\n",
       "      <td>66.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>974221202</th>\n",
       "      <td>974</td>\n",
       "      <td>97422</td>\n",
       "      <td>Le Tampon</td>\n",
       "      <td>3389.374593</td>\n",
       "      <td>45.2</td>\n",
       "      <td>13940.0</td>\n",
       "      <td>7520.0</td>\n",
       "      <td>28310.0</td>\n",
       "      <td>66.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>974221203</th>\n",
       "      <td>974</td>\n",
       "      <td>97422</td>\n",
       "      <td>Le Tampon</td>\n",
       "      <td>3563.194959</td>\n",
       "      <td>48.7</td>\n",
       "      <td>13210.0</td>\n",
       "      <td>7120.0</td>\n",
       "      <td>27720.0</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>12391 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id_dep id_ville          lib_ville   nb_menages  taux_pauv   median  \\\n",
       "IRIS                                                                            \n",
       "010040101     01    01004  Ambérieu-en-Bugey  1552.119901       20.1  19300.0   \n",
       "010040102     01    01004  Ambérieu-en-Bugey  3588.320330       25.7  17530.0   \n",
       "010040201     01    01004  Ambérieu-en-Bugey  4092.419670       19.1  19580.0   \n",
       "010040202     01    01004  Ambérieu-en-Bugey  4464.688022        8.9  24530.0   \n",
       "010330102     01    01033         Valserhône  2958.728809       18.6  19960.0   \n",
       "...          ...      ...                ...          ...        ...      ...   \n",
       "974221001    974    97422          Le Tampon  3196.457258       44.1  14100.0   \n",
       "974221101    974    97422          Le Tampon  2372.638529       43.3  14390.0   \n",
       "974221201    974    97422          Le Tampon  6924.584727       48.0  13410.0   \n",
       "974221202    974    97422          Le Tampon  3389.374593       45.2  13940.0   \n",
       "974221203    974    97422          Le Tampon  3563.194959       48.7  13210.0   \n",
       "\n",
       "                d1       d9  taux_activite  \n",
       "IRIS                                        \n",
       "010040101  10340.0  31610.0           71.3  \n",
       "010040102   9970.0  29840.0           69.5  \n",
       "010040201  11000.0  33210.0           70.9  \n",
       "010040202  13600.0  40790.0           71.4  \n",
       "010330102  10230.0  39080.0           74.4  \n",
       "...            ...      ...            ...  \n",
       "974221001   7160.0  32410.0           74.1  \n",
       "974221101   7900.0  31470.0           73.2  \n",
       "974221201   7310.0  29090.0           66.9  \n",
       "974221202   7520.0  28310.0           66.7  \n",
       "974221203   7120.0  27720.0           68.0  \n",
       "\n",
       "[12391 rows x 9 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_IRIS_disp = pd.read_excel('data/BASE_TD_FILO_DISP_IRIS_2018.xlsx', sheet_name=\"IRIS_DISP\", skiprows=5, usecols='A,E,J,G,Q,U')\n",
    "df_IRIS_indexed = df_IRIS.set_index('IRIS')\n",
    "df_IRIS_disp_indexed = df_IRIS_disp.set_index('IRIS')\n",
    "df_IRIS_merged_disp = pd.concat([df_IRIS_indexed, df_IRIS_disp_indexed], axis=1, join='inner')\n",
    "df_IRIS_merged_disp = df_IRIS_merged_disp.rename(columns={'DEP': 'id_dep', 'COM': 'id_ville', 'LIBCOM': 'lib_ville', 'P18_PMEN': 'nb_menages', 'DISP_TP6018' :'taux_pauv', 'DISP_D118': 'd1', 'DISP_MED18': 'median', 'DISP_D918': 'd9', 'DISP_PACT18': 'taux_activite'})\n",
    "df_IRIS_merged_disp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Ensuite on va vouloir connaitre le nombre de ménages total dont on connait les revenus pour chaque ville.\n",
    "\n",
    "<span style=\"color: red\">Il ne s'agit pas du nombres de ménages total par ville précédément calculé.</span>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>id_ville</th>\n",
       "      <th>nb_menages</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01004</td>\n",
       "      <td>13697.547924</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01033</td>\n",
       "      <td>16190.510062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01053</td>\n",
       "      <td>38929.004207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01173</td>\n",
       "      <td>12839.994636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01283</td>\n",
       "      <td>21998.620379</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>992</th>\n",
       "      <td>97415</td>\n",
       "      <td>102367.367153</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>993</th>\n",
       "      <td>97416</td>\n",
       "      <td>83187.802676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>994</th>\n",
       "      <td>97418</td>\n",
       "      <td>32925.697420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>97420</td>\n",
       "      <td>22871.955529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>97422</td>\n",
       "      <td>78899.840257</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>997 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    id_ville     nb_menages\n",
       "0      01004   13697.547924\n",
       "1      01033   16190.510062\n",
       "2      01053   38929.004207\n",
       "3      01173   12839.994636\n",
       "4      01283   21998.620379\n",
       "..       ...            ...\n",
       "992    97415  102367.367153\n",
       "993    97416   83187.802676\n",
       "994    97418   32925.697420\n",
       "995    97420   22871.955529\n",
       "996    97422   78899.840257\n",
       "\n",
       "[997 rows x 2 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_IRIS_ville_disp = df_IRIS_merged_disp.groupby(['id_dep', 'id_ville', 'lib_ville']).sum().reset_index()[['id_ville', 'nb_menages']]\n",
    "df_IRIS_ville_disp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Ce nombre total de ménages nous permet d'attribuer un coefficient à chaque IRIS en fonction de sa proportions par rapport aux nombre de ménages prise en compte de la ville."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>id_ville</th>\n",
       "      <th>nb_menages</th>\n",
       "      <th>coeff</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IRIS</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>010040101</th>\n",
       "      <td>01004</td>\n",
       "      <td>1552.119901</td>\n",
       "      <td>0.113314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>010040102</th>\n",
       "      <td>01004</td>\n",
       "      <td>3588.320330</td>\n",
       "      <td>0.261968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>010040201</th>\n",
       "      <td>01004</td>\n",
       "      <td>4092.419670</td>\n",
       "      <td>0.298770</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>010040202</th>\n",
       "      <td>01004</td>\n",
       "      <td>4464.688022</td>\n",
       "      <td>0.325948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>010330102</th>\n",
       "      <td>01033</td>\n",
       "      <td>2958.728809</td>\n",
       "      <td>0.182745</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>974221001</th>\n",
       "      <td>97422</td>\n",
       "      <td>3196.457258</td>\n",
       "      <td>0.040513</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>974221101</th>\n",
       "      <td>97422</td>\n",
       "      <td>2372.638529</td>\n",
       "      <td>0.030072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>974221201</th>\n",
       "      <td>97422</td>\n",
       "      <td>6924.584727</td>\n",
       "      <td>0.087764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>974221202</th>\n",
       "      <td>97422</td>\n",
       "      <td>3389.374593</td>\n",
       "      <td>0.042958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>974221203</th>\n",
       "      <td>97422</td>\n",
       "      <td>3563.194959</td>\n",
       "      <td>0.045161</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>12391 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id_ville   nb_menages     coeff\n",
       "IRIS                                     \n",
       "010040101    01004  1552.119901  0.113314\n",
       "010040102    01004  3588.320330  0.261968\n",
       "010040201    01004  4092.419670  0.298770\n",
       "010040202    01004  4464.688022  0.325948\n",
       "010330102    01033  2958.728809  0.182745\n",
       "...            ...          ...       ...\n",
       "974221001    97422  3196.457258  0.040513\n",
       "974221101    97422  2372.638529  0.030072\n",
       "974221201    97422  6924.584727  0.087764\n",
       "974221202    97422  3389.374593  0.042958\n",
       "974221203    97422  3563.194959  0.045161\n",
       "\n",
       "[12391 rows x 3 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "coeff = []\n",
    "for i, row in df_IRIS_merged_disp.iterrows():\n",
    "    total_menages = df_IRIS_ville_disp.loc[df_IRIS_ville_disp['id_ville'] == row['id_ville']]['nb_menages'].values[0]\n",
    "    coeff.append(row['nb_menages']/total_menages)\n",
    "\n",
    "df_IRIS_merged_disp['coeff'] = coeff\n",
    "df_IRIS_merged_disp[['id_ville', 'nb_menages', 'coeff']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Si on additionne les coefficients des IRIS pour chaque ville on a donc bien 1."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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></th>\n",
       "      <th></th>\n",
       "      <th>coeff</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id_dep</th>\n",
       "      <th>id_ville</th>\n",
       "      <th>lib_ville</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">01</th>\n",
       "      <th>01004</th>\n",
       "      <th>Ambérieu-en-Bugey</th>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01033</th>\n",
       "      <th>Valserhône</th>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01053</th>\n",
       "      <th>Bourg-en-Bresse</th>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01173</th>\n",
       "      <th>Gex</th>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01283</th>\n",
       "      <th>Oyonnax</th>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">974</th>\n",
       "      <th>97415</th>\n",
       "      <th>Saint-Paul</th>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97416</th>\n",
       "      <th>Saint-Pierre</th>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97418</th>\n",
       "      <th>Sainte-Marie</th>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97420</th>\n",
       "      <th>Sainte-Suzanne</th>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97422</th>\n",
       "      <th>Le Tampon</th>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>997 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   coeff\n",
       "id_dep id_ville lib_ville               \n",
       "01     01004    Ambérieu-en-Bugey    1.0\n",
       "       01033    Valserhône           1.0\n",
       "       01053    Bourg-en-Bresse      1.0\n",
       "       01173    Gex                  1.0\n",
       "       01283    Oyonnax              1.0\n",
       "...                                  ...\n",
       "974    97415    Saint-Paul           1.0\n",
       "       97416    Saint-Pierre         1.0\n",
       "       97418    Sainte-Marie         1.0\n",
       "       97420    Sainte-Suzanne       1.0\n",
       "       97422    Le Tampon            1.0\n",
       "\n",
       "[997 rows x 1 columns]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_IRIS_merged_disp.groupby(['id_dep', 'id_ville', 'lib_ville']).sum()[['coeff']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Il nous faut maintenant recalculer chaque variables des IRIS (taux de pauvreté, 1er décil, ...) en applicant son coefficient."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>IRIS</th>\n",
       "      <th>id_ville</th>\n",
       "      <th>median</th>\n",
       "      <th>d1</th>\n",
       "      <th>d9</th>\n",
       "      <th>taux_pauv</th>\n",
       "      <th>taux_activite</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>010040101</td>\n",
       "      <td>01004</td>\n",
       "      <td>2186.954502</td>\n",
       "      <td>1171.663707</td>\n",
       "      <td>3581.846208</td>\n",
       "      <td>2.277605</td>\n",
       "      <td>8.079267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>010040102</td>\n",
       "      <td>01004</td>\n",
       "      <td>4592.300442</td>\n",
       "      <td>2611.821757</td>\n",
       "      <td>7817.127506</td>\n",
       "      <td>6.732580</td>\n",
       "      <td>18.206782</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>010040201</td>\n",
       "      <td>01004</td>\n",
       "      <td>5849.921285</td>\n",
       "      <td>3286.472632</td>\n",
       "      <td>9922.159646</td>\n",
       "      <td>5.706512</td>\n",
       "      <td>21.182810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>010040202</td>\n",
       "      <td>01004</td>\n",
       "      <td>7995.503852</td>\n",
       "      <td>4432.892474</td>\n",
       "      <td>13295.417942</td>\n",
       "      <td>2.900937</td>\n",
       "      <td>23.272685</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>010330102</td>\n",
       "      <td>01033</td>\n",
       "      <td>3647.582862</td>\n",
       "      <td>1869.477589</td>\n",
       "      <td>7141.660233</td>\n",
       "      <td>3.399050</td>\n",
       "      <td>13.596201</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12386</th>\n",
       "      <td>974221001</td>\n",
       "      <td>97422</td>\n",
       "      <td>571.231161</td>\n",
       "      <td>290.071994</td>\n",
       "      <td>1313.021413</td>\n",
       "      <td>1.786617</td>\n",
       "      <td>3.002002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12387</th>\n",
       "      <td>974221101</td>\n",
       "      <td>97422</td>\n",
       "      <td>432.729247</td>\n",
       "      <td>237.565049</td>\n",
       "      <td>946.350896</td>\n",
       "      <td>1.302097</td>\n",
       "      <td>2.201236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12388</th>\n",
       "      <td>974221201</td>\n",
       "      <td>97422</td>\n",
       "      <td>1176.918494</td>\n",
       "      <td>641.556614</td>\n",
       "      <td>2553.061819</td>\n",
       "      <td>4.212684</td>\n",
       "      <td>5.871428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12389</th>\n",
       "      <td>974221202</td>\n",
       "      <td>97422</td>\n",
       "      <td>598.833682</td>\n",
       "      <td>323.043708</td>\n",
       "      <td>1216.139277</td>\n",
       "      <td>1.941699</td>\n",
       "      <td>2.865295</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12390</th>\n",
       "      <td>974221203</td>\n",
       "      <td>97422</td>\n",
       "      <td>596.576688</td>\n",
       "      <td>321.546254</td>\n",
       "      <td>1251.862665</td>\n",
       "      <td>2.199340</td>\n",
       "      <td>3.070947</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>12391 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            IRIS id_ville       median           d1            d9  taux_pauv  \\\n",
       "0      010040101    01004  2186.954502  1171.663707   3581.846208   2.277605   \n",
       "1      010040102    01004  4592.300442  2611.821757   7817.127506   6.732580   \n",
       "2      010040201    01004  5849.921285  3286.472632   9922.159646   5.706512   \n",
       "3      010040202    01004  7995.503852  4432.892474  13295.417942   2.900937   \n",
       "4      010330102    01033  3647.582862  1869.477589   7141.660233   3.399050   \n",
       "...          ...      ...          ...          ...           ...        ...   \n",
       "12386  974221001    97422   571.231161   290.071994   1313.021413   1.786617   \n",
       "12387  974221101    97422   432.729247   237.565049    946.350896   1.302097   \n",
       "12388  974221201    97422  1176.918494   641.556614   2553.061819   4.212684   \n",
       "12389  974221202    97422   598.833682   323.043708   1216.139277   1.941699   \n",
       "12390  974221203    97422   596.576688   321.546254   1251.862665   2.199340   \n",
       "\n",
       "       taux_activite  \n",
       "0           8.079267  \n",
       "1          18.206782  \n",
       "2          21.182810  \n",
       "3          23.272685  \n",
       "4          13.596201  \n",
       "...              ...  \n",
       "12386       3.002002  \n",
       "12387       2.201236  \n",
       "12388       5.871428  \n",
       "12389       2.865295  \n",
       "12390       3.070947  \n",
       "\n",
       "[12391 rows x 7 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Code pas optimal\n",
    "median_coeff = []\n",
    "d1_coeff = []\n",
    "d9_coeff = []\n",
    "taux_pauv_coeff = []\n",
    "taux_activite_coeff =[]\n",
    "for i, row in df_IRIS_merged_disp.iterrows():\n",
    "    coeff = row['coeff']\n",
    "    median_coeff.append(row['median']*coeff)\n",
    "    d1_coeff.append(row['d1']*coeff)\n",
    "    d9_coeff.append(row['d9']*coeff)\n",
    "    taux_pauv_coeff.append(row['taux_pauv']*coeff)\n",
    "    taux_activite_coeff.append(row['taux_activite']*coeff)\n",
    "\n",
    "df_IRIS_merged_coeff = df_IRIS_merged_disp.reset_index()[['IRIS', 'id_ville']]\n",
    "df_IRIS_merged_coeff['median'] = median_coeff\n",
    "df_IRIS_merged_coeff['d1'] = d1_coeff\n",
    "df_IRIS_merged_coeff['d9'] = d9_coeff\n",
    "df_IRIS_merged_coeff['taux_pauv'] = taux_pauv_coeff\n",
    "df_IRIS_merged_coeff['taux_activite'] = taux_activite_coeff\n",
    "df_IRIS_merged_coeff"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "On additionne tout pour retrouver la table de revenu final."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>median</th>\n",
       "      <th>d1</th>\n",
       "      <th>d9</th>\n",
       "      <th>taux_pauv</th>\n",
       "      <th>taux_activite</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id_ville</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>01004</th>\n",
       "      <td>20624.680080</td>\n",
       "      <td>11502.850570</td>\n",
       "      <td>34616.551301</td>\n",
       "      <td>17.617634</td>\n",
       "      <td>70.741544</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01033</th>\n",
       "      <td>22472.030765</td>\n",
       "      <td>11523.115878</td>\n",
       "      <td>44501.137824</td>\n",
       "      <td>15.901412</td>\n",
       "      <td>78.155982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01053</th>\n",
       "      <td>19338.766058</td>\n",
       "      <td>10662.079385</td>\n",
       "      <td>34202.131656</td>\n",
       "      <td>20.866870</td>\n",
       "      <td>65.171422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01173</th>\n",
       "      <td>32645.352672</td>\n",
       "      <td>13215.815725</td>\n",
       "      <td>67975.112831</td>\n",
       "      <td>9.671756</td>\n",
       "      <td>99.743312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01283</th>\n",
       "      <td>17845.047600</td>\n",
       "      <td>9945.670268</td>\n",
       "      <td>30642.956689</td>\n",
       "      <td>26.402180</td>\n",
       "      <td>67.445883</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>97415</th>\n",
       "      <td>18142.652494</td>\n",
       "      <td>8609.337160</td>\n",
       "      <td>39417.616718</td>\n",
       "      <td>32.367365</td>\n",
       "      <td>78.287258</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97416</th>\n",
       "      <td>16076.537319</td>\n",
       "      <td>8303.118229</td>\n",
       "      <td>35765.373480</td>\n",
       "      <td>38.259666</td>\n",
       "      <td>74.321268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97418</th>\n",
       "      <td>17915.869743</td>\n",
       "      <td>9013.980175</td>\n",
       "      <td>37825.525791</td>\n",
       "      <td>31.908019</td>\n",
       "      <td>81.279353</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97420</th>\n",
       "      <td>16070.026538</td>\n",
       "      <td>8434.207229</td>\n",
       "      <td>34318.870472</td>\n",
       "      <td>37.356086</td>\n",
       "      <td>79.382509</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97422</th>\n",
       "      <td>15073.807627</td>\n",
       "      <td>8029.921440</td>\n",
       "      <td>33430.602944</td>\n",
       "      <td>40.266404</td>\n",
       "      <td>67.374040</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>997 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                median            d1            d9  taux_pauv  taux_activite\n",
       "id_ville                                                                    \n",
       "01004     20624.680080  11502.850570  34616.551301  17.617634      70.741544\n",
       "01033     22472.030765  11523.115878  44501.137824  15.901412      78.155982\n",
       "01053     19338.766058  10662.079385  34202.131656  20.866870      65.171422\n",
       "01173     32645.352672  13215.815725  67975.112831   9.671756      99.743312\n",
       "01283     17845.047600   9945.670268  30642.956689  26.402180      67.445883\n",
       "...                ...           ...           ...        ...            ...\n",
       "97415     18142.652494   8609.337160  39417.616718  32.367365      78.287258\n",
       "97416     16076.537319   8303.118229  35765.373480  38.259666      74.321268\n",
       "97418     17915.869743   9013.980175  37825.525791  31.908019      81.279353\n",
       "97420     16070.026538   8434.207229  34318.870472  37.356086      79.382509\n",
       "97422     15073.807627   8029.921440  33430.602944  40.266404      67.374040\n",
       "\n",
       "[997 rows x 5 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_revenu_final = df_IRIS_merged_coeff.groupby(['id_ville']).sum()\n",
    "df_revenu_final"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## Insertion de la table des crimes et des faits\n",
    "\n",
    "La table des faits est la somme des faits répertorié par les services de police et par les services de la gendarmeries. Ces informations sont dans deux feuille excel différentes.\n",
    "\n",
    "Chargement des données du excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "df_faits_gn_2020 = pd.read_excel('data/crimes-et-delits-enregistres-par-les-services-de-gendarmerie-et-de-police-depuis-2012.xlsx', sheet_name=\"Services GN 2020\", skiprows=0, header=None)\n",
    "df_faits_pn_2020 = pd.read_excel('data/crimes-et-delits-enregistres-par-les-services-de-gendarmerie-et-de-police-depuis-2012.xlsx', sheet_name=\"Services PN 2020\", skiprows=0, header=None)\n",
    "\n",
    "df_faits_gn_2019 = pd.read_excel('data/crimes-et-delits-enregistres-par-les-services-de-gendarmerie-et-de-police-depuis-2012.xlsx', sheet_name=\"Services GN 2019\", skiprows=0, header=None)\n",
    "df_faits_pn_2019 = pd.read_excel('data/crimes-et-delits-enregistres-par-les-services-de-gendarmerie-et-de-police-depuis-2012.xlsx', sheet_name=\"Services PN 2019\", skiprows=0, header=None)\n",
    "\n",
    "df_faits_gn_2018 = pd.read_excel('data/crimes-et-delits-enregistres-par-les-services-de-gendarmerie-et-de-police-depuis-2012.xlsx', sheet_name=\"Services GN 2018\", skiprows=0, header=None)\n",
    "df_faits_pn_2018 = pd.read_excel('data/crimes-et-delits-enregistres-par-les-services-de-gendarmerie-et-de-police-depuis-2012.xlsx', sheet_name=\"Services PN 2018\", skiprows=0, header=None)\n",
    "\n",
    "df_faits_gn_2017 = pd.read_excel('data/crimes-et-delits-enregistres-par-les-services-de-gendarmerie-et-de-police-depuis-2012.xlsx', sheet_name=\"Services GN 2017\", skiprows=0, header=None)\n",
    "df_faits_pn_2017 = pd.read_excel('data/crimes-et-delits-enregistres-par-les-services-de-gendarmerie-et-de-police-depuis-2012.xlsx', sheet_name=\"Services PN 2017\", skiprows=0, header=None)\n",
    "\n",
    "df_faits_gn_2016 = pd.read_excel('data/crimes-et-delits-enregistres-par-les-services-de-gendarmerie-et-de-police-depuis-2012.xlsx', sheet_name=\"Services GN 2016\", skiprows=0, header=None)\n",
    "df_faits_pn_2016 = pd.read_excel('data/crimes-et-delits-enregistres-par-les-services-de-gendarmerie-et-de-police-depuis-2012.xlsx', sheet_name=\"Services PN 2016\", skiprows=0, header=None)\n",
    "\n",
    "df_faits_gn_2015 = pd.read_excel('data/crimes-et-delits-enregistres-par-les-services-de-gendarmerie-et-de-police-depuis-2012.xlsx', sheet_name=\"Services GN 2015\", skiprows=0, header=None)\n",
    "df_faits_pn_2015 = pd.read_excel('data/crimes-et-delits-enregistres-par-les-services-de-gendarmerie-et-de-police-depuis-2012.xlsx', sheet_name=\"Services PN 2015\", skiprows=0, header=None)\n",
    "\n",
    "df_faits_gn_2014 = pd.read_excel('data/crimes-et-delits-enregistres-par-les-services-de-gendarmerie-et-de-police-depuis-2012.xlsx', sheet_name=\"Services GN 2014\", skiprows=0, header=None)\n",
    "df_faits_pn_2014 = pd.read_excel('data/crimes-et-delits-enregistres-par-les-services-de-gendarmerie-et-de-police-depuis-2012.xlsx', sheet_name=\"Services PN 2014\", skiprows=0, header=None)\n",
    "\n",
    "df_faits_gn_2013 = pd.read_excel('data/crimes-et-delits-enregistres-par-les-services-de-gendarmerie-et-de-police-depuis-2012.xlsx', sheet_name=\"Services GN 2013\", skiprows=0, header=None)\n",
    "df_faits_pn_2013 = pd.read_excel('data/crimes-et-delits-enregistres-par-les-services-de-gendarmerie-et-de-police-depuis-2012.xlsx', sheet_name=\"Services PN 2013\", skiprows=0, header=None)\n",
    "\n",
    "df_faits_gn_2012 = pd.read_excel('data/crimes-et-delits-enregistres-par-les-services-de-gendarmerie-et-de-police-depuis-2012.xlsx', sheet_name=\"Services GN 2012\", skiprows=0, header=None)\n",
    "df_faits_pn_2012 = pd.read_excel('data/crimes-et-delits-enregistres-par-les-services-de-gendarmerie-et-de-police-depuis-2012.xlsx', sheet_name=\"Services PN 2012\", skiprows=0, header=None)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "On retrouve d'abord la table Crime (id_crime / lib_crime)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>1</th>\n",
       "      <th>id_crime</th>\n",
       "      <th>lib_crime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>Règlements de compte entre malfaireurs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>Homicides pour voler et à l'occasion de vols</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3</td>\n",
       "      <td>Homicides pour d'autres motifs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4</td>\n",
       "      <td>Tentatives d'homicides pour voler et à l'occas...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5</td>\n",
       "      <td>Tentatives homicides pour d'autres motifs</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>103</td>\n",
       "      <td>Infractions à l'exercice d'une profession règl...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>105</th>\n",
       "      <td>104</td>\n",
       "      <td>Infractions au droit de l'urbanisme et de la c...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>105</td>\n",
       "      <td>Fraudes fiscales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>106</td>\n",
       "      <td>Autres délits économiques et financiers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>108</th>\n",
       "      <td>107</td>\n",
       "      <td>Autres délits</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>107 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "1   id_crime                                          lib_crime\n",
       "2          1             Règlements de compte entre malfaireurs\n",
       "3          2       Homicides pour voler et à l'occasion de vols\n",
       "4          3                     Homicides pour d'autres motifs\n",
       "5          4  Tentatives d'homicides pour voler et à l'occas...\n",
       "6          5          Tentatives homicides pour d'autres motifs\n",
       "..       ...                                                ...\n",
       "104      103  Infractions à l'exercice d'une profession règl...\n",
       "105      104  Infractions au droit de l'urbanisme et de la c...\n",
       "106      105                                   Fraudes fiscales\n",
       "107      106            Autres délits économiques et financiers\n",
       "108      107                                      Autres délits\n",
       "\n",
       "[107 rows x 2 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_crimes = df_faits_gn_2020.drop(0)\n",
    "df_crimes.columns = df_crimes.iloc[0]\n",
    "df_crimes = df_crimes[1:].rename(columns={\"Code index\" : 'id_crime', 'Libellé index \\\\ CGD': 'lib_crime'})[['id_crime', 'lib_crime']]\n",
    "df_crimes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Puis on retrouve la table des faits répertorié par la police"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>nb_faits</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id_dep</th>\n",
       "      <th>id_crime</th>\n",
       "      <th>annee</th>\n",
       "      <th>police</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>06</th>\n",
       "      <th>1</th>\n",
       "      <th>2012</th>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <th>1</th>\n",
       "      <th>2012</th>\n",
       "      <th>1</th>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <th>1</th>\n",
       "      <th>2012</th>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2A</th>\n",
       "      <th>1</th>\n",
       "      <th>2012</th>\n",
       "      <th>1</th>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <th>1</th>\n",
       "      <th>2012</th>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>973</th>\n",
       "      <th>107</th>\n",
       "      <th>2020</th>\n",
       "      <th>1</th>\n",
       "      <td>169</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>974</th>\n",
       "      <th>107</th>\n",
       "      <th>2020</th>\n",
       "      <th>1</th>\n",
       "      <td>480</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>976</th>\n",
       "      <th>107</th>\n",
       "      <th>2020</th>\n",
       "      <th>1</th>\n",
       "      <td>125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>987</th>\n",
       "      <th>107</th>\n",
       "      <th>2020</th>\n",
       "      <th>1</th>\n",
       "      <td>110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>988</th>\n",
       "      <th>107</th>\n",
       "      <th>2020</th>\n",
       "      <th>1</th>\n",
       "      <td>341</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>74770 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                              nb_faits\n",
       "id_dep id_crime annee police          \n",
       "06     1        2012  1              2\n",
       "13     1        2012  1             24\n",
       "25     1        2012  1              1\n",
       "2A     1        2012  1             13\n",
       "30     1        2012  1              1\n",
       "...                                ...\n",
       "973    107      2020  1            169\n",
       "974    107      2020  1            480\n",
       "976    107      2020  1            125\n",
       "987    107      2020  1            110\n",
       "988    107      2020  1            341\n",
       "\n",
       "[74770 rows x 1 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_faits_pn_clean_2020 = df_faits_pn_2020.drop(columns=[1]).set_index([0]).T.rename(columns={\"Année 2020 - services de police\": \"id_dep\", np.nan: \"Périmètres\"}).groupby(['id_dep']).sum().drop(columns=['Périmètres', 'Code index'])\n",
    "df_faits_pn_clean_2019 = df_faits_pn_2019.drop(columns=[1]).set_index([0]).T.rename(columns={\"Année 2019 - services de police\": \"id_dep\", np.nan: \"Périmètres\"}).groupby(['id_dep']).sum().drop(columns=['Périmètres', 'Code index'])\n",
    "df_faits_pn_clean_2018 = df_faits_pn_2018.drop(columns=[1]).set_index([0]).T.rename(columns={\"Année 2018 - services de police\": \"id_dep\", np.nan: \"Périmètres\"}).groupby(['id_dep']).sum().drop(columns=['Périmètres', 'Code index'])\n",
    "df_faits_pn_clean_2017 = df_faits_pn_2017.drop(columns=[1]).set_index([0]).T.rename(columns={\"Année 2017 - services de police\": \"id_dep\", np.nan: \"Périmètres\"}).groupby(['id_dep']).sum().drop(columns=['Périmètres', 'Code index'])\n",
    "df_faits_pn_clean_2016 = df_faits_pn_2016.drop(columns=[1]).set_index([0]).T.rename(columns={\"Année 2016 - services de police\": \"id_dep\", np.nan: \"Périmètres\"}).groupby(['id_dep']).sum().drop(columns=['Périmètres', 'Code index'])\n",
    "df_faits_pn_clean_2015 = df_faits_pn_2015.drop(columns=[1]).set_index([0]).T.rename(columns={\"Année 2015 - services de police\": \"id_dep\", np.nan: \"Périmètres\"}).groupby(['id_dep']).sum().drop(columns=['Périmètres', 'Code index'])\n",
    "df_faits_pn_clean_2014 = df_faits_pn_2014.drop(columns=[1]).set_index([0]).T.rename(columns={\"Année 2014 - services de police\": \"id_dep\", np.nan: \"Périmètres\"}).groupby(['id_dep']).sum().drop(columns=['Périmètres', 'Code index'])\n",
    "df_faits_pn_clean_2013 = df_faits_pn_2013.drop(columns=[1]).set_index([0]).T.rename(columns={\"Année 2013 - services de police\": \"id_dep\", np.nan: \"Périmètres\"}).groupby(['id_dep']).sum().drop(columns=['Périmètres', 'Code index'])\n",
    "df_faits_pn_clean_2012 = df_faits_pn_2012.drop(columns=[1]).set_index([0]).T.rename(columns={\"Année 2012 - services de police\": \"id_dep\", np.nan: \"Périmètres\"}).groupby(['id_dep']).sum().drop(columns=['Périmètres', 'Code index'])\n",
    "\n",
    "df_faits_pn_clean_annee =[df_faits_pn_clean_2012, df_faits_pn_clean_2013, df_faits_pn_clean_2014, df_faits_pn_clean_2015,df_faits_pn_clean_2016,df_faits_pn_clean_2017,df_faits_pn_clean_2018,df_faits_pn_clean_2019,df_faits_pn_clean_2020]\n",
    "\n",
    "df_dict_list_pn = {\"id_dep\": [], \"id_crime\": [], \"nb_faits\": [], \"annee\":[], \"police\":[]}\n",
    "\n",
    "annee_pn = 2012\n",
    "for df_faits_pn_clean in df_faits_pn_clean_annee :\n",
    "    for id_cirme, fait_dict in df_faits_pn_clean.to_dict().items():\n",
    "        for id_dep, nb_faits in fait_dict.items():\n",
    "            if nb_faits == 0: continue\n",
    "            df_dict_list_pn[\"id_dep\"].append(id_dep)\n",
    "            df_dict_list_pn[\"id_crime\"].append(id_cirme)\n",
    "            df_dict_list_pn[\"nb_faits\"].append(nb_faits)\n",
    "            df_dict_list_pn[\"annee\"].append(annee_pn)\n",
    "            df_dict_list_pn[\"police\"].append(1)\n",
    "    annee_pn=annee_pn+1\n",
    "df_faits_pn_final = pd.DataFrame.from_dict(df_dict_list_pn).set_index(['id_dep', 'id_crime','annee','police'])\n",
    "df_faits_pn_final"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Vient ensuite celle de la gendarmerie"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>nb_faits</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id_dep</th>\n",
       "      <th>id_crime</th>\n",
       "      <th>annee</th>\n",
       "      <th>police</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>04</th>\n",
       "      <th>1</th>\n",
       "      <th>2012</th>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>06</th>\n",
       "      <th>1</th>\n",
       "      <th>2012</th>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <th>1</th>\n",
       "      <th>2012</th>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <th>1</th>\n",
       "      <th>2012</th>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2B</th>\n",
       "      <th>1</th>\n",
       "      <th>2012</th>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>973</th>\n",
       "      <th>107</th>\n",
       "      <th>2020</th>\n",
       "      <th>0</th>\n",
       "      <td>658</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>974</th>\n",
       "      <th>107</th>\n",
       "      <th>2020</th>\n",
       "      <th>0</th>\n",
       "      <td>557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>978</th>\n",
       "      <th>107</th>\n",
       "      <th>2020</th>\n",
       "      <th>0</th>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>987</th>\n",
       "      <th>107</th>\n",
       "      <th>2020</th>\n",
       "      <th>0</th>\n",
       "      <td>397</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>988</th>\n",
       "      <th>107</th>\n",
       "      <th>2020</th>\n",
       "      <th>0</th>\n",
       "      <td>227</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>74743 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                              nb_faits\n",
       "id_dep id_crime annee police          \n",
       "04     1        2012  0              1\n",
       "06     1        2012  0              1\n",
       "13     1        2012  0              1\n",
       "14     1        2012  0              1\n",
       "2B     1        2012  0              5\n",
       "...                                ...\n",
       "973    107      2020  0            658\n",
       "974    107      2020  0            557\n",
       "978    107      2020  0             87\n",
       "987    107      2020  0            397\n",
       "988    107      2020  0            227\n",
       "\n",
       "[74743 rows x 1 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_faits_gn_clean_2020 = df_faits_gn_2020.drop(columns=[1]).set_index([0]).T.rename(columns={\"Année 2020 - compagnies de gendarmerie\": \"id_dep\"})\n",
    "df_faits_gn_clean_2020 = df_faits_gn_clean_2020.groupby(['id_dep']).sum().drop(columns=['Code index'])\n",
    "df_faits_gn_clean_2019 = df_faits_gn_2019.drop(columns=[1]).set_index([0]).T.rename(columns={\"Année 2019 - compagnies de gendarmerie\": \"id_dep\"})\n",
    "df_faits_gn_clean_2019 = df_faits_gn_clean_2019.groupby(['id_dep']).sum().drop(columns=['Code index'])\n",
    "df_faits_gn_clean_2018 = df_faits_gn_2018.drop(columns=[1]).set_index([0]).T.rename(columns={\"Année 2018 - compagnies de gendarmerie\": \"id_dep\"})\n",
    "df_faits_gn_clean_2018 = df_faits_gn_clean_2018.groupby(['id_dep']).sum().drop(columns=['Code index'])\n",
    "df_faits_gn_clean_2017 = df_faits_gn_2017.drop(columns=[1]).set_index([0]).T.rename(columns={\"Année 2017 - compagnies de gendarmerie\": \"id_dep\"})\n",
    "df_faits_gn_clean_2017 = df_faits_gn_clean_2017.groupby(['id_dep']).sum().drop(columns=['Code index'])\n",
    "df_faits_gn_clean_2016 = df_faits_gn_2016.drop(columns=[1]).set_index([0]).T.rename(columns={\"Année 2016 - compagnies de gendarmerie\": \"id_dep\"})\n",
    "df_faits_gn_clean_2016 = df_faits_gn_clean_2016.groupby(['id_dep']).sum().drop(columns=['Code index'])\n",
    "df_faits_gn_clean_2015 = df_faits_gn_2015.drop(columns=[1]).set_index([0]).T.rename(columns={\"Année 2015 - compagnies de gendarmerie\": \"id_dep\"})\n",
    "df_faits_gn_clean_2015 = df_faits_gn_clean_2015.groupby(['id_dep']).sum().drop(columns=['Code index'])\n",
    "df_faits_gn_clean_2014 = df_faits_gn_2014.drop(columns=[1]).set_index([0]).T.rename(columns={\"Année 2014 - compagnies de gendarmerie\": \"id_dep\"})\n",
    "df_faits_gn_clean_2014 = df_faits_gn_clean_2014.groupby(['id_dep']).sum().drop(columns=['Code index'])\n",
    "df_faits_gn_clean_2013 = df_faits_gn_2013.drop(columns=[1]).set_index([0]).T.rename(columns={\"Année 2013 - compagnies de gendarmerie\": \"id_dep\"})\n",
    "df_faits_gn_clean_2013 = df_faits_gn_clean_2013.groupby(['id_dep']).sum().drop(columns=['Code index'])\n",
    "df_faits_gn_clean_2012 = df_faits_gn_2012.drop(columns=[1]).set_index([0]).T.rename(columns={\"Année 2012 - compagnies de gendarmerie\": \"id_dep\"})\n",
    "df_faits_gn_clean_2012 = df_faits_gn_clean_2012.groupby(['id_dep']).sum().drop(columns=['Code index'])\n",
    "\n",
    "df_faits_gn_clean_annee =[df_faits_gn_clean_2012, df_faits_gn_clean_2013, df_faits_gn_clean_2014, df_faits_gn_clean_2015,df_faits_gn_clean_2016,df_faits_gn_clean_2017,df_faits_gn_clean_2018,df_faits_gn_clean_2019,df_faits_gn_clean_2020]\n",
    "\n",
    "df_dict_list_gn = {\"id_dep\": [], \"id_crime\": [], \"nb_faits\": [], \"annee\":[], \"police\":[]}\n",
    "annee_gn = 2012\n",
    "for df_faits_gn_clean in df_faits_gn_clean_annee :\n",
    "    for id_cirme, fait_dict in df_faits_gn_clean.to_dict().items():\n",
    "        for id_dep, nb_faits in fait_dict.items():\n",
    "            if nb_faits == 0: continue\n",
    "            df_dict_list_gn[\"id_dep\"].append(id_dep)\n",
    "            df_dict_list_gn[\"id_crime\"].append(id_cirme)\n",
    "            df_dict_list_gn[\"nb_faits\"].append(nb_faits)\n",
    "            df_dict_list_gn[\"annee\"].append(annee_gn)\n",
    "            df_dict_list_gn[\"police\"].append(0)\n",
    "    annee_gn = annee_gn+1\n",
    "df_faits_gn_final = pd.DataFrame.from_dict(df_dict_list_gn).set_index(['id_dep', 'id_crime','annee','police'])\n",
    "df_faits_gn_final"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Ensuite on somme ces deux tables pour retrouver notre table des faits final.\n",
    "\n",
    "<span style=\"color: rgb(150,50,50)\">Il faut bien préciser les index avant cette opération.</span>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>nb_faits</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id_dep</th>\n",
       "      <th>id_crime</th>\n",
       "      <th>annee</th>\n",
       "      <th>police</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">01</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">2</th>\n",
       "      <th>2016</th>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019</th>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">3</th>\n",
       "      <th>2012</th>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2013</th>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"5\" valign=\"top\">988</th>\n",
       "      <th rowspan=\"5\" valign=\"top\">107</th>\n",
       "      <th>2018</th>\n",
       "      <th>1</th>\n",
       "      <td>281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2019</th>\n",
       "      <th>0</th>\n",
       "      <td>264</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2020</th>\n",
       "      <th>0</th>\n",
       "      <td>227</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>341</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>149513 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                              nb_faits\n",
       "id_dep id_crime annee police          \n",
       "01     2        2016  0              1\n",
       "                2019  0              1\n",
       "       3        2012  0              2\n",
       "                2013  0              2\n",
       "                      1              1\n",
       "...                                ...\n",
       "988    107      2018  1            281\n",
       "                2019  0            264\n",
       "                      1            322\n",
       "                2020  0            227\n",
       "                      1            341\n",
       "\n",
       "[149513 rows x 1 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_faits_final = df_faits_gn_final.add(df_faits_pn_final, fill_value=0).astype('int32')\n",
    "df_faits_final"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## Insertion de la table des Catégories Socio-Pro et la table Répartion CSP\n",
    "\n",
    "On charge le fichier et on récupère tout d'abord la table CSP (id_categorie / lib_categorie)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "df_csp_load = pd.read_excel('data/CSP_par_region.xlsx', sheet_name=\"DEP\", skiprows=3, skipfooter=2).drop(96)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>id_categorie</th>\n",
       "      <th>lib_categorie</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Agriculteurs exploitants</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Artisans, Commerçants, Chefs d'entreprises</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Cadres, Professions intellectuelles supérieures</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Professions intermédiaires</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Employés</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Ouvriers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>Retraités</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>Sans activité professionnelle</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_categorie                                    lib_categorie\n",
       "0             1                         Agriculteurs exploitants\n",
       "1             2       Artisans, Commerçants, Chefs d'entreprises\n",
       "2             3  Cadres, Professions intellectuelles supérieures\n",
       "3             4                       Professions intermédiaires\n",
       "4             5                                         Employés\n",
       "5             6                                         Ouvriers\n",
       "6             7                                        Retraités\n",
       "7             8                    Sans activité professionnelle"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_csp_full = df_csp_load.rename(columns={\n",
    "    'Unnamed: 0': 'id_dep',\n",
    "    'Unnamed: 1': 'lib_dep',\n",
    "    'Part des agriculteurs exploitants (en %)': 'Agriculteurs exploitants',\n",
    "    'Part des artisans, commerçants, chefs d\\'entreprises (en %)': 'Artisans, Commerçants, Chefs d\\'entreprises',\n",
    "    'Part des cadres, professions intellectuelles supérieures (en %)': 'Cadres, Professions intellectuelles supérieures',\n",
    "    'Part des professions intermédiaires (en %)': 'Professions intermédiaires',\n",
    "    'Part des employés (en %)': 'Employés',\n",
    "    'Part des ouvriers (en %)': 'Ouvriers',\n",
    "    'Part des retraités (en %)': 'Retraités',\n",
    "    'Part des autres personnes sans activité professionnelle (en %)': 'Sans activité professionnelle'\n",
    "})\n",
    "\n",
    "df_csp_lib = pd.DataFrame(df_csp_full.columns)[2:].reset_index()[[0]].reset_index().rename(columns={'index': 'id_categorie', 0: 'lib_categorie'})\n",
    "df_csp_lib[['id_categorie']] += 1\n",
    "df_csp_lib"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "On a plus qu'à retrouver les répartitions.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>id_dep</th>\n",
       "      <th>id_categorie</th>\n",
       "      <th>taux</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01</td>\n",
       "      <td>1</td>\n",
       "      <td>0.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01</td>\n",
       "      <td>2</td>\n",
       "      <td>3.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01</td>\n",
       "      <td>3</td>\n",
       "      <td>9.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01</td>\n",
       "      <td>4</td>\n",
       "      <td>16.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01</td>\n",
       "      <td>5</td>\n",
       "      <td>16.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>795</th>\n",
       "      <td>974</td>\n",
       "      <td>4</td>\n",
       "      <td>12.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>796</th>\n",
       "      <td>974</td>\n",
       "      <td>5</td>\n",
       "      <td>20.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>797</th>\n",
       "      <td>974</td>\n",
       "      <td>6</td>\n",
       "      <td>13.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>798</th>\n",
       "      <td>974</td>\n",
       "      <td>7</td>\n",
       "      <td>14.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>799</th>\n",
       "      <td>974</td>\n",
       "      <td>8</td>\n",
       "      <td>30.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>800 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    id_dep  id_categorie  taux\n",
       "0       01             1   0.6\n",
       "1       01             2   3.9\n",
       "2       01             3   9.1\n",
       "3       01             4  16.2\n",
       "4       01             5  16.4\n",
       "..     ...           ...   ...\n",
       "795    974             4  12.2\n",
       "796    974             5  20.3\n",
       "797    974             6  13.1\n",
       "798    974             7  14.9\n",
       "799    974             8  30.1\n",
       "\n",
       "[800 rows x 3 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "id_categorie_list =[]\n",
    "id_dep_list = []\n",
    "taux_list = []\n",
    "for i, row in df_csp_full.iterrows():\n",
    "    row_cat = row.drop(['id_dep', 'lib_dep'])\n",
    "    for cat, taux in row_cat.items():\n",
    "        id_categorie_list.append(df_csp_lib.loc[df_csp_lib['lib_categorie'] == cat].values[0, 0])\n",
    "        id_dep_list.append(row.id_dep)\n",
    "        taux_list.append(taux)\n",
    "\n",
    "df_csp_reparties = pd.DataFrame.from_dict({'id_dep': id_dep_list, 'id_categorie': id_categorie_list, 'taux': taux_list})\n",
    "df_csp_reparties"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## Insertion de la table chômage"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Certains des noms des départements diffère un petit peu (accents, ...) de ceux que l'on a dans notre table département. On normalise donc pour comparer.\n",
    "\n",
    "On a aussi une erreur sur 'Val-de-Marne'."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "import unicodedata\n",
    "\n",
    "def normalize_str(str):\n",
    "    return unicodedata.normalize('NFKD' ,str).encode('ASCII', 'ignore').decode('utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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>id_dep</th>\n",
       "      <th>taux</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>02</td>\n",
       "      <td>11.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>03</td>\n",
       "      <td>8.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>04</td>\n",
       "      <td>9.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>05</td>\n",
       "      <td>7.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>95</td>\n",
       "      <td>8.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>971</td>\n",
       "      <td>17.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>972</td>\n",
       "      <td>12.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>973</td>\n",
       "      <td>16.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>974</td>\n",
       "      <td>17.3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_dep  taux\n",
       "0      01   6.0\n",
       "1      02  11.1\n",
       "2      03   8.6\n",
       "3      04   9.4\n",
       "4      05   7.5\n",
       "..    ...   ...\n",
       "95     95   8.5\n",
       "96    971  17.4\n",
       "97    972  12.4\n",
       "98    973  16.1\n",
       "99    974  17.3\n",
       "\n",
       "[100 rows x 2 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_chomage = pd.read_excel(\"data/ECRT2021-F12.xlsx\", sheet_name=\"Figure 2b\", skiprows=2, skipfooter=3, header=None)\n",
    "df_chomage = df_chomage.rename(columns={0: 'lib_dep', 1: 'taux'}).replace({'Va-de-Marne': 'Val-de-Marne'})\n",
    "\n",
    "df_departement_normalized = df_departement.copy()\n",
    "df_departement_normalized['lib_dep'] = df_departement_normalized['lib_dep'].apply(lambda s: normalize_str(s))\n",
    "\n",
    "id_chomage_dep_list = []\n",
    "\n",
    "for i, row in df_chomage.iterrows():\n",
    "    id_chomage_dep_list.append(df_departement_normalized.loc[df_departement_normalized['lib_dep'] == normalize_str(row['lib_dep'])].values[0,0])\n",
    "\n",
    "df_chomage['id_dep'] = id_chomage_dep_list\n",
    "df_chomage = df_chomage[['id_dep', 'taux']]\n",
    "df_chomage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>id_dep</th>\n",
       "      <th>evolution_taux</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01</td>\n",
       "      <td>-1.4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>02</td>\n",
       "      <td>-2.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>03</td>\n",
       "      <td>-2.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>04</td>\n",
       "      <td>-2.3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>05</td>\n",
       "      <td>-2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>95</td>\n",
       "      <td>-1.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>971</td>\n",
       "      <td>-6.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>972</td>\n",
       "      <td>-5.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>973</td>\n",
       "      <td>-5.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>974</td>\n",
       "      <td>-6.2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_dep  evolution_taux\n",
       "0      01            -1.4\n",
       "1      02            -2.9\n",
       "2      03            -2.1\n",
       "3      04            -2.3\n",
       "4      05            -2.0\n",
       "..    ...             ...\n",
       "95     95            -1.9\n",
       "96    971            -6.2\n",
       "97    972            -5.5\n",
       "98    973            -5.8\n",
       "99    974            -6.2\n",
       "\n",
       "[100 rows x 2 columns]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_taux_chomage = pd.read_excel(\"data/ECRT2021-F12.xlsx\", sheet_name=\"Figure 5b\", skiprows=2, skipfooter=3, header=None)\n",
    "df_taux_chomage = df_taux_chomage.rename(columns={0: 'lib_dep', 1: 'evolution_taux'}).replace({'Va-de-Marne': 'Val-de-Marne'})\n",
    "\n",
    "\n",
    "id_chomage_dep_list = []\n",
    "\n",
    "for i, row in df_taux_chomage.iterrows():\n",
    "    id_chomage_dep_list.append(df_departement_normalized.loc[df_departement_normalized['lib_dep'] == normalize_str(row['lib_dep'])].values[0,0])\n",
    "\n",
    "df_taux_chomage['id_dep'] = id_chomage_dep_list\n",
    "df_taux_chomage = df_taux_chomage[['id_dep', 'evolution_taux']]\n",
    "df_taux_chomage"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## Insertion dans la BDD\n",
    "\n",
    "Maintenant que toutes les dataframes sont présentes, il nous reste à les insérer dans la BDD, ajouter les clés primaire et étrangères.\n",
    "\n",
    "\n",
    "```\n",
    "database = 'crime'\n",
    "user = 'root'\n",
    "mdp = ''\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "import sqlalchemy\n",
    "\n",
    "sqlEngine = sqlalchemy.create_engine('mysql+pymysql://root:@127.0.0.1/crimes', pool_recycle=3600)\n",
    "con = sqlEngine.connect()\n",
    "\n",
    "if_exists = 'replace'\n",
    "\n",
    "sql_drops = ['csp_repartition', 'fait', 'revenu','chomage', 'ville', 'departement']\n",
    "\n",
    "try:\n",
    "    if if_exists == if_exists:\n",
    "        for i in sql_drops:\n",
    "            try:\n",
    "                con.execute('DROP TABLE `%s`;' % i)\n",
    "            except Exception as ex:\n",
    "                pass\n",
    "\n",
    "    #Région\n",
    "    df_regions.to_sql(\"region\", con, if_exists=if_exists, index_label='id_region')\n",
    "    con.execute('ALTER TABLE `region` ADD PRIMARY KEY (`id_region`);')\n",
    "    #Département\n",
    "    df_departement.to_sql(\"departement\", con, if_exists=if_exists, index=False, dtype={'id_dep': sqlalchemy.NVARCHAR(length=3), 'id_region': sqlalchemy.BIGINT})\n",
    "    con.execute('ALTER TABLE `departement` ADD PRIMARY KEY (`id_dep`);')\n",
    "    con.execute('ALTER TABLE `departement` ADD FOREIGN KEY (`id_region`) REFERENCES region(`id_region`);')\n",
    "    #Ville\n",
    "    df_IRIS_ville.to_sql(\"ville\", con, if_exists=if_exists, index=False, dtype={'id_ville': sqlalchemy.NVARCHAR(length=5), 'id_dep': sqlalchemy.NVARCHAR(length=3)})\n",
    "    con.execute('ALTER TABLE `ville` ADD PRIMARY KEY (`id_ville`);')\n",
    "    con.execute('ALTER TABLE `ville` ADD FOREIGN KEY (`id_dep`) REFERENCES departement(`id_dep`);')\n",
    "    #Revenus\n",
    "    df_revenu_final.to_sql('revenu', con, if_exists=if_exists, dtype={'id_ville': sqlalchemy.NVARCHAR(length=5)})\n",
    "    con.execute('ALTER TABLE `revenu` ADD PRIMARY KEY (`id_ville`);')\n",
    "    con.execute('ALTER TABLE `revenu` ADD FOREIGN KEY (`id_ville`) REFERENCES ville(`id_ville`);')\n",
    "    #Crime\n",
    "    df_crimes.to_sql('crime', con, if_exists=if_exists, index=False, dtype={'id_dep': sqlalchemy.NVARCHAR(length=3)})\n",
    "    con.execute('ALTER TABLE `crime` ADD PRIMARY KEY (`id_crime`);')\n",
    "    #Faits\n",
    "    df_faits_final.to_sql('fait', con, if_exists=if_exists, dtype={'id_dep': sqlalchemy.NVARCHAR(length=3)})\n",
    "    con.execute('ALTER TABLE `fait` ADD PRIMARY KEY (`id_dep`, `id_crime`,`annee`,`police`);')\n",
    "    con.execute('ALTER TABLE `fait` ADD FOREIGN KEY (`id_dep`) REFERENCES departement(`id_dep`);')\n",
    "    con.execute('ALTER TABLE `fait` ADD FOREIGN KEY (`id_crime`) REFERENCES crime(`id_crime`);')\n",
    "    #Catégorie Socio-Pro List\n",
    "    df_csp_lib.to_sql('categorie_sp', con, if_exists=if_exists, index=False)\n",
    "    con.execute('ALTER TABLE `categorie_sp` ADD PRIMARY KEY (`id_categorie`);')\n",
    "    #Catégorie Socio-Pro Répartition\n",
    "    df_csp_reparties.to_sql('csp_repartition', con, if_exists=if_exists, index=False, dtype={'id_dep': sqlalchemy.NVARCHAR(length=3)})\n",
    "    con.execute('ALTER TABLE `csp_repartition` ADD PRIMARY KEY (`id_dep`, `id_categorie`);')\n",
    "    con.execute('ALTER TABLE `csp_repartition` ADD FOREIGN KEY (`id_dep`) REFERENCES departement(`id_dep`);')\n",
    "    con.execute('ALTER TABLE `csp_repartition` ADD FOREIGN KEY (`id_categorie`) REFERENCES categorie_sp(`id_categorie`);')\n",
    "    #Chômage\n",
    "    df_chomage.to_sql('chomage', con, if_exists=if_exists, index=False, dtype={'id_dep': sqlalchemy.NVARCHAR(length=3)})\n",
    "    con.execute('ALTER TABLE `chomage` ADD PRIMARY KEY (`id_dep`);')\n",
    "    con.execute('ALTER TABLE `chomage` ADD FOREIGN KEY (`id_dep`) REFERENCES departement(`id_dep`);')\n",
    "    #Evolution taux de chomage\n",
    "    df_taux_chomage.to_sql('evolution_chomage', con, if_exists=if_exists, index=False, dtype={'id_dep': sqlalchemy.NVARCHAR(length=3)})\n",
    "    con.execute('ALTER TABLE `evolution_chomage` ADD PRIMARY KEY (`id_dep`);')\n",
    "    con.execute('ALTER TABLE `evolution_chomage` ADD FOREIGN KEY (`id_dep`) REFERENCES departement(`id_dep`);')\n",
    "except ValueError as vx:\n",
    "    print(vx)\n",
    "except Exception as ex:\n",
    "    print(ex)\n",
    "\n",
    "con.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "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.9.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}