如何使用Pandas生成两种特殊结构的透视DataFrame？

先看样本DataFrame：

import pandas as pd

df = pd.DataFrame({
    "Name": ["Alan","Alan","Kate","Kate","Brian"],
    "Shop" :["A","B","C","A","B"],
    "Amount":[4,2,1,3,5]
})

输出结果：

Name Shop  Amount
0   Alan    A       4
1   Alan    B       2
2   Kate    C       1
3   Kate    A       3
4  Brian    B       5

实现第一个预期输出 #

需求要点 #

• 列包含Shop所有唯一值 + Name列
• 索引为Shop所有唯一值，每个Name对应重复一次这些索引
• 值为对应Name和Shop匹配的Amount，无匹配则为0

代码实现 #

# 获取Shop的唯一值集合
shops = df['Shop'].unique()
# 生成Name与Shop的笛卡尔积，构建基础数据框架
name_shop_cartesian = pd.MultiIndex.from_product(
    [df['Name'].unique(), shops], 
    names=['Name', 'Shop']
).to_frame(index=False)
# 透视原数据，得到每个Name对应各Shop的Amount，缺失值补0
pivot_base = df.pivot(index='Name', columns='Shop', values='Amount').fillna(0)
# 合并笛卡尔积表与透视表，调整索引和列顺序
result1 = name_shop_cartesian.merge(pivot_base, on='Name', how='left')
result1 = result1.set_index('Shop')[list(shops) + ['Name']]
result1.index.name = None

print(result1)

输出结果 #

A  B  C   Name
A  4  2  0   Alan
B  4  2  0   Alan
C  4  2  0   Alan
A  3  0  1   Kate
B  3  0  1   Kate
C  3  0  1   Kate
A  0  5  0  Brian
B  0  5  0  Brian
C  0  5  0  Brian

实现第二个预期输出 #

需求要点 #

与第一个输出基本一致，核心差异：值为**当前行索引（Shop）**与列（Shop）匹配的Amount，无匹配则为0

代码实现 #

# 获取Shop的唯一值集合
shops = df['Shop'].unique()
# 生成Name与Shop的笛卡尔积，构建基础数据框架
name_shop_cartesian = pd.MultiIndex.from_product(
    [df['Name'].unique(), shops], 
    names=['Name', 'Shop']
).to_frame(index=False)
# 构建(Name, Shop)到Amount的映射字典，方便快速查找
amount_mapping = df.set_index(['Name', 'Shop'])['Amount'].to_dict()

# 定义行填充函数：根据当前行的Name和Shop索引获取Amount，广播到所有Shop列
def fill_row_values(row):
    target_amount = amount_mapping.get((row['Name'], row['Shop']), 0)
    return pd.Series([target_amount]*len(shops), index=shops)

# 应用函数生成数值列，合并后调整结构
result2 = name_shop_cartesian.join(name_shop_cartesian.apply(fill_row_values, axis=1))
result2 = result2.set_index('Shop')[list(shops) + ['Name']]
result2.index.name = None

print(result2)

输出结果 #

A  B  C   Name
A  4  4  4   Alan
B  2  2  2   Alan
C  0  0  0   Alan
A  3  3  3   Kate
B  0  0  0   Kate
C  1  1  1   Kate
A  0  0  0  Brian
B  5  5  5  Brian
C  0  0  0  Brian

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