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, Nov 28 '22 at 08: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: https://stackoverflow.com/questions/53010406/pandas-how-to-merge-two-dataframes-on-a-column-by-keeping-the-information-of-th — Robin Reiche, Nov 28 '22 at 09: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, Nov 28 '22 at 09:07

Bushmaster · Accepted Answer · 2022-11-26T19:57:50.227

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.

i had to remove the ~ and then the code did what i wanted. Thank you — Robin Reiche, Nov 26 '22 at 20:35

Python: delete row in dataframe by condition

1 Answers1