Home > Mobile >  how count repetitive unchanged signs of a column?
how count repetitive unchanged signs of a column?

Time:11-02

I have a column. how I can make a new column to count repetative positive and negative signs?

col1
-5
-3
-7
4
5
-0.5
6
8
9

col1        count_sign
-5              3
-3              3
-7              3
4               2
5               2
-0.5            1
6               3
8               3
9               3

the first 3 rows are 3 because we have 3 negative signs in the first 3 rows, then 2 positive signs and ....

CodePudding user response:

# identify the change of signs among rows,
# making count as NaN, where sign is same, else 1
df['count']=np.where(np.sign(df['col1']).diff().eq(0),
        np.nan,
        1) 
# cumsum to group the rows
df['count']=df['count'].cumsum().ffill()

# groupby to take count of each group of rows and return groupsize using transform
df['count']=df.groupby('count')['col1'].transform('size')
df
col1    count
0   -5.0    3
1   -3.0    3
2   -7.0    3
3   4.0     2
4   5.0     2
5   -0.5    1
6   6.0     3
7   8.0     3
8   9.0     3

To add a sign to the count values


df['count']=np.where(np.sign(df['col1']).diff().eq(0),
        np.nan,
        1) 
df['count']=df['count'].cumsum().ffill()
df['count']=df.groupby('count')['col1'].transform('size')*np.sign(df['col1'])
df

    col1    count
0   -5.0    -3.0
1   -3.0    -3.0
2   -7.0    -3.0
3    4.0     2.0
4    5.0     2.0
5   -0.5    -1.0
6    6.0     3.0
7    8.0     3.0
8    9.0     3.0
  • Related