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¶

In [1]:
suppressPackageStartupMessages({
  library(dplyr)
  library(stringr)
  library(ggplot2)
  library(cpcHCAD)  # Created by KKF YUEN
})

output_dir <- "outputData"

Importing the movies.csv Dataset¶

In [2]:
moviesDS = read.csv("ml-25m/movies.csv", 
                  colClasses = c("integer","character","character"),
                  sep = ",",
                  stringsAsFactors = FALSE)
In [3]:
cpcHCAD::headTail(moviesDS,show_rownames = TRUE)
A data.frame: 6 × 3
movieIdtitlegenres
<int><chr><chr>
1 1Toy Story (1995) Adventure|Animation|Children|Comedy|Fantasy
2 2Jumanji (1995) Adventure|Children|Fantasy
3 3Grumpier Old Men (1995) Comedy|Romance
62421209163Bad Poems (2018) Comedy|Drama
62422209169A Girl Thing (2001) (no genres listed)
62423209171Women of Devil's Island (1962)Action|Adventure|Drama
In [4]:
dim(moviesDS)
  1. 62423
  2. 3

Checking and Handling Missing Data¶

In [5]:
cpcHCAD::naSummary(moviesDS)
A data.frame: 3 × 3
FeatureNA_CountNA_Percent
<chr><dbl><dbl>
movieId00
title 00
genres 00
In [6]:
anyNA(moviesDS)
FALSE
In [7]:
movies = na.omit(moviesDS)
dim(movies)
  1. 62423
  2. 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.

In [8]:
movies$year <- as.numeric(str_extract(movies$title, "\\d{4}(?=[^\\d]*$)"))

Verifying Missing Values¶

In [9]:
cpcHCAD::naSummary(movies$year)
A data.frame: 1 × 3
FeatureNA_CountNA_Percent
<chr><dbl><dbl>
movies$year4060.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.

In [10]:
naIndices = is.na(movies$year)
In [11]:
sum(naIndices)
406

The count of 406 missing years aligns with the summary output. Next, movies missing release year data are inspected below.

In [12]:
moviesDS[moviesDS$movieId %in% head(movies[naIndices,])$movieId	, ]
A data.frame: 6 × 3
movieIdtitlegenres
<int><chr><chr>
15037 79607Millions Game, The (Das Millionenspiel)Action|Drama|Sci-Fi|Thriller
25388123619Terrible Joe Moran (no genres listed)
26285125571The Court-Martial of Jackie Robinson (no genres listed)
26310125632In Our Garden (no genres listed)
26393125958Stephen Fry In America - New World (no genres listed)
26577126438Two: The Story of Roman & Nyro Documentary|Drama
In [13]:
sampleView(movies)
A data.frame: 6 × 4
movieIdtitlegenresyear
<int><chr><chr><dbl>
61413205839About Endlessness (2019) Comedy|Drama 2019
54425189535How Long Will I Love U (2018) Comedy|Fantasy|Romance2018
34020144490The Cat (2011) Mystery 2011
8826 26472Norte, El (1984) Adventure|Drama 1984
46208171975Spider-Plant Man (2005) Comedy 2005
47128173859The 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.

In [14]:
# 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), ])
A data.frame: 6 × 4
movieIdtitlegenresyear
<int><chr><chr><dbl>
15037 79607Millions Game, The (Das Millionenspiel)Action|Drama|Sci-Fi|ThrillerNA
25388123619Terrible Joe Moran (no genres listed) NA
26285125571The Court-Martial of Jackie Robinson (no genres listed) NA
26310125632In Our Garden (no genres listed) NA
26393125958Stephen Fry In America - New World (no genres listed) NA
26577126438Two: The Story of Roman & Nyro Documentary|Drama NA
In [15]:
cpcHCAD::naSummary(movies)
A data.frame: 4 × 3
FeatureNA_CountNA_Percent
<chr><dbl><dbl>
movieId 00.00
title 00.00
genres 00.00
year 4060.65
In [16]:
# clearn NA
movies = na.omit(movies)
dim(movies)
  1. 62017
  2. 4
In [17]:
sum(is.na.data.frame(movies))
0
In [18]:
movies[is.na(movies$year), ]
A data.frame: 0 × 4
movieIdtitlegenresyear
<int><chr><chr><dbl>
In [19]:
62423 - 406 == 62017
TRUE
In [20]:
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.

In [21]:
movies[movies$year==1013,]
A data.frame: 1 × 4
movieIdtitlegenresyear
<int><chr><chr><dbl>
610282047701013 Briar LaneHorror1013

Manual verification confirms that 2009 is the correct release year for 1013 Briar Lane.

In [22]:
movies[movies$title=="1013 Briar Lane",]$year = 2009
movies[movies$title=="1013 Briar Lane",]
A data.frame: 1 × 4
movieIdtitlegenresyear
<int><chr><chr><dbl>
610282047701013 Briar LaneHorror2009
In [23]:
summary(movies$year)
   Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
   1874    1976    2002    1992    2012    2019 
In [24]:
movies[movies$year== 1874,]
A data.frame: 1 × 4
movieIdtitlegenresyear
<int><chr><chr><dbl>
35537148054Passage de Venus (1874)Documentary1874

Passage de Vénus (1874) is confirmed as a legitimate entry and retained.

https://en.wikipedia.org/wiki/Passage_de_V%C3%A9nus

Visualizing Year Distribution¶

In [25]:
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)
  )
No description has been provided for this image

Extracting Title¶

In [26]:
movies$title <- stringr::str_trim(
  stringr::str_replace(movies$title, "\\s*\\(\\d{4}[^)]*\\)\\s*$|\\s+\\d{4}\\s*$", "")
)
In [27]:
cpcHCAD::naSummary(movies)
A data.frame: 4 × 3
FeatureNA_CountNA_Percent
<chr><dbl><dbl>
movieId00
title 00
genres 00
year 00

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.

In [28]:
genresList = unique(unlist(stringr::str_split(movies$genres,"\\|")))
In [29]:
genresList
  1. 'Adventure'
  2. 'Animation'
  3. 'Children'
  4. 'Comedy'
  5. 'Fantasy'
  6. 'Romance'
  7. 'Drama'
  8. 'Action'
  9. 'Crime'
  10. 'Thriller'
  11. 'Horror'
  12. 'Mystery'
  13. 'Sci-Fi'
  14. 'IMAX'
  15. 'Documentary'
  16. 'War'
  17. 'Musical'
  18. 'Western'
  19. 'Film-Noir'
  20. '(no genres listed)'
In [30]:
length(genresList)
20
In [31]:
cpcHCAD::naSummary(movies)
A data.frame: 4 × 3
FeatureNA_CountNA_Percent
<chr><dbl><dbl>
movieId00
title 00
genres 00
year 00
In [32]:
dim(movies[which(movies$genres =="(no genres listed)"),])
  1. 4801
  2. 4
In [33]:
movies %>% 
  filter(str_detect(genres,"(no genres listed)")) %>% nrow()
4801
In [34]:
dim(movies[which(movies$genres =="IMAX"),])
  1. 1
  2. 4
In [35]:
length((movies %>% 
  filter(str_detect(genres,"(no genres listed)") ))$movieId )
4801
In [36]:
invalid_genres_id = c((movies %>% 
    filter(str_detect(genres,"(no genres listed)") ))$movieId,
    which(movies$genres =="IMAX"))
In [37]:
length(invalid_genres_id) # 1 + 4801 = 4082
4802

Removing Rows with Invalid Genres¶

In [38]:
movies = movies[!(movies$movieId %in% invalid_genres_id),]
dim(movies)
  1. 57215
  2. 4
In [39]:
sum(is.na.data.frame(movies))
0
In [40]:
genresList = genresList[! genresList %in% c("IMAX","(no genres listed)")]
In [41]:
length(genresList)
18
In [42]:
sum(str_detect(movies$genres,"no genres"))
0

One-Hot Encoding / Dummy Variables¶

In [43]:
for(genre in genresList){
  movies[genre] = ifelse(( stringr::str_detect(movies$genres,genre)) , 
                         TRUE , FALSE)
}
In [44]:
cpcHCAD::sampleView(movies)
A data.frame: 6 × 22
movieIdtitlegenresyearAdventureAnimationChildrenComedyFantasyRomance⋯CrimeThrillerHorrorMysterySci-FiDocumentaryWarMusicalWesternFilm-Noir
<int><chr><chr><dbl><lgl><lgl><lgl><lgl><lgl><lgl>⋯<lgl><lgl><lgl><lgl><lgl><lgl><lgl><lgl><lgl><lgl>
59297200540Aladdin Adventure|Fantasy|Romance2019 TRUEFALSEFALSEFALSE TRUE TRUE⋯FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE
35729148540Desirable Teacher 2Comedy 1982FALSEFALSEFALSE TRUEFALSEFALSE⋯FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE
8827 26475Lianna Drama|Romance 1983FALSEFALSEFALSEFALSEFALSE TRUE⋯FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE
50053180169Daguerréotypes Documentary 1976FALSEFALSEFALSEFALSEFALSEFALSE⋯FALSEFALSEFALSEFALSEFALSE TRUEFALSEFALSEFALSEFALSE
51093182395Judwaa 2 Action|Comedy|Romance 2017FALSEFALSEFALSE TRUEFALSE TRUE⋯FALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE
16746 88235Guard, The Comedy|Crime 2011FALSEFALSEFALSE TRUEFALSEFALSE⋯ TRUEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSEFALSE

Testing the Results¶

In [45]:
sum(str_detect(movies$genres,"Adventure"))
4136
In [46]:
sum(movies["Adventure"])
4136
In [47]:
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¶

In [48]:
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" ...
In [49]:
cpcHCAD::columnNameIndex(movies)
A data.frame: 22 × 3
IndexColumnClass
<int><chr><chr>
movieId 1movieId integer
title 2title character
genres 3genres character
year 4year numeric
Adventure 5Adventure logical
Animation 6Animation logical
Children 7Children logical
Comedy 8Comedy logical
Fantasy 9Fantasy logical
Romance10Romance logical
Drama11Drama logical
Action12Action logical
Crime13Crime logical
Thriller14Thriller logical
Horror15Horror logical
Mystery16Mystery logical
Sci-Fi17Sci-Fi logical
Documentary18Documentarylogical
War19War logical
Musical20Musical logical
Western21Western logical
Film-Noir22Film-Noir logical
In [50]:
# 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")
No description has been provided for this image
In [51]:
write.csv(movies,"./outputData/ml-25m_movies_transformed.csv", row.names = FALSE)

Cleaning and Transforming ratings.csv¶

Importing the ratings.csv Dataset¶

In [52]:
ratings = read.csv("ml-25m/ratings.csv",
                   # colClasses = c("integer","integer","numeric","NULL"),
                   sep=",",
                   stringsAsFactors = FALSE)
In [53]:
cpcHCAD::sampleView(ratings)
A data.frame: 6 × 4
userIdmovieIdratingtimestamp
<int><int><dbl><int>
3207141 211611124972.51494332716
21025945136676 9243.0 974772315
4818148 31470 10594.01129626768
9511546 61803 88542.51093849069
3191936 21068 7483.0 850465596
19839000128941 41333.0 995588864
In [54]:
dim(ratings)
  1. 25000095
  2. 4
In [55]:
length( unique(ratings$movieId))
59047
In [56]:
length( unique(ratings$userId))
162541

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¶

In [57]:
ratings <- ratings %>% filter(movieId %in% movies$movieId)
In [58]:
dim(ratings)
  1. 24963372
  2. 4

The number of records removed from the ratings dataset is shown below:

In [59]:
25000095 - 24963372
36723
In [60]:
length( unique(ratings$movieId))
54351
In [61]:
# no changes
length( unique(ratings$userId))
162541

There are now 54,351 shared movieId values in both ratings and movies datasets, contributed by 162,541 unique userID users.

In [62]:
head(ratings)
A data.frame: 6 × 4
userIdmovieIdratingtimestamp
<int><int><dbl><int>
11 2965.01147880044
21 3063.51147868817
31 3075.01147868828
41 6655.01147878820
51 8993.51147868510
6110884.01147868495

Handling Timestamps¶

In [63]:
ratings$ratingYear = as.integer(format(
    as.POSIXct(ratings$timestamp, origin="1970-01-01", tz="UTC"), "%Y"
))
ratings$timestamp = NULL 
In [64]:
head(ratings)
A data.frame: 6 × 4
userIdmovieIdratingratingYear
<int><int><dbl><int>
11 2965.02006
21 3063.52006
31 3075.02006
41 6655.02006
51 8993.52006
6110884.02006
In [65]:
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¶

In [66]:
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()
In [67]:
head(MoviesRatingFeatures)
A data.frame: 6 × 12
movieIdraterSizeratingScoreMeanratingScoreSDratingScoreMedianratingScoreMinratingScoreMaxratingYearMeanratingYearSDratingYearMedianratingYearMinratingYearMax
<int><int><dbl><dbl><dbl><dbl><dbl><dbl><dbl><dbl><int><int>
11573093.8937080.921551940.552006.0417.564349200519962019
22242283.2515270.959851430.552005.0407.608254200519962019
33118043.1420281.008442730.552000.5915.456962199719962019
44 25232.8535471.108530830.551999.3304.770699199719962019
55117143.0584340.996611330.552001.3496.683915199719962019
66245883.8549090.877870640.552003.5197.325704200119962019

Joining the Datasets¶

In [68]:
dim(MoviesRatingFeatures)
  1. 54351
  2. 12
In [69]:
dim(movies)
  1. 57215
  2. 22
In [70]:
moviesDB = left_join(MoviesRatingFeatures,movies, by="movieId")
In [71]:
dim(moviesDB)
  1. 54351
  2. 33
In [72]:
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¶

In [73]:
moviesDB =moviesDB %>% mutate(movieAge = 2019 - year,
                    ratingPeriodMean = ratingYearMean - year,
                    ratingPeriodMedian = ratingYearMedian - year,
                    ratingPeriodMin = ratingYearMin - year,
                    ratingPeriodMax = ratingYearMax - year
                    )
In [74]:
dim(moviesDB) 
  1. 54351
  2. 38
In [75]:
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.

In [76]:
data.frame(
  Index = 1:ncol(moviesDB),
  Column = names(moviesDB),
  Class = sapply(moviesDB, class)
)
A data.frame: 38 × 3
IndexColumnClass
<int><chr><chr>
movieId 1movieId integer
raterSize 2raterSize integer
ratingScoreMean 3ratingScoreMean numeric
ratingScoreSD 4ratingScoreSD numeric
ratingScoreMedian 5ratingScoreMedian numeric
ratingScoreMin 6ratingScoreMin numeric
ratingScoreMax 7ratingScoreMax numeric
ratingYearMean 8ratingYearMean numeric
ratingYearSD 9ratingYearSD numeric
ratingYearMedian10ratingYearMedian numeric
ratingYearMin11ratingYearMin integer
ratingYearMax12ratingYearMax integer
title13title character
genres14genres character
year15year numeric
Adventure16Adventure logical
Animation17Animation logical
Children18Children logical
Comedy19Comedy logical
Fantasy20Fantasy logical
Romance21Romance logical
Drama22Drama logical
Action23Action logical
Crime24Crime logical
Thriller25Thriller logical
Horror26Horror logical
Mystery27Mystery logical
Sci-Fi28Sci-Fi logical
Documentary29Documentary logical
War30War logical
Musical31Musical logical
Western32Western logical
Film-Noir33Film-Noir logical
movieAge34movieAge numeric
ratingPeriodMean35ratingPeriodMean numeric
ratingPeriodMedian36ratingPeriodMediannumeric
ratingPeriodMin37ratingPeriodMin numeric
ratingPeriodMax38ratingPeriodMax numeric
In [77]:
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  
In [78]:
remove_negative_age_index = which(moviesDB[,35:38]<0, arr.ind = TRUE)
In [79]:
sampleView(remove_negative_age_index, seed_No = 100)
A matrix: 6 × 2 of type int
rowcol
288043
448193
306143
15943
383953
282953
In [80]:
dim(remove_negative_age_index)
  1. 96
  2. 2
In [81]:
sampleView(moviesDB[remove_negative_age_index[,1] ,35:38])
A data.frame: 6 × 4
ratingPeriodMeanratingPeriodMedianratingPeriodMinratingPeriodMax
<dbl><dbl><dbl><dbl>
18944 3.3379035823-1 6
24494 1.6830391402-1 3
1619 7.6069784717-121
28804 1.0000000001-1 3
1032 2.7833333331-122
50203.1-0.0069444440-1 0
In [82]:
length(unique(remove_negative_age_index[, 1]))
85
In [83]:
# 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, ]
}
In [84]:
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  
In [85]:
dim(moviesDB) 
  1. 54266
  2. 38
In [86]:
54351 - 85 == 54266
TRUE

Saving the Transformed Dataset to CSV¶

In [87]:
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.

In [ ]: