<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 <p><font size="6"><b>03 - Pandas
Step1:  Subsetting data
Subset variables (columns)
For a DataFrame, basic indexing selects the columns (cfr. the dictionaries of pure python)
Selecting a single column
Step2:  Remember that the same syntax can also be used to add a new columns
Step3:  Subset observations (rows)
Using [], slicing or boolean indexing accesses the rows
Step4:  Boolean indexing (filtering)
Often, you want to select rows based on a certain condition. This can be done with 'boolean indexing' (like a where clause in SQL) and comparable to numpy. 
The indexer (or boolean mask) should be 1-dimensional and the same length as the thing being indexed.
Step5:  An overview of the possible comparison operations
Step6:  This can then be used to filter the dataframe with boolean indexing
Step7:  Let's say we want to select all data for which the capital starts with a 'B'. In Python, when having a string, we could use the startswith method
Step8:  In pandas, these are available on a Series through the str namespace
Step9:  For an overview of all string methods, see
Step10:  <div class="alert alert-success">
<b>EXERCISE</b>
Step11:  We will later see an easier way to calculate both averages at the same time with groupby.
<div class="alert alert-success">
<b>EXERCISE</b>
Step12:  <div class="alert alert-success">
<b>EXERCISE</b>
Step13:  <div class="alert alert-success">
<b>EXERCISE</b>
Step14:  <div class="alert alert-success">
<b>EXERCISE</b>
Step15:  <div class="alert alert-success">
<b>EXERCISE</b>
Step16:  <div class="alert alert-success">
<b>EXERCISE</b>
Step17:  [OPTIONAL] more exercises
For the quick ones among you, here are some more exercises with some larger dataframe with film data. These exercises are based on the PyCon tutorial of Brandon Rhodes (so all credit to him!) and the datasets he prepared for that. You can download these data from here
Step18:  <div class="alert alert-success">
<b>EXERCISE</b>
Step19:  <div class="alert alert-success">
<b>EXERCISE</b>
Step20:  <div class="alert alert-success">
<b>EXERCISE</b>
Step21:  <div class="alert alert-success">
<b>EXERCISE</b>
Step22:  <div class="alert alert-success">
<b>EXERCISE</b>
Step23:  <div class="alert alert-success">
<b>EXERCISE</b>
Step24:  <div class="alert alert-success">
<b>EXERCISE</b>
Step25:  <div class="alert alert-success">
<b>EXERCISE</b>
Step26:  <div class="alert alert-success">
<b>EXERCISE</b>
<ASSISTANT_TASK:>
Python Code:
import pandas as pd
# redefining the example DataFrame
data = {'country': ['Belgium', 'France', 'Germany', 'Netherlands', 'United Kingdom'],
        'population': [11.3, 64.3, 81.3, 16.9, 64.9],
        'area': [30510, 671308, 357050, 41526, 244820],
        'capital': ['Brussels', 'Paris', 'Berlin', 'Amsterdam', 'London']}
countries = pd.DataFrame(data)
countries
Explanation: <p><font size="6"><b>03 - Pandas: Indexing and selecting data - part I</b></font></p>
© 2021, Joris Van den Bossche and Stijn Van Hoey  (&#106;&#111;&#114;&#105;&#115;&#118;&#97;&#110;&#100;&#101;&#110;&#98;&#111;&#115;&#115;&#99;&#104;&#101;&#64;&#103;&#109;&#97;&#105;&#108;&#46;&#99;&#111;&#109;, &#115;&#116;&#105;&#106;&#110;&#118;&#97;&#110;&#104;&#111;&#101;&#121;&#64;&#103;&#109;&#97;&#105;&#108;&#46;&#99;&#111;&#109;). Licensed under CC BY 4.0 Creative Commons
End of explanation
countries['area'] # single []
Explanation: Subsetting data
Subset variables (columns)
For a DataFrame, basic indexing selects the columns (cfr. the dictionaries of pure python)
Selecting a single column:
End of explanation
countries[['area', 'population']] # double [[]]
Explanation: Remember that the same syntax can also be used to add a new columns: df['new'] = ....
We can also select multiple columns by passing a list of column names into []:
End of explanation
countries[0:4]
Explanation: Subset observations (rows)
Using [], slicing or boolean indexing accesses the rows:
Slicing
End of explanation
countries['area'] > 100000
countries[countries['area'] > 100000]
countries[countries['population'] > 50]
Explanation: Boolean indexing (filtering)
Often, you want to select rows based on a certain condition. This can be done with 'boolean indexing' (like a where clause in SQL) and comparable to numpy. 
The indexer (or boolean mask) should be 1-dimensional and the same length as the thing being indexed.
End of explanation
s = countries['capital']
s.isin?
s.isin(['Berlin', 'London'])
Explanation: An overview of the possible comparison operations:
Operator   |  Description
------ | --------
==       | Equal
!=       | Not equal
>       | Greater than
>=       | Greater than or equal
\<       | Lesser than
<=       | Lesser than or equal
and to combine multiple conditions:
Operator   |  Description
------ | --------
&       | And (cond1 &amp; cond2)
\|       | Or (cond1 \| cond2)
<div class="alert alert-info" style="font-size:120%">
<b>REMEMBER</b>: <br><br>
So as a summary, `[]` provides the following convenience shortcuts:
* **Series**: selecting a **label**: `s[label]`
* **DataFrame**: selecting a single or multiple **columns**:`df['col']` or `df[['col1', 'col2']]`
* **DataFrame**: slicing or filtering the **rows**: `df['row_label1':'row_label2']` or `df[mask]`
</div>
Some other useful methods: isin and string methods
The isin method of Series is very useful to select rows that may contain certain values:
End of explanation
countries[countries['capital'].isin(['Berlin', 'London'])]
Explanation: This can then be used to filter the dataframe with boolean indexing:
End of explanation
string = 'Berlin'
string.startswith('B')
Explanation: Let's say we want to select all data for which the capital starts with a 'B'. In Python, when having a string, we could use the startswith method:
End of explanation
countries['capital'].str.startswith('B')
Explanation: In pandas, these are available on a Series through the str namespace:
End of explanation
df = pd.read_csv("data/titanic.csv")
df.head()
Explanation: For an overview of all string methods, see: https://pandas.pydata.org/pandas-docs/stable/reference/series.html#string-handling
Exercises using the Titanic dataset
End of explanation
males = df[df['Sex'] == 'male']
males['Age'].mean()
df[df['Sex'] == 'female']['Age'].mean()
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>Select all rows for male passengers and calculate the mean age of those passengers. Do the same for the female passengers.</li>
</ul>
</div>
End of explanation
len(df[df['Age'] > 70])
(df['Age'] > 70).sum()
Explanation: We will later see an easier way to calculate both averages at the same time with groupby.
<div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>How many passengers older than 70 were on the Titanic?</li>
</ul>
</div>
End of explanation
df[(df['Age'] > 30) & (df['Age'] <= 40)]
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>Select the passengers that are between 30 and 40 years old?</li>
</ul>
</div>
End of explanation
name = 'Braund, Mr. Owen Harris'
name.split(",")[0]
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
For a single string `name = 'Braund, Mr. Owen Harris'`, split this string (check the `split()` method of a string) and get the first element of the resulting list.
<details><summary>Hints</summary>
- No Pandas in this exercise, just standard Python.
- The `split()` method of a string returns a python list. Accessing elements of a python list can be done using the square brackets indexing (`a_list[i]`).
</details> 
</div>
End of explanation
df['Surname'] = df['Name'].str.split(",").str.get(0)
df['Surname']
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
Convert the solution of the previous exercise to all strings of the `Name` column at once. Split the 'Name' column on the `,`, extract the first part (the surname), and add this as new column 'Surname'. 
<details><summary>Hints</summary>
- Pandas uses the `str` accessor to use the string methods such as `split`, e.g. `.str.split(...)` as the equivalent of the `split()` method of a single string (note: there is a small difference in the naming of the first keyword argument: `sep` vs `pat`).
- The [`.str.get()`](https://pandas.pydata.org/docs/reference/api/pandas.Series.str.get.html#pandas.Series.str.get) can be used to get the n-th element of a list, which is what the `str.split()` returns. This is the equivalent of selecting an element of a single list (`a_list[i]`) but then for all values of the Series.
- One can chain multiple `.str` methods, e.g. `str.SOMEMETHOD(...).str.SOMEOTHERMETHOD(...)`.
</details>    
</div>
End of explanation
df[df['Surname'].str.startswith('Williams')]
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>Select all passenger that have a surname starting with 'Williams'.</li>
</ul>
</div>
End of explanation
df[df['Surname'].str.len() > 15]
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>Select all rows for the passengers with a surname of more than 15 characters.</li>
</ul>
</div>
End of explanation
cast = pd.read_csv('data/cast.csv')
cast.head()
titles = pd.read_csv('data/titles.csv')
titles.head()
Explanation: [OPTIONAL] more exercises
For the quick ones among you, here are some more exercises with some larger dataframe with film data. These exercises are based on the PyCon tutorial of Brandon Rhodes (so all credit to him!) and the datasets he prepared for that. You can download these data from here: titles.csv and cast.csv and put them in the /notebooks/data folder.
End of explanation
len(titles)
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>How many movies are listed in the titles dataframe?</li>
</ul>
</div>
End of explanation
titles.sort_values('year').head(2)
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>What are the earliest two films listed in the titles dataframe?</li>
</ul>
</div>
End of explanation
len(titles[titles['title'] == 'Hamlet'])
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>How many movies have the title "Hamlet"?</li>
</ul>
</div>
End of explanation
titles[titles['title'] == 'Treasure Island'].sort_values('year')
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>List all of the "Treasure Island" movies from earliest to most recent.</li>
</ul>
</div>
End of explanation
len(titles[(titles['year'] >= 1950) & (titles['year'] <= 1959)])
len(titles[titles['year'] // 10 == 195])
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>How many movies were made from 1950 through 1959?</li>
</ul>
</div>
End of explanation
inception = cast[cast['title'] == 'Inception']
len(inception[inception['n'].isna()])
inception['n'].isna().sum()
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>How many roles in the movie "Inception" are NOT ranked by an "n" value?</li>
</ul>
</div>
End of explanation
len(inception[inception['n'].notna()])
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>But how many roles in the movie "Inception" did receive an "n" value?</li>
</ul>
</div>
End of explanation
titanic = cast[(cast['title'] == 'Titanic') & (cast['year'] == 1997)]
titanic = titanic[titanic['n'].notna()]
titanic.sort_values('n')
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>Display the cast of the "Titanic" (the most famous 1997 one) in their correct "n"-value order, ignoring roles that did not earn a numeric "n" value.</li>
</ul>
</div>
End of explanation
brad = cast[cast['name'] == 'Brad Pitt']
brad = brad[brad['year'] // 10 == 199]
brad = brad[brad['n'] == 2]
brad.sort_values('year')
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>List the supporting roles (having n=2) played by Brad Pitt in the 1990s, in order by year.</li>
</ul>
</div>
End of explanation
<END_TASK>