R - use dplyr to filter each column based on each column's quantiles

Question

Given a data frame, I'd like to use to filter each column, using the quantiles of each column. I would prefer to use dplyr/tidyverse to accomplish this.

set.seed(23)
df <- data.frame(
  x1 = runif(10, 0, 100),
  x2 = runif(10, 0, 100),
  x3 = runif(10, 0, 100)
)
df
> df
         x1       x2       x3
1  57.66037 86.59590 58.63978
2  22.30729 70.14217 27.47410
3  33.18966 39.04731 14.76570
4  71.07246 31.47697 80.14103
5  81.94490 84.59473 38.64098
6  42.37206 13.92785 82.04507
7  96.35445 51.81206 68.49373
8  97.81304 59.35508 88.33893
9  84.05219 94.24617 11.19208
10 99.66112 62.80196 77.88340

> quantile(df$x1, .95)
     95% 
98.82949 
> quantile(df$x2, .95)
     95% 
90.80355 

My desired result would then be either of 1. a data frame in long format with anything above the percentiles set to NA or removed completely or 2. a wide data frame with anything above the percentiles set to NA.

Answer 1

I think the easiest way to do these operations is to convert to a long shape and use x1, x2, and x3 as groups for calculating the quantiles. You can then stretch it back out into a wide shape if you choose. You could replace the high values with NA explicitly, but if you use tidyr::spread, you get NAs filled in for missing values anyway.

I'm keeping some intermediate steps for clarity, but the gist is to gather into a long shape, find the 95th percentile, keep values at or below the 95th percentile, and spread back to wide. After grouping, I'm also adding a row number as an ID column to avoid the dreaded "duplicate names..." error. With quantiles, it looks like this:

library(tidyverse)

...

df %>%
  gather(key, value) %>%
  group_by(key) %>%
  mutate(q95 = quantile(value, 0.95), row = row_number())
#> # A tibble: 30 x 4
#> # Groups:   key [3]
#>    key   value   q95   row
#>    <chr> <dbl> <dbl> <int>
#>  1 x1     57.7  98.8     1
#>  2 x1     22.3  98.8     2
#>  3 x1     33.2  98.8     3
#>  4 x1     71.1  98.8     4
#>  5 x1     81.9  98.8     5
#>  6 x1     42.4  98.8     6
#>  7 x1     96.4  98.8     7
#>  8 x1     97.8  98.8     8
#>  9 x1     84.1  98.8     9
#> 10 x1     99.7  98.8    10
#> # ... with 20 more rows

You can see from these first several lines that the 10th row has a value above the corresponding 95th percentile, so we'll expect that to be filtered out and turned into NA.

Then use the quantiles to filter and spread.

df %>%
  gather(key, value) %>%
  group_by(key) %>%
  mutate(q95 = quantile(value, 0.95), row = row_number()) %>%
  filter(value <= q95) %>%
  select(-q95) %>%
  spread(key, value) %>%
  select(-row)
#> # A tibble: 10 x 3
#>       x1    x2    x3
#>    <dbl> <dbl> <dbl>
#>  1  57.7  86.6  58.6
#>  2  22.3  70.1  27.5
#>  3  33.2  39.0  14.8
#>  4  71.1  31.5  80.1
#>  5  81.9  84.6  38.6
#>  6  42.4  13.9  82.0
#>  7  96.4  51.8  68.5
#>  8  97.8  59.4  NA  
#>  9  84.1  NA    11.2
#> 10  NA    62.8  77.9

In practice, you don't need to add a whole column just for q95, and could instead use something more concise, like filter(value <= quantile(value, 0.95)).

Answer 2

As of 2021, use filter with if_all:

df %>%
    dplyr::filter(if_all(everything(), ~.x >= quantile(.x,.01) & .x <= quantile(.x,.99)))