R-Data-Querrying-and-Visualization-movies.R

#Load libraries
library(dplyr)
library(stringr)
library(ggplot2)
library(tidyr)
library(forcats)


#----------------------------------------
#DATA PREPROCESSING

#Import and load the data from csv file
file_path <- "C:/Users/olagr/Desktop/SGH/Querrying, Data Presentation, Visualization, Reporting/netflix_list.csv"

netflix_data <- read.csv(file_path, header = TRUE, sep = ",")
#----
#View the data
# See the column names
head(netflix_data,0)

# Display a summary of the dataset
summary(netflix_data)

# Check the structure of the dataset
str(netflix_data)
#----
#Dropping some columns that will not be used in visual analysis
data_df <- netflix_data %>%
  select(-imdb_id, -plot, -summary, -image_url)


#what kind of values has isAdult - only 0, so I drop it too
unique(data_df$isAdult)

data_df <-data_df %>%
  select(-isAdult)

head(data_df,0)


#Tidy up the certificate 
unique(data_df$certificate)

# Replace values in the certificate column of data_df
replacements <- c(
  '\\(Banned\\)' = NA,
  'Not Rated' = NA,
  'Unrated' = NA,
  '7\\+' = '7',
  '12\\+' = '12',
  '13\\+' = '13',
  '15\\+' = '15',
  '16\\+' = '16',
  '18\\+' = '18',
  'U' = '0',
  'All' = '0',
  'UA' = '12',
  'PG' = '8',
  'PG-13' = '13',
  'NC-17' = '17',
  'A' = '18',
  'G' = '18',
  'R' = '17'
)

# Apply the replacements
data_df$certificate <- data_df$certificate %>%
  str_replace_all(replacements)

unique(data_df$certificate)

#Correct some replacements
replacements <- c(
  '018' = '18',
  '8-13' = '13'
)

data_df$certificate <- data_df$certificate %>%
  str_replace_all(replacements)

unique(data_df$certificate)

#delete empty string
data_df$certificate[data_df$certificate == ""] <- NA
unique(data_df$certificate)

#Change certificate from characters to factor
typeof(data_df$certificate)
data_df$certificate <- factor(data_df$certificate)
summary(data_df$certificate)
#I have 4620 NA values in certificate from 7008. This number is huge
#In visualization I can show proportion of missing values to avaiable ones and based on that do visualizations on a smaller subset.


#check runtime
typeof(data_df$runtime)
str(data_df$runtime)

data_df$runtime[data_df$runtime == "\\N"] <- NA
data_df$runtime <- as.numeric(data_df$runtime)
typeof(data_df$runtime)
summary(data_df$runtime)

summary(data_df)
str(data_df)

#clean genres
data_df$genres[data_df$genres == "\\N"] <- NA
sum(is.na(data_df$genres))

data_df <- data_df %>%
  drop_na(genres)

sum(is.na(data_df$genres))

#----------------------------------------
#Create a subset with movies only
data_df_movies_only <- data_df[data_df$type == "movie", ]

#Create a subset with series only
data_df_series_only <- data_df[data_df$type == "tvSeries", ]

# Select specific columns for the movie subset
data_movies_col_subset <- data_df_movies_only[, c("title", "orign_country", "genres", "rating", "startYear", "runtime", "certificate")]


#----------------------------------------
#DATA VISUALIZATIONS

#boxplot of number of votes
typeof(data_df$numVotes)
summary(data_df$numVotes)

unique(data_df$type)

movies_and_series <- data_df[data_df$type %in% c("movie", "tvSeries"), ]
unique(movies_and_series$type)


movie_and_series_stats <- movies_and_series %>%
  group_by(type) %>%
  summarise(
    min = min(numVotes, na.rm = TRUE),
    q1 = quantile(numVotes, 0.25, na.rm = TRUE),
    median = median(numVotes, na.rm = TRUE),
    q3 = quantile(numVotes, 0.75, na.rm = TRUE),
    max = max(numVotes, na.rm = TRUE)
  )

ggplot(movies_and_series, aes(x = type, y = numVotes)) +
  geom_boxplot() +
  scale_y_log10() +  # Apply log scale to y-axis
  labs(title = "Boxplot of Number of Votes for Movies vs TV Series",
       x = "Type",
       y = "Number of Votes (Log Scale)") +
  theme_minimal() +
  theme(
    plot.title = element_text(size = 10)  # Change the title size to 16
  ) +
  geom_text(data = movie_and_series_stats, aes(x = type, y = median, label = paste("Median: ", median)),
            color = "black", vjust = -1, size = 3) +   # Median label
  geom_text(data = movie_and_series_stats, aes(x = type, y = q1, label = paste("Q1: ", q1)),
            color = "black", vjust = 1.5, size = 3) +   # Q1 label
  geom_text(data = movie_and_series_stats, aes(x = type, y = q3, label = paste("Q3: ", q3)),
            color = "black", vjust = -0.5, size = 3) +  # Q3 label
  geom_text(data = movie_and_series_stats, aes(x = type, y = min, label = paste("Min: ", min)),
            color = "blue", vjust = 1.5, size = 2) +    # Min label
  geom_text(data = movie_and_series_stats, aes(x = type, y = max, label = paste("Max: ", max)),
            color = "blue", vjust = -1, size = 2)      # Max label 


type_counts <- table(movies_and_series$type)
type_counts["tvSeries"]
type_counts["movie"]

#----------------
#histogram of ratings
ggplot(movies_and_series, aes(x = rating)) +
  geom_histogram(fill = "orange") +
  labs(title = "Distribution of Ratings", x = "Rating", y = "Frequency")

summary(movies_and_series$rating)
sum(is.na(movies_and_series$rating))

# function to find dominant
get_numerical_mode <- function(x) {
  x <- na.omit(x)  # Remove NA values
  uniq_x <- unique(x)  # Get unique values
  uniq_x[which.max(tabulate(match(x, uniq_x)))]  # Return the most frequent value
}

dominant_value <- get_numerical_mode(movies_and_series$rating) #it is 6.7
dominant_value


#------
#number of episodes in TV Series
typeof(data_df_series_only$episodes)
summary(data_df_series_only$episodes)

summary_episodes_stats <- summary(data_df_series_only$episodes)

# Extracting specific statistics for episodes
min_value <- summary_episodes_stats["Min."]
max_value <- summary_episodes_stats["Max."]
first_quartile <- summary_episodes_stats["1st Qu."]
median_value <- summary_episodes_stats["Median"]
third_quartile <- summary_episodes_stats["3rd Qu."]
average_value <- mean(data_df_series_only$episodes, na.rm = TRUE)


ggplot(data_df_series_only, aes(x = "", y = episodes)) +
  geom_boxplot(fill = "lightpink", color = "black") + 
  geom_jitter(aes(x = "", y = episodes), width = 0.2, height = 0, alpha = 0.08, color = "black") +
  geom_text(aes(x = 1, y = min_value, label = paste("Min:", min_value)), vjust = -1, color = "black", size=3) +
  geom_text(aes(x = 1, y = max_value, label = paste("Max:", max_value)), vjust = 1.5, color = "black", size=3) +
  geom_text(aes(x = 1, y = first_quartile, label = paste("1st Qu.:", first_quartile)), vjust = 0.5, color = "black", size=3) +
  geom_text(aes(x = 1, y = median_value, label = paste("Median:", median_value)), vjust = 1, color = "black", size=3) +
  geom_text(aes(x = 1, y = third_quartile, label = paste("3rd Qu.:", third_quartile)), vjust = 1.5, color = "black", size=3) +
  geom_text(aes(x = 1, y = average_value, label = paste("Avg:", round(average_value, 2))), vjust = -1.5, color = "red", size=3) +
  scale_y_log10() +
  labs(title = "Boxplot of Episodes in TV Series",
       y = "Number of Episodes (Log Scale)",
       x = "") +
  theme_minimal()

#-----------
#pie chart of genres in movies only

genre_counts <- data_movies_col_subset %>%
  mutate(genres = strsplit(as.character(genres), ",")) %>%
  unnest(genres) %>%
  count(genres)
#----------
#halfway <- ceiling(nrow(genre_counts) / 2)
#table_part1 <- genre_counts[1:halfway, ]
#table_part2 <- genre_counts[(halfway + 1):nrow(genre_counts), ]

#table1_plot <- tableGrob(table_part1, theme = ttheme_minimal(base_size = 6, rownames = FALSE))
#table2_plot <- tableGrob(table_part2, theme = ttheme_minimal(base_size = 6, rownames = FALSE))

#grid.arrange(table1_plot, table2_plot, ncol = 2)

#----------------------
# Define labels and categorize 'Other'
selected_labels <- c("Comedy", "Drama", "Adventure", "Crime", "Romance", "Action", "Thriller", "Documentary", "Horror")
genre_counts <- genre_counts %>%
  mutate(
    category = ifelse(genres %in% selected_labels, genres, "Other")
  ) %>%
  group_by(category) %>%
  summarise(count = sum(n)) %>%
  mutate(percentage = count / sum(count) * 100)

# Prepare data for pie chart
labels <- paste(genre_counts$category, round(genre_counts$percentage, 1), "%")
values <- genre_counts$count

# Create the pie chart
pie(
  values,
  labels = labels,
  main = "Distribution of Movie Genres"
)

#----------
#bar chart - certificate

#movies only
#create a small subset with no NA
data_df_movies_only_clean <- data_df_movies_only %>%
  filter(!is.na(certificate))

sum(is.na(data_df_movies_only_clean$certificate))


data_df_movies_only_clean %>%
  mutate(certificate = fct_relevel(certificate, 
                                   "0", "7", "8", "12", "13", "16", "17", "18")) %>%
  count(certificate) %>%
  ggplot(aes(x = certificate, y = n, fill = certificate)) +  
  geom_bar(stat = "identity") +  
  xlab("Minimum age for watching the movie") +
  ylab("Count") +
  theme_minimal() +
  theme(legend.position = "none")

#-----
#movies and series 
data_df_movies_only_clean <- data_df_movies_only %>%
  filter(!is.na(certificate))

data_df_series_only_clean <- data_df_series_only %>%
  filter(!is.na(certificate))

movies_certificate_count <- data_df_movies_only_clean %>%
  count(certificate) %>%
  mutate(type = "Movies", 
         certificate = fct_relevel(certificate, 
                                   "0", "7", "8", "12", "13", "15", "16", "17", "18"))

series_certificate_count <- data_df_series_only_clean %>%
  count(certificate) %>%
  mutate(type = "Series", 
         certificate = fct_relevel(certificate, 
                                   "0", "7", "8", "12", "13", "15", "16", "17", "18"))

#Combine both counts into one data frame
combined_data <- bind_rows(movies_certificate_count, series_certificate_count)

ggplot(combined_data, aes(x = certificate, y = n, fill = type, group = type)) +
  geom_bar(stat = "identity", position = "dodge") +  
  xlab("Minimum age for watching") +
  ylab("Count") +
  theme_minimal() +  # Clean theme
  scale_fill_manual(values = c("Movies" = "skyblue", "Series" = "lightgreen"))


#--------
#correlation between NumVotes and ratings

#correlation
sum(is.na(data_df$rating))  
sum(is.na(data_df$numVotes))
data_df <- data_df[complete.cases(data_df$rating, data_df$numVotes), ]

cor.test(data_df$numVotes, data_df$rating, method = "pearson")

ggplot(data_df, aes(x = rating, y = numVotes)) +
  geom_point(alpha = 0.5, color = "maroon") +  
  geom_smooth(method = "lm", color = "black") +
  xlab("Rating") + 
  ylab("Number of Votes") +  
  ggtitle("Correlation between Rating vs Number of Votes") +  
  theme_minimal()

#--------------
#correlation between episodes and rating
sum(is.na(data_df_series_only$rating))  
sum(is.na(data_df_series_only$episodes))
typeof(data_df_series_only$episodes)


#remove outliers
top_outliers <- head(data_df_series_only_cleanepi[order(-data_df_series_only_cleanepi$episodes), ], 4)

# Remove these top outliers from the dataset
data_df_series_only_cleanepi <- data_df_series_only_cleanepi[!(data_df_series_only_cleanepi$episodes %in% top_outliers$episodes), ]

sum(is.na(data_df_series_only_cleanepi$rating))  
sum(is.na(data_df_series_only_cleanepi$episodes))

cor.test(data_df_series_only_cleanepi$episodes, data_df_series_only_cleanepi$rating, method = "pearson")


ggplot(data_df_series_only_cleanepi, aes(x = rating, y = episodes)) +
  geom_point(alpha = 0.5, color = "purple") +  
  geom_smooth(method = "lm", color = "black") +
  xlab("Rating") + 
  ylab("Number of Episodes") +  
  ggtitle("Correlation between Rating vs Number of Epiosodes") +  
  theme_minimal()


#-------------
#number of votes rating for paticular genres in the whole dataset
data_df_genres <- data_df %>%
  mutate(genres = strsplit(as.character(genres), ",")) %>% 
  unnest(genres) %>% 
  filter(genres %in% c("Crime", "Comedy", "Adventure", "Action", "Thriller", "Romance", "Drama", "Documentary"))

ggplot(data_df_genres, aes(x = rating, fill = genres)) +
  geom_histogram(binwidth = 0.5, position = "stack", alpha = 0.8, color = "black") +
  scale_fill_manual(values = c("lightblue", "tan", "plum", "purple", "pink", "orange", "maroon", "lightgreen")) +
  xlab("Average Rating") +
  ylab("Number of Votes") +
  ggtitle("Distribution of Ratings by Genre") +
  theme_minimal() +
  theme(legend.title = element_blank())


# Bar chart of average ratings by genre
average_rating_per_genre <- data_df_genres %>%
  group_by(genres) %>%
  summarize(average_rating = mean(rating, na.rm = TRUE), .groups = 'drop')

print(average_rating_per_genre)

ggplot(avrage_rating_per_genre, aes(x = reorder(genres, average_rating), y = average_rating, fill = genres)) +
  geom_bar(stat = "identity", alpha = 0.8, color = "black") +
  scale_fill_manual(values = c("lightblue", "tan", "plum", "purple", "pink", "orange", "maroon", "lightgreen")) +
  xlab("Genre") +
  ylab("Average Rating") +
  ggtitle("Average Rating by Genre") +
  theme_minimal() +
  theme(legend.position = "none")

#-----------------
movies_and_series_genres <- movies_and_series %>%
  mutate(genres = strsplit(as.character(genres), ",")) %>% 
  unnest(genres) %>% 
  filter(genres %in% c("Crime", "Comedy", "Adventure", "Action", "Thriller", "Romance", "Drama", "Documentary"))

ggplot(movies_and_series_genres, aes(x = rating, fill = genres)) +
  geom_histogram(binwidth = 0.5, position = "stack", alpha = 0.8, color = "black") +
  scale_fill_manual(values = c("lightblue", "tan", "plum", "purple", "pink", "orange", "maroon", "lightgreen")) +
  xlab("Rating") +
  ylab("Number of Votes") +
  ggtitle("Distribution of Ratings by Genre") +
  theme_minimal() +
  theme(legend.title = element_blank())


# Bar chart of average ratings by genre
average_rating_per_genre <- movies_and_series_genres %>%
  group_by(genres) %>%
  summarize(average_rating = mean(rating, na.rm = TRUE), .groups = 'drop')

print(average_rating_per_genre)

ggplot(average_rating_per_genre, aes(x = reorder(genres, average_rating), y = average_rating, fill = genres)) +
  geom_bar(stat = "identity", alpha = 0.8, color = "black") +
  scale_fill_manual(values = c("lightblue", "tan", "plum", "purple", "pink", "orange", "maroon", "lightgreen")) +
  xlab("Genre") +
  ylab("Average Rating") +
  ggtitle("Average Rating by Genre") +
  theme_minimal() +
  theme(legend.position = "none")

genre_counts <- movies_and_series_genres %>%
  count(genres, name = "count") %>%  
  arrange(desc(count))

print(genre_counts)
#------

sum(is.na(movies_genres$startYear))
sum(is.na(movies_genres$runtime))

data_movies_filtered <- movies_genres %>%
  filter(!is.na(runtime) & !is.na(startYear) & runtime >= 60 & runtime <=200)

sum(is.na(data_movies_filtered$startYear))
sum(is.na(data_movies_filtered$runtime)) #both are 0 now

#add column with colors
data_movies_filtered <- data_movies_filtered %>%
  mutate(color = case_when(
    grepl("Drama", genres) ~ "maroon",
    grepl("Romance", genres) ~ "lightblue",
    grepl("Comedy", genres) ~ "lightgreen",
    TRUE ~ "black"
  ))

cor.test(data_movies_filtered$runtime, data_movies_filtered$startYear, method = "pearson")
#no correlation

# Create the scatter plot
ggplot(data_movies_filtered, aes(x = startYear, y = runtime, color = color)) +
  geom_point() +
  scale_color_identity() +
  labs(
    title = "Movie Runtime by Release Year",
    x = "Release Year",
    y = "Runtime (minutes)"
  ) +
  theme_minimal()

#----------
#heatmap
correlation_data <- data_df_movies_only[, c("rating", "startYear", "runtime", "numVotes")]

correlation_data <- na.omit(correlation_data)

cor_matrix <- cor(correlation_data, use = "complete.obs")

# Convert the correlation matrix to a data frame for ggplot2
cor_matrix_df <- as.data.frame(as.table(cor_matrix))

ggplot(cor_matrix_df, aes(x = Var1, y = Var2, fill = Freq)) +
  geom_tile() +
  scale_fill_gradient2(low = "blue", mid = "white", high = "red", midpoint = 0) +
  labs(title = "Heatmap of Correlations Between Movie Properties", 
       x = "Movie Properties", 
       y = "Movie Properties", 
       fill = "Correlation") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))


#-------------
#correlation between runtime and rating

sum(is.na(data_df_movies_only$rating))  
sum(is.na(data_df_movies_only$runtime)) #not many, can be removed

cor.test(data_df_movies_only$runtime, data_df_movies_only$rating, method = "pearson")


ggplot(data_df_movies_only, aes(x = rating, y = runtime)) +
  geom_point(alpha = 0.5, color = "orange") +  
  geom_smooth(method = "lm", color = "black") +
  xlab("Rating") + 
  ylab("Duration of the Movie") +  
  ggtitle("Correlation between Rating vs Duration") +  
  theme_minimal()