Group by multiple keys and summarize/average values of a list of dictionaries

What is the most pythonic way to group by multiple keys and summarize/average values of a list of dictionaries in Python please? Say I have a list of dictionaries as below:

input = [
{'dept': '001', 'sku': 'foo', 'transId': 'uniqueId1', 'qty': 100},
{'dept': '001', 'sku': 'bar', 'transId': 'uniqueId2', 'qty': 200},
{'dept': '001', 'sku': 'foo', 'transId': 'uniqueId3', 'qty': 300},
{'dept': '002', 'sku': 'baz', 'transId': 'uniqueId4', 'qty': 400},
{'dept': '002', 'sku': 'baz', 'transId': 'uniqueId5', 'qty': 500},
{'dept': '002', 'sku': 'qux', 'transId': 'uniqueId6', 'qty': 600},
{'dept': '003', 'sku': 'foo', 'transId': 'uniqueId7', 'qty': 700}
]

Desired output for aggregation:

output=[
{'dept': '001', 'sku': 'foo', 'qty': 400},
{'dept': '001', 'sku': 'bar', 'qty': 200},
{'dept': '002', 'sku': 'baz', 'qty': 900},
{'dept': '002', 'sku': 'qux', 'qty': 600},
{'dept': '003', 'sku': 'foo', 'qty': 700}
]

or average:

output=[
{'dept': '001', 'sku': 'foo', 'avg': 200},
{'dept': '001', 'sku': 'bar', 'avg': 200},
{'dept': '002', 'sku': 'baz', 'avg': 450},
{'dept': '002', 'sku': 'qux', 'avg': 600},
{'dept': '003', 'sku': 'foo', 'avg': 700}
]

I have found this: Group by and aggregate the values of a list of dictionaries in Python but it doesn't seem to give me what I want.


To get the aggregated results

from itertools import groupby
from operator import itemgetter

grouper = itemgetter("dept", "sku")
result = []
for key, grp in groupby(sorted(input_data, key = grouper), grouper):
    temp_dict = dict(zip(["dept", "sku"], key))
    temp_dict["qty"] = sum(item["qty"] for item in grp)
    result.append(temp_dict)

from pprint import pprint
pprint(result)

Output

[{'dept': '001', 'qty': 200, 'sku': 'bar'},
 {'dept': '001', 'qty': 400, 'sku': 'foo'},
 {'dept': '002', 'qty': 900, 'sku': 'baz'},
 {'dept': '002', 'qty': 600, 'sku': 'qux'},
 {'dept': '003', 'qty': 700, 'sku': 'foo'}]

And to get the averages, you can simply change the contents inside the for loop, like this

temp_dict = dict(zip(["dept", "sku"], key))
temp_list = [item["qty"] for item in grp]
temp_dict["avg"] = sum(temp_list) / len(temp_list)
result.append(temp_dict)

Output

[{'avg': 200, 'dept': '001', 'sku': 'bar'},
 {'avg': 200, 'dept': '001', 'sku': 'foo'},
 {'avg': 450, 'dept': '002', 'sku': 'baz'},
 {'avg': 600, 'dept': '002', 'sku': 'qux'},
 {'avg': 700, 'dept': '003', 'sku': 'foo'}]

Suggestion: Anyway, I would have added both the qty and avg in the same dict like this

temp_dict = dict(zip(["dept", "sku"], key))
temp_list = [item["qty"] for item in grp]
temp_dict["qty"] = sum(temp_list)
temp_dict["avg"] = temp_dict["qty"] / len(temp_list)
result.append(temp_dict)

Output

[{'avg': 200, 'dept': '001', 'qty': 200, 'sku': 'bar'},
 {'avg': 200, 'dept': '001', 'qty': 400, 'sku': 'foo'},
 {'avg': 450, 'dept': '002', 'qty': 900, 'sku': 'baz'},
 {'avg': 600, 'dept': '002', 'qty': 600, 'sku': 'qux'},
 {'avg': 700, 'dept': '003', 'qty': 700, 'sku': 'foo'}]

Inspired by Eelco Hoogendoorn's answer. Here is another way to resolve this using Pandas package. The code is more readable.

import numpy as np
import pandas as pd

def sum_by_cusip_and_dept(data):
    df = pd.DataFrame(data)
    grouped = df.groupby(['sku', 'dept'])    
    sum = grouped.sum()
    return [{'sku': r[0], 'dept': r[1], 'qty': kv.to_dict().get('qty')} for r, kv in sum.iterrows()]     

Using the numpy EP you can find here, you could write:

inputs = dict( (k, [i[k] for i in input ]) for k in input[0].keys())
print group_by((inputs['dept'], inputs['sku'])).mean(inputs['qty'])

However, you may want to consider using the pandas package if you are doing a lot of relational operations of this kind.