Assignment2

db_movies <- dbConnect(MySQL(), 
        user = 'data607', password = 'pass', host = 'localhost', dbname = 'Movies')

dbListTables(db_movies)

## [1] "Critics" "Movies"  "Ratings"

sql_movies <- "select m.title, m.release_date, c.name, r.rating from movies m join critics c on m.movieid = c.movieid join ratings r on r.ratingid = c.ratingid;"
movies <- dbGetQuery(db_movies, sql_movies)
colnames(movies)[3] <- "critic_name"
movies

##           title release_date critic_name    rating
## 1    Braveheart   1995-05-24       Emrah Excellent
## 2    Braveheart   1995-05-24      Connie Excellent
## 3    Braveheart   1995-05-24       Clara   Average
## 4    Braveheart   1995-05-24         Alp   Average
## 5    Braveheart   1995-05-24       Deniz Excellent
## 6    Braveheart   1995-05-24        Emre Excellent
## 7   Forest Gump   1994-07-06       Emrah Excellent
## 8   Forest Gump   1994-07-06      Connie      Good
## 9   Forest Gump   1994-07-06       Clara      Good
## 10  Forest Gump   1994-07-06         Alp Excellent
## 11  Forest Gump   1994-07-06       Deniz Excellent
## 12  Forest Gump   1994-07-06        Emre Excellent
## 13   Awakenings   1991-01-11       Emrah      Good
## 14   Awakenings   1991-01-11      Connie Excellent
## 15   Awakenings   1991-01-11       Clara   Average
## 16   Awakenings   1991-01-11         Alp       Bad
## 17   Awakenings   1991-01-11       Deniz Excellent
## 18   Awakenings   1991-01-11        Emre      Good
## 19       Matrix   1999-03-31       Emrah Excellent
## 20       Matrix   1999-03-31      Connie   Average
## 21       Matrix   1999-03-31       Clara      Good
## 22       Matrix   1999-03-31         Alp       Bad
## 23       Matrix   1999-03-31       Deniz       Bad
## 24       Matrix   1999-03-31        Emre Excellent
## 25      Memento   2001-05-25       Emrah Excellent
## 26      Memento   2001-05-25      Connie      Good
## 27      Memento   2001-05-25       Clara      Good
## 28      Memento   2001-05-25         Alp       Bad
## 29      Memento   2001-05-25       Deniz   Average
## 30      Memento   2001-05-25        Emre      Good
## 31 Interstellar   2014-11-07       Emrah       Bad
## 32 Interstellar   2014-11-07      Connie   Average
## 33 Interstellar   2014-11-07       Clara   Average
## 34 Interstellar   2014-11-07         Alp     Awful
## 35 Interstellar   2014-11-07       Deniz       Bad
## 36 Interstellar   2014-11-07        Emre Excellent

# same dataframe can also be read in R from the following github link:
# url <- "https://raw.githubusercontent.com/emrahakin1985/DATA607/master/datasets/movies.csv"
# movies <- subset(read.csv(url),,-1)

Pivot operation in R to pivot movie titles into columns so we have a column for each movie title and one row for each critic.

# using reshape function, cast
movies_reshaped <- cast(movies, critic_name ~ title, value = 'rating')

knitr::kable(movies_reshaped)

critic_name	Awakenings	Braveheart	Forest Gump	Interstellar	Matrix	Memento
Alp	Bad	Average	Excellent	Awful	Bad	Bad
Clara	Average	Average	Good	Average	Good	Good
Connie	Excellent	Excellent	Good	Average	Average	Good
Deniz	Excellent	Excellent	Excellent	Bad	Bad	Average
Emrah	Good	Excellent	Excellent	Bad	Excellent	Excellent
Emre	Good	Excellent	Excellent	Excellent	Excellent	Good

Arranging the order so that movies have the same alphabetical order (before extracting year information) as the reshaped data frame.

movies <- movies %>%
  arrange(title)

# using lubridate function, year
year_vector <- unique(year(movies$release_date))
year_vector

## [1] 1991 1995 1994 2014 1999 2001

Formatting vector before using it to change the column names on reshaped dataset

year_vector <- paste('(', year_vector, ')', sep = "")
year_vector

## [1] "(1991)" "(1995)" "(1994)" "(2014)" "(1999)" "(2001)"

Adding year information to the columns on movies_reshaped.

colnames(movies_reshaped)[2:7] <- paste (colnames(movies_reshaped)[2:7], year_vector, sep = " ")
movies_reshaped

##   critic_name Awakenings (1991) Braveheart (1995) Forest Gump (1994)
## 1         Alp               Bad           Average          Excellent
## 2       Clara           Average           Average               Good
## 3      Connie         Excellent         Excellent               Good
## 4       Deniz         Excellent         Excellent          Excellent
## 5       Emrah              Good         Excellent          Excellent
## 6        Emre              Good         Excellent          Excellent
##   Interstellar (2014) Matrix (1999) Memento (2001)
## 1               Awful           Bad            Bad
## 2             Average          Good           Good
## 3             Average       Average           Good
## 4                 Bad           Bad        Average
## 5                 Bad     Excellent      Excellent
## 6           Excellent     Excellent           Good

Better looking table with kable

knitr::kable(movies_reshaped)

critic_name	Awakenings (1991)	Braveheart (1995)	Forest Gump (1994)	Interstellar (2014)	Matrix (1999)	Memento (2001)
Alp	Bad	Average	Excellent	Awful	Bad	Bad
Clara	Average	Average	Good	Average	Good	Good
Connie	Excellent	Excellent	Good	Average	Average	Good
Deniz	Excellent	Excellent	Excellent	Bad	Bad	Average
Emrah	Good	Excellent	Excellent	Bad	Excellent	Excellent
Emre	Good	Excellent	Excellent	Excellent	Excellent	Good