Pivot / Reshape data

Question

My sample data looks like this:

 data <- read.table(header=T, text='
  pid     measurement1     Tdays1     measurement2      Tdays2     measurement3     Tdays3  measurment4    Tdays4 
   1         1356           1435         1483            1405         1563           1374       NA           NA     
   2         943            1848         1173            1818         1300           1785       NA           NA     
   3         1590           185          NA              NA           NA             NA         1585         294    
   4         130            72           443             70           NA             NA         136          79     
   4         140            82           NA              NA           NA             NA         756          89     
   4         220            126          266             124          NA             NA         703          128    
   4         166            159          213             156          476            145        776          166    
   4         380            189          583             173          NA             NA         586          203    
   4         353            231          510             222          656            217        526          240    
   4         180            268          NA              NA           NA             NA         NA           NA       
   4         NA             NA           NA              NA           NA             NA         580          278    
   4         571            334          596             303          816            289        483          371    
  ')

Now i would like it to look something like this:

PID     Time (days)   Value
 1       1435         1356
 1       1405         1483
 1       1374         1563
 2       1848         943
 2       1818         1173
 2       1785         1300
 3       185          1590
...      ...          ...

How would i tend to get there? I have looked up some things about wide to longformat, but it doesn't seem to do the trick. Kind regards, and thank you in advance.

How do you get there? What do you want to do? Please describe the problem and add the code you have tried and failed. — Sotos, Sep 14 '20 at 13:08
Does this answer your question? [Pivot/Reshape data in R](https://stackoverflow.com/questions/63882983/pivot-reshape-data-in-r) — Duck, Sep 14 '20 at 13:22
How does this differ from the question you posted [2 hours before](https://stackoverflow.com/q/63882983/5325862)? — camille, Sep 14 '20 at 18:26
It doesn't Camille, it was edited so many times that even I lost track so I figured I'd post a new one. Thanks for the help! — JannickLinden, Sep 15 '20 at 16:32

score 0 · Accepted Answer · answered Sep 14 '20 at 13:30

Here is a base R option

u <- cbind(
  data[1],
  do.call(
    rbind,
    lapply(
      split.default(data[-1], ceiling(seq_along(data[-1]) / 2)),
      setNames,
      c("Value", "Time")
    )
  )
)

out <- `row.names<-`(
  subset(
    x <- u[order(u$pid), ],
    complete.cases(x)
  ), NULL
)

such that

> out
   pid Value Time
1    1  1356 1435
2    1  1483 1405
3    1  1563 1374
4    2   943 1848
5    2  1173 1818
6    2  1300 1785
7    3  1590  185
8    3  1585  294
9    4   130   72
10   4   140   82
11   4   220  126
12   4   166  159
13   4   380  189
14   4   353  231
15   4   180  268
16   4   571  334
17   4   443   70
18   4   266  124
19   4   213  156
20   4   583  173
21   4   510  222
22   4   596  303
23   4   476  145
24   4   656  217
25   4   816  289
26   4   136   79
27   4   756   89
28   4   703  128
29   4   776  166
30   4   586  203
31   4   526  240
32   4   580  278
33   4   483  371

This solved it for me! Thank you so much for the help, I spent days on Google but got nowhere. Thanks a bunch! — JannickLinden, Sep 15 '20 at 16:33

score 0 · Answer 2 · answered Sep 14 '20 at 16:48

An option with pivot_longer

library(dplyr)
library(tidyr)
names(data)[8] <- "measurement4"
data %>%
    pivot_longer(cols = -pid, names_to = c('.value', 'grp'),
    names_sep = "(?<=[a-z])(?=[0-9])", values_drop_na = TRUE) %>% select(-grp)
# A tibble: 33 x 3
#     pid measurement Tdays
#   <int>       <int> <int>
# 1     1        1356  1435
# 2     1        1483  1405
# 3     1        1563  1374
# 4     2         943  1848
# 5     2        1173  1818
# 6     2        1300  1785
# 7     3        1590   185
# 8     3        1585   294
# 9     4         130    72
#10     4         443    70
# … with 23 more rows

Pivot / Reshape data

2 Answers2