Is there a way in R to get table1 to calculate percentages by group based on total sum of a column,-CodePudding

I'm working in R, looking for a way to make a nice table. For each first name or last name, I want the percentage of all individuals counted at each site who have that name, and the sum of individuals with that name next to the percentage. This seems to only calculate percentages based on the number of rows, rather than the actual count data. Not necessarily married to table1 if there are other functions/packages that would do the trick!

Here is some example code:

set.seed(123)
    dat <- data.frame(
      site = factor(sample(c("A", "B", "C", "D"), 100, replace = TRUE)),
      count = (sample(1:150, 100, replace = TRUE)),
      first.name = factor(sample(c("John", "Sue", "Bob", "Mary", "Cara"), 100, replace = TRUE)),
      last.name =  factor(sample(c("Williams", "Smith", "Lee"), 100, replace = TRUE)))
    
library(table1)
tab<- table1(~ last.name   first.name|site, #only counts rows, doesn't sum "Count" column
                                data = dat, 
                                render.continuous = ("Sum(Count) (PCTnoNA=100*Count/Sum(Count))"),
                                digits= 1) 

tab

Any help is appreciated, and thank you!

CodePudding user response：

Update: With the hint of dcarlson(many thanks!) here is a gtsummary solution:

library(gtsummary)
library(dplyr)
dat.big %>%
  tbl_summary(by = site) %>% 
  add_overall()

Are you looking for such a solution:

table1(~ last.name   first.name|site, data=dat)

CodePudding user response：

You will have to trick table1 into giving you what you want by making a data frame that has sum(dat$count) == 6873 rows. That is not difficult:

idx <- rep(rownames(dat), dat$count)
length(idx)
# [1] 6873
dat.big <- dat[idx, ]
table1(~ last.name   first.name|site, data = dat.big)