I received a JSON file to transform into a CSV file.
[
{
"id": "132465",
"ext_numero_commande": "4500291738L",
"ext_line_items": [
{
"key_0": "10",
"key_1": "10021405 / 531.415.110",
"key_4": "4 Pce"
},
{
"key_0": "20",
"key_1": "10021258 / 531.370.140 / NPK-Nr. 224412",
"key_4": "4 Pce"
},
{
"key_0": "30",
"key_1": "10020895 / 531.219.120 / NPK-Nr. 222111",
"key_4": "10 Pce"
},
{
"key_0": "40",
"key_1": "10028633 / 552.470.110",
"key_4": "3 Pce"
}
],
"ext_prix": [
{
"key_0": "11.17"
},
{
"key_0": "9.01"
},
{
"key_0": "18.63"
},
{
"key_0": "24.15"
}
],
"ext_tag": "Date_livraison",
"ext_jour_livraison": "23-07-2021",
"ext_jour_livraison_1": null
}
]
id | Ext_Numero_Commande | Ext_line items1 | Ext_line items 4 | Ext_Prix | Ext_Tag | Ext_Jour_Livraison | Ext_Jour_Livraison 1 |
---|---|---|---|---|---|---|---|
132465 | 4500291738L | 10 | 10021405 / 531.415.110 | 4 Pce | 11.17 | Date_livraison | 23-07-2021 |
132465 | 4500291738L | 20 | 10021258 / 531.370.140 / NPK-Nr. 224412 | 4 Pce | 9.01 | Date_livraison | 23-07-2021 |
132465 | 4500291738L | 30 | 10020895 / 531.219.120 / NPK-Nr. 222111 | 10 Pce | 18.63 | Date_livraison | 23-07-2021 |
132465 | 4500291738L | 40 | 10028633 / 552.470.110 | 3 Pce | 24.15 | Date_livraison | 23-07-2021 |
I found the function pd.json_normalize
.
df=pd.json_normalize(
json_object[0],
record_path=['ext_line_items'],
meta=['id', 'ext_numero_commande', 'ext_tag',
'ext_jour_livraison', 'ext_jour_livraison_1'])
I have nearly my end result, and I can add the last column ["ext_prix"]
with the same method and a concatenation function.
Is a function which does it automatically?
I used this function, but it returns an error.
df=pd.json_normalize(
json_object[0],
record_path=['ext_line_items','ext_prix'],
meta=['id', 'ext_numero_commande', 'ext_tag',
'ext_jour_livraison', 'ext_jour_livraison_1'])
CodePudding user response:
You can solve this problem if your JSON dataset fixed length, I hope this code will work properly. Follow this way..(JSON to Dictionary to CSV using pandas).
# import pandas
import pandas as pd
# Read JSON file
df = pd.read_json('C://Users//jahir//Desktop//json_file.json')
# create dictionary
create_dict = {}
# Iteration JSON file
for index,values in df.iterrows():
# for loop ext_line_items
for ext_lines in df['ext_line_items'][index]:
for item in ext_lines:
column_name = 'ext_line_items_' item
if column_name not in create_dict:
create_dict[column_name] =[]
# four time for to create four product ext_line_items_key_0, ext_line_items_key_1, ext_line_items_key_4
time_loop = 4
while time_loop > 0:
time_loop-=1
create_dict[column_name] =[ext_lines[item]]
# for loop ext_prix dataset
# four time for to create four product ext_prix_key_0
time_loop = 4
while time_loop > 0:
time_loop-=1
for ext_prix in df['ext_prix'][index]:
for item in ext_prix:
column_name = 'ext_prix_' item
if column_name not in create_dict:
create_dict[column_name] =[]
create_dict[column_name] =[ext_prix[item]]
# add key in dictionary
for i in ['id', 'ext_numero_commande', 'ext_tag', 'ext_jour_livraison']:
create_dict[i]=[]
# 16 time for to create 4*4 product 'id', 'ext_numero_commande', 'ext_tag', 'ext_jour_livraison'
total_time = 16
while total_time > 0:
total_time-=1
for j in ['id', 'ext_numero_commande', 'ext_tag', 'ext_jour_livraison']:
create_dict[j] = [df[j][index]]
# Dictionary to DataFrame
pd_dict= pd.DataFrame.from_dict(create_dict)
# DataFrame to write csv file
write_csv_path = 'C://Users//jahir//Desktop//csv_file.csv'
pd_dict.to_csv(write_csv_path, index = False, header = True)
Output:
CodePudding user response:
Using pd.json_normalize you can solve this problem. Follow this way..(json_normalize to merge to csv)
import pandas as pd
data = [
{
"id": "132465",
"ext_numero_commande": "4500291738L",
"ext_line_items": [
{
"key_0": "10",
"key_1": "10021405 / 531.415.110",
"key_2": "4 Pce"
},
{
"key_0": "20",
"key_1": "10021258 / 531.370.140 / NPK-Nr. 224412",
"key_2": "4 Pce"
},
{
"key_0": "30",
"key_1": "10020895 / 531.219.120 / NPK-Nr. 222111",
"key_2": "10 Pce"
},
{
"key_0": "40",
"key_1": "10028633 / 552.470.110",
"key_2": "3 Pce"
}
],
"ext_prix": [
{
"key_4": "11.17"
},
{
"key_4": "9.01"
},
{
"key_4": "18.63"
},
{
"key_4": "24.15"
}
],
"ext_tag": "Date_livraison",
"ext_jour_livraison": "23-07-2021",
"ext_jour_livraison_1": None
}
]
# Used json_normalize for record path ext_line_items
normalize_ext_line_items = pd.json_normalize(data, record_path = "ext_line_items", meta = ["id", "ext_numero_commande", 'ext_tag', 'ext_jour_livraison'])
# Used json_normalize for record path ext_prix
normalize_ext_prix = pd.json_normalize(data,record_path = "ext_prix", meta = ["id"])
# merge to
final_output = normalize_ext_line_items.merge(normalize_ext_prix, on='id', how = 'left')
# DataFrame to write csv file used your file path
write_csv_path = 'C://Users//jahir//Desktop//csv_file.csv'
final_output.to_csv(write_csv_path, index = False, header = True)
Output: