資料分析(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)