CODEWITHSUNDEEP
python-3.xpandasdataframe
Ricardo Carrera
Ricardo Carrera

Reputation: 133

How to get the max value from previous N rows of a record in Pandas?

I have the following Pandas DataFrame:

date          value
2021-01-01    10
2021-01-02    5
2021-01-03    7
2021-01-04    1
2021-01-05    12
2021-01-06    8
2021-01-07    9
2021-01-08    8
2021-01-09    4
2021-01-10    3

I need to get the max value from the previous N-1 rows (counting the current record) and make an operation. For example:

For N=3 and the operation = current_row / MAX (previous_N-1_rows_and_current), this should be the result:

date          value    Operation
2021-01-01    10       10/10
2021-01-02    5        5/10
2021-01-03    7        7/10
2021-01-04    1        1/7
2021-01-05    12       12/12
2021-01-06    8        8/12
2021-01-07    9        9/12
2021-01-08    8        8/9
2021-01-09    4        4/9
2021-01-10    3        3/8

If it's possible, in the spirit of the pythonic way.

Thanks and regards.

Upvotes: 0

Views: 1605

Answers (2)

Shubham Sharma
Shubham Sharma

Reputation: 71689

We can calculate rolling max over the value column then divide value column by this rolling max to get the result

df['op'] = df['value'] / df.rolling(3, min_periods=1)['value'].max()

         date  value        op
0  2021-01-01     10  1.000000
1  2021-01-02      5  0.500000
2  2021-01-03      7  0.700000
3  2021-01-04      1  0.142857
4  2021-01-05     12  1.000000
5  2021-01-06      8  0.666667
6  2021-01-07      9  0.750000
7  2021-01-08      8  0.888889
8  2021-01-09      4  0.444444
9  2021-01-10      3  0.375000

Upvotes: 4

Andrej Kesely
Andrej Kesely

Reputation: 195418

You can use .rolling:

df["Operation"] = df.rolling(3, min_periods=1)["value"].apply(
    lambda x: x.iat[-1] / x.max()
)
print(df)

Prints:

         date  value  Operation
0  2021-01-01     10   1.000000
1  2021-01-02      5   0.500000
2  2021-01-03      7   0.700000
3  2021-01-04      1   0.142857
4  2021-01-05     12   1.000000
5  2021-01-06      8   0.666667
6  2021-01-07      9   0.750000
7  2021-01-08      8   0.888889
8  2021-01-09      4   0.444444
9  2021-01-10      3   0.375000

Upvotes: 1

Related Questions