python pandas select rows where two columns are (not) equal

Question

hsp.loc[hsp['Len_old'] == hsp['Len_new']]

I try this code, it's working.

But I tried these three

hsp.loc[hsp['Type_old'] == hsp['Type_new']] 
hsp.loc[hsp['Type_old'] != hsp['Type_new']] 
hsp.loc[hsp['Len_old'] != hsp['Len_new']]

They are not working.

My data table hsp is like

id  Type_old  Type_new  Len_old  Len_new
1    Num       Num       15       15
2    Num       Char      12       12
3    Char      Num       10       8
4    Num       Num       4        5
5    Char      Char      9        10

Is there a better approach to select rows where two columns are not queal.

What do you mean by "not working"? Whats' the expected output? What's the actual output? I ran the commands you mentioned and they are working I expect them to. — Bonifacio2
– Bonifacio2, Commented Jul 11, 2017 at 17:02
Feel free to up-vote my answer as well if you found it useful. — piRSquared
– piRSquared, Commented Jul 11, 2017 at 18:42

VinceP · Accepted Answer · 2019-08-08 11:17:11Z

43

Use the complement operator ~

hsp.loc[~(hsp['Type_old'] == hsp['Type_new'])]

which gives:

   id Type_old Type_new  Len_old  Len_new
1   2      Num     Char       12       12
2   3     Char      Num       10        8

When dealing with Boolean operations, the complement operator is a handy way to invert True with False

edited Aug 8, 2019 at 11:17

answered Jul 11, 2017 at 16:31

VinceP

2,1832 gold badges23 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

piRSquared · Accepted Answer · 2017-07-11 17:33:38Z

19

Ways to be confused by == versus != when comparing pd.Series

As expected

df[['Len_old', 'Len_new']].assign(NE=df.Len_old != df.Len_new)

   Len_old  Len_new     NE
0       15       15  False
1       12       12  False
2       10        8   True
3        4        5   True
4        9       10   True

But if one of the column's values were strings!

df[['Len_old', 'Len_new']].assign(NE=df.Len_old.astype(str) != df.Len_new)

   Len_old  Len_new    NE
0       15       15  True
1       12       12  True
2       10        8  True
3        4        5  True
4        9       10  True

Make sure both are the same types.

answered Jul 11, 2017 at 17:33

piRSquared

296k68 gold badges509 silver badges654 bronze badges

Comments

JonathanLoscalzo · Accepted Answer · 2021-02-22 22:49:50Z

4

Your code, as piRSquared said, had an issue with types.

Besides that, you could use comparing methods, in this case pd.Series.ne

Using your data:

hsp.loc[hsp['Type_old'].ne(hsp['Type_new'])]

But again, as piRSquared mentioned, because of dtypes it didn't work. Just in case, you have to take care about NaN/None values at your data... such:

hsp.loc[ ( hsp['Type_old'].ne(hsp['Type_new']) ) && (hsp['Type_old'].notna())]

In this case, .ne has another argument, fill_value, which fill missing data.

In addition, you could use "compare" method to show difference between two series (or DataFrames)

hsp.Len_old.compare(hsp.Len_new)

And it might return (if columns were of the same dtype):

   self  other
2  10.0    8.0
3   4.0    5.0
4   9.0   10.0

But just force to have another dtype:

hsp.Len_old.compare(hsp.Len_new.astype('str')) # string type new column

It will return all rows:

answered Feb 22, 2021 at 22:49

JonathanLoscalzo

811 silver badge10 bronze badges

1 Comment

JonathanLoscalzo Over a year ago

Pandas Version: 1.2.2

rachwa · Accepted Answer · 2022-07-17 11:16:17Z

2

You can also use query here:

In [7]: hsp.query('Type_old != Type_new')
Out[7]: 
   id Type_old Type_new  Len_old  Len_new
1   2      Num     Char       12       12
2   3     Char      Num       10        8

answered Jul 17, 2022 at 11:16

rachwa

2,3901 gold badge21 silver badges20 bronze badges

Collectives™ on Stack Overflow

python pandas select rows where two columns are (not) equal

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related