pandas 对数据框重新采样，以按 CustomerID 的另一列每天汇总销售额

Question

我有一个带有 datetime (TransactionDate) 列和一个 CustomerID 列和一个 Sales 列的 pandas 数据框。我想对数据 Daily 重新采样以每天汇总销售额，但分别针对每个 CustomerID。我尝试了两种不同的方法，但都没有产生预期的结果。当我尝试这样做时，通过仅将 TransactionDate 列设置为索引，Sales 总和，但 CustomerID 列也是如此，我丢失了有关哪个 CustomerID 产生了多少销售额的信息。当我尝试通过将 TransactionDate 列和 CustomerID 列设置为索引来执行此操作时，出现错误

TypeError: Only valid with DatetimeIndex, TimedeltaIndex or PeriodIndex, but got an instance of 'MultiIndex'

我该怎么做才能通过 CustomerID 获得每日销售额的数据框？

完整数据的代码如下：

import pandas as pd

import numpy as np

import random

random.seed(30)

np.random.seed(30)

InvoiceNo = range(10000,10500)

print('len(InvoiceNo)',len(InvoiceNo))

start_date,end_date = '1/1/2015','12/31/2019'

date_rng = pd.date_range(start= start_date, periods=len(InvoiceNo), freq='3H')

length_of_field = date_rng.shape[0]

df = pd.DataFrame(date_rng, columns=['TransactionDate'])

df['InvoiceNo']=InvoiceNo

df['Quantity'] = np.random.randint(18,100,size=(len(date_rng)))

Items = ('ItemA','ItemB','ItemC','ItemD')

group_1 = np.random.choice(Items, len(InvoiceNo), p = [0.3, 0.5, 0.15, 0.05])

Price = (10.0,20,30,40)

dict_item_price = dict(zip(Items,Price))

PriceList = [dict_item_price[i] for i in group_1]

CustomerID = (18750,18751,18752,18753,18754,18756,18757)

group_2 = np.random.choice(CustomerID, len(InvoiceNo), p = [0.10, 0.25, 0.15, 0.05,0.35,0.05,0.05])

df['ItemCode'] = group_1

df['Price'] = PriceList

df['CustomerID'] = group_2

df['CustomerID'].astype(str)

df['Sales']=df['Price']*df['Quantity']

print('\ndf:')

print(df)

print(df.dtypes)

df1 = df[['CustomerID','Sales','TransactionDate']].copy().set_index(['TransactionDate'])

print('\n df1 :')

print(df1)

total_sales = df['Sales'].sum()

print('\ntotal sales :',total_sales)

daily_sales = df1.resample('D').sum()

print('\n daily_sales :')

print(daily_sales)

Answer 1

就像是：df.groupby(['CustomerID', df['TransactionDate'].dt.normalize()])['Sales'].sum()或者df.groupby(['CustomerID', df['TransactionDate'].dt.to_period('D')])['Sales'].sum()