Yesterday I posted a question and I get help from you guys and solved part of my problem.
I'm coding a tool that reads a xlxs file and converts it to JSON. I'm using python 3 and 0.23.0 version of pandas for it. Here is the data that my code is reading from xlxs:
id label id_customer label_customer part_number
6 Sao Paulo CUST-99992 Brazil 7897
6 Sao Paulo CUST-99992 Brazil 1437
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)
==================================
My code is working, and this is the output that I'm getting:
[
{
"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": "1437"
}
]
}
]
},
{
"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": "1437"
}
]
}
]
},
{
"id": 92,
"label": "Hong Hong",
"Customer": [
{
"id": "CUST-88888",
"label": "China",
"number" : [
{
"part": "785"
}
]
}
]
}
]
==================================
I have been searching for other topics here or any useful material, but haven't found yet. This is just a piece of my code and excel file (they are too big to post here). I believe I have to use 'if' command to verify the content inside each row before add it in my json, but idk how to do it.
I can have a lot of 'Customer' and 'number' lists inside 'list_final' with different contents (this is why I created my excel like that)
Could anyone help me?
Aucun commentaire:
Enregistrer un commentaire