Notebooks/Aula01/Exercicio_Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ffcb22fd",
   "metadata": {},
   "source": [
    "# Exercícios"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1b7bff2",
   "metadata": {},
   "source": [
    "## Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "id": "2040a736",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d0f5db4",
   "metadata": {},
   "source": [
    "## Arquivo ~~Exercicio_Pandas_1.csv~~ german_credit_data.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04ef54a5",
   "metadata": {},
   "source": [
    "1. Carregue um arquivo CSV contendo dados de uma planilha e crie um DataFrame com o Pandas.\n",
    "\n",
    "(*) datasource alterado para conseguir implementar consultas mais complexas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "id": "1b32d9f6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1000, 9)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Age</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Job</th>\n",
       "      <th>Housing</th>\n",
       "      <th>Saving accounts</th>\n",
       "      <th>Checking account</th>\n",
       "      <th>Credit amount</th>\n",
       "      <th>Duration</th>\n",
       "      <th>Purpose</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>67</td>\n",
       "      <td>male</td>\n",
       "      <td>2</td>\n",
       "      <td>own</td>\n",
       "      <td>NaN</td>\n",
       "      <td>little</td>\n",
       "      <td>1169</td>\n",
       "      <td>6</td>\n",
       "      <td>radio/TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>22</td>\n",
       "      <td>female</td>\n",
       "      <td>2</td>\n",
       "      <td>own</td>\n",
       "      <td>little</td>\n",
       "      <td>moderate</td>\n",
       "      <td>5951</td>\n",
       "      <td>48</td>\n",
       "      <td>radio/TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>49</td>\n",
       "      <td>male</td>\n",
       "      <td>1</td>\n",
       "      <td>own</td>\n",
       "      <td>little</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2096</td>\n",
       "      <td>12</td>\n",
       "      <td>education</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>45</td>\n",
       "      <td>male</td>\n",
       "      <td>2</td>\n",
       "      <td>free</td>\n",
       "      <td>little</td>\n",
       "      <td>little</td>\n",
       "      <td>7882</td>\n",
       "      <td>42</td>\n",
       "      <td>furniture/equipment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>53</td>\n",
       "      <td>male</td>\n",
       "      <td>2</td>\n",
       "      <td>free</td>\n",
       "      <td>little</td>\n",
       "      <td>little</td>\n",
       "      <td>4870</td>\n",
       "      <td>24</td>\n",
       "      <td>car</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Age     Sex  Job Housing Saving accounts Checking account  Credit amount  \\\n",
       "0   67    male    2     own             NaN           little           1169   \n",
       "1   22  female    2     own          little         moderate           5951   \n",
       "2   49    male    1     own          little              NaN           2096   \n",
       "3   45    male    2    free          little           little           7882   \n",
       "4   53    male    2    free          little           little           4870   \n",
       "\n",
       "   Duration              Purpose  \n",
       "0         6             radio/TV  \n",
       "1        48             radio/TV  \n",
       "2        12            education  \n",
       "3        42  furniture/equipment  \n",
       "4        24                  car  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Load datasource\n",
    "data = pd.read_csv('./../../Datasets/german_credit_data.csv')\n",
    "\n",
    "# Exluir primeira coluna (index duplicado)\n",
    "data.drop(data.columns[0], axis=1, inplace=True)\n",
    "\n",
    "display(data.shape, data.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c385e9ab",
   "metadata": {},
   "source": [
    "2. Exiba as primeiras linhas do DataFrame para verificar os dados que foram carregados."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "id": "3264c400",
   "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>Age</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Job</th>\n",
       "      <th>Housing</th>\n",
       "      <th>Saving accounts</th>\n",
       "      <th>Checking account</th>\n",
       "      <th>Credit amount</th>\n",
       "      <th>Duration</th>\n",
       "      <th>Purpose</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>67</td>\n",
       "      <td>male</td>\n",
       "      <td>2</td>\n",
       "      <td>own</td>\n",
       "      <td>NaN</td>\n",
       "      <td>little</td>\n",
       "      <td>1169</td>\n",
       "      <td>6</td>\n",
       "      <td>radio/TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>22</td>\n",
       "      <td>female</td>\n",
       "      <td>2</td>\n",
       "      <td>own</td>\n",
       "      <td>little</td>\n",
       "      <td>moderate</td>\n",
       "      <td>5951</td>\n",
       "      <td>48</td>\n",
       "      <td>radio/TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>49</td>\n",
       "      <td>male</td>\n",
       "      <td>1</td>\n",
       "      <td>own</td>\n",
       "      <td>little</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2096</td>\n",
       "      <td>12</td>\n",
       "      <td>education</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>45</td>\n",
       "      <td>male</td>\n",
       "      <td>2</td>\n",
       "      <td>free</td>\n",
       "      <td>little</td>\n",
       "      <td>little</td>\n",
       "      <td>7882</td>\n",
       "      <td>42</td>\n",
       "      <td>furniture/equipment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>53</td>\n",
       "      <td>male</td>\n",
       "      <td>2</td>\n",
       "      <td>free</td>\n",
       "      <td>little</td>\n",
       "      <td>little</td>\n",
       "      <td>4870</td>\n",
       "      <td>24</td>\n",
       "      <td>car</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Age     Sex  Job Housing Saving accounts Checking account  Credit amount  \\\n",
       "0   67    male    2     own             NaN           little           1169   \n",
       "1   22  female    2     own          little         moderate           5951   \n",
       "2   49    male    1     own          little              NaN           2096   \n",
       "3   45    male    2    free          little           little           7882   \n",
       "4   53    male    2    free          little           little           4870   \n",
       "\n",
       "   Duration              Purpose  \n",
       "0         6             radio/TV  \n",
       "1        48             radio/TV  \n",
       "2        12            education  \n",
       "3        42  furniture/equipment  \n",
       "4        24                  car  "
      ]
     },
     "execution_count": 146,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1e3af38",
   "metadata": {},
   "source": [
    "3. Verifique o número de linhas e colunas presentes no DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "id": "cdad2a03",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'1000 linhas, 9 colunas'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(f\"{data.shape[0]} linhas, {data.shape[1]} colunas\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c18c48f6",
   "metadata": {},
   "source": [
    "4. Utilize o método `info()` para obter informações sobre os tipos de dados e a contagem de valores não nulos em cada coluna."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "id": "1167d917",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1000 entries, 0 to 999\n",
      "Data columns (total 9 columns):\n",
      " #   Column            Non-Null Count  Dtype \n",
      "---  ------            --------------  ----- \n",
      " 0   Age               1000 non-null   int64 \n",
      " 1   Sex               1000 non-null   object\n",
      " 2   Job               1000 non-null   int64 \n",
      " 3   Housing           1000 non-null   object\n",
      " 4   Saving accounts   817 non-null    object\n",
      " 5   Checking account  606 non-null    object\n",
      " 6   Credit amount     1000 non-null   int64 \n",
      " 7   Duration          1000 non-null   int64 \n",
      " 8   Purpose           1000 non-null   object\n",
      "dtypes: int64(4), object(5)\n",
      "memory usage: 70.4+ KB\n"
     ]
    }
   ],
   "source": [
    "data.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54063436",
   "metadata": {},
   "source": [
    "5. Selecione uma coluna específica do DataFrame e exiba suas estatísticas básicas usando o método `describe()`.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "id": "1374c196",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count    1000.000000\n",
       "mean       35.546000\n",
       "std        11.375469\n",
       "min        19.000000\n",
       "25%        27.000000\n",
       "50%        33.000000\n",
       "75%        42.000000\n",
       "max        75.000000\n",
       "Name: Age, dtype: float64"
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Obtendo a descrição da coluna Age\n",
    "data['Age'].describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aa6cd4af",
   "metadata": {},
   "source": [
    "6. Faça uma filtragem no DataFrame para exibir apenas as linhas que atendam a determinada condição.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "id": "7365bac5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Registros cuja idade está entre o Q1 (27 anos) e o Q3 (42 anos)'"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Age</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Job</th>\n",
       "      <th>Housing</th>\n",
       "      <th>Saving accounts</th>\n",
       "      <th>Checking account</th>\n",
       "      <th>Credit amount</th>\n",
       "      <th>Duration</th>\n",
       "      <th>Purpose</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>35</td>\n",
       "      <td>male</td>\n",
       "      <td>1</td>\n",
       "      <td>free</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9055</td>\n",
       "      <td>36</td>\n",
       "      <td>education</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>35</td>\n",
       "      <td>male</td>\n",
       "      <td>3</td>\n",
       "      <td>rent</td>\n",
       "      <td>little</td>\n",
       "      <td>moderate</td>\n",
       "      <td>6948</td>\n",
       "      <td>36</td>\n",
       "      <td>car</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>28</td>\n",
       "      <td>male</td>\n",
       "      <td>3</td>\n",
       "      <td>own</td>\n",
       "      <td>little</td>\n",
       "      <td>moderate</td>\n",
       "      <td>5234</td>\n",
       "      <td>30</td>\n",
       "      <td>car</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>28</td>\n",
       "      <td>female</td>\n",
       "      <td>2</td>\n",
       "      <td>rent</td>\n",
       "      <td>little</td>\n",
       "      <td>little</td>\n",
       "      <td>1403</td>\n",
       "      <td>15</td>\n",
       "      <td>car</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>32</td>\n",
       "      <td>female</td>\n",
       "      <td>1</td>\n",
       "      <td>own</td>\n",
       "      <td>moderate</td>\n",
       "      <td>little</td>\n",
       "      <td>1282</td>\n",
       "      <td>24</td>\n",
       "      <td>radio/TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>993</th>\n",
       "      <td>30</td>\n",
       "      <td>male</td>\n",
       "      <td>3</td>\n",
       "      <td>own</td>\n",
       "      <td>little</td>\n",
       "      <td>little</td>\n",
       "      <td>3959</td>\n",
       "      <td>36</td>\n",
       "      <td>furniture/equipment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>31</td>\n",
       "      <td>female</td>\n",
       "      <td>1</td>\n",
       "      <td>own</td>\n",
       "      <td>little</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1736</td>\n",
       "      <td>12</td>\n",
       "      <td>furniture/equipment</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>40</td>\n",
       "      <td>male</td>\n",
       "      <td>3</td>\n",
       "      <td>own</td>\n",
       "      <td>little</td>\n",
       "      <td>little</td>\n",
       "      <td>3857</td>\n",
       "      <td>30</td>\n",
       "      <td>car</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>38</td>\n",
       "      <td>male</td>\n",
       "      <td>2</td>\n",
       "      <td>own</td>\n",
       "      <td>little</td>\n",
       "      <td>NaN</td>\n",
       "      <td>804</td>\n",
       "      <td>12</td>\n",
       "      <td>radio/TV</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>27</td>\n",
       "      <td>male</td>\n",
       "      <td>2</td>\n",
       "      <td>own</td>\n",
       "      <td>moderate</td>\n",
       "      <td>moderate</td>\n",
       "      <td>4576</td>\n",
       "      <td>45</td>\n",
       "      <td>car</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>525 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Age     Sex  Job Housing Saving accounts Checking account  Credit amount  \\\n",
       "5     35    male    1    free             NaN              NaN           9055   \n",
       "7     35    male    3    rent          little         moderate           6948   \n",
       "9     28    male    3     own          little         moderate           5234   \n",
       "14    28  female    2    rent          little           little           1403   \n",
       "15    32  female    1     own        moderate           little           1282   \n",
       "..   ...     ...  ...     ...             ...              ...            ...   \n",
       "993   30    male    3     own          little           little           3959   \n",
       "995   31  female    1     own          little              NaN           1736   \n",
       "996   40    male    3     own          little           little           3857   \n",
       "997   38    male    2     own          little              NaN            804   \n",
       "999   27    male    2     own        moderate         moderate           4576   \n",
       "\n",
       "     Duration              Purpose  \n",
       "5          36            education  \n",
       "7          36                  car  \n",
       "9          30                  car  \n",
       "14         15                  car  \n",
       "15         24             radio/TV  \n",
       "..        ...                  ...  \n",
       "993        36  furniture/equipment  \n",
       "995        12  furniture/equipment  \n",
       "996        30                  car  \n",
       "997        12             radio/TV  \n",
       "999        45                  car  \n",
       "\n",
       "[525 rows x 9 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Filtrando os registros cuja idade está entre o primeiro e o terceito quartis (IIQ)\n",
    "\n",
    "# Extraindo os quartiles Q1 e Q3\n",
    "quantiles = data['Age'].quantile([.25, .75]).values\n",
    "\n",
    "# Montando o filtro\n",
    "filtro = (data.Age >= quantiles[0]) & (data.Age <= quantiles[1]) \n",
    "\n",
    "display(f\"Registros cuja idade está entre o Q1 ({int(quantiles[0])} anos) e o Q3 ({int(quantiles[1])} anos)\", \\\n",
    "    data[filtro])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e42b06d",
   "metadata": {},
   "source": [
    "7. Crie uma nova coluna no DataFrame, calculando-a com base em outras colunas existentes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "id": "285f0834",
   "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>Age</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Job</th>\n",
       "      <th>Housing</th>\n",
       "      <th>Saving accounts</th>\n",
       "      <th>Checking account</th>\n",
       "      <th>Credit amount</th>\n",
       "      <th>Duration</th>\n",
       "      <th>Purpose</th>\n",
       "      <th>installment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>23</td>\n",
       "      <td>female</td>\n",
       "      <td>2</td>\n",
       "      <td>own</td>\n",
       "      <td>little</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2288</td>\n",
       "      <td>21</td>\n",
       "      <td>furniture/equipment</td>\n",
       "      <td>108.952381</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>267</th>\n",
       "      <td>38</td>\n",
       "      <td>female</td>\n",
       "      <td>2</td>\n",
       "      <td>own</td>\n",
       "      <td>little</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1533</td>\n",
       "      <td>24</td>\n",
       "      <td>radio/TV</td>\n",
       "      <td>63.875000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>30</td>\n",
       "      <td>male</td>\n",
       "      <td>3</td>\n",
       "      <td>own</td>\n",
       "      <td>quite rich</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2333</td>\n",
       "      <td>30</td>\n",
       "      <td>radio/TV</td>\n",
       "      <td>77.766667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>684</th>\n",
       "      <td>31</td>\n",
       "      <td>male</td>\n",
       "      <td>1</td>\n",
       "      <td>own</td>\n",
       "      <td>moderate</td>\n",
       "      <td>moderate</td>\n",
       "      <td>9857</td>\n",
       "      <td>36</td>\n",
       "      <td>business</td>\n",
       "      <td>273.805556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>540</th>\n",
       "      <td>23</td>\n",
       "      <td>male</td>\n",
       "      <td>2</td>\n",
       "      <td>rent</td>\n",
       "      <td>little</td>\n",
       "      <td>moderate</td>\n",
       "      <td>1534</td>\n",
       "      <td>12</td>\n",
       "      <td>radio/TV</td>\n",
       "      <td>127.833333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>805</th>\n",
       "      <td>24</td>\n",
       "      <td>male</td>\n",
       "      <td>2</td>\n",
       "      <td>own</td>\n",
       "      <td>little</td>\n",
       "      <td>little</td>\n",
       "      <td>9271</td>\n",
       "      <td>36</td>\n",
       "      <td>car</td>\n",
       "      <td>257.527778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>819</th>\n",
       "      <td>26</td>\n",
       "      <td>male</td>\n",
       "      <td>2</td>\n",
       "      <td>own</td>\n",
       "      <td>little</td>\n",
       "      <td>little</td>\n",
       "      <td>1345</td>\n",
       "      <td>18</td>\n",
       "      <td>radio/TV</td>\n",
       "      <td>74.722222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>37</td>\n",
       "      <td>male</td>\n",
       "      <td>2</td>\n",
       "      <td>own</td>\n",
       "      <td>little</td>\n",
       "      <td>rich</td>\n",
       "      <td>1225</td>\n",
       "      <td>10</td>\n",
       "      <td>domestic appliances</td>\n",
       "      <td>122.500000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>527</th>\n",
       "      <td>42</td>\n",
       "      <td>male</td>\n",
       "      <td>1</td>\n",
       "      <td>own</td>\n",
       "      <td>little</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1503</td>\n",
       "      <td>4</td>\n",
       "      <td>radio/TV</td>\n",
       "      <td>375.750000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>541</th>\n",
       "      <td>60</td>\n",
       "      <td>male</td>\n",
       "      <td>2</td>\n",
       "      <td>free</td>\n",
       "      <td>little</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2032</td>\n",
       "      <td>24</td>\n",
       "      <td>car</td>\n",
       "      <td>84.666667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Age     Sex  Job Housing Saving accounts Checking account  Credit amount  \\\n",
       "144   23  female    2     own          little              NaN           2288   \n",
       "267   38  female    2     own          little              NaN           1533   \n",
       "40    30    male    3     own      quite rich              NaN           2333   \n",
       "684   31    male    1     own        moderate         moderate           9857   \n",
       "540   23    male    2    rent          little         moderate           1534   \n",
       "805   24    male    2     own          little           little           9271   \n",
       "819   26    male    2     own          little           little           1345   \n",
       "38    37    male    2     own          little             rich           1225   \n",
       "527   42    male    1     own          little              NaN           1503   \n",
       "541   60    male    2    free          little              NaN           2032   \n",
       "\n",
       "     Duration              Purpose  installment  \n",
       "144        21  furniture/equipment   108.952381  \n",
       "267        24             radio/TV    63.875000  \n",
       "40         30             radio/TV    77.766667  \n",
       "684        36             business   273.805556  \n",
       "540        12             radio/TV   127.833333  \n",
       "805        36                  car   257.527778  \n",
       "819        18             radio/TV    74.722222  \n",
       "38         10  domestic appliances   122.500000  \n",
       "527         4             radio/TV   375.750000  \n",
       "541        24                  car    84.666667  "
      ]
     },
     "execution_count": 151,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "'''\n",
    "Criação da coluna 'prestação' (installment) com o resultado do valor do empréstimo (Credit amount) \n",
    "dividido pelo número de parcelas (Duration)\n",
    "'''\n",
    "\n",
    "data['installment'] = data['Credit amount']/data['Duration']\n",
    "data.sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc73e986",
   "metadata": {},
   "source": [
    "8. Agrupe o DataFrame com base em uma coluna específica e realize uma operação de agregação, como soma, média ou contagem."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "id": "b46de10b",
   "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>mean</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Housing</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>free</th>\n",
       "      <td>4906.212963</td>\n",
       "      <td>529871</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>own</th>\n",
       "      <td>3060.939691</td>\n",
       "      <td>2182450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rent</th>\n",
       "      <td>3122.553073</td>\n",
       "      <td>558937</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                mean      sum\n",
       "Housing                      \n",
       "free     4906.212963   529871\n",
       "own      3060.939691  2182450\n",
       "rent     3122.553073   558937"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.groupby('Housing')['Credit amount'].agg(['mean', 'sum'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3ed6fac7",
   "metadata": {},
   "source": [
    "9.  Utilize a função `apply()` para aplicar uma função personalizada a uma coluna ou linha do DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "id": "8a973149",
   "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>Age</th>\n",
       "      <th>Duration</th>\n",
       "      <th>Age_Finish_Pay</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>838</th>\n",
       "      <td>63</td>\n",
       "      <td>24</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>395</th>\n",
       "      <td>32</td>\n",
       "      <td>39</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>281</th>\n",
       "      <td>50</td>\n",
       "      <td>12</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>433</th>\n",
       "      <td>33</td>\n",
       "      <td>24</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>823</th>\n",
       "      <td>47</td>\n",
       "      <td>8</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>808</th>\n",
       "      <td>55</td>\n",
       "      <td>42</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>263</th>\n",
       "      <td>57</td>\n",
       "      <td>12</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>67</td>\n",
       "      <td>6</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>390</th>\n",
       "      <td>30</td>\n",
       "      <td>18</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>298</th>\n",
       "      <td>43</td>\n",
       "      <td>18</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Age  Duration  Age_Finish_Pay\n",
       "838   63        24              65\n",
       "395   32        39              35\n",
       "281   50        12              51\n",
       "433   33        24              35\n",
       "823   47         8              47\n",
       "808   55        42              58\n",
       "263   57        12              58\n",
       "0     67         6              67\n",
       "390   30        18              31\n",
       "298   43        18              44"
      ]
     },
     "execution_count": 153,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Cálculo da idade ao finalizar a duração do empréstimo\n",
    "\n",
    "def calc_fim_emprestimo(idade_, duracao_):\n",
    "    return int(idade_ + (duracao_/12))\n",
    "\n",
    "assert calc_fim_emprestimo(20,18) == 21, \"Resultado deve ser 21\"\n",
    "\n",
    "data['Age_Finish_Pay'] = data.apply(lambda row: calc_fim_emprestimo(row['Age'], row['Duration']),axis=1)\n",
    "\n",
    "data[['Age', 'Duration', 'Age_Finish_Pay']].sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f86fa7a",
   "metadata": {},
   "source": [
    "10. Faça uma ordenação dos dados em ordem crescente ou decrescente com base em uma coluna específica."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "id": "39bd9d1b",
   "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>Age</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Job</th>\n",
       "      <th>Housing</th>\n",
       "      <th>Saving accounts</th>\n",
       "      <th>Checking account</th>\n",
       "      <th>Credit amount</th>\n",
       "      <th>Duration</th>\n",
       "      <th>Purpose</th>\n",
       "      <th>installment</th>\n",
       "      <th>Age_Finish_Pay</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>25</td>\n",
       "      <td>female</td>\n",
       "      <td>2</td>\n",
       "      <td>rent</td>\n",
       "      <td>little</td>\n",
       "      <td>moderate</td>\n",
       "      <td>1295</td>\n",
       "      <td>12</td>\n",
       "      <td>car</td>\n",
       "      <td>107.916667</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>24</td>\n",
       "      <td>female</td>\n",
       "      <td>2</td>\n",
       "      <td>rent</td>\n",
       "      <td>little</td>\n",
       "      <td>little</td>\n",
       "      <td>4308</td>\n",
       "      <td>48</td>\n",
       "      <td>business</td>\n",
       "      <td>89.750000</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>28</td>\n",
       "      <td>female</td>\n",
       "      <td>2</td>\n",
       "      <td>rent</td>\n",
       "      <td>little</td>\n",
       "      <td>little</td>\n",
       "      <td>1403</td>\n",
       "      <td>15</td>\n",
       "      <td>car</td>\n",
       "      <td>93.533333</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>42</td>\n",
       "      <td>female</td>\n",
       "      <td>2</td>\n",
       "      <td>rent</td>\n",
       "      <td>rich</td>\n",
       "      <td>rich</td>\n",
       "      <td>409</td>\n",
       "      <td>12</td>\n",
       "      <td>radio/TV</td>\n",
       "      <td>34.083333</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>23</td>\n",
       "      <td>female</td>\n",
       "      <td>0</td>\n",
       "      <td>rent</td>\n",
       "      <td>quite rich</td>\n",
       "      <td>little</td>\n",
       "      <td>1352</td>\n",
       "      <td>6</td>\n",
       "      <td>car</td>\n",
       "      <td>225.333333</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>23</td>\n",
       "      <td>female</td>\n",
       "      <td>1</td>\n",
       "      <td>rent</td>\n",
       "      <td>little</td>\n",
       "      <td>little</td>\n",
       "      <td>6229</td>\n",
       "      <td>36</td>\n",
       "      <td>furniture/equipment</td>\n",
       "      <td>173.027778</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>24</td>\n",
       "      <td>female</td>\n",
       "      <td>1</td>\n",
       "      <td>rent</td>\n",
       "      <td>moderate</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1568</td>\n",
       "      <td>18</td>\n",
       "      <td>business</td>\n",
       "      <td>87.111111</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>111</th>\n",
       "      <td>23</td>\n",
       "      <td>female</td>\n",
       "      <td>2</td>\n",
       "      <td>rent</td>\n",
       "      <td>little</td>\n",
       "      <td>rich</td>\n",
       "      <td>392</td>\n",
       "      <td>15</td>\n",
       "      <td>education</td>\n",
       "      <td>26.133333</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>117</th>\n",
       "      <td>27</td>\n",
       "      <td>female</td>\n",
       "      <td>2</td>\n",
       "      <td>rent</td>\n",
       "      <td>NaN</td>\n",
       "      <td>little</td>\n",
       "      <td>2132</td>\n",
       "      <td>10</td>\n",
       "      <td>furniture/equipment</td>\n",
       "      <td>213.200000</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>121</th>\n",
       "      <td>41</td>\n",
       "      <td>female</td>\n",
       "      <td>3</td>\n",
       "      <td>rent</td>\n",
       "      <td>little</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3868</td>\n",
       "      <td>24</td>\n",
       "      <td>car</td>\n",
       "      <td>161.166667</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Age     Sex  Job Housing Saving accounts Checking account  Credit amount  \\\n",
       "10    25  female    2    rent          little         moderate           1295   \n",
       "11    24  female    2    rent          little           little           4308   \n",
       "14    28  female    2    rent          little           little           1403   \n",
       "27    42  female    2    rent            rich             rich            409   \n",
       "47    23  female    0    rent      quite rich           little           1352   \n",
       "59    23  female    1    rent          little           little           6229   \n",
       "82    24  female    1    rent        moderate              NaN           1568   \n",
       "111   23  female    2    rent          little             rich            392   \n",
       "117   27  female    2    rent             NaN           little           2132   \n",
       "121   41  female    3    rent          little              NaN           3868   \n",
       "\n",
       "     Duration              Purpose  installment  Age_Finish_Pay  \n",
       "10         12                  car   107.916667              26  \n",
       "11         48             business    89.750000              28  \n",
       "14         15                  car    93.533333              29  \n",
       "27         12             radio/TV    34.083333              43  \n",
       "47          6                  car   225.333333              23  \n",
       "59         36  furniture/equipment   173.027778              26  \n",
       "82         18             business    87.111111              25  \n",
       "111        15            education    26.133333              24  \n",
       "117        10  furniture/equipment   213.200000              27  \n",
       "121        24                  car   161.166667              43  "
      ]
     },
     "execution_count": 154,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Ordenando por housing (descendente) e por sex (ascendente)\n",
    "data.sort_values(by=['Housing', 'Sex'], ascending=[False, True]).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "978e35a3",
   "metadata": {},
   "source": [
    "11. Utilize o recurso de indexação booleana para filtrar o DataFrame com base em várias condições."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "id": "6b7210e3",
   "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>Age</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Job</th>\n",
       "      <th>Housing</th>\n",
       "      <th>Saving accounts</th>\n",
       "      <th>Checking account</th>\n",
       "      <th>Credit amount</th>\n",
       "      <th>Duration</th>\n",
       "      <th>Purpose</th>\n",
       "      <th>installment</th>\n",
       "      <th>Age_Finish_Pay</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>25</td>\n",
       "      <td>female</td>\n",
       "      <td>2</td>\n",
       "      <td>rent</td>\n",
       "      <td>little</td>\n",
       "      <td>moderate</td>\n",
       "      <td>1295</td>\n",
       "      <td>12</td>\n",
       "      <td>car</td>\n",
       "      <td>107.916667</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>24</td>\n",
       "      <td>female</td>\n",
       "      <td>2</td>\n",
       "      <td>rent</td>\n",
       "      <td>little</td>\n",
       "      <td>little</td>\n",
       "      <td>4308</td>\n",
       "      <td>48</td>\n",
       "      <td>business</td>\n",
       "      <td>89.750000</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>28</td>\n",
       "      <td>female</td>\n",
       "      <td>2</td>\n",
       "      <td>rent</td>\n",
       "      <td>little</td>\n",
       "      <td>little</td>\n",
       "      <td>1403</td>\n",
       "      <td>15</td>\n",
       "      <td>car</td>\n",
       "      <td>93.533333</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>42</td>\n",
       "      <td>female</td>\n",
       "      <td>2</td>\n",
       "      <td>rent</td>\n",
       "      <td>rich</td>\n",
       "      <td>rich</td>\n",
       "      <td>409</td>\n",
       "      <td>12</td>\n",
       "      <td>radio/TV</td>\n",
       "      <td>34.083333</td>\n",
       "      <td>43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>23</td>\n",
       "      <td>female</td>\n",
       "      <td>0</td>\n",
       "      <td>rent</td>\n",
       "      <td>quite rich</td>\n",
       "      <td>little</td>\n",
       "      <td>1352</td>\n",
       "      <td>6</td>\n",
       "      <td>car</td>\n",
       "      <td>225.333333</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>952</th>\n",
       "      <td>28</td>\n",
       "      <td>female</td>\n",
       "      <td>2</td>\n",
       "      <td>rent</td>\n",
       "      <td>quite rich</td>\n",
       "      <td>moderate</td>\n",
       "      <td>4113</td>\n",
       "      <td>24</td>\n",
       "      <td>car</td>\n",
       "      <td>171.375000</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>955</th>\n",
       "      <td>57</td>\n",
       "      <td>female</td>\n",
       "      <td>3</td>\n",
       "      <td>rent</td>\n",
       "      <td>rich</td>\n",
       "      <td>little</td>\n",
       "      <td>1231</td>\n",
       "      <td>24</td>\n",
       "      <td>radio/TV</td>\n",
       "      <td>51.291667</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>967</th>\n",
       "      <td>54</td>\n",
       "      <td>female</td>\n",
       "      <td>3</td>\n",
       "      <td>rent</td>\n",
       "      <td>little</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3568</td>\n",
       "      <td>15</td>\n",
       "      <td>radio/TV</td>\n",
       "      <td>237.866667</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>972</th>\n",
       "      <td>29</td>\n",
       "      <td>female</td>\n",
       "      <td>0</td>\n",
       "      <td>rent</td>\n",
       "      <td>little</td>\n",
       "      <td>little</td>\n",
       "      <td>1193</td>\n",
       "      <td>24</td>\n",
       "      <td>car</td>\n",
       "      <td>49.708333</td>\n",
       "      <td>31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>985</th>\n",
       "      <td>25</td>\n",
       "      <td>female</td>\n",
       "      <td>2</td>\n",
       "      <td>rent</td>\n",
       "      <td>little</td>\n",
       "      <td>little</td>\n",
       "      <td>1433</td>\n",
       "      <td>15</td>\n",
       "      <td>furniture/equipment</td>\n",
       "      <td>95.533333</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>95 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Age     Sex  Job Housing Saving accounts Checking account  Credit amount  \\\n",
       "10    25  female    2    rent          little         moderate           1295   \n",
       "11    24  female    2    rent          little           little           4308   \n",
       "14    28  female    2    rent          little           little           1403   \n",
       "27    42  female    2    rent            rich             rich            409   \n",
       "47    23  female    0    rent      quite rich           little           1352   \n",
       "..   ...     ...  ...     ...             ...              ...            ...   \n",
       "952   28  female    2    rent      quite rich         moderate           4113   \n",
       "955   57  female    3    rent            rich           little           1231   \n",
       "967   54  female    3    rent          little              NaN           3568   \n",
       "972   29  female    0    rent          little           little           1193   \n",
       "985   25  female    2    rent          little           little           1433   \n",
       "\n",
       "     Duration              Purpose  installment  Age_Finish_Pay  \n",
       "10         12                  car   107.916667              26  \n",
       "11         48             business    89.750000              28  \n",
       "14         15                  car    93.533333              29  \n",
       "27         12             radio/TV    34.083333              43  \n",
       "47          6                  car   225.333333              23  \n",
       "..        ...                  ...          ...             ...  \n",
       "952        24                  car   171.375000              30  \n",
       "955        24             radio/TV    51.291667              59  \n",
       "967        15             radio/TV   237.866667              55  \n",
       "972        24                  car    49.708333              31  \n",
       "985        15  furniture/equipment    95.533333              26  \n",
       "\n",
       "[95 rows x 11 columns]"
      ]
     },
     "execution_count": 155,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Definição do filtro: registros de mulheres (female) que alugam (rent)\n",
    "filtro = (data.Sex  == 'female') & (data.Housing == 'rent')\n",
    "\n",
    "# Aplicação do filtro\n",
    "data.loc[filtro, data.columns]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3eade39f",
   "metadata": {},
   "source": [
    "## Arquivo Exercicio_Pandas_2.json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "id": "f8005908",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5, 4)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Produto</th>\n",
       "      <th>Quantidade_Vendida</th>\n",
       "      <th>Valor_Unitario</th>\n",
       "      <th>Valor_Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Celular</td>\n",
       "      <td>100</td>\n",
       "      <td>1000</td>\n",
       "      <td>100000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TV</td>\n",
       "      <td>50</td>\n",
       "      <td>2000</td>\n",
       "      <td>100000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Notebook</td>\n",
       "      <td>70</td>\n",
       "      <td>2500</td>\n",
       "      <td>175000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Tablet</td>\n",
       "      <td>30</td>\n",
       "      <td>800</td>\n",
       "      <td>24000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Headphone</td>\n",
       "      <td>80</td>\n",
       "      <td>150</td>\n",
       "      <td>12000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     Produto  Quantidade_Vendida  Valor_Unitario  Valor_Total\n",
       "0    Celular                 100            1000       100000\n",
       "1         TV                  50            2000       100000\n",
       "2   Notebook                  70            2500       175000\n",
       "3     Tablet                  30             800        24000\n",
       "4  Headphone                  80             150        12000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Load datasource json\n",
    "produtos1 = pd.read_json('./../../Datasets/Exercicio_Pandas_2.json')\n",
    "display(produtos1.shape, produtos1.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "52e5370f",
   "metadata": {},
   "source": [
    "2.  Utilize técnicas de indexação avançada para selecionar, filtrar e modificar dados em um DataFrame."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "id": "12c3ae6b",
   "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>Produto</th>\n",
       "      <th>Quantidade_Vendida</th>\n",
       "      <th>Valor_Unitario</th>\n",
       "      <th>Valor_Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TV</td>\n",
       "      <td>50</td>\n",
       "      <td>2000</td>\n",
       "      <td>100000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Notebook</td>\n",
       "      <td>70</td>\n",
       "      <td>2500</td>\n",
       "      <td>175000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Produto  Quantidade_Vendida  Valor_Unitario  Valor_Total\n",
       "1        TV                  50            2000       100000\n",
       "2  Notebook                  70            2500       175000"
      ]
     },
     "execution_count": 157,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Produtos com valor unitário maior que a média\n",
    "produtos1[produtos1.Valor_Unitario>produtos1.Valor_Unitario.mean()]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eabaccf0",
   "metadata": {},
   "source": [
    "## Arquivos Exercicio_Pandas_2.json e Exercicio_Pandas_3.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d2e55a04",
   "metadata": {},
   "source": [
    "1.  Combine dois DataFrames usando operações de junção (merge) ou concatenação."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "id": "51d69189",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2, 4)"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Produto</th>\n",
       "      <th>Quantidade_Vendida</th>\n",
       "      <th>Valor_Unitario</th>\n",
       "      <th>Valor_Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Mouse</td>\n",
       "      <td>60</td>\n",
       "      <td>50</td>\n",
       "      <td>3000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Teclado</td>\n",
       "      <td>40</td>\n",
       "      <td>100</td>\n",
       "      <td>4000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Produto  Quantidade_Vendida  Valor_Unitario  Valor_Total\n",
       "0    Mouse                  60              50         3000\n",
       "1  Teclado                  40             100         4000"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Load de arquivo deparado por tabulações (tsv)\n",
    "produtos2 = pd.read_csv('./../../Datasets/Exercicio_Pandas_3.csv', sep='\\t')\n",
    "\n",
    "display(produtos2.shape, produtos2.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "id": "dd731922",
   "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>old_index</th>\n",
       "      <th>Produto</th>\n",
       "      <th>Quantidade_Vendida</th>\n",
       "      <th>Valor_Unitario</th>\n",
       "      <th>Valor_Total</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Celular</td>\n",
       "      <td>100</td>\n",
       "      <td>1000</td>\n",
       "      <td>100000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>TV</td>\n",
       "      <td>50</td>\n",
       "      <td>2000</td>\n",
       "      <td>100000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Notebook</td>\n",
       "      <td>70</td>\n",
       "      <td>2500</td>\n",
       "      <td>175000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Tablet</td>\n",
       "      <td>30</td>\n",
       "      <td>800</td>\n",
       "      <td>24000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Headphone</td>\n",
       "      <td>80</td>\n",
       "      <td>150</td>\n",
       "      <td>12000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0</td>\n",
       "      <td>Mouse</td>\n",
       "      <td>60</td>\n",
       "      <td>50</td>\n",
       "      <td>3000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>Teclado</td>\n",
       "      <td>40</td>\n",
       "      <td>100</td>\n",
       "      <td>4000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   old_index    Produto  Quantidade_Vendida  Valor_Unitario  Valor_Total\n",
       "0          0    Celular                 100            1000       100000\n",
       "1          1         TV                  50            2000       100000\n",
       "2          2   Notebook                  70            2500       175000\n",
       "3          3     Tablet                  30             800        24000\n",
       "4          4  Headphone                  80             150        12000\n",
       "5          0      Mouse                  60              50         3000\n",
       "6          1    Teclado                  40             100         4000"
      ]
     },
     "execution_count": 159,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Concatendando 2 dataframes com o mesmo layout e resetando o índice para evitar duplicidades\n",
    "produtos_full = pd.concat([produtos1, produtos2]).reset_index()\n",
    "\n",
    "# Renomeando o antigo índice para não perder rastrabilidade\n",
    "produtos_full.rename(columns={'index': 'old_index'}, inplace=True)\n",
    "\n",
    "produtos_full"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "id": "f8299b01",
   "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>old_index</th>\n",
       "      <th>Produto</th>\n",
       "      <th>Quantidade_Vendida</th>\n",
       "      <th>Valor_Unitario</th>\n",
       "      <th>Valor_Total</th>\n",
       "      <th>categ_vendas</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>Celular</td>\n",
       "      <td>100</td>\n",
       "      <td>1000</td>\n",
       "      <td>100000</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>TV</td>\n",
       "      <td>50</td>\n",
       "      <td>2000</td>\n",
       "      <td>100000</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>Notebook</td>\n",
       "      <td>70</td>\n",
       "      <td>2500</td>\n",
       "      <td>175000</td>\n",
       "      <td>med</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Tablet</td>\n",
       "      <td>30</td>\n",
       "      <td>800</td>\n",
       "      <td>24000</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Headphone</td>\n",
       "      <td>80</td>\n",
       "      <td>150</td>\n",
       "      <td>12000</td>\n",
       "      <td>high</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0</td>\n",
       "      <td>Mouse</td>\n",
       "      <td>60</td>\n",
       "      <td>50</td>\n",
       "      <td>3000</td>\n",
       "      <td>med</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1</td>\n",
       "      <td>Teclado</td>\n",
       "      <td>40</td>\n",
       "      <td>100</td>\n",
       "      <td>4000</td>\n",
       "      <td>low</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   old_index    Produto  Quantidade_Vendida  Valor_Unitario  Valor_Total  \\\n",
       "0          0    Celular                 100            1000       100000   \n",
       "1          1         TV                  50            2000       100000   \n",
       "2          2   Notebook                  70            2500       175000   \n",
       "3          3     Tablet                  30             800        24000   \n",
       "4          4  Headphone                  80             150        12000   \n",
       "5          0      Mouse                  60              50         3000   \n",
       "6          1    Teclado                  40             100         4000   \n",
       "\n",
       "  categ_vendas  \n",
       "0         high  \n",
       "1          low  \n",
       "2          med  \n",
       "3          low  \n",
       "4         high  \n",
       "5          med  \n",
       "6          low  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# categorizar produtos por quantidade vendida: low, med, high\n",
    "produtos_full['categ_vendas'] = pd.cut(produtos_full['Quantidade_Vendida'],bins=3, labels=('low', 'med', 'high'))\n",
    "\n",
    "display(produtos_full)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9df3b25d",
   "metadata": {},
   "source": [
    "   \n",
    "2.  Utilize o método `groupby()` para agrupar os dados por múltiplas colunas e realizar operações de agregação em grupos."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "id": "60619eeb",
   "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></th>\n",
       "      <th>sum</th>\n",
       "      <th>mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sex</th>\n",
       "      <th>Saving accounts</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">female</th>\n",
       "      <th>little</th>\n",
       "      <td>560031</td>\n",
       "      <td>2886.757732</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>moderate</th>\n",
       "      <td>96013</td>\n",
       "      <td>3000.406250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>quite rich</th>\n",
       "      <td>50649</td>\n",
       "      <td>3165.562500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rich</th>\n",
       "      <td>35849</td>\n",
       "      <td>1886.789474</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">male</th>\n",
       "      <th>little</th>\n",
       "      <td>1362232</td>\n",
       "      <td>3330.640587</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>moderate</th>\n",
       "      <td>252543</td>\n",
       "      <td>3556.943662</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>quite rich</th>\n",
       "      <td>111394</td>\n",
       "      <td>2370.085106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rich</th>\n",
       "      <td>87674</td>\n",
       "      <td>3023.241379</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            sum         mean\n",
       "Sex    Saving accounts                      \n",
       "female little            560031  2886.757732\n",
       "       moderate           96013  3000.406250\n",
       "       quite rich         50649  3165.562500\n",
       "       rich               35849  1886.789474\n",
       "male   little           1362232  3330.640587\n",
       "       moderate          252543  3556.943662\n",
       "       quite rich        111394  2370.085106\n",
       "       rich               87674  3023.241379"
      ]
     },
     "execution_count": 161,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Agrupando os valor de emrpéstimo agregado por sexo e categoria de ahorros e calculando a soma e média \n",
    "data.groupby(['Sex', 'Saving accounts'])['Credit amount'].agg(['sum', 'mean'])"
   ]
  }
 ],
 "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.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}