I would like to have an expss banner table with column percents, a total row at the very top with weighted valid n, and a total row below each stub with weighted valid percent. I have tried adding two statistics like below, however I end up duplicating the rows. Is there a way I can remove the duplicated rows? Or add two totals rows for one statistic?
I am writing to excel using expss::xl_write and I tried using remove_repeated = "rows" with no success. It only removed the first repeated row. I also am going to be creating a lot of these with the same banners using functions.
This is when I tried adding the statistic twice:
mtcars = apply_labels(mtcars,
cyl = "Number of cylinders",
am = "Transmission",
am = c("Automatic" = 0,
"Manual"=1),
period = "Measurement period",
vs = "Engine",
vs = c("V-Shaped" = 0,
"Straight"=1)
)
mtcars %>%
tab_cells(cyl, vs) %>%
tab_cols(total(), am) %>%
tab_stat_cpct(total_row_position = c("above"),
total_statistic = c("w_responses"),
total_label = "Total N") %>%
tab_stat_cpct(total_row_position = c("below"),
total_statistic = c("w_cpct"),
total_label = "Total %") %>%
tab_pivot()
| | | #Total | Transmission | |
| | | | Automatic | Manual |
| ------------------- | -------- | ------ | ------------ | ------ |
| Number of cylinders | #Total N | 32.0 | 19.0 | 13.0 |
| | 4 | 34.4 | 15.8 | 61.5 |
| | 6 | 21.9 | 21.1 | 23.1 |
| | 8 | 43.8 | 63.2 | 15.4 |
| Engine | #Total N | 32.0 | 19.0 | 13.0 |
| | V-Shaped | 56.2 | 63.2 | 46.2 |
| | Straight | 43.8 | 36.8 | 53.8 |
| Number of cylinders | 4 | 34.4 | 15.8 | 61.5 |
| | 6 | 21.9 | 21.1 | 23.1 |
| | 8 | 43.8 | 63.2 | 15.4 |
| | #Total % | 100.0 | 100.0 | 100.0 |
| Engine | V-Shaped | 56.2 | 63.2 | 46.2 |
| | Straight | 43.8 | 36.8 | 53.8 |
| | #Total % | 100.0 | 100.0 | 100.0 |
I tried splitting the table in two, like on this question.
mtcars %>%
tab_cols(total(), am) %>%
tab_cells(cyl) %>%
tab_stat_cpct(total_row_position = c("above"),
total_statistic = c("w_responses"),
total_label = "Total N") %>%
tab_cells(vs) %>%
tab_stat_cpct(total_row_position = c("below"),
total_statistic = c("w_cpct"),
total_label = "Total %") %>%
tab_pivot()
And I get this, which is close but I don't get the column percent on both.
| | | #Total | Transmission | |
| | | | Automatic | Manual |
| ------------------- | -------- | ------ | ------------ | ------ |
| Number of cylinders | #Total N | 32.0 | 19.0 | 13.0 |
| | 4 | 34.4 | 15.8 | 61.5 |
| | 6 | 21.9 | 21.1 | 23.1 |
| | 8 | 43.8 | 63.2 | 15.4 |
| Engine | V-Shaped | 56.2 | 63.2 | 46.2 |
| | Straight | 43.8 | 36.8 | 53.8 |
| | #Total % | 100.0 | 100.0 | 100.0 |
This is what I want:
| | | #Total | Transmission | |
| | | | Automatic | Manual |
| ------------------- | -------- | ------ | ------------ | ------ |
| Number of cylinders | #Total N | 32.0 | 19.0 | 13.0 |
| | 4 | 34.4 | 15.8 | 61.5 |
| | 6 | 21.9 | 21.1 | 23.1 |
| | 8 | 43.8 | 63.2 | 15.4 |
| | #Total % | 100.0 | 100.0 | 100.0 |
| Engine | V-Shaped | 56.2 | 63.2 | 46.2 |
| | Straight | 43.8 | 36.8 | 53.8 |
| | #Total % | 100.0 | 100.0 | 100.0 |
It seems, the simplest method is to use subtotal:
mtcars %>%
tab_cols(total(), am) %>%
# other() means all other categories. If use it alone it will use all categories.
tab_cells(subtotal(cyl, "#Total %" = other()), subtotal(vs, "#Total %" = other())) %>%
tab_stat_cpct(total_row_position = c("above"),
total_statistic = c("w_responses"),
total_label = c("Total N")) %>%
tab_pivot()
UPDATE: Sorry, I misunderstood you question. The appropriate answer is:
mtcars %>%
tab_cols(total(), am) %>%
# block especially for top total
tab_cells(total(cyl, label = "#Total N")) %>%
tab_stat_cases(total_row_position = "none") %>%
# end of block
tab_cells(cyl, vs) %>%
tab_stat_cpct(total_row_position = c("below"),
total_statistic = c("w_cpct"),
total_label = c("Total %")) %>%
tab_pivot()