08-Análise de Dados.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Análise de Dados"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## NumPy "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Definições"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A biblioteca `NumPy` é a biblioteca padrão para computação científica com Python pois ela fornece uma estrutura de dados chamado `ndarray` que permite realizar cálculos multidimensional de forma rápida e eficiente. Por esta razão, muitas bibliotecas de análise e visualização de dados foram construídas usando recursos do `NumPy`.\n",
    "\n",
    "Por convenção, importamos a biblioteca `NumPy` da seguinte forma: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Para criarmos um objeto do tipo `ndarray` basta chamarmos a função `np.array` e passar uma lista de números."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[1 2 3]\n"
     ]
    }
   ],
   "source": [
    "arr = np.array([1,2,3])\n",
    "\n",
    "print(arr)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ao imprimir o tipo, vemos que é do tipo `numpy.ndarray`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'numpy.ndarray'>\n"
     ]
    }
   ],
   "source": [
    "print(type(arr))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "E o método `shape` nos mostra a dimensão do `array`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(3,)\n"
     ]
    }
   ],
   "source": [
    "print(arr.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "E podemos acessar os dados da mesma forma que fazemos com uma lista"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 2 3\n"
     ]
    }
   ],
   "source": [
    "print(arr[0], arr[1], arr[2])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Também é possível substituir uma entrada no nosso `array` da mesma forma que fazemos com listas:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[1 5 3]\n"
     ]
    }
   ],
   "source": [
    "arr[1] = 5\n",
    "print(arr)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Podemos criar um `array` bidimensional: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[ 1  2  3  4]\n",
      " [ 5  6  7  8]\n",
      " [ 9 10 11 12]]\n"
     ]
    }
   ],
   "source": [
    "arr = np.array(\n",
    "    [\n",
    "        [1,2,3,4], \n",
    "        [5,6,7,8], \n",
    "        [9,10,11,12]\n",
    "    ]\n",
    ")\n",
    "print(arr)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "E o `shape` deste `array` é `3x4`: 3 linhas e 4 colunas."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(3, 4)\n"
     ]
    }
   ],
   "source": [
    "print(arr.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "O `array` foi construído para facilitar o acesso a seus elementos:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[5 6 7 8]\n"
     ]
    }
   ],
   "source": [
    "print(arr[1,:]) # Obtém segunda linha"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[ 2  6 10]\n"
     ]
    }
   ],
   "source": [
    "print(arr[:,1]) # Obtém segunda coluna"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[ 8 12]\n"
     ]
    }
   ],
   "source": [
    "print(arr[1:, 3]) # Obtém elementos da segunda até a última linha da quarta coluna"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[ 2 10]\n"
     ]
    }
   ],
   "source": [
    "print(arr[[0,2], [1]]) # Obtém elementos da primeira e terceira linhas da segunda coluna"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "É possível criar um `array` booleano para usar como máscara para acessar o nosso `array`: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[False  True False  True]\n",
      " [False  True False  True]\n",
      " [False  True False  True]]\n",
      "[ 2  4  6  8 10 12]\n"
     ]
    }
   ],
   "source": [
    "mask = arr % 2 == 0\n",
    "print(mask)\n",
    "print(arr[mask])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Este é um recurso útil para filtrar valores do `array`. E podemos substituir valores baseados em uma condição:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[ 1  0  3  0]\n",
      " [ 5  0  7  0]\n",
      " [ 9  0 11  0]]\n"
     ]
    }
   ],
   "source": [
    "arr[mask] = 0\n",
    "print(arr)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Em um `array`, todos os elementos são do mesmo tipo:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('int64')"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "arr.dtype"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "E ao tentar introduzir um elemento que não é do tipo definido pelo `array`, ele tentará ser convertido"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[ 1  0  3  0]\n",
      " [ 5  2  7  0]\n",
      " [ 9  0 11  0]]\n"
     ]
    }
   ],
   "source": [
    "arr[1,1] = 2.3\n",
    "print(arr)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se tentarmos incluir um tipo que não possa ser convertido, obteremos um erro:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "tags": [
     "raises-exception"
    ]
   },
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "invalid literal for int() with base 10: 'oi'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-17-6ba5ac2c24ee>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0marr\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m'oi'\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mValueError\u001b[0m: invalid literal for int() with base 10: 'oi'"
     ]
    }
   ],
   "source": [
    "arr[1,1] = 'oi'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Operações"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "O `NumPy` foi construído visando acelerar operações matemáticas entre `array`s. Podemos ver as operações básicas que são aplicadas elemento a elemento (*element wise*):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[ 6.  8.]\n",
      " [10. 12.]]\n",
      "[[ 6.  8.]\n",
      " [10. 12.]]\n"
     ]
    }
   ],
   "source": [
    "x = np.array([[1,2],[3,4]], dtype=np.float64)\n",
    "y = np.array([[5,6],[7,8]], dtype=np.float64)\n",
    "\n",
    "\n",
    "print(x+y)\n",
    "print(np.add(x,y))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[-4. -4.]\n",
      " [-4. -4.]]\n",
      "[[-4. -4.]\n",
      " [-4. -4.]]\n"
     ]
    }
   ],
   "source": [
    "print(x-y)\n",
    "print(np.subtract(x,y))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[ 5. 12.]\n",
      " [21. 32.]]\n",
      "[[ 5. 12.]\n",
      " [21. 32.]]\n"
     ]
    }
   ],
   "source": [
    "print(x*y)\n",
    "print(np.multiply(x,y))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[0.2        0.33333333]\n",
      " [0.42857143 0.5       ]]\n",
      "[[0.2        0.33333333]\n",
      " [0.42857143 0.5       ]]\n"
     ]
    }
   ],
   "source": [
    "print(x/y)\n",
    "print(np.divide(x,y))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Também é possível aplicar uma função a todos os elementos com uma simples chamada de função. Por exemplo, aqui está uma operação de raiz quadrada:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[1.        , 1.41421356],\n",
       "       [1.73205081, 2.        ]])"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.sqrt(x)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Também podemos realizar algumas operações em um determinado eixo:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[1., 2.],\n",
       "       [3., 4.]])"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10.0\n",
      "[4. 6.]\n",
      "[3. 7.]\n"
     ]
    }
   ],
   "source": [
    "print(np.sum(x)) # soma todos os elementos\n",
    "print(np.sum(x, axis=0)) # soma as linhas\n",
    "print(np.sum(x, axis=1)) # soma as colunas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "E podemos realizar operações matemáticas em `array`s de tamanhos diferentes. O `NumPy` *estica* o `array` menor para ter as mesmas dimensões que o `array` maior para realizar a operação. A figura abaixo obtida do livro [Python Data Science Handbook](https://jakevdp.github.io/PythonDataScienceHandbook/02.05-computation-on-arrays-broadcasting.html) ilustra bem como funciona\n",
    "\n",
    "![Ref: Python Data Science Handbook](https://jakevdp.github.io/PythonDataScienceHandbook/figures/02.05-broadcasting.png)\n",
    "\n",
    "Vamos ver na prática:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[[ 2  2  4]\n",
      " [ 5  5  7]\n",
      " [ 8  8 10]\n",
      " [11 11 13]]\n"
     ]
    }
   ],
   "source": [
    "x = np.array([[1,2,3], [4,5,6], [7,8,9], [10, 11, 12]])\n",
    "v = np.array([1, 0, 1])\n",
    "\n",
    "print(x + v)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SciPy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`SciPy` é uma biblioteca com recursos matemáticos que extende as funcionalidades do `NumPy`, como por exemplo:\n",
    "\n",
    "* [Álgebra linear](https://docs.scipy.org/doc/scipy-1.4.1/reference/linalg.html#module-scipy.linalg)\n",
    "* [Processamento de imagem](https://docs.scipy.org/doc/scipy-1.4.1/reference/ndimage.html#module-scipy.ndimage)\n",
    "* [Tranformadas de Fourier](https://docs.scipy.org/doc/scipy-1.4.1/reference/fftpack.html#module-scipy.fftpack)\n",
    "* [Integradores de equações diferenciais](https://docs.scipy.org/doc/scipy-1.4.1/reference/integrate.html#module-scipy.integrate)\n",
    "* [Interpoladores](https://docs.scipy.org/doc/scipy-1.4.1/reference/interpolate.html#module-scipy.interpolate)\n",
    "\n",
    "Não veremos estes recursos aqui, mas é interessante saber que eles existem caso seja necessário realizar alguma computação matricial mais aprimorada."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Definição"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "O Pandas é uma biblioteca para análise de dados. Com ela, conseguimos trabalhar em algumas das principais etapas do processo de Ciência de Dados:\n",
    "\n",
    "* Importação\n",
    "* Manipulação\n",
    "* Visualização\n",
    "\n",
    "Esta biblioetca insere dois tipos de estruturas de dados:\n",
    "\n",
    "* A `Series` é um `array` unidimensional rotulado que suporta vários tipos de dados.\n",
    "* O `DataFrame` é um `array` bidimensional também rotulado.\n",
    "\n",
    "Ambas estruturas aceitam quase todos os métods do `NumPy`.\n",
    "\n",
    "Uma `Series` pode ser construída da seguinte forma:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0        carro\n",
       "1         moto\n",
       "2    bicicleta\n",
       "dtype: object"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "series = pd.Series(['carro','moto','bicicleta'])\n",
    "series"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Veja que a representação da `Series` já apresenta o tipo do dado armazenado. O tipo `object` refere-se, na maioria das vezes, ao tipo `str` mas pode significar que há uma mistura de tipos.\n",
    "\n",
    "E podemos acessar o elemento pelo índice:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'bicicleta'"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "series[2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Se passarmos um dicionário como argumento, as chaves destes dicionários serão os índices:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "João     1\n",
       "Maria    2\n",
       "José     3\n",
       "dtype: int64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "series = pd.Series({'João':1,'Maria':2,'José':3})\n",
    "series"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "O `DataFrame` foi baseado na estrutura de mesmo nome do R e podemos inicializar uma da seguinte forma:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>João</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Maria</td>\n",
       "      <td>2</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>José</td>\n",
       "      <td>3</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       A  B      C\n",
       "0   João  1  False\n",
       "1  Maria  2   True\n",
       "2   José  3   True"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame({'A':['João', 'Maria', 'José'], 'B':[1,2,3], 'C':[False, True, True]})\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Neste caso, as chaves do dicionário passado como argumento são os nomes das colunas. \n",
    "\n",
    "Acessamos os elementos de uma coluna da seguinte forma:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     João\n",
       "1    Maria\n",
       "2     José\n",
       "Name: A, dtype: object"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['A']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Como você já deve ter notado, o resultado é uma `Series`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.series.Series"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(df['A'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Então podemos dizer que um `DataFrame` é uma coleção de `Series`.\n",
    "\n",
    "Para acessar uma entrada específica usamos a propriedade `loc` com acesso por índice:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'José'"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[2,'A']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Como acessaríamos apenas uma linha?\n",
    ">\n",
    "> E se quiséssemos acessar uma coluna/linha por sua posição ao invés do índice? Como faríamos?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Leitura de arquivos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "O Pandas possui diversos métodos para ingestão de dados e todas elas são do formato `pd.read_*`. Por exemplo, para ler um arquivo CSV podemos fazer o seguinte:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>survived</th>\n",
       "      <th>pclass</th>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "      <th>sibsp</th>\n",
       "      <th>parch</th>\n",
       "      <th>fare</th>\n",
       "      <th>embarked</th>\n",
       "      <th>class</th>\n",
       "      <th>who</th>\n",
       "      <th>adult_male</th>\n",
       "      <th>deck</th>\n",
       "      <th>embark_town</th>\n",
       "      <th>alive</th>\n",
       "      <th>alone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>male</td>\n",
       "      <td>22.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7.2500</td>\n",
       "      <td>S</td>\n",
       "      <td>Third</td>\n",
       "      <td>man</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>no</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>38.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>71.2833</td>\n",
       "      <td>C</td>\n",
       "      <td>First</td>\n",
       "      <td>woman</td>\n",
       "      <td>False</td>\n",
       "      <td>C</td>\n",
       "      <td>Cherbourg</td>\n",
       "      <td>yes</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>female</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7.9250</td>\n",
       "      <td>S</td>\n",
       "      <td>Third</td>\n",
       "      <td>woman</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>yes</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>35.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>53.1000</td>\n",
       "      <td>S</td>\n",
       "      <td>First</td>\n",
       "      <td>woman</td>\n",
       "      <td>False</td>\n",
       "      <td>C</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>yes</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>male</td>\n",
       "      <td>35.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>8.0500</td>\n",
       "      <td>S</td>\n",
       "      <td>Third</td>\n",
       "      <td>man</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>no</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>886</th>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>male</td>\n",
       "      <td>27.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>13.0000</td>\n",
       "      <td>S</td>\n",
       "      <td>Second</td>\n",
       "      <td>man</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>no</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>887</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>19.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30.0000</td>\n",
       "      <td>S</td>\n",
       "      <td>First</td>\n",
       "      <td>woman</td>\n",
       "      <td>False</td>\n",
       "      <td>B</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>yes</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>888</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>female</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>23.4500</td>\n",
       "      <td>S</td>\n",
       "      <td>Third</td>\n",
       "      <td>woman</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>no</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>889</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>male</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30.0000</td>\n",
       "      <td>C</td>\n",
       "      <td>First</td>\n",
       "      <td>man</td>\n",
       "      <td>True</td>\n",
       "      <td>C</td>\n",
       "      <td>Cherbourg</td>\n",
       "      <td>yes</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>890</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>male</td>\n",
       "      <td>32.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7.7500</td>\n",
       "      <td>Q</td>\n",
       "      <td>Third</td>\n",
       "      <td>man</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Queenstown</td>\n",
       "      <td>no</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>891 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     survived  pclass     sex   age  sibsp  parch     fare embarked   class  \\\n",
       "0           0       3    male  22.0      1      0   7.2500        S   Third   \n",
       "1           1       1  female  38.0      1      0  71.2833        C   First   \n",
       "2           1       3  female  26.0      0      0   7.9250        S   Third   \n",
       "3           1       1  female  35.0      1      0  53.1000        S   First   \n",
       "4           0       3    male  35.0      0      0   8.0500        S   Third   \n",
       "..        ...     ...     ...   ...    ...    ...      ...      ...     ...   \n",
       "886         0       2    male  27.0      0      0  13.0000        S  Second   \n",
       "887         1       1  female  19.0      0      0  30.0000        S   First   \n",
       "888         0       3  female   NaN      1      2  23.4500        S   Third   \n",
       "889         1       1    male  26.0      0      0  30.0000        C   First   \n",
       "890         0       3    male  32.0      0      0   7.7500        Q   Third   \n",
       "\n",
       "       who  adult_male deck  embark_town alive  alone  \n",
       "0      man        True  NaN  Southampton    no  False  \n",
       "1    woman       False    C    Cherbourg   yes  False  \n",
       "2    woman       False  NaN  Southampton   yes   True  \n",
       "3    woman       False    C  Southampton   yes  False  \n",
       "4      man        True  NaN  Southampton    no   True  \n",
       "..     ...         ...  ...          ...   ...    ...  \n",
       "886    man        True  NaN  Southampton    no   True  \n",
       "887  woman       False    B  Southampton   yes   True  \n",
       "888  woman       False  NaN  Southampton    no  False  \n",
       "889    man        True    C    Cherbourg   yes   True  \n",
       "890    man        True  NaN   Queenstown    no   True  \n",
       "\n",
       "[891 rows x 15 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Obtém os dados do Titanic\n",
    "from urllib.request import urlretrieve\n",
    "\n",
    "urlretrieve(\n",
    "    'https://raw.githubusercontent.com/mwaskom/seaborn-data/master/titanic.csv', \n",
    "    'Dados/titanic.csv'\n",
    ")\n",
    "\n",
    "# Carrega os dados para um DataFrame\n",
    "df_titanic = pd.read_csv('Dados/titanic.csv')\n",
    "df_titanic"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Apesar do Jupyter Notebook apresentar uma versão compactada dos resultados, a tabela completa foi importada."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "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>TrackId</th>\n",
       "      <th>Name</th>\n",
       "      <th>AlbumId</th>\n",
       "      <th>MediaTypeId</th>\n",
       "      <th>GenreId</th>\n",
       "      <th>Composer</th>\n",
       "      <th>Milliseconds</th>\n",
       "      <th>Bytes</th>\n",
       "      <th>UnitPrice</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>For Those About To Rock (We Salute You)</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Angus Young, Malcolm Young, Brian Johnson</td>\n",
       "      <td>343719</td>\n",
       "      <td>11170334</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Balls to the Wall</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>342562</td>\n",
       "      <td>5510424</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Fast As a Shark</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>F. Baltes, S. Kaufman, U. Dirkscneider &amp; W. Ho...</td>\n",
       "      <td>230619</td>\n",
       "      <td>3990994</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Restless and Wild</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. D...</td>\n",
       "      <td>252051</td>\n",
       "      <td>4331779</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Princess of the Dawn</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>Deaffy &amp; R.A. Smith-Diesel</td>\n",
       "      <td>375418</td>\n",
       "      <td>6290521</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3498</th>\n",
       "      <td>3499</td>\n",
       "      <td>Pini Di Roma (Pinien Von Rom) \\ I Pini Della V...</td>\n",
       "      <td>343</td>\n",
       "      <td>2</td>\n",
       "      <td>24</td>\n",
       "      <td>None</td>\n",
       "      <td>286741</td>\n",
       "      <td>4718950</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3499</th>\n",
       "      <td>3500</td>\n",
       "      <td>String Quartet No. 12 in C Minor, D. 703 \"Quar...</td>\n",
       "      <td>344</td>\n",
       "      <td>2</td>\n",
       "      <td>24</td>\n",
       "      <td>Franz Schubert</td>\n",
       "      <td>139200</td>\n",
       "      <td>2283131</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3500</th>\n",
       "      <td>3501</td>\n",
       "      <td>L'orfeo, Act 3, Sinfonia (Orchestra)</td>\n",
       "      <td>345</td>\n",
       "      <td>2</td>\n",
       "      <td>24</td>\n",
       "      <td>Claudio Monteverdi</td>\n",
       "      <td>66639</td>\n",
       "      <td>1189062</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3501</th>\n",
       "      <td>3502</td>\n",
       "      <td>Quintet for Horn, Violin, 2 Violas, and Cello ...</td>\n",
       "      <td>346</td>\n",
       "      <td>2</td>\n",
       "      <td>24</td>\n",
       "      <td>Wolfgang Amadeus Mozart</td>\n",
       "      <td>221331</td>\n",
       "      <td>3665114</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3502</th>\n",
       "      <td>3503</td>\n",
       "      <td>Koyaanisqatsi</td>\n",
       "      <td>347</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>Philip Glass</td>\n",
       "      <td>206005</td>\n",
       "      <td>3305164</td>\n",
       "      <td>0.99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3503 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      TrackId                                               Name  AlbumId  \\\n",
       "0           1            For Those About To Rock (We Salute You)        1   \n",
       "1           2                                  Balls to the Wall        2   \n",
       "2           3                                    Fast As a Shark        3   \n",
       "3           4                                  Restless and Wild        3   \n",
       "4           5                               Princess of the Dawn        3   \n",
       "...       ...                                                ...      ...   \n",
       "3498     3499  Pini Di Roma (Pinien Von Rom) \\ I Pini Della V...      343   \n",
       "3499     3500  String Quartet No. 12 in C Minor, D. 703 \"Quar...      344   \n",
       "3500     3501               L'orfeo, Act 3, Sinfonia (Orchestra)      345   \n",
       "3501     3502  Quintet for Horn, Violin, 2 Violas, and Cello ...      346   \n",
       "3502     3503                                      Koyaanisqatsi      347   \n",
       "\n",
       "      MediaTypeId  GenreId                                           Composer  \\\n",
       "0               1        1          Angus Young, Malcolm Young, Brian Johnson   \n",
       "1               2        1                                               None   \n",
       "2               2        1  F. Baltes, S. Kaufman, U. Dirkscneider & W. Ho...   \n",
       "3               2        1  F. Baltes, R.A. Smith-Diesel, S. Kaufman, U. D...   \n",
       "4               2        1                         Deaffy & R.A. Smith-Diesel   \n",
       "...           ...      ...                                                ...   \n",
       "3498            2       24                                               None   \n",
       "3499            2       24                                     Franz Schubert   \n",
       "3500            2       24                                 Claudio Monteverdi   \n",
       "3501            2       24                            Wolfgang Amadeus Mozart   \n",
       "3502            2       10                                       Philip Glass   \n",
       "\n",
       "      Milliseconds     Bytes  UnitPrice  \n",
       "0           343719  11170334       0.99  \n",
       "1           342562   5510424       0.99  \n",
       "2           230619   3990994       0.99  \n",
       "3           252051   4331779       0.99  \n",
       "4           375418   6290521       0.99  \n",
       "...            ...       ...        ...  \n",
       "3498        286741   4718950       0.99  \n",
       "3499        139200   2283131       0.99  \n",
       "3500         66639   1189062       0.99  \n",
       "3501        221331   3665114       0.99  \n",
       "3502        206005   3305164       0.99  \n",
       "\n",
       "[3503 rows x 9 columns]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlite3\n",
    "import zipfile\n",
    "# Obtém banco de dados Chinook\n",
    "filename, response = urlretrieve('https://www.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip', 'Dados/chinook.zip')\n",
    "\n",
    "with zipfile.ZipFile(filename, 'r') as zip_ref:\n",
    "    zip_ref.extractall('Dados/')\n",
    "\n",
    "conn = sqlite3.connect('Dados/chinook.db')\n",
    "df_tracks = pd.read_sql('SELECT * FROM tracks', conn)\n",
    "conn.close()\n",
    "df_tracks"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Assim, para ler os dados de um banco de dados, precisamos apenas passar uma conexão válida e a `query` que queremos realizar.\n",
    "\n",
    "Outros formas de importar dados pode ser visto [aqui](https://pandas.pydata.org/pandas-docs/stable/reference/io.html).\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Estatśtica Descritiva"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As estruturas de dados do Pandas possuem alguns métodos disponíveis que facilitam a obtenção de aglumas estatísticas. Para uma visão global nós podemos usar o método `.describe`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>survived</th>\n",
       "      <th>pclass</th>\n",
       "      <th>age</th>\n",
       "      <th>sibsp</th>\n",
       "      <th>parch</th>\n",
       "      <th>fare</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>891.000000</td>\n",
       "      <td>891.000000</td>\n",
       "      <td>714.000000</td>\n",
       "      <td>891.000000</td>\n",
       "      <td>891.000000</td>\n",
       "      <td>891.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>0.383838</td>\n",
       "      <td>2.308642</td>\n",
       "      <td>29.699118</td>\n",
       "      <td>0.523008</td>\n",
       "      <td>0.381594</td>\n",
       "      <td>32.204208</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.486592</td>\n",
       "      <td>0.836071</td>\n",
       "      <td>14.526497</td>\n",
       "      <td>1.102743</td>\n",
       "      <td>0.806057</td>\n",
       "      <td>49.693429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.420000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>20.125000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>7.910400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>28.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>14.454200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>38.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>31.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>80.000000</td>\n",
       "      <td>8.000000</td>\n",
       "      <td>6.000000</td>\n",
       "      <td>512.329200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         survived      pclass         age       sibsp       parch        fare\n",
       "count  891.000000  891.000000  714.000000  891.000000  891.000000  891.000000\n",
       "mean     0.383838    2.308642   29.699118    0.523008    0.381594   32.204208\n",
       "std      0.486592    0.836071   14.526497    1.102743    0.806057   49.693429\n",
       "min      0.000000    1.000000    0.420000    0.000000    0.000000    0.000000\n",
       "25%      0.000000    2.000000   20.125000    0.000000    0.000000    7.910400\n",
       "50%      0.000000    3.000000   28.000000    0.000000    0.000000   14.454200\n",
       "75%      1.000000    3.000000   38.000000    1.000000    0.000000   31.000000\n",
       "max      1.000000    3.000000   80.000000    8.000000    6.000000  512.329200"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_titanic.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ao invés de obter uma tabela descritiva com várias estatísticas como a apresentada acima, podemos invocar métodos específicos:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "survived       0.383838\n",
       "pclass         2.308642\n",
       "age           29.699118\n",
       "sibsp          0.523008\n",
       "parch          0.381594\n",
       "fare          32.204208\n",
       "adult_male     0.602694\n",
       "alone          0.602694\n",
       "dtype: float64"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_titanic.mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Temos outros métodos disponíveis como `median`, `min` e `max`."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Manuseio de dados"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Uma tarefa bem comum é a a limpeza e alteração dos dados. Por exemplo, podemos ter valores monetários negativos ou precisamos criar colunas novas. Com o Pandas, podemos realizar estas tarefas com facilidade.\n",
    "\n",
    "Por exemplo, os dados do Titanic apresentam alguns valores ausentes (`null`)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "survived         0\n",
       "pclass           0\n",
       "sex              0\n",
       "age            177\n",
       "sibsp            0\n",
       "parch            0\n",
       "fare             0\n",
       "embarked         2\n",
       "class            0\n",
       "who              0\n",
       "adult_male       0\n",
       "deck           688\n",
       "embark_town      2\n",
       "alive            0\n",
       "alone            0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_titanic.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Existem duas formas de lidar com dados ausentes:\n",
    "1. Removê-los, ou\n",
    "1. Imputá-los com a média/mediana ou algum outro método sofisticado.\n",
    "\n",
    "Vamos seguir com o método 1 e remover os dados nulos:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "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>survived</th>\n",
       "      <th>pclass</th>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "      <th>sibsp</th>\n",
       "      <th>parch</th>\n",
       "      <th>fare</th>\n",
       "      <th>embarked</th>\n",
       "      <th>class</th>\n",
       "      <th>who</th>\n",
       "      <th>adult_male</th>\n",
       "      <th>deck</th>\n",
       "      <th>embark_town</th>\n",
       "      <th>alive</th>\n",
       "      <th>alone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>38.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>71.2833</td>\n",
       "      <td>C</td>\n",
       "      <td>First</td>\n",
       "      <td>woman</td>\n",
       "      <td>False</td>\n",
       "      <td>C</td>\n",
       "      <td>Cherbourg</td>\n",
       "      <td>yes</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>35.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>53.1000</td>\n",
       "      <td>S</td>\n",
       "      <td>First</td>\n",
       "      <td>woman</td>\n",
       "      <td>False</td>\n",
       "      <td>C</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>yes</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>male</td>\n",
       "      <td>54.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>51.8625</td>\n",
       "      <td>S</td>\n",
       "      <td>First</td>\n",
       "      <td>man</td>\n",
       "      <td>True</td>\n",
       "      <td>E</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>no</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>female</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>16.7000</td>\n",
       "      <td>S</td>\n",
       "      <td>Third</td>\n",
       "      <td>child</td>\n",
       "      <td>False</td>\n",
       "      <td>G</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>yes</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>58.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>26.5500</td>\n",
       "      <td>S</td>\n",
       "      <td>First</td>\n",
       "      <td>woman</td>\n",
       "      <td>False</td>\n",
       "      <td>C</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>yes</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>871</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>47.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>52.5542</td>\n",
       "      <td>S</td>\n",
       "      <td>First</td>\n",
       "      <td>woman</td>\n",
       "      <td>False</td>\n",
       "      <td>D</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>yes</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>872</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>male</td>\n",
       "      <td>33.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5.0000</td>\n",
       "      <td>S</td>\n",
       "      <td>First</td>\n",
       "      <td>man</td>\n",
       "      <td>True</td>\n",
       "      <td>B</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>no</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>879</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>56.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>83.1583</td>\n",
       "      <td>C</td>\n",
       "      <td>First</td>\n",
       "      <td>woman</td>\n",
       "      <td>False</td>\n",
       "      <td>C</td>\n",
       "      <td>Cherbourg</td>\n",
       "      <td>yes</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>887</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>19.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30.0000</td>\n",
       "      <td>S</td>\n",
       "      <td>First</td>\n",
       "      <td>woman</td>\n",
       "      <td>False</td>\n",
       "      <td>B</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>yes</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>889</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>male</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>30.0000</td>\n",
       "      <td>C</td>\n",
       "      <td>First</td>\n",
       "      <td>man</td>\n",
       "      <td>True</td>\n",
       "      <td>C</td>\n",
       "      <td>Cherbourg</td>\n",
       "      <td>yes</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>182 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     survived  pclass     sex   age  sibsp  parch     fare embarked  class  \\\n",
       "1           1       1  female  38.0      1      0  71.2833        C  First   \n",
       "3           1       1  female  35.0      1      0  53.1000        S  First   \n",
       "6           0       1    male  54.0      0      0  51.8625        S  First   \n",
       "10          1       3  female   4.0      1      1  16.7000        S  Third   \n",
       "11          1       1  female  58.0      0      0  26.5500        S  First   \n",
       "..        ...     ...     ...   ...    ...    ...      ...      ...    ...   \n",
       "871         1       1  female  47.0      1      1  52.5542        S  First   \n",
       "872         0       1    male  33.0      0      0   5.0000        S  First   \n",
       "879         1       1  female  56.0      0      1  83.1583        C  First   \n",
       "887         1       1  female  19.0      0      0  30.0000        S  First   \n",
       "889         1       1    male  26.0      0      0  30.0000        C  First   \n",
       "\n",
       "       who  adult_male deck  embark_town alive  alone  \n",
       "1    woman       False    C    Cherbourg   yes  False  \n",
       "3    woman       False    C  Southampton   yes  False  \n",
       "6      man        True    E  Southampton    no   True  \n",
       "10   child       False    G  Southampton   yes  False  \n",
       "11   woman       False    C  Southampton   yes   True  \n",
       "..     ...         ...  ...          ...   ...    ...  \n",
       "871  woman       False    D  Southampton   yes  False  \n",
       "872    man        True    B  Southampton    no   True  \n",
       "879  woman       False    C    Cherbourg   yes  False  \n",
       "887  woman       False    B  Southampton   yes   True  \n",
       "889    man        True    C    Cherbourg   yes   True  \n",
       "\n",
       "[182 rows x 15 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_titanic_null_dropped = df_titanic.dropna()\n",
    "df_titanic_null_dropped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "survived       0\n",
       "pclass         0\n",
       "sex            0\n",
       "age            0\n",
       "sibsp          0\n",
       "parch          0\n",
       "fare           0\n",
       "embarked       0\n",
       "class          0\n",
       "who            0\n",
       "adult_male     0\n",
       "deck           0\n",
       "embark_town    0\n",
       "alive          0\n",
       "alone          0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_titanic_null_dropped.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Desafio: Impute os dados ausentes para idade usando a média da idade dos passageiros."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Outro cenário possível é trocar o valor do dado. Por exemplo, podemos trocar os valores da coluna `alive` de `no/yes` para `não/sim` usando o método `replace` e passando um dicionário como argumento:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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>survived</th>\n",
       "      <th>pclass</th>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "      <th>sibsp</th>\n",
       "      <th>parch</th>\n",
       "      <th>fare</th>\n",
       "      <th>embarked</th>\n",
       "      <th>class</th>\n",
       "      <th>who</th>\n",
       "      <th>adult_male</th>\n",
       "      <th>deck</th>\n",
       "      <th>embark_town</th>\n",
       "      <th>alive</th>\n",
       "      <th>alone</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>male</td>\n",
       "      <td>22.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>7.2500</td>\n",
       "      <td>S</td>\n",
       "      <td>Third</td>\n",
       "      <td>man</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>não</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>38.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>71.2833</td>\n",
       "      <td>C</td>\n",
       "      <td>First</td>\n",
       "      <td>woman</td>\n",
       "      <td>False</td>\n",
       "      <td>C</td>\n",
       "      <td>Cherbourg</td>\n",
       "      <td>sim</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>female</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7.9250</td>\n",
       "      <td>S</td>\n",
       "      <td>Third</td>\n",
       "      <td>woman</td>\n",
       "      <td>False</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>sim</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>female</td>\n",
       "      <td>35.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>53.1000</td>\n",
       "      <td>S</td>\n",
       "      <td>First</td>\n",
       "      <td>woman</td>\n",
       "      <td>False</td>\n",
       "      <td>C</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>sim</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>male</td>\n",
       "      <td>35.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>8.0500</td>\n",
       "      <td>S</td>\n",
       "      <td>Third</td>\n",
       "      <td>man</td>\n",
       "      <td>True</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Southampton</td>\n",
       "      <td>não</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   survived  pclass     sex   age  sibsp  parch     fare embarked  class  \\\n",
       "0         0       3    male  22.0      1      0   7.2500        S  Third   \n",
       "1         1       1  female  38.0      1      0  71.2833        C  First   \n",
       "2         1       3  female  26.0      0      0   7.9250        S  Third   \n",
       "3         1       1  female  35.0      1      0  53.1000        S  First   \n",
       "4         0       3    male  35.0      0      0   8.0500        S  Third   \n",
       "\n",
       "     who  adult_male deck  embark_town alive  alone  \n",
       "0    man        True  NaN  Southampton   não  False  \n",
       "1  woman       False    C    Cherbourg   sim  False  \n",
       "2  woman       False  NaN  Southampton   sim   True  \n",
       "3  woman       False    C  Southampton   sim  False  \n",
       "4    man        True  NaN  Southampton   não   True  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_titanic['alive'].replace({'no':'não', 'yes':'sim'}, inplace=True)\n",
    "df_titanic.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Aqui introduzimos dois novos recursos:\n",
    "* o `inplace`, como argumento do `replace`, que faz a modificação diretamente no `DataFrame` de origem e não retorna um novo. Outros métodos possuem o argumento `inplace` como o `dropna` visto anteriormente.\n",
    "* O método `.head` que mostra as 5 primeiras linhas do `DataFrame`. Se passado um número inteiro como argumento, será mostrado esta mesma quantidade de linhas."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Adcionar uma coluna é bem simples:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>João</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Maria</td>\n",
       "      <td>2</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>José</td>\n",
       "      <td>3</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       A  B      C\n",
       "0   João  1  False\n",
       "1  Maria  2   True\n",
       "2   José  3   True"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>João</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>Carro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Maria</td>\n",
       "      <td>2</td>\n",
       "      <td>True</td>\n",
       "      <td>Moto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>José</td>\n",
       "      <td>3</td>\n",
       "      <td>True</td>\n",
       "      <td>Bicicleta</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       A  B      C          D\n",
       "0   João  1  False      Carro\n",
       "1  Maria  2   True       Moto\n",
       "2   José  3   True  Bicicleta"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['D'] = ['Carro', 'Moto', 'Bicicleta']\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> O que acontece quando tentamos criar uma coluna com um tamanho diferente ao de linhas?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Para criar uma linha, usamos o atributo `loc`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>João</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>Carro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Maria</td>\n",
       "      <td>2</td>\n",
       "      <td>True</td>\n",
       "      <td>Moto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>José</td>\n",
       "      <td>3</td>\n",
       "      <td>True</td>\n",
       "      <td>Bicicleta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Carlos</td>\n",
       "      <td>4</td>\n",
       "      <td>False</td>\n",
       "      <td>Ônibus</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        A  B      C          D\n",
       "0    João  1  False      Carro\n",
       "1   Maria  2   True       Moto\n",
       "2    José  3   True  Bicicleta\n",
       "3  Carlos  4  False     Ônibus"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[3] = ['Carlos', 4, False, 'Ônibus']\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Agrupamentos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Com o Pandas, podemos gerar agrupamento de forma similar a como fazemos com SQL. Por exemplo para obtermos a média da idade dos passageiros do Titanic por gênero, nós faríamos:\n",
    "\n",
    "```sql\n",
    "SELECT sex, mean(age)\n",
    "FROM titanic\n",
    "GROUP BY sex\n",
    "```\n",
    "\n",
    "No Pandas, a sintaxe é bem similar:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "sex\n",
       "female    27.915709\n",
       "male      30.726645\n",
       "Name: age, dtype: float64"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_titanic.groupby('sex')['age'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Para fazer um agrupamento por múltiplas colunas, basta passarmos uma lista com a colunas a serem agrupadas como argumento do `groupby`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pclass  sex   \n",
       "1       female    34.611765\n",
       "        male      41.281386\n",
       "2       female    28.722973\n",
       "        male      30.740707\n",
       "3       female    21.750000\n",
       "        male      26.507589\n",
       "Name: age, dtype: float64"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_titanic.groupby(['pclass','sex'])['age'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Também podemos executar múltipas funções no argupamento:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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></th>\n",
       "      <th>sum</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pclass</th>\n",
       "      <th>sex</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">1</th>\n",
       "      <th>female</th>\n",
       "      <td>2942.00</td>\n",
       "      <td>34.611765</td>\n",
       "      <td>13.612052</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>male</th>\n",
       "      <td>4169.42</td>\n",
       "      <td>41.281386</td>\n",
       "      <td>15.139570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2</th>\n",
       "      <th>female</th>\n",
       "      <td>2125.50</td>\n",
       "      <td>28.722973</td>\n",
       "      <td>12.872702</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>male</th>\n",
       "      <td>3043.33</td>\n",
       "      <td>30.740707</td>\n",
       "      <td>14.793894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">3</th>\n",
       "      <th>female</th>\n",
       "      <td>2218.50</td>\n",
       "      <td>21.750000</td>\n",
       "      <td>12.729964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>male</th>\n",
       "      <td>6706.42</td>\n",
       "      <td>26.507589</td>\n",
       "      <td>12.159514</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   sum       mean        std\n",
       "pclass sex                                  \n",
       "1      female  2942.00  34.611765  13.612052\n",
       "       male    4169.42  41.281386  15.139570\n",
       "2      female  2125.50  28.722973  12.872702\n",
       "       male    3043.33  30.740707  14.793894\n",
       "3      female  2218.50  21.750000  12.729964\n",
       "       male    6706.42  26.507589  12.159514"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_titanic.groupby(['pclass','sex'])['age'].agg([np.sum, np.mean, np.std])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Junção de tabelas"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Outra operação bem comum para quem está familiarizado com SQL é a junção de tabelas (*join*). Vamos criar dois `DataFrame`s para usarmos como exemplo:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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>class</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>C</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>D</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id class\n",
       "0   1     A\n",
       "1   2     A\n",
       "2   3     C\n",
       "3   4     B\n",
       "4   5     D"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left = pd.DataFrame(\n",
    "    {\n",
    "        'id':[1,2,3,4,5],\n",
    "        'class':['A', 'A', 'C', 'B', 'D']\n",
    "    }\n",
    ")\n",
    "left"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>valores</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>-3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  valores\n",
       "0   4       50\n",
       "1   5       99\n",
       "2   6       -3"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "right = pd.DataFrame(\n",
    "    {\n",
    "        'id':[4,5,6],\n",
    "        'valores':[50, 99, -3]\n",
    "    })\n",
    "right"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Para realizar a junção, nós usamos `pd.merge`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "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>class</th>\n",
       "      <th>valores</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>B</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>D</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id class  valores\n",
       "0   4     B       50\n",
       "1   5     D       99"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(left, right)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Por padrão é realizado um `inner join` nas colunas em comum (neste caso, `id`). O exemplo abaixo mostra um `outer join`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "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>class</th>\n",
       "      <th>valores</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>A</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>C</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>B</td>\n",
       "      <td>50.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>D</td>\n",
       "      <td>99.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id class  valores\n",
       "0   1     A      NaN\n",
       "1   2     A      NaN\n",
       "2   3     C      NaN\n",
       "3   4     B     50.0\n",
       "4   5     D     99.0\n",
       "5   6   NaN     -3.0"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(left, right, how='outer')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "E, se necessário, podemos realiza a junção usando o índice:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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_x</th>\n",
       "      <th>class</th>\n",
       "      <th>id_y</th>\n",
       "      <th>valores</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>A</td>\n",
       "      <td>4</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>A</td>\n",
       "      <td>5</td>\n",
       "      <td>99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>C</td>\n",
       "      <td>6</td>\n",
       "      <td>-3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_x class  id_y  valores\n",
       "0     1     A     4       50\n",
       "1     2     A     5       99\n",
       "2     3     C     6       -3"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(left, right, left_index=True, right_index=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note como os valores da coluna em comum `id` diferem, foram criados duas colunas novas com os nomes sendo o nome da coluna em comum mais um sufixo. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Desafio: acesse o banco de dados `chinook` e:\n",
    ">\n",
    "> 1. Liste o nome dos artistas, músicas e álbuns. \n",
    ">\n",
    "> 2. Em seguida, crie um relatório com os 10 artistas que mais possuem música."
   ]
  }
 ],
 "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.6"
  },
  "toc-autonumbering": true,
  "toc-showmarkdowntxt": false
 },
 "nbformat": 4,
 "nbformat_minor": 4
}