reshaping columns in a data frame

Question

Say I have the following data frame:

ID<-c(1,1,1,1,1,2,2,2,2,2,3,3,3,3,3, 4,4,4,4,4,5,5,5,5,5)
Score<- sample(1:20, 25, replace=TRUE)
days<-rep(c("Mon", "Tue", "Wed", "Thu", "Fri"), times=5)
t<-cbind(ID, Score, days)

I would like to reshape it so that the new columns are ID and the actual weekday names, (meaning 6 columns) and the Score values are distributed according to their ID and day name. Something like this:

I found that reshape package might do. Tried (melt and cast) but it did not produce the result I wanted, but something like in this post: Melt data for one column

You do not need any external package to do this. R has a built-in `reshape` command that will do it. Also, when posting examples that use random sampling, you should include `set.seed()` so that the item is reproducible. — TARehman, Dec 21 '15 at 17:37

score 10 · Answer 1 · edited Dec 21 '15 at 17:40

A base R solution that uses the built-in reshape command.

set.seed(12345)
t <- data.frame(id = c(1,1,1,1,1,2,2,2,2,2,3,3,3,3,3,4,4,4,4,4,5,5,5,5,5),
                score = sample(x = 1:20,size = 25,replace = TRUE),
                days = rep(x = c("Mon","Tue","Wed","Thu","Fri"),times = 5))

t.wide <- reshape(data = t,
                  v.names = "score",
                  timevar = "days",
                  idvar = "id",
                  direction = "wide")
names(t.wide) <- gsub(pattern = "score.",replacement = "",x = names(t.wide),fixed = TRUE)
t.wide
   id Mon Tue Wed Thu Fri
1   1  15  18  16  18  10
6   2   4   7  11  15  20
11  3   1   4  15   1   8
16  4  10   8   9   4  20
21  5  10   7  20  15  13

score 8 · Answer 2 · answered Dec 21 '15 at 17:31

You can use reshape2 to do this, but you need a data.frame to do that. Using cbind produces a matrix. (And converts all your numerical variables to characters in this case, as matrices can only hold one data type).

I've changed your code to produce a dataframe, which is already in long format (one row per observation).

set.seed(123)
ID<-c(1,1,1,1,1,2,2,2,2,2,3,3,3,3,3, 4,4,4,4,4,5,5,5,5,5)
Score<- sample(1:20, 25, replace=TRUE)
days<-rep(c("Mon", "Tue", "Wed", "Thu", "Fri"), times=5)
dat<-data.frame(ID, Score, days)

Changing it to wide using reshape2 is then quite straightforward:

library(reshape2)

res <- dcast(ID~days,value.var="Score",data=dat)


> res
  ID Fri Mon Thu Tue Wed
1  1  16   3   2  12   6
2  2  19  13  12   7  19
3  3  19  19  17   8  15
4  4  15   3   8   1  20
5  5   3  11  18   8  15

score 3 · Answer 3 · answered Dec 22 '15 at 05:16

You could also use unstack if your data are complete (same number of days per id).

Here's an example (using the data from TARehman's answer):

unstack(t, score ~ days)
#   Fri Mon Thu Tue Wed
# 1  10  15  18  18  16
# 2  20   4  15   7  11
# 3   8   1   1   4  15
# 4  20  10   4   8   9
# 5  13  10  15   7  20

Here's the clean-up for the column ordering, and for adding in the ID column:

cbind(ID = unique(t$id), unstack(t, score ~ days)[c("Mon", "Tue", "Wed", "Thu", "Fri")])
##   ID Mon Tue Wed Thu Fri
## 1  1  15  18  16  18  10
## 2  2   4   7  11  15  20
## 3  3   1   4  15   1   8
## 4  4  10   8   9   4  20
## 5  5  10   7  20  15  13

boshek · Accepted Answer · 2015-12-22T16:47:56.753

2

Rather than reshape I'd move to the newer tidyr package and also make use of dplyr like so:

library(dplyr)
library(tidyr)

tdf<-as.data.frame(t) %>%
  mutate(Score=as.numeric(Score)) %>%
  spread(days,Score, fill=NA)

glimpse(tdf)

HTH

edited Dec 22 '15 at 16:47

answered Dec 21 '15 at 17:26

boshek

4,100
1
31
55

@ boshek I can not install the tidyr to my work computer (restricted access). Will try it at home a little bit later and will let you know. Thanks – Vasile Dec 21 '15 at 17:30
@Heroka Nothing is wrong with it per se. But since I've moved to a tidyr and dplyr of doing things I've noticed improvements in efficiency and code readability for my workflow. Combined use of pipes and gather/spread makes a much nicer and readable code. – boshek Dec 21 '15 at 17:36
this doesn't give the desired results – rawr Dec 21 '15 at 17:47
2

How did you make a use of dplyr here? – David Arenburg Dec 22 '15 at 01:03
1

@DavidArenburg Because it's not a complete answer without the pipe! – A5C1D2H2I1M1N2O1R2T1 Dec 22 '15 at 05:16
2

This gives all factor columns in return, which is not really desirable considering it's nothing but numbers. – Rich Scriven Dec 22 '15 at 05:47

Veerendra Gadekar · Answer 5 · 2015-12-21T19:11:16.623

2

Just another option using splitstackshape

library(splitstackshape)
data = data.frame(t)
out = setnames(cSplit(setDT(data)[, .(x = toString(Score)), by = ID], 
               'x', ','), c('ID', unique(days)))

#> out
#   ID Mon Tue Wed Thu Fri
#1:  1   8  14  11   5  10
#2:  2  16   1   4  14   8
#3:  3   8  18  19  13   3
#4:  4  16   9  19  16   6
#5:  5   7   2   1   2  13

edited Dec 21 '15 at 19:11

answered Dec 21 '15 at 17:43

Veerendra Gadekar

4,452
19
24

score 1 · Answer 6 · answered Dec 21 '15 at 17:28

1

Within both the dplyr & tidyr package, use spread to achieve the following:

library(dplyr)
library(tidyr)
t <- tbl_df(as.data.frame(t))
t %>% spread(days, Score, ID)

and you get the following output:

      ID    Fri    Mon    Thu    Tue    Wed
  (fctr) (fctr) (fctr) (fctr) (fctr) (fctr)
1      1     10     10     18     17     10
2      2     18     11     14      3     16
3      3     11     13      9     15     17
4      4     13     13     16     17     11
5      5      7     14      9     15     20

answered Dec 21 '15 at 17:28

Steven_

738
2
7
20

5

why not simply `library(tidyr); spread(data.frame(t), 'days', 'Score')` – Veerendra Gadekar Dec 21 '15 at 17:33
This doesn't solve the issue in the data-creation: all columns appear numeric, but are factors. – Heroka Dec 21 '15 at 17:48
@VeerendraGadekar, that works too, I just like all of my data frames as table data frames. So yes - you wouldn't need the dplyr library given your way. Also, Changing variable types can easily be done by changing your factors into numeric - such as the following: `as.numeric.factor <- function(x) {as.numeric(levels(x))[x]}` [http://stackoverflow.com/questions/3418128/how-to-convert-a-factor-to-an-integer-numeric-without-a-loss-of-information] – Steven_ Dec 21 '15 at 18:01

reshaping columns in a data frame

6 Answers6