Converting JSON file to new CSV file using Python Script

Question

I'm trying to convert JSON data into CSV format so I can import it into LibreCalc. I've found a pretty simple Python script that almost does what I'm looking for, but it doesn't quite work properly.

This is the script I've made, taken from snippets of code in the guide link. EDIT: I can't add the link as stack overflow thinks its spam.

import json
import csv

#read file
with open('test.json', 'r') as jsonfile:
  data=jsonfile.read()

# parse file
jsonobj = json.loads(data)

def jsontocsv(input_json, output_path):
  keylist = []
  for key in jsonobj[0]:
    keylist.append(key)
    f = csv.writer(open(output_path, "w"))
    f.writerow(keylist)

  for record in jsonobj:
    currentrecord = []
    for key in keylist:
      currentrecord.append(record[key])
      f.writerow(currentrecord)



jsontocsv(jsonobj,'test.csv')

And this is the test JSON data, also taken from the links example.

[{
"Name" : "Bob",
"City" : "San Diego",
"State" : "CA"
},
{
"Name" : "Sue",
"City" : "San Francisco",
"State" : "CA"
},
{
"Name" : "Ted",
"City" : "New York",
"State" : "NY"
},
{
"Name" : "Joe",
"City" : "Miami",
"State" : "FL"
}]

When I run the script, instead of getting a CSV file that looks like this:

Name,City,State
Bob,San Diego,CA
Sue,San Francisco,CA
Ted,New York,NY
Joe,Miami,FL

I get this...

City,State,Name
San Diego
San Diego,CA
San Diego,CA,Bob
San Francisco
San Francisco,CA
San Francisco,CA,Sue
New York
New York,NY
New York,NY,Ted
Miami
Miami,FL
Miami,FL,Joe

I'm literally copying this guys code and following his example, so I'm not sure what I'm doing wrong. I've tried playing with it a bit and changing the indenting etc, but nothing seems to work. I'm sure it's something ridiculously simple, and no doubt something I've messed up, but I can't figure it out.

Using my own JSON files with the actual data I want to convert has the same effect.

Any help with this would be appreciated.

Thank you.

Trenton McKinney · Accepted Answer · 2019-09-05 22:51:07Z

2

`pandas` is a quick and easy option:

Given the data shown, in a file name test.json
Load it into a pandas.DataFrame
Save it to a csv format with df.to_csv

import pandas as pd

df = pd.read_json('test.json')

df.to_csv('test.csv', index=False)

DataFrame:

Saved `csv` file:

Name,City,State
Bob,San Diego,CA
Sue,San Francisco,CA
Ted,New York,NY
Joe,Miami,FL

edited Sep 5, 2019 at 22:51

answered Sep 1, 2019 at 3:02

Trenton McKinney

63.2k41 gold badges169 silver badges212 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

PaxPrz · Accepted Answer · 2019-09-01 03:01:16Z

1

First Let me note down your bug:

You have kept f.writerow(keylist) inside the loop. Basically, it is causing that for every key element that you append on keylist, it writes it down to the file.

SOLUTION: Simply put them outside the loop:

def jsontocsv(input_json, output_path):
  keylist = []
  for key in jsonobj[0]:
    keylist.append(key)
  f = csv.writer(open(output_path, "w"))
  f.writerow(keylist)

  for record in jsonobj:
    currentrecord = []
    for key in keylist:
      currentrecord.append(record[key])
    f.writerow(currentrecord)

You should be good to go :)

answered Sep 1, 2019 at 3:01

PaxPrz

1,9681 gold badge21 silver badges35 bronze badges

Comments

awakenedhaki · Accepted Answer · 2019-09-02 02:04:43Z

import json
import csv

from operator import itemgetter

# Load json file
with open(filepath, 'r') as f:
    data = json.load(f)

# Get keys of a dictionary within the loaded json data
# . The keys represent the headers of the CSV
headers = data[0].keys()
# Creater an itemgetter.
# . This will facilitate getting the values of our keys (aka headers)
values = itemgetter(*headers)

# Open file to write
with open(filepath, 'w') as f:
    # Create csv writer object
    writer = csv.writer(f)

    # Write the header row
    writer.writerow(headers)

    for row in data:
        # values(row) will output the values of the dictionary
        # If you are in Python >3.7 you don't really need values()
        # . since the dictionaries are ordered.
        writer.writerow(values(row))

ComplicatedPhenomenon · Accepted Answer · 2019-09-01 03:01:02Z

0

import json
import csv

#read file
with open('test.json', 'r') as jsonfile:
    data = jsonfile.read()

# parse file
jsonobj = json.loads(data)

with open('test.csv', 'w') as f:
    wr = csv.writer(f, delimiter=',')

    # Extract the header
    headers = list(jsonobj[0].keys())
    wr.writerow(headers)

    # Extract the value from list of dict 
    m = [[v for _, v in i.items()] for i in jsonobj]
    for k in m:
        wr.writerow(k)

answered Sep 1, 2019 at 3:01

ComplicatedPhenomenon

4,2113 gold badges26 silver badges52 bronze badges

Collectives™ on Stack Overflow

Converting JSON file to new CSV file using Python Script

4 Answers 4

`pandas` is a quick and easy option:

DataFrame:

Saved `csv` file:

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

pandas is a quick and easy option:

DataFrame:

Saved csv file:

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related

`pandas` is a quick and easy option:

Saved `csv` file: