jeudi 5 décembre 2019

Pandas dataframe and appending objects in conversion to JSON

Basically I´m reading a xlsx file using pandas and converting it to json file. I idk how to do it, but I think I have to create a 'if' statement to read each row and find which elements are different regarding to the previous line and then append it in my object.

Data that I´m reading :

id     label        id_customer     label_customer    part_number 

6     Sao Paulo      CUST-99992         Brazil          7897

6     Sao Paulo      CUST-99992         Brazil          982

6     Sao Paulo      CUST-43535         Brazil          435

92    Hong Hong      CUST-88888         China           785

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

Here is my code:

import pandas as pd
import json

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

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

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

    list2.append({
        "part"    : str(row['part_number'])  
       })      

print (list_final)

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

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

Json output:

    [
     {
      "id": 6,
      "label": "Sao Paulo",
      "Customer": [
       {
        "id": "CUST-99992",
        "label": "Brazil",
        "number" : [
        {
        "part": "7897"
        }
        ]
       }
      ]
     },
     {
      "id": 6,
      "label": "Sao Paulo",
      "Customer": [
       {
        "id": "CUST-99992",
        "label": "Brazil",
        "number" : [
        {
        "part": "982"
        }
        ]
       }
      ]
     },
     {
      "id": 6,
      "label": "Sao Paulo",
      "Customer": [
       {
        "id": "CUST-43535",
        "label": "Brazil",
        "number" : [
        {
        "part": "435"
        }
        ]
       }
      ]
     },
     {
      "id": 92,
      "label": "Hong Hong",
      "Customer": [
       {
        "id": "CUST-88888",
        "label": "China",
        "number" : [
        {
        "part": "785"
        }
        ]
       }
      ]
     }
    ]  

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

and I need something like this:

[
 {
  "id": 6,
  "label": "Sao Paulo",
  "Customer": [
   {
    "id": "CUST-99992",
    "label": "Brazil",
    "number" : [
    {
    "part": "7897"
    },
    {
    "part": "982"
    }
    ]
   },
   {
    "id": "CUST-43535",
    "label": "Brazil",
    "number" : [
    {
    "part": "435"
    }
    ]
   }
  ]
 },
 {
  "id": 92,
  "label": "Hong Hong",
  "Customer": [
   {
    "id": "CUST-88888",
    "label": "China",
    "number" : [
    {
    "part": "785"
    }
    ]
   }
  ]
 }
]

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

Can somebody help me?????

Aucun commentaire:

Enregistrer un commentaire