CODEWITHSUNDEEP
pythonpandas
PeterL
PeterL

Reputation: 515

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],
 [5903.75, -512.0],
 [5898.75, -141.0],
 [5897.5, -303.0],
 [5895.0, -107.0],
 [5893.25, 27.0]]

pd.DataFrame(data,columns=['A','B'])

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

What is fast pythonic way to make column “C” that counts how many rows is number in “B” negative? So the dataframe would look like:

    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

Upvotes: 2

Views: 3868

Answers (1)

Bharath M Shetty
Bharath M Shetty

Reputation: 30605

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 = 0
def count_neg(x):
    global count
    if x < 0:
        count+=1
    else :
        count = 0 
    return 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

Upvotes: 6

Related Questions