How do you compare substrings in a Python dataframe to create a new column?

Question

I currently have a dataframe where I am analyzing sports data. One column, "Team", has the team that a player belongs to and another column, "Game Info", has the information about the game. The Game Info column looks like this

SAC@HOU 12/09/2019 08:00PM ET

and the Team column could either have "SAC" or "HOU". I am trying to create a new column that contains the opponent. Currently what I've tried is

df.insert(7, "Opp", '', True)
df["Opp"][df['Game Info'].str[:3].str.contains(df['Team'])] = df['Game Info'].str[4:7]
df["Opp"][df['Opp'].empty] = df['Team']

This gives me the following error:

'Series' objects are mutable, thus they cannot be hashed

I have also tried

df['Opp'] = np.where(df['Team'].str != df['Game Info'].str[:3]), df['Game Info'].str[:3], df['Game Info'].str[4:7])

and

df['Opp'] = df['Game Info'].str[:3] if df['Team'].str != df['Game Info'].str[:3] else df['Game Info'].str[4:7]

but both give me the following error:

The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all()

How would I be able to properly compare these substrings?

gbruenjes · Accepted Answer · 2019-12-09 13:00:30Z

1

Use:

df=pd.DataFrame({'Team':['SAC','HOU'], 'Game Info':['SAC@HOU 12/09/2019 08:00PM ET', 'SAC@HOU 12/09/2019 08:00PM ET']})    
df['Opp'] = np.where(df['Team'] == df['Game Info'].str[:3], df['Game Info'].str[4:7], df['Game Info'].str[:3])
df
  Team                      Game Info  Opp
0  SAC  SAC@HOU 12/09/2019 08:00PM ET  HOU
1  HOU  SAC@HOU 12/09/2019 08:00PM ET  SAC

answered Dec 9, 2019 at 13:00

gbruenjes

4,2251 gold badge18 silver badges32 bronze badges

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

Collectives™ on Stack Overflow

How do you compare substrings in a Python dataframe to create a new column?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related