How do I keep both parts of a concatenated variable when using melt() on two or more variables? (dat-CodePudding

I'm trying to reshape (i.e., make longer) a dataframe with concatenated variables using the data.table::melt() function. Both variables are concatenated with year. [note: I am using data.table dev version (1.14.3)]

library(data.table)

dt <-
  data.table(
    id = c(1, 2, 3),
    varA_2000 = c(2, 6, 1),
    varA_2001 = c(1, 1, 1),
    varA_2002 = c(1, 2, 3),
    varB_2000 = c(1, 0, 1),
    varB_2001 = c(1, 1, 1),
    varB_2002 = c(0, 0, 0)
  )

print(dt)
#>       id varA_2000 varA_2001 varA_2002 varB_2000 varB_2001 varB_2002
#>    <num>     <num>     <num>     <num>     <num>     <num>     <num>
#> 1:     1         2         1         1         1         1         0
#> 2:     2         6         1         2         0         1         0
#> 3:     3         1         1         3         1         1         0

How can I separate multiple concatenated column variables while also making the dataframe longer using the melt() function so that it results in this format?

desiredDT <- structure(
  list(
    id = c(1, 2, 3, 1, 2, 3, 1, 2, 3),
    year = c(
      2020,
      2020, 2020, 2021, 2021, 2021, 2022, 2022, 2022
    ),
    varA = c(
      2,
      6, 1, 1, 1, 1, 1, 2, 3
    ),
    varB = c(1, 0, 1, 1, 1, 1, 0, 0, 0)
  ),
  row.names = c(NA, -9L),
  class = c("data.table", "data.frame")
)
head(desiredDT)
#>   id year varA varB
#> 1  1 2020    2    1
#> 2  2 2020    6    0
#> 3  3 2020    1    1
#> 4  1 2021    1    1
#> 5  2 2021    1    1
#> 6  3 2021    1    1

This question is related to this on SO. In 2014 it looks like there was not a pure data.table solution to this original post. In addition, my dateset involves making long multiple variables (i.e., both varA, and varB).

So far I have been able to generate my desired format using two different methods (but both take multiple steps).

Method 1 (melt, then use fcase to re label the variable).

dx <- melt(dt,
  id.vars = "id", measure = patterns("^varA", "^varB"),
  value.name = c("varA", "varB"),
  variable.name = "year"
)
first_twoStepApproach <- dx[, year := fcase(
  year == "1", 2020,
  year == "2", 2021,
  year == "3", 2022
)]
head(first_twoStepApproach)
#>       id  year  varA  varB
#>    <num> <num> <num> <num>
#> 1:     1  2020     2     1
#> 2:     2  2020     6     0
#> 3:     3  2020     1     1
#> 4:     1  2021     1     1
#> 5:     2  2021     1     1
#> 6:     3  2021     1     1

Method 2 (melt, then split variable in a second step using tstrsplit)

dx <- melt(dt, id.vars = "id", variable.name = c("variable"),
           value.name = c("value"),
           verbose = TRUE)
#> 'measure.vars' is missing. Assigning all columns other than 'id.vars' columns as 'measure.vars'.
#> Assigned 'measure.vars' are [varA_2000, varA_2001, varA_2002, varB_2000, ...].
dx[, c("variable", "year") := tstrsplit(variable, "_")]

second_twoStepApproach <- dcast(dx, id   year ~ variable, value.name = value)

head(second_twoStepApproach)
#> Key: <id, year>
#>       id   year  varA  varB
#>    <num> <char> <num> <num>
#> 1:     1   2000     2     1
#> 2:     1   2001     1     1
#> 3:     1   2002     1     0
#> 4:     2   2000     6     0
#> 5:     2   2001     1     1
#> 6:     2   2002     2     0

Is there a way to do this transformation using just melt()?

CodePudding user response：

It may be easier with pivot_longer

library(tidyr)
library(dplyr)
pivot_longer(dt, cols = -id, names_to = c(".value", "year"), names_sep = "_")%>%
   arrange(year)

-output

# A tibble: 9 × 4
     id year   varA  varB
  <dbl> <chr> <dbl> <dbl>
1     1 2000      2     1
2     2 2000      6     0
3     3 2000      1     1
4     1 2001      1     1
5     2 2001      1     1
6     3 2001      1     1
7     1 2002      1     0
8     2 2002      2     0
9     3 2002      3     0

Or with data.table, use the measure.vars

library(data.table)
melt(dt, measure.vars = measure(value.name, year, sep = "_"))

-output

      id   year  varA  varB
   <num> <char> <num> <num>
1:     1   2000     2     1
2:     2   2000     6     0
3:     3   2000     1     1
4:     1   2001     1     1
5:     2   2001     1     1
6:     3   2001     1     1
7:     1   2002     1     0
8:     2   2002     2     0
9:     3   2002     3     0