CODEWITHSUNDEEP
r
randmlaber
randmlaber

Reputation: 149

Wide to long with multiple variables

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).

enter image description here

or

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!

Upvotes: 0

Views: 64

Answers (3)

Allan Cameron
Allan Cameron

Reputation: 174546

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

Upvotes: 3

TarJae
TarJae

Reputation: 79246

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

Upvotes: 1

langtang
langtang

Reputation: 24845

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

Upvotes: 1

Related Questions