PROFILS_SOCIAUX_2019_SECTEURS_COLLEGES_LILLE.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Les secteurs de collèges de la métropole de Lille sont-ils assez mixtes ? \n",
    "Les Indices de position sociale (IPS) moyens des collèges, tels qu'ils sont communiqués par l'Education nationale, ne permettent pas de mesurer le niveau de mixité socio-économique des élèves. Est-il possible de mesurer cette diversité, de manière indirecte, en observant les zones de recrutement des élèves des collèges publics ? Et les secteurs définis par le Conseil départemental permettent-ils d'atteindre cette mixité ?\n",
    "\n",
    "**SOURCES**\n",
    "- Carte scolaire des collèges publics du Nord (Département du Nord): https://services.lenord.fr/trouver-mon-college-de-secteur / https://geonord-lenord.opendata.arcgis.com/pages/telechargement \n",
    "- Données par Iris du recensement de la population de 2019 (INSEE): \n",
    " - \"Logements ordinaires\" (permet d'isoler les foyers avec enfants de 6 à 15 ans) : https://www.insee.fr/fr/statistiques/6544344?sommaire=6456104 \n",
    "- Population (données CSP sur l'ensemble des actifs): https://www.insee.fr/fr/statistiques/6543200#consulter\n",
    "   \n",
    "*Traitements : Denis Vannier (Le Plan Studio)*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import geopandas as gpd\n",
    "from math import pi\n",
    "import numpy as np\n",
    "import fiona"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 117 entries, 213 to 6798\n",
      "Data columns (total 10 columns):\n",
      " #   Column               Non-Null Count  Dtype  \n",
      "---  ------               --------------  -----  \n",
      " 0   codeuai              117 non-null    object \n",
      " 1   nom_etablissement    117 non-null    object \n",
      " 2   code_departement     117 non-null    object \n",
      " 3   code_commune         117 non-null    object \n",
      " 4   nom_commune          117 non-null    object \n",
      " 5   IPS                  117 non-null    float64\n",
      " 6   statut_public_prive  117 non-null    object \n",
      " 7   effectif             117 non-null    int64  \n",
      " 8   longitude            117 non-null    float64\n",
      " 9   latitude             117 non-null    float64\n",
      "dtypes: float64(3), int64(1), object(6)\n",
      "memory usage: 10.1+ KB\n"
     ]
    }
   ],
   "source": [
    "# On récupère la moyenne des indices de position sociale (IPS) de chaque collège de la métropole de Lille (MEL)\n",
    "\n",
    "# Codes Insee des communes de la MEL :\n",
    "mel=[\"59279\",\"59013\",\"59512\",\"59332\",\"59017\",\"59602\",\"59247\",\"59005\",\"59507\",\"59056\",\"59643\",\"59195\",\"59598\",\"59508\",\"59585\",\"59410\",\"59523\",\"59650\",\"59426\",\"59163\",\"59599\",\"59421\",\"59009\",\"59044\",\"59051\",\"59522\",\"59339\",\"59367\",\"59660\",\"59299\",\"59146\",\"59477\",\"59275\",\"59368\",\"59220\",\"59350\",\"59386\",\"59527\",\"59656\",\"59560\",\"59636\",\"59256\",\"59106\",\"59343\",\"59646\",\"59090\",\"59346\",\"59352\",\"59098\",\"59356\",\"59611\",\"59566\",\"59286\",\"59457\",\"59173\",\"59278\",\"59553\",\"59437\",\"59328\",\"59648\",\"59482\",\"59152\",\"59360\",\"59316\",\"59193\",\"59524\",\"59670\",\"59208\",\"59487\",\"59143\",\"59250\",\"59658\",\"59088\",\"59128\",\"59470\",\"59252\",\"59653\",\"59201\",\"59196\",\"59317\",\"59133\",\"59052\",\"59011\",\"59281\",\"59388\",\"59303\",\"59257\",\"59202\",\"59550\",\"59025\",\"59320\",\"59458\",\"59609\",\"59378\",\"59371\"]\n",
    "# Base de données nationale des IPS : \n",
    "ips=pd.read_csv('../ips_colleges_2021_france.csv')\n",
    "ips=ips[ips['code_commune'].isin(mel)]\n",
    "ips=ips[['codeuai','nom_etablissement','code_departement','code_commune','nom_commune','IPS','statut_public_prive','effectif','longitude','latitude']]\n",
    "ips=ips[ips['code_departement']=='059']\n",
    "ips['longitude']=ips['longitude'].round(5)\n",
    "ips['latitude']=ips['latitude'].round(5)\n",
    "#ips.to_csv('IPS_COLLEGES_MEL.csv')\n",
    "ips.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sélection des données du recensement 2019 par Iris : "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Traitement des données du fichier détail RP019 logements : \n",
    "- On ne conserve que les Iris de la MEL (la variable 'COMMUNE' contient l'un des codes Insee des commune de la MEL)\n",
    "- On ne retient que les logements dans lesquels vivent des enfants scolarisés (variable 'INPSM' supérieure ou égale à 1)\n",
    "- Parmi ces logements, on filtre ceux hébergeant au moins un enfant de 6 à 15 ans en 2019, en âge d'être scolarisés en collège ('INP15M' et 'INP11M').\n",
    "- Puis on conserve les variables suivante : Diplôme le plus élevé obtenu par la personne de référence du ménage ('DIPLM'), Situation quant à l'immigration de la personne de référence du ménage ('IMMIM'), Nombre de personnes actives ayant un emploi du ménage ('INPOM'), Type d'activité de la personne de référence du ménage ('TACTM')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/denisvannier/opt/anaconda3/envs/DATAXPLORE/lib/python3.9/site-packages/IPython/core/interactiveshell.py:3251: DtypeWarning: Columns (2,64) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 47906 entries, 2852909 to 3638592\n",
      "Data columns (total 13 columns):\n",
      " #   Column      Non-Null Count  Dtype \n",
      "---  ------      --------------  ----- \n",
      " 0   COMMUNE     47906 non-null  int64 \n",
      " 1   IRIS        47906 non-null  object\n",
      " 2   INPSM       47906 non-null  object\n",
      " 3   INP15M      47906 non-null  object\n",
      " 4   INP5M       47906 non-null  object\n",
      " 5   DIPLM       47906 non-null  object\n",
      " 6   IMMIM       47906 non-null  object\n",
      " 7   INPOM       47906 non-null  object\n",
      " 8   TACTM       47906 non-null  object\n",
      " 9   STAT_CONJM  47906 non-null  object\n",
      " 10  EMPLM       47906 non-null  object\n",
      " 11  HLML        47906 non-null  object\n",
      " 12  SEXEM       47906 non-null  object\n",
      "dtypes: int64(1), object(12)\n",
      "memory usage: 5.1+ MB\n"
     ]
    }
   ],
   "source": [
    "# Import du fichier détail \"logement\" du RP2019 :\n",
    "iris_log=pd.read_csv('RP2019_LOGEMTZB_csv/FD_LOGEMTZB_2019.csv', sep=';')[['COMMUNE','IRIS','INPSM','INP15M','INP5M','DIPLM','IMMIM','INPOM','TACTM','STAT_CONJM','EMPLM','HLML','SEXEM']]\n",
    "\n",
    "# filtrage des logements situés dans une commune de la MEL : \n",
    "mel=[\"59279\",\"59013\",\"59512\",\"59332\",\"59017\",\"59602\",\"59247\",\"59005\",\"59507\",\"59056\",\"59643\",\"59195\",\"59598\",\"59508\",\"59585\",\"59410\",\"59523\",\"59650\",\"59426\",\"59163\",\"59599\",\"59421\",\"59009\",\"59044\",\"59051\",\"59522\",\"59339\",\"59367\",\"59660\",\"59299\",\"59146\",\"59477\",\"59275\",\"59368\",\"59220\",\"59350\",\"59386\",\"59527\",\"59656\",\"59560\",\"59636\",\"59256\",\"59106\",\"59343\",\"59646\",\"59090\",\"59346\",\"59352\",\"59098\",\"59356\",\"59611\",\"59566\",\"59286\",\"59457\",\"59173\",\"59278\",\"59553\",\"59437\",\"59328\",\"59648\",\"59482\",\"59152\",\"59360\",\"59316\",\"59193\",\"59524\",\"59670\",\"59208\",\"59487\",\"59143\",\"59250\",\"59658\",\"59088\",\"59128\",\"59470\",\"59252\",\"59653\",\"59201\",\"59196\",\"59317\",\"59133\",\"59052\",\"59011\",\"59281\",\"59388\",\"59303\",\"59257\",\"59202\",\"59550\",\"59025\",\"59320\",\"59458\",\"59609\",\"59378\",\"59371\"]\n",
    "iris_log_LA=iris_log[iris_log['COMMUNE'].astype('str').isin(mel)]\n",
    "\n",
    "# filtrage des logements avec enfants scolarisés :\n",
    "iris_log_LA1=iris_log_LA[iris_log_LA['INPSM']!='Y']\n",
    "iris_log_LA1=iris_log_LA1[iris_log_LA1['INPSM'].astype('int64')>=1]\n",
    "\n",
    "# filtrage des logements avec enfants de 11 ans et moins :\n",
    "iris_log_LA2=iris_log_LA1[iris_log_LA1['INP15M']!='Y']\n",
    "iris_log_LA2=iris_log_LA2[iris_log_LA2['INP15M'].astype('int64')>=1]\n",
    "\n",
    "# filtrage des logements avec enfants de 6 à 15 ans :\n",
    "iris_log_LA2=iris_log_LA2[(iris_log_LA2['INP15M'].astype('int64')-iris_log_LA2['INP5M'].astype('int64'))>=1]\n",
    "iris_log_LA2.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'geopandas.geodataframe.GeoDataFrame'>\n",
      "Int64Index: 526 entries, 0 to 525\n",
      "Columns: 101 entries, INSEE_COM to NB_FOYERS_COLLEGIENS\n",
      "dtypes: float64(90), geometry(1), int64(1), object(9)\n",
      "memory usage: 419.2+ KB\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/denisvannier/opt/anaconda3/envs/DATAXPLORE/lib/python3.9/site-packages/IPython/core/interactiveshell.py:3251: DtypeWarning: Columns (0,1,4) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  exec(code_obj, self.user_global_ns, self.user_ns)\n",
      "/Users/denisvannier/opt/anaconda3/envs/DATAXPLORE/lib/python3.9/site-packages/geopandas/geodataframe.py:1351: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  super().__setitem__(key, value)\n"
     ]
    }
   ],
   "source": [
    "\n",
    "###### Création de nouvelles variables pour préparer la fusion des données de logements par Iris :\n",
    "\n",
    "# Niveau de diplôme de la personne de référence du ménage :\n",
    "nodip=[\"01\",\"02\",\"03\"]\n",
    "bep=[\"11\",\"12\"]\n",
    "cap_bac=[\"13\",\"14\"]\n",
    "pro_bts=[\"15\",\"16\",\"17\"]\n",
    "master_doc=[\"18\",\"19\"]\n",
    "iris_log_LA2.loc[iris_log_LA2[\"DIPLM\"].astype('str').isin(nodip), \"DIPLOM_AUCUN\"] = '1'\n",
    "iris_log_LA2.loc[iris_log_LA2[\"DIPLM\"].astype('str').isin(bep), \"DIPLOM_BREVET\"] = '1'\n",
    "iris_log_LA2.loc[iris_log_LA2[\"DIPLM\"].astype('str').isin(cap_bac), \"DIPLOM_CAP_BAC\"] = '1'\n",
    "iris_log_LA2.loc[iris_log_LA2[\"DIPLM\"].astype('str').isin(pro_bts), \"DIPLOM_BACPRO_BTS_LIC\"] = '1'\n",
    "iris_log_LA2.loc[iris_log_LA2[\"DIPLM\"].astype('str').isin(master_doc), \"DIPLOM_MASTER_DOC\"] = '1'\n",
    "\n",
    "# Nombre d'actifs du logement ayant un emploi :\n",
    "iris_log_LA2.loc[iris_log_LA2[\"INPOM\"]==\"0\", \"ACTIF_LOG_AUCUN\"] = '1'  \n",
    "iris_log_LA2.loc[iris_log_LA2[\"INPOM\"]==\"1\", \"ACTIF_LOG_UN\"] = '1'\n",
    "iris_log_LA2.loc[iris_log_LA2[\"INPOM\"]==\"2\", \"ACTIF_LOG_DEUX\"] = '1'  \n",
    "\n",
    "#Statut conjugal de la personne de référence du ménage\n",
    "monoparent=['4','5','6']\n",
    "iris_log_LA2.loc[iris_log_LA2[\"STAT_CONJM\"].astype('str').isin(monoparent), \"MONOPARENT\"] = '1'\n",
    "\n",
    "# Condition d'emploi de la personne de référence du ménage:\n",
    "iris_log_LA2.loc[iris_log_LA2[\"EMPLM\"]==\"16\", \"EMPLOICDI\"] = '1'  \n",
    "\n",
    "# Appartenance du logement à un organisme HLM :\n",
    "iris_log_LA2.loc[iris_log_LA2[\"HLML\"]==\"1\", \"LOGSOCIAL\"] = '1'\n",
    "\n",
    "# Type d'activité de la personne de référence du ménage :\n",
    "iris_log_LA2.loc[iris_log_LA2[\"TACTM\"]==\"11\", \"ACTIV_ACTIFS\"] = '1'\n",
    "iris_log_LA2.loc[iris_log_LA2[\"TACTM\"]==\"12\", \"ACTIV_CHOMEURS\"] = '1'\n",
    "iris_log_LA2.loc[iris_log_LA2[\"TACTM\"]==\"22\", \"ACTIV_ETUDIANT_STAGE\"] = '1'\n",
    "iris_log_LA2.loc[iris_log_LA2[\"TACTM\"]==\"24\", \"ACTIV_AU_FOYER\"] = '1'\n",
    "\n",
    "#Situation quant à l'immigration (personne de référence du ménage) :\n",
    "iris_log_LA2.loc[iris_log_LA2[\"IMMIM\"]==\"1\", \"IMMIGRES\"] = '1'\n",
    "\n",
    "# conversion des nouvelles variables en format numérique :\n",
    "iris_log_LA2[\"MONOPARENT\"]=iris_log_LA2[\"MONOPARENT\"].fillna(0).astype('int64')\n",
    "iris_log_LA2[\"EMPLOICDI\"]=iris_log_LA2[\"EMPLOICDI\"].fillna(0).astype('int64')\n",
    "iris_log_LA2[\"LOGSOCIAL\"]=iris_log_LA2[\"LOGSOCIAL\"].fillna(0).astype('int64')\n",
    "iris_log_LA2[\"DIPLOM_AUCUN\"]=iris_log_LA2[\"DIPLOM_AUCUN\"].fillna(0).astype('int64')\n",
    "iris_log_LA2[\"DIPLOM_CAP_BAC\"]=iris_log_LA2[\"DIPLOM_CAP_BAC\"].fillna(0).astype('int64')\n",
    "iris_log_LA2[\"DIPLOM_BREVET\"]=iris_log_LA2[\"DIPLOM_BREVET\"].fillna(0).astype('int64')\n",
    "iris_log_LA2[\"DIPLOM_BACPRO_BTS_LIC\"]=iris_log_LA2[\"DIPLOM_BACPRO_BTS_LIC\"].fillna(0).astype('int64')\n",
    "iris_log_LA2[\"DIPLOM_MASTER_DOC\"]=iris_log_LA2[\"DIPLOM_MASTER_DOC\"].fillna(0).astype('int64')\n",
    "iris_log_LA2[\"ACTIF_LOG_AUCUN\"]=iris_log_LA2[\"ACTIF_LOG_AUCUN\"].fillna(0).astype('int64')\n",
    "iris_log_LA2[\"ACTIF_LOG_UN\"]=iris_log_LA2[\"ACTIF_LOG_UN\"].fillna(0).astype('int64')\n",
    "iris_log_LA2[\"ACTIF_LOG_DEUX\"]=iris_log_LA2[\"ACTIF_LOG_DEUX\"].fillna(0).astype('int64')\n",
    "iris_log_LA2[\"ACTIV_ACTIFS\"]=iris_log_LA2[\"ACTIV_ACTIFS\"].fillna(0).astype('int64')\n",
    "iris_log_LA2[\"ACTIV_CHOMEURS\"]=iris_log_LA2[\"ACTIV_CHOMEURS\"].fillna(0).astype('int64')\n",
    "iris_log_LA2[\"ACTIV_ETUDIANT_STAGE\"]=iris_log_LA2[\"ACTIV_ETUDIANT_STAGE\"].fillna(0).astype('int64')\n",
    "iris_log_LA2[\"ACTIV_AU_FOYER\"]=iris_log_LA2[\"ACTIV_AU_FOYER\"].fillna(0).astype('int64')\n",
    "iris_log_LA2[\"IMMIGRES\"]=iris_log_LA2[\"IMMIGRES\"].fillna(0).astype('int64')\n",
    "\n",
    "# Conversion du code iris des communes composées d'un seul iris (\"ZZZZZZZZZ\"):\n",
    "iris_log_LA2.loc[iris_log_LA2[\"IRIS\"]==\"ZZZZZZZZZ\", \"IRIS\"] = iris_log_LA2[\"COMMUNE\"].astype('str')+'0000'\n",
    "\n",
    "# Création d'un nouveau dataframe qui synthétise les données logements par iris :\n",
    "\n",
    "iris_log_LA2[\"INP15M\"]=iris_log_LA2[\"INP15M\"].astype(\"int64\")\n",
    "iris_log_LA2[\"INP5M\"]=iris_log_LA2[\"INP5M\"].astype(\"int64\")\n",
    "iris_log_LA2['NB_COLLEGIENS']=iris_log_LA2['INP15M']-iris_log_LA2['INP5M']\n",
    "iris_log_LA2['NB_FOYERS_COLLEGIENS']=\"1\"\n",
    "iris_log_LA2['NB_FOYERS_COLLEGIENS']=iris_log_LA2['NB_FOYERS_COLLEGIENS'].astype('int64')\n",
    "profil_soc_iris=iris_log_LA2.groupby(by=['IRIS'],as_index=False).sum()\n",
    "profil_soc_iris=profil_soc_iris.drop({\"COMMUNE\",\"INP15M\",\"INP5M\"}, axis=1)\n",
    "profil_soc_iris=profil_soc_iris.rename({'IRIS':'CODE_IRIS'}, axis=1)\n",
    "\n",
    "# Import des limites des iris de la mel :\n",
    "iris_france=gpd.read_file('../CONTOURS-IRIS.shp')\n",
    "iris_mel=iris_france[iris_france['INSEE_COM'].astype('str').isin(mel)]\n",
    "\n",
    "# Import des données \"Population\" du RP2019, pour la mel :\n",
    "iris_pop=pd.read_csv('base-ic-evol-struct-pop-2019_csv/base-ic-evol-struct-pop-2019.CSV', sep=';')\n",
    "iris_pop=iris_pop[iris_pop['COM'].astype('str').isin(mel)]\n",
    "iris_pop=iris_pop.rename({'IRIS':'CODE_IRIS'}, axis=1)\n",
    "\n",
    "iris_pop['CODE_IRIS']=iris_pop['CODE_IRIS'].astype('str')\n",
    "iris_mel['CODE_IRIS']=iris_mel['CODE_IRIS'].astype('str')\n",
    "\n",
    "#Fusion des limites d'iris du mel et des données de population :\n",
    "iris_pop_mel=iris_mel.merge(iris_pop, how='left', on='CODE_IRIS')\n",
    "\n",
    "# Fusion avec les données de logements par Iris :\n",
    "iris_pop_mel[\"CODE_IRIS\"]=iris_pop_mel[\"CODE_IRIS\"].astype(\"str\")\n",
    "profil_soc_iris[\"CODE_IRIS\"]=profil_soc_iris[\"CODE_IRIS\"].astype(\"str\")\n",
    "iris_pop_log=iris_pop_mel.merge(profil_soc_iris, how='left', on='CODE_IRIS')\n",
    "iris_pop_log.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Projection des données démographiques sur le carroyage 200m de l'Insee"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "nom=\"mel\"\n",
    "\n",
    "# Import des centroïdes de carrés 200m avec le nombre d'individus :\n",
    "resid=gpd.read_file('POINTS_200M_POP2017_'+nom+'.geojson')\n",
    "\n",
    "# Ajout de l'Iris d'appartenance de chaque carré 200m :\n",
    "iris_ind=resid.sjoin(iris_pop_mel)\n",
    "iris_ind=iris_ind[['Idcar_200m','CODE_IRIS','Ind','geometry']]\n",
    "\n",
    "# Calcul du nombre d'individus par Iris :\n",
    "iris_ind_tot=iris_ind.groupby(by='CODE_IRIS').sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Agrégation des données démographiques par secteurs de collèges"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Découpe des iris avec données population et logements par secteurs de collèges"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/denisvannier/opt/anaconda3/envs/DATAXPLORE/lib/python3.9/site-packages/geopandas/geodataframe.py:2196: UserWarning: `keep_geom_type=True` in overlay resulted in 146 dropped geometries of different geometry types than df1 has. Set `keep_geom_type=False` to retain all geometries\n",
      "  return geopandas.overlay(\n"
     ]
    }
   ],
   "source": [
    "# Import\n",
    "coll=gpd.read_file('../Secteurs_colleges/Secteurs_colleges.shp')[['NUMETAB','CODE_INSEE','NOMCOM','geometry']]\n",
    "coll=coll.to_crs('EPSG:2154')\n",
    "\n",
    "# Intersections entre le découpage des secteurs de collèges et le découpage par Iris :\n",
    "coll_iris=coll.overlay(iris_pop_mel, how='intersection')\n",
    "coll_iris=coll_iris[['NUMETAB','CODE_IRIS','geometry']]\n",
    "coll_iris=coll_iris.merge(iris_ind_tot, how='left', on='CODE_IRIS')\n",
    "\n",
    "fusion=gpd.sjoin(coll_iris, resid)\n",
    "fusion['index']=fusion.index\n",
    "fusion['INDICE']=fusion['Ind_right']/fusion['Ind_left']\n",
    "\n",
    "fusion['CODE_IRIS']=fusion['CODE_IRIS'].astype('str')\n",
    "iris_pop_log=iris_pop_log.drop({'geometry'}, axis=1)\n",
    "iris_pop_log['CODE_IRIS']=iris_pop_log['CODE_IRIS'].astype('str')\n",
    "fusion_pop=fusion.merge(iris_pop_log, how='left', on='CODE_IRIS')\n",
    "\n",
    "\n",
    "# Chaque valeur de la base iris est alors multiplié par l'indice correspondant au carré 200m :\n",
    "fusion_pop['C19_POP15P']=(fusion_pop['C19_POP15P']*fusion_pop['INDICE'])\n",
    "fusion_pop['C19_POP15P_CS1']=(fusion_pop['C19_POP15P_CS1']*fusion_pop['INDICE'])\n",
    "fusion_pop['C19_POP15P_CS2']=(fusion_pop['C19_POP15P_CS2']*fusion_pop['INDICE'])\n",
    "fusion_pop['C19_POP15P_CS3']=(fusion_pop['C19_POP15P_CS3']*fusion_pop['INDICE'])\n",
    "fusion_pop['C19_POP15P_CS4']=(fusion_pop['C19_POP15P_CS4']*fusion_pop['INDICE'])\n",
    "fusion_pop['C19_POP15P_CS5']=(fusion_pop['C19_POP15P_CS5']*fusion_pop['INDICE'])\n",
    "fusion_pop['C19_POP15P_CS6']=(fusion_pop['C19_POP15P_CS6']*fusion_pop['INDICE'])\n",
    "fusion_pop['C19_POP15P_CS7']=(fusion_pop['C19_POP15P_CS7']*fusion_pop['INDICE'])\n",
    "fusion_pop['C19_POP15P_CS8']=(fusion_pop['C19_POP15P_CS8']*fusion_pop['INDICE'])\n",
    "fusion_pop['NB_FOYERS_COLLEGIENS']=(fusion_pop['NB_FOYERS_COLLEGIENS']*fusion_pop['INDICE'])\n",
    "fusion_pop['NB_COLLEGIENS']=(fusion_pop['NB_COLLEGIENS']*fusion_pop['INDICE'])\n",
    "fusion_pop['DIPLOM_AUCUN']=(fusion_pop['DIPLOM_AUCUN']*fusion_pop['INDICE'])\n",
    "fusion_pop['DIPLOM_BREVET']=(fusion_pop['DIPLOM_BREVET']*fusion_pop['INDICE'])\n",
    "fusion_pop['DIPLOM_CAP_BAC']=(fusion_pop['DIPLOM_CAP_BAC']*fusion_pop['INDICE'])\n",
    "fusion_pop['DIPLOM_BACPRO_BTS_LIC']=(fusion_pop['DIPLOM_BACPRO_BTS_LIC']*fusion_pop['INDICE'])\n",
    "fusion_pop['DIPLOM_MASTER_DOC']=(fusion_pop['DIPLOM_MASTER_DOC']*fusion_pop['INDICE'])\n",
    "fusion_pop['ACTIF_LOG_AUCUN']=(fusion_pop['ACTIF_LOG_AUCUN']*fusion_pop['INDICE'])\n",
    "fusion_pop['ACTIF_LOG_UN']=(fusion_pop['ACTIF_LOG_UN']*fusion_pop['INDICE'])\n",
    "fusion_pop['ACTIF_LOG_DEUX']=(fusion_pop['ACTIF_LOG_DEUX']*fusion_pop['INDICE'])\n",
    "fusion_pop['MONOPARENT']=(fusion_pop['MONOPARENT']*fusion_pop['INDICE'])\n",
    "fusion_pop['EMPLOICDI']=(fusion_pop['EMPLOICDI']*fusion_pop['INDICE'])\n",
    "fusion_pop['LOGSOCIAL']=(fusion_pop['LOGSOCIAL']*fusion_pop['INDICE'])\n",
    "fusion_pop['ACTIV_ACTIFS']=(fusion_pop['ACTIV_ACTIFS']*fusion_pop['INDICE'])\n",
    "fusion_pop['ACTIV_CHOMEURS']=(fusion_pop['ACTIV_CHOMEURS']*fusion_pop['INDICE'])\n",
    "fusion_pop['ACTIV_ETUDIANT_STAGE']=(fusion_pop['ACTIV_ETUDIANT_STAGE']*fusion_pop['INDICE'])\n",
    "fusion_pop['ACTIV_AU_FOYER']=(fusion_pop['ACTIV_AU_FOYER']*fusion_pop['INDICE'])\n",
    "fusion_pop['IMMIGRES']=(fusion_pop['IMMIGRES']*fusion_pop['INDICE'])\n",
    "\n",
    "\n",
    "fusion_pop=fusion_pop[['CODE_IRIS','NB_FOYERS_COLLEGIENS','NB_COLLEGIENS','C19_POP15P', 'C19_POP15P_CS1', 'C19_POP15P_CS2','C19_POP15P_CS3', 'C19_POP15P_CS4', 'C19_POP15P_CS5','C19_POP15P_CS6', 'C19_POP15P_CS7', 'C19_POP15P_CS8','DIPLOM_AUCUN', 'DIPLOM_BREVET', 'DIPLOM_CAP_BAC','DIPLOM_BACPRO_BTS_LIC', 'DIPLOM_MASTER_DOC', 'ACTIF_LOG_AUCUN','ACTIF_LOG_UN', 'ACTIF_LOG_DEUX', 'MONOPARENT', 'EMPLOICDI','LOGSOCIAL', 'ACTIV_ACTIFS', 'ACTIV_CHOMEURS','ACTIV_ETUDIANT_STAGE', 'ACTIV_AU_FOYER', 'IMMIGRES','NUMETAB', 'geometry']]\n",
    "fusion_pop=fusion_pop.rename({'NUMETAB': 'codeuai'}, axis=1)\n",
    "\n",
    "fusion_pop_ok=fusion_pop.groupby(by=['codeuai'],as_index=False).sum().round()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fusion du fichier des secteurs de collèges, avec les données RP2019 par secteur :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "coll=gpd.read_file('SECTEURS_COLLEGES_mel.geojson')[['codeuai','geometry']]\n",
    "coll=coll.dissolve(by='codeuai')\n",
    "\n",
    "# Export des limites des secteurs de collèges :\n",
    "coll=coll.to_crs('EPSG:4326')\n",
    "#coll.to_file('SECTEURS_COLLEGES_MEL.geojson')\n",
    "\n",
    "coll_def=coll.merge(fusion_pop_ok,how='left', on='codeuai')\n",
    "\n",
    "# Ajout des Indices de position sociale (IPS) 2021 et des effectifs des établissements :\n",
    "ips=pd.read_csv('IPS_COLLEGES_MEL.csv')[['codeuai','IPS','effectif','longitude','latitude', 'nom_commune']]\n",
    "coll_def=coll_def.merge(ips,how='left', on='codeuai')\n",
    "coll_def.sample(5)\n",
    "\n",
    "# Distinction des secteurs exclusifs (attribués à un seul collège) et des secteurs partagés entre deux collèges :\n",
    "coll_def.loc[coll_def[\"codeuai\"].str.len()>12, \"type_secteur\"] = 'partage'\n",
    "coll_def.loc[coll_def[\"codeuai\"].str.len()<12, \"type_secteur\"] = 'exclusif'\n",
    "\n",
    "# Calcul des pourcentages de chaque indicateur social :\n",
    "coll_def['C19_POP15ACT']=coll_def['C19_POP15P_CS1']+coll_def['C19_POP15P_CS2']+coll_def['C19_POP15P_CS3']+coll_def['C19_POP15P_CS4']+coll_def['C19_POP15P_CS5']+coll_def['C19_POP15P_CS6']+coll_def['C19_POP15P_CS7']+coll_def['C19_POP15P_CS8']\n",
    "coll_def['%_CS1']=((coll_def['C19_POP15P_CS1']/coll_def['C19_POP15ACT'])*100).round(1)\n",
    "coll_def['%_CS2']=((coll_def['C19_POP15P_CS2']/coll_def['C19_POP15ACT'])*100).round(1)\n",
    "coll_def['%_CS3']=((coll_def['C19_POP15P_CS3']/coll_def['C19_POP15ACT'])*100).round(1)\n",
    "coll_def['%_CS4']=((coll_def['C19_POP15P_CS4']/coll_def['C19_POP15ACT'])*100).round(1)\n",
    "coll_def['%_CS5']=((coll_def['C19_POP15P_CS5']/coll_def['C19_POP15ACT'])*100).round(1)\n",
    "coll_def['%_CS6']=((coll_def['C19_POP15P_CS6']/coll_def['C19_POP15ACT'])*100).round(1)\n",
    "coll_def['%_CS7']=((coll_def['C19_POP15P_CS7']/coll_def['C19_POP15ACT'])*100).round(1)\n",
    "coll_def['%_CS8']=((coll_def['C19_POP15P_CS8']/coll_def['C19_POP15ACT'])*100).round(1)\n",
    "coll_def['%_LOG0ACT']=(coll_def['ACTIF_LOG_AUCUN']/(coll_def['NB_FOYERS_COLLEGIENS'])*100).round(1)\n",
    "coll_def['%_LOG1ACT']=(coll_def['ACTIF_LOG_UN']/(coll_def['NB_FOYERS_COLLEGIENS'])*100).round(1)\n",
    "coll_def['%_LOG2ACT']=(coll_def['ACTIF_LOG_DEUX']/(coll_def['NB_FOYERS_COLLEGIENS'])*100).round(1)\n",
    "coll_def['%_DIP0']=(coll_def['DIPLOM_AUCUN']/(coll_def['NB_FOYERS_COLLEGIENS'])*100).round(1)\n",
    "coll_def['%_DIPBRE']=(coll_def['DIPLOM_BREVET']/(coll_def['NB_FOYERS_COLLEGIENS'])*100).round(1)\n",
    "coll_def['%_DIPCAP']=(coll_def['DIPLOM_CAP_BAC']/(coll_def['NB_FOYERS_COLLEGIENS'])*100).round(1)\n",
    "coll_def['%_DIPBAC']=(coll_def['DIPLOM_BACPRO_BTS_LIC']/(coll_def['NB_FOYERS_COLLEGIENS'])*100).round(1)\n",
    "coll_def['%_DIPMAS']=(coll_def['DIPLOM_MASTER_DOC']/(coll_def['NB_FOYERS_COLLEGIENS'])*100).round(1)\n",
    "\n",
    "coll_def['%_MONOPARENT']=(coll_def['MONOPARENT']/(coll_def['NB_FOYERS_COLLEGIENS'])*100).round(1)\n",
    "coll_def['%_EMPLOICDI']=(coll_def['EMPLOICDI']/(coll_def['NB_FOYERS_COLLEGIENS'])*100).round(1)\n",
    "coll_def['%_LOGSOCIAL']=(coll_def['LOGSOCIAL']/(coll_def['NB_FOYERS_COLLEGIENS'])*100).round(1)\n",
    "coll_def['%_ACTIV_ACTIFS']=(coll_def['ACTIV_ACTIFS']/(coll_def['NB_FOYERS_COLLEGIENS'])*100).round(1)\n",
    "coll_def['%_ACTIV_CHOMEURS']=(coll_def['ACTIV_CHOMEURS']/(coll_def['NB_FOYERS_COLLEGIENS'])*100).round(1)\n",
    "coll_def['%_ACTIV_ETUDIANT_STAGE']=(coll_def['ACTIV_ETUDIANT_STAGE']/(coll_def['NB_FOYERS_COLLEGIENS'])*100).round(1)\n",
    "coll_def['%_ACTIV_AU_FOYER']=(coll_def['ACTIV_AU_FOYER']/(coll_def['NB_FOYERS_COLLEGIENS'])*100).round(1)\n",
    "coll_def['%_IMMIGRES']=(coll_def['IMMIGRES']/(coll_def['NB_FOYERS_COLLEGIENS'])*100).round(1)\n",
    "\n",
    "#Calcul de l'écart à la moyenne départementale de la composition sociale de chaque secteur de collège (en nombre de points) :\n",
    "coll_def['DIFF_CS1']=(coll_def['%_CS1']-((coll_def['C19_POP15P_CS1'].sum()/coll_def['C19_POP15ACT'].sum())*100)).round(1)\n",
    "coll_def['DIFF_CS2']=(coll_def['%_CS2']-((coll_def['C19_POP15P_CS2'].sum()/coll_def['C19_POP15ACT'].sum())*100)).round(1)\n",
    "coll_def['DIFF_CS3']=(coll_def['%_CS3']-((coll_def['C19_POP15P_CS3'].sum()/coll_def['C19_POP15ACT'].sum())*100)).round(1)\n",
    "coll_def['DIFF_CS4']=(coll_def['%_CS4']-((coll_def['C19_POP15P_CS4'].sum()/coll_def['C19_POP15ACT'].sum())*100)).round(1)\n",
    "coll_def['DIFF_CS5']=(coll_def['%_CS5']-((coll_def['C19_POP15P_CS5'].sum()/coll_def['C19_POP15ACT'].sum())*100)).round(1)\n",
    "coll_def['DIFF_CS6']=(coll_def['%_CS6']-((coll_def['C19_POP15P_CS6'].sum()/coll_def['C19_POP15ACT'].sum())*100)).round(1)\n",
    "coll_def['DIFF_CS7']=(coll_def['%_CS7']-((coll_def['C19_POP15P_CS7'].sum()/coll_def['C19_POP15ACT'].sum())*100)).round(1)\n",
    "coll_def['DIFF_CS8']=(coll_def['%_CS8']-((coll_def['C19_POP15P_CS8'].sum()/coll_def['C19_POP15ACT'].sum())*100)).round(1)\n",
    "\n",
    "coll_def['DIFF_LOG0ACT']=(coll_def['%_LOG0ACT']-((coll_def['ACTIF_LOG_AUCUN'].sum()/coll_def['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "coll_def['DIFF_LOG1ACT']=(coll_def['%_LOG1ACT']-((coll_def['ACTIF_LOG_UN'].sum()/coll_def['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "coll_def['DIFF_LOG2ACT']=(coll_def['%_LOG2ACT']-((coll_def['ACTIF_LOG_DEUX'].sum()/coll_def['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "coll_def['DIFF_DIP0']=(coll_def['%_DIP0']-((coll_def['DIPLOM_AUCUN'].sum()/coll_def['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "coll_def['DIFF_DIPBRE']=(coll_def['%_DIPBRE']-((coll_def['DIPLOM_BREVET'].sum()/coll_def['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "coll_def['DIFF_DIPCAP']=(coll_def['%_DIPCAP']-((coll_def['DIPLOM_CAP_BAC'].sum()/coll_def['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "coll_def['DIFF_DIPBAC']=(coll_def['%_DIPBAC']-((coll_def['DIPLOM_BACPRO_BTS_LIC'].sum()/coll_def['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "coll_def['DIFF_DIPMAS']=(coll_def['%_DIPMAS']-((coll_def['DIPLOM_MASTER_DOC'].sum()/coll_def['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "\n",
    "coll_def['DIFF_MONOPARENT']=(coll_def['%_MONOPARENT']-((coll_def['MONOPARENT'].sum()/coll_def['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "coll_def['DIFF_EMPLOICDI']=(coll_def['%_EMPLOICDI']-((coll_def['EMPLOICDI'].sum()/coll_def['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "coll_def['DIFF_LOGSOCIAL']=(coll_def['%_LOGSOCIAL']-((coll_def['LOGSOCIAL'].sum()/coll_def['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "coll_def['DIFF_ACTIV_ACTIFS']=(coll_def['%_ACTIV_ACTIFS']-((coll_def['ACTIV_ACTIFS'].sum()/coll_def['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "coll_def['DIFF_ACTIV_CHOMEURS']=(coll_def['%_ACTIV_CHOMEURS']-((coll_def['ACTIV_CHOMEURS'].sum()/coll_def['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "coll_def['DIFF_ACTIV_ETUDIANT_STAGE']=(coll_def['%_ACTIV_ETUDIANT_STAGE']-((coll_def['ACTIV_ETUDIANT_STAGE'].sum()/coll_def['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "coll_def['DIFF_ACTIV_AU_FOYER']=(coll_def['%_ACTIV_AU_FOYER']-((coll_def['ACTIV_AU_FOYER'].sum()/coll_def['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "coll_def['DIFF_IMMIGRES']=(coll_def['%_IMMIGRES']-((coll_def['IMMIGRES'].sum()/coll_def['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "\n",
    "# Classement des écarts par catégories (pour faciliter le filtrage et la visualisation sous kepler.gl)\n",
    "# A1 : écart supérieur de 5 points à la moyenne départementale\n",
    "# A2 : écart supérieur de 10 points à la moyenne départementale\n",
    "# B1 : écart inférieur de -5 points à la moyenne départementale\n",
    "# B2 : écart inférieur de -10 points à la moyenne départementale\n",
    "# Les écarts entre -5 et 5 points sont considéres non-significatifs (valeur NaN)\n",
    "def tri_ecart(value):\n",
    "    a=-5\n",
    "    b=-10\n",
    "    arr=abs(value)    \n",
    "    if value<b:\n",
    "        result='b2'\n",
    "    elif value<a:\n",
    "        result='b1'\n",
    "    elif value>10:\n",
    "        result='A2'\n",
    "    elif value>5:\n",
    "        result='A1'\n",
    "    else:\n",
    "        result='moyen'\n",
    "    return result\n",
    "\n",
    "var=['CS1','CS2','CS3','CS4','CS5','CS6','CS7','CS8','LOG0ACT','LOG1ACT','LOG2ACT','DIP0','DIPBRE','DIPCAP','DIPBAC','DIPMAS','MONOPARENT','EMPLOICDI','LOGSOCIAL','ACTIV_ACTIFS','ACTIV_CHOMEURS','ACTIV_ETUDIANT_STAGE','ACTIV_AU_FOYER','IMMIGRES']\n",
    "for var in var:\n",
    "    coll_def[\"cat_\"+var]=coll_def[\"DIFF_\"+var].apply(tri_ecart)\n",
    "\n",
    "\n",
    "# Ajout du nom des collèges, avec les données de l'Annuaire de l'éducation : https://data.education.gouv.fr/explore/dataset/fr-en-annuaire-education/export/?disjunctive.nom_etablissement&disjunctive.type_etablissement&disjunctive.appartenance_education_prioritaire&disjunctive.type_contrat_prive&disjunctive.libelle_departement&disjunctive.libelle_academie&disjunctive.libelle_region&disjunctive.code_type_contrat_prive&disjunctive.pial&refine.libelle_academie=Lille&refine.libelle_departement=Nord&refine.type_etablissement=Coll%C3%A8ge\n",
    "noms=pd.read_csv('../fr-en-annuaire-education_NORD.csv', sep=';')[['Identifiant_de_l_etablissement','Nom_etablissement','Nom_commune']]\n",
    "noms=noms.rename({'Identifiant_de_l_etablissement':'codeuai', 'Nom_etablissement':'NOM_COLLEGE'}, axis=1)\n",
    "coll_def=coll_def.merge(noms, on='codeuai')\n",
    "coll_def=coll_def[[\"codeuai\",\"NOM_COLLEGE\",\"Nom_commune\",\"effectif\",\"IPS\",\"type_secteur\",\"NB_FOYERS_COLLEGIENS\",\"NB_COLLEGIENS\",\"longitude\",\"latitude\",\"geometry\",\"C19_POP15P\",\"C19_POP15ACT\",\"DIFF_LOG0ACT\",\"DIFF_LOG1ACT\",\"DIFF_LOG2ACT\",\"DIFF_DIP0\",\"DIFF_DIPBRE\",\"DIFF_DIPCAP\",\"DIFF_DIPBAC\",\"DIFF_DIPMAS\",\"DIFF_CS1\",\"DIFF_CS2\",\"DIFF_CS3\",\"DIFF_CS4\",\"DIFF_CS5\",\"DIFF_CS6\",\"DIFF_CS7\",\"DIFF_CS8\",\"DIFF_MONOPARENT\",\"DIFF_EMPLOICDI\",\"DIFF_LOGSOCIAL\",\"DIFF_ACTIV_ACTIFS\",\"DIFF_ACTIV_CHOMEURS\",\"DIFF_ACTIV_ETUDIANT_STAGE\",\"DIFF_ACTIV_AU_FOYER\",\"DIFF_IMMIGRES\",\"cat_CS1\", \"cat_CS2\", \"cat_CS3\", \"cat_CS4\",\"cat_CS5\", \"cat_CS6\", \"cat_CS7\", \"cat_CS8\", \"cat_LOG0ACT\",\"cat_LOG1ACT\", \"cat_LOG2ACT\", \"cat_DIP0\", \"cat_DIPBRE\", \"cat_DIPCAP\", \"cat_DIPBAC\", \"cat_DIPMAS\",\"cat_MONOPARENT\",\"cat_EMPLOICDI\", \"cat_LOGSOCIAL\", \"cat_ACTIV_ACTIFS\",\"cat_ACTIV_CHOMEURS\", \"cat_ACTIV_ETUDIANT_STAGE\",\"cat_ACTIV_AU_FOYER\", \"cat_IMMIGRES\",\"%_LOG0ACT\",\"%_LOG1ACT\",\"%_LOG2ACT\",\"%_DIP0\",\"%_DIPBRE\",\"%_DIPCAP\",\"%_DIPBAC\",\"%_DIPMAS\",\"%_CS1\",\"%_CS2\",\"%_CS3\",\"%_CS4\",\"%_CS5\",\"%_CS6\",\"%_CS7\",\"%_CS8\",\"%_MONOPARENT\",\"%_EMPLOICDI\",\"%_LOGSOCIAL\",\"%_ACTIV_ACTIFS\",\"%_ACTIV_CHOMEURS\",\"%_ACTIV_ETUDIANT_STAGE\",\"%_ACTIV_AU_FOYER\",\"%_IMMIGRES\",\"C19_POP15P_CS1\",\"C19_POP15P_CS2\",\"C19_POP15P_CS3\",\"C19_POP15P_CS4\",\"C19_POP15P_CS5\",\"C19_POP15P_CS6\",\"C19_POP15P_CS7\",\"C19_POP15P_CS8\",\"DIPLOM_AUCUN\",\"DIPLOM_BREVET\",\"DIPLOM_CAP_BAC\",\"DIPLOM_BACPRO_BTS_LIC\",\"DIPLOM_MASTER_DOC\",\"ACTIF_LOG_AUCUN\",\"ACTIF_LOG_UN\",\"ACTIF_LOG_DEUX\",\"MONOPARENT\",\"EMPLOICDI\",\"LOGSOCIAL\",\"ACTIV_ACTIFS\",\"ACTIV_CHOMEURS\",\"ACTIV_ETUDIANT_STAGE\",\"ACTIV_AU_FOYER\",\"IMMIGRES\"]]\n",
    "\n",
    "# Export au format geojson : \n",
    "coll_def=coll_def.to_crs('EPSG:4326')\n",
    "coll_def.to_file('SECTEURS_COLLEGES_MEL_PROFILS_SOCIAUX_2019.geojson')\n",
    "\n",
    "# Export au format csv (sans la géométrie) : \n",
    "coll_def_plat=coll_def.drop({'geometry'}, axis=1)\n",
    "coll_def_plat.to_csv('SECTEURS_COLLEGES_MEL_PROFILS_SOCIAUX_2019.csv')\n",
    "\n",
    "# Fusion avec les cercles 200m population 2017\n",
    "cercles=gpd.read_file('CERCLES_200M_POP2017_mel.geojson')[['geometry']]\n",
    "cercles['geometry']=cercles['geometry'].to_crs('EPSG:2154')\n",
    "cercles['centroid']=cercles['geometry'].centroid\n",
    "cercles=cercles.set_geometry('centroid')\n",
    "cercles=cercles.to_crs('EPSG:4326')\n",
    "coll_def_cercles=cercles.sjoin(coll_def)\n",
    "coll_def_cercles=coll_def_cercles.set_geometry('geometry')\n",
    "coll_def_cercles=coll_def_cercles.drop({'centroid'}, axis=1)\n",
    "coll_def_cercles=coll_def_cercles.dissolve(by='codeuai')\n",
    "coll_def_cercles=coll_def_cercles.to_crs('EPSG:4326')\n",
    "coll_def_cercles.to_file('SECTEURS_COLLEGES_MEL_PROFILS_SOCIAUX_CERCLES_2019.geojson')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Données par Iris"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'geopandas.geodataframe.GeoDataFrame'>\n",
      "Int64Index: 526 entries, 0 to 525\n",
      "Columns: 173 entries, INSEE_COM to cat_IMMIGRES\n",
      "dtypes: float64(139), int64(1), object(33)\n",
      "memory usage: 715.0+ KB\n"
     ]
    }
   ],
   "source": [
    "# Calcul des pourcentages de chaque indicateur social :\n",
    "iris_pop_log['C19_POP15ACT']=iris_pop_log['C19_POP15P_CS1']+iris_pop_log['C19_POP15P_CS2']+iris_pop_log['C19_POP15P_CS3']+iris_pop_log['C19_POP15P_CS4']+iris_pop_log['C19_POP15P_CS5']+iris_pop_log['C19_POP15P_CS6']+iris_pop_log['C19_POP15P_CS7']+iris_pop_log['C19_POP15P_CS8']\n",
    "\n",
    "\n",
    "iris_pop_log['%_CS1']=(iris_pop_log['C19_POP15P_CS1']/iris_pop_log['C19_POP15ACT'])*100\n",
    "iris_pop_log['%_CS2']=(iris_pop_log['C19_POP15P_CS2']/iris_pop_log['C19_POP15ACT'])*100\n",
    "iris_pop_log['%_CS3']=(iris_pop_log['C19_POP15P_CS3']/iris_pop_log['C19_POP15ACT'])*100\n",
    "iris_pop_log['%_CS4']=(iris_pop_log['C19_POP15P_CS4']/iris_pop_log['C19_POP15ACT'])*100\n",
    "iris_pop_log['%_CS5']=(iris_pop_log['C19_POP15P_CS5']/iris_pop_log['C19_POP15ACT'])*100\n",
    "iris_pop_log['%_CS6']=(iris_pop_log['C19_POP15P_CS6']/iris_pop_log['C19_POP15ACT'])*100\n",
    "iris_pop_log['%_CS7']=(iris_pop_log['C19_POP15P_CS7']/iris_pop_log['C19_POP15ACT'])*100\n",
    "iris_pop_log['%_CS8']=(iris_pop_log['C19_POP15P_CS8']/iris_pop_log['C19_POP15ACT'])*100\n",
    "iris_pop_log['%_LOG0ACT']=iris_pop_log['ACTIF_LOG_AUCUN']/(iris_pop_log['NB_FOYERS_COLLEGIENS'])*100\n",
    "iris_pop_log['%_LOG1ACT']=iris_pop_log['ACTIF_LOG_UN']/(iris_pop_log['NB_FOYERS_COLLEGIENS'])*100\n",
    "iris_pop_log['%_LOG2ACT']=iris_pop_log['ACTIF_LOG_DEUX']/(iris_pop_log['NB_FOYERS_COLLEGIENS'])*100\n",
    "iris_pop_log['%_DIP0']=iris_pop_log['DIPLOM_AUCUN']/(iris_pop_log['NB_FOYERS_COLLEGIENS'])*100\n",
    "iris_pop_log['%_DIPBRE']=iris_pop_log['DIPLOM_BREVET']/(iris_pop_log['NB_FOYERS_COLLEGIENS'])*100\n",
    "iris_pop_log['%_DIPCAP']=iris_pop_log['DIPLOM_CAP_BAC']/(iris_pop_log['NB_FOYERS_COLLEGIENS'])*100\n",
    "iris_pop_log['%_DIPBAC']=iris_pop_log['DIPLOM_BACPRO_BTS_LIC']/(iris_pop_log['NB_FOYERS_COLLEGIENS'])*100\n",
    "iris_pop_log['%_DIPMAS']=iris_pop_log['DIPLOM_MASTER_DOC']/(iris_pop_log['NB_FOYERS_COLLEGIENS'])*100\n",
    "\n",
    "iris_pop_log['%_MONOPARENT']=iris_pop_log['MONOPARENT']/(iris_pop_log['NB_FOYERS_COLLEGIENS'])*100\n",
    "iris_pop_log['%_EMPLOICDI']=iris_pop_log['EMPLOICDI']/(iris_pop_log['NB_FOYERS_COLLEGIENS'])*100\n",
    "iris_pop_log['%_LOGSOCIAL']=iris_pop_log['LOGSOCIAL']/(iris_pop_log['NB_FOYERS_COLLEGIENS'])*100\n",
    "iris_pop_log['%_ACTIV_ACTIFS']=iris_pop_log['ACTIV_ACTIFS']/(iris_pop_log['NB_FOYERS_COLLEGIENS'])*100\n",
    "iris_pop_log['%_ACTIV_CHOMEURS']=iris_pop_log['ACTIV_CHOMEURS']/(iris_pop_log['NB_FOYERS_COLLEGIENS'])*100\n",
    "iris_pop_log['%_ACTIV_ETUDIANT_STAGE']=iris_pop_log['ACTIV_ETUDIANT_STAGE']/(iris_pop_log['NB_FOYERS_COLLEGIENS'])*100\n",
    "iris_pop_log['%_ACTIV_AU_FOYER']=iris_pop_log['ACTIV_AU_FOYER']/(iris_pop_log['NB_FOYERS_COLLEGIENS'])*100\n",
    "iris_pop_log['%_IMMIGRES']=iris_pop_log['IMMIGRES']/(iris_pop_log['NB_FOYERS_COLLEGIENS'])*100\n",
    "\n",
    "#Calcul de l'écart à la moyenne départementale de la composition sociale de chaque secteur de collège (en nombre de points) :\n",
    "iris_pop_log['DIFF_CS1']=(iris_pop_log['%_CS1']-((iris_pop_log['C19_POP15P_CS1'].sum()/iris_pop_log['C19_POP15ACT'].sum())*100)).round(1)\n",
    "iris_pop_log['DIFF_CS2']=(iris_pop_log['%_CS2']-((iris_pop_log['C19_POP15P_CS2'].sum()/iris_pop_log['C19_POP15ACT'].sum())*100)).round(1)\n",
    "iris_pop_log['DIFF_CS3']=(iris_pop_log['%_CS3']-((iris_pop_log['C19_POP15P_CS3'].sum()/iris_pop_log['C19_POP15ACT'].sum())*100)).round(1)\n",
    "iris_pop_log['DIFF_CS4']=(iris_pop_log['%_CS4']-((iris_pop_log['C19_POP15P_CS4'].sum()/iris_pop_log['C19_POP15ACT'].sum())*100)).round(1)\n",
    "iris_pop_log['DIFF_CS5']=(iris_pop_log['%_CS5']-((iris_pop_log['C19_POP15P_CS5'].sum()/iris_pop_log['C19_POP15ACT'].sum())*100)).round(1)\n",
    "iris_pop_log['DIFF_CS6']=(iris_pop_log['%_CS6']-((iris_pop_log['C19_POP15P_CS6'].sum()/iris_pop_log['C19_POP15ACT'].sum())*100)).round(1)\n",
    "iris_pop_log['DIFF_CS7']=(iris_pop_log['%_CS7']-((iris_pop_log['C19_POP15P_CS7'].sum()/iris_pop_log['C19_POP15ACT'].sum())*100)).round(1)\n",
    "iris_pop_log['DIFF_CS8']=(iris_pop_log['%_CS8']-((iris_pop_log['C19_POP15P_CS8'].sum()/iris_pop_log['C19_POP15ACT'].sum())*100)).round(1)\n",
    "\n",
    "iris_pop_log['DIFF_LOG0ACT']=(iris_pop_log['%_LOG0ACT']-((iris_pop_log['ACTIF_LOG_AUCUN'].sum()/iris_pop_log['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "iris_pop_log['DIFF_LOG1ACT']=(iris_pop_log['%_LOG1ACT']-((iris_pop_log['ACTIF_LOG_UN'].sum()/iris_pop_log['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "iris_pop_log['DIFF_LOG2ACT']=(iris_pop_log['%_LOG2ACT']-((iris_pop_log['ACTIF_LOG_DEUX'].sum()/iris_pop_log['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "iris_pop_log['DIFF_DIP0']=(iris_pop_log['%_DIP0']-((iris_pop_log['DIPLOM_AUCUN'].sum()/iris_pop_log['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "iris_pop_log['DIFF_DIPBRE']=(iris_pop_log['%_DIPBRE']-((iris_pop_log['DIPLOM_BREVET'].sum()/iris_pop_log['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "iris_pop_log['DIFF_DIPCAP']=(iris_pop_log['%_DIPCAP']-((iris_pop_log['DIPLOM_CAP_BAC'].sum()/iris_pop_log['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "iris_pop_log['DIFF_DIPBAC']=(iris_pop_log['%_DIPBAC']-((iris_pop_log['DIPLOM_BACPRO_BTS_LIC'].sum()/iris_pop_log['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "iris_pop_log['DIFF_DIPMAS']=(iris_pop_log['%_DIPMAS']-((iris_pop_log['DIPLOM_MASTER_DOC'].sum()/iris_pop_log['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "\n",
    "iris_pop_log['DIFF_MONOPARENT']=(iris_pop_log['%_MONOPARENT']-((iris_pop_log['MONOPARENT'].sum()/iris_pop_log['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "iris_pop_log['DIFF_EMPLOICDI']=(iris_pop_log['%_EMPLOICDI']-((iris_pop_log['EMPLOICDI'].sum()/iris_pop_log['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "iris_pop_log['DIFF_LOGSOCIAL']=(iris_pop_log['%_LOGSOCIAL']-((iris_pop_log['LOGSOCIAL'].sum()/iris_pop_log['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "iris_pop_log['DIFF_ACTIV_ACTIFS']=(iris_pop_log['%_ACTIV_ACTIFS']-((iris_pop_log['ACTIV_ACTIFS'].sum()/iris_pop_log['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "iris_pop_log['DIFF_ACTIV_CHOMEURS']=(iris_pop_log['%_ACTIV_CHOMEURS']-((iris_pop_log['ACTIV_CHOMEURS'].sum()/iris_pop_log['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "iris_pop_log['DIFF_ACTIV_ETUDIANT_STAGE']=(iris_pop_log['%_ACTIV_ETUDIANT_STAGE']-((iris_pop_log['ACTIV_ETUDIANT_STAGE'].sum()/iris_pop_log['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "iris_pop_log['DIFF_ACTIV_AU_FOYER']=(iris_pop_log['%_ACTIV_AU_FOYER']-((iris_pop_log['ACTIV_AU_FOYER'].sum()/iris_pop_log['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "iris_pop_log['DIFF_IMMIGRES']=(iris_pop_log['%_IMMIGRES']-((iris_pop_log['IMMIGRES'].sum()/iris_pop_log['NB_FOYERS_COLLEGIENS'].sum())*100)).round(1)\n",
    "\n",
    "# Classement des écarts par catégories (pour faciliter le filtrage et la visualisation sous kepler.gl)\n",
    "# A1 : écart supérieur de 5 points à la moyenne départementale\n",
    "# A2 : écart supérieur de 10 points à la moyenne départementale\n",
    "# B1 : écart inférieur de -5 points à la moyenne départementale\n",
    "# B2 : écart inférieur de -10 points à la moyenne départementale\n",
    "# Les écarts entre -5 et 5 points sont considéres non-significatifs (valeur NaN)\n",
    "def tri_ecart(value):\n",
    "    a=-5\n",
    "    b=-10\n",
    "    arr=abs(value)    \n",
    "    if value<b:\n",
    "        result='b2'\n",
    "    elif value<a:\n",
    "        result='b1'\n",
    "    elif value>10:\n",
    "        result='A2'\n",
    "    elif value>5:\n",
    "        result='A1'\n",
    "    else:\n",
    "        result='moyen'\n",
    "    return result\n",
    "\n",
    "var=['CS1','CS2','CS3','CS4','CS5','CS6','CS7','CS8','LOG0ACT','LOG1ACT','LOG2ACT','DIP0','DIPBRE','DIPCAP','DIPBAC','DIPMAS','MONOPARENT','EMPLOICDI','LOGSOCIAL','ACTIV_ACTIFS','ACTIV_CHOMEURS','ACTIV_ETUDIANT_STAGE','ACTIV_AU_FOYER','IMMIGRES']\n",
    "for var in var:\n",
    "    iris_pop_log[\"cat_\"+var]=iris_pop_log[\"DIFF_\"+var].apply(tri_ecart)\n",
    "\n",
    "iris_pop_log.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# On récupère le géodataframe des Iris du département du Nord extrait plus haut:\n",
    "iris_metrop=iris_mel[['CODE_IRIS','geometry']]\n",
    "# On le fusionne avec les données sociales :\n",
    "iris_pop_log_OK=iris_metrop.merge(iris_pop_log, on='CODE_IRIS')\n",
    "\n",
    "#Export des données au format geojson par polygones Iris:\n",
    "iris_pop_log_OK=iris_pop_log_OK.to_crs('EPSG:4326')\n",
    "iris_pop_log_OK.to_file(\"IRIS_MEL_PROFILS_SOCIAUX_2019.geojson\")\n",
    "# Export d'une version CSV sans la géométrie\n",
    "iris_pop_log_plat=iris_pop_log_OK.drop(\"geometry\", axis=1)\n",
    "iris_pop_log_plat.to_csv('IRIS_MEL_PROFILS_SOCIAUX_PLAT_2019.csv')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "\"['CS1', 'CS2', 'CS3', 'CS4', 'CS5', 'CS6', 'CS7', 'CS8', 'LOG0ACT', 'LOG1ACT', 'LOG2ACT', 'DIP0', 'DIPBRE', 'DIPCAP', 'DIPBAC', 'DIPMAS'] not in index\"",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[1;32m/Users/denisvannier/Desktop/DATA_EXPLOR/PROJETS/SEGREGATIONS URBAINES/CARTE SCOLAIRE/SECTEURS COLLEGES LILLE/MEDIACITES_COLLEGES_LILLE/PROFILS_SOCIAUX_2019_SECTEURS_COLLEGES_LILLE.ipynb Cellule 18\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> <a href='vscode-notebook-cell:/Users/denisvannier/Desktop/DATA_EXPLOR/PROJETS/SEGREGATIONS%20URBAINES/CARTE%20SCOLAIRE/SECTEURS%20COLLEGES%20LILLE/MEDIACITES_COLLEGES_LILLE/PROFILS_SOCIAUX_2019_SECTEURS_COLLEGES_LILLE.ipynb#X21sZmlsZQ%3D%3D?line=0'>1</a>\u001b[0m iris_pop_log\u001b[39m=\u001b[39miris_pop_log[[\u001b[39m\"\u001b[39;49m\u001b[39mCODE_IRIS\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mINSEE_COM\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mNOM_COM\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mNOM_IRIS\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mNB_FOYERS_COLLEGIENS\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mNB_COLLEGIENS\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIFF_LOG0ACT\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIFF_LOG1ACT\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIFF_LOG2ACT\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIFF_DIP0\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIFF_DIPBRE\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIFF_DIPCAP\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIFF_DIPBAC\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIFF_DIPMAS\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIFF_CS1\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIFF_CS2\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIFF_CS3\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIFF_CS4\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIFF_CS5\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIFF_CS6\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIFF_CS7\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIFF_CS8\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIFF_MONOPARENT\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIFF_EMPLOICDI\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIFF_LOGSOCIAL\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIFF_ACTIV_ACTIFS\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIFF_ACTIV_CHOMEURS\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIFF_ACTIV_ETUDIANT_STAGE\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIFF_ACTIV_AU_FOYER\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIFF_IMMIGRES\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mcat_CS1\u001b[39;49m\u001b[39m\"\u001b[39;49m, \u001b[39m\"\u001b[39;49m\u001b[39mcat_CS2\u001b[39;49m\u001b[39m\"\u001b[39;49m, \u001b[39m\"\u001b[39;49m\u001b[39mcat_CS3\u001b[39;49m\u001b[39m\"\u001b[39;49m, \u001b[39m\"\u001b[39;49m\u001b[39mcat_CS4\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mcat_CS5\u001b[39;49m\u001b[39m\"\u001b[39;49m, \u001b[39m\"\u001b[39;49m\u001b[39mcat_CS6\u001b[39;49m\u001b[39m\"\u001b[39;49m, \u001b[39m\"\u001b[39;49m\u001b[39mcat_CS7\u001b[39;49m\u001b[39m\"\u001b[39;49m, \u001b[39m\"\u001b[39;49m\u001b[39mcat_CS8\u001b[39;49m\u001b[39m\"\u001b[39;49m, \u001b[39m\"\u001b[39;49m\u001b[39mcat_LOG0ACT\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mcat_LOG1ACT\u001b[39;49m\u001b[39m\"\u001b[39;49m, \u001b[39m\"\u001b[39;49m\u001b[39mcat_LOG2ACT\u001b[39;49m\u001b[39m\"\u001b[39;49m, \u001b[39m\"\u001b[39;49m\u001b[39mcat_DIP0\u001b[39;49m\u001b[39m\"\u001b[39;49m, \u001b[39m\"\u001b[39;49m\u001b[39mcat_DIPBRE\u001b[39;49m\u001b[39m\"\u001b[39;49m, \u001b[39m\"\u001b[39;49m\u001b[39mcat_DIPCAP\u001b[39;49m\u001b[39m\"\u001b[39;49m, \u001b[39m\"\u001b[39;49m\u001b[39mcat_DIPBAC\u001b[39;49m\u001b[39m\"\u001b[39;49m, \u001b[39m\"\u001b[39;49m\u001b[39mcat_DIPMAS\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mcat_MONOPARENT\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mcat_EMPLOICDI\u001b[39;49m\u001b[39m\"\u001b[39;49m, \u001b[39m\"\u001b[39;49m\u001b[39mcat_LOGSOCIAL\u001b[39;49m\u001b[39m\"\u001b[39;49m, \u001b[39m\"\u001b[39;49m\u001b[39mcat_ACTIV_ACTIFS\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mcat_ACTIV_CHOMEURS\u001b[39;49m\u001b[39m\"\u001b[39;49m, \u001b[39m\"\u001b[39;49m\u001b[39mcat_ACTIV_ETUDIANT_STAGE\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mcat_ACTIV_AU_FOYER\u001b[39;49m\u001b[39m\"\u001b[39;49m, \u001b[39m\"\u001b[39;49m\u001b[39mcat_IMMIGRES\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39m%\u001b[39;49m\u001b[39m_LOG0ACT\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39m%\u001b[39;49m\u001b[39m_LOG1ACT\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39m%\u001b[39;49m\u001b[39m_LOG2ACT\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39m%\u001b[39;49m\u001b[39m_DIP0\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39m%\u001b[39;49m\u001b[39m_DIPBRE\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39m%\u001b[39;49m\u001b[39m_DIPCAP\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39m%\u001b[39;49m\u001b[39m_DIPBAC\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39m%\u001b[39;49m\u001b[39m_DIPMAS\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39m%\u001b[39;49m\u001b[39m_CS1\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39m%\u001b[39;49m\u001b[39m_CS2\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39m%\u001b[39;49m\u001b[39m_CS3\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39m%\u001b[39;49m\u001b[39m_CS4\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39m%\u001b[39;49m\u001b[39m_CS5\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39m%\u001b[39;49m\u001b[39m_CS6\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39m%\u001b[39;49m\u001b[39m_CS7\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39m%\u001b[39;49m\u001b[39m_CS8\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39m%\u001b[39;49m\u001b[39m_MONOPARENT\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39m%\u001b[39;49m\u001b[39m_EMPLOICDI\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39m%\u001b[39;49m\u001b[39m_LOGSOCIAL\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39m%\u001b[39;49m\u001b[39m_ACTIV_ACTIFS\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39m%\u001b[39;49m\u001b[39m_ACTIV_CHOMEURS\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39m%\u001b[39;49m\u001b[39m_ACTIV_ETUDIANT_STAGE\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39m%\u001b[39;49m\u001b[39m_ACTIV_AU_FOYER\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39m%\u001b[39;49m\u001b[39m_IMMIGRES\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mCS1\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mCS2\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mCS3\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mCS4\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mCS5\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mCS6\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mCS7\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mCS8\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mLOG0ACT\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mLOG1ACT\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mLOG2ACT\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIP0\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIPBRE\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIPCAP\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIPBAC\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mDIPMAS\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mMONOPARENT\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mEMPLOICDI\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mLOGSOCIAL\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mACTIV_ACTIFS\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mACTIV_CHOMEURS\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mACTIV_ETUDIANT_STAGE\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mACTIV_AU_FOYER\u001b[39;49m\u001b[39m\"\u001b[39;49m,\u001b[39m\"\u001b[39;49m\u001b[39mIMMIGRES\u001b[39;49m\u001b[39m\"\u001b[39;49m]]\n\u001b[1;32m      <a href='vscode-notebook-cell:/Users/denisvannier/Desktop/DATA_EXPLOR/PROJETS/SEGREGATIONS%20URBAINES/CARTE%20SCOLAIRE/SECTEURS%20COLLEGES%20LILLE/MEDIACITES_COLLEGES_LILLE/PROFILS_SOCIAUX_2019_SECTEURS_COLLEGES_LILLE.ipynb#X21sZmlsZQ%3D%3D?line=2'>3</a>\u001b[0m \u001b[39m# On récupère le géodataframe des Iris du département du Nord extrait plus haut:\u001b[39;00m\n\u001b[1;32m      <a href='vscode-notebook-cell:/Users/denisvannier/Desktop/DATA_EXPLOR/PROJETS/SEGREGATIONS%20URBAINES/CARTE%20SCOLAIRE/SECTEURS%20COLLEGES%20LILLE/MEDIACITES_COLLEGES_LILLE/PROFILS_SOCIAUX_2019_SECTEURS_COLLEGES_LILLE.ipynb#X21sZmlsZQ%3D%3D?line=3'>4</a>\u001b[0m iris_metrop\u001b[39m=\u001b[39miris_mel[[\u001b[39m'\u001b[39m\u001b[39mCODE_IRIS\u001b[39m\u001b[39m'\u001b[39m,\u001b[39m'\u001b[39m\u001b[39mgeometry\u001b[39m\u001b[39m'\u001b[39m]]\n",
      "File \u001b[0;32m~/opt/anaconda3/envs/DATAXPLORE/lib/python3.9/site-packages/geopandas/geodataframe.py:1327\u001b[0m, in \u001b[0;36mGeoDataFrame.__getitem__\u001b[0;34m(self, key)\u001b[0m\n\u001b[1;32m   1321\u001b[0m \u001b[39mdef\u001b[39;00m \u001b[39m__getitem__\u001b[39m(\u001b[39mself\u001b[39m, key):\n\u001b[1;32m   1322\u001b[0m     \u001b[39m\"\"\"\u001b[39;00m\n\u001b[1;32m   1323\u001b[0m \u001b[39m    If the result is a column containing only 'geometry', return a\u001b[39;00m\n\u001b[1;32m   1324\u001b[0m \u001b[39m    GeoSeries. If it's a DataFrame with a 'geometry' column, return a\u001b[39;00m\n\u001b[1;32m   1325\u001b[0m \u001b[39m    GeoDataFrame.\u001b[39;00m\n\u001b[1;32m   1326\u001b[0m \u001b[39m    \"\"\"\u001b[39;00m\n\u001b[0;32m-> 1327\u001b[0m     result \u001b[39m=\u001b[39m \u001b[39msuper\u001b[39;49m()\u001b[39m.\u001b[39;49m\u001b[39m__getitem__\u001b[39;49m(key)\n\u001b[1;32m   1328\u001b[0m     geo_col \u001b[39m=\u001b[39m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_geometry_column_name\n\u001b[1;32m   1329\u001b[0m     \u001b[39mif\u001b[39;00m \u001b[39misinstance\u001b[39m(result, Series) \u001b[39mand\u001b[39;00m \u001b[39misinstance\u001b[39m(result\u001b[39m.\u001b[39mdtype, GeometryDtype):\n",
      "File \u001b[0;32m~/opt/anaconda3/envs/DATAXPLORE/lib/python3.9/site-packages/pandas/core/frame.py:3464\u001b[0m, in \u001b[0;36mDataFrame.__getitem__\u001b[0;34m(self, key)\u001b[0m\n\u001b[1;32m   3462\u001b[0m     \u001b[39mif\u001b[39;00m is_iterator(key):\n\u001b[1;32m   3463\u001b[0m         key \u001b[39m=\u001b[39m \u001b[39mlist\u001b[39m(key)\n\u001b[0;32m-> 3464\u001b[0m     indexer \u001b[39m=\u001b[39m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mloc\u001b[39m.\u001b[39;49m_get_listlike_indexer(key, axis\u001b[39m=\u001b[39;49m\u001b[39m1\u001b[39;49m)[\u001b[39m1\u001b[39m]\n\u001b[1;32m   3466\u001b[0m \u001b[39m# take() does not accept boolean indexers\u001b[39;00m\n\u001b[1;32m   3467\u001b[0m \u001b[39mif\u001b[39;00m \u001b[39mgetattr\u001b[39m(indexer, \u001b[39m\"\u001b[39m\u001b[39mdtype\u001b[39m\u001b[39m\"\u001b[39m, \u001b[39mNone\u001b[39;00m) \u001b[39m==\u001b[39m \u001b[39mbool\u001b[39m:\n",
      "File \u001b[0;32m~/opt/anaconda3/envs/DATAXPLORE/lib/python3.9/site-packages/pandas/core/indexing.py:1314\u001b[0m, in \u001b[0;36m_LocIndexer._get_listlike_indexer\u001b[0;34m(self, key, axis)\u001b[0m\n\u001b[1;32m   1311\u001b[0m \u001b[39melse\u001b[39;00m:\n\u001b[1;32m   1312\u001b[0m     keyarr, indexer, new_indexer \u001b[39m=\u001b[39m ax\u001b[39m.\u001b[39m_reindex_non_unique(keyarr)\n\u001b[0;32m-> 1314\u001b[0m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_validate_read_indexer(keyarr, indexer, axis)\n\u001b[1;32m   1316\u001b[0m \u001b[39mif\u001b[39;00m needs_i8_conversion(ax\u001b[39m.\u001b[39mdtype) \u001b[39mor\u001b[39;00m \u001b[39misinstance\u001b[39m(\n\u001b[1;32m   1317\u001b[0m     ax, (IntervalIndex, CategoricalIndex)\n\u001b[1;32m   1318\u001b[0m ):\n\u001b[1;32m   1319\u001b[0m     \u001b[39m# For CategoricalIndex take instead of reindex to preserve dtype.\u001b[39;00m\n\u001b[1;32m   1320\u001b[0m     \u001b[39m#  For IntervalIndex this is to map integers to the Intervals they match to.\u001b[39;00m\n\u001b[1;32m   1321\u001b[0m     keyarr \u001b[39m=\u001b[39m ax\u001b[39m.\u001b[39mtake(indexer)\n",
      "File \u001b[0;32m~/opt/anaconda3/envs/DATAXPLORE/lib/python3.9/site-packages/pandas/core/indexing.py:1377\u001b[0m, in \u001b[0;36m_LocIndexer._validate_read_indexer\u001b[0;34m(self, key, indexer, axis)\u001b[0m\n\u001b[1;32m   1374\u001b[0m     \u001b[39mraise\u001b[39;00m \u001b[39mKeyError\u001b[39;00m(\u001b[39mf\u001b[39m\u001b[39m\"\u001b[39m\u001b[39mNone of [\u001b[39m\u001b[39m{\u001b[39;00mkey\u001b[39m}\u001b[39;00m\u001b[39m] are in the [\u001b[39m\u001b[39m{\u001b[39;00maxis_name\u001b[39m}\u001b[39;00m\u001b[39m]\u001b[39m\u001b[39m\"\u001b[39m)\n\u001b[1;32m   1376\u001b[0m not_found \u001b[39m=\u001b[39m \u001b[39mlist\u001b[39m(ensure_index(key)[missing_mask\u001b[39m.\u001b[39mnonzero()[\u001b[39m0\u001b[39m]]\u001b[39m.\u001b[39munique())\n\u001b[0;32m-> 1377\u001b[0m \u001b[39mraise\u001b[39;00m \u001b[39mKeyError\u001b[39;00m(\u001b[39mf\u001b[39m\u001b[39m\"\u001b[39m\u001b[39m{\u001b[39;00mnot_found\u001b[39m}\u001b[39;00m\u001b[39m not in index\u001b[39m\u001b[39m\"\u001b[39m)\n",
      "\u001b[0;31mKeyError\u001b[0m: \"['CS1', 'CS2', 'CS3', 'CS4', 'CS5', 'CS6', 'CS7', 'CS8', 'LOG0ACT', 'LOG1ACT', 'LOG2ACT', 'DIP0', 'DIPBRE', 'DIPCAP', 'DIPBAC', 'DIPMAS'] not in index\""
     ]
    }
   ],
   "source": [
    "\n",
    "#iris_pop_log=iris_pop_log[[\"CODE_IRIS\",\"INSEE_COM\",\"NOM_COM\",\"NOM_IRIS\",\"NB_FOYERS_COLLEGIENS\",\"NB_COLLEGIENS\",\"DIFF_LOG0ACT\",\"DIFF_LOG1ACT\",\"DIFF_LOG2ACT\",\"DIFF_DIP0\",\"DIFF_DIPBRE\",\"DIFF_DIPCAP\",\"DIFF_DIPBAC\",\"DIFF_DIPMAS\",\"DIFF_CS1\",\"DIFF_CS2\",\"DIFF_CS3\",\"DIFF_CS4\",\"DIFF_CS5\",\"DIFF_CS6\",\"DIFF_CS7\",\"DIFF_CS8\",\"DIFF_MONOPARENT\",\"DIFF_EMPLOICDI\",\"DIFF_LOGSOCIAL\",\"DIFF_ACTIV_ACTIFS\",\"DIFF_ACTIV_CHOMEURS\",\"DIFF_ACTIV_ETUDIANT_STAGE\",\"DIFF_ACTIV_AU_FOYER\",\"DIFF_IMMIGRES\",\"cat_CS1\", \"cat_CS2\", \"cat_CS3\", \"cat_CS4\",\"cat_CS5\", \"cat_CS6\", \"cat_CS7\", \"cat_CS8\", \"cat_LOG0ACT\",\"cat_LOG1ACT\", \"cat_LOG2ACT\", \"cat_DIP0\", \"cat_DIPBRE\", \"cat_DIPCAP\", \"cat_DIPBAC\", \"cat_DIPMAS\",\"cat_MONOPARENT\",\"cat_EMPLOICDI\", \"cat_LOGSOCIAL\", \"cat_ACTIV_ACTIFS\",\"cat_ACTIV_CHOMEURS\", \"cat_ACTIV_ETUDIANT_STAGE\",\"cat_ACTIV_AU_FOYER\", \"cat_IMMIGRES\",\"%_LOG0ACT\",\"%_LOG1ACT\",\"%_LOG2ACT\",\"%_DIP0\",\"%_DIPBRE\",\"%_DIPCAP\",\"%_DIPBAC\",\"%_DIPMAS\",\"%_CS1\",\"%_CS2\",\"%_CS3\",\"%_CS4\",\"%_CS5\",\"%_CS6\",\"%_CS7\",\"%_CS8\",\"%_MONOPARENT\",\"%_EMPLOICDI\",\"%_LOGSOCIAL\",\"%_ACTIV_ACTIFS\",\"%_ACTIV_CHOMEURS\",\"%_ACTIV_ETUDIANT_STAGE\",\"%_ACTIV_AU_FOYER\",\"%_IMMIGRES\",\"CS1\",\"CS2\",\"CS3\",\"CS4\",\"CS5\",\"CS6\",\"CS7\",\"CS8\",\"LOG0ACT\",\"LOG1ACT\",\"LOG2ACT\",\"DIP0\",\"DIPBRE\",\"DIPCAP\",\"DIPBAC\",\"DIPMAS\",\"MONOPARENT\",\"EMPLOICDI\",\"LOGSOCIAL\",\"ACTIV_ACTIFS\",\"ACTIV_CHOMEURS\",\"ACTIV_ETUDIANT_STAGE\",\"ACTIV_AU_FOYER\",\"IMMIGRES\"]]\n",
    "\n",
    "# On récupère le géodataframe des Iris du département du Nord extrait plus haut:\n",
    "iris_metrop=iris_mel[['CODE_IRIS','geometry']]\n",
    "# On le fusionne avec les données sociales :\n",
    "iris_pop_log_OK=iris_metrop.merge(iris_pop_log, on='CODE_IRIS')\n",
    "\n",
    "#Export des données au format geojson par polygones Iris:\n",
    "iris_pop_log_OK=iris_pop_log_OK.to_crs('EPSG:4326')\n",
    "iris_pop_log_OK.to_file(\"IRIS_MEL_PROFILS_SOCIAUX_2019.geojson\")\n",
    "# Export d'une version CSV sans la géométrie\n",
    "iris_pop_log_plat=iris_pop_log_OK.drop(\"geometry\", axis=1)\n",
    "iris_pop_log_plat.to_csv('IRIS_MEL_PROFILS_SOCIAUX_PLAT_2019.csv')\n",
    "\n",
    "\n",
    "# Fusion avec les cercles 200m population 2017\n",
    "cercles=gpd.read_file('CERCLES_200M_POP2017_mel.geojson')[['geometry']]\n",
    "cercles['geometry']=cercles['geometry'].to_crs('EPSG:2154')\n",
    "cercles['centroid']=cercles['geometry'].centroid\n",
    "cercles=cercles.set_geometry('centroid')\n",
    "cercles=cercles.to_crs('EPSG:4326')\n",
    "iris_pop_log_OK['CODE_IRIS']=iris_pop_log_OK['CODE_IRIS'].astype('str')\n",
    "iris_pop_log_OK=iris_pop_log_OK.to_crs('EPSG:4326')\n",
    "iris_pop_log_cercles=cercles.sjoin(iris_pop_log_OK)\n",
    "iris_pop_log_cercles=iris_pop_log_cercles.set_geometry('geometry')\n",
    "iris_pop_log_cercles=iris_pop_log_cercles.drop({'centroid'}, axis=1)\n",
    "iris_pop_log_cercles=iris_pop_log_cercles.dissolve(by='CODE_IRIS')\n",
    "iris_pop_log_cercles=iris_pop_log_cercles.to_crs('EPSG:4326')\n",
    "iris_pop_log_cercles.to_file('IRIS_MEL_PROFILS_SOCIAUX_CERCLES_2019.geojson')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.9 ('DATAXPLORE')",
   "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.9 | packaged by conda-forge | (main, Dec 20 2021, 02:41:37) \n[Clang 11.1.0 ]"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "375f3d611b5238bc734cda3c649c29caee2849da8d5fb7814243c33fcb6d771d"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}