How do I append all the rows in my pandas dataframe into one big row?

0

Issue

I want to create new columns based on the time column.
I am trying to append the last 5 values in each row onto row zero, and then use the time column as the header for each column.

index ticker date time vol open close high low

0 AAPL 2022-01-06 09:00 121611 174.78 174.00 175.08 173.76
1 AAPL 2022-01-06 10:00 83471 174.11 173.89 174.64 173.88
2 AAPL 2022-01-06 11:00 76327 173.99 173.55 174.25 173.16
3 AAPL 2022-01-06 12:00 83471 174.11 173.89 174.64 173.88

Ultimately I want it to look like this:

Ticker date time vol9am open9am close9am high9am low9am vol10am open10am close10am high10am low10am

AAPL 2022-01-06 09:00 121611 174.78 174.00 175.08 173.76 83471 174.11 173.89 174.64 173.88

Any suggestions?

Solution

Pandas’ unstack function will achieve this. This will provide a multi-index for the column names; you can use to_flat_index if you only want a single layer index:

df = pd.DataFrame(columns = ['index', 'ticker', 'date', 'time', 'vol', 'open', 'close', 'high', 'low'], 
            data = [
                [0, 'AAPL', '2022-01-06', '09:00', 121611, 174.78, 174.00, 175.08, 173.76],
                [1, 'AAPL', '2022-01-06', '10:00', 83471, 174.11, 173.89, 174.64, 173.88],
                [2, 'AAPL', '2022-01-06', '11:00', 76327, 173.99, 173.55, 174.25, 173.16],
                [3, 'AAPL', '2022-01-06', '12:00', 83471, 174.11, 173.89, 174.64, 173.88],
            ]
            )

df.set_index(['ticker','date','time'])[['open','close']].unstack()

Ideally if you can post your questions with some minimum working code it makes it much easier to replicate 🙂

Answered By – DaveB

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave A Reply

Your email address will not be published.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More