<jupyter_start><jupyter_text>Automatic alignment on labels<jupyter_code>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<jupyter_output><empty_output><jupyter_text>`Series` alignment Let's define a table with natural increase rates in 2013 (data from World Bank):<jupyter_code>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)<jupyter_output>Netherlands    10
Germany         9
Poland         10
France         12
dtype: int64<jupyter_text>Now we calculate the natural increae by subtracting death rate from birth rate:<jupyter_code>natural_increase = birth_rate - death_rate
print(natural_increase)<jupyter_output>France             3
Germany           -2
Netherlands        2
Poland             0
United Kingdom   NaN
dtype: float64<jupyter_text>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.<jupyter_code>pop_change = pd.DataFrame({'death rate' : death_rate, 
                           'birth rate' : birth_rate,
                           'natural increase' : natural_increase})<jupyter_output><empty_output><jupyter_text>Missing values We can remove the missing data using `dropna` method:<jupyter_code>pop_change.dropna(inplace=True)
pop_change<jupyter_output><empty_output><jupyter_text>EXERCISE: Calculate estimated population in 2014 by summing the population and natural increase (remember that the natural increase is given per 1000 people).     EXERCISE: Calculate ratio of the highest and lowest estimated population in 2014.  Joining two data frames Let's now try to add the data to the country data:<jupyter_code>countries.join(pop_change)<jupyter_output><empty_output><jupyter_text>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 framesNote that the methods are similar to SQL JOIN clause.<jupyter_code>countries.join(pop_change, how='right')<jupyter_output><empty_output><jupyter_text>EXERCISE: Try inner and outer join. What's the difference?  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 structureSimilar to SQL `GROUP BY` The example of the image in pandas syntax:<jupyter_code>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()<jupyter_output><empty_output><jupyter_text>You can also simply count members of each split:<jupyter_code>df.groupby('key').size()<jupyter_output><empty_output><jupyter_text>Movie database These exercises are based on the [PyCon tutorial of <NAME>hodes](https://github.com/brandon-rhodes/pycon-pandas-tutorial/) (so all credit to him!) and the datasets he prepared for that. You can download these data from here: [`titles.csv`](https://drive.google.com/file/d/0B3G70MlBnCgKa0U4WFdWdGdVOFU/view?usp=sharing) and [`cast.csv`](https://drive.google.com/file/d/0B3G70MlBnCgKRzRmTWdQTUdjNnM/view?usp=sharing) and put them in the `/data` folder.<jupyter_code>cast = pd.read_csv('data/cast.csv')
cast[10:15]
titles = pd.read_csv('data/titles.csv')
titles.head()<jupyter_output><empty_output><jupyter_text>EXERCISE: Using groupby(), plot the number of films that have been released each year in the history of cinema.     EXERCISE: Use groupby() to determine how many roles are listed for each of The Pink Panther movies.  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:<jupyter_code>greek = ['α', 'β', 'β', 'β', 'β', 'α', 'β','α', 'α']
df.groupby(greek).max()<jupyter_output><empty_output><jupyter_text>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:<jupyter_code>df.groupby(df['data'] % 2).size()<jupyter_output><empty_output><jupyter_text>EXERCISE: Using groupby(), plot the number of films that have been released each **decade** in the history of cinema.     EXERCISE: Use groupby() to plot the number of "Hamlet" films made each decade.  Multiple groups Note that you can also groupby on multiple keys:<jupyter_code>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']<jupyter_output>data key  type
0     0   A  even
1     5   B   odd
2    10   C  even
3     5   A   odd
4    10   B  even
5    15   C   odd
6    10   A  even
7    15   B   odd
8    20   C  even<jupyter_text>Note that it creates a *hierarchical index*. More on that later.     EXERCISE: List each of the characters that <NAME> has portrayed at least twice.     EXERCISE: List, in order by year, each of the films in which <NAME> has played more than 1 role.     EXERCISE: How many leading (n=1) roles were available to actors, and how many to actresses, in each year of the 1950s?  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?<jupyter_code>titles.title.value_counts().head()<jupyter_output><empty_output><jupyter_text>EXERCISE: What are the 11 most common character names in movie history?  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:<jupyter_code>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)<jupyter_output><empty_output>