Pandas：将DataFrame多列转成行并新增etf_pct_change列

问题场景 #

现有Pandas DataFrame包含两类核心列：以.NS结尾的ETF数值列、对应的.NS_pct_change涨跌幅列，此外还有avg、weekday等辅助列。当前仅能通过melt拆分出ETF列生成etf_mrr，但无法同时关联对应涨跌幅列生成etf_pct_change，需要得到包含Date、etf、etf_mrr、etf_pct_change、avg、weekday的目标结构。

原代码 #

import pandas as pd
import numpy as np
data = {
    'NIFTYBEES.NS': [0.26, 0.18],
    'abc.NS': [0.1, 0.1],
    'NIFTYBEES.NS_pct_change': [0.86, -0.21],
    'abc.NS_pct_change': [0.2, 0.2],
    'avg': [0.4775, 0.76],
    'weekday': ['Monday', 'Tuesday']
}
Date = pd.date_range('2024-04-29', periods=2, freq='D')
df = pd.DataFrame(data, index=Date)
df.reset_index(inplace=True)

# Use melt to reshape the DataFrame
df_melted = df.melt(id_vars=['index', 'avg', 'weekday'], value_vars=df.filter(regex='\.NS$').columns, 
                    var_name='etf', value_name='value')
df_melted['etf_mrr'] = df_melted['value']
df_melted.drop(columns=['value'], inplace=True)
df_melted = df_melted[['index', 'etf', 'etf_mrr', 'avg', 'weekday']]
df_melted.rename(columns={'index': 'Date'}, inplace=True)
print(df_melted)

当前输出 #

Date           etf  etf_mrr     avg  weekday
0 2024-04-29  NIFTYBEES.NS     0.26  0.4775   Monday
1 2024-04-30  NIFTYBEES.NS     0.18  0.7600  Tuesday
2 2024-04-29        abc.NS     0.10  0.4775   Monday
3 2024-04-30        abc.NS     0.10  0.7600  Tuesday

期望输出 #

Date           etf  etf_mrr etf_pct_change     avg  weekday
0 2024-04-29  NIFTYBEES.NS     0.26           0.86  0.4775   Monday
1 2024-04-29        abc.NS     0.10           0.20  0.4775   Monday
2 2024-04-30  NIFTYBEES.NS     0.18          -0.21  0.7600  Tuesday
3 2024-04-30        abc.NS     0.10           0.20  0.7600  Tuesday

解决方案 #

通过分别拆分ETF数值列和涨跌幅列，再按日期与ETF名称关联合并，即可实现目标结构：

修改后代码 #

import pandas as pd
import numpy as np

data = {
    'NIFTYBEES.NS': [0.26, 0.18],
    'abc.NS': [0.1, 0.1],
    'NIFTYBEES.NS_pct_change': [0.86, -0.21],
    'abc.NS_pct_change': [0.2, 0.2],
    'avg': [0.4775, 0.76],
    'weekday': ['Monday', 'Tuesday']
}
Date = pd.date_range('2024-04-29', periods=2, freq='D')
df = pd.DataFrame(data, index=Date)
df.reset_index(inplace=True)
df.rename(columns={'index': 'Date'}, inplace=True)

# 拆分ETF数值列，生成etf与etf_mrr
mrr_df = df.melt(
    id_vars=['Date', 'avg', 'weekday'],
    value_vars=df.filter(regex=r'\.NS$').columns,
    var_name='etf',
    value_name='etf_mrr'
)

# 拆分涨跌幅列，提取对应ETF名称后生成etf_pct_change
pct_df = df.melt(
    id_vars=['Date'],
    value_vars=df.filter(regex=r'\.NS_pct_change$').columns,
    var_name='etf_raw',
    value_name='etf_pct_change'
)
pct_df['etf'] = pct_df['etf_raw'].str.replace('_pct_change', '')
pct_df.drop(columns=['etf_raw'], inplace=True)

# 合并两个结果，调整列顺序并排序
final_df = pd.merge(mrr_df, pct_df, on=['Date', 'etf'])
final_df = final_df[['Date', 'etf', 'etf_mrr', 'etf_pct_change', 'avg', 'weekday']]
final_df.sort_values('Date', inplace=True)
final_df.reset_index(drop=True, inplace=True)

print(final_df)

代码说明 #

1. 预处理：先将原索引重命名为Date，统一列名格式
2. 拆分ETF数值列：用melt提取所有.NS结尾的列，保留Date、avg、weekday作为关联键，得到etf和对应etf_mrr
3. 拆分涨跌幅列：同样用melt提取.NS_pct_change结尾的列，通过字符串替换去掉_pct_change后缀，匹配到对应的etf名称
4. 合并与整理：按Date和etf合并两个DataFrame，调整列顺序并按日期排序，得到期望结构

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