Return rows establishing a “closest value to” in R

Question

I have a data frame with different IDs and I want to make a subgroup in which: for each ID I will only obtain one row with the closest value to 0.5 in variable Y.

This is my data frame:

df <- data.frame(ID=c("DB1", "BD1", "DB2", "DB2", "DB3", "DB3", "DB4", "DB4", "DB4"), X=c(0.04, 0.10, 0.10, 0.20, 0.02, 0.30, 0.01, 0.20, 0.30), Y=c(0.34, 0.49, 0.51, 0.53, 0.48, 0.49, 0.49, 0.50, 1.0) )

This is what I want to get

ID X Y DB1 0.10 0.49 DB2 0.10 0.51 DB3 0.30 0.49 DB4 0.20 0.50

I know I can add a filter with ddply using something like this

ddply(df, .(ID), function(z) { z[z$Y == 0.50, ][1, ] })
and this would work fine if there were always a 0.50 value in Y, which is not the case.

How do change the == for a "nearest to" 0.5, or is there another function I could use instead?

Thank you in advance!

Answer 1

You need to calculate the difference from 0.5 and then keep the smallest one. One way to do this would be as so:

ddply(df, .(ID), function(z) {
  z[abs(z$Y - 0.50) == min(abs(z$Y - 0.50)), ]
})

Note that the way I've coded it above, omitting your [1, ], if two rows are exactly tied both will be kept.

It should be fine since we're doing the exact same calculation on either side of ==, but I often worry about numerical precision problems, so we could instead use which.min. Note that which.min will return the first minimum in the case of a tie.

ddply(df, .(ID), function(z) {
  z[which.min(abs(z$Y - 0.50)), ]
})

Another robust way to do it would be to order the data frame by difference from 0.5 and keep the first row per ID. At this point I'll transition over to dplyr, though of course you could use dplyr or plyr::ddply for any of these methods.

library(dplyr)
df %>% group_by(ID) %>%
  arrange(abs(Y - 0.5)) %>%
  slice(1)

I'm not sure how arrange handles ties. For more methods see Get rows with minimum of variable, but only first row if multiple minima, and just always use abs(Y - 0.5) as the variable you are minimizing.