Pandas Grouping
| |
I. Grouping Patterns and Their Objects
1. The General Pattern of Grouping
Grouping operations are ubiquitous in daily life. For example:
- Group by gender to compute the average lifespan of the national population
- Group by season and standardize the temperatures within each season
- Group by class and filter out classes whose mean math score exceeds 80
From the examples above, three elements are clearly required for a grouping operation: the grouping key, the data source, and the operation along with its return value. Viewed from the other direction, once these three aspects are specified, a grouping operation is fully determined. The general form of grouping code is therefore:
| |
For instance, the code for the first example should look like this:
| |
Let us return to the student fitness dataset. To compute the median height by gender, we can write:
| |
Gender
Female 159.6
Male 173.4
Name: Height, dtype: float64
2. The Essence of the Grouping Key
All the examples mentioned so far group the data along a single dimension, such as gender. What if we need to group by several dimensions at once? In fact, it suffices to pass groupby a list of the corresponding column names. For example, to group by school and gender and compute the mean height, we write:
| |
School Gender
Fudan University Female 158.776923
Male 174.212500
Peking University Female 158.666667
Male 172.030000
Shanghai Jiao Tong University Female 159.122500
Male 176.760000
Tsinghua University Female 159.753333
Male 171.638889
Name: Height, dtype: float64
Up to this point, every grouping key we have used could be fetched directly from a column by name. Suppose instead we want to group by a more elaborate rule - say, whether a student’s weight exceeds the overall mean - while still computing the mean height.
First, write out the grouping condition:
| |
Then pass it to groupby:
| |
Weight
False 159.034646
True 172.705357
Name: Height, dtype: float64
[Exercise]
Split weight into three groups - high, normal, and low - using the upper and lower quartiles, and compute the mean height of each group.
[END]
As the index shows, the resulting groups are in fact formed according to the values in the condition list (True and False here). Let us verify this idea by passing in a random sequence of letters:
| |
a 163.094828
b 163.874603
c 162.666129
Name: Height, dtype: float64
The index here is simply the elements of the original item. If several sequences are passed to groupby, the grouping key becomes the unique combination of the corresponding rows across those sequences:
| |
Weight
False a 159.334146
b 159.257143
c 158.543182
True a 172.164706
b 173.109524
c 172.744444
Name: Height, dtype: float64
This shows that passing column names earlier was only a convenient shorthand: it is equivalent to passing one or more columns, and the actual groups come from the unique values of the combined data source. Using drop_duplicates reveals the specific group categories:
| |
| School | Gender | |
|---|---|---|
| 0 | Shanghai Jiao Tong University | Female |
| 1 | Peking University | Male |
| 2 | Shanghai Jiao Tong University | Male |
| 3 | Fudan University | Female |
| 4 | Fudan University | Male |
| 5 | Tsinghua University | Female |
| 9 | Peking University | Female |
| 16 | Tsinghua University | Male |
| |
School Gender
Fudan University Female 158.776923
Male 174.212500
Peking University Female 158.666667
Male 172.030000
Shanghai Jiao Tong University Female 159.122500
Male 176.760000
Tsinghua University Female 159.753333
Male 171.638889
Name: Height, dtype: float64
3. The GroupBy Object
Notice that the methods actually used for grouping all come from a groupby object in pandas. This object defines many methods and also exposes a few convenient attributes.
| |
<pandas.core.groupby.generic.DataFrameGroupBy object at 0x000001C1E7AB1408>
The ngroups attribute gives the number of groups:
| |
16
The groups attribute returns a dictionary mapping $\color{#FF0000}{\text{group names}}$ to $\color{#FF0000}{\text{lists of group indexes}}$:
| |
dict_keys([('Fudan University', 'Freshman'), ('Fudan University', 'Junior'), ('Fudan University', 'Senior'), ('Fudan University', 'Sophomore'), ('Peking University', 'Freshman'), ('Peking University', 'Junior'), ('Peking University', 'Senior'), ('Peking University', 'Sophomore'), ('Shanghai Jiao Tong University', 'Freshman'), ('Shanghai Jiao Tong University', 'Junior'), ('Shanghai Jiao Tong University', 'Senior'), ('Shanghai Jiao Tong University', 'Sophomore'), ('Tsinghua University', 'Freshman'), ('Tsinghua University', 'Junior'), ('Tsinghua University', 'Senior'), ('Tsinghua University', 'Sophomore')])
[Exercise]
The previous subsection showed how to obtain the concrete group categories with drop_duplicates. Now use the groups attribute to achieve something similar.
[END]
When size is used as an attribute of a DataFrame, it returns the number of rows multiplied by the number of columns; on a groupby object, however, it counts the elements in each group:
| |
School Grade
Fudan University Freshman 9
Junior 12
Senior 11
Sophomore 8
Peking University Freshman 13
Junior 8
Senior 8
Sophomore 5
Shanghai Jiao Tong University Freshman 13
Junior 17
Senior 22
Sophomore 5
Tsinghua University Freshman 17
Junior 22
Senior 14
Sophomore 16
dtype: int64
The get_group method retrieves the rows belonging to a given group directly, provided you know the group’s exact name:
| |
| School | Grade | Name | Gender | Height | Weight | Transfer | Test_Number | Test_Date | Time_Record | |
|---|---|---|---|---|---|---|---|---|---|---|
| 15 | Fudan University | Freshman | Changqiang Yang | Female | 156.0 | 49.0 | N | 3 | 2020/1/1 | 0:05:25 |
| 28 | Fudan University | Freshman | Gaoqiang Qin | Female | 170.2 | 63.0 | N | 2 | 2020/1/7 | 0:05:24 |
| 63 | Fudan University | Freshman | Gaofeng Zhao | Female | 152.2 | 43.0 | N | 2 | 2019/10/31 | 0:04:00 |
| 70 | Fudan University | Freshman | Yanquan Wang | Female | 163.5 | 55.0 | N | 1 | 2019/11/19 | 0:04:07 |
| 73 | Fudan University | Freshman | Feng Wang | Male | 176.3 | 74.0 | N | 1 | 2019/9/26 | 0:03:31 |
| 105 | Fudan University | Freshman | Qiang Shi | Female | 164.5 | 52.0 | N | 1 | 2019/12/11 | 0:04:23 |
| 108 | Fudan University | Freshman | Yanqiang Xu | Female | 152.4 | 38.0 | N | 1 | 2019/12/8 | 0:05:03 |
| 157 | Fudan University | Freshman | Xiaoli Lv | Female | 152.5 | 45.0 | N | 2 | 2019/9/11 | 0:04:17 |
| 186 | Fudan University | Freshman | Yanjuan Zhao | Female | NaN | 53.0 | N | 2 | 2019/10/9 | 0:04:21 |
Here we have listed two attributes and two methods, whereas the earlier mean and median are also methods on the groupby object. These functions work in much the same way as many others and will be introduced in detail in later sections.
4. The Three Major Grouping Operations
Now that we have covered the basics of grouping, let us return to the three examples at the beginning and look at them with fresh eyes: the data they return come in three different shapes.
- In the first example, each group yields a single scalar - a mean, a median, the group size
size, and so on. - In the second example, the original series is standardized, so each group returns a
Series. - In the third example, the result is neither a scalar nor a series; it is the rows of the entire group, hence a
DataFrame.
This leads to the three major grouping operations: aggregation, transformation, and filtering, corresponding to our three examples. We will now introduce the associated agg, transform, and filter functions and how they operate.
II. Aggregation Functions
1. Built-in Aggregation Functions
Before discussing agg, we should look at the aggregation functions defined directly on the groupby object, since their speed is usually internally optimized and they should be your first choice. Functions that return a scalar include: max/min/mean/median/count/all/any/idxmax/idxmin/mad/nunique/skew/quantile/sum/std/var/sem/size/prod.
| |
Gender
Female 143
Male 199
Name: Height, dtype: int64
| |
Gender
Female 166.8
Male 185.9
Name: Height, dtype: float64
[Exercise]
Consult the documentation and clarify what the functions all/any/mad/skew/sem/prod do.
[END]
When the data source passed to these aggregation functions contains several columns, the computation is carried out column by column:
| |
| Height | Weight | |
|---|---|---|
| Gender | ||
| Female | 170.2 | 63.0 |
| Male | 193.9 | 89.0 |
2. The agg Method
Although the groupby object offers many convenient functions, the following limitations remain:
- Several functions cannot be applied at the same time
- A specific aggregation function cannot be assigned to a specific column
- Custom aggregation functions cannot be used
- Result column names cannot be customized before aggregation
Let us see how the agg function resolves these four issues.
[a] Using Multiple Functions
To apply several aggregation functions, pass their built-in string names in a list; every string mentioned earlier is valid.
| |
| Height | Weight | |||||
|---|---|---|---|---|---|---|
| sum | idxmax | skew | sum | idxmax | skew | |
| Gender | ||||||
| Female | 21014.0 | 28 | -0.219253 | 6469.0 | 28 | -0.268482 |
| Male | 8854.9 | 193 | 0.437535 | 3929.0 | 2 | -0.332393 |
As the result shows, the column index is now a multi-level index: the first level is the data source and the second the aggregation method used. Aggregation is applied to each column separately, giving six columns in total.
[b] Applying Specific Aggregation Functions to Specific Columns
A special correspondence between methods and columns can be expressed by passing a dictionary to agg, with column names as keys and aggregation strings (or lists of strings) as values.
| |
| Height | Weight | ||
|---|---|---|---|
| mean | max | count | |
| Gender | |||
| Female | 159.19697 | 170.2 | 135 |
| Male | 173.62549 | 193.9 | 54 |
[Exercise]
Use the dictionary-passing approach from [b] to carry out the equivalent aggregation task in [a].
[END]
[c] Using Custom Functions
A concrete custom function can be used in agg; keep in mind that $\color{#FF0000}{\text{the function receives columns from the original data source and computes each column separately}}$. Here we compute the range of height and weight within each group:
| |
| Height | Weight | |
|---|---|---|
| Gender | ||
| Female | 13.79697 | 13.918519 |
| Male | 17.92549 | 21.759259 |
[Exercise]
The describe method on a groupby object summarizes statistics. Use several aggregation functions at once to reproduce what that method does.
[END]
Because a series is passed in, any method or attribute available on a series can be used inside the function, as long as the return value is a scalar. The next example returns High when a group’s mean for an indicator exceeds the overall mean of that indicator, and Low otherwise.
| |
| Height | Weight | |
|---|---|---|
| Gender | ||
| Female | Low | Low |
| Male | High | High |
[d] Renaming Aggregation Results
To rename the columns of an aggregation result, replace each function above with a tuple whose first element is the new name and whose second is the original function - either an aggregation string or a custom function. A few examples will make this clear:
| |
| Height | Weight | |||
|---|---|---|---|---|
| range | my_sum | range | my_sum | |
| Gender | ||||
| Female | 24.8 | 21014.0 | 29.0 | 6469.0 |
| Male | 38.2 | 8854.9 | 38.0 | 3929.0 |
| |
| Height | Weight | ||
|---|---|---|---|
| my_func | sum | <lambda> | |
| Gender | |||
| Female | Low | 21014.0 | 63.0 |
| Male | High | 8854.9 | 89.0 |
One more point deserves attention: when a single aggregation is applied to one or several columns, renaming requires an extra pair of square brackets. Otherwise pandas cannot tell whether you mean a new name or have simply misspelled a built-in function string:
| |
| Height | Weight | |
|---|---|---|
| my_sum | my_sum | |
| Gender | ||
| Female | 21014.0 | 6469.0 |
| Male | 8854.9 | 3929.0 |
| |
| Height | Weight | ||
|---|---|---|---|
| my_func | sum | range | |
| Gender | |||
| Female | Low | 21014.0 | 63.0 |
| Male | High | 8854.9 | 89.0 |
III. Transformation and Filtering
1. Transformation Functions and the transform Method
A transformation function returns a series of the same length. The most common built-in transformations are the cumulative functions cumcount/cumsum/cumprod/cummax/cummin; they are used in the same way as aggregation functions, except that they accumulate within each group. In addition, the groupby object defines filling and sliding-window transformations, whose general forms are discussed in Chapters 7 and 10 and are skipped here.
| |
| Height | Weight | |
|---|---|---|
| 0 | 158.9 | 46.0 |
| 1 | 166.5 | 70.0 |
| 2 | 188.9 | 89.0 |
| 3 | NaN | 46.0 |
| 4 | 188.9 | 89.0 |
[Exercise]
On a groupby object, the rank method is another useful transformation. Look up what it does and give an example of its use.
[END]
For a custom transformation we use the transform method. The custom function receives $\color{#FF0000}{\text{a Series from the data source}}$, just as with agg, but its final result is a DataFrame whose row and column indexes match those of the data source.
Let us standardize height and weight within each group, that is, subtract the group mean and divide by the group standard deviation:
| |
| Height | Weight | |
|---|---|---|
| 0 | -0.058760 | -0.354888 |
| 1 | -1.010925 | -0.355000 |
| 2 | 2.167063 | 2.089498 |
| 3 | NaN | -1.279789 |
| 4 | 0.053133 | 0.159631 |
[Exercise]
Unlike agg, transform cannot take a dictionary that assigns different transformations to different columns. If you need that behavior within a single transform call, how would you do it?
[END]
We said earlier that transform returns a series of the same length, but it can also return a scalar; in that case the scalar is broadcast across the entire group. This technique of $\color{#FF0000}{\text{scalar broadcasting}}$ is very common in feature engineering. For example, we can build two new features giving the mean height and mean weight of each sample’s gender group:
| |
| Height | Weight | |
|---|---|---|
| 0 | 159.19697 | 47.918519 |
| 1 | 173.62549 | 72.759259 |
| 2 | 173.62549 | 72.759259 |
| 3 | 159.19697 | 47.918519 |
| 4 | 173.62549 | 72.759259 |
2. Group Indexing and Filtering
The previous chapter introduced indexing, so how does indexing differ from filtering?
Filtering, in the context of grouping, operates on groups, whereas indexing operates on rows. The return values discussed in Chapter 2 - whether a list of booleans, a list of elements, or a list of positions - are all essentially row selections: a row is kept if it meets the condition and discarded otherwise.
Group filtering generalizes row filtering: a group is kept when the statistic computed over all of its rows returns True, and discarded when it returns False. Finally, the rows of every surviving group are concatenated into a single DataFrame and returned.
The groupby object defines a filter method for this purpose. Its custom function receives the DataFrame formed by the data source itself - in the groupby object defined above, that is df[['Height', 'Weight']]. Consequently, all table methods and attributes are available inside the function, and it only needs to return a boolean value.
For example, to keep every group with more than 100 rows from the original table:
| |
| Height | Weight | |
|---|---|---|
| 0 | 158.9 | 46.0 |
| 3 | NaN | 41.0 |
| 5 | 158.0 | 51.0 |
| 6 | 162.5 | 52.0 |
| 7 | 161.9 | 50.0 |
[Exercise]
Conceptually, indexing is a subset of group filtering. Use filter to reproduce the behavior of loc[...], assuming “…” is a list of elements.
[END]
IV. Cross-Column Grouping
1. Introducing apply
The previous sections covered the three major grouping operations, yet there is another common scenario that none of them can handle. Suppose body mass index is defined as follows:
$${\rm BMI} = {\rm\frac{Weight}{Height^2}}$$Here weight and height are measured in kilograms and meters, respectively, and we need to compute the group-wise mean of BMI.
First, this is clearly not a filtering operation, so filter will not do. Second, the mean is a scalar rather than a series, ruling out transform. Finally, agg might appear to work, but aggregation functions process one column at a time and cannot $\color{#FF0000}{\text{process several columns at once}}$. This is where apply comes in.
2. Using apply
By design, apply passes the same arguments to its custom function as filter does; the difference is that filter only allows a boolean result. Here is how the calculation above can be solved:
| |
Gender
Female 18.860930
Male 24.318654
dtype: float64
Besides scalars, apply can return one-dimensional Series objects and two-dimensional DataFrame objects. How, then, do the dimensionality of the resulting frame and the number of index levels change? Three examples make the rules clear:
[a] Scalar case: the result is a Series whose index matches that of agg
| |
Gender Test_Number
Female 1 0
2 0
3 0
Male 1 0
2 0
3 0
dtype: int64
| |
Gender Test_Number
Female 1 [0, 0]
2 [0, 0]
3 [0, 0]
Male 1 [0, 0]
2 [0, 0]
3 [0, 0]
dtype: object
[b] Series case: the result is a DataFrame; its row index matches the scalar case, and its column index is the index of the returned Series
| |
| a | b | ||
|---|---|---|---|
| Gender | Test_Number | ||
| Female | 1 | 0 | 0 |
| 2 | 0 | 0 | |
| 3 | 0 | 0 | |
| Male | 1 | 0 | 0 |
| 2 | 0 | 0 | |
| 3 | 0 | 0 |
[Exercise]
In the custom function passed to apply, try returning a Series of the same length but with a different index based on some feature of the group. Will an error be raised?
[END]
[c] DataFrame case: the result is also a DataFrame. To the innermost row index of each group’s original agg result, one more level is appended from the returned DataFrame’s row index; meanwhile, the column index of the grouped result is identical to that of the returned DataFrame.
| |
| w | y | |||
|---|---|---|---|---|
| x | z | |||
| Gender | Test_Number | |||
| Female | 1 | a | 1.0 | 1.0 |
| b | 1.0 | 1.0 | ||
| 2 | a | 1.0 | 1.0 | |
| b | 1.0 | 1.0 | ||
| 3 | a | 1.0 | 1.0 | |
| b | 1.0 | 1.0 | ||
| Male | 1 | a | 1.0 | 1.0 |
| b | 1.0 | 1.0 | ||
| 2 | a | 1.0 | 1.0 | |
| b | 1.0 | 1.0 | ||
| 3 | a | 1.0 | 1.0 | |
| b | 1.0 | 1.0 |
[Exercise]
In the custom function passed to apply, try returning a DataFrame of the same size but with different column labels based on some feature of the group. Will an error be raised? What if only the row index differs?
[END]
Finally, it must be emphasized that the flexibility of apply comes at the cost of some performance. Unless cross-column processing is genuinely needed, prefer one of the specially designed groupby methods, or you will pay a substantial speed penalty. Likewise, when using aggregation and transformation functions, favor the built-in implementations: they are highly optimized and are generally faster than equivalent custom functions.
[Exercise]
The groupby object also defines cov and corr, which conceptually are forms of cross-column grouping. Using the gb object defined earlier, reproduce gb.cov() with apply and compare their performance.
[END]
V. Exercises
Ex1: The Car Dataset
We have a dataset of cars in which Brand, Disp., HP stand for the brand, engine displacement, and engine output, respectively.
| |
| Brand | Price | Country | Reliability | Mileage | Type | Weight | Disp. | HP | |
|---|---|---|---|---|---|---|---|---|---|
| 0 | Eagle Summit 4 | 8895 | USA | 4.0 | 33 | Small | 2560 | 97 | 113 |
| 1 | Ford Escort 4 | 7402 | USA | 2.0 | 33 | Small | 2345 | 114 | 90 |
| 2 | Ford Festiva 4 | 6319 | Korea | 4.0 | 37 | Small | 1845 | 81 | 63 |
- First filter out cars whose
Countryappears no more than twice in the whole dataset, keeping only those from countries with more than two cars. Then group byCountryand compute the mean price, the coefficient of variation of price, and the number of cars perCountry. The coefficient of variation is the standard deviation divided by the mean; rename itCoVin the result. - Split the rows by position into the first, middle, and last thirds, and compute the mean of
Pricefor each group. - Group by
Type, compute the maximum and minimum ofPriceandHPseparately, and - since this produces a multi-level index - join the column levels with an underscore to form a flat index. - Group by
Typeand apply min-max normalization toHPwithin each group. - Group by
Typeand compute the correlation coefficient betweenDisp.andHP.
Ex2: Implementing the transform Function
- The
groupbyobject is constructed asmy_groupby(df, group_cols) - Support grouping by a single column and by several columns
- Support
my_groupby(df)[col].transform(my_func)with scalar broadcasting pandas’transformcannot compute across columns; support that too by still returning aSerieswhile allowingcolto name several columns There is no need to handle performance or exceptions: just implement the features above. Provide test cases and check that the results agree withpandas’transform.
