Transform row wise postgres data to grouped column wise data

Question

I have a stock market data which looks likes like

instrument_symbol|close |timestamp          |
-----------------|------|-------------------|
IOC              |134.15|2019-08-05 00:00:00|
YESBANK          | 83.75|2019-08-05 00:00:00|
IOC              |135.25|2019-08-02 00:00:00|
YESBANK          |  88.3|2019-08-02 00:00:00|
IOC              |136.95|2019-08-01 00:00:00|
YESBANK          |  88.4|2019-08-01 00:00:00|
IOC              | 139.3|2019-07-31 00:00:00|
YESBANK          |  91.2|2019-07-31 00:00:00|
YESBANK          | 86.05|2019-07-30 00:00:00|
IOC              | 133.5|2019-07-30 00:00:00|
IOC              |138.25|2019-07-29 00:00:00|

I want to transform it to

timestamp,           IOC,     YESBANK
2019-08-05 00:00:00  134.15   83.75
2019-08-02 00:00:00  135.25   88.3
......
.....
...

format.

Is there some Postgres query to do this? Or do we have to do this programmatically?

just pivot: df.pivot('timestamp','instrument_symbol','close') — anky
– anky, Commented Aug 7, 2019 at 4:18
Pivot (or crosstab) is much better done in the application. SQL isn't really designed to do that. — user330315
– user330315, Commented Aug 7, 2019 at 5:53

Gordon Linoff · Accepted Answer · 2019-08-07 12:03:44Z

2

You can use conditional aggregation. In Postgres, I like the filter syntax:

select "timestamp",
       max(close) filter (where instrument_symbol = 'IOC') as ioc,
       max(close) filter (where instrument_symbol = 'YESBANK') as yesbank
from t
group by "timestamp"
order by 1 desc;

answered Aug 7, 2019 at 12:03

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Asnim P Ansari Over a year ago

What if I have n number of instrument symbols? should I list it one by one?

Gordon Linoff Over a year ago

@AsnimPAnsari . . . If you know what they are, then yes. Otherwise you need to use dynamic SQL . . . or perhaps arrays if that meets your need.

Asnim P Ansari Over a year ago

I want it to be dynamic. As the fields may change & i dont want to hardcode it every time

Kaushik Nayak · Accepted Answer · 2019-08-07 04:55:11Z

1

Use conditional aggregation.

select "timestamp" :: date, max( case 
                         when instrument_symbol = 'IOC' 
                        then close end ) as ioc,
                   max( case 
                         when instrument_symbol = 'YESBANK' 
                        then close end ) as yesbank FROM t
                        group by "timestamp" :: date
                        order by 1 desc

DEMO

answered Aug 7, 2019 at 4:55

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Collectives™ on Stack Overflow

Transform row wise postgres data to grouped column wise data

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related