Reading in the data file from the sql server:
ratingsdb = dbConnect(MySQL(), user="setec", password="toomanysecrets", host="localhost", dbname="movies")
#not my real password, this is a copy of the real R code with the login and password changed, the real R code
#is below and is executed with an "echo=FALSE" statement
fromdb<-dbGetQuery(ratingsdb, "SELECT * FROM ratings")
##ratings of each movie
fromdb
##quartiles and averages of each movie
summary(fromdb)
##putting the average rating into another data.frame to rank them
averatings<-lapply(fromdb[,3:7], FUN = function(x) mean(x))
### how people felt about each movie:
rank(averatings)
## how much does liking each movie correlate to liking other ones?
cor(fromdb[3:7])We can see that nothing correlates with having a positive view of ShapeOfWater, which is borne out in the fact no one except Wren really liked it
## FriendNumber Name BlackPanther ThorRagnarok InfinityWar CrazyRichAsians
## 1 1 Bryce 5 4 3 5
## 2 2 April 5 5 2 5
## 3 3 Jill 4 2 3 4
## 4 4 John 5 4 2 5
## 5 5 Alex 4 5 5 3
## 6 6 Wren 5 3 3 5
## ShapeOfWater
## 1 2
## 2 1
## 3 3
## 4 2
## 5 2
## 6 4## FriendNumber Name BlackPanther ThorRagnarok
## Min. :1.00 Length:6 Min. :4.000 Min. :2.000
## 1st Qu.:2.25 Class :character 1st Qu.:4.250 1st Qu.:3.250
## Median :3.50 Mode :character Median :5.000 Median :4.000
## Mean :3.50 Mean :4.667 Mean :3.833
## 3rd Qu.:4.75 3rd Qu.:5.000 3rd Qu.:4.750
## Max. :6.00 Max. :5.000 Max. :5.000
## InfinityWar CrazyRichAsians ShapeOfWater
## Min. :2.00 Min. :3.00 Min. :1.000
## 1st Qu.:2.25 1st Qu.:4.25 1st Qu.:2.000
## Median :3.00 Median :5.00 Median :2.000
## Mean :3.00 Mean :4.50 Mean :2.333
## 3rd Qu.:3.00 3rd Qu.:5.00 3rd Qu.:2.750
## Max. :5.00 Max. :5.00 Max. :4.000## BlackPanther ThorRagnarok InfinityWar CrazyRichAsians
## 5 3 2 4
## ShapeOfWater
## 1## BlackPanther ThorRagnarok InfinityWar CrazyRichAsians
## BlackPanther 1.0000000 0.2208631 -0.7071068 0.9258201
## ThorRagnarok 0.2208631 1.0000000 0.1561738 -0.1022397
## InfinityWar -0.7071068 0.1561738 1.0000000 -0.8728716
## CrazyRichAsians 0.9258201 -0.1022397 -0.8728716 1.0000000
## ShapeOfWater -0.1250000 -0.7730207 0.1767767 0.0000000
## ShapeOfWater
## BlackPanther -0.1250000
## ThorRagnarok -0.7730207
## InfinityWar 0.1767767
## CrazyRichAsians 0.0000000
## ShapeOfWater 1.0000000