Home > Software design >  Best way to convert a defaultdict(list) dictionary with list of dictionaries to a csv
Best way to convert a defaultdict(list) dictionary with list of dictionaries to a csv

Time:12-20

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()
  • Related