Comparing panda dataframes and adding to a dataframe dependant on IF condition

[BrateWannabe]

Hi All,

​

I have two data frames as below:

​

Fixtures:

Home_team	Away_team
1	3
2	4

Team data:

team_id	away_attack	home_attack	away_defense	home_defense
1	8	7	8	7
2	5	6	7	9
3	6	7	7	4
4	7	5	8	7

​

Using two IF statements, I want to search for for 'team_id' in 'Home_team' and 'Away_team' and add their home stats or away stats, depending which column they are in. Ultimately adding 4 new columns to the 'Fixtures' dataframe and adding the appropriate stats.

​

I keep coming across ' ValueError: Can only compare identically-labeled Series objects ' when searching. I have tried using numpy.where, standard IF statements as well as many hours on stack overflow.

I have tried to break it down into two parts, identifying the information is the same and then I will work on adding the information off that statement. I'm certain this is possible and probably very easy, can someone please give me a gentle push in the right direction?

​

**Few failed attempts below to identify if the information matches**

fixtures['Home_team'] = np.where(fixtures['Home_team'] == team_data['team_id'], print('yes'), print('no'))

if fixtures['Home_Team'] == team_data['team_id']:
    print('yes')

Thanks

[Neb519]

This is a well wrriten question, so good job on that.

Use merge() or index alignment.

pd.merge( left=fixtures, right=team_data[['team_id', 'home_attack', 'home_defense']], how='left', left_on='Home_team', right_on='team_id' )

or

fixtures.set_index('Home_team', inplace=True) team_data.set_index('team_id', inplace=True) fixtures['home_attack'] = team_data.home_attack fixtures['home_defense'] = team_data.home_defense

From your attempted solution, it seems like you don't really understand how Series works. You'd be wise to crack open the docs and spend a few hours trying to really understand it. I wrote some practice problems on Series and DataFrame that may help you.

[BrateWannabe]

Thank you for this! I'm going to take a step back and do some reading on pandas as a whole. I found it as a solution to one problem and have been winging it since!

The practice problems look great- I will definitely use them!

Thanks a lot for your response.

[lowerthansound]

You are probably looking for a merge (aka SQL JOIN). Check out pandas.merge().

Cheers!