How to convert Json file to csv-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

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)