Import Data from SQL to R
Alain T Kuiete
9/8/2019
SQL code
use movierating;
CREATE TABLE RatingMovies ( id INT PRIMARY KEY AUTO_INCREMENT, CriticNames VARCHAR(20) NOT NULL UNIQUE, GOOD_BOYS INT, ANGEL_HAS_FALLEN INT, THE_LION_KING INT, IT_CHAPTER_TWO INT, TOY_STORY_FOUR INT, AVENGER_ENDGAMES INT, BLACK_PANTER INT, FAST_AND_FURIOUS INT);
INSERT INTO RatingMovies (CriticNames, GOOD_BOYS, ANGEL_HAS_FALLEN, THE_LION_KING, IT_CHAPTER_TWO, TOY_STORY_FOUR, AVENGER_ENDGAMES, BLACK_PANTER, FAST_AND_FURIOUS) VALUES(‘Rotten’,4, 2, 3, 3, 5, 5, 5, 3),(‘Audience’, 4, 5, 4, 4, 5, 4, 4, 4), (‘Metric’, 3, 2, 3, 3, 4, 4, 4, 3), (‘Metacritic’, 3, 2, 3, 3, 3, 4, 4, 3), (‘Pascal’, 3, 3, 4, 3, 4, 4, 4, 3), (‘Dayshawn’, 4, 2, 3, 4, 5, 4, 5, 3), (‘Letoya’, 3, 2, 4, 3, 4, 4, 5, 3), (‘Amanda’, 3, 1, 2, 4, 4, 5, 5, 4), (‘Andy’, 4, 2, 3, 4, 4, 5, 5, 4), (‘Philip’, 3, 3, 1, 2, 4, 4, 4, 3), (‘Detroit’, 3, 3, 5, 3, 5, 4, 4, 3), (‘Elhaja’, 3, 4, 4, 3, 4, 5, 5, 4);
SELECT * FROM RatingMovies;
SELECT CriticNames as Critics, GOOD_BOYS, ANGEL_HAS_FALLEN, THE_LION_KING, IT_CHAPTER_TWO, TOY_STORY_FOUR, AVENGER_ENDGAMES, BLACK_PANTER, FAST_AND_FURIOUS INTO OUTFILE ‘C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/data/RatingMovies.csv’ FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘"’ ESCAPED BY ‘\’
FROM RatingMovies;
Reading cvs file
movierating <- read.csv('https://raw.githubusercontent.com/AlainKuiete/DATA607/master/RatingMovies.csv')Eploring the data
summary(movierating)## id CriticNames GOOD_BOYS ANGEL_HAS_FALLEN
## Min. : 1.00 Amanda :1 Min. :3.000 Min. :1.000
## 1st Qu.: 3.75 Andy :1 1st Qu.:3.000 1st Qu.:2.000
## Median : 6.50 Audience:1 Median :3.000 Median :2.000
## Mean : 6.50 Dayshawn:1 Mean :3.333 Mean :2.583
## 3rd Qu.: 9.25 Detroit :1 3rd Qu.:4.000 3rd Qu.:3.000
## Max. :12.00 Elhaja :1 Max. :4.000 Max. :5.000
## (Other) :6
## THE_LION_KING IT_CHAPTER_TWO TOY_STORY_FOUR AVENGER_ENDGAMES
## Min. :1.00 Min. :2.00 Min. :3.00 Min. :4.000
## 1st Qu.:3.00 1st Qu.:3.00 1st Qu.:4.00 1st Qu.:4.000
## Median :3.00 Median :3.00 Median :4.00 Median :4.000
## Mean :3.25 Mean :3.25 Mean :4.25 Mean :4.333
## 3rd Qu.:4.00 3rd Qu.:4.00 3rd Qu.:5.00 3rd Qu.:5.000
## Max. :5.00 Max. :4.00 Max. :5.00 Max. :5.000
##
## BLACK_PANTER FAST_AND_FURIOUS
## Min. :4.0 Min. :3.000
## 1st Qu.:4.0 1st Qu.:3.000
## Median :4.5 Median :3.000
## Mean :4.5 Mean :3.333
## 3rd Qu.:5.0 3rd Qu.:4.000
## Max. :5.0 Max. :4.000
## Data structure
str(movierating)## 'data.frame': 12 obs. of 10 variables:
## $ id : int 1 2 3 4 5 6 7 8 9 10 ...
## $ CriticNames : Factor w/ 12 levels "Amanda","Andy",..: 12 3 9 8 10 4 7 1 2 11 ...
## $ GOOD_BOYS : int 4 4 3 3 3 4 3 3 4 3 ...
## $ ANGEL_HAS_FALLEN: int 2 5 2 2 3 2 2 1 2 3 ...
## $ THE_LION_KING : int 3 4 3 3 4 3 4 2 3 1 ...
## $ IT_CHAPTER_TWO : int 3 4 3 3 3 4 3 4 4 2 ...
## $ TOY_STORY_FOUR : int 5 5 4 3 4 5 4 4 4 4 ...
## $ AVENGER_ENDGAMES: int 5 4 4 4 4 4 4 5 5 4 ...
## $ BLACK_PANTER : int 5 4 4 4 4 5 5 5 5 4 ...
## $ FAST_AND_FURIOUS: int 3 4 3 3 3 3 3 4 4 3 ...head(movierating)## id CriticNames GOOD_BOYS ANGEL_HAS_FALLEN THE_LION_KING IT_CHAPTER_TWO
## 1 1 Rotten 4 2 3 3
## 2 2 Audience 4 5 4 4
## 3 3 Metric 3 2 3 3
## 4 4 Metacritic 3 2 3 3
## 5 5 Pascal 3 3 4 3
## 6 6 Dayshawn 4 2 3 4
## TOY_STORY_FOUR AVENGER_ENDGAMES BLACK_PANTER FAST_AND_FURIOUS
## 1 5 5 5 3
## 2 5 4 4 4
## 3 4 4 4 3
## 4 3 4 4 3
## 5 4 4 4 3
## 6 5 4 5 3good boy and Black panter
library(ggplot2)
a <- ggplot(movierating,aes(x= GOOD_BOYS,y=BLACK_PANTER))
a + geom_boxplot()## Warning: Continuous x aesthetic -- did you forget aes(group=...)?