pandas isin not filtering data on multiple columns

Question

nifty

    name      date   time      open      high       low     close
0  NIFTY  20180903  09:16  11736.05  11736.10  11699.35  11700.15
1  NIFTY  20180903  09:17  11699.00  11707.60  11699.00  11701.85
2  NIFTY  20180903  09:18  11702.65  11702.65  11690.95  11692.40
3  NIFTY  20180903  09:19  11692.55  11698.10  11688.65  11698.10
4  NIFTY  20180903  09:20  11698.40  11698.40  11687.25  11687.70

option

      date      time    option_type  strike_price    open    high       low     close  volume  
0     20180903  09:15          CE         11500     313.65  319.10      296.00  299.80    5250 
1     20180903  09:16          CE         11500     299.00  303.85      299.00  300.60    3975  
2     20180903  09:17          CE         11500     299.05  302.30      290.65  293.25    4500
3     20180903  09:18          CE         11500     294.95  300.00      291.00  300.00    1500 
4     20180903  09:19          CE         11500     300.50  300.50      295.60  295.60     975

In both dfs, I want to filter only those rows where date and time is present in both. I have tried isin for same. But it is not working as expected.

option=option[(option.date.isin(nifty.date)) & (option.time.isin(nifty.time))]  
nifty=nifty[(nifty.date.isin(option.date)) & (nifty.time.isin(option.time))]

Can anybody help me on this. My expected output is:

nifty

    name      date   time      open      high       low     close
0  NIFTY  20180903  09:16  11736.05  11736.10  11699.35  11700.15
1  NIFTY  20180903  09:17  11699.00  11707.60  11699.00  11701.85
2  NIFTY  20180903  09:18  11702.65  11702.65  11690.95  11692.40
3  NIFTY  20180903  09:19  11692.55  11698.10  11688.65  11698.10

option

      date      time    option_type  strike_price    open    high       low     close  volume  

1     20180903  09:16          CE         11500     299.00  303.85      299.00  300.60    3975  
2     20180903  09:17          CE         11500     299.05  302.30      290.65  293.25    4500
3     20180903  09:18          CE         11500     294.95  300.00      291.00  300.00    1500 
4     20180903  09:19          CE         11500     300.50  300.50      295.60  295.60     975

score 1 · Accepted Answer · answered Dec 08 '18 at 05:39

1

Use merge for this on 'date' and 'time' only, this way both your df's will return a subset for the matching values;

nifty_ = nifty.merge(option[['date','time']])

option_ = option.merge(nifty[['date', 'time']])

answered Dec 08 '18 at 05:39

Zanshin

1,202
1
9
26

pandas isin not filtering data on multiple columns

1 Answers1