透视聚合

import pandas as pd
import numpy as np

df = pd.DataFrame({'Name':['Mary', 'Jon','Lucy', 'Jane', 'Sue', 'Mary', 'Lucy'],
                   'Age':[35, 37, 40, 29, 31, 26, 28],
                   'City':['Boston', 'Chicago', 'Los Angeles', 'Chicago', 'Boston', 'Boston', 'Chicago'],
                   'Position':['Manager','Manager','Manager','Programmer', 'Programmer','Manager','Manager'],
                    'Sex':['Female','Male','Female','Female', 'Female','Female','Female']},
                    columns=['Name','Position','City','Age','Sex'])

print (df)
   Name    Position         City  Age  Sex
0  Mary     Manager       Boston   35  Female
1   Jon     Manager      Chicago   37  Male
2  Lucy     Manager  Los Angeles   40  Female
3  Jane  Programmer      Chicago   29  Female
4   Sue  Programmer       Boston   31  Female
5  Mary     Manager       Boston   26  Female
6  Lucy     Manager      Chicago   28  Female

如果使用 pivot ,得到错误:

print (df.pivot(index='Position', columns='City', values='Age'))

ValueError:索引包含重复的条目,无法重新整形

使用 pivot_table 和聚合功能:

#default aggfunc is np.mean
print (df.pivot_table(index='Position', columns='City', values='Age'))
City        Boston  Chicago  Los Angeles
Position                                
Manager       30.5     32.5         40.0
Programmer    31.0     29.0          NaN

print (df.pivot_table(index='Position', columns='City', values='Age', aggfunc=np.mean))
City        Boston  Chicago  Los Angeles
Position                                
Manager       30.5     32.5         40.0
Programmer    31.0     29.0          NaN

另一个 agg 功能:

print (df.pivot_table(index='Position', columns='City', values='Age', aggfunc=sum))
City        Boston  Chicago  Los Angeles
Position                                
Manager       61.0     65.0         40.0
Programmer    31.0     29.0          NaN

#lost data !!!
print (df.pivot_table(index='Position', columns='City', values='Age', aggfunc='first'))
City        Boston  Chicago  Los Angeles
Position                                
Manager       35.0     37.0         40.0
Programmer    31.0     29.0          NaN

如果需要按 string 值的列聚合:

print (df.pivot_table(index='Position', columns='City', values='Name')) 

DataError:没有要聚合的数字类型

你可以使用这些 aggragating 功能:

print (df.pivot_table(index='Position', columns='City', values='Name', aggfunc='first')) 
City       Boston Chicago Los Angeles
Position                             
Manager      Mary     Jon        Lucy
Programmer    Sue    Jane        None

print (df.pivot_table(index='Position', columns='City', values='Name', aggfunc='last')) 
City       Boston Chicago Los Angeles
Position                             
Manager      Mary    Lucy        Lucy
Programmer    Sue    Jane        None

print (df.pivot_table(index='Position', columns='City', values='Name', aggfunc='sum')) 
City          Boston  Chicago Los Angeles
Position                                 
Manager     MaryMary  JonLucy        Lucy
Programmer       Sue     Jane        None

print (df.pivot_table(index='Position', columns='City', values='Name', aggfunc=', '.join)) 
City            Boston    Chicago Los Angeles
Position                                     
Manager     Mary, Mary  Jon, Lucy        Lucy
Programmer         Sue       Jane        None

print (df.pivot_table(index='Position', columns='City', values='Name', aggfunc=', '.join, fill_value='-')
         .reset_index()
         .rename_axis(None, axis=1))
     Position      Boston    Chicago Los Angeles
0     Manager  Mary, Mary  Jon, Lucy        Lucy
1  Programmer         Sue       Jane           -

有关性别的信息尚未使用。它可以由其中一个列切换,也可以作为另一个级别添加:

print (df.pivot_table(index='Position', columns=['City','Sex'], values='Age', aggfunc='first'))

City       Boston Chicago       Los Angeles
Sex        Female  Female  Male      Female
Position
Manager      35.0    28.0  37.0        40.0
Programmer   31.0    29.0   NaN         NaN

可以在索引,列和值的任何属性中指定多个列。

print (df.pivot_table(index=['Position','Sex'], columns='City', values='Age', aggfunc='first'))

City               Boston  Chicago  Los Angeles
Position   Sex
Manager    Female    35.0     28.0         40.0
           Male       NaN     37.0          NaN
Programmer Female    31.0     29.0          NaN

应用几个聚合函数

你可以在单个数据透视中轻松应用多个功能:

In [23]: import numpy as np

In [24]: df.pivot_table(index='Position', values='Age', aggfunc=[np.mean, np.std])
Out[24]: 
                 mean       std
Position                       
Manager     34.333333  5.507571
Programmer  32.333333  4.163332

有时,你可能希望将特定函数应用于特定列:

In [35]: df['Random'] = np.random.random(6)
In [36]: df
Out[36]: 
   Name    Position         City  Age    Random
0  Mary     Manager       Boston   34  0.678577
1  Josh  Programmer     New York   37  0.973168
2   Jon     Manager      Chicago   29  0.146668
3  Lucy     Manager  Los Angeles   40  0.150120
4  Jane  Programmer      Chicago   29  0.112769
5   Sue  Programmer       Boston   31  0.185198

For example, find the mean age, and standard deviation of random by Position:

In [37]: df.pivot_table(index='Position', aggfunc={'Age': np.mean, 'Random': np.std})
Out[37]: 
                  Age    Random
Position                       
Manager     34.333333  0.306106
Programmer  32.333333  0.477219

可以传递一系列函数以应用于各个列:

In [38]: df.pivot_table(index='Position', aggfunc={'Age': np.mean, 'Random': [np.mean, np.std]})]
Out[38]: 
                  Age    Random          
                 mean      mean       std
Position                                 
Manager     34.333333  0.325122  0.306106
Programmer  32.333333  0.423712  0.477219