Grouping and Aggregating

Which manufacturer has the highest mean sugar content?

cereal['mfr'].value_counts()

mfr
K    23
G    22
P     9
     ..
Q     8
N     6
A     1
Name: count, Length: 7, dtype: int64

Often, we are interested in examining specific groups in our data.

Perhaps the question we want to answer from the cereal dataset is:

Which manufacturer has the highest mean sugar content?

We found in Module 1 using .value_counts() that there are 7 different manufacturers; K, G, P, R, Q, N and A.

Let’s start with “K”:

cereal[cereal['mfr'] == 'K'].mean(numeric_only=True)[['sugars']]

sugars    7.565217
dtype: float64

Next “G”:

cereal[cereal['mfr'] == 'G'].mean(numeric_only=True)[['sugars']]

sugars    7.954545
dtype: float64

To find the mean sugar content of each manufacturer, we could filter on each manufacturer and calculate the mean sugar content using .mean(). We can chain to make this process a little faster too.

Let’s start with “K” and then next “G”.

We could do this for the remaining 5 manufacturers. However, it’s obvious that this is time-consuming and a lot of work to do this repeatedly.

Imagine how tedious this would be if we had 100 different manufacturers?

Using groupby

mfr_group = cereal.groupby(by='mfr')
mfr_group

<pandas.core.groupby.generic.DataFrameGroupBy object at 0x7fb084daecc0>

Pandas has a solution for this.

It’s not uncommon to be interested in examining specific groups in our data. Hence there is a verb that is helpful in grouping like-rows together.

.groupby() allows us to group our data based on a specified column.

Let’s group our cereal dataframe on the mfr column and save it as object mfr_group.

This returns a DataFrame GroupBy object.

For example if we only had two manufacturers, the output would look like this.

What exactly is a groubpy object though?

mfr_group.groups

{'A': [43], 'G': [5, 7, 11, 12, 13, 14, 18, 22, 31, 36, 40, 42, 47, 51, 59, 69, 70, 71, 72, 73, 75, 76], 'K': [2, 3, 6, 16, 17, 19, 21, 24, 25, 26, 28, 38, 39, 46, 48, 49, 50, 53, 58, 60, 62, 66, 67], 'N': [0, 20, 63, 64, 65, 68], 'P': [9, 27, 29, 30, 32, 33, 34, 37, 52], 'Q': [1, 10, 35, 41, 54, 55, 56, 57], 'R': [4, 8, 15, 23, 44, 45, 61, 74]}

A DataFrame GroupBy object contains information about the groups of the dataframe.

We can access it with the .groups attribute (noun).

Reading carefully, we can see there are 7 groups: A, G, K, N, P, Q and R, and it lists the index labels (cereal names) in each group.

mfr_group.groups['K']

Index([2, 3, 6, 16, 17, 19, 21, 24, 25, 26, 28, 38, 39, 46, 48, 49, 50, 53, 58, 60, 62, 66, 67], dtype='int64')

We can obtain all the row index names of a group by specifying the group name in square brackets after the groups method.

Take the group K as an example.

mfr_group.get_group('K')

	name	mfr	type	calories	...	shelf	weight	cups	rating
2	All-Bran	K	Cold	70	...	3	1.0	0.33	59.425505
3	All-Bran with Extra Fiber	K	Cold	50	...	3	1.0	0.50	93.704912
6	Apple Jacks	K	Cold	110	...	2	1.0	1.00	33.174094
...	...	...	...	...	...	...	...	...	...
62	Rice Krispies	K	Cold	110	...	1	1.0	1.00	40.560159
66	Smacks	K	Cold	110	...	2	1.0	0.75	31.230054
67	Special K	K	Cold	110	...	1	1.0	1.00	53.131324

23 rows × 16 columns

We can get the full dataframe of the group K alone using the method .get_group().

cereal['mfr'].value_counts()

mfr
K    23
G    22
P     9
     ..
Q     8
N     6
A     1
Name: count, Length: 7, dtype: int64

mfr_group.size()

mfr
A     1
G    22
K    23
     ..
P     9
Q     8
R     8
Length: 7, dtype: int64

Similarly to how we made frequency tables using .value_counts(), we can now use .size() to obtain the number of rows in each group.

Summary Statistics with Groups

mfr_group = cereal.drop(columns=["name", "type"]).groupby(by='mfr')
mfr_group.mean()

	calories	protein	fat	sodium	...	shelf	weight	cups	rating
mfr
A	100.000000	4.000000	1.000000	0.000000	...	2.000000	1.000000	1.00000	54.850917
G	111.363636	2.318182	1.363636	200.454545	...	2.136364	1.049091	0.87500	34.485852
...	...	...	...	...	...	...	...	...	...
Q	95.000000	2.625000	1.750000	92.500000	...	2.375000	0.875000	0.82375	42.915990
R	115.000000	2.500000	1.250000	198.125000	...	2.000000	1.000000	0.87125	41.542997

7 rows × 13 columns

mfr_group.max()

	calories	protein	fat	sodium	...	shelf	weight	cups	rating
mfr
A	100	4	1	0	...	2	1.0	1.00	54.850917
G	140	6	3	290	...	3	1.5	1.50	51.592193
...	...	...	...	...	...	...	...	...	...
Q	120	5	5	220	...	3	1.0	1.00	63.005645
R	150	4	3	280	...	3	1.0	1.13	49.787445

7 rows × 13 columns

What now?

Grouping doesn’t answer our initial question of Which manufacturer has the highest mean sugar content?

Where do we go from here?

We need to calculate the mean sugar content in each manufacturing group! With a groupby object, this is super simple, as shown here.

Using .mean() on our groupby object answers the initial question and confirms that manufacturer “P” has the highest mean sugar content across cereals.

See how convenient this was to do in comparison to our initial method? Not only does this give us the result quicker, but it also gives us the mean of each column of the dataframe.

Think of how many filtering and mean calculations would have to be done if we were to do this using our initial approach.

Of course, using groups is not limited to finding only the mean. We can do the same thing for other statistics too like .min() and .max(), or many other operations.

Aggregating dataframes

cereal.select_dtypes(include=np.number).agg('mean')

calories    106.883117
protein       2.545455
fat           1.012987
               ...    
weight        1.029610
cups          0.821039
rating       42.665705
Length: 13, dtype: float64

cereal.mean(numeric_only=True)

calories    106.883117
protein       2.545455
fat           1.012987
               ...    
weight        1.029610
cups          0.821039
rating       42.665705
Length: 13, dtype: float64

In situations where we want to collect multiple statistics together, we can aggregate them in one step using a verb called .agg().

.agg() can be used on its own using a single measurement, without .groupby().

Using .agg() with only a mean input is essentially the same thing as calling the statistic mean() on the dataframe.

cereal.select_dtypes(include=np.number).agg(['max', 'min', 'median'])

	calories	protein	fat	sodium	...	shelf	weight	cups	rating
max	160.0	6.0	5.0	320.0	...	3.0	1.5	1.50	93.704912
min	50.0	1.0	0.0	0.0	...	1.0	0.5	0.25	18.042851
median	110.0	3.0	1.0	180.0	...	2.0	1.0	0.75	40.400208

3 rows × 13 columns

.agg() gets a chance to really shine when we want several specific measures.

Let’s say we want the max, min and median. We specify them in square brackets within our .agg() method.

This produces a convenient dataframe giving the value for each statistic, for each column.

Aggregating groupby objects

mfr_group.agg(['max', 'min', 'median'])

	calories			protein	...	cups	rating
	max	min	median	max	...	median	max	min	median
mfr
A	100	100	100.0	4	...	1.000	54.850917	54.850917	54.850917
G	140	100	110.0	6	...	0.875	51.592193	19.823573	36.181877
K	160	50	110.0	6	...	0.750	93.704912	29.924285	40.560159
...	...	...	...	...	...	...	...	...	...
P	120	90	110.0	3	...	0.670	53.371007	28.025765	40.917047
Q	120	50	100.0	5	...	0.875	63.005645	18.042851	47.419974
R	150	90	110.0	4	...	0.875	49.787445	34.139765	41.721976

7 rows × 39 columns

.agg() is particularly useful with groupby objects.

Let’s try it on our manufacturer groupby object named mfr_group.

This gives us a value for each group and for each statistic we specified.

For example:

Look at the ‘150’ in the bottom row on the far left under calories. The interpretation is that, for cases where the manufacturer is ‘R’, the max number of calories is 150.

In a similar manner if the manufacturer is ‘P’ the minumum amount of sodium is 45.

Extra Fancy Aggregation

mfr_group.agg({"calories":['max', 'min'],
               "rating":['sum'],  
               "sugars":['mean', 'median']})

	calories		rating	sugars
	max	min	sum	mean	median
mfr
A	100	100	54.850917	3.000000	3.0
G	140	100	758.688737	7.954545	8.5
K	160	50	1012.884634	7.565217	7.0
...	...	...	...	...	...
P	120	90	375.351697	8.777778	10.0
Q	120	50	343.327919	5.500000	6.0
R	150	90	332.343977	6.125000	5.5

7 rows × 5 columns

You might have noticed that when we used .agg(), we calculated the same 3 statistics for every column in the dataframe. But we can also calculate different statistics for different columns.

Let’s say we are concerned about the max and min calorie values, the total sum of the ratings and the mean and median sugar content for each manufacturing group.

We can achieve this by wrapping everything in curly brackets and using a colon to separate the column name from the statistics values. We need to put the statistics within square brackets.

The code is a bit more complicated, but the result is a bit easier to read.

Let’s apply what we learned!