Finding maximum value of column, and returning value in another column

Question

I have a dataframe like this:

   Allotment  NDII Percent  NDII Value  NDVI Percent  NDVI Value
0    Arnston        0.0550   -0.199448           NaN         NaN
1    Arnston        0.0000   -0.198036           NaN         NaN
2    Arnston        0.0000   -0.196623           NaN         NaN
3    Arnston        0.0000   -0.195211           NaN         NaN
4    Arnston        0.0000   -0.193799           NaN         NaN
5    Arnston        0.0275   -0.192386           NaN         NaN
6    Arnston        0.0550   -0.190974           NaN         NaN
7    Arnston        0.0000   -0.189561           NaN         NaN
8    Arnston        0.0275   -0.188149           NaN         NaN
9    Arnston        0.1926   -0.186737           NaN         NaN
10      Anex        0.0275   -0.185324           NaN         NaN
11      Anex        0.0275   -0.183912           NaN         NaN
12      Anex        0.1376   -0.182499           NaN         NaN
13      Anex        0.0000   -0.181087           NaN         NaN
14      Anex        0.1100   -0.179675           NaN         NaN
15      Anex        0.0550   -0.178262           NaN         NaN
16      Anex        0.0000   -0.176850           NaN         NaN
17      Anex        0.0275   -0.175437           NaN         NaN
18      Anex        0.1100   -0.174025           NaN         NaN
0    Arnston           NaN         NaN        5.0338    0.000000
1    Arnston           NaN         NaN        1.0000    0.002235
2    Arnston           NaN         NaN        0.0200    0.004469
3    Arnston           NaN         NaN        0.1000    0.006704
4    Arnston           NaN         NaN        2.0000    0.008939
5    Arnston           NaN         NaN        1.0000    0.011173
6    Arnston           NaN         NaN        2.0000    0.013408
7    Arnston           NaN         NaN        0.0000    0.015643
8    Arnston           NaN         NaN        0.0000    0.017877
9    Arnston           NaN         NaN        0.0000    0.020112
10   Arnston           NaN         NaN        0.0000    0.022346
11      Anex           NaN         NaN        1.0000    0.024581
12      Anex           NaN         NaN        3.0000    0.026816
13      Anex           NaN         NaN        5.0000    0.029050
14      Anex           NaN         NaN        0.0000    0.031285
15      Anex           NaN         NaN        0.0000    0.033520
16      Anex           NaN         NaN        0.0000    0.035754
17      Anex           NaN         NaN        0.0000    0.037989
18      Anex           NaN         NaN        0.0000    0.040224
19      Anex           NaN         NaN        0.0000    0.042458
20      Anex           NaN         NaN        0.0000    0.044693
21      Anex           NaN         NaN        0.0000    0.046928
22      Anex           NaN         NaN        0.0000    0.049162
23      Anex           NaN         NaN        0.0000    0.051397
24      Anex           NaN         NaN        0.0000    0.053631
25      Anex           NaN         NaN        0.0000    0.055866
26      Anex           NaN         NaN        0.0000    0.058101

and I want to find the Maximum NDII Value and its associated NDII Percent for each Allotment and the maximum NDVI Value and its associated NDVI Percent for each Allotment. So I know I will need to use a groupby on the Allotment first, but that is where I am getting stuck.

My desired output is:

Allotment    NDII Percent NDII Value   NDVI Percent   NDVI Value
Arnston      0.1926       -0.186737    0.0000         0.022346
Anex         0.1100       -0.174025    0.0000         0.058101

Fabio Lamanna · Accepted Answer · 2015-10-12 17:54:45Z

1

Maybe there's a more straight and elegant way to do this but, let df be your original dataframe:

df1 = df.iloc[df.groupby(['Allotment'])['NDII Value'].apply(lambda x: x.idxmax())]
df2 = df.iloc[df.groupby(['Allotment'])['NDVI Value'].apply(lambda x: x.idxmax())]

Then I'm selecting only the columns I need:

d1 = df1[['Allotment','NDII Value','NDII Percent']]
d2 = df2[['Allotment','NDVI Value','NDVI Percent']]

and merge:

d = d1.merge(d2,how='outer')

This returns:

  Allotment  NDII Value  NDII Percent  NDVI Value  NDVI Percent
0      Anex   -0.174025        0.1100    0.058101             0
1   Arnston   -0.186737        0.1926    0.022346             0

Hope that helps.

edited Oct 12, 2015 at 17:54

answered Oct 12, 2015 at 17:43

Fabio Lamanna

21.7k24 gold badges95 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Finding maximum value of column, and returning value in another column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related