CODEWITHSUNDEEP
rdplyrpivottidyr
stucash
stucash

Reputation: 1258

multiple variables tidyr pivoting

I Have a tibble called test looking like this:

   date       series_id.x  value.x series_id.y value.y
   <date>     <chr>        <dbl>   <chr>       <dbl>
 1 2020-08-03 ABC           0.1      ^FHJ          0.078
 2 2020-07-27 ABC           0.105    ^FHJ          0.093
 3 2020-07-20 ABC           0.12     ^FHJ          0.105
 4 2020-07-13 ABC           0.145    ^FHJ          0.125
 5 2020-07-06 ABC           0.15     ^FHJ          0.128
 6 2020-06-29 ABC           0.15     ^FHJ          0.125
 7 2020-06-22 ABC           0.155    ^FHJ          0.14 
 8 2020-06-15 ABC           0.175    ^FHJ          0.155
 9 2020-06-08 ABC           0.17     ^FHJ          0.153
10 2020-06-01 ABC           0.15     ^FHJ          0.128

I was trying to pivot this tibble into longer series of data, where series_id.x and series_id.y become series_id, and value.x and value.y become value, date column essentially remains unchanged (but actually can be used to do grouping as each date now has two series associated with it, i.e series and value)

I've read the tidyr pivoting guide on google and have tried a few permutations, none of them worked.

Typically I got:

test %>% tidyr::pivot_longer(cols=contains(c("x","y")), names_to="series_id", values_to="value")

test %>% tidyr::pivot_longer(-date, names_to="series_id", values_to="value")

test %>% tidyr::pivot_longer(-date, names_to=c("series_id", "value"), names_pattern="(.)(.)")

test %>% tidyr::pivot_longer(cols = starts_with("series_id"), names="series_id", values="value")

All above were failed attempts. I know I missed something, but it's not clear from guide whether my use-case is possible at all.

Upvotes: 3

Views: 62

Answers (1)

akrun
akrun

Reputation: 886948

Here, we may need to specify the delimiter which is .. By default, it is in regex mode i.e. . can match any character, so we escape (\\). Also, as there are multiple set of columns, specify the names_to as a vector of .value and the column name

library(dplyr)
library(tidyr)
test %>%
    pivot_longer(cols = matches('\\.[xy]'),
      names_to = c('.value', '.series_id'), values_to = 'value', names_sep="\\.")

Upvotes: 1

Related Questions