0

I'm trying to remove all rows if a non unique value is found example below:

    N1  N2
1   2   4
2   4   5
3   6   6
4   8   7
5   10  8
6   12  10
7   NaN 12
8   NaN 14

So in this instance the values I want is 2 5 7 and 14. Also one column is longer than the other and hence has to ignore NaN. I basically want to find repeating values and delete both from N1 and N2. This is what I tried:

df[~df.N1.isin(['N2'])]

Got some error. Thank you for your help.

Kevin

Mabel Villalba
  • 2,538
  • 8
  • 19
user3276223
  • 59
  • 1
  • 8

3 Answers3

1

A quick solution:

>> df.stack().drop_duplicates(keep=False).unstack()

    N1    N2
1  2.0   NaN
2  NaN   5.0
4  NaN   7.0
8  NaN  14.0

As a list:

>> df.stack().drop_duplicates(keep=False).values.tolist()

[2.0, 5.0, 7.0, 14.0]
Mabel Villalba
  • 2,538
  • 8
  • 19
0

Here is how it can be achieved:

from io import StringIO
import pandas as pd

s = '''N1 N2
2 4
4 5
6 6
8 7
10 8
12 10
NaN 12
NaN 14'''

ss = StringIO(s)


df = pd.read_csv(ss, sep=r'\s+')

df = df.dropna()

df[~df.N1.isin(['N2'])]

Output: enter image description here

quest
  • 3,576
  • 2
  • 16
  • 26
0

Create a dataframe out of the values that you have posted:

import numpy as np
import pandas as pd

df = pd.DataFrame({'N1':[2, 4, 6, 8, 10, 12, np.nan, np.nan], 
                   'N2':[4,5,6,7,8,10,12,14]})

Find the common values:

common = list(set(df['N1']) & set(df['N2']))

Exclude all the rows that either N1 or N2 has one of them:

df[(~df["N1"].isin(common)) | (~df["N2"].isin(common))]

Update

common = set(df['N1']) & set(df['N2'])
result = list(set(df['N2'])-common) + list(set(df['N1'])-common)
result = [x for x in result if x==x]
DimKoim
  • 1,024
  • 6
  • 20
  • 33