Find max of two columns and populate with value in third column

user_9344585 0
Viewed 93

I have a pandas dataframe with 4 columns - A, B, C,D and E. I want create groups by column A and then find the max of column B and C and then populate E with the corresponding value present in D as shown in example below.

My expected output:

column A    column B    column C    column D    Column E
AA         1           a            12          15
AA         2           d            13          15
AA         3           b            14          15
AA         3           e            15          15
BB         1           c            15          17
BB         1           d            16          17
BB         2           b            17          17
BB         2           a            18          17
CC         1           a            11          0
CC         1           c            10          0
CC         1           b            11          0
CC         1           e            15          0
pandas-groupby pandas dataframe group-by python
1 Answers

You can sort the dataframe before the grouping, then select the first value from Column D:

df = df.sort_values(
    by=["column A", "column B", "column C", "column D"],
    ascending=[True, False, False, False],
)

df["Column E"] = df.groupby("column A")["column D"].transform("first")
print(df.sort_index())

Prints:

  column A  column B column C  column D  Column E
0       AA         1        a        12        15
1       AA         2        d        13        15
2       AA         3        b        14        15
3       AA         3        e        15        15
4       BB         1        c        15        17
5       BB         1        d        16        17
6       BB         2        b        17        17
7       BB         2        a        18        17
user_100359850
Related