如何将多队列对角线指标转换为垂直表？

需要将多队列的对角线指标数据转换为垂直表，方便后续直接提取整列数据（无需偏移量）。初始年龄为用户加入时的年龄（最低18岁），例如18岁加入者，第1年对应值为1，第2年年龄19时对应值仍为1，以此类推。实际数据包含63个队列，年龄范围18-60岁，年份1-20年（年份数量可变动）。

原数据表格 #

目标输出表格 #

用Pandas通过转长表→计算初始年龄→转宽表三步即可实现需求，代码如下：

import pandas as pd

# 1. 读取/构造数据（替换成你的实际数据读取逻辑，比如pd.read_excel/pd.read_csv）
data = {
    "队列": [1,1,1,1,2,2,2,2],
    "年龄": [18,19,20,21,18,19,20,21],
    "第1年": [1,2,3,4,1.5,2.5,3.5,4.5],
    "第2年": [None,1,2,3,None,1.5,2.5,3.5],
    "第3年": [None,None,1,2,None,None,1.5,2.5],
    "第4年": [None,None,None,1,None,None,None,1.5]
}
df = pd.DataFrame(data)

# 2. 宽表转长表：拆分年份列和对应指标值
melted = df.melt(id_vars=["队列", "年龄"], var_name="年份", value_name="指标值")
# 提取年份数字，用于计算初始年龄
melted["年份数"] = melted["年份"].str.extract(r"(\d+)").astype(int)

# 3. 计算初始年龄：初始年龄 = 当前年龄 - (年份数 - 1)
# 逻辑：第n年对应加入后的第n年，初始年龄=当前年龄减去已度过的年数
melted["初始年龄"] = melted["年龄"] - (melted["年份数"] - 1)

# 4. 筛选有效数据：保留初始年龄≥18、指标值非空的记录
filtered = melted[(melted["初始年龄"] >= 18) & (melted["指标值"].notna())]

# 5. 转成目标宽表：行=年份，列=(队列, 初始年龄)，值=指标值
result = filtered.pivot_table(
    index="年份",
    columns=["队列", "初始年龄"],
    values="指标值",
    aggfunc="first"  # 确保每个行列组合仅取一个值
)

# 6. 调整表头格式（可选，贴近需求展示样式）
result.columns = [f"*{q}*<br/>{age}" for q, age in result.columns]
result.index.name = "队列<br/>初始年龄"

# 输出结果（可替换为保存到文件：result.to_excel("目标表.xlsx")）
print(result.to_markdown(na_rep=""))

关键逻辑说明 #

• 转长表：用melt将分散在列的年份数据整合为行，便于统一计算初始年龄。
• 初始年龄计算：这是匹配目标表行列的核心，通过当前年龄和年份数反推用户加入时的年龄，确保对角线数据能对应到正确的初始年龄列。
• 转宽表：用pivot_table重新组织数据结构，直接得到以年份为行、队列+初始年龄为列的目标格式。

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