How do I create a new column in a pandas dataframe that is made up of a lookup value within the same-CodePudding

I have a dataframe with a bunch of sports data in it. I have calculated a set of rolling averages for certain statistics, like team_pace (rolling over the last 5 games). There is a unique game_code that is made up of the three letter team abbreviation date played. I want to create a new column that is the team_pace (called op_team_pace) that represents the team_pace of the other team.

I created a column that is the op_game_code - aka the game_code of the opposing team, so that I can use that value as a "lookup" to find the game data for the other team and extract that team's team_pace value to paste in the op_team_pace column for all games played. I don't know how to do this last step.

Data example:

team    game_code   opponent    team_pace   op_game_code
ABC ABC_01-01-2010  XYZ     50      XYZ_01-01-2010
ABC ABC_02-12-2010  KLM     48      KLM_02-12-2010
KLM KLM_02-12-2010  ABC     65      ABC_02-12-2010
KLM KLM_03-11-2010  XYZ     62      XYZ_03-11-2010
XYZ XYZ_01-01-2010  ABC     47      ABC_01-01-2010
XYZ XYZ_03-11-2010  KLM     63      KLM_03-11-2010

I want a new column op_team_pace that matches op_game_code and returns the team_pace for that opposing team. For example, in the first row, it should find that XYZ game based on op_game_code, (aka the game played against ABC on 01-01-2010) and return the value of 47 for that row of the new op_team_pace column.

CodePudding user response：

You can use the map method to create a new column based on the values of another column. The map method takes a dictionary where the keys are the values in the column you want to map, and the values are the corresponding values for the new column. For example...

# Create a dictionary where the keys are the values in the 'op_game_code' column
# and the values are the corresponding 'team_pace' values
mapping = dict(zip(df['op_game_code'], df['team_pace']))

# Create the 'op_team_pace' column by mapping the values in the 'op_game_code' column
# to the corresponding values in the dictionary
df['op_team_pace'] = df['op_game_code'].map(mapping)