Parte 1: Capturando dados das medalhas nas Olimpíadas de múltiplas páginas web utilizando Power Query/ Power BI Desktop

Este post será divido em duas partes. O nosso objetivo principal é conseguir prever – com base nos dados históricos do total de medalhas – quantas medalhas os países devem ganhar nas próximas Olimpíadas. Na parte 1 iremos buscar e carregar os dados necessários sobre o histórico de medalhas conquistadas por cada país em todas as Olimpíadas utilizando o Power Query; na parte 2, iremos gerar os gráficos com as informações de previsão usando o software estatístico R.

Onde encontrar os dados

A etapa mais difícil em qualquer processo de análise de dados é encontrar os dados, garantir que eles estejam corretos e dentro de um formato esperado. Para buscar dados da quantidade de medalhas conquistada por cada país em cada uma das edições da história das Olimpíadas utilizaremos o site Sports Reference, que possui todas estas informações até os Jogos Olímpicos de 2012*.

Captura dos dados utilizando Power Query

Mas e como capturar dados da web? Para isso vamos utilizar o Power Query, que é um add-in para Excel, mas que também funciona dentro do Power BI Desktop, a ferramenta de criação de relatórios de BI da Microsoft. O Power Query é considerado uma ferramenta de “self-service ETL”, ou seja, permite que usuários sem tanto conhecimento de técnicas de extração de dados consigam buscar e transformar seus dados com facilidade. Utilizando tanto o Power Query para Excel como ele no Power BI Desktop conseguimos obter o mesmo resultado. No exemplo a seguir vou utilizar o Power Query para Excel 2013 para no final exportamos o resultado para o formato CSV.

Criando uma função para buscar dados de várias páginas web

Para ler uma página web utilizando o Power Query temos que, na aba “Power Query”, escolher a opção “From Web” e inserirmos a URL que desejamos nos conectar (na imagem abaixo, colocamos o endereço da página das Olimpíadas de 2012). Após isso, clicar em “OK”:

powerquery_web

Realizando a conexão no Power Query com a página web que possui dados de medalhas das Olimpíadas de 2012

Vamos precisar fazer algumas alterações na consulta do Power Query para conseguirmos ler todas as páginas, com dados de todas as Olimpíadas. Para verificarmos a consulta que o Power Query está fazendo, basta clicar na opção “Advanced Editor”:

powerquery_advanced_editor

Query inicial na opção “Advanced Editor”

A query abaixo é a nossa query inicial, que traz dados de uma única edição das Olimpíadas:

let
    Source = Web.Page(Web.Contents("http://www.sports-reference.com/olympics/summer/2012/")),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Rk", Int64.Type}, 
{"Country", type text}, {"Gold", Int64.Type}, {"Silver", Int64.Type}, 
{"Bronze", Int64.Type}, {"Total", Int64.Type}})
in
    #"Changed Type"

 

Para buscarmos dados desta e de todas as outras Olimpíadas ao mesmo tempo, precisaremos criar uma função que realize esta consulta a todas as páginas e acrescentar o parâmetro desta função (chamaremos de “year”) no lugar do ano. Para isso, vamos adicionar duas novas expressões no código:

  • (year as number) as table => : para criar a função;
  • “& Number.ToText(year) &” : para concatenar o ano na URL.

A nova query com a função criada ficará desta forma:

= (year as number) as table =>
let
    Source = Web.Page(Web.Contents
("http://www.sports-reference.com/olympics/summer/"& Number.ToText(year) &"")),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Rk", Int64.Type}, 
{"Country", type text}, {"Gold", Int64.Type}, {"Silver", Int64.Type}, 
{"Bronze", Int64.Type}, {"Total", Int64.Type}})
in
    #"Changed Type"

É possível testar o funcionamento da função clicando no botão “Invoke” e passando o ano de referência (imagem a seguir):

powerquery_function

Botão “Invoke” para testar o funcionamento da função

Vamos renomear a query para “GetData” (este nome será importante para a chamada da função a seguir).

Adicionando dados dos anos olímpicos à função

Testado o funcionamento da função, iremos criar uma tabela com os anos em que houve Olimpíadas para, dessa forma, podermos passar estes anos como parâmetros para a função, retornando assim os dados de todas as páginas. Na aba “Power Query”, vamos escolher criar uma nova conexão “From Other Sources”, “Blank Query”.

No primeiro passo, adicionaremos os anos dentro do espaço disponível para preenchimento da fórmula. Chamaremos esta consulta de “Medalhas”:

{2012,2008,2004,2000,1996,1992,1988,1984,1980,1976,1972,1968,1964,1960,1956,1952,1948,1936,1932,1928,1924,1920,1912,1908,1904,1900,1896}

No segundo, é preciso converter a lista gerada em tabela clicando na opção “To Table” (imagem abaixo):

powerquey_convert_to_table

Botão “To Table” dentro do Power Query

No terceiro passo, vamos adicionar uma nova coluna (imagem a seguir) nesta consulta “Medalhas” com a seguinte chamada da função:

GetData([Year])
powerquery_customcolumn

Botão “Add Custom Column” no Power Query

Feito isso, o Power Query irá carregar a página conforme a imagem abaixo. Vamos expandir todas as colunas para carregar todas linhas referentes às medalhas conquistadas pelo países em todas as edições das Olimpíadas para o Power Query (imagem abaixo):

powerquery_expand_columns

Tela que retorna dados de todas as Olimpíadas

Para finalizar esta etapa, como iremos trabalhar com séries temporais no R para realizar as previsões (parte 2), vamos criar uma coluna customizada de data para facilitar nosso trabalho (imagem abaixo):

powerquery_date_column

Adicionando coluna de data no formato YYYY-MM-DD

Complementando com dados das Olimpíadas Rio 2016

Feita esta etapa inicial, agora vamos adicionar as informações de medalhas das Olimpíadas Rio 2016. Para isso, vamos utilizar o quadro de medalhas da Wikipédia. De forma idêntica ao que fizemos na etapa anterior, basta criar uma nova consulta para buscar os dados da Wikipédia e realizarmos algumas transformações básicas para deixar esta nova consulta com as mesmas colunas que a consulta já criada.

Por fim, precisamos unir as informações de medalhas de 1896 até 2012 com a lista de medalhas de 2016. Para isso vamos utilizar a funcionalidade de “Append Queries” do Power Query. Selecionamos a nova consulta (chamamos de “Medalhas Atualizado”) e clicamos nesta opção (imagem abaixo) para juntar os dados da consulta “Medalhas” com os dados da consulta “Medalhas Atualizado”.

powerquery_append_medalhas_atualizado

Botão “Append Queries” do Power Query

Pronto! Basta clicar em “Close em Load” para carregar os dados para o Excel e depois salvá-los no formato CSV.

Na parte 2, iremos utilizar estes dados já organizados para criar os gráficos de forecast utilizando o R para prever o total de medalhas que os países devem conquistar nas próximas Olimpíadas.

P.S: Para facilitar, adicionei os scripts do Power Query (linguagem M) no GitHub (quem quiser reproduzir, é só copiar estes scripts e colar na aba “Advanced Editor” do Power Query para obter os dados sem precisar fazer todos estes passos). Quem quiser somente o arquivo CSV final para testar, também está lá.

*Acesso: 24/08/2016

Referências:

Iterating over multiple pages of web data using Power Query

Sugestão de leitura relacionada:

Tutorial: análise do Facebook com o Power BI Desktop

Parte 2: Gerando gráficos de forecast utilizando R para previsão de medalhas nas Olimpíadas

Anúncios

2 pensamentos sobre “Parte 1: Capturando dados das medalhas nas Olimpíadas de múltiplas páginas web utilizando Power Query/ Power BI Desktop

  1. Pingback: Parte 2: Gerando gráficos de forecast utilizando R para previsão de medalhas nas Olimpíadas | Blog Dataficação

  2. Pingback: Web scraping com Power BI Desktop: o que você precisa saber | Blog Dataficação

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s