Download

I’ll download the following datasets:

• title.basics.tsv.gz
• title.ratings.tsv.gz
• title.crew.tsv.gz
• name.basics.tsv.gz

I’ll provide more information about them in the following sections.

# Create "data" folder if does not exist
if (!file.exists("data")) {
  dir.create("data")
}

# Download the files from IMDb if they haven't been downloaded
if (!file.exists("data/title.basics.tsv.gz")) {
download.file("https://datasets.imdbws.com/title.basics.tsv.gz",
              destfile = "data/title.basics.tsv.gz")
}

if (!file.exists("data/title.ratings.tsv.gz")) {
download.file("https://datasets.imdbws.com/title.ratings.tsv.gz",
              destfile = "data/title.ratings.tsv.gz")
}

if (!file.exists("data/title.crew.tsv.gz")) {
download.file("https://datasets.imdbws.com/title.crew.tsv.gz",
              destfile = "data/title.crew.tsv.gz")
}

if (!file.exists("data/name.basics.tsv.gz")) {
download.file("https://datasets.imdbws.com/name.basics.tsv.gz",
              destfile = "data/name.basics.tsv.gz")
}

Description, Load and Inspection

In this section I’ll describe what variables are included in each dataset, load them into R and take a look at their first few rows.

I created the following function to set the column width of a variable.

# Function to set kable column width
col_width <- function(kable_input, data, col, width_min) {
  column_spec(kable_input = kable_input, 
              column = which( colnames(data) == enexpr(col) ), 
              width_min = width_min)
}

# Load title.basics
title.basics <- gzfile("data/title.basics.tsv.gz") %>%
      read_delim(
            "\t", 
            escape_double = FALSE, 
            na = "\\N", 
            trim_ws = TRUE, 
            quote='',
            col_types = cols(
                  tconst = col_character(), 
                  titleType = col_character(),
                  primaryTitle = col_character(),
                  originalTitle = col_character(),
                  isAdult = col_logical(),
                  startYear = col_integer(),
                  endYear = col_integer(),                 
                  runtimeMinutes = col_integer(), 
                  genres = col_character()
                  )
            )

title.basics %>%
  head %>%
  kable(format = "html", table.attr = "style = \"color: white;\"") %>%
  col_width(title.basics, tconst, "3em") %>%
  col_width(title.basics, titleType, "3em") %>%
  col_width(title.basics, primaryTitle, "18em") %>%
  col_width(title.basics, originalTitle, "18em") %>%
  col_width(title.basics, isAdult, "3em") %>%
  col_width(title.basics, startYear, "3em") %>%
  col_width(title.basics, endYear, "3em") %>%
  col_width(title.basics, runtimeMinutes, "4em") %>%
  col_width(title.basics, genres, "5em")

# Load title.ratings
title.ratings <- gzfile("data/title.ratings.tsv.gz") %>%
      read_delim("\t", 
                 escape_double = FALSE, 
                 na = "\\N", 
                 trim_ws = TRUE, 
                 quote='',
                 col_types = cols(
                       tconst = col_character(), 
                       averageRating = col_double(),
                       numVotes = col_integer()
                       )
                 )

title.ratings %>%
  head %>%
  kable(format = "html", table.attr = "style = \"color: white;\"") %>%
  col_width(title.ratings, tconst, "3em") %>%
  col_width(title.ratings, averageRating, "3em") %>%
  col_width(title.ratings, numVotes, "3em")

# Load title.crew, skipping writers column
title.crew <- gzfile("data/title.crew.tsv.gz") %>%
      read_delim("\t", 
              escape_double = FALSE, 
              na = "\\N", 
              trim_ws = TRUE, 
              quote='',
              col_types = cols(
                 tconst = col_character(),
                 directors = col_character(),
                 writers = col_skip()
                 )
              )

title.crew %>%
  head(8) %>%
  kable(format = "html", table.attr = "style = \"color: white;\"") %>%
  col_width(title.crew, directors, "14em")

# Load name.basics
name.basics <- gzfile("data/name.basics.tsv.gz") %>%
      read_delim("\t", 
              escape_double = FALSE, 
              na = "\\N", 
              trim_ws = TRUE, 
              quote='',
              col_types = cols(
                 nconst = col_character(),
                 primaryName = col_character(),
                 birthYear = col_skip(),
                 deathYear = col_skip(),
                 primaryProfession = col_skip(),
                 knownForTitles = col_skip()
                 )
              )

name.basics %>%
  head %>%
  kable(format = "html", table.attr = "style = \"color: white;\"") %>%
  col_width(name.basics, nconst, "3em") %>%
  col_width(name.basics, primaryName, "10em")

Details

This is what the code does:

• Begin with title.basics dataset.
• Filter titles with:
  • titleType == "movie",
  • startYear >= 1970
  • runtimeMinutes >= 45 & runtimeMinutes <= 210.
• Add ratings information from title.ratings.
• Delete duplicate titles.
• Filter titles with:
  • averageRating >= 4,
  • numVotes >= 2500
• Create linkTitle column.

If you want to change any of the parameters I defined in the filters, you can do it!

# Creation of the movies dataset
movies <- title.basics %>%
  
  # Apply filters
      filter(
            titleType == "movie",
            startYear >= 1970,
            runtimeMinutes >= 45 & runtimeMinutes <= 210
            ) %>%
  
  # Add ratings and votes
  left_join(title.ratings, by = "tconst") %>%
  
  # Remove duplicates
      distinct(tconst, .keep_all = TRUE) %>%
  
  # Apply filters
      filter(
            averageRating >= 4,
            numVotes >= 2500
      ) %>%
  # Create linkTitle
   mutate(
     linkTitle = str_c(
       "<a href='https://www.imdb.com/title/", tconst, "/'>", originalTitle,"</a>")
     )

Let’s take a look at our dataset so far.

movies %>%
  head %>%
  kable(format = "html", table.attr = "style = \"color: white;\"") %>%
  col_width(movies, tconst, "3em") %>%
  col_width(movies, titleType, "3em") %>%
  col_width(movies, primaryTitle, "18em") %>%
  col_width(movies, originalTitle, "18em") %>%
  col_width(movies, isAdult, "3em") %>%
  col_width(movies, startYear, "3em") %>%
  col_width(movies, endYear, "3em") %>%
  col_width(movies, runtimeMinutes, "4em") %>%
  col_width(movies, genres, "5em") %>%
  col_width(movies, averageRating, "3em") %>%
  col_width(movies, numVotes, "3em") %>%
  col_width(movies, linkTitle, "45em")

To finish Part I, I’ll add directors information to our movies dataset.

This would have been pretty straightforward if title.crew had directors’ names instead of codes. As that’s not the case, some preprocessing is going to be necessary. Also, the fact that when a title has more than one director they are comma-separated in the same cell adds certain complexity to the task of replacing codes with names. But be not afraid, because the tidyverse is going to help us with this task!

The following code applies the desired change to title.crew.

Details

This is what the code does:

• Begin with title.crew dataset.
• Keep crew that corresponds to titles in movies to speed up calculations
• Go from a dataset where we have one row per title with comma-separated directors codes, to a dataset where we have one row per director code, indicating the title they directed. This step creates a longer dataset which will have duplicate tconst values for titles with more than one director.
• Add the name by directors’ code.
• Return to a dataset with one row per title and comma-separated named directors.

title.crew <- title.crew %>%
  
  # Keep crew from titles in movies
   semi_join(movies, "tconst") %>%
  
  # One row for each director
   separate_rows(directors, sep = ",") %>%
  
  # Add name
   left_join(name.basics, by = c("directors" = "nconst")) %>%
  
  # Return to semicolon separated values
   group_by(tconst) %>%
   summarise(directors = str_c(primaryName, collapse = ";")) %>%
   ungroup()

## `summarise()` ungrouping output (override with `.groups` argument)

Let’s see how title.crew looks like now.

title.crew %>%
  head(8) %>%
  kable(format = "html", table.attr = "style = \"color: white;\"") %>%
  col_width(title.crew, directors, "18em")

Now we can add this to movies.

# Add the column to movies
movies <- movies %>%
   left_join(title.crew, by = "tconst")

This is how movies looks like in the end of Part I.

movies %>%
  head %>%
  kable(format = "html", table.attr = "style = \"color: white;\"") %>%
  col_width(movies, tconst, "3em") %>%
  col_width(movies, titleType, "3em") %>%
  col_width(movies, primaryTitle, "18em") %>%
  col_width(movies, originalTitle, "18em") %>%
  col_width(movies, isAdult, "3em") %>%
  col_width(movies, startYear, "3em") %>%
  col_width(movies, endYear, "3em") %>%
  col_width(movies, runtimeMinutes, "4em") %>%
  col_width(movies, genres, "5em") %>%
  col_width(movies, averageRating, "3em") %>%
  col_width(movies, numVotes, "3em") %>%
  col_width(movies, linkTitle, "45em") %>%
  col_width(movies, directors, "18em")

Web scraping IMDb

If we take a look at any movie website in IMDb (for example, this one), we see that there is a lot more information related to that movie. I’m particularly interested in the following:

• Metascore
• The place or places where the production companies for that title are based, and therefore where the financing originated (Country)
• Estimated production cost of the movie (Budget)
• How much the movie took at the box office in the USA in its first weekend of release (Opening Weekend USA)
• How much the movie made at the box office in USA (Gross USA)
• How much the movie made at the box office all over the world, including the USA (Worldwide Gross)
• Awards information
• Full cast

How can we add that information to our dataset? Now is when web scraping joins the party. I’ll use the rvest package to extract the information mentioned above for each movie (when available). Also, as we have to do this for 16191 movies, I’ll be using the parallel package to speed up the work. Even with this approach, the code took around one hour and a half to run.

The following code creates an object named scraping that contains the information we want for each movie. Some preprocessing is going to be necessary before adding new columns to movies, but I’ll talk about it later.

# Web scraping more information about movies

# Set the cluster to work in parallel
n_cores <- detectCores() - 1
clust <- makeCluster(n_cores)

# Load packages on each cluster
# Obs: library() returns a character vector of all the attached packages, but
# it does so invisibly. When executed via clusterEvalQ, it isn't invisible.
# To make it "invisible" again, I assigned the result of clusterEvalQ to junk. 

junk <- clusterEvalQ(clust, {
  library(dplyr)
  library(stringr)
  library(rvest)
})

# Create scraping object by running a parallel apply
scraping <- parLapply(clust, movies$tconst, function(x){
  
  # Read title page
  title_page <- read_html(str_c("https://www.imdb.com/title/", x)) 
  
  # Scrape metascore
  scrap_metascore <- title_page %>%
    html_nodes(".metacriticScore span") %>%
    html_text
  
  # Scrape box office (and country)
  scrap_boxOffice <- title_page %>%
    html_nodes(".txt-block") %>%
    html_text
  
  # Scrape awards
  scrap_awards <- title_page %>%
    html_nodes(".awards-blurb b") %>%
    html_text
  
  
  # Read cast page
  cast_page <- read_html(
    str_c("https://www.imdb.com/title/", x, "/fullcredits?ref_=tt_cl_sm#cast")
    )
  
  # Scrape cast
  scrap_cast <- cast_page %>%
    html_nodes(".primary_photo+ td a") %>%
    html_text %>%
    str_c(collapse = ";") %>%
    str_replace_all("\\n", "")
  
  # Output for each movie
  my_list <- list("scrap_cast" = scrap_cast,
                  "scrap_metascore" = scrap_metascore,
                  "scrap_boxOffice" = scrap_boxOffice,
                  "scrap_awards" = scrap_awards)
  return(my_list)
})

# Stop the cluster
stopCluster(clust)

Vector Assignment

The following code uses the scraping object to create the vectors of data that will be used to add columns to movies.

# Number of movies
n_movies <- nrow(movies)

# Preallocate vectors
scrap_cast <- vector("character", length = n_movies)

scrap_metascore <- vector("character", length = n_movies)

scrap_country <- vector("character", length = n_movies)
scrap_budget <- vector("character", length = n_movies)
scrap_open_usa <- vector("character", length = n_movies)
scrap_gross_usa <- vector("character", length = n_movies)
scrap_gross_world <- vector("character", length = n_movies)

scrap_awards <- vector("character", length = n_movies)

# Function to fix length zero problem
no_lenght_zero <- function(x){
  ifelse(identical(x, character(0)), NA_character_, x)
}

# Assign extracted info
for (i in seq_along(scraping)){
  
  scrap_cast[[i]] <- scraping[[i]][["scrap_cast"]] %>%
    no_lenght_zero
  
  scrap_metascore[[i]] <- scraping[[i]][["scrap_metascore"]] %>%
    no_lenght_zero
  
  boxOffice_list <- scraping[[i]][["scrap_boxOffice"]]
  
  scrap_country[[i]] <- boxOffice_list %>%
    keep(~ str_detect(.x, "Country:")) %>%
    no_lenght_zero
  scrap_budget[[i]] <- boxOffice_list %>%
    keep(~ str_detect(.x, "Budget:")) %>%
    no_lenght_zero
  scrap_open_usa[[i]] <- boxOffice_list %>%
    keep(~ str_detect(.x, "Opening Weekend USA:")) %>%
    no_lenght_zero
  scrap_gross_usa[[i]] <- boxOffice_list %>%
    keep(~ str_detect(.x, "Gross USA:")) %>%
    no_lenght_zero
  scrap_gross_world[[i]] <- boxOffice_list %>%
    keep(~ str_detect(.x, "Cumulative Worldwide Gross:")) %>%
    no_lenght_zero
  
  scrap_awards[[i]] <- scraping[[i]][["scrap_awards"]] %>%
    no_lenght_zero
}

Add columns

Finally, we can use mutate to add the new columns to movies.

# Add scraped columns to data
movies <- movies %>%
  mutate(cast = scrap_cast,
         metascore_raw = scrap_metascore,
         country_raw = scrap_country,
         budget_raw = scrap_budget,
         open_usa_raw = scrap_open_usa,
         gross_usa_raw = scrap_gross_usa,
         gross_world_raw = scrap_gross_world,
         awards_raw = scrap_awards)

This is how movies looks like in the end of Part II.

Obs: I have taken the first 30 characters of cast and replace the rest with ... to make the table look better.

movies %>%
  head %>%
  mutate(cast = cast %>%
           str_sub(1, 30) %>%
           str_c("...")) %>%
  kable(format = "html", table.attr = "style = \"color: white;\"") %>%
  col_width(movies, tconst, "3em") %>%
  col_width(movies, titleType, "3em") %>%
  col_width(movies, primaryTitle, "18em") %>%
  col_width(movies, originalTitle, "18em") %>%
  col_width(movies, isAdult, "3em") %>%
  col_width(movies, startYear, "3em") %>%
  col_width(movies, endYear, "3em") %>%
  col_width(movies, runtimeMinutes, "4em") %>%
  col_width(movies, genres, "5em") %>%
  col_width(movies, averageRating, "3em") %>%
  col_width(movies, numVotes, "3em") %>%
  col_width(movies, linkTitle, "45em") %>%
  col_width(movies, directors, "18em") %>%
  col_width(movies, cast, "18em") %>%
  col_width(movies, metascore_raw, "3em") %>%
  col_width(movies, country_raw, "20em") %>%
  col_width(movies, budget_raw, "18em") %>%
  col_width(movies, open_usa_raw, "28em") %>%
  col_width(movies, gross_usa_raw, "18em") %>%
  col_width(movies, gross_world_raw, "25em") %>%
  col_width(movies, awards_raw, "18em")

Metascore

metascore_raw is a character column. I used parse_number to create metascore, which is numeric.

# metascore to number
movies <- movies %>%
  mutate(metascore = metascore_raw %>%
           parse_number)

Country

country_raw needs some cleaning that can be done with stringr::str_replace_all function.

movies <- movies %>%
  mutate(country = country_raw %>%
           str_replace_all("Country:", "") %>%
           str_replace_all("\\n", "") %>%
           str_replace_all(" ", "") %>%
           str_replace_all("\\|", ";")
)

Box Office Information

By taking a fast look at this “money” variables, it seemed that the currency was dollars ($). Just to be sure, I created the function check_currency with the following logic: if the sum of values where I find the dollar sign ($) and NA values of a column is equal to the total number of rows, then all currencies are the same (and dollars).

check_currency <- function(data, col) {
  n_movies <- nrow(data)
  missing <- data %>%
    pull(enexpr(col)) %>%
    is.na %>%
    sum
  
  dollar_sign <- data %>%
    pull(enexpr(col)) %>%
    str_detect("\\$") %>%
    sum(na.rm = TRUE)
  
  identical(missing + dollar_sign, n_movies)
}

After applying this function to the corresponding columns, we see that not all currencies are the same. Later I checked the dataset and saw in fact that this was the case.

check_currency(movies, budget_raw)

## [1] FALSE

check_currency(movies, open_usa_raw)

## [1] TRUE

check_currency(movies, gross_usa_raw)

## [1] FALSE

check_currency(movies, gross_world_raw)

## [1] FALSE

I decided to keep only those values express in dollars, assigning NA otherwise. For that matter, I created the keep_dollar function.

keep_dollar <- function(col) {
  ifelse(str_detect(col, "\\$"), col, NA_real_)
}

Now we can create our new variables, through parse_number. This function works just fine, even with columns where there were more numbers present (for example, take a look at open_usa_raw and open_usa)

movies <- movies %>%
  mutate(budget = keep_dollar(budget_raw) %>%
           parse_number,
         open_usa = keep_dollar(open_usa_raw) %>%
           parse_number,
         gross_usa = keep_dollar(gross_usa_raw) %>%
           parse_number,
         gross_world = keep_dollar(gross_world_raw) %>%
           parse_number)

Awards information

Using the awards_raw column I created a few variables.

For every movie:

• won_oscar, an indicator variable that takes the value 1 if the movie won any Oscar and 0 otherwise
• oscars_won, the number of Oscars won by a movie

For movies that didn’t win an Oscar:

• nominated_oscar, an indicator variable that takes the value 1 if the movie was nominated for any Oscar and 0 otherwise
• oscars_nominations, the number of Oscar nominations

Obs: If a movie won at least one Oscar, we don’t know how many nominations it had. This has to do with the way I scraped this data.

movies <- movies %>%
  mutate(
    
    won_oscar = ifelse(
      test = str_detect(awards_raw, "Won") & 
        str_detect(awards_raw, "Oscar") &
        !is.na(awards_raw), 
      yes = 1, 
      no = 0
      ),
    
    oscars_won = ifelse(
      test = won_oscar == 1, 
      yes = parse_number(awards_raw),
      no = 0
    ),
    
    nominated_oscar = ifelse(
      test = str_detect(awards_raw, "Nominated") & 
        str_detect(awards_raw, "Oscar") &
        !is.na(awards_raw), 
      yes = 1, 
      no = 0
    ),
    
    oscars_nominations = ifelse(
      test = nominated_oscar == 1, 
      yes = parse_number(awards_raw),
      no = 0
    )
)

The final dataset looks like this. I kept the raw columns for you to see the differences with the cleaned ones.

Obs: I have taken the first 30 characters of cast and replace the rest with ... to make the table look better.

movies %>%
  head %>%
  mutate(cast = cast %>%
           str_sub(1, 30) %>%
           str_c("...")) %>%
  kable(format = "html", table.attr = "style = \"color: white;\"") %>%
  col_width(movies, tconst, "3em") %>%
  col_width(movies, titleType, "3em") %>%
  col_width(movies, primaryTitle, "18em") %>%
  col_width(movies, originalTitle, "18em") %>%
  col_width(movies, isAdult, "3em") %>%
  col_width(movies, startYear, "3em") %>%
  col_width(movies, endYear, "3em") %>%
  col_width(movies, runtimeMinutes, "4em") %>%
  col_width(movies, genres, "5em") %>%
  col_width(movies, averageRating, "3em") %>%
  col_width(movies, numVotes, "3em") %>%
  col_width(movies, linkTitle, "45em") %>%
  col_width(movies, directors, "18em") %>%
  col_width(movies, cast, "18em") %>%
  col_width(movies, metascore_raw, "3em") %>%
  col_width(movies, country_raw, "20em") %>%
  col_width(movies, budget_raw, "18em") %>%
  col_width(movies, open_usa_raw, "28em") %>%
  col_width(movies, gross_usa_raw, "18em") %>%
  col_width(movies, gross_world_raw, "25em") %>%
  col_width(movies, awards_raw, "18em") %>%
  col_width(movies, metascore, "3em") %>%
  col_width(movies, country, "9em") %>%
  col_width(movies, budget, "7em") %>%
  col_width(movies, open_usa, "7em") %>%
  col_width(movies, gross_usa, "7em") %>%
  col_width(movies, gross_world, "7em") %>%
  col_width(movies, won_oscar, "10em") %>%
  col_width(movies, oscars_won, "10em") %>%
  col_width(movies, nominated_oscar, "10em") %>%
  col_width(movies, oscars_nominations, "10em")