数据分析(pandas)---08.特殊的透视表(交叉表)

import pandas as pd
import numpy as np

detail = pd.read_excel('meal_order_detail.xlsx')

# 创建交叉表(特殊透视表)
cross_table = pd.crosstab(index=detail['order_id'],
            columns=detail['dishes_name'],
            values=detail['counts'],
            aggfunc=np.sum)
print('以order_id为行分组键、和dishes_name为列分组键,得到交叉表为:\n',cross_table.head())

# 准备工作
detail['place_order_time'] = pd.to_datetime(detail['place_order_time'])
date = [i.date() for i in detail['place_order_time']]
# print(date)
# 添加date列
detail['date'] = date

result = pd.crosstab(index=detail['date'],
            columns=detail['dishes_name'],
            values=detail['amounts'],
            aggfunc=np.sum,
            margins=True)
print(result.head())

# fp = pd.ExcelWriter('交叉表.xlsx')
# result.to_excel(fp)
# fp.save()


# 补充知识点
# loc 索引名称
result=detail.loc[detail['order_id']==137,['dishes_name','amounts']]
print(result)

result=detail.loc[detail['amounts']>100,['dishes_name','amounts']]
print('菜品单价超过100',result.head)

# iloc 索引位置

a = detail.iloc[1,1]
b = detail.iloc[0:6,1:6]
print(a)
print(b)