Create Subtotal, Total and Percentage of multiple SubGroups using adorn_totals

39 Views Asked by At

I am trying to create Subtotal and Total (and Percentage) for each region for the following dataframe using janitor. But, the result not quite right, there is some mistake. TOTAL twice the amount where it should be (Subtotal Counted to the final Total). The Percentage using all value in the column.

Is it possible using janitor (adorn) here?

The Current Output

Here the code for the data.

df <- structure(list(Region = c("RegionA", "RegionA", "RegionA", "RegionA", "RegionA", 
                                 "RegionA", "RegionB", "RegionB", "RegionB", "RegionB", "RegionB", "RegionB"), College = c("CollegeC", 
                                                                                                                           "CollegeC", "CollegeA", "CollegeA", "CollegeB", "CollegeB", "CollegeC", "CollegeC", "CollegeA", "CollegeA", "CollegeB", 
                                                                                                                           "CollegeB"), Class = c("Class", "Non Class", "Class", "Non Class", 
                                                                                                                                                  "Class", "Non Class", "Class", "Non Class", "Class", 
                                                                                                                                                  "Non Class", "Class", "Non Class"), minat1 = c(4399L, 4351L, 
                                                                                                                                                                                                 9519L, 8935L, 2195L, 1823L, 682L, 642L, 2382L, 2592L, 612L, 550L
                                                                                                                                                  ), minat2 = c(4609L, 4141L, 10291L, 8163L, 2321L, 1697L, 723L, 
                                                                                                                                                                601L, 2588L, 2386L, 632L, 530L), minat3 = c(3752L, 4998L, 7969L, 
                                                                                                                                                                                                            10485L, 1941L, 2077L, 543L, 781L, 1613L, 3361L, 394L, 768L), 
                      minat4 = c(4688L, 4062L, 10352L, 8102L, 2546L, 1472L, 675L, 
                                 649L, 2414L, 2560L, 660L, 502L), minat5 = c(2554L, 6196L, 
                                                                             5761L, 12693L, 1597L, 2421L, 350L, 974L, 951L, 4023L, 222L, 
                                                                             940L), minat6 = c(6723L, 2027L, 14253L, 4201L, 3046L, 972L, 
                                                                                               1045L, 279L, 3997L, 977L, 939L, 223L), minat7 = c(4109L, 
                                                                                                                                                 4641L, 8681L, 9773L, 2064L, 1954L, 574L, 750L, 1998L, 2976L, 
                                                                                                                                                 432L, 730L), minat8 = c(6443L, 2307L, 13794L, 4660L, 2998L, 
                                                                                                                                                                         1020L, 994L, 330L, 3724L, 1250L, 820L, 342L)), class = c("grouped_df", 
                                                                                                                                                                                                                                  "tbl_df", "tbl", "data.frame"), row.names = c(NA, -12L), groups = structure(list(
                                                                                                                                                                                                                                    Region = c("RegionA", "RegionA", "RegionA", "RegionB", "RegionB", "RegionB"
                                                                                                                                                                                                                                    ), College = c("CollegeC", "CollegeA", "CollegeB", "CollegeC", "CollegeA", "CollegeB"
                                                                                                                                                                                                                                    ), .rows = structure(list(1:2, 3:4, 5:6, 7:8, 9:10, 11:12), ptype = integer(0), class = c("vctrs_list_of", 
                                                                                                                                                                                                                                                                                                                              "vctrs_vctr", "list"))), class = c("tbl_df", "tbl", "data.frame"
                                                                                                                                                                                                                                                                                                                              ), row.names = c(NA, -6L), .drop = TRUE))

The code that i have been using

library(janitor)
library(dplyr)
fff <- iii %>%
  group_by(Region, Class) %>%
  group_modify(~ adorn_totals(.x, where = "row", name = "Subtotal")) %>%
  group_by(Region) %>%
  group_modify(~ adorn_totals(.x, where = "row", name = "TOTAL")) %>%
  adorn_percentages("col") %>%
  adorn_pct_formatting(digits = 0) %>%
  adorn_ns(position = "front")
0

There are 0 best solutions below