Display table ordered by row and columns

Question

I am struggling to display an HTML table with data issued from a database. I just retrieve the data using a SELECT and Join multiples tables. Datas are retrieved like :

StorageLocation	Quantity	RiskPhrase
Storage1	10	H225
Storage2	4	H225
Storage1	3	H226
Storage3	3	H226
Storage4	3	H226
Storage4	3	H300

I want to be displayed like this :

StorageLocation	H225	H226	H300
Storage1	10	0	3
Storage2	4	0	0
Storage3	0	3	0
Storage4	0	3	3

I really don't know how to deal with this. Is Dataframe the best solution ? Your help will be more than appreciated. Thanks

If you have control over the query I suggest doing the sorting there. You might need a self join and an outer join to get what you are after. — Jonathan, Nov 23 '21 at 15:30
Do you really want the H225 column listed twice in your output? I think your inputs don't match the output in your example. — Jonathan, Nov 23 '21 at 15:33

score 1 · Accepted Answer · answered Nov 23 '21 at 15:36

IIUC, you want pivot:

>>> df.pivot("StorageLocation", "RiskPhrase", "Quantity").fillna(0).rename_axis(None, axis=1)

                 H225  H226  H300
StorageLocation                  
Storage1         10.0   3.0   0.0
Storage2          4.0   0.0   0.0
Storage3          0.0   3.0   0.0
Storage4          0.0   3.0   3.0

Display table ordered by row and columns

1 Answers1