How to sum dictionary values based on keys?

Question

I have a list of dictionary as below:

data = [{'student_id': '1','mark': 7.8,'course_id': '1',},
        {'student_id': '1','mark': 34.8,'course_id': '1'},
        {'student_id': '1','mark': 12.8,'course_id': '2'},
        {'student_id': '1','mark': 39.0,'course_id': '2'},
        {'student_id': '1','mark': 70.2,'course_id': '3'},
        {'student_id': '2','mark': 7.8,'course_id': '1'},
        {'student_id': '2','mark': 34.8,'course_id': '1'}]

I am trying to sum the marks per student_id per given course. such as the student no.1's total marks from course 1 will be 42.6, etc. Ideally, I would create a new clean list with only the total marks per students per a course.

One thing came to my mind was to write an iteration to sum add it each if the student and course id from the previous item matches the next one:

for i in range(len(data)-1):
    if data[i]['course_id'] == data[i+1]['course_id'] and data[i]['student_id'] == data[i+1]['student_id']:
        data[i+1]['sum_mark'] = round(float(data[i]['mark'])+float(data[i+1]['mark']),3) 

I don't think this is a good way to approach the problem.

Answer 1

You can do this with stock "low-level" Python easily enough by implementing the special __missing__() method on custom dictionary subclasses to set and return a new instances of the type of container you want there. This approach has been available (and documented) since Python 2.5

Note that a viable and often-used alternative would be to use the more generic collections.defaultdict subclass in the standard library, but since the former it fairly easy, I'll demonstrate doing things that way:

from pprint import pprint


class CourseMarks(dict):
    def __missing__(self, course_id):
        value = self[course_id] = []
        return value


class StudentCourseMarks(dict):
    def __missing__(self, student_id):
        value = self[student_id] = CourseMarks()
        return value


data = [{'student_id': 'id 1','mark': 7.8,'course_id': 'crs 1',},
        {'student_id': 'id 1','mark': 34.8,'course_id': 'crs 1'},
        {'student_id': 'id 1','mark': 12.8,'course_id': 'crs 2'},
        {'student_id': 'id 1','mark': 39.0,'course_id': 'crs 2'},
        {'student_id': 'id 1','mark': 70.2,'course_id': 'crs 3'},
        {'student_id': 'id 2','mark': 7.8,'course_id': 'crs 1'},
        {'student_id': 'id 2','mark': 34.8,'course_id': 'crs 1'}]

scm = StudentCourseMarks()

for grade in data:
    scm[grade['student_id']][grade['course_id']].append(grade['mark'])

print('Student course marks:')
pprint(scm)

for courses in scm.values():
    for course in courses:
        courses[course] = round(sum(courses[course]), 1)

print()
print('Total marks per student per course:')
pprint(scm, compact=0)

Output:

Student course marks:
{'id 1': {'crs 1': [7.8, 34.8], 'crs 2': [12.8, 39.0], 'crs 3': [70.2]},
 'id 2': {'crs 1': [7.8, 34.8]}}

Total marks per student per course:
{'id 1': {'crs 1': 42.6, 'crs 2': 51.8, 'crs 3': 70.2}, 
 'id 2': {'crs 1': 42.6}}

Answer 2

If you're not bothered by sorting your data, you can use itertools.groupby:

data = [
    {'student_id': '1', 'mark': 7.8, 'course_id': '1'},
    {'student_id': '1', 'mark': 34.8, 'course_id': '1'},
    {'student_id': '1', 'mark': 12.8, 'course_id': '2'},
    {'student_id': '1', 'mark': 39.0, 'course_id': '2'},
    {'student_id': '1', 'mark': 70.2, 'course_id': '3'},
    {'student_id': '2', 'mark': 7.8, 'course_id': '1'},
    {'student_id': '2', 'mark': 34.8, 'course_id': '1'}
]

def to_summed(data):
    from itertools import groupby
    from operator import itemgetter

    keys = ("student_id", "course_id")
    key = itemgetter(*keys)

    for current_key, group in groupby(sorted(data, key=key), key=key):
        sum_mark = sum(map(itemgetter("mark"), group))
        yield dict(zip(keys, current_key)) | {"sum_mark": sum_mark}

for entry in to_summed(data):
    print(entry)

Output:

{'student_id': '1', 'course_id': '1', 'sum_mark': 42.599999999999994}
{'student_id': '1', 'course_id': '2', 'sum_mark': 51.8}
{'student_id': '1', 'course_id': '3', 'sum_mark': 70.2}
{'student_id': '2', 'course_id': '1', 'sum_mark': 42.599999999999994}
>>> 

Answer 3

You could use pandas.

import pandas as pd

data = [{'student_id': '1','mark': 7.8,'course_id': '1',},
        {'student_id': '1','mark': 34.8,'course_id': '1'},
        {'student_id': '1','mark': 12.8,'course_id': '2'},
        {'student_id': '1','mark': 39.0,'course_id': '2'},
        {'student_id': '1','mark': 70.2,'course_id': '3'},
        {'student_id': '2','mark': 7.8,'course_id': '1'},
        {'student_id': '2','mark': 34.8,'course_id': '1'}]

df = pd.DataFrame(data)
result = df.groupby(by=["student_id", "course_id"], as_index=False).sum()

print(result)

Output:

  student_id course_id  mark
0          1         1  42.6
1          1         2  51.8
2          1         3  70.2
3          2         1  42.6

See also: Pandas group-by and sum

---

Addendum for completeness: Use result.to_dict(orient="records") to convert back to a dictionary. (Kudos to Priya's answer for that!)

Answer 4

You can also do this easily with pandas library.

import pandas as pd
df = pd.DataFrame(data)
grouped = df.groupby(["student_id","course_id"]).sum()
new_df = grouped.reset_index()
new_df.to_dict(orient='records')

Output:

[{'course_id': '1', 'mark': 42.599999999999994, 'student_id': '1'},
 {'course_id': '2', 'mark': 51.8, 'student_id': '1'},
 {'course_id': '3', 'mark': 70.2, 'student_id': '1'},
 {'course_id': '1', 'mark': 42.599999999999994, 'student_id': '2'}]

Answer 5

Instead of getting stuck with low level python, one can use the pandas data manipulation library.

It supports grouped operations, like sums, means, etc.

Pandas can accept a variety of inputs, including a Python dict, a .csv file, and a number of other formats.

data = [{'student_id': '1','mark': 7.8,'course_id': '1',},
        {'student_id': '1','mark': 34.8,'course_id': '1'},
        {'student_id': '1','mark': 12.8,'course_id': '2'},
        {'student_id': '1','mark': 39.0,'course_id': '2'},
        {'student_id': '1','mark': 70.2,'course_id': '3'},
        {'student_id': '2','mark': 7.8,'course_id': '1'},
        {'student_id': '2','mark': 34.8,'course_id': '1'}]
import pandas as pd
df = pd.DataFrame(data)
df.groupby(['student_id','course_id']).sum()  
# output in iPython or Jupyter
                      mark
student_id course_id      
1          1          42.6
           2          51.8
           3          70.2
2          1          42.6

# often teachers/students need an average, not a sum...
df.groupby(['student_id','course_id']).mean()
                      mark
student_id course_id      
1          1          21.3
           2          25.9
           3          70.2
2          1          21.3

Answer 6

If you use a defaultdict you can use a tuple of (student_id, course_id) for the key. Then you can just add to this as you go. If you want a list at the end, it's a simple list comprehension:

from collections import defaultdict

totals = defaultdict(float)

for d in data:
    totals[(d['student_id'], d['course_id'])] += d['mark']
    
[{'student_id':s_id, 'course_id': c_id, 'total': round(total, 3)} 
 for (s_id, c_id), total in totals.items()]

Which gives you:

[{'student_id': '1', 'course_id': '1', 'total': 42.6},
 {'student_id': '1', 'course_id': '2', 'total': 51.8},
 {'student_id': '1', 'course_id': '3', 'total': 70.2},
 {'student_id': '2', 'course_id': '1', 'total': 42.6}]

Answer 7

You can create temporary dictionary where you sum the marks and then convert this dictionary to your desired format afterwards:

tmp = {}
for d in data:
    tmp.setdefault(d["student_id"], {}).setdefault(d["course_id"], 0)
    tmp[d["student_id"]][d["course_id"]] += d["mark"]

tmp = [
    {"student_id": k, "course_id": kk, "sum_mark": vv}
    for k, v in tmp.items()
    for kk, vv in v.items()
]

print(tmp)

Prints:

[
    {"student_id": "1", "course_id": "1", "sum_mark": 42.599999999999994},
    {"student_id": "1", "course_id": "2", "sum_mark": 51.8},
    {"student_id": "1", "course_id": "3", "sum_mark": 70.2},
    {"student_id": "2", "course_id": "1", "sum_mark": 42.599999999999994},
]