Unpack dictionary that contains a list of dictionaries and insert in columns

Question

With the data below, I'm trying to unfold a dictionary that contains a list of dictionaries, and then group each key with the corresponding values of the other dictionaries together. For example:

result = {
    'themes' : [{
            'a' : 'orange',
            'b' : 6,
            'c' : 'neutral',
            'd' : 6,
            'e' : 0.24
        }, {
            'a' : 'banana',
            'b' : 6,
            'c' : 'neutral',
            'd' : 6,
            'e' : 0.16
        }, {
            'a' : 'phone',
            'b' : 5,
            'c' : 'neutral',
            'd' : 5,
            'e' : 0.02
        }
    ]
}

...should become something along these lines:

themes={'a' : ['orange','banana', 'phone']}

count={'b' : [6,6,5]}

s_score={'c' : [neutral, neutral, neutral]}

...and so on.

I've looked here, here, and here among other places, but couldn't find something close enough to what I want to do. This came pretty close, but it's checking for at least one or more common values, whereas mine should group common keys. I know I can separate the outer key from the values like this:

>>>(k, v), = result.items()
>>>k
>>>'themes'
>>>v
>>>[{
        'a' : 'orange',
        'b :6,
        'c' : 'neutral',
        'd' : 6,
        'e' : 0.24
    }, {
        'a' : 'banana',
        'b' : 6,
        'c' : 'neutral',
        'd' : 6,
        'e' : 0.16
    }, {
        'a' : 'phone',
        'b' : 5,
        'c' : 'neutral',
        'd' : 5,
        'e' : 0.02
    }
]

but how do I get the v list of dictionaries to the way I described? Do I have to convert them to sets first?

To make my intention clear, my ultimate goal is iterate through the list of values of the keys that I want to keep, so I can enter them into their respective columns in my fairly basic flask-sqlalchemy SQLite database. So in the end I'll be able to query and get them displayed as html:

+-----------------+----------+----------+-------+
|       a         |    b     |    c     |   d   |
+-----------------+----------+----------+-------+
|     orange      |   2.4    | neutral  |   6   |
|     banana      |   1.6    | neutral  |   6   |
+-----------------+----------+----------+-------+

Answer 1

You should first flatMap all your values in the list of tuples ([('a', 'orange'), ('c', 'neutral'), ('b', '6')..]) and then groupBy first element. I would do it this way:

import itertools

pairs =  itertools.chain.from_iterable([d.items() for d in result["themes"]])
result = {}
for key, elem in pairs:
    result.setdefault(key, []).append(elem)
print result 

Answer 2

dict1 = {}
for eachKey in list(set(",".join(each.keys()) for each in result["themes"]))[0].split(","):
    dict1[eachKey] = [each[eachKey] for each in result["themes"]]
print dict1

It will reduce your result to following dictionary-

{'a': ['orange', 'banana', 'phone'], 'c': ['neutral', 'neutral', 'neutral'], 'b': ['6', 6, 5], 'e': [0.24, 0.16, 0.02], 'd': [6, 6, 5]}

Answer 3

Try this using defaultdict

from collections import defaultdict
d = defaultdict(list)
for i,j in result.iteritems():
    for k in j:
        for l,m in k.iteritems():
            d[l].append(m)
>>>d
defaultdict(<type 'list'>, {'a': ['orange', 'banana', 'phone'], 'c': ['neutral', 'neutral', 'neutral'], 'b': ['6', 6, 5], 'e': [0.24, 0.16, 0.02], 'd': [6, 6, 5]})

Now you can parse it by

themes = {'a':d['a']}    
>>>themes
{'a': ['orange', 'banana', 'phone']}

And so on.Hope this helps

Answer 4

You can keep themes, count and score in one dictionary -- final_dict. In code:

>>> lst = result['themes']
>>> final_dict = {}

>>> for d in lst:
...    for (k, v) in d.items():
...        final_dict.setdefault(k, []).append(v)
>>> print final_dict

{'a': ['orange', 'banana', 'phone'], 'c': ['neutral', 'neutral', 'neutral'], 'b': [6, 6, 5], 'e': [0.24, 0.16, 0.02], 'd': [6, 6, 5]}