Pivot longer syntax

user_14764540 0
Viewed 60

I have the following data format that seems like a very simple pivot_longer should result in the desired tall dataframe, but I'm not getting there. Here is a simplified sample of the the original dataframe (HW) and the desired tall dataframe (HWT). How can I get from HW to HWT with a pivot_longer()? Perhaps I need to use names_pattern instead of names_sep? I played with that but regex is still a mystery to me.

    HW <- tribble(
  ~Subject,  ~Pre.Height,  ~Post1.Height, ~Post2.Height, ~Pre.Weight,  ~Post1.Weight, ~Post2.Weight,
  "A", 110.0, 113.5, 120.0, 18.1, 20.0, 24.4,
  "B", 95.5, 124.5, 129.7, 15.2, 24.6, 27.2,
  "C", 116, 125.8, 136.2, 21, 29.9, 39.9,
  "D", 131, 135.8, 155, 24.3, 26.7, 38.8,
  "E", 145.6, 154, 158.5, 41.3, 48.1, 63.5,
  "F", 121.5, 140, 147.9, 22.8, 36.5, 51.5)
  
HW

HWT <- tribble(
  ~Subject,  ~Time,  ~Height,  ~Weight,
  "A", "Pre", 110.0, 18.1,
  "A", "Post1", 113.5, 20.0, 
  "A", "Post2", 120.0, 24.4,
  "B", "Pre", 95.5, 15.2, 
  "B", "Post1", 124.5, 24.6, 
  "B", "Post2", 129.7, 127.2,
  "C", "Pre", 116, 21,
  "C", "Post1", 125.8,  29.9, 
  "C", "Post2", 136.2, 39.9,
  "D", "Pre", 131, 24.3, 
  "D", "Post1", 135.8, 26.7,
  "D", "Post2", 155, 38.8,
  "E", "Pre", 145.6,  41.3,
  "E", "Post1", 154, 148.1, 
  "E", "Post2", 158.5, 63.5,
  "F", "Pre", 121.5, 122.8, 
  "F", "Post1", 140, 36.5, 
  "F", "Post2", 147.9, 51.5)

# Intuitively I think this should work, names are taken from separator = "Pre", "Post1", "Post2",
# these are store in column Time, associated values parsed and stored in Height/Weight
HW %>% pivot_longer(cols = !Subject,
                    names_sep = ".",
                    names_to = c("Time"),
                    values_to = c("Height", "Weight")
                    )
# Of course this throws an error, I don't believe you can have multiple values_to columns

# Starting simple:
HW %>% pivot_longer(cols = !Subject)


# Tried this but it doesn't help:
HW %>% pivot_longer(cols = !Subject,
                    names_sep = ".",
                    names_to = c("Height", "Weight")
)
tidyr r
1 Answers

Two issues i.e. 1) by default the names_sep will parse in regex mode i.e. . is regarded as metacharacter in regex to match any character. So escape it (\\), 2). As we are specifying the names_sep, there are two components, which should be specified in 'names_to' i.e. in addition to 'Time', the value component i.e. .value which should be after the 'Time' as the Time part will take the prefix part in column name before the .

library(dplyr)
library(tidyr)
HW %>% 
     pivot_longer(cols = -Subject, names_sep = "\\.",
          names_to = c("Time", ".value"))

-output

# A tibble: 18 x 4
   Subject Time  Height Weight
   <chr>   <chr>  <dbl>  <dbl>
 1 A       Pre    110     18.1
 2 A       Post1  114.    20  
 3 A       Post2  120     24.4
 4 B       Pre     95.5   15.2
 5 B       Post1  124.    24.6
 6 B       Post2  130.    27.2
 7 C       Pre    116     21  
 8 C       Post1  126.    29.9
 9 C       Post2  136.    39.9
10 D       Pre    131     24.3
11 D       Post1  136.    26.7
12 D       Post2  155     38.8
13 E       Pre    146.    41.3
14 E       Post1  154     48.1
15 E       Post2  158.    63.5
16 F       Pre    122.    22.8
17 F       Post1  140     36.5
18 F       Post2  148.    51.5
user_37322710
Related