Lab 02 607

#Part 1: Build Table • Choose six recent popular movies. #I choose following movies: Barbie, Spider Man, Mission Impossible, Super Mario, Garden of Galaxy

• Ask at least five people that you know (friends, family, classmates, imaginary friends if necessary) to rate each of these movies that they have seen on a scale of 1 to 5.

#Part 2: Store data in SQL database Take the results (observations) and store them in the class MySQL database: - Server name: cunydata607sql.mysql.database.azure.com - Username / password: will be given to you in an email Note: it is good practice to change your password. To do so, use this SQL command: SET PASSWORD = ‘’;

#Part 3: Transfer data from SQL database to R dataframe Load the information from the SQL database into an R dataframe.

#Part 4: Missing data strategy Implement an approach to missing data Explain why you decided to take the chosen approach: I chooose to replace null with 0 first because my rating is from 1-5 after that we replaced 0 with mean of the column which is mean imputation.

print(movie_data)

##       Name Barbie Movie Spider man Mission Impossible Super Mario
## 1  Harmain            4          5                  2           0
## 2   Hayyan            0          5                  4           5
## 3    Hifza            3          4                  0           4
## 4 Hoorain             4          2                  5           3
## 5   Ishraa            4          0                  3           4
##   Garden of Galaxy
## 1                4
## 2                0
## 3                4
## 4                4
## 5                4

# Ensure that all columns (except 'Name') are numeric
for (col in names(movie_data)[-1]) {  # Skip the 'Name' column
  # Convert the column to numeric if it's not already
  movie_data[[col]] <- as.numeric(movie_data[[col]])
  
  # Calculate the mean of the column, ignoring 0s in the mean calculation
  column_mean <- mean(movie_data[[col]][movie_data[[col]] != 0], na.rm = TRUE)
  
  # Round the mean to the nearest whole number
  rounded_mean <- round(column_mean)
  
  # Replace 0s with the rounded column mean
  movie_data[[col]][movie_data[[col]] == 0] <- rounded_mean
}

# Check the modified data
print(movie_data)

##       Name Barbie Movie Spider man Mission Impossible Super Mario
## 1  Harmain            4          5                  2           4
## 2   Hayyan            4          5                  4           5
## 3    Hifza            3          4                  4           4
## 4 Hoorain             4          2                  5           3
## 5   Ishraa            4          4                  3           4
##   Garden of Galaxy
## 1                4
## 2                4
## 3                4
## 4                4
## 5                4