I want to sort a dataframe by a 'sum' of a group. So, I don't want the data frame to be ordered by group, but by the total amount of a group. I.e. I want to know which group is the biggest, 1 or 2 or 3 and then order the values according to that. So, say group 3 is the biggest group, then I want group 3 at the top and I want the values of group 3 in descending order.
set.seed(123)
d <- data.frame(
x = runif(90),
grp = gl(3, 30))
Thanks!
One way using dplyr is
d %>%
group_by(grp) %>%
mutate(sum_ = sum(x)) %>%
arrange(desc(sum_), desc(x)) %>%
select(-sum_)
Basically, we create a temporary variable sum_ that indicates the sum of x by group, and then arrange according to sum_ first and x second. Afterwards, we remove sum_ since it's no longer needed.
Output
# A tibble: 90 x 2
# Groups: grp [3]
# x grp
# <dbl> <fct>
# 1 0.994 1
# 2 0.957 1
# 3 0.955 1
# 4 0.940 1
# 5 0.900 1
# 6 0.892 1
# 7 0.890 1
# 8 0.883 1
# 9 0.788 1
# 10 0.709 1
# ... with 80 more rows
Another option is to reorder the levels of grp according to the sum of x. This can be done with tidyverse like this:
library(tidyverse)
set.seed(123)
df <- tibble(
x = runif(90),
grp = gl(3, 30)
)
df %>%
mutate(
grp = fct_reorder(grp, x, .fun = "sum", .desc = TRUE)
) %>%
arrange(grp)
#> # A tibble: 90 x 2
#> x grp
#> <dbl> <fct>
#> 1 0.288 1
#> 2 0.788 1
#> 3 0.409 1
#> 4 0.883 1
#> 5 0.940 1
#> 6 0.0456 1
#> 7 0.528 1
#> 8 0.892 1
#> 9 0.551 1
#> 10 0.457 1
#> # … with 80 more rows
df %>%
group_by(grp) %>%
summarise(tot = sum(x))
#> # A tibble: 3 x 2
#> grp tot
#> <fct> <dbl>
#> 1 1 17.2
#> 2 2 13.2
#> 3 3 15.4
Created on 2020-06-17 by the reprex package (v0.3.0)
In base you can first use order on all, than split by grp and rbind by the order of rowsum.
d <- d[order(-d$x),]
do.call(rbind, split(d, d$grp)[order(-rowsum(d$x, d$grp))])
# x grp
#1.24 0.9942697766 1
#1.11 0.9568333453 1
#1.20 0.9545036491 1
#1.5 0.9404672843 1
#1.16 0.8998249704 1
#1.8 0.8924190444 1
#1.21 0.8895393161 1
#1.4 0.8830174040 1
#1.2 0.7883051354 1
#1.26 0.7085304682 1
#1.22 0.6928034062 1
#1.13 0.6775706355 1
#1.25 0.6557057991 1
#1.23 0.6405068138 1
#1.28 0.5941420204 1
#1.14 0.5726334020 1
#1.9 0.5514350145 1
#1.27 0.5440660247 1
#1.7 0.5281054880 1
#1.10 0.4566147353 1
#1.12 0.4533341562 1
#1.3 0.4089769218 1
#1.19 0.3279207193 1
#1.29 0.2891597373 1
#1.1 0.2875775201 1
#1.17 0.2460877344 1
#1.30 0.1471136473 1
#1.15 0.1029246827 1
#1.6 0.0455564994 1
#1.18 0.0420595335 1
#3.87 0.9849569800 3
#3.88 0.8930511144 3
#3.89 0.8864690608 3
#3.65 0.8146400389 3
#3.68 0.8123895095 3
#3.67 0.8100643530 3
#3.69 0.7943423211 3
#3.84 0.7881958340 3
#3.71 0.7544751586 3
#3.73 0.7101824014 3
#3.82 0.6680555874 3
#3.61 0.6651151946 3
#3.72 0.6292211316 3
#3.78 0.6127710033 3
#3.75 0.4753165741 3
#3.66 0.4485163414 3
#3.70 0.4398316876 3
#3.86 0.4348927415 3
#3.83 0.4176467797 3
#3.63 0.3839696378 3
#3.77 0.3798165377 3
#3.79 0.3517979092 3
#3.64 0.2743836446 3
#3.81 0.2436194727 3
#3.76 0.2201188852 3
#3.90 0.1750526503 3
#3.80 0.1111354243 3
#3.85 0.1028646443 3
#3.62 0.0948406609 3
#3.74 0.0006247733 3
#2.31 0.9630242325 2
#2.32 0.9022990451 2
#2.59 0.8950453592 2
#2.50 0.8578277153 2
#2.53 0.7989248456 2
#2.34 0.7954674177 2
#2.37 0.7584595375 2
#2.58 0.7533078643 2
#2.33 0.6907052784 2
#2.55 0.5609479838 2
#2.36 0.4777959711 2
#2.48 0.4659624503 2
#2.52 0.4422000742 2
#2.42 0.4145463358 2
#2.43 0.4137243263 2
#2.60 0.3744627759 2
#2.44 0.3688454509 2
#2.39 0.3181810076 2
#2.49 0.2659726404 2
#2.47 0.2330340995 2
#2.40 0.2316257854 2
#2.38 0.2164079358 2
#2.56 0.2065313896 2
#2.45 0.1524447477 2
#2.41 0.1428000224 2
#2.46 0.1388060634 2
#2.57 0.1275316502 2
#2.54 0.1218992600 2
#2.51 0.0458311667 2
#2.35 0.0246136845 2
Another option is to reorder the levels of grp which can than be used in order.
d$grp <- factor(d$grp, levels(d$grp)[order(-rowsum(d$x, d$grp))])
d[order(d$grp, -d$x),]