JSON Object to CSV Question
Saran Ahluwalia
ahlusar.ahluwalia at gmail.com
Wed Jun 17 17:49:35 EDT 2015
Good Evening Everyone:
I would like to have this JSON object written out to a CSV file so that the
keys are header fields (for each of the columns) and the values are values
that are associated with each header field. Is there a best practice for
working with this? Ideally I would like to recursively iterate through the
key value pairs. Thank you in advance. I am using Python 3.4 on Windows. My
editor is Sublime 2.
Here is the JSON object:
{
"Fee": {
"A": "5",
"FEC": "1/1/0001 12:00:00 AM",
"TE": null,
"Locator": null,
"Message": "Transfer Fee",
"AT": null,
"FT": null,
"FR": "True",
"FY": null,
"FR": null,
"FG": "0",
"Comment": null,
"FUD": null,
"cID": null,
"GEO": null,
"ISO": null,
"TRID": null,
"XTY": "931083",
"ANM": null,
"NM": null
},
"CF": "Fee",
"ID": "2"
}
The value, "Fee" associated with the key, "CF" should not be included as a
column header (only as a value of the key "CF").
Other than the former, the keys should be headers and the corresponding
tuples - the field values.
In essence, my goal is to the following:
You get a dictionary object (the "outer" dictionary)
You get the data from the "CF" key (fixed name?) which is a string ("Fee" in
your example)
You use that value as a key to obtain another value from the same "outer"
dictionary, which should be a another dictionary (the "inner" dictionary)
You make a CSV file with:
- a header that contains "CF" plus all keys in the "inner" dictionary
that have an associated value
- the value from key "CF" in the "outer" dictionary plus all non-null
values in the "inner" dictionary.
I have done the following:
import csv
import json
import sys
def hook(obj):
return obj
def flatten(obj):
for k, v in obj:
if isinstance(v, list):
yield from flatten(v)
else:
yield k, v
if __name__ == "__main__":
with open("data.json") as f:
data = json.load(f, object_pairs_hook=hook)
pairs = list(flatten(data))
writer = csv.writer(sys.stdout)
writer.writerow([k for k, v in pairs])
writer.writerow([v for k, v in pairs])
The output is as follows:
$ python3 json2csv.py
A,FEC,TE,Locator,Message,AT,FT,FR,FY,FR,FG,Comment,FUD,
cID,GEO,ISO,TRID,XTY,ANM,NM,CF,ID
5,1/1/0001 12:00:00 AM,,,Transfer Fee,,,True,,,0,,,,,,,931083,,,Fee,2
I do not want to have duplicate column names.
Any advice on other best practices that I may utilize?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20150617/133a4657/attachment.html>
More information about the Python-list
mailing list