Python中将CSV数据转换为嵌套JSON

Question

Python中将CSV数据转换为嵌套JSON

5

我需要将一个CSV数据文件转换为嵌套的JSON格式，用于一个应用程序。下面是我目前的Python代码，在处理单个客户或账户文档时可以正常工作，但是在处理CSV文件中的所有客户时无法创建json dump。

以下是提供的Python代码，你可以了解到我的目标是什么。如果有任何现有的解决方案，请告诉我。

样例Python代码：

import pandas as pd
from itertools import groupby 
from collections import OrderedDict
import json    

df = pd.read_csv('cust.csv', dtype={
        "ClientID" : str,
        "ClientName" : str,
        "AcctID" : str,
        "AcctNbr" : str,
        "AcctTyp" : str
    })

results = []

for (ClientID, ClientName), bag in df.groupby(["ClientID", "ClientName"]):
contents_df = bag.drop(["ClientID", "ClientName"], axis=1)
subset = [OrderedDict(row) for i,row in contents_df.iterrows()]
results.append(OrderedDict([("ClientID", ClientID),("ClientName", ClientName),("subset", subset)]))

print json.dumps(results[0], indent=4)

with open('ExpectedJsonFile.json', 'w') as outfile:
outfile.write(json.dumps(results[0], indent=4))

示例输入CSV：

ClientID,ClientName,AcctID,AcctNbr,AcctTyp
----------------------------------------------------------
00001,John George,812001,812001095,DDA
00001,John George,813002,813002096,SAV
00001,John George,814003,814003097,AFS
00024,Richard Polado,512987,512987085,ML
00024,Richard Polado,512983,512983086,IL
00345,John Cruze,1230,123001567,SAV
00345,John Cruze,5145,514502096,CD
00345,John Cruze,7890,7890033527,SGD

期望输出JSON：

{  
   "clientId":00001,
   "ClientName":"John George",
   "subset":[  
      {  
         "AcctID":812001,
         "AcctNbr":"812001095",
         "AcctTyp":"DDA",
      },
      {  
         "AcctID":813002,
         "AcctNbr":"813002096",
         "AcctTyp":"SAV",
      },
      {  
         "AcctID":814003,
         "AcctNbr":"814003097",
         "AcctTyp":"AFS",
      }
   ]
},
{  
   "clientId":00024,
   "ClientName":"Richard Polado",
   "subset":[  
      {  
         "AcctID":512987,
         "AcctNbr":"512987085",
         "AcctTyp":"ML",
      },
      {  
         "AcctID":512983,
         "AcctNbr":"512983086",
         "AcctTyp":"IL",
      }
   ]
}

这些文档应该继续为其他成千上万的客户创建。

- ShivKumar

它只为第一个ClientID创建一个文档。 { "clientId":00001, "ClientName":"John George", "subset":[ { "AcctID":812001, "AcctNbr":"812001095", "AcctTyp":"DDA", }, { "AcctID":813002, "AcctNbr":"813002096", "AcctTyp":"SAV", }, { "AcctID":814003, "AcctNbr":"814003097", "AcctTyp":"AFS", } ] } - ShivKumar

听起来像是一个调试问题。编写一些代码来检测它是否在循环的第二次通过时到达文件写入部分。它没有到达那里吗？或者，也许你的文件写入失败是因为它正在使用相同的文件名？ - halfer

我不熟悉Python，但是循环不需要缩进吗？在这种情况下，您似乎没有任何缩进。 - halfer

@ShivKumar 我的解决方案有效吗？ - ababuji

@Abhishek，非常感谢，完美地按照要求带来了数据，无法期待更好的结果...你是最棒的...现在让我尝试将这个JSON导入到MongoDB中，希望它能够正常工作！！ - ShivKumar

显示剩余2条评论

2个回答

2

使用dictList.append(anotherDict.copy())，否则你将在列表中得到相同的字典对象。

更多细节请参考这个问题：创建Python字典列表。

"Original Answer"翻译成"最初的回答"。

- Prasad Sawant

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- ababuji · Accepted Answer

解决方案按每个'ClientID'和'ClientName'对分组。

您的数据框：

df = pd.DataFrame([['00001','John George','812001','812001095','DDA'],
['00001','John George','813002','813002096','SAV'],
['00001','John George','814003','814003097','AFS'],
['00024','Richard Polado','512987','512987085','ML'],
['00024','Richard Polado','512983','512983086','IL'],
['00345','John Cruze','1230','123001567','SAV'],
['00345','John Cruze','5145','514502096','CD'],
['00345','John Cruze','7890','7890033527','SGD']])

df.columns = ['ClientID','ClientName','AcctID','AcctNbr','AcctTyp']

现在

finalList = []
finalDict = {}
grouped = df.groupby(['ClientID', 'ClientName'])
for key, value in grouped:


    dictionary = {}

    j = grouped.get_group(key).reset_index(drop=True)
    dictionary['ClientID'] = j.at[0, 'ClientID']
    dictionary['ClientName'] = j.at[0, 'ClientName']


    dictList = []
    anotherDict = {}
    for i in j.index:

        anotherDict['AcctID'] = j.at[i, 'AcctID']
        anotherDict['AcctNbr'] = j.at[i, 'AcctNbr']
        anotherDict['AcctTyp'] = j.at[i, 'AcctTyp']

        dictList.append(anotherDict)

    dictionary['subset'] = dictList


    finalList.append(dictionary)

import json
json.dumps(finalList)

提供：

'[
   {"ClientID": "00001", 
    "ClientName": "John George", 
    "subset": 
            [{"AcctID": "814003", 
              "AcctNbr": "814003097", 
              "AcctTyp": "AFS"}, 

             {"AcctID": "814003", 
              "AcctNbr": "814003097", 
              "AcctTyp": "AFS"}, 

             {"AcctID": "814003", 
              "AcctNbr": "814003097", 
              "AcctTyp": "AFS"}]

   }, 

  {
   "ClientID": "00024", 
   "ClientName": "Richard Polado", 
   "subset": 
            [{"AcctID": "512983", 
              "AcctNbr": "512983086", 
              "AcctTyp": "IL"}, 

             {"AcctID": "512983", 
              "AcctNbr": "512983086", 
              "AcctTyp": "IL"}]
   }, 

  {
   "ClientID": "00345", 
   "ClientName": "John Cruze", 
   "subset": 
            [{"AcctID": "7890", 
              "AcctNbr": "7890033527", 
              "AcctTyp": "SGD"}, 

             {"AcctID": "7890", 
              "AcctNbr": "7890033527", 
              "AcctTyp": "SGD"}, 

             {"AcctID": "7890", 
              "AcctNbr": "7890033527", 
              "AcctTyp": "SGD"}]
   }

]'

这是您想要的方式吗？