Mutate median in df with intervals from another df

user_16488952 0
Viewed 40

I am looking for a way to calculate the median in a df with intervals from another data frame.

Example:

df_a <- tibble(values = c (1:30))
df_b <- tibble(lower_limit = c(1, 5, 11, 19),
               upper_limit = c(3, 9, 17, 27))

Now I want to mutate a vector named "median" in df_b that computes the median from the data in df_a according to the limits of the intervals in df_b, how can I do that? Regards!

dplyr tidyverse r
2 Answers

We can use map2 from purrr to extract the 'values' column from 'df_a' between that range for each corresponding range column values, then get the median and create a new column

library(dplyr)
library(purrr)
df_b %>%
    mutate(median = map2_dbl(lower_limit, upper_limit, 
            ~ median(df_a$values[between(df_a$values, .x, .y)])))

-output

# A tibble: 4 x 3
  lower_limit upper_limit median
        <dbl>       <dbl>  <dbl>
1           1           3      2
2           5           9      7
3          11          17     14
4          19          27     23

Or with rowwise

df_b %>%
    rowwise %>%
    mutate(median =  df_a %>% 
       filter(between(values, lower_limit, upper_limit)) %>% 
       summarise(values = median(values)) %>% 
       pull(values)) %>% 
    ungroup

-output

# A tibble: 4 x 3
  lower_limit upper_limit median
        <dbl>       <dbl>  <int>
1           1           3      2
2           5           9      7
3          11          17     14
4          19          27     23
user_37322710

You can use fuzzyjoin to join based on range.

library(fuzzyjoin)
library(dplyr)

df_a %>%
  fuzzy_inner_join(df_b, by = c('values' = 'lower_limit','values'='upper_limit'),
                   match_fun = c(`>=`, `<=`)) %>%
  group_by(lower_limit, upper_limit) %>%
  summarise(values = median(values), .groups = 'drop')

#  lower_limit upper_limit values
#        <dbl>       <dbl>  <int>
#1           1           3      2
#2           5           9      7
#3          11          17     14
#4          19          27     23
user_39629140
Related