Select top 2 column sums and names

Question

I am trying to calculate the column sums, then select the top 2. This is a sample of the data frame:

df <- data.frame(precinct = c("A", "B", "C"), 
                 steve = c(309, 337, 294), 
                 mike = c(120, 151, 240), 
                 allan = c(379, 442, 597))

I know how to use colSums to get the column totals.

dfColTot <- colSums(df[ , 2:ncol(df)])

From there, how do you select the top 2 sums and names?

score 1 · Accepted Answer · answered Jan 21 '22 at 04:09

1

You could use

top_2=head(sort(dfColTot, decreasing=TRUE), 2)

top_2
#> allan steve 
#>  1418   940

^{Created on 2022-01-20 by the reprex package (v2.0.1)}

answered Jan 21 '22 at 04:09

Joe Erinjeri

1,200
1
7
15

score 0 · Answer 2 · answered Jan 21 '22 at 04:20

Here is an alternative using order()

df <- data.frame(precinct = c("A", "B", "C"), 
                 steve = c(309, 337, 294), 
                 mike = c(120, 151, 240), 
                 allan = c(379, 442, 597))

dfColTot <- colSums(df[ , 2:ncol(df)])

head(dfColTot[order(dfColTot,decreasing = TRUE)],2)

The result is:

> head(dfColTot[order(dfColTot,decreasing = TRUE)],2)
allan steve 
 1418   940

score 0 · Answer 3 · answered Jan 21 '22 at 04:21

Keeping a dplyr option as well.

library(dplyr)

    df %>% 
      pivot_longer(cols=-precinct) %>%  #advisable to be it long format since summary is required by names.
      group_by(name) %>% 
      summarise(value=sum(value)) %>% #summation per name.
      ungroup() %>% 
      slice_max(order_by=value,n=2) # get the top2 based on descending value column

Output:

# A tibble: 2 × 2
  name  value
  <chr> <dbl>
1 allan  1418
2 steve   940

score 0 · Answer 4 · answered Jan 21 '22 at 05:37

A dplyr and base R combination, if you do not want to have to pivot to long form. After summarizing the columns, I convert the row to a numeric list, then sort the the list and return the index number. I keep only the index number, $ix. Then, I sort and get the index of the top 2 values using tail. Then, I use the indices to select the columns.

library(dplyr)

df %>%
  summarise(across(-precinct, sum)) %>%
  select(c(tail(
    sort(as.numeric(.[1, ]), index.return = TRUE)$ix, 2)))

Output

  steve allan
1   940  1418

Select top 2 column sums and names

4 Answers4