adding values in new column based on string contains in another column

Question

I have DataFarame

    date        descriptions           Code
1. 1/1/2020     this is aPple          6546
2. 21/8/2019    this is fan for him    4478
3. 15/3/2020    this is ball of hockey 5577
4. 12/2/2018    this is Green apple    7899
5. 13/3/2002    this is iron fan       7788
6. 14/5/2020    this ball is soft      9991

I want to create a new column 'category' whose value would be if there is expression apple, fan, ball(capital or small letters) in the description column then value A001, F009, B099 respectively should be enter in the category column, required DataFrame would be.

    date        descriptions           Code   category
1. 1/1/2020     this is aPple          6546   A001
2. 21/8/2019    this is fan for him    4478   F009
3. 15/3/2020    this is ball of hockey 5577   B099
4. 12/2/2018    this is Green apple    7899   A001
5. 13/3/2002    this is iron fan       7788   F009
6. 14/5/2020    this ball is soft      9991   B099

Vishnudev Krishnadas · Accepted Answer · 2020-11-28 10:50:06Z

2

Use str.extract to get the substring from the string-based column

d = {'apple': 'A001', 'ball': 'B099', 'fan': 'F009'}

df['category'] = (
    df.descriptions
      .str.lower()
      .str.extract('(' + '|'.join(d.keys()) + ')')
      .squeeze().map(d)
)

edited Nov 28, 2020 at 10:50

answered Nov 28, 2020 at 6:19

Vishnudev Krishnadas

11k2 gold badges29 silver badges58 bronze badges

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

1 Comment

raheel Over a year ago

can you give answer now i have edited the question as the above method it does not work on the orignal DataFrame

sammywemmy · Accepted Answer · 2020-11-28 06:30:49Z

1

You can use numpy select, which allows for multiple conditional selection.

content = ["apple", "fan", "ball"]
condlist = [df.descriptions.str.lower().str.contains(letter) for letter in content]
choicelist = ["A001", "F009", "B099"]
df["category"] = np.select(condlist, choicelist)
df


    date    descriptions                Code    category
0   1/1/2020    this is aPple           6546    A001
1   21/8/2019   this is fan for him     4478    F009
2   15/3/2020   this is ball of hockey  5577    B099
3   12/2/2018   this is Green apple     7899    A001
4   13/3/2002   this is iron fan        7788    F009
5   14/5/2020   this ball is soft       9991    B099

answered Nov 28, 2020 at 6:30

sammywemmy

28.9k4 gold badges21 silver badges35 bronze badges

Collectives™ on Stack Overflow

adding values in new column based on string contains in another column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related