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).set_index('country')
countries

"""
Explanation: Automatic alignment on labels
End of explanation
"""


death_rate = pd.Series([10, 9, 11, 8, 9],
                       index=['Poland','United Kingdom', 'Germany', 'Netherlands', 'France'])
print(death_rate)

birth_rate = pd.Series([10, 9, 10, 12],
                       index=['Netherlands',  'Germany', 'Poland', 'France'])
print(birth_rate)

"""
Explanation: Series alignment
Let's define a table with natural increase rates in 2013 (data from World Bank):
End of explanation
"""


natural_increase = birth_rate - death_rate
print(natural_increase)

"""
Explanation: Now we calculate the natural increae by subtracting death rate from birth rate:
End of explanation
"""


pop_change = pd.DataFrame({'death rate' : death_rate, 
                           'birth rate' : birth_rate,
                           'natural increase' : natural_increase})

"""
Explanation: Note that the rows where the two series did not overlap contain missing values (NaN = Not a Number) and that the data were properly aligned on the index.
End of explanation
"""


pop_change.dropna(inplace=True)

pop_change

"""
Explanation: Missing values
We can remove the missing data using dropna method:
End of explanation
"""


countries.join(pop_change)

"""
Explanation: <div class="alert alert-success">
    <b>EXERCISE</b>: Calculate estimated population in 2014 by summing the population and natural increase (remember that the natural increase is given per 1000 people).
</div>

<div class="alert alert-success">
    <b>EXERCISE</b>: Calculate ratio of the highest and lowest estimated population in 2014.
</div>

Joining two data frames
Let's now try to add the data to the country data:
End of explanation
"""


countries.join(pop_change, how='right')

"""
Explanation: There are four different ways we can handle the missing rows: 

left (default) &mdash; take all the rows of the left data frame
right &mdash; take all the rows of the right data frame
inner &mdash; take the common rows of both data frames
outer &mdash; take all the rows present in either or both data frames

Note that the methods are similar to SQL JOIN clause.
End of explanation
"""


df = pd.DataFrame({'key':['A','B','C','A','B','C','A','B','C'],
                   'data': [0, 5, 10, 5, 10, 15, 10, 15, 20]})
df

df.groupby('key').aggregate('sum')  # np.sum

df.groupby('key').sum()

"""
Explanation: <div class="alert alert-success">
    <b>EXERCISE</b>: Try inner and outer join. What's the difference?
</div>

Groupby operations
Some 'theory': the groupby operation (split-apply-combine)
By "group by" we are referring to a process involving one or more of the following steps

Splitting the data into groups based on some criteria
Applying a function to each group independently
Combining the results into a data structure

<img src="img/splitApplyCombine.png">
Similar to SQL GROUP BY
The example of the image in pandas syntax:
End of explanation
"""


df.groupby('key').size()

"""
Explanation: You can also simply count members of each split:
End of explanation
"""


cast = pd.read_csv('data/cast.csv')
cast[10:15]

titles = pd.read_csv('data/titles.csv')
titles.head()

"""
Explanation: Movie database
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
"""


greek = ['α', 'β', 'β', 'β', 'β', 'α', 'β','α', 'α']
df.groupby(greek).max()

"""
Explanation: <div class="alert alert-success">
    <b>EXERCISE</b>: Using groupby(), plot the number of films that have been released each year in the history of cinema.
</div>

<div class="alert alert-success">
    <b>EXERCISE</b>: Use groupby() to determine how many roles are listed for each of The Pink Panther movies.
</div>

Custom  grouping criteria
You can also group by the values on another array under the condition that this array has the length equal to the number of rows:
End of explanation
"""


df.groupby(df['data'] % 2).size()

"""
Explanation: The values for the grouping array can be also computed from values in the data frame. For example, to count odd and even number in the data column we could simply:
End of explanation
"""


df['type'] = np.where(df['data'] % 2, 'odd', 'even')
print(df)

df.groupby(['type', 'key']).sum()

df['type'] = np.where(df['data'] % 2, 'odd', 'even')
print(df)
df['data']

"""
Explanation: <div class="alert alert-success">
    <b>EXERCISE</b>: Using groupby(), plot the number of films that have been released each **decade** in the history of cinema.
</div>

<div class="alert alert-success">
    <b>EXERCISE</b>: Use groupby() to plot the number of "Hamlet" films made each decade.
</div>

Multiple groups
Note that you can also groupby on multiple keys:
End of explanation
"""


titles.title.value_counts().head()

"""
Explanation: Note that it creates a hierarchical index. More on that later.
<div class="alert alert-success">
    <b>EXERCISE</b>: List each of the characters that Frank Oz has portrayed at least twice.
</div>

<div class="alert alert-success">
    <b>EXERCISE</b>: List, in order by year, each of the films in which Frank Oz has played more than 1 role.
</div>

<div class="alert alert-success">
    <b>EXERCISE</b>: How many leading (n=1) roles were available to actors, and how many to actresses, in each year of the 1950s?
</div>

Value counts
A useful shortcut to calculate the number of occurences of certain values is value_counts (this is somewhat equivalent to df.groupby(key).size()))
For example, what are the most occuring movie titles?
End of explanation
"""


def most_frequent(x):
    return x.value_counts().index[0]

cast.loc[(cast['year'] >= 2010) & (cast['year'] < 2020), ['year', 'name']].groupby('year').agg(most_frequent)

"""
Explanation: <div class="alert alert-success">
    <b>EXERCISE</b>: What are the 11 most common character names in movie history?
</div>

Custom aggregate functions
Aggregate function could be any function accepting the Series object. 
For example, let's calculate most frequent apperances in each year of last decade:
End of explanation
"""


