Search code examples
pythonpandasstock

Comparing the row values of a stock price column in a Dataframe with lots of conditions

Our problem statement looks like:

np.random.seed(0)
df = pd.DataFrame({'Close': np.random.uniform(0, 100, size=10)})

This is sample data taken,other actual data is of a company's stock price

  Close         change    
0   54.881350   NaN
1   71.518937   16.637586
2   60.276338   -11.242599
3   54.488318   -5.788019
4   42.365480   -12.122838

We have assinged a threshold with a range(0-1)

First, diff in change in index 1 and index 2 value are compared with threshold value,

  • if result is positive and greater than threshold, then assign = 1
  • if result is negative and less than threshold, then assign = -1
  • if result is within the range of threshold, then assign = 0

Same will be done for index 2 and index 3, and then index 3 and index 4

Now say the results are, final result will be through majority of voting:

index 1&2       index 2&3    index 3&4     Majority of voting

     1               0             1             1

Exception

  • if results are 1, 0, -1 then the result would be 0

Now, the final result by majority of voting will be assigned to a new column at index 0, and so on.

EXPECTED RESULT(example)

    Close      change          Result
0   54.881350   NaN              0
1   71.518937   16.637586        1
2   60.276338   -11.242599      -1
3   54.488318   -5.788019        1
4   42.365480   -12.122838      -1

I tried few times, but couldn't figure out how it will finally executed.

Solution

  • np.select is what you are looking for:

    lbound, ubound = 0, 1
change = df["Close"].diff()

df["Change"] = change
df["Result"] = np.select(
    [
        # The exceptions. Floats are not exact. If they are "close enough" to
        # 1, we consider them to be equal to 1, etc.
        np.isclose(change, 1) | np.isclose(change, 0) | np.isclose(change, -1),
        # The other conditions
        (change > 0) & (change > ubound),
        (change < 0) & (change < lbound),
        change.between(lbound, ubound),
    ],
    [0, 1, -1, 0],
)