How to pivot_longer with prefix to ID column

bmacwilliams 0
Viewed 45

My data often contain either "Left/Right" or "Pre/Post" prefixes without separators in a wide format that I need to pivot to tall format combining variables by these prefixes. I have a work around of using "gsub()" to insert a separator ("_" or ".") into the column names. "pivot_longer" then does what I want with the "names_sep" argument. I'm wondering though if there is a way to make this work more directly with "pivot_longer" "names" syntax ("names_prefix", "names_pattern", "names_to"). Here is what I am attempting:

Original wide format example:

HW <- tribble(
  ~Subject,  ~LeftV1,  ~RightV1, ~LeftV2, ~RightV2,  ~LeftV3, ~RightV3,
  "A", 0, 1, 10, 11, 100, 101,
  "B", 2, 3, 12, 13, 102, 103,
  "C", 4, 5, 14, 15, 104, 105)

Desired tall format:

HWT <- tribble(
  ~Subject,  ~Side, ~V1,  ~V2,  ~V3,
  "A", "Left", 0, 10, 100,
  "A", "Right", 1, 11, 101, 
  "B", "Left", 2, 12, 102,
  "B", "Right", 3, 13, 103,
  "C", "Left", 4, 14, 104,
  "C", "Right", 5, 15, 105)

I've tried various iterations of syntax that look more or less like this:

HWT <- HW %>% pivot_longer(
  cols = contains(c("Left", "Right")),
  names_pattern = "/^(Left|Right)",
  names_to =  c('Side', '.value') )

or this:

HWT <- HW %>% pivot_longer(
  cols = contains(c("Left", "Right")),
  names_prefix = "/^(Left|Right)",
  names_to =  c('Side', '.value') )

Each of which give syntax errors that I am unsure how to resolve.

tidyr dplyr r
2 Answers

We could use

library(tidyr)
library(dplyr)
HW %>% 
   pivot_longer(cols = -Subject, names_to = c("Side", ".value"),
   names_pattern = "^(Left|Right)(.*)")
# A tibble: 6 × 5
  Subject Side     V1    V2    V3
  <chr>   <chr> <dbl> <dbl> <dbl>
1 A       Left      0    10   100
2 A       Right     1    11   101
3 B       Left      2    12   102
4 B       Right     3    13   103
5 C       Left      4    14   104
6 C       Right     5    15   105
akrun0

Here is a similar approach concerning pivot_longer but with another strategy. I find it easier to understand if we could a simple separate like _. For this we could use rename_with and str_replace before pivoting:

librayr(dplyr)
library(stringr)

HW %>% 
  rename_with(., ~str_replace_all(., 'V', '_V')) %>% 
  pivot_longer(-Subject,
               names_to =c("Side", ".value"),
               names_sep ="_")

# A tibble: 6 x 5
  Subject Side     V1    V2    V3
  <chr>   <chr> <dbl> <dbl> <dbl>
1 A       Left      0    10   100
2 A       Right     1    11   101
3 B       Left      2    12   102
4 B       Right     3    13   103
5 C       Left      4    14   104
6 C       Right     5    15   105
TarJae0
Related