r - 使用 grand_summary_rows() 创建用户定义的总计

Question

我有一个看起来像这样的表：

  category family       amount
  <chr>    <chr>        <chr> 
1 SALES    ONLINE SALES 47    
2 SALES    IN STORE     72    
3 COGS     LABOR        28    
4 COGS     TAXES        35    
5 COGS     WORKERS COMP 24    
6 COGS     BENEFITS     33    
7 EXPENSE  AUTOMOBILE   44    
8 EXPENSE  RENT         12    
9 EXPENSE  TELEPHONE    26    

我正在尝试从中创建一个 gt 表，因此我创建了以下代码：

library(tidyverse)
library(gt)

category <- c(rep("SALES",2),
              rep("COGS", 4),
              rep("EXPENSE",3)
)
family <- c("ONLINE SALES","IN STORE","LABOR","TAXES","WORKERS COMP","BENEFITS",
            "AUTOMOBILE", "RENT","TELEPHONE")

amount <- c(47,72,28,35,24,33,44,12,26)


output <- as_tibble(cbind(category,family,amount)) %>% 
    mutate(amount= as.numeric(amount)) %>% 
    gt(rowname_col = 'family',
       groupname_col = 'category') %>% 
    row_group_order(c("SALES","COGS", "EXPENSE")) %>% 
    summary_rows(groups = TRUE,
                 columns = 'amount',
                 fns = list(
                     Total = ~sum(.,na.rm = TRUE)
                 ))
output

在所有金额仍然为正的情况下，您如何获得SALES-COGS-EXPENSE使用该 功能的总体总数？grand_summary_rows()

标签： r

Answer 1

如果我正确理解了您的要求，您可以使用以下代码：

output2 <- as_tibble(cbind(category,family,amount)) %>% 
           mutate(amount= as.numeric(amount)) %>% 
           gt(rowname_col = 'family', groupname_col = 'category') %>%
           summary_rows(groups = TRUE,
             columns = 'amount',
             fns = list(
             Total = ~sum(.,na.rm = TRUE)
             )) %>%  
           grand_summary_rows(
             columns = c("family","category","amount"),
             fns = list(
             "Grand Total" = ~sum(.,na.rm = TRUE)),
             formatter = fmt_number,
             use_seps = FALSE
            )

output2

＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃ 编辑 ＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃

我从文档中找到的唯一方法是创建自定义聚合函数。这是输出打印屏幕的完整工作示例。

customFunc <- function(data) {

  salesSum <- sum(subset(data$`_data`[,c("amount")], category == 'SALES'))
  cogsSum <- sum(subset(data$`_data`[,c("amount")], category == 'COGS'))
  expenseSum <- sum(subset(data$`_data`[,c("amount")], category == 'EXPENSE'))

  return (salesSum - cogsSum - expenseSum)

}

data <- as_tibble(cbind(category,family,amount)) %>% 
  mutate(amount= as.numeric(amount)) %>% 
  gt(rowname_col = 'family', groupname_col = 'category')

output3 <- data %>%
  summary_rows(groups = TRUE,
               columns = 'amount',
               fns = list(
                 Total = ~sum(.,na.rm = TRUE)
               )) %>%  
  grand_summary_rows(
    columns = c("family","category","amount"),
    fns = list(
      "Grand Total" = ~customFunc(data)),
    formatter = fmt_number,
    use_seps = FALSE
  )

output3