Chapter 2 — Groupby-Aggregate

In this chapter, you’ll learn how to calculate statistics like the average number of kills per game for each champion. These statistics summarize (or aggregate) information about groups of observations in a dataset. Computing these statistics is easy with the groupby-aggregate operation, which acts in two steps:

We’ll get into the implementation details shortly, but before that, it will be helpful to start with the concept of data levels.

Data levels

The level of a table is a conceptual unit that a row represents. So, if you stored your match history in a table, that would be a match-level table, since each row corresponds to a match. Here are a few examples of tables from the same data source, where each contains rows representing different objects.

Table A

Table A stores players (or “participants”) in matches, with variables corresponding to a player’s kills and deaths in a match. Therefore, it is a (match,player)-level table. Table B stores the total kills and deaths for each team in a match, so it is a (match,team)-level table. Table C stores the total kills and deaths for each match, so it is a match-level table.

If you like, you can refer to Table A as a (match,team,player)-level table, since each row corresponds to a player on a particular team in a match. However, it is not necessary to include the team variable because each player can only be on one team.

In the last chapter, we learned to create new variables as functions of old ones, and to subset DataFrames. None of these operations change the data level of a table, but groupby-aggregate does.

Groupby-aggregate

The groupby-aggregate command allows us to calculate averages, totals, and much more. This can be a great way to summarize data to produce insights.

Suppose you are given a DataFrame that contains observations of players in matches, and you want to compute the average wards placed for each team. The original DataFrame is shown in Table 1, and the desired output is in Table 2.

matchId	teamId	wardsPlaced
3321609215	100	5
3321609215	100	11
3321609215	100	4
3321609215	100	5
3321609215	100	5
3321609215	200	6
3321609215	200	8
3321609215	200	8
3321609215	200	5
3321609215	200	5

Table 1: The original table

matchId	teamId	(mean) wardsPlaced
3321609215	100	6
3321609215	200	6.4

Table 2: The desired output

Groupby-aggregate: Groups are shown with colors and aggregation is shown in bold.

Using groupby-aggregate, the code to create Table 2 df2 from Table 1 table1 is as simple as:

The operation works in two steps:

STEP 1: Groupby step

Observations are assigned to groups defined by the groupby variables, as shown in color. Observations are put in the same group if they have the same value for every groupby variable. In the example, the groupby variables are matchId and teamId. In the table below, groups are shown with colors and the groupby variables are shown in bold.

STEP 2: Aggregate step

The specified aggregation function is applied to each group. In the example, the aggregation function computes the mean of wardsPlaced within each group of observations.

The .reset_index() command is optional; we’ll explain what it does later in the Indexes section.

Parameters in groupby-aggregate

There are two components that must be specified for any groupby-aggregate command: the grouping variable or variables (by) and the aggregating function or functions (func).

Pandas is flexible about how you specify aggregation functions. Each of the following is a valid input for the func parameter.

matchId	teamId	wardsPlaced	wardsKilled
3321609215	100	5	1
3321609215	100	11	4
3321609215	100	4	1
3321609215	100	5	2
3321609215	100	5	0
3321609215	200	6	1
3321609215	200	8	1
3321609215	200	8	3
3321609215	200	5	1
3321609215	200	5	0

Table 3: The original table

matchId	teamId	(mean) wardsPlaced	(max) wardsKilled
3321609215	100	6	4
3321609215	200	6.4	3

Table 4: The result of using two aggregation functions for different variables

Note: It’s important to give variables accurate names, especially after a groupby-aggregate command, which by default leaves variable names unchanged. For example, in Table 4, we should rename 'wardsPlaced' to 'avgWardsPlaced' to reflect the meaning of the new variable. The following command will rename the variables.

Alternatively, you can rename all of the variables in your DataFrame by assigning a list of new column names directly to the DataFrame.

If you do this, make sure you’ve got your variables in the same order as your names, or else they’ll be mismatched!

A noteworthy detail: Indexes

Every Pandas DataFrame has an Index, accessible via df.index, which is essentially just a list of row names. As we have seen in the previous chapter, we can use .iloc or .loc to slice a DataFrame based on the Index or variable names. For now, it’s not important to know the details of Indexes, but it’s good to know they exist so you don’t get confused when they show up.

When applying groupby-aggregate, the groups defined by the operation become the row labels for the resulting DataFrame in the form of an Index. (In the case of multiple grouping variables, it is a MultiIndex object.) This is a nice way of reminding us of the level of our new data, but can be inconvenient if you prefer working with regular columns. To convert this Index of a Dataframe back to columns, you can simply use .reset_index() on the DataFrame.

Let’s go back to Table 1. After this line of code

we get the following table.

matchId	teamId	wardsPlaced
3321609215	100	6
3321609215	200	6.4

Table 5: The result of groupby-aggregate without reset_index(). In this table, matchId and teamId are part of the index of the DataFrame.

matchId	teamId	wardsPlaced
3321609215	100	6
3321609215	200	6.4

Table 2 copied from earlier this chapter. In this table, matchId and teamId are columns.

Compared to the three variables of Table 2, Table 5 has only one variable (wardsPlaced), but with a MultiIndex with two keys.

Leaving the Indexes in, however, allows for easy selection or subsetting of the DataFrame using the .loc command. The syntax for slicing a DataFrame with a MultiIndex is also df.loc[row_labels, column_names] as we have seen in Chapter 1. The only difference is that we need to pass row_labels a tuple of row labels.

To subset this table, use df.loc[row_labels,col_labels] like we saw in Chapter 1. Instead of a string or number, row_labels will be a tuple (or list of tuples) corresponding to the desired row(s). For example, if we want to know the average wards placed by team 100 in this match, we can use:

Data

The tasks given here use the same data as the previous chapter, match_player_basic.csv.

Tasks