DATA 607 Week 2

library(tidyverse)
library(openintro)
library(DBI)
library(RMySQL)

TRANSFER DATA FROM SQL DATABASE TO R DATAFRAME

The following code is used to run a connection between the built table that was stored in the class SQL database and this R dataframe. Additionally I can confirm that the data is loaded in with the code in line 25

con <- dbConnect(RMySQL::MySQL(),
                 dbname = "gullit.navarrete26", 
                 host = "cuny607sql.mysql.database.azure.com", 
                 user = "gullit.navarrete26", 
                 password = "8f64d26864ec87e4", 
                 port = 3306)
dbListTables(con)

## [1] "movieratings"

RETRIVE THE DATA

After running the connection to see if the data has successfully loaded in, I will now demonstrate the printing/display of the table data that had been built back in MySQL using the following code:

table_data <- dbGetQuery(con, "SELECT * FROM movieratings;")
print(table_data)

##    ID            MovieName RaterName Rating
## 1   1  Joker: Folie à Deux      Matt      2
## 2   2  Joker: Folie à Deux     Jesus      1
## 3   3  Joker: Folie à Deux      Alex      2
## 4   4  Joker: Folie à Deux    Albert      1
## 5   5  Joker: Folie à Deux   Jeffery      1
## 6   6               Wicked      Matt      1
## 7   7               Wicked     Jesus      3
## 8   8               Wicked      Alex      5
## 9   9               Wicked    Albert      3
## 10 10               Wicked   Jeffery      4
## 11 11 Sonic the Hedgehog 3      Matt      5
## 12 12 Sonic the Hedgehog 3     Jesus      4
## 13 13 Sonic the Hedgehog 3      Alex      4
## 14 14 Sonic the Hedgehog 3    Albert      3
## 15 15 Sonic the Hedgehog 3   Jeffery      4
## 16 16        The Substance      Matt      4
## 17 17        The Substance     Jesus      5
## 18 18        The Substance      Alex      5
## 19 19        The Substance    Albert      1
## 20 20        The Substance   Jeffery      4
## 21 21              Moana 2      Matt      4
## 22 22              Moana 2     Jesus      5
## 23 23              Moana 2      Alex      2
## 24 24              Moana 2    Albert      1
## 25 25              Moana 2   Jeffery      2
## 26 26             Babygirl      Matt      3
## 27 27             Babygirl     Jesus      4
## 28 28             Babygirl      Alex      4
## 29 29             Babygirl    Albert      3
## 30 30             Babygirl   Jeffery      3

Missing data strategy (for R)

In the following example, and reminiscent of last week’s code, I use a code to remove all rows without any valid data or “NA.” This code is effective approach to implement because removing all void rows keeps the data overall concise and without any unnecessary data cluttering the display.

cleaned_data <- table_data %>%
  drop_na()

Missing data strategy (for SQL)

Additionally, here is code that would normally remove the “NA” invalid values, or in this case, “null” in SQL. Displayed is a common way to resolve this issue, similarly to the approach for R, involving removing the unnecessary null values/invalid data. More specifically and more realistically, from the Rating column.

DELETE FROM MovieRatings
WHERE Rating IS NULL;

…