如何拆分DataFrame中带引号的CSV列并生成指定命名列？

解决方案思路 #

借助io.StringIO把每行的message字段内容转为内存文本流，再用pd.read_csv批量解析带引号的CSV内容，最后将解析结果与原DataFrame合并，替换原message字段为拆分后的指定列。

完整实现代码 #

import pandas as pd
import json
from io import StringIO

# 测试数据
dfMyData = pd.DataFrame({"_raw": [\
            """{"timestamp":1691096387000,"message":"20230803 20:59:47,ip-123-123-123-123,mickey,321.321.321.321,111111,10673010,type,,,'I am a, quoted, string, with commas,',0,,","logstream":"Blah1","loggroup":"group 1"}""",
            """{"timestamp":1691096386000,"message":"20230803 21:00:47,ip-456-456-456-456,mouse,654.654.654.654,222222,10673010,type,,,'I am another quoted string',0,,","logstream":"Blah2","loggroup":"group 2"}"""
            ]})
# message字段拆分后的列名
MessageColumnNames =  ["Timestamp","dest_host","username","src_ip","port","number","type","who_knows","message_string","another_number","who_knows2","who_knows3"]

# 原JSON解析步骤
dfMyData['_raw'] = dfMyData['_raw'].map(json.loads)
dfMyData = pd.json_normalize(dfMyData.to_dict(orient='records'))

# 核心函数：解析单条带引号的CSV字符串
def parse_quoted_csv(s):
    return pd.read_csv(
        StringIO(s),
        header=None,
        names=MessageColumnNames,
        quotechar="'",  # 指定单引号为字段引用符
        skipinitialspace=True  # 忽略分隔符后的空格
    )

# 批量解析所有message内容并合并为DataFrame
parsed_messages = dfMyData['message'].apply(parse_quoted_csv).concat()

# 重置索引确保与原DataFrame对齐
parsed_messages = parsed_messages.reset_index(drop=True)
dfMyData = dfMyData.reset_index(drop=True)

# 合并原数据与解析结果，删除原message字段
final_df = pd.concat([dfMyData.drop('message', axis=1), parsed_messages], axis=1)

# 查看最终结果
print(final_df)

关键细节 #

• quotechar="'专门匹配示例中用单引号包裹的含逗号字符串，避免错误拆分
• skipinitialspace=True处理CSV中可能存在的分隔符后空格问题
• 通过apply+concat实现批量解析，保证每行内容都对应到指定列名

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