05_pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Pandas\n",
    "\n",
    "![image](./images/pandas.png)\n",
    "\n",
    "Pandas est le package de prédilection pour traiter des données structurées.\n",
    "\n",
    "Pandas est basé sur 2 structures extrêmement liées les Series et le DataFrame.\n",
    "\n",
    "Ces deux structures permettent de traiter des données sous forme de tableaux indexés.\n",
    "\n",
    "Les classes de Pandas utilisent des classes de Numpy, il est donc possible d'utiliser les fonctions universelles de Numpy sur les objets Pandas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# on importe pandas avec :\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Le DataFrame \n",
    "\n",
    "- Les DataFrame sont des objets très souples pouvant être construits de différentes façon\n",
    "- On peut les construire en récupérant des données copier / coller, où directement sur Internet, ou en entrant les valeurs manuellement\n",
    "\n",
    "\n",
    "- Les DataFrame se rapprochent des dictionnaires et on peut construire ces objets en utilisant `DataFrame(dico)`\n",
    "- De nombreux détails sur la création des DataFrame se trouve sur ce site :\n",
    "\n",
    "<http://pandas.pydata.org/pandas-docs/dev/generated/pandas.DataFrame.html>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "# Construction de DataFrame\n",
    "\n",
    "On peut simplement construire un DataFrame avec le classe pd.DataFrame() à partir de différentes structures :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "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>col_0</th>\n",
       "      <th>col_1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>obs_0</th>\n",
       "      <td>-0.422672</td>\n",
       "      <td>-1.681172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>obs_1</th>\n",
       "      <td>0.523909</td>\n",
       "      <td>1.599709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>obs_2</th>\n",
       "      <td>-0.120143</td>\n",
       "      <td>-0.552422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>obs_3</th>\n",
       "      <td>-0.054424</td>\n",
       "      <td>0.859583</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>obs_4</th>\n",
       "      <td>1.228404</td>\n",
       "      <td>1.540424</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          col_0     col_1\n",
       "obs_0 -0.422672 -1.681172\n",
       "obs_1  0.523909  1.599709\n",
       "obs_2 -0.120143 -0.552422\n",
       "obs_3 -0.054424  0.859583\n",
       "obs_4  1.228404  1.540424"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame1=pd.DataFrame(np.random.randn(10).reshape(5,2),\n",
    "             index=[\"obs_\"+str(i) for i in range(5)],\n",
    "             columns=[\"col_\"+str(i) for i in range(2)])\n",
    "frame1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "# Opérations sur les DataFrame\n",
    "\n",
    "On peut afficher le nom des colonnes :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['col_0', 'col_1'], dtype='object')\n"
     ]
    }
   ],
   "source": [
    "print(frame1.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "On peut accéder à une colonne avec :\n",
    "- `frame1.col_0` : attention au cas de nom de colonnes avec des espaces...\n",
    "- `frame1['col_0']`\n",
    "\n",
    "On peut accéder à une cellule avec :\n",
    "- `frame1.loc['obs1','col_0']` : on utilise les index et le nom des colonnes\n",
    "- `frame1.iloc[1,0]` : on utilise les positions dans le DataFrame\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "# Options de visualisation et de résumé\n",
    "\n",
    "Pour afficher les 3 premières lignes, on peut utiliser :\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "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>col_0</th>\n",
       "      <th>col_1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>obs_0</th>\n",
       "      <td>-0.422672</td>\n",
       "      <td>-1.681172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>obs_1</th>\n",
       "      <td>0.523909</td>\n",
       "      <td>1.599709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>obs_2</th>\n",
       "      <td>-0.120143</td>\n",
       "      <td>-0.552422</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          col_0     col_1\n",
       "obs_0 -0.422672 -1.681172\n",
       "obs_1  0.523909  1.599709\n",
       "obs_2 -0.120143 -0.552422"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame1.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "Pour afficher un résumé du DF :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 5 entries, obs_0 to obs_4\n",
      "Data columns (total 2 columns):\n",
      " #   Column  Non-Null Count  Dtype  \n",
      "---  ------  --------------  -----  \n",
      " 0   col_0   5 non-null      float64\n",
      " 1   col_1   5 non-null      float64\n",
      "dtypes: float64(2)\n",
      "memory usage: 120.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "frame1.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Importer des données externes\n",
    "\n",
    "Pandas est l'outil le plus efficace pour importer des données externes, il prend en charge de nombreux formats dont csv, Excel, SQL, SAS...\n",
    "\n",
    "\n",
    "## Importation de données avec Pandas\n",
    "\n",
    "Quel que soit le type de fichier, Pandas possède une fonction :\n",
    "```python\n",
    "frame=pd.read_...('chemin_du_fichier/nom_du_fichier',...)\n",
    "```\n",
    "Pour écrire un DataFrame dans un fichier, on utilise :\n",
    "```python\n",
    "frame.to_...('chemin_du_fichier/nom_du_fichier',...)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**Exercice :**\n",
    "    \n",
    "Importer un fichier `.csv` avec `pd.read_csv()`. On utilisera le fichier \"./data/airbnb.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/tljh/user/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3156: DtypeWarning: Columns (43,61,62) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "airbnb = pd.read_csv(\"https://www.stat4decision.com/airbnb.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Bien importé !\n"
     ]
    }
   ],
   "source": [
    "# pour vérifier que vous avez bien répondu à l'exercice, soumettre cette cellule\n",
    "# on utilise maj + Entrée\n",
    "assert airbnb[\"price\"].dtype == object\n",
    "print(\"Bien importé !\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Importer un fichier `.csv` qui a comme séparateur `;`, le nom du fichier est `base-dpt.csv`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "base_dpt = pd.read_csv(\"./Data/base-dpt.csv\", sep = ';')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Bien importé !\n"
     ]
    }
   ],
   "source": [
    "# pour vérifier que vous avez bien répondu à l'exercice, soumettre cette cellule\n",
    "# on utilise maj + Entrée\n",
    "assert type(base_dpt) == pd.DataFrame\n",
    "print(\"Bien importé !\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "# D'autres types de données\n",
    "\n",
    "## JSON\n",
    "Les objets JSON ressemblent à des dictionnaires.\n",
    "\n",
    "On utilise le module `json` puis la fonction `json.loads()` pour transformer une entrée JSON en objet json\n",
    "\n",
    "## HTML\n",
    "On utilise `pd.read_html(url)`. Cet fonction est basée sur les packages `beautifulsoup` et `html5lib`\n",
    "\n",
    "Cette fonction renvoie une liste de DataFrame qui représentent tous les DataFrame de la page. On ira ensuite chercher l'élément qui nous intéresse avec `frame_list[0]`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**Exercice :**\n",
    "    \n",
    "Importez un tableau en html depuis la page https://www.fdic.gov/resources/resolutions/bank-failures/failed-bank-list/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "web_bank = pd.read_html(\"https://www.fdic.gov/resources/resolutions/bank-failures/failed-bank-list/\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "frame_bank = web_bank[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Bien importé !\n"
     ]
    }
   ],
   "source": [
    "# pour vérifier que vous avez bien répondu à l'exercice, soumettre cette cellule\n",
    "# on utilise maj + Entrée\n",
    "assert type(frame_bank) == pd.DataFrame\n",
    "print(\"Bien importé !\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "# Importer depuis Excel\n",
    "\n",
    "On a deux approches pour Excel :\n",
    "- On peut utiliser `pd.read_excel()`\n",
    "- On peut utiliser la classe `pd.ExcelFile()`\n",
    "\n",
    "Dans ce cas, on utilise :\n",
    "```python\n",
    "xlsfile=pd.ExcelFile('fichier.xlsx')\n",
    "xlsfile.parse('Sheet1')\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**Exercice :** \n",
    "    \n",
    "Importez un fichier Excel avec les deux approches, on utilisera : `credit2.xlsx` et `ville.xls`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1- avec `pd.read_excel()` pour le fichier `credit2.xlsx`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/tljh/user/lib/python3.7/site-packages/ipykernel_launcher.py:1: FutureWarning: Your version of xlrd is 1.2.0. In xlrd >= 2.0, only the xls format is supported. As a result, the openpyxl engine will be used if it is installed and the engine argument is not specified. Install openpyxl instead.\n",
      "  \"\"\"Entry point for launching an IPython kernel.\n"
     ]
    }
   ],
   "source": [
    "credit2 = pd.read_excel(\"./Data/credit2.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Bravo !\n"
     ]
    }
   ],
   "source": [
    "# pour vérifier que vous avez bien répondu à l'exercice, soumettre cette cellule\n",
    "# on utilise maj + Entrée\n",
    "assert credit2[\"Age\"].max() == 83\n",
    "print(\"Bravo !\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2- avec `pd.ExcelFile()` pour le fichier `ville.xls`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "ville_excel = pd.ExcelFile(\"./Data/ville.xls\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['ville', 'ville-ain']"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ville_excel.sheet_names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "frame_ville = ville_excel.parse(\"ville\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Bravo !\n"
     ]
    }
   ],
   "source": [
    "# pour vérifier que vous avez bien répondu à l'exercice, soumettre cette cellule\n",
    "# on utilise maj + Entrée\n",
    "assert type(frame_ville) == pd.DataFrame\n",
    "print(\"Bravo !\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "# Importer des données SQL\n",
    "\n",
    "Pandas possède une fonction `read_sql()` qui permet d’importer directement des bases de données ou des queries dans des DataFrame\n",
    "\n",
    "Il faut tout de même un connecteur pour accéder aux bases de données\n",
    "\n",
    "Pour mettre en place ce connecteur, on utlise le package SQLAlchemy.\n",
    "\n",
    "Suivant le type de base de données, on utilisera différents codes mais la structure du code est toujours la même"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# on importe l'outil de connexion\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "On crée une connexion\n",
    "```python\n",
    "connexion=create_engine(\"sqlite:///(...).sqlite\")\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "On utlise une des fonctions de Pandas pour charger les données\n",
    "```python\n",
    "requete=\"\"\"select ... from ...\"\"\"\n",
    "frame_sql=pd.read_sql_query(requete,connexion)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**Exercices :**\n",
    "    \n",
    "Importez la base de données SQLite salaries et récupérez la table Salaries dans un DataFrame \n",
    "\n",
    "*Indice :* On commence par créer la connexion et ensuite on fait la requête `select * from salaries`, la connexion se fait en utilisant l'adresse : \"sqlite:///./data/salaries.sqlite\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "connexion=create_engine(\"sqlite:///./Data/salaries.sqlite\")\n",
    "salaries = pd.read_sql_query(\"select * from salaries\", connexion)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Bravo !\n"
     ]
    }
   ],
   "source": [
    "# pour vérifier que vous avez bien répondu à l'exercice, soumettre cette cellule\n",
    "# on utilise maj + Entrée\n",
    "assert type(salaries) == pd.DataFrame\n",
    "print(\"Bravo !\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Les tris avec Pandas \n",
    "\n",
    "Pour effectuer des tris, on utilise :\n",
    "- `.sort_index()` pour le tri des index\n",
    "- `.sort_values()` pour le tri des données\n",
    "- `.rank()` affiche le rang des observations\n",
    "\n",
    "Il peut y avoir plusieurs tris dans la même opération. Dans ce cas, on utilise des listes de colonnes :\n",
    "```python\n",
    "frame.sort_values([\"col_1\",\"col_2\"])\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**Exercice :** \n",
    "    \n",
    "Triez les données sur les salaires en se basant sur le BasePay et le JobTitle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "salaries_sorted = salaries.sort_values([\"JobTitle\",\"BasePay\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Bravo !\n"
     ]
    }
   ],
   "source": [
    "# pour vérifier que vous avez bien répondu à l'exercice, soumettre cette cellule\n",
    "# on utilise maj + Entrée\n",
    "assert salaries_sorted[\"JobTitle\"].min() == salaries_sorted[\"JobTitle\"].iloc[0]\n",
    "print(\"Bravo !\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exportez des fichiers \n",
    "\n",
    "La plupart des outils d'importation existent en exportation, on utilise :\n",
    "```\n",
    "frame.to_csv(\"fichier.csv\")\n",
    "```\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercice :**\n",
    "    \n",
    "Exportez les données salaries dans un fichier Excel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "salaries_sorted.to_excel(\"./Data/salaries.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Les statistiques simples\n",
    "\n",
    "Les Dataframe possèdent de nombreuses méthodes pour calculer des statistiques simples :\n",
    "- `.sum(axis=0)` permet de faire une somme par colonne\n",
    "- `.sum(axis=1)` permet de faire une somme par ligne\n",
    "- `.min()` et `.max()` donnent le minimum par colonne\n",
    "- `.idxmin()` et `.idxmax()` donnent l’index du minimum et du maximum\n",
    "- `.describe()` affiche un tableau de statistiques descriptives par colonne\n",
    "- `.corr()` pour calculer la corrélation entre les colonnes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**Exercice :**\n",
    "    \n",
    "Obtenir les moyennes et variances de la colonne `TotalPay` pour les données Salaries.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "74768.32197169267\n"
     ]
    }
   ],
   "source": [
    "print(salaries[\"TotalPay\"].mean())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "mean    7.476832e+04\n",
       "var     2.551968e+09\n",
       "Name: TotalPay, dtype: float64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salaries[\"TotalPay\"].agg([\"mean\", \"var\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Le traitement des données manquantes\n",
    "\n",
    "- Les données manquantes sont identifiées par `NaN`\n",
    "\n",
    "\n",
    "- `.dropna()` permet de retirer les données manquantes dans un objet Series et l’ensemble d’une ligne dans le cas d’un DataFrame\n",
    "- Pour éliminer par colonne, on utilise `.dropna(axis=1)`\n",
    "- Remplacer toutes les données manquantes `.fillna(valeur)`\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Les jointures avec Pandas\n",
    "\n",
    "On veut joindre des jeux de données en utilisant des clés (variables communes)\n",
    "\n",
    "- `pd.merge()` permet de joindre deux DataFrame, on utilise comme options `on='key'`\n",
    "\n",
    "- On peut utiliser comme option `how=`, on peut avoir :\n",
    "    - `left` dans ce cas, on garde le jeu de données à gauche et pour les données de droite des valeurs manquantes sont ajoutées.\n",
    "    - `outer`, on garde toutes les valeurs des deux jeux de données\n",
    "    - ...\n",
    "\n",
    "- On peut avoir plusieurs clés et faire une jointure sur les deux clés `on=['key1','key2']`\n",
    "\n",
    "Pour plus de détails : <http://pandas.pydata.org/pandas-docs/dev/generated/pandas.DataFrame.merge.html>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**Exercice :**\n",
    "    \n",
    "Joindre deux dataframes (credit1 et credit2). On commence par importer `credit1.txt`\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "credit1 = pd.read_table(\"./Data/credit1.txt\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "credit_merged = pd.merge(credit1,credit2,on=\"Customer_ID\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Bravo !\n"
     ]
    }
   ],
   "source": [
    "# pour vérifier que vous avez bien répondu à l'exercice, soumettre cette cellule\n",
    "# on utilise maj + Entrée\n",
    "assert credit_merged.shape[1] == 31\n",
    "print(\"Bravo !\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Gestion des duplications\n",
    "\n",
    "- On utilise `.duplicated()` ou `.drop_duplicates()` dans le cas où on désire effacer les lignes se répétant\n",
    "\n",
    "\n",
    "- On peut se concentrer sur une seule variables en entrant directement le nom de la variable. Dans ce cas, c’est la première apparition qui compte. Si on veut prendre la dernière apparition, on utilise l’option `keep=\"last\"`. On pourra avoir :\n",
    "```python\n",
    "frame1.drop_duplicates([\"col_0\",\"col_1\"],keep=\"last\")\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Les tableaux croisés avec Pandas\n",
    "\n",
    "Les DataFrame possèdent des méthodes pour générer des tableaux croisés, notamment :\n",
    "```python\n",
    "frame1.pivot_table()\n",
    "```\n",
    "Cette méthode permet de gérer de nombreux cas avec des fonctions standards et sur mesure."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# L'utilisation de GroupBy sur des DataFrame\n",
    "\n",
    "- `.groupby` permet de rassembler des observations en fonction d’une variable dite de groupe\n",
    "\n",
    "\n",
    "- Par exemple, `frame.groupby('X').mean()` donnera les moyennes par groupes de `X`\n",
    "\n",
    "\n",
    "- On peut aussi utiliser `.size()` pour connaître la taille des groupes et utiliser d’autres fonctions (`.sum()`)\n",
    "\n",
    "\n",
    "- On peut effectuer de nombreuses opérations de traitement avec le groupby\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "**Exercice :**\n",
    "    \n",
    "- Données sur les salaires\n",
    "\n",
    "\n",
    "- On utilise le `groupby()` pour rassembler les types d’emploi\n",
    "\n",
    "\n",
    "- Et on calcule des statistiques pour chaque type\n",
    "\n",
    "\n",
    "On peut utiliser la méthode `.agg()` avec par exemple `'mean'` comme paramètre\n",
    "\n",
    "On utilise aussi fréquemment la méthode `.apply()` combinée à une fonction lambda"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "JobTitle\n",
       "GENERAL MANAGER-METROPOLITAN TRANSIT AUTHORITY    399211.275000\n",
       "Chief Investment Officer                          339653.700000\n",
       "Chief of Police                                   329183.646667\n",
       "Chief, Fire Department                            325971.683333\n",
       "DEPUTY DIRECTOR OF INVESTMENTS                    307899.460000\n",
       "                                                      ...      \n",
       "Commissioner 16.700c, No Pay                          25.760000\n",
       "IS Technician Assistant                                0.000000\n",
       "Not provided                                           0.000000\n",
       "Public Safety Comm Tech                                0.000000\n",
       "Drug Court Coordinator                                 0.000000\n",
       "Name: TotalPay, Length: 2159, dtype: float64"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "salaries.groupby(\"JobTitle\")[\"TotalPay\"].mean().sort_values(ascending = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "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
}