如何用Python按子串更便捷地拆分CSV数据

需求说明 #

需要将CSV中包含多组key=value格式的字段，按等号前的键拆分补全，确保所有行统一包含所有出现过的键；缺失的键对应位置留空或显示key=，最终在CSV/Excel中能清晰区分哪些字段存在、哪些缺失。

原始CSV数据 #

date, time, ID1, ID2, ID3, "Action=xxx, ProdCode=XXXX, Cmd=xxx, Price=xxxxx, Qty=xxx, TradedQty=xxx, Validity=xxx, Status=xxx, AddBy=xxxxxx, TimeStamp=xxx, ClOrderId=xxx, ChannelId=xxx",x,x,ID4
date, time, ID1, ID2, ID3, "Action=xxx, RetCode=xxx, ProdCode=xxxx, Cmd=xxx, Price=xxxx, Qty=xxx, TradedQty=0, Validity=xxx, Status=xxx, ExtOrderNo=xxxxx, Ref=0, AddBy=xxxxx, Gateway=xxxxx, TimeStamp=xxx, ClOrderId=xxx",x,x,ID4
date, time, ID1, ID2, ID3, "Action=xx, RetCode=xx, ProdCode=xxx, Cmd=xx, Price=xxx, Qty=x, TradedQty=x, Status=xxx, ExtOrderNo=xxx, Ref=xxx, AddBy=xx, Gateway=xxx, TimeStamp=xxx",x,x,ID4
date,time,ID1,ID2,ID3,"Action=xxx, ProdCode=xxx, Cmd=xxx, Price=xxx, Qty=x, ExtOrderNo=xxx, TradeNo=xxx, Ref=@xxx, AddBy=xxx, Gateway=xxx",x,x,ID4

期望效果示例 #

date, time, ID1, ID2, ID3, "Action=xxx, RetCode=, ProdCode=XXXX, Cmd=xxx, Price=xxxxx, Qty=xxx, TradedQty=xxx, Validity=xxx, Status=xxx, ExtOrderNo=, TradeNo=, Ref=, AddBy=xxxxxx, Gateway=, TimeStamp=xxx, ClOrderId=xxx, ChannelId=xxx",x,x,ID4
date, time, ID1, ID2, ID3, "Action=xxx, RetCode=xxx, ProdCode=xxxx, Cmd=xxx, Price=xxxx, Qty=xxx, TradedQty=0, Validity=xxx, Status=xxx, ExtOrderNo=xxxxx, TradeNo=, Ref=0, AddBy=xxxxx, Gateway=xxxxx, TimeStamp=xxx, ClOrderId=xxx, ChannelId=,",x,x,ID4
date, time, ID1, ID2, ID3, "Action=xxx, RetCode=xx, ProdCode=xxx, Cmd=xx, Price=xxx, Qty=x, TradedQty=x, Validity=, Status=xxx, ExtOrderNo=xxx, TradeNo=, Ref=xxx, AddBy=xx, Gateway=xxx, TimeStamp=xxx, ClOrderId=, ChannelId=,",x,x,ID4
date,time,ID1,ID2,ID3,"Action=xxx, RetCode=, ProdCode=xxx, Cmd=xxx, Price=xxx, Qty=x, TradedQty=, Validity=, Status=, ExtOrderNo=xxx, TradeNo=xxx, Ref=@xxx, AddBy=xxx, Gateway=xxx, TimeStamp=, ClOrderId=, ChannelId=,",x,x,ID4

具体实现方法 #

方法1：Excel Power Query（推荐，非编程用户首选） #

Power Query可自动识别所有键并批量补全缺失项，步骤如下：

1. 打开Excel，导入CSV数据：数据 > 获取数据 > 从文件 > 从CSV，选择目标文件。
2. 在Power Query编辑器中，找到包含键值对的列（第6列），点击列标题旁的拆分列 > 按分隔符，选逗号, 作为分隔符，勾选拆分为行。
3. 拆分后每行显示一个key=value，再次点击该列的拆分列 > 按分隔符，选等号=拆分出“键”和“值”两列。
4. 选中所有原始固定列（date、time、ID1等），点击转换 > 透视列，值列选择“值”列，高级选项选不要聚合，缺失值留空。
5. 透视完成后，所有出现过的键会成为单独列，缺失单元格自动留空。若需要key=格式，可使用公式=IF(ISBLANK([@键名]), "键名=", "键名="&[@键名])合并列，再用TEXTJOIN将所有键值对拼接回一个字段。
6. 点击关闭并上载，将处理后的数据导出为CSV或保留在Excel中。

方法2：Python脚本（适合批量/复杂场景） #

用pandas库可快速自动化处理大量数据：

import pandas as pd
import re

# 读取CSV文件
df = pd.read_csv("your_file.csv", quotechar='"', skipinitialspace=True)

# 提取所有出现过的键
all_keys = set()
def parse_key_values(s):
    key_value_pairs = re.findall(r'(\w+)=([^,]+)', s)
    keys = [k for k, _ in key_value_pairs]
    all_keys.update(keys)
    return dict(key_value_pairs)

# 将键值对列转为字典
df['key_dict'] = df.iloc[:, 5].apply(parse_key_values)
all_keys = sorted(all_keys)

# 补全所有键，缺失值为空字符串
for key in all_keys:
    df[key] = df['key_dict'].apply(lambda x: x.get(key, ''))

# 合并补全后的键值对为一个字段
def combine_pairs(row):
    return ', '.join([f"{k}={row[k]}" for k in all_keys])
df['combined_key_values'] = df.apply(combine_pairs, axis=1)

# 整理输出列并导出CSV
output_columns = df.columns[:5].tolist() + ['combined_key_values'] + df.columns[-3:].tolist()
df[output_columns].to_csv("output.csv", index=False, quotechar='"')

• 替换your_file.csv为你的文件名，运行后生成的output.csv会自动补全所有缺失键，缺失项显示key=。

方法3：Excel公式法（适合小数据量） #

手动提取补全，步骤如下：

1. 先整理所有出现过的键，做成表头（如Action、RetCode等）。
2. 用公式提取每个键对应的值：=IFERROR(MID($F2,SEARCH(G$1&"=",$F2)+LEN(G$1&"="),SEARCH(",",$F2&",",SEARCH(G$1&"=",$F2))-SEARCH(G$1&"=",$F2)-LEN(G$1&"=")),"")，其中F2是包含键值对的单元格，G$1是键所在的表头单元格。
3. 提取完所有键的值后，用TEXTJOIN(", ", TRUE, G2&"="&H2, ...)将所有键值对拼接成一个字符串，缺失的键会自动显示key=。

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