1
> df <- data.frame(g=c(1,2,3,1,2,3), foo_x=c(12,32,14,23,12,34), foo_y=c(32,35,12,12,32,34), bar_x=c(100,233,122,433,122,442), bar_y=c(744,546,236,137,652,235))

I'm trying to create this data frame:

g  foo  bar val_foo  val_bar
1   x    x     12      100
2   x    x     32      233
3   x    x     14      122
1   x    y     32      744
2   x    y     35      546
3   x    y     12      236
1   y    x     23      433
2   y    x     12      122
3   y    x     34      442
1   y    y     12      137
2   y    y     32      652
3   y    y     34      235

I think I should be using the melt function, but how can I create two different variable (foo and bar) and value (val_foo and val_bar) columns? melt(df, id.vars="g") only creates a single variable/value pair.

HappyPy
  • 7,017
  • 10
  • 32
  • 56

1 Answers1

4

We can use pivot_longer for multiple sets of columns

library(dplyr)
library(tidyr)
df %>%
   pivot_longer(cols = -g, names_to = c(".value", "grp"), names_sep="_")

Or with melt from data.table

library(data.table)
melt(setDT(df), measure = patterns("^foo", "^bar"), 
      value.name = c("val_foo", "val_bar"))

Or as @markus mentioned with base R

reshape(df, idvar = "g", varying = list(2:3, 4:5), sep = "_",
    direction = "long", v.names = c("val_foo", "val_bar"), new.row.names = 1:12)
akrun
  • 674,427
  • 24
  • 381
  • 486
  • 4
    To complete the holy trinity: `reshape(df, idvar = "g", varying = list(2:3, 4:5), sep = "_", direction = "long", v.names = c("val_foo", "val_bar"), new.row.names = 1:12)` – markus Nov 29 '19 at 22:49