0

I am trying to select certain inputs that match my conditions of other columns and create a new column with that value.

I have tried .merge, .loc ifelse and I continue to have something wrong.

I have a dataframe with tickers as the index and the following columns for multiple stocks and option data ['date', 'price', 'exdate', 'position']

If the condition is met of having a '1' in the position column, I want to look in the table and obtain the 'price' on the 'date' that would be equal to the 'exdate' and display it in a new column to the right. The price should be in the same price as the 1. Short example: 

import numpy as np                                  
import pandas as pd 

t = pd.DataFrame(index=['AAPL','AAPL','AAPL','AAPL', 'AMZN', 'AMZN', 'AMZN', 'AMZN'])
t['Date']     = ['12/01/2017','12/02/2017','12/03/2017','12/04/2017','12/01/2017','12/02/2017','12/03/2017','12/04/2017']
t['Price']    = [125.5, 123.1, 126.4, 128.9, 431.2, 433.5, 432.6, 444.0]
t['Exdate']   = ['12/04/2017','12/04/2017','12/04/2017','12/04/2017','12/04/2017','12/04/2017','12/04/2017','12/04/2017']
t['Position'] = [1,0,0,0,0,0,1,0]
t
Rodrigo Lask.
  • 1
  • 1

1 Answers1

0

As far as I understand you want to merge on two conditions, one of which is index match and another date vs exdate match. It is similar to this question, but back then (0.19) Pandas didn't support mixed index/column conditions. Now (0.24) it does, but the index should have a name. So, here is an example:

t.index.name = 'name'
t.loc[t['Position'].astype(bool)].merge(
    t[['Date', 'Price']], left_on=['name', 'Exdate'], right_on=['name', 'Date'])

Output

          Date_x  Price_x      Exdate  Position      Date_y  Price_y
name                                                                
AAPL  12/01/2017    125.5  12/04/2017         1  12/04/2017    128.9
AMZN  12/03/2017    432.6  12/04/2017         1  12/04/2017    444.0

From the question I'm not sure what should be done with records where Position is 0, but I hope you can figure it out the same way.

Marat
  • 15,215
  • 2
  • 39
  • 48
  • Thanks @Marat. I just tried this and I am getting the same table as before, no difference. Any thoughts? `table.index.name = 'ticker' table.loc[table['position'].astype(bool)].merge(table[['date', 'price']], left_on=['ticker', 'exdate'], right_on=['ticker', 'date'])` – Rodrigo Lask. Apr 30 '19 at 22:45
  • I think there is something missing in the code to look for 'position' == 1 – Rodrigo Lask. Apr 30 '19 at 22:51
  • 1) what is your pandas version? This feature was introduced not so long ago. 2) converting to bool is equivalent to `== 1` – Marat May 01 '19 at 02:54
  • and additional question to modify the data, how can I include the rows where position is 0 as you mentioned above? So I don't discard that info which might help me for further calculations – Rodrigo Lask. May 03 '19 at 13:49
  • it depends on what you wanto to do with these rows. Trivially, you can just skip the `.loc[t['Position'].astype(bool)]` part, but I'm not sure how useful will be the result – Marat May 03 '19 at 13:59