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 - -