rexpss

rearrange cpct out in r expss table from row to columns


I'm using the R expss package to calculate the below. Any help is much appreciated.

df <- data.frame(repid=c(1:5), 
             q1=c(1,3,2,1,3), 
             q2=c(2,3,1,1,2), 
             q3=c(2,2,3,1,2))
df %>% tab_cells(q1, q2, q3) %>% 
   tab_stat_cpct(total_row_position = "none") %>%  
   tab_pivot()

# output table from the code above
#       #Total 
# q1 
#   1   40
#   2   20
#   3   40
# q2 
#   1   40
#   2   40
#   3   20
# q3 
#   1   20
#   2   60
#   3   20
#
#
# needed table
#       1   2    3
# q1   40  20   40 
# q2   40  40   20
# q3   20  60   20

I tried the tab_transpose capability within the expss package.


Solution

  • I managed to find a solution.

    library(expss)
    df <- data.frame(repid=c(1:5), 
                 q1=c(1,3,2,1,3), 
                 q2=c(2,3,1,1,2), 
                 q3=c(2,2,3,1,2))
    Out=df %>% tab_cells(q1, q2, q3) %>% 
       tab_stat_cpct(total_row_position = "none") %>%  
       tab_pivot()
    
    Out2=split_table_to_df(Out, remove_repeated = FALSE)
    colnames(Out2) <- c('Metric', 'Level', 'Value')
    Out2 <- Out2[-1,]  # Removes first row 
    
    library(tidyverse)
    Out3 <- Out2 %>% 
            pivot_wider(id_cols = Metric, names_from = Level, values_from = Value)