r - Count number of patient with percentage with row-wise denominator by paired-column - Stack Overflow

admin管理员组

文章数量:1122846

I am working on this demanding table to count number of patients:

• in columns: with and without flag
• in rows: by country and site with different fomats, AND with different denominators in row within same ARM.

To reproduce the outcome, you can try this:

library(dplyr)
library(rtables)
library(forcats)

adsl_1 <- ex_adsl %>%
  select(USUBJID, ARM, ARMCD, BMEASIFL, COUNTRY, SITEID) %>%
  dplyr::mutate(
    COUNTRY = as.factor(COUNTRY),
    ARM = as.factor(ARM),
    LAYER1 = "Group 1",
    LAYER1 = factor(LAYER1)
  ) %>%
  dplyr::mutate(ARM = forcats::fct_reorder(ARM, rank(ARMCD))) %>%
  arrange(SITEID) %>%
  dplyr::mutate(SITEID = forcats::fct_inorder(SITEID))    

site_in_use <- adsl_1 %>%
  select(COUNTRY, SITEID) %>%
  distinct() %>%
  arrange(COUNTRY, SITEID) %>%
  mutate(COUNTRY=as.character(COUNTRY),
         SITEID=as.character(SITEID))

adsl_2 <- adsl_1 %>%
  filter(BMEASIFL == "Y") %>%
  dplyr::mutate(
    # LAYER1 to display the first row in header
    LAYER1 = "Group 2",
    LAYER1 = factor(LAYER1)
  )

cfun_cntrysite <- function(df, labelstr, .N_col, ...) {
  in_rows(
    rcell(nrow(df), format = "xx"),
    .labels = labelstr
  )
}

tbl_recipe <- basic_table() %>%
  rtables::split_cols_by(var = "LAYER1") %>%
  rtables::split_cols_by(var = "ARM") %>%
  rtables::summarize_row_groups(cfun = cfun_cntrysite) %>%
  rtables::split_rows_by("COUNTRY", split_fun = trim_levels_to_map(map = site_in_use)) %>%
  rtables::summarize_row_groups() %>%
  rtables::split_rows_by("SITEID", split_fun = trim_levels_to_map(map = site_in_use)) %>%
  rtables::summarize_row_groups()

tbl1 <- tbl_recipe %>%
  rtables::build_table(adsl_1)

tbl_recipe2 <- basic_table() %>%
  rtables::split_cols_by(var = "LAYER1") %>%
  rtables::split_cols_by(var = "ARM")%>%
  rtables::summarize_row_groups(cfun = cfun_cntrysite) %>%
  rtables::split_rows_by("COUNTRY", split_fun = trim_levels_to_map(map = site_in_use)) %>%
  rtables::summarize_row_groups() %>%
  rtables::split_rows_by("SITEID", split_fun = trim_levels_to_map(map = site_in_use)) %>%
  rtables::summarize_row_groups() 

tbl2 <- tbl_recipe2 %>%
  rtables::build_table(adsl_2)

t <- cbind_rtables(tbl1, tbl2)
t

then got this:

> t
                             Group 1                                    Group 2                 
             A: Drug X    B: Placebo   C: Combination   A: Drug X    B: Placebo   C: Combination
————————————————————————————————————————————————————————————————————————————————————————————————
                134          134            132             68           73             62      
  CHN        74 (55.2%)   81 (60.4%)     64 (48.5%)     35 (51.5%)   44 (60.3%)     25 (40.3%)  
    CHN-1    21 (15.7%)   20 (14.9%)     16 (12.1%)     10 (14.7%)   14 (19.2%)     9 (14.5%)   

yet what I need is that, in a row, with same ARM, the fraction of group2 is counted by using the number in group1 as denominator.

ideally, I need to have this:

> t
                             Group 1                                    Group 2                 
             A: Drug X    B: Placebo   C: Combination   A: Drug X    B: Placebo   C: Combination
————————————————————————————————————————————————————————————————————————————————————————————————
                134          134            132             68           73             62      
  CHN        74 (55.2%)   81 (60.4%)     64 (48.5%)     35 (47.3%)   44 (54.3%)     25 (39.1%)  
    CHN-1    21 (15.7%)   20 (14.9%)     16 (12.1%)     10 (47.6%)   14 (70.0%)     9 (56.3%)   

can anyone shine a light on this? thx!

I am working on this demanding table to count number of patients:

• in columns: with and without flag
• in rows: by country and site with different fomats, AND with different denominators in row within same ARM.

To reproduce the outcome, you can try this:

library(dplyr)
library(rtables)
library(forcats)

adsl_1 <- ex_adsl %>%
  select(USUBJID, ARM, ARMCD, BMEASIFL, COUNTRY, SITEID) %>%
  dplyr::mutate(
    COUNTRY = as.factor(COUNTRY),
    ARM = as.factor(ARM),
    LAYER1 = "Group 1",
    LAYER1 = factor(LAYER1)
  ) %>%
  dplyr::mutate(ARM = forcats::fct_reorder(ARM, rank(ARMCD))) %>%
  arrange(SITEID) %>%
  dplyr::mutate(SITEID = forcats::fct_inorder(SITEID))    

site_in_use <- adsl_1 %>%
  select(COUNTRY, SITEID) %>%
  distinct() %>%
  arrange(COUNTRY, SITEID) %>%
  mutate(COUNTRY=as.character(COUNTRY),
         SITEID=as.character(SITEID))

adsl_2 <- adsl_1 %>%
  filter(BMEASIFL == "Y") %>%
  dplyr::mutate(
    # LAYER1 to display the first row in header
    LAYER1 = "Group 2",
    LAYER1 = factor(LAYER1)
  )

cfun_cntrysite <- function(df, labelstr, .N_col, ...) {
  in_rows(
    rcell(nrow(df), format = "xx"),
    .labels = labelstr
  )
}

tbl_recipe <- basic_table() %>%
  rtables::split_cols_by(var = "LAYER1") %>%
  rtables::split_cols_by(var = "ARM") %>%
  rtables::summarize_row_groups(cfun = cfun_cntrysite) %>%
  rtables::split_rows_by("COUNTRY", split_fun = trim_levels_to_map(map = site_in_use)) %>%
  rtables::summarize_row_groups() %>%
  rtables::split_rows_by("SITEID", split_fun = trim_levels_to_map(map = site_in_use)) %>%
  rtables::summarize_row_groups()

tbl1 <- tbl_recipe %>%
  rtables::build_table(adsl_1)

tbl_recipe2 <- basic_table() %>%
  rtables::split_cols_by(var = "LAYER1") %>%
  rtables::split_cols_by(var = "ARM")%>%
  rtables::summarize_row_groups(cfun = cfun_cntrysite) %>%
  rtables::split_rows_by("COUNTRY", split_fun = trim_levels_to_map(map = site_in_use)) %>%
  rtables::summarize_row_groups() %>%
  rtables::split_rows_by("SITEID", split_fun = trim_levels_to_map(map = site_in_use)) %>%
  rtables::summarize_row_groups() 

tbl2 <- tbl_recipe2 %>%
  rtables::build_table(adsl_2)

t <- cbind_rtables(tbl1, tbl2)
t

then got this:

> t
                             Group 1                                    Group 2                 
             A: Drug X    B: Placebo   C: Combination   A: Drug X    B: Placebo   C: Combination
————————————————————————————————————————————————————————————————————————————————————————————————
                134          134            132             68           73             62      
  CHN        74 (55.2%)   81 (60.4%)     64 (48.5%)     35 (51.5%)   44 (60.3%)     25 (40.3%)  
    CHN-1    21 (15.7%)   20 (14.9%)     16 (12.1%)     10 (14.7%)   14 (19.2%)     9 (14.5%)   

yet what I need is that, in a row, with same ARM, the fraction of group2 is counted by using the number in group1 as denominator.

ideally, I need to have this:

> t
                             Group 1                                    Group 2                 
             A: Drug X    B: Placebo   C: Combination   A: Drug X    B: Placebo   C: Combination
————————————————————————————————————————————————————————————————————————————————————————————————
                134          134            132             68           73             62      
  CHN        74 (55.2%)   81 (60.4%)     64 (48.5%)     35 (47.3%)   44 (54.3%)     25 (39.1%)  
    CHN-1    21 (15.7%)   20 (14.9%)     16 (12.1%)     10 (47.6%)   14 (70.0%)     9 (56.3%)   

can anyone shine a light on this? thx!

• r
• nest
• nest-rtables
• nest-tern

Share Improve this question Follow asked Nov 21, 2024 at 16:40 Huan LuHuan Lu 2511 silver badge44 bronze badges 1

• Have you tried the table1 package? It's pretty flexible, though it might not do exactly what you want. It's worth a shot. – Rick Hass Commented Nov 21, 2024 at 19:49

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

It's possible to build this table by creating a custom analysis function and using it in your summarize_row_groups() calls. Custom analysis functions are able to leverage the .spl_context argument, which extracts information about the current table context including previous column data.

To create this table:

library(rtables)

adsl <- ex_adsl %>%
  dplyr::select(USUBJID, ARM, ARMCD, BMEASIFL, COUNTRY, SITEID) %>%
  dplyr::mutate(
    COUNTRY = as.factor(COUNTRY),
    ARM = as.factor(ARM),
    LAYER1 = "Group 1",
    LAYER1 = factor(LAYER1)
  ) %>%
  dplyr::mutate(ARM = forcats::fct_reorder(ARM, rank(ARMCD))) %>%
  dplyr::arrange(SITEID) %>%
  dplyr::mutate(SITEID = forcats::fct_inorder(SITEID))    

df <- adsl %>% 
  rbind(
    adsl %>%
      dplyr::filter(BMEASIFL == "Y") %>%
      dplyr::mutate(
        # LAYER1 to display the first row in header
        LAYER1 = "Group 2",
        LAYER1 = factor(LAYER1)
      )
  )

site_in_use <- adsl %>%
  dplyr::select(COUNTRY, SITEID) %>%
  dplyr::distinct() %>%
  dplyr::arrange(COUNTRY, SITEID) %>%
  dplyr::mutate(
    COUNTRY = as.character(COUNTRY),
    SITEID = as.character(SITEID)
  )

## custom analysis function
cfun_custom_denom <- function(df, labelstr, .N_col, .spl_context, ...) {
  denom <- if (.spl_context$cur_col_split_val[[1]][1] == "Group 2") {
    .spl_context[[paste0("Group 1.", .spl_context$cur_col_split_val[[1]][2])]] %>%
      tail(1) %>%
      unlist() %>%
      sum()
  } else {
    .N_col
  }
  rcell(c(nrow(df), nrow(df) / max(denom, 1)), format = "xx (xx.x%)", label = labelstr)
}

tbl_recipe <- basic_table() %>%
  split_cols_by("LAYER1") %>%
  split_cols_by("ARM") %>%
  summarize_row_groups(format = "xx") %>%
  split_rows_by("COUNTRY", split_fun = trim_levels_to_map(map = site_in_use)) %>%
  summarize_row_groups(cfun = cfun_custom_denom) %>%
  split_rows_by("SITEID") %>%
  summarize_row_groups(cfun = cfun_custom_denom)

t <- tbl_recipe %>% build_table(df)

head(t, 3)
#>                             Group 1                                    Group 2                 
#>             A: Drug X    B: Placebo   C: Combination   A: Drug X    B: Placebo   C: Combination
#> ———————————————————————————————————————————————————————————————————————————————————————————————
#>                134          134            132             68           73             62      
#>   CHN       74 (55.2%)   81 (60.4%)     64 (48.5%)     35 (47.3%)   44 (54.3%)     25 (39.1%)  
#>     CHN-1   21 (15.7%)   20 (14.9%)     16 (12.1%)     10 (47.6%)   14 (70.0%)     9 (56.2%)

^{Created on 2024-11-21 with reprex v2.1.1}

Note that the custom analysis function I created in the example above is highly specific to this table and any changes to the table structure are likely to necessitate changes to the analysis function as well.

For more information on creating custom analysis functions read the following {rtables} articles:

• Custom split functions
• The .spl_context argument

本文标签： rCount number of patient with percentage with rowwise denominator by pairedcolumnStack Overflow