5 - Traitement de donnees avec Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Chapitre 5 : Traitement de données avec Pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Importer et mettre les options"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:29:25.297013Z",
     "iopub.status.busy": "2020-12-22T18:29:25.295652Z",
     "iopub.status.idle": "2020-12-22T18:29:27.676910Z",
     "shell.execute_reply": "2020-12-22T18:29:27.677720Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:29:27.702569Z",
     "iopub.status.busy": "2020-12-22T18:29:27.695883Z",
     "iopub.status.idle": "2020-12-22T18:29:27.718744Z",
     "shell.execute_reply": "2020-12-22T18:29:27.719470Z"
    }
   },
   "outputs": [],
   "source": [
    "pd.set_option('display.width', 80)\n",
    "pd.set_option('display.max_columns', 5)\n",
    "pd.set_option('precision', 1)\n",
    "pd.set_option('max_colwidth', 12)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Charger les données"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:29:27.727037Z",
     "iopub.status.busy": "2020-12-22T18:29:27.723974Z",
     "iopub.status.idle": "2020-12-22T18:31:04.082780Z",
     "shell.execute_reply": "2020-12-22T18:31:04.081955Z"
    }
   },
   "outputs": [],
   "source": [
    "tableau = pd.read_excel(\"./data/base-pop-2015-communes.xls\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:04.104984Z",
     "iopub.status.busy": "2020-12-22T18:31:04.086065Z",
     "iopub.status.idle": "2020-12-22T18:31:04.162985Z",
     "shell.execute_reply": "2020-12-22T18:31:04.164782Z"
    }
   },
   "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>CODGEO</th>\n",
       "      <th>REG</th>\n",
       "      <th>...</th>\n",
       "      <th>C15_POP55P_CS7</th>\n",
       "      <th>C15_POP55P_CS8</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01001</td>\n",
       "      <td>84</td>\n",
       "      <td>...</td>\n",
       "      <td>140.0</td>\n",
       "      <td>10.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01002</td>\n",
       "      <td>84</td>\n",
       "      <td>...</td>\n",
       "      <td>64.5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01004</td>\n",
       "      <td>84</td>\n",
       "      <td>...</td>\n",
       "      <td>2864.2</td>\n",
       "      <td>244.6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01005</td>\n",
       "      <td>84</td>\n",
       "      <td>...</td>\n",
       "      <td>310.0</td>\n",
       "      <td>30.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01006</td>\n",
       "      <td>84</td>\n",
       "      <td>...</td>\n",
       "      <td>24.8</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 108 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  CODGEO  REG  ... C15_POP55P_CS7 C15_POP55P_CS8\n",
       "0  01001   84  ...        140.0           10.0  \n",
       "1  01002   84  ...         64.5            0.0  \n",
       "2  01004   84  ...       2864.2          244.6  \n",
       "3  01005   84  ...        310.0           30.0  \n",
       "4  01006   84  ...         24.8            0.0  \n",
       "\n",
       "[5 rows x 108 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tableau.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:04.181983Z",
     "iopub.status.busy": "2020-12-22T18:31:04.180977Z",
     "iopub.status.idle": "2020-12-22T18:31:04.185098Z",
     "shell.execute_reply": "2020-12-22T18:31:04.185609Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(35399, 108)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tableau.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:04.311504Z",
     "iopub.status.busy": "2020-12-22T18:31:04.257000Z",
     "iopub.status.idle": "2020-12-22T18:31:04.342280Z",
     "shell.execute_reply": "2020-12-22T18:31:04.347972Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(tableau.index)[0:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:04.379720Z",
     "iopub.status.busy": "2020-12-22T18:31:04.359321Z",
     "iopub.status.idle": "2020-12-22T18:31:04.412240Z",
     "shell.execute_reply": "2020-12-22T18:31:04.413761Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['CODGEO',\n",
       " 'REG',\n",
       " 'DEP',\n",
       " 'LIBGEO',\n",
       " 'P15_POP',\n",
       " 'P15_POP0014',\n",
       " 'P15_POP1529',\n",
       " 'P15_POP3044']"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(tableau.columns)[0:8]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:04.419616Z",
     "iopub.status.busy": "2020-12-22T18:31:04.418518Z",
     "iopub.status.idle": "2020-12-22T18:31:04.453978Z",
     "shell.execute_reply": "2020-12-22T18:31:04.455747Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'01'"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tableau.loc[0,\"DEP\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:04.478732Z",
     "iopub.status.busy": "2020-12-22T18:31:04.477289Z",
     "iopub.status.idle": "2020-12-22T18:31:04.503295Z",
     "shell.execute_reply": "2020-12-22T18:31:04.513162Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    01001\n",
       "1    01002\n",
       "2    01004\n",
       "3    01005\n",
       "4    01006\n",
       "Name: CODGEO, dtype: object"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "colonne_a_afficher = tableau.columns[0]\n",
    "tableau[colonne_a_afficher].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Mettre en forme"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:04.532253Z",
     "iopub.status.busy": "2020-12-22T18:31:04.528059Z",
     "iopub.status.idle": "2020-12-22T18:31:04.584958Z",
     "shell.execute_reply": "2020-12-22T18:31:04.552519Z"
    }
   },
   "outputs": [],
   "source": [
    "colonnes = ['DEP', 'LIBGEO', 'P15_POP', 'P15_POPF', 'P15_POP0014', \n",
    "            'P15_POP1529','P15_POP3044', 'P15_POP4559', \n",
    "            'P15_POP6074', 'P15_POP7589','P15_POP90P', \n",
    "            'P15_F0014', 'P15_F1529', 'P15_F3044', 'P15_F4559',\n",
    "            'P15_F6074', 'P15_F7589', 'P15_F90P']\n",
    "tableau = tableau[colonnes]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:04.697599Z",
     "iopub.status.busy": "2020-12-22T18:31:04.696307Z",
     "iopub.status.idle": "2020-12-22T18:31:04.789951Z",
     "shell.execute_reply": "2020-12-22T18:31:04.791171Z"
    }
   },
   "outputs": [],
   "source": [
    "tableau = tableau.set_index(\"DEP\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:04.818363Z",
     "iopub.status.busy": "2020-12-22T18:31:04.817227Z",
     "iopub.status.idle": "2020-12-22T18:31:04.848890Z",
     "shell.execute_reply": "2020-12-22T18:31:04.850225Z"
    }
   },
   "outputs": [],
   "source": [
    "tableau.columns = [\n",
    "     \"Ville\", \"Total\", \"Nombre femme\", \"Nombre 0-14 ans\", \n",
    "     \"Nombre 15-29\", \"Nombre 30-44\", \"Nombre 45-59\",\n",
    "     \"Nombre 60-74\", \"Nombre 75-89\", \"Nombre 90 et +\",\n",
    "     \"Nombre femme 0-14 ans\", \"Nombre femme 15-29\",\n",
    "     \"Nombre femme 30-44\", \"Nombre femme 45-59\",\n",
    "     \"Nombre femme 60-74\", \"Nombre femme 75-89\",\n",
    "     \"Nombre femme 90 et +\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:04.868742Z",
     "iopub.status.busy": "2020-12-22T18:31:04.867766Z",
     "iopub.status.idle": "2020-12-22T18:31:04.880360Z",
     "shell.execute_reply": "2020-12-22T18:31:04.881448Z"
    }
   },
   "outputs": [],
   "source": [
    "tableau[\"numeros_departements\"] = tableau.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:04.900825Z",
     "iopub.status.busy": "2020-12-22T18:31:04.899635Z",
     "iopub.status.idle": "2020-12-22T18:31:05.133314Z",
     "shell.execute_reply": "2020-12-22T18:31:05.144058Z"
    }
   },
   "outputs": [],
   "source": [
    "tableau.loc[\"01\", \"noms_departements\"] = \"Ain\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:05.157136Z",
     "iopub.status.busy": "2020-12-22T18:31:05.150389Z",
     "iopub.status.idle": "2020-12-22T18:31:05.388217Z",
     "shell.execute_reply": "2020-12-22T18:31:05.390138Z"
    }
   },
   "outputs": [],
   "source": [
    "nouveau_tableau = tableau.drop(\"noms_departements\", axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Recodage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:05.422342Z",
     "iopub.status.busy": "2020-12-22T18:31:05.421062Z",
     "iopub.status.idle": "2020-12-22T18:31:05.560676Z",
     "shell.execute_reply": "2020-12-22T18:31:05.562759Z"
    }
   },
   "outputs": [],
   "source": [
    "def recodage_population(pop):\n",
    "    if not isinstance(pop, int):\n",
    "        return None\n",
    "    if pop < 1000:\n",
    "        return \"Population faible\"\n",
    "    if pop < 50000:\n",
    "        return \"Population moyenne\"\n",
    "    return \"Population forte\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:05.589638Z",
     "iopub.status.busy": "2020-12-22T18:31:05.583974Z",
     "iopub.status.idle": "2020-12-22T18:31:05.821379Z",
     "shell.execute_reply": "2020-12-22T18:31:05.822502Z"
    }
   },
   "outputs": [],
   "source": [
    "recodage = tableau[\"Total\"].apply(recodage_population)\n",
    "tableau[\"Total_reco\"] = recodage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:05.835356Z",
     "iopub.status.busy": "2020-12-22T18:31:05.827980Z",
     "iopub.status.idle": "2020-12-22T18:31:05.850647Z",
     "shell.execute_reply": "2020-12-22T18:31:05.851663Z"
    }
   },
   "outputs": [],
   "source": [
    "def calculer_proportion(ligne):\n",
    "    if pd.isnull(ligne[\"Total\"]) or ligne[\"Total\"]==0:\n",
    "        return None\n",
    "    pourcentage = 100 * ligne[\"Nombre femme\"]/ligne[\"Total\"]\n",
    "    return pourcentage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:05.870087Z",
     "iopub.status.busy": "2020-12-22T18:31:05.863095Z",
     "iopub.status.idle": "2020-12-22T18:31:12.850548Z",
     "shell.execute_reply": "2020-12-22T18:31:12.851564Z"
    }
   },
   "outputs": [],
   "source": [
    "proportions = tableau.apply(calculer_proportion,axis=1)\n",
    "tableau[\"Proportion femmes\"] = proportions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:12.866159Z",
     "iopub.status.busy": "2020-12-22T18:31:12.865085Z",
     "iopub.status.idle": "2020-12-22T18:31:12.869366Z",
     "shell.execute_reply": "2020-12-22T18:31:12.870189Z"
    }
   },
   "outputs": [],
   "source": [
    "def fonction_x2(x):\n",
    "    return 2*x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:12.884721Z",
     "iopub.status.busy": "2020-12-22T18:31:12.878342Z",
     "iopub.status.idle": "2020-12-22T18:31:12.916666Z",
     "shell.execute_reply": "2020-12-22T18:31:12.919574Z"
    }
   },
   "outputs": [],
   "source": [
    "tableau[\"Proportion femmes sup\"] = tableau[\"Proportion femmes\"]\\\n",
    "                .apply(lambda x : True if x > 51.45 else False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:12.932677Z",
     "iopub.status.busy": "2020-12-22T18:31:12.931460Z",
     "iopub.status.idle": "2020-12-22T18:31:12.941013Z",
     "shell.execute_reply": "2020-12-22T18:31:12.949417Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "L'Abergement-Clémenciat a 767.0 habitants\n",
      "L'Abergement-de-Varey a 241.0 habitants\n",
      "Ambérieu-en-Bugey a 14127.0 habitants\n",
      "Ambérieux-en-Dombes a 1619.0 habitants\n",
      "Ambléon a 109.0 habitants\n"
     ]
    }
   ],
   "source": [
    "for i,l in tableau.head().iterrows():\n",
    "    print(\"{} a {} habitants\".format(l[\"Ville\"],l[\"Total\"]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:12.966171Z",
     "iopub.status.busy": "2020-12-22T18:31:12.956918Z",
     "iopub.status.idle": "2020-12-22T18:31:13.091493Z",
     "shell.execute_reply": "2020-12-22T18:31:13.095432Z"
    }
   },
   "outputs": [],
   "source": [
    "nouveau_tableau = tableau.fillna(\"donnée absente\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:13.115853Z",
     "iopub.status.busy": "2020-12-22T18:31:13.114193Z",
     "iopub.status.idle": "2020-12-22T18:31:13.226671Z",
     "shell.execute_reply": "2020-12-22T18:31:13.230804Z"
    }
   },
   "outputs": [],
   "source": [
    "nouveau_tableau = tableau.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:13.255773Z",
     "iopub.status.busy": "2020-12-22T18:31:13.252039Z",
     "iopub.status.idle": "2020-12-22T18:31:13.311911Z",
     "shell.execute_reply": "2020-12-22T18:31:13.313130Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "42\n"
     ]
    }
   ],
   "source": [
    "filtre = tableau[\"Total\"] > 100000\n",
    "echantillon = tableau[filtre]\n",
    "print(len(echantillon))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Associer des tableaux"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:13.333106Z",
     "iopub.status.busy": "2020-12-22T18:31:13.329738Z",
     "iopub.status.idle": "2020-12-22T18:31:13.371824Z",
     "shell.execute_reply": "2020-12-22T18:31:13.372510Z"
    }
   },
   "outputs": [],
   "source": [
    "donnees_comp = pd.read_excel(\"data/departements-francais.xls\")\n",
    "donnees_comp[\"NUMÉRO\"] = donnees_comp[\"NUMÉRO\"].astype(str)\n",
    "donnees_comp = donnees_comp.set_index(\"NUMÉRO\")\n",
    "noms_departements = donnees_comp[\"NOM\"]\n",
    "dictionnaire_num_nom = dict(noms_departements)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:13.379719Z",
     "iopub.status.busy": "2020-12-22T18:31:13.378685Z",
     "iopub.status.idle": "2020-12-22T18:31:13.383349Z",
     "shell.execute_reply": "2020-12-22T18:31:13.384585Z"
    }
   },
   "outputs": [],
   "source": [
    "def associer(x, dic):\n",
    "    if x in dic:\n",
    "        return dic[x]\n",
    "    return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:13.393938Z",
     "iopub.status.busy": "2020-12-22T18:31:13.390716Z",
     "iopub.status.idle": "2020-12-22T18:31:13.434441Z",
     "shell.execute_reply": "2020-12-22T18:31:13.435188Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DEP\n",
       "01    None\n",
       "01    None\n",
       "01    None\n",
       "01    None\n",
       "01    None\n",
       "Name: noms_départements, dtype: object"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tableau[\"noms_départements\"] = tableau[\"numeros_departements\"]\\\n",
    "            .apply(associer,args=[dictionnaire_num_nom])\n",
    "tableau[\"noms_départements\"].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:13.459082Z",
     "iopub.status.busy": "2020-12-22T18:31:13.447063Z",
     "iopub.status.idle": "2020-12-22T18:31:13.704298Z",
     "shell.execute_reply": "2020-12-22T18:31:13.705363Z"
    }
   },
   "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>Ville</th>\n",
       "      <th>Total</th>\n",
       "      <th>...</th>\n",
       "      <th>NOM</th>\n",
       "      <th>CHEF LIEU</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DEP</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>01</th>\n",
       "      <td>L'Aberge...</td>\n",
       "      <td>767.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Ain</td>\n",
       "      <td>Bourg-en...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01</th>\n",
       "      <td>L'Aberge...</td>\n",
       "      <td>241.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Ain</td>\n",
       "      <td>Bourg-en...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01</th>\n",
       "      <td>Ambérieu...</td>\n",
       "      <td>14127.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Ain</td>\n",
       "      <td>Bourg-en...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01</th>\n",
       "      <td>Ambérieu...</td>\n",
       "      <td>1619.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Ain</td>\n",
       "      <td>Bourg-en...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>01</th>\n",
       "      <td>Ambléon</td>\n",
       "      <td>109.0</td>\n",
       "      <td>...</td>\n",
       "      <td>Ain</td>\n",
       "      <td>Bourg-en...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           Ville    Total  ...  NOM    CHEF LIEU\n",
       "DEP                        ...                  \n",
       "01   L'Aberge...    767.0  ...  Ain  Bourg-en...\n",
       "01   L'Aberge...    241.0  ...  Ain  Bourg-en...\n",
       "01   Ambérieu...  14127.0  ...  Ain  Bourg-en...\n",
       "01   Ambérieu...   1619.0  ...  Ain  Bourg-en...\n",
       "01       Ambléon    109.0  ...  Ain  Bourg-en...\n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "donnees_comp = pd.read_excel(\"data/departements-francais.xls\")\n",
    "donnees_comp[\"NUMÉRO\"] = donnees_comp[\"NUMÉRO\"].astype(str)\n",
    "donnees_comp[\"NUMÉRO\"] = donnees_comp[\"NUMÉRO\"]\\\n",
    "                    .apply(lambda x : \"0\"+x if len(x)==1 else x)\n",
    "donnees_comp = donnees_comp.set_index(\"NUMÉRO\")\n",
    "donnees_comp = donnees_comp[[\"NOM\",\"CHEF LIEU\"]]\n",
    "\n",
    "nouveau_tableau = tableau.join(donnees_comp,\n",
    "                    on=\"numeros_departements\")\n",
    "nouveau_tableau.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:13.718086Z",
     "iopub.status.busy": "2020-12-22T18:31:13.716665Z",
     "iopub.status.idle": "2020-12-22T18:31:13.727835Z",
     "shell.execute_reply": "2020-12-22T18:31:13.729288Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    12\n",
       "1    14\n",
       "2    11\n",
       "3     8\n",
       "4    19\n",
       "5    13\n",
       "6     3\n",
       "dtype: int64"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "notes = pd.Series([12,14,11,8,19,13,3])\n",
    "notes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:13.737064Z",
     "iopub.status.busy": "2020-12-22T18:31:13.736056Z",
     "iopub.status.idle": "2020-12-22T18:31:13.775579Z",
     "shell.execute_reply": "2020-12-22T18:31:13.775101Z"
    }
   },
   "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>notes 1</th>\n",
       "      <th>notes 2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>19</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>13</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   notes 1  notes 2\n",
       "0       12       14\n",
       "1       14       15\n",
       "2       11       18\n",
       "3        8       16\n",
       "4       19       11\n",
       "5       13       14\n",
       "6        3        7"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "notes = pd.DataFrame([\n",
    "        [12,14],[14,15],[11,18],[8,16],[19,11],[13,14],[3,7]],\n",
    "        columns = [\"notes 1\",\"notes 2\"])\n",
    "notes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2020-12-22T18:31:13.780053Z",
     "iopub.status.busy": "2020-12-22T18:31:13.779238Z",
     "iopub.status.idle": "2020-12-22T18:31:14.171623Z",
     "shell.execute_reply": "2020-12-22T18:31:14.172223Z"
    }
   },
   "outputs": [],
   "source": [
    "notes.to_excel(\"./data/fichier_notes.xls\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}