Group by operation with condition as agg function

user_1853412 0
Viewed 59

I have a dataframe in this fashion

node touch1 touch2 touch3 touch4 touch5
A    Best   Mid    Mid     
A           Best   Worst  Worst

I want to have a groupby node based on a condition tree so that I will have in return

node touch1 touch2 touch3 touch4 touch5
A    Best   Best   Mid    Worst

Or basically if there is Best show Best, if not but there is Mid show Mid and if not but there is Worst show Worst.

I am trying something like

group_cols = ["touch1", "touch2", "touch3", "touch4", "touch5"]
output.groupby(group_cols).agg({'Best':lambda val: (val == "Best").any(),'Mid':lambda val: (val == "Mid").any(), 'Worst':lambda val: (val == "Worst").any()}).reset_index()

But cant make it work. I think I am missing something. Do you know how to do this?

pandas python
3 Answers

Using a mapping dictionary as suggested is the best approach.

import pandas as pd
mapping_dict = {'Best': 0, 'Mid': 1, 'Worst': 2, None: 3}
df = pd.DataFrame({
    "node": ["A", "A"],
    "touch1": ["Best", None],
    "touch2": ["Mid", "Best"],
    "touch3": ["Mid", "Worst"],
    "touch4": [None, "Worst"],
    "touch5": [None, None],
})
result = df.groupby('node').agg(lambda x: {value: key for key, value in mapping_dict.items()}[min(x.map(mapping_dict))])
print(result)

Gives :

     touch1 touch2 touch3 touch4 touch5
node                                   
A      Best   Best    Mid  Worst   None

Note that {value: key for key, value in mapping_dict.items()} is simply the invert (key: value becomes value: key) of mapping_dict and is used to retrieve the original encoding.

user_75234990

As J_H says in the comments, text labels by themselves are generally hard to work with. I'd suggest converting them to categorical first, and then selecting the highest ranked one in your aggregation.

To do this, first build the categories in order of smallest to largest:

categories = ["Worst", "Mid", "Best"]

Then, convert all of the columns that aren't node to this categorical type:

df = df.set_index("node")
df = df.apply(lambda x: pd.Categorical(x, categories=categories, ordered=True))

Now, if you groupby node, the aggregation can just take the largest value in each column:

df.groupby("node").max().reset_index()

This gives the desired result:

node touch1 touch2 touch3 touch4 touch5
A    Best   Best   Mid    Worst  NaN

Note: If you don't want to keep your data as categorical after this, you will need to convert it back with df = df.astype(str).

Data

df = pd.DataFrame({
    "node": ["A", "A"],
    "touch1": ["Best", None],
    "touch2": ["Mid", "Best"],
    "touch3": ["Mid", "Worst"],
    "touch4": [None, "Worst"],
    "touch5": [None, None],
})
user_87152780

Use key option in sort_values of pandas 1.1.0+

d = {'Best': 0, 'Mid': 1, 'Worst': 2, '': 3}
df_final = df.groupby('node').agg(lambda x: x.sort_values(key=lambda x: x.map(d))
                                             .head(1))

Out[600]:
     touch1 touch2 touch3 touch4 touch5
node
A      Best   Best    Mid  Worst
user_101892140
Related