Thompson Screener Dataframes com R
Adhmir
30/03/2022
1 Introdução
O intuito desse documento é apresentar uma forma para manipulação de dados com R considerando as saídas do screener da thompson refinitiv.
A organização dos dados após a coleta, pode ser muito trabalhosa caso o volume de dados seja grande. A transformação de dados em colunas para painel exige trabalho e tempo.
Outro ponto importante é a estratégia para lidar com valores ausentes, que pode ser adotada desde a exlusão até a substituição por zero, média, mediana, entre outras
Portanto, este documento aborda os seguintes tópicos:
- Início dos trabalhos
- Pré-processamento dos dados
- Criar novas variáveis
- Valores discrepantes
- Conclusão
1.1 Início dos trabalhos
Para facilitar a leitura de arquivos, uma opção interessante é configurar qual será a pasta de trabalho (working diretory) setwd()
setwd("C:/PHD/A parte/Ideia oficinas/Software R/markdowns")O R permite a importação de pacotes que tem inumeras funcionalidades. Uma dessas é a tidyverse. Para saber mais a respeito veja materiais como o de Stanley, 2017
Para importar o pacote o comando é o seguinte:
#install.packages("tidyverse")
library(tidyverse)## -- Attaching packages --------------------------------------- tidyverse 1.3.1 --## v ggplot2 3.3.5 v purrr 0.3.4
## v tibble 3.1.1 v dplyr 1.0.5
## v tidyr 1.1.3 v stringr 1.4.0
## v readr 1.4.0 v forcats 0.5.1## -- Conflicts ------------------------------------------ tidyverse_conflicts() --
## x dplyr::filter() masks stats::filter()
## x dplyr::lag() masks stats::lag()
Para esse exemplo vamos usar o pacote readxl e usar o comando read_excel, com o nome do arquivo entre aspas e pular uma linha, pois temos dois níveis de cabeçalho skip = 1
#install.packages("readxl")
library(readxl)
df <- read_excel("exemplo_df1.xlsx", skip = 1)## New names:
## * `` -> ...1
## * `` -> ...2
## * `` -> ...3
## * `` -> ...4
## * `` -> ...5
## * ...head(df,3)## # A tibble: 3 x 35
## ...1 ...2 ...3 ...4 ...5 FY0...6 `FY-1...7` `FY-2...8` `FY-3...9`
## <chr> <chr> <chr> <chr> <chr> <dbl> <dbl> <dbl> <dbl>
## 1 <NA> <NA> <NA> <NA> <NA> NA NA NA NA
## 2 KEPL3~ Kepler W~ Braz~ Braz~ Industr~ 550. 303. 316. 304.
## 3 MYPK3~ Iochpe M~ Braz~ Braz~ Consume~ 5162. 3358. 3524. 3049.
## # ... with 26 more variables: FY-4...10 <dbl>, FY-5...11 <dbl>, FY0...12 <dbl>,
## # FY-1...13 <dbl>, FY-2...14 <dbl>, FY-3...15 <dbl>, FY-4...16 <dbl>,
## # FY-5...17 <dbl>, FY0...18 <dbl>, FY-1...19 <dbl>, FY-2...20 <dbl>,
## # FY-3...21 <dbl>, FY-4...22 <dbl>, FY-5...23 <dbl>, FY0...24 <dbl>,
## # FY-1...25 <dbl>, FY-2...26 <dbl>, FY-3...27 <dbl>, FY-4...28 <dbl>,
## # FY-5...29 <dbl>, FY0...30 <dbl>, FY-1...31 <dbl>, FY-2...32 <dbl>,
## # FY-3...33 <dbl>, FY-4...34 <dbl>, FY-5...35 <dbl>Notem que a primeira linha ainda é a linha vazia. Portanto é necessário remover.
df <- df[-1, ]
head(df)## # A tibble: 6 x 35
## ...1 ...2 ...3 ...4 ...5 FY0...6 `FY-1...7` `FY-2...8` `FY-3...9`
## <chr> <chr> <chr> <chr> <chr> <dbl> <dbl> <dbl> <dbl>
## 1 KEPL3~ Kepler We~ Braz~ Braz~ Indust~ 5.50e2 303. 316. 304.
## 2 MYPK3~ Iochpe Ma~ Braz~ Braz~ Consum~ 5.16e3 3358. 3524. 3049.
## 3 JOPA4~ Josapar J~ Braz~ Braz~ Consum~ 1.35e3 1119. 1236. 1133.
## 4 CEBR3~ Companhia~ Braz~ Braz~ Utilit~ 3.32e3 1760. 1854. 1683.
## 5 PPAR3~ Polpar SA Braz~ Braz~ Basic ~ 2.51e0 2.72 2.37 2.19
## 6 CSNA3~ Companhia~ Braz~ Braz~ Basic ~ 2.34e4 12726. 12014. 11881.
## # ... with 26 more variables: FY-4...10 <dbl>, FY-5...11 <dbl>, FY0...12 <dbl>,
## # FY-1...13 <dbl>, FY-2...14 <dbl>, FY-3...15 <dbl>, FY-4...16 <dbl>,
## # FY-5...17 <dbl>, FY0...18 <dbl>, FY-1...19 <dbl>, FY-2...20 <dbl>,
## # FY-3...21 <dbl>, FY-4...22 <dbl>, FY-5...23 <dbl>, FY0...24 <dbl>,
## # FY-1...25 <dbl>, FY-2...26 <dbl>, FY-3...27 <dbl>, FY-4...28 <dbl>,
## # FY-5...29 <dbl>, FY0...30 <dbl>, FY-1...31 <dbl>, FY-2...32 <dbl>,
## # FY-3...33 <dbl>, FY-4...34 <dbl>, FY-5...35 <dbl>É necessário renomear os dados, pois ao pular a primeira linha, as colunas sem o primeiro nível ficaram com a ordem da coluna, 1,2,3..assim por diante. E as que tinham dois níveis, tinham apenas o nome tempo FY0, FY-1, FY-2, FY-3,FY-4,FY-5. O problema é que a parte dos FY’s se repetem para cada uma das colunas no conjunto de dados, por isso, é necessário renomear.
nomesColunas <- c('Identifier', 'CompanyName', 'Headquarters', 'Exchange', 'TRBCSector',
'TotalCurrentAssets_2019', 'TotalCurrentAssets_2018',
'TotalCurrentAssets_2017','TotalCurrentAssets_2016',
'TotalCurrentAssets_2015', 'TotalCurrentAssets_2014',
'TotalCurrentLiabilities_2019', 'TotalCurrentLiabilities_2018',
'TotalCurrentLiabilities_2017', 'TotalCurrentLiabilities_2016',
'TotalCurrentLiabilities_2015', 'TotalCurrentLiabilities_2014',
'TotalAssets_2019','TotalAssets_2018', 'TotalAssets_2017',
'TotalAssets_2016','TotalAssets_2015', 'TotalAssets_2014',
'EBITDA_2019','EBITDA_2018', 'EBITDA_2017','EBITDA_2016',
'EBITDA_2015', 'EBITDA_2014','TotalRevenue_2019','TotalRevenue_2018',
'TotalRevenue_2017','TotalRevenue_2016','TotalRevenue_2015',
'TotalRevenue_2014')
names(df) <- nomesColunas
Para inspecionarmos as variáveis podemos utilizar o comando glimpse
glimpse(df)## Rows: 321
## Columns: 35
## $ Identifier <chr> "KEPL3.SA", "MYPK3.SA", "JOPA4.SA", "CEBR~
## $ CompanyName <chr> "Kepler Weber SA", "Iochpe Maxion SA", "J~
## $ Headquarters <chr> "Brazil", "Brazil", "Brazil", "Brazil", "~
## $ Exchange <chr> "Brazil", "Brazil", "Brazil", "Brazil", "~
## $ TRBCSector <chr> "Industrials", "Consumer Cyclicals", "Con~
## $ TotalCurrentAssets_2019 <dbl> 550.0390, 5161.9260, 1347.9610, 3318.0720~
## $ TotalCurrentAssets_2018 <dbl> 303.2670, 3357.6210, 1119.3560, 1759.8710~
## $ TotalCurrentAssets_2017 <dbl> 316.2340, 3523.8020, 1236.1710, 1854.1990~
## $ TotalCurrentAssets_2016 <dbl> 304.324, 3048.763, 1133.187, 1683.112, 2.~
## $ TotalCurrentAssets_2015 <dbl> 315.8280, 2364.7110, 1081.3250, 1162.0220~
## $ TotalCurrentAssets_2014 <dbl> 363.8490, 2727.8550, 985.8810, 1587.4640,~
## $ TotalCurrentLiabilities_2019 <dbl> 396.5250, 4352.4510, 765.7140, 2844.6350,~
## $ TotalCurrentLiabilities_2018 <dbl> 176.9530, 2583.6180, 527.5140, 1568.0800,~
## $ TotalCurrentLiabilities_2017 <dbl> 204.3210, 3261.8290, 549.4370, 1928.8430,~
## $ TotalCurrentLiabilities_2016 <dbl> 226.9440, 3060.8540, 540.6770, 1682.7260,~
## $ TotalCurrentLiabilities_2015 <dbl> 208.3620, 2521.7190, 607.4850, 1299.8490,~
## $ TotalCurrentLiabilities_2014 <dbl> 223.27400, 3088.75000, 634.06900, 1436.62~
## $ TotalAssets_2019 <dbl> 948.7780, 12411.8030, 2031.8040, 3833.792~
## $ TotalAssets_2018 <dbl> 676.1020, 9375.4110, 1802.2680, 3706.6900~
## $ TotalAssets_2017 <dbl> 704.2730, 9019.1900, 1815.5010, 3753.5170~
## $ TotalAssets_2016 <dbl> 716.2460, 7922.2480, 1712.6390, 3602.7480~
## $ TotalAssets_2015 <dbl> 763.8050, 7057.1150, 1667.3370, 3078.5230~
## $ TotalAssets_2014 <dbl> 791.2350, 8015.0820, 1566.2400, 3364.5460~
## $ EBITDA_2019 <dbl> 112.8590, 592.7180, 134.1550, 91.5120, -0~
## $ EBITDA_2018 <dbl> 96.774, 1156.855, 44.967, 115.822, -0.157~
## $ EBITDA_2017 <dbl> 52.55900, 1104.23200, 80.57800, 182.02400~
## $ EBITDA_2016 <dbl> -9.80500, 871.06300, 80.68600, 282.97800,~
## $ EBITDA_2015 <dbl> -22.05600, 763.40700, 127.12800, 197.4280~
## $ EBITDA_2014 <dbl> 32.67400, 710.92300, 100.38700, 294.85900~
## $ TotalRevenue_2019 <dbl> 671.2430, 8760.5680, 1564.0810, 255.1780,~
## $ TotalRevenue_2018 <dbl> 583.4650, 10016.3950, 1218.6300, 304.7370~
## $ TotalRevenue_2017 <dbl> 576.300, 9616.299, 1106.752, 2590.210, 0.~
## $ TotalRevenue_2016 <dbl> 578.3750, 7487.9400, 1069.2820, 2716.2560~
## $ TotalRevenue_2015 <dbl> 475.298, 6816.454, 1172.311, 2114.554, 0.~
## $ TotalRevenue_2014 <dbl> 705.9790, 6846.4560, 1056.8310, 2396.0040~Com as colunas já renomeadas e com a estrutura definida, podemos partir para o pré-processamento dos dados.
2 Pré-Processamento dos dados
A sintaxe do R as vezes pode ser um pouco repetitiva, em códigos pequenos não é tão ruim, mas em códigos que demandam mais linhas o uso do pip %>% é uma solução. Essa pequena função é capaz de armazenar a saída da função da esquerda e alimentar na função da direita como o seu primeiro argumento (Stanley, 2017). Ao invés de usar os comandos em linhas separadas como o usual, podemos utilizar o pip.
names(df) %>% print()## [1] "Identifier" "CompanyName"
## [3] "Headquarters" "Exchange"
## [5] "TRBCSector" "TotalCurrentAssets_2019"
## [7] "TotalCurrentAssets_2018" "TotalCurrentAssets_2017"
## [9] "TotalCurrentAssets_2016" "TotalCurrentAssets_2015"
## [11] "TotalCurrentAssets_2014" "TotalCurrentLiabilities_2019"
## [13] "TotalCurrentLiabilities_2018" "TotalCurrentLiabilities_2017"
## [15] "TotalCurrentLiabilities_2016" "TotalCurrentLiabilities_2015"
## [17] "TotalCurrentLiabilities_2014" "TotalAssets_2019"
## [19] "TotalAssets_2018" "TotalAssets_2017"
## [21] "TotalAssets_2016" "TotalAssets_2015"
## [23] "TotalAssets_2014" "EBITDA_2019"
## [25] "EBITDA_2018" "EBITDA_2017"
## [27] "EBITDA_2016" "EBITDA_2015"
## [29] "EBITDA_2014" "TotalRevenue_2019"
## [31] "TotalRevenue_2018" "TotalRevenue_2017"
## [33] "TotalRevenue_2016" "TotalRevenue_2015"
## [35] "TotalRevenue_2014"
Os benefícios de utilizar o pip vai desde a economia de digitação, isso ajuda diminuir a quantidade de códigos, até a economia de cliques para executar o comando. No entanto, o %>%não se limita a duas funções, é possível empilhar quantos for necessário, como se fosse um parágrafo. Outro benefício é que reduz os objetos criados, pois não é necessário criar nomes para cada uma das etapas intermediárias (Stanley, 2017).
2.1 Comando Select
O comando select possibilita selecionar algumas variáveis de interesse, como por exemplo, selecionado direto os nomes das colunas.
Se pode selecionar as de outras formas, como por exemplo, se contém as strings “EBTIDA”.
roa <- df %>%
select(CompanyName, contains("TotalAssets"),contains("EBITDA"))
names(roa)## [1] "CompanyName" "TotalAssets_2019" "TotalAssets_2018" "TotalAssets_2017"
## [5] "TotalAssets_2016" "TotalAssets_2015" "TotalAssets_2014" "EBITDA_2019"
## [9] "EBITDA_2018" "EBITDA_2017" "EBITDA_2016" "EBITDA_2015"
## [13] "EBITDA_2014"Outro exemplo:
ebitda <- df %>%
select(CompanyName,TRBCSector, contains("EBITDA"))
names(ebitda)## [1] "CompanyName" "TRBCSector" "EBITDA_2019" "EBITDA_2018" "EBITDA_2017"
## [6] "EBITDA_2016" "EBITDA_2015" "EBITDA_2014"Ou então selecionar pelas colunas
AtivoCirculante <- df %>%
select(TotalCurrentAssets_2014:TotalCurrentAssets_2019)
names(AtivoCirculante)## [1] "TotalCurrentAssets_2014" "TotalCurrentAssets_2015"
## [3] "TotalCurrentAssets_2016" "TotalCurrentAssets_2017"
## [5] "TotalCurrentAssets_2018" "TotalCurrentAssets_2019"Também é possível restringir quais colunas não selecionar
AC_maior_que_2016 <- AtivoCirculante %>%
select(-c(TotalCurrentAssets_2014,TotalCurrentAssets_2015,TotalCurrentAssets_2016))
names(AC_maior_que_2016)## [1] "TotalCurrentAssets_2017" "TotalCurrentAssets_2018"
## [3] "TotalCurrentAssets_2019"Com esse comando também é possível reordenar as colunas
AC_maior_que_2016 <- AC_maior_que_2016 %>%
select(TotalCurrentAssets_2019,TotalCurrentAssets_2018,TotalCurrentAssets_2017)
names(AC_maior_que_2016)## [1] "TotalCurrentAssets_2019" "TotalCurrentAssets_2018"
## [3] "TotalCurrentAssets_2017"Para selecionar tudo após algumas variáveis é possível realizar dessa forma:
ebitda %>%
select(TRBCSector, everything()) %>% names()## [1] "TRBCSector" "CompanyName" "EBITDA_2019" "EBITDA_2018" "EBITDA_2017"
## [6] "EBITDA_2016" "EBITDA_2015" "EBITDA_2014"
Note que neste caso, chamamos a função names() com o uso do pip %>%.
2.2 Filtros do conjunto de dados
Existem muitos operadores que são úteis ao construir as expressões para filtrar os dados
igual ==
df_oper <- df %>% filter(TRBCSector == "Consumer Cyclicals" )
não contém !=
df_oper <- df %>% filter(TRBCSector != "Industrials")
ou |
df_oper <- df %>% filter(TRBCSector == "Consumer Cyclicals" |
TRBCSector == " Utilities")
Maior `>, <, >=, <=
media_ebitda_2019 <- mean(df$EBITDA_2019, na.rm = TRUE)
df_oper <- df %>% filter(EBITDA_2019 <= media_ebitda_2019)media_ebitda_2019 <- mean(df$EBITDA_2019, na.rm=TRUE)
df_oper <- df %>% filter(TRBCSector == "Consumer Cyclicals" &
df$EBITDA_2019 < media_ebitda_2019)2.3 Estratégia para valores ausentes
Existem várias possibilidades comuns para lidar com valores ausentes. Por exemplo;
- É possível excluir
- Substituir por zero, quando conveniente.
- Substituir pela média
- Substituir pela mediana.
No entanto, é necessário inspecionar se o seu conjunto de dados possuem valores ausentes. Pode inspecionar o conjunto de dados inteiro.
nulos <- map(df, ~sum(is.na(.)))
nulos[c(5:10)]## $TRBCSector
## [1] 0
##
## $TotalCurrentAssets_2019
## [1] 13
##
## $TotalCurrentAssets_2018
## [1] 13
##
## $TotalCurrentAssets_2017
## [1] 17
##
## $TotalCurrentAssets_2016
## [1] 36
##
## $TotalCurrentAssets_2015
## [1] 65Ou dessa outra forma:
df %>% summarise_all(~ sum(is.na(.)))## # A tibble: 1 x 35
## Identifier CompanyName Headquarters Exchange TRBCSector TotalCurrentAssets_20~
## <int> <int> <int> <int> <int> <int>
## 1 0 0 0 0 0 13
## # ... with 29 more variables: TotalCurrentAssets_2018 <int>,
## # TotalCurrentAssets_2017 <int>, TotalCurrentAssets_2016 <int>,
## # TotalCurrentAssets_2015 <int>, TotalCurrentAssets_2014 <int>,
## # TotalCurrentLiabilities_2019 <int>, TotalCurrentLiabilities_2018 <int>,
## # TotalCurrentLiabilities_2017 <int>, TotalCurrentLiabilities_2016 <int>,
## # TotalCurrentLiabilities_2015 <int>, TotalCurrentLiabilities_2014 <int>,
## # TotalAssets_2019 <int>, TotalAssets_2018 <int>, TotalAssets_2017 <int>,
## # TotalAssets_2016 <int>, TotalAssets_2015 <int>, TotalAssets_2014 <int>,
## # EBITDA_2019 <int>, EBITDA_2018 <int>, EBITDA_2017 <int>, EBITDA_2016 <int>,
## # EBITDA_2015 <int>, EBITDA_2014 <int>, TotalRevenue_2019 <int>,
## # TotalRevenue_2018 <int>, TotalRevenue_2017 <int>, TotalRevenue_2016 <int>,
## # TotalRevenue_2015 <int>, TotalRevenue_2014 <int>Ou apenas a coluna de interesse.
sum(is.na(df$TotalCurrentAssets_2016))## [1] 36Como podemos perceber, existem diversar maneiras para observar os valores nulos. Mas, ainda precisamos lidar com os mesmos.
2.3.1 Exclusão
Atenção, quando temos que excluir e desejamos ter os dados balanceados, uma das formas é excluir as observações e, posteriormente tranformar em painel. Uma das formas mais convencionais é a remoção dos dados. Isso não é um problema para amostras grandes. Quando não for o caso, as outras opções são válidas. De forma simples é possível remover os valores nulos de todo o conjunto de dados. Vamos trabalhar com o dataframe retorno sobre o ativo (roa).
df_naoNulos <- roa %>% drop_na()
print(paste('valores ausentes em roa:',
sum(is.na(roa)),
'valores ausentes em df_naoNulos:',
sum(is.na(df_naoNulos))))## [1] "valores ausentes em roa: 445 valores ausentes em df_naoNulos: 0"Podemos observar a saída que a quantidade de valores é igual a zero. Podemos comparar o tamanho dos conjuntos de dados antes e depois.
print(paste("Quantidade de linhas do conjunto original",nrow(roa),
"Quantidade sem os valores nulos",nrow(df_naoNulos)))## [1] "Quantidade de linhas do conjunto original 321 Quantidade sem os valores nulos 242"print(paste("A diferença é", (nrow(roa)-nrow(df_naoNulos))))## [1] "A diferença é 79"No entanto, é possível escolher quais variáveis se pretende remover.
df_naoNulos <- roa %>% drop_na(EBITDA_2019,TotalAssets_2019)
nrow(df_naoNulos) ## [1] 3082.3.2 Substitução por zero
Podemos substir todos os valores nulos por zero de uma só vez.
df_naoNulos <- roa %>% replace(is.na(.), 0)
print(paste('nº de linhas em roa:',
nrow(roa),'e, o',
'nº de linhas em df_naoNulos',
nrow(df_naoNulos)))## [1] "nº de linhas em roa: 321 e, o nº de linhas em df_naoNulos 321"print(paste('nº de nulos em roa',
sum(is.na(roa)),'e, o',
'nº de nulos em df_naoNulos',
sum(is.na(df_naoNulos))))## [1] "nº de nulos em roa 445 e, o nº de nulos em df_naoNulos 0"Podemos observar que a quantidade de linhas permanece a mesma, porém, o conjunto criado não tem valores nulos. Embora a quantidade de nulos foi maior que a quantidade de linhas, isto é normal, pois temos várias colunas. Além disso, é possível usar outros comandos com a mesma finalidade. Como por exemplo, com o comando mutate_at e selecionarmos as colunas.
df_naoNulos <- mutate_at(roa, c("TotalAssets_2019", "EBITDA_2019"),
~replace(., is.na(.), 0))
sum(is.na(df_naoNulos))## [1] 419Como foi selecionado apenas duas colunas os valores foi substituido pela média. Não se limita a esses comando para substituir por zero.
2.3.3 Substituir pela média
Em algumas ocasiões é válido substituir os valores ausentes pela média. No entanto, como temos empresas, a análise fica mais robusta caso a média esteja próxima da média da empresa e não do conjunto de dados como um todo. Para isso é possível transformar os dados em painel primeiro e após isso, substituir os valores ausentes pela média de acordo com as empresas
Relembrando, como foi construído o dataframe roa
roa <- df %>%
select(CompanyName, contains("TotalAssets"),contains("EBITDA"))
names(roa)## [1] "CompanyName" "TotalAssets_2019" "TotalAssets_2018" "TotalAssets_2017"
## [5] "TotalAssets_2016" "TotalAssets_2015" "TotalAssets_2014" "EBITDA_2019"
## [9] "EBITDA_2018" "EBITDA_2017" "EBITDA_2016" "EBITDA_2015"
## [13] "EBITDA_2014"Transformando em painel
Uma das formas é delimitar todas as colunas para ser agrupadas gather(), posteriormente separadas do ano separate(), e enfim dispostas em outras colunas spread().
longdata2 <- roa %>%
gather("TotalAssets_2019","TotalAssets_2018","TotalAssets_2017","TotalAssets_2016",
"TotalAssets_2015","TotalAssets_2014","EBITDA_2019","EBITDA_2018","EBITDA_2017", "EBITDA_2016", "EBITDA_2015","EBITDA_2014", key = Variavel, value = valor)%>%
separate(Variavel, c("Variavel","Ano"))%>%
spread(Variavel,valor)
head(longdata2)## # A tibble: 6 x 4
## CompanyName Ano EBITDA TotalAssets
## <chr> <chr> <dbl> <dbl>
## 1 3R Petroleum Oleo e Gas SA 2014 NA NA
## 2 3R Petroleum Oleo e Gas SA 2015 NA NA
## 3 3R Petroleum Oleo e Gas SA 2016 17.4 453.
## 4 3R Petroleum Oleo e Gas SA 2017 53.1 426.
## 5 3R Petroleum Oleo e Gas SA 2018 -19.3 396.
## 6 3R Petroleum Oleo e Gas SA 2019 39.3 2109.
Outra maneira, é agrupar por meio de pivot_longer() que considera já a separação do ano pelo names_pattern e posteriormente pivot_wider() que separa em colunas deixando em painel.
roa2 <- roa
PainelRoa <- roa2 %>%
pivot_longer(-c("CompanyName"),
names_to = c("name", "Ano"),
names_pattern = "([^\\d]+)(\\d+)$") %>%
pivot_wider()
names(PainelRoa) <- c("CompanyName","Ano","AtivoTotal","EBITDA")
PainelRoa <- PainelRoa %>% arrange(CompanyName, Ano)
head(PainelRoa)## # A tibble: 6 x 4
## CompanyName Ano AtivoTotal EBITDA
## <chr> <chr> <dbl> <dbl>
## 1 3R Petroleum Oleo e Gas SA 2014 NA NA
## 2 3R Petroleum Oleo e Gas SA 2015 NA NA
## 3 3R Petroleum Oleo e Gas SA 2016 453. 17.4
## 4 3R Petroleum Oleo e Gas SA 2017 426. 53.1
## 5 3R Petroleum Oleo e Gas SA 2018 396. -19.3
## 6 3R Petroleum Oleo e Gas SA 2019 2109. 39.3Inspecionando os valores ausentes
PainelRoa %>% summarise_all(~ sum(is.na(.)))## # A tibble: 1 x 4
## CompanyName Ano AtivoTotal EBITDA
## <int> <int> <int> <int>
## 1 0 0 223 222As colunas AtivoTotal e EBITDA possuem 223 e 222 valores ausentes. O comando a seguir calcula a média dos valores ausente por empresas
Substituindo pela média
PainelRoa_media <- PainelRoa %>%
group_by(CompanyName) %>%
mutate_all(funs(ifelse(is.na(.), mean(., na.rm = TRUE),.)))## `mutate_all()` ignored the following grouping variables:
## Column `CompanyName`
## Use `mutate_at(df, vars(-group_cols()), myoperation)` to silence the message.## Warning: `funs()` was deprecated in dplyr 0.8.0.
## Please use a list of either functions or lambdas:
##
## # Simple named list:
## list(mean = mean, median = median)
##
## # Auto named with `tibble::lst()`:
## tibble::lst(mean, median)
##
## # Using lambdas
## list(~ mean(., trim = .2), ~ median(., na.rm = TRUE))Pode observar que alguns grupos foram ignorados, pois não tinham valores para calcular a média. Como podemos observar no conjunto de dados PainelRoa.
head(subset(PainelRoa,
(PainelRoa$CompanyName == 'AES Brasil Operacoes SA')
)) ## # A tibble: 6 x 4
## CompanyName Ano AtivoTotal EBITDA
## <chr> <chr> <dbl> <dbl>
## 1 AES Brasil Operacoes SA 2014 NA NA
## 2 AES Brasil Operacoes SA 2015 NA NA
## 3 AES Brasil Operacoes SA 2016 NA NA
## 4 AES Brasil Operacoes SA 2017 NA NA
## 5 AES Brasil Operacoes SA 2018 NA NA
## 6 AES Brasil Operacoes SA 2019 NA NASe manteve no PainelRoa_media
head(subset(PainelRoa_media,
(PainelRoa_media$CompanyName == 'AES Brasil Operacoes SA')
)) ## # A tibble: 6 x 4
## # Groups: CompanyName [1]
## CompanyName Ano AtivoTotal EBITDA
## <chr> <chr> <dbl> <dbl>
## 1 AES Brasil Operacoes SA 2014 NaN NaN
## 2 AES Brasil Operacoes SA 2015 NaN NaN
## 3 AES Brasil Operacoes SA 2016 NaN NaN
## 4 AES Brasil Operacoes SA 2017 NaN NaN
## 5 AES Brasil Operacoes SA 2018 NaN NaN
## 6 AES Brasil Operacoes SA 2019 NaN NaNPortanto, neste caso é necessário adicionar outra estratégia para os nulos restantes.
2.3.4 Substituir pela mediana
A mediana é o valor que mais se repete. Logo, é uma estratégia interessante para dados primários provenientes de surveys, pois a reposição fica na mesma escala que os demais. Contudo, é possível atribuir a outros conjuntos de dados também.
PainelRoa_mediana <- PainelRoa %>%
group_by(CompanyName) %>%
mutate_all(funs(ifelse(is.na(.), median(., na.rm = TRUE),.)))## `mutate_all()` ignored the following grouping variables:
## Column `CompanyName`
## Use `mutate_at(df, vars(-group_cols()), myoperation)` to silence the message.Da mesma forma, para empresas que não possuiam valores. Note
Notem as diferenças para a empresa 3R Petroleum Oleo e Gas SA
- No conjunto original PainelRoa
- No conjunto média
- No conjunto mediana
Conjunto original
head(subset(PainelRoa,
(PainelRoa_media$CompanyName == '3R Petroleum Oleo e Gas SA')
)) ## # A tibble: 6 x 4
## CompanyName Ano AtivoTotal EBITDA
## <chr> <chr> <dbl> <dbl>
## 1 3R Petroleum Oleo e Gas SA 2014 NA NA
## 2 3R Petroleum Oleo e Gas SA 2015 NA NA
## 3 3R Petroleum Oleo e Gas SA 2016 453. 17.4
## 4 3R Petroleum Oleo e Gas SA 2017 426. 53.1
## 5 3R Petroleum Oleo e Gas SA 2018 396. -19.3
## 6 3R Petroleum Oleo e Gas SA 2019 2109. 39.3Pela média
head(subset(PainelRoa_media,
(PainelRoa_media$CompanyName == '3R Petroleum Oleo e Gas SA')
)) ## # A tibble: 6 x 4
## # Groups: CompanyName [1]
## CompanyName Ano AtivoTotal EBITDA
## <chr> <chr> <dbl> <dbl>
## 1 3R Petroleum Oleo e Gas SA 2014 846. 22.6
## 2 3R Petroleum Oleo e Gas SA 2015 846. 22.6
## 3 3R Petroleum Oleo e Gas SA 2016 453. 17.4
## 4 3R Petroleum Oleo e Gas SA 2017 426. 53.1
## 5 3R Petroleum Oleo e Gas SA 2018 396. -19.3
## 6 3R Petroleum Oleo e Gas SA 2019 2109. 39.3Pela mediana
head(subset(PainelRoa_mediana,
(PainelRoa_mediana$CompanyName == '3R Petroleum Oleo e Gas SA')
)) ## # A tibble: 6 x 4
## # Groups: CompanyName [1]
## CompanyName Ano AtivoTotal EBITDA
## <chr> <chr> <dbl> <dbl>
## 1 3R Petroleum Oleo e Gas SA 2014 440. 28.3
## 2 3R Petroleum Oleo e Gas SA 2015 440. 28.3
## 3 3R Petroleum Oleo e Gas SA 2016 453. 17.4
## 4 3R Petroleum Oleo e Gas SA 2017 426. 53.1
## 5 3R Petroleum Oleo e Gas SA 2018 396. -19.3
## 6 3R Petroleum Oleo e Gas SA 2019 2109. 39.3Existem outras estratégias para substituir os valores ausentes, entre essas, pelo valor posterior, anterior, proximidade.
3 Criando novas variáveis
É muito comum ser necessário calcular variáveis com base em outras, por exemplo, para calularmos as seguintes variáveis:
- Cacular o tamanho da empresa
É necesário fazer o log da varável ativo total - Calcular o retorno sobre o ativo
- Calcular a participação de mercado.
Para calcular o ROA, é necessário calcular o lucro do período, conceitualmente, pode ser o ratio do ebitda pelo ativo total
Para esse exemplo vamos usar o conjunto PainelRoa_media, e remover os ausentes restantes.
PainelRoa_media <- drop_na(PainelRoa_media)
sum(is.na(PainelRoa_media))## [1] 0Também temos que tomar cuidado com valores negativos em algumas variáveis, como o Ativo Total, que podem estar contido na amostra. O que não é o caso, pois o mínimo é 0
summary(PainelRoa_media)## CompanyName Ano AtivoTotal EBITDA
## Length:1848 Length:1848 Min. : 0.0 Min. : -5504.00
## Class :character Class :character 1st Qu.: 433.2 1st Qu.: 21.17
## Mode :character Mode :character Median : 1849.2 Median : 164.16
## Mean : 13044.9 Mean : 1525.55
## 3rd Qu.: 7924.2 3rd Qu.: 882.16
## Max. :987419.0 Max. :145036.00Se fosse o caso, poderíamos fazer um filtro
PainelRoa_media <- PainelRoa_media%>%
filter(
AtivoTotal >0)Vamos calcular o tamanho da empresa, usualmente o log do ativo total.
PainelRoa_media <- PainelRoa_media %>%
mutate(tamanho = log(AtivoTotal))
head(PainelRoa_media)## # A tibble: 6 x 5
## # Groups: CompanyName [1]
## CompanyName Ano AtivoTotal EBITDA tamanho
## <chr> <chr> <dbl> <dbl> <dbl>
## 1 3R Petroleum Oleo e Gas SA 2014 846. 22.6 6.74
## 2 3R Petroleum Oleo e Gas SA 2015 846. 22.6 6.74
## 3 3R Petroleum Oleo e Gas SA 2016 453. 17.4 6.12
## 4 3R Petroleum Oleo e Gas SA 2017 426. 53.1 6.05
## 5 3R Petroleum Oleo e Gas SA 2018 396. -19.3 5.98
## 6 3R Petroleum Oleo e Gas SA 2019 2109. 39.3 7.65Calcular o retorno sobre o ativo
PainelRoa_media <- PainelRoa_media %>%
mutate(ROA = (EBITDA/AtivoTotal))
head(PainelRoa_media)## # A tibble: 6 x 6
## # Groups: CompanyName [1]
## CompanyName Ano AtivoTotal EBITDA tamanho ROA
## <chr> <chr> <dbl> <dbl> <dbl> <dbl>
## 1 3R Petroleum Oleo e Gas SA 2014 846. 22.6 6.74 0.0267
## 2 3R Petroleum Oleo e Gas SA 2015 846. 22.6 6.74 0.0267
## 3 3R Petroleum Oleo e Gas SA 2016 453. 17.4 6.12 0.0384
## 4 3R Petroleum Oleo e Gas SA 2017 426. 53.1 6.05 0.125
## 5 3R Petroleum Oleo e Gas SA 2018 396. -19.3 5.98 -0.0486
## 6 3R Petroleum Oleo e Gas SA 2019 2109. 39.3 7.65 0.01863.1 Group by
A maioria das operações de dados são feitas em grupos definidos por variáveis. group_by() pega um conjunto de dados existente e a converte em uma tabela agrupada onde as operações são executadas “por grupo”.
Vamos calcular a participação de mercado das empresas (market share). Precisamos obter a receita por setor. Por isso, adicionamos no conjunto de dados o TRBCSector
#Adicionamos o TRBCSector na lista
roa <- df %>%
select(CompanyName, TRBCSector, contains("TotalAssets"),contains("EBITDA"))
#Transformamos em painel
PainelRoa <- roa %>%
pivot_longer(-c("CompanyName","TRBCSector"),
names_to = c("name", "Ano"),
names_pattern = "([^\\d]+)(\\d+)$") %>%
pivot_wider()
#Renomeamos para retirar o _
names(PainelRoa) <- c("CompanyName","TRBCSector","Ano","AtivoTotal","EBITDA")
#Substituimos ausentes pela média quando possível
PainelRoa_media <- PainelRoa %>%
group_by(CompanyName) %>%
mutate_all(funs(ifelse(is.na(.), mean(., na.rm = TRUE),.)))## `mutate_all()` ignored the following grouping variables:
## Column `CompanyName`
## Use `mutate_at(df, vars(-group_cols()), myoperation)` to silence the message.#Removemos os restantes dos vazios
PainelRoa_media <- drop_na(PainelRoa_media)
#Calculamos o tamanho
PainelRoa_media <- PainelRoa_media %>%
mutate(tamanho = log(AtivoTotal))
#Calculamos o ROA
PainelRoa_media <- PainelRoa_media %>%
mutate(ROA = (EBITDA/AtivoTotal))#Calculamos o participação de mercado, em decimal
PainelRoa_media <- PainelRoa_media %>%
group_by(Ano,TRBCSector) %>%
mutate(marketShare = AtivoTotal/sum(AtivoTotal))Podemos escolher algumas medidas de tendência e dispersão por grupo
Pnl_summary <- PainelRoa_media %>%
group_by(Ano)%>%
summarise("Média marketShare" = mean(marketShare, na.rm = TRUE),
"Desvio Padrão marketShare" = sd(marketShare, na.rm = TRUE),.groups = 'drop')plot(Pnl_summary$Ano, (Pnl_summary$`Desvio Padrão marketShare`)*100, type = "b", pch = 19,
col = "blue", xlab = "Ano", ylab = "Desvio Padrão da Participação de mercado",
main="Desvio padrão da Participação de mercado por ano")
Para inspecionarmos a participação de mercado, vamos fazer a somatória do setor de tecnologia e do ano 2019, esta soma deve ser igual à 1.
tech_df <- PainelRoa_media %>%
filter(TRBCSector == 'Technology'&
Ano == 2019)
SomaTotal <- sum(tech_df$marketShare)
SomaTotal## [1] 1Note que a soma total das receitas no ano de 2019 foi igual 1. Desta forma, é possível identificar que as variáveis foram calculadas por setor e ano.
4 Bônus
Já que temos a participação de mercado, vamos calucar o herfindahl-hirschman index, grau de concentração do setor.
A formula do hhi é a seguinte
\[hhi=\sum_{i=1}^{N}{marketshare_i}^2 \]Portanto, podemos calcular da seguinte maneira
hhi <- PainelRoa_media %>%
group_by(Ano,TRBCSector) %>%
mutate(ihh = (sum(((AtivoTotal/sum(AtivoTotal)*100)^2))))Para inspecionarmos, vamos selecionar o setor de tecnologia. ATENÇÃO como os valores se repetem para cada setor em cada ano, a média se refere a concentração
tech_df <- hhi %>%
filter(TRBCSector == 'Technology')
ihhSetor <- summarise(tech_df, ihh = mean(ihh), .groups = 'drop')
plot(ihhSetor$Ano, ihhSetor$ihh, type = "b", pch = 19,
col = "red", xlab = "Ano", ylab = "Herfindahl Index",
main="Concentração de mercado do setor de tecnologia")
4.1 Criar variáveis dummies
Uma das formas para criar variáveis dicotômicas é com ifelse
media_hhi <- mean(hhi$ihh)
hhi$Concentração <- ifelse(hhi$ihh > media_hhi, 1, 0)
Para modelos econométricos, pode se atribuir o comando factor() que mesmo se as variáveis forem textos, será separada como dummies.
5 Valores discrepantes
Entre as estratégias para remover valores discrepantes, se tem a remoção de outliers e a winsorização.
5.1 Outliers
Porém primeiro temos que identificar. Um simples boxplot é uma ferramenta útil.
boxplot(hhi$tamanho)
Uma das formas para remover outliers é a remoção dos valores que ficou 1,5 vezes acima do intervalo interquartil.
fonte: Michael Galarnyk
Para obter o intervalo interquartil pode usar o comando IQR
Q1 <- quantile(hhi$tamanho, .25)
Q3 <- quantile(hhi$tamanho, .75)
IQR <- IQR(hhi$tamanho)
ls <- (Q3 + 1.5*IQR)
hhi_sem_outliers <- subset(hhi, tamanho > (Q1 - 1.5*IQR) &
tamanho < (Q3 + 1.5*IQR))
print(paste("limite superior",ls))## [1] "limite superior 13.3373751308193"boxplot(hhi_sem_outliers$tamanho)
Neste médoto, após remover os outliers, as estatísticas de tendência e dispersão mudam, e por isso, um novo quartil é encontrado podendo obter novos valores outliers. Nesse sentido, é possível tornar a removê-los.
Q3 <- quantile(hhi_sem_outliers$tamanho, .75)
IQR <- IQR(hhi_sem_outliers$tamanho)
limitesuperior <- (Q3 + 1.5*IQR)
limitesuperior## 75%
## 13.25559O intevalo interquartil e quartis mudaram.
Existem outras formas para remover outlier, aprenda mais em finnstats
5.2 Winsorização
O método para winsorização é o seguinte:
\[ f(n) = \left \{ \begin{matrix} -c & \mbox{para }x\le-c \\ x & \mbox{para }|x|\mbox{ < c} \\ c & \mbox{para }x \ge \mbox{ c} \end{matrix} \right. \]A borda inferior, todos os valores inferiores a este serão substituídos por este valor. O padrão é definido para o quantil 5% de x.
A borda alta, todos os valores maiores que isso serão substituídos por esse valor. O padrão é definido para o quantil de 95% de x.
Q_05 <- quantile(hhi$tamanho, .05)
Q_95 <- quantile(hhi$tamanho, .95)
print(paste('Quartil .05:',Q_05," ","Quartil 0.95:",Q_95))## [1] "Quartil .05: 4.14626475739567 Quartil 0.95: 10.7477904940164"
Todos os valores abaixo do quartil 0.05 devem ser substituido por Q_05 e da mesma forma todos acima d do quartil 0.95, que devem ser substituídos por Q_95.
hhi_winsor <- hhi
hhi_winsor$tamanho = ifelse(hhi_winsor$tamanho < Q_05,
Q_05,
hhi_winsor$tamanho)
hhi_winsor$tamanho = ifelse(hhi_winsor$tamanho > Q_95,
Q_95,
hhi_winsor$tamanho)
boxplot(hhi_winsor$tamanho)
Podemos observar que os máximos e mínimos respeitaram os intervalos delimitados
max(hhi_winsor$tamanho)## [1] 10.74779min(hhi_winsor$tamanho)## [1] 4.146265Uma vantegem desta técnica é que não se perde observações
6 Conclusão
O objetivo foi apresentar uma ferramenta para manipular os dados com cabeçalhos de dois níveis, como os provenientes do screener da refinitiv eikon. tranformando os dados em painel. E ainda, foi abordado algumas estratégias para lidar com valores discrepantes, bem como, formas para calcular algumas variáveis
Evidentemente, é possível obter os mesmos resultados de outras formas, isto é, não se limita aos comandos apresentados aqui. No entanto, é um caminho para aprender a aprender a linguagem R, direcionado ao pré-processamento de dados com variáveis contábeis
Para terminar podemos salvar o conjunto de dados organizado
#=====================SALVAR CONJUNTO FINAL===============================
#install.packages("writexl")
library(writexl)
write_xlsx(hhi_winsor, "hhi_winsor.xlsx")