library(dplyr)
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
library(readr)

# Load the movies dataset
movies <- read_csv("https://gist.githubusercontent.com/tiangechen/b68782efa49a16edaf07dc2cdaa855ea/raw/0c794a9717f18b094eabab2cd6a6b9a226903577/movies.csv")
## Rows: 77 Columns: 8
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (4): Film, Genre, Lead Studio, Worldwide Gross
## dbl (4): Audience score %, Profitability, Rotten Tomatoes %, Year
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

Rename the “Film” column to “movie_title” and “Year” to “release_year”.

q1 <- movies %>%
  rename (movie_title=Film, release_year=Year)
(head(q1))
## # A tibble: 6 × 8
##   movie_title               Genre `Lead Studio` `Audience score %` Profitability
##   <chr>                     <chr> <chr>                      <dbl>         <dbl>
## 1 Zack and Miri Make a Por… Roma… The Weinstei…                 70          1.75
## 2 Youth in Revolt           Come… The Weinstei…                 52          1.09
## 3 You Will Meet a Tall Dar… Come… Independent                   35          1.21
## 4 When in Rome              Come… Disney                        44          0   
## 5 What Happens in Vegas     Come… Fox                           72          6.27
## 6 Water For Elephants       Drama 20th Century…                 72          3.08
## # ℹ 3 more variables: `Rotten Tomatoes %` <dbl>, `Worldwide Gross` <chr>,
## #   release_year <dbl>

Create a new dataframe with only the columns: movie_title, release_year, Genre, Profitability,

q2 <- movies %>%
  select("Film", "Year", "Genre", "Profitability")
head (q2)
## # A tibble: 6 × 4
##   Film                                Year Genre   Profitability
##   <chr>                              <dbl> <chr>           <dbl>
## 1 Zack and Miri Make a Porno          2008 Romance          1.75
## 2 Youth in Revolt                     2010 Comedy           1.09
## 3 You Will Meet a Tall Dark Stranger  2010 Comedy           1.21
## 4 When in Rome                        2010 Comedy           0   
## 5 What Happens in Vegas               2008 Comedy           6.27
## 6 Water For Elephants                 2011 Drama            3.08

Filter the dataset to include only movies released after 2000 with a Rotten Tomatoes % higher than 80.

q3 <- movies %>%
  filter(Year> 2000, `Rotten Tomatoes %` > 80)
head (q3)
## # A tibble: 6 × 8
##   Film  Genre `Lead Studio` `Audience score %` Profitability `Rotten Tomatoes %`
##   <chr> <chr> <chr>                      <dbl>         <dbl>               <dbl>
## 1 WALL… Anim… Disney                        89         2.90                   96
## 2 Wait… Roma… Independent                   67        11.1                    89
## 3 Tang… Anim… Disney                        88         1.37                   89
## 4 Rach… Drama Independent                   61         1.38                   85
## 5 My W… Drama The Weinstei…                 84         0.826                  83
## 6 Midn… Rome… Sony                          84         8.74                   93
## # ℹ 2 more variables: `Worldwide Gross` <chr>, Year <dbl>

Add a new column called “Profitability_millions” that converts the Profitability to millions of dollars.

q4 <- movies %>%
  mutate(Profitability_millions = Profitability / 1e6)
head (q4)
## # A tibble: 6 × 9
##   Film  Genre `Lead Studio` `Audience score %` Profitability `Rotten Tomatoes %`
##   <chr> <chr> <chr>                      <dbl>         <dbl>               <dbl>
## 1 Zack… Roma… The Weinstei…                 70          1.75                  64
## 2 Yout… Come… The Weinstei…                 52          1.09                  68
## 3 You … Come… Independent                   35          1.21                  43
## 4 When… Come… Disney                        44          0                     15
## 5 What… Come… Fox                           72          6.27                  28
## 6 Wate… Drama 20th Century…                 72          3.08                  60
## # ℹ 3 more variables: `Worldwide Gross` <chr>, Year <dbl>,
## #   Profitability_millions <dbl>

Sort the filtered dataset by Rotten Tomatoes % in descending order, and then by Profitability in descending order. five <- four %>% arrange(desc(Rotten Tomatoes %) , desc(Profitability_millions))

q5 <- movies %>%
  arrange(desc(`Rotten Tomatoes %`), desc(Profitability/1e6))
head (q5)
## # A tibble: 6 × 8
##   Film  Genre `Lead Studio` `Audience score %` Profitability `Rotten Tomatoes %`
##   <chr> <chr> <chr>                      <dbl>         <dbl>               <dbl>
## 1 WALL… Anim… Disney                        89          2.90                  96
## 2 Midn… Rome… Sony                          84          8.74                  93
## 3 Ench… Come… Disney                        80          4.01                  93
## 4 Knoc… Come… Universal                     83          6.64                  91
## 5 Wait… Roma… Independent                   67         11.1                   89
## 6 A Se… Drama Universal                     64          4.38                  89
## # ℹ 2 more variables: `Worldwide Gross` <chr>, Year <dbl>

Use the pipe operator (%>%) to chain these operations together, starting with the original dataset and ending with a final dataframe that incorporates all the above transformations.

q6 <- movies %>%
  filter(Year> 2000, 'Rotten Tomatoes%' > 80) %>%
  select(Film, Year, Genre, Profitability) %>%
mutate(Profitability_millions = Profitability / 1e6) %>%
arrange(desc('Rotten Tomatoes %'), desc(Profitability/1e6))
   head(q6)
## # A tibble: 6 × 5
##   Film                           Year Genre Profitability Profitability_millions
##   <chr>                         <dbl> <chr>         <dbl>                  <dbl>
## 1 Fireproof                      2008 Drama         66.9              0.0000669 
## 2 High School Musical 3: Senio…  2008 Come…         22.9              0.0000229 
## 3 The Twilight Saga: New Moon    2009 Drama         14.2              0.0000142 
## 4 Waitress                       2007 Roma…         11.1              0.0000111 
## 5 Twilight                       2008 Roma…         10.2              0.0000102 
## 6 Mamma Mia!                     2008 Come…          9.23             0.00000923

Create a summary dataframe that shows the average rating and Profitability_millions for movies by Genre. Hint: You’ll need to use group_by() and summarize().

q8 <- movies %>%
  mutate(
    Genre = toupper(Genre),  # Convert all genres to uppercase
    Genre = case_when(
      Genre %in% c("COMDY", "COMEDY") ~ "COMEDY",
      Genre %in% c("ROMENCE", "ROMANCE") ~ "ROMANCE",
      TRUE ~ Genre
    ),
    Profitability_millions = Profitability / 1e6  # Convert profitability to millions
  ) %>%
  group_by(Genre) %>%
  summarize(avg_rating = mean(`Rotten Tomatoes %`, na.rm = TRUE),
    avg_profitability_millions = mean(Profitability_millions, na.rm = TRUE))
  (q8)
## # A tibble: 6 × 3
##   Genre     avg_rating avg_profitability_millions
##   <chr>          <dbl>                      <dbl>
## 1 ACTION          11                   0.00000125
## 2 ANIMATION       74.2                 0.00000376
## 3 COMEDY          43.0                 0.00000385
## 4 DRAMA           51.5                 0.00000841
## 5 FANTASY         73                   0.00000178
## 6 ROMANCE         46.3                 0.00000408