Reshape data across timepoints in R

Question

I want view the variables of interest (height,weight and BP) according to their visits in a horizontal manner.

This is the original dataset:

attempt = data.frame( ID = c(rep("S1",3),rep("S2",3)),
             visit = rep(seq(1:3),2),
             height = c(154,155,156,150,152,158),
             weight = c(54,56,57,60,62,70),
             BP = c(140,120,160,170,180,190))

original data

I tried the reshape command below but the output looks strange.

data2 <- reshape(attempt, timevar="visit", idvar="ID", direction="wide")

after reshape command

Any ideas on why my output does not show? Thanks

My desired output for the column heading is:

ID height.1 height.2 height.3 weight.1 weight.2 weight.3 BP.1 BP.2 BP.3

I want each ID's height, weight and BP to be sorted based on their visits in horizontal columns.

What is your desired output, how would you want you data to be — Confusion Matrix, Nov 11 '19 at 06:09

score 2 · Answer 1 · answered Nov 11 '19 at 07:18

2

Using the dcast() from the data.table-package, this becomes pretty straightforward:

library(data.table)
#make a data.table out of 'attempt'
data.table::setDT(attempt)
#cast to desired wide format
data.table::dcast( attempt,
                   ID ~ visit,
                   value.var = c( "height", "weight", "BP" ) )

#    ID height_1 height_2 height_3 weight_1 weight_2 weight_3 BP_1 BP_2 BP_3
# 1: S1      154      155      156       54       56       57  140  120  160
# 2: S2      150      152      158       60       62       70  170  180  190

answered Nov 11 '19 at 07:18

Wimpel

26,031
1
20
37

Nice ! I did not know about the `data.table` package. Thks ! – dc37 Nov 11 '19 at 07:27
Instead of writing down the column names for the value.var, is it possible to indicate as a column range? – racergeek Nov 11 '19 at 07:57
@racergeek only indeirect using `names()` like `names(attempt[3:5])` as a vector. – Wimpel Nov 11 '19 at 08:05

dc37 · Answer 2 · 2019-11-11T07:21:58.220

Hi and welcome to StackOverflow !

So, here is a solution, I doubt that it is a perfect solution but it is doing the job, here what you can do:

attempt = reshape(attempt, idvar = "ID", direction = "wide", v.names = c("height","weight","BP"), timevar = "visit")

attempt = attempt[,c("ID",colnames(attempt)[grep("height",colnames(attempt))],
             colnames(attempt)[grep("weight",colnames(attempt))],
             colnames(attempt)[grep("BP",colnames(attempt))])]

And here is the output:

> attempt
  ID height.1 height.2 height.3 weight.1 weight.2 weight.3 BP.1 BP.2 BP.3
1 S1      154      155      156       54       56       57  140  120  160
4 S2      150      152      158       60       62       70  170  180  190

As I said, this solution is definitely not the best but the best to my current knowledge. Most likely more experienced R coders will be able to show up with a nicer and more elegant solution ;)

Sorry, I think my question was not clear. I have added your suggestions! Thank you! — racergeek, Nov 11 '19 at 06:56

score 0 · Answer 3 · answered Nov 11 '19 at 07:30

Base R solution slightly amending your code:

Should do the trick (worked for me):

data2 <- data.frame(reshape(attempt, timevar="visit", idvar="ID", direction="wide"), stringsAsFactors = F)

But just incase:

data2 <- setNames(data2, gsub("[[:punct:]]|\\s+", ".", trimws(names(data2), "both")))

Data:

attempt <-
  structure(
    list(
      ID = structure(
        c(1L, 1L, 1L, 2L, 2L, 2L),
        .Label = c("S1",
                   "S2"),
        class = "factor"
      ),
      visit = c(1L, 2L, 3L, 1L, 2L, 3L),
      height = c(154, 155, 156, 150, 152, 158),
      weight = c(54,
                 56, 57, 60, 62, 70),
      BP = c(140, 120, 160, 170, 180, 190)
    ),
    class = "data.frame",
    row.names = c(NA,-6L)
  )

Reshape data across timepoints in R

3 Answers3