Create a feature in pandas based on another column

Question

I have the following dataset:

data = {'ROC_9':  [0.006250, 0.087230, 0.045028, 0.165738, -0.006993, -0.432736, -0.11162, 0.057466, 0.203138, -0.008234]}

price_data = pd.DataFrame (data)

It is an indicator about a stock price, namely rate of change.

I want to write a code that creates a new feature (column) on the pandas data frame when a current feature on the pandas data frame goes from positive to negative, or vice versa.

It is easier explained through an example: lets use the feature ROC9.

I create a new variable called ROC9_signal and set it equal to 0:

`price_data['ROC9_signal'] = 0`

When ROC_9 goes from negative to positive, I want to change the ROC9_signal from 0 to 1.

When ROC_9 goes from positive to negative, I want to change the ROC9_signal from 0 to -1.

Looking at the data, I would like ROC9_signal to change from 0 to -1, since the value has gone from 0.16 (positive) to -0.006 (negative).

Looking at the data, I would like ROC_9 signal to change from 0 to 1, since the value has gone from -0.11 (negative) to 0.05 (positive).

Looking at the data, I would like ROC9_signal to change from 0 to -1, since the value has gone from 0.20 (positive) to -0.008 (negative).

It is only the row where the change happens that I want to change from 0 to 1 or 0 to -1, the other rows must remain at 0.

I will then apply this same logic to create a momentum10_signal column and a chalkin_money_flow_signal column. Therefore I want a solution that can be applied to different columns and not manually.

Thanks in advance for the help.

This is what the full data looks like:

Full Data

Please provide a small set of sample data in the form of text that we can copy and paste. Include the corresponding desired result. Check out the guide on [how to make good reproducible pandas examples](https://stackoverflow.com/a/20159305/3620003). — timgeb, Jun 01 '20 at 16:53
Apologies. I have provided some sample data now. Thanks @timgeb — alitkie, Jun 01 '20 at 18:13

score 0 · Answer 1 · answered Jun 01 '20 at 22:13

Try using np.where:

First create a column of 'ROC9_prev' using shift:

price_data['ROC9_prev'] = price_data['ROC9'].shift(1)

You may need to groupby a column before shifting in order to ensure that you're taking each stock's previous ROC9 as opposed to a different stock's current ROC9.

Then creating the signal column will follow like this:

price_data['ROC9_Signal'] = np.where(price_data['ROC9'] > 0 & price_data['ROC9_prev'] < 0, 1, 0)
price_data['ROC9_Signal'] = np.where(price_data['ROC9'] < 0 & price_data['ROC9_prev'] > 0, -1, 0)

Hope this helps.

wwnde · Answer 2 · 2020-06-01T23:17:14.503

Data

data = {'ROC_9':  [0.006250, 0.087230, 0.045028, 0.165738, -0.006993, -0.432736, -0.11162, 0.057466, 0.203138, -0.008234]}

price_data = pd.DataFrame (data)
price_data['ROC9_signal'] = 0
price_data

Use Boolean selection to find the two different crossings:

 ZeroCrossing1=price_data.ROC_9.ge(0)&price_data.ROC_9.shift(1).le(0)#from 0 to 1
 ZeroCrossing2=price_data.ROC_9.ge(0)&price_data.ROC_9.shift(-1).le(0)# from 0 to -1.

Put the Zerocrossing likely outcomes into a list

 ZeroCrossingOutcomes=[1,-1]

Conditionally calculate price_data['ROC9_signal'] using np.where

price_data['ROC9_signal']=np.select([ZeroCrossing1,ZeroCrossing2],ZeroCrossingOutcomes,0)
print(price_data)

Alternatively use nested np.where(if condition, ye, else no)

price_data['ROC9_signal']=np.where(ZeroCrossing1,1,np.where(ZeroCrossing2,-1,0)))

Thank you for the help! This works very nicely. Appreciate it! — alitkie, Jun 02 '20 at 07:13

Create a feature in pandas based on another column

2 Answers2