Convert json with lists in csv in python

Question

I want to convert a json file into csv (then Excel). A sample of my json file:

{
    "populaires": {
        "perfo_indice": {
            "perfo_indice_annual": {
                "perfo_indice_annual_value": 100.96,
                "perfo_percentage_annual_value": 0.96
            },
            "perfo_indice_monthly": {
                "perfo_percentage_monthly_value": 0.96
            }
        },
        "modeles": [
            {
                "marque": "Austin",
                "modele": "Mini",
                "cote": {
                    "cote_2020": 13553,
                },
                "perfo_modeles": {
                    "perfo_modeles_annual": {
                        "perfo_modeles_indice_annual_value": 101.87,
                        "perfo_modeles_percentage_annual_value": 1.87
                    },
                    "perfo_modeles_monthly": {
                        "perfo_modeles_percentage_monthly_value": 1.87
                    }
                }
            },
            {
                "marque": "Citroën",
                "modele": "2CV",
                "cote": {
                    "cote_2020": 11157,
                },
                "perfo_modeles": {
                    "perfo_modeles_annual": {
                        "perfo_modeles_indice_annual_value": 101.92,
                        "perfo_modeles_percentage_annual_value": 1.92
                    },
                    "perfo_modeles_monthly": {
                        "perfo_modeles_percentage_monthly_value": 1.92
                    }
                }
            }]
    }]

Id' like to have an output with just the keys with values, for example: populaires, perfo_indice_annual_value, perfo_percentage_annual_value, perfo_percentage_monthly_value, marque, etc.

I have 300 modele.

I tried with a:

with open('./json/indice_na_2021-01-20.json', encoding='utf-8') as data_model:
data = json.loads(data_model.read())

pt_data1 = open('pt_data1.csv', 'w')
csvwriter = csv.writer(pt_data1)
count = 0

for pt in data:
    if count == 0:
            header = pt.keys()
            csvwriter.writerow(header)
            count += 1
    csvwriter.writerow(pt.values())
pt_data1.close()

But the .csv just displayed the list.

How can I have just a csv file by "modele" with all the keys with value ?

Martin Evans · Accepted Answer · 2021-01-21 14:32:13Z

I suggest you extract the keys you need one at a time and construct each row at a time. For example:

import json
import csv


with open('indice_na_2021-01-20.json', encoding='utf-8') as data_model:
    data = json.load(data_model)

with open('pt_data1.csv', 'w', newline='', encoding='utf-8') as pt_data1:
    csvwriter = csv.writer(pt_data1)
    csvwriter.writerow(["marque", "modele", "perfo_modeles_indice_annual_value", "perfo_modeles_percentage_annual_value"])
    
    for model in data["populaires"]["modeles"]:
        marque = model["marque"]
        modele = model["modele"]
        perfo_modeles_indice_annual_value = model["perfo_modeles"]["perfo_modeles_annual"]["perfo_modeles_indice_annual_value"]
        perfo_modeles_percentage_annual_value = model["perfo_modeles"]["perfo_modeles_annual"]["perfo_modeles_percentage_annual_value"]
        
        csvwriter.writerow([marque, modele, perfo_modeles_indice_annual_value, perfo_modeles_percentage_annual_value])

This would give you the following for the data you have provided:

marque,modele,perfo_modeles_indice_annual_value,perfo_modeles_percentage_annual_value
Austin,Mini,101.87,1.87
Citroën,2CV,101.92,1.92

Collectives™ on Stack Overflow

Convert json with lists in csv in python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related