JSON Object to CSV Question

[ryguy7272]

On Thursday, June 18, 2015 at 2:59:11 AM UTC-4, kbtyo 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 valuethe 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?



I am literally just learning Python now...total newbie...don't take this as the be-all-end-all...but maybe this will work for you:
https://pypi.python.org/pypi/xmlutils

Read the entire page.