Convert Data Table Columns to Rows to Create Presentable Tables in R-CodePudding

Hi so as I continue to try to learn R, I'm having trouble understanding how to tidy up my data. So in the end I'd love to have a presentable table that resembles this one-ish. (Omitting the average and improvement columns)

This is the data table I have currently:

  group = c("Group 1", "Group 2", "Group 3", "Group 4", "Group 5",
                    "Group 6", "Group 7", "Group 8", "Group 9"),
  yes = c("9", "9", "9", "12", "12", "12", "10", "10", "10"),
  no = c("9", "9", "9", "6", "6", "6", "8", "8", "8"), 
  perc = c(0.5, 0.5, 0.5, 0.67, 0.67, 0.67, 0.56, 0.56, 0.56))

dt

 group yes no perc
1: Group 1   9  9 0.50
2: Group 2   9  9 0.50
3: Group 3   9  9 0.50
4: Group 4  12  6 0.67
5: Group 5  12  6 0.67
6: Group 6  12  6 0.67
7: Group 7  10  8 0.56
8: Group 8  10  8 0.56
9: Group 9  10  8 0.56

However, I'd like it reformatted where I could have the "yes" and "no" columns to become rows under the column name of "responses" and the perc column, which represents the percentage of people who said "yes" to also become the final row after the "responses" row

Response Group 1 Group 2 Group 3 Group 4 Group 5...
yes      9       9       9       12      12 
no       9       9       9       6       6 
per      0.50    0.50    0.50    0.67    0.67

Any help on this would be appreciated! Thank you!

CodePudding user response：

Here is tidyverse solution:

library(dplyr)
library(tidyr)

dt %>% 
  pivot_longer(c(yes, no, perc),
               names_to = "key",
               values_to = "val", 
               values_transform = list(val = as.character)
               ) %>% 
  pivot_wider(names_from = group, values_from = val) %>% 
  mutate(across(-key, as.numeric))

 key   `Group 1` `Group 2` `Group 3` `Group 4` `Group 5` `Group 6` `Group 7` `Group 8` `Group 9`
  <chr>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>     <dbl>
1 yes         9         9         9       12        12        12        10        10        10   
2 no          9         9         9        6         6         6         8         8         8   
3 perc        0.5       0.5       0.5      0.67      0.67      0.67      0.56      0.56      0.56

CodePudding user response：

You are using data.table hence you can do:

transpose(dt,keep.names = 'Response',make.names = 'group')

   Response Group 1 Group 2 Group 3 Group 4 Group 5 Group 6 Group 7 Group 8 Group 9
1:      yes       9       9       9      12      12      12      10      10      10
2:       no       9       9       9       6       6       6       8       8       8
3:     perc     0.5     0.5     0.5    0.67    0.67    0.67    0.56    0.56    0.56

Note that if you have other libraries loaded eg purrr ensure to load transpose from the data.table package using the scope resolution operator ie data.table::transpose(...)