How can I reshape and aggregate list of tuples in Python?

Question

I'm a newb to Python so apologies in advance if my question looks trivial.

From a psycopg2 query i have a result in the form of a list of tuples looking like:

[(1, 0), (1, 0), (1, 1), (2, 1), (2, 2), (2, 2), (2, 2)]

Each tuple represents id of a location where event happened and hour of the day when event took place.

I'd like to reshape and aggregate this list with subtotals for each hour in each location, to a form where it looks like:

[(1, 0, 2), (1, 1, 1), (1, 2, 0), (2, 0, 0), (2, 1, 1), (2, 3, 3)]

Where each touple will now tell me that, for example: in location 1, at hour 0 there were 2 events; in location 1, at hour 1 there was 1 event; and so on...

If there were 0 events at certain hour, I still would like to see it, as for example 0 events at 0 hours in location 2: (2, 0, 0)

How could I implement it in Python?

EDIT: Thanks for help!

Answer 1

If you're getting this from the database, why not have the query do it in the first place? Something like: SELECT hour, location, COUNT(*) FROM events GROUP BY hour, location ORDER BY hour, location.

In Python, maybe something like this:

timed_events = {}
# Count them up
for event in events_from_database:
    timed_events[event] = timed_events.setdefault(event, 0) + 1

# Form a new list with the original data plus the count
aggregate_list = [(evt[0], evt[1], count) for evt,count in events.items()]

Answer 2

Something like...:

import collections

raw_data = [(1, 0), (1, 0), (1, 1), (2, 1), (2, 2), (2, 2), (2, 2)]
aux = collections.defaultdict(int)
for x, y in raw_data:
  aux[x, y] += 1

locations = sorted(set(x for x, y in raw_data))
hours = sorted(set(y for x, y in raw_data))
result = [(x, y, aux[x, y]) for x in locations for y in hours]

if you want locations and hours to reflect what's in the raw data. You might want to use range(some, thing) for each of locations and hours instead if you have independent information about the ranges that both locations and hours should span, quite separately from whatever hours and locations actually happen to be in raw_data.