I have below Dataframe how to find dedicated result. I have found multi row maximum with column A.
col_0 col_1
Caps Lower
A a 0 0.246490 2
1 -1.265711 5
2 -0.477415 6
3 -0.355812 1
4 -0.724521 2
b 0 -0.409198 1
1 -0.062552 1
2 -0.731789 9
3 1.131616 5
4 0.085248 3
B a 0 0.193948 7
1 2.010710 6
2 0.289300 4
3 0.305373 3
4 1.376965 4
b 0 0.210522 1
1 1.431279 3
2 -0.247171 1
3 0.899074 8
4 0.639926 1
result must be :
col_0 col_1 Caps A -0.731789 9 B 0.899074 8
How can I find maximum with col_1?
Advertisement
Answer
based on your desired example output, your question appears to be: how do I return the rows with the largest col_1 value per Caps group.
Your example input makes it look like 'Caps' and 'Lower' are your indices, but it’s a little easier if they are not indices and just regular columns.
I set up your data like this:
df = pd.DataFrame({'Caps': np.repeat(['A', 'B'], 10),
'Lower': list(np.repeat(['a', 'b'], 5)) * 2,
'col_0': [0.246490, -1.265711, -0.477415, -0.355812, -0.724521,
-0.409198, -0.062552, -0.731789, 1.131616, 0.085248,
0.193948, 2.010710, 0.289300, 0.305373, 1.376965,
0.210522, 1.431279, -0.247171, 0.899074, 0.639926],
'col_1': [2, 5, 6, 1, 2, 1, 1, 9, 5, 3, 7, 6, 4, 3, 4, 1, 3, 1, 8, 1]
})
[pandas.Series.idxmax][1] should help, which returns the indices of the rows with the largest col_1 per Caps group:
df.groupby('Caps')['col_1'].idxmax()
# Caps
# A 7
# B 18
# Name: col_1, dtype: int64
Plugging that in:
df.loc[df.groupby('Caps')['col_1'].idxmax()]
# Caps Lower col_0 col_1
# 7 A a -0.731789 9
# 18 B b 0.899074 8