<jupyter_start><jupyter_text>Groupby operations Some imports:<jupyter_code>%matplotlib inline
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
try:
    import seaborn
except ImportError:
    pass

pd.options.display.max_rows = 10<jupyter_output><empty_output><jupyter_text>Recap: the groupby operation (split-apply-combine)The "group by" concept: we want to **apply the same function on subsets of your dataframe, based on some key to split the dataframe in subsets**This operation is also referred to as the "split-apply-combine" operation, involving 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<jupyter_output><empty_output><jupyter_text>Using the filtering and reductions operations we have seen in the previous notebooks, we could do something like:    df[df['key'] == "A"].sum()    df[df['key'] == "B"].sum()    ...But pandas provides the `groupby` method to do this:<jupyter_code>df.groupby('key').aggregate('sum')  # np.sum
df.groupby('key').sum()<jupyter_output><empty_output><jupyter_text>Pandas does not only let you group by a column name. In `df.groupby(grouper)` can be many things:- Series (or string indicating a column in df)- function (to be applied on the index)- dict : groups by values- levels=[], names of levels in a MultiIndex<jupyter_code>df.groupby(lambda x: x % 2).mean()<jupyter_output><empty_output><jupyter_text>And now applying this on some real data These exercises are based on the [PyCon tutorial of <NAME>](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/open?id=0B3G70MlBnCgKajNMa1pfSzN6Q3M) and [`cast.csv`](https://drive.google.com/open?id=0B3G70MlBnCgKal9UYTJSR2ZhSW8) and put them in the `/data` folder. `cast` dataset: different roles played by actors/actresses in films- title: title of the film- name: name of the actor/actress- type: actor/actress- n: the order of the role (n=1: leading role)<jupyter_code>cast = pd.read_csv('data/cast.csv')
cast.head()
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 decade in the history of cinema.<jupyter_code>titles.groupby(titles.year // 10 * 10).size().plot(kind='bar')<jupyter_output><empty_output><jupyter_text>EXERCISE: Use groupby() to plot the number of "Hamlet" films made each decade.<jupyter_code>hamlet = titles[titles['title'] == 'Hamlet']
hamlet.groupby(hamlet.year // 10 * 10).size().plot(kind='bar')<jupyter_output><empty_output><jupyter_text>EXERCISE: How many leading (n=1) roles were available to actors, and how many to actresses, in each year of the 1950s?<jupyter_code>cast1950 = cast[cast.year // 10 == 195]
cast1950 = cast1950[cast1950.n == 1]
cast1950.groupby(['year', 'type']).size()<jupyter_output><empty_output><jupyter_text>EXERCISE: List the 10 actors/actresses that have the most leading roles (n=1) since the 1990's.<jupyter_code>cast1990 = cast[cast['year'] >= 1990]
cast1990 = cast1990[cast1990.n == 1]
cast1990.groupby('name').size().nlargest(10)<jupyter_output><empty_output><jupyter_text>EXERCISE: Use groupby() to determine how many roles are listed for each of The Pink Panther movies.<jupyter_code>c = cast
c = c[c.title == 'The Pink Panther']
c = c.groupby(['year'])[['n']].max()
c<jupyter_output><empty_output><jupyter_text>EXERCISE: List, in order by year, each of the films in which <NAME> has played more than 1 role.<jupyter_code>c = cast
c = c[c.name == '<NAME>']
g = c.groupby(['year', 'title']).size()
g[g > 1]<jupyter_output><empty_output><jupyter_text>EXERCISE: List each of the characters that <NAME> has portrayed at least twice.<jupyter_code>c = cast
c = c[c.name == '<NAME>']
g = c.groupby(['character']).size()
g[g > 1].sort_values()<jupyter_output><empty_output><jupyter_text>Transforms Sometimes you don't want to aggregate the groups, but transform the values in each group. This can be achieved with `transform`:<jupyter_code>df
df.groupby('key').transform('mean')
def normalize(group):
    return (group - group.mean()) / group.std()
df.groupby('key').transform(normalize)
df.groupby('key').transform('sum')<jupyter_output><empty_output><jupyter_text>EXERCISE: Add a column to the `cast` dataframe that indicates the number of roles for the film.<jupyter_code>cast['n_total'] = cast.groupby('title')['n'].transform('max')
cast.head()<jupyter_output><empty_output><jupyter_text>EXERCISE: Calculate the ratio of leading actor and actress roles to the total number of leading roles per decade.     Tip: you can to do a groupby twice in two steps,  once calculating the numbers, and then the ratios.<jupyter_code>leading = cast[cast['n'] == 1]
sums_decade = leading.groupby([cast['year'] // 10 * 10, 'type']).size()
sums_decade
#sums_decade.groupby(level='year').transform(lambda x: x / x.sum())
ratios_decade = sums_decade / sums_decade.groupby(level='year').transform('sum')
ratios_decade
ratios_decade[:, 'actor'].plot()
ratios_decade[:, 'actress'].plot()<jupyter_output><empty_output><jupyter_text>Intermezzo: string manipulations Python strings have a lot of useful methods available to manipulate or check the content of the string:<jupyter_code>s = 'Bradwurst'
s.startswith('B')<jupyter_output><empty_output><jupyter_text>In pandas, those methods (together with some additional methods) are also available for string Series through the `.str` accessor:<jupyter_code>s = pd.Series(['Bradwurst', 'Kartoffelsalat', 'Sauerkraut'])
s.str.startswith('B')<jupyter_output><empty_output><jupyter_text>For an overview of all string methods, see: http://pandas.pydata.org/pandas-docs/stable/api.htmlstring-handling     EXERCISE: We already plotted the number of 'Hamlet' films released each decade, but not all titles are exactly called 'Hamlet'. Give an overview of the titles that contain 'Hamlet', and that start with 'Hamlet':<jupyter_code>hamlets = titles[titles['title'].str.contains('Hamlet')]
hamlets['title'].value_counts()
hamlets = titles[titles['title'].str.match('Hamlet')]
hamlets['title'].value_counts()<jupyter_output><empty_output><jupyter_text>EXERCISE: List the 10 movie titles with the longest name.<jupyter_code>title_longest = titles['title'].str.len().nlargest(10)
title_longest
pd.options.display.max_colwidth = 210
titles.loc[title_longest.index]<jupyter_output><empty_output><jupyter_text>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: Which years saw the most films released?<jupyter_code>t = titles
t.year.value_counts().head(3)<jupyter_output><empty_output><jupyter_text>EXERCISE: Plot the number of released films over time<jupyter_code>titles.year.value_counts().sort_index().plot()<jupyter_output><empty_output><jupyter_text>EXERCISE: Plot the number of "Hamlet" films made each decade.<jupyter_code>t = titles
t = t[t.title == 'Hamlet']
(t.year // 10 * 10).value_counts().sort_index().plot(kind='bar')<jupyter_output><empty_output><jupyter_text>EXERCISE: What are the 11 most common character names in movie history?<jupyter_code>cast.character.value_counts().head(11)<jupyter_output><empty_output><jupyter_text>EXERCISE: Which actors or actresses appeared in the most movies in the year 2010?<jupyter_code>cast[cast.year == 2010].name.value_counts().head(10)<jupyter_output><empty_output><jupyter_text>EXERCISE: Plot how many roles <NAME> has played in each year of his career.<jupyter_code>cast[cast.name == '<NAME>'].year.value_counts().sort_index().plot()<jupyter_output><empty_output><jupyter_text>EXERCISE: What are the 10 most film titles roles that start with the word "The Life"?<jupyter_code>c = cast
c[c.title.str.startswith('The Life')].title.value_counts().head(10)<jupyter_output><empty_output><jupyter_text>EXERCISE: How many leading (n=1) roles were available to actors, and how many to actresses, in the 1950s? And in 2000s?<jupyter_code>c = cast
c = c[c.year // 10 == 195]
c = c[c.n == 1]
c.type.value_counts()
c = cast
c = c[c.year // 10 == 200]
c = c[c.n == 1]
c.type.value_counts()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13550384042187322, 'fraction_numerical': 0.018915510718789406, 'mean_word_length': 6.64592462751972, 'pattern_counts': {'":': 0, '<': 120, '<?xml version=': 0, '>': 123, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '28', 'dir': 'jupyter-structured-clean-dedup', 'id': '343062', 'license': 'BSD-2-Clause', 'n_tokens_mistral': 3137, 'n_tokens_neox': 2936, 'n_words': 797, 'path': 'solved - 04b - Operaciones GroupBy Avanzadas.ipynb', 'repo_name': 'Fireblend/pandas-tutorial', 'size': '8745'}