CODEWITHSUNDEEP
pythonpython-2.7csvdictionary
waivek
waivek

Reputation: 193

Convert Nested Dictionary to CSV Table

I'm going through a data mining tutorial and I'm using the following dictionary.

users = {
    "Angelica": {
        "Blues Traveler": 3.5, 
        "Broken Bells": 2.0, 
        "Norah Jones": 4.5, 
        "Phoenix": 5.0, 
        "Slightly Stoopid": 1.5, 
        "The Strokes": 2.5, 
        "Vampire Weekend": 2.0
    },         
    "Bill":{
        "Blues Traveler": 2.0, 
        "Broken Bells": 3.5, 
        "Deadmau5": 4.0, 
        "Phoenix": 2.0, 
        "Slightly Stoopid": 3.5, 
        "Vampire Weekend": 3.0
    },
    "Chan": {
        "Blues Traveler": 5.0, 
        "Broken Bells": 1.0, 
        "Deadmau5": 1.0, 
        "Norah Jones": 3.0, 
        "Phoenix": 5, 
        "Slightly Stoopid": 1.0
    },
    "Dan": {
        "Blues Traveler": 3.0, 
        "Broken Bells": 4.0, 
        "Deadmau5": 4.5, 
        "Phoenix": 3.0, 
        "Slightly Stoopid": 4.5, 
        "The Strokes": 4.0, 
        "Vampire Weekend": 2.0
    },
    "Hailey": {
        "Broken Bells": 4.0, 
        "Deadmau5": 1.0, 
        "Norah Jones": 4.0, 
        "The Strokes": 4.0, 
        "Vampire Weekend": 1.0
    },
    "Jordyn":  {
        "Broken Bells": 4.5, 
        "Deadmau5": 4.0, 
        "Norah Jones": 5.0, 
        "Phoenix": 5.0, 
        "Slightly Stoopid": 4.5, 
        "The Strokes": 4.0, 
        "Vampire Weekend": 4.0
    },
    "Sam": {
        "Blues Traveler": 5.0, 
        "Broken Bells": 2.0, 
        "Norah Jones": 3.0, 
        "Phoenix": 5.0, 
        "Slightly Stoopid": 4.0, 
        "The Strokes": 5.0
    },
    "Veronica": {
        "Blues Traveler": 3.0, 
        "Norah Jones": 5.0, 
        "Phoenix": 4.0, 
        "Slightly Stoopid": 2.5, 
        "The Strokes": 3.0
    }
}

I want to convert this into a .csv file so that when I open it in Excel, I get a table with the songs on the rows side and the names on the columns side: Table with the ratings as values

Are there any in-built python methods which will help me achieve this?

Upvotes: 2

Views: 4982

Answers (3)

a guest
a guest

Reputation: 21

import pandas as pd
data = pd.DataFrame(users)
data = data.fillna("-")
data.to_csv("./users.csv")

Upvotes: 1

Martijn Pieters
Martijn Pieters

Reputation: 1121466

You'll have to transpose from columns containing rows to rows containing columns. Using a collections.defaultdict() object would be easiest here:

rows = defaultdict(dict)

for user, artists in users.iteritems():
    for artist, count in artists.iteritems():
        rows[artist][user] = count

Now you have dictionaries that can be written directly to a csv.DictWriter():

with open(csvfilename, 'wb') as outf:
    writer = csv.DictWriter(outf, [''] + users.keys())
    writer.writeheader()
    writer.writerows(dict(row, **{'': key}) for key, row in rows.iteritems())

The generator expression is needed to give each value in the rows dictionary the added first column key-value pair.

Demo:

>>> from collections import defaultdict
>>> import csv
>>> users = { ... }  # elided for brevity
>>> rows = defaultdict(dict)
>>> for user, artists in users.iteritems():
...     for artist, count in artists.iteritems():
...         rows[artist][user] = count
... 
>>> import sys
>>> writer = csv.DictWriter(sys.stdout, [''] + users.keys())
>>> writer.writeheader()
,Angelica,Veronica,Sam,Jordyn,Dan,Bill,Chan,Hailey
>>> writer.writerows(dict(row, **{'': key}) for key, row in rows.iteritems()) 
The Strokes,2.5,3.0,5.0,4.0,4.0,,,4.0
Blues Traveler,3.5,3.0,5.0,,3.0,2.0,5.0,
Phoenix,5.0,4.0,5.0,5.0,3.0,2.0,5,
Broken Bells,2.0,,2.0,4.5,4.0,3.5,1.0,4.0
Deadmau5,,,,4.0,4.5,4.0,1.0,1.0
Norah Jones,4.5,5.0,3.0,5.0,,,3.0,4.0
Slightly Stoopid,1.5,2.5,4.0,4.5,4.5,3.5,1.0,
Vampire Weekend,2.0,,,4.0,2.0,3.0,,1.0

Upvotes: 2

Neel
Neel

Reputation: 21243

Try this

import csv
# Create header line
a = ["Album/Track"] + users.keys()

# Create unique keys.
x = list(set([y for z in users.values() for y in z.keys()]))

# Create rows
rows = [a]+[[q]+[users[p].get(q, '-') for p in a[1:]] for q in x]

with open('my.csv', 'wb') as csvfile:
    writer = csv.writer(csvfile)
    for row in rows:
        writer.write(row)

Upvotes: 2

Related Questions