CODEWITHSUNDEEP
r
Narjems
Narjems

Reputation: 111

R - for each observation in a column, find the closest one in another column

I'm trying to filter my dataframe to keep only the rows that meet the following condition:

For each day AND each price_1, keep only the row where price_2 is the closest to price_1, and if two rows are at equal distance, take the mean of the 2 prices and volatilies. For example :

 Date              price_2        price_1   Volat
 2011-07-15        215            200.0     5
 2011-07-15        217            200.0     6
 2011-07-15        235            200.0     5.5
 2011-07-15        240            200.0     5.3
 2011-07-15        200            201.5     6.2
 2011-07-16        203            205.0     6.4
 2011-07-16        207            205.0     5.1


Expected output:

 Date              price_2        price_1  Volat
 2011-07-15        215            200.0      5
 2011-07-15        200            201.5     6.2
 2011-07-16        205            205.0     5.75

I started like this, but I don't know how to continue : 

group_by(Date)  %>% 
which(df,abs(df$price_1-df$price_2)==min(abs(df$price_1-df$price_2)))

Thanks a lot in advance!

Upvotes: 2

Views: 200

Answers (2)

hello_friend
hello_friend

Reputation: 5788

Base R Solution: 

price_summary <-
  data.frame(do.call("rbind", lapply(split(
    df, paste(df$Date, df$price_1, sep = " - ")
  ),
  function(x) {
    data.frame(
      Date = unique(x$Date),
      price_1 = unique(x$price_1),
      price_2 = mean(x$price_2[which.min(abs(x$price_2 - x$price_1))]),
      Volat = mean(x$Volat),
      stringsAsFactors = FALSE
    )
  })),
  row.names = NULL)

Data: 

df <- structure(
  list(
    Date = structure(c(
      15170, 15170, 15170, 15170,
      15170, 15171, 15171
    ), class = "Date"),
    price_2 = c(215L, 217L,
                235L, 240L, 200L, 203L, 207L),
    price_1 = c(200, 200, 200, 200,
                201.5, 205, 205),
    Volat = c(5, 6, 5.5, 5.3, 6.2, 6.4, 5.1)
  ),
  row.names = c(NA,-7L),
  class = "data.frame"
)

Upvotes: 2

tmfmnk
tmfmnk

Reputation: 39858

One dplyr option could be:

df %>%
 group_by(Date, price_1) %>%
 mutate(diff = abs(price_2 - price_1)) %>%
 filter(diff == min(diff)) %>%
 summarise_at(vars(price_2, Volat), mean)

  Date       price_1 price_2 Volat
  <chr>        <dbl>   <dbl> <dbl>
1 2011-07-15    200      215  5   
2 2011-07-15    202.     200  6.2 
3 2011-07-16    205      205  5.75

Upvotes: 1

Related Questions