Data 607 - Week 2 Assignment

Data Collection

All data was collected using a Survey Gizmo instrument located here.

For the purposes of this assignment 10 simulated responses were entered into the survey tool and exported to CSV.

Import data from CSV into MySQL

The following SQL script will import the CSV movie rating data into MySQL.

/*
  MovingRatings.sql
*/

DROP TABLE IF EXISTS MovieRatings;

CREATE TABLE MovieRatings 
(
  ResponseID INT NOT NULL,
  DateSubmitted DATE NOT NULL,
  FirstName VARCHAR(50) NULL,
  Gender VARCHAR(6) NULL,
  Age VARCHAR(50) NULL,
  MaritalStatus VARCHAR(50) NULL,
  Race_Ethnicity VARCHAR(50) NULL,
  FindingDory INT NULL,
  CaptainAmericaCivilWar INT NULL,
  JungleBook INT NULL,
  Deadpool INT NULL,
  SecretLifePets INT NULL,
  Zootopia INT NULL
);

SELECT * FROM MovieRatings ;

LOAD DATA LOCAL INFILE 'users/brandonohara/desktop/is607/Week 2 Assignment/SurveyExport.csv' 
INTO TABLE MovieRatings 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(ResponseID, DateSubmitted, FirstName, Gender, Age, MaritalStatus, Race_Ethnicity, @FindingDory, @CaptainAmericaCivilWar, @JungleBook, @Deadpool, @SecretLifePets, @Zootopia)
SET
FindingDory = nullif(@FindingDory,''),
CaptainAmericaCivilWar = nullif(@CaptainAmericaCivilWar,''),
JungleBook = nullif(@JungleBook,''),
Deadpool = nullif(@Deadpool,''),
SecretLifePets = nullif(@SecretLifePets,''),
Zootopia = nullif(@Zootopia,'')

;

SELECT * FROM MovieRatings
SELECT COUNT(*) FROM MovieRatings;

Import data into R

This link is helpful in utilizing the RMySQL package to load data into R.

Install and load necessary packages.

install.packages("RMySQL")
library(RMySQL)

Load data from MYSQL into data frame.

mydb = dbConnect(MySQL(), user='root', password='', dbname='bmo', host='localhost')

dbListTables(mydb)

MovieRatingsCSV = dbSendQuery(mydb, "select * from MovieRatings")

MovieRatings = fetch(MovieRatingsCSV)

Verify data imported as expected.

head(MovieRatings)