Question: Let's say I have a Pandas Dataframe of the price and stock history of a product at 10 different points in time: df = pd.DataFrame(index=[np.arange(10)]) df['price'] = 10,10,11,15,20,10,10,11,15,20 df['stock'] = 30,20,13,8,4,30,20,13,8,4 df   price stock 0 10 30 1 10 20 2 11 13 3 15 8 4 20 4 5 10 30 6 10 20 7 11 13 8 15 8 9 20 4  How do I perform operations between specific rows that meet certain criteria? In my example row 0 and row 5 meet the criteria "stock over 25" and row 4 and row 9 meet the criteria "stock under 5". I would like to calculate: df['price'][4] - df['price'][0] and df['price'][9] - df['price'][5] but not df['price'][9] - df['price'][0] or df['price'][4] - df['price'][5].  In other words, I would like to calculate the price change between the most recent event where stock was under 5 vs the most recent event where stock was over 25; over the whole series. Of course, I would like to do this over larger datasets where picking them manually is not good.

Answer: First, set up data frame and add some calculations: import pandas as pd import numpy as np df = pd.DataFrame(index=[np.arange(10)]) df['price'] = 10,10,11,15,20,10,10,11,15,20 df['stock'] = 30,20,13,8,4,30,20,13,8,4 df['stock_under_5'] = df['stock'] < 5 df['stock_over_25'] = df['stock'] > 25 df['cum_stock_under_5'] = df['stock_under_5'].cumsum() df['change_stock_under_5'] = df['cum_stock_under_5'].diff() df['change_stock_under_5'].iloc[0] = df['stock_under_5'].iloc[0]*1 df['next_row_change_stock_under_5'] = df['change_stock_under_5'].shift(-1) df['cum_stock_over_25'] = df['stock_over_25'].cumsum() df['change_stock_over_25'] = df['cum_stock_over_25'].diff() df['change_stock_over_25'].iloc[0] = df['stock_over_25'].iloc[0]*1 df['next_row_change_stock_over_25'] = df['change_stock_over_25'].shift(-1) df['row'] = np.arange(df.shape[0]) df['next_row'] = df['row'].shift(-1) df['next_row_price'] = df['price'].shift(-1)  Next we find all windows where either the stock went over 25 or below 5 by grouping over the cumulative marker of those events. changes = ( df.groupby(['cum_stock_under_5', 'cum_stock_over_25']) .agg({'row':'first', 'next_row':'last', 'change_stock_under_5':'max', 'change_stock_over_25':'max', 'next_row_change_stock_under_5':'max', 'next_row_change_stock_over_25':'max', 'price':'first', 'next_row_price':'last'}) .assign(price_change = lambda x: x['next_row_price'] - x['price']) .reset_index(drop=True) )  For each window we find what happened at the beginning of the window: if change_stock_under_5 = 1 it means the window started with the stock going under 5, if change_stock_over_25 = 1 it started with the stock going over 25. Same for the end of the window using the columns next_row_change_stock_under_5 and next_row_change_stock_over_25  Now, we can readily extract the stock price change in rows where the stock went from being over 25 to being under 5: from_over_to_below = changes[(changes['change_stock_over_25']==1) & (changes['next_row_change_stock_under_5']==1)]  and the other way around: from_below_to_over = changes[(changes['change_stock_under_5']==1) & (changes['next_row_change_stock_over_25']==1)]  You can for example calculate the average price change when the stock went from over 25 to below 5: from_over_to_below.price_change.mean()