Change the value of a pandas dataframe column based on a condition ,also depending on other columns of the dataframe

Question

    Category              DishName   Id 
0   a                     Pistachio  621f4884e48bc60012364b13   
1   a                     Pistachio  621f4884e48bc60012364b13   
2   a                     Pistachio  621f4884e48bc60012364b13   
3   a                     achar      621f4884e48bc60012364b13   
4   b                     achar      621f4884e48bc60012364b13   
5   b                     achar      621f4884e48bc60012364b13   
6   a                     chicken    621f4884e48bc60012364b13   
7   b                     chicken    621f4884e48bc60012364b13   
8   c                     chicken    621f4884e48bc60012364b13

My dataframe has 3 columns category, dishname and id. Considering the id and the dishname I have to assign category.

Assign "a" if all the category values are "a"

Assign "b" if category values are "a","b"

Assign "c" if category values are "a","b","c"

Expected output is

    Category              DishName   Id 
0   a                     Pistachio  621f4884e48bc60012364b13   
1   a                     Pistachio  621f4884e48bc60012364b13   
2   a                     Pistachio  621f4884e48bc60012364b13   
3   b                     achar      621f4884e48bc60012364b13   
4   b                     achar      621f4884e48bc60012364b13   
5   b                     achar      621f4884e48bc60012364b13   
6   c                     chicken    621f4884e48bc60012364b13   
7   c                     chicken    621f4884e48bc60012364b13   
8   c                     chicken    621f4884e48bc60012364b13

mozway · Accepted Answer · 2022-07-09 07:24:44Z

2

You can transform to ordered Categorical and get the max per group:

df['Category'] = (pd
                  .Series(pd.Categorical(df['Category'],
                                         categories=['a', 'b', 'c'], ordered=True),
                          index=df.index)
                  .groupby(df['DishName'])
                  .transform('max')
                  )

NB. You wouldn't need the categorical for simply a, b, c, as those three are lexicographically sorted, but I imagine a real life case wouldn't necessarily be. As example low < medium < high is logically but not lexicographically sorted.

Output:

  Category   DishName                        Id
0        a  Pistachio  621f4884e48bc60012364b13
1        a  Pistachio  621f4884e48bc60012364b13
2        a  Pistachio  621f4884e48bc60012364b13
3        b      achar  621f4884e48bc60012364b13
4        b      achar  621f4884e48bc60012364b13
5        b      achar  621f4884e48bc60012364b13
6        c    chicken  621f4884e48bc60012364b13
7        c    chicken  621f4884e48bc60012364b13
8        c    chicken  621f4884e48bc60012364b13

edited Jul 9, 2022 at 7:24

answered Jul 9, 2022 at 7:19

mozway

267k13 gold badges56 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mila Over a year ago

What if i need to consider the column Id also in groupby? ie both Id and DishName

Mila Over a year ago

I tried the same, but giving me key error

mozway Over a year ago

My bad, groupby([df['DishName'], df ['Id']])

BeRT2me · Accepted Answer · 2022-07-09 07:19:34Z

0

df['Category'] = df.groupby('DishName')['Category'].transform('max')

Output:

  Category   DishName                        Id
0        a  Pistachio  621f4884e48bc60012364b13
1        a  Pistachio  621f4884e48bc60012364b13
2        a  Pistachio  621f4884e48bc60012364b13
3        b      achar  621f4884e48bc60012364b13
4        b      achar  621f4884e48bc60012364b13
5        b      achar  621f4884e48bc60012364b13
6        c    chicken  621f4884e48bc60012364b13
7        c    chicken  621f4884e48bc60012364b13
8        c    chicken  621f4884e48bc60012364b13

answered Jul 9, 2022 at 7:19

BeRT2me

13.3k2 gold badges18 silver badges39 bronze badges

4 Comments

mozway Over a year ago

As I detailed in my answer, this likely works here by "chance". OP probably has more complex categories, not necessarily lexicographically sorted, in the real life data.

BeRT2me Over a year ago

I agree, yours is the more robust method, mine just happens to work in this specific case. @mozway

mozway Over a year ago

Maybe I'm wrong and this is really a/b/c :p

BeRT2me Over a year ago

You never know with these :')

Collectives™ on Stack Overflow

Change the value of a pandas dataframe column based on a condition ,also depending on other columns of the dataframe

2 Answers 2

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related