CODEWITHSUNDEEP
rmax
nyct0phile
nyct0phile

Reputation: 21

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

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

Upvotes: 1

Views: 877

Answers (2)

Ben Bolker
Ben Bolker

Reputation: 226077

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

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

should work ...

Upvotes: 2

Duck
Duck

Reputation: 39585

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.

Upvotes: 5

Related Questions