pandas series mark all the rows between two values

Question

I have a series ( a single col in a df) with 3 possible values:

Stable, Increase, Decresae

, and I want to mark all the areas between a Increase to the subsequent Decrease. So for the values:

Stable
Stable
Stable
Increase
Increase
Stable
Stable
Decrease
Stable
Increase
Stable
Decrease

I will get: -,-,-,+,+,+,+,-,-,+,+,- What is the best way to do so?

score 1 · Answer 1 · answered Jun 06 '23 at 13:29

1

A possible solution:

np.where
(s.where(s.eq('Increase') | s.eq('Decrease')).ffill().eq('Increase'),
 '+', '-')

Output:

array(['-', '-', '-', '+', '+', '+', '+', '-', '-', '+', '+', '-'],
      dtype='<U1')

answered Jun 06 '23 at 13:29

PaulS

21,159
2
9
26

score 1 · Answer 2 · answered Jun 06 '23 at 13:35

Use Series.replace to NaNs only Stable values, forward filling existing values here Increase and Decrease and compare by Increase for set values 0/- in numpy.where:

df['new'] = np.where(df['col'].replace({'Stable': np.nan}).ffill().eq('Increase'), '+','-')
print (df)
         col new
0     Stable   -
1     Stable   -
2     Stable   -
3   Increase   +
4   Increase   +
5     Stable   +
6     Stable   +
7   Decrease   -
8     Stable   -
9   Increase   +
10    Stable   +
11  Decrease   -

Intermediate:

print (df.assign(repl=df['col'].replace({'Stable': np.nan}),
                 ffill=df['col'].replace({'Stable': np.nan}).ffill(),
                 comp=df['col'].replace({'Stable': np.nan}).ffill().eq('Increase'),
                 out=np.where(df['col'].replace({'Stable': np.nan}).ffill().eq('Increase'), '+','-')))
         col      repl     ffill   comp out
0     Stable       NaN       NaN  False   -
1     Stable       NaN       NaN  False   -
2     Stable       NaN       NaN  False   -
3   Increase  Increase  Increase   True   +
4   Increase  Increase  Increase   True   +
5     Stable       NaN  Increase   True   +
6     Stable       NaN  Increase   True   +
7   Decrease  Decrease  Decrease  False   -
8     Stable       NaN  Decrease  False   -
9   Increase  Increase  Increase   True   +
10    Stable       NaN  Increase   True   +
11  Decrease  Decrease  Decrease  False   -

mozway · Accepted Answer · 2023-06-06T14:46:14.870

map True on Increase and False on Decrease, then ffill. Finally map the +/- with numpy.where:

s = df['col'].map({'Increase': True, 'Decrease': False}).ffill().fillna(False)
df['indicator'] = np.where(s, '+', '-')

As a one-liner:

df['indicator'] = np.where(df['col'].map({'Increase': True, 'Decrease': False})
                                    .ffill().fillna(False),
                           '+', '-')

Output:

         col indicator
0     Stable         -
1     Stable         -
2     Stable         -
3   Increase         +
4   Increase         +
5     Stable         +
6     Stable         +
7   Decrease         -
8     Stable         -
9   Increase         +
10    Stable         +
11  Decrease         -

Intermediates:

         col    map  ffill+fillna indicator
0     Stable    NaN         False         -
1     Stable    NaN         False         -
2     Stable    NaN         False         -
3   Increase   True          True         +
4   Increase   True          True         +
5     Stable    NaN          True         +
6     Stable    NaN          True         +
7   Decrease  False         False         -
8     Stable    NaN         False         -
9   Increase   True          True         +
10    Stable    NaN          True         +
11  Decrease  False         False         -

pandas series mark all the rows between two values

3 Answers3