34 - Tratando Valores nulos.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "355a91b5",
   "metadata": {},
   "source": [
    "### Tratando valores nulos (NaN)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "b981c614",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "0d152320",
   "metadata": {},
   "outputs": [],
   "source": [
    "file=\"C:\\\\projetos\\\\ufo.csv\"\n",
    "ufo = pd.read_csv(file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4caa5cc9",
   "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>City</th>\n",
       "      <th>Colors Reported</th>\n",
       "      <th>Shape Reported</th>\n",
       "      <th>State</th>\n",
       "      <th>Time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>18236</th>\n",
       "      <td>Grant Park</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TRIANGLE</td>\n",
       "      <td>IL</td>\n",
       "      <td>12/31/2000 23:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18237</th>\n",
       "      <td>Spirit Lake</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DISK</td>\n",
       "      <td>IA</td>\n",
       "      <td>12/31/2000 23:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18238</th>\n",
       "      <td>Eagle River</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>WI</td>\n",
       "      <td>12/31/2000 23:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18239</th>\n",
       "      <td>Eagle River</td>\n",
       "      <td>RED</td>\n",
       "      <td>LIGHT</td>\n",
       "      <td>WI</td>\n",
       "      <td>12/31/2000 23:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18240</th>\n",
       "      <td>Ybor</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OVAL</td>\n",
       "      <td>FL</td>\n",
       "      <td>12/31/2000 23:59</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              City Colors Reported Shape Reported State              Time\n",
       "18236   Grant Park             NaN       TRIANGLE    IL  12/31/2000 23:00\n",
       "18237  Spirit Lake             NaN           DISK    IA  12/31/2000 23:00\n",
       "18238  Eagle River             NaN            NaN    WI  12/31/2000 23:45\n",
       "18239  Eagle River             RED          LIGHT    WI  12/31/2000 23:45\n",
       "18240         Ybor             NaN           OVAL    FL  12/31/2000 23:59"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Utilizando o tail (inverso do head)\n",
    "\n",
    "ufo.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e4d02f7",
   "metadata": {},
   "source": [
    "° \"NaN\" não é uma string. Ele é um valor especial do numpy: **numpy.nan**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be55ceb7",
   "metadata": {},
   "source": [
    "° É comum a referência \"Not a number\", que significa **missing value**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec823e29",
   "metadata": {},
   "source": [
    "° O método **read_csv** detecta valores nulos automaticamente e preenche com NaN na leitura do arquivo"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f55b5ca0",
   "metadata": {},
   "source": [
    "### O isnull() retorna um Dataframe boleano, onde True indica valor nulo e False valor preenchido"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "4acd31f4",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "#No arquivo null, os valores ímpares são nulos\n",
    "\n",
    "file=\"C:\\\\projetos\\\\nulos.csv\"\n",
    "nulos = pd.read_csv(file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "fed29835",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>nome</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id   nome\n",
       "0  False   True\n",
       "1  False  False\n",
       "2  False   True\n",
       "3  False  False"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nulos.isnull().tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "2a66f1bf",
   "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>City</th>\n",
       "      <th>Colors Reported</th>\n",
       "      <th>Shape Reported</th>\n",
       "      <th>State</th>\n",
       "      <th>Time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>18236</th>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18237</th>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18238</th>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18239</th>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18240</th>\n",
       "      <td>False</td>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        City  Colors Reported  Shape Reported  State   Time\n",
       "18236  False             True           False  False  False\n",
       "18237  False             True           False  False  False\n",
       "18238  False             True            True  False  False\n",
       "18239  False            False           False  False  False\n",
       "18240  False             True           False  False  False"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ufo.isnull().tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0089327f",
   "metadata": {},
   "source": [
    "### notnull() é exatamente o oposto do isnull()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "15a5cfd7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>nome</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>True</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id   nome\n",
       "0  True  False\n",
       "1  True   True\n",
       "2  True  False\n",
       "3  True   True"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nulos.notnull().tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "90c100de",
   "metadata": {},
   "source": [
    "### E como saber a quantidade de valores nulos em uma coluna?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "e4cdb632",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "City                  25\n",
       "Colors Reported    15359\n",
       "Shape Reported      2644\n",
       "State                  0\n",
       "Time                   0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ufo.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04d77dd2",
   "metadata": {},
   "source": [
    "### Como o isnull().sum() funciona?"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d100d7c5",
   "metadata": {},
   "source": [
    "Lembram que o **isnull()** retorna verdadeiro ou falso? O retorno verdadeiro ou falso é convertido pelo Pandas em 0 e 1 para verdadeiro. Então o método **sum()** agrega os valores das colunas, pois ele **opera por padrão no eixo 0.**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "edaf7059",
   "metadata": {},
   "source": [
    "## Construindo um filtro de valores nulos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "f8f0d7df",
   "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>City</th>\n",
       "      <th>Colors Reported</th>\n",
       "      <th>Shape Reported</th>\n",
       "      <th>State</th>\n",
       "      <th>Time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LA</td>\n",
       "      <td>8/15/1943 0:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>LIGHT</td>\n",
       "      <td>LA</td>\n",
       "      <td>8/15/1943 0:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DISK</td>\n",
       "      <td>CA</td>\n",
       "      <td>7/15/1952 12:30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>241</th>\n",
       "      <td>NaN</td>\n",
       "      <td>BLUE</td>\n",
       "      <td>DISK</td>\n",
       "      <td>MT</td>\n",
       "      <td>7/4/1953 14:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>613</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>DISK</td>\n",
       "      <td>NV</td>\n",
       "      <td>7/1/1960 12:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1877</th>\n",
       "      <td>NaN</td>\n",
       "      <td>YELLOW</td>\n",
       "      <td>CIRCLE</td>\n",
       "      <td>AZ</td>\n",
       "      <td>8/15/1969 1:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NH</td>\n",
       "      <td>8/1/1970 9:30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2546</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FIREBALL</td>\n",
       "      <td>OH</td>\n",
       "      <td>10/25/1973 23:30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3123</th>\n",
       "      <td>NaN</td>\n",
       "      <td>RED</td>\n",
       "      <td>TRIANGLE</td>\n",
       "      <td>WV</td>\n",
       "      <td>11/25/1975 23:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4736</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SPHERE</td>\n",
       "      <td>CA</td>\n",
       "      <td>6/23/1982 23:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5269</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>AZ</td>\n",
       "      <td>6/30/1985 21:30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6735</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FORMATION</td>\n",
       "      <td>TX</td>\n",
       "      <td>4/1/1992 2:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7208</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CIRCLE</td>\n",
       "      <td>MI</td>\n",
       "      <td>10/4/1993 17:30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8828</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TRIANGLE</td>\n",
       "      <td>WA</td>\n",
       "      <td>10/30/1995 21:30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8967</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VARIOUS</td>\n",
       "      <td>CA</td>\n",
       "      <td>12/8/1995 18:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9273</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TRIANGLE</td>\n",
       "      <td>OH</td>\n",
       "      <td>5/1/1996 3:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9388</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OVAL</td>\n",
       "      <td>CA</td>\n",
       "      <td>6/12/1996 12:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9587</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>EGG</td>\n",
       "      <td>FL</td>\n",
       "      <td>8/24/1996 15:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10399</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TRIANGLE</td>\n",
       "      <td>IL</td>\n",
       "      <td>6/15/1997 23:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11625</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CIRCLE</td>\n",
       "      <td>TX</td>\n",
       "      <td>6/7/1998 7:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      City Colors Reported Shape Reported State              Time\n",
       "21     NaN             NaN            NaN    LA    8/15/1943 0:00\n",
       "22     NaN             NaN          LIGHT    LA    8/15/1943 0:00\n",
       "204    NaN             NaN           DISK    CA   7/15/1952 12:30\n",
       "241    NaN            BLUE           DISK    MT    7/4/1953 14:00\n",
       "613    NaN             NaN           DISK    NV    7/1/1960 12:00\n",
       "1877   NaN          YELLOW         CIRCLE    AZ    8/15/1969 1:00\n",
       "2013   NaN             NaN            NaN    NH     8/1/1970 9:30\n",
       "2546   NaN             NaN       FIREBALL    OH  10/25/1973 23:30\n",
       "3123   NaN             RED       TRIANGLE    WV  11/25/1975 23:00\n",
       "4736   NaN             NaN         SPHERE    CA   6/23/1982 23:00\n",
       "5269   NaN             NaN            NaN    AZ   6/30/1985 21:30\n",
       "6735   NaN             NaN      FORMATION    TX     4/1/1992 2:00\n",
       "7208   NaN             NaN         CIRCLE    MI   10/4/1993 17:30\n",
       "8828   NaN             NaN       TRIANGLE    WA  10/30/1995 21:30\n",
       "8967   NaN             NaN        VARIOUS    CA   12/8/1995 18:00\n",
       "9273   NaN             NaN       TRIANGLE    OH     5/1/1996 3:00\n",
       "9388   NaN             NaN           OVAL    CA   6/12/1996 12:00\n",
       "9587   NaN             NaN            EGG    FL   8/24/1996 15:00\n",
       "10399  NaN             NaN       TRIANGLE    IL   6/15/1997 23:00\n",
       "11625  NaN             NaN         CIRCLE    TX     6/7/1998 7:00"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Estamos verificando as linhas em que a coluna City possui valor nulo\n",
    "\n",
    "ufo[ufo.City.isnull()].head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "b31f4726",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(18241, 5)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Numero de linhas e colunas\n",
    "A = ufo.shape\n",
    "A"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97008870",
   "metadata": {},
   "source": [
    "## dropna()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be70cf19",
   "metadata": {},
   "source": [
    "**any - Se qualquer valor estiver faltando na linha, então o dropna APAGA a linha** "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "2ed0b4dc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2486, 5)"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "B = ufo.dropna(how='any').shape\n",
    "B"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "84d4e339",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Deletadas15755linhas com valores nulos\n"
     ]
    }
   ],
   "source": [
    "#O retorno do shape é uma tupla\n",
    "\n",
    "print('Deletadas' + str((A[0] - B[0])) + 'linhas com valores nulos')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "5cf78123",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2486, 5)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#O inplace também é um parametro do dropna e é falso por padrão.\n",
    "\n",
    "ufo.dropna(how='any').shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "d8ace731",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(18241, 5)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#As linhas com NaN continuam no Dataframe\n",
    "\n",
    "ufo.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ccb46fd",
   "metadata": {},
   "source": [
    "## all - O all funciona da seguinte forma: Caso todos os valores de uma linha sejam nulo, ela será deletada"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "611bd215",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(18241, 5)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ufo.dropna(how='all').shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9010543",
   "metadata": {},
   "source": [
    "## Utilizando o dropna com parâmetros"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1156116c",
   "metadata": {},
   "source": [
    "**O parametro subset recebe uma lista** "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "62caeec3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(15576, 5)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Estamos criando um subset com City e Shape Reported. Se houver qualquer valor nulo nessas colunas, a linha será deletada.\n",
    "\n",
    "ufo.dropna(subset=['City', 'Shape Reported'], how='any').shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "651ff0fd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(18237, 5)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Se todos os valores estiverem nulos em City ou Shape Reported, as linhas serão deletadas\n",
    "ufo.dropna(subset=['City', 'Shape Reported'], how='all').shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b23cff0",
   "metadata": {},
   "source": [
    "## Como trabalha o parametro how()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "08de8065",
   "metadata": {},
   "source": [
    "- O any funciona como um or ou |\n",
    "- O all funciona como um and ou &"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df33b84f",
   "metadata": {},
   "source": [
    "### value_counts realiza um count, porém não conta os valores NaN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "4c1df2e3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "LIGHT       2803\n",
       "DISK        2122\n",
       "TRIANGLE    1889\n",
       "OTHER       1402\n",
       "CIRCLE      1365\n",
       "Name: Shape Reported, dtype: int64"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ufo['Shape Reported'].value_counts().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "734cbe99",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "LIGHT       2803\n",
       "NaN         2644\n",
       "DISK        2122\n",
       "TRIANGLE    1889\n",
       "OTHER       1402\n",
       "Name: Shape Reported, dtype: int64"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Podemos incluir os valores NaN\n",
    "ufo['Shape Reported'].value_counts(dropna=False).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "a2d6ea73",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Podemos ainda preencher os valores que estão faltando\n",
    "ufo['Shape Reported'].fillna(value='DIVERSOS',inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "252f4627",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "LIGHT       2803\n",
       "DIVERSOS    2644\n",
       "DISK        2122\n",
       "TRIANGLE    1889\n",
       "OTHER       1402\n",
       "Name: Shape Reported, dtype: int64"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Confirmando a contagem incluindo o valor DIVERSOS\n",
    "ufo['Shape Reported'].value_counts().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "98a63b42",
   "metadata": {},
   "source": [
    "## E a coca???"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "5b7d1445",
   "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>order_id</th>\n",
       "      <th>quantity</th>\n",
       "      <th>item_name</th>\n",
       "      <th>choice_description</th>\n",
       "      <th>item_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Chips and Fresh Tomato Salsa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$2.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Izze</td>\n",
       "      <td>[Clementine]</td>\n",
       "      <td>$3.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Nantucket Nectar</td>\n",
       "      <td>[Apple]</td>\n",
       "      <td>$3.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Chips and Tomatillo-Green Chili Salsa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>$2.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Chicken Bowl</td>\n",
       "      <td>[Tomatillo-Red Chili Salsa (Hot), [Black Beans...</td>\n",
       "      <td>$16.98</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   order_id  quantity                              item_name  \\\n",
       "0         1         1           Chips and Fresh Tomato Salsa   \n",
       "1         1         1                                   Izze   \n",
       "2         1         1                       Nantucket Nectar   \n",
       "3         1         1  Chips and Tomatillo-Green Chili Salsa   \n",
       "4         2         2                           Chicken Bowl   \n",
       "\n",
       "                                  choice_description item_price  \n",
       "0                                                NaN     $2.39   \n",
       "1                                       [Clementine]     $3.39   \n",
       "2                                            [Apple]     $3.39   \n",
       "3                                                NaN     $2.39   \n",
       "4  [Tomatillo-Red Chili Salsa (Hot), [Black Beans...    $16.98   "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "arquivo = \"C:\\\\projetos\\\\sanduiches.txt\"\n",
    "vendas = pd.read_table(arquivo)\n",
    "vendas.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "56a20c79",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(4622, 5)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "vendas.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "8fb30965",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Apagando os valores as linhas com valores nulos em descrição.\n",
    "\n",
    "ordens = vendas.dropna(subset=['choice_description'], how='any')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "cf11299d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3376, 5)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ordens.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "bfa89b6d",
   "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>order_id</th>\n",
       "      <th>quantity</th>\n",
       "      <th>item_name</th>\n",
       "      <th>choice_description</th>\n",
       "      <th>item_price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Izze</td>\n",
       "      <td>[Clementine]</td>\n",
       "      <td>$3.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Nantucket Nectar</td>\n",
       "      <td>[Apple]</td>\n",
       "      <td>$3.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Chicken Bowl</td>\n",
       "      <td>[Tomatillo-Red Chili Salsa (Hot), [Black Beans...</td>\n",
       "      <td>$16.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Chicken Bowl</td>\n",
       "      <td>[Fresh Tomato Salsa (Mild), [Rice, Cheese, Sou...</td>\n",
       "      <td>$10.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>Steak Burrito</td>\n",
       "      <td>[Tomatillo Red Chili Salsa, [Fajita Vegetables...</td>\n",
       "      <td>$11.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>Steak Soft Tacos</td>\n",
       "      <td>[Tomatillo Green Chili Salsa, [Pinto Beans, Ch...</td>\n",
       "      <td>$9.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>Steak Burrito</td>\n",
       "      <td>[Fresh Tomato Salsa, [Rice, Black Beans, Pinto...</td>\n",
       "      <td>$9.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>Chicken Crispy Tacos</td>\n",
       "      <td>[Roasted Chili Corn Salsa, [Fajita Vegetables,...</td>\n",
       "      <td>$8.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>Chicken Soft Tacos</td>\n",
       "      <td>[Roasted Chili Corn Salsa, [Rice, Black Beans,...</td>\n",
       "      <td>$8.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>Chicken Bowl</td>\n",
       "      <td>[Fresh Tomato Salsa, [Fajita Vegetables, Rice,...</td>\n",
       "      <td>$11.25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    order_id  quantity             item_name  \\\n",
       "1          1         1                  Izze   \n",
       "2          1         1      Nantucket Nectar   \n",
       "4          2         2          Chicken Bowl   \n",
       "5          3         1          Chicken Bowl   \n",
       "7          4         1         Steak Burrito   \n",
       "8          4         1      Steak Soft Tacos   \n",
       "9          5         1         Steak Burrito   \n",
       "11         6         1  Chicken Crispy Tacos   \n",
       "12         6         1    Chicken Soft Tacos   \n",
       "13         7         1          Chicken Bowl   \n",
       "\n",
       "                                   choice_description item_price  \n",
       "1                                        [Clementine]     $3.39   \n",
       "2                                             [Apple]     $3.39   \n",
       "4   [Tomatillo-Red Chili Salsa (Hot), [Black Beans...    $16.98   \n",
       "5   [Fresh Tomato Salsa (Mild), [Rice, Cheese, Sou...    $10.98   \n",
       "7   [Tomatillo Red Chili Salsa, [Fajita Vegetables...    $11.75   \n",
       "8   [Tomatillo Green Chili Salsa, [Pinto Beans, Ch...     $9.25   \n",
       "9   [Fresh Tomato Salsa, [Rice, Black Beans, Pinto...     $9.25   \n",
       "11  [Roasted Chili Corn Salsa, [Fajita Vegetables,...     $8.75   \n",
       "12  [Roasted Chili Corn Salsa, [Rice, Black Beans,...     $8.75   \n",
       "13  [Fresh Tomato Salsa, [Fajita Vegetables, Rice,...    $11.25   "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ordens.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "6a6361a1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "257"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ordens.choice_description.str.contains('Coke').astype(int).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6449a6b1",
   "metadata": {},
   "source": [
    "### E quantos pratos tem Guacamole?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "4fdc2a01",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1037"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ordens.choice_description.str.contains('Guacamole').astype(int).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c355214c",
   "metadata": {},
   "source": [
    "### E quantos pratos tem Arroz?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "07e40187",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2402"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ordens.choice_description.str.contains('Rice').astype(int).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2a25f293",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}