Tuple of tuples to Dict of dicts using Dict Comprehension

Question

I am executing the following sqlite command:

c.execute("SELECT surname,forename,count(*) from census_data group by surname, forename")

so that c.fetchall() is as follows:

(('Griffin','John', 7), 
 ('Griffin','James', 23), 
 ('Griffin','Mary',30), 
 ('Griffith', 'John', 4),
 ('Griffith','Catherine', 5)
)

Is it possible to construct a dict of the following form using a dict comprehension:

{'Griffin': {'John': 7, 'James': 23, 'Mary':30}, 'Griffith': {'John':4,'Catherine':5}}

this is as far as I got:

counts = {s:(f,c) for s,f,c in c.fetchall()}

which overwrites values. Im using python 3.

Answer 1

You can use a collections.defaultdict to create the inner dicts automatically when needed:

from collections import defaultdict

data = (('Griffin','John', 7), 
 ('Griffin','James', 23), 
 ('Griffin','Mary',30), 
 ('Griffith', 'John', 4),
 ('Griffith','Catherine', 5)
)

out = defaultdict(dict)
for (name, first, value) in data:
    out[name][first] = value


# {'Griffin': {'John': 7, 'James': 23, 'Mary': 30}, 'Griffith': {'John': 4, 'Catherine': 5}}

Answer 2

Coming with dict comprehension though with itertools.groupby magic:

from itertools import groupby

counts = {k: dict(_[1:] for _ in g) for k, g in groupby(c.fetchall(), key=lambda t: t[0])}
print(counts)

The output:

{'Griffin': {'John': 7, 'James': 23, 'Mary': 30}, 'Griffith': {'John': 4, 'Catherine': 5}}