Turning data long to wide with repeating values

Question

  fill       W   id       X        T
    1       403 29730    100      111
    1      8395 10766    100       92
    1      4170 14291    100       98
    1      2768 20506    200      110
    1      3581 15603    100      112
    6         1 10504    200       87
    9       48  29730    100       89
    1     4790  10766    200       80

This is a slightly modified random sample from my actual data. I'd like:

 id       X        T   403 8395 ....
 29730    100      111   1
 10766    100       92       1  
 14291    100       98
 20506    200      110
 15603    100      112
 10504    200       87
 29730    100       89
 10766    200       80

Notice ID 29730 is both in T 89 and 111. I think this should just be reshape2::dcast however

data_wide <- reshape2::dcast(data_long, id + T + X ~ W, value.var = "fill") gives an illogical result. Is there generally a to keep the same ID at T1 and T2 while casting a data frame?

Isn't this just a reshaping problem? `tidyr::spread(df, W, fill)` Possible duplicate of https://stackoverflow.com/questions/5890584/how-to-reshape-data-from-long-to-wide-format — Ronak Shah, Oct 01 '19 at 01:00

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

If I understand correctly, this is not a trivial reshape long to wide question considering OP's requirements:

The row order must be maintained.
The columns must be ordered in appearance of W.
Missing entries should appear blank rather than NA.

This requires

to add a row number to be included in the reshape formula,
to turn W into a factor where the factor levels are ordered by appearance using forecats::fct_inorder(), e.g.,
to use a aggregation function which turns NA in "" using toString(), e.g.,
and to remove the row numbers from the reshaped result.

Here, the data.table implementation of dcast() is used as data.table appears a bit more convenient, IMHO.

library(data.table)
dcast(setDT(data_long)[, rn := .I], rn + id + T + X  ~ forcats::fct_inorder(factor(W)), 
      toString, value.var = "fill")[
        , rn := NULL][]

      id   T   X 403 8395 4170 2768 3581 1 48 4790
1: 29730 111 100   1                              
2: 10766  92 100        1                         
3: 14291  98 100             1                    
4: 20506 110 200                  1               
5: 15603 112 100                       1          
6: 10504  87 200                         6        
7: 29730  89 100                            9     
8: 10766  80 200                                 1

Data

library(data.table)
data_long <- fread("  fill       W   id       X        T
    1       403 29730    100      111
    1      8395 10766    100       92
    1      4170 14291    100       98
    1      2768 20506    200      110
    1      3581 15603    100      112
    6         1 10504    200       87
    9       48  29730    100       89
    1     4790  10766    200       80")

Turning data long to wide with repeating values

1 Answers1

Data