How to build a window through number positive and negative ranges in dataframe column?

Question

I would like to have average value and max value in every positive and negative range. From sample data below:

import pandas as pd
test_list = [-1, -2, -3, -2, -1, 1, 2, 3, 2, 1, -1, -4, -5, 2 ,4 ,7  ]
df_test = pd.DataFrame(test_list, columns=['value'])

Which give me dataframe like this:

    value
0      -1
1      -2
2      -3
3      -2
4      -1
5       1
6       2
7       3
8       2
9       1
10     -1
11     -4
12     -5
13      2
14      4
15      7

I would like to have something like that:

AVG1 = [-1, -2, -3, -2, -1] / 5 = - 1.8
Max1 = -3
AVG2 = [1, 2, 3, 2, 1] / 5 = 1.8 
Max2 = 3
AVG3 = [2 ,4 ,7] / 3 =  4.3
Max3 = 7

If solution need new column or new dataframe, that is ok for me.

I know that I can use .mean like here pandas get column average/mean with round value But this solution give me average from all positive and all negative value.

How to build some kind of window that I can calculate average from first negative group next from second positive group and etc..

Regards

Answer 1

You can create Series by np.sign for distinguish positive and negative groups with compare shifted values with cumulative sum for groups and then aggregate mean and max:

s = np.sign(df_test['value'])
g = s.ne(s.shift()).cumsum()
df = df_test.groupby(g)['value'].agg(['mean','max'])
print (df)
           mean  max
value               
1     -1.800000   -1
2      1.800000    3
3     -3.333333   -1
4      4.333333    7

EDIT:

For find locale extremes is used solution from this answer:

test_list = [-1, -2, -3, -2, -1, 1, 2, 3, 2, 1, -1, -4, -5, 2 ,4 ,7  ]
df_test = pd.DataFrame(test_list, columns=['value'])

from scipy.signal import argrelextrema

#https://stackoverflow.com/a/50836425
n=2 # number of points to be checked before and after 
# Find local peaks
df_test['min'] = df_test.iloc[argrelextrema(df_test.value.values, np.less_equal, order=n)[0]]['value']
df_test['max'] = df_test.iloc[argrelextrema(df_test.value.values, np.greater_equal, order=n)[0]]['value']

Then are replaced values after extremes to missing values, separately for negative and positive groups:

s = np.sign(df_test['value'])
g = s.ne(s.shift()).cumsum()

df_test[['min1','max1']] = df_test[['min','max']].notna().astype(int).iloc[::-1].groupby(g[::-1]).cumsum()
df_test['min1'] = df_test['min1'].where(s.eq(-1) & df_test['min1'].ne(0))
df_test['max1'] = df_test['max1'].where(s.eq(1) & df_test['max1'].ne(0))

df_test['g'] = g
print (df_test)
    value  min  max  min1  max1  g
0      -1  NaN -1.0   1.0   NaN  1
1      -2  NaN  NaN   1.0   NaN  1
2      -3 -3.0  NaN   1.0   NaN  1
3      -2  NaN  NaN   NaN   NaN  1
4      -1  NaN  NaN   NaN   NaN  1
5       1  NaN  NaN   NaN   1.0  2
6       2  NaN  NaN   NaN   1.0  2
7       3  NaN  3.0   NaN   1.0  2
8       2  NaN  NaN   NaN   NaN  2
9       1  NaN  NaN   NaN   NaN  2
10     -1  NaN  NaN   1.0   NaN  3
11     -4  NaN  NaN   1.0   NaN  3
12     -5 -5.0  NaN   1.0   NaN  3
13      2  NaN  NaN   NaN   1.0  4
14      4  NaN  NaN   NaN   1.0  4
15      7  NaN  7.0   NaN   1.0  4

So is possible separately aggregate last 3 values per groups with lambda function and mean, rows with missing values in min1 or max1 are removed by default in groupby:

df1 = df_test.groupby(['g','min1'])['value'].agg(lambda x: x.tail(3).mean())
print (df1)
g  min1
1  1.0    -2.000000
3  1.0    -3.333333
Name: value, dtype: float64

df2 = df_test.groupby(['g','max1'])['value'].agg(lambda x: x.tail(3).mean())
print (df2)
g  max1
2  1.0     2.000000
4  1.0     4.333333
Name: value, dtype: float64