My default dict has an address key and has a list of dictionaries that match that key. I'd like to export this defaultdict to a csv file.
See below:
Right now my structure looks like this defaultdict(list)
#As you can see 1 key with multiple matching dictionaries.
#And im just copying 1 address but I have ~10 w/ varying matches
defaultdic1 =
defaultdict(list,
{'Address_1': [{'Name': 'name',
'Address_match': 'address_match_1',
'ID': 'id',
'Type': 'abc'},
{'Name': 'name',
'Address_match': 'address_match_2',
'ID': 'id',
'Type': 'abc'},
{'Name': 'name',
'Address_match': 'address_match_3',
'ID': 'id',
'Type': 'abc'}]})
I tried doing this:
json_data = json.dumps(data_json, indent=2)
jsondf = pd.read_json(json_data, typ = 'series')
and my result was this:
Address 1 [{'Name':'name', 'Address_match':'address_match_1' 'ID' : 'id', 'Type':'abc'} {'Name':'name', 'Address_match':'address_match_2' 'ID' : 'id', 'Type':'abc'}, {'Name':'name', 'Address_match':'address_match_3' 'ID' : 'id', 'Type':'abc'}]
Result/output:
Id like to export this to an excel file
Update I tried this. The first row is printing the key but 2nd row is still in a {}, it would be great to get them out of the brackets and shifted into columns. Any tips there?
for k, v in defaultdict.items():
f.writerow([k])
for values in v:
f.writerow([values])
results in CSV are:
Address 1
{'Name':'name', 'Address_match':'address_match_1' 'ID' : 'id', 'Type':'abc'}
{'Name':'name', 'Address_match':'address_match_1' 'ID' : 'id', 'Type':'abc'}
{'Name':'name', 'Address_match':'address_match_2' 'ID' : 'id', 'Type':'abc'}
I'd like my results to be:
Address 1 Name, Address_match1, ID, Type
Name, Address_match2, ID, Type
Name, Address_match3, ID, Type
Address 2 Name1, Address_match1, ID, Type
Name1, Address_match1, ID, Type
Address 3 Name1, Address_match1, ID, Type
Name1, Address_match1, ID, Type
CodePudding user response:
Your input data and output data do not match, so it's awfully difficult to tell how to transform things, but here is something that takes your defaultdict and converts it to a CSV file:
import csv
dic1 = {'Address_2':
[
{'Address 1':
[
{'Name':'name', 'Address_match':'address_match_1', 'ID':'id', 'Type':'abc'}
]
},
{'Address 2':
[
{'Name':'name', 'Address_match':'address_match_2', 'ID':'id', 'Type':'abc'}
]
},
{'Address 3':
[
{'Name':'name', 'Address_match':'address_match_3', 'ID':'id', 'Type':'abc'}
]
}
]
}
names = list(dic1['Address_2'][0]['Address 1'][0].keys())
myfile = csv.DictWriter( open('xxx.csv','w'), fieldnames = names )
for row in dic1['Address_2']:
myfile.writerow({'Name':list(row.keys())[0]})
myfile.writerow(list(row.values())[0][0])
CodePudding user response:
This is what ended up solving it!
names = list(dic1['Address_1'][0].keys())
f.close()
with open ("file.csv", "w", newline="") as f:
writer = csv.writer(f)
keys = names
writer.writerow(["Address"] (keys))
for k, vl in defaultdict.items():
for v in vl:
writer.writerow([k] [v[key] for key in keys])
f.close()