在SQL语言里有group by功能,在Pandas里有groupby函数与之功能相对应。DataFrame数据对象经groupby()之后有ngroups和groups等属性,本质是DataFrame类的子类DataFrameGroupBy的实例对象。ngroups反应的是分组的个数,而groups类似dict结构,key是分组的index或label,value则为index或label所对应的分组数据。size函数则是可以返回所有分组的字节大小。count函数可以统计分组后各列数据项个数。get_group函数可以返回指定组的数据信息。而discribe函数可以返回分组后的数据的统计数据。
- 基于单列的分组。
import pandas as pd
idx = [101,101,101,102,102,102,103,103,103]
name = ["apple","pearl","orange", "apple","pearl","orange","apple","pearl","orange"]
price = [1.0,2.0,3.0,4.00,5.0,6.0,7.0,8.0,9.0]
df0 = pd.DataFrame({ "fruit": name, "price" : price, "supplier" :idx})
print "*" * 30
print df0
dg = df0.groupby("fruit")
print "*" * 30
for n, g in dg:
print "group_name:", n, "\n|",g,"|"
print "*" * 30
for n,_ in dg:
print "group_name:", n, "\n|",dg.get_group(n),"|"
print "*" * 30
print dg.describe()
print "*" * 30
程序执行结果:
******************************
fruit price supplier
0 apple 1 101
1 pearl 2 101
2 orange 3 101
3 apple 4 102
4 pearl 5 102
5 orange 6 102
6 apple 7 103
7 pearl 8 103
8 orange 9 103
******************************
group_name: apple
| fruit price supplier
0 apple 1 101
3 apple 4 102
6 apple 7 103 |
group_name: orange
| fruit price supplier
2 orange 3 101
5 orange 6 102
8 orange 9 103 |
group_name: pearl
| fruit price supplier
1 pearl 2 101
4 pearl 5 102
7 pearl 8 103 |
******************************
group_name: apple
| fruit price supplier
0 apple 1 101
3 apple 4 102
6 apple 7 103 |
group_name: orange
| fruit price supplier
2 orange 3 101
5 orange 6 102
8 orange 9 103 |
group_name: pearl
| fruit price supplier
1 pearl 2 101
4 pearl 5 102
7 pearl 8 103 |
******************************
price supplier
fruit
apple count 3.0 3.0
mean 4.0 102.0
std 3.0 1.0
min 1.0 101.0
25% 2.5 101.5
50% 4.0 102.0
75% 5.5 102.5
max 7.0 103.0
orange count 3.0 3.0
mean 6.0 102.0
std 3.0 1.0
min 3.0 101.0
25% 4.5 101.5
50% 6.0 102.0
75% 7.5 102.5
max 9.0 103.0
pearl count 3.0 3.0
mean 5.0 102.0
std 3.0 1.0
min 2.0 101.0
25% 3.5 101.5
50% 5.0 102.0
75% 6.5 102.5
max 8.0 103.0
******************************
- 多列数据分组。在groupby函数内给出一个列表,列表里列出多列列名。
import pandas as pd
idx = [101,101,101,102,102,102,103,103,103]
name = ["apple","pearl","orange", "apple","pearl","orange","apple","pearl","orange"]
price = [1.0,2.0,3.0,4.00,5.0,6.0,7.0,8.0,9.0]
df0 = pd.DataFrame({ "fruit": name, "price" : price, "supplier" :idx})
print "*" * 30
print df0
print "*" * 30
dg2 = df0.groupby(["fruit", "supplier"])
for n, g in dg2:
print "multiGroup on:", n, "\n|",g ,"|"
print "*" * 30
程序里基于["fruit", "supplier"]
两列进行分组。执行结果如下:
******************************
fruit price supplier
0 apple 1 101
1 pearl 2 101
2 orange 3 101
3 apple 4 102
4 pearl 5 102
5 orange 6 102
6 apple 7 103
7 pearl 8 103
8 orange 9 103
******************************
multiGroup on: ('apple', 101)
| fruit price supplier
0 apple 1 101 |
multiGroup on: ('apple', 102)
| fruit price supplier
3 apple 4 102 |
multiGroup on: ('apple', 103)
| fruit price supplier
6 apple 7 103 |
multiGroup on: ('orange', 101)
| fruit price supplier
2 orange 3 101 |
multiGroup on: ('orange', 102)
| fruit price supplier
5 orange 6 102 |
multiGroup on: ('orange', 103)
| fruit price supplier
8 orange 9 103 |
multiGroup on: ('pearl', 101)
| fruit price supplier
1 pearl 2 101 |
multiGroup on: ('pearl', 102)
| fruit price supplier
4 pearl 5 102 |
multiGroup on: ('pearl', 103)
| fruit price supplier
7 pearl 8 103 |
******************************
- 使用set_index进行分组。
import pandas as pd
idx = [101,101,101,102,102,102,103,103,103]
name = ["apple","pearl","orange", "apple","pearl","orange","apple","pearl","orange"]
price = [1.0,2.0,3.0,4.00,5.0,6.0,7.0,8.0,9.0]
df0 = pd.DataFrame({ "fruit": name, "price" : price, "supplier" :idx})
print "*" * 30
print df0
print "*" * 30
dg2 = df0.groupby(["fruit", "supplier"])
for n, g in dg2:
print "multiGroup on:", n, "\n|",g ,"|"
print "*" * 30
si = df0.set_index(["fruit", "supplier"])
print si
print "*" * 30
程序的执行结果:
******************************
fruit price supplier
0 apple 1 101
...
8 orange 9 103
******************************
multiGroup on: ('apple', 101)
| fruit price supplier
0 apple 1 101 |
...
multiGroup on: ('pearl', 103)
| fruit price supplier
7 pearl 8 103 |
******************************
price
fruit supplier
apple 101 1
...
orange 103 9
******************************
Pandas的数据分组-aggregate聚合
在对数据进行分组之后,可以对分组后的数据进行聚合处理统计。
- agg函数,agg的形参是一个函数会对分组后每列都应用这个函数。
import pandas as pd
import numpy as np
idx = [101,101,101,102,102,102,103,103,103]
idx += [101,102,103]
name = ["apple","pearl","orange", "apple","pearl","orange","apple","pearl","orange"]
name += ["apple"] * 3
price = [1.0,2.0,3.0,4.00,5.0,6.0,7.0,8.0,9.0]
price += [4] * 3
df0 = pd.DataFrame({ "fruit": name, "price" : price, "supplier" :idx})
print "*" * 30
print df0
print "*" * 30
dg1 = df0.groupby(["fruit", "supplier"])
for n, g in dg1:
print "multiGroup on:", n, "\n|",g ,"|"
print "*" * 30
print dg1.agg(np.mean)
程序的执行结果:
******************************
fruit price supplier
0 apple 1 101
1 pearl 2 101
2 orange 3 101
3 apple 4 102
4 pearl 5 102
5 orange 6 102
6 apple 7 103
7 pearl 8 103
8 orange 9 103
9 apple 4 101
10 apple 4 102
11 apple 4 103
******************************
multiGroup on: ('apple', 101)
| fruit price supplier
0 apple 1 101
9 apple 4 101 |
...
multiGroup on: ('pearl', 103)
| fruit price supplier
7 pearl 8 103 |
******************************
price
fruit supplier
apple 101 2.5
102 4.0
103 5.5
orange 101 3.0
102 6.0
103 9.0
pearl 101 2.0
102 5.0
103 8.0
请注意水果apple的输出。
- agg应用均值、求和、最大等示例。
import pandas as pd
import numpy as np
idx = [101,101,101,102,102,102,103,103,103]
idx += [101,102,103] * 3
name = ["apple","pearl","orange", "apple","pearl","orange","apple","pearl","orange"]
name += ["apple"] * 3 + ["pearl"] * 3 + ["orange"] * 3
price = [4.1,5.3,6.3,4.20,5.4,6.0,4.5,5.5,6.8]
price += [4] * 3 + [5] * 3 + [6] * 3
df0 = pd.DataFrame({ "fruit": name, "price" : price, "supplier" :idx})
print "*" * 30
print df0
print "*" * 30
dg1 = df0.groupby(["fruit", "supplier"])
print dg1.agg(np.mean)
print "*" * 30
print dg1.agg([np.mean, np.std, np.min, np.sum])
程序执行结果:
******************************
fruit price supplier
0 apple 4.1 101
...
17 orange 6.0 103
******************************
price
fruit supplier
apple 101 4.05
102 4.10
103 4.25
orange 101 6.15
102 6.00
103 6.40
pearl 101 5.15
102 5.20
103 5.25
******************************
price
mean std amin sum
fruit supplier
apple 101 4.05 0.070711 4 8.1
102 4.10 0.141421 4 8.2
103 4.25 0.353553 4 8.5
orange 101 6.15 0.212132 6 12.3
102 6.00 0.000000 6 12.0
103 6.40 0.565685 6 12.8
pearl 101 5.15 0.212132 5 10.3
102 5.20 0.282843 5 10.4
103 5.25 0.353553 5 10.5
- 各列用不同的处理函数。需要在agg函数里以字典的形式给出,分组后的那列用那个函数处理。
import pandas as pd
import numpy as np
idx = [101,101,101,102,102,102,103,103,103]
idx += [101,102,103] * 3
name = ["apple","pearl","orange", "apple","pearl","orange","apple","pearl","orange"]
name += ["apple"] * 3 + ["pearl"] * 3 + ["orange"] * 3
price = [4.1,5.3,6.3,4.20,5.4,6.0,4.5,5.5,6.8]
price += [4] * 3 + [5] * 3 + [6] * 3
df0 = pd.DataFrame({ "fruit": name, "price" : price, "supplier" :idx})
print "*" * 30
print df0
print "*" * 30
dg1 = df0.groupby(["fruit"])
print dg1.agg(np.mean)
print "*" * 30
print dg1.agg([np.mean, np.std, np.min, np.sum])
print "*" * 30
print dg1.agg({"price" : np.mean, "supplier" : np.max})
程序的执行结果:
******************************
fruit price supplier
0 apple 4.1 101
1 pearl 5.3 101
2 orange 6.3 101
3 apple 4.2 102
4 pearl 5.4 102
5 orange 6.0 102
6 apple 4.5 103
7 pearl 5.5 103
8 orange 6.8 103
9 apple 4.0 101
10 apple 4.0 102
11 apple 4.0 103
12 pearl 5.0 101
13 pearl 5.0 102
14 pearl 5.0 103
15 orange 6.0 101
16 orange 6.0 102
17 orange 6.0 103
******************************
price supplier
fruit
apple 4.133333 102
orange 6.183333 102
pearl 5.200000 102
******************************
price supplier
mean std amin sum mean std amin sum
fruit
apple 4.133333 0.196638 4 24.8 102 0.894427 101 612
orange 6.183333 0.325064 6 37.1 102 0.894427 101 612
pearl 5.200000 0.228035 5 31.2 102 0.894427 101 612
******************************
supplier price
fruit
apple 103 4.133333
orange 103 6.183333
pearl 103 5.200000
agg函数是对列而言的,如果打算对分组后列的数据进行处理可以使用tranform函数。
Pandas的数据分组-transform函数
pandas有些函数在不同的版本下可能使用功能有些不同,这是个麻烦事情。查看pandas的版本。
$ python
>>> import pandas as pd
>>> print pd.__version__
0.17.1
升级pandas
$ sudo pip install -U pandas
或者安装指定版本的软件:
$ sudo pip install pandas=x.y.z
x.y.z为选用的pandas的版本号。而本章的transform函数是在pandas的0.20版本后才加入pandas的。 transform函数可以作用于groupby之后的每个组的所有数据。
import pandas as pd
import numpy as np
idx = [101,101,101,102,102,102,103,103,103]
idx += [101,102,103] * 3
name = ["apple","pearl","orange", "apple","pearl","orange","apple","pearl","orange"]
name += ["apple"] * 3 + ["pearl"] * 3 + ["orange"] * 3
price = [4.1,5.3,6.3,4.20,5.4,6.0,4.5,5.5,6.8]
price += [4] * 3 + [5] * 3 + [6] * 3
df0 = pd.DataFrame({ "fruit": name, "price" : price, "supplier" :idx})
print "df", "*" * 30
print df0
def p_data(o):
for name, group in o:
print name
print group[:3]
dg1 = df0.groupby(["fruit"])
print "1", "*" * 30
print p_data(dg1)
def f1(x):
return x + 1
def f2(x):
return x + 100
print "2", "*" * 30
print dg1["price"].transform(f1)[:3]
print "3", "*" * 30
print dg1["supplier"].transform(f2)[:3]
print "4", "*" * 30
print dg1.transform(f2)[:3]
示例里的print dg1.transform(f2)[:3]
语句是对dg1各组里各个值都在原有基础上加100,从执行结果可以看出是这样的。
df ******************************
fruit price supplier
0 apple 4.1 101
1 pearl 5.3 101
2 orange 6.3 101
3 apple 4.2 102
4 pearl 5.4 102
5 orange 6.0 102
6 apple 4.5 103
7 pearl 5.5 103
8 orange 6.8 103
9 apple 4.0 101
10 apple 4.0 102
11 apple 4.0 103
12 pearl 5.0 101
13 pearl 5.0 102
14 pearl 5.0 103
15 orange 6.0 101
16 orange 6.0 102
17 orange 6.0 103
1 ******************************
apple
fruit price supplier
0 apple 4.1 101
3 apple 4.2 102
6 apple 4.5 103
orange
fruit price supplier
2 orange 6.3 101
5 orange 6.0 102
8 orange 6.8 103
pearl
fruit price supplier
1 pearl 5.3 101
4 pearl 5.4 102
7 pearl 5.5 103
2 ******************************
0 5.1
1 6.3
2 7.3
Name: price, dtype: float64
3 ******************************
0 201
1 201
2 201
Name: supplier, dtype: int64
4 ******************************
price supplier
0 104.1 201
1 105.3 201
2 106.3 201