CODEWITHSUNDEEP
r
Felix T.
Felix T.

Reputation: 530

Find closest value between vectors in data frame by group

I have a dataset df: 

df <- tibble(
  id = sort(rep(letters[1:3], 3)),
  visit_id = rep(c(0, 5, 10), 3),
  true_visit = c(NA, 3, NA, 0, 5, 10, 1, 7, NA)
)
> df
# A tibble: 9 x 3
  id    visit_id true_visit
  <chr>    <dbl>      <dbl>
1 a            0         NA
2 a            5          3
3 a           10         NA
4 b            0          0
5 b            5          5
6 b           10         10
7 c            0          1
8 c            5          7
9 c           10         NA

I’m trying to create a new column closest_visit where I find the true_visit that is closest to visit_id within each individual. The result would look like: 

# A tibble: 9 x 4
  id    visit_id true_visit closest_visit
  <chr>    <dbl>      <dbl>         <dbl>
1 a            0         NA             3
2 a            5          3             3
3 a           10         NA             3
4 b            0          0             0
5 b            5          5             5
6 b           10         10            10
7 c            0          1             1
8 c            5          7             7
9 c           10         NA             7

To clarify, closest_visit is 3 for individual a because it's the only true_visit. closest_visit is 1 for the seventh row because 0 (the visit_id for that row) is closer to 1 than it is to 7 (the true_visits for that participant), and so on.

I tried looking here, here, and here. They were a good start but not exactly what I'm looking for. Any ideas?

Upvotes: 3

Views: 183

Answers (3)

arg0naut91
arg0naut91

Reputation: 14774

Could go for:

library(dplyr)

df %>%
  group_by(id) %>%
  mutate(
    closest_visit = case_when(
      visit_id == true_visit ~ true_visit,
      TRUE ~ true_visit[sapply(visit_id, 
                               function(x) which.min(abs(x - true_visit)))]
      )
    )

Output:

# A tibble: 9 x 4
# Groups:   id [3]
  id    visit_id true_visit closest_visit
  <chr>    <dbl>      <dbl>         <dbl>
1 a            0         NA             3
2 a            5          3             3
3 a           10         NA             3
4 b            0          0             0
5 b            5          5             5
6 b           10         10            10
7 c            0          1             1
8 c            5          7             7
9 c           10         NA             7

Upvotes: 1

Andr&#233; Oliveira
Andr&#233; Oliveira

Reputation: 175

This isn't the most pretty way, but it works on your example:

library(dplyr)

for (id in unique(df$id) ) {

  available_visit = na.omit(df[df$id == id ,'true_visit']) %>% pull()
  unique_id = unique(df$visit_id[df$id == id])

    for (visit_id in unique_id) {

      df[df$id == id & df$visit_id ==  visit_id, 'closest_visit' ] <- 
        available_visit[which.min(abs(available_visit-visit_id))]
      }
  }

Upvotes: 1

akrun
akrun

Reputation: 887981

One option is findInterval and then fill

library(dplyr)
library(tidyr)
df %>%
   group_by(id) %>% 
   mutate(closest_visit = na.omit(true_visit)[findInterval(true_visit, 
          visit_id)]) %>% 
   fill(closest_visit, .direction = "updown")
# A tibble: 9 x 4
# Groups:   id [3]
#  id    visit_id true_visit closest_visit
#  <chr>    <dbl>      <dbl>         <dbl>
#1 a            0         NA             3
#2 a            5          3             3
#3 a           10         NA             3
#4 b            0          0             0
#5 b            5          5             5
#6 b           10         10            10
#7 c            0          1             1
#8 c            5          7             7
#9 c           10         NA             7

Upvotes: 1

Related Questions