python - 具有数据初始化的 CSV 列的 JSON 键值

Question

csv 文件在每一行中都有 json，但在一列中，格式如下:

╔═══╦══════════════════════╗
║   ║ A                    ║
╠═══╬══════════════════════╣
║ 2 ║ {"discover":"123"}   ║
║ 3 ║ {"offer":"321"}      ║
║ 4 ║ {"roadmap":"788789"} ║
║ 5 ║ {"nebravvska":"890"} ║
╚═══╩══════════════════════╝

我希望将上述 JSON 键和值写入 csv col1 和 col2 中的每一行，而不替换现有数据。俄亥俄州和加利福尼亚州是现有数据。

预期输出:

╔═══╦════════════════╦══════════╗
║   ║ A              ║ B        ║
╠═══╬════════════════╬══════════╣
║ 1 ║     discover   ║ 123      ║
║ 3 ║     offer      ║ 321      ║
║ 4 ║     roadmap    ║ 78890    ║
║ 5 ║     nebrask    ║ 890      ║
║ 6 ║     ohio       ║ hjsd8943 ║
║ 7 ║     california ║ 68yubkj  ║
╚═══╩════════════════╩══════════╝

我使用的是 pycharm 2.6。我下面的代码是将键和值写入一列

    ╔═══╦════════════════╗
    ║   ║ A              ║
    ╠═══╬════════════════╣
    ║ 1 ║ discover 123   ║
    ║ 2 ║ offer 321      ║
    ║ 3 ║ roadmap 788789 ║
    ║ 4 ║ nebravvska 890 ║
    ╚═══╩════════════════╝

    with open("JSONsinfile.csv","rU") as infile:
            with open("output.csv","a+") as outfile:
                writer = csv.writer(outfile, delimiter=' ')
                for line in infile:
                    d = json.loads(line)
                    writer.writerows(d.items())

        infile.close()
        outfile.close()

Answer 1

使用“pandas”库，您可以得到以下解决方案:

1)- 安装 Pandas

pip install pandas

2)- 脚本:

import csv, json
import csv
import pandas as pd

inp_csv = pd.read_csv("JSONsinfile.csv")
lst_of_dict = inp_csv['{"discover":"123"}'].tolist()
mydict={key:value for elem in lst_of_dict for key,value in eval(elem).items()}

with open('output.csv', 'wb') as csv_file:
    writer = csv.writer(csv_file)
    for key, value in mydict.items():
        writer.writerow([key, value])