CODEWITHSUNDEEP
pythonexceldictionary
Deonyi
Deonyi

Reputation: 15

Importing and Exporting Nested Dictionary to Excel in an Easy-to-Edit-and-Read Way

I'm trying to export a dictionary, with the format:

d = {'Apple':{'Weight':12,'Colour':'red'},
     'Banana':{'Weight':11,'Colour':'yellow','Bunched':1}        
    }

to an excel file with the format:

NAME    Weight  Colour  Bunched
Apple   12      red
Banana  11      yellow  1

where not every key in d has the same number of keys themselves.

I've managed to get the importing code, but I'm having trouble with the exporting code. I've got this so far:

   import csv
    data = Fruits
    with open('fruits.csv', 'r') as f:
        reader = csv.reader(f)
        for row in reader:
            a = iter(row[1:])
            data[row[0]] = dict(zip(a, a))
    data = Vegetables
    with open('veg.csv', 'r') as f:
        reader = csv.reader(f)
        for row in reader:
            a = iter(row[1:])
            data[row[0]] = dict(zip(a, a))

but it doesn't work. Any help?

Upvotes: 1

Views: 8958

Answers (2)

David
David

Reputation: 624

You can use csv.DictWriter and csv.DictReader like this:

import csv

d = {'Apple':{'Weight': 12,
              'Colour': 'red'},
     'Banana':{'Weight': 11,
               'Colour': 'yellow',
               'Bunched': 1}}

fieldnames = ["name", "Weight", "Colour", "Bunched"]

with open("fruits.csv", "w") as f:
    writer = csv.DictWriter(f, fieldnames=fieldnames)
    for fruit, fruit_info in d.items():
        row = fruit_info
        row.update({"name": fruit})
        writer.writerow(row)

with open('fruits.csv', 'r') as f:
    reader = csv.DictReader(f, fieldnames=fieldnames)
    ret_d = {}
    for row in reader:
        fruit = {}
        for key, value in row.items():
            if not key == "name" and value:
                fruit[key] = value
        ret_d[row["name"]] = fruit
    print(ret_d)

With this solution, you need to flatten the dictionary, because csv.DictWriter writes each row with a dict of the following pattern:

{column1: data, column2: data, column3: data, ...}

And you have to give the fieldnames, because a dict is unordered.

See the docs for more information.

Upvotes: 4

Constantino
Constantino

Reputation: 2391

You can use pandas from_dict() to do this quite easily:

import pandas as pd

d = {'Apple':{'Weight':12,'Colour':'red'},
     'Banana':{'Weight':11,'Colour':'yellow','Bunched':1}
    }

df = pd.DataFrame.from_dict(d, orient='index') # convert dict to dataframe

df.to_csv('fruits.csv') # write dataframe to file

Upvotes: 4

Related Questions