I have a dataframe which I would like to add a column identifying the closest value to the respective column from only all previous values ignoring itself.
I found a closest value function but am unsure how to limit it to only previous rows. In the following example I would like to find the closest Revenue value considering only previous rows.
set.seed(1)
df<-data.frame(id=c(1:20),Revenue=sample(20))
closest<-function(xv,sv){
xv[which(abs(xv-sv)==min(abs(xv-sv)))] }
You can try the code below using dist + apply
transform(
df,
close_prev = Revenue[apply(`diag<-`(m <- as.matrix(dist(Revenue)), Inf) / upper.tri(m), 2, which.min)]
)
which gives
id Revenue close_prev
1 1 4 4
2 2 7 4
3 3 1 4
4 4 2 1
5 5 13 7
6 6 19 13
7 7 11 13
8 8 17 19
9 9 14 13
10 10 3 4
11 11 18 19
12 12 5 4
13 13 9 7
14 14 16 17
15 15 6 7
16 16 15 14
17 17 12 13
18 18 10 11
19 19 20 19
20 20 8 7
To get only 1 closest value for each number you can change the function using which.min and use the following.
library(dplyr)
library(purrr)
closest <- function(xv,sv) xv[which.min(abs(xv-sv))]
df %>%
mutate(close_prev = map_dbl(row_number(),
~closest(Revenue[seq_len(max(.x - 1, 1))], Revenue[.x])))
# id Revenue close_prev
#1 1 4 4
#2 2 7 4
#3 3 1 4
#4 4 2 1
#5 5 13 7
#6 6 19 13
#7 7 11 13
#8 8 17 19
#9 9 14 13
#10 10 3 4
#11 11 18 19
#12 12 5 4
#13 13 9 7
#14 14 16 17
#15 15 6 7
#16 16 15 14
#17 17 12 13
#18 18 10 11
#19 19 20 19
#20 20 8 7
All the previous values (.x - 1) are passed everytime in closest function. max(.x - 1, 1) is used to handle the 1st row since there is no value before that.