|
f@0
|
1 library(tidyverse)
|
|
f@0
|
2 library(RSQLite)
|
|
f@0
|
3
|
|
f@0
|
4
|
|
f@0
|
5 #
|
|
f@0
|
6 get_excerpts <- function(db = "../db/gtzan.db"){
|
|
f@0
|
7
|
|
f@0
|
8 con <- dbConnect(SQLite(), db)
|
|
f@0
|
9
|
|
f@0
|
10 res <- dbGetQuery(
|
|
f@0
|
11 con, "SELECT ex_id FROM excerpts")
|
|
f@0
|
12
|
|
f@0
|
13 dbDisconnect(con)
|
|
f@0
|
14
|
|
f@0
|
15 return(res)
|
|
f@0
|
16
|
|
f@0
|
17 }
|
|
f@0
|
18
|
|
f@0
|
19 #
|
|
f@0
|
20 get_excerpts_artists <- function(db = "../db/gtzan.db"){
|
|
f@0
|
21
|
|
f@0
|
22 con <- dbConnect(RSQLite::SQLite(), db)
|
|
f@0
|
23
|
|
f@0
|
24 res <- dbGetQuery(
|
|
f@0
|
25 con,
|
|
f@0
|
26 "SELECT ex_id, artist_id FROM
|
|
f@0
|
27 recordings_artists AS r INNER JOIN excerpts AS e
|
|
f@0
|
28 ON r.rec_id = e.rec_id;")
|
|
f@0
|
29
|
|
f@0
|
30 dbDisconnect(con)
|
|
f@0
|
31
|
|
f@0
|
32 return(res)
|
|
f@0
|
33
|
|
f@0
|
34 }
|
|
f@0
|
35
|
|
f@0
|
36 .filter_by_class <- function(df,
|
|
f@0
|
37 db = "../db/gtzan.db", classes = NULL){
|
|
f@0
|
38
|
|
f@0
|
39 class_ids <- get_class_names(db = db) %>%
|
|
f@0
|
40 filter(class %in% classes) %>%
|
|
f@0
|
41 select(class_id) %>%
|
|
f@0
|
42 unlist()
|
|
f@0
|
43 df %>% filter(class_id %in% class_ids)
|
|
f@0
|
44
|
|
f@0
|
45 }
|
|
f@0
|
46
|
|
f@0
|
47 #
|
|
f@0
|
48 get_excerpts_classes <- function(db = "../db/gtzan.db",
|
|
f@0
|
49 classes = NULL){
|
|
f@0
|
50
|
|
f@0
|
51 con <- dbConnect(SQLite(), db)
|
|
f@0
|
52
|
|
f@0
|
53 res <- dbGetQuery(
|
|
f@0
|
54 con,
|
|
f@0
|
55 "SELECT ex_id, class_id, ex_id_class FROM
|
|
f@0
|
56 excerpts_classes;")
|
|
f@0
|
57
|
|
f@0
|
58 if (!is.null(classes))
|
|
f@0
|
59 res <- .filter_by_class(res, db, classes)
|
|
f@0
|
60
|
|
f@0
|
61 dbDisconnect(con)
|
|
f@0
|
62
|
|
f@0
|
63 return(res)
|
|
f@0
|
64
|
|
f@0
|
65 }
|
|
f@0
|
66
|
|
f@0
|
67 #
|
|
f@0
|
68 get_class_names <- function(db = "../db/gtzan.db"){
|
|
f@0
|
69
|
|
f@0
|
70 con <- dbConnect(SQLite(), db)
|
|
f@0
|
71
|
|
f@0
|
72 res <- dbGetQuery(
|
|
f@0
|
73 con,
|
|
f@0
|
74 "SELECT class_id, class FROM
|
|
f@0
|
75 classes;")
|
|
f@0
|
76
|
|
f@0
|
77 dbDisconnect(con)
|
|
f@0
|
78
|
|
f@0
|
79 return(res)
|
|
f@0
|
80
|
|
f@0
|
81 }
|
|
f@0
|
82
|
|
f@0
|
83 #
|
|
f@0
|
84 get_artists <- function(excerpts, unique_artists = T,
|
|
f@0
|
85 db = "../db/gtzan.db"){
|
|
f@0
|
86
|
|
f@0
|
87 excerpts_artists <- get_excerpts_artists(db = db)
|
|
f@0
|
88
|
|
f@0
|
89 res <-
|
|
f@0
|
90 excerpts_artists %>%
|
|
f@0
|
91 inner_join(data.frame(ex_id = excerpts), by = c('ex_id')) %>%
|
|
f@0
|
92 select(artist_id) %>%
|
|
f@0
|
93 unlist() %>%
|
|
f@0
|
94 unname()
|
|
f@0
|
95
|
|
f@0
|
96 if(unique_artists)
|
|
f@0
|
97 return(unique(res))
|
|
f@0
|
98 else
|
|
f@0
|
99 return(res)
|
|
f@0
|
100
|
|
f@0
|
101 }
|
|
f@0
|
102
|
|
f@0
|
103 #
|
|
f@0
|
104 get_classes <- function(excerpts, unique_classes = T,
|
|
f@0
|
105 use_names = T,
|
|
f@0
|
106 db = "../db/gtzan.db"){
|
|
f@0
|
107
|
|
f@0
|
108 excerpts_classes <- get_excerpts_classes(db = db)
|
|
f@0
|
109 class_names <- get_class_names(db = db)
|
|
f@0
|
110
|
|
f@0
|
111 res <-
|
|
f@0
|
112 excerpts_classes %>%
|
|
f@0
|
113 inner_join(data.frame(ex_id = excerpts), by = c('ex_id')) %>%
|
|
f@0
|
114 select(class_id)
|
|
f@0
|
115
|
|
f@0
|
116 if(use_names)
|
|
f@0
|
117 res <- res %>%
|
|
f@0
|
118 inner_join(class_names, by = c('class_id')) %>%
|
|
f@0
|
119 select(class) %>%
|
|
f@0
|
120 mutate(class = factor(class,
|
|
f@0
|
121 levels = unique(class_names$class)))
|
|
f@0
|
122 else
|
|
f@0
|
123 res <- res %>%
|
|
f@0
|
124 mutate(class_id = factor(class_id,
|
|
f@0
|
125 levels = unique(class_names$class_id)))
|
|
f@0
|
126
|
|
f@0
|
127 res <- res %>% unlist() %>% unname()
|
|
f@0
|
128
|
|
f@0
|
129 if(unique_classes)
|
|
f@0
|
130 return(unique(res))
|
|
f@0
|
131 else
|
|
f@0
|
132 return(res)
|
|
f@0
|
133
|
|
f@0
|
134 }
|
