Make each column a row with values per group-CodePudding

How would I transform the input data into the output data below? For each Grp1 I would like the metrics columns to become rows with a column for each value of Grp2. Thanks

Input Data

 structure(list(Grp1 = c("A", "A", "B", "B"), Grp2 = c("test", 
    "ctrl", "test", "ctrl"), metric1 = c(10L, 50L, 40L, 30L), metric2 = c(10L, 
    20L, 45L, 40L), metric3 = c(15L, 20L, 30L, 40L), metric4 = 1:4), class = "data.frame", row.names = c(NA, 
    -4L))

Desired Output

structure(list(Grp1 = c("A", "A", "A", "A", "B", "B", "B", "B"
), metric = c("metric1", "metric2", "metric3", "metric4", "metric1", 
"metric2", "metric3", "metric4"), test = c(10L, 10L, 15L, 1L, 
40L, 45L, 30L, 3L), ctrl = c(50L, 20L, 20L, 2L, 30L, 40L, 40L, 
4L)), class = "data.frame", row.names = c(NA, -8L))

CodePudding user response：

We could reshape the 'metric' columns to 'long' first and then reshape back to wide using Grp2 as the column names (names_from)

library(dplyr)
library(tidyr)
df1 %>% 
   pivot_longer(cols = starts_with('metric'), names_to = 'metric') %>%
   pivot_wider(names_from = Grp2, values_from = value)

-output

# A tibble: 8 × 4
  Grp1  metric   test  ctrl
  <chr> <chr>   <int> <int>
1 A     metric1    10    50
2 A     metric2    10    20
3 A     metric3    15    20
4 A     metric4     1     2
5 B     metric1    40    30
6 B     metric2    45    40
7 B     metric3    30    40
8 B     metric4     3     4

CodePudding user response：

and the data .table alternative goes:

library(data.table)
setDT(DT)
DT.melt <- melt(DT, id.vars = c("Grp1", "Grp2"), variable.name = "metric")
dcast(DT.melt, Grp1   metric ~ Grp2, value.var = 'value')
#    Grp1  metric ctrl test
# 1:    A metric1   50   10
# 2:    A metric2   20   10
# 3:    A metric3   20   15
# 4:    A metric4    2    1
# 5:    B metric1   30   40
# 6:    B metric2   40   45
# 7:    B metric3   40   30
# 8:    B metric4    4    3