<jupyter_start><jupyter_text>pandas Exercise Notebook Solutions  Exercise Notebook Instructions 1. Important: Only modify the cells which instruct you to modify them - leave "do not modify" cells alone.  The code which tests your responses assumes you have run the startup/read-only code exactly. 2. Work through the notebook in order.Some of the steps depend on previous, so you'll want to move through the notebook in order. 3. It is okay to use libraries.You may find some questions are fairly straightforward to answer using built-in library functions.  That's totally okay - part of the point of these exercises is to familiarize you with the commonly used functions. 4. Seek help if stuckIf you get stuck, don't worry!  You can either review the videos/notebooks from this week, ask in the course forums, or look to the solutions for the correct answer.  BUT, be careful about looking to the solutions too quickly.  Struggling to get the right answer is an important part of the learning process.<jupyter_code>import pandas as pd
import numpy as np
import os.path<jupyter_output><empty_output><jupyter_text>In this exercise notebook you will have the opportunity to load the MovieLens database and perform additional analysis.First let's load the data into a Pandas Dataframe:<jupyter_code># DO NOT MODIFY

# set here the relative path to the movielens folder
MOVIELENS="../Week-4-Pandas/data/"

movies = pd.read_csv(os.path.join(MOVIELENS, 'movies.csv'), sep=',')
ratings = pd.read_csv(os.path.join(MOVIELENS, 'ratings.csv'), sep=',')
movies.head()
ratings.head()<jupyter_output><empty_output><jupyter_text>Exercise 1: Find the Minimum RatingLet's start by computing the minimum rating.In the next cell, define the `min_rating` variable to be the minimum rating across all of the DataFrame:<jupyter_code>min_rating = None
### BEGIN SOLUTION
min_rating = ratings.rating.min()
### END SOLUTION
# DO NOT MODIFY

assert isinstance(min_rating, np.float64), "Try again, make sure you are taking the min of just 1 column"
assert abs(min_rating - 0.5) < .01, "Try again, the minimum should be 0.5"<jupyter_output><empty_output><jupyter_text>Exercise 2: Find the Mean Rating of a MovieThe movie **Toy Story** has `movieId` 1. Find out the mean rating for Toy Story. For this exercise you just need to use the `ratings` DataFrame:<jupyter_code>toy_story_rating = None
### BEGIN SOLUTION
toy_story_rating = ratings[ratings.movieId == 1].rating.mean()
### END SOLUTION
assert abs(toy_story_rating - 3.92) < 0.01, "Try again, select only the rows where the movieId is equal to 1"<jupyter_output><empty_output><jupyter_text>Exercise 3: Find the Most Common RatingNext, find which rating is the most common.<jupyter_code>rating_counts = None
### BEGIN SOLUTION
rating_counts = ratings.rating.value_counts()
### END SOLUTION
# DO NOT MODIFY

assert rating_counts.iloc[:1].index[0] == 4., "Print out rating_counts and try to understand what is wrong"<jupyter_output><empty_output><jupyter_text>It is also interesting to inspect step by step what the statement above is doing:    rating_counts.iloc[:1].index[0]print all the intermediate stages of this expression, read the documentation of the functions used here.  Exercise 4: Usage of the Index in pandasIn `numpy` the way to point to a specific entry in an array is by using its integer position. In `pandas` you can do the same with `iloc`, but you also have the option of defining a column in a DataFrame as an `index` and refer to rows using `index` labels instead of integer locations using `loc`.For example in `rating_counts` defined above, the rating is the `index`, so we can reference a value either by its position or by its rating.For example we can reference the 4th record either with `iloc` and its position or with `loc` and its label:<jupyter_code>rating_counts.iloc[3]
rating_counts.loc[3.5]<jupyter_output><empty_output><jupyter_text>Exercise 5: Set an Index for the Movies DataFrame Movies have one column `movieId` that is a natural way of uniquely identifying each row, when that is the case, it is useful to turn that into an index.<jupyter_code>movies = movies.set_index("movieId")
ratings.head()<jupyter_output><empty_output><jupyter_text>Ratings instead do not have a row identifier, both `userId` and `movieId` reference records in other dataframes, therefore there is no good candidate for an index so we can just leave the default integer indexing.  Exercise 6: Year with Maximum Standard Deviation in the RatingFirst assignment is to find which year has the maximum standard deviation in the rating, **not** the maximum value of the standard deviation, but the year when it occurs.You can use the `idxmax` method, look at its documentation on the pandas website, https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.idxmax.html First we want to convert the timestamp into a datetime object:<jupyter_code># DO NOT MODIFY

ratings['parsed_time'] = pd.to_datetime(ratings['timestamp'], unit='s')<jupyter_output><empty_output><jupyter_text>We can then access to datetime related fields through `dt`, for example:<jupyter_code>ratings.parsed_time.dt.month.head()
def find_year_with_max_std(ratings):
    """Function to find the year with the larger standard deviation in rating"""
    ### BEGIN SOLUTION
    return ratings.rating.groupby(ratings.parsed_time.dt.year).std().idxmax()
    ### END SOLUTION
assert find_year_with_max_std(ratings) == 1998, "Wrong year identified, try again!"<jupyter_output><empty_output><jupyter_text>Advanced Exercise 1: Identify Popular Movies*In the rest of the notebook, we will introduce new concepts not covered in class, this will challenge you to read additional pandas documentation.*First we would like only to consider movies that have a significant number of ratings. This task is complicated by the fact that movies and ratings are in 2 different DataFrames and we want to filter the `movies` DataFrame based on a statistics on the `ratings` DataFrame.First let's compute the number of ratings per movie:<jupyter_code>number_of_ratings = ratings.movieId.value_counts()
number_of_ratings.head()<jupyter_output><empty_output><jupyter_text>Now we want to filter this pandas Series object and keep only the rows where the count is larger or equal to 100.We don't want to pollute our analysis with movies with a tiny number of reviews:<jupyter_code>number_of_ratings_of_popular_movies = None
### BEGIN SOLUTION
number_of_ratings_of_popular_movies = number_of_ratings[number_of_ratings >= 100]
### END SOLUTION
assert len(number_of_ratings_of_popular_movies) == 8546, "Try again, check that movies with 100 ratings are accepted"<jupyter_output><empty_output><jupyter_text>Finally we want to use the `reindex` function to change the index of movies, this will create a new DataFrame with a new index that contains the movieId of only the most popular movies.The value of all the rows of `movies` that have the same movieId will be copied over to the new `popular_movies` dataset, the rest will be discarded.<jupyter_code>all_popular_movies = movies.reindex(number_of_ratings_of_popular_movies)<jupyter_output><empty_output><jupyter_text>Advanced Exercise 2: Data CleaningEverytime we perform a reindexing operation, `pandas` will create a row for every value of the new index, even if it doesn't exist in the original data structure, and it will mark those data as missing with `NaN` (Not A Number).Always check if reindexing generated invalid data:<jupyter_code>all_popular_movies.isnull().sum()<jupyter_output><empty_output><jupyter_text>In the next cell we want to drop the invalid data, look for a `pandas` function that performs that operation (it starts with "drop"!).<jupyter_code>popular_movies = None
### BEGIN SOLUTION
popular_movies = all_popular_movies.dropna()
### END SOLUTION
assert len(popular_movies) == 7847, "Try again, check the documentation of the function you used"<jupyter_output><empty_output><jupyter_text>Advanced Exercise 3: Filter by GenreLet's implement a general function that filters movies by genres:<jupyter_code>def filter_by_genre(input_movies, genre):
    """Return only movies of a specific genre"""
    ### BEGIN SOLUTION
    return input_movies[input_movies.genres.str.contains(genre)]
    ### END SOLUTION<jupyter_output><empty_output><jupyter_text>Then let's apply it to the `popular_movies` dataset to retain only the "Fantasy" movies:<jupyter_code>fantasy_movies = filter_by_genre(popular_movies, "Fantasy")
assert len(fantasy_movies) == 382, """Try again, Make sure you are filtering the popular movies"""<jupyter_output><empty_output><jupyter_text>Advanced Exercise 4: Join Movies and RatingsLet's create a single `DataFrame` that contains both titles and mean ratings of the popular fantasy movies.Titles are only available in the `movies` `DataFrame`, while ratings in the `ratings` `DataFrame`, we would like to create a single DataFrame that includes Title. Create the `mean_ratings` variable by computing the mean rating for each movie:<jupyter_code>mean_ratings = None
### BEGIN SOLUTION
mean_ratings = ratings.rating.groupby(ratings.movieId).mean()
### END SOLUTION<jupyter_output><empty_output><jupyter_text>In this case we don't even need to use a join operation, we can just create a new column in the `fantasy_movies` DataFrame. This will automatically match the index of `mean_ratings` with the index of `fantasy_movies` and attach to each movie its rating. Ratings for movies that are not in the `fantasy_movies` DataFrame are discarded.The recommended way of creating columns in the recent versions of `pandas` is through the `assign` function, read its documentation!<jupyter_code>fantasy_movies.assign?
fantasy_movies_with_ratings = fantasy_movies.assign(rating = mean_ratings)
fantasy_movies_with_ratings.head()
assert fantasy_movies_with_ratings.loc[7842].title.startswith("Dune"), "Try again, missing or wrong title"
assert abs(fantasy_movies_with_ratings.loc[7842].rating - 3.56) < 0.01 , "Try again, missing or wrong rating"<jupyter_output><empty_output><jupyter_text>Advanced Exercise 5: Find the Highest Rated Fantasy MovieAgain we need to find the index where a column is max, in this case rating:<jupyter_code>index_of_max_rating = None
### BEGIN SOLUTION
index_of_max_rating = fantasy_movies_with_ratings.rating.idxmax()
### END SOLUTION
highest_rated_fantasy_movie = fantasy_movies_with_ratings.loc[index_of_max_rating]
assert highest_rated_fantasy_movie.title.startswith("Princess"), "Try again"<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07423954372623574, 'fraction_numerical': 0.00779467680608365, 'mean_word_length': 6.056338028169014, 'pattern_counts': {'":': 0, '<': 80, '<?xml version=': 0, '>': 78, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '19', 'dir': 'jupyter-structured-clean-dedup', 'id': '105640', 'license': 'Apache-2.0', 'n_tokens_mistral': 3219, 'n_tokens_neox': 3041, 'n_words': 1240, 'path': 'Week 4/Week-4-Exercises-2/Solutions/ex04_1_pandas-SOLUTIONS.ipynb', 'repo_name': 'innat-2k14/Data-Science-In-Python', 'size': '10520'}