0
$\begingroup$

I want to select rows by the maximum values of another column which would be the duplicated rows containing duplicated maximum values of a group.

This should contain three steps:
(1) group dataframe by column A;
(2) get duplicated rows with duplicated maximum values of column B;
(3) get rows if it contains maximum values of column C (if it is still duplicated, pick the first).

Example:

df_test = pd.DataFrame({'A':[1,1,2,3,4,2,4,3,3,2],
                        'B':[3,3,2,4,5,2,5,3,4,3],
                    'C':[80,85,88,90,70,83,85,90,90,70]})

enter image description here

df_result=pd.DataFrame({'A':[1,2,3,4],
                        'B':[3,3,4,5],
                    'C':[85,70,90,85]})

enter image description here

$\endgroup$

1 Answer 1

1
$\begingroup$

This is more of a programming than a data science question, and would therefore be better suited for stackoverflow, but this can be achieved relatively easily using a combination of sorting and grouping:

(
    df
    # sort such that the first row is within each group is the one you want
    .sort_values(["A", "B", "C"], ascending=[True, False, False])
    # group based on column A
    .groupby("A")
    # select the first row within each group
    .first()
    # reset the index such that A is a column instead of the index
    .reset_index()
)

Which gives the following result:

A B C
1 3 85
2 3 70
3 4 90
4 5 85
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.