DATA 607 Week 2 Homework

Movie Ratings

DB Quries

con <- dbConnect(RMySQL::MySQL(), dbname="movies", host="127.0.0.1", port=3306, user="root", password="n9ugx12#2@!")

dbListTables(con)

## [1] "movie"   "ratings"

con <- dbConnect(RMySQL::MySQL(), dbname="movies", host="127.0.0.1", port=3306, user="root", password="n9ugx12#2@!")

dbListTables(con)

## [1] "movie"   "ratings"

ratings <- c()

moviesQuery <-  fetch(dbSendQuery(con, "SELECT * FROM movie m
LEFT JOIN (SELECT ratedby, movieid, date, rating, `desc` FROM ratings r) r
ON m.id = r.movieid
order by m.id
  "))

## Warning in .local(conn, statement, ...): Decimal MySQL column 10 imported
## as numeric

 # necessary because the LEFT JOIN pulls the parents and children and make the parent appear duplicated based on the number of children
movies <-distinct(moviesQuery, id, title, summary, releasedate, poster, details, genre)

# for index table
mv <- data.frame(c(movies$title), c(movies$releasedate), c(movies$genre), c(movies$details))

Index

kable(mv,
col.names = linebreak(c("Movie", "Release Date", "Genre", "Details"), align = "c")) %>%
  kable_styling("striped", full_width = T) %>%
  column_spec(1:3, bold = T, color = "#000") %>%
  row_spec(1:6, bold = T, color = "#000")

Movie	Release Date	Genre	Details
Creed II	2018-11-21	Action, Drama, Thriller, Crime	Rating: PG-13; Runtime: 130 min
Bumblebee	2018-12-21	Action, Adventure, Sci-Fi	Rating: PG-13; Runtime: 114 min
Sicario: Day of the Soldado	2018-06-29	Action, Drama, Thriller, Crime	Rating: R ; Runtime: 122 min
Avengers: Infinity War	2018-04-27	Action, Adventure, Sci-Fi, Fantasy	Rating: PG-13; Runtime: 149 min
Spider-Man: Into the Spider-Verse	2018-12-14	Action, Adventure, Sci-Fi, Comedy, Animation, Family	Rating: PG; Runtime: 117 min
Black Panther	2018-02-16	Action, Adventure, Sci-Fi, Drama	Rating: PG-13; Runtime: 134 min

Creed II

## Title: 
## Creed II

## Release Date: 
## 2018-11-21

## Genre: 
## Action, Drama, Thriller, Crime

## Others: 
## Rating: PG-13; 
 Runtime: 130 min

## Summary: 
## Life has become a balancing act for Adonis Creed. Between personal obligations and training for his next big fight, he is up against the challenge of his life. Facing an opponent with ties to his family's past only intensifies his impending battle in the ring. Rocky Balboa is there by his side through it all and, together, Rocky and Adonis will confront their shared legacy, question what's worth fighting for, and discover that nothing's more important than family. Creed II is about going back to basics to rediscover what made you a champion in the first place, and remembering that, no matter where you go, you can't escape your history

Ratings

r <- moviesQuery %>% select(ratedby, movieid, date, rating, desc) %>% filter(movieid == movies[1,]$id)
names(r) <- c('ratedby', 'movieid', 'date', 'rating', 'desc')
if(count(r) > 0)
{
  for(i in 1: nrow(r))
  {
    o <- r[i,]
    cat("Rated by: ", o$ratedby, sep = '\n')
    cat("Date Rated: ", o$date, sep = '\n')
    cat("Rating: ", o$rating, sep = '\n')
    cat("Details: ", o$desc, sep = '\n')
  }
}

Bumblebee

## Title: 
## Bumblebee

## Release Date: 
## 2018-12-21

## Genre: 
## Action, Drama, Thriller, Crime

## Others: 
## Rating: PG-13;
 Runtime: 114 min

## Summary: 
## On the run in the year 1987, Bumblebee finds refuge in a junkyard in a small Californian beach town. Charlie (Hailee Steinfeld), on the cusp of turning 18 and trying to find her place in the world, discovers Bumblebee, battle-scarred and broken. When Charlie revives him, she quickly learns this is no ordinary, yellow VW bug

Ratings

r <- moviesQuery %>% select(ratedby, movieid, date, rating, desc) %>% filter(movieid == movies[2,]$id)
names(r) <- c('ratedby', 'movieid', 'date', 'rating', 'desc')
if(count(r) > 0)
{
  for(i in 1: nrow(r))
  {
    o <- r[i,]
    cat("Rated by: ", o$ratedby, sep = '\n')
    cat("Date Rated: ", o$date, sep = '\n')
    cat("Rating: ", o$rating, sep = '\n')
    cat("Details: ", o$desc, sep = '\n')
  }
}

Sicario

## Title: 
## Sicario: Day of the Soldado

## Release Date: 
## 2018-06-29

## Genre: 
## Action, Drama, Thriller, Crime

## Others: 
## Rating: R
; Runtime: 122 min

## Summary: 
## In the drug war, there are no rules – and as the cartels have begun trafficking terrorists across the US border, federal agent Matt Graver (Josh Brolin) calls on the mysterious Alejandro (Benicio Del Toro), whose family was murdered by a cartel kingpin, to escalate the war in nefarious ways. Alejandro kidnaps the kingpin’s daughter to inflame the conflict – but when the girl is seen as collateral damage, her fate will come between the two men as they question everything they are fighting for

Ratings

r <- moviesQuery %>% select(ratedby, movieid, date, rating, desc) %>% filter(movieid == movies[3,]$id)
names(r) <- c('ratedby', 'movieid', 'date', 'rating', 'desc')
if(count(r) > 0)
{
  for(i in 1: nrow(r))
  {
    o <- r[i,]
    dt <- data.frame(c("Rated by", "Date Rated", "Rating", "Details"), c(o$ratedby, o$date, o$rating, o$desc))
    print(kable(dt, col.names = NULL, format="html", padding_bottom = 0) %>%
      kable_styling(full_width = F) %>%
      column_spec(1, bold = T, width = "20em",  border_right = F, background = "#f5f5f5", color = "black") %>%
      column_spec(2, bold = F, width = "80em", background = "#f5f5f5", color = "black"))
    cat("\n")
  }
}

## <table class="table" style="width: auto !important; margin-left: auto; margin-right: auto;">
## <tbody>
##   <tr>
##    <td style="text-align:left;width: 20em; font-weight: bold;color: black;background-color: #f5f5f5;"> Rated by </td>
##    <td style="text-align:left;width: 80em; color: black;background-color: #f5f5f5;"> John rieber </td>
##   </tr>
##   <tr>
##    <td style="text-align:left;width: 20em; font-weight: bold;color: black;background-color: #f5f5f5;"> Date Rated </td>
##    <td style="text-align:left;width: 80em; color: black;background-color: #f5f5f5;"> 2018-07-07 00:00:00 </td>
##   </tr>
##   <tr>
##    <td style="text-align:left;width: 20em; font-weight: bold;color: black;background-color: #f5f5f5;"> Rating </td>
##    <td style="text-align:left;width: 80em; color: black;background-color: #f5f5f5;"> 4 </td>
##   </tr>
##   <tr>
##    <td style="text-align:left;width: 20em; font-weight: bold;color: black;background-color: #f5f5f5;"> Details </td>
##    <td style="text-align:left;width: 80em; color: black;background-color: #f5f5f5;"> This is a terrific followup to the first film, continuing to explore the drug and human trafficking at the border. Brolin and Del Toro are great, and it is in NO way slow or boring - a sharp script that really shows what is happening at the border. </td>
##   </tr>
## </tbody>
## </table>
## 
## <table class="table" style="width: auto !important; margin-left: auto; margin-right: auto;">
## <tbody>
##   <tr>
##    <td style="text-align:left;width: 20em; font-weight: bold;color: black;background-color: #f5f5f5;"> Rated by </td>
##    <td style="text-align:left;width: 80em; color: black;background-color: #f5f5f5;"> Metaflix </td>
##   </tr>
##   <tr>
##    <td style="text-align:left;width: 20em; font-weight: bold;color: black;background-color: #f5f5f5;"> Date Rated </td>
##    <td style="text-align:left;width: 80em; color: black;background-color: #f5f5f5;"> 2018-07-04 00:00:00 </td>
##   </tr>
##   <tr>
##    <td style="text-align:left;width: 20em; font-weight: bold;color: black;background-color: #f5f5f5;"> Rating </td>
##    <td style="text-align:left;width: 80em; color: black;background-color: #f5f5f5;"> 3 </td>
##   </tr>
##   <tr>
##    <td style="text-align:left;width: 20em; font-weight: bold;color: black;background-color: #f5f5f5;"> Details </td>
##    <td style="text-align:left;width: 80em; color: black;background-color: #f5f5f5;"> The first twenty minutes of 'Sicario 2' feels like it's produced by Breitbart for the specific purpose of serving as border gore porn for their ill informed readers.
## 
## The meandering storyline is a surprise given that the film was written by Taylor Sheridan, who wrote the first installment before following that up with 'Hell or High Water,' our #10 pick for best movies of 2016.
## 
## Both Benicio Del Toro and Josh Brolin are riveting to watch here, as they are in just about everything they do, but neither are able to deliver the pure magnetism that Emily Blunt brought to the original 'Sicario.'
## 
## Lastly, the complete non-ending in which the story is abruptly cut off for the sole purpose of setting up 'Sicario 3' is insulting to every last person who spent the time and money going to see a movie that presumably included a beginning, middle, and ending. Evidently Lionsgate wants to treat their audience like suckers by taking their wallets for another spin on this franchise, but they won't be getting any of mine. Thanks to their little cliffhanger stunt, 'Sicario 3' is getting the boycott treatment. </td>
##   </tr>
## </tbody>
## </table>
## 
## <table class="table" style="width: auto !important; margin-left: auto; margin-right: auto;">
## <tbody>
##   <tr>
##    <td style="text-align:left;width: 20em; font-weight: bold;color: black;background-color: #f5f5f5;"> Rated by </td>
##    <td style="text-align:left;width: 80em; color: black;background-color: #f5f5f5;"> Ginak </td>
##   </tr>
##   <tr>
##    <td style="text-align:left;width: 20em; font-weight: bold;color: black;background-color: #f5f5f5;"> Date Rated </td>
##    <td style="text-align:left;width: 80em; color: black;background-color: #f5f5f5;"> 2018-07-04 00:00:00 </td>
##   </tr>
##   <tr>
##    <td style="text-align:left;width: 20em; font-weight: bold;color: black;background-color: #f5f5f5;"> Rating </td>
##    <td style="text-align:left;width: 80em; color: black;background-color: #f5f5f5;"> 4 </td>
##   </tr>
##   <tr>
##    <td style="text-align:left;width: 20em; font-weight: bold;color: black;background-color: #f5f5f5;"> Details </td>
##    <td style="text-align:left;width: 80em; color: black;background-color: #f5f5f5;"> I enjoyed Benicio Del Toro’s soulful performance most of all, but the rest of the cast was also very strong. I was a bit bored during the setup at the beginning, and towards the end I was ready to scream every time the “here comes something dramatic” musical theme was pounded out ad nauseum. But generally, this was an entertaining film with strong performances that kept me interested, and the charisma of Isabela Moner as Isabela Reyes (the drug lord’s kidnapped daughter) made up a bit for the absence of Emily Blunt. If your other option is being outside on a miserably hot and humid summer day and you are not into “Won’t You Be My Neighbor?”, this is a good choice. </td>
##   </tr>
## </tbody>
## </table>
## 
## <table class="table" style="width: auto !important; margin-left: auto; margin-right: auto;">
## <tbody>
##   <tr>
##    <td style="text-align:left;width: 20em; font-weight: bold;color: black;background-color: #f5f5f5;"> Rated by </td>
##    <td style="text-align:left;width: 80em; color: black;background-color: #f5f5f5;"> Kirollos-Noah </td>
##   </tr>
##   <tr>
##    <td style="text-align:left;width: 20em; font-weight: bold;color: black;background-color: #f5f5f5;"> Date Rated </td>
##    <td style="text-align:left;width: 80em; color: black;background-color: #f5f5f5;"> 2018-11-29 00:00:00 </td>
##   </tr>
##   <tr>
##    <td style="text-align:left;width: 20em; font-weight: bold;color: black;background-color: #f5f5f5;"> Rating </td>
##    <td style="text-align:left;width: 80em; color: black;background-color: #f5f5f5;"> 4 </td>
##   </tr>
##   <tr>
##    <td style="text-align:left;width: 20em; font-weight: bold;color: black;background-color: #f5f5f5;"> Details </td>
##    <td style="text-align:left;width: 80em; color: black;background-color: #f5f5f5;"> Sicario: Day of the Soldado is directed by Stefano Sollima, written by Taylor Sheridan, starring Benicio Del Toro, Josh Brolin and Isabela Moner.
## 
## In Sicario: Day of the Soldado, the series begins a new chapter. In the drug war, there are no rules--and as the cartels have begun trafficking terrorists across the US border, federal agent Matt Graver (Josh Brolin) calls on the mysterious Alejandro (Benicio Del Toro), whose family was murdered by a cartel kingpin, to escalate the war in nefarious ways. Alejandro kidnaps the kingpin's daughter to inflame the conflict--but when the girl is seen as collateral damage, her fate will come between the two men as they question everything they are fighting for.
## 
## The sequel of Sicario offers the same dark theme of the first one, and here's the first flaw of this movie, it offers the same dark theme, with no hopeful scenes, just like the first one.. of course the story is changed, but for me, they just thought that the first did a great job with critics, audience and in the box office.
## 
## Nothing new or more than the first one as the performance of Benicio Del Toro.. Josh Brolin did a little pit more than the first one. Isabela Moner is bad, just like every movie she did, she was like reading the script during acting!
## 
## The story was great, but it's like some young assassins, getting trained in this movie. The movie sometimes felt like.. the creators trying to make more money of this sequel.
## 
## Also, some scenes were super-unnecessary, I think they did that to make the movie longer! I don't want to spoil anything to you, in case you didn't watch it. So for me, the movie was good, but it can't help, it's unnecessary.
## 
## It's not bad, but it's a let down to the fans of the first one.
## I'm gonna give Sicario: Day of the Soldado a B.
## 
## Alright, guys, thank you as always for reading my review, let me know if you like my reviews or not, and I welcome all the comments. </td>
##   </tr>
## </tbody>
## </table>
## 
## <table class="table" style="width: auto !important; margin-left: auto; margin-right: auto;">
## <tbody>
##   <tr>
##    <td style="text-align:left;width: 20em; font-weight: bold;color: black;background-color: #f5f5f5;"> Rated by </td>
##    <td style="text-align:left;width: 80em; color: black;background-color: #f5f5f5;"> compi24 </td>
##   </tr>
##   <tr>
##    <td style="text-align:left;width: 20em; font-weight: bold;color: black;background-color: #f5f5f5;"> Date Rated </td>
##    <td style="text-align:left;width: 80em; color: black;background-color: #f5f5f5;"> 2018-07-08 00:00:00 </td>
##   </tr>
##   <tr>
##    <td style="text-align:left;width: 20em; font-weight: bold;color: black;background-color: #f5f5f5;"> Rating </td>
##    <td style="text-align:left;width: 80em; color: black;background-color: #f5f5f5;"> 4 </td>
##   </tr>
##   <tr>
##    <td style="text-align:left;width: 20em; font-weight: bold;color: black;background-color: #f5f5f5;"> Details </td>
##    <td style="text-align:left;width: 80em; color: black;background-color: #f5f5f5;"> Well, color me completely and utterly shocked. This is a sequel that's actually better than it's predecessor -- a movie directed by one of my favorite directors working, I might add. Things are just so much more tighter now. There's no dead weight in the form of a protagonist that's relegated to just spectatorship (like Blunt's character from the first one). There's a clear and understandable goal this time around that everyone being can identify with. And the action and set pieces are more spread out throughout each act. I don't know what else to say, I simply enjoyed this movie more than the first </td>
##   </tr>
## </tbody>
## </table>
## 
## <table class="table" style="width: auto !important; margin-left: auto; margin-right: auto;">
## <tbody>
##   <tr>
##    <td style="text-align:left;width: 20em; font-weight: bold;color: black;background-color: #f5f5f5;"> Rated by </td>
##    <td style="text-align:left;width: 80em; color: black;background-color: #f5f5f5;"> Carlos Haz </td>
##   </tr>
##   <tr>
##    <td style="text-align:left;width: 20em; font-weight: bold;color: black;background-color: #f5f5f5;"> Date Rated </td>
##    <td style="text-align:left;width: 80em; color: black;background-color: #f5f5f5;"> 2018-06-29 00:00:00 </td>
##   </tr>
##   <tr>
##    <td style="text-align:left;width: 20em; font-weight: bold;color: black;background-color: #f5f5f5;"> Rating </td>
##    <td style="text-align:left;width: 80em; color: black;background-color: #f5f5f5;"> 5 </td>
##   </tr>
##   <tr>
##    <td style="text-align:left;width: 20em; font-weight: bold;color: black;background-color: #f5f5f5;"> Details </td>
##    <td style="text-align:left;width: 80em; color: black;background-color: #f5f5f5;"> Loved this film, I feel like most people thought that this was going to be just another mindless action movie. You have to actually pay attention and understand the message its trying to convey to the audience (us). Im glad it avoided all the typical Hollywood clichés. And the violence depicted in the film was very intense and had a purpose behind it. Josh Brolin steals the show here, his portrayal of Matt Graver was captivating; you will see far Matt Graver will go to complete mission. Del Toro gave a powerful performance as Alejandro as well. The ending is very bold but also very understanding when you consider the type of characters we are dealing with. </td>
##   </tr>
## </tbody>
## </table>

Infinity War

## Title: 
## Avengers: Infinity War

## Release Date: 
## 2018-04-27

## Genre: 
## Action, Drama, Thriller, Crime

## Others: 
## Rating: PG-13;
 Runtime: 149 min

## Summary: 
## As the Avengers and their allies have continued to protect the world from threats too large for any one hero to handle, a new danger has emerged from the cosmic shadows: Thanos. A despot of intergalactic infamy, his goal is to collect all six Infinity Stones, artifacts of unimaginable power, and use them to inflict his twisted will on all of reality. Everything the Avengers have fought for has led up to this moment – the fate of Earth and existence itself has never been more uncertain

Ratings

r <- moviesQuery %>% select(ratedby, movieid, date, rating, desc) %>% filter(movieid == movies[4,]$id)
names(r) <- c('ratedby', 'movieid', 'date', 'rating', 'desc')
if(count(r) > 0)
{
  for(i in 1: nrow(r))
  {
    o <- r[i,]
    cat("Rated by: ", o$ratedby, sep = '\n')
    cat("Date Rated: ", o$date, sep = '\n')
    cat("Rating: ", o$rating, sep = '\n')
    cat("Details: ", o$desc, sep = '\n')
  }
}

Into the Spider-Verse

## Title: 
## Spider-Man: Into the Spider-Verse

## Release Date: 
## 2018-12-14

## Genre: 
## Action, Drama, Thriller, Crime

## Others: 
## Rating: PG;
 Runtime: 117 min

## Summary: 
## Spider-Man: Into the Spider-Verse introduces Brooklyn teen Miles Morales, and the limitless possibilities of the Spider-Verse, where more than one can wear the mask

Ratings

r <- moviesQuery %>% select(ratedby, movieid, date, rating, desc) %>% filter(movieid == movies[5,]$id)
names(r) <- c('ratedby', 'movieid', 'date', 'rating', 'desc')
if(count(r) > 0)
{
  for(i in 1: nrow(r))
  {
    o <- r[i,]
    cat("Rated by: ", o$ratedby, sep = '\n')
    cat("Date Rated: ", o$date, sep = '\n')
    cat("Rating: ", o$rating, sep = '\n')
    cat("Details: ", o$desc, sep = '\n')
  }
}

Black Panther

## Title: 
## Black Panther

## Release Date: 
## 2018-02-16

## Genre: 
## Action, Drama, Thriller, Crime

## Others: 
## Rating: PG-13;
 Runtime: 134 min

## Summary: 
## After the events of Captain America: Civil War, King T’Challa returns home to the reclusive, technologically advanced African nation of Wakanda to serve as his country’s new leader. However, T’Challa soon finds that he is challenged for the throne from factions within his own country. When two foes conspire to destroy Wakanda, the hero known as Black Panther must team up with C.I.A. agent Everett K. Ross and members of the Dora Milaje, Wakanadan special forces, to prevent Wakanda from being dragged into a world war.

Ratings

r <- moviesQuery %>% select(ratedby, movieid, date, rating, desc) %>% filter(movieid == movies[6,]$id)
names(r) <- c('ratedby', 'movieid', 'date', 'rating', 'desc')
if(count(r) > 0)
{
  for(i in 1: nrow(r))
  {
    o <- r[i,]
    cat("Rated by: ", o$ratedby, sep = '\n')
    cat("Date Rated: ", o$date, sep = '\n')
    cat("Rating: ", o$rating, sep = '\n')
    cat("Details: ", o$desc, sep = '\n')
  }
}