<jupyter_start><jupyter_text>SLU2 - Subsetting data: Exercise notebookIn this notebook you'll practice the concepts you've seen on the Learning and example notebooks:    - Setting pandas Dataframe index    - Selecting columns with brackets notation    - Selecting columns with dot notation    - Selecting rows with loc     - Selecting rows with iloc    - Multi-axis indexing (as an alternative to Chain indexing)     - Masks    - Where    - Subsetting on conditions    - Removing and Adding columns**Note**: For these exercices we will be using a netflix dataset containing the description and ratings of several movies and tv shows. In each exercise, you'll be asked to implement a function, which you can check against the assertions provided.Let's dive right in.<jupyter_code>import pandas as pd
import numpy as np
import math
pd.options.display.max_rows = 10<jupyter_output><empty_output><jupyter_text>Welcome to NetflixYou just succeded in getting a job for Netflix. Wow, congratz! Granted, it's mostly a tech costumer support job, but you feel it will allow you to go up inside the company. It's your first day on the job and you got inducted, they gave you a few tips, access to the data you need to find people's problems and showed you to your space. You're now settling in and getting ready for all of those password reset requests that are bound to come in:![tech-cs](./images/tech_cs.jpg)You have a couple of minutes before they activate your line, so you start to explore a little bit. While looking into the data you have access to, you stumble upon a collection of the available movies and tv shows on the platform and you decide to take a look:<jupyter_code># Read netflix shows dataset and set show id column as index
netflix_shows = pd.read_csv('data/netflix_titles.csv',index_col='show_id').sort_index()

# Show first 5 lines
netflix_shows.head(5)<jupyter_output><empty_output><jupyter_text>Looks like an interesting dataset, so you remember to put in practice what you're learning in that data science academy you took (yikes, maybe you should have payed more attention to it 😅). Exercise 1 - Exploring the data Exercise 1.1 - Selecting rowsYou want to retrieve the the **39th**, **42nd**, the **512th** and the **2019th** rows. Implement the function `select_rows` so that given a set of ids, it retrieves a dataframe with the corresponding rows:<jupyter_code>def select_rows_by_id(df, ids):
    """ 
    Select the desired rows
    
    Args:
        df (pd.DataFrame): the input DataFrame
        rows: list with the desired rows indices

    Returns:p
        (pd.DataFrame): subset Dataframe

    """
    
    # YOUR CODE HERE
    raise NotImplementedError()
# This cell is what will test your code, please ignore it!

netflix_shows = pd.read_csv('data/netflix_titles.csv', index_col='show_id').sort_index()
df_rows = select_rows_by_id(netflix_shows, [42, 39, 512, 2019])
df_rows = df_rows.sort_index()

assert isinstance(df_rows, pd.DataFrame)
assert df_rows.shape[0] == 4
assert df_rows.shape[1] == netflix_shows.shape[1]
assert df_rows.country.unique()[0] == 'United States'
assert np.mean(df_rows.release_year) == 2000.25<jupyter_output><empty_output><jupyter_text>Exercise 1.2 - Selecting rows by titleYou now want to change the index of your data to the country and retrieve all movies for which the country of origin is either the US or UK. Implement the function `select_rows_by_country` so that given a set of countries, it retrieves a dataframe with the corresponding rows, where the index corresponds to the `country` column.**Hint**: Keep in mind that we don't want to discard the `show_id`'s, this is still useful information about the shows! Additionally, make sure the `country` column is not "duplicated"<jupyter_code>def select_rows_by_country(df, countries):
    """ 
    Select the desired rows given the country names
    
    Args:
        df (pd.DataFrame): the input DataFrame, with an available column `country`
        rows: list with the desired countries

    Returns:
        (pd.DataFrame): subset Dataframe

    """
    
    # YOUR CODE HERE
    raise NotImplementedError()
# This cell is what will test your code, please ignore it!

netflix_shows = pd.read_csv('data/netflix_titles.csv', index_col='show_id').sort_index()
desired_countries = ['United States', 'United Kingdom']
df_rows = select_rows_by_country(netflix_shows, desired_countries)

assert isinstance(df_rows, pd.DataFrame)
assert df_rows.shape[0] == 2380
assert df_rows.shape[1] == netflix_shows.shape[1]
assert df_rows.index.name == 'country'

assert 'show_id' in list(df_rows.columns)
assert list(df_rows.index.unique()) == desired_countries
assert math.isclose(np.mean(df_rows.release_year), 2012.914705882353)<jupyter_output><empty_output><jupyter_text>Exercise 1.3 - Selecting columnsTo retrieve the **title** and **description** columns, implement the function `select_columns`:<jupyter_code>def select_columns(df, columns):
    """ 
    Select the desired columns of a dataframe
    
    Args:
        df (pd.DataFrame): the input DataFrame
        columns: list with columns to retrieve

    Returns:
        (pd.DataFrame): subset Dataframe

    """
    
    # YOUR CODE HERE
    raise NotImplementedError()
# This cell is what will test your code, please ignore it!

netflix_shows = pd.read_csv('data/netflix_titles.csv', index_col='show_id').sort_index()

desired_columns = ['title']
df_columns = select_columns(netflix_shows, desired_columns)
assert isinstance(df_columns, pd.DataFrame)
assert df_columns.shape[0] == netflix_shows.shape[0]
assert df_columns.shape[1] == 1
assert df_columns.columns.tolist() == desired_columns

desired_columns = ['title', 'description']
df_columns = select_columns(netflix_shows, desired_columns)
assert isinstance(df_columns, pd.DataFrame)
assert df_columns.shape[0] == netflix_shows.shape[0]
assert df_columns.shape[1] == 2
assert df_columns.columns.tolist() == desired_columns<jupyter_output><empty_output><jupyter_text>Exercise 1.4 - Selecting rows and columnsLet's combine what you've learned above and retrieve all the show ids between **22040803** and **80175275** together with each movie's __title__, __description__ and __release_year__. Implement it under the function below:<jupyter_code>def dedicated_subset(df):
    """ 
    Select columns title, description and release year for show ids 22040803 to 80175275
    
    Args:
        df (pd.DataFrame): the input DataFrame

    Returns:
        (pd.DataFrame): subsetted df

    """
    
    # YOUR CODE HERE
    raise NotImplementedError()
# This cell is what will test your code, please ignore it!

netflix_shows = pd.read_csv('data/netflix_titles.csv', index_col='show_id').sort_index()
df_out = dedicated_subset(netflix_shows)

assert isinstance(df_out, pd.DataFrame)
assert df_out.shape[0] == 3220
assert df_out.shape[1] == 3
assert df_out.columns.tolist() == ['title', 'description', 'release_year']
assert math.isclose(np.mean(df_out.release_year), 2011.3403726708075)<jupyter_output><empty_output><jupyter_text>Exercise 1.5 - Hiding dataNow we're going to be hiding some of the rows from our dataset. Exercise 1.5.1 Start by hiding all TV shows.<jupyter_code>def hide_tv_shows(df):
    """ 
    Hide all TV shows in dataframe
    
    Args:
        df (pd.DataFrame): the input DataFrame

    Returns:
        (pd.DataFrame): output DataFrame

    """
    
    # YOUR CODE HERE
    raise NotImplementedError()
# This cell is what will test your code, please ignore it!

netflix_shows = pd.read_csv('data/netflix_titles.csv', index_col='show_id').sort_index()
df_out = hide_tv_shows(netflix_shows)

assert isinstance(df_out, pd.DataFrame)
assert df_out.shape[0] == netflix_shows.shape[0]
assert df_out.shape[1] == netflix_shows.shape[1]
assert sum(df_out.type=='TV Show') == 0<jupyter_output><empty_output><jupyter_text>Exercise 1.5.2 Now reverse it and leave only TV shows.<jupyter_code>def show_tv_shows(df):
    """ 
    Show only TV shows in dataframe
    
    Args:
        df (pd.DataFrame): the input DataFrame

    Returns:
        (pd.DataFrame): output DataFrame

    """
    
    # YOUR CODE HERE
    raise NotImplementedError()
# This cell is what will test your code, please ignore it!
netflix_shows = pd.read_csv('data/netflix_titles.csv', index_col='show_id').sort_index()
df_out = show_tv_shows(netflix_shows)

assert isinstance(df_out, pd.DataFrame)
assert df_out.shape[0] == netflix_shows.shape[0]
assert df_out.shape[1] == netflix_shows.shape[1]
assert sum(df_out.dropna().type!='TV Show') == 0<jupyter_output><empty_output><jupyter_text>Oops, phone's ringing, time to go start working. Drop your playing and pick up the phone... Exercise 2 - _A damsel in distress_A few calls in, you answer the phone to this old lady. Her problem doesn't seem very technical, but she sounds sweet and you're eager to help and do well on your first day, so you listen to her problem anyways. Well, at least until you get another call.![old_lady](images/old_lady_netflix.jpg)Mrs. Ferguson (let's call her that) wants help in knowing which movies she can show her grandkids. They're coming over and she wanted to do a **movie** session, but her daughter was the one setting up netflix and she has no idea how to use it, except to search for specific movies. And the suggestions that she's getting for popular movies seem everything but appropriate. You think for a bit and decided it shouldn't be too hard and you decide to help her. You ask her just a couple of questions and decide the following:* Her grandkids are aged 5 and 8* They don't yet read subtitles, so obviously they need to be English* Recent movies are preferred (after 2015)* Short movies are preferred (less than 1h20)* No documentaries - she wants her grandkids to like her!You also pull some info on the ratings of movies and find out the following:|Rating | Description ||-------|-------------|| G | General exhibition, suitable for all ages but not necessarily intended for children || NC-17 | Not suitable for children under 17 || NR | Not rated || PG | Some material may not be suitable for children. || PG-13| Not suitable for children under 13 || R | Not suitable for children under 17  || TV-14 | This program contains some material that many parents would find unsuitable for children under 14 years of age || TV-G  | Most parents would find this program suitable for all ages. || TV-MA| This program is intended to be viewed by adults and therefore may be unsuitable for children under 17 || TV-PG | Parental guidance is recommendedes - these programs may be unsuitable for younger children || TV-Y  | This program is aimed at a very young audience, including children from ages 2–6 || TV-Y7 | This program is designed for children age 7 and above. || TV-Y7-FV | Similar to TV-Y7, but may contain fantasy violence and generally be more intense || UR | Unrated |You'll use this list to make sure the list you provide her is appropriate (assume films without rating are possibly problematic). First, you notice the duration provided is a string, and when it refers to minutes seems to allways follow the pattern `Xmin`, so you design a function to extract it as a number and apply it to your dataframe:<jupyter_code>from utils import add_column_duration_int

netflix_shows = pd.read_csv('data/netflix_titles.csv', index_col='show_id').sort_index()
netflix_shows = add_column_duration_int(netflix_shows)
netflix_shows.head()<jupyter_output><empty_output><jupyter_text>Now implement below the final code to return the suggested list of movies for Mrs. Ferguson. **Use the previous function to add a readable duration column** and return the following columns: `title`, `description`, `duration_int`, and `release_year` columns.**Hint**: Look into the function `.isin` described in the learning notebook and how it can be used to check a value against a list<jupyter_code>def mrs_ferguson_list(df):
    """ 
    Show netflix shows according to mrs ferguson parameters:
    
      - only movies
      - from english-speaking countries (US and UK)
      - appropriate for ages 5 and 8
      - from or after 2015
      - at most 80 min of duration
      - no documentaries
      
    Return only titles, description, duration_int and release_year
    
    Args:
        df (pd.DataFrame): the input DataFrame

    Returns:
        (pd.DataFrame): subsetted df

    """
    
    # YOUR CODE HERE
    raise NotImplementedError()
# This cell is what will test your code, please ignore it!

netflix_shows = pd.read_csv('data/netflix_titles.csv', index_col='show_id').sort_index()
netflix_shows = add_column_duration_int(netflix_shows)
df_out = mrs_ferguson_list(netflix_shows)

assert df_out.shape[0] == 19
assert df_out.shape[1] == 4
assert df_out.columns.tolist() == ['title', 'description', 'duration_int', 'release_year']
assert math.isclose(np.mean(df_out.duration_int), 53.63157894736842)<jupyter_output><empty_output><jupyter_text>You provide a couple of suggestions to Mrs. Ferguson and she says goodbye happily. As you hang up you see a signal that you have another call. Just in time!  Exercise 3 - Oh no, not again After a few more calls, and almost ready to clock out, you receive another call from another old lady. Turns out Mrs. Fergunson mentioned your help to her friends and one of them decided to also call. Mrs. Williams wants to get the oldest movies from the 70s she can find on Netflix. She vaguely remembers her favorites were from that time. Start by filtering all english-speaking movies from the 70s (1970-1979). Return the title, description, duration and release year. Use release year as the index and return the results ordered. Keep the `release_year` column in your final result.**Hint**: the sorting by year should follow the natural order, this is, an ascending order<jupyter_code>def mrs_williams_list(df):
    """ 
    Show netflix shows according to mrs ferguson parameters:
    
      - only movies
      - from english-speaking countries (US and UK)
      - between and including 1970 and 1979 

    Return only titles, description, duration and release_year

    Args:
        df (pd.DataFrame): the input DataFrame

    Returns:
        (pd.DataFrame): subsetted df

    """
    
    # YOUR CODE HERE
    raise NotImplementedError()
# This cell is what will test your code, please ignore it!

netflix_shows = pd.read_csv('data/netflix_titles.csv', index_col='show_id').sort_index()
df_out = mrs_williams_list(netflix_shows)

assert df_out.shape[0] == 21
assert df_out.shape[1] == 4
assert df_out.index.name == 'release_year'
assert df_out.columns.tolist() == ['title', 'description', 'duration', 'release_year']

assert df_out.iloc[0].release_year == 1972
assert df_out.iloc[-1].release_year == 1979
assert math.isclose(np.mean(df_out.release_year), 1975.4761904761904)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08353543519267044, 'fraction_numerical': 0.017919698194556723, 'mean_word_length': 4.408014571948998, 'pattern_counts': {'":': 0, '<': 45, '<?xml version=': 0, '>': 45, 'https://': 0, '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': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '376892', 'license': 'MIT', 'n_tokens_mistral': 4563, 'n_tokens_neox': 4248, 'n_words': 1713, 'path': 'S01 - Bootcamp and Binary Classification/SLU02 - Subsetting Data in Pandas/Exercise notebook.ipynb', 'repo_name': 'LDSSA/batch4-students', 'size': '14844'}