How to find the maximum value in the 20 previous rows of a column in R?

Question

How to find the maximum value in the 20 previous rows of a column in R? For example I have price data for the last 40 days. Starting with day 21 I want to know what the high price was in the previous 20 days--the 20 day high. Then for day 22, it would be the high for the previous 20 days of that days price. Here is a snapshot from Excel, but how to implement in R? I can't figure out how to index it properly. Thank you. Excel example

Does this answer your question? [Find running minimum and Max in R](https://stackoverflow.com/questions/38405025/find-running-minimum-and-max-in-r) — Valeri Voev, Aug 28 '20 at 11:15
@ValeriVoev, that doesn't seem to be the same question (cumulative vs. running) — Ben Bolker, Aug 28 '20 at 11:19
Please do not post your data as an image. Images cannot be searched or copied into R. They also mess with screen readers. Use `dput()` on your data in R and paste the results into your question. — Ben Norris, Aug 28 '20 at 11:23

score 4 · Accepted Answer · answered Aug 28 '20 at 11:37

Another approach is using slide_dbl() function from slider package and dplyr. Here an example with some dummy data:

library(dplyr)
library(slider)
#Data
df <- structure(list(Date = structure(c(18502, 18503, 18504, 18505, 
18506, 18507, 18508, 18509, 18510, 18511, 18512, 18513, 18514, 
18515, 18516, 18517, 18518, 18519, 18520, 18521, 18522, 18523, 
18524, 18525, 18526, 18527, 18528, 18529, 18530, 18531, 18532, 
18533, 18534, 18535, 18536, 18537, 18538, 18539, 18540), class = "Date"), 
    Price = c(1490, 3604, 2003, 4004, 4247, 468, 2506, 4044, 
    2604, 2204, 4316, 2190, 3137, 2694, 711, 4075, 1315, 454, 
    1660, 4306, 4032, 3201, 2980, 4474, 3044, 3267, 2573, 2784, 
    1497, 897, 4342, 4086, 3192, 3634, 380, 2293, 3478, 1190, 
    1619)), class = "data.frame", row.names = c(NA, -39L))

The code. In .before you define the size of your window:

#Compute
df %>%
  mutate(TwentyDayHigh = slide_dbl(Price, max, .before = 20, .complete = TRUE))

Output:

         Date Price TwentyDayHigh
1  2020-08-28  1490            NA
2  2020-08-29  3604            NA
3  2020-08-30  2003            NA
4  2020-08-31  4004            NA
5  2020-09-01  4247            NA
6  2020-09-02   468            NA
7  2020-09-03  2506            NA
8  2020-09-04  4044            NA
9  2020-09-05  2604            NA
10 2020-09-06  2204            NA
11 2020-09-07  4316            NA
12 2020-09-08  2190            NA
13 2020-09-09  3137            NA
14 2020-09-10  2694            NA
15 2020-09-11   711            NA
16 2020-09-12  4075            NA
17 2020-09-13  1315            NA
18 2020-09-14   454            NA
19 2020-09-15  1660            NA
20 2020-09-16  4306            NA
21 2020-09-17  4032          4316
22 2020-09-18  3201          4316
23 2020-09-19  2980          4316
24 2020-09-20  4474          4474
25 2020-09-21  3044          4474
26 2020-09-22  3267          4474
27 2020-09-23  2573          4474
28 2020-09-24  2784          4474
29 2020-09-25  1497          4474
30 2020-09-26   897          4474
31 2020-09-27  4342          4474
32 2020-09-28  4086          4474
33 2020-09-29  3192          4474
34 2020-09-30  3634          4474
35 2020-10-01   380          4474
36 2020-10-02  2293          4474
37 2020-10-03  3478          4474
38 2020-10-04  1190          4474
39 2020-10-05  1619          4474

With your real data, results must change.

@nyct0phile Great ! If you think this answe was helpful, you could accept it by clicking the tick on the left side of this answer :) — Duck, Aug 28 '20 at 12:14

Ben Bolker · Answer 2 · 2020-08-29T01:53:13.063

2

You haven't given a reproducible example, but something like

zoo::rollmaxr(data$Price,k=20,fill=NA)

should work ...

edited Aug 29 '20 at 01:53

answered Aug 28 '20 at 11:18

Ben Bolker

211,554
25
370
453

1

na.pad is deprecated. Try `rollmaxr(data$Price, 20, fill = NA)` – G. Grothendieck Aug 29 '20 at 01:12

How to find the maximum value in the 20 previous rows of a column in R?

2 Answers2