'Creating custom Quantiles within data frame?
If i have the following table:
tibble(year = c("2020", "2020", "2020","2021", "2021", "2021"),
website = c("facebook", "google", "youtube","facebook", "google", "youtube"),
method = c("laptop", "laptop", "laptop", "mobile", "mobile", "mobile"),
values = c(10,30,60, 90,25, 40))
How can i try to go about creating a column based on a custom q-tile of the numbers in the value column.
For example, if i have the following custom q-tile conditions:
Risky - > 50% Neither - 25-50% Safe - <25%
These are basically saying for the numbers in the values column, calculate what their ranking based on the q-tile conditions above and give them a rank value of 1,2,3 accordingly.
The final table should look like this:
tibble(year = c("2020", "2020", "2020","2021", "2021", "2021"),
website = c("facebook", "google", "youtube","facebook", "google", "youtube"),
method = c("laptop", "laptop", "laptop", "mobile", "mobile", "mobile"),
values = c(10,30,60, 90,25, 40),
rank = c(3,2,1,1,3,2))
I know that the table will have to be grouped by year and method along, so the code will look like this:
df %>% group_by(year, method) %>% mutate(rank = quantile(???))
Solution 1:[1]
You can use quantile(x, c(0.25, 0.5))
to get cutpoints and pass them into findInterval()
. Note that findInterval()
is similar to cut(*, labels = FALSE)
but is more efficient.
library(dplyr)
df %>%
group_by(year, method) %>%
mutate(rank = findInterval(-values, quantile(-values, c(0.25, 0.5)), left.open = TRUE) + 1) %>%
ungroup()
# # A tibble: 6 × 5
# year website method values rank
# <chr> <chr> <chr> <dbl> <dbl>
# 1 2020 facebook laptop 10 3
# 2 2020 google laptop 30 2
# 3 2020 youtube laptop 60 1
# 4 2021 facebook mobile 90 1
# 5 2021 google mobile 25 3
# 6 2021 youtube mobile 40 2
If you want labels rather than ranks, use cut()
:
df %>%
group_by(year, method) %>%
mutate(rank = cut(values, quantile(values, c(0, 0.25, 0.5, 1)),
c("Safe", "Neither", "Risky"), include.lowest = TRUE)) %>%
ungroup()
# # A tibble: 6 × 5
# year website method values rank
# <chr> <chr> <chr> <dbl> <fct>
# 1 2020 facebook laptop 10 Safe
# 2 2020 google laptop 30 Neither
# 3 2020 youtube laptop 60 Risky
# 4 2021 facebook mobile 90 Risky
# 5 2021 google mobile 25 Safe
# 6 2021 youtube mobile 40 Neither
Solution 2:[2]
You can use the ntile
function from dplyr
to create quantiles:
library(dplyr)
df %>%
group_by(year, method) %>%
mutate(rank = ntile(values, 4))
Output:
# A tibble: 6 × 5
# Groups: year, method [2]
year website method values rank
<chr> <chr> <chr> <dbl> <int>
1 2020 facebook laptop 10 1
2 2020 google laptop 30 2
3 2020 youtube laptop 60 3
4 2021 facebook mobile 90 3
5 2021 google mobile 25 1
6 2021 youtube mobile 40 2
Solution 3:[3]
df %>%
group_by(year, method) %>%
mutate(rank = rank(-cut(values, breaks = c(-Inf, quantile(values, probs = c(0.25, 0.50), names = F), Inf), labels = F)))
# # A tibble: 6 x 5
# # Groups: year, method [2]
# year website method values rank
# <chr> <chr> <chr> <dbl> <dbl>
# 1 2020 facebook laptop 10 3
# 2 2020 google laptop 30 2
# 3 2020 youtube laptop 60 1
# 4 2021 facebook mobile 90 1
# 5 2021 google mobile 25 3
# 6 2021 youtube mobile 40 2
Solution 4:[4]
A {santoku}
one-liner:
mutate(df,
rank = santoku::chop_quantiles(rank, c(0.25, 0.5),
labels = c("Safe", "Neither", "Risky"))
)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | |
Solution 2 | Quinten |
Solution 3 | Merijn van Tilborg |
Solution 4 | dash2 |