Extração_CAGED.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "UPAqpoPjM9Gk"
   },
   "source": [
    "# Preparação"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Bibliotecas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "2Izy_c8lGM-l",
    "outputId": "8c90aa31-3f39-45bb-a820-b9c260e9b110"
   },
   "outputs": [],
   "source": [
    "\n",
    "import pandas as pd\n",
    "\n",
    "import os\n",
    "from os import remove\n",
    "\n",
    "# !pip install wget\n",
    "import wget\n",
    "\n",
    "# !pip install py7zr\n",
    "from py7zr import SevenZipFile\n",
    "\n",
    "import glob"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Diretório"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'c:\\\\Users\\\\João\\\\Desktop\\\\Portfólio\\\\CAGED'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%pwd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "id": "U1fUcfCAGwXh"
   },
   "outputs": [],
   "source": [
    "# Ajustando diretório de trabalho\n",
    "pwd = os.getcwd()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "yEXom9FiNCmy"
   },
   "source": [
    "# Download e Extração dos arquivos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Funções"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "id": "6SfSDnvNKBac"
   },
   "outputs": [],
   "source": [
    "def extract_caged (ano, mes):\n",
    "  \"\"\" Faz o download dos arquivos do NOVO CAGED para o ano e o mês especificados.\n",
    "  O período do NOVO CAGED tem início em Janeiro de 2020.\n",
    "  Nesse script não extraímos os microdados antriores a esse período devido a divergências metodológicas estabelcidas pelo MTE.\n",
    "\n",
    "  Argumentos:\n",
    "      ano: Ano do arquivo.\n",
    "      mes: Mês do arquivo.\n",
    "  \"\"\"\n",
    "  # Download\n",
    "  if ano < 2020:\n",
    "        print(f\"Os microdados de {mes}/{ano} não serão baixados.\")\n",
    "        return\n",
    "\n",
    "  tipos_arquivos = ['CAGEDMOV', 'CAGEDEXC', 'CAGEDFOR']\n",
    "\n",
    "  for tipo in tipos_arquivos:\n",
    "    url = f'ftp://ftp.mtps.gov.br/pdet/microdados/NOVO CAGED/{ano}/{ano}{mes}/{tipo}{ano}{mes}.7z'\n",
    "    nome_arquivo = f'{tipo}{ano}{mes}.7z'\n",
    "\n",
    "    try:\n",
    "      print(f'\\nBaixando {tipo} de {mes}/{ano}...')\n",
    "      wget.download(url, nome_arquivo)\n",
    "      print(f'\\n{tipo}{ano}{mes}.7z baixado com sucesso.')\n",
    "    except:\n",
    "      print(f'\\nOs microdados {tipo} de {mes}/{ano} não estão disponíveis.')\n",
    "      continue\n",
    "\n",
    "    # Extração do arquivo 7z\n",
    "    try:\n",
    "        with SevenZipFile(nome_arquivo, mode='r') as archive:\n",
    "             archive.extractall()\n",
    "             print(f'{tipo}{ano}{mes}.txt extraído com sucesso.')\n",
    "    except:\n",
    "        print(f'Erro ao extrair {tipo}{ano}{mes}.7z.')\n",
    "        continue\n",
    "\n",
    "    # Remoção do arquivo 7z após a extração\n",
    "    try:\n",
    "        remove(nome_arquivo)\n",
    "        print(f'{nome_arquivo} removido.\\n')\n",
    "    except:\n",
    "        print(f'Erro ao remover {nome_arquivo}.')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extração"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "cbovT9RSNOYa"
   },
   "outputs": [],
   "source": [
    "# Extraindo arquivos para todo o período\n",
    "\n",
    "# Definir meses de janeiro a dezembro\n",
    "meses = [f'{i:02}' for i in range(1, 13)]\n",
    "\n",
    "# Loop\n",
    "for ano in range(2020, 2025):\n",
    "    for mes in meses:\n",
    "        extract_caged(ano, mes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "0uwffAxQdsMO",
    "outputId": "43ec350b-2e8b-4a2f-a53b-d31e992a3a22"
   },
   "outputs": [],
   "source": [
    "# Extraindo arquivos de um mês específico\n",
    "extract_caged(2024, 11)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Leitura"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Abrindo dicionários"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1361 entries, 0 to 1360\n",
      "Data columns (total 6 columns):\n",
      " #   Column          Non-Null Count  Dtype \n",
      "---  ------          --------------  ----- \n",
      " 0   Seção           1361 non-null   object\n",
      " 1   Classe          1361 non-null   int64 \n",
      " 2   Subclasse       1361 non-null   int64 \n",
      " 3   Nome Subclasse  1361 non-null   object\n",
      " 4   Nome Classe     1361 non-null   object\n",
      " 5   Nome Seção      1361 non-null   object\n",
      "dtypes: int64(2), object(4)\n",
      "memory usage: 63.9+ KB\n",
      "None\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>Seção</th>\n",
       "      <th>Classe</th>\n",
       "      <th>Subclasse</th>\n",
       "      <th>Nome Subclasse</th>\n",
       "      <th>Nome Classe</th>\n",
       "      <th>Nome Seção</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>1113</td>\n",
       "      <td>111301</td>\n",
       "      <td>Cultivo de arroz</td>\n",
       "      <td>Cultivo de cereais</td>\n",
       "      <td>AGRICULTURA, PECUÁRIA, PRODUÇÃO FLORESTAL, PES...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>1113</td>\n",
       "      <td>111302</td>\n",
       "      <td>Cultivo de milho</td>\n",
       "      <td>Cultivo de cereais</td>\n",
       "      <td>AGRICULTURA, PECUÁRIA, PRODUÇÃO FLORESTAL, PES...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>1113</td>\n",
       "      <td>111303</td>\n",
       "      <td>Cultivo de trigo</td>\n",
       "      <td>Cultivo de cereais</td>\n",
       "      <td>AGRICULTURA, PECUÁRIA, PRODUÇÃO FLORESTAL, PES...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>1113</td>\n",
       "      <td>111399</td>\n",
       "      <td>Cultivo de outros cereais não especificados an...</td>\n",
       "      <td>Cultivo de cereais</td>\n",
       "      <td>AGRICULTURA, PECUÁRIA, PRODUÇÃO FLORESTAL, PES...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>1121</td>\n",
       "      <td>112101</td>\n",
       "      <td>Cultivo de algodão herbáceo</td>\n",
       "      <td>Cultivo de algodão herbáceo e de outras fibras...</td>\n",
       "      <td>AGRICULTURA, PECUÁRIA, PRODUÇÃO FLORESTAL, PES...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Seção  Classe  Subclasse                                     Nome Subclasse  \\\n",
       "0     A    1113     111301                                   Cultivo de arroz   \n",
       "1     A    1113     111302                                   Cultivo de milho   \n",
       "2     A    1113     111303                                   Cultivo de trigo   \n",
       "3     A    1113     111399  Cultivo de outros cereais não especificados an...   \n",
       "4     A    1121     112101                        Cultivo de algodão herbáceo   \n",
       "\n",
       "                                         Nome Classe  \\\n",
       "0                                 Cultivo de cereais   \n",
       "1                                 Cultivo de cereais   \n",
       "2                                 Cultivo de cereais   \n",
       "3                                 Cultivo de cereais   \n",
       "4  Cultivo de algodão herbáceo e de outras fibras...   \n",
       "\n",
       "                                          Nome Seção  \n",
       "0  AGRICULTURA, PECUÁRIA, PRODUÇÃO FLORESTAL, PES...  \n",
       "1  AGRICULTURA, PECUÁRIA, PRODUÇÃO FLORESTAL, PES...  \n",
       "2  AGRICULTURA, PECUÁRIA, PRODUÇÃO FLORESTAL, PES...  \n",
       "3  AGRICULTURA, PECUÁRIA, PRODUÇÃO FLORESTAL, PES...  \n",
       "4  AGRICULTURA, PECUÁRIA, PRODUÇÃO FLORESTAL, PES...  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dic_classes = pd.read_excel(pwd + \"\\\\entrada\\\\dicionários.xlsx\",\n",
    "                           sheet_name=0)\n",
    "\n",
    "print(dic_classes.info())\n",
    "dic_classes.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cod</th>\n",
       "      <th>nom</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>10 a 14 anos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>15 a 17 anos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>18 a 24 anos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>25 a 29 anos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>30 a 39 anos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>40 a 49 anos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>50 a 64 anos</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>65 anos ou mais</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>99</td>\n",
       "      <td>Não Informado</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cod              nom\n",
       "0    1     10 a 14 anos\n",
       "1    2     15 a 17 anos\n",
       "2    3     18 a 24 anos\n",
       "3    4     25 a 29 anos\n",
       "4    5     30 a 39 anos\n",
       "5    6     40 a 49 anos\n",
       "6    7     50 a 64 anos\n",
       "7    8  65 anos ou mais\n",
       "8   99    Não Informado"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dic_faixaeta = pd.read_excel(pwd + \"\\\\entrada\\\\dicionários.xlsx\",\n",
    "                           sheet_name=1)\n",
    "\n",
    "dic_faixaeta.head(n=9)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cod</th>\n",
       "      <th>nom</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Analfabeto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Ate 5 Incompleto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>5 Completo Fundamental</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>6 a 9 Fundamental</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Fundamental Completo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Medio Incompleto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>Medio Completo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>Superior Incompleto</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>Superior Completo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>Mestrado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>11</td>\n",
       "      <td>Doutorado</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>80</td>\n",
       "      <td>Pos Graduacao completa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>99</td>\n",
       "      <td>Nao Identificado</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    cod                     nom\n",
       "0     1              Analfabeto\n",
       "1     2        Ate 5 Incompleto\n",
       "2     3  5 Completo Fundamental\n",
       "3     4       6 a 9 Fundamental\n",
       "4     5    Fundamental Completo\n",
       "5     6        Medio Incompleto\n",
       "6     7          Medio Completo\n",
       "7     8     Superior Incompleto\n",
       "8     9       Superior Completo\n",
       "9    10                Mestrado\n",
       "10   11               Doutorado\n",
       "11   80  Pos Graduacao completa\n",
       "12   99        Nao Identificado"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dic_escolaridade = pd.read_excel(pwd + \"\\\\entrada\\\\dicionários.xlsx\",\n",
    "                           sheet_name=2)\n",
    "\n",
    "dic_escolaridade.head(n=13)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cod</th>\n",
       "      <th>nom</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Homem</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>Mulher</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>Nao Identificado</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cod               nom\n",
       "0    1             Homem\n",
       "1    3            Mulher\n",
       "2    9  Nao Identificado"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dic_sexo = pd.read_excel(pwd + \"\\\\entrada\\\\dicionários.xlsx\",\n",
    "                           sheet_name=3)\n",
    "\n",
    "dic_sexo.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cod</th>\n",
       "      <th>nom</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Branca</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Preta</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Parda</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Amarela</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Indigena</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>Nao Informada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>9</td>\n",
       "      <td>Nao Identificado</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   cod               nom\n",
       "0    1            Branca\n",
       "1    2             Preta\n",
       "2    3             Parda\n",
       "3    4           Amarela\n",
       "4    5          Indigena\n",
       "5    6     Nao Informada\n",
       "6    9  Nao Identificado"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dic_racacor = pd.read_excel(pwd + \"\\\\entrada\\\\dicionários.xlsx\",\n",
    "                           sheet_name=4)\n",
    "\n",
    "dic_racacor.head(n = 7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 5570 entries, 0 to 5569\n",
      "Data columns (total 13 columns):\n",
      " #   Column                                Non-Null Count  Dtype \n",
      "---  ------                                --------------  ----- \n",
      " 0   UF                                    5570 non-null   int64 \n",
      " 1   Nome_UF                               5570 non-null   object\n",
      " 2   Região Geográfica Intermediária       5570 non-null   int64 \n",
      " 3   Nome Região Geográfica Intermediária  5570 non-null   object\n",
      " 4   Região Geográfica Imediata            5570 non-null   int64 \n",
      " 5   Nome Região Geográfica Imediata       5570 non-null   object\n",
      " 6   Mesorregião Geográfica                5570 non-null   int64 \n",
      " 7   Nome_Mesorregião                      5570 non-null   object\n",
      " 8   Microrregião Geográfica               5570 non-null   int64 \n",
      " 9   Nome_Microrregião                     5570 non-null   object\n",
      " 10  Município                             5570 non-null   int64 \n",
      " 11  Código Município Completo             5570 non-null   int64 \n",
      " 12  Nome_Município                        5570 non-null   object\n",
      "dtypes: int64(7), object(6)\n",
      "memory usage: 565.8+ KB\n",
      "None\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>UF</th>\n",
       "      <th>Nome_UF</th>\n",
       "      <th>Região Geográfica Intermediária</th>\n",
       "      <th>Nome Região Geográfica Intermediária</th>\n",
       "      <th>Região Geográfica Imediata</th>\n",
       "      <th>Nome Região Geográfica Imediata</th>\n",
       "      <th>Mesorregião Geográfica</th>\n",
       "      <th>Nome_Mesorregião</th>\n",
       "      <th>Microrregião Geográfica</th>\n",
       "      <th>Nome_Microrregião</th>\n",
       "      <th>Município</th>\n",
       "      <th>Código Município Completo</th>\n",
       "      <th>Nome_Município</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>11</td>\n",
       "      <td>Rondônia</td>\n",
       "      <td>1102</td>\n",
       "      <td>Ji-Paraná</td>\n",
       "      <td>110005</td>\n",
       "      <td>Cacoal</td>\n",
       "      <td>2</td>\n",
       "      <td>Leste Rondoniense</td>\n",
       "      <td>6</td>\n",
       "      <td>Cacoal</td>\n",
       "      <td>15</td>\n",
       "      <td>1100015</td>\n",
       "      <td>Alta Floresta D'Oeste</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td>Rondônia</td>\n",
       "      <td>1102</td>\n",
       "      <td>Ji-Paraná</td>\n",
       "      <td>110005</td>\n",
       "      <td>Cacoal</td>\n",
       "      <td>2</td>\n",
       "      <td>Leste Rondoniense</td>\n",
       "      <td>6</td>\n",
       "      <td>Cacoal</td>\n",
       "      <td>379</td>\n",
       "      <td>1100379</td>\n",
       "      <td>Alto Alegre dos Parecis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11</td>\n",
       "      <td>Rondônia</td>\n",
       "      <td>1101</td>\n",
       "      <td>Porto Velho</td>\n",
       "      <td>110002</td>\n",
       "      <td>Ariquemes</td>\n",
       "      <td>2</td>\n",
       "      <td>Leste Rondoniense</td>\n",
       "      <td>3</td>\n",
       "      <td>Ariquemes</td>\n",
       "      <td>403</td>\n",
       "      <td>1100403</td>\n",
       "      <td>Alto Paraíso</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>11</td>\n",
       "      <td>Rondônia</td>\n",
       "      <td>1102</td>\n",
       "      <td>Ji-Paraná</td>\n",
       "      <td>110004</td>\n",
       "      <td>Ji-Paraná</td>\n",
       "      <td>2</td>\n",
       "      <td>Leste Rondoniense</td>\n",
       "      <td>5</td>\n",
       "      <td>Alvorada D'Oeste</td>\n",
       "      <td>346</td>\n",
       "      <td>1100346</td>\n",
       "      <td>Alvorada D'Oeste</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>11</td>\n",
       "      <td>Rondônia</td>\n",
       "      <td>1101</td>\n",
       "      <td>Porto Velho</td>\n",
       "      <td>110002</td>\n",
       "      <td>Ariquemes</td>\n",
       "      <td>2</td>\n",
       "      <td>Leste Rondoniense</td>\n",
       "      <td>3</td>\n",
       "      <td>Ariquemes</td>\n",
       "      <td>23</td>\n",
       "      <td>1100023</td>\n",
       "      <td>Ariquemes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   UF   Nome_UF  Região Geográfica Intermediária  \\\n",
       "0  11  Rondônia                             1102   \n",
       "1  11  Rondônia                             1102   \n",
       "2  11  Rondônia                             1101   \n",
       "3  11  Rondônia                             1102   \n",
       "4  11  Rondônia                             1101   \n",
       "\n",
       "  Nome Região Geográfica Intermediária  Região Geográfica Imediata  \\\n",
       "0                            Ji-Paraná                      110005   \n",
       "1                            Ji-Paraná                      110005   \n",
       "2                          Porto Velho                      110002   \n",
       "3                            Ji-Paraná                      110004   \n",
       "4                          Porto Velho                      110002   \n",
       "\n",
       "  Nome Região Geográfica Imediata  Mesorregião Geográfica   Nome_Mesorregião  \\\n",
       "0                          Cacoal                       2  Leste Rondoniense   \n",
       "1                          Cacoal                       2  Leste Rondoniense   \n",
       "2                       Ariquemes                       2  Leste Rondoniense   \n",
       "3                       Ji-Paraná                       2  Leste Rondoniense   \n",
       "4                       Ariquemes                       2  Leste Rondoniense   \n",
       "\n",
       "   Microrregião Geográfica Nome_Microrregião  Município  \\\n",
       "0                        6            Cacoal         15   \n",
       "1                        6            Cacoal        379   \n",
       "2                        3         Ariquemes        403   \n",
       "3                        5  Alvorada D'Oeste        346   \n",
       "4                        3         Ariquemes         23   \n",
       "\n",
       "   Código Município Completo           Nome_Município  \n",
       "0                    1100015    Alta Floresta D'Oeste  \n",
       "1                    1100379  Alto Alegre dos Parecis  \n",
       "2                    1100403             Alto Paraíso  \n",
       "3                    1100346         Alvorada D'Oeste  \n",
       "4                    1100023                Ariquemes  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dic_municipios = pd.read_excel(pwd + \"\\\\entrada\\\\dicionários.xlsx\",\n",
    "                           sheet_name=5)\n",
    "\n",
    "print(dic_municipios.info())\n",
    "dic_municipios.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Leitura, Processamento e Unificação"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "teste_1 = pd.read_csv(pwd + \"\\\\entrada\\\\Dados\\\\CAGEDMOV202411.txt\", sep = ';')\n",
    "teste_2 = pd.read_csv(pwd + \"\\\\entrada\\\\Dados\\\\CAGEDEXC202411.txt\", sep = ';')\n",
    "teste_3 = pd.read_csv(pwd + \"\\\\entrada\\\\Dados\\\\CAGEDFOR202411.txt\", sep = ';')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['competênciamov', 'região', 'uf', 'município', 'seção', 'subclasse',\n",
      "       'saldomovimentação', 'cbo2002ocupação', 'categoria', 'graudeinstrução',\n",
      "       'idade', 'horascontratuais', 'raçacor', 'sexo', 'tipoempregador',\n",
      "       'tipoestabelecimento', 'tipomovimentação', 'tipodedeficiência',\n",
      "       'indtrabintermitente', 'indtrabparcial', 'salário', 'tamestabjan',\n",
      "       'indicadoraprendiz', 'origemdainformação', 'competênciadec',\n",
      "       'indicadordeforadoprazo', 'unidadesaláriocódigo', 'valorsaláriofixo'],\n",
      "      dtype='object')\n",
      "Index(['competênciamov', 'região', 'uf', 'município', 'seção', 'subclasse',\n",
      "       'saldomovimentação', 'cbo2002ocupação', 'categoria', 'graudeinstrução',\n",
      "       'idade', 'horascontratuais', 'raçacor', 'sexo', 'tipoempregador',\n",
      "       'tipoestabelecimento', 'tipomovimentação', 'tipodedeficiência',\n",
      "       'indtrabintermitente', 'indtrabparcial', 'salário', 'tamestabjan',\n",
      "       'indicadoraprendiz', 'origemdainformação', 'competênciadec',\n",
      "       'competênciaexc', 'indicadordeexclusão', 'indicadordeforadoprazo',\n",
      "       'unidadesaláriocódigo', 'valorsaláriofixo'],\n",
      "      dtype='object')\n",
      "Index(['competênciamov', 'região', 'uf', 'município', 'seção', 'subclasse',\n",
      "       'saldomovimentação', 'cbo2002ocupação', 'categoria', 'graudeinstrução',\n",
      "       'idade', 'horascontratuais', 'raçacor', 'sexo', 'tipoempregador',\n",
      "       'tipoestabelecimento', 'tipomovimentação', 'tipodedeficiência',\n",
      "       'indtrabintermitente', 'indtrabparcial', 'salário', 'tamestabjan',\n",
      "       'indicadoraprendiz', 'origemdainformação', 'competênciadec',\n",
      "       'indicadordeforadoprazo', 'unidadesaláriocódigo', 'valorsaláriofixo'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "print(teste_1.columns)\n",
    "print(teste_2.columns)\n",
    "print(teste_3.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CAGEDMOV2020 processado com sucesso.\n",
      "CAGEDMOV2021 processado com sucesso.\n",
      "CAGEDMOV2022 processado com sucesso.\n"
     ]
    },
    {
     "ename": "ParserError",
     "evalue": "Error tokenizing data. C error: Expected 28 fields in line 2622581, saw 30\n",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mParserError\u001b[0m                               Traceback (most recent call last)",
      "Cell \u001b[1;32mIn[14], line 21\u001b[0m\n\u001b[0;32m     18\u001b[0m \u001b[38;5;66;03m# Loop pelos arquivos encontrados\u001b[39;00m\n\u001b[0;32m     19\u001b[0m \u001b[38;5;28;01mfor\u001b[39;00m arquivo \u001b[38;5;129;01min\u001b[39;00m arquivos:\n\u001b[0;32m     20\u001b[0m     \u001b[38;5;66;03m# Carregar arquivo TXT\u001b[39;00m\n\u001b[1;32m---> 21\u001b[0m     df \u001b[38;5;241m=\u001b[39m \u001b[43mpd\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mread_csv\u001b[49m\u001b[43m(\u001b[49m\u001b[43marquivo\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43msep\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43m;\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mencoding\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mutf-8\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m)\u001b[49m\n\u001b[0;32m     23\u001b[0m     \u001b[38;5;66;03m# Remover espaços extras nos nomes das colunas\u001b[39;00m\n\u001b[0;32m     24\u001b[0m     df\u001b[38;5;241m.\u001b[39mcolumns \u001b[38;5;241m=\u001b[39m df\u001b[38;5;241m.\u001b[39mcolumns\u001b[38;5;241m.\u001b[39mstr\u001b[38;5;241m.\u001b[39mstrip()\n",
      "File \u001b[1;32mc:\\Users\\João\\anaconda3\\Lib\\site-packages\\pandas\\io\\parsers\\readers.py:1026\u001b[0m, in \u001b[0;36mread_csv\u001b[1;34m(filepath_or_buffer, sep, delimiter, header, names, index_col, usecols, dtype, engine, converters, true_values, false_values, skipinitialspace, skiprows, skipfooter, nrows, na_values, keep_default_na, na_filter, verbose, skip_blank_lines, parse_dates, infer_datetime_format, keep_date_col, date_parser, date_format, dayfirst, cache_dates, iterator, chunksize, compression, thousands, decimal, lineterminator, quotechar, quoting, doublequote, escapechar, comment, encoding, encoding_errors, dialect, on_bad_lines, delim_whitespace, low_memory, memory_map, float_precision, storage_options, dtype_backend)\u001b[0m\n\u001b[0;32m   1013\u001b[0m kwds_defaults \u001b[38;5;241m=\u001b[39m _refine_defaults_read(\n\u001b[0;32m   1014\u001b[0m     dialect,\n\u001b[0;32m   1015\u001b[0m     delimiter,\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m   1022\u001b[0m     dtype_backend\u001b[38;5;241m=\u001b[39mdtype_backend,\n\u001b[0;32m   1023\u001b[0m )\n\u001b[0;32m   1024\u001b[0m kwds\u001b[38;5;241m.\u001b[39mupdate(kwds_defaults)\n\u001b[1;32m-> 1026\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43m_read\u001b[49m\u001b[43m(\u001b[49m\u001b[43mfilepath_or_buffer\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mkwds\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32mc:\\Users\\João\\anaconda3\\Lib\\site-packages\\pandas\\io\\parsers\\readers.py:626\u001b[0m, in \u001b[0;36m_read\u001b[1;34m(filepath_or_buffer, kwds)\u001b[0m\n\u001b[0;32m    623\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m parser\n\u001b[0;32m    625\u001b[0m \u001b[38;5;28;01mwith\u001b[39;00m parser:\n\u001b[1;32m--> 626\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mparser\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mread\u001b[49m\u001b[43m(\u001b[49m\u001b[43mnrows\u001b[49m\u001b[43m)\u001b[49m\n",
      "File \u001b[1;32mc:\\Users\\João\\anaconda3\\Lib\\site-packages\\pandas\\io\\parsers\\readers.py:1923\u001b[0m, in \u001b[0;36mTextFileReader.read\u001b[1;34m(self, nrows)\u001b[0m\n\u001b[0;32m   1916\u001b[0m nrows \u001b[38;5;241m=\u001b[39m validate_integer(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mnrows\u001b[39m\u001b[38;5;124m\"\u001b[39m, nrows)\n\u001b[0;32m   1917\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[0;32m   1918\u001b[0m     \u001b[38;5;66;03m# error: \"ParserBase\" has no attribute \"read\"\u001b[39;00m\n\u001b[0;32m   1919\u001b[0m     (\n\u001b[0;32m   1920\u001b[0m         index,\n\u001b[0;32m   1921\u001b[0m         columns,\n\u001b[0;32m   1922\u001b[0m         col_dict,\n\u001b[1;32m-> 1923\u001b[0m     ) \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_engine\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mread\u001b[49m\u001b[43m(\u001b[49m\u001b[43m  \u001b[49m\u001b[38;5;66;43;03m# type: ignore[attr-defined]\u001b[39;49;00m\n\u001b[0;32m   1924\u001b[0m \u001b[43m        \u001b[49m\u001b[43mnrows\u001b[49m\n\u001b[0;32m   1925\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m   1926\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mException\u001b[39;00m:\n\u001b[0;32m   1927\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mclose()\n",
      "File \u001b[1;32mc:\\Users\\João\\anaconda3\\Lib\\site-packages\\pandas\\io\\parsers\\c_parser_wrapper.py:234\u001b[0m, in \u001b[0;36mCParserWrapper.read\u001b[1;34m(self, nrows)\u001b[0m\n\u001b[0;32m    232\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[0;32m    233\u001b[0m     \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mlow_memory:\n\u001b[1;32m--> 234\u001b[0m         chunks \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_reader\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mread_low_memory\u001b[49m\u001b[43m(\u001b[49m\u001b[43mnrows\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    235\u001b[0m         \u001b[38;5;66;03m# destructive to chunks\u001b[39;00m\n\u001b[0;32m    236\u001b[0m         data \u001b[38;5;241m=\u001b[39m _concatenate_chunks(chunks)\n",
      "File \u001b[1;32mparsers.pyx:838\u001b[0m, in \u001b[0;36mpandas._libs.parsers.TextReader.read_low_memory\u001b[1;34m()\u001b[0m\n",
      "File \u001b[1;32mparsers.pyx:905\u001b[0m, in \u001b[0;36mpandas._libs.parsers.TextReader._read_rows\u001b[1;34m()\u001b[0m\n",
      "File \u001b[1;32mparsers.pyx:874\u001b[0m, in \u001b[0;36mpandas._libs.parsers.TextReader._tokenize_rows\u001b[1;34m()\u001b[0m\n",
      "File \u001b[1;32mparsers.pyx:891\u001b[0m, in \u001b[0;36mpandas._libs.parsers.TextReader._check_tokenize_status\u001b[1;34m()\u001b[0m\n",
      "File \u001b[1;32mparsers.pyx:2061\u001b[0m, in \u001b[0;36mpandas._libs.parsers.raise_parser_error\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;31mParserError\u001b[0m: Error tokenizing data. C error: Expected 28 fields in line 2622581, saw 30\n"
     ]
    }
   ],
   "source": [
    "# Criando dicionário para armazenar os dados\n",
    "caged_data = {}\n",
    "\n",
    "# Definindo anos e tipos de arquivos\n",
    "anos = [2020, 2021, 2022, 2023, 2024]\n",
    "tipos = ['CAGEDMOV', 'CAGEDEXC', 'CAGEDFOR']\n",
    "colunas_excluir = ['competênciadec', 'indicadordeforadoprazo', 'unidadesaláriocódigo', 'valorsaláriofixo']\n",
    "\n",
    "# Loop pelos tipos e anos\n",
    "for tipo in tipos:\n",
    "    for ano in anos:\n",
    "        # Procurar arquivos do tipo-ano\n",
    "        arquivos = glob.glob(f\"entrada\\\\Dados\\\\{tipo}{ano}*.txt\")\n",
    "\n",
    "        # Lista para armazenar dados temporários\n",
    "        temp_data = []\n",
    "\n",
    "        # Loop pelos arquivos encontrados\n",
    "        for arquivo in arquivos:\n",
    "            # Carregar arquivo TXT\n",
    "            df = pd.read_csv(arquivo, sep=';', encoding='utf-8')\n",
    "\n",
    "            # Remover espaços extras nos nomes das colunas\n",
    "            df.columns = df.columns.str.strip()\n",
    "\n",
    "            # Excluir colunas desnecessárias (criando um novo DataFrame sem as colunas a excluir)\n",
    "            colunas_presentes = [col for col in colunas_excluir if col in df.columns]\n",
    "            df = df.drop(columns=colunas_presentes)  # Criando um novo DataFrame sem as colunas\n",
    "\n",
    "            # Adicionar colunas de admissão e desligamento\n",
    "            df['admissoes'] = df['saldomovimentação'].apply(lambda x: 1 if x == 1 else 0)\n",
    "            df['desligamentos'] = df['saldomovimentação'].apply(lambda x: 1 if x == -1 else 0)\n",
    "\n",
    "            # Agrupar por todas as colunas, somando valores\n",
    "            df = df.groupby(df.columns.tolist()).sum().reset_index()\n",
    "\n",
    "            # Armazenar no data temporário\n",
    "            temp_data.append(df)\n",
    "\n",
    "        # Concatenar dados de todos os arquivos do ano-tipo\n",
    "        if temp_data:\n",
    "            caged_data[f\"{tipo}_{ano}\"] = pd.concat(temp_data, ignore_index=True)\n",
    "            print(f\"{tipo}{ano} processado com sucesso.\")\n",
    "\n",
    "# Unificar todos os dados em um único dataframe\n",
    "caged_final = pd.concat(caged_data.values(), ignore_index=True)\n",
    "\n",
    "# Exibir uma amostra do resultado final\n",
    "print(caged_final.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "caged_final.to_csv(pwd + \"saida\\\\Novo_CAGED.csv\", index=False)"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "provenance": []
  },
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}