CODEWITHSUNDEEP
pythonpandas
N.Varela
N.Varela

Reputation: 910

Python: How to remove rows where multiple columns have equal values?

I want to remove rows where multiple columns have the same values. I read this question about two columns and tried to extend to multiple columns, however I get an error.

Here is some sample data, similar to my dataframe:

import pandas as pd
data = [['table1',10,8,7],['table2',3,3,3],['table3',3,8,11],['table4',12,12,12],['table5',13,15,5]]
df = pd.DataFrame(data,columns=['table_name','Attr1','Attr2','Attr3'])

and my desired result

res = [['table1',10,8,7],['table3',3,8,11],['table5',13,15,5]]
result = pd.DataFrame(res,columns=['table_name','Attr1','Attr2','Attr3'])

I tried 

[df[df['Attr1'] != df['Attr2'] | df['Attr1'] != df['Attr3'] | df['Attr2'] != df['Attr3']]]

which retrieves the error

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

Any ideas?

Upvotes: 1

Views: 1086

Answers (4)

Loochie
Loochie

Reputation: 2472

Use df.query:

df = df.query("Attr1 != Attr2 != Attr3")

Upvotes: 3

Chris Adams
Chris Adams

Reputation: 18647

Boolean index with the condition being that the number of unique values across axis 1, must be equal to the width of the DataFrame:

df = df[df.nunique(axis=1).eq(df.shape[1])]

Upvotes: 2

Alexander
Alexander

Reputation: 109546

You can create conditions for each and then perform your comparison:

c1 = df['Attr1'].ne(df['Attr2'])
c2 = df['Attr1'].ne(df['Attr3'])
c3 = df['Attr2'].ne(df['Attr3'])
>>> df[c1 | c2 | c3]
  table_name  Attr1  Attr2  Attr3
0     table1     10      8      7
2     table3      3      8     11
4     table5     13     15      5

Each condition will be a series indicating whether or not the inequality holds, e.g.

>>> c1
0     True
1    False
2     True
3    False
4     True
dtype: bool

>>> c1 | c2 | c3
0     True
1    False
2     True
3    False
4     True
dtype: bool

Upvotes: 2

jezrael
jezrael

Reputation: 862701

Use DataFrame.ne for compare all values by Attr1 column and test if at least one True per row by DataFrame.any, last filter by boolean indexing:

df = df[df[['Attr1','Attr2','Attr3']].ne(df['Attr1'], axis=0).any(axis=1)]
print (df)
  table_name  Attr1  Attr2  Attr3
0     table1     10      8      7
2     table3      3      8     11
4     table5     13     15      5

Details:

print (df[['Attr1','Attr2','Attr3']].ne(df['Attr1'], axis=0))
   Attr1  Attr2  Attr3
0  False   True   True
1  False  False  False
2  False   True   True
3  False  False  False
4  False   True   True

print (df[['Attr1','Attr2','Attr3']].ne(df['Attr1'], axis=0).any(axis=1))
0     True
1    False
2     True
3    False
4     True
dtype: bool

Another solution is test number of unique values by DataFrame.nunique:

df = df[df[['Attr1','Attr2','Attr3']].nunique(axis=1).ne(1)]

Upvotes: 2

Related Questions