如何将DataFrame的log列按Agent/Customer拆分生成新行？

处理大型文本DataFrame，原始数据结构如下：

ID  CustomerName     topics_seq  topics_count   log  
812199329          ['Due'/'Shift']      2     ["Agent: Hello", "Customer: Hello: Can you help?"] 
813447595          ['Shift']            1     ["Customer: Alright let's go", "Agent: Due to"]

需求是将log列中每个Agent或Customer对应的内容拆分为新行，预期结果：

ID  CustomerName     topics_seq  topics_count   log  
812199329.1          ['Due'/'Shift']      2     "Agent: Hello"
812199329.2          ['Due'/'Shift']      2     "Customer: Hello: Can you help?"
813447595.1          ['Shift']            1     "Customer: Alright let's go"
813447595.2          ['Shift']            1     "Agent: Due to"

尝试以下代码未得到预期结果：

df = df['log'].str.split('Agent|Customer', '/n')

• 先将log列的字符串格式转为列表（如果原始数据中log是字符串而非列表的话，需要先解析）：

  import ast
  # 解析log列的字符串为列表
  df['log'] = df['log'].apply(ast.literal_eval)
  # 若topics_seq也是字符串格式，同样解析为列表
  df['topics_seq'] = df['topics_seq'].apply(ast.literal_eval)
  

• 使用pandas的explode方法将列表中的每个元素拆分为单独行，保留原索引用于后续ID编号：

  df_exploded = df.explode('log', ignore_index=False)
  

• 对ID进行重命名，在原ID后添加序号：

  df_exploded['ID'] = df_exploded.groupby(df_exploded.index)['ID'].transform(
      lambda x: x.astype(str) + '.' + (x.groupby(level=0).cumcount() + 1).astype(str)
  )
  

• 重置索引并整理最终结果：

  df_exploded = df_exploded.reset_index(drop=True)
  

完整代码示例 #

import pandas as pd
import ast

# 构造示例数据
data = {
    'ID': [812199329, 813447595],
    'CustomerName': ['', ''],
    'topics_seq': ["['Due'/'Shift']", "['Shift']"],
    'topics_count': [2, 1],
    'log': ['["Agent: Hello", "Customer: Hello: Can you help?"]', '["Customer: Alright let\'s go", "Agent: Due to"]']
}

df = pd.DataFrame(data)

# 解析字符串列为列表
df['log'] = df['log'].apply(ast.literal_eval)
df['topics_seq'] = df['topics_seq'].apply(ast.literal_eval)

# 拆分log列为多行
df_exploded = df.explode('log', ignore_index=False)

# 生成带序号的ID
df_exploded['ID'] = df_exploded.groupby(df_exploded.index)['ID'].transform(
    lambda x: x.astype(str) + '.' + (x.groupby(level=0).cumcount() + 1).astype(str)
)

# 重置索引
df_exploded = df_exploded.reset_index(drop=True)

print(df_exploded)

运行后输出结果：

ID CustomerName       topics_seq  topics_count                              log
0  812199329.1                        [Due/Shift]             2                  Agent: Hello
1  812199329.2                        [Due/Shift]             2  Customer: Hello: Can you help?
2  813447595.1                          [Shift]             1    Customer: Alright let's go
3  813447595.2                          [Shift]             1                Agent: Due to

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