python - Pandas 数据框和转换为 Json

Question

基本上，我正在读取一个 pandas 数据框并将其转换为 Json。我是编码的初学者，但我知道最好使用 apply 函数而不是 iterrows (我已经尝试过使用 apply 函数，但是在理解语法和找出我的解决方案时遇到了一些困难)!!

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

我从 excel 读取的数据

id     label        id_customer     label_customer    part_number   number_customer   product   label_product    key    country  value_product

6     Sao Paulo      CUST-99992         Brazil          982               10          sho1564       shoes       SH-99   Chile         1.5        

6     Sao Paulo      CUST-99992         Brazil          982               10          sn47282       sneakers    SN-71   Germany       43.8 

6     Sao Paulo      CUST-43535         Argentina       435               15          sk84393       skirt       SK-11   Netherlands   87.1  

92    Hong Hong      CUST-88888         China           785               58          ca40349       cap         CA-82   Russia        3.95

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

代码:

import pandas as pd 
import json

df = pd.read_excel(path)

result = []
for labels, df1 in df.groupby(['id', 'label'],sort=False):
    id_, label = labels
    record = {'id': int(id_), 'label': label, 'Customer': []}
    for inner_labels, df2 in df1.groupby(['id_customer', 'label_customer'],sort=False):
        id_,label = inner_labels
        record['Customer'].append({
            'id': id_,
            'label': label,
            'Number': [{'part': str(p), 'number_customer': str(s)} for p, s in zip(df2['part_number'], df2['number_customer'])]  
            })

    result.append(record)

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

我得到的 Json:

[
 {
  "id": 6,
  "label": "Sao Paulo",
  "Customer": [
   {
    "id": "CUST-99992",
    "label": "Brazil",
    "Number": [
     {
      "part": "982",
      "number_customer": "10"
     },
     {
      "part": "982",
      "number_customer": "10"
     }
    ]
   },
   {
    "id": "CUST-43535",
    "label": "Argentina",
    "Number": [
     {
      "part": "435",
      "number_customer": "15"
     }
    ]
   }
  ]
 },
 {
  "id": 92,
  "label": "Hong Kong",
  "Customer": [
   {
    "id": "CUST-88888",
    "label": "China",
    "Number": [
     {
      "part": "785",
      "number_customer": "58"
     }
    ]
   }
  ]
 }
]

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

预期的 Json:

[
 {
  "id": 6,
  "label": "Sao Paulo",
  "Customer": [
   {
    "id": "CUST-99992",
    "label": "Brazil",
    "Number": [
     {
      "part": "982",
      "number_customer": "10",
      "Procucts": [
       {
        "product": "sho1564",
        "label_product": "shoes",
        "Order": [
        {
         "key": "SH-99",
         "country": "Chile",    
         "value_product": "1.5"
        }   
       ]            
     },
     {
        "product": "sn47282",
        "label_product": "sneakers",
        "Order": [
        {
         "key": "SN-71",
         "country": "Germany",  
         "value_product": "43.8"
        }   
       ] 
      }
      ]
     }
    ] 
   },
   {
    "id": "CUST-43535",
    "label": "Argentina",
    "Number": [
     {
      "part": "435",
      "number_customer": "15",
      "Procucts": [
       {
        "product": "sk84393",
        "label_product": "skirt",
        "Order": [
        {
         "key": "SK-11",
         "country": "Netherlands",  
         "value_product": "87.1"
        }   
       ]            
      }
      ]
     }
    ]
   }
  ]
 },
 {
  "id": 92,
  "label": "Hong Kong",
  "Customer": [
   {
    "id": "CUST-88888",
    "label": "China",
    "Number": [
     {
      "part": "785",
      "number_customer": "58",
      "Procucts": [
       {
        "product": "ca40349",
        "label_product": "cap",
        "Order": [
        {
         "key": "CA-82",
         "country": "Russia",   
         "value_product": "3.95"
        }   
       ]            
      }
      ]
     }
    ]
   }
  ]
 }
]

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

看 id 和 label 是一组信息，而 id_customer 和 label customer 是另一组信息， part_number 和number_customer 是另一个，product 和label_product 是另一个，key, country 和 value_product 另一个。

我预期的 Json 取决于我的数据框中的信息。

有人可以帮助我吗？

Answer 1

import pandas as pd 
import json

df = pd.read_excel(path)

result = []
for labels, df1 in df.groupby(['id', 'label'], sort=False):
    id_, label = labels
    record = {'id': int(id_), 'label': label, 'Customer': []}
    for inner_labels, df2 in df1.groupby(['id_customer', 'label_customer'], sort=False):
        id_, label = inner_labels
        customer = {'id': id_, 'label': label, 'Number': []}
        for inner_labels, df3 in df2.groupby(['part_number', 'number_customer'], sort=False):
            p, s = inner_labels
            number = {'part': str(p), 'number_customer': str(s), 'Products': []}
            for inner_labels, df4 in df3.groupby(['product', 'label_product'], sort=False):
                p, lp = inner_labels
                product = {'product': p, 'label_product': lp, 'Order': []}
                for k, c, v in zip(df4['key'], df4['country'], df4['value_product']):
                    product['Order'].append({'key': k, 'country': c, 'value_product': v})
                number['Products'].append(product)
            customer['Number'].append(number)
        record['Customer'].append(customer)
    result.append(record)