How to convert JSON file to CSV

Question

I have the following JSON file:

[
    {
      "Names": {
         "0": "Nat",
         "1": "Harry",
         "2": "Joe"
      },
      "Marks": {
         "0": 78.22,
         "1": 32.54,
         "2": 87.23
      }
   }
]

I have written the following code for conversion:

import csv, json

def conversion(Jsonfile,Csvfile):
    readfile=open(Jsonfile,"r")
    print(readfile)
    jsondata=json.load(readfile)
    print(jsondata)
    readfile.close()
    
    data_file=open(Csvfile,'w')
    csv_writer=csv.writer(data_file)
    
    count=0
    for data in jsondata:
        if count==0:
            header=data.keys()
            print(header)
            csv_writer.writerow(header)
            count=count+1
        
        csv_writer.writerow(data.values())
        print(data.values())
    data_file.close()

Jsonfile="Series.json"
Csvfile="convertedfile.csv"

conversion(Jsonfile,Csvfile)

I am getting the following output in CSV

Names,Marks

"{'0': 'Nat', '1': 'Harry', '2': 'Joe'}","{'0': 78.22, '1': 32.54, '2': 87.23}"

My question is how to correct the code to get the following output ( that is each name with marks in a different line):

Names,Marks
0,Nat,78.22
1,Harry,32.54
2,Joe,87.23

First google result https://datatofish.com/json-string-to-csv-python/ — po.pe, May 25 '22 at 06:20
There are answers here https://stackoverflow.com/questions/1871524/how-can-i-convert-json-to-csv Hope helpful. — ild flue, May 25 '22 at 06:21
This is essentially the same question that you asked on May 21st — DarkKnight, May 25 '22 at 07:09
Do you realise that the required output is **not** valid CSV because you have 2 headers followed by 3 rows each containing 3 columns? — DarkKnight, May 25 '22 at 07:24

score 1 · Answer 1 · answered May 25 '22 at 06:42

pandas has the utility for both, reading json and writing csv.

import pandas as pd


 j = '[{"Names":{"0":"Nat","1":"Harry","2":"Joe"},"Marks":{"0":78.22,"1":32.54,"2":87.23}}]'
df = pd.read_json(j[1:-1], orient='records')  # 1:-1 because we need to remove the square brackets
df.to_csv("output.csv")

Output:

,Names,Marks
0,Nat,78.22
1,Harry,32.54
2,Joe,87.23

score 0 · Answer 2 · answered May 25 '22 at 06:44

Here's a way of doing it.

First, we open the json file and parse it:

import json

json_file = "foo.json"
csv_file = "foo.csv"

with open(json_file, "r") as f:
    json_data = json.load(f)

We then extract the dict from the json list:

my_dict = json_data[0]  # we get the dict inside the list
my_list = []  # we create a new list to store data in the format we need

We format the data in a way we can easily print it to the file:

for key in my_dict["Names"].keys():
    my_list.append(
        [
            key,
            my_dict["Names"][key],
            str(my_dict["Marks"][key])  # we cast the mark to string so it's easier to use later on
        ]
    )

And now, the data is formatted in a way that's easy to print for us, so we just save it as such in our csv file!

# Our new list is now created, we can now save it to our file
with open(csv_file, "w") as f:
    f.write("Names,Marks\n")
    for line in my_list:
        f.write(",".join(line) + "\n")

Output:

Names,Marks
0,Nat,78.22
1,Harry,32.54
2,Joe,87.23

That output is not valid CSV. There are 3 values per row and only 2 columns — DarkKnight, May 25 '22 at 07:34
@LancelotduLac I agree. However, it is the output the author asked. — ImranD, May 25 '22 at 08:37

score 0 · Answer 3 · edited May 25 '22 at 08:04

0

import pandas as pd

df = pd.read_json(r'Path where the JSON file is saved\File Name.json')
df.to_csv(r'Path where the new CSV file will be stored\New File Name.csv', index = None)

edited May 25 '22 at 08:04

RiveN

2,595
11
13
26

answered May 25 '22 at 07:17

Safeer Ahmad Zaid

1
2

3

Kindly add enough details so that future readers can understand your answer. – Abhyuday Vaish May 25 '22 at 11:01

score 0 · Answer 4 · answered May 25 '22 at 07:38

You can generate a valid CSV file from the JSON data without heavyweight modules such as pandas or csv. You only need the json module to load the JSON data from the file and (implicitly) validate it.

import json

Jsonfile = 'Series.json'
Csvfile = 'convertedfile.csv'

with open(Jsonfile) as jfile:
    d = json.load(jfile)[0]

    with open(Csvfile, 'w') as cfile:
        print('ID,Name,Marks', file=cfile)
        if names := d.get('Names'):
            for id_, name in names.items():
                mark = d.get('Marks', {}).get(id_, '')
                print(f'{id_},{name},{mark}', file=cfile)

The output file will look like this:

ID,Name,Marks
0,Nat,78.22
1,Harry,32.54
2,Joe,87.23

How to convert JSON file to CSV

4 Answers4