rtidyversegtjanitor

How to get sum and mean when using summarise(across())


I have a small dataset below and I want to sum 4 columns and also want to get the mean of another two columns using the same summarise(across( call. The code below works but sums all the columns. I only want W,F,LF,S to be summed and want the mean from temp and turb to be displayed on the gt table. I attempted summarise_at with no luck. Thanks.

enter image description here

library(dplyr)
library(tidyr)
library(janitor)
library(gt)

a <- structure(list(SampleDate = structure(c(15710, 15713, 15713, 
15710, 15710, 15713, 15713, 15710, 15708, 15713, 15712, 15708, 
15708, 15713, 15712, 15708), class = "Date"), year = c("2012", 
"2013", "2013", "2012", "2013", "2013", "2013", "2013", "2013", 
"2012", "2013", "2013", "2013", "2013", "2013", "2013"), F = c(0, 
1, 0, 0, 0, 1, 0, 0, 0, 22, 0, 0, 0, 65, 0, 0), W = c(0, 0, 1, 
0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 1, 0), S = c(0, 0, 0, 0, 1, 
0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0), LF = c(1, 0, 0, 1, 0, 0, 0, 
1, 0, 0, 0, 0, 1, 0, 0, 1), temp = c(45L, 46L, 47L, 48L, 45L, 
46L, 47L, 48L, 45L, 46L, 47L, 48L, 45L, 46L, 47L, 48L), turb = c(14L, 
15L, 16L, 17L, 14L, 15L, 16L, 17L, 14L, 15L, 16L, 17L, 14L, 15L, 
16L, 17L)), row.names = c(NA, -16L), class = "data.frame")

a |>
  mutate(SampleDate = as.character(SampleDate)) |>
  group_by(year, SampleDate) |>
  summarise(across(c(W, F, LF, S,temp,turb), sum)) |>    #I want the mean of temp and turb
  gt() |>
  summary_rows(
    columns = -c(SampleDate,temp,turb),
    fns =  list(label = "Total", fn = "sum"),   #Sum salmon runs only
    side = "bottom") |>
     summary_rows(
    columns = c(temp,turb),
    fns = list(fun="mean"),                #Get mean of temp and turb
    side = "bottom") 
    

Solution

  • Use double across:

    a |>
      mutate(SampleDate = as.character(SampleDate)) |>
      group_by(year, SampleDate) |>
      summarise(across(c(W, F, LF, S), sum), across(c(temp,turb), mean))