merge data using pandas to get exclusive right data

Question

I have two sets

A = {1,2,3,4,5,6}
                B = {4,5,6,7,8,9}

I want to get data from set B which is excusive to set B (i.e., data should not include intersection data)

exclBdata = pd.merge(A, B, how='right', left_index =True, right_index = True)

when i use above command i am getting

{4,5,6, 7,8,9}

I think i am not passing arguments properly. Please correct me what is right command to get output as

{7,8,9}

Below I am mentioning data frame example

right1 = DataFrame({'key': ['a', 'b', 'a', 'a', 'b', 'c'],
           'value': range(6)})
>>> left1 = DataFrame({'group_val': [3.5, 7]}, index=['a', 'b'])
>>> right1
  key  value
0   a      0
1   b      1
2   a      2
3   a      3
4   b      4
5   c      5
>>> right1
  key  value
0   a      0
1   b      1
2   a      2
3   a      3
4   b      4
5   c      5
>>> left1
   group_val
a        3.5
b        7.0

so when i do merge I should get only 'c' 5 as 'a' and 'b' exists in both

Thanks (FYI: I am using python 3.4.2)

If these are actually sets you need `B-A`. Why are you using pandas for this? — ayhan, Jul 17 '17 at 08:51
Above I made a simple example. Actually I have big data frame with multiple columns. — venkysmarty, Jul 17 '17 at 08:52

Till · Answer 1 · 2017-07-17T09:46:24.170

2

You can try to use a mask:

import pandas as pd
A = {1,2,3,4,5,6}
B = {4,5,6,7,8,9}
a = pd.DataFrame(list(A), columns=['test'])
b = pd.DataFrame(list(B), columns=['test'])
mask = b['test'].isin(a.test)
b[~mask]

Out[7]: 
   test
3     7
4     8
5     9

Source: Excluding rows from a pandas dataframe based on column value and not index value

edited Jul 17 '17 at 09:46

answered Jul 17 '17 at 09:34

Till

4,183
3
16
18

score 2 · Answer 2 · answered Jul 17 '17 at 09:45

Alternatively to isin() method (which was very well explained in the @Till's answer) we can use query() method:

In [223]: right1.query("key not in @left1.index")
Out[223]:
  key  value
5   c      5

Source DFs:

In [224]: left1
Out[224]:
   group_val
a        3.5
b        7.0

In [225]: right1
Out[225]:
  key  value
0   a      0
1   b      1
2   a      2
3   a      3
4   b      4
5   c      5

score 0 · Answer 3 · answered Jul 17 '17 at 08:56

0

No need for pandas if you are working on set

Try this :

A = {1,2,3,4,5,6}
B = {4,5,6,7,8,9}

# You want to take the elements in B if they are not in A
C = [element for element in B if element not in A]

answered Jul 17 '17 at 08:56

AZJB

76
8

actually I am looking for merge operation as mentioned above I am using data frame. Data frame example is given above in question – venkysmarty Jul 17 '17 at 09:07
Can't you merge them "usually" then use something like : df = df[df['C'].isin(df['A'] .tolist()] , that's tough to imagine what your data look like ... – AZJB Jul 17 '17 at 09:13
Sorry I didn't get from example mentioned in question request to please elobarte. Thanks – venkysmarty Jul 17 '17 at 09:19

merge data using pandas to get exclusive right data

3 Answers3