如何基于其他行列条件重塑Pandas DataFrame？

输入数据复现 #

import numpy as np
import pandas as pd

input_lst = [[27141, 0, 0, 2081.39, np.nan, np.nan, '31/05/2025', '31/03/2021'],
             [26142, 401.04, 1934.52, 0, np.nan, np.nan, '01/04/2021', '20/11/2009'],
             [27748, 0, 0, 266.09, np.nan, np.nan, '18/01/2011', '30/04/2005'],
             [26742, 0, 990.48, 0, np.nan, np.nan, '21/06/2011', '27/06/2008'],
             [27564, 0, 1173.24, 466.33, np.nan, np.nan, '10/06/2004', '31/12/2004']]
input_headers = ['Ref', 'ABC', 'DEF', 'GHI', 'JKL', 'MNO', 'Commence Date 1', 'Commence Date 2']
test_df = pd.DataFrame(input_lst, columns=input_headers)

重塑步骤与代码 #

按照给定规则实现数据转换：

• 将ABC、DEF、GHI、JKL、MNO列转为长表结构，提取Type和Amount
• 过滤掉Amount为0或空的无效行
• 根据Type匹配对应的起始日期
• 整理列顺序得到目标结构

# 1. 宽表转长表，保留Ref和日期列
melted_df = test_df.melt(
    id_vars=['Ref', 'Commence Date 1', 'Commence Date 2'],
    value_vars=['ABC', 'DEF', 'GHI', 'JKL', 'MNO'],
    var_name='Type',
    value_name='Amount'
)

# 2. 过滤无效行：排除Amount为0或NaN的记录
filtered_df = melted_df[(melted_df['Amount'] != 0) & (~melted_df['Amount'].isna())]

# 3. 根据Type选择对应的起始日期
filtered_df['Commence Date'] = filtered_df.apply(
    lambda row: row['Commence Date 1'] if row['Type'] in ['ABC', 'DEF'] else row['Commence Date 2'],
    axis=1
)

# 4. 调整列顺序并重置索引
result_df = filtered_df[['Ref', 'Amount', 'Type', 'Commence Date']].reset_index(drop=True)

最终结果 #

运行代码后得到的result_df与目标结构完全一致：

Ref   Amount Type Commence Date
0  27141  2081.39  GHI    31/03/2021
1  26142   401.04  ABC    01/04/2021
2  26142  1934.52  DEF    01/04/2021
3  27748   266.09  GHI    30/04/2005
4  26742   990.48  DEF    21/06/2011
5  27564  1173.24  DEF    10/06/2004
6  27564   466.33  GHI    31/12/2004

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