Painel sobre Violência Contra as Mulheres

Dashboard Violência Contra Mulheres – Parte 1 – Web Scraping com Pentaho e Python

Olá pessoal, depois de um tempo sem postagens estou voltando para mais artigos e tutorias, espero os comentários de vocês com sugestões de assuntos para abordar. Vou começar 2018 com um tema muito interessante: Web Scraping. Neste artigo vou apresentar uma solução para a extração de dados da Secretária de Segurança Pública do Estado de São Paulo sobre a Violência Contra Mulheres. Nesta primeira parte vou apresentar como realizar a extração dos dados da página da SSP/SP utilizando Python, e como automatizar este processo utilizando o Pentaho Data Integration, nosso conhecido PDI.

Web Scraping e Dados Abertos

Para quem não está familiarizado com o assunto, nada mais é do que a coleta de dados de um site web. Em um mundo perfeito os dados seriam acessíveis via webservices e dentro dos padrões da Open Knowledge Internacional, porém isso nem sempre acontece. Eu gostaria de acreditar que é por desconhecimento tecnológico, ao invés da falta de interesse destes dados ficarem disponíveis e acessíveis, mas não acredito. A partir deste cenário podemos “garimpar” a web para tabular e encontrar os dados que desejamos, isso é Web Scraping.

Existem vários datasets (conjunto de dados) espalhados pela web com dados públicos do governo, o Brasil ainda engatinha nesta área porém já existem boas iniciativas como o portal Dados.gov.br. Infelizmente muitos dados ainda não estão disponíveis de uma forma acessível, alguns estão dispostos em tabelas no corpo da página, outros até em documentos PDF, esse tipo de informação não pode nem ser considerada um “dado aberto”.

Porque o Tema Violência Contra as Mulheres?

No mundo: Até 70% das mulheres sofrem violência ao longo da vida. A violência física imposta por um parceiro íntimo, como espancamento, relações sexuais forçada  e outras condutas abusivas, é a forma mais comum de violência sofrida pelas mulheres no mundo.

No Brasil: A cada 4 minutos uma mulher é vítima de agressão. A cada uma hora e meia ocorre um feminicídio (morte de mulher por questões de gênero). Mais de 43 mil mulheres foram assassinadas nos últimos 10 anos, boa parte pelo próprio parceiro. O Brasil é o sétimo país no ranking de assassinato de mulheres dentre 84 países. Os números são maiores do que os de todos os países árabes e africanos. 75% dos brasileiros acreditam que as agressões nunca ou quase nunca são punidas.

Fonte: Livre Abuso

Eu poderia ter escolhido qualquer outro tema, algo mais “leve”, mas acredito que temos que utilizar nossos conhecimentos, também, a favor da sociedade. Este é um tema muito sensível e pouco explorado e, infelizmente, com pouco dados disponíveis para pesquisas e estudos. Então porque não ajudar um pouco na divulgação desses números para demonstrar e sensibilizar as pessoas sobre ele? Quem sabe até ajudar alguém!

Se você não se sensibiliza com este tema e acha uma bobagem fique a vontade para procurar outros tutorias, você não é o tipo de pessoa que eu gostaria que acompanhasse este Blog…

Fonte de Dados

Nossa fonte de dados será o site da Secretária de Segurança Pública do Estado de São Paulo, que disponibiliza estatísticas sobre ocorrências, neste caso vamos utilizar os dados referentes a Violência Contra as Mulheres. Abaixo podemos ver que o site tem informações relevantes porém estão dispostas em várias tabelas, o que não é nada prático para a sua utilização.

Página com Estatísticas de Violência Contra Mulheres da SSP/SP
Página com Estatísticas de Violência Contra Mulheres da SSP/SP

Uma forma bem arcaica de obter estes dados seria copiar todas as tabelas e colar em um programa como o Excel e, então, fazer o “trabalho sujo” manualmente. Além de trabalhoso, este processo tem um alto risco de gerar falhas nos dados, quanto menos intervenção manual tivermos mais confiável será nossa informação. Apesar das tabelas não serem a melhor opção temos outras piores, imaginem dados em um PDF? A parte boa é que o site apresenta uma estrutura bem definida no HTML da página, assim é possível “navegar” entre os elementos de uma forma mais fácil.

Por que Python?

Poderíamos coletar as informações diretamente com o PDI, ou então utilizar uma rotina em Java, porém optei pelo Python pela sua simplicidade. Em apenas 36 linhas consegui escrever uma rotina que abre o site, faz o “parser” do HTML e extraí seu conteúdo em um arquivo CSV, de uma forma bem simples. Quem estiver mais familiarizado com a linguem e suas bibliotecas pode melhorar este código.

from urllib.request import urlopen
from bs4 import BeautifulSoup
import csv

page = 'http://www.ssp.sp.gov.br/Estatistica/ViolenciaMulher.aspx'
content = urlopen(page)
soup = BeautifulSoup(content, 'html.parser')
tables = soup.findAll('table')

linesContent = []
for i in range(2, len(tables)+1):
    dataText = soup.find('span', {'id':'conteudo_repPeriodo_lblPeriodo_'+str(i)}).text
    mesAno = str(dataText).split(':')[1].replace('de','|').replace(' ','').split('|')
    table = soup.find('table',{'id':'conteudo_repPeriodo_grdOcorrencias_'+str(i)})
    rows = table.findAll('tr')
    for i in range(1, len(rows)):
        cells = rows[i].findAll('td')
        obj = {
            'mes': mesAno[0],
            'ano': mesAno[1],
            'tipo':str(cells[0].text),
            'regCapital':int(cells[1].text),
            'regDemacro':int(cells[2].text),
            'regInterior':int(cells[3].text),
            'total':int(cells[4].text)
        }
        linesContent.append(obj)

file = open('result.csv', 'w')
with file:
    fieldNames = ['mes', 'ano','tipo','regCapital','regDemacro','regInterior','total']
    writer = csv.DictWriter(file, fieldnames=fieldNames, quoting=csv.QUOTE_ALL)
    writer.writeheader()
    for line in linesContent:
        writer.writerow(line)

Não vou focar em explicar o código acima ele, basicamente, utiliza os padrões de identificação das tabelas nas páginas para iterar sobre elas e assim extrair os seus dados em objetos. Após a extração os dados são convertidos em um arquivo CSV. Estou utilizando o Python 3 juntamento com a biblioteca BeautifulSoup, vale a pena você dar uma pesquisada nela, é muito prática para trabalhar com web scraping.

Banco de Dados

Para este artigo estou utilizando um banco PostgreSQL rodando no Docker, o que me ajuda demais na questão de configuração e facilidade de utilização. Segue abaixo um script do Docker Compose para a criação do banco e de um Client para você conseguir manipular o SGBD através de uma interface gráfica. Sugiro que você estude um pouco sobre o Docker, auxilia muito no dia-a-dia.

version: '2'

services:
  postgres:
    image: postgres
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: pgdb
      PGDATA: /data/postgres
    volumes:
      - /data/postgres:/data/postgres
    ports:
      - "5432:5432"
    networks:
      - postgres
    restart: unless-stopped
 
  pgadmin:
    links:
      - postgres:postgres
    image: fenglc/pgadmin4
    volumes:
      - /data/pgadmin:/root/.pgadmin
    ports:
      - "5050:5050"
    networks:
      - postgres
    restart: unless-stopped

networks:
  postgres:
    driver: bridge

 

Teremos duas tabelas no projeto, a tabela TIPO_OCORRENCIA ficará responsável por armazenar os tipos das ocorrências relatadas já tabela OCORRENCIA armazenará cada evento registrado, execute o script abaixo para criar as tabelas e as sequences que iremos utilizar:

CREATE SEQUENCE tipo_ocorrencia_id_seq
  INCREMENT BY 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1;

CREATE SEQUENCE ocorrencia_id_seq
  INCREMENT BY 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1;

CREATE TABLE tipo_ocorrencia ( 
  id int8 NOT NULL DEFAULT nextval('tipo_ocorrencia_id_seq'::regclass),
  descricao text NOT NULL, 
  CONSTRAINT tipo_ocorrencia_pkey PRIMARY KEY (id)
);

CREATE TABLE ocorrencia ( 
  id int8 NOT NULL DEFAULT nextval('ocorrencia_id_seq'::regclass), 
  mes text NOT NULL, 
  ano int4 NOT NULL, 
  tipo text NOT NULL, 
  total_regiao_capital int4 NULL, 
  total_regiao_demacro int4 NULL, 
  total_regiao_interior int4 NULL, 
  total_geral int4 NULL, 
  CONSTRAINT ocorrencia_pkey PRIMARY KEY (id)
);

Job Principal

Agora que já temos a estrutura do nosso banco pronta é hora de partir para o PDI e desenhar nosso processo ETL. Inicialmente vamos excluir, se existir, o arquivo CSV que já estiver na pasta (gerado pelo script Python), depois será executado o script Python que será responsável pelo web scraping e por salvar os dados em um arquivo CSV, a próxima etapa será verificar a existência do arquivo CSV e, por fim, a carga de dados em nosso SGBD. O Job principal ficou assim:

Job Principal
Job Principal

Transformações para Carga de Dados

Vamos criar duas transformações para realizar a carga de dados dos Tipos e das Ocorrências. A de Tipos é um pouco mais trabalhosa pois temos que filtrar todos os tipos utilizados, já a de ocorrência é bem simples, o único step diferente é a questão de busca do tipo em uma outra tabela.

Carga de Tipos de Ocorrências

Para os tipos vamos percorrer todo o CSV, selecionar apenas o campo Tipo, e então remover as duplicidades, assim teremos apenas os tipos que estão sendo utilizados. Após este processo vamos persistir os dados no banco, veja como fica a transformação:

Transformação: Carga de Tipos de Ocorrências
Transformação: Carga de Tipos de Ocorrências

Para o primeiro step vamos utilizar o “CSV Input” que está dentro da pasta “Input”. Na imagem abaixo estão as suas definições, além do caminho para o arquivo podemos alterar o caractere delimitador, neste caso é a “,” além do encapsulador para Strings, que neste caso são as aspas duplas. Na parte inferior da janela são apresentados os campos que foram mapeados automaticamente pelo PDI, podemos alterar os seus tipos, caso seja necessário. Para verificar se os tipos estão corretos utilize o botão “Preview” para visualizar parte do arquivo.

Definições do step CSV Input
Definições do step CSV Input

Já no segundo step vamos “limpar” os campos do primeiro step, pois utilizaremos apenas o campo “tipo”. Para isso vamos utilizar o step “Select Value”, dentro da pasta “Transform”, nele vamos selecionar apenas o campo tipo na aba “Select & Alter”. O terceiro step é a ordenação dos nossos dados, que se faz necessária para a aplicação ganhar velocidade, além de ser obrigatória para alguns steps. Utilize o step “Sort rows”, dentro da pasta “Transform”, e escolha o campo que utilizaremos para a ordenação, no caso, só temos um campo.

Para finalizar a parte de Transformação do dados vamos eliminar os registros duplicados, assim teremos uma lista com todos os tipos de ocorrência utilizados. Conseguimos isso com o step “Unique rows”, dentro da pasta “Transform”, nele conseguimos definir quais campos serão utilizados na comparação, neste caso só temos um campo e ele que iremos utilizar.

Removendo linhas duplicadas
Removendo linhas duplicadas

Por fim, vamos utilizar o step “Insert/Update” que está na aba “Output”, neste caso este step é importante pois ira atualizar os Tipos de Ocorrência que já existem no banco e, se existir uma nova, irá inserir este registro. Veja abaixo as suas definições:

Configurações de Insert/Update
Configurações de Insert/Update

Carga de Ocorrências

Assim como foi feito na transformação acima vamos percorrer todo o CSV, porém dessa vez vamos utilizar todos os registros. Também vamos fazer um “Lookup” na tabela de TIPO_OCORRENCIA para encontrar o ID do tipo a partir do valor que está no formato texto em cada linha. Veja como ficou a transformação:

Transformação: Carga de Ocorrências
Transformação: Carga de Ocorrências

O primeiro step desta transformação é exatamente igual ao da Carga de Tipos de Ocorrências, para o segundo vamos utilizar o “Database lookup”, com ele podemos buscar em uma tabela um determinado campo a partir de combinações de campos que temos em nosso Stream, como na imagem abaixo:

Database Lookup: Buscando o ID do tipo da ocorrência
Database Lookup: Buscando o ID do tipo da ocorrência

Neste caso vamos comparar o campo “tipo” do Stream com o campo “descricao” da tabela, utilizando como comparador o sinal de “=”. E na parte de baixo da tabela escolhemos qual campo queremos trazer da tabela pesquisada caso a condição acima seja atendida. Vamos habilitar as caixas “Habilitar cache?” e “Load all data from table”, a primeira guarda todos os valores que já foram encontrados em um cache, para que não seja necessário acessar o banco por um valor já encontrado, já o segundo campo faz um acesso prévio na tabela e guarda todos os valores na memória, assim acessa o banco apenas uma vez. Para tabelas grandes a primeira opção é mais recomendada.

Por fim vamos inserir os dados na tabela de ocorrências, para isso vamos utilizar o step “Table Output”, dentro da pasta “Output”, pois, neste caso, a cada vez que for executada a rotina todos os dados serão apagados e inseridos novamente. Para isto temos de deixar marcada a opção “Truncate table”, também vamos marcar a opção “Specify database fieds” para mapearmos as colunas que vem do nosso Stream para as colunas da tabela. Veja na imagem abaixo:

Inserir dados na tabela de ocorrências
Inserir dados na tabela de ocorrências

Arquivos Utilizados

 

 

 

Conclusão

A primeira parte para o nosso Painel já está pronta, realizamos boa parte do trabalho de ETL, extraindo os dados da nossa fonte a colocando em um SGBD. Para isso combinamos um processo do PDI com uma rotina em Python, isso é muito comum pois existem várias ferramentas que podem facilitar o nosso trabalho. Por isso é importante conhecer várias ferramentas e métodos para conseguir utilizar o que vai te trazer melhor desempenho e produtividade.

Na segunda parte do artigo irei mostrar como criar e popular um Data Warehouse para os nossos dados, que servirá como base para o nosso Painel. Deixe seus comentários com dúvidas e/ou sugestões!

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *