Computing statistics on trends in a time series in pandas

Question

I have a time series data on the prices of items in different periods:

import pandas as pd
d = {'ItemID': {0: '1',
1: '1',
2: '1',
3: '1',
4: '1',
5: '1',
6: '1',
7: '1',
8: '1',
9: '1',
10: '1',
11: '2',
12: '2',
13: '2',
14: '2',
15: '2',
16: '2',
17: '2',
18: '2',
19: '2',
20: '2',
21: '2'},
'Period': {0: '1',
1: '1',
2: '1',
3: '1',
4: '1',
5: '1',
6: '2',
7: '2',
8: '2',
9: '2',
10: '2',
11: '1',
12: '1',
13: '1',
14: '1',
15: '1',
16: '2',
17: '2',
18: '2',
19: '2',
20: '2',
21: '2'},
'Price': {0: 1,
1: 2,
2: 1,
3: 2,
4: 2,
5: 3,
6: 6,
7: 6,
8: 7,
9: 7,
10: 8,
11: 50,
12: 49,
13: 50,
14: 49,
15: 48,
16: 61,
17: 62,
18: 63,
19: 64,
20: 64,
21: 65}}

df = pd.DataFrame(d)

I would like to compute the following statistics about the price changes per item and period:

number of streaks
max streak length
avg streak length

A streak is, essentially, a list of either non-decreasing or non-increasing values. In the following list [0,5,4,3,3] there are 2 streaks: [0,5] and [4,3,3].

For the above dataframe the correct output would be:

s = {'ItemID': {0: '1',1: '1', 2: '2', 3: '2'}, 'Period' : {0: '1',1: '2', 2: '1', 3: '2'},
     'MaxStreakLength': {0: 4,1: 5, 2: 3, 3: 6},
     'AvgStreakLength': {0: 3,1: 3, 2: 2.5, 3: 6},
     'NumStreaks':{0: 2,1: 1, 2: 2, 3: 1}}

How to do this efficiently? The initial dataframe is quite large (millions of records)

user_8208006 · Answer

I assume there no direct methods to achieve these sequence splitting, here i have added conditional sequence splitting,

def sequential_split(p):
    a = p >= 0
    b = a.cumsum()
    arr = b-b.mask(a).ffill().fillna(0).astype(int)
    streak_ends = (np.where(a==0)[0]-1)
    return arr, streak_ends
def get_data(p):
    arr,s_e = sequential_split(p.diff())
    arr1,s_e1 = sequential_split(p.diff(-1))
    if len(s_e)>len(s_e1):
        s_e , arr = s_e1, arr1
        streak_peaks = arr.iloc[s_e].add(1).tolist()
    else:
        streak_peaks = arr.loc[s_e[1:]].add(1).tolist() + [arr.iloc[-1]+1]
    return [arr.max()+1, sum(streak_peaks)/len(streak_peaks), arr[arr==0].shape[0]]

columns=['MaxStreakLength','AvgStreakLength','NumStreaks']
a = df.groupby(['ItemID','Period'])['Price'].apply(get_data)
a.apply(lambda x: pd.Series(x, index=columns)).reset_index()

Out:

 ItemID Period  MaxStreakLength AvgStreakLength NumStreaks
0   1      1          4.0            3.0         2.0
1   1      2          5.0            5.0         1.0
2   2      1          3.0            2.5         2.0
3   2      2          6.0            6.0          1.0

Computing statistics on trends in a time series in pandas

1 Answers