Find the 3 nearest neighbours (dist()?) and calculate mean in new column

user_9157005 0
Viewed 71

This is a sample of the data

structure(list(Season = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), .Label = c("2018/2019", 
"2019/2020"), class = "factor"), Date2 = structure(c(17860, 17888, 
17916, 17940, 17945, 17952, 17953, 17954, 17978, 17999, 18005, 
18188, 18209, 18223, 18237, 18320, 18322, 18334, 18447, 18476
), class = "Date"), HT.av.points = c(0.57, 1.5, 1.67, 1.8, 1.09, 
2.18, 1.42, 1.45, 1.79, 1.35, 1.14, 1.83, 2, 1.17, 1.88, 1.83, 
1.33, 0.92, 1.31, 1.06), AT.av.points = c(1.14, 2.33, 0.56, 1.2, 
1.09, 1.6, 1.08, 1.9, 1.17, 0.9, 1.38, 0.67, 2.14, 1.33, 0.62, 
1.08, 2.17, 1.38, 0.56, 0.94), HT_av.PointsTotal = c(0.86, 1.16, 
1.18, 1.23, 0.86, 1.86, 1.2, 1.18, 1.5, 1.1, 1.07, 1.46, 1.6, 
1.08, 1.75, 1.4, 1.16, 0.92, 1.03, 0.97), AT_av.PointsTotal = c(2.07, 
2.21, 0.76, 1.42, 1.59, 1.5, 1.2, 1.91, 1.65, 1.43, 1.38, 0.54, 
1.87, 1.58, 0.8, 1.6, 2.32, 1.42, 1.12, 1.32), DIFF.AV.POINTS.PREDICTION = c(-0.28, 
-0.43, 0.51, 0.52, -0.36, 0.56, 0.28, -0.38, -0.2, 0.03, -0.43, 
1.24, -0.32, -0.29, 1.44, 0.28, -0.85, -0.38, 1.01, 0.22), Over2.5G = c(0, 
0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 1, 0, 0, 0, 1)), row.names = c(NA, 
-20L), .internal.selfref = <pointer: 0x1ca2448>, class = c("data.table", 
"data.frame"), .Names = c("Season", "Date2", "HT.av.points", 
"AT.av.points", "HT_av.PointsTotal", "AT_av.PointsTotal", "DIFF.AV.POINTS.PREDICTION", 
"Over2.5G"))

enter image description here

What I want to do:

group by Season

After the group by, I want to find the 3 previous rows that are most similar (according to the following columns) to the current row.

(HT.av.points, AT.av.points, HT_av.PointsTotal, AT_av.PointsTotal, DIFF.AV.POINTS.PREDICTION)

I guess the dist() function is a possibility.

Finally I want to create a new column with the mean of the values of the Over2.5G column of those 3 most similar rows.

New column:

First 3 rows(of the Season) NAs.

In fourth row(of the Season) the 3 nearest neighbours (and their Over2.5G values) will always be the first 3 rows.

nearest-neighbor distance r
1 Answers

breaking below code up:

  • a helper function which returns row indices of nearest neighbours with a ready-made function, e.g. get.knn of package FNN
  • calling this function for increasingly large slices (from row one to current) of the input data df and storing the result as an extra column
  • extracting the row indices as integers from the result string to index the desired column of the input data for the aggregation (mean, in your case)

here we go:

## helper function returns row indices of nearest 3 neighbours
## as comma-separated string
find_nearest_predecessors <- function(df, ...){
    ifelse(nrow(df) < 4, ## can't calculate n neighbours for <n rows:
           paste(1:3, collapse = ','),
           ## otherwise = if sufficient rows,
           ## get row indices of 3 nearest neighbours:
           get.knn(data = df,
                   k = 3,
                   algo = 'CR'
                   ) %>%
           .[['nn.index']] %>%
           tail(1) %>% paste(collapse = ',')
           )
}

## df being your input data:
df %>%
    mutate(rownum = row_number()) %>%
    rowwise %>%
    mutate(nearest_neighbours = find_nearest_predecessors(
               df = ## use previous data up to current row: 
                   slice(df, 1:rownum) %>%
                   ## choose features/dimensions of distance:
                   select(HT.av.points, AT.av.points, HT_av.PointsTotal,
                          AT_av.PointsTotal, DIFF.AV.POINTS.PREDICTION)
           ),
           ## calculate mean of OVER2.5G
           mean_Over2.5G = mean(df$Over2.5G[
                                       strsplit(nearest_neighbours,',') %>%
                                       unlist %>% as.integer
                                   ], na.rm = TRUE)
           )
user_183097110
Related