Python数据分析pandas之分组统计透视表

import pandas as pd
df2=pd.DataFrame([["李四",32,'A',90],["王二",19,'B',78],["王三",34,'C',55],["刘七",35,'A',91]],columns=['name','age','level','score'],index=['no_001','no_002','no_003','no_006'])
#查看所有数值列数据概况
print(df2.describe())
#查看age列的数据概况
print(df2.age.describe())
# 当然也可以指定percentiles，比如这里仅显示百分之30、50分位数。
# print(df2.describe(percentiles=[0.3,0.5]))
#结果
             age      score
count   4.000000   4.000000
mean   30.000000  78.500000
std     7.438638  16.743158
min    19.000000  55.000000
25%    28.750000  72.250000
50%    33.000000  84.000000
75%    34.250000  90.250000
max    35.000000  91.000000
count     4.000000
mean     30.000000
std       7.438638
min      19.000000
25%      28.750000
50%      33.000000
75%      34.250000
max      35.000000
Name: age, dtype: float64

此代码由Java架构师必看网-架构君整理
import pandas as pd
df2=pd.DataFrame([["李四",32,'A',90],["王二",19,'B',78],["王三",34,'C',55],["刘七",35,'A',91]],columns=['name','age','level','score'],index=['no_001','no_002','no_003','no_006'])
#查看所有数值列数据详情，分开统计
print(df2.age.count(),df2.age.mean(),df2.age.std(),df2.age.min(),df2.age.quantile(0.25),df2.age.quantile(0.5),df2.age.quantile(0.75),df2.age.sum())
#结果
4 30.0 7.438637868140466 19 28.75 33.0 34.25 120

#这里按照等级列进行分组，以求最大值为例，其它的聚合函数类似。

import pandas as pd df2=pd.DataFrame([["李四",32,'A',90],["王二",19,'B',78],["王三",34,'C',55],["刘七",35,'A',91],["麻六",34,'A',83]],columns=['name','age','level','score'],index=['no_001','no_002','no_003','no_004','no_005']) 
#查看等级列的聚合(最大值)情况 
print(df2.groupby('level').max())         
#结果
name  age  score 
level          
A       麻六   35     91 
B       王二   19     78 
C       王三   34     55

此代码由Java架构师必看网-架构君整理
import pandas as pd df2=pd.DataFrame([["李四",32,'A',90,2,1500,300],["王二",19,'B',78,2,1300,200],["王三",34,'C',55,1,1250,235],["刘七",35,'A',91,3,1000,100],["麻六",34,'A',83,3,900,400]],columns=['name','age','level','score','rn','income','bouns'],index=['no_001','no_002','no_003','no_004','no_005']) # 按照默认列分组取最大值
#print(df2.groupby(['level','rn']).max())
##指定列(age)分组取最大值 print(df2.groupby(['level','rn']).age.max()) #print(df2.groupby(['level','rn'])['age'].max())#等同于上式
#结果
 name  age  score  income  bouns level rn                                 A     2    
 李四   32     90    1500    300       3    
 麻六   35     91    1000    400 B     2    
 王二   19     78    1300    200 C     1    
 王三   34     55    1250    235 
level  rn 
A      2     32        
       3     35 
B      2     19 
C      1     34 
Name: age, dtype: int64

import pandas as pd
df2=pd.DataFrame([["李四",32,'A',90,2,1500,300],["王二",19,'B',78,2,1300,200],["王三",34,'C',55,1,1250,235],["刘七",35,'A',91,3,1000,100],["麻六",34,'A',83,3,900,400]],columns=['name','age','level','score','rn','income','bouns'],index=['no_001','no_002','no_003','no_004','no_005'])
#通过APPLY结合lambda表达式生成新列
df_apply = pd.DataFrame(df2.groupby(['level', 'rn'], as_index=False).apply(lambda x: sum(x['income'])/sum(x['income']+x['bouns'])),columns=['rate'])
df_fin= df_apply.reset_index()
print(df_fin)
#结果，这里level和rn是索引。
              rate 
level rn           
A     2   0.833333       
      3   0.791667 
B     2   0.866667 
C     1   0.841751

import pandas as pd
df2=pd.DataFrame([["李四",32,'A',90,2,1500,300],["王二",19,'B',78,2,1300,200],["王三",34,'C',55,1,1250,235],["刘七",35,'A',91,3,1000,100],["麻六",34,'A',83,3,900,400]],columns=['name','age','level','score','rn','income','bouns'],index=['no_001','no_002','no_003','no_004','no_005'])
#通过APPLY结合lambda表达式生成新列
df_apply = pd.DataFrame(df2.groupby(['level', 'rn'], as_index=False).apply(lambda x: sum(x['income'])/sum(x['income']+x['bouns'])),columns=['rate'])
df_fin= df_apply.reset_index()
print(df_fin)
#结果
  level  rn      rate
0     A   2  0.833333
1     A   3  0.791667
2     B   2  0.866667
3     C   1  0.841751

import pandas as pd df2=pd.DataFrame([["李四",32,'A',90,2,1500,300],["王二",19,'B',78,2,1300,200],["王三",34,'C',55,1,1250,235],["刘七",35,'A',91,3,1000,100],["麻六",34,'A',83,3,900,400]],columns=['name','age','level','score','rn','income','bouns'],index=['no_001','no_002','no_003','no_004','no_005']) print(df2.pivot_table(index='name',columns=["level"],aggfunc={'age':max,'income':sum}))
#结果
        age    income level 
          A     B     C       A       B       C 
name                                            
刘七     35.0   NaN   NaN  1000.0     NaN     NaN 
李四     32.0   NaN   NaN  1500.0     NaN     NaN 
王三      NaN   NaN  34.0     NaN     NaN  1250.0 
王二      NaN  19.0   NaN     NaN  1300.0     NaN 
麻六     34.0   NaN   NaN   900.0     NaN     NaN