So I have this dataframe:
structure(list(id = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 2, 2, 2,
2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 4, 4, 4, 4,
4, 4, 4, 4, 4, 4, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 6, 6, 6, 6, 6,
6, 6, 6, 6, 6, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 8, 8, 8, 8, 8, 8,
8, 8, 8, 8, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9), year = c("2017", "2018",
"2019", "2020", "2021", "2022", "2023", "2024", "2025", "2026",
"2017", "2018", "2019", "2020", "2021", "2022", "2023", "2024",
"2025", "2026", "2017", "2018", "2019", "2020", "2021", "2022",
"2023", "2024", "2025", "2026", "2017", "2018", "2019", "2020",
"2021", "2022", "2023", "2024", "2025", "2026", "2017", "2018",
"2019", "2020", "2021", "2022", "2023", "2024", "2025", "2026",
"2017", "2018", "2019", "2020", "2021", "2022", "2023", "2024",
"2025", "2026", "2017", "2018", "2019", "2020", "2021", "2022",
"2023", "2024", "2025", "2026", "2017", "2018", "2019", "2020",
"2021", "2022", "2023", "2024", "2025", "2026", "2017", "2018",
"2019", "2020", "2021", "2022", "2023", "2024", "2025", "2026"
), volume = c(0.0013, 0.0013, 0.0012579, 0.0011895, 0.0011421,
0.0010842, 0.0010211, 0.0010158, 0.00099474, 0.00092632, 0.07878,
0.078791, 0.077295, 0.076638, 0.075538, 0.074468, 0.074776, 0.074051,
0.071706, 0.068056, 0.023269, 0.023011, 0.022374, 0.021962, 0.021408,
0.020949, 0.020811, 0.020354, 0.019309, 0.018042, 0.0004, 0.0004,
0.00038421, 0.00035263, 0.00033158, 0.00032105, 0.00026842, 0.00028421,
0.00026842, 0.00024211, 0.0002, 0.0001, 0.00011579, 0, 0, 0,
0, 0, 0, 0, 0.028422, 0.028361, 0.027768, 0.027501, 0.027029,
0.02651, 0.026588, 0.026209, 0.025094, 0.023391, 0.0001, 0.0001,
0, 0, 0, 0, 0, 0, 0, 0, 0.0047, 0.0047158, 0.0048368, 0.0048316,
0.0049263, 0.0049737, 0.0049947, 0.0051684, 0.0052526, 0.0051842,
0.0106, 0.010389, 0.010279, 0.010005, 0.0098421, 0.0096368, 0.0094053,
0.0093368, 0.0092526, 0.0089316)), class = c("tbl_df", "tbl",
"data.frame"), row.names = c(NA, -90L))
Which looks like this:
# A tibble: 6 × 3
id year volume
<dbl> <chr> <dbl>
1 1 2017 0.0013
2 1 2018 0.0013
3 1 2019 0.00126
4 1 2020 0.00119
5 1 2021 0.00114
6 1 2022 0.00108
Id has 9 distinct IDs, each with 10 rows. Now I would like to find the maximum value for the column volume and then filter out the groups (or just make an extra column like inTop3 ) that highlights those IDs which are in the top-3 highest volume values.
This could mean that the largest 3 values are within the group with ID = 2. But I really only want to compare the maximum value of each group with the maximum value of each other group.
Getting the maximum value per group is trivial:
df %>%
group_by(id) %>%
mutate(
m = max(volume)
)
But then I am a little lost how to go on. Especially I wonder how I could create a boolean column that indicates wheter a group is in the top-3 or not.
CodePudding user response：
You may use dplyr::top_n
df %>%
group_by(id) %>%
arrange(id, desc(volume)) %>%
top_n(3)
id year volume
<dbl> <chr> <dbl>
1 1 2017 0.0013
2 1 2018 0.0013
3 1 2019 0.00126
4 2 2018 0.0788
5 2 2017 0.0788
6 2 2019 0.0773
7 3 2017 0.0233
8 3 2018 0.0230
9 3 2019 0.0224
10 4 2017 0.0004
# … with 24 more rows
top3/nottop3
df %>%
group_by(id) %>%
arrange(id, desc(volume)) %>%
mutate(top3 = ifelse(row_number() %in% c(1,2,3), "top3", "nottop3"))
id year volume top3
<dbl> <chr> <dbl> <chr>
1 1 2017 0.0013 top3
2 1 2018 0.0013 top3
3 1 2019 0.00126 top3
4 1 2020 0.00119 nottop3
5 1 2021 0.00114 nottop3
6 1 2022 0.00108 nottop3
7 1 2023 0.00102 nottop3
8 1 2024 0.00102 nottop3
9 1 2025 0.000995 nottop3
10 1 2026 0.000926 nottop3
CodePudding user response：
Another possible solution:
library(dplyr)
df %>%
group_by(id) %>%
summarise(m = max(volume)) %>%
slice_max(m, n = 3)
#> # A tibble: 3 × 2
#> id m
#> <dbl> <dbl>
#> 1 2 0.0788
#> 2 6 0.0284
#> 3 3 0.0233