CODEWITHSUNDEEP
pythonpandas
Iamback
Iamback

Reputation: 86

How to find the column name which has max value by comparing multiple column in pandas

I need the column name for which there are same value in many columns, I need to pick the last max value column name in another column . for example 

   KEY    A    B    C    D    E   F
0    1  100  100  100  100   50  50
1    2  200  200  200   50  200  50

Output should be like 

   KEY    A    B    C    D    E   F  G
0    1  100  100  100  100   50  50  D
1    2  200  200  200   50  200  50  E

Please help me to get the result. I have tried using .idxmax(axis=1) which is pulling the first occurrence and giving me the first column name.

I have a data frame like df

SITE    Counter KEY Full_name   B   C   D   E   F
LO  ALA LO_ALA  ALPHA   100 100 100 50  50
LO  BLA LO_BLA  BALPA   200 200 50  200 50
LO  ALA LO_ALA  ALPHA   1000    1000    1000    100 1000
LO  BLA LO_BLA  BALPA   2000    2000    100 2000    1000

I run the query

df['G'] = df[["B","C","D","E","F"]].values.max(1)
df = df.sort_values(['MAX_LIMIT'], ascending=[0])
df = df.drop_duplicates(['KEY'], keep='first')

after above code, I get one value for each KEY, then I need is that the column name which is matching to the last

Next I run df['H'] =df[["B","C","D","E","F"]].idxmax(axis =1)

I need the output as 

SITE    Counter KEY Full_name G     H
LO  ALA LO_ALA  ALPHA   1000    F   
LO  BLA LO_BLA  BALPA   2000    E

BUT THE OUTPUT I AM GETTING IS 

SITE    Counter KEY Full_name G     H
LO  ALA LO_ALA  ALPHA   1000    B   
LO  BLA LO_BLA  BALPA   2000    B

Upvotes: 0

Views: 88

Answers (1)

Space Impact
Space Impact

Reputation: 13255

Reverse the columns and find idxmax:

df.set_index('KEY', inplace=True)
df[df.columns[::-1]].idxmax(axis=1)

KEY
1    D
2    E
dtype: object

EDIT : Change the query like:

df['G'] = df[["B","C","D","E","F"]].values.max(1)
df = df.drop_duplicates(['KEY'], keep='last')
df['H'] = df[df.columns[4:-1][::-1]].idxmax(axis=1)

print(df)
  SITE Counter     KEY Full_name     B     C     D     E     F     G  H
2   LO     ALA  LO_ALA     ALPHA  1000  1000  1000   100  1000  1000  F
3   LO     BLA  LO_BLA     BALPA  2000  2000   100  2000  1000  2000  E

Upvotes: 2

Related Questions