Merging subsequent rows in a Dataframe in Python-CodePudding

I've got a Dataframe that looks like this :

       cross entry  cross exit   Rate
Dates           
2000-04-27  6.49223              6.6130
2000-06-06           6.63997     6.4920
2001-11-26  3.03064              3.1830
2001-12-04           2.99758     2.8000
... ... ... ..

I am trying to get a DataFrame that merges subsequent rows. Meaning that the Entry and Exit Date and the Entry and Exit Rate are all in one row. So it would look like this :

            Exit Date      cross entry  cross exit  Entry Rate   Exit Rate
Entry Dates         
2000-04-27  2000-06-06       6.49223    6.63997     6.6130       6.4920
2001-11-26  2001-12-04       3.03064    2.99758     3.1830       2.8000
.....

I tried to extract the values in lists first to concate them into a new Dataframe later on but it did not work. I feel like I am making it too complicated. Is there a more efficient and more clean way to get that result ?

CodePudding user response：

It is possible to one-line it, but for the sake of comprehension I believe this would be my approach:

df = df.reset_index()
entry_df = df.iloc[df.index[::2]].drop(columns='cross exit').rename(
    columns={'Date':'Entry Date',
             'Rate':'Entry Rate'}).reset_index(drop=True)
exit_df = df.iloc[df.index[1::2]].drop(columns='cross entry').rename(
    columns={'Date':'Exit Date',
             'Rate':'Exit Rate'}).reset_index(drop=True)

Finally:

output = pd.concat([entry_df, exit_df], axis = 1)[['Entry Date',
                                               'Exit Date',
                                               'cross entry',
                                               'cross exit',
                                               'Entry Rate',
                                               'Exit Rate']]

Returning:

   Entry Date   Exit Date  cross entry  cross exit  Entry Rate  Exit Rate
0  2000-04-27  2000-06-06      6.49223     6.63997       6.613      6.492
1  2001-11-26  2001-12-04      3.03064     2.99758       3.183      2.800