如何将pandas DataFrame中groupby的年份行转为列展示各年度销量

你可以通过pandas的pivot_table方法直接实现按客户、商品为行维度，年份为列维度的销量聚合展示，也可以基于你原有分组逻辑加unstack转宽表实现，两种可用方案如下：

完整可运行代码（推荐pivot_table写法） #

import pandas as pd

data = [
[1,'Apples','2017-02-23',10,0.4],
[2,'Oranges','2017-03-06',20,0.7],
[1,'Apples','2017-09-23',8,0.5],
[1,'Apples','2018-05-14',14,0.5],
[1,'Apples','2019-04-27',7,0.6],
[2,'Apples','2018-09-10',14,0.4],
[1,'Oranges','2018-07-12',9,0.7],
[1,'Oranges','2018-12-07',4,0.7]]

df = pd.DataFrame(data, columns = ['CustomerID','Product','Invoice Date','Amount','Price'])
# 提取年份字段
df['Year'] = pd.to_datetime(df['Invoice Date']).dt.year

# 生成透视表
result = df.pivot_table(
    index=['CustomerID', 'Product'],
    columns='Year',
    values='Amount',
    aggfunc='sum',
    fill_value=0
)
# 按要求修改列名
result.columns = [f'Amount in {col}' for col in result.columns]
# 重置索引将客户ID、商品转为普通列
result = result.reset_index()

print(result)

基于原有分组逻辑的调整方案 #

你原有代码分组时加入了Price维度会导致数据拆分异常，去掉该维度后加unstack即可实现转列：

import pandas as pd

data = [
[1,'Apples','2017-02-23',10,0.4],
[2,'Oranges','2017-03-06',20,0.7],
[1,'Apples','2017-09-23',8,0.5],
[1,'Apples','2018-05-14',14,0.5],
[1,'Apples','2019-04-27',7,0.6],
[2,'Apples','2018-09-10',14,0.4],
[1,'Oranges','2018-07-12',9,0.7],
[1,'Oranges','2018-12-07',4,0.7]]

df = pd.DataFrame(data, columns = ['CustomerID','Product','Invoice Date','Amount','Price'])
df['Invoice Date'] = pd.to_datetime(df['Invoice Date']).dt.strftime('%Y')
# 去掉不需要的Price分组维度
grpyear = df.groupby(['CustomerID','Product','Invoice Date'])['Amount'].sum()
# 把年份维度从行转成列，无销量的年份填充0
result = grpyear.unstack(level='Invoice Date', fill_value=0)
# 重命名列名
result.columns = [f'Amount in {col}' for col in result.columns]
# 重置索引
result = result.reset_index()

print(result)

输出效果 #

两种方法最终都会得到你需要的结构，输出样例如下：

内容的提问来源于stack exchange，提问作者user3027413