pandas - Count streak of values higher/lower than current rows

Viewed 2218

I'm looking for a way to take a pandas Series and return new Series representing the number of prior, consecutive values that are higher/lower than each row in the Series:

a = pd.Series([30, 10, 20, 25, 35, 15])

...should output:

Value   Higher than streak  Lower than streak
30      0                   0
10      0                   1
20      1                   0
25      2                   0
35      4                   0
15      0                   3

This will allow someone to identify how important each "regional max/min" value is in a time series.

Thanks in advance.

5 Answers

Since you're looking backwards at the previous values to see if there are consecutive values, you're going to have to interact with indices somehow. This solution first looks at any values prior to the value at the current index to see if they are less than or greater than the value, and then sets any values to False where there was a False following it. It also avoids creating iterators over the DataFrame, which may speed up operations for larger datasets.

import pandas as pd
from operator import gt, lt

a = pd.Series([30, 10, 20, 25, 35, 15])

def consecutive_run(op, ser, i):
    """
    Sum the uninterrupted consecutive runs at index i in the series where the previous data
    was true according to the operator.
    """
    thresh_all = op(ser[:i], ser[i])
    # find any data where the operator was not passing.  set the previous data to all falses
    non_passing = thresh_all[~thresh_all]
    start_idx = 0
    if not non_passing.empty:
        # if there was a failure, there was a break in the consecutive truth values,
        # so get the final False position. Starting index will be False, but it
        # will either be at the end of the series selection and will sum to zero
        # or will be followed by all successive True values afterwards
        start_idx = non_passing.index[-1]
    # count the consecutive runs by summing from the start index onwards
    return thresh_all[start_idx:].sum()


res = pd.concat([a, a.index.to_series().map(lambda i: consecutive_run(gt, a, i)),
                 a.index.to_series().map(lambda i: consecutive_run(lt, a, i))],
       axis=1)
res.columns = ['Value', 'Higher than streak', 'Lower than streak']
print(res)

Result:

   Value  Higher than streak  Lower than streak
0     30                   0                  0
1     10                   1                  0
2     20                   0                  1
3     25                   0                  2
4     35                   0                  4
5     15                   3                  0
import pandas as pd
import numpy as np

value = pd.Series([30, 10, 20, 25, 35, 15])



Lower=[(value[x]<value[:x]).sum() for x in range(len(value))]
Higher=[(value[x]>value[:x]).sum() for x in range(len(value))]


df=pd.DataFrame({"value":value,"Higher":Higher,"Lower":Lower})

print(df)





      Lower  Higher  value
0       0      0     30
1       1      0     10
2       1      1     20
3       1      2     25
4       0      4     35
5       4      1     15

Edit: Updated to really count consecutive values. I couldn't come up with a feasible pandas solution so we are back to looping.

df = pd.Series(np.random.rand(10000))

def count_bigger_consecutives(values):
  length = len(values)
  result = np.zeros(length)
  for i in range(length):
    for j in range(i):
      if(values[i]>values[j]):
        result[i] += 1
      else:
        break
  return result

%timeit count_bigger_consecutives(df.values)
1 loop, best of 3: 365 ms per loop

If performance is a concern for you it is possible to archive speedup with numba, a just-in-time compiler for python code. And in this example you really can see numba shine:

from numba import jit 
@jit(nopython=True)
def numba_count_bigger_consecutives(values):
  length = len(values)
  result = np.zeros(length)
  for i in range(length):
    for j in range(i):
      if(values[i]>values[j]):
        result[i] += 1
      else:
        break
  return result

%timeit numba_count_bigger_consecutives(df.values)
The slowest run took 543.09 times longer than the fastest. This could mean that an intermediate result is being cached.
10000 loops, best of 3: 161 µs per loop

This is my solution - it has a loop but the number of iterations will only be the maximum streak length. It stores a state of whether the streak for each row has been calculated, and stops when that is done. It uses shift to test if the previous row is higher/lower and keeps increasing the shift until all streaks are found.

a = pd.Series([30, 10, 20, 25, 35, 15, 15])

a_not_done_greater = pd.Series(np.ones(len(a))).astype(bool)
a_not_done_less = pd.Series(np.ones(len(a))).astype(bool)

a_streak_greater = pd.Series(np.zeros(len(a))).astype(int)
a_streak_less = pd.Series(np.zeros(len(a))).astype(int)

s = 1
not_done_greater = True
not_done_less = True

while not_done_greater or not_done_less:
    if not_done_greater:
        a_greater_than_shift = (a > a.shift(s))
        a_streak_greater = a_streak_greater + (a_not_done_greater.astype(int) * a_greater_than_shift)
        a_not_done_greater = a_not_done_greater & a_greater_than_shift
        not_done_greater = a_not_done_greater.any()

    if not_done_less:
        a_less_than_shift = (a < a.shift(s))
        a_streak_less = a_streak_less + (a_not_done_less.astype(int) * a_less_than_shift)
        a_not_done_less = a_not_done_less & a_less_than_shift
        not_done_less = a_not_done_less.any()

    s = s + 1


res = pd.concat([a, a_streak_greater, a_streak_less], axis=1)
res.columns = ['value', 'greater_than_streak', 'less_than_streak']
print(res)

Which gives the dataframe

   value  greater_than_streak  less_than_streak
0     30                    0                 0
1     10                    0                 1
2     20                    1                 0
3     25                    2                 0
4     35                    4                 0
5     15                    0                 3
6     15                    0                 0

Here is the solution a co-worker came up with (probably not the most efficient but it does the trick):

Input data

a = pd.Series([30, 10, 20, 25, 35, 15])

Create 'higher' column

b = []

for idx, value in enumerate(a):
    count = 0
    for i in range(idx, 0, -1):
        if value < a.loc[i-1]:
            break
        count += 1
    b.append([value, count])

higher = pd.DataFrame(b, columns=['Value', 'Higher'])

Create 'lower' column

c = []

for idx, value in enumerate(a):
    count = 0
    for i in range(idx, 0, -1):
        if value > a.loc[i-1]:
            break
        count += 1
    c.append([value, count])

lower = pd.DataFrame(c, columns=['Value', 'Lower'])

Merge the two new series

print(pd.merge(higher, lower, on='Value'))

   Value  Higher  Lower
0     30       0      0
1     10       0      1
2     20       1      0
3     25       2      0
4     35       4      0
5     15       0      3
Related