Create new DataFrame column base on other column's search result-CodePudding

I have a dataframe as below with a datetime and a price. I would like to create a new column "lowest price" that checks the lowest price of past 5 minutes and return the price value. Is there a way to do it?

Input :

DateTime            Price
23/11/2021 23:59    57540.60
23/11/2021 23:58    57560.96
23/11/2021 23:57    57581.44
23/11/2021 23:56    57617.08
23/11/2021 23:55    57710.88
23/11/2021 23:54    57654.52
23/11/2021 23:53    57635.68
23/11/2021 23:52    57644.85
23/11/2021 23:51    57626.73

Expected Output :

DateTime            Price         Lowest Price (Past 5 min)
23/11/2021 23:59    57640.60      57560.96
23/11/2021 23:58    57560.96      57560.96
23/11/2021 23:57    57581.44      57581.44
23/11/2021 23:56    57617.08      57554.52
23/11/2021 23:55    57710.88      .
23/11/2021 23:54    57554.52      .
23/11/2021 23:53    57635.68      .
23/11/2021 23:52    57644.85
23/11/2021 23:51    57626.73

CodePudding user response：

Try this :

df.set_index('DateTime').rolling(5).min().shift(-4).reset_index().add_suffix(' min')

if you want to look more than 5 rows, adjust the rolling accordingly. rolling(n) and shift(n-1)

CodePudding user response：

I use below code to resolve the issue.

price_df_min=price_df.set_index('DateTime').rolling(5).min().shift(-4).reset_index().add_suffix(' min')