Best way to unmelt dataframe in r

Question

My dataframe looks like

df <- data.frame(Role = c("a","a","b", "b", "c", "c"), Men = c(1,0,3,1,2,4), Women = c(2,1,1,4,3,1)) 
df.melt <- melt(df)

I only have access to the version that looks like df.melt, how to I get it in the df form?

Useing dcast just gets me errors I cant figure out the syntax of it.

akrun · Accepted Answer · 2020-09-20T20:49:35.967

3

We need a sequence column to specify the rows as there are duplicates in the melt for 'variable

library(tidyr)
library(dplyr)
library(data.table)
df.melt %>%
   mutate(rn = rowid(variable)) %>%
   pivot_wider(names_from = variable, values_from = value) %>%
   select(-rn)
# A tibble: 6 x 3
#  Role    Men Women
#  <chr> <dbl> <dbl>
#1 a         1     2
#2 a         0     1
#3 b         3     1
#4 b         1     4
#5 c         2     3
#6 c         4     1

If we are looking for efficient way for "best" way, dcast from data.table is fast

library(data.table)
dcast(setDT(df.melt), rowid(variable) + Role ~
     variable, value.var = 'value')[, variable := NULL][]
#   Role Men Women
#1:    a   1     2
#2:    a   0     1
#3:    b   3     1
#4:    b   1     4
#5:    c   2     3
#6:    c   4     1

edited Sep 20 '20 at 20:49

answered Sep 20 '20 at 20:41

akrun

874,273
37
540
662

Getting errors with both of these, Error: Problem with `mutate()` input `rn`. x could not find function "rowid" i Input `rn` is `rowid(variable)`. for the first version – Adam Sep 20 '20 at 20:47
Error in setDT(df.melt) : could not find function "setDT" – Adam Sep 20 '20 at 20:48
@Adam It is from `data.table`. I did show `library(data.table)` to load the package – akrun Sep 20 '20 at 20:48
@Adam you need to load `library(data.table)` – akrun Sep 20 '20 at 20:52

ThomasIsCoding · Answer 2 · 2020-09-20T21:05:59.027

Here is a base R option using unstack

cbind(
  Role = df.melt[1:(nrow(df.melt) / length(unique(df.melt$variable))), 1],
  unstack(rev(df.melt[-1]))
)

which gives

  Role Men Women
1    a   1     2
2    a   0     1
3    b   3     1
4    b   1     4
5    c   2     3
6    c   4     1

Another option is using reshape

subset(
  reshape(
    transform(
      df.melt,
      id = ave(1:nrow(df.melt), Role, variable, FUN = seq_along)
    ),
    direction = "wide",
    idvar = c("Role", "id"),
    timevar = "variable"
  ),
  select = -id
)

which gives

  Role value.Men value.Women
1    a         1           2
2    a         0           1
3    b         3           1
4    b         1           4
5    c         2           3
6    c         4           1

Best way to unmelt dataframe in r

2 Answers2