Question Details

No question body available.

Tags

python mysql dictionary pivot-table

Answers (2)

Accepted Answer Available
Accepted Answer
March 13, 2026 Score: 3 Rep: 173,609 Quality: Expert Completeness: 80%

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 treatmentids (and duetimes) so I have been trying hard to keep it to a single SELECT and then just manipulate the result set.

Fetch and group the data in a single database query using JSON functions and allow the database to do the things that it is good at (rather than trying to get all the data into Python and reorganising it there):

SELECT JSONARRAY(
       treatmentid,
       patientid,
       treatment,
       status,
       JSONARRAYAGG(JSONARRAY(duetime, treatmentscheduleid))
       ) AS treatment
FROM   treatments
GROUP BY
       patientid,
       treatmentid,
       treatment,
       status;

Which, for the sample data:

CREATE TABLE treatments (
  treatmentid INT,
  patientid   INT,
  treatment    VARCHAR(50),
  duetime     INT,
  status       INT,
  treatmentscheduleid INT
);
INSERT INTO treatments (treatmentid, patientid, treatment, duetime, status, treatmentscheduleid)
VALUES (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);

Which outputs:

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

Or, if you wanted the data as individual columns then you can use conditional aggregation:

SELECT treatmentid,
       patientid,
       treatment,
       status,
       MAX(CASE duetime WHEN  0 THEN treatmentscheduleid END) AS "00",
       MAX(CASE duetime WHEN  1 THEN treatmentscheduleid END) AS "01",
       MAX(CASE duetime WHEN  2 THEN treatmentscheduleid END) AS "02",
       MAX(CASE duetime WHEN  3 THEN treatmentscheduleid END) AS "03",
       MAX(CASE duetime WHEN  4 THEN treatmentscheduleid END) AS "04",
       MAX(CASE duetime WHEN  5 THEN treatmentscheduleid END) AS "05",
       MAX(CASE duetime WHEN  6 THEN treatmentscheduleid END) AS "06",
       MAX(CASE duetime WHEN  7 THEN treatmentscheduleid END) AS "07",
       MAX(CASE duetime WHEN  8 THEN treatmentscheduleid END) AS "08",
       MAX(CASE duetime WHEN  9 THEN treatmentscheduleid END) AS "09",
       MAX(CASE duetime WHEN 10 THEN treatmentscheduleid END) AS "10",
       MAX(CASE duetime WHEN 11 THEN treatmentscheduleid END) AS "11",
       MAX(CASE duetime WHEN 12 THEN treatmentscheduleid END) AS "12",
       MAX(CASE duetime WHEN 13 THEN treatmentscheduleid END) AS "13",
       MAX(CASE duetime WHEN 14 THEN treatmentscheduleid END) AS "14",
       MAX(CASE duetime WHEN 15 THEN treatmentscheduleid END) AS "15",
       MAX(CASE duetime WHEN 16 THEN treatmentscheduleid END) AS "16",
       MAX(CASE duetime WHEN 17 THEN treatmentscheduleid END) AS "17",
       MAX(CASE duetime WHEN 18 THEN treatmentscheduleid END) AS "18",
       MAX(CASE duetime WHEN 19 THEN treatmentscheduleid END) AS "19",
       MAX(CASE duetime WHEN 20 THEN treatmentscheduleid END) AS "20",
       MAX(CASE duetime WHEN 21 THEN treatmentscheduleid END) AS "21",
       MAX(CASE duetime WHEN 22 THEN treatmentscheduleid END) AS "22",
       MAX(CASE duetime WHEN 23 THEN treatmentscheduleid END) AS "23"
FROM   treatments
GROUP BY
       patientid,
       treatmentid,
       treatment,
       status;

Which outputs:

treatmentid patientid treatment status 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23
13 91 co-amox 2.3ml IV 0 null null null null 11 null 10 null null null null null 4 9 3 null 1 null 13 null null null 12 null
14 91 metacam 4.8kg dose PO SID 0 null 15 null null null null 17 16 null null null null null null null null null null null null null null 14 null

Then you can use Python to display it.

fiddle

March 13, 2026 Score: 1 Rep: 98,036 Quality: Medium Completeness: 60%

How about this:

def grouprowsbykey(data):
    groupeddata = {}
    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[3], item[4]) 
        if key in groupeddata:
            #if treatment id key, add value tupel to it
            groupeddata[key][2].append(value)
        else:
            #if no treatment key, then create one
            groupeddata[key] = [item[0], item[2], [value]]
    return groupeddata
parseddata = grouprowsbykey(data)
print(parsed_data)

Result will be

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

Using an object on the outer level instead of array just makes it easier to find the right element to append new data to; with an array you would need some method to loop over the existing items and see if you find one that matches the current ID.