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
