python: pandas: add values from other dataframe into new column by condition

Question

I have two dataframes with the following data:

fixtures = pd.DataFrame(
    {'HomeTeam': ["A", "B", "C", "D"], 'AwayTeam': ["E", "F", "G", "H"]})

ratings = pd.DataFrame({'team': ["A", "B", "C", "D", "E", "F", "G", "H"], "rating": [
                       "1,5", "0,2", "0,5", "2", "3", "4,8", "0,9", "-0,4"]})

now i want to map the value from ratings["rating"] to the respective team names but i can't get it to work. is it possible to have new columns with the ratings appear to the right of the HomeTeam and AwayTeam columns?

expected output:

fixtures:

homeTeam  homeTeamRating  awayTeam  AwayTeamRating  
Team A    1,5             Team E    3

Please add the expected output.

Bushmaster
– Bushmaster

2022-12-10 18:45:09 +00:00
Commented Dec 10, 2022 at 18:45 — Bushmaster
– Bushmaster, Commented Dec 10, 2022 at 18:45
i've updated the original post

Robin Reiche
– Robin Reiche

2022-12-10 18:51:57 +00:00
Commented Dec 10, 2022 at 18:51 — Robin Reiche
– Robin Reiche, Commented Dec 10, 2022 at 18:51

Bushmaster · Accepted Answer · 2022-12-10 18:56:04Z

1

you can use:

to_replace=dict(zip(ratings.team,ratings.rating)) #create a dict. Key is team name value is rating.
#{'A': '1,5', 'B': '0,2', 'C': '0,5', 'D': '2', 'E': '3', 'F': '4,8', 'G': '0,9', 'H': '-0,4'}

fixtures['homeTeamRating']=fixtures['HomeTeam'].map(to_replace) #use map and  replace team column as a new column.
fixtures['AwayTeamRating']=fixtures['AwayTeam'].map(to_replace)

fixtures=fixtures[['HomeTeam','homeTeamRating','AwayTeam','AwayTeamRating']]

'''
  HomeTeam homeTeamRating AwayTeam AwayTeamRating
0        A            1,5        E              3
1        B            0,2        F            4,8
2        C            0,5        G            0,9
3        D              2        H           -0,4
'''

answered Dec 10, 2022 at 18:56

Bushmaster

4,6364 gold badges11 silver badges34 bronze badges

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

Comments

Only god knows · Accepted Answer · 2022-12-10 22:06:59Z

0

If you need to apply a method over an existing column in order to compute some values that will eventually be added as a new column in the existing DataFrame, then pandas.DataFrame.apply() method should do the trick.

edited Dec 10, 2022 at 22:06

Only god knows

3051 silver badge13 bronze badges

answered Dec 10, 2022 at 16:31

Ritik Kumar

11 bronze badge

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

python: pandas: add values from other dataframe into new column by condition

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related