Pandas按Market分组后为DataFrame生成Related_Markets拼接列的实现方案

实现思路 #

• 先为每一行生成Ticker-Time-Signal格式的临时拼接字符串
• 按Market分组后，对每个分组内的每一行，排除当前行本身，将组内其余行的临时字符串拼接为结果
• 最后删除临时列得到最终结果，若已提前构造groupedMarket分组对象可直接复用，无需重复分组

完整代码 #

import pandas as pd
import numpy as np

# 示例数据构造
dat = [["Bull_Flag","EURUSD","W","FX"],["Bull_Candle","GBPUSD","D","FX"],["Bull_Volume","UK100","H1","Index"],["Bear_Volume","USDCHF","W","FX"]]
df = pd.DataFrame(dat,columns=['Signal', 'Ticker', 'Time', 'Market'])

# 你已提前执行的分组代码
groupedMarket = df.groupby("Market")

# 步骤1：生成每行的临时拼接字符串
df['tmp_str'] = df['Ticker'] + '-' + df['Time'] + '-' + df['Signal']

# 步骤2：分组计算关联市场字段，直接复用已有的groupedMarket对象
def calc_related(str_series):
    result_list = []
    for idx in str_series.index:
        # 排除当前行，拼接其余行的字符串，默认用逗号+空格分隔，需空格分隔可修改sep参数为' '
        other_strs = str_series[str_series.index != idx]
        result_list.append(other_strs.str.cat(sep=', ') if len(other_strs) > 0 else '')
    return result_list

df['Related_Markets'] = groupedMarket['tmp_str'].transform(calc_related)

# 步骤3：删除临时列
df.drop('tmp_str', axis=1, inplace=True)

# 输出结果
print(df)

输出验证 #

运行后结果与期望一致：

Signal  Ticker Time Market                        Related_Markets
0    Bull_Flag  EURUSD    W     FX  GBPUSD-D-Bull_Candle, USDCHF-W-Bear_Volume
1  Bull_Candle  GBPUSD    D     FX   EURUSD-W-Bull_Flag, USDCHF-W-Bear_Volume
2  Bull_Volume   UK100   H1  Index                                           
3  Bear_Volume  USDCHF    W     FX    EURUSD-W-Bull_Flag, GBPUSD-D-Bull_Candle

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