Calculate mean value by interval coordinates in pandas

May 18, 2022

I have a dataframe such as :

Name Position Value
A    1        10
A    2        11
A    3        10
A    4        8
A    5        6
A    6        12
A    7        10
A    8        9
A    9        9
A    10       9
A    11       9
A    12       9

and I woulde like for each interval of 3 position, to calculate the mean of Values.
And create a new df with start and end coordinates (of length 3 then), with the Mean_value column.

Name Start End Mean_value 
A    1     3   10.33     <---- here this is (10+11+10)/3 = 10.33 
A    4     6   8.7
A    7     9   9.3
A    10    13  9

Does someone have an idea using pandas please ?

>Solution :

Solution for get each 3 rows (if exist) per Name groups – first get counter by GroupBy.cumcount with integer division and pass it to named aggregations:

g = df.groupby('Name').cumcount() // 3
df = df.groupby(['Name',g]).agg(Start=('Position','first'),
                                End=('Position','last'),
                                Value=('Value','mean')).droplevel(1).reset_index()
print (df)
  Name  Start  End      Value
0    A      1    3  10.333333
1    A      4    6   8.666667
2    A      7    9   9.333333
3    A     10   12   9.000000