Python: delete row in dataframe by condition

Question

I want to drop all rows in the ratings df where the team has no game. So not in the fixtures df in HomeTeam or AwayTeam occur. following I tried:

fixtures = pd.DataFrame({'HomeTeam': ["Team1", "Team3", "Team5", "Team6"], 'AwayTeam': [
    "Team2", "Team4", "Team6", "Team8"]})

ratings = pd.DataFrame({'team': ["Team1", "Team2", "Team3", "Team4", "Team5",
                                 "Team6", "Team7", "Team8", "Team9", "Team10", "Team11", "Team12"], "rating": ["1,5", "0,2", "0,5", "2", "3", "4,8", "0,9", "-0,4", "-0,6", "1,5", "0,2", "0,5"]})

ratings = ratings[(ratings.team != fixtures.HomeTeam) &
                  (ratings.team != fixtures.AwayTeam)]

but I get the error message:

ValueError: Can only compare identically-labeled Series objects

what can i do to stop the error from occurring?

Why did you deleted your other question? Seemed like an interesting one. In any case check my answer even if you want to leave it deleted. — mozway
– mozway, Commented Nov 28, 2022 at 8:31
i got a message almost immediately after creating the question that my question was closed by stackoverflow because it was already answered by another question: stackoverflow.com/questions/53010406/… — Robin Reiche
– Robin Reiche, Commented Nov 28, 2022 at 9:02
in the end you are the judge, I believe the closing was not really justified. It was not closed by stackoverflow but by one user who judged it should be closed, other users can vote to reopen — mozway
– mozway, Commented Nov 28, 2022 at 9:07

Bushmaster · Accepted Answer · 2022-11-26 19:57:50Z

1

Because both dataframes are not of equal size. You can use isin() instead.

ratings = ratings[~ratings.team.isin(fixtures.stack())]

#output
'''
    team    rating
6   Team7   0,9
8   Team9   -0,6
9   Team10  1,5
10  Team11  0,2
11  Team12  0,5

'''

Details:

print(fixtures.stack())
'''
0  HomeTeam    Team1
   AwayTeam    Team2
1  HomeTeam    Team3
   AwayTeam    Team4
2  HomeTeam    Team5
   AwayTeam    Team6
3  HomeTeam    Team6
   AwayTeam    Team8
dtype: object
'''

As you can see this returns all values in fixtures. Using the ~ operator in the isin function, we filter out those that do not contain these values.

edited Nov 26, 2022 at 19:57

answered Nov 26, 2022 at 19:50

Bushmaster

4,6364 gold badges11 silver badges34 bronze badges

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

1 Comment

Robin Reiche Over a year ago

i had to remove the ~ and then the code did what i wanted. Thank you

Collectives™ on Stack Overflow

Python: delete row in dataframe by condition

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related