Counting Days where numbers turn from positive to negative

Question

I have the following DataFrame:

macd_hist
Out[10]: 
            ADANIPORTS.NS  ASIANPAINT.NS  ...  WIPRO.NS   ZEEL.NS
Date                                      ...                    
2015-06-22            NaN            NaN  ...       NaN       NaN
2015-06-23            NaN            NaN  ...       NaN       NaN
2015-06-24            NaN            NaN  ...       NaN       NaN
2015-06-25            NaN            NaN  ...       NaN       NaN
2015-06-26            NaN            NaN  ...       NaN       NaN
                  ...            ...  ...       ...       ...
2020-06-12      -0.064481       1.635353  ...  0.213215 -1.800832
2020-06-15      -0.702969       0.135702  ... -0.096160 -3.020285
2020-06-16      -1.125824      -0.567845  ... -0.438076 -3.804984
2020-06-17      -1.423891      -2.635996  ... -0.347506 -4.095071
2020-06-18      -1.497237      -3.613468  ... -0.312098 -3.520918

[1227 rows x 50 columns]

How can i count the number of days where the number had turned from positive to negative for each ticker column. So if the number was positive yesterday and it turns negative today, that would be 1 but it should not count until it goes negative again and turns positive and then turns negative again, that would be another count.

What i am trying to count is this:

Hugolmn · Accepted Answer · 2020-06-19T06:06:47.760

1

If I get it right, you can try the following:

((macd_hist > 0).astype(int).diff() > 0).sum()

Let's break it down. It will do the following :

(macd_hist >= 0) : to check if your number is positive or not
.astype(int) : to convert into integers
.diff(): to detect changes (-1 for pos to neg, else 1)
< 0 : to only keep the changes from pos to neg
.sum() : to count the number of such changes

edited Jun 19 '20 at 06:06

answered Jun 18 '20 at 21:31

Hugolmn

1,530
1
7
20

score 1 · Answer 2 · answered Jun 18 '20 at 21:51

1

You can use enumerate():

d = [-1,-2,-1,1,2,4,1,-1,-2,-4,-1,3,4,5,2,-2,-3,-1,3,4,3,1,-1,-3,-2,-1,2,3,4]

count = 0
for i,n in enumerate(d):
    if i < len(d)-1 and d[i] > 0 and d[i+1] < 0:
        count += 1
print(count)

Output:

answered Jun 18 '20 at 21:51

Red

26,798
7
36
58

While this works, it's not so suitable when using pandas. One primary rule is to avoid manually writint for-loops – Hugolmn Jun 19 '20 at 06:08

wwnde · Answer 3 · 2020-06-19T11:09:35.467

I would tally zero crossing,. Attempted in my solution below but there is not enough for me to tally. There is only one zero crossing in each ticker. My logic was, get zerocrossing from each ticker and allocate it 1 else zero. cumsum and cumcount

Part1

 #Zerocrossing

a=df.ZEEL.lt(0)
c1=a.ne(a.shift(1))

b=df.WIPRO.lt(0)
c2=b.ne(b.shift(1))

c=df.ASIANPAINT.lt(0)
c3=c.ne(c.shift(1))

d=df.ADANIPORTS.lt(0)
c4=d.ne(d.shift(1))



 Attribute in columns

df['ADANIPORTSZC']=np.where(c4,1,0)
df['ASIANPAINTZC']=np.where(c3,1,0)
df['WIPROZC']=np.where(c2,1,0)
df['ZEELZC']=np.where(c1,1,0)




 Date  ADANIPORTS  ASIANPAINT     WIPRO      ZEEL  ADANIPORTSZC  \
0  2015-06-22    0.000000    0.000000  0.000000  0.000000             1   
1  2015-06-23    0.000000    0.000000  0.000000  0.000000             0   
2  2015-06-24    0.000000    0.000000  0.000000  0.000000             0   
3  2015-06-25    0.000000    0.000000  0.000000  0.000000             0   
4  2015-06-26    0.000000    0.000000  0.000000  0.000000             0   
5  2020-06-12   -0.064481    1.635353  0.213215 -1.800832             1   
6  2020-06-15   -0.702969    0.135702 -0.096160 -3.020285             0   
7  2020-06-16   -1.125824   -0.567845 -0.438076 -3.804984             0   
8  2020-06-17   -1.423891   -2.635996 -0.347506 -4.095071             0   
9  2020-06-18   -1.497237   -3.613468 -0.312098 -3.520918             0   

   ASIANPAINTZC  WIPROZC  ZEELZC  
0             1        1       1  
1             0        0       0  
2             0        0       0  
3             0        0       0  
4             0        0       0  
5             0        0       1  
6             0        1       0  
7             1        0       0  
8             0        0       0  
9             0        0       0

If all wanted is zerocrossing for the tickers. This one line of code could do. Basically slice the rows with tickers, boolean select and convert boolean to integerg

df.iloc[:,1::].apply(lambda x:x.le(0).ne(x.le(0).shift(1))).astype(int)



     ADANIPORTS  ASIANPAINT  WIPRO  ZEEL
0           1           1      1     1
1           0           0      0     0
2           0           0      0     0
3           0           0      0     0
4           0           0      0     0
5           0           1      1     0
6           0           0      1     0
7           0           1      0     0
8           0           0      0     0
9           0           0      0     0

Counting Days where numbers turn from positive to negative

3 Answers3