Tutorial Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "d2875751-5f29-4dd2-b078-2c0af62d1e21",
   "metadata": {},
   "source": [
    "# Tutorial Pandas - ptBR"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "620564a3-13b2-44fa-941a-36850bc3d9da",
   "metadata": {},
   "source": [
    "## Sumário\n",
    "<a id='sumario'></a>\n",
    "\n",
    "1. [Carregar dados, definir tipos, reordenar e alterar nomes das colunas ( .rename, .astype )](#importacao)\n",
    "2. [Descrição do DataFrame ( .info, .describe, .unique )](#descricao)\n",
    "3. [Tratar valores nulos ( .isna, .dropna, .fillna )](#nulos)\n",
    "4. [Remover Acentos e multiplos Espaços entre palavras / strings ( .str.replace, .apply(unidecode) )](#acentos)\n",
    "5. [Ordenar dataframe ( .sort_values )](#ordenar)\n",
    "6. [Tratar valores duplicados ( .drop_duplicates, .duplicated )](#duplicados)\n",
    "7. [Alterar, Ajustar ou Fatiar dados dos registros ( .str.replace, .str.lower, .str[:8], .str.strip )](#alterar_dados)\n",
    "8. [Pegar a diferença entre dois dataframes ( .isin )](#diff)\n",
    "9. [Filtrar o dataframe](#filtro)\n",
    "10. [Excluir / Deletar registros do dataframe por filtro](#deletar)\n",
    "11. [Trabalhando com datas](#datas)\n",
    "12. [Substituir valores no dataframe (.map)](#substituir)\n",
    "13. [Operações com banco de dados Sqlite (sqlite3)](#bd)\n",
    "14. [Concatenar Dataframes (.concat)](#concat)\n",
    "15. [Relacionar Dataframes (.merge)](#merge)\n",
    "16. [Link](#link)\n",
    "17. [Link](#link)\n",
    "18. [Link](#link)\n",
    "19. [Link](#link)\n",
    "20. [Link](#link)\n",
    "21. [Link](#link)\n",
    "22. [Link](#link)\n",
    "23. [Link](#link)\n",
    "24. [Link](#link)\n",
    "25. [Link](#link)\n",
    "26. [Link](#link)\n",
    "27. [Link](#link)\n",
    "28. [Link](#link)\n",
    "29. [Link](#link)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "89f544f3-4960-40e9-bcd5-38c6c99aa509",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2ae0142-fd5a-4437-90df-e03b45e74bee",
   "metadata": {},
   "source": [
    "<a id='importacao'></a>\n",
    "# Carregar dados, definir tipos, reordenar e alterar nomes das colunas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "02d4991b-ba85-4ffd-a51d-b86bd23550d9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Shape:  (54, 7) \n",
      "\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>Cnpj do Buyer</th>\n",
       "      <th>Seguradora</th>\n",
       "      <th>Estado do Buyer</th>\n",
       "      <th>Valor Solicitado</th>\n",
       "      <th>Valor Aprovado</th>\n",
       "      <th>Data Decisão</th>\n",
       "      <th>Segmento do Buyer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01780733000139</td>\n",
       "      <td>Seguradora       1</td>\n",
       "      <td>SP</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2023-04-02</td>\n",
       "      <td>Mineração</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01779733000139</td>\n",
       "      <td>Seguradora       1</td>\n",
       "      <td>RJ</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>2023-04-03</td>\n",
       "      <td>Energia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01778733000139</td>\n",
       "      <td>Seguradora       1</td>\n",
       "      <td>JR</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>2023-04-26</td>\n",
       "      <td>Petróleo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01777733000139</td>\n",
       "      <td>Seguradora       2</td>\n",
       "      <td>MG</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>2023-05-02</td>\n",
       "      <td>Infraestrutura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01776733000139</td>\n",
       "      <td>Seguradora       2</td>\n",
       "      <td>PR</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>2023-05-02</td>\n",
       "      <td>Serviço</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Cnpj do Buyer          Seguradora Estado do Buyer  Valor Solicitado  \\\n",
       "0  01780733000139  Seguradora       1              SP         1000000.0   \n",
       "1  01779733000139  Seguradora       1              RJ         1000000.0   \n",
       "2  01778733000139  Seguradora       1              JR         1000000.0   \n",
       "3  01777733000139  Seguradora       2              MG         1000000.0   \n",
       "4  01776733000139  Seguradora       2              PR         1000000.0   \n",
       "\n",
       "   Valor Aprovado Data Decisão Segmento do Buyer  \n",
       "0             0.0   2023-04-02         Mineração  \n",
       "1       1000000.0   2023-04-03           Energia  \n",
       "2       1000000.0   2023-04-26          Petróleo  \n",
       "3        775000.0   2023-05-02    Infraestrutura  \n",
       "4       1000000.0   2023-05-02           Serviço  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "datatypes = {\n",
    "    \"Seguradora\": str,\n",
    "    \"Valor Solicitado\": float,\n",
    "    \"Valor Aprovado\": float,\n",
    "    \"Data Decisão\": 'datetime64[ns]',\n",
    "    \"Cnpj do Buyer\": str,\n",
    "    \"Segmento do Buyer\": str,\n",
    "    \"Estado do Buyer\": str\n",
    "}\n",
    "\n",
    "df = pd.read_excel(\n",
    "    'dados.xlsx',\n",
    "    engine='openpyxl',\n",
    "    usecols=datatypes.keys(),\n",
    "    dtype=datatypes\n",
    ")\n",
    "\n",
    "print(\"\\nShape: \", df.shape, \"\\n\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "36245ca7-5c2b-40e7-91a2-398c343a8160",
   "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>seguradora</th>\n",
       "      <th>valor_solicitado</th>\n",
       "      <th>valor_aprovado</th>\n",
       "      <th>data_decisao</th>\n",
       "      <th>buyer_cnpj</th>\n",
       "      <th>buyer_segmento</th>\n",
       "      <th>buyer_estado</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Seguradora       1</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2023-04-02</td>\n",
       "      <td>01780733000139</td>\n",
       "      <td>Mineração</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Seguradora       1</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>2023-04-03</td>\n",
       "      <td>01779733000139</td>\n",
       "      <td>Energia</td>\n",
       "      <td>RJ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Seguradora       1</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>2023-04-26</td>\n",
       "      <td>01778733000139</td>\n",
       "      <td>Petróleo</td>\n",
       "      <td>JR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Seguradora       2</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>2023-05-02</td>\n",
       "      <td>01777733000139</td>\n",
       "      <td>Infraestrutura</td>\n",
       "      <td>MG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Seguradora       2</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>2023-05-02</td>\n",
       "      <td>01776733000139</td>\n",
       "      <td>Serviço</td>\n",
       "      <td>PR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           seguradora  valor_solicitado  valor_aprovado data_decisao  \\\n",
       "0  Seguradora       1         1000000.0             0.0   2023-04-02   \n",
       "1  Seguradora       1         1000000.0       1000000.0   2023-04-03   \n",
       "2  Seguradora       1         1000000.0       1000000.0   2023-04-26   \n",
       "3  Seguradora       2         1000000.0        775000.0   2023-05-02   \n",
       "4  Seguradora       2         1000000.0       1000000.0   2023-05-02   \n",
       "\n",
       "       buyer_cnpj  buyer_segmento buyer_estado  \n",
       "0  01780733000139       Mineração           SP  \n",
       "1  01779733000139         Energia           RJ  \n",
       "2  01778733000139        Petróleo           JR  \n",
       "3  01777733000139  Infraestrutura           MG  \n",
       "4  01776733000139         Serviço           PR  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nomes_colunas = {\n",
    "    \"Seguradora\": \"seguradora\",\n",
    "    \"Valor Solicitado\": \"valor_solicitado\",\n",
    "    \"Valor Aprovado\": \"valor_aprovado\",\n",
    "    \"Data Decisão\": \"data_decisao\",\n",
    "    \"Cnpj do Buyer\": \"buyer_cnpj\",\n",
    "    \"Segmento do Buyer\": \"buyer_segmento\",\n",
    "    \"Estado do Buyer\": \"buyer_estado\",\n",
    "}\n",
    "\n",
    "df = df.rename(columns=nomes_colunas)\n",
    "\n",
    "nova_ordem_colunas = nomes_colunas.values()\n",
    "df = df[nova_ordem_colunas]  # Reordenar as colunas\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b7062715-8fcc-4e23-afc5-397a8f93d0be",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['valor_solicitado'] = df['valor_solicitado'].astype(float)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c0910fd-06c5-4b9b-88e6-ea0d8b1e383d",
   "metadata": {},
   "source": [
    "<a id='descricao'></a>\n",
    "# Descrição do DataFrame\n",
    "\n",
    "[Voltar para o Sumário](#sumario)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "af3b3baa-defb-4b73-b968-25308fd04ca3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 54 entries, 0 to 53\n",
      "Data columns (total 7 columns):\n",
      " #   Column            Non-Null Count  Dtype         \n",
      "---  ------            --------------  -----         \n",
      " 0   seguradora        54 non-null     object        \n",
      " 1   valor_solicitado  52 non-null     float64       \n",
      " 2   valor_aprovado    52 non-null     float64       \n",
      " 3   data_decisao      52 non-null     datetime64[ns]\n",
      " 4   buyer_cnpj        53 non-null     object        \n",
      " 5   buyer_segmento    53 non-null     object        \n",
      " 6   buyer_estado      53 non-null     object        \n",
      "dtypes: datetime64[ns](1), float64(2), object(4)\n",
      "memory usage: 3.1+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "02302fd8-378b-4b04-87ec-e348a5a0e00f",
   "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>valor_solicitado</th>\n",
       "      <th>valor_aprovado</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>52.000000</td>\n",
       "      <td>52.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>650961.538462</td>\n",
       "      <td>305288.461538</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>284353.478003</td>\n",
       "      <td>328593.875120</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>250000.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>500000.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>775000.000000</td>\n",
       "      <td>250000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>1000000.000000</td>\n",
       "      <td>500000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1000000.000000</td>\n",
       "      <td>1000000.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       valor_solicitado  valor_aprovado\n",
       "count         52.000000       52.000000\n",
       "mean      650961.538462   305288.461538\n",
       "std       284353.478003   328593.875120\n",
       "min       250000.000000        0.000000\n",
       "25%       500000.000000        0.000000\n",
       "50%       775000.000000   250000.000000\n",
       "75%      1000000.000000   500000.000000\n",
       "max      1000000.000000  1000000.000000"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "39898d95-0fb2-4111-bc58-13612d3fab55",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['SP' 'RJ' 'JR' 'MG' 'PR' 'MA' 'RN' 'BA' 'RS' nan]\n"
     ]
    }
   ],
   "source": [
    "print(df['buyer_estado'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae2ca052-cc17-4ec2-be9f-49750c6b0876",
   "metadata": {},
   "source": [
    "<a id='nulos'></a>\n",
    "# Tratar valores nulos (na, nan)\n",
    "\n",
    "[Voltar para o Sumário](#sumario)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "27d7a6ef-184c-44b9-9e4d-ff97c5b518e6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "seguradora          0\n",
       "valor_solicitado    2\n",
       "valor_aprovado      2\n",
       "data_decisao        2\n",
       "buyer_cnpj          1\n",
       "buyer_segmento      1\n",
       "buyer_estado        1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "79659010-3936-4263-8398-76155921c418",
   "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>seguradora</th>\n",
       "      <th>valor_solicitado</th>\n",
       "      <th>valor_aprovado</th>\n",
       "      <th>data_decisao</th>\n",
       "      <th>buyer_cnpj</th>\n",
       "      <th>buyer_segmento</th>\n",
       "      <th>buyer_estado</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>Seguradora       3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            seguradora  valor_solicitado  valor_aprovado data_decisao  \\\n",
       "25  Seguradora       3               NaN             NaN          NaT   \n",
       "\n",
       "   buyer_cnpj buyer_segmento buyer_estado  \n",
       "25        NaN            NaN          NaN  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['buyer_cnpj'].isna()]  # Filtrando dados nulos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "eaa48b01-c00b-4df2-8b72-debd0f0b53d2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Shape:  (54, 7) \n",
      "\n",
      "\n",
      "Shape:  (53, 7) \n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(\"\\nShape: \", df.shape, \"\\n\")\n",
    "\n",
    "df.dropna(subset='buyer_cnpj', inplace=True)  # excluindo dados nulos\n",
    "\n",
    "print(\"\\nShape: \", df.shape, \"\\n\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "c1a1b379-2d3e-4368-b243-cd309c3dd2c9",
   "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>seguradora</th>\n",
       "      <th>valor_solicitado</th>\n",
       "      <th>valor_aprovado</th>\n",
       "      <th>data_decisao</th>\n",
       "      <th>buyer_cnpj</th>\n",
       "      <th>buyer_segmento</th>\n",
       "      <th>buyer_estado</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [seguradora, valor_solicitado, valor_aprovado, data_decisao, buyer_cnpj, buyer_segmento, buyer_estado]\n",
       "Index: []"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['buyer_cnpj'].isna()]  # Filtrando dados nulos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "bc6673f9-16be-4c57-889b-909a7e66bd5d",
   "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>seguradora</th>\n",
       "      <th>valor_solicitado</th>\n",
       "      <th>valor_aprovado</th>\n",
       "      <th>data_decisao</th>\n",
       "      <th>buyer_cnpj</th>\n",
       "      <th>buyer_segmento</th>\n",
       "      <th>buyer_estado</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>Seguradora       3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaT</td>\n",
       "      <td>01733733000139</td>\n",
       "      <td>Petróleo</td>\n",
       "      <td>MG</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            seguradora  valor_solicitado  valor_aprovado data_decisao  \\\n",
       "47  Seguradora       3               NaN             NaN          NaT   \n",
       "\n",
       "        buyer_cnpj buyer_segmento buyer_estado  \n",
       "47  01733733000139       Petróleo           MG  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['valor_solicitado'].isna()]  # Filtrando dados nulos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "3a55bcef-d860-44ee-ac26-45d3c834464e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Preenchendo dados vazios com a média da coluna\n",
    "\n",
    "media_valor_solicitado = df['valor_solicitado'].mean()\n",
    "media_valor_aprovado = df['valor_aprovado'].mean()\n",
    "media_data_decisao = df['data_decisao'].mean()\n",
    "\n",
    "df['valor_solicitado'] = df['valor_solicitado'].fillna(media_valor_solicitado)\n",
    "df['valor_aprovado'] = df['valor_aprovado'].fillna(media_valor_aprovado)\n",
    "df['data_decisao'] = df['data_decisao'].fillna(media_data_decisao)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "60be6ad8-e72b-4093-899f-5e0623053a18",
   "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>seguradora</th>\n",
       "      <th>valor_solicitado</th>\n",
       "      <th>valor_aprovado</th>\n",
       "      <th>data_decisao</th>\n",
       "      <th>buyer_cnpj</th>\n",
       "      <th>buyer_segmento</th>\n",
       "      <th>buyer_estado</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>Seguradora       3</td>\n",
       "      <td>650961.538462</td>\n",
       "      <td>305288.461538</td>\n",
       "      <td>2023-04-30 10:36:55.384615424</td>\n",
       "      <td>01733733000139</td>\n",
       "      <td>Petróleo</td>\n",
       "      <td>MG</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            seguradora  valor_solicitado  valor_aprovado  \\\n",
       "47  Seguradora       3     650961.538462   305288.461538   \n",
       "\n",
       "                    data_decisao      buyer_cnpj buyer_segmento buyer_estado  \n",
       "47 2023-04-30 10:36:55.384615424  01733733000139       Petróleo           MG  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['valor_solicitado'].index == 47]  # Filtrando registro por índice"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "f788f143-5aff-43e8-806a-d51b518fed87",
   "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>seguradora</th>\n",
       "      <th>valor_solicitado</th>\n",
       "      <th>valor_aprovado</th>\n",
       "      <th>data_decisao</th>\n",
       "      <th>buyer_cnpj</th>\n",
       "      <th>buyer_segmento</th>\n",
       "      <th>buyer_estado</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [seguradora, valor_solicitado, valor_aprovado, data_decisao, buyer_cnpj, buyer_segmento, buyer_estado]\n",
       "Index: []"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['valor_solicitado'].isna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "a433128e-5f6b-4d1e-9982-cc4590a4e7d2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "seguradora          0\n",
       "valor_solicitado    0\n",
       "valor_aprovado      0\n",
       "data_decisao        0\n",
       "buyer_cnpj          0\n",
       "buyer_segmento      0\n",
       "buyer_estado        0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "47c8882b-f8b2-4e2a-9571-0b8260091b0b",
   "metadata": {},
   "source": [
    "<a id='acentos'></a>\n",
    "\n",
    "# Remover Acentos e multiplos Espaços entre palavras / strings\n",
    "\n",
    "[Voltar para o Sumário](#sumario)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "d068a185-5aac-435e-8659-59ea8a79103c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Antes:  ['Seguradora       1' 'Seguradora       2' 'Seguradora       3']\n",
      "\n",
      "Depois:  ['Seguradora 1' 'Seguradora 2' 'Seguradora 3']\n"
     ]
    }
   ],
   "source": [
    "# Remover 2 ou mais espaços entre palavras\n",
    "\n",
    "print(\"Antes: \",df['seguradora'].unique())\n",
    "\n",
    "df['seguradora'] = df['seguradora'].str.replace(r'\\s+', ' ', regex=True)\n",
    "\n",
    "print(\"\\nDepois: \",df['seguradora'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "8145fe0b-c6f4-474f-b362-c9db3662a61e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Antes:  ['Mineração' 'Energia' 'Petróleo' 'Infraestrutura' 'Serviço']\n"
     ]
    },
    {
     "ename": "ModuleNotFoundError",
     "evalue": "No module named 'unidecode'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mModuleNotFoundError\u001b[0m                       Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[18], line 3\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[38;5;28mprint\u001b[39m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mAntes: \u001b[39m\u001b[38;5;124m\"\u001b[39m,df[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mbuyer_segmento\u001b[39m\u001b[38;5;124m'\u001b[39m]\u001b[38;5;241m.\u001b[39munique())\n\u001b[1;32m----> 3\u001b[0m \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01munidecode\u001b[39;00m \u001b[38;5;28;01mimport\u001b[39;00m unidecode  \u001b[38;5;66;03m# pip install Unidecode\u001b[39;00m\n\u001b[0;32m      4\u001b[0m df[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mbuyer_segmento\u001b[39m\u001b[38;5;124m'\u001b[39m] \u001b[38;5;241m=\u001b[39m df[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mbuyer_segmento\u001b[39m\u001b[38;5;124m'\u001b[39m]\u001b[38;5;241m.\u001b[39mapply(unidecode)\n\u001b[0;32m      7\u001b[0m \u001b[38;5;28mprint\u001b[39m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;130;01m\\n\u001b[39;00m\u001b[38;5;124mDepois: \u001b[39m\u001b[38;5;124m\"\u001b[39m,df[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mbuyer_segmento\u001b[39m\u001b[38;5;124m'\u001b[39m]\u001b[38;5;241m.\u001b[39munique())\n",
      "\u001b[1;31mModuleNotFoundError\u001b[0m: No module named 'unidecode'"
     ]
    }
   ],
   "source": [
    "print(\"Antes: \",df['buyer_segmento'].unique())\n",
    "\n",
    "from unidecode import unidecode  # pip install Unidecode\n",
    "df['buyer_segmento'] = df['buyer_segmento'].apply(unidecode)\n",
    "\n",
    "\n",
    "print(\"\\nDepois: \",df['buyer_segmento'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2d90c93f-3254-4e2e-913a-5091eb16352f",
   "metadata": {},
   "source": [
    "<a id='ordenar'></a>\n",
    "# Ordenar dataframes\n",
    "\n",
    "[Voltar para o Sumário](#sumario)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "e03d4752-7ef6-4b12-8590-116bf7e4aea8",
   "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>seguradora</th>\n",
       "      <th>valor_solicitado</th>\n",
       "      <th>valor_aprovado</th>\n",
       "      <th>data_decisao</th>\n",
       "      <th>buyer_cnpj</th>\n",
       "      <th>buyer_segmento</th>\n",
       "      <th>buyer_estado</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>Seguradora 1</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2023-05-02</td>\n",
       "      <td>01731733000139</td>\n",
       "      <td>Serviço</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>Seguradora 1</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2023-05-02</td>\n",
       "      <td>01731733000139</td>\n",
       "      <td>Serviço</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Seguradora 1</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2023-05-02</td>\n",
       "      <td>01731733000139</td>\n",
       "      <td>Serviço</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Seguradora 1</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2023-05-02</td>\n",
       "      <td>01731733000139</td>\n",
       "      <td>Serviço</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>Seguradora 3</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2023-05-20</td>\n",
       "      <td>01732733000139</td>\n",
       "      <td>Infraestrutura</td>\n",
       "      <td>PR</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      seguradora  valor_solicitado  valor_aprovado data_decisao  \\\n",
       "52  Seguradora 1          775000.0             0.0   2023-05-02   \n",
       "51  Seguradora 1          775000.0             0.0   2023-05-02   \n",
       "50  Seguradora 1          775000.0             0.0   2023-05-02   \n",
       "49  Seguradora 1          775000.0             0.0   2023-05-02   \n",
       "48  Seguradora 3          775000.0             0.0   2023-05-20   \n",
       "\n",
       "        buyer_cnpj  buyer_segmento buyer_estado  \n",
       "52  01731733000139         Serviço           MA  \n",
       "51  01731733000139         Serviço           MA  \n",
       "50  01731733000139         Serviço           MA  \n",
       "49  01731733000139         Serviço           MA  \n",
       "48  01732733000139  Infraestrutura           PR  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Ordenar por apenas 1 coluna\n",
    "\n",
    "df = df.sort_values(\"buyer_cnpj\", ascending=True)\n",
    "\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "b673e196-8ff0-48e9-845a-97208666bde7",
   "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>seguradora</th>\n",
       "      <th>valor_solicitado</th>\n",
       "      <th>valor_aprovado</th>\n",
       "      <th>data_decisao</th>\n",
       "      <th>buyer_cnpj</th>\n",
       "      <th>buyer_segmento</th>\n",
       "      <th>buyer_estado</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Seguradora 1</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2023-04-02</td>\n",
       "      <td>01780733000139</td>\n",
       "      <td>Mineração</td>\n",
       "      <td>SP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Seguradora 1</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>2023-04-03</td>\n",
       "      <td>01779733000139</td>\n",
       "      <td>Energia</td>\n",
       "      <td>RJ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Seguradora 2</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>2023-04-08</td>\n",
       "      <td>01774733000139</td>\n",
       "      <td>Energia</td>\n",
       "      <td>RN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Seguradora 2</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>2023-04-09</td>\n",
       "      <td>01773733000139</td>\n",
       "      <td>Petróleo</td>\n",
       "      <td>BA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Seguradora 2</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>2023-04-12</td>\n",
       "      <td>01770733000139</td>\n",
       "      <td>Mineração</td>\n",
       "      <td>RS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      seguradora  valor_solicitado  valor_aprovado data_decisao  \\\n",
       "0   Seguradora 1         1000000.0             0.0   2023-04-02   \n",
       "1   Seguradora 1         1000000.0       1000000.0   2023-04-03   \n",
       "6   Seguradora 2         1000000.0       1000000.0   2023-04-08   \n",
       "7   Seguradora 2         1000000.0        775000.0   2023-04-09   \n",
       "10  Seguradora 2         1000000.0       1000000.0   2023-04-12   \n",
       "\n",
       "        buyer_cnpj buyer_segmento buyer_estado  \n",
       "0   01780733000139      Mineração           SP  \n",
       "1   01779733000139        Energia           RJ  \n",
       "6   01774733000139        Energia           RN  \n",
       "7   01773733000139       Petróleo           BA  \n",
       "10  01770733000139      Mineração           RS  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Ordenar por 2 ou mais colunas\n",
    "\n",
    "df = df.sort_values(\n",
    "    by=['valor_solicitado', 'data_decisao'], ascending=[False, True]\n",
    ")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "76defd81-5b0d-474f-ba46-73d6a0e7f0f4",
   "metadata": {},
   "source": [
    "<a id='duplicados'></a>\n",
    "# Tratar valores duplicados\n",
    "\n",
    "[Voltar para o Sumário](#sumario)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "d4e02319-1869-48ae-9ca9-8eae358c2505",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Shape:  (53, 7) \n",
      "\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>seguradora</th>\n",
       "      <th>valor_solicitado</th>\n",
       "      <th>valor_aprovado</th>\n",
       "      <th>data_decisao</th>\n",
       "      <th>buyer_cnpj</th>\n",
       "      <th>buyer_segmento</th>\n",
       "      <th>buyer_estado</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>Seguradora 1</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2023-05-02</td>\n",
       "      <td>01731733000139</td>\n",
       "      <td>Serviço</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>Seguradora 1</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2023-05-02</td>\n",
       "      <td>01731733000139</td>\n",
       "      <td>Serviço</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Seguradora 1</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2023-05-02</td>\n",
       "      <td>01731733000139</td>\n",
       "      <td>Serviço</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>Seguradora 1</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2023-05-02</td>\n",
       "      <td>01731733000139</td>\n",
       "      <td>Serviço</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>Seguradora 3</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>2023-05-13</td>\n",
       "      <td>01739733000139</td>\n",
       "      <td>Energia</td>\n",
       "      <td>BA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>Seguradora 3</td>\n",
       "      <td>250000.0</td>\n",
       "      <td>250000.0</td>\n",
       "      <td>2023-05-02</td>\n",
       "      <td>01739733000139</td>\n",
       "      <td>Petróleo</td>\n",
       "      <td>MA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      seguradora  valor_solicitado  valor_aprovado data_decisao  \\\n",
       "52  Seguradora 1          775000.0             0.0   2023-05-02   \n",
       "51  Seguradora 1          775000.0             0.0   2023-05-02   \n",
       "50  Seguradora 1          775000.0             0.0   2023-05-02   \n",
       "49  Seguradora 1          775000.0             0.0   2023-05-02   \n",
       "41  Seguradora 3          775000.0        775000.0   2023-05-13   \n",
       "53  Seguradora 3          250000.0        250000.0   2023-05-02   \n",
       "\n",
       "        buyer_cnpj buyer_segmento buyer_estado  \n",
       "52  01731733000139        Serviço           MA  \n",
       "51  01731733000139        Serviço           MA  \n",
       "50  01731733000139        Serviço           MA  \n",
       "49  01731733000139        Serviço           MA  \n",
       "41  01739733000139        Energia           BA  \n",
       "53  01739733000139       Petróleo           MA  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"\\nShape: \", df.shape, \"\\n\")\n",
    "\n",
    "df[df.duplicated(subset='buyer_cnpj', keep=False)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "024006c7-8f77-4f56-829b-49dbc8130d37",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop_duplicates(subset='buyer_cnpj', keep='first')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "81bc8a1c-a5b4-4647-9e00-971ac4376e3b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Shape:  (49, 7) \n",
      "\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>seguradora</th>\n",
       "      <th>valor_solicitado</th>\n",
       "      <th>valor_aprovado</th>\n",
       "      <th>data_decisao</th>\n",
       "      <th>buyer_cnpj</th>\n",
       "      <th>buyer_segmento</th>\n",
       "      <th>buyer_estado</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [seguradora, valor_solicitado, valor_aprovado, data_decisao, buyer_cnpj, buyer_segmento, buyer_estado]\n",
       "Index: []"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"\\nShape: \", df.shape, \"\\n\")\n",
    "\n",
    "df[df.duplicated(subset='buyer_cnpj', keep=False)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a4e5b8c-b570-4f81-8087-901fab36a9e0",
   "metadata": {},
   "source": [
    "<a id='alterar_dados'></a>\n",
    "\n",
    "# Alterar, Ajustar ou Fatiar dados dos registros\n",
    "\n",
    "[Voltar para o Sumário](#sumario)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "10c864d3-805a-4109-8a2d-720bdf5a1dfa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "\"\"\"\n",
    "MÉTODOS DISPONÍVEIS\n",
    "\n",
    ".str.cat()                 .str.split()               .str.rsplit()          .str.get()\n",
    ".str.join()                .str.get_dummies()         .str.contains()        .str.replace()\n",
    ".str.removeprefix()        .str.removesuffix()        .str.repeat()          .str.pad()\n",
    ".str.center()              .str.ljust()               .str.rjust()           .str.zfill()\n",
    ".str.wrap()                .str.slice()               .str.slice_replace()   .str.count()\n",
    ".str.startswith()          .str.endswith()            .str.findall()         .str.match()\n",
    ".str.extract()             .str.extractall()          .str.len()             .str.strip()\n",
    ".str.rstrip()              .str.lstrip()              .str.partition()       .str.rpartition()\n",
    ".str.lower()               .str.casefold()            .str.upper()           .str.find()\n",
    ".str.rfind()               .str.index()               .str.rindex()          .str.capitalize()\n",
    ".str.swapcase()            .str.normalize()           .str.translate()       .str.isalnum()\n",
    ".str.isalpha()             .str.isdigit()             .str.isspace()         .str.islower()\n",
    ".str.isupper()             .str.istitle()             .str.isnumeric()       .str.isdecimal()\n",
    "\"\"\"\n",
    "print()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "3f9c6b2f-8469-4ace-9d01-96ea388f9baa",
   "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>seguradora</th>\n",
       "      <th>valor_solicitado</th>\n",
       "      <th>valor_aprovado</th>\n",
       "      <th>data_decisao</th>\n",
       "      <th>buyer_cnpj</th>\n",
       "      <th>buyer_segmento</th>\n",
       "      <th>buyer_estado</th>\n",
       "      <th>cnpj_raiz</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Seguradora 1</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2023-04-02</td>\n",
       "      <td>01780733000139</td>\n",
       "      <td>Mineração</td>\n",
       "      <td>SP</td>\n",
       "      <td>01780733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Seguradora 1</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>2023-04-03</td>\n",
       "      <td>01779733000139</td>\n",
       "      <td>Energia</td>\n",
       "      <td>RJ</td>\n",
       "      <td>01779733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Seguradora 2</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>2023-04-08</td>\n",
       "      <td>01774733000139</td>\n",
       "      <td>Energia</td>\n",
       "      <td>RN</td>\n",
       "      <td>01774733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Seguradora 2</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>2023-04-09</td>\n",
       "      <td>01773733000139</td>\n",
       "      <td>Petróleo</td>\n",
       "      <td>BA</td>\n",
       "      <td>01773733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Seguradora 2</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>2023-04-12</td>\n",
       "      <td>01770733000139</td>\n",
       "      <td>Mineração</td>\n",
       "      <td>RS</td>\n",
       "      <td>01770733</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      seguradora  valor_solicitado  valor_aprovado data_decisao  \\\n",
       "0   Seguradora 1         1000000.0             0.0   2023-04-02   \n",
       "1   Seguradora 1         1000000.0       1000000.0   2023-04-03   \n",
       "6   Seguradora 2         1000000.0       1000000.0   2023-04-08   \n",
       "7   Seguradora 2         1000000.0        775000.0   2023-04-09   \n",
       "10  Seguradora 2         1000000.0       1000000.0   2023-04-12   \n",
       "\n",
       "        buyer_cnpj buyer_segmento buyer_estado cnpj_raiz  \n",
       "0   01780733000139      Mineração           SP  01780733  \n",
       "1   01779733000139        Energia           RJ  01779733  \n",
       "6   01774733000139        Energia           RN  01774733  \n",
       "7   01773733000139       Petróleo           BA  01773733  \n",
       "10  01770733000139      Mineração           RS  01770733  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Fatiar string\n",
    "\n",
    "df['cnpj_raiz'] = df['buyer_cnpj'].str[:8]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "e44c2fe0-6ea7-44ba-be51-8d4922b0cc74",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Antes:  ['Seguradora 1' 'Seguradora 2' 'Seguradora 3']\n",
      "Antes:  ['Mineração' 'Energia' 'Petróleo' 'Serviço' 'Infraestrutura']\n",
      "\n",
      "Depois:  ['SEGURADORA 1' 'SEGURADORA 2' 'SEGURADORA 3']\n",
      "Depois:  ['mineração' 'energia' 'petróleo' 'serviço' 'infraestrutura']\n"
     ]
    }
   ],
   "source": [
    "#Strip, upper e lower\n",
    "print(\"Antes: \",df['seguradora'].unique())\n",
    "print(\"Antes: \",df['buyer_segmento'].unique())\n",
    "\n",
    "df['seguradora'] = df['seguradora'].str.strip().str.upper()\n",
    "df['buyer_segmento'] = df['buyer_segmento'].str.lower()\n",
    "\n",
    "print(\"\\nDepois: \",df['seguradora'].unique())\n",
    "print(\"Depois: \",df['buyer_segmento'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "22a9ec3a-ef23-401a-ab1a-b65ad8fa38f0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Antes:  ['SP' 'RJ' 'RN' 'BA' 'RS' 'MA' 'JR' 'PR' 'MG']\n",
      "\n",
      "Depois:  ['São Paulo' 'Rio de Janeiro' 'RN' 'Bahia' 'RS' 'MA' 'JR' 'PR' 'MG']\n"
     ]
    }
   ],
   "source": [
    "print(\"Antes: \",df['buyer_estado'].unique())\n",
    "\n",
    "df['buyer_estado'] = df['buyer_estado'].str.replace('SP', 'São Paulo').str.replace('RJ', 'Rio de Janeiro').str.replace('BA', 'Bahia')\n",
    "\n",
    "print(\"\\nDepois: \",df['buyer_estado'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "f1436d0c-2c53-4154-8eb4-887831c799af",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Antes:  ['SEGURADORA 1' 'SEGURADORA 2' 'SEGURADORA 3']\n",
      "\n",
      "Depois:  ['SEGURADORA 1' 'SEGURADORA 2' 'SEGURADORA 3']\n"
     ]
    }
   ],
   "source": [
    "# Remover 2 ou mais espaços entre palavras\n",
    "\n",
    "print(\"Antes: \",df['seguradora'].unique())\n",
    "\n",
    "df['seguradora'] = df['seguradora'].str.replace(r'\\s+', ' ', regex=True)\n",
    "\n",
    "print(\"\\nDepois: \",df['seguradora'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "93e77a08-174b-4d88-b0b1-72fc953fb6c2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      True\n",
       "1      True\n",
       "6     False\n",
       "7     False\n",
       "10    False\n",
       "Name: seguradora, dtype: bool"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['seguradora'].str.match(\"SEGURADORA 1\").head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "e9804a9e-7544-49c2-9883-cc0ba36a6133",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0     2\n",
       "1     2\n",
       "6     2\n",
       "7     2\n",
       "10    2\n",
       "Name: seguradora, dtype: int64"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['seguradora'].str.count(\"A\").head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "b17d7491-39cc-4a2b-8324-0f2c156f8049",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'SEGURADORA 1,SEGURADORA 1,SEGURADORA 2,SEGURADORA 2,SEGURADORA 2,SEGURADORA 2,SEGURADORA 2,SEGURADORA 2,SEGURADORA 2,SEGURADORA 1,SEGURADORA 2,SEGURADORA 2,SEGURADORA 2,SEGURADORA 2,SEGURADORA 1,SEGURADORA 3,SEGURADORA 3,SEGURADORA 3,SEGURADORA 3,SEGURADORA 3,SEGURADORA 3,SEGURADORA 3,SEGURADORA 3,SEGURADORA 1,SEGURADORA 3,SEGURADORA 3,SEGURADORA 2,SEGURADORA 1,SEGURADORA 1,SEGURADORA 1,SEGURADORA 3,SEGURADORA 3,SEGURADORA 3,SEGURADORA 3,SEGURADORA 1,SEGURADORA 3,SEGURADORA 1,SEGURADORA 1,SEGURADORA 2,SEGURADORA 3,SEGURADORA 2,SEGURADORA 3,SEGURADORA 2,SEGURADORA 2,SEGURADORA 2,SEGURADORA 3,SEGURADORA 2,SEGURADORA 2,SEGURADORA 3'"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['seguradora'].str.cat(sep=\",\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "58aa8d56",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4bd72d39",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "407ecfe6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8521f15a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "af48560e-a41a-4ead-952e-916bca085e99",
   "metadata": {},
   "source": [
    "<a id='diff'></a>\n",
    "\n",
    "# Pegar a diferença entre dois dataframes\n",
    "\n",
    "[Voltar para o Sumário](#sumario)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "be14521e-bbd7-4fae-974d-2ede53b22380",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Antes:  (49, 8)\n",
      "Depois:  (43, 8)\n"
     ]
    }
   ],
   "source": [
    "print(\"Antes: \", df.shape)\n",
    "\n",
    "lista_cnpjs = [\"01780733000139\", \"01779733000139\", \"01778733000139\", \"01777733000139\", \"01776733000139\", \"01775733000139\"]\n",
    "df = df[~df['buyer_cnpj'].isin(lista_cnpjs)]\n",
    "# Exemplo de uso com dataframe: df_diff = df[~df['cnpj_raiz'].isin(tb_clientes['cnpj_raiz'])]\n",
    "\n",
    "print(\"Depois: \",df.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e3634daf-dfbd-474b-9f2c-5383c36c6c83",
   "metadata": {},
   "source": [
    "<a id='filtro'></a>\n",
    "\n",
    "# Filtrar o dataframe\n",
    "\n",
    "[Voltar para o Sumário](#sumario)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "86d38fd4-719a-4345-9d2b-5b738d274dfe",
   "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>seguradora</th>\n",
       "      <th>valor_solicitado</th>\n",
       "      <th>valor_aprovado</th>\n",
       "      <th>data_decisao</th>\n",
       "      <th>buyer_cnpj</th>\n",
       "      <th>buyer_segmento</th>\n",
       "      <th>buyer_estado</th>\n",
       "      <th>cnpj_raiz</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>SEGURADORA 1</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2023-05-02</td>\n",
       "      <td>01731733000139</td>\n",
       "      <td>serviço</td>\n",
       "      <td>MA</td>\n",
       "      <td>01731733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>SEGURADORA 1</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>250000.0</td>\n",
       "      <td>2023-05-18</td>\n",
       "      <td>01734733000139</td>\n",
       "      <td>energia</td>\n",
       "      <td>JR</td>\n",
       "      <td>01734733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>SEGURADORA 1</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>2023-04-18</td>\n",
       "      <td>01764733000139</td>\n",
       "      <td>energia</td>\n",
       "      <td>MA</td>\n",
       "      <td>01764733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>SEGURADORA 1</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>2023-04-21</td>\n",
       "      <td>01761733000139</td>\n",
       "      <td>serviço</td>\n",
       "      <td>Bahia</td>\n",
       "      <td>01761733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>SEGURADORA 1</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>2023-04-22</td>\n",
       "      <td>01760733000139</td>\n",
       "      <td>mineração</td>\n",
       "      <td>Bahia</td>\n",
       "      <td>01760733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>SEGURADORA 1</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>2023-04-26</td>\n",
       "      <td>01765733000139</td>\n",
       "      <td>mineração</td>\n",
       "      <td>PR</td>\n",
       "      <td>01765733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>SEGURADORA 1</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>2023-05-02</td>\n",
       "      <td>01762733000139</td>\n",
       "      <td>infraestrutura</td>\n",
       "      <td>Bahia</td>\n",
       "      <td>01762733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>SEGURADORA 1</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>2023-05-07</td>\n",
       "      <td>01763733000139</td>\n",
       "      <td>petróleo</td>\n",
       "      <td>RN</td>\n",
       "      <td>01763733</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      seguradora  valor_solicitado  valor_aprovado data_decisao  \\\n",
       "52  SEGURADORA 1          775000.0             0.0   2023-05-02   \n",
       "46  SEGURADORA 1          775000.0        250000.0   2023-05-18   \n",
       "16  SEGURADORA 1          500000.0        500000.0   2023-04-18   \n",
       "19  SEGURADORA 1          500000.0        500000.0   2023-04-21   \n",
       "20  SEGURADORA 1          500000.0        100000.0   2023-04-22   \n",
       "15  SEGURADORA 1          500000.0        100000.0   2023-04-26   \n",
       "18  SEGURADORA 1          500000.0        100000.0   2023-05-02   \n",
       "17  SEGURADORA 1          500000.0        500000.0   2023-05-07   \n",
       "\n",
       "        buyer_cnpj  buyer_segmento buyer_estado cnpj_raiz  \n",
       "52  01731733000139         serviço           MA  01731733  \n",
       "46  01734733000139         energia           JR  01734733  \n",
       "16  01764733000139         energia           MA  01764733  \n",
       "19  01761733000139         serviço        Bahia  01761733  \n",
       "20  01760733000139       mineração        Bahia  01760733  \n",
       "15  01765733000139       mineração           PR  01765733  \n",
       "18  01762733000139  infraestrutura        Bahia  01762733  \n",
       "17  01763733000139        petróleo           RN  01763733  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Filtro com 1 condição\n",
    "\n",
    "seguradora1 = df[df['seguradora'] == \"SEGURADORA 1\"]\n",
    "seguradora1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fce16b4c",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Filtro com 1 condição\n",
    "\n",
    "seguradora1 = df.loc[df['seguradora'].isin([\"SEGURADORA 1\",])]\n",
    "seguradora1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "44e1ef40",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Filtro com várias condições\n",
    "\n",
    "seguradora1e2 = df.loc[df['seguradora'].isin([\"SEGURADORA 1\", \"SEGURADORA 2\"])]\n",
    "seguradora1e2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a5efc196",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Filtro com várias condições\n",
    "\n",
    "seguradora1 = df.loc[~df['seguradora'].isin([\"SEGURADORA 2\", \"SEGURADORA 3\"])]\n",
    "seguradora1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "b120c189-3c60-4a62-a765-eff3d9c095cc",
   "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>seguradora</th>\n",
       "      <th>valor_solicitado</th>\n",
       "      <th>valor_aprovado</th>\n",
       "      <th>data_decisao</th>\n",
       "      <th>buyer_cnpj</th>\n",
       "      <th>buyer_segmento</th>\n",
       "      <th>buyer_estado</th>\n",
       "      <th>cnpj_raiz</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>SEGURADORA 2</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>2023-04-08</td>\n",
       "      <td>01774733000139</td>\n",
       "      <td>energia</td>\n",
       "      <td>RN</td>\n",
       "      <td>01774733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>SEGURADORA 2</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>2023-04-09</td>\n",
       "      <td>01773733000139</td>\n",
       "      <td>petróleo</td>\n",
       "      <td>Bahia</td>\n",
       "      <td>01773733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>SEGURADORA 2</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>2023-04-12</td>\n",
       "      <td>01770733000139</td>\n",
       "      <td>mineração</td>\n",
       "      <td>RS</td>\n",
       "      <td>01770733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>SEGURADORA 2</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>2023-04-13</td>\n",
       "      <td>01769733000139</td>\n",
       "      <td>energia</td>\n",
       "      <td>São Paulo</td>\n",
       "      <td>01769733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>SEGURADORA 2</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>2023-04-26</td>\n",
       "      <td>01768733000139</td>\n",
       "      <td>petróleo</td>\n",
       "      <td>Rio de Janeiro</td>\n",
       "      <td>01768733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>SEGURADORA 2</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>2023-04-26</td>\n",
       "      <td>01771733000139</td>\n",
       "      <td>serviço</td>\n",
       "      <td>Bahia</td>\n",
       "      <td>01771733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>SEGURADORA 2</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>250000.0</td>\n",
       "      <td>2023-05-02</td>\n",
       "      <td>01772733000139</td>\n",
       "      <td>infraestrutura</td>\n",
       "      <td>Bahia</td>\n",
       "      <td>01772733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>SEGURADORA 2</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>2023-05-07</td>\n",
       "      <td>01767733000139</td>\n",
       "      <td>infraestrutura</td>\n",
       "      <td>JR</td>\n",
       "      <td>01767733</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      seguradora  valor_solicitado  valor_aprovado data_decisao  \\\n",
       "6   SEGURADORA 2         1000000.0       1000000.0   2023-04-08   \n",
       "7   SEGURADORA 2         1000000.0        775000.0   2023-04-09   \n",
       "10  SEGURADORA 2         1000000.0       1000000.0   2023-04-12   \n",
       "11  SEGURADORA 2         1000000.0        775000.0   2023-04-13   \n",
       "12  SEGURADORA 2         1000000.0        500000.0   2023-04-26   \n",
       "9   SEGURADORA 2         1000000.0        100000.0   2023-04-26   \n",
       "8   SEGURADORA 2         1000000.0        250000.0   2023-05-02   \n",
       "13  SEGURADORA 2         1000000.0        775000.0   2023-05-07   \n",
       "\n",
       "        buyer_cnpj  buyer_segmento    buyer_estado cnpj_raiz  \n",
       "6   01774733000139         energia              RN  01774733  \n",
       "7   01773733000139        petróleo           Bahia  01773733  \n",
       "10  01770733000139       mineração              RS  01770733  \n",
       "11  01769733000139         energia       São Paulo  01769733  \n",
       "12  01768733000139        petróleo  Rio de Janeiro  01768733  \n",
       "9   01771733000139         serviço           Bahia  01771733  \n",
       "8   01772733000139  infraestrutura           Bahia  01772733  \n",
       "13  01767733000139  infraestrutura              JR  01767733  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Filtro com 1 condição\n",
    "\n",
    "df_filtrado = df[df['valor_solicitado'] > 775000.0\t]\n",
    "df_filtrado"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "79a189a3-f8b1-4419-9df7-69bd84228e5f",
   "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>seguradora</th>\n",
       "      <th>valor_solicitado</th>\n",
       "      <th>valor_aprovado</th>\n",
       "      <th>data_decisao</th>\n",
       "      <th>buyer_cnpj</th>\n",
       "      <th>buyer_segmento</th>\n",
       "      <th>buyer_estado</th>\n",
       "      <th>cnpj_raiz</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>SEGURADORA 1</td>\n",
       "      <td>775000.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2023-05-02 00:00:00.000000000</td>\n",
       "      <td>01731733000139</td>\n",
       "      <td>serviço</td>\n",
       "      <td>MA</td>\n",
       "      <td>01731733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>SEGURADORA 3</td>\n",
       "      <td>775000.000000</td>\n",
       "      <td>100000.000000</td>\n",
       "      <td>2023-05-10 00:00:00.000000000</td>\n",
       "      <td>01742733000139</td>\n",
       "      <td>infraestrutura</td>\n",
       "      <td>MA</td>\n",
       "      <td>01742733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>SEGURADORA 3</td>\n",
       "      <td>775000.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2023-05-11 00:00:00.000000000</td>\n",
       "      <td>01741733000139</td>\n",
       "      <td>serviço</td>\n",
       "      <td>RN</td>\n",
       "      <td>01741733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>SEGURADORA 3</td>\n",
       "      <td>775000.000000</td>\n",
       "      <td>250000.000000</td>\n",
       "      <td>2023-05-12 00:00:00.000000000</td>\n",
       "      <td>01740733000139</td>\n",
       "      <td>mineração</td>\n",
       "      <td>Bahia</td>\n",
       "      <td>01740733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>SEGURADORA 3</td>\n",
       "      <td>775000.000000</td>\n",
       "      <td>775000.000000</td>\n",
       "      <td>2023-05-13 00:00:00.000000000</td>\n",
       "      <td>01739733000139</td>\n",
       "      <td>energia</td>\n",
       "      <td>Bahia</td>\n",
       "      <td>01739733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>SEGURADORA 3</td>\n",
       "      <td>775000.000000</td>\n",
       "      <td>100000.000000</td>\n",
       "      <td>2023-05-14 00:00:00.000000000</td>\n",
       "      <td>01738733000139</td>\n",
       "      <td>petróleo</td>\n",
       "      <td>Bahia</td>\n",
       "      <td>01738733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>SEGURADORA 3</td>\n",
       "      <td>775000.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2023-05-15 00:00:00.000000000</td>\n",
       "      <td>01737733000139</td>\n",
       "      <td>infraestrutura</td>\n",
       "      <td>RS</td>\n",
       "      <td>01737733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>SEGURADORA 3</td>\n",
       "      <td>775000.000000</td>\n",
       "      <td>250000.000000</td>\n",
       "      <td>2023-05-16 00:00:00.000000000</td>\n",
       "      <td>01736733000139</td>\n",
       "      <td>serviço</td>\n",
       "      <td>São Paulo</td>\n",
       "      <td>01736733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>SEGURADORA 3</td>\n",
       "      <td>775000.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2023-05-17 00:00:00.000000000</td>\n",
       "      <td>01735733000139</td>\n",
       "      <td>mineração</td>\n",
       "      <td>Rio de Janeiro</td>\n",
       "      <td>01735733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>SEGURADORA 1</td>\n",
       "      <td>775000.000000</td>\n",
       "      <td>250000.000000</td>\n",
       "      <td>2023-05-18 00:00:00.000000000</td>\n",
       "      <td>01734733000139</td>\n",
       "      <td>energia</td>\n",
       "      <td>JR</td>\n",
       "      <td>01734733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>SEGURADORA 3</td>\n",
       "      <td>775000.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2023-05-20 00:00:00.000000000</td>\n",
       "      <td>01732733000139</td>\n",
       "      <td>infraestrutura</td>\n",
       "      <td>PR</td>\n",
       "      <td>01732733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>SEGURADORA 3</td>\n",
       "      <td>650961.538462</td>\n",
       "      <td>305288.461538</td>\n",
       "      <td>2023-04-30 10:36:55.384615424</td>\n",
       "      <td>01733733000139</td>\n",
       "      <td>petróleo</td>\n",
       "      <td>MG</td>\n",
       "      <td>01733733</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      seguradora  valor_solicitado  valor_aprovado  \\\n",
       "52  SEGURADORA 1     775000.000000        0.000000   \n",
       "38  SEGURADORA 3     775000.000000   100000.000000   \n",
       "39  SEGURADORA 3     775000.000000        0.000000   \n",
       "40  SEGURADORA 3     775000.000000   250000.000000   \n",
       "41  SEGURADORA 3     775000.000000   775000.000000   \n",
       "42  SEGURADORA 3     775000.000000   100000.000000   \n",
       "43  SEGURADORA 3     775000.000000        0.000000   \n",
       "44  SEGURADORA 3     775000.000000   250000.000000   \n",
       "45  SEGURADORA 3     775000.000000        0.000000   \n",
       "46  SEGURADORA 1     775000.000000   250000.000000   \n",
       "48  SEGURADORA 3     775000.000000        0.000000   \n",
       "47  SEGURADORA 3     650961.538462   305288.461538   \n",
       "\n",
       "                    data_decisao      buyer_cnpj  buyer_segmento  \\\n",
       "52 2023-05-02 00:00:00.000000000  01731733000139         serviço   \n",
       "38 2023-05-10 00:00:00.000000000  01742733000139  infraestrutura   \n",
       "39 2023-05-11 00:00:00.000000000  01741733000139         serviço   \n",
       "40 2023-05-12 00:00:00.000000000  01740733000139       mineração   \n",
       "41 2023-05-13 00:00:00.000000000  01739733000139         energia   \n",
       "42 2023-05-14 00:00:00.000000000  01738733000139        petróleo   \n",
       "43 2023-05-15 00:00:00.000000000  01737733000139  infraestrutura   \n",
       "44 2023-05-16 00:00:00.000000000  01736733000139         serviço   \n",
       "45 2023-05-17 00:00:00.000000000  01735733000139       mineração   \n",
       "46 2023-05-18 00:00:00.000000000  01734733000139         energia   \n",
       "48 2023-05-20 00:00:00.000000000  01732733000139  infraestrutura   \n",
       "47 2023-04-30 10:36:55.384615424  01733733000139        petróleo   \n",
       "\n",
       "      buyer_estado cnpj_raiz  \n",
       "52              MA  01731733  \n",
       "38              MA  01742733  \n",
       "39              RN  01741733  \n",
       "40           Bahia  01740733  \n",
       "41           Bahia  01739733  \n",
       "42           Bahia  01738733  \n",
       "43              RS  01737733  \n",
       "44       São Paulo  01736733  \n",
       "45  Rio de Janeiro  01735733  \n",
       "46              JR  01734733  \n",
       "48              PR  01732733  \n",
       "47              MG  01733733  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Filtro com 2 condições\n",
    "\n",
    "df_filtrado = df[(df['valor_solicitado'] > 500000.0) & (df['valor_solicitado'] < 800000.0)]\n",
    "df_filtrado"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "af4ef135-503b-48c9-9eb1-8043e30ce1f2",
   "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>seguradora</th>\n",
       "      <th>valor_solicitado</th>\n",
       "      <th>valor_aprovado</th>\n",
       "      <th>data_decisao</th>\n",
       "      <th>buyer_cnpj</th>\n",
       "      <th>buyer_segmento</th>\n",
       "      <th>buyer_estado</th>\n",
       "      <th>cnpj_raiz</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>SEGURADORA 3</td>\n",
       "      <td>775000.000000</td>\n",
       "      <td>100000.000000</td>\n",
       "      <td>2023-05-10 00:00:00.000000000</td>\n",
       "      <td>01742733000139</td>\n",
       "      <td>infraestrutura</td>\n",
       "      <td>MA</td>\n",
       "      <td>01742733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>SEGURADORA 3</td>\n",
       "      <td>775000.000000</td>\n",
       "      <td>250000.000000</td>\n",
       "      <td>2023-05-12 00:00:00.000000000</td>\n",
       "      <td>01740733000139</td>\n",
       "      <td>mineração</td>\n",
       "      <td>Bahia</td>\n",
       "      <td>01740733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>SEGURADORA 3</td>\n",
       "      <td>775000.000000</td>\n",
       "      <td>775000.000000</td>\n",
       "      <td>2023-05-13 00:00:00.000000000</td>\n",
       "      <td>01739733000139</td>\n",
       "      <td>energia</td>\n",
       "      <td>Bahia</td>\n",
       "      <td>01739733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>SEGURADORA 3</td>\n",
       "      <td>775000.000000</td>\n",
       "      <td>100000.000000</td>\n",
       "      <td>2023-05-14 00:00:00.000000000</td>\n",
       "      <td>01738733000139</td>\n",
       "      <td>petróleo</td>\n",
       "      <td>Bahia</td>\n",
       "      <td>01738733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>SEGURADORA 3</td>\n",
       "      <td>775000.000000</td>\n",
       "      <td>250000.000000</td>\n",
       "      <td>2023-05-16 00:00:00.000000000</td>\n",
       "      <td>01736733000139</td>\n",
       "      <td>serviço</td>\n",
       "      <td>São Paulo</td>\n",
       "      <td>01736733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>SEGURADORA 1</td>\n",
       "      <td>775000.000000</td>\n",
       "      <td>250000.000000</td>\n",
       "      <td>2023-05-18 00:00:00.000000000</td>\n",
       "      <td>01734733000139</td>\n",
       "      <td>energia</td>\n",
       "      <td>JR</td>\n",
       "      <td>01734733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>SEGURADORA 3</td>\n",
       "      <td>650961.538462</td>\n",
       "      <td>305288.461538</td>\n",
       "      <td>2023-04-30 10:36:55.384615424</td>\n",
       "      <td>01733733000139</td>\n",
       "      <td>petróleo</td>\n",
       "      <td>MG</td>\n",
       "      <td>01733733</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      seguradora  valor_solicitado  valor_aprovado  \\\n",
       "38  SEGURADORA 3     775000.000000   100000.000000   \n",
       "40  SEGURADORA 3     775000.000000   250000.000000   \n",
       "41  SEGURADORA 3     775000.000000   775000.000000   \n",
       "42  SEGURADORA 3     775000.000000   100000.000000   \n",
       "44  SEGURADORA 3     775000.000000   250000.000000   \n",
       "46  SEGURADORA 1     775000.000000   250000.000000   \n",
       "47  SEGURADORA 3     650961.538462   305288.461538   \n",
       "\n",
       "                    data_decisao      buyer_cnpj  buyer_segmento buyer_estado  \\\n",
       "38 2023-05-10 00:00:00.000000000  01742733000139  infraestrutura           MA   \n",
       "40 2023-05-12 00:00:00.000000000  01740733000139       mineração        Bahia   \n",
       "41 2023-05-13 00:00:00.000000000  01739733000139         energia        Bahia   \n",
       "42 2023-05-14 00:00:00.000000000  01738733000139        petróleo        Bahia   \n",
       "44 2023-05-16 00:00:00.000000000  01736733000139         serviço    São Paulo   \n",
       "46 2023-05-18 00:00:00.000000000  01734733000139         energia           JR   \n",
       "47 2023-04-30 10:36:55.384615424  01733733000139        petróleo           MG   \n",
       "\n",
       "   cnpj_raiz  \n",
       "38  01742733  \n",
       "40  01740733  \n",
       "41  01739733  \n",
       "42  01738733  \n",
       "44  01736733  \n",
       "46  01734733  \n",
       "47  01733733  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Filtro com várias condições\n",
    "\n",
    "df_filtrado = df[(df['valor_solicitado'] > 500000.0) & (df['valor_solicitado'] < 800000.0) & (df['valor_aprovado'] > 0)]\n",
    "df_filtrado"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "1d4eba72-b555-436c-84ec-263812786a37",
   "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>seguradora</th>\n",
       "      <th>valor_solicitado</th>\n",
       "      <th>valor_aprovado</th>\n",
       "      <th>data_decisao</th>\n",
       "      <th>buyer_cnpj</th>\n",
       "      <th>buyer_segmento</th>\n",
       "      <th>buyer_estado</th>\n",
       "      <th>cnpj_raiz</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>SEG B</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>2023-04-08</td>\n",
       "      <td>01774733000139</td>\n",
       "      <td>energia</td>\n",
       "      <td>RN</td>\n",
       "      <td>01774733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>SEG B</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>2023-04-09</td>\n",
       "      <td>01773733000139</td>\n",
       "      <td>petróleo</td>\n",
       "      <td>Bahia</td>\n",
       "      <td>01773733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>SEG B</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>2023-04-12</td>\n",
       "      <td>01770733000139</td>\n",
       "      <td>mineração</td>\n",
       "      <td>RS</td>\n",
       "      <td>01770733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>SEG B</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>2023-04-13</td>\n",
       "      <td>01769733000139</td>\n",
       "      <td>energia</td>\n",
       "      <td>São Paulo</td>\n",
       "      <td>01769733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>SEG B</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>2023-04-26</td>\n",
       "      <td>01768733000139</td>\n",
       "      <td>petróleo</td>\n",
       "      <td>Rio de Janeiro</td>\n",
       "      <td>01768733</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   seguradora  valor_solicitado  valor_aprovado data_decisao      buyer_cnpj  \\\n",
       "6       SEG B         1000000.0       1000000.0   2023-04-08  01774733000139   \n",
       "7       SEG B         1000000.0        775000.0   2023-04-09  01773733000139   \n",
       "10      SEG B         1000000.0       1000000.0   2023-04-12  01770733000139   \n",
       "11      SEG B         1000000.0        775000.0   2023-04-13  01769733000139   \n",
       "12      SEG B         1000000.0        500000.0   2023-04-26  01768733000139   \n",
       "\n",
       "   buyer_segmento    buyer_estado cnpj_raiz  \n",
       "6         energia              RN  01774733  \n",
       "7        petróleo           Bahia  01773733  \n",
       "10      mineração              RS  01770733  \n",
       "11        energia       São Paulo  01769733  \n",
       "12       petróleo  Rio de Janeiro  01768733  "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "c9a7058c-06e2-486c-af23-f9094b8fbc87",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['data'] = pd.to_datetime(df['data_decisao'])\n",
    "df['mes'] = df['data_decisao'].dt.month\n",
    "df['ano'] = df['data_decisao'].dt.year\n",
    "\n",
    "\n",
    "df['buyer_cnpj'] = df['buyer_cnpj'].str.replace(r'\\D', '', regex=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "0bdbc0d2",
   "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>seguradora</th>\n",
       "      <th>valor_solicitado</th>\n",
       "      <th>valor_aprovado</th>\n",
       "      <th>data_decisao</th>\n",
       "      <th>buyer_cnpj</th>\n",
       "      <th>buyer_segmento</th>\n",
       "      <th>buyer_estado</th>\n",
       "      <th>cnpj_raiz</th>\n",
       "      <th>data</th>\n",
       "      <th>mes</th>\n",
       "      <th>ano</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>SEG B</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>2023-04-08</td>\n",
       "      <td>01774733000139</td>\n",
       "      <td>energia</td>\n",
       "      <td>RN</td>\n",
       "      <td>01774733</td>\n",
       "      <td>2023-04-08</td>\n",
       "      <td>4</td>\n",
       "      <td>2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>SEG B</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>2023-04-09</td>\n",
       "      <td>01773733000139</td>\n",
       "      <td>petróleo</td>\n",
       "      <td>Bahia</td>\n",
       "      <td>01773733</td>\n",
       "      <td>2023-04-09</td>\n",
       "      <td>4</td>\n",
       "      <td>2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>SEG B</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>2023-04-12</td>\n",
       "      <td>01770733000139</td>\n",
       "      <td>mineração</td>\n",
       "      <td>RS</td>\n",
       "      <td>01770733</td>\n",
       "      <td>2023-04-12</td>\n",
       "      <td>4</td>\n",
       "      <td>2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>SEG B</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>2023-04-13</td>\n",
       "      <td>01769733000139</td>\n",
       "      <td>energia</td>\n",
       "      <td>São Paulo</td>\n",
       "      <td>01769733</td>\n",
       "      <td>2023-04-13</td>\n",
       "      <td>4</td>\n",
       "      <td>2023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>SEG B</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>2023-04-26</td>\n",
       "      <td>01768733000139</td>\n",
       "      <td>petróleo</td>\n",
       "      <td>Rio de Janeiro</td>\n",
       "      <td>01768733</td>\n",
       "      <td>2023-04-26</td>\n",
       "      <td>4</td>\n",
       "      <td>2023</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   seguradora  valor_solicitado  valor_aprovado data_decisao      buyer_cnpj  \\\n",
       "6       SEG B         1000000.0       1000000.0   2023-04-08  01774733000139   \n",
       "7       SEG B         1000000.0        775000.0   2023-04-09  01773733000139   \n",
       "10      SEG B         1000000.0       1000000.0   2023-04-12  01770733000139   \n",
       "11      SEG B         1000000.0        775000.0   2023-04-13  01769733000139   \n",
       "12      SEG B         1000000.0        500000.0   2023-04-26  01768733000139   \n",
       "\n",
       "   buyer_segmento    buyer_estado cnpj_raiz       data  mes   ano  \n",
       "6         energia              RN  01774733 2023-04-08    4  2023  \n",
       "7        petróleo           Bahia  01773733 2023-04-09    4  2023  \n",
       "10      mineração              RS  01770733 2023-04-12    4  2023  \n",
       "11        energia       São Paulo  01769733 2023-04-13    4  2023  \n",
       "12       petróleo  Rio de Janeiro  01768733 2023-04-26    4  2023  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "08878e7f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "f45a3063",
   "metadata": {},
   "source": [
    "<a id='deletar'></a>\n",
    "\n",
    "# Excluir / Deletar registros do dataframe por filtro\n",
    "\n",
    "[Voltar para o Sumário](#sumario)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cecb4016",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a7d6aa99",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Excluir registros\n",
    "\n",
    "seguradora1e2 = df.drop(df[df['seguradora'] == \"SEGURADORA 3\"].index)\n",
    "seguradora1e2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e0167af9",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Filtro com várias condições\n",
    "print(df.shape)\n",
    "\n",
    "registros_a_excluir = df[(df['valor_solicitado'] > 500000.0) & (df['valor_solicitado'] < 800000.0) & (df['valor_aprovado'] > 0)].index\n",
    "\n",
    "print(\"Quantidade de registros a excluir: \", len(registros_a_excluir))\n",
    "\n",
    "df_resultado = df.drop(registros_a_excluir)\n",
    "df_resultado\n",
    "\n",
    "print(df_resultado.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f9dee06b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Manter apenas determinados dados por filtro\n",
    "\n",
    "seguradora1 = df.loc[~df['seguradora'].isin([\"SEGURADORA 2\", \"SEGURADORA 3\"])]\n",
    "seguradora1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "488349d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Manter apenas determinados dados por filtro\n",
    "\n",
    "seguradora1e2 = df.loc[df['seguradora'] != \"SEGURADORA 3\"]\n",
    "seguradora1e2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4803f57f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "4e9cd228",
   "metadata": {},
   "source": [
    "<a id='datas'></a>\n",
    "\n",
    "# Trabalhando com datas\n",
    "\n",
    "[Voltar para o Sumário](#sumario)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "33d57bc4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['data_decisao'] = pd.to_datetime(df['data_decisao'])\n",
    "df['mes'] = df['data_decisao'].dt.month\n",
    "df['ano'] = df['data_decisao'].dt.year\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b50a004",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "01e9851d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4079ccbe",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ed4576de",
   "metadata": {},
   "outputs": [],
   "source": [
    "dff = df.loc[~df['seguradora'].isin([\"SEGURADORA 2\", \"SEGURADORA 3\"])]\n",
    "\n",
    "dff.to_dict(orient='records')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d4bf4aa7",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "54489022",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "230884f3",
   "metadata": {},
   "source": [
    "<a id='substituir'></a>\n",
    "\n",
    "# Substituir valores no dataframe (.map)\n",
    "\n",
    "[Voltar para o Sumário](#sumario)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "32c3f5c5",
   "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>seguradora</th>\n",
       "      <th>valor_solicitado</th>\n",
       "      <th>valor_aprovado</th>\n",
       "      <th>data_decisao</th>\n",
       "      <th>buyer_cnpj</th>\n",
       "      <th>buyer_segmento</th>\n",
       "      <th>buyer_estado</th>\n",
       "      <th>cnpj_raiz</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>SEG B</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>2023-04-08</td>\n",
       "      <td>01774733000139</td>\n",
       "      <td>energia</td>\n",
       "      <td>RN</td>\n",
       "      <td>01774733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>SEG B</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>2023-04-09</td>\n",
       "      <td>01773733000139</td>\n",
       "      <td>petróleo</td>\n",
       "      <td>Bahia</td>\n",
       "      <td>01773733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>SEG B</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>2023-04-12</td>\n",
       "      <td>01770733000139</td>\n",
       "      <td>mineração</td>\n",
       "      <td>RS</td>\n",
       "      <td>01770733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>SEG B</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>2023-04-13</td>\n",
       "      <td>01769733000139</td>\n",
       "      <td>energia</td>\n",
       "      <td>São Paulo</td>\n",
       "      <td>01769733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>SEG B</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>2023-04-26</td>\n",
       "      <td>01768733000139</td>\n",
       "      <td>petróleo</td>\n",
       "      <td>Rio de Janeiro</td>\n",
       "      <td>01768733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>SEG B</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>2023-04-26</td>\n",
       "      <td>01771733000139</td>\n",
       "      <td>serviço</td>\n",
       "      <td>Bahia</td>\n",
       "      <td>01771733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>SEG B</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>250000.0</td>\n",
       "      <td>2023-05-02</td>\n",
       "      <td>01772733000139</td>\n",
       "      <td>infraestrutura</td>\n",
       "      <td>Bahia</td>\n",
       "      <td>01772733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>SEG B</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>2023-05-07</td>\n",
       "      <td>01767733000139</td>\n",
       "      <td>infraestrutura</td>\n",
       "      <td>JR</td>\n",
       "      <td>01767733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>SEG A</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2023-05-02</td>\n",
       "      <td>01731733000139</td>\n",
       "      <td>serviço</td>\n",
       "      <td>MA</td>\n",
       "      <td>01731733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>SEG C</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>2023-05-10</td>\n",
       "      <td>01742733000139</td>\n",
       "      <td>infraestrutura</td>\n",
       "      <td>MA</td>\n",
       "      <td>01742733</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   seguradora  valor_solicitado  valor_aprovado data_decisao      buyer_cnpj  \\\n",
       "6       SEG B         1000000.0       1000000.0   2023-04-08  01774733000139   \n",
       "7       SEG B         1000000.0        775000.0   2023-04-09  01773733000139   \n",
       "10      SEG B         1000000.0       1000000.0   2023-04-12  01770733000139   \n",
       "11      SEG B         1000000.0        775000.0   2023-04-13  01769733000139   \n",
       "12      SEG B         1000000.0        500000.0   2023-04-26  01768733000139   \n",
       "9       SEG B         1000000.0        100000.0   2023-04-26  01771733000139   \n",
       "8       SEG B         1000000.0        250000.0   2023-05-02  01772733000139   \n",
       "13      SEG B         1000000.0        775000.0   2023-05-07  01767733000139   \n",
       "52      SEG A          775000.0             0.0   2023-05-02  01731733000139   \n",
       "38      SEG C          775000.0        100000.0   2023-05-10  01742733000139   \n",
       "\n",
       "    buyer_segmento    buyer_estado cnpj_raiz  \n",
       "6          energia              RN  01774733  \n",
       "7         petróleo           Bahia  01773733  \n",
       "10       mineração              RS  01770733  \n",
       "11         energia       São Paulo  01769733  \n",
       "12        petróleo  Rio de Janeiro  01768733  \n",
       "9          serviço           Bahia  01771733  \n",
       "8   infraestrutura           Bahia  01772733  \n",
       "13  infraestrutura              JR  01767733  \n",
       "52         serviço              MA  01731733  \n",
       "38  infraestrutura              MA  01742733  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "5a01776f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['seguradora'] = df['seguradora'].map({\n",
    "    'SEGURADORA 1': 'SEG A', 'SEGURADORA 2': 'SEG B', 'SEGURADORA 3': 'SEG C'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "aadbcbd8",
   "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>seguradora</th>\n",
       "      <th>valor_solicitado</th>\n",
       "      <th>valor_aprovado</th>\n",
       "      <th>data_decisao</th>\n",
       "      <th>buyer_cnpj</th>\n",
       "      <th>buyer_segmento</th>\n",
       "      <th>buyer_estado</th>\n",
       "      <th>cnpj_raiz</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>SEG B</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>2023-04-08</td>\n",
       "      <td>01774733000139</td>\n",
       "      <td>energia</td>\n",
       "      <td>RN</td>\n",
       "      <td>01774733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>SEG B</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>2023-04-09</td>\n",
       "      <td>01773733000139</td>\n",
       "      <td>petróleo</td>\n",
       "      <td>Bahia</td>\n",
       "      <td>01773733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>SEG B</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>2023-04-12</td>\n",
       "      <td>01770733000139</td>\n",
       "      <td>mineração</td>\n",
       "      <td>RS</td>\n",
       "      <td>01770733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>SEG B</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>2023-04-13</td>\n",
       "      <td>01769733000139</td>\n",
       "      <td>energia</td>\n",
       "      <td>São Paulo</td>\n",
       "      <td>01769733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>SEG B</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>500000.0</td>\n",
       "      <td>2023-04-26</td>\n",
       "      <td>01768733000139</td>\n",
       "      <td>petróleo</td>\n",
       "      <td>Rio de Janeiro</td>\n",
       "      <td>01768733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>SEG B</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>2023-04-26</td>\n",
       "      <td>01771733000139</td>\n",
       "      <td>serviço</td>\n",
       "      <td>Bahia</td>\n",
       "      <td>01771733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>SEG B</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>250000.0</td>\n",
       "      <td>2023-05-02</td>\n",
       "      <td>01772733000139</td>\n",
       "      <td>infraestrutura</td>\n",
       "      <td>Bahia</td>\n",
       "      <td>01772733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>SEG B</td>\n",
       "      <td>1000000.0</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>2023-05-07</td>\n",
       "      <td>01767733000139</td>\n",
       "      <td>infraestrutura</td>\n",
       "      <td>JR</td>\n",
       "      <td>01767733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>SEG A</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2023-05-02</td>\n",
       "      <td>01731733000139</td>\n",
       "      <td>serviço</td>\n",
       "      <td>MA</td>\n",
       "      <td>01731733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>SEG C</td>\n",
       "      <td>775000.0</td>\n",
       "      <td>100000.0</td>\n",
       "      <td>2023-05-10</td>\n",
       "      <td>01742733000139</td>\n",
       "      <td>infraestrutura</td>\n",
       "      <td>MA</td>\n",
       "      <td>01742733</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   seguradora  valor_solicitado  valor_aprovado data_decisao      buyer_cnpj  \\\n",
       "6       SEG B         1000000.0       1000000.0   2023-04-08  01774733000139   \n",
       "7       SEG B         1000000.0        775000.0   2023-04-09  01773733000139   \n",
       "10      SEG B         1000000.0       1000000.0   2023-04-12  01770733000139   \n",
       "11      SEG B         1000000.0        775000.0   2023-04-13  01769733000139   \n",
       "12      SEG B         1000000.0        500000.0   2023-04-26  01768733000139   \n",
       "9       SEG B         1000000.0        100000.0   2023-04-26  01771733000139   \n",
       "8       SEG B         1000000.0        250000.0   2023-05-02  01772733000139   \n",
       "13      SEG B         1000000.0        775000.0   2023-05-07  01767733000139   \n",
       "52      SEG A          775000.0             0.0   2023-05-02  01731733000139   \n",
       "38      SEG C          775000.0        100000.0   2023-05-10  01742733000139   \n",
       "\n",
       "    buyer_segmento    buyer_estado cnpj_raiz  \n",
       "6          energia              RN  01774733  \n",
       "7         petróleo           Bahia  01773733  \n",
       "10       mineração              RS  01770733  \n",
       "11         energia       São Paulo  01769733  \n",
       "12        petróleo  Rio de Janeiro  01768733  \n",
       "9          serviço           Bahia  01771733  \n",
       "8   infraestrutura           Bahia  01772733  \n",
       "13  infraestrutura              JR  01767733  \n",
       "52         serviço              MA  01731733  \n",
       "38  infraestrutura              MA  01742733  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c6081c53",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "44836405",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e5fc3642",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "8ec3e763",
   "metadata": {},
   "source": [
    "<a id='bd'></a>\n",
    "\n",
    "# Operações com banco de dados Sqlite (sqlite3)\n",
    "\n",
    "[Voltar para o Sumário](#sumario)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "e6a40a93",
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'sqlite3' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[60], line 1\u001b[0m\n\u001b[1;32m----> 1\u001b[0m conn \u001b[38;5;241m=\u001b[39m \u001b[43msqlite3\u001b[49m\u001b[38;5;241m.\u001b[39mconnect(\u001b[38;5;124mfr\u001b[39m\u001b[38;5;124m'\u001b[39m\u001b[38;5;132;01m{\u001b[39;00mos\u001b[38;5;241m.\u001b[39mpath\u001b[38;5;241m.\u001b[39mexpanduser(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124m~\u001b[39m\u001b[38;5;124m\"\u001b[39m)\u001b[38;5;132;01m}\u001b[39;00m\u001b[38;5;124m\\\u001b[39m\u001b[38;5;124mArcelorMittal\u001b[39m\u001b[38;5;124m\\\u001b[39m\u001b[38;5;124mDados da VP Corporativa de Finanças e TI - Crédito Corporativo\u001b[39m\u001b[38;5;124m\\\u001b[39m\u001b[38;5;124mAutomações\u001b[39m\u001b[38;5;124m\\\u001b[39m\u001b[38;5;124mBanco de dados\u001b[39m\u001b[38;5;124m\\\u001b[39m\u001b[38;5;124mDados de Credito.db\u001b[39m\u001b[38;5;124m'\u001b[39m)\n\u001b[0;32m      3\u001b[0m consulta_coface \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m'''\u001b[39m\n\u001b[0;32m      4\u001b[0m \u001b[38;5;124mSELECT c.cnpj_raiz, c.data_decisao_coface, c.valor_aprovado_coface, c.freeup\u001b[39m\n\u001b[0;32m      5\u001b[0m \u001b[38;5;124mFROM apolice_consolidada_coface AS c\u001b[39m\n\u001b[0;32m      6\u001b[0m \u001b[38;5;124m'''\u001b[39m\n\u001b[0;32m      8\u001b[0m coface \u001b[38;5;241m=\u001b[39m pd\u001b[38;5;241m.\u001b[39mread_sql_query(consulta_coface, conn, dtype\u001b[38;5;241m=\u001b[39m{\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mcnpj_raiz\u001b[39m\u001b[38;5;124m'\u001b[39m: \u001b[38;5;28mstr\u001b[39m})\n",
      "\u001b[1;31mNameError\u001b[0m: name 'sqlite3' is not defined"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "\n",
    "banco_de_dados = fr'{os.path.expanduser(\"~\")}\\caminho\\banco.db'\n",
    "\n",
    "conn = sqlite3.connect(banco_de_dados)\n",
    "\n",
    "consulta = '''\n",
    "SELECT c.tabela, c.tabela, c.tabela, c.tabela\n",
    "FROM tabela AS c\n",
    "'''\n",
    "\n",
    "coface = pd.read_sql_query(consulta, conn, dtype={'tabela': str})\n",
    "\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2cf24461",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9bbfb257",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "806ecd55",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ef98b053",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bc3c54ed",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "62b3f2e0",
   "metadata": {},
   "source": [
    "<a id='concat'></a>\n",
    "\n",
    "# Concatenar Dataframes (.concat)\n",
    "\n",
    "[Voltar para o Sumário](#sumario)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "3f49240a",
   "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>Nome</th>\n",
       "      <th>Idade</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Filipe</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Marcos</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Luiz</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Nome  Idade\n",
       "0  Filipe     30\n",
       "1  Marcos     45\n",
       "2    Luiz      1"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d1 = {\"Nome\": [\"Filipe\"], \"Idade\": [30]}\n",
    "d2 = {\"Nome\": [\"Marcos\"], \"Idade\": [45]}\n",
    "d3 = {\"Nome\": [\"Luiz\"], \"Idade\": [1]}\n",
    "\n",
    "df1 = pd.DataFrame(d1)\n",
    "df2 = pd.DataFrame(d2)\n",
    "df3 = pd.DataFrame(d3)\n",
    "\n",
    "df_concatenado = pd.concat([df1, df2, df3], ignore_index=True)\n",
    "df_concatenado"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2249f753",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0ddd2ca2",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "136fd960",
   "metadata": {},
   "source": [
    "<a id='merge'></a>\n",
    "\n",
    "# Relacionar Dataframes (.merge)\n",
    "\n",
    "[Voltar para o Sumário](#sumario)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "6c0ad6bf",
   "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>nome</th>\n",
       "      <th>idade</th>\n",
       "      <th>status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Filipe</td>\n",
       "      <td>33</td>\n",
       "      <td>Aprovado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Marcos</td>\n",
       "      <td>45</td>\n",
       "      <td>Recusado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Luiz</td>\n",
       "      <td>15</td>\n",
       "      <td>Aprovado</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     nome  idade    status\n",
       "0  Filipe     33  Aprovado\n",
       "1  Marcos     45  Recusado\n",
       "2    Luiz     15  Aprovado"
      ]
     },
     "execution_count": 83,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Criando dois DataFrames de exemplo\n",
    "df1 = pd.DataFrame({'nome': ['Filipe', 'Marcos', 'Luiz'],\n",
    "                   'idade': [33, 45, 15]})\n",
    "df2 = pd.DataFrame({'nome': ['Filipe', 'Marcos', 'Luiz'],\n",
    "                   'status': ['Aprovado', 'Recusado', 'Aprovado']})\n",
    "\n",
    "# Fazendo um merge interno (inner join)\n",
    "df_merged = pd.merge(df1, df2, on='nome')\n",
    "df_merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "aaf7249b",
   "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",
       "      <th>cliente_id</th>\n",
       "      <th>idade</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Alice</td>\n",
       "      <td>1.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Bob</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Charlie</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id     nome  cliente_id  idade     _merge\n",
       "0   1    Alice         1.0   25.0       both\n",
       "1   2      Bob         NaN    NaN  left_only\n",
       "2   3  Charlie         3.0   30.0       both"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Criando DataFrames com nomes de colunas diferentes para a chave\n",
    "df5 = pd.DataFrame({'id': [1, 2, 3],\n",
    "                   'nome': ['Alice', 'Bob', 'Charlie']})\n",
    "df6 = pd.DataFrame({'cliente_id': [1, 3, 4],\n",
    "                   'idade': [25, 30, 55]})\n",
    "\n",
    "# Merge com nomes de colunas diferentes e indicador\n",
    "df_merged = pd.merge(df5, df6, left_on='id', right_on='cliente_id', how='left', indicator=True)\n",
    "df_merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "8e37ec6b",
   "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",
       "      <th>cliente_id</th>\n",
       "      <th>idade</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>Alice</td>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3.0</td>\n",
       "      <td>Charlie</td>\n",
       "      <td>3</td>\n",
       "      <td>30</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>55</td>\n",
       "      <td>right_only</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id     nome  cliente_id  idade      _merge\n",
       "0  1.0    Alice           1     25        both\n",
       "1  3.0  Charlie           3     30        both\n",
       "2  NaN      NaN           4     55  right_only"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Criando DataFrames com nomes de colunas diferentes para a chave\n",
    "df5 = pd.DataFrame({'id': [1, 2, 3],\n",
    "                   'nome': ['Alice', 'Bob', 'Charlie']})\n",
    "df6 = pd.DataFrame({'cliente_id': [1, 3, 4],\n",
    "                   'idade': [25, 30, 55]})\n",
    "\n",
    "# Merge com nomes de colunas diferentes e indicador\n",
    "df_merged = pd.merge(df5, df6, left_on='id', right_on='cliente_id', how='right', indicator=True)\n",
    "df_merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "9a0d883e",
   "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",
       "      <th>cliente_id</th>\n",
       "      <th>idade</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Alice</td>\n",
       "      <td>1</td>\n",
       "      <td>25</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>Charlie</td>\n",
       "      <td>3</td>\n",
       "      <td>30</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id     nome  cliente_id  idade _merge\n",
       "0   1    Alice           1     25   both\n",
       "1   3  Charlie           3     30   both"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Criando DataFrames com nomes de colunas diferentes para a chave\n",
    "df5 = pd.DataFrame({'id': [1, 2, 3],\n",
    "                   'nome': ['Alice', 'Bob', 'Charlie']})\n",
    "df6 = pd.DataFrame({'cliente_id': [1, 3, 4],\n",
    "                   'idade': [25, 30, 55]})\n",
    "# Merge com nomes de colunas diferentes e indicador\n",
    "df_merged = pd.merge(df5, df6, left_on='id', right_on='cliente_id', how='inner', indicator=True)\n",
    "df_merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "46b5331c",
   "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",
       "      <th>cliente_id</th>\n",
       "      <th>idade</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>Alice</td>\n",
       "      <td>1.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2.0</td>\n",
       "      <td>Bob</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>left_only</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3.0</td>\n",
       "      <td>Charlie</td>\n",
       "      <td>3.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>right_only</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id     nome  cliente_id  idade      _merge\n",
       "0  1.0    Alice         1.0   25.0        both\n",
       "1  2.0      Bob         NaN    NaN   left_only\n",
       "2  3.0  Charlie         3.0   30.0        both\n",
       "3  NaN      NaN         4.0   55.0  right_only"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Criando DataFrames com nomes de colunas diferentes para a chave\n",
    "df5 = pd.DataFrame({'id': [1, 2, 3],\n",
    "                   'nome': ['Alice', 'Bob', 'Charlie']})\n",
    "df6 = pd.DataFrame({'cliente_id': [1, 3, 4],\n",
    "                   'idade': [25, 30, 55]})\n",
    "\n",
    "# Merge com nomes de colunas diferentes e indicador\n",
    "df_merged = pd.merge(df5, df6, left_on='id', right_on='cliente_id', how='outer', indicator=True)\n",
    "df_merged"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65f081bb",
   "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.10.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}