how to find values from multiple conditions in pandas

i’m trying to get column for death using value from kill column and how to achieve this by locating the game_id , player , opponent, team and opponent_team. My table look like this

and i need to transform into

I’m expecting to get death by using kill column as for the value from condition each game_id , player , opponent, team and opponent_team

>Solution :

IIUC, you need to perform a self-merge after swapping the team/opponent_team and player/opponent columns:

cols = ['team', 'opponent_team', 'game_id', 'match_id', 'player', 'opponent']

out = df.merge(df.rename(columns={'opponent_team': 'team', 'team': 'opponent_team',
                                  'player': 'opponent', 'opponent': 'player',
                                  'kill': 'death'})[cols+['death']],
               on=cols, how='left')

Output:

       player   opponent  kill  team opponent_team       map  game_id  match_id  death
0         Laz       stax     4  ZETA           DRX  1 Ascent   119512    184456      1
1         Dep       stax     5  ZETA           DRX  1 Ascent   119512    184456      3
2        crow       stax     1  ZETA           DRX  1 Ascent   119512    184456      3
3   SugarZ3ro       stax     1  ZETA           DRX  1 Ascent   119512    184456      2
4       TENNN       stax     4  ZETA           DRX  1 Ascent   119512    184456      2
5         Laz       MaKo     1  ZETA           DRX  1 Ascent   119512    184456      2
6         Dep       MaKo     2  ZETA           DRX  1 Ascent   119512    184456      3
7        crow       MaKo     3  ZETA           DRX  1 Ascent   119512    184456      1
8   SugarZ3ro       MaKo     1  ZETA           DRX  1 Ascent   119512    184456      6
9       TENNN       MaKo     2  ZETA           DRX  1 Ascent   119512    184456      3
10        Laz       Zest     2  ZETA           DRX  1 Ascent   119512    184456      3
11        Dep       Zest     1  ZETA           DRX  1 Ascent   119512    184456      2
12       crow       Zest     2  ZETA           DRX  1 Ascent   119512    184456      4
13  SugarZ3ro       Zest     1  ZETA           DRX  1 Ascent   119512    184456      3
14      TENNN       Zest     4  ZETA           DRX  1 Ascent   119512    184456      3
15        Laz      Foxy9     5  ZETA           DRX  1 Ascent   119512    184456      5
16        Dep      Foxy9     0  ZETA           DRX  1 Ascent   119512    184456      6
17       crow      Foxy9     2  ZETA           DRX  1 Ascent   119512    184456      0
18  SugarZ3ro      Foxy9     2  ZETA           DRX  1 Ascent   119512    184456      3
19      TENNN      Foxy9     2  ZETA           DRX  1 Ascent   119512    184456      2
20        Laz         Rb     3  ZETA           DRX  1 Ascent   119512    184456      6
21        Dep         Rb     2  ZETA           DRX  1 Ascent   119512    184456      1
22       crow         Rb     0  ZETA           DRX  1 Ascent   119512    184456      3
23  SugarZ3ro         Rb     2  ZETA           DRX  1 Ascent   119512    184456      4
24      TENNN         Rb     3  ZETA           DRX  1 Ascent   119512    184456      0
25       stax        Laz     1   DRX          ZETA  1 Ascent   119512    184456      4
26       MaKo        Laz     2   DRX          ZETA  1 Ascent   119512    184456      1
27       Zest        Laz     3   DRX          ZETA  1 Ascent   119512    184456      2
28      Foxy9        Laz     5   DRX          ZETA  1 Ascent   119512    184456      5
29         Rb        Laz     6   DRX          ZETA  1 Ascent   119512    184456      3
30       stax        Dep     3   DRX          ZETA  1 Ascent   119512    184456      5
31       MaKo        Dep     3   DRX          ZETA  1 Ascent   119512    184456      2
32       Zest        Dep     2   DRX          ZETA  1 Ascent   119512    184456      1
33      Foxy9        Dep     6   DRX          ZETA  1 Ascent   119512    184456      0
34         Rb        Dep     1   DRX          ZETA  1 Ascent   119512    184456      2
35       stax       crow     3   DRX          ZETA  1 Ascent   119512    184456      1
36       MaKo       crow     1   DRX          ZETA  1 Ascent   119512    184456      3
37       Zest       crow     4   DRX          ZETA  1 Ascent   119512    184456      2
38      Foxy9       crow     0   DRX          ZETA  1 Ascent   119512    184456      2
39         Rb       crow     3   DRX          ZETA  1 Ascent   119512    184456      0
40       stax  SugarZ3ro     2   DRX          ZETA  1 Ascent   119512    184456      1
41       MaKo  SugarZ3ro     6   DRX          ZETA  1 Ascent   119512    184456      1
42       Zest  SugarZ3ro     3   DRX          ZETA  1 Ascent   119512    184456      1
43      Foxy9  SugarZ3ro     3   DRX          ZETA  1 Ascent   119512    184456      2
44         Rb  SugarZ3ro     4   DRX          ZETA  1 Ascent   119512    184456      2
45       stax      TENNN     2   DRX          ZETA  1 Ascent   119512    184456      4
46       MaKo      TENNN     3   DRX          ZETA  1 Ascent   119512    184456      2
47       Zest      TENNN     3   DRX          ZETA  1 Ascent   119512    184456      4
48      Foxy9      TENNN     2   DRX          ZETA  1 Ascent   119512    184456      2
49         Rb      TENNN     0   DRX          ZETA  1 Ascent   119512    184456      3