JSON Object to CSV file
Locked
 
|
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" } |
|
|
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. |
|
|
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? |
|
|
In reply to this post by Saran Ahluwalia
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? |
|
|
In reply to this post by Saran Ahluwalia
On Wed, 17 Jun 2015 08:00:11 -0700, 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. > { > "CF": { ....... > "CF": "Fee", Your json object seems to have the same key used for two elements at the same level, are you sure this is legal json? -- Denis McMahon, denismfmcmahon at gmail.com |
JSON Object to CSV file
|
|
On Sunday, 21 June 2015 02:47:31 UTC-4, Denis McMahon wrote:
> On Wed, 17 Jun 2015 08:00:11 -0700, 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. > > > { > > "CF": { > ....... > > "CF": "Fee", > > Your json object seems to have the same key used for two elements at the > same level, are you sure this is legal json? > > -- > Denis McMahon, denismfmcmahon at gmail.com I converted this from an XML file given to me from a third party. It is as is. I am not sure what you mean by "valid"; that is a very subjective measure for any form of quantitative or qualitative data. |
|
|
In reply to this post by Denis McMahon
On Sunday, June 21, 2015 at 2:47:31 AM UTC-4, Denis McMahon wrote:
> On Wed, 17 Jun 2015 08:00:11 -0700, 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. > > > { > > "CF": { > ....... > > "CF": "Fee", > > Your json object seems to have the same key used for two elements at the > same level, are you sure this is legal json? > > -- > Denis McMahon, denismfmcmahon at gmail.com I converted this from an XML file given to me from a third party. It is as is. I am not sure what you mean by "valid"; that is a very subjective measure for any form of quantitative or qualitative data. |
|
|
In reply to this post by Denis McMahon
On Sunday, June 21, 2015 at 2:47:31 AM UTC-4, Denis McMahon wrote:
> On Wed, 17 Jun 2015 08:00:11 -0700, 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. > > > { > > "CF": { > ....... > > "CF": "Fee", > > Your json object seems to have the same key used for two elements at the > same level, are you sure this is legal json? > > -- > Denis McMahon, denismfmcmahon at gmail.com This is a duplicate to the post titled: JSON to CSV Troubleshooting: It is difficult to explain this to someone asynchronously and without an in person meeting. Moreover, the strict guidelines for disclosing information make it difficult for me to explain the client's requirements and the problems that they face. I do agree with you Denis that this is an unconventional approach. I was wondering then that perhaps I should add additional functionality at the XML to JSON step? So far, with JSON objects without nested lists (as values) I have been successful with this (the following is rather lengthy): import xml.etree.cElementTree as ElementTree from xml.etree.ElementTree import XMLParser import json import csv import tokenize import token try: from collections import OrderedDict import json except ImportError: from ordereddict import OrderedDict import simplejson as json import itertools import six import string from csvkit import CSVKitWriter class XmlListConfig(list): def __init__(self, aList): for element in aList: if element: # treat like dict if len(element) == 1 or element[0].tag != element[1].tag: self.append(XmlDictConfig(element)) # treat like list elif element[0].tag == element[1].tag: self.append(XmlListConfig(element)) elif element.text: text = element.text.strip() if text: self.append(text) class XmlDictConfig(dict): ''' Example usage: >>> tree = ElementTree.parse('your_file.xml') >>> root = tree.getroot() >>> xmldict = XmlDictConfig(root) Or, if you want to use an XML string: >>> root = ElementTree.XML(xml_string) >>> xmldict = XmlDictConfig(root) And then use xmldict for what it is..a dictionary. ''' def __init__(self, parent_element): if parent_element.items(): self.update(dict(parent_element.items())) for element in parent_element: if element: # treat like dict - we assume that if the first two tags # in a series are different, then they are all different. if len(element) == 1 or element[0].tag != element[1].tag: aDict = XmlDictConfig(element) # treat like list - we assume that if the first two tags # in a series are the same, then the rest are the same. else: # here, we put the list in dictionary; the key is the # tag name the list elements all share in common, and # the value is the list itself aDict = {element[0].tag: XmlListConfig(element)} # if the tag has attributes, add those to the dict if element.items(): aDict.update(dict(element.items())) self.update({element.tag: aDict}) # this assumes that if you've got an attribute in a tag, # you won't be having any text. This may or may not be a # good idea -- time will tell. It works for the way we are # currently doing XML configuration files... elif element.items(): self.update({element.tag: dict(element.items())}) # finally, if there are no child tags and no attributes, extract # the text else: self.update({element.tag: element.text}) def main(): #Lines 88-89stantiate the class Elementree #and applies the method to recursively traverse from the root node #XmlDictConfig is instantiated in line 90 with open('C:\\Users\\somefile.xml', 'r', encoding='utf-8') as f: xml_string = f.read() xml_string= xml_string.replace('�', '') #optional to remove ampersands. root = ElementTree.XML(xml_string) xmldict = XmlDictConfig(root) json_str = json.dumps(xmldict, sort_keys=True, indent=4, separators=(',', ': ')) newly_formatted_data = json.loads(json_str) #encode into JSON with open('data2.json', 'w') as f: #writing JSON file json.dump(newly_formatted_data, f) Peter Otten was very helpful with subsequently converting aJ SON string to a CSV file: 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("somefileneame.json") as f: data = json.load(f, object_pairs_hook=hook) pairs = list(flatten(data)) writer = csv.writer(sys.stdout) header = writer.writerow([k for k, v in pairs]) row = writer.writerow([v for k, v in pairs]) #writer.writerows for any other iterable object However, for the nested keys as dictionaries with values as dictionaries, I have been taking smaller chunks of the JSON objects and developing some functional programs that allow me to blow up lists: For example for this example JSON snippet, to de-nest by level the nested dictionaries, I use this function: def flatten(d, parent_key=''): items = [] for k, v in d.items(): try: items.extend(flatten(v, '%s%s_' % (parent_key, k)).items()) except AttributeError: items.append(('%s%s' % (parent_key, k), v)) return dict(items) final = (flatten(data2, parent_key ='')) ##JSON sample: data2 = { "OTF": "0", "F": "False", "F": { "Int32": ["0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0"] }, "D": { "B": ["0", "0", "0", "0", "0", "0", "0", "0", "0", "0", "0"] }, "PBDS": { "DateTime": ["1/1/0001 12:00:00 AM", "1/1/0001 12:00:00 AM", "1/1/0001 12:00:00 AM", "1/1/0001 12:00:00 AM", "1/1/0001 12:00:00 AM", "1/1/0001 12:00:00 AM", "1/1/0001 12:00:00 AM", "1/1/0001 12:00:00 AM"] }, "PBDS": { "Double": ["0", "0", "0", "0", "0", "0", "0", "0"] }, "SCS": { "String": ["1", "2"] } } The result: {'D_B': ['0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0'], 'F_Int32': ['0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0'], 'OTF': '0', 'PBDS_Double': ['0', '0', '0', '0', '0', '0', '0', '0'], 'SCS_String': ['1', '2']} I then have used a decorator to explode the lists (thank you to Gribouillis for feedback): from postprocess import post_process @post_process(dict) def explode_lists(adict): for key, value in adict.items(): if isinstance(value, list): if all(isinstance(x, str) for x in value): for i, x in enumerate(value, 1): yield ('{}{}'.format(key, i), x) continue yield key, value # postprocess.py def post_process(*filters): """Decorator to post process a function's return value through a sequence of filters (functions with a single argument). Example: @post_process(f1, f2, f3) def f(*args, **kwd): ... return value then calling f(...) will actually return f3( f2( f1( f(...)))). This can also be used to convert a generator to a function returning a sequence type: @post_process(dict) def my_generator(): ... yield key, value """ def decorate(func): from functools import wraps @wraps(func) def wrapper(*args, **kwd): rv = func(*args, **kwd) for f in filters: rv = f(rv) return rv return wrapper return decorate I know that this is alot of sequential steps. I am wondering if I could insert or conditionally pass these functions when originally parsing the XML, so that the JSON is formatted for more recursive reading of the JSON dictionary and then writing to CSV? I welcome constructive feedback for refactoring.... |
|
|
On Sunday, June 21, 2015 at 10:54:44 AM UTC-4, Sahlusar wrote:
> This is a duplicate to the post titled: JSON to CSV Troubleshooting: Please don't do that. --Ned. |
|
|
On Sunday, 21 June 2015 11:31:44 UTC-4, Ned Batchelder wrote:
> On Sunday, June 21, 2015 at 10:54:44 AM UTC-4, Sahlusar wrote: > > > This is a duplicate to the post titled: JSON to CSV Troubleshooting: > > Please don't do that. > > --Ned. My apologies - is it possible to delete one of my posts? |
|
|
In reply to this post by sahluwalia@wynyardgroup.com
On Sun, 21 Jun 2015 06:57:01 -0700, sahluwalia wrote:
> On Sunday, 21 June 2015 02:47:31 UTC-4, Denis McMahon wrote: >> On Wed, 17 Jun 2015 08:00:11 -0700, 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. >> >> > { >> > "CF": { >> ....... >> > "CF": "Fee", >> >> Your json object seems to have the same key used for two elements at >> the same level, are you sure this is legal json? > I converted this from an XML file given to me from a third party. It is > as is. I am not sure what you mean by "valid"; that is a very subjective > measure for any form of quantitative or qualitative data. Put it this way, when I feed your json object into a jason lint, the output is the following: { "CF": "Fee", "ID": "2" } The second occurrence in the parent object of name "CF" with a value of string literal "Fee" overwrites the earlier name "CF" whose value is (in python terms) a dictionary or (in json terms) an object. -- Denis McMahon, denismfmcmahon at gmail.com |
|
|
On Sunday, June 21, 2015 at 4:54:27 PM UTC-4, Denis McMahon wrote:
> On Sun, 21 Jun 2015 06:57:01 -0700, sahluwalia wrote: > > > On Sunday, 21 June 2015 02:47:31 UTC-4, Denis McMahon wrote: > >> On Wed, 17 Jun 2015 08:00:11 -0700, 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. > >> > >> > { > >> > "CF": { > >> ....... > >> > "CF": "Fee", > >> > >> Your json object seems to have the same key used for two elements at > >> the same level, are you sure this is legal json? > > > I converted this from an XML file given to me from a third party. It is > > as is. I am not sure what you mean by "valid"; that is a very subjective > > measure for any form of quantitative or qualitative data. > > Put it this way, when I feed your json object into a jason lint, the > output is the following: > > { > "CF": "Fee", > "ID": "2" > } > > The second occurrence in the parent object of name "CF" with a value of > string literal "Fee" overwrites the earlier name "CF" whose value is (in > python terms) a dictionary or (in json terms) an object. > > -- > Denis McMahon, denismfmcmahon at gmail.com I am stepping back and rethinking the notion of JSON/dictionaries. Maybe this was a very overcomplicated approach for this type of data. |
«
Return to Python - python-list
|
1 view|%1 views
| Free forum by Nabble | Edit this page |