comparison db/create_db.Rmd @ 0:205974c9568c tip

Initial commit. Predictions not included for lack of space.

+---
+title: "Create Database"
+output: html_notebook
+---
+
+```{r}
+library(tidyverse)
+if(!require("RSQLite")){
+  install.packages("RSQLite")
+  library("RSQLite")
+}
+```
+
+```{r read_csv}
+filename <- '../metadata/GTZANindex.csv'
+data <- read.csv(filename, stringsAsFactors = F)
+```
+
+```{r fill_empty}
+data <- 
+  data %>%
+  mutate(
+    artist_list = ifelse(
+      artist_list == '',
+      paste0("Unknown Artist (", class, "_", ex_id_class, ")"), 
+      artist_list),
+    track_name = ifelse(
+      track_name == '',
+      paste0("Unknown Track (", class, "_", ex_id_class, ")"), 
+      track_name)    
+    )
+```
+
+```{r reconstruct_file}
+ext <- 'au'
+
+get_filename <- function(class, ex_id_class, ext = 'wav'){
+  paste0(class, '.',
+         ifelse(ex_id_class < 10, "0000", "000"),
+         ex_id_class, '.', ext)
+}
+```
+
+```{r create_rec_id}
+rep <- read.csv(file = 'rep.csv')
+
+data$rec_id <- 
+  sapply(1:max(data$ex_id),
+         function(x) ifelse(x %in% rep$ex_id, {
+           i <- rep[which(x == rep$ex_id), 'rep_id']
+           min(rep[which(rep$rep_id == i), 'ex_id'])},
+           x))
+```
+
+
+## Create Tables
+
+```{r excerpts}
+excerpts <- data %>% select(ex_id, rec_id)
+```
+```{r recordings}
+recordings <- data %>%
+  select(rec_id, track_name) %>%
+  rename(title = track_name) %>%
+  unique()
+```
+```{r artists}
+# Find and split individual artists from artist lists
+artist_list <- data %>% select(artist_list) %>% unlist()
+artists <- data.frame(artist_id = numeric(), 
+                      artist_name = character(),
+                      stringsAsFactors = F)
+recordings_artists <- data.frame(rec_id = numeric(), 
+                                 artist_id = numeric())
+
+for (i in 1:nrow(data)){
+  aux <- unname(
+    sapply(
+    unlist(strsplit(data[i, 'artist_list'], split = ';')), trimws))
+
+  for (j in 1:length(aux)){
+   if (any(aux[j] %in% artists$artist_name)){
+      artist_id <- as.numeric(
+        artists[artists$artist_name == aux[j], 'artist_id'])
+    }
+    else {
+      artist_id <- ifelse(nrow(artists) < 1, 1, 
+                          max(artists$artist_id) + 1)
+      artists <- 
+        rbind(artists,
+              data.frame(artist_id = artist_id, 
+                         artist_name = unname(aux[j]),
+                         stringsAsFactors = F))
+    }
+    recordings_artists <-
+      rbind(recordings_artists,
+            data.frame(rec_id = as.numeric(data[i, 'rec_id']),
+                       artist_id = artist_id))
+  }
+  
+}
+
+recordings_artists <- unique(recordings_artists)
+```
+
+```{r classes}
+class_names <- unique(data$class)
+classes <- data.frame(class_id = 1:length(class_names),
+                      class = class_names, stringsAsFactors = F)
+excerpts_classes <- data %>%
+  select(ex_id, class, ex_id_class) %>%
+  inner_join(classes, by = c('class')) %>% 
+    select(ex_id, class_id, ex_id_class)
+```
+
+
+
+```{r create_db}
+
+tables <- c(
+  "excerpts", "recordings", "artists", "recordings_artists",
+  "classes", "excerpts_classes"
+)
+
+con <- dbConnect(SQLite(), "gtzan.db")
+
+sapply(
+  X = tables, 
+  FUN = function(x) 
+    dbWriteTable(con, name = x,  value = get(x),  overwrite = T)
+)
+
+dbDisconnect(con)
+```
+