Changing structure of rows in columns of table in R

Question

I have a table that starts out looking like this:

test <- data.frame("Season" = c("Winter", "Winter", "Winter", "Winter", "Winter", "Winter"),
       "Site" = c(123,123,123,123,123,123),
       "Flow" = c("H001", "H007", "H030", "L001", "L007", "L030"),
       "P-Value" = c(0.05, 0.05, 0.05, 0.05, 0.05, 0.05),
       "H0" = c(1, 1, 1, 1, 1, 1),
       "Stat" = c(0.4, 0.4, 0.4, 0.4, 0.4, 0.4),
       "Slope" = c(1, 1, 1, 1, 1, 1))

And I want to change the rows/columns around so the table looks like this:

test_2 <- data.frame("Site" = c(123), "P-Value for H001 winter" = c(0.05), "P-Value for H007 winter" = c(0.05),
                 "P-Value for H030 winter" = c(0.05), "P-Value for L001 winter" = c(0.05), "P-Value for L007 winter" = c(0.05),
                 "P-Value for L030 winter" = c(0.05), "H0 for H001 winter" = c(1), "H0 for H007 winter" = c(1), "H0 for H030 winter" = c(1),
                 "H0 for L001 winter" = c(1), "H0 for L007 winter" = c(1), "H0 for L030 winter" = c(1),
                 "Stat for H001 winter" = c(0.04), "Stat for H007 winter" = c(0.04), "Stat for H030 winter" = c(0.04),
                 "Stat for L001 winter" = c(0.04), "Stat for L007 winter" = c(0.04), "Stat for L030 winter" = c(0.04),
                 "Slope for H001 winter" = c(1), "Slope for H007 winter" = c(1), "Slope for H030 winter" = c(1),
                 "Slope for L001 winter" = c(1), "Slope for L007 winter" = c(1), "Slope for L030 winter" = c(1))

I've tried many different functions within dplyr and I can't get it exactly how I want it.

Thank you!

Answer 1

I guess you want this:

test %>%
  pivot_longer(cols=c(P.Value, H0, Stat, Slope)) %>%
  mutate(name=paste(name, "for", Flow, Season)) %>%
  select(-Flow, -Season) %>%
  pivot_wider()

which returns

structure(list(Site = 123, `P.Value for H001 Winter` = 0.05, 
    `H0 for H001 Winter` = 1, `Stat for H001 Winter` = 0.4, `Slope for H001 Winter` = 1, 
    `P.Value for H007 Winter` = 0.05, `H0 for H007 Winter` = 1, 
    `Stat for H007 Winter` = 0.4, `Slope for H007 Winter` = 1, 
    `P.Value for H030 Winter` = 0.05, `H0 for H030 Winter` = 1, 
    `Stat for H030 Winter` = 0.4, `Slope for H030 Winter` = 1, 
    `P.Value for L001 Winter` = 0.05, `H0 for L001 Winter` = 1, 
    `Stat for L001 Winter` = 0.4, `Slope for L001 Winter` = 1, 
    `P.Value for L007 Winter` = 0.05, `H0 for L007 Winter` = 1, 
    `Stat for L007 Winter` = 0.4, `Slope for L007 Winter` = 1, 
    `P.Value for L030 Winter` = 0.05, `H0 for L030 Winter` = 1, 
    `Stat for L030 Winter` = 0.4, `Slope for L030 Winter` = 1), row.names = c(NA, 
-1L), class = "data.frame")