1

I want to get the streak since a value in a specific column changed:

datetime                 val1          val2  val1-streak   val2-streak
2018-04-01 00:00:00        4           1        0             0
2018-05-01 00:00:00        5           2        0             0
2018-06-01 00:00:00        5           2        1             1
2018-07-01 00:00:00        6           2        0             2
2018-08-01 00:00:00        7           2        0             3
2018-09-01 00:00:00        7           3        1             0
2018-10-01 00:00:00        7           3        2             1
2018-11-01 00:00:00        5           2        0             0

For now it is ok to assume that the dattime column is equaly spaced. That way i can just count the "periods" since the value in a target column changed. Ideally the function can additionally count the difference in datetinme since the value changed last.

I found a few solutions on this site but when i looked more closely they did not really describe my problem:

Pandas - count since last transaction

Counting changes in pandas row values?

In pandas, how do you find the time since a column by value changes, when grouped by another column?

Pythonic way to calculate streaks in pandas dataframe

finding streaks in pandas dataframe

https://joshdevlin.com/blog/calculate-streaks-in-pandas/

.... at least I was not able to transfer the given answers to my problem.

NorrinRadd
  • 545
  • 6
  • 21
  • why is "val2-streak" `0, 1, 0, 1, 2, 0`? shouldn't it be `0, 0, 1, 2, 3, 0`, since it changes from 1 to 2 in the second row? –  Mar 10 '22 at 17:41
  • Yes you are correct, i added a few rows to show the inteded behaviour, as it can also happend the values that where previously encountered can show up again. – NorrinRadd Mar 11 '22 at 08:37

3 Answers3

2

Use custom function for generate counter by consecutive values by compare differencies per columns specified in list by Series.diff, compare for not equal by Series.ne with cumulative sum, last pass to GroupBy.cumcount:

vals = ['val1','val2']

def f(x):
    x = x.diff().ne(0).cumsum()
    return x.groupby(x).cumcount()

df = df.join(df[vals].apply(f).add_suffix('_streak'))
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
1

Let's start with how to do it with a single column. First off we need to find each point where the value changes:

diffs = df['val1'].diff(1)
change_points = diffs != 0

Since previously encountered values can show up again we need to find a proper way to distinguish different streaks of the same value. We'll achieve that with a cumulative sum of the change point array. For ease we'll create a temporary DataFrame to hold those results (you can create a new column in your dataframe, but that's a bit messy)

change_points_cumsum = change_points.cumsum()
tmp_merged = pd.concat([serie, change_points_cumsum], axis=1, keys=['val1', 'change_points_cumsum'])

Every streak is assigned a different change_points_cumsum value so now a simple group by and cumulative count can be applied to get the final result

tmp_merged["val1-streak"] = tmp_merged.groupby(change_points_cumsum).cumcount()
print(tmp_merged)

   val1  change_points_cumsum  val1-streak 
0     4                     1             0
1     5                     2             0
2     5                     2             1
3     6                     3             0
4     7                     4             0
5     7                     4             1
6     7                     4             2
7     5                     5             0

If you are looking for a shorter, more compact solution

change_points_cumsum = df['val1'].diff(1).ne(0).cumsum()
change_points_cumsum.groupby(change_points_cumsum).cumcount()

0    0
1    0
2    1
3    0
4    0
5    1
6    2
7    0
Grinjero
  • 436
  • 2
  • 7
  • Thank you very much for your answer. I also really liked this Answer. And it would also solve my problem. I can not mark two answers as correct i think. – NorrinRadd Mar 11 '22 at 16:08
1

Let's try the following:

(i) Create a stacked Series using set_index + unstack (this is to use not repeat the same function on two columns separately).

(ii) Use groupby + diff to find differences among consecutive elements: tmp

(iii) We're interested in the location where the differences are 0; we do another groupby + cumsum to get the desired outcome.

(iv) Use pivot to get the output back into the shape of df.

tmp = df.set_index('datetime')[['val1','val2']].unstack().groupby(level=0).diff()
df[['val1-streak','val2-streak']] = pd.pivot(tmp.eq(0)
                                             .groupby([tmp.index.get_level_values(0), 
                                                       tmp.ne(0).cumsum()])
                                             .cumsum()
                                             .reset_index(), 
                                             'datetime', 'level_0', 0).to_numpy()

Output:

              datetime  val1  val2  val1-streak  val2-streak
0  2018-04-01 00:00:00     4     1            0            0
1  2018-05-01 00:00:00     5     2            0            0
2  2018-06-01 00:00:00     5     2            1            1
3  2018-07-01 00:00:00     6     2            0            2
4  2018-08-01 00:00:00     7     2            0            3
5  2018-09-01 00:00:00     7     3            1            0
6  2018-10-01 00:00:00     7     3            2            1
7  2018-11-01 00:00:00     5     2            0            0
  • Thank you very much for your answer. I also really liked this Answer as it gave me the idea to use the information i get when applying diff() to calculated values like mean increase /decrease and mean lenght of the streak. And it would also solve my problem. I can not mark two answers as correct i think. – NorrinRadd Mar 11 '22 at 16:09