use pivot_longer to created multiple value columns

user_5837689 0
Viewed 48

Can you specify multiple value columns in pivot_longer()?

My original data (posted below) I had to transpose to be in a wider format. Then I want to take this new transposed data and return it to the original format (lets assume I did some transformations/ and can't use the original data). To re-transpose back into a longer format I have to use both pivot_longer() then pivot_wider() because there are multiple values I want to be their own columns.

I would like to avoid the pivot_wider() and just use pivot_longer() when re-transposing the data back if possible.

As a side note the unique identifier for each row is the combination of id and report.

Code

dfa <- dfx %>% 
  pivot_wider(
    id_cols = id,
    names_from = report,
    values_from = c(pts,
                    p1, p2, p3,p4,p5,
                    d1,d2,d3,d4,d5)
  )


df_return <- dfa %>% 
  pivot_longer(cols = !id,
               names_to = c('vars','report'),
               names_pattern = "([a-z0-9]+)_(.*)",
               values_drop_na = TRUE) %>% 
  pivot_wider(id_cols = c(id, report),
              names_from = vars,
              values_from = value)

Data

structure(list(pts = c(431L, 167L, 167L, 760L, 348L, 768L, 619L, 
169L, 416L, 155L, 47L, 37L, 6L, 17L, 22L, 1L, 149L, 3L, 284L, 
7L), d1 = c(129L, 48L, 52L, 166L, 90L, 178L, 184L, 20L, 158L, 
42L, 3L, 15L, 2L, 7L, 9L, 0L, 54L, 1L, 69L, 6L), d2 = c(172L, 
67L, 64L, 257L, 132L, 255L, 261L, 30L, 201L, 61L, 9L, 20L, 2L, 
9L, 12L, 0L, 69L, 1L, 123L, 6L), d3 = c(205L, 77L, 73L, 312L, 
153L, 307L, 310L, 39L, 235L, 70L, 12L, 21L, 2L, 10L, 12L, 0L, 
77L, 2L, 139L, 6L), d4 = c(227L, 81L, 82L, 363L, 177L, 350L, 
342L, 52L, 257L, 75L, 15L, 24L, 2L, 12L, 13L, 0L, 86L, 2L, 151L, 
6L), d5 = c(248L, 88L, 92L, 414L, 192L, 387L, 374L, 66L, 279L, 
86L, 16L, 26L, 2L, 12L, 15L, 0L, 90L, 3L, 164L, 7L), report = c("2006", 
"2006", "2006", "2006", "2006", "2006", "2006", "2006", "2006", 
"2006", "2006", "2006", "2006", "2006", "2006", "2006", "2006", 
"2006", "2006", "2006"), p1 = c(1.0360364394094, 1.22979866735429, 
1.21423740998677, 0.87891144382145, 0.810310827130179, 0.965901663505148, 
1.02621739486337, 0.69319116444678, 1.18938130906092, 1.04220816515009, 
0.683545688193799, 1.05179228560845, 1.51468104603873, 1.15200888955888, 
0.948041330809858, 0, 1.23227405154205, 3.11155226007598, 0.908056299174703, 
1.57712371536702), p2 = c(0.986884800185635, 1.23066225499351, 
1.07336930339221, 0.966734485786667, 0.87421381769247, 0.974775549615439, 
1.06274655160121, 0.705150638862953, 1.12934487417415, 1.10234720984265, 
1.11084642794988, 1.06558505521222, 1.0197697665798, 1.15605466288868, 
1.01469386643771, 0, 1.17689541437029, 1.42783711234222, 1.16124019281912, 
1.27756288696848), p3 = c(0.993575954694177, 1.17968893104311, 
1.02608313159672, 0.965200422661265, 0.862910478266102, 0.976436243011877, 
1.06679768502287, 0.722966824498357, 1.12591016481614, 1.05867627021151, 
1.11227024088529, 0.98275117259764, 0.803738347803303, 1.09341228936369, 
0.878291424560146, 0, 1.10500006213832, 1.93128861370172, 1.0949534752299, 
1.14755029569502), p4 = c(0.986244633210798, 1.08520792731261, 
1.01128789684232, 0.977245321880205, 0.89785754450165, 0.981536130349165, 
1.04454959427709, 0.807825580390444, 1.1035817255901, 1.00192975678877, 
1.14371311954082, 1.02812279984398, 0.66742040677939, 1.15526702119886, 
0.878479047328667, 0, 1.10559111180852, 1.4717526513624, 1.05479137550321, 
1.07005088091939), p5 = c(0.992583778223324, 1.06016737802091, 
1.02253158347207, 1.00026491073882, 0.896290873874826, 0.985549150023704, 
1.04187931404895, 0.886647217836043, 1.09837506943384, 1.0323002052873, 
1.05833769015682, 1.05042831618603, 0.592515872759586, 1.05106420250504, 
0.961672664191663, 0, 1.05868657273466, 1.81304485775152, 1.04168095802127, 
1.19437925124365), id = c("ID 1", "ID 2", "ID 3", "ID 4", "ID 5", 
"ID 6", "ID 7", "ID 8", "ID 9", "ID 10", "ID 11", "ID 12", "ID 13", 
"ID 14", "ID 15", "ID 16", "ID 17", "ID 18", "ID 19", "ID 20"
)), row.names = c(NA, 20L), class = "data.frame")
tidyr dplyr r
1 Answers

We may need the .value in the names_to, which selects the prefix part of the column name before the _ as the column value and the 'report' will return the suffix column name

library(tidyr)
pivot_longer(dfa, cols = -id, names_to = c(".value", "report"), 
      names_sep = "_")

-output

# A tibble: 20 × 13
   id    report   pts    p1    p2    p3    p4    p5    d1    d2    d3    d4    d5
   <chr> <chr>  <int> <dbl> <dbl> <dbl> <dbl> <dbl> <int> <int> <int> <int> <int>
 1 ID 1  2006     431 1.04  0.987 0.994 0.986 0.993   129   172   205   227   248
 2 ID 2  2006     167 1.23  1.23  1.18  1.09  1.06     48    67    77    81    88
 3 ID 3  2006     167 1.21  1.07  1.03  1.01  1.02     52    64    73    82    92
 4 ID 4  2006     760 0.879 0.967 0.965 0.977 1.00    166   257   312   363   414
 5 ID 5  2006     348 0.810 0.874 0.863 0.898 0.896    90   132   153   177   192
 6 ID 6  2006     768 0.966 0.975 0.976 0.982 0.986   178   255   307   350   387
 7 ID 7  2006     619 1.03  1.06  1.07  1.04  1.04    184   261   310   342   374
 8 ID 8  2006     169 0.693 0.705 0.723 0.808 0.887    20    30    39    52    66
 9 ID 9  2006     416 1.19  1.13  1.13  1.10  1.10    158   201   235   257   279
10 ID 10 2006     155 1.04  1.10  1.06  1.00  1.03     42    61    70    75    86
11 ID 11 2006      47 0.684 1.11  1.11  1.14  1.06      3     9    12    15    16
12 ID 12 2006      37 1.05  1.07  0.983 1.03  1.05     15    20    21    24    26
13 ID 13 2006       6 1.51  1.02  0.804 0.667 0.593     2     2     2     2     2
14 ID 14 2006      17 1.15  1.16  1.09  1.16  1.05      7     9    10    12    12
15 ID 15 2006      22 0.948 1.01  0.878 0.878 0.962     9    12    12    13    15
16 ID 16 2006       1 0     0     0     0     0         0     0     0     0     0
17 ID 17 2006     149 1.23  1.18  1.11  1.11  1.06     54    69    77    86    90
18 ID 18 2006       3 3.11  1.43  1.93  1.47  1.81      1     1     2     2     3
19 ID 19 2006     284 0.908 1.16  1.09  1.05  1.04     69   123   139   151   164
20 ID 20 2006       7 1.58  1.28  1.15  1.07  1.19      6     6     6     6     7
user_37322710
Related