How to set one pandas dataframe column value based on a 2nd column's value

Question

Situation

I have a pandas dataframe dfwith a column sentiment_rating.

index	sentiment_rating
2022-03-20	.3
2022-03-21	-.4
2022-03-24	-.7
2022-03-28	.6
2022-03-31	.2

Goal

I'm trying to create a new column status who's value will be either positive if the sentiment score is .5 or greater, negative if -.5 or less, or neutral if between -.5 and .5.

What I've tried

I've installed the pandas DataFrame module, and using this apply method:

df['status'] = df['sentiment_rating'].apply(lambda x: 'Positive' if x <= .8 else 'Neutral' if x > -.5 or < .5 else 'Negative' if x < -.5)

Results

I'm getting an Error message of invalid syntax, which doesn't tell me much.

I don't have a clear understanding of the lambda function, and am not even sure if apply is the right way to accomplish my goal.

I've also tried testing with this on 2 dimensions: df['status'] = ['Positive' if x > '.5' else 'other' for x in df['sentiment_rating']], and that's returning Error message TypeError: '>' not supported between instances of 'float' and 'str'

Any input on my approach and what I'm doing wrong greatly appreciated. Thx

score 2 · Answer 1 · answered Apr 07 '22 at 04:53

2

You can use numpy.select:

>>> import numpy as np
>>> df['status'] = np.select(
        condlist = [df.sentiment_rating > 0.5, df.sentiment_rating < -0.5],
        choicelist = ['positive', 'negative'],
        default = 'neutral'
    )
>>> df
        index  sentiment_rating    status
0  2022-03-20               0.3   neutral
1  2022-03-21              -0.4   neutral
2  2022-03-24              -0.7  negative
3  2022-03-28               0.6  positive
4  2022-03-31               0.2   neutral

For lambda:

>>> df['status'] = df.sentiment_rating.apply(
        lambda x: 'positive' if x > 0.5 
                  else 
                      'negative' if x < -0.5 
                      else 'neutral'
    )

But this is unnecessary and slow.

answered Apr 07 '22 at 04:53

Sayandip Dutta

15,602
4
23
52

1

It' better to use map() method of pandas.Series while working with single column of a dataframe. – bmrs Apr 07 '22 at 05:18
1

@bmrs I am not sure I follow why `map` is better in this case. I would think it would be worse. – Sayandip Dutta Apr 07 '22 at 05:21
1

Execution time using map(custom_function) i.e without lambda function, is lesser compared to using numpy.select() . here the difference in execution time is negligible but for thousands of data it looks more efficient using map() method. And as you mentioned , lambda function performs the worst. – bmrs Apr 07 '22 at 05:47
1

@bmrs in my testing its `map` is 3 times slower than using `np.select` for 5000 rows, 8.5 times slower for 50000 rows. – Sayandip Dutta Apr 07 '22 at 07:34

score 1 · Accepted Answer · answered Apr 07 '22 at 04:56

You can extract the lambda function into separate function in order to make it more readable. You can use following,

def return_status(x):
    if x >= .5:
        return 'Positive'
    elif x <= -.5:
        return 'Negative'
    return 'Neutral'


df['status'] = df['rating'].apply(return_status)
print(df.head())

You will get following output

        index  rating    status
0  2022-03-20     0.3   Neutral
1  2022-03-21    -0.4   Neutral
2  2022-03-24    -0.7  Negative
3  2022-03-28     0.6  Positive
4  2022-03-31     0.2   Neutral

score 1 · Answer 3 · answered Apr 07 '22 at 04:58

This might be a bit longer than what you were trying, but it does the job:

def status(series):
  if series >= 0.5:
    return "Positive"
  elif series > -0.5 and series < 0.5:
    return "Neutral"
  return "Negative"

df["Status"] = df["Sentiments"].apply(status)

How to set one pandas dataframe column value based on a 2nd column's value

3 Answers3