1

I'm trying to calculate statistics (min, max, avg...) of streaks of consecutive higher values of a column. I'm rather new to pandas and stats, searched a bit but could not find an answer.

The data is financial data, with OHLC values in column, e.g.

              Open     High     Low     Close
Date                                          
2013-10-20  1.36825  1.38315  1.36502  1.38029
2013-10-27  1.38072  1.38167  1.34793  1.34858   
2013-11-03  1.34874  1.35466  1.32941  1.33664   
2013-11-10  1.33549  1.35045  1.33439  1.34950  
....

For example the average consecutive higher Low streak.

LATER EDIT

I think I didn't explain well. An item that was counted in a sequence can't be counted again. So for the sequence:

1,2,3,4,1,2,3,3,2,1

There are 4 streaks: 1,2,3,4 | 1,2,3,3 | 2 | 1

max = 4
min = 1
avg = (4+4+1+1)/4 = 2.5
schiorean
  • 23
  • 3
  • Hi @soso, by "streaks of consecutive higher values of a column", do you mean something like moving average, moving minimum, and moving maxima? – ericmjl Jan 04 '14 at 16:54
  • Hi ericmjl, Take the following sequence: 5,2,4,6,4,7. The max streak of consecutive values has a value of 3 because of: 2,4,6 sequence. Every number in the sequence is higher (or at least equal) than the previous. Is it clear? – schiorean Jan 04 '14 at 20:00

1 Answers1

0
import pandas as pd
import numpy as np

s = pd.Series([1,2,3,4,1,2,3,3,2,1])

def ascends(s):
    diff = np.r_[0, (np.diff(s.values)>=0).astype(int), 0]
    diff2 = np.diff(diff)
    descends = np.where(np.logical_not(diff)[1:] & np.logical_not(diff)[:-1])[0]
    starts = np.sort(np.r_[np.where(diff2 > 0)[0], descends])
    ends = np.sort(np.r_[np.where(diff2 < 0)[0], descends])
    return ends - starts + 1

b = ascends(s)
print b
print b.max()
print b.min()
print b.mean()

(reference)

Output:

[4 4 1 1]
4
1
2.5
Community
  • 1
  • 1
cyborg
  • 9,989
  • 4
  • 38
  • 56
  • Sorry I think I didnt' explain well, I edited the original post with an example from your list. Anyway thank you it's a good start! – schiorean Jan 05 '14 at 13:39
  • Thanks a lot @cyborg! It is exactly what I was looking for. Now will go ahead and try understanding the code. – schiorean Jan 06 '14 at 07:59