Workshop de PostGIS que ministrei com o Ignacio Talavera (IMM - Intendencia de Montevideo) no V CONSEGI (Congresso Internacional Software Livre e Governo Eletrônico):
sexta-feira, 7 de dezembro de 2012
terça-feira, 11 de setembro de 2012
Extraindo indicadores do DATASUS com Shell, SED e AWK
O DATASUS (Departamento de Informática do SUS) disponibiliza na Internet informações que podem servir para subsidiar análises objetivas da situação sanitária, tomadas de decisão baseadas em evidências e elaboração de programas de ações de saúde.
Entre essas destacam-se as Informações de Saúde (vide link), produtos de ações integradas do governo brasileiro nessa área. Apesar de os dados estarem públicos em em diversos formatos, a sua extração automática não é tão trivial.
Neste artigo irei explorar ferramentas poderosas do GNU/Linux, em especial o meu tripleto favorito Shell, SED e AWK, além do utilitário cURL e do SGBD PostgreSQL, na missão de extrair de forma automática alguns indicadores do DATASUS disponíveis na Internet.
Apesar de basear-se em um caso altamente específico, os indicadores do DATASUS, as ideias e trechos de instruções contidas nesse texto podem servir para automatizar a obtenção de dados provenientes de outras fontes. Então vamos começar!
Antes de qualquer coisa, precisamos preparar as estruturas de banco de dados em que serão armazenados os indicadores coletados. No PostgreSQL, crie as estruturas segundo as instruções SQL a seguir:
Em seguida, crie um arquivo com o nome sugestivo carregar_indice_datasus.sh e inclua as seguintes intruções em linguagem Shell Script:
Ufa! Espero que tenha sido claro nessa explicação do script... :D
Grave o arquivo e agora tente executar o script! Sem especificar parâmetro, ele exibirá o seguinte:
E aí, demorou muito pra executar? Não mediu o tempo..? Sem problemas, execute isso agora:
Neste artigo irei explorar ferramentas poderosas do GNU/Linux, em especial o meu tripleto favorito Shell, SED e AWK, além do utilitário cURL e do SGBD PostgreSQL, na missão de extrair de forma automática alguns indicadores do DATASUS disponíveis na Internet.
-- criação do esquema específico CREATE SCHEMA indicador; -- criação da tabela de entrada CREATE TABLE indicador.datasus_entrada ( cod_munic integer, vlr_dado integer ); COMMENT ON TABLE indicador.datasus_entrada IS 'Tabela de entrada para os indicadores do DATASUS para os Municípios'; -- criação da tabela final CREATE TABLE indicador.datasus_municipio ( cod_munic integer NOT NULL, num_ano_mes integer NOT NULL, num_inter_hospi_sus integer, num_consu_medic_sus integer, num_estab_urgen_sus integer, num_equip_saude_famil integer, PRIMARY KEY (cod_munic, num_ano_mes) ); COMMENT ON TABLE indicador.datasus_municipio IS 'Indicadores mensais do DATASUS para os Municípios'; COMMENT ON COLUMN indicador.datasus_municipio.cod_munic IS 'Código do Município (IBGE)'; COMMENT ON COLUMN indicador.datasus_municipio.num_ano_mes IS 'Ano e Mês do Indicador (ex: 201209 => Set/2012)'; COMMENT ON COLUMN indicador.datasus_municipio.num_inter_hospi_sus IS 'Internações Hospitalares pelo SUS'; COMMENT ON COLUMN indicador.datasus_municipio.num_consu_medic_sus IS 'Consultas Médicas pelo SUS'; COMMENT ON COLUMN indicador.datasus_municipio.num_estab_urgen_sus IS 'Estabelecimentos com Atendimento de Urgência SUS'; COMMENT ON COLUMN indicador.datasus_municipio.num_equip_saude_famil IS 'Equipes de Saúde da Família';Repare que foram definidos comentários para as tabelas e colunas em questão, uma vez que o padrão de nomenclatura que costumo adotar pode não te parecer trivial. :D
Em seguida, crie um arquivo com o nome sugestivo carregar_indice_datasus.sh e inclua as seguintes intruções em linguagem Shell Script:
#!/bin/bash
if [ $# -ne 1 ]
then
echo "Sintaxe: $0 <indicador>"
echo "Indicadores: qrbr qabr aturgbr equipebr"
exit 1
fi
ind="$1"
# parametrizar de acordo com indicador especificado
case "$ind" in
"qrbr")
nom="Internações Hospitalares pelo SUS"
col="num_inter_hospi_sus"
sis="sih"
;;
"qabr")
nom="Consultas Médicas pelo SUS"
col="num_consu_medic_sus"
sis="sia"
;;
"aturgbr")
nom="Estabelecimentos com Atendimento de Urgência SUS"
col="num_estab_urgen_sus"
sis="cnes"
;;
"equipebr")
nom="Equipes de Saúde da Família"
col="num_equip_saude_famil"
sis="cnes"
;;
*)
echo "Índice desconhecido: $ind"
exit 2
esac
url="http://tabnet.datasus.gov.br/cgi/tabcgi.exe?$sis/cnv/$ind.def"
tmp="$ind.tmp"
res="$ind.res"
csv="$ind.csv"
echo "Indicador: $nom ($ind)"
# buscar último período disponível
echo "Buscando último período..."
curl -s $url > $tmp
arq=$(awk 'BEGIN{FS="\""}/dbf" SELECTED/{print$2}' $tmp)
mes=$(echo $arq | sed 's/^.*\([0-9]\{2\}\)\.dbf/\1/')
ano=$(echo $arq | sed 's/^.*\([0-9]\{2\}\)[0-9]\{2\}\.dbf/20\1/')
rm -f $tmp
echo "Último período disponível: $mes/$ano (arquivo $arq)"
# definir parâmetros da requisição
case "$ind" in
"qrbr")
par="Linha=Munic%EDpio&Coluna=--N%E3o-Ativa--&Incremento=Interna%E7%F5es&Arquivos=$arq&SMicrorregi%E3o=TODAS_AS_CATEGORIAS__&SReg.Metropolitana=TODAS_AS_CATEGORIAS__&SAglomerado_urbano=TODAS_AS_CATEGORIAS__&SCapital=TODAS_AS_CATEGORIAS__&SUnidade_Federa%E7%E3o=TODAS_AS_CATEGORIAS__&SProcedimento=TODAS_AS_CATEGORIAS__&SGrupo_procedimento=TODAS_AS_CATEGORIAS__&SSubgrupo_proced.=TODAS_AS_CATEGORIAS__&SForma_organiza%E7%E3o=TODAS_AS_CATEGORIAS__&SComplexidade=TODAS_AS_CATEGORIAS__&SFinanciamento=TODAS_AS_CATEGORIAS__&SRubrica_FAEC=TODAS_AS_CATEGORIAS__&SRegra_contratual=TODAS_AS_CATEGORIAS__&SNatureza=TODAS_AS_CATEGORIAS__&SRegime=TODAS_AS_CATEGORIAS__&SNatureza_jur%EDdica=TODAS_AS_CATEGORIAS__&SEsfera_jur%EDd%EDca=TODAS_AS_CATEGORIAS__&SGest%E3o=TODAS_AS_CATEGORIAS__&zeradas=exibirlz&formato=prn&mostre=Mostra"
;;
"qabr")
par="Linha=Munic%EDpio&Coluna=--N%E3o-Ativa--&Incremento=Qtd.apresentada&Arquivos=$arq&SMicrorregi%E3o=TODAS_AS_CATEGORIAS__&SReg.Metropolitana=TODAS_AS_CATEGORIAS__&SAglomerado_urbano=TODAS_AS_CATEGORIAS__&SCapital=TODAS_AS_CATEGORIAS__&SUnidade_Federa%E7%E3o=TODAS_AS_CATEGORIAS__&SProcedimento=1075&SProcedimento=1076&SProcedimento=1077&SProcedimento=1078&SProcedimento=1079&SProcedimento=1080&SProcedimento=1081&SProcedimento=1082&SProcedimento=1083&SProcedimento=1084&SProcedimento=1085&SProcedimento=1086&SProcedimento=1087&SProcedimento=1088&SProcedimento=1089&SProcedimento=1090&SProcedimento=1091&SProcedimento=1092&SProcedimento=1093&SProcedimento=1094&SProcedimento=1114&SProcedimento=1115&SProcedimento=1117&SProcedimento=1125&SProcedimento=1126&SProcedimento=1127&SProcedimento=1128&SProcedimento=1129&SProcedimento=1130&SProcedimento=1131&SProcedimento=1132&SProcedimento=1133&SProcedimento=1134&SProcedimento=1135&SProcedimento=1136&SProcedimento=1137&SProcedimento=1138&SProcedimento=1139&SProcedimento=1140&SProcedimento=1141&SProcedimento=1142&SProcedimento=1143&SProcedimento=1144&SProcedimento=1145&SProcedimento=1146&SProcedimento=1147&SProcedimento=1148&SProcedimento=1149&SProcedimento=1150&SProcedimento=1169&SProcedimento=1170&SProcedimento=1189&SProcedimento=1190&SProcedimento=1191&SProcedimento=1192&SProcedimento=1193&SProcedimento=1194&SProcedimento=1195&SProcedimento=1196&SProcedimento=1197&SGrupo_procedimento=TODAS_AS_CATEGORIAS__&SSubgrupo_proced.=TODAS_AS_CATEGORIAS__&SForma_organiza%E7%E3o=TODAS_AS_CATEGORIAS__&SComplexidade=TODAS_AS_CATEGORIAS__&SFinanciamento=TODAS_AS_CATEGORIAS__&SSubtp_Financiament=TODAS_AS_CATEGORIAS__&SRegra_contratual=TODAS_AS_CATEGORIAS__&SCar%E1ter_Atendiment=TODAS_AS_CATEGORIAS__&SGest%E3o=TODAS_AS_CATEGORIAS__&SDocumento_registro=TODAS_AS_CATEGORIAS__&SEsfera_administrat=TODAS_AS_CATEGORIAS__&STipo_de_prestador=TODAS_AS_CATEGORIAS__&SAprova%E7%E3o_produ%E7%E3o=TODAS_AS_CATEGORIAS__&zeradas=exibirlz&formato=prn&mostre=Mostra"
;;
"aturgbr")
par="Linha=Munic%EDpio&Coluna=--N%E3o-Ativa--&Incremento=SUS&Arquivos=$arq&SRegi%E3o=TODAS_AS_CATEGORIAS__&SUnidade_Federa%E7%E3o=TODAS_AS_CATEGORIAS__&SCapital=TODAS_AS_CATEGORIAS__&SMicrorregi%E3o=TODAS_AS_CATEGORIAS__&SReg.Metropolitana=TODAS_AS_CATEGORIAS__&SAglomerado_urbano=TODAS_AS_CATEGORIAS__&SEnsino%2FPesquisa=TODAS_AS_CATEGORIAS__&SEsfera_Administrativa=TODAS_AS_CATEGORIAS__&SNatureza=TODAS_AS_CATEGORIAS__&STipo_de_Estabelecimento=TODAS_AS_CATEGORIAS__&STipo_de_Gest%E3o=TODAS_AS_CATEGORIAS__&STipo_de_Prestador=TODAS_AS_CATEGORIAS__&zeradas=exibirlz&formato=prn&mostre=Mostra"
;;
"equipebr")
par="Linha=Munic%EDpio&Coluna=--N%E3o-Ativa--&Incremento=Quantidade&Arquivos=$arq&SRegi%E3o=TODAS_AS_CATEGORIAS__&SUnidade_Federa%E7%E3o=TODAS_AS_CATEGORIAS__&SCapital=TODAS_AS_CATEGORIAS__&SMicrorregi%E3o=TODAS_AS_CATEGORIAS__&SReg.Metropolitana=TODAS_AS_CATEGORIAS__&SAglomerado_urbano=TODAS_AS_CATEGORIAS__&SEnsino%2FPesquisa=TODAS_AS_CATEGORIAS__&SEsfera_Administrativa=TODAS_AS_CATEGORIAS__&SNatureza=TODAS_AS_CATEGORIAS__&STipo_de_Estabelecimento=TODAS_AS_CATEGORIAS__&STipo_de_Gest%E3o=TODAS_AS_CATEGORIAS__&STipo_de_Prestador=TODAS_AS_CATEGORIAS__&STipo_da_Equipe=1&STipo_da_Equipe=2&STipo_da_Equipe=3&zeradas=exibirlz&formato=prn&mostre=Mostra"
;;
esac
# obter dados via requisição HTTP POST
echo "Buscando dados do servidor..."
curl -s -d $par $url > $res
# extrair trecho e converter em formato CSV
sed -n '/<PRE>/,/PRE>/p' $res | tr -d '\r' | sed \
-e '/^"[0-9]/!d' -e 's/^"\([0-9]\+\).*;/\1;/' \
-e '/^000000/d' -e 's/;-$/;0/' > $csv
# manipulações no banco de dados
export PGDATABASE="banco"
export PGHOST="servidor"
export PGUSER="usuario"
# popular tabela de entrada
echo "Populando tabela de entrada..."
cat $csv | psql -c "TRUNCATE TABLE indicador.datasus_entrada; COPY indicador.datasus_entrada FROM stdin CSV DELIMITER ';'"
# popular tabela final
echo "Populando tabela final..."
echo "UPDATE indicador.datasus_municipio SET $col = NULL WHERE num_ano_mes = $ano$mes" | psql
echo "INSERT INTO indicador.datasus_municipio (cod_munic, num_ano_mes, $col) SELECT cod_munic, $ano$mes, vlr_dado FROM indicador.datasus_entrada" | psql
echo "UPDATE indicador.datasus_municipio a SET $col = vlr_dado FROM indicador.datasus_entrada b WHERE a.cod_munic = b.cod_munic AND num_ano_mes = $ano$mes" | psql
psql -c "TRUNCATE TABLE indicador.datasus_entrada; ANALYZE indicador.datasus_municipio"
Lembre de dar permissão de execução nesse arquivo antes de rodá-lo. Esse script realiza os seguintes procedimentos:
- valida a informação de um parâmetro (entre "qrbr", "qabr", "aturgbr" e "equipebr") que identifica qual dos indicadores do DATASUS ele irá buscar
- utiliza a ferramenta cURL para fazer uma requisição HTTP do tipo GET e com isso obter o último período disponibilizado (i.e., Mês/Ano) para o indicador
- através de SED e AWK, extrai os valores de ano, mês e nome do arquivo a ser recuperado
- efetua uma segunda requisição HTTP com o cURL, desta vez do tipo POST, para agora assim obter os dados finais em formato pseudo-CSV
- usando SED, faz o parse da página HTML obtida e extrai apenas os dados desejados, transformando-os em um arquivo do tipo CSV puro
- com o auxílio do utilitário psql, interface cliente do PostgreSQL via terminal, atualiza as tabelas que criamos
Ufa! Espero que tenha sido claro nessa explicação do script... :D
Grave o arquivo e agora tente executar o script! Sem especificar parâmetro, ele exibirá o seguinte:
$ ./carregar_indice_datasus.sh Sintaxe: ./carregar_indice_datasus.sh <indicador> Indicadores: qrbr qabr aturgbr equipebrChegou o grande momento! Ao especificar "qrbr", temos a seguinte saída:
$ ./carregar_indice_datasus.sh qrbr Indicador: Internações Hospitalares pelo SUS (qrbr) Buscando último período... Último período disponível: 06/2012 (arquivo qrbr1206.dbf) Buscando dados do servidor... Populando tabela de entrada... Populando tabela final... UPDATE 0 INSERT 0 5652 UPDATE 5652 ANALYZEExecuções subsequentes serão um pouco diferentes. Veja:
$ ./carregar_indice_datasus.sh aturgbr Indicador: Estabelecimentos com Atendimento de Urgência SUS (aturgbr) Buscando último período... Último período disponível: 06/2012 (arquivo stbr1206.dbf) Buscando dados do servidor... Populando tabela de entrada... Populando tabela final... UPDATE 5652 ERROR: duplicate key value violates unique constraint "datasus_municipio_pkey" DETAIL: Key (cod_munic, num_ano_mes)=(110001, 201206) already exists. UPDATE 5652 ANALYZEComo produtos intermediários, teremos os arquivos no formato CSV. Veja o conteúdo deles:
$ head -3 *.csv ==> aturgbr.csv <== 110001;1 110037;1 110040;1 ==> equipebr.csv <== 110001;5 110037;5 110040;3 ==> qabr.csv <== 110001;13718 110037;3216 110040;0 ==> qrbr.csv <== 110001;173 110037;60 110040;31Mas o nosso objetivo final era armazenar esses dados no banco de dados. Olha só como ficou:
$ psql -c "SELECT * FROM indicador.datasus_municipio LIMIT 5"
cod_munic | num_ano_mes | num_inter_hospi_sus | num_consu_medic_sus | num_estab_urgen_sus | num_equip_saude_famil
-----------+-------------+---------------------+---------------------+---------------------+-----------------------
110001 | 201206 | 173 | 13718 | 1 | 5
110037 | 201206 | 60 | 3216 | 1 | 5
110040 | 201206 | 31 | 0 | 1 | 3
110034 | 201206 | 21 | 0 | 1 | 2
110002 | 201206 | 438 | 40356 | 11 | 13
(5 rows)
Ou seja, para cada município (i.e., código do IBGE) e período (i.e., ano/mês), obtivemos quatro indicadores de saúde do DATASUS.
$ time for i in qrbr qabr aturgbr equipebr; do ./carregar_indice_datasus.sh $i; doneEssa instrução executará de forma serializada o script para cada possível indicador. Observe o final da saída. Pra mim apareceu o seguinte:
real 0m15.776s user 0m0.976s sys 0m0.260sOu seja, levou menos de 16 segundos para coletar mais de 22 mil linhas de dados do DATASUS transmitidos em 700 kB em um total de 8 requisições HTTP e ainda gravar tudo no banco de dados de forma estruturada... E isso via terminal e sem qualquer intervenção humana - porque pessoas erram! :D
quinta-feira, 6 de setembro de 2012
Fazendo Mágica com NAT no iptables
O GNU/Linux é um sistema operacional tão poderoso que nos permite interferir no tráfego da interface de rede de forma simplesmente mágica através de ferramentas de rede como iptables.
O iptables é muito conhecido na implementação de firewalls no Linux. Todavia, ele oferece muitos outros recursos, e dentre eles o redirecionamento de pacotes.
Para demonstrar esse potencial, ilustraremos duas situações interessantes:
- redirecionar a porta TCP 81 para a 80 na mesma máquina
- redirecionar a porta TCP 5436 local para a 5432 de máquina em outra rede
Serão considerados 10.220.10.62/16 como endereço IP local e 10.1.2.71/16 como endereço IP remoto. Atenção: a maioria das instruções citadas neste artigo deve ser executada pelo "root" ou outro usuário com poderes de super-vaca!
Antes de qualquer coisa, devemos nos certificar de que as regras existentes do NAT no iptables tenham sido reinicializadas. Para limpar as tabelas e cadeias (chains) e zerar os respectivos contadores, executamos as instruções a seguir:
iptables -t nat -F iptables -t nat -X iptables -t nat -Z
O redirecionamento de portas locais pode ser feito com o alvo (target) REDIRECT.
O REDIRECT redireciona o pacote para a própria máquina alterando o IP de destino para o endereço primário da interface de entrada (pacotes gerados localmente são mapeados para o endereço 127.0.0.1).
Assim, afim de redirecionar a porta TCP 81 para a 80 na mesma máquina, executamos:
iptables -t nat -A PREROUTING -p tcp --dport 81 -j REDIRECT --to-port 80
É interessante analisar se o tráfego está chegando à essa porta 81. Para isso, podemos usar o alvo LOG, o qual provocará a inclusão de uma entrada no /var/log/syslog a cada requisição.
O alvo LOG liga o registro dos pacotes que se encaixam em determinado critério pelo log do kernel (o qual pode ser lido com dmesg ou syslogd). É extremamente útil para ser usado em conjunto com regras que descartam o pacote (i.e., alvos DROP ou REJECT).
Para o nosso caso, basta executar a instrução a seguir para habilitar o log:
iptables -t nat -A PREROUTING -p tcp --dport 81 -j LOG --log-prefix "[Porta 81] "
Executamos o comando abaixo para verificar as regras aplicadas à tabela NAT do iptables:
iptables -t nat -L -n -v
Para testar se o redirecionamento está acontecendo como esperado, abra um novo terminal e execute a instrução a seguir para acompanhar no log do Linux as requisições que chegam à porta 81:
tail -f /var/log/syslog | grep 'Porta 81'
Para este caso, o teste deve ser disparado de uma máquina externa, digamos, do endereço 10.220.8.250/16. Podemos usar o telnet executando essa instrução:
telnet 10.220.10.62 81
Como se trata de um serviço Web, outra opção é usar o próprio protocolo HTTP, através da ferramenta curl. Eis um exemplo:
curl -v http://10.220.10.62:81/
Fácil, não?! Vejamos agora a segunda situação, um pouco mais complicada.
O redirecionamento de IPs precisa ser habilitado no kernel do Linux. Para fazer isso, basta executar esse comando:
echo 1 > /proc/sys/net/ipv4/ip_forward
Para garantir que esse parâmetro do kernel esteja ligado durante uma possível reinicialização da máquina, uma sugestão é adicionar tal configuração no sysctl.conf:
echo "net.ipv4.ip_forward = 1" >> /etc/sysctl.conf
Voltando ao iptables, usaremos um outro alvo, o DNAT.
O DNAT determina que o endereço de destino deve ser modificado (assim como todos os demais pacotes da respectiva conexão) e as regras devem parar de ser examinadas.
Assim, para redirecionar a porta 5436 local (10.220.10.62) para a 5432 de outra máquina (10.1.2.71), executamos:
iptables -t nat -A PREROUTING -s 10.220.0.0/16 -p tcp --dport 5436 -j DNAT --to-destination 10.1.2.71:5432
A opção "-s" restringe a origem dos pacotes à subrede da máquina local. Como estamos redirecionando o pacote para uma máquina em outra subrede, precisamos usar o alvo MASQUERADE.
iptables -t nat -A POSTROUTING -j MASQUERADE
Da mesma forma que no caso anterior, podemos ligar o log do Linux para capturar as requisições nessa porta TCP:
iptables -t nat -A PREROUTING -p tcp --dport 5436 -j LOG --log-prefix "[Porta 5436] "
E em seguida acompanhar as mudanças através desse comando:
tail -f /var/log/syslog | grep 'Porta 5436'
Para testar essa nova regra, abra um terminal em outra máquina na mesma subrede e use o telnet:
telnet 10.220.10.62 5436
Como trata-se de um serviço do SGBD PostgreSQL, podemos testar definitivamente usando o cliente psql:
psql -h 10.220.10.62 -p 5436 -U usuario banco
Uma alternativa para acompanhar o tráfego de rede nessas regras de NAT no iptables é a ferramenta tcpdump. Através dela podemos visualizar os IPs com setas mostrando a direção do tráfego, e com isso verificar se as requisições estão chegando e se as respectivas respostas estão voltando.
Por exemplo, para analisar o tráfego que chega à porta 5436 local, podemos usar:
tcpdump -n -i eth0 port 5436
Já para verificar as requisições entre a máquina local e a máquina remota, uma opção seria:
tcpdump -n -i eth0 host 10.1.2.71
Muito simples e prático!
sexta-feira, 24 de agosto de 2012
Desvendando os Microdados do ENEM 2010
Estava olhando o conteúdo do Portal Brasileiro de Dados Abertos (vide link) e me deparei com os Microdados do ENEM[1] fornecidos pelo INEP [2]. Fiquei interessado nessas informações e resolvi baixar os arquivos da última edição disponibilizada do exame: 2010.
Achei muito organizado o material fornecido pela instituição ao público, e então resolvi destrinchar os dados usando uma ferramenta mais apropriada: o SGBD de código aberto mais avançado do mundo, o PostgreSQL[3].
O principal conteúdo no arquivo ZIP é o "DADOS_ENEM_2010.txt", um arquivo de texto com 4.626.094 linhas e míseros 4,4 GB...! Cada linha representa um inscrito no exame, e os campos dividem-se em seções de variáveis como CONTROLE DO INSCRITO, CONTROLE DA ESCOLA, CIDADE DA PROVA, PROVA OBJETIVA e PROVA DE REDAÇÃO.
A lista de informações de cada inscrito é extensa, por isso resolvi extrair apenas algun campos de maior interessante. Fazemos isso usando as instruções a seguir no Linux:
Uma vez conectado ao banco recém-criado, criaremos a tabela "enem10" usando a seguinte instrução SQL:
Assim que o processo de carga finalizar, é preciso executar as instruções SQL abaixo para ajustes finais nos dados:
Para criar a tabela "nota_media_cidade", uma agregação da média e desvio padrão das notas e quantidades de inscritos para cada cidade (i.e., municípios com mais de 1.000 alunos), podemos executar a seguinte instrução SQL (note que desconsideramos os candidatos que não compareceram às provas):
Pronto! Agora podemos começar a fazer as análises dos dados usando essas tabelas e visão. Eis alguns exemplos a seguir.
(Vide "nota_media_cidade")
(Vide "nota_media_estado")
(Vide "nota_prova")
Bom, através dos dados pude constatar que o ensino médio brasileiro de qualidade (pelo menos no ano de 2010) está polarizado no eixo Sudeste-Sul do país. Parabéns a Niterói - RJ, Florianópolis - SC e Valinhos - SP, as cidades campeãs no ensino! Tomara que o Ministério da Educação tenha ideia de como homogeneizar (para melhor!) o ensino em todas as regiões do Brasil.
Com relação às notas, a mídia limita-se a divulgar apenas as mínimas e máximas (vide [5,6]). Entretanto, qualquer profissional com conhecimento estatístico sabe que o mais importante nesse tipo de análise são as médias e os desvios padrão. Um exemplo disso é na prova de matemática, onde ocorreu a maior nota das objetivas, porém também a maior diferença entre as notas dos candidatos. Ou seja, é uma disciplina cujo ensino precisa ser reforçado! :D
[1] Sobre o Enem - http://portal.inep.gov.br/web/enem/sobre-o-enem/
[2] Microdados do Enem - http://dados.gov.br/dataset/microdados-do-exame-nacional-do-ensino-medio-enem/
[3] PostgreSQL - http://www.postgresql.org/
[4] pgloader - http://pgfoundry.org/projects/pgloader/
[5] Confira as notas mínima e máxima das provas do Enem (Estadão) - http://www.estadao.com.br/noticias/vidae,confira-as-notas-minima-e-maxima-das-provas-do-enem,666251,0.htm
[6] Como calcular a nota do Enem? - http://vestibular.brasilescola.com/enem/como-calcular-nota-enem.htm
O principal conteúdo no arquivo ZIP é o "DADOS_ENEM_2010.txt", um arquivo de texto com 4.626.094 linhas e míseros 4,4 GB...! Cada linha representa um inscrito no exame, e os campos dividem-se em seções de variáveis como CONTROLE DO INSCRITO, CONTROLE DA ESCOLA, CIDADE DA PROVA, PROVA OBJETIVA e PROVA DE REDAÇÃO.
A lista de informações de cada inscrito é extensa, por isso resolvi extrair apenas algun campos de maior interessante. Fazemos isso usando as instruções a seguir no Linux:
cat DADOS_ENEM_2010.txt | cut -b 1-12,21-179,533-572,951,997-1006 > enem10a.txtO arquivo resultante "enem10a.txt" fica bem menor, com cerca de 980 MB... Os dados nele ainda não estão perfeitos: existem valores em branco em colunas como código e nome de município e nas notas. Para o código do município, usamos o SED com a seguinte instrução para inserir zeros no lugar de vazio, o que será tratado posteriormente:
sed 's/^\(.\{12\}\)\s\{7\}/\10000000/' enem10a.txt > enem10b.txt
Agora temos outro arquivo de texto com 980 MB, pronto para ser carregado no SGBD. É preciso então criar o banco de dados "enem". Podemos fazer isso usando o comando createdb.
Uma vez conectado ao banco recém-criado, criaremos a tabela "enem10" usando a seguinte instrução SQL:
CREATE TABLE enem10 ( num_inscr int8, cod_munic int, nom_munic varchar, sig_uf char(2), idc_cn int2, idc_ch int2, idc_lc int2, idc_mt int2, not_cn numeric(6,2), not_ch numeric(6,2), not_lc numeric(6,2), not_mt numeric(6,2), idc_rd char(1), not_rd numeric(6,2) );Veja que começamos a normalizar os dados, principalmente pela especificação de restrições de tipos de dados para cada uma das colunas. Além disso, tabela e cada uma de duas colunas serão melhor documentadas se dotadas de descrições. Isso pode ser feito através dos comandos de criação de comentários abaixo:
COMMENT ON TABLE enem10 IS 'Microdados do Exame Nacional do Ensino Médio 2010'; COMMENT ON COLUMN enem10.num_inscr IS 'Número de inscrição no ENEM 2010'; COMMENT ON COLUMN enem10.cod_munic IS 'Código do Município em que o inscrito mora'; COMMENT ON COLUMN enem10.nom_munic IS 'Nome do município em que o inscrito mora '; COMMENT ON COLUMN enem10.sig_uf IS 'Código da Unidade da Federação do inscrito no Enem'; COMMENT ON COLUMN enem10.idc_cn IS 'Presença à prova objetiva de Ciências da Natureza'; COMMENT ON COLUMN enem10.idc_ch IS 'Presença à prova objetiva de Ciências Humanas'; COMMENT ON COLUMN enem10.idc_lc IS 'Presença à prova objetiva de Linguagens e Códigos'; COMMENT ON COLUMN enem10.idc_mt IS 'Presença à prova objetiva de Matemática'; COMMENT ON COLUMN enem10.not_cn IS 'Nota da prova de Ciências da Natureza '; COMMENT ON COLUMN enem10.not_ch IS 'Nota da prova de Ciências Humanas'; COMMENT ON COLUMN enem10.not_lc IS 'Nota da prova de Linguagens e Códigos'; COMMENT ON COLUMN enem10.not_mt IS 'Nota da prova de Matemática'; COMMENT ON COLUMN enem10.idc_rd IS 'Presença à redação'; COMMENT ON COLUMN enem10.not_rd IS 'Nota da prova de redação';Para dar carga de maneira mais eficiente no PostgreSQL, além de um tuning básico, podemos utilizar a ferramenta pgloader [4]. Para isso, após instalar o pacote "pgloader", crie um arquivo de configurações de nome "pgloader.conf" com o seguinte conteúdo:
[pgsql] base = enem log_file = /tmp/pgloader.log ;log_min_messages = DEBUG client_min_messages = WARNING client_encoding = 'utf-8' lc_messages = C ;pg_option_client_encoding = 'utf-8' ;pg_option_standard_conforming_strings = on pg_option_work_mem = 512MB copy_every = 10000 commit_every = 50000 null = " " empty_string = "" max_parallel_sections = 4 [enem10] table = enem10 format = fixed filename = enem10b.txt columns = * fixed_specs = num_inscr:0:12, cod_munic:12:7, nom_munic:19:150, sig_uf:169:2, idc_cn:171:1, idc_ch:172:1, idc_lc:173:1, idc_mt:174:1, not_cn:175:9, not_ch:184:9, not_lc:193:9, not_mt:202:9, idc_rd:211:1, not_rd:212:9Esse arquivo especificará ao pgloader de que forma o arquivo de entrada "enem10b.txt" será lido para alimentar a tabela "enem10" no banco de dados. Para maior desempenho, é utilizado o comando COPY (e não INSERT INTO) a cada 10 mil linhas e as transações são efetivadas a cada 50 mil registros. O grande pulo do gato é a substituição de espaços em branco pelo valor nulo. Para iniciar a carga, basta executar pgloader nesse diretório.
Assim que o processo de carga finalizar, é preciso executar as instruções SQL abaixo para ajustes finais nos dados:
UPDATE enem10 SET nom_munic = trim(nom_munic); UPDATE enem10 SET cod_munic = null WHERE cod_munic = 0;Confira então se a tabela "enem10" possui as 4,6 milhões de linhas referentes a cada inscrito no exame de 2010. Eis um exemplo do conteúdo dessa tabela:
enem=# SELECT * FROM enem10 LIMIT 10; num_inscr | cod_munic | nom_munic | sig_uf | idc_cn | idc_ch | idc_lc | idc_mt | not_cn | not_ch | not_lc | not_mt | idc_rd | not_rd --------------+-----------+----------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+-------- 200000382760 | 2105302 | IMPERATRIZ | MA | 1 | 1 | 1 | 1 | 545.40 | 598.20 | 589.00 | 502.10 | P | 550.00 200004076118 | 3202306 | GUACUI | ES | 1 | 1 | 1 | 1 | 434.10 | 505.80 | 439.00 | 495.30 | P | 475.00 200001265338 | 4309209 | GRAVATAI | RS | 1 | 1 | 1 | 1 | 491.10 | 598.40 | 528.70 | 322.50 | P | 450.00 200003174558 | 2211001 | TERESINA | PI | 1 | 1 | 1 | 1 | 499.90 | 521.10 | 479.10 | 411.50 | P | 875.00 200000277562 | 1501709 | BRAGANCA | PA | 1 | 1 | 1 | 1 | 479.70 | 583.90 | 447.20 | 398.60 | P | 575.00 200000104197 | 2800670 | BOQUIM | SE | 1 | 1 | 1 | 1 | 341.90 | 438.40 | 360.00 | 370.70 | P | 550.00 200004343078 | 3139409 | MANHUACU | MG | 1 | 1 | 1 | 1 | 499.00 | 610.90 | 452.00 | 513.80 | P | 450.00 200001011958 | 1502400 | CASTANHAL | PA | 1 | 1 | 1 | 1 | 597.80 | 599.30 | 517.80 | 586.60 | P | 700.00 200002382852 | 3106200 | BELO HORIZONTE | MG | 1 | 1 | 1 | 1 | 506.50 | 623.70 | 555.50 | 530.10 | P | 725.00 200000757106 | 1709500 | GURUPI | TO | 1 | 1 | 1 | 1 | 494.70 | 534.10 | 558.00 | 430.80 | P | 800.00 (10 rows)A essa altura você já deve ter percebido que trabalhar com tamanho volume de dados no SGBD não é nada trivial. As consultas tendem a ser mais lentas a cada vez que uma varredura sequencial de tabela (i.e., full scan) é invocado. Para minimizar esse problema, podemos criar tabelas totalizadoras.
Para criar a tabela "nota_media_cidade", uma agregação da média e desvio padrão das notas e quantidades de inscritos para cada cidade (i.e., municípios com mais de 1.000 alunos), podemos executar a seguinte instrução SQL (note que desconsideramos os candidatos que não compareceram às provas):
SELECT nom_munic AS municipio, sig_uf AS uf, avg(not_cn + not_ch + not_lc + not_mt + not_rd)::int AS media, stddev(not_cn + not_ch + not_lc + not_mt + not_rd)::int AS desvio, count(num_inscr) AS inscritos INTO nota_media_cidade FROM enem10 WHERE cod_munic IS NOT NULL AND idc_cn = 1 AND idc_ch = 1 AND idc_lc = 1 AND idc_mt = 1 AND idc_rd = 'P' GROUP BY nom_munic, sig_uf HAVING count(num_inscr) > 1000 ORDER BY media DESC;Outra análise interessante é criar a tabela "nota_media_estado", uma agregação da média, desvio padrão, mínima e máxima das notas e quantidades de inscritos para cada Unidade da Federação (i.e., estado brasileiro). Para isso, executamos a instrução SQL abaixo:
SELECT sig_uf AS uf, avg(not_cn + not_ch + not_lc + not_mt + not_rd)::int AS media, stddev(not_cn + not_ch + not_lc + not_mt + not_rd)::int AS desvio, min(not_cn + not_ch + not_lc + not_mt + not_rd)::int AS minima, max(not_cn + not_ch + not_lc + not_mt + not_rd)::int AS maxima, count(num_inscr) AS inscritos INTO nota_media_estado FROM enem10 WHERE cod_munic IS NOT NULL AND idc_cn = 1 AND idc_ch = 1 AND idc_lc = 1 AND idc_mt = 1 AND idc_rd = 'P' GROUP BY sig_uf ORDER BY 2 DESC;Nas agregações anteriores, usamos a soma das notas dos candidatos nas 4 provas objetivas e na redação. Para obter o desempenho dos candidatos separadamente em cada uma das provas, podemos criar a tabela "nota_prova_geral" conforme instrução a seguir:
SELECT min(not_cn) AS min_cn, max(not_cn) AS max_cn, avg(not_cn)::numeric(6,2) AS med_cn, stddev(not_cn)::numeric(6,2) AS dsv_cn, min(not_ch) AS min_ch, max(not_ch) AS max_ch, avg(not_ch)::numeric(6,2) AS med_ch, stddev(not_ch)::numeric(6,2) AS dsv_ch, min(not_lc) AS min_lc, max(not_lc) AS max_lc, avg(not_lc)::numeric(6,2) AS med_lc, stddev(not_lc)::numeric(6,2) AS dsv_lc, min(not_mt) AS min_mt, max(not_mt) AS max_mt, avg(not_mt)::numeric(6,2) AS med_mt, stddev(not_mt)::numeric(6,2) AS dsv_mt, min(not_rd) AS min_rd, max(not_rd) AS max_rd, avg(not_rd)::numeric(6,2) AS med_rd, stddev(not_rd)::numeric(6,2) AS dsv_rd INTO nota_prova_geral FROM enem10 WHERE idc_cn = 1 AND idc_ch = 1 AND idc_lc = 1 AND idc_mt = 1 AND idc_rd = 'P';A fim de melhor entender os dados dessa tabela, podemos criar a visão "nota_prova" com esse comando SQL:
CREATE VIEW nota_prova AS SELECT 'Ciências da Natureza' AS prova, min_cn AS min, max_cn AS max, med_cn AS media, dsv_cn AS desvio FROM nota_prova_geral UNION SELECT 'Ciências Humanas', min_ch, max_ch, med_ch, dsv_ch FROM nota_prova_geral UNION SELECT 'Linguagens e Códigos', min_lc, max_lc, med_lc, dsv_lc FROM nota_prova_geral UNION SELECT 'Matemática', min_mt, max_mt, med_mt, dsv_mt FROM nota_prova_geral UNION SELECT 'Redação', min_rd, max_rd, med_rd, dsv_rd FROM nota_prova_geral ORDER BY 1;Como resultado, teremos as seguintes estruturas no banco de dados "enem":
enem=# \d+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+-------------------+-------+-------+------------+---------------------------------------------------
public | enem10 | table | hjort | 1452 MB | Microdados do Exame Nacional do Ensino Médio 2010
public | nota_media_cidade | table | hjort | 40 kB |
public | nota_media_estado | table | hjort | 8192 bytes |
public | nota_prova | view | hjort | 0 bytes |
public | nota_prova_geral | table | hjort | 16 kB |
(5 rows)
1. Quais são as cidades cujos alunos obtiveram as maiores médias?
| municipio | uf | media | desvio | inscritos |
|---|---|---|---|---|
| NITEROI | RJ | 2.935 | 420 | 9.328 |
| FLORIANOPOLIS | SC | 2.935 | 385 | 6.160 |
| VALINHOS | SP | 2.892 | 424 | 1.634 |
| NOVA FRIBURGO | RJ | 2.891 | 376 | 2.129 |
| SAO CAETANO DO SUL | SP | 2.888 | 402 | 2.092 |
| BOTUCATU | SP | 2.887 | 405 | 1.449 |
| ITAJUBA | MG | 2.886 | 361 | 2.647 |
| JUIZ DE FORA | MG | 2.884 | 399 | 11.411 |
| ARAXA | MG | 2.883 | 396 | 1.054 |
| ARARAQUARA | SP | 2.882 | 390 | 3.214 |
| CATANDUVA | SP | 2.863 | 408 | 1.115 |
| PATOS DE MINAS | MG | 2.858 | 392 | 1.767 |
| RIBEIRAO PRETO | SP | 2.856 | 406 | 9.512 |
| SAO CARLOS | SP | 2.855 | 403 | 5.528 |
| SAO JOSE DO RIO PRETO | SP | 2.852 | 413 | 5.686 |
| BARBACENA | MG | 2.849 | 374 | 2.469 |
| JAU | SP | 2.847 | 409 | 1.233 |
| POUSO ALEGRE | MG | 2.846 | 379 | 2.340 |
| VICOSA | MG | 2.845 | 410 | 2.713 |
| UBERABA | MG | 2.843 | 420 | 4.058 |
| PORTO ALEGRE | RS | 2.843 | 389 | 24.059 |
| UBA | MG | 2.841 | 385 | 1.229 |
| BELO HORIZONTE | MG | 2.840 | 422 | 63.090 |
| POCOS DE CALDAS | MG | 2.839 | 355 | 2.614 |
| BLUMENAU | SC | 2.831 | 364 | 1.485 |
| PIRASSUNUNGA | SP | 2.830 | 396 | 1.317 |
| CAMPINAS | SP | 2.830 | 417 | 13.638 |
| VITORIA | ES | 2.828 | 439 | 8.475 |
| VOLTA REDONDA | RJ | 2.828 | 373 | 3.934 |
| RIO DE JANEIRO | RJ | 2.826 | 408 | 93.300 |
| SAO JOSE DOS CAMPOS | SP | 2.825 | 403 | 11.545 |
| JABOTICABAL | SP | 2.822 | 381 | 1.077 |
| DIVINOPOLIS | MG | 2.822 | 369 | 4.787 |
| SANTA MARIA | RS | 2.819 | 390 | 7.601 |
| SANTOS | SP | 2.817 | 404 | 5.195 |
| GUARATINGUETA | SP | 2.817 | 397 | 1.559 |
| LAVRAS | MG | 2.814 | 388 | 2.446 |
| JUNDIAI | SP | 2.814 | 392 | 5.232 |
| CONSELHEIRO LAFAIETE | MG | 2.813 | 382 | 2.429 |
| PASSOS | MG | 2.813 | 399 | 1.316 |
| CURITIBA | PR | 2.812 | 394 | 38.904 |
| SAO JOAO DEL REI | MG | 2.812 | 354 | 2.273 |
| CRICIUMA | SC | 2.810 | 399 | 1.232 |
| MARILIA | SP | 2.807 | 409 | 2.721 |
| LAGOA SANTA | MG | 2.806 | 391 | 1.038 |
| MOGI MIRIM | SP | 2.806 | 412 | 1.249 |
| NOVA LIMA | MG | 2.806 | 403 | 1.701 |
| SAO JOSE | SC | 2.805 | 350 | 2.478 |
| PIRACICABA | SP | 2.804 | 399 | 4.435 |
| TAUBATE | SP | 2.804 | 407 | 3.209 |
(Vide "nota_media_cidade")
2. Em quais estados os alunos obtiveram as maiores médias?
| uf | media | desvio | minima | maxima | inscritos |
|---|---|---|---|---|---|
| RJ | 2.764 | 392 | 1.588 | 4.242 | 220.383 |
| SP | 2.739 | 392 | 1.488 | 4.346 | 522.098 |
| MG | 2.737 | 387 | 1.513 | 4.239 | 368.835 |
| SC | 2.728 | 363 | 1.605 | 4.179 | 60.242 |
| PR | 2.704 | 370 | 1.563 | 4.111 | 159.061 |
| RS | 2.688 | 363 | 1.578 | 4.230 | 199.630 |
| DF | 2.675 | 383 | 1.579 | 4.111 | 40.292 |
| GO | 2.645 | 393 | 1.585 | 4.134 | 77.254 |
| CE | 2.641 | 408 | 1.543 | 4.221 | 146.687 |
| ES | 2.640 | 392 | 1.592 | 4.174 | 75.985 |
| PE | 2.626 | 380 | 1.450 | 4.186 | 155.738 |
| PB | 2.592 | 371 | 1.561 | 4.160 | 68.475 |
| MS | 2.590 | 371 | 1.601 | 4.127 | 69.354 |
| RN | 2.589 | 374 | 1.493 | 4.105 | 64.952 |
| PA | 2.587 | 368 | 1.475 | 4.131 | 120.739 |
| MT | 2.559 | 356 | 1.544 | 4.029 | 76.255 |
| AP | 2.551 | 335 | 1.615 | 3.782 | 9.413 |
| PI | 2.550 | 390 | 1.567 | 4.217 | 63.441 |
| RO | 2.548 | 346 | 1.570 | 4.094 | 33.387 |
| AL | 2.545 | 366 | 1.633 | 4.107 | 30.301 |
| BA | 2.545 | 366 | 1.443 | 4.166 | 264.654 |
| MA | 2.543 | 374 | 1.544 | 4.109 | 123.806 |
| RR | 2.527 | 350 | 1.622 | 3.873 | 8.921 |
| TO | 2.523 | 374 | 1.558 | 4.014 | 19.491 |
| AM | 2.514 | 345 | 1.517 | 4.084 | 80.490 |
| SE | 2.499 | 370 | 1.537 | 4.126 | 33.099 |
| AC | 2.491 | 344 | 1.621 | 3.995 | 9.887 |
(Vide "nota_media_estado")
3. Qual foi o desempenho geral dos alunos em cada uma das provas?
| prova | min | max | media | desvio |
|---|---|---|---|---|
| Ciências da Natureza | 297.30 | 844.70 | 489.05 | 79.96 |
| Ciências Humanas | 265.10 | 883.70 | 550.16 | 89.83 |
| Linguagens e Códigos | 254.00 | 810.10 | 512.00 | 77.28 |
| Matemática | 313.40 | 973.20 | 506.90 | 112.51 |
| Redação | 250.00 | 1000.00 | 596.44 | 132.34 |
(Vide "nota_prova")
Bom, através dos dados pude constatar que o ensino médio brasileiro de qualidade (pelo menos no ano de 2010) está polarizado no eixo Sudeste-Sul do país. Parabéns a Niterói - RJ, Florianópolis - SC e Valinhos - SP, as cidades campeãs no ensino! Tomara que o Ministério da Educação tenha ideia de como homogeneizar (para melhor!) o ensino em todas as regiões do Brasil.
Referências
[1] Sobre o Enem - http://portal.inep.gov.br/web/enem/sobre-o-enem/
[2] Microdados do Enem - http://dados.gov.br/dataset/microdados-do-exame-nacional-do-ensino-medio-enem/
[3] PostgreSQL - http://www.postgresql.org/
[4] pgloader - http://pgfoundry.org/projects/pgloader/
[5] Confira as notas mínima e máxima das provas do Enem (Estadão) - http://www.estadao.com.br/noticias/vidae,confira-as-notas-minima-e-maxima-das-provas-do-enem,666251,0.htm
[6] Como calcular a nota do Enem? - http://vestibular.brasilescola.com/enem/como-calcular-nota-enem.htm
Assinar:
Postagens (Atom)
