List of dictionaries collection based on total and groupby of keys

Question

My dictionary looks like following

d = [{'status': 'Red', 'name': 'Alex'}, {'status': 'Red', 'name': 'Alex'}, 
     {'status': 'Green', 'name': 'Alex'}, {'status': 'Yellow', 'name': 'Peter'}, 
     {'status': 'Green', 'name': 'Mike'}, {'status': 'Yellow', 'name': 'Alex'}, 
     {'status': 'Green', 'name': 'Peter'}, {'status': 'Red', 'name': 'Mike'}, 
     {'status': 'Yellow', 'name': 'Alex'}]

I am trying to aggregate it in most efficient way, my desired output should look like following

d = [{"name": "Alex", "Red": 2, "Green": 1, "Yellow": 2, "Total": 5},
     {"name": "Peter", "Red": 0, "Green": 1, "Yellow": 1, "Total": 2},
     {"name": "Mike", "Red": 1, "Green": 1, "Yellow": 0, "Total": 2}
    ]

I am able to aggregate by total count but having trouble grouping it by 'status' value

from collections import Counter
output = Counter(i['name'] for i in d)

Answer 1

One method to do this is by creating a dict and storing the values and finally converting it to the required format. In my case, I created an empty dict with the name as the keys and update the values (This works out to O(N) as compared to directly creating it with a lower efficiency of O(N^2)). The code for the same:

d = [{'status': 'Red', 'name': 'Alex'}, {'status': 'Red', 'name': 'Alex'}, 
     {'status': 'Green', 'name': 'Alex'}, {'status': 'Yellow', 'name': 'Peter'}, 
     {'status': 'Green', 'name': 'Mike'}, {'status': 'Yellow', 'name': 'Alex'}, 
     {'status': 'Green', 'name': 'Peter'}, {'status': 'Red', 'name': 'Mike'}, 
     {'status': 'Yellow', 'name': 'Alex'}]

res = {}
for val in d:
    if val["name"] not in res:
        res[val["name"]] = {"Red": 0, "Green": 0, "Yellow": 0}
    res[val["name"]][val["status"]] += 1
data = [{"name": key,
         "Red": res[key]["Red"],
         "Green": res[key]["Green"],
         "Yellow": res[key]["Yellow"]} for key in res.keys()]