Pivot Longer with Modification of Columns

November 16, 2021

I have data that is in the following format:

(data <- tribble(
  ~Date, ~ENRSxOPEN, ~ENRSxCLOSE, ~INFTxOPEN, ~INFTxCLOSE,
  "1989-09-11",82.97,82.10,72.88,72.56,
  "1989-09-12",83.84,83.96,73.52,72.51,
  "1989-09-13",83.16,83.88,72.91,72.12))

# A tibble: 3 x 5
  Date       ENRSxOPEN ENRSxCLOSE INFTxOPEN INFTxCLOSE
  <chr>          <dbl>      <dbl>     <dbl>      <dbl>
1 1989-09-11      83.0       82.1      72.9       72.6
2 1989-09-12      83.8       84.0      73.5       72.5
3 1989-09-13      83.2       83.9      72.9       72.1

For analysis, I want to pivot this tibble longer to the following format:

tribble(
  ~Ticker, ~Date, ~OPEN, ~CLOSE,
  "ENRS","1989-09-11",82.97,82.10,
  "ENRS","1989-09-12",83.84,83.96,
  "ENRS","1989-09-13",83.16,83.88,
  "INFT","1989-09-11",72.88,72.56,
  "INFT","1989-09-12",73.52,72.51,
  "INFT","1989-09-13",72.91,72.12)

# A tibble: 3 x 5
  Date       ENRSxOPEN ENRSxCLOSE INFTxOPEN INFTxCLOSE
  <chr>          <dbl>      <dbl>     <dbl>      <dbl>
1 1989-09-11      83.0       82.1      72.9       72.6
2 1989-09-12      83.8       84.0      73.5       72.5
3 1989-09-13      83.2       83.9      72.9       72.1

I.e., I want to separate the Open/Close prices from the ticker, and put the latter as an entirely new column in the beginning.

I’ve tried to use the function pivot_longer:

pivot_longer(data, cols = ENRSxOPEN:INFTxCLOSE)

While this goes into the direction of what I wanna achieve, it does not separate the prices and keep them in one row for each Ticker.
Is there a way to add additional arguments to pivot_longer()to achieve that?

>Solution :

pivot_longer(data, -Date, names_to = c('Ticker', '.value'), names_sep = 'x')

# A tibble: 6 x 4
   Date Ticker  OPEN CLOSE
  <dbl> <chr>  <dbl> <dbl>
1  1969 ENRS    83.0  82.1
2  1969 INFT    72.9  72.6
3  1968 ENRS    83.8  84.0
4  1968 INFT    73.5  72.5
5  1967 ENRS    83.2  83.9
6  1967 INFT    72.9  72.1