problem with reshape wide to long, multiple columns and multiple time periods-CodePudding

I'm trying to reshape data from wide to long and I cant get the answer. The structure is as follows:

id	n_dep_1	n_dep_2	bleed_1	bleed_2	secretion_1	secretion_2	eva_1	eva_2
1	1	2	1	1	1	1	9	7
2	2	1	0	1	1	0	5	6
3	2	1	1	0	1	1	7	5

note: i have 14 measurements of every key variable "n_dep","bleed","secretion" and "eva".

I need something like this:

id	n_dep	bleed	secretion	eva
1	1	1	1	9
1	2	1	1	7
2	2	0	1	5
2	1	1	0	6
3	2	1	1	7
3	1	0	1	5

I tried this:

require(data.table)
melt(setDT(data_wide), id="id",
     measure=patterns("n_dep_$", "eva_$", "bleed_$","secretion_$"), 
     value.name=c("n_dep", "eva", "bleed","secretion"))

and I got this error:

Error in do_patterns(measure.sub, names(data)) : Patterns not found: [n_dep_$, eva_$, bleed_$, secretion_$]

I also tried with reshape and gather, and I can't make it work.

Please help.

CodePudding user response：

An easier way to do this is with a single call to pivot_longer from tidyr:

tidyr::pivot_longer(data_wide, -id, names_pattern = '(^.*)_', names_to = ".value") 

#> # A tibble: 6 x 5
#>      id n_dep bleed secretion   eva
#>   <int> <int> <int>     <int> <int>
#> 1     1     1     1         1     9
#> 2     1     2     1         1     7
#> 3     2     2     0         1     5
#> 4     2     1     1         0     6
#> 5     3     2     1         1     7
#> 6     3     1     0         1     5

CodePudding user response：

It is not data.table, but with the tidyverse you can do this:

library(tidyverse)
d |> 
  gather("var", "val", -id) |> 
  mutate(id2 = str_extract(var, "\\d$"), 
         var = str_remove(var, "_\\d$")) |> 
  spread(var, val)
#> # A tibble: 6 × 6
#>      id id2   bleed   eva n_dep secretion
#>   <dbl> <chr> <dbl> <dbl> <dbl>     <dbl>
#> 1     1 1         1     9     1         1
#> 2     1 2         1     7     2         1
#> 3     2 1         0     5     2         1
#> 4     2 2         1     6     1         0
#> 5     3 1         1     7     2         1
#> 6     3 2         0     5     1         1

^{Created on 2022-03-28 by the reprex package (v2.0.1)}