Create a column based on value of another column using Pandas

Question

Please consider this data frame:

date         value      
-------------------
20201001      -100
20200202      200
20200303       0
...

I want to hav1e another very simple column: "Status"

if Value < 0 Then "Status" = -1

if Value > 0 Then "Status" = 1

if Value = 0 Then "Status" = 0

I wrote this code:

data['Status'] = (data['Value'] / math.fabs(data['Value'])) if data['Value'] != 0 else 0

but I got this error:

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

How to create this column without For loop. Thanks

Edit 1) I wrote the code this way but I got same error:

data['Status'] = (data['Value'] / math.fabs(data['Value'])) if data[data['Value'] != 0] else 0

jezrael · Accepted Answer · 2020-09-21 08:58:43Z

3

Use numpy.sign:

data['Status'] = np.sign(data['Value'])

If only integers use Series.clip:

data['Status'] = data['Value'].clip(lower=-1, upper=1)

Your solution working with list comprehension:

data['Status'] = [x / math.fabs(x) if x != 0 else 0 for x in data['Value']]
print (data)
       date  value  Status
0  20201001   -100    -1.0
1  20200202    200     1.0
2  20200303      0     0.0

Performance:

#300k rows
data = pd.concat([data] * 100000, ignore_index=True)

In [72]: %timeit data['Status'] = np.sign(data['Value'])
3.2 ms ± 57.8 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

In [73]: %timeit data['Status'] = data['Value'].clip(lower=-1, upper=1)
5.99 ms ± 49.3 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

In [74]: %timeit data['Status'] = [x / math.fabs(x) if x != 0 else 0 for x in data['Value']]
144 ms ± 483 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)

edited Sep 21, 2020 at 8:58

answered Sep 21, 2020 at 8:19

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

6 Comments

DooDoo Over a year ago

Thanks. Is there any way without numpy?

jezrael Over a year ago

@Arian - Then is possible use clip

DooDoo Over a year ago

thanks it works. But I want to know What's wrong with my code? Why I can't write condition that way? Thanks

jezrael Over a year ago

@Arian - If need performance with multiple if-else best is use numpy.select, but for -1,0,1 is best np.sign here

DooDoo Over a year ago

Honestly, I wanted to know what is wrong with my code that gives an error?

|

Collectives™ on Stack Overflow

Create a column based on value of another column using Pandas

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related