0

You can see my dataset below. I want to transform this table and have the name of the column with the maximum value of the columns in one column [Names]. For example for the first row, I need to have 'store_other' because this store has the value of 400, and 400 is maxium.

 import pandas as pd
import numpy as np
    
data = { 
            'store_A': [100,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,100], 
            'store_B': [0,100,0,100,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,400,0], 
            'store_C': [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0], 
            'store_D': [0,100,0,100,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0], 
            'store_E': [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0], 
            'store_F': [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0], 
            'store_G': [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0], 
            'store_H': [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0], 
            'store_I': [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0], 
            'store_J': [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0], 
            'store_K': [0,0,0,0,0,100,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,100], 
            'store_L': [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0], 
            'store_M': [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0], 
            'store_N': [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0], 
            'store_O': [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0], 
            'store_P': [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0], 
            'store_Q': [100,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,100], 
            'store_R': [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0], 
            'store_S': [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0], 
            'store_T': [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0], 
            'store_U': [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0], 
            'store_other': [400,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,400], 
           }
    
    
df = pd.DataFrame(data, columns = ['store_A','store_B','store_C','store_D','store_E','store_F','store_G','store_H','store_I','store_J','store_K',
                    'store_L','store_M','store_N','store_O','store_P','store_Q','store_R','store_S','store_T','store_U','store_other'])
    
    
df

I tried with this function but this gives me all names.

df['Names'] = df.apply(lambda column: ','.join(df.columns[column != 0]), axis=1)

So can anybody help me how to solve this problem ? I am looking for column with maximum value

silent_hunter
  • 2,224
  • 1
  • 12
  • 30

1 Answers1

1

Probably not optimal but it works, if there are multiple columns with the max value, the first column is what gets returned

df.apply(max, axis=0).idxmax()

yields

'store_B'
myz540
  • 537
  • 4
  • 7
  • 1
    `df[df==df.max().max()].dropna(axis=1, thresh=1).dropna()` would return all columns with the max value, returned trough: `max(df.max(axis=0))` and with `df.max(axis=0)` you would get the list with all maxima for each row (always good to check) – guardian Apr 15 '22 at 18:13