Remapping dataframe values with dictionary key/value

user_8645863 0
Viewed 117

I have a dictionary of simulated team scores:

{'t1': 83,
 't2': 82,
 't3': 66,
 't4': 74}

And a dataframe of matchups:

team_home  score_home  team_away  score_away
t1         0           t2         0
t3         0           t4         0

I want to remap the scores in the dataframe with the corresponding team's score in the dictionary. The final output should look like this:

team_home  score_home  team_away  score_away
t1         83           t2        82
t3         66           t4        74
pandas dataframe python-3.x dictionary
2 Answers

Let's filter the team like columns then stack to reshape and map the values from dct then use unstack and assign the result to the corresponding score like columns:

c = df.filter(like='team').columns
df['score' + c.str.lstrip('team')] = df[c].stack().map(dct).unstack()

  team_home  score_home team_away  score_away
0        t1          83        t2          82
1        t3          66        t4          74
user_128331660

Consider the situation where the following code is given in advance.

import pandas as pd

df = pd.DataFrame({
    'team_home': ['t1', 't3'],
    'score_home': [0, 0],
    'team_away': ['t2', 't4'],
    'score_away': [0, 0],
})
print(df)
#   team_home  score_home team_away  score_away
# 0        t1           0        t2           0
# 1        t3           0        t4           0

team_scores = {
    't1': 83,
    't2': 82,
    't3': 66,
    't4': 74
}
print(team_scores)
# {'t1': 83, 't2': 82, 't3': 66, 't4': 74}

Use pandas .replace(). The Solution is as follows.

# Divide into home and away.
df_home = df[['team_home', 'score_home']]
df_away = df[['team_away', 'score_away']]

# Find all the keys in team_scores and replace with that value.
df_home_all = pd.DataFrame()
df_away_all = pd.DataFrame()
for key in team_scores.keys():
    df_home1 = df_home[df_home['team_home'] == key].copy()
    df_home1['score_home'] = df_home1['score_home'].replace(0, team_scores[key])
    df_away1 = df_away[df_away['team_away'] == key].copy()
    df_away1['score_away'] = df_away1['score_away'].replace(0, team_scores[key])
    df_home_all = pd.concat([df_home_all, df_home1], axis=0)
    df_away_all = pd.concat([df_away_all, df_away1], axis=0)

# Finally concat.
output = pd.concat([df_home_all, df_away_all], axis=1)
print(output)
#   team_home  score_home team_away  score_away
# 0        t1          83        t2          82
# 1        t3          66        t4          74
user_45104210
Related