tab_transpose with nested cells

[zelihay]

Hi Mr. Demin,

I need to transpose the percent table. _tabtranspose works very well if I have only one question in _tabcells. However, I have to nest the second question, in this case, the Week column. But it generates the wrong result. Is it possible to use tab_transpose with nested cells? Many thanks.

Data %>% tab_prepend_all %>% tab_cols(Index1) %>% tab_cells(Q1 %nest% Week) %>% tab_stat_cpct(total_row_position = "none") %>%
tab_pivot()%>% tab_transpose()

[gdemin]

Sorry, I don't see what is wrong:

library(expss)
data(mtcars)
mtcars = apply_labels(mtcars,
                      vs = "Engine",
                      vs = num_lab("
                             0 V-engine 
                             1 Straight engine
                             "),
                      cyl = "Cylinders",
                      am = "Transmission",
                      am = num_lab("
                             0 Automatic 
                             1 Manual
                             ")
)

mtcars %>%
    tab_prepend_all %>%
    tab_cols(cyl) %>%
    tab_cells(vs %nest% am) %>%
    tab_stat_cpct(total_row_position = "none") %>%
    tab_pivot()%>%
    tab_transpose()

# |               |    |       vs Engine |          |                   |          |
# |               |    |      0 V-engine |          | 1 Straight engine |          |
# |               |    | am Transmission |          |   am Transmission |          |
# |               |    |     0 Automatic | 1 Manual |       0 Automatic | 1 Manual |
# | ------------- | -- | --------------- | -------- | ----------------- | -------- |
# | cyl Cylinders |  4 |                 |      9.1 |              27.3 |     63.6 |
# |               |  6 |                 |     42.9 |              57.1 |          |
# |               |  8 |            85.7 |     14.3 |                   |          |

[zelihay]

Sorry to bother you. I noticed that I made a mistake; I need to put the nested variable in _tabcols. When I do that, the nested part is in rows in the table naturally. Is it possible to show the nested question (am) under the variable vs without putting it in _tabcells? I could not put the nested part in _tabcells as it gives the wrong percents. The only solution I found is to put the nested question both in _tabcols and _tabcells. In that case, in the output, I have to move each cell manually above. Maybe, it is impossible to do that. It is hard to explain though.

mtcars %>% tab_prepend_all %>% tab_cols(cyl %nest% am) %>% tab_cells(vs) %>% tab_stat_cpct(total_row_position = "none") %>% tab_pivot()%>% tab_transpose()

[gdemin]

Is it what do you want?

library(expss)
expss_output_commented()

data(mtcars)
mtcars = apply_labels(mtcars,
                      vs = "Engine",
                      vs = num_lab("
                             0 V-engine 
                             1 Straight engine
                             "),
                      cyl = "Cylinders",
                      am = "Transmission",
                      am = num_lab("
                             0 Automatic 
                             1 Manual
                             ")
)
mtcars %>%
    tab_prepend_all %>%
    tab_cols(cyl) %>%
    tab_cells(vs) %>%
    tab_rows(am) %>%
    tab_stat_rpct(total_row_position = "none") %>%
    tab_pivot()

# |                 |             |           |                   | cyl Cylinders |      |       |
# |                 |             |           |                   |             4 |    6 |     8 |
# | --------------- | ----------- | --------- | ----------------- | ------------- | ---- | ----- |
# | am Transmission | 0 Automatic | vs Engine |        0 V-engine |               |      | 100.0 |
# |                 |             |           | 1 Straight engine |          42.9 | 57.1 |       |
# |                 |    1 Manual | vs Engine |        0 V-engine |          16.7 | 50.0 |  33.3 |
# |                 |             |           | 1 Straight engine |         100.0 |      |       |

Or, could you provide screenshot of the table which you do manually?

[zelihay]

Here is the table:

If I move every cell one cell up in Week2, Week3 columns, that would be OK. Of course, I need to remove empty rows in this case.

_data %>% tab_prepend_all %>% tab_cols(Index1 %nest% Week) %>% tab_cells(Q1 %nest% Week) %>% tab_stat_cpct(total_row_position = "none") %>%
tab_pivot()%>% drop_empty_rows()%>% tabtranspose()

Thanks for your help.

[gdemin]

There is no ready-made option for such column reordering. But it is quite easy to reorder columns in the table becuase it is usual data.frame:

data(mtcars)
mtcars = apply_labels(mtcars,
                      vs = "Engine",
                      vs = num_lab("
                             0 V-engine 
                             1 Straight engine
                             "),
                      cyl = "Cylinders",
                      am = "Transmission",
                      am = num_lab("
                             0 Automatic 
                             1 Manual
                             ")
)

swap_nested_colnames = function(tab){
    colnames(tab) = gsub("^(.+?)\\|(.+?)\\|(.+?)\\|(.+?)$", "\\3|\\4|\\1|\\2", colnames(tab))
    tab
}

reorder_columns = function(tab){
    curr_names = colnames(tab)[-1]
    first_layer =  gsub("^(.+?)\\|(.+?)\\|(.+?)\\|(.+?)$", "\\1|\\2", curr_names)
    first_layer_reorder = order(match(first_layer, unique(first_layer)))
    tab[,c(1, first_layer_reorder + 1)]
}

mtcars %>%
    tab_prepend_all %>%
    tab_cols(cyl %nest% am) %>%
    tab_cells(vs) %>%
    tab_stat_rpct(total_row_position = "none") %>%
    tab_pivot() %>% 
    swap_nested_colnames() %>% 
    reorder_columns()

# |           |                   | am Transmission |      |      |               |      |      |
# |           |                   |     0 Automatic |      |      |      1 Manual |      |      |
# |           |                   |   cyl Cylinders |      |      | cyl Cylinders |      |      |
# |           |                   |               4 |    6 |    8 |             4 |    6 |    8 |
# | --------- | ----------------- | --------------- | ---- | ---- | ------------- | ---- | ---- |
# | vs Engine |        0 V-engine |                 |      | 66.7 |           5.6 | 16.7 | 11.1 |
# |           | 1 Straight engine |            21.4 | 28.6 |      |          50.0 |      |      |

[zelihay]

Thanks for your time. Although I could not make it work for my case, I'll use different methods out of R. Thanks again.