preparation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tutoriel sur la préparation des données\n",
    "\n",
    "## 1. Lecture des données\n",
    "\n",
    "Pour lire les données, on va utiliser la bibliothèque **pandas**.\n",
    "Elle support [plusieurs types de fichiers](https://pandas.pydata.org/pandas-docs/stable/io.html): csv (read\\_csv), JSON (read\\_json), HTML (read\\_html), MS Excel (read\\_excel), SQL (read\\_sql), etc.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 704,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ici, on va utiliser l'ensemble des données [Census Income Data Set (Adult)](https://archive.ics.uci.edu/ml/datasets/Census+Income).\n",
    "Les données se composent de 14 caractéristiques et de 48842 échantillons.\n",
    "Dans le but de l'exercice, on a réduit le nombre des caractéristiques à 7 et quelques échantillons dispersés sur plusieurs formats de fichiers.\n",
    "\n",
    "### 1.1. Lecture du fichier adult1.csv\n",
    "\n",
    "Le premier fichier (data/adult1.csv) est un fichier CSV avec des colonnes séparées par des virgules (50 échantillons).\n",
    "Le fichier contient les colonnes suivantes (avec l'entête: titres des colonnes) dans l'ordre:\n",
    "1. age: entier.\n",
    "1. workclass: Private, Self-emp-not-inc, Self-emp-inc, Federal-gov, Local-gov, State-gov, Without-pay, Never-worked.  \n",
    "1. education: Bachelors, Some-college, 11th, HS-grad, Prof-school, Assoc-acdm, Assoc-voc, 9th, 7th-8th, 12th, Masters, 1st-4th, 10th, Doctorate, 5th-6th, Preschool.  \n",
    "1. Marital-status: Married-civ-spouse, Divorced, Never-married, Separated, Widowed, Married-spouse-absent, Married-AF-spouse.\n",
    "1. occupation: Tech-support, Craft-repair, Other-service, Sales, Exec-managerial, Prof-specialty, Handlers-cleaners, Machine-op-inspct, Adm-clerical, Farming-fishing, Transport-moving, Priv-house-serv, Protective-serv, Armed-Forces.  \n",
    "1. sex: Female, Male.\n",
    "1. Hours-per-week: entier.\n",
    "1. class: <=50K, >50K\n",
    "\n",
    "On sait que le fichier est bien formé; donc, on ne va pas vérifier le format.\n",
    "On va ignorer les espaces qui suivent les séparateurs en utilisant l'option *skipinitialspace*."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 705,
   "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>age</th>\n",
       "      <th>workclass</th>\n",
       "      <th>education</th>\n",
       "      <th>Marital-status</th>\n",
       "      <th>occupation</th>\n",
       "      <th>sex</th>\n",
       "      <th>Hours-per-week</th>\n",
       "      <th>class</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>39.0</td>\n",
       "      <td>State-gov</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>Never-married</td>\n",
       "      <td>Adm-clerical</td>\n",
       "      <td>Male</td>\n",
       "      <td>40</td>\n",
       "      <td>&lt;=50K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>50.0</td>\n",
       "      <td>Self-emp-not-inc</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>Married-civ-spouse</td>\n",
       "      <td>Exec-managerial</td>\n",
       "      <td>Male</td>\n",
       "      <td>13</td>\n",
       "      <td>&lt;=50K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>38.0</td>\n",
       "      <td>Private</td>\n",
       "      <td>HS-grad</td>\n",
       "      <td>Divorced</td>\n",
       "      <td>Handlers-cleaners</td>\n",
       "      <td>Male</td>\n",
       "      <td>40</td>\n",
       "      <td>&lt;=50K</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    age         workclass  education      Marital-status         occupation  \\\n",
       "0  39.0         State-gov  Bachelors       Never-married       Adm-clerical   \n",
       "1  50.0  Self-emp-not-inc  Bachelors  Married-civ-spouse    Exec-managerial   \n",
       "2  38.0           Private    HS-grad            Divorced  Handlers-cleaners   \n",
       "\n",
       "    sex  Hours-per-week  class  \n",
       "0  Male              40  <=50K  \n",
       "1  Male              13  <=50K  \n",
       "2  Male              40  <=50K  "
      ]
     },
     "execution_count": 705,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adult1 = pandas.read_csv(\"data/adult1.csv\", skipinitialspace=True)\n",
    "adult1.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.2. Lecture du fichier adult2.csv\n",
    "\n",
    "Le deuxième fichier est un fichier CSV lui aussi, mais les colonnes sont séparées par des points-virgules.\n",
    "Le fichier est mal-formé; il existe des lignes avec séparation par virgules.\n",
    "Aussi, il n'y a pas d'entête pour désigner les noms des colonnes (caractéristiques).\n",
    "Voici le sens des colonnes dans l'ordre:\n",
    "1. class: Y, N (il gagne plus de 50K)\n",
    "1. age: entier\n",
    "1. sex: F, M\n",
    "1. workclass: Private, Self-emp-not-inc, Self-emp-inc, Federal-gov, Local-gov, State-gov, Without-pay, Never-worked.\n",
    "1. education: Bachelors, Some-college, 11th, HS-grad, Prof-school, Assoc-acdm, Assoc-voc, 9th, 7th-8th, 12th, Masters, 1st-4th, 10th, Doctorate, 5th-6th, Preschool.\n",
    "1. hours-per-week: entier.\n",
    "1. marital-status: Married-civ-spouse, Divorced, Never-married, Separated, Widowed, Married-spouse-absent, Married-AF-spouse.\n",
    "\n",
    "Dans ce cas, on va créer une liste des noms des colonnes (*noms*) et l'assigner comme entête en utilisant l'option *names*.\n",
    "Aussi, il faut spécifier quil n'y a pas d'entête (la première ligne contient des données et pas les noms des colonnes) en utilisant l'option *header=None*.\n",
    "Le séparateur du fichier CSV peut être spécifié en utilisant l'option *sep*."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 706,
   "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>class</th>\n",
       "      <th>age</th>\n",
       "      <th>sex</th>\n",
       "      <th>workclass</th>\n",
       "      <th>education</th>\n",
       "      <th>hours-per-week</th>\n",
       "      <th>marital-status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>N</td>\n",
       "      <td>25</td>\n",
       "      <td>F</td>\n",
       "      <td>Private</td>\n",
       "      <td>Some-college</td>\n",
       "      <td>40</td>\n",
       "      <td>Married-civ-spouse</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>N</td>\n",
       "      <td>18</td>\n",
       "      <td>F</td>\n",
       "      <td>Private</td>\n",
       "      <td>HS-grad</td>\n",
       "      <td>30</td>\n",
       "      <td>Never-married</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Y</td>\n",
       "      <td>47</td>\n",
       "      <td>F</td>\n",
       "      <td>Private, Prof-school</td>\n",
       "      <td>60</td>\n",
       "      <td>Married-civ-spouse</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  class age sex             workclass     education      hours-per-week  \\\n",
       "0     N  25   F               Private  Some-college                  40   \n",
       "1     N  18   F               Private       HS-grad                  30   \n",
       "2     Y  47   F  Private, Prof-school            60  Married-civ-spouse   \n",
       "\n",
       "       marital-status  \n",
       "0  Married-civ-spouse  \n",
       "1       Never-married  \n",
       "2                 NaN  "
      ]
     },
     "execution_count": 706,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "noms = [\"class\", \"age\", \"sex\", \"workclass\", \"education\", \"hours-per-week\", \"marital-status\"]\n",
    "adult2 = pandas.read_csv(\"data/adult2.csv\", skipinitialspace=True, sep=\";\", header=None, names=noms)\n",
    "adult2.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pour plus d'options veuillez consulter la documentation de [read_csv](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html).\n",
    "Le problème qui se pose est qu'on va avoir des lignes avec des \"NaN\" (valeurs non définies).\n",
    "On va régler ça dans l'étape de nétoyage des données.\n",
    "\n",
    "### Lecture du fichier adult3.db (sqlite)\n",
    "\n",
    "Le troisième fichier est de format Sqlite, dont le schéma est le suivant:\n",
    "\n",
    "```sql\n",
    "CREATE TABLE `income` (\n",
    "\t`num`\tINTEGER, --identifiant\n",
    "\t`age`\tINTEGER,\n",
    "\t`workclass`\tTEXT,\n",
    "\t`education`\tTEXT,\n",
    "\t`marital-status`\tTEXT,\n",
    "\t`sex`\tTEXT,\n",
    "\t`hours-per-day`\tREAL,\n",
    "\t`class`\tTEXT\n",
    ");\n",
    "```\n",
    "\n",
    "Les valeurs possibles des champs (les valeurs non définies sont marquées par \"?\"):\n",
    "1. num: entier pour identifier l'individu\n",
    "1. age: entier\n",
    "1. workclass: Private, Self-emp-not-inc, Self-emp-inc, Federal-gov, Local-gov, State-gov, Without-pay, Never-worked.\n",
    "1. education: Bachelors, Some-college, 11th, HS-grad, Prof-school, Assoc-acdm, Assoc-voc, 9th, 7th-8th, 12th, Masters, 1st-4th, 10th, Doctorate, 5th-6th, Preschool.\n",
    "1. marital-status: married, divorced, widowed, single.\n",
    "1. sex: F, M\n",
    "1. hours-per-day: réel; la moyenne des heurs pour chaque jour (supposant, on travaille 5 jours/semaine)\n",
    "1. class: Y, N\n",
    "\n",
    "Pour lire les données d'une base de données, on va utiliser la méthode **read_sql_query** de pandas.\n",
    "L'SGBD peut être interrogé en utilisant le module **sqlite3**.\n",
    "Les valeurs \"?\" veulent dire \"pas définies\".\n",
    "Pour être cohérent avec les données précédentes, on doit remplacer les \"?\" par la valeur \"NaN\" de **numpy**.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 707,
   "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>num</th>\n",
       "      <th>age</th>\n",
       "      <th>workclass</th>\n",
       "      <th>education</th>\n",
       "      <th>marital-status</th>\n",
       "      <th>sex</th>\n",
       "      <th>hours-per-day</th>\n",
       "      <th>class</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>76</td>\n",
       "      <td>Private</td>\n",
       "      <td>Masters</td>\n",
       "      <td>married</td>\n",
       "      <td>M</td>\n",
       "      <td>8.0</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>44</td>\n",
       "      <td>Private</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>married</td>\n",
       "      <td>M</td>\n",
       "      <td>12.0</td>\n",
       "      <td>Y</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>47</td>\n",
       "      <td>Self-emp-not-inc</td>\n",
       "      <td>Masters</td>\n",
       "      <td>single</td>\n",
       "      <td>F</td>\n",
       "      <td>10.0</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   num  age         workclass  education marital-status sex  hours-per-day  \\\n",
       "0    1   76           Private    Masters        married   M            8.0   \n",
       "1    2   44           Private  Bachelors        married   M           12.0   \n",
       "2    3   47  Self-emp-not-inc    Masters         single   F           10.0   \n",
       "\n",
       "  class  \n",
       "0     Y  \n",
       "1     Y  \n",
       "2     N  "
      ]
     },
     "execution_count": 707,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlite3\n",
    "import numpy\n",
    "#établir la connexion avec la base de données\n",
    "con = sqlite3.connect(\"data/adult3.db\")\n",
    "#récupérer le résultat d'une réquête SQL sur cette connexion\n",
    "adult3 = pandas.read_sql_query(\"SELECT * FROM income\", con)\n",
    "\n",
    "#remplacer les valeurs \"?\" par NaN de numpy\n",
    "adult3 = adult3.replace('?', numpy.nan)\n",
    "\n",
    "adult3.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Lecture du fichier adult4.xml\n",
    "\n",
    "Le 4ième fichier est de format XML dont la [DTD](https://fr.wikipedia.org/wiki/Document_type_definition) est la suivante:\n",
    "\n",
    "```xml\n",
    "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n",
    "<!ELEMENT income (candidat)*>\n",
    "<!ATTLIST candidat id ID #REQUIRED>\n",
    "<!ELEMENT candidat (age, workclass, education, marital-status, sex, hours-per-week, class)>\n",
    "<!ELEMENT age #PCDATA>\n",
    "<!ELEMENT workclass #PCDATA>\n",
    "<!ELEMENT education #PCDATA>\n",
    "<!ELEMENT marital-status #PCDATA>\n",
    "<!ELEMENT sex #PCDATA>\n",
    "<!ELEMENT hours-per-week #PCDATA>\n",
    "<!ELEMENT class #PCDATA>\n",
    "```\n",
    "\n",
    "- Les valeurs possibles des champs nominaux sont comme celles de la base de données sqlite (fichier adult3.db).\n",
    "- Les valeurs non définies sont représentées par l'absence de leurs balises respectives dans le fichier XML.\n",
    "\n",
    "Ce dernier point pose un problème puisque le DTD spécifie que les champs doivent tous être présents. Donc, on doi changer le DTD afin d'accepter des champs manquants. \n",
    "\n",
    "```xml\n",
    "<!ELEMENT candidat (age?, workclass?, education?, marital-status?, sex?, hours-per-week?, class)>\n",
    "```\n",
    "\n",
    "Pour valider le fichier XML, on va utiliser la bibliothèque **lxml**.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 708,
   "metadata": {},
   "outputs": [],
   "source": [
    "from lxml import etree\n",
    "#créer le parser et spécifier qu'il doit valider le DTD\n",
    "parser = etree.XMLParser(dtd_validation=True)\n",
    "#analyser le fichier XML en utilisant ce parser\n",
    "arbre = etree.parse(\"data/adult4.xml\", parser)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pour traiter un nœud XML, on va définir une fonction qui retourne son texte s'il existe, sinon la valeur \"NaN\" de **numpy**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 709,
   "metadata": {},
   "outputs": [],
   "source": [
    "def valeur_noeud(noeud):\n",
    "    return noeud.text if noeud is not None else numpy.nan"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On crée un objet de type **pandas.DataFrame** avec les titres des colonnes.\n",
    "Ensuite, on parcourt les éléments un par un en ajoutant les valeurs dans l'objet qu'on a créé."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 710,
   "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</th>\n",
       "      <th>age</th>\n",
       "      <th>workclass</th>\n",
       "      <th>education</th>\n",
       "      <th>marital-status</th>\n",
       "      <th>sex</th>\n",
       "      <th>hours-per-week</th>\n",
       "      <th>class</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>52</td>\n",
       "      <td>47</td>\n",
       "      <td>Local-gov</td>\n",
       "      <td>Some-college</td>\n",
       "      <td>divorced</td>\n",
       "      <td>F</td>\n",
       "      <td>38</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>53</td>\n",
       "      <td>34</td>\n",
       "      <td>Private</td>\n",
       "      <td>HS-grad</td>\n",
       "      <td>single</td>\n",
       "      <td>F</td>\n",
       "      <td>40</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>54</td>\n",
       "      <td>33</td>\n",
       "      <td>Private</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>single</td>\n",
       "      <td>F</td>\n",
       "      <td>40</td>\n",
       "      <td>N</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id age  workclass     education marital-status sex hours-per-week class\n",
       "0  52  47  Local-gov  Some-college       divorced   F             38     N\n",
       "1  53  34    Private       HS-grad         single   F             40     N\n",
       "2  54  33    Private     Bachelors         single   F             40     N"
      ]
     },
     "execution_count": 710,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "noms2 = [\"id\", \"age\", \"workclass\", \"education\", \"marital-status\", \"sex\", \"hours-per-week\", \"class\"]\n",
    "adult4 = pandas.DataFrame(columns=noms2)\n",
    "\n",
    "for candidat in arbre.getroot():\n",
    "    idi = candidat.get(\"id\")\n",
    "    age = valeur_noeud(candidat.find(\"age\"))\n",
    "    workclass = valeur_noeud(candidat.find(\"workclass\"))\n",
    "    education = valeur_noeud(candidat.find(\"education\"))\n",
    "    marital = valeur_noeud(candidat.find(\"marital-status\"))\n",
    "    sex = valeur_noeud(candidat.find(\"sex\"))\n",
    "    hours = valeur_noeud(candidat.find(\"hours-per-week\"))\n",
    "    klass = valeur_noeud(candidat.find(\"class\"))\n",
    "\n",
    "    adult4 = adult4.append(\n",
    "        pandas.Series([idi, age, workclass, education, marital, sex, hours, klass],\n",
    "        index=noms2), ignore_index=True)\n",
    "adult4.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Intégration des données\n",
    "\n",
    "Lors de la lecture des 4 fichiers, on remarque que leurs schémas sont différents.\n",
    "Donc, avant de fusionner les 4 schémas, il faut régler les problèmes qui gênent à cette opération.\n",
    "Voici la liste des problèmes rencontrés:\n",
    "\n",
    "1. Ordre et noms différents des caractéristiques. Par exemple, le champs \"class\" est en dernier dans des schémas, et en premier dans d'autres.\n",
    "  - Renommer les caractéristiques\n",
    "  - Réorganiser l'ordre des caractéristiques dans les tableaux.\n",
    "\n",
    "1. Problème d'échelle: dans la caractéristique \"adult3.hours-per-day\" qui est représetée par \"hours-per-week\" dans les autres schémas. On multiplie les valeurs par 5 (nous avons supposé 5 jours/semaines) et on renomme la colonne \"hours-per-week\".\n",
    "\n",
    "1. Échantillons (enregistrement) redondants: les schémas de \"adult3.db\" et \"adult4.xml\" contiennent une caractéristique: \"num\" et \"id\" respectivement (qu'on a unifié dans l'étape précédente).\n",
    "Ici, on ne veut pas qu'une personne se répète plus d'une fois.\n",
    "  - Supprimer une des deux échantillons redondants\n",
    "  - On remarque qu'il y a des échantillons dupliqués où un est plus complet (ne contient pas de valeurs manquantes) que l'autre. Donc, on garde le plus complet.\n",
    "1. Caractéristiques inutiles (de plus): adult1.csv contient la caractéristique \"occupation\" qui ne figure pas chez les autres fichiers.\n",
    "  - Supprimer la colonne\n",
    "1. Conflits de valeurs, les caractéristiques suivantes ont des différentes valeurs possibles entre les schémas (solution: unifier les valeurs).\n",
    "  - marital-status: on garde les valeurs les plus restreintes (married, divorced, widowed, single). Les autres valeurs seront transformées à une de ces 4.\n",
    "  - sex: on garde les valeurs avec moins de taille (F, M)\n",
    "  - class: on garde les valeurs avec moins de taille (Y, N)\n",
    "\n",
    "\n",
    "### 2.1. Ordre et noms différents des caractéristiques\n",
    "\n",
    "On commence par renommer les caractéristiques identiques.\n",
    "- adult3.num sera adult3.id, pour être homogène avec adult4.id\n",
    "- adult3.hours-per-day sera adult3.hours-per-week pour être homogène avec les autres schémas. On va transformer les valeurs après.\n",
    "- adult1.Hours-per-week sera adult1.hours-per-week (un problème de majuscule)\n",
    "- adult1.Marital-status sera adult1.marital-status (un problème de majuscule)\n",
    "\n",
    "On va utiliser la méthode [pandas.DataFrame.rename](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.rename.html) où l'objet \"adult3\" est de type **pandas.DataFrame**.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 711,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['num',\n",
       " 'age',\n",
       " 'workclass',\n",
       " 'education',\n",
       " 'marital-status',\n",
       " 'sex',\n",
       " 'hours-per-day',\n",
       " 'class']"
      ]
     },
     "execution_count": 711,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# les colonnes adult3 avant renomage\n",
    "list(adult3.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 712,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['id',\n",
       " 'age',\n",
       " 'workclass',\n",
       " 'education',\n",
       " 'marital-status',\n",
       " 'sex',\n",
       " 'hours-per-week',\n",
       " 'class']"
      ]
     },
     "execution_count": 712,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adult3.rename(columns={\"num\": \"id\", \"hours-per-day\": \"hours-per-week\"}, inplace=True)\n",
    "adult1.rename(columns={\"Hours-per-week\": \"hours-per-week\", \"Marital-status\": \"marital-status\"}, inplace=True)\n",
    "\n",
    "# les colonnes adult3 après renomage\n",
    "list(adult3.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ensuite, on va ordonner les caractéristiques selon cet ordre: \"age\", \"workclass\", \"education\", \"marital-status\", \"sex\", \"hours-per-week\", \"class\". Les caractéristiques en plus vont être mises en derniers. On va utiliser la méthode [pandas.DataFrame.reindex](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reindex.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 713,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['age',\n",
       " 'workclass',\n",
       " 'education',\n",
       " 'marital-status',\n",
       " 'sex',\n",
       " 'hours-per-week',\n",
       " 'class',\n",
       " 'id']"
      ]
     },
     "execution_count": 713,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ordre = [\"age\", \"workclass\", \"education\", \"marital-status\", \"sex\", \"hours-per-week\", \"class\"]\n",
    "adult1 = adult1.reindex(ordre + [\"occupation\"], axis=1)\n",
    "#print adult1.head()\n",
    "adult2 = adult2.reindex(ordre, axis=1)\n",
    "adult3 = adult3.reindex(ordre + [\"id\"], axis=1)\n",
    "adult4 = adult4.reindex(ordre + [\"id\"], axis=1)\n",
    "\n",
    "#les colonnes adult3 après ordonnancement\n",
    "list(adult3.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2. Problème d'échelle\n",
    "\n",
    "On a modifié \"adult3.hours-per-day\" par \"hours-per-week\" qui change le sens mais pas les valeurs.\n",
    "On va régler ça ici.\n",
    "\n",
    "On règle ce problème avant de régler la redondance, puisque dans l'étape suivante on va fusionner les deux tables \"adult3\" et \"adult4\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 714,
   "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>age</th>\n",
       "      <th>workclass</th>\n",
       "      <th>education</th>\n",
       "      <th>marital-status</th>\n",
       "      <th>sex</th>\n",
       "      <th>hours-per-week</th>\n",
       "      <th>class</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>76</td>\n",
       "      <td>Private</td>\n",
       "      <td>Masters</td>\n",
       "      <td>married</td>\n",
       "      <td>M</td>\n",
       "      <td>8.0</td>\n",
       "      <td>Y</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>44</td>\n",
       "      <td>Private</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>married</td>\n",
       "      <td>M</td>\n",
       "      <td>12.0</td>\n",
       "      <td>Y</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   age workclass  education marital-status sex  hours-per-week class  id\n",
       "0   76   Private    Masters        married   M             8.0     Y   1\n",
       "1   44   Private  Bachelors        married   M            12.0     Y   2"
      ]
     },
     "execution_count": 714,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# adult3 avant la transformation de l'échelle\n",
    "adult3.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 715,
   "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>age</th>\n",
       "      <th>workclass</th>\n",
       "      <th>education</th>\n",
       "      <th>marital-status</th>\n",
       "      <th>sex</th>\n",
       "      <th>hours-per-week</th>\n",
       "      <th>class</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>76</td>\n",
       "      <td>Private</td>\n",
       "      <td>Masters</td>\n",
       "      <td>married</td>\n",
       "      <td>M</td>\n",
       "      <td>40.0</td>\n",
       "      <td>Y</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>44</td>\n",
       "      <td>Private</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>married</td>\n",
       "      <td>M</td>\n",
       "      <td>60.0</td>\n",
       "      <td>Y</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   age workclass  education marital-status sex  hours-per-week class  id\n",
       "0   76   Private    Masters        married   M            40.0     Y   1\n",
       "1   44   Private  Bachelors        married   M            60.0     Y   2"
      ]
     },
     "execution_count": 715,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#transformer les heurs/jour à heurs/semaine\n",
    "adult3[\"hours-per-week\"] *= 5\n",
    "\n",
    "# adult3 après la transformation de l'échelle\n",
    "adult3.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3. Echantillons (enregistrement) redondants\n",
    "\n",
    "Les tables \"adult3\" et \"adult4\" contiennent des enregistrements avec le même \"id\". Une solution est de fusionner les deux tables dans une seule (disant \"adult34\"), ensuite utiliser la méthode [pandas.DataFrame.drop_duplicates](http://pandas.pydata.org/pandas-docs/version/0.17/generated/pandas.DataFrame.drop_duplicates.html). On peut choisir quelle occurence on veut garder.\n",
    "\n",
    "On fusionne les deux dataframes en une seule. \n",
    "Ensuite, on vérifie s'il y a des échantillons avec le même \"id\". Pour ce faire, on ordonne les échantillons selon le \"id\". Mais avant, il faut le transformer en entier\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 716,
   "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>age</th>\n",
       "      <th>workclass</th>\n",
       "      <th>education</th>\n",
       "      <th>marital-status</th>\n",
       "      <th>sex</th>\n",
       "      <th>hours-per-week</th>\n",
       "      <th>class</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>70</td>\n",
       "      <td>Private</td>\n",
       "      <td>Some-college</td>\n",
       "      <td>single</td>\n",
       "      <td>M</td>\n",
       "      <td>40</td>\n",
       "      <td>N</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>70</td>\n",
       "      <td>Private</td>\n",
       "      <td>Some-college</td>\n",
       "      <td>single</td>\n",
       "      <td>M</td>\n",
       "      <td>8</td>\n",
       "      <td>N</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>31</td>\n",
       "      <td>Private</td>\n",
       "      <td>HS-grad</td>\n",
       "      <td>single</td>\n",
       "      <td>F</td>\n",
       "      <td>30</td>\n",
       "      <td>N</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>31</td>\n",
       "      <td>Private</td>\n",
       "      <td>HS-grad</td>\n",
       "      <td>single</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6</td>\n",
       "      <td>N</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>22</td>\n",
       "      <td>Private</td>\n",
       "      <td>Some-college</td>\n",
       "      <td>married</td>\n",
       "      <td>M</td>\n",
       "      <td>24</td>\n",
       "      <td>N</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>22</td>\n",
       "      <td>Private</td>\n",
       "      <td>Some-college</td>\n",
       "      <td>married</td>\n",
       "      <td>M</td>\n",
       "      <td>4.8</td>\n",
       "      <td>N</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>36</td>\n",
       "      <td>Private</td>\n",
       "      <td>HS-grad</td>\n",
       "      <td>widowed</td>\n",
       "      <td>F</td>\n",
       "      <td>24</td>\n",
       "      <td>N</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Private</td>\n",
       "      <td>HS-grad</td>\n",
       "      <td>widowed</td>\n",
       "      <td>F</td>\n",
       "      <td>4.8</td>\n",
       "      <td>N</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>64</td>\n",
       "      <td>Private</td>\n",
       "      <td>11th</td>\n",
       "      <td>married</td>\n",
       "      <td>M</td>\n",
       "      <td>40</td>\n",
       "      <td>N</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>64</td>\n",
       "      <td>Private</td>\n",
       "      <td>11th</td>\n",
       "      <td>married</td>\n",
       "      <td>M</td>\n",
       "      <td>8</td>\n",
       "      <td>N</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>43</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Some-college</td>\n",
       "      <td>divorced</td>\n",
       "      <td>F</td>\n",
       "      <td>40</td>\n",
       "      <td>N</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>43</td>\n",
       "      <td>Federal-gov</td>\n",
       "      <td>Some-college</td>\n",
       "      <td>divorced</td>\n",
       "      <td>F</td>\n",
       "      <td>8</td>\n",
       "      <td>N</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    age    workclass     education marital-status  sex hours-per-week class  \\\n",
       "44   70      Private  Some-college         single    M             40     N   \n",
       "94   70      Private  Some-college         single    M              8     N   \n",
       "45   31      Private       HS-grad         single    F             30     N   \n",
       "95   31      Private       HS-grad         single  NaN              6     N   \n",
       "46   22      Private  Some-college        married    M             24     N   \n",
       "96   22      Private  Some-college        married    M            4.8     N   \n",
       "47   36      Private       HS-grad        widowed    F             24     N   \n",
       "97  NaN      Private       HS-grad        widowed    F            4.8     N   \n",
       "48   64      Private          11th        married    M             40     N   \n",
       "98   64      Private          11th        married    M              8     N   \n",
       "49   43          NaN  Some-college       divorced    F             40     N   \n",
       "99   43  Federal-gov  Some-college       divorced    F              8     N   \n",
       "\n",
       "    id  \n",
       "44  45  \n",
       "94  45  \n",
       "45  46  \n",
       "95  46  \n",
       "46  47  \n",
       "96  47  \n",
       "47  48  \n",
       "97  48  \n",
       "48  49  \n",
       "98  49  \n",
       "49  50  \n",
       "99  50  "
      ]
     },
     "execution_count": 716,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adult34 = pandas.concat([adult3, adult4], ignore_index=True)\n",
    "# définir le type de \"id\" comme étant entier, et remplacer la colonne\n",
    "adult34[\"id\"] = pandas.to_numeric(adult34[\"id\"], downcast=\"integer\")\n",
    "# ordonner les enregistrements par \"id\"\n",
    "# il faut ordonner pour que les fonctions duplicated() et groupby() marchent\n",
    "adult34 = adult34.sort_values(by=\"id\")\n",
    "\n",
    "# adult34 : vérification des échantillons redondants\n",
    "red = adult34[adult34.duplicated(\"id\", keep=False)]\n",
    "red"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Avant de supprimer un échantillon, il faut remplir les valeurs manquantes à partir des autres échaantillons identiques (le même \"id\") avant de supprimer un des deux. L'idée est la suivante:\n",
    "- On fusionne les deux tables\n",
    "- On regroupe les enregistrements en se basant sur la caractéristique (champs, attribut, colonne) \"id\".\n",
    "- On fait un remplissage en arrière par groupe: la valeur \"NaN\" sera remplacée par une valeur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 717,
   "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</th>\n",
       "      <th>age</th>\n",
       "      <th>workclass</th>\n",
       "      <th>education</th>\n",
       "      <th>marital-status</th>\n",
       "      <th>sex</th>\n",
       "      <th>hours-per-week</th>\n",
       "      <th>class</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [id, age, workclass, education, marital-status, sex, hours-per-week, class]\n",
       "Index: []"
      ]
     },
     "execution_count": 717,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# regrouper les par \"id\", et pour chaque groupe remplacer les\n",
    "# valeurs absentes par une valeur précédente dans le même groupe\n",
    "adult34 = adult34.groupby(\"id\").ffill()\n",
    "# supprimer les enregistrements dupliqués\n",
    "# on garde les derniers, puisqu'ils sont été réglés\n",
    "adult34.drop_duplicates(\"id\", keep=\"last\", inplace=True)\n",
    "\n",
    "# adult34 : vérification des échantillons redondants\n",
    "red = adult34[adult34.duplicated(\"id\", keep=False)]\n",
    "red"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.4 Caractéristiques inutiles\n",
    "\n",
    "Ici on va supprimer les colonnes inutiles:\n",
    "- adult1.occupation\n",
    "- adult34.id\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 718,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['age',\n",
       " 'workclass',\n",
       " 'education',\n",
       " 'marital-status',\n",
       " 'sex',\n",
       " 'hours-per-week',\n",
       " 'class',\n",
       " 'occupation']"
      ]
     },
     "execution_count": 718,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# les colonnes de adult1 avant\n",
    "list(adult1.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 719,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['age',\n",
       " 'workclass',\n",
       " 'education',\n",
       " 'marital-status',\n",
       " 'sex',\n",
       " 'hours-per-week',\n",
       " 'class']"
      ]
     },
     "execution_count": 719,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adult1.drop([\"occupation\"], axis=1, inplace=True)\n",
    "adult34.drop([\"id\"], axis=1, inplace=True)\n",
    "# les colonnes de adult1 après\n",
    "list(adult1.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.5. Conflits de valeurs\n",
    "\n",
    "Remplacer les valeurs de \"marital-status\" dans les tables \"adult1\" et \"adult2\" comme suit:\n",
    "- \"Never-married\" par \"single\"\n",
    "- \"Married-civ-spouse\", \"Married-spouse-absent\" et \"Married-AF-spouse\"  par \"married\"\n",
    "- \"Divorced\" et \"Separated\" par \"divorced\"\n",
    "- \"Widowed\" par \"widowed\"\n",
    "\n",
    "On peut vérifier les valeurs possibles pour une colonne en utilisant la fonction [pandas.Series.unique](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.unique.html):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 720,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Never-married', 'Married-civ-spouse', 'Divorced', nan,\n",
       "       'Married-spouse-absent', 'Separated', 'Married-AF-spouse'],\n",
       "      dtype=object)"
      ]
     },
     "execution_count": 720,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# les différentes valeurs du colonne adult1.marital-status\n",
    "adult1[\"marital-status\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 721,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['single', 'married', 'divorced', nan], dtype=object)"
      ]
     },
     "execution_count": 721,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dic = {\n",
    "    \"Never-married\": \"single\",\n",
    "    \"Married-civ-spouse\": \"married\",\n",
    "    \"Married-spouse-absent\": \"married\",\n",
    "    \"Married-AF-spouse\": \"married\",\n",
    "    \"Divorced\": \"divorced\",\n",
    "    \"Separated\": \"divorced\",\n",
    "    \"Widowed\": \"widowed\"\n",
    "}\n",
    "adult1[\"marital-status\"] = adult1[\"marital-status\"].map(dic)\n",
    "adult2[\"marital-status\"] = adult2[\"marital-status\"].map(dic)\n",
    "\n",
    "# les différentes valeurs du colonne adult1.marital-status après mappage\n",
    "adult1[\"marital-status\"].unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On remplace les valeurs de \"sex\" dans la table \"adult1\": (Female, Male) par (F, M) respectivement.\n",
    "\n",
    "Aussi, on remplace les valeurs de \"class\" dans la table \"adult1\": (<=50K, >50K) par (N, Y) respectivement.\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 722,
   "metadata": {},
   "outputs": [],
   "source": [
    "adult1[\"sex\"] = adult1[\"sex\"].map({\"Female\": \"F\", \"Male\": \"M\"})\n",
    "adult1[\"class\"] = adult1[\"class\"].map({\"<=50K\": \"N\", \">50K\": \"Y\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 723,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(50, 7)"
      ]
     },
     "execution_count": 723,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# dimension de la table adult1\n",
    "adult1.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 724,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(50, 7)"
      ]
     },
     "execution_count": 724,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# dimension de la table adult2\n",
    "adult2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 725,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(94, 7)"
      ]
     },
     "execution_count": 725,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# dimension de la table adult34\n",
    "adult34.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 726,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(194, 7)"
      ]
     },
     "execution_count": 726,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# fusionner les tables\n",
    "adult = pandas.concat([adult1, adult2, adult34], ignore_index=True)\n",
    "# dimension de la table adult\n",
    "adult.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Nétoyage des données\n",
    "Avant tout, on va vérifier le nombre des valeurs indéfinies dans chaque colonne."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 727,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "age                5\n",
       "workclass         10\n",
       "education          1\n",
       "marital-status     4\n",
       "sex                2\n",
       "hours-per-week     2\n",
       "class              0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 727,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adult.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On va, donc, nettoyer tous les enregistrements avec une valeur \"NaN\" sauf la colonne \"age\", on va la traiter autrement."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 728,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "age               3\n",
       "workclass         0\n",
       "education         0\n",
       "marital-status    0\n",
       "sex               0\n",
       "hours-per-week    0\n",
       "class             0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 728,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adult.dropna(subset=[\"workclass\", \"education\", \"marital-status\", \"sex\", \"hours-per-week\", \"class\"], inplace=True)\n",
    "adult.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Pour les valeurs de absentes de \"age\", on va faire un lissage par moyenne:\n",
    "- Transformer les valeurs de \"age\" comme numériques\n",
    "- On regroupe les enregistrements par \"class\" et \"education\". Ceci en supposant que les individus avec la même classe et le même niveau d'éducation ont le même age. Si on veut être plus sûr, on peut tracer des graphes entre \"age\" et les autres attributs.\n",
    "- On calcule la moyenne et l'arrondir\n",
    "- On l'affecte aux valeurs indéfinies\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 729,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "age               0\n",
       "workclass         0\n",
       "education         0\n",
       "marital-status    0\n",
       "sex               0\n",
       "hours-per-week    0\n",
       "class             0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 729,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adult[\"age\"] = pandas.to_numeric(adult[\"age\"])\n",
    "adult[\"age\"] = adult.groupby([\"class\", \"education\"])[\"age\"].transform(lambda x: x.fillna(int(round(x.mean()))))\n",
    "adult.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Transformation des données\n",
    "\n",
    "Pour transformer les données, on va utiliser la bibliothèque **scikit-learn**.\n",
    "\n",
    "### 4.1. Encodage des caractéristiques nominales\n",
    "\n",
    "Dans Scikit-learn, les caractéristiques nominales ne sont pas utilisables telles qu'elles sont. On doit les encoder vers des valeurs numériques. \n",
    "\n",
    "### 4.1.1. Encodage ordinal\n",
    "C'est le fait d'affecter des entiers à chaque valeur \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 730,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    Bachelors\n",
       "1    Bachelors\n",
       "2      HS-grad\n",
       "4    Bachelors\n",
       "5      Masters\n",
       "6          9th\n",
       "Name: education, dtype: object"
      ]
     },
     "execution_count": 730,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adult[\"education\"].head(6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 731,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[6.],\n",
       "       [6.],\n",
       "       [8.],\n",
       "       [6.],\n",
       "       [9.],\n",
       "       [3.]])"
      ]
     },
     "execution_count": 731,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sklearn import preprocessing\n",
    "ord_enc = preprocessing.OrdinalEncoder()\n",
    "# le résultat c'est un numpy.ndarray\n",
    "education_enc = ord_enc.fit_transform(adult[[\"education\"]])\n",
    "education_enc[:6,]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.1.2. Encodage One-Hot\n",
    "Des algorithmes d'apprentissage qui nécessitent des valeurs numériques et pas des catégories (comme les réseaux de neurones) ne marchent pas bien avec l'encodeur ordinal. Un autre encodeur est OneHot qui transforme une caractéristique vers plusieurs selon le nombre de ces valeurs.\n",
    "\n",
    "On va transformer la colonne \"sex\" vers deux colonnes \"M\" et \"F\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 732,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    M\n",
       "1    M\n",
       "2    M\n",
       "4    F\n",
       "5    F\n",
       "6    F\n",
       "Name: sex, dtype: object"
      ]
     },
     "execution_count": 732,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adult[\"sex\"].head(6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 733,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[0., 1.],\n",
       "       [0., 1.],\n",
       "       [0., 1.],\n",
       "       [1., 0.],\n",
       "       [1., 0.],\n",
       "       [1., 0.]])"
      ]
     },
     "execution_count": 733,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "onehot_enc = preprocessing.OneHotEncoder()\n",
    "# le résultat c'est un numpy.ndarray\n",
    "sex_enc = onehot_enc.fit_transform(adult[[\"sex\"]])\n",
    "sex_enc.toarray()[:6,]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.2. Normalisation\n",
    "\n",
    "#### 4.2.1. Mise à l'échelle min-max\n",
    "\n",
    "La mise en échelle min-max transforme chaque valeur numérique *x* vers une autre valeur *x' ∈ [0,1]* en utilisant la valeur minimale et la valeur maximale dans les données. Cette normalisation conserve la distance proportionnelle entre les valeurs d'une caractéristique.\n",
    "\n",
    "Supposant, on veut créer une colonne qui représente les proportions de \"hours-per-week\"."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 734,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    40.0\n",
       "1    13.0\n",
       "2    40.0\n",
       "Name: hours-per-week, dtype: float64"
      ]
     },
     "execution_count": 734,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adult[\"hours-per-week\"] = pandas.to_numeric(adult[\"hours-per-week\"])\n",
    "adult[\"hours-per-week\"].head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On utilise *MinMaxScaler*. Il n'accepte pas des colonnes, donc on utilise double crochets pour avoir la colonne comme une dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 735,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[0.49367089],\n",
       "       [0.15189873],\n",
       "       [0.49367089]])"
      ]
     },
     "execution_count": 735,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "min_max_scaler = preprocessing.MinMaxScaler()\n",
    "# le résultat c'est un numpy.ndarray\n",
    "hours_per_week_prop = min_max_scaler.fit_transform(adult[[\"hours-per-week\"]])\n",
    "hours_per_week_prop[:3,]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 736,
   "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>age</th>\n",
       "      <th>workclass</th>\n",
       "      <th>education</th>\n",
       "      <th>marital-status</th>\n",
       "      <th>sex</th>\n",
       "      <th>hours-per-week</th>\n",
       "      <th>class</th>\n",
       "      <th>hours-per-week-prop</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>39.0</td>\n",
       "      <td>State-gov</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>single</td>\n",
       "      <td>M</td>\n",
       "      <td>40.0</td>\n",
       "      <td>N</td>\n",
       "      <td>0.493671</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>50.0</td>\n",
       "      <td>Self-emp-not-inc</td>\n",
       "      <td>Bachelors</td>\n",
       "      <td>married</td>\n",
       "      <td>M</td>\n",
       "      <td>13.0</td>\n",
       "      <td>N</td>\n",
       "      <td>0.151899</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>38.0</td>\n",
       "      <td>Private</td>\n",
       "      <td>HS-grad</td>\n",
       "      <td>divorced</td>\n",
       "      <td>M</td>\n",
       "      <td>40.0</td>\n",
       "      <td>N</td>\n",
       "      <td>0.493671</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    age         workclass  education marital-status sex  hours-per-week class  \\\n",
       "0  39.0         State-gov  Bachelors         single   M            40.0     N   \n",
       "1  50.0  Self-emp-not-inc  Bachelors        married   M            13.0     N   \n",
       "2  38.0           Private    HS-grad       divorced   M            40.0     N   \n",
       "\n",
       "   hours-per-week-prop  \n",
       "0             0.493671  \n",
       "1             0.151899  \n",
       "2             0.493671  "
      ]
     },
     "execution_count": 736,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# pour ajouter la nouvelle caractéristique au dataframe\n",
    "adult[\"hours-per-week-prop\"] = hours_per_week_prop\n",
    "adult.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 4.2.2. Z-score\n",
    "\n",
    "Le Z-score est utilisé pour assurer que la distribution d'une caractéristique ait une moyenne = 0 et un écart type = 1.\n",
    "\n",
    "Supposant, on veut appliquer cette transformation sur l'age pour avoir une autre caractéristique"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 737,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    39.0\n",
       "1    50.0\n",
       "2    38.0\n",
       "Name: age, dtype: float64"
      ]
     },
     "execution_count": 737,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adult[\"age\"].head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 738,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[0.3442623 ],\n",
       "       [0.52459016],\n",
       "       [0.32786885]])"
      ]
     },
     "execution_count": 738,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "std_scaler = preprocessing.StandardScaler()\n",
    "# le résultat c'est un numpy.ndarray\n",
    "age_normal = min_max_scaler.fit_transform(adult[[\"age\"]])\n",
    "age_normal[:3,]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.3. Binarisation\n",
    "\n",
    "Supposant, on veut avoir une caractéristique \"jeune\" (age inférieure à 40)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 739,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     39.0\n",
       "1     50.0\n",
       "2     38.0\n",
       "4     28.0\n",
       "5     37.0\n",
       "6     49.0\n",
       "7     52.0\n",
       "8     31.0\n",
       "9     42.0\n",
       "10    43.0\n",
       "Name: age, dtype: float64"
      ]
     },
     "execution_count": 739,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adult[\"age\"].head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 740,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[0.],\n",
       "       [1.],\n",
       "       [0.],\n",
       "       [0.],\n",
       "       [0.],\n",
       "       [1.],\n",
       "       [1.],\n",
       "       [0.],\n",
       "       [1.],\n",
       "       [1.]])"
      ]
     },
     "execution_count": 740,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "binarizer = preprocessing.Binarizer(threshold=40)\n",
    "# le résultat c'est un numpy.ndarray\n",
    "age_bin = binarizer.fit_transform(adult[[\"age\"]])\n",
    "age_bin[:10,]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.17"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}