Data Transformation for the MovieLens Dataset¶
Kevin Kam Fung YUEN
This notebook details the data cleaning, preprocessing, and feature engineering pipeline for the MovieLens dataset ( movies.csv and ratings.csv). It cleans raw metadata by extracting release years from movie titles, one-hot encoding 18 distinct genres, and filtering out invalid or incomplete records. Simultaneously, it aggregates over 24 million user ratings to derive key statistical metrics, including rating distributions and temporal features such as movie age and rating duration periods. Finally, the transformed datasets are merged, screened for temporal anomalies, and exported as a standardized dataset for downstream analysis and machine learning tasks.
Cleaning and Transforming movies.csv¶
suppressPackageStartupMessages({
library(dplyr)
library(stringr)
library(ggplot2)
library(cpcHCAD) # Created by KKF YUEN
})
output_dir <- "outputData"
Importing the movies.csv Dataset¶
moviesDS = read.csv("ml-25m/movies.csv",
colClasses = c("integer","character","character"),
sep = ",",
stringsAsFactors = FALSE)
cpcHCAD::headTail(moviesDS,show_rownames = TRUE)
| movieId | title | genres | |
|---|---|---|---|
| <int> | <chr> | <chr> | |
| 1 | 1 | Toy Story (1995) | Adventure|Animation|Children|Comedy|Fantasy |
| 2 | 2 | Jumanji (1995) | Adventure|Children|Fantasy |
| 3 | 3 | Grumpier Old Men (1995) | Comedy|Romance |
| 62421 | 209163 | Bad Poems (2018) | Comedy|Drama |
| 62422 | 209169 | A Girl Thing (2001) | (no genres listed) |
| 62423 | 209171 | Women of Devil's Island (1962) | Action|Adventure|Drama |
dim(moviesDS)
- 62423
- 3
Checking and Handling Missing Data¶
cpcHCAD::naSummary(moviesDS)
| Feature | NA_Count | NA_Percent |
|---|---|---|
| <chr> | <dbl> | <dbl> |
| movieId | 0 | 0 |
| title | 0 | 0 |
| genres | 0 | 0 |
anyNA(moviesDS)
movies = na.omit(moviesDS)
dim(movies)
- 62423
- 3
Conclusion: There are no missing values in the raw data.
Extracting the (YYYY) Pattern from Movie Titles¶
Extract the section containing the year¶
The release year pattern appears either inside parentheses or at the end of the movie title.
movies$year <- as.numeric(str_extract(movies$title, "\\d{4}(?=[^\\d]*$)"))
Verifying Missing Values¶
cpcHCAD::naSummary(movies$year)
| Feature | NA_Count | NA_Percent |
|---|---|---|
| <chr> | <dbl> | <dbl> |
| movies$year | 406 | 0.65 |
Given a missing data rate of only 0.65%, these records were removed entirely rather than applying imputation techniques or attempting to fix the source data.
naIndices = is.na(movies$year)
sum(naIndices)
The count of 406 missing years aligns with the summary output. Next, movies missing release year data are inspected below.
moviesDS[moviesDS$movieId %in% head(movies[naIndices,])$movieId , ]
| movieId | title | genres | |
|---|---|---|---|
| <int> | <chr> | <chr> | |
| 15037 | 79607 | Millions Game, The (Das Millionenspiel) | Action|Drama|Sci-Fi|Thriller |
| 25388 | 123619 | Terrible Joe Moran | (no genres listed) |
| 26285 | 125571 | The Court-Martial of Jackie Robinson | (no genres listed) |
| 26310 | 125632 | In Our Garden | (no genres listed) |
| 26393 | 125958 | Stephen Fry In America - New World | (no genres listed) |
| 26577 | 126438 | Two: The Story of Roman & Nyro | Documentary|Drama |
sampleView(movies)
| movieId | title | genres | year | |
|---|---|---|---|---|
| <int> | <chr> | <chr> | <dbl> | |
| 61413 | 205839 | About Endlessness (2019) | Comedy|Drama | 2019 |
| 54425 | 189535 | How Long Will I Love U (2018) | Comedy|Fantasy|Romance | 2018 |
| 34020 | 144490 | The Cat (2011) | Mystery | 2011 |
| 8826 | 26472 | Norte, El (1984) | Adventure|Drama | 1984 |
| 46208 | 171975 | Spider-Plant Man (2005) | Comedy | 2005 |
| 47128 | 173859 | The Night Before Christmas (1951) | Animation|Fantasy | 1951 |
Removing Missing Data¶
Certain movies lack a release year or have an invalid title format. Their movieId values are noted, and these records are excluded from the analysis.
# Extract IDs of movies with missing years
missing_year_ids <- movies$movieId[is.na(movies$year)]
# Display discarded movies
head(movies[is.na(movies$year), ])
| movieId | title | genres | year | |
|---|---|---|---|---|
| <int> | <chr> | <chr> | <dbl> | |
| 15037 | 79607 | Millions Game, The (Das Millionenspiel) | Action|Drama|Sci-Fi|Thriller | NA |
| 25388 | 123619 | Terrible Joe Moran | (no genres listed) | NA |
| 26285 | 125571 | The Court-Martial of Jackie Robinson | (no genres listed) | NA |
| 26310 | 125632 | In Our Garden | (no genres listed) | NA |
| 26393 | 125958 | Stephen Fry In America - New World | (no genres listed) | NA |
| 26577 | 126438 | Two: The Story of Roman & Nyro | Documentary|Drama | NA |
cpcHCAD::naSummary(movies)
| Feature | NA_Count | NA_Percent |
|---|---|---|
| <chr> | <dbl> | <dbl> |
| movieId | 0 | 0.00 |
| title | 0 | 0.00 |
| genres | 0 | 0.00 |
| year | 406 | 0.65 |
# clearn NA
movies = na.omit(movies)
dim(movies)
- 62017
- 4
sum(is.na.data.frame(movies))
movies[is.na(movies$year), ]
| movieId | title | genres | year |
|---|---|---|---|
| <int> | <chr> | <chr> | <dbl> |
62423 - 406 == 62017
summary(movies$year)
Min. 1st Qu. Median Mean 3rd Qu. Max. 1013 1976 2002 1992 2012 2019
Handling Anomalies¶
The value 1013 is identified as an anomaly in the year column.
movies[movies$year==1013,]
| movieId | title | genres | year | |
|---|---|---|---|---|
| <int> | <chr> | <chr> | <dbl> | |
| 61028 | 204770 | 1013 Briar Lane | Horror | 1013 |
Manual verification confirms that 2009 is the correct release year for 1013 Briar Lane.
movies[movies$title=="1013 Briar Lane",]$year = 2009
movies[movies$title=="1013 Briar Lane",]
| movieId | title | genres | year | |
|---|---|---|---|---|
| <int> | <chr> | <chr> | <dbl> | |
| 61028 | 204770 | 1013 Briar Lane | Horror | 2009 |
summary(movies$year)
Min. 1st Qu. Median Mean 3rd Qu. Max. 1874 1976 2002 1992 2012 2019
movies[movies$year== 1874,]
| movieId | title | genres | year | |
|---|---|---|---|---|
| <int> | <chr> | <chr> | <dbl> | |
| 35537 | 148054 | Passage de Venus (1874) | Documentary | 1874 |
Passage de Vénus (1874) is confirmed as a legitimate entry and retained.
Visualizing Year Distribution¶
ggplot(movies, aes(x = year)) +
geom_bar(fill = "steelblue", color = "white", linewidth = 0.2) +
scale_x_continuous(
limits = c(1870, 2020),
breaks = seq(1870, 2020, by = 10)
) +
theme_minimal() +
labs(
title = "Distribution of Movie Release Years",
x = "Release Year",
y = "Number of Movies"
) +
theme(
plot.title = element_text(face = "bold", hjust = 0.5, size = 14),
axis.text = element_text(size = 10),
axis.title = element_text(size = 12)
)
Extracting Title¶
movies$title <- stringr::str_trim(
stringr::str_replace(movies$title, "\\s*\\(\\d{4}[^)]*\\)\\s*$|\\s+\\d{4}\\s*$", "")
)
cpcHCAD::naSummary(movies)
| Feature | NA_Count | NA_Percent |
|---|---|---|
| <chr> | <dbl> | <dbl> |
| movieId | 0 | 0 |
| title | 0 | 0 |
| genres | 0 | 0 |
| year | 0 | 0 |
Extracting Genres¶
One-Hot Encoding: Generate binary indicator variables for 18 target genres (excluding "IMAX").
Filtering Invalid Records: Exclude entries with invalid or missing genre information (e.g., "(no genres listed)").
Clean up: Verify the encoded outputs and drop the original genres column.
genresList = unique(unlist(stringr::str_split(movies$genres,"\\|")))
genresList
- 'Adventure'
- 'Animation'
- 'Children'
- 'Comedy'
- 'Fantasy'
- 'Romance'
- 'Drama'
- 'Action'
- 'Crime'
- 'Thriller'
- 'Horror'
- 'Mystery'
- 'Sci-Fi'
- 'IMAX'
- 'Documentary'
- 'War'
- 'Musical'
- 'Western'
- 'Film-Noir'
- '(no genres listed)'
length(genresList)
cpcHCAD::naSummary(movies)
| Feature | NA_Count | NA_Percent |
|---|---|---|
| <chr> | <dbl> | <dbl> |
| movieId | 0 | 0 |
| title | 0 | 0 |
| genres | 0 | 0 |
| year | 0 | 0 |
dim(movies[which(movies$genres =="(no genres listed)"),])
- 4801
- 4
movies %>%
filter(str_detect(genres,"(no genres listed)")) %>% nrow()
dim(movies[which(movies$genres =="IMAX"),])
- 1
- 4
length((movies %>%
filter(str_detect(genres,"(no genres listed)") ))$movieId )
invalid_genres_id = c((movies %>%
filter(str_detect(genres,"(no genres listed)") ))$movieId,
which(movies$genres =="IMAX"))
length(invalid_genres_id) # 1 + 4801 = 4082
Removing Rows with Invalid Genres¶
movies = movies[!(movies$movieId %in% invalid_genres_id),]
dim(movies)
- 57215
- 4
sum(is.na.data.frame(movies))
genresList = genresList[! genresList %in% c("IMAX","(no genres listed)")]
length(genresList)
sum(str_detect(movies$genres,"no genres"))
One-Hot Encoding / Dummy Variables¶
for(genre in genresList){
movies[genre] = ifelse(( stringr::str_detect(movies$genres,genre)) ,
TRUE , FALSE)
}
cpcHCAD::sampleView(movies)
| movieId | title | genres | year | Adventure | Animation | Children | Comedy | Fantasy | Romance | ⋯ | Crime | Thriller | Horror | Mystery | Sci-Fi | Documentary | War | Musical | Western | Film-Noir | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| <int> | <chr> | <chr> | <dbl> | <lgl> | <lgl> | <lgl> | <lgl> | <lgl> | <lgl> | ⋯ | <lgl> | <lgl> | <lgl> | <lgl> | <lgl> | <lgl> | <lgl> | <lgl> | <lgl> | <lgl> | |
| 59297 | 200540 | Aladdin | Adventure|Fantasy|Romance | 2019 | TRUE | FALSE | FALSE | FALSE | TRUE | TRUE | ⋯ | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE |
| 35729 | 148540 | Desirable Teacher 2 | Comedy | 1982 | FALSE | FALSE | FALSE | TRUE | FALSE | FALSE | ⋯ | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE |
| 8827 | 26475 | Lianna | Drama|Romance | 1983 | FALSE | FALSE | FALSE | FALSE | FALSE | TRUE | ⋯ | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE |
| 50053 | 180169 | Daguerréotypes | Documentary | 1976 | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | ⋯ | FALSE | FALSE | FALSE | FALSE | FALSE | TRUE | FALSE | FALSE | FALSE | FALSE |
| 51093 | 182395 | Judwaa 2 | Action|Comedy|Romance | 2017 | FALSE | FALSE | FALSE | TRUE | FALSE | TRUE | ⋯ | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE |
| 16746 | 88235 | Guard, The | Comedy|Crime | 2011 | FALSE | FALSE | FALSE | TRUE | FALSE | FALSE | ⋯ | TRUE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE | FALSE |
Testing the Results¶
sum(str_detect(movies$genres,"Adventure"))
sum(movies["Adventure"])
for(genre in genresList){
sizeGenre =sum(str_detect(movies$genres,genre))
print(paste0(genre," : ", sizeGenre, "; Pass test result: ",
sizeGenre == sum(movies[genre])))
}
[1] "Adventure : 4136; Pass test result: TRUE" [1] "Animation : 2927; Pass test result: TRUE" [1] "Children : 2930; Pass test result: TRUE" [1] "Comedy : 16843; Pass test result: TRUE" [1] "Fantasy : 2724; Pass test result: TRUE" [1] "Romance : 7707; Pass test result: TRUE" [1] "Drama : 25559; Pass test result: TRUE" [1] "Action : 7335; Pass test result: TRUE" [1] "Crime : 5314; Pass test result: TRUE" [1] "Thriller : 8631; Pass test result: TRUE" [1] "Horror : 5971; Pass test result: TRUE" [1] "Mystery : 2921; Pass test result: TRUE" [1] "Sci-Fi : 3583; Pass test result: TRUE" [1] "Documentary : 5561; Pass test result: TRUE" [1] "War : 1874; Pass test result: TRUE" [1] "Musical : 1052; Pass test result: TRUE" [1] "Western : 1399; Pass test result: TRUE" [1] "Film-Noir : 353; Pass test result: TRUE"
Visualizing the Genre Distribution¶
str(movies)
'data.frame': 57215 obs. of 22 variables: $ movieId : int 1 2 3 4 5 6 7 8 9 10 ... $ title : chr "Toy Story" "Jumanji" "Grumpier Old Men" "Waiting to Exhale" ... $ genres : chr "Adventure|Animation|Children|Comedy|Fantasy" "Adventure|Children|Fantasy" "Comedy|Romance" "Comedy|Drama|Romance" ... $ year : num 1995 1995 1995 1995 1995 ... $ Adventure : logi TRUE TRUE FALSE FALSE FALSE FALSE ... $ Animation : logi TRUE FALSE FALSE FALSE FALSE FALSE ... $ Children : logi TRUE TRUE FALSE FALSE FALSE FALSE ... $ Comedy : logi TRUE FALSE TRUE TRUE TRUE FALSE ... $ Fantasy : logi TRUE TRUE FALSE FALSE FALSE FALSE ... $ Romance : logi FALSE FALSE TRUE TRUE FALSE FALSE ... $ Drama : logi FALSE FALSE FALSE TRUE FALSE FALSE ... $ Action : logi FALSE FALSE FALSE FALSE FALSE TRUE ... $ Crime : logi FALSE FALSE FALSE FALSE FALSE TRUE ... $ Thriller : logi FALSE FALSE FALSE FALSE FALSE TRUE ... $ Horror : logi FALSE FALSE FALSE FALSE FALSE FALSE ... $ Mystery : logi FALSE FALSE FALSE FALSE FALSE FALSE ... $ Sci-Fi : logi FALSE FALSE FALSE FALSE FALSE FALSE ... $ Documentary: logi FALSE FALSE FALSE FALSE FALSE FALSE ... $ War : logi FALSE FALSE FALSE FALSE FALSE FALSE ... $ Musical : logi FALSE FALSE FALSE FALSE FALSE FALSE ... $ Western : logi FALSE FALSE FALSE FALSE FALSE FALSE ... $ Film-Noir : logi FALSE FALSE FALSE FALSE FALSE FALSE ... - attr(*, "na.action")= 'omit' Named int [1:406] 15037 25388 26285 26310 26393 26577 26700 27217 27270 27578 ... ..- attr(*, "names")= chr [1:406] "15037" "25388" "26285" "26310" ...
cpcHCAD::columnNameIndex(movies)
| Index | Column | Class | |
|---|---|---|---|
| <int> | <chr> | <chr> | |
| movieId | 1 | movieId | integer |
| title | 2 | title | character |
| genres | 3 | genres | character |
| year | 4 | year | numeric |
| Adventure | 5 | Adventure | logical |
| Animation | 6 | Animation | logical |
| Children | 7 | Children | logical |
| Comedy | 8 | Comedy | logical |
| Fantasy | 9 | Fantasy | logical |
| Romance | 10 | Romance | logical |
| Drama | 11 | Drama | logical |
| Action | 12 | Action | logical |
| Crime | 13 | Crime | logical |
| Thriller | 14 | Thriller | logical |
| Horror | 15 | Horror | logical |
| Mystery | 16 | Mystery | logical |
| Sci-Fi | 17 | Sci-Fi | logical |
| Documentary | 18 | Documentary | logical |
| War | 19 | War | logical |
| Musical | 20 | Musical | logical |
| Western | 21 | Western | logical |
| Film-Noir | 22 | Film-Noir | logical |
# Count of each movie genre
genre_counts = colSums(movies[, 5:22])
genre_counts_df = data.frame(
genre = names(genre_counts),
movieCount = genre_counts
)
# Sort for plotting
genre_counts_df$genre = factor(
genre_counts_df$genre,
levels = names(sort(genre_counts, decreasing = FALSE))
)
ggplot(genre_counts_df, aes(x = genre, y = movieCount, fill = genre)) +
geom_bar(stat = "identity") +
coord_flip() +
ggtitle("Genre Distribution") +
theme(legend.position = "none")
write.csv(movies,"./outputData/ml-25m_movies_transformed.csv", row.names = FALSE)
Cleaning and Transforming ratings.csv¶
Importing the ratings.csv Dataset¶
ratings = read.csv("ml-25m/ratings.csv",
# colClasses = c("integer","integer","numeric","NULL"),
sep=",",
stringsAsFactors = FALSE)
cpcHCAD::sampleView(ratings)
| userId | movieId | rating | timestamp | |
|---|---|---|---|---|
| <int> | <int> | <dbl> | <int> | |
| 3207141 | 21161 | 112497 | 2.5 | 1494332716 |
| 21025945 | 136676 | 924 | 3.0 | 974772315 |
| 4818148 | 31470 | 1059 | 4.0 | 1129626768 |
| 9511546 | 61803 | 8854 | 2.5 | 1093849069 |
| 3191936 | 21068 | 748 | 3.0 | 850465596 |
| 19839000 | 128941 | 4133 | 3.0 | 995588864 |
dim(ratings)
- 25000095
- 4
length( unique(ratings$movieId))
length( unique(ratings$userId))
Note: Note: A total of 59,047 unique movieId values across 162,541 unique userId values are contained in the ratings dataset, compared to 57,215 valid movies in the cleaned movies dataset.
Aligning ratings with Valid movies¶
ratings <- ratings %>% filter(movieId %in% movies$movieId)
dim(ratings)
- 24963372
- 4
The number of records removed from the ratings dataset is shown below:
25000095 - 24963372
length( unique(ratings$movieId))
# no changes
length( unique(ratings$userId))
There are now 54,351 shared movieId values in both ratings and movies datasets, contributed by 162,541 unique userID users.
head(ratings)
| userId | movieId | rating | timestamp | |
|---|---|---|---|---|
| <int> | <int> | <dbl> | <int> | |
| 1 | 1 | 296 | 5.0 | 1147880044 |
| 2 | 1 | 306 | 3.5 | 1147868817 |
| 3 | 1 | 307 | 5.0 | 1147868828 |
| 4 | 1 | 665 | 5.0 | 1147878820 |
| 5 | 1 | 899 | 3.5 | 1147868510 |
| 6 | 1 | 1088 | 4.0 | 1147868495 |
Handling Timestamps¶
ratings$ratingYear = as.integer(format(
as.POSIXct(ratings$timestamp, origin="1970-01-01", tz="UTC"), "%Y"
))
ratings$timestamp = NULL
head(ratings)
| userId | movieId | rating | ratingYear | |
|---|---|---|---|---|
| <int> | <int> | <dbl> | <int> | |
| 1 | 1 | 296 | 5.0 | 2006 |
| 2 | 1 | 306 | 3.5 | 2006 |
| 3 | 1 | 307 | 5.0 | 2006 |
| 4 | 1 | 665 | 5.0 | 2006 |
| 5 | 1 | 899 | 3.5 | 2006 |
| 6 | 1 | 1088 | 4.0 | 2006 |
str(ratings)
'data.frame': 24963372 obs. of 4 variables: $ userId : int 1 1 1 1 1 1 1 1 1 1 ... $ movieId : int 296 306 307 665 899 1088 1175 1217 1237 1250 ... $ rating : num 5 3.5 5 5 3.5 4 3.5 3.5 5 4 ... $ ratingYear: int 2006 2006 2006 2006 2006 2006 2006 2006 2006 2006 ...
Feature Engineering: Extracting Movie Rating Statistics¶
MoviesRatingFeatures = ratings %>%
group_by(movieId) %>%
summarise(raterSize = n(), ratingScoreMean = mean(rating),
ratingScoreSD = sd(rating),
ratingScoreMedian =median(rating),
ratingScoreMin = min(rating),
ratingScoreMax = max(rating),
ratingYearMean = mean(ratingYear),
ratingYearSD = sd(ratingYear),
ratingYearMedian = median(ratingYear),
ratingYearMin = min(ratingYear),
ratingYearMax = max(ratingYear)) %>%
as.data.frame()
head(MoviesRatingFeatures)
| movieId | raterSize | ratingScoreMean | ratingScoreSD | ratingScoreMedian | ratingScoreMin | ratingScoreMax | ratingYearMean | ratingYearSD | ratingYearMedian | ratingYearMin | ratingYearMax | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| <int> | <int> | <dbl> | <dbl> | <dbl> | <dbl> | <dbl> | <dbl> | <dbl> | <dbl> | <int> | <int> | |
| 1 | 1 | 57309 | 3.893708 | 0.9215519 | 4 | 0.5 | 5 | 2006.041 | 7.564349 | 2005 | 1996 | 2019 |
| 2 | 2 | 24228 | 3.251527 | 0.9598514 | 3 | 0.5 | 5 | 2005.040 | 7.608254 | 2005 | 1996 | 2019 |
| 3 | 3 | 11804 | 3.142028 | 1.0084427 | 3 | 0.5 | 5 | 2000.591 | 5.456962 | 1997 | 1996 | 2019 |
| 4 | 4 | 2523 | 2.853547 | 1.1085308 | 3 | 0.5 | 5 | 1999.330 | 4.770699 | 1997 | 1996 | 2019 |
| 5 | 5 | 11714 | 3.058434 | 0.9966113 | 3 | 0.5 | 5 | 2001.349 | 6.683915 | 1997 | 1996 | 2019 |
| 6 | 6 | 24588 | 3.854909 | 0.8778706 | 4 | 0.5 | 5 | 2003.519 | 7.325704 | 2001 | 1996 | 2019 |
Joining the Datasets¶
dim(MoviesRatingFeatures)
- 54351
- 12
dim(movies)
- 57215
- 22
moviesDB = left_join(MoviesRatingFeatures,movies, by="movieId")
dim(moviesDB)
- 54351
- 33
str(moviesDB)
'data.frame': 54351 obs. of 33 variables: $ movieId : int 1 2 3 4 5 6 7 8 9 10 ... $ raterSize : int 57309 24228 11804 2523 11714 24588 12132 1344 3711 28265 ... $ ratingScoreMean : num 3.89 3.25 3.14 2.85 3.06 ... $ ratingScoreSD : num 0.922 0.96 1.008 1.109 0.997 ... $ ratingScoreMedian: num 4 3 3 3 3 4 3 3 3 3.5 ... $ ratingScoreMin : num 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5 ... $ ratingScoreMax : num 5 5 5 5 5 5 5 5 5 5 ... $ ratingYearMean : num 2006 2005 2001 1999 2001 ... $ ratingYearSD : num 7.56 7.61 5.46 4.77 6.68 ... $ ratingYearMedian : num 2005 2005 1997 1997 1997 ... $ ratingYearMin : int 1996 1996 1996 1996 1996 1996 1996 1996 1996 1996 ... $ ratingYearMax : int 2019 2019 2019 2019 2019 2019 2019 2019 2019 2019 ... $ title : chr "Toy Story" "Jumanji" "Grumpier Old Men" "Waiting to Exhale" ... $ genres : chr "Adventure|Animation|Children|Comedy|Fantasy" "Adventure|Children|Fantasy" "Comedy|Romance" "Comedy|Drama|Romance" ... $ year : num 1995 1995 1995 1995 1995 ... $ Adventure : logi TRUE TRUE FALSE FALSE FALSE FALSE ... $ Animation : logi TRUE FALSE FALSE FALSE FALSE FALSE ... $ Children : logi TRUE TRUE FALSE FALSE FALSE FALSE ... $ Comedy : logi TRUE FALSE TRUE TRUE TRUE FALSE ... $ Fantasy : logi TRUE TRUE FALSE FALSE FALSE FALSE ... $ Romance : logi FALSE FALSE TRUE TRUE FALSE FALSE ... $ Drama : logi FALSE FALSE FALSE TRUE FALSE FALSE ... $ Action : logi FALSE FALSE FALSE FALSE FALSE TRUE ... $ Crime : logi FALSE FALSE FALSE FALSE FALSE TRUE ... $ Thriller : logi FALSE FALSE FALSE FALSE FALSE TRUE ... $ Horror : logi FALSE FALSE FALSE FALSE FALSE FALSE ... $ Mystery : logi FALSE FALSE FALSE FALSE FALSE FALSE ... $ Sci-Fi : logi FALSE FALSE FALSE FALSE FALSE FALSE ... $ Documentary : logi FALSE FALSE FALSE FALSE FALSE FALSE ... $ War : logi FALSE FALSE FALSE FALSE FALSE FALSE ... $ Musical : logi FALSE FALSE FALSE FALSE FALSE FALSE ... $ Western : logi FALSE FALSE FALSE FALSE FALSE FALSE ... $ Film-Noir : logi FALSE FALSE FALSE FALSE FALSE FALSE ...
Feature Engineering: Computing Time-Based Metrics¶
moviesDB =moviesDB %>% mutate(movieAge = 2019 - year,
ratingPeriodMean = ratingYearMean - year,
ratingPeriodMedian = ratingYearMedian - year,
ratingPeriodMin = ratingYearMin - year,
ratingPeriodMax = ratingYearMax - year
)
dim(moviesDB)
- 54351
- 38
options(width = 150)
summary(moviesDB)
movieId raterSize ratingScoreMean ratingScoreSD ratingScoreMedian ratingScoreMin ratingScoreMax ratingYearMean
Min. : 1 Min. : 1.0 Min. :0.500 Min. :0.0000 Min. :0.500 Min. :0.500 Min. :0.500 Min. :1997
1st Qu.: 70420 1st Qu.: 2.0 1st Qu.:2.700 1st Qu.:0.7530 1st Qu.:2.750 1st Qu.:0.500 1st Qu.:3.500 1st Qu.:2014
Median :132142 Median : 7.0 Median :3.154 Median :0.9643 Median :3.000 Median :1.000 Median :4.500 Median :2017
Mean :117024 Mean : 459.3 Mean :3.073 Mean :0.9583 Mean :3.121 Mean :1.582 Mean :4.193 Mean :2015
3rd Qu.:172677 3rd Qu.: 45.0 3rd Qu.:3.500 3rd Qu.:1.1473 3rd Qu.:3.500 3rd Qu.:2.500 3rd Qu.:5.000 3rd Qu.:2018
Max. :209171 Max. :81491.0 Max. :5.000 Max. :3.1820 Max. :5.000 Max. :5.000 Max. :5.000 Max. :2019
NAs :8417
ratingYearSD ratingYearMedian ratingYearMin ratingYearMax title genres year Adventure Animation
Min. :0.0000 Min. :1996 Min. :1995 Min. :2001 Length :54351 Length :54351 Min. :1874 Mode :logical Mode :logical
1st Qu.:0.7576 1st Qu.:2014 1st Qu.:2010 1st Qu.:2018 N.unique :51134 N.unique : 1620 1st Qu.:1979 FALSE:50491 FALSE:51442
Median :1.5275 Median :2016 Median :2015 Median :2019 N.blank : 0 N.blank : 0 Median :2003 TRUE :3860 TRUE :2909
Mean :2.1809 Mean :2015 Mean :2013 Mean :2018 Min.nchar: 1 Min.nchar: 3 Mean :1993
3rd Qu.:3.4646 3rd Qu.:2018 3rd Qu.:2017 3rd Qu.:2019 Max.nchar: 184 Max.nchar: 77 3rd Qu.:2013
Max. :9.1357 Max. :2019 Max. :2019 Max. :2019 Max. :2019
NAs :8417
Children Comedy Fantasy Romance Drama Action Crime Thriller Horror
Mode :logical Mode :logical Mode :logical Mode :logical Mode :logical Mode :logical Mode :logical Mode :logical Mode :logical
FALSE:51489 FALSE:38323 FALSE:51691 FALSE:47057 FALSE:29927 FALSE:47448 FALSE:49332 FALSE:46042 FALSE:48622
TRUE :2862 TRUE :16028 TRUE :2660 TRUE :7294 TRUE :24424 TRUE :6903 TRUE :5019 TRUE :8309 TRUE :5729
Mystery Sci-Fi Documentary War Musical Western Film-Noir movieAge ratingPeriodMean
Mode :logical Mode :logical Mode :logical Mode :logical Mode :logical Mode :logical Mode :logical Min. : 0.00 Min. : -0.4595
FALSE:51573 FALSE:50861 FALSE:48935 FALSE:52581 FALSE:53336 FALSE:53195 FALSE:54002 1st Qu.: 6.00 1st Qu.: 3.8157
TRUE :2778 TRUE :3490 TRUE :5416 TRUE :1770 TRUE :1015 TRUE :1156 TRUE :349 Median : 16.00 Median : 9.4531
Mean : 25.52 Mean : 21.4549
3rd Qu.: 40.00 3rd Qu.: 35.1877
Max. :145.00 Max. :143.5000
ratingPeriodMedian ratingPeriodMin ratingPeriodMax
Min. : -1.00 Min. : -4.00 Min. : 0.00
1st Qu.: 3.00 1st Qu.: 1.00 1st Qu.: 6.00
Median : 9.00 Median : 8.00 Median : 15.00
Mean : 21.16 Mean : 19.34 Mean : 24.92
3rd Qu.: 35.00 3rd Qu.: 33.00 3rd Qu.: 39.00
Max. :144.00 Max. :142.00 Max. :145.00
Removing Negative Age Values¶
The negative values in the ratingPeriod metrics indicate that some users submitted ratings for a movie before its officially recorded release year (i.e., the calculated ratingYear is smaller than the movieYear). Due to the large scale of the dataset, records with negative rating periods—where a user submitted a rating prior to a movie's recorded release year—are excluded from further analysis.
data.frame(
Index = 1:ncol(moviesDB),
Column = names(moviesDB),
Class = sapply(moviesDB, class)
)
| Index | Column | Class | |
|---|---|---|---|
| <int> | <chr> | <chr> | |
| movieId | 1 | movieId | integer |
| raterSize | 2 | raterSize | integer |
| ratingScoreMean | 3 | ratingScoreMean | numeric |
| ratingScoreSD | 4 | ratingScoreSD | numeric |
| ratingScoreMedian | 5 | ratingScoreMedian | numeric |
| ratingScoreMin | 6 | ratingScoreMin | numeric |
| ratingScoreMax | 7 | ratingScoreMax | numeric |
| ratingYearMean | 8 | ratingYearMean | numeric |
| ratingYearSD | 9 | ratingYearSD | numeric |
| ratingYearMedian | 10 | ratingYearMedian | numeric |
| ratingYearMin | 11 | ratingYearMin | integer |
| ratingYearMax | 12 | ratingYearMax | integer |
| title | 13 | title | character |
| genres | 14 | genres | character |
| year | 15 | year | numeric |
| Adventure | 16 | Adventure | logical |
| Animation | 17 | Animation | logical |
| Children | 18 | Children | logical |
| Comedy | 19 | Comedy | logical |
| Fantasy | 20 | Fantasy | logical |
| Romance | 21 | Romance | logical |
| Drama | 22 | Drama | logical |
| Action | 23 | Action | logical |
| Crime | 24 | Crime | logical |
| Thriller | 25 | Thriller | logical |
| Horror | 26 | Horror | logical |
| Mystery | 27 | Mystery | logical |
| Sci-Fi | 28 | Sci-Fi | logical |
| Documentary | 29 | Documentary | logical |
| War | 30 | War | logical |
| Musical | 31 | Musical | logical |
| Western | 32 | Western | logical |
| Film-Noir | 33 | Film-Noir | logical |
| movieAge | 34 | movieAge | numeric |
| ratingPeriodMean | 35 | ratingPeriodMean | numeric |
| ratingPeriodMedian | 36 | ratingPeriodMedian | numeric |
| ratingPeriodMin | 37 | ratingPeriodMin | numeric |
| ratingPeriodMax | 38 | ratingPeriodMax | numeric |
summary(moviesDB[,35:38])
ratingPeriodMean ratingPeriodMedian ratingPeriodMin ratingPeriodMax Min. : -0.4595 Min. : -1.00 Min. : -4.00 Min. : 0.00 1st Qu.: 3.8157 1st Qu.: 3.00 1st Qu.: 1.00 1st Qu.: 6.00 Median : 9.4531 Median : 9.00 Median : 8.00 Median : 15.00 Mean : 21.4549 Mean : 21.16 Mean : 19.34 Mean : 24.92 3rd Qu.: 35.1877 3rd Qu.: 35.00 3rd Qu.: 33.00 3rd Qu.: 39.00 Max. :143.5000 Max. :144.00 Max. :142.00 Max. :145.00
remove_negative_age_index = which(moviesDB[,35:38]<0, arr.ind = TRUE)
sampleView(remove_negative_age_index, seed_No = 100)
| row | col |
|---|---|
| 28804 | 3 |
| 44819 | 3 |
| 30614 | 3 |
| 1594 | 3 |
| 38395 | 3 |
| 28295 | 3 |
dim(remove_negative_age_index)
- 96
- 2
sampleView(moviesDB[remove_negative_age_index[,1] ,35:38])
| ratingPeriodMean | ratingPeriodMedian | ratingPeriodMin | ratingPeriodMax | |
|---|---|---|---|---|
| <dbl> | <dbl> | <dbl> | <dbl> | |
| 18944 | 3.337903582 | 3 | -1 | 6 |
| 24494 | 1.683039140 | 2 | -1 | 3 |
| 1619 | 7.606978471 | 7 | -1 | 21 |
| 28804 | 1.000000000 | 1 | -1 | 3 |
| 1032 | 2.783333333 | 1 | -1 | 22 |
| 50203.1 | -0.006944444 | 0 | -1 | 0 |
length(unique(remove_negative_age_index[, 1]))
# Extract unique row indices to remove
rows_to_remove <- unique(remove_negative_age_index[, 1])
if (length(rows_to_remove) > 0) {
moviesDB <- moviesDB[-rows_to_remove, ]
}
summary(moviesDB[,35:38])
ratingPeriodMean ratingPeriodMedian ratingPeriodMin ratingPeriodMax Min. : 0.000 Min. : 0.00 Min. : 0.00 Min. : 0.00 1st Qu.: 3.833 1st Qu.: 3.00 1st Qu.: 1.00 1st Qu.: 6.00 Median : 9.500 Median : 9.00 Median : 8.00 Median : 15.00 Mean : 21.485 Mean : 21.19 Mean : 19.37 Mean : 24.94 3rd Qu.: 35.250 3rd Qu.: 35.00 3rd Qu.: 33.00 3rd Qu.: 39.00 Max. :143.500 Max. :144.00 Max. :142.00 Max. :145.00
dim(moviesDB)
- 54266
- 38
54351 - 85 == 54266
Saving the Transformed Dataset to CSV¶
write.csv(moviesDB,"outputData/ml-25m_movies_rating_transformed.csv", row.names = FALSE)
How to Cite This Work¶
Yuen, K. K. F. (2026). Advancing Cognitive Pairwise Comparisons for Human-Centric Artificial Intelligence and Data Science. Manuscript under review. A preprint will be available shortly.