Overview

This assignment explores moving data across multiple environments, specifically .csv files, MySQL, and R.

The assignment processes through the following steps:

  1. Obtain new Data
  2. Load new data into a MySQL database
  3. Join/transform the data in MySQL
  4. Export the final dataset from MySQL to a .csv file
  5. Load the .csv file to R

All files used for this assignment are stored at https://github.com/ChadRyanBailey/607-Week2-Assignment

0. Obtain new data

In this particular case:

  1. Choose six recent popular movies
  2. For each movie produce at least 5 records, each having a 1-5 rating
  3. Save this data in a .csv file “03 Movie Ratings.csv”

1. Load new data into a MySQL database

Use the MySQL script, “04 Load Movie Ratings.sql”, to load the .csv file into the MySQL database

2. Join/Transform the data in MySQL

No transformations are needed for this assignment

3. Export the final dataset from MySQL to a .csv file

Use the MySQL script, “05 Export Movie Ratings.sql”, to export the data to a new .csv file “06 Movie Ratings SQL Output.csv”

4. Load the .csv file to R

Load the Data:

fileLocation <- "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Data 607/Week2/06 Movie Ratings SQL Output.csv"
MovieRatings <- read.table(file = fileLocation, header = TRUE, sep = ",")

Review the loaded data:

head(MovieRatings)
##   RaterId RaterName                          Movie Rating
## 1       1   Darlene         Avengers: Infinity War      2
## 2       1   Darlene              The Incredibles 2      5
## 3       1   Darlene    Mission Impossible: Fallout      1
## 4       1   Darlene         Solo: A Starwars Story      5
## 5       1   Darlene Jurassic World: Fallen Kingdom      3
## 6       1   Darlene           Ant Man and the Wasp      5
summary(MovieRatings)
##     RaterId      RaterName                            Movie  
##  Min.   :1.0   Bryson :6   Ant Man and the Wasp          :6  
##  1st Qu.:2.0   Darlene:6   Avengers: Infinity War        :6  
##  Median :3.5   Janice :6   Jurassic World: Fallen Kingdom:6  
##  Mean   :3.5   Jeff   :6   Mission Impossible: Fallout   :6  
##  3rd Qu.:5.0   John   :6   Solo: A Starwars Story        :6  
##  Max.   :6.0   Ryan   :6   The Incredibles 2             :6  
##      Rating     
##  Min.   :1.000  
##  1st Qu.:2.000  
##  Median :3.000  
##  Mean   :3.222  
##  3rd Qu.:5.000  
##  Max.   :5.000