使用json列表在python中保存csv

Question

我正在尝试从一个文件夹解析python中的多个json文件，并将它们保存到一个csv中

这是我的“json”文件格式：

{
  "width": 4032,
  "height": 3024,
  "ispano": false,
  "objects": [
    {
      "key": "vERA48mAToOV36JrGge-8w",
      "label": "regulatory--no-heavy-goods-vehicles--g2",
      "bbox": {
        "xmin": 1702.96875,
        "ymin": 812.84765625,
        "xmax": 2181.375,
        "ymax": 1304.54296875
      },
      "properties": {
        "barrier": false,
        "occluded": false,
        "out-of-frame": false,
        "exterior": false,
        "ambiguous": false,
        "included": false,
        "direction-or-information": false,
        "highway": false,
        "dummy": false
      }
    },
    {
      "key": "MXdgK-YrQrSrATvLYkJ7kQ",
      "label": "information--dead-end--g1",
      "bbox": {
        "xmin": 1283.625,
        "ymin": 488.7421875,
        "xmax": 1739.390625,
        "ymax": 1050.57421875
      },
      "properties": {
        "barrier": false,
        "occluded": false,
        "out-of-frame": false,
        "exterior": false,
        "ambiguous": false,
        "included": false,
        "direction-or-information": false,
        "highway": false,
        "dummy": false
      }
    }
  ]
}

我不需要所有的信息，所以我查了所有的子字典。以下是我在python中提取数据的方式：

import pandas as pd
import glob
import json 
from datetime import datetime
import csv

data = []

root = glob.glob("./labels/*.json")

for single_file in root:
    with open(single_file, "r") as f:
        json_file = json.load(f)

我在一个列表中迭代子目录，如下所示：

for sub_list in json_file["objects"]:
    print (sub_info)

lst = []
count = 0
for key, val in sub_list.items():
    #print(val)
    lst.append([
        sub_child["key"],
        sub_child["label"],
        sub_child["bbox"]["xmin"],
        sub_child["bbox"]["ymin"],
        sub_child["bbox"]["xmax"],
        sub_child["bbox"]["ymax"]
    ])
#print(lst)

# Add headers
lst.insert(0, ["key","label","xmin","ymin","xmax","ymax"])

dir = "./"
with open(os.path.join(dir,"test.csv"),"w", newline="") as d:
    writer = csv.writer(d)
    #writer.writerow(lst)
    writer.writerows(lst)
    count += 1

print('updated csv')

它保存一个名为“test.csv”的csv文件，但只包含最后一行的信息，而不是所有json文件中的信息

我想保存csv，其中包括所有json文件中提到的信息

我想这样

|文件名|键|标签| xmin | ymin | xmax | ymax |

它包括相应的文件名、键、标签、xmin、ymin。xmax，ymax

你能帮我解决我的问题吗

Answer 1

您可以在迭代对象时将每一行写入文件：

import glob
import json 
import csv

with open('test.csv', 'w', newline='') as f_csv:
    csv_output = csv.writer(f_csv)
    csv_output.writerow(["file_name", "key", "label", "xmin", "ymin", "xmax", "ymax"])
    
    for single_file in glob.glob("*.json"):
        print(single_file)
        
        with open(single_file) as f_json:
            json_data = json.load(f_json)

        for object in json_data["objects"]:
            csv_output.writerow([
                single_file,
                object["key"],
                object["label"],
                object["bbox"]["xmin"],
                object["bbox"]["ymin"],
                object["bbox"]["xmax"],
                object["bbox"]["ymax"]
            ])

给你test.txt如下：

file_name,key,label,xmin,ymin,xmax,ymax
test1.json,vERA48mAToOV36JrGge-8w,regulatory no-heavy-goods-vehicles g2,1702.96875,812.84765625,2181.375,1304.54296875
test1.json,MXdgK-YrQrSrATvLYkJ7kQ,information dead-end g1,1283.625,488.7421875,1739.390625,1050.57421875
test2.json,vERA48mAToOV36JrGge-8w,regulatory no-heavy-goods-vehicles g3,1702.96875,812.84765625,2181.375,1304.54296875
test2.json,MXdgK-YrQrSrATvLYkJ7kQ,information dead-end g1,1283.625,488.7421875,1739.390625,1050.57421875