How to delete rows if the value duplicated more than two times in a column

bigsmoke 0
Viewed 23

I have a DataFrame looks like this:

              name  age favorite_color  grade
    Willard Morris   20           blue     88
      Omar Mullins   22         yellow     95
      Space Morris   23           blue     88
  Spencer McDaniel   21          green     70
       Mary Morris   24           blue     88
    Apple Driscoll   26            red     81
    Ahmed Driscoll   24            red     88
       Sneha Petal   24            red     98

The favorite_color "blue" and "red" occur three times, how do I delete these rows and looks like the following:

              name  age favorite_color  grade
      Omar Mullins   22         yellow     95
  Spencer McDaniel   21          green     70

I have tried df.groupby('favorite_color').cumcount().le(2) but failed. Thanks for the help.

pandas python
1 Answers
df = df.groupby('favorite_color').filter(lambda x: len(x) <= 2)

Output:

               name  age favorite_color  grade
1      Omar Mullins   22         yellow     95
3  Spencer McDaniel   21          green     70

You were on the right track though, I think you needed size for your method, rather than a count:

df = df[df.groupby('favorite_color').transform('size').le(2)]
BeRT2me0
Related