Pandas dataframe, how to find a very specific max values?

[Peter Otten]

zljubisic at gmail.com wrote:

> I have a dataframe:
> 
> 
> df = pd.DataFrame({
>    'x': [3,4,5,8,10,11,12,13,15,16,18,21,24,25],
>    'a': [10,9,16,4,21,5,3,17,11,5,21,19,3,9]
> })
> 
> df
> Out[30]:
>      a   x
> 0   10   3
> 1    9   4
> 2   16   5
> 3    4   8
> 4   21  10
> 5    5  11
> 6    3  12
> 7   17  13
> 8   11  15
> 9    5  16
> 10  21  18
> 11  19  21
> 12   3  24
> 
> 
> for every "x" value I should subtract a certain number (say 3), and then
> find a maximum "a" value where x is in a range from x-3 to x.
> 
> For example:
> index,  x,    x-3,   max(a) where x is between x-3 and x
> 0, 	3, 	0,	10
> 1,	4,	1,	10
> 2,	5,	2,	16
> 3,	8,	5,	16
> 4,     10,      7,      21
> ...
> 
> "x" is incremental, "a" is random
> How to find max "a" values?

I have no idea how you'd do that in pandas.
Here's generic Python:

>>> m = df.as_matrix()
>>> pd.DataFrame(
... [[x, x-3, max(aa for aa, xx in m if x-3<=xx<=x)] for a, x in m],
... columns=["x", "x-3", "max(a)..."]
... )
     x  x-3  max(a)...
0    3    0         10
1    4    1         10
2    5    2         16
3    8    5         16
4   10    7         21
5   11    8         21
6   12    9         21
7   13   10         21
8   15   12         17
9   16   13         17
10  18   15         21
11  21   18         21
12  24   21         19
13  25   22          9

[14 rows x 3 columns]
>>>