CODEWITHSUNDEEP
pythonjsoncsvdictionary
wwl
wwl

Reputation: 2065

dictionary from JSON to CSV

I have a JSON file containing a dictionary with many key-value pairs. I want to write it to a single CSV. One way to do this is simply to iterate through each key:

csvwriter.writerow([f["dict"]["key1"], f["dict"]["key2"],
                    f["dict"]["key3"], ... ])

This would be very tedious.

Another possibility is simply to use

csvwriter.writerow([f["dict"].values()])

but it writes everything into one column of the CSV file, which is not helpful.

Is there a way I can write each value into one column of the CSV file?

Upvotes: 2

Views: 6185

Answers (4)

martineau
martineau

Reputation: 123393

It's not necessary to use csv.DictWriter. The following, which works in both Python 2 and 3, shows how to create a CSV file that will automatically have the key/value pairs in the same order as they appear in the JSON file (instead of requiring a manually defined fieldnames list):

from collections import OrderedDict
import csv
import json
from io import StringIO

# in-memory JSON file for testing
json_file = StringIO(u'{"dict": {"First": "value1", "Second": "value2",'
                                '"Third": "value3", "Fourth": "value4"}}')

# read file and preserve order by using OrderedDict
json_obj = json.load(json_file, object_pairs_hook=OrderedDict)

with open('pairs.csv', 'w') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow(json_obj["dict"].keys())  # header row
    writer.writerow(json_obj["dict"].values())

Contents of pairs.csv file written:

First,Second,Third,Fourth
value1,value2,value3,value4

Upvotes: 0

benten
benten

Reputation: 1991

Pandas is good for this kind of thing.

I would read the JSON file into a pandas dataframe (link). Then write it as a CSV (link). 

import pandas as pd

#read in the json
df = pd.read_json("json_path_here")

# write the csv
df.to_csv("csv_path_here")

Upvotes: 1

Moinuddin Quadri
Moinuddin Quadri

Reputation: 48047

Equivalent to your code: 

csvwriter.writerow(f["dict"].values())

Note: For this, your dictionary should be of collections.OrderedDict because Python's default dictionaries are not ordered. Hence, you'll end up with different order in each row.

Alternatively, better way to achieve this is using DictWriter (for which you don't need ordered dict):

csvwriter.writerow(f["dict"])

Upvotes: 1

mgilson
mgilson

Reputation: 309831

You probably want to use a csv.DictWriter

The example in the official documentation is pretty straight-forward:

import csv

with open('names.csv', 'w') as csvfile:
    fieldnames = ['first_name', 'last_name']
    writer = csv.DictWriter(csvfile, fieldnames=fieldnames)

    writer.writeheader()
    writer.writerow({'first_name': 'Baked', 'last_name': 'Beans'})
    writer.writerow({'first_name': 'Lovely', 'last_name': 'Spam'})
    writer.writerow({'first_name': 'Wonderful', 'last_name': 'Spam'})

Note that you must provide fieldnames to the constructor. If you're certain that all your dict have the same keys and don't care about the order of the output, you can just use list(first_dict) to get the column names, otherwise, you'll want to come up with a way to specify them more explicitly.

Upvotes: 3

Related Questions