让我们以Pandas Dataframe 为例，它有两列["date"]和["price"]：['date']总是升序['price']是随机的

df = pd.DataFrame({
'date':['01/01/2019','01/02/2019','01/03/2019','01/04/2019','01/05/2019','01/06/2019','01/07/2019','01/08/2019','01/09/2019','01/10/2019'],
'price': [10,2,5,4,12,8,9,19,12,3]
})

目标是再添加两列['next_date']包含第一次出现价格高于当前价格的日期['next_price']包含第一次出现价格高于当前价格的价格
像这样

date  price   next_date next_price
0  01/01/2019     10  01/05/2019         12
1  01/02/2019      2  01/03/2019          5
2  01/03/2019      5  01/05/2019         12
3  01/04/2019      4  01/05/2019         12
4  01/05/2019     12  01/08/2019         19
5  01/06/2019      8  01/07/2019          9
6  01/07/2019      9  01/08/2019         19
7  01/08/2019     19         NaN        NaN
8  01/09/2019     12         NaN        NaN
9  01/10/2019      3         NaN        NaN

我已经测试了一些解决方案，它们确实达到了我的要求，但是性能非常差，实际的df有超过一百万行
以下是我的测试溶液：
使用Pandasql

result = sqldf("SELECT l.date, l.price, min(r.date) as next_date from df as l left join df as r on (r.date > l.date and r.price > l.price) group by l.date, l.price  order by l.date")
result=pd.merge(result ,df, left_on='next_date', right_on='date', suffixes=('', '_next'), how='left')
print(result)

使用Pandas到SQLite

df.to_sql('df', conn, index=False)
qry = "SELECT l.date, l.price, min(r.date) as next_date from df as l left join df as r on (r.date > l.date and r.price > l.price) group by l.date, l.price  order by l.date "
result = pd.read_sql_query(qry, conn)
result=pd.merge(result ,df, left_on='next_date', right_on='date', suffixes=('', '_next'), how='left')
print(result)

使用应用

def find_next_price(row):
    mask = (df['price'] > row['price']) & (df['date'] > row['date'])
    if len(df[mask]):
        return df[mask]['date'].iloc[0], df[mask]['price'].iloc[0]
    else:
        return np.nan, np.nan

df[['next_date', 'next_price']] = list(df.apply(find_next_price, axis=1))
print(df)

其中一些解决方案在50000行DF上开始失败，而我需要在1000000行DF上执行此任务
注：这里有一个非常类似的问题：而且https://stackoverflow.com/questions/72047646/python-pandas-add-column-containing-first-index-where-future-column-value-is-gr性能较差