Steven G - 1 year ago 50

Python Question

I am trying to clean up a code where I have a dataframe such has:

`df = pd.DataFrame({'value': {'2016-09-21': 13.30,`

'2016-09-22': 12.02,

'2016-09-23': 12.28,

'2016-09-26': 14.5,

'2016-09-27': 13.1,

'2016-09-28': 12.39,

'2016-09-29': 14.02}})

I have a ON and OFF signal based on levels. when 'value' cross upward 14.39 I want to have 1 until it cross 12.50 downward such has:

`df`

value sig

2016-09-21 13.3000 0

2016-09-22 12.0200 0

2016-09-23 12.2800 0

2016-09-26 14.5000 1

2016-09-27 13.1000 1

2016-09-28 12.3900 0

2016-09-29 14.0200 0

I am approching the problem through a loop but I am pretty sure there's a better way to do it. here is my approach:

`off, on, sig = 14.39, 12.50, 0`

log = []

for level in df.itertuples():

if level.value > off:

sig = 1

elif (sig == 1) & (level.value < on):

sig = 0

log.append([level.value, sig])

log = pd.DataFrame(log, index=df.index, columns=['value', 'sig'])

Answer Source

Here is a vectorized solution with `pandas.Series.where`

method:

```
import numpy as np
ON, OFF = 14.39, 12.50
df['sig'] = 0 # set the initial value to be 0
df['sig'] = (df.sig.where(df.value < ON, 1) # if value > ON, set it 1
.where((df.value < OFF) | (df.value > ON), np.nan)
# if value < ON, and value > OFF, set it nan
.ffill().fillna(0)) # forward fill the nan value as they depend
# on their previous state, and fill initial
# value as 0
df
# value sig
#2016-09-21 13.30 0
#2016-09-22 12.02 0
#2016-09-23 12.28 0
#2016-09-26 14.50 1
#2016-09-27 13.10 1
#2016-09-28 12.39 0
#2016-09-29 14.02 0
```

A similar `np.where()`

method with maybe clearer intention：

```
import numpy as np
df['sig'] = np.where(df.value > ON, 1, np.where(df.value < OFF, 0, np.nan))
df['sig'] = df.sig.ffill().fillna(0)
```