Create new dataframe column with 0 and 1 values according to given series

Question

I have a dataframe as show below

df = 
                     value
2014-05-21 10:00:00    0.0
2014-05-21 11:00:00    3.4
2014-05-21 12:00:00    nan
2014-05-21 13:00:00    0.0
2014-05-21 14:00:00    nan
2014-05-21 15:00:00    1.0
..............

I would like to add two columns,

first one named "active" to switch the value to 1 (if df.value >=0 )and 0 (if df.value = nan), and the second one "unactive" to switch the value to 0 (if df.value >=0 )and -1 (if df.value = nan),so the new dataframe would be like

df_new = 
                     value   active  unactive
2014-05-21 10:00:00    0.0        1         0
2014-05-21 11:00:00    3.4        1         0
2014-05-21 12:00:00    nan        0        -1
2014-05-21 13:00:00    0.0        1         0
2014-05-21 14:00:00    nan        0        -1
2014-05-21 15:00:00    1.0        1         0
............

I try to use for loop, but it takes too much time when the time series is long. Does anyone know a better way to do it ? thanks for advance!

So if df.value < 0 and is not nan, what do you want 'active' to be? 1,-1, don't-care? — smci, Apr 20 '18 at 21:36
@smci good question, in the question I don't have negative number, but actually I want to point the value (>=0) equal to (=! nan). — Chi, Apr 20 '18 at 22:03

user3483203 · Answer 1 · 2018-04-20T22:06:11.823

You may use df.value >= 0 and use astype(int):

In [44]: df['active'], df['inactive'] = (df.value >= 0).astype(int), -(~(df.value >= 0)).astype(int)

In [45]: df
Out[45]:
                     value  active  inactive
2014-05-21 10:00:00    0.0       1         0
           11:00:00    3.4       1         0
           12:00:00    NaN       0        -1
           13:00:00    0.0       1         0
           14:00:00    NaN       0        -1
           15:00:00    1.0       1         0

score 1 · Answer 2 · edited Apr 20 '18 at 22:15

1

df['active'] = df['value'].notnull().astype(int)

and:

df['unactive'] = -df['value'].isnull().astype(int)

(Also you didn't specify what 'active' should be when df.value < 0 and is not nan. Should 'active' be? 1,-1, don't-care?)

edited Apr 20 '18 at 22:15

user3483203

50,081
9
65
94

answered Apr 20 '18 at 21:33

smci

32,567
20
113
146

1

@smci the output is not what OP asked for actually (im not the down voter by the way) – rafaelc Apr 20 '18 at 21:41
@RafaelC it is now actually. Again, comments are supposed to be constructive. It was trivial to convert to int. The hard part was formulating the expression. – smci Apr 20 '18 at 21:42
2

`notnull`==`~isnull`.... – BENY Apr 20 '18 at 21:44
3

Are you really arguing semantics when you had an *incorrect* answer that raised an error, and instead of downvoting, a user spent the time to explain why it would return an error? – user3483203 Apr 20 '18 at 21:54
This is an interesting part you mentioned... actually I would like to make new column "unactve" and switch the value to 0 (if df.value >=0 )and -1 (if df.value = nan), how can I write it ? – Chi Apr 20 '18 at 21:54
@Chi if you edit that second question into your question above, I can answer – smci Apr 20 '18 at 21:56
I added the second answer, but in general chrisz is right, you should open a new question. But this was a trivial variation of the existing one. – smci Apr 20 '18 at 22:02
@smci I saw it, however it is a smart way, thanks :) – Chi Apr 20 '18 at 22:06

Create new dataframe column with 0 and 1 values according to given series

2 Answers2