[FIXED] Combining rows of timestamps into a row with multiple measurements at specific times in padas

Issue

I have minute by minute trading data in Pandas that I would like to transform into a single date with specific measurements in each column. Example:

Current data:

Date | 00:01 | Stock Price

Date | 00:02 | Stock Price

.....

Date | 23:59 | Stock Price

Date +1 | 00:01 | Stock Price

Date +1 | 00:02 | Stock Price

....

Date +1 | 23:59 | Stock Price

Desired Result:

Date | Stock price @ "12:00" | Stock price @ "14:00"

Date +1 | Stock price @ "12:00" | Stock price @ "14:00"

etc.

Anyone got a clue on how to get this to work in Pandas? I tried converting it to a CSV and doing it manually with a lot of loops (I need more than 2 measurements), but it ended up getting very messy and hard to modify. Could anyone help me out or point me in the right direction? Thanks!

Solution

I think you want to use pivot.

Suppose the following dataframe:

>>> df
         Date   Time  Stock Price
0  2022-07-16  00:01            1
1  2022-07-16  00:02            2
2  2022-07-16  23:59            3
3  2022-07-17  00:01            4
4  2022-07-17  00:02            5
5  2022-07-17  23:59            6

Output with pivot:

>>> df.pivot('Date', 'Time', 'Stock Price')
Time        00:01  00:02  23:59
Date                           
2022-07-16      1      2      3
2022-07-17      4      5      6

So, if you are looking for pivot, read How can I pivot a dataframe?

Answered By - Corralien