1

I have two dataframes, a base ticker df called ticker_performance and another one called market_performance

enter image description here

enter image description here

I want a column to show if the base ticker beat the performance (%_diff) of the market tickers per Date.

This is what i want:

s = []
for i in market_performance.index:
    diff = market_performance['%_diff'][i]
    date = market_performance['Date'][i]
    
    other_row = ticker_performance[ticker_performance['Date'] == date]
    val = 'Y' if ((other_row['%_diff'].iloc[0] - diff) > 0) else 'N'
    
    s.append(val)
    
market_performance['Beaten?'] = s
market_performance

enter image description here

However this is very inefficient. Is there an efficient way of doing this?

Sample data:

ticker_performance = pd.read_json('{"ticker":{"0":"NET","1":"NET"},"Date":{"0":1612137600000,"1":1612224000000},"Open":{"0":77.75,"1":81.82},"Close":{"0":80.49,"1":83.57},"abs_diff":{"0":2.74,"1":1.75},"%_diff":{"0":3.52,"1":2.14}}')
market_performance = pd.read_json('{"ticker":{"0":"QQQ","1":"QQQ","2":"VOO","3":"VOO"},"Date":{"0":1612137600000,"1":1612224000000,"2":1612137600000,"3":1612224000000},"Open":{"0":318.11,"1":325.48,"2":343.63,"3":349.05},"Close":{"0":322.48,"1":327.76,"2":345.81,"3":350.69},"abs_diff":{"0":4.37,"1":2.28,"2":2.18,"3":1.64},"%_diff":{"0":1.37,"1":0.7,"2":0.63,"3":0.47}}')
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Wboy
  • 2,452
  • 2
  • 24
  • 45
  • `ticker_performance` will only have one row for a given ticker on a given date, so i took 0 cause its easier @Ben.T – Wboy Feb 03 '21 at 14:18
  • 1
    Whoops sorry, forgot python assumes negative values as true as well. edited :) @Ben.T – Wboy Feb 03 '21 at 14:25
  • s = `market_performance.merge(ticker_performance, on='Date', how='left'); np.where(s['%_diff_x'] < s['%_diff_y'], 'Y', 'N')` Please tell me that this is not what are you looking for. – Quang Hoang Feb 03 '21 at 14:49
  • Or even `market_performance['Date'].map(ticker_performance.set_index('Date')['%_diff'])`, which is a variance for `merge` when only one column is needed. – Quang Hoang Feb 03 '21 at 14:51

0 Answers0