从带引号含内部逗号的字符串列表创建DataFrame遇问题求助

问题场景 #

你有如下格式的字符串列表：

lst = ['"column1","column2","column3","column4","column5","column6","column7"',
       '"A",2022/03/11 00:03:08,"55","01","Bob, Pit","Bob",""',
       '"B",2021/04/11 09:13:06,"","","Niel","Arm","02"']

尝试手动分割字符串转DataFrame时遇到两个核心问题：

• 包含逗号的字段（如"Bob, Pit"）被错误拆分到多列
• 字段的双引号无法自动去除，同时像"01"这样的前导零容易丢失

你的原始代码如下：

dt = pd.DataFrame(lst, columns=["data"])

dt = dt["data"].str.split(',', expand=True)
new_header = dt.iloc[0] #grab the first row for the header
dt = dt[1:] #take the data less the header row
dt.columns = new_header #set the header row as the df header
dt

简便解决方法 #

直接利用Python的csv模块或pandas内置的read_csv函数，它们会自动处理带引号的CSV格式，完美解决上述问题。

方法一：使用csv.reader配合StringIO #

import pandas as pd
import csv
from io import StringIO

lst = ['"column1","column2","column3","column4","column5","column6","column7"',
       '"A",2022/03/11 00:03:08,"55","01","Bob, Pit","Bob",""',
       '"B",2021/04/11 09:13:06,"","","Niel","Arm","02"']

# 将列表拼接为标准CSV格式字符串
csv_content = '\n'.join(lst)
# 使用csv.reader解析，自动识别带引号的字段
csv_reader = csv.reader(StringIO(csv_content), quotechar='"')
# 转换为DataFrame
df = pd.DataFrame(list(csv_reader))
# 设置表头并移除表头行
df.columns = df.iloc[0]
df = df[1:].reset_index(drop=True)

print(df)

方法二：直接用pandas.read_csv（更简便） #

import pandas as pd
from io import StringIO

lst = ['"column1","column2","column3","column4","column5","column6","column7"',
       '"A",2022/03/11 00:03:08,"55","01","Bob, Pit","Bob",""',
       '"B",2021/04/11 09:13:06,"","","Niel","Arm","02"']

csv_content = '\n'.join(lst)
# 自动解析带引号字段，dtype=str保留前导零，keep_default_na=False保留空字符串
df = pd.read_csv(
    StringIO(csv_content),
    quotechar='"',
    dtype=str,
    keep_default_na=False
)

print(df)

方案优势 #

• 自动识别带引号的字段，确保Bob, Pit这类包含逗号的内容留在同一列
• 自动去除字段外层的双引号
• 通过dtype=str强制保留字符串类型，避免"01"被转为整数丢失前导零
• 空字符串会被正确保留，不会被转为NaN

内容的提问来源于stack exchange，提问作者Nafisa Anjum Samia