pandas GroupBy: How to Summarize a DataFrame Without Losing Track of Your Rows

1. Understanding groupby: split, apply, combine

The groupby operation in pandas works through a three-step process. First, it splits the DataFrame into separate mini-tables based on unique values in the grouping column(s). In this case, the DataFrame is divided into three groups: East, South, and West. Second, it applies a specified function, such as calculating the mean, to each mini-table individually. Lastly, it combines the results into a new table with one row per group. This process is referred to as split-apply-combine.

2. The basic usage: one column and one statistic

The fundamental command for summarizing a DataFrame using groupby is:

df.groupby('region')['amount'].mean()

This computes the mean of the 'amount' column for each unique 'region' value. For example, the East region has a mean amount of 158.00, South has 83.33, and West has 186.67. Note that the mean calculation in pandas automatically skips missing values (np.nan). When applied to the 'amount' column, it results in a Series with the group values as the index.

3. Multiple statistics simultaneously: using agg and named aggregation

Instead of computing just one statistic, you can calculate multiple statistics at once using the agg function. For instance:

df.groupby('region')['amount'].agg(['mean', 'sum', 'size', 'count'])

This returns a multi-index DataFrame, where each group has four summary statistics: mean, sum, count of rows, and size of the group. You can also use named aggregation for cleaner output:

df.groupby('region')['amount'].agg(mean='mean', total='sum', cnt='size', count='count')

This gives the same results but with named columns for each statistic.