My database includes treatments (treatment_id) that are given to patients at different times during the day (due_time). I would like to display this in a GUI where each treatment has its own row and each due_time is displayed in one of 24 horizontal boxes representing a timeline, 00, 01, 02, etc. Something like this:

               00   01   02   03   04   05 .... 21   22   23
Treatment1      x                        x            x

Treatment2                x              x             

Where Treatment1 is due at 00:00, 05:00 and 22:00, and Treatment2 is due at 02:00 and 05:00

The returned data currently looks like this:

Cols in DB
treatment_id|patient_id|treatment|due_time|status|treatment_schedule_id

[
(13, 91,'co-amox 2.3ml IV', 16, 0, 1), 
(13, 91,'co-amox 2.3ml IV', 14, 0, 3), 
(13, 91,'co-amox 2.3ml IV', 12, 0, 4), 
(13, 91,'co-amox 2.3ml IV', 13, 0, 9), 
(13, 91,'co-amox 2.3ml IV', 6, 0, 10), 
(13, 91,'co-amox 2.3ml IV', 4, 0, 11), 
(13, 91,'co-amox 2.3ml IV', 22, 0, 12), 
(13, 91,'co-amox 2.3ml IV', 18, 0, 13), 
(14, 91,'metacam 4.8kg dose PO SID', 22, 0, 14), 
(14, 91,'metacam 4.8kg dose PO SID', 1, 0, 15), 
(14, 91,'metacam 4.8kg dose PO SID', 7, 0, 16), 
(14, 91,'metacam 4.8kg dose PO SID', 6, 0, 17)]
]

I'd like to manipulate this data in such a way that it is grouped by treatment_id (e.g. 13) and treatment. This would allow me to create a single row with title e.g. "co-amox 2.3ml IV" and then loop over the various due_time values and display them in the horizontal timeline described above.

Here's a minimal working example of what I have so far:

data = [(13, 91, 'co-amox 2.3ml IV', 16, 1), (13, 91, 'co-amox 2.3ml IV', 14, 3), (13, 91, 'co-amox 2.3ml IV', 12, 4), (13, 91, 'co-amox 2.3ml IV', 13, 9), (13, 91, 'co-amox 2.3ml IV', 6, 10), (13, 91, 'co-amox 2.3ml IV', 4, 11), (13, 91, 'co-amox 2.3ml IV', 22, 12), (13, 91, 'co-amox 2.3ml IV', 18, 13), (14, 91, 'metacam 4.8kg dose PO SID', 22, 14), (14, 91, 'metacam 4.8kg dose PO SID', 1, 15), (14, 91, 'metacam 4.8kg dose PO SID', 7, 16), (14, 91, 'metacam 4.8kg dose PO SID', 6, 17)]


def group_rows_by_key(data):
    grouped_data = {}
    for item in data:
        #loop over initial db results (lots of repeated treatment id rows)
        #get id we want to group around e.g. 33
        key = item[0]

        #record associated with this key 
        value = item 

        if key in grouped_data:
            #if treatment id key, add this row to it
            grouped_data[key].append(value)
        else:
            #if no treatment key, then create one, and give it a value of the entire row that contains it
            grouped_data[key] = [value]

    return grouped_data

parsed_data = group_rows_by_key(data)

#below grabs the treatment name value[0][2] to store as 'standalone' single element in list
#needed for row title field corresponding to one-or-more treatments_schedules 8am 10am etc
for key, value in parsed_data.items():
    parsed_data[key] = [value[0][2],value] 
    
print(parsed_data)

This gives me the following result:

{13: 
['co-amox 2.3ml IV', [
(13, 91, 'co-amox 2.3ml IV', 16, 1), 
(13, 91, 'co-amox 2.3ml IV', 14, 3), 
(13, 91, 'co-amox 2.3ml IV', 12, 4), 
(13, 91, 'co-amox 2.3ml IV', 13, 9), 
(13, 91, 'co-amox 2.3ml IV', 6, 10), 
(13, 91, 'co-amox 2.3ml IV', 4, 11), 
(13, 91, 'co-amox 2.3ml IV', 22, 12), 
(13, 91, 'co-amox 2.3ml IV', 18, 13)]
], 

14: ['metacam 4.8kg dose PO SID', [
(14, 91, 'metacam 4.8kg dose PO SID', 22, 14), 
(14, 91, 'metacam 4.8kg dose PO SID', 1, 15), 
(14, 91, 'metacam 4.8kg dose PO SID', 7, 16), 
(14, 91, 'metacam 4.8kg dose PO SID', 6, 17)]
]
}

which works ok but feels a bit clunky and I just wondered if there is a better way of achieving this. In a perfect world, it'd look something like this with just the required values in a simple list:

[13, 'co-amox 2.3ml IV', [ (16,1),(14,3),(12,4),(13,9),(6,10),(4,11),(22,12),(18,13) ]]

[14, 'metacam 4.8kg dose PO SID', [ (22,14), (1,15), (7,16), (6,17) ]]

I was wondering if I should perhaps just make multiple requests to the database to get the result set exactly as I want it, but that could lead to a few dozen DB calls depending on the number of treatment_ids (and due_times) so I have been trying hard to keep it to a single SELECT and then just manipulate the result set.