Reshaping Data with Multiple Columns From Wide to Long

[cathal]

I am reshaping data from wide to long using reshape, but three of the columns get mixed up.

Here is an example of the wide data:

dat <- data.frame(id = c(1, 2, 3),
                  BF1.desc = c("meal1", "other", "meal3"),
                  BF1.portion = c(1, 2, 1),
                  BF1.portionqualifier = c("lower", "same", "greater"),
                  BF1.other = c(NA, "food", NA),
                  BF2.desc = c(NA, NA, NA),
                  BF2.portion = c(NA, NA, NA),
                  BF2.portionqualifier = c(NA, NA, NA),
                  BF2.other = c(NA, NA, NA),
                  LN1.desc = c("meal4", "meal5", "meal6"),
                  LN1.portion = c(2, 3, 3),
                  LN1.portionqualifier = c("same", "same", "lower"),
                  LN1.other = c(NA, NA, NA),
                  LN2.desc = c("meal7", "meal8", "other"),
                  LN2.portionqualifier = c("same", "greater", "same"),
                  LN2.portion = c(3, 1, 2),
                  LN2.other = c(NA, NA, "this food")
               )

This is the long output that I am trying to achieve:

# id MealTypes    desc   portion   portionqualifier       other
#  1       BF1   meal1         1              lower        <NA>
#  2       BF1   other         2               same        food 
#  3       BF1   meal3         1            greater        <NA>
#  1       BF2    <NA>         NA              <NA>        <NA>
#  2       BF2    <NA>         NA              <NA>        <NA>
#  3       BF2    <NA>         NA              <NA>        <NA>
#  1       LN1   meal4         2               same        <NA>
#  2       LN1   meal5         3               same        <NA>
#  3       LN1   meal6         3              lower        <NA>
#  1       LN2   meal7         3               same        <NA>
#  2       LN2   meal8         1            greater        <NA>
#  3       LN2   other         2               same   this food

However, when I use the following code:

longdat <- 
  reshape(dat, direction = 'long',
          varying = c("BF1.desc", "BF1.portion", "BF1.portionqualifier", "BF1.other", 
                      "BF2.desc", "BF2.portion", "BF2.portionqualifier", "BF2.other", 
                      "LN1.desc", "LN1.portion", "LN1.portionqualifier", "LN1.other", 
                      "LN2.desc", "LN2.portion", "LN2.portionqualifier", "LN2.other"),
          timevar = "MealTypes",
          times = c("BF1", "BF2", "LN1", "LN2"),
          v.names = c("desc", "portion", "portionqualifier", "other"),
          idvar = "id"
          )

This is the output I get (the last three columns get mixed up):

# id    MealTypes         desc        portion      portionqualifier   other
#  1          BF1        meal1           <NA>                     1   lower
#  2          BF1        other           food                     2    same
#  3          BF1        meal3           <NA>                     1 greater
#  1          BF2         <NA>           <NA>                    NA    <NA>
#  2          BF2         <NA>           <NA>                    NA    <NA>
#  3          BF2         <NA>           <NA>                    NA    <NA>
#  1          LN1        meal4           <NA>                     2    same
#  2          LN1        meal5           <NA>                     3    same
#  3          LN1        meal6           <NA>                     3   lower
#  1          LN2        meal7           <NA>                     3    same
#  2          LN2        meal8           <NA>                     1 greater
#  3          LN2        other      this food                     2    same

Any ideas where I am going wrong would be greatly appreciated.

[JackDavison]

I'd use tidyr rather than reshape. Note that I've made all the columns into characters to achieve this - perhaps someone better with tidyr would have a more elegant solution.

library(tidyverse)

dat <- data.frame(id = c(1, 2, 3),
                  BF1.desc = c("meal1", "other", "meal3"),
                  BF1.portion = c(1, 2, 1),
                  BF1.portionqualifier = c("lower", "same", "greater"),
                  BF1.other = c(NA, "food", NA),
                  BF2.desc = c(NA, NA, NA),
                  BF2.portion = c(NA, NA, NA),
                  BF2.portionqualifier = c(NA, NA, NA),
                  BF2.other = c(NA, NA, NA),
                  LN1.desc = c("meal4", "meal5", "meal6"),
                  LN1.portion = c(2, 3, 3),
                  LN1.portionqualifier = c("same", "same", "lower"),
                  LN1.other = c(NA, NA, NA),
                  LN2.desc = c("meal7", "meal8", "other"),
                  LN2.portionqualifier = c("same", "greater", "same"),
                  LN2.portion = c(3, 1, 2),
                  LN2.other = c(NA, NA, "this food")
)

dat |> 
  tibble() |> 
  mutate(across(.fns = as.character)) |> 
  pivot_longer(-id, names_sep = "\\.", names_to = c("meal_type", "name")) |> 
  pivot_wider() |> 
  mutate(across(c(portion, id), as.numeric)) |> 
  arrange(meal_type)
#> # A tibble: 12 x 6
#>       id meal_type desc  portion portionqualifier other    
#>    <dbl> <chr>     <chr>   <dbl> <chr>            <chr>    
#>  1     1 BF1       meal1       1 lower            <NA>     
#>  2     2 BF1       other       2 same             food     
#>  3     3 BF1       meal3       1 greater          <NA>     
#>  4     1 BF2       <NA>       NA <NA>             <NA>     
#>  5     2 BF2       <NA>       NA <NA>             <NA>     
#>  6     3 BF2       <NA>       NA <NA>             <NA>     
#>  7     1 LN1       meal4       2 same             <NA>     
#>  8     2 LN1       meal5       3 same             <NA>     
#>  9     3 LN1       meal6       3 lower            <NA>     
#> 10     1 LN2       meal7       3 same             <NA>     
#> 11     2 LN2       meal8       1 greater          <NA>     
#> 12     3 LN2       other       2 same             this food

^{Created on 2021-12-09 by the reprex package (v2.0.1)}

[cathal]

Thanks @JackDavison