Count values less than x and find nearest values to x by multiple groups

Question

Sample data frame data

         uid     bas_id dist2mouth type
2020   2019 W3A9101601   2.413629    1
2021   2020 W3A9101601   2.413629    1
2022   2021 W3A9101602   2.413629    1
2023   2022 W3A9101602   3.313893    1
2032   2031 W3A9101602   3.313893    1
2033   2032 W3A9101602   3.313893    1
2034   2033 W3A9101602   3.313893    1
15023 15022 W3A9101601   1.349000    2
15025 15024 W3A9101601   3.880000    2
15026 15025 W3A9101602   3.880000    2
15027 15026 W3A9101602   0.541101    2
16106 17097 W3A9101602   1.349000    2

For each row I'd like to calculate how many rows of type=2 within the same bas_id have a lower dist2mouth. Effectively how many rows type=2 are downstream of each row. Store it as ds_n_type2. So far I've tried dplyr

ds <- data %>%
  group_by(id) %>%
  summarize(n_ds = sum(dist2mouth > id[dist2mouth]))

I would then like to find the closest row type=2 to each row type=1 within the same bas_id maybe using which in a for or apply loop. Store it as closest_uid_type2. Maybe something like

which(abs(x[i:n]-x[i])==min(abs(x[i:n]-x[i])))

Happy to clarify

Edit 2 Desired output amended

         uid     bas_id dist2mouth type ds_n_type2 closest_uid_type2
2020   2019 W3A9101601   2.413629    1  1          15022 
2021   2020 W3A9101601   2.413629    1  1          15022 
2022   2021 W3A9101602   2.413629    1  2          15022 
2023   2022 W3A9101602   3.313893    1  2          15024 
2032   2031 W3A9101602   3.313893    1  2          15024 
2033   2032 W3A9101602   3.313893    1  2          15024 
2034   2033 W3A9101602   3.313893    1  2          15024 
15023 15022 W3A9101601   1.349000    2  -          -
15025 15024 W3A9101601   3.880000    2  -          -
15026 15025 W3A9101602   3.880000    2  -          -
15027 15026 W3A9101602   0.541101    2  -          -
17097 W3A9101602   1.349000    2  -          -

Answer 1

Try this:

require(dplyr)

df %>%
  group_by(bas_id) %>%
  mutate(n_ds = match(dist2mouth,sort(dist2mouth))-1) %>%
  mutate(closest_uid=apply(
    sapply(dist2mouth,function(i)abs(i-dist2mouth)),
    2,function(n) uid[which(n==sort(n)[2])])) %>%
  data.frame()

Output:

  uid dist2mouth bas_id type n_ds closest_uid
1   1         10      1    1    2           4
2   2          5      1    2    0           3
3   3          6      1    1    1           2
4   4         11      1    1    3           1
5   5          3      2    2    0           6
6   6          4      2    1    1           5

Edit:

This may not be the most elegant, but here's one way to solve the updated question (pending time to refine it):

df$ds_n_type2[df$type==1] <- sapply(as.numeric(row.names(df[df$type==1,])), 
                                function(x) sum(as.numeric(df$dist2mouth[x]) > as.numeric(df$dist2mouth[df$bas_id==df$bas_id[x] & df$type==2])))

df$closest_uid_type2[df$type==1] <- sapply(as.numeric(row.names(df[df$type==1,])),
                                       function(x) df$uid[which(df$dist2mouth==df$dist2mouth[df$bas_id==df$bas_id[x] & df$type==2][which.min(abs(c(df$dist2mouth[df$bas_id==df$bas_id[x] & df$type==2])-df$dist2mouth[x]))])[1]])

Output:

      uid     bas_id dist2mouth type ds_n_type2 closest_uid_type2
 1:  2019 W3A9101601   2.413629    1          1             15022
 2:  2020 W3A9101601   2.413629    1          1             15022
 3:  2021 W3A9101602   2.413629    1          2             15022
 4:  2022 W3A9101602   3.313893    1          2             15024
 5:  2031 W3A9101602   3.313893    1          2             15024
 6:  2032 W3A9101602   3.313893    1          2             15024
 7:  2033 W3A9101602   3.313893    1          2             15024
 8: 15022 W3A9101601   1.349000    2         NA                NA
 9: 15024 W3A9101601   3.880000    2         NA                NA
10: 15025 W3A9101602   3.880000    2         NA                NA
11: 15026 W3A9101602   0.541101    2         NA                NA
12: 17097 W3A9101602   1.349000    2         NA                NA