CODEWITHSUNDEEP
pythondataframepandas-groupbyapply
AD94
AD94

Reputation: 43

Assign values to a column based on Min and Max of a particular group

I have 3 fields Cust_ID, Acc_No and Product like the table below enter image description here

I need to add a column 'Type' based on 'Product' value for each Cust_ID. If all the 'Product' values for a customer lie between range 'a' to 'm' or 'n' to 'z' then it should be labeled as 'Single' else 'Multiple' like in the below table enter image description here

I am trying to group by 'Cust_ID' and compare min and max value of 'Product' with range '<=m' and '>=n' but not able to implement it successfully. Any help will be appreciated, thanks in advance.

Upvotes: 1

Views: 215

Answers (1)

Andrej Kesely
Andrej Kesely

Reputation: 195543

You can use .groupby.transform + Series.between:

df["Type"] = df.groupby("Cust_ID")["Product"].transform(
    lambda x: np.where(
        x.between("a", "m").all() | x.between("n", "z").all(),
        "Single",
        "Multiple",
    )
)
print(df)

Prints:

   Cust_ID  Acc_No Product      Type
0        1     111       a    Single
1        1     112       b    Single
2        1     113       c    Single
3        2     221       a  Multiple
4        2     222       x  Multiple
5        2     223       y  Multiple
6        3     331       z    Single
7        3     332       x    Single

Upvotes: 1

Related Questions