R语言如何在df中灵活添加小计行

txu3uszq 于 2023-02-01 发布在其他

关注(0)|答案(4)|浏览(115)

我有一个df，就像左边的那个，我想给每个主题加一个小计行（右边表格用黄色高亮显示），目前我的代码能够达到我的目标，但是用了一个很傻的方法，我必须一个一个的做总结，🤣有没有更聪明的方法？

# Data:
df<-structure(list(SUBJECT = c("ELA", "ELA", "ELA", "ELA", "ELA", 
"Math", "Math", "Math", "Math", "Math", "Sci", "Sci", "Sci", 
"Sci", "Sci"), Level = c("Grade 1", "Grade 2", "Grade 3", "Grade 4", 
"Grade 5", "Grade 1", "Grade 2", "Grade 3", "Grade 4", "Grade 5", 
"Grade 1", "Grade 2", "Grade 3", "Grade 4", "Grade 5"), Total = c(2, 
0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0), Class1 = c(0, 0, 0, 
0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0), Class2 = c(0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0), Class3 = c(2, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 1, 0, 0), Class4 = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0), Class5 = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0)), row.names = c(NA, -15L), class = c("tbl_df", "tbl", 
"data.frame"))

ClassLev<-c("Class1","Class2", "Class3", "Class4", "Class5"))) 

## Output
df2<-df %>% 
  group_by(SUBJECT) %>%
  do(add_row(.data = .,
             SUBJECT = .$SUBJECT,
             Level = "All Grades",
             `Class1` = sum(.$`Class1`),
             `Class2` = sum(.$`Class2`),
             `Class3` = sum(.$`Class3`),
             `Class4` = sum(.$`Class4`),
             `Class5` = sum(.$`Class5`),
             Total = sum(.$`Total`),
             .before = 1))%>% 
  distinct()

来源：https://stackoverflow.com/questions/75260835/how-to-add-subtotal-row-in-a-df-smartly

4条答案

按热度按时间

ou6hu8tu1#

下面是使用bind_rows执行此操作的一种方法：

library(dplyr)

df %>% 
  group_by(SUBJECT) %>% 
  bind_rows(summarise(.,
                      across(where(is.numeric), sum),
                      across(where(is.character), ~"All Grades"))) %>% 
  arrange(SUBJECT, Level, .by_group = TRUE)

SUBJECT Level      Total Class1 Class2 Class3 Class4 Class5
   <chr>   <chr>      <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
 1 ELA     All Grades     2      0      0      2      0      0
 2 ELA     Grade 1        2      0      0      2      0      0
 3 ELA     Grade 2        0      0      0      0      0      0
 4 ELA     Grade 3        0      0      0      0      0      0
 5 ELA     Grade 4        0      0      0      0      0      0
 6 ELA     Grade 5        0      0      0      0      0      0
 7 Math    All Grades     1      1      0      0      0      0
 8 Math    Grade 1        1      1      0      0      0      0
 9 Math    Grade 2        0      0      0      0      0      0
10 Math    Grade 3        0      0      0      0      0      0
11 Math    Grade 4        0      0      0      0      0      0
12 Math    Grade 5        0      0      0      0      0      0
13 Sci     All Grades     1      0      0      1      0      0
14 Sci     Grade 1        0      0      0      0      0      0
15 Sci     Grade 2        0      0      0      0      0      0
16 Sci     Grade 3        1      0      0      1      0      0
17 Sci     Grade 4        0      0      0      0      0      0
18 Sci     Grade 5        0      0      0      0      0      0

赞(0）回复(0）举报 2023-02-01

yfjy0ee72#

我们可以使用adorn_totals

library(janitor)
library(dplyr)
df %>% 
  group_by(SUBJECT) %>%
  group_modify(~ adorn_totals(.x, name = "All Grades")) %>% 
  ungroup

输出

# A tibble: 18 × 8
   SUBJECT Level      Total Class1 Class2 Class3 Class4 Class5
   <chr>   <chr>      <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
 1 ELA     Grade 1        2      0      0      2      0      0
 2 ELA     Grade 2        0      0      0      0      0      0
 3 ELA     Grade 3        0      0      0      0      0      0
 4 ELA     Grade 4        0      0      0      0      0      0
 5 ELA     Grade 5        0      0      0      0      0      0
 6 ELA     All Grades     2      0      0      2      0      0
 7 Math    Grade 1        1      1      0      0      0      0
 8 Math    Grade 2        0      0      0      0      0      0
 9 Math    Grade 3        0      0      0      0      0      0
10 Math    Grade 4        0      0      0      0      0      0
11 Math    Grade 5        0      0      0      0      0      0
12 Math    All Grades     1      1      0      0      0      0
13 Sci     Grade 1        0      0      0      0      0      0
14 Sci     Grade 2        0      0      0      0      0      0
15 Sci     Grade 3        1      0      0      1      0      0
16 Sci     Grade 4        0      0      0      0      0      0
17 Sci     Grade 5        0      0      0      0      0      0
18 Sci     All Grades     1      0      0      1      0      0

在devel版本中，我们还可以将reframe与pick一起使用

df %>% 
  reframe(adorn_totals(pick(everything()), name = "All Grades"), .by = 'SUBJECT')

输出

# A tibble: 18 × 8
   SUBJECT Level      Total Class1 Class2 Class3 Class4 Class5
   <chr>   <chr>      <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
 1 ELA     Grade 1        2      0      0      2      0      0
 2 ELA     Grade 2        0      0      0      0      0      0
 3 ELA     Grade 3        0      0      0      0      0      0
 4 ELA     Grade 4        0      0      0      0      0      0
 5 ELA     Grade 5        0      0      0      0      0      0
 6 ELA     All Grades     2      0      0      2      0      0
 7 Math    Grade 1        1      1      0      0      0      0
 8 Math    Grade 2        0      0      0      0      0      0
 9 Math    Grade 3        0      0      0      0      0      0
10 Math    Grade 4        0      0      0      0      0      0
11 Math    Grade 5        0      0      0      0      0      0
12 Math    All Grades     1      1      0      0      0      0
13 Sci     Grade 1        0      0      0      0      0      0
14 Sci     Grade 2        0      0      0      0      0      0
15 Sci     Grade 3        1      0      0      1      0      0
16 Sci     Grade 4        0      0      0      0      0      0
17 Sci     Grade 5        0      0      0      0      0      0
18 Sci     All Grades     1      0      0      1      0      0

赞(0）回复(0）举报 2023-02-01

afdcj2ne3#

使用rows_append的另一种方法是添加汇总行：

library(dplyr)
df %>%
  rows_append(df %>% 
                group_by(SUBJECT) %>%
                summarise(across(Total:Class5, ~ sum(.x)),
                          Level = "All Grades")) %>%
  group_by(SUBJECT) %>%
  arrange(desc(Total), Level, .by_group = TRUE)
#> # A tibble: 18 × 8
#> # Groups:   SUBJECT [3]
#>    SUBJECT Level      Total Class1 Class2 Class3 Class4 Class5
#>    <chr>   <chr>      <dbl>  <dbl>  <dbl>  <dbl>  <dbl>  <dbl>
#>  1 ELA     All Grades     2      0      0      2      0      0
#>  2 ELA     Grade 1        2      0      0      2      0      0
#>  3 ELA     Grade 2        0      0      0      0      0      0
#>  4 ELA     Grade 3        0      0      0      0      0      0
#>  5 ELA     Grade 4        0      0      0      0      0      0
#>  6 ELA     Grade 5        0      0      0      0      0      0
#>  7 Math    All Grades     1      1      0      0      0      0
#>  8 Math    Grade 1        1      1      0      0      0      0
#>  9 Math    Grade 2        0      0      0      0      0      0
#> 10 Math    Grade 3        0      0      0      0      0      0
#> 11 Math    Grade 4        0      0      0      0      0      0
#> 12 Math    Grade 5        0      0      0      0      0      0
#> 13 Sci     All Grades     1      0      0      1      0      0
#> 14 Sci     Grade 3        1      0      0      1      0      0
#> 15 Sci     Grade 1        0      0      0      0      0      0
#> 16 Sci     Grade 2        0      0      0      0      0      0
#> 17 Sci     Grade 4        0      0      0      0      0      0
#> 18 Sci     Grade 5        0      0      0      0      0      0

创建于2023年1月27日，使用reprex v2.0.2

赞(0）回复(0）举报 2023-02-01

zvokhttg4#

使用Map、rbind和split的基R方法

do.call(rbind, 
  c(Map(rbind, 
    split(df, df$SUBJECT), lapply(split(df, df$SUBJECT), function(x) 
      c(unique(x[[1]]), "All Grades", colSums(x[-(1:2)])))), 
  make.row.names=F))
   SUBJECT      Level Total Class1 Class2 Class3 Class4 Class5
1      ELA    Grade 1     2      0      0      2      0      0
2      ELA    Grade 2     0      0      0      0      0      0
3      ELA    Grade 3     0      0      0      0      0      0
4      ELA    Grade 4     0      0      0      0      0      0
5      ELA    Grade 5     0      0      0      0      0      0
6      ELA All Grades     2      0      0      2      0      0
7     Math    Grade 1     1      1      0      0      0      0
8     Math    Grade 2     0      0      0      0      0      0
9     Math    Grade 3     0      0      0      0      0      0
10    Math    Grade 4     0      0      0      0      0      0
11    Math    Grade 5     0      0      0      0      0      0
12    Math All Grades     1      1      0      0      0      0
13     Sci    Grade 1     0      0      0      0      0      0
14     Sci    Grade 2     0      0      0      0      0      0
15     Sci    Grade 3     1      0      0      1      0      0
16     Sci    Grade 4     0      0      0      0      0      0
17     Sci    Grade 5     0      0      0      0      0      0
18     Sci All Grades     1      0      0      1      0      0

由于这是一个基于行的方法，数字被转换成字符。

df2 <- do.call(rbind, 
  c(Map(rbind, 
    split(df, df$SUBJECT), lapply(split(df, df$SUBJECT), function(x) 
      c(unique(x[[1]]), "All Grades", colSums(x[-(1:2)])))), 
  make.row.names=F))

asNum <- Vectorize(\(x) as.numeric(x))

cbind(df2[, 1:2], asNum(df2[, 3:8]))

赞(0）回复(0）举报 2023-02-01

我来回答

R语言如何在df中灵活添加小计行

4条答案

相关问题

热门标签

最新问答

R语言 如何在df中灵活添加小计行

4条答案

相关问题

热门标签

最新问答

R语言如何在df中灵活添加小计行