How to melt your data

Question

For most of you this is an easy question so apologise for asking this.

I would like to melt my data.

My data structure

structure(list(PersonalCare = c(2, 2, 2, 2, 2, 2), Sleep = c(28, 
60, 54, 58, 80, 78)), row.names = c("04:00", "04:10", "04:20", 
"04:30", "04:40", "04:50"), class = "data.frame"

The data format is the following

        Personalcare    Sleep
04:00   2   28
04:10   2   60
04:20   2   54
04:30   2   58
04:40   2   80
04:50   2   78
05:00   2   110
05:10   2   156
05:20   1   172
05:30   1   192
05:40   1   214

After I melt my data using melted_PersonalcareSleep<- melt(PersonalcareSleep) the format of my data changes to

1   04:00   Personalcare    2
2   04:10   Personalcare    2
3   04:20   Personalcare    2
4   04:30   Personalcare    2
5   04:40   Personalcare    2
6   04:50   Personalcare    2
7   05:00   Personalcare    2
8   05:10   Personalcare    2
9   05:20   Personalcare    1
10  05:30   Personalcare    1
11  05:40   Personalcare    1

...

145 04:00   Sleep   28
146 04:10   Sleep   60
147 04:20   Sleep   54
148 04:30   Sleep   58
149 04:40   Sleep   80
150 04:50   Sleep   78
....

After I plot my data with:

ggplot(melted_PersonalcareSleep, aes(x = Var1,y = value,group=Var2, color=Var2)) + geom_line(size=1) +labs(x="Time", y="Frequences", colour="Activties", fill="Activites" ) + theme(legend.position="right", axis.text.x = element_text(angle = 0, hjust = 0))  +annotate("rect", fill = "black", alpha = 0.3, xmin = c(97), xmax = c(121), ymin = -Inf, ymax = Inf)

Output

As you can see on the image one activity is not shown correctly

How can I melt my data based on time (I don't want time to repeat itself).

Many thanks

I don't find the expected output. You only speciffied that the output you are recieving is incorrect. In addition, the `dput` and the input data showed are different — akrun, Aug 05 '19 at 13:52

score 0 · Answer 1 · answered Aug 05 '19 at 13:43

0

We could create a column from row.names and then use gather

library(tidyverse)
rownames_to_column(df1, 'time') %>%
       gather(key, val, -time)

answered Aug 05 '19 at 13:43

akrun

874,273
37
540
662

score 0 · Accepted Answer · answered Aug 05 '19 at 13:57

You could also use -

> data.table::setDT(df1,keep.rownames = "time")
> melt(df1,id.vars = "time")


    time     variable     value
 1: 04:00 PersonalCare     2
 2: 04:10 PersonalCare     2
 3: 04:20 PersonalCare     2
 4: 04:30 PersonalCare     2
 5: 04:40 PersonalCare     2
 6: 04:50 PersonalCare     2
 7: 04:00       Eating    28
 8: 04:10       Eating    60
 9: 04:20       Eating    54
10: 04:30       Eating    58
11: 04:40       Eating    80
12: 04:50       Eating    78

How to melt your data

2 Answers2