CODEWITHSUNDEEP
pythonpandas
sp1r0u
sp1r0u

Reputation: 23

How to get value in pivot table based on conditions

given a dataframe

import pandas as pd
data = [[1, 10, -1], [1,  7, -2], [1, 3, 14], 
    [2, 11,  1], [2, -2,  2], [1, 13, 4]]
DfObj = pd.DataFrame (data, columns = ['A', 'B', 'C'])

enter image description here

and the pivot table

pivot = DfObj.pivot_table (index=['A', 'B'])

enter image description here

I'd like to get the corresponding C-value to the highest B-value for each A-value, i.e. 4 and 1.

4 is the C-value corresponding to the highest B-value in category A=1.

I assume B-values are unique within each category A.

thank you

Upvotes: 2

Views: 230

Answers (1)

yatu
yatu

Reputation: 88226

No need to pivot here. You can use sort_values and groupby.tail:

DfObj.sort_values(['A', 'B']).groupby('A').C.tail(1)

5    4
3    1
Name: C, dtype: int64

Or using last:

DfObj.sort_values(['A', 'B']).groupby('A').C.last()

Another approach could be to use GroupBy.idxmax:

DfObj.loc[DfObj.groupby('A').B.idxmax().values, 'C']

5    4
3    1
Name: C, dtype: int64

Upvotes: 1

Related Questions