title: Vectorized lookup another row + calculated field
body: <p>I have this DataFrame "dfSummary" - </p>
  
  <pre><code>exchangeBalances = [['ETHBTC','binance',10], ['LTCBTC','binance',10], ['XRPBTC','binance',10], ['ETHBTC','bitfinex',10], ['LTCBTC','bitfinex',10], ['XRPBTC','bitfinex',10]]
  bidOffers = [
               ['ETHBTC','binance', 0.0035, 0.0351, datetime(2018, 9, 1, 8, 15)], ['LTCBTC','binance',0.009,0.092, datetime(2018, 9, 1, 8, 15)], ['XRPBTC','binance',0.000077, 0.000078, datetime(2018, 9, 1, 8, 15)], ['ETHBTC','bitfinex', 0.003522, 0.0353, datetime(2018, 9, 1, 8, 15)], ['LTCBTC','bitfinex',0.0093,0.095, datetime(2018, 9, 1, 8, 15)], ['XRPBTC','bitfinex',0.000083, 0.000085, datetime(2018, 9, 1, 8, 15)],
               ['ETHBTC','binance', 0.0035, 0.0351, datetime(2018, 9, 1, 8, 30)], ['LTCBTC','binance',0.009,0.092, datetime(2018, 9, 1, 8, 30)], ['XRPBTC','binance',0.000077, 0.000078, datetime(2018, 9, 1, 8, 30)], ['ETHBTC','bitfinex', 0.003522, 0.0353, datetime(2018, 9, 1, 8, 30)], ['LTCBTC','bitfinex',0.0093,0.095, datetime(2018, 9, 1, 8, 30)], ['XRPBTC','bitfinex',0.000083, 0.000085, datetime(2018, 9, 1, 8, 30)], 
               ['ETHBTC','binance', 0.0035, 0.0351, datetime(2018, 9, 1, 8, 45)], ['LTCBTC','binance',0.009,0.092, datetime(2018, 9, 1, 8, 45)], ['XRPBTC','binance',0.000077, 0.000078, datetime(2018, 9, 1, 8, 45)], ['ETHBTC','bitfinex', 0.003522, 0.0353, datetime(2018, 9, 1, 8, 45)], ['LTCBTC','bitfinex',0.0093,0.095, datetime(2018, 9, 1, 8, 45)], ['XRPBTC','bitfinex',0.000083, 0.000085, datetime(2018, 9, 1, 8, 45)]
               ]
  dfExchangeBalances = pd.DataFrame(exchangeBalances, columns=['symbol','exchange','balance'])
  dfBidOffers = pd.DataFrame(bidOffers, columns=['symbol','exchange','bid', 'offer', 'created'])
  dfBidOffers["spread"] = dfBidOffers["bid"] - dfBidOffers["offer"]
  dfSummary = dfExchangeBalances.merge(dfBidOffers, how='left', on=['symbol','exchange'])
  </code></pre>
  
  <p>What I need to accomplish is, add a <strong>computed field</strong> to "dfSummary":</p>
  
  <pre><code>currentRow["Spread"] - someOtherRow["Spread"]
  </code></pre>
  
  <p><strong>"someOtherRow"</strong> is a <strong>look up</strong> based on "created" (For example last row with same {symbol, exchange} but "created" 30min ago (compared to "currentRow")</p>
  
  <p><strong>Clarification</strong>: Above example is <strong>simplification</strong> of real problem at hand. Intervals are not exactly 15 minutes. In fact I need to look up corresponding record (same key = {symbol, exchange}) in DataFrame, but first such record created on 1st month, quarter, and year.</p>
  
  <p>I'm trying to avoid manually looping over DataFrame.iter and use Pandas built in lookup instead (Vectorization)</p>
  
  <p>I'm thinking DataFrame.<strong>Lookup</strong>
      <a href="https://stackoverflow.com/questions/13893227/vectorized-look-up-of-values-in-pandas-dataframe">Vectorized look-up of values in Pandas dataframe</a>
  But not sure how to use this from context of a <strong>calculated field</strong> ...? Also instead of lookup against a <strong>different</strong> DataFrame, I want a lookup against <strong>same</strong> DataFrame.</p>
  
  <p>Vectorization (Pandas and Numpy - vs looping):</p>
  
  <ul>
  <li><p><a href="https://engineering.upside.com/a-beginners-guide-to-optimizing-pandas-code-for-speed-c09ef2c6a4d6" rel="nofollow noreferrer">https://engineering.upside.com/a-beginners-guide-to-optimizing-pandas-code-for-speed-c09ef2c6a4d6</a></p></li>
  <li><p><a href="https://www.datascience.com/blog/straightening-loops-how-to-vectorize-data-aggregation-with-pandas-and-numpy/" rel="nofollow noreferrer">https://www.datascience.com/blog/straightening-loops-how-to-vectorize-data-aggregation-with-pandas-and-numpy/</a></p></li>
  <li><p><a href="https://realpython.com/numpy-array-programming/" rel="nofollow noreferrer">https://realpython.com/numpy-array-programming/</a>  </p></li>
  </ul>