Wide to long with multiple variables-CodePudding

I'm looking for some help with transforming wide to long when there are two nested variables (B and R) with repeating targets (Item1, Item2, and Item3) in R. And there are some between-subject variables (e.g., X).

x <- data.frame(ID = c(1,2,3),X = c(2.4,2.3,2.1),Item1_B = c(24,15,31),Item2_B = c(54,66,23),Item3_B = c(23,24,65),Item1_R = c(.2,.3,.1),Item2_R = c(.4,.2,.6),Item3_R = c(.2,.4,.2))

Thanks!

CodePudding user response：

The one-step approach would be to use the keyword ".value" in names_to

library(tidyr)

pivot_longer(x, starts_with("Item"), 
            names_pattern = "(\\d )_(.*)",
            names_to = c("Item", ".value"))
#> # A tibble: 9 x 5
#>      ID     X Item      B     R
#>   <dbl> <dbl> <chr> <dbl> <dbl>
#> 1     1   2.4 1        24   0.2
#> 2     1   2.4 2        54   0.4
#> 3     1   2.4 3        23   0.2
#> 4     2   2.3 1        15   0.3
#> 5     2   2.3 2        66   0.2
#> 6     2   2.3 3        24   0.4
#> 7     3   2.1 1        31   0.1
#> 8     3   2.1 2        23   0.6
#> 9     3   2.1 3        65   0.2

CodePudding user response：

Here is a two-step process, but the tidyverse experts will have a better approach!

library(tidyr)

df %>% 
  pivot_longer(-(id:X),names_to=c("Item", "type"), names_sep="_",names_prefix = "Item") %>% 
  pivot_wider(names_from="type", values_from="value")

Output:

     ID     X Item      B     R
  <dbl> <dbl> <chr> <dbl> <dbl>
1     1   2.4 1        24   0.2
2     1   2.4 2        54   0.4
3     1   2.4 3        23   0.2
4     2   2.3 1        15   0.3
5     2   2.3 2        66   0.2
6     2   2.3 3        24   0.4
7     3   2.1 1        31   0.1
8     3   2.1 2        23   0.6
9     3   2.1 3        65   0.2

CodePudding user response：

Same as @Allan Cameron except using names_pattern:

pivot_longer(x, starts_with("Item"),
             names_pattern = '(.*)_(.*)', 
             names_to = c("Item", ".value")
             )

    ID     X Item      B     R
  <dbl> <dbl> <chr> <dbl> <dbl>
1     1   2.4 Item1    24   0.2
2     1   2.4 Item2    54   0.4
3     1   2.4 Item3    23   0.2
4     2   2.3 Item1    15   0.3
5     2   2.3 Item2    66   0.2
6     2   2.3 Item3    24   0.4
7     3   2.1 Item1    31   0.1
8     3   2.1 Item2    23   0.6
9     3   2.1 Item3    65   0.2