SQL-Join.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## SQL Join e Junção de Tabelas com Pandas "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Imports\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<img src=\"imagens/sql-join.png\"/>"
      ],
      "text/plain": [
       "<IPython.core.display.Image object>"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from IPython.display import Image\n",
    "Image(url = 'imagens/sql-join.png')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>disciplina_id</th>\n",
       "      <th>nome</th>\n",
       "      <th>sobrenome</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Bernardo</td>\n",
       "      <td>Anderson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Alan</td>\n",
       "      <td>Teixeira</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Mateus</td>\n",
       "      <td>Amoedo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Ivo</td>\n",
       "      <td>Trindade</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Gerson</td>\n",
       "      <td>Vargas</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  disciplina_id      nome sobrenome\n",
       "0             1  Bernardo  Anderson\n",
       "1             2      Alan  Teixeira\n",
       "2             3    Mateus    Amoedo\n",
       "3             4       Ivo  Trindade\n",
       "4             5    Gerson    Vargas"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dados originais\n",
    "dados1 = {\n",
    "        'disciplina_id': ['1', '2', '3', '4', '5'],\n",
    "        'nome': ['Bernardo', 'Alan', 'Mateus', 'Ivo', 'Gerson'], \n",
    "        'sobrenome': ['Anderson', 'Teixeira', 'Amoedo', 'Trindade', 'Vargas']}\n",
    "\n",
    "# Criação do dataframe\n",
    "df_a = pd.DataFrame(dados1, columns = ['disciplina_id', 'nome', 'sobrenome'])\n",
    "df_a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>disciplina_id</th>\n",
       "      <th>nome</th>\n",
       "      <th>sobrenome</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>Roberto</td>\n",
       "      <td>Sampaio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>Mariana</td>\n",
       "      <td>Fernandes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>Ana</td>\n",
       "      <td>Arantes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>Marcos</td>\n",
       "      <td>Menezes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>Maria</td>\n",
       "      <td>Martins</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  disciplina_id     nome  sobrenome\n",
       "0             4  Roberto    Sampaio\n",
       "1             5  Mariana  Fernandes\n",
       "2             6      Ana    Arantes\n",
       "3             7   Marcos    Menezes\n",
       "4             8    Maria    Martins"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dados originais\n",
    "dados2 = {\n",
    "        'disciplina_id': ['4', '5', '6', '7', '8'],\n",
    "        'nome': ['Roberto', 'Mariana', 'Ana', 'Marcos', 'Maria'], \n",
    "        'sobrenome': ['Sampaio', 'Fernandes', 'Arantes', 'Menezes', 'Martins']}\n",
    "\n",
    "# Criação do dataframe\n",
    "df_b = pd.DataFrame(dados2, columns = ['disciplina_id', 'nome', 'sobrenome'])\n",
    "df_b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>disciplina_id</th>\n",
       "      <th>teste_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>7</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>8</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>9</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>10</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>11</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  disciplina_id  teste_id\n",
       "0             1        81\n",
       "1             2        75\n",
       "2             3        75\n",
       "3             4        71\n",
       "4             5        76\n",
       "5             7        84\n",
       "6             8        95\n",
       "7             9        61\n",
       "8            10        57\n",
       "9            11        90"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dados originais\n",
    "dados3 = {\n",
    "        'disciplina_id': ['1', '2', '3', '4', '5', '7', '8', '9', '10', '11'],\n",
    "        'teste_id': [81, 75, 75, 71, 76, 84, 95, 61, 57, 90]}\n",
    "\n",
    "# Criação do dataframe\n",
    "df_n = pd.DataFrame(dados3, columns = ['disciplina_id','teste_id'])\n",
    "df_n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>disciplina_id</th>\n",
       "      <th>nome</th>\n",
       "      <th>sobrenome</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Bernardo</td>\n",
       "      <td>Anderson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Alan</td>\n",
       "      <td>Teixeira</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Mateus</td>\n",
       "      <td>Amoedo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Ivo</td>\n",
       "      <td>Trindade</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Gerson</td>\n",
       "      <td>Vargas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>Roberto</td>\n",
       "      <td>Sampaio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>Mariana</td>\n",
       "      <td>Fernandes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>Ana</td>\n",
       "      <td>Arantes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>Marcos</td>\n",
       "      <td>Menezes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>Maria</td>\n",
       "      <td>Martins</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  disciplina_id      nome  sobrenome\n",
       "0             1  Bernardo   Anderson\n",
       "1             2      Alan   Teixeira\n",
       "2             3    Mateus     Amoedo\n",
       "3             4       Ivo   Trindade\n",
       "4             5    Gerson     Vargas\n",
       "0             4   Roberto    Sampaio\n",
       "1             5   Mariana  Fernandes\n",
       "2             6       Ana    Arantes\n",
       "3             7    Marcos    Menezes\n",
       "4             8     Maria    Martins"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Join dos dataframes pelas linhas\n",
    "df_new = pd.concat([df_a, df_b])\n",
    "df_new"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>disciplina_id</th>\n",
       "      <th>nome</th>\n",
       "      <th>sobrenome</th>\n",
       "      <th>disciplina_id</th>\n",
       "      <th>nome</th>\n",
       "      <th>sobrenome</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Bernardo</td>\n",
       "      <td>Anderson</td>\n",
       "      <td>4</td>\n",
       "      <td>Roberto</td>\n",
       "      <td>Sampaio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Alan</td>\n",
       "      <td>Teixeira</td>\n",
       "      <td>5</td>\n",
       "      <td>Mariana</td>\n",
       "      <td>Fernandes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Mateus</td>\n",
       "      <td>Amoedo</td>\n",
       "      <td>6</td>\n",
       "      <td>Ana</td>\n",
       "      <td>Arantes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Ivo</td>\n",
       "      <td>Trindade</td>\n",
       "      <td>7</td>\n",
       "      <td>Marcos</td>\n",
       "      <td>Menezes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Gerson</td>\n",
       "      <td>Vargas</td>\n",
       "      <td>8</td>\n",
       "      <td>Maria</td>\n",
       "      <td>Martins</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  disciplina_id      nome sobrenome disciplina_id     nome  sobrenome\n",
       "0             1  Bernardo  Anderson             4  Roberto    Sampaio\n",
       "1             2      Alan  Teixeira             5  Mariana  Fernandes\n",
       "2             3    Mateus    Amoedo             6      Ana    Arantes\n",
       "3             4       Ivo  Trindade             7   Marcos    Menezes\n",
       "4             5    Gerson    Vargas             8    Maria    Martins"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Join dos dataframes pelas colunas\n",
    "pd.concat([df_a, df_b], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>disciplina_id</th>\n",
       "      <th>nome</th>\n",
       "      <th>sobrenome</th>\n",
       "      <th>teste_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Bernardo</td>\n",
       "      <td>Anderson</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Alan</td>\n",
       "      <td>Teixeira</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Mateus</td>\n",
       "      <td>Amoedo</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Ivo</td>\n",
       "      <td>Trindade</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Roberto</td>\n",
       "      <td>Sampaio</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>Gerson</td>\n",
       "      <td>Vargas</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5</td>\n",
       "      <td>Mariana</td>\n",
       "      <td>Fernandes</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>Marcos</td>\n",
       "      <td>Menezes</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>Maria</td>\n",
       "      <td>Martins</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  disciplina_id      nome  sobrenome  teste_id\n",
       "0             1  Bernardo   Anderson        81\n",
       "1             2      Alan   Teixeira        75\n",
       "2             3    Mateus     Amoedo        75\n",
       "3             4       Ivo   Trindade        71\n",
       "4             4   Roberto    Sampaio        71\n",
       "5             5    Gerson     Vargas        76\n",
       "6             5   Mariana  Fernandes        76\n",
       "7             7    Marcos    Menezes        84\n",
       "8             8     Maria    Martins        95"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Join de dois dataframes pela coluna disciplina_id\n",
    "pd.merge(df_new, df_n, on = 'disciplina_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>disciplina_id</th>\n",
       "      <th>nome</th>\n",
       "      <th>sobrenome</th>\n",
       "      <th>teste_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Bernardo</td>\n",
       "      <td>Anderson</td>\n",
       "      <td>81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Alan</td>\n",
       "      <td>Teixeira</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Mateus</td>\n",
       "      <td>Amoedo</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Ivo</td>\n",
       "      <td>Trindade</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Roberto</td>\n",
       "      <td>Sampaio</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>Gerson</td>\n",
       "      <td>Vargas</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5</td>\n",
       "      <td>Mariana</td>\n",
       "      <td>Fernandes</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>Marcos</td>\n",
       "      <td>Menezes</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>Maria</td>\n",
       "      <td>Martins</td>\n",
       "      <td>95</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  disciplina_id      nome  sobrenome  teste_id\n",
       "0             1  Bernardo   Anderson        81\n",
       "1             2      Alan   Teixeira        75\n",
       "2             3    Mateus     Amoedo        75\n",
       "3             4       Ivo   Trindade        71\n",
       "4             4   Roberto    Sampaio        71\n",
       "5             5    Gerson     Vargas        76\n",
       "6             5   Mariana  Fernandes        76\n",
       "7             7    Marcos    Menezes        84\n",
       "8             8     Maria    Martins        95"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Join de dois dataframes pela coluna disciplina_id (igual ao item anterior)\n",
    "pd.merge(df_new, df_n, left_on = 'disciplina_id', right_on = 'disciplina_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>disciplina_id</th>\n",
       "      <th>nome_x</th>\n",
       "      <th>sobrenome_x</th>\n",
       "      <th>nome_y</th>\n",
       "      <th>sobrenome_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Bernardo</td>\n",
       "      <td>Anderson</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Alan</td>\n",
       "      <td>Teixeira</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Mateus</td>\n",
       "      <td>Amoedo</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Ivo</td>\n",
       "      <td>Trindade</td>\n",
       "      <td>Roberto</td>\n",
       "      <td>Sampaio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Gerson</td>\n",
       "      <td>Vargas</td>\n",
       "      <td>Mariana</td>\n",
       "      <td>Fernandes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ana</td>\n",
       "      <td>Arantes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Marcos</td>\n",
       "      <td>Menezes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Maria</td>\n",
       "      <td>Martins</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  disciplina_id    nome_x sobrenome_x   nome_y sobrenome_y\n",
       "0             1  Bernardo    Anderson      NaN         NaN\n",
       "1             2      Alan    Teixeira      NaN         NaN\n",
       "2             3    Mateus      Amoedo      NaN         NaN\n",
       "3             4       Ivo    Trindade  Roberto     Sampaio\n",
       "4             5    Gerson      Vargas  Mariana   Fernandes\n",
       "5             6       NaN         NaN      Ana     Arantes\n",
       "6             7       NaN         NaN   Marcos     Menezes\n",
       "7             8       NaN         NaN    Maria     Martins"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge outer join\n",
    "# “A união externa completa produz o conjunto de todos os registros na Tabela A e na Tabela B, \n",
    "# com registros correspondentes de ambos os lados, quando disponíveis. \n",
    "# Se não houver correspondência, o lado ausente conterá null.”\n",
    "pd.merge(df_a, df_b, on = 'disciplina_id', how = 'outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>disciplina_id</th>\n",
       "      <th>nome_x</th>\n",
       "      <th>sobrenome_x</th>\n",
       "      <th>nome_y</th>\n",
       "      <th>sobrenome_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>Ivo</td>\n",
       "      <td>Trindade</td>\n",
       "      <td>Roberto</td>\n",
       "      <td>Sampaio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>Gerson</td>\n",
       "      <td>Vargas</td>\n",
       "      <td>Mariana</td>\n",
       "      <td>Fernandes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  disciplina_id  nome_x sobrenome_x   nome_y sobrenome_y\n",
       "0             4     Ivo    Trindade  Roberto     Sampaio\n",
       "1             5  Gerson      Vargas  Mariana   Fernandes"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge inner join\n",
    "# “A junção interna produz apenas o conjunto de registros que correspondem na Tabela A e na Tabela B.”\n",
    "pd.merge(df_a, df_b, on = 'disciplina_id', how = 'inner')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>disciplina_id</th>\n",
       "      <th>nome_x</th>\n",
       "      <th>sobrenome_x</th>\n",
       "      <th>nome_y</th>\n",
       "      <th>sobrenome_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Bernardo</td>\n",
       "      <td>Anderson</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Alan</td>\n",
       "      <td>Teixeira</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Mateus</td>\n",
       "      <td>Amoedo</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Ivo</td>\n",
       "      <td>Trindade</td>\n",
       "      <td>Roberto</td>\n",
       "      <td>Sampaio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Gerson</td>\n",
       "      <td>Vargas</td>\n",
       "      <td>Mariana</td>\n",
       "      <td>Fernandes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  disciplina_id    nome_x sobrenome_x   nome_y sobrenome_y\n",
       "0             1  Bernardo    Anderson      NaN         NaN\n",
       "1             2      Alan    Teixeira      NaN         NaN\n",
       "2             3    Mateus      Amoedo      NaN         NaN\n",
       "3             4       Ivo    Trindade  Roberto     Sampaio\n",
       "4             5    Gerson      Vargas  Mariana   Fernandes"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge left join\n",
    "# \"Junção externa esquerda produz um conjunto completo de registros da Tabela A, \n",
    "# com os registros correspondentes (quando disponíveis) na Tabela B. \n",
    "# Se não houver correspondência, o lado direito conterá nulo.\"\n",
    "pd.merge(df_a, df_b, on = 'disciplina_id', how = 'left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>disciplina_id</th>\n",
       "      <th>nome_x</th>\n",
       "      <th>sobrenome_x</th>\n",
       "      <th>nome_y</th>\n",
       "      <th>sobrenome_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>Ivo</td>\n",
       "      <td>Trindade</td>\n",
       "      <td>Roberto</td>\n",
       "      <td>Sampaio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>Gerson</td>\n",
       "      <td>Vargas</td>\n",
       "      <td>Mariana</td>\n",
       "      <td>Fernandes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Ana</td>\n",
       "      <td>Arantes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Marcos</td>\n",
       "      <td>Menezes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Maria</td>\n",
       "      <td>Martins</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  disciplina_id  nome_x sobrenome_x   nome_y sobrenome_y\n",
       "0             4     Ivo    Trindade  Roberto     Sampaio\n",
       "1             5  Gerson      Vargas  Mariana   Fernandes\n",
       "2             6     NaN         NaN      Ana     Arantes\n",
       "3             7     NaN         NaN   Marcos     Menezes\n",
       "4             8     NaN         NaN    Maria     Martins"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge right join (contrário do item anterior)\n",
    "pd.merge(df_a, df_b, on = 'disciplina_id', how = 'right')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>disciplina_id</th>\n",
       "      <th>nome_left</th>\n",
       "      <th>sobrenome_left</th>\n",
       "      <th>nome_right</th>\n",
       "      <th>sobrenome_right</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Bernardo</td>\n",
       "      <td>Anderson</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Alan</td>\n",
       "      <td>Teixeira</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Mateus</td>\n",
       "      <td>Amoedo</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Ivo</td>\n",
       "      <td>Trindade</td>\n",
       "      <td>Roberto</td>\n",
       "      <td>Sampaio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Gerson</td>\n",
       "      <td>Vargas</td>\n",
       "      <td>Mariana</td>\n",
       "      <td>Fernandes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  disciplina_id nome_left sobrenome_left nome_right sobrenome_right\n",
       "0             1  Bernardo       Anderson        NaN             NaN\n",
       "1             2      Alan       Teixeira        NaN             NaN\n",
       "2             3    Mateus         Amoedo        NaN             NaN\n",
       "3             4       Ivo       Trindade    Roberto         Sampaio\n",
       "4             5    Gerson         Vargas    Mariana       Fernandes"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Adicionando um sufixo para identificar os nomes das colunas\n",
    "pd.merge(df_a, df_b, on = 'disciplina_id', how = 'left', suffixes = ('_left', '_right'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>disciplina_id_x</th>\n",
       "      <th>nome_x</th>\n",
       "      <th>sobrenome_x</th>\n",
       "      <th>disciplina_id_y</th>\n",
       "      <th>nome_y</th>\n",
       "      <th>sobrenome_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Bernardo</td>\n",
       "      <td>Anderson</td>\n",
       "      <td>4</td>\n",
       "      <td>Roberto</td>\n",
       "      <td>Sampaio</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Alan</td>\n",
       "      <td>Teixeira</td>\n",
       "      <td>5</td>\n",
       "      <td>Mariana</td>\n",
       "      <td>Fernandes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Mateus</td>\n",
       "      <td>Amoedo</td>\n",
       "      <td>6</td>\n",
       "      <td>Ana</td>\n",
       "      <td>Arantes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Ivo</td>\n",
       "      <td>Trindade</td>\n",
       "      <td>7</td>\n",
       "      <td>Marcos</td>\n",
       "      <td>Menezes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Gerson</td>\n",
       "      <td>Vargas</td>\n",
       "      <td>8</td>\n",
       "      <td>Maria</td>\n",
       "      <td>Martins</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  disciplina_id_x    nome_x sobrenome_x disciplina_id_y   nome_y sobrenome_y\n",
       "0               1  Bernardo    Anderson               4  Roberto     Sampaio\n",
       "1               2      Alan    Teixeira               5  Mariana   Fernandes\n",
       "2               3    Mateus      Amoedo               6      Ana     Arantes\n",
       "3               4       Ivo    Trindade               7   Marcos     Menezes\n",
       "4               5    Gerson      Vargas               8    Maria     Martins"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Join baseado em índices\n",
    "pd.merge(df_a, df_b, right_index = True, left_index = True)"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}