[FIXED] Pandas: create column with data from another row

Issue

I have dataframe like this

data = {'eventId': [1, 2, 3, 4, 5], 'player': ['A', 'B', 'C', 'D', 'E'], 'related_eventId': [2, 1, 4, 3, np.nan]
    }
df = pd.DataFrame(data)

Could you please help how to add column 'related_player'? It should contain the player from row which eventId is equal related_eventId.

Output should be like this

eventId	player	related_eventId	related_player
1	A	2	B
2	B	1	A
3	C	4	D
4	D	3	C
5	E	NaN	NaN

Solution

Create a new DataFrame with the 'related_player' column

import pandas as pd
import numpy as np

data = {'eventId': [1, 2, 3, 4, 5], 'player': ['A', 'B', 'C', 'D', 'E'], 'related_eventId': [2, 1, 4, 3, np.nan]}
df = pd.DataFrame(data)

result = df.merge(df[['eventId', 'player']], left_on='related_eventId', right_on='eventId', how='left', suffixes=('', '_related'))

result.rename(columns={'player_related': 'related_player', 'eventId_related': 'related_eventId'}, inplace=True)

result = result[['eventId', 'player', 'related_eventId', 'related_player']]

print(result)

Answered By - Seonghun