Fast table pivoting from long to wide and from wide to long.
These functions are supported by dcast.data.table and melt.data.table
from data.table.
longer(.data, ..., name = "name", value = "value", na.rm = FALSE)
wider(.data, ..., name, value = NULL, fun = NULL, fill = NA)A data.table
Columns for unchanged group. Flexible, see examples.
Name for the measured variable names column.
Name for the data values column(s).
If TRUE, NA values will be removed from the molten data.
Should the data be aggregated before casting?
Defaults to NULL, which uses length for aggregation.
If a function is provided, with aggregated by this function.
Value with which to fill missing cells. Default uses NA.
A data.table
stocks <- data.table(
time = as.Date('2009-01-01') + 0:9,
X = rnorm(10, 0, 1),
Y = rnorm(10, 0, 2),
Z = rnorm(10, 0, 4)
)
stocks %>% longer(time)
#> time name value
#> <Date> <fctr> <num>
#> 1: 2009-01-01 X -1.400043517
#> 2: 2009-01-02 X 0.255317055
#> 3: 2009-01-03 X -2.437263611
#> 4: 2009-01-04 X -0.005571287
#> 5: 2009-01-05 X 0.621552721
#> 6: 2009-01-06 X 1.148411606
#> 7: 2009-01-07 X -1.821817661
#> 8: 2009-01-08 X -0.247325302
#> 9: 2009-01-09 X -0.244199607
#> 10: 2009-01-10 X -0.282705449
#> 11: 2009-01-01 Y -1.107398767
#> 12: 2009-01-02 Y 1.257964084
#> 13: 2009-01-03 Y 4.130049791
#> 14: 2009-01-04 Y -3.261978804
#> 15: 2009-01-05 Y 1.024853900
#> 16: 2009-01-06 Y -3.726022984
#> 17: 2009-01-07 Y -1.044025029
#> 18: 2009-01-08 Y -0.105203820
#> 19: 2009-01-09 Y 1.085992685
#> 20: 2009-01-10 Y -1.828149655
#> 21: 2009-01-01 Z 1.872617682
#> 22: 2009-01-02 Z 1.451805023
#> 23: 2009-01-03 Z -5.218174180
#> 24: 2009-01-04 Z 2.951105285
#> 25: 2009-01-05 Z 7.554019717
#> 26: 2009-01-06 Z -0.389780418
#> 27: 2009-01-07 Z -3.743389414
#> 28: 2009-01-08 Z -0.063801245
#> 29: 2009-01-09 Z -3.307155815
#> 30: 2009-01-10 Z -6.049598605
#> time name value
stocks %>% longer(-(2:4)) # same
#> time name value
#> <Date> <fctr> <num>
#> 1: 2009-01-01 X -1.400043517
#> 2: 2009-01-02 X 0.255317055
#> 3: 2009-01-03 X -2.437263611
#> 4: 2009-01-04 X -0.005571287
#> 5: 2009-01-05 X 0.621552721
#> 6: 2009-01-06 X 1.148411606
#> 7: 2009-01-07 X -1.821817661
#> 8: 2009-01-08 X -0.247325302
#> 9: 2009-01-09 X -0.244199607
#> 10: 2009-01-10 X -0.282705449
#> 11: 2009-01-01 Y -1.107398767
#> 12: 2009-01-02 Y 1.257964084
#> 13: 2009-01-03 Y 4.130049791
#> 14: 2009-01-04 Y -3.261978804
#> 15: 2009-01-05 Y 1.024853900
#> 16: 2009-01-06 Y -3.726022984
#> 17: 2009-01-07 Y -1.044025029
#> 18: 2009-01-08 Y -0.105203820
#> 19: 2009-01-09 Y 1.085992685
#> 20: 2009-01-10 Y -1.828149655
#> 21: 2009-01-01 Z 1.872617682
#> 22: 2009-01-02 Z 1.451805023
#> 23: 2009-01-03 Z -5.218174180
#> 24: 2009-01-04 Z 2.951105285
#> 25: 2009-01-05 Z 7.554019717
#> 26: 2009-01-06 Z -0.389780418
#> 27: 2009-01-07 Z -3.743389414
#> 28: 2009-01-08 Z -0.063801245
#> 29: 2009-01-09 Z -3.307155815
#> 30: 2009-01-10 Z -6.049598605
#> time name value
stocks %>% longer(-"X|Y|Z") # same
#> time name value
#> <Date> <fctr> <num>
#> 1: 2009-01-01 X -1.400043517
#> 2: 2009-01-02 X 0.255317055
#> 3: 2009-01-03 X -2.437263611
#> 4: 2009-01-04 X -0.005571287
#> 5: 2009-01-05 X 0.621552721
#> 6: 2009-01-06 X 1.148411606
#> 7: 2009-01-07 X -1.821817661
#> 8: 2009-01-08 X -0.247325302
#> 9: 2009-01-09 X -0.244199607
#> 10: 2009-01-10 X -0.282705449
#> 11: 2009-01-01 Y -1.107398767
#> 12: 2009-01-02 Y 1.257964084
#> 13: 2009-01-03 Y 4.130049791
#> 14: 2009-01-04 Y -3.261978804
#> 15: 2009-01-05 Y 1.024853900
#> 16: 2009-01-06 Y -3.726022984
#> 17: 2009-01-07 Y -1.044025029
#> 18: 2009-01-08 Y -0.105203820
#> 19: 2009-01-09 Y 1.085992685
#> 20: 2009-01-10 Y -1.828149655
#> 21: 2009-01-01 Z 1.872617682
#> 22: 2009-01-02 Z 1.451805023
#> 23: 2009-01-03 Z -5.218174180
#> 24: 2009-01-04 Z 2.951105285
#> 25: 2009-01-05 Z 7.554019717
#> 26: 2009-01-06 Z -0.389780418
#> 27: 2009-01-07 Z -3.743389414
#> 28: 2009-01-08 Z -0.063801245
#> 29: 2009-01-09 Z -3.307155815
#> 30: 2009-01-10 Z -6.049598605
#> time name value
long_stocks = longer(stocks,"ti") # same as above except for assignment
long_stocks %>% wider(time,name = "name",value = "value")
#> Key: <time>
#> time X Y Z
#> <Date> <num> <num> <num>
#> 1: 2009-01-01 -1.400043517 -1.1073988 1.87261768
#> 2: 2009-01-02 0.255317055 1.2579641 1.45180502
#> 3: 2009-01-03 -2.437263611 4.1300498 -5.21817418
#> 4: 2009-01-04 -0.005571287 -3.2619788 2.95110529
#> 5: 2009-01-05 0.621552721 1.0248539 7.55401972
#> 6: 2009-01-06 1.148411606 -3.7260230 -0.38978042
#> 7: 2009-01-07 -1.821817661 -1.0440250 -3.74338941
#> 8: 2009-01-08 -0.247325302 -0.1052038 -0.06380125
#> 9: 2009-01-09 -0.244199607 1.0859927 -3.30715581
#> 10: 2009-01-10 -0.282705449 -1.8281497 -6.04959861
# the unchanged group could be missed if all the rest will be used
long_stocks %>% wider(name = "name",value = "value")
#> Key: <time>
#> time X Y Z
#> <Date> <num> <num> <num>
#> 1: 2009-01-01 -1.400043517 -1.1073988 1.87261768
#> 2: 2009-01-02 0.255317055 1.2579641 1.45180502
#> 3: 2009-01-03 -2.437263611 4.1300498 -5.21817418
#> 4: 2009-01-04 -0.005571287 -3.2619788 2.95110529
#> 5: 2009-01-05 0.621552721 1.0248539 7.55401972
#> 6: 2009-01-06 1.148411606 -3.7260230 -0.38978042
#> 7: 2009-01-07 -1.821817661 -1.0440250 -3.74338941
#> 8: 2009-01-08 -0.247325302 -0.1052038 -0.06380125
#> 9: 2009-01-09 -0.244199607 1.0859927 -3.30715581
#> 10: 2009-01-10 -0.282705449 -1.8281497 -6.04959861