CODEWITHSUNDEEP
rreshape
Rosanne
Rosanne

Reputation: 660

Complicated reshape from wide to long in r

I have data frame that looks like this:

stocks <- tibble(
  ID = 1:10,
  time1 = as.Date('2009-01-01'),
  time2 = as.Date('2009-01-02'),
  time3 = as.Date('2009-01-03'),
  X1 = rnorm(10, 0, 1),
  X2 = rnorm(10, 0, 1),
  X3 = rnorm(10, 0, 1),
  Y1 = rnorm(10, 0, 1),
  Y2 = rnorm(10, 0, 1),
  Y3 = rnorm(10, 0, 1),
  Z1 = rnorm(10, 0, 1),
  Z2 = rnorm(10, 0, 1),
  Z3 = rnorm(10, 0, 4)
)

My goal is to create a data frame like this:

goal <- tibble(
  ID =  rep(1, 12),
  Time = c(rep(as.Date('2009-01-01'), 9), rep(as.Date('2009-01-02'), 3)),
  Group = c("X1", "X2", "X3", "Y1", "Y2", "Y3", "Z1", "Z2", "Z3", "X1", "X2", "X3"),
  Numbers = c(-0.46805576,  0.8490237,  -1.1647371, -0.7519070, -1.0930102, -1.19820707,    0.302461964,
              -1.48164386, -10.0257282, -1.03852259,    -0.4597602, -0.8727067)  
)

I have been struggeling with gather and melt, but the problem seems that I want to gather the Time in a different column than the rest of the data. Does anyone know how to do this?

Thanks in advance

Upvotes: 3

Views: 72

Answers (1)

akrun
akrun

Reputation: 886968

An option would be to use pivot_longer to reshape into 'long' format with one column for 'time', 'X', 'Y', 'Z', then do a second pivot_longer on the 'X', 'Y', 'Z' columns 

library(dplyr)
library(tidyr)
stocks %>%
    pivot_longer(cols = -ID, names_to = c(".value", "Group"),
           names_sep= "(?<=[^0-9])(?=[0-9])") %>% 
    pivot_longer(cols = X:Z, values_to = "Numbers") %>%
    unite(Group, name, Group, sep="")
# A tibble: 90 x 4
#      ID Group time       Numbers
#   <int> <chr> <date>       <dbl>
# 1     1 X1    2009-01-01   1.05 
# 2     1 Y1    2009-01-01   0.525
# 3     1 Z1    2009-01-01  -0.590
# 4     1 X2    2009-01-02   0.195
# 5     1 Y2    2009-01-02  -0.858
# 6     1 Z2    2009-01-02  -1.03 
# 7     1 X3    2009-01-03  -0.417
# 8     1 Y3    2009-01-03  -0.746
# 9     1 Z3    2009-01-03   1.01 
#10     2 X1    2009-01-01  -0.971

Upvotes: 4

Related Questions