search

insightsengineering / rtables

Reporting tables with R
https://insightsengineering.github.io/rtables/
Other
224 stars 48 forks source link

Issue when sorting table using sort_at_path() given cont_n_onecol() #868

Closed huanlugwu closed 5 months ago

huanlugwu commented 5 months ago

Refer from Pruning and Sorting Tables, I happen to have the same need to use:

sort_at_path(pruned, path = c("RACE", "*", "STRATA1"), cont_n_onecol(5))

To generate similar table like:

#                                    A: Drug X                B: Placebo              C: Combination     
#                                 F            M            F            M            F            M     
# -------------------------------------------------------------------------------------------------------
# ASIAN                       44 (62.9%)   35 (68.6%)   37 (66.1%)   31 (62.0%)   40 (65.6%)   44 (64.7%)
#   A                         15 (21.4%)   12 (23.5%)   14 (25.0%)   6 (12.0%)    15 (24.6%)   16 (23.5%)
#     Mean                      30.40        34.42        35.43        30.33        37.40        36.25   
#   C                         13 (18.6%)   15 (29.4%)   10 (17.9%)   9 (18.0%)    15 (24.6%)   16 (23.5%)
#     Mean                      36.92        35.60        34.00        31.89        33.47        31.38   
#   B                         16 (22.9%)   8 (15.7%)    13 (23.2%)   16 (32.0%)   10 (16.4%)   12 (17.6%)
#     Mean                      33.75        34.88        32.46        30.94        33.30        35.92   
# BLACK OR AFRICAN AMERICAN   18 (25.7%)   10 (19.6%)   12 (21.4%)   12 (24.0%)   13 (21.3%)   14 (20.6%)
#   B                         7 (10.0%)     3 (5.9%)     3 (5.4%)     3 (6.0%)     6 (9.8%)     6 (8.8%) 
#     Mean                      36.14        34.33        29.67        32.00        36.33        31.00   
#   A                          5 (7.1%)     1 (2.0%)     5 (8.9%)     2 (4.0%)     4 (6.6%)     4 (5.9%) 
#     Mean                      31.20        33.00        28.00        30.00        30.75        36.50   
#   C                          6 (8.6%)    6 (11.8%)     4 (7.1%)    7 (14.0%)     3 (4.9%)     4 (5.9%) 
#     Mean                      31.33        39.67        34.50        34.00        33.00        36.50   
# WHITE                       8 (11.4%)    6 (11.8%)    7 (12.5%)    7 (14.0%)    8 (13.1%)    10 (14.7%)
#   C                          2 (2.9%)     2 (3.9%)     3 (5.4%)     0 (0.0%)     4 (6.6%)     4 (5.9%) 
#     Mean                      35.50        44.00        44.67          NA         38.50        35.00   
#   B                          4 (5.7%)     3 (5.9%)     1 (1.8%)     4 (8.0%)     3 (4.9%)     1 (1.5%) 
#     Mean                      37.00        43.67        48.00        36.75        34.33        36.00   
#   A                          2 (2.9%)     1 (2.0%)     3 (5.4%)     3 (6.0%)     1 (1.6%)     5 (7.4%) 
#     Mean                      34.00        45.00        29.33        33.33        35.00        32.80

What if, there are 100 participants as BLACK OR AFRICAN AMERICAN + Female in ARM C: Combination

#                                    A: Drug X                B: Placebo              C: Combination     
#                                 F            M            F            M            F            M     
# -------------------------------------------------------------------------------------------------------
# ASIAN                       44 (62.9%)   35 (68.6%)   37 (66.1%)   31 (62.0%)   40 (65.6%)   44 (64.7%)
#   A                         15 (21.4%)   12 (23.5%)   14 (25.0%)   6 (12.0%)    15 (24.6%)   16 (23.5%)
#     Mean                      30.40        34.42        35.43        30.33        37.40        36.25   
#   C                         13 (18.6%)   15 (29.4%)   10 (17.9%)   9 (18.0%)    15 (24.6%)   16 (23.5%)
#     Mean                      36.92        35.60        34.00        31.89        33.47        31.38   
#   B                         16 (22.9%)   8 (15.7%)    13 (23.2%)   16 (32.0%)   10 (16.4%)   12 (17.6%)
#     Mean                      33.75        34.88        32.46        30.94        33.30        35.92   
# BLACK OR AFRICAN AMERICAN   18 (25.7%)   10 (19.6%)   12 (21.4%)   12 (24.0%)   100 (xx.x%)   14 (20.6%)
#   B                         7 (10.0%)     3 (5.9%)     3 (5.4%)     3 (6.0%)     6 (9.8%)     6 (8.8%) 
#     Mean                      36.14        34.33        29.67        32.00        36.33        31.00   
#   A                          5 (7.1%)     1 (2.0%)     5 (8.9%)     2 (4.0%)     4 (6.6%)     4 (5.9%) 
#     Mean                      31.20        33.00        28.00        30.00        30.75        36.50   
#   C                          6 (8.6%)    6 (11.8%)     4 (7.1%)    7 (14.0%)     3 (4.9%)     4 (5.9%) 
#     Mean                      31.33        39.67        34.50        34.00        33.00        36.50   
# WHITE                       8 (11.4%)    6 (11.8%)    7 (12.5%)    7 (14.0%)    8 (13.1%)    10 (14.7%)
#   C                          2 (2.9%)     2 (3.9%)     3 (5.4%)     0 (0.0%)     4 (6.6%)     4 (5.9%) 
#     Mean                      35.50        44.00        44.67          NA         38.50        35.00   
#   B                          4 (5.7%)     3 (5.9%)     1 (1.8%)     4 (8.0%)     3 (4.9%)     1 (1.5%) 
#     Mean                      37.00        43.67        48.00        36.75        34.33        36.00   
#   A                          2 (2.9%)     1 (2.0%)     3 (5.4%)     3 (6.0%)     1 (1.6%)     5 (7.4%) 
#     Mean                      34.00        45.00        29.33        33.33        35.00        32.80

How can I sort this table in column C: Combination/F by RACE first, move BLACK OR AFRICAN AMERICAN before ASIAN?

Melkiades commented 5 months ago

what if you try sort_at_path(pruned, path = c("RACE"), cont_n_onecol(5))?

huanlugwu commented 5 months ago

what if you try sort_at_path(pruned, path = c("RACE"), cont_n_onecol(5))?

That was what I assumed and tried, but it is not worked, it seems like cont_n_onecol() works at child levels under RACE?

huanlugwu commented 5 months ago

I made up some codes here:

library(rtables)
library(dplyr)

raw_lyt <- basic_table() %>%
  split_cols_by("ARM") %>%
  split_rows_by("SEX") %>%
  summarize_row_groups() %>%
  split_rows_by("RACE") %>%
  summarize_row_groups() %>%
  split_rows_by("STRATA1") %>%
  summarize_row_groups() %>%
  analyze("AGE")

DM_black <- DM[DM$RACE == "BLACK OR AFRICAN AMERICAN", ]
DM <- rbind(DM, DM_black, DM_black, DM_black, DM_black)

raw_tbl <- build_table(raw_lyt, DM)

coltrimmed <- raw_tbl[, col_counts(raw_tbl) > 0]

pruned <- prune_table(coltrimmed)
pruned

sort_at_path(pruned, path = c("SEX", "*", "RACE", "*", "STRATA1"), cont_n_onecol(3))

I got:

                               A: Drug X      B: Placebo    C: Combination
——————————————————————————————————————————————————————————————————————————
F                             2302 (64.1%)   1544 (50.1%)    1673 (48.1%) 
  ASIAN                        44 (1.2%)      37 (1.2%)       40 (1.2%)   
    A                          15 (0.4%)      14 (0.5%)       15 (0.4%)   
      Mean                       30.40          35.43           37.40     
    C                          13 (0.4%)      10 (0.3%)       15 (0.4%)   
      Mean                       36.92          34.00           33.47     
    B                          16 (0.4%)      13 (0.4%)       10 (0.3%)   
      Mean                       33.75          32.46           33.30     
  BLACK OR AFRICAN AMERICAN   2250 (62.6%)   1500 (48.7%)    1625 (46.7%) 
    B                         875 (24.4%)    375 (12.2%)     750 (21.6%)  
      Mean                       36.14          29.67           36.33     
    A                         625 (17.4%)    625 (20.3%)     500 (14.4%)  
      Mean                       31.20          28.00           30.75     
    C                         750 (20.9%)    500 (16.2%)     375 (10.8%)  
      Mean                       31.33          34.50           33.00     
  WHITE                         8 (0.2%)       7 (0.2%)        8 (0.2%)   
    C                           2 (0.1%)       3 (0.1%)        4 (0.1%)   
      Mean                       35.50          44.67           38.50     
    B                           4 (0.1%)       1 (0.0%)        3 (0.1%)   
      Mean                       37.00          48.00           34.33     
    A                           2 (0.1%)       3 (0.1%)        1 (0.0%)   
      Mean                       34.00          29.33           35.00     
M                             1291 (35.9%)   1538 (49.9%)    1804 (51.9%) 
  ASIAN                        35 (1.0%)      31 (1.0%)       44 (1.3%)   
    A                          12 (0.3%)       6 (0.2%)       16 (0.5%)   
      Mean                       34.42          30.33           36.25     
    C                          15 (0.4%)       9 (0.3%)       16 (0.5%)   
      Mean                       35.60          31.89           31.38     
    B                           8 (0.2%)      16 (0.5%)       12 (0.3%)   
      Mean                       34.88          30.94           35.92     
  BLACK OR AFRICAN AMERICAN   1250 (34.8%)   1500 (48.7%)    1750 (50.3%) 
    B                         375 (10.4%)    375 (12.2%)     750 (21.6%)  
      Mean                       34.33          32.00           31.00     
    A                          125 (3.5%)     250 (8.1%)     500 (14.4%)  
      Mean                       33.00          30.00           36.50     
    C                         750 (20.9%)    875 (28.4%)     500 (14.4%)  
      Mean                       39.67          34.00           36.50     
  WHITE                         6 (0.2%)       7 (0.2%)       10 (0.3%)   
    A                           1 (0.0%)       3 (0.1%)        5 (0.1%)   
      Mean                       45.00          33.33           32.80     
    C                           2 (0.1%)       0 (0.0%)        4 (0.1%)   
      Mean                       44.00            NA            35.00     
    B                           3 (0.1%)       4 (0.1%)        1 (0.0%)   
      Mean                       43.67          36.75           36.00

Yet I expected:

M                             1291 (35.9%)   1538 (49.9%)    1804 (51.9%)

should have gone up to the first row along together with its childs, rather than:

F                             2302 (64.1%)   1544 (50.1%)    1673 (48.1%)
Melkiades commented 5 months ago

you are asking for two sorting at two different levels of the table so you need to do it twice:

sort_at_path(pruned, path = c("SEX", "*", "RACE", "*", "STRATA1"), cont_n_onecol(3)) %>% 
  sort_at_path(path = c("SEX"), cont_n_onecol(3))
huanlugwu commented 5 months ago 
sort_at_path(pruned, path = c("SEX", "*", "RACE", "*", "STRATA1"), cont_n_onecol(3)) %>% 
  sort_at_path(path = c("SEX"), cont_n_onecol(3))

Thank you @Melkiades , I've detected the issue is resulted in "empty row" added in split_rows_by() in my table, I solved it thru previous closed issue #315 suggested by @gmbecker using "split_rows_by(..., section_div = " ")".

huanlugwu commented 5 months ago 
ort_at_path(pruned, path = c("SEX", "*", "RACE", "*", "STRATA1"), cont_n_onecol(3)) %>% 
  sort_at_path(path = c("SEX"), cont_n_onecol(3))

@Melkiades may I ask if one level share the same total number in cont_n_onecol(1), how can I let it sort it in cont_n_onecol(1) only then by alphabetic, without looking into cont_n_onecol(2)?

For example:

Ophthalmologicals                                                                                  xx (xx.x)    xx (xx.x) 
  ......
    ......
  Local anesthetics                                                                                 3 (1.8)      3 (2.2)  
    Local anesthetics                                                                               3 (1.8)      3 (2.2)  
  Antiinflammatory agents and antiinfectives in combination                                         3 (1.8)         0     
    Corticosteroids and antiinfectives in combination                                               3 (1.8)         0     
  Surgical aids                                                                                     3 (1.8)         0     
    Viscoelastic substances                                                                         3 (1.8)         0

Yetr I expected:

Ophthalmologicals                                                                                  xx (xx.x)    xx (xx.x) 
  ......
    ......
  Antiinflammatory agents and antiinfectives in combination                                         3 (1.8)         0     
    Corticosteroids and antiinfectives in combination                                               3 (1.8)         0     
  Local anesthetics                                                                                 3 (1.8)      3 (2.2)  
    Local anesthetics                                                                               3 (1.8)      3 (2.2)  
  Surgical aids                                                                                     3 (1.8)         0     
    Viscoelastic substances                                                                         3 (1.8)         0
Melkiades commented 5 months ago

There is a complex way in {rtables} but your fastest guess is to reorder alphabetically the factor levels. See how here I changed the order of your example STRATA1:

library(rtables)
library(dplyr)

raw_lyt <- basic_table() %>%
  split_cols_by("ARM") %>%
  split_rows_by("SEX") %>%
  summarize_row_groups() %>%
  split_rows_by("RACE") %>%
  summarize_row_groups() %>%
  split_rows_by("STRATA1") %>%
  summarize_row_groups() %>%
  analyze("AGE")

DM_black <- DM[DM$RACE == "BLACK OR AFRICAN AMERICAN", ]
DM <- rbind(DM, DM_black, DM_black, DM_black, DM_black)
DM <- DM[1:100, ]

DM$STRATA1 <- factor(DM$STRATA1, levels = c("C", "B", "A"))
raw_tbl <- build_table(raw_lyt, DM)

coltrimmed <- raw_tbl[, col_counts(raw_tbl) > 0]

pruned <- prune_table(coltrimmed)
pruned

sort_at_path(pruned, path = c("SEX", "*", "RACE", "*", "STRATA1"), cont_n_onecol(3))
huanlugwu commented 5 months ago

So, what I'm doing is a typical ADCM table to summarize patient counts splited by ATC2/ATC3/ATC4

    rtables::split_rows_by("ATC2", section_div = " ") %>%
    rtables::summarize_row_groups() %>%
    rtables::split_rows_by("ATC3") %>%
    rtables::summarize_row_groups() %>%
    rtables::split_rows_by("ATC4") %>%
    rtables::summarize_row_groups()

I got issue like:

                                                                                                     TRT A        TRT B   
                                                                                                    (N=253)      (N=252)  
—————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————

Antithrombotic Agents                                                                              141 (55.6)   144 (57.0)
  Antithrombotic agents                                                                            141 (55.6)   144 (57.0)
    Platelet aggregation inhibitors excl. heparin                                                  137 (54.0)   138 (54.6)
    Vitamin k antagonists                                                                           5 (1.6)      1 (4.0) 
    Heparin group                                                                                   3 (0.8)      9 (3.2)  
    Direct factor xa inhibitors                                                                     3 (0.8)      5 (1.6)  
    Enzymes                                                                                         3 (0.8)      3 (0.8)  
    Direct thrombin inhibitors                                                                         0         4 (1.2)

I need to have everything sorted decreasingly in TRT A only by ATC2, then ATC3, then ATC4, in the example above, when ATC4 shared a same number, ATC4 cannot be sorted alphabetically, the same to ATC3 and ATC2. How can I address this issue? I tried to factorize ATC2, ATC3 and ATC4 with all unique values as their levels, but it did not work, am I trying in the right direction?

gmbecker commented 5 months ago

Specifying the order of the levels should be sufficient as shown in @Melkiades but we can't see the data or the full layout or the sorting call so its so its very difficult to know what is going on in your case

huanlugwu commented 5 months ago

Thank you @gmbecker , I made up some codes to explain my example:

library(formatters)
library(rtables)
library(dplyr)

raw_lyt <- basic_table() %>%
  split_cols_by("ARM") %>%
  split_rows_by("STRATA1", split_fun = drop_split_levels) %>%
  summarize_row_groups() %>%
  split_rows_by("CMCAT", split_fun = drop_split_levels) %>%
  summarize_row_groups() %>%
  split_rows_by("CMDECOD", split_fun = drop_split_levels) %>%
  summarize_row_groups()

raw_tbl <- build_table(raw_lyt, ex_adcm)
raw_tbl

Then you should be able to have:

> raw_tbl
                     A: Drug X    B: Placebo    C: Combination
——————————————————————————————————————————————————————————————
A                   179 (29.4%)   193 (31.0%)    218 (31.0%)  
  medcl A            59 (9.7%)    65 (10.5%)      87 (12.4%)  
    medname A_1/3    26 (4.3%)     19 (3.1%)      25 (3.6%)   
    medname A_2/3    14 (2.3%)     19 (3.1%)      31 (4.4%)   
    medname A_3/3    19 (3.1%)     27 (4.3%)      31 (4.4%)   
  medcl B           82 (13.5%)    82 (13.2%)      87 (12.4%)  
    medname B_1/4    16 (2.6%)     21 (3.4%)      24 (3.4%)   
    medname B_2/4    26 (4.3%)     26 (4.2%)      22 (3.1%)   
    medname B_3/4    20 (3.3%)     19 (3.1%)      17 (2.4%)   
    medname B_4/4    20 (3.3%)     16 (2.6%)      24 (3.4%)   
  medcl C            38 (6.2%)     46 (7.4%)      44 (6.3%)   
    medname C_1/2    17 (2.8%)     20 (3.2%)      22 (3.1%)   
    medname C_2/2    21 (3.4%)     26 (4.2%)      22 (3.1%)   
B                   207 (34.0%)   205 (33.0%)    249 (35.4%)  
  medcl A           74 (12.2%)    67 (10.8%)      76 (10.8%)  
    medname A_1/3    19 (3.1%)     27 (4.3%)      38 (5.4%)   
    medname A_2/3    37 (6.1%)     23 (3.7%)      21 (3.0%)   
    medname A_3/3    18 (3.0%)     17 (2.7%)      17 (2.4%)   
  medcl B           81 (13.3%)    99 (15.9%)     119 (16.9%)  
    medname B_1/4    25 (4.1%)     31 (5.0%)      36 (5.1%)   
    medname B_2/4    18 (3.0%)     29 (4.7%)      29 (4.1%)   
    medname B_3/4    18 (3.0%)     18 (2.9%)      22 (3.1%)   
    medname B_4/4    20 (3.3%)     21 (3.4%)      32 (4.6%)   
  medcl C            52 (8.5%)     39 (6.3%)      54 (7.7%)   
    medname C_1/2    26 (4.3%)     17 (2.7%)      26 (3.7%)   
    medname C_2/2    26 (4.3%)     22 (3.5%)      28 (4.0%)   
C                   223 (36.6%)   224 (36.0%)    236 (33.6%)  
  medcl A           72 (11.8%)    75 (12.1%)      79 (11.2%)  
    medname A_1/3    26 (4.3%)     24 (3.9%)      36 (5.1%)   
    medname A_2/3    25 (4.1%)     29 (4.7%)      27 (3.8%)   
    medname A_3/3    21 (3.4%)     22 (3.5%)      16 (2.3%)   
  medcl B           101 (16.6%)   94 (15.1%)     100 (14.2%)  
    medname B_1/4    34 (5.6%)     30 (4.8%)      23 (3.3%)   
    medname B_2/4    20 (3.3%)     18 (2.9%)      25 (3.6%)   
    medname B_3/4    21 (3.4%)     28 (4.5%)      29 (4.1%)   
    medname B_4/4    26 (4.3%)     18 (2.9%)      23 (3.3%)   
  medcl C            50 (8.2%)     55 (8.8%)      57 (8.1%)   
    medname C_1/2    28 (4.6%)     30 (4.8%)      27 (3.8%)   
    medname C_2/2    22 (3.6%)     25 (4.0%)      30 (4.3%)

My need is to sort by STRATA1 decreasingly then alphabetically only in arm "A: Drug X", then hierarchically by CMCAT, then by CMDECOD.

A trick part lies in here:

                     A: Drug X    B: Placebo    C: Combination
——————————————————————————————————————————————————————————————
A                   179 (29.4%)   193 (31.0%)    218 (31.0%)  
  ......  
  medcl B           82 (13.5%)    82 (13.2%)      87 (12.4%)  
    ...... 
    medname B_3/4    20 (3.3%)     19 (3.1%)      17 (2.4%)   
    medname B_4/4    20 (3.3%)     16 (2.6%)      24 (3.4%)

Where the total number of all splits columns of "medname B_4/4" is larger than "medname B_3/4", but since both of them share the same total number in arm "A: Drug X", "medname B_3/4" should be sorted before "medname B_4/4".

I hope I explained the issue clearly now, thanks in advance.

gmbecker commented 5 months ago 
srt <- sort_at_path(raw_tbl, c("STRATA1") , cont_n_onecol(1))
srt <- sort_at_path(srt, c("STRATA1", "*", "CMCAT"), cont_n_onecol(1))
srt <- sort_at_path(srt, c("STRATA1", "*", "CMCAT", "*", "CMDECOD"), cont_n_onecol(1))

appears to do what you want it to do, IIUC:

> srt
                     A: Drug X    B: Placebo    C: Combination
——————————————————————————————————————————————————————————————
C                   223 (36.6%)   224 (36.0%)    236 (33.6%)  
  medcl B           101 (16.6%)   94 (15.1%)     100 (14.2%)  
    medname B_1/4    34 (5.6%)     30 (4.8%)      23 (3.3%)   
    medname B_4/4    26 (4.3%)     18 (2.9%)      23 (3.3%)   
    medname B_3/4    21 (3.4%)     28 (4.5%)      29 (4.1%)   
    medname B_2/4    20 (3.3%)     18 (2.9%)      25 (3.6%)   
  medcl A           72 (11.8%)    75 (12.1%)      79 (11.2%)  
    medname A_1/3    26 (4.3%)     24 (3.9%)      36 (5.1%)   
    medname A_2/3    25 (4.1%)     29 (4.7%)      27 (3.8%)   
    medname A_3/3    21 (3.4%)     22 (3.5%)      16 (2.3%)   
  medcl C            50 (8.2%)     55 (8.8%)      57 (8.1%)   
    medname C_1/2    28 (4.6%)     30 (4.8%)      27 (3.8%)   
    medname C_2/2    22 (3.6%)     25 (4.0%)      30 (4.3%)   
B                   207 (34.0%)   205 (33.0%)    249 (35.4%)  
  medcl B           81 (13.3%)    99 (15.9%)     119 (16.9%)  
    medname B_1/4    25 (4.1%)     31 (5.0%)      36 (5.1%)   
    medname B_4/4    20 (3.3%)     21 (3.4%)      32 (4.6%)   
    medname B_2/4    18 (3.0%)     29 (4.7%)      29 (4.1%)   
    medname B_3/4    18 (3.0%)     18 (2.9%)      22 (3.1%)   
  medcl A           74 (12.2%)    67 (10.8%)      76 (10.8%)  
    medname A_2/3    37 (6.1%)     23 (3.7%)      21 (3.0%)   
    medname A_1/3    19 (3.1%)     27 (4.3%)      38 (5.4%)   
    medname A_3/3    18 (3.0%)     17 (2.7%)      17 (2.4%)   
  medcl C            52 (8.5%)     39 (6.3%)      54 (7.7%)   
    medname C_1/2    26 (4.3%)     17 (2.7%)      26 (3.7%)   
    medname C_2/2    26 (4.3%)     22 (3.5%)      28 (4.0%)   
A                   179 (29.4%)   193 (31.0%)    218 (31.0%)  
  medcl B           82 (13.5%)    82 (13.2%)      87 (12.4%)  
    medname B_2/4    26 (4.3%)     26 (4.2%)      22 (3.1%)   
    medname B_3/4    20 (3.3%)     19 (3.1%)      17 (2.4%)   
    medname B_4/4    20 (3.3%)     16 (2.6%)      24 (3.4%)   
    medname B_1/4    16 (2.6%)     21 (3.4%)      24 (3.4%)   
  medcl A            59 (9.7%)    65 (10.5%)      87 (12.4%)  
    medname A_1/3    26 (4.3%)     19 (3.1%)      25 (3.6%)   
    medname A_3/3    19 (3.1%)     27 (4.3%)      31 (4.4%)   
    medname A_2/3    14 (2.3%)     19 (3.1%)      31 (4.4%)   
  medcl C            38 (6.2%)     46 (7.4%)      44 (6.3%)   
    medname C_2/2    21 (3.4%)     26 (4.2%)      22 (3.1%)   
    medname C_1/2    17 (2.8%)     20 (3.2%)      22 (3.1%)

This is controlled by the factor level order as @Melkiades pointed to above, which we can see by reversing their order and then doing identical sorting:

adcm2 <- ex_adcm
adcm2$CMDECOD <- factor(adcm2$CMDECOD, levels = rev(levels(adcm2$CMDECOD)))

raw_tbl2 <- build_table(raw_lyt, adcm2)

srt2 <- sort_at_path(raw_tbl2, c("STRATA1") , cont_n_onecol(1))
srt2 <- sort_at_path(srt2, c("STRATA1", "*", "CMCAT"), cont_n_onecol(1))
srt2 <- sort_at_path(srt2, c("STRATA1", "*", "CMCAT", "*", "CMDECOD"), cont_n_onecol(1))

which gives us

> srt2
                     A: Drug X    B: Placebo    C: Combination
——————————————————————————————————————————————————————————————
C                   223 (36.6%)   224 (36.0%)    236 (33.6%)  
  medcl B           101 (16.6%)   94 (15.1%)     100 (14.2%)  
    medname B_1/4    34 (5.6%)     30 (4.8%)      23 (3.3%)   
    medname B_4/4    26 (4.3%)     18 (2.9%)      23 (3.3%)   
    medname B_3/4    21 (3.4%)     28 (4.5%)      29 (4.1%)   
    medname B_2/4    20 (3.3%)     18 (2.9%)      25 (3.6%)   
  medcl A           72 (11.8%)    75 (12.1%)      79 (11.2%)  
    medname A_1/3    26 (4.3%)     24 (3.9%)      36 (5.1%)   
    medname A_2/3    25 (4.1%)     29 (4.7%)      27 (3.8%)   
    medname A_3/3    21 (3.4%)     22 (3.5%)      16 (2.3%)   
  medcl C            50 (8.2%)     55 (8.8%)      57 (8.1%)   
    medname C_1/2    28 (4.6%)     30 (4.8%)      27 (3.8%)   
    medname C_2/2    22 (3.6%)     25 (4.0%)      30 (4.3%)   
B                   207 (34.0%)   205 (33.0%)    249 (35.4%)  
  medcl B           81 (13.3%)    99 (15.9%)     119 (16.9%)  
    medname B_1/4    25 (4.1%)     31 (5.0%)      36 (5.1%)   
    medname B_4/4    20 (3.3%)     21 (3.4%)      32 (4.6%)   
    medname B_3/4    18 (3.0%)     18 (2.9%)      22 (3.1%)   
    medname B_2/4    18 (3.0%)     29 (4.7%)      29 (4.1%)   
  medcl A           74 (12.2%)    67 (10.8%)      76 (10.8%)  
    medname A_2/3    37 (6.1%)     23 (3.7%)      21 (3.0%)   
    medname A_1/3    19 (3.1%)     27 (4.3%)      38 (5.4%)   
    medname A_3/3    18 (3.0%)     17 (2.7%)      17 (2.4%)   
  medcl C            52 (8.5%)     39 (6.3%)      54 (7.7%)   
    medname C_2/2    26 (4.3%)     22 (3.5%)      28 (4.0%)   
    medname C_1/2    26 (4.3%)     17 (2.7%)      26 (3.7%)   
A                   179 (29.4%)   193 (31.0%)    218 (31.0%)  
  medcl B           82 (13.5%)    82 (13.2%)      87 (12.4%)  
    medname B_2/4    26 (4.3%)     26 (4.2%)      22 (3.1%)   
    medname B_4/4    20 (3.3%)     16 (2.6%)      24 (3.4%)   
    medname B_3/4    20 (3.3%)     19 (3.1%)      17 (2.4%)   
    medname B_1/4    16 (2.6%)     21 (3.4%)      24 (3.4%)   
  medcl A            59 (9.7%)    65 (10.5%)      87 (12.4%)  
    medname A_1/3    26 (4.3%)     19 (3.1%)      25 (3.6%)   
    medname A_3/3    19 (3.1%)     27 (4.3%)      31 (4.4%)   
    medname A_2/3    14 (2.3%)     19 (3.1%)      31 (4.4%)   
  medcl C            38 (6.2%)     46 (7.4%)      44 (6.3%)   
    medname C_2/2    21 (3.4%)     26 (4.2%)      22 (3.1%)   
    medname C_1/2    17 (2.8%)     20 (3.2%)      22 (3.1%)
huanlugwu commented 5 months ago

Thank you @gmbecker and @Melkiades , I detected my issue is due to factor levels, after I factorize every single levels alphabetically in every single split row variable, I can prevent sort_at_path sorting table looking into other columns, many many thanks to you!