r convert a wide format to a summary format

Question

If this is my dataframe

   Factors       Group       n     Average     Max      Min
   Calcium       Above       1599  0.412       42.872   0.017
   Calcium       Below       1040  0.011       0.017    -0.01
   Lead          Above       1345  1.312       0.043    0.037
   Lead          Below       882   0.614       64.65    0.065

I am trying to reorganize this to summary format like this, not sure where to start. Any advice is appreciated. Thanks in advance.

                    Group
  Factor      Above          Below     
  Calcium
        n    1599(60.5%)     1040(39.4%)
  Average    0.412           0.011
  Max,Min    42.872,0.017    0.017,-0.01 
                 

     Lead 
        n    1345(60.4%)     882(39.6%)
  Average    1.312           0.614 
  Max,Min    0.043,0.037     64.65, 0.065


              

Answer 1

Not precisely the requested format, but pretty close.

library(dplyr)
library(tidyr)

data <- read.table(header = TRUE, text = "
  Factors       Group       n     Average     Max      Min
  Calcium       Above       1599  0.412       42.872   0.017
  Calcium       Below       1040  0.011       0.017    -0.01
  Lead          Above       1345  1.312       0.043    0.037
  Lead          Below       882   0.614       64.65    0.065
")

data %>%
  rename(Factor = Factors) %>%
  group_by(Factor) %>%
  mutate(
    n = sprintf("%d (%.1f%%)", n, n / sum(n) * 100),
    "Max,Min" = paste(Max, Min, sep = ","),
    Average = as.character(Average)
  ) %>%
  select(-Max, -Min) %>%
  pivot_longer(n:last_col()) %>%
  pivot_wider(names_from = Group, values_from = value)

  Factor  name    Above        Below       
1 Calcium n       1599 (60.6%) 1040 (39.4%)
2 Calcium Average 0.412        0.011       
3 Calcium Max,Min 42.872,0.017 0.017,-0.01 
4 Lead    n       1345 (60.4%) 882 (39.6%) 
5 Lead    Average 1.312        0.614       
6 Lead    Max,Min 0.043,0.037  64.65,0.065