06_pandas_dataframe.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Pandas - DataFrame"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### DataFrame\n",
    "\n",
    "É um objeto que representa uma matriz de 2 dimensões, onde todas as colunas são alinhadas pelo mesmo índice. Um dataframe pode ter séries (colunas) com tipos diferentes.\n",
    "\n",
    "Pode ser comparado a uma planilha excel ou a uma tabela de banco de dados.\n",
    "\n",
    "\n",
    "![DataFrame](imagens/excel_dataframe.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "# por convenção utiliza-se o alias np ao importar a biblioteca\n",
    "import numpy as np\n",
    "\n",
    "# por convenção utiliza-se o alias pd ao importar a biblioteca\n",
    "import pandas as pd\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "\n",
    "# outros imports que serão úteis\n",
    "import os\n",
    "import re\n",
    "from collections import OrderedDict\n",
    "\n",
    "# forçar a renderização do dataframe sem notação científica\n",
    "pd.set_option('display.float_format', lambda x: '{:.2f}'.format(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>Sensor A</th>\n",
       "      <th>Sensor B</th>\n",
       "      <th>Médias</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-02-01</th>\n",
       "      <td>27.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-02</th>\n",
       "      <td>28.30</td>\n",
       "      <td>28.60</td>\n",
       "      <td>28.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-03</th>\n",
       "      <td>29.10</td>\n",
       "      <td>32.10</td>\n",
       "      <td>30.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-04</th>\n",
       "      <td>nan</td>\n",
       "      <td>26.00</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Sensor A  Sensor B  Médias\n",
       "2018-02-01     27.00       nan     nan\n",
       "2018-02-02     28.30     28.60   28.45\n",
       "2018-02-03     29.10     32.10   30.60\n",
       "2018-02-04       nan     26.00     nan"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s5 = pd.Series([27, 28.3, 29.1], name='Temperatura Sensor A', index=['2018-02-01', '2018-02-02', '2018-02-03'])\n",
    "s6 = pd.Series([28.6, 32.1, 26], name='Temperatura Sensor B', index=['2018-02-02', '2018-02-03', '2018-02-04'])\n",
    "s7 = (s5 + s6) / 2\n",
    "s7.rename('Temperatura Média', inplace=True)\n",
    "\n",
    "# Criação de um DataFrame à partir de um dicionário de séries\n",
    "d = OrderedDict({\n",
    "    'Sensor A': s5,\n",
    "    'Sensor B': s6,\n",
    "    'Médias': s7,\n",
    "})\n",
    "df1 = pd.DataFrame(d)\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>Sensor A</th>\n",
       "      <th>Sensor B</th>\n",
       "      <th>Médias</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-02-02</th>\n",
       "      <td>28.30</td>\n",
       "      <td>28.60</td>\n",
       "      <td>28.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-03</th>\n",
       "      <td>29.10</td>\n",
       "      <td>32.10</td>\n",
       "      <td>30.60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Sensor A  Sensor B  Médias\n",
       "2018-02-02     28.30     28.60   28.45\n",
       "2018-02-03     29.10     32.10   30.60"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# se passarmos uma lista com os índices, todos os dados que não estiverem\n",
    "# de acordo com os índices informados serão descartados\n",
    "df2 = pd.DataFrame(d, index=['2018-02-02', '2018-02-03'])\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>Produto</th>\n",
       "      <th>Unidade de Medida</th>\n",
       "      <th>Valor Unitário</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Maçã</td>\n",
       "      <td>kg</td>\n",
       "      <td>4.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Leite</td>\n",
       "      <td>L</td>\n",
       "      <td>6.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Canela</td>\n",
       "      <td>g</td>\n",
       "      <td>1.60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Produto Unidade de Medida  Valor Unitário\n",
       "0    Maçã                kg            4.99\n",
       "1   Leite                 L            6.70\n",
       "2  Canela                 g            1.60"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Criação de um DataFrame à partir de um dicionário de listas\n",
    "\n",
    "d = dict({\n",
    "    'Produto': ['Maçã', 'Leite', 'Canela'],\n",
    "    'Unidade de Medida': ['kg', 'L', 'g'],\n",
    "    'Valor Unitário': [4.99, 6.7, 1.6],\n",
    "})\n",
    "df3 = pd.DataFrame(d)\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "RangeIndex(start=0, stop=3, step=1)\n"
     ]
    }
   ],
   "source": [
    "# Se nenhum índice é informado, o DataFrame terá um RangeIndex iniciando em 0.\n",
    "print(df3.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>Produto</th>\n",
       "      <th>Unidade de Medida</th>\n",
       "      <th>Valor Unitário</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Maçã</td>\n",
       "      <td>kg</td>\n",
       "      <td>4.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Leite</td>\n",
       "      <td>L</td>\n",
       "      <td>6.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Canela</td>\n",
       "      <td>g</td>\n",
       "      <td>1.60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Produto Unidade de Medida  Valor Unitário\n",
       "0    Maçã                kg            4.99\n",
       "1   Leite                 L            6.70\n",
       "2  Canela                 g            1.60"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Criação de um DataFrame à partir de um dicionário de listas\n",
    "\n",
    "d = dict({\n",
    "    'Produto': ['Maçã', 'Leite', 'Canela'],\n",
    "    'Unidade de Medida': ['kg', 'L', 'g'],\n",
    "    'Valor Unitário': [4.99, 6.7, 1.6],\n",
    "})\n",
    "df3 = pd.DataFrame(d)\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>Temperatura Média</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-02-01</th>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-02</th>\n",
       "      <td>28.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-03</th>\n",
       "      <td>30.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-04</th>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Temperatura Média\n",
       "2018-02-01                nan\n",
       "2018-02-02              28.45\n",
       "2018-02-03              30.60\n",
       "2018-02-04                nan"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Uma série pode ser transformada em um DataFrame de uma coluna utilizando o método 'to_frame'\n",
    "df4 = s7.to_frame()\n",
    "df4"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "* Características e atributos importantes do dataframes\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Valores: [[27.     nan   nan]\n",
      " [28.3  28.6  28.45]\n",
      " [29.1  32.1  30.6 ]\n",
      " [  nan 26.     nan]] \n",
      "Tipo: <class 'numpy.ndarray'>\n",
      "\n",
      "Índices: Index(['2018-02-01', '2018-02-02', '2018-02-03', '2018-02-04'], dtype='object') \n",
      "Tipo: <class 'pandas.core.indexes.base.Index'>\n",
      "\n",
      "Nome: Index(['Sensor A', 'Sensor B', 'Médias'], dtype='object') \n",
      "Tipo: <class 'pandas.core.indexes.base.Index'>\n",
      "\n",
      "Shape: (4, 3) \n",
      "Tipo: <class 'tuple'>\n"
     ]
    }
   ],
   "source": [
    "print('Valores:', df1.values, '\\nTipo:', type(df1.values))\n",
    "print('\\nÍndices:', df1.index, '\\nTipo:', type(df1.index))\n",
    "print('\\nNome:', df1.columns, '\\nTipo:', type(df1.columns))\n",
    "print('\\nShape:', df1.shape, '\\nTipo:', type(df1.shape))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "O dataframe possui o método info() que descreve algumas características básicas como, tipo de dado, quantidade de registros, intervalo do índice, registros não nulos por coluna e tamanho em memória."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Index: 4 entries, 2018-02-01 to 2018-02-04\n",
      "Data columns (total 3 columns):\n",
      "Sensor A    3 non-null float64\n",
      "Sensor B    3 non-null float64\n",
      "Médias      2 non-null float64\n",
      "dtypes: float64(3)\n",
      "memory usage: 128.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "df1.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "A operação padrão de indexação/fatiamento do DataFrame é um pouco diferente da da Série. Enquanto a série representa apenas uma coluna, o dataframe pode conter várias colunas.\n",
    "\n",
    "Dessa forma, os slices no dataframe podem abranger linhas e/ou colunas. Abaixo veremos uma série de exemplos simples de slices em DataFrames. Seleções mais complexas e completas serão abordadas em uma seção específica no próximo capítulo."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2018-02-01   27.00\n",
       "2018-02-02   28.30\n",
       "2018-02-03   29.10\n",
       "2018-02-04     nan\n",
       "Name: Sensor A, dtype: float64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# seleção de uma coluna no dataframe retorna uma série\n",
    "df1['Sensor A']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "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>Sensor A</th>\n",
       "      <th>Sensor B</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-02-01</th>\n",
       "      <td>27.00</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-02</th>\n",
       "      <td>28.30</td>\n",
       "      <td>28.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-03</th>\n",
       "      <td>29.10</td>\n",
       "      <td>32.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-04</th>\n",
       "      <td>nan</td>\n",
       "      <td>26.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Sensor A  Sensor B\n",
       "2018-02-01     27.00       nan\n",
       "2018-02-02     28.30     28.60\n",
       "2018-02-03     29.10     32.10\n",
       "2018-02-04       nan     26.00"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# seleção de mais de uma coluna no dataframe retorna um novo dataframe com a seleção\n",
    "df1[['Sensor A', 'Sensor B']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>Sensor B</th>\n",
       "      <th>Sensor A</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-02-01</th>\n",
       "      <td>nan</td>\n",
       "      <td>27.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-02</th>\n",
       "      <td>28.60</td>\n",
       "      <td>28.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-03</th>\n",
       "      <td>32.10</td>\n",
       "      <td>29.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-04</th>\n",
       "      <td>26.00</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Sensor B  Sensor A\n",
       "2018-02-01       nan     27.00\n",
       "2018-02-02     28.60     28.30\n",
       "2018-02-03     32.10     29.10\n",
       "2018-02-04     26.00       nan"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# se for especificada uma ordem diferente um novo dataframe sera \n",
    "#criado com as colunas na ordem informada\n",
    "df1[['Sensor B', 'Sensor A']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "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>Sensor A</th>\n",
       "      <th>Sensor B</th>\n",
       "      <th>Médias</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-02-01</th>\n",
       "      <td>27.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-02</th>\n",
       "      <td>28.30</td>\n",
       "      <td>28.60</td>\n",
       "      <td>28.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-03</th>\n",
       "      <td>29.10</td>\n",
       "      <td>32.10</td>\n",
       "      <td>30.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-04</th>\n",
       "      <td>nan</td>\n",
       "      <td>26.00</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Sensor A  Sensor B  Médias\n",
       "2018-02-01     27.00       nan     nan\n",
       "2018-02-02     28.30     28.60   28.45\n",
       "2018-02-03     29.10     32.10   30.60\n",
       "2018-02-04       nan     26.00     nan"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# um dataframe pode ser criado à partir de outro de forma fácil\n",
    "df5 = df1.copy()\n",
    "\n",
    "#ou\n",
    "\n",
    "df5 = pd.DataFrame(df1)\n",
    "\n",
    "df5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>Sensor A</th>\n",
       "      <th>Sensor B</th>\n",
       "      <th>Médias</th>\n",
       "      <th>Sensor C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-02-01</th>\n",
       "      <td>27.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-02</th>\n",
       "      <td>28.30</td>\n",
       "      <td>28.60</td>\n",
       "      <td>28.45</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-03</th>\n",
       "      <td>29.10</td>\n",
       "      <td>32.10</td>\n",
       "      <td>30.60</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-04</th>\n",
       "      <td>nan</td>\n",
       "      <td>26.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Sensor A  Sensor B  Médias  Sensor C\n",
       "2018-02-01     27.00       nan     nan        30\n",
       "2018-02-02     28.30     28.60   28.45        30\n",
       "2018-02-03     29.10     32.10   30.60        30\n",
       "2018-02-04       nan     26.00     nan        30"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#novas colunas podem ser criadas no dataframe à partir de um valor escalar\n",
    "df5['Sensor C'] = 30\n",
    " \n",
    "df5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "-"
    }
   },
   "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>Sensor A</th>\n",
       "      <th>Sensor B</th>\n",
       "      <th>Médias</th>\n",
       "      <th>Sensor C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-02-01</th>\n",
       "      <td>27.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-02</th>\n",
       "      <td>28.30</td>\n",
       "      <td>28.60</td>\n",
       "      <td>28.45</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-03</th>\n",
       "      <td>29.10</td>\n",
       "      <td>32.10</td>\n",
       "      <td>30.60</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-04</th>\n",
       "      <td>nan</td>\n",
       "      <td>26.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Sensor A  Sensor B  Médias  Sensor C\n",
       "2018-02-01     27.00       nan     nan         1\n",
       "2018-02-02     28.30     28.60   28.45         2\n",
       "2018-02-03     29.10     32.10   30.60         3\n",
       "2018-02-04       nan     26.00     nan         4"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# novas colunas podem ser criadas no dataframe à partir de listas,\n",
    "# desde que o número de linhas seja igual ao do dataframe\n",
    "df5['Sensor C'] = [1,2,3,4]\n",
    "df5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>Sensor A</th>\n",
       "      <th>Sensor B</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-02-01</th>\n",
       "      <td>27.00</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-02</th>\n",
       "      <td>28.30</td>\n",
       "      <td>28.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-03</th>\n",
       "      <td>29.10</td>\n",
       "      <td>32.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-04</th>\n",
       "      <td>nan</td>\n",
       "      <td>26.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            Sensor A  Sensor B\n",
       "2018-02-01     27.00       nan\n",
       "2018-02-02     28.30     28.60\n",
       "2018-02-03     29.10     32.10\n",
       "2018-02-04       nan     26.00"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# colunas podem ser removidas com o comando del (veja na mais sobre o del na documentação do python)\n",
    "del(df5['Sensor C'])\n",
    "del(df5['Médias'])\n",
    "df5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>Sensores</th>\n",
       "      <th>Sensor A</th>\n",
       "      <th>Sensor B</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Data</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-02-01</th>\n",
       "      <td>27.00</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-02</th>\n",
       "      <td>28.30</td>\n",
       "      <td>28.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-03</th>\n",
       "      <td>29.10</td>\n",
       "      <td>32.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-02-04</th>\n",
       "      <td>nan</td>\n",
       "      <td>26.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Sensores    Sensor A  Sensor B\n",
       "Data                          \n",
       "2018-02-01     27.00       nan\n",
       "2018-02-02     28.30     28.60\n",
       "2018-02-03     29.10     32.10\n",
       "2018-02-04       nan     26.00"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# os índices podem ter nomes atribuídos\n",
    "df5.columns.name = 'Sensores'\n",
    "df5.index.name = 'Data'\n",
    "\n",
    "df5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "A maioria dos métodos vistos para Series funcionam para DataFrames também, mas podem ter variações ou limitações dependendo do tipo de dado de cada série."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": false,
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "min:\n",
      " Sensores\n",
      "Sensor A   27.00\n",
      "Sensor B   26.00\n",
      "dtype: float64\n",
      "\n",
      "max:\n",
      " Sensores\n",
      "Sensor A   29.10\n",
      "Sensor B   32.10\n",
      "dtype: float64\n",
      "\n",
      "mean:\n",
      " Sensores\n",
      "Sensor A   28.13\n",
      "Sensor B   28.90\n",
      "dtype: float64\n",
      "\n",
      "median:\n",
      " Sensores\n",
      "Sensor A   28.30\n",
      "Sensor B   28.60\n",
      "dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print('min:\\n', df1.min())\n",
    "\n",
    "# maior valor\n",
    "print('\\nmax:\\n', df1.max())\n",
    "\n",
    "# média\n",
    "print('\\nmean:\\n', df1.mean())\n",
    "\n",
    "# mediana\n",
    "print('\\nmedian:\\n', df1.median())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "notes"
    }
   },
   "source": [
    "Documentação do Pandas para a classe DataFrame:\n",
    "https://pandas.pydata.org/pandas-docs/stable/api.html#series\n",
    "\n",
    "Outras formas de criar DataFrames podem ser consultadas aqui: http://pandas.pydata.org/pandas-docs/stable/dsintro.html#alternate-constructors"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Seleções e subconjuntos de dados"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Esta seção pretende abordar as  mais variadas formas de seleções de dados utilizando DataFrames, Séries e Índices.\n",
    "\n",
    "### Expressões\n",
    "\n",
    "Expressões são operações que podem ser feitas com DataFrames, Séries e Índices utilizando operadores lógicos e de comparação, com o objetivo de criar filtros para gerar subconjuntos de dados."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "scrolled": false,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(122, 8)\n"
     ]
    },
    {
     "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>Município</th>\n",
       "      <th>Estado</th>\n",
       "      <th>Área territorial</th>\n",
       "      <th>População (IBGE/2007)</th>\n",
       "      <th>Densidade demográfica (hab/km2)</th>\n",
       "      <th>PIB (IBGE/2005)</th>\n",
       "      <th>PIB per capita (R$)</th>\n",
       "      <th>IDH/2000</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Aceguá</td>\n",
       "      <td>Rio Grande do Sul</td>\n",
       "      <td>1550.00</td>\n",
       "      <td>4138.00</td>\n",
       "      <td>2.66</td>\n",
       "      <td>71638000.00</td>\n",
       "      <td>17266.00</td>\n",
       "      <td>nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Acrelândia</td>\n",
       "      <td>Acre</td>\n",
       "      <td>1575.00</td>\n",
       "      <td>11520.00</td>\n",
       "      <td>7.31</td>\n",
       "      <td>114350000.00</td>\n",
       "      <td>9986.00</td>\n",
       "      <td>0.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alecrim</td>\n",
       "      <td>Rio Grande do Sul</td>\n",
       "      <td>315.00</td>\n",
       "      <td>7357.00</td>\n",
       "      <td>23.35</td>\n",
       "      <td>44373000.00</td>\n",
       "      <td>5944.00</td>\n",
       "      <td>0.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Almeirim</td>\n",
       "      <td>Pará</td>\n",
       "      <td>72960.00</td>\n",
       "      <td>30903.00</td>\n",
       "      <td>0.42</td>\n",
       "      <td>462258000.00</td>\n",
       "      <td>13485.00</td>\n",
       "      <td>0.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Alta Floresta d'Oeste</td>\n",
       "      <td>Rondônia</td>\n",
       "      <td>7067.00</td>\n",
       "      <td>23857.00</td>\n",
       "      <td>3.37</td>\n",
       "      <td>186812000.00</td>\n",
       "      <td>6525.00</td>\n",
       "      <td>0.71</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Município             Estado  Área territorial  \\\n",
       "0                 Aceguá  Rio Grande do Sul           1550.00   \n",
       "1             Acrelândia               Acre           1575.00   \n",
       "2                Alecrim  Rio Grande do Sul            315.00   \n",
       "3               Almeirim               Pará          72960.00   \n",
       "4  Alta Floresta d'Oeste           Rondônia           7067.00   \n",
       "\n",
       "   População (IBGE/2007)  Densidade demográfica (hab/km2)  PIB (IBGE/2005)  \\\n",
       "0                4138.00                             2.66      71638000.00   \n",
       "1               11520.00                             7.31     114350000.00   \n",
       "2                7357.00                            23.35      44373000.00   \n",
       "3               30903.00                             0.42     462258000.00   \n",
       "4               23857.00                             3.37     186812000.00   \n",
       "\n",
       "   PIB per capita (R$)  IDH/2000  \n",
       "0             17266.00       nan  \n",
       "1              9986.00      0.68  \n",
       "2              5944.00      0.74  \n",
       "3             13485.00      0.74  \n",
       "4              6525.00      0.71  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def to_float(valor):\n",
    "    if valor == 'ni':\n",
    "        return np.nan\n",
    "    return float(valor.replace('.','').replace(',','.').replace(' ',''))\n",
    "    \n",
    "df = pd.read_csv(\n",
    "    'dados/arq_municipios_fronteiricos.tsv', \n",
    "    sep='\\t',\n",
    "    decimal='.', \n",
    "    thousands=',', \n",
    "    converters={\n",
    "        'Área territorial': to_float,\n",
    "        'População (IBGE/2007)': to_float,\n",
    "        'Densidade demográfica (hab/km2)': to_float,\n",
    "        'PIB (IBGE/2005)': to_float,\n",
    "        'PIB per capita (R$)': to_float,\n",
    "        'IDH/2000': to_float,\n",
    "    }, \n",
    "    na_values=['ni'],\n",
    ")\n",
    "\n",
    "df['Município'] = df['Município'].str.replace(r'([\\d]* [-] )(.*)', r'\\2')\n",
    "\n",
    "print(df.shape)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Vejamos um exemplo de expressão:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.series.Series'>\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0    False\n",
       "1    False\n",
       "2     True\n",
       "3     True\n",
       "4    False\n",
       "Name: IDH/2000, dtype: bool"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# utilizando um operador de comparação associado a uma \n",
    "# estrutura de dados do pandas temos uma expressão\n",
    "expressao = df['IDH/2000'] >= 0.741\n",
    "\n",
    "print(type(expressao))\n",
    "\n",
    "expressao.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count       122\n",
       "unique        2\n",
       "top       False\n",
       "freq         70\n",
       "Name: IDH/2000, dtype: object"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "expressao.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "As expressões são séries ou dataframes com o mesmo tamanho dos originais, porém seus valores são preenchidos com True ou False de acordo com a avaliação da expressão para cada item. Essas séries ou dataframes de booleanos podem ser passados para os operadores de indexação __[ ]__ e __loc__."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(52, 8)\n"
     ]
    },
    {
     "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>Município</th>\n",
       "      <th>Estado</th>\n",
       "      <th>Área territorial</th>\n",
       "      <th>População (IBGE/2007)</th>\n",
       "      <th>Densidade demográfica (hab/km2)</th>\n",
       "      <th>PIB (IBGE/2005)</th>\n",
       "      <th>PIB per capita (R$)</th>\n",
       "      <th>IDH/2000</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alecrim</td>\n",
       "      <td>Rio Grande do Sul</td>\n",
       "      <td>315.00</td>\n",
       "      <td>7357.00</td>\n",
       "      <td>23.35</td>\n",
       "      <td>44373000.00</td>\n",
       "      <td>5944.00</td>\n",
       "      <td>0.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Almeirim</td>\n",
       "      <td>Pará</td>\n",
       "      <td>72960.00</td>\n",
       "      <td>30903.00</td>\n",
       "      <td>0.42</td>\n",
       "      <td>462258000.00</td>\n",
       "      <td>13485.00</td>\n",
       "      <td>0.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Bagé</td>\n",
       "      <td>Rio Grande do Sul</td>\n",
       "      <td>4096.00</td>\n",
       "      <td>112550.00</td>\n",
       "      <td>27.47</td>\n",
       "      <td>906488000.00</td>\n",
       "      <td>7473.00</td>\n",
       "      <td>0.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Bandeirante</td>\n",
       "      <td>Santa Catarina</td>\n",
       "      <td>146.00</td>\n",
       "      <td>3028.00</td>\n",
       "      <td>20.73</td>\n",
       "      <td>21423000.00</td>\n",
       "      <td>7546.00</td>\n",
       "      <td>0.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Barra do Quaraí</td>\n",
       "      <td>Rio Grande do Sul</td>\n",
       "      <td>1056.00</td>\n",
       "      <td>3776.00</td>\n",
       "      <td>3.57</td>\n",
       "      <td>61540000.00</td>\n",
       "      <td>14429.00</td>\n",
       "      <td>0.78</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Município             Estado  Área territorial  \\\n",
       "2           Alecrim  Rio Grande do Sul            315.00   \n",
       "3          Almeirim               Pará          72960.00   \n",
       "12             Bagé  Rio Grande do Sul           4096.00   \n",
       "13      Bandeirante     Santa Catarina            146.00   \n",
       "15  Barra do Quaraí  Rio Grande do Sul           1056.00   \n",
       "\n",
       "    População (IBGE/2007)  Densidade demográfica (hab/km2)  PIB (IBGE/2005)  \\\n",
       "2                 7357.00                            23.35      44373000.00   \n",
       "3                30903.00                             0.42     462258000.00   \n",
       "12              112550.00                            27.47     906488000.00   \n",
       "13                3028.00                            20.73      21423000.00   \n",
       "15                3776.00                             3.57      61540000.00   \n",
       "\n",
       "    PIB per capita (R$)  IDH/2000  \n",
       "2               5944.00      0.74  \n",
       "3              13485.00      0.74  \n",
       "12              7473.00      0.80  \n",
       "13              7546.00      0.77  \n",
       "15             14429.00      0.78  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(df[expressao].shape)\n",
    "\n",
    "df[expressao].head()\n",
    "\n",
    "# ou \n",
    "\n",
    "df.loc[expressao].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Podemos utilizar operadores lógicos para avaliar mais de uma expressão caso sejam necessárias condicionais mais complexas para os filtros."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(23, 8)\n"
     ]
    },
    {
     "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>Município</th>\n",
       "      <th>Estado</th>\n",
       "      <th>Área territorial</th>\n",
       "      <th>População (IBGE/2007)</th>\n",
       "      <th>Densidade demográfica (hab/km2)</th>\n",
       "      <th>PIB (IBGE/2005)</th>\n",
       "      <th>PIB per capita (R$)</th>\n",
       "      <th>IDH/2000</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Alecrim</td>\n",
       "      <td>Rio Grande do Sul</td>\n",
       "      <td>315.00</td>\n",
       "      <td>7357.00</td>\n",
       "      <td>23.35</td>\n",
       "      <td>44373000.00</td>\n",
       "      <td>5944.00</td>\n",
       "      <td>0.74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Bagé</td>\n",
       "      <td>Rio Grande do Sul</td>\n",
       "      <td>4096.00</td>\n",
       "      <td>112550.00</td>\n",
       "      <td>27.47</td>\n",
       "      <td>906488000.00</td>\n",
       "      <td>7473.00</td>\n",
       "      <td>0.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>Barra do Quaraí</td>\n",
       "      <td>Rio Grande do Sul</td>\n",
       "      <td>1056.00</td>\n",
       "      <td>3776.00</td>\n",
       "      <td>3.57</td>\n",
       "      <td>61540000.00</td>\n",
       "      <td>14429.00</td>\n",
       "      <td>0.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Chuí</td>\n",
       "      <td>Rio Grande do Sul</td>\n",
       "      <td>203.00</td>\n",
       "      <td>5278.00</td>\n",
       "      <td>26.00</td>\n",
       "      <td>67525000.00</td>\n",
       "      <td>10574.00</td>\n",
       "      <td>0.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>Crissiumal</td>\n",
       "      <td>Rio Grande do Sul</td>\n",
       "      <td>362.00</td>\n",
       "      <td>14726.00</td>\n",
       "      <td>40.67</td>\n",
       "      <td>114089000.00</td>\n",
       "      <td>8376.00</td>\n",
       "      <td>0.79</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Município             Estado  Área territorial  \\\n",
       "2           Alecrim  Rio Grande do Sul            315.00   \n",
       "12             Bagé  Rio Grande do Sul           4096.00   \n",
       "15  Barra do Quaraí  Rio Grande do Sul           1056.00   \n",
       "30             Chuí  Rio Grande do Sul            203.00   \n",
       "35       Crissiumal  Rio Grande do Sul            362.00   \n",
       "\n",
       "    População (IBGE/2007)  Densidade demográfica (hab/km2)  PIB (IBGE/2005)  \\\n",
       "2                 7357.00                            23.35      44373000.00   \n",
       "12              112550.00                            27.47     906488000.00   \n",
       "15                3776.00                             3.57      61540000.00   \n",
       "30                5278.00                            26.00      67525000.00   \n",
       "35               14726.00                            40.67     114089000.00   \n",
       "\n",
       "    PIB per capita (R$)  IDH/2000  \n",
       "2               5944.00      0.74  \n",
       "12              7473.00      0.80  \n",
       "15             14429.00      0.78  \n",
       "30             10574.00      0.81  \n",
       "35              8376.00      0.79  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "expressao = (df['IDH/2000'] >= .741) & (df['Estado'] == 'Rio Grande do Sul')\n",
    "print(df[expressao].shape)\n",
    "df.loc[expressao].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "Os comparadores lógicos são:\n",
    "* __<__  : menor que ...\n",
    "* __>__  : maior que ...\n",
    "* __==__  : igual a  ...\n",
    "* __<=__  : menor ou igual a  ...\n",
    "* __>=__  : maior ou igual a  ...\n",
    "* __!=__  : diferente de ...\n",
    "* __isnull()__  : se o valor é nulo ou inválido (NaN) ...\n",
    "* __notnull()__  : se o valor não é nulo e nem inválido (NaN) ...\n",
    "* __isin()__  : se o valor está contido em um dos dados informados em uma lista ...\n",
    "\n",
    "Os operadores lógicos são:\n",
    "* __&__: operador E (and)\n",
    "* __|__: operador OU (or)\n",
    "* __~__: operador NAO (not)\n",
    "* __^__: operador OU EXCLUSIVO (xor)\n",
    "* __any()__: se algum elemento da lista for True retornará True\n",
    "* __all()__: se todos os elementos da lista forem True retornará True"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Opções de indexação\n",
    "\n",
    "__df[val]__\n",
    "* seleciona uma ou uma sequência de colunas em um DataFrame\n",
    "* seleciona linhas com expressões\n",
    "* seleciona linhas por posição\n",
    "* seleciona linhas por índice (label), cuidado, resultado pode ser ambíguo (utilize loc neste caso)\n",
    "* caso esteja sendo feito algum filtro de linhas, não permite seleção de colunas ao mesmo tempo. Retornará todas\n",
    " \n",
    "__df.loc[val]__\n",
    "* seleciona uma ou mais linhas pelo índice\n",
    "\n",
    "__df.loc[:, val]__\n",
    "* seleciona uma ou mais colunas pelo nome das colunas\n",
    "\n",
    "__df.loc[val1, val2]__\n",
    "* seleciona linhas e colunas\n",
    "\n",
    "__df.iloc[onde]__\n",
    "* seleciona uma ou mais linhas pela posição. 'onde' pode ser uma posição, slice ou uma lista de posições\n",
    "\n",
    "__df.iloc[:, onde]__\n",
    "* seleciona uma ou mais colunas pela posição. 'onde' pode ser uma posição, slice ou uma lista de posições\n",
    "\n",
    "__df.iloc[onde_i, onde_j]__\n",
    "* seleciona uma ou mais linhas e colunas pela posição. 'onde_?' pode ser uma posição, slice ou uma lista de posições"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercício\n",
    "\n",
    "Para todos os items abaixo, considere o dataframe contido na variável \"df\".\n",
    "\n",
    "1. Utilize os métodos head, tail e sample para visualizar os dados do DataFrame.\n",
    "2. Imprima o número de linhas e o número de colunas deste DataFrame.\n",
    "3. Imprima o valor do maior PIB\n",
    "4. Imprima a linha do município que contém o maior PIB\n",
    "5. Ordene o dataframe pelo PIB em ordem decrescente\n",
    "6. Faça uma seleção com as colunas Município, Estado e IDH, atribua à variável df_idh e imprima as primeiras 3 linhas\n",
    "7. Faça uma seleção com as colunas Município, Estado e Densidade demográfica (hab/km2), e com as linhas de índices 1, 2 e 3. Utilize o método loc.\n",
    "8. Filtre apenas os municípios do Pará\n",
    "9. Filtre apenas os municípios do Acre com IDH menor que 0.6\n",
    "10. Encontre os municípios entre os 25% com maior PIB (IBGE/2005) e faça a contagem desses municípios por estado (Dicas: quantile e value_counts)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Agregações"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 547711 entries, 0 to 547710\n",
      "Data columns (total 9 columns):\n",
      "Ano                             547711 non-null float64\n",
      "Espécie                         547711 non-null category\n",
      "Capitulo da CID                 547711 non-null category\n",
      "Sexo                            547711 non-null category\n",
      "Clientela                       547711 non-null category\n",
      "Quantidade Benefícios Ativos    547711 non-null float64\n",
      "Valor Benefícios Ativos (R$)    547711 non-null float64\n",
      "Grupo/Principais Espécies       547711 non-null category\n",
      "Valor (R$ mil)                  547711 non-null float64\n",
      "dtypes: category(5), float64(4)\n",
      "memory usage: 23.5 MB\n",
      "None\n"
     ]
    },
    {
     "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>Ano</th>\n",
       "      <th>Espécie</th>\n",
       "      <th>Capitulo da CID</th>\n",
       "      <th>Sexo</th>\n",
       "      <th>Clientela</th>\n",
       "      <th>Quantidade Benefícios Ativos</th>\n",
       "      <th>Valor Benefícios Ativos (R$)</th>\n",
       "      <th>Grupo/Principais Espécies</th>\n",
       "      <th>Valor (R$ mil)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2004.00</td>\n",
       "      <td>Ap Invalidez Det Ignorado</td>\n",
       "      <td>Cid 9 - I - Doenças Infecciosas e Parasitária</td>\n",
       "      <td>Masculino</td>\n",
       "      <td>Urbana</td>\n",
       "      <td>18646.00</td>\n",
       "      <td>5795330.45</td>\n",
       "      <td>Ap Invalidez</td>\n",
       "      <td>5795.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2004.00</td>\n",
       "      <td>Ap Invalidez Det Ignorado</td>\n",
       "      <td>Cid 9 - I - Doenças Infecciosas e Parasitária</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>Urbana</td>\n",
       "      <td>9765.00</td>\n",
       "      <td>2343602.54</td>\n",
       "      <td>Ap Invalidez</td>\n",
       "      <td>2343.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2004.00</td>\n",
       "      <td>Ap Invalidez Det Ignorado</td>\n",
       "      <td>Cid 9 - I - Doenças Infecciosas e Parasitária</td>\n",
       "      <td>Ignorado</td>\n",
       "      <td>Urbana</td>\n",
       "      <td>5.00</td>\n",
       "      <td>1093.15</td>\n",
       "      <td>Ap Invalidez</td>\n",
       "      <td>1.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2004.00</td>\n",
       "      <td>Ap Invalidez Det Ignorado</td>\n",
       "      <td>Cid 9 - Ii - Neoplasmas</td>\n",
       "      <td>Masculino</td>\n",
       "      <td>Urbana</td>\n",
       "      <td>8741.00</td>\n",
       "      <td>3935448.30</td>\n",
       "      <td>Ap Invalidez</td>\n",
       "      <td>3935.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2004.00</td>\n",
       "      <td>Ap Invalidez Det Ignorado</td>\n",
       "      <td>Cid 9 - Ii - Neoplasmas</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>Urbana</td>\n",
       "      <td>12185.00</td>\n",
       "      <td>3661307.37</td>\n",
       "      <td>Ap Invalidez</td>\n",
       "      <td>3661.31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Ano                    Espécie  \\\n",
       "0 2004.00  Ap Invalidez Det Ignorado   \n",
       "1 2004.00  Ap Invalidez Det Ignorado   \n",
       "2 2004.00  Ap Invalidez Det Ignorado   \n",
       "3 2004.00  Ap Invalidez Det Ignorado   \n",
       "4 2004.00  Ap Invalidez Det Ignorado   \n",
       "\n",
       "                                 Capitulo da CID       Sexo Clientela  \\\n",
       "0  Cid 9 - I - Doenças Infecciosas e Parasitária  Masculino    Urbana   \n",
       "1  Cid 9 - I - Doenças Infecciosas e Parasitária   Feminino    Urbana   \n",
       "2  Cid 9 - I - Doenças Infecciosas e Parasitária   Ignorado    Urbana   \n",
       "3                        Cid 9 - Ii - Neoplasmas  Masculino    Urbana   \n",
       "4                        Cid 9 - Ii - Neoplasmas   Feminino    Urbana   \n",
       "\n",
       "   Quantidade Benefícios Ativos  Valor Benefícios Ativos (R$)  \\\n",
       "0                      18646.00                    5795330.45   \n",
       "1                       9765.00                    2343602.54   \n",
       "2                          5.00                       1093.15   \n",
       "3                       8741.00                    3935448.30   \n",
       "4                      12185.00                    3661307.37   \n",
       "\n",
       "  Grupo/Principais Espécies  Valor (R$ mil)  \n",
       "0              Ap Invalidez         5795.33  \n",
       "1              Ap Invalidez         2343.60  \n",
       "2              Ap Invalidez            1.09  \n",
       "3              Ap Invalidez         3935.45  \n",
       "4              Ap Invalidez         3661.31  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_parquet('dados/bi_ativ_urb_cid.parquet')\n",
    "for c in ['Espécie', 'Capitulo da CID', 'Sexo', 'Clientela', 'Grupo/Principais Espécies']:\n",
    "    df[c] = df[c].astype('category')\n",
    "print(df.info())\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>Ano</th>\n",
       "      <th>Sexo</th>\n",
       "      <th>Quantidade Benefícios Ativos</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2004.00</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>1396298.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2004.00</td>\n",
       "      <td>Ignorado</td>\n",
       "      <td>29175.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2004.00</td>\n",
       "      <td>Masculino</td>\n",
       "      <td>1959787.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2005.00</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>1460602.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2005.00</td>\n",
       "      <td>Ignorado</td>\n",
       "      <td>25510.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2005.00</td>\n",
       "      <td>Masculino</td>\n",
       "      <td>2041717.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Ano       Sexo  Quantidade Benefícios Ativos\n",
       "0 2004.00   Feminino                    1396298.00\n",
       "1 2004.00   Ignorado                      29175.00\n",
       "2 2004.00  Masculino                    1959787.00\n",
       "3 2005.00   Feminino                    1460602.00\n",
       "4 2005.00   Ignorado                      25510.00\n",
       "5 2005.00  Masculino                    2041717.00"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Agregação simples com uso de somatório\n",
    "\n",
    "df_ano_sexo = df[['Ano', 'Sexo', 'Quantidade Benefícios Ativos']].groupby(['Ano', 'Sexo'], as_index=False).sum()\n",
    "df_ano_sexo.head(6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Ano</th>\n",
       "      <th>Sexo</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Quantidade Benefícios Ativos</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Valor Benefícios Ativos (R$)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Média/CID</th>\n",
       "      <th>Total</th>\n",
       "      <th>Média/CID</th>\n",
       "      <th>Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2004.00</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>8726.86</td>\n",
       "      <td>1396298.00</td>\n",
       "      <td>3443513.27</td>\n",
       "      <td>550962123.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2004.00</td>\n",
       "      <td>Ignorado</td>\n",
       "      <td>182.34</td>\n",
       "      <td>29175.00</td>\n",
       "      <td>48180.35</td>\n",
       "      <td>7708856.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2004.00</td>\n",
       "      <td>Masculino</td>\n",
       "      <td>12248.67</td>\n",
       "      <td>1959787.00</td>\n",
       "      <td>7074641.34</td>\n",
       "      <td>1131942614.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2005.00</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>9128.76</td>\n",
       "      <td>1460602.00</td>\n",
       "      <td>3937167.26</td>\n",
       "      <td>629946761.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2005.00</td>\n",
       "      <td>Ignorado</td>\n",
       "      <td>212.58</td>\n",
       "      <td>25510.00</td>\n",
       "      <td>59876.16</td>\n",
       "      <td>7185139.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2005.00</td>\n",
       "      <td>Masculino</td>\n",
       "      <td>12760.73</td>\n",
       "      <td>2041717.00</td>\n",
       "      <td>8124307.75</td>\n",
       "      <td>1299889240.05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Ano       Sexo Quantidade Benefícios Ativos             \\\n",
       "                                        Média/CID      Total   \n",
       "0 2004.00   Feminino                      8726.86 1396298.00   \n",
       "1 2004.00   Ignorado                       182.34   29175.00   \n",
       "2 2004.00  Masculino                     12248.67 1959787.00   \n",
       "3 2005.00   Feminino                      9128.76 1460602.00   \n",
       "4 2005.00   Ignorado                       212.58   25510.00   \n",
       "5 2005.00  Masculino                     12760.73 2041717.00   \n",
       "\n",
       "  Valor Benefícios Ativos (R$)                \n",
       "                     Média/CID         Total  \n",
       "0                   3443513.27  550962123.88  \n",
       "1                     48180.35    7708856.49  \n",
       "2                   7074641.34 1131942614.36  \n",
       "3                   3937167.26  629946761.14  \n",
       "4                     59876.16    7185139.31  \n",
       "5                   8124307.75 1299889240.05  "
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Agrupamento por mais de uma coluna e uso de agregações específicas para cada coluna\n",
    "agregacoes = {\n",
    "    'Quantidade Benefícios Ativos':{\n",
    "        'Média/CID': 'mean',\n",
    "        'Total': 'sum',\n",
    "    },\n",
    "    'Valor Benefícios Ativos (R$)':{\n",
    "        'Média/CID': 'mean',\n",
    "        'Total': 'sum',\n",
    "    }\n",
    "}\n",
    "\n",
    "df_ano_sexo = df[['Ano', 'Sexo', 'Quantidade Benefícios Ativos', 'Valor Benefícios Ativos (R$)']].groupby(['Ano', 'Sexo'], as_index=False).aggregate(agregacoes)\n",
    "df_ano_sexo.head(6)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Concat\n",
    "\n",
    "Utilizado para incluir as linhas ou colunas de um dataframe em outro."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame({\n",
    "    'A': list(np.random.randint(1, high=100, size=3)),\n",
    "    'B': list(np.random.randint(1, high=100, size=3)),\n",
    "})\n",
    "\n",
    "df2 = pd.DataFrame({\n",
    "    'A': list(np.random.randint(1, high=100, size=4)),\n",
    "    'B': list(np.random.randint(1, high=100, size=4)),\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>35</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12</td>\n",
       "      <td>63</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>55</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>75</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>53</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>43</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>83</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    A   B\n",
       "0  35  19\n",
       "1  12  63\n",
       "2  55  61\n",
       "3  75   2\n",
       "4  53  93\n",
       "5  43  81\n",
       "6  83  16"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df1, df2], ignore_index=True)"
   ]
  },
  {
   "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>A</th>\n",
       "      <th>B</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>35.00</td>\n",
       "      <td>19.00</td>\n",
       "      <td>75</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12.00</td>\n",
       "      <td>63.00</td>\n",
       "      <td>53</td>\n",
       "      <td>93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>55.00</td>\n",
       "      <td>61.00</td>\n",
       "      <td>43</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>83</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      A     B   A   B\n",
       "0 35.00 19.00  75   2\n",
       "1 12.00 63.00  53  93\n",
       "2 55.00 61.00  43  81\n",
       "3   nan   nan  83  16"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.concat([df1, df2], ignore_index=False, axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>Ano</th>\n",
       "      <th>Especialidade</th>\n",
       "      <th>Qtde Exames Complementares</th>\n",
       "      <th>Vlr Exames Complementares (R$)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>404</th>\n",
       "      <td>2011</td>\n",
       "      <td>Perícias Médicas (Credenciadas)</td>\n",
       "      <td>162481</td>\n",
       "      <td>3132612,00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>439</th>\n",
       "      <td>2012</td>\n",
       "      <td>Perícias Médicas (Credenciadas)</td>\n",
       "      <td>0</td>\n",
       "      <td>0,00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>440</th>\n",
       "      <td>2013</td>\n",
       "      <td>Perícias Médicas (Credenciadas)</td>\n",
       "      <td>28097</td>\n",
       "      <td>960041,00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>458</th>\n",
       "      <td>2014</td>\n",
       "      <td>Perícias Médicas (Credenciadas)</td>\n",
       "      <td>49171</td>\n",
       "      <td>1720977,00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>2015</td>\n",
       "      <td>Perícias Médicas (Credenciadas)</td>\n",
       "      <td>43425</td>\n",
       "      <td>1519875,00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Ano                    Especialidade  Qtde Exames Complementares  \\\n",
       "404  2011  Perícias Médicas (Credenciadas)                      162481   \n",
       "439  2012  Perícias Médicas (Credenciadas)                           0   \n",
       "440  2013  Perícias Médicas (Credenciadas)                       28097   \n",
       "458  2014  Perícias Médicas (Credenciadas)                       49171   \n",
       "476  2015  Perícias Médicas (Credenciadas)                       43425   \n",
       "\n",
       "    Vlr Exames Complementares (R$)  \n",
       "404                     3132612,00  \n",
       "439                           0,00  \n",
       "440                      960041,00  \n",
       "458                     1720977,00  \n",
       "476                     1519875,00  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pericias = pd.read_csv('dados/PMD04.csv', sep=',', encoding='latin1')\n",
    "df_pericias_credenciadas = df_pericias[df_pericias['Especialidade'].str.contains('Perí')]\n",
    "df_pericias_credenciadas.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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>Ano</th>\n",
       "      <th>Quantidade Benefícios Ativos</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2004.00</td>\n",
       "      <td>3385260.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2005.00</td>\n",
       "      <td>3527829.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2006.00</td>\n",
       "      <td>3694521.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2007.00</td>\n",
       "      <td>3573442.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2008.00</td>\n",
       "      <td>3624670.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Ano  Quantidade Benefícios Ativos\n",
       "0 2004.00                    3385260.00\n",
       "1 2005.00                    3527829.00\n",
       "2 2006.00                    3694521.00\n",
       "3 2007.00                    3573442.00\n",
       "4 2008.00                    3624670.00"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ano = df[['Ano', 'Quantidade Benefícios Ativos']].groupby(['Ano'], as_index=False).sum()\n",
    "df_ano.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "scrolled": true
   },
   "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>Ano</th>\n",
       "      <th>Quantidade Benefícios Ativos</th>\n",
       "      <th>Especialidade</th>\n",
       "      <th>Qtde Exames Complementares</th>\n",
       "      <th>Vlr Exames Complementares (R$)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011.00</td>\n",
       "      <td>4538149.00</td>\n",
       "      <td>Perícias Médicas (Credenciadas)</td>\n",
       "      <td>162481</td>\n",
       "      <td>3132612,00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2012.00</td>\n",
       "      <td>4635979.00</td>\n",
       "      <td>Perícias Médicas (Credenciadas)</td>\n",
       "      <td>0</td>\n",
       "      <td>0,00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2013.00</td>\n",
       "      <td>4828086.00</td>\n",
       "      <td>Perícias Médicas (Credenciadas)</td>\n",
       "      <td>28097</td>\n",
       "      <td>960041,00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2014.00</td>\n",
       "      <td>4975810.00</td>\n",
       "      <td>Perícias Médicas (Credenciadas)</td>\n",
       "      <td>49171</td>\n",
       "      <td>1720977,00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2015.00</td>\n",
       "      <td>4966702.00</td>\n",
       "      <td>Perícias Médicas (Credenciadas)</td>\n",
       "      <td>43425</td>\n",
       "      <td>1519875,00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Ano  Quantidade Benefícios Ativos                    Especialidade  \\\n",
       "0 2011.00                    4538149.00  Perícias Médicas (Credenciadas)   \n",
       "1 2012.00                    4635979.00  Perícias Médicas (Credenciadas)   \n",
       "2 2013.00                    4828086.00  Perícias Médicas (Credenciadas)   \n",
       "3 2014.00                    4975810.00  Perícias Médicas (Credenciadas)   \n",
       "4 2015.00                    4966702.00  Perícias Médicas (Credenciadas)   \n",
       "\n",
       "   Qtde Exames Complementares Vlr Exames Complementares (R$)  \n",
       "0                      162481                     3132612,00  \n",
       "1                           0                           0,00  \n",
       "2                       28097                      960041,00  \n",
       "3                       49171                     1720977,00  \n",
       "4                       43425                     1519875,00  "
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge = pd.merge(df_ano, df_pericias_credenciadas, how='inner', left_on='Ano', right_on='Ano')\n",
    "df_merge.head()"
   ]
  },
  {
   "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>Ano</th>\n",
       "      <th>Quantidade Benefícios Ativos</th>\n",
       "      <th>Especialidade</th>\n",
       "      <th>Qtde Exames Complementares</th>\n",
       "      <th>Vlr Exames Complementares (R$)</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2004.00</td>\n",
       "      <td>3385260.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2005.00</td>\n",
       "      <td>3527829.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2006.00</td>\n",
       "      <td>3694521.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2007.00</td>\n",
       "      <td>3573442.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2008.00</td>\n",
       "      <td>3624670.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2009.00</td>\n",
       "      <td>3576679.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2010.00</td>\n",
       "      <td>4442648.00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>nan</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2011.00</td>\n",
       "      <td>4538149.00</td>\n",
       "      <td>Perícias Médicas (Credenciadas)</td>\n",
       "      <td>162481.00</td>\n",
       "      <td>3132612,00</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2012.00</td>\n",
       "      <td>4635979.00</td>\n",
       "      <td>Perícias Médicas (Credenciadas)</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0,00</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2013.00</td>\n",
       "      <td>4828086.00</td>\n",
       "      <td>Perícias Médicas (Credenciadas)</td>\n",
       "      <td>28097.00</td>\n",
       "      <td>960041,00</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Ano  Quantidade Benefícios Ativos                    Especialidade  \\\n",
       "0 2004.00                    3385260.00                              NaN   \n",
       "1 2005.00                    3527829.00                              NaN   \n",
       "2 2006.00                    3694521.00                              NaN   \n",
       "3 2007.00                    3573442.00                              NaN   \n",
       "4 2008.00                    3624670.00                              NaN   \n",
       "5 2009.00                    3576679.00                              NaN   \n",
       "6 2010.00                    4442648.00                              NaN   \n",
       "7 2011.00                    4538149.00  Perícias Médicas (Credenciadas)   \n",
       "8 2012.00                    4635979.00  Perícias Médicas (Credenciadas)   \n",
       "9 2013.00                    4828086.00  Perícias Médicas (Credenciadas)   \n",
       "\n",
       "   Qtde Exames Complementares Vlr Exames Complementares (R$)     _merge  \n",
       "0                         nan                            NaN  left_only  \n",
       "1                         nan                            NaN  left_only  \n",
       "2                         nan                            NaN  left_only  \n",
       "3                         nan                            NaN  left_only  \n",
       "4                         nan                            NaN  left_only  \n",
       "5                         nan                            NaN  left_only  \n",
       "6                         nan                            NaN  left_only  \n",
       "7                   162481.00                     3132612,00       both  \n",
       "8                        0.00                           0,00       both  \n",
       "9                    28097.00                      960041,00       both  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merge = pd.merge(df_ano, df_pericias_credenciadas, how='left', left_on='Ano', right_on='Ano', indicator=True)\n",
    "df_merge.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pivot Table"
   ]
  },
  {
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>Ano</th>\n",
       "      <th>Sexo</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Quantidade Benefícios Ativos</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Valor Benefícios Ativos (R$)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Média/CID</th>\n",
       "      <th>Total</th>\n",
       "      <th>Média/CID</th>\n",
       "      <th>Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2004.00</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>8726.86</td>\n",
       "      <td>1396298.00</td>\n",
       "      <td>3443513.27</td>\n",
       "      <td>550962123.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2004.00</td>\n",
       "      <td>Ignorado</td>\n",
       "      <td>182.34</td>\n",
       "      <td>29175.00</td>\n",
       "      <td>48180.35</td>\n",
       "      <td>7708856.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2004.00</td>\n",
       "      <td>Masculino</td>\n",
       "      <td>12248.67</td>\n",
       "      <td>1959787.00</td>\n",
       "      <td>7074641.34</td>\n",
       "      <td>1131942614.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2005.00</td>\n",
       "      <td>Feminino</td>\n",
       "      <td>9128.76</td>\n",
       "      <td>1460602.00</td>\n",
       "      <td>3937167.26</td>\n",
       "      <td>629946761.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2005.00</td>\n",
       "      <td>Ignorado</td>\n",
       "      <td>212.58</td>\n",
       "      <td>25510.00</td>\n",
       "      <td>59876.16</td>\n",
       "      <td>7185139.31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Ano       Sexo Quantidade Benefícios Ativos             \\\n",
       "                                        Média/CID      Total   \n",
       "0 2004.00   Feminino                      8726.86 1396298.00   \n",
       "1 2004.00   Ignorado                       182.34   29175.00   \n",
       "2 2004.00  Masculino                     12248.67 1959787.00   \n",
       "3 2005.00   Feminino                      9128.76 1460602.00   \n",
       "4 2005.00   Ignorado                       212.58   25510.00   \n",
       "\n",
       "  Valor Benefícios Ativos (R$)                \n",
       "                     Média/CID         Total  \n",
       "0                   3443513.27  550962123.88  \n",
       "1                     48180.35    7708856.49  \n",
       "2                   7074641.34 1131942614.36  \n",
       "3                   3937167.26  629946761.14  \n",
       "4                     59876.16    7185139.31  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_ano_sexo.head()"
   ]
  },
  {
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"6\" halign=\"left\">Quantidade Benefícios Ativos</th>\n",
       "      <th colspan=\"6\" halign=\"left\">Valor Benefícios Ativos (R$)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">Média/CID</th>\n",
       "      <th colspan=\"3\" halign=\"left\">Total</th>\n",
       "      <th colspan=\"3\" halign=\"left\">Média/CID</th>\n",
       "      <th colspan=\"3\" halign=\"left\">Total</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sexo</th>\n",
       "      <th>Feminino</th>\n",
       "      <th>Ignorado</th>\n",
       "      <th>Masculino</th>\n",
       "      <th>Feminino</th>\n",
       "      <th>Ignorado</th>\n",
       "      <th>Masculino</th>\n",
       "      <th>Feminino</th>\n",
       "      <th>Ignorado</th>\n",
       "      <th>Masculino</th>\n",
       "      <th>Feminino</th>\n",
       "      <th>Ignorado</th>\n",
       "      <th>Masculino</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ano</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2004.00</th>\n",
       "      <td>8726.86</td>\n",
       "      <td>182.34</td>\n",
       "      <td>12248.67</td>\n",
       "      <td>1396298.00</td>\n",
       "      <td>29175.00</td>\n",
       "      <td>1959787.00</td>\n",
       "      <td>3443513.27</td>\n",
       "      <td>48180.35</td>\n",
       "      <td>7074641.34</td>\n",
       "      <td>550962123.88</td>\n",
       "      <td>7708856.49</td>\n",
       "      <td>1131942614.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2005.00</th>\n",
       "      <td>9128.76</td>\n",
       "      <td>212.58</td>\n",
       "      <td>12760.73</td>\n",
       "      <td>1460602.00</td>\n",
       "      <td>25510.00</td>\n",
       "      <td>2041717.00</td>\n",
       "      <td>3937167.26</td>\n",
       "      <td>59876.16</td>\n",
       "      <td>8124307.75</td>\n",
       "      <td>629946761.14</td>\n",
       "      <td>7185139.31</td>\n",
       "      <td>1299889240.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006.00</th>\n",
       "      <td>162.97</td>\n",
       "      <td>163.43</td>\n",
       "      <td>185.99</td>\n",
       "      <td>1528213.00</td>\n",
       "      <td>22553.00</td>\n",
       "      <td>2143755.00</td>\n",
       "      <td>75864.13</td>\n",
       "      <td>48308.95</td>\n",
       "      <td>127508.52</td>\n",
       "      <td>711377942.46</td>\n",
       "      <td>6666634.55</td>\n",
       "      <td>1469663190.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2007.00</th>\n",
       "      <td>64.46</td>\n",
       "      <td>122.10</td>\n",
       "      <td>71.25</td>\n",
       "      <td>1461801.00</td>\n",
       "      <td>19903.00</td>\n",
       "      <td>2091738.00</td>\n",
       "      <td>31182.93</td>\n",
       "      <td>37458.39</td>\n",
       "      <td>50891.53</td>\n",
       "      <td>707197617.02</td>\n",
       "      <td>6105717.53</td>\n",
       "      <td>1494073565.10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008.00</th>\n",
       "      <td>66.97</td>\n",
       "      <td>8.82</td>\n",
       "      <td>74.53</td>\n",
       "      <td>1494278.00</td>\n",
       "      <td>3536.00</td>\n",
       "      <td>2126856.00</td>\n",
       "      <td>34554.57</td>\n",
       "      <td>2903.68</td>\n",
       "      <td>56696.30</td>\n",
       "      <td>770981564.48</td>\n",
       "      <td>1164373.89</td>\n",
       "      <td>1617828911.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009.00</th>\n",
       "      <td>67.70</td>\n",
       "      <td>7.78</td>\n",
       "      <td>74.82</td>\n",
       "      <td>1480592.00</td>\n",
       "      <td>3012.00</td>\n",
       "      <td>2093075.00</td>\n",
       "      <td>37165.55</td>\n",
       "      <td>2750.05</td>\n",
       "      <td>60920.50</td>\n",
       "      <td>812773501.74</td>\n",
       "      <td>1064269.42</td>\n",
       "      <td>1704311972.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010.00</th>\n",
       "      <td>80.62</td>\n",
       "      <td>7.81</td>\n",
       "      <td>82.28</td>\n",
       "      <td>1805323.00</td>\n",
       "      <td>16456.00</td>\n",
       "      <td>2620869.00</td>\n",
       "      <td>54165.76</td>\n",
       "      <td>4056.34</td>\n",
       "      <td>72610.78</td>\n",
       "      <td>1212933871.90</td>\n",
       "      <td>8542652.36</td>\n",
       "      <td>2312943671.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011.00</th>\n",
       "      <td>83.49</td>\n",
       "      <td>6.98</td>\n",
       "      <td>85.17</td>\n",
       "      <td>1857564.00</td>\n",
       "      <td>13062.00</td>\n",
       "      <td>2667523.00</td>\n",
       "      <td>59983.30</td>\n",
       "      <td>3879.30</td>\n",
       "      <td>80679.28</td>\n",
       "      <td>1334508538.88</td>\n",
       "      <td>7258177.08</td>\n",
       "      <td>2526794264.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012.00</th>\n",
       "      <td>86.90</td>\n",
       "      <td>6.54</td>\n",
       "      <td>88.45</td>\n",
       "      <td>1909594.00</td>\n",
       "      <td>11407.00</td>\n",
       "      <td>2714978.00</td>\n",
       "      <td>68917.41</td>\n",
       "      <td>4146.00</td>\n",
       "      <td>91120.93</td>\n",
       "      <td>1514460105.55</td>\n",
       "      <td>7226469.99</td>\n",
       "      <td>2796865926.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013.00</th>\n",
       "      <td>92.77</td>\n",
       "      <td>6.12</td>\n",
       "      <td>93.49</td>\n",
       "      <td>2008280.00</td>\n",
       "      <td>9846.00</td>\n",
       "      <td>2809960.00</td>\n",
       "      <td>79945.01</td>\n",
       "      <td>4223.12</td>\n",
       "      <td>104722.94</td>\n",
       "      <td>1730649505.71</td>\n",
       "      <td>6795006.38</td>\n",
       "      <td>3147447999.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014.00</th>\n",
       "      <td>97.95</td>\n",
       "      <td>5.68</td>\n",
       "      <td>97.96</td>\n",
       "      <td>2087519.00</td>\n",
       "      <td>8559.00</td>\n",
       "      <td>2879732.00</td>\n",
       "      <td>89948.29</td>\n",
       "      <td>4180.95</td>\n",
       "      <td>116775.42</td>\n",
       "      <td>1917067874.37</td>\n",
       "      <td>6304873.51</td>\n",
       "      <td>3432846944.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015.00</th>\n",
       "      <td>100.76</td>\n",
       "      <td>5.38</td>\n",
       "      <td>99.93</td>\n",
       "      <td>2095713.00</td>\n",
       "      <td>7620.00</td>\n",
       "      <td>2863369.00</td>\n",
       "      <td>99596.26</td>\n",
       "      <td>4306.53</td>\n",
       "      <td>127828.57</td>\n",
       "      <td>2071602281.85</td>\n",
       "      <td>6098040.62</td>\n",
       "      <td>3662671987.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016.00</th>\n",
       "      <td>104.24</td>\n",
       "      <td>4.97</td>\n",
       "      <td>103.85</td>\n",
       "      <td>2122685.00</td>\n",
       "      <td>6367.00</td>\n",
       "      <td>2889412.00</td>\n",
       "      <td>114766.93</td>\n",
       "      <td>4449.22</td>\n",
       "      <td>148105.02</td>\n",
       "      <td>2337113757.98</td>\n",
       "      <td>5699455.60</td>\n",
       "      <td>4120726031.85</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Quantidade Benefícios Ativos                                         \\\n",
       "                           Média/CID                         Total            \n",
       "Sexo                        Feminino Ignorado Masculino   Feminino Ignorado   \n",
       "Ano                                                                           \n",
       "2004.00                      8726.86   182.34  12248.67 1396298.00 29175.00   \n",
       "2005.00                      9128.76   212.58  12760.73 1460602.00 25510.00   \n",
       "2006.00                       162.97   163.43    185.99 1528213.00 22553.00   \n",
       "2007.00                        64.46   122.10     71.25 1461801.00 19903.00   \n",
       "2008.00                        66.97     8.82     74.53 1494278.00  3536.00   \n",
       "2009.00                        67.70     7.78     74.82 1480592.00  3012.00   \n",
       "2010.00                        80.62     7.81     82.28 1805323.00 16456.00   \n",
       "2011.00                        83.49     6.98     85.17 1857564.00 13062.00   \n",
       "2012.00                        86.90     6.54     88.45 1909594.00 11407.00   \n",
       "2013.00                        92.77     6.12     93.49 2008280.00  9846.00   \n",
       "2014.00                        97.95     5.68     97.96 2087519.00  8559.00   \n",
       "2015.00                       100.76     5.38     99.93 2095713.00  7620.00   \n",
       "2016.00                       104.24     4.97    103.85 2122685.00  6367.00   \n",
       "\n",
       "                   Valor Benefícios Ativos (R$)                      \\\n",
       "                                      Média/CID                       \n",
       "Sexo     Masculino                     Feminino Ignorado  Masculino   \n",
       "Ano                                                                   \n",
       "2004.00 1959787.00                   3443513.27 48180.35 7074641.34   \n",
       "2005.00 2041717.00                   3937167.26 59876.16 8124307.75   \n",
       "2006.00 2143755.00                     75864.13 48308.95  127508.52   \n",
       "2007.00 2091738.00                     31182.93 37458.39   50891.53   \n",
       "2008.00 2126856.00                     34554.57  2903.68   56696.30   \n",
       "2009.00 2093075.00                     37165.55  2750.05   60920.50   \n",
       "2010.00 2620869.00                     54165.76  4056.34   72610.78   \n",
       "2011.00 2667523.00                     59983.30  3879.30   80679.28   \n",
       "2012.00 2714978.00                     68917.41  4146.00   91120.93   \n",
       "2013.00 2809960.00                     79945.01  4223.12  104722.94   \n",
       "2014.00 2879732.00                     89948.29  4180.95  116775.42   \n",
       "2015.00 2863369.00                     99596.26  4306.53  127828.57   \n",
       "2016.00 2889412.00                    114766.93  4449.22  148105.02   \n",
       "\n",
       "                                                \n",
       "                Total                           \n",
       "Sexo         Feminino   Ignorado     Masculino  \n",
       "Ano                                             \n",
       "2004.00  550962123.88 7708856.49 1131942614.36  \n",
       "2005.00  629946761.14 7185139.31 1299889240.05  \n",
       "2006.00  711377942.46 6666634.55 1469663190.91  \n",
       "2007.00  707197617.02 6105717.53 1494073565.10  \n",
       "2008.00  770981564.48 1164373.89 1617828911.91  \n",
       "2009.00  812773501.74 1064269.42 1704311972.81  \n",
       "2010.00 1212933871.90 8542652.36 2312943671.60  \n",
       "2011.00 1334508538.88 7258177.08 2526794264.81  \n",
       "2012.00 1514460105.55 7226469.99 2796865926.42  \n",
       "2013.00 1730649505.71 6795006.38 3147447999.11  \n",
       "2014.00 1917067874.37 6304873.51 3432846944.00  \n",
       "2015.00 2071602281.85 6098040.62 3662671987.30  \n",
       "2016.00 2337113757.98 5699455.60 4120726031.85  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot_table(df_ano_sexo, index='Ano', columns='Sexo')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Melt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>Dia da Semana</th>\n",
       "      <th>Operador 1</th>\n",
       "      <th>Operador 2</th>\n",
       "      <th>Operador 3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Segunda</td>\n",
       "      <td>14</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Terça</td>\n",
       "      <td>6</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Quarta</td>\n",
       "      <td>8</td>\n",
       "      <td>17</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Quinta</td>\n",
       "      <td>4</td>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Sexta</td>\n",
       "      <td>13</td>\n",
       "      <td>9</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Dia da Semana  Operador 1  Operador 2  Operador 3\n",
       "0       Segunda          14          12           4\n",
       "1         Terça           6           4           2\n",
       "2        Quarta           8          17           7\n",
       "3        Quinta           4           8           6\n",
       "4         Sexta          13           9           7"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "calendario = {'Dia da Semana': [\"Segunda\", \"Terça\", \"Quarta\", \"Quinta\", \"Sexta\", \"Sábado\", \"Domingo\"],\n",
    "        'Operador 1': [14, 6, 8, 4, 13, 6, 4],\n",
    "        'Operador 2': [12, 4, 17, 8, 9, 12, 5],\n",
    "        'Operador 3': [4, 2, 7, 6, 7, 9, 13]}\n",
    "df_calendario = pd.DataFrame(calendario)\n",
    "df_calendario.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>Dia da Semana</th>\n",
       "      <th>Operador</th>\n",
       "      <th>Atendimentos</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Segunda</td>\n",
       "      <td>Operador 1</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Terça</td>\n",
       "      <td>Operador 1</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Quarta</td>\n",
       "      <td>Operador 1</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Quinta</td>\n",
       "      <td>Operador 1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Sexta</td>\n",
       "      <td>Operador 1</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Dia da Semana    Operador  Atendimentos\n",
       "0       Segunda  Operador 1            14\n",
       "1         Terça  Operador 1             6\n",
       "2        Quarta  Operador 1             8\n",
       "3        Quinta  Operador 1             4\n",
       "4         Sexta  Operador 1            13"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_melt = pd.melt(df_calendario, id_vars=[\"Dia da Semana\"], var_name=\"Operador\", value_name=\"Atendimentos\")\n",
    "df_melt.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Preenchimento de Valores Faltantes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    nan\n",
       "1   0.50\n",
       "2   2.00\n",
       "3    inf\n",
       "4   -inf\n",
       "5    nan\n",
       "dtype: float64"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\"\"\"\n",
    "O que o pandas considera como valores faltantes?\n",
    "\"\"\"\n",
    "\n",
    "# padrão do pandas é False\n",
    "pd.options.mode.use_inf_as_na = False \n",
    "a = pd.Series([np.NaN,1,2,3,-5, None])\n",
    "b = pd.Series([3,2,1,0,0, 1])\n",
    "c = a / b\n",
    "c"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     True\n",
       "1    False\n",
       "2    False\n",
       "3    False\n",
       "4    False\n",
       "5     True\n",
       "dtype: bool"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c.isnull()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     True\n",
       "1    False\n",
       "2    False\n",
       "3     True\n",
       "4     True\n",
       "5     True\n",
       "dtype: bool"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.options.mode.use_inf_as_na = True\n",
    "c.isnull()"
   ]
  },
  {
   "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>Original</th>\n",
       "      <th>fillna</th>\n",
       "      <th>ffill</th>\n",
       "      <th>bfill</th>\n",
       "      <th>interpolate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>2.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>nan</td>\n",
       "      <td>3.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "      <td>nan</td>\n",
       "      <td>5.00</td>\n",
       "      <td>4.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>5.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "      <td>5.00</td>\n",
       "      <td>7.00</td>\n",
       "      <td>6.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7.00</td>\n",
       "      <td>7.00</td>\n",
       "      <td>7.00</td>\n",
       "      <td>7.00</td>\n",
       "      <td>7.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "      <td>7.00</td>\n",
       "      <td>9.00</td>\n",
       "      <td>8.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9.00</td>\n",
       "      <td>9.00</td>\n",
       "      <td>9.00</td>\n",
       "      <td>9.00</td>\n",
       "      <td>9.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>nan</td>\n",
       "      <td>0.00</td>\n",
       "      <td>9.00</td>\n",
       "      <td>11.00</td>\n",
       "      <td>10.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11.00</td>\n",
       "      <td>11.00</td>\n",
       "      <td>11.00</td>\n",
       "      <td>11.00</td>\n",
       "      <td>11.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Original  fillna  ffill  bfill  interpolate\n",
       "0       1.00    1.00   1.00   1.00         1.00\n",
       "1        nan    0.00   1.00    nan         2.00\n",
       "2        nan    0.00    nan    nan         3.00\n",
       "3        nan    0.00    nan   5.00         4.00\n",
       "4       5.00    5.00   5.00   5.00         5.00\n",
       "5        nan    0.00   5.00   7.00         6.00\n",
       "6       7.00    7.00   7.00   7.00         7.00\n",
       "7        nan    0.00   7.00   9.00         8.00\n",
       "8       9.00    9.00   9.00   9.00         9.00\n",
       "9        nan    0.00   9.00  11.00        10.00\n",
       "10     11.00   11.00  11.00  11.00        11.00"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lista = [1,np.nan,np.nan,np.nan,5,np.nan,7,np.nan,9,np.nan,11]\n",
    "\n",
    "pd.DataFrame({\n",
    "    'Original': pd.Series(lista),\n",
    "    'fillna': pd.Series(lista).fillna(0),\n",
    "    'ffill': pd.Series(lista).ffill(limit=1),\n",
    "    'bfill': pd.Series(lista).bfill(limit=1),    \n",
    "    'interpolate': pd.Series(lista).interpolate(),\n",
    "})   \n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "lista = [10,np.nan,np.nan,np.nan,5,np.nan,7,np.nan,3,np.nan,11]\n",
    "\n",
    "df = pd.DataFrame({\n",
    "    'Original': pd.Series(lista),\n",
    "    'fillna': pd.Series(lista).fillna(0),\n",
    "    'ffill': pd.Series(lista).ffill(limit=1),\n",
    "    'bfill': pd.Series(lista).bfill(limit=1),    \n",
    "    'interpolate': pd.Series(lista).interpolate(),\n",
    "})  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Importação de Dados com o Pandas\n",
    "\n",
    "O pandas possui diversas funcionalidades auxiliares para importar dados de diversas fontes de maneira simples. Essas funcionalidades podem ser combinadas com outras bibliotecas e funções do python para acessarmos praticamente qualquer fonte de dados.\n",
    "\n",
    "Vamos ver alguns casos bastante comuns sem a pretensão de esgotar o assunto."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Arquivos delimitados\n",
    "\n",
    "Arquivos delimitados podem ser lidos e transforados em DataFrames utilizando a função __pd.read_csv()__. Entretanto, estes aqrquivo possuem algumas características próprias. Geralmente temos um caracter separador de colunas e cada linha representa um registro. Ainda assim, há casos em que o separador de linhas não é uma quebra de linha. Há também casos em que números e datas não estão no formato correto para que o pandas faça o conversão para o tipo de dados correto.\n",
    "\n",
    "Assim, descrevemos parâmetros desta função que ajudam a resolver os principais problemas. \n",
    "\n",
    "* __encoding__: cada arquivo texto possui um encoding. O encoding define o formato utilizado para representar os caracteres de um arquivo texto. O encoding padrão do python é 'utf8'. Entretanto, é comum ver arquivos em encodings diferentes para representar caracteres da língua portuguesa. Os mais comuns são: 'iso-8859-1', 'latin1' e 'cp1252'. O principal problema de encoding errado é percebido na forma como os caracteres acentuadas são exibidos. Mais detalhes em: https://docs.python.org/3.6/library/codecs.html#standard-encodings\n",
    "\n",
    "* __thousands__ e __decimal__: são utilizados para informar em qual formato os dados numéricos estão representados no arquivo. Utiliza-se __thousands__ para informar o separador de milhar e __decimal__ para informar o separador de milhares\n",
    "\n",
    "* __parse_dates__ e __dayfirst__: utilizados para informar se datas devem ser convertidas para o formato de datas do python.\n",
    "\n",
    "* __iterator__ e __chunksize__: utilizados para definiar um carregamento em blocos para casos onde os dados são muito maiores do que a memória."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>Valor</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Data</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2019-02-13</th>\n",
       "      <td>80299.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-08-30</th>\n",
       "      <td>30787.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-05-26</th>\n",
       "      <td>82908.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-02</th>\n",
       "      <td>75336.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2019-06-21</th>\n",
       "      <td>16934.76</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Valor\n",
       "Data               \n",
       "2019-02-13 80299.81\n",
       "2019-08-30 30787.57\n",
       "2019-05-26 82908.59\n",
       "2018-07-02 75336.25\n",
       "2019-06-21 16934.76"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# exemplo completo\n",
    "arquivo = 'dados/exemplo_delimitado.zip'\n",
    "df_exemplo_delimitado = pd.read_csv(\n",
    "    arquivo, \n",
    "    sep=';', \n",
    "    thousands='.', \n",
    "    decimal=',', \n",
    "    parse_dates=True, \n",
    "    dayfirst=True, \n",
    "    index_col='Data',\n",
    "    )\n",
    "df_exemplo_delimitado.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>Valor</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Data</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-07-02</th>\n",
       "      <td>75336.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-03</th>\n",
       "      <td>10524.73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-26</th>\n",
       "      <td>51842.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-25</th>\n",
       "      <td>7190.29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-20</th>\n",
       "      <td>17242.60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              Valor\n",
       "Data               \n",
       "2018-07-02 75336.25\n",
       "2018-06-03 10524.73\n",
       "2018-07-26 51842.44\n",
       "2018-06-25  7190.29\n",
       "2018-07-20 17242.60"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# consulta fatiando pelo índice\n",
    "df_exemplo_delimitado.loc['2018-06':'2018-07'].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "DatetimeIndex: 100000 entries, 2019-02-13 to 2018-11-01\n",
      "Data columns (total 1 columns):\n",
      "Valor    100000 non-null float64\n",
      "dtypes: float64(1)\n",
      "memory usage: 4.0 MB\n"
     ]
    }
   ],
   "source": [
    "# detalhes sobre os tipos convertidos\n",
    "df_exemplo_delimitado.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>Valor</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Data</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-05-31</th>\n",
       "      <td>303285672.99</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-06-30</th>\n",
       "      <td>305561462.09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-07-31</th>\n",
       "      <td>310799198.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-08-31</th>\n",
       "      <td>307770016.93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-09-30</th>\n",
       "      <td>292280527.94</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  Valor\n",
       "Data                   \n",
       "2018-05-31 303285672.99\n",
       "2018-06-30 305561462.09\n",
       "2018-07-31 310799198.36\n",
       "2018-08-31 307770016.93\n",
       "2018-09-30 292280527.94"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# reorganização dos dados por mês, com a soma mensal\n",
    "df_exemplo_delimitado.resample('1M').sum().sort_index().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Bloco 1, Soma parcial: 502357350.05\n",
      "Bloco 2, Soma parcial: 1005129782.91\n",
      "Bloco 3, Soma parcial: 1508380506.49\n",
      "Bloco 4, Soma parcial: 2009881810.00\n",
      "Bloco 5, Soma parcial: 2514613689.05\n",
      "Bloco 6, Soma parcial: 3015215014.32\n",
      "Bloco 7, Soma parcial: 3515577625.84\n",
      "Bloco 8, Soma parcial: 4015867740.46\n",
      "Bloco 9, Soma parcial: 4519566546.25\n",
      "Bloco 10, Soma parcial: 5017208156.37\n"
     ]
    }
   ],
   "source": [
    "# Exemplo de leitura de um arquivo de 100K registros \n",
    "# em blocos de 10K.\n",
    "reader = pd.read_csv(\n",
    "    arquivo, \n",
    "    sep=';', \n",
    "    thousands='.', \n",
    "    decimal=',', \n",
    "    parse_dates=True, \n",
    "    dayfirst=True, \n",
    "    index_col='Data',\n",
    "    chunksize=10000,\n",
    "    iterator=True,\n",
    "    )\n",
    "\n",
    "somas = 0\n",
    "for idx, df_parcial in enumerate(reader):\n",
    "    somas += df_parcial['Valor'].sum()\n",
    "    print(\"Bloco {}, Soma parcial: {:0.2f}\".format(idx+1, somas))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Arquivos Excel\n",
    "\n",
    "O pandas também possui uma função espcífica para importar dados de arquivos excel."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "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>Data</th>\n",
       "      <th>Valor</th>\n",
       "      <th>Descrição</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019-02-13</td>\n",
       "      <td>80299.81</td>\n",
       "      <td>Uma descrição qualquer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019-08-30</td>\n",
       "      <td>30787.57</td>\n",
       "      <td>Uma descrição qualquer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-05-26</td>\n",
       "      <td>82908.59</td>\n",
       "      <td>Uma descrição qualquer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-07-02</td>\n",
       "      <td>75336.25</td>\n",
       "      <td>Uma descrição qualquer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019-06-21</td>\n",
       "      <td>16934.76</td>\n",
       "      <td>Uma descrição qualquer</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Data    Valor               Descrição\n",
       "0 2019-02-13 80299.81  Uma descrição qualquer\n",
       "1 2019-08-30 30787.57  Uma descrição qualquer\n",
       "2 2019-05-26 82908.59  Uma descrição qualquer\n",
       "3 2018-07-02 75336.25  Uma descrição qualquer\n",
       "4 2019-06-21 16934.76  Uma descrição qualquer"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# leitura de um arquivo excel\n",
    "df_exemplo_excel = pd.read_excel('dados/exemplo_delimitado.xlsx')\n",
    "df_exemplo_excel.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 99 entries, 0 to 98\n",
      "Data columns (total 3 columns):\n",
      "Data         99 non-null datetime64[ns]\n",
      "Valor        99 non-null float64\n",
      "Descrição    99 non-null object\n",
      "dtypes: datetime64[ns](1), float64(1), object(1)\n",
      "memory usage: 2.4+ KB\n"
     ]
    }
   ],
   "source": [
    "df_exemplo_excel.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Bancos de Dados\n",
    "\n",
    "Existem várias formas de conectar em Sistemas de Bancos de Dados utilizando python e então converter os resultados em DataFrames. Abordaremos aqui umas das formas mais simples.\n",
    "\n",
    "Utilizaremos uma biblioteca chamada sqlalchemy.\n",
    "\n",
    "\n",
    "Exemplos de conexões com sqlalchemy para os principais bancos de dados podem ser encontrados aqui: http://docs.sqlalchemy.org/en/latest/core/engines.html.\n",
    "\n",
    "Além do sqlalchemy é necessário ter a biblioteca (também chamada de conector ou driver) do banco de dados que será utilizado."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "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>Data</th>\n",
       "      <th>Valor</th>\n",
       "      <th>Descrição</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>07/11/2018</td>\n",
       "      <td>1728.49</td>\n",
       "      <td>Decrição produto 4157</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12/06/2018</td>\n",
       "      <td>9607.77</td>\n",
       "      <td>Decrição produto 5831</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>03/10/2018</td>\n",
       "      <td>1197.53</td>\n",
       "      <td>Decrição produto 6348</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12/06/2018</td>\n",
       "      <td>9172.97</td>\n",
       "      <td>Decrição produto 6600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>19/08/2019</td>\n",
       "      <td>1845.59</td>\n",
       "      <td>Decrição produto 6570</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Data   Valor              Descrição\n",
       "0  07/11/2018 1728.49  Decrição produto 4157\n",
       "1  12/06/2018 9607.77  Decrição produto 5831\n",
       "2  03/10/2018 1197.53  Decrição produto 6348\n",
       "3  12/06/2018 9172.97  Decrição produto 6600\n",
       "4  19/08/2019 1845.59  Decrição produto 6570"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import create_engine\n",
    "arquivo = os.path.join('arquivos','exemplo_sqlite.db')\n",
    "\n",
    "engine = create_engine('sqlite:///dados/exemplo_sqlite.db')\n",
    "\n",
    "query = \"select * from tb_exemplo_sqlite\"\n",
    "df_sqlite = pd.read_sql(sql=query, con=engine)\n",
    "df_sqlite.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "O exemplo acima funcionaria para um banco SQLServer, por exemplo, fazendo os seguites passos:\n",
    "\n",
    "1. Instalar o sqlalchemy e o pyodbc\n",
    "```bash\n",
    "pip install sqlalchemy pyodbc\n",
    "```\n",
    "\n",
    "2. Criar uma conexão odbc com o banco de dados na máquina onde o jupyter está rodando e dar um nome para ela. Ex: DSN_TESTE\n",
    "\n",
    "3. Fazer uma conexão utlizando o sqlalchemy.\n",
    "```python\n",
    "engine = create_engine('mssql+pyodbc://<usuario>:<senha>@DSN_TESTE')\n",
    "df_mssql = pd.read_sql(sql=query, con=engine)\n",
    "df_mssql.head()\n",
    "```\n",
    "\n",
    "\n",
    "\n",
    "#### #python \n",
    "* para mais detalhes sobre o sqlalchemy veja http://docs.sqlalchemy.org/en/latest/index.html"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Parquet\n",
    "\n",
    "Formato de armazenamento baseado em colunas que armazena os dados de forma compactada além de preservar os metadas dos tipos de cada coluna. Bastante utilizado no ecosistema hadoop."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: pyarrow in c:\\anaconda3\\envs\\curso_08_05_teste\\lib\\site-packages (0.13.0)\n",
      "Requirement already satisfied: numpy>=1.14 in c:\\anaconda3\\envs\\curso_08_05_teste\\lib\\site-packages (from pyarrow) (1.16.4)\n",
      "Requirement already satisfied: six>=1.0.0 in c:\\anaconda3\\envs\\curso_08_05_teste\\lib\\site-packages (from pyarrow) (1.12.0)\n"
     ]
    }
   ],
   "source": [
    "!pip install pyarrow"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 8.98 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "df_parquet = pd.read_parquet('dados/arq_municipios_fronteiricos.parquet')\n",
    "df_parquet.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "data": {
      "application/javascript": [
       "\n",
       "(function() {var css = [\n",
       "    \".container {width: 100%;}\",\n",
       "    \".text_cell_render table {font-size: 24;}\",\n",
       "    \".rendered_html code {background-color: #fafafa;}\",\n",
       "    \".rendered_html :not(pre) > code {padding: 1px 1px;}\",\n",
       "    \".rendered_html pre code {background-color: #fafafa;}\",\n",
       "    \".rendered_html pre {border: 1px;background-color: #fafafa;padding: .5ex .5em;}\"\n",
       "].join(\"\\n\");\n",
       "if (typeof GM_addStyle != \"undefined\") {\n",
       "    GM_addStyle(css);\n",
       "} else if (typeof PRO_addStyle != \"undefined\") {\n",
       "    PRO_addStyle(css);\n",
       "} else if (typeof addStyle != \"undefined\") {\n",
       "    addStyle(css);\n",
       "} else {\n",
       "    var node = document.createElement(\"style\");\n",
       "    node.type = \"text/css\";\n",
       "    node.appendChild(document.createTextNode(css));\n",
       "    var heads = document.getElementsByTagName(\"head\");\n",
       "    if (heads.length > 0) {\n",
       "        heads[0].appendChild(node);\n",
       "    } else {\n",
       "        document.documentElement.appendChild(node);\n",
       "    }\n",
       "}\n",
       "})();\n"
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%javascript\n",
    "\n",
    "(function() {var css = [\n",
    "    \".container {width: 100%;}\",\n",
    "    \".text_cell_render table {font-size: 24;}\",\n",
    "    \".rendered_html code {background-color: #fafafa;}\",\n",
    "    \".rendered_html :not(pre) > code {padding: 1px 1px;}\",\n",
    "    \".rendered_html pre code {background-color: #fafafa;}\",\n",
    "    \".rendered_html pre {border: 1px;background-color: #fafafa;padding: .5ex .5em;}\"\n",
    "].join(\"\\n\");\n",
    "if (typeof GM_addStyle != \"undefined\") {\n",
    "    GM_addStyle(css);\n",
    "} else if (typeof PRO_addStyle != \"undefined\") {\n",
    "    PRO_addStyle(css);\n",
    "} else if (typeof addStyle != \"undefined\") {\n",
    "    addStyle(css);\n",
    "} else {\n",
    "    var node = document.createElement(\"style\");\n",
    "    node.type = \"text/css\";\n",
    "    node.appendChild(document.createTextNode(css));\n",
    "    var heads = document.getElementsByTagName(\"head\");\n",
    "    if (heads.length > 0) {\n",
    "        heads[0].appendChild(node);\n",
    "    } else {\n",
    "        document.documentElement.appendChild(node);\n",
    "    }\n",
    "}\n",
    "})();"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "___\n",
    "__Material produzido para o curso__:\n",
    "* Introdução à Análise de Dados com Python\n",
    "\n",
    "__Autor__:\n",
    "* Fernando Sola Pereira\n",
    "\n",
    "__Revisão__:\n",
    "* 1.1"
   ]
  }
 ],
 "metadata": {
  "hide_input": false,
  "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.6.8"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}