I want append rows of totals in the output of summarise used with group_by.
Data <-
structure(list(CT = c("1", "1", "1", "1", "1", "1", "1", "1",
"1", "1", "1", "1", "2", "2", "2", "2", "2", "2", "2", "2", "2",
"2"), SCT = c("1", "1", "1", "1", "1", "1", "2", "2", "2", "2",
"2", "2", "1", "1", "1", "1", "2", "2", "2", "2", "2", "2"),
SSCT = c("1", "2", "3", "1", "2", "3", "1", "2", "1", "2",
"1", "2", "1", "2", "1", "2", "1", "2", "3", "1", "2", "3"
), Category = c(111L, 112L, 113L, 111L, 112L, 113L, 121L,
122L, 121L, 122L, 121L, 122L, 211L, 212L, 211L, 212L, 221L,
222L, 223L, 221L, 222L, 223L), Y = c(10L, 12L, 15L, 11L,
10L, 13L, 21L, 22L, 20L, 25L, 23L, 24L, 31L, 33L, 36L, 39L,
32L, 31L, 36L, 41L, 44L, 45L)), row.names = c(NA, -22L), class = "data.frame")
head(Data)
CT SCT SSCT Category Y
1 1 1 1 111 10
2 1 1 2 112 12
3 1 1 3 113 15
4 1 1 1 111 11
5 1 1 2 112 10
6 1 1 3 113 13
Here
CT: Category, SCT: Sub Category, SSCT: Sub Sub Category
Data %>%
group_by(CT) %>%
summarise(Total = sum(Y))
# A tibble: 2 x 2
CT Total
<chr> <int>
1 1 206
2 2 368
Data %>%
group_by(CT, SCT) %>%
summarise(Total = sum(Y))
# A tibble: 4 x 3
# Groups: CT [2]
CT SCT Total
<chr> <chr> <int>
1 1 1 71
2 1 2 135
3 2 1 139
4 2 2 229
Data %>%
group_by(CT, SCT, SSCT) %>%
summarise(Total = sum(Y))
# A tibble: 10 x 4
# Groups: CT, SCT [4]
CT SCT SSCT Total
<chr> <chr> <chr> <int>
1 1 1 1 21
2 1 1 2 22
3 1 1 3 28
4 1 2 1 64
5 1 2 2 71
6 2 1 1 67
7 2 1 2 72
8 2 2 1 73
9 2 2 2 75
10 2 2 3 81
Required Output
Need output something following:
CT SCT SSCT Total
Total 206
Total 71
1 1 1 21
1 1 2 22
1 1 3 28
Total 135
1 2 1 64
1 2 2 71
Total 368
Total 139
2 1 1 67
2 1 2 72
Total 229
2 2 1 73
2 2 2 75
2 2 3 81
if you want the final columns to be numeric instead of character, you will have to accept NA instead of ""
bind_rows(
Data %>%
group_by(CT) %>%
summarise(Total = sum(Y)) %>%
mutate(SCT = "Total", SSCT = "Total"),
Data %>%
group_by(CT, SCT) %>%
summarise(Total = sum(Y)) %>%
mutate(SSCT = "Total"),
Data %>%
group_by(CT, SCT, SSCT) %>%
summarise(Total = sum(Y))
) %>%
mutate(across(c("SCT","SSCT"), ~ fct_relevel(., "Total"))) %>%
arrange(CT, SCT, SSCT) %>%
mutate(CT = ifelse(SSCT == "Total", "Total", CT),
SCT = ifelse(SSCT == "Total", "", as.numeric(SCT)-1),
SSCT = gsub("Total", "", SSCT)) %>%
select(CT, SCT, SSCT, Total)
# A tibble: 16 x 4
CT SCT SSCT Total
<chr> <chr> <chr> <int>
1 Total "" "" 206
2 Total "" "" 71
3 1 "1" "1" 21
4 1 "1" "2" 22
5 1 "1" "3" 28
6 Total "" "" 135
7 1 "2" "1" 64
8 1 "2" "2" 71
9 Total "" "" 368
10 Total "" "" 139
11 2 "1" "1" 67
12 2 "1" "2" 72
13 Total "" "" 229
14 2 "2" "1" 73
15 2 "2" "2" 75
16 2 "2" "3" 81