Column contributing Maximum in pandas data frame

user_9213069 0
Viewed 46

I'm using python 3.x I have pandas data frame df. df looks like

Name Maths Science Social studies
abc   80     70      90
cde   90     60      80 
xyz   100    80      85
...
...

I would like to generate a pandas data frame which will store student name, maximum marks & the subject contributed maximum marks. If maximum marks is 100 then it will consider next highest instead of 100. So my output data frame will look like

Name Highest_Marks Subject_contributed_Max
abc   90              Social Studies 
cde   90              Maths 
xyz   85              Social Studies

Can you suggest me how to do it?

pandas python
3 Answers

You can use:

df2 = df.drop(columns='Name').mask(df.eq(100))

df['Highest_Marks'] = df2.max(axis=1)
df['Subject_contributed_Max'] = df2.idxmax(axis=1)

output:

  Name  Maths  Science  Social studies  Highest_Marks Subject_contributed_Max
0  abc     80       70              90           90.0          Social studies
1  cde     90       60              80           90.0                   Maths
2  xyz    100       80              85           85.0          Social studies

For efficiency, avoiding computing twice the max/idxmax, you can compute the idxmax and use a lookup

s = (df
 .drop(columns='Name')
 .mask(df.eq(100))
 .idxmax(axis=1)
)

idx, cols = pd.factorize(s)
df['Highest_Marks'] = df.reindex(cols, axis=1).to_numpy()[np.arange(len(df)), idx]
df['Subject_contributed_Max'] = s
user_163434640

This will work

df_melt = df.melt('Name')
df_melt = df_melt.loc[df_melt['value'] < 100]
df_melt['RN'] = df_melt.sort_values(['value'], ascending=False).groupby(['Name']).cumcount() + 1
df_melt.loc[df_melt['RN'] == 1].sort_values('Name')
user_177504310

This will do it:

df = df.set_index('Name').stack().reset_index().rename(columns={
    'level_1':'Subject_contributed_Max', 0:'Highest_Marks'}).sort_values(
    ['Name','Highest_Marks'])
df = df[df['Highest_Marks'] != 100].groupby('Name').last().reset_index()[[
    'Name', 'Highest_Marks', 'Subject_contributed_Max']]

Input:

  Name  Maths  Science  Social studies
0  abc     80       70              90
1  cde     90       60              80
2  xyz    100       80              85

Output:

  Name  Highest_Marks Subject_contributed_Max
0  abc             90          Social studies
1  cde             90                   Maths
2  xyz             85          Social studies

UPDATE:

Here is a faster approach than my original answer above. It's similar to one of the suggestions in @mozway's answer, though it uses where() instead of mask() (and it also only returns the high marks, not the individual columns of marks).

    df2 = df.set_index('Name')
    df2 = df2.where(df2 < 100)
    df2 = df2.assign(**{'Highest_Marks':df2.max(axis=1).astype(int), 
        'Subject_contributed_Max':df2.idxmax(axis=1)}).reset_index()[[
        'Name', 'Highest_Marks', 'Subject_contributed_Max']]

Prompted by a comment by OP, I ran some benchmarks on answers by @mozway and me (I also tried adding the answer by @ArchAngelPwn, but it doesn't seem to give comparable output in its current form).

Here are the results for a 1000 row by 9000 column dataframe:

Timeit results:
foo_1 (orig) ran in 2.4102477666456252 seconds using 3 iterations
foo_2 (refined) ran in 2.256996333327455 seconds using 3 iterations
foo_3 (where) ran in 1.1588773333545153 seconds using 3 iterations
foo_4 (mozway mask) ran in 1.17148056665125 seconds using 3 iterations
foo_5 (mozway mask lookup) ran in 1.1049298333236948 seconds using 3 iterations
user_181354540
Related