Needed a simple CSV file that had headers and no nesting of data to consume in Tableau. The JSON file I was consuming had nested dictionaries and lists (which sometimes had in themselves nested lists/dictionaries).
The output is a pipe delimited format (because free text fields have comments in that mess things up) with nested fields having both the original dictionary key plus any lower keys as the headers.
# -*- coding: utf-8 -*-
"""
Created on Fri Sep 14 12:22:02 2018
@author: redim_learning
"""
import sys
import time
class parse_json():
# this class handles parsing json data, once it's been read in as a string using the json library
def __init__(self):
return None
#this writes out dictionaries within the main json dictonary
def write_dict(self, sub_dict, dict_name, f, str_dict):
try:
str_dict = str_dict + ('"%s"' % (str(dict_name))) #commar?!
except:
print ('key not passed correctly', str(dict_name))
try:
for second_key, second_value in sub_dict.items():
if type(second_value) == dict:
print ('dictionary within dictionary')
print( second_key)
write_dict(item[key],second_key,f,str_dict)
str_value = str(second_value)
#clean up characters
if '\n' in second_key or '\r' in str_value:
str_value = (str_value).replace('\n','').replace('\r','').replace(chr(34),chr(39))
str_dict = str_dict + (', "%s"' % str_value)
return str_dict[:len(str_dict)]
except:
print('dict write out did not work\n' , str_dict )
print('item[key] is ', sub_dict)
#print('second key:%s, second value:%s' %(second_key, second_value))
#this function manages to parse a list that is stored within the original json dictionary
def write_list(self, item, key, f, list_str):
# don't write a new line, that will be done later
#write first item in the list in current row
for list_value in item[key]:
if type(list_value) ==str:
list_str = list_str +(list_value.replace('\n','').replace('\r','').replace(chr(34),chr(39)) + ", ")
elif type(list_value) ==dict:
#sys.stdout.write("\nkey: %s, type(item): %s" % (key, type(item)))
#print('\nlist_value is :' + list_value)
sys.stdout.flush()
sys.stdout.flush()
sub_dict = list_value
list_str = list_str + write_dict(sub_dict,dict_name, f, list_str)
return list_str[:len(list_str)-2]
#this is needed to know when to add a line feed to the total string
def find_last_field(self, item):
#loop through all fields and return last header value
for header,value in item.items():
last_key = header
#print (header)
return last_key
#parses headers
def write_headers(self, item,last_header):
header_list = ''
object_list = ''
for h,v in item.items():
if type(v) ==dict:
for sub_header in v.items():
if type(sub_header) == tuple:
object_list = object_list + '"' + h + '_' + sub_header[0]+ '",'
else:
object_list = object_list + '"' + sub_header + '",'
elif type(v) ==list:
for rec in v:
object_list = object_list + '"' + h + "',"
else:
header_list = str(header_list) + '"' + h+ '",'
# return the full header string, but exclude the last commar
return header_list + object_list[:len(object_list)-1]
def parse_json_file(self, data, f, page):
full_str = ''
last_header = ''
for item in data:
try:
sys.stdout.write("\rPage %i, record %s of %i records" %(page+1, str(item['id']), len(data))) #Writes out progress for user
sys.stdout.flush()
except TypeError:
sys.stdout.flush()
sys.stdout.write("\rprogress is progressing ")
sys.stdout.flush()
sys.stdout.flush()
#when you're only looking at one record
if type((item))==str:
item = data
dict_str = ''
list_str = ''
item_str = ''
if last_header == '' and page == 0:
#determine the last header so you know when to write the line return
last_header = find_last_field (item)
#write out a the headers in the first row
f.write(write_headers(item, last_header) + "\n")
for key, value in item.items():
#print (item_str )
#print (key,value, type(value))
#try:
if type(item[key]) == dict:
#print('is dict')
try:
dict_str = dict_str + write_dict(value, key, f, dict_str)
except:
sys.stdout.write("\rdictionary didn't write properly ")
sys.stdout.flush()
elif type(item[key]) == list:
#print('is list')
try:
list_str = list_str + write_list(item, key, f, list_str)
except:
sys.stdout.write("\rlist didn't write properly ")
sys.stdout.flush()
elif type(item[key])==tuple:
item_str = item_str + '"' + value[1] +'",'
elif type(item[key])==int:
item_str = item_str +'"' + str(value) +'",'
elif value == 'True' or value == 'False' or type(value) ==bool:
#print('is bool')
item_str = item_str + '"' + str(value).lower() +'",'
#print (item_str)
elif type(value) == str:
#print('is str')
item_str = item_str +'"' + value.lower().replace('\n','').replace('\r','').replace(chr(34),chr(39)) +'",'
#print (item_str)
elif type(value) == None or value == None:
#print('is str')
item_str = item_str +'"",'
else:
print ('not added %s as is type %s' % (value, type(value)))
full_str = full_str + item_str + dict_str + list_str + "\n"
#print (full_str)
time.sleep(0.5) #Wait so we don't overload instance with too many api requests
#break
return (full_str)
