Python Pandas Dataframe filter and replace

Question

I constructed a dataframe which looks like this:

title category1 category2 category3 category4
'a'    0.44214    NAN        0.99     0.35
'b'      NAN      NAN        NAN       NAN
'c'      0.31      0.41       0.5       0.53

For each row, I want to indicate the two highest values with 1 and all others with 0.

Result should look like this:

 title category1 category2 category3 category4
'a'    1           0          1         0
'b'    0           0          0         0
'c'    0           0          1         1

Is there a buildin-function solve this or how could this be implemented otherwise?

What if the numbers are 0.9, 0.9, 0.8, 0.8? What would you want to happen? What if the numbers are 0.9, 0.9, 0.9, 0.8? (In other words, how do you want to handle ties?) — DSM
– DSM, Commented Dec 18, 2015 at 17:01
Very unlikely case because these numbers are random floats. But if it happens it should take two of these highest numbers and replace them with 1 — J-H
– J-H, Commented Dec 18, 2015 at 17:07

Alexander · Accepted Answer · 2015-12-18 17:17:02Z

2

You can rank the rows (setting axis=1) in descending order all numeric values in the dataframe. Then do a boolean comparison to find the rank values less than or equal to two (le(2)), which would be rank values 1 and 2. Finally, convert the boolean mask to integers.

>>> df.rank(axis=1, ascending=False, numeric_only=True).le(2).astype(int)
       category1  category2  category3  category4
title                                            
'a'            1          0          1          0
'b'            0          0          0          0
'c'            0          0          1          1

edited Dec 18, 2015 at 17:17

answered Dec 18, 2015 at 17:00

Alexander

111k32 gold badges212 silver badges208 bronze badges

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

3 Comments

J-H Over a year ago

Thank you for your help but unfortunately your solutions seems not to work. Printing the result returns : Empty DataFrame Columns: [] Index: [0, 1]

Alexander Over a year ago

Are you sure your dataframe contains values instead of text? What is the output of df.info()?

J-H Over a year ago

Oh you're right. Thanks. I converted to dtype=float and everything works fine. Really fancy solution. Thanks

Collectives™ on Stack Overflow

Python Pandas Dataframe filter and replace

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related