<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:  <div class="alert alert-info" style="font-size
Step2:  Reversing this operation, is reset_index
Step3:  Selecting data based on the index
<div class="alert alert-warning" style="font-size
Step4:  But the row or column indexer can also be a list, slice, boolean array (see next section), ..
Step5:  <div class="alert alert-danger">
<b>NOTE</b>
Step6:  The different indexing methods can also be used to assign data
Step7:  <div class="alert alert-info" style="font-size
Step8:  <div class="alert alert-success">
<b>EXERCISE</b>
Step9:  <div class="alert alert-success">
<b>EXERCISE</b>
Step10:  <div class="alert alert-success">
<b>EXERCISE</b>
Step11:  <div class="alert alert-success">
<b>EXERCISE</b>
Step12:  The next exercise uses the titanic data set
Step13:  <div class="alert alert-success">
<b>EXERCISE</b>
Step14:  We will later see an easier way to calculate both averages at the same time with groupby.
Alignment on the index
<div class="alert alert-danger">
**WARNING**
Step15:  Pitfall
Step16:  When updating values in a DataFrame, you can run into the infamous "SettingWithCopyWarning" and issues with chained indexing.
Assume we want to cap the population and replace all values above 50 with 50. We can do this using the basic [] indexing operation twice ("chained indexing")
Step17:  However, we get a warning, and we can also see that the original dataframe did not change
Step18:  The warning message explains that we should use .loc[row_indexer,col_indexer] = value instead. That is what we just learned in this notebook, so we can do
Step19:  And now the dataframe actually changed
Step20:  To explain why the original df[df['population'] &gt; 50]['population'] = 50 didn't work, we can do the "chained indexing" in two explicit steps
<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 II</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 = countries.set_index('country')
countries
Explanation: <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>
Changing the DataFrame index
We have mostly worked with DataFrames with the default 0, 1, 2, ... N row labels (except for the time series data). But, we can also set one of the columns as the index.
Setting the index to the country names:
End of explanation
countries.reset_index('country')
Explanation: Reversing this operation, is reset_index:
End of explanation
countries.loc['Germany', 'area']
Explanation: Selecting data based on the index
<div class="alert alert-warning" style="font-size:120%">
<b>ATTENTION!</b>: <br><br>
One of pandas' basic features is the labeling of rows and columns, but this makes indexing also a bit more complex compared to numpy. <br><br> We now have to distuinguish between:
* selection by **label** (using the row and column names)
* selection by **position** (using integers)
</div>
Systematic indexing with loc and iloc
When using [] like above, you can only select from one axis at once (rows or columns, not both). For more advanced indexing, you have some extra attributes:
loc: selection by label
iloc: selection by position
Both loc and iloc use the following pattern: df.loc[ &lt;selection of the rows&gt; , &lt;selection of the columns&gt; ].
This 'selection of the rows / columns' can be: a single label, a list of labels, a slice or a boolean mask.
Selecting a single element:
End of explanation
countries.loc['France':'Germany', ['area', 'population']]
Explanation: But the row or column indexer can also be a list, slice, boolean array (see next section), ..
End of explanation
countries.iloc[0:2,1:3]
Explanation: <div class="alert alert-danger">
<b>NOTE</b>:
* Unlike slicing in numpy, the end label is **included**!
</div>
Selecting by position with iloc works similar as indexing numpy arrays:
End of explanation
countries2 = countries.copy()
countries2.loc['Belgium':'Germany', 'population'] = 10
countries2
Explanation: The different indexing methods can also be used to assign data:
End of explanation
countries['density'] = countries['population']*1000000 / countries['area']
Explanation: <div class="alert alert-info" style="font-size:120%">
<b>REMEMBER</b>: <br><br>
Advanced indexing with **loc** and **iloc**
* **loc**: select by label: `df.loc[row_indexer, column_indexer]`
* **iloc**: select by position: `df.iloc[row_indexer, column_indexer]`
</div>
<div class="alert alert-success">
<b>EXERCISE</b>:
<p>
<ul>
    <li>Add the population density as column to the DataFrame.</li>
</ul>
</p>
Note: the population column is expressed in millions.
</div>
End of explanation
countries.loc[countries['density'] > 300, ['capital', 'population']]
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>Select the capital and the population column of those countries where the density is larger than 300</li>
</ul>
</div>
End of explanation
countries['density_ratio'] = countries['density'] / countries['density'].mean()
countries
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>Add a column 'density_ratio' with the ratio of the population density to the average population density for all countries.</li>
</ul>
</div>
End of explanation
countries.loc['United Kingdom', 'capital'] = 'Cambridge'
countries
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>Change the capital of the UK to Cambridge</li>
</ul>
</div>
End of explanation
countries[(countries['density'] > 100) & (countries['density'] < 300)]
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
 <ul>
  <li>Select all countries whose population density is between 100 and 300 people/km²</li>
</ul>
</div>
End of explanation
df = pd.read_csv("data/titanic.csv")
df.head()
Explanation: The next exercise uses the titanic data set:
End of explanation
df.loc[df['Sex'] == 'male', 'Age'].mean()
df.loc[df['Sex'] == 'female', 'Age'].mean()
Explanation: <div class="alert alert-success">
<b>EXERCISE</b>:
* Select all rows for male passengers and calculate the mean age of those passengers. Do the same for the female passengers. Do this now using `.loc`.
</div>
End of explanation
population = countries['population']
s1 = population[['Belgium', 'France']]
s2 = population[['France', 'Germany']]
s1
s2
s1 + s2
Explanation: We will later see an easier way to calculate both averages at the same time with groupby.
Alignment on the index
<div class="alert alert-danger">
**WARNING**: **Alignment!** (unlike numpy)
* Pay attention to **alignment**: operations between series will align on the index:
</div>
End of explanation
df = countries.copy()
Explanation: Pitfall: chained indexing (and the 'SettingWithCopyWarning')
End of explanation
df[df['population'] > 50]['population'] = 50
Explanation: When updating values in a DataFrame, you can run into the infamous "SettingWithCopyWarning" and issues with chained indexing.
Assume we want to cap the population and replace all values above 50 with 50. We can do this using the basic [] indexing operation twice ("chained indexing"):
End of explanation
df
Explanation: However, we get a warning, and we can also see that the original dataframe did not change:
End of explanation
df.loc[df['population'] > 50, 'population'] = 50
Explanation: The warning message explains that we should use .loc[row_indexer,col_indexer] = value instead. That is what we just learned in this notebook, so we can do:
End of explanation
df
Explanation: And now the dataframe actually changed:
End of explanation
temp = df[df['population'] > 50]
temp['population'] = 50
Explanation: To explain why the original df[df['population'] &gt; 50]['population'] = 50 didn't work, we can do the "chained indexing" in two explicit steps:
End of explanation
<END_TASK>