Converting data to longitudinal data

Question

Hi i am having difficulties trying to convert my data into longitudinal data using the Reshape package. Would be grateful if anyone could help me, thank you!

Data is as follows:

m <- matrix(sample(c(0, 0:), 100, replace = TRUE), 10)
ID<-c(1:10)
dim(ID)=c(10,1)
m<- cbind(ID,m)
d <- as.data.frame(m)
names(d)<-c('ID', 'litter1', 'litter2', 'litter3', 'litter4', 'litter5', 'litter6', 'litter7', 'litter8', 'litter9', 'litter10')
print(d)
   ID litter1 litter2 litter3 litter4 litter5 litter6 litter7 litter8 litter9 litter10
   1     0       0       0       3       1       0       2       0       0        3
   2     0       2       1       2       0       0       0       2       0        0
   3     1       0       1       2       0       3       3       3       2        0
   4     2       1       2       3       0       2       3       3       1        0
   5     0       1       2       0       0       0       3       3       1        0
   6     2       1       2       0       3       3       0       0       0        0
   7     0       1       0       3       0       0       1       2       2        0
   8     0       1       3       3       2       1       3       2       3        0
   9     0       2       0       2       2       3       2       0       0        3
   10    2       2       2       2       1       3       0       3       0        0

I wish to convert the above data into a longitudinal data with columns 'ID', 'litter category' which tells us the category of the litter, i.e. 1-10 and 'litter number' which tells us the number of pieces for each litter category:

   ID  littercategory litternumber

  1      4          3
  1      5          1
  1      7          2
  1      10         3
  2      2          2
  2      3          1
  2      4          2
  2      8          2

and so on.

Would really appreciate your help thank you!

score 0 · Accepted Answer · answered Nov 25 '17 at 08:42

You could do that as follows:

library(reshape2)
d = melt(d, id.vars=c("ID"))
colnames(d) = c('ID','littercategory','litternumber')
# remove the text in the littercategory column, keep only the number.
d$littercategory = gsub('litter','',d$littercategory)
d = d[d$litternumber!=0]

Output:

 ID littercategory litternumber
  1              1            4
  2              1            8
  3              1            6
  4              1            4
  7              1            6
  8              1            5
 10              1           10
  1              2            6
  2              2            9

As you can see, only the ordering is different as the output you requested, but I'm sure you can fix that yourself. (If not, there are plenty of resources on how to do that).

Hope this helps!

score 0 · Answer 2 · answered Nov 25 '17 at 08:42

To get desired output you have to melt your data and filter out values larger than 0.

library(data.table)
result <- setDT(melt(d, "ID"))[value != 0][order(ID)]

# To get exact structure modify result
result[, .(ID, 
           littercategory = sub("litter", "", variable), 
           litternumber = value)]

Converting data to longitudinal data

2 Answers2