pandas 之 group by 过程
import numpy as np
import pandas as pd
Categorizing a dataset and applying a function to each group whether an aggregation(聚合) or transformation(转换), is often a critical(关键性的) component of a data analysis workflow.
(对数据集进行分类并将函数应用于每个组，无论是聚合还是转换，通常都是数据分析的关键组成部分)
After loading, merging, and preparing a dataset, you may need to compute group statistics or possibly pivot tables for reporting or visualization purpose.
(加载、合并和准备数据集之后，可能需要计算组统计数据，或者可能需要数据透视表来进行报告或可视化.)
pandas provodes a flexible groupby interface, enabling you to slice, dice, and summarize datasets in a natural way.
One reason for the populatity of relational database SQL is the easy with wich data can be joined, filtered, transformed and aggregation.
(关系数据库SQL流行的一个原因是，它可以方便地连接、过滤、转换和聚合数据)
However, query language like SQL are somewhat constrained(受限于) in the kinds of group operations that can be perform. As you will see, with the expressiveness of Python and pandas, we can perform quite complex group operation by utilizing any function that accepts a pandas object or NumPy array. In this chapter, you will learn how to:
- Split a pandas object into piece using one or more keys(in the form of functions, array, or DataFrame column names) 使用多个键将padnas对象分割
- Calculate group summary statistics, like count, mean, or standard deviation, or a user-define function 计算组汇总统计信息，如计数、平均值、标准差或用户定义函数
- Apply within-group transformations or other manipulations like normalization, linear regression, rank or subset selection.组内转换或其他操作，如标准化，线性回归，rank, 选取子集
- Compute pivot talbe and cross-tabulations (交叉, 透视表)
- Perform quantile analysis and other statistics group analyses 分位数统计和其他分析
Aggregationg of time series data, a special use case of groupby, is refered to as resampling(重采样) in this book and will receive separate treatment in Chapter 11
GroupBy 过程
- key -> data -> split -> apply -> combine
- cj 想到了大数据的 MapReduce
Hadley Wichham, an author of many popular package for the R programmng language, coine the term(提出了一个术语) split-apply-combine for describling group oprations.
In the first stage of process, data contained in a pandas object, whether a Series, DataFrame, or otherwise, is split into groups based on one or more keys that you provide The splitting is performed on a praticular axis fo an object. For example, a DataFrame can be grouped on its rows(axis=0) or its columns(axis=1).
Once this done, a function is applied to each group, producing a new value.
Finally, the results of all those function applications are combined into a result object. The form of the resulting object will usually depend on what's being done to the data.
key -> data -> split -> apply -> combine
Each grouping key can take many forms, and the keys do not have to be all of the same type:
- A list of array of values that is the same length as the axis being grouped (等轴长的列表作为分割key)
- A value indicating a column name in a DataFrame (列字段分割)
- A dict or Series giving a correspondence(一致) between the values on the axis being grouped and the group names (字典or Series)
- A function to be invoked on axis index or the individual labels in the index (函数映射在轴索引上)
Note that the latter three methods are shortcuts for producing an array of values to be used to split up the object. Don't worry if this all seems abstract. Throughout this chapter, I will give many examples of all these methods. To get started, here is a small bablular dataset as a DataFrame:
df = pd.DataFrame({
'key1': 'a a b b a'.split(),
'key2': ['one', 'two', 'one', 'two', 'one'],
'data1': np.random.randn(5),
'data2': np.random.randn(5)
})
df
| key1 | key2 | data1 | data2 | |
|---|---|---|---|---|
| 0 | a | one | -2.043830 | 0.364327 |
| 1 | a | two | -0.595880 | 1.066501 |
| 2 | b | one | -0.706536 | 0.936099 |
| 3 | b | two | -1.444520 | -0.561796 |
| 4 | a | one | -1.632010 | -0.188685 |
Suppose you wanted to compute the mean of the data1 column using the lables from key1(以key1分组, 计算data1的均值). There are the number of ways to do this. One is to access data1 and call groupby with the column (s Series) at key1:
grouped = df['data1'].groupby(df['key1'])
grouped
<pandas.core.groupby.groupby.SeriesGroupBy object at 0x000001EDE9DDCB00>
This grouped variable is now a GropBy object. It has not actually computed anything except for some intermediate data about the group key df['key1']. The idea is that this object has all of the infomation needed to then apply some operation to each of the groups. For example, to compute group means we can call the GroupBy's mean method:
(groupby 会生成一个对象, 并不做计算, 计算需调用方法, 然后会将其映射到各个分组中)
grouped.mean()
key1
a -1.423906
b -1.075528
Name: data1, dtype: float64
Later, I'll explain more about what happens when you call .mean(). The important things here is that the data (a Series) has been aggregate(聚合) according to the group key producing a new Series that is now indexed by unique values in the key1 column.
The result index has the name 'key1' because the DataFrame columns df['key1'] did.
If instead we had passed multiple arrays as list, we'd get something different:
"多个键进行分组索引"
means = df['data1'].groupby([df['key1'], df['key2']]).mean()
means
'多个键进行分组索引'
key1 key2
a one -1.837920
two -0.595880
b one -0.706536
two -1.444520
Name: data1, dtype: float64
Here we grouped the data using two keys, and the resulting Series now has a hierarchical consisting of the unique pairs of keys observed:
"unstack => 将S -> Df"
means.unstack()
'unstack => 将S -> Df'
| key2 | one | two |
|---|---|---|
| key1 | ||
| a | -1.837920 | -0.59588 |
| b | -0.706536 | -1.44452 |
In this example, the group keys are all Series, though they could be any arrays of the right length.
"自定义分组"
states = np.array(['Ohio', 'California', 'California', 'Ohio', 'Ohio'])
years = np.array([2005, 2005, 2006, 2005, 2006])
df['data1'].groupby([states, years]).mean()
'自定义分组'
California 2005 -0.595880
2006 -0.706536
Ohio 2005 -1.744175
2006 -1.632010
Name: data1, dtype: float64
Frequently the grouping infomation is found in the same DataFrame as the data you want to work. In that case, you can pass column names(whether those are strings, numbers, or other Python objects) as the group keys:
通常，分组信息与要处理的数据位于相同的DaFrame中。在这种情况下，可以将列名(无论是字符串、数字还是其他Python对象)作为组键传递.
df.groupby('key1').mean()
| data1 | data2 | |
|---|---|---|
| key1 | ||
| a | -1.423906 | 0.414048 |
| b | -1.075528 | 0.187151 |
df.groupby(['key1', 'key2']).mean()
| data1 | data2 | ||
|---|---|---|---|
| key1 | key2 | ||
| a | one | -1.837920 | 0.087821 |
| two | -0.595880 | 1.066501 | |
| b | one | -0.706536 | 0.936099 |
| two | -1.444520 | -0.561796 |
You may have noticed in the first case df.groupby('key1').mean() that there is no key2 columns in the result. Because df['key2'] is not numeric data, it is said to be a nuisance column, which is therefore excluded from the result. By default, all of the numeric columns are aggregated, though it's possible to filter down to a subset, as you'll see soon.
Regardless of the objective in using groupby, a general useful GroupBy method is size which returns a Series containing group size.
df.groupby(['key1', 'key2']).size() # 分组统计
key1 key2
a one 2
two 1
b one 1
two 1
dtype: int64
Group 对象的可迭代
The GroupBy object supports iteration, generating a sequence of 2-tuples containing the group name along with the chunk of data. Consider the following:
(支持迭代, 生成包含组名和数据块的二元序列)
for name, group in df.groupby('key1'):
print(name)
print(group)
a
key1 key2 data1 data2
0 a one -2.04383 0.364327
1 a two -0.59588 1.066501
4 a one -1.63201 -0.188685
b
key1 key2 data1 data2
2 b one -0.706536 0.936099
3 b two -1.444520 -0.561796
In the case of multiple keys, the first element in the tuple will be a tuple of key values.
(在多个键的情况下, 首元素将会被作为元组值的主键)
for (k1, k2), group in df.groupby(['key1', 'key2']):
print(k1, k2)
print(group)
a one
key1 key2 data1 data2
0 a one -2.04383 0.364327
4 a one -1.63201 -0.188685
a two
key1 key2 data1 data2
1 a two -0.59588 1.066501
b one
key1 key2 data1 data2
2 b one -0.706536 0.936099
b two
key1 key2 data1 data2
3 b two -1.44452 -0.561796
Of course, you can choose to do whatever you want with the pieces of data. A recipe you may find useful is computing a dict of the data pieces as a one-line.
(按照字典分组)
pieces = dict(list(df.groupby('key1')))
pieces['b']
| key1 | key2 | data1 | data2 | |
|---|---|---|---|---|
| 2 | b | one | -0.706536 | 0.936099 |
| 3 | b | two | -1.444520 | -0.561796 |
By default groupby groups on axis=0, but you can group on any of the other axes. For example, we could group the columns of our example df here by dtype like so:
grouped = df.groupby('key1')
for dtype, group in grouped:
print(dtype)
print(group)
a
key1 key2 data1 data2
0 a one -2.04383 0.364327
1 a two -0.59588 1.066501
4 a one -1.63201 -0.188685
b
key1 key2 data1 data2
2 b one -0.706536 0.936099
3 b two -1.444520 -0.561796
对分组对象选取子集
Indexing a GroupBy object created from a DataFrame with a column name or array of column names has the effect of column subsetting for aggregation. This means that:
df.groupby('key1')['data1']
df.groupby('key1')[['data2']]
<pandas.core.groupby.groupby.SeriesGroupBy object at 0x000001EDEEF248D0>
<pandas.core.groupby.groupby.DataFrameGroupBy object at 0x000001EDEEF24080>
Especially for large datasets, it may be desirable to aggregate only a few columns. For exmaple, in the preceding dataset, to compute means for just the data2 column and get the result as a DataFrame, we could write:
df.groupby(['key1', 'key2'])[['data2']].mean()
| data2 | ||
|---|---|---|
| key1 | key2 | |
| a | one | 0.087821 |
| two | 1.066501 | |
| b | one | 0.936099 |
| two | -0.561796 |
The object returned by this indexing operation is a grouped DataFrame if a list or array is passed or a grouped Series if only a single column name is passed as a scalar:
s_grouped = df.groupby(['key1', 'key2'])['data2']
s_grouped
<pandas.core.groupby.groupby.SeriesGroupBy object at 0x000001EDEEBD4320>
s_grouped.mean()
key1 key2
a one 0.087821
two 1.066501
b one 0.936099
two -0.561796
Name: data2, dtype: float64
按字典or序列分组
Grouping information may exist in a form other than an array. Let's consider another example DataFrame:
people = pd.DataFrame(np.random.randn(5, 5),
columns=['a', 'b', 'c', 'd', 'e'],
index=['Joe', 'Steve', 'Wes', 'Jim', 'Travis'])
people.iloc[2:3, [1, 2]] = np.nan # 第3行, 第2,3列
people # 前闭后开的还是
| a | b | c | d | e | |
|---|---|---|---|---|---|
| Joe | -0.629941 | -0.537660 | 0.392397 | -0.489149 | -1.668533 |
| Steve | -0.941174 | 0.926352 | 0.858621 | -0.005732 | 0.289938 |
| Wes | -2.316073 | NaN | NaN | 0.765298 | 0.107972 |
| Jim | 0.169023 | -0.859168 | -0.408575 | 0.928599 | -1.519773 |
| Travis | 0.913253 | 0.851410 | 0.672238 | -0.040455 | 0.722729 |
Now, suppose I have a group correspondence for the columns and want to sum together the columns by group:
mapping = {
'a':'red', 'b':'red', 'c':'blue',
'd':'blue', 'e':'red', 'f':'orange'
}
Now, you could construct an array from this dict to pass to groupby , but instead we can just pass the dict
by_column = people.groupby(mapping, axis=1)
by_column.sum()
| blue | red | |
|---|---|---|
| Joe | -0.096752 | -2.836134 |
| Steve | 0.852889 | 0.275115 |
| Wes | 0.765298 | -2.208101 |
| Jim | 0.520024 | -2.209918 |
| Travis | 0.631783 | 2.487391 |
The same functionality holds for Series, which can be viewed as a fixed-size mappinf:
map_series = pd.Series(mapping)
map_series
a red
b red
c blue
d blue
e red
f orange
dtype: object
people.groupby(map_series, axis=1).count()
| blue | red | |
|---|---|---|
| Joe | 2 | 3 |
| Steve | 2 | 3 |
| Wes | 1 | 2 |
| Jim | 2 | 3 |
| Travis | 2 | 3 |
按函数分组
Using Python functions is a more generic way of defining a group mapping compared with a dict or Series. Any function passed as a group key will be called once per index value, with the return values being used as the group names. More concretely(具体地) consider the example DataFrame from the previous section, which has people's first names as index values. Suppose you wanted to group by the length of the names; while you could compute an array of string lengths, it's simpler to just pass the len function.
people.groupby(len).sum()
| a | b | c | d | e | |
|---|---|---|---|---|---|
| 3 | -2.776991 | -1.396828 | -0.016179 | 1.204748 | -3.080333 |
| 5 | -0.941174 | 0.926352 | 0.858621 | -0.005732 | 0.289938 |
| 6 | 0.913253 | 0.851410 | 0.672238 | -0.040455 | 0.722729 |
Mixing functions with arrays, dicts, or Series is not a problem as everything gets convrted to arrays interanlly:
key_list = ['one', 'one', 'one', 'two', 'two']
people.groupby([len, key_list]).min()
| a | b | c | d | e | ||
|---|---|---|---|---|---|---|
| 3 | one | -2.316073 | -0.537660 | 0.392397 | -0.489149 | -1.668533 |
| two | 0.169023 | -0.859168 | -0.408575 | 0.928599 | -1.519773 | |
| 5 | one | -0.941174 | 0.926352 | 0.858621 | -0.005732 | 0.289938 |
| 6 | two | 0.913253 | 0.851410 | 0.672238 | -0.040455 | 0.722729 |
按索引层次分组
A final convenience for hierarchically indexed datasets is the ability to aggregate using one of the levels of an axis index. Let's look at an example:
"多层索引"
columns = pd.MultiIndex.from_arrays([['US', 'US', 'US', 'JP', 'JP'],
[1, 3, 5, 1, 3]],
names=['cty', 'tenor'])
hier_df = pd.DataFrame(np.random.randn(4, 5), columns=columns)
hier_df
'多层索引'
| cty | US | JP | |||
|---|---|---|---|---|---|
| tenor | 1 | 3 | 5 | 1 | 3 |
| 0 | -1.215596 | 1.387763 | 2.339534 | 1.593265 | -1.508100 |
| 1 | 1.519981 | 0.756772 | 0.855458 | 0.403545 | 0.538324 |
| 2 | -2.079601 | -0.487087 | 2.686048 | 1.471465 | 0.206721 |
| 3 | -0.165285 | 0.374494 | -1.994196 | -0.345347 | 2.343612 |
To group by level, pass the level number or name using the level keyword:
hier_df.groupby(level='cty', axis=1).count()
| cty | JP | US |
|---|---|---|
| 0 | 2 | 3 |
| 1 | 2 | 3 |
| 2 | 2 | 3 |
| 3 | 2 | 3 |
