Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!eternal-september.org!feeder.eternal-september.org!border1.nntp.ams1.giganews.com!nntp.giganews.com!bcyclone02.am1.xlned.com!bcyclone02.am1.xlned.com!newsfeed.xs4all.nl!newsfeed8.news.xs4all.nl!newsgate.cistron.nl!newsgate.news.xs4all.nl!post.news.xs4all.nl!not-for-mail
To: python-list@python.org
From: Peter Otten <__peter__@web.de>
Subject: Re: JSON Object to CSV Question
Date: Thu, 18 Jun 2015 09:43:12 +0200
Organization: None
References: <CAC9q6M4w5mNj2Lb2MxND_46jbiSwk123mKGKBJckmYCo+_MoSw@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: 7Bit
User-Agent: KNode/4.13.3
Precedence: list
Newsgroups: comp.lang.python
Message-ID: <mailman.583.1434613423.13271.python-list@python.org>
Lines: 106
NNTP-Posting-Host: 2001:888:2000:d::a6
Xref: csiph.com comp.lang.python:92808

Saran Ahluwalia wrote:

> 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:

For some value of "I" :(
> 
> 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.

If you tell the exact output you want and in particular how you want to 
resolve any name clashes I might give you a hint. I'm unlikely to produce 
more code though.

> Any advice on other best practices that I may utilize?

Align your engineering efforts and capabilities to deliver on your strategy 
and, in particular, your three core, I mean your four core ambitions...