Get a value from another row and add to the current row based on a certain info included in the current row - for the entire dataframe

Although I'm aware of how to get info of specific rows based on some data (and in particular of this rock-solid answer about that), I'm struggling to figure out the best way of doing this for the entire dataframe, hopefully without having to loop through it.

I have records of "buy" and "sell" transactions, all in the same df. What triggered each "buy" transaction is recorded in the respective row where the "buy" transaction is saved. I need to copy that information to the equivalent "sell" transactions. The sell transaction rows contain the "ID" of the "buy" transactions, which are the index of the df.

     Type   Trigger   Buy ID  ID     11   buy     AA       ---  12   buy     BB       ---  13   sell    ---      11  14   sell    ---      12  

So I want to create a new column (e.g. "BuyTrigger") and add the trigger from the "buy" transaction to the "sell" rows. What I have tried (among others):

df = data[data["Type"] == "sell"]  df["BuyTrigger"] = data.loc[df["Buy order ID"]]["Trigger"]  

Above, I've created a copy of a slice of the data with the sell transactions only to make the process faster. Buy that is returning all NANs for "Buy Trigger".

And just to make it super clear: there are two dataframes. DATA contains all records; DF contains only the sell transactions.

Expected result:

     Type   Trigger   Buy ID  BuyTrigger  ID     11   buy     AA       ---     ---   12   buy     BB       ---     ---  13   sell    ---      11      AA  14   sell    ---      12      BB  

I appreciate your help!

https://stackoverflow.com/questions/66609004/get-a-value-from-another-row-and-add-to-the-current-row-based-on-a-certain-info March 13, 2021 at 08:45AM