Oficina Futebol com o R

class: center, middle, inverse, title-slide

.title[
# Oficina Futebol com o R
]
.author[
### Caio O. Azevedo
]
.institute[
### UFSCar
]
.date[
### 2025-08-23
]

---

class: center, middle, animated slideInRight fadeOutLeft, animated slideInRight fadeOutLeft

Para acessar esse documento:

<img src="index_files/figure-html/unnamed-chunk-1-1.png" style="display: block; margin: auto;" />
---
class: middle, animated slideInRight fadeOutLeft, animated slideInRight fadeOutLeft

# Porque estudar o futebol?

* Além de fenômeno de interesse social e econômico, o futebol e os esportes em geral, tem mostrado ser uma área densa para pesquisas científicas, por permitir uma gama de análises pela regularidade, variabilidade e alta frequência de dados (Ter Weel, 2011).

* Dados provenientes dos esportes podem fornecer informações importantes sobre o comportamento do mercado de trabalho, sendo valioso laboratório. (Szymanski, 2003; Kahn, 2000)

---

class:

# Pré-requisitos

- R: faça o download da versão mais recente [neste link](https://cran.r-project.org/).

- RStudio: faça o download da versão mais recente [neste link](https://www.rstudio.com/products/rstudio/download/).

- Faça o dowload do arquivo inicial [aqui](https://github.com/caio-azevedo/auxiliar_futebol/archive/refs/heads/main.zip)

- Instale os seguintes pacotes:

``` r
install.packages("tidyverse")
```

---

# A base de dados

<table>
 <thead>
  <tr>
   <th style="text-align:right;"> id_match </th>
   <th style="text-align:right;"> season </th>
   <th style="text-align:left;"> date </th>
   <th style="text-align:left;"> home </th>
   <th style="text-align:left;"> score </th>
   <th style="text-align:left;"> away </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:left;"> 2x0 </td>
   <td style="text-align:left;"> Grêmio </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Guarani </td>
   <td style="text-align:left;"> 4x2 </td>
   <td style="text-align:left;"> Vasco da Gama </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-30 </td>
   <td style="text-align:left;"> Corinthians </td>
   <td style="text-align:left;"> 0x3 </td>
   <td style="text-align:left;"> Atlético MG </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 4 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-30 </td>
   <td style="text-align:left;"> Goiás </td>
   <td style="text-align:left;"> 2x2 </td>
   <td style="text-align:left;"> Paysandu </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 5 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-30 </td>
   <td style="text-align:left;"> Criciúma </td>
   <td style="text-align:left;"> 2x0 </td>
   <td style="text-align:left;"> Fluminense </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 6 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-30 </td>
   <td style="text-align:left;"> Cruzeiro </td>
   <td style="text-align:left;"> 2x2 </td>
   <td style="text-align:left;"> São Caetano </td>
  </tr>
</tbody>
</table>

* Extraída a partir do pacote "brasileirao" e contendo 8.406 observações e 6 variáveis.

---

# Objetivo

* Nosso objetivo então é transformar esse conjunto de dados, de forma a fornecer a colocação de cada time rodada por rodada, durante as temporadas de 2003 até a mais recente que foi a temporada 2023.

* É fácil perceber que para alcançarmos tal objetivo teremos de fazer manipulações nos dados, de forma a obter variáveis que ainda não estão explicítas na base de dados, como número de pontos, gols a favor, gols contra etc.

* Para esta finalidade faremos uso da programação em linguagem R.

---

# Manipulação de dados

* Assim como em tarefas do dia a dia, existem diversas formas de escrever o código para que possamos alcançar nosso objetivo.

* É comum escutar que o "melhor" código é aquele que possui o menor número de linhas de comando, no entanto, isso não é unanimidade, visto que se alcançarmos o objetivo, independente da forma, fomos eficazes no alcance de nossa meta.

* No entanto, ter um "código limpo" é de fato desejável, mas isso não está estritamente relacionado ao número de linhas de comando, mas sim com a questão de organização e estruturação.

* É neste sentido, que então podemos dizer que códigos mais reduzidos facilitam na organização e a estruturação do script, e por consequência são mais eficientes.

---

# Manipulação de dados

* A forma que escrevemos nosso código está fortemente relacionada a nossa prática enquanto usuários.

* Usuários mais experientes obviamente terão uma gama de informações já processadas, de forma a conseguir resolver certos problemas com menos dispêndio.

* Usuários novos tendem a querer resolver os problemas apenas com as ferramentas (funções e/ou pacotes) que conhece e acabam tendo um percurso maior para a resolução do mesmo problema.

* Aqui iremos tentar evidenciar isso, e portanto, fazer de duas formas: uma onde iremos utilizar um número menor de ferramentas, aproximando-se de um usuário novo e a outra fazendo uso de um conjunto maior de possibilidades da linguagem R, propiciando a comparação entre estas.

---

# Início

* Novamente deixando claro que existem diversas formas de resolvermos o problema proposto, mas aqui iremos propor apenas duas destas.

* Começaremos pela variável "score", ou seja, o placar da partida. Observe que ela é um caractere, do tipo "2x0", logo precisamos atribuir o valor 2 ao time mandante e o valor 0 ao time visitante, eliminando o "x".

```
## Rows: 9,166
## Columns: 6
## $ id_match <dbl> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18…
## $ season   <dbl> 2003, 2003, 2003, 2003, 2003, 2003, 2003, 2003, 2003, 2003, 2…
## $ date     <date> 2003-03-29, 2003-03-29, 2003-03-30, 2003-03-30, 2003-03-30, …
## $ home     <chr> "Athletico PR", "Guarani", "Corinthians", "Goiás", "Criciúma"…
## $ score    <chr> "2x0", "4x2", "0x3", "2x2", "2x0", "2x2", "1x1", "0x0", "1x1"…
## $ away     <chr> "Grêmio", "Vasco da Gama", "Atlético MG", "Paysandu", "Flumin…
```
---

## Trabalhando o "score" - com R base

* Muitos usuários que estão iniciando no R, tiveram algum contato com o Excel. Uma função bastante comum do Excel é o "DIREITA" e "ESQUERDA", ou seja, o raciocínio seria pegar o primeiro valor da direita e também o primeiro valor da esquerda.

* No R poderíamos utilizar a função *substr*, exemplo:

``` r
gols_mandante <- substr(x="2x0", start = 1, stop =1)
gols_mandante
```

```
## [1] "2"
```

``` r
gols_visitante <- substr(x="2x0", start = 3, stop =3)
gols_visitante
```

```
## [1] "0"
```

---

## Trabalhando o "score" - com R base

``` r
gols_mandante <- substr(dados$score,start = 1, stop = 1)
gols_visitante <- substr(dados$score,start = 3, stop = 3)

tabela1 <- cbind(dados, gols_mandante, gols_visitante) # empilhar colunas

tabela1 <- subset(tabela1, select = -c(score)) # excluir coluna
```
<table>
 <thead>
  <tr>
   <th style="text-align:right;"> id_match </th>
   <th style="text-align:right;"> season </th>
   <th style="text-align:left;"> date </th>
   <th style="text-align:left;"> home </th>
   <th style="text-align:left;"> away </th>
   <th style="text-align:left;"> gols_mandante </th>
   <th style="text-align:left;"> gols_visitante </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:left;"> Grêmio </td>
   <td style="text-align:left;"> 2 </td>
   <td style="text-align:left;"> 0 </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Guarani </td>
   <td style="text-align:left;"> Vasco da Gama </td>
   <td style="text-align:left;"> 4 </td>
   <td style="text-align:left;"> 2 </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-30 </td>
   <td style="text-align:left;"> Corinthians </td>
   <td style="text-align:left;"> Atlético MG </td>
   <td style="text-align:left;"> 0 </td>
   <td style="text-align:left;"> 3 </td>
  </tr>
</tbody>
</table>

* É importante apenas garantir que não exista um placar com 10 ou mais gols.

---
## Trabalhando o "score" - com tidyverse

``` r
library(tidyverse)

tabela2 <- dados |> 
  tidyr::separate_wider_delim(
    cols = score,
    delim = "x",
    names = c("gols_mandante", "gols_visitante"))
```
<table>
 <thead>
  <tr>
   <th style="text-align:right;"> id_match </th>
   <th style="text-align:right;"> season </th>
   <th style="text-align:left;"> date </th>
   <th style="text-align:left;"> home </th>
   <th style="text-align:left;"> gols_mandante </th>
   <th style="text-align:left;"> gols_visitante </th>
   <th style="text-align:left;"> away </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:left;"> 2 </td>
   <td style="text-align:left;"> 0 </td>
   <td style="text-align:left;"> Grêmio </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Guarani </td>
   <td style="text-align:left;"> 4 </td>
   <td style="text-align:left;"> 2 </td>
   <td style="text-align:left;"> Vasco da Gama </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-30 </td>
   <td style="text-align:left;"> Corinthians </td>
   <td style="text-align:left;"> 0 </td>
   <td style="text-align:left;"> 3 </td>
   <td style="text-align:left;"> Atlético MG </td>
  </tr>
</tbody>
</table>
---
 
## Tornando as novas variáveis em numéricas
* com R base

``` r
tabela1$gols_mandante <- as.numeric(tabela1$gols_mandante)
tabela1$gols_visitante <- as.numeric(tabela1$gols_visitante)
```

```
## Rows: 9,166
## Columns: 7
## $ id_match       <dbl> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, …
## $ season         <dbl> 2003, 2003, 2003, 2003, 2003, 2003, 2003, 2003, 2003, 2…
## $ date           <date> 2003-03-29, 2003-03-29, 2003-03-30, 2003-03-30, 2003-0…
## $ home           <chr> "Athletico PR", "Guarani", "Corinthians", "Goiás", "Cri…
## $ away           <chr> "Grêmio", "Vasco da Gama", "Atlético MG", "Paysandu", "…
## $ gols_mandante  <dbl> 2, 4, 0, 2, 2, 2, 1, 0, 1, 2, 2, 1, 1, 0, 3, 0, 1, 6, 3…
## $ gols_visitante <dbl> 0, 2, 3, 2, 0, 2, 1, 0, 1, 2, 2, 1, 1, 0, 1, 1, 2, 4, 3…
```

---
## Tornando as novas variáveis em numéricas

* com dplyr

``` r
tabela2 <- tabela2 |> 
  dplyr::mutate(gols_mandante = as.numeric(gols_mandante),
                gols_visitante = as.numeric(gols_visitante))
```

---

## Criando a coluna de pontos - com R base

* Vamos começar pelos pontos do time que é o mandante do jogo.

``` r
tabela1$pontos_mandante <- ifelse(gols_mandante > gols_visitante, 3 , 
                              ifelse(gols_mandante == gols_visitante,1, 0))
```
<table>
 <thead>
  <tr>
   <th style="text-align:left;"> home </th>
   <th style="text-align:left;"> away </th>
   <th style="text-align:right;"> gols_mandante </th>
   <th style="text-align:right;"> gols_visitante </th>
   <th style="text-align:right;"> pontos_mandante </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:left;"> Grêmio </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 3 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> Guarani </td>
   <td style="text-align:left;"> Vasco da Gama </td>
   <td style="text-align:right;"> 4 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 3 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> Corinthians </td>
   <td style="text-align:left;"> Atlético MG </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 0 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> Goiás </td>
   <td style="text-align:left;"> Paysandu </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 1 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> Criciúma </td>
   <td style="text-align:left;"> Fluminense </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 3 </td>
  </tr>
</tbody>
</table>

---

## Criando a coluna de pontos - com R base

* O mesmo procedimento agora para o time visitante:

``` r
tabela1$pontos_visitante <- ifelse(gols_mandante > gols_visitante, 0 , 
                              ifelse(gols_mandante == gols_visitante,1, 3))
```
<table>
 <thead>
  <tr>
   <th style="text-align:left;"> home </th>
   <th style="text-align:left;"> away </th>
   <th style="text-align:right;"> gols_mandante </th>
   <th style="text-align:right;"> gols_visitante </th>
   <th style="text-align:right;"> pontos_mandante </th>
   <th style="text-align:right;"> pontos_visitante </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:left;"> Grêmio </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 0 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> Guarani </td>
   <td style="text-align:left;"> Vasco da Gama </td>
   <td style="text-align:right;"> 4 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 0 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> Corinthians </td>
   <td style="text-align:left;"> Atlético MG </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 3 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> Goiás </td>
   <td style="text-align:left;"> Paysandu </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 1 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> Criciúma </td>
   <td style="text-align:left;"> Fluminense </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 0 </td>
  </tr>
</tbody>
</table>

---

## Criando a coluna de pontos - com dplyr

* Vamos começar pelos pontos do time que é o mandante do jogo.

``` r
tabela2 <- tabela2 |> 
dplyr::mutate(pontos_mandante = dplyr::case_when(
    gols_mandante > gols_visitante ~ 3,
    gols_mandante == gols_visitante ~ 1,
    TRUE ~ 0), .after = home)
```
<table>
 <thead>
  <tr>
   <th style="text-align:left;"> home </th>
   <th style="text-align:right;"> pontos_mandante </th>
   <th style="text-align:right;"> gols_mandante </th>
   <th style="text-align:right;"> gols_visitante </th>
   <th style="text-align:left;"> away </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:left;"> Grêmio </td>
  </tr>
  <tr>
   <td style="text-align:left;"> Guarani </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 4 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> Vasco da Gama </td>
  </tr>
  <tr>
   <td style="text-align:left;"> Corinthians </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:left;"> Atlético MG </td>
  </tr>
</tbody>
</table>

---

## Criando a coluna de pontos - com dplyr

* O mesmo procedimento agora para o time visitante:

``` r
tabela2 <- tabela2 |> 
dplyr::mutate(pontos_visitante = dplyr::case_when(
    gols_mandante > gols_visitante ~ 0,
    gols_mandante == gols_visitante ~ 1,
    TRUE ~ 3), .after = away)
```
<table>
 <thead>
  <tr>
   <th style="text-align:left;"> home </th>
   <th style="text-align:right;"> pontos_mandante </th>
   <th style="text-align:right;"> gols_mandante </th>
   <th style="text-align:right;"> gols_visitante </th>
   <th style="text-align:left;"> away </th>
   <th style="text-align:right;"> pontos_visitante </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:left;"> Grêmio </td>
   <td style="text-align:right;"> 0 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> Guarani </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 4 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> Vasco da Gama </td>
   <td style="text-align:right;"> 0 </td>
  </tr>
</tbody>
</table>

---

## Reordenando as colunas - com R base

``` r
tabela1 <- subset(tabela1, select = c(id_match, season, date, 
                                      home, pontos_mandante,
                                      gols_mandante, away,
                                      pontos_visitante,
                                      gols_visitante))
```

<table>
 <thead>
  <tr>
   <th style="text-align:left;"> home </th>
   <th style="text-align:right;"> pontos_mandante </th>
   <th style="text-align:right;"> gols_mandante </th>
   <th style="text-align:left;"> away </th>
   <th style="text-align:right;"> pontos_visitante </th>
   <th style="text-align:right;"> gols_visitante </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> Grêmio </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 0 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> Guarani </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 4 </td>
   <td style="text-align:left;"> Vasco da Gama </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 2 </td>
  </tr>
</tbody>
</table>

---
## Reordenando as colunas - com dplyr

* Note que apenas a variável *gols_visitante* não está na posição que gostaríamos.

<table>
 <thead>
  <tr>
   <th style="text-align:left;"> home </th>
   <th style="text-align:right;"> pontos_mandante </th>
   <th style="text-align:right;"> gols_mandante </th>
   <th style="text-align:right;"> gols_visitante </th>
   <th style="text-align:left;"> away </th>
   <th style="text-align:right;"> pontos_visitante </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:left;"> Grêmio </td>
   <td style="text-align:right;"> 0 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> Guarani </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 4 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> Vasco da Gama </td>
   <td style="text-align:right;"> 0 </td>
  </tr>
</tbody>
</table>

* Isso se deve por termos utilizado o argumento *.after* na função *mutate* anteriormente.

* Acrescenta-se que também poderíamos utilizar o argumento *.before*
---

## Reordenando as colunas - com dplyr

``` r
tabela2 <- tabela2 |> 
  dplyr::relocate(gols_visitante, .after = pontos_visitante) 
```

---

## Pivotar ou não?

<table class="table" style="color: black; margin-left: auto; margin-right: auto;">
 <thead>
  <tr>
   <th style="text-align:right;font-size: 18px;"> id_match </th>
   <th style="text-align:right;font-size: 18px;"> season </th>
   <th style="text-align:left;font-size: 18px;"> date </th>
   <th style="text-align:left;font-size: 18px;"> home </th>
   <th style="text-align:right;font-size: 18px;"> pontos_mandante </th>
   <th style="text-align:right;font-size: 18px;"> gols_mandante </th>
   <th style="text-align:left;font-size: 18px;"> away </th>
   <th style="text-align:right;font-size: 18px;"> pontos_visitante </th>
   <th style="text-align:right;font-size: 18px;"> gols_visitante </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> Grêmio </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 0 </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Guarani </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 4 </td>
   <td style="text-align:left;"> Vasco da Gama </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 2 </td>
  </tr>
</tbody>
</table>
* Note que cada observação deste conjunto de dados é um jogo de 2 times, neste sentido, precisamos duplicar cada linha desta base.

* Só é imprescendível ter uma coluna que guarde qual time foi o mandante e qual foi o visitante.

---

## Duplicando as linhas

``` r
aux_mandante <- cbind(tabela1,mando=c("mandante"))
aux_visitante <- cbind(tabela1,mando=c("visitante"))

tabela1 <- rbind(aux_mandante,aux_visitante)

tabela1 <- tabela1[order(tabela1$id_match, tabela1$mando),]
```

<table class="table" style="color: black; margin-left: auto; margin-right: auto;">
 <thead>
  <tr>
   <th style="text-align:right;font-size: 12px;"> id_match </th>
   <th style="text-align:right;font-size: 12px;"> season </th>
   <th style="text-align:left;font-size: 12px;"> date </th>
   <th style="text-align:left;font-size: 12px;"> home </th>
   <th style="text-align:right;font-size: 12px;"> pontos_mandante </th>
   <th style="text-align:right;font-size: 12px;"> gols_mandante </th>
   <th style="text-align:left;font-size: 12px;"> away </th>
   <th style="text-align:right;font-size: 12px;"> pontos_visitante </th>
   <th style="text-align:right;font-size: 12px;"> gols_visitante </th>
   <th style="text-align:left;font-size: 12px;"> mando </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> Grêmio </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:left;"> mandante </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> Grêmio </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:left;"> visitante </td>
  </tr>
</tbody>
</table>
* Agora precisamos criar duas coluna **time** e **adversario**, de modo a deixar que cada linha represente um clube, na primeira linha **time** será "Athletico PR" e **adversario** será "Grêmio", enquanto na segunda linha isso se inverte, **time** será "Grêmio" e **adversario** será "Athletico PR".

---

## Criando as colunas *time* e *adversario*

``` r
time <- ifelse(tabela1$mando=="mandante",tabela1$home,
               tabela1$away)
adversario <- ifelse(tabela1$mando=="visitante",tabela1$home,
                  tabela1$away)

tabela1$home <- time
tabela1$away <- adversario
```

---

## Renomeando as colunas *time* e *adversario*

``` r
colnames(tabela1)[colnames(tabela1)=="home"] <- "time"
colnames(tabela1)[colnames(tabela1)=="away"] <- "adversario"
```

<table class="table" style="color: black; margin-left: auto; margin-right: auto;">
 <thead>
  <tr>
   <th style="text-align:right;font-size: 12px;"> id_match </th>
   <th style="text-align:right;font-size: 12px;"> season </th>
   <th style="text-align:left;font-size: 12px;"> date </th>
   <th style="text-align:left;font-size: 12px;"> time </th>
   <th style="text-align:right;font-size: 12px;"> pontos_mandante </th>
   <th style="text-align:right;font-size: 12px;"> gols_mandante </th>
   <th style="text-align:left;font-size: 12px;"> adversario </th>
   <th style="text-align:right;font-size: 12px;"> pontos_visitante </th>
   <th style="text-align:right;font-size: 12px;"> gols_visitante </th>
   <th style="text-align:left;font-size: 12px;"> mando </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> Grêmio </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:left;"> mandante </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Grêmio </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:left;"> visitante </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Guarani </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 4 </td>
   <td style="text-align:left;"> Vasco da Gama </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> mandante </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Vasco da Gama </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 4 </td>
   <td style="text-align:left;"> Guarani </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> visitante </td>
  </tr>
</tbody>
</table>

---

## Criando as colunas *gols_favor* e *gols_contra*

``` r
gols_favor <- ifelse(tabela1$mando=="mandante", tabela1$gols_mandante,tabela1$gols_visitante)

gols_contra <- ifelse(tabela1$mando=="visitante", tabela1$gols_mandante,tabela1$gols_visitante)

tabela1$gols_mandante <- gols_favor
tabela1$gols_visitante <- gols_contra
```

## Renomeando as colunas *gols_favor* e *gols_contra*

``` r
colnames(tabela1)[colnames(tabela1)=="gols_mandante"] <- "gols_favor"
colnames(tabela1)[colnames(tabela1)=="gols_visitante"] <- "gols_contra"
```

* Agora precisamos criar uma coluna com os pontos conquistados por cada time.

---

## Criando a variável *pontos*

``` r
pontos <- ifelse(tabela1$mando=="mandante",tabela1$pontos_mandante,tabela1$pontos_visitante)

tabela1$pontos_mandante <- pontos
colnames(tabela1)[colnames(tabela1)=="pontos_mandante"] <- "pontos"

tabela1 <- subset(tabela1, select = -c(pontos_visitante))
```

<table class="table" style="color: black; margin-left: auto; margin-right: auto;">
 <thead>
  <tr>
   <th style="text-align:right;font-size: 12px;"> id_match </th>
   <th style="text-align:right;font-size: 12px;"> season </th>
   <th style="text-align:left;font-size: 12px;"> date </th>
   <th style="text-align:left;font-size: 12px;"> time </th>
   <th style="text-align:right;font-size: 12px;"> pontos </th>
   <th style="text-align:right;font-size: 12px;"> gols_favor </th>
   <th style="text-align:left;font-size: 12px;"> adversario </th>
   <th style="text-align:right;font-size: 12px;"> gols_contra </th>
   <th style="text-align:left;font-size: 12px;"> mando </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> Grêmio </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:left;"> mandante </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Grêmio </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> visitante </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Guarani </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 4 </td>
   <td style="text-align:left;"> Vasco da Gama </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> mandante </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Vasco da Gama </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> Guarani </td>
   <td style="text-align:right;"> 4 </td>
   <td style="text-align:left;"> visitante </td>
  </tr>
</tbody>
</table>

---
## Pivotar ou não?

* Nossa estratégia inicial foi em não pivotar, no entanto, poderíamos ter optador em utilizar a função *pivot_longer*. A base de dados anteriormente estava da seguinte maneira:

---

## Duplicando as linhas - com tidyr

``` r
tabela2 <- tabela2 |> 
  tidyr::pivot_longer( cols = c(pontos_mandante, pontos_visitante),
    names_to = "mando", values_to = "pontos") |> 
  dplyr::mutate(mando = stringr::str_remove(mando, "pontos_"))
```
<table class="table" style="color: black; margin-left: auto; margin-right: auto;">
 <thead>
  <tr>
   <th style="text-align:right;font-size: 12px;"> id_match </th>
   <th style="text-align:right;font-size: 12px;"> season </th>
   <th style="text-align:left;font-size: 12px;"> date </th>
   <th style="text-align:left;font-size: 12px;"> home </th>
   <th style="text-align:right;font-size: 12px;"> gols_mandante </th>
   <th style="text-align:left;font-size: 12px;"> away </th>
   <th style="text-align:right;font-size: 12px;"> gols_visitante </th>
   <th style="text-align:left;font-size: 12px;"> mando </th>
   <th style="text-align:right;font-size: 12px;"> pontos </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> Grêmio </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:left;"> mandante </td>
   <td style="text-align:right;"> 3 </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> Grêmio </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:left;"> visitante </td>
   <td style="text-align:right;"> 0 </td>
  </tr>
</tbody>
</table>
* É possível observar que duplicamos as linhas igual feito anteriormente, porém, já resolvemos a questão dos pontos.

* Agora precisamos criar duas coluna time e adversario, de modo a deixar que cada linha represente um clube.

---

## Criando as colunas *time* e *adversario*

``` r
tabela2 <- tabela2 |> 
  dplyr::mutate(time = ifelse(mando == "mandante",home,away),
    adversario = ifelse(mando == "mandante",away,home), .after = date) |> 
  dplyr::select(-c(home,away))
```

<table class="table" style="color: black; margin-left: auto; margin-right: auto;">
 <thead>
  <tr>
   <th style="text-align:right;font-size: 12px;"> id_match </th>
   <th style="text-align:right;font-size: 12px;"> season </th>
   <th style="text-align:left;font-size: 12px;"> date </th>
   <th style="text-align:left;font-size: 12px;"> time </th>
   <th style="text-align:left;font-size: 12px;"> adversario </th>
   <th style="text-align:right;font-size: 12px;"> gols_mandante </th>
   <th style="text-align:right;font-size: 12px;"> gols_visitante </th>
   <th style="text-align:left;font-size: 12px;"> mando </th>
   <th style="text-align:right;font-size: 12px;"> pontos </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:left;"> Grêmio </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:left;"> mandante </td>
   <td style="text-align:right;"> 3 </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Grêmio </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:left;"> visitante </td>
   <td style="text-align:right;"> 0 </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Guarani </td>
   <td style="text-align:left;"> Vasco da Gama </td>
   <td style="text-align:right;"> 4 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> mandante </td>
   <td style="text-align:right;"> 3 </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Vasco da Gama </td>
   <td style="text-align:left;"> Guarani </td>
   <td style="text-align:right;"> 4 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> visitante </td>
   <td style="text-align:right;"> 0 </td>
  </tr>
</tbody>
</table>
 
---

## Criando as colunas *gols_favor* e *gols_contra*

``` r
tabela2 <- tabela2 |> 
dplyr::mutate(gols_favor = ifelse(mando == "mandante", gols_mandante, 
                                  gols_visitante),
    gols_contra = ifelse(mando == "mandante", gols_visitante, gols_mandante)) |> 
  dplyr::select(-c(gols_mandante, gols_visitante))
```

---

## Comparando as tabelas

* Tabela 1
<table class="table" style="color: black; margin-left: auto; margin-right: auto;">
 <thead>
  <tr>
   <th style="text-align:right;font-size: 12px;"> id_match </th>
   <th style="text-align:right;font-size: 12px;"> season </th>
   <th style="text-align:left;font-size: 12px;"> date </th>
   <th style="text-align:left;font-size: 12px;"> time </th>
   <th style="text-align:right;font-size: 12px;"> pontos </th>
   <th style="text-align:right;font-size: 12px;"> gols_favor </th>
   <th style="text-align:left;font-size: 12px;"> adversario </th>
   <th style="text-align:right;font-size: 12px;"> gols_contra </th>
   <th style="text-align:left;font-size: 12px;"> mando </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> Grêmio </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:left;"> mandante </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Grêmio </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> visitante </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Guarani </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 4 </td>
   <td style="text-align:left;"> Vasco da Gama </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> mandante </td>
  </tr>
</tbody>
</table>

* Tabela 2

---
## Vamos praticar!

* ### Usando o dplyr crie as colunas:
  * rodada;
  * pontos acumulado;
  * gols a favor acumulado;
  * gols contra acumulado;
  * vitória (0 ou 1);
  * número de vitórias;
  * saldo de gols acumulado;
  * colocação.

---

## Criando a coluna *rodada* - dplyr

``` r
tabela2 <- tabela2 |> 
dplyr::arrange(season, time, date) |>
  dplyr::group_by(season, time) |>
  dplyr::mutate(rodada = dplyr::row_number(),.after = season)
```

<table class="table" style="color: black; margin-left: auto; margin-right: auto;">
 <thead>
  <tr>
   <th style="text-align:right;font-size: 12px;"> id_match </th>
   <th style="text-align:right;font-size: 12px;"> season </th>
   <th style="text-align:right;font-size: 12px;"> rodada </th>
   <th style="text-align:left;font-size: 12px;"> date </th>
   <th style="text-align:left;font-size: 12px;"> time </th>
   <th style="text-align:left;font-size: 12px;"> adversario </th>
   <th style="text-align:left;font-size: 12px;"> mando </th>
   <th style="text-align:right;font-size: 12px;"> pontos </th>
   <th style="text-align:right;font-size: 12px;"> gols_favor </th>
   <th style="text-align:right;font-size: 12px;"> gols_contra </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:left;"> Grêmio </td>
   <td style="text-align:left;"> mandante </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 0 </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 23 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> 2003-04-06 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:left;"> Paraná </td>
   <td style="text-align:left;"> visitante </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 3 </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 27 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:left;"> 2003-04-12 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:left;"> Atlético MG </td>
   <td style="text-align:left;"> mandante </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 2 </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 44 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:right;"> 4 </td>
   <td style="text-align:left;"> 2003-04-16 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:left;"> Figueirense </td>
   <td style="text-align:left;"> visitante </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 1 </td>
  </tr>
</tbody>
</table>

---

## Criando a coluna *pontos acumulado* - dplyr

``` r
tabela2 <- tabela2 |> 
dplyr::mutate(pontos_acum = case_when(time == lag(time,
                        default = first(time)) ~ cumsum(pontos),
                        TRUE ~ pontos))
```

<table class="table" style="color: black; margin-left: auto; margin-right: auto;">
 <thead>
  <tr>
   <th style="text-align:right;font-size: 12px;"> id_match </th>
   <th style="text-align:right;font-size: 12px;"> season </th>
   <th style="text-align:right;font-size: 12px;"> rodada </th>
   <th style="text-align:left;font-size: 12px;"> date </th>
   <th style="text-align:left;font-size: 12px;"> time </th>
   <th style="text-align:left;font-size: 12px;"> adversario </th>
   <th style="text-align:left;font-size: 12px;"> mando </th>
   <th style="text-align:right;font-size: 12px;"> pontos </th>
   <th style="text-align:right;font-size: 12px;"> gols_favor </th>
   <th style="text-align:right;font-size: 12px;"> gols_contra </th>
   <th style="text-align:right;font-size: 12px;"> pontos_acum </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:left;"> Grêmio </td>
   <td style="text-align:left;"> mandante </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 3 </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 23 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> 2003-04-06 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:left;"> Paraná </td>
   <td style="text-align:left;"> visitante </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 3 </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 27 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:left;"> 2003-04-12 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:left;"> Atlético MG </td>
   <td style="text-align:left;"> mandante </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 3 </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 44 </td>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:right;"> 4 </td>
   <td style="text-align:left;"> 2003-04-16 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:left;"> Figueirense </td>
   <td style="text-align:left;"> visitante </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 4 </td>
  </tr>
</tbody>
</table>
---

## Criando a coluna *gols a favor acumulado* - dplyr

``` r
tabela2 <- tabela2 |> 
dplyr::mutate(gp_acum = case_when(time == lag(time,
                                  default = first(time)) ~ cumsum(gols_favor),
                                  TRUE ~ gols_favor))
```

<table class="table" style="color: black; margin-left: auto; margin-right: auto;">
 <thead>
  <tr>
   <th style="text-align:right;font-size: 12px;"> season </th>
   <th style="text-align:right;font-size: 12px;"> rodada </th>
   <th style="text-align:left;font-size: 12px;"> date </th>
   <th style="text-align:left;font-size: 12px;"> time </th>
   <th style="text-align:left;font-size: 12px;"> adversario </th>
   <th style="text-align:left;font-size: 12px;"> mando </th>
   <th style="text-align:right;font-size: 12px;"> pontos </th>
   <th style="text-align:right;font-size: 12px;"> gols_favor </th>
   <th style="text-align:right;font-size: 12px;"> gols_contra </th>
   <th style="text-align:right;font-size: 12px;"> pontos_acum </th>
   <th style="text-align:right;font-size: 12px;"> gp_acum </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:left;"> Grêmio </td>
   <td style="text-align:left;"> mandante </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> 2003-04-06 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:left;"> Paraná </td>
   <td style="text-align:left;"> visitante </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:left;"> 2003-04-12 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:left;"> Atlético MG </td>
   <td style="text-align:left;"> mandante </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 3 </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 2003 </td>
   <td style="text-align:right;"> 4 </td>
   <td style="text-align:left;"> 2003-04-16 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:left;"> Figueirense </td>
   <td style="text-align:left;"> visitante </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 4 </td>
   <td style="text-align:right;"> 4 </td>
  </tr>
</tbody>
</table>
---

## Criando a coluna *gols contra acumulado* - dplyr

``` r
tabela2 <- tabela2 |> 
dplyr::mutate(gc_acum = case_when(time == lag(time, default = first(time)) ~
                                    cumsum(gols_contra),
                                  TRUE ~ gols_contra))
```

<table class="table" style="color: black; margin-left: auto; margin-right: auto;">
 <thead>
  <tr>
   <th style="text-align:right;font-size: 12px;"> rodada </th>
   <th style="text-align:left;font-size: 12px;"> date </th>
   <th style="text-align:left;font-size: 12px;"> time </th>
   <th style="text-align:left;font-size: 12px;"> adversario </th>
   <th style="text-align:left;font-size: 12px;"> mando </th>
   <th style="text-align:right;font-size: 12px;"> pontos </th>
   <th style="text-align:right;font-size: 12px;"> gols_favor </th>
   <th style="text-align:right;font-size: 12px;"> gols_contra </th>
   <th style="text-align:right;font-size: 12px;"> pontos_acum </th>
   <th style="text-align:right;font-size: 12px;"> gp_acum </th>
   <th style="text-align:right;font-size: 12px;"> gc_acum </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:left;"> 2003-03-29 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:left;"> Grêmio </td>
   <td style="text-align:left;"> mandante </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 0 </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:left;"> 2003-04-06 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:left;"> Paraná </td>
   <td style="text-align:left;"> visitante </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 3 </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:left;"> 2003-04-12 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:left;"> Atlético MG </td>
   <td style="text-align:left;"> mandante </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 5 </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 4 </td>
   <td style="text-align:left;"> 2003-04-16 </td>
   <td style="text-align:left;"> Athletico PR </td>
   <td style="text-align:left;"> Figueirense </td>
   <td style="text-align:left;"> visitante </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 4 </td>
   <td style="text-align:right;"> 4 </td>
   <td style="text-align:right;"> 6 </td>
  </tr>
</tbody>
</table>

---

## Criando as colunas de *vitória e números de vitórias* - dplyr

``` r
tabela2 <- tabela2 |> 
dplyr::mutate(vitoria = ifelse(pontos==3,1,0),.before = pontos) |>
  dplyr::mutate(num_vit = case_when(time==lag(time, default = first(time)) ~
    cumsum(vitoria), TRUE ~ vitoria)) 
```

---

## Criando as colunas de *saldo e saldo acumulado* - dplyr

``` r
tabela2 <- tabela2 |> 
dplyr::mutate(saldo = gols_favor - gols_contra, .before = pontos_acum) |>
  dplyr::mutate(saldo_acum= case_when(time==lag(time, default = first(time)) ~
    cumsum(saldo), TRUE ~ saldo))
```

---

## Criando a coluna de *colocação* - dplyr

``` r
tabela2 <- tabela2 |> 
  dplyr::group_by(season) |>
  dplyr::arrange(desc(season), rodada, desc(pontos_acum),
                 desc(num_vit),desc(saldo_acum), desc(gp_acum)) |>
  dplyr::group_by(season,rodada) |>
  dplyr::mutate(colocacao = dplyr::row_number())
```

---
## Pronto! Concluímos nosso objetivo!

<table class="table" style="color: black; margin-left: auto; margin-right: auto;">
 <thead>
  <tr>
   <th style="text-align:left;font-size: 12px;"> time </th>
   <th style="text-align:left;font-size: 12px;"> adversario </th>
   <th style="text-align:left;font-size: 12px;"> mando </th>
   <th style="text-align:right;font-size: 12px;"> vitoria </th>
   <th style="text-align:right;font-size: 12px;"> pontos </th>
   <th style="text-align:right;font-size: 12px;"> gols_favor </th>
   <th style="text-align:right;font-size: 12px;"> gols_contra </th>
   <th style="text-align:right;font-size: 12px;"> saldo </th>
   <th style="text-align:right;font-size: 12px;"> pontos_acum </th>
   <th style="text-align:right;font-size: 12px;"> gp_acum </th>
   <th style="text-align:right;font-size: 12px;"> gc_acum </th>
   <th style="text-align:right;font-size: 12px;"> num_vit </th>
   <th style="text-align:right;font-size: 12px;"> saldo_acum </th>
   <th style="text-align:right;font-size: 12px;"> colocacao </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:left;"> Fortaleza </td>
   <td style="text-align:left;"> Fluminense </td>
   <td style="text-align:left;"> mandante </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 1 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> Juventude </td>
   <td style="text-align:left;"> Vitória </td>
   <td style="text-align:left;"> mandante </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 2 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> Cruzeiro </td>
   <td style="text-align:left;"> Mirassol </td>
   <td style="text-align:left;"> mandante </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 3 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> Grêmio </td>
   <td style="text-align:left;"> Atlético MG </td>
   <td style="text-align:left;"> mandante </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 4 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> Vasco da Gama </td>
   <td style="text-align:left;"> Santos </td>
   <td style="text-align:left;"> mandante </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 5 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> Bragantino </td>
   <td style="text-align:left;"> Ceará </td>
   <td style="text-align:left;"> mandante </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 1 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 2 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 0 </td>
   <td style="text-align:right;"> 6 </td>
  </tr>
</tbody>
</table>