notebook_tarde.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "alo mundo\n"
     ]
    }
   ],
   "source": [
    "print('alo mundo')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: sqlalchemy in c:\\users\\ead\\desktop\\guido\\projetofinal_tarde\\.venv\\lib\\site-packages (2.0.31)\n",
      "Requirement already satisfied: typing-extensions>=4.6.0 in c:\\users\\ead\\desktop\\guido\\projetofinal_tarde\\.venv\\lib\\site-packages (from sqlalchemy) (4.12.2)\n",
      "Requirement already satisfied: greenlet!=0.4.17 in c:\\users\\ead\\desktop\\guido\\projetofinal_tarde\\.venv\\lib\\site-packages (from sqlalchemy) (3.0.3)\n"
     ]
    }
   ],
   "source": [
    "! pip install sqlalchemy\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Módulo\n",
    "\n",
    "from sqlalchemy import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: pymysql in c:\\users\\ead\\desktop\\guido\\projetofinal_tarde\\.venv\\lib\\site-packages (1.1.1)\n"
     ]
    }
   ],
   "source": [
    "! pip install pymysql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [],
   "source": [
    "import urllib.parse"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "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>ra</th>\n",
       "      <th>nome</th>\n",
       "      <th>tempoestudo</th>\n",
       "      <th>renda_media_mensal_familiar</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>00034547</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>00034548</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>00034549</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>00034550</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>00034551</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id        ra  nome  tempoestudo  renda_media_mensal_familiar\n",
       "0   1  00034547  None          NaN                          NaN\n",
       "1   2  00034548  None          NaN                          NaN\n",
       "2   3  00034549  None          NaN                          NaN\n",
       "3   4  00034550  None          NaN                          NaN\n",
       "4   5  00034551  None          NaN                          NaN"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Dfinir as credenciais\n",
    "user = 'root'\n",
    "password = urllib.parse.quote_plus('senai@123')\n",
    "host = 'localhost'\n",
    "database = 'schooltracker'\n",
    "\n",
    "# Criar uma url de conexão\n",
    "connection_string = f'mysql+pymysql://{user}:{password}@{host}/{database}'\n",
    "\n",
    "# criar uma conexão com o create_engine()\n",
    "engine = create_engine(connection_string)\n",
    "\n",
    "# Escrever uma consulta SQL\n",
    "query_alunos = 'SELECT * FROM aluno'\n",
    "\n",
    "# Criar o DataFrame\n",
    "df_alunos = pd.read_sql(query_alunos,engine)\n",
    "\n",
    "# Mostra os cinco primeiros\n",
    "df_alunos.head() "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Quais bibliotecas um cientista de dados utiliza\n",
    "\n",
    "Pandas\n",
    "Numpy\n",
    "Scikit-Learn (fundamentos de IA)\n",
    "\n",
    "## Material Didático\n",
    "\n",
    "Notebook (pandas, numpy)\n",
    "Apostila senai (scikit-learn)\n",
    "     \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Carregar dados"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Salvar um DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "csv_filename = 'alunos.csv'\n",
    "df_alunos.to_csv(csv_filename, index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercício -Salve o DataFrame no formato Json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "csv_filename = 'alunos.json'\n",
    "df_alunos.to_json(csv_filename, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: openpyxl in c:\\users\\ead\\desktop\\guido\\projetofinal_tarde\\.venv\\lib\\site-packages (3.1.5)\n",
      "Requirement already satisfied: et-xmlfile in c:\\users\\ead\\desktop\\guido\\projetofinal_tarde\\.venv\\lib\\site-packages (from openpyxl) (1.1.0)\n"
     ]
    }
   ],
   "source": [
    "! pip install openpyxl\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Um novo DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [],
   "source": [
    "csv_filename = 'planilha_alunos.xlsx'\n",
    "df_alunos.to_excel(csv_filename, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['id', 'ra', 'nome', 'tempoestudo', 'renda_media_mensal_familiar'], dtype='object')"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_alunos.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "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>tempoestudo</th>\n",
       "      <th>renda_media_mensal_familiar</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>100</th>\n",
       "      <td>21.0</td>\n",
       "      <td>3000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>101</th>\n",
       "      <td>17.0</td>\n",
       "      <td>3100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>25.0</td>\n",
       "      <td>3400.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>12.0</td>\n",
       "      <td>3200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>20.0</td>\n",
       "      <td>2900.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>105 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     tempoestudo  renda_media_mensal_familiar\n",
       "0            NaN                          NaN\n",
       "1            NaN                          NaN\n",
       "2            NaN                          NaN\n",
       "3            NaN                          NaN\n",
       "4            NaN                          NaN\n",
       "..           ...                          ...\n",
       "100         21.0                       3000.0\n",
       "101         17.0                       3100.0\n",
       "102         25.0                       3400.0\n",
       "103         12.0                       3200.0\n",
       "104         20.0                       2900.0\n",
       "\n",
       "[105 rows x 2 columns]"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tempo_renda = df_alunos[['tempoestudo','renda_media_mensal_familiar']]\n",
    "df_tempo_renda"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercício 2 - Crie um recurso para mostrar apenas o tipo desse recurso"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 109,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(df_tempo_renda)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 105 entries, 0 to 104\n",
      "Data columns (total 2 columns):\n",
      " #   Column                       Non-Null Count  Dtype  \n",
      "---  ------                       --------------  -----  \n",
      " 0   tempoestudo                  50 non-null     float64\n",
      " 1   renda_media_mensal_familiar  50 non-null     float64\n",
      "dtypes: float64(2)\n",
      "memory usage: 1.8 KB\n"
     ]
    }
   ],
   "source": [
    "df_tempo_renda.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(105, 2)"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tempo_renda.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0       NaN\n",
       "1       NaN\n",
       "2       NaN\n",
       "3       NaN\n",
       "4       NaN\n",
       "       ... \n",
       "100    21.0\n",
       "101    17.0\n",
       "102    25.0\n",
       "103    12.0\n",
       "104    20.0\n",
       "Name: tempoestudo, Length: 105, dtype: float64"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tempo_renda['tempoestudo']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [],
   "source": [
    "serie_tempo_renda = df_tempo_renda['tempoestudo']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.series.Series"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(serie_tempo_renda)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Revisando Funções Básicas\n",
    "\n",
    "2024/08/06\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercício 01: Retorne o DataFrame da última aula com as duas primeiras linhas como amostras."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "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>ra</th>\n",
       "      <th>nome</th>\n",
       "      <th>tempoestudo</th>\n",
       "      <th>renda_media_mensal_familiar</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>00034547</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>00034548</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id        ra  nome  tempoestudo  renda_media_mensal_familiar\n",
       "0   1  00034547  None          NaN                          NaN\n",
       "1   2  00034548  None          NaN                          NaN"
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ex1 = df_alunos.head(2)\n",
    "\n",
    "ex1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercício 02: Retorne as duas últimas linhas da amostra"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "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>ra</th>\n",
       "      <th>nome</th>\n",
       "      <th>tempoestudo</th>\n",
       "      <th>renda_media_mensal_familiar</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>104</td>\n",
       "      <td>00034648</td>\n",
       "      <td>Rogério Silva</td>\n",
       "      <td>12.0</td>\n",
       "      <td>3200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>105</td>\n",
       "      <td>00034649</td>\n",
       "      <td>Amanda Pereira</td>\n",
       "      <td>20.0</td>\n",
       "      <td>2900.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      id        ra            nome  tempoestudo  renda_media_mensal_familiar\n",
       "103  104  00034648   Rogério Silva         12.0                       3200.0\n",
       "104  105  00034649  Amanda Pereira         20.0                       2900.0"
      ]
     },
     "execution_count": 116,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ex2 = df_alunos.tail(2)\n",
    "\n",
    "ex2"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercício 03: Retorne uma tupla com o número de linhas e colunas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(105, 5)"
      ]
     },
     "execution_count": 117,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_alunos.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercício 04: Retorne as informações gerais do DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 105 entries, 0 to 104\n",
      "Data columns (total 5 columns):\n",
      " #   Column                       Non-Null Count  Dtype  \n",
      "---  ------                       --------------  -----  \n",
      " 0   id                           105 non-null    int64  \n",
      " 1   ra                           105 non-null    object \n",
      " 2   nome                         50 non-null     object \n",
      " 3   tempoestudo                  50 non-null     float64\n",
      " 4   renda_media_mensal_familiar  50 non-null     float64\n",
      "dtypes: float64(2), int64(1), object(2)\n",
      "memory usage: 4.2+ KB\n"
     ]
    }
   ],
   "source": [
    "df_alunos.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercício 05: Retorne as colunas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['id', 'ra', 'nome', 'tempoestudo', 'renda_media_mensal_familiar'], dtype='object')"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_alunos.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercício 06: Retorne as informações apenas da coluna renda famiiar"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.series.Series'>\n",
      "RangeIndex: 105 entries, 0 to 104\n",
      "Series name: renda_media_mensal_familiar\n",
      "Non-Null Count  Dtype  \n",
      "--------------  -----  \n",
      "50 non-null     float64\n",
      "dtypes: float64(1)\n",
      "memory usage: 972.0 bytes\n"
     ]
    }
   ],
   "source": [
    "df_alunos['renda_media_mensal_familiar'].info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercício 07: Mostre os tipos com dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                               int64\n",
       "ra                              object\n",
       "nome                            object\n",
       "tempoestudo                    float64\n",
       "renda_media_mensal_familiar    float64\n",
       "dtype: object"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_alunos.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercício 08: \n",
    "# Crie um dataframe da tabela diário de bordo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "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>texto</th>\n",
       "      <th>datahora</th>\n",
       "      <th>fk_aluno_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>aulatarde</td>\n",
       "      <td>2024-07-31 15:54:02</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>crieiumbd</td>\n",
       "      <td>2024-07-31 17:53:12</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id      texto            datahora  fk_aluno_id\n",
       "0   1  aulatarde 2024-07-31 15:54:02           40\n",
       "1   2  crieiumbd 2024-07-31 17:53:12            2"
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "\n",
    "# Escrever uma consulta SQL\n",
    "query_diariobordo = 'SELECT * FROM diariobordo'\n",
    "\n",
    "# Criar o DataFrame\n",
    "df_diariobordo = pd.read_sql(query_diariobordo,engine)\n",
    "\n",
    "df_diariobordo.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Retorne uma tupla com o número de linhas e colunas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2, 4)"
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_diariobordo.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Mostre os tipos com dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "id                      int64\n",
       "texto                  object\n",
       "datahora       datetime64[ns]\n",
       "fk_aluno_id             int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_diariobordo.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Retorne as informações gerais do DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2 entries, 0 to 1\n",
      "Data columns (total 4 columns):\n",
      " #   Column       Non-Null Count  Dtype         \n",
      "---  ------       --------------  -----         \n",
      " 0   id           2 non-null      int64         \n",
      " 1   texto        2 non-null      object        \n",
      " 2   datahora     2 non-null      datetime64[ns]\n",
      " 3   fk_aluno_id  2 non-null      int64         \n",
      "dtypes: datetime64[ns](1), int64(2), object(1)\n",
      "memory usage: 196.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "df_diariobordo.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Exercício 09: \n",
    "\n",
    "# Crie um dataframe da tabela avaliaçâo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [],
   "source": [
    "query_avaliacao = 'SELECT * FROM avaliacao'\n",
    "df_avaliacao = pd.read_sql(query_avaliacao,engine)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Retorne o DataFrame da última aula com as cinco primeiras linhas como amostras."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "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_pk</th>\n",
       "      <th>nota1</th>\n",
       "      <th>nota2</th>\n",
       "      <th>nota3</th>\n",
       "      <th>nota4</th>\n",
       "      <th>fk_aluno_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>20</td>\n",
       "      <td>18</td>\n",
       "      <td>23</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>21</td>\n",
       "      <td>19</td>\n",
       "      <td>24</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_pk  nota1  nota2  nota3  nota4  fk_aluno_id\n",
       "0      1     15     20     18     23           35\n",
       "1      2     12     21     19     24           36"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cincoprimeiras = df_avaliacao.head(2)\n",
    "\n",
    "cincoprimeiras"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Retorne as duas últimas linhas da amostra"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "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_pk</th>\n",
       "      <th>nota1</th>\n",
       "      <th>nota2</th>\n",
       "      <th>nota3</th>\n",
       "      <th>nota4</th>\n",
       "      <th>fk_aluno_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>16</td>\n",
       "      <td>20</td>\n",
       "      <td>21</td>\n",
       "      <td>23</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>25</td>\n",
       "      <td>19</td>\n",
       "      <td>22</td>\n",
       "      <td>25</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id_pk  nota1  nota2  nota3  nota4  fk_aluno_id\n",
       "3      4     16     20     21     23           38\n",
       "4      5     25     19     22     25           39"
      ]
     },
     "execution_count": 128,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ultimaslinhas = df_avaliacao.tail(2)\n",
    "ultimaslinhas "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Retorne as colunas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['id_pk', 'nota1', 'nota2', 'nota3', 'nota4', 'fk_aluno_id'], dtype='object')"
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_avaliacao.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Retorne as informações apenas da coluna nota3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.series.Series'>\n",
      "RangeIndex: 5 entries, 0 to 4\n",
      "Series name: nota3\n",
      "Non-Null Count  Dtype\n",
      "--------------  -----\n",
      "5 non-null      int64\n",
      "dtypes: int64(1)\n",
      "memory usage: 172.0 bytes\n"
     ]
    }
   ],
   "source": [
    "df_avaliacao['nota3'].info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Realizando um JOIN"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Passos para o merge\n",
    "- Use o comando merge\n",
    "- Crie uma variável: resultado\n",
    "- pd.merge(dataframe1, dataframe2, tipo_uniao, qual chave da direita, qual chave da esquerda)\n",
    "- Qual o tipo de união: how = 'inner'\n",
    "- inner significa queremos um join interno, o que resulta em apenas linhas que tem correspondencia em ambos os dataframes."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Crie uma junção do dataframe aluno com avaliacao. Mostre as nota do aluno e a Ra do aluno"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "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>ra</th>\n",
       "      <th>nota1</th>\n",
       "      <th>nota2</th>\n",
       "      <th>nota3</th>\n",
       "      <th>nota4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00034581</td>\n",
       "      <td>15</td>\n",
       "      <td>20</td>\n",
       "      <td>18</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00034582</td>\n",
       "      <td>12</td>\n",
       "      <td>21</td>\n",
       "      <td>19</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00034583</td>\n",
       "      <td>19</td>\n",
       "      <td>22</td>\n",
       "      <td>17</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00034584</td>\n",
       "      <td>16</td>\n",
       "      <td>20</td>\n",
       "      <td>21</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00034585</td>\n",
       "      <td>25</td>\n",
       "      <td>19</td>\n",
       "      <td>22</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         ra  nota1  nota2  nota3  nota4\n",
       "0  00034581     15     20     18     23\n",
       "1  00034582     12     21     19     24\n",
       "2  00034583     19     22     17     25\n",
       "3  00034584     16     20     21     23\n",
       "4  00034585     25     19     22     25"
      ]
     },
     "execution_count": 140,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "\n",
    "# Realizar um inner join com base na coluna 'ID'\n",
    "resultado2 = pd.merge(df_avaliacao, df_alunos, how ='left', left_on = 'fk_aluno_id', right_on = 'id')\n",
    "# Selecionar as colunas desejadas\n",
    "resultado_selecionado2 = resultado2[['ra','nota1','nota2','nota3','nota4']]\n",
    "# Mostrar o resultado\n",
    "resultado_selecionado2.head()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "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.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}