Working out summaries

Anvil Q&A
1

I’m having a really, really empty head day and would appreciate some help.

I have an array of data in the following format :

[
  {
    "name" : "Dave",
    "group" : "grpdata1",
    "type" : "typedata",
    "totals" : 1500
  },
  {
    next row..
  }
]

and it’s displayed in tabular form :

Customer   Group   Type   Totals

I want to reduce this down to just :

Customer   Group   Totals

where the total is now a total of the types for each customer/group.

Needs to be done client side if at all possible, and I want to avoid using database queries as the query takes ages (that’s another problem). Happy to use any library that can run client side (Pandas?) but I just cannot claw hammer my head to work out why.

Can anyone help please?

EDIT - In SQL I would be doing this :

SELECT name, group, sum(totals) FROM table GROUP BY name, group

How do I do that in Python?

1 Like
2

I reckon you need either itertools.groupby or pandas.DataFrame.groupby

2 Likes
3

yeah itertools groupby would work here… be careful with itertools groupby.

The data must be sorted by group first…

and a quote from Jack Diederich:

I don’t know how many of you have used the groupby function successfully… in 16 years I’m not sure that I have

from this talk

https://youtu.be/rrBJVMyD-Gs?t=1823

I definitely found the stackoverflow answers on groupby helpful when I was trying to understand it:

https://stackoverflow.com/questions/773/how-do-i-use-itertools-groupby

4

How about something like:

from operator import itemgetter
from itertools import groupby

test = [
    {"name": "Dave", "group": "grpdata1", "type": "typedata", "totals": 1500},
    {"name": "Owen", "group": "grpdata1", "type": "typedata2", "totals": 2000},
]

result = [
    {key: sum(item["totals"] for item in group)}
    for key, group in groupby(
        sorted(test, key=itemgetter("group")), key=itemgetter("group")
    )
]

print(result)
5

throws this error :

AttributeError: 'module' object has no attribute 'itemgetter'

6

Ah. I don’t think skulpt copes with the operator module. You can use a lambda instead as the key:

key=lambda x: x["group"]
1 Like
7

@owen.campbell - almost :slight_smile:

I need the grouping on “name” and “group”

EDIT - I think this is the key :

but how do I use multiple values in the lambda function (to replace itemgetter)? I’m reading up on lambdas but if you can short circuit my ham fisted blundering :slight_smile:

8

itemgetter returns a tuple if you pass it multiple values. Get the lambda to do the same:

key=lambda x: (x["name"], x["group"])
9

So that now looks more like:

from itertools import groupby

test = [
    {"name": "Dave", "group": "grpdata1", "type": "typedata", "totals": 1500},
    {"name": "Owen", "group": "grpdata1", "type": "typedata2", "totals": 2000},
]


def grouper(item):
    return (item["name"], item["group"])


result = [
    {key: sum(item["totals"] for item in group)}
    for key, group in groupby(sorted(test, key=grouper), key=grouper)
]

print(result)
2 Likes
10

@owen.campbell - you are a star - I had tried that (I do try) but I’d got my brackets in a muddle.

It all works! Thank you so much. Here’s what I actually used :

def grouper(item):
    return(item['name'], item['group'])

result = [
    {key: sum(item["totals"] for item in name)}
    for key, name in groupby(
        sorted(test, key=grouper), key=grouper
    )
]

(which is exactly what you just wrote - that’s how addled my head is today, I thought I’d changed it!)

2 Likes
11

I have one last question - how can I format the result into an array of dicts again?

The result comes back like this :

[
  {
    ('dave' : 'group1') : 200
  }
]

with the grouping tuple as the key. I can obviously step through it and recreate an items list that I can feed to a repeating panel, but is there anyway to return the data in that format?

12

Just change the comprehension to do whatever you need. e.g.:

result = [
    {"name": key[0], "group": key[1], "totals": sum(item["totals"] for item in group)}
    for key, group in groupby(sorted(test, key=grouper), key=grouper)
]
2 Likes
13

I am such a dunce today - of course that’s how you do it.

Thanks again - really helped me out, and I learnt something new :slight_smile:

2 Likes