coletando_dados_de_FII.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Definindo cabeçalho da requisição"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = 'https://www.fundsexplorer.com.br/ranking'\n",
    "headers = {\n",
    "    'User-Agent': \n",
    "        'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_4) AppleWebKit/537.36'\n",
    "        ' (KHTML, like Gecko) Chrome/51.0.2704.103 Safari/537.36'\n",
    "}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "response = requests.get(url, headers=headers)\n",
    "if response.status_code == 200:\n",
    "    df = pd.read_html(response.content, encoding='utf-8')[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.sort_values('Códigodo fundo', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "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>Códigodo fundo</th>\n",
       "      <th>Setor</th>\n",
       "      <th>Preço Atual</th>\n",
       "      <th>Liquidez Diária</th>\n",
       "      <th>Dividendo</th>\n",
       "      <th>DividendYield</th>\n",
       "      <th>DY (3M)Acumulado</th>\n",
       "      <th>DY (6M)Acumulado</th>\n",
       "      <th>DY (12M)Acumulado</th>\n",
       "      <th>DY (3M)Média</th>\n",
       "      <th>...</th>\n",
       "      <th>PatrimônioLíq.</th>\n",
       "      <th>VPA</th>\n",
       "      <th>P/VPA</th>\n",
       "      <th>DYPatrimonial</th>\n",
       "      <th>VariaçãoPatrimonial</th>\n",
       "      <th>Rentab. Patr.no Período</th>\n",
       "      <th>Rentab. Patr.Acumulada</th>\n",
       "      <th>VacânciaFísica</th>\n",
       "      <th>VacânciaFinanceira</th>\n",
       "      <th>QuantidadeAtivos</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>ABCP11</td>\n",
       "      <td>Shoppings</td>\n",
       "      <td>R$ 73,97</td>\n",
       "      <td>2654.0</td>\n",
       "      <td>R$ 0,51</td>\n",
       "      <td>0,74%</td>\n",
       "      <td>1,27%</td>\n",
       "      <td>2,58%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,42%</td>\n",
       "      <td>...</td>\n",
       "      <td>R$ 1.077.525.130,29</td>\n",
       "      <td>R$ 88,29</td>\n",
       "      <td>84.0</td>\n",
       "      <td>0,44%</td>\n",
       "      <td>-0,05%</td>\n",
       "      <td>0,39%</td>\n",
       "      <td>1,48%</td>\n",
       "      <td>9,10%</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>AFCR11</td>\n",
       "      <td>Híbrido</td>\n",
       "      <td>R$ 110,84</td>\n",
       "      <td>515.0</td>\n",
       "      <td>R$ 1,55</td>\n",
       "      <td>1,40%</td>\n",
       "      <td>3,29%</td>\n",
       "      <td>5,94%</td>\n",
       "      <td>10,57%</td>\n",
       "      <td>1,10%</td>\n",
       "      <td>...</td>\n",
       "      <td>R$ 121.528.154,28</td>\n",
       "      <td>R$ 102,36</td>\n",
       "      <td>108.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>AFOF11</td>\n",
       "      <td>Títulos e Val. Mob.</td>\n",
       "      <td>R$ 106,75</td>\n",
       "      <td>5826.0</td>\n",
       "      <td>R$ 8,76</td>\n",
       "      <td>8,76%</td>\n",
       "      <td>8,76%</td>\n",
       "      <td>8,76%</td>\n",
       "      <td>16,82%</td>\n",
       "      <td>2,92%</td>\n",
       "      <td>...</td>\n",
       "      <td>R$ 39.305.966,92</td>\n",
       "      <td>R$ 107,06</td>\n",
       "      <td>100.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>AIEC11</td>\n",
       "      <td>Lajes Corporativas</td>\n",
       "      <td>R$ 80,60</td>\n",
       "      <td>4416.0</td>\n",
       "      <td>R$ 0,57</td>\n",
       "      <td>0,69%</td>\n",
       "      <td>1,97%</td>\n",
       "      <td>3,81%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,66%</td>\n",
       "      <td>...</td>\n",
       "      <td>R$ 483.257.233,98</td>\n",
       "      <td>R$ 100,16</td>\n",
       "      <td>80.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ALMI11</td>\n",
       "      <td>Lajes Corporativas</td>\n",
       "      <td>R$ 1.145,00</td>\n",
       "      <td>176.0</td>\n",
       "      <td>R$ 0,00</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>...</td>\n",
       "      <td>R$ 229.163.662,27</td>\n",
       "      <td>R$ 2.061,25</td>\n",
       "      <td>56.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>68,15%</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</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",
       "      <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>49</th>\n",
       "      <td>XPML11</td>\n",
       "      <td>Shoppings</td>\n",
       "      <td>R$ 107,75</td>\n",
       "      <td>44998.0</td>\n",
       "      <td>R$ 0,40</td>\n",
       "      <td>0,38%</td>\n",
       "      <td>0,81%</td>\n",
       "      <td>1,95%</td>\n",
       "      <td>3,56%</td>\n",
       "      <td>0,27%</td>\n",
       "      <td>...</td>\n",
       "      <td>R$ 2.001.282.055,70</td>\n",
       "      <td>R$ 109,91</td>\n",
       "      <td>98.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5,90%</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>XPPR11</td>\n",
       "      <td>Outros</td>\n",
       "      <td>R$ 68,70</td>\n",
       "      <td>14574.0</td>\n",
       "      <td>R$ 0,48</td>\n",
       "      <td>0,73%</td>\n",
       "      <td>2,19%</td>\n",
       "      <td>4,17%</td>\n",
       "      <td>8,88%</td>\n",
       "      <td>0,73%</td>\n",
       "      <td>...</td>\n",
       "      <td>R$ 627.324.285,79</td>\n",
       "      <td>R$ 85,74</td>\n",
       "      <td>80.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>47,00%</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>XPSF11</td>\n",
       "      <td>Outros</td>\n",
       "      <td>R$ 93,69</td>\n",
       "      <td>15014.0</td>\n",
       "      <td>R$ 0,75</td>\n",
       "      <td>0,80%</td>\n",
       "      <td>2,50%</td>\n",
       "      <td>4,81%</td>\n",
       "      <td>9,28%</td>\n",
       "      <td>0,83%</td>\n",
       "      <td>...</td>\n",
       "      <td>R$ 401.337.388,51</td>\n",
       "      <td>R$ 92,68</td>\n",
       "      <td>101.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>XTED11</td>\n",
       "      <td>Lajes Corporativas</td>\n",
       "      <td>R$ 6,85</td>\n",
       "      <td>3560.0</td>\n",
       "      <td>R$ 0,00</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>...</td>\n",
       "      <td>R$ 25.862.448,60</td>\n",
       "      <td>R$ 13,97</td>\n",
       "      <td>49.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>100,00%</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>YUFI11B</td>\n",
       "      <td>Residencial</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R$ 0,40</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>...</td>\n",
       "      <td>R$ 37.614.094,39</td>\n",
       "      <td>R$ 95,19</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0,18%</td>\n",
       "      <td>0,23%</td>\n",
       "      <td>0,41%</td>\n",
       "      <td>0,41%</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>244 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Códigodo fundo                Setor  Preço Atual  Liquidez Diária  \\\n",
       "137         ABCP11            Shoppings     R$ 73,97           2654.0   \n",
       "129         AFCR11              Híbrido    R$ 110,84            515.0   \n",
       "29          AFOF11  Títulos e Val. Mob.    R$ 106,75           5826.0   \n",
       "126         AIEC11   Lajes Corporativas     R$ 80,60           4416.0   \n",
       "3           ALMI11   Lajes Corporativas  R$ 1.145,00            176.0   \n",
       "..             ...                  ...          ...              ...   \n",
       "49          XPML11            Shoppings    R$ 107,75          44998.0   \n",
       "71          XPPR11               Outros     R$ 68,70          14574.0   \n",
       "171         XPSF11               Outros     R$ 93,69          15014.0   \n",
       "2           XTED11   Lajes Corporativas      R$ 6,85           3560.0   \n",
       "133        YUFI11B          Residencial          NaN              NaN   \n",
       "\n",
       "    Dividendo DividendYield DY (3M)Acumulado DY (6M)Acumulado  \\\n",
       "137   R$ 0,51         0,74%            1,27%            2,58%   \n",
       "129   R$ 1,55         1,40%            3,29%            5,94%   \n",
       "29    R$ 8,76         8,76%            8,76%            8,76%   \n",
       "126   R$ 0,57         0,69%            1,97%            3,81%   \n",
       "3     R$ 0,00         0,00%            0,00%            0,00%   \n",
       "..        ...           ...              ...              ...   \n",
       "49    R$ 0,40         0,38%            0,81%            1,95%   \n",
       "71    R$ 0,48         0,73%            2,19%            4,17%   \n",
       "171   R$ 0,75         0,80%            2,50%            4,81%   \n",
       "2     R$ 0,00         0,00%            0,00%            0,00%   \n",
       "133   R$ 0,40         0,00%            0,00%            0,00%   \n",
       "\n",
       "    DY (12M)Acumulado DY (3M)Média  ...       PatrimônioLíq.          VPA  \\\n",
       "137             0,00%        0,42%  ...  R$ 1.077.525.130,29     R$ 88,29   \n",
       "129            10,57%        1,10%  ...    R$ 121.528.154,28    R$ 102,36   \n",
       "29             16,82%        2,92%  ...     R$ 39.305.966,92    R$ 107,06   \n",
       "126             0,00%        0,66%  ...    R$ 483.257.233,98    R$ 100,16   \n",
       "3               0,00%        0,00%  ...    R$ 229.163.662,27  R$ 2.061,25   \n",
       "..                ...          ...  ...                  ...          ...   \n",
       "49              3,56%        0,27%  ...  R$ 2.001.282.055,70    R$ 109,91   \n",
       "71              8,88%        0,73%  ...    R$ 627.324.285,79     R$ 85,74   \n",
       "171             9,28%        0,83%  ...    R$ 401.337.388,51     R$ 92,68   \n",
       "2               0,00%        0,00%  ...     R$ 25.862.448,60     R$ 13,97   \n",
       "133             0,00%        0,00%  ...     R$ 37.614.094,39     R$ 95,19   \n",
       "\n",
       "     P/VPA DYPatrimonial VariaçãoPatrimonial Rentab. Patr.no Período  \\\n",
       "137   84.0         0,44%              -0,05%                   0,39%   \n",
       "129  108.0           NaN                 NaN                     NaN   \n",
       "29   100.0           NaN                 NaN                     NaN   \n",
       "126   80.0           NaN                 NaN                     NaN   \n",
       "3     56.0           NaN                 NaN                     NaN   \n",
       "..     ...           ...                 ...                     ...   \n",
       "49    98.0           NaN                 NaN                     NaN   \n",
       "71    80.0           NaN                 NaN                     NaN   \n",
       "171  101.0           NaN                 NaN                     NaN   \n",
       "2     49.0           NaN                 NaN                     NaN   \n",
       "133    NaN         0,18%               0,23%                   0,41%   \n",
       "\n",
       "    Rentab. Patr.Acumulada VacânciaFísica  VacânciaFinanceira QuantidadeAtivos  \n",
       "137                  1,48%          9,10%                 NaN                1  \n",
       "129                    NaN            NaN                 NaN                0  \n",
       "29                     NaN            NaN                 NaN                0  \n",
       "126                    NaN          0,00%                 NaN                2  \n",
       "3                      NaN         68,15%                 NaN                1  \n",
       "..                     ...            ...                 ...              ...  \n",
       "49                     NaN          5,90%                 NaN               13  \n",
       "71                     NaN         47,00%                 NaN                4  \n",
       "171                    NaN            NaN                 NaN                0  \n",
       "2                      NaN          0,00%             100,00%                1  \n",
       "133                  0,41%            NaN                 NaN                0  \n",
       "\n",
       "[244 rows x 26 columns]"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 244 entries, 137 to 133\n",
      "Data columns (total 26 columns):\n",
      " #   Column                   Non-Null Count  Dtype  \n",
      "---  ------                   --------------  -----  \n",
      " 0   Códigodo fundo           244 non-null    object \n",
      " 1   Setor                    243 non-null    object \n",
      " 2   Preço Atual              237 non-null    object \n",
      " 3   Liquidez Diária          237 non-null    float64\n",
      " 4   Dividendo                244 non-null    object \n",
      " 5   DividendYield            231 non-null    object \n",
      " 6   DY (3M)Acumulado         231 non-null    object \n",
      " 7   DY (6M)Acumulado         231 non-null    object \n",
      " 8   DY (12M)Acumulado        231 non-null    object \n",
      " 9   DY (3M)Média             231 non-null    object \n",
      " 10  DY (6M)Média             231 non-null    object \n",
      " 11  DY (12M)Média            231 non-null    object \n",
      " 12  DY Ano                   220 non-null    object \n",
      " 13  Variação Preço           231 non-null    object \n",
      " 14  Rentab.Período           231 non-null    object \n",
      " 15  Rentab.Acumulada         231 non-null    object \n",
      " 16  PatrimônioLíq.           244 non-null    object \n",
      " 17  VPA                      244 non-null    object \n",
      " 18  P/VPA                    237 non-null    float64\n",
      " 19  DYPatrimonial            56 non-null     object \n",
      " 20  VariaçãoPatrimonial      56 non-null     object \n",
      " 21  Rentab. Patr.no Período  56 non-null     object \n",
      " 22  Rentab. Patr.Acumulada   56 non-null     object \n",
      " 23  VacânciaFísica           118 non-null    object \n",
      " 24  VacânciaFinanceira       26 non-null     object \n",
      " 25  QuantidadeAtivos         244 non-null    int64  \n",
      "dtypes: float64(2), int64(1), object(23)\n",
      "memory usage: 51.5+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>Códigodo fundo</th>\n",
       "      <th>Setor</th>\n",
       "      <th>Preço Atual</th>\n",
       "      <th>Liquidez Diária</th>\n",
       "      <th>Dividendo</th>\n",
       "      <th>DividendYield</th>\n",
       "      <th>DY (3M)Acumulado</th>\n",
       "      <th>DY (6M)Acumulado</th>\n",
       "      <th>DY (12M)Acumulado</th>\n",
       "      <th>DY (3M)Média</th>\n",
       "      <th>...</th>\n",
       "      <th>PatrimônioLíq.</th>\n",
       "      <th>VPA</th>\n",
       "      <th>P/VPA</th>\n",
       "      <th>DYPatrimonial</th>\n",
       "      <th>VariaçãoPatrimonial</th>\n",
       "      <th>Rentab. Patr.no Período</th>\n",
       "      <th>Rentab. Patr.Acumulada</th>\n",
       "      <th>VacânciaFísica</th>\n",
       "      <th>VacânciaFinanceira</th>\n",
       "      <th>QuantidadeAtivos</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>244</td>\n",
       "      <td>243</td>\n",
       "      <td>237</td>\n",
       "      <td>237.000000</td>\n",
       "      <td>244</td>\n",
       "      <td>231</td>\n",
       "      <td>231</td>\n",
       "      <td>231</td>\n",
       "      <td>231</td>\n",
       "      <td>231</td>\n",
       "      <td>...</td>\n",
       "      <td>244</td>\n",
       "      <td>244</td>\n",
       "      <td>237.0</td>\n",
       "      <td>56</td>\n",
       "      <td>56</td>\n",
       "      <td>56</td>\n",
       "      <td>56</td>\n",
       "      <td>118</td>\n",
       "      <td>26</td>\n",
       "      <td>244.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>242</td>\n",
       "      <td>9</td>\n",
       "      <td>226</td>\n",
       "      <td>NaN</td>\n",
       "      <td>126</td>\n",
       "      <td>101</td>\n",
       "      <td>150</td>\n",
       "      <td>162</td>\n",
       "      <td>145</td>\n",
       "      <td>100</td>\n",
       "      <td>...</td>\n",
       "      <td>242</td>\n",
       "      <td>236</td>\n",
       "      <td>NaN</td>\n",
       "      <td>44</td>\n",
       "      <td>49</td>\n",
       "      <td>48</td>\n",
       "      <td>52</td>\n",
       "      <td>73</td>\n",
       "      <td>16</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>RBBV11</td>\n",
       "      <td>Títulos e Val. Mob.</td>\n",
       "      <td>R$ 100,00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>R$ 0,00</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>...</td>\n",
       "      <td>R$ 126.592.088,63</td>\n",
       "      <td>R$ 100,94</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1,00%</td>\n",
       "      <td>0,03%</td>\n",
       "      <td>1,04%</td>\n",
       "      <td>3,30%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>3</td>\n",
       "      <td>74</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14</td>\n",
       "      <td>20</td>\n",
       "      <td>22</td>\n",
       "      <td>44</td>\n",
       "      <td>75</td>\n",
       "      <td>22</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>40</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15667.303797</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>inf</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.340164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>45499.605553</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.930969</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-155.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>314.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>84.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3414.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>96.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>14346.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>102.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>545749.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>inf</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>72.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>11 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       Códigodo fundo                Setor Preço Atual  Liquidez Diária  \\\n",
       "count             244                  243         237       237.000000   \n",
       "unique            242                    9         226              NaN   \n",
       "top            RBBV11  Títulos e Val. Mob.   R$ 100,00              NaN   \n",
       "freq                3                   74           3              NaN   \n",
       "mean              NaN                  NaN         NaN     15667.303797   \n",
       "std               NaN                  NaN         NaN     45499.605553   \n",
       "min               NaN                  NaN         NaN         1.000000   \n",
       "25%               NaN                  NaN         NaN       314.000000   \n",
       "50%               NaN                  NaN         NaN      3414.000000   \n",
       "75%               NaN                  NaN         NaN     14346.000000   \n",
       "max               NaN                  NaN         NaN    545749.000000   \n",
       "\n",
       "       Dividendo DividendYield DY (3M)Acumulado DY (6M)Acumulado  \\\n",
       "count        244           231              231              231   \n",
       "unique       126           101              150              162   \n",
       "top      R$ 0,00         0,00%            0,00%            0,00%   \n",
       "freq          14            20               22               44   \n",
       "mean         NaN           NaN              NaN              NaN   \n",
       "std          NaN           NaN              NaN              NaN   \n",
       "min          NaN           NaN              NaN              NaN   \n",
       "25%          NaN           NaN              NaN              NaN   \n",
       "50%          NaN           NaN              NaN              NaN   \n",
       "75%          NaN           NaN              NaN              NaN   \n",
       "max          NaN           NaN              NaN              NaN   \n",
       "\n",
       "       DY (12M)Acumulado DY (3M)Média  ...     PatrimônioLíq.        VPA  \\\n",
       "count                231          231  ...                244        244   \n",
       "unique               145          100  ...                242        236   \n",
       "top                0,00%        0,00%  ...  R$ 126.592.088,63  R$ 100,94   \n",
       "freq                  75           22  ...                  3          3   \n",
       "mean                 NaN          NaN  ...                NaN        NaN   \n",
       "std                  NaN          NaN  ...                NaN        NaN   \n",
       "min                  NaN          NaN  ...                NaN        NaN   \n",
       "25%                  NaN          NaN  ...                NaN        NaN   \n",
       "50%                  NaN          NaN  ...                NaN        NaN   \n",
       "75%                  NaN          NaN  ...                NaN        NaN   \n",
       "max                  NaN          NaN  ...                NaN        NaN   \n",
       "\n",
       "        P/VPA DYPatrimonial VariaçãoPatrimonial Rentab. Patr.no Período  \\\n",
       "count   237.0            56                  56                      56   \n",
       "unique    NaN            44                  49                      48   \n",
       "top       NaN         1,00%               0,03%                   1,04%   \n",
       "freq      NaN             4                   5                       3   \n",
       "mean      inf           NaN                 NaN                     NaN   \n",
       "std       NaN           NaN                 NaN                     NaN   \n",
       "min    -155.0           NaN                 NaN                     NaN   \n",
       "25%      84.0           NaN                 NaN                     NaN   \n",
       "50%      96.0           NaN                 NaN                     NaN   \n",
       "75%     102.0           NaN                 NaN                     NaN   \n",
       "max       inf           NaN                 NaN                     NaN   \n",
       "\n",
       "       Rentab. Patr.Acumulada VacânciaFísica  VacânciaFinanceira  \\\n",
       "count                      56            118                  26   \n",
       "unique                     52             73                  16   \n",
       "top                     3,30%          0,00%               0,00%   \n",
       "freq                        3             40                  11   \n",
       "mean                      NaN            NaN                 NaN   \n",
       "std                       NaN            NaN                 NaN   \n",
       "min                       NaN            NaN                 NaN   \n",
       "25%                       NaN            NaN                 NaN   \n",
       "50%                       NaN            NaN                 NaN   \n",
       "75%                       NaN            NaN                 NaN   \n",
       "max                       NaN            NaN                 NaN   \n",
       "\n",
       "       QuantidadeAtivos  \n",
       "count        244.000000  \n",
       "unique              NaN  \n",
       "top                 NaN  \n",
       "freq                NaN  \n",
       "mean           3.340164  \n",
       "std            7.930969  \n",
       "min            0.000000  \n",
       "25%            0.000000  \n",
       "50%            1.000000  \n",
       "75%            3.000000  \n",
       "max           72.000000  \n",
       "\n",
       "[11 rows x 26 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe(include='all')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Shoppings', 'Híbrido', 'Títulos e Val. Mob.',\n",
       "       'Lajes Corporativas', 'Logística', 'Outros', nan, 'Residencial',\n",
       "       'Hospital', 'Hotel'], dtype=object)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['Setor'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Códigodo fundo', 'Setor', 'Preço Atual', 'Liquidez Diária',\n",
       "       'Dividendo', 'DividendYield', 'DY (3M)Acumulado', 'DY (6M)Acumulado',\n",
       "       'DY (12M)Acumulado', 'DY (3M)Média', 'DY (6M)Média', 'DY (12M)Média',\n",
       "       'DY Ano', 'Variação Preço', 'Rentab.Período', 'Rentab.Acumulada',\n",
       "       'PatrimônioLíq.', 'VPA', 'P/VPA', 'DYPatrimonial',\n",
       "       'VariaçãoPatrimonial', 'Rentab. Patr.no Período',\n",
       "       'Rentab. Patr.Acumulada', 'VacânciaFísica', 'VacânciaFinanceira',\n",
       "       'QuantidadeAtivos'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Limpeza e Formatação de Dados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Códigodo fundo               0\n",
       "Setor                        1\n",
       "Preço Atual                  7\n",
       "Liquidez Diária              7\n",
       "Dividendo                    0\n",
       "DividendYield               13\n",
       "DY (3M)Acumulado            13\n",
       "DY (6M)Acumulado            13\n",
       "DY (12M)Acumulado           13\n",
       "DY (3M)Média                13\n",
       "DY (6M)Média                13\n",
       "DY (12M)Média               13\n",
       "DY Ano                      24\n",
       "Variação Preço              13\n",
       "Rentab.Período              13\n",
       "Rentab.Acumulada            13\n",
       "PatrimônioLíq.               0\n",
       "VPA                          0\n",
       "P/VPA                        7\n",
       "DYPatrimonial              188\n",
       "VariaçãoPatrimonial        188\n",
       "Rentab. Patr.no Período    188\n",
       "Rentab. Patr.Acumulada     188\n",
       "VacânciaFísica             126\n",
       "VacânciaFinanceira         218\n",
       "QuantidadeAtivos             0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dados categóricos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "categorical_columns = ['Códigodo fundo','Setor']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "idx = df[df['Setor'].isna()].index\n",
    "df.drop(idx, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Códigodo fundo    0\n",
       "Setor             0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[categorical_columns].isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[categorical_columns] = df[categorical_columns].astype('category')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 243 entries, 137 to 133\n",
      "Data columns (total 26 columns):\n",
      " #   Column                   Non-Null Count  Dtype   \n",
      "---  ------                   --------------  -----   \n",
      " 0   Códigodo fundo           243 non-null    category\n",
      " 1   Setor                    243 non-null    category\n",
      " 2   Preço Atual              237 non-null    object  \n",
      " 3   Liquidez Diária          237 non-null    float64 \n",
      " 4   Dividendo                243 non-null    object  \n",
      " 5   DividendYield            231 non-null    object  \n",
      " 6   DY (3M)Acumulado         231 non-null    object  \n",
      " 7   DY (6M)Acumulado         231 non-null    object  \n",
      " 8   DY (12M)Acumulado        231 non-null    object  \n",
      " 9   DY (3M)Média             231 non-null    object  \n",
      " 10  DY (6M)Média             231 non-null    object  \n",
      " 11  DY (12M)Média            231 non-null    object  \n",
      " 12  DY Ano                   220 non-null    object  \n",
      " 13  Variação Preço           231 non-null    object  \n",
      " 14  Rentab.Período           231 non-null    object  \n",
      " 15  Rentab.Acumulada         231 non-null    object  \n",
      " 16  PatrimônioLíq.           243 non-null    object  \n",
      " 17  VPA                      243 non-null    object  \n",
      " 18  P/VPA                    237 non-null    float64 \n",
      " 19  DYPatrimonial            56 non-null     object  \n",
      " 20  VariaçãoPatrimonial      56 non-null     object  \n",
      " 21  Rentab. Patr.no Período  56 non-null     object  \n",
      " 22  Rentab. Patr.Acumulada   56 non-null     object  \n",
      " 23  VacânciaFísica           118 non-null    object  \n",
      " 24  VacânciaFinanceira       26 non-null     object  \n",
      " 25  QuantidadeAtivos         243 non-null    int64   \n",
      "dtypes: category(2), float64(2), int64(1), object(21)\n",
      "memory usage: 58.5+ KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Códigodo fundo               0\n",
       "Setor                        0\n",
       "Preço Atual                  6\n",
       "Liquidez Diária              6\n",
       "Dividendo                    0\n",
       "DividendYield               12\n",
       "DY (3M)Acumulado            12\n",
       "DY (6M)Acumulado            12\n",
       "DY (12M)Acumulado           12\n",
       "DY (3M)Média                12\n",
       "DY (6M)Média                12\n",
       "DY (12M)Média               12\n",
       "DY Ano                      23\n",
       "Variação Preço              12\n",
       "Rentab.Período              12\n",
       "Rentab.Acumulada            12\n",
       "PatrimônioLíq.               0\n",
       "VPA                          0\n",
       "P/VPA                        6\n",
       "DYPatrimonial              187\n",
       "VariaçãoPatrimonial        187\n",
       "Rentab. Patr.no Período    187\n",
       "Rentab. Patr.Acumulada     187\n",
       "VacânciaFísica             125\n",
       "VacânciaFinanceira         217\n",
       "QuantidadeAtivos             0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dados Numéricos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "col_floats = list(df.iloc[:,2:-1].columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Dados nulos\n",
    "df[col_floats] = df[col_floats].fillna(value=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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>Preço Atual</th>\n",
       "      <th>Liquidez Diária</th>\n",
       "      <th>Dividendo</th>\n",
       "      <th>DividendYield</th>\n",
       "      <th>DY (3M)Acumulado</th>\n",
       "      <th>DY (6M)Acumulado</th>\n",
       "      <th>DY (12M)Acumulado</th>\n",
       "      <th>DY (3M)Média</th>\n",
       "      <th>DY (6M)Média</th>\n",
       "      <th>DY (12M)Média</th>\n",
       "      <th>...</th>\n",
       "      <th>Rentab.Acumulada</th>\n",
       "      <th>PatrimônioLíq.</th>\n",
       "      <th>VPA</th>\n",
       "      <th>P/VPA</th>\n",
       "      <th>DYPatrimonial</th>\n",
       "      <th>VariaçãoPatrimonial</th>\n",
       "      <th>Rentab. Patr.no Período</th>\n",
       "      <th>Rentab. Patr.Acumulada</th>\n",
       "      <th>VacânciaFísica</th>\n",
       "      <th>VacânciaFinanceira</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>R$ 73,97</td>\n",
       "      <td>2654.0</td>\n",
       "      <td>R$ 0,51</td>\n",
       "      <td>0,74%</td>\n",
       "      <td>1,27%</td>\n",
       "      <td>2,58%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,42%</td>\n",
       "      <td>0,43%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>...</td>\n",
       "      <td>-9,06%</td>\n",
       "      <td>R$ 1.077.525.130,29</td>\n",
       "      <td>R$ 88,29</td>\n",
       "      <td>84.0</td>\n",
       "      <td>0,44%</td>\n",
       "      <td>-0,05%</td>\n",
       "      <td>0,39%</td>\n",
       "      <td>1,48%</td>\n",
       "      <td>9,10%</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>R$ 110,84</td>\n",
       "      <td>515.0</td>\n",
       "      <td>R$ 1,55</td>\n",
       "      <td>1,40%</td>\n",
       "      <td>3,29%</td>\n",
       "      <td>5,94%</td>\n",
       "      <td>10,57%</td>\n",
       "      <td>1,10%</td>\n",
       "      <td>0,99%</td>\n",
       "      <td>0,88%</td>\n",
       "      <td>...</td>\n",
       "      <td>2,38%</td>\n",
       "      <td>R$ 121.528.154,28</td>\n",
       "      <td>R$ 102,36</td>\n",
       "      <td>108.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>R$ 106,75</td>\n",
       "      <td>5826.0</td>\n",
       "      <td>R$ 8,76</td>\n",
       "      <td>8,76%</td>\n",
       "      <td>8,76%</td>\n",
       "      <td>8,76%</td>\n",
       "      <td>16,82%</td>\n",
       "      <td>2,92%</td>\n",
       "      <td>1,46%</td>\n",
       "      <td>1,40%</td>\n",
       "      <td>...</td>\n",
       "      <td>17,52%</td>\n",
       "      <td>R$ 39.305.966,92</td>\n",
       "      <td>R$ 107,06</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>R$ 80,60</td>\n",
       "      <td>4416.0</td>\n",
       "      <td>R$ 0,57</td>\n",
       "      <td>0,69%</td>\n",
       "      <td>1,97%</td>\n",
       "      <td>3,81%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,66%</td>\n",
       "      <td>0,64%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>...</td>\n",
       "      <td>-8,70%</td>\n",
       "      <td>R$ 483.257.233,98</td>\n",
       "      <td>R$ 100,16</td>\n",
       "      <td>80.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>R$ 1.145,00</td>\n",
       "      <td>176.0</td>\n",
       "      <td>R$ 0,00</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>...</td>\n",
       "      <td>-28,29%</td>\n",
       "      <td>R$ 229.163.662,27</td>\n",
       "      <td>R$ 2.061,25</td>\n",
       "      <td>56.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>68,15%</td>\n",
       "      <td>0</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",
       "      <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>49</th>\n",
       "      <td>R$ 107,75</td>\n",
       "      <td>44998.0</td>\n",
       "      <td>R$ 0,40</td>\n",
       "      <td>0,38%</td>\n",
       "      <td>0,81%</td>\n",
       "      <td>1,95%</td>\n",
       "      <td>3,56%</td>\n",
       "      <td>0,27%</td>\n",
       "      <td>0,32%</td>\n",
       "      <td>0,30%</td>\n",
       "      <td>...</td>\n",
       "      <td>-5,05%</td>\n",
       "      <td>R$ 2.001.282.055,70</td>\n",
       "      <td>R$ 109,91</td>\n",
       "      <td>98.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5,90%</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>R$ 68,70</td>\n",
       "      <td>14574.0</td>\n",
       "      <td>R$ 0,48</td>\n",
       "      <td>0,73%</td>\n",
       "      <td>2,19%</td>\n",
       "      <td>4,17%</td>\n",
       "      <td>8,88%</td>\n",
       "      <td>0,73%</td>\n",
       "      <td>0,70%</td>\n",
       "      <td>0,74%</td>\n",
       "      <td>...</td>\n",
       "      <td>-15,23%</td>\n",
       "      <td>R$ 627.324.285,79</td>\n",
       "      <td>R$ 85,74</td>\n",
       "      <td>80.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>47,00%</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>R$ 93,69</td>\n",
       "      <td>15014.0</td>\n",
       "      <td>R$ 0,75</td>\n",
       "      <td>0,80%</td>\n",
       "      <td>2,50%</td>\n",
       "      <td>4,81%</td>\n",
       "      <td>9,28%</td>\n",
       "      <td>0,83%</td>\n",
       "      <td>0,80%</td>\n",
       "      <td>0,77%</td>\n",
       "      <td>...</td>\n",
       "      <td>2,39%</td>\n",
       "      <td>R$ 401.337.388,51</td>\n",
       "      <td>R$ 92,68</td>\n",
       "      <td>101.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>R$ 6,85</td>\n",
       "      <td>3560.0</td>\n",
       "      <td>R$ 0,00</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>...</td>\n",
       "      <td>-37,83%</td>\n",
       "      <td>R$ 25.862.448,60</td>\n",
       "      <td>R$ 13,97</td>\n",
       "      <td>49.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>100,00%</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>R$ 0,40</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>...</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>R$ 37.614.094,39</td>\n",
       "      <td>R$ 95,19</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0,18%</td>\n",
       "      <td>0,23%</td>\n",
       "      <td>0,41%</td>\n",
       "      <td>0,41%</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>243 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Preço Atual  Liquidez Diária Dividendo DividendYield DY (3M)Acumulado  \\\n",
       "137     R$ 73,97           2654.0   R$ 0,51         0,74%            1,27%   \n",
       "129    R$ 110,84            515.0   R$ 1,55         1,40%            3,29%   \n",
       "29     R$ 106,75           5826.0   R$ 8,76         8,76%            8,76%   \n",
       "126     R$ 80,60           4416.0   R$ 0,57         0,69%            1,97%   \n",
       "3    R$ 1.145,00            176.0   R$ 0,00         0,00%            0,00%   \n",
       "..           ...              ...       ...           ...              ...   \n",
       "49     R$ 107,75          44998.0   R$ 0,40         0,38%            0,81%   \n",
       "71      R$ 68,70          14574.0   R$ 0,48         0,73%            2,19%   \n",
       "171     R$ 93,69          15014.0   R$ 0,75         0,80%            2,50%   \n",
       "2        R$ 6,85           3560.0   R$ 0,00         0,00%            0,00%   \n",
       "133            0              0.0   R$ 0,40         0,00%            0,00%   \n",
       "\n",
       "    DY (6M)Acumulado DY (12M)Acumulado DY (3M)Média DY (6M)Média  \\\n",
       "137            2,58%             0,00%        0,42%        0,43%   \n",
       "129            5,94%            10,57%        1,10%        0,99%   \n",
       "29             8,76%            16,82%        2,92%        1,46%   \n",
       "126            3,81%             0,00%        0,66%        0,64%   \n",
       "3              0,00%             0,00%        0,00%        0,00%   \n",
       "..               ...               ...          ...          ...   \n",
       "49             1,95%             3,56%        0,27%        0,32%   \n",
       "71             4,17%             8,88%        0,73%        0,70%   \n",
       "171            4,81%             9,28%        0,83%        0,80%   \n",
       "2              0,00%             0,00%        0,00%        0,00%   \n",
       "133            0,00%             0,00%        0,00%        0,00%   \n",
       "\n",
       "    DY (12M)Média  ... Rentab.Acumulada       PatrimônioLíq.          VPA  \\\n",
       "137         0,00%  ...           -9,06%  R$ 1.077.525.130,29     R$ 88,29   \n",
       "129         0,88%  ...            2,38%    R$ 121.528.154,28    R$ 102,36   \n",
       "29          1,40%  ...           17,52%     R$ 39.305.966,92    R$ 107,06   \n",
       "126         0,00%  ...           -8,70%    R$ 483.257.233,98    R$ 100,16   \n",
       "3           0,00%  ...          -28,29%    R$ 229.163.662,27  R$ 2.061,25   \n",
       "..            ...  ...              ...                  ...          ...   \n",
       "49          0,30%  ...           -5,05%  R$ 2.001.282.055,70    R$ 109,91   \n",
       "71          0,74%  ...          -15,23%    R$ 627.324.285,79     R$ 85,74   \n",
       "171         0,77%  ...            2,39%    R$ 401.337.388,51     R$ 92,68   \n",
       "2           0,00%  ...          -37,83%     R$ 25.862.448,60     R$ 13,97   \n",
       "133         0,00%  ...            0,00%     R$ 37.614.094,39     R$ 95,19   \n",
       "\n",
       "     P/VPA DYPatrimonial VariaçãoPatrimonial  Rentab. Patr.no Período  \\\n",
       "137   84.0         0,44%              -0,05%                    0,39%   \n",
       "129  108.0             0                   0                        0   \n",
       "29   100.0             0                   0                        0   \n",
       "126   80.0             0                   0                        0   \n",
       "3     56.0             0                   0                        0   \n",
       "..     ...           ...                 ...                      ...   \n",
       "49    98.0             0                   0                        0   \n",
       "71    80.0             0                   0                        0   \n",
       "171  101.0             0                   0                        0   \n",
       "2     49.0             0                   0                        0   \n",
       "133    0.0         0,18%               0,23%                    0,41%   \n",
       "\n",
       "    Rentab. Patr.Acumulada VacânciaFísica VacânciaFinanceira  \n",
       "137                  1,48%          9,10%                  0  \n",
       "129                      0              0                  0  \n",
       "29                       0              0                  0  \n",
       "126                      0          0,00%                  0  \n",
       "3                        0         68,15%                  0  \n",
       "..                     ...            ...                ...  \n",
       "49                       0          5,90%                  0  \n",
       "71                       0         47,00%                  0  \n",
       "171                      0              0                  0  \n",
       "2                        0          0,00%            100,00%  \n",
       "133                  0,41%              0                  0  \n",
       "\n",
       "[243 rows x 23 columns]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[col_floats]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>Preço Atual</th>\n",
       "      <th>Liquidez Diária</th>\n",
       "      <th>Dividendo</th>\n",
       "      <th>DividendYield</th>\n",
       "      <th>DY (3M)Acumulado</th>\n",
       "      <th>DY (6M)Acumulado</th>\n",
       "      <th>DY (12M)Acumulado</th>\n",
       "      <th>DY (3M)Média</th>\n",
       "      <th>DY (6M)Média</th>\n",
       "      <th>DY (12M)Média</th>\n",
       "      <th>...</th>\n",
       "      <th>Rentab.Acumulada</th>\n",
       "      <th>PatrimônioLíq.</th>\n",
       "      <th>VPA</th>\n",
       "      <th>P/VPA</th>\n",
       "      <th>DYPatrimonial</th>\n",
       "      <th>VariaçãoPatrimonial</th>\n",
       "      <th>Rentab. Patr.no Período</th>\n",
       "      <th>Rentab. Patr.Acumulada</th>\n",
       "      <th>VacânciaFísica</th>\n",
       "      <th>VacânciaFinanceira</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>R$ 73,97</td>\n",
       "      <td>2654.0</td>\n",
       "      <td>R$ 0,51</td>\n",
       "      <td>0,74%</td>\n",
       "      <td>1,27%</td>\n",
       "      <td>2,58%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,42%</td>\n",
       "      <td>0,43%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>...</td>\n",
       "      <td>-9,06%</td>\n",
       "      <td>R$ 1.077.525.130,29</td>\n",
       "      <td>R$ 88,29</td>\n",
       "      <td>84.0</td>\n",
       "      <td>0,44%</td>\n",
       "      <td>-0,05%</td>\n",
       "      <td>0,39%</td>\n",
       "      <td>1,48%</td>\n",
       "      <td>9,10%</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>R$ 110,84</td>\n",
       "      <td>515.0</td>\n",
       "      <td>R$ 1,55</td>\n",
       "      <td>1,40%</td>\n",
       "      <td>3,29%</td>\n",
       "      <td>5,94%</td>\n",
       "      <td>10,57%</td>\n",
       "      <td>1,10%</td>\n",
       "      <td>0,99%</td>\n",
       "      <td>0,88%</td>\n",
       "      <td>...</td>\n",
       "      <td>2,38%</td>\n",
       "      <td>R$ 121.528.154,28</td>\n",
       "      <td>R$ 102,36</td>\n",
       "      <td>108.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>R$ 106,75</td>\n",
       "      <td>5826.0</td>\n",
       "      <td>R$ 8,76</td>\n",
       "      <td>8,76%</td>\n",
       "      <td>8,76%</td>\n",
       "      <td>8,76%</td>\n",
       "      <td>16,82%</td>\n",
       "      <td>2,92%</td>\n",
       "      <td>1,46%</td>\n",
       "      <td>1,40%</td>\n",
       "      <td>...</td>\n",
       "      <td>17,52%</td>\n",
       "      <td>R$ 39.305.966,92</td>\n",
       "      <td>R$ 107,06</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>R$ 80,60</td>\n",
       "      <td>4416.0</td>\n",
       "      <td>R$ 0,57</td>\n",
       "      <td>0,69%</td>\n",
       "      <td>1,97%</td>\n",
       "      <td>3,81%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,66%</td>\n",
       "      <td>0,64%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>...</td>\n",
       "      <td>-8,70%</td>\n",
       "      <td>R$ 483.257.233,98</td>\n",
       "      <td>R$ 100,16</td>\n",
       "      <td>80.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>R$ 1.145,00</td>\n",
       "      <td>176.0</td>\n",
       "      <td>R$ 0,00</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>0,00%</td>\n",
       "      <td>...</td>\n",
       "      <td>-28,29%</td>\n",
       "      <td>R$ 229.163.662,27</td>\n",
       "      <td>R$ 2.061,25</td>\n",
       "      <td>56.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>68,15%</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Preço Atual  Liquidez Diária Dividendo DividendYield DY (3M)Acumulado  \\\n",
       "137     R$ 73,97           2654.0   R$ 0,51         0,74%            1,27%   \n",
       "129    R$ 110,84            515.0   R$ 1,55         1,40%            3,29%   \n",
       "29     R$ 106,75           5826.0   R$ 8,76         8,76%            8,76%   \n",
       "126     R$ 80,60           4416.0   R$ 0,57         0,69%            1,97%   \n",
       "3    R$ 1.145,00            176.0   R$ 0,00         0,00%            0,00%   \n",
       "\n",
       "    DY (6M)Acumulado DY (12M)Acumulado DY (3M)Média DY (6M)Média  \\\n",
       "137            2,58%             0,00%        0,42%        0,43%   \n",
       "129            5,94%            10,57%        1,10%        0,99%   \n",
       "29             8,76%            16,82%        2,92%        1,46%   \n",
       "126            3,81%             0,00%        0,66%        0,64%   \n",
       "3              0,00%             0,00%        0,00%        0,00%   \n",
       "\n",
       "    DY (12M)Média  ... Rentab.Acumulada       PatrimônioLíq.          VPA  \\\n",
       "137         0,00%  ...           -9,06%  R$ 1.077.525.130,29     R$ 88,29   \n",
       "129         0,88%  ...            2,38%    R$ 121.528.154,28    R$ 102,36   \n",
       "29          1,40%  ...           17,52%     R$ 39.305.966,92    R$ 107,06   \n",
       "126         0,00%  ...           -8,70%    R$ 483.257.233,98    R$ 100,16   \n",
       "3           0,00%  ...          -28,29%    R$ 229.163.662,27  R$ 2.061,25   \n",
       "\n",
       "     P/VPA DYPatrimonial VariaçãoPatrimonial  Rentab. Patr.no Período  \\\n",
       "137   84.0         0,44%              -0,05%                    0,39%   \n",
       "129  108.0             0                   0                        0   \n",
       "29   100.0             0                   0                        0   \n",
       "126   80.0             0                   0                        0   \n",
       "3     56.0             0                   0                        0   \n",
       "\n",
       "    Rentab. Patr.Acumulada VacânciaFísica VacânciaFinanceira  \n",
       "137                  1,48%          9,10%                  0  \n",
       "129                      0              0                  0  \n",
       "29                       0              0                  0  \n",
       "126                      0          0,00%                  0  \n",
       "3                        0         68,15%                  0  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[col_floats].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[col_floats] = df[col_floats].applymap(lambda x: str(x).replace('R$', '').replace('.0','').replace('.','').replace('%','').replace(',','.'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[col_floats] = df[col_floats].astype('float')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 243 entries, 137 to 133\n",
      "Data columns (total 26 columns):\n",
      " #   Column                   Non-Null Count  Dtype   \n",
      "---  ------                   --------------  -----   \n",
      " 0   Códigodo fundo           243 non-null    category\n",
      " 1   Setor                    243 non-null    category\n",
      " 2   Preço Atual              243 non-null    float64 \n",
      " 3   Liquidez Diária          243 non-null    float64 \n",
      " 4   Dividendo                243 non-null    float64 \n",
      " 5   DividendYield            243 non-null    float64 \n",
      " 6   DY (3M)Acumulado         243 non-null    float64 \n",
      " 7   DY (6M)Acumulado         243 non-null    float64 \n",
      " 8   DY (12M)Acumulado        243 non-null    float64 \n",
      " 9   DY (3M)Média             243 non-null    float64 \n",
      " 10  DY (6M)Média             243 non-null    float64 \n",
      " 11  DY (12M)Média            243 non-null    float64 \n",
      " 12  DY Ano                   243 non-null    float64 \n",
      " 13  Variação Preço           243 non-null    float64 \n",
      " 14  Rentab.Período           243 non-null    float64 \n",
      " 15  Rentab.Acumulada         243 non-null    float64 \n",
      " 16  PatrimônioLíq.           243 non-null    float64 \n",
      " 17  VPA                      243 non-null    float64 \n",
      " 18  P/VPA                    243 non-null    float64 \n",
      " 19  DYPatrimonial            243 non-null    float64 \n",
      " 20  VariaçãoPatrimonial      243 non-null    float64 \n",
      " 21  Rentab. Patr.no Período  243 non-null    float64 \n",
      " 22  Rentab. Patr.Acumulada   243 non-null    float64 \n",
      " 23  VacânciaFísica           243 non-null    float64 \n",
      " 24  VacânciaFinanceira       243 non-null    float64 \n",
      " 25  QuantidadeAtivos         243 non-null    int64   \n",
      "dtypes: category(2), float64(23), int64(1)\n",
      "memory usage: 58.5 KB\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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>Códigodo fundo</th>\n",
       "      <th>Setor</th>\n",
       "      <th>Preço Atual</th>\n",
       "      <th>Liquidez Diária</th>\n",
       "      <th>Dividendo</th>\n",
       "      <th>DividendYield</th>\n",
       "      <th>DY (3M)Acumulado</th>\n",
       "      <th>DY (6M)Acumulado</th>\n",
       "      <th>DY (12M)Acumulado</th>\n",
       "      <th>DY (3M)Média</th>\n",
       "      <th>...</th>\n",
       "      <th>PatrimônioLíq.</th>\n",
       "      <th>VPA</th>\n",
       "      <th>P/VPA</th>\n",
       "      <th>DYPatrimonial</th>\n",
       "      <th>VariaçãoPatrimonial</th>\n",
       "      <th>Rentab. Patr.no Período</th>\n",
       "      <th>Rentab. Patr.Acumulada</th>\n",
       "      <th>VacânciaFísica</th>\n",
       "      <th>VacânciaFinanceira</th>\n",
       "      <th>QuantidadeAtivos</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>ABCP11</td>\n",
       "      <td>Shoppings</td>\n",
       "      <td>73.97</td>\n",
       "      <td>2654.0</td>\n",
       "      <td>0.51</td>\n",
       "      <td>0.74</td>\n",
       "      <td>1.27</td>\n",
       "      <td>2.58</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.42</td>\n",
       "      <td>...</td>\n",
       "      <td>1.775251e+08</td>\n",
       "      <td>88.29</td>\n",
       "      <td>84.0</td>\n",
       "      <td>0.44</td>\n",
       "      <td>-0.05</td>\n",
       "      <td>0.39</td>\n",
       "      <td>1.48</td>\n",
       "      <td>9.10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>AFCR11</td>\n",
       "      <td>Híbrido</td>\n",
       "      <td>110.84</td>\n",
       "      <td>515.0</td>\n",
       "      <td>1.55</td>\n",
       "      <td>1.40</td>\n",
       "      <td>3.29</td>\n",
       "      <td>5.94</td>\n",
       "      <td>10.57</td>\n",
       "      <td>1.10</td>\n",
       "      <td>...</td>\n",
       "      <td>1.215282e+08</td>\n",
       "      <td>102.36</td>\n",
       "      <td>108.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>AFOF11</td>\n",
       "      <td>Títulos e Val. Mob.</td>\n",
       "      <td>106.75</td>\n",
       "      <td>5826.0</td>\n",
       "      <td>8.76</td>\n",
       "      <td>8.76</td>\n",
       "      <td>8.76</td>\n",
       "      <td>8.76</td>\n",
       "      <td>16.82</td>\n",
       "      <td>2.92</td>\n",
       "      <td>...</td>\n",
       "      <td>3.930597e+07</td>\n",
       "      <td>107.06</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>AIEC11</td>\n",
       "      <td>Lajes Corporativas</td>\n",
       "      <td>80.60</td>\n",
       "      <td>4416.0</td>\n",
       "      <td>0.57</td>\n",
       "      <td>0.69</td>\n",
       "      <td>1.97</td>\n",
       "      <td>3.81</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.66</td>\n",
       "      <td>...</td>\n",
       "      <td>4.832572e+08</td>\n",
       "      <td>100.16</td>\n",
       "      <td>80.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ALMI11</td>\n",
       "      <td>Lajes Corporativas</td>\n",
       "      <td>1145.00</td>\n",
       "      <td>176.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>2.291637e+08</td>\n",
       "      <td>261.25</td>\n",
       "      <td>56.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>68.15</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Códigodo fundo                Setor  Preço Atual  Liquidez Diária  \\\n",
       "137         ABCP11            Shoppings        73.97           2654.0   \n",
       "129         AFCR11              Híbrido       110.84            515.0   \n",
       "29          AFOF11  Títulos e Val. Mob.       106.75           5826.0   \n",
       "126         AIEC11   Lajes Corporativas        80.60           4416.0   \n",
       "3           ALMI11   Lajes Corporativas      1145.00            176.0   \n",
       "\n",
       "     Dividendo  DividendYield  DY (3M)Acumulado  DY (6M)Acumulado  \\\n",
       "137       0.51           0.74              1.27              2.58   \n",
       "129       1.55           1.40              3.29              5.94   \n",
       "29        8.76           8.76              8.76              8.76   \n",
       "126       0.57           0.69              1.97              3.81   \n",
       "3         0.00           0.00              0.00              0.00   \n",
       "\n",
       "     DY (12M)Acumulado  DY (3M)Média  ...  PatrimônioLíq.     VPA  P/VPA  \\\n",
       "137               0.00          0.42  ...    1.775251e+08   88.29   84.0   \n",
       "129              10.57          1.10  ...    1.215282e+08  102.36  108.0   \n",
       "29               16.82          2.92  ...    3.930597e+07  107.06  100.0   \n",
       "126               0.00          0.66  ...    4.832572e+08  100.16   80.0   \n",
       "3                 0.00          0.00  ...    2.291637e+08  261.25   56.0   \n",
       "\n",
       "     DYPatrimonial  VariaçãoPatrimonial  Rentab. Patr.no Período  \\\n",
       "137           0.44                -0.05                     0.39   \n",
       "129           0.00                 0.00                     0.00   \n",
       "29            0.00                 0.00                     0.00   \n",
       "126           0.00                 0.00                     0.00   \n",
       "3             0.00                 0.00                     0.00   \n",
       "\n",
       "     Rentab. Patr.Acumulada  VacânciaFísica  VacânciaFinanceira  \\\n",
       "137                    1.48            9.10                 0.0   \n",
       "129                    0.00            0.00                 0.0   \n",
       "29                     0.00            0.00                 0.0   \n",
       "126                    0.00            0.00                 0.0   \n",
       "3                      0.00           68.15                 0.0   \n",
       "\n",
       "     QuantidadeAtivos  \n",
       "137                 1  \n",
       "129                 0  \n",
       "29                  0  \n",
       "126                 2  \n",
       "3                   1  \n",
       "\n",
       "[5 rows x 26 columns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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>Preço Atual</th>\n",
       "      <th>Liquidez Diária</th>\n",
       "      <th>Dividendo</th>\n",
       "      <th>DividendYield</th>\n",
       "      <th>DY (3M)Acumulado</th>\n",
       "      <th>DY (6M)Acumulado</th>\n",
       "      <th>DY (12M)Acumulado</th>\n",
       "      <th>DY (3M)Média</th>\n",
       "      <th>DY (6M)Média</th>\n",
       "      <th>DY (12M)Média</th>\n",
       "      <th>...</th>\n",
       "      <th>PatrimônioLíq.</th>\n",
       "      <th>VPA</th>\n",
       "      <th>P/VPA</th>\n",
       "      <th>DYPatrimonial</th>\n",
       "      <th>VariaçãoPatrimonial</th>\n",
       "      <th>Rentab. Patr.no Período</th>\n",
       "      <th>Rentab. Patr.Acumulada</th>\n",
       "      <th>VacânciaFísica</th>\n",
       "      <th>VacânciaFinanceira</th>\n",
       "      <th>QuantidadeAtivos</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>243.000000</td>\n",
       "      <td>243.000000</td>\n",
       "      <td>243.000000</td>\n",
       "      <td>243.000000</td>\n",
       "      <td>243.000000</td>\n",
       "      <td>243.000000</td>\n",
       "      <td>243.000000</td>\n",
       "      <td>243.000000</td>\n",
       "      <td>243.000000</td>\n",
       "      <td>243.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>2.430000e+02</td>\n",
       "      <td>243.000000</td>\n",
       "      <td>243.0</td>\n",
       "      <td>243.000000</td>\n",
       "      <td>243.000000</td>\n",
       "      <td>243.000000</td>\n",
       "      <td>243.000000</td>\n",
       "      <td>243.000000</td>\n",
       "      <td>243.000000</td>\n",
       "      <td>243.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>467.841852</td>\n",
       "      <td>15280.456790</td>\n",
       "      <td>5.390000</td>\n",
       "      <td>1.039959</td>\n",
       "      <td>2.116008</td>\n",
       "      <td>3.418395</td>\n",
       "      <td>5.406461</td>\n",
       "      <td>0.705432</td>\n",
       "      <td>0.569630</td>\n",
       "      <td>0.450453</td>\n",
       "      <td>...</td>\n",
       "      <td>4.057168e+08</td>\n",
       "      <td>597.471152</td>\n",
       "      <td>inf</td>\n",
       "      <td>0.154198</td>\n",
       "      <td>0.049588</td>\n",
       "      <td>0.204074</td>\n",
       "      <td>0.588601</td>\n",
       "      <td>7.384486</td>\n",
       "      <td>1.310288</td>\n",
       "      <td>3.353909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>5110.572838</td>\n",
       "      <td>44998.023384</td>\n",
       "      <td>59.116933</td>\n",
       "      <td>3.403459</td>\n",
       "      <td>3.546429</td>\n",
       "      <td>4.139285</td>\n",
       "      <td>5.860466</td>\n",
       "      <td>1.182217</td>\n",
       "      <td>0.689965</td>\n",
       "      <td>0.488266</td>\n",
       "      <td>...</td>\n",
       "      <td>6.300817e+08</td>\n",
       "      <td>5744.776012</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.316948</td>\n",
       "      <td>0.732803</td>\n",
       "      <td>0.841465</td>\n",
       "      <td>2.339812</td>\n",
       "      <td>17.740402</td>\n",
       "      <td>8.271709</td>\n",
       "      <td>7.944426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>-6.281734e+06</td>\n",
       "      <td>-8.280000</td>\n",
       "      <td>-155.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-2.540000</td>\n",
       "      <td>-1.860000</td>\n",
       "      <td>-9.350000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>79.645000</td>\n",
       "      <td>215.000000</td>\n",
       "      <td>0.415000</td>\n",
       "      <td>0.425000</td>\n",
       "      <td>1.065000</td>\n",
       "      <td>1.045000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.355000</td>\n",
       "      <td>0.175000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>4.817601e+07</td>\n",
       "      <td>92.490000</td>\n",
       "      <td>83.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>97.000000</td>\n",
       "      <td>3195.000000</td>\n",
       "      <td>0.650000</td>\n",
       "      <td>0.670000</td>\n",
       "      <td>1.830000</td>\n",
       "      <td>3.340000</td>\n",
       "      <td>5.800000</td>\n",
       "      <td>0.610000</td>\n",
       "      <td>0.560000</td>\n",
       "      <td>0.480000</td>\n",
       "      <td>...</td>\n",
       "      <td>1.659057e+08</td>\n",
       "      <td>99.870000</td>\n",
       "      <td>95.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>109.800000</td>\n",
       "      <td>14237.000000</td>\n",
       "      <td>0.985000</td>\n",
       "      <td>0.820000</td>\n",
       "      <td>2.385000</td>\n",
       "      <td>4.420000</td>\n",
       "      <td>8.120000</td>\n",
       "      <td>0.795000</td>\n",
       "      <td>0.740000</td>\n",
       "      <td>0.680000</td>\n",
       "      <td>...</td>\n",
       "      <td>4.734209e+08</td>\n",
       "      <td>109.645000</td>\n",
       "      <td>102.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.350000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>79700.050000</td>\n",
       "      <td>545749.000000</td>\n",
       "      <td>919.210000</td>\n",
       "      <td>36.320000</td>\n",
       "      <td>37.990000</td>\n",
       "      <td>40.960000</td>\n",
       "      <td>45.220000</td>\n",
       "      <td>12.660000</td>\n",
       "      <td>6.830000</td>\n",
       "      <td>3.770000</td>\n",
       "      <td>...</td>\n",
       "      <td>3.850279e+09</td>\n",
       "      <td>87737.000000</td>\n",
       "      <td>inf</td>\n",
       "      <td>1.250000</td>\n",
       "      <td>6.960000</td>\n",
       "      <td>7.510000</td>\n",
       "      <td>25.620000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>72.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Preço Atual  Liquidez Diária   Dividendo  DividendYield  \\\n",
       "count    243.000000       243.000000  243.000000     243.000000   \n",
       "mean     467.841852     15280.456790    5.390000       1.039959   \n",
       "std     5110.572838     44998.023384   59.116933       3.403459   \n",
       "min        0.000000         0.000000    0.000000       0.000000   \n",
       "25%       79.645000       215.000000    0.415000       0.425000   \n",
       "50%       97.000000      3195.000000    0.650000       0.670000   \n",
       "75%      109.800000     14237.000000    0.985000       0.820000   \n",
       "max    79700.050000    545749.000000  919.210000      36.320000   \n",
       "\n",
       "       DY (3M)Acumulado  DY (6M)Acumulado  DY (12M)Acumulado  DY (3M)Média  \\\n",
       "count        243.000000        243.000000         243.000000    243.000000   \n",
       "mean           2.116008          3.418395           5.406461      0.705432   \n",
       "std            3.546429          4.139285           5.860466      1.182217   \n",
       "min            0.000000          0.000000           0.000000      0.000000   \n",
       "25%            1.065000          1.045000           0.000000      0.355000   \n",
       "50%            1.830000          3.340000           5.800000      0.610000   \n",
       "75%            2.385000          4.420000           8.120000      0.795000   \n",
       "max           37.990000         40.960000          45.220000     12.660000   \n",
       "\n",
       "       DY (6M)Média  DY (12M)Média  ...  PatrimônioLíq.           VPA  P/VPA  \\\n",
       "count    243.000000     243.000000  ...    2.430000e+02    243.000000  243.0   \n",
       "mean       0.569630       0.450453  ...    4.057168e+08    597.471152    inf   \n",
       "std        0.689965       0.488266  ...    6.300817e+08   5744.776012    NaN   \n",
       "min        0.000000       0.000000  ...   -6.281734e+06     -8.280000 -155.0   \n",
       "25%        0.175000       0.000000  ...    4.817601e+07     92.490000   83.0   \n",
       "50%        0.560000       0.480000  ...    1.659057e+08     99.870000   95.0   \n",
       "75%        0.740000       0.680000  ...    4.734209e+08    109.645000  102.0   \n",
       "max        6.830000       3.770000  ...    3.850279e+09  87737.000000    inf   \n",
       "\n",
       "       DYPatrimonial  VariaçãoPatrimonial  Rentab. Patr.no Período  \\\n",
       "count     243.000000           243.000000               243.000000   \n",
       "mean        0.154198             0.049588                 0.204074   \n",
       "std         0.316948             0.732803                 0.841465   \n",
       "min         0.000000            -2.540000                -1.860000   \n",
       "25%         0.000000             0.000000                 0.000000   \n",
       "50%         0.000000             0.000000                 0.000000   \n",
       "75%         0.000000             0.000000                 0.000000   \n",
       "max         1.250000             6.960000                 7.510000   \n",
       "\n",
       "       Rentab. Patr.Acumulada  VacânciaFísica  VacânciaFinanceira  \\\n",
       "count              243.000000      243.000000          243.000000   \n",
       "mean                 0.588601        7.384486            1.310288   \n",
       "std                  2.339812       17.740402            8.271709   \n",
       "min                 -9.350000        0.000000            0.000000   \n",
       "25%                  0.000000        0.000000            0.000000   \n",
       "50%                  0.000000        0.000000            0.000000   \n",
       "75%                  0.000000        5.350000            0.000000   \n",
       "max                 25.620000      100.000000          100.000000   \n",
       "\n",
       "       QuantidadeAtivos  \n",
       "count        243.000000  \n",
       "mean           3.353909  \n",
       "std            7.944426  \n",
       "min            0.000000  \n",
       "25%            0.000000  \n",
       "50%            1.000000  \n",
       "75%            3.000000  \n",
       "max           72.000000  \n",
       "\n",
       "[8 rows x 24 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Dados de P/VPA tem atributos infinitos e está em uma escala diferente"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "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>Códigodo fundo</th>\n",
       "      <th>Setor</th>\n",
       "      <th>Preço Atual</th>\n",
       "      <th>Liquidez Diária</th>\n",
       "      <th>Dividendo</th>\n",
       "      <th>DividendYield</th>\n",
       "      <th>DY (3M)Acumulado</th>\n",
       "      <th>DY (6M)Acumulado</th>\n",
       "      <th>DY (12M)Acumulado</th>\n",
       "      <th>DY (3M)Média</th>\n",
       "      <th>...</th>\n",
       "      <th>PatrimônioLíq.</th>\n",
       "      <th>VPA</th>\n",
       "      <th>P/VPA</th>\n",
       "      <th>DYPatrimonial</th>\n",
       "      <th>VariaçãoPatrimonial</th>\n",
       "      <th>Rentab. Patr.no Período</th>\n",
       "      <th>Rentab. Patr.Acumulada</th>\n",
       "      <th>VacânciaFísica</th>\n",
       "      <th>VacânciaFinanceira</th>\n",
       "      <th>QuantidadeAtivos</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>RBCB11</td>\n",
       "      <td>Outros</td>\n",
       "      <td>19.0</td>\n",
       "      <td>161.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.29</td>\n",
       "      <td>4.38</td>\n",
       "      <td>14.62</td>\n",
       "      <td>0.1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>inf</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 26 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Códigodo fundo   Setor  Preço Atual  Liquidez Diária  Dividendo  \\\n",
       "7         RBCB11  Outros         19.0            161.0        0.0   \n",
       "\n",
       "   DividendYield  DY (3M)Acumulado  DY (6M)Acumulado  DY (12M)Acumulado  \\\n",
       "7            0.0              0.29              4.38              14.62   \n",
       "\n",
       "   DY (3M)Média  ...  PatrimônioLíq.  VPA  P/VPA  DYPatrimonial  \\\n",
       "7           0.1  ...             0.0  0.0    inf            0.0   \n",
       "\n",
       "   VariaçãoPatrimonial  Rentab. Patr.no Período  Rentab. Patr.Acumulada  \\\n",
       "7                  0.0                      0.0                     0.0   \n",
       "\n",
       "   VacânciaFísica  VacânciaFinanceira  QuantidadeAtivos  \n",
       "7             0.0                 0.0                 0  \n",
       "\n",
       "[1 rows x 26 columns]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Check infinity values pandas\n",
    "df[np.isinf(df[col_floats]).any(1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "idx = df[np.isinf(df[col_floats]).any(1)].index\n",
    "df.drop(idx, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['P/VPA'] = df['P/VPA']/100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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>Preço Atual</th>\n",
       "      <th>Liquidez Diária</th>\n",
       "      <th>Dividendo</th>\n",
       "      <th>DividendYield</th>\n",
       "      <th>DY (3M)Acumulado</th>\n",
       "      <th>DY (6M)Acumulado</th>\n",
       "      <th>DY (12M)Acumulado</th>\n",
       "      <th>DY (3M)Média</th>\n",
       "      <th>DY (6M)Média</th>\n",
       "      <th>DY (12M)Média</th>\n",
       "      <th>...</th>\n",
       "      <th>PatrimônioLíq.</th>\n",
       "      <th>VPA</th>\n",
       "      <th>P/VPA</th>\n",
       "      <th>DYPatrimonial</th>\n",
       "      <th>VariaçãoPatrimonial</th>\n",
       "      <th>Rentab. Patr.no Período</th>\n",
       "      <th>Rentab. Patr.Acumulada</th>\n",
       "      <th>VacânciaFísica</th>\n",
       "      <th>VacânciaFinanceira</th>\n",
       "      <th>QuantidadeAtivos</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>242.000000</td>\n",
       "      <td>242.000000</td>\n",
       "      <td>242.000000</td>\n",
       "      <td>242.000000</td>\n",
       "      <td>242.000000</td>\n",
       "      <td>242.000000</td>\n",
       "      <td>242.000000</td>\n",
       "      <td>242.000000</td>\n",
       "      <td>242.000000</td>\n",
       "      <td>242.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>2.420000e+02</td>\n",
       "      <td>242.000000</td>\n",
       "      <td>242.000000</td>\n",
       "      <td>242.000000</td>\n",
       "      <td>242.000000</td>\n",
       "      <td>242.000000</td>\n",
       "      <td>242.000000</td>\n",
       "      <td>242.000000</td>\n",
       "      <td>242.000000</td>\n",
       "      <td>242.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>469.696570</td>\n",
       "      <td>15342.933884</td>\n",
       "      <td>5.412273</td>\n",
       "      <td>1.044256</td>\n",
       "      <td>2.123554</td>\n",
       "      <td>3.414421</td>\n",
       "      <td>5.368388</td>\n",
       "      <td>0.707934</td>\n",
       "      <td>0.568967</td>\n",
       "      <td>0.447273</td>\n",
       "      <td>...</td>\n",
       "      <td>4.073933e+08</td>\n",
       "      <td>599.940041</td>\n",
       "      <td>0.907975</td>\n",
       "      <td>0.154835</td>\n",
       "      <td>0.049793</td>\n",
       "      <td>0.204917</td>\n",
       "      <td>0.591033</td>\n",
       "      <td>7.415000</td>\n",
       "      <td>1.315702</td>\n",
       "      <td>3.367769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>5121.082757</td>\n",
       "      <td>45080.720946</td>\n",
       "      <td>59.238433</td>\n",
       "      <td>3.409852</td>\n",
       "      <td>3.551824</td>\n",
       "      <td>4.147399</td>\n",
       "      <td>5.842421</td>\n",
       "      <td>1.184023</td>\n",
       "      <td>0.691318</td>\n",
       "      <td>0.486750</td>\n",
       "      <td>...</td>\n",
       "      <td>6.308442e+08</td>\n",
       "      <td>5756.553112</td>\n",
       "      <td>0.351794</td>\n",
       "      <td>0.317449</td>\n",
       "      <td>0.734315</td>\n",
       "      <td>0.843106</td>\n",
       "      <td>2.344353</td>\n",
       "      <td>17.770778</td>\n",
       "      <td>8.288421</td>\n",
       "      <td>7.957947</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>-6.281734e+06</td>\n",
       "      <td>-8.280000</td>\n",
       "      <td>-1.550000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-2.540000</td>\n",
       "      <td>-1.860000</td>\n",
       "      <td>-9.350000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>80.150000</td>\n",
       "      <td>235.000000</td>\n",
       "      <td>0.420000</td>\n",
       "      <td>0.432500</td>\n",
       "      <td>1.080000</td>\n",
       "      <td>0.977500</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.360000</td>\n",
       "      <td>0.162500</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>5.034971e+07</td>\n",
       "      <td>92.537500</td>\n",
       "      <td>0.830000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>97.050000</td>\n",
       "      <td>3195.500000</td>\n",
       "      <td>0.650000</td>\n",
       "      <td>0.670000</td>\n",
       "      <td>1.835000</td>\n",
       "      <td>3.335000</td>\n",
       "      <td>5.770000</td>\n",
       "      <td>0.610000</td>\n",
       "      <td>0.560000</td>\n",
       "      <td>0.480000</td>\n",
       "      <td>...</td>\n",
       "      <td>1.667646e+08</td>\n",
       "      <td>99.895000</td>\n",
       "      <td>0.950000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>110.205000</td>\n",
       "      <td>14251.000000</td>\n",
       "      <td>0.992500</td>\n",
       "      <td>0.825000</td>\n",
       "      <td>2.387500</td>\n",
       "      <td>4.420000</td>\n",
       "      <td>8.110000</td>\n",
       "      <td>0.797500</td>\n",
       "      <td>0.740000</td>\n",
       "      <td>0.680000</td>\n",
       "      <td>...</td>\n",
       "      <td>4.742794e+08</td>\n",
       "      <td>109.777500</td>\n",
       "      <td>1.020000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.375000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>3.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>79700.050000</td>\n",
       "      <td>545749.000000</td>\n",
       "      <td>919.210000</td>\n",
       "      <td>36.320000</td>\n",
       "      <td>37.990000</td>\n",
       "      <td>40.960000</td>\n",
       "      <td>45.220000</td>\n",
       "      <td>12.660000</td>\n",
       "      <td>6.830000</td>\n",
       "      <td>3.770000</td>\n",
       "      <td>...</td>\n",
       "      <td>3.850279e+09</td>\n",
       "      <td>87737.000000</td>\n",
       "      <td>4.040000</td>\n",
       "      <td>1.250000</td>\n",
       "      <td>6.960000</td>\n",
       "      <td>7.510000</td>\n",
       "      <td>25.620000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>72.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Preço Atual  Liquidez Diária   Dividendo  DividendYield  \\\n",
       "count    242.000000       242.000000  242.000000     242.000000   \n",
       "mean     469.696570     15342.933884    5.412273       1.044256   \n",
       "std     5121.082757     45080.720946   59.238433       3.409852   \n",
       "min        0.000000         0.000000    0.000000       0.000000   \n",
       "25%       80.150000       235.000000    0.420000       0.432500   \n",
       "50%       97.050000      3195.500000    0.650000       0.670000   \n",
       "75%      110.205000     14251.000000    0.992500       0.825000   \n",
       "max    79700.050000    545749.000000  919.210000      36.320000   \n",
       "\n",
       "       DY (3M)Acumulado  DY (6M)Acumulado  DY (12M)Acumulado  DY (3M)Média  \\\n",
       "count        242.000000        242.000000         242.000000    242.000000   \n",
       "mean           2.123554          3.414421           5.368388      0.707934   \n",
       "std            3.551824          4.147399           5.842421      1.184023   \n",
       "min            0.000000          0.000000           0.000000      0.000000   \n",
       "25%            1.080000          0.977500           0.000000      0.360000   \n",
       "50%            1.835000          3.335000           5.770000      0.610000   \n",
       "75%            2.387500          4.420000           8.110000      0.797500   \n",
       "max           37.990000         40.960000          45.220000     12.660000   \n",
       "\n",
       "       DY (6M)Média  DY (12M)Média  ...  PatrimônioLíq.           VPA  \\\n",
       "count    242.000000     242.000000  ...    2.420000e+02    242.000000   \n",
       "mean       0.568967       0.447273  ...    4.073933e+08    599.940041   \n",
       "std        0.691318       0.486750  ...    6.308442e+08   5756.553112   \n",
       "min        0.000000       0.000000  ...   -6.281734e+06     -8.280000   \n",
       "25%        0.162500       0.000000  ...    5.034971e+07     92.537500   \n",
       "50%        0.560000       0.480000  ...    1.667646e+08     99.895000   \n",
       "75%        0.740000       0.680000  ...    4.742794e+08    109.777500   \n",
       "max        6.830000       3.770000  ...    3.850279e+09  87737.000000   \n",
       "\n",
       "            P/VPA  DYPatrimonial  VariaçãoPatrimonial  \\\n",
       "count  242.000000     242.000000           242.000000   \n",
       "mean     0.907975       0.154835             0.049793   \n",
       "std      0.351794       0.317449             0.734315   \n",
       "min     -1.550000       0.000000            -2.540000   \n",
       "25%      0.830000       0.000000             0.000000   \n",
       "50%      0.950000       0.000000             0.000000   \n",
       "75%      1.020000       0.000000             0.000000   \n",
       "max      4.040000       1.250000             6.960000   \n",
       "\n",
       "       Rentab. Patr.no Período  Rentab. Patr.Acumulada  VacânciaFísica  \\\n",
       "count               242.000000              242.000000      242.000000   \n",
       "mean                  0.204917                0.591033        7.415000   \n",
       "std                   0.843106                2.344353       17.770778   \n",
       "min                  -1.860000               -9.350000        0.000000   \n",
       "25%                   0.000000                0.000000        0.000000   \n",
       "50%                   0.000000                0.000000        0.000000   \n",
       "75%                   0.000000                0.000000        5.375000   \n",
       "max                   7.510000               25.620000      100.000000   \n",
       "\n",
       "       VacânciaFinanceira  QuantidadeAtivos  \n",
       "count          242.000000        242.000000  \n",
       "mean             1.315702          3.367769  \n",
       "std              8.288421          7.957947  \n",
       "min              0.000000          0.000000  \n",
       "25%              0.000000          0.000000  \n",
       "50%              0.000000          1.000000  \n",
       "75%              0.000000          3.000000  \n",
       "max            100.000000         72.000000  \n",
       "\n",
       "[8 rows x 24 columns]"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Analisando a média por setor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Códigodo fundo', 'Setor', 'Preço Atual', 'Liquidez Diária',\n",
       "       'Dividendo', 'DividendYield', 'DY (3M)Acumulado', 'DY (6M)Acumulado',\n",
       "       'DY (12M)Acumulado', 'DY (3M)Média', 'DY (6M)Média', 'DY (12M)Média',\n",
       "       'DY Ano', 'Variação Preço', 'Rentab.Período', 'Rentab.Acumulada',\n",
       "       'PatrimônioLíq.', 'VPA', 'P/VPA', 'DYPatrimonial',\n",
       "       'VariaçãoPatrimonial', 'Rentab. Patr.no Período',\n",
       "       'Rentab. Patr.Acumulada', 'VacânciaFísica', 'VacânciaFinanceira',\n",
       "       'QuantidadeAtivos'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "indicadores = ['Códigodo fundo',\n",
    "               'Setor', \n",
    "               'DY (12M)Acumulado', \n",
    "               'VacânciaFísica', \n",
    "               'VacânciaFinanceira', \n",
    "               'P/VPA', \n",
    "               'QuantidadeAtivos', \n",
    "               'Liquidez Diária']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_aux = df[indicadores]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "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>Códigodo fundo</th>\n",
       "      <th>Setor</th>\n",
       "      <th>DY (12M)Acumulado</th>\n",
       "      <th>VacânciaFísica</th>\n",
       "      <th>VacânciaFinanceira</th>\n",
       "      <th>P/VPA</th>\n",
       "      <th>QuantidadeAtivos</th>\n",
       "      <th>Liquidez Diária</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>137</th>\n",
       "      <td>ABCP11</td>\n",
       "      <td>Shoppings</td>\n",
       "      <td>0.00</td>\n",
       "      <td>9.10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.84</td>\n",
       "      <td>1</td>\n",
       "      <td>2654.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>AFCR11</td>\n",
       "      <td>Híbrido</td>\n",
       "      <td>10.57</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.08</td>\n",
       "      <td>0</td>\n",
       "      <td>515.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>AFOF11</td>\n",
       "      <td>Títulos e Val. Mob.</td>\n",
       "      <td>16.82</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0</td>\n",
       "      <td>5826.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126</th>\n",
       "      <td>AIEC11</td>\n",
       "      <td>Lajes Corporativas</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.80</td>\n",
       "      <td>2</td>\n",
       "      <td>4416.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ALMI11</td>\n",
       "      <td>Lajes Corporativas</td>\n",
       "      <td>0.00</td>\n",
       "      <td>68.15</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.56</td>\n",
       "      <td>1</td>\n",
       "      <td>176.0</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>XPML11</td>\n",
       "      <td>Shoppings</td>\n",
       "      <td>3.56</td>\n",
       "      <td>5.90</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.98</td>\n",
       "      <td>13</td>\n",
       "      <td>44998.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>XPPR11</td>\n",
       "      <td>Outros</td>\n",
       "      <td>8.88</td>\n",
       "      <td>47.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.80</td>\n",
       "      <td>4</td>\n",
       "      <td>14574.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>171</th>\n",
       "      <td>XPSF11</td>\n",
       "      <td>Outros</td>\n",
       "      <td>9.28</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.01</td>\n",
       "      <td>0</td>\n",
       "      <td>15014.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>XTED11</td>\n",
       "      <td>Lajes Corporativas</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0.49</td>\n",
       "      <td>1</td>\n",
       "      <td>3560.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>YUFI11B</td>\n",
       "      <td>Residencial</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>242 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Códigodo fundo                Setor  DY (12M)Acumulado  VacânciaFísica  \\\n",
       "137         ABCP11            Shoppings               0.00            9.10   \n",
       "129         AFCR11              Híbrido              10.57            0.00   \n",
       "29          AFOF11  Títulos e Val. Mob.              16.82            0.00   \n",
       "126         AIEC11   Lajes Corporativas               0.00            0.00   \n",
       "3           ALMI11   Lajes Corporativas               0.00           68.15   \n",
       "..             ...                  ...                ...             ...   \n",
       "49          XPML11            Shoppings               3.56            5.90   \n",
       "71          XPPR11               Outros               8.88           47.00   \n",
       "171         XPSF11               Outros               9.28            0.00   \n",
       "2           XTED11   Lajes Corporativas               0.00            0.00   \n",
       "133        YUFI11B          Residencial               0.00            0.00   \n",
       "\n",
       "     VacânciaFinanceira  P/VPA  QuantidadeAtivos  Liquidez Diária  \n",
       "137                 0.0   0.84                 1           2654.0  \n",
       "129                 0.0   1.08                 0            515.0  \n",
       "29                  0.0   1.00                 0           5826.0  \n",
       "126                 0.0   0.80                 2           4416.0  \n",
       "3                   0.0   0.56                 1            176.0  \n",
       "..                  ...    ...               ...              ...  \n",
       "49                  0.0   0.98                13          44998.0  \n",
       "71                  0.0   0.80                 4          14574.0  \n",
       "171                 0.0   1.01                 0          15014.0  \n",
       "2                 100.0   0.49                 1           3560.0  \n",
       "133                 0.0   0.00                 0              0.0  \n",
       "\n",
       "[242 rows x 8 columns]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_aux"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "media_setor = df_aux.groupby('Setor').agg(['mean','std'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">DY (12M)Acumulado</th>\n",
       "      <th colspan=\"2\" halign=\"left\">VacânciaFísica</th>\n",
       "      <th colspan=\"2\" halign=\"left\">VacânciaFinanceira</th>\n",
       "      <th colspan=\"2\" halign=\"left\">P/VPA</th>\n",
       "      <th colspan=\"2\" halign=\"left\">QuantidadeAtivos</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Liquidez Diária</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Setor</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Hospital</th>\n",
       "      <td>5.030000</td>\n",
       "      <td>4.530530</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.193333</td>\n",
       "      <td>0.160104</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>316.000000</td>\n",
       "      <td>431.447563</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hotel</th>\n",
       "      <td>4.270000</td>\n",
       "      <td>3.829452</td>\n",
       "      <td>53.866667</td>\n",
       "      <td>46.656761</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.916667</td>\n",
       "      <td>0.102144</td>\n",
       "      <td>9.333333</td>\n",
       "      <td>12.096832</td>\n",
       "      <td>482.333333</td>\n",
       "      <td>751.466788</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Híbrido</th>\n",
       "      <td>4.390526</td>\n",
       "      <td>4.590571</td>\n",
       "      <td>3.865000</td>\n",
       "      <td>8.570495</td>\n",
       "      <td>1.267895</td>\n",
       "      <td>3.838064</td>\n",
       "      <td>0.810000</td>\n",
       "      <td>0.464048</td>\n",
       "      <td>3.736842</td>\n",
       "      <td>5.011793</td>\n",
       "      <td>31270.789474</td>\n",
       "      <td>103635.709337</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lajes Corporativas</th>\n",
       "      <td>6.211282</td>\n",
       "      <td>7.093434</td>\n",
       "      <td>26.343590</td>\n",
       "      <td>29.435617</td>\n",
       "      <td>4.621026</td>\n",
       "      <td>17.139423</td>\n",
       "      <td>0.879744</td>\n",
       "      <td>0.573886</td>\n",
       "      <td>4.205128</td>\n",
       "      <td>10.513599</td>\n",
       "      <td>4479.666667</td>\n",
       "      <td>8120.062984</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Logística</th>\n",
       "      <td>4.768000</td>\n",
       "      <td>2.913261</td>\n",
       "      <td>5.505000</td>\n",
       "      <td>13.695004</td>\n",
       "      <td>4.045000</td>\n",
       "      <td>14.496224</td>\n",
       "      <td>0.975000</td>\n",
       "      <td>0.137286</td>\n",
       "      <td>7.650000</td>\n",
       "      <td>5.382965</td>\n",
       "      <td>13766.250000</td>\n",
       "      <td>15651.514226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Outros</th>\n",
       "      <td>6.061143</td>\n",
       "      <td>5.182761</td>\n",
       "      <td>2.082857</td>\n",
       "      <td>8.197363</td>\n",
       "      <td>0.260000</td>\n",
       "      <td>1.225273</td>\n",
       "      <td>0.894286</td>\n",
       "      <td>0.327569</td>\n",
       "      <td>6.342857</td>\n",
       "      <td>14.356681</td>\n",
       "      <td>16073.285714</td>\n",
       "      <td>26320.257809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Residencial</th>\n",
       "      <td>6.377500</td>\n",
       "      <td>15.825424</td>\n",
       "      <td>4.262500</td>\n",
       "      <td>10.857905</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.996250</td>\n",
       "      <td>0.558491</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>1.488048</td>\n",
       "      <td>2251.875000</td>\n",
       "      <td>4948.055937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shoppings</th>\n",
       "      <td>1.540455</td>\n",
       "      <td>1.991569</td>\n",
       "      <td>10.975455</td>\n",
       "      <td>11.653826</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.764545</td>\n",
       "      <td>0.188218</td>\n",
       "      <td>4.272727</td>\n",
       "      <td>5.063236</td>\n",
       "      <td>8913.409091</td>\n",
       "      <td>14301.492202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Títulos e Val. Mob.</th>\n",
       "      <td>6.348108</td>\n",
       "      <td>5.311715</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.982703</td>\n",
       "      <td>0.095978</td>\n",
       "      <td>0.040541</td>\n",
       "      <td>0.198569</td>\n",
       "      <td>17508.081081</td>\n",
       "      <td>23091.171134</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    DY (12M)Acumulado            VacânciaFísica             \\\n",
       "                                 mean        std           mean        std   \n",
       "Setor                                                                        \n",
       "Hospital                     5.030000   4.530530       0.000000   0.000000   \n",
       "Hotel                        4.270000   3.829452      53.866667  46.656761   \n",
       "Híbrido                      4.390526   4.590571       3.865000   8.570495   \n",
       "Lajes Corporativas           6.211282   7.093434      26.343590  29.435617   \n",
       "Logística                    4.768000   2.913261       5.505000  13.695004   \n",
       "Outros                       6.061143   5.182761       2.082857   8.197363   \n",
       "Residencial                  6.377500  15.825424       4.262500  10.857905   \n",
       "Shoppings                    1.540455   1.991569      10.975455  11.653826   \n",
       "Títulos e Val. Mob.          6.348108   5.311715       0.000000   0.000000   \n",
       "\n",
       "                    VacânciaFinanceira                P/VPA            \\\n",
       "                                  mean        std      mean       std   \n",
       "Setor                                                                   \n",
       "Hospital                      0.000000   0.000000  1.193333  0.160104   \n",
       "Hotel                         0.000000   0.000000  0.916667  0.102144   \n",
       "Híbrido                       1.267895   3.838064  0.810000  0.464048   \n",
       "Lajes Corporativas            4.621026  17.139423  0.879744  0.573886   \n",
       "Logística                     4.045000  14.496224  0.975000  0.137286   \n",
       "Outros                        0.260000   1.225273  0.894286  0.327569   \n",
       "Residencial                   0.000000   0.000000  0.996250  0.558491   \n",
       "Shoppings                     0.000000   0.000000  0.764545  0.188218   \n",
       "Títulos e Val. Mob.           0.000000   0.000000  0.982703  0.095978   \n",
       "\n",
       "                    QuantidadeAtivos            Liquidez Diária                 \n",
       "                                mean        std            mean            std  \n",
       "Setor                                                                           \n",
       "Hospital                    1.000000   0.000000      316.000000     431.447563  \n",
       "Hotel                       9.333333  12.096832      482.333333     751.466788  \n",
       "Híbrido                     3.736842   5.011793    31270.789474  103635.709337  \n",
       "Lajes Corporativas          4.205128  10.513599     4479.666667    8120.062984  \n",
       "Logística                   7.650000   5.382965    13766.250000   15651.514226  \n",
       "Outros                      6.342857  14.356681    16073.285714   26320.257809  \n",
       "Residencial                 0.750000   1.488048     2251.875000    4948.055937  \n",
       "Shoppings                   4.272727   5.063236     8913.409091   14301.492202  \n",
       "Títulos e Val. Mob.         0.040541   0.198569    17508.081081   23091.171134  "
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "media_setor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6.3774999999999995"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "media_setor.loc['Residencial', ('DY (12M)Acumulado', 'mean')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Criando uma função com uma estratégia para oportunidades do mercado"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "def oportunidade_media_setor(df, setor='Shoppings', label_setor='Setor'):\n",
    "    \n",
    "    media_setor = df_aux.groupby('Setor').agg(['mean','std'])\n",
    "    \n",
    "    df_setor = df[df[label_setor].isin([setor])]\n",
    "    \n",
    "    filter_ = \\\n",
    "            (df_setor['QuantidadeAtivos'] > 5) &\\\n",
    "            (df_setor['Liquidez Diária'] > 5000) &\\\n",
    "            (df_setor['P/VPA'] < 1.0) &\\\n",
    "            (df_setor['DY (12M)Acumulado'] > media_setor.loc[setor, ('DY (12M)Acumulado','mean')]) \n",
    "            \n",
    "    print('média do setor Yield: {}'.format(media_setor.loc[setor, ('DY (12M)Acumulado','mean')]))\n",
    "    print('média do setor p/VPA: {}'.format(media_setor.loc[setor, ('P/VPA','mean')]))\n",
    "    print('média do setor Ativos: {}'.format(media_setor.loc[setor, ('QuantidadeAtivos','mean')]))\n",
    "    \n",
    "    return df_setor[filter_]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Shoppings',\n",
       " 'Híbrido',\n",
       " 'Títulos e Val. Mob.',\n",
       " 'Lajes Corporativas',\n",
       " 'Logística',\n",
       " 'Outros',\n",
       " 'Residencial',\n",
       " 'Hospital',\n",
       " 'Hotel']"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(df['Setor'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "média do setor Yield: 4.768\n",
      "média do setor p/VPA: 0.975\n",
      "média do setor Ativos: 7.65\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Códigodo fundo</th>\n",
       "      <th>Setor</th>\n",
       "      <th>DY (12M)Acumulado</th>\n",
       "      <th>VacânciaFísica</th>\n",
       "      <th>VacânciaFinanceira</th>\n",
       "      <th>P/VPA</th>\n",
       "      <th>QuantidadeAtivos</th>\n",
       "      <th>Liquidez Diária</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>204</th>\n",
       "      <td>RBRL11</td>\n",
       "      <td>Logística</td>\n",
       "      <td>7.48</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.99</td>\n",
       "      <td>7</td>\n",
       "      <td>10608.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td>GGRC11</td>\n",
       "      <td>Logística</td>\n",
       "      <td>6.50</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.99</td>\n",
       "      <td>15</td>\n",
       "      <td>13350.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>LVBI11</td>\n",
       "      <td>Logística</td>\n",
       "      <td>5.43</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.95</td>\n",
       "      <td>9</td>\n",
       "      <td>22037.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Códigodo fundo      Setor  DY (12M)Acumulado  VacânciaFísica  \\\n",
       "204         RBRL11  Logística               7.48             0.0   \n",
       "190         GGRC11  Logística               6.50             0.0   \n",
       "73          LVBI11  Logística               5.43             1.0   \n",
       "\n",
       "     VacânciaFinanceira  P/VPA  QuantidadeAtivos  Liquidez Diária  \n",
       "204                 0.0   0.99                 7          10608.0  \n",
       "190                 0.0   0.99                15          13350.0  \n",
       "73                  0.0   0.95                 9          22037.0  "
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "oportunidade = oportunidade_media_setor(df_aux, setor='Logística')\n",
    "oportunidade.sort_values('DY (12M)Acumulado', ascending=False, inplace=True)\n",
    "oportunidade"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}