<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Indexing and selecting data
Step1:  More on NumPy indexing
Step2:  Fancy indexing
Apart from indexing with integers and slices NumPy also supports indexing with arrays of integers (so-called fancy indexing). For example, to get the 2nd and 4th element of a
Step3:  Boolean indexing
To select data fulfilling specific criteria, one can use the bolean indexing. This is best illustrated on 1D arrays; for example, lets select only positive elements of a
Step4:  Note that the index array has the same size as and type of boolean
Step5:  Multiple criteria can be also combine in one query
Step6:  <div class="alert alert-success">
    <b>EXERCISE</b>
Step7:  We can use fancy indexing with the rich index
Step8:  Similarly, boolean indexing can be used to filter the Series. Lets select countries with population of more than 20 millions
Step9:  You can also do position-based indexing by using integers instead of labels
Step10:  Indexing DataFrame
Step11:  Some notes on selecting data
Data frames allow for labeling rows and columns, but this makes indexing also a bit more complex compared to 1D NumPy's array and pandas Series. We now have to distuinguish between
Step12:  or multiple columns using fancy indexing
Step13:  But, slicing accesses the rows
Step14:  We can also select rows similarly to the boolean indexing in numpy. The boolean mask should be 1-dimensional and the same length as the thing being indexed. Boolean indexing of DataFrame  can be used like the WHERE clause of SQL to select rows matching some criteria
Step15:  So as a summary, [] provides the following convenience shortcuts
Step16:  But the row or column indexer can also be a list, slice, boolean array, ..
Step17:  Selecting by position with iloc works similar as indexing numpy arrays
Step18:  The different indexing methods can also be used to assign data
Step19:  <div class="alert alert-success">
    <b>EXERCISE</b>
<ASSISTANT_TASK:>
Python Code:
%matplotlib inline
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
Explanation: Indexing and selecting data
End of explanation
a = np.array([-2, 3, 4, -5, 5])
print(a)
Explanation: More on NumPy indexing
End of explanation
a[[1, 3]]
Explanation: Fancy indexing
Apart from indexing with integers and slices NumPy also supports indexing with arrays of integers (so-called fancy indexing). For example, to get the 2nd and 4th element of a:
End of explanation
a[a > 0]
Explanation: Boolean indexing
To select data fulfilling specific criteria, one can use the bolean indexing. This is best illustrated on 1D arrays; for example, lets select only positive elements of a:
End of explanation
print(a)
print(a > 0)
Explanation: Note that the index array has the same size as and type of boolean:
End of explanation
a[(a > 0) & (a < 5)]
Explanation: Multiple criteria can be also combine in one query:
End of explanation
pop_dict = {'Germany': 81.3, 
            'Belgium': 11.3, 
            'France': 64.3, 
            'United Kingdom': 64.9, 
            'Netherlands': 16.9}
population = pd.Series(pop_dict)
print(population)
Explanation: <div class="alert alert-success">
    <b>EXERCISE</b>: Select all odd numbers from the array <code>a</code>
</div>
<div class="alert alert-success">
    <b>EXERCISE</b>: Select <b>negative</b> odd numbers from the array <code>a</code>
</div>
Indexing pandas Series
Series can be indexed similarly to 1D NumPy array.
End of explanation
population[['Netherlands', 'Germany']]
Explanation: We can use fancy indexing with the rich index:
End of explanation
population[population > 20]
Explanation: Similarly, boolean indexing can be used to filter the Series. Lets select countries with population of more than 20 millions:
End of explanation
population[:2]
Explanation: You can also do position-based indexing by using integers instead of labels:
End of explanation
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
countries = countries.set_index('country')
countries
Explanation: Indexing DataFrame
End of explanation
countries['area']
Explanation: Some notes on selecting data
Data frames allow for labeling rows and columns, but this makes indexing also a bit more complex compared to 1D NumPy's array and pandas Series. We now have to distuinguish between:
selection of rows or columns,
selection by label or position.
[] provides some convenience shortcuts
For a DataFrame, basic indexing selects the columns.
Selecting a single column:
End of explanation
countries[['area', 'population']]
Explanation: or multiple columns using fancy indexing:
End of explanation
countries['France':'Netherlands']
Explanation: But, slicing accesses the rows:
End of explanation
countries[countries['area'] > 100000]
Explanation: We can also select rows similarly to the boolean indexing in numpy. The boolean mask should be 1-dimensional and the same length as the thing being indexed. Boolean indexing of DataFrame  can be used like the WHERE clause of SQL to select rows matching some criteria:
End of explanation
countries.loc['Germany', 'area']
Explanation: So as a summary, [] provides the following convenience shortcuts:
<table>
<tr>
<td></td>
<td>NumPy/`Series`</td>
<td>`DataFrame`</td>
</tr>
<tr>
<td>Integer index<br>`data[label]`</td>
<td>single element</td>
<td>single **column**</td>
</tr>
<tr>
<td>Slice<br>`data[label1:label2]`</td>
<td>sequence</td>
<td>one or more **rows**</td>
</tr>
<tr>
<td>Fancy indexing<br>`data[[label1,label2]]`</td>
<td>sequence</td>
<td>one or more **columns**</td>
</tr>
<tr>
<td>Boolean indexing<br>`data[mask]`</td>
<td>sequence</td>
<td>one or more **rows**</td>
</tr>
</table>
<div class="alert alert-success">
    <b>EXERCISE</b>: Calculate the area of Germany relative to the total area of all other countries in the data frame. *Hint*: you can compare the index of the data frame to any string
</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
These methods index the different dimensions of the frame:
df.loc[row_indexer, column_indexer]
df.iloc[row_indexer, column_indexer]
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, ..
End of explanation
countries.iloc[:2,1:3]
Explanation: 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
cast = pd.read_csv('data/cast.csv')
cast.head()
titles = pd.read_csv('data/titles.csv')
titles.head()
Explanation: <div class="alert alert-success">
    <b>EXERCISE</b>: Add a column `density` with the population density (note: population column is expressed in millions)
</div>
<div class="alert alert-success">
    <b>EXERCISE</b>: Select the capital and the population column of those countries where the density is larger than 300
</div>
<div class="alert alert-success">
    <b>EXERCISE</b>: List names, capitals and population densities of two countries with highest population density.
</div>
<div class="alert alert-success">
    <b>EXERCISE</b>: Change the capital of the UK to Cambridge
</div>
<div class="alert alert-success">
    <b>EXERCISE</b>: Select all countries whose population density is between 100 and 300 people/km²
</div>
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 /data folder.
End of explanation
<END_TASK>