Using idxmax to get value of another column

Question

Dataset:

Data example

I want to make a new column that analyzes the max amount of kills in the row and outputs the player's name. First row would be "arc warden", second row "clinkz", third "axe", fourth "naga siren", and so on. What I achieved:

HKP = final_df.loc(axis='columns')[:,'kills'].idxmax(axis=1).map(lambda x: (list(x)[0],'name'))
for i in range(len(final_df)):
    final_df.loc[i,'HKP'] = final_df.loc[i,HKP[i]]

HKP (highest kill player) is a series column name where kills are maximum. Map is used to change the tuple (since output is still on the kills column so it changes from ('player','kills') to ('player','name')). Then I loop to get names for each row.

It is too slow. Are there other ways?

Oh, and if anyone can teach me how to post a proper dataframe example in stackoverflow it would be helpful too since it is my first time here. Thanks! — Forevermore, Aug 21 '23 at 03:10
Update your post with the output of `print(final_df.head(10).to_dict())` to create a reproducible example. You can also read [How to make good reproducible pandas examples](https://stackoverflow.com/q/20109391/15239951) — Corralien, Aug 21 '23 at 03:52

mozway · Accepted Answer · 2023-08-21T06:38:33.387

Using xs and indexing lookup:

# example dataframe
df = pd.DataFrame([['A', 22, 'B', 10],
                   ['C', 5, 'D', 6]],
                  columns=pd.MultiIndex.from_product([['player_0', 'player_1'], ['name', 'kills']]))

# slice kills and get max column
idx, cols = pd.factorize(df.xs('kills', level=1, axis=1).idxmax(axis=1))

# get name matching max column
df['HKP'] = (df.xs('name', level=1, axis=1)
               .reindex(cols, axis=1).to_numpy()[np.arange(len(df)), idx]
             )

Alternative with reshaping:

tmp = df.stack(0)
df['HKP'] = tmp.loc[tmp['kills'].groupby(level=0).idxmax(), 'name'].droplevel(1)

Output:

  player_0       player_1       HKP
      name kills     name kills    
0        A    22        B    10   A
1        C     5        D     6   D

score 0 · Answer 2 · answered Aug 21 '23 at 06:43

This would be my way of solving, using pd.melt and groupby:

import pandas as pd

data = {
    ('player_1', 'name'): ['Juggernaut'] * 20 + ['Pudge'] * 20,
    ('player_1', 'kills'): range(40),
    ('player_2', 'name'): ['Clinkz'] * 20 + ['Axe'] * 20,
    ('player_2', 'kills'): range(1,41),
    ('player_3', 'name'): ['Tiny'] * 20 + ['Snapfire'] * 20,
    ('player_3', 'kills'): range(2,42),
    ('player_4','name'): ['Undying'] * 20 + ['Riki'] * 20,
    ('player_4','kills'): range(3,43),
}

df = pd.DataFrame(data)

# df.melt(id_vars = [('player_1', 'name'), ('player_2', 'name'), ('player_3', 'name'), ('player_4', 'name')],
#         value_vars = [('player_1', 'kills'), ('player_2', 'kills'), ('player_3', 'kills'), ('player_4', 'kills')],
#         ignore_index=False)

df['Game'] = range(len(df))

df = df.melt(id_vars='Game', var_name = ['player', 'type'])

df = pd.merge(df.loc[df['type'] =='name', ['Game', 'player', 'value']], 
         df.loc[df['type'] =='kills', ['Game', 'player', 'value']], 
         on=['Game', 'player']).rename(columns={'value_x': 'name', 'value_y': 'kills'})

df.groupby('Game')
idx = df.groupby(['Game'])['kills'].transform(max) == df['kills']
print(df[idx])

Output:

     Game    player     name kills
120     0  player_4  Undying     3
121     1  player_4  Undying     4
122     2  player_4  Undying     5
123     3  player_4  Undying     6
124     4  player_4  Undying     7
125     5  player_4  Undying     8
126     6  player_4  Undying     9
127     7  player_4  Undying    10
128     8  player_4  Undying    11
129     9  player_4  Undying    12
130    10  player_4  Undying    13
131    11  player_4  Undying    14
132    12  player_4  Undying    15
133    13  player_4  Undying    16
134    14  player_4  Undying    17
135    15  player_4  Undying    18
136    16  player_4  Undying    19
137    17  player_4  Undying    20
138    18  player_4  Undying    21
139    19  player_4  Undying    22
140    20  player_4     Riki    23
141    21  player_4     Riki    24
142    22  player_4     Riki    25
143    23  player_4     Riki    26
144    24  player_4     Riki    27
145    25  player_4     Riki    28
146    26  player_4     Riki    29
147    27  player_4     Riki    30
148    28  player_4     Riki    31
149    29  player_4     Riki    32
150    30  player_4     Riki    33
151    31  player_4     Riki    34
152    32  player_4     Riki    35
153    33  player_4     Riki    36
154    34  player_4     Riki    37
155    35  player_4     Riki    38
156    36  player_4     Riki    39
157    37  player_4     Riki    40
158    38  player_4     Riki    41
159    39  player_4     Riki    42

Using idxmax to get value of another column

2 Answers2