1

I am just wondering if you could guide me to the right direction about how can I reshape a dataset based on a specific criteria to arrange by hours, for example, I have the follwing example dataset: Dataframe

I am trying to reshape the dataset to look like this:

enter image description here

how can I proceed with this reshaping please? Many Thanks.

My sample data:

data = structure(list(date = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L), .Label = "Jan-97", class = "factor"), day = c(1L, 
1L, 1L, 1L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 3L), hour = c(1L, 2L, 
3L, 4L, 1L, 2L, 3L, 4L, 1L, 2L, 3L, 4L), Value = c(65L, 29L, 
31L, 42L, 42L, 52L, 61L, 57L, 55L, 52L, 57L, 46L)), .Names = c("date", 
"day", "hour", "Value"), class = "data.frame", row.names = c(NA, 
-12L))
Achak
  • 1,286
  • 2
  • 18
  • 36

4 Answers4

5

This uses the reshape2 package. I am sure it can be done with the reshape function, but I am not as facile with that.

library("reshape2")
dcast(data, date+day~hour, value.var="Value")

which gives

> dcast(data, date+day~hour, value.var="Value")
    date day  1  2  3  4
1 Jan-97   1 65 29 31 42
2 Jan-97   2 42 52 61 57
3 Jan-97   3 55 52 57 46

If you don't like the names from that, you can change them afterwards.

widedata <- dcast(data, date+day~hour, value.var="Value")
names(widedata)[-(1:2)] <- paste0("hour",names(widedata[-(1:2)]))

so widedata is:

> widedata
    date day hour1 hour2 hour3 hour4
1 Jan-97   1    65    29    31    42
2 Jan-97   2    42    52    61    57
3 Jan-97   3    55    52    57    46
Brian Diggs
  • 57,757
  • 13
  • 166
  • 188
  • 1
    +1 I was still typing. the `reshape` package and `cast` will do the same thing. `cast(data, date+day~hour, value='Value')` – Justin Apr 12 '12 at 19:52
  • Thanks Brian and Justin, thats exatly what I was looking for to understand how to reshape generally. thanks again, regards,# – Achak Apr 12 '12 at 19:55
3

This can be solved using reshape 

reshape(data, idvar=c('date','day'), direction='wide', timevar='hour')
smu
  • 8,757
  • 2
  • 19
  • 14
  • thanks Smu for your answer, the first one is more appropriate to what I am looking for, but thanks for trying to help me :) – Achak Apr 12 '12 at 19:58
3

This is a way to do it in base:

reshape(data, timevar = c("hour"), v.names = c("Value"),  
    idvar = c("date", "day"), direction = "wide")

EDIT: Like Brian used if want to retain the names use:

names(DF)[-c(1:2)] <- paste0("hour", 1:4)
rownames(DF)<- 1:nrow(DF)
DF

Yielding:

    date day hour1 hour2 hour3 hour4
1 Jan-97   1    65    29    31    42
2 Jan-97   2    42    52    61    57
3 Jan-97   3    55    52    57    46
Tyler Rinker
  • 108,132
  • 65
  • 322
  • 519
1

An update using dplyr and tidyr packages:

library(tidyr)
library(dplyr)
data %>% mutate(hour=paste0('hour', hour)) %>% spread(hour, Value)
#    date day hour1 hour2 hour3 hour4
#1 Jan-97   1    65    29    31    42
#2 Jan-97   2    42    52    61    57
#3 Jan-97   3    55    52    57    46
Pierre L
  • 28,203
  • 6
  • 47
  • 69
  • In case to change the column names, `library(dplyr); data %>% mutate(hour=paste0('hour', hour)) %>% spread(hour, Value))` or just use `spread(data, hour,Value) %>% setNames(., c(names(.)[1:2], paste0('hour', names(.)[3:6])))` – akrun Jul 08 '15 at 19:44
  • 1
    @akrun Looks much better. I'm going to go through other examples and practice wide and long reshapes. – Pierre L Jul 08 '15 at 19:49