How to ensure that when melting data, all possible variables are demonstrated?

Question

Edit 2: Updated with starting data

Edit 1: I would like to know how to modify the data or parameters in melt function (might have overlooked or not understood) before melting it to the long form.

I started with the following data:

             type1 type2 type3 type4 
A            43   0     1       0 
B             6   0     1       0 
C            16   0     3       1 
D            17   0     2       2

When it gets melted, it looks like:

    Sample variable count proportion
1  A       type1    43 0.97727273
2  A       type2     0 0.00000000
3  A       type3     1 0.02272727
4  A       type4     0 0.00000000
5  B       type1     6 0.85714286
6  B       type2     0 0.00000000
7  B       type3     1 0.14285714
8  B       type4     0 0.00000000
9  C       type1    16 0.80000000
10 C       type2     0 0.00000000
11 C       type3     3 0.15000000
12 C       type4     1 0.05000000

However, in total there should have type1 to type5 as all possible variables. Since, the data didn't contain any of type 5, it wasn't part of the melted data. I would like to all variables per sample in the table. So for type 5, which the data doesn't have, I would want to have Sample type5 0 0, instead of no entries for it. I have looked at the API of melt and cast but wasn't able to find an answer to the above inquiry.

Any idea? Thanks!

Do you want to modify data before melting, while melting or after melting? — David Arenburg, Aug 14 '14 at 13:39
@Michael. Without the original dataset it is only a guesswork. Suppose `dat` is unmelted dataset, Wouldn't `dat$type5 <- 0` and then melt gives you the expected output. — akrun, Aug 14 '14 at 14:38
Sorry about that @akrun, I just included it. (first time posting here) — Michael, Aug 14 '14 at 19:31

score 2 · Answer 1 · edited Aug 18 '14 at 09:32

Update

Data tables are excellent for this kind of problems. It might take some practice to really get how they work, but as a reward you get code that is very compact and readable.

# Raw data
dat <- read.table(con <- textConnection("type1 type2 type3 type4 
A            43   0     1       0 
B             6   0     1       0 
C            16   0     3       1 
D            17   0     2       2"), header=TRUE)
dat$Sample <- rownames(dat)

# Aggregate
library("reshape2")
library("data.table") ## 1.9.2+
dt.dat <- melt(dat, value.name="count") ## melt.data.table method
dt.dat[, list(variable, count, proportion=prop.table(count)), by=Sample]

Original answer

You can create a frame with all possible combinations of index variables that should be present in your final results with expand.grid and then copy the values to it with merge.

# Read in the data in your question
> dat <- read.table(con <- textConnection("Sample variable count proportion
A      type1    15 0.93750000    
A      type2     0 0.00000000    
A      type3     1 0.06250000    
A      type4   0 0.00000000    
B      type1    13 0.86666667   
B      type2     0 0.00000000   
B      type3     2 0.13333333   
B      type4     0 0.00000000"), header=TRUE)
> close(con)

# Create all the records that should be present in the final results
> entries <- expand.grid(Sample=c("A", "B"), variable=sprintf("type%i", 1:5))

# Voilà!
> (dat <- merge(entries, dat, by=c("Sample", "variable"), all.x=TRUE))

   Sample variable count proportion
1       A    type1    15  0.9375000
2       A    type2     0  0.0000000
3       A    type3     1  0.0625000
4       A    type4     0  0.0000000
5       A    type5    NA         NA
6       B    type1    13  0.8666667
7       B    type2     0  0.0000000
8       B    type3     2  0.1333333
9       B    type4     0  0.0000000
10      B    type5    NA         NA

If you want 0 instead of NA you can change it like this

dat[3:4] <- lapply(dat[3:4], function(x) ifelse(is.na(x), 0, x))

cool, this could work after melting. I edited my first post to include more information. Ideally, I am looking to see whether I can modify the data before or during melting. — Michael, Aug 14 '14 at 20:05

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

In your new dataset, I assume there is a column named Sample

data

dat <-structure(list(Sample = structure(1:4, .Label = c("A", "B", "C", 
"D"), class = "factor"), type1 = c(43L, 6L, 16L, 17L), type2 = c(0L, 
0L, 0L, 0L), type3 = c(1L, 1L, 3L, 2L), type4 = c(0L, 0L, 1L, 
2L)), .Names = c("Sample", "type1", "type2", "type3", "type4"
), class = "data.frame", row.names = c(NA, -4L))

dat[setdiff(paste0("type", 1:5), colnames(dat)[-1])] <- 0
library(reshape2)

datM <- melt(dat, id.var="Sample")
datM1 <- within(datM, {proportion <-ave(value, Sample, FUN=function(x) x/sum(x))})[order(datM$Sample),]
row.names(datM1) <- 1:nrow(datM1)

 datM1
 #  Sample variable value proportion
#1       A    type1    43 0.97727273
#2       A    type2     0 0.00000000
#3       A    type3     1 0.02272727
#4       A    type4     0 0.00000000
#5       A    type5     0 0.00000000
#6       B    type1     6 0.85714286
#7       B    type2     0 0.00000000
#8       B    type3     1 0.14285714
#9       B    type4     0 0.00000000
#10      B    type5     0 0.00000000
#11      C    type1    16 0.80000000
#12      C    type2     0 0.00000000
#13      C    type3     3 0.15000000
#14      C    type4     1 0.05000000
#15      C    type5     0 0.00000000
#16      D    type1    17 0.80952381
#17      D    type2     0 0.00000000
#18      D    type3     2 0.09523810
#19      D    type4     2 0.09523810
#20      D    type5     0 0.00000000

How to ensure that when melting data, all possible variables are demonstrated?

2 Answers2

Update

Original answer

data