On Wednesday, June 17, 2015 at 2:21:05 PM UTC-4, Peter Otten wrote:
> Sahlusar wrote:
>
> > On Wednesday, June 17, 2015 at 11:00:24 AM UTC-4, Saran A wrote:
> >> 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.
> >>
> >> {
> >> "CF": {
> >> "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"
> >> }
> >
> > My apologies:
> >
> > I originally parsed this from an XML file.
> >
> > It really should be:
> >
> > {
> > "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" does should not be included
> > as a column header.
> >
> > The CSV file, when opened with an application such as MS Excel, should be
> > as follows:
> >
> > (Column Header)----> CF A FEC
> >
> > (Field Value)----> Fee 5 1/1/0001 12:00:00 AM
> >
> > My apologies for an confusion.
>
> Forget about Excel, what should the CSV look like when opened in a text
> editor?
>
> Here's my guess:
>
>
> $ cat input.json
> {
> "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"
> }
> $ cat json2csv.py
> 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("input.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])
> $ 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
>
> But do you really want duplicate column names?
@Peter Otten: Thank you for your feedback. No, I do not want to have duplicates. Any thoughts on how to avoid this?