CODEWITHSUNDEEP
pythonjsoncsv
No One
No One

Reputation: 23

Converting JSON file to new CSV file using Python Script

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.

Upvotes: 2

Views: 10031

Answers (4)

Trenton McKinney
Trenton McKinney

Reputation: 62383

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:

enter image description here

Saved csv file:

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

Upvotes: 2

awakenedhaki
awakenedhaki

Reputation: 301

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))

Upvotes: 1

PaxPrz
PaxPrz

Reputation: 1928

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 :)

Upvotes: 1

ComplicatedPhenomenon
ComplicatedPhenomenon

Reputation: 4189

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)

Upvotes: 0

Related Questions