1

I want to "un-melt" a data.frame that has multiple IDs and multiple melted variables and I'm stuck.

This is what my dataframe looks like:

enter image description here

This is the desirable outcome

enter image description here

I have tried:

unmelted <- dcast(setDT(melted), Id, Date, Type ~  Score, Time, 
                                value.var = c("Score","Time"), sep = "")

And

unmelted <- melted %>%
   group_by(Id, Date, Type) %>%
   unite(variable, Score, Time)%>%
   spread(Score, Time, -Id, Date, Type)

I cannot use pivot_wider.

M--
  • 25,431
  • 8
  • 61
  • 93
Danielle Travassos
  • 141
  • 1
  • 9
  • 4
    **Don't post pictures of your data!** You should provide a [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) – M-- Jan 24 '20 at 18:50

2 Answers2

4

We can use 

library(data.table)
dcast(setDT(df1), id + Date + Type ~ rowid(id, Date, Type),
     value.var = c("Score","Time"), sep = "")
#   id       Date Type Score1 Score2 Score3 Score4 Time1 Time2 Time3 Time4
#1:  1 2001-01-13  aaa    123    456    789     NA 12:12 13:12 14:12  <NA>
#2:  2 2001-01-16  ddd    113    145     NA     NA 15:12 16:12  <NA>  <NA>
#3:  3 2001-01-18  bbb    789    145    113    145 17:12 18:12 19:12 20:12

Using data from @M--

akrun
  • 874,273
  • 37
  • 540
  • 662
  • Hi akrun, Thank you for your reply. I keep getting this error that I was getting before: Error in .subset2(x, i, exact = exact) : subscript out of bounds In addition: Warning message: In if (!(value.var %in% names(data))) { : the condition has length > 1 and only the first element will be used. Do you know what it means, by any chance? – Danielle Travassos Jan 24 '20 at 20:25
  • @DanielleTravassos My testing was based on the example in the M--'s post. If your column names are different, you may need to change accordingly – akrun Jan 24 '20 at 20:29
  • Hi Akrun, Yes, with his data, I'm getting this message. – Danielle Travassos Jan 24 '20 at 20:34
  • @DanielleTravassos Sorry, not reprroducign it with `R 3.6.2` and `packageVersion('data.table')# [1] ‘1.12.8’` – akrun Jan 24 '20 at 20:38
3

A tidyverse solution, using gather and spread from tidyr pacakge:

library(dplyr)
library(tidyr) #version 1.0.0 which has pivot_wider

df1 %>% 
  group_by(Type) %>% 
  mutate(name_x = row_number()) %>% 
  gather(key=var, value=val, c(Score, Time)) %>% 
  mutate(var = paste(var, name_x, sep="_")) %>% 
  select(-name_x) %>% 
  spread(key=var, value=val)

#> # A tibble: 3 x 11
#> # Groups:   Type [3]
#>      id Date  Type  Score_1 Score_2 Score_3 Score_4 Time_1 Time_2 Time_3 Time_4
#>   <dbl> <chr> <chr>   <dbl>   <dbl>   <dbl>   <dbl> <chr>  <chr>  <chr>  <chr> 
#> 1     1 2001~ aaa       123     456     789      NA 12:12  13:12  14:12  <NA>  
#> 2     2 2001~ ddd       113     145      NA      NA 15:12  16:12  <NA>   <NA>  
#> 3     3 2001~ bbb       789     145     113     145 17:12  18:12  19:12  20:12

You can do the same with pivot_wider much more conveniently: 

df1 %>% 
  group_by(Type) %>% 
  mutate(name_x = row_number()) %>% 
  pivot_wider(id_cols = c("id","Date", "Type"), 
              names_from = c("name_x"), 
              values_from = c("Score", "Time"))

Data:

df1 <- data.frame(id=c(1,1,1,2,2,3,3,3,3),
                  Date = c(rep("2001-01-13", 3), rep("2001-01-16", 2), rep("2001-01-18", 4)),
                  Type = c(rep("aaa",3), rep("ddd", 2), rep("bbb",4)),
                  Score = c(123,456,789,113,145,789,145,113,145),
                  Time = paste0(12:20, ":12"),
                  stringsAsFactors = F)
M--
  • 25,431
  • 8
  • 61
  • 93
  • This works perfectly. I have a question. The result was as expected but I got this error. Warning message: attributes are not identical across measure variables; they will be dropped. Could anyone explain what that exactly means? – Danielle Travassos Jan 24 '20 at 20:18
  • @DanielleTravassos That's not an error. It's a warning saying that number of variables for each group (`key`) are not the same. It works as expected. – M-- Jan 24 '20 at 20:55