数据透视熊猫数据帧和和选择性列

Question

我有一个熊猫数据帧，如下所示：

SALES_DATE      CATEGORY_NAME  ITEM_PRICE  SUM_QUANTITY  TOTAL_SALES
0   2019-10-04  Bathroom Supplies        7.49         100.0       749.00
1   2019-10-04            Produce        2.59          71.0       183.89
2   2019-10-04              Dairy        5.29         558.0      2951.82

我想进行透视，使行成为列标题，并按日期对总销售额求和

SALES_DATE Bathroom Supplies Produce Dairy   TOTAL_SALES
2019-05-20 100.0.            71.0    2951.82 3883

我试过这样做，但没有给我正确的回报

basket_NY = pd.pivot_table(basket_NY, index=['SALES_DATE'],values=["SUM_QUANTITY","TOTAL_SALES"],columns=['CATEGORY_NAME'],aggfunc=np.sum)

Answer 1

我认为您需要将其拆分为一个枢轴和一个聚合和，然后将它们连接到您的"索引"：

import pandas as pd
df = pd.DataFrame({
"SALES_DATE": ["2019-10-04", "2019-10-04", "2019-10-04"],
"CATEGORY_NAME": ["Bathroom Supplies", "Produce", "Dairy"],
"ITEM_PRICE": [7.49, 2.59, 5.29],
"SUM_QUANTITY": [100, 71, 558],
"TOTAL_SALES": [749, 183.89, 2951.82]})
# Get pivot values
pivot = pd.pivot_table(
df, index=["SALES_DATE"], values=["SUM_QUANTITY"],
columns=["CATEGORY_NAME"]) 
pivot.columns = [i[1] for i in pivot.columns]
# Join with sum of total sales
result = pivot.join(
df.groupby("SALES_DATE")["TOTAL_SALES"].sum()).reset_index()