Pandas: find column name and value with max (and second max) value for each row

Question

I have in input something that looks like DF1 (code to generate below), and would like in output something that looks like DF2. The idea is to find for each row the column name with the highest value in that row, the corresponding value, as well as the column name with the second highest value in that row, and also its corresponding value. Is there simple way to do this with pandas? ``` import pandas as pd DF1 = pd.DataFrame({'User' : pd.Series(["Line1","Line2","Line3", "Line4"], index=['1', '2','3','4']), 'Var1' : pd.Series([9,12,3,21], index=['1', '2','3','4']),'Var2' : pd.Series([8,16,3,2], index=['1', '2','3','4']),'Var3' : pd.Series([7,5,6,9], index=['1', '2','3','4']),'Var4' : pd.Series([10,13,20,20], index=['1', '2','3','4']),'Var5' : pd.Series([8,2,13,1], index=['1', '2','3','4']),'Var6' : pd.Series([4,4,7,11], index=['1', '2','3','4']),'Var7' : pd.Series([15,13,4,7], index=['1', '2','3','4'])}) DF1 DF2 = pd.DataFrame({'User' : pd.Series(["Line1","Line2","Line3", "Line4"], ind…

Accepted Answer

Not sure if this is the simplest way, but you can do this:

```
def top(x):
    x.set_index('User', inplace=True)
    df = pd.DataFrame({'Max1Name':[],'Max2Name':[],'Max1Value':[],'Max2Value':[]})
    df.index.name='User'
    df.loc[x.index.values[0],['Max1Name', 'Max2Name']] = x.sum().nlargest(2).index.tolist()
    df.loc[x.index.values[0],['Max1Value', 'Max2Value']] = x.sum().nlargest(2).values
    return df

DF1.groupby('User').apply(top).reset_index(level=1, drop=True).reset_index()
```

Which produces desired output:

```
    User Max1Name  Max1Value Max2Name  Max2Value
0  Line1     Var7         15     Var4         10
1  Line2     Var2         16     Var4         13
2  Line3     Var4         20     Var5         13
3  Line4     Var1         21     Var4         20
```

An easier way however is to do this:

```
DF1.groupby('User').apply(lambda x: x.set_index('User').sum().nlargest(2))
```

Which gives you top 2 by user:

```
    User       
Line1  Var7    15
       Var4    10
Line2  Var2    16
       Var4    13
Line3  Var4    20
       Var5    13
Line4  Var1    21
       Var4    20
dtype: int64
```

Pandas: find column name and value with max (and second max) value for each row

Solution

Problem