<jupyter_start><jupyter_text>CS109A Introduction to PANDAS Lecture 2, Exercise 2: PANDAS Intro 2**Harvard University****Fall 2021****Instructors**: <NAME> and <NAME>--- **Exercise 2: PANDAS Intro 2**Let's get some more practice with a few of the core `PANDAS` functions.<jupyter_code>import pandas as pd<jupyter_output><empty_output><jupyter_text>We'll continue working with StudentsPerformance dataset from Exercise 1.<jupyter_code># import the CSV file
df = pd.read_csv("StudentsPerformance.csv")
df.head()<jupyter_output><empty_output><jupyter_text>Indexing - iloc and locIt's very important to understand the differences between loc and iloc. Looking at the next cell code one could think that they do the same thing.  (When you query just for one row you obtain an object whose name is the index of the selected row.)<jupyter_code>df.iloc[10] == df.loc[10]
all(df.iloc[10] == df.loc[10])<jupyter_output><empty_output><jupyter_text>The first time we loaded the CSV into a DataFrame we didn't tell pandas to interpret a specific column as an index, so pandas created an index for us. Whether the ordering imposed on our data by this index should be respected is a matter on which `iloc` and `loc` disagree.**To really learn the difference of `iloc` and `loc` we need to shuffle the rows**To do that we can use the [sample](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.sample.html) method to take a new sample of the dataframe original size (frac=1 means that)_Bonus: Stop and consider: what is the purpose of setting a `random_state` when we call them `sample` method?_<jupyter_code>df = df.sample(frac=1, random_state=109)
df<jupyter_output><empty_output><jupyter_text>Now let's repeat our code from ealier.<jupyter_code>df.iloc[10] == df.loc[10]
all(df.iloc[10] == df.loc[10])<jupyter_output><empty_output><jupyter_text>It turns out that `loc` filters by index value (something like `where df.index == value`).That is, `loc`'s results are depend only on the **indices** (which are now scrambled after sampling). The actual positions of elements in the DataFrame are ignored.<jupyter_code>df.loc[10]<jupyter_output><empty_output><jupyter_text>By contrast, `iloc` filters by row position (something like `where df.index == df.index[value]`)So `iloc`'s results depend on the **actual positions** of elements in a pandas data structure. The indices of these elements are ignored. It's this difference that explains counterintuitive results like this:<jupyter_code>df.index[10]<jupyter_output><empty_output><jupyter_text>Consider a single row index with `iloc`<jupyter_code>df.iloc[10]<jupyter_output><empty_output><jupyter_text>And take note of where you can find the index in output formatted like this.Enough chat. Time for... **Exercise**  In the cell below, fill in the blank so that the row5 variable stores the 5th row of df. To be clear, imagine our DataFrame looked as follows:| Index | Words ||-|-||0|this||1|is||2|not||3|easy|We'd say the 1st row is the one with *this* word, the 2nd row is the one with *is* word, the 3rd row is the one with *not* word, etc.<jupyter_code>### edTest(test_a) ###
row5 = df.iloc[4]
row5<jupyter_output><empty_output><jupyter_text>You can display the first rows to have a better understanding of what you did. Can you find the row you've just selected?<jupyter_code>df.iloc[:5]<jupyter_output><empty_output><jupyter_text>Notice how we can use familar [Python slice notation](https://python-reference.readthedocs.io/en/latest/docs/brackets/slicing.html) with `iloc` and `loc`!  SortingWe scrambled out df earlier with `sample`. We should also know how to tidy things up.  **Exercise**  In the cell below, fill in the blank so that `sorted_df` now stores `df` after sorting it by the math score column in decreasing order ([HINT](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.sort_values.html))<jupyter_code>### edTest(test_b) ###
sorted_df = df.sort_values(by='math score', ascending=False)
sorted_df<jupyter_output><empty_output><jupyter_text>**Exercise**  In the cell below, fill in the blank so that `sorted_row5` stores the 5th row of `sorted_df`. To be clear, imagine our sorted DataFrame looked as follows:| Index | Words ||-|-||3|easy|1|is||2|not||0|this|We'd say the 1st row is the one with *easy*, the 2nd row is the one with *is*, the 3rd row is the one with *not*, etc.<jupyter_code>### edTest(test_c) ###
sorted_row5 = sorted_df.iloc[4]
sorted_row5<jupyter_output><empty_output><jupyter_text>Can you find the row you've just selected?<jupyter_code># len('head()') < len('iloc[:5]') :)
sorted_df.head()<jupyter_output><empty_output><jupyter_text>Column Naming ConventionsHow you've named your df columns can affect the amount of typing required to manipulate your data, the readability of your code, and even the syntax optons you have. Let's take a look at some best practices for naming columns.<jupyter_code>df<jupyter_output><empty_output><jupyter_text>We can see that there are columns names whose lengths are not confortable for coding. What we can do to make our life easier:- Try to work with short columns names- Try to avoid characters like spaces that will allow us to use column access without brackets- Try working in only lower or upper case (prefably lower case; there's no need to shout)Our df already conforms to this last suggestion. **We'll find that, after some renaming, things should become easier for us, making:**expressions like these ones:```pythoncondition = (df['test preparation course'] != 'completed') & (df['writing score'] > df['writing score'].median())```to become:```pythoncondition = (df.course != 'completed') & (df.writing > df.writing.median())``` **Exercise**  In the cell below fill in the blank with these goals in mind:- rename column `race/ethnicity` to `race`- rename column `parental level of education` to `peduc`- raname column `test preparation course` to `course`- remove ` score` (included the left space char) from the rest of the columns**HINT1**: Don't by shy, check the [documentation](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.rename.html) if you need some help  **HINT2**: in many cases, it's faster to access docstrings using `help` ```pythonhelp(df.rename)```**HINT3**: TAB autocomplete can be ued to you explore an object's available methods and attributes.\**HINT4**: Still more exciting, place your cursor after the opening paranthesis of a function call and press SHIFT+TAB once or twice. Instantly, you're presented with a docstring. It's a whole new world! 🌈<jupyter_code>df.columns
### edTest(test_d) ###
df = df.rename(columns={'race/ethnicity':'race', 'parental level of education': 'peduc','test preparation course':'course','math score': 'math',
    'reading score': 'reading',
    'writing score': 'writing', })
df<jupyter_output><empty_output><jupyter_text>Look for missing valuesMissing values in our DataFrames can cause many issues. They can cause certain operations and function calls to fail and throw an error. Perhaps worse, this problems can happen 'silently,' affecting our results without us realizing there is a problem. Unless we take precautions of course!The first step is locating missingness. This dataset doesn't have any missing values. So we'll make some ourselves and 'poke a holes' in the Dataframe.<jupyter_code># 'poking holes' in the data
df.iloc[0,5] = None
df.iloc[2,2] = None<jupyter_output><empty_output><jupyter_text>**Exercise**  Fill in the blank to display whether or not entries in the first 5 rows are missing ([HINT](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.isna.html)) **Solution**<jupyter_code>df.isna().head()<jupyter_output><empty_output><jupyter_text>**Exercise**  Fill in the blanks to [sum](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.sum.html) the total number of missing values in each of the dataset's columns.<jupyter_code>### edTest(test_e) ###
resultE = df.isna().sum()
display(resultE)<jupyter_output><empty_output><jupyter_text>Now let's deal with these 'holes' we've just made **Exercise**  Fill the missing `math` entry with that column's [mean](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.mean.html?highlight=dataframe%20meanpandas.DataFrame.mean)._Hint: Select subsets of the data frame using a column name or names_**Note:** The blanks here represent just one way of doing this. Don't feel constrain by the blanks here. **Solution**<jupyter_code>df['math'] = df['math'].fillna(df['math'].mean())
df.head()<jupyter_output><empty_output><jupyter_text>**Exercise**  [Drop](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.dropna.html?highlight=dropnapandas.DataFrame.dropna) the row with the missing `peduc` from the DataFrame._Hint: to make it easier, consider that this is now the only remaining missing value_ **Solution**<jupyter_code>df = df.dropna()
df.head()<jupyter_output><empty_output><jupyter_text>Categorical Columns - nunique() and unique()If some of your data is categorical (e.g., taking on a descredte set of values with an inherent ordering) you'll often what to know exactly how many unique values you're dealing with. `nunique` and `unique` are here to help and can be used on a single column or across multiple columns. Please consider, `unique` will return all unique values. What if you ask for the uniques of a column with 1 million different values? 🤔This particular method should be used wisely.<jupyter_code>df.nunique()
df['gender'].unique().tolist()
# the line below represents  the usage that should be avoided
# df['math'].unique()<jupyter_output><empty_output><jupyter_text>**Exercise**  Fill in the blanks using `unique()` and `nunique()` to complete the method `print_uniques` to help us to learn more about the categorical variables in our data.<jupyter_code>### edTest(test_f) ###
def print_uniques(df, col, limit=10):
    """Print column's uniques values when the number of column's uniques is lower or equal than limit """
    n = df[col].nunique()
    if n <= limit:
        print(f'{col}:', df[col].unique().tolist())
    else:
        print(f'{col}:', f'more than {limit} uniques')

for col in df.columns:
    print_uniques(df, col)<jupyter_output>gender: ['male', 'female']
race: ['group D', 'group E', 'group C', 'group B', 'group A']
peduc: ['some high school', 'high school', "bachelor's degree", 'some college', "associate's degree", "master's degree"]
lunch: ['free/reduced', 'standard']
course: ['none', 'completed']
math: more than 10 uniques
reading: more than 10 uniques
writing: more than 10 uniques<jupyter_text>Descriptive statisticsIn Pandas, DataFrames have a simple method for displaying summary statistics.<jupyter_code>df.describe()<jupyter_output><empty_output><jupyter_text>**Exercise**Sometimes we don't want to access all these statistics. In the cell below fill in the blanks to get the mean and the standard deviation of the `writing` and `reading` columns ([HINT](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.aggregate.html)).<jupyter_code>### edTest(test_g) ###
resultG = df[['writing', 'reading']].aggregate(['mean', 'std'])
display(resultG)<jupyter_output><empty_output><jupyter_text>Now we can [group](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.groupby.html?highlight=groupbypandas.DataFrame.groupby) our dataframe by a specific column(s) value's and `aggregate` the other columns. _Hint: Try using `agg()` as an alternative to spare some typing_ **Exercise**Group the dataframe by `peduc` and `gender` while aggregating `math`, `reading`, and `writing` with the mean and `course` with the mode._Tip: Again, don't feel constained by the blanks. This command may span multiple lines._<jupyter_code>df.groupby(['peduc', 'gender']).agg({'math': 'mean',
                                     'reading': 'mean',
                                     'writing': 'mean'})<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11612147578013887, 'fraction_numerical': 0.007194846482054714, 'mean_word_length': 5.76131221719457, 'pattern_counts': {'":': 0, '<': 104, '<?xml version=': 0, '>': 104, 'https://': 10, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '25', 'dir': 'jupyter-structured-clean-dedup', 'id': '267807', 'license': 'MIT', 'n_tokens_mistral': 3808, 'n_tokens_neox': 3627, 'n_words': 1333, 'path': 'docs/lectures/lecture02/notebook/Lec2_Ex2.ipynb', 'repo_name': 'yankeesong/2021-CS109A', 'size': '11971'}