filtering data in pandas where string is in multiple columns

Question

I have a dataframe that looks like this:

team_1  score_1 team_2  score_2
AUS     2       SCO     1
ENG     1       ARG     0
JPN     0       ENG     2

I can retreive all the data from a single team by using: #list specifiying team of interest

team = ['ENG']

#slice the dataframe to show only the rows where the column 'Team 1' or 'Team 2' value is in the specified string list 'team'

df.loc[df['team_1'].isin(team) | df['team_2'].isin(team)]

team_1  score_1 team_2  score_2
ENG     1       ARG     0
JPN     0       ENG     2

How can I now return only the score for my 'team' such as:

team    score
ENG     1
ENG     2

Maybe creating an index to each team so as to filter out? Maybe encoding the team_1 and team_2 columns to filter out?

Maryam · Accepted Answer · 2020-07-11 12:51:05Z

1

new_df_1 = df[df.team_1 =='ENG'][['team_1', 'score_1']]
new_df_1 =new_df_1.rename(columns={"team_1":"team", "score_1":"score"})
#   team  score
#  0  ENG      1

new_df_2 = df[df.team_2 =='ENG'][['team_2', 'score_2']]
new_df_2 = new_df_2.rename(columns={"team_2":"team", "score_2":"score"})
#  team  score
# 1  ENG      2

then concat two dataframe:

pd.concat([new_df_1, new_df_2])

the output is :

 team  score
0  ENG      1
1  ENG      2

answered Jul 11, 2020 at 12:51

Maryam

7301 gold badge7 silver badges24 bronze badges

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

1 Comment

okpython Over a year ago

Thanks, rather than repeating the two steps, perhaps it would be cleaner to create two loops. Something like... team_score_dict = {'team_1':'score_1', 'team_2':'score_2'}

for i in team_score_dict:     print(i, team_score_dict[i])     df_team_1 = df[df.team_1 == 'ENG'][[i, team_score_dict[i]]]     df_team_1 = df_team_1.rename(columns={"team_1":"team", "score_1":"score"})

but in this instance df_team_1 is being overwritten when the second key is being evaluated. How can i create a dynamic dataframe name?

sammywemmy · Accepted Answer · 2020-07-11 12:56:42Z

0

Melt the columns, filter for values in team, compute the sum of the scores column, and filter for only teams and score:

 team = ["ENG"]

(
    df
    .melt(cols, value_name="team")
    .query("team in @team")
    .assign(score=lambda x: x.filter(like="score").sum(axis=1))
    .loc[:, ["team", "score"]]
)

    team    score
1   ENG        1
5   ENG        2

answered Jul 11, 2020 at 12:56

sammywemmy

28.9k4 gold badges21 silver badges35 bronze badges

Collectives™ on Stack Overflow

filtering data in pandas where string is in multiple columns

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related