Desafio 13

Author

Maria Eduarda Villéla Silva 

Este arquivo foi gerado em: 21/10/2025 23:55:48
library(reticulate) #para usar python no rstudio
Warning: pacote 'reticulate' foi compilado no R versão 4.4.3
#criando um ambiente virtual
#virtualenv_create("r-reticulate")
use_virtualenv("r-reticulate", required = TRUE)

#use_python("C:/Users/madud/AppData/Local/R/cache/R/reticulate/uv/cache/archive-v0/3ug1lw3PhFztLXk4AkU1r/Scripts/python.exe", required = TRUE)

py_config()
python:         C:/Users/madud/Documents/.virtualenvs/r-reticulate/Scripts/python.exe
libpython:      C:/Users/madud/AppData/Local/Programs/Python/Python313/python313.dll
pythonhome:     C:/Users/madud/Documents/.virtualenvs/r-reticulate
version:        3.13.9 (tags/v3.13.9:8183fa5, Oct 14 2025, 14:09:13) [MSC v.1944 64 bit (AMD64)]
Architecture:   64bit
numpy:          C:/Users/madud/Documents/.virtualenvs/r-reticulate/Lib/site-packages/numpy
numpy_version:  2.3.4

NOTE: Python version was forced by use_python() function
py_install("pandas")
Using virtual environment "C:/Users/madud/Documents/.virtualenvs/r-reticulate" ...
+ "C:/Users/madud/Documents/.virtualenvs/r-reticulate/Scripts/python.exe" -m pip install --upgrade --no-user pandas
 #instalando pandas
  1. Crie um banco de dados SQLite utilizando os 3 arquivos acima. O banco de dados deve conter as seguintes tabelas: basics, ratings e principals
import pandas as pd
import sqlite3
import gc #"garbage collector", usado para limpar a memória

# Cria a conexão com o banco SQLite
con = sqlite3.connect("imdb.db")
cursor = con.cursor()

# Pega o caminho dos arquivos que vamos ler e renomeia eles
arquivos = [
    ("title.basics0.tsv.gz", "basics"),
    ("title.ratings.tsv.gz", "ratings"),
    ("title.principals0.tsv.gz", "principals")
]

# Vamos fazer leitura por chuncks. Para isso, defiinimos o tamanho do chunk
chunk_size = 500_000

# Função que lê os bancos por chuncks e grava cada pedaço no banco de dados do SQL
for file_path, table_name in arquivos:
    print(f"\n Lendo e gravando '{table_name}' em blocos...")

    # Remove tabela antiga se existir (para evitar conflitos)
    cursor.execute(f"DROP TABLE IF EXISTS {table_name}")
    con.commit()

    i = 0
    for chunk in pd.read_csv(
        file_path,
        sep="\t",
        compression="gzip",
        dtype=str,
        na_values="\\N",
        chunksize=chunk_size
    ):
        i += 1
        chunk.to_sql(table_name, con, if_exists="append", index=False)
        print(f"  Bloco {i} gravado ({len(chunk)} linhas)")
        del chunk
        gc.collect()

    print(f" '{table_name}' concluído!")

 Lendo e gravando 'basics' em blocos...
<sqlite3.Cursor object at 0x0000020FA1632940>
500000
  Bloco 1 gravado (500000 linhas)
15
500000
  Bloco 2 gravado (500000 linhas)
0
500000
  Bloco 3 gravado (500000 linhas)
0
500000
  Bloco 4 gravado (500000 linhas)
0
500000
  Bloco 5 gravado (500000 linhas)
0
500000
  Bloco 6 gravado (500000 linhas)
0
500000
  Bloco 7 gravado (500000 linhas)
0
500000
  Bloco 8 gravado (500000 linhas)
0
500000
  Bloco 9 gravado (500000 linhas)
0
500000
  Bloco 10 gravado (500000 linhas)
0
500000
  Bloco 11 gravado (500000 linhas)
0
500000
  Bloco 12 gravado (500000 linhas)
0
500000
  Bloco 13 gravado (500000 linhas)
0
500000
  Bloco 14 gravado (500000 linhas)
0
500000
  Bloco 15 gravado (500000 linhas)
0
500000
  Bloco 16 gravado (500000 linhas)
0
500000
  Bloco 17 gravado (500000 linhas)
0
500000
  Bloco 18 gravado (500000 linhas)
0
500000
  Bloco 19 gravado (500000 linhas)
0
500000
  Bloco 20 gravado (500000 linhas)
0
500000
  Bloco 21 gravado (500000 linhas)
0
500000
  Bloco 22 gravado (500000 linhas)
0
144942
  Bloco 23 gravado (144942 linhas)
0
 'basics' concluído!

 Lendo e gravando 'ratings' em blocos...
<sqlite3.Cursor object at 0x0000020FA1632940>
500000
  Bloco 1 gravado (500000 linhas)
0
500000
  Bloco 2 gravado (500000 linhas)
0
484615
  Bloco 3 gravado (484615 linhas)
0
 'ratings' concluído!

 Lendo e gravando 'principals' em blocos...
<sqlite3.Cursor object at 0x0000020FA1632940>
500000
  Bloco 1 gravado (500000 linhas)
0
500000
  Bloco 2 gravado (500000 linhas)
0
500000
  Bloco 3 gravado (500000 linhas)
0
500000
  Bloco 4 gravado (500000 linhas)
0
500000
  Bloco 5 gravado (500000 linhas)
0
500000
  Bloco 6 gravado (500000 linhas)
0
500000
  Bloco 7 gravado (500000 linhas)
0
500000
  Bloco 8 gravado (500000 linhas)
0
500000
  Bloco 9 gravado (500000 linhas)
0
500000
  Bloco 10 gravado (500000 linhas)
0
500000
  Bloco 11 gravado (500000 linhas)
0
500000
  Bloco 12 gravado (500000 linhas)
0
500000
  Bloco 13 gravado (500000 linhas)
0
500000
  Bloco 14 gravado (500000 linhas)
0
500000
  Bloco 15 gravado (500000 linhas)
0
500000
  Bloco 16 gravado (500000 linhas)
0
500000
  Bloco 17 gravado (500000 linhas)
0
500000
  Bloco 18 gravado (500000 linhas)
0
500000
  Bloco 19 gravado (500000 linhas)
0
500000
  Bloco 20 gravado (500000 linhas)
0
500000
  Bloco 21 gravado (500000 linhas)
0
500000
  Bloco 22 gravado (500000 linhas)
0
500000
  Bloco 23 gravado (500000 linhas)
0
500000
  Bloco 24 gravado (500000 linhas)
0
500000
  Bloco 25 gravado (500000 linhas)
0
500000
  Bloco 26 gravado (500000 linhas)
0
500000
  Bloco 27 gravado (500000 linhas)
0
500000
  Bloco 28 gravado (500000 linhas)
0
500000
  Bloco 29 gravado (500000 linhas)
0
500000
  Bloco 30 gravado (500000 linhas)
0
500000
  Bloco 31 gravado (500000 linhas)
0
500000
  Bloco 32 gravado (500000 linhas)
0
500000
  Bloco 33 gravado (500000 linhas)
0
500000
  Bloco 34 gravado (500000 linhas)
0
500000
  Bloco 35 gravado (500000 linhas)
0
500000
  Bloco 36 gravado (500000 linhas)
0
500000
  Bloco 37 gravado (500000 linhas)
0
500000
  Bloco 38 gravado (500000 linhas)
0
500000
  Bloco 39 gravado (500000 linhas)
0
500000
  Bloco 40 gravado (500000 linhas)
0
500000
  Bloco 41 gravado (500000 linhas)
0
500000
  Bloco 42 gravado (500000 linhas)
0
500000
  Bloco 43 gravado (500000 linhas)
0
500000
  Bloco 44 gravado (500000 linhas)
0
500000
  Bloco 45 gravado (500000 linhas)
0
500000
  Bloco 46 gravado (500000 linhas)
0
500000
  Bloco 47 gravado (500000 linhas)
0
500000
  Bloco 48 gravado (500000 linhas)
0
500000
  Bloco 49 gravado (500000 linhas)
0
500000
  Bloco 50 gravado (500000 linhas)
0
500000
  Bloco 51 gravado (500000 linhas)
0
500000
  Bloco 52 gravado (500000 linhas)
0
500000
  Bloco 53 gravado (500000 linhas)
0
500000
  Bloco 54 gravado (500000 linhas)
0
500000
  Bloco 55 gravado (500000 linhas)
0
500000
  Bloco 56 gravado (500000 linhas)
0
500000
  Bloco 57 gravado (500000 linhas)
0
500000
  Bloco 58 gravado (500000 linhas)
0
500000
  Bloco 59 gravado (500000 linhas)
0
500000
  Bloco 60 gravado (500000 linhas)
0
500000
  Bloco 61 gravado (500000 linhas)
0
500000
  Bloco 62 gravado (500000 linhas)
0
500000
  Bloco 63 gravado (500000 linhas)
0
500000
  Bloco 64 gravado (500000 linhas)
0
500000
  Bloco 65 gravado (500000 linhas)
0
500000
  Bloco 66 gravado (500000 linhas)
0
500000
  Bloco 67 gravado (500000 linhas)
0
500000
  Bloco 68 gravado (500000 linhas)
0
500000
  Bloco 69 gravado (500000 linhas)
0
500000
  Bloco 70 gravado (500000 linhas)
0
500000
  Bloco 71 gravado (500000 linhas)
0
500000
  Bloco 72 gravado (500000 linhas)
0
500000
  Bloco 73 gravado (500000 linhas)
0
500000
  Bloco 74 gravado (500000 linhas)
0
500000
  Bloco 75 gravado (500000 linhas)
0
500000
  Bloco 76 gravado (500000 linhas)
0
500000
  Bloco 77 gravado (500000 linhas)
0
500000
  Bloco 78 gravado (500000 linhas)
0
500000
  Bloco 79 gravado (500000 linhas)
0
500000
  Bloco 80 gravado (500000 linhas)
0
500000
  Bloco 81 gravado (500000 linhas)
0
500000
  Bloco 82 gravado (500000 linhas)
0
500000
  Bloco 83 gravado (500000 linhas)
0
500000
  Bloco 84 gravado (500000 linhas)
0
500000
  Bloco 85 gravado (500000 linhas)
0
500000
  Bloco 86 gravado (500000 linhas)
0
500000
  Bloco 87 gravado (500000 linhas)
0
500000
  Bloco 88 gravado (500000 linhas)
0
500000
  Bloco 89 gravado (500000 linhas)
0
500000
  Bloco 90 gravado (500000 linhas)
0
500000
  Bloco 91 gravado (500000 linhas)
0
500000
  Bloco 92 gravado (500000 linhas)
0
500000
  Bloco 93 gravado (500000 linhas)
0
500000
  Bloco 94 gravado (500000 linhas)
0
500000
  Bloco 95 gravado (500000 linhas)
0
500000
  Bloco 96 gravado (500000 linhas)
0
500000
  Bloco 97 gravado (500000 linhas)
0
500000
  Bloco 98 gravado (500000 linhas)
0
500000
  Bloco 99 gravado (500000 linhas)
0
500000
  Bloco 100 gravado (500000 linhas)
0
500000
  Bloco 101 gravado (500000 linhas)
0
500000
  Bloco 102 gravado (500000 linhas)
0
500000
  Bloco 103 gravado (500000 linhas)
0
500000
  Bloco 104 gravado (500000 linhas)
0
500000
  Bloco 105 gravado (500000 linhas)
0
500000
  Bloco 106 gravado (500000 linhas)
0
500000
  Bloco 107 gravado (500000 linhas)
0
500000
  Bloco 108 gravado (500000 linhas)
0
500000
  Bloco 109 gravado (500000 linhas)
0
500000
  Bloco 110 gravado (500000 linhas)
0
500000
  Bloco 111 gravado (500000 linhas)
0
500000
  Bloco 112 gravado (500000 linhas)
0
500000
  Bloco 113 gravado (500000 linhas)
0
500000
  Bloco 114 gravado (500000 linhas)
0
500000
  Bloco 115 gravado (500000 linhas)
0
500000
  Bloco 116 gravado (500000 linhas)
0
500000
  Bloco 117 gravado (500000 linhas)
0
500000
  Bloco 118 gravado (500000 linhas)
0
500000
  Bloco 119 gravado (500000 linhas)
0
500000
  Bloco 120 gravado (500000 linhas)
0
500000
  Bloco 121 gravado (500000 linhas)
0
500000
  Bloco 122 gravado (500000 linhas)
0
500000
  Bloco 123 gravado (500000 linhas)
0
500000
  Bloco 124 gravado (500000 linhas)
0
500000
  Bloco 125 gravado (500000 linhas)
0
500000
  Bloco 126 gravado (500000 linhas)
0
500000
  Bloco 127 gravado (500000 linhas)
0
500000
  Bloco 128 gravado (500000 linhas)
0
500000
  Bloco 129 gravado (500000 linhas)
0
500000
  Bloco 130 gravado (500000 linhas)
0
500000
  Bloco 131 gravado (500000 linhas)
0
500000
  Bloco 132 gravado (500000 linhas)
0
500000
  Bloco 133 gravado (500000 linhas)
0
500000
  Bloco 134 gravado (500000 linhas)
0
500000
  Bloco 135 gravado (500000 linhas)
0
500000
  Bloco 136 gravado (500000 linhas)
0
500000
  Bloco 137 gravado (500000 linhas)
0
500000
  Bloco 138 gravado (500000 linhas)
0
500000
  Bloco 139 gravado (500000 linhas)
0
500000
  Bloco 140 gravado (500000 linhas)
0
500000
  Bloco 141 gravado (500000 linhas)
0
500000
  Bloco 142 gravado (500000 linhas)
0
500000
  Bloco 143 gravado (500000 linhas)
0
500000
  Bloco 144 gravado (500000 linhas)
0
500000
  Bloco 145 gravado (500000 linhas)
0
500000
  Bloco 146 gravado (500000 linhas)
0
500000
  Bloco 147 gravado (500000 linhas)
0
500000
  Bloco 148 gravado (500000 linhas)
0
500000
  Bloco 149 gravado (500000 linhas)
0
500000
  Bloco 150 gravado (500000 linhas)
0
500000
  Bloco 151 gravado (500000 linhas)
0
500000
  Bloco 152 gravado (500000 linhas)
0
500000
  Bloco 153 gravado (500000 linhas)
0
500000
  Bloco 154 gravado (500000 linhas)
0
500000
  Bloco 155 gravado (500000 linhas)
0
500000
  Bloco 156 gravado (500000 linhas)
0
500000
  Bloco 157 gravado (500000 linhas)
0
500000
  Bloco 158 gravado (500000 linhas)
0
500000
  Bloco 159 gravado (500000 linhas)
0
500000
  Bloco 160 gravado (500000 linhas)
0
500000
  Bloco 161 gravado (500000 linhas)
0
500000
  Bloco 162 gravado (500000 linhas)
0
500000
  Bloco 163 gravado (500000 linhas)
0
500000
  Bloco 164 gravado (500000 linhas)
0
500000
  Bloco 165 gravado (500000 linhas)
0
500000
  Bloco 166 gravado (500000 linhas)
0
500000
  Bloco 167 gravado (500000 linhas)
0
500000
  Bloco 168 gravado (500000 linhas)
0
500000
  Bloco 169 gravado (500000 linhas)
0
500000
  Bloco 170 gravado (500000 linhas)
0
500000
  Bloco 171 gravado (500000 linhas)
0
500000
  Bloco 172 gravado (500000 linhas)
0
500000
  Bloco 173 gravado (500000 linhas)
0
500000
  Bloco 174 gravado (500000 linhas)
0
500000
  Bloco 175 gravado (500000 linhas)
0
500000
  Bloco 176 gravado (500000 linhas)
0
359622
  Bloco 177 gravado (359622 linhas)
0
 'principals' concluído!
print("\n Todos os arquivos foram carregados. Conexão permanece aberta para consultas SQL.")

 Todos os arquivos foram carregados. Conexão permanece aberta para consultas SQL.
# Verificar se de fato todas as tabelas estão no banco de dados
import pandas as pd

tabelas = pd.read_sql_query("SELECT name FROM sqlite_master WHERE type='table';", con)
print(tabelas)
         name
0      basics
1     ratings
2  principals
  1. (Utilizando SQL, responda): Quais são os 5 filmes com as maiores notas (averageRating)? Apresente uma solução capaz de desempatar os filmes baseando-se no número de votos recebidos.
# Carregar bibliotecas
library(DBI)
Warning: pacote 'DBI' foi compilado no R versão 4.4.3
library(RSQLite)
Warning: pacote 'RSQLite' foi compilado no R versão 4.4.3
# Fazer conexão com o banco
con <- dbConnect(RSQLite::SQLite(), "movies.sqlite3")

# Seleciona quais colunas vamos ter na resposta (nome do filme, nota média e número de votos). Pega da tabela "basics" (b) e junta com rantings (r). Ordena por ordem decrescente a nota média e o número de votos. Por fim, seleciona os 5 primeiros.
query <- "
SELECT 
    b.primaryTitle,
    r.averageRating,
    r.numVotes
FROM basics AS b
JOIN ratings AS r
    ON b.tconst = r.tconst
WHERE b.titleType = 'movie'
  AND r.numVotes >= 100   -- filtro para eliminar filmes com poucos votos
ORDER BY r.averageRating DESC, r.numVotes DESC
LIMIT 5;


"

top5 <- dbGetQuery(con, query)
print(top5)
              primaryTitle averageRating numVotes
1                   Kaveri            10     1023
2                  Kurukku            10      451
3 Jedal Dar Omghe 30 Metri            10      142
4                Sargashte            10      134
5          Gorgeous Rascal            10      115
# Ver todas as tabelas disponíveis no banco SQLite
dbListTables(con)
[1] "basics"     "principals" "ratings"
  1. (Utilizando SQL, responda): Qual é o gênero mais frequente entre os filmes com nota maior que 8?
# Seleciona o gênero mais frequente entre filmes com nota média maior que 8, separa os generos (aparecem mais de um genero por filme), juntando com a tabela "ratings", agrupando por gênero, contando a frequência e retornando apenas o mais frequente.
query <- "
WITH RECURSIVE split_genres(tconst, genre, rest) AS (
    SELECT 
        tconst,
        TRIM(SUBSTR(genres, 1, INSTR(genres || ',', ',') - 1)) AS genre,
        SUBSTR(genres, INSTR(genres || ',', ',') + 1) AS rest
    FROM basics
    WHERE genres IS NOT NULL
    UNION ALL
    SELECT
        tconst,
        TRIM(SUBSTR(rest, 1, INSTR(rest || ',', ',') - 1)) AS genre,
        SUBSTR(rest, INSTR(rest || ',', ',') + 1) AS rest
    FROM split_genres
    WHERE rest <> ''
)
SELECT genre, COUNT(*) AS freq
FROM split_genres AS sg
JOIN ratings AS r ON sg.tconst = r.tconst
WHERE r.averageRating > 8
GROUP BY genre
ORDER BY freq DESC
LIMIT 1;
"

resultado <- dbGetQuery(con, query)

print(resultado)
  genre   freq
1 Drama 108356
  1. (Utilizando SQL, responda): Quais são os 3 atores/atrizes que mais participaram de filmes com nota maior que 7.5?
# Seleciona os três atores ou atrizes com mais filmes bem avaliados (nota maior que 7.5), juntando as tabelas "principals", "ratings" e "basics", agrupando por ator/atriz e categoria, contando a quantidade de filmes distintos e ordenando em ordem decrescente.
query <- "
SELECT 
    p.nconst AS id_pessoa,
    p.category AS categoria,
    COUNT(DISTINCT p.tconst) AS qtd_filmes
FROM principals AS p
JOIN ratings AS r ON p.tconst = r.tconst
JOIN basics AS b ON b.tconst = r.tconst
WHERE r.averageRating > 7.5
  AND p.category IN ('actor', 'actress')
GROUP BY p.nconst, p.category
ORDER BY qtd_filmes DESC
LIMIT 3
"

# Executa a consulta e exibe o resultado
top3 <- dbGetQuery(con, query)
top3$rank <- 1:nrow(top3)  
print(top3)
  id_pessoa categoria qtd_filmes rank
1 nm0048389     actor       1901    1
2 nm0217221   actress       1676    2
3 nm1477426   actress       1416    3
dbDisconnect(con) #desconectando