python - 如何将键值管道分隔文件转换为带标题的完美 csv 文件

Question

您好，我有一个格式的文件。

key1=abc||key2=ajdskj||name=ankush||contact=123444
key1=def||name=reddy||contact=456778
key1=aef||address=ashaskawe||name=john

如何使用 python 将其转换为任何带标题的分隔文件。喜欢

key1||key2||name||contact||address
abc||ajdskj||ankush||123444||NULL
def||NULL||reddy||456778||NULL
aef||NULL||john||NULL||ashaskawe

如果有更多的字段，请告诉我可能的方法是什么。

我正在尝试使用 csv 阅读器和 pandas 来读取文件，但我不知道如何分离键和值。

谢谢你的帮助

Answer 1

这是一种使用标准库中的工具并维护列顺序的方法。 messy_data.txt 文件包含原始数据，cleaner_data.txt 是保存清洁数据的地方:

from collections import defaultdict, OrderedDict

with open('messy_data.txt') as infile, open('cleaner_data.txt','w') as outfile:
    whole_data = [x.strip().split("||") for x in infile]
    headers = []
    for x in whole_data:
        for k in [y.split("=")[0] for y in x]:
            if k not in headers:
                headers.append(k)
    whole_data = [dict(y.split("=") for y in x) for x in whole_data]
    output = defaultdict(list)
    for header in headers:
        for d in whole_data:
            output[header].append(d.get(header,'NULL'))
    output = OrderedDict((x,output.get(x)) for x in headers)
    outfile.write("||".join(list(output.keys()))+"\n")
    for row in zip(*output.values()):
        outfile.write("||".join(row)+"\n")

这应该产生:

key1||key2||name||contact||address
abc||ajdskj||ankush||123444||NULL
def||NULL||reddy||456778||NULL
aef||NULL||john||NULL||ashaskawe

编辑:

更便于调试的脚本:

from collections import defaultdict, OrderedDict

with open('messy_data.txt') as infile, open('cleaner_data.txt','w') as outfile:
    whole_data = [x.strip().split("||") for x in infile]
    headers = []
    for x in whole_data:
        for k in [y.split("=")[0] for y in x]:
            if k not in headers:
                headers.append(k)
    #whole_data = [dict(y.split("=") for y in x) for x in whole_data]
    whole_data2 = []
    for x in whole_data:
        temp_list = [y.split("=") for y in x]
        try:
            temp_dict = dict(temp_list)
            whole_data2.append(temp_dict)
        except:
            print(temp_list)
            continue
    output = defaultdict(list)
    for header in headers:
        for d in whole_data2:
            output[header].append(d.get(header,'NULL'))
    output = OrderedDict((x,output.get(x)) for x in headers)
    print(output)
    outfile.write("||".join(list(output.keys()))+"\n")
    for row in zip(*output.values()):
        outfile.write("||".join(row)+"\n")

我希望这证明是有用的。