从xlsx到JSON转换中的Dataframe对象

Question

我是Python编程新手，我正在尝试编写一个程序来读取xlxs文件并将其转换为json。（我一直在使用python3和Jupyter笔记本，但是我遇到了一些问题和困难。）

=================================

我的xlsx文件中有两行，每行有四列：

id     label        id_customer     label_customer

6     Sao Paulo      CUST-99992         Brazil

92    Hong Hong      CUST-88888         China

=================================

这是我的密码：

import pandas as pd
import json

file_imported = pd.read_excel('testing.xlsx', sheet_name = 'Plan1')

list1 = []
list  = []
for index, row in file_imported.iterrows():
    list.append ({
            "id"       : int(row['id']),
            "label"    : str(row['label']),
            "Customer" : list1
            })

    list1.append ({
           "id"       : str(row['id_customer']) ,
           "label"    : str(row['label_customer'])
           })

print (list)

with open ('testing.json', 'w') as f:
    json.dump(list, f, indent= True)

==============================

Json输出：

[
 {
  "id": 6,
  "label": "Sao Paulo",
  "Customer": [
   {
    "id": "CUST-99992",
    "label": "Brazil"
   },
   {
    "id": "CUST-88888",
    "label": "China"
   }
  ]
 },
 {
  "id": 92,
  "label": "Hong Hong",
  "Customer": [
   {
    "id": "CUST-99992",
    "label": "Brazil"
   },
   {
    "id": "CUST-88888",
    "label": "China"
   }
  ]
 }
]

==============================

期望值：

[
 {
  "id": 6,
  "label": "Sao Paulo",
  "Customer": [
   {
    "id": "CUST-99992",
    "label": "Brazil"
   }
  ]
 },
 {
  "id": 92,
  "label": "Hong Hong",
  "Customer": [
   {
    "id": "CUST-88888",
    "label": "China"
   }
  ]
 }
]

==============================

有人能帮我吗？你知道吗

Answer 1

在将列表1添加到列表中之前，请尝试附加它

import pandas as pd
import json

file_imported = pd.read_excel('testing.xlsx', sheet_name = 'Plan1')

list1 = []
list  = []
for index, row in file_imported.iterrows():
    list1.append ({
           "id"       : str(row['id_customer']) ,
           "label"    : str(row['label_customer'])
           })
    list.append ({
            "id"       : int(row['id']),
            "label"    : str(row['label']),
            "Customer" : list1
            })



    print (list)

with open ('testing.json', 'w') as f:
     json.dump(list, f, indent= True)

Answer 2

用你的代码运行了一些测试，我认为你的问题是只得到一行值可能是由于你的代码输入的问题。我用你的数据作为.csv输入运行了这个程序，它的输出结果似乎与你端显示的不同。我不确定您使用的是什么版本的pandas，但我建议您使用file_imported运行一些测试，以确认它具有正确的值。我用file_imported = pd.read_csv('import.csv')来做这个。你知道吗

我还发现了一些你可能遇到的其他相关问题。当输入良好时，这就是我得到的输出：

[
 {
  "id": 6,
  "label": "S\u00e3o Paulo",
  "Customer": [
   {
    "id": "CUST-99992",
    "label": "Brazil"
   },
   {
    "id": "CUST-88888",
    "label": "China"
   }
  ]
 },
 {
  "id": 92,
  "label": "Hong Hong",
  "Customer": [
   {
    "id": "CUST-99992",
    "label": "Brazil"
   },
   {
    "id": "CUST-88888",
    "label": "China"
   }
  ]
 }
]

解决方法很简单，只需在"Customer" : list1引用的末尾添加[index]，就像这样：`“Customer”：list1[index]。有了这两个变化，这就是你应该得到的输出！你知道吗

[
 {
  "id": 6,
  "label": "S\u00e3o Paulo",
  "Customer": {
   "id": "CUST-99992",
   "label": "Brazil"
  }
 },
 {
  "id": 92,
  "label": "Hong Hong",
  "Customer": {
   "id": "CUST-88888",
   "label": "China"
  }
 }
]

希望这有帮助！你知道吗