Python calculate a new column based on conditions and values of other columns

Question

I'd like to calculate transaction returns in a new column based on some conditions. My data frame looks like this:

stock   price  date            year    yearend price  buy/sell  transaction-volume  aggrvolumne
A        10    2020-2-1        2020       15            buy          100                 100
A        12    2020-12-1       2020       15            sell         50                   50
A        18    2021-2-1        2021       20            sell         50                   0
B
B

I need to group the data frame by stocks. and for the third transaction, I need to look for the last "buy" transaction to get its cost.

The outcome should be like this:

stock   price  date            year    yearend price  buy/sell  transaction-volume  aggrvolumne   return 
A        10    2020-2-1        2020       15            buy          100                 100     NaN
A        12    2020-12-1       2020       15            sell         50                   50     20%
A        18    2021-2-1        2021       20            sell         50                   0     80%
B
B

So how to locate the last 'buy' row in the same group, and get the price I need?

Answer 1

Based on your comment , if every first transaction for a given stock is a buy, you could identify and retrieve its price with this:

df.groupby('stock')['price'].transform('first')

Then , we could take its return based on that value

df['return']=(df.groupby('stock')['price'].transform(pd.Series)-df.groupby('stock')['price'].transform('first'))/(df.groupby('stock')['price'].transform('first'))*100