python|jupyter||pandas|4.5创建透视表和交叉表

4.5创建透视表和交叉表

使用pivot_table函数创建透视表

pandas.pivot_table(data,values=None,index=None,columns=None,aggfunc='mean',fill_value=None,margins=False,dropna=True,margins_name='All')

#使用订单号作为透视表索引制作透视表
import pandas as pd
import numpy as np
from sqlalchemy import create_engine
engine=create_engine('mysql+pymysql://root:1234@127.0.0.1:3306/testdb?charset=utf8')
detail=pd.read_sql_table('meal_order_detail',con=engine)
detailpivot=pd.pivot_table(detail[['counts','amounts']],index='order_id')
print('透视表',detailpivot.head())

#修改后
detailpivot1=pd.pivot_table(detail[['order_id','counts','amounts']],index='order_id',aggfunc=np.sum)
print('以order_id作为分组键创建的订单销量与售价总和透视表：',detailpivot1.head())

#index可以有多个
detailpivot2=pd.pivot_table(detail[['order_id','dishes_name','counts','amounts']],index=['order_id','dishes_name'],aggfunc=np.sum)
print('以order_id和dishes_name作为分组键创建的订单/销量与售价总和透视表：',detailpivot2.head())

#指定菜名为列分组键的透视表
detailpivot3=pd.pivot_table(detail[['order_id','dishes_name','counts','amounts']],index='order_id',columns='dishes_name',aggfunc=np.sum)
print('以order_id和dishes_name'作为列分组键创建的透视表前5行4列',detailpivot.iloc[:5,:4])

#指定某些列制作透视表
detailpivot4=detail.pivot_table(detail[['order_id','dishes_name','counts','amounts']],index='order_id',values='counts',aggfunc=np.sum)
print('以order_id作为分组键counts作为值创建的透视表前5行',detailpivot4.head())

#对透视表中缺失值进行填充
detailpivot4=detail.pivot_table(detail[['order_id','dishes_name','counts','amounts']],index='order_id',columns='dishes_name',aggfunc=np.sum,fill_values=0)
print('空值填0，以order_id和dishes_name'作为列分组键创建的透视表前5行4列',detailpivot.iloc[:5,:4])

#在透视图中添加汇总数据
detailpivot4=detail.pivot_table(detail[['order_id','dishes_name','counts','amounts']],index='order_id',columns='dishes_name',aggfunc=np.sum,fill_values=0，margins=True)
print('添加margins后，空值填0，以order_id和dishes_name'作为列分组键创建的透视表前5行后4列',detailpivot.iloc[:5,-4:])

使用crosstab创建交叉表

pandas.crosstab(index,columns,values=None,rownames=None,colnames=None,aggfunc=None,margins=False,dropna=True,normalize=False)

#使用crosstab创建交叉表
detailcross=pd.crosstab(index=detail['order_id'],columns=detail['dishes_name'],values=detail['counts'],aggfunc=np.sum)
print('以order_id和dishes_name为分组键Counts为值的透视表前5行5列',detailcross.iloc[:5,:5])