Maximum for each column, return value of other for max, create new dataframe of returns

Question

I hope the title is not misleading.

I need to go from this dataframe:

    Column_1      Columns_2  First  Second  Third
0  Element_1  to_be_ignored     10       5     77
1  Element_2  to_be_ignored     30      30     11
2  Element_3  to_be_ignored     60       7      3
3  Element_4  to_be_ignored     20      87     90

to:

   New_Column   New_Column_1  Max
0   Element_3          First   60
1   Element_4         Second   87
2   Element_4          Third   90

• get maximum value of every column
• get responding value of Column_1 for maximum value
• transform to new dataframe

what i got so far:

data = {'Column_1': ['Element_1', 'Element_2', 'Element_3', 'Element_4'],
        'Columns_2': ['to_be_ignored', 'to_be_ignored', 'to_be_ignored', 'to_be_ignored'],
        'First': [10,30,60,20], 'Second': [5,30,7,87], 'Third': [77,11,3,90]}

df = pd.DataFrame(data)

df.loc[df.iloc[:, 1:].idxmax(), ['Column_1']

so i am able to get the index position and value for the maximum in the columns.

2    Element_3
3    Element_4
3    Element_4

Unfortunately i can't figure out the rest.

THX

Answer 1

IIUC melt then sort_values + drop_duplicates

df.melt(['Column_1','Columns_2']).sort_values('value').drop_duplicates(['variable'],keep='last')
     Column_1      Columns_2 variable  value
2   Element_3  to_be_ignored    First     60
7   Element_4  to_be_ignored   Second     87
11  Element_4  to_be_ignored    Third     90