CODEWITHSUNDEEP
pythonpandas
Pythonista
Pythonista

Reputation: 79

Create a new column based on the values of the column by ID

Please help me solve this problem. I searched online but couldn’t find the solution. 

df

Id    Fruit
1     Apple
1     Carrot
2     Apple
3     Carrot

list = [fruit, vegetable, both]

The question is, how to assign for each id, if id’s fruit column contains apple or carrot the list. If id contains apple then assign ID 1 fruit, if carrot only, then vegetable, if both then both.

I tried to use str.contains by row, but it doesn’t take ID column.

Result

Newdf

Id    Fruit    New_col
1     Apple    Both
1     Carrot   Both
2     Apple    Fruit
3     Carrot   Vegetable

Thank you a lot.

Upvotes: 1

Views: 32

Answers (1)

BENY
BENY

Reputation: 323376

Doing map first , then we just using transform nunqiue finding the the position should be 'both'

s=df.Fruit.map({'Apple':'Fruit','Carrot':'Vegetable'})
mask=s.groupby(df.Id).transform('nunique').eq(2)
s[mask]='both'
df['New']=s
df
Out[190]: 
   Id   Fruit        New
0   1   Apple       both
1   1  Carrot       both
2   2   Apple      Fruit
3   3  Carrot  Vegetable

Upvotes: 1

Related Questions