<jupyter_start><jupyter_text>Subsetting data: Exercise notebookIn this notebook you'll practice the following:- Setting pandas Dataframe index- Selecting columns (brackets and dot notation)- Selecting rows (loc and iloc)- Subsetting on conditions- Removing and Adding columns<jupyter_code>import pandas as pd<jupyter_output><empty_output><jupyter_text>For these exercices we will be using a zomato dataset containing the description and ratings of several restaurants.In each exercise, you'll be asked to implement a function. In order to test it before you submit the assignement, add a new cell and call the function to inspect it's output.<jupyter_code># Read restaurants dataset and set restaurant name column as index
data_path = 'https://raw.githubusercontent.com/vohcolab/PandaViz-Workshop/main/Pandas/Pandas%20Advanced/data/U.S._Chronic_Disease_Indicators.csv'
# this path would also work if you are running jupyter locally on this repository
# data_path = 'data/U.S._Chronic_Disease_Indicators.csv'

indicators = pd.read_csv(data_path, index_col='indicator_id')

# Show first 5 lines
print(indicators.shape)
indicators.head(5)<jupyter_output><empty_output><jupyter_text>Exercise 1Selecting columnsSelect the column __*Topic*__.<jupyter_code>def exercise_1(df):
    """ 
    Select the column Topic of the DataFrame
    
    Args:
        df (pd.DataFrame): the input DataFrame

    Returns:
        (pd.Series): Topic column

    """
    
    # YOUR CODE HERE
    
    return df['Topic']
    #raise NotImplementedError()
    
# This cell is what will test your code, please ignore it!
df_test = exercise_1(indicators)
assert isinstance(df_test, pd.Series)
assert df_test.name == 'Topic'
assert df_test.shape[0] == indicators.shape[0]
#pd.testing.assert_series_equal(df_test, df_true)<jupyter_output><empty_output><jupyter_text>Exercise 2Selecting columns.Select columns __*LocationDesc*__ and __*Question*__.<jupyter_code>def exercise_2(df):
    """ 
    Select columns LocationDesc and Question
    
    Args:
        df (pd.DataFrame): the input DataFrame

    Returns:
        (pd.DataFrame): LocationDesc and Question columns

    """
    # YOUR CODE HERE
    columns = ['LocationDesc', 'Question']
    return df[columns]
    #raise NotImplementedError()
# This cell is what will test your code, please ignore it!
df_test = exercise_2(indicators)
assert isinstance(exercise_2(indicators), pd.DataFrame)
assert df_test.columns.tolist() == ['LocationDesc', 'Question']
assert df_test.shape[0] == indicators.shape[0]<jupyter_output><empty_output><jupyter_text>Exercise 3Selecting rows.Select the **78th**, the **156th** and the **390th** rows.<jupyter_code>def exercise_3(df):
    """ 
    Select the 78, the 156 and the 390 rows
    
    Args:
        df (pd.DataFrame): the input DataFrame

    Returns:
        (pd.DataFrame): subsetted df

    """
    
    indices = [78,156,390]
    return df.iloc[indices,:]
    # YOUR CODE HERE
    #raise NotImplementedError()
# This cell is what will test your code, please ignore it!
df_test = exercise_3(indicators)
assert isinstance(df_test, pd.DataFrame)
assert df_test.shape[1] == indicators.shape[1]
assert df_test.index.tolist() == [9152, 9115, 1215]<jupyter_output><empty_output><jupyter_text>Exercise 4Selecting rows and columnsSelect columns __*Question*__, __*DataValueUnit*__, and __*DataValue*__,  for indicators whose **id** is __1143__ or __1910__.<jupyter_code>def exercise_4(df):
    """ 
    Select columns aggregate_rating and restaurant name for rooms 8202867 and 16553285
    
    Args:
        df (pd.DataFrame): the input DataFrame

    Returns:
        (pd.DataFrame): subsetted df

    """
    
    return df.loc[[1143,1910], ['Question', 'DataValueUnit', 'DataValue']]
    # YOUR CODE HERE
    #raise NotImplementedError()
# This cell is what will test your code, please ignore it!
df_test = exercise_4(indicators)
assert isinstance(df_test, pd.DataFrame)
assert df_test.index.tolist() == [1143, 1910]
assert df_test.columns.tolist() == ['Question','DataValueUnit', 'DataValue']<jupyter_output><empty_output><jupyter_text>Exercise 5Using slice operation.Use the slice operation to pick the **indicators** rows between **2100** and **3100**.<jupyter_code>def exercise_5(df):
    """ 
    Use the slice operation to pick all indicators whose rows are between 2100 (including) and 3100 (excluding).
    
    Args:
        df (pd.DataFrame): the input DataFrame

    Returns:
        (pd.DataFrame): df subset of rows

    """
    
    return df.iloc[2100:3100]
    # YOUR CODE HERE
    #raise NotImplementedError()
# This cell is what will test your code, please ignore it!
df_test = exercise_5(indicators)
assert isinstance(df_test, pd.DataFrame)
assert df_test.shape == (1000,7)
assert df_test.index[-1] == 8905
assert df_test.columns.tolist() == indicators.columns.tolist()<jupyter_output><empty_output><jupyter_text>Exercise 6Help Sofia find the data she needs to complete her study!Sofia has been researching on the impact of **Arthritis**. For her final remarks on this subject, she needs to look at all the indicators that have been measured for this topic.Find the options that fullfill these criteria. In the end, present only the **YearStart**, **LocationDesc**, **Question**, and **DataValue**.<jupyter_code>def exercise_6(df):
    """ 
    Find all the indicators involving Arthritis.
    
    Args:
        df (pd.DataFrame): the input DataFrame

    Returns:
        (pd.DataFrame): subsetted df

    """
    
    arthritis_mask = df['Topic'] == 'Arthritis'
    columns = ["YearStart", "LocationDesc", "Question", "DataValue"]
    return df.loc[arthritis_mask, columns]
    # YOUR CODE HERE
    #raise NotImplementedError()
# This cell is what will test your code, please ignore it!
df_test = exercise_6(indicators)
assert isinstance(df_test, pd.DataFrame)
assert len(df_test.columns) == 4
assert len(df_test.index) == 681
assert sum(df_test.DataValue.isna()) == 229
assert round(df_test.DataValue.astype(float).mean(),1) == 36.7
assert len("".join(df_test.Question)) == 45972
assert 'California' in df_test.LocationDesc.tolist()<jupyter_output><empty_output><jupyter_text>Exercise 7Help Marco finding the study he needs.Marco is currently working in **Mental Health** issues in the general population. He is looking for recent indicators to help him in his analysis of the situation in **Arkansas**. So he asks you if there are any indicators that started in 2018 that may be helpful to him.Find the options that fullfill this criteria. You must show the **YearStart**, **Question**,**DataValueUnit**, and **DataValue**.Hint: Beaware of the columns data types. You might think some things are strings when in fact their are integers, or vice-versa.<jupyter_code>def exercise_7(df):
    """ 
    Find all Mental Health indicators for Arkansas in the year 2018.
    
    Args:
        df (pd.DataFrame): the input DataFrame

    Returns:
        (pd.DataFrame): subsetted df

    """
    
    mental_health_mask = df.Topic == 'Mental Health'
    arkansas_mask = df.LocationDesc == 'Arkansas'
    startDate = df.YearStart == 2018
    columns = ["YearStart", "Question","DataValueUnit","DataValue"]
    
    # YOUR CODE HERE
    return df.loc[mental_health_mask & arkansas_mask & startDate, columns]
    #raise NotImplementedError()
# This cell is what will test your code, please ignore it!
df_test = exercise_7(indicators)
assert isinstance(df_test, pd.DataFrame)
assert len(df_test.columns) == 4
assert len(df_test.index) == 1
assert 'recent' in df_test['Question'].tolist()[0]
assert df_test.DataValue.get(8144) == '19.6'<jupyter_output><empty_output><jupyter_text>Exercise 8What are the 5 locations with the most indicators in the dataset?**Return** a series where the index is the 5 locations and the values are their respective count in the dataset._hints_:1. Hmum we want to represent each location by their appearance count in the dataset. In other words, despite the fact that each location appears multiple times in the dataset, we want to represent each one of them with only a number. Where did we learn to do such a thing...2. Now we want the 5 highest ones. I believe there's a method to give us just that..<jupyter_code>def exercise_8(df):
    """ 
    Find the 5 locations with the highest appearance on the dataset, and their respective count.
    
    Args:
        df (pd.DataFrame): the input DataFrame

    Returns:
        new_df (pd.DataFrame): the transformed DataFrame

    """
    
    return df.groupby('LocationDesc').size().nlargest(5)
    
    # YOUR CODE HERE
    #raise NotImplementedError()

df_test= exercise_8(indicators)
assert isinstance(df_test, pd.Series)
assert df_test.index.name == 'LocationDesc'
assert 'Kentucky' in df_test.index.tolist()
assert df_test.index.values[-1] == 'Washington' and df_test.values[-1] == 206<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09992114453080995, 'fraction_numerical': 0.021854230032668693, 'mean_word_length': 4.3643504531722055, 'pattern_counts': {'":': 0, '<': 41, '<?xml version=': 0, '>': 41, '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': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '359465', 'license': 'MIT', 'n_tokens_mistral': 2857, 'n_tokens_neox': 2652, 'n_words': 884, 'path': 'Pandas/Pandas Advanced/Solutions notebook.ipynb', 'repo_name': 'vohcolab/PandaViz-Workshop', 'size': '8877'}