含重复项的Advance pivot多关联场景实现求助

嗨，我完全懂你现在卡在哪了——要把两个表对着pre、current、post三个字段分别关联，还要做出年份为主列、状态为子列、姓名为行的透视表来汇总面积，这确实不是常规的单关联透视能搞定的。我来给你捋捋具体的解决思路，先把你的表结构再明确下方便后续说明：

你的两个表结构 #

• 姓名对照表（暂称name_lookup）：

  name	short_name
  Arthur	a
  （其他姓名及对应简称）

• 主数据表格（暂称area_data）：

  title	year	area	pre	current	post
  Lot 1	2023	4	a	b	c
  Lot 2	2024	5	b	a	d
  （其他数据行）

核心解决逻辑：拆分+关联+合并+透视 #

问题的关键在于一个主表要和 lookup 表关联三次，直接做透视没法同时识别三个关联关系，所以我们需要先把主数据拆成三个子数据集（分别对应pre、current、post状态），每个子数据集单独关联lookup表拿到全名，再合并成一个统一的数据集，最后再做透视。

下面分几种常用工具场景具体说：

场景1：用SQL处理

直接用UNION ALL把三个状态的数据集合并，同时关联lookup表：

-- 提取pre状态的数据并关联全名
SELECT 
    nl.name AS full_name,
    ad.year,
    'pre' AS status,
    ad.area
FROM area_data ad
JOIN name_lookup nl ON ad.pre = nl.short_name

UNION ALL

-- 提取current状态的数据并关联全名
SELECT 
    nl.name AS full_name,
    ad.year,
    'current' AS status,
    ad.area
FROM area_data ad
JOIN name_lookup nl ON ad.current = nl.short_name

UNION ALL

-- 提取post状态的数据并关联全名
SELECT 
    nl.name AS full_name,
    ad.year,
    'post' AS status,
    ad.area
FROM area_data ad
JOIN name_lookup nl ON ad.post = nl.short_name

得到合并后的数据集后，直接用PIVOT函数或者导出到工具里做透视：

• 行：full_name
• 列：先选year，再选status
• 值：SUM(area)

场景2：用Excel/Power Pivot处理

Excel的普通透视表没法直接处理多关联，得用Power Pivot：

1. 把两个表导入Power Pivot，然后手动建立三个关系：
   • area_data[pre] ↔ name_lookup[short_name]
   • area_data[current] ↔ name_lookup[short_name]
   • area_data[post] ↔ name_lookup[short_name]
     （注意Power Pivot默认只会激活一个关系，所以需要用函数切换）
2. 在Power Pivot里创建三个度量值，分别计算不同状态的面积总和：

   预状态面积总和 = CALCULATE(SUM(area_data[area]), USERELATIONSHIP(area_data[pre], name_lookup[short_name]))
   当前状态面积总和 = CALCULATE(SUM(area_data[area]), USERELATIONSHIP(area_data[current], name_lookup[short_name]))
   后状态面积总和 = CALCULATE(SUM(area_data[area]), USERELATIONSHIP(area_data[post], name_lookup[short_name]))
   

3. 插入透视表：
   • 行区域拖入name_lookup[name]
   • 列区域拖入area_data[year]
   • 值区域拖入三个度量值，调整列的分组顺序即可得到你要的结构

场景3：用Python Pandas处理

用拆分-合并的思路，代码如下：

import pandas as pd

# 假设已经读入两个表：name_lookup 和 area_data
# 拆分并关联pre状态
pre_df = area_data.merge(name_lookup, left_on='pre', right_on='short_name')[['name', 'year', 'area']]
pre_df['status'] = 'pre'

# 拆分并关联current状态
current_df = area_data.merge(name_lookup, left_on='current', right_on='short_name')[['name', 'year', 'area']]
current_df['status'] = 'current'

# 拆分并关联post状态
post_df = area_data.merge(name_lookup, left_on='post', right_on='short_name')[['name', 'year', 'area']]
post_df['status'] = 'post'

# 合并三个数据集
combined_df = pd.concat([pre_df, current_df, post_df])

# 生成透视表
pivot_result = combined_df.pivot_table(
    index='name',
    columns=['year', 'status'],
    values='area',
    aggfunc='sum',
    fill_value=0  # 空值填充为0，可选
)

print(pivot_result)

这样处理后，就能得到你想要的透视表结构：行是完整姓名，列是年份作为主分组，pre/current/post作为子分组，值是对应状态下的面积总和。

备注：内容来源于stack exchange，提问作者Mr water bottle