acao_2_tratar_dados_fundamentus_raw.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "aafa92e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Bibliotecas\n",
    "import re\n",
    "import unicodedata\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "id": "bac47e58",
   "metadata": {},
   "outputs": [],
   "source": [
    "def converter_string_numero(x):\n",
    "    '''\n",
    "    \n",
    "    '''\n",
    "    if type(x) == str:\n",
    "        xo = x\n",
    "        # Aplicar substituições padrões\n",
    "        x = x.replace(',','.').replace('%', '')\n",
    "        # Garantir que tenhamos um número e nenhuma letra\n",
    "        if bool(re.search(r'\\d', x)) is True and bool(re.search(r'[a-zA-Z]', x)) is False:\n",
    "            # Remover problema de excesso de pontos\n",
    "            if len(x.split('.')) > 1:\n",
    "                x = ''.join(x.split('.')[:-1]) + '.' + x.split('.')[-1]\n",
    "            # Report\n",
    "            #print(f'> De {xo} para {x} que é numérico.')\n",
    "            # Retorno\n",
    "            return float(x)\n",
    "        else:\n",
    "            return np.nan\n",
    "    else:\n",
    "        return x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "id": "a2e3c954",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Carregar dados brutos\n",
    "df = pd.read_csv('dados/1_EXT/raw_fundamentus_2023-11-02.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "id": "7461eea8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fazer tratamentos iniciais\n",
    "df.columns = [c.upper().replace('?','').strip() for c in df.columns] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "id": "008c8830",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Colunas para derrubar\n",
    "cols_to_drop = ['EBIT',\n",
    "                'DEPÓSITOS',\n",
    "                'OSCILAÇÕES',\n",
    "                'UNNAMED: 25',\n",
    "                'REC SERVIÇOS',\n",
    "                'LUCRO LÍQUIDO',\n",
    "                'ÚLTIMOS 3 MESES',\n",
    "                'RECEITA LÍQUIDA',\n",
    "                'CART. DE CRÉDITO',\n",
    "                'ÚLTIMOS 12 MESES',\n",
    "                'RESULT INT FINANC',\n",
    "                'DADOS BALANÇO PATRIMONIAL',\n",
    "                'INDICADORES FUNDAMENTALISTAS',\n",
    "                'DADOS DEMONSTRATIVOS DE RESULTADOS']\n",
    "df.drop(columns=cols_to_drop, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "id": "d72d0773",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Renomear colunas\n",
    "df.rename(columns={'DIA': 'OSCILACOES_%_DIA',\n",
    "                   'MES': 'OSCILACOES_%_MES',\n",
    "                   '30 DIAS': 'OSCILACOES_%_30_DIAS',\n",
    "                   '12 MESES': 'OSCILACOES_%_12_MESES',\n",
    "                   '2023': 'OSCILACOES_%_2023',\n",
    "                   '2022': 'OSCILACOES_%_2022',\n",
    "                   '2021': 'OSCILACOES_%_2021',\n",
    "                   '2020': 'OSCILACOES_%_2020',\n",
    "                   '2019': 'OSCILACOES_%_2019',\n",
    "                   '2018': 'OSCILACOES_%_2018',\n",
    "                   '2017': 'OSCILACOES_%_2017',\n",
    "                   '2016': 'OSCILACOES_%_2016'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "id": "79349fc6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Fazer tratamentos adicionais\n",
    "df.columns = [re.sub('\\s+', ' ', c) for c in df.columns]\n",
    "df.columns = [c.replace('. ', '_') for c in df.columns]\n",
    "df.columns = [c.replace(' / ', '_POR_') for c in df.columns]\n",
    "df.columns = [c.replace('/', '_POR_') for c in df.columns]\n",
    "df.columns = [c.replace(' ', '_') for c in df.columns]\n",
    "df.columns = [c.replace('(', '') for c in df.columns]\n",
    "df.columns = [c.replace(')', '') for c in df.columns]\n",
    "df.columns = [unicodedata.normalize('NFD', c).encode('ascii', 'ignore').decode(\"utf-8\")  for c in df.columns]\n",
    "df.columns = [re.sub('_+', '_', c) for c in df.columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "id": "4348b728",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Colunas de atributos\n",
    "cols_atr = ['PAPEL', 'TIPO', 'EMPRESA', 'SETOR', 'SUBSETOR', 'DATA_ULT_COT', 'ULT_BALANCO_PROCESSADO']\n",
    "cols_num = [c for c in df.columns if c not in cols_atr] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "id": "917b82cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Criar backup para comparar\n",
    "df_bkp = df.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "id": "825d6b9b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Converter colunas numericas\n",
    "for cn in cols_num:\n",
    "    df[cn] = df[cn].apply(lambda x:converter_string_numero(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "id": "0a4a7651",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 450 entries, 0 to 449\n",
      "Data columns (total 47 columns):\n",
      " #   Column                 Non-Null Count  Dtype  \n",
      "---  ------                 --------------  -----  \n",
      " 0   COTACAO                450 non-null    float64\n",
      " 1   MIN_52_SEM             450 non-null    float64\n",
      " 2   MAX_52_SEM             450 non-null    float64\n",
      " 3   VOL_$_MED_2M           450 non-null    int64  \n",
      " 4   VALOR_DE_MERCADO       450 non-null    int64  \n",
      " 5   VALOR_DA_FIRMA         409 non-null    float64\n",
      " 6   NRO_ACOES              450 non-null    int64  \n",
      " 7   OSCILACOES_%_DIA       450 non-null    float64\n",
      " 8   MES                    450 non-null    float64\n",
      " 9   OSCILACOES_%_30_DIAS   450 non-null    float64\n",
      " 10  OSCILACOES_%_12_MESES  450 non-null    float64\n",
      " 11  OSCILACOES_%_2023      447 non-null    float64\n",
      " 12  OSCILACOES_%_2022      449 non-null    float64\n",
      " 13  OSCILACOES_%_2021      450 non-null    float64\n",
      " 14  OSCILACOES_%_2020      450 non-null    float64\n",
      " 15  OSCILACOES_%_2019      450 non-null    float64\n",
      " 16  OSCILACOES_%_2018      450 non-null    float64\n",
      " 17  P_POR_L                450 non-null    float64\n",
      " 18  P_POR_VP               450 non-null    float64\n",
      " 19  P_POR_EBIT             413 non-null    float64\n",
      " 20  PSR                    401 non-null    float64\n",
      " 21  P_POR_ATIVOS           413 non-null    float64\n",
      " 22  P_POR_CAP_GIRO         409 non-null    float64\n",
      " 23  P_POR_ATIV_CIRC_LIQ    409 non-null    float64\n",
      " 24  DIV_YIELD              450 non-null    float64\n",
      " 25  EV_POR_EBITDA          409 non-null    float64\n",
      " 26  EV_POR_EBIT            409 non-null    float64\n",
      " 27  CRES_REC_5A            439 non-null    float64\n",
      " 28  LPA                    450 non-null    float64\n",
      " 29  VPA                    450 non-null    float64\n",
      " 30  MARG_BRUTA             401 non-null    float64\n",
      " 31  MARG_EBIT              401 non-null    float64\n",
      " 32  MARG_LIQUIDA           449 non-null    float64\n",
      " 33  EBIT_POR_ATIVO         450 non-null    float64\n",
      " 34  ROIC                   409 non-null    float64\n",
      " 35  ROE                    449 non-null    float64\n",
      " 36  LIQUIDEZ_CORR          408 non-null    float64\n",
      " 37  DIV_BR_POR_PATRIM      374 non-null    float64\n",
      " 38  GIRO_ATIVOS            401 non-null    float64\n",
      " 39  ATIVO                  450 non-null    int64  \n",
      " 40  DISPONIBILIDADES       413 non-null    float64\n",
      " 41  ATIVO_CIRCULANTE       413 non-null    float64\n",
      " 42  DIV_BRUTA              413 non-null    float64\n",
      " 43  DIV_LIQUIDA            413 non-null    float64\n",
      " 44  PATRIM_LIQ             450 non-null    int64  \n",
      " 45  OSCILACOES_%_2017      3 non-null      float64\n",
      " 46  OSCILACOES_%_2016      1 non-null      float64\n",
      "dtypes: float64(42), int64(5)\n",
      "memory usage: 165.4 KB\n"
     ]
    }
   ],
   "source": [
    "# Checar tipos\n",
    "df[cols_num].info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "id": "184965aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Salvar\n",
    "df.to_csv('dados/2_TRA/fundamentus_2023-11-02.csv', index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}