Pandas Count Sequence Of Negative Values In Column

I have values in Pandas Dataframe column “B” that can be positive or negative: data=[[5889.25, 738.0], [5896.5, 49.0], [5897.5, 130.0], [5899.5, -266.0], [5903.75, -126.0],

Solution 1:

You can use np.where to find the negative values then use groupby and cumcount()+1

data=[[5889.25, 738.0],
 [5896.5, 49.0],
 [5897.5, 130.0],
 [5899.5, -266.0],
 [5903.75, -126.0],
 [5903.75, -512.0],
 [5898.75, -141.0],
 [5897.5, -303.0],
 [5895.0, -107.0],
 [5893.25, 27.0]]

df = pd.DataFrame(data,columns=['A','B'])
df['C'] = np.where(df['B']>0,0,df.groupby(np.where(df['B']<0,0,df['B'])).cumcount()+1)

Output :

         A      B  C
0  5889.25  738.0  0
1  5896.50   49.0  0
2  5897.50  130.0  0
3  5899.50 -266.0  1
4  5903.75 -126.0  2
5  5903.75 -512.0  3
6  5898.75 -141.0  4
7  5897.50 -303.0  5
8  5895.00 -107.0  6
9  5893.25   27.0  0

If you want to create a sequence for every positive number you can write a function

count = 0defcount_neg(x):
    global count
    if x < 0:
        count+=1else :
        count = 0return count
df['C'] = df['B'].apply(count_neg)

Output :

        A      B  C
0  5889.25 -738.0  1
1  5896.50  -49.0  2
2  5897.50  130.0  0
3  5899.50 -266.0  1
4  5903.75 -126.0  2
5  5903.75 -512.0  3
6  5898.75 -141.0  4
7  5897.50 -303.0  5
8  5895.00 -107.0  6
9  5893.25   27.0  0