如何使用Python解析数据多行多行字符串并将数据提取到JSON文件中

Question

我试图将下面的数据添加到JSON file..this中只是一个示例，因为我还在学习如何实现它。

ITEM    QTY   ID        DESCR   LOCATION                    
item1   3     it111     Gold    Rack11      
item2   10    it222     Silver  Rack22   
item3   6     it333     Red     Rack33      
item4   1     it444     Blue    Rack44 

例如，下面我可以添加类和所有者值，因为它只有一行和单个字符串输出。但是关键细节输出由多行键和值组成，我不知道如何逐行读取并解析到json。

{
     "product": [
        {
         "class":"food",
         "owner":"user1",
        }
     ]
}

预期的最终产出如下

{
     "product": [
        {
         "class":"food",
         "owner":"user1",
         "details": [
         {
          "item":"item1",
          "qty":"3",
          "id":"it111",
          "desc":"Gold",
          "loct":"Rack11"
         },
         {
          "item":"item2",
          "qty":"10",
          "id":"it222",
          "desc":"Silver",
          "loct":"Rack22"
         },
         {
          "item":"item3",
          "qty":"6",
          "id":"it333",
          "desc":"Red",
          "loct":"Rack33"
         },
         {
          "item":"item4",
          "qty":"1",
          "id":"it444",
          "desc":"Blue",
          "loct":"Rack44"
         }
        ] 
       }
     ]
}

我的名单如下

product = "class","owner","details"

问题是，我不知道如何将细节输出到" details“，并将其形成json嵌套结构。感谢你的帮助。谢谢

如果输入文本是以制表符分隔的，那么使用csv读取器就是解决方案。works...as建议如下。基于信息.我尝试对另一组类似的输入文本应用相同的内容，它给出了一个错误

ValueError: need more than 4 values to unpack

输入文本示例集如下

Local Interface   Parent Interface   Chassis Id          Port info    System Name
xe-3/0/4.0        ae31.0             b0:c6:9a:63:80:40   xe-0/0/0.0   host.xsrt1.net
xe-3/0/5.0        ae31.0             b0:c6:9a:63:80:40   xe-0/0/1.0   host.xsrt1.net
xe-3/0/6.0        ae31.0             b0:c6:9a:63:80:40   xe-0/0/2.0   host.xsrt1.net
xe-3/0/7.0        ae31.0             b0:c6:9a:63:80:40   xe-0/0/3.0   host.xsrt1.net
xe-3/0/0.0        ae31.0             b0:c6:9a:63:80:40   xe-0/1/0.0   host.xsrt1.net
xe-3/0/1.0        ae31.0             b0:c6:9a:63:80:40   xe-0/1/1.0   host.xsrt1.net
xe-3/0/2.0        ae31.0             b0:c6:9a:63:80:40   xe-0/1/2.0   host.xsrt1.net
xe-3/0/3.0        ae31.0             b0:c6:9a:63:80:40   xe-0/1/3.0   host.xsrt1.net

我不知道为什么..。但是，也许它并不完全是一个以format..if分隔的制表符--这就是如何将它转换成有效的制表符分隔格式？谢谢

Update1：对于上面的输入，我用下面的测试代码分成了几行

with open('lldp.csv', 'r', newline='') as csv_file:
   reader = csv.reader(line.replace('  ', ',') for line in csv_file)
   my_list = list(reader)
   pprint(my_list)  

输出如下

[['Local Interface',' Parent Interface',' Chassis Id','','','','','Port 
info','','System Name'],['xe-3/0/4.0','','','','ae31.0','','','','','',' 
b0:c6:9a:63:80:40',' xe-0/0/0.0',' host.jnpr.net'],['xe- 
3/0/5.0','','','','ae31.0','','','','','',' b0:c6:9a:63:80:40',' xe- 
0/0/1.0',' host.jnpr.net'],['xe-3/0/6.0','','','','ae31.0','','','','','',' 
b0:c6:9a:63:80:40',' xe-0/0/2.0',' host.jnpr.net'],['xe- 
3/0/7.0','','','','ae31.0','','','','','',' b0:c6:9a:63:80:40',' xe- 
0/0/3.0',' host.jnpr.net'],['xe-3/0/0.0','','','','ae31.0','','','','','',' 
b0:c6:9a:63:80:40',' xe-0/1/0.0',' host.jnpr.net'],['xe- 
3/0/1.0','','','','ae31.0','','','','','',' b0:c6:9a:63:80:40',' xe- 
0/1/1.0',' host.jnpr.net'],['xe-3/0/2.0','','','','ae31.0','','','','','',' 
b0:c6:9a:63:80:40',' xe-0/1/2.0',' host.jnpr.net'],['xe- 
3/0/3.0','','','','ae31.0','','','','','',' b0:c6:9a:63:80:40',' xe- 
0/1/3.0',' host.jnpr.net']]  

从上面如何删除多余的'‘和如何开始读从第2行(第一行只是标题)。从列表中，我希望像上面所指定的那样将其解析到json上。

我将为上面的问题打开一个新的问题，并集中于上面的输出，谢谢。

Answer 1

如果列是制表符分隔的，我建议您使用csv阅读器。

首先，使用"class"和"owner"值创建一个基本字典，并为"details"创建一个空列表。然后逐个解析行并添加单独的详细信息。

import csv
import json

out = {
    "product": [
        {
            "class": "food",
            "owner": "user1",
            "details": []
        }
    ]
}

with open("data.csv") as f:
    reader = csv.reader(f, delimiter="\t")
    next(reader) # skip header

    for row in reader:

        detail = {
            "item": row[0],
            "qty" : row[1],
            "id"  : row[2],
            "desc": row[3],
            "loct": row[4]
        }

        out["product"][0]["details"].append(detail)

# now out contains the final dictionary, you can output it like this:
print(json.dumps(out, indent=4))

我不清楚，如果你只有一个项目，为什么你会把"product"作为一个列表--我想你会用更多的产品丰富这个列表。

Answer 2

我不知道您是如何迭代类和ownder的初始列表的，但是他将生成您想要的输出：

import pandas as pd
import json


data = [
['item1','3','it111','Gold','Rack11'],
['item2','10','it222','Silver','Rack22'],  
['item3','6','it333','Red','Rack33'],      
['item4','1','it444','Blue','Rack44']]

df = pd.DataFrame(data,columns=['ITEM','QTY','ID','DESCR','LOCATION'])

#Above was so I had the data to work with, but you can read it in with pandas if its an excel or csv file

# df = pd.read_csv('path/to/datafile.csv')

jsonDict = {}
jsonDict["product"] = []
jsonDict["product"].append({})

jsonDict["product"][0]["class"] = "food"
jsonDict["product"][0]["owner"] = "user1"
jsonDict["product"][0]["details"] = []

for i, row in df.iterrows():

    temp_dict = {}
    temp_dict['item'] = row['ITEM'] 
    temp_dict['qty'] = row['QTY'] 
    temp_dict['id_num'] = row['ID'] 
    temp_dict['desc'] = row['DESCR'] 
    temp_dict['loct'] = row['LOCATION'] 

    jsonDict["product"][0]["details"].append(temp_dict)

with open('data.json', 'w') as fp:
    json.dump(jsonDict, fp, indent=3, sort_keys=False)