Skip to content
Pacote que trata e organiza os dados do Cadastro Nacional da Pessoa Jurídica (CNPJ)
R
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
R Versão 0.1.7 Nov 30, 2019
data
img Versão 0.1.7 Nov 30, 2019
man
.Rbuildignore
.gitignore
DESCRIPTION
LICENSE
NAMESPACE Versão 0.1.3 Mar 29, 2019
NEWS.md
README.Rmd
README.md
qsacnpj.Rproj

README.md

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 96Gb. 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

# Instalar o pacote:
devtools::install_github("georgevbsantiago/qsacnpj")

library(qsacnpj)

# Selecionar a pasta de trabalho (Work Directory) que será armazenado os dados do CNPJ
setwd("/diretorio/")

# 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 hora e 30 minutos, gerando um arquivo SQLite de +/- 24Gb, 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

Adicionamos, no pacote, um conjunto de Tabelas complementares disponíveis no site do Tesouro Nacional 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. Contudo, é bom destacar que essa base de dados foi criada com a primeira versão do pacote, logo, pode haver algumas inconsistência.

O arquivo disponível foi criado com o SQLite. Para acessar os dados, siga 3 passos:

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.1.7

1 - MELHORIAS: 1.1 - Adicionamos, no pacote, uma Tabela com o Código dos Municípios do SIAFI, 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 do desempenho da função, fazendo que com a criação da base de dados demore mais de 2h.

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.

Como essa é a primeira versão do pacote, podem existir problemas ainda não identificados.

Outras alternativas em R ou Python

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

You can’t perform that action at this time.