[FIXED] Reformatting DataFrame so that alternating rows are in same row

Issue

I have a dataframe that lists trading activity in subsequent rows. So row 1 is the information for the buy, and row 2 is the information for the sell, and so on.

Ideally I'd like to see the info for each buy/sell pair in the same row. Normally I'd use pivot_table or pivot to do something like this, but the problem is that the rows I want to pivot are alternating.

Here's an example for a dataframe that I have:

values = pd.DataFrame({
    'Date': ['2010-01-03', '2011-02-06', '2012-03-06', '2012-05-15', '2013-04-12', '2014-03-18'],
    'Signal': [1, -1, 1, -1, 1, -1],
    'Close': [100, 103, 106, 108, 111, 114]
})

Which looks like this:

However, here's an example of what I would like:

new_data = pd.DataFrame({
    'Trade': [1, 2, 3],
    'Begin Date': ['2010-01-03', '2012-03-06', '2013-04-12'],
    'End Date': ['2011-02-06', '2012-05-15', '2014-03-18'],
    'Trade Open': [100, 106, 111],
    'Trade Close': [103, 108, 114]
})

Which looks like this:

I know there's a manual way to do this by looping through each row, but am wondering if there's a more succinct, vectorized way to do it instead.

Solution

values = values[values.Signal.eq(1)].reset_index(drop=True).join(values[values.Signal.eq(-1)].reset_index(drop=True),rsuffix="_").assign(Trade=lambda x: x.index+1)[['Trade','Date','Date_','Close','Close_']]
values.columns = ['Trade','Begin Date','End Date','Trade Open','Trade Close']
print(values)

   Trade  Begin Date    End Date  Trade Open  Trade Close
0      1  2010-01-03  2011-02-06         100          103
1      2  2012-03-06  2012-05-15         106          108
2      3  2013-04-12  2014-03-18         111          114

Answered By - Алексей Р