melt.data.table should offer `variable` to match on the name, rather than the number #3396

HughParsonage · 2019-02-13T05:17:19Z

Currently, using patterns makes the variable column an integer, which is both a bit awkward ...

library(data.table)
DT <- 
  data.table(x = 1:5, 
             y_No = 101:105,
             `y_$`= 1011:1015,
             z_No = 201:205,
             `z_$` = 301:305)
DT_melt <- 
  melt.data.table(DT, 
                  id.vars = "x",
                  measure.vars = patterns("Dollar" = "\\$$", 
                                          "Number" = "No$"))
DT_melt
#>     x variable Dollar Number
#>  1: 1        1   1011    101
#>  2: 2        1   1012    102
#>  3: 3        1   1013    103
#>  4: 4        1   1014    104
#>  5: 5        1   1015    105
#>  6: 1        2    301    201
#>  7: 2        2    302    202
#>  8: 3        2    303    203
#>  9: 4        2    304    204
#> 10: 5        2    305    205
# Intended
DT_melt[, variable_new := c("y", "z")[variable]][]
#>     x variable Dollar Number variable_new
#>  1: 1        1   1011    101            y
#>  2: 2        1   1012    102            y
#>  3: 3        1   1013    103            y
#>  4: 4        1   1014    104            y
#>  5: 5        1   1015    105            y
#>  6: 1        2    301    201            z
#>  7: 2        2    302    202            z
#>  8: 3        2    303    203            z
#>  9: 4        2    304    204            z
#> 10: 5        2    305    205            z

... and potentially error-prone, e.g.

library(data.table)
DT <- 
  data.table(x = 1:5, 
             y_No = 101:105,
             `y_$`= 1011:1015,
             z_No = 201:205,
             `z_$` = 301:305,
             `w1_$` = 401:405,
             w2_No = 501:505)
melt.data.table(DT, 
                id.vars = "x",
                measure.vars = patterns("Dollar" = "\\$$", 
                                        "Number" = "No$"))
#>     x variable Dollar Number
#>  1: 1        1   1011    101
#>  2: 2        1   1012    102
#>  3: 3        1   1013    103
#>  4: 4        1   1014    104
#>  5: 5        1   1015    105
#>  6: 1        2    301    201
#>  7: 2        2    302    202
#>  8: 3        2    303    203
#>  9: 4        2    304    204
#> 10: 5        2    305    205
#> 11: 1        3    401    501    # incorrect
#> 12: 2        3    402    502    # incorrect
#> 13: 3        3    403    503    # incorrect
#> 14: 4        3    404    504    # incorrect
#> 15: 5        3    405    505    # incorrect

^{Created on 2019-02-13 by the reprex package (v0.2.1)}

Can offer a PR if this is a worthwhile feature. Otherwise can just attempt the bugfix.

The text was updated successfully, but these errors were encountered:

MichaelChirico · 2019-02-13T06:19:52Z

I'm starting at this and not seeing what's incorrect?

And maybe duplicate of #2551? The functionality is certainly useful...

HughParsonage · 2019-02-13T06:24:54Z

I think it's the same as that one though it didn't appear in the searches and I think my reprex is clearer.

I think the result of the second melt should be:

#>     x variable Dollar Number
#>  1: 1        1   1011    101
#>  2: 2        1   1012    102
#>  3: 3        1   1013    103
#>  4: 4        1   1014    104
#>  5: 5        1   1015    105
#>  6: 1        2    301    201
#>  7: 2        2    302    202
#>  8: 3        2    303    203
#>  9: 4        2    304    204
#> 10: 5        2    305    205
#> 11: 1        3    401     NA
#> 12: 2        3    402     NA
#> 13: 3        3    403     NA
#> 14: 4        3    404     NA
#> 15: 5        3    405     NA
#> 16: 1        4     NA    501
#> 17: 2        4     NA    502
#> 18: 3        4     NA    503
#> 19: 4        4     NA    504
#> 20: 5        4     NA    505

^{Created on 2019-02-13 by the reprex package (v0.2.1)}

arunsrinivasan · 2019-02-17T11:34:53Z

variable col is factor, not integer. And I believe this is clearly documented? I agree having the levels right would make sense. But patterns is for convenience. The original idea of being able to melt on any columns provided as a list is the original purpose of melt. There's no reason why patterns should automatically match measure vars automatically. Perhaps this could be done using an additional argument. The default functionality should be as it is.

HughParsonage · 2019-02-17T11:57:38Z

Arun, happy to leave the existing functionality as-is (was planning to), but I do think that the second example is a bug: there should be four levels, not three.

arunsrinivasan · 2019-02-17T13:08:56Z

@HughParsonage patterns doesn't implicitly match on column names.. it just resolves columns into a list of column names/indices for every pattern provided. So I wouldn't classify it as a bug.

What you require could be achieved by extending the functionality of patterns (or even another function), and I think it's extremely useful too..

For e.g.,

melt(dt, id="a", measure=patterns("b$", "c$", fill=TRUE))

or strict_patterns(...) or something like that. What do you think?

franknarf1 · 2019-04-03T04:26:01Z

Regarding syntax for applying strict matching, I guess it would be necessary to identify which part of the string to match across the patterns. This could be done in a capture group that additionally could be used to populate the variable factor column's levels, I imagine, like:

melt(DT, id="x", measure=patterns2(Dollar = "^(.+)_\\$$", Number = "^(.+)_No$"))

melt(df, id="id", measure=patterns2(purchased = "^brand(.+)$", price = "^price(.+)$")

(The latter example from #3487 highlighted here to show the connection I see between the two issues to the OP @jsams )

So the two (.*) would have to capture the same content across entries of patterns2(...) to appear on the same row in the end.

tdhock · 2020-09-29T03:48:15Z

hi this is related to #4027 and fixed by #4720, not merged into master yet but can be used via

remotes::install_github(c("Rdatatable/data.table@fix4027", "tdhock/nc@multiple-fill"))
#> Skipping install of 'data.table' from a github remote, the SHA1 (4c5810c2) has not changed since last install.
#>   Use `force = TRUE` to force installation
#> Skipping install of 'nc' from a github remote, the SHA1 (11b61f8e) has not changed since last install.
#>   Use `force = TRUE` to force installation
library(data.table)
DT <- 
  data.table(x = 1:5, 
             y_No = 101:105,
             `y_$`= 1011:1015,
             z_No = 201:205,
             `z_$` = 301:305)
nc::capture_melt_multiple(
  DT,
  variable_new="[yz]",
  "_",
  column=".*", function(x)ifelse(x=="$", "Dollar", "Number"))
#>     x variable_new Dollar Number
#>  1: 1            y   1011    101
#>  2: 2            y   1012    102
#>  3: 3            y   1013    103
#>  4: 4            y   1014    104
#>  5: 5            y   1015    105
#>  6: 1            z    301    201
#>  7: 2            z    302    202
#>  8: 3            z    303    203
#>  9: 4            z    304    204
#> 10: 5            z    305    205
DT2 <- 
  data.table(x = 1:5, 
             y_No = 101:105,
             `y_$`= 1011:1015,
             z_No = 201:205,
             `z_$` = 301:305,
             `w1_$` = 401:405,
             w2_No = 501:505)  
nc::capture_melt_multiple(
  DT2,
  variable_new=".*",
  "_",
  column=".*", function(x)ifelse(x=="$", "Dollar", "Number"),
  fill=TRUE)
#>     x variable_new Dollar Number
#>  1: 1           w1    401     NA
#>  2: 2           w1    402     NA
#>  3: 3           w1    403     NA
#>  4: 4           w1    404     NA
#>  5: 5           w1    405     NA
#>  6: 1           w2     NA    501
#>  7: 2           w2     NA    502
#>  8: 3           w2     NA    503
#>  9: 4           w2     NA    504
#> 10: 5           w2     NA    505
#> 11: 1            y   1011    101
#> 12: 2            y   1012    102
#> 13: 3            y   1013    103
#> 14: 4            y   1014    104
#> 15: 5            y   1015    105
#> 16: 1            z    301    201
#> 17: 2            z    302    202
#> 18: 3            z    303    203
#> 19: 4            z    304    204
#> 20: 5            z    305    205

jangorecki · 2020-09-29T12:38:15Z

Let's wait till it is merged

tdhock · 2020-10-01T03:41:27Z

hi again the solution using only the new data.table::melt (without nc/regex) looks like

remotes::install_github("Rdatatable/data.table@fix4027")
#> Skipping install of 'data.table' from a github remote, the SHA1 (4c5810c2) has not changed since last install.
#>   Use `force = TRUE` to force installation
library(data.table)
DT2 <- 
  data.table(x = 1:5, 
             y_No = 101:105,
             `y_$`= 1011:1015,
             z_No = 201:205,
             `z_$` = 301:305,
             `w1_$` = 401:405,
             w2_No = 501:505)  
DT2.tall <- melt(DT2, measure=list(
  Dollar=c("w1_$", NA, "y_$", "z_$"),
  Number=c(NA, "w2_No", "y_No", "z_No")))
DT2.tall[, variable_new := c("w1", "w2", "y", "z")[variable] ]
DT2.tall
#>     x variable Dollar Number variable_new
#>  1: 1        1    401     NA           w1
#>  2: 2        1    402     NA           w1
#>  3: 3        1    403     NA           w1
#>  4: 4        1    404     NA           w1
#>  5: 5        1    405     NA           w1
#>  6: 1        2     NA    501           w2
#>  7: 2        2     NA    502           w2
#>  8: 3        2     NA    503           w2
#>  9: 4        2     NA    504           w2
#> 10: 5        2     NA    505           w2
#> 11: 1        3   1011    101            y
#> 12: 2        3   1012    102            y
#> 13: 3        3   1013    103            y
#> 14: 4        3   1014    104            y
#> 15: 5        3   1015    105            y
#> 16: 1        4    301    201            z
#> 17: 2        4    302    202            z
#> 18: 3        4    303    203            z
#> 19: 4        4    304    204            z
#> 20: 5        4    305    205            z

tdhock · 2020-10-07T03:53:50Z

pure data.table solution using #4731

remotes::install_github("Rdatatable/data.table@melt-custom-variable")
#> Skipping install of 'data.table' from a github remote, the SHA1 (c02fa9e8) has not changed since last install.
#>   Use `force = TRUE` to force installation
library(data.table)
DT2 <- 
  data.table(x = 1:5, 
             y_No = 101:105,
             `y_$`= 1011:1015,
             z_No = 201:205,
             `z_$` = 301:305,
             `w1_$` = 401:405,
             w2_No = 501:505)  
melt(DT2, measure.vars=measure(
  variable_new, value.name=function(x)c("$"="Dollar","No"="Number")[x]))
#>     x variable_new Number Dollar
#>  1: 1            y    101   1011
#>  2: 2            y    102   1012
#>  3: 3            y    103   1013
#>  4: 4            y    104   1014
#>  5: 5            y    105   1015
#>  6: 1            z    201    301
#>  7: 2            z    202    302
#>  8: 3            z    203    303
#>  9: 4            z    204    304
#> 10: 5            z    205    305
#> 11: 1           w1     NA    401
#> 12: 2           w1     NA    402
#> 13: 3           w1     NA    403
#> 14: 4           w1     NA    404
#> 15: 5           w1     NA    405
#> 16: 1           w2    501     NA
#> 17: 2           w2    502     NA
#> 18: 3           w2    503     NA
#> 19: 4           w2    504     NA
#> 20: 5           w2    505     NA

HughParsonage added bug enhancement labels Feb 13, 2019

arunsrinivasan added feature request and removed bug labels Feb 17, 2019

smingerson mentioned this issue Mar 2, 2019

data.table-style coalesce #3424

Closed

franknarf1 mentioned this issue Apr 2, 2019

Melt corrupts data by using column position instead of name #3487

Closed

Henrik-P mentioned this issue Nov 6, 2019

Ability to explicitly pass index names in melt.data.table measure.vars argument #4027

Closed

otoomet mentioned this issue Nov 16, 2019

Melt on multiple columns: undocumented behavior #4047

Closed

jangorecki added the reshape dcast melt label Apr 5, 2020

MichaelChirico mentioned this issue May 14, 2020

Master list of most-requested issues #3189

Open

75 tasks

MichaelChirico added the High label May 30, 2020

MichaelChirico added top request One of our most-requested issues and removed High labels Jun 7, 2020

tdhock closed this as completed Sep 29, 2020

jangorecki reopened this Sep 29, 2020

tdhock mentioned this issue Oct 1, 2020

melt with custom variable columns using variable_table attribute #4731

Merged

mattdowle added this to the 1.14.1 milestone May 9, 2021

mattdowle closed this as completed in #4731 May 9, 2021

jangorecki modified the milestones: 1.14.9, 1.15.0 Oct 29, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

melt.data.table should offer `variable` to match on the name, rather than the number #3396

melt.data.table should offer `variable` to match on the name, rather than the number #3396

HughParsonage commented Feb 13, 2019

MichaelChirico commented Feb 13, 2019

HughParsonage commented Feb 13, 2019

arunsrinivasan commented Feb 17, 2019

HughParsonage commented Feb 17, 2019

arunsrinivasan commented Feb 17, 2019 •

edited

Loading

franknarf1 commented Apr 3, 2019

tdhock commented Sep 29, 2020

jangorecki commented Sep 29, 2020

tdhock commented Oct 1, 2020

tdhock commented Oct 7, 2020

melt.data.table should offer variable to match on the name, rather than the number #3396

melt.data.table should offer variable to match on the name, rather than the number #3396

Comments

HughParsonage commented Feb 13, 2019

MichaelChirico commented Feb 13, 2019

HughParsonage commented Feb 13, 2019

arunsrinivasan commented Feb 17, 2019

HughParsonage commented Feb 17, 2019

arunsrinivasan commented Feb 17, 2019 • edited Loading

franknarf1 commented Apr 3, 2019

tdhock commented Sep 29, 2020

jangorecki commented Sep 29, 2020

tdhock commented Oct 1, 2020

tdhock commented Oct 7, 2020

melt.data.table should offer `variable` to match on the name, rather than the number #3396

melt.data.table should offer `variable` to match on the name, rather than the number #3396

arunsrinivasan commented Feb 17, 2019 •

edited

Loading