/qsacnpj

Pacote que trata e organiza os dados do Cadastro Nacional da Pessoa Jurídica (CNPJ)

Primary LanguageRGNU General Public License v3.0GPL-3.0

Pacote desenvolvido para tratar e organizar a Base de dados do Cadastro Nacional da Pessoa Jurídica (CNPJ)

O Projeto

O pacote qsacnpj é uma das ferramentas utilizadas no projeto de “Transparência das Contas Públicas”, desenvolvido e executado pelo Observatório Social do Brasil - Município de Santo Antônio de Jesus - Estado da Bahia.

Atualmente, o projeto é composto das seguintes ferramentas:

Pacotes desenvolvidos em Linguagem R para realizar Web Scraping e tratamento de dados:

Paineis desenvolvidos em Power BI para produzir diversas visualização dos dados:

Sobre a proposta e o objetivo do pacote

O objetivo do pacote é tornar os dados do Cadastro Nacional da Pessoa Jurídica (CNPJ) mais acessível, num padrão legível por máquina, para que a Sociedade possa utilizá-los em diversas iniciativas, atividades, projetos etc. Isso se faz necessário porque a Receita Federal publicou a base num formato do tipo fixed width - arquivo de largura fixa, o que requer o prévio tratamento dos dados (com elevado grau de dificuldade técnica e esfoço computacional adicional) antes do seu consumo imediato em sofwares de análise de dados.

Por congregar diversas informações sobre as Pessoas Jurídicas constituídas no Brasil, a base de dados do CNPJ é uma das fontes governamentais de informação pública mais relevantes do país.

Ao ter acesso aos dados tratados, o Observatório Social do Brasil - Município de Santo Antônio de Jesus poderá utilizá-los para aprimor dois outros projetos na área de “Transparência das Contas Públicas”:

Os dois painéis têm como objetivo tornar os dados do Tribunais de Contas dos Municípios do Estado da Bahia mais acessíveis e possibilitar outras visualizações dos dados para fornecer informações de alto interesse social.

Comunidade e Colaboração

O Observatório Social do Brasil, aqui representado pelo Observatório Social do Município de Santo Antônio de Jesus - Ba, gostaria de agradecer o apoio da Comunidade ‘Dados Abertos .BR’ no Telegram, pela incessante busca de tornar os dados governamentais públicos e de alto interesse social abertos à Sociedade. A resiliência dos membros que provocaram à Receita Federal sobre a obrigação de abertura dos dados, conforme determina a Constituição Federal de 1988 c/c a Lei de Acesso à Informação e c/c o Decreto Federal n.° 8.777/2016, foi um fator fundamental para essa conquista.

Gostaríamos de agradecer também à comunidade da Linguagem de Programação R que foi utilizada para o desenvolvimento do presente pacote, em especial à comunidade TidyVerse, ao Curso-R pela colaboração ativa e material disponibilizado, à comunidade R Brasil no Telegram, e todos aqueles de disponibilizam ebook sobre a linguagem R link, posts e desenvolvem pacotes e soluções de infraestrutura para a linguagem. Sem o esforço, colaboração, cooperativismo e abnegação de todos, esse trabalho não seria possível.

Ademais, quaisquer sugestões, reclamações ou críticas podem ser realizadas no área issues do GitHub.

Preparando e Executando o pacote qsacnpj

Preparativos

1 - Baixe os arquivos (.zip) da base de dados do CNPJ no site da Receita Federal.

2 - Salve o arquivo ‘.zip’ no diretório que será utilziado para o processamento dos dados.

3 - Descompacte os arquivos no diretório. Talvez os arquivos tenhom nomes semelhantes a ‘K3241.K03200DV.D91111.L00001’. OBS: Os arquivos descompactados tem mais de 99Gb. Verifique se há espaço suficiente no seu HD

4 - IMPORTANTE: Crie uma pasta específica para armazenar somente os arquivos.

5 - Instale e execute o pacote. Abaixo, seguem algumas sugestões:

Executando o pacote

# Para garantir a instalação e execução do pacote `qsacnpj` sem maiores dificuldades,
# é só seguir os 5 passos a seguir:

# 1 - Instale o pacote `remotes` para poder baixar o pacote `qsacnpj` no repositório do GitHub;

install.packages("remotes")

remotes::install_github("georgevbsantiago/qsacnpj")

# 2 - Em seguida, aparecerá o seguinte aviso e opções de escolhas (abaixo).
# Nessa etapa, em geral, a opção "2" instalará os pacotes que o respectivo
# desenvolvedor considera como estável e adequado para o ambiente de produção.
# Já a opção "1" instalará os pacotes em desenvolvimento armazenados no repositório
# do GitHub. Por serem pacotes em desenvolvimento, podem ter (ou não) algum tipo de instabilidade
# ou inconsistência, mas, em contrapartida, tendem a ter um desempenho melhor durante a execução.

    # Downloading GitHub repo georgevbsantiago/qsacnpj@master
    # These packages have more recent versions available.
    # It is recommended to update all of them.
    # Which would you like to update?
    # 1: All                                           
    # 2: CRAN packages only

# 3 - Após selecionar uma das opções acima, o pacote será instalado.
# Então, carregamos o pacote na memória.

library(qsacnpj)

# 4 - Em seguida, selecionamos a pasta de trabalho (Work Directory) que será armazenado os dados do CNPJ.
# A título de exemplo, indicamos o diretório "D:/qsa_cnpj" (OBS: Sempre utilize "/" no caminho do diretório.)

setwd("D:/qsa_cnpj")

# 5 - Por fim, executamos o pacote. Para tanto, elaboramos 4 sugestões de execução do pacote abaixo.
# (OBS: No argumento "path_arquivos_txt", indique o diretório no qual estão armazenados os arquivos
# que foram baixados e descompactados do site da Receita Federal. O diretório deve conter apenas os arquivos
# da Receita Federal que serão tratados)

# Sugestão 1: Tratar toda a base do CNPJ, 100.000 linha a cada interação,
# armazenando os dados no SQLite
qsacnpj::gerar_bd_cnpj(path_arquivos_txt = "D:/qsa_cnpj",
                       localizar_cnpj = "NAO",
                       n_lines = 100000,
                       armazenar = "sqlite")


# Sugestão 2: Tratar toda a base do CNPJ, 100.000 linha a cada interação,
# armazenando os dados no CSV (OBS: O delimitador do CSV é o simbolo: "#').
qsacnpj::gerar_bd_cnpj(path_arquivos_txt = "D:/qsa_cnpj",
                       localizar_cnpj = "NAO",
                       n_lines = 100000,
                       armazenar = "csv")


# Sugestão 3: Localziar um conjunto de CNPJ, interando a base a cada 100.000 linha,
# armazenando os dados localizados no SQLite
# (OBS1: O delimitador do CSV é o simbolo: "#');
# (OBS2: Exemplo com número de CNPJ, entre aspas (""), do Banco do Brasil, Banco do Nordeste,
# Banco da Amazônia e Caixa Econômica)
qsacnpj::gerar_bd_cnpj(path_arquivos_txt = "D:/qsa_cnpj",
                       localizar_cnpj = c("00000000000191", "07237373000120",
                                          "00360305000104", "04902979000144"),
                       n_lines = 100000,
                       armazenar = "sqlite")


# Sugestão 4: Localziar um conjunto de CNPJ, interando a base a cada 100.000 linha,
# armazenando os dados localizados no CSV
# (OBS1: O delimitador do CSV é o simbolo: "#');
# (OBS2: Exemplo com número de CNPJ, entre aspas (""), do Banco do Brasil, Banco do Nordeste,
# Banco da Amazônia e Caixa Econômica)
qsacnpj::gerar_bd_cnpj(path_arquivos_txt = "D:/qsa_cnpj",
                       localizar_cnpj = c("00000000000191", "07237373000120",
                                          "00360305000104", "04902979000144"),
                       n_lines = 100000,
                       armazenar = "csv")

Resultado esperado: No teste realizado sem aplicar o filtro ‘localizar_cnpj’, o código executou com sucesso o tratamento e organização de todos os dados no tempo de 4 horas, usando um notebook com processador i7 5ª Geração, 16Gb DDR3 e disco HDD.

Ao final do processamento, o usuário visualizará, no diretório escolhido, o arquivo SQLite com 03 tabelas (cnpj_dados_cadastrais_pj, cnpj_dados_socios_pj e cnpj_dados_cnae_secundario_pj), ou, caso opte pelo CSV, 03 arquivos CSV cnpj_dados_cadastrais_pj.csv, cnpj_dados_socios_pj.csv e cnpj_dados_cnae_secundario_pj.csv, dentre outras tabelas que forem adicionadas com as atualizações do pacote.

Informações técnicas complementares podem ser obtidas na documentação do pacote ao digitar ?qsacnpj

Base de dados Complementares

Adicionamos, no pacote, um conjunto de Tabelas complementares disponíveis no site da Receita Federal para enriquecer e detalhar a Bases de Dados do CNPJ.

Adicionamos, no pacote, um conjunto de Tabelas complementares disponíveis no site do IBGE para enriquecer e detalhar a Bases de Dados do CNPJ

Base de Dados do CNPJ tratada

A base de dados do CNPJ já tratada está disponível nos links abaixo, nos formatos SQLite e CSV:

Para acessar os dados usando o SQLite, siga os 3 passos seguintes:

1 - Baixe os arquivos ‘.zip’. OBS: O arquivo no formato ‘.zip’ tem cerca de 5Gb;

2 - Descompacte o arquivo. OBS: Após ser descompactado, o arquivo ficará com 14Gb. Verifique se há espaço no disco;

3 - Instale o programa DB Browser for SQLite ( neste link ). Trata-se de uma programa que permite visualizar os dados do SQLite e fazer consultas SQL;

4 - Ao abrir o arquivo dados_qsa_cnpj.db com o DB Browser for SQLite, o tempo de carregamento do banco de dados no programa pode demorar de 3 a 7 minutos a depender das configurações do computador, caso o usuário opte pela opção de “Nevegar pela tabela”. Se optar por realizar consultas SQL, a base estará pronta para uso imediato.

Modelo Lógico do Banco de Dados

Para melhor visualização das tabelas do Banco de Dados, disponibilizamos esse imagem (link) do Modelo Lógico . (OBS: O modelo lógico foi desenhado sem rigor técnico. A sua disponibilização é só para efeito de visualização das tabelas que compõem a Base de Dados)

Futuras Implementações, Atualizações e BUGs

Futuras Implementações ou Melhorias

  • Implementar função para criar tabela contendo o CNPJ dos entes públicos;

  • Incluir a base de dados do “Mapa das Organizações da Sociedade Civil”, disponibilizado no Portal de Dados Abertos

  • Implementar uma função para verificar se os CNPJ na variável ‘localizar_cnpj’ são válidos;

  • Criar uma função para verificar a adequação da base de dados;

  • Verificar se há ganho de desempenho ao trocar a função do pacote stringr por uma equivalente da base R (ex: trimws() ) ou usando argumentos das funções readr::write_delim ou DBI:dbWriteTable para suprimir os espaços em branco na esquerda e na direita das strings;

  • Verificar se há ganho de desempenho ao filtrar o data frame com uma coluna numérica, em vez de uma coluna character;

  • Verificar se há ganho de desempenho pré-definindo o tipo das colunas durante o tratamento dos dados;

  • Incluir rotina para baixar a Base de dados do CNPJ e as Tabelas complementares disponibilizadas pela Receita Federal

  • Criar índice no SQLite para todas as tabelas;

  • Melhorar o desempenho do código, difinindo novas estratégias para correção do encoding, ao utilizar a função read_chunked_lines, que está dentro da função obter_dados_qsa. A solução atual onerou a execução em mas 2 horas.

Atualizações

qsacnpj - versão: 0.2.1

1 - BUGs Corrigidos:

1.1 - Retirado espaços duplos e quebra de linhas nos valores das Base de dados Complementares (#18)

qsacnpj - versão: 0.1.9

1 - BUGs Corrigidos:

1.1 - Aprimorada a regra da Expressão Regular (regex) que faz a substituição dos caracteres iniciais 000*** por *** no CPF dos sócios. A regex anterior, usada junto com a função gsub, estava substitundindo indevidamente números do CNPJ/PJ sócio com 0000001 por ***1, afetendo cerca de mil de registro.

2 - MELHORIAS:

2.1 - Aprimorada o tratamento das strings usando as funções do pacote stringr.

qsacnpj - versão: 0.1.8

1 - MELHORIAS:

1.1 - Atualizamos a Tabela de Qualificação do Responsável no Quadro Societário da Pessoa Jurídica de acordo com o arquivo mais recente disponibilizado pela Receita Federal qsacnpj::tab_qualificacao_responsavel_socio.

qsacnpj - versão: 0.1.7

1 - MELHORIAS:

1.1 - Adicionamos, no pacote, uma Tabela com o código dos Municípios do SIAFI, foi incluída dentro do pacote, podendo ser acessada pela variável tab_codigo_municipios_siafi. Essa tabela é extremamente relevante, pois será possível, agora, realizar a correspondência entre os códigos dos municípios contidos na base de dados do CNPJ (que são os códigos atribuídos pelo SIAFI - OBS: Sistema Integrado de Administração Financeira do Governo Federal) com o código dos Municípios do IBGE. Ou seja, essa base de dados disponibilizada pelo Tesouro Nacional é uma tabela de correspondência entre os códigos dos municípios da base do CNPJ (os mesmos usados no SIAFI) e os códigos da tabela do IBGE.

2 - BUGs Corrigidos:

2.1 - A coluna tipo_de_registro não estava sendo incluída na tabela cnpj_dados_cnae_secundario_pj.

qsacnpj - versão: 0.1.6

1 - MELHORIAS:

1.1 - Incluída rotina para tratamento de dados. Substituição do “Ç” por “C” na coluna descricao_tipo_logradouro da tabela cnpj_dados_cadastrais_pj, com o objetivo de evitar problemas de encoding ao importar os dados para Sistemas de Gerenciamento de Banco de Dados ou por Ferramentas de Análise de Dados.

1.2 - Reformulado o código para conexão com os SGBDs. Agora, é possível conectar o script com SQLite, MS SQL Server, Oracle e MySQL. Os desenvolvedores conseguirão também implementar a conexão com outros SGBDs.

qsacnpj - versão: 0.1.5

Foram realizados ajustes no código, visto que a Receita Federal, a partir do mês de maio de 2019, passou a liberar a base do CNPJ por meio de 20 arquivos, e não mais num arquivo único.

qsacnpj - versão: 0.1.4

1 - BUGs Corrigidos:

1.1 - Implementado uma rotina de tratamento adequado para construção da tabela referente aos CNAEs secundários.

2 - MELHORIAS:

2.1 - Adicionamos, no pacote, um conjunto de Tabelas complementares disponíveis no site do IBGE para enriquecer e detalhar a Bases de Dados do CNPJ.

2.2 - Tratamento de dados

  • Os dados das colunas que representam datas foram tratados para o padrão internacional (ano-mes-dia). Ex: Antes: 20180505 / Depois: 2018-05-05 . As datas com valores inválidos (Ex: 00000000), ficaram como NULL (vazio) na tabela;

  • Os dados das colunas que representam a capital social foram transformadas para o padrão de valores monetários internacional: Antes: 00000001272800 / Depois: 12728.00

qsacnpj - versão: 0.1.3

1 - BUGs Corrigidos:

1.1 - Corrigido o problema das linhas NULL. Estava relacionado ao encoding, devido a letras com “Ç”, a exemplo da palavra “PRAÇA”. Como tivemos que implementar o argumento locale = readr::locale(encoding = "ISO-8859-1") na função, houve uma perda substancial no desempenho do processamento, fazendo que com a criação da base de dados demore mais de 3h.

2 - MELHORIAS:

2.1 - Rotina para tratar o número dos CPFs dos sócios retirando os 03 zeros iniciais. Antes: 000***718468** / Depois: ***718468**

2.2 - Adicionamos, no pacote, um conjunto de Tabelas complementares disponíveis no site da Receita Federal para enriquecer e detalhar a Bases de Dados do CNPJ.

2.3 - Com a implementação das novas tabelas, o código foi reestruturado. Agora, o tratamento e organização dos dados da base do CNPJ é executado com a função qsacnpj::gerar_bd_cnpj

qsacnpj - versão: 0.1.2

  • Adicionado rotina para substituir “;” por “#”, em virtude da base de dados usar o “;” no corpo de diversos dados.

BUGs e Warnings

Caso identifique um BUG, favor abrir uma issues no Github.

Outras alternativas em R ou Python

Por fim, deixo a sugestão de outras alternativas que foram criados com o mesmo propósito: