Reshape data frames: wide and long format

Using stack() for data frames with a simple structure

Wide format -> long format

set.seed(123)
Nj     <- 4
cond1  <- sample(1:10, Nj, replace=TRUE)
cond2  <- sample(1:10, Nj, replace=TRUE)
cond3  <- sample(1:10, Nj, replace=TRUE)
dfTemp <- data.frame(cond1, cond2, cond3)
(res   <- stack(dfTemp, select=c("cond1", "cond3")))

  values   ind
1      3 cond1
2      3 cond1
3     10 cond1
4      2 cond1
5      9 cond3
6     10 cond3
7      5 cond3
8      3 cond3

str(res)

'data.frame':   8 obs. of  2 variables:
 $ values: int  3 3 10 2 9 10 5 3
 $ ind   : Factor w/ 2 levels "cond1","cond3": 1 1 1 1 2 2 2 2

Long format -> wide format

unstack(res)

  cond1 cond3
1     3     9
2     3    10
3    10     5
4     2     3

res$IVnew <- factor(sample(rep(c("A", "B"), Nj), 2*Nj, replace=FALSE))
res$DVnew <- sample(100:200, 2*Nj)
head(res)

  values   ind IVnew DVnew
1      3 cond1     A   125
2      3 cond1     A   106
3     10 cond1     B   141
4      2 cond1     A   108
5      9 cond3     A   182
6     10 cond3     B   135

unstack(res, DVnew ~ IVnew)

    A   B
1 125 141
2 106 135
3 108 177
4 182 180

Using reshape() for more complex data frames

One within variable

Simulate data

Nj      <- 2
P       <- 2
Q       <- 3
id      <- 1:(P*Nj)
DV_t1   <- round(rnorm(P*Nj, -1, 1), 2)
DV_t2   <- round(rnorm(P*Nj,  0, 1), 2)
DV_t3   <- round(rnorm(P*Nj,  1, 1), 2)
IVbtw   <- factor(rep(c("A", "B"), Nj))
(dfWide <- data.frame(id, IVbtw, DV_t1, DV_t2, DV_t3))

  id IVbtw DV_t1 DV_t2 DV_t3
1  1     A -0.30 -1.03  1.84
2  2     B -1.47 -0.73  1.15
3  3     A -2.07 -0.63 -0.14
4  4     B -1.22 -1.69  2.25

idL    <- rep(id, Q)
DVl    <- c(DV_t1, DV_t2, DV_t3)
IVwth  <- factor(rep(1:3, each=P*Nj))
IVbtwL <- rep(IVbtw, times=Q)
dfLong <- data.frame(id=idL, IVbtw=IVbtwL, IVwth=IVwth, DV=DVl)
dfLong[order(dfLong$id), ]

   id IVbtw IVwth    DV
1   1     A     1 -0.30
5   1     A     2 -1.03
9   1     A     3  1.84
2   2     B     1 -1.47
6   2     B     2 -0.73
10  2     B     3  1.15
3   3     A     1 -2.07
7   3     A     2 -0.63
11  3     A     3 -0.14
4   4     B     1 -1.22
8   4     B     2 -1.69
12  4     B     3  2.25

Wide format -> long format

resLong <- reshape(dfWide, varying=c("DV_t1", "DV_t2", "DV_t3"),
                   direction="long", idvar=c("id", "IVbtw"),
                   v.names="DV", timevar="IVwth")
resLong[order(resLong$id), ]

      id IVbtw IVwth    DV
1.A.1  1     A     1 -0.30
1.A.2  1     A     2 -1.03
1.A.3  1     A     3  1.84
2.B.1  2     B     1 -1.47
2.B.2  2     B     2 -0.73
2.B.3  2     B     3  1.15
3.A.1  3     A     1 -2.07
3.A.2  3     A     2 -0.63
3.A.3  3     A     3 -0.14
4.B.1  4     B     1 -1.22
4.B.2  4     B     2 -1.69
4.B.3  4     B     3  2.25

resLong$IVwth <- factor(resLong$IVwth)
all.equal(dfLong, resLong, check.attributes=FALSE)

[1] TRUE

Long format -> wide format

reshape(dfLong, v.names="DV", timevar="IVwth", idvar=c("id", "IVbtw"),
        direction="wide")

  id IVbtw  DV.1  DV.2  DV.3
1  1     A -0.30 -1.03  1.84
2  2     B -1.47 -0.73  1.15
3  3     A -2.07 -0.63 -0.14
4  4     B -1.22 -1.69  2.25

Two within variables

Simulate data

Nj   <- 4
id   <- 1:Nj
t_11 <- round(rnorm(Nj,  8, 2), 2)
t_21 <- round(rnorm(Nj, 13, 2), 2)
t_31 <- round(rnorm(Nj, 13, 2), 2)
t_12 <- round(rnorm(Nj, 10, 2), 2)
t_22 <- round(rnorm(Nj, 15, 2), 2)
t_32 <- round(rnorm(Nj, 15, 2), 2)
dfW  <- data.frame(id, t_11, t_21, t_31, t_12, t_22, t_32)

Wide format -> long format

(dfL1 <- reshape(dfW, varying=list(c("t_11", "t_21", "t_31"),
                                   c("t_12", "t_22", "t_32")),
                 direction="long", timevar="IV1", idvar="id",
                 v.names=c("IV2-1", "IV2-2")))

    id IV1 IV2-1 IV2-2
1.1  1   1  8.85  7.47
2.1  2   1  7.41 14.34
3.1  3   1  9.79 12.42
4.1  4   1  9.76  7.75
1.2  1   2 14.64 14.19
2.2  2   2 14.38 14.07
3.2  3   2 14.11 16.56
4.2  4   2 12.88 14.83
1.3  1   3 12.39 15.51
2.3  2   3 12.24 14.94
3.3  3   3 11.61 14.91
4.3  4   3 12.58 17.74

dfL2 <- reshape(dfL1, varying=c("IV2-1", "IV2-2"),
                direction="long", timevar="IV2",
                idvar=c("id", "IV1"), v.names="DV")
head(dfL2)

      id IV1 IV2    DV
1.1.1  1   1   1  8.85
2.1.1  2   1   1  7.41
3.1.1  3   1   1  9.79
4.1.1  4   1   1  9.76
1.2.1  1   2   1 14.64
2.2.1  2   2   1 14.38

Long format -> wide format

dfW1 <- reshape(dfL2, v.names="DV", timevar="IV1",
                idvar=c("id", "IV2"), direction="wide")

dfW2 <- reshape(dfW1, v.names=c("DV.1", "DV.2", "DV.3"),
                timevar="IV2", idvar="id", direction="wide")

all.equal(dfW, dfW2, check.attributes=FALSE)

[1] TRUE

Useful packages

Package tidyr provides functions pivot_longer() and pivot_wider() for an alternative approach to reshaping data frames that can be integrated into a dplyr based workflow.

Get the article source from GitHub

R markdown - markdown - R code - all posts