joins---alunos

Prévia do material em texto

Juntando bases de dados
Mauricio Izumi (UFES)
20 de maio de 2022
Introdução
Na maioria das vezes em que pretendemos fazer um trabalho empírico, os dados de que necessi-
tamos não estão todos compilados em uma única tabela. Eles geralmente estão dispersos em
diferentes bases de dados oriundos de diferentes fontes. Portanto, precisamos aprender como
juntar diferentes bases de dados. Este é o objetivo desta seção.
Iremos focar nas funções do pacote dplyr. E, como de costume, após instalá-lo, o primeiro passo
é carregar o pacote:
library(dplyr)
Principais funções (joins)
Para nos familiarizarmos com as funções utilizadas para juntar bases de dados, vamos, em
primeiro lugar, construir duas bases de dados bem simples que serão utilizadas nos exemplos.
tab1 <- data.frame("jogador" = c("Dudu","Deyverson","Hulk", "Gabriel","Marinho"),
"time" = c("palmeiras","palmeiras","atletico","flamengo","flamengo"))
tab2 <- data.frame("jogador" = c("Veiga","Deyverson","Zaracho","Gabriel","Andreas"),
"gols" = c(10,15,3,1,-1))
tab1;tab2
## jogador time
## 1 Dudu palmeiras
## 2 Deyverson palmeiras
## 3 Hulk atletico
## 4 Gabriel flamengo
## 5 Marinho flamengo
## jogador gols
## 1 Veiga 10
## 2 Deyverson 15
## 3 Zaracho 3
1
## 4 Gabriel 1
## 5 Andreas -1
A tab1 contém duas variáveis, uma chamada “jogador” e outra chamada “time”. Nesta tabela
temos o nome dos atacantes e o time que defendem. Já a tab2 contém uma variável chamada
“jogador” e outra chamada “gols”. Aqui temos o nome dos jogadores que marcaram gols em
um campeonato qualquer (vejam que não temos apenas atacantes).Notem que ambas tabelas
possuem uma variável em comum, chamada “jogador”. Essa é a variável que faz a correpondência
entre as duas tabelas.
Chamamos de chaves primárias as variáveis utilizadas para conectar cada par de tabelas. Esta
variável deve ser capaz de identicar uma única observação. Em nosso exemplo, a chave primária
é a variável “jogador”. Muitas vezes, a chave é composta por mais de uma variável. Por exemplo,
se tivéssemos em nossas bases de dados dois jogadores chamados “Gabriel” um do Palmeiras e
outro do Flamengo, nós precisaríamos utilizar duas variáveis como chaves primárias.
Se quisermos ter as informações dos jogadores, times e gols em uma mesma tabela, precisamos
juntar a tab1 e a tab2. Porém, há várias formas de se fazer essa fusão de tabelas.
inner_join(x, y)
Combina pares de observações em que as chaves primárias são iguais.
tab.inner <- inner_join(tab1, tab2, by = "jogador")
tab.inner
## jogador time gols
## 1 Deyverson palmeiras 15
## 2 Gabriel flamengo 1
O resultado que observamos é uma tabela que contém apenas dois jogadores: Deyverson e
Gabriel. Isso ocorre porque a função inner_join combina e retorna apenas as observações que
possuem chaves primárias iguais (variável “jogador”). Como os únicos atacantes (tab1) que
marcaram gols (tab2) foram o Deyverson e Gabriel, eles são os únicos resultados que observamos.
Outro ponto importante é que a função inner_join não retorna apenas a variável “jogador”, mas
também a variável “time” da tab1 e a variável “gols” da tab2.
anti_join(x, y)
O anti_join é praticamente o oposto do inner_join. Esta função retorna todas as observações da
tab1 que não possuem correspondência na tab2. No caso, todos os atacantes que NÃO marcaram
gols. Veja também que ela retorna apenas a variável “time” da *tab1".
tab.anti <- anti_join(tab1, tab2, by = "jogador")
tab.anti
## jogador time
## 1 Dudu palmeiras
2
## 2 Hulk atletico
## 3 Marinho flamengo
Nota-se também que a soma de linhas da tab.inner e da tab.anti é igual ao número de linhas na
tab1, já que uma é o complemento da outra.
nrow(tab.inner) + nrow(tab.anti) == nrow(tab1)
## [1] TRUE
semi_join(x, y)
Retorna todas as linhas da tab1 para as quais existem valores correspondente na tab2, mantendo
apenas as da tab1. É parecido com o inner_join, a diferença é as variáveis da tab2 não são
incluídas na tabela.
tab.semi <- semi_join(tab1, tab2, by = "jogador")
tab.semi
## jogador time
## 1 Deyverson palmeiras
## 2 Gabriel flamengo
left_join(x, y)
Retorna todas as observações da tab1 e somente aquelas da tab2 que também estão na tab1.
tab.left <- left_join(tab1, tab2, by = "jogador")
tab.left
## jogador time gols
## 1 Dudu palmeiras NA
## 2 Deyverson palmeiras 15
## 3 Hulk atletico NA
## 4 Gabriel flamengo 1
## 5 Marinho flamengo NA
right_join(x, y)
Retorna todas as observações da tab2 e somente aquelas da tab1 que também estão na tab2.
tab.right <- right_join(tab1, tab2, by = "jogador")
tab.right
## jogador time gols
## 1 Deyverson palmeiras 15
## 2 Gabriel flamengo 1
## 3 Veiga <NA> 10
3
## 4 Zaracho <NA> 3
## 5 Andreas <NA> -1
full_join(x, y)
Retorna todas as observações da tab1 e tab2.
tab.full <- full_join(tab1, tab2, by = "jogador")
tab.full
## jogador time gols
## 1 Dudu palmeiras NA
## 2 Deyverson palmeiras 15
## 3 Hulk atletico NA
## 4 Gabriel flamengo 1
## 5 Marinho flamengo NA
## 6 Veiga <NA> 10
## 7 Zaracho <NA> 3
## 8 Andreas <NA> -1
Nos exemplos acima, as relações são de um para um. Para cada observação da tab1 há apenas
uma observações na tab2. No entanto, as relações entre tabelas normalmente são de um para
muitos. Vejamos isso no próximo exemplo.
Exemplo: IMDb
Nesta seção iremos analisar os dados IMDb (Internet Movie Database), que é uma base de dados
online de informação sobre cinema.
O primeiro passo é carregar os pacotes que iremos utilizar:
library(data.table)
library(tidyr)
library(humaniformat)
library(genderBR)
Depois disso, vamos carregar os dados:
#lista de filmes
title.akas.br <- as.data.frame(fread("title.akas.br.csv.gz"))
#informações dos filmes
title.basics.br <- as.data.frame(fread("title.basics.br.csv.gz"))
#avaliação dos filmes
title.ratings.br <- as.data.frame(fread("title.ratings.br.csv.gz"))
#equipe dos filmes
title.principals.br <- as.data.frame(fread("title.principals.br.csv.gz"))
4
#dicionário de nomes
name.basics.br <- as.data.frame(fread("name.basics.br.csv.gz"))
Vamos avaliar o conteúdo de cada uma dessas tabelas. Começemos pela lista de filmes (ti-
tle.akas.br).
head(title.akas.br)
## titleId title
## 1 tt0000012 A Chegada de um Trem à Estação
## 2 tt0000014 O Regador Regado
## 3 tt0000028 A Pesca do Peixe Dourado
## 4 tt0000029 O Almoço do Bebê
## 5 tt0000033 A Acrobacia
## 6 tt0000041 A Batalha de Neve
nrow(title.akas.br)
## [1] 83148
length(which(duplicated(title.akas.br$title)))
## [1] 6706
subset(title.akas.br, title.akas.br$title == "Califórnia")
## titleId title
## 1690 tt0017727 Califórnia
## 9794 tt0038392 Califórnia
## 16489 tt0056896 Califórnia
## 21232 tt0075796 Califórnia
## 70141 tt4532634 Califórnia
A chave primária é a variável “titleId”. Temos 83.148 filmes (apenas filmes que saíram no Brasil).
Nota-se também que há 6706 filmes com o mesmo. Por isso que utilizamos uma chave primária
diferente do nome dos filmes. Há, por exemplo, 5 filmes chamados “Califórnia”.
Vejamos agora as informações dos filmes (title.basics.br).
head(title.basics.br)
## tconst titleType isAdult startYear runtimeMinutes genres
## 1 tt0000012 short 0 1896 1 Documentary,Short
## 2 tt0000014 short 0 1895 1 Comedy,Short
## 3 tt0000028 short 0 1895 1 Documentary,Short
## 4 tt0000029 short 0 1895 1 Documentary,Short
## 5 tt0000033 short 0 1895 1 Comedy,Documentary,Short
## 6 tt0000041 short 0 1897 1 Comedy,Documentary,Short
5
nrow(title.basics.br)
## [1] 83148
#ver as informações dos 5 filmes chamados califórnia
cali <- subset(title.akas.br$titleId, title.akas.br$title == "Califórnia")
#cria vetor com os códigos dos filmes
subset(title.basics.br, title.basics.br$tconst %in% cali)
## tconst titleType isAdult startYear runtimeMinutes genres
## 1690 tt0017727 movie 0 1927 50 Western
## 9794 tt0038392 movie 0 1947 97 Western
## 16489 tt0056896 movie 0 1963 86 Western
## 21232 tt0075796 movie 0 1977 98 Drama,Western
## 70141 tt4532634 movie 0 2015 85 Drama,Romance
Agora o nome da chave primária é “tconst”. Aqui há também 83.148 filmes.
A próxima tabela contéma avaliação dos filmes (title.ratings.br).
head(title.ratings.br) #chave primária "tconst"
## tconst averageRating numVotes
## 1 tt0000012 7.5 11541
## 2 tt0000014 7.1 5177
## 3 tt0000028 5.2 997
## 4 tt0000029 6.0 3179
## 5 tt0000033 5.6 965
## 6 tt0000041 6.7 1712
nrow(title.basics.br) #83.148 filmes
## [1] 83148
#ver as informações dos 5 filmes chamados califórnia
subset(title.ratings.br, title.ratings.br$tconst %in% cali)
## tconst averageRating numVotes
## 895 tt0017727 6.1 13
## 8041 tt0038392 6.2 955
## 14644 tt0056896 4.9 78
## 19367 tt0075796 6.7 575
## 54147 tt4532634 7.1 764
Novamente, o nome da chave primária é “tconst” e há 83.148 filmes.
Passando agora para as informações sobre as pessoas que participaram dos filmes temos a
tabela title.principals.br.
6
head(title.principals.br) #chave primária: "nconst"
## tconst nconst category
## 1 tt0000012 nm2880396 self
## 2 tt0000012 nm9735580 self
## 3 tt0000012 nm0525900 self
## 4 tt0000012 nm9735581 self
## 5 tt0000012 nm0525908 director
## 6 tt0000012 nm0525910 director
nrow(title.principals.br) #699.200 observações
## [1] 699200
#ver a equipe do filme "tt4532634"
subset(title.principals.br, title.principals.br$tconst == "tt4532634")
## tconst nconst category
## 602654 tt4532634 nm3689258 producer
## 602655 tt4532634 nm7198741 actress
## 602656 tt4532634 nm5569248 actor
## 602657 tt4532634 nm0087794 actor
## 602658 tt4532634 nm4962201 actor
## 602659 tt4532634 nm1495717 director
## 602660 tt4532634 nm1389636 writer
## 602661 tt4532634 nm1336100 writer
## 602662 tt4532634 nm5902376 producer
## 602663 tt4532634 nm2803340 producer
Vejam que para o mesmo filme há várias linhas. Temos os produtores, roteiristas, diretores,
atores para um mesmo filme. Por exemplo, o filme “tt4532634” tem 10 observações. Este é um
daqueles casos em que a relação é de um para muitos que comentamos anteriormente.
Finalmente, temos o dicionário de nomes (name.basics.br). Esta tabela é importante porque é
interessante saber o nome das pessoas e não só a chave primária.
head(name.basics.br) #chave primária: "nconst"
## nconst primaryName birthYear deathYear
## 1 nm0000001 Fred Astaire 1899 1987
## 2 nm0000002 Lauren Bacall 1924 2014
## 3 nm0000003 Brigitte Bardot 1934 NA
## 4 nm0000004 John Belushi 1949 1982
## 5 nm0000005 Ingmar Bergman 1918 2007
## 6 nm0000006 Ingrid Bergman 1915 1982
nrow(name.basics.br) #279.826 observações
7
## [1] 279826
#ver as informações da equipe do filme "tt4532634"
cali.n <- subset(title.principals.br$nconst, title.principals.br$tconst == "tt4532634")
subset(name.basics.br, name.basics.br$nconst %in% cali.n)
## nconst primaryName birthYear deathYear
## 15420 nm0087794 Caio Blat 1980 NA
## 160579 nm1336100 Mariana Veríssimo NA NA
## 162793 nm1389636 Francisco Guarnieri 1984 NA
## 166467 nm1495717 Marina Person 1969 NA
## 200563 nm2803340 Gustavo Rosa de Moura 1975 NA
## 215751 nm3689258 Giulia Setembrino NA NA
## 233021 nm4962201 Giovanni Gallo NA NA
## 240613 nm5569248 Caio Horowicz NA NA
## 244246 nm5902376 Carmem Maia NA NA
## 255979 nm7198741 Clara Gallo NA NA
Agora chegou a hora de juntar essas diferentes tabelas. Por um lado, criar tabelas separadas tem
a vantagem de evitar a repetição de informações. Por outro, isso dificulta análise, uma vez que
as informações estão espalhadas em tabelas diferentes.
Vamos começar fazendo um left_join para os filmes.
filmes <- title.akas.br %>%
left_join(title.basics.br, by = c("titleId" = "tconst"))
head(filmes)
## titleId title titleType isAdult startYear
## 1 tt0000012 A Chegada de um Trem à Estação short 0 1896
## 2 tt0000014 O Regador Regado short 0 1895
## 3 tt0000028 A Pesca do Peixe Dourado short 0 1895
## 4 tt0000029 O Almoço do Bebê short 0 1895
## 5 tt0000033 A Acrobacia short 0 1895
## 6 tt0000041 A Batalha de Neve short 0 1897
## runtimeMinutes genres
## 1 1 Documentary,Short
## 2 1 Comedy,Short
## 3 1 Documentary,Short
## 4 1 Documentary,Short
## 5 1 Comedy,Documentary,Short
## 6 1 Comedy,Documentary,Short
Podemos juntar mais tabelas de uma única vez.
filmes <- title.akas.br %>%
left_join(title.basics.br, by = c("titleId" = "tconst")) %>%
left_join(title.ratings.br, by = c("titleId" = "tconst"))
8
head(filmes)
## titleId title titleType isAdult startYear
## 1 tt0000012 A Chegada de um Trem à Estação short 0 1896
## 2 tt0000014 O Regador Regado short 0 1895
## 3 tt0000028 A Pesca do Peixe Dourado short 0 1895
## 4 tt0000029 O Almoço do Bebê short 0 1895
## 5 tt0000033 A Acrobacia short 0 1895
## 6 tt0000041 A Batalha de Neve short 0 1897
## runtimeMinutes genres averageRating numVotes
## 1 1 Documentary,Short 7.5 11541
## 2 1 Comedy,Short 7.1 5177
## 3 1 Documentary,Short 5.2 997
## 4 1 Documentary,Short 6.0 3179
## 5 1 Comedy,Documentary,Short 5.6 965
## 6 1 Comedy,Documentary,Short 6.7 1712
Vejamos os filmes chamados Califórnia.
filmes %>%
filter(title == "Califórnia")
## titleId title titleType isAdult startYear runtimeMinutes genres
## 1 tt0017727 Califórnia movie 0 1927 50 Western
## 2 tt0038392 Califórnia movie 0 1947 97 Western
## 3 tt0056896 Califórnia movie 0 1963 86 Western
## 4 tt0075796 Califórnia movie 0 1977 98 Drama,Western
## 5 tt4532634 Califórnia movie 0 2015 85 Drama,Romance
## averageRating numVotes
## 1 6.1 13
## 2 6.2 955
## 3 4.9 78
## 4 6.7 575
## 5 7.1 764
Agora um left_join para a equipe.
equipe <- title.principals.br %>%
left_join(name.basics.br, by = "nconst")
head(equipe); nrow(equipe)
## tconst nconst category primaryName birthYear deathYear
## 1 tt0000012 nm2880396 self Madeleine Koehler 1895 1970
## 2 tt0000012 nm9735580 self Marcel Koehler NA NA
## 3 tt0000012 nm0525900 self Mrs. Auguste Lumiere 1874 1963
## 4 tt0000012 nm9735581 self Jeanne-Joséphine Lumière NA NA
## 5 tt0000012 nm0525908 director Auguste Lumière 1862 1954
9
## 6 tt0000012 nm0525910 director Louis Lumière 1864 1948
## [1] 699200
Vamos acrescentar o nome do filme à tabela.
equipe <- equipe %>%
left_join(title.akas.br, by = c("tconst" = "titleId"))
head(equipe);nrow(equipe)
## tconst nconst category primaryName birthYear deathYear
## 1 tt0000012 nm2880396 self Madeleine Koehler 1895 1970
## 2 tt0000012 nm9735580 self Marcel Koehler NA NA
## 3 tt0000012 nm0525900 self Mrs. Auguste Lumiere 1874 1963
## 4 tt0000012 nm9735581 self Jeanne-Joséphine Lumière NA NA
## 5 tt0000012 nm0525908 director Auguste Lumière 1862 1954
## 6 tt0000012 nm0525910 director Louis Lumière 1864 1948
## title
## 1 A Chegada de um Trem à Estação
## 2 A Chegada de um Trem à Estação
## 3 A Chegada de um Trem à Estação
## 4 A Chegada de um Trem à Estação
## 5 A Chegada de um Trem à Estação
## 6 A Chegada de um Trem à Estação
## [1] 699200
Finalmente, vamos selecionar o filme Califórnia de 2015 (tt4532634).
equipe %>%
filter(tconst == "tt4532634")
## tconst nconst category primaryName birthYear deathYear
## 1 tt4532634 nm3689258 producer Giulia Setembrino NA NA
## 2 tt4532634 nm7198741 actress Clara Gallo NA NA
## 3 tt4532634 nm5569248 actor Caio Horowicz NA NA
## 4 tt4532634 nm0087794 actor Caio Blat 1980 NA
## 5 tt4532634 nm4962201 actor Giovanni Gallo NA NA
## 6 tt4532634 nm1495717 director Marina Person 1969 NA
## 7 tt4532634 nm1389636 writer Francisco Guarnieri 1984 NA
## 8 tt4532634 nm1336100 writer Mariana Veríssimo NA NA
## 9 tt4532634 nm5902376 producer Carmem Maia NA NA
## 10 tt4532634 nm2803340 producer Gustavo Rosa de Moura 1975 NA
## title
## 1 Califórnia
## 2 Califórnia
## 3 Califórnia
## 4 Califórnia
10
## 5 Califórnia
## 6 Califórnia
## 7 Califórnia
## 8 Califórnia
## 9 Califórnia
## 10 Califórnia
A partir dessas tabelas, podemos fazer algumas análises. Por exemplo, qual a participação das
mulheres nos filmes?
Vamos calcular o número de atores e de atrizes por filme.
n.atores <- equipe %>%
group_by(tconst, category) %>%
summarize(n = n()) %>%
spread(key = category,
value = n,
fill = 0) %>%
select(tconst, actor, actress)
Agora, o número e diretores homens e mulheres.
diretores <- equipe %>%
filter(category == "director") %>%
select(tconst,primaryName)
Como saber o sexo dos diretores a partir do nome?
#1.Selecione o primeiro nome (humaniformat)
diretores$first_name <- first_name(diretores$primaryName)
#2. Captura o sexo a partir do Censo de 2010 (genderBR)
diretores$sexo <- get_gender(diretores$first_name)
#3. Calcula o número de diretores por sexo por filmes
n.diretores <- diretores %>%
group_by(tconst, sexo) %>%
summarize(n = n()) %>%
spread(key = sexo,
value = n,
fill = 0) %>%
select(tconst, Female, Male) %>%
filter(Female > 0 | Male > 0) %>%
rename(director.fem = Female, director.male = Male)
#Junta os dados
filmes <- filmes %>%
left_join(n.atores, by = c("titleId" = "tconst")) %>%
11
left_join(n.diretores, by = c("titleId" = "tconst"))
Estes são os resultados:
summary(filmes$actor)
## Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
## 0.000 1.000 2.000 2.353 3.000 10.000 1318
summary(filmes$actress)
## Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
## 0.000 0.000 1.000 1.458 2.000 10.000 1318
summary(filmes$director.male)
## Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
## 0.000 1.000 1.000 0.986 1.000 6.000 26829
summary(filmes$director.fem)
## Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
## 0.000 0.000 0.000 0.118 0.000 5.000 26829
Exercícios
1. A base dedados eleitores.csv contém o número de eleitores registrados em cada município
do país em cada eleição local (2000, 2004, 2008 e 2012). Há 22.260 observações nessa
tabela. A variável “COD_MUN” indica o código numérico que o TSE atribui a cada um
dos municípios do Brasil e a variável “ELEICAO” indica o ano da eleição. A base de dados
candpref.csv possui o número de candidatos a prefeito em cada uma dessas eleições. Qual
a chave primária devemos utilizar para juntar essas duas tabelas? É necessário utilizar
mais de uma variável para termos uma chave-primária? Faça um left_join entre essas
duas tabelas.
2. A base de dados coords.csv possui as coordenadas geográficas de cada um dos municípios
(latitude e longitude), além do código numérico do município (TSE). Como as coordenadas
geográficas são uma característica fixa do município (que não se altera ao longo do tempo),
cada município possui uma única entrada nessa tabela. Se quisermos acrescentar essa
informação tabela criada no exercício anterior, qual join deveríamos utilizar? Faça o join.
12
	Introdução
	Principais funções (joins)
	inner_join(x, y)
	anti_join(x, y)
	semi_join(x, y)
	left_join(x, y)
	right_join(x, y)
	full_join(x, y)
	Exemplo: IMDb
	Exercícios